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…

Instructor's Notes

(If applicable)

• Buffer Cache – Sets the initialization parameter DB_CACHE_SIZE and sets the number of buffers that should be within the buffer cache. This controls, in conjunction with DB_BLOCK_SIZE, the size of the buffer cache. This is a critical database tuning issue.
• Java Pool – Sets the initialization parameter JAVA_POOL_SIZE and sets the size in bytes of the Java pool for session specific Java code.
• Large Pool – Sets the LARGE_POOl_SIZE initialization parameter and the size of large pool. The large pool is used for the Shared Server implementation for session information.
• PGA – Controls the storage of parse and control information from users SQL. This includes bind variables from SQL, sort areas, and cursor handling.

Instructor's Notes

(If applicable)

• The CHARACTER SETS tab of step 6 determines the following parameters:
• Using the default is highly recommended if your database is dealing strictly with English characters and numbers. If your database is storing international characters then the database administrator should research the possibility of changing character sets.
• Use Unicode sets the initialization parameter NATIONAL_CHARACTER_SET and determines the storage of NCHAR, NVARCHAR2, and NCLOB data types. The only available types are UTF8 and AL16UTF16. The setting to AL16UTF16 will allow the storage of multiple languages. The default is UTF8.
• Choose from the list of character sets sets the initialization parameter CHARACTER_SET and determines what language and characters you want to be displayed in the database. It is critical that this is set correctly as it can not be changed after the database has been created. Problems may be encountered if data from one database is imported into another database!


• Supplemental Note
  • Please refer to the Oracle 9i Globalization Support Guide for exact details on character sets and languages supported by Oracle.

Instructor's Notes

(If applicable)

• DB SIZING tab of step 6 sets the following parameters:
• DB_BLOCK_SIZE sets the initialization parameter DB_BLOCK_SIZE. The DB_BLOCK_SIZE is the size of internal Oracle blocks and is usually some multiplier of the operating system block size. The DB_BLOCK_SIZE can not be changed after the database has been created. Block sizes for individual tablespaces can be different than the default DB_BLOCK_SIZE set whenever the database was created.
• SORT_AREA_SIZE sets the initialization parameter SORT_AREA_SIZE. The SORT_AREA_SIZE set the size of the User Global Area (UGA). This area is used to perform sorts for individual users. The SORT_AREA_SIZE parameter can be a critical parameter in transactions that performs many sorts.

Instructor's Notes

(If applicable)

• FILE LOCATIONS tab of step 6 determines storage options of initialization parameter files and trace files:
• The SPFILE option is new to Oracle9i and has the following characteristics:
• The SPFILE is stored only on the respective Oracle 9i database server.
• Database changes by the database administrator such as ALTER DATABASE are can changed in memory, to the SPFILE, or both. This is dependent on the SCOPE specified in the ALTER DATABASE statement.
• Allows the database to be started remotely without access to the PFILE. This is extremely helpful in a web enabled environment using a web browser.
• Trace File Directories specifies the location for the following trace files.
• User Processes - User process that start a trace (e.g. ALTER SESSION SET SQL_TRACE = TRUE) will write trace output to the location specified in the ‘For User Processes’ field. This sets the INIT.ORA parameter USER_DUMP_DEST. Trace file output is converted to readable format with the TKPROF utility.
• Notes continue…

Instructor's Notes

(If applicable)

• Background Processes – Includes the processes DBWR, LGWR, PMON, SMON, and CKPT. Oracle background processes generate “alert” files which record significant events in the life of the process (e.g. startup, shutdown and errors). The BACKGROUND_DUMP_DEST INIT.ORA parameter sets the location of the alert files.
• Core Dumps – Core dumps are generated whenever a serious database failure. Generally, core dumps are only readable by Oracle support personnel. The core dump location is set by the initialization parameter CORE_DUMP_DEST.

Instructor's Notes

(If applicable)

• The ARCHIVE tab of step 6 determines the following parameters:
• Archiving allows the saving of online redo log files after a log switch. Archiving protects the Oracle database in the event of media failure.
• The following options are available whenever enabling ARCHIVELOG mode;
• Archive Log Mode places the Oracle database in ARCHIVELOG mode.
• Automatic archival will set the initialization parameter LOG_ARCHIVE_START to TRUE. The setting to TRUE will instruct Oracle to automatically copy the newly filed Online redo log file to the designated archiving location. The copying newly filed Online redo log files is performed by the background process ARCH.
• Log Archive Filename Format sets the serialized format of the archived redo log files.
• Log Archive Filename Destination(s) sets the destination of the Archived redo log files. This should be set to an available disk resource and not a tape drive. The setting to a tape drive may cause the database to “hang” since Oracle may have to wait for a file to be completely copied to the tape drive.
• Additional information on archiving will be presented in the Managing Redo Logs and User Managed Backup and Recovery lessons.

Instructor's Notes

(If applicable)

• The DATABASE STORAGE option of step 7 allows the database administrator to customize storage locations and restrictions of most database files including:
• Control Files
• Tablespaces
• datafiles
• Rollback Segments (Undo)
• Redo Log Groups


• Tablespaces and datafiles can be added and deleted from the specifications.

Instructor's Notes

(If applicable)

• The CONTROL FILES option of step 7 sets important information about the control files.
• The database control file is a critical OS file that contains internal database information such as database name, location and names of all database files, location and names of log files and other recovery information. The control file is used in the second stage of instance start-up (MOUNT).
• Losing this file causes a major headache in recovery. However, a further level of protection can be set-up by having the Oracle instance keep more than one copy of the control file always updated on separate physical disks. This would ensure that if one copy is lost we always have another copy which we can use. This step directly affects the CONTROL_FILES database parameter. Additional control files can be added at any time.
• The Options tab of step 7 set limits on the maximum number of certain files. The maximum number of datafiles, redo logs and log members which can exist on the database can be specified as part of the CREATE DATABASE statement using the MAXDATAFILES, MAXLOGMEMBERS and MAXLOGFILES parameters. This last 3 entries on this window control those parameters. The maximum number of datafiles that can be specified is 9999. The maximum number of log files that can be specified is 255. The maximum number of log members is 5. Refer to the “CREATE DATABASE” command later in this lesson and the Managing Redo Log Files lesson for more information.

Instructor's Notes

(If applicable)

• The TABLESPACES option of step 7 also allows us to specify the initial size and growth controls of tablespaces that will hold database segments (tables, indexes, clusters and temporary objects). Following are characteristics of common tablespaces contained in an Oracle database:
• SYSTEM - The SYSTEM tablespace is the first tablespace created and holds the data dictionary (and should not be populated with user objects). The SYSTEM tablespace can be either locally or dictionary managed. By default, it is locally managed (LMT). If LMT is used for the SYSTEM tablespace, all other tablespaces must also be locally managed.
• USERS – This USERS tablespace is anticipated to be used as a storage area for users objects.
• UNDOTBS - The UNDO tablespace supports undo segments. Creation, monitoring & management of undo segments is covered in the Managing Undo lesson.
• The INDEX tablespace is anticipated to be used for indexes as it is best (for performance) to keep indexes on a different disk device than tables. The INDEX tablespace is locally managed.
• Notes continue…

Instructor's Notes

(If applicable)

• The TEMP tablespace is required for temporary sort space. When creating new users with the CREATE USER command, use the TEMPORARY TABLESPACE parameter to force the use of this tablespace for temporary segments.
• By double clicking on the tablespace another window will appear with the GENERAL and STORAGE tabs. These tabs supports taking a tablespace offline/online, automatic/uniform extent allocation, automatic/manual space management, and alternate block sizes.

Instructor's Notes

(If applicable)

• The DATAFILES option of step 7 allows the specification of the initial size and growth controls for the datafiles associated with tablespaces.
• The following can be changed in regards to datafiles under the GENERAL and STORAGE tabs:
• Taking the datafile offline or online.
• Altering the size of the datafile.
• Ability to reuse a existing datafile. Saves time in creating a new database.
• Automatically extending the datafile whenever it becomes full including predetermined increments.
• Specifying the maximum size of a datafile to prevent runaway growth.
• The adding an removing of datafiles.

Instructor's Notes

(If applicable)

• The ROLLBACK SEGMENTS of option of step 7 allows management of rollback segments or undo.


• One of the major differences between prior versions of Oracle and Oracle 9i is the new management method of Rollback Segments. In Oracle 9i the database administrator has the choice of using automatic Undo management or using the prior method of managing Rollback Segments.


• The concepts of undo and rollback tablespaces is presented in the Tablespaces and Managing Undo lessons.

Instructor's Notes

(If applicable)

• The REDO LOG GROUPS option of step 7 allows the management of redo log files.
• The on-line redo log files are used to record changes made to the database files. They are used in a cyclical fashion - when one redo log file fills, a log switch occurs, causing the database to start writing to the next log file group. Therefore, a minimum of two log files are required.
• The filled, inactive log file will then be written to an archive destination if the ARCH background process has been started. This process consumes time; sometimes the archive may not have finished before the log file is needed again. This will cause the database to cease all operations until the archive process has completed - a very undesirable situation. It is for this reason we can add more redo log files.


• More log file groups can be added to the database at any time.
• More redo log files can be added to groups for multiplexing of the Online redo log files.
• Redo log files are covered in greater detail later in the Managing Redo Log Files lesson.

Instructor's Notes

(If applicable)

• Create Options
• Finish!
• Finally, we are asked the following options:
• CREATE DATABASE – Will create the Oracle 9i database. This process will take a significant amount of time. Avoid interrupting the machine during this - you will have to undo some or all of the following if the database creation fails.
• Use the DBCA to delete the database if possible
• Manually delete files created by the process
• Make changes in the registry
• SAVE AS A DATABASE TEMPLATE - Will save the database configuration as a database template. The file extension will be .dbt. It will be placed in the default directory ORACLE_HOME\ASSISTANTS\DBCA\TEMPLATES. The template file can be viewed however it can not be executed within SQL*Plus as the SQL based script created in option number 3.
• GENERATE DATABASE CREATION SCRIPTS – Creates a SQL formatted database creation script that can be run in SQL*Plus at a later date. This is probably the preferred method for most experienced database administrators.

Instructor's Notes

(If applicable)

• Let’s review the scripts created by the DBCA (Database Configuration Assistant).
• ORACLE_SID - First, the environment variable ORACLE_SID is set so all subsequent activity uses the new SID.
• ORADIM - Second the Oracle Instance Manager is executed to create a new SID and new Windows service. This is the utility explained later in this lesson.
• ORAPWD - Third the Oracle password utility is executed to create the password file.

Instructor's Notes

(If applicable)

• SQL*Plus is then executed numerous times, each time executing another script including;
• CREATEDB.SQL – Connects to the instance and starts the instance in NOMOUNT mode. Uses the initialization parameter file INIT.ORA to start the instance. Creates the database including the SYSTEM tablespace, UNDO tablespace and the redo log files.
• CREATEDBFILES – Creates the additional tablespace including the TEMP tablespace and USERS tablespace. Additional tablespaces may be created depending on the options selected.
• CREATEDBCATALOG.SQL – Calls many SQL*Plus scripts to create the data dictionary, procedural options, PL/SQL utilities and other options selected during DBCA configuration.
• POSTDBCREATION.SQL – Creates a SPFILE from the initialization parameter file to save on the Oracle 9i server.
• The other scripts (not described ) install features such as XML DB.

Instructor's Notes

(If applicable)

• In Windows, a Service is required to create and run an instance. Creating a new service, modifying a service, or deleting a service is accomplished with the Oracle Instance Manager (ORADIM.EXE). Available parameters to pass to ORADIM include;
• -NEW - create a new database instance
• -STARTUP - startup an existing instance, service or both
• -SHUTDOWN - Shutdown an active instance
• -EDIT - Modify an instance. e.g. change the instance name from class to PROD
• -DELETE - Delete an instance and related service. Removes the service from the list shown in theWindowsServices utility.
• -SID - SID name
• -SERVE - Service name; same as SID name.
• -USERPWD - Password (Internal no longer supported)
• -STARTMODE [AUTO | MANUAL ] - Auto starts the instance automatically when Windows starts.
• -PFILE - Name of the initialization parameter file for this instance.


• Notes continue…

Instructor's Notes

(If applicable)

• -SHUTTYPE – Indicates to stop the instance or the service.
• -NEWSID – A new instance name.
• The service will always be called OracleServicexxxx where xxx = SID name.
• The Database Configuration Assistant (DBCA) will include in its scripts the entries to execute ORADIM and create the services. The database administrator may be required to use the Oracle Instance Manager to remove an existing service.

Instructor's Notes

(If applicable)

• The first SQL script executed, CREATEDB.SQL, runs the CREATE DATABASE command. You will be able to see the relationship between the choices you made within the DBCA and the parameters in the CREATE DATABASE script. The following events occur in the order show above;
• A connection is made to the Oracle database. The “connect internal” method of connection is no longer supported. The UserId and Password is actually authenticated via the password file.
• Output is echoed to the screen.
• Output is spooled out to the log file CREATEDB.LOG for review by the database administrator.
• The instance is started however the database can not be mounted since no database exists at this point in time. The database is started using the initialization parameter file:
• D:\oracle\admin\class\scripts\Prod\INIT.ORA
• The key words “CREATE DATABASE class” indicates that you wish to create a new database.
• Three control files are created however they will all be on the same disk. The number and placement of control files are determined by the CONTROL_FILES entry in the initialization file. control files should be multiplexed for redundancy.
• Notes continue…

Instructor's Notes

(If applicable)

• The MAXINSTANCES parameter controls the maximum number of instances, typically running on different machines, which can be connected to database. A real application cluster configuration will have more than 1 instance connected to the same database.
• The maximum number of datafiles, redo log files and redo log members which can exist is controlled using the MAXDATAFILES, MAXLOGFILES and MAXLOGMEMBERS. By specifying lower limits, less resources are used in the SGA. However, the only way to increase these values is to recreate the control file.

Instructor's Notes

(If applicable)

• The SYSTEM tablespace is created with a size of 325MB. This is quite large for a SYSTEM tablespace. An existing file is reused. AUTOEXTEND is enabled and the file can grow to an unlimited size.
• The UNDO tablespace is created with a name of UNDOTBS. An existing file is reused. AUTOEXTEND is enabled and the file can grow to an unlimited size.
• The CHARACTER SET is set to WE8MSWIN1252
• The NATIONAL CHARACTER SET is set to AL16UTF16
• The LOGFILE parameter specifies 3 redo log file groups. However, notice they are all on the same disk device - an undesirable situation. Additional members should be added to each redo log file groups on separate drives for redundancy. This is presented in the Managing Redo Log Files lesson.
• Spooling is turned off.
• An exit is made from SQL*Plus.

Instructor's Notes

(If applicable)

• Create Database Workshop
  1. Stop any active instances/databases/windows services on your server.
  2. Use the DATABASE CONFIGURATION ASSISTANT to create a new database called CLASS.
  3. Inspect the scripts. In Windows, BE CAREFUL NOT TO DOUBLE-CLICK THE .BAT FILE TO ATTEMPT TO EDIT IT - THIS WILL EXECUTE IT BEFORE YOU HAVE HAD A CHANCE TO REVIEW IT. In Windows, Edit it with a single right-click, then select the EDIT option. In Unix use vi.
  4. Execute the batch file. In Windows, execute it by double-clicking on the file name from Windows Explorer, or by typing YOURNAME.BAT from a command prompt while in your class directory. In Unix, execute it by typing "sh yourname.sh" from a Unix prompt while in your class directory. You will see a DOS window with many commands executing and scrolling by. Error messages will be displayed since the scripts attempt to delete objects before creating them, resulting in error messages if the objects are not found.
  5. Rename the batch file extension from “.bat” or “.sh” to “.DoNotExecute” so that you do not accidentally rerun the script, thereby destroying your new database. The batch file should have the prefix of the SID.
• Workshop continues…

Instructor's Notes

(If applicable)

6. After the script has completed (no completion messages appear) the log file will need to be reviewed to insures success.
• First check the CREATEDB.LOG file to insure the the CREATE DATABASE command returned “Statement Processed”. If you do not see this message, chances are the CREATE DATABASE failed and all other subsequent commands will fail.
• Review the CREATEDBFILES.LOG. Again look for the “Statement Processed” message in the log file.
• Then review the CREATEDBCATALOG file. In Windows, open this file with WORDPAD as it may be too large for Notepad. You should find many ORA-01432 errors - “public synonym to be dropped does not exist” and similar messages. These are normal.
7. In Windows, use Services (from Control Panel) to look for the status of the Windows Service that was built for you database. Is it started? Is it automatically started each time you start Windows?
8. Log onto the database with SYS and SYSTEM. Remember if connecting as SYS you must use the “as SYSDBA” to connect. Execute the following queries from SYSTEM:
• SELECT * FROM v$instance;
• SELECT * FROM v$database;
• SELECT * FROM v$controlfile;
9. As we did in the previous lab, create a new user (your first name is a good choice for a username and password), and set its default tablespace to “users”. Grant the privileges necessary for startup and shutdown to this user, and also grant it DBA privileges.