How to clone Pluggable Database from one container to different Container Database

-

How to clone Pluggable Database from one container to different Container Database


Cloning and Refresh are day-to-day activity which DBA perform on regular basic. In today blog , We will discuss about Cloning of the pluggable database from one container database to different container database.

Oracle offers to unplug a PDB from one CDB and then plug into a different CDB. This will be another additional feature of PDB to give the highest availability and scalability the database systems may need in a cloud infrastructure environment. 

1)We have two container database cseeddb(Source Container DB) and ctestdb1 (target container DB).
NOTE: We have created container DB ctestdb1 and it does not have any PDB(Pluggable DB).


2)Verify you are in the correct container database root namespace(CDB).
NOTE: We are on first container (cseeddb) database where we have clonedb pluggable database which we will move in to another empty container ctestdb1.
SQL> set echo on
SQL> SET LINESIZE 200
SQL> SET PAGESIZE 10000
SQL> SET SERVEROUTPUT ON
SQL> COLUMN "DB DETAILS" FORMAT A100
SQL> SELECT 'DB_NAME: '||sys_context('userenv','db_name')||'/ CDB?:'||(select cdb from v$database)||'/ AUTH_ID:'
||sys_context('userenv','authenticated_identity')||'/ USER: '||sys_context('userenv','current_user')
||'/ CONTAINER:'||nvl(sys_Context('userenv','con_Name'),'NON-CDB') "DB DETAILS" FROM DUAL;

DB DETAILS
----------------------------------------------------------------------------------------------------
DB_NAME: cseeddb/ CDB?:YES/ AUTH_ID:oraware/ USER: SYS/ CONTAINER:CDB$ROOT

SQL> SET SERVEROUTPUT ON
SQL>COLUMN "RESTRICTED" FORMAT A10
SQL>select v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
from v$PDBs v inner join dba_pdbs d using (GUID) order by v.create_scn;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------------
PDB$SEED                       READ ONLY  NO         NORMAL
PSEEDDB                        READ WRITE NO         NORMAL
CLONEDB                        READ WRITE NO         NORMAL

3) On cseeddb Container database , Close, unplug and drop CLONEDB Pluggable DB with keep datafile..

SQL> alter pluggable database CLONEDB close;
Pluggable database altered.

SQL> alter pluggable database CLONEDB unplug into '/orabackup/CLONEDB.xml';
Pluggable database altered.

SQL> SET SERVEROUTPUT ON
SQL> COLUMN "RESTRICTED" FORMAT A10
SQL> select v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
from v$PDBs v inner join dba_pdbs d using (GUID) order by v.create_scn;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------------
PDB$SEED                       READ ONLY  NO         NORMAL
PSEEDDB                        READ WRITE NO         NORMAL
CLONEDB                        MOUNTED    n/a        UNPLUGGED

SQL> drop pluggable database CLONEDB keep datafiles;
Pluggable database dropped.


SQL> SET SERVEROUTPUT ON
SQL> COLUMN "RESTRICTED" FORMAT A10
SQL> select v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
from v$PDBs v inner join dba_pdbs d using (GUID) order by v.create_scn;



4) Verify you are on correct container database.



SQL> set echo on
SQL> SET LINESIZE 200
SET PAGESIZE 10000
SQL> SQL> SET SERVEROUTPUT ON
SQL> COLUMN "DB DETAILS" FORMAT A100
SQL> SELECT 'DB_NAME: '||sys_context('userenv','db_name')||'/ CDB?:'||(select cdb from v$database)||'/ AUTH_ID:'
  ||sys_context('userenv','authenticated_identity')||'/ USER: '||sys_context('userenv','current_user')
  ||'/ CONTAINER:'||nvl(sys_Context('userenv','con_Name'),'NON-CDB') "DB DETAILS" FROM DUAL;

DB DETAILS
----------------------------------------------------------------------------------------------------
DB_NAME: ctestdb1/ CDB?:YES/ AUTH_ID:oraware/ USER: SYS/ CONTAINER:CDB$ROOT
SQL> SET SERVEROUTPUT ON
COLUMN "RESTRICTED" FORMAT A10
select v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
from v$PDBs v inner join dba_pdbs d using (GUID) order by v.create_scn;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------------
PDB$SEED                       READ ONLY  NO         NORMAL




5) Check Compatibility for plugging database.

SQL> SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/orabackup/CLONEDB.xml',
pdb_name => 'CLONEDB')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
NO

NOTE: If the scripts returns NO, then check PDB_PLUG_IN_VIOLATIONS view


Compare both container compatibility using below command :

SQL> col cause for a10
col name for a10
col message for a35 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='CLONEDB';

NAME       CAUSE      TYPE      MESSAGE                             STATUS
---------- ---------- --------- ----------------------------------- ---------
CLONEDB    Parameter  WARNING   CDB parameter processes mismatch:   PENDING
                                Previous 300 Current 100
CLONEDB    Parameter  WARNING   CDB parameter memory_target         PENDING
                                mismatch: Previous 2147483648
                                Current 1073741824

Do not proceed unless you resolve all the issue of the reported by above query.

6) Plug Pluggable database .

SQL> create pluggable database CLONEDB using '/orabackup/CLONEDB.xml' move file_name_convert = ('/cseeddb/', '/ctestdb1/');

Pluggable database created.

SQL> alter pluggable database CLONEDB open;
Pluggable database altered.

SQL> set echo on
SQL> SET LINESIZE 200
SQL> SET PAGESIZE 10000
SQL> SET SERVEROUTPUT ON
SQL> COLUMN "DB DETAILS" FORMAT A100
SQL> SELECT 'DB_NAME: '||sys_context('userenv','db_name')||'/ CDB?:'||(select cdb from v$database)||'/ AUTH_ID:'
  2  ||sys_context('userenv','authenticated_identity')||'/ USER: '||sys_context('userenv','current_user')
  3  ||'/ CONTAINER:'||nvl(sys_Context('userenv','con_Name'),'NON-CDB') "DB DETAILS" FROM DUAL;

DB DETAILS
----------------------------------------------------------------------------------------------------
DB_NAME: ctestdb1/ CDB?:YES/ AUTH_ID:oraware/ USER: SYS/ CONTAINER:CDB$ROOT

SQL> COLUMN "PDB NAME" FORMAT A20
SQL> select PDB_NAME,GUID from DBA_PDBS;

PDB_NAME       GUID
-------------- --------------------------------
CLONEDB        F2FBCA4CE6AE7C40E04400144FF91E85
PDB$SEED       F325C852F0680BABE04400144FF91E85


7)Verify Container and Pluggable DB in target Container database.

SQL> SET SERVEROUTPUT ON
SQL> COLUMN "RESTRICTED" FORMAT A10
SQL> select v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
from v$PDBs v inner join dba_pdbs d using (GUID) order by v.create_scn;

NAME       OPEN_MODE  RESTRICTED STATUS
---------- ---------- ---------- -------------
PDB$SEED   READ ONLY  NO         NORMAL
CLONEDB    READ WRITE NO         NORMAL

SQL> SQL> set echo on
SQL> SET LINESIZE 200
SQL> SET PAGESIZE 10000
SQL> SET SERVEROUTPUT ON
SQL> COLUMN "DB DETAILS" FORMAT A100
SQL> SELECT 'DB_NAME: '||sys_context('userenv','db_name')||'/ CDB?:'||(select cdb from v$database)||'/ AUTH_ID:'
||sys_context('userenv','authenticated_identity')||'/ USER: '||sys_context('userenv','current_user')
||'/ CONTAINER:'||nvl(sys_Context('userenv','con_Name'),'NON-CDB') "DB DETAILS" FROM DUAL;

DB DETAILS
----------------------------------------------------------------------------------------------------
DB_NAME: ctestdb1/ CDB?:YES/ AUTH_ID:oraware/ USER: SYS/ CONTAINER:CDB$ROOT

SQL> select name, cdb from v$database;

NAME       CDB
---------- ---
CTESTDB1   YES

Please read below books in order to know more about oracle CDB and PDB databases:
================================================================
You can also , Visit our Blogs for related topic on Pluggable and Container database:




Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Restore MySQL Database from mysqlbackup

-
Image
 Restore MySQL Database from mysqlbackup  In todays world, One of the most common activity is to perform database restore using old backup or performing PIT recovery for database.  In this blog , We will show you how to perform restore and recovery of the MySQL database using backup taken via mysqlbackup. In order to restore Mysql database from MySQL backup , We will follow below steps: 1) Shut down MySQL Database Server/Service Check the status of the mysql service using command : systemctl status mysql Shut down mysql service using command : systemctl stop mysql 2) Delete all existing file of MySQL Server Delete all files inside the server's data directory. Also delete all files inside the directories specified by the --innodb_data_home_dir, --innodb_log_group_home_dir, and --innodb_undo_directory options for restore, if the directories are different from the data directory. 3) Restore full Backup mysqlbackup  --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf --d...
Read more

Oracle Database 19C Installation on Windows Server 2016

-
Image
Oracle Database 19C Installation on Windows Server 2016 In Database world, Its important to be on the latest version of the database flavor. Though Oracle have release 21c Database as well but by far Oracle 19C is most stable version.( You also check our similar blog for  Installation of Microsoft SQL Server 2016 ) Please do also check out blog for  Oracle 19c New Features to know about Oracle 19c features.  In this blog, We will be installing Oracle 19C DB on Windows Server. 1) Create required directory for Oracle Home and Oracle Base. Oracle Base: E:\app\oracle Oracle Home: E:\app\oracle\db\19.0.0\dbhome_1 2) Move the binary to E:\app\oracle\product\19.0.0 directory and unzip 3) Check unzipped directory. 4)Right Click on setup and  Run Setup as Administrator: 5)   On Configuration Screen, Select Set up software only 6)   On Database Installation Option page, Select Single instance Database installation for stand alone database server. 7)  O...
Read more

MySQL InnoDB Cluster Restore/Create Issue : - Dba.createCluster: Group Replication failed to start: MySQL Error 3094 (HY000)

-
Image
MySQL InnoDB Cluster Restore Issue : - Dba.createCluster: Group Replication failed to start: MySQL Error 3094 (HY000) Recently ,We have been restoring and recreating MySQL InnoDB Cluster and faced below issue after firing : MySQL mysqlhost01:3306 ssl JS > var cluster = dba.createCluster('testdbCluster') .. ..... ... Creating InnoDB cluster 'testdbCluster' on 'mysqlhost01:3306'... Adding Seed Instance... ERROR: Unable to start Group Replication for instance 'mysqlhost01:3306'. Please check the MySQL server error log for more information. Dba.createCluster: Group Replication failed to start: MySQL Error 3094 (HY000): mysqlhost01:3306: The START GROUP_REPLICATION command failed as the applier module failed to start. (RuntimeError) MySQL mysqlhost01:3306 ssl JS > Below is full command output with error message: Below are the message in MySQL error log: This issue is Caused by the old(original server) relay log information exiting in metadata tables ....
Read more