Monday, September 6, 2010

Rebuild reporting database

In one of my previous posts, I had spoken about creating a reporting database that is editable at the same time, though the edits are transient. The last month, I faced an interesting situation wherein we had to rebuild this reporting database. In this post, I'll talk about rebuild the reporting database when it is corrupted.


Context
The standby database has been corrupted due to a server crash and needs to be rebuilt from the primary database.


Solution

I followed the steps mentioned in this post about creating a physical standby database. The steps in the pre-requisites can be skipped because they are already existing.


The rest of the steps hold good!

Creating a physical standby database

Requirement

Create a standby database for the primary production database.


Pre-requisites

1. The primary database is in archive log mode.


2. Primary and standby database names:

Primary DB Name: PRIM
Primary DB Host: primary.hostname.com


Standby DB Name: STBY
Standby DB Host: standby.hostname.com


3. Initialization parameters required for enabling standby instance to be set on the primary:





*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STBY)'

*.LOG_ARCHIVE_DEST_2='SERVICE=STBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY'

*.log_archive_dest_state_2='ENABLE'

*.log_file_name_convert='/u001/app/oracle/stby/data/','/opt/apps/prim/data/'

*.standby_file_management='auto'

*.db_file_name_convert='/u001/app/oracle/stby/data/','/opt/apps/prim/data/'

*.fal_client='PRIM'

*.fal_server='STBY'

*.db_name='PRIM'

*.db_unique_name='PRIM'





4. The listener and tnsnames to be modified on the primary:

listener.ora on primary



# When primary becomes standby (switchover).
STBY =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCSTBY))
(ADDRESS= (PROTOCOL= TCP)(Host= primary.hostname.com )(Port= 1532))
)

SID_LIST_STBY =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /opt/dbms/app/oracle/product/oraprim/10.2.0)
(SID_NAME = STBY)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/dbms/app/oracle/product/oraprim/10.2.0)
(PROGRAM = extproc)
)
)

# Normal primary database listener
PRIM =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCPRIM))
(ADDRESS= (PROTOCOL= TCP)(Host= primary.hostname.com )(Port= 1532))
)

SID_LIST_PRIM =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /opt/dbms/app/oracle/product/oraprim/10.2.0)
(SID_NAME = PRIM)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/dbms/app/oracle/product/oraprim/10.2.0)
(PROGRAM = extproc)
)
)



tnsnames.ora on primary




STBY=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=standby.hostname.com)(PORT=1526))
(CONNECT_DATA=
(SID=STBY)
)
)

PRIM = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=primary.hostname.com)(PORT=1532))
(CONNECT_DATA=(SID=PRIM))
)




Backup tnsnames.ora on primary (for switchover)



## Create a tnsnames_ifile.ora.
STBY=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=primary.hostname.com)(PORT=1532))
(CONNECT_DATA=
(SID=STBY)
)
)

PRIM = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=standby.hostname.com)(PORT=1526))
(CONNECT_DATA=(SID=PRIM))
)





5. Create the initialization file for the standby database.



*.db_create_file_dest='/u001/app/oracle/stby/data'

*.db_file_name_convert='/opt/apps/prim/data/','/u001/app/oracle/stby/data/'

*.db_name='PRIM'

*.db_recovery_file_dest='/u001/app/oracle/stby/flashrecovery'

*.db_recovery_file_dest_size=42949672960

*.db_unique_name='STBY'

*.fal_client='STBY'

*.fal_server='PRIM'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STBY)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/u001/app/oracle/stby/arch/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=STBY'

*.LOG_ARCHIVE_DEST_2='SERVICE=PRIM LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PRIM'

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'

*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

*.log_file_name_convert='/opt/apps/prim/data/','/u001/app/oracle/stby/data/'

*.standby_file_management='auto'



6. Create the listener.ora and the tnsnames.ora files:

listener.ora on standby



# For normal standby operations
STBY =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCSTBY))
(ADDRESS= (PROTOCOL= TCP)(Host= standby.hostname.com )(Port= 1526))
)

SID_LIST_STBY =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /u001/app/oracle/stby/10203)
(SID_NAME = STBY)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u001/app/oracle/stby/10203)
(PROGRAM = extproc)
)
)

# For switchover (ie., when standby becomes the primary).
PRIM =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCPRIM))
(ADDRESS= (PROTOCOL= TCP)(Host= standby.hostname.com )(Port= 1526))
)

SID_LIST_PRIM =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /u001/app/oracle/stby/10203)
(SID_NAME = PRIM)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u001/app/oracle/stby/10203)
(PROGRAM = extproc)
)
)



tnsnames.ora for standby




STBY=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=SUOMT34L.dev.qintra.com)(PORT=1526))
(CONNECT_DATA=
(SID=STBY)
)
)

PRIM=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=primary.hostname.com)(PORT=1532))
(CONNECT_DATA=
(SID=PRIM)
)
)




Backup tnsnames.ora on standby (for switchover)



## Create a tnsnames_ifile.ora.
STBY=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=primary.hostname.com)(PORT=1532))
(CONNECT_DATA=
(SID=STBY)
)
)

PRIM = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=standby.hostname.com)(PORT=1526))
(CONNECT_DATA=(SID=PRIM))
)




7. Last but not the least, SSH Equivalence needs to be established between the primary database owner and the standby database owner, to facilitate the transfer of archives.



Steps - using manual hotbackup

1. Generate the hot backup script on the primary database using the below sql:


set echo off heading off feedback off verify off
set pagesize 0
spool backup.sql
set linesize 80
set serveroutput on size 1000000
set linesize 150
DECLARE
cursor C_ts_name is
select tablespace_name from dba_tablespaces
order by tablespace_name;
BEGIN
for ts_name in C_ts_name loop
dbms_output.put_line('alter tablespace ' ts_name.tablespace_name
' begin backup;');
for dbf_name in (select file_name from sys.dba_data_files
where tablespace_name = ts_name.tablespace_name) loop
dbms_output.put_line(' scp -p ' dbf_name.file_name
' standby_db_owner@standby.hostname.com:/u001/app/oracle/stby/data/.');
end loop;
dbms_output.put_line('alter tablespace ' ts_name.tablespace_name
' end backup;');
end loop;
END;
/






2. Kick off the hotbackup using the script so generated, on the primary database. This script also takes care of the copying of datafiles to the standby server.


3. Once the backup is completed, copy all the archives generated during the backup from primary archive location to the standby archive location.


4. Generate the standby controlfile on the primary using the below command:




Alter database create standby controlfile as '<destination>';

Eg:

SQL> alter database create standby controlfile as '/export/home/oraprim/sb_cntrl01.dbf';






The created file must meet two conditions:

a. Its filename must be different from any other control file.

b. Must be created after the backup of the datafiles.


5. Copy the controlfile so generated from the primary to the standby. To be on the safer side, always multiplex the standby control files. So, copy the standby controlfiles as per the multiplexed order. Also ensure that the parameter control_files in the init.ora is also in sync with this.

6. Start the standby listener.

7. Mount the standby database.

SQL> startup nomount

SQL> alter database mount standby database;

8. Recover the standby database till the latest archive log using the below command. Note that the command "alter database recover continue default" needs to be executed as many times as there are archive logs to be applied!

Now, I prefer to do an incomplete recovery till the lastest SCN. Find the scn number, using the below steps and then perform the recovery.

a. Find the latest archive log generated on the primary after the backup is done.

b. Find the next_change# from v$archived_log view:

SQL> select next_change# from v$archived_log where sequence# = &sequence#;

SQL> alter database recover standby database until change <SCN>;

SQL> alter database recover continue default;


9. After all the archives have been applied, cancel the recovery. Note that you should not receive any errors (esp. ORA-1547 errors) when you cancel the recovery.



SQL> alter database recover cancel;




10. Open the database for read only access. The temp files will be added automatically.


SQL> alter database open read only;



11. Shutdown the database and then mount the standby database.


SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;



12. Start the MRP (managed recovery process):


SQL> alter database recover managed standby database disconnect;




13. Perform log switches on the primary to test the MRP. The archives on the primary should automatically be copied over to the standby archive location and automatically be applied.



Voila! Your dataguard is ready to be used.

Tuesday, May 18, 2010

ORA-07445 [kopxccc()+124] error trying to submit a concurrent request

I am writing this blog entry to share an interesting error with you. There is a parameter ORA_NLS10 without setting which, the database was throwing a lot of ORA-07445 errors. I spoke about the ORA_NLS10 parameter in a previous post. A related issue was occuring here too.

Issue

Unable to submit concurrent requests. Receiving the below error:

APP-FND-01564: ORACLE error 3113 in FDFDDS_RAISE_DFF_COMPILED


Also, receiving ORA-07445 errors in the alert log.

Mon May 17 05:54:00 2010
Completed: ALTER DATABASE OPEN
Mon May 17 05:57:12 2010
Errors in file /u005/app/oracle/cpe2eprd/cpe2eprddb/10.2.0/admin/cpe2eprd_cpeidb/udump/cpe2eprd_ora_1094.trc:
ORA-07445: exception encountered: core dump [lfilic()+328] [SIGSEGV] [Address not mapped to object] [0x000000040] [] []
Mon May 17 06:03:18 2010
Errors in file /u005/app/oracle/cpe2eprd/cpe2eprddb/10.2.0/admin/cpe2eprd_cpeidb/udump/cpe2eprd_ora_2966.trc:
ORA-07445: exception encountered: core dump [kopxccc()+124] [SIGSEGV] [Address not mapped to object] [0x000000062] [] []
Mon May 17 06:06:40 2010
Errors in file /u005/app/oracle/cpe2eprd/cpe2eprddb/10.2.0/admin/cpe2eprd_cpeidb/udump/cpe2eprd_ora_3956.trc:
ORA-07445: exception encountered: core dump [kopxccc()+124] [SIGSEGV] [Address not mapped to object] [0x000000062] [] []


Cause

Database was started with incorrect ORA_NLS10 parameter.

Analysis

As per MOS Note ID: 553264.1, ORA_NLS10 parameter should be set to $ORACLE_HOME/nls/data/9idata.

Solution

1. Shutdown the database.

2. Set the parameter ORA_NLS10 to the correct value as per MOS Note ID: 553264.1

export ORA_NLS10=$ORACLE_HOME/nls/data/9idata

3. Startup the database.


References

1. MOS Note ID: 553264.1

Wednesday, March 31, 2010

WF Mailer Error - Unable to make a connection with the user and responsibility

Yesterday, I faced an issue while starting WF Mailer in a development instance. It was a clone from production and was recently patched, during the course of which, autoconfig was also run. Without further ado, let me explain the problem and the solution:

Issue

Receiving below error while trying to start WF Mailer:


[Mar 30, 2010 4:22:44 AM CDT]:1269940964344:Thread[BES Dispatch Thread,5,main]:0:-1:suomt32l:10.6.71.100:-1:-1:ERROR:[SVC-GSM-WFMLRSVC-801965 : oracle.apps.fnd.cp.gsc.SvcComponentContainer.handleComponentEvent(int, String, String)]:Could not start component; performing rollback -> oracle.apps.fnd.cp.gsc.SvcComponentException: Validation failed for the following parameters ->

>>>>>>>>>>>>>>{FRAMEWORK_USER=Unable to make a connection with the user and responsibility}.<<<<<<<<<<<<<<

Parameters were -> {INBOUND_MAX_IGNORE_SIZE=1000, INBOUND_MAX_LOOKUP_CACHE_SIZE=100, ATTACH_IMAGES=Y, ALLOW_FORWARDED_RESPONSE=Y, INBOUND_UNSOLICITED_THRESHOLD=2, NODENAME=WFMAIL, AUTOCLOSE_FYI=Y, INBOUND_PROTOCOL=IMAP, PROCESSOR_READ_TIMEOUT_CLOSE=Y, ATTACHED_URLS=WFMAIL:ATTACHED_URLS, TEST_ADDRESS=NONE, PROCESSOR_OUT_THREAD_COUNT=1, EXPUNGE_ON_CLOSE=Y, ATTACH_STYLESHEET=Y, OUTBOUND_CONNECTION_TIMEOUT=120, INBOUND_MAX_RET_EMAIL_SIZE=100, MAX_INVALID_ADDR_LIST_SIZE=100, PROCESSOR_MAX_LOOP_SLEEP=60, SEND_ACCESS_KEY=N, PROCESSOR_IN_THREAD_COUNT=1, FRAMEWORK_APP=1, CLOSED=WFMAIL:CLOSED, INBOUND_FETCH_SIZE=100, SUMMARY=WFMAIL:SUMMARY, ENABLE_STYLESHEET=N, PROCESSOR_ERROR_LOOP_SLEEP=60, OPEN_MAIL_FYI=WFMAIL:OPEN_MAIL_FYI, FRAMEWORK_RESP=20420, ALTERNATE_EMAIL_PARSER=oracle.apps.fnd.wf.mailer.DirectEmailParser, INBOUND_PASSWORD=_$@^@6@9^$4 `+{90_8A_%&B~+8?@9(?~(B!!~{($A?B&, HTTP_USER_AGENT=Mozilla/4.76, RESET_NLS=N, PROCESS=PROCESS, OPEN_MAIL_DIRECT=WFMAIL:OPEN_MAIL_DIRECT, PROCESSOR_LOOP_SLEEP=5, MESSAGE_FORMATTER=oracle.apps.fnd.wf.mailer.NotificationFormatter, REPLYTO=osowner@****.com, FRAMEWORK_USER=0, CANCELED=WFMAIL:CANCELED, OUTBOUND_PROTOCOL=SMTP, DISCARD=DISCARD, ACCOUNT=appltst, FROM=Workflow Mailer Oracle Development, PROCESSOR_DEFER_EVTDATA_READ=Y, WARNING=WFMAIL:WARNING, PROCESSOR_MAX_ERROR_COUNT=1000, INBOUND_CONNECTION_TIMEOUT=120, HTMLAGENT=http://****.com:8005/pls/SID, INBOX=INBOX, OPEN_INVALID_MORE_INFO=WFMAIL:OPEN_INVALID_MORE_INFO, INBOUND_SERVER=****.com, OPEN_MORE_INFO=WFMAIL:OPEN_MORE_INFO, INLINE_ATTACHMENT=N, OPEN_INVALID=WFMAIL:OPEN_INVALID, EMAIL_PARSER=oracle.apps.fnd.wf.mailer.TemplatedEmailParser, DIRECT_RESPONSE=N, OPEN_MAIL=WFMAIL:OPEN_MAIL, FRAMEWORK_URL_TIMEOUT=30, COMPONENT_LOG_LEVEL=5, SUMHTML=WFMAIL:SUMHTML, PROCESSOR_READ_TIMEOUT=10, DEBUG_MAIL_SESSION=N, OUTBOUND_SERVER=********.com}


Symptom

* Instance is cloned from Production
* Instance is using virtual hosts


Cause

Whenever, Autoconfig is run, it will set the same server ID for both virtual and Physical hosts which causes this problem.

Solution

1. Shutdown Concurrent Manager

2. Take a backup of FND_Nodes.

3. Delete Physical host from OAM (OAM > Administrator > Hosts)

4. Register same host again from OAM (or through forms).

5. Reregister the physical host for concurrent processing as below:

SQL> exec fnd_cp_fndsm.register_fndsm_fcq('');

5. Start Concurrent Manager

6. Mailer was working fine after this.

Reference

Thanks to Sundeep Dubey's blog for pointing me in the right direction!

Tuesday, February 2, 2010

Quirky 11i Apps Patch queires

Long time since I blogged. I was a bit busy with my vacations and work! Anyway, check out my travelogue, if you are interested enough.


This time, I plan to write about a few quirky and rarely used queries to find out whether a patch is applied.


Method 1

Check Patches applied from Oracle Applications Manager (OAM).

a) Connect to OAM:

http://hostname.domain:port/servlets/weboam/oam/oamLogin

Go to Site Map-->Maintenance-->Applied Patches

Enter Patch ID and press 'Go'

See if Patch is returned.


Method 2

Use 'adphrept' utility documented in Metalink Note:181665.1, section 'Retrieving information on patches or files that have been applied via adpatch'.


$AD_TOP/patch/115/sql/adphrept.sql (available on admin and web nodes in multi tier APPL_TOP)

(1) Patch History report usage:
$AD_TOP/patch/115/sql/adphrept.sql <query_depth> < bug_number or ALL> <bug_product or ALL> \
<end_date_from (mm/dd/rr or ALL)> <end_date_to (mm/dd/rr or ALL)> \
<patchname/ALL> <patchtype/ALL> <level/ALL> <language/ALL> \
<appltop/ALL> <limit to forms server? (Y/N)> \
<limit to web server? (Y/N)> \
<limit to node server? (Y/N)> \
<limit to admin server? (Y/N)> \
<only patches that change DB? (Y/N)>


Specify 1 or 2 or 3 for query_depth


1-> Details of patches only


2-> Details of patches and their Bug Fixes only


3-> Details of patches their Bug Fixes and Bug Actions


The output would be generated in the current directory and would be named adfilerep.xml.


Example1: To get the complete patch details for patches applied in Oct 2009:


$ sqlplus apps @$AD_TOP/patch/115/sql/adphrept.sql 3 ALL ALL 10/01/09 10/31/09 ALL ALL ALL ALL ALL N N N N N


SQL*Plus: Release 8.0.6.0.0 - Production on Thu Nov 26 01:41:59 2009


(c) Copyright 1999 Oracle Corporation. All rights reserved.


Enter password:


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options



Please be patient, this will take a very long time.


If you are prompted for any parameters, please exit and review the parameter list you have submitted.


Gathering data for report. Report output will be written to adfilerep.xml.


Writing data to report file.
Your AD Patch History XML Report is ready at adfilerep.xml.


Note:Please copy the file to OA_HTML top to view it thru browser




Example2: To find whether bug number 6502082 is applied or not.


$ sqlplus apps @$AD_TOP/patch/115/sql/adphrept.sql 1 6502082 all all all all all all all all n n n n n


SQL*Plus: Release 8.0.6.0.0 - Production on Thu Nov 26 01:22:31 2009


(c) Copyright 1999 Oracle Corporation. All rights reserved.


Enter password:


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options



This report should take less than a minute.


If you are prompted for any parameters, please exit and review the parameter list you have submitted.


Gathering data for report. Report output will be written to adfilerep.xml.


Writing data to report file.
Your AD Patch History XML Report is ready at adfilerep.xml.


Note:Please copy the file to OA_HTML top to view it thru browser



Method 3

You might also use the following query, however methods 1 and 2 are more reliable.

SQL> SELECT DISTINCT RPAD(a.bug_number,
11) RPAD(e.patch_name,
11) RPAD(TRUNC(c.end_date),
12) RPAD(b.applied_flag, 4) BUG_APPLIED
FROM
ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d ,
ad_applied_patches e
WHERE
a.bug_id = b.bug_id AND
b.patch_run_id = c.patch_run_id AND
c.patch_driver_id = d.patch_driver_id AND
d.applied_patch_id = e.applied_patch_id AND
a.bug_number in ('<XXXXXX>','<YYYYYY>')
ORDER BY 1 DESC;



A modified version of the above query is as below:

SELECT DISTINCT a.bug_number, e.patch_name,
TRUNC(c.end_date) end_date, b.applied_flag BUG_APPLIED
FROM
ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d ,
ad_applied_patches e
WHERE
a.bug_id = b.bug_id AND
b.patch_run_id = c.patch_run_id AND
c.patch_driver_id = d.patch_driver_id AND
d.applied_patch_id = e.applied_patch_id AND
a.bug_number in ('&bug_number1','&bug_number2')
ORDER BY 1 DESC;



Note: Please enter Patch number in place of and , e.g '3453499'


Method 4

In multi-node environment you are advised to use Patch Query from section "Checking the Patch Requirements on each Appl_Top" of the Metalink Note:364439.1 - Tips and Queries for Troubleshooting Advanced Topologies.

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;
/



A modification of the above query to find whether a single patch is applied or not:

-- Query to find whether a single patch was applied or not.
set serveroutput on;
DECLARE
p_appltop_name varchar2(50);
p_patch_status varchar2(15);
p_appl_top_id number;
--
cursor atoplist 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 atoplist;
--
LOOP
FETCH atoplist INTO p_appl_top_id,p_appltop_name;
EXIT WHEN atoplist%NOTFOUND;
--
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name ':');
p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,&bug);
println('..Patch ' &bug ' was ' p_patch_status);
END if;
println('.');
END LOOP;
--
close atoplist;
END;
/




References:


1. Metalink (MOS) Note: 443761.1 - How to check if a certain Patch was applied to Oracle Applications instance?

2. Metalink (MOS) Note: 181665.1 - Release 11i Adpatch Basics

3. Metalink (MOS) Note: 364439.1 - Tips and Queries for Troubleshooting Advanced Topologies