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.

Wednesday, March 22, 2017

Oracle Database 12C - Installation Database Standalone Server

Hi, everyone.

Today in this article I will show you how to install Oracle Database 12c.

We will use Oracle Automatic Storage Management (Oracle ASM) to store our database, take a look in my previous post about install and configure: Oracle Grid Infrastructure for a Standalone Server 12c.

1. Download Oracle Database 12c (Linux x86_64) installation packages: and
  • Unpack package called: and after that, unpack package called:
It will be created a directory called database with the following content:

1.1 It's time to call the installation utility, execute: ./runInstaller

  •    For purpose of this how to, uncheck the option to receive security updates, let's email field blank and click --> Next
  • It will be presented a warning message, because we are ignoring the email field, just ignore it too and click --> Yes.

1.2 Next screen it will be presented the options to that we want to perform:
  •  Create and configure a database 
  • Install database software only   ---> Choose this one.
  • Upgrade an existing database

We will only install the database software binaries first, because I want to show you more in deep details the command "DBCA - Database Configuration Assistant".

 1.3 Select "Single instance database installation" --> Next --> Next again to choose the language:

We are installing a single database, Oracle Real Application Clusters and Oracle Rac One Node, it will be discussed in a future posts. 

1.4 The only option that as available is "Enterprise Edition" --> Next

1.5 Setup Oracle Base and Oracle Home --> Next

1.6 Setup groups to "oinstall" --> Next:

  • Once again, I will not explain about the "Job Role Separation" or apply it for the purpose of our how to, please take a look at the link.

 1.7 Next step is to check if all prerequisites are satisfied --> Next:

  • As we are good DBA's and take's care about the environment, it will not complain about any failed prerequisite.

 1.8 Last look in the summary screen, to check if everything is where it need's to be --> Install:

 1.9 Installing....

2.0 It will be required to execute as root user the script $ORACLE_HOME/

Just pay attention to the "OK" button, because it is to be pressed only after finish the execution of the script.

2.1 During the script execution it will be prompt to setup the directory path to the: "dbhome", "oraenv" and "coraenv" scripts, that contains the variables of the Oracle  Database environment --> Just "Press Enter":

2.2 Finally the last screen click --> Close.

  • If you have followed step-by-step, it will be presented the following message:
          "The installation of Oracle Database was successful."

                                                                 See you in the next post

Thursday, November 3, 2016

Oracle Grid Infrastructure for a Standalone Server 12C - Installation

Hi, everyone.

Today in this article I will show you how to install and configure Oracle Grid Infrastructure for a Standalone Server 12c.
In my previous post, I showed how to install and prepare the Operating System to install Oracle Database.

If you don't even have your environment yet, please start from here: Oracle Linux 6.7 Installation - VM VirtualBox

1. After install and configure OEL 6.7 (Oracle Enterprise Linux), we need the Oracle Grid Infrastructure for a Standalone Server 12c installation packages, for more details and download: Oracle Database 12c (Linux x86_64) 
When download installation packages finish, we need to upload to the OS (Operating System) all packages, if you are using Windows platform I recommend use WinSCP, it's a free (GPL License) graphic tool that use SSH protocol to transfer through network using TCP/IP. 
But if your are using Linux or Mac, it will be simple, because SSH protocol comes natively in most of OS versions and SCP program it's part of OpenSSH package.
When upload packages has finished, we will unpack using unzip program:
  • Unpack package called: and after that, unpack package called:
  • Execute: unzip and unzip
It will be created a directory called grid with the following content: 

 2. Before start install Oracle Grid Infrastructure for a Standalone Server, we need to be sure about every prerequisites (Preinstallation Tasks), if you are using Windows to follow this "HowTo" you will need a client to access OS using SSH protocol (PuTTY) and it will be needed a program to export the display (Xming) from VM to physical machine (desktop). 
  In the "PuTTY Configuration", expand "Connection" menu, expand "SSH" menu, go to the "X11" option and check box "Enable X11 forwarding" and start Xming program:

Now you will be able to export the display of your VM to your physical machine (desktop).

3. In this step I will guide you how to check prerequisites before start installation setup. It is not uncommon run the installation software and after made every configuration the installation setup perform the prerequisites check with "Failed" status. To prevent it to happen, it's more easy to run first the setup installation with parameter "-executePrereqs", it will "jump" to the prerequisites check and after everything is compliance we can perform installation definitely.

If you followed my previous post or if you are installing own your own and followed the best practices reading the official documentation, you will have the same result as me "No results" for "Show Failed" option. 
But if there is some prerequisites with failed status you need to fix and run "Check Again" to be sure that there is no more complaints.
To call the installation setup utility, execute: ./runInstaller -executePrereqs

3.1 After match every prerequisites, call again the installation setup utility but without "-executePrereqs" parameter, select "Install and Configure Oracle Grid Infrastructure for a Standalone Server"

3.2 Select your preferred language --> Next.

3.3 In the step 3, we need to fill the field "Disk group name", choose the type of "Redundancy", configure "Allocation Unit Size" (Keep it default 1 MB), let's selected "Candidate Disks", in the option "Change Discovery Path" type "/dev/*", then OK --> Next.

  • The first disk group "DG_BLOG_DATA" it will be used to store data, datafiles and etc: /dev/sdb1
  •  Selecting "External" redundancy we assume that disks are redundant by Storage Server or anything else.
  • "Disk Discovery Path" is the same value of "ASM_DISKSTRING" parameter and points to the directory that "ASM" uses to discover candidates disks or disks that is already in use by some disk group.
  • When "Candidate Disks" option is selected only disks that are not in use and has the right permissions are presented to use, preventing us to make any mistake.

3.4 Set SYS and ASMSMP users password: 

3.5 If in your environment you already have an "Oracle Enterprise Manager Cloud Control 12c", you can set up your grid/database to be managed and monitored:

But if you are not using, just click --> Next.

3.6 In the step 6, we are prompted to choose some OS groups. If we intend to use "Job Role Separation", now is the time to determine each job role responsibility:

To this purpose we are not gonna configure separated user roles, this is more used in a big and huge companies, that has a good division of teams: OS, backup, storage, database, middleware, network team and etc. Inside of each team each one has separated duties. 

3.7 In the field "Oracle base" type "/opt/oracle" and "Software location" field type "/opt/oracle/product/12.1.0/grid": 

3.8 On step 8 the "Inventory Directory" field, type: "/opt/oracle/oraInventory"

3.9 I really prefer to take control about every action in the installation process, but you can just check box "Automatically run configuration scripts" and give one of the two alternatives:
  • Use "root" user credential, for that you need to type root user password in this field
  • Use "sudo", you will need to configure /etc/sudoers and add sudo permissions
I will not check or type any information now, just let unchecked and click: Next:

3.10 Now you can see that the installation will execute "Perform Prerequisite Checks", imagine that after make all configuration this step complain about something...

But we are "lucky" and everything is fine. 
3.11 It will be presented an installation summary and an option to "Save Response File" to install Oracle software in other environments using silent mode installation, click "Install":

3.12 Finally the installation has started and in some moment we will be prompted to execute two script files as "root" user:

After complete the execution of these two scripts, click "OK" and the installation will proceed and finish:

Now we have an ASM instance up running and one new disk group: "DG_BLOG_DATA".

4. I will show how to create the other two disk groups in two different ways:
  • Using sqlplus utility to create: DG_BLOG_REDO (/dev/sdc1)

Set ORACLE_SID and  ORACLE_HOME environment variable:
export ORACLE_HOME=/opt/oracle/product/12.1.0/grid 
Connect to the ASM instance using sqlplus: sqlplus '/ as sysasm' 

4.1 The another way to create disk group is using "Oracle ASM Configuration Assistant (ASMCA)":
 Execute: asmca: select "Create" --> Type "Disk Group Name" --> Rundancy "External" --> Select "Show Eligible" --> Check box "Disk Path" - "/dev/sdd1" --> OK.

4.2 Now you can see all the three disk groups created:

We are done here, the next step is just install and configure our database environment.

See you in the next post