備份與恢復--重新控制檔案資料字典和控制檔案不一致的恢復

在執行恢復後，應該檢查資料庫的alert檔案，看是否出現控制檔案中的資料檔名稱和資料字典中資料檔名稱無法一一對應的現象。

這篇文章描述透過RESETLOGS方式開啟資料庫時，如果發現不一致情況及解決方法。

[@more@]

如果是利用備份的控制檔案，除了資料檔案的新增和刪除以外，不會出現不一致的情況。這種不一致的情況更多的是出現在手工重建控制檔案過程中。

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
JGLU ONLINE
TEST ONLINE

8 rows selected.

SQL> alter tablespace users read only;

Tablespace altered.

SQL> alter tablespace jglu offline;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS READ ONLY
EXAMPLE ONLINE
JGLU OFFLINE
TEST ONLINE

8 rows selected.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;

Database altered.

trace出來的控制檔案如下：

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf',
'/u01/app/oracle/oradata/orcl/test02.dbf'
CHARACTER SET WE8ISO8859P1
;

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/admin/orcl/archive/1_1_675592897.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Files in normal offline tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00006'
TO '/u01/app/oracle/oradata/orcl/jglu01.dbf';
ALTER DATABASE RENAME FILE 'MISSING00007'
TO '/u01/app/oracle/oradata/orcl/jglu02.dbf';
-- Files in read-only tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00004'
TO '/u01/app/oracle/oradata/orcl/users01.dbf';
-- Online the files in read-only tablespaces.
ALTER TABLESPACE "USERS" ONLINE;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.

此時模擬資料庫有問題需要恢復,假設由於沒有將指令碼儲存到trace檔案中，而完全透過手工建立。因此，

在建立的時候丟失了test02.dbf。

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 335544320 bytes
Fixed Size 1219280 bytes
Variable Size 71304496 bytes
Database Buffers 260046848 bytes
Redo Buffers 2973696 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,
9 GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M,
10 GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/oradata/orcl/system01.dbf',
14 '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
15 '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
16 '/u01/app/oracle/oradata/orcl/example01.dbf' 17 CHARACTER SET WE8ISO8859P1;

Control file created.

SQL> recover database using backup controlfile;
ORA-00279: change 691653 generated at 01/18/2009 01:52:39 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_23_675592897.dbf
ORA-00280: change 691653 for thread 1 is in sequence #23

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS READ ONLY
EXAMPLE ONLINE
JGLU OFFLINE
TEST ONLINE

8 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/product/10201/dbs/MISSING00004
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/product/10201/dbs/MISSING00006
/u01/app/oracle/product/10201/dbs/MISSING00007
/u01/app/oracle/product/10201/dbs/MISSING00008

8 rows selected.

SQL> set linesize 200
SQL> col file_name format a60
SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE
/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/product/10201/dbs/MISSING00004 USERS
/u01/app/oracle/product/10201/dbs/MISSING00006 JGLU
/u01/app/oracle/product/10201/dbs/MISSING00007 JGLU
/u01/app/oracle/product/10201/dbs/MISSING00008 TEST

8 rows selected.

從上面的資料字典可以看到，存在不正常的表空間和資料檔案。也可以從alert日誌中得到相應的資訊。

Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'USERS' #4 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'JGLU' #7 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'TEST' #8 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #4 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00004' in the controlfile.
Corrected file 5 plugged in read-only status in control file
File #6 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00006' in the controlfile.
File #7 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00007' in the controlfile.
File #8 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00008' in the controlfile.
This file can no longer be recovered so it must be dropped.
Dictionary check complete

由於USER表空間在重建控制檔案之前是隻讀狀態，因此不需要進行恢復，直接RENAME後ONLINE就可以了。

SQL> alter database rename file '/u01/app/oracle/product/10201/dbs/MISSING00004'
2 to '/u01/app/oracle/oradata/orcl/users01.dbf';

Database altered.

SQL> alter tablespace users online;

Tablespace altered.

SQL> alter tablespace users read write;

Tablespace altered.

由於JGLU表空間在重建設控制檔案之前是OFFLINE的，也不需要恢復，直接RENAME後ONLINE就可以了。
SQL> alter database rename file '/u01/app/oracle/product/10201/dbs/MISSING00006'
2 to '/u01/app/oracle/oradata/orcl/jglu01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/product/10201/dbs/MISSING00007'
2 to '/u01/app/oracle/oradata/orcl/jglu02.dbf';

Database altered.

SQL> alter tablespace jglu online;

Tablespace altered.

對於臨時表空間只要新增加一下就行了

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

對於表空間TEST由於沒有被新增到控制檔案中，且資料庫以RESETLOGS方式開啟，用來恢復表空間的日誌

已經被重置，在以前的版本中，因此只能將表空間刪除。但10g是可以recover的

SQL> alter database rename file '/u01/app/oracle/product/10201/dbs/MISSING00008'
2 to '/u01/app/oracle/oradata/orcl/test02.dbf';

Database altered.

SQL> alter tablespace test online;
alter tablespace test online
*
ERROR at line 1:
ORA-01190: control file or data file 8 is from before the last RESETLOGS
ORA-01110: data file 8: '/u01/app/oracle/oradata/orcl/test02.dbf'

SQL> recover tablespace test;
ORA-00279: change 691653 generated at 01/18/2009 01:52:39 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_23_675592897.dbf
ORA-00280: change 691653 for thread 1 is in sequence #23

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
Log applied.
Media recovery complete.
SQL> alter tablespace test online;

Tablespace altered.

SQL> select owner,table_name,TABLESPACE_NAME from dba_tables where tablespace_name='TEST';

OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
JGLU TEST1 TEST

SQL> select count(*) from jglu.test1;

COUNT(*)
----------
40688

楊版主的blog說這種情況需要將TEST表空間刪除掉，看來10g在這方面有所改進了。
http://yangtingkun.itpub.net/post/468/57972