Sunday, May 21, 2017

Oracle Database 12C - Creating and Configuring Database (DBCA)

Hi, everyone.

Today in this article I will show you how to create and configure a database.

There is some techniques to create a new database, such as: DBCA (Database Configuration Assistant, SQLPLUS and RMAN (Recovery Manager) - Duplicate Database.

1. Setup all environment variables inside of the file "~/.bash_profile":
  • export ORACLE_HOME=/opt/oracle/product/12.1.0/db
  • export ORACLE_BASE=/opt/oracle
  • export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch

On Unix / Linux environment "~" represents the $HOME of the user, in our case: /home/oracle.
For a complete view: /home/oracle/.bash_profile

It is need to read again this file to set up all variables, execute: source ~/.bash_profile

DBCA - Database Configuration Assistant
2. After setup the environment variables, call the database configuration assistant: $ORACLE_HOME/bin/dbca

2.1 Select "Create Database" --> Next.

2.2 Select "Advanced Mode" --> Next.  (We know what we are doing)

2.3 Select "General Purpose or Transaction Processing" --> Next.

2.4 Setup "Global Database Name" (ORACLE_UNQNAME / DB_UNIQUE_NAME), "SID" (ORACLE_SID / INSTANCE_NAME), check box "Create As Container Database", select "Create a Container Database with one or more PDBs", "PDB Name" (Choose a name of your pluggable database ) --> Next.

2.5 We won't configure "Enterprise Manager (EM) Database Express", it will be discussed in a future post. 

 2.6 Set up a password for each user -->  Next.

2.7 If you wish to create a new listener it can be done on this screen or use an already existent listener --> Next

2.8 Select "Automatic Storage Management (ASM)" for storage type of database files, choose "Use Common Location for All Database Files" and set up the diskgroup that it will use to storage the datafiles: +DG_BLOG_DATA, for recovery files choose ASM and set up the archivelog diskgroup for fast recovery are: +DG_BLOG_ARCH, enable the archive log checking box "Enable Archiving", set up the diskgroup that will store the archivelog: LOCATION=+DG_BLOG_ARCH and finally change the extension of the archivelog: default .dbf (same to datafile) --> new one .arc (it makes more sense and avoid some mistake --> OK --> Next.

2.9 We won't use sample schemas and won't configure Database Vault & Label Security --> Next:

3. In the "Memory" tab, select box "Use Automatic Memory Management", leave with value "40%" of memory RAM: 

3.1 Tab "Sizing" we can change the value of the processes (keep default):

3.2 Tab "Character Sets", choose "Use Unicode (AL32UTF8)": 

3.3 Tab "Connection Mode", select "Dedicated Server Mode":

3.4 Select "All Initialization Parameters" menu, if you wish to change a default value of some database parameter before create the database, now it's the time. We won't change anything --> Close --> Next:

3.5 Open "Customize Storage Locations...", alter all redo log files from 50MB to 500MB and change the diskgroup from +DG_BLOG_DATA to +DG_BLOG_REDO --> OK:

3.6 Clicking in Next, it will go to the "Create Database - Summary" screen, check if everything is according as you planned --> Finish:

3.7 Creating Database...

PS: You can follow what is going on in background, checking the "Activity Log" or "Alert Log"

3.8 Finally... finished:

Now we have created a single database instance using DBCA.