How do Oracle databases migrate from 32-bit platforms to 64-bit?

1: Backup Oracle Database

2: Backup control files to trace (in case you need to create a new control file)

The code is as follows

Copy Code

sql> ALTER DATABASE backup Controlfile to trace; Database altered. Sql> Oradebug Setmypid Statement processed. Sql> Oradebug Tracefile_name /u01/app/admin/easy/udump/easy_ora_18830.trc Sql> Host CAT/U01/APP/ADMIN/EASY/UDUMP/EASY_ORA_18830.TRC ...... -- --Set #1. Noresetlogs case -- --The following commands would create a new control file and use it --To open the database. --Data used by Recovery Manager'll be lost. --Additional logs May is required for media recovery of offline --Use the ' only if ' versions ' all online logs are --Available. --After mounting the created Controlfile, the following SQL --statement would place the database in the appropriate --Protection mode: --ALTER database SET STANDBY database to maximize performance STARTUP Nomount CREATE controlfile Reuse DATABASE "Easy" noresetlogs Noarchivelog Maxlogfiles 16 Maxlogmembers 3 Maxdatafiles 100 Maxinstances 8 Maxloghistory 292 LOGFILE GROUP 1 '/oradata/easy/redo01.log ' SIZE 11200K, GROUP 2 '/oradata/easy/redo02.log ' SIZE 11200K --STANDBY LOGFILE DataFile '/oradata/easy/system01.dbf ', '/oradata/easy/undotbs01.dbf ', '/oradata/easy/sysaux01.dbf ', '/ORADATA/EASY/USERS01.DBF ' CHARACTER SET ZHS16GBK ; --Commands to re-create incarnation table --Below log names must is changed to existing filenames on --disk. Any one log file from each branch can is used to --Re-create incarnation records. --ALTER DATABASE REGISTER LOGFILE '/u01/app/flash_recovery_area/easy/archivelog/2014_11_05/o1_mf_1_1_%u_.arc '; --ALTER DATABASE REGISTER LOGFILE '/u01/app/flash_recovery_area/easy/archivelog/2014_11_05/o1_mf_1_1_%u_.arc '; --Recovery is required if any of the datafiles are restored backups, --or if the last shutdown is not normal or immediate. RECOVER DATABASE --Database can now is opened normally. ALTER DATABASE OPEN; --Commands to add tempfiles to temporary tablespaces. --Online tempfiles have complete space information. --tempfiles may require adjustment. ALTER tablespace TEMP ADD tempfile '/oradata/easy/temp01.dbf ' SIZE 20971520 Reuse autoextend on NEXT 655360 MAXSIZE 32767M; --End of tempfile additions. -- --Set #2. Resetlogs case -- --The following commands would create a new control file and use it --To open the database. --Data used by Recovery Manager'll be lost. --The contents of online logs'll is lost and all backups'll --be invalidated. Use this only if online logs are damaged. --After mounting the created Controlfile, the following SQL --statement would place the database in the appropriate --Protection mode: --ALTER database SET STANDBY database to maximize performance STARTUP Nomount CREATE controlfile Reuse DATABASE "Easy" resetlogs Noarchivelog Maxlogfiles 16 Maxlogmembers 3 Maxdatafiles 100 Maxinstances 8 Maxloghistory 292 LOGFILE GROUP 1 '/oradata/easy/redo01.log ' SIZE 11200K, GROUP 2 '/oradata/easy/redo02.log ' SIZE 11200K --STANDBY LOGFILE DataFile '/oradata/easy/system01.dbf ', '/oradata/easy/undotbs01.dbf ', '/oradata/easy/sysaux01.dbf ', '/ORADATA/EASY/USERS01.DBF ' CHARACTER SET ZHS16GBK ; --Commands to re-create incarnation table --Below log names must is changed to existing filenames on --disk. Any one log file from each branch can is used to --Re-create incarnation records. --ALTER DATABASE REGISTER LOGFILE '/u01/app/flash_recovery_area/easy/archivelog/2014_11_05/o1_mf_1_1_%u_.arc '; --ALTER DATABASE REGISTER LOGFILE '/u01/app/flash_recovery_area/easy/archivelog/2014_11_05/o1_mf_1_1_%u_.arc '; --Recovery is required if any of the datafiles are restored backups, --or if the last shutdown is not normal or immediate. RECOVER DATABASE USING BACKUP controlfile --Database can now be opened zeroing the online logs. ALTER DATABASE OPEN resetlogs; --Commands to add tempfiles to temporary tablespaces. --Online tempfiles have complete space information. --tempfiles may require adjustment. ALTER tablespace TEMP ADD tempfile '/oradata/easy/temp01.dbf ' SIZE 20971520 Reuse autoextend on NEXT 655360 MAXSIZE 32767M; --End of tempfile additions. --

3: View the current database status

The code is as follows

Copy Code

Sql> Select COUNT (*) from dba_objects where status = ' INVALID '; COUNT (*) ---------- 0 Sql> select Comp_name,status from Dba_registry; Comp_name STATUS ---------------------------------------- ---------------------- Oracle Database Catalog views VALID Oracle Database Packages and Types VALID Oracle Workspace Manager VALID Jserver JAVA Virtual Machine VALID Oracle XDK VALID Oracle Database Java Packages VALID Oracle Expression Filter VALID Oracle Data Mining VALID Oracle Text VALID Oracle XML Database VALID Oracle Rules Manager VALID Comp_name STATUS ---------------------------------------- ---------------------- Oracle intermedia VALID OLAP Analytic Workspace VALID Oracle OLAP API VALID OLAP Catalog VALID Spatial VALID Oracle Enterprise Manager VALID Rows selected.

4: Copy parameter files, control files, data files, log files to 64-bit database

5: Create the corresponding directory on the 64-bit system, such as Udmp adump, to be consistent with the 32-bit system

6: If the database's data file directory changes, you need to create a new control file, (preferably keep the source and target libraries consistent, reduce workload and error incidence)

7: Put the stored procedures in the database as invalid, the Utlirp script will save the stored procedure as invalid

The code is as follows	Copy Code
-If you are migrating a oracle9i database, execute the following command: Sql> STARTUP MIGRATE -If it is oracle10g or 11g, execute the following command: Sql> STARTUP UPGRADE Sql> SPOOL EASY.log; sql> @ $ORACLE _home/rdbms/admin/utlirp.sql Sql> SPOOL off;

8: Restart the database,

9: If the database has an OLAP component, delete OLAP, and the SYS user executes the following script

The code is as follows

Copy Code

Sql> Conn/as SYSDBA ----> Remove OLAP Catalog Sql> @?/olap/admin/catnoamd.sql ----> Remove OLAP API Sql> @?/olap/admin/olapidrp.plb Sql> @?/olap/admin/catnoxoq.sql ----> Deinstall aps-olap AW component Sql> @?/olap/admin/catnoaps.sql The sql> @?/olap/admin/cwm2drop.sql---only needs to be performed in 11g. In 10g, the script is already included in the Catnoamd.sql script, so you do not have to perform Could getsome invalid objects under SYS and public owner, and they are the old duplicate Olapsys objects copied under These schemas when Olap has been installedpreviously. Generally, Theolap objects are named with the context like%olap%,%awm%, or other OLAP word keys Intheir "object_name" field, However, if it needs a help to Recognizethem, then please contact Oracle Support and create a Service Request toget Assi Stance for this question. Note This catnoadm.sql could fail from 10.1.0.5 to 11.1.0.7release. Due to the "factthat it refers to three scripts which don t get shipped until 11.2 this scriptwill fail. Besides that Itwill error on the 7 non-existing synonyms to drop. Prior 11.2, execute these three dropsynonym statements: sql> drop public synonym Olapfactview; sql> drop public synonym Olapdimview; sql> drop public synonym dbms_odm;

10: Compile Invalid object

The code is as follows	Copy Code
Sql> @?/rdbms/admin/utlrp.sql

There will be an error, because OLAP is not installed, the following installation of OLAP, and then recompiling

11: Add OLAP

The code is as follows	Copy Code
sql> @?/olap/admin/olap.sql Sysaux TEMP; Sql> @?/rdbms/admin/utlrp.sql--This is going to be very fast.

12. Verifying invalid objects and component states

The code is as follows

Copy Code

Sql> L 1* Select COUNT (*) from dba_objects where status<> ' VALID ' Sql> Col Comp_name for A40 Sql> select Comp_name,status from Dba_registry; Comp_name STATUS ---------------------------------------- ---------------------- Oracle Database Catalog views VALID Oracle Database Packages and Types VALID Oracle Workspace Manager VALID Jserver JAVA Virtual Machine VALID Oracle XDK VALID Oracle Database Java Packages VALID Oracle Expression Filter VALID Oracle Data Mining VALID Oracle Text VALID Oracle XML Database VALID Oracle Rules Manager VALID Comp_name STATUS ---------------------------------------- ---------------------- Oracle intermedia VALID OLAP Analytic Workspace VALID Oracle OLAP API VALID OLAP Catalog VALID Spatial VALID Oracle Enterprise Manager VALID

11: Recompile Java objects

The code is as follows

Copy Code

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 Select COUNT (*) into the 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 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 /

The code is as follows	Copy Code
Create or replace Java system /

The code is as follows	Copy Code
It May is necessary to recompilethe the Java objects with ncomp:i.e. % Ncomp-user Scott/tiger Hello.class

Note: In the migration process, it is best not to have DDL operations, so as not to cause deadlock.

Through the above 11 steps, the database will be able to successfully migrate to the 64-bit machine, if there are students to follow some steps to operate, I wish migration success