Creating Container and Pluggable Database Using CREATE DATABASE SQL Command

In Oracle OCM exam, You may need to create database using command line. Even in real life, You may no have the option to use the software for GUI. I will show how to create a CDB and PDB using SQL Command.

Create required directories:

[oracle@ocm12c admin]$ mkdir -p /u01/app/oracle/oradata/newdb/

Set Environment Variables:

[oracle@ocm12c oradata]$ export ORACLE_SID=newdb
[oracle@ocm12c oradata]$ export ORACLE_BASE=/u01/app/oracle
[oracle@ocm12c oradata]$ export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_2
[oracle@ocm12c oradata]$ export PATH=$ORACLE_HOME/bin:$PATH

Create Password File:

[oracle@ocm12c ~]$ orapwd FILE='/u01/app/oracle/product/12.1.0/dbhome_2/dbs/orapwNEWDB' ENTRIES=10 FORMAT=12

Enter password for SYS:

Create a PFile & SPFile:

[oracle@ocm12c ~]$ vi /home/oracle/pfile.ora
db_name=newdb
control_files='/u01/app/oracle/oradata/newdb/control01.ctl'
ENABLE_PLUGGABLE_DATABASE=true
[oracle@ocm12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 19 17:08:29 2020

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

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/pfile.ora'
ORACLE instance started.

Total System Global Area  272629760 bytes
Fixed Size                  2923336 bytes
Variable Size             213910712 bytes
Database Buffers           50331648 bytes
Redo Buffers                5464064 bytes

SQL> create spfile from pfile='/home/oracle/pfile.ora';

File created.

Run Create Database Command:

This example includes the ENABLE PLUGGABLE DATABASE clause to create a CDB with the root and the seed. This example also includes the SEED FILE_NAME_CONVERT clause to specify the names and locations of the seed’s files. This example also includes tablespace_datafile clauses that specify attributes of the seed’s data files for the SYSTEM and SYSAUX tablespaces that differ from the root’s.

SQL> CREATE DATABASE newdb
  USER SYS IDENTIFIED BY sys_password
  USER SYSTEM IDENTIFIED BY system_password
  LOGFILE GROUP 1 ('/u01/app/oracle/oradata/newdb/redo01a.log','/u01/app/oracle/oradata/newdb/redo01b.log') 
             SIZE 50M BLOCKSIZE 512,
          GROUP 2 ('/u01/app/oracle/oradata/newdb/redo02a.log','/u01/app/oracle/oradata/newdb/redo02b.log') 
             SIZE 50M BLOCKSIZE 512,
          GROUP 3 ('/u01/app/oracle/oradata/newdb/redo03a.log','/u01/app/oracle/oradata/newdb/redo03b.log') 
             SIZE 50M BLOCKSIZE 512
  MAXLOGHISTORY 1
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 1024
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16
  EXTENT MANAGEMENT LOCAL
  DATAFILE '/u01/app/oracle/oradata/newdb/system01.dbf'
    SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  SYSAUX DATAFILE '/u01/app/oracle/oradata/newdb/sysaux01.dbf'
    SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  DEFAULT TABLESPACE deftbs
     DATAFILE '/u01/app/oracle/oradata/newdb/deftbs01.dbf'
     SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
  DEFAULT TEMPORARY TABLESPACE tempts1
     TEMPFILE '/u01/app/oracle/oradata/newdb/temp01.dbf'
     SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
  UNDO TABLESPACE undotbs1
     DATAFILE '/u01/app/oracle/oradata/newdb/undotbs01.dbf'
     SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
  ENABLE PLUGGABLE DATABASE
    SEED
    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/newdb/', 
                         '/u01/app/oracle/oradata/pdbseed/')
    SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
    SYSAUX DATAFILES SIZE 100M
  USER_DATA TABLESPACE usertbs
    DATAFILE '/u01/app/oracle/oradata/pdbseed/usertbs01.dbf'
    SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Database created.

Create Data Dictionary Views:

When we create a database using CREATE DATABASE SQL command, by default no data dictionary views creates. We need to run manually couple of Oracle supplied dictionary scripts like catalog.sql, catproc.sql, pupbld.sql to populate the necessary dictionary views.

But this is not simple for a container database, because we have multiple container databases such as CDB$ROOT and PDB$SEED, we need to run these scripts against each of these containers. To simplify the creation of dictionary views in a CDB, Oracle provides a script called catcdb.sql located under $ORACLE_HOME/rdbms/admin directory.

So, when we run the catcdb.sql script, it creates all the database components like the way DBCA does, it will create database components like Spatial, Oracle Text, XDK, etc, even if we wouldn’t use them.

Note:::Before executing catcdb.sql, check perl setting and set appropriate perl to avoid unwanted errors.

[oracle@ocm12c admin]$ which perl
/usr/bin/perl
[oracle@ocm12c admin]$ export PATH=/u01/app/oracle/product/12.1.0/dbhome_2/perl/bin/:$PATH
[oracle@ocm12c admin]$ which perl
/u01/app/oracle/product/12.1.0/dbhome_2/perl/bin/perl

[oracle@ocm12c admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 19 17:38:33 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, OLAP, Advanced Analytics and Real Application Testing options

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

Session altered.

Enter new password for SYS:
Enter new password for SYSTEM:
Enter temporary tablespace name: tempts1

Session altered.

@catcdb.sql execution log:

SQL> @catcdb.sql

Session altered.

Enter new password for SYS:
Enter new password for SYSTEM:
Enter temporary tablespace name: tempts1

Session altered.

Connected.

old   1: select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin'||'&&slash'||'catcon.pl' as rdbms_admin_catcon from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'rdbms'||'/'||'admin'||'/'||'catcon.pl' as rdbms_admin_catcon from dual

old   1: select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin' as rdbms_admin from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'rdbms'||'/'||'admin' as rdbms_admin from dual

old   1: select '&&oracle_home'||'&&slash'||'sqlplus'||'&&slash'||'admin'||'&&slash'||'help' as sqlplus_admin_help from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'sqlplus'||'/'||'admin'||'/'||'help' as sqlplus_admin_help from dual

old   1: select '&&oracle_home'||'&&slash'||'sqlplus'||'&&slash'||'admin' as sqlplus_admin from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'sqlplus'||'/'||'admin' as sqlplus_admin from dual

old   1: select '&&oracle_home'||'&&slash'||'javavm'||'&&slash'||'install' as jvm_install from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'javavm'||'/'||'install' as jvm_install from dual

old   1: select '&&oracle_home'||'&&slash'||'xdk'||'&&slash'||'admin' as xdk_admin from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'xdk'||'/'||'admin' as xdk_admin from dual

old   1: select '&&oracle_home'||'&&slash'||'ctx'||'&&slash'||'admin' as ctx_admin from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'ctx'||'/'||'admin' as ctx_admin from dual

old   1: select '&&oracle_home'||'&&slash'||'ctx'||'&&slash'||'admin'||'&&slash'||'defaults' as ctx_admin_defaults from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'ctx'||'/'||'admin'||'/'||'defaults' as ctx_admin_defaults from dual


old   1: select '&&oracle_home'||'&&slash'||'ord'||'&&slash'||'admin' as ord_admin from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'ord'||'/'||'admin' as ord_admin from dual


old   1: select '&&oracle_home'||'&&slash'||'ord'||'&&slash'||'im'||'&&slash'||'admin' as im_admin from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'ord'||'/'||'im'||'/'||'admin' as im_admin from dual


old   1: select '&&oracle_home'||'&&slash'||'olap'||'&&slash'||'admin' as olap_admin from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'olap'||'/'||'admin' as olap_admin from dual


old   1: select '&&oracle_home'||'&&slash'||'md'||'&&slash'||'admin' as md_admin from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'md'||'/'||'admin' as md_admin from dual

old   1: select '&&oracle_home'||'&&slash'||'apex' as apex_home from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'apex' as apex_home from dual

catcon: ALL catcon-related output will be written to catalog_catcon_28753.lst
catcon: See catalog*.log files for output generated by scripts
catcon: See catalog_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catproc_catcon_28865.lst
catcon: See catproc*.log files for output generated by scripts
catcon: See catproc_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catoctk_catcon_29579.lst
catcon: See catoctk*.log files for output generated by scripts
catcon: See catoctk_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to owminst_catcon_29636.lst
catcon: See owminst*.log files for output generated by scripts
catcon: See owminst_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to pupbld_catcon_29697.lst
catcon: See pupbld*.log files for output generated by scripts
catcon: See pupbld_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to hlpbld_catcon_29750.lst
catcon: See hlpbld*.log files for output generated by scripts
catcon: See hlpbld_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to initjvm_catcon_29803.lst
catcon: See initjvm*.log files for output generated by scripts
catcon: See initjvm_*.lst files for spool files, if any
A process terminated prior to completion.
    Review the initjvm*.log files to identify the failure
Died at /u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/catcon.pm line 6149.

catcon: ALL catcon-related output will be written to initxml_catcon_29828.lst
catcon: See initxml*.log files for output generated by scripts
catcon: See initxml_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to xmlja_catcon_29889.lst
catcon: See xmlja*.log files for output generated by scripts
catcon: See xmlja_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catjava_catcon_29942.lst
catcon: See catjava*.log files for output generated by scripts
catcon: See catjava_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catxdbj_catcon_29995.lst
catcon: See catxdbj*.log files for output generated by scripts
catcon: See catxdbj_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catctx_catcon_30048.lst
catcon: See catctx*.log files for output generated by scripts
catcon: See catctx_*.lst files for spool files, if any
catcon.pl: completed successfully


User altered.

catcon: ALL catcon-related output will be written to dr0defin_catcon_30124.lst
catcon: See dr0defin*.log files for output generated by scripts
catcon: See dr0defin_*.lst files for spool files, if any
catcon.pl: completed successfully


User altered.

catcon: ALL catcon-related output will be written to dbmsxdbt_catcon_30177.lst
catcon: See dbmsxdbt*.log files for output generated by scripts
catcon: See dbmsxdbt_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to ordinst_catcon_30230.lst
catcon: See ordinst*.log files for output generated by scripts
catcon: See ordinst_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catim_catcon_30285.lst
catcon: See catim*.log files for output generated by scripts
catcon: See catim_*.lst files for spool files, if any
A process terminated prior to completion.
    Review the catim*.log files to identify the failure
Died at /u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/catcon.pm line 6149.

catcon: ALL catcon-related output will be written to olap.sql_catcon_30310.lst
catcon: See olap.sql*.log files for output generated by scripts
catcon: See olap.sql_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to mdinst_catcon_30374.lst
catcon: See mdinst*.log files for output generated by scripts
catcon: See mdinst_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catols_catcon_30556.lst
catcon: See catols*.log files for output generated by scripts
catcon: See catols_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catapx_catcon_30612.lst
catcon: See catapx*.log files for output generated by scripts
catcon: See catapx_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catmac_catcon_31380.lst
catcon: See catmac*.log files for output generated by scripts
catcon: See catmac_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catclust_catcon_31458.lst
catcon: See catclust*.log files for output generated by scripts
catcon: See catclust_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catbundleapply_catcon_31522.lst
catcon: See catbundleapply*.log files for output generated by scripts
catcon: See catbundleapply_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to utlrp_catcon_31576.lst
catcon: See utlrp*.log files for output generated by scripts
catcon: See utlrp_*.lst files for spool files, if any
catcon.pl: completed successfully

Connected.

Session altered.


Pluggable database altered.


Pluggable database altered.


PL/SQL procedure successfully completed.


Session altered.


PL/SQL procedure successfully completed.


Session altered.


Pluggable database altered.


Pluggable database altered.


Session altered.


Session altered.


Session altered.

Create a PDB Database:

SQL> select con_id, dbid, name, open_mode from v$containers;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         1 3289283615 CDB$ROOT                       READ WRITE
         2 2757438212 PDB$SEED                       READ ONLY

SQL> select cdb from v$database;

CDB
---
YES

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

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

SQL> CREATE PLUGGABLE DATABASE mypdb
      ADMIN USER pdbadmin IDENTIFIED BY pdbadmin
      ROLES = (dba)
      DEFAULT TABLESPACE users
        DATAFILE '/u01/app/oracle/oradata/mydb/mypdb/mypdb_users01.dbf' SIZE 25M AUTOEXTEND ON
      FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/pdbseed/','/u01/app/oracle/oradata/mydb/mypdb/') STORAGE (MAXSIZE 2G)
      PATH_PREFIX = '/u01/app/oracle/oradata/pdbseed/';

Pluggable database created.

SQL> show pdbs

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

SQL> alter pluggable database mypdb open;

Pluggable database altered.

SQL> show pdbs

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

Database Components Status:

SQL> col comp_name for a40
col version for a15
col status for a10
SELECT name, comp_name, version, status FROM v$database, dba_registry;

NAME      COMP_NAME                                VERSION         STATUS
--------- ---------------------------------------- --------------- ----------
NEWCDB    Oracle Application Express                               VALID
NEWCDB    Oracle Label Security                    12.1.0.2.0      VALID
NEWCDB    Spatial                                  12.1.0.2.0      INVALID
NEWCDB    Oracle Multimedia                                        LOADING
NEWCDB    Oracle Text                              12.1.0.2.0      VALID
NEWCDB    Oracle Workspace Manager                 12.1.0.2.0      VALID
NEWCDB    Oracle XML Database                      12.1.0.2.0      VALID
NEWCDB    Oracle Database Catalog Views            12.1.0.2.0      INVALID
NEWCDB    Oracle Database Packages and Types       12.1.0.2.0      INVALID
NEWCDB    Oracle XDK                               12.1.0.2.0      VALID
NEWCDB    Oracle Database Java Packages                            LOADING
NEWCDB    OLAP Analytic Workspace                  12.1.0.2.0      VALID
NEWCDB    Oracle OLAP API                          12.1.0.2.0      VALID

13 rows selected.

Recompile All Invalid Objects:

SQL> col comp_name for a40
col version for a15
col status for a10

SQL> SELECT name, comp_name, version, status FROM v$database, dba_registry where status='INVALID';

NAME      COMP_NAME                                VERSION         STATUS
--------- ---------------------------------------- --------------- ----------
NEWCDB    Spatial                                  12.1.0.2.0      INVALID
NEWCDB    Oracle Database Java Packages                            INVALID

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

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

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2020-08-20 21:56:27

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
...
Function dropped.

...Database user "SYS", database schema "APEX_040200", user# "88" 21:56:37
...Compiled 0 out of 3014 objects considered, 0 failed compilation 21:56:37
...271 packages
...263 package bodies
...452 tables
...11 functions
...16 procedures
...3 sequences
...457 triggers
...1320 indexes
...211 views
...0 libraries
...6 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 21:56:37
...Completed key object existence check 21:56:38
...Setting DBMS Registry 21:56:38
...Setting DBMS Registry Complete 21:56:38
...Exiting validate 21:56:38

PL/SQL procedure successfully completed.
SQL> SELECT name, comp_name, version, status FROM v$database, dba_registry;

NAME      COMP_NAME                                VERSION         STATUS
--------- ---------------------------------------- --------------- ----------
NEWCDB    Oracle Database Vault                    12.1.0.2.0      VALID
NEWCDB    Oracle Application Express               4.2.5.00.08     VALID
NEWCDB    Oracle Label Security                    12.1.0.2.0      VALID
NEWCDB    Spatial                                  12.1.0.2.0      INVALID
NEWCDB    Oracle Multimedia                                        LOADING
NEWCDB    Oracle Text                              12.1.0.2.0      VALID
NEWCDB    Oracle Workspace Manager                 12.1.0.2.0      VALID
NEWCDB    Oracle XML Database                      12.1.0.2.0      VALID
NEWCDB    Oracle Database Catalog Views            12.1.0.2.0      VALID
NEWCDB    Oracle Database Packages and Types       12.1.0.2.0      VALID
NEWCDB    Oracle XDK                               12.1.0.2.0      VALID
NEWCDB    Oracle Database Java Packages                            INVALID
NEWCDB    OLAP Analytic Workspace                  12.1.0.2.0      VALID
NEWCDB    Oracle OLAP API                          12.1.0.2.0      VALID
NEWCDB    Oracle Real Application Clusters         12.1.0.2.0      OPTION OFF

15 rows selected.

Error:

Got below error while execute the catcdb.sql because of Perl was using default one.

Can't locate Term/ReadKey.pm in @INC (@INC contains: /u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 . /u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/) at /u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/catcon.pm line 189.
BEGIN failed--compilation aborted at /u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/catcon.pm line 189.
Compilation failed in require at /u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/catcon.pl line 94.
BEGIN failed--compilation aborted at /u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/catcon.pl line 94.

Solution: Need to set oracle parl on for the environment.

@catcdb.sql log with error:

[oracle@ocm12c admin]$ which perl
/usr/bin/perl
[oracle@ocm12c admin]$ export PATH=/u01/app/oracle/product/12.1.0/dbhome_2/perl/bin/:$PATH
[oracle@ocm12c admin]$ which perl
/u01/app/oracle/product/12.1.0/dbhome_2/perl/bin/perl

[oracle@ocm12c admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 19 17:38:33 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, OLAP, Advanced Analytics and Real Application Testing options

SQL> @catcdb.sql

Session altered.

Enter new password for SYS:
Enter new password for SYSTEM:
Enter temporary tablespace name: tempts1

Session altered.

Connected.

old   1: select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin'||'&&slash'||'catcon.pl' as rdbms_admin_catcon from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'rdbms'||'/'||'admin'||'/'||'catcon.pl' as rdbms_admin_catcon from dual

old   1: select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin' as rdbms_admin from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'rdbms'||'/'||'admin' as rdbms_admin from dual

old   1: select '&&oracle_home'||'&&slash'||'sqlplus'||'&&slash'||'admin'||'&&slash'||'help' as sqlplus_admin_help from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'sqlplus'||'/'||'admin'||'/'||'help' as sqlplus_admin_help from dual

old   1: select '&&oracle_home'||'&&slash'||'sqlplus'||'&&slash'||'admin' as sqlplus_admin from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'sqlplus'||'/'||'admin' as sqlplus_admin from dual

old   1: select '&&oracle_home'||'&&slash'||'javavm'||'&&slash'||'install' as jvm_install from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'javavm'||'/'||'install' as jvm_install from dual

old   1: select '&&oracle_home'||'&&slash'||'xdk'||'&&slash'||'admin' as xdk_admin from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'xdk'||'/'||'admin' as xdk_admin from dual

old   1: select '&&oracle_home'||'&&slash'||'ctx'||'&&slash'||'admin' as ctx_admin from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'ctx'||'/'||'admin' as ctx_admin from dual

old   1: select '&&oracle_home'||'&&slash'||'ctx'||'&&slash'||'admin'||'&&slash'||'defaults' as ctx_admin_defaults from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'ctx'||'/'||'admin'||'/'||'defaults' as ctx_admin_defaults from dual

old   1: select '&&oracle_home'||'&&slash'||'ord'||'&&slash'||'admin' as ord_admin from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'ord'||'/'||'admin' as ord_admin from dual

old   1: select '&&oracle_home'||'&&slash'||'ord'||'&&slash'||'im'||'&&slash'||'admin' as im_admin from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'ord'||'/'||'im'||'/'||'admin' as im_admin from dual

old   1: select '&&oracle_home'||'&&slash'||'olap'||'&&slash'||'admin' as olap_admin from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'olap'||'/'||'admin' as olap_admin from dual

old   1: select '&&oracle_home'||'&&slash'||'md'||'&&slash'||'admin' as md_admin from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'md'||'/'||'admin' as md_admin from dual

old   1: select '&&oracle_home'||'&&slash'||'apex' as apex_home from dual
new   1: select '/u01/app/oracle/product/12.1.0/dbhome_2'||'/'||'apex' as apex_home from dual

catcon: ALL catcon-related output will be written to catalog_catcon_19107.lst
catcon: See catalog*.log files for output generated by scripts
catcon: See catalog_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catproc_catcon_19201.lst
catcon: See catproc*.log files for output generated by scripts
catcon: See catproc_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catoctk_catcon_19828.lst
catcon: See catoctk*.log files for output generated by scripts
catcon: See catoctk_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to owminst_catcon_19885.lst
catcon: See owminst*.log files for output generated by scripts
catcon: See owminst_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to pupbld_catcon_19951.lst
catcon: See pupbld*.log files for output generated by scripts
catcon: See pupbld_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to hlpbld_catcon_20004.lst
catcon: See hlpbld*.log files for output generated by scripts
catcon: See hlpbld_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to initjvm_catcon_20059.lst
catcon: See initjvm*.log files for output generated by scripts
catcon: See initjvm_*.lst files for spool files, if any
A process terminated prior to completion.
    Review the initjvm*.log files to identify the failure
Died at /u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/catcon.pm line 6149.

catcon: ALL catcon-related output will be written to initxml_catcon_20084.lst
catcon: See initxml*.log files for output generated by scripts
catcon: See initxml_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to xmlja_catcon_20148.lst
catcon: See xmlja*.log files for output generated by scripts
catcon: See xmlja_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catjava_catcon_20201.lst
catcon: See catjava*.log files for output generated by scripts
catcon: See catjava_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catxdbj_catcon_20254.lst
catcon: See catxdbj*.log files for output generated by scripts
catcon: See catxdbj_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catctx_catcon_20307.lst
catcon: See catctx*.log files for output generated by scripts
catcon: See catctx_*.lst files for spool files, if any
catcon.pl: completed successfully

User altered.

catcon: ALL catcon-related output will be written to dr0defin_catcon_20370.lst
catcon: See dr0defin*.log files for output generated by scripts
catcon: See dr0defin_*.lst files for spool files, if any
catcon.pl: completed successfully

User altered.

catcon: ALL catcon-related output will be written to dbmsxdbt_catcon_20423.lst
catcon: See dbmsxdbt*.log files for output generated by scripts
catcon: See dbmsxdbt_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to ordinst_catcon_20476.lst
catcon: See ordinst*.log files for output generated by scripts
catcon: See ordinst_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catim_catcon_20529.lst
catcon: See catim*.log files for output generated by scripts
catcon: See catim_*.lst files for spool files, if any
A process terminated prior to completion.
    Review the catim*.log files to identify the failure
Died at /u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/catcon.pm line 6149.

catcon: ALL catcon-related output will be written to olap.sql_catcon_20554.lst
catcon: See olap.sql*.log files for output generated by scripts
catcon: See olap.sql_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to mdinst_catcon_20611.lst
catcon: See mdinst*.log files for output generated by scripts
catcon: See mdinst_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catols_catcon_20802.lst
catcon: See catols*.log files for output generated by scripts
catcon: See catols_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catapx_catcon_20869.lst
catcon: See catapx*.log files for output generated by scripts
catcon: See catapx_*.lst files for spool files, if any
A process terminated prior to completion.
    Review the catapx*.log files to identify the failure
Died at /u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/catcon.pm line 6149.

catcon: ALL catcon-related output will be written to catmac_catcon_21240.lst
catcon: See catmac*.log files for output generated by scripts
catcon: See catmac_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catclust_catcon_21317.lst
catcon: See catclust*.log files for output generated by scripts
catcon: See catclust_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to catbundleapply_catcon_21380.lst
catcon: See catbundleapply*.log files for output generated by scripts
catcon: See catbundleapply_*.lst files for spool files, if any
catcon.pl: completed successfully

catcon: ALL catcon-related output will be written to utlrp_catcon_21433.lst
catcon: See utlrp*.log files for output generated by scripts
catcon: See utlrp_*.lst files for spool files, if any
catcon.pl: completed successfully

Connected.

Session altered.

Pluggable database altered.

alter pluggable database pdb$seed open READ WRITE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01405: fetched column value is NULL

PL/SQL procedure successfully completed.

Session altered.

 FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (
                                    *
ERROR at line 2:
ORA-06550: line 2, column 37:
PL/SQL: ORA-01219: database or pluggable database not open: queries allowed on
fixed tables or views only
ORA-06550: line 2, column 16:
PL/SQL: SQL Statement ignored
ORA-06550: line 5, column 52:
PLS-00364: loop index variable 'ITEM' use is invalid
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
ORA-06550: line 7, column 4:
PLS-00201: identifier 'SYS.DBMS_ASSERT' must be declared
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored

Session altered.

alter pluggable database pdb$seed close
*
ERROR at line 1:
ORA-65020: pluggable database PDB$SEED already closed

Pluggable database altered.
Session altered.
Session altered.
Session altered.