9
Creating a Database

This chapter describes how to create a database.

Specific topics discussed are:

• Naming Conventions for Oracle8 Databases

• Creating a Database Overview

• Using Oracle Database Assistant

• Using BUILD_DB.SQL

Naming Conventions for Oracle8 Databases

Before creating a database, it is important to understand that starting with Oracle8 Enterprise Edition, all mounted Oracle databases in a network must have unique database names. A database name is associated with a database at "CREATE DATABASE" time and stored in the control file(s) of the database. If the database keyword is provided in the CREATE DATABASE statement, then that value becomes the database name for that database. If not, then the value of the DB_NAME parameter in the INITSID.ORA file is used. If the DB_NAME parameter is not specified, then the default DB_NAME value is ORACLE.

If you attempt to mount two Oracle8 databases with the same database name, you receive the following error during the second mount:

ORA-01102: cannot mount database in EXCLUSIVE mode 

If there are two or more Oracle8 databases on the same computer located in different Oracle homes:

• each database name must be unique

• each SID must be unique

To change the name of an existing database, you must use the CREATE CONTROLFILE statement to recreate your control file(s) and specify a new database name. This restriction is imposed only on Oracle8 instances. Any Oracle7 instances running simultaneously with an Oracle8 instance are not subject to this restriction.

Creating a Database Overview

This section describes how to create a new database. You can choose either of the following tools to create a database:

• Oracle Database Assistant

• BUILD_DB.SQL script

Use Oracle Database Assistant to create a database because it is the easiest method. If you want to create a database using command line tools, you can use the BUILD_DB.SQL script located in ORACLE_HOME\RDBMS80\ADMIN.

Using Oracle Database Assistant

During installation of Oracle8 Enterprise Edition, you can select either the Typical Configuration option or Custom Configuration option to create a database with Oracle Database Assistant. If you already have a database, or do not want to create a database during the installation process, you can choose to use this assistant to create a database at a later date.

To create a database using Oracle Database Assistant:

1. Choose Start > Programs > Oracle for Windows NT - [HOME_NAME] > Oracle Database Assistant.

   The Oracle Database Assistant welcome window appears:

   

1. Select Create a database, then click Next.

   The following window appears:

   

2. Choose the Typical or Custom option to create a database.

   Option	Description
   Typical	Creates a standard database with default INITSID.ORA parameter settings.
   Custom	Customizes the creation of your database. This option is only for Oracle database administrators experienced with advanced database creation procedures, such as customizing: Data, control, and redo log file settings Tablespace sizes Extent sizes Database memory parameters Archiving formats and destinations Trace file destinations Character set values

3. Respond to instructions in each Oracle Database Assistant window, then click Next when you are ready to continue to the next screen. When you get to the last screen, click Finish to start the creation of the Oracle8 database.

Configure Oracle8 Cartridges Using Oracle Database Assistant

You can modify a Custom Configuration Oracle8 database to support the following cartridges that you installed through the Custom installation option on your CD-ROM, but have not yet configured:

• Oracle8 Spatial Cartridge

• Oracle8 Image Cartridge

• Oracle8 ConText Cartridge

• Oracle8 Time Series Cartridge

• Oracle8 Visual Information Retrieval Cartridge

Cartridges installed through the Custom Installation option are not automatically configured during installation. The Modify option of Oracle Database Assistant automatically configures your cartridges. If you installed Oracle cartridges through the Oracle8 Enterprise Edition option on the CD-ROM, your cartridges were automatically configured for the starter database ORCL.

Note: If you installed the Oracle8 Visual Information Retrieval Cartridge and its check box is shaded, you must select the Oracle8 Image Cartridge check box first. This action clears the Oracle8 Visual Information Retrieval Cartridge check box.

Delete a Database Using Oracle Database Assistant

The Delete a Database option of Oracle Database Assistant lets you quickly and easily delete databases.

Enable and Disable Multi-Threaded Support Using Oracle Database Assistant

You can choose options in Oracle Database Assistant to enable or disable multi-threaded server support in your Oracle8 database. Multi-threaded server (MTS) support enables many client user processes to share a small number of server processes. Many client users can connect to a dispatcher process. The dispatcher process then routes client requests to the next available shared server process. There is no dedicated server process for each user process that remains associated with the client user process for the duration of the connection. Instead, inactive server processes are "recycled" and used as needed. This reduces system overhead and enables you to increase the number of supported users. See Net8 Administrator's Guide, Oracle8 Server Concepts, and Oracle8 Reference for more information.

Using BUILD_DB.SQL

This section describes the steps to create a database manually. There are a number of ways to create a database depending on if you want to:

• Copy an existing database to a new database and remove the old database.

• Copy an existing database to a new database and keep the old database.

• Create a new database when no other database exists on your system that you can copy.

The table below summarizes the steps to create a new database for each of the above database creation scenarios. Each step is explained in detail in the following sub-sections.

Perform These Tasks...	If You Want To...
	Copy an existing database and remove the old database	Copy an existing database and keep the old database	Create a new database when no other database exists that you can copy
Export an Existing Database	Yes	Yes. Only if you want to copy data from an existing database to the additional database	Not applicable
Delete Database Files	Yes	No	Not applicable
Modify the INITSID.ORA File	Yes	Yes	Yes
Create and Start an Oracle8 Service	No	Yes	Yes
Put CREATE DATABASE Statement in a Script	Yes	Yes	Yes
Create a Database	Yes	Yes	Yes
Import a Database	Yes	Yes. Only if you want to import tables and other objects exported from an existing database	Not applicable
Update ORACLE_SID in the Registry	No	Only if you want to change the default SID	Yes
Back Up the New Database	Yes	Yes	Yes

Create Database Example

An example is used in the following sections to demonstrate the commands to create a database. In this example, you will copy an existing database (the starter database with a SID of ORCL located in the C:\ ORANT directory) to a new database with a SID of PROD located in the C:\MYDIR directory. You will delete the ORCL database after you have created the PROD database.

Create \MYDIR Directory

Create a directory, for example, C:\MYDIR in which to put the new database PROD.

Export an Existing Database

It is only necessary to export if you want to copy the contents of an existing database to a new database. You must perform this task if you are going to remove the old database and put its data in the new database. If you are going to create an additional database, you only need to this if you want a copy of the data from the old database in the new database.

To export all data from an existing database to a new database:

1. Set ORACLE_SID to the database service of the database whose contents you want to export. For example, if the database you want to export is the starter database ORCL, enter the following at the MS-DOS command prompt. Note there are no spaces around the equal sign (=) character.

   C:\ORANT> SET ORACLE_SID=ORCL 
   

1. Start the Export utility from the MS-DOS command prompt:

   C:\ORANT> EXP80 SYSTEM/PASSWORD FILE=MYEXP.DMP FULL=Y LOG=MYEXP.LOG 
   

   You now have a full database export of the starter database ORCL in the file MYEXP.DMP, with all messages from the Export utility logged in the MYEXP.LOG file.

Delete Database Files

It is only necessary to delete database files if you want to copy an existing database to a new database and then remove the old database.

To delete database files:

1. Shut down the starter database ORCL at the MS-DOS command prompt:

   C:\ORANT> ORADIM80 -SHUTDOWN -SID ORCL -USRPWD PASSWORD -SHUTTYPE INST 
   -SHUTMODE I
   

1. Delete the following database files located in the ORANT\DATABASE directory:
   • SYS1ORCL.ORA
   • USR1ORCL.ORA
   • RBS1ORCL.ORA
   • TMP1ORCL.ORA
   • LOG1ORCL.ORA
   • LOG2ORCL.ORA
   • LOG3ORCL.ORA
   • LOG4ORCL.ORA
   • CTL1ORCL.ORA

LOG3ORCL.ORA AND LOG4ORCL.ORA database files are only available with release 7.3.4 and 8.0.4 databases. Do not delete the INITORCL.ORA file, as it will be the basis for the new initialization parameter file INITPROD.ORA in "Modify the INITSID.ORA File".

Modify the INITSID.ORA File

If you are using the starter database ORCL as the basis for your new database, copy C:\ORANT\DATABASE\INITORCL.ORA to C:\MYDIR\DATABASE\ INITPROD.ORA and modify the file.

If you do not have an existing database on your system, you cannot copy an initialization parameter file to use as the basis for your INITPROD.ORA file. You can use the sample initialization parameter file INITORCL.80 provided in the C:\ORANT\DATABASE directory as the basis for INITPROD.ORA.

You must modify the CONTROL_FILES and DB_NAME initialization parameters in the INITPROD.ORA file or you will not be able to start the PROD database. Modifying the initialization parameters GLOBAL_NAMES and DB_FILES is highly recommended to optimize performance.

Initialization Parameter	How to Modify...
CONTROL_FILES	This parameter lists the control files of the database. You do not have the control files on your file system at this point, because the control files are created when you execute the CREATE DATABASE statement. Ensure you specify the complete path and file name, including drive letter. For example, CONTROL_FILES = (C:\MYDIR\DATABASE\CTL1PROD.ORA, ORACLE_HOME\DATABASE\CTL2PROD.ORA)
DB_NAME	This parameter indicates the name of the database, and must match the name used in the CREATE DATABASE statement in "Using BUILD_DB.SQL" in this chapter. This name does not need to match the SID of the database service. You give a unique database name to each database. For example, DB_NAME=PROD. You can use up to eight characters for the database name.
GLOBAL_NAMES	The default value of this parameter is FALSE; however, it is recommended to set this parameter to TRUE. See the Oracle8 Administrator's Guide to find out more about global names and how they relate to database links.
DB_FILES	Set this initialization parameter to the same number as the value of the MAXDATAFILES option of the CREATE DATABASE statement. The value of 100 is used for this example. DB_FILES=100

Additional Information: See Appendix B, "Initialization Parameters" for information on other initialization parameters you may want to add to the INITPROD.ORA file.

Create and Start an Oracle8 Service

It is only necessary to create and start an Oracle8 service if you want to:

• Copy an existing database to a new database and keep the old database.

• Create a new database when no other database exists on your system that you can copy.

Before you create the database, first create a Windows NT service to run the database. This service is the Oracle8 database process, ORACLE80.EXE, installed in the form of a Windows NT service. Use ORADIM80 to create the service. Upon creation, the service starts automatically. See "Using ORADIM80" in Chapter 3, "Database Tools Overview", for information on how to use ORADIM80.

To create a Windows NT service:

1. Go to the \MYDIR directory.

   C:\> CD MYDIR
   

1. Run ORADIM80 from the MS-DOS command prompt:

   C:\MYDIR> ORADIM80 -NEW -SID PROD -INTPWD PASSWORD -STARTMODE AUTO 
   -PFILE C:\MYDIR\DATABASE\INITPROD.ORA 
   

   Note that the previously created INITPROD.ORA file is specified, with complete path, including drive name. You do not receive a message indicating the success or failure of this command. You can check if the service is started in the services window of the Windows NT Control Panel.

2. Set ORACLE_SID to equal PROD. Note there are no spaces around the equal sign (=) character:

   C:\MYDIR> SET ORACLE_SID=PROD 
   

Put CREATE DATABASE Statement in a Script

The CREATE DATABASE statement is a sequence of SQL statements that creates the database. Create a script containing this statement that you can reuse anytime you want to create a database. Use the BUILD_DB.SQL script located in C:\ORANT\RDBMS80\ADMIN as a basis for your script.

To prepare the CREATE DATABASE script:

1. Copy C:\ORANT\RDBMS80\ADMIN\BUILD_DB.SQL to C:\MYDIR\RDBMS80\ADMIN\BUILD_PROD.SQL and make the following changes to the BUILD_PROD.SQL script:

   1. Set PFILE so it points to the C:\MYDIR\DATABASE\INITPROD.ORA initialization file.

   2. Modify CREATE DATABASE ORACLE to CREATE DATABASE PROD.

   3. Modify all the log file and data file names. Change all occurrences of ORCL to PROD. For example, change LOG1ORCL.ORA to LOG1PROD.ORA.

   4. Modify the location of the Oracle home directory including drive name if necessary. Change all occurrences of ORANT to MYDIR. For example, change C:\ORANT\DATABASE to C:\MYDIR\DATABASE.

   A sample script follows:

   -- This file must be run out of the directory containing the 
   -- initialization file. 
   startup nomount pfile=C:\mydir\DATABASE\initprod.ora 
   --  Create database 
   create database prod 
   controlfile reuse 
   logfile 'C:\mydir\DATABASE\log1prod.ora' size 1M reuse, 
   	'C:\mydir\DATABASE\log2prod.ora' size 1M reuse, 
   	'C:\mydir\DATABASE\log3prod.ora' size 1M reuse, 
   	'C:\mydir\DATABASE\log4prod.ora' size 1M reuse, 
   datafile 'C:\mydir\DATABASE\sys1prod.ora' size 10M reuse autoextend on 
   next 10M maxsize 200M 
   character set WE8ISO8859P1;  
   create rollback segment rb_temp;  
   -- Create additioanal tablespaces ...  
   -- USER_DATA: Create user sets this as the default tablespace 
   -- TEMPORARY_DATA: Create user sets this as the temporary tablespace 
   -- ROLLBACK_DATA: For rollback segments 
   create tablespace user_data 
   	datafile 'C:\mydir\DATABASE\usr1prod.ora' size 3M reuse autoextend on 
   	next 5M maxsize 150M; 
   create tablespace rollback_data 
   	datafile 'C:\mydir\DATABASE\rbs1prod.ora' size 5M reuse autoextend on 
   	next 5M maxsize 150M; 
   create tablespace temporary_data 
   	datafile 'C:\mydir\DATABASE\tmp1prod.ora' size 2M reuse autoextend on 
   	next 5M maxsize 150M; 
   alter rollback segment rb_temp online;\x7f  
   -- Change the SYSTEM users' password, default tablespace and 
   -- temporary tablespace. 
   alter user system temporary tablespace temporary_data; 
   alter user system default tablespace user_data; 
   -- Create 16 rollback segments.  Allows 16 concurrent users with open 
   -- transactions updating the database. This should be enough. 
   create public rollback segment rb1 storage(initial 50K next 50K) 
   tablespace rollback_data; 
   create public rollback segment rb2 storage(initial 50K next 50K) 
   tablespace rollback_data; 
   create public rollback segment rb3 storage(initial 50K next 50K) 
   tablespace rollback_data; 
   create public rollback segment rb4 storage(initial 50K next 50K) 
   tablespace rollback_data; 
   create public rollback segment rb5 storage(initial 50K next 50K) 
   tablespace rollback_data; 
   create public rollback segment rb6 storage(initial 50K next 50K) 
   tablespace rollback_data; 
   create public rollback segment rb7 storage(initial 50K next 50K) 
   tablespace rollback_data; 
   create public rollback segment rb8 storage(initial 50K next 50K) 
   tablespace rollback_data; 
   create public rollback segment rb9 storage(initial 50K next 50K) 
   tablespace rollback_data; 
   create public rollback segment rb10 storage(initial 50K next 50K) 
   tablespace rollback_data; 
   create public rollback segment rb11 storage(initial 50K next 50K) 
   tablespace rollback_data; 
   create public rollback segment rb12 storage(initial 50K next 50K) 
   tablespace rollback_data; 
   create public rollback segment rb13 storage(initial 50K next 50K) 
   tablespace rollback_data; 
   create public rollback segment rb14 storage(initial 50K next 50K) 
   tablespace rollback_data; 
   create public rollback segment rb15 storage(initial 50K next 50K) 
   tablespace rollback_data; 
   create public rollback segment rb16 storage(initial 50K next 50K) 
   tablespace rollback_data; 
   

You will run this script at the Server Manager prompt in the section, "Create a Database".

Create a Database

Before you run the BUILD_PROD.SQL script to create a database, you must perform the following tasks:

1. Check if the service is started in the Windows NT Control Panel. In this example, the service name is OracleServicePROD, and its status column should display Started. If not, single-click the service name and choose the Start button to start the service. Alternatively, check the status of the service by entering the following at the MS-DOS command prompt.

   C:\MYDIR> NET START 
   

   A list of all Windows NT services currently running on the system appears. If OracleServicePROD is missing from the list, enter:

   C:\MYDIR> NET START ORACLESERVICEPROD 
   

1. Make PROD the current SID:

   C:\MYDIR> SET ORACLE_SID=PROD 
   

2. Start Server Manager from the MS-DOS command prompt, and connect to the database as INTERNAL:

   C:\MYDIR> SVRMGR30
   SVRMGR> CONNECT INTERNAL/PASSWORD 
   

   The password is the one you previously used to create the service, with the ORADIM80 -NEW command in "To create a Windows NT service:". You should see the message Connected.

3. Start the database in NOMOUNT mode:

   SVRMGR> STARTUP NOMOUNT PFILE=C:\MYDIR\DATABASE\INITPROD.ORA 
   

   When the initialization parameter file INITPROD.ORA is located in the DATABASE subdirectory of your Oracle home directory, the PFILE specification is optional. Otherwise, you must specify the PFILE option with the complete path and file name of the initialization parameter file. Note that there is an equal sign (=) between the keyword PFILE and the path/filename. There are no space characters around the equal sign (=).

4. Turn on spooling to save the messages and run the BUILD_PROD.SQL script you created in the section, "Put CREATE DATABASE Statement in a Script":

   SVRMGR> SPOOL BUILD_PROD.SQL.LOG 
   SVRMGR> @BUILD_PROD.SQL 
   

   This command assumes the script file is located in the directory from which Server Manager was invoked. If this is not the case, specify the complete path in front of the file name. If the database is created successfully, the message Statement processed appears. If you receive any errors, there are three possible causes:

   • The BUILD_PROD.SQL script contained syntax errors. Correct them.
   • Some of the files to be created by the BUILD_PROD.SQL script already exist in the file system. Make sure you are not using any file names already used by another database on the system.
   • There was an error at the operating system level, such as a file or directory permission problem. You should have received a series of errors in Server Manager, the last one of which should have the OSD- prefix. At the end of the OSD error you typically see an operating system error number in parentheses. To see what the error means:

     From the MS-DOS command prompt, enter:

     C:\> NET HELPMSG n 
     

     or

     From the Server Manager prompt, enter:

     SVRMGR> HOST NET HELPMSG n 
     

     where n is the operating system error number. See "Operating System Permission Issues" in Appendix F, "Troubleshooting" for more information.

   You must correct these problems before attempting to recreate a database.

5. Run the following script to generate the data dictionary:

   SVRMGR> @%RDBMS80%\ADMIN\CATALOG.SQL 
   

6. Run the following script to install the objects used by the Oracle8 database's PL/SQL functionality:

   SVRMGR> @%RDBMS80%\ADMIN\CATPROC.SQL 
   

7. Run the following script if you want Advanced Replication functionality with the new database:

   SVRMGR> @%RDBMS80%\ADMIN\CATREP8M.SQL 
   

   Note that CATREP8M.SQL requires at least an hour to run, depending on your system.

8. Turn off spooling after all scripts have finished running:

   SVRMGR> SPOOL OFF 
   

9. Examine the BUILD_PROD.SQL.LOG file for any errors.

   Note: You may see many messages such as object to be dropped does not exist and name already used by another object while the scripts are running. These are information messages and are intended to occur while creating a new database. If you see any unusual errors in the BUILD_PROD.SQL.LOG log file, see Oracle8 Server Messages for suggested actions.

   IMPORTANT: The new database contains two users, SYS and SYSTEM, with passwords CHANGE_ON_INSTALL and MANAGER, respectively. For security reasons, change the passwords now. Use the ALTER USER statement to change the passwords: SVRMGR> ALTER USER SYS IDENTIFIED BY NEW_SYS_PASSWORD; SVRMGR> ALTER USER SYSTEM IDENTIFIED BY NEW_SYSTEM_PASSWORD;

Import a Database

You can choose to import the full export created in the section "Export an Existing Database" into the new database.

1. Exit Server Manager:

   SVRMGR> EXIT 
   

1. Run the Import utility:

   C:\MYDIR> IMP80 SYSTEM/PASSWORD FILE=MYEXP.DMP FULL=Y LOG=MYIMP.LOG
   

   IMPORTANT: If the original database from which the export file was generated contains tablespaces that are not in the new database, then the Import utility attempts to create those tablespaces with associated data files. The easy solution is to ensure both databases contain the same tablespaces. The data files do not have to be identical. Only the tablespace names are important.

Update ORACLE_SID in the Registry

If this is the first database on the system or if you want to make the new database the default database, you must make a change in the registry.

1. Invoke the registry editor at the MS-DOS command prompt by entering REGEDT32. For example:

   C:\MYDIR> REGEDT32 
   

   The registry editor window appears.

1. Choose the \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID subkey for the first Oracle home on your computer. For subsequent installations to different Oracle homes on the same computer, the path is \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\NLS_LANG\HOMEID where ID is the unique number identifying the Oracle home. See Appendix C, "Configuration Parameters and the Registry" for more information on the subkey locations for multiple Oracle homes.

2. Locate the ORACLE_SID parameter on the right side of the registry editor window.

3. Double-click the parameter name, and change the data to the new SID, which is PROD in this example.

If you do not have ORACLE_SID because this is the first database on your system, you must create the ORACLE_SID parameter.

To create ORACLE_SID parameter:

1. Start the registry in either of two ways:
   • From the MS-DOS command prompt, enter:

     C:\> REGEDT32
     

   • Choose Start > Run, enter REGEDT32 in the Open field, and click OK.

   The registry editor window appears.

1. Navigate to the \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID key.

2. Choose the Add Value... option in the Edit menu.

   The Add Value dialog box appears:

   

3. Enter ORACLE_SID in the Value Name field.

4. Enter REG_EXPAND_SZ (for an expandable string) in the Data Type box

5. Click OK.

   A string editor dialog box appropriate for the data type appears:

   

6. Enter PROD in the string editor dialog box

   The registry editor adds the ORACLE_SID parameter.

7. Choose Exit from the registry menu.

   The registry exits.

Back Up the New Database

WARNING: If anything goes wrong while operating the new database without a backup, you must repeat the database creation procedure. Back up your database now to prevent such damage.

To back up the new database:

1. Shut down the database instance and stop the service:

   C:\MYDIR> ORADIM80 -SHUTDOWN -SID PROD -USRPWD PASSWORD 
   -SHUTTYPE SRVC,INST -SHUTMODE I
   

   WARNING: Although ORADIM80 returns the prompt immediately, you must wait for the database and the service to stop completely before continuing to Step 2. Wait until the Control Panel indicates the OracleServicePROD service has stopped. If you do not do this, the backup may be useless as it was taken while data was being written to the data files.

1. Using the tool of your choice, back up the database files. Data files consist of the initialization parameter file, control files, online redo log files, and data files.

   You can now start the database again, create users and objects if necessary, make any other changes, and use the database. Ensure you make a database backup, if possible, after making any significant change to the database, such as switching the ARCHIVELOG mode or adding a tablespace or data file.

   Additional Information: See Chapter 13, "Backing Up and Recovering Database Files", Oracle8 Concepts, Oracle8 Backup and Recovery, and Oracle8 Administrator's Guide for more information on archiving and backup/recovery.

---