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

Library

Product

Contents

Index


Prev Next

9
Creating a Database

This chapter describes how to create a new database.

Specific topics discussed are:

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.

Additional Information:

See Oracle8 Installation for Windows NT CD-ROM Insert for information on how to install Oracle Database Assistant.  

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
  3. The Oracle Database Assistant welcome screen appears:

  4. Select Create a database, then click Next.
  5. The following screen appears:

  6. Choose the Typical or Custom option to create a database.
  7. 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

     

  8. Proceed to the last Oracle Database Assistant screen and complete the creation of the Oracle8 database.
  9. 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:

Step 1: Export an Existing Database

Note:

This step is necessary only if you want to copy the contents of an existing database to a new 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.
  2. C:\MYDIR> SET ORACLE_SID=ORCL 
    
  3. Run the Export utility:
  4. 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

Note:

This step is necessary only if you want to replace an existing database.  

To delete database files:

  1. Shut down the starter database ORCL.
  2. C:\MYDIR> ORADIM80 -SHUTDOWN -SID ORCL -USRPWD PASSWORD -SHUTTYPE INST 
    -SHUTMODE I
    
  3. Delete the following database files located in the ORACLE_HOME\DATABASE directory:

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.

Additional Information:

See the Appendix B, "Initialization Parameter Files" for information on how to create an initialization parameter file.  

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. For example,

CONTROL_FILES = (ORACLE_HOME\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 to be executed in "Step 5: Prepare CREATE DATABASE SQL Statement". This name does not need to match the SID of the database service. Oracle Corporation recommends that you give a unique database name to each database. For example, DB_NAME=PROD_DB.

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 Server 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  

Step 4: Create and Start an Oracle8 Service

Note:

Do not perform this step if you are replacing an existing database. It is not necessary to create a new instance if an existing database was installed. You can use the default instance ORCL, if it is available.  

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

  3. Set ORACLE_SID to equal PROD. Note there are no spaces around the equal sign (=) character.
  4. 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.

Additional Information:

See Oracle8 Server SQL Reference for the complete syntax of the CREATE DATABASE statement.  

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:

Parameter  

Description  

PROD_DB  

Is the name of the database (the same name as entered for DB_NAME in the INITPROD.ORA file).  

LOGFILE  

Specifies two (minimum required) online redo log files of 500 KB each in size. It also reserves space for 100 data files in the control file, and creates a single data file of 20 MB in size, to be used for the SYSTEM tablespace.  

MAXDATAFILES  

Sets the maximum number of data files that can ever be used by the new database. If you subsequently want to increase this limit, you must re-create the control files of the database. See the explanation of the CREATE CONTROLFILE statement in Oracle8 Server SQL Reference for more information.

The default value of MAXDATAFILES on Windows NT is 32, and the maximum value is 1022.  

NOARCHIVELOG  

Is the default setting. If you want to enable archiving, change this entry to ARCHIVELOG.  

CHARACTER SET  

WE8ISO8859P1 is the recommended 8-bit character set for Western European languages, which includes English. This 8-bit character set is recommended over the default set US7ASCII, which can handle 7-bit characters only and is therefore of little use for languages other than English. Note the default character set used by Oracle client applications on various Windows operating systems is WE8ISO8859P1.  

Note:

The file names, locations, and sizes in the CREATE_PROD_DB.SQL script are only examples. Your database will have different values to suit your requirements.  

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:
  2. 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 
    
  3. Make PROD the current SID:
  4. 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.  

  5. Start Server Manager from the MS-DOS command prompt, and connect to the database as INTERNAL:
  6. 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.

  7. Start the database in NOMOUNT mode:
  8. 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 (=).

  9. Turn on spooling to save the messages and run the previously created CREATE_PROD_DB.SQL script:
  10. 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:

  11. 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.
  12. Run the following Oracle provided scripts:
  13. 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.

  14. Turn off spooling after all scripts have finished running:
  15. SVRMGR> SPOOL OFF 
    
  16. Examine the CREATE_PROD_DB.LOG file for any errors.
  17. 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:
  2. SVRMGR> EXIT 
    
  3. Run the Import utility:
  4. 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,
  2. C:\MYDIR> REGEDT32 
    
  3. Choose the \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE key.
  4. Locate the ORACLE_SID parameter on the right side of the registry screen.
  5. 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

WARNING:

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

To back up the new database:

  1. Shut down the database and stop the service:
  2. 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.  

  3. Using the tool of your choice, back up the database files. Back up the:
  4. 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
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.

Library

Product

Contents

Index