Thursday, September 29, 2011

How to find if a psu is applied or not on an Oracle database

Please make sure that ORACLE_HOME/OPatch is in your PATH, afterwards execute

opatch lsinventory -bugs_fixed | grep -i 'database psu'

Expected Output:

10248523   12419331  Fri Sep 16 21:09:53 PKST 2011  DATABASE PSU 11.2.0.2.1
11724916   12419331  Fri Sep 16 21:09:53 PKST 2011  DATABASE PSU 11.2.0.2.2 (INCLUDES CPUAPR2011)
12419331   12419331  Fri Sep 16 21:09:53 PKST 2011  DATABASE PSU 11.2.0.2.3 (INCLUDES CPUJUL2011)

After creating policy in Oracle Access Manager, will it directly effect on the application, or we need to restart the OAM?

The policies could learned by the OAM, but these will take some times. typical 4-8hours.
When you don't have time to wait then you may to restart the OAM.

Unable to recieve Grid Control Alerts in Email

Please make sure that you have setup the notifications as per the following documents:

Note 429426.1:Configuring Email Notification Method in EM - Steps and Troubleshooting
Note 429422.1: How To Configure Notification Rules in Enterprise Manager Grid Control?

If still the issue is there then proceed with the following


- Stop the OMS:

cd /opmn/bin
./opmnctl stopall


- Login to the Repository Database as the SYSMAN user and execute:

BEGIN
emd_maintenance.remove_em_dbms_jobs();
emd_maintenance.submit_em_dbms_jobs();
commit;
end;

- Check the schedule of the dbms_jobs using:

SELECT p.display_name
FROM (SELECT display_name, dbms_jobname
FROM mgmt_performance_names
WHERE is_dbmsjob = 'Y') p,
user_jobs j
WHERE UPPER(j.what) LIKE '%'||UPPER(p.dbms_jobname)||'%'
AND (
j.broken != 'N'
OR
j.failures > 0
OR
(j.next_date < SYSDATE-1/48 AND j.this_date IS NULL)
OR
(j.next_date < SYSDATE-1/6 AND j.this_date IS NOT NULL)
);

This query should NOT return any rows.

- Re-start the OMS:

cd /opmn/bin
./opmnctl startall

- Wait for sometime and check whether you are receiving the email notifications.

OPatch cannot find a valid oraInst.loc file to locate Central Inventory. OPatch failed with error code 104

OPatch cannot find a valid oraInst.loc file to locate Central Inventory. OPatch failed with error code 104

$ opatch lsinventory
Invoking OPatch 11.1.0.8.1

Oracle Interim Patch Installer version 11.1.0.8.1
Copyright (c) 2010, Oracle Corporation. All rights reserved.


Oracle Home : /ora/app/product/11.1.0/db_1
Central Inventory : n/a
from :
OPatch version : 11.1.0.8.1
OUI version : 11.1.0.7.0
OUI location : /ora/app/product/11.1.0/db_1/oui
Log file location : n/a

Patch history file: /ora/app/product/11.1.0/db_1/cfgtoollogs/opatch/opatch_history.txt

OPatch cannot find a valid oraInst.loc file to locate Central Inventory.

OPatch failed with error code 104


Work around
--------------

OPTION 1:
a) Use the -invPtrLoc /etc/oraInst.loc
% opatch lsinventory -invPtrLoc /etc/oraInst.loc


OPTION 2:
a) Copy the oraInst.loc file from the /etc to /var/opt/oracle directory
b) Then run the opatch lsinventory
% opatch lsinventory

Doc ID 437026.1 ERROR :"OPatch cannot find a valid oraInst.loc file to locate Central Inventory.OPatch failed with error code 104"

I tried this workaround but it didn't worked. I found that both oraInst.loc and oraInventory were not there so, I had to create a new
inventory which I did as following and afterwards I was able to apply the patch.

cd $ORACLE_HOME/oui/bin
% ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="" ORACLE_HOME_NAME=""


For Eg :
ORACLE_HOME=/u01/app/oracle/product/10.2.0.3
ORACLE_HOME_NAME=OraDb10g_home1

% ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/u01/app/oracle/product/10.2.0.3" ORACLE_HOME_NAME="OraDb10g_home1"

ORA-01157: cannot identify/lock data file 10 - see DBWR trace file

SQL> startup
ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size                  2198344 bytes
Variable Size            1090520248 bytes
Database Buffers         7449083904 bytes
Redo Buffers                9773056 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/oracle/data/data01/RAIDTEST/users02.dbf'


It could be that an OS process is locking the file and because of this oracle is throwing this error, in this case a System reboot would fix the issue.
Any how I followed the action plan as below:.

ls –al /oracle/data/data01/RAIDTEST/users02.dbf

/oracle/data/data01/RAIDTEST/users02.dbf not found

ls –ltr /oracle/data/data01/RAIDTEST/

$ ls -lrt /oracle/data/data01/RAIDTEST/
total 163308512
-rw-r-----   1 oracle     oinstall   9437192192 Aug 19 16:18 temp01.dbf
-rw-r-----   1 oracle     oinstall   20971528192 Aug 19 16:27 raid_TEST_t3_psys_01.dbf
-rw-r-----   1 oracle     oinstall   10485768192 Aug 19 16:28 raid_TEST_i3_psys_01.dbf
-rw-r-----   1 oracle     oinstall   10485768192 Sep 29 10:52 raid_TEST_l02.dbf
-rw-r-----   1 oracle     oinstall   6815752192 Sep 29 10:52 raid_TEST_i02.dbf
-rw-r-----   1 oracle     oinstall   314580992 Sep 29 10:52 raid_TEST_i01.dbf
-rw-r-----   1 oracle     oinstall   11534344192 Sep 29 10:52 raid_TEST_t02.dbf
-rw-r-----   1 oracle     oinstall   524296192 Sep 29 10:52 raid_TEST_t01.dbf
-rw-r-----   1 oracle     oinstall   209723392 Sep 29 10:52 users01.dbf
-rw-r-----   1 oracle     oinstall   20971528192 Sep 29 10:52 undotbs01.dbf
-rw-r-----   1 oracle     oinstall   545267712 Sep 29 10:52 sysaux01.dbf
-rw-r-----   1 oracle     oinstall   744497152 Sep 29 10:52 system01.dbf
-rw-r-----   1 oracle     oinstall   9748480 Sep 29 11:52 control01.ctl

select DISTINCT segment_name,segment_type from dba_extents where file_id = (select file_id from dba_data_files where FILE_name = '/oracle/data/data01/RAIDTEST/users02.dbf')

ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

After looking at the above outputs, I found that the data file does not exist and there are no users in the file.
So I did following and it fixed the issue.

SQL>startup mount;

SQL>alter database datafile 10 OFFLINE DROP;

SQL>alter database open;