Oracle 19c New Feature of Multitenant-Cloning Oracle 19c PDBs Using DBCA in Silent Mode
Description:-
In this article we are going to see how to use Database Configuration Assistant (DBCA) to clone a remote pluggable database (PDB) into a container database (CDB).
Source DB
SQL> COLUMN DB_NAME FORMAT A10
COLUMN CON_ID FORMAT 999
COLUMN PDB_NAME FORMAT A15
COLUMN OPERATION FORMAT A16
COLUMN OP_TIMESTAMP FORMAT A10
COLUMN CLONED_FROM_PDB_NAME FORMAT A15
SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY
WHERE CON_ID > 2
ORDER BY CON_ID;
Target DB
COLUMN DB_NAME FORMAT A10
COLUMN CON_ID FORMAT 999
COLUMN PDB_NAME FORMAT A15
COLUMN OPERATION FORMAT A16
COLUMN OP_TIMESTAMP FORMAT A10
COLUMN CLONED_FROM_PDB_NAME FORMAT A15
SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY
WHERE CON_ID > 2
ORDER BY CON_ID;
Prepare for clone oracle 19c PDB Database
Step:-1 Prepare the DBWRPDB Before Cloning
Now i am going to create some tables in source PDB database DBWRPDB.
Log in to DBWRPDB in DBWR as SYSTEM.
[oracle@dev19c ~]$ sqlplus system/oracle@DBWRPDB
SQL*Plus: Release 19.0.0.0.0 – Production on Fri Oct 4 12:21:56 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Fri Oct 04 2019 12:16:49 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
- Use the hr.sql script to create the HR user and EMPLOYEES table in DBWRPDB.
Download sample data HERE
SQL> @hr.sql
- Verify that PDB1 contains the HR.EMPLOYEES table.
SQL> select count(*) from employees;
COUNT(*)
———-
107
Connect to DBWR as SYSTEM.
[oracle@dev19c ~]$ sqlplus system/oracle@dbwr
SQL*Plus: Release 19.0.0.0.0 – Production on Fri Oct 4 12:27:00 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Fri Oct 04 2019 12:21:56 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
- Create a common user in DBWR, used in the database link automatically created in india to connect to DBWR during the cloning operation.
SQL> CREATE USER c##DBWR_remote IDENTIFIED BY password CONTAINER=ALL;
User created.
- Grant the privileges.
SQL> GRANT create session, create pluggable database TO c##DBWR_remote CONTAINER=ALL;
Grant succeeded.
Step:-2 Use DBCA to Clone a Remote PDB
export ORACLE_SID=dbwr
[oracle@dev19c ~]$ dbca -silent -createPluggableDatabase -createFromRemotePDB -remotePDBName dbwrpdb -remoteDBConnString dbwr -remoteDBSYSDBAUserName SYS -remoteDBSYSDBAUserPassword oracle -sysDBAUserName sys -sysDBAPassword oracle -dbLinkUsername c##DBWR_remote -dbLinkUserPassword password -sourceDB india -pdbName DBWRPDB1
Prepare for db operation
50% complete
Create pluggable database using remote clone operation
100% complete
Pluggable database “DBWRPDB1” plugged successfully.
Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/india/DBWRPDB1/india.log” for further details.
Step3:-Check the status PDB dbwrpdb1 Is Cloned
- Connect to India database as SYS.
[oracle@dev19c ~]$ export ORACLE_SID=india
[oracle@dev19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Fri Oct 4 14:49:09 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 INDPDB READ WRITE NO
4 DBWRPDB1 READ WRITE NO
[oracle@dev19c admin]$ tnsping dbwrpdb1
TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 04-OCT-2019 14:51:26
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.155)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbwrpdb1)))
OK (10 msec)
- Check that india database contains the HR.EMPLOYEES table as in dbwrpdb1
SQL> conn system/oracle@DBWRPDB1
Connected.
SQL> select count(*) from hr.employees;
COUNT(*)
———-
107
- Login to India database and check the below query
COLUMN DB_NAME FORMAT A10
COLUMN CON_ID FORMAT 999
COLUMN PDB_NAME FORMAT A15
COLUMN OPERATION FORMAT A16
COLUMN OP_TIMESTAMP FORMAT A10
COLUMN CLONED_FROM_PDB_NAME FORMAT A15
SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY
WHERE CON_ID > 2
ORDER BY CON_ID;
Connect with me:-
Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/hariprasathdba
