Wednesday, March 7, 2012

How to find out the SQL with most redo log generation

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well.
We can query Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.

SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc, 1, 2, 3, 4;

We need to run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

This just give me an idea about what is currently happening. But I want more historical information. Let us say for last 3 days, what was the SQLs involed in most redo.
I did generate an AWR for last two days but that did not gave me the information that I was looking for. So I decided to directly query DBA_HIST views.

I found a way to get the hostorical db block change information from DBA_HIST views. dba_hist_seg_stat view has a column called db_block_changes_delta column. We can query this view and find out the sum of block changes per object.

SELECT dhso.object_name,
sum(db_block_changes_delta)
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND begin_interval_time BETWEEN to_date('2011_05_25 08′,'YYYY_MM_DD HH24′) AND to_date('2011_05_27 00′,'YYYY_MM_DD HH24′)
GROUP BY dhso.object_name
order by sum(db_block_changes_delta) desc
/

WINDOW_EVENTS 809200
WINDOWS 153968
HISTGRM$ 65040
I_H_OBJ#_COL# 61168
MODEL_REPORT 31840
DATA_PROCESS 16784
RULE_EXECUTE_INFO 12448
PK_MODEL_REPR 9088

Above query gives the sum(db_block_changes_delta). Now we can use dba_hist_sqlstat view to get the SQL statements. So I did run another query to see what SQLs are run on WINDOW_EVENTS or WINDOW during same period.

SELECT distinct dbms_lob.substr(sql_text,4000,1),
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE '%WINDOW%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND dhss.sql_id = dhst.sql_id and rownum<2;

INSERT INTO WINDOWS (
EVENT_ID,
DATA_PROCESS_ID,
EVENT_TIME,
DENOMINATOR_STR,
TIME_WINDOW_ID,
TIME_WINDOW_PANE,
OWNER_ORG_ID
)

INSERT INTO WINDOW_EVENTS (
EVENT_ID ,
DATA_PROCESS_ID ,
EVENT_TIME ,
EVENT_EPOCH_TIME ,
OWNER_ORG_ID ,
DENOMINATOR_STR
)

This is just the part of the output. There was a lot this insert during that same period. Now I know which SQL was actually generating a lot of redo log.

Get Oracle Certifications for all Exams
Free Online Exams.com

Which Sessions Generating Lots of Redo logs in oracle

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com
Using below query, we can easily identify which session/transaction generating more redo logs.

Query 1:

V$Sess_io & V$Session

SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc;

BLOCK_CHANGES column shows how much blocks have been changed the session.

Query 2:

Below query shows the amount of undo blocks & undo records accessed by the transaction.


SELECT s.sid, s.serial#, s.username, s.program, 
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5,6 desc;
Get Oracle Certifications for all Exams
Free Online Exams.com

Saturday, March 3, 2012

No Cancellation Notification Received

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: No Cancellation Notification Received



Solution:




No Cancellation Notification Received
• Please upload manually $FND_TOP/patch/115/xml/US/wfstde.wfx using WFXLOAD:

EXAMPLE

. APPSORA.env

cd $FND_TOP/patch/115/xml/US

jre oracle.apps.fnd.wf.WFXLoad -u apps apps_pwd \
host..com:1501:db_sid thin \
US wfstde.wfx

Root Cause:

Pre wfstde.wfx Manual Upload:

SQL> select name, generate_function from wf_events where name = 'oracle.apps.wf.notification.cancel';


NAME
-----------------------------------------------------------------------
oracle.apps.wf.notification.cancel

GENERATE_FUNCTION
-------------------------------------


Post wfstde.wfx Manual Upload:


SQL> connect apps/apps@orlaol-11i
Connected.
SQL> /

NAME
-----------------------------------------------------------------------
oracle.apps.wf.notification.cancel

GENERATE_FUNCTION
-------------------------------------
WF_XML.Generate



References:


Java Mailer and Other 11.5.9/OWF G Current Issues in Applications 11i [ID 260393.1]
Get Oracle Certifications for all Exams
Free Online Exams.com

Notification Mailer OWF G Patches

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: Notification Mailer OWF G Patches


Solution:



OWF G Patches
• 3868138 POST OWF G ROLLUP 7
• 3940679 11.5.9:WF DEFERRED AGENT LSNR UNABLE TO PROCESS DEFERRED EVENTS (ORA-1555)
• 3894483 NOTIFICATION MAILER SENDING INVALID RESPONSE NOTIFICATION IN WRONG LANGUAGE
• 3960600 11.5.9: ERROR WHILE OPENING NOTIFICATION DETAIL.HTML LINK ON EMAIL NOTIFICATION
• 3041387 ORA-600: INTERNAL ERROR CODE, ARGUMENTS: [KOPI2_READLEN083]
• 2984987 ORA-00600 [12760] WHEN DEQUEUING USING JAVA API (64 Bit UNIX only)

ORA-00600: internal error code, arguments: [12760] WF_EVENT_OJMSTEXT_QH.DEQUEUE:

select name, value from v$parameter where name='cursor_space_for_time';

FALSE is correct result.


References:


Java Mailer and Other 11.5.9/OWF G Current Issues in Applications 11i [ID 260393.1]
Get Oracle Certifications for all Exams
Free Online Exams.com

Additional Java Mailer Scripts

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: Additional Java Mailer Scripts


Solution:



Additional Scripts
 Work Flow Histograms Creation Script. This is for the Embedded version for Apps only. Performance improvements.

sqlplus apps/apps @wfhistc APPLSYS

 Create Index on WF Queue Tables

sqlplus apps/apps @wfqidxc APPLSYS APPS tablespace_name

Example Syntax:

sqlplus apps/apps @wfqidxc APPLSYS APPS USER_IDX

 Use to diagnose an individual notification once Java Mailer is starting:

sqlplus apps/apps @$FND_TOP/sql/wfmlrdbg.sql NID applsys
 Purges the wf_notification_out outbound message queue and repopulates from the WF_NOTIFICATION table:

sqlplus usr/passwd@db @wfntfqup APPSusr APPSpw FNDusr

Example Syntax:

sqlplus apps/apps@db @wfntfqup apps apps applsys

 This script will move the oracle.apps.wf.notificaiton.send messages from the WF_ERROR queue to the WF_DEFERRED queue for reprocessing. Substitute for Reset Failed in OWF G.

sqlplus usr/passwd@db @wfnequ APPSusr APPSpw FNDusr

Example Syntax:

sqlplus apps/apps@db @wfnequ apps apps applsys

 wfbkgbld.sql - Utility script to rebuild the background queue:

sqlplus wf_account/wf_account @wfbkgbld wf_schema

Example Syntax:

sqlplus apps/apps @wfbkgbld applsys


References:

Java Mailer and Other 11.5.9/OWF G Current Issues in Applications 11i [ID 260393.1]
Get Oracle Certifications for all Exams
Free Online Exams.com

Recreate/Validate WF AQ (The scripts lists the queues they create and its details). Please try using ADADMIN to compile the APPS Schema before trying any of the scripts.

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:
Recreate/Validate WF AQ (The scripts lists the queues they create and its details). Please try using ADADMIN to compile the APPS Schema before trying any of the scripts.


Solution:



Recreate/Validate WF AQ (The scripts lists the queues they create and its details). Please try using ADADMIN to compile the APPS Schema before trying any of the scripts.

 sqlplus APPSusr/APPSpwd @wfquec2.sql APPSusr FNDusr FNDpwd

Example Syntax:
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfquec2.sql apps applsys apps
 sqlplus APPSusr/APPSpwd @wfjmsqc.sql FNDusr FNDpwd

Example Syntax:

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfjmsqc.sql applsys apps

OR

sqlplus APPSusr/APPSpwd @wfjmsqc2.sql FNDusr FNDpwd
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfjmsqc2.sql applsys apps
 sqlplus APPSusr/APPSpwd @wfevquec.sql FNDusr FNDpwd WF_DEFERRED APPSusr

Example Syntax:

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfevquec.sql APPLSYS APPS WF_DEFERRED APPS
OR

sqlplus APPSusr/APPSpwd @wfevquc2 FNDusr FNDpwd qname APPSusr retry delay retention
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfevquc2 APPLSYS APPS WF_DEFERRED APPS 5 3600 86400
 sqlplus APPSusr/APPSpwd @wfmqsubc.sql FNDusr FNDpwd

Example Syntax:

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfmqsubc.sql applsys apps
 sqlplus APPSusr/APPSpwd @afwfqgnt.sql APPSusr APPSpwd FNDusr FNDpwd

Example Syntax:

sqlplus apps/apps @FND_TOP/patch/115/sql/afwfqgnt.sql apps apps applsys apps


References:

Java Mailer and Other 11.5.9/OWF G Current Issues in Applications 11i [ID 260393.1]
Get Oracle Certifications for all Exams
Free Online Exams.com

DIAGNOSTICS (UNIX) Use Equivalent Commands for Windows for Java Mailer issues

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: DIAGNOSTICS (UNIX) Use Equivalent Commands for Windows for Java Mailer issues



Solution:



DIAGNOSTICS (UNIX) Use Equivalent Commands for Windows.
 Please download and run the Java Mailer Diagnotics Test contained in Note: 274764.1

 Please review the "Known Issues for Oracle Workflow Mini-pack 11i.OWF.G" section in Note: 225947.1 andNote: 231286.1

For ORA-24033 Errors:

RDBMS 9.2.0.1 - 9.2.0.3 Customers ONLY:

desc sys.dbms_aqin there should be 3 AQ$_DEQUEUE_IN repeated in it. If there is less than 3 AQ$_DEQUEUE_IN, you need to download and reapply Patch 2805491 which was re-released as a Generic Patch on July 29. Make sure this manual step in the readme is done correctly:

Apply patch 2805491


Patch Special Instructions:
# ---------------------------
# After the patch has been applied please reload the package into
# the database. To do this connect as SYS and execute the following;
#
# SQL> @?/rdbms/admin/prvtaqin.plb
#
# It is recomended that the system should be in a quiescent state.

Apply patch 2757441:

# Patch Special Instructions:
# ---------------------------
# After the patch has been applied please reload the package into
# the database. To do this connect as SYS and execute the following;
#
# SQL> @?/rdbms/admin/prvtreut.plb
#
# It is recomended that the system should be in a quiescent state.

For All Database Versions:

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfmqsubc.sql applsys apps (Adds Subscribers/Recipients)
 Increase Diagnostics:

1. Please change the * Log Level from Exception to Statement on the Workflow Mailer
Configuration page in OAM page 3.

2. Increase Diagnostics Level on Workflow Mailer and Agent Listener Services:

a) Terminate the mailer or agent listener service

b) Edit log level of Mailer or Agent Listener service by:

In OAM Sitemap > Status Overview > View All > Generic Service Component
Container > select Workflow Mailer Service or Workflow Agent Listener Service >
Edit > Edit Service Parameters >

Change from:

SVC_WRITE_DIAG_TO_GSM_LOG=Y:SVC_CONTAINER_LOOP_SLEEP=10:
SVC_CONTAINER_READ_TIMEOUT=10:SVC_CONTAINER_LOG_LEVEL=4:
SVC_COMP_MONITOR_LOOP_SLEEP=60:SVC_COMP_MONITOR_ONDEMAND_FREQ=300:
SVC_COMP_MAX_ERROR_COUNT=5:SVC_PROXY_SET=NONE:
SVC_PROXY_HOST=NONE:SVC_PROXY_PORT=NONE

To:

SVC_WRITE_DIAG_TO_GSM_LOG=Y:SVC_CONTAINER_LOOP_SLEEP=10:
SVC_CONTAINER_READ_TIMEOUT=10:SVC_CONTAINER_LOG_LEVEL=1:
SVC_COMP_MONITOR_LOOP_SLEEP=60:SVC_COMP_MONITOR_ONDEMAND_FREQ=300:
SVC_COMP_MAX_ERROR_COUNT=5:SVC_PROXY_SET=NONE:
SVC_PROXY_HOST=NONE:SVC_PROXY_PORT=NONE

> OK > Save > select Workflow Mailer or Workflow Agent Listener Service > Start
 Return Diagnostics back to Default after uploading log:

1. Please change the * Log Level from Statement to Exception on the Workflow Mailer Configuration
page in OAM page 3.

2. Decrease Diagnostics Level on Workflow Mailer and Agent Listener Services:

a) Terminate the mailer or agent listener service

b) Edit log level of Mailer or Agent Listener service by:

In OAM Sitemap > Status Overview > View All > Generic Service Component Container >
select Workflow Mailer Service or Workflow Agent Listener Service > Edit > Edit Service Parameters >

Change from:

SVC_WRITE_DIAG_TO_GSM_LOG=Y:SVC_CONTAINER_LOOP_SLEEP=10:
SVC_CONTAINER_READ_TIMEOUT=10:SVC_CONTAINER_LOG_LEVEL=1:
SVC_COMP_MONITOR_LOOP_SLEEP=60:SVC_COMP_MONITOR_ONDEMAND_FREQ=300:
SVC_COMP_MAX_ERROR_COUNT=5:SVC_PROXY_SET=NONE:
SVC_PROXY_HOST=NONE:SVC_PROXY_PORT=NONE

To:

SVC_WRITE_DIAG_TO_GSM_LOG=Y:SVC_CONTAINER_LOOP_SLEEP=10:
SVC_CONTAINER_READ_TIMEOUT=10:SVC_CONTAINER_LOG_LEVEL=4:
SVC_COMP_MONITOR_LOOP_SLEEP=60:SVC_COMP_MONITOR_ONDEMAND_FREQ=300:
SVC_COMP_MAX_ERROR_COUNT=5:SVC_PROXY_SET=NONE:
SVC_PROXY_HOST=NONE:SVC_PROXY_PORT=NONE

> OK > Save > select Workflow Mailer or Workflow Agent Listener Service > Start
 Please use ADADMIN to regenerate your jar files using force=y.

Example Errors in your workflow mailer log $APPLCSF/$APPLLOG/FNDCPGSC.txt:

unexpected RuntimeException or other Throwable occurred -> java.lang.NoSuchMethodError
java.lang.NoSuchMethodError

 Please run $FND_TOP/sql/wfver.sql from your concurrent manager server node OS making sure to spool and upload the output. Support need the output to make sure your WF AQ (contains rules, valid, etc), packages, etc are valid.

The last entries in the wfver.sql output should have the same settings as your adovars.env parameters beginning with AF settings for JDK 1.3.1.

 Please review/upload your adovars.env. You must have migrated your concurrent manager tier to JDK 1.3.Note: 130091.1.

These are the correct parameters for the concurrent manager to use JDK 1.3:

AF_JRE_TOP=[JDK13_TOP]

AFJVAPRG=[JDK13_TOP]/bin/Java

AF_CLASSPATH=[JDK13_TOP]/lib/dt.jar:[JDK13_TOP]/lib/tools.jar:
[JDK13_TOP]/jre/lib/rt.jar:[JDK13_TOP]/jre/lib/i18n.jar:
$JAVA_TOP/appsborg2.zip:$JAVA_TOP/apps.zip:
$ORACLE_HOME/forms60/Java:$JAVA_TOP

where [JDK13_TOP] is the full path to the JDK 1.3 installation top directory on Unix.

 Please review the following Metalink Notes as OWF G uses Java and just like Rapidwiz requires an X Server to function correctly:

Note: 181244.1 Using VNC or XVFB as the X server for Applications 11i

Note: 153960.1 FAQ: X Server testing and troubleshooting

 Please run and upload the results of the following:

grep jar $APPL_TOP/admin/adjborg2.txt

It should point to these two files on your concurrent manager node which MUST exist:

aqapi.jar and jmscommon.jar

unzip -l $JAVA_TOP/appsborg2.zip | grep -i jms | wc –l

If this returns 0, then please manually add the path to to the AF_CLASSPATH in the adovars.env residing on the concurrent manager node as a test. appsborg2.zip is the file that should contain these jar files. They are added when you regenerate your jar files and adjborg2.txt is read and used to rebuild appsborg2.zip.

 Please check the timestamp on the classes in these directories for the error could not start Service Component Container because an unexpected RuntimeException or other Throwable occurred -> java.lang.NoSuchMethodError
java.lang.NoSuchMethodError:

$JAVA_TOP/oracle/apps/fnd/cp/gsc

$JAVA_TOP/oracle/apps/fnd/wf/common
 Please provide the versions of these two classes if the timestamp actually shows that you regenerated the classes today and your concurrent manager server node:

strings –a $JAVA_TOP/oracle/apps/fnd/cp/gsc/SvcComponentContainer.class|grep Header

strings -a $JAVA_TOP/oracle/apps/fnd/wf/common/WorkflowResourceStore.class|grep Header

 Listener and Mailer Services will not start if Local System is Disabled. This status is displayed in the $FND_TOP/sql/wfver.sql output:

Status
----------
DISABLED
 Bounce concurrent manager and retest.

References:



Java Mailer and Other 11.5.9/OWF G Current Issues in Applications 11i [ID 260393.1]
Get Oracle Certifications for all Exams
Free Online Exams.com

Testing Java Mailer Connection to Email Server.

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: Testing Java Mailer Connection to Email Server.



Solution:



Testing Java Mailer Connection to Email Server.
 Apply patch 3265133 RECEIVING ERROR WHEN WFMAILER EMAILS -- UNABLE TO CONNECT TO MAIL STORE

This will provide the syntax for the test:

Unix:

$AFJVAPRG -classpath $AF_CLASSPATH oracle.apps.fnd.wf.mailer.Mailer

Win:

%AFJVAPRG% -classpath %AF_CLASSPATH% oracle.apps.fnd.wf.mailer.Mailer

Example Syntax on Windows:

java -Dprotocol=imap -Dserver=rgmamerimap.oraclecorp.com -Dport=143 -Daccount=account
-Dpassword=password oracle.apps.fnd.wf.mailer.Mailer

Successful Connection Results:

Server rgmamerimap.oraclecorp.com at port 143 is reachable
Successfully connected to the IMAP account, folders defined are:

INBOX
discard
processed
trash


References:

Java Mailer and Other 11.5.9/OWF G Current Issues in Applications 11i [ID 260393.1]
Get Oracle Certifications for all Exams
Free Online Exams.com

Java Mailer Cannot Update Context File

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:

Java Mailer Cannot Update Context File



Solution:



Java Mailer Cannot Update Context File
• Note:255063.1
• Note:245344.1

References:


Java Mailer and Other 11.5.9/OWF G Current Issues in Applications 11i [ID 260393.1]
Get Oracle Certifications for all Exams
Free Online Exams.com

Data Guard Gap Detection and Resolution

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: Data Guard Gap Detection and Resolution


Symptoms:
An archive gap is a range of missing archived redo logs created whenever the
standby system is unable to receive the next archived redo log generated by
the primary database.

For example, an archive gap occurs when the network becomes unavailable and
automatic archiving from the primary database to the standby database stops.
When the network is available again, automatic transmission of the redo data
from the primary database to the failed standby database resumes.


Solution:



Methods of Gap Resolution:
===========================

Data Guard provides two methods for gap resolution, automatic and FAL (Fetch
Archive Log). The automatic method requires no configuration while FAL requires
configuration via init.ora parameters. Both methods are discussed below.


Automatic Gap Resolution:
~~~~~~~~~~~~~~~~~~~~~~~~~~~

In both 9.0.1 and 9.2.0 Automatic Gap Resolution is implemented during log
transport processing. As the LGWR or ARCH process begins to send redo over to
the standby, the sequence number of the log being archived is compared to the
last sequence received by the RFS process on the standby. If the RFS process
detects that the archive log being received is greater than the last sequence
received plus one, then the RFS will piggyback a request to the primary to send
the missing archive logs. Since the standby destination requesting the gap
resolution is already defined by the LOG_ARCHIVE_DEST_n parameter on the
primary, the ARCH process on the primary sends the logs to the standby and
notifies the LGWR that the gaps have been resolved.

Starting in 9.2.0, automatic gap resolution has been enhanced. In addition
to the above, the ARCH process on the primary database polls all standby
databases every minute to see if there is a gap in the sequence of archived
redo logs. If a gap is detected then the ARCH process sends the missing
archived redo log files to the standby databases that reported the gap. Once
the gap is resolved, the LGWR process is notified that the site is up to date.


FAL Gap Resolution:
~~~~~~~~~~~~~~~~~~~~~~~~

As the RFS process on the standby receives an archived log, it updates the
standby controlfile with the name and location of the file. Once the MRP
(Media Recovery Process) sees the update to the controlfile, it attempts to
recover that file. If the MRP process finds that the archived log is missing
or is corrupt, FAL is called to resolve the gap or obtain a new copy. Since
MRP has no direct communications link with the primary, it must use the
FAL_SERVER and FAL_CLIENT initialization parameters to resolve the gap.
Both of these parameters must be set in the standby init.ora. The two
parameters are defined as:

FAL_SERVER: An OracleNet service name that exist in the standby tnsnames.ora
file that points to the primary database listener. The FAL_SERVER
parameter can contain a comma delimited list of locations that
should be attempted during gap resolution.

FAL_CLIENT: An OracleNet service name that exist in the primary tnsnames.ora
file that points to the standby database listener. The value of
FAL_CLIENT should also be listed as the service in a remote
archive destination pointing to the standby.

Once MRP needs to resolve a gap it uses the value from FAL_SERVER to call the
primary database. Once communication with the primary has been established,
MRP passes the FAL_CLIENT value to the primary ARCH process. The primary ARCH
process locates the remote archive destination with the corresponding service
name and ships the missing archived redo logs. If the first destination listed
in FAL_SERVER is unable to resolve the gap then the next destination is
attempted until either the gap is resolved or all FAL_SERVER destination have
been tried.

As of 9.2.0 FAL Gap Resolution only works with Physical Standby databases as
the process is tied to MRP. Gap recovery on a logical standby database is
handled through the heartbeat mechanism.


Simulating Gap Recovery
==========================

The follow steps can be used to illustrate and verify both automatic and FAL
gap recovery. As the steps involve shutting down the standby database, which
can impact disaster recovery, it is recommended to perform these procedures
in a test environment.


Automatic Gap Resolution:

1. Shutdown the physical standby database.

2. Determine the current sequence on the primary database.

3. Perform at least three log switches on the primary database.

4. Verify that the logs did not get transferred to the standby archive dest.

5. Start the standby database.

6. Perform a log switch on the primary and verify that the gap gets resolved
on the standby.


FAL Gap Resolution:

1. In the standby init.ora define the fal_server and fal_client parameters.

2. Bounce the standby database so that the parameters are put into effect.

3. Perform three log switches on the primary database.

4. In the standby_archive_dest directory delete the middle archive log on
the standby.

5. Start managed recovery and verify that the gap is resolved by FAL_SERVER
and FAL_CLIENT.


Manually Resolving a Gap:
=============================

In some rare cases it might be necessary to manually resolve gaps. The following
section describes how to query the appropriate views to determine if a gap
exists.

On your physical standby database:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Query the V$ARCHIVE_GAP view:

SQL> SELECT * FROM V$ARCHIVE_GAP;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
----------- ------------- --------------
1 443 446

The query results show that your physical standby database is currently missing
logs from sequence 443 to sequence 446 for thread 1. After you identify the
gap, issue the following SQL statement on the primary database to locate the
archived redo logs on your primary database:

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND
2> SEQUENCE# BETWEEN 443 AND 446;

NAME
--------------------------------------------------------------------------------
/u01/oradata/arch/arch_1_443.arc
/u01/oradata/arch/arch_1_444.arc
/u01/oradata/arch/arch_1_445.arc

Copy the logs returned by the query to your physical standby database and
register using the ALTER DATABASE REGISTER LOGFILE command.

SQL> ALTER DATABASE REGISTER LOGFILE
'/u01/oradata/stby/arch/arch_1_443.arc';
SQL> ALTER DATABASE REGISTER LOGFILE
'/u01/oradata/stby/arch/arch_1_444.arc';
SQL> ALTER DATABASE REGISTER LOGFILE
'/u01/oradata/stby/arch/arch_1_445.arc';

Once the log files have been registered in the standby controlfile, you can
restart the MRP process.


On a logical standby database:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Query the DBA_LOGSTDBY_LOG view.

SQL> SELECT THREAD#, SEQUENCE#, FILE_NAME FROM DBA_LOGSTDBY_LOG L
2> WHERE NEXT_CHANGE# NOT IN
3> (SELECT FIRST_CHANGE# FROM DBA_LOGSTDBY_LOG WHERE L.THREAD# = THREAD#)
4> ORDER BY THREAD#,SEQUENCE#;

THREAD# SEQUENCE# FILE_NAME
---------- ---------- -----------------------------------------------
1 451 /u01/oradata/logical_stby/arch/arch_1_451.arc
1 453 /u01/oradata/logical_stby/arch/arch_1_453.arc


Copy the missing logs to the logical standby system and register them using the
ALTER DATABASE REGISTER LOGICAL LOGFILE statement on your logical standby
database.

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE /u01/oradata/logical_stby/arch/arch_1_452.arc;


After you register these logs on the logical standby database, you can restart
log apply services.

References:


Data Guard Gap Detection and Resolution [ID 232649.1]
Get Oracle Certifications for all Exams
Free Online Exams.com

How To Get Client IP Address For a Locking User

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: How To Get Client IP Address For a Locking User


Symptoms:
This document gives sample coding on getting the IP address of the client for forms deployed on the web.
The goal of this code is to store the information about the IP address in the DB system table v$session using WHEN-NEW-FORM-INSTANCE and WHEN-TIMER-EXPIRED triggers together and WebUtil
WebUtil can only start to communicate with the client once the Form has instantiated the WebUtil PJCs, so that a timer with small duration (2 ms) is created in the WHEN-NEW-FORM-INSTANCE trigger, then the call to the WebUtil is made in a WHEN-TIMER-EXPIRED trigger.



Solution:



Software Requirements/Prerequisites
This sample code is created using Forms Builder 10.1.2.0.2 and the corresponding WebUtil Version.
Configuring the Sample Code
WebUtil Environment Configuration:
Please configure your environment to run WebUtil demo, you can use the readme.html file of the webutil_demo, which can be downloaded from -Webutil Demo Download Location (Doc ID 1272222.1)
Running the Sample Code
For step (4), user needs execute privilege on DBMS_APPLICATION_INFO
For step (7), you need to connect to the DB using any user has a "Select" privilege on v$session
Caution
This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.
Proofread this sample code before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this sample code may not be in an executable state when you first receive it. Check over the sample code to ensure that errors of this type are corrected.
Sample Code
Create IP-Address Sample form
1. Install and configure your Developer installation to use webutil .

2. Create new form using webutil

3. Create a WHEN-NEW-FORM-INSTANCE trigger add the following:
declare
v_timer timer;
begin
v_timer := create_timer('ip_timer',2,no_repeat);
end;

4. Create a database procedure
create or replace procedure SETCLIENTINFO (p_info varchar2) is
begin
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(p_info);
exception
when others then
raise_application_error(-20101,'SETCLIENTINFO: error: '||sqlerrm);
end;

5. Create a WHEN-TIMER-EXPIRED add the following code:
declare
v_trimer_name varchar2(30) := get_application_property(TIMER_NAME);
v_ip_address varchar2(40);
begin
if upper(v_trimer_name) = upper('ip_timer') THEN
v_ip_address := webutil_clientinfo.get_ip_address;
SETCLIENTINFO('Client IP='||v_ip_address);
end if;
end;
6. Call the form

7. Go to SQL*Plus or iSQL*Plus and run the following SQL statement:
select client_info from v$session where client_info is not null;

Sample Code Output
The SQL output in step (7) should be like :
Client IP = x.x.x.x
To avoid webutil performance issues, It is recommended to add this solution in the calling (parent) form of the forms application, and pass the client IP information to any other child form.

References:

How To Get Client IP Address For a Locking User [ID 403192.1]

NOTE:270940.1 - Oracle Forms WebUtil :Technical FAQ
Get Oracle Certifications for all Exams
Free Online Exams.com

How to work around standby database in case of missing archive log file

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: How to work around standby database in case of missing archive log file


Symptoms:
Missing archive log file from production and from standby before apply


Solution:



SQL> alter database recover managed standby database cancel;

2. Shutdown the Standby Database:

SQL> shutdown immediate;

3. Connect to the Primary Database as SYS:

SQL> connect sys/@ as sysdba

4. Create a new Physical Standby Controlfile from the Primary:

SQL> alter database create standby controlfile as '';

5. Copy the new created Standby Controlfile to the Standby Database using OS-Tools (eg. cp or ftp) and replace the current Standby Controlfile with the new created one.

6. Mount the Standby Database with the new Standby Controlfile:

SQL> startup mount
10. Start Managed Recovery again:

SQL> alter database recover managed standby database disconnect;

References:

Note 232649.1 Data Guard Gap Detection and Resolution
Refer to Note.459411.1 Steps to recreate a Physical Standby Controlfile .
Get Oracle Certifications for all Exams
Free Online Exams.com

How to Get the Physical IP Address of the Client Machine

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: How to Get the Physical IP Address of the Client Machine


Solution:



The Physical IP Address is also known as MAC Address or MAC ID or Hardware Address. This can be achieved with a java program which can be implemented via a Forms PJC. This is a three step process
Create the Java Bean
1. Create a Java file "getMacAddress.java" as follows say in d:\pjc folder
import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.InetAddress;
import java.net.UnknownHostException;
import oracle.forms.ui.*;
import oracle.forms.properties.*;

public class getMacAddress extends VBean
{

public getMacAddress()
{
System.out.println("Bean has been initialised");
}

/* public static void main(String[] args)
{
String res = getAddress();
System.out.println(res);
} */

public String getAddress()
{

String macAddress = null;
String getAddress = null;

Process p = null;
BufferedReader in = null;

try {
String osname = System.getProperty("os.name");
if (osname.startsWith("Windows")) {
p = Runtime.getRuntime().exec( new String[] { "ipconfig",
"/all" }, null);

}
// Solaris code must appear before the generic code
else if (osname.startsWith("Solaris") || osname.startsWith("SunOS"))
{
String hostName = getFirstLineOfCommand(new String[] { "uname",
"-n" });
if (hostName != null) {
p = Runtime.getRuntime().exec(
new String[] { "/usr/sbin/arp", hostName }, null);
}
}
else if (new File("/usr/sbin/lanscan").exists()) {
p = Runtime.getRuntime().exec(
new String[] { "/usr/sbin/lanscan" }, null);
}
else if (new File("/sbin/ifconfig").exists()) {
p = Runtime.getRuntime().exec(
new String[] { "/sbin/ifconfig", "-a" }, null);
}

if (p != null) {
in = new BufferedReader(new InputStreamReader(
p.getInputStream()), 128);
String l = null;
while ((l = in.readLine()) != null) {
macAddress = parse(l);
if (macAddress != null && parseShort(macAddress) != 0xff)
break;
}
}

}
catch(IOException e)
{
e.printStackTrace();
}

catch(Exception e)
{
e.printStackTrace();
}

return macAddress;
}


public String parse(String in)
{
int hexStart = in.indexOf("0x");
if (hexStart != -1 && in.indexOf("ETHER") != -1) {
int hexEnd = in.indexOf(' ', hexStart);
if (hexEnd > hexStart + 2) {
return in.substring(hexStart, hexEnd);
}
}

int octets = 0;
int lastIndex, old, end;

lastIndex = in.lastIndexOf('-');

if (lastIndex > in.length() - 2) return null;

end = Math.min(in.length(), lastIndex + 3);

++octets;
old = lastIndex;
while (octets != 5 && lastIndex != -1 && lastIndex > 1) {
lastIndex = in.lastIndexOf('-', --lastIndex);
if (old - lastIndex == 3 || old - lastIndex == 2) {
++octets;
old = lastIndex;
}
}

if (octets == 5 && lastIndex > 1) {
return in.substring(lastIndex - 2, end).trim();
}
return null;
}


public short parseShort(String s) throws NullPointerException {
s = s.toLowerCase();
short out = 0;
byte shifts = 0;
char c;
for (int i = 0; i < s.length() && shifts < 4; i++) { c = s.charAt(i); if ((c > 47) && (c < 58)) { out <<= 4; ++shifts; out |= c - 48; } else if ((c > 96) && (c < 103)) { ++shifts; out <<= 4; out |= c - 87; } } return out; } public String getFirstLineOfCommand(String[] commands) throws IOException { Process p = null; BufferedReader reader = null; try { p = Runtime.getRuntime().exec(commands); reader = new BufferedReader(new InputStreamReader( p.getInputStream()), 128); return reader.readLine(); } finally { if (p != null) { if (reader != null) { try { reader.close(); } catch (IOException ex) {} } try { p.getErrorStream().close(); } catch (IOException ex) {} try { p.getOutputStream().close(); } catch (IOException ex) {} p.destroy(); } } } } 2. open a cmd prompt and go to d:\pjc folder(where the java file is stored) and do the following a) set ORACLE_HOME=d:\oracle\developersuite (substitute your physical ORACLE_HOME for Developer Suite here) b) set PATH=%ORACLE_HOME%\jdk\bin c) set CLASSPATH=%ORACLE_HOME%\forms\java\frmall.jar;d:\pjc;%CLASSPATH% 3. Compile the above java code (getMacAddress.java) using javac javac getMacAddress.java 4. Package getMacAddress.class into a jar file using jar.exe. jar -cvf getMacAddress.jar getMacAddress.class 5. Sign the getMacAddress.jar file by following the instructions in Note 202768.1 Signing a Java Bean for Deployment with Forms 9i / 10g Oracle Jinitiator using JDK 1.3 Code the Form 6. Create a simple form and add a bean area to it. 7. In the implementation class of the bean area put our class "getMacAddress" 8. Create a button with the following code in the When-Button-Pressed trigger: declare hbean1 item := find_item('block5.bean1'); begin Fbean.register_bean(hbean1,1,'getMacAddress'); :text1 := Fbean.Invoke_char(hbean1,1,'getAddress'); end; Note that this code assumes that you have a text item called "text1" and the bean area is in block5 and called "bean1". Please adjust if your naming convention is different. Deploy the Form with the Bean 9.Copy the getMacAddress.jar file into %ORACLE_HOME%/forms/java directory 10. Edit the formsweb.cfg from %ORACLE_HOME%/forms/server/forsweb.cfg Add the getMacAddress.jar to the archive,archive_jini parameters in the configuration section e.g.: [myconfig] archive=frmall.jar,getMacAddress.jar archive_jini=frmall_jinit.jar,getMacAddress.jar 11) Compile and run the form. This code will work with both JInitiator and JRE 1.6.0_X You can download a working example from -> download
Note: If the end user browser plugin is going to be JRE 1.6 + then the following, simpler, java code can be used to get the Physical address. Prior to JDK 1.6 there was no method availaable to get the hardware address of the client. However, in JDK 1.6, a new method getHardwareAddress() is available to get the hardware address. So if the JRE is going to be 1.6 or higher, then the following java source can be used
import java.net.InetAddress;
import java.net.NetworkInterface;
import java.net.SocketException;
import java.net.UnknownHostException;
import oracle.forms.ui.*;

public class getMacAddress extends VBean {

public String bytesText(byte in[]) {

byte ch = 0x00;
int i = 0;

if (in == null || in.length <= 0) return null; String pseudo[] = {"0", "1", "2","3", "4", "5", "6", "7", "8","9", "A", "B", "C", "D", "E","F"}; StringBuffer out = new StringBuffer(in.length * 2); while (i < in.length) { ch = (byte) (in[i] & 0xF0); ch = (byte) (ch >>> 4);
// shift the bits down

ch = (byte) (ch & 0x0F);
// must do this is high order bit is on!
out.append(pseudo[ (int) ch]);
ch = (byte) (in[i] & 0x0F);
out.append(pseudo[ (int) ch]);
out.append("-");
i++;
}

String rslt = new String(out);
return rslt.substring(0,rslt.length()-1);

}


public String getAddress()
{

String res = "none";
try {
InetAddress address = InetAddress.getLocalHost();

NetworkInterface ni = NetworkInterface.getByInetAddress(address);
byte[] mac = ni.getHardwareAddress();

res = bytesText(mac);
}
catch (UnknownHostException e) {
e.printStackTrace();
}
catch (SocketException e) {
e.printStackTrace();
}
return res;
}
}


References:
How to Get the Physical IP Address of the Client Machine [ID 853971.1]

NOTE:202768.1 - Signing a Java Bean for Deployment with Forms 9i / 10g With Oracle Jinitiator.
Get Oracle Certifications for all Exams
Free Online Exams.com

Steps to recreate a Physical Standby Controlfile .

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: Steps to recreate a Physical Standby Controlfile .

Symptoms:

Sometimes it is necessary to recreate the Physical Standby Controlfile for any Reason.
This Document shows Step-by-Step how to recreate the Physical Standby Controlfile.


Solution:



Follow these Steps if you have to recreate the Physical Standby Controlfile:

1. Stop Managed Recovery Process on the Standby Database (if still running):

SQL> alter database recover managed standby database cancel;

2. Shutdown the Standby Database:

SQL> shutdown immediate;

3. Connect to the Primary Database as SYS:

SQL> connect sys/@ as sysdba

4. Create a new Physical Standby Controlfile from the Primary:

SQL> alter database create standby controlfile as '';

5. Copy the new created Standby Controlfile to the Standby Database using OS-Tools (eg. cp or ftp) and replace the current Standby Controlfile with the new created one.

6. Mount the Standby Database with the new Standby Controlfile:

Oracle 8i and 9i (8.x - 9.x):

SQL> startup nomount
SQL> alter database mount standby database;

Oracle 10g and 11g (10.x - 11.x):

SQL> startup mount

7. If the File-Structure is different between Primary and Standby Database and db_file_name_convert/log_file_name_convert is not set, rename the File-Location(s) in the new Standby Controlfile:

SQL> alter database rename file '' to '';

8. Starting with 9i: If you don't have Standby RedoLogs proactively on the Primary and want them on the Standby Database or you had Standby RedoLogs with this Standby Database before, now add the Standby RedoLogs (again):

SQL> alter database add standby logfile group ('Filespecification(s)') size reuse;

Please see
Note 219344.1:Usage, Benefits and Limitations of Standby Redo Logs (SRL)
for further Details about Standby RedoLogs

9. Re-enable Flashback (if Flashback was enabled previously):

SQL>ALTER DATABASE FLASHBACK OFF;
SQL>ALTER DATABASE FLASHBACK ON;

10. Start Managed Recovery again:

SQL> alter database recover managed standby database disconnect [using current logfile];

References:

Note.459411.1 Steps to recreate a Physical Standby Controlfile .
NOTE:734862.1 - Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files




Get Oracle Certifications for all Exams
Free Online Exams.com

How to Relink Oracle Database Software on UNIX

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: How to Relink Oracle Database Software on UNIX


Solution:



Relinking Oracle
================

Background:
Applications for UNIX are generally not distributed as complete executables.
Oracle, like many application vendors who create products for UNIX, distribute
individual object files, library archives of object files, and some source
files which then get "relinked" at the operating system level during
installation to create usable executables. This guarantees a reliable
integration with functions provided by the OS system libraries.

Relinking occurs automatically under these circumstances:

- An Oracle product has been installed with an Oracle provided installer.
- An Oracle patch set has been applied via an Oracle provided installer.

The following information has been added to the 'Certify' section of MyOracleSupport:

General Notes For Oracle Database - Enterprise Edition:
O/S Information:
The vendors guarantee operating system binary compatibility; therefore, no
reinstall or relink of the Oracle software is required when upgrading these
operating systems unless specifically stated otherwise.

Relinking Oracle manually is suggested under the following circumstances
(even though the OS vendor may not require it):

- An OS upgrade has occurred.
- A change has been made to the OS system libraries. This can occur during
the application of an OS patch.
- A new install failed during the relinking phase.
- Individual Oracle executables core dump during initial startup.
- An individual Oracle patch has been applied (however, explicit relink
instructions are usually either included in the README or integrated into
the patch install script)

ATTENTION :
This note could not be appropriate for 11GR2 GI/ASM home's (GI : Grid Infrastructure)
you may rather refer to clusterware admin guide and/or
Note 284785.1 : How to Check Whether Oracle Binary/Instance is RAC Enabled and Relink Oracle Binary in RAC

Customers who want to (or have been advised to) manually relink should follow
the procedure below.

[Step 1] Log into the UNIX system as the Oracle software owner
==============================================================================
Typically this is the user 'oracle'.


[STEP 2] Verify that your $ORACLE_HOME is set correctly:
===============================================================================
For all Oracle Versions and Platforms, perform this basic environment check
first:

% cd $ORACLE_HOME
% pwd

...Doing this will ensure that $ORACLE_HOME is set correctly in your current
environment.


[Step 3] Verify and/or Configure the UNIX Environment for Proper Relinking:
===============================================================================
For all Oracle Versions and UNIX Platforms:
The Platform specific environment variables LIBPATH, LD_LIBRARY_PATH, &
SHLIB_PATH typically are already set to include system library locations like
'/usr/lib'. In most cases, you need only check what they are set to first,
then add the $ORACLE_HOME/lib directory to them where appropriate.
i.e.: % setenv LD_LIBRARY_PATH ${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
(see Note:131207.1 How to Set UNIX Environment Variables for help with
setting UNIX environment variables)

If on AIX with:
--------------

NOTE: Before relinking, after stopping all required processes, issue the
following as root to detach all files from memory that are not attached
to an active process:

# /usr/sbin/slibclean

Oracle 7.3.X:
- Set LIBPATH to include $ORACLE_HOME/lib

Oracle 8.0.X:
- Set LIBPATH to include $ORACLE_HOME/lib
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib and
$ORACLE_HOME/network/lib (Required when using Oracle products that
use Java)
- Set LINK_CNTRL to L_PTHREADS_D7 if using AIX 4.3. ('oslevel' verifies
OS version)

Oracle 8.1.X, 9.X.X, 10.X.X or 11.x.x:
- For 8.1.5, set LINK_CNTRL to L_PTHREADS_D7
- If not 8.1.5, ensure that LINK_CNTRL is not set
- Set LIBPATH to include $ORACLE_HOME/lib


If on DATA GENERAL AVIION (DG) with:
-----------------------------------

Oracle 7.3.X or 8.0.X:
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib
- ensure TARGET_BINARY_INTERFACE is unset

Oracle 8.1.X:
- Set LD_LIBRARY_PATH to include
$ORACLE_HOME/lib:$ORACLE_HOME/JRE/lib/PentiumPro/native_threads

If on HP-UX with:
----------------

Oracle 7.3.X, 8.0.X, 8.1.X;
- Set SHLIB_PATH to $ORACLE_HOME/lib
If using 64bit 8i Oracle, also
- Set LD_LIBRARY_PATH to $ORACLE_HOME/lib64
- ensure LPATH is unset

Oracle 9.X.X, 10.X.X or 11.x.x;
- ensure LPATH is unset

If on NCR with:
--------------

Oracle 7.3.X, 8.0.X or 8.1.X:
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib:/usr/ccs/lib

If on SCO UNIXware with:
-----------------------

Oracle 7.3.X or 8.0.X:
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib

Oracle 8.1.X:
- Set LD_LIBRARY_PATH to include
$ORACLE_HOME/lib:$ORACLE_HOME/JRE/lib/x86at/native_threads

If on SGI with:
--------------

32bit Oracle 7.3.X or 8.0.X:
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib
- Set SGI_ABI to -32

64bit Oracle 8.0.X or 8.1.X (8i is only available in 64bit):
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib
- Set SGI_ABI to -64
- If one does not already exist, create the file compiler.defaults and
set the COMPILER_DEFAULTS_PATH variable:

In the Oracle software owner's $HOME directory, create a file called
'compiler.defaults':

% cd $HOME
% echo "-DEFAULT:abi=64:isa=mips3:proc=r10k" > compiler.defaults

Then set the environment variable COMPILER_DEFAULTS_PATH to point to the
$HOME directory.

% setenv COMPILER_DEFAULTS_PATH $HOME

If this is not set, relinking will fail because the compiler defaults to
MIPS4 objects although Oracle requires MIPS3.
- Set LD_LIBRARY64_PATH to include the $ORACLE_HOME/lib and the
$ORACLE_HOME/javavm/admin directories.
- Set LD_LIBRARYN32_PATH to include the $ORACLE_HOME/lib32 directory.
NOTE: LD_LIBRARY64_PATH & LD_LIBRARYN32_PATH must be undefined when
installing software with Oracle Universal Installer.

If on SOLARIS (Sparc or Intel) with:
------------------------------------

Oracle 7.3.X, 8.0.X, or 8.1.X:
- Ensure that /usr/ccs/bin is before /usr/ucb in $PATH
% which ld ....should return '/usr/ccs/bin/ld'

If using 32bit(pre 9i) Oracle,
- Set LD_LIBRARY_PATH=$ORACLE_HOME/lib

If using 64bit(pre 9i) Oracle,
- Set LD_LIBRARY_PATH=$ORACLE_HOME/lib
- Set LD_LIBRARY_PATH_64=$ORACLE_HOME/lib64

Oracle 9.X.X or higher:
- LD_LIBRARY_PATH & LD_LIBRARY_PATH_64 do not need to be set to include
a reference to $ORACLE_HOME/lib or $ORACLE_HOME/lib64 for a generic database
software installation.
(However they should not contain a reference to the ORACLE_HOME of another Oracle version)


If on Digital/Tru64, IBM/Sequent PTX, Linux or any other UNIX Platform not
mentioned above with:
------------------------------------------------------------------------------

Oracle 7.3.X, 8.0.X, 8.1.X, 9.X.X, 10.X.X or 11.x.x:
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib


[Step 4] For all Oracle Versions and UNIX Platforms:
===============================================================================
Verify that you performed Step 2 correctly:

% env | pg ....make sure that you see the correct absolute path for
$ORACLE_HOME in the variable definitions.


[Step 5] For all Oracle Versions and UNIX Platforms:
===============================================================================
Verify umask is set correctly:

% umask

This must return 022. If it does not, set umask to 022.

% umask 022
% umask

[Step 6] Run the OS Commands to Relink Oracle:
===============================================================================
Important Note: Before relinking Oracle, shut down both the database and the
listener.
Important Note: The following commands will output a lot of text to your
session window. To capture this output for upload to support,
redirect the output to a file.
Important Note: If relinking a client installation, it's expected that some
aspects of the following commands will fail if the components
were not originally installed.

For all UNIX platforms:

Oracle 7.3.x
------------
For executables: oracle, exp, imp, sqlldr, tkprof

% cd $ORACLE_HOME/rdbms/lib
% make -f ins_rdbms.mk install

For executables: svrmgrl, svrmgrm

% cd $ORACLE_HOME/svrmgr/lib
% make -f ins_svrmgr.mk linstall minstall <- linstall is for svrmgrl, minstall is for svrmgrm For executables: sqlplus % cd $ORACLE_HOME/sqlplus/lib % make -f ins_sqlplus.mk install For executables: dbsnmp, oemevent, oratclsh % cd $ORACLE_HOME/network/lib % make -f ins_agent.mk install For executables: names, namesctl % cd $ORACLE_HOME/network/lib % make -f ins_names.mk install For executables: tnslsnr, lsnrctl, tnsping, csmnl, trceval, trcroute % cd $ORACLE_HOME/network/lib % make -f ins_network.mk install Oracle 8.0.x ------------ For executables: oracle, exp, imp, sqlldr, tkprof, mig, dbv, orapwd, rman, svrmgrl, ogms, ogmsctl % cd $ORACLE_HOME/rdbms/lib % make -f ins_rdbms.mk install For executables: sqlplus % cd $ORACLE_HOME/sqlplus/lib % make -f ins_sqlplus.mk install For executables: dbsnmp, oemevent, oratclsh, libosm.so % cd $ORACLE_HOME/network/lib % make -f ins_oemagent.mk install For executables: tnslsnr, lsnrctl, namesctl, names, osslogin, trcasst, trcroute % cd $ORACLE_HOME/network/lib % make -f ins_network.mk install Oracle 8.1.X, 9.X.X, 10.X.X or 11.X.X ------------------------------------- *** NEW IN 8i AND ABOVE *** A 'relink' script is provided in the $ORACLE_HOME/bin directory. % cd $ORACLE_HOME/bin % relink ...this will display all of the command's options. usage: relink
accepted values for parameter: all, oracle, network, client,
client_sharedlib, interMedia, precomp, utilities, oemagent, ldap

Note: ldap option is available only from 9i. In 8i, you would have to manually relink
ldap.

You can relink most of the executables associated with an Oracle Server Installation
by running the following command:
% relink all
This will not relink every single executable Oracle provides(you can
discern which executables were relinked by checking their timestamp with
'ls -l' in the $ORACLE_HOME/bin directory). However, 'relink all' will
recreate the shared libraries that most executables rely on and thereby
resolve most issues that require a proper relink.


11.2.X special instructions
--------------------------------------
In 11.2 relink script will only accept "all" as argument


-or-

Since the 'relink' command merely calls the traditional 'make' commands, you
still have the option of running the 'make' commands independently:

For executables: oracle, exp, imp, sqlldr, tkprof, mig, dbv, orapwd, rman,
svrmgrl, ogms, ogmsctl

% cd $ORACLE_HOME/rdbms/lib
% make -f ins_rdbms.mk install

NOTE: After relinking the oracle executable, make sure that the
permissions on the executable are 6751 (-rwsr-s--x). If they are
not, run the following command as the Oracle software owner:

% cd $ORACLE_HOME/bin
% chmod 6751 oracle

For executables: sqlplus

% cd $ORACLE_HOME/sqlplus/lib
% make -f ins_sqlplus.mk install

For executables: isqlplus (Oracle9i and higher versions)

% cd $ORACLE_HOME/sqlplus/lib
% make -f ins_sqlplus.mk install_isqlplus

For executables: dbsnmp, oemevent, oratclsh

% cd $ORACLE_HOME/network/lib
% make -f ins_oemagent.mk install

NOTE: After relinking the dbsnmp executable, it is necessary to run
the following commands as root (so that the ownership/permissions on
the executable are correct):

# cd $ORACLE_HOME/bin
# chown root dbsnmp
# chmod 6750 dbsnmp

@ References: Note:233559.1 and Bug:2858326

For executables: names, namesctl

% cd $ORACLE_HOME/network/lib
% make -f ins_names.mk install

For executables: osslogin, trcasst, trcroute, onrsd, tnsping

% cd $ORACLE_HOME/network/lib
% make -f ins_net_client.mk install

For executables: tnslsnr, lsnrctl

% cd $ORACLE_HOME/network/lib
% make -f ins_net_server.mk install

For executables related to ldap (for example Oracle Internet Directory):

% cd $ORACLE_HOME/ldap/lib
% make -f ins_ldap.mk install

How to Tell if Relinking Was Successful:
===============================================================================
If relinking was successful, the make command will eventually return to the OS
prompt without an error. There will NOT be a 'Relinking Successful' type
message.


If You Receive an Error Message During Relinking:
===============================================================================
Confirm that the message you received is an actual fatal error and not a
warning. Relinking errors usually terminate the relinking process and contain
verbiage similar to the following:
'Fatal error', 'Ld: fatal', 'Exit Code 1'
While warnings will look similar to: 'ld: warning: option -YP appears more than
once, first setting taken' and can most often be ignored.

If you receive an error that terminates the relinking process, your first step
should be to extract the relevant information about the error from the make
output:

This can be broken down into three basic steps:
1. Identify the OS utility that is returning the error.
'ld', 'make', 'cc', 'mv', 'cp', 'ar' are common sources.
2. Identify the type of error:
'Permission Denied', 'Undefined Symbol', 'File Not Found' are common types.
3. Identify the files or symbols involved.

Using the information from above as keywords, search Oracle's Metalink
repository ( http://metaLink.oracle.com ) for previous occurrences of the same
error. If no previous occurrences are found or a solution is not provided,
generate an iTAR that includes the complete error text.

Help setting environment variables.
==============================================================================
See Note:131207.1 How to Set UNIX Environment Variables
for help with setting UNIX environment variables.


Relinking with Orainst:
===============================================================================
For Oracle7 & Oracle8 only, the following document illustrates how to relink
with the 'orainst' utility:
Note:1032747.6 HOW TO RELINK ORACLE USING THE 7.3.X INSTALLER
While 'orainst' will run the same commands as [Step 4], performing [Step 4]
manually from a UNIX shell is the preferred approach.

References:
How to Relink Oracle Database Software on UNIX [ID 131321.1]

NOTE:284785.1 - How to Check Whether Oracle Binary/Instance is RAC Enabled and Relink Oracle Binary in RAC
Get Oracle Certifications for all Exams
Free Online Exams.com

When do we need to relink Oracle Binaries?

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: When do we need to relink Oracle Binaries?



Solution:



Oracle binaries should be relinked after the following:
- An OS upgrade has occurred.
- A change has been made to the OS system libraries. This can occur during the application of an OS patch.
- A new install failed during the relinking phase.
- Individual Oracle executables core dump during initial startup.
- An individual Oracle patch has been applied (however, explicit relink instructions are usually either included in the README or integrated into the patch install script)

References:


note 131321.1 How to Relink Oracle Database Software on UNIX
Get Oracle Certifications for all Exams
Free Online Exams.com

How to retrieve the Forms Client IP address ?

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: How to retrieve the Forms Client IP address ?

Symptoms:

I create trigger to Audit table X, i use this command to get ip_address
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') from dual;
it returns application server ip, but i need the client machine ip


Solution:



In order to find the IP address of the "Client PC" (The PC where the Forma applet is running), it is necessary to execute some java code in the JVM executing the Forms Applet :

- I have first displayed the client_info for the current session
- I have added the client IP address with DBMS_APPLICATION_INFO.SET_CLIENT_INFO('IP=10.166.244.173');
- finaly, I have displayed the client_info for the current session


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

SQL> select client_info from v$session where audsid=sys_context('userenv','SESSIONID');

CLIENT_INFO
----------------------------------------------------------------


SQL> exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO('IP=10.166.244.173');

PL/SQL procedure successfully completed.

SQL> select client_info from v$session where audsid=sys_context('userenv','SESSIONID');

CLIENT_INFO
----------------------------------------------------------------
IP=10.166.244.173

SQL>

References:


It is possible to create a java Bean :
Note 853971.1 How to Get the Physical IP Address of the Client Machine?

or use Webutil :
Note 403192.1 How To Get Client IP Address For a Locking User

For more details about WebUtil :
http://www.oracle.com/technology/products/forms/htdocs/webutil/webutil.htm

The WebUtil demo : http://www.oracle.com/technology/products/forms/htdocs/webutil/Webutil_demo.zip
implements an example Showing the Client IP Address.
Get Oracle Certifications for all Exams
Free Online Exams.com

Adpatch Fails With ORA-12505 Error In RAC Environment On R12

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: Adpatch Fails With ORA-12505 Error In RAC Environment On R12


Symptoms:
Migrating to RAC

When applying a patch workers connecting using thin JDBC - like for loadjava and akload jobs - fail with error

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor


Solution:


This error occurs because the JDBC connect string used by adpatch does not point to a SID but to a servicename, and adpatch cannot connect with it in all cases.
Solution
Workaround:

1.
Create a custom TNS alias in the $TNS_ADMIN/__ifile.ora :
(replace host.domain, port-nr and instance1 with the actual values)
PATCHSID=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=host.domain)(PORT=port-nr))
(CONNECT_DATA=(SID=instance1))
)

2.
Before each adpatch, and after having run the Applications environment file, set the TWO_TASK environment variable to point to the alias created in step 1:
export TWO_TASK=PATCHSID


Fix:
________________________________________
Please note the following important information when using multiple instance simultaneously for patching:

In this release adpatch has not yet been optimized for this, and using more instances for patching might show it:
• Does not scale well
• Can lead to contention during dictionary updates as well as transaction data updates
• Can even cause performance degradation

Due to the above mentioned concerns, it is not recommended to use multiple RAC instances for patching. In case this use introduces issues it might be required to revert back to using a single instance when patching. This can be achieved by using the above workaround and inserting the following command in file $APPL_TOP/custom$CONTEXT_NAME.env :

export AD_APPS_JDBC_URL=
"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=.)(PORT=)))(CONNECT_DATA=(SID=)))"

Note: Having too many instances specified in AD_APPS_JDBC_URL might for some adpatch worker jobs lead to error "string beginning "(DESCRIPTI..." is too long. maximum size is 239 characters.". This is because of a limitation in SQL*Plus where the connect string length can not exceed 239 characters. In that case the length of AD_APPS_JDBC_URL needs to be reduced by setting it to less - maybe even one - instances for patching to work properly.
________________________________________

Apply the following 2 patches:

Patch 7491048 introduces a new environment variable named "AD_APPS_JDBC_URL" to be used by the AD utilities to make them work in a RAC environment.

Patch 6810458 "AD JAVA UTILITIES SHOULD USE S_APPS_JDBC_CONNECT DESCRIPTOR TO CONNECT TO D/B" provides the fix in adpatch


References:

Adpatch Fails With ORA-12505 Error In RAC Environment On R12 [ID 742371.1]

BUG:6768619 - WRONG CONNECT STRING GENERATED BY OCM
PATCH:6810458 - AD JAVA UTILITIES SHOULD USE S_APPS_JDBC_CONNECT DESCRIPTOR TO CONNECT TO D/B
PATCH:7491048 - BACKWORDPORT OF BUG:6768619:WRONG CONNECT STRING GENERATED BY OCM
Get Oracle Certifications for all Exams
Free Online Exams.com

Parameters that affect the session timeout functionality of forms.

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com


Problem:     parameters that affect the session timeout functionality of forms


Solution:

Basically there are three parameters that affect the session timeout
functionality of forms.

1.FORMS60_TIMEOUT
2.heartbeat
3.session.timeout (zone.properties)

For example
Say you want the timeout to happen after 3 hours.

In this case you should set the FORMS60_TIMEOUT to 3 hours. i.e., 180 minutes and heartbeat to more than 3 hours, 185 minutes.

In this case the heartbeat will occur after the duration of the timeout causing a Form di
sconnect to occur.

If you never wish for the timeout to occur, then you should accomplish the
following:
Set the FORMS60_TIMEOUT to 180 minutes and heartbeat to 60 minutes.
In this case heartbeat will send the request to the 60 minutes to tell that the
client
session is alive and the FORMS60_TIMEOUT will reset to 180 minutes after the
heartbeat ‘ping’ is received. In this way the timeout end time will never be reached unless a
client session abnormally aborts.

Note that you can also specify a timeout setting for idle connections in the
zone.properties file.

Get Oracle Certifications for all Exams
Free Online Exams.com

Unable to generate a document number for a manual receipt

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com


Problem:     Unable to generate a document number for a manual receipt

Solution:

1. Upload detailed screnshots with the receipt class and method setup, document sequence, category and assignment.

There is a common problem that when you RENAME a receipt method, the document sequence category is not updated.

When you create a new receipt class and method, Receivables automatically insert a document category - which you can find in system administrator - application - document - category.

The receipt method NAME = document category CODE

But this category CODE becomes READONLY - as it works like a primary key and gets referred by other tables.
On the other hand, in Receivables you can rename the receipt method NAME - because in Receivables the receipt method ID is used in forms.
Due to the fact that the category code cannot be updated, there is no available internal API function to do that so the AR development can't propagate the change.

Get Oracle Certifications for all Exams
Free Online Exams.com

How to get the current patchset level for PSB

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com


Problem:     How to get the current patchset level for PSB

Solution:

select substr(aa.application_short_name,1,20) "Product", a.patch_level
"Patch Level" from fnd_product_installations a, fnd_application aa
where a.application_id = aa.application_id
and aa.application_short_name like '%PSB%';

select substr(aa.application_short_name,1,20) "Product", a.patch_level
"Patch Level" from fnd_product_installations a, fnd_application aa
where a.application_id = aa.application_id
and aa.application_short_name like '%PSA%';





Get Oracle Certifications for all Exams
Free Online Exams.com

Create Budget Revisions - Unable to Open form ORA-01403: no data found, FRM-40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-06502.

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com


Problem:    Create Budget Revisions - Unable to Open form  ORA-01403: no data found, FRM-40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-06502.


On 12.0.6 in Production:
When trying to create a budget revision,
the following error occurs:

ERROR
-----------------------
ORA-01403 : no data found.
FRM-40735 : when button pressed trigger raised unhandled exception ORA-06502


Symptoms:
The issue can be reproduced at will with the following steps:
1. PSB responsibility--Budget Revisions--Define.
2. Query the existing budget revisions--Get the error.



Solution:

1. Please download and apply Patch 8635281:R12.PSB.A on a test instance on which the issue can be reproduced.
2. This patch provides the latest roll up patch for PSB.
3. After patch application, bounce the apache and applications and retest the issue.
There is a number of issues fixed on the latest PSB roll up patch provided by Patch 8635281.
Users need to apply the latest roll up patch to get the latest form version. Applying the latest roll up patch for PSB resolved the issue for user.

This is also mentioned in the following bug for the roll up patch:
Bug 8635281 PUBLIC SECTOR BUDGETING ROLLUP PATCH - JUL 2009.

References:

Bug 8635281 PUBLIC SECTOR BUDGETING ROLLUP PATCH - JUL 2009.

Get Oracle Certifications for all Exams
Free Online Exams.com

Worksheet operations - Freeze / unfreeze options

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com


Problem:     Worksheet operations - Freeze / unfreeze options

Symptoms:
We are using the standard menu ps budget user for users and ps budget super user for super users.

In the ps budget user menu , the freeze and unfreeze worksheet options have been enabled. Eventhough enabled the users are unable to perform the operations. The options are frozen and not available for selection

Log into PS Budget user responsibility. Select any worksheet and try to select freeze / unfreeze option



Solution:


When a parent budget group moves a worksheet to the next budget stage, both the parent and child worksheets are frozen. The parent budget group must unfreeze its own worksheet before making any modifications to the worksheet. The parent budget group can unfreeze the child worksheets by redistributing the worksheet.

The current funcitionality we are enconutering is contrary to what documentation says. When a worksheet is moved to next stage , only the next level in the hierarchy can unfreeze.




Get Oracle Certifications for all Exams
Free Online Exams.com

ORA-04063: package body "APPS.PSB_GL_INTERFACE_PVT" has errors

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com


Problem:     ORA-04063: package body "APPS.PSB_GL_INTERFACE_PVT" has errors
On 12.0.6 in Production:
When attempting to Approve the Budget revisions workflow then system showing
the following error occurs:

ERROR
-----------------------
To SYSADMIN
Sent 04-Nov-2009 09:14:55
ID 18070


An Error occurred in the following Workflow.

Item Type = PSBBR
Item Key = 235
User Key =1001

Error Name = -4063
Error Message = ORA-04063: package body "APPS.PSB_GL_INTERFACE_PVT" has errors
ORA-06508: PL/SQL: could not find program unit being called: "APPS.PSB_GL_INTERFACE_PVT"
Error Stack =
PSBBR.Post_Revisions_To_GL()
Wf_Engine_Util.Function_Call(PSB_SUBMIT_REVISION_PVT.POST_REVISIONS_TO_GL, PSBBR, 235, 210932, RUN)

Activity Id = 210932
Activity Label = SUBMIT_REVISION:POST_REVISIONS_TO_GL-1
Result Code = #EXCEPTION
Notification Id =
Assigned User =

Symptoms:
The issue can be reproduced at will with the following steps:
1. Create a budget revision and approve the budget revision



Solution:

To implement the solution, please execute the following steps:
1) Please download and review the readme for Patch.7639620.
2) Please apply Patch.7639620 in a test environment.
3) Please confirm the following file versions:
PSBVOGLB.pls 120.24.12000000.13
psbotgl.odf ----------120.0.12000000.5

4) Please retest the issue.

Get Oracle Certifications for all Exams
Free Online Exams.com

Is it possible to personalize by changing the mandatory field 'Title' to a different name on negotiation creation page?

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: Is it possible to personalize by changing the mandatory field 'Title' to a different name on negotiation creation page? Symptoms: I want change the title field name but the personalization not working with this field Solution: There is no standard way to do this. Only option would be to use customizations. You may engage Oracle consulting services for this. You may only personalize fields that are not mandatory. Title is a mandatory field and as such cannot be personalized.
Get Oracle Certifications for all Exams
Free Online Exams.com