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:
 

 
Advertisements

7 comments

  1. Jamsher · · Reply

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

    Thanks & Best Regards
    Jamsher

    1. 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

  2. Jamsher · · 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. Which version of the database are you installing? 12.1.0.1.0?

  3. Jamsher · · 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. I want to say this is likely fixed in the 12.1.0.2 release. Why not go for that instead?

  4. Jamsher · · 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Musings

Things I see and learn!

Thoughts from James H. Lui

If you Care a Little More, Things Happen. Bees can be dangerous. Always wear protective clothing when approaching or dealing with bees. Do not approach or handle bees without proper instruction and training.

bdt's oracle blog

Sharing experience (by Bertrand Drouvot)

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Vishal desai's Oracle Blog

Just another WordPress.com weblog

%d bloggers like this: