If you are using 32-bit oracle database on any 32-bit / 64-bit plateform then you can convert or migrate 32-bit database to 64-bit database. Now i am describing you in two senerio.
First Senerio: Suppose you are using 32-bit database (9iR2) on any plateform and you want to convert your 32-bit database (9iR2) to 64-bit database.
Second Senerio: Suppose you are using 32-bit database (9iR2) and want to upgrade your current 32-bit database (9iR2) to 64-bit higher version database like 10g, 11g.
In the second senerion, database will automatically be converted to 64-bit during the upgrade to oracle database higher version.
Here we are describing only first senerion in this section. Project
Convert / Migrate a 32-bit Oracle 8.0.x, Oracle 8i, Oracle 9i, Oracle 10g and oracle 11g Database to 64-bit.
Step 1
Startup SQL*PLUS, connect with 32-bit database instance AS SYSDBA and shutdown database by using SHUTDOWN IMMEDIATE command.
Step 2
Tack full cold backup of database. Step 3
Install 64-bit version of the same oracle software realease in Different ORACLE_HOME. Step 4
Copy initialization parameter file eg initSID.ora, spfileSID.ora from old ORACLE_HOME to new 64-bit ORACLE_HOME.
Step 5
Change your environment to point at the new 64Bit ORACLE_HOME. Step 6
Add following parameter in new 64-bit initialization parameter file. aq_tm_processes=0
job_queue_processes=0 _system_trig_enabled= false Step 7
startup SQL*PLUS, connect with 64-bit database instance AS SYSDBA. Step 8
If you are working with in Oracle 8.0.x, Oracle8i or Oracle9i 9.0.x database, run STARTUP RESTRICT:
SQL> STARTUP RESTRICT
If you are working with in Oracle9i 9.2.0.x database, run STARTUP MIGRATE: SQL> STARTUP MIGRATE
If you are working with in Oracle10g database, run STARTUP UPGRADE: SQL> STARTUP UPGRADE
If you are working with in Oracle11g database, run STARTUP UPGRADE: SQL> STARTUP UPGRADE
Step 9
Set the system to spool results to a log file for later verification of success: SQL> SPOOL catoutw.log
Step 10
Run utlirp.sql:
SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql
This script recompiles existing PL/SQL modules in the format required by the new database. This script first alters certain dictionary tables. Then, it reloads package STANDARD and DBMS_STANDARD, which are necessary for using PL/SQL.
Optional Steps:
If the patchset level is not being changed (for example, you are migrating a 9.2.0.8 32-bit database to 9.2.0.8 64-bit) then there is no need of optional steps.
If the patchset level is change to run then need to optional steps.
If you are working with in Oracle 8.0, Oracle8i or Oracle 9i 9.0.x database, run the following script:
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
If you are working with in Oracle9i 9.2.0.x database, run the following script:
SQL> @$ORACLE_HOME/rdbms/admin/catpatch.sql
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql Step 11
Run utlrp.sql:
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql This script recompiles all invalid objects
TIPS:
If you are using same machine for converting 32-bit to 64 bit. Only you will create new ORACLE_HOME for 64-bit oracle software and you will use same phiysical database structure. If you are using different machine for convertion 32-bit to 64-bit . You will install Oracle 64 bit oracle software on differnet machine and you will clone your 32 bit database on new machine. If you are using UNIX based OS and want to use different machine for converting 32 bit to 64 bit better you create same database file structure and restore from old box to new box.
Moving From the Standard Edition to the Enterprise Edition and via-verse
If you are using a Standard Edition database (Release prior to 11gR1), then you can change it to an Enterprise Edition database.
Step 1
Standard Edition database software should be same as the Enterprise Edition database software. Step 2
Shutdown the database Step 3
Shut down your all oracle services including oracle database Step 4
De-install the Standard Edition oracle software Step 5
Install the Enterprise Edition server software using the Oracle Universal Installer. Step 6
Select the same Oracle home that was used for the de-installed Standard Edition. During the installation, be sure to select the Enterprise Edition. When prompted, choose Software only from the Database Configuration screen.
Step 7
Start up your database.
Tips:
1. You can only convert Standard Edition Database to the Enterprise Edition Database by using above method.
2. If you want to convert from an Enterprise Edition database to a Standard Edition database, you must use Export/Import operation. Without Export/Import you can not convert.
Inside Story:
1. The Enterprise Edition contains data dictionary objects which are not available in the Standard Edition. If you just install the Standard Edition software, then you will end up with data dictionary objects which are useless. Some of them might be invalid and possibly create problems when maintaining the database.
2. The Export/Import operation does not introduce data dictionary objects specific to the Enterprise Edition, because the SYS schema objects are not exported. Oracle recommends using the Standard Edition EXP utility to export the data.
3. After the Import in the Standard Edition database, you are only required to drop all user schemas related to Enterprise Edition features, such as the MDSYS account used with Oracle Spatial.
Upgrade Project
Project A Upgrade Oracle Database from Version 8.1.x(x>7) to 8.1.7
Here I am describing Manual methods to upgrade our oracle database. Step 1 (Prepare the Existing Database to be Upgrade)
1.1. Make sure the DB_DOMAIN initialization parameter in your initialization parameter file is set to one of the following:
.WORLD
A valid domain setting for your environment
1.2. Make sure the _SYSTEM_TRIG_ENABLED initialization parameter is set to FALSE in the initialization parameter file. If this initialization parameter is not currently set, then explicitly set it to FALSE:
_SYSTEM_TRIG_ENABLED = FALSE
1.3. Check free Space in SYSTEM and Rollback Segment Tablepace
Upgrading to a new release requires more space in your SYSTEM tablespace where you store rollback segments. In general, you need at least 20 MB of free space in your SYSTEM tablespace to upgrade.
Query for checking the free space. clear buffer
clear columns clear breaks set linesize 500 set pagesize 5000
column a1 heading 'Tablespace' format a15 column a2 heading 'Data File' format a45
column a3 heading 'TotalSpace [MB]' format 99999.99 column a4 heading 'FreeSpace [MB]' format 99999.99 column a5 heading 'Free%' format 9999.99
break on a1 on report compute sum of a3 on a1 compute sum of a4 on a1 compute sum of a3 on report compute sum of a4 on report
SELECT a.tablespace_name a1,a.file_name a2,a.avail a3,NVL(b.free,0) a4,NVL(ROUND(((free/avail)*100),2),0) a5
FROM
(SELECT tablespace_name,SUBSTR(file_name,1,45)
FROM sys.dba_data_files GROUP BY tablespace_name, SUBSTR(file_name,1,45),file_id) a,(SELECT tablespace_name, file_id, ROUND(SUM(bytes/(1024*1024)),3) free
FROM sys.dba_free_space GROUP BY tablespace_name, file_id) bWHERE a.file_id = b.file_id (+)ORDER BY 1, 2
If you need to add more space in your system tablespace, execute following steps: How to add more space to the SYSTEM tablespace?
ALTER TABLESPACE system ADD DATAFILE ''
SIZE 16M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
1.4. Run SHUTDOWN IMMEDIATE on the database and backup your database. SQL> SHUTDOWN IMMEDIATE
Step 2 (If you are using Windows platform, stop and delete the services)
2.1. Stop the OracleServiceSID Oracle service of the oracle 8.1.6 database if you are using Windows
C :\> NET STOP OracleService
2.2. Selete the OracleServiceSID at command line of 8.1.6 Home, if you are using Windows C:\>ORADIM –DELETE –SID