[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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/429786/viewspace-776896/