Oracle8i Administrator's Guide
Release 2 (8.1.6) for Windows NT
A73008-01

Library
Product
Contents
Index

Prev Next

6
Post-Installation Database Creation

This chapter describes how to create a database with Oracle Database Configuration Assistant or the BUILD_DB.SQL script after installing Oracle.

Specific topics discussed are:

Before You Create a Database

Before you create a database, consider the following requirements described below.

Naming Conventions for Oracle Databases

With Oracle8i database, all mounted Oracle databases in a network must have unique database names.

A name is associated with a database at database creation time and stored in its control files. If the database keyword is provided in the CREATE DATABASE statement or when prompted by the Oracle Database Configuration Assistant, that value becomes the name for that database. If not, the program uses the value of the DB_NAME parameter in the INIT.ORA file.

If you attempt to mount two Oracle8i 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 Oracle8i databases on the same computer, but located in different Oracle homes, the following rules apply:

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 only exists for Oracle8i instances. Any Oracle7 instances running simultaneously with an Oracle8i instance are not subject to this restriction.

Note:

The directory path examples in this chapter follow Optimal Flexible Architecture (OFA) guidelines (for example, ORACLE_BASE\ORACLE_HOME\RDBMS\ADMIN). If you specified non-OFA compliant directories during installation, your directory paths will differ. See "OFA and Multiple Oracle Home Configurations" for information. 

Creating Data Files and Log Files on Remote Computers

Although it is possible for Oracle to access database files on remote computers using Universal Naming Convention (UNC), it is not recommended because of performance and network reliability concerns.

UNC is a PC format for specifying the location of resources on a local area network. UNC uses the following format: 

\\SERVER-NAME\SHARED-RESOURCE-PATHNAME

For example, to access the file SYSTEM01.DBF in the directory C:\ORACLE\ORADATA\ORCL on the shared server ARGON, you reference the file as: 

\\ARGON\ORACLE\ORADATA\ORCL\SYSTEM01.DBF

Note that the location of archive log files cannot be specified using UNC. If you set the LOG_ARCHIVE_DEST_n initialization parameter to a UNC specification, the database does not start and you receive the following errors: 

ORA-00256: error occurred in translating archive text string '\meldell\rmdrive'
ORA-09291: sksachk: invalid device specified for archive destination 
OSD-04018: Unable to access the specified directory or device
O/S-Error: (OS 2) The system cannot find the file specified

Ensure that you set the LOG_ARCHIVE_DEST_n initialization parameter to a mapped drive.

Note:

An ORA-00256 error also occurs if you enter:

\\\meldell\rmdrive

or

\\\meldell\\rmdrive

Control files required the additional backslashes for release 8.0.4, but redo log files and data files did not. 

Creating a Database Using Tools

You can choose either of the following tools to create a database:

Use Oracle Database Configuration Assistant to create a database, because it is the easier method.

If you want to create a database using command line tools, you can use the BUILD_DB.SQL script located in ORACLE_BASE\ORACLE_HOME\RDBMS\ADMIN.

Using Oracle Database Configuration Assistant

Oracle Database Configuration Assistant enables you to:

Create a Database

Note:

If you use Oracle Database Configuration Assistant to create a new database in a new multiple Oracle home, the LISTENER.ORA file located in ORACLE_HOME\NETWORK\ADMIN is updated with the SID information. Also a new TNS entry is generated in the TNSNAMES.ORA file located in ORACLE_HOME\NETWORK\ADMIN. 

To create a database using Oracle Database Configuration Assistant:

Note:

Users must have Windows NT Administrator's privileges in order to create an Oracle8i database. If Oracle Database Configuration Assistant is run from an account that is not part of the Administrator group it gives an appropriate warning stating that you don't have administrative privileges to create the database. Log in as a user that is part of the Administrator groupand restart this tool again to create the database. 

  1. Choose Start > Programs > Oracle - HOME_NAME > Database Administration > Database Configuration Assistant.

    The Oracle Database Configuration Assistant Welcome page appears.


  2. Select Create a database, then click Next.

    The following page appears:


    Option  Description 

    Typical 

    Consists of two suboptions:

    • Copy existing database files from the CD

      Automatically installs a Hybrid starter database with the default initialization parameter settings

    • Create new database files

      Asks you several database environment questions before dynamically creating a database

     

    Custom 

    Enables you to customize the creation of your database. This option is only for Oracle database administrators (DBAs) 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. Choose the Typical or Custom option to create a database:

    Both the Typical option (through its Create new database files suboption) and the Custom option enable you to specify the type of environment in which to operate your Oracle8i database:

    Environment  Description 

    Online Transaction Processing (OLTP)  

    Many concurrent users performing numerous transactions requiring rapid access to data. Availability, speed, concurrence, and recoverability are key issues.

    Transactions consist of reading (SELECT statements), writing (INSERT and UPDATE statements), and deleting (DELETE statements) data in database tables.  

    Warehousing

     

    Users perform numerous, complex queries that process large volumes of data. Response time, accuracy, and availability are key issues.

    These queries (typically read-only) range from a simple fetch of a few records to numerous complex queries that sort thousands of records from many different tables. Warehousing environments are also known as Decision Support System (DSS) environments. 

    Multipurpose 

    Both types of applications(OLTP or warehousing) can access this database. 
  4. Respond to instructions on each Oracle Database Configuration Assistant page, then click Next when you are ready to continue to the next page. When you get to the last page, click Finish to start the creation of the Oracle8i database.

Importing Sample Schemas

Sample OLTP and Warehousing database schemas are available on the CD-ROM. After you finish creating your Oracle8i database with Oracle Database Configuration Assistant, you can import the appropriate sample schema.

If you selected multipurpose, you do not need to import a sample schema; one is already provided with your database.

To import an OLTP or DSS sample schema into your Oracle8i database:

  1. Start SQL*Plus: 

    C:\> SQLPLUS
  2. Connect with the SYSTEM account: 

    Enter user-name: SYSTEM/PASSWORD
  3. Create a special user account for importing the appropriate schema: 

    SQL> CREATE USER SAMPLE_USER IDENTIFIED BY PASSWORD;
SQL> GRANT RESOURCE TO SAMPLE_USER;
SQL> GRANT CONNECT TO SAMPLE_USER;

    where SAMPLE_USER is SAMPLEOLTP for the OLTP sample schema or SAMPLESTAR for the DSS sample schema.

  4. Exit SQL*Plus: 

    SQL> EXIT
  5. Go to the ORACLE_BASE\ORACLE_HOME\ASSISTANTS\DBCA\SAMPLES directory on your hard drive.

  6. Import the appropriate schema: 

    C:\ORACLE\ORA81\ASSISTANTS\DBCA\SAMPLES> IMP SAMPLE_USER/PASSWORD 
FILE=SAMPLE.DMP FULL=Y LOG=MYIMP.LOG

    where: 

     

    C:\ORACLE 

    is the ORACLE_BASE directory 

    ORA81 

    is the ORACLE_HOME directory 

    SAMPLE_USER 

    is SAMPLEOLTP for the OLTP sample schema or SAMPLESTAR for the Warehousing sample schema 

    SAMPLE.DMP 

    is SOURCE80.DMP for the OLTP sample schema or TARGET80.DMP for the Warehousing sample schema 

Change Database Configuration

The Change Database Configuration option enables you to perform the following procedures:

Configuring Advanced Replication and Oracle options

This enables an Oracle8i database to support Advanced Replication functionality and the following as-yet-unconfigured options that you installed from your CD-ROM:

These options (if installed during a separate installation from Oracle8i Enterprise Edition) are not automatically configured during installation. If you installed Oracle options through the Oracle8i Enterprise Edition Typical installation type on the CD-ROM, your options were automatically configured for the starter database.

Note:

If you installed Oracle Visual Information Retrieval and its check box is greyed-out, select the check box for Oracle interMedia Image. Doing this makes the check box for Oracle Visual Information Retrieval selectable. This is because Oracle Visual Information Retrieval is dependent upon Oracle interMedia Image. In addition, JServer is a required dependency for Oracle interMedia Image and Oracle Visual Information Retrieval. 

Enabling and Disabling MultiThreaded Server Support

This lets you choose whether to enable or disable multithreaded server support in your Oracle8i database.

Multithreaded server (MTS) support enables multiple 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. No dedicated server process exists for each remaining user process 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.

Additional Information:

See "Multithreaded Server Support".

In addition, see the following guides:

 

Delete a Database

The Delete a Database option of Oracle Database Configuration Assistant lets you quickly and easily delete all database files including the initialization parameter file.

Using BUILD_DB.SQL

This section describes how to create a new database manually using a SQL script. There are a number of ways to create a database depending on if you want to:

The following table summarizes the steps involved in creating a new database for each of these database creation scenarios. Each step is explained in detail in the following subsections.

Perform these tasks...  If you want to... 
Copy an existing database to a new database, then remove the old database  Copy an existing database to a new database, then keep the old database  Create a new database when no other database exists on your system 

Exporting an Existing Database 

Yes 

Only if you want to copy data from the existing database to the new database 

Not applicable 

Deleting Database Files 

Yes 

No 

Not applicable 

Modifying the INIT.ORA File 

Yes 

Yes 

Yes 

Creating and Starting an Oracle Service 

No 

Yes 

Yes 

Putting the CREATE DATABASE Statement in a Script 

Yes 

Yes 

Yes 

Creating a Database 

Yes 

Yes 

Yes 

Importing a Database 

Yes 

Only if you want to import tables and other objects exported from the existing database 

Not applicable 

Updating the ORACLE_SID in the Registry 

No 

Only if you want to change the default SID 

Yes 

Backing Up the New Database 

Yes 

Yes 

Yes 

How to Create a Database

An example is used in the following sections to demonstrate how 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:\ORACLE\ORADATA\ORCL directory) to a new database with a database name and SID of PROD located in the C:\ORACLE\ORADATA\PROD directory.

You will delete the starter database ORCL after you have created the PROD database.

Note:

In this example, ORACLE_BASE is C:\ORACLE. See Chapter 3, "Multiple Oracle Homes and Optimal Flexible Architecture" for more information on ORACLE_BASE. 

Creating Directories

Create the following directories in which to put the administration and database files for the new database PROD:

Exporting an Existing Database

You only need to export an existing database if you want to copy its contents to a new database.

You can invoke the Export Utility by using either parameter mode or interactive mode. However, parameter mode is the recommended mode. Interactive mode provides less functionality than parameter mode and exists for backward compatibility only.

Example 6-1 Parameter Mode

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

Example 6-2 Interactive Mode

C:\> EXP SYSTEM/PASSWORD

Enter only the command EXP SYSTEM/PASSWORD to begin an interactive session and let the Export Utility prompt you for the information it needs.

See Oracle8i Utilities for more information on using the Export Utility.

Note:

If you use parameter mode, the Export Utility considers file names and directory names to be invalid if a blank space is present. Enclose the full path in the FILE= parameter in triple quotes. For example:

FILE="""C:\PROGRAM FILES\EXPORT.DMP"""

or

FILE="""C:\PROGRAM FILES\EXPORT FILE.DMP"""

If the Export Utility is used in interactive mode, the file name or directory name can contain a space without quotes. 

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 that there are no spaces around the equal sign (=) character. 

    C:\> SET ORACLE_SID=ORCL
  2. Start the Export Utility from the MS-DOS command prompt: 

    C:\> EXP 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. All messages from the Export Utility are logged in the file MYEXP.LOG.

Deleting Database Files

Deleting database files is only required when you want to copy an existing database to a new database to replace the old database. In the following example, you delete the database files of the starter database ORCL.

To delete database files:

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

    C:\> ORADIM -SHUTDOWN -SID ORCL -USRPWD PASSWORD -SHUTTYPE INST
-SHUTMODE I
  2. Delete the following database files located in the C:\ORACLE\ORADATA\ORCL directory:

    • CONTROL01.CTL

    • CONTROL02.CTL

    • CONTROL03.CTL

    • INDX01.DBF

    • DR01.DBF

    • RBS01.DBF

    • SYSTEM01.DBF

    • TEMP01.DBF

    • USERS01.DBF

    • REDO01.LOG

    • REDO02.LOG

    • REDO03.LOG

    • TOOLS01.DBF

Modifying the INIT.ORA File

If you are using the starter database ORCL as the basis for your new database, copy INIT.ORA file: 

C:\ORACLE\AORACLE_HOME\DMIN\ORCL\PFILE\INIT.ORA

to 

C:\ORACLE\ORACLE_HOME\ADMIN\PROD\PFILE\INIT.ORA

and modify the file as described in this section.

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 new INIT.ORA file. However, you can use the sample initialization parameter file INITSMPL.ORA provided in the ORACLE_BASE\ORACLE_HOME\ADMIN\SAMPLE\PFILE directory as the basis for the INIT.ORA file for the PROD database.

If you use INITSMPL.ORA as the basis for the INIT.ORA file, you must modify the following initialization parameters in the INIT.ORA file, or you will not be able to start the PROD database:

Modifying the DB_FILES initialization parameter is recommended to optimize performance.

Initialization Parameter  Modification Instructions. 

DB_NAME  

This parameter indicates the name of the database, and must match the name used in the CREATE DATABASE statement in "Putting the CREATE DATABASE Statement in a Script". You give a unique database name to each database. You can use up to eight characters for a database name. The name does not need to match the SID of the database service.

Set this parameter to:

DB_NAME=PROD.DOMAIN 

INSTANCE_NAME  

Set this parameter to:

INSTANCE_NAME=PROD.DOMAIN 

SERVICE_NAMES  

Set this parameter to:

SERVICE_NAMES=PROD.DOMAIN 

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 run the CREATE DATABASE statement. Ensure that you specify the complete path and file name, including drive letter.

Set this parameter to:

CONTROL_FILES = ("C:\ORACLE\ORADATA\PROD\CONTROL01.CTL", "C:\ORACLE\ORADATA\PROD\CONTROL02.CTL", "C:\ORACLE\ORADATA\PROD\CONTROL03.CTL") 

BACKGROUND_DUMP_DEST  

Set this parameter to:

BACKGROUND_DUMP_DEST = C:\ORACLE\ADMIN\PROD\BDUMP 

USER_DUMP_DEST  

Set this parameter to:

USER_DUMP_DEST = C:\ORACLE\ADMIN\PROD\UDUMP 

DB_FILES  

Set this 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 

See Also::

Appendix B, "Oracle8i Database Specifications for Windows NT" and Oracle8i Reference for information on other initialization parameters that you may want to add or modify. 

Creating and Starting an Oracle Service

You only need to create and start an Oracle service if you want to do one of the following:

Before you create the database, first create a Windows NT service to run the database. This service is the Oracle8i database process, ORACLE.EXE, installed in the form of a Windows NT service.

Use ORADIM to create the service. After it has been created, the service starts automatically. See "Using ORADIM to Administer an Oracle Instance" for information on how to use ORADIM.

To create and start an Oracle service:

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

    C:\> ORADIM -NEW -SID PROD -INTPWD PASSWORD -STARTMODE MANUAL
-PFILE C:\ORACLE\ADMIN\PROD\PFILE\INIT.ORA

    Note that the previously-created INIT.ORA file is specified, with complete path, including drive name. 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 that there are no spaces around the equal sign (=) character: 

    C:\> SET ORACLE_SID=PROD

Putting the 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.

Open the BUILD_DB.SQL script located in C:\ORACLE\ORA81\RDBMS\ADMIN and save it as BUILD_PROD.SQL.

This file becomes the basis for your script.

Note:

The following example uses the BUILD_DB.SQL script to create a database. You can also use the BUILDALL.SQL script to create a database. BUILDALL.SQL not only creates the database by calling BUILD_DB.SQL but also runs many other scripts such as CATALOG.SQL, CATSNMP.SQL, SCOTT.SQL, and COMDEMO.SQL. 

To prepare the CREATE DATABASE script:

Make the following changes to the BUILD_PROD.SQL script.

  1. Set PFILE so it points to the C:\ORACLE\ADMIN\PROD\PFILE\INIT.ORA initialization file.

  2. Change CREATE DATABASE SAMPLE to CREATE DATABASE PROD.

  3. Change all occurrences of SAMPLE to PROD. For example, change C:\ORACLE\ORADATA\SAMPLE\REDO01.LOG to C:\ORACLE\ORADATA\PROD\REDO01.LOG

The following is the sample BUILD_DB.SQL script. Areas that you must modify to create a database called PROD are highlighted. 

--
-- This file must be run out of the directory containing the
-- initialization file.

startup nomount pfile=C:\Oracle\ADMIN\SAMPLE\pfile\initsmpl.ora

--  Create database

create database SAMPLE
    controlfile reuse
    logfile 'C:\Oracle\ORADATA\SAMPLE\redo01.log' size 1M reuse,
            'C:\Oracle\ORADATA\SAMPLE\redo02.log' size 1M reuse,
            'C:\Oracle\ORADATA\SAMPLE\redo03.log' size 1M reuse
                datafile 'C:\Oracle\ORADATA\SAMPLE\system01.dbf' size 10M reuse 
autoextend on
next 10M maxsize 200M 
character set WE8ISO8859P1;

create rollback segment rb_temp storage (initial 100 k next 250 k);

-- Create additional tablespaces ...

-- USERs: Create user sets this as the default tablespace
-- TEMP: Create user sets this as the temporary tablespace
-- RBS: For rollback segments

create tablespace users
    datafile 'C:\Oracle\ORADATA\SAMPLE\users01.dbf' size 3M reuse autoextend on
      next 5M maxsize 150M;
create tablespace rbs
    datafile 'C:\Oracle\ORADATA\SAMPLE\rbs01.dbf' size 5M reuse autoextend on
      next 5M maxsize 150M;
create tablespace temp
    datafile 'C:\Oracle\ORADATA\SAMPLE\temp01.dbf' size 2M reuse autoextend on
      next 5M maxsize 150M;
create tablespace oem_repository
    datafile 'C:\Oracle\ORADATA\SAMPLE\oemrep01.dbf' size 3M reuse autoextend on
      next 5M maxsize 150M;
create tablespace indx;
    datafile 'C:\Oracle\ORADATA\SAMPLE\indx01.dbf' size 2M reuse autoextend on
      next 5M maxsize 150M;
--      next 5M maxsize 150M;
alter rollback segment rb_temp online;

-- Change the SYSTEM users' password, default tablespace and
-- temporary tablespace.

alter user system temporary tablespace temp;
alter user system default tablespace users;

-- 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 250K)
  tablespace rbs;
create public rollback segment rb2 storage(initial 50K next 250K)
  tablespace rbs;
create public rollback segment rb3 storage(initial 50K next 250K)
  tablespace rbs;
create public rollback segment rb4 storage(initial 50K next 250K)
  tablespace rbs;
create public rollback segment rb5 storage(initial 50K next 250K)
  tablespace rbs;
create public rollback segment rb6 storage(initial 50K next 250K)
  tablespace rbs;
create public rollback segment rb7 storage(initial 50K next 250K)
  tablespace rbs;
create public rollback segment rb8 storage(initial 50K next 250K)
  tablespace rbs;
create public rollback segment rb9 storage(initial 50K next 250K)
  tablespace rbs;
create public rollback segment rb10 storage(initial 50K next 250K)
  tablespace rbs;
create public rollback segment rb11 storage(initial 50K next 250K)
  tablespace rbs;
create public rollback segment rb12 storage(initial 50K next 250K)
  tablespace rbs;
create public rollback segment rb13 storage(initial 50K next 250K)
  tablespace rbs;
create public rollback segment rb14 storage(initial 50K next 250K)
  tablespace rbs;
create public rollback segment rb15 storage(initial 50K next 250K)
  tablespace rbs;
create public rollback segment rb16 storage(initial 50K next 250K)
  tablespace rbs;

You will run this script at the SQL*Plus prompt in "Creating a Database".

Additional Information:

If creating a tablespace in a raw partition, modify the data file names with a naming convention of \\.\DRIVE_LETTER: or \\.\SYMBOLIC LINK NAME. See Appendix D, "Storing Tablespaces on Raw Partitions" for more information on storing tablespaces in raw partitions. 

Creating a Database

To use the BUILD_PROD.SQL script to create a database:

  1. Verify that the service is started in the Windows NT Control Panel. In this example, the service name is OracleServicePROD, and its status column must display Started. If not, select the service name and click Start.

    You can also 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:\> SET ORACLE_SID=PROD
  3. Start SQL*Plus from the MS-DOS command prompt, and connect to the database as INTERNAL: 

    C:\> SQLPLUS
SQL> CONNECT INTERNAL/PASSWORD

    The password is the one that you used to create the service, with the ORADIM -NEW command in "Creating and Starting an Oracle Service".

    The message Connected appears.

  4. Turn on spooling to save the messages: 

    SQL> SPOOL BUILD_PROD.LOG
  5. Run the BUILD_PROD.SQL script that you created in "Putting the CREATE DATABASE Statement in a Script": 

    SQL> @C:\ORACLE\ORA81\RDBMS\ADMIN\BUILD_PROD.SQL;

    If the database is created successfully, the instance is started and the message Statement processed appears numerous times.

    If you receive any errors, there are three possible causes, as illustrated below.

    Cause  Solution 

    The BUILD_PROD.SQL script contains 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. 

    An error occurred at the operating system level, such as a file or directory permission problem. 

    You should have received a series of errors in SQL*Plus, 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, do either of the following:

    From the MS-DOS command prompt, enter:

    C:\> NET HELPMSG n

    or

    From the SQL*Plus prompt, enter:

    SQL> HOST NET HELPMSG n

    where n is the operating system error number. 

    You must correct these problems before making another attempt to create a database.

  6. Run the CATALOG.SQL script to create the data dictionary: 

    SQL> @C:\ORACLE\ORA81\RDBMS\ADMIN\CATALOG.SQL;

    Note:

    You may see messages such as ORA-01432: public synonym to be dropped does not exist while the CATALOG.SQL, CATPROC.SQL, and CATREP.SQL scripts are running. These are information messages and are intended to occur while creating a new database.

    If you see any unusual errors while examining the BUILD_PROD.LOG log file in step 10, see Oracle8i Error Messages for suggested actions. 

  7. Run the CATPROC.SQL script to install the objects used by the Oracle8i database's PL/SQL functionality: 

    SQL> @C:\ORACLE\ORA81\RDBMS\ADMIN\CATPROC.SQL;
  8. Run the CATREP.SQL script if you want Advanced Replication functionality with the new database. Ensure that the rollback segments are large enough and are online before you run CATREP.SQL. 

    SQL> @C:\ORACLE\ORA81\RDBMS\ADMIN\CATREP.SQL;
  9. Turn off spooling: 

    SQL> SPOOL OFF
  10. Examine the BUILD_PROD.LOG file for any unusual errors.

    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: 

    SQL> ALTER USER SYS IDENTIFIED BY NEW_SYS_PASSWORD; 
SQL> ALTER USER SYSTEM IDENTIFIED BY NEW_SYSTEM_PASSWORD;
     

  11. Exit SQL*Plus: 

    SQL> EXIT
  12. Run ORADIM from the MS-DOS command prompt to set the database to start automatically when you start the computer: 

    C:\> ORADIM -EDIT -SID PROD -STARTMODE AUTO

Importing a Database

You can import the full export created in "Exporting an Existing Database" into the new database.

You can also invoke the Import Utility, using parameter mode or interactive mode. Parameter mode is recommended, because interactive mode provides less functionality. Interactive mode exists solely for backward compatibility.

Example 6-3 Parameter Mode

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

Example 6-4 Interactive Mode

C:\> IMP SYSTEM/PASSWORD

Enter only the command IMP SYSTEM/PASSWORD to begin an interactive session and let the Import Utility prompt you for the information it needs.

See Oracle8i Utilities for more information on using the Import Utility.

Note:

If you use parameter mode, the Import Utility considers file names and directory names to be invalid if there is a blank space. Enclose the full path in the FILE= parameter in triple quotes. For example:

FILE="""C:\PROGRAM FILES\EXPORT.DMP"""

or

FILE="""C:\PROGRAM FILES\EXPORT FILE.DMP"""

If you use the Import Utility in interactive mode, the file name or directory name can contain a space without quotes. 

To import a database:

Updating the 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. Start the registry editor at the MS-DOS command prompt: 

    C:\> REGEDT32

    The registry editor window appears.

  2. Choose the \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 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\HOMEID, where ID is the unique number identifying the Oracle home.

    See Also:

    See Appendix C, "Oracle8i Configuration Parameters and the Registry" for more information on the subkey locations for multiple Oracle homes. 

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

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

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

To create the ORACLE_SID parameter:

  1. Choose Add Value from the Edit menu.

    The Add Value dialog box appears:


  2. Enter ORACLE_SID in the Value Name text box.

  3. Select REG_EXPAND_SZ (for an expandable string) in the Data Type list box.

  4. Click OK.

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


  5. Enter PROD in the String Editor dialog box.

  6. Click OK.

    The registry editor adds the ORACLE_SID parameter.

  7. Choose Exit from the Registry menu.

    The registry exits.

Backing 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 loss of data.  

To back up the new database:

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

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

    WARNING:

    Although ORADIM 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.

    Database files consist of the initialization parameter file, control files, online redo log files, and data files.

    When the backup is complete, you can start the database again, create users and objects, if necessary, make any other changes, and use the database.

    Be sure to back up the database backup after making any significant changes to the database, such as switching the ARCHIVELOG mode, or adding a tablespace or data file.

    Warning::

    Do not store database files on a compressed drive. This can result in write errors and decreased performance. 

Using ORADIM to Administer an Oracle Instance

ORADIM is a command line tool that is only available with the Oracle8i database.

You only need to use ORADIM if you are manually creating, deleting, or modifying databases. Oracle Database Configuration Assistant is an easier tool to use for this purpose.

ORADIM and Oracle Database Configuration Assistant perform similar tasks. The following table compares what you can do with these tools.

ORADIM  Oracle Database Configuration Assistant 
  • Can create, start, stop, modify, and delete instances (and not any associated database files) at the MS-DOS command prompt.

 
  • Can create and delete databases (and their associated instances and services). Note that the assistant cannot start or stop existing databases.

 
  • Can be used to modify an instance. You can modify an existing instance to change such values as the instance name, the password, the startup mode, or the shutdown mode.

 
  • Cannot be used to modify an instance.

 
  • Only creates the password file and the related service. The database (that is, the database files) is not created.

 
  • Creates the database, the associated instance, the service, and the password file.

 

When you use ORADIM, a log file called ORADIM.LOG opens in ORACLE_BASE\ORACLE_HOME\DATABASE, or in the directory specified by the ORA_CWD registry parameter. All operations, whether successful and failed, are logged in this file. You must check this file to verify the success of an operation.

The following sections describe the ORADIM commands and parameters. Note that each command is preceded by a dash (-).

To get a list of ORADIM parameters and descriptions... 

Use this syntax: 

 
ORADIM -? | -H | -HELP

Note: Specifying ORADIM without any options also returns a list of ORADIM parameters and descriptions.  

Example: 

C:\> ORADIM -? 

Creating an Instance

To create an instance... 

Use this syntax: 

ORADIM -NEW -SID SID | -SRVC SERVICE_NAME [-INTPWD INTERNAL_PWD] - SHUTTYPE SRVC | INST | SRVC, INST

[-MAXUSERS NUMBER][-STARTMODE AUTO | MANUAL][-PFILE FILENAME]  

Example to create an instance called PROD: 

C:\> ORADIM -NEW -SID PROD -INTPWD MYPASSWORD1 -STARTMODE AUTO -PFILE C:\ORACLE\ADMIN\PROD\PFILE\INIT.ORA  

Syntax description: 
  • -NEW

 

Indicates that you want to create a new instance. This is a mandatory parameter. 

 
  • -SID SID

 

The name of the instance you want to create. You must specify either this parameter or the -SRVC parameter described below. 

 
  • -SRVC SERVICE_NAME

 

The name of the service you want to create (OracleServiceSID). You must specify either this parameter or the -SID parameter described above. 

 
  • -INTPWD INTERNAL_PWD

 

The password for the INTERNAL account. The -INTPWD option is not required. If you do not specify it, operating system authentication is used, and no password is required. See "Automatically Enabling Operating System Authentication During Installation" for a description of features. 

 
  • -MAXUSERS NUMBER

 

The number of users defined in the password file. The default is 5. 

 
  • -STARTMODE AUTO, MANUAL

 

Indicates whether to start the instance automatically or manually at startup. The default setting is MANUAL. 

 
  • -PFILE FILENAME

 

The INIT.ORA file to be used with this instance. Ensure that you specify the complete pathname of this file, including drive letter. 

 
  • -SHUTTYPE SRVC, INST

 

Indicates whether to stop the service or the instance. Both can be specified. This is a mandatory parameter. 

Starting an Instance

To start an instance... 

Use this syntax: 

ORADIM -STARTUP -SID SID [-USRPWD USER_PWD] [-STARTTYPE SRVC | INST | SRVC, INST] [-PFILE FILENAME] 

Example to start an instance called PUMA: 

C:\> ORADIM -STARTUP -SID PUMA -STARTTYPE SRVC -PFILE C:\ORACLE\ADMIN\PROD\PFILE\INIT.ORA  

Syntax description: 
  • -STARTUP

 

Indicates that you want to start an instance that already exists. This is a mandatory parameter. 

 
  • -SID SID

 

The name of the instance you want to start. This is a mandatory parameter. 

 
  • -USERPWD USER_PWD

 

The password. 

 
  • -STARTTYPE SRVC, INST

 

Indicates whether to start the service or the instance. One or both values can be specified. If not specified, the registry is checked for the current setting. 

Stopping an Instance

To stop an instance: 

Use this syntax: 

 
ORADIM -SHUTDOWN -SID SID  [-USRPWD USER_PWD] [-SHUTTYPE SRVC | INST | SRVC, 
INST] [-SHUTMODE A | I | N]
 

Example to stop an instance called PUMA: 

C:\> ORADIM -SHUTDOWN -SID PUMA -SHUTTYPE SRVC INST 

Syntax description: 
  • -SHUTDOWN

 

Indicates that you want to stop an instance. This is a mandatory parameter. 

 
  • -SID SID

 

The name of the instance you want to stop. This is a mandatory parameter. 

 
  • -USERPWD USER_PWD

 

The password. 

 
  • -SHUTTYPE SRVC, INST

 

Indicates whether to stop the service or the instance. One or both values can be specified. If not specified, the registry is checked for the current setting. 

 
  • -SHUTMODE A, I, N

 

Specifications on how to stop an instance; A indicates abort mode, I indicates immediate mode, and N indicates normal mode. This is an optional parameter. If you do not specify how to stop an instance, normal is the default mode. 

Modifying an Instance

To modify an instance... 

Use this syntax: 

 
ORADIM -EDIT -SID SID [-NEWSID NEWSID] [-INTPWD INTERNAL_PWD]

[-STARTMODE AUTO | MANUAL][-PFILE FILENAME]
 

Example to modify an instance called PROD: 

C:\> ORADIM -EDIT -SID PROD -NEWSID LYNX -INTPWD MYCAT123 -STARTMODE AUTO -PFILE C:\ORACLE\ADMIN\LYNX\PFILE\INIT.ORA 

Note: 

You can modify an existing instance, in this example PROD, to change such values as the instance name, the password, the startup mode, and the number of users. 

Syntax description: 
  • -EDIT

 

Indicates that you want to modify an instance. This is a mandatory parameter. 

 
  • -SID SID

 

The name of the instance you want to modify. This is a mandatory parameter. 

 
  • -NEWSID NEWSID

 

The new instance name. This is an optional parameter.  

 
  • -INTPWD INTERNAL_PWD

 

The password for the INTERNAL account.

Note: This parameter cannot be used to change the password, as it does not overwrite the existing password file. It can only create a new password file when none already exists. To create a new password file, use ORAPWD, or delete the Oracle8i services (this action implicitly deletes the associated password file) and then recreate the Oracle8i services (this action implicitly creates the associated password file). See "Password Utility (ORAPWD)" in Chapter 2, "Database Tools Overview". 

 
  • -STARTMODE AUTO, MANUAL

 

Indicates whether to start the instance automatically or manually at startup. The default setting is MANUAL. 

 
  • -PFILE FILENAME

 

The INIT.ORA file to be used with this instance. Ensure that you specify the complete pathname of this file, including drive letter. 

Prev Next
Oracle
Copyright © 2000 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index