A Not So Straightforward Approach to Manual Database (CDB and PDB) Creation Via SQL*Plus

While beginning my tinkering with Database 12c, I was curious to create the container database via SQL*Plus. Below are the steps I followed.

Set your environment variable for ORACLE_SID. It’s a given that you need ORACLE_HOME set J

[oracle@collabn1 dbs]$ ORACLE_SID=beans
[oracle@collabn1 dbs]$ export ORACLE_SID

Edit your initialization parameter file. It is important to note, for the CREATE DATABASE script to create the SEED database, the ENABLE_PLUGGABLE_DATABASE parameter must be set to TRUE. To simplify my creation, I enabled Oracle Managed Filsystem (OMF) by setting db_create_file_dest.

[oracle@collabn1 dbs]$ ls -lhtr
total 16K
-rw-r--r-- 1 oracle oinstall 3.0K Feb  3  2012 init.ora
-rw-r----- 1 oracle oinstall   33 Sep  1 16:15 initrac1.ora
-rw-r----- 1 oracle oinstall    0 Sep  2 14:05 lkinstslob
-rw-rw---- 1 oracle oinstall 1.6K Sep  2 14:05 hc_slob.dat
-rw-rw---- 1 oracle oinstall 1.6K Sep  2 15:00 hc_rac1.dat
[oracle@collabn1 dbs]$ vi initbeans.ora
db_name='beans'
memory_target=1G
processes=150
db_block_size=8192
db_domain=''
db_create_file_dest='+DATA'
db_recovery_file_dest='+FRA'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=BEANSXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files=('+DATA', '+FRA')
compatible='12.0.0'
enable_pluggable_database=true

Create the server parameter file from the above parameter file.

[oracle@collabn1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 2 15:08:46 2013

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

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

Start the instance in nomount state.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2296576 bytes
Variable Size             671089920 bytes
Database Buffers          390070272 bytes
Redo Buffers                5480448 bytes

Create the Container Database. I’ll create a pluggable one later in the article.

SQL> CREATE DATABASE beans
USER SYS IDENTIFIED BY "oracle"
USER SYSTEM IDENTIFIED BY "oracle"
EXTENT MANAGEMENT LOCAL
DEFAULT TABLESPACE users
DEFAULT TEMPORARY TABLESPACE temp
UNDO TABLESPACE undotbs1
ENABLE PLUGGABLE DATABASE
      SEED
            SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
            SYSAUX DATAFILES SIZE 100M;

Database created.

Let’s add a couple of tablespaces.

SQL> CREATE TABLESPACE apps_tbs LOGGING
     DATAFILE '+DATA'
     SIZE 500M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
     EXTENT MANAGEMENT LOCAL;

Tablespace created.

SQL> CREATE TABLESPACE indx_tbs LOGGING
     DATAFILE '+DATA'
     SIZE 100M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
     EXTENT MANAGEMENT LOCAL;

Tablespace created.

Build dictionary views

SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql

As the system user, run the following script.

SQL> @?/sqlplus/admin/pupbld.sql

So far, the creation steps have been similar to previous versions. Of course, the last few scripts create objects specific to the new Pluggable Database architecture.

Courtesy of Wissem’s article, I found I was missing a few extra scripts.

SQL> @?/rdbms/admin/catblock.sql  
SQL> @?/rdbms/admin/catoctk.sql  
SQL> @?/rdbms/admin/owminst.plb

The last script (mentioned in Oracle Documentation) doesn’t exist!

Apparently it’s a bug which will be fixed in a future release. Bug 17033183 – $OH/rdbms/admin/catcdb.sql is missing from 12c release (Doc ID 17033183.8)

SQL> @?/rdbms/admin/catcdb.sql
SP2-0310: unable to open file "/u01/app/oracle/product/12.1.0.1/dbhome_1/rdbms/admin/catcdb.sql"

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

1 row selected.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4067699455 PDB$SEED                       READ ONLY

1 row selected.

SQL> SELECT NAME from  v$database; 

NAME
---------
BEANS

1 row selected.

Configure Enterprise Manager Express

SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5501);

PL/SQL procedure successfully completed.

At this point, our database looks like this:

Create a Pluggable Database from the Seed Database

Next, we start with creating a Pluggable Database from the Seed (PDB$SEED).

The script below created a pluggable database from PDB$SEED, with an administrator called heinzadmin, storage limit of 2G and maximum shared temporary tablespace size to 100M and, a default tablespace called heinz_data.

SQL> CREATE PLUGGABLE DATABASE heinz_pdb ADMIN USER heinz_admin IDENTIFIED BY beans
  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  DEFAULT TABLESPACE heinz_data
    DATAFILE '+DATA' SIZE 250M AUTOEXTEND ON;
CREATE PLUGGABLE DATABASE heinz_pdb ADMIN USER heinz_admin IDENTIFIED BY beans
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

Interesting, nothing in the alert.log that’s of use either.

[oracle@collabn1 trace]$ tail -f alert_beans.log
…
Mon Sep 02 23:35:18 2013
CREATE PLUGGABLE DATABASE heinz_pdb ADMIN USER heinz_admin IDENTIFIED BY *  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  DEFAULT TABLESPACE heinz_data
    DATAFILE '+DATA' SIZE 250M AUTOEXTEND ON
ORA-604 signalled during: CREATE PLUGGABLE DATABASE heinz_pdb ADMIN USER heinz_admin IDENTIFIED BY *  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  DEFAULT TABLESPACE heinz_data
    DATAFILE '+DATA' SIZE 250M AUTOEXTEND ON...

I can, however, successfully create the pluggable database on a CDB created using DBCA. I assume I am missing a component but, I can’t find anything in MOS of use. It would be too easy to say that it’s a bug but, I will log a ticket with MOS at some point this week.

For the sake of continuing my investigation, I will use the new RAC database. This was configured as a Real Application Cluster database – I know, the name isn’t exactly original J

SQL> select name, open_mode from v$database;

NAME    OPEN_MODE
--------- --------------------
RAC     READ WRITE

SQL> CREATE PLUGGABLE DATABASE heinz_pdb ADMIN USER heinz_admin IDENTIFIED BY beans
  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  DEFAULT TABLESPACE heinz_data
    DATAFILE '+DATA' SIZE 250M AUTOEXTEND ON;

Pluggable database created.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                 OPEN_MODE
---------- ------------------------------ ----------
       2 PDB$SEED               READ ONLY
       3 PDB                   MOUNTED
       4 HEINZ_PDB              MOUNTED

SQL> alter pluggable database Heinz_pdb open;

Pluggable database altered.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                 OPEN_MODE
---------- ------------------------------ ----------
       2 PDB$SEED               READ ONLY
       3 PDB                    MOUNTED
       4 HEINZ_PDB              READ WRITE

Create a Pluggable Database from Clone

According to documentation, it’s pretty easy!

SQL> create pluggable database bushbrothers_pdb from heinz_pdb;
create pluggable database bushbrothers_pdb from heinz_pdb
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode

Oops. Which mode is it in now?

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                 OPEN_MODE
---------- ------------------------------ ----------
       2 PDB$SEED               READ ONLY
       3 PDB                    MOUNTED
       4 HEINZ_PDB              MOUNTED

Okay, I see my mistake. It needs to be in READ ONLY mode.

SQL> alter pluggable database heinz_pdb open read only;
alter pluggable database heinz_pdb open read only
*
ERROR at line 1:
ORA-65085: cannot open pluggable database in read only mode

I wasn’t sure why this one gave me an error. I worked around it by switching my session, opening it for read write, shutdown and then started with read only option.

SQL> alter session set container=heinz_pdb;

Session altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-65085: cannot open pluggable database in read only mode

SQL> alter database open;

Database altered.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                 OPEN_MODE
---------- ------------------------------ ----------
       4 HEINZ_PDB                    READ WRITE

SQL> shutdown immediate
Pluggable Database closed.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                 OPEN_MODE
---------- ------------------------------ ----------
       4 HEINZ_PDB                    MOUNTED

SQL> alter database open read only;

Database altered.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                 OPEN_MODE
---------- ------------------------------ ----------
       4 HEINZ_PDB                    READ ONLY

Okay, now I’m all set to try the clone again.

SQL> create pluggable database bushbrothers_pdb from heinz_pdb;
create pluggable database bushbrothers_pdb from heinz_pdb
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level
ORA-19504: failed to create file "+DATA"

Hmm. Interesting. What say you oh alert.log?

Tue Sep 03 00:01:57 2013
create pluggable database bushbrothers_pdb from heinz_pdb
ORA-604 signalled during: create pluggable database bushbrothers_pdb from heinz_pdb...

Nothing useful! I believe the issue lies in using OMF (regardless of the fact that I’m using ASM). For the time being, I will revert to a single instance database that’s not running on ASM to continue with my investigation. Don’t worry; I will revisit this at a later date. At a later date only because it is 12:30 AM here and I have to be at work in the morning J

Starting over!

Create a Pluggable Database from Clone

Let’s find out what’s in this new database.

SQL> select name, open_mode from v$database;

NAME    OPEN_MODE
--------- --------------------
SESAMEST  READ WRITE

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID  DBID NAME                   OPEN_MODE
---------- ---------- ------------------------------ ----------
       2 4061728508 PDB$SEED                   READ ONLY
       3 2760837952 GROVER                     MOUNTED

Datafile locations.

SQL> select file_name from dba_data_Files;

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/SESAMESTREET/datafile/o1_mf_system_8wpfkdq9_.dbf
/home/oracle/oradata/SESAMESTREET/datafile/o1_mf_sysaux_8wpfhcmz_.dbf
/home/oracle/oradata/SESAMESTREET/datafile/o1_mf_undotbs1_8wpfmh17_.dbf
/home/oracle/oradata/SESAMESTREET/datafile/o1_mf_users_8wpfmfwc_.dbf

Issue the create statement again.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID  DBID NAME                   OPEN_MODE
---------- ---------- ------------------------------ ----------
       2 4061728508 PDB$SEED                   READ ONLY
       3 2760837952 GROVER                     READ ONLY

SQL> alter pluggable database grover open read only;

Pluggable database altered.

SQL> create pluggable database oscar from grover;

Pluggable database created.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID  DBID NAME                   OPEN_MODE
---------- ---------- ------------------------------ ----------
       2 4061728508 PDB$SEED                   READ ONLY
       3 2760837952 GROVER                     READ ONLY
       4 1749240269 OSCAR                      MOUNTED

SQL> alter pluggable database oscar open;          

Pluggable database altered.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID  DBID NAME                   OPEN_MODE
---------- ---------- ------------------------------ ----------
       2 4061728508 PDB$SEED                   READ ONLY
       3 2760837952 GROVER                     READ ONLY
       4 1749240269 OSCAR                      READ WRITE

Bingo! That worked!

SQL> alter session set container=oscar;

Session altered.

SQL> select username from dba_users;

USERNAME
--------------------------------------------------------------------------------
SYS
SYSTEM
OLAPSYS
SI_INFORMTN_SCHEMA
PDBADMIN
DVSYS
AUDSYS
GSMUSER
ORDPLUGINS
SPATIAL_WFS_ADMIN_USR
SPATIAL_CSW_ADMIN_USR

USERNAME
--------------------------------------------------------------------------------
XDB
HR
APEX_PUBLIC_USER
GROVER_FAN_1 # User from the Grover Pluggable Database
OE
SYSDG
DIP
OUTLN
SH
ANONYMOUS
CTXSYS

USERNAME
--------------------------------------------------------------------------------
ORDDATA
IX
SYSBACKUP
MDDATA
GSMCATUSER
GSMADMIN_INTERNAL
PM
BI
LBACSYS
SYSKM
XS$NULL

USERNAME
--------------------------------------------------------------------------------
OJVMSYS
APPQOSSYS
ORACLE_OCM
APEX_040200
WMSYS
SCOTT
DBSNMP
ORDSYS
MDSYS
DVF
FLOWS_FILES

44 rows selected.

Just to make sure, lets check the location of the data files.

SQL> set lines 1000
SQL> select file_name from dba_data_files;

FILE_NAME
----------------------------------------------------------------
/home/oracle/oradata/SESAMESTREET/E5744BAEDB960B47E0430100007F20BF/datafile/o1_mf_example_92bt5px5_.dbf
/home/oracle/oradata/SESAMESTREET/E5744BAEDB960B47E0430100007F20BF/datafile/o1_mf_users_92bt5ot7_.dbf
/home/oracle/oradata/SESAMESTREET/E5744BAEDB960B47E0430100007F20BF/datafile/o1_mf_sysaux_92bt4g44_.dbf
/home/oracle/oradata/SESAMESTREET/E5744BAEDB960B47E0430100007F20BF/datafile/o1_mf_system_92bt4g2p_.dbf

Interestingly enough, the GUID is used as the unique identifier of the folders within DB_CREATE_FILE_DEST.

SQL> select con_id, con_uid, guid, name from v$pdbs;

    CON_ID    CON_UID GUID                       NAME
---------- ---------- -------------------------------- ------------------------------
       4 1749240269 E5744BAEDB960B47E0430100007F20BF OSCAR

Conclusion

As big a fan as I am of the command line interface, there seem to be a few issues with the create statements. I plan to revisit these steps at a later date. For now, I did create an SR with Oracle support regarding the creation of a Pluggable Database from a Container Database created via SQL*Plus.

If you have any questions or comments, please feel free to share!

Cheers.

Links:

Create Database via SQL*Plus

Create CDB

Create and Remove Pluggable Database

7 comments

Jamsher · March 31, 2015 - 11:54 AM · Reply→

Thanks for Sharing this post for creating CDB/PDB on ASM. I was trying this from long time..

Thanks & Best Regards
Jamsher
1. maazanjum · March 31, 2015 - 1:00 PM · Reply→
  
  Jamsher,
  
  I hope this is still relevant. I posted this in 2013 prior to the latest 12.1.0.2.0 release. From what I understand there were numerous bug fixes and several enhancements to the product.
  
  Cheers,
  Maaz
Jamsher · March 31, 2015 - 2:44 PM · Reply→

Hi Maaz,

Even i got the same error while creating PDB in CDB which is manually created and I also tried to create PDB with DBCA option in same CDB which is manually created also encounter same error.

But same command working in CDB created from DBCA.

SQL> CREATE PLUGGABLE DATABASE PDB1 ADMIN USER pdb_admin IDENTIFIED BY oracle
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
DEFAULT TABLESPACE USERS
DATAFILE ‘+DATA1’ SIZE 250M AUTOEXTEND ON; 2 3 4
CREATE PLUGGABLE DATABASE PDB1 ADMIN USER pdb_admin IDENTIFIED BY oracle
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

Can you please share the Reason and Solution provided by Oracle.

Thanks & Regards
Jamsher
1. maazanjum · March 31, 2015 - 5:28 PM · Reply→
  
  Which version of the database are you installing? 12.1.0.1.0?
Jamsher · April 1, 2015 - 11:02 AM · Reply→

Yes Same

SQL> select banner from v$version;

BANNER
——————————————————————————–
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
PL/SQL Release 12.1.0.1.0 – Production
CORE 12.1.0.1.0 Production
TNS for Linux: Version 12.1.0.1.0 – Production
NLSRTL Version 12.1.0.1.0 – Production
1. maazanjum · April 1, 2015 - 1:58 PM · Reply→
  
  I want to say this is likely fixed in the 12.1.0.2 release. Why not go for that instead?
Jamsher · April 2, 2015 - 7:54 AM · Reply→

Thanks for Reply Maazan, Sure I will upgrade. Currently I am just practicing Oracle 12C.
And Really thanks for this Blog help me a lot.

Regards
Jamsher