DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Thursday, June 27, 2013

11g RMAN PL/SQL package CATALOG.DBMS_RCVCAT version in GIS database is not current.

PL/SQL package RCAT.DBMS_RCVCAT version in RCVCAT database is not current.

when we are trying to connect the Rman catalog database we are getting  the error it is cause because The particular version of the source database catalog is not sync with the current catalog.

The Cause of this Error is due to the recover catalog database or the target database must be upgraded .. or the recover catalog database is changed.

1.[oracle@dbprod02 obk]$ rman
Recovery Manager: Release - Production on Thu Jun 27 07:43:06 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /
connected to target database: GIS (DBID=2213204443)

RMAN> connect catalog catalog/catalog@pascat
connected to recovery catalog database
PL/SQL package CATALOG.DBMS_RCVCAT version in RCVCAT database is not current
PL/SQL package CATALOG.DBMS_RCVMAN version in RCVCAT database is not current

2. For check the issue if we give the Register database or Resync catalog thou will get the below error message

RMAN> register database;

DBGSQL:     RCVCAT> begin dbms_rcvman.dumpPkgState('RCVMAN after sqlerror'); end;
DBGSQL:        sqlcode = 6550
DBGSQL:     RCVCAT> begin dbms_rcvcat.dumpPkgState('RCVCAT after sqlerror');end;
DBGSQL:        sqlcode = 6550
database registered in recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03008: error while performing automatic resync of recovery catalog
RMAN-10015: error compiling PL/SQL program
RMAN-10014: PL/SQL error 0 on line 1655 column 12: Statement ignored
RMAN-10014: PL/SQL error 306 on line 1655 column 12: wrong number or types of arguments in call to 'CHECKTABLESPACE'

RMAN> resync catalog;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 06/27/2013 07:50:06
RMAN-10015: error compiling PL/SQL program
RMAN-10014: PL/SQL error 0 on line 1655 column 12: Statement ignored
RMAN-10014: PL/SQL error 306 on line 1655 column 12: wrong number or types of arguments in call to 'CHECKTABLESPACE'

3 We need to give the upgrade catalog command to sync the new change in the binaries to reflect in the catalog database

RMAN> upgrade catalog;
recovery catalog owner is CATALOG
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;
recovery catalog upgraded to version
DBMS_RCVMAN package upgraded to version
DBMS_RCVCAT package upgraded to version

4. Connect the target database with the rman and catalog now you can issue the command report schema

[oracle@dbprod02 obk]$ rman
Recovery Manager: Release - Production on Thu Jun 27 07:50:17 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /
connected to target database: GIS (DBID=2213204443)

RMAN> connect catalog catalog/catalog@pascat
connected to recovery catalog database

RMAN> report schema;

Monday, June 24, 2013

Script to collect Apache, Jserv, Forms, Jinitiator and Perl version in E-Business suite R12

# +===========================================================================+
# |
# |
# |   This script can be used to collect Apache, Jserv, Forms, Jinitiator and  
# |   Perl version in E-Business suite R12
# |
# |   Before running the script ensure that you have sourced the environment 
# |   by running APPS<SID>_host.env file from $APPL_TOP.  
# |
# +===========================================================================+
echo "Script Started "
## The script shows output on screen and creates file named hostname_date.txt file in current ## directory
## Collect the Apache version
echo "*****Apache Version*****"
$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v
echo " "
## Collect perl version
echo "*****perl version******"
$IAS_ORACLE_HOME/perl/bin/perl -v|grep built
echo " "
## Collect Java version
echo "******Java Version******"
sh -c "`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' $ADMIN_SCRIPTS_HOME/` -version;"
echo " "
## Collect client JRE version
echo "*****Client JRE version*****"
cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35
echo " "
## Collect Forms version
echo "*****Forms Version*****"
$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version
echo " "
## Collect PL/SQL Version
echo "*****PL/SQL Version****"
$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version
echo " "
## Collect Forms communication mode
echo "****Forms Communication Mode****"
cat $FORMS_WEB_CONFIG_FILE|grep serverURL=
echo "If the serverURL parameter has no value then Forms is implemented in socket mode else it is servlet"
echo " "
echo "Script Completed Successfully and it has generated the file  zz`hostname`_`date +%m%d%y.%H%M`.txt file in current directory"
echo "Script completed "
) 2>&1 | tee zz`hostname`_`date +%m%d%y.%H%M`.txt

Friday, June 21, 2013

Oracle 11g Parallel Backup of the Same Datafile

Parallel Backup of the Same Data files in Oracle 11g RMAN
In Oracle Database 11g RMAN, the channels can break the datafiles into chunks known as "sections."

we can specify the size of each section. Here's an example:
RMAN> run {
2>      allocate channel c1 type disk format '/backup1/%U';
3>      allocate channel c2 type disk format '/backup2/%U';
4>      backup
5>      section size 500m
6>      datafile 6;
7> }
This RMAN command allocates two channels and backs up the users' tablespace in parallel on two channels.
Each channel takes a 500MB section of the datafile and backs it up in parallel. This makes backup of large files faster.
When backed up this way, the backups show up as sections as well.
RMAN> list backup of datafile 6;

    List of Backup Pieces for backup set 901 Copy #1
    BP Key  Pc# Status      Piece Name
    -------    ---  -----------      ----------
    2007    1   AVAILABLE   /backup1/9dhk7os1_1_1
    2008    2   AVAILABLE   /backup2/9dhk7os1_1_1
    2009    3   AVAILABLE   /backup1/9dhk7os1_1_3
    2009    3   AVAILABLE   /backup2/9dhk7os1_1_4
Some points to remember about multisection backups include:
•If the section size is larger than the file size, RMAN does not use a multisection backup for the file.
•If the section size is so small that more than 256 sections would be produced, RMAN increases the section size such that 256 sections will be created.
•SECTION SIZE and MAXPIECESIZE cannot be used together.
•A backup set never contains a partial datafile, regardless of whether or not it is a multisection backup.

Check Listening Ports in AIX

To Determine which processes have listening ports on AIX

 The open source "lsof" tool is great for determining what process has a port open.  Unfortunately lsof isn't included with AIX so if you just want to quickly identify which process is using a port and you don't have lsof you can use "netstat -Aan" combined with the "rmsock" command.  

For example, lets say I want to identify which process is listening on port 1334.   I would first run:

# netstat -Aan | grep LISTEN | grep 1334

f100050000b05bb8 tcp4       0      0  *.1334      *.*  LISTEN

The socket 0xf100050000b05808 is being held by proccess 5767378 (writesrv).

You can see that port 1334 is open by the writesrv process with PID 5767378.

You then take the first column (f100050000b05bb8 in this example) and run the following command:

# rmsock f100050000b05bb8 tcpcb

If you want to see all of the TCP listening ports and which processes and PID's are assigned to them, run the following script:

print "Port            PID              Process"
netstat -Aan | grep LISTEN | awk '{print $1 " " $5}' | while read pcb port; do 
        out=`rmsock $pcb tcpcb`
        if echo "$out" | grep "Kernel Extension" > /dev/null; then
                printf "%-15s Kernel Extension\n" "$port"
                pid=`echo "$out" | sed -n 's/.*pro[c]*ess \([0-9][0-9]*\) .*/\1/p'`
                if [ -n "$pid" ]; then
                        proc=`ps -p $pid | tail -n 1 | awk '{print $4}'`
                        printf "%-15s %-16s $proc\n" "$port" $pid
                        echo "Error, Line not recognized \"$out\" for Port $port"


 Here is example output from the script:

*.13            4063356          inetd
*.21            4063356          inetd
*.23            4063356          inetd
*.25            1835206          sendmail
*.37            4063356          inetd
*.24575         15597582         java
*.111           Kernel Extension
*.199           3539070          snmpdv3ne
*.49293         35389582         rwrun
*.512           4063356          inetd
*.513           4063356          inetd
*.514           4063356          inetd
*.25075         15597582         java
*.657           6095060          rmcd 51052606         java
*.25575         15597582         java
*.1334          5570768          writesrv
*.1536          22610158         tnslsnr
*.1641          34472056         tnslsnr
*.2049          Kernel Extension 20316174         java 25559104         java
*.4458          6750376          httpd
*.6000          5373952          X
*.6000          5373952          X
*.6001          6619338          X
*.6001          6619338          X
*.6112          4063356          inetd
*.55731         Kernel Extension 34472056         tnslsnr

Tuesday, June 18, 2013

Oracle 11g Server Result Cache

In Oracle 11g, there is a new SGA component called result cache, which is used cache SQL query and PL/SQL function results.
The database serves the results for the executed SQL queries and PL/SQL functions from the cache instead of re-executing the actual query. Of course, the target is to obtain high response time.
The cached results stored become invalid when data in the dependent database objects is modified.
As clear from its concept, result cache is mostly useful in for frequently executed queries with rare changes on the retrieved data.
Result Cache Restrictions

Following are some restrictions with regards to the SQL result cache:
1.Queries against data dictionary objects and temporary tables are not supported.
3.Queries with bind variables can reuse a cached result only for identical variable values.
4.Results of the queries retrieving non current version of data are not cached in the result cache.

5.Restrictions on PL/SQL Function Result Cache include:
*.The function cannot be defined in a module using invoker’s rights.
*.The function cannot be used in an anonymous block.
*.The function cannot have any OUT or IN OUT parameters.
*.The function cannot have IN parameters that are BLOB, CLOB, NCLOB, REF CURSOR, collections, objects, or records.
*.The function cannot return a BLOB, CLOB, NCLOB, REF CURSOR, OBJECTS, or records. It can return a collection as long as the collection does not contain one of these types.

Configuring Result Cache
You can enable and disable result cache in the database server using the parameter RESULT_CACHE_MAX_SIZE.  This parameter specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache. If the value of this parameter is 0, then the feature is disabled.

Memory allocated for the result cache is taken from the shared pool.
The default value of RESULT_CACHE_MAX_SIZE parameter is derived from values of other parameters and as shown in the following table:
Default Percentage of Shared Pool to Result Cache

Of course, you can increase value of the RESULT_CACHE_MAX_SIZE parameter but in all cases Oracle does not allocate more than 75 percent of shared pool memory to result cache.


Note : RESULT_CACHE_MAX_SIZE cannot be dynamically changed if its value is set to 0 during database startup.

Controlling Result Cache Behavior
The RESULT_CACHE_MODE initialization parameter determines the SQL query result cache mode. The parameter specifies when a ResultCache operator is spliced into a query's execution plan.
The parameter accepts the following values:
MANUAL : The ResultCache operator is added, only if you use the RESULT_CACHE hint in the SQL query.
FORCE : The ResultCache operator is added to the root of all SELECT statements, if that is possible. However, if the statement contains a NO_RESULT_CACHE hint, then the hint takes precedence over the parameter setting.

The parameter can be modified in the system or session level.

Following is an example of using the RESULT_CACHE hint.
SELECT /*+ result_cache */

The parameter RESULT_CACHE_MAX_RESULT specifies the percentage of RESULT_CACHE_MAX_SIZE that any single result can use. Its default value is five.


The parameter RESULT_CACHE_REMOTE_EXPIRATION specifies the number of minutes that a result using a remote object is allowed to remain valid.

Setting this parameter to 0 (the default) implies that results using remote objects should not be cached.
Setting this parameter to a non-zero value can produce stale answers.

PL/SQL Function Result Cache

When PL/SQL function result cache is enabled,
Oracle will check the result cache to see if a previous call to the function exists (using the same parameter passed values) and if so it will return the cached result instead of executing the function.

A PL/SQL function can take advantage of the PL/SQL Function Result Cache by adding the RESULT_CACHE clause to the function definition.
In the optional RELIES_ON clause, specify any tables or views on which the function results depend.

Following is an example:
-- Package specification
-- Function declaration
FUNCTION get_dept_info (dept_id NUMBER) RETURN dept_info_record RESULT_CACHE;
END department_pks;
-- Function definition
FUNCTION get_dept_info (dept_id NUMBER) RETURN dept_info_record

If there is a dependent table in a function and that table was modified, then the result cache will be invalidated (cache miss).
In this case, the function will be re-executed when called. The same will also occur if you re-compile the function.

When a session reads from a PL/SQL function result cache, the function body is not executed. This means, if the function includes any IO or auditing code, this code will not actually be executed.

PL/SQL Cached functions works across sessions. This means, if the function is cached by a session, its result cache will also be used when executing the same function and arguments by other sessions.
If you need to apply a hot patch PL/SQL code in a running system to a PL/SQL module on which a result cached function directly or transitively depends, then the cached results associated with the result cache function are not automatically flushed. In this case, the following steps must be undertaken:

1. Place the result cache in bypass mode, and flush existing result. When bypass mode is turned on, it implies that cached results are no longer used and that no new results are saved in the cache.


2. Apply the PL/SQL code patches.
3. Resume use of the result cache, by turning off the cache bypass mode.

Monitoring Result Cache
If you display the explain plan for any query with a RESULT_CACHE hint,
you will notice the ResultCache operator. You can use the CACHE_ID value provided in the
explain plan to find details about the cached query results using the V$RESULT_CACHE_OBJECTS view, as shown in the following example:

where CACHE_ID='ctpgzz1qb222tfqw61j203h01b';

Following are the possible values for the STATUS column and their descriptions:
NEW : Result is still under construction
PUBLISHED : Result is available for use
BYPASS : Result will be bypassed from use
EXPIRED : Result has exceeded expiration time
INVALID : Result is no longer available for use

V$RESULT_CACHE_STATISTICS view provides information and statistics on cache settings and memory usage.

The NAME column possible values are as follows:Block Size : (Bytes) size of each memory block
Block Count : Maximum number of memory blocks allowed
Block Count : Current Number of memory blocks currently allocated
Result Size : Maximum (Blocks) Maximum number of blocks allowed for a single result
Create Count : Success Number of cache results successfully created
Create Count : Failure Number of cache results that failed to create
Find Count : Number of cached results that were successfully found
Invalidation : Count Total number of invalidations
Delete Count : Invalid Number of invalid cached results deleted
Delete Count : Valid Number of valid cached results deleted

The V$RESULT_CACHE_MEMORY view displays all the memory blocks and their status. Of course, number of rows in this view increases as the result cache is enlarged by its usage.

The V$RESULT_CACHE_DEPENDENCY view displays the depends-on relationship between the cached results objects.
Monitoring and Managing Result Cache with DBMS_RESULT_CACHE

Beside the dictionary views, the package DBMS_RESULT_CACHE can also be used to monitor and manage result cache usage.
Below are some examples of using it:
-- check the status of the Result Cache
-- Note: this is the reliable method to know whether result cache is enabled or not
-- display report on result cache memory
SQL>set serveroutput on
-- turn bypass mode on and off
-- to flush the result cache

Monday, June 17, 2013

Oracle 11g ASM Intelligent Data Placement

Intelligent Data Placement enables you to specify disk regions on Oracle ASM disks for best performance. Using the disk region settings, you can ensure that frequently accessed data is placed on the outermost (hot) tracks which have greater speed and higher bandwidth.

In addition, files with similar access patterns are located physically close, reducing latency. Intelligent Data Placement also enables the placement of primary and mirror extents into different hot or cold regions.

Intelligent Data Placement settings can be specified for a file or in disk group templates. The disk region settings can be modified after the disk group has been created. The disk region setting can improve I/O performance by placing more frequently accessed data in regions furthest from the spindle, while reducing your cost by increasing the usable space on a disk.

Intelligent Data Placement works best for the following:
  • Databases with data files that are accessed at different rates. A database that accesses all data files in the same way is unlikely to benefit from Intelligent Data Placement.

  • Disk groups that are more than 25% full. If the disk group is only 25% full, the management overhead is unlikely to be worth any benefit.
  • Disks that have better performance at the beginning of the media relative to the end. Because Intelligent Data Placement leverages the geometry of the disk, it is well suited to JBOD (just a bunch of disks). In contrast, a storage array with LUNs composed of concatenated volumes masks the geometry from Oracle ASM.
The COMPATIBLE.ASM and COMPATIBLE.RDBMS disk group attributes must be set to 11.2 or higher to use Intelligent Data Placement.

Intelligent Data Placement can be managed with the ALTER DISKGROUP ADD or MODIFY TEMPLATE SQL statements and the ALTER DISKGROUP MODIFY FILE SQL statement.
  • The ALTER DISKGROUP TEMPLATE SQL statement includes a disk region clause for setting hot/mirrorhot or cold/mirrorcold regions in a template:
    ALTER DISKGROUP data ADD TEMPLATE datafile_hot
      ATTRIBUTE ( 
  • The ALTER DISKGROUP ... MODIFY FILE SQL statement that sets disk region attributes for hot/mirrorhot or cold/mirrorcold regions:

  • ALTER DISKGROUP data MODIFY FILE '+data/orcl/datafile/users.259.679156903'
      ATTRIBUTE ( 
When you modify the disk region settings for a file, this action applies to new extensions of the file, but existing file contents are not affected until a rebalance operation.
To apply the new Intelligent Data Placement policy for existing file contents, you can manually initiate a rebalance. A rebalance operation uses the last specified policy for the file extents.

Information about Intelligent Data Placement is displayed in the columns of the V$ASM_DISK, V$ASM_DISK_IOSTAT, V$ASM_FILE, and V$ASM_TEMPLATE views.

Example 6-9 shows queries for Intelligent Data Placement information in the PRIMARY_REGION and MIRROR_REGION columns of the V$ASM_FILE view.

Example 6-9 Viewing Intelligent Data Placement information with V$ASM_FILE
SQL> SELECT AS diskgroup, f.file_number, f.primary_region, f.mirror_region, f.hot_reads,
       f.hot_writes,  f.cold_reads, f.cold_writes
       WHERE dg.group_number = f.group_number and = 'DATA';
------------------------------ ----------- ---- ---- ---------- ---------- ---------- -----------
DATA                                   257 COLD COLD          0          0     119770      886575
DATA                                   258 COLD COLD          0          0       1396      222282
DATA                                   259 COLD COLD          0          0       2056         199
DATA                                   260 COLD COLD          0          0      42377     1331016
DATA                                   261 COLD COLD          0          0    4336300     1331027

Example 6-10 displays Intelligent Data Placement information in the PRIMARY_REGION and MIRROR_REGION columns of the V$ASM_TEMPLATE view.

Example 6-10 Viewing Intelligent Data Placement information with V$ASM_TEMPLATE
SQL> SELECT AS diskgroup,, t.stripe, t.redundancy, t.primary_region, t.mirror_region 
       WHERE dg.group_number = t.group_number and = 'DATA' ORDER BY;

DISKGROUP                      NAME                           STRIPE REDUND PRIM MIRR
------------------------------ ------------------------------ ------ ------ ---- ----
DATA                           ARCHIVELOG                     COARSE MIRROR COLD COLD
DATA                           ASMPARAMETERFILE               COARSE MIRROR COLD COLD
DATA                           AUTOBACKUP                     COARSE MIRROR COLD COLD
DATA                           BACKUPSET                      COARSE MIRROR COLD COLD
DATA                           CHANGETRACKING                 COARSE MIRROR COLD COLD
DATA                           CONTROLFILE                    FINE   HIGH   COLD COLD
DATA                           DATAFILE                       COARSE MIRROR COLD COLD
DATA                           DATAGUARDCONFIG                COARSE MIRROR COLD COLD
DATA                           DUMPSET                        COARSE MIRROR COLD COLD
DATA                           FLASHBACK                      COARSE MIRROR COLD COLD
DATA                           FLASHFILE                      COARSE MIRROR COLD COLD
DATA                           OCRFILE                        COARSE MIRROR COLD COLD
DATA                           ONLINELOG                      COARSE MIRROR COLD COLD
DATA                           PARAMETERFILE                  COARSE MIRROR COLD COLD
DATA                           TEMPFILE                       COARSE MIRROR COLD COLD
DATA                           XTRANSPORT                     COARSE MIRROR COLD COLD
15 rows selected.

Friday, June 14, 2013

Script to check AD Patch timing

set lines 130
alter session set nls_date_format='DD-MON-YY HH24:MI:SS';
select APPS_SYSTEM_NAME,DRIVER_FILE_NAME,start_date,end_date
where a.PATCH_DRIVER_ID = b.PATCH_DRIVER_ID and DRIVER_FILE_NAME like '%&Patch_number%';

Wednesday, June 12, 2013

You must allow access to at least one attribute group


When are working in the inventory module and when we tried to do some transactions, We faced the following issue.

Error :

You must allow access to at least one attribute group

We have checked the responsibility Inventory Vision Operations (USA) and it doesn't have any exclusions in System Administrator.

We have also checked the seeded Inventory Responsibility and when we opened Master Items or Organization Items screen the same problem You must allow access to at least one attribute group and after clicking on OK button the Item screen opens up and only Process Manufacturing tab is active and all the other tabs are greyed out.

Also we get the following error when we ignored the above and started working further.

The components for the Inventory Items screen are not licensed in License Manager in R12.1.1.

Need to license the Products through System Administration or System Administrator Responsibility.

System Administrator > Oracle Applications Manager > Lincese Manager

Click on the the Products under License section and license the Respective Products. For example (INV etc)

After performing the above steps the Items Screen attributes are active.