Data Protector acts as a media management software for the Oracle system, therefore RMAN can be used for a restore.
This section only describes examples of how you can perform a restore.
The examples provided do not apply to all situations where a restore is needed.
See the Oracle Recovery Manager User’s Guide and References for detailed information on how to perform:
• Restore and recovery of the database, tablespace, control file, and datafile.
• Duplication of a database.
The following examples of restore are given:
• “Example of Full Database Restore and Recovery” on page 97
• “Example of Point-in-Time Restore” on page 98
• “Example of Tablespace Restore and Recovery” on page 100
• “Example of Datafile Restore and Recovery” on page 102
• “Example of Archive Log Restore” on page 105
The restore and recovery procedure of Oracle control files is a very delicate operation, which depends on whether you are using the recovery catalog or control file as a central repository and the version of the Oracle database you are using. For detailed steps on how to perform the restore of control files, see the Recovery Manager User’s Guide and References.
Preparing the Oracle Database for Restore
The restore of an Oracle database can be performed when the database is in mount mode. However, when you are performing the restore of tablespaces or datafiles, only a part of the Oracle database can be put offline.
Prerequisites The following requirements must be met before you start a restore of an Oracle database:
• If you use the recovery catalog database, make sure that the recovery catalog database is open. If the recovery catalog database cannot be brought online, you will probably need to restore the recovery catalog database. See “Restoring an Oracle Database” on page 70 for details on how to restore the recovery catalog database.
• Control files must be available. If the control files are not available, you must restore them. See the Oracle Recovery Manager User’s Guide and References for more details.
If you have to perform a restore of the recovery catalog databaseor control files, you must perform this restore first. Only then can you perform a restore of other parts of the Oracle database.
When you are sure that the recovery catalog databaseor control files are in place, start the recovery catalog database.
• Make sure that the following environment variables are set:
✓ ORACLE_BASE
✓ ORACLE_HOME
✓ ORACLE_TERM
✓ DB NAME
✓ PATH
✓ NLS_LANG
✓ NLS_DATE_FORMAT
Windows Example ORACLE_BASE=<Oracle_home>
ORACLE_HOME=<Oracle_home>\product\10.1.0 ORACLE_TERM=hp
DB_NAME=PROD
PATH=$PATH:<Oracle_home>\product\10.1.0\bin NLS_LANG=american
NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'
UNIX Example ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/10.1.0 ORACLE_TERM=hp
DB_NAME=PROD
PATH=$PATH:/opt/oracle/product/10.1.0/bin NLS_LANG=american
NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'
OpenVMS Example
ORACLE_HOME=DKA400:[ORACLE9I]
ORACLE_TERM=hp DB_NAME=PROD
• Check that the /etc/oratab file has the following line:
Windows: PROD:<Oracle_home>\product\10.1.0:N UNIX: PROD:/opt/oracle/product/10.1.0:N OpenVMS:
— Oracle 9i:
<oracle_home>/oratab TEST:/DKA400/ORACLE9I:N CAT:/DKA400/ORACLE9I:N
— Oracle 8i:
<oracle_home>/rdbms/ORA_RDBMS_SIDS.DAT VMS1 TEST TEST
VMS1 CAT CAT
The last letter determines whether the database will automatically start upon bootup (Y) or not (N).
Connection Strings Used in the Examples
In the examples below, the following connection strings are used:
• Target connection string for target database:
sys/manager@PROD
where sys is the username, manager is the password and PROD is a net service name.
• Recovery catalog connection string for recovery catalog database:
rman/rman@CATAL
where rman is the username and password and CATAL is a net service name.
Example of Full Database Restore and Recovery
To perform a full database restore and recovery, you also need to restore and apply all the archive logs. To perform a full database restore and recovery:
1. Log in to the Oracle RMAN:
If you use the recovery catalog database, run:
Oracle 9i/10g:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD catalog rman/rman@CATAL
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD catalog rman/rman@CATAL
• On OpenVMS: rman target sys/manager@PROD sys/manager@PROD catalog rman/rman@CAT Oracle 8i:
Use rcvcat instead of catalog in the above syntax.
If you do not use the recovery catalog database, run:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD nocatalog
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD nocatalog
• On OpenVMS: rman target sys/manager@PROD nocatalog 2. Start the full database restore and recovery:
run{
allocate channel 'dev1' type 'sbt_tape' parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=<DB_NAME>)';
restore database;
recover database;
sql 'alter database open';
release channel 'dev1';
}
You can also save the script into a file and perform a full database restore using the saved files. The procedure in such cases is as follows:
1. Create a file restore_database in the /var/opt/omni/tmp (UNIX systems) or <Data_Protector_home>\tmp directory.
2. Start the full database restore:
If you use the recovery catalog database, run:
Oracle 9i/10g:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD catalog rman/rman@CATAL
cmdfile=<Data_Protector_home>\tmp\restore_datafile
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD catalog rman/rman@CATAL
cmdfile=/var/opt/omni/tmp/restore_datafile Oracle 8i:
Use rcvcat instead of catalog in the above syntax.
If you do not use the recovery catalog database, run:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD nocatalog
cmdfile=<Data_Protector_home>\tmp\restore_datafile
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD nocatalog cmdfile=/var/opt/omni/tmp/restore_datafile
Example of Point-in-Time Restore
To perform a point-in-time restore, you also need to restore and apply the archive logs to the specified point in time. To perform a point-in-time database restore and recovery:
1. Log in to the Oracle RMAN:
If you use the recovery catalog database, run:
Oracle 9i/10g:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD catalog rman/rman@CATAL
• On OpenVMS: rman target sys/manager@PROD sys/manager@PROD catalog rman/rman@CAT Oracle 8i:
Use rcvcat instead of catalog in the above syntax.
If you do not use the recovery catalog, run:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD nocatalog
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD nocatalog
• On OpenVMS: rman target sys/manager@PROD nocatalog 2. Start the point-in-time restore:
run{
allocate channel 'dev1' type 'sbt_tape' parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=<DB_NAME>)';
set until time 'Mar 14 2004 11:40:00';
restore database;
recover database;
sql 'alter database open';
release channel 'dev1';
}
3. After you have performed a point-in-time restore, reset the database in the Recovery Catalog.
You can also save the script into a file and perform a point-in-time restore using the saved files:
1. Create a file restore_PIT in the /var/opt/omni/tmp or
<Data_Protector_home>\tmp directory.
2. Start the point-in-time restore:
If you use the recovery catalog database, run:
Oracle 9i/10g:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD catalog rman/rman@CATAL cmdfile=<Data_Protector_home>\tmp\restore_PIT
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD catalog rman/rman@CATAL
cmdfile=/var/opt/omni/tmp/restore_PIT Oracle 8i:
Use rcvcat instead of catalog in the above syntax.
If you do not use the recovery catalog, run:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD nocatalog
cmdfile=<Data_Protector_home>\tmp\restore_PIT
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD nocatalog cmdfile=/var/opt/omni/tmp/restore_PIT
Example of Tablespace Restore and Recovery
If a table is missing or corrupted, you need to perform a restore and recovery of the entire tablespace. To restore a tablespace, you may take only a part of the database offline, so that the database does not have to be in the mount mode. You can use either a recovery catalog database or control files to perform a tablespace restore and recovery. Follow the steps below:
1. Log in to the Oracle RMAN:
If you use the recovery catalog database, run:
Oracle 9i/10g:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD catalog rman/rman@CATAL
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD catalog rman/rman@CATAL
• On OpenVMS: rman target sys/manager@PROD sys/manager@PROD catalog rman/rman@CAT Oracle 8i:
Use rcvcat instead of catalog in the above syntax.
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD nocatalog
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD nocatalog
• On OpenVMS: rman target sys/manager@PROD nocatalog 2. Start the tablespace restore and recovery.
• If the database is in the open state, the script to restore and recover the tablespace should have the following format:
run{
allocate channel <dev1> type 'sbt_tape' parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=<DB_NAME>)';
sql 'alter tablespace TEMP offline immediate';
restore tablespace TEMP;
recover tablespace TEMP;
sql 'alter tablespace TEMP online';
release channel dev1;
}
• If the database is in the mount state, the script to restore and recover the tablespace should have the following format:
run{
allocate channel <dev1> type 'sbt_tape' parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=<DB_NAME>)';
restore tablespace 'TEMP';
recover tablespace 'TEMP';
release channel <dev1>;
}
You can also save the script into a file and perform a tablespace restore using the saved files:
1. Create a file restore_TAB in the /var/opt/omni/tmp (UNIX systems) or <Data_Protector_home>\tmp (Windows systems) directory.
2. Start the tablespace restore.
If you use the recovery catalog database, run:
Oracle 9i/10g:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD catalog rman/rman@CATAL cmdfile=<Data_Protector_home>\tmp\restore_TAB
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD catalog rman/rman@CATAL
cmdfile=/var/opt/omni/tmp/restore_TAB Oracle 8i:
Use rcvcat instead of catalog in the above syntax.
If you do not use the recovery catalog, run:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD nocatalog
cmdfile=<Data_Protector_home>\tmp\restore_TAB
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD nocatalog cmdfile=/var/opt/omni/tmp/restore_TAB
Example of Datafile Restore and Recovery
To restore and recover a datafile, you may take only a part of the database offline.
To restore and recover a datafile:
1. Log in to the Oracle RMAN.
If you use the recovery catalog database, run:
Oracle 9i/10g:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD catalog rman/rman@CATAL
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD catalog rman/rman@CATAL
• On OpenVMS: rman target sys/manager@PROD sys/manager@PROD catalog rman/rman@CAT
Use rcvcat instead of catalog in the above syntax.
If you do not use the recovery catalog database, run:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD nocatalog
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD nocatalog
• On OpenVMS: rman target sys/manager@PROD nocatalog 2. Start the datafile restore and recovery:
• If the database is in an open state, the script to restore the datafile should have the following format:
UNIX run{
allocate channel dev1 type 'sbt_tape' parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=<DB_NAME>)';
sql "alter database datafile
''/opt/oracle/data/oradata/DATA/temp01.dbf'' offline";
restore datafile
'/opt/oracle/data/oradata/DATA/temp01.dbf';
recover datafile
'/opt/oracle/data/oradata/DATA/temp01.dbf';
sql "alter database datafile
'/opt/oracle/data/oradata/DATA/temp01.dbf' online";
release channel dev1;
}
Windows run{
allocate channel dev1 type 'sbt_tape' parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=<DB_NAME>)';
sql "alter database datafile
''C:\oracle\data\oradata\DATA\temp01.dbf'' offline";
restore datafile
'C:\oracle\data\oradata\DATA\temp01.dbf';
recover datafile
'C:\oracle\data\oradata\DATA\temp01.dbf';
sql "alter database datafile
''C:\oracle\data\oradata\DATA\temp01.dbf'' online";
release channel dev1;
}
• If the database is in a mount state, the script to restore and recover the datafile should have the following format:
UNIX run{
allocate channel dev1 type 'sbt_tape' parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=<DB_NAME>)';
restore datafile
'/opt/oracle/data/oradata/DATA/temp01.dbf';
recover datafile
'/opt/oracle/data/oradata/DATA/temp01.dbf';
release channel dev1;
}
Windows run{
allocate channel dev1 type 'sbt_tape' parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=<DB_NAME>)';
restore datafile
'<Oracle_home>\data\oradata\DATA\temp01.dbf';
recover datafile
'<Oracle_home>\data\oradata\DATA\temp01.dbf';
release channel dev1;
}
You can also save the script into a file and perform a datafile restore using the saved files:
1. Create a file restore_dbf the /var/opt/omni/tmp or
<Data_Protector_home>\tmp (Windows systems) directory.
2. Start the datafile restore:
If you use the recovery catalog database, run:
Oracle 9i/10g:
• On Windows: <ORACLE_HOME>/bin/rman target sys/manager@PROD catalog rman/rman@CATAL cmdfile=/var/opt/omni/tmp/restore_dbf
• On UNIX: <ORACLE_HOME>\bin\rman target sys/manager@PROD catalog rman/rman@CATAL
cmdfile=<Data_Protector_home>\tmp\restore_dbf Oracle 8i:
Use rcvcat instead of catalog in the above syntax.
If you do not use the recovery catalog database, run:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD nocatalog
cmdfile=<Data_Protector_home>\tmp\restore_dbf
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD nocatalog cmdfile=/var/opt/omni/tmp/restore_dbf
Example of Archive Log Restore To restore an archive log:
1. Login to the Oracle RMAN:
If you use the recovery catalog database, run:
Oracle 9i/10g:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD catalog rman/rman@CATAL
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD catalog rman/rman@CATAL
• On OpenVMS: rman target sys/manager@PROD sys/manager@PROD catalog rman/rman@CAT Oracle 8i:
Use rcvcat instead of catalog in the above syntax.
If you do not use the recovery catalog database, run:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD nocatalog
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD nocatalog
• On OpenVMS: rman target sys/manager@PROD nocatalog 2. Start the archive log restore:
run{
allocate channel dev1 type 'sbt_tape' parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=<DB_NAME>)';
restore archivelog all;
release channel dev1;
}
You can also save the script into a file and perform an archive log restore using the saved files:
1. Create a file restore_arch in the /var/opt/omni/tmp (UNIX systems) or <Data_Protector_home>\tmp (Windows systems) directory.
2. Start the archive log restore:
If you use the recovery catalog database, run:
Oracle 9i/10g:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD catalog rman/rman@CATAL
cmdfile=<Data_Protector_home>\tmp\restore_arch
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD catalog rman/rman@CATAL
cmdfile=/var/opt/omni/tmp/restore_arch Oracle 8i:
Use rcvcat instead of catalog in the above syntax.
If you do not use the recovery catalog database, run:
• On Windows: <ORACLE_HOME>\bin\rman target sys/manager@PROD nocatalog
cmdfile=<Data_Protector_home>\tmp\restore_arch
• On UNIX: <ORACLE_HOME>/bin/rman target sys/manager@PROD