数据库第一次resetlogs是发生在db创建时

下面是验证过程。[@more@]

--=========================================
SQL> select file#,creation_change#,checkpoint_change#,name from v$datafile;

FILE# CREATION_CHANGE# CHECKPOINT_CHANGE# NAME
---------- ---------------- ------------------ ----------------------------------------
1 6 15080624 G:ORADATATESTSYSTEM01.DBF
2 15053253 15080624 G:ORADATATESTTEST.DBF
3 6626 15080624 G:ORADATATESTSYSAUX01.DBF
4 10620 15080624 G:ORADATATESTUSERS01.DBF
5 14817603 15080624 G:ORADATATESTUNDOTBS02.DBF

SQL>
--我原来一直以为我们在db里看到的系统最小的scn是v$datafile里file#=1时对应的CREATION_CHANGE#，这里是6，后来发现
v$database_incarnation里的字段RESETLOGS_CHANGE#=1,也正是这个1以及v$database_incarnation里
在创建完db时就出现了一条记录，我断定db发生的第一次resetlogs是发生在db创建时而不是我们第一次执行不完全
恢复之后执行的alter database open resetlogs;

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIM STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWE
------------ ----------------- ------------------- ----------------------- ------------------- ------- ------------ ------------------ -------------------------
1 1 2011-7-15 10:11:04 0 PARENT 756555064 0 NO
2 15049651 2011-7-20 14:47:05 1 2011-7-15 10:11:04 PARENT 757003625 1 NO
3 15051342 2011-7-20 15:15:23 15049651 2011-7-20 14:47:05 CURRENT 757005323 2 NO

--=========================================
--看到v$database_incarnation里面的字段RESETLOGS_CHANGE#=1时而且RESETLOGS_TIME的时间正好差不多
是建库时instance第一次启动时的时间，因此我断定db的第一次resetlogs发生在建库时，当然redo
自然是在resetlogs时生成的，下面是验证过程。
--==========================================
SQL> alter database backup controlfile to trace;

Database altered.
--=======================
--冷备份db
--=======================
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--============================
--delete dbf,ctl,redo
--============================
SQL> startup nomount
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 1248116 bytes
Variable Size 92275852 bytes
Database Buffers 109051904 bytes
Redo Buffers 7139328 bytes
SQL> startup nomount
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 1248116 bytes
Variable Size 92275852 bytes
Database Buffers 109051904 bytes
Redo Buffers 7139328 bytes
SQL>
--================================
SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'G:ORADATATESTREDO01.LOG' SIZE 50M,
9 GROUP 2 'G:ORADATATESTREDO02.LOG' SIZE 50M,
10 GROUP 4 'G:ORADATATESTREDO04.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'G:ORADATATESTSYSTEM01.DBF',
14 'G:ORADATATESTTEST.DBF',
15 'G:ORADATATESTSYSAUX01.DBF',
16 'G:ORADATATESTUSERS01.DBF',
17 'G:ORADATATESTUNDOTBS02.DBF'
18 CHARACTER SET ZHS16GBK
19 ;
CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file 'G:ORADATATESTSYSTEM01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) ??????????????????????

SQL>
--==================================
--拷贝dbf文件回来
SQL> /
CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file 'G:ORADATATESTREDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) ??????????????????????

SQL>
--===================================
--创建controlfile时使用resetlogs，继续创建controlfile(注意redo目前没有)
SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'G:ORADATATESTREDO01.LOG' SIZE 50M,
9 GROUP 2 'G:ORADATATESTREDO02.LOG' SIZE 50M,
10 GROUP 4 'G:ORADATATESTREDO04.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'G:ORADATATESTSYSTEM01.DBF',
14 'G:ORADATATESTTEST.DBF',
15 'G:ORADATATESTSYSAUX01.DBF',
16 'G:ORADATATESTUSERS01.DBF',
17 'G:ORADATATESTUNDOTBS02.DBF'
18 CHARACTER SET ZHS16GBK
19 ;

Control file created.

SQL>
--========================================
SQL> select status from v$instance;

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

SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL>
--================================
--重建建一个库观查：
在建库时我发现ctl和redo几乎是同时出现的，而且紧接着是dbf挨个出来...按照上面的推断
应该是dbf先存在，然后是ctl，然后是redo...不过ctl先出现也不难理解，因为毕竟此时scn是从1开始的，和后来创建ctl情况不同，后来在dbf存在的情况下创建ctl
oracle需要从dbf的头上至少读取checkpoint_change#吧。
在创建db刚刚百分之20左右的时侯我查询了下面结果：
C:>set oracle_sid=orcl

C:>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 20 19:40:48 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> set linesize 200
SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIM STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATAB
ASE_ALLOWED
------------ ----------------- ------------------- ----------------------- ------------------- ------- ------------ ------------------ --------------------------
1 1 2011-07-20 19:40:47 0 CURRENT 757021247 0 NO

SQL>
--======================================
--也就是说在实例刚刚起来，db已经open了，紧接着是执行一堆脚本创建数据字典和package...

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

转载于:http://blog.itpub.net/19602/viewspace-1052787/