sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle重建控制文件时resetlogs和noresetlogs区别

时间:2016-12-03 12:38   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库在归档和非归档模式下重建控制文件采用resetlogs和noresetlogs的区别对比。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: About recreate controlfile

上午群里网友讨论了一下关于controlfile重建的问题,曾经的一次恢复(记一次非归档RAC恢复(大量的ORA-600 错误))的印象中发现对于resetlogs和noresetlogs是有点不同的,于是这里就做了一个简单的测试:

------归档模式

SQL> conn /AS sysdba
Connected.
SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed SIZE                  1272600 bytes
Variable SIZE             109053160 bytes
DATABASE Buffers           54525952 bytes
Redo Buffers                2920448 bytes
DATABASE mounted.

SQL> archive log list;
DATABASE log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/ora10g/archivelog
Oldest online log SEQUENCE     358
NEXT log SEQUENCE TO archive   360
CURRENT log SEQUENCE           360

SQL> SELECT checkpoint_change# FROM v$database;

CHECKPOINT_CHANGE#
------------------
           4043243

SQL> SELECT file# , CHECKPOINT_CHANGE# FROM v$datafile_header ORDER BY 1;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            4043243
         2            4043243
         3            4043243
         4            4043243
         5            4043243
         6            4043243
         7            4043243
         8            4043243
         9            4043243

9 ROWS selected.

SQL> SET LINES 200
SQL> SELECT hxfil,hxsts,fhtyp,fhscn,fhprc,fhprs,fhrls,fhrlc,fhcsq,fhcpc,fhccc FROM x$kcvfh ORDER BY 1;

     HXFIL HXSTS                 FHTYP FHSCN            FHPRC                FHPRS            FHRLS            FHRLC                     FHCSQ      FHCPC      FHCCC
---------- ---------------- ---------- ---------------- -------------------- ---------------- ---------------- -------------------- ---------- ---------- ----------
         1 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        754        753
         2 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        718        717
         3 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        754        753
         4 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        753        752
         5 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        523        522
         6 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        525        524
         7 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        405        404
         8 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        206        205
         9 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        110        109

9 ROWS selected.

—-noresetlogs 重建controlfile

SQL> shutdown immediate;
ORA-01109: DATABASE NOT OPEN

DATABASE dismounted.
ORACLE instance shut down.

SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ROGER" noresetlogs  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/home/ora10g/oradata/roger/redo01.log'  SIZE 50M,
  GROUP 2 '/home/ora10g/oradata/roger/redo02.log'  SIZE 50M,
  GROUP 3 '/home/ora10g/oradata/roger/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/home/ora10g/oradata/roger/system01.dbf',
  '/home/ora10g/oradata/roger/undotbs01.dbf',
  '/home/ora10g/oradata/roger/sysaux01.dbf',
  '/home/ora10g/oradata/roger/users01.dbf',
  '/home/ora10g/oradata/roger/roger01.dbf',
  '/home/ora10g/oradata/roger/roger02.dbf',
  '/home/ora10g/oradata/roger/test1.dbf',
  '/home/ora10g/oradata/roger/sqlt_01.dbf',
  '/home/ora10g/oradata/roger/undotbs03.dbf'
CHARACTER SET ZHS16GBK
;ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed SIZE                  1272600 bytes
Variable SIZE             109053160 bytes
DATABASE Buffers           54525952 bytes
Redo Buffers                2920448 bytes

SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23 
Control file created.

SQL> SELECT STATUS FROM v$instance;

STATUS
------------
MOUNTED

SQL> SELECT checkpoint_change# FROM v$database;

CHECKPOINT_CHANGE#
------------------
           4043243

SQL> SELECT file# , CHECKPOINT_CHANGE# FROM v$datafile_header ORDER BY 1;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            4043243
         2            4043243
         3            4043243
         4            4043243
         5            4043243
         6            4043243
         7            4043243
         8            4043243
         9            4043243

9 ROWS selected.

SQL> SET LINES 200
SQL> SELECT hxfil,hxsts,fhtyp,fhscn,fhprc,fhprs,fhrls,fhrlc,fhcsq,fhcpc,fhccc FROM x$kcvfh ORDER BY 1;

     HXFIL HXSTS                 FHTYP FHSCN            FHPRC  Oracleо              FHPRS            FHRLS            FHRLC                     FHCSQ      FHCPC      FHCCC
---------- ---------------- ---------- ---------------- -------------------- ---------------- ---------------- -------------------- ---------- ---------- ----------
         1 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        754        753
         2 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        718        717
         3 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        754        753
         4 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        753        752
         5 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        523        522
         6 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        525        524
         7 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        405        404
         8 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        206        205
         9 4043243                   3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        110        109

9 ROWS selected.


-----resetlogs 方式重建controlfile

SQL> shutdown immediate;
ORA-01109: DATABASE NOT OPEN


DATABASE dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ROGER" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/home/ora10g/oradata/roger/redo01.log'  SIZE 50M,
  GROUP 2 '/home/ora10g/oradata/roger/redo02.log'  SIZE 50M,
  GROUP 3 '/home/ora10g/oradata/roger/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/home/ora10g/oradata/roger/system01.dbf',
  '/home/ora10g/oradata/roger/undotbs01.dbf',
  '/home/ora10g/oradata/roger/sysaux01.dbf',
  '/home/ora10g/oradata/roger/users01.dbf',
  '/home/ora10g/oradata/roger/roger01.dbf',
  '/home/ora10g/oradata/roger/roger02.dbf',
  '/home/ora10g/oradata/roger/test1.dbf',
  '/home/ora10g/oradata/roger/sqlt_01.dbf',
  '/home/ora10g/oradata/roger/undotbs03.dbf'
CHARACTER SET ZHS16GBK
;ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed SIZE                  1272600 bytes
Variable SIZE             109053160 bytes
DATABASE Buffers           54525952 bytes
Redo Buffers                2920448 bytes

SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23
Control file created

SQL> SELECT checkpoint_change# FROM v$database;

CHECKPOINT_CHANGE#
------------------
                 0

SQL> SELECT file# , CHECKPOINT_CHANGE# FROM v$datafile_header ORDER BY 1;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            4043243
         2            4043243
         3            4043243
         4            4043243
         5            4043243
         6            4043243
         7            4043243
         8            4043243
         9            4043243

9 ROWS selected.

SQL> SET LINES 200
SQL> SELECT hxfil,hxsts,fhtyp,fhscn,fhprc,fhprs,fhrls,fhrlc,fhcsq,fhcpc,fhccc FROM x$kcvfh ORDER BY 1;

     HXFIL HXSTS                 FHTYP FHSCN            FHPRC                FHPRS            FHRLS            FHRLC                     FHCSQ      FHCPC      FHCCC
---------- ---------------- ---------- ---------------- -------------------- ---------------- ---------------- -------------------- ---------- ---------- ----------
         1 281474976710655           3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        754        753
         2 281474976710655           3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        718        717
         3 281474976710655           3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        754        753
         4 281474976710655           3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        753        752
         5 281474976710655           3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        523        522
         6 281474976710655           3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        525        524
         7 281474976710655           3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        405        404
         8 281474976710655           3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        206        205
         9 281474976710655           3 4043243          10/20/2011 07:40:58  666180           848661           11/03/2011 02:05:04        5037        110        109

9 ROWS selected.

SQL> ALTER DATABASE OPEN RESETLOGS;

DATABASE altered.


SQL> archive log list;
DATABASE log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/ora10g/archivelog
Oldest online log SEQUENCE     1
NEXT log SEQUENCE TO archive   1
CURRENT log SEQUENCE           1

SQL> SELECT checkpoint_change# FROM v$database;

CHECKPOINT_CHANGE#
------------------
           4043247

SQL> SELECT file# , CHECKPOINT_CHANGE# FROM v$datafile_header ORDER BY 1;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            4043247
         2            4043247
         3            4043247
         4            4043247
         5            4043247
         6            4043247
         7            4043247
         8            4043247
         9            4043247

9 ROWS selected.

SQL> SELECT hxfil,hxsts,fhtyp,fhscn,fhprc,fhprs,fhrls,fhrlc,fhcsq,fhcpc,fhccc FROM x$kcvfh ORDER BY 1;

     HXFIL HXSTS                 FHTYP FHSCN            FHPRC                FHPRS            FHRLS            FHRLC                     FHCSQ      FHCPC      FHCCC
---------- ---------------- ---------- ---------------- -------------------- ---------------- ---------------- -------------------- ---------- ---------- ----------
         1 281474976710655           3 4043247          11/03/2011 02:05:04  848661           4043244          07/10/2012 21:10:46        5053        756        755
         2 281474976710655           3 4043247          11/03/2011 02:05:04  848661           4043244          07/10/2012 21:10:46        5053        720        719
         3 281474976710655           3 4043247          11/03/2011 02:05:04  848661           4043244          07/10/2012 21:10:46        5053        756        755
         4 281474976710655           3 4043247          11/03/2011 02:05:04  848661           4043244          07/10/2012 21:10:46        5053        755        754
         5 281474976710655           3 4043247          11/03/2011 02:05:04  848661           4043244          07/10/2012 21:10:46        5053        525        524
         6 281474976710655           3 4043247          11/03/2011 02:05:04  848661           4043244          07/10/2012 21:10:46        5053        527        526
         7 281474976710655           3 4043247          11/03/2011 02:05:04  848661           4043244          07/10/2012 21:10:46        5053        407        406
         8 281474976710655           3 4043247          11/03/2011 02:05:04  848661           4043244          07/10/2012 21:10:46        5053        208        207
         9 281474976710655           3 4043247          11/03/2011 02:05:04  848661           4043244          07/10/2012 21:10:46        5053        112        111

9 ROWS selected.

SQL> SELECT hxfil,hxsts,fhtyp,fhscn,FHRBA_SEQ        FROM x$kcvfh ORDER BY 1;

     HXFIL HXSTS                 FHTYP FHSCN             FHRBA_SEQ
---------- ---------------- ---------- ---------------- ----------
         1 281474976710655           3 4043247                   1
         2 281474976710655           3 4043247                   1
         3 281474976710655           3 4043247                   1
         4 281474976710655           3 4043247                   1
         5 281474976710655           3 4043247                   1
         6 281474976710655           3 4043247                   1
         7 281474976710655           3 4043247                   1
         8 281474976710655           3 4043247                   1
         9 281474976710655           3 4043247                   1

9 ROWS selected.
从上面实验来看,说明了2点;

1. 重建controlfile,controlfile中datafile scn来源于datafile header。
2. 针对归档模式,resetlogs和noresetlogs重建controlfile本质上没有任何不同,仅仅是log sequence被重置了而已,仅此而已。

——非归档模式

SQL> archive log list;
DATABASE log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /home/oracle/archivelog
Oldest online log SEQUENCE     10
CURRENT log SEQUENCE           12

SQL> SELECT checkpoint_change# FROM v$database;

CHECKPOINT_CHANGE#
------------------
           3532673

SQL> SELECT file# , CHECKPOINT_CHANGE# FROM v$datafile_header ORDER BY 1;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            3532673
         2            3532673
         3            3532673
         4            3532673
         5            3532673
         6            3532673
         7            3532673

7 ROWS selected.

SQL> SET LINES 200
SQL> SELECT hxfil,hxsts,fhtyp,fhscn,fhprc,fhprs,fhrls,fhrlc,fhcsq,fhcpc,fhccc FROM x$kcvfh ORDER BY 1;

     HXFIL HXSTS                 FHTYP FHSCN            FHPRC                FHPRS            FHRLS            FHRLC                     FHCSQ      FHCPC      FHCCC
---------- ---------------- ---------- ---------------- -------------------- ---------------- ---------------- -------------------- ---------- ---------- ----------
         1 3532673                   3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         75         74
         2 3532673                   3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         38         37
         3 3532673                   3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         75         74
         4 3532673                   3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         75         74
         5 3532673                   3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         26         25
         6 3532673                   3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         17         16
         7 3532673                   3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         11         10

7 ROWS selected.

----noresetlogs方式重建controlfile

SQL> shutdown immediate;
ORA-01109: DATABASE NOT OPEN

DATABASE dismounted.
ORACLE instance shut down.

SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "10GASM" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA01/10gasm/onlinelog/group_1.261.776886835'  SIZE 50M,
  GROUP 2 '+DATA01/10gasm/onlinelog/group_2.262.776886835'  SIZE 50M,
  GROUP 3 '+DATA01/10gasm/onlinelog/group_3.263.776886837'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '+DATA01/10gasm/datafile/system.256.776886753',
  '+DATA01/10gasm/datafile/undotbs1.258.776886753',
  '+DATA01/10gasm/datafile/sysaux.257.776886753',
  '+DATA01/10gasm/datafile/users.259.776886755',
  '+DATA02/10gasm/datafile/roger.256.777429425',
  '+DATA02/10gasm/datafile/test.257.777779169',
  '+DATA01/10gasm/datafile/testasm.267.785448525'
CHARACTER SET ZHS16GBK
;ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed SIZE                  1218556 bytes
Variable SIZE              79693828 bytes
DATABASE Buffers          125829120 bytes
Redo Buffers                2973696 bytes

SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21 

Control file created.

SQL> archive log list;
DATABASE log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /home/oracle/archivelog
Oldest online log SEQUENCE     10
CURRENT log SEQUENCE           12

SQL> SELECT checkpoint_change# FROM v$database;

CHECKPOINT_CHANGE#
------------------
           3532673

SQL> SELECT file# , CHECKPOINT_CHANGE# FROM v$datafile_header ORDER BY 1;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            3532673
         2            3532673
         3            3532673
         4            3532673
         5            3532673
         6            3532673
         7            3532673

7 ROWS selected.

SQL> SELECT hxfil,hxsts,fhtyp,fhscn,fhprc,fhprs,fhrls,fhrlc,fhcsq,fhcpc,fhccc FROM x$kcvfh ORDER BY 1;

     HXFIL HXSTS                 FHTYP FHSCN            FHPRC                FHPRS            FHRLS            FHRLC                     FHCSQ      FHCPC      FHCCC
---------- ---------------- ---------- ---------------- -------------------- ---------------- ---------------- -------------------- ---------- ---------- ----------
         1 3532673                   3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         75         74
         2 3532673                   3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         38         37
         3 3532673                   3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         75         74
         4 3532673                   3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         75         74
         5 3532673                   3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         26         25
         6 3532673                   3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         17         16
         7 3532673                   3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         11         10

7 ROWS selected.

----resetlogs方式重建controlfile

SQL> shutdown immediate;
ORA-01109: DATABASE NOT OPEN


DATABASE dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "10GASM" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA01/10gasm/onlinelog/group_1.261.776886835'  SIZE 50M,
  GROUP 2 '+DATA01/10gasm/onlinelog/group_2.262.776886835'  SIZE 50M,
  GROUP 3 '+DATA01/10gasm/onlinelog/group_3.263.776886837'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '+DATA01/10gasm/datafile/system.256.776886753',
  '+DATA01/10gasm/datafile/undotbs1.258.776886753',
  '+DATA01/10gasm/datafile/sysaux.257.776886753',
  '+DATA01/10gasm/datafile/users.259.776886755',
  '+DATA02/10gasm/datafile/roger.256.777429425',
  '+DATA02/10gasm/datafile/test.257.777779169',
  '+DATA01/10gasm/datafile/testasm.267.785448525'
CHARACTER SET ZHS16GBK
;ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed SIZE                  1218556 bytes
Variable SIZE              79693828 bytes
DATABASE Buffers          125829120 bytes
Redo Buffers                2973696 bytes
SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21 

Control file created.

SQL> archive log list;
DATABASE log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /home/oracle/archivelog
Oldest online log SEQUENCE     0
CURRENT log SEQUENCE           0
SQL> SELECT checkpoint_change# FROM v$database;

CHECKPOINT_CHANGE#
------------------
                 0

SQL> SELECT file# , CHECKPOINT_CHANGE# FROM v$datafile_header ORDER BY 1;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            3532673
         2            3532673
         3            3532673
         4            3532673
         5            3532673
         6            3532673
         7            3532673

7 ROWS selected.

SQL>  SELECT checkpoint_change# FROM v$database;

CHECKPOINT_CHANGE#
------------------
                 0

SQL> SELECT hxfil,hxsts,fhtyp,fhscn,fhprc,fhprs,fhrls,fhrlc,fhcsq,fhcpc,fhccc FROM x$kcvfh ORDER BY 1;

     HXFIL HXSTS                 FHTYP FHSCN            FHPRC                FHPRS            FHRLS            FHRLC                     FHCSQ      FHCPC      FHCCC
---------- ---------------- ---------- ---------------- -------------------- ---------------- ---------------- -------------------- ---------- ---------- ----------
         1 281474976710655           3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         75         74
         2 281474976710655           3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         38         37
         3 281474976710655           3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         75         74
         4 281474976710655           3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         75         74
         5 281474976710655           3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         26         25
         6 281474976710655           3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         17         16
         7 281474976710655           3 3532673          06/30/2005 19:09:40  1                446075           03/02/2012 17:53:54        2727         11         10

7 ROWS selected.

SQL> ALTER DATABASE OPEN resetlogs;

DATABASE altered.

SQL> archive log list;
DATABASE log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /home/oracle/archivelog
Oldest online log SEQUENCE     0
CURRENT log SEQUENCE           1
SQL> SELECT checkpoint_change# FROM v$database;

CHECKPOINT_CHANGE#
------------------
           3532675

SQL> SELECT file# , CHECKPOINT_CHANGE# FROM v$datafile_header ORDER BY 1;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            3532675
         2            3532675
         3            3532675
         4            3532675
         5            3532675
         6            3532675
         7            3532675

7 ROWS selected.

SQL> SELECT hxfil,hxsts,fhtyp,fhscn,fhprc,fhprs,fhrls,fhrlc,fhcsq,fhcpc,fhccc FROM x$kcvfh ORDER BY 1;

     HXFIL HXSTS                 FHTYP FHSCN            FHPRC                FHPRS            FHRLS            FHRLC                     FHCSQ      FHCPC      FHCCC
---------- ---------------- ---------- ---------------- -------------------- ---------------- ---------------- -------------------- ---------- ---------- ----------
         1 281474976710655           3 3532675          03/02/2012 17:53:54  446075           3532674          07/10/2012 21:32:19        2736         77         76
         2 281474976710655           3 3532675          03/02/2012 17:53:54  446075           3532674          07/10/2012 21:32:19        2736         40         39
         3 281474976710655           3 3532675          03/02/2012 17:53:54  446075           3532674          07/10/2012 21:32:19        2736         77         76
         4 281474976710655           3 3532675          03/02/2012 17:53:54  446075           3532674          07/10/2012 21:32:19        2736         77         76
         5 281474976710655           3 3532675          03/02/2012 17:53:54  446075           3532674          07/10/2012 21:32:19        2736         28         27
         6 281474976710655           3 3532675          03/02/2012 17:53:54  446075           3532674          07/10/2012 21:32:19        2736         19         18
         7 281474976710655           3 3532675          03/02/2012 17:53:54  446075           3532674          07/10/2012 21:32:19        2736         13         12

7 ROWS selected.

SQL>
SQL> SELECT hxfil,hxsts,fhtyp,fhscn,FHRBA_SEQ        FROM x$kcvfh ORDER BY 1;

     HXFIL HXSTS                 FHTYP FHSCN             FHRBA_SEQ
---------- ---------------- ---------- ---------------- ----------
         1 281474976710655           3 3532675                   1
         2 281474976710655           3 3532675                   1
         3 281474976710655           3 3532675                   1
         4 281474976710655           3 3532675                   1
         5 281474976710655           3 3532675                   1
         6 281474976710655           3 3532675                   1
         7 281474976710655           3 3532675                   1

7 ROWS selected.

针对非归档模式,resetlogs和noresetlogs 方式重建controlfile的情况其实跟归档模式下没有什么不同,完全一致。只是需要注意的是,不管是非归档还是归档,只有进行open resetlogs操作之后,那么数据文
件头都中的如下信息会发生改变:

FHPRC FHPRS FHRLS FHRLC。

这里针对上述情况做一些描述补充:
HXSTS —controlfile stop scn

当数据库正常停止的时候,会把该值设置为跟检查点scn(fhscn)设置为一致,数据处于运行状态时,改值会被设置为最大值即:ffffffffffff 这是为什么呢?因为当数据运行时,oracle不知道你何时停止也就没法设置stop scn,只能设置为一个最大值,也就是 无穷大。

SQL> SELECT to_char(281474976710655,'xxxxxxxxxxxxx') FROM dual;

TO_CHAR(281474
--------------
  ffffffffffff

FHSCN —checkpoint scn

oracle在启动的时候,将datafile header 中的启动scn与datafile checkpoint scn进行比较,如果相等,接下来还要比较datafile header中的启动scn和控制文件中数据文件的stop scn,如果这个也相等,那么
oracle就认为这个datafile是正常的,不需要进行恢复操作。

如果oracle要对该datafile进行恢复,那么只需要恢复到fhscn值即可。

FHPRC —-Prior resetLogs timestamp
FHRLS —-Prior resetLogs SCN
FHPRS —-Reset Logs SCN
FHRLC —-Reset Logs timestamp

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle重建控制文件时resetlogs和noresetlogs区别

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1359.html

Oracle研究中心

关键词:

Oracle重建控制文件

Oracle resetlogs和noresetlogs

Oracle归档与非归档模式重建控制文件的区别