In Oracle, how can we correctly perform database recovery?

When the database needs to recover the media, in order to ensure that the database can smoothly perform the recovery process, restore the database to the current state. All we need to do is verify! What is verification? Of course, it is to verify whether the backup set and archive can be effectively restored. After the restore is prevented, when executing the recover operation, we find that the archive is missing and suddenly dumb.

For example, we have completely backed up the database when the current log serial number of the database is 3. When the current online log serial number of the database is 13, the database must be recovered if it is damaged. Assume that the online log Group of the database is three groups. It can be inferred that the online log serial numbers of the database are 11, 12, and 13 respectively. Therefore, after the database executes the restore database, when executing the recover operation, it is not difficult to infer that the database needs to archive applications 3, 4, 5, 6, 7, 8, 9, 10, and online logs 11, 12, and 13 for full recovery.

To ensure smooth and complete recovery, we need to restore the backup set called by restore before executing the restoration (statement: restorevalidate database) and archive 3-10 (statement: restore validate archivelog sequence between 3 and10) required to verify the recover process ).

Taking full recovery as an example:

1. The current log seq Number of the database is 59. We back up the database.

SQL> selectgroup #, archived, sequence #, status from v $ log;

GROUP # arc sequence # STATUS
---------------------------------------
1 YES 58 INACTIVE
2 NO 59 CURRENT
3 YES 57 INACTIVE

RMAN> backup database format'/backup/fullbk-% T-% U. bak ';

Starting backup at 12:03:28
Using channel ORA_DISK_1
Channel ORA_DISK_1: starting full datafilebackup set
Channel ORA_DISK_1: specifying datafile (s) in backup set
Input datafile file number = 00004 name =/Oracle/CRM/users01.dbf
Input datafile file number = 00001 name =/oracle/CRM/system01.dbf
Input datafile file number = 00002 name =/oracle/CRM/sysaux01.dbf
Input datafile file number = 00003 name =/oracle/CRM/undotbs01.dbf
Input datafile file number = 00005 name =/oracle/CRM/crm. dbf
Input datafile file number = 00006 name =/oracle/CRM/test. dbf
Input datafile file number = 00008 name =/oracle/CRM/jxc. dbf
Input datafile file number = 00007 name =/oracle/CRM/user01.dbf
Channel ORA_DISK_1: starting piece 1 at2014-02-17 12:03:29
Channel ORA_DISK_1: finished piece 1 at2014-02-17 12:05:57
Piecehandle =/backups/fullbk-20140217-3ep0rj0h_1_1.bak tag = TAG20140217T120328 comment = NONE
Channel ORA_DISK_1: backup set complete, elapsed time: 00:02:28
Channel ORA_DISK_1: starting full datafilebackup set
Channel ORA_DISK_1: specifying datafile (s) in backup set
Including current control file in backupset
Including current SPFILE in backup set
Channel ORA_DISK_1: starting piece 1 at2014-02-17 12:06:01
Channel ORA_DISK_1: finished piece 1 at2014-02-17 12:06:02
Piecehandle =/backups/fullbk-20140217-3fp0rj56_1_1.bak tag = TAG20140217T120328comment = NONE
Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 12:06:02

2 when the database online log is 69, the database crashes and the media needs to be restored.

SQL> selectgroup #, archived, sequence #, status from v $ Log;

GROUP # arc sequence # STATUS
---------------------------------------
1 YES 67 INACTIVE
2 YES 68 INACTIVE
3 NO 69 CURRENT

Note: here we can infer that if the database needs to be restored to the current state, all archives archived from 59 to 66 must be effectively restored. We only need to initiate the restore database preview command, Oracle will be able to give us an archive list, continue to look down.

 

3. Determine the backup set and archive entries required for database restoration.

Note that for the archive entries listed in restore database preview, full recovery of recover will not be fully applied, because the full recovery recover process is: Application-related archiving + all online logs, seq numbers are applied in ascending order. The recover process will be crawled later. Here we will mention it for the moment.

RMAN> restore database preview;

Starting restore at 16:14:21
Using target database control file insteadof recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 63 device type = DISK

 

List of Backup Sets
==============================

 

BS Key Type LV Size Device TypeElapsed Time Completion Time
-----------------------------------------------------------------
108 Full 2.03g disk 00:02:26 12:05:38
BP Key: 108 Status: AVAILABLE Compressed: NO Tag: TAG20140217T120328
Piece Name:/backup/fullbk-20140217-3ep0rj0h_1_1.bak
Note: The backups are always the latest backups of data files recorded in the rman database.
List of Datafiles in backup set 108
File LV Type Ckp SCN CkpTime Name
-------------------------------------------
1 Full 4028039 12:03:29/oracle/CRM/system01.dbf
2 Full 4028039 12:03:29/oracle/CRM/sysaux01.dbf
3 Full 4028039 12:03:29/oracle/CRM/undotbs01.dbf
4 Full 4028039 12:03:29/oracle/CRM/users01.dbf
5 Full 4028039 12:03:29/oracle/CRM/crm. dbf
6 Full 4028039 12:03:29/oracle/CRM/test. dbf
7 Full 4028039 12:03:29/oracle/CRM/user01.dbf
8 Full 4028039 12:03:29/oracle/CRM/jxc. dbf
Using channel ORA_DISK_1

List of Archived Log Copies for databasewith db_unique_name CRM
========================================================== ==================================

Key Thrd Seq S Low Time
--------------------------------------
131 1 59 A 2014-02-17 11:55:37
Name:/oracle/archivelog/arch_000059_839098938.arch

132 1 60 A 12:10:20
Name:/oracle/archivelog/arch_1_60_839098938.arch

133 1 61 A 12:10:21
Name:/oracle/archivelog/arch_1_61_839098938.arch

134 1 62 A 12:10:26
Name:/oracle/archivelog/arch_000062_839098938.arch

135 1 63 A 12:10:30
Name:/oracle/archivelog/arch_201763_839098938.arch

136 1 64 A 2014-02-17 12:10:31
Name:/oracle/archivelog/arch_000064_839098938.arch

137 1 65 A 2014-02-17 12:10:32
Name:/oracle/archivelog/arch_000065_839098938.arch

138 1 66 A 12:10:33
Name:/oracle/archivelog/arch_1_66_839098938.arch

139 1 67 A 12:10:34
Name:/oracle/archivelog/arch_000067_839098938.arch

140 1 68 A 12:10:36
Name:/oracle/archivelog/arch_000068_839098938.arch

Media recovery start SCN is 4028039
Recovery must be done beyond SCN 4028039 toclear datafile fuzziness
Finished restore at 2014-02-17 16:14:24

Note:
1 The Last archived seq shown in the seq number column above is 68 (from the beginning we can see that the current online Log File seq Number of the database is 69) that is to say, the last archived seq number in the archive list result displayed in the restore database preview is always better than the current online log (the current online log is the log group in which the v $ log status is currnt) the file seq number is smaller than 1.

2. The seq numbers of the online log Group of the current database are 67 68 69, respectively. It can be determined that after the last archive seq number of the recover application is 66, oracle reads online log files with seq numbers 67, 68, and 69 and continues to promote the database to achieve full database recovery.
The entire verification and recovery process is demonstrated below:

4. Verify that the backup set required for recovery can be recovered normally.

RMAN> restore validate database;

Note: This command will go directly to the rman database to find the latest backup set for verification, that is, the backup set displayed by the restore database preview command.

Starting restore at 16:14:59
Using channel ORA_DISK_1

Channel ORA_DISK_1: starting validation ofdatafile backup set
Channel ORA_DISK_1: reading from backuppiece/backup/fullbk-20140217-3ep0rj0h_1_1.bak
Channel ORA_DISK_1: piecehandle =/backups/fullbk-20140217-3ep0rj0h_1_1.bak tag = TAG20140217T120328
Channel ORA_DISK_1: restored backup piece 1
Channel ORA_DISK_1: validation complete, elapsed time: 00:00:36
Finished restore at 2014-02-17 16:15:35

 

5. Verify the application archive during recovery

 

RMAN> restore validate archivelogsequence between 59 and 66;

Starting restore at 16:16:34
Using channel ORA_DISK_1

Channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_000059_839098938.arch
Channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_60_839098938.arch
Channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_61_839098938.arch
Channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_000062_839098938.arch
Channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_201763_839098938.arch
Channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_64_839098938.arch
Channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_65_839098938.arch
Channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_66_839098938.arch
Finished restore at 2014-02-17 16:16:37

 

 

6. The restore and recover processes are as follows:

 

RMAN> restore database;

Starting restore at 16:36:23
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 63 device type = DISK

Channel ORA_DISK_1: starting datafilebackup set restore
Channel ORA_DISK_1: specifying datafile (s) to restore from backup set
Channel ORA_DISK_1: restoring datafile into 1to/oracle/CRM/system01.dbf
Channel ORA_DISK_1: restoring datafile00002 to/oracle/CRM/sysaux01.dbf
Channel ORA_DISK_1: restoring datafile00003 to/oracle/CRM/undotbs01.dbf
Channel ORA_DISK_1: restoring datafile00004 to/oracle/CRM/users01.dbf
Channel ORA_DISK_1: restoring datafile00005 to/oracle/CRM/crm. dbf
Channel ORA_DISK_1: restoring datafile00006 to/oracle/CRM/test. dbf
Channel ORA_DISK_1: restoring datafile00007 to/oracle/CRM/user01.dbf
Channel ORA_DISK_1: restoring datafile1_8 to/oracle/CRM/jxc. dbf
Channel ORA_DISK_1: reading from backuppiece/backup/fullbk-20140217-3ep0rj0h_1_1.bak
Channel ORA_DISK_1: piecehandle =/backups/fullbk-20140217-3ep0rj0h_1_1.bak tag = TAG20140217T120328
Channel ORA_DISK_1: restored backup piece 1
Channel ORA_DISK_1: restore complete, elapsed time: 00:02:08
Finished restore at 2014-02-17 16:38:35

Note: After restore, We can query the seq number (FHRBA_SEQ field) of the redo byte address (RBA) of x $ kcvfh to obtain the rba recorded in the header of the data file after restore database. seq number. This value indicates that the recover process needs to be applied from the seq number 59 archive.

Alternatively, you can compare the archived first_change # And next_change # To find that the recover must be archived.

SQL> select hxfil, fhscn, fhrba_seq fromx $ kcvfh;

Hxfil fhscn FHRBA_SEQ
------------------------------------
1 4028039 59
2 4028039 59
3 4028039 59
4 4028039 59
5 4028039 59
6 4028039 59
7 4028039 59
8 4028039 59

8 rows selected.

Of course, after restore database, we can directly query v $ recvoery_log to obtain the archive entries to be applied in the recover process, as shown below:
Select * from v $ recovery_log;

THREAD # SEQUENCE # TIME ARCHIVE_NAME
-----------------------------------------------------------------------------------------
1 59 17-FEB-14/oracle/archivelog/arch_000059_839098938.arch
1 60 17-FEB-14/oracle/archivelog/arch_1_60_839098938.arch
1 61 17-FEB-14/oracle/archivelog/arch_1_61_839098938.arch
1 62 17-FEB-14/oracle/archivelog/arch_000062_839098938.arch
1 63 17-FEB-14/oracle/archivelog/arch_201763_839098938.arch
1 64-17-feb-14/oracle/archivelog/arch_1_64_839098938.arch
1 65-17 feb-14/oracle/archivelog/arch_1_65_839098938.arch
1 66 17-FEB-14/oracle/archivelog/arch_1_66_839098938.arch

 

RMAN> recover database;

Starting recover at 16:45:01
Using channel ORA_DISK_1

Starting media recovery

Archived log for thread 1 with sequence 59is already on disk as file/oracle/archivelog/arch_1_59_839098938.arch
Archived log for thread 1 with sequence 60is already on disk as file/oracle/archivelog/arch_1_60_839098938.arch
Archived log for thread 1 with sequence 61is already on disk as file/oracle/archivelog/arch_1_61_839098938.arch
Archived log for thread 1 with sequence 62is already on disk as file/oracle/archivelog/arch_1_62_839098938.arch
Archived log for thread 1 with sequence 63is already on disk as file/oracle/archivelog/arch_1_63_839098938.arch
Archived log for thread 1 with sequence 64is already on disk as file/oracle/archivelog/arch_1_64_839098938.arch
Archived log for thread 1 with sequence 65is already on disk as file/oracle/archivelog/arch_1_65_839098938.arch
Archived log for thread 1 with sequence 66is already on disk as file/oracle/archivelog/arch_1_66_839098938.arch
Archived log for thread 1 with sequence 67is already on disk as file/oracle/archivelog/arch_1_67_839098938.arch
Archived log for thread 1 with sequence 68is already on disk as file/oracle/archivelog/arch_1_68_839098938.arch
Archived log filename =/oracle/archivelog/arch_1_59_839098938.arch thread = 1 sequence = 59
Archived log file name =/oracle/archivelog/arch_1_60_839098938.archthread = 1 sequence = 60
Archived log filename =/oracle/archivelog/arch_1_61_839098938.arch thread = 1 sequence = 61
Archived log filename =/oracle/archivelog/arch_1_62_839098938.arch thread = 1 sequence = 62
Archived log filename =/oracle/archivelog/arch_1_63_839098938.arch thread = 1 sequence = 63
Archived log filename =/oracle/archivelog/arch_1_64_839098938.arch thread = 1 sequence = 64
Archived log filename =/oracle/archivelog/arch_1_65_839098938.arch thread = 1 sequence = 65
Archived log filename =/oracle/archivelog/arch_1_66_839098938.arch thread = 1 sequence = 66
Media recovery complete, elapsed time: 00: 00: 08
Finished recover at 16:45:16
Note: here we can clearly see the archive entries of the application (marked in red)

7. The recover tracking process is as follows:

Alter database recoverlogfile '/oracle/archivelog/arch_201759_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_rj59_839098938.arch
Mon Feb 17 16:45:12 2014
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_000059_839098938.arch '...
Alter database recoverlogfile '/oracle/archivelog/arch_1_60_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_60_839098938.arch
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_1_60_839098938.arch '...
Alter database recoverlogfile '/oracle/archivelog/arch_1_61_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_rj61_839098938.arch

ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_rj61_839098938.arch '...
Alter database recoverlogfile '/oracle/archivelog/arch_000062_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_000062_839098938.arch
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_000062_839098938.arch '...
Alter database recoverlogfile '/oracle/archivelog/arch_201763_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_201763_839098938.arch
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_201763_839098938.arch '...
Alter database recoverlogfile '/oracle/archivelog/arch_000064_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_64_839098938.arch
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_000064_839098938.arch '...
Alter database recoverlogfile '/oracle/archivelog/arch_1_65_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_65_839098938.arch
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_000065_839098938.arch '...
Alter database recoverlogfile '/oracle/archivelog/arch_1_66_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_66_839098938.arch
Mon Feb 17 16:45:14 2014
Recovery of Online RedoLog: Thread 1 Group 1 Seq 67 Reading mem 0
Mem #0:/oracle/CRM/redo01.log
Recovery of Online RedoLog: Thread 1 Group 2 Seq 68 Reading mem 0
Mem #0:/oracle/CRM/redo02.log
Recovery of Online RedoLog: Thread 1 Group 3 Seq 69 Reading mem 0
Mem #0:/oracle/CRM/redo03.log
Media Recovery Complete (CRM)

Note: by tracking the entire recovery process, you can clearly observe that archive is applied before full recovery with recover, then, the entire database is fully restored through all online log files.

 

8. How to obtain the archive required for recovery if the database is not completely recovered

Taking Restoration Based on Time points as an example, we can use this to obtain the archive list required for restoring the database to this time point.

Run {
SQL 'alter session setnls_date_format = "yyyy-mm-dd hh24: mi: ss "';
Set until time = '2017-12-09: 05: 50: 12 ';
Restore database preview;
}

Summary:
1. When restoring the database, the first step is to check whether the database is archived, the second step is to check whether the database is backed up, and the third step is to verify the validity of the backup and archive. Finally, the entire recovery process is executed.

2. When the database is completely restored, we can clearly release the database completely recovered by comparing the archived list seq number displayed in the restore database preview with the seq Number of the on-line log group, apply archive for recover.