pat98's always & forever

- dbca 이용 DB 생성 테스트

silent 모드에서 Template 값이 우선인지, 명시적으로 지정한 값이 우선이지 테스트함.

현재 Template 인 /u01/app/oracle/product/19.0.0.0/dbhome_1/assistants/dbca/templates/OLTP_X2_2_190000.dbt 

의 값은 기본 <characterSet>AL32UTF8</characterSet> 임

명시적으로 -characterset KO16MSWIN949 값으로 지정하고 생성해 봄.

어떤 것으로 생성될까?

[EXA1]oracle@db01:/home/oracle# ORACLE_SID=ORCL;export ORACLE_SID;ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1;export ORACLE_HOME;export SKIP_CVU_CHECK=true;PATH=$PATH:/u01/app/oracle/product/19.0.0.0/dbhome_1/bin:/u01/app/oracle/product/19.0.0.0/dbhome_1;export PATH;/u01/app/oracle/product/19.0.0.0/dbhome_1/bin/dbca -createDatabase -silent  -gdbName ORCL -sid ORCL -sysPassword XXXXX -systemPassword XXXXX -databaseType MULTIPURPOSE  -responseFile NO_VALUE  -dvConfiguration false  -olsConfiguration false  -registerWithDirService false  -asmSysPassword XXXXX -asmsnmpPassword XXXXX -storageType ASM  -diskGroupName +DATAC1 -recoveryGroupName +RECOC1 -nodelist db01,db02 -ignorePreReqs  -characterset KO16MSWIN949 -templateName /u01/app/oracle/product/19.0.0.0/dbhome_1/assistants/dbca/templates/OLTP_X2_2_190000.dbt -runCVUChecks false -emConfiguration NONE

[WARNING] [DBT-06801] Specified Fast Recovery Area size (140,000 MB) is less than the recommended value.
   CAUSE: Fast Recovery Area size should at least be three times the database size (91,698 MB).
   ACTION: Specify Fast Recovery Area Size to be at least three times the database size.
[WARNING] [DBT-11153] Character set specified (KO16MSWIN949) is different from that of the character set (AL32UTF8) in the template. Character set mismatch may cause data truncation.
   ACTION: Make sure the character sets uses the same encoding scheme.
[WARNING] [DBT-06801] Specified Fast Recovery Area size (140,000 MB) is less than the recommended value.
   CAUSE: Fast Recovery Area size should at least be three times the database size (99,890 MB).
   ACTION: Specify Fast Recovery Area Size to be at least three times the database size.
Prepare for db operation
8% complete
Creating and starting Oracle instance
10% complete
11% complete
15% complete
Creating database files
16% complete
17% complete
23% complete
Creating data dictionary views
25% complete
30% complete
31% complete
32% complete
37% complete
39% complete
42% complete
46% complete
Creating cluster database views
48% complete
62% complete
Completing Database Creation
65% complete
68% complete
69% complete
Executing Post Configuration Actions
92% complete
Running Custom Scripts
100% complete
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/ORCL.
Database Information:
Global Database Name:ORCL
System Identifier(SID) Prefix:ORCL
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/ORCL/ORCL1.log" for further details.

[EXA1]oracle@db01:/home/oracle# export ORACLE_SID=ORCL1
[ORCL1]oracle@db01:/home/oracle# sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 9 20:10:35 2024
Version 19.25.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0

SQL> set linesize 120 
SQL> col parameter for A30
SQL> col value     for A20
SQL> select parameter,value from  NLS_DATABASE_PARAMETERS where parameter like '%CHARACTERSET';

PARAMETER                      VALUE
------------------------------ --------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_CHARACTERSET               KO16MSWIN949

결과 : 명시적으로 지정한 값으로 생성됨.

- 테스트 후 삭제

[ORCL]oracle@db01:/home/oracle# dbca -silent -deleteDatabase -sourceDB ORCL -sysDBAUserName sys -sysDBAPassword XXXXX
[WARNING] [DBT-19202] The Database Configuration Assistant will delete the Oracle instances and datafiles for your database. All information in the database will be destroyed.
Prepare for db operation
32% complete
Connecting to database
39% complete
42% complete
45% complete
48% complete
52% complete
55% complete
58% complete
65% complete
Updating network configuration files
68% complete
Deleting instances and datafiles
77% complete
87% complete
97% complete
100% complete
Database deletion completed.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/ORCL/ORCL2.log" for further details.

11.2.0.4 dbca 옵션

dbca -help

dbca  [-silent | -progressOnly | -customCreate] {<command> <options> }  | { [<command> [options] ] -responseFile  <response file > } [-continueOnNonFatalErrors <true | false>]

Please refer to the manual for details.

You can enter one of the following command:

Create a database by specifying the following parameters:

        -createDatabase

                -templateName <name of an existing template in default location or the complete template path>

                [-cloneTemplate]

                -gdbName <global database name>

                [-sid <database system identifier>]

                [-sysPassword <SYS user password>]

                [-systemPassword <SYSTEM user password>]

                [-emConfiguration <CENTRAL|LOCAL|ALL|NONE>

                        -dbsnmpPassword <DBSNMP user password>

                        -sysmanPassword <SYSMAN user password>

                        [-hostUserName <Host user name for EM backup job>

                         -hostUserPassword <Host user password for EM backup job>

                         -backupSchedule <Daily backup schedule in the form of hh:mm>]

                        [-centralAgent <Enterprise Manager central agent home>]]

                [-disableSecurityConfiguration <ALL|AUDIT|PASSWORD_PROFILE|NONE>

                [-datafileDestination <destination directory for all database files> |  -datafileNames <a text file containing database objects such as controlfiles, tablespaces, redo log files and spfile to their corresponding raw device file names mappings in name=value format.>]

                [-redoLogFileSize <size of each redo log file in megabytes>]

                [-recoveryAreaDestination <destination directory for all recovery files>]

                [-datafileJarLocation  <location of the data file jar, used only for clone database creation>]

                [-storageType < FS | ASM > 

                        [-asmsnmpPassword     <ASMSNMP password for ASM monitoring>]

                         -diskGroupName   <database area disk group name>

                         -recoveryGroupName       <recovery area disk group name>

                [-characterSet <character set for the database>]

                [-nationalCharacterSet  <national character set for the database>]

                [-registerWithDirService <true | false> 

                        -dirServiceUserName    <user name for directory service>

                        -dirServicePassword    <password for directory service >

                        -walletPassword    <password for database wallet >]

                [-listeners  <list of listeners to configure the database with>]

                [-variablesFile   <file name for the variable-value pair for variables in the template>]]

                [-variables  <comma separated list of name=value pairs>]

                [-initParams <comma separated list of name=value pairs>]

                [-sampleSchema  <true | false> ]

                [-memoryPercentage <percentage of physical memory for Oracle>]

                [-automaticMemoryManagement ]

                [-totalMemory <memory allocated for Oracle in MB>]

                [-databaseType <MULTIPURPOSE|DATA_WAREHOUSING|OLTP>]]

Configure a database by specifying the following parameters:

        -configureDatabase

                -sourceDB    <source database sid>

                [-sysDBAUserName     <user name  with SYSDBA privileges>

                 -sysDBAPassword     <password for sysDBAUserName user name>]

                [-registerWithDirService|-unregisterWithDirService|-regenerateDBPassword <true | false> 

                        -dirServiceUserName    <user name for directory service>

                        -dirServicePassword    <password for directory service >

                        -walletPassword    <password for database wallet >]

                [-disableSecurityConfiguration <ALL|AUDIT|PASSWORD_PROFILE|NONE>

                [-enableSecurityConfiguration <true|false>

                [-emConfiguration <CENTRAL|LOCAL|ALL|NONE>

                        -dbsnmpPassword <DBSNMP user password>

                        -sysmanPassword <SYSMAN user password>

                        [-hostUserName <Host user name for EM backup job>

                         -hostUserPassword <Host user password for EM backup job>

                         -backupSchedule <Daily backup schedule in the form of hh:mm>]

                        [-centralAgent <Enterprise Manager central agent home>]]

Create a template from an existing database by specifying the following parameters:

        -createTemplateFromDB

                -sourceDB    <service in the form of <host>:<port>:<sid>>

                -templateName      <new template name>

                -sysDBAUserName     <user name  with SYSDBA privileges>

                -sysDBAPassword     <password for sysDBAUserName user name>

                [-maintainFileLocations <true | false>]

Create a clone template from an existing database by specifying the following parameters:

        -createCloneTemplate

                -sourceSID    <source database sid>

                -templateName      <new template name>

                [-sysDBAUserName     <user name  with SYSDBA privileges>

                 -sysDBAPassword     <password for sysDBAUserName user name>]

                [-maintainFileLocations <true | false>]

                [-datafileJarLocation       <directory to place the datafiles in a compressed format>]

Generate scripts to create database by specifying the following parameters:

        -generateScripts

                -templateName <name of an existing template in default location or the complete template path>

                -gdbName <global database name>

                [-scriptDest       <destination for all the scriptfiles>]

Delete a database by specifying the following parameters:

        -deleteDatabase

                -sourceDB    <source database sid>

                [-sysDBAUserName     <user name  with SYSDBA privileges>

                 -sysDBAPassword     <password for sysDBAUserName user name>]

Query for help by specifying the following options: -h | -help

dbca -silent -createDatabase help

help

   -createDatabase - Command to Create a database.

        -responseFile | (-gdbName,-templateName)

        -responseFile - <Fully qualified path for a response file>

        -gdbName <Global database name>

        -templateName <Name of an existing template in default location or the complete template path>

        [-characterSet <Character set for the database>]

        [-createAsContainerDatabase <true | false>]

                [-numberOfPDBs <Number of pluggable databases to be created, default is 0>]

                [-pdbAdminPassword <PDB Administrator user Password, required only while creating new PDB>]

                [-pdbName <Pluggable database name>]

                [-pdbOptions <A comma separated list of name:value pairs with database options to enable/disable. For example JSERVER:true,DV:false>]

                [-pdbStorageMAXSizeInMB <value>]

                [-pdbStorageMAXTempSizeInMB <value>]

                [-useLocalUndoForPDBs <true | false>  Specify false to disable local undo tablespace for PDBs.]

        [-createListener <Create a new listener to register your database. Specify in format, LISTENER_NAME:PORT>]

        [-customScripts <A comma separated list of SQL scripts which needs to be run post db creation.The scripts are run in order they are listed>]

        [-databaseConfigType <SINGLE | RAC | RACONENODE>]

                [-RACOneNodeServiceName <Service name for the service to be created for RAC One Node database. This option is mandatory when the databaseConfigType is RACONENODE>]

        [-databaseType <MULTIPURPOSE | DATA_WAREHOUSING | OLTP>]

        [-datafileDestination <Destination directory for all database files>]

        [-datafileJarLocation <Directory to place the datafiles in compressed format>]

        [-dbOptions <A comma separated list of name:value pairs with database options to enable/disable. For example JSERVER:true,DV:false>]

        [-dvConfiguration <true | false> Specify true to configure and enable database vault.]

                -dvUserName <Specify database vault owner user name>

                -dvUserPassword <Specify database vault owner password>

                [-dvAccountManagerName <Specify separate database vault account manager>]

                [-dvAccountManagerPassword <Specify database vault account manager password>]

        [-emConfiguration <DBEXPRESS | CENTRAL | BOTH | NONE>]

                [-dbsnmpPassword <DBSNMP user password>]

                [-emExpressPort <EM database express port number. Generally used during createDatabase>]

                [-emPassword <EM Admin user password>]

                [-emUser <EM Admin username to add or modify targets>]

                [-omsHost <EM management server host name>]

                [-omsPort <EM management server port number>]

        [-enableArchive <true | false> Specify true to enable archive>]

                [-archiveLogDest <Specify archive log destinations separated by comma. If archive log destination is not specified, fast recovery area location will be used for archive log files.>]

                [-archiveLogMode <AUTO|MANUAL , the default is Automatic archiving>]

        [-initParams <Comma separated list of name=value pairs>]

                [-initParamsEscapeChar <Specify escape character for comma when a specific initParam has multiple values.If the escape character is not specified backslash is the default escape character>]

        [-listeners <A comma separated list of listeners that the database can be configured with>]

        [-memoryMgmtType <AUTO|AUTO_SGA|CUSTOM_SGA>]

        [-memoryPercentage | -totalMemory]

        [-memoryPercentage <Percentage of physical memory for oracle database>]

        [-totalMemory <Memory allocated for Oracle in MB>]

        [-nationalCharacterSet <National character set for the database>]

        [-nodelist <Node names separated by comma for the database>]

        [-olsConfiguration <true | false> Specify true to configure and enable Oracle Label Security.]

                [-configureWithOID This flag configures Oracle Label Security with OID.]

        [-oracleHomeUserName <Specify Oracle Home User Name>]

        [-oracleHomeUserPassword <Specify Oracle Home User Password>]

        [-policyManaged | -adminManaged]

        [-policyManaged <Policy managed database, default option is Admin managed database>]

                -serverPoolName <Specify the single server pool name in case of create server pool or comma separated list in case of existing server pools>

                [-createServerPool <Create a new server pool, which will be used by the database>]

                        [-cardinality <Specify the cardinality of the new server pool that is to be created, default is the number of qualified nodes>]

                        [-force <To create server pool by force when adequate free servers are not available. This may affect the database which is already in running mode>]

                        [-pqCardinality <value>]

                        [-pqPoolName <value>]

                [-pqPoolName <value>]

        [-adminManaged <Admin managed database, this is default option>]

        [-recoveryAreaDestination <Destination directory for all recovery files. Specify "NONE" for disabling Fast Recovery Area>]

                [-recoveryAreaSize <Fast Recovery Area Size in MB>]

        [-redoLogFileSize <Size of each redo log file in MB>]

        [-registerWithDirService <true | false>]

                -dirServiceUserName <User name for directory service>

                [-databaseCN <Database common name>]

                [-dirServicePassword <Password for directory service>]

                [-walletPassword <Password for database wallet>]

        [-runCVUChecks <Specify "true" in order to run Cluster Verification Utility checks periodically for RAC databases.>]

        [-sampleSchema <true | false>]

        [-sid <Database system identifier>]

        [-storageType < FS | ASM >]

                -datafileDestination <Destination directory for all database files>

                [-asmsnmpPassword <ASMSNMP password for ASM monitoring>]

        [-sysPassword <SYS user password>]

        [-systemPassword <SYSTEM user password>]

        [-useOMF <true | false> Specify true to use Oracle-Managed Files.]

        [-useWalletForDBCredentials <true | false> Specify true to load database credentials from wallet]

                -dbCredentialsWalletLocation <Path of the directory containing the wallet files>

                [-dbCredentialsWalletPassword <Password to open wallet with auto login disabled>]

        [-variables <Comma separated list of name=value pairs>]

        [-variablesFile <File name of the variable-value pair for variables in the template>]