CLONE A REMOTE PDB – Multitenant – 12.1.0.2
Starting
from 12.1.0.2 patch we have ability to create a PDB as a clone of a remote.
This feature is available in 12.1.0.1 but not working….
·
Source Database : cdb1@PDB1_2 @vm215.ora.com (OEL 6.5 x64)
· Target Database : cdb5 @ vm216.ora.com (OEL 6.5 x64)
· Target Database : cdb5 @ vm216.ora.com (OEL 6.5 x64)
Here is my 12c Lab environment
for target database
On SOURCE : cdb1@vm215.ora.com
[oracle@vm215
~]$ . oraenv
ORACLE_SID =
[orcl] ? cdb1
The Oracle
base remains unchanged with value /u01/app/oracle
[oracle@vm215
~]$ sqlplus / as sysdba
SQL> @open
INST_ID DB_NAME INSTANCE_NAME VERSION HOST_NAME OPEN_MODE STARTUP_TIME CURR_SCN
----------
---------- ---------------- ----------------- ---------------
-------------------- ---------------------------- --------------
1 CDB1 cdb1 12.1.0.2.0 vm215.ora.com READ WRITE 11-JUL-2020 03:29:55 PM 6031823
PDBS CHECKING
CON_ID
DBID NAME OPEN_MODE OPEN_TIME
----------
---------- ---------- ---------- --------------------------------
2 2388059743 PDB$SEED READ ONLY
11-JUL-20 03.30.08.885 PM +05:30
3 3462552188 PDB1_1 READ WRITE 11-JUL-20 04.21.42.975 PM
+05:30
4 3518416229 PDB1_2 READ WRITE 11-JUL-20 04.21.42.977 PM
+05:30
5 2691280463 NPTEST READ WRITE 11-JUL-20 04.21.42.978 PM
+05:30
SQL> select tablespace_name from
dba_tablespaces ;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1_1 READ WRITE NO
4 PDB1_2 READ WRITE NO
5 NPTEST READ WRITE NO
SQL> alter session set container=PDB1_2 ;
Session
altered.
SQL> show pdbs ;
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
4 PDB1_2 READ WRITE NO
CREATING ADMIN_USER for REMOTE
CLONING
SQL> grant CREATE SESSION, CREATE
PLUGGABLE DATABASE TO remote_clone_user identified by oracle ;
Grant
succeeded.
Opening db in Read only mode..
SQL> alter pluggable database PDB1_2 close
;
Pluggable
database altered.
SQL> alter pluggable database PDB1_2 OPEN
READ ONLY ;
Pluggable
database altered.
SQL> set
lin200 pages 200
col guid for
a10
col open_time
for a10
col name for
a10
select inst_id, CON_ID, DBID, NAME ,OPEN_MODE
from gv$pdbs where name <> 'PDB$SEED' ;
INST_ID
CON_ID DBID NAME
OPEN_MODE
----------
---------- ---------- ----------
----------
1 4 3518416229 PDB1_2
READ ONLY
Get the file system details for
PDB1_2 datafiles.
[oracle@vm215 ~]$ cd /u01/app/oracle/oradata
[oracle@vm215
oradata]$ ls
cdb1 cdb2
CDB2 cdb3 DB12CR2
MDASDB NCTEST OCM
OCM11G OCM11G_DB orcl
ORCL pdb1_1 PROD
PROD1
[oracle@vm215 ~]$ cd
/u01/app/oracle/oradata/cdb1/PDB1_2
[oracle@vm215
PDB1_2]$ ls
PDB1_2_users01.dbf sysaux01.dbf
system01.dbf
temp012016-10-27_04-31-21-AM.dbf
SQL> select name from v$datafile ;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/PDB1_2/system01.dbf
/u01/app/oracle/oradata/cdb1/PDB1_2/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/PDB1_2/PDB1_2_users01.dbf
SQL> SELECT name, open_mode FROM v$pdbs ;
NAME OPEN_MODE
------------------------------
----------
PDB1_2 MOUNTED
Now we are good to proceed for
cloning
TARGET : cdb5@vm216.ora.com
[oracle@vm216
~]$ . oraenv
ORACLE_SID =
[CCBTEST] ? cdb5
The Oracle
base remains unchanged with value /u01/app/oracle
Checking the db under cdb5
[oracle@vm216 ~]$ sqlplus / as sysdba
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 CPDB MOUNTED
Get the list of tablespace and
validate with source. If any dependency
then need to create before cloning
SQL>
select tablespace_name from dba_tablespaces ;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
Add tnsentry in target
ORACLE_HOME (vm216.ora.com) referring to Source PDB which is going to clone...
[oracle@vm216 admin]$ vi tnsnames.ora
PDB1_2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
vm216.ora.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDB1_2)
)
)
After addging tnsentry, need to
validate the source db connection..
[oracle@vm216 admin]$ tnsping PDB1_2
TNS Ping
Utility for Linux: Version 12.1.0.2.0 - Production on 25-JUN-2020 16:15:59
Copyright (c)
1997, 2014, Oracle. All rights reserved.
Used parameter
files:
/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES
adapter to resolve the alias
Attempting to
contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =
vm215.ora.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PDB1_2)
OK (0 msec)
[oracle@vm216 admin]$ sqlplus
dbsnmp/oracle@PDB1_2
SQL*Plus:
Release 12.1.0.2.0 Production on Thu Jun 25 16:16:22 2020
Copyright (c)
1982, 2014, Oracle. All rights reserved.
Last
Successful login time: Mon Oct 15 2018 08:50:23 +05:30
Connected to:
Oracle
Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the
Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select CON_ID, DBID, NAME, OPEN_MODE from
v$pdbs ;
CON_ID DBID NAME
OPEN_MODE
------ ----------
------- ----------
4
3518416229 PDB1_2 READ WRITE
Creating remote database link
for Source PDB (vm215) at target CDB (vm216)
SQL> create database link REMOTE_PDB1_2
CONNECT TO remote_clone_user identified by oracle using 'PDB1_2';
Database link
created.
Validate database link
SQL> select sysdate from
dual@REMOTE_PDB1_2 ;
SYSDATE
---------
25-JUN-20
Since I am using different
pdbname from source so I am converting the filesystem location using
FILE_NAME_CONVERT parameter..
Cloning the Remote PDB
SQL> CREATE PLUGGABLE DATABASE JUL_PDB
FROM PDB1_2@REMOTE_PDB1_2
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/PDB1_2/','/u01/app/oracle/oradata/cdb5/JUL_PDB','/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb5/JUL_PDB')
;
Pluggable
database created.
Validate the created db and
status.
SQL> SELECT
name, open_mode FROM v$pdbs ;
NAME OPEN_MODE
------------------------------
----------
PDB$SEED READ ONLY
CPDB READ WRITE
JUL_PDB MOUNTED
Open in Read/Write mode
SQL> alter pluggable database JUL_PDB open
;
Pluggable
database altered.
SQL>
SQL> SELECT name, open_mode FROM v$pdbs ;
NAME OPEN_MODE
------------------------------
----------
PDB$SEED READ ONLY
CPDB READ WRITE
JUL_PDB READ WRITE
SQL> alter session set container=JUL_PDB ;
Session
altered.
Validate user
and object details from source and target
SQL> select
username from dba_users ;
USERNAME
--------------------------
REMOTE_CLONE_USER
PDB2ADMIN
……..
………
SYS
38 rows
selected.
SQL> set
lin200 pages 200
SQL> conn hr/hr@vm216:1521/JUL_PDB
ERROR:
ORA-28002: the
password will expire within 7 days
Connected.
SQL> show
user
USER is
"HR"
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
----------------
------- ----------
GTT1 TABLE
MY_TEMP_TABLE TABLE
T1 TABLE
No comments:
Post a Comment