一、概括

其實這個問題確切來說由於自己的疏忽所造成，之前做過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 error

HPUX-ia64 Error: 2: No such file or directory

Additional information: 4

Additional information: 105473

Additional information: 433152

檢視alert日誌

ORA-1589 signalled during: alter database open...

Wed Oct 29 08:15:21 2014

alter database open resetlogs

Wed Oct 29 08:15:21 2014

Errors in file /oracle/admin/pmssd/udump/pmssd1_ora_11597.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/dev/vgdb/rredo1_1b_256m'

ORA-27037: unable to obtain file status

HPUX-ia64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 1 thread 1: '/dev/vgdb/rredo1_1ax`_256m'

ORA-27037: unable to obtain file status

HPUX-ia64 Error: 2: No such file or directory

Additional information: 3

Wed 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 2014

alter database open resetlogs

RESETLOGS after incomplete recovery UNTIL CHANGE 783268

Errors 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 1

ORA-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.859438553

ORA-15001: diskgroup "DATA" does not exist or is not mounted

ORA-15077: could not locate ASM instance serving a required diskgroup

ORA-29701: unable to connect to Cluster Synchronization Service

ORA-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.859438547

ORA-15001: diskgroup "DATA" does not exist or is not mounted

ORA-15077: could not locate ASM instance serving a required diskgroup

ORA-29701: unable to connect to Cluster Synchronization Service

Errors 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 ASM

WARNING: Cannot delete Oracle managed file +DATA/racdb/onlinelog/group_4.275.859442003

WARNING: ASM communication error: op 16 state 0x40 (15077)

ERROR: slave communication error with ASM

WARNING: Cannot delete Oracle managed file +DATA/racdb/onlinelog/group_4.271.859442011

Wed Oct 29 09:28:15 2014

Clearing online redo logfile 4 complete

Resetting resetlogs activation ID 857045326 (0x3315794e)

Online log /oracle/oradata/racdb/RACDB/onlinelog/o1_mf_1_b50jmr0p_.log: Thread 1 Group 1 was previously cleared

Online log /oracle/backup/archive_racdb/RACDB/onlinelog/o1_mf_1_b50jmsl4_.log: Thread 1 Group 1 was previously cleared

Online log /oracle/oradata/racdb/RACDB/onlinelog/o1_mf_2_b50jn5rw_.log: Thread 1 Group 2 was previously cleared

Online log /oracle/backup/archive_racdb/RACDB/onlinelog/o1_mf_2_b50jn6jm_.log: Thread 1 Group 2 was previously cleared

Online log /oracle/oradata/racdb/RACDB/onlinelog/o1_mf_3_b50jnkqn_.log: Thread 2 Group 3 was previously cleared

Online log /oracle/backup/archive_racdb/RACDB/onlinelog/o1_mf_3_b50jnltx_.log: Thread 2 Group 3 was previously cleared

Online log /oracle/oradata/racdb/RACDB/onlinelog/o1_mf_4_b50jnz10_.log: Thread 2 Group 4 was previously cleared

Online log /oracle/backup/archive_racdb/RACDB/onlinelog/o1_mf_4_b50jo0jb_.log: Thread 2 Group 4 was previously cleared

Wed Oct 29 09:28:16 2014

Setting recovery target incarnation to 2

Wed Oct 29 09:28:17 2014

Assigning activation ID 859863386 (0x3340795a)

LGWR: STARTING ARCH PROCESSES

Wed Oct 29 09:28:17 2014

ARC0 started with pid=20, OS id=3924

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

Thread 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_.log

Successful open of redo thread 1

ARC0: STARTING ARCH PROCESSES

Wed Oct 29 09:28:17 2014

ARC1 started with pid=21, OS id=3926

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Wed Oct 29 09:28:17 2014

SMON: enabling cache recovery

Wed Oct 29 09:28:17 2014

ARC2 started with pid=22, OS id=3928

Wed Oct 29 09:28:17 2014

ARC3 started with pid=23, OS id=3930

ARC1: Archival started

ARC2: Archival started

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Redo thread 2 internally disabled at seq 1 (CKPT)

ARC0: Archiving disabled thread 2 sequence 1

Archived 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 beginning

File #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 2014

Errors 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 file

ORA-01110: data file 201: '+DATA/racdb/tempfile/temp.286.859438605'

ORA-17503: ksfdopn:2 Failed to open file +DATA/racdb/tempfile/temp.286.859438605

ORA-15001: diskgroup "DATA" does not exist or is not mounted

ORA-15077: could not locate ASM instance serving a required diskgroup

ORA-29701: unable to connect to Cluster Synchronization Service

Errors in file /oracle/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_dbw0_3814.trc:

ORA-01186: file 201 failed verification tests

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

ORA-01110: data file 201: '+DATA/racdb/tempfile/temp.286.859438605'

File 201 not verified due to error ORA-01157

Dictionary check complete

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Re-creating tempfile +DATA/racdb/tempfile/temp.286.859438605 as /oracle/oradata/racdb/RACDB/datafile/o1_mf_temp_b50jor8m_.tmp

Database Characterset is ZHS16GBK

Wed Oct 29 09:28:27 2014

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Wed Oct 29 09:28:31 2014

QMNC started with pid=24, OS id=3937

LOGSTDBY: Validating controlfile with logical metadata

LOGSTDBY: Validation complete

Wed Oct 29 09:28:42 2014

db_recovery_file_dest_size of 5727 MB is 13.97% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

Wed Oct 29 09:28:45 2014

Starting background process CJQ0

Wed Oct 29 09:28:45 2014

CJQ0 started with pid=27, OS id=3957

Wed Oct 29 09:28:46 2014

Completed: 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 resetlogs

RESETLOGS after incomplete recovery UNTIL CHANGE 783268

Errors 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 1

ORA-00312: online log 1 thread 1: '/oracle/oradata/racdb/redo1_b.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 1 thread 1: '/oracle/oradata/racdb/redo1_a.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors 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 1

ORA-00312: online log 2 thread 1: '/oracle/oradata/racdb/redo2_b.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

……………………………..

Additional information: 3

ORA-00312: online log 2 thread 1: '/oracle/oradata/racdb/redo2_a.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Clearing online redo logfile 2 /oracle/oradata/racdb/redo2_a.log

Clearing online log 2 of thread 1 sequence number 30

Errors 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 1

ORA-00312: online log 2 thread 1: '/oracle/oradata/racdb/redo2_b.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

………………………………..

Clearing online redo logfile 4 complete

Resetting resetlogs activation ID 857045326 (0x3315794e)

Online log /oracle/oradata/racdb/redo1_a.log: Thread 1 Group 1 was previously cleared

Online log /oracle/oradata/racdb/redo1_b.log: Thread 1 Group 1 was previously cleared

Online log /oracle/oradata/racdb/redo2_a.log: Thread 1 Group 2 was previously cleared

Online log /oracle/oradata/racdb/redo2_b.log: Thread 1 Group 2 was previously cleared

Online log /oracle/oradata/racdb/redo3_a.log: Thread 2 Group 3 was previously cleared

Online log /oracle/oradata/racdb/redo3_b.log: Thread 2 Group 3 was previously cleared

Online log /oracle/oradata/racdb/redo4_a.log: Thread 2 Group 4 was previously cleared

Online log /oracle/oradata/racdb/redo4_b.log: Thread 2 Group 4 was previously cleared

Wed Oct 29 09:18:08 2014

Setting recovery target incarnation to 2

Wed Oct 29 09:18:09 2014

Assigning activation ID 859838345 (0x33401789)

LGWR: STARTING ARCH PROCESSES

Wed Oct 29 09:18:09 2014

ARC0 started with pid=20, OS id=3730

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Thread 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.log

Successful open of redo thread 1

Wed Oct 29 09:18:10 2014

ARC1 started with pid=21, OS id=3732

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Wed Oct 29 09:18:10 2014

SMON: enabling cache recovery

Wed Oct 29 09:18:10 2014

ARC2 started with pid=22, OS id=3734

Wed Oct 29 09:18:10 2014

ARC3 started with pid=23, OS id=3736

ARC1: Archival started

ARC2: Archival started

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

ARC0: Becoming the 'no FAL' ARCH

ARC0: Becoming the 'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

Redo thread 2 internally disabled at seq 1 (CKPT)

ARC0: Archiving disabled thread 2 sequence 1

Archived 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 beginning

File #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 2014

Errors 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 file

ORA-01110: data file 201: '+DATA/racdb/tempfile/temp.286.859438605'

ORA-17503: ksfdopn:2 Failed to open file +DATA/racdb/tempfile/temp.286.859438605

ORA-15001: diskgroup "DATA" does not exist or is not mounted

ORA-15077: could not locate ASM instance serving a required diskgroup

ORA-29701: unable to connect to Cluster Synchronization Service

Errors in file /oracle/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_dbw0_3455.trc:

ORA-01186: file 201 failed verification tests

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

ORA-01110: data file 201: '+DATA/racdb/tempfile/temp.286.859438605'

File 201 not verified due to error ORA-01157

Dictionary check complete

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

Wed Oct 29 09:18:23 2014

SMON: enabling tx recovery

Re-creating tempfile +DATA/racdb/tempfile/temp.286.859438605 as /oracle/oradata/racdb/RACDB/datafile/o1_mf_temp_b50j2zdh_.tmp

Database Characterset is ZHS16GBK

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Wed Oct 29 09:18:32 2014

QMNC started with pid=24, OS id=3743

Wed Oct 29 09:18:33 2014

LOGSTDBY: Validating controlfile with logical metadata

LOGSTDBY: Validation complete

Wed Oct 29 09:18:45 2014

Starting background process CJQ0

Wed Oct 29 09:18:45 2014

CJQ0 started with pid=27, OS id=3763

Wed Oct 29 09:18:45 2014

Completed: alter database open resetlogs

三、總結
由上可知，當Oracle資料庫在恢復時（也就是執行’alter database open resetlogs’時），如果之前Oracle資料庫的儲存方式為ASM模式，開啟資料庫時資料庫會自動在相應目錄建立redo日誌，當之前Oracle資料庫的儲存方式為裸裝置、檔案模式，開啟資料庫時資料庫會查詢原裸裝置或者目錄，並建立原有目錄結構，無法建立時則報錯。如需更改，需手動設定。可見ASM自動管理是很有好處的，當然，作為技術人員，我們可以更嚴謹一些（自動的東西有時也不靠譜），在執行open之前，修改redo日誌目錄。在做hpux之前，我已反覆檢視操作命令及相關文件，總會百密一疏，當然，由於之前恢復的是ASM模式下並無此問題，再者應變、應急處理能力還有待提高。

【RMAN】RMAN_RAC恢復至單機時redo日誌引發的“恐慌”

相關文章