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)
/u02/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.
SID = <SID>
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.
DB_UNIQUE_NAME = <SID>_SITE1
DOMAIN_NAME
With this parameter you must inform the domain_name from the database you want to create.
DOMAIN_NAME = it.dbi-services.com
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
OSTYPE = UNIX|WIN
DISKTYPE
Two different database templates exist for the DISKTYPE fs (file systems) or asm (Oracle ASM disks),
DISKTYPE = asm|fs
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.
Note that the <VERSION> is automatically calculated from the release of the running Oracle Home used by dmk_dbcreate.
TEMPLATE_FILE_NAME = <FILENAME>.dbt
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.
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:
Select Template file dbi_template_UNIX_asm_11gR2.dbt and adapt it with the GUI
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:
The use of Unified Auditing by DMK dbcreate:
--------------------------------------------
- is default in Oracle version >= 19
- is optional in Oracle version 12.2 and 18
- is not recommended in Oracle version <= 12.1
Currently the ORACLE_HOME=/opt/oracle/product/18c/dbhome_1 is NOT linked
with Unified Auditing.
Do you want to continue without Unified Auditing Y/N [Y]: N
To relink with Unified Auditing please run:
-------------------------------------------
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle
Enabling Unified Auditing impacts any existing database in the same
ORACLE_HOME and you have to manually setup policies & cleanup job!
2019-10-14_16-10-54::dmk_dbcreate.pl::dmk_dbcreate.pl ::INFO ==> 1099 -
User choosed to exit.
oracle@oel74-dmk:/u01/app/oracle/local/dmk_dbcreate/ [DB18]
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
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:
AUDIT_RETENTION_DAYS = 31
This retention is used to setup the audit purge job.
a) For Unified Auditing a purge job is created as follows:
The use of Unified Auditing by DMK dbcreate:
--------------------------------------------
- is default in Oracle version >= 19
- is optional in Oracle version 12.2 and 18
- is not recommended in Oracle version <= 12.1
Currently the ORACLE_HOME=/opt/oracle/product/18c/dbhome_1 is NOT linked
with Unified Auditing.
Do you want to continue without Unified Auditing Y/N [Y]: N
To relink with Unified Auditing please run:
-------------------------------------------
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle
Enabling Unified Auditing impacts any existing database in the same
ORACLE_HOME and you have to manually setup policies & cleanup job!
2019-10-14_16-10-54::dmk_dbcreate.pl::dmk_dbcreate.pl ::INFO ==> 1099 -
User choosed to exit.
oracle@oel74-dmk:/u01/app/oracle/local/dmk_dbcreate/ [DB18]
b) For Legacy Auditing a purge job is created as follows:
SQL> COLUMN priv_user FORMAT a9
SQL> COLUMN interval FORMAT a40
SQL> COLUMN what FORMAT a80
SQL> SET PAGESIZE 65 LINESIZE 80
SQL> SELECT job, priv_user, interval, what FROM dba_jobs
2 WHERE what LIKE '%aud$%';
JOB PRIV_USER INTERVAL
---------- --------- ----------------------------------------
WHAT
-------------------------------------------------------------------
1 SYS trunc(SYSDATE)+7
delete from sys.aud$ where ntimestamp# <= TRUNC(SYSTIMESTAMP - 31);
SQL>
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
oracle@oel74-dmk:/u01/app/oracle/local/dmk_dbcreate/ [DB18] dmk_dbcreate.ksh
-s DB18
2019-10-14_16-17-20::dmk_dbcreate.pl::dmk_dbcreate.pl ::INFO ==> The SID
DB18 does not exist in /etc/oratab.
The use of Unified Auditing by DMK dbcreate:
--------------------------------------------
- is default in Oracle version >= 19
- is optional in Oracle version 12.2 and 18
- is not recommended in Oracle version <= 12.1
Currently the ORACLE_HOME=/opt/oracle/product/18c/dbhome_1 is linked with
Unified Auditing.
Do you want to continue with Unified Auditing Y/N [Y]:
The audit policies ORA_SECURECONFIG, ORA_LOGON_FAILURES will be enabled and
the parameter AUDIT_TRAIL set to NONE.
A purge audit job will be setup with a retention of 31 days (= template
parameter AUDIT_RETENTION_DAYS).
2019-10-14_16-17-22::dmk_dbcreate.pl::dmk_dbcreate.pl ::INFO ==> All
scripts will be created in /opt/oracle/admin/DB18/create with
ORACLE_HOME=/opt/oracle/product/18c/dbhome_1
Do you want to continue=[Y/N]: Y
2019-10-14_16-17-25::dmk_dbcreate.pl::dmk_dbcreate.pl ::INFO ==> Dbca is
running in silent mode, please wait...
[WARNING] [DBT-06801] Specified Fast Recovery Area size (15,360 MB) is less
than the recommended value.
CAUSE: Fast Recovery Area size should at least be three times the
database size (6,336 MB).
ACTION: Specify Fast Recovery Area Size to be at least three times the
database size.
[WARNING] [DBT-06801] Specified Fast Recovery Area size (15,360 MB) is less
than the recommended value.
CAUSE: Fast Recovery Area size should at least be three times the
database size (6,336 MB).
ACTION: Specify Fast Recovery Area Size to be at least three times the
database size.
Prepare for db operation
10% complete
Creating and starting Oracle instance
13% complete
14% complete
20% complete
Creating database files
21% complete
30% complete
Creating data dictionary views
33% complete
39% complete
45% complete
48% complete
51% complete
60% complete
Completing Database Creation
64% complete
67% complete
70% complete
Executing Post Configuration Actions
100% complete
The generation of the script "/opt/oracle/admin/DB18/create" is successful.
Look at the log file "/opt/oracle/cfgtoollogs/dbca/DB18/DB180.log" for
further details.
2019-10-14_16-17-33::dmk_dbcreate.pl::dmk_dbcreate.pl ::INFO ==> Scripts
have been created successfully.
2019-10-14_16-17-33::dmk_dbcreate.pl::dmk_dbcreate.pl ::INFO ==> Appending
dbi best practices into /opt/oracle/admin/DB18/create/DB18.sh and
/opt/oracle/admin/DB18/create/init.ora
2019-10-14_16-17-33::dmk_dbcreate.pl::dmk_dbcreate.pl ::INFO ==>
/opt/oracle/admin/DB18/create/init.ora file altered
2019-10-14_16-17-33::dmk_dbcreate.pl::dmk_dbcreate.pl ::INFO ==>
/opt/oracle/admin/DB18/create/DB18.sh file altered
2019-10-14_16-17-33::dmk_dbcreate.pl::dmk_dbcreate.pl ::INFO ==>
/opt/oracle/admin/DB18/create/postDBCreation.sql file altered
2019-10-14_16-17-33::dmk_dbcreate.pl::dmk_dbcreate.pl ::INFO ==> Would you
like to create the database DB18 now?
Do you want to continue=[Y/N]:
2019-10-14_16-18-42::dmk_dbcreate.pl::dmk_dbcreate.pl ::INFO ==> Creating
database DB18 using script /opt/oracle/admin/DB18/create/DB18.sh. Please
wait...
You should Add this entry in the /etc/oratab:
DB18:/opt/oracle/product/18c/dbhome_1:Y
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Oct 14 16:18:42 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Enter new password for SYS:
Enter new password for SYSTEM:
Enter password for SYS:
Connected to an idle instance.
SQL> spool /opt/oracle/admin/DB18/create/CreateDB.log append
SQL> startup nomount pfile="/opt/oracle/admin/DB18/create/init.ora";
ORACLE instance started.
Total System Global Area 2147480376 bytes
Fixed Size 8659768 bytes
Variable Size 704643072 bytes
Database Buffers 1426063360 bytes
Redo Buffers 8114176 bytes
SQL> CREATE DATABASE "DB18"
2 MAXINSTANCES 8
3 MAXLOGHISTORY 1
4 MAXLOGFILES 16
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 1024
7 DATAFILE '/u02/oradata/DB18/system01DB18.dbf' SIZE 700M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE 2048M
8 EXTENT MANAGEMENT LOCAL
9 SYSAUX DATAFILE '/u02/oradata/DB18/sysaux01DB18.dbf' SIZE 600M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE 2048M
10 SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'/u02/oradata/DB18/temp01DB18.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT
640K MAXSIZE 2048M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
11 SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'/u02/oradata/DB18/undotbs01DB18.dbf' SIZE 2048M REUSE
12 CHARACTER SET AL32UTF8
13 NATIONAL CHARACTER SET AL16UTF16
14 LOGFILE GROUP 1
('/u02/oradata/DB18/redog1m1DB18.dbf','/u02/oradata/DB18/redog1m2DB18.dbf')
SIZE 200M,
15 GROUP 2
('/u02/oradata/DB18/redog2m1DB18.dbf','/u02/oradata/DB18/redog2m2DB18.dbf')
SIZE 200M,
16 GROUP 3
('/u02/oradata/DB18/redog3m1DB18.dbf','/u02/oradata/DB18/redog3m2DB18.dbf')
SIZE 200M,
17 GROUP 4
('/u02/oradata/DB18/redog4m1DB18.dbf','/u02/oradata/DB18/redog4m2DB18.dbf')
SIZE 200M,
18 GROUP 5
('/u02/oradata/DB18/redog5m1DB18.dbf','/u02/oradata/DB18/redog5m2DB18.dbf')
SIZE 200M,
19 GROUP 6
('/u02/oradata/DB18/redog6m1DB18.dbf','/u02/oradata/DB18/redog6m2DB18.dbf')
SIZE 200M
20 USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY
"&&systemPassword";
...