This appendix describes how you can recover the backed up Oracle Database 11g Release 1 (11.1.0.7) or 10g Release 2 (10.2.0.5) from Microsoft Windows 32-bit to Microsoft Windows 64-bit. This appendix is based on the following 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 (21) of Section J.2, follow the steps mentioned in Note [ID 386990.1].
This appendix contains the following sections:
To recover the backed up Oracle Database 11g Release 1 (11.1.0.7) or 10g Release 2 (10.2.0.5) from Microsoft Windows 32-bit to Microsoft Windows 64-bit, follow these steps on source host:
Access the database home <DB_HOME>\bin\sqlplus /NOLOG
Run the following command:
SQL> CONNECT / AS SYSDBA :
Note:
If you are using Oracle Database 10g Release 2 (10.2.0.5):Access <DB HOME>/admin/<SID>/udump, and make a backup of this in your local system.
Run the following command:
<OMS_HOME>\bin>
emctl config emkey -copy_to_repos -sysman_pwd <sysman_password>
For example:
C:\Oracle\Middleware\oms\BIN>emctl config emkey -copy_to_repos -sysman_pwd Welcome1
Run the following command:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Note:
If you are using Oracle Database 10g Release 2 (10.2.0.5), 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 that will be performed later on the destination host on Microsoft Windows 64-bit.
If you are using Oracle Database 11g Release 1 (11.1.0.7) 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).
Run the following commands:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
To recover Oracle Database 11g Release 1 (11.1.0.7) or 10g Release 2 (10.2.0.5) from Microsoft Windows 32-bit to Microsoft Windows 64-bit, follow these steps on the destination host:
Perform a software-only installation of the Oracle Database software on the computer.
Note:
Ensure that the database being installed has the same path as the database on the source host that is on Microsoft Windows 32-bit. This ensures speedy recovery.Copy the entire <DB_Base>\oradata directory from Microsoft Windows 32-bit source host to the <DB_Base>\ directory of Microsoft Windows 64-bit destination host.
Run the following command:
<DB_HOME>\bin\oradim -new -sid <SID> -maxusers <no. of users> -startmode auto -pfile <DB_HOME>\database\init<SID>.ora
For example: C:\DB\db\BIN>oradim.exe -new -sid orcl -maxusers 3 -startmode auto -pfile C:\DB\db\database\initorcl.ora
Note:
MAXUSERS may not work with Oracle Database 10g Release 2 (10.2.0.5).
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 Microsoft Windows 32-bit source host to the <DB_Base>\ directory of Microsoft Windows 64-bit destination host.
Copy the entire <DB_Base>\fast_recovery_area directory from Microsoft Windows 32-bit source host to the <DB_Base>\ directory of Microsoft Windows 64-bit destination host.
Copy <DBHOME>\database\*.* files from Microsoft Windows 32-bit source host to the <DBHOME>\database\ directory of Microsoft Windows 64-bit destination host.
Note:
If you are using Oracle Database 10g Release 2 (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
Set the following environment variables:
set ORACLE_HOME=<DB_home>
set ORACLE_SID=<SID>
Access the DB_HOME\rdbms\admin directory.
Note:
If you cannot find sqlplus inside this, then go to<DB_HOME>\binRun the following command:
sqlplus /NOLOG
Connect /as sysdba.
Run the following command:
SQL> create pfile from spfile;
Note:
If you are using Oracle Database 10g Release 5 (10.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';
If you are using Oracle Database 11g Release 1 (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 Step (3) of Section J.1. Perform Step (15), Step (16), and Step (17) 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_deepdive.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 Step (28). Otherwise, if you receive an error, do the following:
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.
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/olapidrp.plb
Add olap by running the following commands:
SQL> connect SYS as SYSDBA
SQL> spool olap.log
SQL> @?/olap/admin/olap.sql SYSAUX TEMP;
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
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;
To finalize the process, do the following:
On the Microsoft Windows 32-bit source host, do the following:
Access <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\oms\BIN>emctl config emkey -remove_from_repos
Access <DB_HOME>\bin.
sqlplus "/as sysdba"
shutdown immediate;
startup;
On Microsoft Windows 64-bit destination host, 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;
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 = host.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.example.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)