oracledba.help
Create

Create Oracle 19c 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:

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

TOC

If Linux 8 run this before running dbca:
export CV_ASSUME_DISTID=OEL7.6

DBCA: File System Based Database

Prerequisites

  1. Choose a database name that wont come back and bite you.
  2. You have created the BASH profile for the oracle user account.
  3. 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  Edit archive mode parameters 
     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/19.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: ********
 Accounts: SYS, SYSTEM, DBSNMP

Creation Option 

 ☑ Create database
    Select  Customize Storage Locations 

    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: 🖸 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 🖸 Uniform and specify Bytes.

    Tablespace    Minimum Size
    -----------   ------------
    SYSAUX                3 gb
    SYSTEM                3 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:
        Add 
       Group #: 4
       File Size: 200 M Bytes
       File Name: {ORADATA1}/rdo/redo04a.rdo
                  {ORADATA2}/rdo/redo04b.rdo
        Apply 
  Next

Summary 

 Press:  Finish 
 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

  1. Choose a database name that wont come back and bite you.
  2. You understand Oracle Managed Files (OMF).
  3. You have created the BASH profile for the oracle user account.
  4. 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  Edit archive mode parameters  
     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/19.n.0.n/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: ********
 Accounts: SYS, SYSTEM, DBSNMP

Creation Option 

 ☑ Create database
    Select  Customize Storage Locations 
    For each where possible:
       ☑ Use Locally Managed Tablespace
           Allocation: 🖸 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 🖸 Uniform and specify Bytes.

    Tablespace    Minimum Size
    -----------   ------------
    SYSAUX                3 gb
    SYSTEM                3 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:
        Add 
       Group #: 4
       File Size: 200 M Bytes
       File Name: <OMF_4_REDOLOG_MEMBER_0>
        Apply 
   Next

Summary 

 Press:  Finish 
 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: RAC Based Database

Prerequisites

  1. You have installed and configured the Grid Infrastructure (GI).
  2. Oracle Database product is installed.
  3. As grid user, ensure core cluster components up:
    • crsctl status res -t -init
    • crsctl check cluster -all
  4. 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 &

Database Operation 

 🖸 Create Database

Creation Mode 

 🖸 Advanced configuration

Deployment Type 

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

Nodes Selection 

 Select all nodes to be used for RAC.
 Example:
 ☑ lnx01
 ☑ lnx02

Database Identification 

 Global Database Name: oradb
 SID Prefix: oradb
 Whatever name you choose will be used as the base name for instances.
 Ex: oradb1, oradb2...
 ☐ Create As Container Database

Storage Option 

 🖸 Use following for the database storage attributes 
     Database files Storage Type: Automatic Storage Management (ASM)
     Database files locations:    +DATA
     ☑ Use Oracle-Managed Files (OMF)

Fast Recovery Option 

  ☑ Specify Fast Recovery Area
      Recovery files Storage Type: Automatic Storage Management (ASM)
      Fast Recovery Area:          +FRA
      Fast Recovery Area Size:     <Set to three times the database size>
  ☑ 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

Configuration Options 

  Memory
    🖸 Use Automatic Shared Memory Management
        Set slider to use 50% to 80% of the systems RAM.
        Example:
        SGA size: 2394 mb
        PGA size:  800 mb
        Dont use Automatic Memory Management (AMM) for Linux Oracle systems.
  Sizing
     Block Size: 8192 Bytes
     Processes:  1500
  Character sets
     Use defaults unless your application requires different settings.
  Connection mode
     🖸 Dedicated Server Mode
  Sample Schemas
     ☐ Add sample schemas to the database

Management Options 

 ☑ Run Cluster Verification Utility (CVU) Check Periodically
 ☑ Configure Enterprise Manager (EM) Database Express
           EM Database Express Port: 5500

User Credentials 

 🖸 Use the same administrative password for all accounts
     Password:         ********
     Confirm Password: ********
 Accounts: SYS, SYSTEM, DBSNMP

Creation Options 

 ☑ Create database
    Select  Customize Storage Locations 
    After each change make sure to press:  Apply 

 • Control Files:  Options
      Maximum Datafiles:     256
      Maximum Instances:       8
      Maximum Log History:     1
      Maximum Redo Log Files: 16
      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                         3 gb |  5 gb
      OMF_SYSTEM_1                         3 gb |  5 gb
      OMF_TEMP_1                           5 gb | 32 gb
      OMF_UNDOTBS<n_1>                     2 gb |  5 gb
      OMF_USERS_1                         25 mb
 • Redo Log Groups  
      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
 ☐ Save as a Database Template <- 19.n dbca has bug if enabled!

Prerequisite Checks 

  Change anything identified as needed.

Summary 

   Press  Finish 
   Progress Page displayed...
   It may stay on 0% for a few minutes as it prepares for DB operations.

   Common log file locations: 
      /u01/app/oracle/cfgtoollogs/dbca/<db>/trace.log_<Timestamp>
      /u01/app/oracle/diag/rdbms/<db>/<inst>/trace/alert_<inst>.log
      tail -f /u01/app/oracle/diag/rdbms/oradb/oradb1/trace/alert_oradb1.log

APPENDIX

Delete Database

Run the Database Configuration Assistant (DBCA). 

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

If using GI, next you might need to SweepASM.

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

ANONYMOUS
APPQOSSYS
AUDSYS
DBSFWUSER
DBSNMP
DIP
GGSYS
GSMADMIN_INTERNAL
GSMCATUSER
GSMUSER
OJVMSYS
ORACLE_OCM
OUTLN
REMOTE_SCHEDULER_AGENT
SYS
SYS$UMF
SYSBACKUP
SYSDG
SYSKM
SYSRAC
SYSTEM
WMSYS
XDB

Common Errors

java.lang.NullPointerException

Option 1 

 1. export CV_ASSUME_DISTID=OEL7.6
 2. Restart DBCA.

Option 2
As root user (change the path if the location of your "scp" is not the same with below): 

 # Rename the original scp.
 mv /usr/bin/scp /usr/bin/scp.orig

 # Create a new file </usr/bin/scp>
 vi /usr/bin/scp

 # Add the below line to the new created file </usr/bin/scp>
 /usr/bin/scp.orig -T $*

 # Change the file permission.
 chmod 555 /usr/bin/scp

PRCS-1046 : Name is not valid

BACKGOUND 

 DBCA run for RAC environment with "Save as database template" checked.

CAUSE 

 Cause of this problem is BUG: 28811533.

Solution 

 Workaround is to unset check option to "Save as database template".

Automatic Memory Management (AMM)

If using LINUX dont use Automatic Memory Management (AMM) and the corresponding memory_target\memory_max_target parameters. This is because "The use of AMM is absolutely incompatible with [Linux] HugePages" -Oracle.

Use Automatic Shared Memory Management (ASMM) instead by setting the SGA_MAX_SIZE/SGA_TARGET and PGA_AGGREGATE_TARGET parameters.

<- Create