Validating sysadmin password in 11i/R12

Use Following query to validate wether sysadmin password that we are using is correct or not

select fnd_web_sec.validate_login('SYSADMIN','')
from dual;

If it returns Y then sysadmin password is correct.
If it returns N then sysadmin password that we are using is wrong.

Oracle Apps Printer queries

Printer Navigation steps

        Printer types > Navigate - Install - Printer - Types    FNDPRTYP
        Printer > Navigate -Install - Printer - Register        FNDPRMPR
        Print Styles > Navigate - Install - Printer - Style     FNDPRRPS
        Printer Driver > Navigate - Install - Printer - Driver  FNDPRMPD

SELECT NUMBER_OF_COPIES ,NLS_LANGUAGE ,NLS_TERRITORY ,PRINTER ,
PRINT_STYLE ,COMPLETION_TEXT ,OUTPUT_FILE_TYPE ,
NLS_CODESET ,OUTFILE_NODE_NAME,OUTFILE_NAME
FROM apps.FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID=&REQID;

SELECT PRINTER_STYLE_NAME ,SRW_DRIVER ,WIDTH , LENGTH ,
ORIENTATION FROM apps.FND_PRINTER_STYLES
WHERE PRINTER_STYLE_NAME= ( SELECT PRINT_STYLE FROM apps.FND_CONCURRENT_REQUESTS WHERE REQUEST_ID= &REQID );

SELECT PRINTER_DRIVER_NAME,
USER_PRINTER_DRIVER_NAME ,
PRINTER_DRIVER_METHOD_CODE ,
SPOOL_FLAG ,
SRW_DRIVER ,
COMMAND_NAME ,
ARGUMENTS ,
INITIALIZATION , RESET
FROM apps.FND_PRINTER_DRIVERS
WHERE PRINTER_DRIVER_NAME =( SELECT PRINTER_DRIVER FROM apps.FND_PRINTER_INFORMATION WHERE PRINTER_STYLE=( SELECT PRINT_STYLE FROM apps.FND_CONCURRENT_REQUESTS WHERE REQUEST_ID= &1 AND PRINTER_TYPE=( SELECT PRINTER_TYPE FROM apps.FND_PRINTER WHERE PRINTER_NAME=( SELECT PRINTER
FROM apps.FND_CONCURRENT_REQUESTS WHERE REQUEST_ID= &REQID)) );

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.

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.
$

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

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.

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