DB Details: Name : test SID: test Source DB Version: 11.2.0.1 Target DB Version: 11.2.0.4 |
Step 1: Ask your Application Team whether their Application is compatible with new Oracle version to be upgraded i.e. 11.2.0.4. If so then proceed, otherwise do not start upgrade process. Step 2 : Take full database backup of existing database plus archivelog, controlfile and spfile. |
Step 3: Capture below details from existing 11.2.0.1 database. –dbinfo.sql spool dbinfo.txt set lines 300 pages 3000 set lines 300 pages 3000 col DB_UNIQUE_NAME for a12 col OPEN_MODE for a12 col host_name for a17 select name,instance_name,db_unique_name,open_mode,database_role,controlfile_type,log_mode,flashback_on,to_char(startup_time,'dd-mm-yyyy hh24:mi:ss') startup_time,host_name from v$database,v$instance; select * from v$version; set lines 300 pages 3000 col COMP_ID for a10 col COMP_NAME for a40 col STATUS for a17 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; set lines 300 pages 3000 col property_name for a33 col property_value for a33 select property_name,property_value from database_properties where property_name like '%NLS_%' order by 1; select * from v$timezone_file; set lines 300 pages 3000 select owner,OBJECT_TYPE,status,count(*) from dba_objects where owner not in('SYS','SYSTEM') and status='INVALID' group by owner,OBJECT_TYPE,status order by 1; select count(*) from dba_objects where status='INVALID' and owner not in('SYS','SYSTEM'); 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'; SELECT * FROM DBA_DEPENDENCIES WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS'); SELECT version FROM v$timezone_file; select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8; SELECT * FROM v$recover_file; SELECT * FROM v$backup WHERE status != 'NOT ACTIVE'; select * from v$database_block_corruption: SELECT * FROM dba_2pc_pending; SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1) FROM v$parameter WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%'; –Disable any cronjob set pagesize 2000 set lines 2000 set long 99999 select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs where owner not in('SYS','SYSTEM'); SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM'); SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = 'GLOBAL'; !ps -ef | grep agent !emctl stop dbcontrol SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name; spool off; |
Step 4: Download patchset for 11.2.0.4 setup with latest OCT2016 patch(Ex. : Here OCT2016 patch used). Install new 11.2.0.4 binaries on server. Step 5: For applying opatch, you need to stop DB and it's associated services like - - MTS - dbscheduler - oracleservice cmd> Opatch apply |
Step 6: Configure parameter files and copy below files to target Oracle Home 11.2.0.4. - pfile - spfile - password - tnsnames.ora - listener.ora |
Step 7: Now run utlu112i.sql and rectify any conflict in it for the upgrade process. Step 8: Now it's a time to delete the existing Oracle Service 11.2.0.1 First shut down all Oracle Services before deleting Oracle Service. Shutdown DB and associated Listener. - MTS - dbscheduler - oracleservice cmd> oradim -delete -sid test |
Step 9: Set the below environment variables for newly installed Oracle Home 11.2.0.4 and create a new service for Oracle 11.2.0.4 - ORACLE_SID - ORACLE_ BASE - ORACLE_HOME - PATH cmd> oradim -new -sid test -startmode auto Step 10: Check once again if any service is running from Oracle Home 11.2.0.4. Stop it if it is running. |
Step 11: Start the upgrade process and run upgrade scripts one by one. SQL> startup upgrade SQL> spool catupgrd_spool.log SQL> catupgrd.sql SQL> spool off; Check if any ORA- errors in the log file using below command. cmd> type catupgrd_spool.log | findstr /i /b -ora |
Step 12: Check database registry components. SQL> select comp_id,status,version from dba_registry; Step 13: Now run the below scripts - utlu112s.sql - catuppst.sql - utlrp.sql Step 14: Create listener using NETCA and copy old tnsnames.ora entry to new tnsnames.ora file Check listener is running from New oracle 11.2.0.4 Home and everything is fine. |
Step 15: For upgrade time zone, download dst package script and copy it on server and run below scripts. It will automatically upgrade the time zone by shutting down the database and putting into the upgrade mode. SQL > @DST_check.sql SQL> @DST_apply.sql Step 16: Verify all Oracle Services are up and running fine and these services are running from Oracle 11.2.0.4. |
Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get next blog updates !
Thank you for your comment !