Tuesday, May 17, 2016

Transporting a SQL Profile

To transport a SQL profile:
  1. Connect SQL*Plus to the database with the appropriate privileges, and then use the CREATE_STGTAB_SQLPROF procedure to create a staging table to hold the SQL profiles.
    The following example creates my_staging_table in the dba1 schema:
    BEGIN
      DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF ( 
        table_name  => 'my_staging_table'
    ,   schema_name => 'dba1' 
    );
    END;
    /
    
  2. Use the PACK_STGTAB_SQLPROF procedure to export SQL profiles into the staging table.
    The following example populates dba1.my_staging_table with the SQL profile my_profile:
    BEGIN
      DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (  
        profile_name         => 'my_profile'
    ,   staging_table_name   => 'my_staging_table'
    ,   staging_schema_owner => 'dba1' 
    );
    END;
    / 
    
  3. Move the staging table to the database where you plan to unpack the SQL profiles.
    Move the table using your utility of choice. For example, use Oracle Data Pump or a database link.
  4. On the database where you plan to import the SQL profiles, use UNPACK_STGTAB_SQLPROF to unpack SQL profiles from the staging table.
    The following example shows how to unpack SQL profiles in the staging table:
    BEGIN
      DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
         replace            => true
    ,    staging_table_name => 'my_staging_table'
    );
    END;
    /

Sunday, April 10, 2016

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)) );

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.