oracle控制檔案丟失了怎麼辦?

三大方法：1.利用rman備份來還原

恢複控制檔案自動備份
 如果未使用恢複目錄，則應該配置控制檔案自動備份，從而可以在需要時快速還原控制檔案。無論是否使用快速恢複區，用於還原控制檔案的

命令都相同。但是，如果使用快速恢複區，則 RMAN  會隱式交叉檢驗控制檔案中列出的備份和影像副本，並對處於快速恢複區但已還原控制文

件中未記錄的所有檔案進行編錄，從而提高已還原控制檔案在還原資料庫的其餘部分時的作用。 
註： 還原控制檔案後不自動交叉檢驗磁帶備份。如果使用磁帶備份，則在還原控制檔案並裝載資料庫後，必須交叉檢驗磁帶上的備份。 
 要從自動備份還原控制檔案，資料庫必須處於 NOMOUNT  狀態。然後，使用 RESTORE
CONTROLFILE FROM AUTOBACKUP  命令： 
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP; 

 

恢複控制檔案自動備份（續）
RMAN  將搜尋控制檔案自動備份。如果找到一個自動備份，則 RMAN  會將控制檔案從該備份還原到 CONTROL_FILES  初始化參數中列出的所有控制檔案位置。 
 如果有恢複目錄，則不必設定 DBID  或使用控制檔案自動備份來還原控制檔案。可以使用 RESTORE CONTROLFILE  命令，且沒有任何參數： 
RMAN> RESTORE CONTROLFILE;
 執行此操作時，執行個體必須處於 NOMOUNT  狀態，且 RMAN  必須串連到恢複目錄。還原的控制檔案將寫入 CONTROL_FILES  初始化參數中列出的所有位置。 
 如果同時丟失了資料庫的 SPFILE  且需要從自動備份進行還原，則過程與從自動備份還原控制檔案類似。必須首先設定資料庫的 DBID， 然後才能使用 RESTORE SPFILE FROM AUTOBACKUP  命令。 
 在使用已還原伺服器參數檔案啟動執行個體後，RMAN  可以從自動備份還原控制檔案。在還原並裝載控制檔案後，將擁有還原和恢複資料庫所需的備份資訊。 
 從備份還原資料庫的控制檔案後，必須執行完全介質恢複，然後才能使用 RESETLOGS  選項開啟資料庫。

 

 

[oracle@yysf 2009_09_30]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Sep 30 17:08:44 2009

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area     285212672 bytes

Fixed Size                     1218992 bytes
Variable Size                109053520 bytes
Database Buffers             171966464 bytes
Redo Buffers                   2973696 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 30-SEP-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /oracle/oradata
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /oracle/oradata/ORCL/autobackup/2009_09_30/o1_mf_s_698950847_5d67z0ww_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/oracle/oradata/orcl/control01.ctl
output filename=/oracle/oradata/orcl/control02.ctl
output filename=/oracle/oradata/orcl/control03.ctl
Finished restore at 30-SEP-09

RMAN> alter databaset mount;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "clone, database"
RMAN-01008: the bad identifier was: databaset
RMAN-01007: at line 1 column 7 file: standard input

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 30-SEP-09
Starting implicit crosscheck backup at 30-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 30-SEP-09

Starting implicit crosscheck copy at 30-SEP-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 30-SEP-09

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oracle/oradata/ORCL/autobackup/2009_09_30/o1_mf_s_698950847_5d67z0ww_.bkp

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 30 is already on disk as file /oracle/oradata/orcl/redo03.log
archive log filename=/oracle/oradata/orcl/redo03.log thread=1 sequence=30
media recovery complete, elapsed time: 00:00:03
Finished recover at 30-SEP-09

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/30/2009 17:10:12
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

database opened

RMAN>

2.使用老的可以開啟的控制檔案產生trace指令碼，再重建控制檔案
1.制定pfile,添加 老的控制檔案作為源 old.ctl

orcl.__db_cache_size=79691776
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=109051904
orcl.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/orcl/adump'
*.audit_trail='NONE'
*.background_dump_dest='/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/oracle/old.ctl'
*.core_dump_dest='/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_cache_size=79691776
*.db_create_file_dest='/oracle/oradata'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.java_pool_size=4194304
*.job_queue_processes=10
*.large_pool_size=4194304
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=66060288
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=201326592
*.shared_pool_size=109051904
*.streams_pool_size=0
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/orcl/udump'

2.利用老的控制檔案把資料庫置於mount狀態,
 SQL> startup mount pfile='initorcl.ora';
SQL> select status from v$instance;

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

3.把資料庫的ctl檔案產生指令碼錄一下：
SQL>alter database backup controlfile to trace;

需要資料庫在掛載的情況下,
產生一個trc檔案匯出到 udump檔案夾中去,
這個trc大小是7K左右.

 

4.建立pfile，制定我欲恢複的控制檔案的位置
[oracle@yysf dbs]$ vi initorcl.ora

orcl.__db_cache_size=79691776
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=109051904
orcl.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/orcl/adump'
*.audit_trail='NONE'
*.background_dump_dest='/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/oracle/new.ctl'
*.core_dump_dest='/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_cache_size=79691776
*.db_create_file_dest='/oracle/oradata'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.java_pool_size=4194304
*.job_queue_processes=10
*.large_pool_size=4194304
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=66060288
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=201326592
*.shared_pool_size=109051904
*.streams_pool_size=0
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/orcl/udump'
~
5.察看trc 檔案，把建立控制檔案的那一部分拷貝出來:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/oracle/oradata/ORCL/onlinelog/o1_mf_1_5bbblfr4_.log',
    '/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_1_5bbblgwf_.log',
    '/home/oracle/damao.dbf'
  ) SIZE 50M,
  GROUP 2 (
    '/oracle/oradata/ORCL/onlinelog/o1_mf_2_5bbbljjw_.log',
    '/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_5bbblkdc_.log'
  ) SIZE 50M,
  GROUP 3 (
    '/oracle/oradata/ORCL/onlinelog/o1_mf_3_5bbbllln_.log',
    '/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5bbblmq0_.log'
  ) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/ORCL/datafile/o1_mf_system_5bbbjkn1_.dbf',
  '/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_5bbbjkv1_.dbf',
  '/oracle/oradata/ORCL/datafile/o1_mf_sysaux_5bbbjko0_.dbf',
  '/oracle/oradata/ORCL/datafile/o1_mf_users_5bbbjkw8_.dbf',
  '/oracle/oradata/ORCL/datafile/o1_mf_example_5bbbmdyq_.dbf'
CHARACTER SET AL32UTF8
;

做成檔案 recover.sql

5.利用這個指令碼產生新的控制檔案
 startup nomount;
 SQL>@recover.sql

[oracle@yysf ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 13 21:47:46 2009

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> shutdown abort;
ORACLE instance shut down.

---用建立的pfile把資料庫置於 nomount狀態，---------
SQL> startup nomount pfile='/oracle/product/10.2.0/db_1/dbs/initorcl.ora';
ORACLE instance started.

Total System Global Area  201326592 bytes
Fixed Size                  1218508 bytes
Variable Size            117442612 bytes
Database Buffers          79691776 bytes
Redo Buffers                2973696 bytes

----用建立的sql指令碼重建控制檔案---------
SQL> @/home/oracle/recover.sql
ORA-01081: cannot start already-running ORACLE - shut it down first

Control file created.

6.恢複資料庫，並已resetlogs 方式開啟資料庫。
[oracle@yysf ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 13 21:50:31 2009

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
------------
MOUNTED

SQL> recover database using backup controlfile;
ORA-00279: change 813732 generated at 09/13/2009 21:21:13 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2009_09_13/o1_mf_1_26_%u_.arc
ORA-00280: change 813732 for thread 1 is in sequence #26

Specify log: { <RET>=suggested | filename | AUTO | CANCEL}
/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_5bbblkdc_.log
Log applied.
Media recovery complete

SQL> alter database open resetlogs;

Database altered.

 

3.手動寫指令碼，根據datafile的情況，編寫ctl file產生指令碼，剩餘步驟和2一樣。