DB upgrade from 11.2.0.2 to 11.2.0.3
11.2.0.2 database name : ORCL (to be upgraded to 11.2.0.3)
1.Download the required patchset for 11.2.0.3
below are the 2 patchsets( patchset number :1040450)
p10404530_112030_LINUX_1of7.zip
p10404530_112030_LINUX_2of7.zip
2. I have the existing 11.2.0.2 home at /u01/app/oracle/product/11.2.0/dbhome_1 so i installed the new 11.2.0.3 home to '/extend/app/oracle/product/11.2.0.3/dbhome_2'
3. move the existing init.ora and password file from dbs folder(/u01/app/oracle/product/11.2.0/dbhome_1/dbs) to new dbs location (/extend/app/oracle/product/11.2.0.3/dbhome_2/dbs)
4. login to existing ORCL database and run the utlu112i.sql script from (/extend/app/oracle/product/11.2.0.3/dbhome_2/rdbms/admin) location
this would do the pre-upgrade checks
the above script would advise to run gather_dictionary_stats script
EXECUTE dbms_stats.gather_dictionary_stats;
5. set the new HOME,PATH variables to new 11.2.0.3 values
[oracle@goldengate admin]$ echo $ORACLE_HOME
/extend/app/oracle/product/11.2.0.3/dbhome_2
[oracle@goldengate admin]$ echo $PATH
/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/extend/app/oracle/product/11.2.0.3/dbhome_2/bin
[oracle@goldengate admin]$ echo $LD_LIBRARY_PATH
/extend/app/oracle/product/11.2.0.3/dbhome_2/lib:/etc/alternatives/jdk1.6.0_27/jre/lib/i386/server
6. export ORACLE_SID=ORCL and start in upgrade mode
oracle@goldengate:/extend/app/oracle/product/11.2.0.3/dbhome_2/rdbms/admin[oracle@goldengate admin]$ echo $ORACLE_SID
orcl
oracle@goldengate:/extend/app/oracle/product/11.2.0.3/dbhome_2/rdbms/admin[oracle@goldengate admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 18 08:41:26 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 369100956 bytes
Database Buffers 46137344 bytes
Redo Buffers 6086656 bytes
Database mounted.
Database opened.
7. run the catupgrade script
SQL> spool /tmp/upgrade.log
SQL> @/extend/app/oracle/product/11.2.0.3/dbhome_2/rdbms/admin/catupgrd.sql
would take around 20-30 mins
8. After upgrade complete start the database in normal mode and run the utlu112s.sql (/extend/app/oracle/product/11.2.0.3/dbhome_2/utlu112s.sql) script.
9.Run utlrp.sql to recompile the invalid objects
10. Done... database has been upgraded to 11.2.0.3
[oracle@goldengate admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 18 11:55:39 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL READ WRITE PRIMARY
SQL> select * from v$version
2 ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
=========================
Issues faced --after upgrading to 11.2.0.3 my alert log was showing the below error messages
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_76957/orcl_ora_13359_i76957.trc:
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2014_04_17/o1_mf_1_186_9nzglgqk_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-07445: exception encountered: core dump [pevm_icd_call_common()+638] [SIGSEGV] [ADDR:0x74] [PC:0x100632AA] [Address not mapped to object] []
ORA-04067: not executed, package body "GGUSER.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "GGUSER.DDLREPLICATION"
ORA-04067: not executed, package body "GGUSER.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "GGUSER.DDLREPLICATION"
Solution : disable the trigger 'alter trigger sys.GGS_DDL_TRIGGER_BEFORE disable'
11.2.0.2 database name : ORCL (to be upgraded to 11.2.0.3)
1.Download the required patchset for 11.2.0.3
below are the 2 patchsets( patchset number :1040450)
p10404530_112030_LINUX_1of7.zip
p10404530_112030_LINUX_2of7.zip
2. I have the existing 11.2.0.2 home at /u01/app/oracle/product/11.2.0/dbhome_1 so i installed the new 11.2.0.3 home to '/extend/app/oracle/product/11.2.0.3/dbhome_2'
3. move the existing init.ora and password file from dbs folder(/u01/app/oracle/product/11.2.0/dbhome_1/dbs) to new dbs location (/extend/app/oracle/product/11.2.0.3/dbhome_2/dbs)
4. login to existing ORCL database and run the utlu112i.sql script from (/extend/app/oracle/product/11.2.0.3/dbhome_2/rdbms/admin) location
this would do the pre-upgrade checks
the above script would advise to run gather_dictionary_stats script
EXECUTE dbms_stats.gather_dictionary_stats;
5. set the new HOME,PATH variables to new 11.2.0.3 values
[oracle@goldengate admin]$ echo $ORACLE_HOME
/extend/app/oracle/product/11.2.0.3/dbhome_2
[oracle@goldengate admin]$ echo $PATH
/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/extend/app/oracle/product/11.2.0.3/dbhome_2/bin
[oracle@goldengate admin]$ echo $LD_LIBRARY_PATH
/extend/app/oracle/product/11.2.0.3/dbhome_2/lib:/etc/alternatives/jdk1.6.0_27/jre/lib/i386/server
6. export ORACLE_SID=ORCL and start in upgrade mode
oracle@goldengate:/extend/app/oracle/product/11.2.0.3/dbhome_2/rdbms/admin[oracle@goldengate admin]$ echo $ORACLE_SID
orcl
oracle@goldengate:/extend/app/oracle/product/11.2.0.3/dbhome_2/rdbms/admin[oracle@goldengate admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 18 08:41:26 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 369100956 bytes
Database Buffers 46137344 bytes
Redo Buffers 6086656 bytes
Database mounted.
Database opened.
7. run the catupgrade script
SQL> spool /tmp/upgrade.log
SQL> @/extend/app/oracle/product/11.2.0.3/dbhome_2/rdbms/admin/catupgrd.sql
would take around 20-30 mins
8. After upgrade complete start the database in normal mode and run the utlu112s.sql (/extend/app/oracle/product/11.2.0.3/dbhome_2/utlu112s.sql) script.
9.Run utlrp.sql to recompile the invalid objects
10. Done... database has been upgraded to 11.2.0.3
[oracle@goldengate admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 18 11:55:39 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL READ WRITE PRIMARY
SQL> select * from v$version
2 ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
=========================
Issues faced --after upgrading to 11.2.0.3 my alert log was showing the below error messages
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_76957/orcl_ora_13359_i76957.trc:
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2014_04_17/o1_mf_1_186_9nzglgqk_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-07445: exception encountered: core dump [pevm_icd_call_common()+638] [SIGSEGV] [ADDR:0x74] [PC:0x100632AA] [Address not mapped to object] []
ORA-04067: not executed, package body "GGUSER.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "GGUSER.DDLREPLICATION"
ORA-04067: not executed, package body "GGUSER.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "GGUSER.DDLREPLICATION"
Solution : disable the trigger 'alter trigger sys.GGS_DDL_TRIGGER_BEFORE disable'
