同時丟失參數檔案、控制檔案及redo log file的不完全恢複（有資料檔案的熱備和歸檔）

轉載請註明出處：http://blog.csdn.net/guoyjoe/article/details/30839817

##########恢複前的準備工作

   1、做個熱備

   select    'alter tablespace '||tablespace_name|| ' begin backup;' ||chr(10)||    'host cp '||file_name||' /backup' ||chr(10)||    'alter tablespace '||tablespace_name|| ' end backup;'   from dba_data_files order by tablespace_name;

 sys@PROD> alter tablespace SYSAUX begin backup;
host cp /u01/app/oracle/oradata/PROD/sysaux01.dbf /backup
alter tablespace SYSAUX end backup;

Tablespace altered.

sys@PROD> alter tablespace SYSTEM begin backup;
host cp /u01/app/oracle/oradata/PROD/system01.dbf /backup
alter tablespace SYSTEM end backup;


alter tablespace TP1 begin backup;
host cp /u01/app/oracle/oradata/PROD/tp01.dbf /backup
alter tablespace TP1 end backup;


alter tablespace UNDOTBS begin backup;
host cp /u01/app/oracle/oradata/PROD/undotbs01.dbf /backup
alter tablespace UNDOTBS end backup;


alter tablespace USERS begin backup;
host cp /u01/app/oracle/oradata/PROD/users01.dbf /backup
alter tablespace USERS end backup;

2、日誌做切換

sys@PROD> alter system switch logfile;System altered.sys@PROD> alter system switch logfile;System altered.sys@PROD> alter system switch logfile;System altered.sys@PROD> conn gyj/gyjConnected.gyj@PROD> select * from gyj_test1;        ID NAME---------- ----------------------------------------------------------------------------------------------------         1 guoyJ         2 BBBBBgyj@PROD> insert into gyj_test1 values(3,'CCCCC');1 row created.gyj@PROD> commit;Commit complete.gyj@PROD> alter system switch logfile;System altered.gyj@PROD> alter system switch logfile;System altered.

3、丟失參數檔案，控制檔案，redo記錄檔

[oracle@jfdb dbs]$ rm  -rf spfile.ora[oracle@jfdb dbs]$ rm -rf spfilePROD.ora[oracle@jfdb dbs]$ rm -rf initPROD.ora [oracle@jfdb PROD]$ rm -rf control0*[oracle@jfdb PROD]$ rm -rf redo0* 

###########開始恢複
  1、建參數檔案

[oracle@jfdb trace]$ cat alert_PROD.log    vi /tmp/pfile.ora   processes                = 150  sga_max_size             = 900M  sga_target               = 900M  control_files            = "/u01/app/oracle/oradata/PROD/control01.ctl"  control_files            = "/u01/app/oracle/oradata/PROD/control02.ctl"  _controlfile_update_check= "OFF"  db_block_size            = 8192  log_archive_dest_1       = "location=/arch"  undo_tablespace          = "UNDOTBS"  _in_memory_undo          = FALSE  service_names            = "PROD,crm,oa"  local_listener           = "(DESCRIPTION=    (ADDRESS_LIST=      (ADDRESS = (PROTOCOL=TCP)(HOST=jfdb)(PORT=1521))      (ADDRESS = (PROTOCOL=TCP)(HOST=jfdb)(PORT=1522))))"  db_name                  = "PROD"  pga_aggregate_target     = 200M  

2、啟動執行個體

sys@PROD> startup pfile='/tmp/pfile.ora' nomount;ORACLE instance started.Total System Global Area  939495424 bytesFixed Size                  2233960 bytesVariable Size             251660696 bytesDatabase Buffers          679477248 bytesRedo Buffers                6123520 bytessys@PROD> create spfile from pfile='/tmp/pfile.ora';File created.

3、開始還原資料檔案

oracle@jfdb arch]$ cd /backup[oracle@jfdb backup]$ lltotal 1664052-rw-r-----. 1 oracle oinstall 340795392 Jun  8 06:01 sysaux01.dbf-rw-r-----. 1 oracle oinstall 524296192 Jun  8 06:01 system01.dbf-rw-r-----. 1 oracle oinstall 524296192 Jun  8 06:01 tp01.dbf-rw-r-----. 1 oracle oinstall 209723392 Jun  8 06:01 undotbs01.dbf-rw-r-----. 1 oracle oinstall 104865792 Jun  8 06:02 users01.dbf[oracle@jfdb backup]$ cp * -rf /u01/app/oracle/oradata/PROD

4、查資料庫字元集

select distinct dbms_rowid.rowid_block_number(rowid) from props$;DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)------------------------------------                                 801[oracle@jfdb PROD]$ dd if=system01.dbf of=guoyJoe bs=8192 skip=801 count=11+0 records in1+0 records out8192 bytes (8.2 kB) copied, 0.000159113 s, 51.5 MB/s[oracle@jfdb PROD]$ strings guoyJoeNO_USERID_VERIFIER_SALT 0438054C4F979EC5A5F74990346F5327,WORKLOAD_REPLAY_MODEbPREPARE implies external replay clients can connect; REPLAY implies workload replay is in progress,WORKLOAD_CAPTURE_MODE/CAPTURE implies workload capture is in progress,EXPORT_VIEWS_VERSIONExport views revision #,DEFAULT_PERMANENT_TABLESPACEUSERS$Name of default permanent tablespace,GLOBAL_DB_NAMEPRODGlobal database name,NLS_RDBMS_VERSION11.2.0.3.0 RDBMS version for NLS parameters,NLS_NCHAR_CHARACTERSET  AL16UTF16NCHAR Character set,NLS_NCHAR_CONV_EXCPFALSENLS conversion exception,NLS_LENGTH_SEMANTICSBYTENLS length semantics,NLS_COMPBINARYNLS comparison,NLS_DUAL_CURRENCYDual currency symbol,NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZRTimestamp with timezone format,NLS_TIME_TZ_FORMATHH.MI.SSXFF AM TZRTime with timezone format,NLS_TIMESTAMP_FORMATDD-MON-RR HH.MI.SSXFF AMTime stamp format,NLS_TIME_FORMATHH.MI.SSXFF AMTime format,NLS_SORTBINARYLinguistic definition,NLS_DATE_LANGUAGEAMERICANDate language,NLS_DATE_FORMAT DD-MON-RRDate format,NLS_CALENDAR    GREGORIANCalendar system,NLS_CHARACTERSETZHS16GBKCharacter set,NLS_NUMERIC_CHARACTERSNumeric characters,NLS_ISO_CURRENCYAMERICAISO currency,NLS_CURRENCYLocal currency,NLS_TERRITORYAMERICA Territory,NLS_LANGUAGEAMERICANLanguage,DEFAULT_TBS_TYPE        SMALLFILEDefault tablespace type,DST_SECONDARY_TT_VERSION0'Version of secondary timezone data file,DST_PRIMARY_TT_VERSION14%Version of primary timezone data file,DST_UPGRADE_STATENONE&State of Day Light Saving Time Upgrade,DBTIMEZONE+08:00DB time zone,TDE_MASTER_KEY_ID,Flashback Timestamp TimeZoneGMT"Flashback timestamp created in GMT,DEFAULT_EDITIONORA$BASE$Name of the database default edition,DEFAULT_PERMANENT_TABLESPACESYSTEM$Name of default permanent tablespace,DEFAULT_TEMP_TABLESPACETEMPTS$Name of default temporary tablespace,        DICT.BASE2 dictionary base tables version #

5、建立控制檔案

sys@PROD> CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS ARCHIVELOG  2      MAXLOGFILES 32  3      MAXLOGMEMBERS 2  4      MAXDATAFILES 32  5      MAXINSTANCES 1  6      MAXLOGHISTORY 449  7  LOGFILE  8    GROUP 1 '/u01/app/oracle/oradata/PROD/redo01.log'  SIZE 50M,  9    GROUP 2 '/u01/app/oracle/oradata/PROD/redo02.log'  SIZE 50M, 10    GROUP 3 '/u01/app/oracle/oradata/PROD/redo03.log'  SIZE 50M 11  DATAFILE 12    '/u01/app/oracle/oradata/PROD/system01.dbf', 13    '/u01/app/oracle/oradata/PROD/sysaux01.dbf', 14    '/u01/app/oracle/oradata/PROD/undotbs01.dbf', 15    '/u01/app/oracle/oradata/PROD/users01.dbf', 16    '/u01/app/oracle/oradata/PROD/tp01.dbf' 17  CHARACTER SET ZHS16GBK;Control file created.

6、註冊規檔日誌

sys@PROD> select count(*) from v$archived_log;  COUNT(*)----------         0sys@PROD> alter database register physical logfile '/arch/1_134_842976958.dbf';Database altered.sys@PROD> alter database register physical logfile '/arch/1_135_842976958.dbf';Database altered.sys@PROD> alter database register physical logfile '/arch/1_136_842976958.dbf';Database altered. sys@PROD> alter database register physical logfile '/arch/1_137_842976958.dbf';Database altered.sys@PROD> alter database register physical logfile '/arch/1_138_842976958.dbf';Database altered.sys@PROD> select count(*) from v$archived_log;  COUNT(*)----------         5

7、查看資料檔案頭的檢查點與控制檔案的檢查點是否一致

sys@PROD> select file#,checkpoint_change# from v$datafile;     FILE# CHECKPOINT_CHANGE#---------- ------------------         1            1658759         2            1658739         3            1658788         4            1658802         5            1658774sys@PROD> select file#,checkpoint_change# from v$datafile_header;     FILE# CHECKPOINT_CHANGE#---------- ------------------         1            1658759         2            1658739         3            1658788         4            1658802         5            1658774

8、開始不完全恢複

sys@PROD> recover database using backup controlfile until cancel;ORA-00279: change 1658739 generated at 06/08/2014 06:01:29 needed for thread 1ORA-00289: suggestion : /arch/1_134_842976958.dbfORA-00280: change 1658739 for thread 1 is in sequence #134Specify log: {<RET>=suggested | filename | AUTO | CANCEL}ORA-00279: change 1658838 generated at 06/08/2014 06:02:46 needed for thread 1ORA-00289: suggestion : /arch/1_135_842976958.dbfORA-00280: change 1658838 for thread 1 is in sequence #135ORA-00278: log file '/arch/1_134_842976958.dbf' no longer needed for this recoverySpecify log: {<RET>=suggested | filename | AUTO | CANCEL}ORA-00279: change 1658841 generated at 06/08/2014 06:02:47 needed for thread 1ORA-00289: suggestion : /arch/1_136_842976958.dbfORA-00280: change 1658841 for thread 1 is in sequence #136ORA-00278: log file '/arch/1_135_842976958.dbf' no longer needed for this recoverySpecify log: {<RET>=suggested | filename | AUTO | CANCEL}ORA-00279: change 1658844 generated at 06/08/2014 06:02:50 needed for thread 1ORA-00289: suggestion : /arch/1_137_842976958.dbfORA-00280: change 1658844 for thread 1 is in sequence #137ORA-00278: log file '/arch/1_136_842976958.dbf' no longer needed for this recoverySpecify log: {<RET>=suggested | filename | AUTO | CANCEL}ORA-00279: change 1658856 generated at 06/08/2014 06:03:17 needed for thread 1ORA-00289: suggestion : /arch/1_138_842976958.dbfORA-00280: change 1658856 for thread 1 is in sequence #138ORA-00278: log file '/arch/1_137_842976958.dbf' no longer needed for this recoverySpecify log: {<RET>=suggested | filename | AUTO | CANCEL}ORA-00279: change 1658859 generated at 06/08/2014 06:03:21 needed for thread 1ORA-00289: suggestion : /arch/1_139_842976958.dbfORA-00280: change 1658859 for thread 1 is in sequence #139ORA-00278: log file '/arch/1_138_842976958.dbf' no longer needed for this recoverySpecify log: {<RET>=suggested | filename | AUTO | CANCEL}cancelMedia recovery cancelled.

9、再次查看資料檔案頭的檢查點與控制檔案的檢查點是否一致

sys@PROD> select file#,checkpoint_change# from v$datafile;     FILE# CHECKPOINT_CHANGE#---------- ------------------         1            1658859         2            1658859         3            1658859         4            1658859         5            1658859sys@PROD> select file#,checkpoint_change# from v$datafile_header;     FILE# CHECKPOINT_CHANGE#---------- ------------------         1            1658859         2            1658859         3            1658859         4            1658859         5            1658859

10、用resetlogs開啟資料庫

sys@PROD> alter database open resetlogs;Database altered.

#########恢複完成
gyj@PROD> select * from gyj_test1;


        ID NAME
---------- -----------------------------------------
         1 guoyJ
         2 BBBBB
         3 CCCCC