Backup Options   «Prev  Next»

Lesson 6 Restoring files to an alternative location
Objective Restore files to a different location after media failure.

Restoring Files to a Different Location After Media Failure in Oracle 12c

In the event of a media failure, Oracle Database 12c provides robust mechanisms to restore and recover data files, ensuring database integrity and minimizing downtime. Here's a step-by-step guide to restore files to a different location:
  1. Assessment of Damage:
    • Utilize the `V$DATABASE` and `V$DATAFILE` views to ascertain which files have been affected by the media failure. 
      SELECT FILE#, STATUS FROM V$DATAFILE WHERE STATUS = 'RECOVER';
  2. Initiate Database in Mount Mode:
    • If the database is running, shut it down. 
      SHUTDOWN IMMEDIATE;
    • Start the database in mount mode. 
      STARTUP MOUNT;
  3. Determine Restore Location:
    • Choose a different location with sufficient space to restore the damaged files. For this guide, let's use `/newpath/` as the new location.
  4. Restore Data Files:
    • Utilize the `RMAN` (Recovery Manager) tool to perform the restoration. Initiate RMAN and connect to the target database. 
      rman TARGET /
    • Restore the data file to the new location. 
      RESTORE DATAFILE '/originalpath/originalfilename.dbf' TO '/newpath/newfilename.dbf';
  5. Update Database Control Files:
    • Inform the database about the new location of the restored data files. 
      ALTER DATABASE RENAME FILE '/originalpath/originalfilename.dbf' TO '/newpath/newfilename.dbf';
  6. Recovery of Data Files:
    • In RMAN, execute the recovery command to apply any pending changes from the redo logs. 
      RECOVER DATAFILE '/newpath/newfilename.dbf';
  7. Open the Database:
    • Once the restoration and recovery are complete, open the database. 
      ALTER DATABASE OPEN;
  8. Post-Restoration Checks:
    • It's crucial to verify the integrity of the restored files. Execute the following: 
      SELECT FILE#, STATUS FROM V$DATAFILE;
    • Ensure that all data files have a status of "ONLINE."
  9. Regular Backups:
    • To safeguard against future media failures, schedule regular RMAN backups. Implement a backup strategy that encompasses full, incremental, and archived log backups.
  10. Monitor Database Health:
    • Regularly inspect database alert logs and listen for any warnings or errors to preempt potential issues.
By meticulously following the above steps, you can effectively restore files to a different location after a media failure in Oracle 12c, ensuring the continuity and reliability of your database operations.

In an earlier lesson, you learned how to recover a database by restoring backup files after a media failure occurs and is corrected. However, if a media failure cannot be corrected in time and it is necessary to have the database operating, you can restore the backup files to an alternative location in order to make the database accessible.

Restoring files to a different location

Restoring backup files to a different location involves a few simple steps. As you learned earlier in this module, all of the datafiles, control files, and redo log files must be restored, not just the damaged files. This will guarantee that the entire database is synchronized to a single point in time and functions properly. View the code below to see how to restore backup files to a different location.
You may also preview all the steps.

Steps to restore Backup Files to a different location [Legacy]

  1. Shut down the database by issuing the SHUTDOWN ABORT command.
  2. Restore the most recent database backup to a different location.
  3. Edit the parameter file to indicate the new location of the control file.
  4. Start an Oracle instance using the restored backup files and edited parameter file and mount the database by issuing the STARTUP MOUNT PFILE=INITSID.ORA command. Note: do not open the database at this point.
  5. Update the control file with the new file location by issuing the ALTER DATABASE RENAME FILE <FILENAME> TO <FILENAME>; command.
  6. The location for all restored datafiles and online redo log files must be updated in the control file. To check the information about the file location change, use the data dictionary view V$DATAFILE, which shows all datafile information recorded in the control file.
  7. Repeat step 5 until all the necessary file locations are changed.
  8. Open the database by issuing the ALTER DATABASE OPEN; command.

Inconsistent Backups

Any database backup that is not consistent is an inconsistent backup. A backup made when the database is open is inconsistent, as is a backup made after an instance failure or SHUTDOWN ABORT command. When a database is restored from an inconsistent backup, Oracle Database must perform media recovery before the database can be opened, applying any pending changes from the redo logs.
Note: RMAN does not permit you to make inconsistent backups when the database is in NOARCHIVELOG mode. If you employ user-managed backup techniques for a NOARCHIVELOG database, then you must not make inconsistent backups of this database.
If the database runs in ARCHIVELOG mode, and you back up the archived redo logs and datafiles, inconsistent backups can be the foundation for a sound backup and recovery strategy. Inconsistent backups offer superior availability because you do not have to shut down the database to make backups that fully protect the database.

The code below represents legacy output from an Oracle 8i installation circa 2000.
This is what you would have seen on your screen as you restored files to an alternate location. 
SQL>
SQL> SHUTDOWN ABORT
ORACLE instance shut down.
SQL> HOST
Microsoft(R) Windows NT(TM)
(C) Copyright 1985-1996 Microsoft Corp.

C:\>COPY D:\ORCL_BCKUP\*.* D:\ORCL_NEW
D:\ORCL_BCKUP\Ctl1orcl.ora
D:\ORCL_BCKUP\LOG1ORCL.ORA
D:\ORCL_BCKUP\LOG2ORCL.ORA
D:\ORCL_BCKUP\LOG3ORCL.ORA
D:\ORCL_BCKUP\LOG4ORCL.ORA
D:\ORCL_BCKUP\OEM1ORCL.ORA
D:\ORCL_BCKUP\RBS1ORCL.ORA
D:\ORCL_BCKUP\SYS1ORCL.ORA
D:\ORCL_BCKUP\TMP1ORCL.ORA
D:\ORCL_BCKUP\Usr1orcl.ora
       10 file(s) copied.


    
C:\>HOST
SQL> STARTUP MOUNT PFILE=D:\ORANT\DBS\INITORCL.ORA
ORACLE instance started.
Total System Global Area                         15077376 bytes
Fixed Size                                          49152 bytes
Variable Size                                    12906496 bytes
Database Buffers                                  2048000 bytes
Redo Buffers                                        73728 bytes
Database mounted.
SQL> ALTER DATABASE RENAME FILE 
     2> 'C:\ORANT\DATABASE\USR1ORCL.ORA'
     3> TO 'D:\ORCL_NEW\USR1ORCL.ORA';
Statement processed.
SQL> ALTER DATABASE RENAME FILE
     2> 'C:\ORANT\DATABASE\SYS1ORCL.ORA'
     3> TO 'D:\ORCL_NEW\SYS1ORCL.ORA';
Statement processed.
SQL> ALTER DATABASE RENAME FILE
     2> 'C:\ORANT\DATABASE\OEM1ORCL.ORA'
     3> TO 'D:\ORCL_NEW\OEM1ORCL.ORA';
Statement processed.
SQL> ALTER DATABASE RENAME FILE
     2> 'C:\ORANT\DATABASE\LOG4ORCL.ORA'
     3> TO 'D:\ORCL_NEW\LOG4ORCL.ORA';
Statement processed.
SQL> ALTER DATABASE RENAME FILE
     2> 'C:\ORANT\DATABASE\LOG3ORCL.ORA'
     3> TO 'D:\ORCL_NEW\LOG3ORCL.ORA';
Statement processed.
SQL> ALTER DATABASE RENAME FILE
     2> 'C:\ORANT\DATABASE\LOG2ORCL.ORA'
     3> TO 'D:\ORCL_NEW\LOG2ORCL.ORA';
Statement processed.
SQL> ALTER DATABASE RENAME FILE
     2> 'C:\ORANT\DATABASE\LOG1ORCL.ORA'
     3> TO 'D:\ORCL_NEW\LOG1ORCL.ORA';
Statement processed.
SQL> ALTER DATABASE OPEN;
Statement processed.
SQL>

RMAN: Restoring and Recovering Control Files

Because of media failure, we can lose control files as well as datafiles and redo log files. This section will go over different scenarios with control file loss and practical recovery procedures.

Recovering from the Loss of a Control File When a Mirrored Copy is Available

It is always best to have backup copies of the control files. Oracle recommends having at least three copies of the control file at all times. It is recommended to multiplex control files to different physical disks so that if one of them is lost due to media failure, it can be restored from the other physical disks. Please note that more than three copies can be made, but here, the more the merrier? is not the right thing to do. If multiplexed copies of the control file are going to be made and the database is doing a huge amount of control file updates, it would lead to the Control File Parallel Write wait event and also the Control File Enqueue will come into contention. So it is always better to use just the number of copies needed. If more than the maximum of eight copies of control files are made, the following error will occur: 

ORA-00208: number of control file names exceeds limit of 8

If there is just one lost copy of the mirrored files, there is much less to do. See what happens in the following scenarios with our resident DBA, Bob:
1st Scenario
Due to media failure, Bob lost one of the control files of the database and got the following error: 
ERROR at line 1:
 ORA-00210: cannot open the specified control file
 ORA-00202: control file: '/u02/oradata/db1/control01.ctl'
 ORA-27041: unable to open file
 Linux Error: 2: No such file or directory
 Additional information: 3

As Glenn has multiplexed controlfiles on the different hard drives, he performs the following actions:
  1. Shuts down the database (in abort mode)
  2. Copies available control file to the directory where the file was lost and renames it
  3. Starts the database
Here are the steps in detail to recover the database:
If the database is already up, bring it down immediately without invoking the checkpoint. 
SQL> shut abort;

Restoring a File to an Alternative Location

  1. To shut down the database and start the restoring process, type SHUTDOWN ABORT at the prompt and press Enter.
  2. To access the operating system, type HOST at the prompt and press Enter.
  3. To copy the backup files to a new location, type COPY D:\ORCL_BCKUP\*.* D:\ORCL_NEW at the prompt and press Enter.
  4. You must edit the initorcl.ora file to update the new file location for the control file. You can open this file in an editor like Notepad. Once you open the file, find the line control_files = and update it with the new location for the control file. In this particular example, we will make the change in the initorcl.oa for you. The original control_files = C:\orant\DATABASE\ctl1ORCL.ora is replaced by control_files = D:\ORCL_NEW\Ctl1orcl.ora in the initorcl.ora file. To access Server Manager, type EXIT at the prompt and press Enter.
  5. To mount the database, type STARTUP MOUNT PFILE=D:\ORANT\DBS\INITORCL.ORA at the prompt and press Enter (Note: do not open the database at this point).
  6. To update the new file location in the control file, type 
    ALTER DATABASE RENAME FILE 'C:\ORANT\DATABASE\USR1ORCL.ORA' TO
'D:\ORCL_NEW\USR1ORCL.ORA';
    at the prompt and press Enter (Note: The location for all restored datafiles and online redo log files must be updated in the control file. To check the information about the file location change, use the data dictionary view V$CONTROLFILE).
  7. The new file location for all the datafiles and redo logs must be updated within the control file. So you need to repeat the previous step for all of them. We will do this for you. Just press Enter at the prompt and observe the results.
  8. Click anywhere on the screen to see the rest of the results for the location of all the files and redo logs.
  9. Click anywhere on the screen to see the rest of the results for the location of all the files and redo logs.
  10. To open the database at the new location, D:\orcl_new, type ALTER DATABASE OPEN; and press enter.
  11. This is the end of the Simulation.

The next lesson concludes this module.