備份與恢復--重新控制檔案資料字典和控制檔案不一致的恢復
在執行恢復後，應該檢查資料庫的alert檔案，看是否出現控制檔案中的資料檔名稱和資料字典中資料檔名稱無法一一對應的現象。
這篇文章描述透過RESETLOGS方式開啟資料庫時，如果發現不一致情況及解決方法。
如果是利用備份的控制檔案，除了資料檔案的新增和刪除以外，不會出現不一致的情況。這種不一致的情況更多的是出現在手工重建控制檔案過程中。
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: {
/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: {
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/271283/viewspace-1016333/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 與控制檔案有關的恢復
- 與控制檔案有關的恢復(二)
- 【/proc/檔案淺析】另類辦法恢復資料檔案和控制檔案
- 12 使用RMAN備份和恢復檔案
- 【RMAN】如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？
- 檔案的基本管理和XFS檔案系統備份恢復
- XFS檔案系統的備份、恢復、修復
- Oracle-無備份情況下，如何手動恢復控制檔案Oracle
- 備份與恢復：polardb資料庫備份與恢復資料庫
- [20210225]控制檔案序列號滿的恢復.txt
- 實戰：xfs檔案系統的備份和恢復
- Oracle為什麼使用備份的控制檔案恢復後一定要resetlogsOracle
- 怎樣恢復Mac檔案及資料夾資料？BackupLoupe for mac(資料恢復備份助手)3.5.4Mac資料恢復
- RMAN備份恢復典型案例——資料檔案存在壞快
- Linux中XFS檔案系統的備份，恢復，修復Linux
- RAC控制檔案恢復（三種不同情況）
- rman恢復控制檔案的一個小錯誤
- 資料恢復新姿勢——通過ibd和frm檔案恢復資料資料恢復
- Oracle使用備份檔案集恢復歸檔日誌Oracle
- 從備份片中恢復某個指定得歸檔或者資料檔案
- 同名檔案替換怎麼恢復，恢復同名檔案
- 【資料庫資料恢復】透過恢復NDF檔案修復資料庫的資料恢復過程資料庫資料恢復
- 檔案替換後怎麼恢復，恢復被覆蓋的檔案
- word怎麼恢復儲存前的檔案，word檔案恢復
- 如何從Active Backup for Business備份中恢復檔案
- 如何備份及恢復Linux檔案許可權Linux
- 讀資料保護：工作負載的可恢復性04備份與檔案負載
- word自動儲存的檔案怎麼恢復，word檔案恢復
- 剪下後的檔案可以恢復嗎？恢復剪下檔案怎麼辦？
- Mysql資料備份與恢復MySql
- 【伺服器資料恢復】StorNext檔案系統資料恢復案例伺服器資料恢復
- RAC備份恢復之Voting備份與恢復
- 剪下的檔案還能恢復嗎，恢復剪貼丟失的檔案
- gitlab的資料備份和恢復Gitlab
- 檔案中勒索恢復
- 【資料庫資料恢復】MongoDB資料庫檔案損壞的資料恢復案例資料庫資料恢復MongoDB
- FastDFS檔案系統遷移和資料恢復AST資料恢復
- 【儲存資料恢復】WAFL檔案系統下raid資料恢復案例資料恢復AI
- 電腦檔案丟失資料恢復資料恢復