Oracle 12c: Manual container database creation in SQL*Plus

July 4th, 2013 | Posted in 12c, Blog | 1 Comment

The following describes the manual creation of a Container database (CDB) using sql*plus.

In the following post:

-The database named “CDB” is the container root database we are going to create.
-We use Oracle Managed Files (OMF) for the new Container database (CDB).

1- Prerequisites :

Before you can create a new CDB, some prerequisites must be met.

1.1 Refer to the following link:
Prerequisites for a Multitenant Environment

1.2 Create all directories:
For manual creation method of a CDB, all directories must be created on the server (FS or ASM).

1.3 Create parameter file:

We have to create an initialization parameter file , specify the number and location of control files with the CONTROL_FILES parameter, The DB_NAME parameter must be specified and enable_pluggable_database must be set to true.

Example of parameter file ;

sandbox1(orawiss):/home/oracle>cat /tmp/pfile_CDB.ora
*.audit_file_dest='/opt/app/oracle/admin/cdb/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='+DATA/CDB/CONTROLFILE/control.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='CDB'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4800m
*.diagnostic_dest='/opt/app/oracle'
*.enable_pluggable_database=true
*.open_cursors=300
*.pga_aggregate_target=200m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=600m
*.undo_tablespace='UNDOTBS1'
sandbox1(orawiss):/home/oracle>

2- Steps Using Sql*Plus to Create a CDB:

2.1 Start the new CDB in NOMOUNT mode:

Set the environment parameters ORACLE_SID, ORACLE_HOME etc …

sandbox1(CDB):/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 4 10:07:21 2013

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

Connected to an idle instance.

SQL> STARTUP NOMOUNT pfile='/tmp/pfile_CDB.ora';
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2291472 bytes
Variable Size             180357360 bytes
Database Buffers          436207616 bytes
Redo Buffers                7471104 bytes

2.2 CREATE DATABASE Statement:

Create the CDB using CREATE DATABASE Statement; Note the following options / parameters:

- SEED FILE_NAME_CONVERT: This clause specifies how to generate the names of the seed’s files using the names of root’s files. This clause must be added to the “CREATE DATABASE” statement.

- PDB_FILE_NAME_CONVERT Initialization Parameter: specify the names and locations of the seed’s files. The seed can be used as a template to create new Pluggable databases (PDB).


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

Database created.

SQL> 

You may have the following error during CREATE DATABASE statement:

ORA-65005: missing or invalid file name pattern for file - string
Cause: Either source or replacement file name pattern was missing or invalid in a SOURCE_FILE_NAME_CONVERT or FILE_NAME_CONVERT clause.
Action: Correct the SOURCE_FILE_NAME_CONVERT or FILE_NAME_CONVERT clause and reissue the statement.

refer to LINK for more details about the error.

2.3 Open the pluggable database:


SQL> alter session set "_oracle_script"=true;

Session altered.

SQL> alter pluggable database pdb$seed close;

Pluggable database altered.

SQL> alter pluggable database pdb$seed open;
alter pluggable database pdb$seed open
*
ERROR at line 1:
ORA-00704: bootstrap process failure
ORA-00942: table or view does not exist

-- repeat again 
SQL> alter pluggable database pdb$seed open;

Pluggable database altered.

2.5 Run the following sql files:

Enter the following in SQL*Plus to run the scripts:

@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catblock.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/rdbms/admin/catoctk.sql
@$ORACLE_HOME/rdbms/admin/owminst.plb
@$ORACLE_HOME/sqlplus/admin/pupbld.sql

If you want to add more features and options you may refer to the following document: HERE(Step 12)

SQL> select open_mode from v$database;

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

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

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4062472313 PDB$SEED                       READ WRITE

1 row selected.

SQL>
SQL> SELECT NAME from  v$database;

NAME
---------
CDB

1 row selected.

SQL>

2.6 Configure Enterprise Manager Express for a CDB:

Connect AS SYSDBA and run;

exec DBMS_XDB_CONFIG.SETHTTPSPORT(https_port_number);

The URL will be: https://database_hostname:https_port_number/em/

References:

Doc 12cR1

Doc 12cR1



One Comment to “Oracle 12c: Manual container database creation in SQL*Plus”

  1. Brian says:

    I created the cdb manually using the instruction, but then I tried to create pdb within in, it failed. have you tried that?

    Thanks


Leave a Comment





Polls

which oracle topic interests you most?

View Results

Loading ... Loading ...


Oracle Class Tweets


Recent Posts


Recent Comments

  • Sergio: Hi, Where is declared the function agent_clean() ? I dont see agent_clean() definition !! regards, Sergio
  • Sikandar: I do salute you brother, This is very………..nti mes helpful and correct documents. I...
  • Karthik Singh: Thanks for sharing a useful article. Looks like you have installed weblogic 10.3.4. Then shall I...
  • Joseph: Paul, Here target is from which database we are taking cloning. and auxilary is the cloning database. Wissem,...
  • sundar: In Doc ID 1472171.1 it says that the snapshot control file should be in a shared location for RAC databases....
  • Taoqir: Hello, This is a wonderfull and very helping material.now I am going to configure Data Guard. Thanks
  • Adrian Iriarte: Saludos, Que buen articulo, casi siempre al momento de querer implementar soluciones para el...
  • Patricia: Hi Wissem, is there a workaround for this? I mean I want to use the sqlnet.authentication_services =none,...
  • Brian: I created the cdb manually using the instruction, but then I tried to create pdb within in, it failed. have...
  • Farmer Johnson: Hi Wissem. How did you avoid the error you had previsouly encountered: “sudo opatch auto...
  • Héctor Moro: Querido Nelson; Después de entrevistar a mas de 50 profesionales de primer nivel en Uruguay, al fin te...
  • What is NO2 Maximus: I genuinely apрreciated your site! You shoulԁ update it wiϳth new info!
  • Nav: Indeed helpful, to the point article..
  • Koti: Can you please explain all the sections of AWR report in RAC. I mean to ask output produced by awrgrpt.sql...
  • Himanshu: Nice post.can you please comment on this Why RMAN restored the DATA_REORTING datafie to +DATA diskgroup?
  • gabriel: what version of sqldeveloper support plugable database?
  • Kal: Quality – thanks…
  • wissem: Well , like I mentioned in the post, delete service and add service using IP.
  • Kelly: were you able to solve this? Thanks!
  • wissem: Hello, You can buy the book from PakT: http://www.packtpub.com/oracle -data-guard-11gr2-administr...

Oracle-Class Forums Latest Activity