Create

Create 18c Database

Overview

What follows are the steps to create a standard Oracle database for an enterprise environment. It is a common practice to create your database in two major steps:

Create the core database as shown in these instructions.
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. The Oracle Database Configuration Assistant (DBCA) is used in the below methods to create the database.

You can view the progress of the dbca via:

 tail -f /u01/app/oracle/cfgtoollogs/dbca/<DBName>/<CurrentLog>

TOC

Create Database Options
- Create File System Based Database
- Create File System Based Database using OMF
Post Database Creation
Delete Database
SGA\PGA Models

DBCA: File System Based Database

Prerequisites

Choose a database name that wont come back and bite you.
You have created the BASH profile ? for the oracle user account.
As root you have created data directories.

Example session:

mkdir -p /u01/oradata
chown -R oracle:oinstall /u01/oradata
chmod -R 775 /u01/oradata

mkdir -p /u02/oradata
chown -R oracle:oinstall /u02/oradata
chmod -R 775 /u02/oradata

This example uses File Location Variables to the corresponding oradata folder. Using these vars makes it easier and helps avoid typos.

{ORADATA1}=/u01/oradata/{DB_NAME}
{ORADATA2}=/u02/oradata/{DB_NAME}

You can set these in the DBCA a few steps into the process.

Procedure

Logged in as oracle user:

Run dbca.

 cd $ORACLE_HOME/bin
 ./dbca &

Database Operation

  Create Database

Creation Mode

  Advanced configuration

Deployment Type

 Database type: Oracle Single Instance database
  Custom Database

Database Identification

 Global Database Name: oradb
 SID: oradb
  Create as Container database

Storage Option

  Use following for the database storage attributes
     Database files storage type: File System
     Database files location: {ORADATA1}/dbf

Fast Recovery Option

  Specify Fast Recovery Area

  Enable archiving 
     Archive Log File Format: %t_%s_%r.arc
     Archive log destinations: {ORADATA1}/arc
     Archive log destinations: {ORADATA2}/arc

Network Configuration

  Create a New Listener
     Name: LISTENER
     Port: 1521
     Home: /u01/app/oracle/product/12.n.0.n/dbhome_1

 If prompted: Data Vault Option

Database Options

 Only select what you will actually be using.
 Example:  Oracle JVM
 Dont  Include in PDBs if you are not running multitenant.

Configuration Options

 • MEMORY
    Use Automatic Shared Memory Management
       Set slider to about 50-80% of your RAM.
       8 gb RAM example (3192):
          SGA Size: 2394
          PGA Size: 798
   Dont use Automatic Memory Management (AMM) for Linux Oracle systems.?
 • SIZING
   Block size: 8192 BYTES
   Processes: 300
 • CHARACTER SETS
   <Use Defaults>
 • CONNECTION MODE
    Dedicated server mode
 • SAMPLE SCHEMAS
    Add sample schemas to the database

Management Options

  Configure Enterprise (EM) Database Express
     EM Database Express Port: 5500

User Credentials

  Use the same administrative password for all Accounts
     Password:         ********
     Confirm Password: ********

Creation Option

  Create database
    Select 

    File Paths
    • Control(2): {ORADATA1}/ctl/
                  {ORADATA2}/ctl/

    • Datafiles: {ORADATA1}/dbf

    • Redo:      {ORADATA1}/rdo
                 {ORADATA2}/rdo
                 File Name Format:
                 redoNNa.rdo
                 redoNNb.rdo

    For each tablespace where possible:
        Use Locally Managed Tablespace
           Allocation: (x) Automatic  
        Use Bigfile Tablespace

    Caveats
    • A SYSTEM tablespace cannot use Automatic Segment Space Management.
    • A Temp tablespace cannot be automatically allocated,
      i.e. you have to use (x) Uniform and specify Bytes.

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

    Datafiles
    Leave as is.

    Redo Log Groups
    Create a minimum 4 groups (default is 3).
    Example:
       
       Group #: 4
       File Size: 200 M Bytes
       File Name: {ORADATA1}/rdo/redo04a.rdo
                  {ORADATA2}/rdo/redo04b.rdo
        ->

Summary

 Press 
 Process runs...

 Alert Log Example: 
 tail -f /u01/app/oracle/diag/rdbms/oradb/oradb/trace/alert_oradb.log

 Once completed you will get a window that shows the creation status and 
 Oracle Express connection info. 
 Select

DBCA: File System Based Database using OMF

Prerequisites

Choose a database name that wont come back and bite you.
You understand Oracle Managed Files (OMF).
You have created the BASH profile ? for the oracle user account.
As root you have created data directories.

Example session:

mkdir -p /u01/oradata
chown -R oracle:oinstall /u01/oradata
chmod -R 775 /u01/oradata

mkdir -p /u02/oradata
chown -R oracle:oinstall /u02/oradata
chmod -R 775 /u02/oradata

Procedure

Logged in as oracle user:

Run dbca.

 cd $ORACLE_HOME/bin
 ./dbca &

Database Operation

  Create Database

Creation Mode

  Advanced configuration

Deployment Type

 Database type: Oracle Single Instance database
  Custom Database

Database Identification

 Global Database Name: oradb
 SID: oradb
  Create as Container database

Storage Option

  Use following for the database storage attributes
     Database files storage type: File System
     Database files location: /u01/oradata

     OMF creates the following directories correspondingly:
        /u01/oradata/ORADB
        /u01/oradata/ORADB/controlfile
        /u01/oradata/ORADB/datafile
        /u01/oradata/ORADB/onlinelog
     An ORADB subdirectory is automatically created.

  Use Oracle-Managed Files (OMF)

This will be the: DB_CREATE_FILE_DEST

Fast Recovery Option

  Specify Fast Recovery Area
     Recovery files storage type: File System
     Fast Recovery Area: /u02/oradata
     Fast Recovery size: 25 GB  => Determine FRA Size

This will be the: DB_RECOVERY_FILE_DEST

  Enable archiving 
     Archive Log File Format: %t_%s_%r.arc

     OMF creates the following directories correspondingly:
        /u02/oradata/ORADB
        /u02/oradata/ORADB/archivelog
        /u02/oradata/ORADB/controlfile
        /u02/oradata/ORADB/onlinelog

Network Configuration

  Create a New Listener
     Name: LISTENER
     Port: 1521
     Home: /u01/app/oracle/product/12.2.0.1/dbhome_1

 If prompted: Data Vault Option

Database Options

 Only select what you will actually be using.
 Example:  Oracle JVM

Configuration Options

 • MEMORY
    Use Automatic Shared Memory Management
       Set slider to about 50-80% of your RAM.
       8 gb RAM example (3192):
          SGA Size: 2394
          PGA Size: 798
   Dont use Automatic Memory Management (AMM) for Linux Oracle systems.?
 • SIZING
   Block size: 8192 BYTES
   Processes: 300
 • CHARACTER SETS
   <Use Defaults>
 • CONNECTION MODE
    Dedicated server mode
 • SAMPLE SCHEMAS
    Add sample schemas to the database

Management Options

  Configure Enterprise (EM) Database Express
     EM Database Express Port: 5500

User Credentials

  Use the same administrative password for all Accounts
     Password:         ********
     Confirm Password: ********

Creation Option

  Create database
    Select 
    For each where possible:
        Use Locally Managed Tablespace
           Allocation: (x) Automatic  
        Use Bigfile Tablespace

    Caveats
    • A SYSTEM tablespace cannot use Automatic Segment Space Management.
    • A Temp tablespace cannot be automatically allocated,
      i.e. you have to use (x) Uniform and specify Bytes.

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

    Datafiles
    Leave as is.  They are being managed by OMF.

    Redo Log Groups
    Create a minimum 4 groups (default is 3).
    Example:
       
       Group #: 4
       File Size: 200 M Bytes
       File Name: <OMF_4_REDOLOG_MEMBER_0>
        ->

Summary

 Press 

 Alert Log Example: 
 tail -f /u01/app/oracle/diag/rdbms/oradb/oradb/trace/alert_oradb.log

 Once completed you will get a window that shows the creation status and 
 Oracle Express connection info. 
 Select

APPENDIX

Delete Database

Run the Database Configuration Assistant (DBCA).

1. Select: (x) 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"

Fast Recovery Area (FRA) Size

The minimum recommended size is:

 FRA = database size + RMAN files + archived logs + flashback logs (if used)

New Database Initial Oracle Schema

AUDSYS
DBSNMP
GSMADMIN_INTERNAL
OJVMSYS
OUTLN
SYS
SYSTEM
WMSYS
XDB

SGA\PGA Models

 -- 2394m\798m
 alter system set sga_max_size=2394m        scope=spfile;
 alter system set sga_target=2394m          scope=spfile;
 alter system set pga_aggregate_target=798m scope=spfile;

 -- 1200m\400m
 alter system set sga_max_size=1200m        scope=spfile;
 alter system set sga_target=1200m          scope=spfile;
 alter system set pga_aggregate_target=400m scope=spfile;

 -- 1024m\256m
 alter system set sga_max_size=1024m        scope=spfile;
 alter system set sga_target=1024m          scope=spfile;
 alter system set pga_aggregate_target=256m scope=spfile;

 -- 512m\64m
 alter system set sga_max_size=512m        scope=spfile;
 alter system set sga_target=512m          scope=spfile;
 alter system set pga_aggregate_target=64m scope=spfile;

<- Create