RAC 环境下的恢复问题 -- 恢复时应用归档日志的问题

来源：互联网发布：故宫的淘宝店编辑：程序博客网时间：2024/06/05 02:53

备注：
之前面试的时候，有人问过。说RAC的日志，两个节点的日志序列是不一样的（这个没错，各个节点有各个节点的归档日志）。
问的问题意思大概是下面的两种之一：
比如RAC1的归档日志到第10个日志，RAC2的归档日志到第3个日志。
问：如果RAC出现问题了，恢复的时候需要日志。这个时候用rman去恢复日志，恢复到最小序列的那个日志（要恢复到第3个日志即可），不然会出错。不一致？
或者
问：如果RAC出现问题了，恢复的时候需要用归档日志进行恢复，是不是recover的时候，只能用到最小的那个日志，比如序列号为3个那个归档日志？

考官认为是，recover的时候，只能recover到序列号为3个日志。否则会出现不一致的问题。

当时感觉考官说的不对，就没有和考官过多地讨论这个问题。今天在RAC下验证下，用事实说话。

-------------------------------------------------------------------------------------------------------

-- rac1，在RAC1上创建一个表空间TEST，创建一个表t，插入数据

SYS@RACDB1>create tablespace test datafile '+DATA' size 20M autoextend on;Tablespace created.SYS@RACDB1>create table t(id number);Table created.SYS@RACDB1>insert into t values(1);1 row created.SYS@RACDB1>insert into t values(2);1 row created.SYS@RACDB1>insert into t values(3);1 row created.SYS@RACDB1>insert into t values(4);1 row created.SYS@RACDB1>insert into t values(5);1 row created.SYS@RACDB1>commit;Commit complete.SYS@RACDB1>select * from t;        ID----------         1         2         3         4         5

-- RAC1 的归档日志

SYS@RACDB1>archive log listDatabase log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     3Next log sequence to archive   4Current log sequence           4SYS@RACDB1>alter system switch logfile;System altered.SYS@RACDB1>/System altered.SYS@RACDB1>/System altered.SYS@RACDB1>/System altered.SYS@RACDB1>/System altered.SYS@RACDB1>archive log listDatabase log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     8Next log sequence to archive   9Current log sequence           9SYS@RACDB1>SYS@RACDB1>select group#,thread#,sequence#,status from v$log;    GROUP#    THREAD#  SEQUENCE# STATUS---------- ---------- ---------- ----------------         1          1          9 CURRENT         2          1          8 INACTIVE         3          2          3 CURRENT         4          2          2 ACTIVE

-- rac2，在RAC2上查询数据，并查看归档日志信息

SYS@RACDB2>select * from t;        ID----------         1         2         3         4         5SYS@RACDB2>archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     1Next log sequence to archive   2Current log sequence           2SYS@RACDB2>archive log listDatabase log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     2Next log sequence to archive   3Current log sequence           3SYS@RACDB2>SYS@RACDB2>select group#,thread#,sequence#,status from v$log;    GROUP#    THREAD#  SEQUENCE# STATUS---------- ---------- ---------- ----------------         1          1          9 CURRENT         2          1          8 INACTIVE         3          2          3 CURRENT         4          2          2 ACTIVE

-- backup ，在RMAN下备份。注意，rman下test是保留字，需要加双引号

RMAN> report schema;Report of database schema for database with db_unique_name RACDBList of Permanent Datafiles===========================File Size(MB) Tablespace           RB segs Datafile Name---- -------- -------------------- ------- ------------------------1    720      SYSTEM               ***     +DATA/racdb/datafile/system.256.9612412672    570      SYSAUX               ***     +DATA/racdb/datafile/sysaux.257.9612412673    70       UNDOTBS1             ***     +DATA/racdb/datafile/undotbs1.258.9612412674    5        USERS                ***     +DATA/racdb/datafile/users.259.9612412675    313      EXAMPLE              ***     +DATA/racdb/datafile/example.264.9612413596    25       UNDOTBS2             ***     +DATA/racdb/datafile/undotbs2.265.9612414277    20       TEST                 ***     +DATA/racdb/datafile/test.269.961248715List of Temporary Files=======================File Size(MB) Tablespace           Maxsize(MB) Tempfile Name---- -------- -------------------- ----------- --------------------1    20       TEMP                 32767       +DATA/racdb/tempfile/temp.263.961241355RMAN> backup tablespace TEST;RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-00558: error encountered while parsing input commandsRMAN-01009: syntax error: found "test": expecting one of: "double-quoted-string, identifier, single-quoted-string"RMAN-01007: at line 1 column 19 file: standard inputRMAN> backup tablespace "TEST";Starting backup at 28-NOV-17allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=36 instance=RACDB1 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00007 name=+DATA/racdb/datafile/test.269.961248715channel ORA_DISK_1: starting piece 1 at 28-NOV-17channel ORA_DISK_1: finished piece 1 at 28-NOV-17piece handle=+FRA/racdb/backupset/2017_11_28/nnndf0_tag20171128t133608_0.263.961248969 tag=TAG20171128T133608 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 28-NOV-17RMAN>

-- 破坏掉ASM磁盘组上的文件，然后启动RAC1和RAC2节点。破坏的时候，要把DB关掉，这个和单机不太一样。

ASMCMD> lsofDB_Name  Instance_Name  Path                                           +ASM     +ASM1          +ocr.255.4294967295                            RACDB    RACDB1         +data/racdb/controlfile/current.260.961241349  RACDB    RACDB1         +data/racdb/datafile/example.264.961241359     RACDB    RACDB1         +data/racdb/datafile/sysaux.257.961241267      RACDB    RACDB1         +data/racdb/datafile/system.256.961241267      RACDB    RACDB1         +data/racdb/datafile/test.269.961248715        RACDB    RACDB1         +data/racdb/datafile/undotbs1.258.961241267    RACDB    RACDB1         +data/racdb/datafile/undotbs2.265.961241427    RACDB    RACDB1         +data/racdb/datafile/users.259.961241267       RACDB    RACDB1         +data/racdb/onlinelog/group_1.261.961241351    RACDB    RACDB1         +data/racdb/onlinelog/group_2.262.961241353    RACDB    RACDB1         +data/racdb/onlinelog/group_3.266.961241447    RACDB    RACDB1         +data/racdb/onlinelog/group_4.267.961241447    RACDB    RACDB1         +data/racdb/tempfile/temp.263.961241355        ASMCMD> rm -r test.269.961248715You may delete multiple files and/or directories. Are you sure? (y/n) yORA-15032: not all alterations performedORA-15028: ASM file '+DATA/RACDB/DATAFILE/test.269.961248715' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)ASMCMD> rm -rf test.269.961248715ORA-15032: not all alterations performedORA-15028: ASM file '+DATA/RACDB/DATAFILE/test.269.961248715' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)ASMCMD> rm -rf test.269.961248715ASMCMD> lsofDB_Name  Instance_Name  Path                 +ASM     +ASM1          +ocr.255.4294967295  ASMCMD> [oracle@host01 ~]$ oerr ORA 1502815028, 00000, "ASM file '%s' not dropped; currently being accessed"// *Cause:  An attempt was made to drop an ASM file, but the file was//          being accessed by one or more clients and therefore could //          not be dropped.// *Action: Stop all clients that are using this file and retry the drop //          command. Query the V$ASM_CLIENT fixed view in an ASM instance//          or use ASMCMD LSOF to list active clients.//[oracle@host01 ~]$

-- 启动，并进行restore recover。注意，RAC环境下，是不支持list failure的

SYS@RACDB1>startupORACLE instance started.Total System Global Area 1071333376 bytesFixed Size                  1349732 bytesVariable Size             683673500 bytesDatabase Buffers          381681664 bytesRedo Buffers                4628480 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 7 - see DBWR trace fileORA-01110: data file 7: '+DATA/racdb/datafile/test.269.961248715'SYS@RACDB1>

RMAN> list failure;using target database control file instead of recovery catalogRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of list command at 11/28/2017 13:45:44RMAN-05533: LIST FAILURE is not supported on RAC databaseRMAN>

RMAN> restore datafile 7;Starting restore at 28-NOV-17allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=39 instance=RACDB1 device type=DISKchannel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00007 to +DATA/racdb/datafile/test.269.961248715channel ORA_DISK_1: reading from backup piece +FRA/racdb/backupset/2017_11_28/nnndf0_tag20171128t133608_0.263.961248969channel ORA_DISK_1: piece handle=+FRA/racdb/backupset/2017_11_28/nnndf0_tag20171128t133608_0.263.961248969 tag=TAG20171128T133608channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:03Finished restore at 28-NOV-17RMAN> recover datafile 7;Starting recover at 28-NOV-17using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 28-NOV-17RMAN>

-- 以上的recover过程，不需要归档日志。直接用online redo就recover了。下面测试要使用归档日志。

---再次测试，还用之前的备份，插入数据，多切换几次redo，这样恢复的时候，就会用到archived log了。

RAC1 上插入大量数据，并切换日志

SYS@RACDB1>/5242880 rows created.SYS@RACDB1>select count(*) from t;10485760 rows created.SYS@RACDB1>  COUNT(*)----------  20971520SYS@RACDB1>SYS@RACDB1>archive log listDatabase log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     15Next log sequence to archive   16Current log sequence           16SYS@RACDB1>

-- RAC2 上的情况

SYS@RACDB2>select count(*) from t;  COUNT(*)----------  20971520SYS@RACDB2>archive log listDatabase log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     5Next log sequence to archive   6Current log sequence           6SYS@RACDB2>

-- 查看都有那些文件，然后关闭db后删除文件

ASMCMD> lsofDB_Name  Instance_Name  Path                                           +ASM     +ASM1          +ocr.255.4294967295                            RACDB    RACDB1         +data/racdb/controlfile/current.260.961241349  RACDB    RACDB1         +data/racdb/datafile/example.264.961241359     RACDB    RACDB1         +data/racdb/datafile/sysaux.257.961241267      RACDB    RACDB1         +data/racdb/datafile/system.256.961241267      RACDB    RACDB1         +data/racdb/datafile/test.269.961249627        RACDB    RACDB1         +data/racdb/datafile/undotbs1.258.961241267    RACDB    RACDB1         +data/racdb/datafile/undotbs2.265.961241427    RACDB    RACDB1         +data/racdb/datafile/users.259.961241267       RACDB    RACDB1         +data/racdb/onlinelog/group_1.261.961241351    RACDB    RACDB1         +data/racdb/onlinelog/group_2.262.961241353    RACDB    RACDB1         +data/racdb/onlinelog/group_3.266.961241447    RACDB    RACDB1         +data/racdb/onlinelog/group_4.267.961241447    RACDB    RACDB1         +data/racdb/tempfile/temp.263.961241355        ASMCMD> lsofDB_Name  Instance_Name  Path                 +ASM     +ASM1          +ocr.255.4294967295  ASMCMD> rm -rf test.269.961249627ASMCMD>

-- 启动RAC1和RAC2节点

SYS@RACDB1>startupORACLE instance started.Total System Global Area 1071333376 bytesFixed Size                  1349732 bytesVariable Size             683673500 bytesDatabase Buffers          381681664 bytesRedo Buffers                4628480 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 7 - see DBWR trace fileORA-01110: data file 7: '+DATA/racdb/datafile/test.269.961249627'SYS@RACDB1>SYS@RACDB2>startupORACLE instance started.Total System Global Area 1071333376 bytesFixed Size                  1349732 bytesVariable Size             683673500 bytesDatabase Buffers          381681664 bytesRedo Buffers                4628480 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 7 - see DBWR trace fileORA-01110: data file 7: '+DATA/racdb/datafile/test.269.961249627'SYS@RACDB2>

-- 进行restore recover，可以看到在recover的时候，节点1和节点2产生的归档日志，都是需要的，而不是恢复到归档日志顺序最小的那个时刻。

RMAN> restore datafile 7;Starting restore at 28-NOV-17using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=39 instance=RACDB1 device type=DISKchannel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00007 to +DATA/racdb/datafile/test.269.961249627channel ORA_DISK_1: reading from backup piece +FRA/racdb/backupset/2017_11_28/nnndf0_tag20171128t133608_0.263.961248969channel ORA_DISK_1: piece handle=+FRA/racdb/backupset/2017_11_28/nnndf0_tag20171128t133608_0.263.961248969 tag=TAG20171128T133608channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:03Finished restore at 28-NOV-17RMAN> recover datafile 7;Starting recover at 28-NOV-17using channel ORA_DISK_1starting media recoveryarchived log for thread 1 with sequence 9 is already on disk as file +FRA/racdb/archivelog/2017_11_28/thread_1_seq_9.264.961249403archived log for thread 1 with sequence 10 is already on disk as file +FRA/racdb/archivelog/2017_11_28/thread_1_seq_10.267.961249671archived log for thread 1 with sequence 11 is already on disk as file +FRA/racdb/archivelog/2017_11_28/thread_1_seq_11.269.961249797archived log for thread 1 with sequence 12 is already on disk as file +FRA/racdb/archivelog/2017_11_28/thread_1_seq_12.270.961249799archived log for thread 1 with sequence 13 is already on disk as file +FRA/racdb/archivelog/2017_11_28/thread_1_seq_13.271.961249807archived log for thread 1 with sequence 14 is already on disk as file +FRA/racdb/archivelog/2017_11_28/thread_1_seq_14.272.961249811archived log for thread 1 with sequence 15 is already on disk as file +FRA/racdb/archivelog/2017_11_28/thread_1_seq_15.273.961249817archived log for thread 1 with sequence 16 is already on disk as file +FRA/racdb/archivelog/2017_11_28/thread_1_seq_16.276.961249957archived log for thread 2 with sequence 3 is already on disk as file +FRA/racdb/archivelog/2017_11_28/thread_2_seq_3.265.961249669archived log for thread 2 with sequence 4 is already on disk as file +FRA/racdb/archivelog/2017_11_28/thread_2_seq_4.266.961249671archived log for thread 2 with sequence 5 is already on disk as file +FRA/racdb/archivelog/2017_11_28/thread_2_seq_5.268.961249673archived log for thread 2 with sequence 6 is already on disk as file +FRA/racdb/archivelog/2017_11_28/thread_2_seq_6.274.961249949archived log for thread 2 with sequence 7 is already on disk as file +FRA/racdb/archivelog/2017_11_28/thread_2_seq_7.275.961249953archived log file name=+FRA/racdb/archivelog/2017_11_28/thread_1_seq_9.264.961249403 thread=1 sequence=9archived log file name=+FRA/racdb/archivelog/2017_11_28/thread_2_seq_3.265.961249669 thread=2 sequence=3archived log file name=+FRA/racdb/archivelog/2017_11_28/thread_2_seq_4.266.961249671 thread=2 sequence=4archived log file name=+FRA/racdb/archivelog/2017_11_28/thread_1_seq_10.267.961249671 thread=1 sequence=10archived log file name=+FRA/racdb/archivelog/2017_11_28/thread_2_seq_5.268.961249673 thread=2 sequence=5archived log file name=+FRA/racdb/archivelog/2017_11_28/thread_1_seq_11.269.961249797 thread=1 sequence=11archived log file name=+FRA/racdb/archivelog/2017_11_28/thread_1_seq_12.270.961249799 thread=1 sequence=12archived log file name=+FRA/racdb/archivelog/2017_11_28/thread_1_seq_13.271.961249807 thread=1 sequence=13archived log file name=+FRA/racdb/archivelog/2017_11_28/thread_1_seq_14.272.961249811 thread=1 sequence=14archived log file name=+FRA/racdb/archivelog/2017_11_28/thread_1_seq_15.273.961249817 thread=1 sequence=15media recovery complete, elapsed time: 00:00:02Finished recover at 28-NOV-17RMAN>

-- 从上面的recover的过程中看到，很明显，用到一个节点上的3 4 号归档日志，用到了另一个节点上的9 11 12 13 14 15 号归档日志。而不是只用到一个节点上的3 4 号归档日志。

End。

阅读全文

0 0