Tuesday, March 19, 2013
OATC-StreetSmarts® coming soon!
Authors and Purveyors of OATC-StreetSmarts® guiding you down the street to success! Coming soon!
Thursday, March 7, 2013
E-Business: What Database Files (Display Database Files and Sizes)
#!/usr/bin/ksh
#whatdbfiles ()
#------------------------------------------------------------------------------#
# Module Name: whatdbfiles #
# #
# Purpose: Report on the DBA_DB_FILES #
# #
# Maintenance #
# Date Author Description #
# ----------- ------------ -------------------------------------------- #
# 10-Oct-2009 M.Barone Module design/creation #
#------------------------------------------------------------------------------#
{
DIRNAM=$(dirname $0)
ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
HNAME=$(uname -n)
#----------------------------------------------------------------------#
# TEST: Number ($#) of arguments passed to this function? #
#----------------------------------------------------------------------#
if [ $# = 0 ]
then
clear
echo
echo " #--------------------------------------------------------#"
echo " # whatdbfiles: Oracle DBA_DB_LINKS #"
echo " #--------------------------------------------------------#"
echo
echo " Connecting as SYSDBA"
sleep 2
clear
else
PASSWD=$1
clear
fi
#------------------------------------------------------------------------------#
# Initialize #
#------------------------------------------------------------------------------#
SPOOLNM=/tmp/whatdbfiles_$$.txt
#------------------------------------------------------------------------------#
# SQL*Plus ("Here-Document") #
#------------------------------------------------------------------------------#
sqlplus -s /nolog <<-END_FILE
CONNECT / AS SYSDBA
SPOOL ${SPOOLNM}
clear BREAKS
clear BUFFER
clear COLUMNS
clear COMPUTES
clear SCREEN
clear SQL
clear TIMING
set feedback off
set flush on
set heading on
set pause off
set space 1
set termout on
set verify on
set linesize 95
set pagesize 999
prompt #---------------------------------------------------------------#
prompt #- What (whatdbfiles) -#
prompt #---------------------------------------------------------------#
COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
COLUMN tablespace_name FORMAT A19 Heading 'Tablespace' WORD_WRAPPED
COLUMN file_name FORMAT A45 Heading 'DBF FileName' WORD_WRAPPED
COLUMN bytes FORMAT 99999999999 -
Heading 'Tablespace|Size(Bytes)'
COLUMN maxbytes FORMAT 9999999999 -
Heading 'Auto|Extend|Size|(Bytes)'
COLUMN autoextensible FORMAT A4 Heading 'Auto|Extd'
TTITLE LEFT DATEVAR -
RIGHT 'Page:' FORMAT 999 SQL.pno -
CENTER 'Tablespace AutoExtend DBA Report' skip 1 -
LEFT TIMEVAR -
CENTER 'Oracle Tablespace/File Name' skip 2
SELECT TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
TO_CHAR(sysdate, 'HH24:MI:SS') timevalue,
tablespace_name, file_name, bytes, maxbytes, autoextensible
FROM sys.dba_data_files
ORDER BY tablespace_name, file_name;
END_FILE
print
print "Please see /tmp/whatdbfiles_$$.txt"
print
}
#whatdbfiles ()
#------------------------------------------------------------------------------#
# Module Name: whatdbfiles #
# #
# Purpose: Report on the DBA_DB_FILES #
# #
# Maintenance #
# Date Author Description #
# ----------- ------------ -------------------------------------------- #
# 10-Oct-2009 M.Barone Module design/creation #
#------------------------------------------------------------------------------#
{
DIRNAM=$(dirname $0)
ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
HNAME=$(uname -n)
#----------------------------------------------------------------------#
# TEST: Number ($#) of arguments passed to this function? #
#----------------------------------------------------------------------#
if [ $# = 0 ]
then
clear
echo
echo " #--------------------------------------------------------#"
echo " # whatdbfiles: Oracle DBA_DB_LINKS #"
echo " #--------------------------------------------------------#"
echo
echo " Connecting as SYSDBA"
sleep 2
clear
else
PASSWD=$1
clear
fi
#------------------------------------------------------------------------------#
# Initialize #
#------------------------------------------------------------------------------#
SPOOLNM=/tmp/whatdbfiles_$$.txt
#------------------------------------------------------------------------------#
# SQL*Plus ("Here-Document") #
#------------------------------------------------------------------------------#
sqlplus -s /nolog <<-END_FILE
CONNECT / AS SYSDBA
SPOOL ${SPOOLNM}
clear BREAKS
clear BUFFER
clear COLUMNS
clear COMPUTES
clear SCREEN
clear SQL
clear TIMING
set feedback off
set flush on
set heading on
set pause off
set space 1
set termout on
set verify on
set linesize 95
set pagesize 999
prompt #---------------------------------------------------------------#
prompt #- What (whatdbfiles) -#
prompt #---------------------------------------------------------------#
COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
COLUMN tablespace_name FORMAT A19 Heading 'Tablespace' WORD_WRAPPED
COLUMN file_name FORMAT A45 Heading 'DBF FileName' WORD_WRAPPED
COLUMN bytes FORMAT 99999999999 -
Heading 'Tablespace|Size(Bytes)'
COLUMN maxbytes FORMAT 9999999999 -
Heading 'Auto|Extend|Size|(Bytes)'
COLUMN autoextensible FORMAT A4 Heading 'Auto|Extd'
TTITLE LEFT DATEVAR -
RIGHT 'Page:' FORMAT 999 SQL.pno -
CENTER 'Tablespace AutoExtend DBA Report' skip 1 -
LEFT TIMEVAR -
CENTER 'Oracle Tablespace/File Name' skip 2
SELECT TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
TO_CHAR(sysdate, 'HH24:MI:SS') timevalue,
tablespace_name, file_name, bytes, maxbytes, autoextensible
FROM sys.dba_data_files
ORDER BY tablespace_name, file_name;
END_FILE
print "Please see /tmp/whatdbfiles_$$.txt"
}
E-Business: What FreeSpace (What Database Freespace)
#!/usr/bin/ksh
#whatfreespace ()
#------------------------------------------------------------------------------#
# Module Name: whatfreespace #
# #
# Purpose: Report on the Database Freesapce. #
# #
# Maintenance #
# Date Author Description #
# ----------- ------------ -------------------------------------------- #
# 22-Jun-2010 S.Bommareddy Module design/creation #
# M.Barone Module design/creation #
#------------------------------------------------------------------------------#
{
DIRNAM=$(dirname $0)
ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
HNAME=$(uname -n)
#----------------------------------------------------------------------#
# TEST: Number ($#) of arguments passed to this function? #
#----------------------------------------------------------------------#
if [ $# = 0 ]
then
clear
echo
echo " #--------------------------------------------------------#"
echo " # whatfreespace: Oracle Database Freespace #"
echo " #--------------------------------------------------------#"
echo
#------------------------------------------------------------------#
# Trap: Set CNTL-C CNTL-D CNTL-\ Before Password Protection #
#------------------------------------------------------------------#
trap "stty echo; return" 2
trap "stty echo; return" 3
stty -echo
read PASSWD?" Please Enter the Oracle APPS Passwd: "
stty echo
echo
#------------------------------------------------------------------#
# Trap: Reset CNTL-C CNTL-D CNTL-\ After Password Protection #
#------------------------------------------------------------------#
trap 2
trap 3
clear
else
PASSWD=$1
clear
fi
#------------------------------------------------------------------------------#
# Initialize #
#------------------------------------------------------------------------------#
SPOOLNM=/tmp/whatfreespace_$$.txt
#------------------------------------------------------------------------------#
# SQL*Plus ("Here-Document") #
#------------------------------------------------------------------------------#
sqlplus -s /nolog <<-END_FILE
CONNECT APPS/$PASSWD
SPOOL ${SPOOLNM}
clear BREAKS
clear BUFFER
clear COLUMNS
clear COMPUTES
clear SCREEN
clear SQL
clear TIMING
set feedback off
set flush on
set heading on
set pause off
set space 1
set termout on
set verify on
set linesize 80
set pagesize 999
prompt #---------------------------------------------------------------#
prompt #- Oracle Database Freespace (whatfreespace) -#
prompt #---------------------------------------------------------------#
COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
COLUMN tsname FORMAT A17 HEADING "TableSpace"
COLUMN nfiles FORMAT 990 HEADING "Num|DBFs"
COLUMN nfrags FORMAT 9990 HEADING "Free|Frag"
COLUMN mxfrag FORMAT 9,999,990 HEADING "Largest|Frag (Kb)"
COLUMN usedsize FORMAT 999,999,999,990 HEADING "Used|Space(Kb)"
COLUMN freesize FORMAT 999,999,990 HEADING "Free|Space(Kb)"
COLUMN totsize FORMAT 999,999,999,990 HEADING "Total|Space(Kb)"
COLUMN pctusd FORMAT 990 HEADING "Pct|Used"
BREAK on REPORT
TTITLE CENTER '${ORASID} (${HNAME}) eBusiness Database Report ' -
LEFT DATEVAR skip 1 -
CENTER 'eBusiness 12 eBusiness Database Freespace' -
LEFT TIMEVAR skip 2
alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
COMPUTE sum of nfiles nfrags totsize usedsize freesize on report
SELECT TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
TO_CHAR(sysdate, 'HH24:MI:SS') timevalue,
total.tablespace_name tsname,
total.nfiles,
free.nfrags,
total.totsize,
(total.totsize - free.freesize) usedsize,
free.freesize,
round(((1-(free.freesize/total.totsize))*100 ),0) pctusd
FROM (select tablespace_name,
count(*) nfiles,
round((sum(bytes)/(1024)),0) totsize
FROM dba_data_files
GROUP by tablespace_name ) total,
(SELECT tablespace_name,
count(*) nfrags,
round(nvl((sum(bytes)/(1024)),0),0) freesize
FROM dba_free_space
GROUP BY tablespace_name
UNION
(SELECT tablespace_name, 0, 0 from dba_data_files
MINUS
SELECT tablespace_name, 0, 0 from dba_free_space)) free
WHERE total.tablespace_name = free.tablespace_name
AND total.tablespace_name like upper('%')
ORDER BY 7 ;
END_FILE
print
print "Please see /tmp/whatfreespace_$$.txt"
print
}
#whatfreespace ()
#------------------------------------------------------------------------------#
# Module Name: whatfreespace #
# #
# Purpose: Report on the Database Freesapce. #
# #
# Maintenance #
# Date Author Description #
# ----------- ------------ -------------------------------------------- #
# 22-Jun-2010 S.Bommareddy Module design/creation #
# M.Barone Module design/creation #
#------------------------------------------------------------------------------#
{
DIRNAM=$(dirname $0)
ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
HNAME=$(uname -n)
#----------------------------------------------------------------------#
# TEST: Number ($#) of arguments passed to this function? #
#----------------------------------------------------------------------#
if [ $# = 0 ]
then
clear
echo
echo " #--------------------------------------------------------#"
echo " # whatfreespace: Oracle Database Freespace #"
echo " #--------------------------------------------------------#"
echo
#------------------------------------------------------------------#
# Trap: Set CNTL-C CNTL-D CNTL-\ Before Password Protection #
#------------------------------------------------------------------#
trap "stty echo; return" 2
trap "stty echo; return" 3
stty -echo
read PASSWD?" Please Enter the Oracle APPS Passwd: "
stty echo
echo
#------------------------------------------------------------------#
# Trap: Reset CNTL-C CNTL-D CNTL-\ After Password Protection #
#------------------------------------------------------------------#
trap 2
trap 3
clear
else
PASSWD=$1
clear
fi
#------------------------------------------------------------------------------#
# Initialize #
#------------------------------------------------------------------------------#
SPOOLNM=/tmp/whatfreespace_$$.txt
#------------------------------------------------------------------------------#
# SQL*Plus ("Here-Document") #
#------------------------------------------------------------------------------#
sqlplus -s /nolog <<-END_FILE
CONNECT APPS/$PASSWD
SPOOL ${SPOOLNM}
clear BREAKS
clear BUFFER
clear COLUMNS
clear COMPUTES
clear SCREEN
clear SQL
clear TIMING
set feedback off
set flush on
set heading on
set pause off
set space 1
set termout on
set verify on
set linesize 80
set pagesize 999
prompt #---------------------------------------------------------------#
prompt #- Oracle Database Freespace (whatfreespace) -#
prompt #---------------------------------------------------------------#
COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
COLUMN tsname FORMAT A17 HEADING "TableSpace"
COLUMN nfiles FORMAT 990 HEADING "Num|DBFs"
COLUMN nfrags FORMAT 9990 HEADING "Free|Frag"
COLUMN mxfrag FORMAT 9,999,990 HEADING "Largest|Frag (Kb)"
COLUMN usedsize FORMAT 999,999,999,990 HEADING "Used|Space(Kb)"
COLUMN freesize FORMAT 999,999,990 HEADING "Free|Space(Kb)"
COLUMN totsize FORMAT 999,999,999,990 HEADING "Total|Space(Kb)"
COLUMN pctusd FORMAT 990 HEADING "Pct|Used"
BREAK on REPORT
TTITLE CENTER '${ORASID} (${HNAME}) eBusiness Database Report ' -
LEFT DATEVAR skip 1 -
CENTER 'eBusiness 12 eBusiness Database Freespace' -
LEFT TIMEVAR skip 2
alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
COMPUTE sum of nfiles nfrags totsize usedsize freesize on report
SELECT TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
TO_CHAR(sysdate, 'HH24:MI:SS') timevalue,
total.tablespace_name tsname,
total.nfiles,
free.nfrags,
total.totsize,
(total.totsize - free.freesize) usedsize,
free.freesize,
round(((1-(free.freesize/total.totsize))*100 ),0) pctusd
FROM (select tablespace_name,
count(*) nfiles,
round((sum(bytes)/(1024)),0) totsize
FROM dba_data_files
GROUP by tablespace_name ) total,
(SELECT tablespace_name,
count(*) nfrags,
round(nvl((sum(bytes)/(1024)),0),0) freesize
FROM dba_free_space
GROUP BY tablespace_name
UNION
(SELECT tablespace_name, 0, 0 from dba_data_files
MINUS
SELECT tablespace_name, 0, 0 from dba_free_space)) free
WHERE total.tablespace_name = free.tablespace_name
AND total.tablespace_name like upper('%')
ORDER BY 7 ;
END_FILE
print "Please see /tmp/whatfreespace_$$.txt"
}
E-Business Suite: How to Uncover Which Synonym is Invalid.
E-Business Suite: How to Uncover Which Synonym is Invalid.
While Compiling APPS Packages:
ALTER PACKAGE package-name COMPILE BODY;
ORA-00980: synonym translation is no longer valid
sqlplus APPS
prompt #---------------------------------------------------------------#
prompt #- What Synonyms are Invalid (Have No Source-Object) ?? -#
prompt #- When Compiling a Package: -#
prompt #- ORA-00980: synonym translation is no longer valid -#
prompt #- -#
prompt #- This SQL-Script will List ALL the Invalid Synonyms. -#
prompt #---------------------------------------------------------------#
COLUMN sownr FORMAT A9 HEADING 'Schema|Owner'
COLUMN sname FORMAT A29 HEADING 'Synonym Name'
COLUMN townr FORMAT A9 HEADING 'Table|Owner'
COLUMN tname FORMAT A29 HEADING 'Table Name'
COLUMN dblnk FORMAT A9 HEADING 'Table Name'
ALTER SESSION set NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
SELECT owner sownr,
synonym_name sname,
table_owner townr,
table_name tname
FROM dba_synonyms dbas
WHERE table_owner not in('SYSTEM','SYS')
AND table_owner = ('&SCHEMA_NAME')
AND db_link is null
AND not exists
(SELECT 1
FROM dba_objects dbao
WHERE dbas.table_owner=dbao.owner
AND dbas.table_name=dbao.object_name);
While Compiling APPS Packages:
ALTER PACKAGE package-name COMPILE BODY;
ORA-00980: synonym translation is no longer valid
sqlplus APPS
prompt #---------------------------------------------------------------#
prompt #- What Synonyms are Invalid (Have No Source-Object) ?? -#
prompt #- When Compiling a Package: -#
prompt #- ORA-00980: synonym translation is no longer valid -#
prompt #- -#
prompt #- This SQL-Script will List ALL the Invalid Synonyms. -#
prompt #---------------------------------------------------------------#
COLUMN sownr FORMAT A9 HEADING 'Schema|Owner'
COLUMN sname FORMAT A29 HEADING 'Synonym Name'
COLUMN townr FORMAT A9 HEADING 'Table|Owner'
COLUMN tname FORMAT A29 HEADING 'Table Name'
COLUMN dblnk FORMAT A9 HEADING 'Table Name'
ALTER SESSION set NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
SELECT owner sownr,
synonym_name sname,
table_owner townr,
table_name tname
FROM dba_synonyms dbas
WHERE table_owner not in('SYSTEM','SYS')
AND table_owner = ('&SCHEMA_NAME')
AND db_link is null
AND not exists
(SELECT 1
FROM dba_objects dbao
WHERE dbas.table_owner=dbao.owner
AND dbas.table_name=dbao.object_name);
Friday, March 1, 2013
E-Business 11i/12.1/12.2 Concurrent Request LOG & OUT Files
E-Business 11i/12.1/12.2 Concurrent Request LOG & OUT Files
Michael Barone, E-Business Suite DBA Michael.Barone@oatcinc.com
Michael Barone, E-Business Suite DBA Michael.Barone@oatcinc.com
If the environment variable $APPLCSF. is set, the OUTPUT and LOG files are created in:
$APPLCSF/$APPLLOG Log Files
$APPLCSF/$APPLOUT Output Files
Note that $APPLCSF must be a full, absolute path, and $APPLLOG and $APPLOUT are directory names.
If $APPLCSF is not set, it places the files under the product top of the application associated with the request. For example, a PO report would go under $PO_TOP/$APPLLOG and $PO_TOP/$APPLOUT.
All these directories must exist and have the correct permissions.
Note that all concurrent requests produce a log file, but not necessarily an output file.
Log file Format:
Request logfiles: l######.req
Output files Format If $APPCPNAM is not set: .
If $APPCPNAM = REQID: o.out
If $APPCPNAM = USER: .out
Note that all concurrent requests produce a log file, but not necessarily an output file.
Log file Format:
Request logfiles: l######.req
Output files Format If $APPCPNAM is not set: .
If $APPCPNAM = REQID: o.out
If $APPCPNAM = USER: .out
REQID is the request id of the concurrent request.
USER is the id of the user that submitted the request.
Custom Output file Format: APPCPNAM=USER.REQID
Manager logfiles:
ICM logfile: Default is std.mgr, can be changed with the mgrname startup parameter
Manager logfiles:
ICM logfile: Default is std.mgr, can be changed with the mgrname startup parameter
Concurrent Manager log: w.mgr
Transaction Manager log: t.mgr
Conflict Resolution Manager log: c.mgr
Conflict Resolution Manager log: c.mgr
Subscribe to:
Posts (Atom)