Pre-Requisite:
You should have enough MEMORY, server space and SWAP SPACE on Oracle database 10g, which you want to migrate.
Also here we are upgrading 10g to Oracle Database 11g – Beta 6 (11.1.0.6)
Also here we are upgrading 10g to Oracle Database 11g – Beta 6 (11.1.0.6)
Step 1) Installing Oracle 11g Home
We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a separate ORACLE_HOME in parallel to 10g Oracle Home.
Step 2) Pre-Upgrade Utility
In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location.
$ cp utlu111i.sql /tmp
For more about utlu111i.sql :
Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home
WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.
Finding the Version of existing timezone files:
SQL> select version from v$timezone_file;
version
------------
2
here oracle 10.2.0.1 has timezone file version of 2
Before you upgrade your timezone file version, you must run utltzuv2.sql to see if there is data that will be affected by the timezone file version upgrade. The script utltzuv2.sql creates a table named sys.sys_tzuv2_temptab, and fills it with data that may get affected by the timezone file version.
SQL > select * from sys.sys_tzuv2_temptab ;
no rows selected
here we see no rows but if we see any rows then we need to take that data backup and restore after timezone file version upgrade
Step 4) Run Pre-Upgrade Utility again
Run utlrp.sql to validate the objects if there is any invalidate objects.
SQL> @?/rdbms/admin/utlrp.sql
Check for any INVALID Objects in the database before upgrade starts:
SQL> select count(*),object_type,owner from dba_objects where status = ‘INVALID’ group by object_type,owner;
SQL> purge DBA_RECYCLEBIN
Gather dictionary stats to run UPGRADE process fast,
SQL> exec dbms_stats.gather_dictionary_stats;
Create pfile from spfile
Shutdown à shut down the database in oracle 10g.
Copy the pfile of database from ORACLE_HOME/dbs(10g) to ORACLE_HOME/dbs(11g) using cp command.
In that make changes shown below,
1. Remove BDUMP, CDUMP, UDUMP and their locations
2. Add Diagnostic_dest=/’$ORACLE_BASE/diag’-àlocation for diagnostic dest
3. Change audit_file_dest location
4. Change compatibility to 11g and save it.
After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don’t get any critical warnings. If everything looks fine, shut down the database from 10g Oracle Home.
Step 5) Starting Upgrade
Source the following variables for 11g Oracle Home
[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export ORACLE_HOME=/opt/oracle/11.1.0/db_1[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export PATH=$ORACLE_HOME/bin:$PATH[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export ORACLE_SID=orcl[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
connected to the database sys as sysdba
sqlplus “/ as sysdba” –> will be connected to idle instance
SQL> startup upgrade
SQL> SPOOL upgrade.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
Once the upgrade finishes, It will shut down the database automatically.
Now export New oracle HOME and startup the database
Now export New oracle HOME and startup the database
[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export ORACLE_HOME=/opt/oracle/11.1.0/db_1
[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export ORACLE_SID=orcl
[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
Login again as sysdba and startup in normal mode.
Check the dba_registry for the components and its status
SQL> startup
SQL> select substr(comp_id,1,10) comp_id, substr(comp_name,1,25) comp_name, substr(version,1,10) version, status from dba_registry order by modified;
All the components should be VALID state
Step 6) Post-Upgrade Steps
Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.
SQL> STARTUP
Run utlu111s.sql to display/verify the results of the upgrade:
SQL> @?/rdbms/admin/utlu111s.sql
Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode:
SQL> @?/rdbms/admin/catuppst.sql
Run utlrp.sql to recompile invalid objects after upgrade
SQL> @?/rdbms/admin/utlrp.sql
SQL> select count(*),object_type,owner from dba_objects where status = ‘INVALID’ group by object_type,owner;
Run utluiobj.sql after the upgrade to identify any new invalid objects
Edit /etc/oratab file with new 11g Oracle home and comment out 10g oracle home
No comments:
Post a Comment