Database creation

This chapter presents the database creation script dmk_dbcreate.{ksh|ps1}.

Prerequisites

Sqlnet Setup for the new database:

  • If it is the first database on the node then copy the template files:

cp ${DMK_HOME}/templates/network/admin/*.ora ${TNS_ADMIN}/

  • Modify all the ${TNS_ADMIN}/*.ora files according to your host & database.

  • Finally (re)start the listener:

lsnrctl start LISTENER

Ensure the following directories exists on your system, the below mentioned mount points are defined within the DBCA templates which are customizable.

  • Oracle admin directory

/u01/app/oracle/admin

  • Oracle oradata directories

/u02/oradata

  • Oracle Flash Recovery Area ( newly called Fast Recovery Area)

/u90/fast_recovery_area

⚠️These directory must also have the correct privileges ( owner,write permission, …)

  • Ensure that DMK is sourced on the correct ORACLE_HOME before creating database. Usually there is one dummy entry in the oratab file for each ORACLE_HOME installed on the server. Before creating a database make sure that you switch DMK to the ORACLE_HOME you want to have the database created with.

Command-line parameters

The execution of dmk_dbcreate.{ksh|ps1} requires a configuration file, which contains the following parameters.

The script requires command lines argument in addition to the configuration file. Some of the configuration file parameters are also available in command-line.

The parameters given in command line are from higher priority compared to their configuration values.

The command line parameters are prefixed by a hyphen character as they would be used within a shell.

–s <ORACLE_SID>

This is the database name ORACLE_SID for which the DBCA operations start.

ℹ️ This ORACLE_SID is limited to 8 characters

–f <configuration file>

Parameter mandatory if the configuration file isn’t stored at the default location with default name:

${DMK_DBCREATE}/etc/${ORACLE_SID}.cfg

–t <dbca template file>

Optional parameter which permits to override the configuration file parameter “TEMPLATE_FILE_NAME”

–silent=TRUE|FALSE

Optional parameter to allow silent run with Ansible.

TRUE runs in silent mode. Also if -silent without an argument is specified then TRUE is used.

Deviates input to null and output to $DMK_HOME/log/dmk_dbcreate-silent_<timestamp>.log.

Overwrites the environment variable DBCREATE_SILENT.

Configuration file parameters

SID

The parameter <SID> in the configuration file, must have the same value as dmk_dbcreate.ksh -s <SID> when you start the database creation. The ORACLE_SID can only have 8 characters maximum.

DB_UNIQUE_NAME

dbi best practice is to set the parameter <DB_UNIQUE_NAME> to <SID>_SITE1 but this is not manadatory a name as <SID>RZ1 or <SID><CityName> are also valid.

DOMAIN_NAME

With this parameter you must inform the domain_name from the database you want to create.

OSTYPE

As OSTYPE you can enter the value UNIX for all (Linux/Unix OS ) or WINDOWS, but currently dmk_dbcreate.ksh work only for linux/unix database

DISKTYPE

Two different database templates exist for the DISKTYPE fs (file systems) or asm (Oracle ASM disks),

CHARCTER_SET & NATIONAL_CHARACTER_SET

It's mandatory to also define the CHARACTER_SET and NATIONAL_CHARACTER_SET. For theses parameters you can use all available Oracle characterset.

LOCAL_LISTENER

local_listener parameter can be set and space into local_listener setting are valid.

No comment character like “#” at the end of line are allowed.

And do not set the parameter between simple or double buckets

SERVICE_NAMES

AUDIT_RETENTION_DAYS

The above parameter is used to create a database audit cleanup job.

TEMPLATE_FILE_NAME

DBCA template which will be used for the database creation. If the parameter is empty then the default DBCA template will be used:

”${DMK_DBCREATE}/templates/dbca/database/dbi_template_<DISKTYPE><VERSION><CREATE_AS_CDB>.dbt”.

Note that the <VERSION> is automatically calculated from the release of the running Oracle Home used by dmk_dbcreate.

Manual created TEMPLATE_FILE_NAME should be located under ${DMK_DBCREATE}/etc.

Take care ! manual created TEMPLATE_FILE_NAME, must be created from the existing template files located under ${DMK_DBCREATE}/templates/dbca/database

VARIABLES

This Parameter allows you to define the paths directly into the configuration file, where the database files will be located. Thus you no longer need to manually adapt the template file, if you have only the paths to define.

Take Care ! No space is allowed into the “VARIABLES” parameter definition

CREATE_AS_CDB

With Oracle 12c dmk_dbcreate.ksh gives the possibility to create a Multitenant Database “CDB” or a Non-CDB database. This parameter is only interpreted with Oracle 12c.

To create a Multitenant Database set this parameter to true and do not forget to review the below parameters.

NUMBERS_OF_PDBS

Only interpreted with Oracle 12c Database and if CREATE_AS_CDB is true. Create the indicated number of pdbs

PDB_NAME_PREFIX

Only interpreted with Oracle 12c Database and if CREATE_AS_CDB is true. This parameter defines the prefix for the PDB naming. Usually use “PDB”

PDB_NAMES

A comma separates list of PDB names.

If omitted or empty then PDB’s are named with the PDB_NAME_PREFIX and the sequence number of the PDB.

The generated files are still named: plug_PDB<n>.sql and postPDBCreation_PDB<n>.sql, just inside these files the PDB name is globally replaced.

PDB_ADMIN_USERNAME

Only interpreted with Oracle 12c Database and if CREATE_AS_CDB is true. This parameter define the admin user of the PDB. We propose to use the username “PDBADMIN”

PDB_ADMIN_PASSWORD

Only interpreted with Oracle 12c Database and if CREATE_AS_CDB is true. This parameter define the password for the PDB_ADMIN_USERNAME user.

Customized database creation example

In this example with create a Oracle Container Database TESTDB with 2 pluggable database under Linux with the database files located on ASM.

  • Create Mandatory configuration File

Use the default Template.cfg configuration file, and copy it to create your configuration file.

Adapt all necessary parameter into your create TESTDB.cfg file. (see below adapt example)

If TEMPLATE_FILE_NAME is not specified, the default template file located under

${DMK_DBCREATE}/templates/dbca/database/ will be used.

While specifying the TEMPLATE_FILE_NAME parameter into the configuration file, you can use your own database template file. This file must be located under ${DMK_DBCREATE}/etc and should be based on the ones in ${DMK_DBCREATE}/templates/dbca/database/.

Create specific dbca template file per Commandline

As into the above defined configuration file, a TEMPLATE_FILE_NAME is specified. Now it is mandatory to create a dbca database template file under ${DMK_DBCREATE}/etc, the previous example pointed to TESTDB.dbt, the following scenery will focus on an “ASM” database using the TESTASM.dbt template:

From now on it is possible to adapt all parameters from the dbca template file before to create the scripts.

  • parameter to activate database options

If you create a Container database all Options will be automatically installed.

  • Parameter to set init parameters

  • parameter to define ASM diskgroups

  • parameter to define the tablespace size

Attention only the unit KB and MB are valid.

  • parameter to define the redolog size

Attention only the unit KB and MB are valid.

Create specific dbca template file with Oracle dbca GUI

  1. Copy the dbcreate template file into the default dbca assistants location

  1. Start dbca GUI

dbca

  1. Select Template file dbi_template_UNIX_asm_11gR2.dbt and adapt it with the GUI

  2. At the end save the template under ${DMK_DBCREATE}/etc/TESTDB.dbt

Unified Auditing

Then dmk_dbcreate.ksh is started, it validates if Oracle is linked with Unified Auditing. Below you’ll find the recommendation and the output example from oracle 18:

No Custom Audit Tablespace

Dmk_dbcreate uses the SYSAUX tablespace that means does not setup a custom audit tablespace.

This can be done manually, but there are some issues:

  • Move of the audit data in a custom tablespace does not properly work: index and lob partitions are not moved unless the patch below is installed:

  • Bug 27576342 dbms_audit_mgmt.set_audit_trail_location does not move lob and index partitions

    • The fix for 27576342 is first included in:

      • 18.7.0.0.190716 (Jul 2019) Database Jul2019 Release Update (DB RU)

      • 12.2.0.1.190716 (Jul 2019) Database Jul2019 Release Update (DB RU)

  • Only the next partitions will be created in the custom tablespace, current ones remain in SYSAUX (Per default unified auditing partitions are created with interval 30 days).

Enabled Unified Auditing Policies

By default the following auditing policies will be enabled for unified auditing, but disabled for legacy auditing:

  • ORA_SECURECONFIG

  • ORA_LOGON_FAILURES

Note: For CDB/PDB’s this will done only in the root container!

Audit purge job

The audit data retention can be configured in the templates under /u01/app/oracle/local/dmk_dbcreate/etc/ and is set per default as follows:

This retention is used to setup the audit purge job.

a) For Unified Auditing a purge job is created as follows:

b) For Legacy Auditing a purge job is created as follows:

Using Unified Auditing for CDB/PDB’s

It is buggy and has many restrictions:

  • Container=ALL is not supported! Neither for policies nor for cleanup:

  • Bug 22859443 : CONTAINER_ALL IS IGNORED IN CREATE_PURGE_JOB

    • no patch available

  • Bug 22596655 : AUDIT OPTIONS ENABLED WITH CONTAINER CLAUSE ARE NOT ENABLED IN PDBS

    • no patch available

DMK DBCreate configures Unified auditing only for CDB Root Container. The configuration (policies, cleanup…) for the PDB's has to be setup manually.

Auditing SYS-Operations with Unified Auditing

DB-parameter audit_sys_operations has no effect with unified auditing anymore. To get the same auditing of sys-operations with unified auditing as we have with audit_sys_operations=TRUE we need the enhancement request 21493004. See MOS Note "Master Note For Database Unified Auditing (Doc ID 2351084.1)".

→20190813_CBL_migrate_old_auditing_policies_to_unified_auditing.txt has been adjusted.

Fix against Unlimited SM/ADVISOR retention in oracle 12.2 & 18

The new SM/ADVISOR was introduced in Oracle 12.2., but unfortunately by default the retention is set to UNLIMITED. This causes excessive growth of SYSAUX tablespace; see Doc ID 2305512.1 and Doc ID 2439129.1.

As workaround the scripts dbi_best_practices_post_db_creation.sql changes the retention to 30 days.

Starting with oracle 19 the issue is fixed by Oracle.

Create the Database with dmk_dbcreate.ksh

Now it's time to start the creation of the database script at the database

PreviousFunctionalities & FeaturesNextStructure

Last updated