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

July 4th, 2013 | Posted in 12c, Blog | 2 Comments

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



2 Comments 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

  2. Leszek K says:

    *.db_create_file_dest=’+DATA’

    create database command – why do you use full datafile name ?


Leave a Comment





Polls

which oracle topic interests you most?

View Results

Loading ... Loading ...


Oracle Class Tweets


Recent Posts


Recent Comments

  • T Robinson: In regard to the comment “In our case, RMAN didn’t found anything corrupted.” Please notice...
  • iftikhar: if we backup in multiple backupsets and will be command for restoring for more than one backupsets ,
  • Abey: Very helpful article.Thanks a lot. Can we follow this on oracle DG enabled deployment also?
  • Mauricio Daher: Need help configuring SBT for cloud backups. Does not seem to work for 12c. Thanks! RMAN>...
  • Leszek K: *.db_create_file_dest=’+ DATA’ create database command – why do you use full datafile...
  • Prabhakar Kumar: Thanks buddy!!! It really helps to DBAs who are novice to 12c ASM feature.
  • Wubeshet: I am getting the ORA-07445 This morning on the trace file. The following is the symptom of the problem. It...
  • Lakshmi N: Hi Thanks for the post. Just curious, is there a way to restore to a point wher my db is in no archive...
  • Shivakkumar P: This is really very good article. Iam learning Oracle 12c I used this to replicate testing from...
  • Uday: Hi Wissem, Once we apply the level 1 incremental backup to datafile copy it is no longed required. My question...
  • avnish: Can I set up active duplicate database in 11g standard edition (11.2.0.1)
  • Jaya V: hi, am having rman backup. i need to restore on it, from test instance. But the test instance name not same...
  • Jaya: hi, am having rman backup. i need to restore on it, from test instance. But the test instance name not same...
  • wissem: SQL Developer 4.0
  • oracleman consulting: great info thanks for sharing
  • Mike: Very nice article!! Thanks for sharing!!
  • yathish: I really appreciate if you can send me, if you have tutorial for 11g (11.2.0.1) RAC & ASM (prefer to be...
  • Farrukh Kamal: Fahad, I’m interesting in pursuing remote employment with Pythian. What tips would you suggest...
  • pavankumar: for the error it need to set env correctly http://orcl11gdba.blogspot.in/ 2014/08/asmcmd-08102-no-con...
  • wissem: Post result off; crsctl check has crsctl check crs

Oracle-Class Forums Latest Activity