Shulou(Shulou.com)06/01 Report--

In the previous article, we learned about the common fault categories and planning resolution of oracle. Next, let's take a look at the relevant content of ORA-01196 error resolution in oracle database, as follows:

Problem phenomenon

After using shutdown abort to stop DataGuard to prepare the database, the slave database cannot be open and an ORA-01196 error is reported.

It was found that the log could not be applied to the primary database, and no error was found when checking the log of the standby database. It was suspected that the application log service of the standby database was stopped, so I tried to restart the standby database.

It may be because the standby database is busy with reading business, and the waiting time is too long when shutdown immediate shuts down the standby database, so the shutdown abort command is used.

But later, an error occurred when starting the standby library, resulting in damage to the data file and inconsistency between the scn number of the control file and the data file.

The SQL > startupORACLE routine has been started. Total System Global Area 2.0310E+10 bytesFixed Size 2235256 bytesVariable Size 9328133256 bytesDatabase Buffers 1.0939E+10 bytesRedo Buffers 40894464 bytes

The database is loaded.

ORA-10458: standby database requiresrecovery

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

ORA-01110: the data file 1 is the data file 1, which is called data transaction database, htdb5, datafile, system. 261.759082693'.

-- check the log

Alter database openData Guard Brokerinitializing...Data Guard Brokerinitialization completeBeginning standby crash recovery.Serial Media Recovery startedManaged Standby Recoverystarting Real Time ApplyMedia Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180068.1541.885192077Thu Jul 16 12:00:47 2015Errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc:ORA-01013: user requests to cancel the current operation ORA-10567: Redo is inconsistentwith data block (file# 47, block# 1187724) File offset is 1139900416 bytes) ORA-10564: tablespace JDYWP_IDXORA-01110: data file 47:'+DATA/htdb5/datafile/jdywp_idx.336.856967805'ORA-10561: block type'TRANSACTION MANAGED INDEX BLOCK' Data object# 251837Errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc:ORA-00339: archive log does not contain any redo ORA-00334: archive log:'+ DATA/htdb5/onlinelog/group_2.280.759082845'ORA-10567: Redo is inconsistentwith data block (file# 47, block# 1187724) File offset is 1139900416 bytes) ORA-10564: tablespace JDYWP_IDXORA-01110: data file 47:'+DATA/htdb5/datafile/jdywp_idx.336.856967805'ORA-10561: block type'TRANSACTION MANAGED INDEX BLOCK', data object# 251837Errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc (incident=116743): ORA-00600: internal error code, parameters: [3020], [47], [1187724], [198320012], [], [] [], [] ORA-10567: Redo is inconsistentwith data block (file# 47, block# 1187724, file offset is 1139900416 bytes) ORA-10564: tablespace JDYWP_IDXORA-01110: data file 47:'+DATA/htdb5/datafile/jdywp_idx.336.856967805'ORA-10561: block type'TRANSACTION MANAGED INDEX BLOCK' Data object# 251837Incident details in:/u01/app/ora11g/diag/rdbms/htdb5/htdb5/incident/incdir_116743/htdb5_ora_10154_i116743.trcUse ADRCI or Support Workbenchto package the incident.See Note 411.1 at My OracleSupport for error and packaging details.Standby crash recovery aborteddue to error 600.Errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc:ORA-00600: internal error code, parameters: [3020], [47], [1187724], [198320012] [], [] ORA-10567: Redo is inconsistentwith data block (file# 47, block# 1187724, file offset is 1139900416 bytes) ORA-10564: tablespace JDYWP_IDXORA-01110: data file 47:'+DATA/htdb5/datafile/jdywp_idx.336.856967805'ORA-10561: block type'TRANSACTION MANAGED INDEX BLOCK' Data object# 251837Recovery invalid some recovered datafiles maybeleft media fuzzyMedia recovery may continue butopen resetlogs may failCompleted standby crashrecovery.Errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc:ORA-10458: standby databaserequires recoveryORA-01196: file 1 is inconsistent due to media recovery session failure ORA-01110: data file 1:'+DATA/htdb5/datafile/system.261.759082693'ORA-10458 signalled during:alter database open...Thu Jul 16 12:00 : 49 2015Sweep [inc] [116743]: completedSweep [inc2] [116743]: completedThu Jul 1612: 00:49 2015Dumping diagnostic data indirectory= [cdmp _ 20150716120049] Requested by (instance=1, osid=10154), summary= [instance=1 = 116743] .Thu Jul 16 12:01:50 2015

Solution:

The time to flash the repository back to normal.

-- premise that SQL > select FLASHBACK_ON from vested database has been opened before the database flashback; or use Flashbackdatabase to scn 947921SQL > alter database open; SQL > select open_mode from v$database ('2015-07-16 4laze 05V 05V YYMMMMMAVIDH); or use FLASHBACK > select open_mode from v$database OPEN_MODE-READ ONLY-- launches the real-time application SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;SQL > select open_mode from vested database

-- check the log and see that the log has been applied since the time of flashback.

Thu Jul 16 13:36:01 2015Flashback database to timestampto_timestamp ('2015-07-16 4mono 05' 'yyyy-mm-dd hh34:mi:ss') Flashback Restore StartThu Jul 16 13:39:30 2015Flashback Restore CompleteFlashback Media Recovery Start started logmerger processParallel Media Recovery startedwith 16 slavesFlashback Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180047.2212.885180637Thu Jul 16 13:41:54 2015Flashback Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180061.2611.885182343Thu Jul 16 13:42:04 2015Flashback Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1 _ seq_180062.2861.885182537Thu Jul 16 13:42:12 2015Incomplete Recovery applieduntil change 7148977 2016 time 07 Unix 16 Unix 2015 04:00:06Flashback Media RecoveryCompleteCompleted: Flashback databaseto timestamp to_timestamp ('2015-07-16 4V 05' 'yyyy-mm-dd hh34:mi:ss') Thu Jul 16 13:43:25 2015Deleted Oracle managed file+FRA/htdb5/archivelog/2015_07_15/thread_1_seq_179690.2885.885083087Thu Jul 16 13:43:25 2015Standby controlfile consistentwith primaryRFS [3]: Selected log 8 forthread 1 sequence 180122 dbid 1083719948 branch 759079182Archived Log entry 180115 addedfor thread 1 sequence 180121 ID 0x40a48484 dest 1:Thu Jul 16 13:45:41 2015alter database openData Guard Brokerinitializing...Data Guard Brokerinitialization complete SMON: enabling cache recoveryDictionary check beginningDictionary check completeDatabase Characterset isZHS16GBKNo Resource Manager plan Activereplication_dependency_trackingturned off (no async multimaster replication found) Physical standby databaseopened for read only access.Completed: alter database openThu Jul 16 13:45:44 2015ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE THROUGH ALL SWITCHOVERDISCONNECT USING CURRENT LOGFILEAttempt to start backgroundManaged Standby Recovery process (htdb5) Thu Jul 16 13:45:44 2015MRP0 started with pid=51 OSid=14743MRP0: Background ManagedStandby Recovery process started (htdb5) started logmerger processThu Jul 16 13:45:50 2015Managed Standby Recoverystarting Real Time ApplyParallel Media Recoverysta rtedwith 16 slavesWaiting for all non-currentORLs to be archived...All non-currentORLs have beenarchived.Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_ 180062.2861.885182537Completed: ALTER DATABASERECOVER MANAGED STANDBY DATABASE THROUGHALL SWITCHOVER DISCONNECT USING CURRENTLOGFILEThu Jul 16 13:46:08 2015Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180063.3683.885182777Thu Jul 16 13:46:35 2015Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180064.2542.885183119Thu Jul 16 13:47:07 2015Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180065.2717.885183615

Summary

The above is all about the sharing of oracle database ORA-01196 error solutions in this article. I hope it will be helpful to you. Interested friends can continue to refer to this site: ORACLE SQL statement optimization technical points analysis, Oracle RMAN automatic backup control file method introduction, oracle database startup phase analysis, etc., if you have any questions, you can leave a message directly, the editor will reply you in time. Thank you for your support to this site! Here recommend several oracle-related books for the majority of programming hobbies and workers to learn and reference.

Build Oracle High availability Environment (Chen Jiping) Chinese pdf scan version

Https://www.jb51.net/books/554126.html

Oracle Chinese manual collection CHM version

Https://www.jb51.net/books/547791.html

I hope you can like it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.