Oracle ERP DBA

Alternatives to Flash version of My Oracle Support

2011-02-23T12:33:00.008+05:30

I am sure you all know this but this is for those who do not! :)

For those who think that the flash version of MOS is slow (including yours truly), Oracle provides the html version of MOS.

For those who were accustomed to using the Metalink (when MOS was Metalink and George W Bush was the president of the US of A - not the father, I'm talking about the son) patch search and download page, do not fret. You can use the classic patches window to get the old page (and a lot of memories) back!

To open any MOS note id, you can use this link. Only substitute Note ID 221211.1 with the note id that you want to open.

WGET shell Script for downloading patches

2011-02-23T12:14:00.008+05:30

What I have to offer this time is a script using which you can download patches using WGET from Meta.....oops, from My Oracle Support, I should say!

This script needs (or accepts) the following parameters:

1. Me...oops...MOS Username
2. MOS (I got it right this time!) password
3. Patch file name
4. ARU Number

You can get 3 and 4 in the following way:

Using MOS:

Query for the particular patch, click on the download link, click on WGET Options on the left side bottom corner and then copy the WGET script to clipboard. That script will have both the patch name and the ARU number.

Using HTML (supporthtml.oracle.com):

Query for the particular patch, click on the patch, then RIGHT CLICK on the patch zip file link and click on copy shortcut in IE (or copy link location in Mozilla Firefox). This link will have both the patch file name and the ARU number.

Using the classic patches window:

Query for the particular patch, then RIGHT CLICK on the DOWNLOAD button and click on copy shortcut in IE (or copy link location in Mozilla Firefox). This link will have both the patch file name and the ARU number.

I do understand that this is a roundabout process but I believe it is better than downloading the file to your desktop and then uploading to the server.

I am also working on a script to retrieve the aru number and the patch file name given the patch number. But I have had no success so far. If anyone has that script or any better script to download patches, please do not hesitate to share it.

WGET SCRIPT:

#!/bin/sh



# Start of user configurable variables

#



# SSO username and password

printf "\n\nEnter the SSO USERNAME for Metalink:\t"

read SSO_USERNAME

printf "\n\n\n\nEnter the SSO PASSWORD for Metalink:\t"

##SSO_PASSWORD="password"

stty -echo

read SSO_PASSWORD

stty echo



# E-Delivery token

EPD_TOKEN=







# Path to wget command

WGET=/usr/bin/wget



# Location of cookie file

COOKIE_FILE=/tmp/$$.cookies



# Log directory and file

LOGDIR=.

LOGFILE=$LOGDIR/wgetlog-`date +%d-%b-%y-%H-%M`.log



# Output directory and file

OUTPUT_DIR=.



## Added by Praveen B K, for customising the script.

printf "\n\nEnter the PATCH FILE NAME:\t"

read PATCH_FILE_NAME



printf "\n\nEnter the aru number:\t"

read ARU



#

# End of user configurable variable

#



if [ "$SSO_USERNAME " = " " ]

then

 printf "\n\nSSO USERNAME cannot be null. Exiting............\n\n\n"

 exit 1

fi



if [ "$SSO_PASSWORD " = " " ]

then

 printf "\n\nSSO PASSWORD cannot be null. Exiting............\n\n\n"

 exit 1

fi



if [ "$PATCH_FILE_NAME " = " " ]

then

 printf "\n\nPATCH FILE NAME cannot be blank. Exiting............\n\n\n"

 exit 1

fi



if [ "$ARU " = " " ]

then

 printf "\n\naru number cannot be blank. Exiting............\n\n\n"

 exit 1

fi



# Contact updates site so that we can get SSO Params for logging in

SSO_RESPONSE=$($WGET https://updates.oracle.com/Orion/Services/download 2>&1grep Location)



# Extract request parameters for SSO

SSO_TOKEN=`echo $SSO_RESPONSE cut -d '=' -f 2cut -d ' ' -f 1`

SSO_SERVER=`echo $SSO_RESPONSE cut -d ' ' -f 2cut -d 'p' -f 1,2`

SSO_AUTH_URL=sso/auth

AUTH_DATA="ssousername=$SSO_USERNAME&password=$SSO_PASSWORD&site2pstoretoken=$SSO_TOKEN"



# The following command to authenticate uses HTTPS. This will work only if the wget in the environment

# where this script will be executed was compiled with OpenSSL. Remove the --secure-protocol option

# if wget was not compiled with OpenSSL

# Depending on the preference, the other options are --secure-protocol= autoSSLv2SSLv3TLSv1

$WGET --secure-protocol=auto --post-data $AUTH_DATA --save-cookies=$COOKIE_FILE --keep-session-cookies $SSO_SERVER$SSO_AUTH_URL -O sso.out >> $LOGFILE 2>&1



rm -f sso.out



$WGET  --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/${PATCH_FILE_NAME}?aru=${ARU}&patch_file=${PATCH_FILE_NAME}" -O $OUTPUT_DIR/${PATCH_FILE_NAME}   >> $LOGFILE 2>&1





# Cleanup

rm -f $COOKIE_FILE

Rebuild reporting database

2010-09-06T15:35:00.002+05:30

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

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

Solution

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

The rest of the steps hold good!

Creating a physical standby database

2010-09-06T12:33:00.009+05:30

Requirement

Create a standby database for the primary production database.

Pre-requisites

1. The primary database is in archive log mode.

2. Primary and standby database names:

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

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

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

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

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

*.log_archive_dest_state_2='ENABLE'

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

*.standby_file_management='auto'

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

*.fal_client='PRIM'

*.fal_server='STBY'

*.db_name='PRIM'

*.db_unique_name='PRIM'

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

listener.ora on primary

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

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

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

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

tnsnames.ora on primary

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

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

Backup tnsnames.ora on primary (for switchover)

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

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

5. Create the initialization file for the standby database.

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

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

*.db_name='PRIM'

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

*.db_recovery_file_dest_size=42949672960

*.db_unique_name='STBY'

*.fal_client='STBY'

*.fal_server='PRIM'

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

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

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

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'

*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

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

*.standby_file_management='auto'

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

listener.ora on standby

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

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

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

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

tnsnames.ora for standby

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

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

Backup tnsnames.ora on standby (for switchover)

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

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

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

Steps - using manual hotbackup

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

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

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

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

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

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

Eg:

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

The created file must meet two conditions:

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

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

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

6. Start the standby listener.

7. Mount the standby database.

SQL> startup nomount

SQL> alter database mount standby database;

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

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

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

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

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

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

SQL> alter database recover continue default;

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

SQL> alter database recover cancel;

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

SQL> alter database open read only;

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

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

12. Start the MRP (managed recovery process):

SQL> alter database recover managed standby database disconnect;

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

Voila! Your dataguard is ready to be used.

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

2010-05-18T14:27:00.007+05:30

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

Issue

Unable to submit concurrent requests. Receiving the below error:

APP-FND-01564: ORACLE error 3113 in FDFDDS_RAISE_DFF_COMPILED

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

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

Cause

Database was started with incorrect ORA_NLS10 parameter.

Analysis

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

Solution

1. Shutdown the database.

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

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

3. Startup the database.

References

1. MOS Note ID: 553264.1

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

2010-03-31T11:26:00.014+05:30

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

Issue

Receiving below error while trying to start WF Mailer:

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

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

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

Symptom

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

Cause

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

Solution

1. Shutdown Concurrent Manager

2. Take a backup of FND_Nodes.

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

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

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

SQL> exec fnd_cp_fndsm.register_fndsm_fcq('');

5. Start Concurrent Manager

6. Mailer was working fine after this.

Reference

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

Quirky 11i Apps Patch queires

2010-02-02T12:04:00.008+05:30

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

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

Method 1

Check Patches applied from Oracle Applications Manager (OAM).

a) Connect to OAM:

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

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

Enter Patch ID and press 'Go'

See if Patch is returned.

Method 2

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

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

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

Specify 1 or 2 or 3 for query_depth

1-> Details of patches only

2-> Details of patches and their Bug Fixes only

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

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

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

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

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

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

Enter password:

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

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

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

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

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

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

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

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

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

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

Enter password:

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

This report should take less than a minute.

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

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

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

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

Method 3

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

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

A modified version of the above query is as below:

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

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

Method 4

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

set serveroutput on
DECLARE
  TYPE p_patch_array_type is varray(10) of varchar2(10);
  --
  p_patchlist p_patch_array_type;
  p_appltop_name        varchar2(50);
  p_patch_status        varchar2(15);
  p_appl_top_id         number;
  --
  CURSOR alist IS
    select appl_top_id, name
    from ad_appl_tops;
  --
  procedure println(msg in varchar2)
  is
  begin
    dbms_output.enable;
    dbms_output.put_line(msg);
  end;
  --
BEGIN
  open alist;
  --
  p_patchlist:= p_patch_array_type('3240000','3460000','4204335','4125550','3942483','4733943');
  --
  LOOP
    FETCH alist INTO p_appl_top_id,p_appltop_name;
    EXIT WHEN alist%NOTFOUND;
    --
    IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
    THEN
      println(p_appltop_name  ':');
      for i in 1..p_patchlist.count
      loop
        p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,p_patchlist(i));
        println('..Patch '  p_patchlist(i)  ' was '  p_patch_status);
      end loop;
    END if;
    println('.');
  END LOOP;
  --
  close alist;
END;
/

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

-- Query to find whether a single patch was applied or not.
set serveroutput on;
DECLARE
  p_appltop_name        varchar2(50);
  p_patch_status        varchar2(15);
  p_appl_top_id         number;
  --
  cursor atoplist is
    select appl_top_id, name from ad_appl_tops;
  --
  procedure println(msg in varchar2)
    is
    begin
      dbms_output.enable;
      dbms_output.put_line(msg);
    end;
  --
BEGIN
  open atoplist;
  --
  LOOP
    FETCH atoplist INTO p_appl_top_id,p_appltop_name;
    EXIT WHEN atoplist%NOTFOUND;
    --
    IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
    THEN
      println(p_appltop_name  ':');
      p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,&bug);
      println('..Patch '  &bug  ' was '  p_patch_status);
    END if;
    println('.');
  END LOOP;
  --
  close atoplist;
END;
/

References:

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

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

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

Build a Reporting Database using standby and flashback

2009-11-23T14:57:00.008+05:30

It's been a long time since I posted something. So, here's something worthwhile. Thanks to my colleague Kevin who devised this method!

Requirement

A reporting database to be refreshed daily from the UAT instance. The reporting database should be available during the daytime and the refresh can happen during the night.

Solution

1. Initially, build the reporting database as a standby database.

2. Once the Managed Recovery process (MRP) is initiated and the standby is in sync with the primary database, cancel the MRP.

SQL> alter database recover managed standby database cancel;

3. Enable flashback in the standby database. Note that the DB should be in mount state for this.

SQL> startup mount force;




SQL> alter database flashback on;

4. create guaranteed restore point.

SQL> drop restore point before_open;



SQL> create restore point before_open guarantee flashback database;

5. Activate the standby database.

SQL> alter database activate standby database;

SQL> startup force;

6. In the evening/night, flashback the database to the guaranteed restore point i.e., standby database and resync it with the primary.

SQL> startup mount force;



SQL> flashback database to before_open;



SQL> alter database convert to physical standby;



SQL> startup mount force;



SQL> alter database recover managed standby database disconnect from session;

7. The next day monring, verify that the recovery has indeed completed, by using the following sql:

SQL> set pages 1000



SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;



SQL> select max(sequence#) from v$archived_log; -- execute on both primary and standby.

8. Repeat the Steps 4 & 5 - to open the reporting database to the users.

9. In the evening/night, repeat Step 6. - to resync the reporting database.

References

1. Introduction to Restore points/flashback databases.

2. Introduction to data guard.

3. Business Continuity for Oracle Applications Release 11i, Database Releases 9i and 10g - Note: 216212.1

WGET Syntax for downloading patches from Metalink

2009-10-12T17:51:00.008+05:30

I am sure everyone knows this but just to emphasise the point (and ease the pain somewhat), Classic MetaLink Retires November 6, 2009.

Extract of the text of email from Oracle Support:

During the weekend of November 6 – 8, 2009, My Oracle Support, Oracle's next-generation customer support platform, will be upgraded and Classic MetaLink will be retired.

Moreover (and this is the most painful part especially for Apps DBAs), the ftp to the patch site will also retire on 06-Nov-2009. :(

Below is the extract of the message displayed on updates.oracle.com ftp site:

230-  This service will be retired on November 06, 2009 and "My  Oracle Support"
230-  will be the only interface for downloading the patches and updates. To
230-  search and download patches from "My Oracle Support" users are required
230-  to login to https://metalink.oracle.com and then go to "Patches & Updates"
230-  tab.

Though I do NOT like it, I suppose I have to force myself to use wget to download patches directly on to the server, instead of downloading them to my local machine and then transferring them to the server (a much more painful process).

So, as a heads up, I would like to share with you the syntax for using wget.

Note: The below syntax worked for Solaris 10 and might not work for other flavours of UNIX since the default installation directory may be different.

/usr/sfw/bin/wget --http-user=<Metalink Username> --http-passwd=<Metalink Password> --output-document=<Name of patch>
"<URL>"

1. Get the URL in the following way:

a. Login to metalink and query up the patch.
b. Right click on the download button and click on "Copy Shortcut" (IE).

2. The name of the patch would be a subset of the URL.


eg:

/usr/sfw/bin/wget --http-user=<Metalink Username> --http-passwd=<Metalink Password> --output-document=p8618053_11i_GENERIC.zip  "http://updates.oracle.com/ARULink/Download/process_form/p8618053_11i_GENERIC.zip?file_id=27640717&aru=11477117&userid=ml-693674.993&email=praveen.krishnamurthy@qwest.com&patch_password=&patch_file=p8618053_11i_GENERIC.zip"

Hope this is helpful in the painful transition from the updates.oracle.com ftp site to the Flash Player based new and "improved" Metalink.

Find Request Set Name, Conc Manager given a request ID

2009-10-09T10:28:00.010+05:30

Given a request id, how does one find out the program name and the manager running the request? Most of the Metalink notes and blogs provide the answer to this question but for one aspect. What is that? Read on to find out.

What the SQL queries available on the web and Metalink lack is that they provide the concurrent program name for a single request given the request id. What about for a request set? Do they provide the answer? No, they don't.

I have tried here to cover this aspect too. So, here goes.

Note: This query works for all request ids, not only for request ids for request sets.

select request_id,fcpv.CONCURRENT_PROGRAM_NAME "PROGRAM_NAME",
nvl (fcr.description,fcpv.user_concurrent_program_name) "USER_PROGRAM_NAME",
decode(fcpv.REQUEST_SET_FLAG,'N','SINGLE_REQUEST','Y','REQUEST_SET') "PROGRAM_TYPE",
fcqv.CONCURRENT_QUEUE_NAME, fcqv.USER_CONCURRENT_QUEUE_NAME
from
fnd_concurrent_requests fcr, fnd_concurrent_programs_vl fcpv, fnd_concurrent_processes fcp, fnd_concurrent_queues_vl fcqv
where
fcr.concurrent_program_id = fcpv.concurrent_program_id (+)
and fcr.controlling_manager = fcp.CONCURRENT_PROCESS_ID
and fcp.concurrent_queue_id = fcqv.concurrent_queue_id
and fcr.request_id = &request_id;

Print UNIX pipe character ("|") in Blogger

2009-09-22T18:42:00.005+05:30

In order to get the UNIX pipe character ("|") printed in blogger, do the following:

Always use the "Edit Html" tab for posts containing the pipe character and NOT the Compose tab.

The ASCII value for the pipe character is "& # 124 ;" (I am delibrately using whitespace in betwen the ASCII value. Please remove them before using).

Thanks for Greg Houston's blog and this thread for pointing me in the right direction.

Get R12 Login URL from Command Prompt

2009-09-08T14:25:00.016+05:30

A quick question before getting started with today's subject. How does one retrieve the url of an instance? Suppose you were asked to work (or login to the front end) on an instance of which you know only the server names, how do you find the login URL?

There are 2 methods (of course, there might be more but I've listed the ones that I use the most) to retrieve the login URL information:

1. Login to sqlplus as apps and query the table icx_parameters for the home_url.

2. Better still and much faster than the above method would be to issue the below command. The output would be nothing but the value for the env variable FORMS60_MAPPING.

$ env | grep http

I prefer the 2nd method over the 1st.

Anyway, to my surprise and dismay, I found out that R12 does NOT set the above env variable. So, the quick recourse to getting the login URL is ruled out or is it?

I have written a shell script to get/set the env variable. You can access it here.

Let me know if you notice any errors or have any comments/suggestions.

Find the number of CPUs and RAM size in UNIX

2009-09-02T12:20:00.011+05:30

I have tried to collate commands to find the number of CPUs and total physical memory (RAM) size in all common flavours of UNIX in one blog entry. This certainly is not an exhaustive list and will be modified as and when I find new commands. You are also more than welcome to add to this list.

Number of CPUs

Sun SPARC Solaris

1. prtconf | grep cpu | wc -l

2. psrinfo | wc -l

3. psrinfo -v | grep "Status of" | wc -l

4. psrinfo -vp

5. prtdiag | grep "on-line" | wc -l

6. mpstat | grep -vi cpu | wc -l

Linux

1. grep "processor"/proc/cpuinfo | wc -l

HP-UX

1. ioscan -kfnC processor | grep "processor" | wc -l

2. sar -M 1 | awk 'END {print NR-5}'

3. sar -Mu 1 1 | awk 'END {print NR-5}'

4. ioscan -kf | grep -c processor

5. cat /var/adm/syslog/syslog.log | grep processor | grep "vmunix" | wc -l

AIX

1. lscfg -vp | grep proc | wc -l

2. lparstat -i | grep "Online Virtual CPUs" | cut -d ':' -f2

3. lsdev -C | grep Process | wc -l

4. lsdev -Ccprocessor | grep -ci available

Total Physical Memory (RAM) Size

Solaris

1. prtdiag | grep "Memory size" | cut -d ':' -f2

2. prtconf | grep "Memory size" | cut -d ':' -f2

Linux

1. echo "$(free -mt | grep "Mem:" | awk '{print $2}' | tr -d [:space:]) MB"

or

free -mt | grep "Mem:" | awk '{print $2}' | tr -d [:space:];echo " MB"

2. grep MemTotal /proc/meminfo | cut -d ':' -f2

HP-UX

1. echo "selclass qualifier memory;info;wait;infolog" | /usr/sbin/cstm | grep "Total Configured Memory" | cut -d ':' -f2

2. echo "$(grep Physical /var/adm/syslog/syslog.log | awk '{print $7}') KB"

AIX

1. echo "$(lsattr -El mem0 | grep "Total" | awk '{print $2}') MB"

2. lsattr -El mem0 | grep "Total" | awk '{print $2}'; echo " MB"

Choose Connection Not Visible in Discoverer 10g after install

2009-08-28T16:57:00.008+05:30

Issue

“Choose Connection” is NOT seen in Discoverer Plus and Discoverer Viewer screen after installation.

Symptom

Installed Discoverer 10g as an OracleBI standalone installation (i.e., without associating with an OracleAS infrastructure).

Associated the standalone Discoverer to Oracle Apps Database.

Users are unable to see Choose Connection in the Discoverer Plus and Viewer login screen.

Cause

Discoverer has not been associated with an infrastructure. This happens when Discoverer 10g is installed as a Standalone without associating it with an OracleAS infrastructure.

Quoting from the documentation:

“An OracleBI standalone installation is not associated with an OracleAS Infrastructure, and therefore has a limited number of components available.

..

..

If Discoverer is not associated with an OracleAS Infrastructure, Discoverer connections are not available to end users. For more information about associating Discoverer with an Oracle Infrastructure install, see Chapter 2, "About Oracle Business Intelligence installations and OracleAS Infrastructures".”

Resolution

2.2 How to associate an OracleBI installation with an OracleAS Infrastructure

If you install an OracleBI standalone installation, you might want to associate it with an Oracle Application Server Infrastructure in order to deploy Discoverer with other OracleAS components (e.g. Oracle Portal, Oracle Single Sign-On).

You can associate OracleBI Discoverer with either an OracleAS 9.0.4 Infrastructure or an OracleAS 10.1.2 Infrastructure.

Note: If you want to associate an OracleBI standalone installation with a 9.0.4 Infrastructure, you must also upgrade the Discoverer part of the MR to deploy Discoverer Portlet Provider (for more information, see Section 2.3, "How to upgrade a 9.0.4 metadata repository to work with Discoverer Portlet Provider Version 10.1.2 and Oracle Portal 10.1.2").

To associate an OracleBI standalone installation with an OracleAS Infrastructure:

Start a Web browser and enter the Application Server Control URL containing the fully qualified host name and domain of the OracleBI installation that you want to configure (for more information, see Section 5.1.2, "How to start Application Server Control and display the System Components page").

Display the Infrastructure tab.

In the Identity Management area, do one of the following:

to associate the OracleBI installation, click Configure to display the Configure Identity Management: Internet Directory page

to reassociate the OracleBI installation, click Change to display the Change Identity Management: Internet Directory page

Use the Host field and the Port field to enter the host name (e.g. infra.mycompany.com) and port number (e.g. 389) of the Oracle Internet Directory component on the OracleAS Infrastructure machine.

Hint: To find out the host name and port number values to specify, start Oracle Application Server Control on the OracleAS Infrastructure machine, display the Infrastructure tab, and note down the values of the Internet Directory Host field and the Internet Directory Port field.

Click Next to display the Configure Identity Management: Login page.

Use the User Name field and the Password fields to enter the administration user name and password for the Oracle Internet Directory component on the Infrastructure machine.

Hint: Prefix the user name value with cn= (e.g. cn=orcladmin).

Click Next to display the Configure Identity Management: Validation page.

Click Finish.

Having configured Identity Management, you now configure the Metadata Repository.

In the Metadata Repository area, click Configure to display the Configure Repository: Internet Directory page.

Follow the steps in the Configure/Change Repository: Internet Directory wizard, then click Finish.

The OracleBI standalone installation is now associated with an OracleAS Infrastructure. You can now deploy Discoverer with other OracleAS components (i.e. Oracle Portal, Oracle Single Sign-On) and use Discoverer connections and Discoverer Portlet Provider.

Reference

1. http://download.oracle.com/docs/cd/B14099_19/bi.1012/b13918/assoc.htm#CJAJGEJG

2. 390222.1 - How To Connect to Discoverer Plus/Viewer When "Create Connection" Button Is Not Displayed in Connection Page

Oracle Discoverer 10g rehost a.k.a moving Discoverer 10g from one server to another

2009-08-18T12:43:00.008+05:30

Long time no blog! The reason being that I was busy performing this Discoverer 10g rehost. Without wasting much of your time, let me get into the details.

Background

Recently, we rehosted (i.e., moved) our production instance to a new set of production servers. Discoverer 10g is also a part of our prodcution implementation. Discoverer resided on the old production servers.

Requirement

Move Discoverer 10g from the old production servers to the new ones (a.k.a rehost!).

Methodology

After scrutinizing all the available Metalink docs, I found out that no Metalink doc existed for my requirement! I had to chart my own course here (not that no one had done this before but lack of documentation - both on the web and Metalink, was a bit of an impediment).

After burrowing through all the available documentation, I surmised that I had two methods to achieve my goal:

1. Reinstallation of Discoverer 10g (including OID)

2. Cloning of Discoverer 10g ORACLE_HOME.

Steps involved in both the methods is given below:

Reinstallation of Discoverer 10g

1. Install OracleAS Identity Management Infrastructure 10g (10.1.4.0.1) [infra tier].

2. Upgrade OracleAS Identity Management Infrastructure 10g to Oracle Identity Management 10g Release 3 Patch Set 1 (10.1.4.2).

3. Install Oracle Application Server 10g Release 2 (10.1.2.0.2) type Business Intelligence and Forms (mid tier).

4. Copy Database Connection file from $FND_TOP/secure/cpepdb1_cpep.dbc on the admin node to DISCOVERER $ORACLE_HOME/discoverer/secure on the new Discoverer server.

Notes:
a. One can also copy the dbc file from the old Discoverer ORALCE_HOME
on the old server to the new Discoverer ORACLE_HOME on the new
server.

b. Create the directory
$ORACLE_HOME/discoverer/secure if it does not exist.

5. Update tnsnames.ora file - Copy the file DISCOVERER

$ORACLE_HOME/network/admin/tnsnames.ora on the old server to Discoverer

$ORACLE_HOME/network/admin/tnsnames.ora on the new server.

6. Specify Java Virtual Machine for Discoverer Plus - Check what is selected as the default (should be Sun Java 1.4) in Enterprise Manager on the Oracle Business Intelligence 10g Release 2 ORACLE_HOME using ias_admin login.

7. Modify below Applications profile options related to Discoverer in Oracle Applications

ICX_DISCOVERER_LAUNCHER & ICX_DISCOVERER_VIEWER_LAUNCHER

Cloning of Discoverer 10g ORACLE_HOME

1. Install OracleAS Identity Management Infrastructure 10g (10.1.4.0.1) [infra tier] on suomp02l.

2. Upgrade OracleAS Identity Management Infrastructure 10g to Oracle Identity Management 10g Release 3 Patch Set 1 (10.1.4.2) on suomp02l.

3. Prepare the source middle tier (Discoverer 10gAS on suomp971) for cloning.

a. For cloning, Perl 5.6.1 or higher must be installed on your system.

$ export

PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1:$ORACLE_HOME/perl/lib/site_perl/5.6.1:$PERL5LIB

b. Run the script prepare_clone.pl. This script prepares the source to be cloned.

$ cd $ORACLE_HOME/clone/bin

$ perl prepare_clone.pl ORACLE_HOME=$ORACLE_HOME -oid_password <OIDPassword>

4. Archive and compress the source Oracle home, using your preferred tool for archiving (using tar and gzip).

5. Copy the compressed Oracle home from the source machine to the destination machine.

6. Extract the compressed Oracle home into a directory, which will become the new Oracle home at the destination location.

7. Run the clone.pl script. You must have write permission to the directory containing the Oracle inventory file.

$ cd $ORACLE_HOME/clone/bin

$ perl clone.pl ORACLE_HOME=<NEW_ORACLE_HOME_LOCATION> ORACLE_HOME_NAME=OH_1012_BI -instance <new_instance_name> -ias_admin_old_pwd <old_ias_admin_password> -ias_admin_new_pwd <new_ias_admin_password> -oid_password <oid_password>

8. Modify below Applications profile options related to Discoverer in Oracle Applications

ICX_DISCOVERER_LAUNCHER & ICX_DISCOVERER_VIEWER_LAUNCHER

The method that I followed was reinstallation primarily because cloning a 10g AS ORACLE_HOME is renowned to be riddled with errors and pitfalls! The reinstallation method worked like a charm and I was able to move the Discoverer 10g without any hitches and significantly, without any downtime too (!) thus avoiding weekend work!!!

References

1. Cloning a 10g AS ORACLE_HOME

2. Metalink Note ID: 313418.1 - Using Discoverer 10.1.2 with Oracle E-Business Suite 11i

Extract Controlfile from a backupset using RMAN

2009-07-28T12:16:00.009+05:30

In my previous post, I had talked about extracting controlfiles, datafiles and archivelogs from RMAN backupsets using the package dbms_backup_restore.

In this post, I would like to throw light upon using RMAN as an alternate method that could be employed to extract controlfiles from RMAN backupsets. This method uses the RMAN command "restore controlfile from '<FULL_PATH_TO_CONTROLFILE_BACKUP>'".

The RMAN method can be used in 3 scenarios. So, make sure that at least any one of the backups is indeed available. Else, you are DOOMED!

I. the controlfile autobackup is turned on in RMAN. In this case the file format would be "c<DBID>-<DATE>-<COUNT>".

It is turned on using the command:

configure controlfile autobackup on;

configure controlfile autobackup format for device type disk to '/opt/apps/vision/backup/%F';

II. the controlfile snapshot backup is configured in RMAN.

It is turned on using the command:

configure snapshot controlfile name to '<FULL_PATH_TO_SNAP_CTRL_FILE>';

III. the controlfile is included in the RMAN backupset i.e., using the clause "include current controlfile" in the backup database command.

Scenario I - Restoring controlfile using RMAN and controlfile autobackup.

a. Startup the database in nomount state.

b. Run RMAN commands as below:

$ rman target /

Recovery Manager: Release 9.2.0.6.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: BKP (not mounted)

RMAN> restore controlfile from '/opt/apps/vision/backup/c-261506738-20090722-02';

Starting restore at 23-JUL-09

using target database controlfile instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=16 devtype=DISK

channel ORA_DISK_1: restoring controlfile

channel ORA_DISK_1: restore complete

replicating controlfile

input filename=/opt/apps/vision/bkpdata/bkpdatacontrol01.ctl

output filename=/opt/apps/vision/bkpdata/bkpdatacontrol02.ctl

output filename=/opt/apps/vision/bkpdata/bkpdatacontrol03.ctl

Finished restore at 23-JUL-09

RMAN>

Scenario II - Restoring controlfile using RMAN and controlfile snapshot.

a. Startup the database in nomount state.

b. Run RMAN commands as below:

$ rman target /

Recovery Manager: Release 9.2.0.6.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: BKP (not mounted)

RMAN> restore controlfile from '/opt/apps/vision/backup/snap_BKP_controlfile.ctl';

Starting restore at 23-JUL-09

using target database controlfile instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=16 devtype=DISK

channel ORA_DISK_1: copied controlfile copy

replicating controlfile

input filename=/opt/apps/vision/bkpdata/bkpdatacontrol01.ctl

output filename=/opt/apps/vision/bkpdata/bkpdatacontrol02.ctl

output filename=/opt/apps/vision/bkpdata/bkpdatacontrol03.ctl

Finished restore at 23-JUL-09

RMAN>

Scenario III - Restoring controlfile using RMAN and backupsets (provided backup is taken using the "include current controlfile" clause).

Note: The RMAN backup log would record the backup piece name which includes the controlfile. If you are not sure which backup pice contains the controlfile, then try this on all the backup pieces till the controlfile is restored.

a. Startup the database in nomount state.

Starting restore at 23-JUL-09

using target database controlfile instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=16 devtype=DISK

channel ORA_DISK_1: restoring controlfile

channel ORA_DISK_1: restore complete

replicating controlfile

input filename=/opt/apps/vision/bkpdata/bkpdatacontrol01.ctl

output filename=/opt/apps/vision/bkpdata/bkpdatacontrol02.ctl

output filename=/opt/apps/vision/bkpdata/bkpdatacontrol03.ctl

Finished restore at 23-JUL-09

RMAN>

Extract Controlfile, Datafiles & Archivelogs from RMAN Backupsets

2009-07-27T16:48:00.009+05:30

The other day, one of my colleagues posed me this interesting and thought-provoking question/scenario: "Suppose, you were to lose both your production and your RMAN catalog databases irrevertably. You do have a full/incremental RMAN backup, however. How are you going to recover?"

How indeed? I scratched my head and shone a torch upon the dark recesses of my memory to come up with an answer. The light (of the torch) did indeed reveal something - that is that a note existed in Metalink which list the method that could be used to delve into a RMAN backup set to extract the controlfile, the datafiles and also the archivelogs and Voilà, I hit the nail right on the head.

I did some more digging and found a bunch of "Notes" in Metalink that pointed me to the eventual answer to the above question. The Notes that I am referring to are listed below. A summary of the procedure to be followed in such scenarios is also provided. All the notes mentioned use the package dbms_backup_restore.

Scenario:

A successful production backup (RMAN) is available (cold or hot - if hot, then the archivelogs are also available) while the production and the RMAN catalog databases are irrevertably lost.

Assumptions:

1. RMAN is being used to backup the database.

2. The RMAN backup is a "good" backup and is restore and recovery ready.

3. The list of datafiles (file name + full file path) and their datafile numbers is available (this can be obtained from the RMAN backup log file).

Solution Summary (from Note ID: 388057.1):

1. Start the target instance (nomount).

2. Restore the latest controlfile, ideally from the same backupset as the last incremental to be restored (Note 388052.1 : restore controlfile using PLSQL).

3. Make further copies if necessary as per the target database init.ora (pfile).

4. Mount the target instance (alter database mount).

NOTE: (RMAN may be able to be used to restore / recover the database at this point if the controlfile restored knows about the backup required to be restored and recovered).

5. Restore the datafiles from the level 0 backupset (Note 388053.1 : restore datafiles using PLSQL).

6. Restore (apply) the datafiles from the incremental backupsets (Note 388054.1 : restore incremental backups using PLSQL).

7. Restore the archivelogs from the archivelog backupset (Note 388055.1 : restore archivelogs using PLSQL).

8. Using traditional recovery techniques, recover the database using SQLPLUS. (until cancel using backup controlfile).

9. Open the database (resetlogs).

10. Rebuild the recovery catalog & re-register the target database.

11. Make backups of the target database and recovery catalog database.

References:

1. 60545.1 - How to Extract Controlfiles, Datafiles, and Archived Logs from RMAN Backupsets

2. 388052.1 - How to Extract the Control file from an RMAN backupset using PL/SQL

3. 388053.1 - How to Extract Datafile(s) from an RMAN backupset Level-0(Full) backup using PL/SQL.

4. 388054.1 - How to Extract Incremental Backups of Datafile(s) from an RMAN Incremental backupset Level-1 or higher using PL/SQL.

5. 388055.1 - How to Extract archive logs from an RMAN using PL/SQL.

6. 388057.1 - How to Restore an RMAN Backed up database using PL/SQL.

Creating defaultsfile - an easy guide!

2009-07-01T14:38:00.013+05:30

This post is for the automation fanatics and enthusiasts. Of course, the others aren't excluded! The defaultsfile option that can be used with adutilities is a handy option to save time during patching or other maintenance activities.

Though the defaultsfile gets a mention by almost everyone in the context of reducing downtime, the method of creating it is not, unfortunately. In this post, I'd like to share this using an example - compiling apps schema using adadmin.

Note: The full explanation of creating a defaultsfile is mentioned in Chapter 2 (Maintaining Your System) of the Oracle Applications Maintenance Procedures.

1. Run adadmin command as below:

adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/<DEFAULTS_FILE_NAME>

e.g.:

adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/adadmin_cmpl_apps_schema.txt

The above command searches for a file with name adadmin_cmpl_apps_schema.txt in $APPL_TOP/admin/$TWO_TASK directory. If it exists, it uses the values provided in that file. Else, it creates a new file and the contents of the new file would be the answers that one provides at the subsequent prompts of adadmin.

Note: Not mentioning the correct path would error out as below. So, always ensure that you mention the full path to the default file.

$ adadmin defaultsfile=adadmin_maint_on.txt

Redwood Shores, California, USA
Oracle Applications AD Administration
Version 11.5.0

NOTE: You may not use this utility for custom development unless you have written permission from Oracle Corporation.

You have not specified the correct path for defaults file.

The expected location of the defaults file is

$APPL_TOP/admin/$TWO_TASK

and you must provide full access path to the file.

2. Assuming that the defaultsfile does not exist, proceed as you normally would for compiling apps schema.

3. Once the compilation is complete, exit out of adadmin and look for the file $APPL_TOP/admin/$TWO_TASK/adadmin_cmpl_apps_schema.txt.

4. Voilà! You are done! You have the defaultsfile (including passwords) for compiling apps schema. Whenever you want to compile apps schema using adadmin NON-INTERACTIVELY, run the below command:

$ adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/adadmin_cmpl_apps_schema.txt

PCP in Non-RAC Instances - Pitfalls -- Solutions?

2009-06-30T18:27:00.008+05:30

In the last post, I had talked about the pitfalls in configuring PCP in non-RAC instances. I have figured out (is it really the solution? - only time will tell!) workarounds for the 2 pitfalls that I had listed.

1. How would the VNC server failover when using PCP with non-RAC?

Ans: The best bet would be to start the vnc server using the virtual hostname instead of the physical hostname (thanks to my colleague Mansoor who suggested it).

Once the VNC is started using the virtual hostname, set the display variable to this value and run autoconfig so that the changes are reflected in the concurrent manager and the report server startup scripts.

So, when the VIP (and hence, the virtual host) fails over, the VNC would still be running. This solution needs to be tested thoroughly though.

Below is the command that I can use:

$ vncserver -name <VIRTUAL_HOSTNAME>:<PORT_NUMBER>

2. The web tier 806_ORACLE_HOME tnsnames.ora can be modified to include the FNDFS_<PHYSICAL_HOSTNAME> entry for both the primary server and the failover server. This way, there is no need to reconfigure it each and every time.

Of course, this tnsnames.ora should be saved before running autoconfig else it would be overwritten.

Signing off hoping that the above solutions work!

PCP in non-RAC instances - Pitfalls

2009-06-03T15:00:00.003+05:30

It was a wonderful surprise when I stumbled upon Metalink Note ID: 743716.1, which details steps for configuring PCP in non-RAC instances.

This is very useful while using hardware or software clustering and not Oracle clustering (RAC).

We are using Veritas clustering (software clustering) for server failover. We were recently testing server failover and there are two things that I am unable to figure out as of now and which necessitates manual intervention rendering the whole failover mechanism manual:

1. How would the VNC server failover when using PCP with non-RAC?

Since, the hostname changes when the failover happens, the display variable would still be pointing to the failed server and not the failover server.

The work around to the above problem is obviously to reset the display variable and run autoconfig, which would mean that the failover mechanism is no longer automatic.

2. As soon as the failover happens, we need to make a manual entry in the Web Tier $TNS_ADMIN/tnsnames.ora to reflect the change in the concurrent processing server. This entry takes the form FNDFS_<FAILOVER_SERVER_NAME>. Thankfully, this does NOT necessitate downtime of the instance.

Do post your comments/suggestions/ideas on the above 2 problems.

Find Tablespace Usage Percent

2009-03-17T14:54:00.008+05:30

I have written a sql query which would output the usage percent of all the tablespaces in a given database. Although lots of tablespace usage queries are available, most of them, if not all, utilise dba_free_space view as the tool of measurement.

It is well known that this view is inadequate when datafiles have autoextend feature turned on. I have tried to address this inadequacy in my query.

Caveat: This query is not optimised for performance. Hence, you might observe slow performance.

set linesize 100
set pagesize 10000
set feedback off verify off
col tablespace_name format a18
col num_files format 999
col total_space format 9999999.99
col free_space format 9999999.99
col used_percent format 999.99



select /*+ parallel(tbs) */
tbs.tablespace_name,
count(tbs.file_id) num_files,
round(sum(TOTAL_SPACE),2) TOTAL_SPACE_MB,
sum(FREE_SPACE) FREE_SPACE_MB,
round(((sum(tbs.USED_SPACE)/sum(tbs.TOTAL_SPACE)) * 100),2) USED_PERCENT
from
(select
ddf.file_id file_id,
ddf.file_name,
ddf.tablespace_name tablespace_name,
round(decode(ddf.autoextensible,'NO',ddf.bytes/1024/1024,'YES',ddf.maxbytes/1024/1024),2) "TOTAL_SPACE",
ddf.autoextensible,
case
when ddf.maxbytes < ddf.bytes
then round(((ddf.bytes/1024/1024) - nvl((dfs.free_space),0)),2)
else round(ddf.bytes/1024/1024,2)
END "USED_SPACE",
case
when ddf.maxbytes < ddf.bytes
then nvl(dfs.free_space,0)
else round(((ddf.maxbytes/1024/1024) - (ddf.bytes/1024/1024)),2)
END "FREE_SPACE"
/*, case autoextensible
when 'NO' then round(((((ddf.bytes/1024/1024) - nvl(dfs.free_space,0)) / (ddf.bytes/1024/1024)) * 100),2)
when 'YES' then round((((ddf.bytes/1024/1024) / (ddf.maxbytes/1024/1024)) * 100),2)
END  '%' "USED_PERCENT" */
from dba_data_files ddf,
(select file_id, round(sum(bytes)/1024/1024,2) free_space from dba_free_space group by file_id) dfs
where
ddf.file_id = dfs.file_id(+)
) tbs
group by tbs.tablespace_name
order by 5 desc;

EXCEPTION_ACCESS_VIOLATION in Jinitiator and IE crashes

2009-03-04T11:51:00.006+05:30

The Environment
Apps Version : 11.5.10.2
Jinitiator Version : 1.3.1.26
Internet Explorer Version : 6

The Issue

One of our end users was receiving a very strange error while using jinitiator. The Internet explorer window would crash (close by itself) after creating a file HS_ERR_PID<####>.log on the desktop, where #### stands for the Process ID of the f60webmx session.

This error is intermittent and not easily reproducible. Moreover, the user would have to redo his/her work and hence this issue was turning out to be very vexing indeed.

The error log is as below:

An unexpected exception has been detected in native code outside the VM.

Unexpected Signal : EXCEPTION_ACCESS_VIOLATION occurred at PC=0x6D043D1AFunction name=Java_sun_java2d_loops_DefaultComponent_IntIsomorphicCopy

Library=C:\Program Files\Oracle\JInitiator 1.3.1.26\bin\awt.dll
Current Java thread:

at sun.java2d.loops.DefaultComponent.IntIsomorphicCopy(Native Method)

at sun.java2d.loops.IntRgbToIntRgb.OpaqueBlit(Unknown Source)

at sun.java2d.loops.RasterOutputManager.performOpaqueBlit(Unknown Source)

at sun.java2d.loops.RasterOutputManager.compositeSrcDst(Unknown Source)

at sun.java2d.loops.RasterOutputManager.renderImage(Unknown Source)

at sun.java2d.SunGraphics2D.renderingPipeImage(Unknown Source)

at sun.java2d.SunGraphics2D.drawImage(Unknown Source)

at sun.awt.windows.WGraphics.drawImage(Unknown Source)

at oracle.ewt.lwAWT.SharedPainter.paint(Unknown Source)

at oracle.ewt.lwAWT.BufferedFrame.paint(Unknown Source)

at sun.awt.RepaintArea.paint(Unknown Source)

at sun.awt.windows.WComponentPeer.handleEvent(Unknown Source)

at java.awt.Component.dispatchEventImpl(Unknown Source)

at java.awt.Container.dispatchEventImpl(Unknown Source)

at java.awt.Window.dispatchEventImpl(Unknown Source)

at java.awt.Component.dispatchEvent(Unknown Source)

at java.awt.EventQueue.dispatchEvent(Unknown Source)

at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)

at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)

at java.awt.EventDispatchThread.pumpEvents(Unknown Source)

at java.awt.EventDispatchThread.run(Unknown Source)
Dynamic libraries:

0x00400000 - 0x0049B000 C:\Program Files\Internet Explorer\iexplore.exe

0x7C900000 - 0x7C9B0000 C:\WINNT\system32\ntdll.dll

0x7C800000 - 0x7C8F5000 C:\WINNT\system32\kernel32.dll

0x77DD0000 - 0x77E6B000 C:\WINNT\system32\ADVAPI32.dll

0x77E70000 - 0x77F02000 C:\WINNT\system32\RPCRT4.dll

0x77FE0000 - 0x77FF1000 C:\WINNT\system32\Secur32.dll

0x77F10000 - 0x77F58000 C:\WINNT\system32\GDI32.dll

0x7E410000 - 0x7E4A0000 C:\WINNT\system32\USER32.dll

0x77C10000 - 0x77C68000 C:\WINNT\system32\msvcrt.dll

0x77F60000 - 0x77FD6000 C:\WINNT\system32\SHLWAPI.dll

0x7C9C0000 - 0x7D1D6000 C:\WINNT\system32\SHELL32.dll

0x774E0000 - 0x7761D000 C:\WINNT\system32\ole32.dll

0x78130000 - 0x78257000 C:\WINNT\system32\urlmon.dll

0x77120000 - 0x771AB000 C:\WINNT\system32\OLEAUT32.dll

0x78000000 - 0x78045000 C:\WINNT\system32\iertutil.dll

0x77C00000 - 0x77C08000 C:\WINNT\system32\VERSION.dll

......

....

Initial Troubleshooting

Uninstalling and reinstalling jinitiator did not work. So, probed in Metalink to find out whether it is a known issue.

Found only one relevant hit in Metalink - Doc ID: 422761.1 - for this issue.

The Cause

As per the Note 422761.1, "This is a work station hardware issue related to a device driver associated with usually video cards. It also could cause by other device drivers."

The Solution

Again, as per the Note 422761.1, there are 2 solutions.

Solution 1
1. Add the following parameter to your Jinitiator Java Runtime Parameters:2. Control Panel > Open Jinitiaitor 1.3.1.24 (or whatever version) > [Tab] Basic > Enter the following in your Java Runtime Parameters field:
-Dsun.java2d.noddraw=true

3. Click Apply

4. Open your browser, then clear browser cache, and test.

Solution 2
To add to the environment:
1. Start->Control Panel->System
2. Click the Advanced Tab
3. Click the Environment Variables button
4. Click the New button
5. For Variable name:_JAVA_OPTIONS
6. For Variable value -Dsun.java2d.noddraw=true [or -Dsun.java2d.d3d=false ]
7. Click OK

Followed solution 1 mentioned above and Voilà, the issue is resolved.

java.lang.ExceptionInInitializerError while trying to Access login page

2009-02-16T18:33:00.017+05:30

Today morning, we (the DBAs) were faced with a strange issue. On Saturday (the 14th of February - (St.) Valentine's day!!), the UNIX boxes hosting our critical test instance went down for scheduled maintenance without any prior information. So, we could not bring down our databases and applications.

As soon as the unix box came up, we encountered this issue. Let me briefly describe the environment, the issue, the analysis performed and of course, the solution to this most vexing problem.

Environment:

Apps Version: 11.5.10.2 with ATG RUP 5

DB Version: 10.2.0.3

Architecture: Two Node instance with database + admin on one tier and web server on another.

PS: Another instance (development) exists on the same server with the same configuration, which was working fine.

Issue:

When trying to access the login page of Oracle Apps, received the below error. Even OAM login page was throwing the same error.

Request URI: /OA_HTML/AppsLocalLogin.jsp
Exception:

java.lang.ExceptionInInitializerError

Request URI:/OA_HTML/AppsLocalLogin.jsp
Exception:

java.lang.NoClassDefFoundError

As soon as the services were bounced, we were receiving the first error. After some time, we received the second error (yes, truth is indeed stranger than fiction!!).

Analysis:

Since, we were receiving "NoClassDefFoundError", performed the following:

1. Checked the CLASSPATH and AF_CLASSPATH variable and ensured that every path is valid.

2. Checked all the class paths defined by wrapper.classpath in jserv.properties using the command below:

for i in `grep classpath $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties grep -v "#" cut -d '=' -f2`
do
ls -ld $i >> /tmp/check_class_path.txt
if [[ $? -eq 0 ]]; then
continue
else
printf "\n\n '${i}' is not proper. \n\n"
fi
done

I compared the output generated from the above script with that from the development instance that was working and found no discrepancies.

3. Ran aoljtest.jsp and found that it was running fine till the step "Verify Guest User", where it was throwing "Page cannot be found" error.

4. The above error set me thinking and I checked the GUEST username and password in the

dbc file
database (profile option)
CONTEXT_FILE
output of the below query "select fnd_web_sec.validate_login ('GUEST','<PASSWORD>') from dual;"

and found it to be consistent.

5. Checked a lot of Metalink Notes and documents and blogs and what not, but to no avail.

6. Enabled debug in :

jserv.conf
jserv.properties
httpd.conf

but no log file was informative enough to point me in the right direction.

7. Meanwhile, the error log had been throwing the below error, which I analysed but could not make much headway and found it to be actually misleading.

[Sun Feb 15 23:13:20 2009] [error] [client <IP_ADDRESS>] (78)File name too long: access to /redirectURL$url=http_3A$$ <hostname.domain_name>_3A<port_number>$oa*_servlets$ oracle.apps.fnd.sso.AppsLogin_3FrequestUrl$=$oa*_servlets$weboam$ oam$oamLogin_26cancelUrl$=http_3A$$ <hostname.domain_name>_3A<port_number>$oa*_servlets$ oracle.apps.fnd.sso.AppsLogin$forward=oam$oamLogin failed

8. After exhausting all the options, I decided to take a break and promptly headed for lunch!

9. Post lunch, I tried to re-examine the issue from a different perspective. As point 3 above states, I was receiving an error while trying to check the Guest username using aoljtest.jsp. Again checked the guest username and password in the palces mentioned in point 4 above and found them to be consistent.

10. Finally (actually, I had found this note earlier, but ignored it because I found the guest username and password to be correct), I found Note: 458064.1, which I found relevant on three counts:

It mentions about the "java.lang.ExceptionInInitializerError" error.
we were also on ATG RUP 5 (5473858), as the note mentions.
It also mentions that "Verify Guest user" ends with an error (though different from the one that I was receiving).

So, I decided to follow the solution mentioned in that note.

Solution:

1. a. Login directly to Forms via http://<hostname>:<port>/dev60cgi/f60cgi

b. Set the value of system profile "Password Case Option" to "Insensitive"

c. Save and close Oracle Applications

2. On the operating system as the apps user (e.g. applmgr) execute these commands:

a. cd $FND_TOP/secure/<SID>_<hostname>

b. java oracle.apps.fnd.security.AdminAppServer <apps username>/<apps password> UPDATE GUEST_USER_PWD=GUEST/<PASSWORD ALL UPPERCASE> DB_HOST=<host> DB_NAME=<SID> DB_PORT=<port>

The values for GUEST_USER_PWD, DB_HOST, DB_NAME, and DB_PORT can be found in the $FND_TOP/secure/<SID>_hostname/<SID>.dbc file if required.

3. Verify that the following things are true:

a. The GUEST password is updated and all upper-case in the $FND_TOP/secure/<SID>_<hostname>/<SID>.dbc file.

b. That the profile GUEST_USER_PWD is set correctly after the password change. The SQL to run is:

set serveroutput on set echo on set timing on set feedback on set long 10000 set linesize 120 set pagesize 132 column SHORT_NAME format A30 column NAME format A40 column LEVEL_SET format a15 column CONTEXT format a30 column VALUE format A60 wrap select p.profile_option_name SHORT_NAME, n.user_profile_option_name NAME, decode(v.level_id, 10001, 'Site', 10002, 'Application', 10003, 'Responsibility', 10004, 'User', 10005, 'Server', 10007, 'SERVRESP', 'UnDef') LEVEL_SET, decode(to_char(v.level_id), '10001', '', '10002', app.application_short_name, '10003', rsp.responsibility_key, '10005', svr.node_name, '10006', org.name, '10004', usr.user_name, '10007', 'Serv/resp', 'UnDef') "CONTEXT", v.profile_option_value VALUE from fnd_profile_options p, fnd_profile_option_values v, fnd_profile_options_tl n, fnd_user usr, fnd_application app, fnd_responsibility rsp, fnd_nodes svr, hr_operating_units org where p.profile_option_id = v.profile_option_id (+) and p.profile_option_name = n.profile_option_name and upper(n.user_profile_option_name) = 'GUEST_USER_PWD' and usr.user_id (+) = v.level_value and rsp.application_id (+) = v.level_value_application_id and rsp.responsibility_id (+) = v.level_value and app.application_id (+) = v.level_value and svr.node_id (+) = v.level_value and org.organization_id (+) = v.level_value order by short_name, level_set;

c. That the following SQL returns 'Y':

SELECT fnd_web_sec.validate_login('GUEST','') FROM dual;

4. Bounced the services and retried the login, which worked like a charm.

Flower brackets {} and variables in UNIX - A perfect match

2009-02-05T16:19:00.009+05:30

When passing a variable in unix, the general convention is to use $variable. Although this does suffice in most cases but in some rare cases, it gives connotations totally different than what was originally intended, as shown in the below example.

The requirement

Copy all files ending with ver to ver_old. For example, suppose I have a file called formver, I would like to copy it to formver_old.

The code

below is the code that I wrote to achieve the above requirement.

for i in `ls *ver`

cp -p $i $i_old

done

The pitfall

A cursory glance at the code indicates that it should run smoothly like a river. But it doent' and herein lies the catch. Let's have a look at the error and figure out what exactly is wrong with the above code.
__________________________________________________________________

$ for i in `ls *ver`

> do

> cp -p $i $i_old

> done