oracledba.help
Legacy

Oracle 12c (12.1.0.2) Create Database: LINUX

<- Legacy

Overview

What follows are the steps to create a standard Oracle database using the Database Configuration Assistant (DBCA) for an enterprise environment. It is a common practice to create your database in two major steps:

  1. Create the core database as shown in these instructions.
  2. Create custom components required by the business mission (tablespaces, settings, schema etc.).

In this way, you can ensure your core database is structurally sound and issue free. Think of it like building a house. The steps outlined here are the foundation.

TOC

DBCA: File System Based Database (via OMF)

oradb is used as the database name in this example.

Prerequisites

  1. Dont make your database name the same as the hostname.
  2. You have installed the Oracle database product as indicated here.
  3. If not using ASM, as root create data directories as needed.
Example: 
mkdir -p /u01/oradata/ORADB
chown -R oracle:oinstall /u01/oradata
chmod -R 775 /u01/oradata

mkdir -p /u02/oradata/ORADB
chown -R oracle:oinstall /u02/oradata
chmod -R 775 /u02/oradata
Notice the database name is in UPPER case.

Procedure

Logged in as oracle user: 

 0. cd $ORACLE_HOME/bin
    ./dbca &

 1. Welcome 
    Next

 2. Database Operation
    🖸 Create Database

 3. Creation Mode
    🖸 Advanced Mode

 4. Database Template
    🖸 Custom Database

 5. Database Identification
    Global Database Name: oradb
    SID: oradb
    ☐ Create as Container database

 6. Management Options
    ☑ Configure Enterprise Manager (EM) Database Express
       EM Database Express Port: 5500

 7. Database Credentials
    🖸 Use the Same Administrative Passord for All Accounts

 8. Network Configuration
    ☑ Create a New Listener
    Name: LISTENER
    Port: 1521
    Home: /u01/app/oracle/product/12.1.0.2/dbhome_1

    If prompted: Data Vault Option [Next]

 7. Storage Locations
    Database files Storage Type: File System
    🖸 Use Common Location for All Database Files
        File Location: /u01/oradata OR +DATA
    ☑ Use Oracle-Managed Files (OMF)


    Recovery files Storage Type: File System
    ☑ Specify Fast Recoery Area
        Fast Recovery Area: /u02/oradata OR +FRA
        Fast Recovery size: 10 GB (3 times your expected databases size)

    ☑ Enable archiving [Edit archive mode parameters]
        ☑ Automatic Archiving
        Archive Log File Format: Change extension to: .arc

        OMF creates the following directories correspondingly:
          /u01/oradata/ORADB/controlfile
          /u01/oradata/ORADB/datafile
          /u01/oradata/ORADB/onlinelog

          /u02/oradata/ORADB/archivelog
          /u02/oradata/ORADB/controlfile
          /u02/oradata/ORADB/onlinelog

 8. Database Options
       Database Components (just select what you will be using)
       Example: ☑ Oracle JVM

 9. Initialization Parameters
    MEMORY
    🖸 Custom Settings
        Memory Management: Automatic Shared Memory Management
        Set to 50-80% of your RAM. 4 gb RAM example (1580):
        SGA Size: 1580
        PGA Size: 256

    *** Dont Use Automatic Memory Management ***

    SIZING
    Block size: 8192 BYTES
    Processes: 300

    CHARACTER SETS
    <Use Defaults>

    CONNECTION MODE
    🖸 Dedicated server mode

    SAMPLE SCHEMAS
    ☐ Add sample schemas to the database

 10. Creation Options
     ☑ Create database

     Select: [Customize Storage Locations]
         For each where possible:
         ☑ Use Locally Managed Tablespace
            Allocation: 🖸 Automatic  
         ☑ Use Bigfile Tablespace

         •• SYSTEM cannot use Automatic Segment Space Management.
            ☐ Automatic Segment Space Management
         •• TEMP cannot be automatically allocated,
            i.e. you have to select 🖸 Uniform and specify Bytes.

           Tablespace    Minimum Size
           -----------   ------------
           SYSAUX                1 gb
           SYSTEM                1 gb
           TEMP                  5 gb
           UNDOTBS1              2 gb
           USERS                25 mb

           Datafiles = Leaves as is.  They are being managed by OMF.

           Redo Log Groups (minimum 4 groups)
           ------------------------------------------
           Example:
           [Add]
              Group #: 4
              File Size: 200 M Bytes
              File Name: <OMF_4_REDOLOG_MEMBER_0>
              [Apply]

 11. Create Database - Summary
     Press [Finish]
  • Once completed you will get a window that shows the creation status and Oracle Express connection info.
  • Select [Close].

DBCA: RAC Based Database

Prerequisites

  1. Oracle Database product is installed.
  2. You have created the BASH profile? for the oracle user account.
  3. Your SA has provided disks and volumes for ASM.
  4. You have installed and configured the Grid Infrastructure (GI).
  5. As grid user, ensure core cluster components up:
    • crsctl status res -t -init
    • crsctl check cluster -all
  6. Using asmca you have created the required Disk Groups
    Commonly created groups below:
    • GRID: Grid Infrastructure Management Repository (AKA GIMR), MGMT, OCR and Voting Files.
    • DATA: DATA: Control, data, redo, parameter, password and temp files.
    • FRA: FRA (Fast Recovery Area): Archived logs, control and redo files.

Procedure

Perform the below as the oracle user account. 

  cd $ORACLE_HOME/bin
  ./dbca &

Welcome 

 Next

Database Operation 

 🖸 Create Database

Database Mode 

 🖸 Advanced Mode

Database Template 

  Database Type:      [Oracle Real Application Clusters (RAC) Database]
  Configuration Type: [Admin-Managed]
  🖸 Custom Database

Database Identification 

 Global Database Name: oradb
 SID Prefix: oradb

 Note: Whatever name you choose will be used as the base name for instances.
 Ex: oradb1, oradb2 ...

 ☐ Create As Container Database

Database Placement 

 Move all nodes to: Selected
 Example Selected: lnx01, lnx02

Management Options 

 ☑ Run Cluster Verification Utility (CVU) Check Periodically
 ☑ Configure Enterprise Manager (EM) Database Express
    EM Database Express Port: 5500
 ☐ Register with Enterprise Manager (EM) Cloud Control

Database Credentials 

 🖸 Use the same administrative password for all accounts
     Password:         ********
     Confirm Password: ********

Storage Locations 

 Database files Storage Type: Automatic Storage Management (ASM)
    🖸 Use Common Location for All Database Files
        [Browse] -> +DATA
        ☑ Use Oracle-Managed Files

    Recovery Files Storage Type: Automatic Storage Management (ASM)
    ☑ Specify Fast Recovery Area
       Fast Recovery Area:      +FRA
       Fast Recovery Area Size: 7851 <use default or greater>
    ☑ Enable Archiving
       [Edit Archive Mode Parameters]
       ☑ Automatic Archiving
          Archive Log File Format: Change extension to: .arc

Database Options 

 Only select Database Components you will actually be using.
 Example: ☑ Oracle JVM

Initialization Parameters 

    🖸 Custom Settings
    Memory Management: [Automatic Shared Memory Management]
       SGA size: 2394 mb
       PGA size:  800 mb
    Sizing
       Block Size: 8192 Bytes
       Processes:  1500
    Character Sets
       🖸 Use Default
    Connection Mode
       🖸 Dedicated Server Mode

Creation Options 

    ☑ Create database

    Select: [Customize Storage Locations]
    ===> Make sure to press [Apply] after each change!  <===

   • Control Files:  Options
        Maximum Datafiles: 256
        Maximum Instances: 32
        Maximum Log History: 1
        Maximum Redo Log Files: 192
        Maximum Log Members: 3

    • Tablespaces
        For Each where possible:
           ☑ Use Locally Managed Tablespace
              Allocation: 🖸 Automatic  
           ☑ Use Bigfile Tablespace

        •• SYSTEM cannot use Automatic Segment Space Management.
           ☐ Automatic Segment Space Management
        •• TEMP cannot be automatically allocated,
           i.e. you have to select 🖸 Uniform and specify Bytes.
    • Datafiles
        ☑ (AUTOEXTEND)
           Increment: 25 MBytes
        🖸 Unlimited

      File                               Size (small | medium-large)
      --------------------------------   ---------------------------
      OMF_SYSAUX_1                       1024 mb |  2 gb
      OMF_SYSTEM_1                       1024 mb |  2 gb
      OMF_TEMP_1                            5 gb | 32 gb
      OMF_UNDOTBS<n_1>                      2 gb |  5 gb
      OMF_USERS_1                          25 mb

    • Redo Log Groups (minimum 4 groups with two members each)

      File Name                            Size
      -----------------------------------  ------
      <OMF_1_REDOLOG_MEMBER_0>             200 mb
      <OMF_2_REDOLOG_MEMBER_0>             ...
      <OMF_3_REDOLOG_MEMBER_0>             ...
      <OMF_4_REDOLOG_MEMBER_0>             ...

     ☑ Create Database

Create Database - Summary 

     Press [Finish]
     Progress Page displayed...

     You can tail the corresponding log files in:
        /u01/app/oracle/diag/rdbms/oradb/oradb1/trace
        /u01/app/oracle/cfgtoollogs/dbca/oradb
  • Once completed you will get a window that shows the creation status and Oracle Express connection info.
  • Select [Close].

APPENDIX

Delete Database

Run the Database Configuration Assistant (DBCA). 

1. Select: 🖸 Delete Database.
2. Delete Database: Ensure database to delete is selected.
3. Management Options: Select Next accepting defaults.
4. Summary: Confirm this is the database you want deleted.
            Then press Finish
5. Process will run...
6. When completed you will see the message:
   "Database deletion completed"

Add RAC Database Instance

  • Assumes Oracle database product already installed.
  • Run using the oracle user from first RAC node. You will add a node from there.
  1. Database Operation
    🖸 Instance Management
  2. Instance Operation
    🖸 Add an instance
  3. Database List
    You should see Database listed\selected. Select Next.
  4. List Instance
    Existing instances will be displayed.
    Click Next to add the new instance.
  5. Add Instance
    Instance Name: db3 (increments existing base name)
    Node name: <Hostname Displayed>
  6. Summary
    • If need be change the Custom Storage Locations.
    • Review then press Finish.
  7. Progress Page (process displayed)
    • Adding instance...
    • Completing instance management...
  8. Finish
    Success = Instance "<InstanceName>" added successfully on node "<NodeName>".
    Select Close.
  9. Confirm
    srvctl status database -d <DBName>
    Added instance should be displayed.

Delete RAC Database Instance

This process deletes the Oracle instance and its associated OFA directory structure. All information about this instance will be deleted. Run using the oracle user from first RAC node. You will delete the node from there.

  1. Run DBCA
    cd $ORACLE_HOME/bin
    ./dbca &
  2. Database Operation
    🖸 Instance Management
  3. Instance Operation
    🖸 Delete an instance
  4. Database List
    You should see the Database listed\selected.
    Select Next.
  5. List Instance
    Select instance from list to delete.
    Click Next.
  6. Summary
    • Instance to be deleted will be displayed.
    • If correct press Finish.
  7. Progress Page (process displayed)
    • Deleting instance...
    • Completing instance management...
  8. Finish
    Success = Instance "<InstanceName>" deleted successfully on node "<NodeName>".
    Select Close.
  9. Confirm
    srvctl status database -d <DBName>
    Deleted instance should not be displayed.

New Database Initial Oracle Schema

AUDSYS
DBSNMP
GSMADMIN_INTERNAL
OJVMSYS
OUTLN
SYS
SYSTEM
WMSYS
XDB

<- Legacy