How To Create Oracle Service In Windows Manually

7/27

3 Postinstallation Database Creation on Windows

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

This affiliate contains these topics:

• Naming Conventions for Oracle Database

• Creating a Database on Windows Using Database Configuration Assistant

• Creating a Database on Windows Using Command-Line Tools

• Using ORADIM to Administer an Oracle Database Instance

• Database Migration from a 32-bit Windows Reckoner

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 information technology and stored in its control files. If yous 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 mountain 2 Oracle Database servers with the same database proper name, then you receive the post-obit mistake during mounting of the second server:

ORA-01102: cannot mountain database in EXCLUSIVE mode            

If at that place are ii or more than Oracle Database servers on the same reckoner, but located in different Oracle homes, so the following rules apply:

• Each database name must be unique

• Each SID must exist unique

To modify the proper noun of an existing database, you lot must use the CREATE CONTROLFILE statement to re-create your command files and specify a new database name.

Creating a Database on Windows Using Database Configuration Assistant

Oracle recommends y'all 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.

Database Configuration Banana 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 change 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 non recognize the renamed file every bit an initialization parameter file, and information technology is not used later the instance is started.

If you want to alter an instance created with Database Configuration Assistant afterwards information technology starts, you lot must use ALTER System statements. You cannot alter 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 file name is spfile SID .ora.

Creating a Database on Windows Using Command-Line Tools

This section describes how to create a new database manually. As role 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 DBCA.

Database creations are of three types:

• Copy an existing database and delete the old database.

• Re-create an existing database and go on the sometime database.

• Create a new database when no database exists on your system.

Tabular array iii-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 Cosmos Tasks

Task	Copy existing database and delete onetime database	Copy existing database and keep old database	Create new database when no database exists on organisation
Exporting an Existing Database	Yes	Notation 1	Non applicable
Deleting Database Files	Yes	No	Not applicable
Modifying the Initialization Parameter File	Yeah	Yes	Yes
Starting an Oracle Database Instance	Yes	Yes	Yes
Creating and Starting an Oracle Database Service	No	Yes	Yep
Putting the CREATE DATABASE Statement in a Script	Yes	Yes	Aye
the CREATE DATABASE Script	Yes	Aye	Yeah
Importing a Database	Aye	Notation two	Non applicative
Updating ORACLE_SID in the Registry	No	Only if you change the default SID	Yeah
Bankroll Up the New Database	Yes	Yes	Aye

Note ane

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

Note 2

Yep if you import tables and other objects exported from the existing database; not otherwise.

We use an instance 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:\app\ username \product\eleven.2.0\oradata\orcl. You will copy orcl to a new database with a database proper noun and SID of prod located in directory C:\app\ username \product\eleven.2.0\oradata\prod. You will then delete starter database orcl.

Exporting an Existing Database

You are required to consign an existing database only if you intend to copy its contents to a new database. If yous are working with data from an earlier Oracle release, then you can utilise Export for this task. If you are using Oracle Database xthou Release 1 (10.1) or afterward data, then Oracle recommends that you use Information Pump Consign considering it supports new Oracle Database 10g Release 1 (10.one) or later features, such as floating points.

Although you can start Data Pump Export or Export in either parameter manner or interactive manner, 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 Total=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 y'all for information it needs.

Note:

If you utilize parameter fashion, then Data Pump Export considers file names and directory names to be invalid if they comprise one or more blank spaces. The workaround is to enclose the total path in the DUMPFILE= parameter in triple quotation marks. For example:

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

If Information 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 style is:

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

The syntax for Export interactive way is:

C:\> exp Organisation Password:                password              

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

Note:

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

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

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

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 consign. For example, if the database you intend to export is starter database orcl, and then enter the following at the command prompt. Note that there are no spaces around the equal sign (=) character.

   C:\> ready ORACLE_SID=orcl                  

2. If the existing database is Oracle Database 10g Release 1 (x.i) or later, and then start Data Pump Export from the command prompt:

   C:\> expdp Organisation DUMPFILE=myexp.dmp Total=y LOG=myexp.log Countersign:                    password                  

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

3. If the existing database is before Oracle Database 10yard Release one (ten.1), then offset Consign from the command prompt:

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

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

Deleting Database Files

Deleting database files is required only when you re-create an existing database to a new database to supplant the old database. In the following case, you lot delete the database files of starter database orcl.

To delete database files:

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

   C:\> oradim -SHUTDOWN -SID orcl -SHUTTYPE inst -SHUTMODE immediate                  

2. Delete the following files from directory C:\app\ username \product\xi.2.0\oradata\orcl:

   File Proper name	File Name	File Proper 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 starter database orcl every bit the basis for your new database, first copy ORACLE_BASE \admin\orcl\pfile\init.ora. 2nd, put the copy in ORACLE_BASE \admin\prod\pfile\init.ora. 3rd, modify the file as described in this department.

Note:

Outset with Oracle9i Release two (nine.2), nesting of quotation marks using the backslash (\) escape grapheme is no longer supported. This may bear upon how Oracle Database interprets parameter values in your initialization parameter file. For example, if you lot specified CONTROL_FILES = "ctlfile\'1.ora" in releases before release nine.2, the file name would be interpreted as ctlfile'1.ora. Starting with release 9.2, the file name would be interpreted as ctlfile\'ane.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 lot practice not have an existing database on your organisation, so you cannot re-create an existing initialization parameter file to apply as the basis for your new initialization parameter file. Notwithstanding, y'all tin employ the sample initialization parameter file initsmpl.ora provided in

                ORACLE_HOME\admin\sample\pfile              

every bit the basis for the initialization parameter file for database prod.

If yous utilise initsmpl.ora every bit the basis for the initialization parameter file, then you must gear up the following parameters to the indicated values, or you will not be able to commencement database prod:

• DB_NAME=prod.domain

  Parameter DB_NAME indicates the database proper noun and must match the proper name used in the CREATE DATABASE statement in "Putting the CREATE DATABASE Statement in a Script". You give a unique database proper name to each database. You can use up to viii characters for a database proper name. The proper 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 \product\11.2.0\oradata\prod\control01.ctl", "C:\app\ username \product\xi.ii.0\oradata\prod\control02.ctl", "C:\app\ username \product\xi.2.0\oradata\prod\control03.ctl")

  Parameter CONTROL_FILES lists database control files. You lot practise non have command files on your file arrangement at this point, because control files are created when yous run the CREATE DATABASE statement. Ensure that you specify the complete path and file name, including drive letter of the alphabet.

• DB_FILES=100

  Modifying initialization parameter DB_FILES is not required, simply information technology is recommended to optimize operation. Set this parameter to the same number as the value of the MAXDATAFILES option of the CREATE DATABASE argument. The value of 100 is used for this example.

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

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

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

Creating and Starting an Oracle Database Service

You are required to create and start an Oracle Database service simply if you do 1 of the following:

• Re-create an existing database to a new database and go along the former database

• Create a new database when you have no other database to copy

Before you create the database, get-go 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.

Employ ORADIM to create the service. Later information technology has been created, the service starts automatically. See "Using ORADIM to Administrate an Oracle Database Instance" for information on how to use ORADIM.

To create and start an Oracle Database service:

1. Run ORADIM from the command prompt:

   C:\> oradim -NEW -SID prod -STARTMODE transmission -PFILE "C:\app\oracle\product\11.ii.0\admin\prod\pfile\init.ora"                  

   Note that the previously created initialization parameter file is specified, with consummate path, including drive proper name. You can check if the service is started in the Services window of the Control Console.

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

   C:\> fix ORACLE_SID=prod                  

Starting an Oracle Database Example

First an instance without mounting a database.

SQL> STARTUP NOMOUNT              

You are non required to specify the PFILE clause in this example, because the initialization parameter file is stored in the default location. At this betoken, there is no database. Merely the SGA is created and groundwork 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 may have the following parameters:

• MAXDATAFILES - default value: 32, maximum value: 65534

• MAXLOGFILES - default value: 32, maximum value: 255

When yous run a CREATE DATABASE argument, Oracle Database performs several operations depending upon clauses that you specified in the CREATE DATABASE statement or initialization parameters that y'all accept prepare.

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 arrangement files comprising an Oracle Database server, because you specify operations in terms of database objects rather than file names. For more than information on using Oracle Managed Files see Oracle Database Administrator'south Guide.

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

CREATE DATABASE prod USER SYS IDENTIFIED BY                sys_password                USER Organization IDENTIFIED BY                system_password                MAXLOGFILES 5 MAXDATAFILES 100 DATAFILE 'C:\app\oracle\product\11.ii.0\oradata\prod\system01.dbf' SIZE 325M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED Undo TABLESPACE "UNDOTBS" DATAFILE 'app\oracle\product\11.2.0\oradata\prod\undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED CHARACTER Prepare WE8MSWIN1252 logfile 'C:\app\username\product\eleven.ii.0\oradata\prod\redo01.log' size 100M reuse,         'C:\app\username\product\xi.ii.0\oradata\prod\redo02.log' size 100M reuse,         'C:\app\username\production\11.ii.0\oradata\prod\redo03.log' size 100M reuse EXTENT Management LOCAL;              

the CREATE DATABASE Script

To employ the SQL script to create a database:

1. Verify that the service is started in the Command Panel. In this case, the service proper noun is OracleServicePROD, and its status column must display Started. If not, then select the service proper noun and choose Beginning.

   Yous tin also check the status of the service by inbound the following at the control prompt:

   C:\> net Kickoff                  

   A list of all Windows services currently on the arrangement appears. If OracleServicePROD is missing from the list, and so enter:

   C:\> net START OracleServicePROD                  

2. Make PROD the electric current SID :

   C:\> set up ORACLE_SID=PROD                  

3. Add ORACLE_HOME \bin to your PATH environs variable:

   set up PATH=ORACLE_BASE\ORACLE_HOME\bin;%PATH%                  

4. Start SQL*Plus from the control prompt, and connect to the database as SYSDBA:

   C:\> sqlplus /NOLOG SQL> CONNECT / Every bit SYSDBA                  

   The message continued appears.

5. Turn on spooling to salve letters:

   SQL> SPOOL                    script_name.log                  

6. Run script script_name .sql that you created in "Putting the CREATE DATABASE Argument in a Script":

   SQL> C:\app\oracle\product\11.two.0\dbhome_1\rdbms\admin\script_name.sql;                  

   If the database is successfully created, and so the instance is started and the post-obit bulletin appears numerous times: Argument processed

Importing a Database

You tin use Information Pump Import (for Oracle Database xm Release 1 (10.1) or later information) or Import (for earlier data) to import the full consign created in "Exporting an Existing Database" into the new database. Although y'all tin can start Data Pump Import or Import using either parameter way or interactive mode, Oracle recommends parameter mode because information technology provides more than functionality. Interactive mode exists solely for backward compatibility.

The syntax for Data Pump Import parameter way is:

C:\> impdp SYSTEM DUMPFILE=myexp.dmp FULL=y LOG=myexp.log Password:                password              

The syntax for Information Pump Import interactive mode is:

C:\> impdp SYSTEM  Password:                password              

Enter only impdp SYSTEM to begin an interactive session and permit Information Pump Import prompt you for data it needs.

Note:

• If you use parameter mode, then Data Pump Import considers file names and directory names to be invalid if they contain ane 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 lot use Data Pump Import in interactive way, and so the file proper noun or directory proper noun tin incorporate 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 datafiles.

  The like shooting fish in a barrel solution is to ensure that both databases comprise the aforementioned tablespaces. Datafiles are not required to exist identical. Only tablespace names are important.

Updating ORACLE_SID in the Registry

If this is the beginning database on the organization or if you intend to brand the new database the default database, then you must make a modify in the registry.

1. Start Registry Editor at the command prompt:

   C:\> regedit                  

   The Registry Editor window appears.

2. Cull subkey \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 for the offset Oracle home on your computer. For subsequent installations to different Oracle homes on the aforementioned computer, the path is \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\Abode ID where ID is the unique number identifying the Oracle abode.

3. Locate parameter ORACLE_SID on the right side of the Registry Editor window.

4. Double-click the parameter name and alter the information to the new SID , which is prod in this case.

If you do not yet have parameter ORACLE_SID, considering this is the get-go database on your system, then you must create it.

To create parameter ORACLE_SID:

1. Choose Add Value from the Edit menu.

   The Add Value dialog appears:

   
   Clarification of the illustration addvalue.gif

2. Enter ORACLE_SID in the Value Name field.

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

4. Click OK.

   A cord editor dialog appropriate for the data type appears:

   
   Description of the illustration stringre.gif

5. Enter prod in the String field.

6. Click OK.

   Registry Editor adds parameter ORACLE_SID.

7. Choose Get out from the Registry menu.

   Registry Editor exits.

Backing Upward the New Database

Caution:

If annihilation goes incorrect while operating the new database without a backup, then you must echo the database creation procedure. Back up your database at present to prevent loss of information.

To back upwards the new database:

1. Shut downwardly the database example and stop the service:

   C:\> oradim -SHUTDOWN -SID prod -SHUTTYPE srvc,inst -SHUTMODE immediate                  

   Caution:

   Although ORADIM returns the prompt immediately, you must await for the database and the service to end completely before continuing to Step 2. Look until the Control Panel indicates service OracleServicePROD has stopped. If you exercise not practise this, then the backup may be useless because it was taken while data was beingness written to datafiles.

2. Using the tool of your choice, support 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 once again, create users and objects, if necessary, make whatever other changes, and utilize the database.

   Exist sure to back up the database afterward making whatsoever significant changes, such as switching archiving manner or adding a tablespace or datafile.

Caution:

Do not store database files on a compressed bulldoze. This can upshot in write errors and decreased functioning.

Using ORADIM to Administrate an Oracle Database Instance

ORADIM is a command-line tool that is available with Oracle Database. You are required to use ORADIM merely if you are manually creating, deleting, or modifying databases. Database Configuration Assistant is an easier tool to use for this purpose.

The following sections depict ORADIM commands and parameters. Note that each control is preceded by a dash (-). To get a list of ORADIM parameters, enter:

oradim -? | -h | -aid            

Note:

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

When y'all 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 bank check this file to verify success of an operation.

If you lot have installed an Oracle Database service on Windows, then when logging in as SYSTEM user (LocalSystem), with startup fashion set to Automatic, information technology is possible that the Oracle Database service starts but the database does non start automatically. The following error message is written to file ORADIM.LOG in directory ORACLE_HOME \database:

ORA-12640: Hallmark adapter initialization failed            

Oracle Enterprise Direction Agent, Oracle Enterprise Manager Management Server and Oracle Internet Directory may also neglect, because they cannot connect to the database for the aforementioned reason. The workarounds are:

• Modify SQLNET.ORA

  You can modify SQLNET.ORA, either past removing the line

  sqlnet.authentication_services=(NTS)                

  or by irresolute it to

  sqlnet.authentication_services=(NONE)                

• Outset the database after the service starts

  Yous can start the database manually after the Oracle Database service has started, using SQL*Plus and connecting as SYSDBA.

• Start the service equally a specific user

  See Also:

  Your operating system documentation for instructions on starting services

Creating an Instance

To apply ORADIM to create an instance, enter:

oradim [-NEW -SID                SID] | -SRVC                service_name                | -ASMSID                SID                | -ASMSRVC                service_name                [-SYSPWD                countersign] [-STARTMODE motorcar | manual] [-SRVCSTART organization | 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 proper noun 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 Automated Storage Management instance to create.

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

• -SYSPWD password is the system password.

• -STARTMODE auto | manual indicates whether to kickoff the example when the Oracle Database service is started. Default is manual.

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

• -PFILE filename is the initialization parameter file to exist used with this example. Ensure that you specify the complete path proper 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 terminate an instance. It requires an argument and the default is immediate. If SHUTMODE is omitted, and so there is no attempt fabricated to shutdown the example when the service is shutdown.

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

• -RUNAS osusr/ospass ("run every bit") sets the operating system user with which the Oracle service logs on to the organisation. You supply an operating organization user proper name and password, and the service logs on and runs with the privileges of that user. If omitted, the service logs on to the system using the Local organisation business relationship, which is a privileged user.

Annotation:

For simplicity in demonstrating this characteristic, this example does not perform the password direction techniques that a deployed organisation commonly uses. In a product environment, follow the Oracle Database password management guidelines, and disable any sample accounts. Run into Oracle Database Security Guide for password direction 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\oracle\product\11.2.0\admin\prod\pfile\init.ora              

Starting an Instance and Services

To use ORADIM to beginning an instance and services, enter:

oradim -STARTUP -SID                SID                | -ASMSID                SID                [-SYSPWD                countersign] [-STARTTYPE srvc |  inst | srvc,inst] [-PFILE filename | -SPFILE]              

where

• -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 outset.

• -ASMSID SID is the proper noun of the Automated Storage Direction example to start.

• -STARTTYPE srvc, inst indicates whether to outset the service or the instance. One or both values can be specified. If it is non specified, then the registry is checked for the current setting.

  -STARTTYPE srvc is equivalent to net start oracleservice<sid> from the command line.

  -STARTTYPE inst is equivalent of startup within SQL*Plus.

• -PFILE filename is the initialization parameter file to be used with this instance. Ensure that you lot specify the consummate path name of this file, including drive alphabetic character.

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

To start an instance called puma, for instance, you can enter:

C:\> oradim -STARTUP -SID puma -STARTTYPE inst -PFILE C:\app\oracle\product\eleven.2.0\admin\prod\pfile\init.ora              

Stopping an Instance and Services

The Enterprise Database Control service (OracleDBConsole SID ) is dependent on the Oracle Database service (OracleService SID ). Yous must stop the dependent Enterprise Database Command service (if installed) before ORADIM to cease the database example service.

To use ORADIM to terminate an example, enter:

oradim -SHUTDOWN -SID                SID                | -ASMSID                SID                [-SYSPWD password]  [-SHUTTYPE srvc | inst | srvc,inst] [-SHUTMODE normal | immediate | arrest]              

where

• -SHUTDOWN indicates that you are stopping an example. This is a mandatory parameter.

• -SID SID specifies the proper name of the instance to cease.

• -ASMSID SID is the name of the Automated Storage Management instance to stop.

• -SHUTTYPE srvc, inst indicates whether to stop the service or the instance. One or both values tin exist 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, and then immediate is the default manner.

To stop an instance chosen puma, for case, you lot tin enter:

C:\> oradim -SHUTDOWN -SID puma -SHUTTYPE srvc,inst              

Editing an Instance

You can edit an existing instance to change such values as instance name, startup mode, shutdown mode, and shutdown type. To utilise ORADIM to change an instance, enter:

oradim -EDIT -SID                SID                | -ASMSID                SID                [-SYSPWD                password] [-STARTMODE auto |  transmission] [-SRVCSTART system | demand] [-PFILE                filename                | -SPFILE][SHUTMODE normal  | immediate | arrest] [SHUTTYPE srvc | inst | srvc,inst]              

where

• -EDIT indicates that y'all 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 proper noun of the Automatic Storage Management case to alter.

• -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 calculator restart. Default is demand.

• -PFILE filename specifies the initialization parameter file to exist used with this instance. Ensure that you lot specify the complete path name of this file, including drive letter.

• -SPFILE indicates that a server parameter file (SPFILE) exist 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 cease an instance, then immediate is the default mode.

• -SHUTTYPE indicates whether to cease 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 case, you can enter:

C:\> oradim -EDIT -SID prod -PFILE C:\app\oracle\product\11.2.0\admin\lynx\pfile\init.ora              

Deleting an Case

The Enterprise Database Control service (OracleDBConsole SID ) is dependent on the Oracle Database service (OracleService SID ). You must end the dependent Enterprise Database Control service (if installed) before ORADIM to delete the database example.

To utilise 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 example or service. This is a mandatory parameter.

• -SID SID specifies the name of the SID to delete.

• -SRVC service_name specifies the proper noun of the service to delete (OracleServiceSID). User should specify either SID or SRVC.

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

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

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

C:\> oradim -DELETE -SID prod              

Database Migration from a 32-bit Windows Figurer

This section contains these topics:

• Backing Up a 32-Chip Oracle Database

• Migrating an Oracle Database 11g Release ii (11.2)

• Migrating an Oracle Database 10g Release 2 (10.ii) or Earlier Database

See As well:

Oracle Database Upgrade Guide for information near upgrading an earlier release of Oracle Database to Oracle Database elevengrand Release 2 (11.two)

Backing Up a 32-Chip Oracle Database

To support a 32-flake Oracle home database:

1. Start SQL*Plus:

   C:\> sqlplus /NOLOG                  

2. Connect to the database instance every bit SYSDBA:

   SQL> CONNECT / Equally SYSDBA;                  

3. Create a.trc file to use as a template to re-create the control files on the 64-bit computer:

   SQL> Change DATABASE Backup CONTROLFILE TO TRACE;                  

4. Close down the database:

   SQL> SHUTDOWN IMMEDIATE;                  

5. Perform a full offline backup of the database.

Migrating an Oracle Database xig Release 2 (11.two)

To migrate an Oracle Database elevenyard Release 2 (11.2) for 32-chip Windows to an Oracle Database eleveng Release 2 (eleven.2) for 64-bit Windows:

1. Install Oracle Database 11g Release two (11.two) for 64-bit Windows.

2. Create the new Oracle Database 11yard Release 2 (11.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 on the values yous must supply.

   Parameter	Description
   SID	SID of the database you are migrating
   PASSWORD	Password for the new Oracle Database 11g Release 2 (eleven.2) for 64-scrap Windows database. This is the countersign for the user connected with SYSDBA privileges. The -INTPWD pick is not required. If you exercise not specify it, and 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 y'all specify the full path name with the -PFILE pick, including drive letter of the Oracle home directory.

   
   

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

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

   1. If your 32-bit initialization parameter file has an IFILE (include file) entry, and so copy the file specified past the IFILE entry to the 64-bit Oracle dwelling house and edit the IFILE entry in the initialization parameter file to point to its new location.

   2. If you take a countersign file that resides in the 32-bit Oracle home, and so copy the password file to the 64-bit Oracle domicile. The default 32-bit password file is located in ORACLE_HOME \database\pwd SID .ora., where SID is your Oracle instance ID.

5. In the 64-bit Oracle domicile, add the _SYSTEM_TRIG_ENABLED = fake parameter to the ORACLE_HOME \database\ORACLE_ SID \init.ora file before changing the word size.

6. Remove this parameter from the initialization file subsequently the discussion size change is complete.

7. Go to the 64-bit ORACLE_HOME \rdbms\admin directory from the command prompt.

8. Offset SQL*Plus:

   C:\> sqlplus /NOLOG                  

9. Connect to the database instance equally SYSDBA:

   SQL> CONNECT / Equally SYSDBA;                  

10. Re-create the 64-scrap control files using the CREATE CONTROLFILE control. Edit the trace file created in "Backing Up a 32-Chip Oracle Database" to change the paths to the datafiles, log files and control files to betoken to the Oracle domicile on the 64-bit calculator. This creates the new control file in ORACLE_HOME \database.

    Here is an case of a database named orcl32 on a 32-bit computer migrating to orcl64 on a 64-scrap computer:

    CREATE CONTROLFILE REUSE DATABASE "T1" NORESETLOGS NOARCHIVELOG     MAXLOGFILES 32     MAXLOGMEMBERS 2     MAXDATAFILES 32     MAXINSTANCES sixteen     MAXLOGHISTORY 1815 LOGFILE     GROUP one 'C:\app\oracle\product\xi.2.0\oradata\orcl64\REDO03.LOG'  SIZE 1M,      # was   'C:\app\oracle\production\11.2.0\oradata\orcl32\...LOG'      # on the 32-bit computer     Grouping 2 'C:\app\oracle\product\eleven.2.0\oradata\orcl64\REDO02.LOG'  SIZE 1M,     Group 3 'C:\app\oracle\production\11.2.0\oradata\orcl64\REDO01.LOG'  SIZE 1M DATAFILE    'C:\app\oracle\production\11.2.0\oradata\orcl64\SYSTEM01.DBF',     # was 'C:\app\oracle\product\xi.ii.0\oradata\orcl32\...DBF'      # on the 32-bit computer    'C:\app\oracle\production\11.two.0\oradata\orcl64\RBS01.DBF',    'C:\app\oracle\product\eleven.two.0\oradata\orcl64\USERS01.DBF',    'C:\app\oracle\product\xi.2.0\oradata\orcl64\TEMP01.DBF',    'C:\app\oracle\product\11.two.0\oradata\orcl64\TOOLS01.DBF',    'C:\app\oracle\product\11.ii.0\oradata\orcl64\INDX01.DBF',    'C:\app\oracle\product\11.ii.0\oradata\orcl64\DR01.DBF' Character Gear up WE8ISO8859P1;                  

11. Modify the init file from the 32-bit computer to include the new control file generated in the preceding step.

12. Shut down the database:

    SQL> SHUTDOWN Immediate;                  

13. Starting time the database in UPGRADE mode to run utlirp.sql:

    SQL> STARTUP UPGRADE;                  

    Yous must utilize the PFILE option to specify the location of your initialization parameter file.

14. Set the system to spool results to a log file for later verification of success. For example:

    SQL> SPOOL mig32-64.log;                  

15. Enter the post-obit command to view the output of the script on-screen:

    SQL> SET Echo ON;                  

16. Recompile existing PL/SQL modules in the format required by the 64-scrap Oracle Database:

    SQL> @utlirp.sql;                  

17. Turn off the spooling of script results to the log file:

    SQL> SPOOL OFF;                  

18. Check the spool file and verify that the packages and procedures compiled successfully. Correct any problems you find in this file.

19. Shut down the database:

    SQL> SHUTDOWN Firsthand;                  

20. Showtime the database:

    SQL> STARTUP;                  

21. If the JServer JAVA Virtual Machine component is installed, perform the post-obit steps later connecting as SYS:

    begin   update obj$ set status=5 where obj#=(select obj# from obj$,javasnm$     where owner#=0 and type#=29 and brusk(+)=name and     nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler');   commit;   declare     cursor C1 is select        'DROP JAVA DATA "' || u.proper name || '"."' || o.name || '"'        from obj$ o,user$ u where o.type#=56 and u.user#=o.owner#;      ddl_statement varchar2(200);     iterations number;     previous_iterations number;     loop_count number;     my_err     number;   brainstorm     previous_iterations := 10000000;     loop       -- To make certain we eventually cease, selection a max number of iterations       select count(*) into iterations from obj$ where blazon#=56;       exit when iterations=0 or iterations >= previous_iterations;       previous_iterations := iterations;       loop_count := 0;       open C1;      loop        begin          fetch C1 into ddl_statement;          exit when C1%NOTFOUND or loop_count > iterations;       exception when others then          my_err := sqlcode;          if my_err = -1555 then -- snapshot besides former, re-execute fetch query            get out;          else            heighten;         end if;       end;       initjvmaux.exec(ddl_statement);       loop_count := loop_count + 1;      finish loop;      close C1;     end loop;   end;   commit;   initjvmaux.drp('delete from java$policy$shared$table');   update obj$ set status=ane where obj#=(select obj# from obj$,javasnm$      where owner#=0 and blazon#=29 and curt(+)=proper noun and     nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler');   commit; end; / create or supercede java system; /                  

22. Recompile existing PL/SQL modules in the format required by the 64-bit Oracle Database:

    SQL> @utlrp.sql;                  

Migrating an Oracle Database 10g Release 2 (10.2) or Before Database

To migrate an Oracle Database 10k Release 2 (10.2) to an Oracle Database 11g Release ii (11.2) for 64-bit Windows:

1. Perform steps 1 - 11 in "Migrating an Oracle Database 11yard Release 2 (11.ii)".

2. Shut down the database on the 64-scrap computer:

   SQL> SHUTDOWN Immediate;                  

3. Kickoff the database migration:

   SQL> STARTUP Drift;                  

4. Migrate the database equally described in Chapter 3, "Upgrading to the New Release" in Oracle Database Upgrade Guide.

5. Shut downward the database:

   SQL> SHUTDOWN Immediate;                  

6. Restart the database:

   SQL> STARTUP Open up;                  

Source: https://docs.oracle.com/cd/E11882_01/win.112/e10845/create.htm

Posted by: boxainge1980.blogspot.com

Share this post