Migrate the oracle database from the 32-bit platform to the 64-bit, oracle32-bit

Source: Internet
Author: User

Migrate the oracle database from the 32-bit platform to the 64-bit, oracle32-bit

The disk of the customer's 32-bit oracle database system is damaged. Fortunately, the oracle database is intact. The customer requested that the database be migrated to the newly purchased device. The memory of the new device is 64 GB, and the system REDHAT 6.2 64-bit. It is definitely not feasible to directly copy data files, because the wordsize of oracle stored procedures is different in 32-bit and 64-bit platforms, although the user's stored procedures can be re-compiled during use, however, oracle's system storage process requires manual conversion.

To sum up, the migration process is as follows:

1: Back up the oracle database

2: Back up the control file to trace (for new control files)

SQL> alter database backup controlfile to trace;Database altered.SQL> oradebug setmypidStatement processed.SQL> oradebug tracefile_name/u01/app/admin/easy/udump/easy_ora_18830.trcSQL> host cat /u01/app/admin/easy/udump/easy_ora_18830.trc......----     Set #1. NORESETLOGS case---- The following commands will create a new control file and use it-- to open the database.-- Data used by Recovery Manager will be lost.-- Additional logs may be required for media recovery of offline-- Use this only if the current versions of all online logs are-- available.-- After mounting the created controlfile, the following SQL-- statement will place the database in the appropriate-- protection mode:--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "EASY" NORESETLOGS  NOARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/oradata/easy/redo01.log'  SIZE 11200K,  GROUP 2 '/oradata/easy/redo02.log'  SIZE 11200K-- STANDBY LOGFILEDATAFILE  '/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 be changed to existing filenames on-- disk. Any one log file from each branch can be 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 was not normal or immediate.RECOVER DATABASE-- Database can now be opened normally.ALTER DATABASE OPEN;-- Commands to add tempfiles to temporary tablespaces.-- Online tempfiles have complete space information.-- Other 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 will create a new control file and use it-- to open the database.-- Data used by Recovery Manager will be lost.-- The contents of online logs will be lost and all backups will-- be invalidated. Use this only if online logs are damaged.-- After mounting the created controlfile, the following SQL-- statement will place the database in the appropriate-- protection mode:--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "EASY" RESETLOGS  NOARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/oradata/easy/redo01.log'  SIZE 11200K,  GROUP 2 '/oradata/easy/redo02.log'  SIZE 11200K-- STANDBY LOGFILEDATAFILE  '/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 be changed to existing filenames on-- disk. Any one log file from each branch can be 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 was 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.-- Other 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

SQL> select count(*) from dba_objects where status ='INVALID';  COUNT(*)---------- 0SQL> select comp_name,status from dba_registry;COMP_NAME STATUS---------------------------------------- ----------------------Oracle Database Catalog Views VALIDOracle Database Packages and Types VALIDOracle Workspace Manager VALIDJServer JAVA Virtual Machine VALIDOracle XDK VALIDOracle Database Java Packages VALIDOracle Expression Filter VALIDOracle Data Mining VALIDOracle Text VALIDOracle XML Database VALIDOracle Rules Manager VALIDCOMP_NAME STATUS---------------------------------------- ----------------------Oracle interMedia VALIDOLAP Analytic Workspace  VALIDOracle OLAP API  VALIDOLAP Catalog VALIDSpatial  VALIDOracle Enterprise Manager VALID17 rows selected.

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

5: create a directory on a 64-bit system, such as udmp adump, which must be consistent with the 32-bit system.

6: if the data file directory of the database changes, you need to create a new control file (it is best to keep the source database consistent with the target database to reduce the workload and error rate)

7: Set the stored procedures in the database to invalid, and the UTLIRP script will set the stored procedures to invalid

<Span style = "white-space: pre"> </span> -- if the oracle9i database is migrated, run the following command: SQL> startup migrate -- if it is Oracle10g or 11g, run 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 the OLAP component and run the following script as sys.

SQL> conn/as sysdba ----> Remove OLAP CatalogSQL> @? /Olap/admin/catnoamd. SQL ----> Remove OLAP APISQL> @? /Olap/admin/olapidrp. plbSQL> @? /Olap/admin/catnoxoq. SQL ----> Deinstall APS-OLAP AW componentSQL> @? /Olap/admin/catnoaps. sqlSQL> @? /Olap/admin/cwm2drop. SQL --- only need to be executed in 11 GB. In 10 Gb, catnoamd. this script is already included in the SQL script, so You do not need to execute You cocould getsome invalid objects under SYS and PUBLIC owner, and they are the old duplicate OLAPSYS objects copied under these schemas when Olap has been installedpreviusly. generally, theOlap objects are named with 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 maintenance ance for this question. note that catnoadm. SQL cocould 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 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: Compilation of invalid objects

SQL> @?/rdbms/admin/utlrp.sql

There will be an error because olap is not installed. Install olap below and re-compile

11. Add OLAP

SQL> @?/olap/admin/olap.sql SYSAUX TEMP;
<Pre name = "code" class = "SQL"> SQL> @? /Rdbms/admin/utlrp. SQL -- this time will be very fast

 

12. Verify the invalid object and component Status

SQL> l  1* select count(*) from dba_objects where status<>'VALID'SQL> col comp_name for a40SQL> select comp_name,status from dba_registry;COMP_NAME STATUS---------------------------------------- ----------------------Oracle Database Catalog Views VALIDOracle Database Packages and Types VALIDOracle Workspace Manager VALIDJServer JAVA Virtual Machine VALIDOracle XDK VALIDOracle Database Java Packages VALIDOracle Expression Filter VALIDOracle Data Mining VALIDOracle Text VALIDOracle XML Database VALIDOracle Rules Manager VALIDCOMP_NAME STATUS---------------------------------------- ----------------------Oracle interMedia VALIDOLAP Analytic Workspace  VALIDOracle OLAP API  VALIDOLAP Catalog VALIDSpatial  VALIDOracle Enterprise Manager VALID

11. recompile the java object

<pre name="code" class="sql">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 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;/

 
create or replace java system/

It may be necessary to recompilethe Java objects with ncomp: I. e. % ncomp-user scott/tiger Hello. class


Ddl operations are not recommended during migration to avoid deadlock.



How can I migrate a 64-bit oracle database to a 32-bit oracle database?

To migrate a 64-bit Oracle database to a 32-bit Oracle database, you must use EXP to export data on the server and then execute IMP import on the 32-bit system. Other methods cannot be implemented.
During the migration process, ensure that the versions are consistent, and pay attention to the storage and memory configurations of the new system to avoid exceptions during the big data import process. For details, refer to the relevant documentation for export and import.

The ORACLE system is 64-bit. How to export the database is 32-bit?

EXP/IMP can be used.

In export/import Untilty, BIT is not counted.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.