Restoring Database to a new location using rman

Scenario

All database files got lost (controlfiles,redo,datafiles), server parameter files is also lost (spfile).

The database has to be restored meeting the following conditions

1. After restoring all database files(controlfiles, redolog, datafiles) should have new location
2. Spfile will be residing at  same location.

Environment

Oracle database 11.2.0.1.0
OEL 5.7


Before Disaster

Database files (datafiles,controlfiles,redo) /u01/app/oracle/oradata/orcl
spfil $ORACLE_HOME/dbs
archive log location /u01/oracle/arch
backup controlfile location -/u01/app/oracle/
controlfile autobackup is turned on

After recovery

spfile same location ($ORACLE_HOME/dbs)
databasfiles and controlfiles /u01/oracle/oradata/orcl
archive log location /u01/oracle/arch

There is a controlfile autobackup and database backup taken sometime before disaster.


Restoring and recovering

• Create new directory structures

          cd /u01/oracle

          mkdir -p oradata/orcl

• start rman

          cd /u01/oracle/bkp

          rman target / | tee -a rman_test_log.log

• set dbid

          set dbid=1334549344

• startup database in nomount mode

          startup nomount;

• set controlfile autobackup format (the autoback was not at its default location)

          set controlfile autobackup format for device type disk to '/u01/app/oracle/%F'

• restore spfile

          restore spfile from autobackup;

• shutdown and startup in nomount(this will start with newly created spfile)

          startup nomount force ;

• set controlfile new location

          sql "alter system set control_files=''/u01/oracle/oradata/orcl/    control01.dbf'',''/u01/oracle/oradata/orcl/control02.dbf'' scope=spfile" ;

• shutdown and startup in nomount(for controlfile new location to take effect)

          startup nomount force ;

• make sure the controlfile location is set to our new location with sqlplus

          sqlplus / as sysdba

          show parameter controlfile

previous

   control_files string /u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/   oracle/oradata/orcl/control02.ctl

now

 control_files string /u01/oracle/oradata/orcl/control01.dbf, /u01/oracle/ oradata/orcl/control02.dbf

•   restore controlfile (it should be restored in new location)

          restore controlfile from autobackup;

• now mount the database

          alter database mount ;

• run the rman command to rename datafiles,tempfile to newlocation and restore the database

   run

  { 

  set newname for database to '/u01/oracle/oradata/orcl/%b' ;

  restore database ;

  switch datafile all;

  switch tempfile all;

  }

• run sql statements to rename redo logs to new location

          SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/ redo01.log'' TO ''/u01/oracle/oradata/orcl/redo01.log'' ";
          SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/ redo02.log'' TO ''/u01/oracle/oradata/orcl/redo02.log'' ";

          SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo03.log'' TO ''/u01/oracle/oradata/orcl/redo03.log'' ";

• recover database until last sequence

          recover database until sequence 7 ;

• open database with resetlogs

          alter database open resetlogs ;

Rman Session of  Restore and Recovery

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Feb 19 15:03:11 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1334549344)

listing of backup taken when the database was up and running

RMAN> list backup summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
47 B A A DISK 19-FEB-13 1 1 NO TAG20130219T150521
48 B F A DISK 19-FEB-13 1 1 NO TAG20130219T150524
49 B A A DISK 19-FEB-13 1 1 NO TAG20130219T150620
50 B F A DISK 19-FEB-13 1 1 NO TAG20130219T150621

RMAN> list backup;

List of Backup Sets
===================


BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
47 93.13M DISK 00:00:02 19-FEB-13
BP Key: 47 Status: AVAILABLE Compressed: NO Tag: TAG20130219T150521
Piece Name: /u01/oracle/bkp/arc20130219ORCL1mo2cath_1_1

List of Archived Logs in backup set 47
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 3 1668460 19-FEB-13 1671683 19-FEB-13
1 4 1671683 19-FEB-13 1672642 19-FEB-13
1 5 1672642 19-FEB-13 1672835 19-FEB-13

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
48 Full 1.22G DISK 00:00:51 19-FEB-13
BP Key: 48 Status: AVAILABLE Compressed: NO Tag: TAG20130219T150524
Piece Name: /u01/oracle/bkp/20130219ORCL1no2catl_1_1
List of Datafiles in backup set 48
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1672845 19-FEB-13 /u01/app/oracle/oradata/orcl/system01.dbf
2 Full 1672845 19-FEB-13 /u01/app/oracle/oradata/orcl/sysaux01.dbf
3 Full 1672845 19-FEB-13 /u01/app/oracle/oradata/orcl/undotbs01.dbf
4 Full 1672845 19-FEB-13 /u01/app/oracle/oradata/orcl/users01.dbf
5 Full 1672845 19-FEB-13 /u01/app/oracle/oradata/orcl/example01.dbf

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
49 80.50K DISK 00:00:00 19-FEB-13
BP Key: 49 Status: AVAILABLE Compressed: NO Tag: TAG20130219T150620
Piece Name: /u01/oracle/bkp/arc20130219ORCL1oo2cavc_1_1

List of Archived Logs in backup set 49
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 1672835 19-FEB-13 1672872 19-FEB-13

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
50 Full 9.42M DISK 00:00:01 19-FEB-13
BP Key: 50 Status: AVAILABLE Compressed: NO Tag: TAG20130219T150621
Piece Name: /u01/app/oracle/c-1334549344-20130219-02
SPFILE Included: Modification time: 19-FEB-13
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 1672883 Ckp time: 19-FEB-13

RMAN> show all; 
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

RMAN>

Recovery Manager complete.


Restore steps



Recovery Manager: Release 11.2.0.1.0 - Production on Tue Feb 19 15:37:40 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)

RMAN>  startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 159019008 bytes

Fixed Size 1335192 bytes
Variable Size 75497576 bytes
Database Buffers 79691776 bytes
Redo Buffers 2494464 bytes

RMAN>  set dbid=1334549344
executing command: SET DBID

RMAN>  set controlfile autobackup format for device type disk to '/u01/app/oracle/%F'
executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN>  resrote spfile from autobackup;
Starting restore at 19-FEB-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130219
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/oracle/c-1334549344-20130219-02
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/c-1334549344-20130219-02
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 19-FEB-13

RMAN>  startup noumount force ;
Oracle instance started

Total System Global Area 631914496 bytes

Fixed Size 1338364 bytes
Variable Size 482345988 bytes
Database Buffers 142606336 bytes
Redo Buffers 5623808 bytes


RMAN>
sql statement: alter system set control_files=''/u01/oracle/oradata/orcl/control01.dbf'',''/u01/oracle/oradata/orcl/control02.dbf'' scope=spfile

RMAN> startup nomount force;
Oracle instance started

Total System Global Area 631914496 bytes

Fixed Size 1338364 bytes
Variable Size 482345988 bytes
Database Buffers 142606336 bytes
Redo Buffers 5623808 bytes


RMAN> restore controlfile from autobackup;
Starting restore at 19-FEB-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130219
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/oracle/c-1334549344-20130219-02
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/c-1334549344-20130219-02
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/oracle/oradata/orcl/control01.dbf
output file name=/u01/oracle/oradata/orcl/control02.dbf
Finished restore at 19-FEB-13

RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

RMAN>run
{
set newname for database to '/u01/oracle/oradata/orcl/%b' ;
restore database;
switch datafile all;
switch tempfile all;
}

executing command: SET NEWNAME

Starting restore at 19-FEB-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/bkp/20130219ORCL1no2catl_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/bkp/20130219ORCL1no2catl_1_1 tag=TAG20130219T150524
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 19-FEB-13

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=807813386 file name=/u01/oracle/oradata/orcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=807813386 file name=/u01/oracle/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=807813386 file name=/u01/oracle/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=807813386 file name=/u01/oracle/oradata/orcl/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=807813386 file name=/u01/oracle/oradata/orcl/example01.dbf

renamed tempfile 1 to /u01/oracle/oradata/orcl/temp01.dbf in control file

RMAN>  SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo01.log''
TO ''/u01/oracle/oradata/orcl/redo01.log'' ";

sql statement: ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo01.log''TO ''/u01/oracle/oradata/orcl/redo01.log''

RMAN> SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo02.log''
TO ''/u01/oracle/oradata/orcl/redo02.log'' ";

sql statement: ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo02.log''TO ''/u01/oracle/oradata/orcl/redo02.log''

RMAN> SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo03.log''
TO ''/u01/oracle/oradata/orcl/redo03.log'' ";

sql statement: ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo03.log''TO ''/u01/oracle/oradata/orcl/redo03.log''

RMAN>

List of Backup Sets
===================


BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
47 93.13M DISK 00:00:02 19-FEB-13
BP Key: 47 Status: AVAILABLE Compressed: NO Tag: TAG20130219T150521
Piece Name: /u01/oracle/bkp/arc20130219ORCL1mo2cath_1_1

List of Archived Logs in backup set 47
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 3 1668460 19-FEB-13 1671683 19-FEB-13
1 4 1671683 19-FEB-13 1672642 19-FEB-13
1 5 1672642 19-FEB-13 1672835 19-FEB-13

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
48 Full 1.22G DISK 00:00:51 19-FEB-13
BP Key: 48 Status: AVAILABLE Compressed: NO Tag: TAG20130219T150524
Piece Name: /u01/oracle/bkp/20130219ORCL1no2catl_1_1
List of Datafiles in backup set 48
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1672845 19-FEB-13 /u01/oracle/oradata/orcl/system01.dbf
2 Full 1672845 19-FEB-13 /u01/oracle/oradata/orcl/sysaux01.dbf
3 Full 1672845 19-FEB-13 /u01/oracle/oradata/orcl/undotbs01.dbf
4 Full 1672845 19-FEB-13 /u01/oracle/oradata/orcl/users01.dbf
5 Full 1672845 19-FEB-13 /u01/oracle/oradata/orcl/example01.dbf

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
49 80.50K DISK 00:00:00 19-FEB-13
BP Key: 49 Status: AVAILABLE Compressed: NO Tag: TAG20130219T150620
Piece Name: /u01/oracle/bkp/arc20130219ORCL1oo2cavc_1_1

List of Archived Logs in backup set 49
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 1672835 19-FEB-13 1672872 19-FEB-13



RMAN> recover database until sequence 7 ;
Starting recover at 19-FEB-13
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 19-FEB-13

RMAN> report shcema
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 690 SYSTEM *** /u01/oracle/oradata/orcl/system01.dbf
2 660 SYSAUX *** /u01/oracle/oradata/orcl/sysaux01.dbf
3 60 UNDOTBS1 *** /u01/oracle/oradata/orcl/undotbs01.dbf
4 5 USERS *** /u01/oracle/oradata/orcl/users01.dbf
5 100 EXAMPLE *** /u01/oracle/oradata/orcl/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 0 TEMP 32767 /u01/oracle/oradata/orcl/temp01.dbf

RMAN>  alter database open resetlogs;
database opened

RMAN>

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
50 Full 9.42M DISK 00:00:02 19-FEB-13
BP Key: 50 Status: AVAILABLE Compressed: NO Tag: TAG20130219T164944
Piece Name: /u01/app/oracle/c-1334549344-20130219-03
Control File Included: Ckp SCN: 1672939 Ckp time: 19-FEB-13

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51 Full 9.42M DISK 00:00:03 19-FEB-13
BP Key: 51 Status: AVAILABLE Compressed: NO Tag: TAG20130219T164953
Piece Name: /u01/app/oracle/c-1334549344-20130219-04
Control File Included: Ckp SCN: 1673164 Ckp time: 19-FEB-13

RMAN>