ORA

浸心閣 2015-09-11

展開全文

官方解釋：

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

錯誤描述：

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

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í)行到這步報錯

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上執(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ù)
Errors in file /u01/oracle/diag/rdbms/standby/ora11g/trace/ora11g_arc1_18114.trc:
ORA-12514: TNS: 監(jiān)聽程序當(dā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，忽略這個問題，直接啟備庫到mount狀態(tài)。

2，啟動主庫到open狀態(tài)。

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

第二種方法：

1，將主庫啟動到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，啟動主庫

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，保證監(jiān)聽都開啟，主從庫t網(wǎng)絡(luò)都暢通。

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

啟動 standby --> primary

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

小結(jié)：

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

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

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

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