Database Restore In Different Server With Different File Location

 

##########################################################################

RMAN Backup Restore on Different Server with Different file Location

##########################################################################

 BACKUP SCRIPT:

#!/bin/bash

export ORACLE_SID=t1sipmdm

export ORACLE_HOME=/u01/oracle/product/12.1.0/db_1

export PATH=$PATH:$ORACLE_HOME/bin:$PATH

cd /exp_backup/rman_28sep2024

mkdir  `date +%d%b%Y`

export todaydir=`date +%d%b%Y`

export timestamp=`date +%d%b%Y_%H%M%S`

export logfile=/exp_backup/rman_28sep2024/level0_$timestamp.out

export backup_location=/exp_backup/rman_28sep2024/$todaydir

rman target / nocatalog <<EOF

spool log to '$logfile' append

run

{

allocate channel ch1 type disk;

allocate channel ch2 type disk;

crosscheck archivelog all;

BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 FORMAT "$backup_location/${ORACLE_SID}_l0_%U_%T" DATABASE PLUS ARCHIVELOG;

BACKUP CURRENT CONTROLFILE FORMAT "$backup_location/controlfile_%t_dbid_%I.ctl";

release channel ch1;

}

spool log off;

exit;

EOF

***********************************************************************************

Part -1 : With SET NEWNAME

 

Old data/ctl location: /u02/testdb

New data/ctl Location: /data/oradata

old archive location : /u01/app/oracle/arch

new Archive location : /data/arch

 

We Need Below RMAN Backups:

 

a. DB Backup

b. Archive Backup

c. Spfile and CTL file

 

Start Dummy DB from RMAN

--------------------------

 

startup nomount force;

 

Restore Spfile

-------------------

 

restore spfile from '/data/fra/spfile_431leuk3_1_1';

 

Verify Some Important Parameters and Locations

-----------------------------------------------

 

shut abort;

startup nomount;

sho parameter audit_file_dest

sho parameter control_files                --- Modify it in case Diff location

sho parameter log_archive_dest_                  --- Modify it in case DR setup

sho parameter log_archive_dest_1    --- Modify in Case diff location

sho parameter convert

sho parameter db_create_file_dest

 

Update Locations and Relevant Parameters

--------------------------------------------

 

alter system set log_archive_dest_state_2=defer;

alter system set log_archive_dest_1='LOCATION=/data/arch';

alter system

set control_files='/data/oradata/control01.ctl','/data/oradata/control02.ctl'

scope=spfile;

########### OR##########

1) Create the pfile from spfile on SOURCE database & copy to target database.

2) Modify the location of control file , archive log path, audit file path, DB name as per new database .

Bounce DB and Restore CTL

---------------------------

 

shut immediate;

startup nomount;

 

restore controlfile from '/data/fra/ctl_451leuk6_1_1';

alter database mount;

 

Check Old File Locations

----------------------------

 

report schema;

 

Restore RMAN Backup

---------------------

 

catalog backuppiece '/data/fra/DB_BKP_3v1leuh3_1_1';

catalog backuppiece '/data/fra/Arc_BKP_411leujv_1_1';

 

crosscheck backup;

delete noprompt expired backup;

 

a. Generate new file location scritp

 

set echo off pages 0 feed off

spool /home/oracle/file.log;

select 'set newname for datafile '||file#||' to '''||name||''';'

from v$datafile

union all

select 'set newname for tempfile '||file#||' to '''||name||''';'

from v$tempfile;

spool off;

spool /home/oracle/switchfile.log;

select 'switch datafile '||file#||' ;'

from v$datafile

union all

select 'switch tempfile '||file#||' ;'

from v$tempfile;

spool off;

 

cat /home/oracle/file.log

cat /home/oracle/switchfile.log

 

c. Modify File with new location

 

vi /home/oracle/file.log

 

:%s'/u02/testdb'/data/oradata

 b.Create RMAN Sript

 run

{

ALLOCATE CHANNEL C1 TYPE DISK;

ALLOCATE CHANNEL C2 TYPE DISK;

set newname for datafile 1 to '/data/oradata/system01.dbf';---New datafile location

set newname for datafile 3 to '/data/oradata/sysaux01.dbf';

set newname for datafile 4 to '/data/oradata/undotbs01.dbf';

set newname for datafile 7 to '/data/oradata/users01.dbf';

set newname for tempfile 1 to '/data/oradata/temp01.dbf';

set until scn = 16573413867832;

restore database;

switch datafile 1 ;

switch datafile 3 ;

switch datafile 4 ;

switch datafile 7 ;

switch tempfile 1 ;

recover database;

}

 

Confirm TEMPFile Location and Update if needed

-------------------------------------------------

 

select name from v$tempfile;

 

select 'alter database rename file '''||name||

''' to '||'''/data/oradata/'||substr(name,15,20)||''';' from v$tempfile;

 

alter database rename file '/u02/tempdb/temp01.dbf' to '/data/oradata/temp01.dbf';

 

Update logfile locations

---------------------------

 

select member from v$logfile;

 

alter database drop standby logfile group 11;

alter database drop standby logfile group 12;

alter database drop standby logfile group 13;

 

select 'alter database rename file '''||member||

''' to '||'''/data/oradata/'||substr(member,15,20)||''';' from v$logfile;

 

or

 

select 'alter database rename file ''' ||member|| '''

to

'||'''/data/oradata/'|| substr(member,instr(member,'/',-1)+1,

instr(substr(member,instr(member,'/',-1)+1),'.')-1 ) ||'.log'' ;'

file_name

from v$logfile;

 

select member from v$logfile;

 

 

Open DB

---------------

 

alter database open resetlogs;

 

 

Confirm DB and Datafiles Status

-----------------------------------

 

select name,open_mode from v$database;

select name from v$datafile;

select name from v$tempfile;

select member from v$logfile;

 

Done !!!!