ubuntu 에 설치를 하니까
1. 제품 설치 후 아래와 같은 메세지가 뜬다면 Password: psql.bin (10.1.5) Type "help" for help. Cannot read termcap database; using dumb terminal settings. 2. 환경변수에 readline 라이브러리를 미리 로드 하게 설정 export LD_PRELOAD=/usr/lib/x86_64-linux-gnu/libreadline.so 3. 해당 readline 라이브러리 추가 yum install readline-devel apt install libreadline-dev
/*
Test DB : Oracle Enterprise 12.2.0.1 Single
*/
-- 01. test table 생성
CREATE TABLE "SCOTT"."MYTEST"
(
"ID" VARCHAR2(50) NOT NULL ENABLE,
"UPDATED" DATE NOT NULL ENABLE,
"MYNAME" VARCHAR2(100) NOT NULL ENABLE,
PRIMARY KEY ("ID", "UPDATED")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE
STATISTICS
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
-- 02. 통계정보 설정
BEGIN
dbms_stats.set_column_stats ('SCOTT', 'MYTEST', 'ID', distcnt => 8 );
dbms_stats.set_column_stats ('SCOTT', 'MYTEST','UPDATED', distcnt => 300000);
dbms_stats.set_table_stats ('SCOTT', 'MYTEST', NUMROWS => 2400000);
dbms_stats.set_index_stats ('SCOTT', 'SYS_C0011249', NUMROWS => 2400000);
END;
-- 03. 원본 쿼리
SELECT *
FROM mytest T1
WHERE T1.updated = (SELECT max(updated)
FROM mytest
WHERE id = T1.id);
Plan hash value: 4215771275
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 992 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 8 | 992 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 8 | 992 | 2 (0)| 00:00:01 |
| 3 | VIEW | VW_SQ_1 | 8 | 288 | 1 (0)| 00:00:01 |
| 4 | HASH GROUP BY | | 8 | 288 | 1 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | SYS_C0011249 | 2400K| 82M| 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SYS_C0011249 | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| MYTEST | 1 | 88 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("ITEM_1"="T1"."ID" AND "T1"."UPDATED"="MAX(UPDATED)")
-- 04. 쿼리 변형
select *
from mytest aa,
(select /*+ index_ffs(bb) */
id, max(updated) as updated
from mytest bb
group by ID
) cc
where aa.id = cc.id
and aa.UPDATED = cc.updated
;
Plan hash value: 198263429
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 992 | 69 (96)| 00:00:01 |
| 1 | NESTED LOOPS | | 8 | 992 | 69 (96)| 00:00:01 |
| 2 | NESTED LOOPS | | 8 | 992 | 69 (96)| 00:00:01 |
| 3 | VIEW | | 8 | 288 | 68 (98)| 00:00:01 |
| 4 | HASH GROUP BY | | 8 | 288 | 68 (98)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN | SYS_C0011249 | 2400K| 82M| 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SYS_C0011249 | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| MYTEST | 1 | 88 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("AA"."ID"="CC"."ID" AND "AA"."UPDATED"="CC"."UPDATED")
select
*
from mytest aa,
(select /*+ parallel_index(bb, 2) index_ffs(bb) */
id, max(updated) as updated
from mytest bb
group by ID
) cc
where aa.id = cc.id
and aa.UPDATED = cc.updated
;
Plan hash value: 1395081935
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 992 | 38 (93)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 8 | 992 | 38 (93)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | 8 | 992 | 38 (93)| 00:00:01 | Q1,01 | PCWP | |
| 4 | NESTED LOOPS | | 8 | 992 | 38 (93)| 00:00:01 | Q1,01 | PCWP | |
| 5 | VIEW | | 8 | 288 | 73 (98)| 00:00:01 | Q1,01 | PCWP | |
| 6 | HASH GROUP BY | | 8 | 288 | 73 (98)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 8 | 288 | 73 (98)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 8 | 288 | 73 (98)| 00:00:01 | Q1,00 | P->P | HASH |
| 9 | HASH GROUP BY | | 8 | 288 | 73 (98)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 2400K| 82M| 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 11 | INDEX FAST FULL SCAN| SYS_C0011249 | 2400K| 82M| 2 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 12 | INDEX UNIQUE SCAN | SYS_C0011249 | 1 | | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
| 13 | TABLE ACCESS BY INDEX ROWID| MYTEST | 1 | 88 | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
12 - access("AA"."ID"="CC"."ID" AND "AA"."UPDATED"="CC"."UPDATED")
--
-- Table 에 MYNAME 칼럼이 없다면
--
SELECT /*+ index_ffs(T1, SYS_C0011249) */
*
FROM mytest T1
WHERE T1.UPDATED = (
select
UPDATED
from
(SELECT
/*+ index_desc(mytest, SYS_C0011249) */
updated
FROM mytest
WHERE id = T1.id
ORDER BY updated DESC
)
where rownum <= 1
);
Plan hash value: 2051182498
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 3 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | INDEX FAST FULL SCAN | SYS_C0011249 | 1 | 36 | 2 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 1 | 9 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN DESCENDING| SYS_C0011249 | 1 | 36 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"."UPDATED"= (SELECT "UPDATED" FROM (SELECT /*+ INDEX_DESC ("MYTEST"
"SYS_C0011249") */ "UPDATED" "UPDATED" FROM "MYTEST" "MYTEST" WHERE "ID"=:B1 ORDER BY
"UPDATED" DESC) "from$_subquery$_002" WHERE ROWNUM<=1))
3 - filter(ROWNUM<=1)
5 - access("ID"=:B1)
select t1.*
FROM
(
SELECT
aa.id,
aa.updated
FROM
(
SELECT
t1.id,
t1.updated,
rank() over (partition BY id ORDER BY updated DESC) AS rnum
FROM mytest t1
) aa
WHERE rnum <= 1
) bb, mytest t1
where t1.id = bb.id
and t1.updated = bb.updated
;
| 도서] PostgreSQL 9.6 성능 이야기 |
| 저자 김시연,최두원 | 출판사 시연아카데미 |
#!env python3
import mysql.connector
from mysql.connector import errorcode
config = {
'user': 'dbmgr',
'password': 'audtlr2',
'host': 'localhost',
'database': 'spp',
'raise_on_warnings': True,
'use_pure': True,
}
try:
cnx = mysql.connector.connect(**config)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database dose not exists")
else:
print(err)
else:
cnx.close()
--
--
--
SELECT DISTINCT
placement_id
, zone_id
FROM ox_placement_zone_assoc assoc
WHERE EXISTS(SELECT 1
FROM ox_campaigns m INNER JOIN ox_clients c
ON m.clientid = c.clientid AND c.block_status = 'pass'
WHERE
m.status = 0 AND m.block_status = 'pass' AND m.deliverable <= 1 AND assoc.placement_id = m.campaignid)
;
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
| 1 | PRIMARY | c | ALL | PRIMARY | NULL | NULL | NULL | 6728 | Using where; Using temporary |
| 1 | PRIMARY | m | ref | PRIMARY,ox_campaigns_clientid | ox_campaigns_clientid | 3 | OPENX_V1_ADMIN.c.clientid | 3 | Using where |
| 1 | PRIMARY | assoc | ref | ox_placement_zone_assoc_placement_id | ox_placement_zone_assoc_placement_id | 4 | OPENX_V1_ADMIN.m.campaignid | 78 | Using index |
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
+------------------------------+------------+
| Status | Duration |
+------------------------------+------------+
| starting | 0.000070 |
| checking permissions | 0.000014 |
| checking permissions | 0.000012 |
| checking permissions | 0.000012 |
| Opening tables | 0.000034 |
| After opening tables | 0.000016 |
| System lock | 0.000014 |
| Table lock | 0.000017 |
| init | 0.000037 |
| optimizing | 0.000043 |
| statistics | 0.000040 |
| preparing | 0.000032 |
| executing | 0.000012 |
| Creating tmp table | 0.000037 |
| Copying to tmp table | 1.089634 |
| converting HEAP to Aria | 0.121867 |
| Creating index | 0.000091 |
| Repair by sorting | 0.912552 |
| Saving state | 0.000058 |
| Creating index | 0.000014 |
| converting HEAP to Aria | 0.006953 |
| Copying to tmp table on disk | 145.988661 | -- 메모리의 임시 테이블을 디스크로 내리면서 걸리는 시간 이 대부분
| Sending data | 1.860387 |
| end | 0.000047 |
| removing tmp table | 0.062840 |
| end | 0.000021 |
| query end | 0.000021 |
| closing tables | 0.000015 |
| Unlocking tables | 0.000031 |
| freeing items | 0.000028 |
| updating status | 0.000026 |
| cleaning up | 0.000041 |
+------------------------------+------------+
-- 현재 인스턴스의 메모리 사용 부분
# CACHES AND LIMITS #
tmp_table_size = 64M
max_heap_table_size = 64M
# 수정
tmp_table_size = 512M
max_heap_table_size = 512M
####################
개발
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
| 1 | PRIMARY | c | ALL | PRIMARY | NULL | NULL | NULL | 4668 | Using where; Using temporary |
| 1 | PRIMARY | m | ref | PRIMARY,ox_campaigns_clientid | ox_campaigns_clientid | 3 | OPENX_V1_ADMIN.c.clientid | 3 | Using where |
| 1 | PRIMARY | assoc | ref | ox_placement_zone_assoc_placement_id | ox_placement_zone_assoc_placement_id | 4 | OPENX_V1_ADMIN.m.campaignid | 76 | Using index |
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
| 1 | PRIMARY | m | ref | PRIMARY,ox_campaigns_clientid,ox_campaigns_status | ox_campaigns_status | 6 | const,const | 2728 | Using index condition; Using where; Using temporary |
| 1 | PRIMARY | c | eq_ref | PRIMARY | PRIMARY | 3 | OPENX_V1_ADMIN.m.clientid | 1 | Using where |
| 1 | PRIMARY | assoc | ref | ox_placement_zone_assoc_placement_id | ox_placement_zone_assoc_placement_id | 4 | OPENX_V1_ADMIN.m.campaignid | 76 | Using index |
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
##################
delivery2@localhost:OPENX_V1_ADMIN 12:21:04>select status, block_status, count(*)
-> from ox_campaigns
-> group by 1, 2
->
-> ;
+--------+--------------+----------+
| status | block_status | count(*) |
+--------+--------------+----------+
| 0 | | 166 |
| 0 | pass | 5400 | -- 정의역
| 0 | block | 12060 |
| 2 | | 1 |
| 2 | pass | 36 |
| 2 | block | 20 |
| 3 | | 112 |
| 3 | pass | 20078 |
| 3 | block | 18450 |
| 4 | pass | 6 |
+--------+--------------+----------+
-- ox_campaigns index 구성
+--------------+------------+--------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ox_campaigns | 0 | PRIMARY | 1 | campaignid | A | 50058 | NULL | NULL | | BTREE | | |
| ox_campaigns | 1 | ox_campaigns_clientid | 1 | clientid | A | 16686 | NULL | NULL | | BTREE | | |
| ox_campaigns | 1 | ox_campaigns_campaign_group_id | 1 | campaign_group_id | A | 32 | NULL | NULL | YES | BTREE | | |
+--------------+------------+--------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
-- 인덱스 생성
-- 그러나 status 값이 변동이 빈번 할 수 있음
-- (2차)
alter table ox_campaigns
add key if not exists ox_campaigns_status (status, block_status) using BTREE ;
--
-- 개발 플랜
--
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
| 1 | PRIMARY | m | ref | PRIMARY,ox_campaigns_clientid,ox_campaigns_status | ox_campaigns_status | 6 | const,const | 2728 | Using index condition; Using where; Using temporary |
| 1 | PRIMARY | c | eq_ref | PRIMARY | PRIMARY | 3 | OPENX_V1_ADMIN.m.clientid | 1 | Using where |
| 1 | PRIMARY | assoc | ref | ox_placement_zone_assoc_placement_id | ox_placement_zone_assoc_placement_id | 4 | OPENX_V1_ADMIN.m.campaignid | 76 | Using index |
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
--
-- 최종안
--
set global tmp_table_size = 512 * 1024 * 1024;
set global max_heap_table_size = 512 * 1024 * 1024;
Copying to tmp table on disk 사라짐
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000079 |
| checking permissions | 0.000015 |
| checking permissions | 0.000014 |
| checking permissions | 0.000015 |
| Opening tables | 0.000036 |
| After opening tables | 0.000016 |
| System lock | 0.000016 |
| Table lock | 0.000018 |
| init | 0.000040 |
| optimizing | 0.000045 |
| statistics | 0.000043 |
| preparing | 0.000036 |
| executing | 0.000015 |
| Creating tmp table | 0.000038 |
| Copying to tmp table | 5.617840 |
| Sending data | 1.745527 |
| end | 0.000019 |
| removing tmp table | 0.024801 |
| end | 0.000015 |
| query end | 0.000015 |
| closing tables | 0.000013 |
| Unlocking tables | 0.000018 |
| freeing items | 0.000020 |
| updating status | 0.000020 |
| cleaning up | 0.000019 |
+----------------------+----------+
./runInstaller -silent -ignoreSysPrereqs -responseFile /tmp/oui12102.rsp
####################################################################
## Copyright(c) Oracle Corporation 1998,2014. All rights reserved.##
## ##
## Specify values for the variables listed below to customize ##
## your installation. ##
## ##
## Each variable is associated with a comment. The comment ##
## can help to populate the variables with the appropriate ##
## values. ##
## ##
## IMPORTANT NOTE: This file contains plain text passwords and ##
## should be secured to have read permission only by oracle user ##
## or db administrator who owns this installation. ##
## ##
####################################################################
#-------------------------------------------------------------------------------
# Do not change the following system generated value.
#-------------------------------------------------------------------------------
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.1.0
#-------------------------------------------------------------------------------
# Specify the installation option.
# It can be one of the following:
# - INSTALL_DB_SWONLY
# - INSTALL_DB_AND_CONFIG
# - UPGRADE_DB
#-------------------------------------------------------------------------------
oracle.install.option=INSTALL_DB_SWONLY
#-------------------------------------------------------------------------------
# Specify the hostname of the system as set during the install. It can be used
# to force the installation to use an alternative hostname rather than using the
# first hostname found on the system. (e.g., for systems with multiple hostnames
# and network interfaces)
#-------------------------------------------------------------------------------
ORACLE_HOSTNAME=obtest1
#-------------------------------------------------------------------------------
# Specify the Unix group to be set for the inventory directory.
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME=oinstall
#-------------------------------------------------------------------------------
# Specify the location which holds the inventory files.
# This is an optional parameter if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION=/u01/app/oraInventory
#-------------------------------------------------------------------------------
# Specify the languages in which the components will be installed.
#
# en : English ja : Japanese
# fr : French ko : Korean
# ar : Arabic es : Latin American Spanish
# bn : Bengali lv : Latvian
# pt_BR: Brazilian Portuguese lt : Lithuanian
# bg : Bulgarian ms : Malay
# fr_CA: Canadian French es_MX: Mexican Spanish
# ca : Catalan no : Norwegian
# hr : Croatian pl : Polish
# cs : Czech pt : Portuguese
# da : Danish ro : Romanian
# nl : Dutch ru : Russian
# ar_EG: Egyptian zh_CN: Simplified Chinese
# en_GB: English (Great Britain) sk : Slovak
# et : Estonian sl : Slovenian
# fi : Finnish es_ES: Spanish
# de : German sv : Swedish
# el : Greek th : Thai
# iw : Hebrew zh_TW: Traditional Chinese
# hu : Hungarian tr : Turkish
# is : Icelandic uk : Ukrainian
# in : Indonesian vi : Vietnamese
# it : Italian
#
# all_langs : All languages
#
# Specify value as the following to select any of the languages.
# Example : SELECTED_LANGUAGES=en,fr,ja
#
# Specify value as the following to select all the languages.
# Example : SELECTED_LANGUAGES=all_langs
#-------------------------------------------------------------------------------
SELECTED_LANGUAGES=en,en_GB
#-------------------------------------------------------------------------------
# Specify the complete path of the Oracle Home.
#-------------------------------------------------------------------------------
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
#-------------------------------------------------------------------------------
# Specify the complete path of the Oracle Base.
#-------------------------------------------------------------------------------
ORACLE_BASE=/u01/app/oracle
#-------------------------------------------------------------------------------
# Specify the installation edition of the component.
#
# The value should contain only one of these choices.
# - EE : Enterprise Edition
#-------------------------------------------------------------------------------
oracle.install.db.InstallEdition=EE
###############################################################################
# #
# PRIVILEGED OPERATING SYSTEM GROUPS #
# ------------------------------------------ #
# Provide values for the OS groups to which OSDBA and OSOPER privileges #
# needs to be granted. If the install is being performed as a member of the #
# group "dba", then that will be used unless specified otherwise below. #
# #
# The value to be specified for OSDBA and OSOPER group is only for UNIX based #
# Operating System. #
# #
###############################################################################
#------------------------------------------------------------------------------
# The DBA_GROUP is the OS group which is to be granted OSDBA privileges.
#-------------------------------------------------------------------------------
oracle.install.db.DBA_GROUP=dba
#------------------------------------------------------------------------------
# The OPER_GROUP is the OS group which is to be granted OSOPER privileges.
# The value to be specified for OSOPER group is optional.
#------------------------------------------------------------------------------
oracle.install.db.OPER_GROUP=
#------------------------------------------------------------------------------
# The BACKUPDBA_GROUP is the OS group which is to be granted OSBACKUPDBA privileges.
#------------------------------------------------------------------------------
oracle.install.db.BACKUPDBA_GROUP=dba
#------------------------------------------------------------------------------
# The DGDBA_GROUP is the OS group which is to be granted OSDGDBA privileges.
#------------------------------------------------------------------------------
oracle.install.db.DGDBA_GROUP=dba
#------------------------------------------------------------------------------
# The KMDBA_GROUP is the OS group which is to be granted OSKMDBA privileges.
#------------------------------------------------------------------------------
oracle.install.db.KMDBA_GROUP=dba
###############################################################################
# #
# Grid Options #
# #
###############################################################################
#------------------------------------------------------------------------------
# Specify the type of Real Application Cluster Database
#
# - ADMIN_MANAGED: Admin-Managed
# - POLICY_MANAGED: Policy-Managed
#
# If left unspecified, default will be ADMIN_MANAGED
#------------------------------------------------------------------------------
oracle.install.db.rac.configurationType=
#------------------------------------------------------------------------------
# Value is required only if RAC database type is ADMIN_MANAGED
#
# Specify the cluster node names selected during the installation.
# Leaving it blank will result in install on local server only (Single Instance)
#
# Example : oracle.install.db.CLUSTER_NODES=node1,node2
#------------------------------------------------------------------------------
oracle.install.db.CLUSTER_NODES=
#------------------------------------------------------------------------------
# This variable is used to enable or disable RAC One Node install.
#
# - true : Value of RAC One Node service name is used.
# - false : Value of RAC One Node service name is not used.
#
# If left blank, it will be assumed to be false.
#------------------------------------------------------------------------------
oracle.install.db.isRACOneInstall=false
#------------------------------------------------------------------------------
# Value is required only if oracle.install.db.isRACOneInstall is true.
#
# Specify the name for RAC One Node Service
#------------------------------------------------------------------------------
oracle.install.db.racOneServiceName=
#------------------------------------------------------------------------------
# Value is required only if RAC database type is POLICY_MANAGED
#
# Specify a name for the new Server pool that will be configured
# Example : oracle.install.db.rac.serverpoolName=pool1
#------------------------------------------------------------------------------
oracle.install.db.rac.serverpoolName=
#------------------------------------------------------------------------------
# Value is required only if RAC database type is POLICY_MANAGED
#
# Specify a number as cardinality for the new Server pool that will be configured
# Example : oracle.install.db.rac.serverpoolCardinality=2
#------------------------------------------------------------------------------
oracle.install.db.rac.serverpoolCardinality=0
###############################################################################
# #
# Database Configuration Options #
# #
###############################################################################
#-------------------------------------------------------------------------------
# Specify the type of database to create.
# It can be one of the following:
# - GENERAL_PURPOSE
# - DATA_WAREHOUSE
# GENERAL_PURPOSE: A starter database designed for general purpose use or transaction-heavy applications.
# DATA_WAREHOUSE : A starter database optimized for data warehousing applications.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
#-------------------------------------------------------------------------------
# Specify the Starter Database Global Database Name.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.globalDBName=
#-------------------------------------------------------------------------------
# Specify the Starter Database SID.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.SID=
#-------------------------------------------------------------------------------
# Specify whether the database should be configured as a Container database.
# The value can be either "true" or "false". If left blank it will be assumed
# to be "false".
#-------------------------------------------------------------------------------
oracle.install.db.ConfigureAsContainerDB=false
#-------------------------------------------------------------------------------
# Specify the Pluggable Database name for the pluggable database in Container Database.
#-------------------------------------------------------------------------------
oracle.install.db.config.PDBName=
#-------------------------------------------------------------------------------
# Specify the Starter Database character set.
#
# One of the following
# AL32UTF8, WE8ISO8859P15, WE8MSWIN1252, EE8ISO8859P2,
# EE8MSWIN1250, NE8ISO8859P10, NEE8ISO8859P4, BLT8MSWIN1257,
# BLT8ISO8859P13, CL8ISO8859P5, CL8MSWIN1251, AR8ISO8859P6,
# AR8MSWIN1256, EL8ISO8859P7, EL8MSWIN1253, IW8ISO8859P8,
# IW8MSWIN1255, JA16EUC, JA16EUCTILDE, JA16SJIS, JA16SJISTILDE,
# KO16MSWIN949, ZHS16GBK, TH8TISASCII, ZHT32EUC, ZHT16MSWIN950,
# ZHT16HKSCS, WE8ISO8859P9, TR8MSWIN1254, VN8MSWIN1258
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.characterSet=
#------------------------------------------------------------------------------
# This variable should be set to true if Automatic Memory Management
# in Database is desired.
# If Automatic Memory Management is not desired, and memory allocation
# is to be done manually, then set it to false.
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.memoryOption=false
#-------------------------------------------------------------------------------
# Specify the total memory allocation for the database. Value(in MB) should be
# at least 256 MB, and should not exceed the total physical memory available
# on the system.
# Example: oracle.install.db.config.starterdb.memoryLimit=512
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.memoryLimit=
#-------------------------------------------------------------------------------
# This variable controls whether to load Example Schemas onto
# the starter database or not.
# The value can be either "true" or "false". If left blank it will be assumed
# to be "false".
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.installExampleSchemas=false
###############################################################################
# #
# Passwords can be supplied for the following four schemas in the #
# starter database: #
# SYS #
# SYSTEM #
# DBSNMP (used by Enterprise Manager) #
# #
# Same password can be used for all accounts (not recommended) #
# or different passwords for each account can be provided (recommended) #
# #
###############################################################################
#------------------------------------------------------------------------------
# This variable holds the password that is to be used for all schemas in the
# starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.ALL=
#-------------------------------------------------------------------------------
# Specify the SYS password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYS=
#-------------------------------------------------------------------------------
# Specify the SYSTEM password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYSTEM=
#-------------------------------------------------------------------------------
# Specify the DBSNMP password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.DBSNMP=
#-------------------------------------------------------------------------------
# Specify the PDBADMIN password required for creation of Pluggable Database in the Container Database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.PDBADMIN=
#-------------------------------------------------------------------------------
# Specify the management option to use for managing the database.
# Options are:
# 1. CLOUD_CONTROL - If you want to manage your database with Enterprise Manager Cloud Control along with Database Express.
# 2. DEFAULT -If you want to manage your database using the default Database Express option.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.managementOption=DEFAULT
#-------------------------------------------------------------------------------
# Specify the OMS host to connect to Cloud Control.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.omsHost=
#-------------------------------------------------------------------------------
# Specify the OMS port to connect to Cloud Control.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.omsPort=0
#-------------------------------------------------------------------------------
# Specify the EM Admin user name to use to connect to Cloud Control.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.emAdminUser=
#-------------------------------------------------------------------------------
# Specify the EM Admin password to use to connect to Cloud Control.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.emAdminPassword=
###############################################################################
# #
# SPECIFY RECOVERY OPTIONS #
# ------------------------------------ #
# Recovery options for the database can be mentioned using the entries below #
# #
###############################################################################
#------------------------------------------------------------------------------
# This variable is to be set to false if database recovery is not required. Else
# this can be set to true.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.enableRecovery=false
#-------------------------------------------------------------------------------
# Specify the type of storage to use for the database.
# It can be one of the following:
# - FILE_SYSTEM_STORAGE
# - ASM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.storageType=
#-------------------------------------------------------------------------------
# Specify the database file location which is a directory for datafiles, control
# files, redo logs.
#
# Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=
#-------------------------------------------------------------------------------
# Specify the recovery location.
#
# Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=
#-------------------------------------------------------------------------------
# Specify the existing ASM disk groups to be used for storage.
#
# Applicable only when oracle.install.db.config.starterdb.storageType=ASM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.asm.diskGroup=
#-------------------------------------------------------------------------------
# Specify the password for ASMSNMP user of the ASM instance.
#
# Applicable only when oracle.install.db.config.starterdb.storage=ASM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.asm.ASMSNMPPassword=
#------------------------------------------------------------------------------
# Specify the My Oracle Support Account Username.
#
# Example : MYORACLESUPPORT_USERNAME=abc@oracle.com
#------------------------------------------------------------------------------
MYORACLESUPPORT_USERNAME=
#------------------------------------------------------------------------------
# Specify the My Oracle Support Account Username password.
#
# Example : MYORACLESUPPORT_PASSWORD=password
#------------------------------------------------------------------------------
MYORACLESUPPORT_PASSWORD=
#------------------------------------------------------------------------------
# Specify whether to enable the user to set the password for
# My Oracle Support credentials. The value can be either true or false.
# If left blank it will be assumed to be false.
#
# Example : SECURITY_UPDATES_VIA_MYORACLESUPPORT=true
#------------------------------------------------------------------------------
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
#------------------------------------------------------------------------------
# Specify whether user doesn't want to configure Security Updates.
# The value for this variable should be true if you don't want to configure
# Security Updates, false otherwise.
#
# The value can be either true or false. If left blank it will be assumed
# to be false.
#
# Example : DECLINE_SECURITY_UPDATES=false
#------------------------------------------------------------------------------
DECLINE_SECURITY_UPDATES=true
#------------------------------------------------------------------------------
# Specify the Proxy server name. Length should be greater than zero.
#
# Example : PROXY_HOST=proxy.domain.com
#------------------------------------------------------------------------------
PROXY_HOST=
#------------------------------------------------------------------------------
# Specify the proxy port number. Should be Numeric and at least 2 chars.
#
# Example : PROXY_PORT=25
#------------------------------------------------------------------------------
PROXY_PORT=
#------------------------------------------------------------------------------
# Specify the proxy user name. Leave PROXY_USER and PROXY_PWD
# blank if your proxy server requires no authentication.
#
# Example : PROXY_USER=username
#------------------------------------------------------------------------------
PROXY_USER=
#------------------------------------------------------------------------------
# Specify the proxy password. Leave PROXY_USER and PROXY_PWD
# blank if your proxy server requires no authentication.
#
# Example : PROXY_PWD=password
#------------------------------------------------------------------------------
PROXY_PWD=
#------------------------------------------------------------------------------
# Specify the Oracle Support Hub URL.
#
# Example : COLLECTOR_SUPPORTHUB_URL=https://orasupporthub.company.com:8080/
#------------------------------------------------------------------------------
COLLECTOR_SUPPORTHUB_URL=
dbca -silent -createDatabase \
-templateName General_Purpose.dbc \
-gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \
-characterSet AL32UTF8 \
-sysPassword OraPasswd1 \
-systemPassword OraPasswd1 \
-createAsContainerDatabase true \
-numberOfPDBs 1 \
-pdbName pdb1 \
-pdbAdminPassword OraPasswd1 \
-databaseType MULTIPURPOSE \
-automaticMemoryManagement false \
-storageType FS \
-ignorePreReqs