Oracle8(TM) Getting Started for Windows NT Release 8.0.3 A54894-01

Library Product Contents Index

9
Creating a Database

This chapter describes how to create a new database.

Specific topics discussed are:

• Naming Conventions for Oracle8 Databases
• Using Oracle Database Assistant
• Creating a Database Manually

Naming Conventions for Oracle8 Databases

Before creating a database, it is important to understand that starting with Oracle8, all mounted Oracle databases on a given computer 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 

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.

Using Oracle Database Assistant

Oracle Corporation recommends you use Oracle Database Assistant as the simplest and quickest method to create or delete a database. During installation of Oracle8, you are prompted to create a database with this wizard if Oracle Installer detects that no database exists on your system. If you already have a database, or do not want to create a database during the installation process, you can choose to use this wizard to create a database at a later date.

To create a database using Oracle Database Assistant:

1. Choose Start>Programs>Oracle for Windows NT>Oracle Database Assistant.
2. During installation choose the option, Custom using Oracle Database Assistant

The Oracle Database Assistant welcome screen appears:



3. Select Create a database, then click Next.

The following screen appears:



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

Option	Description
Typical	Creates a standard database with default INITSID.ORA parameter settings. You are not prompted to provide any settings for these database parameters. This option is sufficient for most environments and for Oracle database administrators inexperienced with advanced database creation procedures.
Custom	Offers you the opportunity to customize 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

5. Proceed to the last Oracle Database Assistant screen and complete the creation of the Oracle8 database.

Additional Information: Click Help to access the online documentation.

Creating a Database Manually

This section describes steps to create a database manually. An example is used in the following sections to demonstrate the commands involved. In this example:

1. Create a new database whose System Identifier (SID) is PROD. SID is a unique name for an Oracle database instance.
2. Copy data from an existing database (use the starter database whose SID is ORCL) to PROD.
3. Set the PROD database to start automatically when the Windows NT operating system starts.

The following table summarizes the database creation steps and indicates when each step is required, depending on if you want to:

• Replace an existing database (and copy its data to a new database)
• Create a new database (and no other database exists on your system)
• Create an additional database

Database Creation Steps	Replace an Existing Database	Create a New Database (no other database exists)	Create an Additional Database
Step 1: Export an Existing Database	Yes	No	Only if you want to copy data from an existing database to the additional database
Step 2: Delete Database Files	Yes	No	No
Step 3: Modify or Create the INITSID.ORA File	Yes	Yes	Yes
Step 4: Create and Start an Oracle8 Service	No	Yes	Yes
Step 5: Prepare CREATE DATABASE SQL Statement	Yes	Yes	Yes
Step 6: Create a Database	Yes	Yes	Yes
Step 7: Import a Database	Yes	No	Only if you want to import tables and other objects exported from an existing database
Step 8: Update ORACLE_SID in the Registry	No	Yes	Only if you want to change the default SID
Step 9: Back Up the New Database	Yes	Yes	Yes

CAUTION: Database creation is an advanced function. Oracle Corporation recommends that you use the standard starter database and expand it to suit your needs if you are not an experienced user.

Note: Assume all operations are performed in the C:\MYDIR\ directory for examples used in the following sections.

Step 1: Export an Existing Database

To use the Export utility to export all data from an existing database to the new database:

1. Set ORACLE_SID to the database service of the database whose contents you want to export. For example, export the starter database ORCL. Note there are no spaces around the equal sign (=) character.

C:\MYDIR> SET ORACLE_SID=ORCL 

2. Run the Export utility:

C:\MYDIR> 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.

Step 2: Delete Database Files

To delete database files:

1. Shut down the starter database ORCL.

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

2. Delete the following database files located in the ORACLE_HOME\DATABASE directory:
• SYS1ORCL.ORA
• USR1ORCL.ORA
• RBS1ORCL.ORA
• TMP1ORCL.ORA
• LOG1ORCL.ORA
• LOG2ORCL.ORA
• CTL1ORCL.ORA

CAUTION: Do not delete the INITORCL.ORA file, as it will be the basis of the new initialization parameter file INITPROD.ORA.

Step 3: Modify or Create the INITSID.ORA File

If you are replacing the starter database ORCL, copy INITORCL.ORA to INITPROD.ORA and modify the file. You must modify the CONTROL_FILES initialization parameter in the INITPROD.ORA file. Modifying the initialization parameters DB_NAME, GLOBAL_NAMES, and DB_FILES is highly recommended. If you do not have an existing INITORCL.ORA file to copy, create INITPROD.ORA.

Step 4: Create and Start an Oracle8 Service

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.

To create a Windows NT service

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

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

Note that the previously created INITPROD.ORA file is specified, with complete path.

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

C:\MYDIR> SET ORACLE_SID=PROD 

Additional Information: See the section, "Using ORADIM80" in Chapter 4, "Database Tools".

Step 5: Prepare CREATE DATABASE SQL Statement

You can enter the CREATE DATABASE statement directly in Server Manager. Alternatively, you can use a text editor to create a SQL script containing the CREATE DATABASE statement, and then execute the script in Server Manager. In this example, this script is called CREATE_PROD_DB.SQL, located in the C:\MYDIR directory.

The text of the CREATE_PROD_DB.SQL script is given below.

CREATE DATABASE PROD_DB 
          LOGFILE 'C:\MYDIR\DATABASE\LOG1PROD.ORA' SIZE 500K, 
                  'C:\MYDIR\DATABASE\LOG2PROD.ORA' SIZE 500K 
          MAXDATAFILES 100 
          DATAFILE 'C:\MYDIR\DATABASE\SYS1PROD.ORA' SIZE 20M 
          NOARCHIVELOG 
          CHARACTER SET WE8ISO8859P1; 

This statement creates a database where:

Step 6: Create a Database

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:\> NET START 

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

C:\> NET START OracleServicePROD 

2. Make PROD the current SID:

C:\MYDIR> SET ORACLE_SID=PROD 

The second command overrides the setting of the LOCAL environment variable, if it is set in the registry or the AUTOEXEC.BAT file. Without overriding LOCAL, it is possible to receive an ORA-3121, ORA-9352, ORA-12154, or ORA-12203 error in Step 3.

Additional Information: See Oracle8 Server Error Messages for information on the meaning of each error.

3. 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. You should see the message Connected to an idle instance.

4. Start the database in NOMOUNT mode:

SVRMGR> STARTUP NOMOUNT PFILE=ORACLE_HOME\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 must be no space characters around the equal sign (=).

5. Turn on spooling to save the messages and run the previously created CREATE_PROD_DB.SQL script:

SVRMGR> SPOOL CREATE_PROD_DB.LOG 
SVRMGR> @CREATE_PROD_DB.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 CREATE DATABASE script contained syntax errors. Correct them.
• Some of the files to be created by the CREATE DATABASE statement 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 the section, "Operating System Permission Issues" in Appendix G, "Troubleshooting" for more information.

Note: You must correct these problems before attempting to re-create a new database.

6. Create tablespaces and rollback segments to be used by the new database. For an example, see the file ORACLE_HOME\RDBMS80\ADMIN\BUILD_DB.SQL, which shows the commands to duplicate the starter database. It is a good idea to write your own commands in a script file, and execute it in Server Manager, similar to running the CREATE DATABASE script, rather than typing all the commands at the Server Manager prompt.
7. Run the following Oracle provided scripts:

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

The first script generates the data dictionary, the second script installs the objects used by the Oracle8 database's PL/SQL functionality.

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

SVRMGR> SPOOL OFF 

9. Examine the CREATE_PROD_DB.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 CREATE_PROD_DB.LOG log file, see Oracle8 Server Error 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;

Step 7: Import a Database

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

1. Exit Server Manager:

SVRMGR> EXIT 

2. 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.

Step 8: 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.

To 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 

2. Choose the \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE key.
3. Locate the ORACLE_SID parameter on the right side of the registry screen.
4. Double-click the parameter name, and change the data to the new SID, 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, called a value in Windows NT terminology.

1. Go to the Edit menu.
2. Choose the New... item, and choose the String Value subitem.
3. Change the Value Name to ORACLE_SID.
4. Double-click the Value Name, and set the Value Data to PROD.

Step 9: Back Up the New Database

To back up the new database:

1. Shut down the database 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.

2. Using the tool of your choice, back up the database files. Back up the:
• initialization parameter file
• control files
• online redo log files
• 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 12, "Backing Up and Recovering Database Files", Oracle8 Server Concepts, Oracle8 Server Backup and Recovery Guide, and Oracle8 Server Administrator's Guide for more information on archiving and backup/recovery.

Prev Next

Copyright © 1997 Oracle Corporation. All Rights Reserved.

Library Product Contents Index