Jairo SuarezHow 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
