Showing posts with label RMAN. Show all posts
Showing posts with label RMAN. Show all posts

Thursday, November 19, 2015

Skipping Tablespaces in RMAN cloning

Skipping Tablespaces in RMAN cloning

Skipping Tablespaces While Cloning RMAN Database  

The example will show how to skip Read Write, Read Only and Offline tablespaces. Then how to clone the database to a past point in time will be covered.
Perform all above steps up to Step 5 and bring the auxiliary database to the nomount mode. Then create three tablespaces in the production database: 
  • tbs_skip:  This is a Read Write tablespace. This tablespace will be skipped during the clone process.
  • tbs_readonly:  This is a Read Only tablespace and will be skipped automatically by the skip readonly command during the clone process.
  • tbs_offline: This is an offline tablespace and will be skipped automatically by RMAN during the clone process.
Then create a table and use it to show the result of the past point in time recovery. Look at this example in more details. Create three tablespaces:

sys@TEST> 
create 
tablespace tbs_skip datafile '/u02/oradata/test/tbs_skip.dbf' SIZE 1M;
Tablespace created.
sys@TEST> 
create 
tablespace tbs_readonly datafile '/u02/oradata/test/tbs_readonly.dbf'
SIZE 1M;
Tablespace created.
sys@TEST> 
create 
tablespace tbs_offline datafile '/u02/oradata/ /test/tbs_offline.dbf'
SIZE 1M;
Tablespace created.
sys@TEST>

Create two tables:  tbs_test and tbs_readonly.  The first table will be used in our past point in time recovery scenario. The second table will be used when the Read Only tablespace is recovered after the clone process. Create the tbs_readonly table and insert one row in it:

sys@TEST>
create 
table tbl_test (id number);
Table created.
sys@TEST>
create 
table tbl_readonly (col1 varchar2(15)) tablespace tbs_readonly;
Table created.
sys@TEST>
insert into
 tbl_readonly values('Readonly table');
1 row created.
sys@TEST> 
commit;
Commit complete;

Change the status of the tablespace tbs_readonly to Read Only, tbs_offline to Offline:

sys@TEST>
alter 
Tablespace tbs_readonly Read Only;
Tablespace altered.
sys@TEST>
alter 
tablespace tbs_offline Offline Normal;
Tablespace altered.
sys@TEST>


Connect to RMAN and take a full backup:

RMAN> backup database plus archivelog delete input;
Starting backup at 01-OCT-09
....................
....................
Finished backup at 01-OCT-09

Make changes to the tbl_test table and take a backup of the database and archivelogs.  Insert a row to the tbl_test table and commit it. Then take the current scn value of the database that will be used during recovery of said database. A clone will be made of the database to this scn value.
Next, insert one more row since it will be assumed that this row is a mistake and will not be recovered during the clone process. Then switch the redo log file and take a backup of the archived redo log files:

sys@TEST>
insert into 
tbl_test values(1);
1 row created.
sys@TEST>
commit;
Commit complete.
sys@TEST>
select * from 
tbl_test;

        ID
----------
         1
sys@TEST>
select 
current_scn 
from 
v$database;
current_scn
-----------
     471662
sys@TEST>
insert into 
tbl_test  values(100000);
1 row created.
sys@TEST>
commit;
Commit complete.
sys@TEST>
select * from
 tbl_test;

        ID
----------
         1
    100000

sys@TEST>
alter 
system switch logfile;
System altered.
sys@TEST>
exit
$ rman target /
RMAN> backup archivelog all delete input;
Starting backup at 01-OCT-09
....................
....................
Finished backup at 01-OCT-09
RMAN>

The database is ready to be cloned. To clone the database, connect to both databases and run the following script:

$ rman target sys/test auxiliary sys/test@clone_db
connected to target database: test (DBID=782965739)
connected to auxiliary database: clone_db (not mounted)
RMAN> run {
set newname for tempfile 1 to '/u03/oracle/new_clone/temp01.dbf';
set newname for datafile 1 to '/u03/oracle/new_clone/system01.dbf';
set newname for datafile 2 to '/u03/oracle/new_clone/undotbs01.dbf';
set newname for datafile 3 to '/u03/oracle/new_clone/sysaux01.dbf';
set newname for datafile 4 to '/u03/oracle/new_clone/users01.dbf';
set newname for datafile 5 to '/u03/oracle/new_clone/skip.dbf';
set newname for datafile 6 to '/u03/oracle/new_clone/readonly.dbf';
set newname for datafile 7 to '/u03/oracle/new_clone/offline.dbf';
duplicate target database to clone_db
skip tablespace tbs_skip
skip readonly
until scn 471662
logfile 
    '/u03/oracle/new_clone/redo log01.log' SIZE 5M,
    '/u03/oracle/new_clone/redo log02.log' SIZE 5M,  
    '/u03/oracle/new_clone/redo log03.log' SIZE 5M;
}

RMAN starts to clone and recovers the database until the specified scn value by skipping the tbl_skip tablespace, Read Only and Offline tablespaces. With the SKIP TABLESPACE clause, the tablespaces can be excluded from the duplicate database, but not the system tablespace or undo tablespace which contains rollback or undo segments. Below is the part of the result of the script:


contents of Memory Script:
{
   set until scn  471662;
   recover
   clone database
   delete archivelog
   ;
}
executing Memory Script
executing command: set until clause
Starting recover at 01-OCT-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=disk
datafile 5 not processed because file is offline
datafile 6 not processed because file is read-only
datafile 7 not processed because file is offline
starting media recovery
.................
.................
Finished recover at 01-OCT-09
contents of Memory Script:
{
   shutdown clone;
   startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
................
................
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script
database opened
contents of Memory Script:
{
# drop offline and skipped tablespaces
sql clone "drop tablespace  tbs_skip including contents cascade
constraints";
# drop offline and skipped tablespaces
sql clone "drop tablespace  tbs_offline including contents cascade
constraints";
}
executing Memory Script

sql statement: drop tablespace  tbs_skip including contents cascade
constraints

sql statement: drop tablespace  tbs_offline including contents cascade
constraints
Finished Duplicate Db at 01-OCT-09

RMAN>


As can be seen, the database has been recovered to scn 471662 and all tablespaces have been skipped.
datafile 5 not processed because file is offline
datafile 6 not processed because file is read-only
datafile 7 not processed because file is offline
Datafile 5 was manually skipped because the skip tablespace tbs_skip command was used.  Datafile 6 was skipped because it was a Read Only tablespace and all Read Only tablespaces were skipped by the skip readonly command.  Datafile 7 was skipped automatically by RMAN because it is an offline tablespace. Then the database was opened with the resetlogs optionand the tbs_skip and tbs_offline tablespaces dropped by RMAN. 
Now, query the tbl_test table:

sys@clone_db>
select * from
 tbl_test;

        ID
----------
         1

The last time when this table was queried, there were two lines: however, as the database was recovered until a specific scn value, at that scn there was only one row.
Now, query the tbl_readonly table:

sys@clone_db>
select * from 
tbl_readonly;
select * from
 tbl_readonly
              *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6:
'/u01/oracle/product/10.2.0/db_1/dbs/MISSING00006'


This shows that since the tbs_readonly tablespace was skipped with the skip readonly command, the table which resides on Datafile 6 cannot be queried. It was dropped after the database had been cloned and opened. Check the status of the datafile in the v$datafile view:

sys@clone_db>
set
 linesize 1000
sys@clone_db>
select 
status, enabled, name 
from 
v$datafile;


STATUS  ENABLED    NAME
------- ---------- -------------------------------------
SYSTEM  READ WRITE /u03/oracle/new_clone/system01.dbf
ONLINE  READ WRITE /u03/oracle/new_clone/undotbs01.dbf
ONLINE  READ WRITE /u03/oracle/new_clone/sysaux01.dbf
ONLINE  READ WRITE /u03/oracle/new_clone/users01.dbf
OFFLINE READ ONLY  /u01/oracle/product/10.2.0/db_1/dbs/MISSING00006


To recover this tablespace, copy it from the production databaseto the location where the auxiliary database's files are located and recover them by performing the following steps:
Copy it from the production database's file location to the auxiliary database's location:

sys@clone_db>host
$ cp /u01/oracle/test/tbs_readonly.dbf /u03/oracle/new_clone/

Note:  Now is the time to use a cp copy of the datafile without taking a fresh backup because the tablespace is Read Only. 

Then rename it and change the status to online in the clone_db database:

sys@clone_db>
alter tablespace
 tbs_readonly rename datafile
'/u01/oracle/product/10.2.0/db_1/dbs/MISSING00006' TO
'/u03/oracle/new_clone/tbs_readonly.dbf';
Tablespace altered.
sys@clone_db>
alter tablespace
 tbs_readonly online;
Tablespace altered.

Now view the status and datafile name of that file from the v$datafile view:

sys@clone_db>
select 
status, enabled, name
 from
 v$datafile;

STATUS  ENABLED    NAME
------- ---------- ---------------------------------
SYSTEM  READ WRITE /u03/oracle/new_clone/system01.dbf
ONLINE  READ WRITE /u03/oracle/new_clone/undotbs01.dbf
ONLINE  READ WRITE /u03/oracle/new_clone/sysaux01.dbf
ONLINE  READ WRITE /u03/oracle/new_clone/users01.dbf
ONLINE  READ ONLY  /u03/oracle/new_clone/tbs_readonly.dbf
Now the tbl_readonly table can be queried: 

sys@clone_db>
select * from
 tbl_readonly;
COL1
---------------
Readonly table
sys@clone_db>

Thursday, October 15, 2015

RMAN-03014: implicit resync of recovery catalog failed RMAN-06004: ORACLE error from recovery catalog database: RMAN-20035: invalid high RECID

RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database:
RMAN-20035: invalid high RECID

RMAN backup error 

Solution :

After the refresh this error is normal, because the catalog database could not identify the database because it holds the old information. So in order to let the catalog database identify the database we have to unregister and register the database once again to identify the database.

$rman target=/ catalog=rman/rman@rmancatalog

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 13 09:20:30 2015

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

connected to target database: ORCLL (DBID=2353077066)
connected to recovery catalog database

RMAN> unregister database;

database name is "ORCL" and DBID is 2353077066

Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

ORA-01008: not all variables bound while doing RMAN backup

ORA-01008: not all variables bound while doing RMAN backup

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jan 17 15:00:22 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
RMAN>
connected to target database: MADM (DBID=xvxvxvxv)
RMAN>
connected to recovery catalog database

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>

DBGSQL:     TARGET> select  nvl(max(al.recid), '0'),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in ('X', 'A')    and  al.is_recovery_dest_file = 'YES'    and  al.creator = 'RMAN'
DBGSQL:        sqlcode = 1008
allocated channel: t1
channel t1: SID=1726 device type=SBT_TAPE
channel t1: Veritas NetBackup for Oracle - Release 6.5 (2009050106)
DBGSQL:     TARGET> select  nvl(max(al.recid), '0'),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in ('X', 'A')    and  al.is_recovery_dest_file = 'YES'    and  al.creator = 'RMAN'
DBGSQL:        sqlcode = 1008
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 01/17/2012 15:00:36
RMAN-03014: implicit resync of recovery catalog failed
ORA-01008: not all variables bound
RMAN>
Recovery Manager complete.

I noticed that resync catalog or crosscheck archivelog all did not help in this case and this error appears due to a BUG in 11.2.0.2. You can read more about this issue at Oracle Metalink doc ID: 1280447.1

Current fix is to flush shared_pool and you will be able to start rman backup again.
SQL> alter system flush shared_pool;

Thursday, September 3, 2015

ORACLE 11G :ACTIVE DATABASE DUPLICATION - RMAN

ORACLE 11G :ACTIVE DATABASE DUPLICATION-RMAN

Oracle 11g introduced active database duplication using which we can create a duplicate database of the target database without any backups. Duplication is performed over the network.

Procedure :
Overview:
on the source  host
- Create Pfile from source database
- Create an entry in tnsnames.ora for duplictae database on target host on port 1522
on the target host
- Add a line in the file /etc/oratab to reflect the database instance you are going to copy
- create folders
- Copy the initialization parameter file from the source database add edit it.
- Copy the password file
- Create a listener in database home on port 1522 and register duplicate database statically with it
- Startup the target database in nomount mode using modified parameter file
- Using RMAN  connect to the source database(orcl) as target database and duplicate database (orclt) as auxiliary instance
- duplicate the target database
********************************
  source database  orcl
  Duplicate database  orclt
***********************************
Implementation
– On source host
– CREATE PFILE FROM SOURCE DATABASE
SQL>CREATE PFILE=’/u01/app/oracle/oradata/orcl/initsource.ora’     FROM SPFILE;
– On source database, create a service for orclt on target host on port 1522
The rest of the steps occur on the target host.
– Add a line in the file /etc/oratab to reflect the database instance you are going to copy
     orclt:/u01/app/oracle/product/11.2.0/db1:N
– Now set the Oracle SID as the duplicated database SID:
# . oraenv
ORACLE_SID = [orclt] ?
– create folders
 $mkdir -p /u01/app/oracle/oradata/orclt
  mkdir -p /u01/app/oracle/flash_recovery_area/orclt
  mkdir -p /u01/app/oracle/admin/orclt/adump
  mkdir -p /u01/app/oracle/admin/orclt/dpdump
– Copy the initialization parameter file from the main database.
$cp  /u01/app/oracle/oradata/orcl/initsource.ora /u01/app/oracle/oradata/orclt/inittarget.ora
– Edit the initialization parameter file
$vi /u01/app/oracle/oradata/orclt/inittarget.ora
   – Change db_name = orclt
   – Edit it to reflect the new locations that might be appropriate
     such as control file locations,audit dump destinations, datafile
     locations, etc.
   – add these lines –
     db_file_name_convert = (“/u01/app/oracle/oradata/orcl”,
                             “/u01/app/oracle/oradata/orclt”)
     log_file_name_convert = (“/u01/app/oracle/oradata/orcl”,
                             “/u01/app/oracle/oradata/orclt”)
In case source and destination databases ae ASM, following lines can be added :
db_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”)
log_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”, “+FRA/orcl”,”+FRA/orclt”)
– Copy the password file as well
$cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl  /u01/app/oracle/product/11.2.0/db_1/dbs/orapworclt
– Startup the target database in nomount mode using modified parameter file
$ . oraenv
ORACLE_SID = [orclt] ?
$sqlplus sys/oracle as sysdba
SQL> startup nomount pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora';
     create spfile from pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora';
– create a listener on port 1522 in database home on target host and statically register service
    orclt with it.
– connect to the auxiliary instance
$. oraenv
   orclt
$rman target sys/oracle@orcl auxiliary sys/oracle@orclt
– duplicate the database orcl to orclt from active database
– the command performs the following steps:
    * Creates an SPFILE
    * Shuts down the instance and restarts it with the new spfile
    * Restores the controlfile from the backup
    * Mounts the database
    * Performs restore of the datafiles. In this stage it creates the files in the
      converted names.
    * Recovers the datafiles up to the time specified and opens the database
RMAN>duplicate target database to orclt from active database;
– check that duplicate database is up
$sqlplus / as sysdba
sql>conn hr/hr
    select * from tab;
-- Note that  DBID is different from the main database so it can be backed up
   independently and using the same catalog as well.
SQL> select dbid from v$database;
      DBID
———-
3779357884
     conn sys/oracle@orcl as sysdba
    
     select dbid from v$database;
      DBID
———-
1326904854

Monday, July 27, 2015

Oracle RMAN Backup error - ORA-01008

ORA-01008: not all variables bound while doing RMAN backup

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jul 217 13:026:22 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
RMAN>
connected to target database: QSTPRDR (DBID=3992020636)
RMAN>
connected to recovery catalog database

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>

DBGSQL:     TARGET> select  nvl(max(al.recid), '0'),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in ('X', 'A')    and  al.is_recovery_dest_file = 'YES'    and  al.creator = 'RMAN'
DBGSQL:        sqlcode = 1008
allocated channel: t1
channel t1: SID=1726 device type=SBT_TAPE
channel t1: Veritas NetBackup for Oracle - Release 6.5 (2009050106)
DBGSQL:     TARGET> select  nvl(max(al.recid), '0'),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in ('X', 'A')    and  al.is_recovery_dest_file = 'YES'    and  al.creator = 'RMAN'
DBGSQL:        sqlcode = 1008
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 27/07/2015 13:28:36
RMAN-03014: implicit resync of recovery catalog failed
ORA-01008: not all variables bound
RMAN>
Recovery Manager complete.

I noticed that resync catalog or crosscheck archivelog all did not help in this case and this error appears due to a BUG in 11.2.0.2. You can read more about this issue at Oracle Metalink doc ID: 1280447.1

Current fix is to flush shared_pool and you will be able to start rman backup again.
SQL> alter system flush shared_pool;

Friday, February 6, 2015

Restore database to new server using RMAN Backup


Restore RMAN to New Host

Steps On Source Database

 **Most Of work will be done on target database

 1- Get DBID By Using The below :

SQL > Select Db_id from v$database ;

 Or RMAN target /

2- Get the last SCN On Database since we will not have active redolog using the below document :

SQL > select max(next_change#) from v$archived_log where archived = 'YES' group by thread#;

 Or Using RMAN:

 RMAN> list backup of archivelog all;

3- Take Backup Using RMAN :

 run
{ allocate channel d1 type disk format 'YOUR-PATH/Database_%U;
allocate channel c1 type disk; allocate channel c2 type disk;
 allocate channel c3 type disk;
BACKUP DATABASE PLUS ARCHIVELOG;
backup current controlfile format 'YOUR_PATH/controlfile_%U';
release channel c1;
release channel c2;
 release channel c3;
 }

Now move all generated backup file to the new host with the same directory for example if you backup on folder /u01/app then on new host should be the same.

Steps On New Server 

After copy file do the below steps:

 1- export ORACLE_SID=SID_SAME_AS_PROD

RMAN TARGET / RMAN > set dbid ; 

RMAN > Startup nomount; 

RMAN > restore spfile to pfile '/u01/app/oracle/oradata/orcl/initorcl.ora' from autobackup; 

RMAN> shutdown abort; 

Now you have to edit SPFile ; 

*.audit_file_dest=’LOCATION’ 
*.control_files=’LOCATION’ 
*.db_recovery_file_dest_size=Size 
*.db_recovery_file_dest=’LOCATION’ 
*.diagnostic_dest=’LOCATION’ 
*.log_archive_dest_1='LOCATION=LOCATION’ 
*.local_listener='LISTENER_ORCL'

RMAN> startup nomount pfile='/u01/app/oracle/oradata/orcl/initorcl.ora'; 

RMAN> restore controlfile from autobackup; 

RMAN> alter database mount; 

Note: now you have 2 options

1 - move the backup to same location on target server 
2- Copy Backup to any location but you need to use CATALOG Command check below : 

RMAN> catalog start with 'Where You copy Backup'; 
You are almost Done,

You have But before restore database you can move datafile to any location using (all the below steps on RMAN ) But Remember don't run the below yet: 

SET NEWNAME FOR DATAFILE 1 TO '/u01/app/oracle/oradata/orcl/system01.dbf'; 
SET NEWNAME FOR DATAFILE 2 TO '/u01/app/oracle/oradata/orcl/sysaux01.dbf'; 
SET NEWNAME FOR DATAFILE 3 TO '/u01/app/oracle/oradata/orcl/undotbs01.dbf'; 
SET NEWNAME FOR DATAFILE 4 TO '/u01/app/oracle/oradata/orcl/users01.dbf';
 SET NEWNAME FOR DATAFILE 5 TO '/u01/app/oracle/oradata/orcl/example01.dbf'; 
SET NEWNAME FOR DATAFILE 6 TO '/u01/app/oracle/oradata/orcl/test.dbf'; 
SET NEWNAME FOR DATAFILE 7 TO '/u01/app/oracle/oradata/orcl/dbfs01.dbf'; 

**Note: After TO: New Location.


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

Are you still having SCN that we see it above :) 
Ok Now you RMAN Script will looks like below 

RUN 
{ 
SET NEWNAME FOR DATAFILE 1 TO '/u01/app/oracle/oradata/orcl/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/u01/app/oracle/oradata/orcl/sysaux01.dbf'; 
SET NEWNAME FOR DATAFILE 3 TO '/u01/app/oracle/oradata/orcl/undotbs01.dbf'; 
SET NEWNAME FOR DATAFILE 4 TO '/u01/app/oracle/oradata/orcl/users01.dbf'; 
SET NEWNAME FOR DATAFILE 5 TO '/u01/app/oracle/oradata/orcl/example01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/u01/app/oracle/oradata/orcl/test.dbf'; 
SET NEWNAME FOR DATAFILE 7 TO '/u01/app/oracle/oradata/orcl/dbfs01.dbf'; 
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo01.log'' TO ''/u01/app/oracle/oradata/orcl/redo/redo01.log'' "; 
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo02.log'' TO ''/u01/app/oracle/oradata/orcl/redo/redo02.log'' "; 
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo03.log'' TO ''/u01/app/oracle/oradata/orcl/redo/redo03.log'' ";
SET UNTIL SCN ; 

RESTORE DATABASE; 

SWITCH DATAFILE ALL;

 RECOVER DATABASE;
 } 

Sqlplus / as sysdba 

SQL > Alter database open Resetlogs ;

Restore RMAN backup to another server for testing disaster recovery procedures as well as for cloning

Restore RMAN backup to another server for testing disaster recovery procedures as well as for cloning

This note explains the procedure used to restore an Oracle Suggested Strategy incremental backup on another server.
This is useful when we have to do periodic  testing of disaster recovery procedures where we simulate a scenario when the complete database server has crashed and a new server has been provisioned.
But let us assume that the directory structure on the new or target server is different to that of the source server.
So in this case the backup has been restored to the staging location /home/oracle/stage on the new server  and all the database files are being restored in a different location to that on the source – /home/oracle/sqlfun

Restore the SPFILE
RMAN> startup nomount force;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_2/dbs/initsqlfun.ora'

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

Total System Global Area     158662656 bytes

Fixed Size                     2226456 bytes
Variable Size                104859368 bytes
Database Buffers              46137344 bytes
Redo Buffers                   5439488 bytes

RMAN> restore spfile from '/home/oracle/stage/o1_mf_s_819826669_8x7w7g70_.bkp';

Starting restore at 04-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/stage/o1_mf_s_819826669_8x7w7g70_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 04-JUL-13
Create the PFILE from SPFILE and make parameter changes as required
$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 09:43:48 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:
Connected to an idle instance.

SQL> create pfile from spfile;

File created.
Note- make required changes here in the init.ora file to take care of new directories for control files, audit dump, fast recovery area etc
SQL> create spfile from pfile;

File created.

Restore the Control Files
RMAN> restore controlfile from '/home/oracle/stage/o1_mf_s_819826669_8x7w7g70_.bkp';

Starting restore at 04-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/sqlfun/control01.ctl
output file name=/home/oracle/sqlfun/control02.ctl
Finished restore at 04-JUL-13

Mount the database and catalog the backup pieces which have been restored in the new location
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> catalog start with '/home/oracle/stage';

Starting implicit crosscheck backup at 04-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
Crosschecked 25 objects
Finished implicit crosscheck backup at 04-JUL-13

Starting implicit crosscheck copy at 04-JUL-13
using channel ORA_DISK_1
Crosschecked 10 objects
Finished implicit crosscheck copy at 04-JUL-13

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/SQLFUN/autobackup/2013_07_04/o1_mf_n_819884144_8x9ncjfl_.bkp
File Name: /u01/app/oracle/flash_recovery_area/SQLFUN/archivelog/2013_07_04/2013_07_03/o1_mf_1_33_8x84ld7x_.arc
File Name: /u01/app/oracle/flash_recovery_area/SQLFUN/archivelog/2013_07_04/2013_07_03/o1_mf_1_35_8x8db3hp_.arc
File Name: /u01/app/oracle/flash_recovery_area/SQLFUN/archivelog/2013_07_04/2013_07_03/o1_mf_1_36_8x8dbgcm_.arc
File Name: /u01/app/oracle/flash_recovery_area/SQLFUN/archivelog/2013_07_04/2013_07_03/o1_mf_1_34_8x8d9y2h_.arc
File Name: /u01/app/oracle/flash_recovery_area/SQLFUN/archivelog/2013_07_04/2013_07_03/o1_mf_1_32_8x6mflv0_.arc

searching for all files that match the pattern /home/oracle/stage

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/stage/o1_mf_sysaux_7zpgb1hd_.dbf
File Name: /home/oracle/stage/o1_mf_nnnd1_ORA_OEM_LEVEL_0_8x7w6zcb_.bkp
File Name: /home/oracle/stage/o1_mf_nnnd1_ORA_OEM_LEVEL_0_8x7w76jv_.bkp
File Name: /home/oracle/stage/o1_mf_undotbs1_7zpgck9f_.dbf
File Name: /home/oracle/stage/o1_mf_nnnd1_ORA_OEM_LEVEL_0_8x7w65l9_.bkp
File Name: /home/oracle/stage/o1_mf_example_7zgkdp6p_.dbf
File Name: /home/oracle/stage/o1_mf_threaten_7zpgbyck_.dbf
File Name: /home/oracle/stage/o1_mf_users_7zpgcnno_.dbf
File Name: /home/oracle/stage/o1_mf_s_819826669_8x7w7g70_.bkp
File Name: /home/oracle/stage/o1_mf_index_da_7zgkcz04_.dbf
File Name: /home/oracle/stage/o1_mf_example__7zpgcg0x_.dbf
File Name: /home/oracle/stage/o1_mf_system_7zpgbh79_.dbf
File Name: /home/oracle/stage/o1_mf_nnnd1_ORA_OEM_LEVEL_0_8x7w7b0f_.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/stage/o1_mf_sysaux_7zpgb1hd_.dbf
File Name: /home/oracle/stage/o1_mf_nnnd1_ORA_OEM_LEVEL_0_8x7w6zcb_.bkp
File Name: /home/oracle/stage/o1_mf_nnnd1_ORA_OEM_LEVEL_0_8x7w76jv_.bkp
File Name: /home/oracle/stage/o1_mf_undotbs1_7zpgck9f_.dbf
File Name: /home/oracle/stage/o1_mf_nnnd1_ORA_OEM_LEVEL_0_8x7w65l9_.bkp
File Name: /home/oracle/stage/o1_mf_example_7zgkdp6p_.dbf
File Name: /home/oracle/stage/o1_mf_threaten_7zpgbyck_.dbf
File Name: /home/oracle/stage/o1_mf_users_7zpgcnno_.dbf
File Name: /home/oracle/stage/o1_mf_s_819826669_8x7w7g70_.bkp
File Name: /home/oracle/stage/o1_mf_index_da_7zgkcz04_.dbf
File Name: /home/oracle/stage/o1_mf_example__7zpgcg0x_.dbf
File Name: /home/oracle/stage/o1_mf_system_7zpgbh79_.dbf
File Name: /home/oracle/stage/o1_mf_nnnd1_ORA_OEM_LEVEL_0_8x7w7b0f_.bkp

Generate the SET NEWNAME FOR DATAFILE command
Note:
Since the OSS backup image copy is based on Oracle Managed File format (OMF), I find this error even though we have set the DB_FILE_NAME_CONVERT parameter to account for the directory path change between source and target.
RMAN is not restoring the data files in the new location but is looking for the directory path which existed on the source database, but which is not present on the new or target server where we are doing the restore.

RMAN> restore database;
Starting restore at 04-JUL-13
using channel ORA_DISK_1
channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=3060 STAMP=819884997 file name=/home/oracle/stage/o1_mf_system_7zpgbh79_.dbf
destination for restore of datafile 00001: /u01/app/oracle/oradata/sqlfun/system01.dbf
ORA-19504: failed to create file “/u01/app/oracle/oradata/sqlfun/system01.dbf”
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
ORA-19600: input file is datafile-copy 3060 (/home/oracle/stage/o1_mf_system_7zpgbh79_.dbf)
ORA-19601: output file is datafile 1 (/u01/app/oracle/oradata/sqlfun/system01.dbf)

So to work around this we will generate a text file via SQL*PLUS which will contain the SET NEWNAME commands and call this file from RMAN.
These are the contents of the text file rename_files.sql
set head off pages 0 feed off echo off verify off
set lines 200
spool rename_datafiles.lst
select ‘SET NEWNAME FOR DATAFILE ‘ || FILE# || ‘ TO ”’ || ‘/home/oracle/sqlfun/’ || substr(name,instr(name,’/’,-1)+1) || ”’;’ from v$datafile;
spool off
exit;
$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 09:52:44 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @rename_files.sql
SET NEWNAME FOR DATAFILE 1 TO '/home/oracle/sqlfun/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/home/oracle/sqlfun/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/home/oracle/sqlfun/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/home/oracle/sqlfun/users01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/home/oracle/sqlfun/threatened_fauna_data.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/home/oracle/sqlfun/example_temp01.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/home/oracle/sqlfun/EXAMPLE_5';
SET NEWNAME FOR DATAFILE 8 TO '/home/oracle/sqlfun/INDEX_DATA_6';

Restore and Recover the database
Now pass this file name to the RMAN run block
RMAN> run {
2> @rename_datafiles.lst
3> SET NEWNAME FOR DATAFILE 1 TO '/home/oracle/sqlfun/system01.dbf';
4> SET NEWNAME FOR DATAFILE 2 TO '/home/oracle/sqlfun/sysaux01.dbf';
5> SET NEWNAME FOR DATAFILE 3 TO '/home/oracle/sqlfun/undotbs01.dbf';
6> SET NEWNAME FOR DATAFILE 4 TO '/home/oracle/sqlfun/users01.dbf';
7> SET NEWNAME FOR DATAFILE 5 TO '/home/oracle/sqlfun/threatened_fauna_data.dbf';
8> SET NEWNAME FOR DATAFILE 6 TO '/home/oracle/sqlfun/example_temp01.dbf';
9> SET NEWNAME FOR DATAFILE 7 TO '/home/oracle/sqlfun/EXAMPLE_5';
10> SET NEWNAME FOR DATAFILE 8 TO '/home/oracle/sqlfun/INDEX_DATA_6';
11> **end-of-file**
12> restore database;
13> switch datafile all;
14> recover database;
15> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 04-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK

channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=3060 STAMP=819884997 file name=/home/oracle/stage/o1_mf_system_7zpgbh79_.dbf
destination for restore of datafile 00001: /home/oracle/sqlfun/system01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00001
output file name=/home/oracle/sqlfun/system01.dbf RECID=3061 STAMP=819885296
channel ORA_DISK_1: restoring datafile 00002
input datafile copy RECID=3053 STAMP=819884997 file name=/home/oracle/stage/o1_mf_sysaux_7zpgb1hd_.dbf
destination for restore of datafile 00002: /home/oracle/sqlfun/sysaux01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00002
output file name=/home/oracle/sqlfun/sysaux01.dbf RECID=3062 STAMP=819885313
channel ORA_DISK_1: restoring datafile 00003
input datafile copy RECID=3054 STAMP=819884997 file name=/home/oracle/stage/o1_mf_undotbs1_7zpgck9f_.dbf
destination for restore of datafile 00003: /home/oracle/sqlfun/undotbs01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00003
output file name=/home/oracle/sqlfun/undotbs01.dbf RECID=3063 STAMP=819885321
channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=3057 STAMP=819884997 file name=/home/oracle/stage/o1_mf_users_7zpgcnno_.dbf
destination for restore of datafile 00004: /home/oracle/sqlfun/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/home/oracle/sqlfun/users01.dbf RECID=3064 STAMP=819885322
channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=3056 STAMP=819884997 file name=/home/oracle/stage/o1_mf_threaten_7zpgbyck_.dbf
destination for restore of datafile 00005: /home/oracle/sqlfun/threatened_fauna_data.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00005
output file name=/home/oracle/sqlfun/threatened_fauna_data.dbf RECID=3065 STAMP=819885327
channel ORA_DISK_1: restoring datafile 00006
input datafile copy RECID=3059 STAMP=819884997 file name=/home/oracle/stage/o1_mf_example__7zpgcg0x_.dbf
destination for restore of datafile 00006: /home/oracle/sqlfun/example_temp01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00006
output file name=/home/oracle/sqlfun/example_temp01.dbf RECID=3066 STAMP=819885331
channel ORA_DISK_1: restoring datafile 00007
input datafile copy RECID=3055 STAMP=819884997 file name=/home/oracle/stage/o1_mf_example_7zgkdp6p_.dbf
destination for restore of datafile 00007: /home/oracle/sqlfun/EXAMPLE_5
channel ORA_DISK_1: copied datafile copy of datafile 00007
output file name=/home/oracle/sqlfun/EXAMPLE_5 RECID=3067 STAMP=819885334
channel ORA_DISK_1: restoring datafile 00008
input datafile copy RECID=3058 STAMP=819884997 file name=/home/oracle/stage/o1_mf_index_da_7zgkcz04_.dbf
destination for restore of datafile 00008: /home/oracle/sqlfun/INDEX_DATA_6
channel ORA_DISK_1: copied datafile copy of datafile 00008
output file name=/home/oracle/sqlfun/INDEX_DATA_6 RECID=3068 STAMP=819885339
Finished restore at 04-JUL-13

datafile 1 switched to datafile copy
input datafile copy RECID=3069 STAMP=819885343 file name=/home/oracle/sqlfun/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3070 STAMP=819885343 file name=/home/oracle/sqlfun/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3071 STAMP=819885343 file name=/home/oracle/sqlfun/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3072 STAMP=819885343 file name=/home/oracle/sqlfun/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=3073 STAMP=819885343 file name=/home/oracle/sqlfun/threatened_fauna_data.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=3074 STAMP=819885343 file name=/home/oracle/sqlfun/example_temp01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=3075 STAMP=819885343 file name=/home/oracle/sqlfun/EXAMPLE_5
datafile 8 switched to datafile copy
input datafile copy RECID=3076 STAMP=819885343 file name=/home/oracle/sqlfun/INDEX_DATA_6

Starting recover at 04-JUL-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /home/oracle/sqlfun/system01.dbf
destination for restore of datafile 00002: /home/oracle/sqlfun/sysaux01.dbf
destination for restore of datafile 00007: /home/oracle/sqlfun/EXAMPLE_5
destination for restore of datafile 00008: /home/oracle/sqlfun/INDEX_DATA_6
channel ORA_DISK_1: reading from backup piece /home/oracle/stage/o1_mf_nnnd1_ORA_OEM_LEVEL_0_8x7w65l9_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/stage/o1_mf_nnnd1_ORA_OEM_LEVEL_0_8x7w65l9_.bkp tag=ORA_OEM_LEVEL_0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /home/oracle/sqlfun/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/stage/o1_mf_nnnd1_ORA_OEM_LEVEL_0_8x7w6zcb_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/stage/o1_mf_nnnd1_ORA_OEM_LEVEL_0_8x7w6zcb_.bkp tag=ORA_OEM_LEVEL_0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /home/oracle/sqlfun/users01.dbf
destination for restore of datafile 00005: /home/oracle/sqlfun/threatened_fauna_data.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/stage/o1_mf_nnnd1_ORA_OEM_LEVEL_0_8x7w76jv_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/stage/o1_mf_nnnd1_ORA_OEM_LEVEL_0_8x7w76jv_.bkp tag=ORA_OEM_LEVEL_0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00006: /home/oracle/sqlfun/example_temp01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/stage/o1_mf_nnnd1_ORA_OEM_LEVEL_0_8x7w7b0f_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/stage/o1_mf_nnnd1_ORA_OEM_LEVEL_0_8x7w7b0f_.bkp tag=ORA_OEM_LEVEL_0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 33 is already on disk as file /u01/app/oracle/flash_recovery_area/SQLFUN/archivelog/2013_07_04/2013_07_03/o1_mf_1_33_8x84ld7x_.arc
archived log for thread 1 with sequence 34 is already on disk as file /u01/app/oracle/flash_recovery_area/SQLFUN/archivelog/2013_07_04/2013_07_03/o1_mf_1_34_8x8d9y2h_.arc
archived log for thread 1 with sequence 35 is already on disk as file /u01/app/oracle/flash_recovery_area/SQLFUN/archivelog/2013_07_04/2013_07_03/o1_mf_1_35_8x8db3hp_.arc
archived log for thread 1 with sequence 36 is already on disk as file /u01/app/oracle/flash_recovery_area/SQLFUN/archivelog/2013_07_04/2013_07_03/o1_mf_1_36_8x8dbgcm_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/SQLFUN/archivelog/2013_07_04/2013_07_03/o1_mf_1_33_8x84ld7x_.arc thread=1 sequence=33
archived log file name=/u01/app/oracle/flash_recovery_area/SQLFUN/archivelog/2013_07_04/2013_07_03/o1_mf_1_34_8x8d9y2h_.arc thread=1 sequence=34
archived log file name=/u01/app/oracle/flash_recovery_area/SQLFUN/archivelog/2013_07_04/2013_07_03/o1_mf_1_35_8x8db3hp_.arc thread=1 sequence=35
archived log file name=/u01/app/oracle/flash_recovery_area/SQLFUN/archivelog/2013_07_04/2013_07_03/o1_mf_1_36_8x8dbgcm_.arc thread=1 sequence=36
unable to find archived log
archived log thread=1 sequence=37
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/04/2013 09:55:58
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 37 and starting SCN of 33985482

RMAN>
the error can be ignored as RMAN has applied all the available archive log files and is now trying to apply a non-existent archive log file.
Open the database with RESETLOGS
The RESETLOGS command will fail because we do not have the same directory path which existed on the source for the Online Redo Log files.
So we create a script which will generate the new online redo log file names.
These are the contents of the rename_logfiles.sql file
set head off pages 0 feed off echo off verify off
set lines 200
spool rename_logfiles.lst
select ‘alter database rename file ”’|| member ||”’ ‘||chr(10)|| ‘ TO ”’ || ‘/home/oracle/sqlfun/’ || substr(member,instr(member,’/’,-1)+1) || ”’;’ from v$logfile;
spool off
exit;
$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 10:05:17 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @rename_logfiles.sql
alter database rename file  '/u01/app/oracle/oradata/sqlfun/redo03.log'
TO '/home/oracle/sqlfun/redo03.log';

alter database rename file  '/u01/app/oracle/oradata/sqlfun/redo02.log'
TO '/home/oracle/sqlfun/redo02.log';

alter database rename file  '/u01/app/oracle/oradata/sqlfun/redo01.log'
TO '/home/oracle/sqlfun/redo01.log';
Note – we can do all this from the RMAN prompt as well using the RMAN SQL command. But for this example we do it from SQL*PLUS
$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 10:50:44 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @rename_logfiles.lst

Database altered.

Database altered.

Database altered.

SQL> alter database open resetlogs;

Database altered.