同时丢失参数文件、控制文件及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/gyj Connected. gyj@PROD> select * from gyj_test1;          ID NAME ---------- ----------------------------------------------------------------------------------------------------          1 guoyJ          2 BBBBB  gyj@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 bytes Fixed Size                  2233960 bytes Variable Size             251660696 bytes Database Buffers          679477248 bytes Redo Buffers                6123520 bytes  sys@PROD> create spfile from pfile='/tmp/pfile.ora';  File created.

3、开始还原数据文件

oracle@jfdb arch]$ cd /backup [oracle@jfdb backup]$ ll total 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=1  1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 0.000159113 s, 51.5 MB/s [oracle@jfdb PROD]$ strings guoyJoe NO_USERID_VERIFIER_SALT 0438054C4F979EC5A5F74990346F5327, WORKLOAD_REPLAY_MODE bPREPARE 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_VERSION Export views revision #, DEFAULT_PERMANENT_TABLESPACE USERS$Name of default permanent tablespace, GLOBAL_DB_NAME PROD Global database name, NLS_RDBMS_VERSION 11.2.0.3.0 RDBMS version for NLS parameters, NLS_NCHAR_CHARACTERSET  AL16UTF16 NCHAR Character set, NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception, NLS_LENGTH_SEMANTICS BYTE NLS length semantics, NLS_COMP BINARY NLS comparison, NLS_DUAL_CURRENCY Dual currency symbol, NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format, NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format, NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format, NLS_TIME_FORMAT HH.MI.SSXFF AM Time format, NLS_SORT BINARY Linguistic definition, NLS_DATE_LANGUAGE AMERICAN Date language, NLS_DATE_FORMAT DD-MON-RR Date format, NLS_CALENDAR    GREGORIAN Calendar system, NLS_CHARACTERSET ZHS16GBK Character set, NLS_NUMERIC_CHARACTERS Numeric characters, NLS_ISO_CURRENCY AMERICA ISO currency, NLS_CURRENCY Local currency, NLS_TERRITORY AMERICA Territory, NLS_LANGUAGE AMERICAN Language, DEFAULT_TBS_TYPE        SMALLFILE Default tablespace type, DST_SECONDARY_TT_VERSION 0'Version of secondary timezone data file, DST_PRIMARY_TT_VERSION 14%Version of primary timezone data file, DST_UPGRADE_STATE NONE&State of Day Light Saving Time Upgrade, DBTIMEZONE +08:00 DB time zone, TDE_MASTER_KEY_ID, Flashback Timestamp TimeZone GMT"Flashback timestamp created in GMT, DEFAULT_EDITION ORA$BASE$Name of the database default edition, DEFAULT_PERMANENT_TABLESPACE SYSTEM$Name of default permanent tablespace, DEFAULT_TEMP_TABLESPACE TEMPTS$Name of default temporary tablespace,         DICT.BASE 2 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(*) ----------          0 sys@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            1658774  sys@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 1 ORA-00289: suggestion : /arch/1_134_842976958.dbf ORA-00280: change 1658739 for thread 1 is in sequence #134   Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00279: change 1658838 generated at 06/08/2014 06:02:46 needed for thread 1 ORA-00289: suggestion : /arch/1_135_842976958.dbf ORA-00280: change 1658838 for thread 1 is in sequence #135 ORA-00278: log file '/arch/1_134_842976958.dbf' no longer needed for this recovery   Specify log: {<RET>=suggested | filename | AUTO | CANCEL}  ORA-00279: change 1658841 generated at 06/08/2014 06:02:47 needed for thread 1 ORA-00289: suggestion : /arch/1_136_842976958.dbf ORA-00280: change 1658841 for thread 1 is in sequence #136 ORA-00278: log file '/arch/1_135_842976958.dbf' no longer needed for this recovery   Specify log: {<RET>=suggested | filename | AUTO | CANCEL}  ORA-00279: change 1658844 generated at 06/08/2014 06:02:50 needed for thread 1 ORA-00289: suggestion : /arch/1_137_842976958.dbf ORA-00280: change 1658844 for thread 1 is in sequence #137 ORA-00278: log file '/arch/1_136_842976958.dbf' no longer needed for this recovery   Specify log: {<RET>=suggested | filename | AUTO | CANCEL}  ORA-00279: change 1658856 generated at 06/08/2014 06:03:17 needed for thread 1 ORA-00289: suggestion : /arch/1_138_842976958.dbf ORA-00280: change 1658856 for thread 1 is in sequence #138 ORA-00278: log file '/arch/1_137_842976958.dbf' no longer needed for this recovery   Specify log: {<RET>=suggested | filename | AUTO | CANCEL}  ORA-00279: change 1658859 generated at 06/08/2014 06:03:21 needed for thread 1 ORA-00289: suggestion : /arch/1_139_842976958.dbf ORA-00280: change 1658859 for thread 1 is in sequence #139 ORA-00278: log file '/arch/1_138_842976958.dbf' no longer needed for this recovery   Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled.

9、再次查看数据文件头的检查点与控制文件的检查点是否一致

sys@PROD> select file#,checkpoint_change# from v$datafile;       FILE# CHECKPOINT_CHANGE# ---------- ------------------          1            1658859          2            1658859          3            1658859          4            1658859          5            1658859  sys@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