ORA-01196: file 1 is inconsistent due to a failed media recovery session

官方解释：

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.

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

环境： 

rhel5u7+11.2.0.1.0+physical standby    
ADG保护模式：MAXIMUM PERFORMANCE
重做日志传输模式：LGWR ASYNC AFFIRM  
应用方法：redo apply

错误描述：

使用vmware搭建了一个物理ADG实验环境，保护模式为"maximum protection",为了验证standby 库down而primary也会down机，在standby 上执行了abort强制关闭数据库。

Standby上执行下面操作如下：

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;      #执行到这步报错

ERROR at line 1:
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'

Primary上执行的操作如下：

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参数文件：

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参数文件：

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: 监听程序当前无法识别连接描述符中请求的服务
Errors in file /u01/oracle/diag/rdbms/standby/ora11g/trace/ora11g_arc1_18114.trc:
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务
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，忽略这个问题，直接启备库到mount状态。

2，启动主库到open状态。

3，在备库上应用实时应用redo log。

第二种方法：

1，将主库启动到mount状态，修改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，启动主库

SQL> startup

4，启动从库

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， 保证监听都开启，主从库t网络都畅通。

2，正常情况下ADG的启动和关闭顺序是

启动  standby --> primary

关闭  primary --> standby

小结：

这次实验主要是验证一下，ADG在最大保护模式下如果standby  down掉时的情况，也就是从库down了，主库是不是也会随着也挂了。没想到还出了这么多问题，实验中在从库上执行了"shut abort"命令，导致从库非一致性停库，接着主库也确实挂了，但是再次启动从库到open（可以启动到mount状态）时报“ORA-10458”错误，说是需要从库需要恢复，则又执行recover database进行恢复，但没有成功。可能是redo log还没有传到standby库上所导致，突然想到在最大保护模式下，redo log必须要传到一台standby上才可以在主库上进行commit，也就是说备库上没有接收完主库上的redo log就down机了，而主库上的redo数据还在redo log里面并没有传到备库上的standby redo log中，所以备库不能open,而主库需要open的前提是备库有应用redo log才能启库，因此只能先将主库修改成最大性能模式再启库，因为最大性能模式是异步的并不要求从库必须应用redo log后主库才能提交。以上只是个人的理解，如有不对的地方希望大家更正或补充。

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

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任! 谢谢合作！

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