Sunday, February 25, 2018

Moving Pluggable Database (PDB) between the different Container Database (CDB) in Different Servers with ASM Storage


Moving Pluggable Database (PDB) between the different Container Database (CDB) with ASM Storage


Moving Pluggable Database (PDB) between the different Container Database (CDB) with ASM Storage

==========Source Cluster RAC1 and RAC2==============================>

Source Cluster: RAC1 and RAC2
Source Container Database: CONTDB
Source Pluggable Database: CONPDB3


Target Cluster: RAC3 and RAC4
Target Container Database: CONTDB
Target Pluggable Database: CONPDB3



[oracle@rac1-12c ~]$ . oraenv
ORACLE_SID = [primdb1] ? contdb
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac1-12c ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 25 06:10:56 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
SQL> connect sys/oracle@contdb as sysdba
Connected.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CONPDB1                        READ WRITE NO
         4 CONPDB2                        READ WRITE NO
         5 CONPDB3                        READ WRITE NO
SQL> alter session set container=CONPDB3;
Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/system.335.968994861
+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/sysaux.334.968994861
+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/users.332.968994889

SQL> connect sys/oracle@contdb as sysdba
Connected.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CONPDB1                        READ WRITE NO
         4 CONPDB2                        READ WRITE NO
         5 CONPDB3                        READ WRITE NO


SQL> alter pluggable database conpdb3 close immediate instances=all;
Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CONPDB1                        READ WRITE NO
         4 CONPDB2                        READ WRITE NO
         5 CONPDB3                        MOUNTED
SQL> select name ,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
CONPDB1                        READ WRITE
CONPDB2                        READ WRITE
CONPDB3                        MOUNTED

SQL> alter pluggable database conpdb3 unplug into '/u01/app/oracle/CONPDB3_NEW.xml';
Pluggable database altered.

SQL> exit

[oracle@rac1-12c ~]$ . oraenv
ORACLE_SID = [contdb] ? contdb1
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac1-12c ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Feb 25 06:16:04 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: CONTDB (DBID=1349816764)

RMAN> BACKUP FOR TRANSPORT AS COMPRESSED BACKUPSET FILESPERSET=1024 
PLUGGABLE DATABASE 'CONPDB3' FORMAT '/u01/app/oracle/CONPDB3.dfb';

Starting backup at 25-FEB-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=82 instance=contdb1 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00019 name=+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/sysaux.334.968994861
input datafile file number=00018 name=+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/system.335.968994861
input datafile file number=00020 name=+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/users.332.968994889
channel ORA_DISK_1: starting piece 1 at 25-FEB-18
channel ORA_DISK_1: finished piece 1 at 25-FEB-18
piece handle=/u01/app/oracle/CONPDB3.dfb tag=TAG20180225T061635 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 25-FEB-18

RMAN> exit


[oracle@rac1-12c ~]$ cd /u01/app/oracle/
[oracle@rac1-12c oracle]$ ls -lrth
total 148M
drwxrwxr-x.  3 oracle oinstall 4.0K Jun 23  2017 product
drwxr-xr-x   4 root   root     4.0K Jun 23  2017 tfa
drwxr-xr-x   3 oracle oinstall 4.0K Jun 23  2017 rac1-12c
drwxr-xr-x   3 oracle oinstall 4.0K Jun 24  2017 log
drwxr-xr-x   2 oracle oinstall 4.0K Jun 24  2017 checkpoints
drwxrwxr-x   5 oracle oinstall 4.0K Jun 24  2017 crsdata
drwxr-x---   7 oracle oinstall 4.0K Jun 29  2017 cfgtoollogs
drwxrwxr-x  19 oracle oinstall 4.0K Oct 25 22:48 diag
drwxr-x---   8 oracle oinstall 4.0K Nov  8 04:53 audit
drwxr-x---   7 oracle oinstall 4.0K Feb 25 05:13 admin
-rw-r--r--   1 oracle vboxsf   5.4K Feb 25 05:31 conpdb3.xml
-rw-r--r--   1 oracle vboxsf   5.4K Feb 25 06:13 CONPDB3_NEW.xml
-rw-r-----   1 oracle vboxsf   148M Feb 25 06:16 CONPDB3.dfb

[oracle@rac1-12c oracle]$ scp CONPDB3_NEW.xml oracle@192.168.2.201:/u01/app/oracle/
oracle@192.168.2.201's password:
CONPDB3_NEW.xml                                   100% 5525     5.4KB/s   00:00
[oracle@rac1-12c oracle]$ scp CONPDB3.dfb oracle@192.168.2.201:/u01/app/oracle/
oracle@192.168.2.201's password:
CONPDB3.dfb                                       100%  148MB  73.9MB/s   00:02
[oracle@rac1-12c oracle]$ cd


[oracle@rac1-12c ~]$ . oraenv
ORACLE_SID = [contdb1] ? contdb
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac1-12c ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 25 09:43:06 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> connect sys/oracle@contdb as sysdba
Connected.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CONPDB1                        READ WRITE NO
         4 CONPDB2                        READ WRITE NO
         5 CONPDB3                        MOUNTED

SQL> col PATCH_ID format 999999999
SQL> col PATCH_UID format 99999999
SQL> col VERSION format a10
SQL> col STATUS format a15
SQL> col DESCRIPTION format a80
SQL> set lines 200 pages 1000
SQL> select PATCH_ID, PATCH_UID, VERSION, STATUS, DESCRIPTION 
from DBA_REGISTRY_SQLPATCH order by BUNDLE_SERIES;
no rows selected

SQL> !
[oracle@rac1-12c ~]$ cd /u01/app/oracle/product/12.1.0.2/db_1/OPatch/
[oracle@rac1-12c OPatch]$ ./opatch lspatches
There are no Interim patches installed in this Oracle Home "/u01/app/oracle/product/12.1.0.2/db_1".

OPatch succeeded.
[oracle@rac1-12c OPatch]$

===========================Target Cluster RAC3 and RAC4=================>

[oracle@rac3-12c ~]$ . oraenv
ORACLE_SID = [orcldb1] ? contdb
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac3-12c ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 25 06:22:16 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> connect sys/oracle@contdb as sysdba
Connected.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CONPDB1                        READ WRITE NO
         4 CONPDB2                        READ WRITE NO
SQL> exit


[oracle@rac3-12c ~]$ . oraenv
ORACLE_SID = [+ASM1] ? contdb1
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0.2/db_1
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac3-12c ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Feb 25 06:51:58 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: CONTDB (DBID=1349963590)

RMAN> run
{
set command id  to 'CONPDB3';
RESTORE FOREIGN DATAFILE  18 TO NEW, 19 TO NEW, 
20 TO NEW FROM BACKUPSET '/u01/app/oracle/CONPDB3.dfb';
};
2> 3> 4> 5>
executing command: SET COMMAND ID

Starting restore at 25-FEB-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=97 instance=contdb1 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring foreign file 00018
channel ORA_DISK_1: restoring foreign file 00019
channel ORA_DISK_1: restoring foreign file 00020
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/CONPDB3.dfb
channel ORA_DISK_1: restoring foreign file 18 to +DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/system.350.969000723
channel ORA_DISK_1: restoring foreign file 19 to +DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/sysaux.349.969000723
channel ORA_DISK_1: restoring foreign file 20 to +DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/users.351.969000723
channel ORA_DISK_1: foreign piece handle=/u01/app/oracle/CONPDB3.dfb
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 25-FEB-18
RMAN> exit

[oracle@rac3-12c ~]$ . oraenv
ORACLE_SID = [contdb1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle

ASMCMD> cd DATA
ASMCMD> cd CONTDB
ASMCMD> ls
5D900B1A1E953788E053C902A8C0836A/
5D902973DAA94A24E053C902A8C0F831/
5D902BDD14014BFCE053C902A8C0636A/
65FEDAA33CD86078E0536502A8C07E3C/
ARCHIVELOG/
AUTOBACKUP/
CONTROLFILE/
DATAFILE/
FD9AC20F64D244D7E043B6A9E80A2F2F/
ONLINELOG/
PARAMETERFILE/
PASSWORD/
TEMPFILE/
ASMCMD> cd 65FEDAA33CD86078E0536502A8C07E3C/

ASMCMD> ls
DATAFILE/
ASMCMD> cd datafile
ASMCMD> ls
SYSAUX.349.969000723
SYSTEM.350.969000723
USERS.351.969000723
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   FEB 25 06:00:00  Y    SYSAUX.349.969000723
DATAFILE  UNPROT  COARSE   FEB 25 06:00:00  Y    SYSTEM.350.969000723
DATAFILE  UNPROT  COARSE   FEB 25 06:00:00  Y    USERS.351.969000723
ASMCMD> exit

[oracle@rac3-12c ~]$ . oraenv
ORACLE_SID = [+ASM1] ? contdb
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac3-12c ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 25 06:55:00 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> connect sys/oracle@contdb as sysdba
Connected.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CONPDB1                        READ WRITE NO
         4 CONPDB2                        READ WRITE NO
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

[oracle@rac3-12c ~]$ . oraenv
ORACLE_SID = [contdb] ? contdb1
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0.2/db_1
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac3-12c ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Feb 25 06:58:45 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: CONTDB (DBID=1349963590)

RMAN> change datafilecopy '+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/system.350.969000723',
2> '+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/sysaux.349.969000723',
3> '+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/users.351.969000723' uncatalog;

using target database control file instead of recovery catalog
uncataloged datafile copy
datafile copy file name=+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/
system.350.969000723 RECID=7 STAMP=969000740
uncataloged datafile copy
datafile copy file name=+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/
sysaux.349.969000723 RECID=8 STAMP=969000748
uncataloged datafile copy
datafile copy file name=+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/
users.351.969000723 RECID=6 STAMP=969000724
Uncataloged 3 objects

RMAN> exit
Recovery Manager complete.

[oracle@rac3-12c ~]$ cd /u01/app/oracle/
[oracle@rac3-12c oracle]$ cat CONPDB3_NEW.xml | grep path
  +DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/system.335.968994861
  +DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/sysaux.334.968994861
  +DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/TEMPFILE/temp.333.968997067
  +DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/users.332.968994889
[oracle@rac3-12c oracle]$

ASMCMD> pwd
+data/contdb/65FEDAA33CD86078E0536502A8C07E3C/datafile

ASMCMD> exit
[oracle@rac3-12c oracle]$ cd

[oracle@rac3-12c ~]$ . oraenv
ORACLE_SID = [+ASM1] ? contdb1
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0.2/db_1
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac3-12c ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 25 07:04:54 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> connect sys/oracle@contdb as sysdba
Connected.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CONPDB1                        READ WRITE NO
         4 CONPDB2                        READ WRITE NO

SQL> create pluggable database CONPDB3 as clone  using '/u01/app/oracle/CONPDB3_NEW.xml'
  2  source_file_name_convert = (
  3  '+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/system.335.968994861',
     '+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/system.350.969000723',
  4  '+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/sysaux.334.968994861',
     '+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/sysaux.349.969000723',
  5  '+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/users.332.968994889',
     '+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/users.351.969000723',
  6  '+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/TEMPFILE/temp.333.968997067',
     '+DATA/CONTDB/65FEDAA33CD86078E0536502A8C07E3C/DATAFILE/conpdb3_temp01.dbf')
  7  file_name_convert=NONE
  8  NOCOPY;

Pluggable database created.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
CONPDB1                        READ WRITE
CONPDB2                        READ WRITE
CONPDB3                        MOUNTED

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CONPDB1                        READ WRITE NO
         4 CONPDB2                        READ WRITE NO
         5 CONPDB3                        MOUNTED
SQL>
SQL> alter pluggable database CONPDB3 open instances=all;

Warning: PDB altered with errors.

[oracle@rac3-12c ~]$ . oraenv
ORACLE_SID = [contdb] ?
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac3-12c ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 25 10:54:01 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> connect sys/oracle@contdb as sysdba
Connected.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CONPDB1                        READ WRITE NO
         4 CONPDB2                        READ WRITE NO
         5 CONPDB3                        READ WRITE YES

SQL> select cause, type, message from PDB_PLUG_IN_VIOLATIONS where name = 'CONPDB3';

CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
---
SQL Patch                                                        ERROR
PSU bundle patch 171017 (DATABASE PATCH SET UPDATE 12.1.0.2.171017): Installed in the CDB but not in the PDB.

[oracle@rac3-12c ~]$ . oraenv
ORACLE_SID = [contdb] ? contdb1
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0.2/db_1
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac3-12c ~]$ cd /u01/app/oracle/product/12.1.0.2/db_1/OPatch/

[oracle@rac3-12c OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Sun Feb 25 10:56:36 2018
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/
sqlpatch_11332_2018_02_25_10_56_36/sqlpatch_invocation.log

Connecting to database...OK
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series PSU:
  Not installed in the binary registry and ID 171017 in PDB CDB$ROOT, ID 171017 in PDB PDB$SEED, 
  ID 171017 in PDB CONPDB1, ID 171017 in PDB CONPDB2

Adding patches to installation queue and performing prereq checks...
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED CONPDB1 CONPDB2
    The following patches will be rolled back:
      26713565 (DATABASE PATCH SET UPDATE 12.1.0.2.171017)
    Nothing to apply
  For the following PDBs: CONPDB3
    Nothing to roll back
    Nothing to apply

Installing patches...
Patch installation complete.  Total patches installed: 4

Validating logfiles...
Patch 26713565 rollback (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26713565/21602269/
26713565_rollback_CONTDB_CDBROOT_2018Feb25_10_57_32.log (no errors)
Patch 26713565 rollback (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26713565/21602269/
26713565_rollback_CONTDB_PDBSEED_2018Feb25_10_58_23.log (no errors)
Patch 26713565 rollback (pdb CONPDB1): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26713565/21602269/
26713565_rollback_CONTDB_CONPDB1_2018Feb25_10_58_23.log (no errors)
Patch 26713565 rollback (pdb CONPDB2): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26713565/21602269/
26713565_rollback_CONTDB_CONPDB2_2018Feb25_10_58_23.log (no errors)
SQL Patching tool complete on Sun Feb 25 10:59:37 2018
[oracle@rac3-12c OPatch]$

[oracle@rac3-12c OPatch]$ . oraenv
ORACLE_SID = [contdb] ? contdb1
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac3-12c OPatch]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 25 11:01:09 2018
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, Real Application Clusters, 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 CONPDB1                        READ WRITE NO
         4 CONPDB2                        READ WRITE NO
         5 CONPDB3                        READ WRITE YES

SQL> select inst_id, name, restricted from gv$containers;

   INST_ID NAME                           RES
---------- ------------------------------ ---
         1 CDB$ROOT                       NO
         1 PDB$SEED                       NO
         1 CONPDB1                        NO
         1 CONPDB2                        NO
         1 CONPDB3                        YES
         2 CDB$ROOT                       NO
         2 PDB$SEED                       NO
         2 CONPDB1                        NO
         2 CONPDB2                        NO
         2 CONPDB3                        YES

10 rows selected.

SQL> alter pluggable database CONPDB3 close immediate instances=ALL;
Pluggable database altered.

SQL> select inst_id, name, restricted from gv$containers;

   INST_ID NAME                           RES
---------- ------------------------------ ---
         1 CDB$ROOT                       NO
         1 PDB$SEED                       NO
         1 CONPDB1                        NO
         1 CONPDB2                        NO
         1 CONPDB3
         2 CDB$ROOT                       NO
         2 PDB$SEED                       NO
         2 CONPDB1                        NO
         2 CONPDB2                        NO
         2 CONPDB3

10 rows selected.

SQL> alter pluggable database CONPDB3 open read write instances=ALL;
Pluggable database altered.

SQL> select inst_id, name, restricted from gv$containers;

   INST_ID NAME                           RES
---------- ------------------------------ ---
         1 CDB$ROOT                       NO
         1 PDB$SEED                       NO
         1 CONPDB1                        NO
         1 CONPDB2                        NO
         1 CONPDB3                        NO
         2 CDB$ROOT                       NO
         2 PDB$SEED                       NO
         2 CONPDB1                        NO
         2 CONPDB2                        NO
         2 CONPDB3                        NO

10 rows selected.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CONPDB1                        READ WRITE NO
         4 CONPDB2                        READ WRITE NO
         5 CONPDB3                        READ WRITE NO

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CONPDB1                        READ WRITE NO
         4 CONPDB2                        READ WRITE NO
         5 CONPDB3                        READ WRITE NO

SQL> connect sys/oracle@192.168.2.201:1521/conpdb3 as sysdba
Connected.

SQL> show con_name

CON_NAME
------------------------------
CONPDB3

SQL> connect user1/oracle@192.168.2.201:1521/conpdb3
Connected.

SQL> select * from tab1;

        NO
----------
         1

SQL>



3 comments:

  1. Hi,
    This is a great post. So clear and easy to follow. Thanks for the tangible and attainable help. All your hard work is much appreciated. If someone want to learn Online (Virtual) instructor lead live training in Oracle GoldenGate.

    ReplyDelete

  2. Excellent Blog very imperative good content, this article is useful to beginners and real time Employees.
    Oracle R12 Financials Training in Hyderabad

    ReplyDelete