Search

How to duplicate ASM Database to Non-ASM Filesystem

Duplicating a Database - Advanced


Situation

We need to clone an ASM Database from PROD environment and put it into the new server with filesystem or Non-ASM. To do that is necessary to take a new backup from the current controlfile and then proceed with the restore.


1. Take a new database backup from original database.

run {

allocate channel disk1 device type disk;

backup format '/fs/common/exp_imp/EM/df_t%t_s%s_p%p' database;

backup current controlfile format '/fs/common/exp_imp/EM/ctrfile.%U';

sql 'alter system switch logfile';

sql 'alter system switch logfile';

sql 'alter system switch logfile';

backup format '/fs/common/exp_imp/EM/arch_t%t_s%s_p%p' archivelog all;

release channel disk1;

}


Info:

One more switch than we have logfiles (we have 4 at the moment).

Check the output for the last archive log backed up. Something like:

input archive log thread=1 sequence=153134 recid=153115 stamp=787320461


Open a SQL*Plus session as sysdba

sqlplus / as sysdba

column next_change# format 999999999999

select next_change# from v$archived_log where sequence# = 153134


We are going the sequence number later to recover the database until that moment.


2. Meanwhile, the backup is finished, go to the machine when you are cloning the database, we assume that you are installing the Oracle binaries

Create a new password file with the same password of the original database.Create a new and simple pfile.ora file and with the following parameter *.db_name='EMREP'


3. Configure tnsnames file on the new machine when the database is going to be cloned.

To get the connection with the original database.


EM_PRD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = server-dbora-p01)(PORT = 1525))

(ADDRESS = (PROTOCOL = TCP)(HOST = server-dbora-p02)(PORT = 1525))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = EM_HA.PRD)

(FAILOVER_MODE =

(TYPE = SELECT)

(METHOD = BASIC)

(RETRIES = 180)

(DELAY = 5))))


New database connections string


EM_GRIDP03.PRD =

(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server-grid-p03)(PORT = 1525)))

(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = EM_GRIDP03.PRD))

)



4. Continuing into the new machine please, connect via RMAN and run the following script this script.

connect target sys/******@EM_ORAP02.PRD -- Original database

connect auxiliary sys/***** -- Clone database connection as an auxiliary, remember to use the same sys password as original


run

{

set newname for datafile  1 to '/data/emrep/oradata/EMREP/datafile/system.283.903890003';

set newname for datafile  2 to '/data/emrep/oradata/EMREP/datafile/sysaux.278.903889977';

set newname for datafile  3 to '/data/emrep/oradata/EMREP/datafile/undotbs1.282.903890003';

set newname for datafile  4 to '/data/emrep/oradata/EMREP/datafile/users.285.903890027';

set newname for datafile  5 to '/data/emrep/oradata/EMREP/datafile/mgmt_ecm_depot_ts.281.903890003';

set newname for datafile  6 to '/data/emrep/oradata/EMREP/datafile/mgmt_tablespace.284.903890027';

set newname for datafile  7 to '/data/emrep/oradata/EMREP/datafile/mgmt_ad4j_ts.280.903889977';

set newname for datafile  8 to '/data/emrep/oradata/EMREP/datafile/rman_catalog.279.903889977';

set newname for datafile  9 to '/data/emrep/oradata/EMREP/datafile/mgmt_tablespace.499.916503681';

SET NEWNAME FOR TEMPFILE 1 TO '/data/emrep/oradata/EMREP/datafile/temp01.dbf';

set until scn 1532145828; ------------- SCN was taken with the script in the 1st Step.

DUPLICATE TARGET DATABASE TO EMREP

NOFILENAMECHECK

DB_FILE_NAME_CONVERT '+DATA','/data/emrep/oradata/EMREP/datafile'

LOGFILE

GROUP 1 ('/data/emrep/onlinelog/EM_GRIDP03/redo01a.log','/data/emrep/onlinelog/EM_GRIDP03/redo01b.log') SIZE 600M REUSE,

GROUP 2 ('/data/emrep/onlinelog/EM_GRIDP03/redo02a.log','/data/emrep/onlinelog/EM_GRIDP03/redo02b.log') SIZE 600M REUSE

SPFILE

SET LOG_FILE_NAME_CONVERT '+FRA/','/data/emrep/onlinelog', '+DATA','/data/emrep/onlinelog'

SET AUDIT_FILE_DEST '/app/oracle/admin/EMREP/adump'

SET CONTROL_FILES '/data/emrep/oradata/EMREP/controlfile/control01.ctl'

SET DB_RECOVERY_FILE_DEST '/data/emrep/FRA'

SET DB_CREATE_FILE_DEST '/data/emrep/oradata/EMREP/datafile/'

SET DIAGNOSTIC_DEST '/app/oracle/admin';

}


Final results after some minutes of replication....


executing command: SET NEWNAME

renamed tempfile 1 to /data/emrep/oradata/EMREP/datafile/temp01.dbf in control file

cataloged datafile copy

datafile copy file name=/data/emrep/oradata/EMREP/datafile/sysaux.278.903889977 RECID=1 STAMP=940432070

cataloged datafile copy

datafile copy file name=/data/emrep/oradata/EMREP/datafile/undotbs1.282.903890003 RECID=2 STAMP=940432070

cataloged datafile copy

datafile copy file name=/data/emrep/oradata/EMREP/datafile/users.285.903890027 RECID=3 STAMP=940432070

cataloged datafile copy

datafile copy file name=/data/emrep/oradata/EMREP/datafile/mgmt_ecm_depot_ts.281.903890003 RECID=4 STAMP=940432070

cataloged datafile copy

datafile copy file name=/data/emrep/oradata/EMREP/datafile/mgmt_tablespace.284.903890027 RECID=5 STAMP=940432070

cataloged datafile copy

datafile copy file name=/data/emrep/oradata/EMREP/datafile/mgmt_ad4j_ts.280.903889977 RECID=6 STAMP=940432070

cataloged datafile copy

datafile copy file name=/data/emrep/oradata/EMREP/datafile/rman_catalog.279.903889977 RECID=7 STAMP=940432070

cataloged datafile copy

datafile copy file name=/data/emrep/oradata/EMREP/datafile/mgmt_tablespace.499.916503681 RECID=8 STAMP=940432070

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=940432070 file name=/data/emrep/oradata/EMREP/datafile/sysaux.278.903889977

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=940432070 file name=/data/emrep/oradata/EMREP/datafile/undotbs1.282.903890003

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=940432070 file name=/data/emrep/oradata/EMREP/datafile/users.285.903890027

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=940432070 file name=/data/emrep/oradata/EMREP/datafile/mgmt_ecm_depot_ts.281.903890003

datafile 6 switched to datafile copy

input datafile copy RECID=5 STAMP=940432070 file name=/data/emrep/oradata/EMREP/datafile/mgmt_tablespace.284.903890027

datafile 7 switched to datafile copy

input datafile copy RECID=6 STAMP=940432070 file name=/data/emrep/oradata/EMREP/datafile/mgmt_ad4j_ts.280.903889977

datafile 8 switched to datafile copy

input datafile copy RECID=7 STAMP=940432070 file name=/data/emrep/oradata/EMREP/datafile/rman_catalog.279.903889977

datafile 9 switched to datafile copy

input datafile copy RECID=8 STAMP=940432070 file name=/data/emrep/oradata/EMREP/datafile/mgmt_tablespace.499.916503681

Reenabling controlfile options for auxiliary database

Executing: alter database force logging

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Executing: alter database flashback on

Finished Duplicate Db at 04-APR-17



RMAN>

Verify that the database is open, the listener is up and running.



Cheers;


Jairo Suarez