Upgrading 10g to 11g
Pre-work for upgrade:
- Install 11g Home
- Run pre-upgrade script in the target database
- Review and complete recommendations made by pre-upgrade script
- Create a text copy of spfile and make necessary changes as recommended
- Please do not change COMPATIBLE parameter value to 11.2.0 if you have a plan to downgrade back to 10.2.0
After completing all the necessary recommendations to the existing database follow the steps:
Step 1. Compile all invalid objects in 11g
SQL>@?/rdbms/admin/utlrp.sql
keep a list of INVALID objects to compare after upgrade INVALID objects.
Step 2. Stop dbconsole
SQL>emctl stop dbconsole
Setp 3. Shutdown all the servies (Instance, listener, dbconsole) of the target database.
[oracle@prod db_1]$ sqlplus / as sysdba
SQL> Shutdown immediate
[oracle@prod db_1]$lsnrctl stop
[oracle@prod db_1]$emctl stop dbconsole
Step 4. On Unix/Linux environment, ensure that sid in Oratab is pointing to the 11g Oracle Home. Also, comment out the previous oratab entry for this database, it might be required in case of downgrade.
#prod:/u01/app/oracle/product/10.2.0/db_1:Y
prod:/u01/app/oracle/product/11.2.0/db_1:Y
Source in the database again and change directory to verify the current
Oracle Home:
[oracle@prod db_1]$ . oraenv
ORACLE_SID = [prod] ?
[oracle@prod db_1]$ cd $ORACLE_HOME
[oracle@prod db_1]cd rdbms\admin
In Windows Environment
C:\> NET STOP OracleServicePROD
C:\> ORADIM -DELETE -SID PROD
C:\> SET ORACLE_HOME=E:\oracle\product\10.2.0\db_1
C:\> ORADIM -DELETE -SID PROD
C:\> ORADIM -NEW -SID PROD -INTPWD XXXXXXX -MAXUSERS USERS -
STARTMODE AUTO -PFILE %ORACLE_HOME%\DATABASE\INITPROD.ORA
Setp 4.
SQL> startup upgrade
SQL> spool upgrade.txt
SQL> @catupgrd
SQL> SPOOL OFF
SQL> shutdown immediate
Step 5. Spool off and check upgrade.txt file for possible errors during upgrade.
Step 6. Bounce the database after successful upgrade.
SQL>startup
Step 7. compile the invalid objects by executing utlrp.sql
SQL>@utlrp.sql
Step 9.Shut down and restart the instance
SQL> shutdown immediate
SQL> startup
Step 10. Verify the component name, version and status of each component, it should show like below:
SQL> select comp_name,version, status from dba_registry;
COMP_NAME
VERSION STATUS
----------------------- --------------
-----------
OLAP Catalog 11.2.0.3.0
VALID
Spatial
11.2.0.3.0 VALID
Oracle interMedia
11.2.0.3.0 VALID
Oracle XML Database
11.2.0.3.0 VALID
Oracle Text 11.2.0.3.0
VALID
Oracle Data Mining
11.2.0.3.0 VALID
Oracle Expression Filter
11.2.0.3.0 VALID
Oracle Rule Manager
11.2.0.3.0 VALID
Oracle Workspace Manager 11.2.0.3.0 VALID
Oracle Database Catalog Views 11.2.0.3.0 VALID
Oracle Database Packages and Types 11.2.0.3.0 VALID
JServer JAVA Virtual Machine 11.2.0.3.0 VALID
Oracle XDK
11.2.0.3.0
VALID
Oracle Database Java Packages 11.2.0.3.0 VALID
OLAP Analytic Workspace 11.2.0.3.0 VALID
Oracle OLAP API
11.2.0.3.0
VALID
16 rows selected.
Issues faced during 10.2.0.4 to 11.2.0.3 upgrade
Issue No 1.
The upgrade shows error altering DBMS_SQLTUNE_INTERNAL and/or DBMS_SQLTUNE package :
LINE/COL ERROR
------- ----------------------------------------------------------
6370/5 PL/SQL: SQL Statement ignored
6376/18 PL/SQL: ORA-00980: synonym translation is no longer valid
Reason:Public synonym of XMLCONCAT exists. The package depends on this invalid synonym.
Workaround :
Step 1. Drop the synonym:
SQL> drop public synonym XMLCONCAT;
Step 2. Recompile the package
SQL> alter package DBMS_SQLTUNE_INTERNAL compile body;
or
SQL> alter package DBMS_SQLTUNE compile body;
Step 3. Run utlrp.sql to recompile the other INVALID objects
Issue No 2.
ORA-39714: upgrade script utlmmig.sql failed
Reference: Metalink Doc Id 761961.1
Reason : A normal database open was attempted, but the upgrade script utlmmig.sql failed to complete.
Workaround:
Step 1. SQL>STARTUP UPGRADE
Step 2. SQL>@utlmmig.sql
The script will execute and will shutdown the database.
Step 3. Start the database
SQL>STARTUP
The database will start without any issues.
Downgrading database to 10g from 11g
Step 1. Compile all invalid objects in 11g
SQL>@?/rdbms/admin/utlrp.sql
Step 2. stop dbconsole
[oracle@prod db_1]$emctl stop dbconsole
Setp 3. SQL> shutdown immediate
Step 4. Startup database in upgrade mode and drop SYSMAN user and run the downgrade script
SQL> startup downgrade
SQL> drop user sysman cascade;
SQL> spool downgrade.txt
SQL> @catdwgrd
SQL> spool off
SQL> shutdown immediate
Step 5. On Unix/Linux, ensure that sid in Oratab is pointing to the 10g Oracle Home.
prod:/u01/app/oracle/product/10.2.0/db_1:Y
#prod:/u01/app/oracle/product/11.2.0/db_1:N
C:\> NET STOP OracleServicePROD
Change directory to Oracle 10g Home
C:\> ORADIM -DELETE -SID PROD
C:\> ORADIM -NEW -SID PROD -INTPWD XXXXXXX -MAXUSERS USERS -
STARTMODE AUTO -PFILE %ORACLE_HOME%\DATABASE\INITPROD.ORA
Step 6. Change directory to 10g Oracle Home/rdbms/admin and start database in upgrade mode
SQL> startup upgrade
SQL> spool reload.log
SQL> @catrelod.sql
Step 7. Turn off the spooling
SQL> spool off
Step 8. Check the spool file to verify that the packages and procedures compiled successfully.
Step 9.Shut down and restart the instance
SQL> shutdown immediate
SQL> startup
Step 10. Verify the component name, version and status of each component, it should show like below:
SQL> select comp_name,version,status from dba_registry;
COMP_NAME VERSION STATUS
--------------------------------- -------------- -----------
OLAP Catalog 10.2.0.4.0 VALID
Spatial 10.2.0.4.0 VALID
Oracle interMedia 10.2.0.4.0 VALID
Oracle XML Database 10.2.0.4.0 VALID
Oracle Text 10.2.0.4.0 VALID
Oracle Data Mining 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Rule Manager 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
OLAP Analytic Workspace 10.2.0.4.0 VALID
Oracle OLAP API 10.2.0.4.0 VALID
16 rows selected.
Step 11. Start the listener
[oracle@prod db_1]$lsnrctl start
Step 12. Recreate the dbconsole
[oracle@prod db_1]$ emca -config dbcontrol db -repose create
No comments:
Post a Comment