Wednesday, June 18, 2014

11gR2 Database with EBS R12 Perl lib version doesn't match executable version

11gR2 Database with EBS R12 Perl lib version doesn't match executable version

Error:
While dbTechStack

RC-00110: Fatal: Error occurred while relinking of ApplyDBTechStack

Perl lib version (v5.8.4) doesn't match executable version (v5.10.0)
( Or )
Perl lib version (v5.8.8) doesn't match executable version (v5.10.0) at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/Config.pm line 46.
Compilation failed in require at /d02/oracle/uatdb/11.2.0.3/appsutil/clone/ouicli.pl line 35.
BEGIN failed--compilation aborted at /d02/oracle/uatdb/11.2.0.3/appsutil/clone/ouicli.pl line 35.

Solution :

Setting the PERL5LIB environment variable on the Database tier

By default, the PERL5LIB environment variable is set to the following on the Database tier:

/perl/lib/5.8.3:/perl/site_perl/5.8.3:/appsutil/perl

But for an instance with 11gR2 Database, the perl version on the database tier is '5.10.0'. So the variable 'PERL5LIB' on the 11gR2 database tier needs to be set as follows:

export PERL5LIB=/perl/lib/5.10.0:/perl/site_perl/5.10.0:/appsutil/perl

Change PERL5LIB path in db context file and run adcfgclone again.

Sunday, June 1, 2014

opatch lsinventory error : LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

$ /u01/app/R12/product/11.2.0/OPatch/opatch lsinventory --invPtrLoc=/etc/oraInst.loc
Oracle Interim Patch Installer version 11.2.0.3.2
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/R12/product/11.2.0
Central Inventory : /u01/app/R12/oraInventory
   from           : /u01/app/R12/product/11.2.0/oraInst.loc
OPatch version    : 11.2.0.3.2
OUI version       : 11.2.0.3.0
Log file location : /u01/app/R12/product/11.2.0/cfgtoollogs/opatch/opatchAM_1.log

List of Homes on this system:

Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73
$

Fix
=====

$ cd $ORACLE_HOME/oui/bin
$ ./attachHome.sh
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 2006 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/R12/oraInventory
'AttachHome' was successful.
$

Friday, May 30, 2014

Move or Rename Tempfile in Oracle

#####  Move tempfile from location  ‘/u01/data/temp01.dbf’ to ‘/u03/data/temp01.dbf’  #####

SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
FILE#  FILE_NAME STATUS
-----------------------------------------------
 1  /u01/data/temp01.dbf  ONLINE
 2 /u02/data/temp02.dbf   ONLINE

SQL> ALTER DATABASE TEMPFILE '/u01/data/temp01.dbf' OFFLINE;

Database altered.

SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
 FILE# FILE_NAME STATUS
----------------------------------------------------
 1 /u01/data/temp01.dbf  OFFLINE
 2 /u02/data/temp02.dbf  ONLINE


#Copy the old temp files to other location(/u03):

SQL> !cp -p /u01/data/temp01.dbf /u03/data/temp01.dbf;

SQL> ALTER DATABASE RENAME FILE '/u01/data/temp01.dbf' TO '/u03/data/temp01.dbf';

Database altered.

SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
  FILE#   FILE_NAME STATUS
--------------------------------------------------------
  1    /u03/data/temp01.dbf OFFLINE
  2   /u02/data/temp02.dbf  ONLINE

SQL> ALTER DATABASE TEMPFILE '/u03/data/temp01.dbf' ONLINE;

Database altered.

SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
 FILE# FILE_NAME STATUS
---------------------------------------------------------
 1 /u03/data/temp01.dbf   ONLINE
 2 /u02/data/temp02.dbf   ONLINE


#Remove the old temp file
SQL> !rm -rf /u01/data/temp01.dbf

Thursday, May 29, 2014

ORA-01105: mount is incompatible with mounts by other instances

Database startup with srvctl failed with following error :

PRCR-1079 : Failed to start resource ora.database.db
CRS-5017: The resource action "ora.database.db start" encountered the following error:
ORA-01105: mount is incompatible with mounts by other instances
ORA-19808: recovery destination parameter mismatch

Verified the value of db_recovery_file_dest & db_recovery_file_dest_size parameters in database :

SQL> sho parameter db_recovery

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_recovery_file_dest                string
+DATA
db_recovery_file_dest_size           big integer
15G
SQL>

SQL> sho parameter db_recovery

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_recovery_file_dest                string
+DATA
db_recovery_file_dest_size           big integer
14G
SQL>

Changed db_recovery_file_dest_size to match on both instances :

SQL> alter system set db_recovery_file_dest_size=15G scope=both;

System altered.

SQL>

Started DB successfully using srvctl.

Tuesday, April 1, 2014

Query to find Oracle Alert

The following query finds all enabled custom alerts. You can comment out the very last two lines (alr.enabled_flag and alr.created_by) to display all both enabled and disabled alerts.

-------------------------------------------------------------------------------
-- Query to find Custom Oracle Alert
-------------------------------------------------------------------------------
SELECT alr.application_id,
       alr.alert_id,
       alr.alert_name,
       alr.start_date_active,
       alr.description,
       alr.sql_statement_text
  FROM alr.alr_alerts alr
 WHERE 1=1
   AND alr.created_by <> 1      -- show only custom alerts
   AND alr.enabled_flag = 'Y';  -- show only enabled alerts

Friday, March 28, 2014

How to clear Cache in EBS 11i and R12 environments and in similar environments

Apache / iAS
For 11i and earlier versions:
- shutdown iAS server
- go to $OA_HTML (for 11.5.9) or $COMMON_TOP (for 11.5.10.x) directory
- backup the directory _pages and delete its contents by running for instance:

rm -rf $COMMON_TOP/_pages/*

- for modplsql caches remove contents of $IAS_ORACLE_HOME/Apache/modplsql/cache directory
- restart iAS server


To clear middle tier cache in release 12:

- go to "Functional Administrator" responsibility
- select Core Services => Caching Framework => Global Configuration => Clear cache
(please review Note 759038.1 for details).
In case you have login issue after accidentally cleared the _pages instead of using the method above for r12
please review Note 433386.1 to recompile jsp files.

Cabo
Images and style sheets can be corrupted or out of sync in the cabo caches,
you may need to clear the related directories after backup:

$OA_HTML/cabo/images/cache
$OA_HTML/cabo/styles/cache


Web Browser

for Internet Explorer:
- go to menu Tools => Internet Options,
- select 'General' tab,
- click on button 'Delete Files' in 'Temporary Internet files' area
or
- click on button 'Delete...' then in 'Delete Browsing History' pop-up window click on 'Delete Files...'
- close all IE windows and restart new browser session.

for Mozilla Firefox:
- go to menu Tools
- select 'Clear Private Data...' or 'Clear Recent History...' then check 'Cache'
or
- go to menu Edit or Tools
- select Preferences or Options.
- expand the 'Advance' options and choose 'Cache' or Privacy
- click the button called 'Clear Cache'.

for Safari:
- go to Safari menu
- select Empty Cache => Click 'Empty' in the dialogue box
for Netscape:
- go to menu Edit => Preferences,
- choose 'Cache' in 'Advanced' category,
- click on buttons 'Clear Memory Cache' and 'Clear Disk Cache'.
- close all Netscape windows and restart new browser session.

Jinitiator

Two possibilities depending of the Jinitiator version:

for 1.1.8.x versions:
- delete all files in directory:

C:\Program Files\Oracle\Jinitiator \jcache\

for 1.3.1.x versions:
- go to Start => Parameters => Control Panel
- double-click on "Jinitiator " icon
- in the new pop-up window, click on "Cache" tab
- click on "Clear Jar Cache " button. On prompt, click Yes.

(you can also delete directly all files under directory:
C:\Documents and Settings\\Oracle Jar Cache)


Java
Java/JRE plug-in (Windows)
- go to Start => Parameters => Control Panel
- double-click on 'Java' icon
- in the new pop-up window, click on 'General' tab
- click on 'Setting...' button in 'Temporary Internet files' area then click on 'Delete Files...' button
(you can select Applets, Applications or other files)

JVM

- go to responsibility "Functional Administrator"
- click on "Core Services" tab then the "Caching Framework" sub-tab
- click on "Global Configuration" link then click on "Clear all cache" button to clear all of the Java Cache's


Portal
- go to the url: http://:/pls/admin_/gateway.htm or cache.htm
- click on "Cache Settings" option and note the "Cache Directory"
- go to this directory and delete all the cache files in the directory and sub directories

See also modplsql caches in Apache/iAS section above.


WebADI / BNE cache
For instance when enabling BNE log
- go to url: http://:/oa_servlets/oracle.apps.bne.framework.BneAdminServlet
- click on the "clear-cache" link
- at the bottom of the page you should see 'Cache Cleared'

Monday, March 24, 2014

Workflow Analyzer

The EBS Workflow Analyzer is a script that reviews the current Workflow Footprint, analyzes the configurations, environment, providing feedback, and recommendations on Best Practices and areas of concern.

Go to Doc ID 1369938.1  for more details and script download with a short overview video on it.

Proactive Benefits:
  • Immediate Analysis and Output of Workflow Environment
    • Identifies Aged Records
    • Identifies Workflow Errors & Volumes
    • Identifies looping Workflow items and stuck activities
    • Identifies Workflow System Setup and configurations
    • Identifies and Recommends Workflow Best Practices
  • Easy To Add Tool for regular Workflow Maintenance
  • Execute Analysis anytime to compare trending from past outputs
The Workflow Analyzer presents key details in an easy to review graphical manner.   See the examples below.
Workflow Runtime Data Table Gauge The Workflow Runtime Data Table Gauge will show critical (red), bad (yellow) and good (green) depending on the number of workflow items (WF_ITEMS).
Workflow Error Notifications Pie Chart A pie chart shows the workflow error notification types.
Workflow Runtime Table Footprint Bar Chart
A pie chart shows the workflow error notification types and a bar chart shows the workflow runtime table footprint.
The analyzer also gives detailed listings of setups and configurations. As an example the workflow services are listed along with their status for review:
The analyzer draws attention to key details with yellow and red boxes highlighting areas of review:
You can extend on any query by reviewing the SQL Script and then running it on your own or making modifications for your own needs:
Find more details in these notes:
  • Doc ID 1369938.1 Workflow Analyzer script for E-Business Suite Worklfow Monitoring and Maintenance
  • Doc ID 1425053.1 How to run EBS Workflow Analyzer Tool as a Concurrent Request

Thursday, March 20, 2014

Unable to Start “Output Post Processor” Concurrent Manager

Unable to Start “Output Post Processor” Concurrent Manager

Error in logfile :

$ cat FNDOPP106924.txt
Unable to initialize state monitor.
oracle.apps.fnd.cp.gsm.GenCartCommException: ORA-01403: no data found
ORA-06512: at "APPS.FND_CP_GSM_IPC", line 539
ORA-06512: at line 1

        at oracle.apps.fnd.cp.gsm.GenCartComm.initService(GenCartComm.java:233)
        at oracle.apps.fnd.cp.gsm.GenCartComm.(GenCartComm.java:80)
        at oracle.apps.fnd.cp.gsf.GSMStateMonitor.init(GSMStateMonitor.java:74)
        at oracle.apps.fnd.cp.gsf.GSMStateMonitor.(GSMStateMonitor.java:62)
        at oracle.apps.fnd.cp.gsf.GSMServiceController.init(GSMServiceController.java:117)
        at oracle.apps.fnd.cp.gsf.GSMServiceController.(GSMServiceController.java:72)
        at oracle.apps.fnd.cp.gsf.GSMServiceController.main(GSMServiceController.java:446)
0.0119120 secs]
$

Checked and found "Service Manager" was down.

Started "Service Manager" and then started "Output Post Processor".

Wednesday, March 12, 2014

Query a Language Patch

SQL> col PATCH_NAME format a15
SQL> col PATCH_TYPE format a10
SQL> col DRIVER_FILE_NAME format a20
SQL> col PLATFORM format a10
SQL> select AP.PATCH_NAME, AP.PATCH_TYPE, AD.DRIVER_FILE_NAME, AD.CREATION_DATE,  AD.PLATFORM,AL.LANGUAGE
  2  from AD_APPLIED_PATCHES AP, AD_PATCH_DRIVERS AD, AD_PATCH_DRIVER_LANGS AL
where AP.APPLIED_PATCH_ID = AD.APPLIED_PATCH_ID
  3    4  and AD.PATCH_DRIVER_ID = AL.PATCH_DRIVER_ID
  5  and AP.PATCH_NAME = '17666002';

PATCH_NAME      PATCH_TYPE DRIVER_FILE_NAME     CREATION_DATE   PLATFORM   LANG
--------------- ---------- -------------------- --------------- ---------- ----
17666002        ONE-OFF    u17666002.drv        11-MAR-11       LINUX      US
17666002        ONE-OFF    u17666002.drv        11-MAR-11       GENERIC    FRC
17666002        ONE-OFF    u17666002.drv        11-MAR-11       GENERIC    F

SQL>

How to test an SMTP server using telnet

$ telnet server_name smtp
Trying server_name...
Connected to server_name.
Escape character is '^]'.

MAIL from: a@b.com
250-HELO/EHLO Command not issued, proceeding anyway
250 a@b.com... Sender OK
RCPT to: a@b.com
250 a@b.com... Recipient OK
DATA
354 Enter message, end with "." on a line by itself
From: a@b.com
TO: a@b.com
Subject: Test message

This is a test message.
.
250 Message accepted for delivery
QUIT
221 smtp.smtp.com SMTP Service closing transmission channel
Connection closed by foreign host.
$

Monday, March 10, 2014

AutoPatch error: ERROR [code=11] Running adjcopy.class

Getting following error when applying a patch using adpatch :

Running adjcopy.class:

adjava -mx1024m -nojit oracle.apps.ad.jri.adjcopy @/u01/oracle/DEV/apps/apps_st/appl/admin/DEV/out/apps.cmd
stat_low = B
stat_high = 0
emsg:was terminated by signal 11

AutoPatch error:
ERROR [code=11] Running adjcopy.class


AutoPatch error:
Error updating master archive


An error occurred while Updating Oracle Applications Java files if necessary.
Continue as if it were successful [No] :

FIX
====
Run the adrelink

$ adrelink.sh force=y ranlib=y "ad adjava"

Once the adrelink completes successfully rerun the patch. This will resolve the issue.

Friday, February 28, 2014

ORA-01586: When trying to drop a database (RAC)

SQL> startup mount restrict;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             507511688 bytes
Database Buffers          545259520 bytes
Redo Buffers               13905920 bytes
Database mounted.
SQL> select name from v$database;

NAME
---------
R12TST

SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation


SQL> sho parameter cluster_data

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
SQL> alter system set cluster_database=FALSE scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount restrict;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             507511688 bytes
Database Buffers          545259520 bytes
Redo Buffers               13905920 bytes
Database mounted.
SQL> select name from v$database;

NAME
---------
R12TST

SQL> sho parameter cluster_data

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>

Apps User Connection Details


This SQL shows details about users connected to the system (sid, responsibilty, form name, Oracle pid, Application pid and more..)

The parameters are Apps Username or Oracle Session Id (put % where you don't pass a parameter).

It contains 3 SQL's with unions:
1) 1st sql - OA Framework screens (login screen, iSupport etc...)
2) 2nd sql - Responsibility connection details (for Java Applet)
3) 3rd sql - Forms connection details


select usr.user_name "Apps Username"
,i.first_connect "First Connect Date"
,ses.sid
,ses.serial#
,ses.module
,v.spid "Oracle Server Process"
,ses.process "Application Server Process"
,rsp.responsibility_name "Responsibility Name"
,null "Responsibility Start Time"
,fuc.function_name "Function Name"
,i.function_type "Function Type"
,i.last_connect "Function Start Time"
from icx_sessions i
,fnd_logins l
,fnd_appl_sessions a
,fnd_user usr
,fnd_responsibility_tl rsp
,fnd_form_functions fuc
,gv$process v
,gv$session ses
where i.disabled_flag = 'N'
and i.login_id = l.login_id
and l.end_time is null
and i.user_id = usr.user_id
and l.login_id = a.login_id
and a.audsid = ses.audsid
and l.pid = v.pid
and l.serial# = v.serial#
and i.responsibility_application_id = rsp.application_id(+)
and i.responsibility_id = rsp.responsibility_id(+)
and i.function_id = fuc.function_id(+)
and i.responsibility_id not in (select t1.responsibility_id
from fnd_login_responsibilities t1
where t1.login_id = l.login_id)
and rsp.language(+) = 'US'
and usr.user_name like '&APPS_USER_NAME'
and ses.sid like '&SID'
union
select usr.user_name
,l.start_time
,ses.sid
,ses.serial#
,ses.module
,v.spid
,ses.process
,rsp.responsibility_name
,r.start_time
,null
,null
,null form_start_time
from fnd_logins l
,fnd_login_responsibilities r
,fnd_user usr
,fnd_responsibility_tl rsp
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and l.pid = v.pid
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = r.login_id(+)
and r.end_time is null
and r.responsibility_id = rsp.responsibility_id(+)
and r.resp_appl_id = rsp.application_id(+)
and rsp.language(+) = 'US'
and r.audsid = ses.audsid
and usr.user_name like '&APPS_USER_NAME'
and ses.sid like '&SID'
union
select usr.user_name
,l.start_time
,ses.sid
,ses.serial#
,ses.module
,v.spid
,ses.process
,null
,null
,frm.user_form_name
,ff.type
,f.start_time
from fnd_logins l
,fnd_login_resp_forms f
,fnd_user usr
,fnd_form_tl frm
,fnd_form_functions ff
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and l.pid = v.pid
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = f.login_id(+)
and f.end_time is null
and f.form_id = frm.form_id(+)
and f.form_appl_id = frm.application_id(+)
and frm.language(+) = 'US'
and f.audsid = ses.audsid
and ff.form_id = frm.form_id
and usr.user_name like '&APPS_USER_NAME'
and ses.sid like '&SID';

DBC file parameters for R12

DBC file parameters for R12 :

FND_MAX_JDBC_CONNECTIONS=200
FND_JDBC_BUFFER_MIN=5
FND_JDBC_BUFFER_MAX=25%
FND_JDBC_BUFFER_DECAY_INTERVAL=60
FND_JDBC_BUFFER_DECAY_SIZE=1
FND_JDBC_USABLE_CHECK=true
FND_JDBC_CONTEXT_CHECK=true
FND_JDBC_PLSQL_RESET=false

When Apache starts, it will create a buffer of 5 JDBC connections (FND_JDBC_BUFFER_MIN=5) plus a few more as Java dependent products initialize. As users login, those 5 connections will get used and more connections will be created to replenish the buffer. These connections will increase as needed up to the limit of 200 (FND_MAX_JDBC_CONNECTIONS). As users log off, the JDBC connections will die off at a rate of 1 every 60 seconds (FND_JDBC_BUFFER_DECAY_SIZE=1, FND_JDBC_BUFFER_DECAY_INTERVAL=60), but this decay won’t start until you have reached 50 JDBC connections (25% of 200 where FND_JDBC_BUFFER_MAX=25% and FND_MAX_JDBC_CONNECTIONS=200). The decay will slowly reduce the number of inactive JDBC connections down to FND_JDBC_BUFFER_MIN=5, with the least recently used ones being removed first. Note - that each of these parameters are per JVM.

Sunday, February 16, 2014

How to migrate Oracle datafiles to ASM storage via RMAN


$ sqlplus / as sysdba

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING
*
ERROR at line 1:
ORA-19759: block change tracking is not enabled

/*
   If you are using block change tracking, then disable it.
   In my case I am not using it.
*/

SQL> alter system set db_create_file_dest='+DATA' scope=spfile;

System altered.

/*
  This parameter define the default location for data files,
  control_files etc, if no location for these files is specified
  at the time of their creation. +DATA is the diskgroup in ASM.
*/

SQL> alter system set db_create_online_log_dest_1='+DATA' scope=spfile;

System altered.

/*
  This parameter defines the default location number 1 for online logs.
*/

SQL> alter system reset control_files scope=spfile sid='*';

System altered.

/*
   Here we remove the control_files parameter from spfile.
   So next time we restore the control file it will automatically go
   to +DATA diskgroup since it is defined in db_create_file_dest,
   and the new path will be automatically updated in spfile.
*/

SQL> exit

Once all initialization parameters are set properly, start RMAN to migrate the files to ASM diskgroups.

$ rman target /

RMAN> startup nomount

Oracle instance started

Total System Global Area     608174080 bytes

Fixed Size                     1220820 bytes
Variable Size                171970348 bytes
Database Buffers             427819008 bytes
Redo Buffers                   7163904 bytes

RMAN> restore controlfile from
      '/u01/apps/oracle/oradata/ora10g/control01.ctl';

output trimmed......

channel ORA_DISK_1: copied control file copy
output filename=+DATA/ora10g/controlfile/backup.256.716296513
Finished restore at 28-AUG-09

/*
   Control file restored to +DATA from its old location.
   It will be restored to +DATA because we set db_create_file_dest to
   +DATA.
*/

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> backup as copy database format '+DATA';

.......
input datafile fno=00001 name=/u01/apps/oracle/oradata/ora10g
/system01.dbf
output filename=+DATA/ora10g/datafile/system.257.716296627
 tag=TAG20090828T111707 recid=2 stamp=716296715
.......
input datafile fno=00003 name=/u01/apps/oracle/oradata/ora10g
/sysaux01.dbf
output filename=+DATA/ora10g/datafile/sysaux.258.716296723
 tag=TAG20090828T111707 recid=3 stamp=716296785
.......
input datafile fno=00005 name=/u01/apps/oracle/oradata/ora10g
/example01.dbf
output filename=+DATA/ora10g/datafile/example.259.716296789
 tag=TAG20090828T111707 recid=4 stamp=716296809
.......
input datafile fno=00002 name=/u01/apps/oracle/oradata/ora10g
/undotbs01.dbf
output filename=+DATA/ora10g/datafile/undotbs1.260.716296815
 tag=TAG20090828T111707 recid=5 stamp=716296822
.......
input datafile fno=00004 name=/u01/apps/oracle/oradata/ora10g
/users01.dbf
output filename=+DATA/ora10g/datafile/users.261.716296823
tag=TAG20090828T111707 recid=6 stamp=716296823
.......
Finished backup at 28-AUG-09

/* make a copy of all datafiles on +DATA diskgroup. */

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/ora10g/datafile
/system.257.716296627"
datafile 2 switched to datafile copy "+DATA/ora10g/datafile
/undotbs1.260.716296815"
datafile 3 switched to datafile copy "+DATA/ora10g/datafile
/sysaux.258.716296723"
datafile 4 switched to datafile copy "+DATA/ora10g/datafile
/users.261.716296823"
datafile 5 switched to datafile copy "+DATA/ora10g/datafile
/example.259.716296789"

/*
   Switch database to the datafile copies. After this switch the
copies on +DATA
   have become the database datafiles and the original files have
 become copies.
*/

RMAN> alter database open;

database opened

RMAN> exit


Recovery Manager complete.

After you open the database, go ahead and enable the block change tracking file if you disabled it in the start of this process.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Lets now verify where our control file and data files are sitting:

$ sqlplus / as sysdba

SQL> select name from v$datafile;

NAME
-------------------------------------------------
+DATA/ora10g/datafile/system.257.716296627
+DATA/ora10g/datafile/undotbs1.260.716296815
+DATA/ora10g/datafile/sysaux.258.716296723
+DATA/ora10g/datafile/users.261.716296823
+DATA/ora10g/datafile/example.259.716296789

SQL> show parameter control_files

NAME                TYPE        VALUE
------------------- ----------- ------------------------------
control_files       string      +DATA/ora10g/controlfile/backu
                                p.256.716296513
SQL>

They are on the +DATA diskgroup in ASM storage.

Migrate tempfile to ASM storage:

SQL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------------
/u01/app/oracle/oradata/ora10g/temp01.dbf

SQL> alter tablespace temp add tempfile size 20m;

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------------
/u01/app/oracle/oradata/ora10g/temp01.dbf
+DATA/ora10g/tempfile/temp.264.718706509

SQL> alter tablespace temp drop tempfile
  2  '/u01/app/oracle/oradata/ora10g/temp01.dbf';

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------------
+DATA/ora10g/tempfile/temp.264.718706509

SQL> 

Tuesday, February 4, 2014

How to Enable Passwordless Authentication with SSH

Often times you have automated scripts that require access to multiple machines from a single source and need to do so without having to deal with ssh password prompts. And in other instances you may have a bastian host (strong point) security model which you would like to have passwordless communication from. Below are 10 steps to setting up passwordless authentication with SSH in Linux.


Step 1:(*note server1 is the source server and server2 will be the destination server)
server1# mkdir ~/.ssh
Step 2:
server1# cd ~/.ssh
Step 3:
server1# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (“your_local_home”/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in id_rsa.
Your public key has been saved in id_rsa.pub.
The key fingerprint is:
18:6a:e3:78:ab:2d:0c:8e:f9:67:f7:30:32:44:77:34 vv@server1
Step 4:
server1# scp ~/.ssh/id_rsa.pub vv@server2:/home/vv/id_rsa.server1.pub
Step 5:
server1# ssh vv@server2
Password:
Step 6:
server2# mkdir .ssh
Step 7:
server2# chmod 700 .ssh
Step 8:
server2# cat id_rsa.server1.pub >> .ssh/authorized_keys
Step 9:
server2# chmod 644 .ssh/authorized_keys
Step 10:
server2# exit
server1# ssh vv@server2
*Note repeat steps 4-10 for all target servers you would like passwordless access from server1
* FYI Ensure your /home/user directory has the permission 755 also!
You should be all set!

Exporting tables from different Schemas using EXPDP


Following command can be used to export a single table from a different schema :

$ expdp directory=DATA_PUMP_DIR dumpfile=expdp_tst.dmp logfile=expdp_tst.log TABLES=SCHEMA.TABLE_NAME,SCHEMA1.TABLE_NAME

Monday, February 3, 2014

Restore FNDLIBR executable

Restore FNDLIBR executable which has been deleted accidentally:

1. Invoke adadmin on concurrent manager node
2. Select option 2. Maintain Applications Files menu
3. Select 1. Relink Applications programs
4. Enter FND when it prompts for
Enter list of products to link ('all' for all products) [all]
5. When prompted for
Generate specific executable for each selected product [No] ? YES
select YES
& from list of executable select FNDLIBR.

This will create new FNDLIBR executable.

Sunday, February 2, 2014

Build oracle with RAC off/on

If the Oracle binaries are installed with the RAC, one can’t use those binaries to start Oracle Instance if CRS is not running, so in case when one needs to start the instance without CRS one needs to rebuild the oracle and it’s libraries to start the instance.

To turn off RAC
# link the oracle libraries
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_off
# rebuild oracle
$ cd $ORACLE_HOME/bin
$ relink oracle

To turn on RAC
# link the oracle libraries
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on
# rebuild oracle
$ cd $ORACLE_HOME/bin
$ relink oracle

Incase above set of commands do not work following can be used :

To turn on RAC
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on ioracle

Sunday, January 26, 2014

Set Date format in RMAN


rman always shows date in DD-MON-YY format.

How to set date format to MM/DD/YYYY HH24:MI:SS in rman ? 

You can just set the NLS_DATE_FORMAT before going into RMAN: 

$ export NLS_DATE_FORMAT="dd-month-yyyy hh:mi:ss am"
$ rman target=/

Tuesday, January 21, 2014

Useful Queries for Checking Concurrent Requests and Programs

-- Concurrent Program Queries for Apps DBA:


-- Query 1:For checking the locks in concurrent jobs 

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,inst_id,id1, id2, lmode, request, type FROM gV$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY id1,request;

-- Query 2:For checking the concurrent programs running currently with Details of Processed time-- and Start Date

 SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
 a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,(a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
 (a.actual_start_date-a.request_date)*24*60*60 AS lag_time,d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM   apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.FND_CONCURRENT_PROGRAMS_TL c,apps.fnd_user d
WHERE  a.concurrent_program_id=b.concurrent_program_id AND b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by=d.user_id AND status_code='R' order by Process_time desc;
   
-- Query 3:For checking last run of a Concurrent Program along with Processed time
-- Useful to find the Details of Concurrent programs which run daily and comparison purpose

SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
            round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,
            a.request_id,a.parent_request_id,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS'),To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS'),
  To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS'), (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
            (a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
            d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM   apps.fnd_concurrent_requests a,
            apps.fnd_concurrent_programs b ,
            apps.FND_CONCURRENT_PROGRAMS_TL c,
            apps.fnd_user d
WHERE       a.concurrent_program_id= b.concurrent_program_id AND
            b.concurrent_program_id=c.concurrent_program_id AND
            a.requested_by =d.user_id AND
--          trunc(a.actual_completion_date) = '24-AUG-2005'
c.USER_CONCURRENT_PROGRAM_NAME='Incentive Compensation Analytics - ODI' --  and argument_text like  '%, , , , ,%';
--          and status_code!='C'

-- Query 4:For Checking the last run of concurrent Program.
- Use below query to check all the concurrent request running which may refer given package
-- This is very useful check before compiling any package on given instance.
-- The query can be modified as per requirement.
-- Remove FND_CONCURRENT_REQUESTS table and joins to check all program dependent on given package.

SELECT
 FCR.REQUEST_ID
,FCPV.USER_CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_ID
,FCR.STATUS_CODE
,FCR.PHASE_CODE
FROM FND_CONCURRENT_PROGRAMS_VL FCPV
,FND_EXECUTABLES FE
,SYS.DBA_DEPENDENCIES DD
,FND_CONCURRENT_REQUESTS FCR
WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND FE.EXECUTION_METHOD_CODE = 'I'
AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME)
AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- add as required
--AND referenced_owner = 'XXCUS'
AND DD.REFERENCED_NAME = UPPER('&Package_name')
AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID
AND fcr.phase_code NOT IN ( 'C','P');

-- Query 5:The following query will display the time taken to execute the concurrent Programs
--for a particular user with the latest concurrent programs sorted in least time taken 
-- to complete the request. 

 SELECT
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' HOURS ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' MINUTES ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' SECS ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
      f.status_code
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      AND pt.language = USERENV('Lang')
      and f.actual_start_date is not null
order by
      f.actual_start_date desc;

--Query 6: By using the below Query we can get sid,serial#,spid of the concurrent Request..
   
    SELECT a.request_id, d.sid, d.serial# , c.SPID
    FROM apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_processes b,
    v$process c,
    v$session d
    WHERE a.controlling_manager = b.concurrent_process_id
    AND c.pid = b.oracle_process_id
    AND b.session_id=d.audsid
    AND a.request_id = &Request_ID
    AND a.phase_code = 'R';

--Query 7: By using below Concurrent Manager and Program rules...
--Gives Detail of the Concurrent_queue_name and User_concurrent_program_name

SELECT b.concurrent_queue_name, c.user_concurrent_program_name
FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
WHERE a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(INCLUDE_FLAG, 'I', 1, 2), type_code;
   
--Query 8: Gives Details of Running Concurrent jobs

SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
      round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
    a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
      (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
      (a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
      d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM     apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_programs b ,
    apps.FND_CONCURRENT_PROGRAMS_TL c,
    apps.fnd_user d
WHERE   a.concurrent_program_id=b.concurrent_program_id AND
    b.concurrent_program_id=c.concurrent_program_id AND
    a.requested_by=d.user_id AND
    status_code='R' order by Process_time desc;
   
 -- Query 9: Gives detail of Concurrent job completed and pending

SELECT
 FCR.REQUEST_ID
,FCPV.USER_CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_ID
,FCR.STATUS_CODE
,FCR.PHASE_CODE
FROM FND_CONCURRENT_PROGRAMS_VL FCPV
,FND_EXECUTABLES FE
,SYS.DBA_DEPENDENCIES DD
,FND_CONCURRENT_REQUESTS FCR
WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND FE.EXECUTION_METHOD_CODE = 'I'
AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME)
AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- add as required
--AND referenced_owner = 'XXCUS'
AND DD.REFERENCED_NAME = UPPER('&Package_name')
AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID
AND fcr.phase_code NOT IN ( 'C','P');

-- Query 10:Gives Detail of Running and Completed Concurrent jobs with Start date and end date 
-- Latest one at the top

select
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' HOURS ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' MINUTES ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' SECS ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
      f.status_code
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      AND pt.language = USERENV('Lang')
      and f.actual_start_date is not null
order by
      f.actual_start_date desc;

--- Query 11 wait events details related with Concurrent programs

SELECT s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
UPPER (s.program) program, s.TYPE, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.wait_time_micro, s.time_remaining_micro,
s.time_since_last_wait_micro, s.service_name, s.sql_trace,
s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats,
s.session_edition_id, s.creator_addr, s.creator_serial#
FROM v$session s
WHERE ( (s.username IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND') AND STATUS='ACTIVE'
)
ORDER BY "PROGRAM";

-- Query 12 To find the pid of the Concurrent job and kill it.

Select a.inst_id, sid, b.spid
from gv$session a, gv$process b,apps.fnd_concurrent_requests c where a.paddr = b.addr and request_ID ='31689665' 
AND a.inst_id = b.inst_id and c.os_process_id = a.process;

-- Query 13:To find the Database SID of the Concurrent job
-- We need our concurrent request ID as an input.
-- c.SPID= is the operating system process id
-- d.sid= is the Oracle process id

SQL> column process heading "FNDLIBR PID"
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';