it 21003 database administration section 02. creating databases creating a database: overview 1....

IT 21003 IT 21003 Database AdministrationDatabase Administration

SECTION 02SECTION 02

CREATING DATABASESCREATING DATABASES

Creating a Database: OverviewCreating a Database: Overview

1.1. Plan the physical design and storage Plan the physical design and storage structuresstructures

2.2. Back Up any existing databasesBack Up any existing databases

3.3. Set the instance indentifierSet the instance indentifier

4.4. Connect to Enterprise Manager or other Connect to Enterprise Manager or other utility and startup an instance utility and startup an instance


Creating a Database: OverviewCreating a Database: Overview

5.5. Create the database using an appropriate Create the database using an appropriate facilityfacility

6.6. Run any required post-installation scriptsRun any required post-installation scripts

7.7. Back Up the new databaseBack Up the new database


Pre-Creation Activities: PlanningPre-Creation Activities: Planning

– Plan the Tables and IndexesPlan the Tables and Indexes

• Estimate SizesEstimate Sizes

– Plan the Backup and Recovery strategy Plan the Backup and Recovery strategy



– Creating a Database can overwrite existing Creating a Database can overwrite existing database filesdatabase files

• In practice a new init.ora file is often In practice a new init.ora file is often created by copying and editing an created by copying and editing an existing oneexisting one

• If the Control_Files parameter is not If the Control_Files parameter is not changed, existing files may be changed, existing files may be overwrittenoverwritten



• The Instance_Name and Database_Name The Instance_Name and Database_Name also need changedalso need changed

• Decide on New Database or Migration Decide on New Database or Migration of old Databaseof old Database


Pre-Creation Activities: Parameter FilesPre-Creation Activities: Parameter Files

– Create and edit the required parameter file(s)Create and edit the required parameter file(s)

• The parameter file configures the instance The parameter file configures the instance that will be used to create the databasethat will be used to create the database

– Rename and edit a supplied init.ora file or Rename and edit a supplied init.ora file or utilize an existing fileutilize an existing file

• Make sure the name is changed, do not Make sure the name is changed, do not use the same parameter file for different use the same parameter file for different databasesdatabases

• initprod.ora for production instanceinitprod.ora for production instance

• initest.ora for test instanceinitest.ora for test instance


Pre-Creation Activities: Parameter Files Cont’dPre-Creation Activities: Parameter Files Cont’d

– Examine the following parameters in your Examine the following parameters in your filefile

• (DB_NAME)(DB_NAME)

• (DB_BLOCK_SIZE)(DB_BLOCK_SIZE)

• (PROCESSES)(PROCESSES)

• (LICENSE_SESSION_WARNING)(LICENSE_SESSION_WARNING)


Pre-Creation Activities: Parameter Files Cont’dPre-Creation Activities: Parameter Files Cont’d

– Examine the following parameters in your Examine the following parameters in your filefile

• (CONTROL_FILES)(CONTROL_FILES)

• (DB_BLOCK_BUFFERS)(DB_BLOCK_BUFFERS)

• (LICENSE_MAX_SESSIONS)(LICENSE_MAX_SESSIONS)

• (LICENSE_MAX_USERS)(LICENSE_MAX_USERS)

– These MUST BE CHANGEDThese MUST BE CHANGED


DB_NAMEDB_NAME

– String of eight or fewer characters used to String of eight or fewer characters used to identify the local name component of the identify the local name component of the database namedatabase name

• To avoid confusion, this should match To avoid confusion, this should match the Oracle instance identifierthe Oracle instance identifier

– DB_NAME is stored in the datafile DB_NAME is stored in the datafile headers, redo log files, and control filesheaders, redo log files, and control files

– This parameter is not easy to change once This parameter is not easy to change once the database has been createdthe database has been created


CONTROL_FILESCONTROL_FILES

– Control_Files can be specified within the Control_Files can be specified within the parameter file as follows:parameter file as follows:

• CONTROL_FILES=/usr/oracle/home/CONTROL_FILES=/usr/oracle/home/control_file,control_file,

/usr/oracle/home/control_file2/usr/oracle/home/control_file2



– At least TWO FILES should be specified so At least TWO FILES should be specified so that the loss of a control file is not criticalthat the loss of a control file is not critical

– The control files should be placed on The control files should be placed on DIFFERENT DISKSDIFFERENT DISKS



– If a name for the control file is not If a name for the control file is not specified, an operating-system-dependent specified, an operating-system-dependent default file is createddefault file is created

– Be careful on Create Database the control Be careful on Create Database the control files are replaced, could cause old databases files are replaced, could cause old databases to not functionto not function


DB_BLOCK_SIZEDB_BLOCK_SIZE

– DB_BLOCK_SIZE specifies the size of a DB_BLOCK_SIZE specifies the size of a database block and therefore the size of the database block and therefore the size of the database buffers in SGAdatabase buffers in SGA



– Default size is operating-system dependentDefault size is operating-system dependent

• Usually set at either 2Kb or 4Kb or 2048 Usually set at either 2Kb or 4Kb or 2048 kb or 4096 kbkb or 4096 kb

• More realistic size is 16Kb or 32KbMore realistic size is 16Kb or 32Kb

• It should be an exact multiple of the OS It should be an exact multiple of the OS block sizeblock size



– The parameter should probably be The parameter should probably be increased, if:increased, if:

• The Oracle host machine is very large, The Oracle host machine is very large, has lots of memory, and lots of fast has lots of memory, and lots of fast storagestorage

• If there are a relatively high number of If there are a relatively high number of large rowslarge rows

• Not many requests for small amounts of Not many requests for small amounts of datadata



– Oracle to 8i – block size cannot be changed Oracle to 8i – block size cannot be changed once set without restructuring the databaseonce set without restructuring the database

– Oracle 9i up – block size can be changedOracle 9i up – block size can be changed


DB_BLOCK_BUFFERSDB_BLOCK_BUFFERS

– DB_BLOCK_BUFFERS is the total DB_BLOCK_BUFFERS is the total number of buffers(database blocks) in the number of buffers(database blocks) in the buffer cache at one timebuffer cache at one time



– Governs the size of the large portion of the Governs the size of the large portion of the SGA and can have profound effect on SGA and can have profound effect on performanceperformance

– Size of each buffer is determined by Size of each buffer is determined by DB_BLOCK_SIZEDB_BLOCK_SIZE



– Estimate the number of buffers by Estimate the number of buffers by determining the number of blocks each determining the number of blocks each application accesses at one time (include application accesses at one time (include data, index, and rollback segment blocks)data, index, and rollback segment blocks)

– High values enable larger cache, which High values enable larger cache, which may reduce I/Omay reduce I/O

• If set too high, the SGA may be larger If set too high, the SGA may be larger than physical memory and lead to than physical memory and lead to swapping, and reduction in system swapping, and reduction in system performanceperformance


The PROCESSES ParameterThe PROCESSES Parameter

– Determines the maximum number of users Determines the maximum number of users who can connect to the instance at any one who can connect to the instance at any one timetime



– To estimate the number required:To estimate the number required:

• One for each Server ProcessOne for each Server Process

• Overhead of One Process required for Overhead of One Process required for each Background Processeach Background Process



– Example:Example:

• If the required maximum number of users If the required maximum number of users connected to an instance at one time is connected to an instance at one time is 100, the setting of PROCESSES may 100, the setting of PROCESSES may need to be 107need to be 107

• PROCESSES = Background + UsersPROCESSES = Background + Users


Listing Additional ParametersListing Additional Parameters

– To obtain a list of parameters and their To obtain a list of parameters and their current values for an instancecurrent values for an instance

• Connect as a DBAConnect as a DBA

• SELECT *SELECT *

• FROM v$parameter;FROM v$parameter;


Setting the Instance Identifier (SID)Setting the Instance Identifier (SID)

– The instance identifier is used by the The instance identifier is used by the operating system to determine the default operating system to determine the default instanced for database connectionsinstanced for database connections

– The value is often the DB_NAME of the The value is often the DB_NAME of the databasedatabase

– Setting the instance identifier is operating-Setting the instance identifier is operating-system specificsystem specific

– Utilize SQL Plus Worksheet or Oracle Utilize SQL Plus Worksheet or Oracle Enterprise ManagerEnterprise Manager


Creating the DatabaseCreating the Database

– Syntax:Syntax:

– CREATE DATABASE [database_name]CREATE DATABASE [database_name]

– [CONTROLFILE REUSE][CONTROLFILE REUSE]

– [LOGFILE {GROUP integer} filespec][LOGFILE {GROUP integer} filespec]

– [MAXLOGFILES integer][MAXLOGFILES integer]


Creating the DatabaseCreating the Database

– Syntax Cont’d:Syntax Cont’d:

– [DATAFILE filespec, filespec][DATAFILE filespec, filespec]

– [MAXDATAFILES integer][MAXDATAFILES integer]

– [MAXINSTANCES integer][MAXINSTANCES integer]

– [ARCHIVELOG | NOARCHIVELOG][ARCHIVELOG | NOARCHIVELOG]

– [CHARACTER SET charset];[CHARACTER SET charset];

– [NATIONAL CHARACTER SET [NATIONAL CHARACTER SET charset];charset];


Creating the Database Example:Creating the Database Example:

CREATE DATABASE db1CREATE DATABASE db1

DATAFILE ‘E:\production\system_dbf’ SIZE 50MDATAFILE ‘E:\production\system_dbf’ SIZE 50M

LOGFILE GROUP 1 (‘E:\production\log01\LOGFILE GROUP 1 (‘E:\production\log01\dev_log1a.rdo’, ‘E:\production\log01\dev_log1a.rdo’, ‘E:\production\log01\dev_log1b.rdo’, ‘E:\production\log01\dev_log1b.rdo’, ‘E:\production\log01\dev_log1c.rdo’) SIZE 1Mdev_log1c.rdo’) SIZE 1M

GROUP 2 (‘E:\production\log02\dev_log2a.rdo’, GROUP 2 (‘E:\production\log02\dev_log2a.rdo’, ‘E:\production\log01\dev_log2b.rdo’, ‘E:\‘E:\production\log01\dev_log2b.rdo’, ‘E:\production\log01\dev_log2c.rdo’) SIZE 1M;production\log01\dev_log2c.rdo’) SIZE 1M;


Creating the Database Example Results:Creating the Database Example Results:

• Creates a database named db1Creates a database named db1

• Creates two online redo log groups with three Creates two online redo log groups with three log files of 1mb eachlog files of 1mb each

• Creates a SYSTEM tablespace with size of Creates a SYSTEM tablespace with size of 50MB50MB

• Remember that other parameters will be Remember that other parameters will be loading default valuesloading default values

• Example: MAXDATAFILES will be set to 32Example: MAXDATAFILES will be set to 32


Database Creation ParametersDatabase Creation Parameters

– database_namedatabase_name

• Name of the database being createdName of the database being created

• Character string of up to eight charactersCharacter string of up to eight characters

• Defaults to DB_NAME in the parameter Defaults to DB_NAME in the parameter file used at startup timefile used at startup time

– Specify this parameter to ensure that Specify this parameter to ensure that the correct parameter file is usedthe correct parameter file is used



– CONTROLFILE REUSE – (NEVER USE CONTROLFILE REUSE – (NEVER USE THIS)THIS)

• Specifies that the control files listed in Specifies that the control files listed in the parameter file are to be overwritten the parameter file are to be overwritten (if they already exist)(if they already exist)



– LOGFILELOGFILE

• Specifies one or more redo log filesSpecifies one or more redo log files

• Each filespec specifies one or more redo Each filespec specifies one or more redo log groups, each of which contain more log groups, each of which contain more than one filethan one file

• If the GROUP clause is omitted, a If the GROUP clause is omitted, a default group is createddefault group is created

• If omitted, two redo log file groups are If omitted, two redo log file groups are created by defaultcreated by default


Database Creation Parameters Cont’dDatabase Creation Parameters Cont’d

– DATAFILEDATAFILE

• One or more files to be used for the One or more files to be used for the SYSTEM tablespaceSYSTEM tablespace

• If omitted, Oracle creates an If omitted, Oracle creates an operating-system-dependent file in the operating-system-dependent file in the default directorydefault directory

• Files can be specified to extend Files can be specified to extend automaticallyautomatically



– ARCHIVELOG | NOARCHIVELOGARCHIVELOG | NOARCHIVELOG

• Specifies whether the database will be Specifies whether the database will be in ARCHIVELOG more or in ARCHIVELOG more or NOARCHIVELOG modeNOARCHIVELOG mode



– CHARACTER SETCHARACTER SET

• Specifies the character set that will be Specifies the character set that will be use for the databaseuse for the database

– NATIONAL CHARACTER SETNATIONAL CHARACTER SET

• Specifies the national character set to Specifies the national character set to be used for the databasebe used for the database

– Defaults to CHARACTER SETDefaults to CHARACTER SET


Database Creation Parameters Cont’d - LimitsDatabase Creation Parameters Cont’d - Limits

– The MAX...parameters limit the size of the The MAX...parameters limit the size of the control filecontrol file

• The control file has to reserve space for The control file has to reserve space for the file detailsthe file details

– MAXLOGFILESMAXLOGFILES

• Integer value specifying the maximum Integer value specifying the maximum number of redo log file groups that the number of redo log file groups that the database will havedatabase will have



– MAXLOGMEMBERSMAXLOGMEMBERS

• Maximum number of log members Maximum number of log members (files) in a group(files) in a group



– MAXDATAFILESMAXDATAFILES

• Integer value specifying the maximum Integer value specifying the maximum number of datafiles that can make up the number of datafiles that can make up the databasedatabase

– Default value is 32, difficult to change Default value is 32, difficult to change after database creation depending on after database creation depending on the Oracle version usedthe Oracle version used

• DB_FILES in the parameter can be used DB_FILES in the parameter can be used to temporarily reduce the parameterto temporarily reduce the parameter


CREATE DATABASE OperationsCREATE DATABASE Operations

– CREATE DATABASE automatically does CREATE DATABASE automatically does the following:the following:

• Creates (or overwrites) the Control FilesCreates (or overwrites) the Control Files

• Creates the Database FilesCreates the Database Files

• Creates the Redo Log FilesCreates the Redo Log Files

• Creates the SYSTEM rollback segment Creates the SYSTEM rollback segment in the SYSTEM tablespacein the SYSTEM tablespace


CREATE DATABASE OperationsCREATE DATABASE Operations

• Creates and loads the Data Dictionary Creates and loads the Data Dictionary TablesTables

– A script called sql.bsq is run to create A script called sql.bsq is run to create the tablesthe tables

– Change this file parameters before Change this file parameters before running CREATE DATABASErunning CREATE DATABASE

• Mounts the DatabaseMounts the Database

• Opens the DatabaseOpens the Database

• Creates the SYS and SYSTEM UsersCreates the SYS and SYSTEM Users


Post Creation ActivitiesPost Creation Activities

– The CREATE DATABASE statement The CREATE DATABASE statement builds the dictionary base tablesbuilds the dictionary base tables

• These tables are extremely difficult to These tables are extremely difficult to readread

• Their structure may change across Their structure may change across releases of Oraclereleases of Oracle


Post Creation ActivitiesPost Creation Activities

– Scripts need to be run to create views on Scripts need to be run to create views on the base tablesthe base tables

• Held in the ORACLE_HOME\rdbms\Held in the ORACLE_HOME\rdbms\admin folderadmin folder

– catalog.sqlcatalog.sql

• Builds some basic views on the Builds some basic views on the dictionary tablesdictionary tables

– catproc.sqlcatproc.sql

• Builds the views for procedural objects Builds the views for procedural objects (packages, procedures, etc)(packages, procedures, etc)


Post Creation Activities Cont’dPost Creation Activities Cont’d

– Change passwords for SYS and SYSTEM usersChange passwords for SYS and SYSTEM users

• Initially, password for SYS is set to Initially, password for SYS is set to change_on_installchange_on_install and SYSTEM password is and SYSTEM password is managermanager

– Create additional tablespaces, rollback Create additional tablespaces, rollback segments, control files, and redo log files and segments, control files, and redo log files and backup the entire databasebackup the entire database

– Create the user accountsCreate the user accounts

– Create and populate the tables and other storage Create and populate the tables and other storage structuresstructures

it 21003 database administration section 02. creating databases creating a database: overview 1....

Documents