丢失归档日志做不完全恢复(until sequence)

末点 2019-12-11

环境:

OS:Centos 7

DB:12.2.0.1

1.备份数据库
run
{
allocate channel ch1 device type disk;
backup as compressed backupset full filesperset 5 database format ‘/u01/rman_backup/db_fullbackup_%d_%s_%p_%T‘;
sql ‘alter system archive log current‘;
backup current controlfile format ‘/u01/rman_backup/ctl_%d_%s_%p_%T‘;
backup spfile format ‘/u01/rman_backup/spfile_%d_%s_%p_%T‘;
release channel ch1;
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup;
delete noprompt obsolete;
}

2.切换生成多个logfile
SQL> connect / as sysdba
Connected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive_log
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.


3.模拟手工删除数据一个归档日志
[ archive_log]$ ls -al
total 4672
drwxr-xr-x.  2 oracle oinstall     136 Dec 11 03:27 .
drwxr-xr-x. 11 oracle oinstall     151 Dec  6 04:18 ..
-rw-r-----.  1 oracle oinstall 4765696 Dec 11 03:25 1_2_1026700907.dbf
-rw-r-----.  1 oracle oinstall    3072 Dec 11 03:27 1_3_1026700907.dbf
-rw-r-----.  1 oracle oinstall    1024 Dec 11 03:27 1_4_1026700907.dbf
-rw-r-----.  1 oracle oinstall    1024 Dec 11 03:27 1_5_1026700907.dbf
-rw-r-----.  1 oracle oinstall    1024 Dec 11 03:27 1_6_1026700907.dbf
[ archive_log]$ rm 1_4_1026700907.dbf

这里我们删除了4这个归档日志


4.可恢复性检查

RMAN> run{
2> set until sequence 4;  ##这里不包含归档日志4
3> restore database preview;
4> }

executing command: SET until clause

Starting restore at 2019-12-11 03:39:43
using channel ORA_DISK_1


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
64      Full    375.04M    DISK        00:00:41     2019-12-11 03:24:37
        BP Key: 64   Status: AVAILABLE  Compressed: YES  Tag: TAG20191211T032356
        Piece Name: /u01/rman_backup/db_fullbackup_ORA12C_71_1_20191211
  List of Datafiles in backup set 64
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  1       Full 2287488    2019-12-11 03:23:56              NO    /u01/app/oracle/oradata/ora12c/system01.dbf
  3       Full 2287488    2019-12-11 03:23:56              NO    /u01/app/oracle/oradata/ora12c/sysaux01.dbf
  4       Full 2287488    2019-12-11 03:23:56              NO    /u01/app/oracle/oradata/ora12c/undotbs01.dbf
  7       Full 2287488    2019-12-11 03:23:56              NO    /u01/app/oracle/oradata/ora12c/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
65      Full    165.62M    DISK        00:00:17     2019-12-11 03:24:55
        BP Key: 65   Status: AVAILABLE  Compressed: YES  Tag: TAG20191211T032356
        Piece Name: /u01/rman_backup/db_fullbackup_ORA12C_72_1_20191211
  List of Datafiles in backup set 65
  Container ID: 2, PDB Name: PDB$SEED
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  5       Full 1440734    2019-12-06 03:16:13              NO    /u01/app/oracle/oradata/ora12c/pdbseed/system01.dbf
  6       Full 1440734    2019-12-06 03:16:13              NO    /u01/app/oracle/oradata/ora12c/pdbseed/sysaux01.dbf
  8       Full 1440734    2019-12-06 03:16:13              NO    /u01/app/oracle/oradata/ora12c/pdbseed/undotbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
67      Full    54.10M     DISK        00:00:06     2019-12-11 03:25:28
        BP Key: 67   Status: AVAILABLE  Compressed: YES  Tag: TAG20191211T032356
        Piece Name: /u01/rman_backup/db_fullbackup_ORA12C_74_1_20191211
  List of Datafiles in backup set 67
  Container ID: 3, PDB Name: ORA12CPDB1
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  9       Full 2287536    2019-12-11 03:25:22              NO    /u01/app/oracle/oradata/ora12c/ora12cpdb1/system01.dbf
  11      Full 2287536    2019-12-11 03:25:22              NO    /u01/app/oracle/oradata/ora12c/ora12cpdb1/undotbs01.dbf
  13      Full 2287536    2019-12-11 03:25:22              NO    /u01/app/oracle/oradata/ora12c/ora12cpdb1/TPS_DATA13.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
66      Full    115.23M    DISK        00:00:14     2019-12-11 03:25:21
        BP Key: 66   Status: AVAILABLE  Compressed: YES  Tag: TAG20191211T032356
        Piece Name: /u01/rman_backup/db_fullbackup_ORA12C_73_1_20191211
  List of Datafiles in backup set 66
  Container ID: 3, PDB Name: ORA12CPDB1
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  10      Full 2287525    2019-12-11 03:25:07              NO    /u01/app/oracle/oradata/ora12c/ora12cpdb1/sysaux01.dbf
  12      Full 2287525    2019-12-11 03:25:07              NO    /u01/app/oracle/oradata/ora12c/ora12cpdb1/users01.dbf
  14      Full 2287525    2019-12-11 03:25:07              NO    /u01/app/oracle/oradata/ora12c/ora12cpdb1/TPS_DATA14.dbf

List of Archived Log Copies for database with db_unique_name ORA12C
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - -------------------
58      1    2       A 2019-12-11 02:43:10
        Name: /u01/app/oracle/archive_log/1_2_1026700907.dbf

59      1    3       A 2019-12-11 03:25:35
        Name: /u01/app/oracle/archive_log/1_3_1026700907.dbf

recovery will be done up to SCN 2287678
Media recovery start SCN is 2287488
Recovery must be done beyond SCN 2287536 to clear datafile fuzziness
Finished restore at 2019-12-11 03:39:44

说明可以恢复,2和3归档都存在,下面我们验证恢复到归档日志5看下什么情况



RMAN> run{
2> set until sequence 5;
3> restore database preview;
4> }

executing command: SET until clause

Starting restore at 2019-12-11 03:40:22
using channel ORA_DISK_1


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
64      Full    375.04M    DISK        00:00:41     2019-12-11 03:24:37
        BP Key: 64   Status: AVAILABLE  Compressed: YES  Tag: TAG20191211T032356
        Piece Name: /u01/rman_backup/db_fullbackup_ORA12C_71_1_20191211
  List of Datafiles in backup set 64
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  1       Full 2287488    2019-12-11 03:23:56              NO    /u01/app/oracle/oradata/ora12c/system01.dbf
  3       Full 2287488    2019-12-11 03:23:56              NO    /u01/app/oracle/oradata/ora12c/sysaux01.dbf
  4       Full 2287488    2019-12-11 03:23:56              NO    /u01/app/oracle/oradata/ora12c/undotbs01.dbf
  7       Full 2287488    2019-12-11 03:23:56              NO    /u01/app/oracle/oradata/ora12c/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
65      Full    165.62M    DISK        00:00:17     2019-12-11 03:24:55
        BP Key: 65   Status: AVAILABLE  Compressed: YES  Tag: TAG20191211T032356
        Piece Name: /u01/rman_backup/db_fullbackup_ORA12C_72_1_20191211
  List of Datafiles in backup set 65
  Container ID: 2, PDB Name: PDB$SEED
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  5       Full 1440734    2019-12-06 03:16:13              NO    /u01/app/oracle/oradata/ora12c/pdbseed/system01.dbf
  6       Full 1440734    2019-12-06 03:16:13              NO    /u01/app/oracle/oradata/ora12c/pdbseed/sysaux01.dbf
  8       Full 1440734    2019-12-06 03:16:13              NO    /u01/app/oracle/oradata/ora12c/pdbseed/undotbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
67      Full    54.10M     DISK        00:00:06     2019-12-11 03:25:28
        BP Key: 67   Status: AVAILABLE  Compressed: YES  Tag: TAG20191211T032356
        Piece Name: /u01/rman_backup/db_fullbackup_ORA12C_74_1_20191211
  List of Datafiles in backup set 67
  Container ID: 3, PDB Name: ORA12CPDB1
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  9       Full 2287536    2019-12-11 03:25:22              NO    /u01/app/oracle/oradata/ora12c/ora12cpdb1/system01.dbf
  11      Full 2287536    2019-12-11 03:25:22              NO    /u01/app/oracle/oradata/ora12c/ora12cpdb1/undotbs01.dbf
  13      Full 2287536    2019-12-11 03:25:22              NO    /u01/app/oracle/oradata/ora12c/ora12cpdb1/TPS_DATA13.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
66      Full    115.23M    DISK        00:00:14     2019-12-11 03:25:21
        BP Key: 66   Status: AVAILABLE  Compressed: YES  Tag: TAG20191211T032356
        Piece Name: /u01/rman_backup/db_fullbackup_ORA12C_73_1_20191211
  List of Datafiles in backup set 66
  Container ID: 3, PDB Name: ORA12CPDB1
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  10      Full 2287525    2019-12-11 03:25:07              NO    /u01/app/oracle/oradata/ora12c/ora12cpdb1/sysaux01.dbf
  12      Full 2287525    2019-12-11 03:25:07              NO    /u01/app/oracle/oradata/ora12c/ora12cpdb1/users01.dbf
  14      Full 2287525    2019-12-11 03:25:07              NO    /u01/app/oracle/oradata/ora12c/ora12cpdb1/TPS_DATA14.dbf

no backup of archived log for thread 1 with sequence 4 and starting SCN of 2287678 found to restore ##提示说用到归档日志4,但是我们的归档日志4已经删除了,所以不能恢复到5
List of Archived Log Copies for database with db_unique_name ORA12C
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - -------------------
58      1    2       A 2019-12-11 02:43:10
        Name: /u01/app/oracle/archive_log/1_2_1026700907.dbf

59      1    3       A 2019-12-11 03:25:35
        Name: /u01/app/oracle/archive_log/1_3_1026700907.dbf

recovery will be done up to SCN 2287682
Media recovery start SCN is 2287488
Recovery must be done beyond SCN 2287536 to clear datafile fuzziness
Finished restore at 2019-12-11 03:40:22

相关推荐