Oracle 11g 例項恢復

--解釋一下last_change#

sys@TESTDB11>col name for a60

sys@TESTDB11>select name, checkpoint_change#, last_change# from v$datafile;

NAME                                                         CHECKPOINT_CHANGE# LAST_CHANGE#

------------------------------------------------------------ ------------------ ------------

/u01/app/oracle/oradata/TestDB11/system01.dbf                           2637384

/u01/app/oracle/oradata/TestDB11/sysaux01.dbf                           2637384

/u01/app/oracle/oradata/TestDB11/undotbs01.dbf                          2637384

/u01/app/oracle/oradata/TestDB11/users01.dbf                            2637384

/u01/app/oracle/oradata/TestDB11/example01.dbf                          2637384

/u01/app/oracle/oradata/TestDB11/rotbs01.dbf                            2637384

6 rows selected.

--開啟並檢視預警日誌

--檢視預警日誌

sys@TESTDB11>col name for a25

sys@TESTDB11>col value for a80

sys@TESTDB11>select * from v$diag_info;

   INST_ID NAME                      VALUE

---------- ------------------------- --------------------------------------------------------------------------------

         1 Diag Enabled              TRUE

         1 ADR Base                  /u01/app/oracle

         1 ADR Home                  /u01/app/oracle/diag/rdbms/testdb11/TestDB11

         1 Diag Trace                /u01/app/oracle/diag/rdbms/testdb11/TestDB11/trace

         1 Diag Alert                /u01/app/oracle/diag/rdbms/testdb11/TestDB11/alert

         1 Diag Incident             /u01/app/oracle/diag/rdbms/testdb11/TestDB11/incident

         1 Diag Cdump                /u01/app/oracle/diag/rdbms/testdb11/TestDB11/cdump

         1 Health Monitor            /u01/app/oracle/diag/rdbms/testdb11/TestDB11/hm

         1 Default Trace File        /u01/app/oracle/diag/rdbms/testdb11/TestDB11/trace/TestDB11_ora_2071.trc

         1 Active Problem Count      0

         1 Active Incident Count     0

11 rows selected.

--跟蹤預警日誌內容

[oracle@S1011:/export/home/oracle]$ tail -f /u01/app/oracle/diag/rdbms/testdb11/TestDB11/trace/alert_TestDB11.log

Last login: Fri Aug  9 08:40:27 2013 from 192.168.6.10

Oracle Corporation      SunOS 5.10      Generic Patch   January 2005

[oracle@S1011:/export/home/oracle]$ tail -f /u01/app/oracle/diag/rdbms/testdb11/TestDB11/trace/alert_TestDB11.log

CJQ0 started with pid=22, OS id=1105

Fri Aug 09 09:16:01 2013

alter database datafile '/u01/app/oracle/oradata/TestDB11/rotbs01.dbf' offline

ORA-1145 signalled during: alter database datafile '/u01/app/oracle/oradata/TestDB11/rotbs01.dbf' offline...

Fri Aug 09 09:17:31 2013

Time drift detected. Please check VKTM trace file for more details.

Fri Aug 09 09:25:37 2013

Starting background process SMCO

Fri Aug 09 09:25:37 2013

SMCO started with pid=21, OS id=1157

--關庫重啟到MOUNT狀態

sys@TESTDB11>shutdown abort;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TESTDB11>startup mount;

ORACLE instance started.

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             641730040 bytes

Database Buffers          209715200 bytes

Redo Buffers                2306048 bytes

Database mounted.

--檢視此時的last_change#

sys@TESTDB11>col name for a60

sys@TESTDB11>select name, checkpoint_change#, last_change# from v$datafile;

NAME                                                         CHECKPOINT_CHANGE# LAST_CHANGE#

------------------------------------------------------------ ------------------ ------------

/u01/app/oracle/oradata/TestDB11/system01.dbf                           2638811      2638811

/u01/app/oracle/oradata/TestDB11/sysaux01.dbf                           2638811      2638811

/u01/app/oracle/oradata/TestDB11/undotbs01.dbf                          2638811      2638811

/u01/app/oracle/oradata/TestDB11/users01.dbf                            2638811      2638811

/u01/app/oracle/oradata/TestDB11/example01.dbf                          2638811      2638811

/u01/app/oracle/oradata/TestDB11/rotbs01.dbf                            2638811      2638811

6 rows selected.

sys@TESTDB11>alter database open;

Database altered.

--檢視預警日誌內容的變化，確定進行了例項恢復

Successful mount of redo thread 1, with mount id 2588169812

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE   MOUNT

Thu Aug 08 17:54:35 2013

alter database open

Beginning crash recovery of 1 threads

Started redo scan

Completed redo scan

 read 131 KB redo, 68 data blocks need recovery

Started redo application at

 Thread 1: logseq 89, block 7296

Recovery of Online Redo Log: Thread 1 Group 2 Seq 89 Reading mem 0

  Mem# 0: /u01/app/oracle/oradata/TestDB11/redo02.log

Completed redo application of 0.11MB

Completed crash recovery at

 Thread 1: logseq 89, block 7558, scn 2572507

 68 data blocks read, 68 data blocks written, 131 redo k-bytes read

Thu Aug 08 17:54:37 2013

LGWR: STARTING ARCH PROCESSES

Thu Aug 08 17:54:37 2013

ARC0 started with pid=20, OS id=2219

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Thu Aug 08 17:54:38 2013

ARC1 started with pid=21, OS id=2221

Thu Aug 08 17:54:38 2013

ARC2 started with pid=22, OS id=2223

ARC1: Archival started

ARC2: Archival started

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

Thu Aug 08 17:54:38 2013

ARC3 started with pid=23, OS id=2225

Thread 1 advanced to log sequence 90 (thread open)

Thread 1 opened at log sequence 90

  Current log# 3 seq# 90 mem# 0: /u01/app/oracle/oradata/TestDB11/redo03.log

Successful open of redo thread 1

Thu Aug 08 17:54:38 2013

SMON: enabling cache recovery

Archived Log entry 1 added for thread 1 sequence 89 ID 0x99b61782 dest 1:

[2215] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:12714081 end:12714422 diff:341 (3 seconds)

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is AL32UTF8

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Thu Aug 08 17:54:39 2013

QMNC started with pid=24, OS id=2227

Completed: alter database open

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Thu Aug 08 17:54:40 2013

db_recovery_file_dest_size of 4122 MB is 0.09% 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.

Thu Aug 08 17:54:40 2013

Starting background process CJQ0

Thu Aug 08 17:54:40 2013

CJQ0 started with pid=27, OS id=2241