ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SCN与oracle启动

SCN与oracle启动

原创 Linux操作系统 作者:听海★蓝心梦 时间:2012-02-09 14:57:10 0 删除 编辑


一、SCN简介
        在数据库启动过程中,当System Checkpoint SCN、Datafile Checkpoint SCN和Start SCN号都相同时,数据库可以正常启动,不需要做media recovery。三者当中有一个不同时,则需要做media recovery。如果在启动的过程中,End SCN号为NULL,则需要做instance recovery。这种情况一般是数据库不正常关闭导致。ORACLE在启动过程中首先检查是否需要media recovery,然后再检查是否需要instance recovery。


二、测试过程


1、查看正常关闭/启动过程
        数据库正常关闭,在数据库启动过程中:当System Checkpoint SCN、Datafile Checkpoint SCN和Start SCN号都相同时,数据库可以正常启动
SQL> shutdown normal
Database dismounted.
ORACLE instance shut down.


SQL> startup mount;
ORACLE instance started.
Total System Global Area  101785252 bytes
Fixed Size                   454308 bytes
Variable Size              75497472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.


SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
    229935


SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE
------------------
    229935
    229935
    229935
    229935
    229935


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

CHECKPOINT_CHANGE#  LAST_CHANGE#
--------------------------  --------------
   229935          229935
   229935          229935
   229935          229935
   229935          229935
   229935          229935


SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
-----------------------------
     229935
     229935
     229935
    229935
    229935


SQL> alter database open;
Database altered.


        看此时database的日志文件:
Completed: ALTER DATABASE   MOUNT
Tue Mar 18 08:54:19 2008
alter database open
Tue Mar 18 08:54:19 2008
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 opened at log sequence 15
Current log# 3 seq# 15 mem# 0: D:\ORACLE\ORADATA\PUBTEST\REDO03.LOGITPUB
Successful open of redo thread 1.
Tue Mar 18 08:54:19 2008
SMON: enabling cache recovery
Tue Mar 18 08:54:20 2008
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
 
 Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Tue Mar 18 08:54:20 2008
SMON: enabling tx recovery
Tue Mar 18 08:54:20 2008
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open
        可以看到数据库正常启动没有做任何instance recovery,和media recovery


2、END SCN为NULL
        如果在启动的过程中,End SCN号为NULL,则需要做instance recovery. 这种情况一般是数据库不正常关闭导致  测试如下:


SQL>shutdown abort;
SQL> startup mount;
ORACLE instance started.
Total System Global Area  101785252 bytes
Fixed Size                   454308 bytes
Variable Size              75497472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.


SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
     229936
     229936
     229936
     229936
     229936


SQL> select checkpoint_change#,last_change# from v$datafile;
CHECKPOINT_CHANGE#  LAST_CHANGE#
------------------     ------------ 
     229936  
     229936
     229936
     229936
     229936           ----可以看到last_change# 下面的内容为null


SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
      229936


SQL> alter database open;  -------在这个过程中做了实例恢复
Database altered.
       


        此时日志文件中记录如下:
Shutting down instance (abort)
License high water mark = 1
Instance terminated by USER, pid = 5976
Tue Mar 18 08:56:21 2008
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
  processes                = 150
  timed_statistics         = TRUE
  shared_pool_size         = 50331648
  large_pool_size          = 8388608
  java_pool_size           = 0
  control_files            = D:\oracle\oradata\pubtest\control01.ctl, D:\oracle\oradata\pubtest\control02.ctl, D:\oracle\oradata\pubtest\control03.ctl
  db_block_size            = 8192
  db_cache_size            = 25165824
  compatible               = 9.2.0.0.0
  log_archive_start        = TRUE
  db_file_multiblock_read_count= 16
  fast_start_mttr_target   = 300
  log_checkpoints_to_alert = TRUE
  undo_management          = AUTO
 undo_tablespace          = UNDOTBS1]
  undo_retention           = 10800]
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  instance_name            = pubtest
  hash_join_enabled        = TRUE
  background_dump_dest     = D:\oracle\admin\pubtest\bdump
  user_dump_dest           = D:\oracle\admin\pubtest\udump
  core_dump_dest           = D:\oracle\admin\pubtest\cdump
  sort_area_size           = 524288
  db_name                  = pubtest
  open_cursors             = 300
  star_transformation_enabled= FALSE
  pga_aggregate_target     = 25165824
  PMON started with pid=2
  DBW0 started with pid=3
  LGWR started with pid=4
  CKPT started with pid=5
  SMON started with pid=6
  RECO started with pid=7
  Tue Mar 18 08:56:23 2008
  ARCH: STARTING ARCH PROCESSES
  ARC0 started with pid=8I
  ARC0: Archival started
  ARC1 started with pid=9
  ARC1: Archival started
  Tue Mar 18 08:56:23 2008
ARCH: STARTING ARCH PROCESSES COMPLETE
Tue Mar 18 08:56:23 2008
ARC1: Thread not mounted
Tue Mar 18 08:56:24 2008
ALTER DATABASE   MOUNT
Tue Mar 18 08:56:24 2008
ARC0: Thread not mounted
Tue Mar 18 08:56:29 2008
Successful mount of redo thread 1, with mount id 794635960.
Tue Mar 18 08:56:29 2008
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
Tue Mar 18 08:57:12 2008
alter database open
Tue Mar 18 08:57:12 2008
Beginning crash recovery of 1 threads
Tue Mar 18 08:57:12 2008
Started first pass scan
Tue Mar 18 08:57:13 2008
Completed first pass scan
 82 redo blocks read, 49 data blocks need recovery
Tue Mar 18 08:57:13 2008
Started recovery at
 Thread 1: logseq 15, block 809, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 3 Seq 15 Reading mem 0 --当前日志文件做实例恢复instance   recovery
  Mem# 0 errs 0: D:\ORACLE\ORADATA\PUBTEST\REDO03.LOG
Tue Mar 18 08:57:13 2008
Completed redo application
Tue Mar 18 08:57:13 2008
Ended recovery at
Thread 1: logseq 15, block 891, scn 0.250029
 49 data blocks read, 49 data blocks written, 82 redo blocks read
Crash recovery completed successfully
Tue Mar 18 08:57:13 2008
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 16
Thread 1 opened at log sequence 16
  Current log# 1 seq# 16 mem# 0: D:\ORACLE\ORADATA\PUBTEST\REDO01.LOG
Successful open of redo thread 1.
Tue Mar 18 08:57:14 2008
SMON: enabling cache recovery
Tue Mar 18 08:57:14 2008
ARC0: Evaluating archive   log 3 thread 1 sequence 15
?ARC0: Beginning to archive log 3 thread 1 sequence 15
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\ORA92\RDBMS\ARC00015.001'  --并且做了日志切换和归档
kccrsz: expanded controlfile section 11 from 13 to 27 records
  requested to grow by 5 record(s); added 1 block(s) of records
ARC0: Completed archiving  log 3 thread 1 sequence 15
Tue Mar 18 08:57:15 2008
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Successfully onlined Undo Tablespace 1.
Tue Mar 18 08:57:15 2008
SMON: enabling tx recovery
Tue Mar 18 08:57:15 2008
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open

3、SCN不同时恢复
        oracle在启动过程中三者当中有一个不同时,则需要做media recovery,步骤是:首先检查是否需要media recovery,然后再检查是否需要instance recovery.测试如下;


SQL> alter tablespace tools begin backup;
Tablespace altered.   ---时拷贝tools01.dbf 到别处

SQL> create table tools (id number) tablespace tools;
Table created.

SQL> alter tablespace tools end backup;
Tablespace altered.

SQL> create table tools (id number) tablespace tools;
Table created.

SQL> insert into tools values(10);
1 row created.

SQL> commit;
Commit complete.

SQL> shutdown abort;
ORACLE instance shut down.      --- 拷贝 tools01.dbf 回来

SQL> startup mount;
ORACLE instance started.
Total System Global Area  101785252 bytes
Fixed Size                   454308 bytes
Variable Size              75497472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.

SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
     251084

SQL> select checkpoint_change#,last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
            251084
            251084
            251084
            251084
            251084

SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
            251084
            251084
            251084
            250867
            251084
        可以看到 数据文件头的最小scn为250867,小于数据文件的scn 251084,和系统检查号为scn 251084。

SQL> alter database open;    --提示做介质恢复
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: 'D:\ORACLE\ORADATA\PUBTEST\TOOLS01.DBF'

SQL> recover datafile 4;
Media recovery complete.

SQL> alter database open;
Database altered. 
        此时日志文件中内容如下:
ALTER DATABASE RECOVER  datafile 4
Media Recovery Datafile: 4
Media Recovery Start
Starting datafile 4 recovery in thread 1 sequence 16
Datafile 4: 'D:\ORACLE\ORADATA\PUBTEST\TOOLS01.DBF'
Media Recovery Log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 16 Reading mem 0
Mem# 0 errs 0: D:\ORACLE\ORADATA\PUBTEST\REDO01.LOG
Recovery of Online Redo Log: Thread 1 Group 2 Seq 17 Reading mem 0
Mem# 0 errs 0: D:\ORACLE\ORADATA\PUBTEST\REDO02.LOG
Media Recovery Complete
Completed: ALTER DATABASE RECOVER  datafile 4
Tue Mar 18 09:48:50 2008
alter database open
Tue Mar 18 09:48:50 2008
  Beginning crash recovery of 1 threads
Tue Mar 18 09:48:50 2008
Started first pass scan
Tue Mar 18 09:48:52 2008
Completed first pass scan
 83 redo blocks read, 50 data blocks need recovery
Tue Mar 18 09:48:52 2008
Started recovery at
Thread 1: logseq 17, block 29, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 2 Seq 17 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\ORADATA\PUBTEST\REDO02.LOG
Tue Mar 18 09:48:52 2008
Completed redo application
Tue Mar 18 09:48:52 2008
Ended recovery at
 Thread 1: logseq 17, block 112, scn 0.271189
 50 data blocks read, 50 data blocks written, 83 redo blocks read
Crash recovery completed successfully
Tue Mar 18 09:48:53 2008
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 18
Thread 1 opened at log sequence 18
  Current log# 3 seq# 18 mem# 0: D:\ORACLE\ORADATA\PUBTEST\REDO03.LOG
Successful open of redo thread 1.
Tue Mar 18 09:48:54 2008
SMON: enabling cache recovery
Tue Mar 18 09:48:54 2008
ARC0: Evaluating archive   log 2 thread 1 sequence 17
ARC0: Beginning to archive log 2 thread 1 sequence 17
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\ORA92\RDBMS\ARC00017.001'
ARC0: Completed archiving  log 2 thread 1 sequence 17
Tue Mar 18 09:48:54 2008
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Tue Mar 18 09:48:54 2008
SMON: enabling tx recovery
Tue Mar 18 09:48:54 2008
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/751371/viewspace-715956/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-18

  • 博文量
    256
  • 访问量
    1188492