Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

oneday_appsdba

VIEWS: 596 PAGES: 11

									Be Apps DBA In 1 Day

Be Apps DBA In 1 Day

Author: Pradeep Agarwal
(pradeep.agarwal@wipro.com)

Wipro Technologies Oct 2004

Wipro Technologies

Confidential

1 of 11

Be Apps DBA In 1 Day

Table Of Contents 1. Purpose Of The Document ..................................3 . 2. How to Use ............................................................3 4. Details ................................................................... 3

Wipro Technologies

Confidential

2 of 11

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.

Wipro Technologies

Confidential

3 of 11

Be Apps DBA In 1 Day
11.5.9 Key Configuration Files – App (8.0.6.3)
(Applications Administrator Role)
Filename $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 – DB (8.1.7.4/9.2.0.4*)
(Oracle DBA Role)
SID Path Port Host Filename $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

Apache HTTP Settings
Parameter httpd.conf:Timeout httpd.conf:KeepAliveTimeout httpd.conf:SSLSessionCacheTimeout Default None None None Recommendation 300 (seconds) 15 (seconds) 300 (seconds)

SID Path Port Host • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • •

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

11.5.9 Key Site Profile Options
Filename 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
SID Path Port Host • • • • • • • • • • • • • • • • • • • • • • • • • • • •

11.5.9 Key Configuration Files – Web/iAS (1.0.2.2.2)
(Web Administrator Role)
Filename
$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

Application Profile Values Relating to User Sign On
Profile Signon Password Failure Limit Signon Password Hard to Guess Signon Password Length Signon Password No Reuse Signon Password Custom Sign-On:Notification Default None No 5 None None No Recommendation 3 (attempts) Yes 7 (characters) 180 (days) *1 Yes *2

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

SID Path Port Host
• • • • •

• • • •
• • • • • • • •

• • • • • • -

• • • • • -

Application Profile Values Relating to ICX
Parameter ICX:Session Timeout ICX: Limit Time ICX: Limit Connect Default none 4 (hours) 1000 Recommendation 30 (minutes) 10 (hours) 1000

Default Port Numbers (Port Pool = 0)
Database Listener RPC Reports Server Web Server OprocMgr Web PL/SQL Servlet
Forms Listener

Jserv (Java) Settings
Parameter disco4iviewer.properties:session.timeout formservlet.ini:FORMS60_TIMEOUT formservlet.properties:session.timeout jserv.conf:ApJServVMTimeout mobile.properties:session.timeout zone.properties:session.timeout zone.properties:servlet.framework.initArg Default None None None None None None None Recommendation 5400000 (milliseconds) 55 (minutes) 5400000 (milliseconds) 360 (seconds) 5400000 (milliseconds) 5400000 (milliseconds) 5400000 (milliseconds)

1521 1626 7000 8000 8100 8200 8800
9000

JTF Fulfillment Server Map Viewer Servlet OEM Web Utility Visibroker OrbServer Agent MSCA Server MSCA Dispatcher OACORE Servlet Range
Discoverer Servlet Range

Forms Environment Variables
Parameter FORMS60_TIMEOUT FORMS60_CATCHTERM Default None None Recommendation 55 (minutes) 0

Metrics Server Data
Metrics Server Request

9100
9200

Forms Servlet Range
XML SVCS Servlet Range

9300 9800 10000 10100 10200 10300 16000 to 16009 17000 to 17009 18000 to 18019 19000 to 19009

Wipro Technologies

Confidential

4 of 11

Be Apps DBA In 1 Day
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.

Wipro Technologies

Confidential

5 of 11

Be Apps DBA In 1 Day
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

Setup – 11.5.9 – Required

$which sendmail – if no path returned, add SendMail executable folder to $PATH $sendmail <email address> <test message> <Ctrl>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 <each file name> – 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 – <applmgr>@<server> (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 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)

Setup – 11.5.9 – Optional

Wipro Technologies

Confidential

6 of 11

Be Apps DBA In 1 Day
Recommended periodic searches With activities that are ‘In Error’ No Progress in <n> 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

Workflow Monitor

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

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) (<ItemType> default null, <ItemKey> default null, enddate default sysdate, docommit default TRUE) – purges obsolete runtime and obsolete directory services data 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 <ItemType> <ItemKey> – displays errored activities; admin must reply with expediting action. WFPROT – resets protection level of all objects associated with specified ItemType WFSTATUS <ItemType> <ItemKey> – 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

Purge Runtime History

Troubleshooting/Admin Scripts

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)

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

Wipro Technologies

Confidential

7 of 11

Be Apps DBA In 1 Day
DBA Views
DBA_EXTENTS, DBA_FREE_SPACE, DBA_OBJECTS, DBA_OBJECT_SIZE, DBA_SEGMENTS, DBA_TABLESPACES, DBA_ROLLBACK_SEGS, DBA_UNDO_EXTENTS

Dynamic Performance Views
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 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

Dynamic Performance Views
Fixed Views

(continued)

Storage Information

Instance Level Tuning

v$SGA ,v$SGA_STAT, v$TABLESPACE, v$VERSION v$FIXED_TABLE, v$FIXED_VIEW_DEFINITION, v$INDEXED_FIXED_COLUMN v$TIMER, v$TYPE_SIZE, v$SEQUENCES v$CIRCUIT, v$DISPATCHER, v$DISPATCHER_RATE, v$MTS, v$QUEUE, v$REQDIST, v$SHARED_SERVER, v$THREAD

Operating System

DBA_DATA_FILES, DBA_EXP_FILES, DBA_TEMP_FILES DBA_COL_PRIVS, DBA_PROFILES, DBA_ROLES, DBA_ROLE_PRIVS, DBA_SYS_PRIVS, DBA_TAB_PRIVS, DBA_UPDATABLE_COLUMNS DBA_INDEXES, DBA_IND_COLUMNS, DBA_IND_EXPRESSIONS, DBA_IND_PARTITIONS

Miscellaneous Views

Privileges

Recovery Based Views

MTS and Parallel Server Views

Indexes

File Mapping

Tables/Views

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

DBA_TABLES, DBA_TAB_COLUMNS, DBA_TAB_PARTITIONS, DBA_TAB_COMMENTS, DBA_UNUSED_COL_TABS, DBA_VIEWS

Cache Views

Constraints Triggers

DBA_CONSTRAINTS, DBA_CONS_COLUMNS DBA_TRIGGERS, DBA_TRIGGER_COLS, DBA_INTERNAL_TRIGGERS DBA_MVIEW_AGGREGATES, DBA_MVIEW_ANALYSIS, DBA_MVIEW_DETAIL_RELATIONS, DBA_MVIEW_JOINS, DBA_MVIEW_KEYS 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

v$CACHE, v$LIBRARYCACHE, v$SUBCACHE, v$DB_OBJECT_CACHE, v$ROWCACHE

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

Control File Views

v$CONTROLFILE, v$CONTROLFILE_RECORD_SELECTION

Cursor and SQL Views

Materialized Views

Partitions

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 v$ENABLEDPRIVS, v$PWFILE_USERS 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 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
v$ACTIVE_INSTANCES, v$BGPROCESS, v$BH,

Security Views Session Views

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

Latch and Lock Views

Operators

DBA_OPANCILLARY, DBA_OPARGUMENTS, DBA_OPBINDINGS, DBA_OPERATORS DBA_SUMMARIES, DBA_SUMMARY_AGGREGATES, DBA_SUMMARY_DETAIL_TABLES, DBA_SUMMARY_JOINS, DBA_SUMMARY_KEYS 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

Summaries

Miscellaneous

Instance Views

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

Wipro Technologies

Confidential

8 of 11

Be Apps DBA In 1 Day
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 (1065535) 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
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
$ 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 $ exp help = y export example: $ exp system/manager file=expdat.dmp full=y compress=y consistent=y buffer=1024000 log=full_exp.log $ sqlldr <Enter> Installing Help into SQL*Plus – As user oracle … Set the SYSTEM_PASS environment variable: $ SYSTEM_PASS=system/<password>; export SYSTEM_PASS execute the script, $ORACLE_HOME/bin/helpins As user oracle … $ sqlplus / nolog SQL> connect / as sysdba SQL> startup startup = startup the database shutdown – shutdown the database

Import

Export

SQL*Loader SQL*Plus

Database Control (UNIX)

Wipro Technologies

Confidential

9 of 11

Be Apps DBA In 1 Day
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.

Visual Editor Commands and Navigation
Cursor Movement h,j,k,l 0 $ w CTRL-D CTRL-U G 1G CTRL-R or CTRL-L Searching / text ? text n Line Numbering CTRL-G :99 Inserting Text a A i I o ESC
left, down, up and right movement. Jump to beginning of line. Jump to end of line. skip to next word. Down one page. Up one page. Skip to end of file. Skip to top of file. Refresh screen. Search forward for text. Search backward for text. Repeat search after find. Display current line number. Move to line number 99. Append after cursor. Append to end of line. Insert before cursor. Insert at beginning of line. Open new line below cursor. Terminate edit mode and return to command mode. Change word. Change whole line. Change text to end of line. Delete line. Delete current line and next 4. Delete to end of line. Undo last change. Restore current line. Yank a copy of current line. Insert the previously yanked line. Save and exit file. Same as ZZ. Exit without saving. Go to next file to be edited. Save edited file as filename1.

Changing Text cw cc C dd 5dd D u U Moving Text yy p Saving and Exiting ZZ :wq :q! :n :w filename1

Symbols
> < >> | & ; * ? Redirect output. Redirect input. Append to file. Pipe Output. Run process in background. Separate commands. Match any char(s). Match a char.

Wipro Technologies

Confidential

10 of 11

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 -<days> | /usr/bin/xargs rm -rf finds and removes files older than <days> 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.

Wipro Technologies

Confidential

11 of 11


								
To top