表格中升序或降序之后怎么取消排序?(表格中的升序降序怎么消除)
1054
2022-05-29
0. 前言:
# 前端业务人员误操作,把我们ERP里面的部分数据删除了,幸好之前rman备份比较完备,直接另起炉灶,进行异机恢复吧;
# 在华为云ECS上搭建的Oracle环境,RMAN备份放在SFS存储内;
# 恢复时间目标: 2020-11-30 01:00:00
1. 确认恢复点
# 恢复之前,需要查看源数据库的RMAN备份日志,确认需要恢复的时间点
# 查看源库,以确认需要恢复的spfile的位置:
RMAN> list backup;
......
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3585 Incr 0 3.43G DISK 00:05:27 29-NOV-20
BP Key: 3585 Status: AVAILABLE Compressed: YES Tag: OADB_INCR_L0
Piece Name: /dbbackup/ltdb/oadb/full_OADB_20201129_3585
List of Datafiles in backup set 3585
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 978758364 29-NOV-20 /home/oracle/oadb/system01.dbf
2 0 Incr 978758364 29-NOV-20 /home/oracle/oadb/sysaux01.dbf
3 0 Incr 978758364 29-NOV-20 /home/oracle/oadb/undotbs01.dbf
4 0 Incr 978758364 29-NOV-20 /home/oracle/oadb/users01.dbf
5 0 Incr 978758364 29-NOV-20 /home/oracle/oadb/TABLE_SPACE
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3586 Incr 0 1.09M DISK 00:00:02 29-NOV-20
BP Key: 3586 Status: AVAILABLE Compressed: YES Tag: OADB_INCR_L0
Piece Name: /dbbackup/ltdb/oadb/full_OADB_20201129_3586
SPFILE Included: Modification time: 26-NOV-20
SPFILE db_unique_name: OADB
Control File Included: Ckp SCN: 978759124 Ckp time: 29-NOV-20
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3587 77.96M DISK 00:00:06 29-NOV-20
BP Key: 3587 Status: AVAILABLE Compressed: YES Tag: TAG20201129T030542
Piece Name: /dbbackup/ltdb/oadb/arclog_0_3587_1_1057719942_OADB
List of Archived Logs in backup set 3587
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 7028 978623087 28-NOV-20 978669945 28-NOV-20
1 7029 978669945 28-NOV-20 978732444 28-NOV-20
1 7030 978732444 28-NOV-20 978759135 29-NOV-20
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3588 Incr 1 43.51M DISK 00:02:05 30-NOV-20
BP Key: 3588 Status: AVAILABLE Compressed: YES Tag: OADB_INCR_L1
Piece Name: /dbbackup/ltdb/oadb/incr_OADB_20201130_3588
List of Datafiles in backup set 3588
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 1 Incr 978892860 30-NOV-20 /home/oracle/oadb/system01.dbf
2 1 Incr 978892860 30-NOV-20 /home/oracle/oadb/sysaux01.dbf
3 1 Incr 978892860 30-NOV-20 /home/oracle/oadb/undotbs01.dbf
4 1 Incr 978892860 30-NOV-20 /home/oracle/oadb/users01.dbf
5 1 Incr 978892860 30-NOV-20 /home/oracle/oadb/TABLE_SPACE
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3589 Incr 1 1.09M DISK 00:00:01 30-NOV-20
BP Key: 3589 Status: AVAILABLE Compressed: YES Tag: OADB_INCR_L1
Piece Name: /dbbackup/ltdb/oadb/incr_OADB_20201130_3589
SPFILE Included: Modification time: 26-NOV-20
SPFILE db_unique_name: OADB
Control File Included: Ckp SCN: 978893333 Ckp time: 30-NOV-20
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3590 78.09M DISK 00:00:06 30-NOV-20
BP Key: 3590 Status: AVAILABLE Compressed: YES Tag: TAG20201130T030211
Piece Name: /dbbackup/ltdb/oadb/arclog_1_3590_1_1057806131_OADB
List of Archived Logs in backup set 3590
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 7031 978759135 29-NOV-20 978802271 29-NOV-20
1 7032 978802271 29-NOV-20 978865784 29-NOV-20
1 7033 978865784 29-NOV-20 978893344 30-NOV-20
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3591 Incr 1 102.13M DISK 00:01:59 01-DEC-20
BP Key: 3591 Status: AVAILABLE Compressed: YES Tag: OADB_INCR_L1
Piece Name: /dbbackup/ltdb/oadb/incr_OADB_20201201_3591
List of Datafiles in backup set 3591
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 1 Incr 979213617 01-DEC-20 /home/oracle/oadb/system01.dbf
2 1 Incr 979213617 01-DEC-20 /home/oracle/oadb/sysaux01.dbf
3 1 Incr 979213617 01-DEC-20 /home/oracle/oadb/undotbs01.dbf
4 1 Incr 979213617 01-DEC-20 /home/oracle/oadb/users01.dbf
5 1 Incr 979213617 01-DEC-20 /home/oracle/oadb/TABLE_SPACE
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3592 Incr 1 1.09M DISK 00:00:01 01-DEC-20
BP Key: 3592 Status: AVAILABLE Compressed: YES Tag: OADB_INCR_L1
Piece Name: /dbbackup/ltdb/oadb/incr_OADB_20201201_3592
SPFILE Included: Modification time: 26-NOV-20
SPFILE db_unique_name: OADB
Control File Included: Ckp SCN: 979214188 Ckp time: 01-DEC-20
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3593 118.16M DISK 00:00:10 01-DEC-20
BP Key: 3593 Status: AVAILABLE Compressed: YES Tag: TAG20201201T030211
Piece Name: /dbbackup/ltdb/oadb/arclog_1_3593_1_1057978931_OADB
List of Archived Logs in backup set 3593
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 7034 978893344 30-NOV-20 979002365 30-NOV-20
1 7035 979002365 30-NOV-20 979123966 30-NOV-20
1 7036 979123966 30-NOV-20 979172358 30-NOV-20
1 7037 979172358 30-NOV-20 979186182 30-NOV-20
1 7038 979186182 30-NOV-20 979214198 01-DEC-20
......
#注意一定要选择要恢复的时间点前的第一个备份,否则会报错:
Starting recover at 05-NOV-20
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/05/2020 21:04:41
RMAN-06555: datafile 1 must be restored from backup created before 31-OCT-20
2. 基础环境准备
# 准备rman备份集,将备份集复制到目标数据库环境;
# 为懒省事儿,在目标主机创建位置相同的路径
mkdir /dbbackup
mount -t nfs -o vers=3,timeo=600,nolock sfs-nas3.cn-east-2.myhuaweicloud.com:/share-****** /nfs
cp -r /nfs/ly /dbbackup/
chown -R oracle.dba /dbbackup/
# 在新环境里,准备数据库所需要的目录
[root@ecs-ae88 ~]# mkdir -p /home/oracle/oadb
[root@ecs-ae88 ~]# chown -R oracle.dba /home/oracle/oadb/
[root@ecs-ae88 ~]# mkdir -p /home/oracle11g/app/archive/oadb
[root@ecs-ae88 ~]# mkdir -p /home/oracle11g/app/fast_recovery_area/oadb/
[root@ecs-ae88 ~]# mkdir -p /home/oracle11g/app/admin/oadb/adump/
[root@ecs-ae88 ~]# chown -R oracle.dba /home/oracle11g
[oracle@ly-eims-db-01 app]$ cd /home/oracle11g/app/
[oracle@ly-eims-db-01 app]$ scp -r admin 192.168.50.218:$PWD/
3. 开始恢复
# 恢复参数文件
rman target /
RMAN> startup nomount;
RMAN> restore spfile from '/dbbackup/ltdb/oadb/incr_OADB_20201201_3592';
RMAN> shutdown immediate;
# 恢复控制文件
##准备恢复
RMAN> startup nomount;
RMAN> restore controlfile from '/dbbackup/ltdb/oadb/incr_OADB_20201201_3592';
RMAN> alter database mount;
# 恢复数据文件,约需要10分钟
RMAN> restore database;
Starting restore at 05-NOV-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3771 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/oadb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/oadb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/oadb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/oadb/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/oadb/TABLE_SPACE
channel ORA_DISK_1: reading from backup piece /dbbackup/ly/oadb/full_OADB_20201101_3177
channel ORA_DISK_1: piece handle=/dbbackup/ly/oadb/full_OADB_20201101_3177 tag=OADB_INCR_L0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:55
Finished restore at 05-NOV-20
# 数据库recover
run{
allocate channel t1 device type disk;
set until time "to_date('2020-11-30 01:00:00','yyyy-mm-dd hh24:mi:ss')";
recover database;
}
#报错:
starting media recovery
unable to find archived log
archived log thread=1 sequence=6163
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/05/2020 21:16:28
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6163 and starting SCN of 896957574
#因为恢复的控制文件时间较早,所以部分需要的归档,不在list backup内:
#查看源库,发现上面提示的6163号归档日志,其对应的备份文件是在磁盘上存在的;
#所以进行手动编目:
catalog start with '/dbbackup/ly/oadb/';
catalog start with '/dbbackup/ltdb/oadb/';
# 再次执行recover
......
Finished recover at 05-NOV-20
released channel: t1
4. 启动数据库
# 启动数据库,完成恢复:
alter database open resetlogs;
至此,数据库恢复完成。
Oracle 数据库
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。