Recovering an instance in a catastrophic environment of an Oracle database (1)

Recovering an instance in a catastrophic environment of an Oracle database (1)

The so-called catastrophic means that all database-related files are lost or damaged due to disk faults or other causes and cannot be repaired. Backup must be used for restoration. The following experiments simulate the loss of all database files, then, restore and restore through backup.

Version and database file information

 
 

  
  
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production 
  
  
PL/SQL Release 11.2.0.3.0 - Production 
  
  
CORE    11.2.0.3.0      Production 
  
  
TNS for Linux: Version 11.2.0.3.0 - Production 
  
  
NLSRTL Version 11.2.0.3.0 - Production 
  
  
 
  
  
SQL> column name format a50 
  
  
SQL> select file#,status,name from v$datafile; 
  
  
 
  
  
    FILE# STATUS  NAME 
  
  
---------- ------- -------------------------------------------------- 
  
  
         1 SYSTEM  /u01/oradata/sydb/system01.dbf 
  
  
         2 ONLINE  /u01/oradata/sydb/sysaux01.dbf 
  
  
         3 ONLINE  /u01/oradata/sydb/undotbs01.dbf 
  
  
         4 ONLINE  /u01/oradata/sydb/users01.dbf 
  
  
         5 ONLINE  /u01/oradata/sydb/tbs01.dbf 
  
  
 
  
  
SQL> column member format a50 
  
  
SQL> select * from v$Logfile; 
  
  
 
  
  
    GROUP# STATUS  TYPE    MEMBER                                             IS_ 
  
  
---------- ------- ------- -------------------------------------------------- --- 
  
  
         1         ONLINE  /u01/oradata/sydb/REDO01.LOG                       NO 
  
  
         2         ONLINE  /u01/oradata/sydb/REDO02.LOG                       NO 
  
  
 
  
  
SQL> select * from v$controlfile; 
  
  
 
  
  
STATUS  NAME                                               IS_ BLOCK_SIZE FILE_SIZE_BLKS 
  
  
------- -------------------------------------------------- --- ---------- -------------- 
  
  
        /u01/oradata/sydb/control01.ctl                    NO       16384            668 
 
 

Back up database

Note: If the tablespace configured with configure exclude for tablespace tbsname is not backed up during database backup, recover the tablespace backup configure exclude for tablespace tbsname clear; if the automatic backup control file and parameter file are not set, manually add the backup control file script at the end of the backup script; set the automatic backup control file and parameter file:

 
 

  
  
CONFIGURE CONTROLFILE AUTOBACKUP On;  
  
  
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/%F_%d_controlfile.bkp';  
 
 

The control file name format must contain % F, % F in the format of: c-IIIIIIIIII-YYYYMMDD-QQ, IIIIIIIIII (10 bits) means that DBID can be known through it in the event of a disaster, YYYYMMDD indicates the timestamp during automatic backup, and QQ indicates the hexadecimal serial number. The starting value is 00 and the maximum value is FF. Start data backup

 
 

  
  
 run 
  
  
 { 
  
  
 allocate channel dev type disk; 
  
  
 allocate channel dev2 type disk; 
  
  
 backup incremental level 0 database plus archivelog delete input 
  
  
 tag 'sydb_incr_level0' 
  
  
 format '/u01/backup/%d_%s_%U'; 
  
  
 release channel dev; 
  
  
 release channel dev2; 
  
  
 } 
  
  
 
  
  
allocated channel: dev 
  
  
channel dev: SID=181 device type=DISK 
  
  
 
  
  
allocated channel: dev2 
  
  
channel dev2: SID=18 device type=DISK 
  
  
 
  
  
 
  
  
Starting backup at 29-MAY-15 
  
  
current log archived 
  
  
channel dev: starting archived log backup set 
  
  
channel dev: specifying archived log(s) in backup set 
  
  
input archived log thread=1 sequence=17 RECID=1 STAMP=880994007 
  
  
channel dev: starting piece 1 at 29-MAY-15 
  
  
channel dev2: starting archived log backup set 
  
  
channel dev2: specifying archived log(s) in backup set 
  
  
input archived log thread=1 sequence=18 RECID=2 STAMP=880994016 
  
  
input archived log thread=1 sequence=19 RECID=3 STAMP=880994311 
  
  
channel dev2: starting piece 1 at 29-MAY-15 
  
  
channel dev: finished piece 1 at 29-MAY-15 
  
  
piece handle=/u01/backup/SYDB_1_01q85q07_1_1 tag=SYDB_INCR_LEVEL0 comment=NONE 
  
  
channel dev: backup set complete, elapsed time: 00:00:07 
  
  
channel dev: deleting archived log(s) 
  
  
archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_17_880905808.dbf RECID=1 STAMP=880994007 
  
  
channel dev2: finished piece 1 at 29-MAY-15 
  
  
piece handle=/u01/backup/SYDB_2_02q85q07_1_1 tag=SYDB_INCR_LEVEL0 comment=NONE 
  
  
channel dev2: backup set complete, elapsed time: 00:00:08 
  
  
channel dev2: deleting archived log(s) 
  
  
archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_18_880905808.dbf RECID=2 STAMP=880994016 
  
  
archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_19_880905808.dbf RECID=3 STAMP=880994311 
  
  
Finished backup at 29-MAY-15 
  
  
 
  
  
Starting backup at 29-MAY-15 
  
  
channel dev: starting incremental level 0 datafile backup set 
  
  
channel dev: specifying datafile(s) in backup set 
  
  
input datafile file number=00001 name=/u01/oradata/sydb/system01.dbf 
  
  
input datafile file number=00004 name=/u01/oradata/sydb/users01.dbf 
  
  
input datafile file number=00005 name=/u01/oradata/sydb/tbs01.dbf 
  
  
channel dev: starting piece 1 at 29-MAY-15 
  
  
channel dev2: starting incremental level 0 datafile backup set 
  
  
channel dev2: specifying datafile(s) in backup set 
  
  
input datafile file number=00003 name=/u01/oradata/sydb/undotbs01.dbf 
  
  
input datafile file number=00002 name=/u01/oradata/sydb/sysaux01.dbf 
  
  
channel dev2: starting piece 1 at 29-MAY-15 
  
  
channel dev: finished piece 1 at 29-MAY-15 
  
  
piece handle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 tag=TAG20150529T163839 comment=NONE 
  
  
channel dev: backup set complete, elapsed time: 00:00:35 
  
  
channel dev2: finished piece 1 at 29-MAY-15 
  
  
piece handle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 tag=TAG20150529T163839 comment=NONE 
  
  
channel dev2: backup set complete, elapsed time: 00:00:35 
  
  
Finished backup at 29-MAY-15 
  
  
 
  
  
Starting backup at 29-MAY-15 
  
  
current log archived 
  
  
channel dev: starting archived log backup set 
  
  
channel dev: specifying archived log(s) in backup set 
  
  
input archived log thread=1 sequence=20 RECID=4 STAMP=880994354 
  
  
channel dev: starting piece 1 at 29-MAY-15 
  
  
channel dev: finished piece 1 at 29-MAY-15 
  
  
piece handle=/u01/backup/SYDB_5_05q85q1i_1_1 tag=SYDB_INCR_LEVEL0 comment=NONE 
  
  
channel dev: backup set complete, elapsed time: 00:00:01 
  
  
channel dev: deleting archived log(s) 
  
  
archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_20_880905808.dbf RECID=4 STAMP=880994354 
  
  
Finished backup at 29-MAY-15 
  
  
 
  
  
Starting Control File and SPFILE Autobackup at 29-MAY-15 
  
  
piece handle=/u01/backup/c-3634177744-20150529-00_control.bkp comment=NONE 
  
  
Finished Control File and SPFILE Autobackup at 29-MAY-15 
  
  
 
  
  
released channel: dev 
  
  
 
  
  
released channel: dev2 
 
 

By using the backup log, you can know which data files are backed up and which are not backed up or have not been backed up successfully. For example, you can find that many data blocks are damaged and the tablespace is excluded from the backup (exclude) table space is skip. It is very important to pay attention to this information, because once a disaster occurs, it means your database recovery success rate and helps you optimize and adjust the backup script.