Below document describes the high level steps of Oracle Endeca Information Discovery(12.2) implementation/installation and its integration with Oracle EBS R12.2.x
Monday, May 22, 2017
Oracle Endeca Information Discovery (EID) Installation and Overview
Below document describes the high level steps of Oracle Endeca Information Discovery(12.2) implementation/installation and its integration with Oracle EBS R12.2.x
Embedded PL/SQL Gateway: HTTP-404 Bad Request : The HTTP client sent a request that this server could not understand
Issue:
Getting - Bad Request : The HTTP client sent a request that this server could not understand error
WHEN PL/SQL DYNAMIC CONTENT GREATER than default value.
Embedded PL/SQL Gateway: HTTP-404 ORA-6502: PL/SQL: numeric or value error: character string buffer too small.
After enabling trace we could see below error in trace file.
[Refer --> Enable Logging and Debugging for the Embedded PL/SQL HTTP Gateway (EPG) ( Doc ID 563704.1 )]
"Embedded PL/SQL Gateway: /apex/wwv_flow.accept HTTP-400 Too many arguments passed in. Got 2120 parameters. Upper limit is 2000"
Cause:
These kind of issues will occur due to a modplsql error that indicates that the number of parameters exceeds the number of parameters defined for the modplsql Database Access Descriptor (DAD).
The default value is 2000. Since the APEX page request exceeds the maximum defined, the request fails.
Solution:
To modify the global parameter "max-parameters" to be set for the XDB:
1. Connect to the database through SQLPLUS as SYSDBA user and run the following command to set the parameter at the global level:
SQL> exec dbms_epg.set_global_attribute('max-parameters','6000');
SQL> commit;
2. Verify the value set by above
SQL> SELECT dbms_epg.get_global_attribute('max-parameters') FROM dual;
DBMS_EPG.GET_GLOBAL_ATTRIBUTE('MAX-PARAMETERS')
--------------------------------------------------------------------------------
6000
SQL>
3. Restart the database and listener.
4. Test the issue
Unlock specific USER in the APEX Workspace
If an APEX User is unlocked then follow below procedure to unlock the same
Check the status of an User:
SQL> select WORKSPACE_NAME,USER_NAME,ACCOUNT_LOCKED,DESCRIPTION from APEX_040200.apex_workspace_apex_users;
SQL> select DEFAULT_SCHEMA,LAST_FAILED_LOGIN,FAILED_ACCESS_ATTEMPTS,USER_NAME from APEX_040200.WWV_FLOW_FND_USER;
Steps to Unlock ADMIN user in the TEST Workspace
Login to SQL*Plus as the owner of the parsing schema if you are making modifications to a workspace.
As an alternative, you can login as SYS or SYSTEM and then do an alter session:
SQL> alter session set current_schema = TEST;
Run the following, which sets the workspace contect using a combination of apex_util.find_security_group_id ./ apex_util.set_security_group_idand issues the appropriate requests.
SQL> begin apex_util.set_security_group_id(p_security_group_id => apex_util.find_security_group_id('TEST')); apex_util.unlock_account(p_user_name => 'ADMIN'); commit; end; /
Wednesday, May 17, 2017
SQL Script to Start and Stop Oracle EBS Workflow Service Containers
Download(wf_container_restart.sql)
REM +=============================================================================+ REM REM Script Name : wf_container_restart.sql REM REM This is an admin script to start and stop the Workflow Service Containers. REM - WFMLRSVC : Workflow Mailer Service REM - WFALSNRSVC : Workflow Agent Listener Service REM - WFWSSVC : Workflow Document Web Services Service REM REM How to run it? REM REM sqlplus apps/<password> REM REM @wf_container_restart.sql REM REM REM Type Number to Perform Operation: REM REM 1. Activate REM REM 2. Deactivate REM REM 3. Abort REM REM +=============================================================================+ WHENEVER SQLERROR EXIT FAILURE ROLLBACK; WHENEVER OSERROR EXIT FAILURE; SET VERIFY OFF SET SERVEROUTPUT ON SIZE 200000 select to_char(rownum)|| '. ' || decode(meaning, 'Activated', 'Activate', 'Deactivated', 'Deactivate', 'Terminated', 'Abort' ) meaning from fnd_lookups where lookup_type='CP_CONTROL_CODE' and meaning in ('Activated', 'Terminated', 'Deactivated') order by meaning; -- Option value from user accept l_option default '1' prompt 'Enter Workflow Service Container Operation[1]: ' Declare l_qaid number; l_queue number; spid number; errbuf varchar2(300); l_cqname varchar2(300); l_operation varchar2(30) := &l_option; cursor wfsrv_ctl is select application_id, CONCURRENT_QUEUE_ID, CONCURRENT_QUEUE_NAME from fnd_concurrent_queues where CONCURRENT_QUEUE_NAME in ('WFMLRSVC', 'WFALSNRSVC', 'WFWSSVC'); Begin fnd_global.apps_initialize(0,20420,1); for l_rec in wfsrv_ctl loop l_queue := l_rec.CONCURRENT_QUEUE_ID; l_cqname := l_rec.CONCURRENT_QUEUE_NAME; if l_queue > 0 and l_operation = '1' then spid:=fnd_request.submit_svc_ctl_request( command => 'ACTIVATE', service => l_cqname, service_app => 'FND'); elsif l_queue > 0 and l_operation = '2' then spid:=fnd_request.submit_svc_ctl_request( command => 'DEACTIVATE', service => l_cqname, service_app => 'FND'); else spid:=fnd_request.submit_svc_ctl_request( command => 'ABORT', service => l_cqname, service_app => 'FND'); end if; if spid = 0 then errbuf := fnd_message.get; DBMS_OUTPUT.put_line(errbuf); end if; end loop; end; / commit; exit;
Friday, November 18, 2016
Managing Oracle wallets and certificates using orapki utility
Below are the commands to manage oracle wallets and certificates using orapki utility
To create an Oracle wallet:
To create an Oracle wallet:
$ orapki wallet create -wallet wallet_location [-pwd password]
To create an Oracle wallet with auto login enabled:
$ orapki wallet create -wallet wallet_location -auto_login [-pwd password]
To view an Oracle Wallet:
Go To the Wallet Location where cwallet.sso and ewallet.p12 is created
$ orapki wallet display -wallet wallet_location
To view the autologin or password protected oracle wallet:
$ orapki wallet display -wallet wallet_location -pwd <password>
To add a trusted certificate to an Oracle wallet:
$ orapki wallet add -wallet wallet_location -trusted_cert -cert certificate_location -pwd <password>
To add a user certificate to an Oracle wallet:
$ orapki wallet add -wallet wallet_location -user_cert -cert certificate_location -pwd <password>
To remove trusted certificate from an Oracle Wallet:
$ orapki wallet remove -wallet wallet_location -alias 'CN=*.h2hdba.blogspot.com' -trusted_cert -pwd "Welcome1"
To Display Certificate:
$ orapki cert display -cert <Certificate>
If your oracle wallet is not password protected i.e auto login enabled then do not provide the password or do not pass the password parameter in above commands.
Once the certificates are installed, The "UTL_HTTP" package is been used to make callouts from SQL and PL/SQL and access access data on the Internet:
Run UTL.HTTP test:
SQL> select utl_http.request('https://your_site.com',null,'file:<wallet_location>','<wallet_password>') from dual;
This function returns up to the first 2000 bytes of data retrieved from the given URL.
It can be used directly in SQL queries.
Wednesday, November 16, 2016
Script to Monitor Current Database Size and its Growth Since Last Run
Pre-requisites to Run the script:
Make sure mailx and sendmail is installed on your OS, also check below environements/file is created prior to execute the scripts.
This script is tested on Linux Server.
This script needs to be deployed in database node/server.
#Base location for the DBA scripts DBA_SCRIPTS_HOME=$HOME/DBA_MON
#OS User profile where database environment file is set $HOME/.bash_profile
Create Custom .sysenv file for scripts
Download(.sysenv)
Download .sysenv file and save it under $HOME/DBA_MON $ chmod 777 .sysenv
$ cat $HOME/DBA_MON/.sysenv
export DBA_SCRIPTS_HOME=$HOME/DBA_MON export PATH=${PATH}:$DBA_SCRIPTS_HOME export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com #Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert. export DOWNTIME_MODE=N
Script to Monitor Current Database Size and its Growth Since Last Run.
Download MonDBSizeGrowth.sh and save it under $HOME/DBA_MON/bin/ $ chmod 755 MonDBSizeGrowth.sh
#!/bin/bash ################################################################################### # Script Name : MonDBSizeGrowth.sh # # # # Description: # # Script to Monitor Current Database Size and its Growth Since Last Run # # # # Usage : sh <script_name> <ORACLE_SID> # # For example : sh MonDBSizeGrowth.sh ORCL # # # # Note : Initially Run this script for 2 Times # # # # Created by : Kiran Jadhav - (https://h2hdba.blogspot.com) # ################################################################################### # Initialize variables INSTANCE=$1 HOST_NAME=`hostname| cut -d'.' -f1` PROGRAM=`basename $0 | cut -d'.' -f1` export DBA_SCRIPTS_HOME=$HOME/DBA_MON APPS_ID=`echo $INSTANCE | tr '[:lower:]' '[:upper:]'` LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME OUT_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.html.out PREV_PHY_SIZE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.prevphy PREV_LOGI_SIZE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.prevlogi CURR_PHY_SIZE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.currphy CURR_LOGI_SIZE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.currlogi LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err LOG_DATE=`date` LAST_CAPTURED_DATE_TIME=`grep "Current Database Size" $OUT_FILE |cut -d'-' -f2` # Source the env . $HOME/.bash_profile . $DBA_SCRIPTS_HOME/.sysenv if [ $? -ne 0 ]; then echo "$LOG_DATE" > $LOG_FILE echo "Please pass correct environment : exiting the script \n" >> $LOG_FILE #cat $LOG_FILE exit fi if [ -s $OUT_FILE ]; then echo "$LOG_DATE" > $LOG_FILE echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE rm -f $OUT_FILE #cat $LOG_FILE fi if [ -s $CURR_PHY_SIZE ]; then echo "$LOG_DATE" > $LOG_FILE echo "Moving $CURR_PHY_SIZE to Previous $PREV_PHY_SIZE" >> $LOG_FILE mv $CURR_PHY_SIZE $PREV_PHY_SIZE #cat $LOG_FILE fi if [ -s $CURR_LOGI_SIZE ]; then echo "$LOG_DATE" > $LOG_FILE echo "Moving $CURR_LOGI_SIZE to Previous $PREV_LOGI_SIZE" >> $LOG_FILE mv $CURR_LOGI_SIZE $PREV_LOGI_SIZE #cat $LOG_FILE fi if [ $DOWNTIME_MODE = "Y" ]; then echo "$LOG_DATE" >> $LOG_FILE echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE #cat $LOG_FILE exit fi # If there is a plan downtime then create $ORACLE_SID.down file in $DBA_SCRIPTS_HOME to silent the alerts during maintenance window. if [ -f $DBA_SCRIPTS_HOME/`echo $ORACLE_SID`.down ]; then echo "$LOG_DATE" >> $LOG_FILE echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE #cat $LOG_FILE exit fi usage() { echo "$LOG_DATE" > $LOG_FILE echo "Script To Monitor Current Database Size and its Growth Since Last Run" >> $LOG_FILE echo "Usage : ksh <script_name> <ORACLE_SID> " >> $LOG_FILE echo "For example : ksh $PROGRAM $ORACLE_SID" >> $LOG_FILE echo } if [ $# -lt 1 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then usage echo "Error : Insufficient arguments." >> $LOG_FILE #cat $LOG_FILE exit fi sqlplus -s '/as sysdba' <<EOF SET ECHO OFF SET pagesize 1000 set feedback off set lines 180 set heading off; spool $CURR_PHY_SIZE SELECT round(SUM (BYTES / (1014 * 1024 ))) "PHYSICAL_SIZE(MB)" FROM dba_data_files; SPOOL OFF; spool $CURR_LOGI_SIZE SELECT "PHYSICAL_SIZE(MB)" - "FREE_SPACE(MB)" "LOGICAL_SIZE(MB)" FROM (SELECT (SELECT round(SUM (BYTES / (1014 * 1024 ))) FROM dba_data_files) "PHYSICAL_SIZE(MB)", (SELECT round(SUM (BYTES / (1024 * 1024 ))) FROM dba_free_space) "FREE_SPACE(MB)" FROM DUAL); spool off; exit; EOF CURR_PHY_SIZE_V=`cat $CURR_PHY_SIZE` CURR_LOGI_SIZE_V=`cat $CURR_LOGI_SIZE` PREV_PHY_SIZE_V=`cat $PREV_PHY_SIZE` PREV_LOGI_SIZE_V=`cat $PREV_LOGI_SIZE` #echo $CURR_PHY_SIZE_V #echo $CURR_LOGI_SIZE_V #echo $PREV_PHY_SIZE_V #echo $PREV_LOGI_SIZE_V PHY_GROWTH=`expr $CURR_PHY_SIZE_V - $PREV_PHY_SIZE_V` #echo $PHY_GROWTH LOGI_GROWTH=`expr $CURR_LOGI_SIZE_V - $PREV_LOGI_SIZE_V` #echo $LOGI_GROWTH sqlplus -s '/as sysdba' <<EOF SET ECHO OFF SET pagesize 1000 set feedback off set lines 180 set heading on; SET MARKUP HTML ON SPOOL ON - HEAD '<title></title> - <style type="text/css"> - table { background: #eee; } - th { font:bold 10pt Arial,Helvetica,sans-serif; color:#b7ceec; background:#151b54; padding: 5px; align:center; } - td { font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding: 5px; align:center; } - </style>' TABLE "border='1' align='left'" ENTMAP OFF spool $OUT_FILE PROMPT Hi Team, PROMPT PROMPT Current Database Size - `date`: PROMPT SELECT Name "DB_NAME","PHYSICAL_SIZE(GB)", "PHYSICAL_SIZE(GB)" - "FREE_SPACE(GB)" "LOGICAL_SIZE(GB)", "FREE_SPACE(GB)" FROM (SELECT (SELECT round(SUM (BYTES / (1014 * 1024 * 1024))) FROM dba_data_files) "PHYSICAL_SIZE(GB)", (SELECT round(SUM (BYTES / (1024 * 1024 * 1024))) FROM dba_free_space) "FREE_SPACE(GB)" FROM DUAL),v\$database; PROMPT <br> PROMPT <br> PROMPT <br> PROMPT Database Growth (in MB) Since $LAST_CAPTURED_DATE_TIME select Name "DB_NAME",$PHY_GROWTH "PHYSICAL_GROWTH(MB)", $LOGI_GROWTH "LOGICAL_GROWTH(MB)" from dual,v\$database; SPOOL OFF SET MARKUP HTML OFF exit; EOF ( echo "To: $DBA_EMAIL_LIST" echo "MIME-Version: 1.0" echo "Content-Type: multipart/alternative; " echo ' boundary="PAA08673.1018277622/server.xyz.com"' echo "Subject: Report : $APPS_ID - Current Database Size and its Growth on $HOST_NAME" echo "" echo "This is a MIME-encapsulated message" echo "" echo "--PAA08673.1018277622/server.xyz.com" echo "Content-Type: text/html" echo "" cat $OUT_FILE echo "--PAA08673.1018277622/server.xyz.com" ) | /usr/sbin/sendmail -t echo "$LOG_DATE" > $LOG_FILE echo "Details sent through an email" >> $LOG_FILE #cat $LOG_FILE #Taking Backup of Output File To keep History On Server cp $OUT_FILE $OUT_FILE.`date +"%m-%b-%Y:%T"`
Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing the script.
Once the script is ready, then as per the requirement please schedule it in crontab/OEM.
Execute the Script as below: Syntax : sh <script_name> <ORACLE_SID> $ cd $HOME/DBA_MON/bin $ sh MonDBSizeGrowth.sh DEV11G
This script will send the notification with current database size and its growth since last run.
Sample Output:
Friday, November 11, 2016
Query To Monitor Archive Log Shipping and Gap Status on a Standby Database
REM +======================================================================+ REM REM File Name: archshipgapmon.sql REM REM Description: REM Query To Monitor Archive Log Shipping and Gap Status REM on a standby Database REM REM Notes: REM Usage: sqlplus "/ as sysdba" @archlogapplymon.sql REM REM REM +======================================================================+ select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') time, a.thread#, (select max(sequence#) from v$archived_log where archived='YES' and thread#=a.thread#) archived, max(a.sequence#) applied, (select max(sequence#) from v$archived_log where archived='YES' and thread#=a.thread#)-max(a.sequence#) gap from v$archived_log a where a.applied='YES' group by a.thread#;
Sample Output:
Subscribe to:
Posts (Atom)