Monday 14 January 2019

Oracle 11.2.0.1.0 to 11.2.0.4.0 Window Upgrade Standalone



  • Database Name : Testupgr
  • Oracle home : 
  • Listener Name : Listener11g
#######################################################################
  • Download Patchset P13390677 and unzip both file 1 and file 2.  
  • Take existing Full RMAN Backup before upgrade.
  • Oracle database Pre check 
  • Copy the following Files to New Home 11.2.0.4.0
  • Stop and delete services.
  • set oracle home and start upgrade
  • Change timezone version from 11 to 14 .
  • Post Upgrade steps.
  • Change the compatibility parameter.
######################################################################
  • Download patchset p13390677 file1 and file 2 from metalink
  • Install 11.2.0.4 binary on different home.



  • Take RMAN FULL Backup Before starting proceeding further ( Script format below if required) - 

RMAN> run

2> {
3> delete backup;
4> allocate channel d0 type disk FORMAT 'D:\varun_D\upgrades_issue\11january2019\%d_%s_U%U.bak';
5> allocate channel d1 type disk FORMAT 'D:\varun_D\upgrades_issue\11january2019\%d_%s_U%U.bak';
6> crosscheck backup;
7> crosscheck archivelog all;
8> report obsolete;
9> delete noprompt obsolete;
10> sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
11> BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 tag='FULL_BACKUP_WEEKLY' DATABASE INCLUDE CURRENT CONTROLFILE FILESPERSET 1;
12> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL;
13> delete noprompt archivelog all backed up 1 times to disk;
14> SQL "ALTER SYSTEM ARCHIVE LOG CURRENT " ;
15> copy current controlfile to 'D:\varun_D\upgrades_issue\11january2019\control01.bak';
16> RELEASE CHANNEL d0;
17> RELEASE CHANNEL d1;
18> }

  • stop Oracle database services using command. or using saervices.exe can be done .
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>net stop oracleserviceTESTUPGR

Pre-checks :-

  • After Installing 11.2.0.4 , Go to 11.2.0.1 database and run :

SQL> spool pre_upgrade.log
SQL> @C:\app\varunyadav\product\11.2.0\dbhome_1\rdbms\admin\utlu112i.sql
SQL> spool off


  • Copy the following Files to New Home 11.2.0.4 :
Sql > Create pfile from spfile;
1.Spfile/Pfile/init.ora
2.orapwd
3.tnsname.ora
4.Listener.ora 

Stop Service
C:\>net stop oracleserviceTESTDB
The OracleServiceTESTDB service is stopping.
The OracleServiceTESTDB service was stopped successfully.

Delete Service using correct oradim
C:\>where oradim
C:\app\salmqure\product\11.2.0\dbhome_1\BIN\oradim.exe
C:\app\salmqure\product\12.1.0\dbhome_1\BIN\oradim.exe

C:\>C:\app\salmqure\product\11.2.0\dbhome_1\BIN\oradim.exe -delete -sid protect
Instance deleted.

C:\>C:\app\salmqure\product\12.1.0\dbhome_1\BIN\oradim.exe -new -sid protect -syspwd syspassword -startmode auto
Instance created.


set ORACLE_SID=protect
For Windows
C:\>set ORACLE_HOME=C:\app\salmqure\product\12.1.0\dbhome_1
C:\>set PATH=%ORACLE_HOME%\bin;%PATH%
C:\>set ORACLE_SID=PROTECT

For UNIX based
$export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
$export PATH=$ORACLE_HOME\bin:$PATH
$export ORACLE_SID=PROTECT

sql>startup upgrade
sql>spool cat_upgrd.log
@d:/oracle/11.2.0.4/dbhome_1/rdbms/admin/catupgrd.sql
spool off

sql>startup
@d:/oracle/11.2.0.4/dbhome_1/rdbms /admin/utlrp.sql
shutdown immediate;



  • Check timezone version

time zone Version 11 to 14

SQL> sho parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0
SQL> select * from v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_11.dat              11

SQL>  SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
  2  FROM DATABASE_PROPERTIES
  3  WHERE PROPERTY_NAME LIKE 'DST_%'
  4  ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION         11
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        11

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

D:\app\varunyadav\product\11.2.4.0\dbhome_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 5 13:41:13 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 3390558208 bytes
Fixed Size                  2285952 bytes
Variable Size            1962937984 bytes
Database Buffers         1409286144 bytes
Redo Buffers               16048128 bytes
Database mounted.
Database opened.
SQL> set serveroutput on
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
  2  FROM DATABASE_PROPERTIES
  3  WHERE PROPERTY_NAME LIKE 'DST_%'
  4  ORDER BY PROPERTY_NAME;

PROPERTY_NAME
------------------------------
VALUE
--------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
11

DST_SECONDARY_TT_VERSION
0

DST_UPGRADE_STATE
NONE


SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
  2  FROM DATABASE_PROPERTIES
  3  WHERE PROPERTY_NAME LIKE 'DST_%'
  4  ORDER BY PROPERTY_NAME;

PROPERTY_NAME
------------------------------
VALUE
--------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
14

DST_SECONDARY_TT_VERSION
11

DST_UPGRADE_STATE
UPGRADE



Post upgrade steps –
$ sqlplus “/as sysdba”
SQL> STARTUP
SQL> @?/rdbms/admin/utlu112s.sql
select comp_name,version,status from dba_registry;
select owner,count(*) from dba_objects where status != ‘VALID’ group by owner;

  • Change the compatibility parameter
Change the compatible parameter and restart the database.
—-SQL> alter system set compatible=’11.2.0.4.0' scope=spfile;
SQL> shutdown immediate;
SQL> startup;
NOTE :-
if OWB component is not upgraded properly, do the following steps...
¦Upgrade the OWB Component to the database version
1.Start SQLPlus and connect with an account having SYSDBA privileges
2.Drop the OWBSYS schema by executing the script:
SQL> @<OH>/owb/UnifiedRepos/clean_owbsys.sql 
3.Re-create the OWBSYS schema with the script:
SQL> spool <a_file_system_path>cat_owb.log
SQL> @<OH>/owb/UnifiedRepos/cat_owb.sql <tablespace_name>
#############################################################################
SQL> @D:\app\varunyadav\product\11.2.4.0\dbhome_1\owb\UnifiedRepos\clean_owbsys.sql
SQL> @D:\app\varunyadav\product\11.2.4.0\dbhome_1\owb\UnifiedRepos\cat_owb.sql

TROUBLESHOOTING

SQL> @C:\app\varunyadav\product\11.2.0.4\dbhome_1\owb\UnifiedRepos\clean_owbsys.sql
drop user owbsys cascade
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of MDSYS.SDO_GEOR_BDDL_TRIGGER
ORA-01031: insufficient privileges


drop user owbsys_audit cascade
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of MDSYS.SDO_GEOR_BDDL_TRIGGER
ORA-01031: insufficient privileges


drop role OWB_user
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of MDSYS.SDO_GEOR_BDDL_TRIGGER
ORA-01031: insufficient privileges


drop role OWB_DESIGNCENTER_view
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of MDSYS.SDO_GEOR_BDDL_TRIGGER
ORA-01031: insufficient privileges


drop role OWB$CLIENT
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of MDSYS.SDO_GEOR_BDDL_TRIGGER
ORA-01031: insufficient privileges


SQL> ALTER SYSTEM SET "_system_trig_enabled" = FALSE scope=both;

System altered.

SQL> @C:\app\varunyadav\product\11.2.0.4\dbhome_1\owb\UnifiedRepos\clean_owbsys.sql



Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)