Sreenu Oracle DBA: Creating or Cloning new pluggable database with existing PDB under same container db and on same host

This document describes how to create or clone new pluggable database with exisitng PDB under same container db and on same host.

In my test case:
Container DB Name: MYCDB
Existing Pluggable DB Name:ANGLES
New Pluggable DB Name:HEARTLY

Prerquites:

1. Verify Existing PDB physical size and Diskgroup size(Diskgroup Free space should be grater than Existing PDB Size)

[oracle@oracle11g-MYCDB] /home/oracle > ps -ef|grep pmon
oracle 24463 1 0 Apr29 ? 00:02:57 ora_pmon_MYCDB
oracle 28099 28002 0 09:02 pts/0 00:00:00 grep --color=auto pmon
grid 29579 1 0 Apr23 ? 00:01:30 asm_pmon_+ASM
[oracle@oracle11g-MYCDB] /home/oracle > . oraenv
ORACLE_SID = [MYCDB] ? MYCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oracle11g-MYCDB] /home/oracle > sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 14 09:02:32 2020

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- ----------
MYCDB READ WRITE

SQL> select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;

NAME TOTAL_MB/1024 FREE_MB/1024
------------------------------ ------------- ------------
DATA 1300 474,527344
LOG 60 54,734375
ARCHIVE 400 363,273438

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ANGLES READ WRITE NO

SQL>alter session set container=ANGLES;

session altered

SQL> show con_name

CON_NAME
------------------------------
ANGLES

SQL>select sum(bytes/1024/1024/1024) from dba_data_files;

SUM(BYTES/1024/1024/1024)
-------------------------
90.0976563

As per above output DATADG free space grater than ANGLES PDB physical size.

Creating or Cloning new PDB with existing PDB.

[oracle@oracle11g-MYCDB] /home/oracle > ps -ef|grep pmon
oracle 24463 1 0 Apr29 ? 00:02:57 ora_pmon_MYCDB
oracle 28099 28002 0 09:02 pts/0 00:00:00 grep --color=auto pmon
grid 29579 1 0 Apr23 ? 00:01:30 asm_pmon_+ASM
[oracle@oracle11g-MYCDB] /home/oracle > . oraenv
ORACLE_SID = [MYCDB] ? MYCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oracle11g-MYCDB] /home/oracle > sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 14 09:02:32 2020

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
MYCDB READ WRITE

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ANGLES READ WRITE NO

SQL> Create pluggable database HEARTLY from ANGLES;

Pluggable database created.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ANGLES READ WRITE NO
4 HEARTLY MOUNTED

SQL> alter session set container=HEARTLY;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
HEARTLY

SQL> ALTER PLUGGABLE DATABASE OPEN;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
5 HEARTLY READ WRITE NO

PDB Created succsessfully, Connect to Container DB and check voilation error of newly created PDB(HEARTLY)

[oracle@oracle11g-MYCDB] /home/oracle > sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 14 09:16:25 2020

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ANGLES READ WRITE NO
4 HEARTLY READ WRITE NO
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> col cause for a30;
col name for a20;
col message for a80 word_wrapped;
col CAUSE for a20
SET PAGESIZE 100;
set linesize 300;
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='HEARTLY';

NAME CAUSE TYPE MESSAGE STATUS
-------------------- -------------------- --------- -------------------------------------------------------------------------------- ---------
HEARTLY OPTION WARNING Database option APS mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.

HEARTLY OPTION WARNING Database option CONTEXT mismatch: PDB installed version NULL. CDB installed PENDING
version 12.1.0.2.0.

HEARTLY OPTION WARNING Database option DV mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.

HEARTLY OPTION WARNING Database option OLS mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.

HEARTLY OPTION WARNING Database option ORDIM mismatch: PDB installed version NULL. CDB installed PENDING
version 12.1.0.2.0.

HEARTLY OPTION WARNING Database option SDO mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.

HEARTLY OPTION WARNING Database option XOQ mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.

HEARTLY APEX WARNING APEX mismatch: PDB installed version NULL CDB installed version 4.2.5.00.08 PENDING
HEARTLY Service Name Conflic WARNING Service name or network name of service TESTXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service SIT in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service TESTXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service XYZ in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service MOXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service MO in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service SIT in the PDB is invalid or conflicts PENDING
t with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service GGADMIN.OGG$Q_INEDW in the PDB is PENDING
t invalid or conflicts with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service ABCXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service ABC in the PDB is invalid or conflicts PENDING
t with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service ANGLESXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.

19 rows selected.

As Per above violations error fine and can be ignored

Verify newly created PDB GUID

SQL> set linesize 300
select con_id, dbid, con_uid, guid, name, open_mode from v$containers;SQL>

CON_ID DBID CON_UID GUID NAME OPEN_MODE
---------- ---------- ---------- -------------------------------- -------------------- ----------
1 3062830738 1 FD9AC20F64D344D7E043B6A9E80A2F2F CDB$ROOT READ WRITE
2 2184882395 2184882395 A457A3D879377F37E053CE80C80A6D40 PDB$SEED READ ONLY
3 1199942799 1199942799 A573324654A42B0EE053CE80C80A90D0 ANGLES READ WRITE
4 956378767 956378767 A59748F1645A6E4AE053CE80C80AF3F2 HEARTLY READ WRITE
SQL>alter session set container=HEARTLY;

session altered.

SQL>
col DB_NAME for a20
col DATA_FILE_NAME for a60
set linesize 400
set pagesize 9999
SELECT c.name DB_NAME, a.name TABLESPACE_NAME, b.name DATA_FILE_NAME, b.bytes/1024/1024 SIZE_MB, b.status
FROM v$tablespace a, v$datafile b, v$containers c
WHERE a.con_id = b.con_id
AND a.con_id = c.con_id
AND a.ts# = b.ts#
ORDER BY a.con_id, a.TS#;
2 3 4 5 6

DB_NAME TABLESPACE_NAME DATA_FILE_NAME SIZE_MB STATUS
-------------------- ------------------------------ ------------------------------------------------------------ ---------- -------
HEARTLY SYSTEM +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/sys 20480 SYSTEM
tem.352.1040375171

HEARTLY SYSAUX +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/sys 5120 ONLINE
aux.354.1040375171

HEARTLY LOBS +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/lob 512 ONLINE
s.300.1040375171

HEARTLY USERS +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/use 5120 ONLINE
rs.362.1040375171

HEARTLY INDX +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ind 10240 ONLINE
x.297.1040375171

HEARTLY INSIS_HIST_DATA +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 20480 ONLINE
is_hist_data.356.1040375171

HEARTLY INSIS_MDS +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 1024 ONLINE
is_mds.353.1040375171

HEARTLY GG_DATA +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/gg_ 3072 ONLINE
data.351.1040375171

HEARTLY INSIS_CUST +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 11264 ONLINE
is_cust.360.1040375171

HEARTLY INSIS_CORE +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 20480 ONLINE
is_core.294.1040375171

HEARTLY INSIS_CORE +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 20480 ONLINE
is_core.361.1040375171

HEARTLY INSIS_CORE +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 20480 ONLINE
is_core.359.1040375171

HEARTLY INSIS_CORE +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 20480 ONLINE
is_core.355.1040375171

HEARTLY INSIS_CORE +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 30720 ONLINE
is_core.357.1040375171

HEARTLY INSIS_JMS +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 1024 ONLINE
is_jms.358.1040375171

Sreenu Oracle DBA

Tuesday, 2 June 2020

Creating or Cloning new pluggable database with existing PDB under same container db and on same host

Creating or Cloning new PDB with existing PDB.

No comments:

Post a Comment