Follow below
steps to clone remote PDB using db link:
Here, we
are cloning the pluggable database, PDBSOURCE from root container database, CDBSOURCE
to PDBTARGET in root container database, CDBTARGET.
Please find
the below test case example steps to clone remote PDB using db link.
1. Login to
Source Database CDBSOURCE
Source -
Container Database, CDBSOURCE
2. Check
the container name.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
3. Verify
the source database, PDBSOURCE status in the source container database, CDBSOURCE.
SQL> show pdbs
CON_ID
CON_NAME OPEN MODE
RESTRICTED
-------- ------------ -------------- --------------
2
PDB$SEED READ ONLY
NO
3 PDBSOURCE
READ ONLY NO
SQL>
4. Close
and Open the PDB in READ WRITE mode to create cloneuser.
SQL> alter pluggable
database PDBSOURCE close;
Pluggable database altered.
SQL>
SQL> alter pluggable
database PDBSOURCE open;
Pluggable database altered.
SQL>
5. Login to
PDBSOURCE container. i.e. PDBSOURCE, Pluggable Database inside Container
Database, CDBSOURCE
SQL> alter session set
container=PDBSOURCE;
Session altered.
SQL>
6. In PDBSOURCE@CDBSOURCE,
Create clone user. This user is used for db link creation from target container
database, CDBTARGET
SQL> create user
cloneuser identified by cloneuser;
User created.
SQL>
7. Grant
create session and create pluggable database privileges to cloneuser.
SQL> grant create
session, create pluggable database to cloneuser;
Grant succeeded.
SQL>
8. Check
the privileges granted to the cloneuser.
SQL> select * from
dba_sys_privs where grantee = 'CLONEUSER';
GRANTEE
PRIVILEGE
ADM COM INH
--------------- --------------------------------- ----- ----- ------
CLONEUSER CREATE
SESSION
NO NO NO
CLONEUSER CREATE PLUGGABLE
DATABASE
NO NO NO
SQL>
9. Create a
table and insert data to validate the data integrity after cloning PDBSOURCE
from CDBSOURCE to CDBTARGET
SQL> create table
PDBCLONETEST (c1 number);
Table created.
SQL> insert into
PDBCLONETEST values(1);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select * from PDBCLONETEST;
C1
----------
1
SQL>
10. Close
the PDBSOURCE in CDBSOURCE.
SQL> alter pluggable
database close;
Pluggable database altered.
SQL>
11. Open
the PDBSOURCE at CDBSOURCE in read only mode.
SQL> alter database open
read only;
Database altered.
SQL>
12. Verify PDBSOURCE
is in READ ONLY mode.
SQL> show pdbs
CON_ID
CON_NAME OPEN MODE RESTRICTED
-------- --------- ------------- --------------
3 PDBSOURCE
READ ONLY NO
SQL>
13. Collect
the data file location for PDBSOURCE in CDBSOURCE. This is to set it in the
file_name_convert clause in create pluggable database command in the target
container database, CDBTARGET.
Source Database Datafile Path - /home/app/19.3.0.0/oracle/oradata/CDBSOURCE/PDBSOURCE/
SQL> select name from
v$datafile;
NAME
-------------------------------------------------------------------
/home/app/19.3.0.0/oracle/oradata/CDBSOURCE/undotbs01.dbf
/home/app/19.3.0.0/oracle/oradata/CDBSOURCE/PDBSOURCE/system01.dbf
/home/app/19.3.0.0/oracle/oradata/CDBSOURCE/PDBSOURCE/sysaux01.dbf
/home/app/19.3.0.0/oracle/oradata/CDBSOURCE/PDBSOURCE/users01.dbf
SQL>
14. In the
same session, change the container to CDBSOURCE to verify the PDBSOURCE is in
READ ONLY mode.
SQL> alter session set
container=cdb$root;
Session altered.
SQL>
15. From CDBSOURCE,
Check the PDBSOURCE is in READ ONLY mode. PDBSOURCE should be in READ ONLY mode
for cloning.
SQL> show pdbs
CON_ID
CON_NAME OPEN MODE
RESTRICTED
-------- ------------- ------------- --------------
2
PDB$SEED READ ONLY NO
3 PDBSOURCE
READ ONLY NO
SQL>
16. Login
to Target Container Database, CDBTARGET
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs
CON_ID
CON_NAME OPEN MODE RESTRICTED
---------- --------- --------- --------------
2 PDB$SEED
READ ONLY NO
SQL>
17. Create
the database link in the target server to clone the PDBSOURCE from CDBSOURCE to
CDBTARGET.
SQL> create database link
clone_pdb connect to cloneuser identified by cloneuser using 'PDBSOURCE';
Database link created.
SQL>
18.
Checking tnsping and database connection to PDBSOURCE pluggable database inside
container database, CDBSOURCE
a) tnsping
connection status check
$ tnsping PDBSOURCE
TNS Ping Utility for Linux:
Version 19.0.0.0.0 - Production on 08-JUL-2022 14:52:39
Copyright (c) 1997, 2021,
Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to
resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
SERVER1.EXAMPLE.COM)(PORT = 1521)) (CONNECT_DATA = (SERVER = dedicated)
(SERVICE_NAME = PDBSOURCE.EXAMPLE.COM)))
OK (60 msec)
$
Note: If
you are facing issues in setting up tns connect string to connect directly to
pdb, then please use below document to setup one.
How to
connect to PDB directly using connect string (Doc ID 2880980.1)
b) sqlplus
connection status check
$sqlplus
SQL*Plus: Release 19.0.0.0.0
- Production on Fri Jul 8 14:52:41 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2020,
Oracle. All rights reserved.
Enter user-name: sys/sys@PDBSOURCE
as sysdba
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL> sho con_name
CON_NAME
------------------------------
PDBSOURCE
SQL>
SQL> show parameter instance_name
NAME
TYPE VALUE
-------------- -------- -------
instance_name string CDBSOURCE
SQL>
SQL> select * from
pdbclonetest;
C1
----------
1
SQL>
19.
Checking data visibility in the PDBSOURCE connect string.
SQL> select * from
pdbclonetest;
C1
----------
1
SQL>
20. Go to
the CDBTARGET path and verify none is there.
$
pwd
/home/app/19.3.0.0/oracle/oradata/CDBTARGET
$
21. No
Files or directories are there for the new PDBTARGET database.
$
ls -lrt
total 0
$
22. Setup
the file_name_convert to use in CREATE PLUGGABLE DATABASE command.
Use the source PDB datafile path collected at step
number 13 to use it in file_name_convert clause.
Source Database Datafile Path - /home/app/19.3.0.0/oracle/oradata/CDBSOURCE/PDBSOURCE/
Target Database Datafile Path - /home/app/19.3.0.0/oracle/oradata/CDBTARGET/PDBTARGET/
file_name_convert=('/home/app/19.3.0.0/oracle/oradata/CDBSOURCE/PDBSOURCE/','/home/app/19.3.0.0/oracle/oradata/CDBTARGET/PDBTARGET/')
23. Run
CREATE PLUGGABLE DATABASE command for cloning PDBSOURCE. The new clone pdb, PDBTARGET
will be created in the CDBTARGET.
SQL> create pluggable
database PDBTARGET from PDBSOURCE@clone_pdb file_name_convert=('/home/app/19.3.0.0/oracle/oradata/CDBSOURCE/PDBSOURCE/','/home/app/19.3.0.0/oracle/oradata/CDBSOURCE/PDBTARGET/');
Pluggable database created.
SQL>
24. Verify
the PDBTARGET database creation.
The created database will be in mount mode.
SQL> sho pdbs
CON_ID
CON_NAME OPEN MODE RESTRICTED
---------- ---------- ---------- -------------
2
PDB$SEED READ ONLY NO
3 PDBTARGET
MOUNTED NO
SQL>
25. Change
container to PDBTARGET.
SQL> alter session set
container=PDBTARGET;
Session altered.
SQL>
26. Verify
the datafile creation with correct datafile path.
SQL> select name from
v$datafile;
NAME
-------------------------------------------------------------------
/home/app/19.3.0.0/oracle/oradata/CDBTARGET/undotbs01.dbf
/home/app/19.3.0.0/oracle/oradata/CDBTARGET/PDBTARGET/system01.dbf
/home/app/19.3.0.0/oracle/oradata/CDBTARGET/PDBTARGET/sysaux01.dbf
/home/app/19.3.0.0/oracle/oradata/CDBTARGET/PDBTARGET/users01.dbf
SQL>
27. After
creation, the database will be in mount mode.
SQL> show pdbs
CON_ID
CON_NAME OPEN MODE RESTRICTED
---------- --------- --------- --------------
3 PDBTARGET
MOUNTED
SQL>
28. Open
the new PDBTARGET database in CDBTARGET.
SQL> alter database open;
Database altered.
SQL>
29. Verify
the new PDBTARGET is in READ WRITE mode.
SQL> show pdbs
CON_ID
CON_NAME OPEN MODE RESTRICTED
-------- --------- ----------- --------------
3 PDBTARGET
READ WRITE NO
SQL>
30. Change
container to cdb$root.
SQL> alter session set
container=cdb$root;
Session altered.
SQL>
31. Check
the container name
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
32. Verify
the new PDBTARGET cloned database status from CDB$ROOT container.
SQL> show pdbs
CON_ID
CON_NAME OPEN MODE RESTRICTED
--------- --------- ------------ --------------
2 PDB$SEED READ
ONLY NO
3 PDBTARGET
READ WRITE NO
SQL>
33. Verify
the data files for the new PDBTARGET in the operating system.
$
pwd
/home/app/19.3.0.0/oracle/oradata/CDBTARGET/PDBTARGET
$ ls -lrt
total 701528
-rw-r----- 1 oracle oinstall 134225920 Jul 8 17:05 temp01.dbf
-rw-r----- 1 oracle oinstall 5251072 Jul 8 17:05 users01.dbf
-rw-r----- 1 oracle oinstall 398467072 Jul 8 17:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall 314580992 Jul 8 17:05 system01.dbf
$
34. Now,
check the data integrity. Connect to PDBTARGET.
Connect to PDBTARGET using "alter session set container" and verify
the data.
SQL> connect sys as
sysdba
Enter password:
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show parameter instance_name
NAME
TYPE VALUE
-------------- --------- ---------
instance_name string CDBTARGET
SQL>
SQL> show pdbs
CON_ID CON_NAME
OPEN MODE RESTRICTED
------ --------- ------------ -----------
2 PDB$SEED READ ONLY NO
3 PDBTARGET
READ WRITE NO
SQL>
SQL> alter session set container=PDBTARGET;
Session altered.
SQL> show pdbs
CON_ID CON_NAME
OPEN MODE RESTRICTED
------- --------- ------------ --------------
3 PDBTARGET
READ WRITE NO
SQL>
SQL> select * from
PDBCLONETEST;
C1
----------
1
0 Comments