In-depth analysis of instance recovery

When Will instance recovery be generated after in-depth analysis of instance recovery? When your database server suffers an abnormal power failure, instance recovery will occur when you restart the database. Instance recovery is automatically completed by the database without DBA interference. Of course, there is a premise: data files, online log files, and control files must not be damaged. Let's use an experiment to analyze the whole process of instance recovery! 1. Before shutting down the database, let's take a look at the SCNSQL> select checkpoint_change # from v $ database for several checkpoints; CHECKPOINT_CHANGE # ------------------ 1455180 -- the database checkpoint SCN number saved in the control file is actually the smallest checkpoint in the header of all data files. SCNSQL> select file #, checkpoint_change # from v $ datafile; FILE # CHECKPOINT_CHANGE # ---------- ---------------- 1 1455180 2 1455180 3 1455180 4 1455180 5 1455180 6 1455180 -- control the data FILE checkpoint SCN saved in the FILE: After a checkpoint is completed, oracle stores the scn of each data file in the control file separately. SQL> select File #, checkpoint_change # from v $ datafile_header; FILE # CHECKPOINT_CHANGE # ---------- ---------------- 1 1455180 2 1455180 3 1455180 4 1455180 5 1455180 6 1455180 -- Check Point SCN in the FILE header of each data FILE. The three check points have the same SCN, and then simulate abnormal power failure, restart the machine 2. This command can simulate an abnormal power failure SQL> shutdown abort; ORACLE instance shut down.3. Monitoring Alarm log [oracle @ guoyj trace] $ tail-f alert_bxocp.logStarting background process VKRMTue Dec 11 22:54:41 2012 VKRM started with pid = 24, OS id = 12500Tue Dec 11 22:58:11 2012 Shutting down instance (abort) License high water mark = 3 USER (ospid: 12479): terminating the instanceInstance terminated by USER, pid = 12479Tue Dec 11 22:58:12 2012 Instance shutdown complete4. The database is started to MOUNT-like SQL> shutdown abort; ORACLE instance shut down. SQL> startup mount; ORACLE instance started. total System Global Area 839282688 bytesFixed Size 2233000 bytesV Ariable Size 524291416 bytesDatabase Buffers 310378496 bytesRedo Buffers 2379776 bytesDatabase mounted.5. Confirm the checkpoint SCNSQL> select checkpoint_change # from v $ database; CHECKPOINT_CHANGE # explain 1455180SQL> SQL> select file #, checkpoint_change # from v $ datafile; FILE # CHECKPOINT_CHANGE # ---------- ------------------ 1 1455180 2 1455180 3 1455180 4 1455180 5 1455180 6 14551806 rows select Ed. SQL> select file #, checkpoint_change # from v $ datafile_header; FILE # CHECKPOINT_CHANGE # ---------- ---------------- 1 1455180 2 1455180 3 1455180 4 1455180 5 1455180 6 1455180 found the same as the SCN of the checkpoint before the power-off exception. No media recovery is required here. Do not worry about opening the database first. We will make some dump6 and dump control files alter session set events 'immediate trace name CONTROLF level 12'; take part of the content: **************************************** * ********************************** database entry *** **************************************** ******************************* (size = 316, compat size = 316, section max = 1, section in-use = 1, last-recid = 0, old-recno = 0, last-recno = 0) (extent = 1, blkno = 1, numrecs = 1) 12/07/2012 10: 36: 14DB Name "BXOCP" Database flags = 0x00404000 0x00001000Controlfile Creation Timestamp 12/07/2012 10: 36: 15 Incmplt recovery scn: 0x0000. 00000000 Resetlogs scn: 0x0000. 000f30dc Resetlogs Timestamp 12/07/2012 10: 36: 16 Prior resetlogs scn: 0x0000.00000001 Prior resetlogs Timestamp 09/17/2011 09: 46: 04 Redo Version: compatible = 0xb200000 # Data files = 6, # Online files = 6 Data Base checkpoint: Thread = 1 scn: 0x0000. 0016344c -- convert the database checkpoint SCN = 16344c to 1455180 Threads: # Enabled = 1, # Open = 1, Head = 1, tail = 1 ************************************* * ************************************ CHECKPOINT PROGRESS RECORDS *************************************** * ********************************** (size = 8180, compat size = 8180, section max = 11, section in-use = 0, last-recid = 0, old-recno = 0, last-recno = 0) (extent = 1, blkno = 2, numrecs = 11) THREAD #1-status: 0x2 flags: 0x0 dirty: 55low cache rba :( 0x13. 3.0) on disk rba :( 0x13. a6.0) -- low cache rba :( 0x13. 3.0) instance recovery start point: log 19, 3rd blocks, 0th bytes -- on disk rba :( 0x13. a6.0): end point of instance recovery: log 19, 166th blocks, 0th bytes on disk scn: 0x0000. 0016359c 12/11/2012 22: 57: 42 resetlogs scn: 0x0000. 000f30dc 12/07/2012 10: 36: 16 heartbeat: 801789080 mount id: 848836772 THREAD #2-st Atus: 0x0 flags: 0x0 dirty: 0low cache rba :( 0x0. 0.0) on disk rba :( 0x0. 0.0) on disk scn: 0x0000.00000000 01/01/1988 00: 00: 00 resetlogs scn: 0x0000.00000000 01/01/1988 00: 00: 00 heartbeat: 0 mount id: 0 *************************************** * ********************************** data file records * **************************************** * ******************************** (size = 520, compat size = 520, section Max = 100, section in-use = 6, last-recid = 43, old-recno = 0, last-recno = 0) (extent = 1, blkno = 11, numrecs = 100) data file #1: name #7: /oradata/bxocp/system01.dbfcreation size = 0 block size = 8192 status = 0xe head = 7 tail = 7 dup = 1 tablespace 0, index = 1 krfil = 1 prev_file = 0 unrecoverable scn: 0x0000.00000000 01/01/1988 00: 00: 00 Checkpoint cnt: 121 scn: 0x0000. 0016344c 12/11/2012 22:54:36 -- data file checkpoint S stored in the control file CN = 16344c to 10: 1455180 Stop scn: 0xffff. ffffffff 12/11/2012 22:53:05 -- the ending SCN is infinite, indicating that the database is shut down abnormally. to restart the database, you must restore the instance Creation Checkpointed at scn: 0x0000.00000007 09/17/2011 09: 46: 08 thread: 0 rba :( 0x0. 0.0) 7. dump the data file header alter session set events 'immediate trace name file_hdrs level 10'; displays part of the data file header: V10 style file header: compatibility Vsn = 186646528 = 0xb200000 Db ID = 848459038 = 0x3292751e, Db Name = 'bxopput' Activation I D = 0 = 0x0 Control Seq = 2099 = 0x833, File size = 79360 = 0x13600 File Number = 2, Blksiz = 8192, file Type = 3 DATATablespace #1-SYSAUX rel_fn: 2 Creation at scn: 0x0000. 0000088c 09/17/2011 09: 46: 16 Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread: 0 reset logs count: 0x2fc45da0 scn: 0x0000. 000f30dcprev reset logs count: 0x2d6c775c scn: 0x0000. 00000001 recovered at 12/11/2012 22: 54: 36 status: 0x4 root dba: 0x00000000 chkpt cnt: 121 ctl cnt: 120begin-hot-backup file size: 0 Checkpointed at scn: 0x0000. 0016344c 12/11/2012 22:54:36 -- the checkpoint SCN = 16344c in the file header of the data file is converted into a 10-digit 1455180 thread: 1 rba :( 0x13. 2.10) -- Address of the redo log 0X13. 2.10-> log 19, block 2nd, 16th bytes start to recover. Note: The starting address for restoring the redo log from the control file is low cache rba :( 0x13. 3.0): log 19, 3rd blocks, 0th bytes start to recover from the data file header get redo log recovery start address: thread: 1 rba :( 0x13. 2.10): log 9, block 2nd, 16th bytes start to recover 8. Finally, we open the database, and then monitor the alarm log alert_bxocp.log, See how to recover [oracle @ guoyj trace] $ tail-f alert_bxocp.logalter database openBeginning crash recovery of 1 threadsStarted redo scanCompleted redo scanread 81 KB redo, 55 data blocks need recoveryStarted redo application atThread 1: logseq 19, block 3 -- Redo Log of instance Recovery start: Log 19 3rd Recovery of Online redo Log: thread 1 Group 1 Seq 19 Reading mem 0 Mem #0:/oradata/bxocp/redo01.logCompleted redo application of 0. 06 MBCompleted crash recovery atThread 1: logseq 19, block 166, scn 1475516 -- redo log of the instance recovery end point: 166th 55 data blocks read, 55 data blocks written, 81 redo k-bytes readTue Dec 11 23:46:42 2012 Thread 1 advanced to log sequence 20 (thread open) Thread 1 opened at log sequence 20 Current log #2 seq #20 mem #0: /oradata/bxocp/redo02.logSuccessful open of redo thread 1 MTTR advisory is disabled because F AST_START_MTTR_TARGET is not set [12867] Successfully onlined Undo Tablespace 2. undo initialization finished serial: 0 start: 20725234 end: 20725294 diff: 60 (0 seconds) Verifying file header compatibility for 11g tablespace encryption .. verifying 11g file header compatibility for tablespace encryption completedTue Dec 11 23:46:42 2012 SMON: enabling cache recoverySMON: enabling tx recoveryDatabase Charac Terset is ZHS16GBKNo Resource Manager plan activereplication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNCTue Dec 11 23:46:43 2012 QMNC started with pid = 21, OS id = 13839 Completed: alter database openTue Dec 11 23:46:44 2012 Starting background process CJQ0Tue Dec 11 23:46:44 2012CJQ0 started with pid = 22, OS id = 13851 Setting Resource Manager pla N SCHEDULER [0x318A]: via scheduler windowSetting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameterTue Dec 11 23:46:47 2012 Starting background process VKRMTue Dec 11 23:46:47 2012 VKRM started with pid = 23, OS id = 138579. It can be seen that the initial redo log for instance recovery is the low cache rba :( 0x13. 3.0): log 19, 3rd blocks, 0th bytes, instead of starting from the thread: 1 rba :( 0x13. 2.10) -- Address of the redo log 0X13. 2.10-> log 19, block 2nd, 16th bytes begin to recover 10, Summary: instance recovery (1) data files, online log files, and control files must not be damaged (2) Automatic database recovery without DBA interference (3) Online log files, no need to archive logs (4) The database starts instance recovery when it is open. In fact, I have not completed the restoration experiment for this instance, and the last step is rollback! This is for your consideration! Three steps for Instance Recovery: Roll Back ---> Open Database ----> rollback (also called rollback). In fact, On Disk RBA is not the end point of Instance Recovery !!!