Friday 7 October 2016

Step by Step RMAN restore to different host with ASM Migration from File System (Standalone Database or Single Instance database )

This post is about  RMAN restore from one host to another host. The database  will be migrated from file system to ASM disk as part of the restore.  There may be tons of post for RMAN restore. But i am not finding the detail level document for RMAN restore with ASM migration. I hope, this fills up the gap and useful  for others. This post is tested in Oracle 11g(11.2.0.4.0).



Source Database : PROD      
Version : 11.2.0.4    
Storage : Filesystem 



Target Database : TEST        
Version : 11.2.0.4    
Stroage : ASM  
Diskgroups : TEST_DATA



This post could be useful for the following circumstance.

1. The production database server is crashed and no Data Guard standby.
2. You need to test some of the bugs in test database with current production  image.
3. Migrating the database from  file system to ASM disk in new hardware.

Here are the high level steps :

1. Backup the database in target database server. We can  also use recent L0 backup in the tape/Disk.
2. FTP the RMAN backups and password file  to auxiliary server (Target Server where we would like restore and recovery )
3. Restore the SPFILE,
4. Start the instance with nomount mode
5. Restore the control file
6. Mount the database
7. Restore  the database & Recover  the database
8. Open the database
9. Create the temp file
10. Create spfile on ASM disk
11. Database verification and validation.

Target  DB info :-

set pages 10000
select name from v$datafile
union
select name from v$controlfile
union
select member from v$logfile
union
select name from v$tempfile;


I am restoring the above database to the new host called DEV.CORP.COM. The RBDMS is already installed in DEV.CORP.COM host and it is same as target database version.





Step 1 :-  Take Database Full Backup

I am using recent RMAN L0 backup  and Archive log backup in target server. I am using the below script to backup the database in target server. We can also use the recent full backup from tape if it is already available in tape. I am using disk backup for my convenience.




run
{
allocate channel c01 device type disk;
BACKUP FORMAT '/backup/backup_recovery/DEV_TEST/dbf_%U' DATABASE TAG Full_bk;
BACKUP CURRENT ControlFile FORMAT '/backup/backup_recovery/DEV_TEST/ctl_%U';
sql 'alter system archive log current';
BACKUP FORMAT '/backup/backup_recovery/DEV_TEST/arch_%U' ARCHIVELOG ALL TAG Archivelog_bk DELETE INPUT;
RESTORE DATABASE VALIDATE;
CROSSCHECK BACKUP OF DATABASE;
REPORT OBSOLETE;
release channel c01;
}


RMAN> list backup of spfile;

-- note down output of backup peice its required to restore on target from this piece

RMAN> list backup of controlfile;

-- note down output of backup peice its required to restore on target from this piece


Step 2:- Copy the RMAN backup files to target server. 


Create the file system directories on target server.


Here i am using the ASM disk.

The RMAN backup files are coped to target server as below.

Also copy the password file from target server to target database server.

 scp orapwPROD username@hostname:$ORACLE_HOME/dbs/orapwTEST

-- Transfer the backup files to target system where you have to restore server

    My backup are location on source server : /backup/backup_recovery/DEV_TEST/
   My  backup need to move on target server location : /tempnfs/OSS_INSTALL/BACKUP/DEV_TEST/

scp /backup/backup_recovery/DEV_TEST/* oracle@test.corp.com:/tempnfs/OSS_INSTALL/BACKUP/DEV_TEST/



Step3: start DB with Nomount


-- start database nomount with temp init.ora with sufficient parameters

startup nomount pfile='$ORACLE_HOME/dbs/inittemp.ora'

Output:-

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             683672456 bytes
Database Buffers          373293056 bytes
Redo Buffers                9711616 bytes


Contents of inittemp.ora

SQL> ! cat $ORACLE_HOME/dbs/inittemp.ora
db_name='TEST'
memory_target=1G
processes = 150
audit_file_dest='/u00/app/oracle/admin/TEST/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
diagnostic_dest='/u00/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'

Step4:- Restore the SPFILE




Note : you will get file from step 1 list spfile backup which file having spfile backup

restore spfile  from '/backup/OSS_INSTALL/BACKUP/DEV_TEST/dbf_46rhmkcj_1_1';

Output:-


RMAN> restore spfile  from '/backup/OSS_INSTALL/BACKUP/DEV_TEST/dbf_46rhmkcj_1_1';

Starting restore at 07-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=193 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /tempnfs/OSS_INSTALL/BACKUP/DEV_TEST/dbf_46rhmkcj_1_1
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 07-OCT-16



dukeortd70 | TEST | /u00/app/oracle/product/11.2.0.4/DB/dbs
> ls -ltrh
total 20K
-rw-r--r-- 1 oracle dba 2.8K May 15  2009 init.ora
-rw-r--r-- 1 oracle dba   58 Oct  7 09:53 initTEST.ora.bak
-rw-r--r-- 1 oracle dba  470 Oct  7 12:19 inittemp.ora
-rw-rw---- 1 oracle dba 1.6K Oct  7 12:35 hc_TEST.dat
-rw-r----- 1 oracle dba 3.5K Oct  7 12:44 spfileTEST.ora



Step 5:-   Create pfile from RESTORED SPFILE and Modify 

Create pfile from  above restored spfile and modify the parameters according to auxiliary database server. Copy the updated parameter file under $ORACLE_HOME/dbs location.

Here is the modified pfile in auxiliary database under $ORACLE_HOME/dbs

cd $ORACLE_HOME/dbs

SQL> create pfile from spfile='/u00/app/oracle/product/11.2.0.4/DB/dbs/spfileTEST.ora';

File created.


remove any old references file systems below are the change in my initTEST.ora


*.audit_file_dest='/u00/app/oracle/admin/TEST/adump'
#*.control_files='/u02/oradata/TEST/*.db_create_file_dest='+TEST_DATA'
log_archive_dest_1='location=+TEST_DATA'
*.DB_CREATE_ONLINE_LOG_DEST_1='+TEST_DATA'
db_file_name_convert='/u02/oradata/TEST/datafile/','+TEST_DATA'
log_file_name_convert='/u02/oradata/TEST/onlinelog/','+TEST_DATA'


-- start with above initTEST.ora database


SQL> startup nomount pfile='$ORACLE_HOME/dbs/initTEST.ora'
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             721424360 bytes
Database Buffers          520093696 bytes
Redo Buffers                8892416 bytes



Step 6:-   Restore the Controlfile


RMAN> RESTORE CONTROLFILE FROM '/backup/OSS_INSTALL/BACKUP/DEV_TEST/ctl_47rhmkcl_1_1';

Starting restore at 07-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1776 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+TEST_DATA/TEST/controlfile/current.278.924618987
Finished restore at 07-OCT-16



Step 7: Update the correct control file name in pfile. 


Here is the updated pfile info under $ORACLE_HOME/dbs


grep -i control initTEST.ora
*.control_files='+TEST_DATA/TEST/controlfile/current.278.924618987'



shutdown immediate

startup mount pfile='$ORACLE_HOME/dbs/initTEST.ora'

ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             721424360 bytes
Database Buffers          520093696 bytes
Redo Buffers                8892416 bytes
Database mounted.




Step 8:- catalog the database backups 


rman target /
catalog start with '/backup/OSS_INSTALL/BACKUP/DEV_TEST/';


~~~~~~~~~
OUTPTUT:-
~~~~~~~~~

RMAN> catalog start with '/backup/OSS_INSTALL/BACKUP/DEV_TEST/';

using target database control file instead of recovery catalog
searching for all files that match the pattern /backup/OSS_INSTALL/BACKUP/DEV_TEST/

List of Files Unknown to the Database
=====================================
File Name: /backup/OSS_INSTALL/BACKUP/DEV_TEST/dbf_45rhmk5i_1_1
File Name: /backup/OSS_INSTALL/BACKUP/DEV_TEST/dbf_46rhmkcj_1_1
File Name: /backup/OSS_INSTALL/BACKUP/DEV_TEST/ctl_47rhmkcl_1_1
File Name: /backup/OSS_INSTALL/BACKUP/DEV_TEST/arch_48rhmkco_1_1

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

List of Cataloged Files
=======================
File Name: /backup/OSS_INSTALL/BACKUP/DEV_TEST/dbf_45rhmk5i_1_1
File Name: /backup/OSS_INSTALL/BACKUP/DEV_TEST/dbf_46rhmkcj_1_1
File Name: /backup/OSS_INSTALL/BACKUP/DEV_TEST/ctl_47rhmkcl_1_1
File Name: /backup/OSS_INSTALL/BACKUP/DEV_TEST/arch_48rhmkco_1_1


Step 9:- Restore Database & Recovery



 run {
 set newname for database to '+TEST_DATA';
 restore database;
 switch datafile all;
 RECOVER DATABASE;
 }

----------
OUTPUT:-
----------


executing command: SET NEWNAME

Starting restore at 07-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1634 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 +TEST_DATA
channel ORA_DISK_1: restoring datafile 00002 to +TEST_DATA
channel ORA_DISK_1: restoring datafile 00003 to +TEST_DATA
channel ORA_DISK_1: restoring datafile 00004 to +TEST_DATA
channel ORA_DISK_1: restoring datafile 00005 to +TEST_DATA
channel ORA_DISK_1: restoring datafile 00006 to +TEST_DATA
channel ORA_DISK_1: restoring datafile 00007 to +TEST_DATA
channel ORA_DISK_1: restoring datafile 00008 to +TEST_DATA
channel ORA_DISK_1: restoring datafile 00009 to +TEST_DATA
channel ORA_DISK_1: restoring datafile 00010 to +TEST_DATA
channel ORA_DISK_1: restoring datafile 00012 to +TEST_DATA
channel ORA_DISK_1: reading from backup piece /backup/backup_recovery/DEV_TEST/dbf_45rhmk5i_1_1
channel ORA_DISK_1: errors found reading piece handle=/backup/backup_recovery/DEV_TEST/dbf_45rhmk5i_1_1
channel ORA_DISK_1: failover to piece handle=/backup/OSS_INSTALL/BACKUP/DEV_TEST/dbf_45rhmk5i_1_1 tag=FULL_BK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 07-OCT-16

datafile 1 switched to datafile copy
input datafile copy RECID=14 STAMP=924619606 file name=+TEST_DATA/TEST/datafile/system.273.924619581
datafile 2 switched to datafile copy
input datafile copy RECID=15 STAMP=924619606 file name=+TEST_DATA/TEST/datafile/sysaux.275.924619581
datafile 3 switched to datafile copy
input datafile copy RECID=16 STAMP=924619606 file name=+TEST_DATA/TEST/datafile/undotbs1.272.924619581
datafile 4 switched to datafile copy
input datafile copy RECID=17 STAMP=924619606 file name=+TEST_DATA/TEST/datafile/users.269.924619585
datafile 5 switched to datafile copy
input datafile copy RECID=18 STAMP=924619606 file name=+TEST_DATA/TEST/datafile/hsddev02sb_data.271.924619581
datafile 6 switched to datafile copy
input datafile copy RECID=19 STAMP=924619606 file name=+TEST_DATA/TEST/datafile/hsddev03sb.268.924619585
datafile 7 switched to datafile copy
input datafile copy RECID=20 STAMP=924619606 file name=+TEST_DATA/TEST/datafile/oksbprodsb_data.267.924619585
datafile 8 switched to datafile copy
input datafile copy RECID=21 STAMP=924619606 file name=+TEST_DATA/TEST/datafile/sdsbprodsb_data.276.924619581
datafile 9 switched to datafile copy
input datafile copy RECID=22 STAMP=924619606 file name=+TEST_DATA/TEST/datafile/hsddev01sb_data.270.924619581
datafile 10 switched to datafile copy
input datafile copy RECID=23 STAMP=924619606 file name=+TEST_DATA/TEST/datafile/sbhsd_data.277.924619581
datafile 12 switched to datafile copy
input datafile copy RECID=24 STAMP=924619606 file name=+TEST_DATA/TEST/datafile/devwo_data.274.924619581

Starting recover at 07-OCT-16
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2594
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2595
channel ORA_DISK_1: reading from backup piece /backup/OSS_INSTALL/BACKUP/DEV_TEST/arch_48rhmkco_1_1
channel ORA_DISK_1: piece handle=/backup/OSS_INSTALL/BACKUP/DEV_TEST/arch_48rhmkco_1_1 tag=ARCHIVELOG_BK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archived log file name=+TEST_DATA/TEST/archivelog/2016_10_07/thread_1_seq_2594.266.924619611 thread=1 sequence=2594
archived log file name=+TEST_DATA/TEST/archivelog/2016_10_07/thread_1_seq_2595.265.924619611 thread=1 sequence=2595
unable to find archived log
archived log thread=1 sequence=2596
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/07/2016 14:46:51
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 2596 and starting SCN of 111698778



Applied the archive log files till sequence# 2595




Step 10:- Rename the old online redo logs 


select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u02/oradata/TEST/onlinelog/redo01.log
/u02/oradata/TEST/onlinelog/redo02.log
/u02/oradata/TEST/onlinelog/redo03.log


6 rows selected.

select group#,thread#,status from v$log;

SQL> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 INACTIVE
         2          1 INACTIVE
         3          1 CURRENT

6 rows selected.


-- Rename Old 1,2,3 redo log files


alter database rename file '/u02/oradata/TEST/onlinelog/redo01.log' to '+TEST_DATA/TEST/onlinelog/redo01.log';
alter database rename file '/u02/oradata/TEST/onlinelog/redo02.log' to '+TEST_DATA/TEST/onlinelog/redo02.log';
alter database rename file '/u02/oradata/TEST/onlinelog/redo03.log' to '+TEST_DATA/TEST/onlinelog/redo03.log';


--------
output:-
--------
SQL> alter database rename file '/u02/oradata/TEST/onlinelog/redo01.log' to '+TEST_DATA/TEST/onlinelog/redo01.log';

Database altered.

SQL> alter database rename file '/u02/oradata/TEST/onlinelog/redo02.log' to '+TEST_DATA/TEST/onlinelog/redo02.log';

Database altered.

SQL> alter database rename file '/u02/oradata/TEST/onlinelog/redo03.log' to '+TEST_DATA/TEST/onlinelog/redo03.log';

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+TEST_DATA/TEST/onlinelog/redo03.log
+TEST_DATA/TEST/onlinelog/redo02.log
+TEST_DATA/TEST/onlinelog/redo01.log


6 rows selected.




Step 11:- Open the database with reset logs 


SQL>  alter database open resetlogs;

Database altered.




Step 12:- Drop old redolog groups 


SQL> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 UNUSED
         3          1 UNUSED


6 rows selected.




-- Follow the below steps to drop 1st group which is current

SQL> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 UNUSED
         3          1 UNUSED

SQL> alter system switch logfile;

System altered.


SQL> alter system switch logfile;

System altered.


SQL> alter system switch logfile;

System altered.
 


SQL>  select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 CURRENT
         3          1 UNUSED



SQL> alter database drop logfile group 1;

Database altered.

SQL>  select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 UNUSED
         3          1 UNUSED



Step 13:-  Create the temp file


-- Check if Temp is there or not


SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
+TEST_DATA/TEST/tempfile/temp.258.924620691



-- No need to create the Temp file in this case.



Step 14:-   Create the spfile in ASM disk.  


We could do this step at the beginning too.  However, i do this step at the end for my convenience.



SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL>

SQL> create spfile='+TEST_DATA/TEST/PARAMETERFILE/spfileTEST.ora' from PFILE;

File created.

shutdown immediate

-- Here i updated the parameter file to point to SPFILE in ASM disk.

cat initTEST.ora

SPFILE='+TEST_DATA/TEST/PARAMETERFILE/spfileTEST.ora'


startup




Step 15:   Verify all the files and make sure all files are moved to ASM disk. 


Also watch out the alert log for a while. Create the listener and make sure able to connect outside the database server.



col HOST_NAME for a30
select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- ------------------------------
TEST           dukeortd70.corp.cox.com




select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production



set pages 10000
select name from v$datafile
union
select name from v$controlfile
union
select member from v$logfile
union
select name from v$tempfile;



NAME
--------------------------------------------------------------------------------
+TEST_DATA/TEST/controlfile/current.278.924618987
+TEST_DATA/TEST/datafile/devwo_data.274.924619581
+TEST_DATA/TEST/datafile/hsddev01sb_data.270.924619581
+TEST_DATA/TEST/datafile/hsddev02sb_data.271.924619581
+TEST_DATA/TEST/datafile/hsddev03sb.268.924619585
+TEST_DATA/TEST/datafile/oksbprodsb_data.267.924619585
+TEST_DATA/TEST/datafile/sbhsd_data.277.924619581
+TEST_DATA/TEST/datafile/sdsbprodsb_data.276.924619581
+TEST_DATA/TEST/datafile/sysaux.275.924619581
+TEST_DATA/TEST/datafile/system.273.924619581
+TEST_DATA/TEST/datafile/undotbs1.272.924619581
+TEST_DATA/TEST/datafile/users.269.924619585
+TEST_DATA/TEST/onlinelog/group_4.264.924620193
+TEST_DATA/TEST/onlinelog/group_5.263.924620201
+TEST_DATA/TEST/onlinelog/group_6.262.924620213
+TEST_DATA/TEST/tempfile/temp.258.924620691

16 rows selected.



No comments:

Post a Comment