How to duplicate 18c database to a different server (including note on standby creation) Example in this note: Source DB (2-node RAC): db_name and db_unique_name dbcp14; running on nodes myservera,b Target or Destination DB (2-node RAC): if cloning, db_name and db_unique_name dbcp14cl; if standby, db_name is dbcp14 and db_unique_name dbcp14cl ("cl" here means "clone"); running on nodes myserverc,d On myserverc: $ vi $DBHOME/network/admin/sqlnet.ora NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP, EZCONNECT) $ vi $DBHOME/network/admin/tnsnames.ora dbcp14 = (DESCRIPTION = (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myservera)(PORT=1521))) (CONNECT_DATA=(ORACLE_SID=dbcp141)) ) dbcp14n = (DESCRIPTION = (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myserverc)(PORT=1521))) (CONNECT_DATA=(ORACLE_SID=dbcp141)) ) Note: Here dbcp14n is TNS connect identifier for standby, intentionally made different from its db_name or db_unique_name in this document so as to make some parameter values easier to understand (e.g. log_archive_dest_2). But it's perfectly OK to use the same string. $GIHOME/network/admin also needs those 2 files. So copy them there. (Ref: Doc 1982003.1) The paragraph for dbcp14n should also be added to Source (myservera,b) $DBHOME/network/admin/tnsnames.ora (but no need in Source $GIHOME/network/admin/tnsnames.ora). Still on Target myserverc: $ vi $DBHOME/dbs/initdbcp141.ora db_domain=... <-- only if the source or primary DB has set it db_name=dbcp14cl <-- dbcp14 if creating standby (i.e. must be the same as on primary), dbcp14cl if cloning db_unique_name= <-- must set if creating standby, optional if cloning cluster_database=false db_create_file_dest='+DATA' <-- if primary has it but datafiles are actually scattered in more than 1 diskgroup, do not set it, at least for now db_create_online_log_dest_1='+FRA' <-- same comment as above db_block_size=16384 <-- dbcp14 uses 16k db_block_size; so must specify it to avoid ORA-00058; default would be 8k db_files=1000 <-- default would be only 200 enable_pluggable_database=true <-- DB of multitenant architecture needs this to avoid ORA-65093 sga_max_size=10240m <-- default too small, need to set it to a bigger value to avoid ORA-4031 sga_target=10240m open_cursors=1000 dbcp141.instance_number=1 <-- not needed now, but OK to set now; if Target is single-node, not needed at all dbcp142.instance_number=2 <-- same dbcp141.undo_tablespace='UNDOTBS1' <-- same dbcp142.undo_tablespace='UNDOTBS2' <-- same Prepare /etc/oratab. Set local instance to dbcp141, and startup nomount: $ . oraenv dbcp141 $ sqlplus / as sysdba SQL> startup nomount If need to bounce instance, remember to rm auto created spfile. (If need to change any parameter and bounce instance, remember to remove auto-created spfile and bounce instance.) Append to $GIHOME/network/admin/listener.ora: SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME=dbcp141) (ORACLE_HOME=/u01/app/oracle/product/18.0.0/db) (GLOBAL_DBNAME=dbcp14cl) ) ) Note: GLOBAL_DBNAME should be db_unique_name if it's different from db_name as in the case of creating standby. $ srvctl stop listener $ srvctl start listener $ lsnrctl service <-- should show dbcp14cl, which matches GLOBAL_DBNAME $ cd $DBHOME/dbs $ orapwd file=orapwdbcp141 password= format=12 Note the password file name is orapw, not orapwd. $ sqlplus sys@dbcp14 as sysdba SQL> select host_name from v$instance; -- should show myservera $ sqlplus sys@dbcp14n as sysdba SQL> select host_name from v$instance; -- should show myserverc $ tmux #optional; use in case losing Putty connection The following RMAN command is for cloning, not for standby creation. You can add more channels if you wish. $ rman target sys/@dbcp14 auxiliary sys/@dbcp14n run { allocate channel c1 device type disk; allocate channel c2 device type disk; allocate auxiliary channel a1 device type disk; allocate auxiliary channel a2 device type disk; duplicate target database to dbcp14cl from active database; } If you get error RMAN-05520 (database name mismatch, auxiliary instance has DBCP14, command specified DBCP14CL), it's because RMAN has created a spfile from your pfile and changed db_name in it from dbcp14cl to dbcp14 (the same as on Source). Delete the spfile, shutdown instance, startup nomount and try again. Note for standby creation: If you're creating a standby database, init.ora must set db_name to the same as the db_name of primary and set db_unique_name to some other name. The duplicate command is not the one shown above, but instead: RMAN> duplicate target database for standby from active database; The connect string dbcp14n must be added to the tnsnames.ora on Source i.e. primary side (in addition to this standby). And of course you need to do the following sooner or later: alter system set log_archive_config='dg_config=(dbcp14,dbcp14cl)'; <-- on both primary and standby alter system set log_archive_dest_2='service=dbcp14n async valid_for=(all_logfiles,primary_role) db_unique_name=dbcp14cl'; <-- on primary alter system set log_archive_dest_2='service=dbcp14 async valid_for=(all_logfiles,primary_role) db_unique_name=dbcp14'; <-- on standby alter system set standby_file_management=auto; <-- on primary and standby alter system set db_recovery_file_dest_size=5242860m; <-- on standby, e.g. value from v$asm_diskgroup where name='FRA' alter system set db_recovery_file_dest='+FRA'; <-- on standby alter system set fal_client='dbcp14'; <-- on primary alter system set fal_server='dbcp14n'; <-- on primary alter system set fal_client='dbcp14n'; <-- on standby alter system set fal_server='dbcp14'; <-- on standby alter database add standby logfile thread 1 size 1073741824; <-- on standby, recovery must be cancelled to do this alter database add standby logfile thread 2 size 1073741824; <-- on standby, recovery must be cancelled to do this ... alter database recover managed standby database disconnect; <-- on standby It takes only 30+ minutes to clone this 1.3 TB DB. Once done, an spfile spfiledbcp141.ora should have been auto-generated. Modify it this way: $ sqlplus / as sysdba show parameter spfile <-- make sure it's using spfile $DBHOME/dbs/spfiledbcp141.ora alter system set cluster_database=true scope=spfile; alter system reset instance_number; alter system set instance_number=1 scope=spfile sid='dbcp141'; alter system set instance_number=2 scope=spfile sid='dbcp142'; alter system set sga_max_size= scope=spfile; alter system set sga_target= scope=spfile; alter system set open_cursors=1000; <-- or higher alter system set pga_aggregate_target=; Run in the source DB: SQL> col name for a40 SQL> col value for a80 SQL> select name, value from v$parameter where isdefault='FALSE' order by 1; According to the result, set other params in the cloned DB (in CDB if multitenant architecture). For example, dbcp14 has these params: alter system set allow_global_dblinks=true; alter system set db_cache_advice=off; alter system set undo_retention=7200; alter system set open_cursors=2000; select total_mb from v$asm_diskgroup where name='FRA'; alter system set db_recovery_file_dest_size=M; alter system set db_recovery_file_dest='+FRA'; <-- Otherwise, archive logs would be in ?/dbs. /* The auto-generated spfile will have lots of undocumented parameters added by cloning. Remove them with the result of: SQL> select 'alter system reset "'||name||'";' from v$parameter where isdefault='FALSE' and name like '\_%' escape '\' order by 1; */ create pfile='/tmp/init.ora' from spfile='/u01/app/oracle/product/18.0.0/db/dbs/spfiledbcp141.ora'; <-- path from show parameter spfile The auto-generated spfile, and so /tmp/init.ora, will have lots of undocumented parameters added by cloning. Remove them. If some undocumented params are indeed needed according to the source DB, make sure they are set in the cloned DB. Move spfile into ASM: SQL> create spfile='+DATA' from pfile='/tmp/init.ora'; Make sure $DB_HOME/dbs has no initdbcp141.ora nor spfiledbcp141.ora. You can rename them instead of delete. Find spfile in ASM with asmcmd and specify its full path in the following command (for standby, also specify -role PHYSICAL_STANDBY): $ srvctl add database -db dbcp14cl -oraclehome /u01/app/oracle/product/18.0.0/db -dbtype RAC -spfile '+DATA/DBCP14CL/PARAMETERFILE/spfile.318.1053098315' $ srvctl add instance -db dbcp14cl -instance dbcp141 -node myserverc $ srvctl add instance -db dbcp14cl -instance dbcp142 -node myserverd $ srvctl config database -db dbcp14cl $ srvctl add service -db dbcp14cl -service mdap -pdb oracp14 -preferred dbcp141,dbcp142 -failovertype SESSION -failovermethod BASIC -rlbgoal SERVICE_TIME -failoverretry 3 -failoverdelay 1 Make sure there's no init.ora or spfile.ora in $DBHOME/dbs on both nodes. The following command will start the service, and if instance is down, also the instance: $ srvctl start service -db dbcp14cl -service mdap Bounce database $ srvctl stop database -db dbcp14cl #if this doesn't work, use sqlplus because DB status is not known to OCR yet $ srvctl start database -db dbcp14cl SQL> sho parameter listener If not set: SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.70.231.53)(PORT=1521))' sid='dbcp141'; SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.70.231.54)(PORT=1521))' sid='dbcp142'; SQL> alter system set remote_listener='myservercdSCAN.example.com:1521'; SQL> sho parameter service SQL> select inst_id, con_id, name from gv$active_services order by 1,2; Add PDB connect identifiers to client side tnsnames.ora (oracp14cl1,2 are needed to connect to a specific instance e.g. when doing data pump): oracp14cl=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myservercdSCAN.example.com)(PORT=1521))(CONNECT_DATA=(service_name=mdap))) optionally: oracp14cl1=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserverc.example.com)(PORT=1521))(CONNECT_DATA=(service_name=oracp14))) oracp14cl2=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserverd.example.com)(PORT=1521))(CONNECT_DATA=(service_name=oracp14))) If needed, adjust SGA, set HugePages (including vi /etc/sysctl.conf and /etc/security/limits.conf) and bounce instances. If there's only 1 controlfile, follow Doc 2006213.1 to add another one. Add 1 log group and some log members if needed (the numbers below are for this DB only, after checking v$log and v$logfile): SQL> alter database add logfile thread 1 group 5 ('+DATA','+FRA') size 1073741824; SQL> alter database add logfile thread 2 group 6 ('+DATA','+FRA') size 1073741824; SQL> alter database add logfile member '+DATA' to group 1; SQL> alter database add logfile member '+DATA' to group 2; SQL> alter database add logfile member '+DATA' to group 3; SQL> alter database add logfile member '+DATA' to group 4; Run SQL> alter system switch logfile; a few times (maybe on the other instance) so SQL> select * from v$log; SQL> select group#, status from v$logfile; does not show 'UNUSED' log group or 'INVALID' group member. Note for standby creation: For a standby: SQL> alter database recover managed standby database disconnect; With active data guard license: SQL> alter database open read only; SQL> alter database recover managed standby database disconnect; Ref: https://blog.pythian.com/duplicate-from-active-database-using-rman-step-by-step/ https://oracle-base.com/articles/11g/duplicate-database-using-rman-11gr2 https://www.thegeekdiary.com/rman-duplicate-from-active-database-feature-in-oracle-11g/ tmux notes: To scroll up, ^b [ UP or PageUp. q to quit. To list tmux sessions: tmux ls (or list-sessions) To attach to session 0: tmux attach-session 0