Oracle 12c: Create a Container Database manually

Oracle 12c: Create a Container Database (CDB) manually with CREATE DATABASE script

Note:
Oracle strongly recommends using the Database Configuration Assistant (DBCA) instead of the CREATE DATABASE SQL statement to create a CDB, because using DBCA is a more automated approach, and your CDB is ready to use when DBCA completes.

1. Setup Database environment
Set the ORACLE_SID and ORACLE_HOME environment variable and include the ORACLE_HOME/bin directory to the PATH variable.

export ORACLE_SID=newcdb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH

 

CREATE CDB MANUALLY 01

 

NOTE: You must choose a Database Administrator authentication method
– You must be authenticated and granted appropriate system privileges in order to create a database.
– You can be authenticated as an administrator with the required privileges in the following ways:
a. With a password file (sqlplus sys as sysdba)
Example: orapwd FILE=’/u01/app/oracle/product/12.1.0/db_1/dbs/orapwnewcdb’ ENTRIES=10 FORMAT=12

CREATE CDB MANUALLY 02 ORAPWD

b. With operating system authentication (sqlplus / as sysdba)
Ensure that you log in to the host computer with a user account that is a member of the appropriate operating system user group (typically the dba user group).

 

2. Create the Initialization Parameter File
cd $ORACLE_HOME/dbs
vi initnewcdb.ora
cat initnewcdb.ora

sga_target=500M
pga_aggregate_target=260M
audit_file_dest="/u01/app/oracle/admin/newcdb/adump"
audit_trail=db
compatible=12.1.0.2.0
control_files=("/u01/app/oracle/oradata/newcdb/control01.ctl","/u01/app/oracle/fast_recovery_area/newcdb/control02.ctl")
db_block_size=8192
db_domain=localdomain
db_name="newcdb"
db_recovery_file_dest="/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size=4560m
diagnostic_dest=/u01/app/oracle
dispatchers="(PROTOCOL=TCP) (SERVICE=newcdbXDB)"
enable_pluggable_database=true
open_cursors=300
processes=300
remote_login_passwordfile=EXCLUSIVE
undo_tablespace=UNDOTBS1

 

CREATE CDB MANUALLY 03 INIT

CREATE CDB MANUALLY 03 INIT 2

 

3. Create needed directories
CONTROL FILES:
mkdir -p /u01/app/oracle/oradata/newcdb
mkdir -p /u01/app/oracle/fast_recovery_area/newcdb
DATA FILES:
# mkdir -p /u01/app/oracle/oradata/newcdb
mkdir -p /u01/app/oracle/oradata/newcdb/pdbseed
REDO LOG FILES:
# mkdir -p /u01/app/oracle/oradata/newcdb
mkdir -p /u02/app/oracle/oradata/newcdb
ADUMP:
mkdir -p /u01/app/oracle/admin/newcdb/adump

 

CREATE CDB MANUALLY 04 MKDIR

 

4. Create Database script
To create a CDB, the ENABLE_PLUGGABLE_DATABASE initialization parameter must be set to TRUE.
Note: /home/oracle/bin exists in my environment
vi /home/oracle/bin/create_db_newcdb.sql

CREATE DATABASE newcdb
 USER SYS IDENTIFIED BY sys_password
 USER SYSTEM IDENTIFIED BY system_password
 LOGFILE GROUP 1 ('/u01/app/oracle/oradata/newcdb/redo01a.log','/u02/app/oracle/oradata/newcdb/redo01b.log')
 SIZE 100M BLOCKSIZE 512,
 GROUP 2 ('/u01/app/oracle/oradata/newcdb/redo02a.log','/u02/app/oracle/oradata/newcdb/redo02b.log')
 SIZE 100M BLOCKSIZE 512,
 GROUP 3 ('/u01/app/oracle/oradata/newcdb/redo03a.log','/u02/app/oracle/oradata/newcdb/redo03b.log')
 SIZE 100M BLOCKSIZE 512
 MAXLOGHISTORY 1
 MAXLOGFILES 16
 MAXLOGMEMBERS 3
 MAXDATAFILES 1024
 CHARACTER SET AL32UTF8
 NATIONAL CHARACTER SET AL16UTF16
 EXTENT MANAGEMENT LOCAL
 DATAFILE '/u01/app/oracle/oradata/newcdb/system01.dbf'
 SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
 SYSAUX DATAFILE '/u01/app/oracle/oradata/newcdb/sysaux01.dbf'
 SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
 DEFAULT TABLESPACE USERS
 DATAFILE '/u01/app/oracle/oradata/newcdb/users01.dbf'
 SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
 DEFAULT TEMPORARY TABLESPACE TEMP
 TEMPFILE '/u01/app/oracle/oradata/newcdb/temp01.dbf'
 SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
 UNDO TABLESPACE UNDOTBS1
 DATAFILE '/u01/app/oracle/oradata/newcdb/undotbs01.dbf'
 SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
 ENABLE PLUGGABLE DATABASE
 SEED
 FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/newcdb/',
 '/u01/app/oracle/oradata/newcdb/pdbseed/')
 SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
 SYSAUX DATAFILES SIZE 100M
;

 

CREATE CDB MANUALLY 05 CREATE DB SCRIPT

 

5. Login to the Database

$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 9 13:15:24 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL>

6. Create a Server Parameter File

SQL> create spfile from pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initnewcdb.ora';
File created.

7. Start the Instance

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 2926320 bytes
Variable Size 268437776 bytes
Database Buffers 247463936 bytes
Redo Buffers 5459968 bytes
SQL>

 

CREATE CDB MANUALLY 06 SPFILE

 

8. Use the CREATE DATABASE statement to create the new CDB.

SQL> set echo on
SQL> set timing on
SQL> @/home/oracle/bin/create_db_newcdb.sql
SQL> CREATE DATABASE newcdb
 2 USER SYS IDENTIFIED BY oracle3
 3 USER SYSTEM IDENTIFIED BY oracle3
 4 LOGFILE GROUP 1 ('/u01/app/oracle/oradata/newcdb/redo01a.log','/u02/app/oracle/oradata/newcdb/redo01b.log')
 5 SIZE 100M BLOCKSIZE 512,
 6 GROUP 2 ('/u01/app/oracle/oradata/newcdb/redo02a.log','/u02/app/oracle/oradata/newcdb/redo02b.log')
 7 SIZE 100M BLOCKSIZE 512,
 8 GROUP 3 ('/u01/app/oracle/oradata/newcdb/redo03a.log','/u02/app/oracle/oradata/newcdb/redo03b.log')
 9 SIZE 100M BLOCKSIZE 512
 10 MAXLOGHISTORY 1
 11 MAXLOGFILES 16
 12 MAXLOGMEMBERS 3
 13 MAXDATAFILES 1024
 14 CHARACTER SET AL32UTF8
 15 NATIONAL CHARACTER SET AL16UTF16
 16 EXTENT MANAGEMENT LOCAL
 17 DATAFILE '/u01/app/oracle/oradata/newcdb/system01.dbf'
 18 SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
 19 SYSAUX DATAFILE '/u01/app/oracle/oradata/newcdb/sysaux01.dbf'
 20 SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
 21 DEFAULT TABLESPACE USERS
 22 DATAFILE '/u01/app/oracle/oradata/newcdb/users01.dbf'
 23 SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
 24 DEFAULT TEMPORARY TABLESPACE TEMP
 25 TEMPFILE '/u01/app/oracle/oradata/newcdb/temp01.dbf'
 26 SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
 27 UNDO TABLESPACE UNDOTBS1
 28 DATAFILE '/u01/app/oracle/oradata/newcdb/undotbs01.dbf'
 29 SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
 30 ENABLE PLUGGABLE DATABASE
 31 SEED
 32 FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/newcdb/',
 33 '/u01/app/oracle/oradata/newcdb/pdbseed/')
 34 SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
 35 SYSAUX DATAFILES SIZE 100M
 36 ;
Database created.
Elapsed: 00:01:15.92

 

CREATE CDB MANUALLY 07 CREATE DB RUN

 

— Validate if the database created is a CDB

SQL> SELECT dbid, name, created, log_mode, open_mode, cdb, con_id FROM v$database;
 DBID NAME CREATED LOG_MODE OPEN_MODE CDB CON_ID
---------- --------- --------- ------------ -------------------- --- ----------
 46140884 NEWCDB 10-OCT-17 NOARCHIVELOG READ WRITE YES 0

— Validate if the seed database is created

SQL> SELECT con_id, dbid, name, open_mode, restricted, TO_CHAR(open_time, 'DD-MON-YY HH:MI:SS AM') OPEN_TIME FROM v$containers;
 CON_ID DBID NAME OPEN_MODE RES OPEN_TIME
---------- ---------- -------- ---------- --- ------------------------------
 1 46140884 CDB$ROOT READ WRITE NO 10-OCT-17 10:36:54 AM
 2 3138122242 PDB$SEED READ ONLY NO 10-OCT-17 10:36:54 AM

 

CREATE CDB MANUALLY 08 VALIDATE

 

— Query CDB/PDB name, tablespace name, datafile location, size, and status

SELECT c.name DB_NAME, a.name TABLESPACE_NAME, b.name DATA_FILE_NAME, b.bytes/1024/1024 SIZE_MB, b.status
 -- c.name DB_NAME, a.con_id, a.ts#, a.name TABLESPACE_NAME, b.name DATA_FILE_NAME, b.file#, b.blocks, b.bytes/1024/1024 SIZE_MB, b.status, b.enabled, b.creation_time
 FROM v$tablespace a, v$datafile b, v$containers c
 WHERE a.con_id = b.con_id
 AND a.con_id = c.con_id
 AND a.ts# = b.ts#
 ORDER BY a.con_id, a.TS#;

 

CREATE CDB MANUALLY 09 TBS

 

— Check Control files

SQL> show parameter control_files
NAME TYPE VALUE
-------------- -------- ------------------------------
control_files string /u01/app/oracle/oradata/newcdb/control01.ctl, /u01/app/oracle/fast_recovery_area/newcdb/control02.ctl

— Check Redo log files

SQL> SELECT a.group#, a.members, a.bytes/1024/1024 SIZE_MB, a.status, b.member
 FROM v$log a, v$logfile b
 WHERE a.group# = b.group#;
 GROUP# MEMBERS SIZE_MB STATUS MEMBER
---------- ---------- ---------- ---------- ----------------------------------------
 1 2 100 CURRENT /u01/app/oracle/oradata/newcdb/redo01a.log
 1 2 100 CURRENT /u02/app/oracle/oradata/newcdb/redo01b.log
 2 2 100 UNUSED /u01/app/oracle/oradata/newcdb/redo02a.log
 2 2 100 UNUSED /u02/app/oracle/oradata/newcdb/redo02b.log
 3 2 100 UNUSED /u01/app/oracle/oradata/newcdb/redo03a.log
 3 2 100 UNUSED /u02/app/oracle/oradata/newcdb/redo03b.log

 

CREATE CDB MANUALLY 10 Ctl redo

 

— Check if the NLS parameters are set as per the response file

SQL> SELECT * FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET%';
PARAMETER VALUE CON_ID
------------------------- --------------- ------
NLS_CHARACTERSET AL32UTF8 0
NLS_NCHAR_CHARACTERSET AL16UTF16 0

 

CREATE CDB MANUALLY 11 NLS

 

9. Data Dictionary views

We need to run the Oracle supplied dictionary scripts like catalog.sql, catproc.sql, pupbld.sql to populate the necessary dictionary views when we create a Oracle database manually using CREATE DATABASE statement.

But it’s different for a container database, because we have multiple container databases such as CDB$ROOT and PDB$SEED, we need to run these scripts against each of these containers. To simplify the creation of dictionary views in a CDB, Oracle provides a script called catcdb.sql located under $ORACLE_HOME/rdbms/admin directory.

However, when we run the catcdb.sql script, it creates all the database components like the way DBCA does, it will create database components like Spatial, Oracle Text, XDK, etc, even if we wouldn’t use them. So we would use the catcon.pl located under $ORACLE_HOME/rdbms/admin to run the Oracle supplied scripts (catalog.sql, catproc.sql, etc) against our container databases.

# running catalog.sql script against CDB containers
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -d $ORACLE_HOME/rdbms/admin -b catalog_output -e -l /home/oracle catalog.sql

# running catproc.sql script against CDB containers
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -d $ORACLE_HOME/rdbms/admin -b catproc_output -e -l /home/oracle catproc.sql

# running pupbld.sql script against CDB containers
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u system -d $ORACLE_HOME/sqlplus/admin -b pupbld_output -e -l /home/oracle pupbld.sql

NOTE: We used sys, sys, and system account here

 

CREATE CDB MANUALLY 12 catcon 1

 

— Query the status of database components

col comp_name for a40
col version for a15
col status for a10
SELECT name, comp_name, version, status FROM v$database, dba_registry;
NAME COMP_NAME VERSION STATUS
--------- ---------------------------------------- --------------- ----------
newcdb Oracle XML Database 12.1.0.2.0 VALID
newcdb Oracle Database Catalog Views 12.1.0.2.0 INVALID
newcdb Oracle Database Packages and Types 12.1.0.2.0 INVALID

 

CREATE CDB MANUALLY 13 dba_registry

 

— Recompile all the INVALID objects

@?/rdbms/admin/utlrp.sql

 

CREATE CDB MANUALLY 13 dba_registry 2

 

— Query the status of database components again

col comp_name for a40
col version for a15
col status for a10
SELECT name, comp_name, version, status FROM v$database, dba_registry;
NAME COMP_NAME VERSION STATUS
--------- ---------------------------------------- --------------- ----------
newcdb Oracle XML Database 12.1.0.2.0 VALID
newcdb Oracle Database Catalog Views 12.1.0.2.0 VALID
newcdb Oracle Database Packages and Types 12.1.0.2.0 VALID

 

CREATE CDB MANUALLY 13 dba_registry 3

 

10. Create a PDB

-- Creating PDB mypdb_01 using seed database (PDB$SEED)
CREATE PLUGGABLE DATABASE newpdb1 ADMIN USER pdbadmin IDENTIFIED BY oracle
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/newcdb/pdbseed/','/u01/app/oracle/oradata/newcdb/newpdb1/');
Pluggable database created.

— Open the PDB for READ-WRITE

ALTER PLUGGABLE DATABASE newpdb1 OPEN;
Pluggable database altered.

— Validate the PDB

SELECT name, open_mode, restricted FROM v$pdbs;
NAME OPEN_MODE RES
------------------------------ ---------- ---
PDB$SEED READ ONLY NO
PDB1 READ WRITE NO

 

CREATE CDB MANUALLY 14 NEWPDB

 

— Validate list of datafiles from each of the containers

-- Query CDB/PDB name, con_id, tablespace name, datafile location, size, and status
SELECT c.name DB_NAME, b.name DATA_FILE_NAME
 -- a.con_id, a.ts#, a.name TABLESPACE_NAME, b.name DATA_FILE_NAME, b.file#, b.blocks, b.bytes/1024/1024 SIZE_MB, b.status, b.enabled, b.creation_time
 FROM v$tablespace a, v$datafile b, v$containers c
 WHERE a.con_id = b.con_id
 AND a.con_id = c.con_id
 AND a.ts# = b.ts#
 ORDER BY a.con_id, a.TS#;

DB_NAME DATA_FILE_NAME
---------- --------------------------------------------------
CDB$ROOT /u01/app/oracle/oradata/newcdb/system01.dbf
CDB$ROOT /u01/app/oracle/oradata/newcdb/sysaux01.dbf
CDB$ROOT /u01/app/oracle/oradata/newcdb/undotbs01.dbf
CDB$ROOT /u01/app/oracle/oradata/newcdb/users01.dbf
PDB$SEED /u01/app/oracle/oradata/newcdb/pdbseed/system01.dbf
PDB$SEED /u01/app/oracle/oradata/newcdb/pdbseed/sysaux01.dbf
PDB$SEED /u01/app/oracle/oradata/newcdb/pdbseed/users01.dbf
PDB1 /u01/app/oracle/oradata/newcdb/pdb1/system01.dbf
PDB1 /u01/app/oracle/oradata/newcdb/pdb1/sysaux01.dbf
PDB1 /u01/app/oracle/oradata/newcdb/pdb1/users01.dbf

NOTE: Because we created the CDB manually, if we did not create an entry for it on /etc/oratab, we wouldn’t see its entry on DBCA (let’s say you wanted to delete it).

This is my personal notes and I hope you learned something from it!

Like Loading…

Measure

Measure

Read more

世界越快心越慢

在晚飯後的休息時間,我特別享受在客廳瀏灠youtube上各樣各式創作者的影音作品。很大不同於傳統媒體,節目多是針對大多數族群喜好挑選的,在youtube上我會依心情看無腦的動畫、一些旅拍記錄、新聞時事談論。 尤其在看了大量的Youtube的分享後,我真的感受到會限制我的是我的無知,特別是那些我想都沒想過的實際應用,在學習後大大幫助到我的生活和工作層面。 休息在家時,我喜歡想一些沒做過的菜,動手去設計生活和工作上的解決方案,自己是真的很難閒著沒事做。 如創作文章,陪養新的習慣都能感覺到成長的喜悅,是不同於吃喝玩樂的快樂的。 創作不去限制固定的形式,文字是創作、影像聲音也是創作,記錄生活也是創作,我想留下的就是創造—》實現—》回憶,這樣子的循環過程,在留下的足跡面看到自己一路上的成長、失敗、絕望、重新再來。 雖然大部份的時候去做這些創作也不明白有什麼特別的意義,但不去做也不會留下什麼,所以呀不如反事都去試試看,也許能有不一樣的水花也許有意想不到的結果,投資自己永遠不會是失敗的決定,不是嗎?先問問自己再開始計畫下一步,未來沒人說得準。 像最近看youtube仍大一群人在為DOS開

By Phillips Hsieh

知識管理的三個步驟:一小時學會把知識運用到生活上

摘錄瓦基「閱讀前哨站」文章作為自己學習知識管理的內容 Part1「篩選資訊」 如何從海量資訊中篩選出啟發性、實用性和相關性的精華,讓你在學習過程中不再迷失方向。 1. 實用性 2. 啟發性 Part2「提高理解」 如何通過譬喻法和應用法,將抽象的知識與日常生活和工作緊密結合,建立更深刻的理解。 1. 應用法 2. 譬喻法 Part3「運用知識」 如何連結既有知識,跟自己感興趣的領域和專案產生關聯,讓你在運用知識的路途上游刃有餘。 1. 跟日常工作專案、人際活動產生連結 # 為什麼要寫日記? * 寫日記是為了忘記,忘卻瑣碎事情,保持專注力 * 寫日記就像在翻譯這個世界,訓練自己的解讀能力 * 不只是透過日記來記錄生活,而是透過日記來發展生活 #如何寫日記? * 不要寫流水帳式的日記,而是寫覆盤式的日記 當我們試著記錄活動和感受之間的關聯,有助於辦認出真正快樂的事 日記的記錄方式要以過程為主,而非結果 * 感恩日記的科學建議,每日感恩的案例

By Phillips Hsieh
2024年 3月30日 14屆美利達環彰化百K

2024年 3月30日 14屆美利達環彰化百K

這是場半小時就被秒報名額滿的經典賽事, 能順利出賽實屬隊友的功勞, 這次的準備工作想試試新買的外胎, 因為是無內胎用的外胎, 特別緊超級難安裝的, 問了其他朋友才知道, 要沾上肥皂水才容易滑入車框。 一早四點起床準備, 五點集合備好咖啡在車上飲用, 約了六點在彰化田尾鄉南鎮國小, 整好裝四人一起出發前往會場。 被排在最後一批出發, 這次的路線會繞行的員林148上139縣道, 其實在早上五點多天就開始有點飄雨, 大伙就開始擔心不會要雨戰吧! 果不其然才出發準備上148爬坡雨勢越來越大, 戴著防風眼鏡的我在身體的熱氣加上雨水冷凝效果下, 鏡面上滿是霧氣肉眼可視距離才剩不到五公尺, 只能緊依前前方的車友幫忙開路, 之後洪大跟上來我立馬請求他幫忙開路, 上了139停下車把防風眼鏡收起來, 反正下雨天又陰天完全用不到太陽眼鏡了。 雨是邊下邊打雷, 大伙都在這條139上一台一台單車好像避電針, 一時有點害怕不然想平時沒做什麼壞事, 真打到自己就是天意了。 下了139雨勢開始變小, 大伙的速度開始有所提昇, 開高鐵列車的時機己成熟, 物色好列車就跟好跟滿。 最後找了一隊似乎整團有固定在練

By Phillips Hsieh
2023 12月9號 美利達單車嘉年華

2023 12月9號 美利達單車嘉年華

第二次參加美利達環南投賽事, 還記得去年第一次參加這美利達環南投, 還特地提前一天跟車友在魚池住了一晚。 這回用上了剛在7月份剛安裝的車頂架, 安裝了二種不同的攜車架, 都樂這邊可以不用拆車輪直上車頂, YAKIMA這邊選了經濟的款式, 折掉前輪利用前叉固定在攜車架上。 約了唯一一位一起參加的朋友, 二人一早四點約見面, 幫朋友帶上了拿鐵咖啡, 開上日月潭在水社碼頭停好車, 騎往向山遊客中心, 路過美麗的日月潭簡直不要太美了拍一張。 抵達會場己是人山人海了, 跟著大伙排隊順便也看網紅也欣賞名車。 出發就先沿著日月潭順時針騎, 騎到玄裝寺很急停下來上一下廁所, 比賽時都會尿都特別的滿, 一方面是比較緊張,一方面是特別興奮。 這時己經跟車友失散了, 只能獨推沿路看有沒有車友可以一起組隊的, 很可惜在山區大家的實力不一只求平安順騎了, 原則就是有補給就停有食物就吃。 下到水里人群再次聚集起來, 光等紅綠燈就是一條車龍。 騎行了一大圈水里再回到131縣道, 這時背後傳來熟悉的聲音叫菲哥, 終於跟車友重新集合接下來就一路邊聊邊騎。 最後來幾張專業攝影師拍攝的照片 回到終點台上

By Phillips Hsieh