1 实例恢复什么时候发生
根据Oracle官方的《Oracle database 概念指南》,实例恢复redo线程的状态有关。一个redo线程记录了一个数据库实例产生产生的所有变更。单实例数据库有一个redo线程,RAC数据库的redo线程数同数据库的实例数量相同。
当数据库以读写模式打开时,redo线程在控制文件中的状态值是open,当数据库一致性的关闭时,redo线程在控制文件中的状态被标识为closed。
数据库处于读写状态时,控制文件中redo线程的状态可以通过dump控制文件看到,这里使用的oradebug工具,dump步骤如下:
SQL> oradebug setmypid Statement processed. SQL> oradebug dump controlf 12 Statement processed.
dump文件中redo线程部分如下:
REDO THREAD RECORDS ***************************************************************************(size =256, compat size =256, section max =8, section in-use =1, last-recid=0, old-recno =0, last-recno =0)(extent =1, blkno =9, numrecs =8) THREAD #1 - status:0xf thread links forward:0 back:0 #logs:3 first:1 last:3 current:1 last used seq#:0xd enabled at scn:0x0000.000e200609/07/202210:10:51 disabled at scn:0x0000.0000000001/01/198800:00:00 opened at 09/13/202215:04:15by instance orcl11g
正常关闭数据库,然后启动数据库到mount状态,再dump控制文件,这是会话的默认跟踪文件已经发生了变化。
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 534462464 bytes Fixed Size 2254952 bytes Variable Size 213911448 bytes Database Buffers 310378496 bytes Redo Buffers 7917568 bytes Database mounted.
数据库在mount状态时是可以查询v$diag_info视图和dump控制文件的,dump的方法和前面的相同,只是要注意会话的跟踪文件发生了变化,需要重新查询,dump出来的控制文件redo线程部分如下,
REDO THREAD RECORDS ***************************************************************************(size =256, compat size =256, section max =8, section in-use =1, last-recid=0, old-recno =0, last-recno =0)(extent =1, blkno =9, numrecs =8) THREAD #1 - status:0xe thread links forward:0 back:0 #logs:3 first:1 last:3 current:1 last used seq#:0xd enabled at scn:0x0000.000e200609/07/202210:10:51 disabled at scn:0x0000.0000000001/01/198800:00:00 opened at 09/13/202215:04:15by instance orcl11g Checkpointed at scn:0x0000.0010d0f509/13/202215:20:56 thread:1 rba:(0xd.9d2.10)
这时redo线程的状态是0xe。以abort方式关闭数据库,启动到mount模式,dump控制文件,redo线程部分内容如下:
REDO THREAD RECORDS *************************************************************************** THREAD #1 - status:0xf thread links forward:0 back:0 #logs:3 first:1 last:3 current:1 last used seq#:0xd enabled at scn:0x0000.000e200609/07/202210:10:51 disabled at scn:0x0000.0000000001/01/198800:00:00 opened at 09/13/202215:26:28by instance orcl11g Checkpointed at scn:0x0000.0010d0f809/13/202215:26:28 thread:1 rba:(0xd.9d2.10)
redo线程的状态仍然为0xf,同数据库在读写模式下的状态相同。这种情况下数据库是强制关闭的,数据并不一致,需要进行实例恢复。
2 通过Oracle 告警日志检查实例恢复过程
Oracle实例恢复的过程会记录在数据库的告警日志中,下面截取了告警日志中实例恢复相关的片段。
ALTER DATABASE MOUNT Successful mount of redo thread 1, with mount id 1222144143 Database mounted in Exclusive Mode Lost write protection disabled Completed:ALTER DATABASE MOUNT Tue Sep 1309:22:282022ALTER DATABASE OPEN Beginning crash recovery of 1 threads parallel recovery started with 3 processes Started redo scan Completed redo scan read 14 KB redo,26 data blocks need recovery Started redo application at Thread 1: logseq 11, block 125 Recovery of Online Redo Log: Thread 1Group2 Seq 11 Reading mem 0 Mem# 0:/u01/app/oracle/oradata/orcl11g/redo02.log Completed redo application of 0.01MB Completed crash recovery at Thread 1: logseq 11, block 153, scn 108037526 data blocks read,26 data blocks written,14 redo k-bytes read LGWR: STARTING ARCH PROCESSES Tue Sep 1309:22:282022 ARC0 started with pid=23, OS id=2059 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Tue Sep 1309:22:302022 ARC1 started with pid=24, OS id=2062 Tue Sep 1309:22:302022 ARC2 started with pid=25, OS id=2064 Tue Sep 1309:22:302022 ARC3 started with pid=26, OS id=2066
实例恢复发生在打开数据库时,首先进行redo 扫描,扫描后获得需要恢复的数据块,进行恢复,恢复时只读取在线重做日志。对于未提交事务应用undo日志的过程这里没有显示。