SCN与oracle启动

最新推荐文章于 2021-05-29 23:17:34 发布
最新推荐文章于 2021-05-29 23:17:34 发布
阅读量157 收藏
点赞数
文章标签： 数据库

一、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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/751371/viewspace-715956/

cnqt2015
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
ORACLE SCN问题解析
07-26
在某些环境下，程序bug会引起ORACLE数据库SCN的异常增长，超出ORACLE设定的合理范围，该事件很可能导致宕机，并且由于企业内部存在众多的分布式事务，引起SCN的剧烈增长很容易出现扩散现象，导致更多的数据库宕机。强烈建议相关数据库安装2012年1月以后的PSU以及one-off补丁，设置参数来缓解。同时建议查找应用端分布式事务的并发源头，合理利用并发性等，减少SCN剧烈增长的原因。这个问题已经出现在客户环境中，需要引起大家的足够重视
Oracle数据库启动过程验证检查点SCN
congxinlan3332的博客
02-21 266
SCN（System Change Number，系统改变号）是Oracle数据库非常重要的机制。SCN在数据库启动，读一致性，数据的恢复，FLASHBACK操作等情况下都会用到。 1.SC...
Oracle SCN机制解析
08-17
SCN（System Chang Number）作为oracle中的一个重要机制，在数据恢复、Data Guard、Streams复制、RAC节点间的同步等各个功能中起着重要作用。理解SCN的运作机制，可以帮助你更加深入地了解上述功能。
个人经验总结：Oracle数据库SCN号详解
01-06
Oracle数据库SCN号详解 系统检查点scn) 数据文件检查点（v$datafile） 数据文件终止scn) 数据文件中存放的检查点 启动scn
Oracle SCN详解
04-22
在 shutdown normal 或 shutdown immediate 下，Oracle 会将所有未提交的交易回滚，并将所有数据文件的头信息更新到最新的 SCN 号，以便在下次启动时可以快速恢复数据库。 SCN 是 Oracle 中一种非常重要的机制，...
oracle scn 详解
03-08
### Oracle SCN 详解 #### 一、SCN概念解析 **SCN (System Change Number)** 是Oracle数据库中用于标识数据库状态变化的一种内部编号机制。它是一个递增的数字，每当数据库发生更新操作时，SCN就会增加。这个机制...
oracle scn修改利器-Patch SCN最新版-202407
最新发布
07-05
该软件是惜分飞（https://www.xifenfei.com）开发，仅用来查看和修改Oracle...特别是Oracle新版本中使用隐含参数,event,oradebug等方法无法推进Oracle SCN的情况下，使用该工具能够快速修改SCN,实现数据库启动成功.
验证oracle是否成功,通过修改SCN来骗过ORACLE启动验证
weixin_32767591的博客
04-08 142
实验过程的整理，还没有添加解释说明，后面有时间再整理。sys>create tablespace users datafile '/data1/oracle/crmd/users01.dbf' size 20m;Tablespace created.sys>shutdown immediateDatabase closed.Database dismounted.ORACLE inst...
oracle 提升scn强制打开数据库
namu
04-30 1845
由于开发人员强制关闭数据库，导致数据文件不一致，并且采用的是老的备份方式，alter database begin backup的方式，由于以前有备份失败，一直没有end backup，启动数据库时，找很久前的归档，这些归档都已经被删除，无法启动数据库，尝试多种方式无效            利用*._allow_resetlogs_corruption=true
SP2-0103: Nothing in SQL buffer to run.
12-24 2088
今天在测试脚本的时候发现一个告警信息： [oracle@RACDB01 scripts]$ ./sendmail.sh 上次行数：11813 本次行数：11833 检测到错误，发邮件 SP2-0103: Nothing...
Oracle使用sqlplus后台执行遇到的问题SP2-0103
yyf960126的博客
05-29 900
${sqls}=sql语句 sqlplus -S oracle连接 <<END -- -S代表静默执行 set echo off feedback off heading off underline off; ${sqls}或者@sql文件名 quit; END 运行sql文件的形式的时候报错SP2-0103 因为sql中写了注释 eg：/*注释内容*/ 我把注释改成了--注释内容，并且每回运行之前处理一下每行开头的字符，使用sed命令，sed -i 's/^\s*/...
一种快速批量创建子版本的方法
积思园
03-12 2966
经常使用ArcGIS版本编辑的用户经常会为随着时间越来越长，查询和编辑效率下降所苦恼，而且用户也都明白只能使用版本压缩来解决这个问题，但是想要彻底的进行版本压缩，首先必须将相关的子版本名称给删除掉，这是让人很头疼的事情，因为有些用户的业务是多用户编辑的，可能这个业务创建了几十个版本，也有相关的父子版本，那么每次都要删除再重新创建，肯定会让用户崩溃的，但是不删除又不能彻底的进行版本压缩，这就成为了一
oracle 系统操作语句(system)
der322的博客
10-27 1033
查询当前实例的状态：select status from v$instance; (正常结果：OPEN) 查询当前数据库的打开模式：select open_mode from v$database; (正常结果：READ WRITE) 查询当前scn：select to_char(current_scn) from v$database; 查询所有的schema： select use...
0103
北极企鹅
01-31 300
0103
[转] oracle 数据库 SQL plus 连接方法
weixin_30835649的博客
04-29 138
http://hi.baidu.com/zzy382/item/a5b197f97a38e01ba7298832 之前电脑上安装了一个 Oracle 有一段时间没用，就把密码给忘了，按上面链接里的操作，重置密码成功，步骤如下： connect system/oracle以 system 用户 oracle 密码连接 在进入 sql plus 界面后输入 / as oraclesys...
SQL*Plus Error Messages
weixin_30808693的博客
05-20 1187
This appendix lists error messages with prefixes SP2- and CPY- generated by SQL*Plus and iSQL*Plus: SQL*Plus Error Messages iSQL*Plus Error Messages COPY Command Messages For error...
Oracle数据库启动与关闭：控制文件详解
在Nomount状态下，Oracle仅启动实例，分配内存并启动后台进程，但不加载数据文件。在Mount状态下，Oracle会加载控制文件，并识别数据库的结构。最后，在Open状态下，数据库准备好接受用户连接和数据访问。 启动...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值