利用Oracle的STANDBY技術,可以将單執行個體資料庫更新到RAC資料庫。這種方式可以有效的降低單執行個體遷移到RAC環境的停機時間。
這篇文章描述整個操作過程中碰到的錯誤。
最開始碰了幾個初始化參數設定的小錯誤,主要問題是FLASH_RECOVERY_AREA設定到ASM執行個體上導緻了問題:
SQL> startup nomount pfile=/export/home/oracle/inittest11gr1.ora
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
将DB_RECOVERY_FILE_DEST設定到本地磁盤後,問題解決。
由于SWITCHOVER過程中,設定DB_RECOVERY_FILE_DEST可能會引發bug,是以在主庫和STANDBY資料庫,都先将DB_RECOVERY_FILE_DEST關閉。
随後檢查主庫到STANDBY資料庫的遠端歸檔時發現,查詢V$ARCHIVE_DEST視圖,對應遠端歸檔的目的地出現ORA-1031錯誤。
SQL> select dest_name, status, target, destination, error
2 from v$archive_dest
3 where dest_id in (1, 2);
DEST_NAME STATUS TARGET DESTINATION ERROR
-------------------- ------ ------- -------------------------------- -------------------
LOG_ARCHIVE_DEST_1 VALID PRIMARY /data/oradata/test11g/archivelog
LOG_ARCHIVE_DEST_2 ERROR STANDBY TEST11GR ORA-01031:權限不足
這個沒有權限的錯誤是由于沒有拷貝密碼檔案造成的。将遠端的密碼檔案拷貝到本地的$ORACLE_HOME/dbs目錄下,并進行重命名,修改為orapwtest11gr1。這時必須重新開機本地的執行個體,才能加載密碼檔案。成功加載密碼檔案後,查詢V$ARCHIVE_DEST視圖,結果恢複正常:
SQL> select dest_name, status, target, destination, error
2 from v$archive_dest
3 where dest_id in (1, 2);
DEST_NAME STATUS TARGET DESTINATION ERROR
-------------------- ------ ------- -------------------------------- -----------------------
LOG_ARCHIVE_DEST_1 VALID PRIMARY /data/oradata/test11g/archivelog
LOG_ARCHIVE_DEST_2 VALID STANDBY TEST11GR
随後在啟動standby資料庫的恢複過程後,檢查不到對應的程序在工作,檢查背景alert檔案,發現出現了很多錯誤:
Mon Jul 13 16:55:17 2009
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (test11gr1)
Mon Jul 13 16:55:17 2009
MRP0 started with pid=15, OS id=8356
MRP0: Background Managed Standby Recovery process started (test11gr1)
Fast Parallel Media Recovery enabled
Mon Jul 13 16:55:22 2009
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1110
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_mrp0_8356.trc:
ORA-01110: data file 1: '+DATA/test11g/system01.dbf'
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '+DATA/test11g/system01.dbf'
ORA-01251: Unknown File Header Version read for file number 1
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_mrp0_8356.trc:
ORA-01110: data file 1: '+DATA/test11g/system01.dbf'
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '+DATA/test11g/system01.dbf'
ORA-01251: Unknown File Header Version read for file number 1
MRP0: Background Media Recovery process shutdown (test11gr1)
Completed: alter database recover managed standby database disconnect from session
Mon Jul 13 17:07:05 2009
db_recovery_file_dest_size of 4096 MB is 0.00% 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.
Mon Jul 13 17:18:40 2009
Using STANDBY_ARCHIVE_DEST parameter default value as +DATA/test11g/archivelog
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 23554
RFS[1]: Identified database type as 'physical standby'
RFS LogMiner: Client disabled from further notification
Mon Jul 13 17:18:41 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 23564
RFS[2]: Identified database type as 'physical standby'
Mon Jul 13 17:18:41 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 23578
RFS[3]: Identified database type as 'physical standby'
Mon Jul 13 17:18:53 2009
RFS[2]: Archived Log: '+DATA/test11g/archivelog/1_119_683602501.dbf'
Mon Jul 13 17:18:53 2009
RFS[1]: Archived Log: '+DATA/test11g/archivelog/1_121_683602501.dbf'
Mon Jul 13 17:18:53 2009
RFS[3]: Archived Log: '+DATA/test11g/archivelog/1_120_683602501.dbf'
Mon Jul 13 17:18:55 2009
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
Shutting down instance (immediate)
License high water mark = 4
Mon Jul 13 17:18:57 2009
ORA-1089 : opidrv aborting process O000 ospid (23558_1)
Mon Jul 13 17:18:57 2009
ORA-1089 : opidrv aborting process O001 ospid (23726_1)
ORA-1089 : opidrv aborting process unknown ospid (23564_1)
ORA-1089 : opidrv aborting process unknown ospid (23554_1)
ORA-1089 : opidrv aborting process unknown ospid (23578_1)
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_rfs_23564.trc (incident=465):
ORA-00600: internal error code, arguments: [kfmdSlvLeaveWrt1], [12], [], [], [], [], [], []
ORA-01089: immediate shutdown in progress - no operations are permitted
ORA-12577: Message 12577 not found; product=RDBMS; facility=ORA
Incident details in: /data/oracle/diag/rdbms/test11gr/test11gr1/incident/incdir_465/test11gr1_rfs_23564_i465.trc
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_rfs_23554.trc (incident=121):
ORA-00600: internal error code, arguments: [kfmdSlvLeaveWrt1], [16], [], [], [], [], [], []
ORA-01089: immediate shutdown in progress - no operations are permitted
ORA-12577: Message 12577 not found; product=RDBMS; facility=ORA
Incident details in: /data/oracle/diag/rdbms/test11gr/test11gr1/incident/incdir_121/test11gr1_rfs_23554_i121.trc
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_rfs_23578.trc (incident=481):
ORA-00600: internal error code, arguments: [kfmdSlvLeaveWrt1], [12], [], [], [], [], [], []
ORA-01089: immediate shutdown in progress - no operations are permitted
ORA-12577: Message 12577 not found; product=RDBMS; facility=ORA
Incident details in: /data/oracle/diag/rdbms/test11gr/test11gr1/incident/incdir_481/test11gr1_rfs_23578_i481.trc
不但有ORA-1110、ORA-1122和ORA-1151錯誤,後面還有ORA-600(kfmdSlvLeaveWrt1)錯誤。仔細檢查了一下,發現後面的600錯誤倒是沒有多大關系,是由于SHUTDOWN操作強行關閉了RFS程序引發的。這并不是什麼大問題。
關鍵是前面的ORA-1110、ORA-1122和ORA-1151錯誤。是什麼導緻了資料檔案驗證出現了問題,查詢metalink很久,也沒有找到對應的描述。
回想所有的操作,隻有一個地方有疑點,就是資料檔案是通過ASMCMD工具的CP指令放到ASM磁盤組中的,會不會是這個操作導緻的問題呢,嘗試利用其他的方法來傳輸資料檔案。為了有所對比,利用ftp的方式,先把system01.dbf資料檔案從源站點再傳送一次,然後再次啟動ALTER DATABASE RECOVER MANAGED STANDBY DATABASE指令:
SQL> alter database recover managed standby database disconnect from session;
資料庫已更改。
再次檢查alert檔案,發現錯誤資訊改變了:
Wed Jul 15 15:08:34 2009
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (test11gr1)
Wed Jul 15 15:08:34 2009
MRP0 started with pid=64, OS id=8453
MRP0: Background Managed Standby Recovery process started (test11gr1)
Fast Parallel Media Recovery enabled
Wed Jul 15 15:08:39 2009
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1110
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_mrp0_8453.trc:
ORA-01110: data file 2: '+DATA/test11g/sysaux01.dbf'
ORA-01122: database file 2 failed verification check
ORA-01110: data file 2: '+DATA/test11g/sysaux01.dbf'
ORA-01251: Unknown File Header Version read for file number 2
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_mrp0_8453.trc:
ORA-01110: data file 2: '+DATA/test11g/sysaux01.dbf'
ORA-01122: database file 2 failed verification check
ORA-01110: data file 2: '+DATA/test11g/sysaux01.dbf'
ORA-01251: Unknown File Header Version read for file number 2
MRP0: Background Media Recovery process shutdown (test11gr1)
Completed: alter database recover managed standby database disconnect from session
剛才傳送的system資料檔案已經沒有問題了,看來問題就是asmcmd造成的,通過ftp的方式将所有的資料檔案重新傳送,問題解決。
由于開始的時候沒有拷貝REDO.LOG檔案,是以alert檔案中出現下面的錯誤資訊:
Wed Jul 15 16:30:53 2009
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 7 processes
Waiting for all non-current ORLs to be archived...
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_mrp0_3616.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DATA/test11g/redo01.log'
ORA-17503: ksfdopn:2 Failed to open file +DATA/test11g/redo01.log
ORA-15173: entry 'redo01.log' does not exist in directory 'test11g'
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_mrp0_3616.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DATA/test11g/redo01.log'
ORA-17503: ksfdopn:2 Failed to open file +DATA/test11g/redo01.log
ORA-15173: entry 'redo01.log' does not exist in directory 'test11g'
Clearing online redo logfile 1 +DATA/test11g/redo01.log
Clearing online log 1 of thread 1 sequence number 151
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_mrp0_3616.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DATA/test11g/redo01.log'
ORA-17503: ksfdopn:2 Failed to open file +DATA/test11g/redo01.log
ORA-15173: entry 'redo01.log' does not exist in directory 'test11g'
Clearing online redo logfile 1 complete
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_mrp0_3616.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '+DATA/test11g/redo02.log'
ORA-17503: ksfdopn:2 Failed to open file +DATA/test11g/redo02.log
ORA-15173: entry 'redo02.log' does not exist in directory 'test11g'
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_mrp0_3616.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '+DATA/test11g/redo02.log'
ORA-17503: ksfdopn:2 Failed to open file +DATA/test11g/redo02.log
ORA-15173: entry 'redo02.log' does not exist in directory 'test11g'
Clearing online redo logfile 2 +DATA/test11g/redo02.log
Clearing online log 2 of thread 1 sequence number 152
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_mrp0_3616.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '+DATA/test11g/redo02.log'
ORA-17503: ksfdopn:2 Failed to open file +DATA/test11g/redo02.log
ORA-15173: entry 'redo02.log' does not exist in directory 'test11g'
Clearing online redo logfile 2 complete
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_mrp0_3616.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '+DATA/test11g/redo03.log'
ORA-17503: ksfdopn:2 Failed to open file +DATA/test11g/redo03.log
ORA-15173: entry 'redo03.log' does not exist in directory 'test11g'
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_mrp0_3616.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '+DATA/test11g/redo03.log'
ORA-17503: ksfdopn:2 Failed to open file +DATA/test11g/redo03.log
ORA-15173: entry 'redo03.log' does not exist in directory 'test11g'
Clearing online redo logfile 3 +DATA/test11g/redo03.log
Clearing online log 3 of thread 1 sequence number 150
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_mrp0_3616.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '+DATA/test11g/redo03.log'
ORA-17503: ksfdopn:2 Failed to open file +DATA/test11g/redo03.log
ORA-15173: entry 'redo03.log' does not exist in directory 'test11g'
Completed: alter database recover managed standby database disconnect from session
Clearing online redo logfile 3 complete
将所有的REDO檔案拷貝到ASM磁盤組中,問題消失。
下面這個問題是由于建立STANDBY CONTROLFILE過早造成的:
Media Recovery Log +DATA/test11g/archivelog/1_119_683602501.dbf
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_mrp0_3616.trc:
ORA-01119: error in creating database file '+DATA/test11g/undotbs2_01.dbf'
ORA-17502: ksfdcre:4 Failed to create file +DATA/test11g/undotbs2_01.dbf
ORA-15005: name "test11g/undotbs2_01.dbf" is already used by an existing alias
File #7 added to control file as 'UNNAMED00007'.
Originally created as:
'/data/oracle/oradata/test11g/undotbs2_01.dbf'
Recovery was unable to create the file as:
'+DATA/test11g/undotbs2_01.dbf'
MRP0: Background Media Recovery terminated with error 1274
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_mrp0_3616.trc:
ORA-01274: cannot add datafile '/data/oracle/oradata/test11g/undotbs2_01.dbf' - file could not be created
Shutting down recovery slaves due to error 1274
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Errors in file /data/oracle/diag/rdbms/test11gr/test11gr1/trace/test11gr1_mrp0_3616.trc:
ORA-01274: cannot add datafile '/data/oracle/oradata/test11g/undotbs2_01.dbf' - file could not be created
MRP0: Background Media Recovery process shutdown (test11gr1)
由于RAC環境需要建立THREAD2對應的UNDOTBS2表空間,而這個操作發生在建立STANDBY CONTROLFILE之後,是以導緻了上面的錯誤資訊。
通過重建STANDBY CONTROLFILE的方面可以友善的解決這個錯誤。
oracle視訊教程請關注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
轉載于:https://blog.51cto.com/19880614/1208661