|
Preview DRM-free music
Play clips of the most popular
DRM-Free music in MP3 format
Legal Music .com
Get information on your favorite TV Shows at 
Clarify the meaning of the acronyms on your web site.
Acronym Dictionary
Acronym Finder
Social Networking Web Sites
Register your own domain name at A to Z Domains.
or buy domains at Domain Names For Sale .net
Printer-friendly PDF* format:
Recovery Scenarios
This Section
|
Recovery Scenarios
This can be used to recover the data in individual tables,
possibly after a table has been dropped, for example.
The tablespace containing the table is restored to an auxiliary instance,
after which the data can be manually copied back to the original instance.
Note: Although the tablespace point in time recovery (TSPITR)
initially restores the tablespace to an auxiliary instance, it also recovers
the tablespace by transporting it back to the original instance.
See Oracle Metalink Document #228257.1
RMAN "Duplicate Database" Feature in Oracle 9i and Oracle 10g.
- Log onto the system using the ID for starting the Oracle database.
- Shut down the auxiliary instance:
export ORACLE_SID=auxsvc
sqlplus 'username/password as sysdba'
show parameter db_name
shutdown immediate
- The control file of the auxilary instance will be replaced.
If the instance will need to be restored back to its original state then
make a copy of the existing control file before starting the restore.
- Start up the instance in
nomount mode:
startup nomount
exit
- Generate a list of tablespaces to be skipped:
sqlplus username/password@tspsid
set pagesize 50000
select tablespace_name || ',' from dba_tablespaces where contents not in ('TEMPORARY','UNDO') and tablespace_name not in ('SYSTEM','tspname',...) order by 1
where tspsid is the instance containing the tablespace to be restored.
Include a list of the tablespaces to be restored in the second in clause.
If there are indexes in separate tablespaces, they should also be restored; otherwise
the primary key constraints and/or other indexes on the tables in the tablespaces
that are restored will need to be dropped in order to access the data in the tables.
- Get the file IDs and file names of the data files to be restored:
select file_id, file_name from dba_data_files where tablespace_name in ('tspname',...);
Change the file name to avoid overwriting any existing files.
- Determine the number of groups of redo logs and the number of copies of each.
- Start RMAN, connecting to the target, catalog and auxiliary instances.
rman target sys/password@tspsid catalog rmanid/rmanpass@catsid auxiliary sys/auxpass[@auxsid]
- Resyncronize the catalog if necessary, to include any new archive logs:
list archivelog all;
resync catalog;
- Execute the following RMAN command to restore the tablespace to the auxiliary instance:
run {
allocate auxiliary channel 'tape1' device type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo_tspsid.opt)';
set until time "to_date('mm/dd/yyyy hh:mm:ss','mm/dd/yyyy hh24:mi:ss')";
set newname for datafile 1 TO '/u##/oradata/auxsid/systspsid01.dbf';
set newname for datafile 2 TO '/u##/oradata/auxsid/undotspsid01.dbf';
set newname for datafile fileid TO '/u##/oradata/auxsid/tspsid
filename';
duplicate target database to auxsid
skip tablespace
list of tablespaces to be skipped
logfile
group 1 ('/u01/oradata/auxsid/redotspsid01a.log',
'/u02/oradata/auxsid/redotspsid01b.log') size 10M reuse,
group 2 ('/u01/oradata/auxsid/redotspsid02a.log',
'/u02/oradata/auxsid/redotspsid02b.log') size 10M reuse,
group 3 ('/u01/oradata/auxsid/redotspsid03a.log',
'/u02/oradata/auxsid/redotspsid03b.log') size 10M reuse;
}
- Wait for the restore and recovery to complete, then exit RMAN:
Starting restore at ...
channel tape1: starting datafile backupset restore
channel tape1: restore complete
Finished restore at ...
Starting recover at ...
starting media recovery
media recovery complete
Finished recover at ...
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
exit
Recovery Manager complete.
- If the restore fails after media recovery, it may be possible
to continue without having to restore again. For example:
sql statement: drop tablespace XDB including contents
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/15/2007 22:24:22
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 11/15/2007 22:24:22
RMAN-11003: failure during parse/execution of SQL statement: drop tablespace XDB including contents
ORA-29857: domain indexes and/or secondary objects exist in the tablespace
export ORACLE_SID=auxsvc
sqlplus 'username/password as sysdba'
set pagesize 50000
select name, open_mode from v$database;
column name format a60
select status, name, enabled from v$datafile;
- If any required indexes were not restored, an error will occur when attempting to
access the data in the restored table. Those indexes will need to be dropped.
ERROR at line 1:
ORA-00376: file ## cannot be read at this time
ORA-01111: name for data file ## is unknown - rename to correct file
ORA-01110: data file ##: '/u01/app/oracle/product/version/dbs/MISSING000##'
alter table owner.table_name drop constraint constraint_name;
- To keep the restored data in the auxiliary instance,
reset the database to indicate that a RESETLOGS has been done:
export ORACLE_SID=auxsvc
rman target sys/password[@auxsid] catalog rmanid/rmanpass@catsid
reset database;
To return to the original data in the auxiliary instance, shut down the instance:
sqlplus 'username/password as sysdba'
show parameter db_name
shutdown immediate
exit
Rename the control files back, then start up the instance in nomount mode:
sqlplus 'username/password as sysdba'
startup nomount
exit
Reset the database incarnation to the one that matches the restored control files:
rman target sys/password[@auxsid] catalog rmanid/rmanpass@catsid
list incarnation of database auxsid;
reset database to incarnation inckey
exit
back to top
Last updated Monday November 19, 2007
|
|
