Oracle 10g 10.2.0.1 32-bit migration upgrade to 10g 10.2.0.5 64-bit

Note:
It is officially recommended to migrate to the same version, for example, 10.2.0.1 (32) to 10.2.0.1 (64 ).
Upgrade to 10.2.0.5 (64 ).

I. Environment Introduction
Source database
Operating system version: OEL5.8 32bit
Database Version: 10.2.0.1 32bit
Database sid Name: orcl
Test Library
Operating system version: OEL5.8 x64
Database Version: 10.2.0.5 x64
Database sid Name: orcl

Ii. source database
1. Close the source database
# Su-Oracle
$ Sqlplus/as sysdba;
SQL> shutdown immediate;
2. Package backup/u01/app/oracle/oradatadirectory as oradata.tar.gz
3. Package backup/u01/app/oracle/product/10.2.0/db_1/dbsdirectory as dbs.tar.gz
4. Copy oradata.tar.gz dbs.tar.gz to the test database.

Iii. Test Database
1. Close the test database,
2. Restore oradata.tar.gz to the/u01/app/oracle/oradata directory.
3. Restore dbs.tar.gz to the/u01/app/oracle/product/10.2.0/db_1/dbs directory.
4. Create a pfile file for the standby Database
Note:
Because the directory location of the source database and the migration database are the same as the Instance name, the contents of the control file and parameter file do not need to be rebuilt or modified.
# Su-oracle
[Oracle @ rman ~] $ Sqlplus/as sysdba;
SQL * Plus: Release 10.2.0.5.0-Production on D25 21:05:39 2013
Copyright (c) 1982,201 0, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> create pfile from spfile;
File created.
SQL> quit
Disconnected
 
5. Modify the pfile.
# Add the following parameter files
_ SYSTEM_TRIG_ENABLED = false
 
$ Vi/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl. ora
Orcl. _ db_cache_size = 448790528
Orcl. _ Java _pool_size = 4194304
Orcl. _ large_pool_size = 4194304
Orcl. _ shared_pool_size = 146800640
Orcl. _ streams_pool_size = 0
*. Audit_file_dest = '/u01/app/oracle/admin/orcl/adump'
*. Background_dump_dest = '/u01/app/oracle/admin/orcl/bdump'
*. Compatible = '10. 2.0.1.0'
*. Control_files = '/u01/app/oracle/oradata/orcl/control01.ctl', '/u01/app/oracle/oradata/orcl/control02.ctl ', '/u01/app/oracle/oradata/orc
L/control03.ctl'
*. Core_dump_dest = '/u01/app/oracle/admin/orcl/cdump'
*. Db_block_size = 8192
*. Db_domain =''
*. Db_file_multiblock_read_count = 16
*. Db_name = 'orcl'
*. Db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area'
*. Db_recovery_file_dest_size = 2147483648
*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = orclXDB )'
*. Job_queue_processes = 10
*. Log_archive_format = '% t _ % s _ % r. dbf'
*. Open_cursors = 300
*. Pga_aggregate_target = 201326592
*. Processses = 150
*. Remote_login_passwordfile = 'clusive'
*. Sga_target = 605028352
*. Undo_management = 'auto'
*. Undo_tablespace = 'undotbs1'
*. User_dump_dest = '/u01/app/oracle/admin/orcl/udump'
_ SYSTEM_TRIG_ENABLED = false

6. Create a spfile file through pfile and start it in Upgrade mode.
[Oracle @ rman ~] $ Sqlplus/as sysdba;
SQL * Plus: Release 10.2.0.5.0-Production on 25 21:18:41 2013
Copyright (c) 1982,201 0, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL>
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 608174080 bytes
Fixed Size 2022696 bytes
Variable Size 155189976 bytes
Database Buffers 448790528 bytes
Redo Buffers 2170880 bytes
Database mounted.
Database opened.
SQL>

7. compile all objects on the 64-bit Platform
SQL> @? /Rdbms/admin/utlirp. SQL

8. Rebuilding the data dictionary (10.2.0.5)
SQL> @ $ ORACLE_HOME/rdbms/admin/catupgrd. SQL
For more than 40 minutes, the system prompts that some invalid objects need to be edited.

9. Execute the following process to compile invalid objects
SQL> conn/as sysdba;
SQL> @? /Rdbms/admin/utlrp. SQL

Open another window to view the execution process. Only 0 can be completed.
SQL> SELECT COUNT (*) FROM obj $ WHERE status IN (4, 5, 6 );
COUNT (*)
----------
0
SQL> select object_name, object_type, owner from dba_objects where status <> 'valid ';
0

10. Close the database, modify the parameter file, and remove the added parameters.
(1) modify the pfile parameter file
$ Vi/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl. ora
# Remove the following parameters
_ SYSTEM_TRIG_ENABLED = false
(2) Restart through spfile
SQL> shutdown immediate;
SQL> create spfile from pfile;
SQL> startup;

11. Test the database
Archive Mode
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15
Original account and Data
SQL> conn abc/abc;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
-----------------------------------------------
ABC TABLE
View database version information

SQL> select * from v $ version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bi
PL/SQL Release 10.2.0.5.0-Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0-Production
NLSRTL Version 10.2.0.5.0-Production

Recommended reading:

Expdp/impdp for Oracle 10g to 11g data migration

Oracle 10g stored outline migration to 11g

Oracle migration causes sqlplus to report ora-15180 error messages