Scenarios of file lost of Oracle database

Scenarios of file lost of Oracle database

原文鏈結：http://www.itpub.net/thread-1775874-1-2.html

1.恢復密碼檔

orapwd file=$ORACLE_HOME/dbs/orapwocp password=oracle force=y ignorecase=y;

--註：一般用戶的密碼區分大小寫, 由初始參數sec_case_sensitive_logon控制, 不歸密碼檔處理

2.恢復參數檔

找alert_sid.log日誌

3.無備份的控制檔

***場景1：線上redo log無損的情況下的重建

rm -rf control*

shutdown abort;

startup nomount;

CREATE CONTROLFILE REUSE DATABASE "BXDB" NORESETLOGS...

reocver database;

alter database open;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/bxdb/temp01.dbf' reuse;

***場景2：線上redo log損壞的情況下的重建

shutdown immediate; or shutdown abort;

rm -rf control*

rm -rf redo*

startup nomount;

CREATE CONTROLFILE REUSE DATABASE "BXDB" RESETLOGS...

recover database until cancel using BACKUP CONTROLFILE;--RMAN>recover database until sequence 2;

alter database open resetlogs;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/bxdb/temp01.dbf' reuse;

***場景3：具有唯讀datafile情況下的重建

startup nomount;

CREATE CONTROLFILE REUSE DATABASE "BXDB" NORESETLOGS...

recover database; --recover database using backup controlfile;

alter database open; --alter database open resetlogs;

alter database rename file '/u01/app/oracle/product/11g/dbs/MISSING00005' to '/u01/app/oracle/oradata/bxdb/tp2.dbf';

alter tablespace tp12 online; --不能設資料檔案online (alter database datafile 7 online;)

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ocp/temp01.dbf' reuse;

***場景4：使用snapshot controlfile恢復

cp /u01/app/oracle/product/11g/dbs/snapcf_ocp.f /u01/app/oracle/oradata/ocp/control01.ctl

cp /u01/app/oracle/product/11g/dbs/snapcf_ocp.f /u01/app/oracle/oradata/ocp/control02.ctl

cp /u01/app/oracle/product/11g/dbs/snapcf_ocp.f /u01/app/oracle/oradata/ocp/control03.ctl

startup mount;

recover database using backup controlfile; --RMAN>recover database;

alter database open resetlogs;

4.恢復資料檔案(將不見的datafile offline)

rm -rf tp5.dbf;

alter database datafile 8 offline;

alter database create datafile '/u01/app/oracle/oradata/ocp/tp12.dbf';

select * from t20; ---報錯沒找到文件

recover datafile 8;

alter database datafile 8 online;

5.恢復temp datafile

alter tablesapce temp add tempfile '/u01/app/oracle/oradata/oca/temp02.dbf' size 20M autoextend on;

alter tablesapce temp drop tempfile '/u01/app/oracle/oradata/oca/temp01.dbf';

6.恢復online redo log

***場景1：每個redo group有兩個redo member，group 1是其中一個redo01B.log損壞了

alter database drop logfile member '/u01/app/oracle/oradata/oca/redo01B.log';

alter database add logfile member '/u01/app/oracle/oradata/oca/redo01B.log' to group 1;

***場景2：每個redo group有兩個redo member，group 1的所有日誌（redo01A.log和redo01B.log）損壞了

alter database clear logfile group 1;

alter database clear unarchived logfile group 1;

***場景3：所有redo groups全部損壞了

rm -rf redo*

shutdown immediate;

startup mount;

recover database until cancel;

alter database open resetlogs; ==> 如果上一次為不正常關機(非shutdown immediate) , 或current redo logfile 損毀造成datafile 無法進到同一個scn number...基本上open很難成功

curent的redo log都損壞的情況，需使用以下隱含參數, 再帶起資料庫看看:

alter system set "_allow_resetlogs_corruption"=true scope=spfile;