>
Database Backup logo float
Could it be done better, have I written something wrong, you want to complain, please email me : rasmus@webmodelling.com

Oracle Restore Oracle Restore (Oracle Backup, Oracle Replicate)

Recovering Oracle can prove complicated - here I focus on actionable step-by-step how-tos. The best place to start is by identifying what your recovery need is. The 3 most important scenarios are :
  • Your media files are corrupted and you want to recover to the last point-in-time before media corruption - media recovery
  • Your database have been wrongly updated and you want to roll back the database to a former point-in-time - flashback or DBPITR
  • Whatever is wrong, you have a dump (.dmp) file created using the export (exp or expdp) utility, so you create a new database and import the dump file

Oracle versions tested here:
  1. Oracle 11gR2 on Windows/Linux/Solaris
  2. Oracle Express 10g XE on Windows/Linux

Content :

Oracle Restore Restore Oracle - Media Recovery

A media recovery is performed then you experience media failures like eg. a corrupt datafile.

Then restore & recover Oracle, you need a valid control file. If you controlfile have been corrupted or is missing, your absolute first task is to restore the control file before you can continue here.

(Media Recovery cannot be used in case of overwritten or deleted data, eg. a user wrongly updated some data in which case you will need Flashback or DBPITR (DataBase Point-In-Time Recovery)).

  • Media Recovery using RMAN :

    1. Open a shell.
    2. shell> rman target / : logon to RMAN using host credentials.
    3. rman> startup; : see how far Oracle can come - here Oracle can start and mount the database so the control file is valid. However the SYSAUX01.DBF datafile is either corrupted or missing or otherwise inaccessible (I deleted it so it is missing).
    4. rman shutdown immediate : shutdown rolling back pending transactions.
    5. rman startup mount; : start the database and set it in mounted state so that Oracle know the location of the datafiles.
    6. rman> restore database; : overwrite datafiles from backup
    7. rman> recover database; : apply redo files from backup
    8. How you open the database depends on whether you are in ARCHIVELOG or NOARCHIVELOG mode :
      • If in ARCHIVELOG mode :
        1. rman> alter database open; : in ARCHIVELOG mode, the recovery is exactly to the point then media failure happened, therefore there is no change of incarnation and the resetlogs keyword should not be used.
      • If in NOARCHIVELOG mode :
        1. rman> alter database open resetlogs; : in NOARCHIVELOG mode, the recovery is to the point in time then the latest backup was performed (also called incomplete recovery), therefore it is necessary to use the resetlogs keyword to specify that a new incarnation starts.
    9. rman> exit; : you are finished.
  • Media Recovery using Enterprise Manager :

    (very cumbersome)
    1. The database instance is down, however you do not yet know why, so press the "Startup" button to see what happens.
    2. Specify host credentials to login to database host machine and database credentials to allow Oracle to change the status of the database (here to startup the database). Press the "Ok" button.
    3. Confirm the action by clicking on the "Yes" button.
    4. Oracle can start and mount the database so the control file is valid. Press the "View Details" link.
    5. SYSAUX01.DBF is inaccessible (I deleted it so it is missing). Press the "Ok" button to return to the previous page.
    6. Click the "Perform Recovery" link.
    7. Sys login. Beware of the Connect String. Press the "Login" button.
    8. Host login. Press the "Continue" button.
    9. You can now define how and what you want to recover, here I choose "User Directed Recovery" and "Whole Database". Also don't forget your host credentials. Press the "Recover" button.
    10. Step 1 of 5. Choose "Recover to current time" since we try to recover until the latest possible moment. Press the "Next" button.
    11. This is a media recovery and therefore the wizard will skip step 2 Flashack.
    12. Step 3 of 5. I am not sure then it would be relevant to use a different restore location. Press the "Next" button.
    13. This is a restore & recovery operation and therefore the wizard will skip step 4 Schedule.
    14. Step 5 of 5. Confirm by clicking the "Next" button.
    15. Recovery seemed to succeed. Press the "Open Database" button.
    16. Database opened. Press the "Ok" button to get to the standard login page.
    17. Standard login (without connect string).
    18. Enterprise Manager home screen - you are finished.

Oracle Restore Recover Oracle - Flashback Recovery

Flashback is a very fast and convenient way to undo an update to the database.

If your data have been overwritten, eg. a user have by accident deleted a table or updated data that should not have been updated, then you will need to recover your data to a point-in-time before the data was overwritten.

There are 2 methods for point-in-time recovery, Flashback and DBPITR (DataBase Point-In-Time Recovery). If Flashback is enabled and data have been overwritten within the Flashback retention period (default 24 hours), Flashback is what your want to perform to get your data back. However if the data was overwritten more than 24 hours ago and your Flashback data is only going 24 hours back or you have not enabled Flashback at all, then you will need to do DBPITR (DBPITR involves restoring files from backup).

(If you have media failures, eg. corrupt datafiles, you cannot do FLASHBACK nor DBPITR, but must instead use Media Recovery).

  1. Confirm that Flashback is enabled :
    1. Open a shell.
    2. shell> sqlplus / as sysdba : logon to SQL*Plus as sysdba.
    3. sql> select flashback_on from v$database; : If the result is NO, then you CANNOT use Flashback, use DBPITR instead.
  2. Find the oldest time to which the database can be rolled back :
    1. sql> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; : set the date time format to higher precision (default precision is 'dd-mon-yyyy').
    2. sql> select sysdate, min(first_time>, (sysdate-min(first_time)) from v$flashback_database_logfile; : find the oldest time to which the database can be rolled back, if your database needs to roll back to an older time than that, then you CANNOT use Flashback, use DBPITR instead.
  • Flashback using SQL*Plus :

    (specify a point-in-time)
    1. Open a shell.
    2. shell> sqlplus / as sysdba : logon to SQL*Plus as sysdba.
    3. sql> shutdown immediate; : shutdown the database rolling back pending transactions.
    4. sql> startup mount; : start the database and set it in mounted stage (Oracle read the controlfile to eg. identify the datafiles location, but does not load the datafiles).
    5. sql> flashback database to timestamp to_date('2011-06-25 00:00:00', 'yyyy-mm-dd hh24:mi:ss'); : execute the flashback to the specified point-in-time using the specified time format.
    6. sql> alter database open resetlogs; : open the database (load the datafiles) and reset the logfiles to start a new incarnation.
    7. Note that your Enterprise Manager may not work properly after this operation - wait a little and it will likely work again.
  • Flashback using RMAN :

    (specify how long time you want to go back)
    1. Open a shell.
    2. shell> rman target / : logon to RMAN using host credentials.
    3. rman> shutdown immediate; : shutdown the database rolling back pending transactions.
    4. rman> startup mount; : start the database and set it in mounted stage (Oracle read the controlfile to eg. identify the datafiles location, but does not load the datafiles).
    5. rman> flashback database to time '(sysdate-1/24)'; : go one hour back.
    6. rman> alter database open resetlogs; : open the database (load the datafiles) and reset the logfiles to start a new incarnation.
  • Flashback using Enterprise Manager :

    (this is very cumbersome, however it is here for completness)

Flashback Query : enables you to view & restore data at a point in time. This is helpfull if accidentally overwriting data as normal restore cannot solve that problem (unless you save a timeline of backups)

More about using Flashback here and officially here.

Oracle Restore Restore Oracle - Point-In-Time (DBPITR) - using RMAN (CLI)

DBPITR (DataBase Point-In-Time Recovery) is the process of first restore backup files and then recover to a point in time. The point in time can be specified using either a date/time or a SCN (System Change Number). DBPITR is sometimes called Incomplete Recovery because not all available information is used, eg. the transactions recorded in the log files after the point-in-time is not used.

If the point-in-time does not exactly match a backup, then the backup closest but before the specified point-in-time will be restored and then redo logs must be applied up until the point-in-time, this means that normally redo logs will be needed unless you choose a SCN matching one of your backups.

(DBPITR cannot be used in case of media corruption, eg. if a data file or the control file is corrupted in which case you will need Media Recovery).

  • DBPITR using RMAN

    1. Open a shell.
    2. shell> rman target / : logon to RMAN using host credentials.
    3. Set the database in mounted but closed state :
      1. sql> shutdown immediate; : shutdown the database
      2. sql> startup mount; : mount the database but without opening it (Oracle will read the datafiles location from controlfile but not load them).
    4. List some info :
      • rman> list backupset; : lists backup sets (if backup type is Backup Set) - this list includes the SCN of each datafile for each backupset
      • rman> list copy; : lists image copies (if backup type is Image Copy)
      • rman> list backup; : lists backup sets, image copies and proxy copies
      • rman> list incarnation; : lists incarnations. If the Point-In-Time lays in an older incarnation, it is necessary to get SCN from that incarnation.
    5. Restore & Recover - specify Point-In-Time using either SCN or a Time (the Time will actually internally be translated to the nearest SCN)
      • rman> run {set until scn SCN_NUMBER; restore database; recover database;} : using a SCN to specify Point-In-Time
      • rman> run {set until time 'TIME'; restore database; recover database;} : using time to specify Point-In-Time.
        Example : rman> run {set until time '2011-06-03:14:00:00'; restore database; recover database;}
    6. rman> alter database open resetlogs; : this will open the database and start a new incarnation
    7. rman> quit : you are finished.
    8. If it turned out that you recovered to the wrong Point-In-Time and you need to try again, then you will need to change the database into the former incarnation.
  • DBPITR using Enterprise Manager

    1. Log on to Enterprise Manager as 'sys as sysdba'.
    2. On the Availability tab click on the "Perform Recovery" link.
    3. On the "Perform Recovery" page you keep the defaults and click on the "Recovery" button. (If your Host Credentials are not auto written, then you need to write them).
    4. Confirm that you want to shutdown and startup in mounted state.
    5. Wait at least a minute for Oracle to shutdown and startup the database in mounted state and then click the "Refresh" button.
    6. The database is now mounted (but not open). Press the "Perform Recovery" link.
    7. You are required to login again supplying your Host Credentials (the user who installed Oracle) and then press the "Continue" button and then wait a little.
    8. After a short while you are presented with the Database Login screen on which you need to logon as sysdba and press the "Login" button.
    9. You are back on the "Perform Recovery" page (as in step 3), however this time the database is mounted and not open. As under step 3 press the "Recover" button.
    10. Step 1 of 5. Choose the "Recover to a prior point-in-time. Note that I here have Flasback enabled and that I set the Date to later than the oldest Flashback Time, which means that I will be able to use Flashback if I so choose (which I do not). Press the "Next" button.
    11. Step 2 of 5. You only see this page if you have Flashback and you have chosen a date/SCN later than oldest Flashback. If you see this page, the default is to use Flashback, however we will instead choose "No, use regular point-in-time recovery", which will force the use of backup files. Press the "Next" button.
    12. Step 3 of 5. Accept the default that you will restore backup files to default location and press the "Next" button.
    13. Step 4 of 5 - this is a restore operation and therefore the wizard will skip step 4 schedule.
    14. Step 5 of 5. Review and press the "Submit" button.

Oracle Restore Restore Oracle - Import

The Oracle export (exp) & import (imp) utilities is a nice pair for fast & flexible backup & restore of an Oracle database.

Here we will use the Oracle import (imp) utility to import data from an export dump file (see Oracle Backup - Export for how to create an export dump file).

  1. shell> imp : will start an interactive prompt to guide you through the import process.
  2. here I login using the rasmus account, however it may often be more applicable to login using sys as sysdba.
  3. I want to import all objects.
  4. specify location of the exported dump file to import from.
  5. I don't know what is good, so I choose default buffer size.
  6. important - if you choose no (default) and you try to import a table that already exists then you will not only failing on creating the table, you will also not import data from that table (which of course gives good meaning in case you accidentally deleted a table and you don't want to overwrite your existing data from your dump backup).
  7. better import permissions as well.
  8. yes, I also want to import the data not only the structures.
  9. choose whether to import the entire file or to select objects by user - here I choose to select by user *.
  10. rasmus is the name of the user for which I want to select objects.
  11. here I press enter without writing any table names, that will give me all tables for the selected user.
  12. the export file contained only one table (tbl1) with only one row in for the rasmus user.
  13. that went well.

*Then choosing whether to make a full import or a user based import there are several important differences :

  • Full import will create all users in dump file not existing in target database.
  • Full import will try to create tablespaces found in dump file but will fail if the datafile path(s) does not exist on the exact same location on the target host machine as they did on the source host machine. If failing then mapping of objects and tablespaces will not be the same in the target as in the source database.
  • User based import will fail if the users selected does not already exist in the target database.

Sometimes the exported data may be in another charset than the target database. The dump file can still be imported but the data may have been destroyed. In such a case it is necessary to change the charset of the database which is easy if you can use a strict superset of the existing target database charset, but difficult if you cannot.

Oracle Restore Restore Oracle - earlier incarnations

Recover to an earlier incarnation :

  1. Open a shell.
  2. shell> rman target / : logon to RMAN using host credentials.
  3. Set the database in mounted but not open stage (reset database to incarnation command on an open database will result in error ORA-19910: can not change recovery target incarnation in control file).
    1. rman> shutdown immediate; :
    2. rman> startup mount; :
  4. rman reset database to incarnation INCARNATION; :
    Example rman> reset database to incarnation 2; : will reset the database to incarnation number 2.

Oracle Restore Restore Oracle controlfile

Under construction.

Oracle Restore Restore Oracle Express (XE) - Windows

There are 3 scenarios:
  1. You have overwritten data in the database that should not have been overwritten
  2. Your database files have been corrupted but your flash_recovery_area folder is intact
  3. Your database files have been corrupted and your flash_recovery_area folder is not valid
  1. Log in to the Oracle Database Express host computer as a user who is a member of the ORA_DBA group (then Oracle Express was installed, the installer created a ORA_DBA user group and added the current user to that group)
  2. Copy the flash_recovery_area files from your backup to any folder on the host machine. If you copy your backup files directly into the Oracle Express installation flash_recovery_area, then you should first stop the database and then purge that folder and then copy your backup files)
  3. Start restore script (from the windows menu Start >> All Programs >> Oracle Database 10g Express Edition >> Restore Database)
    At the resulting prompt:
    1. Confirm that you want to run
    2. Specify the path to the flash_recovery_area folder that you copied from your backup (if you use the host flash_recovery_area folder, the path is: C:\oraclexe\app\oracle\flash_recovery_area)
  4. Start the database (from the windows menu Start >> All Programs >> Oracle Database 10g Express Edition >> Start Database)

Oracle Restore Restore Oracle Express (XE) - Linux

Under Construction

Appendix : Oracle restore related concepts

  • Restore : restoring datafiles is retrievning them from backups as needed for a recovery operation.
  • Recover : media recovery is the application of changes from redo logs and incremental backups to a restored datafile to bring the datafile forward to a desired SCN or point in time.
  • SCN : System Change Number : Everytime a transaction is committed, Oracle records a new SCN. Then Oracle starts up, Oracle check SCN in all datafile headers and in the control file and if they are all the same then the database is consistent. If they are not the same, the database is in need of recovery.
  • Flasback Recovery : Used for point-in-time restore. If you want to revert the datafiles to their contents at a past time, eg. because the datafiles have unwanted changes.
  • Media Recovery : Used for time-of-failure restore. If you encounter media errors, eg. datafiles are destroyed. The idea is to recover the database to the state at time-of-failure and so media recovery is not suitable for restoring to point-of-time
  • DataBase Point-In-Time recovery (DBPITR) : Sometimes called Incomplete Recovery because it does not use all of the available redo logs or completely recover all changes, instead DBPITR restores the database from backups prior to the target time for recovery, then uses incremental backups and redo logs to roll the database forward to the target time.
  • Incarnation : Each time a DBPITR recovery is performed with resetlogs, the database starts a new incarnation, eg. if at SCN 2000 the database is set back to SCN 1000, the database will start a new incartion from SCN 1000 giving the next transaction SCN 1001, therefore there may be many identical SCN available, however SCN is unique inside an incarnation (here is a good description).
  • Databases :
    • Flashback database :
    • Standby database :
    • Primary database :
    • Recovery Catalog database :

Appendix : How to find your DBID

Your DBID is relevant in different restore situations.
  • Using RMAN:
    1. Open a shell.
    2. shell> rman target / : logon to RMAN using host credentials.
    3. RMAN display among other things DBID.
  • Using backup files - this will only work if you have Image Copy backup (which is not default) :
    1. Open a file explorer and navigate to your backup folder.
    2. DBID is written into the name of the controlfile backup.
    3. If you use Image Copy backup, then DBID is also written into the name of the datafiles backup.
  • Using SQL*Plus - can only do if the database is open
    1. Open a shell.
    2. shell> sqlplus / as sysdba : logon to SQL*Plus as sys as sysdba using host credentials.
    3. sql> select dbid, name from v$database; : shows DBID from current database.

Appendix : I have no backup

This is a bad situation.

I know of the following possibilities :

  • If you have money : Call Oracle, they may be able to help you extract data from an offline database using a utility called DUL (Disk UnLoad).
  • If you have no money : Try DUDE (Database Unloading by Data Extraction) which like DUL is a tool designed to bypass Oracles database engine extracting data when the database is down and/or corrupted, DUDE Homepage.


Other Webmodelling logo webmodelling.com web sites: Menulab logo menulab.com | Jewelpit logo jewelpit.com | DropShip List logo dropshiplist.biz