Better way to check Applied Patches

Being an Apps DBA patching is our bread and butter. And they way we check it make the difference. We had one issue and I was going through metalink note : 364439.1, which had this query. So thought of sharing with you all. Here you go:
===================================================
set serveroutput on
DECLARE
TYPE p_patch_array_type is varray(10) of varchar2(10);
--
p_patchlist p_patch_array_type;
p_appltop_name varchar2(50);
p_patch_status varchar2(15);
p_appl_top_id number;
--
CURSOR alist IS
select appl_top_id, name
from ad_appl_tops;
--
procedure println(msg in varchar2)
is
begin
dbms_output.enable;
dbms_output.put_line(msg);
end;
--
BEGIN
open alist;
--
p_patchlist:= p_patch_array_type('3240000','3460000','4204335','4125550','3942483','4733943');
--
LOOP
FETCH alist INTO p_appl_top_id,p_appltop_name;
EXIT WHEN alist%NOTFOUND;
--
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name ':');
for i in 1..p_patchlist.count
loop
p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,p_patchlist(i));
println('..Patch ' p_patchlist(i) ' was ' p_patch_status);
end loop;
END if;
println('.');
END LOOP;
--
close alist;
END;
/
===========================================================

Sample Output
---------------------------
Server1:
..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was EXPLICIT
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was EXPLICIT
..Patch 3942483 was NOT_APPLIED
..Patch 4733943 was EXPLICIT
.
.
Server2:
..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was NOT_APPLIED
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was EXPLICIT
..Patch 3942483 was EXPLICIT
..Patch 4733943 was NOT_APPLIED
.
.
Server3:
..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was NOT_APPLIED
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was NOT_APPLIED.
.Patch 3942483 was NOT_APPLIED
..Patch 4733943 was NOT_APPLIED
---------------
Happy Troubleshooting !!!

Renaming/Dropping and recreating online redo logs and Standby logfiles

We are in process of testing Dataguard with Physical standby. After we created Primary and Standby DB's we realize what we haven't put file names as per our standard. So I got the task to rename both, online redo logs and standby logs. This what I did :

1. Following is the script to identify all log files we have
SELECT GROUP#, BYTES, 'ONLINE' AS TYPE FROM V$LOG
UNION
SELECT GROUP#, BYTES, 'STANDBY' AS TYPE FROM V$STANDBY_LOG
ORDER BY 1;
2. Make sure protection mode is MAXPERFORMANCE:
SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE FROM V$DATABASE

First we start working on Standby database
3. STOP redo apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
4. Set STANDBY_FILE_MANAGEMENT to MANUAL.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';
5. Gather current status of all member.
SQL> select member from v$logfile;
6. Prepare command to drop standby redo logfiles.
select 'alter database drop standby logfile group ' group# ';' from v$standby_log;
7. Prepare command to drop online redo logfiles.
select 'alter database drop logfile group ' group# ';' from v$log;
8. Check the status of online redolog group.
SELECT GROUP#, STATUS FROM V$LOG;
GROUP# STATUS
---------- ----------------
1 CLEARING_CURRENT
3 CLEARING
2 CLEARING
We can not drop CLEARING_CURRENT status logs, for that you have sync with Primary (Easy way is, leave them for a while and move ahead with droping Primary Database redo logs, and come back to Standby and you will be able to do it).
9. Clear the log group first before drop.
ALTER DATABASE CLEAR LOGFILE GROUP 1;
10. Now drop online redo log.
alter database drop logfile group 1;
Follow the same process for rest of the groups.
11. Now drop standby redo logs, leave the one which refuses to drop.
alter database drop standby logfile group 5;
12. Now create new online redo logs.
ALTER DATABASE ADD LOGFILE GROUP 1 '/path/redo_01.dbf' SIZE 10M;
13. Now we will crate standby redo logs.
ALTER DATABASE ADD STANDBY LOGFILE '/path/redo_std_by_05.dbf' SIZE 10M REUSE;
14. Again check the current status of member.
select member from v$logfile;

Now you will see latest updated names for all files. The one which left we can do them after We are done with Primary DB.

Now on Primary Database:
15. Follow step 5,6,7 to know current status and prepare commands.
16. Drop Standby redo logs and recreate them
alter database drop standby logfile group 5;
ALTER DATABASE ADD STANDBY LOGFILE '/path/redo_std_by_05.dbf' SIZE 10M REUSE;
17. Drop online Redo logs, recrate, switch and drop next inactive one.
alter database drop logfile group 1;
ALTER DATABASE ADD LOGFILE GROUP 1 '/path/redo_01.dbf' SIZE 10M;
ALTER SYSTEM SWITCH LOGFILE;
SELECT GROUP#, STATUS FROM V$LOG;
18. Check the new status.
select member from v$logfile;

Now come back to Standby Database back and drop recreate left over online and standby redo logs.
19. Set STANDBY_FILE_MANAGEMENT to AUTO.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
20. start redo apply.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
21. Check the status of applied log again.
select sequence#, applied from v$archived_log;

Happy Troubleshooting !!!

User unable to connect to EBS with Kerberos

We had one issue, one of the user who rejoined us having issues in accessing Oracle Application with his ID. I did checked his access details and everything looks good to me. But whenever he hit the URL it says you do not have permission to access this page. Another direct access via ADI is working fine for him, but Oracle EBS having issues.
I did a query at OID end to findout if something wrong there, I used the following query to get his data:

ldapsearch -L -h myserver -p 389 -s sub -b "cn=example,cn=example, dc=example,dc=com" -w password -v "cn=*" uid orclguid

I got the 2 matches for his account. Which was causing the issue. Now which is active account that we need to findout. I checked with AD administrator which account is active for this user based on OU. So he gave me the output which I used to delete inactive account. And update GUID in FND_USER for this user and it worked.

User was very happy so do I.

Happy Troubleshooting !!!