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=<some other 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=<same as on source DB dbcp14> format=12
Note the password file name is orapw<SID>, not orapwd<SID>.

$ 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/<password>@dbcp14 auxiliary sys/<password>@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=<some big number> scope=spfile;
alter system set sga_target=<the same as sga_max_size so SGA is allocated up front> scope=spfile;
alter system set open_cursors=1000; <-- or higher
alter system set pga_aggregate_target=<at least 10000m>;
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=<the above value>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<SID>.ora or spfile<SID>.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