Skip Headers
Oracle® Database Platform Guide
12c Release 1 (12.1) for Microsoft Windows

E10714-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

4 Postinstallation Database Creation on Windows

This chapter describes how to create a database after installing Oracle Database, using either Oracle Database Configuration Assistant or command-line tools.

This chapter contains these topics:

Overview of the Naming Conventions for Oracle Database

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.

Overview of Database Creation on Windows Using Oracle Database Configuration Assistant

Oracle recommends you use Oracle 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 screenshots are included here.

See Also:

Oracle Database 2 Day DBA for instructions on using Database Configuration Assistant

Starting with Oracle Database 12c Release 1 (12.1), this administration tool like the other administration tools, prompts for password when the Oracle Home User is a Windows Local User Account or Windows Domain User Account and the password for Oracle Home User is not stored in Oracle wallet. The main purpose of Oracle Home User is to run Windows services with Windows User Account. However, this user account (Oracle Home User) has very limited set of operating system-level privileges and should not be used for database administration. Oracle Database Configuration Assistant now provides an interface to create an Oracle Database service under an Oracle Home User or a Windows built-in Local System Account, as specified during the process of installation. But Oracle Database Configuration Assistant does not provide an interface to create a new Windows user as the Oracle Home User.

The services created are not allowed to interact with the Windows desktop. ORADIM, the Windows utility tool used to create the OracleServiceSID - Oracle Database services, is used by Oracle Database Configuration Assistant to create those services on local and remote nodes. Oracle Database Configuration Assistant now accepts a user name and password to run the service, and also changes the ownership of the files it creates (for example, the password file) so that it can be modified by the Oracle Home User.

Oracle Database Configuration Assistant enables you to:

  • Create a database

  • Configure database options in a database

  • Delete a database

  • Manage templates

An initialization parameter file is an ASCII text file containing parameters. Use this file to create and modify a database using command-line tools. When you create a database using Oracle 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 Oracle Database Configuration Assistant after it starts, you must use ALTER SYSTEM statements. You cannot change the server parameter file itself, because it is a binary file that cannot be browsed or edited using a text editor. The location of the newly-created server parameter file is ORACLE_HOME\database. The server parameter file name is spfileSID.ora.

See Also:

"Managing Initialization Parameters Using a Server Parameter File" in Oracle Database Administrator's Guide

Overview of Database Creation Tasks on Windows Using Command-Line Tools

This section describes how to create a new database manually. As part of its database software files, Oracle Database provides a sample initialization parameter file, which can be edited to suit your needs. You can choose to create database creation scripts using Oracle Database Configuration Assistant.

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.

Manual Database Creation Tasks

Use Table 4-1 to understand the manual 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 4-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
 

About Exporting an Existing Database

Yes

Note 1

Not applicable

Deleting Database Files

Yes

No

Not applicable

Modifying the Initialization Parameter File

Yes

Yes

Yes

Starting an Oracle Database Instance

Yes

Yes

Yes

About Creating and Starting an Oracle Database Service

No

Yes

Yes

Putting the CREATE DATABASE Statement in a Script

Yes

Yes

Yes

Running the CREATE DATABASE Script

Yes

Yes

Yes

About Importing a Database

Yes

Note 2

Not applicable

Updating ORACLE_SID in the Registry

No

Only if you change the default SID

Yes

Backing Up the New Database

Yes

Yes

Yes


Note 1

Yes if you copy data from the existing database to the new database; otherwise, no.

Note 2

Yes if you import tables and other objects exported from the existing database; otherwise, no.

An example in the following sections demonstrates how to create a database. In this example, the existing database is the starter database with a SID of orcl located in directory C:\app\username\oradata\orcl. Copy orcl to a new database with a database name and SID of prod located in directory C:\app\username\oradata\prod. Then, delete the starter database orcl.

About Exporting an Existing Database

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 DUMPFILE=myexp.dmp FULL=y LOGFILE=myexp.log
Password: password

The syntax for Data Pump Export interactive mode is:

C:\> expdp SYSTEM 
Password: password

Enter only the command expdp SYSTEM 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 file names and directory names to be invalid if they contain one or more blank spaces. The workaround is to enclose the full path in the DUMPFILE= parameter in triple quotation marks. For example:

DUMPFILE="""C:\program files\export.dmp"""

If Data Pump Export is used in interactive mode, then the file name or directory name can contain a space without quotation marks.

The syntax for Export parameter mode is:

C:\> exp SYSTEM FILE=myexp.dmp FULL=y LOG=myexp.log
Password: password

The syntax for Export interactive mode is:

C:\> exp SYSTEM
Password: password

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

Note:

If you use parameter mode, then Export considers file names and directory names to be invalid if they contain one or more blank spaces. The workaround is to enclose the full path in the FILE= parameter in triple quotation marks. For example:

FILE="""C:\program files\export.dmp"""

If Export is used in interactive mode, then the file name or directory name can contain a space without quotation marks.

See Also:

Oracle Database Utilities for more information about using Data Pump Export or Export

Exporting All Data from an Existing Database

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 intend to export. For example, if the database you intend to export is the 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
    
  2. 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 DUMPFILE=myexp.dmp FULL=y LOG=myexp.log
    Password: password
    

    You now have a full database export of the starter database orcl in the file myexp.dmp. All messages from Data Pump Export are logged in file myexp.log.

  3. If the existing database is earlier than Oracle Database 10g Release 1 (10.1), then start Export from the command prompt:

    C:\> exp SYSTEM FILE=myexp.dmp FULL=y LOG=myexp.log
    Password: password
    

    You now have a full database export of the starter database orcl in the file myexp.dmp. All messages from Export are logged in the file myexp.log.

Deleting Database Files

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 the starter database orcl.

To delete database files:

  1. Shut down starter database orcl at the command prompt:

    C:\> oradim -SHUTDOWN -SID orcl -SHUTTYPE inst -SHUTMODE immediate
    
  2. Delete the following files from the directory C:\app\username\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

Modifying the Initialization Parameter File

To use the starter database orcl as the basis for your new database:

  1. Copy ORACLE_BASE\admin\orcl\pfile\init.ora.

  2. Place the copy in ORACLE_BASE\admin\prod\pfile\init.ora.

  3. Modify the file by performing the following tasks:

    Note:

    Starting with Oracle9i Release 2 (9.2), nesting of quotation marks using the backslash (\) escape character is no longer supported. This might affect how Oracle Database interprets parameter values in your initialization parameter file. For example, if you specified CONTROL_FILES = "ctlfile\'1.ora" in releases before release 9.2, the file name would be interpreted as ctlfile'1.ora. Starting with release 9.2, the file name 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.

    1. 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_HOME\admin\sample\pfile
      

      This is the basis for the initialization parameter file for the database prod.

    2. If you use the initsmpl.ora file as the basis for the initialization parameter file, then the following parameters must be set to the indicated values, otherwise you cannot start database prod:

      DB_NAME=prod.domain

      The 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". Give a unique database name to each database. You can use 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:\app\username\oradata\prod\control01.ctl", "C:\app\username\oradata\prod\control02.ctl", "C:\app\username\oradata\prod\control03.ctl")

      The 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 file name, including drive letter.

      DB_FILES=100

      Modifying the 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 modify

      The DIAGNOSTIC_DEST initialization parameter sets the location of the Automatic Diagnostic Repository (ADR), which is a directory structure stored outside of the database. The ADR is used in problem diagnostics.

      Use DIAGNOSTIC_DEST = ORACLE_HOME\log if the environment variable ORACLE_BASE is not set.

      Use DIAGNOSTIC_DEST = ORACLE_BASE variable if the environment variable ORACLE_BASE is set.

About Creating and Starting an Oracle Database Service

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. See "About Administering an Oracle Database Instance Using ORADIM" for information about how to use ORADIM.

Creating and Starting an Oracle Database Service

To create and start an Oracle Database service:

  1. Run ORADIM from the command prompt:

    C:\> oradim -NEW -SID prod -STARTMODE manual
    -PFILE "C:\app\username\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. Starting with Oracle Database 12c Release 1 (12.1), ORADIM automatically creates Oracle Database services under the Oracle Home User account. If the Oracle Home User account is a Windows Local User Account or a Windows Domain User Account, then ORADIM prompts for its password.

  2. Set ORACLE_SID to equal prod. Note that there are no spaces around the equal sign (=) character:

    C:\> set ORACLE_SID=prod
    

Creating Oracle Wallets in a File System for Oracle Database Services

When an Oracle wallet is created in the file system, only the user creating the wallet is granted access to that wallet by wallet creation tools. Therefore, Oracle Database services (running as the Windows User Account) might not be able to access the wallet unless you explicitly grant access to the wallet using Windows tools.

See section "About Setting File System ACLs Manually" for more information.

Starting an Oracle Database Instance

Start an instance without mounting a database.

SQL> 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 System Global Area (SGA) is created and background processes are started in preparation for the creation of a new database.

Putting the CREATE DATABASE Statement in a Script

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 might 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 the CREATE 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 file names. For more information about using Oracle Managed Files, see Oracle Database Administrator's Guide.

To create the database prod, copy and save the following statement in a file named script_name.sql:

CREATE DATABASE prod
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
MAXLOGFILES 5
MAXDATAFILES 100
DATAFILE 'C:\app\username\oradata\prod\system01.dbf' SIZE 325M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS" DATAFILE 'app\username\oradata\prod\undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
logfile 'C:\app\username\oradata\prod\redo01.log' size 100M reuse,
        'C:\app\username\oradata\prod\redo02.log' size 100M reuse,
        'C:\app\username\oradata\prod\redo03.log' size 100M reuse
EXTENT MANAGEMENT LOCAL;

Running the CREATE DATABASE Script

To use the SQL script to create a database:

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

    C:\> set ORACLE_SID=PROD
    
  3. Add ORACLE_HOME\bin to your PATH environment variable:

    set PATH=ORACLE_BASE\ORACLE_HOME\bin;%PATH%
    
  4. 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.

  5. Turn on spooling to save messages:

    SQL> SPOOL script_name.log
    
  6. Run the script script_name.sql that you created in "Putting the CREATE DATABASE Statement in a Script":

    SQL> C:\app\username\product\12.1.0\dbhome_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

About Importing a Database

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 "About 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 DUMPFILE=myexp.dmp FULL=y LOG=myexp.log
Password: password

The syntax for Data Pump Import interactive mode is:

C:\> impdp SYSTEM 
Password: password

Enter only impdp SYSTEM 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 file names and directory names to be invalid if they contain one or more blank spaces. The workaround is to enclose the full path in the DUMPFILE= parameter in triple quotation marks. For example:

    DUMPFILE="""C:\program files\export.dmp"""

    If you use Data Pump Import in interactive mode, then the file name or directory name can contain a space without quotation marks.

  • 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 data files.

    The easy solution is to ensure that both databases contain the same tablespaces. Data files 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 Import

Updating ORACLE_SID in the Registry

If 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 as follows:

  1. Start Registry Editor at the command prompt:

    C:\> regedit
    

    The Registry Editor window appears.

  2. Select the 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\HOMEID where ID is the unique number identifying the Oracle home.

    See Also:

    Chapter 16, "Configuration Parameters and the Registry" for more information about subkey locations for multiple Oracle homes
  3. Locate the parameter ORACLE_SID 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.

Creating the ORACLE_SID Parameter

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

To create the parameter ORACLE_SID:

  1. Select New from the Edit menu.

  2. Select Expandable String Value from the menu list.

  3. A New Value #1 expandable string value name is created on the right pane of the Registry Editor window of data type REG_EXPAND_SZ.

  4. Right-click the parameter, select Rename to rename it to ORACLE_SID and press Enter.

  5. Double-click the ORACLE_SID entry to change the value data to the new SID.

    An Edit String dialog box appears:

    Description of stringre.gif follows
    Description of the illustration stringre.gif

  6. Enter PROD in the Value data field.

  7. Click OK.

    Registry Editor adds parameter ORACLE_SID.

  8. Select Exit from the File menu.

    Registry Editor exits.

Backing Up the New Database

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:

  1. 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 service OracleServicePROD has stopped. If you do not do this, then the backup might be useless because it was taken while data was being written to data files.
  2. Back up database files using the tool of your choice.

    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.

    Back up the database after making any significant changes, such as switching archiving mode or adding a tablespace or datafile.

Caution:

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

About Administering an Oracle Database Instance Using ORADIM

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. Oracle Database Configuration Assistant is an easier tool to use for this purpose.

Starting with Oracle Database 12c Release 1 (12.1), ORADIM creates Oracle Database service, Oracle VSS Writer service, and Oracle Scheduler service to run under the Oracle Home User account. If this account is a Windows Local User Account or Windows Domain User Account, then ORADIM prompts for password for that account and accepts the same through stdin.

It is possible to specify both the Oracle Home User and its password using the -RUNAS osusr[/ospass] option to oradim. If the given osusr is different from the Oracle Home User, then the Oracle Home User is used instead of osusr along with the given ospass.

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.

When you use ORADIM, a log file called oradim.log opens in 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.

If you have installed an Oracle Database service on Windows, then when logging in as the 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 file ORADIM.LOG in the directory ORACLE_HOME\database:

ORA-12640: Authentication adapter initialization failed 

Oracle Enterprise Management Agent, Oracle Enterprise Manager Management Server, and Oracle Internet Directory might also fail, because they cannot connect to the database for the same reason. To work around this issue, perform the following tasks:

  1. Modify SQLNET.ORA

    You can modify SQLNET.ORA, be doing either of the following:

    • Remove the line sqlnet.authentication_services=(NTS)

    • Change the line sqlnet.authentication_services=(NONE)

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

  3. Start the service as a specific user

    See Also:

    Your operating system documentation for instructions on starting services

Creating an Instance Using ORADIM

To use ORADIM to create an instance, enter:

oradim [-NEW -SID SID] | -SRVC service_name | -ASMSID SID | -ASMSRVC service_name 
[-SYSPWD password][-MAXUSERS number][-STARTMODE auto | manual] [-SRVCSTART system | demand] 
[-PFILE filename | -SPFILE] [-SHUTMODE normal | immediate | abort] [-TIMEOUT 
secs] [-RUNAS osusr[/ospass]]

For this command, note the following:

  • -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 (OracleServiceSID).

  • -ASMSID SID is the name of the Oracle Automatic Storage Management instance to create.

  • -ASMSRVC service_name is the name of the Oracle Automatic Storage Management service to create.

  • -SYSPWD password is the system password.

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

  • -STARTMODE auto | manual indicates whether to start the instance when the Oracle Database service is started. The default is manual.

  • -SRVCSTART system | demand indicates whether to start the Oracle Database service upon computer restart. Default is demand. Here, system specifies that the service be configured to automatically start when the system boots or reboots. Demand specifies that the user has to explicitly start the service.

  • -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 the drive letter.

  • -SPFILE indicates that a server parameter file (SPFILE) be used during startup instead of a PFILE.

  • -SHUTMODE normal|immediate|abort 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.

    -SHUTMODE requires an argument and the default is immediate. If SHUTMODE is omitted, then there is no attempt made to shutdown the instance when the service is shut down.

  • -TIMEOUT secs sets the maximum time to wait (in seconds) before the service for a particular SID stops. The default is 90 seconds. It cannot be used without the SHUTDOWN argument.

  • -RUNAS osusr[/ospass] ("run as") makes it possible to specify both the Oracle Home User and its password. If the given osusr is different from the Oracle Home User, then the Oracle Home User is used instead of the osusr along with the given ospass.

    Though the ospass can be specified on the command line, Oracle recommends accepting ospass through stdin

    Starting with Oracle Database 12c Release 1 (12.1), ORADIM creates Oracle Database service, Oracle VSS Writer service, and Oracle Scheduler service to run under the Oracle Home User account. If this account is a Windows Local User Account or Windows Domain User Account, then ORADIM prompts for the password for that account and accepts the same through stdin.

Note:

For simplicity in demonstrating this feature, this example does not perform the password management techniques that a deployed system typically uses. In a production environment, follow the Oracle Database password management guidelines, and disable any sample accounts.

See Oracle Database Security Guide for password management guidelines and other security recommendations.

To create an instance called PROD, for example, you can enter:

C:\> oradim -NEW -SID prod -STARTMODE auto -PFILE C:\app\username\admin\prod\pfile\init.ora

Starting an Instance and Services Using ORADIM

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]

For this command, note the following:

  • -STARTUP indicates that you are starting an instance that already exists. This is a mandatory parameter.

  • -SID SID is the name of the instance to start.

  • -ASMSID SID is the name of the Oracle Automatic Storage Management 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 oracleservicesid 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:\app\username\admin\prod\pfile\init.ora

Stopping an Instance and Services Using ORADIM

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]

For this command, note the following:

  • -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 Oracle Automatic Storage Management 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

Editing an Instance Using ORADIM

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]

For this command, note the following:

  • -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 Oracle Automatic Storage Management instance to modify.

  • -STARTMODE indicates whether to start the instance when the Oracle Database service is started. The default is manual.

  • -SRVCSTART system | demand indicates whether to start the Oracle Database service on computer restart. The default is demand.

  • -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 the 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:\app\username\product\11.2.0\admin\lynx\pfile\init.ora

Deleting an Instance Using ORADIM

To use ORADIM to delete an instance, enter:

oradim -DELETE -SID SID | -ASMSID SID | -SRVC service_name | -ASMSRVC service_name

For this command, note the following:

  • -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 (OracleServiceSID). The user should specify either SID or SRVC.

  • -ASMSID SID is the name of the Oracle Automatic Storage Management instance to delete.

  • -ASMSRVC service_name is the name of the Oracle Automatic Storage Management service to delete.

To delete an instance called prod, for example, you can enter:

C:\> oradim -DELETE -SID prod

Overview of Database Migration from a 32-Bit Windows Computer

This section contains these topics:

See Also:

Oracle Database Upgrade Guide for information about upgrading an earlier release of Oracle Database to Oracle Database 12c Release 1 (12.1)

Backing Up a 32-Bit Oracle Database

To back up a 32-bit Oracle home database:

  1. Start SQL*Plus:

    C:\> sqlplus /NOLOG
    
  2. Connect to the database instance as SYSDBA:

    SQL> CONNECT / AS SYSDBA;
    
  3. 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;
    
  4. Shut down the database:

    SQL> SHUTDOWN IMMEDIATE;
    
  5. Perform a full offline backup of the database.

See Also:

Oracle Database Backup and Recovery User's Guide for an overview of backup and recovery solutions

Migration Considerations

While upgrading an ASM disk group from Oracle Database 11g to Oracle Database 12c Release 1 (12.1) on Windows platforms, all current files on the disk group are shown as being accessible to all users. Thus, a user with SYSASM privileges must modify the user ownership, group membership, and permissions of the current files accordingly, so that the files are owned by their respective database users only.

Migrating an Oracle Database 11g Release 2 (11.2) or Earlier Database

To migrate Oracle Database 11g Release 2 (11.2) or earlier database for 32-bit Windows to Oracle Database 12c Release 1 (12.1) for 64-bit Windows, perform the following steps:

  1. Install Oracle Database 12c Release 1 (12.1) for 64-bit Windows.

  2. Create the new Oracle Database 12c Release 1 (12.1) 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 migrating.
    PASSWORD Password for the new Oracle Database 12c Release 1 (12.1) for 64-bit Windows database. This is the password for the user connected with the 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 the drive letter of the Oracle home directory.

  3. Copy the 32-bit data files to the new 64-bit Oracle home.

  4. Copy the 32-bit configuration files to the 64-bit Oracle home.

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

    2. 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_HOME\database\pwdSID.ora., where SID is your Oracle instance ID.

  5. If 12.1 Oracle home uses a Windows User Account as the Oracle Home User, then add this Oracle Home User to the list of users and grant file permissions or directory permissions for all the database files, init.ora files, oracle password files and so on.

  6. Add the _SYSTEM_TRIG_ENABLED = false parameter to the ORACLE_HOME\database\ORACLE_SID \init.ora file in the 64-bit Oracle home before changing the word size.

  7. 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 size
  8. Go to the 64-bit ORACLE_HOME\rdbms\admin directory from the command prompt.

  9. Start SQL*Plus:

    C:\> sqlplus /NOLOG 
    
  10. Connect to the database instance as SYSDBA:

    SQL> CONNECT / AS SYSDBA;
    
  11. 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 data files, log files, and control files to point to the Oracle home on the 64-bit computer. This creates the new control file in the ORACLE_HOME\database directory.

    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:\app\username\oradata\orcl64\REDO03.LOG'  SIZE 1M, 
        # was   'C:\app\username\oradata\orcl32\...LOG' 
        # on the 32-bit computer
        GROUP 2 'C:\app\username\oradata\orcl64\REDO02.LOG'  SIZE 1M,
        GROUP 3 'C:\app\username\oradata\orcl64\REDO01.LOG'  SIZE 1M
    DATAFILE
       'C:\app\username\oradata\orcl64\SYSTEM01.DBF',
        # was 'C:\app\username\oradata\orcl32\...DBF' 
        # on the 32-bit computer
       'C:\app\username\oradata\orcl64\RBS01.DBF',
       'C:\app\username\oradata\orcl64\USERS01.DBF',
       'C:\app\username\oradata\orcl64\TEMP01.DBF',
       'C:\app\username\oradata\orcl64\TOOLS01.DBF',
       'C:\app\username\oradata\orcl64\INDX01.DBF',
       'C:\app\username\oradata\orcl64\DR01.DBF'
    CHARACTER SET WE8ISO8859P1;
    
  12. Alter the init file from the 32-bit computer to include the new control file generated in the preceding step.

  13. Shut down the database on the 64-bit computer:

    SQL> SHUTDOWN IMMEDIATE;
    
  14. Start the database migration:

    SQL> STARTUP MIGRATE;
    
  15. Migrate the database as described in Chapter 3, "Upgrading Oracle Database" in Oracle Database Upgrade Guide.

    Note:

    Upgrading to Oracle Database 12c Release 1 (12.1) is supported only when the same Windows User Account is used as the Oracle Home User in both the source and destination Oracle homes, or when the home from which the database is being upgraded uses a Windows built-in account.

    See Oracle Database Upgrade Guide for information about prerequisites for preparing a new Oracle home on Windows.

  16. Shut down the database:

    SQL> SHUTDOWN IMMEDIATE;
    
  17. Restart the database:

    SQL> STARTUP OPEN;