判斷資料庫是否需要例項恢復

[root@vmrac1 ~]# su - oracle
[oracle@vmrac1 ~]$ sqlplus / as sysdba

-- system checkpoint SCN
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
           1330820

-- datafile checkpoint SCN
SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
           1332188
           1332188
           1332188
           1332188
           1332188
           1332188

6 rows selected.

-- Start SCN
SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
           1332188
           1332188
           1332188
           1332188
           1332188
           1332188

6 rows selected.

-- End SCN
SQL> col name for a50
SQL> select name,last_change# from v$datafile;

NAME                                               LAST_CHANGE#
-------------------------------------------------- ------------
+DATA/rac/datafile/system.256.829110499
+DATA/rac/datafile/sysaux.257.829110501
+DATA/rac/datafile/undotbs1.258.829110501
+DATA/rac/datafile/users.259.829110501
+DATA/rac/datafile/example.267.829110655
+DATA/rac/datafile/undotbs2.268.829111091

6 rows selected.

-- duration database running End SCN is null

SQL> select name,fuzzy from v$datafile_header;

NAME                                               FUZ
-------------------------------------------------- ---
+DATA/rac/datafile/system.256.829110499            YES
+DATA/rac/datafile/sysaux.257.829110501            YES
+DATA/rac/datafile/undotbs1.258.829110501          YES
+DATA/rac/datafile/users.259.829110501             YES
+DATA/rac/datafile/example.267.829110655           YES
+DATA/rac/datafile/undotbs2.268.829111091          YES

6 rows selected.

SQL> select thread#,sequence#,first_change#,next_change# from v$log order by 1,2;

   THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
         1          8       1174330      1263670
         1          9       1263670   2.8147E+14
         2          4       1232702      1283679
         2          5       1283679      1330825

SQL> alter system checkpoint;

System altered.

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
           1335465
           1335465
           1335465
           1335465
           1335465
           1335465

6 rows selected.

SQL> select checkpoint_change#,last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
           1335465
           1335465
           1335465
           1335465
           1335465
           1335465

6 rows selected.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
           1335465

SQL> select thread#,sequence#,first_change#,next_change# from v$log order by 1,2;

   THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
         1          8       1174330      1263670
         1          9       1263670   2.8147E+14
         2          4       1232702      1283679
         2          5       1283679      1330825

-- case
-- when a.checkpoint_change# = b.checkpoint_change#
--  then 'startup normal'
-- when a.checkpoint_change# > b.checkpoint_change#
--  then 'media recovery'
-- when a.checkpoint_change# < b.checkpoint_change#
--  then 'old control file'
-- from v$datafile a     #controlfile SCN for datafile
-- v$datafile_header b   #datafile_header SCN

-- 通過以下儲存過程直接查詢
SQL> grant select on v_$datafile to scott;

Grant succeeded.

SQL> grant select on v_$datafile_header to scott;

Grant succeeded.

SQL> create or replace procedure recover_state as
  result varchar2(100);
  num number(10);
  begin
    select a.checkpoint_change#-b.checkpoint_change# into num from sys.v_$datafile a,sys.v_$datafile_header b where a.file#=b.file# and a.file#=
1;
    result:=
    case
     when num=0 then ('Instance Startup Normal.')
     when num>0 then ('Need Media Recovery.')
     else ('Old Controlfile.')
    end;
  dbms_output.put_line(result);
 end;
/
Procedure created.

SQL> set serveroutput on
SQL> execute recover_state;
Instance Startup Normal.

PL/SQL procedure successfully completed.

SQL> col instance for a20
SQL> select thread#,status,enabled,instance,open_time,checkpoint_change# from v$thread;

   THREAD# STATUS ENABLED  INSTANCE             OPEN_TIME    CHECKPOINT_CHANGE#
---------- ------ -------- -------------------- ------------ ------------------
         1 OPEN   PUBLIC   rac1                 28-OCT-13               1335465
         2 CLOSED PUBLIC   rac2                 27-OCT-13               1317632

-- check v$thread.status at database mount stage
-- select a.thread#,b.open_mode,a.status
-- case
-- when b.open_mode='mounted' and a.status='open'
--    then 'crash recovery Request.'
-- when b.open_mode='mounted' and a.status='closed'
--    then 'no crash Recocvery Request.'
-- when b.open_mode='read write' and a.status='open'
--    then 'Instance already open.'
-- end status
-- from v$thread# a,v$database b,v$instance c
-- where a.thread#=c.thread#

SQL> select a.thread#,b.open_mode,a.status from v$thread a,v$database b,v$instance c where a.thread#=c.thread#;

   THREAD# OPEN_MODE            STATUS
---------- -------------------- ------
         1 READ WRITE           OPEN

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  463478784 bytes
Fixed Size                  2229384 bytes
Variable Size             213912440 bytes
Database Buffers          239075328 bytes
Redo Buffers                8261632 bytes
Database mounted.

-- no crash recovery request like below
SQL> select a.thread#,b.open_mode,a.status from v$thread a,v$database b,v$instance c where a.thread#=c.thread#;

   THREAD# OPEN_MODE        STATUS
   ----------   --------------------   ----------
          1       MOUNTED           CLOSED

SQL> alter database open;

Database altered.

-- Instance already open like below
SQL> select a.thread#,b.open_mode,a.status from v$thread a,v$database b,v$instance c where a.thread#=c.thread#;

   THREAD# OPEN_MODE      STATUS
   ---------- --------------------   ----------
         1     READ WRITE         OPEN

-- instance recovery is automatic run,a dba nothing to do
-- instance recovery do something
-- a.rollover from online redo log
-- b.open database
-- c.SMON or user process do rollback

例項恢復的啟動過程及所有資訊可以從alert日誌檔案中檢視。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/429786/viewspace-776896/，如需轉載，請註明出處，否則將追究法律責任。