Oracle Database Upgrade to 11.2.0.4

Here we are upgrading oracle database from 11.2.0.3 to 11.2.0.4.

Considering the OS version is Linux.

1. Check and make sure you have the latest consistent backup of database. create a restore point as well if possible.

2. Disable all the cron entries on the db server e.g. backups and other stuff which runs on db.

crontab -e

To Dissable Entry

%s/^/####/g
:wq!

3. Take the prechecks.

spool precheck.log

select name,open_mode from v$database;
archive log list
set lines 120
select TABLESPACE_NAME,EXTENT_MANAGEMENT,contents from dba_tablespaces;
select tablespace_name,sum(bytes/1024/1024) from dba_temp_files group by tablespace_name;
select file_name,bytes/1024/1024 from dba_temp_files;
col comp_name for a40
set lines 120
select comp_name,status,version from dba_registry;
show parameter NLS_LENGTH_SEMANTICS
show parameter CLUSTER_DATABASE
show parameter parallel_max_server
show parameter undo_management
show parameter job_queue_process
show parameter pool
show parameter remote_login_password
show parameter spfile
show parameter pga
show parameter sga
show parameter disk_as
select owner,object_type,count(*) from dba_objects group by owner,object_type
order by 1,2;
select owner,count(*) from dba_objects group by owner order by 1;
col owner for a15
col object_name for a35
select OWNER,OBJECT_NAME,OBJECT_TYPE,status from DBA_OBJECTS where
status = 'INVALID';
select count(*) from dba_objects where status='INVALID';
col password for a20
col username for a15
col account_statu for a15
set lines 120
select USERNAME,PASSWORD,ACCOUNT_STATUS,PROFILE,EXPIRY_DATE from dba_users
order by ACCOUNT_STATUS;

select * from v$log;
select PROPERTY_NAME,PROPERTY_VALUE from database_properties;
select * from registry$history;

SELECT DISTINCT owner
FROM DBA_DEPENDENCIES
WHERE referenced_name
IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
AND owner NOT IN ('SYS','ORDPLUGINS');

4. Install the 11.2.0.4 binaries to the new 11.2.0.4 directory inside product directory.

Upload the 11204 binaries zip file on the server say in cd /home/oracle/stage/. make sure the oracle database OS user have access to them. so change their ownership and permissions if needed.

As Oracle user unzip softwaresu - oracle --unzip software 11.2.0.4 unizp p13390677_112040_Linux-x86-64_1of7.zip unzip p13390677_112040_Linux-x86-64_2of7.zip

it should unzip it to one directory “database” ls database p13390677_112040_Linux-x86-64_1of7.zip p13390677_112040_Linux-x86-64_2of7.zip

Response files
Once Oracle 11GR2 binaries are unzipped you can find in directory /home/oracle/stage/database/response dedicated files called “response files” used for silent mode installations.

The response files store parameters necessary to install Oracle components:
db_install.rsp – used to install oracle binaries, install/upgrade a database in silent mode
dbca.rsp – used to install/configure/delete a database in silent mode
netca.rsp – used to configure simple network for oracle database in silent mode cd /home/oracle/stage/database/response $ ls dbca.rsp db_install.rsp netca.rsp

Install Oracle binaries cp db_install.rsp db_install.rsp.bck

Edit file db_install.rsp to set parameters required to install binaries.


--force to install only database software
oracle.install.option=INSTALL_DB_SWONLY
--set your hostname
ORACLE_HOSTNAME=oel6.dbaora.com
--set unix group for oracle inventory
UNIX_GROUP_NAME=oinstall
--set directory for oracle inventory
INVENTORY_LOCATION=/ora01/app/oraInventory
--set oracle home for binaries
ORACLE_HOME=/ora01/app/oracle/product/11.2.04/
--set oracle base
ORACLE_BASE=/ora01/app/oracle 
--set version of binaries to install -- EE - enterprise edition
oracle.install.db.InstallEdition=EE
--force to install advanced options
oracle.install.db.EEOptionsSelection=true
--specify which advanced option to install --
oracle.oraolap:11.2.0.4.0 - Oracle OLAP -- oracle.rdbms.dm:11.2.0.4.0 - Oracle Data Mining -- oracle.rdbms.dv:11.2.0.4.0 - Oracle Database Vault -- oracle.rdbms.lbac:11.2.0.4.0 - Oracle Label Security -- oracle.rdbms.partitioning:11.2.0.4.0 - Oracle Partitioning -- oracle.rdbms.rat:11.2.0.4.0 - Oracle Real Application Testing oracle.install.db.optionalComponents=oracle.rdbms.partitioning:11.2.0.4.0,oracle.oraolap:11.2.0.4.0,oracle.rdbms.dm:11.2.0.4.0,oracle.rdbms.rat:11.2.0.4.0
--specify extra groups for database management
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oper0

once edition is completed. Start binaries installation

cd /home/oracle/stage/database./runInstaller -silent -responseFile /home/oracle/stage/database/response/db_install.rsp


output is following
[oracle@oel6 database]$ ./runInstaller -silent -responseFile /home/oracle/stage/database/response/db_install.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 41752 MB Passed
Checking swap space: must be greater than 150 MB. Actual 4095 MB Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-02-06_09-58-53PM. Please wait ...

[oracle@oel6 database]$ No protocol specified [WARNING] - My Oracle Support Username/Email Address Not Specified [SEVERE] - The product will be registered anonymously using the specified email address. You can find the log of this install session at: /ora01/app/oraInventory/logs/installActions2015-02-06_09-58-53PM.log

The installation of Oracle Database 11g was successful. Please check '/ora01/app/oraInventory/logs/silentInstall2015-02-06_09-58-53PM.log' for more details.

 As a root user, execute the following script(s):
1. /ora01/app/oraInventory/orainstRoot.sh
2. /ora01/app/oracle/product/11.2.0/db_1/root.sh Successfully Setup Software.

You are then asked to run root.sh script as root user. Once it’s done binaries are installed.

Run utlu112i.sql from 11.2.0.4/rdbms/admin location
WARNING:  Database contains INVALID objects prior to upgrade. ....
The list of invalid SYS/SYSTEM objects was written to .... registry$sys_inv_objs. ....
The list of non-SYS/SYSTEM objects was written to .... registry$nonsys_inv_objs. ....
Use utluiobj.sql after the upgrade to identify any new invalid .... objects due to the upgrade.

Copy utltzuv2.sql from 11.2.0.4/rdbms/admin to backup location.

Timezone should be 14, if not please run it after db upgrade. SQL> select * from v$timezone_file; FILENAME VERSION -------------------- ---------- timezlrg_14.dat 14 SQL>


Take Before_upgrade.log spool Before_upgrade.log

select tablespace_name,sum(bytes/1024/1024) from dba_data_files
group by tablespace_name; select file_name from dba_data_files; select file_name,bytes/1024/1024 from dba_temp_files; select * from v$timezone_file; select value from NLS_DATABASE_PARAMETERS where parameter= 'NLS_CHARACTERSET'; SELECT * FROM nls_database_parameters; EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; >> Already done as part of utlu112i.sql grant analyze any to sys; exec dbms_stats.create_stat_table('SYS','dictstattab'); exec dbms_stats.export_schema_stats('SYSMAN','dictstattab',statown => 'SYS'); exec dbms_stats.export_schema_stats('XDB','dictstattab',statown => 'SYS');


SELECT * FROM v$recover_file;

-- This should return no rows.

SELECT * FROM v$backup WHERE status!='NOT ACTIVE'; -- This should return no rows.


SELECT b.FILE#,d.FILE_ID,d.FILE_NAME,b.status FROM v$backup b,dba_data_files d

WHERE b.file#=d.file_id and b.status!='NOT ACTIVE'; select * from dba_2pc_pending; --- If this returns rows you should do the following:


SELECT local_tran_id FROM dba_2pc_pending; EXECUTE dbms_transaction.purge_lost_db_entry(''); COMMIT; SELECT tablespace_name FROM dba_tables WHERE table_name='AUD$';

SELECT name FROM v$controlfile; SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');

--If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the

default tablespace to SYSTEM by using the below command. ALTER user SYS default tablespace SYSTEM; ALTER user SYSTEM default tablespace SYSTEM;

Check connect role and give permissions directly to users as in 11g connect role has

only create session privilege

Put below contents in analyze.sql using vi editor:

Set verify off
Set space 0 Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;' FROM dba_clusters  WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;' FROM dba_tables
WHERE owner='SYS' AND partitioned='NO' AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;' FROM dba_tables WHERE owner='SYS' AND partitioned='YES';
spool off

Now, Run it like below on sql prompt: @analyze.sql

create pfile='/dbs/init.ora' from spfile; SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\'; CREATE RESTORE POINT PRE_UPGRADE_DATABASE GUARANTEE FLASHBACK DATABASE;


Stop database,listener,agent Take the backup of old oracle home

mv old oracle_HOME mv 11.2.0 11.2.0_old change oracle_home to new oracle home path in /etc/oratab and

/var/opt/oracle/oratab

file to load its new location in env variables. vi upgrade_.sql set echo on set time on set timing on spool /home/oracle/pre_11204/upgrade_.log @/rdbms/admin/catupgrd.sql spool off exit

Startup database in upgrade mode SQL> startup upgrade

Run below to execute catupgrd.sql in background
nohup sqlplus "/as sysdba" @upgrade_.sql &;

It can run upto 1 hour or more. once it completes,

Run utlu112s.sql, utlrp.sql from new oracle_home to check the post checks.
 $ sqlplus "/as sysdba
SQL> startup
SQL> spool utlu112s.log
SQL>@?/rdbms/admin/utlu112s.sql
SQL>spool off

 --Compile all invalid objects.
SQL> @?/rdbms/admin/utlrp.sql

 --make sure all sys owned objects are valid.
 col owner for a20;
select owner,object_type,count(*) from dba_objects where status='INVALID'
group by owner,object_type order by owner,object_type ;

Apply psu if needed.

Connect to rman catalog and upgrade catalog using below command
upgrade catalog;
It will ask to type this command again. so type and run it again.
it would take 5 to 10 min to complete.

If all is ok, drop the restore point:
SELECT NAME,SCN,TIME,GUARANTEE_FLASHBACK_DATABASE,
STORAGE_SIZE/1024/1024/1024 STORAGE_SIZE_GB,DATABASE_INCARNATION#
FROM V$RESTORE_POINT;

drop restore point ;

Check with app team and ask them to validation everything from app side. if all is
ok, get confirmation from them to update the compatible parameter. --Update compatible patameter and restart db

alter system set compatible='11.2.0.4' scope=spfile;
shu immediate
startup

$ cd $ORACLE_HOME/lib
$ ls -lrt libsql*
-rw-r--r-- 1 oracle dba 1385072 Jul 20 2013 libsqlplusO.so
-rw-r--r-- 1 oracle dba 1331536 Aug 21 2013 libsql11.a
-rw-r--r-- 1 oracle dba 2189440 Jan 17 15:59 libsqlplus.a
-rw-r----- 1 oracle dba 1307632 Jan 17 16:01 libsqlplus.so $

Make sure the permissions of each of the above file is 644.

If not change it using chmod.

chmod 644 libsql*

Hi SA team

Can you please change below parameters in main.cf file as below tns entry :-- /opt/app/p2dti1d3/oracle/product/11.2.0.4 $ORACLE_HOME :-- /opt/app/p2dti1d3/oracle/product/11.2.0.4 compatible :-- 11.2.0.4

Revert the cron entries.

2. Enable all the cron entries on the db server e.g. backups and other stuff which runs on db.

crontab -e
%s/####//g
:wq!