J Recovering Database 11.1.0.7/10.2.0.5 from Microsoft Windows 32 to Microsoft Windows 64

This appendix is based on note:

How To Change Oracle 11g Wordsize from 32-bit to 64-bit. [ID 548978.1]

For information on repository recovery, refer to How To Change Oracle 11g Wordsize from 32-bit to 64-bit [ID 548978.1].

In case of some olap data in win32, at the end of step 0, follow the steps mentioned in Note [ID 386990.1].

This appendix contains the following sections:

On Source Host (Microsoft Windows 32)
On Destination Host (Microsoft Windows 64)
Final Steps

On Source Host (Microsoft Windows 32)

To recover Database 11.1.0.7/10.2.0.5 from Microsoft Windows 32 to Microsoft Windows 64, on source host follow these steps:

Go to DB Oracle Home <DB_HOME>\bin\sqlplus /NOLOG
Run the following command:

SQL> CONNECT / AS SYSDBA :

Note:
If you are using 10.2.0.5 Database, go to <DB HOME>/admin/<SID>/udump and make a backup of this in your local system.
Run the following command:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Note:
If you are using 10.2.0.5 Database, the trace file will be generated in <DB HOME>/admin/<SID>/udump. Now compare the files with the backed up <DB HOME>/admin/<SID>/udump. You will find some new trace files. One of the new trace files will contain the steps which will be performed later in Win64 host.

Note:
If you are using 11.1 Database, the command creates a new <sid>_ora_XX.trc file in <DB_BASE>\diag\rdbms\<SID>\<SID>\trace\<sid>_ora_xx.trc. For example:
C:\DB\diag\rdbms\orcl\orcl\traceorcl_ora_3832.trc

This file can be used to create the control file in the destination host.

This file can be identified by the <DB_BASE>\diag\rdbms\<SID>\<SID>\trace\alert_<Sid>.log

For information about the generated trace file, go to the last few lines.

Next, go to the console, where you were following the steps for upgrade. Select "Provide Repository Back up details" and then provide the date and time of creation of the trace file which you just identified.
Perform a full offline database backup (optional).

Note:
If you are using 10.2.0.5 Database, run the following command:
<OMS_HOME>\bin>

emctl config emkey -copy_to_repos -sysman_pwd <sysman_password>

For example:

C:\Oracle\Middleware\oms11g\BIN>emctl config emkey -copy_to_repos -sysman_pwd Welcome1
Run the following command:

SQL> SHUTDOWN IMMEDIATE;

STARTUP;

Note:
STARTUP command should also be run after Step 6 on the destination host.

On Destination Host (Microsoft Windows 64)

To recover Database 11.1.0.7/10.2.0.5 from Microsoft Windows 32 to Microsoft Windows 64, on the destination host follow these steps:

Install only the software on the computer.

Note:
Please make sure that the Database being installed here has the same path name as the Database on win32 host. This ensures speedy recovery.
Copy the entire <DB_Base>\oradata directory from Microsoft Windows 32 to <DB_Base>\ directory.
Run the following command:

<DB_HOME>\bin\oradim -new -sid <SID> -maxusers <no. of users> -startmode auto -pfile <DB_HOME>\database\init<SID>.ora

Note:
MAXUSERS may not work with Database 10.2.0.5
```
For example:

C:\DB\db\BIN>oradim.exe -new -sid orcl -maxusers 3 -startmode auto -pfile C:\DB\db\database\initorcl.ora
```
Note:
You may see an error here. This error can be ignored. However, make sure Instance is created. Also, init<SID>.ora will not be created now.
Copy the entire <DB_Base>\admin directory from win32 to the <DB_Base>\ directory.
Copy the entire <DB_Base>\fast_recovery_area directory from win32 to the <DB_Base>\ directory of win64.
Copy <DBHOME>\database\*.* files from win32 to <DBHOME>\database\ of win64.

Note:
If you are using Database 10.2.0.5, copy <DB_HOME>\dbs\SPFILE<SID>.ORA from win32 to the same location on win64. An example of this file is as follows:
C:\DB\db\dbs\SPFILEORCL.ORA

STARTUP;
Set the following environment variables:
- set ORACLE_HOME=<DB_home>
- set ORACLE_SID=<SID>
Go to the 64-bit DB_HOME\rdbms\admin directory.

Note:
If you cannot find sqlplus inside this, then go to <DB_HOME>\bin
Run the following command:

sqlplus /NOLOG
Connect /as sysdba.
Run the following command:

SQL> create pfile from spfile;
Note:
: If you are using Database10.2.0.5, run the following command:
create pfile='<DB_HOME>\database\init<SID>.ora' from SPFILE='<DB_HOME>\dbs\SPFILE<SID>.ORA';
```
For example:
create pfile='C:\DB\db\database\initorcl.ora' from SPFILE='C:\DB\db\dbs\SPFILEORCL.ORA';
```
Note:
If you are using Database 11.1.0.7, run the following command:
create pfile='<DB_HOME>\database\init<SID>.ora' from SPFILE='<DB_HOME>\database\SPFILE<SID>.ORA';
```
For example:
create pfile='C:\DB\db\database\initorcl.ora' from SPFILE='C:\DB\db\database\SPFILEORCL.ORA';
```
Edit <DB_HOME>\database\init<SID>.ora by adding _SYSTEM_TRIG_ENABLED=FALSE.
Run the following command:

SQL> create spfile from pfile

The spfile gets created in the same directory.
Run the following command:

SQL> startup nomount

The Instance starts as nomount.

Note:
You may receive a failure message as follows:
ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name '<listener name>'

To correct this you may need to edit pfile, spfile;

To edit the pfile and spfile, open '<DB_HOME>\database\init<SID>.ora' and delete the following line:

LOCAL_LISTENER

Next, run the following command:

Sql> create spfile from pfile

Go to the file <sid>_ora_xx.trc as identified in 0 of Microsoft Windows 32. Steps 15, 16 and 17 are to be carried out from this file. For your reference, example of these steps are as follows:

SQL > CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'C:\DB\ORADATA\ORCL\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'C:\DB\ORADATA\ORCL\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'C:\DB\ORADATA\ORCL\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE
  'C:\DB\ORADATA\ORCL\SYSTEM01.DBF',
  'C:\DB\ORADATA\ORCL\SYSAUX01.DBF',
  'C:\DB\ORADATA\ORCL\UNDOTBS01.DBF',
  'C:\DB\ORADATA\ORCL\USERS01.DBF',
  'C:\DB\ORADATA\ORCL\MGMT_ECM_DEPOT1.DBF',
  'C:\DB\ORADATA\ORCL\MGMT.DBF',
  'C:\DB\ORADATA\ORCL\MGMT_AD4J.DBF'
CHARACTER SET WE8MSWIN1252;

Note:

: In case you find an error here, try the following command:

SQL> RECOVER DATABASE;

Run the following command:

SQL>ALTER DATABASE OPEN;
Run the following command:

SQL>ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\DB\ORADATA\ORCL\TEMP01.DBF' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

This command updates the temp table space.
Run the following command:

SQL> SHUTDOWN IMMEDIATE;
Run the following command:

SQL> startup upgrade;
Run the following command:

SQL> SPOOL mig32-64.log;
Run the following command:

SQL> SET ECHO ON;
Run the following command:

SQL> @utlirp.sql;

Note:
If you are using sqlplus from <DB_HOME>\bin\ then run the following command:
SQL> @ <DB_HOME>\RDBMS\ADMIN\utlirp.sql

or

SQL> @ ?/RDBMS/ADMIN?utlirp.sql
Run the following command:

SQL> spool off;
Run the following command:

SQL> shutdown immediate;
Run the following command:

SQL> startup

Run the following script. To run the script paste it in SQL>prompt and press Enter.

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;
/

Run the following command:

SQL> @utlrp.sql

Note:
If you are using sqlplus from <DB_HOME>\bin\ then, run the following command:
SQL> @ <DB_HOME>\RDBMS\ADMIN\utlrp.sql

or

SQL> @ ?/RDBMS/ADMIN?utlrp.sql

If it succeeds, then continue to 0 Else, if you receive an error, do the following:
1. Run the following command:
  
  SQL> alter system reset "_system_trig_enabled" scope = spfile;
  
  If you see errrors, ignore them and continue to the next step.
2. Remove olap by running the following commands:
  
  SQL> @?/olap/admin/catnoamd.sql
  
  Note:
  If you get an error related to files not found then, for each of those files, use the following command:
  SQL>@C:\DB\db\olap\admin\<filename>.sql;
  
  However, even if you receive errors, you can just continue.
  
  SQL> @?/olap/admin/catnoaps.sql
  
  SQL> @?/olap/admin/catnoaps.sql
  
  SQL> @?/olap/admin/olapidrp.plb
3. Add olap by running the following commands:
  
  SQL> connect SYS as SYSDBA
  
  SQL> spool olap.log
  
  SQL> @?/olap/admin/olap.sql SYSAUX TEMP;
4. Reenter the following command:
  
  SQl> @utlrp.sql
  
  Note:
  : If you are using sqlplus from <DB_HOME>\bin\ then, run the following command:
  SQL> @ <DB_HOME>\RDBMS\ADMIN\utlrp.sql
  
  or
  
  SQL> @ ?/RDBMS/ADMIN?utlrp.sql
5. Check for the following (optional):
  
  - Invalid OLAPSYS objects, by running the following command:
  
  SQL> select owner, object_name, object_type, status from dba_objects where status = 'INVALID' and OWNER = 'OLAPSYS' ;
  
  - OLAP components in the DBA_REGISTRY, by running the following commands:
  
  SQL> column comp_name format a35
  
  SQL> SELECT comp_name, status, substr(version,1,10) as version from dba_registry;
  
  SQL> Spool Off
Delete the line _SYSTEM_TRIG_ENABLED=FALSE in init<SID>.ora in step 11 and run the following commands:

SQL> ALTER SYSTEM RESET "_system_trig_enabled" SCOPE=SPFILE sid='*';

commit;

shutdown immediate;

startup;

Final Steps

To finalize the process, do the following:

Microsoft Windows 32

On Microsoft Windows 32 computer, do the following:

Go to <DB_HOME>\bin.

sqlplus "/as sysdba"

SQL>startup;
Run the following command:

<OMS_HOME>\bin>emctl config emkey -remove_from_repos

For example:

C:\Oracle\Middleware\oms11g\BIN>emctl config emkey -remove_from_repos
Go to <DB_HOME>\bin.

sqlplus "/as sysdba"

shutdown immediate;

startup;

Microsoft Windows 64

On Microsoft Windows 64 computer, do the following:

Start the listener, by running the following command:

<DB_HOME>\bin\listener.exe start

Alternatively, you can start the listener, by running the following command:

<DB_HOME>\bin\netca

This can be done before or after recovery.
If the listener does not start, perform the following commands with <SID>:

<DB_HOME>\bin\sqlplus "/as sysdba"

SQL>alter system register;

SQL>commit;

SQL>shutdown immediate;

SQL>startup;

Troubleshooting

If you still face any issues, follow these steps:

Note:

These steps have been tested with Database 10.2.0.5.

Run the following command:

<DB_HOME>\BIN\orapwd file=<DB_HOME>\database\PWDorcl.ora password=<enter_password> entries=30 FORCE=Y

You may need to add an entry in tnsnames.ora. An example of the entry is as follows:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = adc2111519.us.oracle.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.us.oracle.com)
    )
  )
 
Where, orcl is the SID.

Bounce the database from SQLPlus by running the following commands:

<DB_HOME>\bin\sqlplus "/as sysdba"

SQL>shutdown immediate;

SQL>startup;
Bounce the listener by running the following commands:

<DB_HOME>\bin\listener.exe stop

<DB_HOME>\bin\listener.exe start

<DB_HOME>\bin\listener.exe status (to be repeated till you see the service name)