Create 12c OS Files Based 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
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
(:input checked=checked) 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 components
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
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.log
{ORADATA2}/rdo/redo04b.log
->
Summary
Press Screen is initially blank for a moment...then process begins. 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 components
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 Screen is initially blank for a moment...then process begins. 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)
Multiplex Redo Logs and Control Files
Prerequisite (init file system):
mkdir -p /u03/oradata chown -R oracle:oinstall /u03/oradata chmod -R 775 /u03/oradata mkdir -p /u04/oradata chown -R oracle:oinstall /u04/oradata chmod -R 775 /u04/oradata
During dbca:
Location: 1. /u03/oradata Location: 2. /u04/oradata
OMF creates the following directories correspondingly:
/u03/oradata/ORADB /u03/oradata/ORADB/controlfile /u03/oradata/ORADB/onlinelog /u04/oradata/ORADB /u04/oradata/ORADB/controlfile /u04/oradata/ORADB/onlinelog
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;