Using RMAN and RMAN recovery catalog.
These steps will restore and recovery the database after losing some or all of the critical database components. i.e. controlfiles, spfile, system.dbf, etc…
Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 - 64bit Production
PL/SQL Release 22.214.171.124.0 - Production
CORE 126.96.36.199.0 Production
TNS for Linux: Version 188.8.131.52.0 - Production
NLSRTL Version 184.108.40.206.0 – Production
Once you have determined you need to restore the database login to sqlplus and issue the shutdown abort command.
SQL> shutdown abort
Restore the server files (Oracle Base and Home directories) from a backup if necessary.
Restore the Oracle directories.
Connect to rman and rman catalog.
# rman target / catalog rcvcat_login/rcvcat_passwd@rcvcat;
Recovery Manager: Release 220.127.116.11.0 - Production on Tue Jul 27 15:46:20 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: HRPROD (not mounted)
connected to recovery catalog database
set the DBID
see setting the DBID thread
RMAN> SET DBID 3800958841
executing command: SET DBID
database name is "HRPROD" and DBID is 3800958841
Startup the database in nomount
RMAN> startup nomount;
Oracle instance started
In order to restore the spfile the current spfile cannot be intact. You cannot restore over a current spfile. Either rename it or delete it if it currently exists before executing the 'restore spfile from autobackup' command.
Restore the SPFILE
RMAN> restore spfile from autobackup;
Starting restore at ...
Shutdown the database.
RMAN> shutdown immediate;
Oracle instance shut down
Restart the instance with the restored server parameter file.
RMAN> startup force nomount;
Determine the last archive log sequence backed up or available on disk.
Since I am using a RAID 5 configuration, if I lose more than one disk at a time I will lose everything, including all archive logs files on disk. I have an output from the last backup in my email that I can reference, but this information can also be obtained from your RMAN catalog.
RMAN> list backup of archivelog from scn=1;
List of Archived Logs in backup set 205213164
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 34 4221565 20-JUL-10 4421777 22-JUL-10
Some other useful RMAN commands are the following as well:
RMAN> list backup;
RMAN> list backup of database completed after '19-JUL-10';
Now that you know the Archive Log Seq you want to restore to, you can issue the following commands in the RUN block.
# Restore autobackup of the control file.
# This example assumes that you have
# accepted the default format
# for the autobackup name.
RESTORE CONTROLFILE FROM AUTOBACKUP;
# The set until command is used in case
# the database structure has changed in
# the most recent backups, and you want to
# recover to that point-in-time.
# In this way RMAN restores the database
# to the same structure that the
# database had at the specified time.
ALTER DATABASE MOUNT;
SET UNTIL SEQUENCE 34 THREAD 1;
ALTER DATABASE OPEN RESETLOGS;
Starting restore at ...
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=98 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=114 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: SID=130 device type=SBT_TAPE
channel ORA_SBT_TAPE_3: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: SID=146 device type=SBT_TAPE
channel ORA_SBT_TAPE_4: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK
Finished restore at ...
Starting recover at ...
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
using channel ORA_DISK_1
starting media recovery
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Hopefully all your restores are tests and if you are ever faced with a production restore it all goes well.
A DBA can do everything else in the world wrong -- EVERYTHING. We can fix that, we can resolve those issues. But if they mess up backup and recovery – forget about it. You are hosed.
~ Unknown author and DBA
Oracle runInstaller fails on Linux, Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set.
How to get Oracle's runInstaller to run on Linux or Unix environment using from Windows using Cygwin as an xterm emulator. Download an...
Whether you are using audit_trail = OS or DB there will be audit files accumulating in the audit_file_dest. Much more if audit_trail paramet...
More on Mater ialized Views Materialized Views What is a Materialized View? Creating a Complete Refreshable Materialized View Creati...
Oracle duplication on 12c. RMAN-04006: error from auxiliary database. RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon deniedOracle 18.104.22.168.0 RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested i...