Oracle9i Database Administrator's Guide Release 1 (9.0.1) for Windows Part Number A90164-01 |
|
This chapter describes how to create a database manually or by using the Oracle Database Configuration Assistant after installing Oracle.
This chapter contains these topics:
Before you create a database, consider the following requirements.
All mounted Oracle databases in a network must have unique database names. A name is associated with a database at 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 you attempt to mount two Oracle9i databases with the same database name, you receive the following error during the mounting of the second database:
ORA-01102: cannot mount database in EXCLUSIVE mode
If there are two or more Oracle9i 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 re-create your control file(s) and specify a new database name. This restriction only exists for Oracle8i and later releases. Any Oracle7 instances running simultaneously with an Oracle9i 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\ |
Although it is possible for Oracle to access database files on remote computers using the 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-path-name
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: Control files required the additional backslashes for release 8.0.4, but redo log files and datafiles did not. |
You can choose either of the following methods to create a database:
Oracle Corporation recommends you use the Oracle Database Configuration Assistant to create a database, because it is the easier method.
If you prefer you can also create a database using command line tools.
Use the Database Configuration Assistant (DBCA) to register a database running in a member server or workstation in Active Directory on a Windows 2000 domain from a member server or workstation.
If the database service is running on a server, make sure everyone
is a member of Pre Windows 2000 Compatible
domain group. If everyone
is not a member of this group, add the username/computer name (LocalSystem) that the database service is running to the Pre Windows 2000 Compatible
domain group. If this is not done, the database on the member server will randomly get ACCESS DENIED errors when accessing Active Directory.
The database service on the server has to be running as a LocalSystem or domain user, for Database Configuration Assistant to successfully register the database with Active Directory. If the database is running as a local user, then trying to register the database with Active Directory using Database Configuration Assistant fails, as this user cannot logon to Active Directory. After successfully registering with the directory using the Database Configuration Assistant, if the database service is running as a LocalSystem, then manually add the computer name to the Access Control List of the OracleDBSecurity container (in Active Directory) with read permissions on OracleDBSecurity container. If the database service is running as a domain user, then the username should be manually added to the Access Control List of the OracleDBSecurity container (in Active Directory) with read permissions on OracleDBSecurity Container. If this is not done, then you may not be able to use the Active Directory to get enterprise roles.
Oracle Database Configuration Assistant enables you to:
Note:
If you use Oracle Database Configuration Assistant to create a new database in a new Oracle home, the listener.ora file located in |
To create a database using Oracle Database Configuration Assistant:
Note: Users must have Windows NT Administrator's privileges in order to create an Oracle9i database. If Oracle Database Configuration Assistant is run from a user account that is not part of the Administrator's group, it displays a warning stating that you do not have administrative privileges to create the database. Log in as a user that is part of the Administrator's group and restart this tool to create the database. |
The Oracle Database Configuration Assistant Welcome page appears.
The following window appears:
name.domain
and an Oracle System Identifier (SID). Choose Next.
Choose Next.
A Custom installation lets you specify initialization parameter values and other options including:
Sample Online Transaction Processing (OLTP) and data warehousing database schemas are available on the CD-ROM. After you finish creating your Oracle9i database with Oracle Database Configuration Assistant, you can import the appropriate sample schema.
If you selected multipurpose, you do not need to import the sample schemas; they are already provided with your database.
To import an OLTP or data warehousing sample schema into your Oracle9i database:
C:\> sqlplus
Enter user-name: SYSTEM/password
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 data warehousing sample schema.
SQL> EXIT
ORACLE_BASE\ORACLE_HOME
\assistants\dbca\samples
directory on your hard drive.
C
:\oracle\ora90\assistants\dbca\samples> imp sample_user/password
FILE=sample.dmp FULL=y LOG=myimp.log
See Also:
for more information on using Oracle Database Configuration Assistant |
The Delete a Database option of Oracle Database Configuration Assistant lets you quickly and easily delete all database files excluding the initialization parameter file.
This section describes how to create a new database manually. Oracle provides a sample database creation script, and a sample initialization parameter file with the database software files it distributes, both of which can be edited to suit your needs. If you have existing scripts you can use them to create a database manually or you can edit your existing script using the sample database creation script as a guide. There are a number of ways to create a database depending on if you want to:
Table 1-1 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.
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, |
Create the following directories in which to put the administration and database files for the new database prod
:
C:\oracle\admin\prod
C:\oracle\admin\prod\bdump
C:\oracle\admin\prod\pfile
C:\oracle\admin\prod\udump
C:\oracle\oradata\prod
You only need to export an existing database if you want to copy its contents to a new database.
You can start 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.
C:\> exp SYSTEM
/password
FILE=myexp.dmp FULL=y LOG=myexp.log
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 Oracle9i Database Utilities for more information on using the Export utility.
To export all data from an existing database to a new database:
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
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 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:
orcl
at the MS-DOS command prompt:
C:\> oradim -SHUTDOWN -SID orcl -USRPWD
password
-SHUTTYPE inst
-SHUTMODE i
C:\oracle\oradata\orcl
directory:
If you are using the starter database orcl
as the basis for your new database, copy the init.ora
file:
C:\
ORACLE_BASE
\admin\orcl\pfile\init.ora
to
C:\
ORACLE_BASE
\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. Table 1-2 describes the modification instructions.
Initialization Parameter | Modification Instructions |
---|---|
|
This parameter indicates the name of the database and must match the name used in the
|
|
|
|
|
|
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
|
|
|
|
|
|
Set this parameter to the same number as the value of the
|
See Also:
Oracle9i Database Reference for information on other initialization parameters that you can add or modify |
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 Oracle9i database process, oracle.exe
, installed in the form of a Windows NT service.
Use the ORADIM utility to create the service. After it has been created, the service starts automatically. See "Using the ORADIM Utility to Administer an Oracle Instance" for information on how to use the ORADIM utility.
To create and start an Oracle service:
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.
ORACLE_SID
to equal prod
. Note that there are no spaces around the equal sign (=) character:
C:\> set ORACLE_SID=prod
The CREATE
DATABASE
statement is a SQL statement that creates the database. A script containing this statement can be used anytime you want to create a database.
The CREATE DATABASE
statement may have the following parameters:
MAXDATAFILES
- default value: 32, maximum value: 65534
MAXLOGFILES
- default value: 32, maximum value: 255
When you run a CREATE DATABASE
statement, Oracle performs several operations depending upon the clauses that you specified in the CREATE DATABASE
statement or the initialization parameters that you have set.
Note: Oracle Managed Files is a feature that can work with the CREATE DATABASE statement. Using Oracle-managed files simplifies the administration of an Oracle database. Oracle-managed files eliminate the need to directly manage the operating system files comprising an Oracle database. Using Oracle Managed Files can simplify the use of the CREATE DATABASE statement. You specify operations in terms of database objects rather than filenames. For more information on using Oracle Managed Files see Oracle9i Database Administrator's Guide. |
The following statement creates the database prod:
CREATE DATABASE prod
MAXLOGFILES 5
MAXDATAFILES 100
DATAFILE 'oracle\oradata\prod\system01.dbf' SIZE 325M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITEDUNDO TABLESPACE "UNDOTBS" DATAFILE 'oracle\oradata\prod\undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
logfile 'C:\oracle\oradata\prod
\redo01.log' size 100M reuse, 'C:\oracle\oradata\prod
\redo02.log' size 100M reuse, 'C:\oracle\oradata\prod
\redo03.log' size 100M reuse;
To use the SQL script to create a database:
OracleServicePROD
, and its status column must display Started. If not, select the service name and choose 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
PROD
the current SID
:
C:\> set ORACLE_SID=PROD
C:\> SQLPLUS / NOLOG SQL> CONNECT /AS
SYSDBA
/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.
SQL> SPOOL script_name.log
script_name.sql
script that you created in "Creating a Database":
SQL> @C:\oracle\ora90\rdbms\admin\script_name.sql;
If the database is successfully created, the instance is started and the following message appears numerous times: Statement processed
You can import the full export created in "Exporting an Existing Database" into the new database.
You can also start 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.
C:\> imp SYSTEM/
password
FILE=myexp.dmp FULL=y LOG=myexp.log
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 Oracle9i Database Utilities for more information on using the Import utility.
To import a database:
C:\> imp 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, the Import utility tries to create those tablespaces with associated datafiles. The easy solution is to ensure that both databases contain the same tablespaces. The datafiles do not have to be identical. Only the tablespace names are important. |
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.
C:\> regedt32
The registry editor window appears.
\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\HOME
ID
where ID
is the unique number identifying the Oracle home.
Oracle9i Database installation guide for Windows for more information on the subkey locations for multiple Oracle homes
See Also:
ORACLE_SID
parameter on the right side of the registry editor window.
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:
The Add Value dialog box appears:
ORACLE_SID
in the Value Name text box.
REG_EXPAND_SZ
(for an expandable string) in the Data Type list box.
A string editor dialog box appropriate for the data type appears:
prod
in the String Editor dialog box.
The registry editor adds the ORACLE_SID
parameter.
The registry editor exits.
Caution: 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:
C:\> oradim -SHUTDOWN -SID prod -USRPWD
password
-SHUTTYPE srvc,inst -SHUTMODE i
Caution:
Although the ORADIM utility 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 |
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 after making any significant changes to the database, such as switching the archiving mode, or adding a tablespace or datafile.
See Also:
Chapter 6, "Backing Up and Recovering Database Files", Oracle9i Database Concepts, Oracle9i User-Managed Backup and Recovery Guide, and Oracle9i Database Administrator's Guide for more information on archiving and backup and recovery. |
The ORADIM utility is a command line tool that is available with the Oracle9i database. You only need to use the ORADIM utility if you are manually creating, deleting, or modifying databases. Oracle Database Configuration Assistant is an easier tool to use for this purpose.
When you use the ORADIM utility, 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.
If you have installed an Oracle database service on Windows 2000, when logging in as SYSTEM user (LocalSystem), with startup mode set to Automatic, it is possible that the Oracle database service starts but the database does not start automatically. The following error message is written to the ORADIM.LOG
file in the ORACLE_BASE\ORACLE_HOME
\database
directory.
ORA-12640: Authentication adapter initialization failed
Also, Oracle Enterprise Management Agent, Oracle Enterprise Manager Management Server and Oracle Internet Directory may also fail because they cannot connect to the database for the same reason.
The workarounds are:
sqlnet.authentication_services=(NTS
) from SQLNET.ORA
or set sqlnet.authentication_services=(NONE)
in SQLNET.ORA
.
SYSDBA
.
The following sections describe the ORADIM utility commands and parameters. Note that each command is preceded by a dash (-
). Table 1-3 shows how to get a list of ORADIM utility parameters.
Table 1-4 describes how to use the ORADIM utility to create an instance.
Table 1-5 describes how to use the ORADIM utility to start an instance.
Table 1-6 describes how to use the ORADIM utility to stop an instance.
Table 1-7 describes how to use the ORADIM utility to modify an instance.
Table 1-8 describes how to use the ORADIM utility to delete an instance.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|