当前位置：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研究中心
本站文章除注明转载外，均为本站原创： 转载自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
