Tuesday, September 3, 2013

Creating 12c R1 non-cdb Database in ASM on OEL 6 x64

Starting from Oracle Database 12c R1 Oracle database supports multi-tenant architecture. This architecture has container database which stores metadata of multiple pluggable databases. In this post I will discuss how to create regular Oracle database called as non-container database. Creating container database and pluggable database will be covered in upcoming posts.

For details of following installations please review my earlier posts:

Oracle Universal Installer (OUI) allows creating database during the installation of Oracle Database 12c R1 installation. However, it is always recommended:

  • Firstly, to install Oracle Database software
  • Secondly, create the listener if listener does not exist. Listener is already created when Oracle GI is installed.
  • Finally, create the database using dbca (Oracle Database Configuration Assistant).

NOTE: Since we are using ASM disk group to store database files, it is recommended to have Oracle GI installed and ASM disk group created before creating the database.

Environment Variables of oracle user

[oracle@db12c-1 ~]$ id -a
uid=64321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54330(asmadmin),54333(oper) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@db12c-1 ~]$ 
[oracle@db12c-1 ~]$ pwd
[oracle@db12c-1 ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc

# User specific environment and startup programs


export PATH

# Oracle Settings for oracle user
export TMP=/tmp
export TMPDIR=$TMP

export ORACLE_HOSTNAME=db121c
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/
export ORACLE_SID=noncdb
export ORACLE_UNQNAME=noncdb

export PATH=/usr/sbin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

[oracle@db12c-1 ~]$ 

We have already completed step 1 and 2 in our earlier posts. Now, let’s create database in ASM using dbca. Follow the following instructions:

1. Login as oracle user

  •  Login to the server as oracle user.
  • Do not login as root user and then switch the user using “su” command. In such cases one might have problem with the display and dbca screen might not open. 

2. Run dbca

  • Check the location of dbca utility.
  • In case of multiple Oracle Homes (ORACLE_HOME) it is always recommended to check from which Oracle Home (ORACLE_HOME) dbca utility is being executed.

3.  Database Operation

  • Select “Create Database” radio option.
  • Click on “Next” button to continue.   

4.  Creation Mode

  • Click on “Advanced Mode”.
  • Click on “Next” button to continue.

5. Database Template

  • Select “General Purpose or Transaction Processing” radio option.
  • Click on “Next” button to continue.
  • Click on “Show Details…” button if you want to see details of each option.

6. Database Identification

  • Enter the "Global Database Name".
  • Enter the "SID" (System Identifier).
  • Click on "Next" button to continue.

7. Management Option

  • Un-check “Configure Enterprise Manager (EM) Database Express” check box. We will configure EM Database Express later.
  • Un-check “Register with Enterprise Manager (EM) Cloud Control”. Since we do not have EM Cloud control at the moment we will not use this option either.
  • Click on “Next” button to continue.

8. Database Credentials

  • Select “Use the Same Administrative Password for All Accounts” radio option. Since this is testing system, for simplicity we are using this option. In production systems it is always recommended to use different and strong passwords for different Administrative accounts.
  • Click on “Next” button to continue.

9. Network Configuration

  • Register the database with the listener whose status is “Up”.
  • Click on “Next” button to continue.

10. Storage Locations

Select the following options for "Database Files":
  • Storage Type: Automatic Storage Management (ASM)
  • Select “Use Common Location for All Database Files” radio option.
  •      Click on “Browse” button to select the ASM disk group in “Database File Locations”. In our case we have selected “+DATA_DISK01” ASM disk group.
Since we are not configuring any recovery settings so select the following options for "Recovery Related Files" in order to disable these settings:
  • Storage Type: File System
  • Un-check "Specify Fast Recovery Area"
  • Un-check "Enable Archiving"
  • Click on “Next” button to continue.

11. File Location Variables

  • Click on “File Location Variables…” button to check different environment variables.
  • Click on “Next” button to continue.

12. Database Options

a. Sample Schemas

  • Check “Sample Schemas” check box, this option creates different sample schemas such as SCOTT, HR e.t.c. This option should not be selected in case of production systems. Selecting this option in production systems violates PCI (Payment Card Industry) compliance.
  • Click on “Browse” button to select script to run after database is created. For e.g. Create a sql script to change the shared pool size and browse the script to run after database creation:
          alter system set shared_pool_size=50M;
  • Click on “Next” button to continue.

b. Database Vault & Label Security

  • Database Vault:
          Un-check “Configure Database Vault” check box. We are not going to configure Database Vault now.
  • Label Security:
          Un-check “Configure Label Security” check box. We are not going to configure Label Security now.
  • Click on “Next” button to continue.

13. Initialization Parameters

a. Memory

By default Oracle dbca utility allocates 40% of available memory to database.
  • Typical Settings:
          Leave the default value of 40%.
  • Check “Use Automatic Memory Management” check box. Oracle always recommends to use AMM (Automatic Memory Management) until and unless you have a strong reason not to use the feature.
  • Click on “Sizing” tab.

b. Sizing
  •      Processes: Increase or decrease the value of process as required.
  •      Click on “Character Sets” tab

           c. Character Sets
  •       Select on “Use Unicode (AL32UTF8)” radio option. If you select “Choose from the list of character sets” you can also select different character sets from available list. Starting from Oracle Database 12c R1 it is recommended to use AL32UTF8 character. In case using CDB (Container Database) this character set has flexibility to store of any character set.
  •       National Character Set: AL16UTF16 - Unicode UTF-16 Universal character set
  •       Default Language: American
  •       Default Territory: United States
  •       Click on “Connection Mode” tab.

    d. Connection Mode

  •       Select “Dedicated Server Mode” radio option.
  •       Click on “Next” button to continue.

14. Creation Options

  •       Check “Create Database” check box to create database.
  •       Check “Save as a Database Template” if you want to save the configuration as a template.
  •       Check “Generate Database Creation Scripts” if you want to save the scripts to create database.
  •       Click on “Customize Storage Locations” to check and modify the storage parameters.

15. Storage Locations

  •      Click on “Control Files” node.

a. Control Files

  •       Add the number of control files or change the location as required.
  •       Click on “Datafiles” node.

b. Datafiles

  • Check the location of different datafiles and change if required.
  • Click on “Redo Log Groups” node.

c. Redo Log Groups

  •       Click on “Redo Log Groups” node to check the default number of redo log groups. By default Oracle creates 3 redo log groups with 1 member in each group. Click on “Add” button to add new log group.

  •       Click on “1” node to check and change the parameters such as file size and file name of Redo Log Group 1.

  •       Click on “2” node to check and change the parameters such as file size and file name of Redo Log Group 2.

  •       Click on “3” node to check and change the parameters such as file size and file name of Redo Log Group 3.
  •       Click on “Close” button to continue.

16. Prerequisite Checks

  •       Oracle automatically performs validation checks and flags for any prevailing errors and warnings. In case there are any errors and warnings these should be resolved before preceding any further.
  •       Click on “Next” button to continue.

17. Create Database Summary

  •        Review the “Summary Page” properly. In case any changes have to be made click on “Back” button to go back to the desired screen to make changes.

18. Progress page

       a. Script Generation
  •       In case “Generate Database Creation Scripts” check box was checked in create options page, dbca will show confirmation after it saves the database creation scripts to file system.
  •        Click on “Ok” button which will start the “Progress Page” showing database configuration in progress.

         b. Database Creation

  •      “Progress Page” showing database configuration in progress.

          c. dbca Complete

  •        After database creation is complete dbca will show a dialog box with the location of log files created during the database creation operation, DB name, SID and location of spfile. Details of EM Express will only be shown in case check “Configure Enterprise Manager (EM) Database Express” check box was check in Management Options page.

          d. dbca password management

  •        Click on “Password Management” button to see list of users created by dbca.
  •        This screen can also be used to unlock the locked users and change the passwords.
  •        Click on “Ok” button to close the “Password Management” page.

           e. Progress Page – Complete

  •       When the progress bar shows 100% click on “Close” button to close the finish the database configuration (dbca).

Hope this helps :).

No comments:

Post a Comment