資料庫第一次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...