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.
Login page.
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.
