| Lesson 7
| Recovering an open database, initially closed
| List the steps to recover an open database, initially closed.
Recovering open Database, initially closed (Legacy)
Oracle does not use the "recover an open database, initially closed" technique to perform database backups.
This technique was used in the early days of Oracle Databases, but it has been deprecated in favor of more modern and reliable techniques.
Modern Oracle Database backup techniques, such as RMAN and Data Guard, allow backups to be performed while the database is still online and in read-write mode. This means that there is no need to take the database offline in order to perform a backup. Additionally, modern backup techniques are more efficient and take less time to complete than the "recover an open database, initially closed" technique.
Here are some of the reasons why Oracle does not use the "recover an open database, initially closed" technique to perform database backups:
- The technique is vulnerable to errors and could lead to data corruption.
- The technique is relatively slow and inefficient.
- The technique is not compatible with modern Oracle Database features, such as Automatic Workload Repository (AWR)  and Automatic Undo Management (AUM).
If you are using an older version of Oracle Database that still supports the "recover an open database, initially closed" technique, it is recommended that you upgrade to a newer version that supports modern backup techniques. You can also use Oracle Enterprise Manager to convert your older backups to a newer format.
Suppose a system failure brings down your database.
After some quick investigation, you find that the failure damaged disk 4, which contains datafile USR1ORCL.ORA.
As a well-organized DBA, you know that the datafile USR1ORCL.ORA. is not a system or rollback segment datafile, and users can still access the database even if this datafile is offline.
In this situation, you decide that the appropriate method to use is recover open database, initially closed.
The following simulation walks you through the steps to recover an open database, initially closed. You may also
Preview all Steps.
Perform Initially Closed Open Database Recovery [Legacy]
The steps to recover an open database are
- To make sure you are pointing to the correct database ORCL, you need to set ORACLE_SID. Type
SET ORACLE_SID=ORCL you should you use at the prompt. After you finish, press Enter.
- To access the Server Manager in the NT environment, type
SVRMGR30 at the prompt. After you finish, press Enter.
CONNECT INTERNAL at the prompt to connect as sysdba. After you finish, press Enter.
- The password ORACLE is entered for you. Now you need to check whether the damaged datafile USR1ORCL.ORA is offline. Type
SELECT A.FILE#, A.NAME, A.STATUS, B.STATUS FROM V$DATAFILE A, V$DATAFILE_HEADER B WHERE A.FILE# = B.FILE#; at the prompt. After you finish, press Enter.
- Since the damaged datafile USRORCL.ORA is still on line, you need to take it offline before you start recovery. Enter
ALTER DATABASE DATAFILE C:\ORANT\DATABASE\USR1ORCL.ORA OFFLINE; at the prompt to to take this datafile offline. After you finish, press Enter.
- Now you are ready to restore the damaged datafile using the operating system command. To access the operating system, type
HOST at the prompt. After you finish, press Enter.
- Suppose you have your backup at the path C:\ORANT\BACKUP and you original datafile path at C:\ORANT\DATABASE. Type
host copy C:\ORANT\BACKUP\USR1ORCL.ORA C:\ORANT\DATABASE\USR1ORCL.ORA
to restore the damaged datafile. After you finish, press Enter.
EXIT to go back to the Server Manager. After you finish, press Enter.
RECOVER DATAFILE C:\ORANT\DATABASE\USR1ORCL.ORA; to start to recover the datafile. After you finish, press Enter.
auto to continue the recovery automatically. After you finish, press Enter.
- You finished the recovery successfully. Now you need to put the datafile online. Type
ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\USR1ORCL.ORA' ONLINE;
at the prompt. After you finish, press Enter.
- You have completed the whole process of recovery. This is the end of the Simulation.
The following procedure has been deprecated and is no longer in use.
Perform Open Database Recovery, initially closed
- Since the database is closed and the datafile is damaged, you can only start the database in the mount mode. Type
STARTUP MOUNT PFILE=C:\ORANT\DATABASE\INITORCL.ORA at the prompt and press Enter.
- To check whether the damaged datafile is still online, type
SELECT A.FILE#, A.NAME, A.STATUS, B.STATUS FROM V$DATAFILE A, V$DATAFILE_HEADER B WHERE A.FILE# = B.FILE#; at the prompt and press Enter.
- To take the damaged datafile offline, type
ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\USR1ORCL.ORA' OFFLINE;
at the prompt and press Enter.
- Now you can start the database. Type
ALTER DATABASE OPEN;
at the prompt and press Enter.
- You are now ready to restore the damaged datafile. To access the operating system, type
HOST at the prompt and press Enter.
- To restore the file, type
COPY C:\DIGITAL\BACKUP\USR1ORCL.ORA C:\ORANT\DATABASE\USR1ORCL.ORA
at the prompt and press Enter.
- To access Server Manager, type
EXIT at the prompt and press Enter.
- To recover the datafile, type
RECOVER AUTOMATIC DATAFILE 'C:\ORANT\DATABASE\USR1ORCL.ORA'; at the prompt and press Enter. Because the automatic option is used in this command, Oracle will not prompt you about archive logs.
- Since the recovery is finished, you have to bring the recovered datafile online.
Type ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\USR1ORCL.ORA' ONLINE; at the prompt and press Enter.
- You have successfully recovered the datafile. This completes the Simulation. Click the Exit button to return to the lesson.
Performing Open Database Recovery
It is possible for a media failure to occur while the database remains open, leaving the undamaged data files online and available for use.
Damaged data files (but not the tablespaces that contain them) are automatically taken offline if the database writer cannot write to them. If the database writer cannot open a data file, an error is still returned. Queries that cannot read damaged files return errors, but the data files are not taken offline because of the failed queries. For example, you may run a SQL query and see output such as:
ERROR at line 1:
ORA-01116: error in opening database file 3
ORA-01110: data file 11: '/oracle/oradata/trgt/cwmlite02.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
You cannot use the procedure in this section to perform complete media recovery on the SYSTEM tablespace while the database is open. If the media failure damages data files of the SYSTEM tablespace, then the database automatically shuts down.
To restore data files in an open database:
The next lesson wraps up this module.
Follow these 3 Steps
To restore and recover damaged or missing data files:
Step 1: If the database is open, query V$RECOVER_FILE to determine which data files must be recovered and why they must be recovered. If you are planning to perform complete recovery rather than point-in-time recovery, then you can recover only those data files that require recovery, rather than the whole database. For point-in-time recovery, you must restore and recover all data files, unless you perform TSPITR .
You can also use Flashback Database, but this procedure affects all data files and returns the entire database to a past time.
You can query the V$RECOVER_FILE view to list data files requiring recovery by data file number with their status and error information.
SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME
Warning: You cannot use V$RECOVER_FILE with a control file restored from backup or a control file that was re-created after the time of the media failure affecting the data files. A restored or re-created control file does not contain the information needed to update V$RECOVER_FILE accurately.
You can also perform useful joins by using the data file number and the V$DATAFILE and V$TABLESPACE views to get the data file and tablespace names. Use the following SQL*Plus commands to format the output of the query:
COL DF# FORMAT 999
COL DF_NAME FORMAT A35
COL TBSP_NAME FORMAT A7
COL STATUS FORMAT A7
COL ERROR FORMAT A10
COL CHANGE# FORMAT 99999999
SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,
d.STATUS, r.ERROR, r.CHANGE#, r.TIME
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS#
AND d.FILE# = r.FILE#;
The ERROR column identifies the problem for each file requiring recovery.
Query the V$ARCHIVED_LOG and V$RECOVERY_LOG views to determine which archived redo log files are needed.
V$ARCHIVED_LOG lists file names for all archived redo logs, whereas V$RECOVERY_LOG lists only the archived redo logs that the database needs to perform media recovery. The latter view also includes the probable names of the files based on the naming convention specified by using the LOG_ARCHIVE_FORMAT parameter.
Warning: V$RECOVERY_LOG is only populated when media recovery is required for a data file. Thus, this view is not useful for a planned recovery, such as recovery from a user error. If a data file requires recovery, but no backup of the data file exists, then you need all redo generated starting from the time when the data file was added to the database.
If all archived logs are available in the default location, then continue.
If some archived logs must be restored, and if sufficient space is available, then restore the required archived redo log files to the location specified by LOG_ ARCHIVE_DEST_1.
The database locates the correct log automatically when required during media recovery. For example, you might enter a command such as the following on Linux or UNIX:
% cp /disk2/arch/* $ORACLE_HOME/oradata/trgt/arch
If sufficient space is not available, then restore some or all of the required archived redo log files to an alternative location.
- If the database is open, then take all tablespaces containing damaged data files offline. For example, if the tablespaces users and tools contain damaged data files, then execute the following SQL statements:
ALTER TABLESPACE users OFFLINE TEMPORARY;
ALTER TABLESPACE tools OFFLINE TEMPORARY;
If you specify TEMPORARY, then Oracle Database creates a checkpoint for all online data files in the tablespace. Files that are offline when you issue this statement may
require media recovery before you bring the tablespace back online. If you specify IMMEDIATE, then you must perform media recovery on the tablespace before bringing it back online.
- Inspect the media to determine the source of the problem.
You can use the DBVERIFY utility to run an integrity check on offline data files.
If the hardware problem that caused the media failure was temporary, and if the data was undamaged,
then no media recovery is required. You can bring the offline tablespaces online and resume normal operations.
If you cannot repair the problem, or if DBVERIFY reports corrupt blocks, then proceed to the Step 4.
- If files are permanently damaged, then use operating system commands to restore the most recent backup files of only the data files damaged by the media failure. For example, to restore users01.dbf you might use the cp command on Linux or UNIX as follows:
% cp /disk2/backup/users01.dbf $ORACLE_HOME/oradata/trgt/users01.dbf
If the hardware problem is fixed and the data files can be restored to their original
locations, then do so. Otherwise, restore the data files to an alternative storage
device. Do not restore undamaged data files, online redo logs, or control files.
- If you restored one or more damaged data files to alternative locations, then update the control file of the database to reflect the new data file names. For example, to change the file name of the data file in tablespace users you might enter:
ALTER DATABASE RENAME FILE '?/oradata/trgt/users01.dbf' TO '/disk2/users01.dbf';
- If you restored archived redo logs to an alternative location, then you can specify the location before media recovery with the LOGSOURCE parameter of the SET command in SQL*Plus. For example, if the logs are staged in /tmp, you can enter the following command:
SET LOGSOURCE /tmp
Alternatively, you can skip Step 6 and use the FROM parameter on the RECOVER command as in Step 7.
For example, if the logs are staged in /tmp, you can enter the following command:
RECOVER AUTOMATIC FROM '/tmp' TABLESPACE users, tools;
Warning: In some circumstances, if you do not have a backup of a
specific data file, you can use the ALTER DATABASE CREATE DATAFILE statement to create an empty replacement file that is recoverable.
Note: Overriding the redo log source does not affect the archive redo log destination for online redo log groups being archived.
- Connect to the database with administrator privileges, and start offline tablespace recovery of all damaged data files in one or more offline tablespaces using one step. For example, recover users and tools as follows:
RECOVER AUTOMATIC TABLESPACE users, tools;
The database begins the roll forward phase of media recovery by applying the necessary archived and online redo logs to reconstruct the restored data files. Unless the application of files is automated with the RECOVER AUTOMATIC or SET AUTORECOVERY ON commands, the database prompts for each required redo log file. Recovery continues until all required archived logs have been applied to the data files. The online redo logs are then automatically applied to the restored data files to complete media recovery.
If no archived redo logs are required for complete media recovery, then the database does not prompt for any. Instead, all necessary
online redo logs are applied, and media recovery is complete.
- When the damaged tablespaces are recovered up to the moment that media failure occurred, bring the offline tablespaces online. For example, to bring tablespaces users and tools online, issue the following statements:
ALTER TABLESPACE users ONLINE;
ALTER TABLESPACE tools ONLINE;
 Automatic Workload Repository:
In the context of Oracle backup, the Automatic Workload Repository (AWR) is a centralized repository that stores performance data about Oracle databases. This data can be used to monitor database performance, identify performance bottlenecks, and troubleshoot performance problems. The AWR is also used to generate performance reports and to create and manage database baselines.