Oracle7 Getting Started for Windows NT

Library

Product

Contents

Index

Creating a New Database

Steps to Create a Database
Creating a New Database with NT Instance Manager GUI

Steps to Create a Database

This section describes the steps to follow when creating your own database. In order to help you understand the database creation process, an example is used to demonstrate the various commands involved. In this example:

Create a new database whose SID is PROD.
Copy existing data in the starter database (whose SID is ORCL) to PROD.
Set the PROD database to start automatically when the Windows NT operating system starts.

Most steps use command line commands in this example. Alternate methods using GUI tools are sometimes mentioned. In particular, see the section "Creating a New Database with NT Instance Manager GUI" at the end of this chapter.

The following table summarizes database creation steps and explains when each step is required, depending on how you create a new database.

Perform If You Want to... Replace the Starter Database Did not Install the Starter Database Create an Additional Database

Export Your Current Database

YES

NO

Only if you want to copy data from your current database to the additional database. You may not need to do this.

Delete Relevant Database Files

YES

NO

NO

Modify the Initialization Parameter File

YES

YES

YES

Create and Start an Oracle7 Database Service

NO

YES

YES

Prepare CREATE DATABASE SQL Script

YES

YES

YES

Create a Database

YES

YES

YES

Import a Database

YES

NO

Only if you want to import database tables and other objects exported from another database. You may not need to do this.

Update ORACLE_SID in the Windows NT Registry

NO

YES

Only if you want to change the default SID.

Back Up the New Database

YES

YES

YES

Each database creation step is described in detail in the following sections.

CAUTION Database creation is an advanced function. Oracle Corporation recommends that you use the 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.

Export Your Current Database

Note This step is necessary only if you want to duplicate the contents of an existing database to a new database.

Use the Export utility to export all data from the current database to the new database.

Set ORACLE_SID to the database service of the database whose contents you want to export. For example, export ORCL. Note there are no spaces around the equal sign (=) character.
```
C:\MYDIR> SET ORACLE_SID=ORCL 
```
Run the Export utility:
```
C:\MYDIR> EXP73 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 Relevant Database Files

Note This step is necessary only if you want to replace the starter database.

Shut down the starter database.

C:\MYDIR> ORADIM73 -SHUTDOWN -SID ORCL -USRPWD password -SHUTTYPE INST -SHUTMODE I

Delete the following database files located in the ORANT\DATABASE directory:

Modify the Initialization Parameter File

If you are replacing the starter database, modify the INITORCL.ORA file. If you are creating a new database, copy INITORCL.ORA to INITPROD.ORA and modify the new file.

Note You must modify the CONTROL_FILES initialization parameter. Modifying the initialization parameters DB_NAME, GLOBAL_NAMES, and DB_FILES is highly recommended.

Initialization parameter How to Modify...
CONTROL_FILES

This parameter lists the control files of the database. You should not have the control files on your file system at this point because the control files are created when you later create the database using the CREATE DATABASE statement.
Ensure you specify the complete path and file name. For example,
CONTROL_FILES = (C:\ORANT\DATABASE\CTL1PROD.ORA,
C:\ORANT\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 a later step. This name does not have 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 Oracle7 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

Additional Information If you plan on using the Advanced Replication Option with the new database, see the section "Configuring Advanced Replication Option" in the chapter "Post-installation Tasks". For more information on each initialization parameter, see Oracle7 Server SQL Reference.

Create and Start an Oracle7 Database Service

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

Before you create the database, you must first create a Windows NT service to run the database. This service is the Oracle7 database process, ORACLE73.EXE, installed in the form of a Windows NT service. Use Instance Manager command line mode to create the service. Upon creation, the service starts automatically.

Run Instance Manager from the command line.
```
C:\MYDIR> ORADIM73 -NEW -SID PROD -INTPWD password -STARTMODE AUTO 
```
```
-PFILE C:\ORANT\DATABASE\INITPROD.ORA 
```
Note that the previously created INITPROD.ORA file is specified, with complete path.

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

C:\MYDIR> SET ORACLE_SID=PROD  



 Additional Information

Refer to the section "Using Instance Manager from the Command Line" in the chapter "Using Tools and Utilities" for instructions on using Instance Manager in command line mode and the online help for Instance Manager in GUI mode.

Prepare CREATE DATABASE SQL Script

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 commands, 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 The complete syntax of the CREATE DATABASE statement is in Oracle7 Server SQL Reference.

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

CREATE DATABASE PROD_DB

	  LOGFILE 'C:\ORANT\DATABASE\LOG1PROD.ORA' SIZE 500K,

	          'C:\ORANT\DATABASE\LOG2PROD.ORA' SIZE 500K

	  MAXDATAFILES 100

          DATAFILE 'C:\ORANT\DATABASE\SYS1PROD.ORA' SIZE 20M

	  NOARCHIVELOG

	  CHARACTER SET WE8ISO8859P1;

This statement creates a database where:

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

Create a Database

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 on the service name and choose the Start button to start the service. Alternatively, you can check the status of the service by entering the following at the command prompt:

NET START
You will see the list of all Windows NT services currently running on the system. If OracleServicePROD is missing from the list, enter:

NET START OracleServicePROD
Make PROD the current SID:
```
C:\MYDIR> SET ORACLE_SID=PROD 
```
```
C:\MYDIR> SET LOCAL=2:PROD 
```
The second command overrides the setting of the LOCAL environment variable, if it is set in the Windows NT Registry or the AUTOEXEC.BAT file. Without overriding LOCAL, it is possible you will receive an ORA-3121, ORA-9352, ORA-12154, or ORA-12203 error in the next step. See Oracle7 Server Messages for information on the meaning of each error.
Start Server Manager from the command prompt and connect to the database as INTERNAL:
```
C:\MYDIR> SVRMGR23 
```
```
SVRMGR> CONNECT INTERNAL/password 
```
The password is the one you previously used to create the service, with the ORADIM73 -NEW command. You should see the message Connected to an idle instance.
Start the database in NOMOUNT mode:
```
SVRMGR> STARTUP NOMOUNT
```
```
PFILE=C:\ORANT\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 should be no space characters around the equal sign (=).
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, you must specify the complete path in front of the file name.
If the database is created successfully, you will see the message Statement processed. If you receive any errors, there are three possible causes:
Create tablespaces and rollback segments to be used by the new database. For an example, see the file C:\ORANT\RDBMS73\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.
Run the following Oracle provided scripts:
```
SVRMGR> @C:\ORANT\RDBMS73\ADMIN\CATALOG.SQL 
```
```
SVRMGR> @C:\ORANT\RDBMS73\ADMIN\CATPROC.SQL 
```
The first script generates the data dictionary, the second script installs the objects used by Oracle7 database's PL/SQL functionality.
Run the following script if you want to use Advanced Replication Option functionality with the new database:
```
SVRMGR> @C:\ORANT\RDBMS73\ADMIN\CATREP.SQL 
```
Note that CATREP.SQL requires an hour to run.
Turn off spooling after all scripts have finished running:
```
SVRMGR> SPOOL OFF 
```

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 Oracle7 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, you should 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 Your Current Database" into the new database.

Exit Server Manager:
```
SVRMGR> EXIT 
```

Run the Import utility:

C:\MYDIR> IMP73 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 Windows NT 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 Windows NT Registry.

Invoke the Registry Editor at the command prompt by entering REGEDT32 for Windows NT 3.51, or REGEDIT for Windows NT 4.0. For example,
```
C:\MYDIR> REGEDT32 
```
Go to the \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE directory.
Locate the ORACLE_SID parameter on the right side of the registry screen.
Double-click on the parameter, 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.

If you have Windows NT 3.51:

Go to the Edit menu, and choose the Add Value... item.
The Add Value dialog box appears.
Enter ORACLE_SID in the Value Name field and REG_EXPAND_SZ in the Data Type field.
Click OK.
Set the String to PROD in the String Editor dialog box.

If you have Windows NT 4.0:

Go to the Edit menu.
Choose the New... item, and choose the String Value sub-item.
Change the Value Name to ORACLE_SID.
Double click on the Value Name, and set the Value Data to PROD.

Back Up the New Database

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

Shutdown the database and stop the service:
```
C:\MYDIR> ORADIM73 -SHUTDOWN -SID PROD -USRPWD password 
```
```
-SHUTTYPE SRVC,INST -SHUTMODE I 
```
Since the Instance Manager returns the prompt immediately, you must wait for the database and the service to stop completely. Wait until the Control Panel indicates the OracleServicePROD service has stopped.
Using the tool of your choice, back up the database files. You should back up the initialization parameter file, the control files, the online redo log files, and the 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 Oracle7 Server Concepts and Oracle7 Server Administrator's Guide for more information on archiving and backup/recovery.

Creating a New Database with NT Instance Manager GUI

The GUI mode of Instance Manager (ORADIM73.EXE) provides a graphical interface to perform most of the tasks described in the previous sections.

While some users may find the graphical interface easier to use than Instance Manager command line, keep in mind that command line gives greater control over the customizing of the new database, as well as providing you with a script file that documents the exact CREATE DATABASE statement used to create the database.

When you click on the New button in Instance Manager GUI, you will see a dialog box with many fields. This dialog box, and another one which pops up when you click on the Advanced button provide a graphical interface to write the CREATE DATABASE statement. Consult the online help and the syntax description of the CREATE DATABASE statement in Oracle7 Server SQL Reference to find out what each field of the dialog boxes is used for.

When you have entered the correct entries in all fields and have clicked the OK button, Instance Manager GUI:

creates the services for the new database
executes the generated CREATE DATABASE statement
runs CATALOG.SQL and CATPROC.SQL scripts

You must separately create custom tablespaces and rollback segments, and run CATREP.SQL if you intend to use the Advanced Replication Option. The rest of the steps and consideration are the same as when using Instance Manager command line.

Prev Next

Library

Product

Contents

Index

Perform If You Want to...	Replace the Starter Database	Did not Install the Starter Database	Create an Additional Database
Export Your Current Database	YES	NO	Only if you want to copy data from your current database to the additional database. You may not need to do this.
Delete Relevant Database Files	YES	NO	NO
Modify the Initialization Parameter File	YES	YES	YES
Create and Start an Oracle7 Database Service	NO	YES	YES
Prepare CREATE DATABASE SQL Script	YES	YES	YES
Create a Database	YES	YES	YES
Import a Database	YES	NO	Only if you want to import database tables and other objects exported from another database. You may not need to do this.
Update ORACLE_SID in the Windows NT Registry	NO	YES	Only if you want to change the default SID.
Back Up the New Database	YES	YES	YES

Initialization parameter	How to Modify...
CONTROL_FILES	This parameter lists the control files of the database. You should not have the control files on your file system at this point because the control files are created when you later create the database using the CREATE DATABASE statement. Ensure you specify the complete path and file name. For example, CONTROL_FILES = (C:\ORANT\DATABASE\CTL1PROD.ORA, C:\ORANT\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 a later step. This name does not have 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 Oracle7 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

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

Creating a New Database

Steps to Create a Database

Export Your Current Database Note This step is necessary only if you want to duplicate the contents of an existing database to a new database.

Delete Relevant Database Files Note This step is necessary only if you want to replace the starter database.

Modify the Initialization Parameter File

Create and Start an Oracle7 Database Service Note Skip this step if you are replacing the starter database. It is not necessary to create a new instance if the starter database was installed. You can use the default instance ORCL, if it is available.

Prepare CREATE DATABASE SQL Script

Create a Database

Import a Database

Update ORACLE_SID in the Windows NT Registry

Back Up the New Database WARNING If anything goes wrong while operating the new database without a backup, you will have to repeat the database creation procedure. Back up your database now to prevent such a disaster.

Creating a New Database with NT Instance Manager GUI

Export Your Current Database

Note This step is necessary only if you want to duplicate the contents of an existing database to a new database.

Delete Relevant Database Files

Note This step is necessary only if you want to replace the starter database.

Create and Start an Oracle7 Database Service

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

Back Up the New Database

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