Create Oracle 21c Database
Attention
Starting in Oracle 21c, all new databases must be created as a
container database, i.e. use multitenant.
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.
TOC
If Linux 8 run this before running dbca: export CV_ASSUME_DISTID=OEL7.6
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
☑ Use Local Undo tablespace for PDBs
🖸 Create a Container database with one or more PDBs
Number of PDBs: 1
PDB name: oradbpdb1
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/20.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 2 gb
SYSTEM 2 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 Close
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
☑ Use Local Undo tablespace for PDBs
🖸 Create a Container database with one or more PDBs
Number of PDBs: 1
PDB name: oradbpdb1
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/21.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 2 gb
SYSTEM 2 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 Close
DBCA: RAC Based Database
Prerequisites
- You have installed and configured the Grid Infrastructure (GI).
- Oracle Database product is installed.
- As grid user, ensure core cluster components up:
- crsctl status res -t -init
- crsctl check cluster -all
- 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 ☑ Use Local Undo tablespace for PDBs 🖸 Create a Container database with one or more PDBs Number of PDBs: 1 PDB name: oradbpdb1
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 2 gb | 5 gb
OMF_SYSTEM_1 2 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 <- 21.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.