Create your CDB without DBCA

Oracle Database 12c – Create your CDB without DBCA

Last article about creating new Virtual Machine on Virtual Box with Oracle Linux 6.5 give us background for further tests with Oracle Database 12c. Next article about installing Oracle Linux and Oracle Database software prepared environment. Now it’s time to do something with it.

Some time ago I’ve spend some time on failed attempt to manually create container database using install media 12.1.0.1.0 – there were some problems with starting the script called: @?/rdbms/admin/catcdb.sql – this script was not included in this version. I had to upgrade to 12.1.0.1.2. One of the issues resolved with this version is:

“17316776I $OH/rdbms/admin/catcdb.sql is missing from 12c release”

With version 12.1.0.2.0 I’m counting that manual database creation does not require applying any patch like “18031528 – 12.1.0.1.3 (Apr 2014) Database Patch Set Update (PSU) (Doc ID 18031528.8)” and all will be just fine.

Introduction

Starting point for this article is presented in a table below.

Attribute Value
Software Oracle Linux 6.5 (64 bit for x86)


Oracle Database 12.1.0.2.0 (64 bit for Linux 6.5)


Installation and configuration of this software has been described in Oracle Database 12c Installation article.

Preparation

Main reason for this guide is to check how Oracle handle couple different scenarios with new clause of create database statement.

Here you can find full description of the new syntax: enable pluggable database.

1. I’m going to check if the database files for seed database will be renamed if I’ll use seed clause with file_name_convert option. This is the highest priority parameter during database creation – this is why setting Oracle Managed Files with db_create_file_dest parameter should not be applied.

2. Then, I’ll set pdb_file_name_convert and use Oracle Managed Files – this should enforce Oracle to do not use pdb_file_name_parameter and use OMF instead as described in Administrator’s Guide.

3. Last test will be related to pdb_file_name_convert initialization parameter with OMF put on hold.

During tests I’m going to check, if dedicated values for datafiles clauses apply for new PDB’s created as a clone of seed databases. For this very last test after creating the CDB I’m going always use the same method to do not confuse two topics: creating container database and creating pluggable database. Last topic will be described in details in next article which will discuss methods to create pluggable database inside already created container database.

In the end we will enable new Database Express tool to see nice and shiny charts about our new database.

From beginning to an end I’m going to use oracle account which is the one on which I’ve installed my Oracle Database software on my odone host (with one exception stated below).

Readkey problem

Before starting database creation you should check and install (if needed) one more module for perl – use root account for this installation. This is not a requirement for installation of Oracle Database binaries but without it you cannot use catcdb.sql script (*or you can use alternative solution presented below). Check if you’ve got ReadKey module. If not then check from where you can get it. After that checks you can install right package to get ReadKey module.

perl -MTerm::ReadKey -e "print \"Module installed.\\n\";"
yum whatprovides "perl(Term::ReadKey)"
perl-TermReadKey-2.30-13.el6.x86_64 : A perl module for simple terminal control
Repo        : local-6.5-x64
Matched from:
Other       : perl(Term::ReadKey)
yum install perl-TermReadKey
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package perl-TermReadKey.x86_64 0:2.30-13.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

============================================================================================================================================================================
 Package                                      Arch                               Version                                    Repository                                 Size
============================================================================================================================================================================
Installing:
 perl-TermReadKey                             x86_64                             2.30-13.el6                                local-6.5-x64                              30 k

Transaction Summary
============================================================================================================================================================================
Install       1 Package(s)

Total download size: 30 k
Installed size: 62 k
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : perl-TermReadKey-2.30-13.el6.x86_64                                                                                                                      1/1
  Verifying  : perl-TermReadKey-2.30-13.el6.x86_64                                                                                                                      1/1

Installed:
  perl-TermReadKey.x86_64 0:2.30-13.el6

Complete!

Error which you can get if you don’t install this package (or use alternate solution) with 12.1.0.2.0 Oracle Database version can look like this one:

Can't locate Term/ReadKey.pm in @INC (@INC contains: /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 . /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/) at /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pm line 189.
BEGIN failed--compilation aborted at /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pm line 189.
Compilation failed in require at /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl line 94.
BEGIN failed--compilation aborted at /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl line 94.

This is a response for trying to execute script with container database dictionary objects: @?/rdbms/admin/catcdb.sql. Notice your working directory before starting this script – you can use another session to find log files from database dictionary creation process inside this directory. You can check current working directory with pwd command in your Linux shell.

Alternative solution for the readkey problem

As an alternative way to deal with perl problem you can change your path variable to look for library inside Oracle Database software installation.

export PATH=$ORACLE_HOME/bin:/u01/app/oracle/product/12.1.0/dbhome_1/perl/bin/:$PATH

After this command my environment for a new database creation is:

echo $PATH
/u01/app/oracle/product/12.1.0/dbhome_1/bin:/u01/app/oracle/product/12.1.0/dbhome_1/perl/bin/:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/12.1.0/dbhome_1/bin
env | grep ORACLE_
ORACLE_SID=beecdb
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
pwd
/u01/app/oracle/product/12.1.0/dbhome_1

Scenarios

For all attempts we will use the same template where you always do the same things but slightly change create database command and/or spfile. In following chapters I’ll show comparison in effects instead of creating couple different guides for each option.

Environment

Before you’ll begin add proper entry in your /etc/oratab file and set your environment with oraenv script.

vi /etc/oratab
beecdb:/u01/app/oracle/product/12.1.0/dbhome_1:N

Start oraenv script and input beecdb when asked for a database name.

. oraenv

As a result you should have variables prepared similar to those that you see below.

env | grep ORACLE_
echo $PATH
ORACLE_SID=beecdb
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
echo $PATH
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/12.1.0/dbhome_1/bin

Password file

With 12c we’ve got new format of the password file – new operating system privileges can be authorized by password file and here is an example of creating password file. Our database will be called beecdb.

cd $ORACLE_HOME/dbs
orapwd file=orapwbeecdb entries=5 format=12 sysbackup=y sysdg=y syskm=y ignorecase=n force=y

You’ll be asked for a password for each user related to operating system privilege. Input your favourite password like “manager” or similar to do not lost or forget it.

Initialization parameter file

Create and edit your pfile for a new container database. Most of the time only basic parameters. Highlighted lines are considered variable between scenarios. Take a close look at them. New parameter enable_pluggable_database need to be set up to true in order to get pluggable database and to do not fail on create database statement with clause that enable container database.

vi /u01/app/oracle/product/12.1.0/dbhome_1/dbs/initbeecdb.ora
db_create_file_dest='/u02/oradata/'
db_create_online_log_dest_1='/u02/oradata/'
memory_max_target=2048m
memory_target=2048m
db_name=beecdb
db_unique_name=beecdb
instance_name=beecdb
db_recovery_file_dest='/u03/fra/'
db_recovery_file_dest_size=32g
diagnostic_dest=/u01/app/oracle
enable_pluggable_database=true
compatible=12.1.0.2.0
remote_login_passwordfile=EXCLUSIVE
undo_tablespace=undotbs
audit_file_dest=/u01/app/oracle/admin/beecdb/adump
audit_trail=db
processes=300
db_block_size=8192
log_archive_format=%t_%s_%r.dbf
open_cursors=500

Create any of mentioned directories or adjust your parameters to meet your environment directory structure. For me there is no audit trail directory and I need to create it.

mkdir -p /u01/app/oracle/admin/beecdb/adump

For control_files we will use Oracle Managed Files – based on db_create_online_log_dest_1 were log files will be created too. No copy to db_create_file_dest nor db_recovery_file_dest will be done as db_create_online_log_dest_1 precedes those two parameters in Oracle Managed Files. One copy of control file will be created though.

After logging to inactive database as sysdba create spfile from pfile.

sqlplus / as sysdba
create spfile from pfile;

Startup your instance to nomount state.

startup nomount

Now we’re ready to enter first create database statement. Below you can find scenarios that were noticed in an introduction.

file_name_convert in seed clause

Sample create database command which will be used for first scenario. Highlighted lines are the main reason for creating this article.

create database beecdb
  user sys identified by manager
  user system identified by manager
  maxdatafiles 1024
  character set al32utf8
  national character set al16utf16
  set default smallfile tablespace
  extent management local
  datafile size 768m autoextend on next 32m maxsize unlimited
  sysaux datafile size 576m autoextend on next 32m maxsize unlimited
  default tablespace users datafile size 8m autoextend off
  default temporary tablespace temp tempfile size 256m autoextend on next 32m maxsize unlimited
  undo tablespace undotbs datafile size 128m autoextend on next 32m maxsize unlimited  
  logfile group 1 size 256m blocksize 512,
          group 2 size 256m blocksize 512,
		  group 3 size 256m blocksize 512
  maxlogfiles 16
  maxlogmembers 3
  maxloghistory 1
  archivelog
  enable pluggable database
  seed
  file_name_convert = ('/u02/oradata/BEECDB/','/u02/oradata/BEECDB/pdb_seed/');

Check filenames to see, what happens on database side.

col name for a70
select name, con_id from v$datafile order by 2,1;
NAME                                                                       CON_ID
---------------------------------------------------------------------- ----------
/u02/oradata/BEECDB/datafile/o1_mf_sysaux_b2zdsvwc_.dbf                         1
/u02/oradata/BEECDB/datafile/o1_mf_system_b2zdskfz_.dbf                         1
/u02/oradata/BEECDB/datafile/o1_mf_undotbs_b2zdt0vs_.dbf                        1
/u02/oradata/BEECDB/datafile/o1_mf_users_b2zdt1k8_.dbf                          1
/u02/oradata/BEECDB/0495D800B96D0771E0530C38A8C00F73/datafile/o1_mf_sy          2
saux_b2zdsyns_.dbf

/u02/oradata/BEECDB/0495D800B96D0771E0530C38A8C00F73/datafile/o1_mf_sy          2
stem_b2zdspy0_.dbf

/u02/oradata/BEECDB/0495D800B96D0771E0530C38A8C00F73/datafile/o1_mf_us          2
ers_b2zdt1ll_.dbf

No file name conversion occurs. According to documentation seed clause file_name_convert should be used with priority one. Instead OMF handle file names and directories. Drop this database and try again. Use drop procedure presented below each time after the test.

Drop database

Do not leave sql*plus session – start with shutdown and startup in restrict session mode.

shutdown immediate;
startup restrict mount
drop database;

Re-login to database and create spfile from pfile.

exit
sqlplus / as sysdba
!rm -rf /u02/oradata/BEECDB
!rm -rf /u03/fra/BEECDB
!mkdir -p /u02/oradata/BEECDB/pdb_seed
create spfile from pfile;
startup nomount

Now we’re ready for another attempt.

Manual paths and conversion

In this attempt I’m going to manually set paths and file names for system, sysaux and users tablespaces. This way Oracle can be aware of paths and apply file name conversion. This time remember to create directories first for database files (target conversion directory) – you can use your sql*plus session with exclamation sign before shell command.

create database beecdb
  user sys identified by manager
  user system identified by manager
  maxdatafiles 1024
  character set al32utf8
  national character set al16utf16
  set default smallfile tablespace
  extent management local
  datafile '/u02/oradata/BEECDB/system01.dbf' size 768m
    autoextend on next 32m maxsize unlimited
  sysaux datafile '/u02/oradata/BEECDB/sysaux01.dbf' size 576m
    autoextend on next 32m maxsize unlimited
  default tablespace users datafile '/u02/oradata/BEECDB/users01.dbf' size 8m
    autoextend off
  default temporary tablespace temp tempfile '/u02/oradata/BEECDB/temp01.dbf' size 256m
    autoextend on next 32m maxsize unlimited
  undo tablespace undotbs datafile '/u02/oradata/BEECDB/undo01.dbf' size 128m
    autoextend on next 32m maxsize unlimited  
  logfile group 1 size 256m blocksize 512,
          group 2 size 256m blocksize 512,
		  group 3 size 256m blocksize 512
  maxlogfiles 16
  maxlogmembers 3
  maxloghistory 1
  archivelog
  enable pluggable database
  seed
  file_name_convert = ('/u02/oradata/BEECDB/','/u02/oradata/BEECDB/pdb_seed/');

Again, check filenames.

col name for a70
select name, con_id from v$datafile order by 2,1;
NAME                                                                       CON_ID
---------------------------------------------------------------------- ----------
/u02/oradata/BEECDB/sysaux01.dbf                                                1
/u02/oradata/BEECDB/system01.dbf                                                1
/u02/oradata/BEECDB/undo01.dbf                                                  1
/u02/oradata/BEECDB/users01.dbf                                                 1
/u02/oradata/BEECDB/pdb_seed/sysaux01.dbf                                       2
/u02/oradata/BEECDB/pdb_seed/system01.dbf                                       2
/u02/oradata/BEECDB/pdb_seed/users01.dbf                                        2
select name, con_id from v$tempfile order by 2,1;
NAME                                                                       CON_ID
---------------------------------------------------------------------- ----------
/u02/oradata/BEECDB/temp01.dbf                                                  1
/u02/oradata/BEECDB/pdb_seed/temp01.dbf                                         2

Now I am pleased with the results. Let’s cleanup and start over with next option – pdb path conversion initialization parameter.

Path conversion through initialization parameter

The plan is to setup pdb_file_name_convert with OMF and see, that it has no influence on path names for seed database and then remove OMF and create database again. First approach fully automatic. Edit initialization parameter file first. If you’re still in sql*plus after cleanup then exit from session and then edit initialization parameter file.

vi /u01/app/oracle/product/12.1.0/dbhome_1/dbs/initbeecdb.ora
db_create_file_dest='/u02/oradata/'
db_create_online_log_dest_1='/u02/oradata/'
pdb_file_name_convert=('/u02/oradata/BEECDB/','/u02/oradata/BEECDB/pdb_seed/')
memory_max_target=2048m
memory_target=2048m
db_name=beecdb
db_unique_name=beecdb
instance_name=beecdb
db_recovery_file_dest='/u03/fra/'
db_recovery_file_dest_size=32g
diagnostic_dest=/u01/app/oracle
enable_pluggable_database=true
compatible=12.1.0.2.0
remote_login_passwordfile=EXCLUSIVE
undo_tablespace=undotbs
audit_file_dest=/u01/app/oracle/admin/beecdb/adump
audit_trail=db
processes=300
db_block_size=8192
log_archive_format=%t_%s_%r.dbf
open_cursors=500

And now we can proceed with database creation as usually – if your database is in nomount state then you need to shutdown this instance, create spfile from pfile and startup with nomount database, then run command presented below.

create database beecdb
  user sys identified by manager
  user system identified by manager
  maxdatafiles 1024
  character set al32utf8
  national character set al16utf16
  set default smallfile tablespace
  extent management local
  datafile size 768m autoextend on next 32m maxsize unlimited
  sysaux datafile size 576m autoextend on next 32m maxsize unlimited
  default tablespace users datafile size 8m autoextend off
  default temporary tablespace temp tempfile size 256m
    autoextend on next 32m maxsize unlimited
  undo tablespace undotbs datafile size 128m
    autoextend on next 32m maxsize unlimited  
  logfile group 1 size 256m blocksize 512,
          group 2 size 256m blocksize 512,
		  group 3 size 256m blocksize 512
  maxlogfiles 16
  maxlogmembers 3
  maxloghistory 1
  archivelog
  enable pluggable database;

Check filenames.

col name for a70
select name, con_id from v$datafile order by 2,1;
NAME                                                                       CON_ID
---------------------------------------------------------------------- ----------
/u02/oradata/BEECDB/datafile/o1_mf_sysaux_b2zfmsxb_.dbf                         1
/u02/oradata/BEECDB/datafile/o1_mf_system_b2zfmn8q_.dbf                         1
/u02/oradata/BEECDB/datafile/o1_mf_undotbs_b2zfmy2y_.dbf                        1
/u02/oradata/BEECDB/datafile/o1_mf_users_b2zfmz90_.dbf                          1
/u02/oradata/BEECDB/049609C797090927E0530C38A8C05C80/datafile/o1_mf_sy          2
saux_b2zfmtxb_.dbf

/u02/oradata/BEECDB/049609C797090927E0530C38A8C05C80/datafile/o1_mf_sy          2
stem_b2zfmoto_.dbf

/u02/oradata/BEECDB/049609C797090927E0530C38A8C05C80/datafile/o1_mf_us          2
ers_b2zfmzbv_.dbf
select name, con_id from v$tempfile order by 2,1;
NAME                                                                       CON_ID
---------------------------------------------------------------------- ----------
/u02/oradata/BEECDB/datafile/o1_mf_temp_b2zfmz1n_.tmp                           1
/u02/oradata/BEECDB/049609C797090927E0530C38A8C05C80/datafile/o1_mf_te          2
mp_b2zfmz2d_.tmp

According to documentation this initialization parameter is 3rd in order. This way this output is as expected it should be. Let’s cleanup and execute another create database command. We’re going to use the same command as in previous section with names for database files.

create database beecdb
  user sys identified by manager
  user system identified by manager
  maxdatafiles 1024
  character set al32utf8
  national character set al16utf16
  set default smallfile tablespace
  extent management local
  datafile '/u02/oradata/BEECDB/system01.dbf' size 768m
    autoextend on next 32m maxsize unlimited
  sysaux datafile '/u02/oradata/BEECDB/sysaux01.dbf' size 576m
    autoextend on next 32m maxsize unlimited
  default tablespace users datafile '/u02/oradata/BEECDB/users01.dbf' size 8m
    autoextend off
  default temporary tablespace temp tempfile '/u02/oradata/BEECDB/temp01.dbf' size 256m
    autoextend on next 32m maxsize unlimited
  undo tablespace undotbs datafile '/u02/oradata/BEECDB/undo01.dbf' size 128m
    autoextend on next 32m maxsize unlimited  
  logfile group 1 size 256m blocksize 512,
          group 2 size 256m blocksize 512,
		  group 3 size 256m blocksize 512
  maxlogfiles 16
  maxlogmembers 3
  maxloghistory 1
  archivelog
  enable pluggable database;

Check filenames.

col name for a70
select name, con_id from v$datafile order by 2,1;
NAME                                                                       CON_ID
---------------------------------------------------------------------- ----------
/u02/oradata/BEECDB/sysaux01.dbf                                                1
/u02/oradata/BEECDB/system01.dbf                                                1
/u02/oradata/BEECDB/undo01.dbf                                                  1
/u02/oradata/BEECDB/users01.dbf                                                 1
/u02/oradata/BEECDB/pdb_seed/sysaux01.dbf                                       2
/u02/oradata/BEECDB/pdb_seed/system01.dbf                                       2
/u02/oradata/BEECDB/pdb_seed/users01.dbf                                        2
select name, con_id from v$tempfile order by 2,1;
NAME                                                                       CON_ID
---------------------------------------------------------------------- ----------
/u02/oradata/BEECDB/temp01.dbf                                                  1
/u02/oradata/BEECDB/pdb_seed/temp01.dbf                                         2
show parameter pdb_file_name_convert
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert                string      /u02/oradata/BEECDB/, /u02/ora
                                                 data/BEECDB/pdb_seed/

All files that were created with manually pointed filename were converted according to pdb_file_name_convert parameter.

Modify paths for seed file names

There is one more thing I would like to check – what if we would like to adjust database filenames after the database have been created and is up and running?

Let’s find out using last example with pdb_file_name_convert parameter. I’ll try to move all seed pdb files from pdb_seed to seed_pdb directory.

!mkdir -p /u02/oradata/BEECDB/seed_pdb/
alter session set container = pdb$seed;
alter database move datafile '/u02/oradata/BEECDB/pdb_seed/sysaux01.dbf'
  to '/u02/oradata/BEECDB/seed_pdb/sysaux01.dbf';
alter database move datafile '/u02/oradata/BEECDB/pdb_seed/system01.dbf'
  to '/u02/oradata/BEECDB/seed_pdb/system01.dbf';
alter database move datafile '/u02/oradata/BEECDB/pdb_seed/users01.dbf'
  to '/u02/oradata/BEECDB/seed_pdb/users01.dbf';
alter database move datafile '/u02/oradata/BEECDB/pdb_seed/temp01.dbf'
  to '/u02/oradata/BEECDB/seed_pdb/temp01.dbf';

Here I’ve received an error:

alter database move datafile '/u02/oradata/BEECDB/pdb_seed/temp01.dbf'
  to '/u02/oradata/BEECDB/seed_pdb/temp01.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"/u02/oradata/BEECDB/pdb_seed/temp01.dbf"

I’ll try the old approach then:

alter database tempfile '/u02/oradata/BEECDB/pdb_seed/temp01.dbf' offline;
!mv /u02/oradata/BEECDB/pdb_seed/temp01.dbf /u02/oradata/BEECDB/seed_pdb/temp01.dbf
alter database rename file '/u02/oradata/BEECDB/pdb_seed/temp01.dbf'
  to '/u02/oradata/BEECDB/seed_pdb/temp01.dbf';
alter database tempfile '/u02/oradata/BEECDB/seed_pdb/temp01.dbf' online;
alter session set container = cdb$root;
col name for a70
select name, con_id from v$datafile order by 2,1;
NAME                                                                       CON_ID
---------------------------------------------------------------------- ----------
/u02/oradata/BEECDB/sysaux01.dbf                                                1
/u02/oradata/BEECDB/system01.dbf                                                1
/u02/oradata/BEECDB/undo01.dbf                                                  1
/u02/oradata/BEECDB/users01.dbf                                                 1
/u02/oradata/BEECDB/seed_pdb/sysaux01.dbf                                       2
/u02/oradata/BEECDB/seed_pdb/system01.dbf                                       2
/u02/oradata/BEECDB/seed_pdb/users01.dbf                                        2
select name, con_id from v$tempfile order by 2,1;
NAME                                                                       CON_ID
---------------------------------------------------------------------- ----------
/u02/oradata/BEECDB/temp01.dbf                                                  1
/u02/oradata/BEECDB/seed_pdb/temp01.dbf                                         2

We’ve managed to rename all seed database files.

Additional parameters in seed clause

Let’s cleanup again and execute this create database statement.

create database beecdb
  user sys identified by manager
  user system identified by manager
  maxdatafiles 1024
  character set al32utf8
  national character set al16utf16
  set default smallfile tablespace
  extent management local
  datafile '/u02/oradata/BEECDB/system01.dbf' size 768m
    autoextend on next 32m maxsize unlimited
  sysaux datafile '/u02/oradata/BEECDB/sysaux01.dbf' size 576m
    autoextend on next 32m maxsize unlimited
  default tablespace users datafile '/u02/oradata/BEECDB/users01.dbf' size 8m
    autoextend off
  default temporary tablespace temp tempfile '/u02/oradata/BEECDB/temp01.dbf' size 256m
    autoextend on next 32m maxsize unlimited
  undo tablespace undotbs datafile '/u02/oradata/BEECDB/undo01.dbf' size 128m
    autoextend on next 32m maxsize unlimited  
  logfile group 1 size 256m blocksize 512,
          group 2 size 256m blocksize 512,
		  group 3 size 256m blocksize 512
  maxlogfiles 16
  maxlogmembers 3
  maxloghistory 1
  archivelog
  enable pluggable database
  seed
  system datafiles size 64m autoextend on next 16m maxsize unlimited
  sysaux datafiles size 64m autoextend on next 16m maxsize unlimited
  user_data tablespace pdbdata datafile size 32m autoextend on next 32m maxsize unlimited;

What happen this time? We’re still using pdb_file_name_convert initialization parameter so I expect that filenames for seed database will be converted according to this parameter.

col name for a70
select name, con_id from v$datafile order by 2,1;
NAME                                                                       CON_ID
---------------------------------------------------------------------- ----------
/u02/oradata/BEECDB/sysaux01.dbf                                                1
/u02/oradata/BEECDB/system01.dbf                                                1
/u02/oradata/BEECDB/undo01.dbf                                                  1
/u02/oradata/BEECDB/users01.dbf                                                 1
/u02/oradata/BEECDB/0496458CF2F20AC6E0530C38A8C09DE3/datafile/o1_mf_pd          2
bdata_b2zgm77s_.dbf

/u02/oradata/BEECDB/pdb_seed/sysaux01.dbf                                       2
/u02/oradata/BEECDB/pdb_seed/system01.dbf                                       2
/u02/oradata/BEECDB/pdb_seed/users01.dbf                                        2
select name, con_id from v$tempfile order by 2,1;
NAME                                                                       CON_ID
---------------------------------------------------------------------- ----------
/u02/oradata/BEECDB/temp01.dbf                                                  1
/u02/oradata/BEECDB/seed_pdb/temp01.dbf                                         2

We can see that manual user tablespace with a name pdbdata couldn’t be converted and we have this ugly directory name – but still unique. OMF grab this one file and initialization parameter coudn’t be used. As we can see there are 3 new parameters highlighted in create database statement. First two changes system and sysaux tablespace database file parameters for initial size and autoextend clause. user_data parameter is responsible for a new tablespace which will be equivalent to users tablespace from a standard database. But it will be dedicated only for seed database in this particular command. Why create users tablespace for seed pluggable database? When we create new pdb from seed we’ll get this tablespace as a default user data tablespace. This way we’re separated from root and seed containers. We should not have any user data inside those two containers.

Ok, let’s check, if the size of the datafiles really were adjusted during seed pdb creation process.

col name for a70
select round(bytes/1024/1024,2) mbytes, name, con_id
  from v$datafile
 order by 3,2;
    MBYTES NAME                                                             CON_ID
---------- ------------------------------------------------------------ ----------
       576 /u02/oradata/BEECDB/sysaux01.dbf                                      1
       768 /u02/oradata/BEECDB/system01.dbf                                      1
       128 /u02/oradata/BEECDB/undo01.dbf                                        1
         8 /u02/oradata/BEECDB/users01.dbf                                       1
        32 /u02/oradata/BEECDB/0496458CF2F20AC6E0530C38A8C09DE3/datafil          2
           e/o1_mf_pdbdata_b2zgm77s_.dbf

        64 /u02/oradata/BEECDB/pdb_seed/sysaux01.dbf                             2
        64 /u02/oradata/BEECDB/pdb_seed/system01.dbf                             2
         8 /u02/oradata/BEECDB/pdb_seed/users01.dbf                              2

New values for datafile size were applied.

Creating database – final attempt

Last attempt with a configuration that I will use if I ever need to do it again in the future.

Exit from sql*plus session from earlier test. Edit initialization parameter file and remove pdb_file_name_convert parameter. I’m not going to use it as it has lowest priority when it comes to parameter precedence.

vi /u01/app/oracle/product/12.1.0/dbhome_1/dbs/initbeecdb.ora
db_create_file_dest='/u02/oradata/'
db_create_online_log_dest_1='/u02/oradata/'
memory_max_target=2048m
memory_target=2048m
db_name=beecdb
db_unique_name=beecdb
instance_name=beecdb
db_recovery_file_dest='/u03/fra/'
db_recovery_file_dest_size=32g
diagnostic_dest=/u01/app/oracle
enable_pluggable_database=true
compatible=12.1.0.2.0
remote_login_passwordfile=EXCLUSIVE
undo_tablespace=undotbs
audit_file_dest=/u01/app/oracle/admin/beecdb/adump
audit_trail=db
processes=300
db_block_size=8192
log_archive_format=%t_%s_%r.dbf
open_cursors=500

Now I can get back to sql*plus:

sqlplus / as sysdba

And follow drop database procedure to cleanup after last attempt.

Execute create database statement that is presetned below.

create database beecdb
  user sys identified by manager
  user system identified by manager
  maxdatafiles 1024
  character set al32utf8
  national character set al16utf16
  set default smallfile tablespace
  extent management local
  datafile '/u02/oradata/BEECDB/system01.dbf' size 768m
    autoextend on next 32m maxsize unlimited
  sysaux datafile '/u02/oradata/BEECDB/sysaux01.dbf' size 576m
    autoextend on next 32m maxsize unlimited
  default tablespace users datafile '/u02/oradata/BEECDB/users01.dbf' size 8m
    autoextend off
  default temporary tablespace temp tempfile '/u02/oradata/BEECDB/temp01.dbf' size 256m
    autoextend on next 32m maxsize unlimited
  undo tablespace undotbs datafile '/u02/oradata/BEECDB/undo01.dbf' size 128m
    autoextend on next 32m maxsize unlimited  
  logfile group 1 size 256m blocksize 512,
          group 2 size 256m blocksize 512,
		  group 3 size 256m blocksize 512
  maxlogfiles 16
  maxlogmembers 3
  maxloghistory 1
  archivelog
  enable pluggable database
  seed
    file_name_convert = ('/u02/oradata/BEECDB/','/u02/oradata/BEECDB/pdb_seed/')
    system datafiles size 64m autoextend on next 16m maxsize unlimited
    sysaux datafiles size 64m autoextend on next 16m maxsize unlimited
  user_data tablespace pdbusers datafile '/u02/oradata/BEECDB/pdb_seed/pdbusers01.dbf'
    size 8m autoextend on next 8m maxsize unlimited;

Because pdbusers tablespace is a part of create database statement (and not part of seed clause) you need to put correct path after datafile keyword or you can leave this job to OMF (as presented in previous example). Let us check filenames one more time:

col name for a70
select name, con_id from v$datafile order by 2,1;
NAME                                                                       CON_ID
---------------------------------------------------------------------- ----------
/u02/oradata/BEECDB/sysaux01.dbf                                                1
/u02/oradata/BEECDB/system01.dbf                                                1
/u02/oradata/BEECDB/undo01.dbf                                                  1
/u02/oradata/BEECDB/users01.dbf                                                 1
/u02/oradata/BEECDB/pdb_seed/pdbusers01.dbf                                     2
/u02/oradata/BEECDB/pdb_seed/sysaux01.dbf                                       2
/u02/oradata/BEECDB/pdb_seed/system01.dbf                                       2
/u02/oradata/BEECDB/pdb_seed/users01.dbf                                        2
select name, con_id from v$tempfile order by 2,1;
NAME                                                                       CON_ID
---------------------------------------------------------------------- ----------
/u02/oradata/BEECDB/temp01.dbf                                                  1
/u02/oradata/BEECDB/pdb_seed/temp01.dbf                                         2

Conclusion

There are many ways of creating pluggable database with seed clause and without it. Each approach gives you the ability to convert paths for the seed pdb. I don’t know, why OMF names were not converted with seed file_name_convert clause – according to documentation they should. As this is still early version of this database we can assume, that this will change in future releases (either, in documentation or in implementation).

Even, if you have chosen wrong set of parameters you can fix your situation and change filenames and/or paths by moving things around until you’ll be pleased with effects.

Appendix: Finishing database creation

To finish installation we need to populate this database with sys objects. Continue with next steps to create dictionary and push this shell into fully operational database.

Create dictionary

After create database command database will be in mounted state. You don’t need to mount it manually.

Last step – create database objects needed for many important features – or I should just say – create sys user schema. Exit from sql*plus session if you’re still online, create working directory and change your current one to this newly created, adjust your environment as presented above for the readkey problem and start the script.

mkdir -p $ORACLE_BASE/admin/beecdb/manual_create
cd $ORACLE_BASE/admin/beecdb/manual_create
export PATH=$ORACLE_HOME/bin:/u01/app/oracle/product/12.1.0/dbhome_1/perl/bin/:$PATH
sqlplus / as sysdba
@?/rdbms/admin/catcdb.sql

After script finish his job you can check log files from database sys schema objects creation process.

!ls -latr
total 48376
drwxr-xr-x 5 oracle oinstall     4096 Oct  3 16:38 ..
-rw-r--r-- 1 oracle oinstall      346 Oct  4 11:52 catalog_catcon_3525.lst
-rw-r--r-- 1 oracle oinstall   487312 Oct  4 11:54 catalog0.log
-rw-r--r-- 1 oracle oinstall      346 Oct  4 11:54 catproc_catcon_3599.lst
-rw-r--r-- 1 oracle oinstall 41287557 Oct  4 12:20 catproc0.log
-rw-r--r-- 1 oracle oinstall      346 Oct  4 12:20 catoctk_catcon_4358.lst
-rw-r--r-- 1 oracle oinstall     4442 Oct  4 12:20 catoctk0.log
-rw-r--r-- 1 oracle oinstall      346 Oct  4 12:21 owminst_catcon_4415.lst
-rw-r--r-- 1 oracle oinstall    18726 Oct  4 12:22 owminst0.log
-rw-r--r-- 1 oracle oinstall      344 Oct  4 12:22 pupbld_catcon_4475.lst
-rw-r--r-- 1 oracle oinstall     6076 Oct  4 12:22 pupbld0.log
-rw-r--r-- 1 oracle oinstall      344 Oct  4 12:22 hlpbld_catcon_4530.lst
-rw-r--r-- 1 oracle oinstall    34534 Oct  4 12:22 hlpbld0.log
-rw-r--r-- 1 oracle oinstall      346 Oct  4 12:22 initjvm_catcon_4586.lst
-rw-r--r-- 1 oracle oinstall   455567 Oct  4 12:25 initjvm0.log
-rw-r--r-- 1 oracle oinstall      346 Oct  4 12:25 initxml_catcon_4666.lst
-rw-r--r-- 1 oracle oinstall     5090 Oct  4 12:27 initxml0.log
-rw-r--r-- 1 oracle oinstall      342 Oct  4 12:27 xmlja_catcon_4738.lst
-rw-r--r-- 1 oracle oinstall     3744 Oct  4 12:28 xmlja0.log
-rw-r--r-- 1 oracle oinstall      346 Oct  4 12:28 catjava_catcon_4791.lst
-rw-r--r-- 1 oracle oinstall     6866 Oct  4 12:28 catjava0.log
-rw-r--r-- 1 oracle oinstall      346 Oct  4 12:29 catxdbj_catcon_4853.lst
-rw-r--r-- 1 oracle oinstall     3690 Oct  4 12:29 catxdbj0.log
-rw-r--r-- 1 oracle oinstall      344 Oct  4 12:29 catctx_catcon_4906.lst
-rw-r--r-- 1 oracle oinstall   100882 Oct  4 12:30 catctx0.log
-rw-r--r-- 1 oracle oinstall      348 Oct  4 12:30 dr0defin_catcon_4983.lst
-rw-r--r-- 1 oracle oinstall     4832 Oct  4 12:30 dr0defin0.log
-rw-r--r-- 1 oracle oinstall      348 Oct  4 12:30 dbmsxdbt_catcon_5036.lst
-rw-r--r-- 1 oracle oinstall     4180 Oct  4 12:30 dbmsxdbt0.log
-rw-r--r-- 1 oracle oinstall      346 Oct  4 12:30 ordinst_catcon_5089.lst
-rw-r--r-- 1 oracle oinstall     6360 Oct  4 12:30 ordinst0.log
-rw-r--r-- 1 oracle oinstall      342 Oct  4 12:30 catim_catcon_5142.lst
-rw-r--r-- 1 oracle oinstall   287437 Oct  4 12:45 catim0.log
-rw-r--r-- 1 oracle oinstall      348 Oct  4 12:45 olap.sql_catcon_5322.lst
-rw-r--r-- 1 oracle oinstall    59190 Oct  4 12:47 olap.sql0.log
-rw-r--r-- 1 oracle oinstall      344 Oct  4 12:47 mdinst_catcon_5393.lst
-rw-r--r-- 1 oracle oinstall   286196 Oct  4 12:56 mdinst0.log
-rw-r--r-- 1 oracle oinstall      344 Oct  4 12:56 catols_catcon_5513.lst
-rw-r--r-- 1 oracle oinstall    29288 Oct  4 12:57 catols0.log
-rw-r--r-- 1 oracle oinstall      344 Oct  4 12:57 catapx_catcon_5570.lst
-rw-r--r-- 1 oracle oinstall  1210766 Oct  4 13:20 install2014-10-04_12-57-34.log
-rw-r--r-- 1 oracle oinstall  1210837 Oct  4 13:42 install2014-10-04_13-20-31.log
-rw-r--r-- 1 oracle oinstall  2424536 Oct  4 13:42 catapx0.log
-rw-r--r-- 1 oracle oinstall      344 Oct  4 13:42 catmac_catcon_6022.lst
-rw-r--r-- 1 oracle oinstall  1406802 Oct  4 13:44 catmac0.log
-rw-r--r-- 1 oracle oinstall      348 Oct  4 13:44 catclust_catcon_6090.lst
-rw-r--r-- 1 oracle oinstall     4736 Oct  4 13:44 catclust0.log
-rw-r--r-- 1 oracle oinstall      360 Oct  4 13:44 catbundleapply_catcon_6147.lst
-rw-r--r-- 1 oracle oinstall     4150 Oct  4 13:44 catbundleapply0.log
-rw-r--r-- 1 oracle oinstall      342 Oct  4 13:44 utlrp_catcon_6208.lst
-rw-r--r-- 1 oracle oinstall    10668 Oct  4 13:46 utlrp0.log
drwxr-xr-x 2 oracle oinstall     4096 Oct  4 13:46 .

New container database is ready to do some work for us.

Network configuration

Add entries in tnsnames.ora file and update parameters for use of local listener.

!vi /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
LISTENER_BEECDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = odone)(PORT = 1521))

BEECDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = odone)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = beecdb)
    )
  )

Update database parameters values and restart the database.

alter system set local_listener=beecdb scope = both;
alter system set dispatchers="(PROTOCOL=TCP)(SERVICE=BEECDBXDB)" scope = both;
shutdown immediate
startup

This way we can check if the database is available through listener.

Adding Database Express

With 12c you don’t need to have dedicated server for your Enterprise Manager – Oracle decided to move this application into the database and enable it as other http services inside XDB. Just enable server and that’s all. From your sysdba session in beecdb enter this line:

exec dbms_xdb_config.sethttpsport(5501);

Using simple select check if server is enabled – you should get as a result one line with port number. If it’s 0 then your server is not working at all. In our case expected value is 5501.

select dbms_xdb_config.gethttpsport from dual;
GETHTTPSPORT
------------
        5501

If you’ve disabled your iptables like mentioned in installation guide from my previous article then you should not have any problem to use link with a format like this:

https://your_ip:your_port/em

For my database properly formatted link looks like:

https://192.168.56.12:5501/em/login

Check status of grid listener where our database is registered:

!lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 04-OCT-2014 15:11:36

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                04-OCT-2014 14:56:19
Uptime                    0 days 0 hr. 15 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/product/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/odone/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=odone)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=odone)(PORT=5501))(Security=(my_wallet_directory=/u01/app/oracle/admin/beecdb/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "BEECDBXDB" has 1 instance(s).
  Instance "beecdb", status READY, has 1 handler(s) for this service...
Service "beecdb" has 1 instance(s).
  Instance "beecdb", status READY, has 1 handler(s) for this service...
The command completed successfully

This way you should get into Express Database 12c.

Image

Login page.

Image

And The Database Home. Nice and shiny.

Optional

You can add your database with srvctl utility to Oracle Restart – this way you’ll provide automatic restart after shutdown of your OS or just ease managing your start/stop operations directly from command line.

Another options is to take full database backup immediately. This database is in archivelog which means it’s ready for level 0 incremental backup as a base for your day-to-day incremental strategy.

As oracle user from shell execute similar command:

srvctl add database -db beecdb -dbname beecdb -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 -spfile /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilebeecdb.ora -pwfile /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwbeecdb -role PRIMARY -startoption OPEN -stopoption IMMEDIATE -instance beecdb -policy AUTOMATIC -verbose

Check configuration with status keyword:

srvctl config database -db beecdb
Database unique name: beecdb
Database name: beecdb
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile: /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilebeecdb.ora
Password file: /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwbeecdb
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups:
Services:
OSDBA group:
OSOPER group:
Database instance: beecdb

It is a good idea to give a shot Oracle Restart and restart the server to see, how it is doing.

Troubleshooting

If you’ll get similar error during execute of command create database:

create database beecdb
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/dcore.bsq' near line 1079
ORA-01519: error while processing file '?/rdbms/admin/dcore.bsq' near line 88
ORA-00604: error occurred at recursive SQL level 1
ORA-01119: error in creating database file '/u02/oradata/BEECDB/pdb_seed/system01.dbf'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
Process ID: 2294
Session ID: 237 Serial number: 32254

Then you should check if the patch for files are created, especially for datafile clauses in normal part of command (system, sysaux, undo and users tablespaces) and then for target of conversion clause for the seed database. All paths should be available because we do not use OMF in such case and paths should be valid before executing this command.

Patrycjusz Konkol has written 5 articles

Oracle Database Administrator with around 7+ years of experience in Oracle Databases. From beginning of professional career worked as SQL and PL/SQL developer with use of Oracle Forms&Reports 6i, C# programmer and finally Oracle DBA with wide area of responsibilities in IBM Power environment with AIX 6 and 7. Right now works for Atos - international information technology services company as an Oracle Database Administrator.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>