RMAN checksyntax worth using it

Yesterday I have started RMAN restore and recovery session of my 70 GB Oracle Apps 12.1.3 RAC database in the run block and went to sleep.

When I saw it in the morning, the script got terminated because of some syntax error in the run block. So the whole night has been wasted because of a simple syntax error.

I was checking for any tools in RMAN which can do a syntax check and fortunately I was able to locate the "checksyntax" command which is available from 10g onwards.

One of the powerful commands in RMAN which can be used to check the syntax of big rman scripts which runs for a long time, before start the script we can check the syntax of the script and run it without worrying of the termination of scripts with syntax errors.

Eg:

$ rman checksyntax

Recovery Manager: Release 10.2.0.5.0 - Production on Thu Dec 23 15:21:32 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN>

RMAN> recover data;

RMAN-00571: ===========================================================

RMAN-00569: =======ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00558: error encountered while parsing input commands

RMAN-01009: syntax error: found "identifier": expecting one of: "copy, clone, database, datafile, datafilecopy, device, standby, tablespace"

RMAN-01008: the bad identifier was: data

RMAN-01007: at line 1 column 9 file: standard input

RMAN-00571: ===========================================================

RMAN-00569: ============ ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00558: error encountered while parsing input commands

RMAN-01009: syntax error: found ";": expecting one of: "allocate, alter, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, cross check, configure, duplicate, debug, delete, drop, exit, endinline, flashback, ho st, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, transport, upgrade , unregister, validate"

RMAN-01007: at line 1 column 13 file: standard input

RMAN>

RMAN> recover database

2> ;

The command has no syntax errors

Happy New Year to All

Wish You All…Great, Prosperous, Blissful, Healthy, Bright, Delightful, Mind Blowing, sweaty & Energetic, Terrific & Extremely ...happy, very HAPPY NEW YEAR 2011.

We are coming again and expecting the same support which was given earlier.

Spurious entries are seen in the concurrent manager tables

.mgr log shows the following error - CONC-SM TNS FAIL, yet all concurrent requests complete successfully without any error.Ping to FNDSM_node_sid entry is successful
No other problems are being experienced
Cause
The cause of this problem is undetermined.
Solution
To implement the solution, please execute the following steps:1. Run the CMCLEAN.sql script to safely clean up the concurrent manager tables.
N.B. The script can be obtained from Metalink Note 134007.1 - CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables

Oracle Workflow Release 12 Diagnostics

Ganeric Workflow Data Collection
Run the script from Note 275379.1 to determine your current R12/ATG version.
Spool, run and upload the output of $FND_TOP/sql/wfver.sql from your Concurrent Manager Server that is running the Workflow Services.
Workflow Mailer
The Workflow Mailer is a key component of E-Business (EBS). The ability to send emails of many types to customers notifying them of key pieces of information is highly appreciated by all stakeholders that interact with EBS. The Workflow Mailer uses SMTP, IMAP and HTTP protocols for processing notifications by email. Run the Oracle Workflow ATG Support: R12 Java Mailer Setup Diagnostic Test from Note 748421.1 to do an initial data collection of your Workflow Mailer configuration.
Workflow has been enhanced to provide more diagnostics for EBS Administrators to use to diagnose notification mailer failures as it uses the protocols. The Mailer.class file is provided to collect diagnostic information when the Workflow Mailer uses these protocols.
Command Line Diagnostics:
Testing IMAP Connectivity
source APPS_.env
$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap \ -Ddbcfile=.dbc \ -Dserver= \ -Dconnect_timeout=120 -Ddebug=Y \ -Daccount= -Dpassword= \ -DdebugMailSession=Y \ oracle.apps.fnd.wf.mailer.Mailer
Example:
Testing default IMAP Port 143:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap \ -Ddbcfile=$FND_SECURE/VIS.dbc \ -Dserver=gggrant2.us.oracle.com \ -Dconnect_timeout=120 -Ddebug=Y \ -Daccount=orlncatst02 -Dpassword=orlncatst02 \ -DdebugMailSession=Y \ oracle.apps.fnd.wf.mailer.Mailer

Testing default IMAPSSL Port 993:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap \ -Ddbcfile=$FND_SECURE/VIS.dbc \-Dport=993 -Dssl=Y \ -Dtruststore=/home/applmgr/.keystore \ -Dserver=gggrant2.us.oracle.com \ -Daccount=orlncatst02 -Dpassword=orlncatst02 \ -Dconnect_timeout=120 -Ddebug=Y \ -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer

NOTE: Capture the output echo'd to the screen so you can upload it and upload the default "test.log" file the tests will create.
Testing SMTP Connectivity
source APPS_.env
$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp \ -Ddbcfile=.dbc \ -Dserver= \ -Dconnect_timeout=120 -Ddebug=Y \ -Daccount= \ -Dpassword= \ -DdebugMailSession=Y \ oracle.apps.fnd.wf.mailer.Mailer
Example:
Testing default SMTP Port 25:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp \ -Ddbcfile=$FND_SECURE/VIS.dbc \ -Dserver=rgmamersmtp.oraclecorp.com \ -Dconnect_timeout=120 -Ddebug=Y \ -Daccount=NoSmtpUser -Dpassword=NoSmtpPasswd \ -DdebugMailSession=Y \ oracle.apps.fnd.wf.mailer.Mailer

Testing SMTPSSL Port 465:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp \ -Ddbcfile=$FND_SECURE/aoldev-pc_vis.dbc \ -Dport=465 -Dssl=Y \ -Dtruststore=/home/applmgr/.keystore \ -Dserver=aoldev-pc.us.oracle.com \ -Daccount=NoSmtpUser -Dpassword=NoSmtpPasswd \ -Dconnect_timeout=120 -Ddebug=Y \ -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer
NOTE: Capture the output echo'd to the screen so you can upload it and upload the default "test.log" file the tests will create.
Testing HTTP Connectivity for notifications with framework message attributes
Example:
NON-SSL TEST: Modify in a text editor to make sure the command is listed on one line as it is one long statement. You need to specify the notification_id (-Dnid) of the failing notification, the protocol (-Dhtp=http), and the dbc filename (-Ddbcfile).
==============Begin garymailertest.sh================= source APPS_.env
$AFJVAPRG -classpath /tmp:
${AF_CLASSPATH} -Dnid=841969 -Ddbcfile=$FND_TOP/secure/orlncatst-02_vis.dbc -Dappuser=0 -Dappresp=20420 -Dappid=1 -Durltimeout=120 -Dhtp=http -DAFLOG_FILENAME=mailerTest.log -DAFLOG_LEVEL=STATEMENT -DAFLOG_ENABLED=true oracle.apps.fnd.wf.mailer.Mailer
=============End garymailertest.sh=====================

SSL TEST:
Modify in a text editor to make sure the command is listed on one line as it is one long statement. You need to specify the notification_id (-Dnid) of the failing notification, the protocol (-Dhtp=https), the name of your and the dbc filename (-Ddbcfile).
==============Begin garymailertestSSL.sh===============

source APPS_.env export LD_LIBRARY_PATH=${AF_LD_LIBRARY_PATH} $AFJVAPRG -classpath $AF_CLASSPATH -Dnid=841969 -Ddbcfile=$FND_TOP/secure/aoldev-pc_vis.dbc -Dappuser=0 -Dappresp=20420 -Dappid=1 -Durltimeout=120 -Dhtp=https -DAFLOG_FILENAME=mailerTest.log -DAFLOG_LEVEL=STATEMENT -DAFLOG_ENABLED=true oracle.apps.fnd.wf.mailer.Mailer
============End garymailertestSSL.sh===============

Java Mailer Diagnostics for HTTP Framework Region Screen Output: Mailer : oracle.apps.fnd.cp.gsc.Logger.Logger(String, int) : Logging to System.out until necessary parameters are retrieved for Logger to be properly started. URL {https://aoldev-pc.us.oracle.com:8443/OA_HTML/OA.jsp?page=/oracle/apps/fnd/wf/worklist/webui/NotifMailerPG&WFRegion=NtfDetail&NtfId=841969&dbc=aoldev-pc_vis&OALAF=blaf&OARF=email} Connection to application server was successful and retrieved 3392 characters plus 4 referenced objects. Time taken to establish the connection and obtain the content was 31.627 seconds Content ID File name and Content Type
========== ========================== 24537094 /OA_HTML/cabo/images/errorl.gif image/gif 5612344 /OA_HTML/cabo/images/cache/cmbte-1.gif image/gif 8344960 /OA_HTML/cabo/images/t.gif image/gif 16625677 /OA_HTML/cabo/images/cache/cmbbn-1.gif image/gif

NOTE: Upload the information echoed to your session and the -DAFLOG_FILENAME=mailerTest.log and "test.log" which contains quite a bit of Statement log level data.

Additional Diagnostics:
Increase the Workflow Mailer's Log Level to Statement in order to get additional diagnostic information for the Workflow Mailer whenever you see issues sending (SMTP) notifications by email and processing (IMAP) responses from the dedicated inbox assigned to the Workflow Mailer.

Changing the Workflow Mailer Log Level:
Login to E-Business as a user assigned the System Administrator responsibility and navigate:
System Administrator > Oracle Applications Manager > Workflow > Notification Mailers > Edit > Advanced
Reproduce the issue and review the Workflow Mailer Service log which is now being written to with the Log Level set to Statement. The log can be viewed in multiple locations from within E-Business and from the file system.
E-Business:
Login to E-Business as a user assigned the System Administrator responsibility and navigate: System Administrator > Oracle Applications Manager > Workflow > Notification Mailers > View Log
OR
System Administrator > Concurrent: Manager > Administer
Select Workflow Mailer Service > Processes > Manager Log
File System:
The Workflow Mailer Service (WFMLRSVC) is a java concurrent manager; therefore, the log is stored in the same location as the other concurrent manager logs on the Concurrent Manager Server node assigned to the Workflow Mailer Service.
$APPLCSF/$APPLLOG is default location. FNDCPGSC.txt is the naming convention of the Workflow Service logs. You can find the concurrent_process_id for the currently active Workflow Mailer Service log using the following query:

set linesize 155;
set pagesize 200;
set verify off;
column MANAGER format a15;
column MEANING format a15; SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup WHERE concurrent_queue_name in ('WFMLRSVC', 'WFALSNRSVC') AND fcq.concurrent_queue_id = fcp.concurrent_queue_id AND fcq.application_id = fcp.queue_application_id AND flkup.lookup_code=fcp.process_status_code AND lookup_type ='CP_PROCESS_STATUS_CODE' AND meaning='Active';
Currently the Retention Time is set to 0 on the WF_ERROR queue. This causes all events on the queue to be removed by the Advanced Queue Time Manager (aq_tm_process) as soon as all events on the WF_ERROR queue is processed. Set the Retention Time to 1 day so you can easily determine if your notification issue is due to a workflow error.
declare l_wf_schema varchar2(200); lagent sys.aq$_agent; l_new_queue varchar2(30); begin l_wf_schema := wf_core.translate('WF_SCHEMA'); l_new_queue := l_wf_schema'.WF_ERROR'; lagent := sys.aq$_agent('WF_ERROR',null,0); dbms_aqadm.alter_queue(queue_name=> l_new_queue, retention_time => 60*60*24 /* one day in seconds */);
end;
/
commit;

Run $FND_TOP/sql/wfmlrdbg.sql to collect information about a particular notification based on its notification_id (NID). It will spool a wfmlrdbg.html file that can be reviewed for any issues that is preventing a notification from being sent or its response from being processed.
sqlplus apps/ @$FND_TOP/sql/wfmlrdbg.sql
Run $FND_TOP/sql/wfsmrdbg.sql to collect information about sending Summary notifications based on the user which have his notification_preference set to receive a summary of his open notifications. It will spool a wfsmrdbg.html file that can be reviewed for issues preventing the summary notification from being emailed.
sqlplus apps/ @$FND_TOP/sql/wfsmrdbg.sql
Example:
sqlplus apps/apps @$FND_TOP/sql/wfsmrdbg.sql SYSADMIN
Workflow Agent Listeners

Oracle E-Business has evolved to use the latest components of the Oracle Database. One of these components that are used extensively in R12 is Advanced Queuing (AQ) as a core component of Oracle Workflow's Business Event System (BES). Many events loaded into the BES provide core functionality such as for processing notifications by email, XML Gateway transactions, User Maintenance/Administration, and other product specific business logic. These events are processed through our AQ by seeded listeners or by custom listeners. A listener simply pings the queue it is configured to check periodically to see if there are any events enqueued on it that need to be processed or in the case of the mailer for inbound processing, it will check the IMAP user account for any responses in the inbox and enqueue proper responses on the Notification Inbound queue. An event is only raised if there is a subscription assigned to it and the queue used is based on the agent that is assigned to the subscription. Events can be enqueued on our default WF_DEFERRED AQ if the phase on the subscription is greater than 99 for asynchronous processing with or without an agent assigned to the subscription. If the phase is less than or equal to 99, the event is processed synchronously by the agent to which is is assigned. The exception this rule is for Java subscriptions used for middle tier java processing. When a java subscription is assigned to an event, all deferred subscriptions and java subscription are enqueued on the WF_JAVA_DEFERRED queue for asynchronous processing by the Workflow Java Deferred Agent Listener.
Run $FND_TOP/sql/wfbesdbg.sql to collect information about the processing of an event based on the event_name and event_key used when it is raised. It will spool a wfevtdbg.html output file. You may need to rename the in order to be able to review the output in a browser.
Due to the complexity of the naming convention of event keys you must run this diagnostics in this manner to avoid failures.
sqlplus apps/ @$FND_TOP/sql/wfbesdbg.sql
Enter value for 1: Enter value for 2:
Example:
sqlplus apps/apps @$FND_TOP/sql/wfbesdbg.sql Enter value for 1: oracle.apps.fnd.wf.ds.userRole.updated Enter value for 2: PER:25FND_RESP:216672454484:54394

Workflow Directory Services (WFDS)
The Workflow Directory Services infrastructure is used to store workflow user and roles. This has been enhanced to directly store E-Business users and their responsibility assignments. Each Product group store their own data in WFDS table range partitions that is based on the origination of the data. As the providers of the infrastructure used by the product groups, Workflow Support and Development investigate the following potential failure points when determining if the reported issue can be addressed as a workflow infrastructure issue.
User/responsibility assignments are maintained by the business event system. The events are linked to subscriptions that are either synchronous or asynchronous. Asynchronous subscriptions will be enqueued on either the Workflow Deferred (WF_DEFERRED) or on Workflow Java Deferred (WF_JAVA_DEFERRED) AQ.
Run the Workflow Directory Services (WFDS) Data Collection Script in Note 742062.1 to collect information about an EBS user and their responsibility assignments.
sqlplus apps/
/
$FND_TOP/sql/wfdirchk.sql to obtain a report of potential directory services data problems. Be sure to spool the file to obtain an output for review.
sqlplus apps/
spool wfdirchk.LST
Workflow Purging (WF_PURGE)
WF_PURGE is a workflow server package used to maintain the workflow infrastructure tables containing obsolete runtime data (data generated at the time a workflow process runs) and design time data (workflow design data that is now obsolete due to the workflow design being changed). The Purge Obsolete Workflow Runtime Data concurrent request calls this package to maintain these tables by periodically deleting completed workflow processes from all the tables used to store the meta-data.
Run the WF Purge Data Collection Script in Note 750497.1 to collect information about the size of the workflow runtime tables and the condition of the workflow processes.
sqlplus apps/

How do you manually regenerate a form in Release 12?

1. Check the FORMS_PATH env variable. Assuming these are US forms the FORMS_PATH should include
$AU_TOP/resource and $AU_TOP/forms/US.

2. To compile a form manually from the command line try either of these two command line arguments (replace FNDFBMAS.fmb with the form of your choice.):

frmcmp.sh module=FNDFBMAS.fmb userid=apps/ module_type=form batch=yes compile_all=yes

frmcmp_batch module=FNDFBMAS.fmb userid=apps/ module_type=form batch=yes compile_all=yes

The Function Is Not Available Under The Responsibility

After applying the ATG RUP 7 When attempting to navigate to a function which has been newly added to a menu the following error occurs:

ERROR
The Function Is Not Available Under The Responsibility

STEPS
The issue can be reproduced at will with the following steps:
1. Query menu:
2. Add function: and Save.
3. Verify that the menu compilation request (FNDSCMPI module: Compile Security) completed successfully.
4. Log into e-Business Suite and try to navigate to function: .
5. The error occurs.

This issue is caused by launching the menu item before the Compile Security has completed.

When a function is added to a menu , a concurrent program 'Compile Security' is submitted which loads the data in Fnd_Compiled_Menu_Functions table.

Whenever a function is accessed, security code checks Fnd_Compiled_Menu_Functions table to determine whether that function is accessible under the given security context or not. So if this function is accessed before the 'Compile Security' program is finished, the function cache gets loaded with stale data and the reported error message is thrown.

So after modifying the 2 menu definitions associated with 'System Administrator' and 'Application Developer' make sure that 2 concurrent requests submitted for the above 2 modifications completed normally and then try to access the functions.

Further once this error occurs, it will be cached which means that even after the menu item has been flagged as available, it the error will be returned from the cache.

Solution
-- To implement the solution, please execute the following steps::
1) Manually run the 'Compile Security' concurrent program, set the parameter to YES.
2) Shut down Middle Tier (Apache)
3) Delete the contents of $OA_HTML/_pages directory (11.5.10 only).
4) Restart Middle tier
5) Test the menu item

How to recover from a corruption of an object in system tablespace

Table IDL_UB2$ who's owner is SYS got corrupted. This was noticed while trying to compile a package EAM_PROCESS_WO_PVT.

=================================================
SQL> alter package EAM_PROCESS_WO_PVT compile package;
Warning: Package altered with compilation errors.
SQL> show errors;
No errors.
SQL> alter package EAM_PROCESS_WO_PVT compile body;

Warning: Package Body altered with compilation errors.
SQL> show errors;
Errors for PACKAGE BODY EAM_PROCESS_WO_PVT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLS-00801: internal error [phdite:node kind]
9361/5 PL/SQL: SQL Statement ignored
9362/32 PL/SQL: ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 2, block # 40099)
ORA-01110: data file 2:
'/u01/oracle/PROD/db/apps_st/data/system02.dbf'
9366/46 PL/SQL: Statement ignored
=================================================

As a first step. we found the object that was in question and its type.

=================================================
SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE FROM DBA_EXTENTS
WHERE FILE_ID = 2
AND 40099 BETWEEN BLOCK_ID AND (BLOCK_ID + BLOCKS -1); 2 3
OWNER
------------------------------
SEGMENT_NAME
-------------------------
SEGMENT_TYPE
------------------
SYS
IDL_UB2$
TABLE
=================================================

then performed the following steps:

=================================================
SQL> shutdown immediate
SQL> startup upgrade
SQL> truncate table idl_ub1$;
SQL> truncate table idl_char$;
SQL> truncate table idl_ub2$;
SQL> truncate table idl_sb4$;
SQL> @?/rdbms/admin/utlirp
SQL> shutdown immediate
SQL> startup upgrade
processes to connect to dbs
SQL> @?/javavm/install/rmjvm
SQL> shutdown immediate
SQL> exit <-- important!<<<<<<<<<<<<<<<<SQL> startup upgrade
processes to connect to dbs
SQL> alter system set "_system_trig_enabled"=false scope=memory;
SQL> @?/javavm/install/initjvm.sql
SQL> shutdown immediate
SQL> exit <-- important! <-- important!<<<<<<<<<<<<<<<<SQL> startup upgrade
processes to connect to dbs
SQL> @?/rdbms/admin/utlrp
SQL> shutdown immediate
SQL> startup
=============================================

The corruption was fixed.