D Creating Templates for Oracle Database

This appendix explains how you can use Database Configuration Assistant (DBCA) to create templates for structuring newly created databases.

Note that DBCA can also be used to create scripts for creating databases using the templates. The necessary options for creating templates and scripts in the DBCA help have been highlighted in this appendix.

This appendix covers the following:

Overview

For detailed information on using DBCA to create and configure a database, see the Oracle Database 2 Day DBA Guide available at:

http://www.oracle.com/technology/documentation/database.html

Creating Database and Templates Using DBCA

You can launch DBCA in interactive mode, provide details for creating a database, and in the end, instead of creating a database, you can save the specified information as a template. You can then use this template to create databases.

This section explains how you can create a database and other templates using DBCA.

Creating a Database using DBCA

Example: Create db glob - use template glob1.dbc

$ORACLE_HOME/bin/dbca -silent -createDatabase -templateName glob1.dbc -gdbname glob glob-sid -characterset AL32UTF8

Example: Create db glob with the location of datafiles - creating a database with the location where datafiles have to be placed in a given location

$ORACLE_HOME/bin/dbca -silent -createDatabase -templateName glob1.dbc -gdbname glob glob-sid-characterset AL32UTF8 -datafiledestination D: \ oracle\product\10.2.0\oradata\glob

Note:

If you want these databases to be monitored by Enterprise Manager Grid Control, then unlock the DBSNMP password of the databases.

Creating .dbt Template File from Existing Databases

template dbt dbca -silent -createTemplateFromDB -sourceDB GLOB -sysDBAUserName sys -sysDBAPassword change_on_install -templateName glob_template

Creating .dbc Template File from Existing Databases

template dbc dbca -silent -createCloneTemplate -sourceDB GLOB -sysDBAUserName sys -sysDBAPassword change_on_install -templateName glob_clone

Creating a Template File and Deleting Databases Using a Template

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

    dbca -silent -createCloneTemplate -sourceDB GLOB -sysDBAUserName sys -sysDBAPassword change_on_install -templateName glob_clone

  • Delete database glob silently or copy glob_clone.dbc and GLOB_CLONE.DFB to other host:

    dbca -silent -deleteDatabase -sourceDB GLOB -sysDBAUserName sys -sysDBAPassword change_on_install

  • Create database using database template clone:

    dbca -silent -createDatabase -templateName glob_clone.dbc -gdbname glob glob-sid-characterset AL32UTF8

Creating Sample Script (template) and Databases

  • Create db scripts in the directory D: \ oracle \ product \ 10.2.0 \ db_1 \ assistants \ dbca \ templates \ scr

    dbca -silent -generateScripts -templateName glob_clone.dbc -gdbName glob -scriptDest D: \ oracle \ product \ 10.2.0 \ db_1 \ assistants \ dbca \ templates \ scr

  • Create database using scripts

    set ORACLE_SID = GLOB

    glob.bat

DBCA Command Line Help Options

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

Creating a Database

  • - createDatabase

  • - templateName <name of an existing template>

  • [-cloneTemplate]

  • - gdbName <global database name>

  • [- Sid <database system identifier>]

  • [-sysPassword <SYS user password>]

  • [-systemPassword <SYSTEM user password>]

  • [-emConfiguration <CENTRAL|LOCAL|ALL|NOBACKUP|NOEMAIL|NONE>

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

  • -dbsnmpPassword <DBSNMP user password> - dbsnmpPassword <DBSNMP user password>

  • -sysmanPassword <SYSMAN user password> - sysmanPassword <SYSMAN user password>

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

  • -hostUserPassword <Host user password for EM backup job> - hostUserPassword <Host user password for backup EM job>

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

  • [-smtpServer <Outgoing mail (SMTP) server for email notifications> [smtpServer <Outgoing-mail server for (SMTP) email notifications>

  • -emailAddress <Email address for email notifications>] - emailAddress <Email email address for notifications>]

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

  • [-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.>] [-datafileDestination <destination directory database for all 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.>]

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

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

  • [-storageType < FS | ASM | RAW> [-storageType <FS | ASM | RAW>

  • [-asmSysPassword <SYS password for ASM instance>] [-asmSysPassword <SYS password for ASM instance>]

  • [-diskString <disk discovery path to be used by ASM>] [-diskString <disk discovery path to be used by ASM>]

  • [-diskList <comma seperated list of disks for the database area disk group> [diskList <comma-seperated list of disks for the database area disc group>

  • -diskGroupName <database area disk group name> diskGroupName <database-area disk group name>

  • -redundancy <HIGH|NORMAL|EXTERNAL>] - redundancy <HIGH|NORMAL|EXTERNAL>]

  • [-recoveryDiskList <comma seperated list of disks for the recovery area disk group> [recoveryDiskList <comma-seperated list of disks for the recovery area disc group>

  • -recoveryGroupName <recovery area disk group name> recoveryGroupName <recovery-area disk group name>

  • -recoveryGroupRedundancy <HIGH|NORMAL|EXTERNAL>]] - recoveryGroupRedundancy <HIGH|NORMAL|EXTERNAL>]]

  • [-characterSet <character set for the database>] [-characterSet <character set for the database>]

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

  • [-registerWithDirService <true | false> [-registerWithDirService <true | false>

  • -dirServiceUserName <user name for directory service> - dirServiceUserName <user name for directory service>

  • -dirServicePassword <password for directory service > - dirServicePassword <password for directory service>

  • -walletPassword <password for database wallet >] - walletPassword <password wallet database for more]

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

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

  • [- variables < comma seperated list of name=value pairs>] [- Variables <comma seperated list of name = value pairs>]

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

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

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

Configuring a Database

  • - configureDatabase

  • -sourceDB <source database sid > - sourceDB <source database sid>

  • [-sysDBAUserName <user name with SYSDBA privileges> [-sysDBAUserName <user name with SYSDBA privileges>

  • -sysDBAPassword <password for sysDBAUserName user name>] - sysDBAPassword <password for sysDBAUserName user name>]

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

  • -dirServiceUserName <user name for directory service> - dirServiceUserName <user name for directory service>

  • -dirServicePassword <password for directory service > - dirServicePassword <password for directory service>

  • -walletPassword <password for database wallet >] - walletPassword <password wallet database for more]

  • [-emConfiguration <CENTRAL|LOCAL|ALL|NOBACKUP|NOEMAIL|NONE> [-emConfiguration <CENTRAL|LOCAL|ALL|NOBACKUP|NOEMAIL|NONE>

  • -dbsnmpPassword <DBSNMP user password> - dbsnmpPassword <DBSNMP user password>

  • -symanPassword <SYSMAN user password> - symanPassword <SYSMAN user password>

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

  • -hostUserPassword <Host user password for EM backup job> - hostUserPassword <Host user password for backup EM job>

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

  • [-smtpServer <Outgoing mail (SMTP) server for email notifications> [smtpServer <Outgoing-mail server for (SMTP) email notifications>

  • -emailAddress <Email address for email notifications>] - emailAddress <Email email address for notifications>]

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

Creating a Template from an Existing Database

  • - createTemplateFromDB

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

  • -templateName <new template name> - templateName <new template name>

  • -sysDBAUserName <user name with SYSDBA privileges> - sysDBAUserName <user name with SYSDBA privileges>

  • -sysDBAPassword <password for sysDBAUserName user name> - sysDBAPassword <password for sysDBAUserName user name>

  • [-maintainFileLocations <true | false>] [-maintainFileLocations <true | false>]

Creating a Clone Template from an Existing Database

  • - createCloneTemplate

  • -sourceSID <source database sid > - sourceSID <source database sid>

  • -templateName <new template name> - templateName <new template name>

  • [-sysDBAUserName <user name with SYSDBA privileges> [-sysDBAUserName <user name with SYSDBA privileges>

  • -sysDBAPassword <password for sysDBAUserName user name>] - sysDBAPassword <password for sysDBAUserName user name>]

  • [-maintainFileLocations <true | false>] [-maintainFileLocations <true | false>]

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

Generate Scripts to Create Database

  • - generateScripts

  • -templateName <name of an existing template > - templateName <name of an existing template>

  • -gdbName <global database name> - gdbName <global database name>

  • [-scriptDest <destination for all the scriptfiles>] [-scriptDest <destination for all the scriptfiles>]

Deleting a Database

  • - deleteDatabase

  • -sourceDB <source database sid > - sourceDB <source database sid>

  • [-sysDBAUserName <user name with SYSDBA privileges> [-sysDBAUserName <user name with SYSDBA privileges>

  • -sysDBAPassword <password for sysDBAUserName user name>] - sysDBAPassword <password for sysDBAUserName user name>]

Configure ASM Disk Groups

  • - configureASM

  • [-asmSysPassword <SYS password for ASM instance>] [-asmSysPassword <SYS password for ASM instance>]

  • [-diskString <disk discovery path to be used by ASM>] [-diskString <disk discovery path to be used by ASM>]

  • [-diskList <comma seperated list of disks for the database area disk group> [diskList <comma-seperated list of disks for the database area disc group>

  • -diskGroupName <database area disk group name> diskGroupName <database-area disk group name>

  • -redundancy <HIGH|NORMAL|EXTERNAL>]] - redundancy <HIGH|NORMAL|EXTERNAL>]]

  • [-recoveryDiskList <comma seperated list of disks for the database area disk group> [recoveryDiskList <comma-seperated list of disks for the database area disc group>

  • -recoveryGroupName <database area disk group name> recoveryGroupName <database-area disk group name>

  • -recoveryGroupRedundancy <HIGH|NORMAL|EXTERNAL>] - recoveryGroupRedundancy <HIGH|NORMAL|EXTERNAL>]

  • [-emConfiguration <CENTRAL|NONE> [-emConfiguration <CENTRAL|NONE>

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

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