How to migrate Oracle11gR1 database from 32-bit linux to 64-bit linux?

How to migrate Oracle11gR1 database from 32-bit linux to 64-bit linux?

Many a times, we will get a requirement to migrate a database which is there in 32-bit linux to 64-bit. Till 10g, we used to use export/import for this. But Oracle 11g Release 1 provides you an easiest way. Here it is…………

1) First of all, please perform the steps described below:

1.1) Start SQL*Plus:

C:\> sqlplus /NOLOG

1.2) Connect to the database instance as SYSDBA:

SQL> CONNECT / AS SYSDBA;

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

1.4) Shut down the database:

SQL> SHUTDOWN IMMEDIATE;

1.5) Perform a full offline database backup.

1.6) Backup your 32-bit Oracle Home (this step is optional since you can restore it from a fresh installation).

2) Install Oracle Database 11g Release 1 (11.1) for 64-bit Linux.

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:

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

4.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_BASE/$ORACLE_HOME/database/pwdSID.ora., where SID is your Oracle instance ID.

5) “This step is required on Windows platforms only”. 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. It is recommended to set _SYSTEM_TRIG_ENABLED=FALSE in the following document though:

=)> Oracle® Database Platform Guide
11g Release 1 (11.1) for Microsoft Windows
Part Number B32010-02

==)> Migrating an Oracle Database 11g Release 1 (11.1) Database

Note: On Linux/Unix there is not need to add _SYSTEM_TRIG_ENABLED=FALSE to init.ora file, when the database is started in upgrade mode _SYSTEM_TRIG_ENABLED is automatically set to FALSE.

After starting 10.2.0.3 in upgrade mode with 11.1.0.6 software alert log file shows:
==============================================================
” ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Autotune of undo retention is turned off.
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY;
Resource Manager disabled during database migration: plan ” not set
ALTER SYSTEM SET resource_manager_plan=” SCOPE=MEMORY;
Resource Manager disabled during database migration
Starting background process FBDA “
==============================================================

6) Go to the 64-bit ORACLE_HOME/rdbms/admin directory from the command prompt.

7) Start SQL*Plus:

SQL> sqlplus /NOLOG

8) Connect to the database instance as SYSDBA:

SQL> CONNECT / AS SYSDBA;

9) Re-create the 64-bit control files using the CREATE CONTROLFILE command using the trace file created in the step “1.3)“ as follow:

9.1) Edit the as follow:

9.2) If it is required, please 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.

9.3) Here is an example of a database named “orcl32″ on a 32-bit computer moving 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 ‘/oracle/product/11.1.0/oradata/orcl64/REDO03.LOG’ SIZE 1M,
# was ‘/oracle/product/11.1.0/oradata/orcl32/…LOG’
# on the 32-bit computer
GROUP 2 ‘/oracle/product/11.1.0/oradata/orcl64/REDO02.LOG’ SIZE 1M,
GROUP 3 ‘/oracle/product/11.1.0/oradata/orcl64/REDO01.LOG’ SIZE 1M
DATAFILE
‘/oracle/product/11.1.0/oradata/orcl64/SYSTEM01.DBF’,
# was ‘/oracle/product/11.1.0/oradata/orcl32/…DBF’
# on the 32-bit computer
‘/oracle/product/11.1.0/oradata/orcl64/RBS01.DBF’,
‘/oracle/product/11.1.0/oradata/orcl64/USERS01.DBF’,
‘/oracle/product/11.1.0/oradata/orcl64/TEMP01.DBF’,
‘/oracle/product/11.1.0/oradata/orcl64/TOOLS01.DBF’,
‘/oracle/product/11.1.0/oradata/orcl64/INDX01.DBF’,
‘/oracle/product/11.1.0/oradata/orcl64/DR01.DBF’
CHARACTER SET WE8ISO8859P1;

9.4) For additional information please check the next note:

10) Having a copy of the initialization parameter file (from the 32-bit computer), please include the new control file generated in the preceding step.

11) Shut down the database:

SQL> SHUTDOWN IMMEDIATE;

12) Start the database in UPGRADE mode to run utlirp.sql:

SQL> STARTUP UPGRADE;

You might need to use the PFILE option to specify the location of your initialization parameter
file.

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

SQL> SPOOL mig32-64.log;

14) Enter the following command to view the output of the script on-screen:

SQL> SET ECHO ON; 

Handling for JVM during Migration
When migrating a database from 32 to 64bit (or vice versa) additional actions
are required for java.  In theory the format of java shared data objects (SRO)
is not compatible between 32 and 64 bit and so these objects need to be dropped
and regenerated.  In practice it may be the case prior to release 11 such
objects could interoperate but if so this would only be by chance and should
not be relied on.

The steps to do the regeneration are as follows.  These should be done
immediately before running utlirp.  They may take several minutes to complete.
They must be done connected 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('deletem 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
/

15) Recompile existing PL/SQL modules in the format required by the 64-bit Oracle Database (utlirp.sql script changes the words size):

SQL> @utlirp.sql;

16) Turn off the spooling of script results to the log file:

SQL> SPOOL OFF;

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

18) Shut down the database:

SQL> SHUTDOWN IMMEDIATE;

19) Start the database:

SQL> STARTUP;

20) Recompile existing PL/SQL modules in the format required by the 64-bit Oracle Database (utlrp.sql script recompile the invalid objects):

SQL> @utlrp.sql;

21) “On Windows platforms only, if you added the “_SYSTEM_TRIG_ENABLED = FALSE” parameter to your initialization parameter file in step “5)” above, remove the parameter from the initialization parameter file, and then shut down and restart the database.”

For more info, refer to MOS doc 548978.1