Upgrade from 9.2.0.8 to 11.1.0.6 using 'DBUA'
1 - Install the new oracle 11g home. 2- Run the Pre-Upgrade Information Tool. Using utlu111i.sql that is located in 11g home ORACLE_HOME/RDBMS/admin directory And connect to 9i sqlplus and run this command SQL> spool C:\utl.txt SQL> @ORACLE_HOME\rdbms\admin\utlu111i.sql
SQL> spool off And then check the output of this command and configure the warning in this output review upgrade guide for more information
3 - Prepare the Database to Be Upgraded •
Deprecated CONNECT Role
After upgrading to Oracle Database 11g Release 1 (11.1) from Oracle9i Release 2 (9.2) Or Oracle Database 10g Release 1 (10.1), the CONNECT role has only the CREATE SESSION privilege; the other privileges number granted to the CONNECT role in earlier releases are revoked during the upgrade. To identify which users and roles in your database are granted the CONNECT role, use the following query SELECT grantee FROM dba_role_privs WHERE granted_role = 'CONNECT' and grantee NOT IN ( 'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP', 'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY', 'WK_TEST', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS', 'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA', 'SI_INFORMTN_SCHEMA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');
Note. If users or roles require privileges other than CREATE SESSION, then grant the specific required privileges prior to upgrading.
TIMESTAMP WITH TIMEZONE Data Type.
The time zone files that are supplied with Oracle Database 11g Release 1 (11.1) have Been updated from version v1 to version v4 to reflect changes in transition rules for some Time zone regions. then you must update your time zone from v1 to v4 before the upgrade process. If you upgrade without do that you will see error during upgrade process 'invalid number'. •
How you can upgrade to v4 timezone
1- Apply patch number 5726045 for configure the utlzuv2.sql utility. 2- Apply the patch number 5845928 from the metal ink and review the read me attachment. If you don’t able to apply this patch because your pat chest utility you can make it manually by read Note: 396387.1
Optimizer Statistics (optional step)
When upgrading to Oracle Database 11g Release 1 (11.1), optimizer statistics are Collected for dictionary tables that lack statistics. This statistics collection can be time Consuming for databases with a large number of dictionary tables
Collecting Statistics for System Component Schemas Spool gdict Grant analyze any to sys; exec dbms_stats.gather_schema_stats('WMSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); exec dbms_stats.gather_schema_stats('MDSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); exec dbms_stats.gather_schema_stats('CTXSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); exec dbms_stats.gather_schema_stats('XDB',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); exec dbms_stats.gather_schema_stats('WKSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); exec dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); spool off
Take note of all INVALID objects:
SQL> SELECT UNIQUE object_name, object_type, owner FROM dba_objects WHERE status='INVALID'; Recompile invalid objects in SYS and SYSTEM with ultrp.sql
Run the Oracle Net Configuration Assistant
Remove listener from 9i home and create it in 11g home.
You must remove the old listener before creating a new one. If you attempt to Create a new listener from the new Oracle home first, and use the same name and Port as the old listener, then Oracle Net Configuration Assistant returns an error.
4 - Upgrade a Database Using Database Upgrade Assistant Run DBUA from 11g home you can the steps in oracle 11g upgrade guide.
Note. If you face problem with configuration enterprise manager you can skip this step and open cmd and write this command.
emca -config dbcontrol db -repos recreate
or you can create the enterprise manage using DBCA .
feedback your opinion is important for me
[email protected]
