Instructor's Notes

(If applicable)

• Lesson 23: Creating a New Database
• This lesson introduces the concepts of creating Oracle 9i databases. In this lesson we will:
• Learn the steps required to create a new database, access the database with an instance, and a new Windows service. Some Unix techniques will also be presented in this lesson.
• Learn how to use the Database Configuration Assistant (DBCA) to create a new database and manage database creation templates.
• In the workshop, stop the service for the Oracle database that may have been created when the Oracle software was installed. Create a new database using the Database Configuration Assistant (DBCA).

Instructor's Notes

(If applicable)

• The major steps in creating a new Oracle database include:
• Backup any databases that exist on the target server. The backup technique should be a cold backup with the Oracle database shutdown in a normal mode. Backup procedures are covered in detail in the lesson User Managed Backup and Recovery and Introduction to RMAN.
• Stop any active instances on the server. Note that this is not absolutely necessary, but I recommend it in case anything goes wrong with the create and you have to reboot the server.
• Use the Database Configuration Assistant to create scripts to create a new database.
• Run the create database scripts
• Backup your new database! The backup technique should be a cold backup with the Oracle database shut down in the normal mode.
• Prepare database for use - Creation of additional tablespaces, multiplexing control files, and multiplexing redo logs may all be required prior to subjecting the database to general use.

Instructor's Notes

(If applicable)

Instructor's Notes

(If applicable)

• The System Identifier, or SID, is a unique identifier for the instance. The SID name must be between 1 and 8 alphanumeric characters and must start with an alphabetic character.
• In a Unix server, set the ORACLE_SID environment variable to the desired SID name and start the new database with NOMOUNT (more on this later) to create the database. Note the the environment variable name, ORACLE_SID, is case-sensitive in Windows and Unix.
• In a Windows server, the system-wide SID is stored in the Windows Registry. Adding or modifying the registry is not often needed. But, it is good to have a general knowledge of the registry keys related to the Oracle instances on your server.
• Use the Windows utility REGEDIT to view the registry. Be very careful – REGEDIT allows updates and corruption in the registry can cause the OS to not start.


• Notes continue on the next page…

Instructor's Notes

(If applicable)

• Locate folder HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0. The number (0) at the end of the folder name will increase by 1 for every additional Oracle Home created on your server. Why create additional Oracle Homes? You could do this to install a new version of the Oracle software on the server.
• Note that you can override the system-wide SID setting by;
• In a DOS window (access via START \ Command Prompt ). Execute the SET command: c:\>
• C> set ORACLE_SID=class
• Or going to START \SETTINGS \CONTROL PANEL\ SYSTEM\ADVANCED\ENVIRONMENT VARIABLES and create an environment variable ORACLE_SID and setting it to the SID desired. Make sure the system wide environment variable is set.

Instructor's Notes

(If applicable)

• In Windows, a service is required to create and run an Oracle instance. The instance runs within the Oracle service. Some of the services that you may find in the Windows Services screen after a database has been started include:
• ORACLESERVICECLASS – This is the actual service that get started whenever the Oracle database is started. The SID is appended to the name of the service. The service is stopped whenever the Oracle database is shutdown and restarted whenever the Oracle database is restarted. The service can also be started manually using the Windows Services panel.
• ORACLE90TNSLISTENER – This is the listener service that listens for Oracle networking requests to the instance. The listener is usually started and stopped at the same time the database is.
• ORACLEMTSRECOVERYSERVICE – Is created whenever the Shared Server option is enabled. Shared Server is also called MTS in earlier versions. Oracle should be consistent in naming!
• ORACLEORAHOME90HTTPSSERVER – Allows web browsers to connect to the Oracle database.
• Creating the service is accomplished with the Oracle Instance Manager (ORADIM.EXE) or commonly referred to as ORADIM. We will see an example of this later.

Instructor's Notes

(If applicable)

• In Windows the OracleServiceSID Service implements the background processes required by Oracle to manage the database. The Service implements the Oracle background processes as threads.
• The threads can be viewed by using the Oracle Administration Assistant for Windows as follows: Start/Programs/Oracle-OraHomexx/Configuration and Migration Tools/Administrative Assistant for Windows NT. Then navigate to your database name and select menu options: Action/Process Information. This will show all of your processes, and the % of CPU that each process is currently consuming.
• In Unix the concept of services are not required and background processes are implemented. The background processes can be seen using the following Unix command;
• $ ps -ef | grep sid

Instructor's Notes

(If applicable)

• I am often asked the question, how do I see what databases are open or running on my server. There are numerous graphical tools (e.g. Oracle Enterprise Manager) that will help answer that question. However, what if such a tool is not available? In a Unix environment, execute the ps command. The SID name is part of the background process name; so you may see, for example, ORADBWR and CLASSDBWR. This would be a strong indication that two instances/databases are running on your server: “ORA” and “CLASS”.
• In an Windows environment, use the Windows Services utility. Services with the prefix “OracleService” are your instances. A status of “Started” indicates the instance is active:

Instructor's Notes

(If applicable)

• The creation of a new database is a resource intensive process. It is for this reason I recommend stopping any other active instances first. Remember, more than one instance and / or database can reside on an Oracle server.
• After shutting down the instance from SQL*Plus, use the STOP feature in the Windows Services utility. Find this by right-clicking on the Oracle service dedicated to the instance. This will fully stop all process and release memory held for the instance.
• Right-clicking and selecting START in Windows Services will start the Windows service and the database. If the registry key ORA_sid_AUTOSTART=TRUE, this includes starting the instance, mounting the database, and opening the database.
• Supplemental Notes
  • If you would like your database to start automatically when Windows starts, select the “Startup” button on the Windows Services utility and change the start parameter to “Automatic”. In most Oracle installations the services are started automatically.

Instructor's Notes

(If applicable)

• The Database Configuration Assistant (DBCA) is a graphical tool that simplifies the task of managing database creation. It supports;
• Database creation
• Database deletion (including the instance and services)
• Database modification (changing from dedicated mode to shared server mode)
• Storage of database creation templates.
• To start the Database Configuration Assistant:
• In Windows:
• Click the START button
• Choose PROGRAMS
• Choose Oracle – OraHome90 (“OraHome90” is the Oracle Home name and can vary slightly)
• Choose Configuration and Migration Tools
• Choose Database Configuration Assistant
• In Unix:
• Type: dbassist
• Supplemental Note In Windows you must have administrator’s privileges in order to create an Oracle database using the DBCA tool.

Instructor's Notes

(If applicable)

• The Database Configuration Assistant provides the following 4 options:
• Create a database
• Configure options in an existing Oracle database including:
• Oracle Spatial – Stores and retrieves multi-dimensional data.
• Oracle Ultra Search – Allows searching of documents and intermedia
• Oracle Label Security – Provides security meeting governmental standards
• Oracle OLAP Services – Provides tools for internet based businesses.
• Example Schemas – Creates various example schemas.
• Oracle JVM – Provides Java execution environment in the Oracle database.
• Oracle Intermedia – Allows storage and retrieval of multi-media applications.
• Dedicated Server Mode / Shared Server Mode
• Delete an existing Oracle database including the instance and datafiles.
• Manage templates allows the following;
• Creating a database creation template from an existing template
• Creating a database creation template from existing Oracle database.
• Deleting a database creation template.
• Supplemental Note The management of templates feature of the DBCA is an excellent tool for new an seasoned database administrator to manage his/her scripts. Seasoned database administrators should always have scripts to recreate any database they manage.

Instructor's Notes

(If applicable)

• The DATABASE TEMPLATES option of step 2 contains the following options;
• Oracle provides four basic templates for the environment your database will support including;
• Data Warehouse
• Transaction Processing (OLTP)
• General Purpose
• New Database
• Your selection will affect the size of the database files, SGA and INIT.ORA parameter settings. Most defaults for the template selected can be overridden, so the choice is not absolutely critical. However, the correct choice will reduce the number of modifications you will need make to sizes and parameter settings as you proceed further in the DBCA to define and create your database.
• For example, choosing transaction processing will create a database with a 4K block size. The choice of a small block size is due to the nature of transaction processing processing: small reads and writes to the database.
• Selecting the New Database option allows full control of database parameters. Also by selecting New Database all scripts can be executed to create the database. This will take longer, however it gives the database administrator greater control over database configuration.
• By clicking SHOW DETAILS button the differences in templates can be seen.

Instructor's Notes

(If applicable)

• The DATABASE IDENTIFICATION option of step 3 includes the following;
• The GLOBAL DATABASE NAME in the first entry prompt (GLOBAL DATABASE NAME) is the fully-qualified database name. The fully qualified database name can be broken down into the following two parts.
• The local database name is the database identifier of the database about to be created. Remember the database consists of the control files, redo log files and the datafiles. CLASS is the local database name in the first entry prompt above (GLOBAL DATABASE NAME). Considerable thought should be given to the database name since it is difficult to change!
• The initialization parameter DB_NAME contains only the database name.
• Creation guidelines for the local database name includes:
• Can not exceed 8 characters
• Is not case sensetive
• Can only contain alphanumeric characters, “_”, “#” and “$”. No other characters are permitted.
• The database name is placed in the initialization parameter DB_NAME whenever the database creation scripts are created by the DBCA. For example the entry in the initialization file would be:
• DB_NAME = CLASS

Instructor's Notes

(If applicable)

• The database name is also placed in the CREATE DATABASE script whenever the database creation scripts are created by the DBCA. For example the entry in the appropriate database creation script would be:
• CREATE DATABASE CLASS
• Whenever the CREATE DATABASE script is executed, the database name (CLASS in our example) will be placed in the control files, redo log files and datafiles. When the database is mounted the DB_NAME in the initialization file must match the entries in the control file(s). If they do not match the database will not start!
• The domain name is the network domain where the database is created. In our example above the US.SKILLBUILDERS.COM is the domain name in the first entry prompt above (GLOBAL DATABASE NAME). This portion of the GLOBAL DATABASE NAME is optional. For example we could place just the database name CLASS if no network domain name existed. If applicable in your environment the value of the domain name is probably already determined and known well in advance of the creation of the database.
  • The The initialization parameter DB_DOMAIN contains only the domain name.
  • Creation guidelines for the domain name follows general domain name guidelines.
  • The domain name DB_DOMAIN is placed in the initialization parameter file whenever the database creation scripts are created by the DBCA. For example the entry in the initialization file would be:
  • DB_DOMAIN = us.skillbuilders.com
  • The domain name is not placed in the create database script that contains the CREATE DATABASE statement whenever the scripts are created by the DBCA.
• The System Identifier, or SID, is a unique identifier for the instance.
• The SID name will, by default, be the same as the database name entered in the Global Database Name box. This is a normal and desirable naming convention. The ability to change the name is related to support for Real Application Cluster environments. This is beyond the scope of this lesson.
• The SID name must be between 1 and 8 alphanumeric characters.
• The SID is included in the name of the background process required for the instance in Unix. In Windows the SID is contained in the name of the service.

Instructor's Notes

(If applicable)

• The ORACLE_SID environment variable in Windows or Unix should be set to the same name as the SID whenever the database is created. This is completed for you in the ORADIM utility whenever it is called from the DBCA during database creation.
• The SID is placed in the initialization parameter file whenever the database creation scripts are created by the DBCA. For example:
• 
  INSTANCE_NAME = CLASS


• Note: It is somewhat confusing that the SID corresponds to the INSTANCE_NAME in the initialization parameter file.


• To add additional confusion to the concept of SID and INSTANCE_NAME the INSTANCE_NAME has a relationship to the connection descriptor whenever a remote user attempts to connect the the Oracle database. For example whenever a user attempts to connect via the following command initiating SQL*Plus:
• 
  Connect System/Manager@connection_identifier


• SQL*Plus will read the TNSNAMES.ORA file for the following entry:
• 
  connection_identifier =(DESCRIPTION =
• (ADDRESS=(PROTOCOL=tcp)(HOST = SkillBuilders-Server)(PORT=1521)
• (CONNECT_DATA =
• (SERVICE_NAME = CLASS.us.skillbuilders.com)))


• Confusing! Yes. And we can see the relationship between the SID > INSTANCE_NAME > SERVICE_NAME.

Instructor's Notes

(If applicable)

• The DATABASE FEATURES tab of step 4 contains the following options:
• Additional options that were installed during product installation are available. Options that were not installed from the product CD will be grayed out. The features may include the following;
• Oracle Spatial – Allows the storage and retrieval of multi-dimensional information. Used in GIS systems.
• Oracle Ultra Search - support the storing and searching of multi-media objects commonly used in Web applications, e.g. audio, image and video types.
• Oracle Label Security – Provides an additional level of security required by government organizations.
• Oracle Data Mining – tools to allow the mining of Oracle databases.
• Oracle OLAP – Provides tools for helping with business operations and markets that can utilize the internet.
• Example Schemas – Installs example schemas including;
• Human Resources
• Order Entry
• Product Media
• It is recommended to install only those features that you have a specific need or interest in. Adding additional “weight” to the database is unnecessary. Most Oracle products can be installed at a later date with very little effort.

Instructor's Notes

(If applicable)

• The DATABASE CONNECTION OPTIONS of step five contains the following options:
• With DEDICATED SERVER MODE, each connection to the database creates a dedicated user process (A.K.A. shadow process). This user process performs the read requests on behalf of the user or client. This is an excellent configuration for environments with a small number of users or for support of users running long-running queries or operations against the database.
• In a SHARED SERVER MODE configuration, client requests are routed through a dispatcher, which acts like a traffic cop, directing requests to idle shared servers. Since this configuration reduces the number of total user processes running on the server, this is an excellent configuration for environments supporting many users, or environments where server resources are constrained. SHARED SERVER MODE was previously called Multi-Thread Server or MTS.
• The determination of which method to implement depends on the total number of users, the average number of concurrently connected sessions, and the type of transaction. For example a application with 1000 users with 50 concurrent connections and very small transactions would definitely point towards the SHARED SERVER MODE.
• Note: Additional information on DEDICATED SERVER MODE and SHARED SERVER MODE is contained in the Oracle 9i Architecture, Part II lesson.

Instructor's Notes

(If applicable)

• The MEMORY tab of Step 6 determines the following initialization file parameters:
• By clicking on the ALL INITIALIZATION PARAMETERS button parameters can be changed and reflected in the initialization file created by the DBCA. For example, if the PROCESSES parameter is changed from 150 to 200 the new value will be placed in the initialization file. The value set for PROCESSES must include the background processes and the user processes. Some parameters automatically change other parameters. For example, PROCESSES have a effect on the values set for SESSIONS and TRANSACTIONS. Initialization parameters can be changed at a later date except for DB_BLOCK_SIZE.
• PERCENTAGE OF PHYSICAL MEMORY (Available Memory) for Oracle – will distribute the SGA to the percentage of total memory set for the Oracle server.
• DATABASE TYPE – By selecting the database type the following SGA sizes will be altered:
• Shared Pool - Sets the initialization parameter SHARED_POOL_SIZE and the size in bytes of the shared pool. The shared pool contains the library cache and the data dictionary cache.
• Notes continue…