Multitenant- Relocating existing PDB from Local CDB to Remote CDB

Multitenant- Relocating existing PDB  from Local CDB to Remote CDB

LOCAL CDB -SMRCDB

REMOTE CDB - STDRAC

• Connect to Remote CDB database and create db link name is relocatelnk 

oracle@srv4:/u01> . oraenv
ORACLE_SID = [PLMPRODB1] ? STDRAC
The Oracle base remains unchanged with value /u01/app/oracle
oracle@srv4:/u01> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 11 15:38:44 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST1                          MOUNTED

SQL> CREATE DATABASE LINK relocatelnk CONNECT TO system IDENTIFIED BY system USING'SMRCDB';

Database link created.

SQL> SELECT SYSDATE FROM DUAL@relocatelnk;

SYSDATE
---------
11-AUG-20

• Connect to local CDB database name SMRCDB 

SQL>
oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 11 15:39:42 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PLM1                           READ WRITE NO
         5 PLM2                           READ WRITE NO
         6 PLM4                           READ WRITE NO
         8 PLM5                           READ WRITE NO

• Grant privilege to common user and create table for testing purpose. Common user system already present in this case.

SQL> GRANT SYSOPER TO system CONTAINER=ALL;

Grant succeeded.

SQL> alter session set container=PLM1;

Session altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PLM1                           READ WRITE NO
SQL> CREATE TABLE SYSTEM.TEST ( RID NUMBER(2), NOTES VARCHAR2(80)) ;

Table created.

SQL> INSERT INTO SYSTEM.TEST VALUES (1,'BEFORE RELOCATING PDB1');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT SYS_CONTEXT('USERENV','HOST') CON_ID FROM DUAL;

CON_ID
--------------------------------------------------------------------------------
srv3

• Connect to Remote CDB and create and relocate PLM1 from CDB database. In relocate PDB scenario PDB name should be same.

SQL> CREATE PLUGGABLE DATABASE PLM1 FROM PLM1@relocatelnk RELOCATE AVAILABILITY MAX;

Pluggable database created.

SQL>
SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST1                          MOUNTED
         4 PLM1                           MOUNTED

• Check the status of RELOCATING PDB  database 

SQL> col PDB_NAME for a40
SQL> SELECT PDB_NAME, STATUS FROM CDB_PDBS WHERE PDB_NAME='PLM1';

PDB_NAME                                 STATUS
---------------------------------------- ----------
PLM1                                     RELOCATING

SQL> SELECT SYS_CONTEXT('USERENV','HOST') FROM dual;

SYS_CONTEXT('USERENV','HOST')
--------------------------------------------------------------------------------
srv4


SQL> ALTER PLUGGABLE DATABASE PLM1 OPEN;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PLM1                           READ WRITE NO

oracle@srv4:/u01> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 11 16:32:17 2020

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

• Check Status and GUID of relocating PDB database

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set container=PLM1;

Session altered.

SQL> select GUID,NAME,OPEN_MODE from v$pdbs;

GUID
--------------------------------
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
AC1FFF3873F80C57E0534938A8C08B37
PLM1
READ WRITE


SQL> col name for a40
SQL> set lines 200
SQL> select GUID,NAME,OPEN_MODE from v$pdbs;

GUID                             NAME                                     OPEN_MODE
-------------------------------- ---------------------------------------- ----------
AC1FFF3873F80C57E0534938A8C08B37 PLM1                                     READ WRITE

SQL> select name,open_mode,database_role from v$database;

NAME                                     OPEN_MODE            DATABASE_ROLE
---------------------------------------- -------------------- ----------------
STDRAC                                   READ WRITE           PRIMARY

• Connecting to local PDB on srv3.example.com server. It will relocate the connection to remote PDB database srv4.example.com server

oracle@srv3:~> sqlplus system/system123@PLM1

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 11 16:29:15 2020

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

Last Successful login time: Fri Aug 07 2020 13:43:09 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
STDRAC    READ WRITE           PRIMARY


SQL> set lines 200
SQL>  select GUID,NAME,OPEN_MODE from v$pdbs;

GUID                             NAME                                     OPEN_MODE
-------------------------------- ---------------------------------------- ----------
AC1FFF3873F80C57E0534938A8C08B37 PLM1                                     READ WRITE

• Check listener PLM1 services  below:

oracle@srv3:~> lsnrctl services

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 11-AUG-2020 16:41:50

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv3.example.com)(PORT=1523)))
Services Summary...
Service "SMRCDB" has 1 instance(s).
  Instance "SMRCDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:12 refused:0 state:ready
         LOCAL SERVER
Service "SMRCDBXDB" has 1 instance(s).
  Instance "SMRCDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: srv3, pid: 26043>
         (ADDRESS=(PROTOCOL=tcp)(HOST=srv3.example.com)(PORT=34901))
Service "ac1fff3873f80c57e0534938a8c08b37" has 1 instance(s).
  Instance "SMRCDB", status READY, has 2 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: srv3, pid: 26043>
         (ADDRESS=(PROTOCOL=tcp)(HOST=srv3.example.com)(PORT=34901))
      "COMMON" established:1 refused:0 state:ready
         FORWARD SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=srv4.example.com)(PORT=1523))
Service "ac37117322776c58e0534938a8c02132" has 1 instance(s).
  Instance "SMRCDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:12 refused:0 state:ready
         LOCAL SERVER
Service "ac37c34d100c4149e0534a38a8c0497e" has 1 instance(s).
  Instance "SMRCDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:12 refused:0 state:ready
         LOCAL SERVER
Service "ac85b17a8dfc65c7e0534938a8c08ad5" has 1 instance(s).
  Instance "SMRCDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:12 refused:0 state:ready
         LOCAL SERVER
Service "plm1" has 1 instance(s).
  Instance "SMRCDB", status READY, has 2 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: srv3, pid: 26043>
         (ADDRESS=(PROTOCOL=tcp)(HOST=srv3.example.com)(PORT=34901))
      "COMMON" established:1 refused:0 state:ready
         FORWARD SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=srv4.example.com)(PORT=1523))
Service "plm2" has 1 instance(s).
  Instance "SMRCDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:12 refused:0 state:ready
         LOCAL SERVER
Service "plm4" has 1 instance(s).
  Instance "SMRCDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:12 refused:0 state:ready
         LOCAL SERVER
Service "plm5" has 1 instance(s).
  Instance "SMRCDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:12 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
oracle@srv3:~>

Multitenant- Creating PDB Using the DBMS_PDB Package from Non-CDB

• Multitenant-  Creating PDB Using the DBMS_PDB Package from  Non-CDB

SRV3 - CDB

SRV4 - NONCDB

• Shutdown Non CDB database and open database in read only mode

SQL> select name,open_mode,database_role,CDB from V$database;

NAME      OPEN_MODE            DATABASE_ROLE    CDB
--------- -------------------- ---------------- ---
SMRNONCD  READ WRITE           PRIMARY          NO

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size                  8621760 bytes
Variable Size             620757312 bytes
Database Buffers         1157627904 bytes
Redo Buffers                8155136 bytes
Database mounted.
SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

• Execute DBMS_PD Package command below

SQL> exec DBMS_PDB.DESCRIBE( PDB_DESCR_FILE => '/home/oracle/SMRNONCDB.xml');

PL/SQL procedure successfully completed.

• Check existing datafile and scp to target CDB database.

SQL> SET PAGESIZE 20
SQL> SET LINESIZE 200
SQL> SELECT 'scp -p ' || NAME || ' root@srv3:/u01/app/oracle/oradata/SMRNONCDB/' as command FROM V$DATAFILE ORDER BY 1;

SQL> exit

oracle@srv4:~> scp -p /u01/app/oracle/oradata/SMRNONCDB/system01.dbf oracle@srv3:/u01/app/oracle/oradata/SMRNONCDB/
Password:
system01.dbf                                                                                                                          100%  800MB  47.1MB/s   00:17
oracle@srv4:~> scp -p /u01/app/oracle/oradata/SMRNONCDB/undotbs01.dbf oracle@srv3:/u01/app/oracle/oradata/SMRNONCDB/
Password:
undotbs01.dbf                                                                                                                         100%   65MB  65.0MB/s   00:01
oracle@srv4:~> scp -p /u01/app/oracle/oradata/SMRNONCDB/users01.dbf oracle@srv3:/u01/app/oracle/oradata/SMRNONCDB/
Password:
users01.dbf 

oracle@srv4:~> scp -p /u01/app/oracle/oradata/SMRNONCDB/sysaux01.dbf oracle@srv3:/u01/app/oracle/oradata/SMRNONCDB/
Password:
sysaux01.dbf                                                                                                                          100%  460MB  92.0MB/s   00:05

• Target CDB database Server SRV3.example.com

(Check the database compatibility )

SQL> begin
IF DBMS_PDB.CHECK_PLUG_COMPATIBILITY( PDB_DESCR_FILE => '/home/oracle/SMRNONCDB.xml', PDB_NAME => 'PLM5')
THEN
DBMS_OUTPUT.PUT_LINE('COMPATIBLE');
ELSE
DBMS_OUTPUT.PUT_LINE('NOT COMPATIBLE');
END IF;
end;
/  2    3    4    5    6    7    8    9
COMPATIBLE

PL/SQL procedure successfully completed.

• Provide permission to SMRNONCDB.xml if already have then kindly ignore.

srv3:/home/oracle # chown oracle:oinstall /home/oracle/SMRNONCDB.xml

Create Pluggable database 

oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 10 20:17:58 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> CREATE PLUGGABLE DATABASE PLM5 USING '/home/oracle/SMRNONCDB.xml';

Pluggable database created.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PLM1                           READ WRITE NO
         4 PDB2                           MOUNTED
         5 PLM2                           READ WRITE NO
         6 PLM4                           READ WRITE NO
         7 PLM3                           READ WRITE NO
         8 PLM5                           MOUNTED
SQL>

SQL> ALTER SESSION SET CONTAINER=PLM5;

Session altered.

• Execute conversion script noncdb_to_pdb.sql

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

• Open and check the status

SQL> ALTER PLUGGABLE DATABASE PLM5 OPEN;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         8 PLM5                           READ WRITE NO

Multitenant - Create PDB from NON CDB database using DBLINK

Multitenant - Create PDB from NON CDB database using DBLINK

LOCAL database  name : SMRCDB

Remote database name : NONCDB

• Provide grant to local user system is common user in both CDB and non CDB database. Also create separate user and provide privilege

SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO SYSTEM;

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PLM1                           READ WRITE NO
         4 PDB2                           MOUNTED
         5 PLM2                           READ WRITE NO
         7 PLM3                           READ WRITE NO

• Create database link NONCDB  

SQL> CREATE DATABASE LINK NONCDB  CONNECT TO system IDENTIFIED BY system USING 'SMRNONCDB';

Database link created.

• Check the status

SQL> SELECT SYSDATE FROM DUAL@NONCDB;

SYSDATE
---------
10-AUG-20

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES@NONCDB ORDER BY 1;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRNONCDB/sysaux01.dbf
/u01/app/oracle/oradata/SMRNONCDB/system01.dbf
/u01/app/oracle/oradata/SMRNONCDB/undotbs01.dbf
/u01/app/oracle/oradata/SMRNONCDB/users01.dbf

• It will through error as NON CDB should be open in read only mode.

SQL> CREATE PLUGGABLE DATABASE PLM4 FROM oradb@NONCDB;

CREATE PLUGGABLE DATABASE PLM4 FROM oradb@NONCDB

*

ERROR at line 1:

ORA-17628: Oracle error 65011 returned by remote Oracle server

ORA-65011: Pluggable database  does not exist.

• Open NON CDB in read only mode

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1795162112 bytes

Fixed Size                  8621760 bytes

Variable Size             620757312 bytes

Database Buffers         1157627904 bytes

Redo Buffers                8155136 bytes

Database mounted.

SQL> ALTER DATABASE OPEN READ ONLY;

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

SMRNONCD  READ ONLY            PRIMARY

• Create PDB using DB link

SQL> CREATE PLUGGABLE DATABASE PLM4 FROM NON$CDB@NONCDB;

Pluggable database created.

SQL> ALTER SESSION SET CONTAINER=PLM4;

Session altered.

• Execute noncdb_to_pdb.sql command

SQL> set timing on

SQL> @/u01/app/oracle/product/12.2.0/dbhome_1//rdbms/admin/noncdb_to_pdb.sql

• Open PDB and check the status 

SQL> ALTER PLUGGABLE DATABASE PLM4 OPEN;

Elapsed: 00:00:00.13

SQL> SELECT PDB_NAME, STATUS FROM DBA_PDBS;

PDB_NAME

--------------------------------------------------------------------------------

STATUS

----------

PLM4

NORMAL

1 row selected.

Elapsed: 00:00:00.02

SQL> col PDB_NAME for a40

SQL> /

PDB_NAME                                 STATUS

---------------------------------------- ----------

PLM4                                     NORMAL

1 row selected.

Elapsed: 00:00:00.00

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         6 PLM4                           READ WRITE YES

SQL> SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N' ORDER BY 1;

USERNAME

--------------------------------------------------------------------------------

INFODBA

1 row selected.