Just Oracle: 2010

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,

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 D

– 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 – verify owner (applmgr) and

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 Days

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, default null, enddate default

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 – displays errored

activities; admin must reply with expediting action.

WFPROT – resets protection level of all objects

associated with specified ItemType

WFSTATUS – written report

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/; export SYSTEM_PASS

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.

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 - | /usr/bin/xargs rm -rf finds

and removes files older than specified.

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.

Just Oracle

Thursday, March 25, 2010

AutoConfig Search Utility

Tuesday, March 2, 2010

Be Apps DBA In 1 Day