Be Apps DBA In 1 Day
1. Introduction
The scope of this document is limited to the explaining the need for managing E-Business Suite 11i Environment.
This document can be used by Oracle Application Database Administrators working with
Oracle Database (9i) and also with Oracle E-Business Suite Environment.
2. How To Use ?
This document can be used as quick reference for Apps DBA as a quick reference,
Can also be used to train fresher to give the basics of Apps DBA Functionality.
3. Details
This Document Consist of -
Oracle Apps Configuration File, Scripts to start different services, Profile Options
and Different AD Utilities.
One of main Component of E-Business Suite, Work Flow and Notification Mailer .
Oracle Database commonly used Database Views, Dynamic Performance Views,
Initialization Parameter and Accessing utility help.
Last but not the least is Visual Editor Command and Common Unix command,which works on most of Unix flavors.
11.5.9 Key Configuration Files – App (8.0.6.3)
(Applications Administrator Role)
Filename
SID
Path
Port
Host
$ORACLE_HOME/SID_HOST.env · · - -
$ORACLE_HOME/forms60_server - · · -
$ORACLE_HOME/reports60/server/CGIcmd.dat · - - -
$ORACLE_HOME/reports60/server/REP60_SID.ora - · - -
$ORACLE_HOME/tools/web60/html/runform.htm - - · ·
$ORACLE_HOME/tools/web60/html/runrep.htm - - · ·
$TNS_ADMIN/listener.ora · · · ·
$TNS_ADMIN/tnsnames.ora · - · ·
$APPL_TOP/APPSORA.env · · - -
$APPL_TOP/APPSSID_HOST.env · · - -
$APPL_TOP/SID.env · · · -
$APPL_TOP/SID_HOST.env · · · -
$APPL_TOP/admin/adconfig.txt · · - -
$APPL_TOP/admin/adovars.env - · - -
$APPL_TOP/admin/topfile.txt - · - -
$FND_TOP/secure/HOST_SID.dbc · - · ·
$COMN_TOP/admin/scripts/SID_HOST/adalnctl.sh · · - -
$COMN_TOP/admin/scripts/SID_HOST/adautocfg.sh - · - -
$COMN_TOP/admin/scripts/SID_HOST/adcmctl.sh · · - ·
$COMN_TOP/admin/scripts/SID_HOST/addisctl.sh - · - -
$COMN_TOP/admin/scripts/SID_HOST/adfrmctl.sh · · · -
$COMN_TOP/admin/scripts/SID_HOST/adrepctl.sh · · · -
$COMN_TOP/admin/scripts/SID_HOST/adstpall.sh - · - -
$COMN_TOP/admin/scripts/SID_HOST/adstrtal.sh - · - -
$COMN_TOP/admin/scripts/SID_HOST/gsmstart.sh - · - ·
$COMN_TOP/admin/install/SID_HOST/adgendbc.sh · · · ·
$COMN_TOP/portal/aplogon.html · - · ·
$COMN_TOP/portal/applist.html · · · ·
$FND_TOP/resource/appsweb.cfg · · · ·
$OA_HTML/bin/appsweb_SID_HOST.cfg · · · ·
$OA_HTML/US/ICXINDEX.htm · - - -
11.5.9 Key Configuration Files – Web/iAS (1.0.2.2.2)
(Web Administrator Role)
Filename
SID
Path
Port
Host
$ORACLE_HOME/SID_HOST.env · · - -
$TNS_ADMIN/tnsnames.ora · - · ·
$ORACLE_HOME/Apache/Apache/conf/httpds.conf - · · ·
$ORACLE_HOME/Apache/Apache/conf/apps.conf · · - -
$ORACLE_HOME/Apache/Apache/conf/oracle_apache
.conf
- · - -
$ORACLE_HOME/Apache/Jserv/etc/jserv.conf - · · ·
$ORACLE_HOME/Apache/Jserv/etc/jserv.properties - · · ·
$ORACLE_HOME/Apache/Jserv/etc/zone.properties - · - -
$ORACLE_HOME/Apache/Jserv/etc/ssp_init.txt · · · ·
$ORACLE_HOME/Apache/Apache/bin/apachectl - · · -
$ORACLE_HOME/Apache/modplsql/cfg/plsql.conf - · - -
$ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app - · - -
$COMN_TOP/admin/scripts/SID_HOST/adapcctl.sh · · - -
Forms Environment Variables
Parameter Default Recommendation
FORMS60_TIMEOUT None 55 (minutes)
FORMS60_CATCHTERM None 0
11.5.9 Key Configuration Files – DB (8.1.7.4/9.2.0.4*)
(Oracle DBA Role)
Filename
SID
Path
Port
Host
$ORACLE_HOME/SID_HOST.env · · - -
$ORACLE_HOME/dbs/init.ora · · - -
$ORACLE_HOME/dbs/initSID.ora · · - -
$ORACLE_HOME/SID_HOST_ifile.ora ** ** ** **
$ORACLE_HOME/rdbms/lib/nmliblist - · - -
$ORACLE_HOME/rdbms/lib/config.s *** - - -
$TNS_ADMIN/listener.ora · · · ·
$TNS_ADMIN/tnsnames.ora · - · ·
$TNS_ADMIN/protocol.ora · - · ·
$ORACLE_HOME/appsutil/scripts/SID_HOST/addbctl.sh · · - -
$ORACLE_HOME/appsutil/scripts/SID_HOST/addlnctl.sh · · - -
$ORACLE_HOME/appsutil/scripts/SID_HOST/adautocfg - · - -
* Recommended
** Custom initialization parameters
*** Contains owner and group information
11.5.9 Key Site Profile Options
Filename
SID
Path
Port
Host
Application Framework Agent · ·
Applications Web Agent · · ·
Apps Servlet Agent · ·
ICX: Discoverer Launcher · ·
ICX: Discoverer Viewer Launcher · ·
ICX: Forms Launcher · ·
ICX: Report Images · ·
ICX: Report Launcher · ·
ICX: Report Link · · ·
ICX: Report Cache · ·
ICX: Requisition Server · ·
JTF_BIS_OA_HTML · ·
TCF: HOST · ·
Application Profile Values Relating to User Sign On
Profile Default Recommendation
Signon Password Failure Limit None 3 (attempts)
Signon Password Hard to Guess No Yes
Signon Password Length 5 7 (characters)
Signon Password No Reuse None 180 (days)
Signon Password Custom None *1
Sign-On:Notification No Yes *2
Application Profile Values Relating to ICX
Parameter Default Recommendation
ICX:Session Timeout none 30 (minutes)
ICX: Limit Time 4 (hours) 10 (hours)
ICX: Limit Connect 1000 1000
Jserv (Java) Settings
Parameter Default Recommendation
disco4iviewer.properties:session.timeout None 5400000 (milliseconds)
formservlet.ini:FORMS60_TIMEOUT None 55 (minutes)
formservlet.properties:session.timeout None 5400000 (milliseconds)
jserv.conf:ApJServVMTimeout None 360 (seconds)
mobile.properties:session.timeout None 5400000 (milliseconds)
zone.properties:session.timeout None 5400000 (milliseconds)
zone.properties:servlet.framework.initArg None 5400000 (milliseconds)
Apache HTTP Settings
Parameter Default Recommendation
httpd.conf:Timeout None 300 (seconds)
httpd.conf:KeepAliveTimeout None 15 (seconds)
httpd.conf:SSLSessionCacheTimeout None 300 (seconds)
Start / Stop Scripts and UNIX Check Commands
Database:
$ORACLE_HOME/appsutil/scripts/SID_HOST/addbctl.sh
ps –ef | grep _SID
Database Listener:
$ORACLE_HOME/appsutil/scripts/SID_HOST/addlnctl.sh
ps -ef | grep 8.1.7 | grep tnslsnr | grep SID
Apps Listener:
$COMN_TOP/admin/scripts/SID_HOST/adalnctl.sh
ps -ef | grep 8.0.6 | grep tnslsnr
Concurrent Managers:
$COMN_TOP/admin/scripts/SID_HOST/adcmctl.sh
ps –ef | grep CPMGR
Reports Server:
$COMN_TOP/admin/scripts/SID_HOST/adrepctl.sh
ps –ef | grep rwmts60
TCF Server:
(Started by jserv )
ps –ef | grep /util/jre or ??
Forms Server:
$COMN_TOP/admin/scripts/SID_HOST/adfrmctl.sh
ps –ef | grep f60srvm
Forms Metric Server:
$COMN_TOP/admin/scripts/SID_HOST/adfmsctl.sh
ps –ef | grep d2ls60
Forms Metric Client:
$COMN_TOP/admin/scripts/SID_HOST/adfmcctl.sh
ps –ef | grep d2lc60
Apache:
$COMN_TOP/admin/scripts/SID_HOST/adapcctl.sh
ps –ef | grep Apache
Site Certificate Files And Scripts
$HOME/identitydb.obj
$APPL_TOP/admin/appltop.cer
$APPL_TOP/admin/adsign.txt
$APPL_TOP/admin/adcert.txt
$APPL_TOP/admin/adjbuild.sh
$APPL_TOP/admin/adjkey.sh
$COMN_TOP/html/oajinit.exe
Default Port Numbers (Port Pool = 0)
Database Listener 1521 JTF Fulfillment Server 9300
RPC 1626 Map Viewer Servlet 9800
Reports Server 7000 OEM Web Utility 10000
Web Server 8000 Visibroker OrbServer Agent 10100
OprocMgr 8100 MSCA Server 10200
Web PL/SQL 8200 MSCA Dispatcher 10300
Servlet 8800 OACORE Servlet Range 16000 to
16009
Forms Listener 9000 Discoverer Servlet Range 17000 to
17009
Metrics Server Data 9100 Forms Servlet Range 18000 to
18019
Metrics Server Request 9200 XML SVCS Servlet Range 19000 to
19009
Be Apps DBA In 1 Day
4 of 11
AD Utilities
The AD utilities are a group of tools designed to install, upgrade,
maintain, and patch a specific set of products contained in a given release
of Oracle Applications.
AD Administration (adadmin)*
AD Administration (adadmin) performs maintenance tasks on an installed
Oracle Applications system. The tasks performed with this utility fall
into two categories: database and file system. On the AD Administration
screens, you can choose tasks from three menus:
1) Generate Applications Files Menu
- Relink Applications programs
- Generate message files
- Generate form files
- Generate report files
- Generate graphics files
- Generate product JAR files
2) Maintain Applications Database Objects Menu
- Validate APPS schema(s)
- Compile APPS schema(s)
- Compile menu information
- Recreate grants and synonyms for APPS schema(s)
- Compile flexfield data in AOL tables
- Maintain multi-lingual tables
- Check DUAL table
- Reload JAR files to database
- Maintain Multiple Reporting Currencies schema
- Convert to MultiOrg or Multiple Reporting Currencies
3) Maintain Applications Files Menu
- Create Applications environment file
- Copy files to destinations
- Convert character set
- Maintain snapshot information
- Check for missing files
AutoUpgrade (adaimgr)
AutoUpgrade is the utility that is run after completion of the basic
installation tasks with Rapid Install to perform tasks such as updating
database objects or converting to industry-specific modules.
AutoPatch (adpatch)
AutoPatch is used to apply individual patches, mini-packs, maintenance
packs, or consolidated update patches. A mini-pack is a cumulative
collection of individual patches for a product, while a maintenance pack
is a collection of mini-packs for all Applications products.
AD Merge Patch (admrgpch)
This utility merges multiple AutoPatch compatible patches into a single
integrated patch. It will not merge patches of different releases or other
incompatibilities.
AD Controller (adctrl)
AD Controller is used in conjunction with AD Administration,
AutoUpgrade, and AutoPatch to determine the status of AD utilities
workers and restart failed AD utilities tasks.
File Character Set Conversion (adncnv)
File Character Set Conversion is used to convert the character set of
unloaded files. File character set conversion for files is normally done
automatically by the Rapid Install and by AutoPatch.
*AD.I will affect all attributes
AD Configuration (adutconf.sql)
This SQL script reports standard information about the installed
configuration of Oracle Applications. It generates a file called adutconf.lst
that provides information about product groups, MultiOrg and Multiple
Reporting Currency (MRC) installations, as well as installed products.
AD File Identification (adident)
With AD File Identification, you can identify the version of one or more
Oracle Applications files. This utility is useful for collecting information
about your site when contacting Oracle Support Services.
AD Splicer (adsplice)
Use AD Splicer to install products that were not included in the current
Oracle Applications release. It modifies your APPL_TOP and database so
AutoPatch and AD Administration can recognize the new product as being
valid.
ODF Comparison (adodfcmp)
Use ODF Comparison to compare, and/or modify selected data model
building blocks, within the database, with the standard data model from the
current release of Oracle Applications.
AD Relink (adrelink.sh)
Use this utility to relink AD utility executable programs with the Oracle
Server product libraries. For relinking product modules other than AD, use
adadmin.
DataMerge (addmimp)
Similar to the Oracle import utility, this utility runs during an upgrade to
import seed data needed by Oracle Applications.
License Manager (adlicmgr.sh)
Use the Oracle Applications License Manager to license new additional
products or languages to your Oracle Applications installation.
Build Javakey Files (adjkey)
This is an Oracle provided executable that is a wrapper around Sun's
javakey program. It is used to create the digital certificate files adsign.txt,
appltop.cer, and identitydb.obj (required post install step in versions prior to
11.5.7).
Repackage JInitiator (adjbuild.sh)
This is an Oracle provided shell script that is used to repackage JInitiator
and the certificate into a self-extracting executable (oajinit.exe).
Auto Config (adconfig.sh)
This script is used to rebuild all instance specific scripts, configuration files,
login profiles, etc associated with the application environment. Used when
cloning one application environment to another or after applying a patch
that requires one or more configuration files to be regenerated.
Applications Context (SID_HOST.xml)
The Applications Context file is an XML formatted file that contains all
application specific configurations. Used by adconfig.sh when rebuilding
the application configuration files and related scripts.
Build Applications Context (adbldxml.sh)
This script is used to build the Applications Context file. Reads information
from Rapidwiz Configuration file.
Rapidwiz Configuration (config.txt)
The Rapidwiz Configuration file is created during initial installation when
Rapid Install is first ran. It contains all instance specific configuration
information such as hostname, instance name, port numbers, etc.
Reset Application Profiles (afadmprf.sh, afcmprf, affrmprf.sh,
afwebprf.sh)
These shell scripts are used to reset all application profiles stored in the
database to installation default values.
Be Apps DBA In 1 Day
5 of 11
Setup – 11.5.9 – Required
Set Global Preferences (must be the workflow
administrator; the seeded administrator is * or
SYSADMIN user)
Workflow Administrator – change to different user or
responsibility
Send Me Electronic Mail Notifications – set to ‘Do not
send me mail’ until Notification Mailer is set up, then
‘HTML mail with attachments’; individuals can be set
to ‘Plain Text Summary Mail’, but requires Summary
Mailer to be configured
Jinitiator parameters – sync with appsweb.cfg (prepatch
2411267)
Verify Directory Services
Run WFDIRCHK.sql ($FND_TOP/sql) – each query
should return ‘No Rows’; fix identified problems;
remove from WF_LOCAL_ROLES,
WF_LOCAL_USER_ROLES all records with
partition_id = 0 and Orig_system like ‘FND_RESP%’
or = ‘FND_USR’ or = ‘PER’
Install Bulk Sync patches or schedule ‘Syncronize WF
LOCAL tables’ for Original Systems not covered by
patches
Profile Options
Socket Listener Port – (at site level) set to port at which
forms should launch
FND: Notification Reassign Mode – controls whether
notifications can be delegated (Delegate), transferred
(Transfer), or both (Reassign)
Schedule Background Engine
Schedule ‘Workflow Background Process’ to run
periodically – if only run one copy, leave Item Type,
Minimum Threshold, Maximum Threshold blank,
answer ‘Yes’ for Process Deferred, Process Timeout,
Process Stuck
Start Containers for Mailer/Events (Mailer only
required if using Notification Mailer)
Login OAM, Navigate to Workflow Manager
Click Icon Next to Service Components
Click on Container Name
Click Go next to Start All – verify status Activated
Setup Notification Mailer (Only required if using
email for notifications) – done through OAM; steps
below will allow use of SendMail as Mailer
Install IMAP addon – either UW IMAP or Cyrus IMAP
Verify SendMail Setup – make sure in home directory
for applmgr and execute the following:
ls -al.forward – if exists, delete it
$which sendmail – if no path returned, add SendMail
executable folder to $PATH
$sendmail
– sends email to address specified; verify message
received and respond to message
$mailx – (Linux command may be $mail) – locate
message, then type ‘Quit’
create 3 files at location readable/writeable by
applmgr – files are Inbox, Processes, Discard;
usual directory is /var/mail or /home/mail (note full
paths for later steps)
ls -al
permissions (-rw-rw----)
If wfmlrwf.tmp exists, owner must be applmgr
Verify permissions on tmp directory (drwxrwxrwt)
Verify applmgr exists in SendMail users directory,
owner is applmgr, permissions (-rw-rw----) –
Applmgr account is now restricted to workflow use
only, system monitoring emails must use different
account
OAM Setups
Login to OAM, Navigate to Workflow Manager
Click Icon Next to Notification Mailers
Click Edit, then Next until get to Page 3
Inbound Email Account – enter the following:
Server Name
User Name – applmgr account
Password – applmgr password
Inbox – full path (from above) of Inbox file
Outbound Email Account – enter the following:
Server Name – may already be filled in
Email Processing – enter the following:
Processed Folder – full path of Processed file
Discard Folder – full path of Discard file
Click Next – if parameters correct, page 4 appears
Reply-to Address –
(Optional) – click ‘Autoclose’ to have FYI
notifications close when sent
Click Next – page 5 appears
(Optional) – Schedule Summary mailer time of day
and re-start interval (in minutes)
Click Next – page 6 appears
(Optional) – add custom tags
Click Next – page 7 appears
Select Role to send test message, click Accept – role
must be user with email address
Respond to Email – use View Log page on test page,
verify your message moved to Processed file
Click Next, click Finish, verify status is Running
Setup Business Event System
Setup database links to external systems – only required
if sending/receiving messages outside local system
Check Event Manager Setup – Verify/Change
Aq_tm_processes, Job_queue_processes,
job_queue_interval (Oracle8i only) >= minimums; if
not, change init.ora file
Check Event Manager Setup – schedule propagation
for outbound agents WF_OUT, WF_JMS_OUT
(ECX_OUTBOUND also required if using EDI)
Click Create next to each Out Agent – recommended
parameter values are Duration=30, Run every 60
seconds, Latency = 0
Schedule ‘Workflow Agent Listener’ for WF_IN,
WF_JMS_IN, WF_REPLAY_IN (ECX_INBOUND
also required if using EDI) – recommended
parameter values are Correlation ID = NULL, AQ
Wait Period = 0; ensure program resubmits
periodically
Run ‘Synchronize Product License and Workflow BES
License’
Ensure ‘Workflow Control Cleanup’ scheduled at least
once a day
Verify system by launching ‘Workflow Agent Ping/
Acknowledge’ workflow with process ‘Master Ping
Process’
Setup – 11.5.9 – Optional
Setup Additional Languages – server side done on
install
Preferences Responsibility – Set Language for User
NLS_LANG environment variable on PC – use
regedit32 to set NSL_LANG at
HKEY_LOCAL_MACHINE/
SOFTWARE/ORACLE; format is
LANGUAGE_TERRITORY.CHARSET
Partition Tables – increases performance
Backup tables
Run ‘Purge Workflow Runtime Data’
$FND_TOP/admin/sql/wFUPARTB.sql
Add Worklist Function to User Responsibilities so
they can view progress of their workflows
Add Notification Rules to User / Admin
Responsibilities
Customize Logo – replace FNDLOGOS.gif
Add Custom Icons – desktop .ico, server .gif
Modify Message Templates
Setup WF_EVENT_OMB_QH queue handler to
propagate messages between systems (Oracle8i)
Be Apps DBA In 1 Day
6 of 11
Workflow Monitor
Recommended periodic searches
With activities that are ‘In Error’
No Progress in
Actions
To view notification, click on icon, not notification name
Filter Activities – shows all steps taken in workflow, all
loops, monitor diagram only shows current loop
Click gray space to see info about process, click node to
see info about node
Double-click process to drill down, or click process and
Zoom In button at top (Zoom Out button returns to
calling process)
AbortProcess – stops process and forces result (if result
modeled at process level); filter activities will show
result = Force
SuspendProcess – suspends process until
ResumeProcess button pressed
ResumeProcess – resumes suspended process
Reassign – Sends Notification to another role
(Notifications Only)
Expedite – typically would use ‘Retry’ for functions to
re-execute PL/SQL, and ‘Skip’ with result for
notifications
Attribute – shows values of attributes for node selected
and allows values to be changed.
Tabs
Definition – shows information from Properties page in
Builder for node/process
Usage – shows whether Start/End, Performer, Timeouts
Status – status of node/process, Result, begin/end date,
error name, short error message, error stack
Notification – shows recipient, status, begin/end/due
date
Item – definition of attributes
Notification / Routing Rules
Recommended – set up rule for SYSADMIN to redirect
all messages to the workflow administrator
Rules can be set for specific message, specific ItemType,
or All
If setting rule for specific message, can specify result
Can mark ‘Deliver Notifications to me, regardless of any
general rule’ to prevent confidential notifications from
being forwarded
Delegate Authority – person acts in your place, your
hierarchy followed
Transfer Ownership – new person’s hierarchy is
followed (if workflow codes for transfer)
Reassign Notifications
Notification Rules only redirect new notifications
Find Notifications – select To Role, click Find; click
each notification, click desired messages, then click
‘Reassign’ button at bottom; specify role to
transfer/delegate, add optional comment, click OK
Purge Runtime History
Schedule ‘Purge Obsolete Workflow Runtime Data’ –
parameters are ItemType (optional; to purge for
specific type workflow), ItemKey (optional; to purge
specific workflow), Age (default=0, days after
workflow is completed to keep workflow), Persistence
Type (Temporary (Default) or Permanent)
WF_PURGE.Total (or TotalPERM) (
default null,
sysdate, docommit default TRUE) – purges obsolete
runtime and obsolete directory services data
Troubleshooting/Admin Scripts
All scripts (except as noted) are in $FND_TOP/sql
WFDIRCHK – validates directory services
WFREFCHK – checks for invalid workflow data that is
missing primary key data for a foreign key
WFSTDCHK – checks and reports any problems in
workflow data model
WFVERCHK – identifies errors in activity versions
that cause multiple versions to seem to be both active
WFVERUPD – corrects multiple version problems
WFVER – displays version of workflow, status and
version of PL/SQL packages and version of views; run
and send results to Oracle Support with any TAR
WFBKGCHK – provides list of activities waiting to be
processed by background engine; useful if need to
create multiple background engines
WFRETRY
activities; admin must reply with expediting action.
WFPROT – resets protection level of all objects
associated with specified ItemType
WFSTATUS
of info about specific instance of a workflow
WFSUPPORT – reports information needed to file
workflow TAR; download from MetaLink / Top Tech
Docs / e-Business Suite-ERP / Workflow
Troubleshoot Account Generator
Set Profile Option ‘Account Generator: Run in Debug
Mode’ to ‘Yes’ – causes activities to be written to
run-time tables and than can be viewed in Monitor
Be Apps DBA In 1 Day
7 of 11
Dynamic Performance Views (continued)
v$SGA ,v$SGA_STAT, v$TABLESPACE, v$VERSION
Fixed Views
v$FIXED_TABLE, v$FIXED_VIEW_DEFINITION,
v$INDEXED_FIXED_COLUMN
Miscellaneous Views
v$TIMER, v$TYPE_SIZE, v$SEQUENCES
MTS and Parallel Server Views
v$CIRCUIT, v$DISPATCHER, v$DISPATCHER_RATE, v$MTS,
v$QUEUE, v$REQDIST, v$SHARED_SERVER, v$THREAD
File Mapping
v$MAP_LIBRARY, v$MAP_FILE, v$MAP_FILE_EXTENT,
v$MAP_ELEMENT, v$MAP_EXT_ELEMENT,
v$MAP_SUBELEMENT, v$MAP_COMP_LIST,
v$MAP_FILE_IO_STACK
Popular Instance Statistics
Descriptions for key statistics stored in the v$SESSTAT and
v$SYSSTAT views. Set TIMED_STATISTICS = TRUE in the
database init.ora.
The Statistics Query:
SELECT n.name, s.value
FROM v$statname n, v$sysstat s
WHERE n.statistic# = s.statistic#
ORDER BY n.class, n.name;
enqueue timeouts – lock timed out, should be small #
enqueue waits – # of times waited for a lock
enqueue requests – # of locks requested
enqueue conversions – # of times lock type changed
enqueue releases – # of locks released
db block gets – # of requests for current copy of block
consistent gets – this + db block gets = # logical reads
physical reads – reads directly from disk
free buffer requested – # of free buffers
DBWR free buffers found – # of clean buffers found in scan
DBWR lru scans – number of times lru scanned
DBWR buffers scanned – # of lru scanned for dirty buffers
logons cumulative – a since last warm start
logons current – current users
opened cursors cumulative – since last warm start
opened cursors current – current SQL cursors
recursive calls – high value indicates dictionary cache too small
redo entries – # redo entries created
redo size – bytes of generated redo entries
redo buffer allocation retries – indicates redo problem
redo wastage – filler added to redos, high value is ok
redo log space requests – requests to write to redo buffer
session logical reads – total # of reads (logical/physical)
sorts(disk) – # of sorts sent to disk
sorts(memory) – # of sorts performed in memory
sorts(rows) – total # of rows sorted cumulative
table scans (long tables) – minimize in application
table scan rows gotten – minimize in application
table fetch by rowid – indexed fetches
DBA Views
Storage Information
DBA_EXTENTS, DBA_FREE_SPACE, DBA_OBJECTS,
DBA_OBJECT_SIZE, DBA_SEGMENTS, DBA_TABLESPACES,
DBA_ROLLBACK_SEGS, DBA_UNDO_EXTENTS
Operating System
DBA_DATA_FILES, DBA_EXP_FILES, DBA_TEMP_FILES
Privileges
DBA_COL_PRIVS, DBA_PROFILES, DBA_ROLES,
DBA_ROLE_PRIVS, DBA_SYS_PRIVS, DBA_TAB_PRIVS,
DBA_UPDATABLE_COLUMNS
Indexes
DBA_INDEXES, DBA_IND_COLUMNS,
DBA_IND_EXPRESSIONS, DBA_IND_PARTITIONS
Tables/Views
DBA_TABLES, DBA_TAB_COLUMNS, DBA_TAB_PARTITIONS,
DBA_TAB_COMMENTS,
DBA_UNUSED_COL_TABS, DBA_VIEWS
Constraints
DBA_CONSTRAINTS, DBA_CONS_COLUMNS
Triggers
DBA_TRIGGERS, DBA_TRIGGER_COLS,
DBA_INTERNAL_TRIGGERS
Materialized Views
DBA_MVIEW_AGGREGATES, DBA_MVIEW_ANALYSIS,
DBA_MVIEW_DETAIL_RELATIONS, DBA_MVIEW_JOINS,
DBA_MVIEW_KEYS
Partitions
DBA_PART_COL_STATISTICS, DBA_PART_HISTOGRAMS,
DBA_PART_INDEXES, DBA_PART_KEY_COLUMNS,
DBA_PART_LOBS, DBA_PART_TABLES,
DBA_IND_SUBPARTITIONS, DBA_LOB_PARTITIONS,
DBA_LOB_SUBPARTITIONS
Objects, Methods and Types
DBA_OBJECT_TABLES, DBA_METHOD_PARAMS,
DBA_METHOD_RESULTS, DBA_TYPES, DBA_TYPE_ATTRS,
DBA_TYPE_METHODS, DBA_DIMENSIONS, DBA_LOBS
Operators
DBA_OPANCILLARY, DBA_OPARGUMENTS,
DBA_OPBINDINGS, DBA_OPERATORS
Summaries
DBA_SUMMARIES, DBA_SUMMARY_AGGREGATES,
DBA_SUMMARY_DETAIL_TABLES, DBA_SUMMARY_JOINS,
DBA_SUMMARY_KEYS
Miscellaneous
DBA_DB_LINKS, DBA_SOURCE, DBA_SEQUENCES,
DBA_SYNONYMS, DBA_USERS, DBA_OUTLINES,
DBA_JOBS, DBA_JOBS_RUNNING, DBA_LIBRARIES,
DBA_PENDING_TRANSACTIONS, DBA_RULESETS,
DBA_OUTLINE_HINTS, DBA_POLICIES,
DBA_SUBPART_KEY_COLUMNS, DBA_TEMP_FILES,
DBA_TS_QUOTAS, DBA_JAVA_POLICY, USER_JAVA_POLICY
Dynamic Performance Views
Instance Level Tuning
v$GLOBAL_TRANSACTION, v$OBJECT_DEPENDENCY,
v$SHARED_POOL_RESERVED, v$SORT_SEGMENT,
v$SYSTEM_CURSOR_CACHE, v$SORT_USAGE, v$STATNAME,
v$SYSSTAT, v$SYSTEM_EVENT, v$TRANSACTION, v$LATCH,
v$LIBRARYCACHE, v$ROLLSTAT, v$ROWCACHE,
v$SGASTAT, v$SQLAREA, v$SQLTEXT, v$WAITSTAT
Recovery Based Views
v$ARCHIVE, v$ARCHIVE_DEST, v$BACKUP_CORRUPTION,
v$BACKUP_DEVICE, v$BACKUP_REDOLOG,
v$DELETED_OBJECT, v$RECOVERY_LOG, v$RECOVER_FILE,
v$ARCHIVED_LOG, v$BACKUP, v$BACKUP_DATAFILE,
v$BACKUP_PIECE, v$BACKUP_SET,
v$RECOVERY_FILE_STATUS, v$RECOVERY_STATUS,
v$DATABASE_BLOCK_CORRUPTION,
v$DATABASE_INCARNATION
Cache Views
v$CACHE, v$LIBRARYCACHE, v$SUBCACHE,
v$DB_OBJECT_CACHE, v$ROWCACHE
Control File Views
v$CONTROLFILE, v$CONTROLFILE_RECORD_SELECTION
Cursor and SQL Views
v$SYSTEM_CURSOR_CACHE, v$OPEN_CURSOR, v$SQLAREA,
v$SQL, v$SQLTEXT, v$SQLTEXT_WITH_NEWLINES,
v$SQL_CURSOR, v$SQL_BIND_METADATA,
v$SQL_SHARED_MEMORY, v$SQL_BIND_DATA,
v$SQL_WORKAREA, v$SQL_WORKAREA_ACTIVE
Security Views
v$ENABLEDPRIVS, v$PWFILE_USERS
Session Views
v$ACCESS, v$MYSTAT, v$PROCESS, v$SESSION ,
v$SESSION_CONNECT_INFO, v$SESSION_CURSOR_CACHE,
v$SESSION_EVENT, v$SESSION_LONGOPS,
v$SESSION_OBJECT_CACHE, v$SESSION_WAIT, v$SESSTAT
(needs v$statname, v$session join), v$SESS_IO
Latch and Lock Views
v$BUFFER_POOL, v$CACHE_LOCK, v$CLASS_PING,
v$DLM_CONVERT_LOCAL, v$DLM_CONVERT_REMOTE,
v$DLM_LATCH, v$DLM_MISC, v$ENQUEUE_LOCK,
v$EVENT_NAME, v$FALSE_PING, v$FILE_PING, v$LATCH,
v$LATCHHOLDER, v$LATCHNAME, v$LATCH_CHILDREN,
v$LATCH_MISSES, v$LATCH_PARENT, v$LOCK,
v$LOCK_ACTIVITY, v$LOCK_ELEMENT, v$LOCKED_OBJECT,
v$LOCKS_WITH_COLLISIONS, v$PING,
v$RESOURCE, v$RESOURCE_LIMIT,
v$TRANSACTION_ENQUEUE, v$LOCK
Instance Views
v$ACTIVE_INSTANCES, v$BGPROCESS, v$BH,
v$COMPATIBILITY, v$COMPATSEG, v$COPY_CORRUPTION,
v$DATABASE, v$DATAFILE, v$DATAFILE_COPY,
v$DATAFILE_HEADER, v$DBFILE, v$DBLINK, v$DB_PIPES,
v$INSTANCE, v$LICENSE, v$OFFLINE_RANGE, v$OPTION,
v$TRANSACTION, v$ROLLSTAT, v$UNDOSTAT
Be Apps DBA In 1 Day
_
mts_servers = 1 – Retained for backward compatibility only,
deprecated in Oracle9i; use SHARED_SERVERS instead
open_cursors = 64 – Maximum number of cursors that a user session
can have open at any one time
parallel_max_servers = 5 – Maximum number of servers that are
allowed to exist concurrently. Set the value to (maximum number
of PQO users * their maximum degree of parallelism * 2).
pga_aggregate_target = 1000m – Specifies the target aggregate PGA
memory available.
processes = 25 – Max number of simultaneous connections allowed
to the instance.
query_rewrite_enabled = FALSE #enable or disable query rewriting
(ex: materialized views/function indexes).
rollback_segments = (r01,r02,r03,r04) – Indicates all of the private
rollback segments that you want brought online at instance startup.
row_locking = ALWAYS – Should row locking be used?
shared_pool_size = 3500000 – Size of shared buffer pool in the SGA.
shared_servers = 1 – Number of server processes that you want to
create when an instance is started up
sort_area_size = 2048000 – Size in bytes that a user process has
available for sorting.
_system_trig_enabled = TRUE – Set to FALSE when upgrading
rdbms.
timed_statistics = TRUE – If set TRUE, provides needed CPU
timing information on your SQL statements and by user sessions.
undo_management = AUTO – Specifies the undo space
management mode
undo_retention = 1800 – Specifies (in seconds) the amount of
committed undo information to retain
undo_tablespace = UNDOTS – Undo tablespace to be used when
instance starts
user_dump_dest = /u01/app/oracle/admin/ORCL/udump – Directory
in which to write user process trace files.
workarea_size_policy = AUTO – Controls mode in which working
areas are tuned.
Accessing Utilities Help
Import
$ imp help = y
import example: $ imp system/manager file=expdat.dmp full=y
rows=y buffer=2048000 indexes=y ignore=y commit=y
log=full_import.log
Export
$ exp help = y
export example: $ exp system/manager file=expdat.dmp full=y
compress=y consistent=y buffer=1024000 log=full_exp.log
SQL*Loader
$ sqlldr
SQL*Plus
Installing Help into SQL*Plus – As user oracle …
Set the SYSTEM_PASS environment variable:
$ SYSTEM_PASS=system/
execute the script, $ORACLE_HOME/bin/helpins
Database Control (UNIX)
As user oracle …
$ sqlplus / nolog
SQL> connect / as sysdba
SQL> startup
startup = startup the database
shutdown – shutdown the database
Initialization Parameters
audit_file_dest = /u01/app/oracle/admin/ORCL/adump – Sets the
path to which audit files are written
audit_trail = FALSE – Enables (TRUE) or disables (FALSE) writing
of rows to the audit table
background_dump_dest = /u01/app/oracle/admin/ORCL/bdump –
Directory in which to write debugging trace files for the background
processes (LGWR, DBWn, and so on)
control_files = (/u01/oradata/ORCL/controlORCL01.ctl,
/u02/oradata/ORCL/controlORCL02.ctl) – Full path to database
control files
core_dump_dest = /u01/app/oracle/admin/ORCL/cdumpn –
Directory in which to write core dumps in an error situation
db_block_buffers = 6000 – Sets the size of the database buffer cache
in memory; in 8.1.x, the default buffer cache size is calculated to be
as many buffers as will fit in 48MB
db_block_size = 8192 – Size of each database buffer in bytes (2048
to 32768; Oracle recommends that you set the parameter to a
minimum of 8 KB and that it be a multiple of the OS block size; this
parameter takes effect only at the time the database is created
db_domain = my_company.com – Specifies the extension
components of a global database name, consisting of valid
identifiers, separated by periods
db_files = 80 – Number of database files that can be open when the
database is running
db_file_multiblock_read_count = 8 – Number of blocks read into
the buffer cache at once when performing a sequential scan
db_name = ORCL – The name of this database
dispatchers = "(PRO=TCP)(DIS=3)" – Configures dispatcher
processes in the shared server architecture
enqueue_resources = 9999 – Sets the number of resources (10-
65535) that can be locked by the operating system lock manager
global_names = TRUE – Enables (TRUE) or disables (FALSE) db
link name checking
java_pool_size = 50000000 – minimum for 11i
log_archive_dest = /u08/oraarch/ORCL/arch – Directory location
and the first part of the name of each archive log that will be written
log_archive_format = _%s.log – Sets format for archived logs
log_archive_start = TRUE – Enables (TRUE) or Disables (FALSE)
archiving
log_buffer = 65536 – Number of bytes allocated to redo log buffer in
the SGA. Max = 500K or 128K * CPU_COUNT
log_checkpoint_interval = 10000 – Number of new redo log file
blocks needed to trigger a checkpoint; values: 2 to UNLIMITED
max_dispatchers = 5 – Maximum number of dispatcher processes
allowed to be running simultaneously
max_dump_file_size = 500 – Limits physical size of the trace file to
the specified number of operating system blocks (or UNLIMITED)
_
max_enabled_roles = 20 – Maximum number of roles per user
_
max_rollback_segments = 30 – Maximum number of rollback
segments that can be kept online simultaneously by one instance
_
max_shared_servers = 20 – Maximum number of shared server
processes allowed to be running simultaneously
mts_dispatchers = “tcp,1” – Retained for backward compatibility
only, deprecated in Oracle9i; use DISPATCHERS instead
mts_max_dispatchers = 5 – Retained for backward compatibility
only, deprecated in Oracle9i; use MAX_DISPATCHERS instead
mts_max_servers = 20 – Retained for backward compatibility only,
deprecated in Oracle9i; use MAX_SHARED_SERVERS instead
Be Apps DBA In 1 Day
Visual Editor Commands and Navigation
Cursor Movement
h,j,k,l left, down, up and right movement.
0
$
Jump to beginning of line.
Jump to end of line.
w skip to next word.
CTRL-D Down one page.
CTRL-U Up one page.
G Skip to end of file.
1G Skip to top of file.
CTRL-R or CTRL-L Refresh screen.
Searching / text Search forward for text.
? text Search backward for text.
n Repeat search after find.
Line Numbering
CTRL-G Display current line number.
:99 Move to line number 99.
Inserting Text
a Append after cursor.
A Append to end of line.
i Insert before cursor.
I Insert at beginning of line.
o Open new line below cursor.
ESC Terminate edit mode and return to
command mode.
Changing Text cw Change word.
cc Change whole line.
C Change text to end of line.
dd Delete line.
5dd Delete current line and next 4.
D Delete to end of line.
u Undo last change.
U Restore current line.
Moving Text
yy Yank a copy of current line.
p Insert the previously yanked line.
Saving and Exiting
ZZ Save and exit file.
:wq Same as ZZ.
:q! Exit without saving.
:n Go to next file to be edited.
:w filename1 Save edited file as filename1.
Symbols > Redirect output.
< Redirect input.
>> Append to file.
| Pipe Output.
& Run process in background.
; Separate commands.
* Match any char(s).
? Match a char.
stty erase ^H Use to reset backspace/delete. ^H is
the key you may want to use for backspace/delete.
Typically the backspace key itself.
su sam Logs you on as user sam.
su - sam Log on as sam, and execute his profile too.
tail filename1 Display last few lines of filename1.
tail -50 filename1 Display last fifty lines of filename1.
tail -f Continually reads updating file. Great for
monitoring growth of a log file while being written.
tar -cvf /dev/rmt/0c /usr/bin/* Tape archive utility.
Copies all files in /usr/bin directory to tape device
/dev/rmt/).
tar -xvf /dev/rmt/0 /usr/bin/ Extracts all files from
tape device /dev/rmt/0 and writes them to /usr/bin/.
tar-tvf /dev/rmt/0c Read tape on device /dev/rmt/0c
and lists contents in verbose mode.
tee Used in scripts to split output to two outputs.
Usually used with a pipe command (|).
tee -a /tmp/filename1 Append the output to filename1
without overwriting its original content.
telnet hostb Create a remote terminal on hostb.
touch filename1 Creates an empty file named
filename1. Changes modification time to current
time if the file already exists.
uname -a Lists O/S revision, host name, hardware.
uncompress filename1 Uncompresses file with .Z
suffix, created by compress command previously.
uptime Displays current time, time logged-in, number
of users etc.
users Displays current logged-in users in a listing.
uucp Unix-to-Unix copy utility.
view filename1 A read only version of vi editor.
w Combination of uptime, who and ps -a commands.
wall Write to all. Allows entry of message to be sent to
line 25 of all terminals. End message with a CTRL-D
command.
wc -l filename1 Utility that counts the number of lines
in filename1.
wc -c filename1 Utility that counts the number of bytes
in filename1. - m provides number of chars.
wc -w filename1 Utility that counts the number of the
words in filename1.
whence filename2 Prints path name location of
executable filename2.
which filename2 Similar to whence command.
who Displays login name, terminal name, date and
time of login, of users currently logged in.
who am I Displays effective user id of user.
who -b Displays date/time of last reboot.
who -r Displays current system run level.
Be Apps DBA In 1 Day
Common UNIX 5VR4 Commands
man Online unix manual ("man-pages"). Gives
detailed instructions on all of the commands listed
here and a lot more. See man man for more.
cat filename1 > filename2 Overwrite contents of
filename2 with filename1.
cat filename1 >> filename2 Append contents of
filename1 to filename2.
cd /usr Change current directory to /usr.
cd .. Change current directory to previous higher
directory.
cd Change to home of current userid.
chgrp group1 filename1 Change group id to group1
for filename1.
chgrp -R group1 * Change group id of all files in
current and subdirectories to group1.
chmod ugo+rwx filename1 Add read/write/execute
permission to filename1 for user/owner, group and
others (world).
chmod o-x filename1 Remove execute from others
(world).
chmod 751 filename1 Set rwx for user/owner, rx for
group and x for others. rwx=421, r=4, w=2, x=1
rx=5x=7x=1.
chown sam filename1 Change owner of file filename1
to sam.
chown -R sam * Change owner to sam for all files in
current and subdirectories.
clear Clear the terminal screen.
compress filename1 Compress file filename1. See
also uncompress.
cp filename1 filename2 copy file filename1 to
filename2 destroying filename2.
cp -i * /usr/local/bin Copy all files in current directory
to /usr/local/bin directory. Prompt before overwriting
files (i).
cpio Copy file archives to disk/tape. See man pages.
crontab -e Edit crontab file for userid.
crontab -e bill Edit crontab file for user bill.
crontab -l List crontab entries for current userid.
csh Start the c shell process. See man pages.
cut -d: f1,5 /etc/passwd Extract username & real names
from file /etc/passwd where delimiter is colon getting
fields 1 & 5.
who | cut -d" " f1 List login names from who
command.
date Display current date string.
date +%D Display current date as 11/21/94 format.
dd Copy file(s) to/from raw devices. See man pages.
df Display free disk blocks and modes on file systems.
df -k Display free space in kilobytes for mounted file
systems.
echo name Displays literal "name" on screen.
echo $PATH Displays PATH environment variable.
ed/edit/ex Alternative line editors, see vi.
env Displays current environment variables or allows
setting.
file filename1 Determines and displays type of file for
filename1 (text, data, executable, directory, symbolic
link...etc...).
find /usr/opt/bin -name "filename1*" -print Starts
searching in /usr/opt/bin for files starting with
filename1. If found prints the full file names.
Continues searching subdirectories.
find . -type f -print | xargs grep -i [PATTERN]-
Recursive grep for a pattern in a file (searches through
files in subdirectories).
find . ! -mtime -
and removes files older than
finger sam Displays data about user session for sam.
ftp solar Establishes a File Transfer Protocol session
over the network between current host and a host
named solar. See man pages for ftp for various ftp
commands.
grep jdoe /etc/passwd Searches the file /etc/passwd
searching for string jdoe. If found, displays.
grep -i Sam filename1 Search filename1 for upper or
lower case string of Sam and display lines found.
groups sam List groups that sam is a member of.
head filename1 Display first few lines of filename1.
head -50 filename1 Display first fifty lines of
filename1.
id List current user id and any group ids.
kill -9 1351 Terminate process number 1351.
ksh Start Korn Shell command interpreter. See man
pages for more information. Preferred shell for most
users.
ln -s filename1 /usr/opt/filename2 Create a symbolic
link named /usr/opt/filename2 that points to
filename1. See man pages.
lp -d lp1 filename1 Print filename1 on destination
printer lp1.
lpstat -d Displays name of default printer, if any.
lpstat -a Lists printers accepting print requests.
lpstat -s Displays most everything regarding printing.
lpstat -u sam Displays status of sam's print jobs.
ls -al Displays all files in wide listing.
ls -al *.doc Displays files ending with .doc.
ls -al /bin/k* Displays files starting with k in /bin
directory.
mail sam Starts mail message to sam.
mailx sam Nicer looking mail utility.
mkdir -p /usr/opt/dirx Creates dirx below /usr/opt.
make Code compilation utility.
mkdir dirx Creates directory dirx.
more filename1 Displays single pages from filename1
pausing after each page. Many options.
mv filename1 /usr/opt/ Moves filename1 to directory
/usr/opt. Unlike the cp (copy) command, mv removes
file from origin.
netstat -i Show the TCP/IP network interfaces.
netstat -r Show network route table.
netstat -rn Displays routing information but bypasses
hostname lookup.
netstat -a | more Show the state of all sockets.
newgrp group1 Changes current group to group 1.
news Displays unread files from /usr/news or
/var/news.
nice/renice Adjusts process' execution priority.
passwd Allows changing your login password.
ps Lists all of current user's live processes.
ps -ef List all users processes that are executing.
pwd Displays current working directory you are in.
rcp filename1 hostb:/usr/local/bin Copies filename1
from current host system to hostb, and places it in the
/usr/local/bin directory. The .rhosts or hosts.equiv
files must be setup to allow action.
rksh Starts restricted Korn Shell session.
rlogin hostb Logs into remote host name hostb.
rm filename1 Deletes filename1 from the disk without
recourse.
rm -i filename1 Deletes filename1 after prompting for
verification.
rmdir dirx Deletes directory dirx.
rmdir -r dirx Deletes directory and all contents.
rsh Restricted version of Bourne Shell for security.
sar System Activity Reporter.
sh Bourne Shell command interpreter. Alternative to
Korn Shell and C shell but is the default on most
systems. Older version that is losing popularity.
sleep 3 Pauses for 3 seconds and continues.
stty sane Attempts to restore terminal settings after
they are hosed. Use CTRL-J with this command.
No comments:
Post a Comment