官方解釋：

ORA-01196: file string is inconsistent due to a failed media recovery session
Cause: The file was being recovered but the recovery did not terminate normally. This left the file in an inconsistent state. No more recovery was successfully completed on this file.
Action: Either apply more logs until the file is consistent or restore the backup again and repeat recovery.

======================================================================================

環(huán)境： 

rhel5u7+11.2.0.1.0+physical standby    
ADG保護(hù)模式：MAXIMUM PERFORMANCE
重做日志傳輸模式：LGWR ASYNC AFFIRM  
應(yīng)用方法：redo apply

錯(cuò)誤描述：

使用vmware搭建了一個(gè)物理ADG實(shí)驗(yàn)環(huán)境，保護(hù)模式為"maximum protection",為了驗(yàn)證standby 庫(kù)down而primary也會(huì)down機(jī)，在standby 上執(zhí)行了abort強(qiáng)制關(guān)閉數(shù)據(jù)庫(kù)。

Standby上執(zhí)行下面操作如下：

SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database using current logfile  disconnect from session;
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;      #執(zhí)行到這步報(bào)錯(cuò)

ORA-01110: data file 1: '/oradata/ora11g/system01.dbf'

Primary上執(zhí)行的操作如下：

SQL> startup 
ORACLE instance started.

Total System Global Area  368263168 bytes
Fixed Size                  1336596 bytes
Variable Size             289409772 bytes
Database Buffers           71303168 bytes
Redo Buffers                6213632 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 17795
Session ID: 1 Serial number: 5

=====================================================

Primary參數(shù)文件：

ora11g.__pga_aggregate_target=134217728
ora11g.__sga_target=234881024
ora11g.__shared_io_pool_size=0
ora11g.__shared_pool_size=138412032
ora11g.__streams_pool_size=8388608
*.audit_file_dest='/u01/oracle/admin/ora11g/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/ora11g/control01.ctl','/u01/oracle/flash_recovery_area/ora11g/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_recovery_file_dest='/u01/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
*.memory_target=367001600
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=170
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
*.db_name='ora11g'
*.db_unique_name='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=/orachivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=primary'
*.log_archive_dest_2='SERVICE=standby LGWR SYNC AFFIRM  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQ
UE_NAME=standby'
*.db_file_name_convert='standby','primary'
*.log_file_name_convert='standby','priamry'
*.fal_client='primary'
*.fal_server='standby'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.standby_file_management=auto

===========================================================

Standby參數(shù)文件：

ora11g.__db_cache_size=71303168
ora11g.__java_pool_size=4194304
ora11g.__large_pool_size=4194304
ora11g.__oracle_base='/u01/oracle'#ORACLE_BASE set from environment
ora11g.__pga_aggregate_target=134217728
ora11g.__sga_target=234881024
ora11g.__shared_io_pool_size=0
ora11g.__shared_pool_size=138412032
ora11g.__streams_pool_size=8388608
*.audit_file_dest='/u01/oracle/admin/ora11g/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/ora11g/standby01.ctl','/oradata/ora11g/standby02.ctl','/u01/oracle/flash_recovery_area/ora11g/standby03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ora11g'
*.db_recovery_file_dest='/u01/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
*.log_archive_dest_1='location=/orachivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=367001600
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=170
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='standby'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=/orachivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=standby'
*.log_archive_dest_2='SERVICE=primary LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_server=primary
*.fal_client=standby
*.db_file_name_convert='primary','standby'
*.log_file_name_convert='priamry','standby'
*.standby_file_management=auto

alterlog文件中的信息如下：

Fatal NI connect error 12514, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.111)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primary)(CID=(PROGRAM=oracle)(HOST=dba2.test.com)(USER=oracle))))


  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.1.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
  Time: 25-JAN-2013 12:14:15
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12564
    
TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
***********************************************************************
Fatal NI connect error 12514, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.111)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primary)(CID=(PROGRAM=oracle)(HOST=dba2.test.com)(USER=oracle))))


  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.1.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
  Time: 25-JAN-2013 12:14:15
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12564
    
TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
Error 12514 received logging on to the standby
FAL[client, ARC1]: Error 12514 connecting to primary for fetching gap sequence
Errors in file /u01/oracle/diag/rdbms/standby/ora11g/trace/ora11g_arc1_18114.trc:
ORA-12514: TNS: 監(jiān)聽程序當(dāng)前無(wú)法識(shí)別連接描述符中請(qǐng)求的服務(wù)
Errors in file /u01/oracle/diag/rdbms/standby/ora11g/trace/ora11g_arc1_18114.trc:
ORA-12514: TNS: 監(jiān)聽程序當(dāng)前無(wú)法識(shí)別連接描述符中請(qǐng)求的服務(wù)
Fri Jan 25 12:14:15 2013
ARCt started with pid=50, OS id=18174 
ARCt: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Fri Jan 25 12:14:18 2013
INSV started with pid=51, OS id=18177 
Fri Jan 25 12:28:30 2013
db_recovery_file_dest_size of 3852 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.
Fri Jan 25 12:55:13 2013
alter database recover managed standby database using current logfile parallel 2 disconnect from session
Attempt to start background Managed Standby Recovery process (ora11g)
Fri Jan 25 12:55:14 2013
MRP0 started with pid=52, OS id=18753 
MRP0: Background Managed Standby Recovery process started (ora11g)
 started logmerger process
Fri Jan 25 12:55:19 2013
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /orachivelog/1_169_777078497.dbf
Completed: alter database recover managed standby database using current logfile disconnect from session
Recovery of Online Redo Log: Thread 1 Group 5 Seq 170 Reading mem 0
  Mem# 0: /oradata/ora11g/dglog08.log
Standby crash recovery failed to bring standby database to a consistent
point because needed redo hasn't arrived yet.
MRP: Wait timeout: thread 1 sequence# 170
Standby crash recovery aborted due to error 16016.
Errors in file /u01/oracle/diag/rdbms/standby/ora11g/trace/ora11g_ora_18085.trc:
ORA-16016: archived log for thread 1 sequence# 170 unavailable
Recovery interrupted!
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Completed standby crash recovery.
Errors in file /u01/oracle/diag/rdbms/standby/ora11g/trace/ora11g_ora_18085.trc:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oradata/ora11g/system01.dbf'
ORA-10458 signalled during: alter database open read only...

ora11g_ora_18085.trc中的信息如下：

*** 2013-01-25 13:19:33.276
Media Recovery drop redo thread 1
KCBR: Number of read descriptors = 1024
KCBR: Media recovery blocks read (SYNC) = 71
KCBR: Influx buffers flushed = 5 times
KCBR: Redo cache copies/changes = 777/777

*** 2013-01-25 13:19:33.277
Completed Media Recovery
In-flux buffer recovery was not started because datafiles were fuzzy beyond in-flux recovery target.
Highest datafile fuzzy SCN: 0.1934411
In-flux buffer recovery target SCN: 0.1933999
*** 2013-01-25 13:19:33.307 1266 krsm.c
Managed Recovery: Not Active posted.
DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 6 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 6 csec) -----
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oradata/ora11g/system01.dbf'

解決方法兩種：

第一種方法：

1，忽略這個(gè)問(wèn)題，直接啟備庫(kù)到mount狀態(tài)。

2，啟動(dòng)主庫(kù)到open狀態(tài)。

3，在備庫(kù)上應(yīng)用實(shí)時(shí)應(yīng)用redo log。

第二種方法：

1，將主庫(kù)啟動(dòng)到mount狀態(tài)，修改redo log傳輸模式為異步。

SQL> alter system set log_archive_dest_2='service=primary async valid_for=(online_logfiles,primary_role) db_unique_name=primary' scope=spfile;

2，將ADG模式修改為最大性能模式

SQL> alter database set standby database to maximize performance;

SQL> shut immediate;

3，啟動(dòng)主庫(kù)

SQL> startup

4，啟動(dòng)從庫(kù)

SQL>startup nomount;

SQL>alter database mount standby database;

SQL>alter database recover managed standby database using current logfile disconnect from session;

SQL>alter database recover managed standby database cancel;

SQL>alter database open read only;

注意：

1， 保證監(jiān)聽都開啟，主從庫(kù)t網(wǎng)絡(luò)都暢通。

2，正常情況下ADG的啟動(dòng)和關(guān)閉順序是

啟動(dòng)  standby --> primary

關(guān)閉  primary --> standby

小結(jié)：

這次實(shí)驗(yàn)主要是驗(yàn)證一下，ADG在最大保護(hù)模式下如果standby  down掉時(shí)的情況，也就是從庫(kù)down了，主庫(kù)是不是也會(huì)隨著也掛了。沒(méi)想到還出了這么多問(wèn)題，實(shí)驗(yàn)中在從庫(kù)上執(zhí)行了"shut abort"命令，導(dǎo)致從庫(kù)非一致性停庫(kù)，接著主庫(kù)也確實(shí)掛了，但是再次啟動(dòng)從庫(kù)到open（可以啟動(dòng)到mount狀態(tài)）時(shí)報(bào)“ORA-10458”錯(cuò)誤，說(shuō)是需要從庫(kù)需要恢復(fù)，則又執(zhí)行recover database進(jìn)行恢復(fù)，但沒(méi)有成功?？赡苁莚edo log還沒(méi)有傳到standby庫(kù)上所導(dǎo)致，突然想到在最大保護(hù)模式下，redo log必須要傳到一臺(tái)standby上才可以在主庫(kù)上進(jìn)行commit，也就是說(shuō)備庫(kù)上沒(méi)有接收完主庫(kù)上的redo log就down機(jī)了，而主庫(kù)上的redo數(shù)據(jù)還在redo log里面并沒(méi)有傳到備庫(kù)上的standby redo log中，所以備庫(kù)不能open,而主庫(kù)需要open的前提是備庫(kù)有應(yīng)用redo log才能啟庫(kù)，因此只能先將主庫(kù)修改成最大性能模式再啟庫(kù)，因?yàn)樽畲笮阅苣Ｊ绞钱惒降牟⒉灰髲膸?kù)必須應(yīng)用redo log后主庫(kù)才能提交。以上只是個(gè)人的理解，如有不對(duì)的地方希望大家更正或補(bǔ)充。

======================================================================================

版權(quán)所有，文章允許轉(zhuǎn)載，但必須以鏈接方式注明源地址，否則追究法律責(zé)任! 謝謝合作！

QQ: 164798858@qq.com
Sina: weibo.com/kaijunfeng
Yahoo: fffygapl@yahoo.com.cn