Creating the Oracle Database Release 12c (12.1.0.1.0) Instance for Data Synchronization Using Oracle GoldenGate 12c on Linux

Overview

    Purpose

    This Oracle-By-Example (OBE) tutorial covers creating and configuring the Oracle Database version 12.c (12.1.0.1.0)  instance to be used together with Oracle GoldenGate 12c to test out data synchronization. You will create the database instance using the standard Oracle utility dbca.

    Time to Complete

    Approximately 30 minutes.

    Introduction

    Several OBEs in this section (Oracle Learning Library - Oracle GoldenGate) require having the Oracle database software installed and a properly configured Oracle instance available to support data replication. This OBE teaches you how to create an Oracle instance suitable for data replication using Oracle GoldenGate. 

    Hardware and Software Requirements

    The following is a list of hardware and software requirements:

    Hardware and OS
    • One Linux host: it can be a virtual machine. This OBE uses Oracle Linux OL 6.5, 64-bit.
    • A different version of the Linux OS can also be used, as long as it is supported by Oracle Corporation such as RedHat Linux and other RedHat-based distributions. CentOS can also be used.
    Software
    • Linux has been installed with a graphic desktop environment. This OBE assumes Gnome as the desktop of choice.
    • The Oracle Database release 12.1.0 .1.0 has been installed in "standard" directory locations, according to Optimal Flexible Architecture (OFA) guidelines. ORACLE_HOME points to /u01/app/oracle/product/12.1.0/dbhome_1.

    Prerequisites

    Before starting this tutorial, you should:

    • Have root access to the Linux environment
    • Have web access to download the software and documentation.

1. Preparing the Host Environment

    On the Linux host (host01.example.com) you must create the OGG12 instance using the dbca utility.

    1.1 Creating Terminal Shells for "root" and "oracle."

      Login to the Linux computer using a Gnome environment connected as oracle.

      Position your mouse pointer somewhere in the main Gnome background and right-click. A pop-up window appears. Select and click "Open in Terminal" twice to create two terminal windows:

      Click and drag the second window to align it next to the first one:

      Click the border of the left-most window to make it active, then click Terminal, and then select the Set Title option from the drop-down window:

      Name the window ROOT. You will run the OS commands which require superuser privileges in this window.

      Select the right-most window and click Terminal, then select Set Title and enter ORACLE as the title for the window. You will run the Oracle sqlplus utility connected to the OGG12 database in this window.

      Select the ROOT window and use the su command to become superuser. NOTE: the su command is followed by a space and a dash (-) to force su to provide an environment similar to what the user would expect had the user logged in directly.

      Host01 - ROOT
      [oracle@host01 Desktop]$ su - 
      Password:  ******
      [root@host01 ~]#                                 
      

    1.2 Creating the Database Instance Directories

      On the Linux host you must create the containers for the database files.

      Select the ROOT window and create the directory /u02/oradata
      Change ownership of the newly created directory to the oracle user. The default group for the oracle user is oinstall.  

      Host01 - ROOT
      [root@host01 ~]# mkdir -p /u02/oradata 
      [root@host01 ~]# chown -R oracle.oinstall /u02/oradata                              
      

      IMPORTANT: The database instance you are about to create requires approximately eight gigabytes of disk space. Make sure that in your environment /u02/oradata has at least eight gigabytes available. If you do not have a device mounted to host /u02/oradata and that directory would be created on the root filesystem (/) make sure that the root filesystem has at least eight gigabytes available (more would be better) so that you do not risk to flood the system disk. If you have limited space on the system disk but you have a lot of space on a different mount point, you can create a logical link /u02 which points to the filesystem which has the necessary space for the creation of the database instance.

    1.3 Increasing the size of /dev/shm

      Depending on the Linux distribution and release installed on the system, the default size allocation  for /dev/shm (shared memory) could not be enough to allow the creation of the RDBMS instance, with an SGA size of two gigabytes. You will create an instance that uses the Automatic Memory Management (AMM) feature. When an Oracle RDBMS instance tries to use automatic memory management and the tmpfs mount point is less than the value specified in the Oracle database instance parameter MEMORY_MAX_TARGET, Oracle throws the error "ORA-00845: MEMORY_TARGET not supported on this system"

      To prevent the occurrence of the ORA-00845 error, you must increase the shared memory size to eight gigabytes.

      Select the ROOT window. Open /etc/fstab with vi or any text editor of your choice and identify the line which begins with tmpfs.

      Host01 - ROOT
      [root@host01 ~]# vi /etc/fstab 
      LABEL=root              /                       ext3    defaults        1 1
      LABEL=boot              /boot                   ext3    defaults        1 2
      LABEL=SWAP              swap                    swap    defaults        0 0
      tmpfs                   /dev/shm                tmpfs   defaults        0 0            
      devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
      sysfs                   /sys                    sysfs   defaults        0 0
      proc                    /proc                   proc    defaults        0 0
      LABEL=u01               /u01                    ext3    defaults        0 0          
      
      Modify the defaults for tmpfs increasing its size to eight gigabytes. Exit vi (or the editor of your choice) saving the /etc/fstab file.

      Host01 - ROOT
      LABEL=root              /                       ext3    defaults        1 1
      LABEL=boot              /boot                   ext3    defaults        1 2
      LABEL=SWAP              swap                    swap    defaults        0 0
      tmpfs                   /dev/shm                tmpfs   defaults,size=8G        0 0
      devpts /dev/pts devpts gid=5,mode=620 0 0 sysfs /sys sysfs defaults 0 0 proc /proc proc defaults 0 0 LABEL=u01 /u01 ext3 defaults 0 0  
      Verify the current size configuration for /dev/shm using the df -h command.

      Host01 - ROOT
      [root@host01 ~]# df -h 
      Filesystem            Size  Used Avail Use% Mounted on
      /dev/xvda2             12G  2.9G  7.7G  28% /
      tmpfs                 4.0G     0  4.0G   0% /dev/shm
      /dev/xvda1            243M   71M  160M  31% /boot
      /dev/xvdb1             60G  322M   56G   1% /u01
                                                                                             
      
      Re-mount the /dev/shm device using the mount -o remount command. Verify that the shared memory space has increased to eight gigabytes.

      Host01 - ROOT
      [root@host01 ~]# mount -o remount /dev/shm
      [root@host01 ~]# df -h
      Filesystem            Size  Used Avail Use% Mounted on
      /dev/xvda2             12G  2.9G  7.7G  28% /
      tmpfs                 8.0G     0  8.0G   0% /dev/shm
      /dev/xvda1            243M   71M  160M  31% /boot
      /dev/xvdb1             60G  322M   56G   1% /u01
      [root@host01 ~]#
                                                                                             
      

2. Creating the Oracle OGG12 Instance

    After the Linux host environment has been prepared, you must create the Oracle database instance OGG12.

    2.1 Using the dbca utility

      Select the  ORACLE window. Make sure you don't have any instance running. If you do, use sqlplus to shutdown the instance(s) currently running on the Linux host host01. At the OS prompts, invoke the DBCA utility, by typing "dbca" lowercase.
      Host01 - ORACLE
      [oracle@host01 Desktop]$ dbca                                                          
      
      After a few second the DBCA  splash window appears on screen.

      When DBCA shows its first screen, choose "Create a Database" and click Next.

      In the next form, choose "Advanced Mode" to have DBCA display more forms, where you can choose to set database parameters, rather than accepting the defaults. Click Next.

      In the next form, choose "General Purpose or Transaction Processing" and click Next.

      The next form asks you to specify the the database name. Type in ogg12 (lowercase.) The second field in the form is automatically filled in with the database name. Leave deselected the checkbox "Create as Container Database." Click Next.

      Select "Configure Enterprise Manager (EM) Database Express" when the next form displays on the screen, and click Next.

      Click on the option "Use the Same Administrative Password for All Accounts" and type in "oracle" as the password. Type in "oracle" in the "Confirm Password" field. Click Next.

      If no listener has been created on the Linux host so far (maybe this is the first time you are running dbca) the form displaysone single row with an empty name and an empty port number:

      If a listener is already present on the system, the form displays the currently defined listener plus an empty row below it.

      If you do not have a listener, click the first cell in the Name column and type LISTENER. Click the first cell in the Port column and type 1521. Select the Select checkbox and click Next. If a listener is already defined for the system, accept the default configured listener and select the Select checkbox. Click Next to progress to the next form.

      The next screen asks you to configure the database file locations. In the "Database Files" section, accept the default (File System) for "Storage Type." Click on the option "Use Common Location for All Database Files" and type in "/u02/oradata" as the location.  In the "Recovery Related Files" section,
      accept the default (File System) for "Storage Type." Select the "Specify Fast Recovery Area" checkbox and enter "/u02/oradata/fast_recovery_area." Increase the "Fast Recovery Area Size" field to 6800. IMPORTANT: Select the "Enable Archiving" checkbox and click Next.

      Accept all defaults for "Sample Schemas" and "Database Vault & Label Security." Click Next.

      In the next form you specify initialization parameters, such as memory size. Set the memory size to 2048 MB and select "Use Automatic Memory Management." Click on the "Character Sets" tab.

      Select the "Use Unicode (AL32UTF8)" option and select the "Connection Mode" tab.

      Select the option "Dedicated Server Mode" as Connection Mode. Click All Initialization Parameters.

      When the "All Initialization Parameters" window appears, click Show Advanced Parameters.

      After the window showing the advanced initialization parameters appears, use the scroll bar on the right-end side to scroll towards the bottom of the list and locate the parameter "streams_pool_size." Change its value to 512M. Integrated Extract uses memory taken from the streams_pool_size memory allocation. This is the reason why you are increasing the size of the streams_pool_size parameter. Click Close, then click Next.

      Accept he default tick on "Create Database" and click Next.

      The DBCA utility runs the prerequisite checks. If those checks are validated, a green tick sign appears under the "Severity" column, and and you can proceed. Click Next.

      The Summary window is displayed. You can verify that all your setting have been entered correctly. Click Finish to force the DBCA utility to create the database instance.

      The database creation step starts. A "Progress Page" page is displayed on screen showing the various phases of the database instance creation.

      After a while the process completes a window is displayed summarizing all important database instance facts, such as its name, its System Identifier, etc. Click Exit to dismiss the window.

      Click Close to dismiss the "Progress Page" window.

      The database instance OGG12 has been created.

    2.2 Adding the ORACLE_SID variable to .bashrc

      You must add the ORACLE_SID environment variable, which points to the OGG12 instance, to your shell environment.
      Select the ORACLE window and change directory to the default directory (~) then edit the bashrc hidden file. Since the file is hidden, you must prefix its name with a dot (.). Add the ORACLE_SID variable and leave the editor, saving the file.

      Host01 - ORACLE
      [oracle@host01 Desktop]$ cd ~
      [oracle@host01 ~]$ vi .bashrc 
      
      # .bash_profile
      
      # Get the aliases and functions
      if [ -f ~/.bashrc ]; then
              . ~/.bashrc
      fi
      
      # User specific environment and startup programs
      
      PATH=$PATH:$HOME/bin
      
      export PATH
      
      ORACLE_SID=ogg12
      export ORACLE_SID
      

      Leave the editor saving the bashrc file.

      Source the .bashrc file to force the shell to reload the environment variables. Make sure ORACLE_SID is defined once you sourced the .bashrc file.

      Host01 - ORACLE
      [oracle@host01 ~]$ . .bashrc
      [oracle@host01 ~]$ echo $ORACLE_SID
      ogg12
      [oracle@host01 ~]$ 
      

    2.3 Verifying that the OGG12 instance is accessible

      The ultimate connectivity test consists of using tnsping to verify that the instance is accessible network-wise. 
      Additionally, sqlplus can be used to connect to OGG12.

      Host01 - ORACLE
      [oracle@host01 ~] tnsping ogg12 
      TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 09-FEB-2014 23:09:39
      
      Copyright (c) 1997, 2013, Oracle.  All rights reserved.
      
      Used parameter files:
      /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
      
      
      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)
      (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ogg12))) OK (0 msec)
      [oracle@host01 ~]$ sqlplus / as sysdba@ogg12 SQL*Plus: Release 12.1.0.1.0 Production on Sun Feb 9 23:17:27 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select name from v$database; NAME --------- OGG12 SQL>  

      This last step completes this OBE.

Summary

    In this tutorial, you have learned how to:

    • Prepare the Linux host environment for the Oracle instance supporting data replication.
    • Increase the size of the /dev/shm to prevent the occurrence of the Oracle error ORA-00845.
    • Use dbca to create the Oracle instance OGG12.
    • Increase the size of the streams_pool_size parameter to support Integrated Extract.
    • Verify and test the newly created Oracle database instance.

    Resources

    Credits

To help navigate this Oracle by Example, note the following:

Hiding Header Buttons:
Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
Topic List Button:
A list of all the topics. Click one of the topics to navigate to that section.
Expand/Collapse All Topics:
To show/hide all the detail for all the sections. By default, all topics are collapsed.
Show/Hide All Images:
To show/hide all the screenshots. By default, all images are displayed.
Print:
To print the content. The content currently displayed or hidden will be printed.

To navigate to a particular section in this tutorial, select the topic from the list.