DB Details:
Name : testSID: test
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 its 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 11: Check database registry components.
SQL> select comp_id,status,version from dba_registry;
Step 12: Now run the below scripts
- utlu112s.sql
- catuppst.sql
- utlrp.sql
Step 13: 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 14: 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
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 !