The term complete recovery means that you can recover all transactions that were committed before a failure occurred. Complete recovery doesn’t mean you that completely restore and recover the entire Oracle database 12C. For instance, if only one data file has experienced media failure, you need to restore and recover only the damaged data file to perform a complete recovery.
Tip If you have access to a test or development database, take the time to walk through every step in each of the examples that follow. Going through these steps can teach you more about backup and recovery than any documentation.
The steps outlined here apply to any database backed up while in archivelog mode. It doesn’t matter if you made a cold backup or hot backup. The steps to restore and recover data files are the same, as long as the Oracle database 12C was in archivelog mode during the backup. For a complete recovery, you need
- to be able to restore the data files that have experienced media failure
- access to all archive redo logs generated since the last backup was started
- intact online redo logs
Here is the basic procedure for a complete recovery:
- Place the database in mount mode; this prevents normal user transaction processing from reading/writing to data files being restored. (If you're not restoring the SYSTEM or UNDO tablespace, you have the option of opening the database and manually taking the data files offline before restoring them. If you do this, make sure you place the data files online after the recovery is complete.)
- Restore the damaged data files with an OS copy utility.
- Issue the appropriate SQL*Plus RECOVER command to apply any information required in the archive redo logs and online redo logs.
- Alter the database open.
The next several sections demonstrate some common complete restore-and-recovery scenarios. You should be able to apply these basic scenarios to diagnose and recover from any complex situation you find yourself in.
Restoring and Recovering with the Database Offline
This section details a simple restore-and-recovery scenario. Described next are the steps to simulate a failure and then perform a complete restore and recovery. Try this scenario in a development database. Ensure that you have a good backup and that you aren’t trying this experiment in a database that contains critical business data.
Before you start this example, create a table, and insert some data. This table and data are selected from the end of the complete recovery process to demonstrate a successful recovery:
SQL> create table foo(foo number) tablespace users;
SQL> insert into foo values(1);
SQL> commit;Now, switch the online logs several times. Doing so ensures that you have to apply archive redo logs as part of the recovery:
SQL> alter system switch logfile;The forward slash (/) reruns the most recently executed SQL statement:
SQL> /
SQL> /
SQL> /Next, simulate a media failure by renaming the data file associated with the USERS tablespace. You can identify the name of this file with this query:
SQL> select file_name from dba_data_files where tablespace_name='USERS';
FILE_NAME
----------------------------------
/u01/dbfile/O12C/users01.dbfFrom the OS, rename the file:
$ mv /u01/dbfile/O12C/users01.dbf /u01/dbfile/O12C/users01.dbf.oldAnd, attempt to stop your Oracle 12C database:
$ sqlplus / as sysdba
SQL> shutdown immediate;You should see an error such as this:
ORA-01116: error in opening database file ...If this were a real disaster, it would be prudent to navigate to the data file directory, list the files, and see if the file in question was in its correct location. You should also inspect the alert.log file to see if any relevant information is logged there by Oracle.
Now that you’ve simulated a media failure, the next several steps walk you through a restore and complete recovery.
Step 1. Place Your Database in Mount Mode
Before you place your database in mount mode, you may need to first shut it down, using ABORT:
$ sqlplus / as sysdba
SQL> shutdown abort;
SQL> startup mount;Step 2. Restore the Data File from the Backup
The next step is to copy from the backup the data file that corresponds to the one that has had a failure:
$ cp /u01/hbackup/O12C/users01.dbf /u01/dbfile/O12C/users01.dbfAt this point, it’s instructional to ponder what Oracle would do if you attempted to start your Oracle database 12C. When you issue the ALTER DATABASE OPEN statement, Oracle inspects the SCN in the control file for each data file. You can examine this SCN by querying V$DATAFILE:
SQL> select checkpoint_change# from v$datafile where file#=4;
CHECKPOINT_CHANGE#
------------------
3543963Oracle compares the SCN in the control file with the SCN in the data file header. You can check the SCN in the data file header by querying V$DATAFILE_HEADER; for example,
select file#, fuzzy, checkpoint_change#
from v$datafile_header
where file#=4;
FILE# FUZ CHECKPOINT_CHANGE#
---------- --- ------------------
4 YES 3502285Note that the SCN recorded in V$DATAFILE_HEADER is less than the SCN in V$DATAFILE for the same data file. If you attempt to open your database, Oracle throws an error stating that media recovery is required (meaning that you need to apply redo) to synchronize the SCN in the data file with the SCN in the control file. The FUZZY column is set to YES. This indicates that redo must be applied to the data file before it can be opened for use. Here is what happens when you try to open the database at this point:
SQL> alter database open;
alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery...Oracle doesn’t let you open the Oracle 12C database until the SCN in all data file headers matches the corresponding SCN in the control file.
Step 3. Issue the Appropriate RECOVER Statement
The archive redo logs and online redo logs have the information required to catch up the data file SCN to the control file SCN. You can apply redo to the data file that needs media recovery by issuing one of the following SQL*Plus statements:
- RECOVER DATAFILE
- RECOVER TABLESPACE
- RECOVER DATABASE
Because only one data file in this example needs to be recovered, the RECOVER DATAFILE statement is appropriate. However, keep in mind that you can run any of the previously listed RECOVER statements, and Oracle will figure out what needs to be recovered. In this particular scenario, you may find it easier to remember the name of the tablespace that contains the restored data file(s) than to remember the data file name(s). Next, any data files that need recovery in the USERS tablespace are recovered:
SQL> recover tablespace users;At this point, Oracle uses the SCN in the data file header to determine which archive redo log or online redo log to use to begin applying redo. You can view the starting log sequence number that RMAN will use to begin the recovery process via the following query:
select
HXFNM file_name
,HXFIL file_num
,FHTNM tablespace_name
,FHTHR thread
,FHRBA_SEQ sequence
from X$KCVFH
where FHTNM = 'USERS';If all the redo required is in the online redo logs, Oracle applies that redo and displays this message:
Media recovery complete.If Oracle needs to apply redo that is only contained in archived redo logs (meaning that the online redo log that contained the appropriate redo has already been overwritten), you’re prompted with a recommendation from Oracle as to which archive redo log to apply first:
ORA-00279: change 3502285 generated at 11/02/2012 10:49:39 needed for thread 1
ORA-00289: suggestion : /u01/oraarch/O12C/1_1_798283209.dbf
ORA-00280: change 3502285 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}You can press Enter or Return (<RET>) to have Oracle apply the suggested archive redo log file, specify a file name, specify AUTO to instruct Oracle to apply any suggested files automatically, or type CANCEL to cancel out of the recovery operation.
In this example, specify AUTO. Oracle applies all redo in all archive redo log files and online redo log files to perform a complete recovery:
AUTOThe last message displayed after all required archive redo and online redo have been applied is this:
Log applied.
Media recovery complete.Step 4. Alter Your Database Open
After the media recovery is complete, you can open your database:
SQL> alter database open;You can now verify that the transaction you committed just prior to the media failure was restored and recovered:
SQL> select * from foo;
FOO
----------
1Restoring and Recovering with a Database Online
If you lose a data file associated with a tablespace other than SYSTEM and UNDO, you can restore and recover the damaged data file while leaving the database online. For this to work, any data files being restored and recovered must be taken offline first. You may be alerted to an issue with a data file in which a user is attempting to update a table and sees an error such as this:
SQL> insert into foo values(2);
ORA-01116: error in opening database file ...You navigate to the OS directory that contains the data file and determine that it has been erroneously removed by a system administrator.
In this example the data file associated with the USERS tablespace is taken offline and subsequently restored and recovered while the rest of the database remains online. First, place take the data file offline:
SQL> alter database datafile '/u01/dbfile/O12C/users01.dbf' offline;Now, restore the appropriate data file from the backup location:
$ cp /u01/hbackup/O12C/users01.dbf /u01/dbfile/O12C/users01.dbfIn this situation, you can’t use RECOVER DATABASE. The RECOVER DATABASE statement attempts to recover all data files in the Oracle database 12C, of which the SYSTEM tablespace is part. The SYSTEM tablespace can’t be recovered while the database is online. If you use the RECOVER TABLESPACE, all data files associated with the tablespace must be offline. In this case, it’s more appropriate to recover at the data file level of granularity:
SQL> recover datafile '/u01/dbfile/O12C/users01.dbf';Oracle inspects the SCN in the data file header and determines which archive redo log or online redo log to use to start applying redo. If all redo required is in the online redo logs, you see this message:
Media recovery complete.If the starting point for redo is contained only in an archive redo log file, Oracle suggests which file to start with:
ORA-00279: change 3502285 generated at 11/02/2012 10:49:39 needed for thread 1
ORA-00289: suggestion : /u01/oraarch/O12C/1_1_798283209.dbf
ORA-00280: change 3502285 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}You can type AUTO to have Oracle apply all required redo in archive redo log files and online redo log files:
AUTOIf successful, you should see this message:
Log applied.
Media recovery complete.You can now bring the data file back online:
SQL> alter database datafile '/u01/dbfile/O12C/users01.dbf' online;If successful, you should see this:
Database altered.Restoring Control Files
When you’re dealing with user-managed backups, you usually restore the control file in one of these situations:
- A control file is damaged, and the file is multiplexed.
- All control files are damaged.
These two situations are covered in the following sections.
Restoring a Damaged Control File When Multiplexed
If you configure your database with more than one control file, you can shut down the Oracle database 12C and use an OS command to copy an existing control file to the location of the missing control file. For example, from the initialization file, you know that two control files are used for this database:
SQL> show parameter control_files
NAME TYPE VALUE
---------------------------- ----------- ------------------------------
control_files string /u01/dbfile/O12C/control01.ctl
,/u02/dbfile/O12C/control02.ctlSuppose the control02.ctl file has become damaged. Oracle throws this error when querying the data dictionary:
ORA-00210: cannot open the specified control file...When a good control file is available, you can shut down the database, move the old/bad control file (this preserves it, in the event that it is later needed for root cause analysis), and copy the existing good control file to the name and location of the bad control file:
SQL> shutdown abort;
$ mv /u02/dbfile/O12C/control02.ctl /u02/dbfile/O12C/control02.ctl.old
$ cp /u01/dbfile/O12C/control01.ctl /u02/dbfile/O12C/control02.ctlNow, restart the database:
SQL> startup;In this manner, you can restore a control file from an existing control file.
Restoring When All Control Files Are Damaged
If you lose all of your control files, you can restore one from a backup, or you can recreate the control file. As long as you have all your data files and any required redo (archive redo and online redo), you should be able to recover your database completely. The steps for this scenario are as follows:
- Shut down the database.
- Restore a control file from the backup.
- Start the database in mount mode, and initiate database recovery, using the RECOVER DATABASE USING BACKUP CONTROLFILE clause.
- For a complete recovery, manually apply the redo contained in the online redo logs.
- Open the database with the OPEN RESETLOGS clause.
In this example all control files for the database were accidentally deleted, and Oracle subsequently reports this error:
ORA-00210: cannot open the specified control file...Step 1. Shut Down the Database
First, shut down the database:
SQL> shutdown abort;Step 2. Restore the Control File from the Backup
This database was configured with just one control file, which you copy back from the backup location, as shown:
$ cp /u01/hbackup/O12C/controlbk.ctl /u01/dbfile/O12C/control01.ctlIf more than one control file is being used, you have to copy the backup control file to each control file and location name listed in the CONTROL_FILES initialization parameter.
Step 3. Start the Database in Mount Mode, and Initiate Database Recovery
Next, start the database in mount mode:
SQL> startup mount;After the control file(s) and data files have been copied back, you can perform a recovery. Oracle knows that the control file was from a backup (because it was created with the ALTER DATABASE BACKUP CONTROLFILE statement), so the recovery must be performed with the USING BACKUP CONTROLFILE clause:
SQL> recover database using backup controlfile;At this point, you’re prompted for the application of archive redo log files:
ORA-00279: change 3584431 generated at 11/02/2012 11:48:46 needed for thread 1
ORA-00289: suggestion : /u01/oraarch/O12C/1_8_798283209.dbf
ORA-00280: change 3584431 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}Type AUTO to instruct the recovery process to apply all archive redo logs automatically:
AUTOThe recovery process applies all available archive redo logs. The recovery process has no one way of determining where the archive redo stream ends and therefore tries to apply an archive redo log that doesn’t exist, resulting in a message such as this:
ORA-00308: cannot open archived log '/u01/oraarch/O12C/1_10_798283209.dbf'
ORA-27037: unable to obtain file statusThe prior message is to be expected. Now, attempt to open the Oracle database 12C:
SQL> alter database open resetlogs;Oracle throws the following error in this situation:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/dbfile/O12C/system01.dbf'Step 4. Apply Redo Contained in the Online Redo Logs
Oracle needs to apply more redo to synchronize the SCN in the control file with the SCN in the data file header. In this scenario the online redo logs are still intact and contain the required redo. To apply redo contained in the online redo logs, first identify the locations and names of the online redo log files:
select a.sequence#, a.status, a.first_change#, b.member
from v$log a, v$logfile b
where a.group# = b.group#
order by a.sequence#;Here is the partial output for this example:
SEQUENCE# STATUS FIRST_CHANGE# MEMBER
---------- ---------------- ------------- ------------------------------
6 INACTIVE 3543960 /u01/oraredo/O12C/redo03a.rdo
6 INACTIVE 3543960 /u02/oraredo/O12C/redo03b.rdo
7 INACTIVE 3543963 /u02/oraredo/O12C/redo01b.rdo
7 INACTIVE 3543963 /u01/oraredo/O12C/redo01a.rdo
8 CURRENT 3583986 /u02/oraredo/O12C/redo02b.rdo
8 CURRENT 3583986 /u01/oraredo/O12C/redo02a.rdoNow, reinitiate the recovery process:
SQL> recover database using backup controlfile;The recovery process prompts for an archive redo log that doesn’t exist:
ORA-00279: change 3584513 generated at 11/02/2012 11:50:50 needed for thread 1
ORA-00289: suggestion : /u01/oraarch/O12C/1_10_798283209.dbf
ORA-00280: change 3584513 for thread 1 is in sequence #10
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}Instead of supplying the recovery process with an archive redo log file, type in the name of a current online redo log file (you may have to attempt each online redo log until you find the one that Oracle needs). This instructs the recovery process to apply any redo in the online redo log:
/u01/oraredo/O12C/redo01a.rdoYou should see this message when the correct online redo log is applied:
Log applied.
Media recovery complete.Step 5. Open the Database with RESETLOGS
The database is completely recovered at this point. However, because a backup control file was used for the recovery process, the database must be opened with the RESETLOGS clause:
SQL> alter database open resetlogs;Upon success, you should see this:
Database altered.
