Cloning Oracle E-Business Suite R12.2 using 19c database Single PDB (Non-RAC)

1 Prepare Source Oracle E-Business Suite Instance for Cloning
1.1 Prepare the Source System Database Tier
Execute the following steps to prepare the source system database tier for cloning. Ensure all processes in the source system are up before proceeding:
As the oracle user, log on to the source system and source the database tier environment file.
$ cd /data/DEV/db/tech_st/19.0.0
$ ls -lrt *.env
-rw-r--r-- 1 oradev oinstall 4572 Feb 11 13:16 FRESH_ebsuat01.env
-rw-r--r-- 1 oradev oinstall 1787 Feb 11 13:16 ebsfresh_ebsuat01.env
$ . ./FRESH_ebsuat01.env
Execute the following commands:
$ cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME
$ perl adpreclone.pl dbTier
Create an archive of the source system Oracle home on the primary node:
$ cd $ORACLE_HOME/..
$ tar -cvzf 19db_oh.tgz 19.0.0
Note: Consider using data integrity utilities such as md5sum, sha1sum, or cksum to validate the file sum both before and after transfer to the target system.

1.2 Prepare the Source System Application Tier

As the applmgr user, log on to the primary node of the source system.

Source the environment file of the run edition file system.

You can use the following command to confirm that the environment variable FILE_EDITION points to the run edition file system:
$ echo $FILE_EDITION

Running this command should return the value:
run

Execute the following commands:
$ cd $INST_TOP/admin/scripts
$ perl adpreclone.pl appsTier
$ cd $RUN_BASE
$ tar -zcvf EBSapps.tar.gz EBSapps

Note: Consider using data integrity utilities such as md5sum, sha1sum, or cksum to validate the file sum both before and after transfer to the target system.

2 Prepare the target environment

2.1 Pre-requisites
Ensure that you have applied all prerequisites of O/S such as RPM, network and storage configuration etc.:
# yum install oracle-ebs-server-R12-preinstall –y
# yum install oracle-database-preinstall-19c

2.2 Prepare the database binaries

Copy the ORACLE_HOME archive from the source system and untar it. Choose a suitable location, and rename the extracted top-level directory name to something meaningful on the new target system.

# mkdir –p /u01/app/oraInventory
# mkdir –p /u01/app/DEV/db/tech_st
# mkdir –p /u01/app/DEV/db/apps_st
# mkdir –p /u01/app/DEV/apps/fs1
# mkdir –p /u01/app/DEV/apps/fs2
# mkdir –p /u01/app/DEV/apps/fs_ne
# chown –R oracle:oinstall /u01/app/DEV/db
# chown –R oracle:oinstall /u01/app/oraInventory
# chown –R applmgr:oinstall /u01/app/DEV/apps
# chmod –R 775 /u01/app

Note: Also ensure you have assigned oper group for oracle database user.  Create /etc/oraInst.loc file with below entries:
inventory_loc=/u01/app/oraInventory
inst_group=oinstall

$ cd /u01/app/DEV/db/tech_st
$ tar -xvzf 19db_oh.tgz

Create and load an environment file with below variables:
export ORACLE_BASE=/u01/app/DEV/db
export ORACLE_HOME=/u01/app/DEV/db/tech_st/19.0.0
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$ORACLE_HOME/OPatch:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PERL5LIB=/u01/app/DEV/db/tech_st/19.0.0/perl/lib/5.28.1:/u01/app/DEV/db/tech_st/19.0.0/perl/lib/site_perl/5.28.1:/u01/app/DEV/db/tech_st/19.0.0/perl/lib
export ORA_NLS10=/u01/app/DEV/db/tech_st/19.0.0/nls/data/9idata
export ORACLE_SID=ebsdev
export ORACLE_PDB_SID=DEV

2.3 Create pairsfile.txt File
Create a <NEW_ORACLE_HOME>/appsutil/clone/pairsfile.txt text file.

For single-node database, if you want to generate the context file non-interactively, use the following content as shown:
s_undo_tablespace=<Source (PDB) system undo tablespace name>
s_db_oh=<Location of new ORACLE_HOME>
s_dbhost=<Target hostname>
s_dbSid=<Target PDB name>
s_pdb_name=<Target PDB name>
s_cdb_name=<Target CDB SID>
s_base=<Base directory for DB Oracle Home>
s_dbuser=<DB User>
s_dbgroup=<DB group>
s_dbhome1=<Data directory>
s_display=<Display>
s_dbCluster=false
s_isDBCluster=n
s_dbport=<DB port>
s_port_pool=<Port pool number>

Note: Check the number of data directories in source context file and provide all here (s_dbhome2, s_dbhome3, etc.) with correct/updated location for the target instance.

2.4 Create Context File
You will now execute the adclonectx.pl utility to create a new target context file, responding to the prompts as applicable.

Create the Context File for a Single-Node Database
Execute the following commands when the target database is a single-node database being cloned from a single-node source database.

$ cd <NEW ORACLE_HOME>/appsutil/clone/bin
$ perl adclonectx.pl \
contextfile=<Source database context file>\
template=<NEW ORACLE_HOME>/appsutil/template/adxdbctx.tmp \
[pairsfile=<Pairs file generated in Section 4.1.2>]

2.5 Configure the Database Technology Stack
Configure the database technology stack copied by executing the following steps:

Navigate to <ORACLE_HOME>/appsutil/clone/bin and run Rapid Clone (adcfgclone.pl utility) with the following parameters to configure the database technology stack:
$ perl adcfgclone.pl dbTechStack <Complete path to the target context file>

Note: The above command will also create the <s_outbound_dir> directory. Hence, ensure that the value of the <s_outbound_dir> is set correctly before executing the above command.

If there is any issue with the directory creation in the above step, review the issue to create the directory manually or to change the <s_outbound_dir> context variable value as required.

2.6 Create the listener.ora and tnsnames.ora for the Target Database
Create the listener.ora and tnsnames.ora files for the target CDB by executing the following commands:
$ cd <ORACLE_HOME>/appsutil
$ source ./txkSetCfgCDB.env -dboraclehome=<ORACLE_HOME>
$ cd <ORACLE_HOME>/appsutil/bin
$ perl txkGenCDBTnsAdmin.pl -dboraclehome=<ORACLE_HOME> -cdbname=<Name of the target container database> \
-cdbsid=<SID of the target container database> -dbport=<Target DB port> -outdir=$ORACLE_HOME/appsutil/log \
-israc=<yes/no> [-virtualhostname=<virtual hostname>]
3 Clone the database
Oracle E-Business Suite support single PDB on 12c & 19c.  You have the below options to clone the database from 12.2 above and 19c.  Each has its own advantages.  I would recommend to use the RMAN clone for the first time preparing the clone and all other next instances you can use Hot PDB clone. In fact Hot PDB clone reduces the cloning time quite a lot if you use refreshable clone PDB and when needed you can open it in read write mode.

3.1 Using RMAN Clone

3.1.1 Prepare for rman clone
Copy the full backup of database from source to target server.  Make sure to set proper db_file_name_convert and log_file_name_convert parameters in pfile or spfile of target instance.

3.1.2 Duplicate the database using rman backup
Load the environment file and start the database in nomount stage. Now execute the below commands:
rman auxiliary /
run
{
allocate auxiliary channel a1 type disk;
allocate auxiliary channel a2 type disk;
allocate auxiliary channel a3 type disk;
duplicate database to “DEV” backup location ‘<Location where you copied the backup>’;
release channel a1;
release channel a2;
release channel a3;
}


3.1.3 Change the name of PDB (OPTIONAL)
If the PDB name needs to be changed, execute the following steps to rename the PDB.
$ export ORACLE_SID=<CDB SID>
$ sqlplus / as sysdba
SQL> alter pluggable database "<SOURCE PDB NAME>" close;
SQL> alter pluggable database "<SOURCE PDB NAME>" unplug into '<ORACLE_HOME>/dbs/<SOURCE PDB NAME>.xml';
SQL> drop pluggable database "<SOURCE PDB NAME>";
SQL> create pluggable database "<NEW PDB NAME>" using '<ORACLE_HOME>/dbs/<SOURCE PDB NAME>.xml' NOCOPY SERVICE_NAME_CONVERT=('ebs_<SOURCE PDB NAME>','ebs_<TARGET PDB NAME>','<SOURCE PDB NAME>_ebs_patch','<TARGET PDB NAME>_ebs_patch');
SQL> alter pluggable database "<TARGET PDB NAME>" open read write;


3.1.4 Startup the PDB and save its state
Startup the PDB and save its state by executing the following commands:

$ export ORACLE_SID=<CDB SID>
$ sqlplus / as sysdba
SQL> alter pluggable database all open; (in case of a single-node database)
SQL> alter pluggable database all save state instances=all;

3.1.5 Run the library update script against the Oracle database.
$ export ORACLE_SID=<CDB_SID>
$ cd <ORACLE_HOME>/appsutil/install/<CONTEXT_NAME>
$ sqlplus / as sysdba @adupdlib.sql <libext>
Where <libext> should be set to sl for HP-UX, so for any other UNIX platform, or dll for Windows.


3.2 Using Hot PDB Clone

3.2.1 Check Pre-Requisites for Hot-Clone

3.2.1.1 Check Undo tablespace and archivelog mode
The remote PDB or non-CDB must be in Local Undo Mode. If not, then it is not a hot-clone, that is, the operation described below can be performed by the source would need to be in read-only mode.
Check source and target DB local undo mode setting (local undo should be enabled and below query should return true)
SQL> SELECT property_name, property_value FROM database_properties WHERE property_name = ‘LOCAL_UNDO_ENABLED’;
The target (local) PDB must be at least 12.2.0.1, the source can be a non-CDB or PDB at least at 12.2.0.1
Source (Remote) DB needs to be in ARCHIVELOG mode. If not, then it is not a hot-clone, that is, the operation described below can be performed by the source would need to be in read-only mode.
Source and target database must have the same endianness.

3.2.1.2 Create a new user in source database for db link requirement
Create a new user in the remote (source) database that would be used for the process of the cloning.
sqlplus “/as sysdba”
SQL> alter session set container=<Source PDB Name>;
SQL> alter session set "_ORACLE_SCRIPT"=true;
SQL> CREATE USER remote_admin IDENTIFIED BY ***** CONTAINER=ALL;
SQL> GRANT CREATE SESSION, RESOURCE, SYSOPER, CREATE PLUGGABLE
DATABASE TO remote_admin CONTAINER=ALL;

Note: even if you grant SYSDBA, but it is not enough, still need SYSOPER.

 Create a TNS alias on target DB to point to source DB

3.2.1.3 Create CDB on target by using DBCA

On the database server node:
Run the Database Configuration Assistant (DBCA) to create the container database (CDB).
When prompted, click on the "Create Database", "Advanced Configuration", and "General Purpose or Transaction Processing" options.
In the Specify Database Identification screen, check to create an empty container database (CDB) without a PDB.
Set the Global Database Name, the SID to the new CDB SID, and check the "Use Local Undo tablespace for PDBs" checkbox. The CDB SID has to be different from the current ORACLE_SID, which will be the PDB SID.
In the "Network Configuration" section, do not create a listener. In the "Specify Configuration Options" section, set the SGA and PGA sizes to 2G and 1G respectively.
Click on the Character Sets tab and choose the Character Set and National Character Set to be the same as in the source database.
In the "Select Database Creation Option" section, click on the "Customize Storage Locations" button. Set the size of the redo log files to be the same as in the source database. Other options can be configured as appropriate.
3.2.1.4 Create database link to source DB
Create a database link on the target CDB, connecting to the common user on the remote database using the database alias created in the tnsnames.ora file.
$ sqlplus / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit
Production
SQL> CREATE DATABASE LINK to_source remote_admin IDENTIFIED BY *****
USING ‘Remote_DB_Alias’;
Database link created

Verify that you are able to connect to the Remote/Source Database from the Target Database.
SQL> select * from dual@to_source;

D
–
X

3.2.2 Creation of Refreshable Hot Clone

A hot clone can be taken while the source is still open read-write i.e without interrupting any operations in the source. A hot clone is known as a “fuzzy read” of all the blocks in the datafiles of the source.

We have two options to clone a hot PDB i.e.:
1. Refreshable Hot Clone
2. Non Refreshable Hot Clone
The cloning operation for production PDBs can take minutes to hours as mentioned above. If there is a need for frequent clones, then the process of larger database systems can be a long time-consuming activity even in the case of hot-clones.

A refreshable clone PDB solves the above problem. There are two types of Refreshable Cloned PDBs you can create:

Manual Refresh
Automatic Refresh (Every “x” minutes)
The REFRESH MODE clause is provided in the CREATE PLUGGABLE DATABASE … FROM statement which was used above:

REFRESH MODE NONE, the default, to create a PDB that is not refreshable – the process defined above was a non-refreshable database. The cloned database created cannot be converted into a refreshable database.
REFRESH MODE MANUAL – creates a refreshable PDB that must be refreshed manually.
REFRESH MODE EVERY “X” MINUTES – Creates a refreshable PDB that is refreshed automatically after “X” minutes has passed. A refreshable PDB that uses automatic refresh can also be refreshed manually.
A refreshable clone PDB must be in either of the following states:

Closed – A refreshable PDB must be closed when a refresh is performed. If not, the refresh is deferred until the next scheduled refresh.
Read-Only – The refreshable PDB is intended to serve as a clone master
A refreshable clone PDB can be converted into an ordinary PDB by running the command:

ALTER PLUGGABLE DATABASE REFRESH MODE NONE;
ALTER PLUGGABLE DATABASE OPEN;

An ordinary PDB cannot be converted into a refreshable clone PDB – that is the process is irreversible.

Hot clone Source to Target PDB, which will be automatically refreshed every 1 hour:

create pluggable database DEV from FRESH@to_source refresh mode none file_name_convert=('/data/DEV/db/apps_st/data/FRESH','/u01/app/DEV/db/apps_st/data') REFRESH MODE EVERY 60 MINUTES;


3.2.3 Creation of Non-Refreshable PDB database:

Hot clone Source to Target PDB, run below on target:

create pluggable database DEV from FRESH@to_source refresh mode none file_name_convert=('/data/DEV/db/apps_st/data/FRESH','/u01/app/DEV/db/apps_st/data') REFRESH MODE NONE;



3.2.3.1 Startup the PDB and save its state by executing the following commands:

$ export ORACLE_SID=<CDB SID>
$ sqlplus / as sysdba
SQL> alter pluggable database all open; (in case of a single-node database)
SQL> alter pluggable database all save state instances=all;

4 Database Post Clone Steps

4.1 Set the target UTL_FILE_DIR in target database:

Starting with Oracle Database 19c, UTL_FILE_DIR is not a supported database initialization parameter. Therefore, additional steps must be performed to retain the functionality provided by this parameter with Oracle E-Business Suite instance.

Perform the following steps to set the target UTL_FILE_DIR values in the database.

Obtain the existing value for the UTL_FILE_DIR using the following commands:
$ cd <ORACLE_HOME>
$ source <CONTEXT_NAME>.env
$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=<DB Context File> \
-oraclehome=<DB Oracle Home> -outdir=<Output/Log location> -mode=getUtlFileDir
This will create a text file <DB_NAME>_utlfiledir.txt under the <ORACLE_HOME>/dbs directory with references to the target Oracle home.
Note: If it did not created the file in $ORACLE_HOME/dbs then manually copy the file to dbs location.

Review the <DB_NAME>_utlfiledir.txt directory and edit the values, if required.

Execute the following command to store the updated values for UTL_FILE_DIR in the database:
$ cd <ORACLE_HOME>/appsutil/bin
$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=<DB Context File> \
-oraclehome=<DB Oracle Home> -outdir=<Output/Log location> \
-mode=setUtlFileDir
This command will validate the directory paths provided in the <DB_NAME>_utlfiledir.txt for existence and will also create directory objects for all the physical directory paths.

Execute the below command to create the directory object for the outbound directory (pointed to by the s_outbound_dir context variable in the database tier context file).
$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE \
-oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -mode=createDirObject
When prompted for the OS path for the directory object to be created, enter the value of the s_outbound_dir context variable in the database tier context file.

Sync up the value of UTL_FILE_DIR in the database tier context file by executing the following command:
$ perl txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE -oraclehome=$ORACLE_HOME \
-outdir=$ORACLE_HOME/appsutil/log -mode=syncUtlFileDir -skipautoconfig=yes

4.2 Execute the adcfgclone utility to configure the target database.

$ sqlplus apps/apps@DEV
$ SQL> exec fnd_conc_clone.setup_clean;
commit;
$ cd <ORACLE_HOME>/appsutil/clone/bin
$ perl adcfgclone.pl dbconfig <Target Database context file>
5 Application Cloning

5.1 Uncompress and untar the application (tar.gz) file
$ cd /u01/app/DEV/apps/fs1
$ tar –zxvf EBSapps.tar.gz

5.2 Execute adcfgclone.pl on application tier
$ cd /u01/app/DEV/apps/fs1/EBSapp/comn/clone
$ perl adcfgclone.pl appsTier dualfs
6 References
Cloning Oracle E-Business Suite Release 12.2 with Multitenant Database using Rapid Clone (Doc ID 2552208.1)
Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c (Doc ID 2552181.1)