Nethence NSBH Laboratory Webmail Your IP BBDock
New Software Burns in Hell print | donate | html/css | terms of use
Home | Unix | Windows | Oracle | Obsolete | Hardware | Mechanics | Scripts | Configs | Private

Oracle RMAN -- Restoring a database
 
 
Introduction
The standard procedure to restore the database, when using the same db name on the same server,
rman target sys/PASSWORD@dbname catalog cat/PASSWORD@rcat
startup mount;
restore database;
recover database;
alter database open;
exit
but here we're proceeding from scratch. The procedure below can also be applied on another server. Of course we're assuming your backup strategy includes everything : datafiles, controlfile, spfile and archivelogs. Check with,
list backup summary;
list backup of database;
list backup of controlfile;
list backup of archivelog all;
 
 
Restore everything from scratch
Prepare the dirs,
export ORACLE_SID=dbname
cd $ORACLE_BASE/admin
mkdir $ORACLE_SID
cd $ORACLE_SID
mkdir adump bdump cdump dpdump pfile scripts udump
cd /u02/oradata
mkdir $ORACLE_SID
chmod 750 $ORACLE_SID
cd /usr/local/oracle
mkdir $ORACLE_SID
chmod 750 $ORACLE_SID
 
Add dbname to oratab,
vi /etc/oratab
 
Spfile
Restore the spfile first and restart nomount with it,
. oraenv
rman target / catalog cat/PASSWORD@rcat
startup nomount
note. expecting a ORA-01078 at first startup try but rman starts the instance anyway
restore spfile;
shutdown immediate
startup nomount
 
Database
restore database;
Note. other possible syntaxes,
#restore archivelog from tag=TAG20100205T162010;
#restore archivelog scn between 214483 and 214488;
 
Control file
Then the control file,
restore controlfile;
alter database mount;
Note. other possible syntaxes,
#restore controlfile from '/u03/backup/ctl_CHECK1_709394097_6_1';
 
Archivelogs
Eventually copy the latest archivelogs, namely those written since the backup, to the recovery area.
list backup of archivelog all;
restore archivelog sequence 27;
restore archivelog sequence 28;
restore archivelog sequence 29;
#restore archivelog from logseq=568 until logseq=582;
#restore archivelog all;
 
Recover
Now the critical step. Check your incarnation,
list incarnation of database;
check what last SCN and TAG you can get from the backups,
list backup;
 
Method 1/ recover from last tag,
recover database from tag="..." validate;
recover database from tag="...";
alter database open XXXXXX
list incarnation of database;
 
Method 2/ quit rman and proceed with sqlplus,
. oraenv
sqlplus / as sysdba
recover database until cancel using backup controlfile;
CANCEL
alter database open;
exit
rman target / catalog cat/PASSWORD@rcat
list incarnation of database;
 
Method 3/ skip the archive logs,
recover database noredo;
alter database open resetlogs;
list incarnation of database;
 
Note. other possible syntaxes,
#list incarnation of database dbname;
Note. to revert to a previous incarnation,
#reset database to incarnation 1;
 
Add dbname to the listener and tnsnames,
vi $ORACLE_HOME/network/admin/listener.ora
vi $ORACLE_HOME/network/admin/tnsnames.ora
 
 
Renaming the database in the process
Pfile
Changing the dbname and path,
sqlplus / as sysdba
create pfile from spfile;
exit
cd $ORACLE_HOME/dbs
  rm -f spfiledbname3.ora
vi initdbname3.ora
then,
remove the first lines
:%s/dbname/dbname3/g
 
Database
Look for datafile IDs,
list backup of database;
add the rman statements to relocate the datafiles,
run {
set newname for datafile 1 to '/u02/oradata/dbname3/system01.dbf';
set newname for datafile 2 to '/u02/oradata/dbname3/undotbs01.dbf';
set newname for datafile 3 to '/u02/oradata/dbname3/sysaux01.dbf';
set newname for datafile 4 to '/u02/oradata/dbname3/users01.dbf';
set newname for datafile 5 to '/u02/oradata/dbname3/tsname.dbf';
restore database;
}
 
Control file
On srv1,
sqlplus / as sysdba
alter database backup controlfile to trace;
exit
cd $ORACLE_BASE/admin/dbname/udump
ls -ltr
scp dbname_ora_12161.trc srv2:~/
note. controlfile needs to be mounted to operate a backup to trace
 
On srv2,
cp dbname_ora_12161.trc dbname_ora_12161.trc.dist
vi dbname_ora_12161.trc
set#2
sed '
s/DBNAME/DBNAME3/g;
s/dbname/dbname3/g;
s/REUSE DATABASE/set database/g;
' dbname_ora_12161.trc > trace.sql
 
Recover
Now that the datafiles are there, copy/paste from the tracefile,
cd ~/
less trace.sql
and on some other windows,
sqlplus / as sysdba
create ...;
note. mounts the db
recover database using backup controlfile until cancel;
alter database enable block change tracking
using file '/u02/oradata/tracking/dbname3.track';
note. no "reuse"
alter database open resetlogs;
alter tablespace temp add tempfile...;
alter tablespace tsnametmp add tempfile...;
 

Last update: Apr 09, 2010
Copyright © 2007-2014 Pierre-Philipp Braun