Tuesday, September 3, 2013
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:
Subscribe to:
Post Comments
(
Atom
)
No comments :
Post a Comment