Recovery with Archiving  «Prev  Next»

Lesson 6 Recovering an open database, initially open
Objective Demonstrate the steps to perform recover open database, initially open.

Recover open Database Oracle

Recovering open Database | initially open

SQL> SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, STATUS FROM 
      > DBA_DATA_FILES;
FILE FILE_NAME                            TABLESPACE   STATUS
---- -----------------------------------  -------- ---------
     1     C:\ORANT\DATABASE\SYS1ORCL.ORA   SYSTEM      AVAILABLE
     2     C:\ORANT\DATABASE\USR1ORCL.ORA   USER_DATA AVAILABLE
     3     C:\ORANT\DATABASE\RBS1ORCL.ORA   RBS         AVAILABLE
...

If one of your junior DBAs informed you that he accidentally deleted the datafile USR1ORCL.ORA using an operating system command, what might you do? Since you know the database is currently open, you must determine to which tablespace the datafile belongs. From the results of the command, you find out that the removed datafile USR1ORCL.ORA does not belong to the system or rollback segment tablespaces. In this case, the best option for recovery, is to use the method recover open database, initially open.

Steps to Recover Open Database, (initially open) in Oracle

  1. Make sure the damaged datafile is offline.
  2. Restore the damaged file using the operating system copy command.
  3. Use the RECOVER DATAFILE or RECOVER TABLESPACE command to apply the archived and the redo logs to the restored datafile.
  4. When recovery is finished, all datafiles are synchronized. Bring the datafile online via the 
    ALTER DATABASE DATAFILE '<FILENAME>';
    statement.

How It Works

As you can see in the two examples, you can use the host command with or without an operating system command as a parameter. If you run the host command as part of a series of RMAN commands, RMAN executes the host command and continues with the rest of the commands. When you execute the host command by itself, RMAN displays the operating system command prompt and resumes after you exit the command-line subshell.

Scripting RMAN

Problem: You want to automate an RMAN process by executing a set of commands that you have placed into a script file. You do not want to type each command one at a time. You want to start the entire sequence of commands and walk away while they execute. You may even want to execute your script periodically via a job scheduler, such as cron.

The following series of images walks you through the steps of the recover open database, initially open method.
You may also preview all steps.

Recover an open Oracle database, initially open

1) The DBA accesses the Server Manager and connect as sysdba
(1) The DBA accesses the Server Manager and (2)connects as sysdba. (3)The DBA queries the data dictionary views V$DATAFILE and V$DATAFILE_HEADER to check whether the damaged datafile is offline. (4) the result show that the file USR1ORCL.ORA is offline.

2) Since the file is offline, the DBA can restore the file.
Since the file is offline, the DBA can restore the file. This is a required step before performing recovery.
(5) First the DBA accesses the operating system.
(6) Then the DBA restores the damaged datafile from the backup.
(7) After copying the file to the original place, the DBA goes back to the Server Manager and (8) starts the recovery.
The DBA uses the AUTOMATIC option for the recover command.


3) After the recovery is finished, the DBA needs to bring the datafile
(9) After the recovery is finished, the DBA needs to bring the datafile

  1. The DBA accesses the Server Manager and (2)connects as sysdba.
  2. Since the file is offline, the DBA can restore the file. This is a required step before performing recovery.
  3. (9) After the recovery is finished, the DBA needs to bring the datafile


The next lesson demonstrates how to recover an open database, initially closed.

Steps for Complete Recovery of Open Datbase

Click the link below to read about complete recovery of an open database, initially open.
Steps for Complete Recovery of Open Datbase

Steps for complete recovery of open database, initially open (Legacy)

The correct order is as follows:
  1. Take the desired datafile offline.
  2. Restore the datafile.
  3. Recover the datafile (or tablespace).
  4. Take the datafile (or tablespace) online.


Note to Reader: The concept of "open database, initially open" was a technique that was used for Oracle 8i and is no longer in use for Oracle Database Backup. This technique involved opening the database in read-only mode, which allowed the backup to be performed without interfering with normal database operations. However, this technique was also more vulnerable to errors and could lead to data corruption.
As Oracle Databases have evolved, more reliable and efficient backup techniques have been developed. These techniques, such as RMAN and Data Guard, allow backups to be performed while the database is still online and in read-write mode. As a result, the "open database, initially open" technique is no longer considered a best practice for Oracle Database backups.
Here is a table summarizing the pros and cons of the "open database, initially open" technique:
Pros Cons
Allows backups to be performed without interfering with normal database operations Vulnerable to errors and could lead to data corruption
Relatively simple to implement Less efficient than modern backup techniques

In general, it is recommended to use modern backup techniques such as RMAN or Data Guard for Oracle Database backups. These techniques are more reliable and efficient, and they can be used to perform both full and incremental backups.