3 Postinstallation Database Creation on Windows

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.

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.

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 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 starter database orcl in file myexp.dmp. All messages from Data Pump Export are logged in file myexp.log.

3. If the existing database is before 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 starter database orcl in file myexp.dmp. All messages from Export are logged in 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 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 directory C:\app\username\product\11.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

   
   

Modifying the Initialization Parameter File

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.

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

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 up to 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\product\11.2.0\oradata\prod\control01.ctl", "C:\app\username\product\11.2.0\oradata\prod\control02.ctl", "C:\app\username\product\11.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 file name, including drive letter.

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

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 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 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 manual
   -PFILE "C:\app\oracle\product\11.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.

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

   C:\> set ORACLE_SID=prod
   

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

To create 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\oracle\product\11.2.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 SET WE8MSWIN1252
logfile 'C:\app\username\product\11.2.0\oradata\prod\redo01.log' size 100M reuse,
        'C:\app\username\product\11.2.0\oradata\prod\redo02.log' size 100M reuse,
        'C:\app\username\product\11.2.0\oradata\prod\redo03.log' size 100M reuse
EXTENT MANAGEMENT LOCAL;

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 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 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 script script_name.sql that you created in "Putting the CREATE DATABASE Statement in a Script":

   SQL> C:\app\oracle\product\11.2.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

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

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.

1. Start Registry Editor at the command prompt:

   C:\> regedit
   

   The Registry Editor window appears.

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

   See Also:

   Chapter 15, "Configuration Parameters and the Registry" for more information on subkey locations for multiple Oracle homes

3. Locate 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.

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:

1. Choose Add Value from the Edit menu.

   The Add Value dialog appears:

   
   Description 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 list.

4. Click OK.

   A string 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 Exit from the Registry menu.

   Registry Editor exits.

Backing Up the New Database

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 may be useless because it was taken while data was being written to datafiles.

2. 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 on archiving, backup, and recovery

   • Oracle Database Concepts

   • Oracle Database Backup and Recovery Basics

   • Oracle Database 2 Day DBA

Creating an Instance

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

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

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

• -SYSPWD password is the system password.

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

• -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") sets the operating system user with which the Oracle service logs on to the system. You supply an operating system user 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 system account, which is a privileged user.

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 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 already exists. This is a mandatory parameter.

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

• -ASMSID SID is the name of the 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 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 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\oracle\product\11.2.0\admin\prod\pfile\init.ora

Stopping an Instance and Services

The Enterprise Database Control service (OracleDBConsoleSID) is dependent on the Oracle Database service (OracleServiceSID). You must stop the dependent Enterprise Database Control service (if installed) before 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 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

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 Automatic Storage Management 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. 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 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\oracle\product\11.2.0\admin\lynx\pfile\init.ora

Deleting an Instance

The Enterprise Database Control service (OracleDBConsoleSID) is dependent on the Oracle Database service (OracleServiceSID). You must stop the dependent Enterprise Database Control service (if installed) before 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 (OracleServiceSID). User should specify either SID or SRVC.

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

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

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

C:\> oradim -DELETE -SID prod

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.

Migrating an Oracle Database 11g Release 2 (11.2)

To migrate an Oracle Database 11g Release 2 (11.2) for 32-bit Windows to an Oracle Database 11g Release 2 (11.2) for 64-bit Windows:

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

   See Also:

   Oracle Database Installation Guide for Microsoft Windows

2. Create the new Oracle Database 11g 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 you must supply.

   Parameter	Description
   SID	SID of the database you are migrating
   PASSWORD	Password for the new Oracle Database 11g Release 2 (11.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.

   
   

3. Copy the 32-bit datafiles 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. 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.

6. Remove this parameter from the initialization file after the word size change is complete.

   See Also:

   Oracle Database Upgrade Guide for more information on changing word size

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

8. Start SQL*Plus:

   C:\> sqlplus /NOLOG 
   

9. Connect to the database instance as SYSDBA:

   SQL> CONNECT / AS SYSDBA;
   

10. 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:\app\oracle\product\11.2.0\oradata\orcl64\REDO03.LOG'  SIZE 1M, 
        # was   'C:\app\oracle\product\11.2.0\oradata\orcl32\...LOG' 
        # on the 32-bit computer
        GROUP 2 'C:\app\oracle\product\11.2.0\oradata\orcl64\REDO02.LOG'  SIZE 1M,
        GROUP 3 'C:\app\oracle\product\11.2.0\oradata\orcl64\REDO01.LOG'  SIZE 1M
    DATAFILE
       'C:\app\oracle\product\11.2.0\oradata\orcl64\SYSTEM01.DBF',
        # was 'C:\app\oracle\product\11.2.0\oradata\orcl32\...DBF' 
        # on the 32-bit computer
       'C:\app\oracle\product\11.2.0\oradata\orcl64\RBS01.DBF',
       'C:\app\oracle\product\11.2.0\oradata\orcl64\USERS01.DBF',
       'C:\app\oracle\product\11.2.0\oradata\orcl64\TEMP01.DBF',
       'C:\app\oracle\product\11.2.0\oradata\orcl64\TOOLS01.DBF',
       'C:\app\oracle\product\11.2.0\oradata\orcl64\INDX01.DBF',
       'C:\app\oracle\product\11.2.0\oradata\orcl64\DR01.DBF'
    CHARACTER SET WE8ISO8859P1;
    

11. Alter 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. Start the database in UPGRADE mode to run utlirp.sql:

    SQL> STARTUP UPGRADE;
    

    You must use 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 following 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-bit 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 IMMEDIATE;
    

20. Start the database:

    SQL> STARTUP;
    

21. If the JServer JAVA Virtual Machine component is installed, perform the following steps after connecting as SYS:

    begin
      update obj$ set status=5 where obj#=(select obj# from obj$,javasnm$
        where owner#=0 and type#=29 and short(+)=name and
        nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler');
      commit;
      declare
        cursor C1 is select
           'DROP JAVA DATA "' || u.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;
      begin
        previous_iterations := 10000000;
        loop
          -- To make sure we eventually stop, pick a max number of iterations
          select count(*) into iterations from obj$ where type#=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 too old, re-execute fetch query
               exit;
             else
               raise;
            end if;
          end;
          initjvmaux.exec(ddl_statement);
          loop_count := loop_count + 1;
         end loop;
         close C1;
        end loop;
      end;
      commit;
      initjvmaux.drp('delete from java$policy$shared$table');
      update obj$ set status=1 where obj#=(select obj# from obj$,javasnm$ 
        where owner#=0 and type#=29 and short(+)=name and
        nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler');
      commit;
    end;
    /
    create or replace 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 Earlier Database

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

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

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

   SQL> SHUTDOWN IMMEDIATE;
   

3. Start the database migration:

   SQL> STARTUP MIGRATE;
   

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

5. Shut down the database:

   SQL> SHUTDOWN IMMEDIATE;
   

6. Restart the database:

   SQL> STARTUP OPEN;