Jairo Suarez
- Feb 23, 2020
- 3 min read

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';

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