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:~>