This chapter describes how to create a database after installing Oracle Database, using either Database Configuration Assistant or command-line tools.
This chapter contains these topics:
All mounted Oracle Database servers in a network must have unique database names. When a database is created, a name is associated with it and stored in its control files. If you provide the database keyword, either in the CREATE DATABASE
statement or when prompted by Database Configuration Assistant, then that value becomes the name for that database.
If you attempt to mount two Oracle Database servers with the same database name, then you receive the following error during mounting of the second server:
ORA-01102: cannot mount database in EXCLUSIVE mode
If there are two or more Oracle Database servers on the same computer, but located in different Oracle homes, then the following rules apply:
Each database name must be unique
Each SID must be unique
To change the name of an existing database, you must use the CREATE CONTROLFILE
statement to re-create your control files and specify a new database name.
Note:
Directory path examples in this chapter follow Optimal Flexible Architecture (OFA) guidelines. If you specified non-OFA directories during installation, then your directory paths will differ.Oracle recommends you use Database Configuration Assistant to create a database, because it is easier. It offers the same interface and operates the same way on all supported platforms, so no step-by-step procedures or screen shots are included here.
See Also:
Oracle Database 2 Day DBA for instructions on using Database Configuration AssistantDatabase Configuration Assistant enables you to:
Create a Database
Configure Automatic Storage Management
Configure Database Options in a database
Delete a Database
Manage Templates
An initialization parameter file is an ASCII text file containing parameters. It can be used to create and modify a database using command-line tools. When you create a database using Database Configuration Assistant, a server parameter file (SPFILE) is created from the initialization parameter file, and the initialization parameter file is renamed. Oracle does not recognize the renamed file as an initialization parameter file, and it is not used after the instance is started.
If you want to modify an instance created with Database Configuration Assistant after it starts, you must use ALTER SYSTEM
statements. You cannot change the SPFILE itself, because it is a binary file that cannot be browsed or edited using a text editor. The location of the newly-created SPFILE is ORACLE_BASE
\
ORACLE_HOME
\database
. The SPFILE filename is spfile
SID
.ora
.
See Also:
"Managing Initialization Parameters Using a Server Parameter File" in Oracle Database Administrator's GuideThis section describes how to create a new database manually. As part of its database software files, Oracle Database provides a sample database creation script and a sample initialization parameter file, both of which can be edited to suit your needs. Alternatively, if you have an existing script you can use it as-is to create a database manually or edit it using the sample database creation script as a guide.
Database creations are of three types:
Copy an existing database and delete the old database.
Copy an existing database and keep the old database.
Create a new database when no database exists on your system.
Table 3-1 summarizes tasks involved in creating a new database for each of these database creation categories. Each step is explained in detail in the following subsections.
Table 3-1 Manual Database Creation Tasks
Task | Copy existing database and delete old database | Copy existing database and keep old database | Create new database when no database exists on system |
---|---|---|---|
Yes |
Yes |
Yes |
|
Yes |
MaybeFoot 1 |
Not applicable |
|
Yes |
No |
Not applicable |
|
Yes |
Yes |
Yes |
|
Yes |
Yes |
Yes |
|
No |
Yes |
Yes |
|
Yes |
Yes |
Yes |
|
Yes |
Yes |
Yes |
|
Yes |
MaybeFoot 2 |
Not applicable |
|
No |
Only if you change the default |
Yes |
|
Yes |
Yes |
Yes |
Footnote 1 Yes if you copy data from the existing database to the new database; no otherwise.
Footnote 2 Yes if you import tables and other objects exported from the existing database; no otherwise.
We use an example in the following sections to demonstrate how to create a database. In this example, the existing database is the starter database with a SID
of orcl
located in directory C:\oracle\product\10.2.0\oradata\orcl
. You will copy orcl
to a new database with a database name and SID
of prod
located in directory C:\oracle\product\10.2.0\oradata\prod
. You will then delete starter database orcl
.
Create the following directories in which to put administration and database files for new database prod
:
C:\oracle\product\10.2.0\admin\prod
C:\oracle\product\10.2.0\admin\prod\bdump
C:\oracle\product\10.2.0\admin\prod\pfile
C:\oracle\product\10.2.0\admin\prod\udump
C:\oracle\product\10.2.0\oradata\prod
You are required to export an existing database only if you intend to copy its contents to a new database. If you are working with data from an earlier Oracle release, then you can use Export for this task. If you are using Oracle Database 10g Release 1 (10.1) or later data, then Oracle recommends that you use Data Pump Export because it supports new Oracle Database 10g Release 1 (10.1) or later features, such as floating points.
Although you can start Data Pump Export or Export in either parameter mode or interactive mode, Oracle recommends parameter mode. Interactive mode provides less functionality than parameter mode and exists for backward compatibility only.
The syntax for Data Pump Export parameter mode is:
C:\> expdp SYSTEM/password DUMPFILE=myexp.dmp FULL=y LOGFILE=myexp.log
The syntax for Data Pump Export interactive mode is:
C:\> expdp SYSTEM/password
Enter only the command expdp
SYSTEM
/password
to begin an interactive session and let Data Pump Export prompt you for information it needs.
Note:
If you use parameter mode, then Data Pump Export considers filenames and directory names to be invalid if they contain one or more blank spaces. The workaround is to enclose the full path in theDUMPFILE=
parameter in triple quotation marks. For example:
DUMPFILE="""C:\program files\export.dmp"""
If Data Pump Export is used in interactive mode, then the filename or directory name can contain a space without quotation marks.
The syntax for Export parameter mode is:
C:\> exp SYSTEM/password FILE=myexp.dmp FULL=y LOG=myexp.log
The syntax for Export interactive mode is:
C:\> exp SYSTEM/password
Enter only the command exp
SYSTEM
/password
to begin an interactive session and let Export prompt you for information it needs.
Note:
If you use parameter mode, then Export considers filenames and directory names to be invalid if they contain one or more blank spaces. The workaround is to enclose the full path in theFILE=
parameter in triple quotation marks. For example:
FILE="""C:\program files\export.dmp"""
If Export is used in interactive mode, then the filename or directory name can contain a space without quotation marks.
See Also:
Oracle Database Utilities for more information about using Data Pump Export or ExportTo export all data from an existing database to a new database:
Set ORACLE_SID
to the database service of the database whose contents you intend to export. For example, if the database you intend to export is starter database orcl
, then enter the following at the command prompt. Note that there are no spaces around the equal sign (=
) character.
C:\> set ORACLE_SID=orcl
If the existing database is Oracle Database 10g Release 1 (10.1) or later, then start Data Pump Export from the command prompt:
C:\> expdp SYSTEM/password DUMPFILE=myexp.dmp FULL=y LOG=myexp.log
You now have a full database export of starter database orcl
in file myexp.dmp
. All messages from Data Pump Export are logged in file myexp.log
.
If the existing database is before Oracle Database 10g Release 1 (10.1), then start Export from the command prompt:
C:\> exp SYSTEM/password FILE=myexp.dmp FULL=y LOG=myexp.log
You now have a full database export of starter database orcl
in file myexp.dmp
. All messages from Export are logged in file myexp.log
.
Deleting database files is required only when you copy an existing database to a new database to replace the old database. In the following example, you delete the database files of starter database orcl
.
To delete database files:
Shut down starter database orcl
at the command prompt:
C:\> oradim -SHUTDOWN -SID orcl -SHUTTYPE inst -SHUTMODE immediate
Delete the following files from directory C:\oracle\product\10.2.0\oradata\orcl
:
File Name | File Name | File Name | File Name |
---|---|---|---|
control01.ctl |
drsys01.dbf |
temp01.dbf |
xdb01.dbf |
control02.ctl |
cwmlite01.dbf |
tools01.dbf |
redo01.log |
control03.ctl |
example01.dbf |
undotbs01.dbf |
redo02.log |
index01.dbf |
system01.dbf |
user01.dbf |
redo03.log |
To use starter database orcl
as the basis for your new database, first copy ORACLE_BASE
\admin\orcl\pfile\init.ora
. Second, put the copy in ORACLE_BASE
\admin\prod\pfile\init.ora
. Third, modify the file as described in this section.
Note:
Beginning with Oracle9i release 2 (9.2), nesting of quotation marks using the backslash (\) escape character is no longer supported. This may affect how Oracle Database interprets parameter values in your initialization parameter file. For example, if you specifiedCONTROL_FILES = "ctlfile\'1.ora"
in releases prior to release 9.2, the filename would be interpreted as ctlfile'1.ora
. Starting with release 9.2, the filename would be interpreted as ctlfile\'1.ora
.
Oracle highly recommends modifying your parameter files to remove such references. See Oracle Database Reference for other methods of nesting quotation marks in initialization parameter values.
If you do not have an existing database on your system, then you cannot copy an existing initialization parameter file to use as the basis for your new initialization parameter file. However, you can use the sample initialization parameter file initsmpl.ora
provided in
ORACLE_BASE\ORACLE_HOME\admin\sample\pfile
as the basis for the initialization parameter file for database prod
.
If you use initsmpl.ora
as the basis for the initialization parameter file, then you must set the following parameters to the indicated values, or you will not be able to start database prod
:
DB_NAME=prod.domain
Parameter DB_NAME
indicates the database name 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 not more than eight characters for a database name. The name is not required to match the SID of the database service.
INSTANCE_NAME=prod.domain
SERVICE_NAMES=prod.domain
CONTROL_FILES = ( "C:\oracle\product\10.2.0\oradata\prod\control01.ctl", "C:\oracle\product\10.2.0\oradata\prod\control02.ctl", "C:\oracle\product\10.2.0\oradata\prod\control03.ctl")
Parameter CONTROL_FILES
lists database control files. You do not have control files on your file system at this point, because control files are created when you run the CREATE DATABASE
statement. Ensure that you specify the complete path and filename, including drive letter.
BACKGROUND_DUMP_DEST = C:\oracle\product\10.2.0\admin\prod\bdump
USER_DUMP_DEST = C:\oracle\product\10.2.0\admin\prod\udump
DB_FILES=100
Modifying initialization parameter DB_FILES
is not required, but it is recommended to optimize performance. 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.
See Also:
Oracle Database Reference for information about other initialization parameters that you can add or modifyStart an instance without mounting a database.
STARTUP NOMOUNT
You are not required to specify the PFILE
clause in this example, because the initialization parameter file is stored in the default location. At this point, there is no database. Only the SGA is created and background processes are started in preparation for the creation of a new database.
You are required to create and start an Oracle Database service only if you do one of the following:
Copy an existing database to a new database and keep the old database
Create a new database when you have no other database to copy
Before you create the database, first create a Windows service to run the database. This service is the Oracle Database process, oracle.exe
, installed in the form of a Windows service.
Use ORADIM to create the service. After it has been created, the service starts automatically. See "Using ORADIM to Administer an Oracle Database Instance" for information about how to use ORADIM.
To create and start an Oracle Database service:
Run ORADIM from the command prompt:
C:\> oradim -NEW -SID prod -STARTMODE manual -PFILE "C:\oracle\product\10.2.0\admin\prod\pfile\init.ora"
Note that the previously created initialization parameter file is specified, with complete path, including drive name. You can check if the service is started in the Services window of the Control Panel.
Set 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 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 Database performs several operations depending upon clauses that you specified in the CREATE DATABASE
statement or initialization parameters that you have set.
Note:
Oracle-managed files is a feature that works with theCREATE DATABASE
statement to simplify administration of Oracle Database. Oracle-managed files eliminates the requirement to directly manage operating system files comprising an Oracle Database server, because you specify operations in terms of database objects rather than filenames. For more information about using Oracle-managed files see Oracle Database Administrator's Guide.To create database prod
, copy and save the following statement in a file named script_name
.sql
:
CREATE DATABASE prod MAXLOGFILES 5 MAXDATAFILES 100 DATAFILE 'C:\oracle\product\10.2.0\oradata\prod\system01.dbf' SIZE 325M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED UNDO TABLESPACE "UNDOTBS" DATAFILE 'oracle\product\10.2.0\oradata\prod\undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED CHARACTER SET WE8MSWIN1252 logfile 'C:\oracle\product\10.2.0\oradata\prod\redo01.log' size 100M reuse, 'C:\oracle\product\10.2.0\oradata\prod\redo02.log' size 100M reuse, 'C:\oracle\product\10.2.0\oradata\prod\redo03.log' size 100M reuse;
To use the SQL script to create a database:
Verify that the service is started in the Control Panel. In this example, the service name is OracleServicePROD
, and its status column must display Started. If not, then select the service name and choose Start.
You can also check the status of the service by entering the following at the command prompt:
C:\> net START
A list of all Windows services currently running on the system appears. If OracleServicePROD
is missing from the list, then enter:
C:\> net START OracleServicePROD
Make PROD
the current SID
:
C:\> set ORACLE_SID=PROD
Add ORACLE_BASE
\
ORACLE_HOME
\bin
to your PATH
environment variable:
set PATH=ORACLE_BASE\ORACLE_HOME\bin;%PATH%
Start SQL*Plus from the command prompt, and connect to the database as SYSDBA:
C:\> sqlplus /NOLOG SQL> CONNECT / AS SYSDBA
The message connected
appears.
Turn on spooling to save messages:
SQL> SPOOL script_name.log
Run script script_name
.sql
that you created in "Putting the CREATE DATABASE Statement in a Script":
SQL> @C:\oracle\product\10.2.0\db_1\rdbms\admin\script_name.sql;
If the database is successfully created, then the instance is started and the following message appears numerous times: Statement
processed
You can use Data Pump Import (for Oracle Database 10g Release 1 (10.1) or later data) or Import (for earlier data) to import the full export created in "Exporting an Existing Database" into the new database. Although you can start Data Pump Import or Import using either parameter mode or interactive mode, Oracle recommends parameter mode because it provides more functionality. Interactive mode exists solely for backward compatibility.
The syntax for Data Pump Import parameter mode is:
C:\> impdp SYSTEM/password DUMPFILE=myexp.dmp FULL=y LOG=myexp.log
The syntax for Data Pump Import interactive mode is:
C:\> impdp SYSTEM/password
Enter only impdp
SYSTEM/
password
to begin an interactive session and let Data Pump Import prompt you for information it needs.
Note:
If you use parameter mode, then Data Pump Import considers filenames and directory names to be invalid if they contain one or more blank spaces. The workaround is to enclose the full path in theDUMPFILE=
parameter in triple quotation marks. For example:
DUMPFILE="""C:\program files\export.dmp"""
If you use Data Pump Import in interactive mode, then the filename or directory name can contain a space without quotation marks.
The syntax for Import parameter mode is:
C:\> imp SYSTEM/password FILE=myexp.dmp FULL=y LOG=myexp.log
The syntax for Import interactive mode is:
C:\> imp SYSTEM/password
Enter only imp
SYSTEM/
password
to begin an interactive session and let Import prompt you for information it needs.
Note:
If you use parameter mode, then Import considers filenames and directory names to be invalid if they contain one or more blank spaces. The workaround is to enclose the full path in theFILE=
parameter in triple quotation marks. For example:
FILE="""C:\program files\export.dmp"""
If you use Import in interactive mode, then the filename or directory name can contain a space without quotation marks.
Note:
If the original database from which the export file was generated contains a tablespace that is not in the new database, then Import tries to create that tablespace with associated datafiles.The easy solution is to ensure that both databases contain the same tablespaces. Datafiles are not required to be identical. Only tablespace names are important.
See Also:
Oracle Database Utilities for more information about using Data Pump Import or ImportIf this is the first database on the system or if you intend to make the new database the default database, then you must make a change in the registry.
Start Registry Editor at the command prompt:
C:\> regedt
The Registry Editor window appears.
Choose subkey \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
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.
See Also:
Chapter 14, "Configuration Parameters and the Registry" for more information about subkey locations for multiple Oracle homesLocate parameter ORACLE_SID
on the right side of the Registry Editor window.
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 parameter ORACLE_SID
, because this is the first database on your system, then you must create it.
To create parameter ORACLE_SID
:
Choose Add Value from the Edit menu.
The Add Value dialog appears:
Enter ORACLE_SID
in the Value Name field.
Select REG_EXPAND_SZ (for an expandable string) in the Data Type list.
Click OK.
A string editor dialog appropriate for the data type appears:
Enter prod in the String field.
Click OK.
Registry Editor adds parameter ORACLE_SID
.
Choose Exit from the Registry menu.
Registry Editor exits.
Caution:
If anything goes wrong while operating the new database without a backup, then you must repeat the database creation procedure. Back up your database now to prevent loss of data.To back up the new database:
Shut down the database instance and stop the service:
C:\> oradim -SHUTDOWN -SID prod -SHUTTYPE srvc,inst -SHUTMODE immediate
Caution:
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 serviceOracleServicePROD
has stopped. If you do not do this, then the backup may be useless because it was taken while data was being written to datafiles.Using the tool of your choice, back up database files.
Database files consist of the initialization parameter file, control files, online redo log files, and datafiles.
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, such as switching archiving mode or adding a tablespace or datafile.
See Also:
For more information about archiving, backup, and recoveryCaution:
Do not store database files on a compressed drive. This can result in write errors and decreased performance.ORADIM is a command-line tool that is available with Oracle Database. You are required to use ORADIM only if you are manually creating, deleting, or modifying databases. Database Configuration Assistant is an easier tool to use for this purpose.
The following sections describe ORADIM commands and parameters. Note that each command is preceded by a dash (-
). To get a list of ORADIM parameters, enter:
oradim -? | -h | -help
Note:
Specifying oradim
without any options also returns a list of ORADIM parameters and descriptions.
Windows Server 2008 and Windows Server 2008 R2 require Administrator privileges to run ORADIM.
When you use ORADIM, a log file called oradim.log
opens in ORACLE_BASE
\
ORACLE_HOME
\database
, or in the directory specified by registry parameter ORA_CWD
. All operations, whether successful or failed, are logged in this file. You must check this file to verify success of an operation.
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 file ORADIM.LOG
in directory ORACLE_BASE
\
ORACLE_HOME
\database
:
ORA-12640: Authentication adapter initialization failed
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:
Modify SQLNET.ORA
You can modify SQLNET.ORA
, either by removing the line
sqlnet.authentication_services=(NTS)
or by changing it to
sqlnet.authentication_services=(NONE)
Start the database after the service starts
You can start the database manually after the Oracle Database service has started, using SQL*Plus and connecting as SYSDBA
.
Start the service as a specific user
See Also:
Your operating system documentation for instructions on starting servicesTo use ORADIM to create an instance, enter:
oradim -NEW -SID SID | -SRVC service_name | -ASMSID SID | -ASMSRVC service_name [-SYSPWD password] [-STARTMODE auto | manual] [-SRVCSTART system | demand] [-PFILE filename | -SPFILE] [-SHUTMODE normal | immediate | abort] [-TIMEOUT secs] [-RUNAS osusr/ospass]
where
-NEW
indicates that you are creating a new instance. This is a mandatory parameter.
-SID
SID
is the name of the instance to create.
-SRVC
service_name
is the name of the service to create (OracleService
SID
).
-ASMSID
SID
is the name of the Automatic Storage Management (ASM) instance to create.
-ASMSRVC
service_name
is the name of the ASM service to create.
-STARTMODE
auto
| manual
indicates whether to start the instance when the Oracle Database service is started. Default is manual
.
-SRVCSTART system | demand
indicates whether to start the Oracle Database service on computer restart.
-PFILE
filename
is the initialization parameter file to be used with this instance. Ensure that you specify the complete path name of this file, including drive letter.
-SPFILE
indicates that a server parameter file (SPFILE) be used during startup instead of a PFILE.
-TIMEOUT
secs
sets the maximum time to wait (in seconds) before the service for a particular SID
stops.
To create an instance called PROD
, for example, you can enter:
C:\> oradim -NEW -SID prod -STARTMODE auto -PFILE C:\oracle\product\10.2.0\admin\prod\pfile\init.ora
To use ORADIM to start an instance and services, enter
oradim -STARTUP -SID SID | -ASMSID SID [-SYSPWD password] [-STARTTYPE srvc | inst | srvc,inst] [-PFILE filename | -SPFILE]
where
-STARTUP
indicates that you are starting an instance that exists. This is a mandatory parameter.
-SID
SID
is the name of the instance to start.
-ASMSID
SID
is the name of the ASM instance to start.
-STARTTYPE
srvc
, inst
indicates whether to start the service or the instance. One or both values can be specified. If it is not specified, then the registry is checked for the current setting.
-STARTTYPE srvc
is equivalent to running net start oracleservice<sid>
from the command line.
-STARTTYPE inst
is equivalent of running startup
within SQL*Plus.
-PFILE
filename
is the initialization parameter file to be used with this instance. Ensure that you specify the complete path name of this file, including drive letter.
-SPFILE
indicates that a server parameter file (SPFILE) be used during startup instead of a PFILE.
To start an instance called puma
, for example, you can enter:
C:\> oradim -STARTUP -SID puma -STARTTYPE inst -PFILE C:\oracle\product\10.2.0\admin\prod\pfile\init.ora
The Enterprise Database Control service (OracleDBConsole
SID
) is dependent on the Oracle Database service (OracleService
SID
). You must stop the dependent Enterprise Database Control service (if installed) before running ORADIM
to stop the database instance service.
To use ORADIM to stop an instance, enter:
oradim -SHUTDOWN -SID SID | -ASMSID SID [-SYSPWD password] [-SHUTTYPE srvc | inst | srvc,inst] [-SHUTMODE normal | immediate | abort]
where
-SHUTDOWN
indicates that you are stopping an instance. This is a mandatory parameter.
-SID
SID
specifies the name of the instance to stop.
-ASMSID
SID
is the name of the ASM instance to stop.
-SHUTTYPE
srvc
, inst
indicates whether to stop the service or the instance. One or both values can be specified. If it is not specified, then the registry is checked for the current setting.
-SHUTMODE
specifies how to stop an instance. This is an optional parameter. If you do not specify how to stop an instance, then normal
is the default mode.
To stop an instance called puma
, for example, you can enter:
C:\> oradim -SHUTDOWN -SID puma -SHUTTYPE srvc,inst
You can edit an existing instance to change such values as instance name, startup mode, shutdown mode, and shutdown type. To use ORADIM to modify an instance, enter:
oradim -EDIT -SID SID | -ASMSID SID [-SYSPWD password] [-STARTMODE auto | manual] [-SRVCSTART system | demand] [-PFILE filename | -SPFILE][SHUTMODE normal | immediate | abort] [SHUTTYPE srvc | inst | srvc,inst]
where
-EDIT
indicates that you are modifying an instance. This is a mandatory parameter.
-SID
SID
specifies the name of the instance to modify. This is a mandatory parameter.
-ASMSID
SID
is the name of the ASM instance to modify.
-STARTMODE
indicates whether to start the instance when the Oracle Database service is started. Default is manual
.
-SRVCSTART system | demand
indicates whether to start the Oracle Database service on computer restart.
-PFILE
filename
specifies the initialization parameter file to be used with this instance. Ensure that you specify the complete path name of this file, including drive letter.
-SPFILE
indicates that a server parameter file (SPFILE) be used during startup instead of a PFILE.
-SHUTMODE
specifies how to stop an instance. This is an optional parameter. If you do not specify how to stop an instance, then normal
is the default mode.
-SHUTTYPE
indicates whether to stop the service or the instance. One or both values can be specified. If it is not specified, then the registry is checked for the current setting.
To specify a new initialization parameter file for the instance prod
, for example, you can enter:
C:\> oradim -EDIT -SID prod -PFILE C:\oracle\product\10.2.0\admin\lynx\pfile\init.ora
The Enterprise Database Control service (OracleDBConsole
SID
) is dependent on the Oracle Database service (OracleService
SID
). You must stop the dependent Enterprise Database Control service (if installed) before running ORADIM
to delete the database instance.
To use ORADIM to delete an instance, enter:
oradim -DELETE -SID SID | -ASMSID SID | -SRVC service_name | -ASMSRVC service_name
where
-DELETE
indicates that you are deleting an instance or service. This is a mandatory parameter.
-SID
SID
specifies the name of the SID to delete.
-SRVC
service_name
specifies the name of the service to delete.
-ASMSID
SID
is the name of the ASM instance to delete.
-ASMSRVC
service_name
is the name of the ASM service to delete.
To delete an instance called prod
, for example, you can enter:
C:\> oradim -DELETE -SID prod
This section contains these topics:
See Also:
Oracle Database Upgrade GuideTo back up a 32-bit Oracle home database:
Start SQL*Plus:
C:\> sqlplus /NOLOG
Connect to the database instance as SYSDBA
:
SQL> CONNECT / AS SYSDBA;
Create a .trc
file to use as a template to re-create the control files on the 64-bit computer:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Shut down the database:
SQL> SHUTDOWN IMMEDIATE;
Perform a full offline backup of the database.
To migrate an Oracle Database 10g Release 2 (10.2) database for 32-bit Windows to an Oracle Database 10g Release 2 (10.2) database for 64-bit Windows:
Install Oracle Database 10g Release 2 (10.2) for 64-bit Windows.
Create the new Oracle Database 10g Release 2 (10.2) service at the command prompt:
C:\> ORADIM -NEW -SID SID [-INTPWD PASSWORD ]-MAXUSERS USERS -STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA
The following table provides more information about the values you must supply.
Parameter | Description |
---|---|
SID |
SID of the database you are upgrading |
PASSWORD |
Password for the new Oracle Database 10g Release 2 (10.2) for 64-bit Windows database. This is the password for the user connected with SYSDBA privileges. The -INTPWD option is not required. If you do not specify it, then operating system authentication is used, and no password is required. |
USERS |
Maximum number of users who can be granted SYSDBA and SYSOPER privileges |
ORACLE_HOME |
Oracle home directory. Ensure that you specify the full path name with the -PFILE option, including drive letter of the Oracle home directory. |
Copy the 32-bit datafiles to the new 64-bit Oracle home.
Copy the 32-bit configuration files to the 64-bit Oracle home.
If your 32-bit initialization parameter file has an IFILE
(include file) entry, then copy the file specified by the IFILE
entry to the 64-bit Oracle home and edit the IFILE
entry in the initialization parameter file to point to its new location.
If you have a password file that resides in the 32-bit Oracle home, then copy the password file to the 64-bit Oracle home. The default 32-bit password file is located in ORACLE_BASE
\
ORACLE_HOME
\database\pwd
SID
.ora
., where SID
is your Oracle instance ID.
In the 64-bit Oracle home, add the _SYSTEM_TRIG_ENABLED = false
parameter to the ORACLE_HOME
\database\ORACLE_
SID
\init.ora
file before changing the word size.
Remove this parameter from the initialization file after the word size change is complete.
See Also:
Oracle Database Upgrade Guide for more information about changing word sizeGo to the 64-bit ORACLE_HOME
\rdbms\admin
directory from the command prompt.
Start SQL*Plus:
C:\> sqlplus /NOLOG
Connect to the database instance as SYSDBA
:
SQL> CONNECT / AS SYSDBA;
Re-create the 64-bit control files using the CREATE CONTROLFILE
command. Edit the trace file created in "Backing Up a 32-Bit Oracle Database" to change the paths to the datafiles, log files and control files to point to the Oracle home on the 64-bit computer. This creates the new control file in ORACLE_HOME
\database
.
Here is an example of a database named "orcl32" on a 32-bit computer migrating to "orcl64" on a 64-bit computer:
CREATE CONTROLFILE REUSE DATABASE "T1" NORESETLOGS NOARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 2 MAXDATAFILES 32 MAXINSTANCES 16 MAXLOGHISTORY 1815 LOGFILE GROUP 1 'C:\oracle\product\10.2.0\oradata\orcl64\REDO03.LOG' SIZE 1M, # was 'C:\oracle\product\10.2.0\oradata\orcl32\...LOG' # on the 32-bit computer GROUP 2 'C:\oracle\product\10.2.0\oradata\orcl64\REDO02.LOG' SIZE 1M, GROUP 3 'C:\oracle\product\10.2.0\oradata\orcl64\REDO01.LOG' SIZE 1M DATAFILE 'C:\oracle\product\10.2.0\oradata\orcl64\SYSTEM01.DBF', # was 'C:\oracle\product\10.2.0\oradata\orcl32\...DBF' # on the 32-bit computer 'C:\oracle\product\10.2.0\oradata\orcl64\RBS01.DBF', 'C:\oracle\product\10.2.0\oradata\orcl64\USERS01.DBF', 'C:\oracle\product\10.2.0\oradata\orcl64\TEMP01.DBF', 'C:\oracle\product\10.2.0\oradata\orcl64\TOOLS01.DBF', 'C:\oracle\product\10.2.0\oradata\orcl64\INDX01.DBF', 'C:\oracle\product\10.2.0\oradata\orcl64\DR01.DBF' CHARACTER SET WE8ISO8859P1;
Alter the init
file from the 32-bit computer to include the new control file generated in the preceding step.
Shut down the database:
SQL> SHUTDOWN IMMEDIATE;
Start the database in UPGRADE
mode to run utlirp.sql
:
SQL> STARTUP UPGRADE;
You might need to use the PFILE
option to specify the location of your initialization parameter file.
Set the system to spool results to a log file for later verification of success. For example:
SQL> SPOOL mig32-64.log;
Enter the following command to view the output of the script on-screen:
SQL> SET ECHO ON;
Recompile existing PL/SQL modules in the format required by the 64-bit Oracle Database:
SQL> @utlirp.sql;
Turn off the spooling of script results to the log file:
SQL> SPOOL OFF;
Check the spool file and verify that the packages and procedures compiled successfully. Correct any problems you find in this file.
Shut down the database:
SQL> SHUTDOWN IMMEDIATE;
Start the database:
SQL> STARTUP;
Recompile existing PL/SQL modules in the format required by the 64-bit Oracle Database:
SQL> @utlrp.sql;
To migrate an Oracle Database 10g Release 1 (10.1) or older database to an Oracle Database 10g Release 2 (10.2) database for 64-bit Windows:
Perform steps 1 - 11 in "Migrating an Oracle Database 10g Release 2 (10.2) Database".
Shut down the database on the 64-bit computer:
SQL> SHUTDOWN IMMEDIATE;
Start the database migration:
SQL> STARTUP MIGRATE;
Migrate the database as described in Chapter 3, "Upgrading a Database to the New Oracle Database 10g Release" in Oracle Database Upgrade Guide.
Shut down the database:
SQL> SHUTDOWN IMMEDIATE;
Restart the database:
SQL> STARTUP MOUNT;