Oracle Apps DBA stuff

Monday, June 28, 2021

DB Parameters

Sunday, April 18, 2021

Script to Check Profile Options Related to Debugging, Tracing, and Logging in EBS 12.2

SELECT po.user_profile_option_name,

po.profile_option_name "NAME" ,

DECODE (TO_CHAR (pov.level_id), '10001', 'SITE' , '10002', 'APP', '10003', 'RESP',

'10004', 'USER', '???') "LEV",

DECODE (TO_CHAR (pov.level_id) , '10001', '', '10002', app.application_short_name ,

'10003', rsp.responsibility_key, '10004', usr.user_name, '???') "CONTEXT",

pov.profile_option_value "VALUE"

FROM fnd_profile_options_vl po,

fnd_profile_option_values pov,

fnd_user usr,

fnd_application app,

fnd_responsibility rsp

WHERE (upper(po.profile_option_name) like '%DEBUG%' or upper(po.profile_option_name) like

'%TRACE%' or upper(po.profile_option_name) like '%LOG%')

AND pov.application_id = po.application_id

AND pov.profile_option_id = po.profile_option_id

AND usr.user_id(+) = pov.level_value

AND rsp.application_id(+) = pov.level_value_application_id

AND rsp.responsibility_id(+) = pov.level_value

AND app.application_id(+) = pov.level_value

ORDER BY "NAME", pov.level_id, "VALUE"

Wednesday, March 17, 2021

verify the patches

- Verify applied patches

sqlplus / as sysdba

SELECT DISTINCT bug_number,language,creation_date

FROM apps.ad_bugs

WHERE bug_number IN ('20128107')

ORDER BY bug_number,language,creation_date

- Disable “Maintenance Mode”

On Primary Application Node:

sqlplus -s apps/`apps` @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

applying patches

adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt workers=36 logfile=u_18712060.log patchtop=/orasoft/oraApps/OEL6/ccc/R122/Add-On_Localizations/CCC/18712060 driver=u18712060.drv

Monday, March 15, 2021

Patch Applied Query

SELECT bug_number,to_char(creation_date,'dd-MON-yyyy hh24:mi:ss') FROM apps.ad_bugs WHERE bug_number IN ('10350522','12539637','20621314','16289505','16541956','22071026','5233248','10231107','5259121','15969486') GROUP BY bug_number,creation_date order by creation_date;

Saturday, January 23, 2021

Color Codes

# Reset

Color_Off='\033[0m' # Text Reset

# Regular Colors

Black='\033[0;30m' # Black

Red='\033[0;31m' # Red

Green='\033[0;32m' # Green

Yellow='\033[0;33m' # Yellow

Blue='\033[0;34m' # Blue

Purple='\033[0;35m' # Purple

Cyan='\033[0;36m' # Cyan

White='\033[0;37m' # White

# Bold

BBlack='\033[1;30m' # Black

BRed='\033[1;31m' # Red

BGreen='\033[1;32m' # Green

BYellow='\033[1;33m' # Yellow

BBlue='\033[1;34m' # Blue

BPurple='\033[1;35m' # Purple

BCyan='\033[1;36m' # Cyan

BWhite='\033[1;37m' # White

# Underline

UBlack='\033[4;30m' # Black

URed='\033[4;31m' # Red

UGreen='\033[4;32m' # Green

UYellow='\033[4;33m' # Yellow

UBlue='\033[4;34m' # Blue

UPurple='\033[4;35m' # Purple

UCyan='\033[4;36m' # Cyan

UWhite='\033[4;37m' # White

# Background

On_Black='\033[40m' # Black

On_Red='\033[41m' # Red

On_Green='\033[42m' # Green

On_Yellow='\033[43m' # Yellow

On_Blue='\033[44m' # Blue

On_Purple='\033[45m' # Purple

On_Cyan='\033[46m' # Cyan

On_White='\033[47m' # White

# High Intensity

IBlack='\033[0;90m' # Black

IRed='\033[0;91m' # Red

IGreen='\033[0;92m' # Green

IYellow='\033[0;93m' # Yellow

IBlue='\033[0;94m' # Blue

IPurple='\033[0;95m' # Purple

ICyan='\033[0;96m' # Cyan

IWhite='\033[0;97m' # White

# Bold High Intensity

BIBlack='\033[1;90m' # Black

BIRed='\033[1;91m' # Red

BIGreen='\033[1;92m' # Green

BIYellow='\033[1;93m' # Yellow

BIBlue='\033[1;94m' # Blue

BIPurple='\033[1;95m' # Purple

BICyan='\033[1;96m' # Cyan

BIWhite='\033[1;97m' # White

# High Intensity backgrounds

On_IBlack='\033[0;100m' # Black

On_IRed='\033[0;101m' # Red

On_IGreen='\033[0;102m' # Green

On_IYellow='\033[0;103m' # Yellow

On_IBlue='\033[0;104m' # Blue

On_IPurple='\033[0;105m' # Purple

On_ICyan='\033[0;106m' # Cyan

On_IWhite='\033[0;107m' # White

Friday, October 2, 2020

ORA-01652: unable to extend temp error, this may be an indication that your temporary tablespace is too small.

When Oracle throws the ORA-01652: unable to extend temp error, this may be an indication that your temporary

tablespace is too small. However, Oracle may throw that error if it runs out of space because of a one-time event, such

as a large index build. You’ll have to decide whether a one-time index build or a query that consumes large amounts

of sort space in the temporary tablespace warrants adding space.

To view the space a session is using in the temporary tablespace, run this query:

SELECT s.sid, s.serial#, s.username

,p.spid, s.module, p.program

,SUM(su.blocks) * tbsp.block_size/1024/1024 mb_used

,su.tablespace

FROM v$sort_usage su

,v$session s

,dba_tablespaces tbsp

,v$process p

WHERE su.session_addr = s.saddr

AND su.tablespace = tbsp.tablespace_name

AND s.paddr = p.addr

GROUP BY

s.sid, s.serial#, s.username, s.osuser, p.spid, s.module,

p.program, tbsp.block_size, su.tablespace

ORDER BY s.sid;

If you determine

Monday, January 20, 2020

catupgrd.sql spooled file

In the case of a manual upgrade, were there errors reported in the spooled output of catupgrd.sql?

    No:
    -----
    Proceed to check 4.

    Yes:
    -----
    Work through the following checks

The following commands can be used to scan the output file for possible problems:

grep "PL/SQL" .log
grep -i "ora-" .log
grep -i "mgr-" .log
grep -i "oci-" .log
grep -i "sp2-" .log
grep -i "sp1-" .log
grep -i "sp3-" .log
grep -i "pls-" .log
grep -i error .log
grep -i severe .log
grep -i fatal .log
grep -i fail .log
grep -i stop .log

Or you can use the following command:

egrep -i '^pl/sql|^ora-|^mgr-|^oci-|^sp3-|^sp2-|^sp1-|^pls-|^error|^severe|^fatal|^fail|^stop' .log

Thursday, December 26, 2019

How to Configure : Configure Parallel Concurrent Processing

Configure Parallel Concurrent Processing

Check prerequisites for setting up Parallel Concurrent Processing
Parallel Concurrent Processing (PCP) spans two or more nodes. If you need to add nodes, follow the relevant instructions in My Oracle Support Knowledge Document 1383621.1, Cloning Oracle Applications Release 12 with Rapid Clone.

Note: If you are planning to implement a shared Application tier file system, refer to My Oracle Support Knowledge Document 1375769.1.1, Sharing the Application Tier File System in Oracle E-Business Suite Release 12, for configuration steps. If you are adding a new Concurrent Processing node to the application tier, you will need to set up load balancing on the new application by repeating steps in Section 4.7.2.
Set Up PCP
Edit the applications context file via Oracle Applications Manager, and set the value of the variable APPLDCP to ON.

Source the Applications environment.

Execute AutoConfig by running the following command on all concurrent processing nodes:
$
$INST_TOP/admin/scripts/adautocfg.sh
Check the tnsnames.ora and listener.ora configuration files, located in $INST_TOP/ora/10.1.2/network/admin. Ensure that the required FNDSM and FNDFS entries are present for all other concurrent nodes.

Restart the Applications listener processes on each application tier node.

Log on to Oracle E-Business Suite Release 12 using the SYSADMIN account, and choose the System Administrator Responsibility. Navigate to the Install > Nodes screen, and ensure that each node in the cluster is registered.

Verify that the Internal Monitor for each node is defined properly, with correct primary node specification, and work shift details. For example, Internal Monitor: Host1 must have primary node as host1. Also ensure that the Internal Monitor manager is activated: this can be done from Concurrent > Manager > Administrator.

Set the $APPLCSF environment variable on all the Concurrent Processing nodes to point to a log directory on a shared file system.

Set the $APPLPTMP environment variable on all the CP nodes to the value of the UTL_FILE_DIR entry in init.ora on the database nodes. (This value should be pointing to a directory on a shared file system.)

Set profile option 'Concurrent: PCP Instance Check' to OFF if database instance-sensitive failover is not required. By setting it to 'ON', a concurrent manager will fail over to a secondary Application tier node if the database instance to which it is connected becomes unavailable for some reason.
Set Up Transaction Managers
Shut down the application services (servers) on all nodes

Shut down all the database instances cleanly in the Oracle RAC environment, using the command:
SQL>shutdown immediate;
Edit $ORACLE_HOME/dbs/_ifile.ora. Add the following parameters:
_lm_global_posts=TRUE
_immediate_commit_propagation=TRUE
Start the instances on all database nodes, one by one.

Start up the application services (servers) on all nodes.

Log on to Oracle E-Business Suite Release 12 using the SYSADMIN account, and choose the System Administrator responsibility. Navigate to Profile > System, change the profile option ‘Concurrent: TM Transport Type' to ‘QUEUE', and verify that the transaction manager works across the Oracle RAC instance.

Navigate to Concurrent > Manager > Define screen, and set up the primary and secondary node names for transaction managers.

Restart the concurrent managers.

If any of the transaction managers are in deactivated status, activate them from Concurrent > Manager > Administrator.
Set Up Load Balancing on Concurrent Processing Nodes
Edit the applications context file through the Oracle Applications Manager interface, and set the value of Concurrent Manager TWO_TASK (s_cp_twotask) to the load balancing alias (_balance>).

Note: Windows users must set the value of "Concurrent Manager TWO_TASK" (s_cp_twotask context variable) to the instance alias.

Execute AutoConfig by running $INST_TOP/admin/scripts/adautocfg.sh on all concurrent nodes.
Note: For further details on Concurrent Processing, refer to the Product Information Center (PIC) (Doc ID 1304305.1).

Saturday, September 28, 2019

mailer

set linesize 100
col Component format a40
SELECT component_name as Component, component_status as Status FROM fnd_svc_components WHERE component_type = 'WF_MAILER';

set linesize 150
set pagesize 9999
col COMPONENT_NAME format a50
col COMPONENT_STATUS format a50
select SC.COMPONENT_TYPE, SC.COMPONENT_NAME, FND_SVC_COMPONENT.Get_Component_Status(SC.COMPONENT_NAME) COMPONENT_STATUS from FND_SVC_COMPONENTS SC order by 1, 2;

workflow status

select fcq.USER_CONCURRENT_QUEUE_NAME Container_Name, DECODE(fcp.OS_PROCESS_ID,NULL,'Not
Running',fcp.OS_PROCESS_ID) PROCID,
fcq.MAX_PROCESSES TARGET,
fcq.RUNNING_PROCESSES ACTUAL,
fcq.ENABLED_FLAG ENABLED,
fsc.COMPONENT_NAME,
fsc.STARTUP_MODE,
fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs, APPS.FND_CONCURRENT_PROCESSES
fcp, fnd_svc_components fsc
where fcq.MANAGER_TYPE = fcs.SERVICE_ID
and fcs.SERVICE_HANDLE = 'FNDCPGSC'
and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+)
and fcq.application_id = fcp.queue_application_id(+)
and fcp.process_status_code(+) = 'A'
order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE

The following SQL script can be run in sqlplus connected as apps/ to determine the profile Option settings in the Database opposed to checking them in the Application:

The following SQL script can be run in sqlplus connected as apps/ to determine the profile Option settings in the Database opposed to checking them in the Application:

set linesize 155;

set pagesize 200;

set verify off;

col Profile format a20;

 col Level format a14;

col Value format a10;

col App format a3;

col Responsibility format a30;

col USER format a8;

col UPDATED_BY format a8;

select distinct fpo.profile_option_name Profile,

      fpov.profile_option_value Value,

      decode(fpov.level_id, 10001,'Site',

                       10002,'Application',

                       10003,'Responsibility',

                       10004,'User',

                       10005,'Server',

                       10006,'Organization')"LEVEL",

      fa.application_short_name App,

      fr.responsibility_name Responsibility,

      fu.user_name "USER"

from fnd_profile_option_values fpov,

fnd_profile_options fpo,

fnd_application fa,

fnd_responsibility_vl fr,

fnd_user fu,

fnd_logins fl

where fpo.profile_option_id=fpov.profile_option_id

and fa.application_id(+)=fpov.level_value

and fr.application_id(+)=fpov.level_value_application_id

and fr.responsibility_id(+)=fpov.level_value

and fu.user_id(+)=fpov.level_value

and fl.login_id(+) = fpov.LAST_UPDATE_LOGIN

and fpov.profile_option_value is not Null

and fpo.profile_option_name in

('APPS_FRAMEWORK_AGENT', 'WF_MAIL_WEB_AGENT', 'APPS_WEB_AGENT',

'APPS_SERVLET_AGENT', 'ICX_FORMS_LAUNCHER') order by 1,3;

https values

col PROFILE_OPTION_NAME format a60

set lines 2000 pages 2000

SELECT A.PROFILE_OPTION_NAME,B.PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTIONS A,FND_PROFILE_OPTION_VALUES B

where a.PROFILE_OPTION_ID=b.PROFILE_OPTION_ID and b.profile_option_value like 'http:/%';

dblinks

set lines 100

set pages 100

column db_link format a40

column host format a100

select distinct owner from dba_db_links;

select OWNER,DB_LINK,CREATED from dba_db_links;

select OWNER,DB_LINK,CREATED,HOST from dba_db_links;

drop database link &n;

select Host,ACL from dba_network_acls;

find xx* -type l | xargs ls –ltr

Saturday, June 29, 2019

1. Please run the following sql query in the eBiz database to check the relevant profile options and provide the sso_profiles.csv:

Could you please confirm if the same issue is encountered when application is accessed via local login?

1. Please run the following sql query in the eBiz database to check the relevant profile options and provide the sso_profiles.csv:

set echo on
set feedback on
set pagesize 400
set linesize 500
column SHORT_NAME format A40
column NAME format A50
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format A60

spool sso_profiles.csv

select p.profile_option_name SHORT_NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
'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,
'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(p.profile_option_name) in (
'APPS_SSO',
'APPS_SERVLET_AGENT',
'APPS_AUTH_AGENT',
'APPS_SSO_HINT_COOKIE_NAME',
'APPLICATIONS_HOME_PAGE',
'APPS_LOCAL_LOGIN_URL',
'APPS_PORTAL',
'APPS_PORTAL_LOGOUT',
'APPS_SSO_AUTO_LINK_USER',
'APPS_SSO_LINK_SAME_NAMES',
'APPS_SSO_ALLOW_MULTIPLE_ACCOUNTS',
'APPS_SSO_LOCAL_LOGIN',
'APPS_LOCAL_CHANGE_PWD_URL',
'APPS_SSO_CHANGE_PWD_URL',
'APPS_SSO_LDAP_SYNC',
'APPS_SSO_OID_IDENTITY',
'APPS_SSO_FORGOT_PWD_URL',
'APPS_SSO_LISTENER_TOKEN',
'APPS_DATABASE_ID',
'PASSWORD_CASE_OPTION','SIGNON_PASSWORD_CASE')
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 user_profile_option_name, level_set;

spool off

how many concurrent users do you have at peak ?

REM
REM SQL to count number of Apps users
REM Run as APPS user
REM
select 'Number of user sessions : ' || count( distinct session_id) How_many_user_sessions
from icx_sessions icx
where disabled_flag != 'Y'
and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate
and counter < limit_connects;
REM
REM
REM END OF SQL

Sunday, September 9, 2018

rman_arch_backup.sh

rman_arch_backup.sh

#!/bin/ksh
# Author : Ready
# Description : This script will perform Archive backup
# Date : 30-Aug-2012
# Parameters :
# 1: Backup location
# If no parameters givenn bckup will be taken at
# /backup/rman_9f46//oracle
#############################################################
#set -x

export DB_NAME=`echo ${ORACLE_SID} | cut -c 1-8`
export DATE=`date '+%Y%m%d'`
export TARGET_CONNECT_STR=/
export LOG_DIR=${ORACLE_HOME}/rman_scripts/logs

if [ $# -gt 1 ]; then
echo " Script Failed: Usage Error!"
echo " Expected Usage: $0 backup_location "
echo " OR "
echo " Expected Usage: $0 "
echo " backup_location -- Target location of the backup. "
echo " If no parameters given, backup will be located at /backup/rman_9f46/${DB_NAME}/oracle "
exit 1
elif [ $# -eq 1 ]; then
export BKP_DIR=$1
else
export BKP_DIR=/backup/rman_9f46/${DB_NAME}/oracle
fi

if [ -d ${LOG_DIR} ]; then
RMAN_ARCH_LOG_FILE=${LOG_DIR}/rman_${DB_NAME}_arch_${DATE}.log
else
mkdir -p ${ORACLE_HOME}/rman_scripts/logs
fi

pmon_count=`ps -ef|grep pmon|grep -c $ORACLE_SID`
if [ $pmon_count -eq 0 ]; then
echo " Database is down"
exit;
fi

mkdir -p ${BKP_DIR}/${DATE}/arch
chmod -R 770 ${BKP_DIR}/${DATE}
export bkparchdir=${BKP_DIR}/${DATE}/arch

CMD="
rman msglog ${RMAN_ARCH_LOG_FILE} append <connect target $TARGET_CONNECT_STR
crosscheck archivelog all;
RUN {

ALLOCATE CHANNEL ch00 TYPE DISK;
ALLOCATE CHANNEL ch01 TYPE DISK;
BACKUP
FORMAT '${bkparchdir}/arch-s%s-p%p-t%t-arch'
FILESPERSET 40
TAG '${DB_NAME}'
ARCHIVELOG
ALL
delete input
;

RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;

}
EOF
"

echo Script $0 > $RMAN_ARCH_LOG_FILE
echo ==== started on `date` ==== >> $RMAN_ARCH_LOG_FILE
echo >> $RMAN_ARCH_LOG_FILE

sh -c "$CMD" >> $RMAN_ARCH_LOG_FILE
RSTAT=$?

if [ "$RSTAT" = "0" ]
then
LOGMSG="Backup completed successfully"

else
LOGMSG=" Backup Failed with error."
fi

echo >> $RMAN_ARCH_LOG_FILE
echo Script $0 >> $RMAN_ARCH_LOG_FILE
echo ==== $LOGMSG on `date` ==== >> $RMAN_ARCH_LOG_FILE
echo "RMAN backup logfile $RMAN_ARCH_LOG_FILE"

RMAN FULL BACKUP

rman_db_full_backup.sh

#!/bin/ksh
# Author : DBA
# Description : This script will perform full database backup
# including Datafiles,cf files and archive files
# Date : 30-Aug-2012
# Parameters :
# 1: Backup location
# If no parameters givenn bckup will be taken at
# /backup/rman_9f46//oracle
#############################################################
#set -x
. /p01/PROD/oracle/product/12.1.0.2/PROD1_HOST01.env
export DB_NAME=`echo ${ORACLE_SID} | cut -c 1-8`
#export DATE=`date '+%Y%m%d'`
export DATE=`date '+%Y%m%d_%H%M%S'`
export TARGET_CONNECT_STR=/
export LOG_DIR=${ORACLE_HOME}/rman_scripts/logs

if [ $# -gt 1 ]; then
echo " Script Failed: Usage Error!"
echo " Expected Usage: $0 backup_location "
echo " OR "
echo " Expected Usage: $0 "
echo " backup_location -- Target location of the backup. "
echo " If no parameters given, backup will be located at /backup/rman_9f46/${DB_NAME}/oracle "
exit 1
elif [ $# -eq 1 ]; then
export BKP_DIR=$1
else
export BKP_DIR=/backup/rman_9f46/${DB_NAME}/oracle
fi

if [ -d ${LOG_DIR} ]; then
RMAN_LOG_FILE=${LOG_DIR}/rman_${DB_NAME}_${DATE}.log
#snapshot_path=${LOG_DIR}/snapcf_${DB_NAME}_${DATE}.f
else
mkdir -p ${ORACLE_HOME}/rman_scripts/logs
#snapshot_path=${LOG_DIR}/snapcf_${DB_NAME}_${DATE}.f
fi

#snapshot_set=$(echo " set snapshot controlfile name to @" $snapshot_path "@;" |sed -e s/@\ /\ \'/g -e s/\ \@/\'/)

pmon_count=`ps -ef|grep pmon|grep -c $ORACLE_SID`
if [ $pmon_count -eq 0 ]; then
echo " Database is down"
exit;
fi

mkdir -p ${BKP_DIR}/${DATE}/db
chmod -R 770 ${BKP_DIR}/${DATE}
export bkpdbdir=${BKP_DIR}/${DATE}/db

CMD="

rman msglog ${RMAN_LOG_FILE} append <connect target $TARGET_CONNECT_STR

${snapshot_set}

RUN {

sql 'alter system archive log current';
# Backup Datafiles
ALLOCATE CHANNEL ch00 TYPE DISK;
ALLOCATE CHANNEL ch01 TYPE DISK;
ALLOCATE CHANNEL ch02 TYPE DISK;
ALLOCATE CHANNEL ch03 TYPE DISK;
ALLOCATE CHANNEL ch04 TYPE DISK;
ALLOCATE CHANNEL ch05 TYPE DISK;
ALLOCATE CHANNEL ch06 TYPE DISK;
ALLOCATE CHANNEL ch07 TYPE DISK;
BACKUP AS COMPRESSED BACKUPSET DATABASE FILESPERSET 15 FORMAT '$bkpdbdir/%d_db_u%u_s%s_p%p_t%t_db' TAG '$DB_NAME' INCLUDE CURRENT CONTROLFILE;

# Restore Point

sql 'create restore point ${DB_NAME}_${DATE}';
# For an offline backup, remove the following sql statement
sql 'alter system archive log current';

# Backup Archived Logs

BACKUP ARCHIVELOG ALL DELETE INPUT FORMAT '$bkpdbdir/arch-s%s-p%p-t%t-arch';
# Control file backup

BACKUP CURRENT CONTROLFILE FORMAT '$bkpdbdir/bk_u%u_s%s_p%p_t%t_bk' TAG '$DB_NAME';

RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
RELEASE CHANNEL ch04;
RELEASE CHANNEL ch05;
RELEASE CHANNEL ch06;
RELEASE CHANNEL ch07;
}
EOF
"

echo Script $0 > $RMAN_LOG_FILE
echo ==== started on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE

sh -c "$CMD" >> $RMAN_LOG_FILE
RSTAT=$?

if [ "$RSTAT" = "0" ]
then
LOGMSG="Backup completed successfully"
(echo "${DB_NAME} Backup Sucessful"; uuencode $RMAN_LOG_FILE $RMAN_LOG_FILE)| mailx -s "${DB_NAME} Backup Sucessful" oracle@XX.com
else
LOGMSG=" Backup Failed with error."
(echo "${DB_NAME} Backup Failed please refer log file"; uuencode $RMAN_LOG_FILE $RMAN_LOG_FILE)| mailx -s "${DB_NAME} Backup Failed" oracle@oracle.com
fi

echo >> $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE
echo "RMAN backup logfile $RMAN_LOG_FILE"

EBS Sanity Script

SQL>conn apps/r0ck#5st3er
SQL>spool health_check_apps_db_JT1.txt

set pages 1000
set linesize 135
col PROPERTY_NAME for a25
col PROPERTY_VALUE for a15
col DESCRIPTION for a35
col DIRECTORY_PATH for a70
col directory_name for a25
col OWNER for a10
col DB_LINK for a40
col HOST for a20
col "User_Concurrent_Queue_Name" format a50 heading 'Manager'
col "Running_Processes" for 9999 heading 'Running'
set head off
set feedback off
set echo off

break on utl_file_dir

select '--------------------------------------------------------------------------------' from dual;
select '----------------------- Database Checks ---------------------------------' from dual;
select '--------------------------------------------------------------------------------' from dual;
Prompt
select '************************ Getting Database Information *************' from dual ;

select 'Database Name..................... : '||name from v$database;
select 'Database Status................... : '||open_mode from v$database;
select 'Archiving Status.................. : '||log_mode from v$database;
select 'Global Name....................... : '||global_name from global_name;
select 'Creation Date..................... : '||to_char(created,'DD-MON-YYYY HH24:MI:SS') from v$database;
select 'Checking For Missing File......... : '||count(*) from v$recover_file;
select 'Checking Missing File Name ....... : '||count(*) from v$datafile where name like '%MISS%';
select 'Total SGA ........................ : '||round(sum(value)/(1024*1024))||' MB' from v$sga ;
select 'Database Version.................. : '||version from v$instance;
select 'Temporary Tablespace.............. : '||property_value from database_properties
where property_name like 'default_temp_tablespace';
select 'Apps Temp Tablespace.............. : '||temporary_tablespace from dba_users where username like '%APPS%';
select 'Temp Tablespace size.............. : '||sum(maxbytes/1024/1024/1024)||' GB' from dba_temp_files group by tablespace_name;
select 'No of Invalid Object ............. : '||count(*) from dba_objects where status = 'INVALID' ;
select 'service Name...................... : '||value from v$parameter2 where name='service_names';
select 'plsql code type................... : '||value from v$parameter2 where name='plsql_code_type';
select 'plsql subdir count................ : '||value from v$parameter2 where name='plsql_native_library_subdir_count';
select 'plsql native library dir.......... : '||value from v$parameter2 where name='plsql_native_library_dir';
select 'Shared Pool Size.........,........ : '||(value/1024/1024) ||' MB' from v$parameter where name='shared_pool_size';
select 'Log Buffer........................ : '||(value/1024/1024) ||' MB' from v$parameter where name='log_buffer';
select 'Buffer Cache...................... : '||(value/1024/1024) ||' MB' from v$parameter where name='db_cache_size';
select 'Large Pool Size................... : '||(value/1024/1024) ||' MB' from v$parameter where name='large_pool_size';
select 'Java Pool Size.................... : '||(value/1024/1024) ||' MB' from v$parameter where name='java_pool_size';
select 'utl_file_dir...................... : '||value from v$parameter2 where name='utl_file_dir';
select directory_name||'.................... : '||directory_path from all_directories where rownum < 15 ;

select '************************ Getting Apps Information *****************' from dual ;

select 'Home URL.......................... : '||home_url from apps.icx_parameters ;
select 'Session Cookie.................... : '||session_cookie from apps.icx_parameters ;
select 'Applicaiton Database ID........... : '||fnd_profile.value('apps_database_id') from dual;
select 'GSM Enabled....................... : '||fnd_profile.value('conc_gsm_enabled') from dual;
select 'Maintainance Mode................. : '||fnd_profile.value('apps_maintenance_mode') from dual;
select 'Site Name......................... : '||fnd_profile.value('Sitename')from dual;
select 'Bug Number........................ : '||bug_number from ad_bugs where bug_number='2728236';

select '************************ Doing Workflow Checks ********************' from dual ;

select 'No Open Notifications............. : '||count(*) from wf_notifications where mail_status in('MAIL','INVALID','OPEN');
select 'Name(wf_systems).................. : '||name from wf_systems;
select 'Display Name(wf_systems).......... : '||display_name from wf_systems;
select 'Address........................... : '||address from wf_agents;
select 'Workflow Mailer Status............ : '||component_status from applsys.fnd_svc_components
where component_name like 'Workflow Notification Mailer';
select 'Test Address...................... : '||b.parameter_value
from fnd_svc_comp_param_vals_v a, fnd_svc_comp_param_vals b
where a.parameter_id=b.parameter_id
and a.parameter_name in ('TEST_ADDRESS');
select 'From Address...................... : '||b.parameter_value
from fnd_svc_comp_param_vals_v a, fnd_svc_comp_param_vals b
where a.parameter_id=b.parameter_id
and a.parameter_name in ('FROM');
select 'WF Admin Role..................... : '||text from wf_resources where name = 'WF_ADMIN_ROLE' and rownum =1;

Prompt
Prompt Getting Apps Node Info
Prompt ************************
select Node_Name,'........................ : '||server_id from fnd_nodes;
select server_type||'......................: '||name from fnd_app_servers, fnd_nodes
where fnd_app_servers.node_id =fnd_nodes.node_id;

select '************************ Doing Conc Mgr Checks ********************' from dual ;

Prompt Getting Con Mgr Status
Prompt ************************
Prompt
Prompt Manager Name Hostname No of Proc Running
Prompt ~~~~~~~~~~~~ ~~~~~~~~ ~~~~~~~~~~~~~~~~~~
set lines 145
Column Target_Node Format A12
select User_Concurrent_Queue_Name,'....... : '||Target_Node||' ...... : '||Running_Processes
from fnd_concurrent_queues_vl
where Running_Processes = Max_Processes
and Running_Processes > 0;

Prompt
Prompt Getting Pending Request
Prompt ***********************
--select user_concurrent_program_name||'........ : '||request_id
-- from fnd_concurrent_requests r, fnd_concurrent_programs_vl p, fnd_lookups s, fnd_lookups ph
-- where r.concurrent_program_id = p.concurrent_program_id
-- and r.phase_code = ph.lookup_code
-- and ph.lookup_type = 'CP_PHASE_CODE'
-- and r.status_code = s.lookup_code
-- and s.lookup_type = 'CP_STATUS_CODE'
-- and ph.meaning ='Pending'
-- and rownum < 10
-- order by to_date(actual_start_date, 'dd-MON-yy hh24:mi');
--

Prompt
Prompt Getting Workflow Components Status
Prompt **********************************

set pagesize 1000
set linesize 125
col COMPONENT_STATUS for a20
col COMPONENT_NAME for a45
col STARTUP_MODE for a12

select fsc.COMPONENT_NAME,
fsc.STARTUP_MODE,
fsc.COMPONENT_STATUS,
fcq.MAX_PROCESSES TARGET,
fcq.RUNNING_PROCESSES ACTUAL
from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs,
APPS.FND_CONCURRENT_PROCESSES fcp, fnd_svc_components fsc
where fcq.MANAGER_TYPE = fcs.SERVICE_ID
and fcs.SERVICE_HANDLE = 'FNDCPGSC'
and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+)
and fcq.application_id = fcp.queue_application_id(+)
and fcp.process_status_code(+) = 'A'
order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE;

select '--------------------------------------------------------------------------------' from dual;
select '----------------------- End Of Database Checks ----------------------------' from dual;
select '--------------------------------------------------------------------------------' from dual;

SQL>spool off

how to decrypt the weblogic password

HI All,

To decrypt the WebLogic password follow the below steps

1)Take the adminserver boot. Properties details

[applmgr@host1 security]$ cat $EBS_DOMAIN_HOME/servers/AdminServer/security/boot.properties
#Sun May 08 17:51:57 EDT 2016
password={AES}RL4vuk2Y1rreNBi0EmKNt0x8zY10ckmKxmv+j64CGak\=
username={AES}YOyAsoH6TA9BvK2qxjayQh3NvkQ4W3/3pygLNc4vWUM\=
[applmgr@host1 security]$

2)create decrypt.py file in

[applmgr@host1 security]$ cd $EBS_DOMAIN_HOME/security

[applmgr@host1 security]$ cat decrypt.py
from weblogic.security.internal import *
from weblogic.security.internal.encryption import *
encryptionService = SerializedSystemIni.getEncryptionService(".")
clearOrEncryptService = ClearOrEncryptedService(encryptionService)

# Take encrypt password from user
pwd = raw_input("Paste encrypted password ({AES}fk9EK...): ")

# Delete unnecessary escape characters
preppwd = pwd.replace("\\", "")

# Display password
print "Decrypted string is: " + clearOrEncryptService.decrypt(preppwd)
[applmgr@host1 security]$

[applmgr@host1 security]$ pwd
/erppwrc1/erpapp/fs2/FMW_Home/user_projects/domains/EBS_domain_erppwrc1/security

3) source the setDomainEnv.sh

[applmgr@host1 security]$ cd $EBS_DOMAIN_HOME/bin
[applmgr@host1 bin]$ ls -ltr
total 56
drwxr-x--- 2 applmgr oinstall 4096 May 7 15:29 service_migration
drwxr-x--- 2 applmgr oinstall 4096 May 7 15:29 server_migration
drwxr-x--- 2 applmgr oinstall 4096 May 7 15:29 nodemanager
-rwxr-x--- 1 applmgr oinstall 2010 May 7 15:29 secureWebLogic.sh
-rwxr-x--- 1 applmgr oinstall 2003 May 7 15:29 stopWebLogic.sh
-rwxr-x--- 1 applmgr oinstall 2473 May 7 15:29 stopManagedWebLogic.sh
-rwxr-x--- 1 applmgr oinstall 5704 May 7 15:29 startWebLogic.sh
-rwxr-x--- 1 applmgr oinstall 3251 May 7 15:29 startManagedWebLogic.sh
-rwxr-x--- 1 applmgr oinstall 17349 May 7 15:29 setDomainEnv.sh
[applmgr@host1 bin]$. ./setDomainEnv.sh

4)run the decrypt password script

[applmgr@host1 security]$ cd $EBS_DOMAIN_HOME/security
[applmgr@host1 security]$ ls -ltr
total 40
-rw-r----- 1 applmgr oinstall 486 May 7 15:29 decrypt.py
-rw-r----- 1 applmgr oinstall 22654 May 7 15:29 XACMLRoleMapperInit.ldift
-rw-r----- 1 applmgr oinstall 64 May 7 15:29 SerializedSystemIni.dat
-rw-r----- 1 applmgr oinstall 2398 May 7 15:29 DefaultRoleMapperInit.ldift
-rw-r----- 1 applmgr oinstall 3301 May 8 17:50 DefaultAuthenticatorInit.ldift
[applmgr@host1 security]$

[applmgr@host1 security]$ java weblogic.WLST decrypt.py

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

Paste encrypted password ({AES}fk9EK...): {AES}RL4vuk2Y1rreNBi0EmKNt0x8zY10ckmKxmv+j64CGak\=
Decrypted string is: weblogic123