Monday, July 15, 2013

Learn Multitenant - CREATE DATABASE... blah blah

It's time to learn a little bit about Oracle Database 12c. Today, I chose to learn "Creating and Configuring a CDB" and pointed at "CREATE DATABASE" for creating CDB.
First of all, I learned I must set compatible ='12.0.0' and "ENABLE_PLUGGABLE_DATABASE=TRUE" for initialization parameters.
[oracle@test12c ~]$ echo $ORACLE_SID
newcdb
[oracle@test12c ~]$ vi /u01/app/oracle/product/12.1.0/dbhome_1/dbs/initnewcdb.ora
[oracle@test12c ~]$ cat  /u01/app/oracle/product/12.1.0/dbhome_1/dbs/initnewcdb.ora
db_name='newcdb'
memory_target=1G
processes = 300
audit_file_dest='/u01/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=4G
dispatchers='(PROTOCOL=TCP) (SERVICE=NEWCDBXDB)'
open_cursors=1000
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ('/u01/app/oracle/oradata/newcdb/ora_control1.ctl','/u01/app/oracle/oradata/newcdb/ora_control2.ctl')
compatible ='12.0.0'
ENABLE_PLUGGABLE_DATABASE=TRUE
[oracle@test12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 14:56:45 2013

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

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2296576 bytes
Variable Size             713032960 bytes
Database Buffers          348127232 bytes
Redo Buffers                5480448 bytes
SQL> show parameter control_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/newcdb
                                                 /ora_control1.ctl, /u01/app/or
                                                 acle/oradata/newcdb/ora_contro
                                                l2.ctl
Then, I learned "CREATE DATABASE ... ENABLE PLUGGABLE DATABASE ...".
Note: I used example from Oracle Document.
SQL> -- Make sure for /u01/app/oracle/admin/orcl/adump, /u01/app/oracle/oradata/newcdb and /u01/app/oracle/oradata/newcdb/pdbseed folders --                                            
SQL> CREATE DATABASE newcdb
  USER SYS IDENTIFIED BY password
  USER SYSTEM IDENTIFIED BY password
  LOGFILE GROUP 1 ('/u01/app/oracle/oradata/newcdb/redo01a.log')
             SIZE 50M BLOCKSIZE 512,
          GROUP 2 ('/u01/app/oracle/oradata/newcdb/redo02a.log')
             SIZE 50M BLOCKSIZE 512,
          GROUP 3 ('/u01/app/oracle/oradata/newcdb/redo03a.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/newcdb/system01.dbf'
        SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
      SYSAUX DATAFILE '/u01/app/oracle/oradata/newcdb/sysaux01.dbf'
        SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
      DEFAULT TABLESPACE deftbs
         DATAFILE '/u01/app/oracle/oradata/newcdb/deftbs01.dbf'
         SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
      DEFAULT TEMPORARY TABLESPACE tempts1
         TEMPFILE '/u01/app/oracle/oradata/newcdb/temp01.dbf'
         SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
      UNDO TABLESPACE undotbs1
         DATAFILE '/u01/app/oracle/oradata/newcdb/undotbs01.dbf'
         SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
      ENABLE PLUGGABLE DATABASE
        SEED
        FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/newcdb/',
                             '/u01/app/oracle/oradata/newcdb/pdbseed/')
        SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        SYSAUX DATAFILES SIZE 100M
  USER_DATA TABLESPACE usertbs
    DATAFILE '/u01/app/oracle/oradata/newcdb/pdbseed/usertbs01.dbf'
    SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
/
 Database created.
In document, I had to perform by "catcdb.sql" script.
SQL> @?/rdbms/admin/catcdb.sql
SP2-0310: unable to open file "/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcdb.sql"
SQL> !ls -la /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcdb.sql
ls: cannot access /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcdb.sql: No such file or director
There has not had "catcdb.sql" script in /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin path.  Oops!
I read on Oracle document again. 
1) Creating the Data Dictionary.
http://docs.oracle.com/cd/E16655_01/server.121/e17615/scripts001.htm
http://docs.oracle.com/cd/E16655_01/server.121/e17636/create.htm#ADMIN11085
catalog.sql    Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms. (SYSDBA)
catproc.sql    Runs all scripts required for or used with PL/SQL. (SYSDBA)
pupbld.sql    Required for SQL*Plus. Enables SQL*Plus to disable commands by user. (SYSTEM)

2) Running Oracle-Supplied SQL Scripts in a CDB.
http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_admin.htm#ADMIN14072
So, I used "catcon.pl" script to perform sql scripts for Multitenant container database.
[oracle@test12c ~]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/
[oracle@test12c admin]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin
[oracle@test12c admin]$ perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b catalog catalog.sql
[oracle@test12c admin]$ perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b catproc catproc.sql
[oracle@test12c admin]$ perl catcon.pl -u SYSTEM -d $ORACLE_HOME/sqlplus/admin -b pupbld pupbld.sql
Enter Password: ********
Then, checked database status.
[oracle@test12c admin]$ cd ~
[oracle@test12c ~]$ sqlplus  / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 17:38:46 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select cdb from v$database;

CDB
---
YES

SQL> select PDB_NAME, STATUS, CON_ID from cdb_pdbs;

PDB_NAME                       STATUS            CON_ID
------------------------------ ------------- ----------
PDB$SEED                       NORMAL                 1

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY

SQL>
-- It works! Really? --
Not Sure ^___________^ So, tested to create pluggable database.
SQL>  CREATE PLUGGABLE DATABASE testpdb ADMIN USER admin IDENTIFIED BY password FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/newcdb/pdbseed/','/u01/app/oracle/oradata/newcdb/testpdb/');

Pluggable database created.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
TESTPDB                        MOUNTED

SQL> alter pluggable database TESTPDB open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
TESTPDB                        READ WRITE

SQL> select file_name, tablespace_name, con_id from cdb_data_files;
FILE_NAME                                               TABLESPACE_NAME                    CON_ID
------------------------------------------------------- ------------------------------ ----------
/u01/app/oracle/oradata/newcdb/system01.dbf             SYSTEM                                  1
/u01/app/oracle/oradata/newcdb/sysaux01.dbf             SYSAUX                                  1
/u01/app/oracle/oradata/newcdb/undotbs01.dbf            UNDOTBS1                                1
/u01/app/oracle/oradata/newcdb/deftbs01.dbf             DEFTBS                                  1
/u01/app/oracle/oradata/newcdb/testpdb/system01.dbf     SYSTEM                                  3
/u01/app/oracle/oradata/newcdb/testpdb/sysaux01.dbf     SYSAUX                                  3
/u01/app/oracle/oradata/newcdb/testpdb/deftbs01.dbf     DEFTBS                                  3
/u01/app/oracle/oradata/newcdb/testpdb/usertbs01.dbf    USERTBS                                 3
/u01/app/oracle/oradata/newcdb/pdbseed/system01.dbf     SYSTEM                                  2
/u01/app/oracle/oradata/newcdb/pdbseed/sysaux01.dbf     SYSAUX                                  2
/u01/app/oracle/oradata/newcdb/pdbseed/deftbs01.dbf     DEFTBS                                  2
/u01/app/oracle/oradata/newcdb/pdbseed/usertbs01.dbf    USERTBS                                 2

12 rows selected.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 15-JUL-2013 17:52:28

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test12c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                15-JUL-2013 14:06:41
Uptime                    0 days 3 hr. 45 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/test12c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test12c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "NEWCDBXDB" has 1 instance(s).
  Instance "newcdb", status READY, has 1 handler(s) for this service...
Service "newcdb" has 1 instance(s).
  Instance "newcdb", status READY, has 1 handler(s) for this service...
Service "testpdb" has 1 instance(s).
  Instance "newcdb", status READY, has 1 handler(s) for this service...

The command completed successfully

SQL>  connect admin/password@test12c:1521/testpdb
Connected.
SQL> show con_name
CON_NAME
------------------------------
TESTPDB
Look Good. Mission Done! However, I will wait for "catcdb.sql" script from Oracle.

2 comments:

Suvendu Samal said...

Thanks for sharing. Indeed it's a great help.

Surachart Opun said...

Today, reading some presentation.
> Execute catalog.sql and other post-creation scripts.
?/rdbms/admin/catalog.sql
?/rdbms/admin/catblock.sql
?/rdbms/admin/catproc.sql
?/rdbms/admin/catoctk.sql
?/rdbms/admin/owminst.plb
?/sqlplus/admin/pupbld.sql