ORACLE DATABASE UPGRADE PLAN FROM VERSION 10.2.0.4 TO 11.2.0.2 ON HP-UX V3.

ORACLE DATABASE UPGRADE PLAN FROM VERSION 10.2.0.4 TO 11.2.0.2 ON HP-UX V3.

==============================================

Oracle Database Upgrade plan.

Prepared by: Tapas Kumar Karmakar.

Designation: Oracle DBA.

Base Version: 10.2.0.4

Upgraded Version:  11.2.0.2

Metalink ID: 837570.1 and 560336.1

Table of Contents

1.      Introduction: 2

2.      Objective: 2

3.      Pre-Requisites . 2

4.      Check Stale Statistics Script. 6

5.      Drop duplicate object script. 8

6.      DR Database Upgrade plan. 9

7.      Rollback Plan. 10

1.       Introduction:

                           

This document includes the steps for upgrading database from oracle 10g to 11g on HP-UX V3 platform.

For creating this document guide lines has been taken from Oracle Metalink ID: 837570.1,,560336.1  and Oracle DBA: Suvajit Maity & Sunil Ghate.

It includes the Pre-requisites, Steps for manual upgrade of database from 10g to 11g R2 and also the rollback plan.

2.       Objective:

The main objective of this plan is to provide a detailed plan for manual up gradation of Oracle database. This is a base document which can be followed in any HP-UX V3 server, where database migration is required from Oracle 10.2.0.4 to Oracle 11.2.0.2.

3.       Pre-Requisites .

Sr No	Activity
1	At least 1GB free space in /tmp directory.
2	Approx 7.5 GB space for Oracle 11g binaries
3	Operating System requirements
	■Verify that the system meets the minimum patch bundle requirements using the  following command: /usr/sbin/swlist -l bundle |grep QPK
	Compiler requirements Pro*C/C++, OracleCall Interface, Oracle C++ Call Interface, and Oracle XML Developer’s Kit (XDK) with Oracle Database 11g Release 2 (11.2): ■ A.06.20 (HP C/aC++ Swlist Bundle - C.11.31.04) - Sep 2008
	■ Patch Requirement
	■ PHCO_41479 11.31 Disk Owner Patch ■ PHKL_38038 VM patch - hot patching/Core file creation directory ■ PHKL_38938 11.31 SCSI cumulative I/O patch ■ PHKL_40941 Scheduler patch: post wait hang ■ PHSS_36354 11.31 assembler patch ■ PHSS_37042 11.31 hppac (packed decimal) ■ PHSS_37959 Libcl patch for alternate stack issue fix (QXCR1000818011) ■ PHSS_39094 11.31 linker + fdp cumulative patch ■ PHSS_39100 11.31 Math Library Cumulative Patch ■ PHSS_39102 11.31 Integrity Unwind Library ■ PHSS_38141 11.31 aC++ Runtime ■ PHSS_39824 - 11.31 HP C/aC++ Compiler (A.06.23) patch                                                                                                                              ■ PHSS_41674 ■ PHSS_41683

4	■ Kernal parameter settings Note: If current kernal parameter is greater than or equal to given values then there is no need to change the parameter values.Kindly inform this to UNIX Team. (Kernel Parameter are the Minimum Level Will be Subject to change according to Setup)
	Parameter Recommended Formula or Value
	ksi_alloc_max 32768
	executable_stack 0
	max_thread_proc 1024
	maxdsiz 1073741824 (1 GB)
	maxdsiz_64bit 2147483648 (2 GB)
	maxssiz 134217728 (128 MB)
	maxssiz_64bit 1073741824 (1 GB)
	maxuprc 3686
	msgmni 4096
	msgtql 4096
	ncsize 35840
	maxfiles_lim 63488
	maxfiles 1024
	nflocks 4096
	ninode 34816
	nkthread 7184
	nproc 4096
	semmni 4096
	semmns 8192
	semmnu 4092
	semvmx 32767
	shmmax 1073741824
	shmmni 4096
	shmseg 512
	tcp_smallest_anon_port 9000
	tcp_largest_anon_port 65500
	udp_smallest_anon_port 9000
	udp_largest_anon_port 65500

Sr. No.	Steps	Actions	command	Remarks
1	OS Pre-Requisite	Complete all the Pre-Requisite before preceding the activity. (Refer Pre-Requisite Work Sheet)
2	Oracle Pre-Requisite	Login as oracle user	sudo su - oracle
		Create Oracle 11g home directory in existing oracle file system. E.g. /oracle	cd /oracle mkdir ora11g
		Change the ownership of ora11g directory	chown oracle:dba ora11g
3	Installed Oracle 11g Binaries	Use oracle 11g home directory path to installed oracle binaries.	export ORACLE_HOME=/oracle/ora11g	Use Xmanager to installed oracle binaries.
4	Stop log shipping	login into the Primary database and defer log_archive_dest_state_2 parameter.	sql> alter system set log_archive_dest_state_2=defer scope=both;
5	Cancel Recovery at standby database.	login into the standby database and cancel the recovery.	sql> alter database recover managed standby database cancel;
6	Check INVALID  components/objects.	Ensure that all database components/objects are VALID  in the source database  prior to starting the upgrade.	sql> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where STATUS='INVALID';
		If found invalid objects kindly run utlrp.sql script located at $ORACLE_HOME/rdbms/admin	sql> @?/rdbms/admin/utlrp.sql
7	Check the duplicate objects in SYS and SYSTEM schema	Ensure that you do not have duplicate objects in the SYS and SYSTEM schema. Note: The following objects are permissible duplicate objects: OBJECT_NAME                                    OBJECT_TYPE AQ$_SCHEDULES                                 TABLE AQ$_SCHEDULES_PRIMARY                INDEX DBMS_REPCAT_AUTH                         PACKAGE DBMS_REPCAT_AUTH                         PACKAGE BODY	column object_name format a30 sql> select object_name, object_type  from dba_objects where object_name||object_type in (select object_name||object_type  from dba_objects where owner = 'SYS') and owner = 'SYSTEM';
8	Generate script to drop duplicate objects.	Generate script to drop duplicate objects other than permissible duplicate objects.	Refer DROP_Duplicate_Obj worksheet to generate script.
		Kindly run the script dropsys.sql with system user.	conn system/<password> sql>@dropsys.sql
9	Record DB Link information if any.	Generate Dblink Script, in case the database has to be downgraded again.	spool dblink.sql SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10) ||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10) ||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''||L.HOST||''''||chr(10)||';' TEXT FROM SYS.LINK$ L, SYS.USER$ U WHERE L.OWNER# = U.USER#; spool off
10	Cold Backup	Perform cold backup of database as well as oracle 10g binaries (DC/DR)	export ORACLE_HOME=<10g oracle_home> sqlplus "/as sysdba" shut immediate;
11	Run Pre-Upgrade step	Startup the database with old oracle home i.e. oracle10g  and run the Upgrade steps.	export ORACLE_HOME=<10g oracle_home> sqlplus "/as sysdba" startup spool Before_Upgrd.sql sql>@/oracle/ora11g/rdbms/admin/utlu112i.sql spool off
12	Check Schemas with Stale Statistics	check_stale_stats.sql script will be primarily used during database upgrade before starting the actual upgrade. Execution of this script will report stale statistics if any. It reports on schemas which are part of DBA_REGISTRY as these are the ones which are upgraded. If it finds any stale statistics it will give the recommended commands to remove those stale statistics. Run the script as sys user take the spool for the same. Script kept at : /oracle/check_stale_stats.sql	sqlplus "/as sysdba" spool chk_stale_stat.log sql> @/oracle/check_stale_stats.sql spool off
13	Collect Optimizer Statistics	collect Optimizer Statistics by running the command as sys user	sqlplus "/as sysdba" sql> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
14	Check datafiles status	Ensure that no files need media recovery and that no files are in backup mode.	sql>SELECT * FROM v$recover_file; sql>SELECT * FROM v$backup WHERE status != 'NOT ACTIVE'; Note: This should return no rows.
15	Create pfile from spfile	Create pfile from spfile and stored in New Oracle Home (11g)	sqlplus "/as sysdba" sql> create pfile='<NEW_ORACLE_HOME/dbs/init<oracle_sid>.ora' from spfile;
16	Remove Parameters from pfile.	Remove the USER_DUMP_DEST, BACKGROUND_DUMP_DEST parameters and set DIAGNOSTIC_DEST='/oracle/11g/app' parameter from parameter file.	cd /oracle/ora11g/dbs vi init<oracle_sid>.ora Note: Remove deprecated parameters and set new parameters.
17	Shutdown database and listener		sqlplus "/as sysdba" sql> shut immediate exit $ lsnrctl stop <Listener_Name>
18	set the enviornment	Set the enviorment which points to 11g binaries.	export ORACLE_HOME=/oracle/ora11g export PATH=$ORACLE_HOME/bin:$PATH export ORACLE_BASE=/oracle
19	Startup database in upgrade mode	Start the database in upgrade mode	sqlplus "/ as sysdba" sql> startup UPGRADE;
20	Run Upgrade script		Spool upgrade.log sql>@?/rdbms/admin/catupgrd.sql spool off	Check for errors if any
21	Shutdown database		sql>shut immediate
22	Startup database		sql>startup
23	Run object validation script	After upgrade If any invalid objects found, run "utlrp" script.	sql>@?/rdbms/admin/utlrp.sql
24	Run post upgrade script		sql>@?/rdbms/admin/utlu112s.sql
		Refresh AWR views	sql>@?/rdbms/admin/catuppst.sql
25	Copy tns and listener files. Start the listener	copy the old tnsnames.ora and listener.ora from old oracle_home to oracle 11g oracle_home	cp tnsnames.ora /oracle/ora11g cp listener.ora /oracle/ora11g lsnrctl start LISTENER
26	Database components	Check whether all the componants are upgraded to 11g	sql>select COMP_NAME,VERSION from dba_registry;
27	User profile	Change in user profile of oracle	Make changes in .prfile file add entries specific to oracle 11g. export ORACLE_HOME=/oracle/ora11g export PATH=$ORACLE_HOME/bin:$PATH export ORACLE_BASE=/oracle

4.       Check Stale Statistics Script.

Check Stale object script and output.

-- - - - - - - - - - - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - - - - - - - - - - - - --  NAME:  CHECK_STALE_STATS.SQL --   Execute as SYS as sysdba -- ---------------------------------------------------------------------------------------------------------------- -- AUTHOR:  --    Raja Ganesh - Oracle Support Services - DataServer Group --    Copyright 2008, Oracle Corporation      -- ----------------------------------------------------------------------------------------------------------------- -- PURPOSE: -- This script is an automated way to deal with stale statistics -- operations that are required to be done as part of manual -- upgrade OR when reported by DBUA. -- -- This script will work in both Windows and Unix platforms from database -- version 9.2 or higher. -- ------------------------------------------------------------------------------------------------------------------ -- DISCLAIMER: --    This script is provided for educational purposes only. It is NOT  --    supported by Oracle World Wide Technical Support. --    The script has been tested and appears to work as intended. --    You should always run new scripts on a test instance initially. -- ------------------------------------------------------------------------------------------------------------------- SET FEEDBACK OFF SET LINESIZE 250 SET SERVEROUTPUT ON DECLARE -- Variables declared P_OTAB DBMS_STATS.OBJECTTAB; MCOUNT NUMBER := 0; P_VERSION VARCHAR2(10); -- Cursor defined CURSOR c1 IS SELECT distinct schema FROM dba_registry ORDER by 1; -- Beginning of the anonymous block BEGIN -- Verifying version from v$instance SELECT version INTO p_version FROM v$instance; DBMS_OUTPUT.PUT_LINE(chr(13)); -- Defining Loop 1 for listing schema which have stale stats FOR x in c1 LOOP DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>x.schema,OPTIONS=>'LIST STALE',OBJLIST=>p_otab); -- Defining Loop 2 to find number of objects containing stale stats FOR i in 1 .. p_otab.count LOOP IF p_otab(i).objname NOT LIKE 'SYS_%' AND p_otab(i).objname NOT IN ('CLU$','COL_USAGE$','FET$','INDPART$', 'MON_MODS$','TABPART$','HISTGRM$', 'MON_MODS_ALL$', 'HIST_HEAD$','IN $','TAB$', 'WRI$_OPTSTAT_OPR','PUIU$DATA', 'XDB$NLOCKS_CHILD_NAME_IDX', 'XDB$NLOCKS_PARENT_OID_IDX', 'XDB$NLOCKS_RAWTOKEN_IDX', 'XDB$SCHEMA_URL', 'XDBHI_IDX', 'XDB_PK_H_LINK') THEN -- Incrementing count for  each object found with statle stats mcount := mcount + 1; END IF; -- End of Loop 2 END LOOP; -- Displays no stale statistics, if coun  is 0 IF mcount!=0 THEN -- Displays Schema with stale stats if count is greater than 0 DBMS_OUTPUT.PUT_LINE(chr(13)); DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------'); DBMS_OUTPUT.PUT_LINE('-- '|| x.schema || ' schema contains stale statistics use the following to gather the statistics '||'--'); DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');                                                 -- Displays Command to be executed if schema with stale statistics is found depending on the version. IF SUBSTR(p_version,1,5) in ('8.1.7','9.0.1','9.2.0') THEN DBMS_OUTPUT.PUT_LINE(chr(13)); DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_SCHEMA_STATS('''||x.schema||''',OPTIONS=>'''||'GATHER STALE'||''', ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => '''||'FOR ALL COLUMNS SIZE AUTO'||''', CASCADE => TRUE);'); ELSIF SUBSTR(p_version,1,6) in ('10.1.0','10.2.0','11.1.0','11.2.0') THEN DBMS_OUTPUT.PUT_LINE(chr(13)); DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('''||x.schema||''',OPTIONS=>'''||'GATHER STALE'||''', ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => '''||'FOR ALL COLUMNS SIZE AUTO'||''', CASCADE => TRUE);'); ELSE DBMS_OUTPUT.PUT_LINE(chr(13)); DBMS_OUTPUT.PUT_LINE('Version is '||p_version); END IF; ELSE DBMS_OUTPUT.PUT_LINE('-- There are no stale statistics in '|| x.schema || ' schema.'); DBMS_OUTPUT.PUT_LINE(chr(13)); END IF; -- Reset count to 0. mcount := 0; -- End of Loop 1 END LOOP; END; / SET FEEDBACK ON -- - - - - - - - - - - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - - - - - - - - - - - - - - SCRIPT OUTPUT: SQL> @check_schema_stale_stats.sql -- There are no stale statistics in CTXSYS schema. -- There are no stale statistics in DMSYS schema. -- There are no stale statistics in EXFSYS schema. -- There are no stale statistics in MDSYS schema. -- There are no stale statistics in OLAPSYS schema. -- There are no stale statistics in ORDSYS schema. ------------------------------------------------------------------------------------------------------- -- SYS schema contains stale statistics use the following to gather the statistics -- ------------------------------------------------------------------------------------------------------- EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYS',OPTIONS=>'GATHER', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE); -- There are no stale statistics in SYSMAN schema. -- There are no stale statistics in WMSYS schema. -- There are no stale statistics in XDB schema.

5.       Drop duplicate object script.

Steps for identifying duplicate objects ,with related error and remedy for fixing those errors.

set pause off set heading off set pagesize 0 set feedback off set verify off spool dropsys.sql select 'DROP ' || object_type || ' SYSTEM.' || object_name || ';' from dba_objects where object_name||object_type in    (select object_name||object_type      from dba_objects     where owner = 'SYS') and owner = 'SYSTEM'; spool off exit

Note: You may receive one or more of the following errors:

ORA-2266 (unique/primary keys in table referenced by enabled foreign keys):  If you encounter this error then some of the tables you are dropping have constraints that prevent the table from being dropped. To fix this problem you will have to manually drop the objects in a different order than the script does.

     

ORA-2429 (cannot drop index used for enforcement of unique/primary key): This is similar to the ORA-2266 error except that it points to an index. You will have to manually disable the constraint associated with the index and then drop the index.

ORA-1418 (specified index does not exist):

 This occurs because the table that the index was created on  has already been dropped  which also drops the index. When the script tries to drop the index it is no longer there  and thus the ORA-1418 error. You can safely ignore this error.

6.       DR Database Upgrade plan.

Note: Upgrade DR database after getting confirmation from customer about successful upgrade of primary database.

Sr. No.	Steps	Actions	command	Remarks
1	OS Pre-Requisite	Complete all the Pre-Requisite before proceeding the activity. (Refer Pre-Requisite Work Sheet)
2	Oracle Pre-Requisite	Login as oracle user	sudo su - oracle
		Create Oracle 11g home directory in existing oracle file system. E.g. /oracle	cd /oracle mkdir ora11g
		Change the ownership of ora11g directory	chown oracle:dba ora11g
3	Installed Oracle 11g Binaries	Use oracle 11g home directory path to installed oracle binaries.	export ORACLE_HOME=/oracle/ora11g	Use Xmanager to installed oracle binaries.
	Stop Listener services		lsnrctl stop LISTENER
	Stop database		shutdown immediate;
	Set enviornment varaibles		export ORACLE_HOME=<location of Oracle 11.2> export PATH=$ORACLE_HOME/bin:$PATH export ORACLE_BASE=<Oracle_Base set during installation>
	Configure listener & start listener		change oracle home entry in listner.ora lsnrctl start LISTENER
	Start database	Open database in mount mode	sql>startup mount
	Start recovery		sql>alter database recover managed standby database disconect from session;

7.       Rollback Plan.

Sr. No.	Steps	Actions	command	Remarks
1	Stop Listener services of 11g		lsnrctl stop LISTENER
2	Stop 11g database		shutdown immediate;
3	Set environment variables of 10g		export ORACLE_HOME=<location of Oracle 10g> export PATH=$ORACLE_HOME/bin:$PATH
4	Restore backup
5	Start 10g listener		lsnrctl start LISTENER
6	Start database	Open database in read write mode	sql>startup

Note: This plan is already tested in HP_UX V3 and Linux platform and working fine without any issue. 

           --------------------------------------------------------End Of Activity-----------------------------------------------------------