RMAN_RAC恢复至单机时redo日志引发的“恐慌”

一、概括其实这个问题确切来说由于自己的疏忽所造成，之前做过RAC到单机的恢复实验，生产环境并无涉足，当时测试环境为RedHat5.4+Oracle11g+RAC，存储方式为ASM；生产环境为hpux+Oracle10g+RAC,存储方式为裸设备；就目前来看，跟数据文件的存储方式有关。二、 Hpux中，recover完成，执行’alter database open resetlogs’时报以下错误SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-19502: write error on file "/dev/vgdb/rredo2_2a_256m", blockno 105473（blocksize=1024）ORA-27072: File I/O errorHPUX-ia64 Error: 2: No such file or directoryAdditional information: 4Additional information: 105473Additional information: 433152
  查看alert日志 ORA-1589 signalled during: alter database open...Wed Oct 29 08:15:21 2014alter database open resetlogsWed Oct 29 08:15:21 2014Errors in file /oracle/admin/pmssd/udump/pmssd1_ora_11597.trc:ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: "/dev/vgdb/rredo1_1b_256m"ORA-27037: unable to obtain file statusHPUX-ia64 Error: 2: No such file or directoryAdditional information: 3ORA-00312: online log 1 thread 1: "/dev/vgdb/rredo1_1ax`_256m"ORA-27037: unable to obtain file statusHPUX-ia64 Error: 2: No such file or directoryAdditional information: 3Wed Oct 29 08:15:21 2014
  查看视图v$logfile SQL> select * from v$logfile;   GROUP# STATUS TYPE MEMBER IS_---------- ------- ------- ---------------------------- --- 1 ONLINE /dev/vgdb/rredo1_1a_256m NO 2 ONLINE /dev/vgdb/rredo1_2a_256m NO 3 ONLINE /dev/vgdb/rredo2_1a_256m NO 4 ONLINE /dev/vgdb/rredo2_2a_256m NO 1 ONLINE /dev/vgdb/rredo1_1b_256m NO 2 ONLINE /dev/vgdb/rredo1_2b_256m NO 3 ONLINE /dev/vgdb/rredo2_1b_256m NO 4 ONLINE /dev/vgdb/rredo2_2b_256m NO 5 ONLINE /dev/vgdb/rredo1_3a_256m NO 5 ONLINE /dev/vgdb/rredo1_3b_256m NO 6 ONLINE /dev/vgdb/rredo2_3a_256m NO 6 ONLINE /dev/vgdb/rredo2_3b_256m NO
  根下目录dev为设置目录，我们需要修改redo日志目录，以完成数据库open操作，语句如下： SQL> alter database rename file "/dev/vgdb/rredo1_1a_256m" to "/oracle/oradata/pmssd/redo01.log";
  我并没有按照以上语句操作，而是重新创建了控制文件，这个问题是自己犯二了。修改完redo日志目录后就可以把数据库open了。下面是我在linux ASM模式下的操作（’alter database open resetlogs’）。如以下日志，数据库自动重建了redo日志，并指定到了相应目录 Wed Oct 29 09:27:19 2014alter database open resetlogsRESETLOGS after incomplete recovery UNTIL CHANGE 783268Errors in file /oracle/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_ora_3900.trc:ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: "+DATA/racdb/onlinelog/group_1.257.859438553"ORA-17503: ksfdopn:2 Failed to open file +DATA/racdb/onlinelog/group_1.257.859438553ORA-15001: diskgroup "DATA" does not exist or is not mountedORA-15077: could not locate ASM instance serving a required diskgroupORA-29701: unable to connect to Cluster Synchronization ServiceORA-00312: online log 1 thread 1: "+DATA/racdb/onlinelog/group_1.273.859438547"ORA-17503: ksfdopn:2 Failed to open file +DATA/racdb/onlinelog/group_1.273.859438547ORA-15001: diskgroup "DATA" does not exist or is not mountedORA-15077: could not locate ASM instance serving a required diskgroupORA-29701: unable to connect to Cluster Synchronization ServiceErrors in file /oracle/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_ora_3900.trc:…………………………………………………………….WARNING: ASM communication error: op 16 state 0x40 （15077）ERROR: slave communication error with ASMWARNING: Cannot delete Oracle managed file +DATA/racdb/onlinelog/group_4.275.859442003WARNING: ASM communication error: op 16 state 0x40 （15077）ERROR: slave communication error with ASMWARNING: Cannot delete Oracle managed file +DATA/racdb/onlinelog/group_4.271.859442011Wed Oct 29 09:28:15 2014Clearing online redo logfile 4 completeResetting resetlogs activation ID 857045326 （0x3315794e）Online log /oracle/oradata/racdb/RACDB/onlinelog/o1_mf_1_b50jmr0p_.log: Thread 1 Group 1 was previously clearedOnline log /oracle/backup/archive_racdb/RACDB/onlinelog/o1_mf_1_b50jmsl4_.log: Thread 1 Group 1 was previously clearedOnline log /oracle/oradata/racdb/RACDB/onlinelog/o1_mf_2_b50jn5rw_.log: Thread 1 Group 2 was previously clearedOnline log /oracle/backup/archive_racdb/RACDB/onlinelog/o1_mf_2_b50jn6jm_.log: Thread 1 Group 2 was previously clearedOnline log /oracle/oradata/racdb/RACDB/onlinelog/o1_mf_3_b50jnkqn_.log: Thread 2 Group 3 was previously clearedOnline log /oracle/backup/archive_racdb/RACDB/onlinelog/o1_mf_3_b50jnltx_.log: Thread 2 Group 3 was previously clearedOnline log /oracle/oradata/racdb/RACDB/onlinelog/o1_mf_4_b50jnz10_.log: Thread 2 Group 4 was previously clearedOnline log /oracle/backup/archive_racdb/RACDB/onlinelog/o1_mf_4_b50jo0jb_.log: Thread 2 Group 4 was previously clearedWed Oct 29 09:28:16 2014Setting recovery target incarnation to 2Wed Oct 29 09:28:17 2014Assigning activation ID 859863386 （0x3340795a）LGWR: STARTING ARCH PROCESSESWed Oct 29 09:28:17 2014ARC0 started with pid=20, OS id=3924ARC0: Archival startedLGWR: STARTING ARCH PROCESSES COMPLETEThread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: /oracle/oradata/racdb/RACDB/onlinelog/o1_mf_1_b50jmr0p_.log Current log# 1 seq# 1 mem# 1: /oracle/backup/archive_racdb/RACDB/onlinelog/o1_mf_1_b50jmsl4_.logSuccessful open of redo thread 1ARC0: STARTING ARCH PROCESSESWed Oct 29 09:28:17 2014ARC1 started with pid=21, OS id=3926MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setWed Oct 29 09:28:17 2014SMON: enabling cache recoveryWed Oct 29 09:28:17 2014ARC2 started with pid=22, OS id=3928Wed Oct 29 09:28:17 2014ARC3 started with pid=23, OS id=3930ARC1: Archival startedARC2: Archival startedARC1: Becoming the "no FAL" ARCHARC1: Becoming the "no SRL" ARCHARC2: Becoming the heartbeat ARCHARC3: Archival startedARC0: STARTING ARCH PROCESSES COMPLETERedo thread 2 internally disabled at seq 1 （CKPT）ARC0: Archiving disabled thread 2 sequence 1Archived Log entry 27 added for thread 2 sequence 1 ID 0x0 dest 1:[3900] Successfully onlined Undo Tablespace 2.Undo initialization finished serial:0 start:4182896 end:4184866 diff:1970 （19 seconds）Dictionary check beginningFile #7 is offline, but is part of an online tablespace.data file 7: "+DATA/racdb/datafile/test2.260.859451699"File #9 is offline, but is part of an online tablespace.data file 9: "+DATA/racdb/datafile/test4.258.859451727"Wed Oct 29 09:28:24 2014Errors in file /oracle/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_dbw0_3814.trc:ORA-01157: cannot identify/lock data file 201 - see DBWR trace fileORA-01110: data file 201: "+DATA/racdb/tempfile/temp.286.859438605"ORA-17503: ksfdopn:2 Failed to open file +DATA/racdb/tempfile/temp.286.859438605ORA-15001: diskgroup "DATA" does not exist or is not mountedORA-15077: could not locate ASM instance serving a required diskgroupORA-29701: unable to connect to Cluster Synchronization ServiceErrors in file /oracle/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_dbw0_3814.trc:ORA-01186: file 201 failed verification testsORA-01157: cannot identify/lock data file 201 - see DBWR trace fileORA-01110: data file 201: "+DATA/racdb/tempfile/temp.286.859438605"File 201 not verified due to error ORA-01157Dictionary check completeVerifying file header compatibility for 11g tablespace encryption..Verifying 11g file header compatibility for tablespace encryption completedSMON: enabling tx recoveryRe-creating tempfile +DATA/racdb/tempfile/temp.286.859438605 as /oracle/oradata/racdb/RACDB/datafile/o1_mf_temp_b50jor8m_.tmpDatabase Characterset is ZHS16GBKWed Oct 29 09:28:27 2014No Resource Manager plan activereplication_dependency_tracking turned off （no async multimaster replication found）Starting background process QMNCWed Oct 29 09:28:31 2014QMNC started with pid=24, OS id=3937LOGSTDBY: Validating controlfile with logical metadataLOGSTDBY: Validation completeWed Oct 29 09:28:42 2014db_recovery_file_dest_size of 5727 MB is 13.97% used. This is auser-specified limit on the amount of space that will be used by thisdatabase for recovery-related files, and does not reflect the amount ofspace available in the underlying filesystem or ASM diskgroup.Wed Oct 29 09:28:45 2014Starting background process CJQ0Wed Oct 29 09:28:45 2014CJQ0 started with pid=27, OS id=3957Wed Oct 29 09:28:46 2014Completed: alter database open resetlogs
  在执行’alter database open resetlogs’之前，修改redo日志目录,open时redo日志就会在指定的目录生成。 alter database rename file "+DATA/racdb/onlinelog/group_1.273.859438547" to "/oracle/oradata/racdb/redo1_a.log";alter database rename file "+DATA/racdb/onlinelog/group_1.257.859438553" to "/oracle/oradata/racdb/redo1_b.log";alter database rename file "+DATA/racdb/onlinelog/group_2.256.859438561" to "/oracle/oradata/racdb/redo2_a.log";alter database rename file "+DATA/racdb/onlinelog/group_2.291.859438567" to "/oracle/oradata/racdb/redo2_b.log";alter database rename file "+DATA/racdb/onlinelog/group_3.277.859441989" to "/oracle/oradata/racdb/redo3_a.log";alter database rename file "+DATA/racdb/onlinelog/group_3.276.859441997" to "/oracle/oradata/racdb/redo3_b.log";alter database rename file "+DATA/racdb/onlinelog/group_4.275.859442003" to "/oracle/oradata/racdb/redo4_a.log";
  查看alert日志
lter database open resetlogsRESETLOGS after incomplete recovery UNTIL CHANGE 783268Errors in file /oracle/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_ora_3683.trc:ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: "/oracle/oradata/racdb/redo1_b.log"ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3ORA-00312: online log 1 thread 1: "/oracle/oradata/racdb/redo1_a.log"ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3Errors in file /oracle/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_ora_3683.trc:ORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: "/oracle/oradata/racdb/redo2_b.log"ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directory……………………………..Additional information: 3ORA-00312: online log 2 thread 1: "/oracle/oradata/racdb/redo2_a.log"ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3Clearing online redo logfile 2 /oracle/oradata/racdb/redo2_a.logClearing online log 2 of thread 1 sequence number 30Errors in file /oracle/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_ora_3683.trc:ORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: "/oracle/oradata/racdb/redo2_b.log"ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3………………………………..Clearing online redo logfile 4 completeResetting resetlogs activation ID 857045326 （0x3315794e）Online log /oracle/oradata/racdb/redo1_a.log: Thread 1 Group 1 was previously clearedOnline log /oracle/oradata/racdb/redo1_b.log: Thread 1 Group 1 was previously clearedOnline log /oracle/oradata/racdb/redo2_a.log: Thread 1 Group 2 was previously clearedOnline log /oracle/oradata/racdb/redo2_b.log: Thread 1 Group 2 was previously clearedOnline log /oracle/oradata/racdb/redo3_a.log: Thread 2 Group 3 was previously clearedOnline log /oracle/oradata/racdb/redo3_b.log: Thread 2 Group 3 was previously clearedOnline log /oracle/oradata/racdb/redo4_a.log: Thread 2 Group 4 was previously clearedOnline log /oracle/oradata/racdb/redo4_b.log: Thread 2 Group 4 was previously clearedWed Oct 29 09:18:08 2014Setting recovery target incarnation to 2Wed Oct 29 09:18:09 2014Assigning activation ID 859838345 （0x33401789）LGWR: STARTING ARCH PROCESSESWed Oct 29 09:18:09 2014ARC0 started with pid=20, OS id=3730ARC0: Archival startedLGWR: STARTING ARCH PROCESSES COMPLETEARC0: STARTING ARCH PROCESSESThread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: /oracle/oradata/racdb/redo1_a.log Current log# 1 seq# 1 mem# 1: /oracle/oradata/racdb/redo1_b.logSuccessful open of redo thread 1Wed Oct 29 09:18:10 2014ARC1 started with pid=21, OS id=3732MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setWed Oct 29 09:18:10 2014SMON: enabling cache recoveryWed Oct 29 09:18:10 2014ARC2 started with pid=22, OS id=3734Wed Oct 29 09:18:10 2014ARC3 started with pid=23, OS id=3736ARC1: Archival startedARC2: Archival startedARC3: Archival startedARC0: STARTING ARCH PROCESSES COMPLETEARC0: Becoming the "no FAL" ARCHARC0: Becoming the "no SRL" ARCHARC2: Becoming the heartbeat ARCHRedo thread 2 internally disabled at seq 1 （CKPT）ARC0: Archiving disabled thread 2 sequence 1Archived Log entry 27 added for thread 2 sequence 1 ID 0x0 dest 1:Wed Oct 29 09:18:22 2014[3683] Successfully onlined Undo Tablespace 2.Undo initialization finished serial:0 start:3581386 end:3583566 diff:2180 （21 seconds）Dictionary check beginningFile #7 is offline, but is part of an online tablespace.data file 7: "+DATA/racdb/datafile/test2.260.859451699"File #9 is offline, but is part of an online tablespace.data file 9: "+DATA/racdb/datafile/test4.258.859451727"Wed Oct 29 09:18:23 2014Errors in file /oracle/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_dbw0_3455.trc:ORA-01157: cannot identify/lock data file 201 - see DBWR trace fileORA-01110: data file 201: "+DATA/racdb/tempfile/temp.286.859438605"ORA-17503: ksfdopn:2 Failed to open file +DATA/racdb/tempfile/temp.286.859438605ORA-15001: diskgroup "DATA" does not exist or is not mountedORA-15077: could not locate ASM instance serving a required diskgroupORA-29701: unable to connect to Cluster Synchronization ServiceErrors in file /oracle/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_dbw0_3455.trc:ORA-01186: file 201 failed verification testsORA-01157: cannot identify/lock data file 201 - see DBWR trace fileORA-01110: data file 201: "+DATA/racdb/tempfile/temp.286.859438605"File 201 not verified due to error ORA-01157Dictionary check completeVerifying file header compatibility for 11g tablespace encryption..Verifying 11g file header compatibility for tablespace encryption completedWed Oct 29 09:18:23 2014SMON: enabling tx recoveryRe-creating tempfile +DATA/racdb/tempfile/temp.286.859438605 as /oracle/oradata/racdb/RACDB/datafile/o1_mf_temp_b50j2zdh_.tmpDatabase Characterset is ZHS16GBKNo Resource Manager plan activereplication_dependency_tracking turned off （no async multimaster replication found）Starting background process QMNCWed Oct 29 09:18:32 2014QMNC started with pid=24, OS id=3743Wed Oct 29 09:18:33 2014LOGSTDBY: Validating controlfile with logical metadataLOGSTDBY: Validation completeWed Oct 29 09:18:45 2014Starting background process CJQ0Wed Oct 29 09:18:45 2014CJQ0 started with pid=27, OS id=3763Wed Oct 29 09:18:45 2014Completed: alter database open resetlogs
三、总结
由上可知，当Oracle数据库在恢复时（也就是执行’alter database open resetlogs’时），如果之前Oracle数据库的存储方式为ASM模式，打开数据库时数据库会自动在相应目录创建redo日志，当之前Oracle数据库的存储方式为裸设备、文件模式，打开数据库时数据库会查找原裸设备或者目录，并创建原有目录结构，无法创建时则报错。如需更改，需手动设置。可见ASM自动管理是很有好处的，当然，作为技术人员，我们可以更严谨一些（自动的东西有时也不靠谱），在执行open之前，修改redo日志目录。在做hpux之前，我已反复查看操作命令及相关文档，总会百密一疏，当然，由于之前恢复的是ASM模式下并无此问题，再者应变、应急处理能力还有待提高。--------------------------------------推荐阅读 --------------------------------------RMAN 配置归档日志删除策略 http://www.linuxidc.com/Linux/2013-11/92670.htmOracle基础教程之通过RMAN复制数据库 http://www.linuxidc.com/Linux/2013-07/87072.htmRMAN备份策略制定参考内容 http://www.linuxidc.com/Linux/2013-03/81695.htmRMAN备份学习笔记 http://www.linuxidc.com/Linux/2013-03/81892.htmOracle数据库备份加密 RMAN加密 http://www.linuxidc.com/Linux/2013-03/80729.htm--------------------------------------分割线 --------------------------------------更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址