At the same time, Incomplete recovery of parameter files, control files, and redo log files is lost (hot backup and archiving of data files)

Reprinted please indicate the source: http://blog.csdn.net/guoyjoe/article/details/30839817

######### Preparations before recovery

1. Perform a Hot Backup

   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. Switch logs

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. Loss of parameter files, control files, and redo log files

[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* 

########## Start recovery
1. Create a parameter file

[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. Start the instance

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. start restoring data files

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. query database character sets

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. Create a control file

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. register the compliance log

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. Check whether the checkpoint of the data file header is consistent with that of the control file.

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. Incomplete recovery

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. Check whether the checkpoint of the data file header is consistent with that of the control file.

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. Use resetlogs to open a database

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

######## Recovery completed
Gyj @ PROD> select * from gyj_test1;


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