| DBAmon |
|
|
Home |
Index/DBAmon Doc. |
|
|
| Updated | Desc. |
|---|---|
| 2010/04/16 | New DB Threshold commands: -emth, -emac, -emah + New -unl, -ser + New way to connect from OS userids other than "oracle". |
To download, click on the orastat Download Form.
The download will consist of one big file (a Perl program) called orastat. After downloading, you will need to save it in a public bin directory (maybe /usr/local/bin) as orastat. You also may have to change the first line (#!) to point to YOUR Perl5 executable (if /usr/bin/env perl doesn't work on your system) Note that orastat requires Perl Version 5. You will also need to make it executable (chmod 775 orastat). After doing these steps, invoke orastat by running (from the OS UX shell): orastat. If you don't supply anything after the word orastat, you will see all of the available orastat subcommands.
If you want to receive EMail when orastat enhancements are made, send an EMail to: DBAmon Support.
orastat is a UX-based Command-Line tool which simplifies performing simple Oracle DBA tasks. In reality it is just a BIG Perl program which issus SQL commands via sqlplus, and reformats the output to the OS command line.
Oracle provides a wealth of information in the Data Dictionary, but it's never really had a command line interface to extract that data (other than sqlplus, but you have to write your own SQL). orastat effectively provides the canned SQL to extract data from the data dictionary to do common DBA tasks. For example, if you need to know how full the tablespaces on your database are, without orastat, you would have to:
select a.tablespace_name as tablespace_name,
d.contents,
a.sum_bytes as bytes_avail,
a.max_id,
nvl(b.sum_bytes,0) as bytes_used,
nvl(c.sum_bytes,0) as bytes_free,
round(nvl(b.sum_bytes,0.00) / a.sum_bytes * 100.0, 2) as pct_used,
d.status from ( select tablespace_name,
sum(bytes) sum_bytes, count(1) max_id
from dba_data_files group by
tablespace_name ) a, ( select
(yada, yada ...)
2010/04/02-13:51:25 orastat | oraver=10.2. oraver_short=10.2 oraver_num=10.2 sqlcmd=sp arg=-ts arg2= 2010/04/02-13:51:25 orastat | ORACLE_SID=mydb ORACLE_HOME=/opt/oracle/oracle/product/10.2.0/db_1 - HP-UX myserver B.11.11 U 9000/800 830940640 unlimited-user license 2010/04/02-13:51:25 orastat | Version=3.46 Host=myserver Company=dec PermanentTablespace Type Status ExtentMgmt AllocTyp NumDF Size(MB) Free(MB) Used(MB) Pct -------------------- ---- -------- ---------- -------- ----- --------- --------- --------- ------ DBATOOLS PERM ONLINE LOCAL UNIFORM 1 500 336 164 32.80 EXAMPLE PERM ONLINE LOCAL SYSTEM 1 150 109 40 26.96 MGMT_ECM_DEPOT_TS PERM ONLINE LOCAL SYSTEM 1 100 71 28 28.44 MGMT_TABLESPACE PERM ONLINE LOCAL SYSTEM 1 4000 799 3200 80.01 SYSAUX PERM ONLINE LOCAL SYSTEM 1 650 214 435 66.95 SYSTEM PERM ONLINE LOCAL SYSTEM 1 750 171 578 77.08 UNDOTBS1 UNDO ONLINE LOCAL SYSTEM 1 960 908 51 5.35 USERS PERM ONLINE LOCAL SYSTEM 1 500 457 42 8.44 ==================== ==== ======== ========== ======== ===== ========= ========= ========= ====== Total: (8 TS) 8 7610 3070 4539 59.66 2010/04/02-13:51:26 orastat | Found 8 tablespace(s) Note: This DB also has 1 8i+ Temporary TS(s) listed in dba_temp_files - invoking orastat -tt TemporaryTablespace Type Status ExtentMgmt AllocTyp NumDF Size(MB) Free(MB) Used(MB) Pct -------------------- ---- -------- ---------- -------- ----- --------- --------- --------- ------ TEMP TEMP ONLINE LOCAL UNIFORM 1 186 183 3 1.61 ==================== ==== ======== ========== ======== ===== ========= ========= ========= ====== Total: 1 186 183 3 1.61 2010/04/02-13:51:26 orastat | NOTE: These numbers are COMPLETELY ACCURATE. They show 2010/04/02-13:51:26 orastat | v$sort_usage and dba_temp_file data. |
Since orastat is run from the OS shell and all output is written to STDOUT, you can obviously use OS command like grep on orastat output.
For example:
Here are the orastat commands that are currently available:
2008/02/29-08:16:11 orastat | oraver=9.2.0 oraver_short=9.2 oraver_num=9.2 sqlcmd=sp arg=-- arg2= 2008/02/29-08:16:11 orastat | ORACLE_SID=LARRY ORACLE_HOME=/opt/oracle/product/9.2.0 - HP-UX bigdog B.11.00 U 9000/800 1614329373 unlimited-user license 2008/02/29-08:16:11 orastat | Version=3.40 Host=bigdog Company=??? orastat | Version 3.40 Usage: -- View This Help File - Check for PMON Running, Show Oracle Version, Instance Status -ab List Archive Log RMAN Backups [Option Archive Log Sequence Number] -ad List Archive Destinations (v$archive_dest) -af List Archive Log Directory Contents -al Show All Archived Logs -amm AMM (Automatic Memory Management) (sga_target) Stats -an Analyze Table COMPUTE STATISTICS - 'orastat -an TABLE-OWNER.TABLE-NAME' -ar Show Current DB Activity Rate -asm ASM Details & Status -asm_dg ASM Diskgroups -asm_dk ASM Disks -asm_fi ASM Files -asm_op ASM Operations -au Show DB Audit Status -av Archive Log Volume - [D/H] [# of days] -az Show Current DB Activity Rate - Log to /opt/oracle/adm/cronlog/db_activity_rate_.txt -ba List Contents of dbamon.backup_age Table -bb List Summary of all RMAN backup pieces - from v$backup_piece -bc List Contents of buffer pool -bd Datafile backup mode status -bi List RMAN backup inventory -- [ALL|SINCE|BETWEEN] -bm List Tablespaces and Datafiles in Backup Mode (from v$backup) -bo Buffer Cache Overview (Many Reports) -bp List running RMAN backup sessions (if any) -br Backups: List all media written to since the last RMAN LVL0 backup -brm Backup Media: List all backup media -bs Create DDL to take datafiles that are in backup mode out of backup mode -c Configuration: View init.ora File -cb Controlfile Backup: Trace and Binary -cc Client Connections -cd Controlfile Details (Sections) -ce Configuration: Edit 'vi' init.ora File -cf List Control Files -ck List Time of Last Checkpoint (from v$datafile_headers) -cm Configuration: View Contents of v$parameter with modifiable flags -cp Configuration: View Contents of v$parameter -ct Coalesce TEMP tablespace -cv Generate syntax to recompile INVALID Objects -da List Datafiles - Autoextend details -dc List Datafiles in 'cp' commands to copy all datafiles elsewhere -dd Reproduce Object DDL (9i+ Only) Table: orastat -dd TABLE SCOTT DEPT Index: orastat -dd INDEX SCOTT DEPT_X Tablespace: orastat -dd TABLESPACE "" TSNAME User: orastat -dd USER "" SCOTT DBLink: orastat -dd USER "" SCOTT -de List All Datafiles, Online Redo Logs and Control Files (For Destroying a DB) -df List Datafiles - Optional FSCHECK 2nd parm to bdf filesystem -df3 List Datafiles - from v$datafile -dh List Datafiles and their 'Used Blocks' High-Water-Mark - Creates RESIZE commands -di Datafiles Contents (extents) by Block address - Must supply DF# -dn A Better and Faster version of -dh -dp Database properties (from DATABASE_PROPERTIES) -dst DST Patch checking -du List Datafiles where UNRECOVERABLE_CHANGE# is not null -ec Configuration: Edit 'vi' config.ora File -er Display contents of DBA_ERRORS -ev Select from v$system_event -ex Select from plan_table -fm Display hours since file modification time - Requires ARG -fra Flash Recovery Area Status -fs Free Space in Each Datafile -ft List 'Fast Start' Transactions being rolled back -hd Hot Tables - With Datafile Name -ht Hot Tables -in List Indexes -iv List INVALID Objects -l List Archive Log Status -la Latch Details (from v$latch) -lf List Redo Log Files -lg Longops: Long Operation Progress -lh Lock Holders/Waiter -li List Resource Limits (v$resource_limit) -lk Locks - Current TX (Non-Row) Locks -ln List DB Links -lo List Current Table Locks -ls Listener Status -lv List LVOL's / Usage -m View Last 20 Lines Of Alert Log -ma Cat entire Alert Log -mf Tail -90f Alert Log -mr Alert log - reformatted - Last 20 lines -mr a Alert log - reformatted - entire file -mr v Alert log - reformatted - 'vi' entire file -mt MTS Statistics -mv 'vi' (read-only mode) Of Alert Log -nf List Objects whose NEXT EXTENT will not fit in the tablespace -nl List NOLOGGING Tables and Indices -ob Objects - With Decimal and Hex Object ID -op OPS: View V$PING - Lock Conversions -pb Performance: Current Data Block Buffer Hit Ratio - RIGHT NOW -pc Password Check: Check for accounts where username=password -pd Performance: View Data Block Buffer Hit Ratio -pf Performance: View Total Cumulative Free List Waits -ph Performance: Hot Blocks - Block With Latch Sleeps -pi Performance: View Histogram of Datafile I/O -pj Performance: I/O Distribution By Filesystem -pk Performance: I/O Distribution By Tablespace -pp Performance: I/O Service Times -pr List Profiles -ps OPS (Parallel Server) Status -pw Performance: Show segment names for tables with buffer waits -qs SQL Statment Count -ra List Rollback Segment Activity -rac RAC Status (the same as orastat -ps) -rb List Rollback Segments -rc List And SHRINK All Rollback Segments -rd List And ALTER MAXEXTENTS UNLIMITED All Rollback Segments -rf View Timestamps from V$RECOVER_FILE -rg View DBA_REGISTRY -rh List REDO Logs - History -rl List REDO Logs - Files -ro List Roles -rp RMAN Long Operation Progress -rs List REDO Logs - Status -ru Rollback Usage - By Session -sa Session Statistics - All Sessions -sb Standby DB - Show log gaps -sc Sessions - By Session CPU Time -sd Sessions - Details - Sessions, Running SQL and Waits -se Sessions -sg List SGA Usage -sh Shared Pool Usage & Tuning Recommendations -sl SELECT * from table - name supplied as 2nd parameter -sn SNAPSHOT - Run systemstate trace 3 times (for Oracle diagnostics) -so List sorts by user -sp List StatsPack Snapshot Data -spf Display SPFILE (if any) -sq Run SQL - Pass SQL as argument in single quotes -sr View Running SQL - 'orastat -sr SESSION_NUMBER' -ss List default storage clause for all TABLESPACES -st System Statistics - from v$sysstat -su System Utilization -sw Session Wait Statistics -sy List All Granted System Privileged -sz Session statistics for one session - 2nd parm is SID -ta List All Tables - From DBA_SEGMENTS (Name, TS, Size, Extents, Maxextents) - 2nd Arg 'ALL' to include SYS* tables -tb Count Tables - By Schema -tc Create SQL to count all rows in all permanent tables -td describe table - name of table supplied as 2nd parameter -ti Table Info - 'orastat -ti TABLE-OWNER.TABLE-NAME [-count]' -tj Datafile Space Allocated - By Filesystem -tm List Temporary Segments -tp List Objects (from DBA_SEGMENTS) By BLOCKS and BUFFER_POOL - Show buffer pool stats -tq Tablespaces - Details and Defaults -tr List Transactions -ts List Tablespaces -tt List 8i+ TEMPORARY Locally Managed Tablespaces -tu Temp space usage by user -tz List Tables - From DBA_TABLES - Shows ANALYZE data/timestamp -ua List USERS with incorrect default/temp tablespace - Creates SQL to ALTER USERS -ub List the byte count of data, by User -ud List Users With ORADBA -ug List Users Table Grants (Much Output) - 2nd Parm is optional grantee name -un Display 9i UNDO Statistics -up User Password Information -us List All Users -ut List the byte count of data, by User and Tablespace -v List Oracle version and whether it is 32-bit or 64-bit -vs List All Views -vw Count Views - By Schema -wr Wait Reasons - By User and Wait Type -ws Wait Stats - from v$waitstat -zr List contents of DBAMON.STANDBY_REBUILD -------------------------------------------------------------------- ???-Specific Commands: -cfbl List trace controlfile backup with create timestamp > [numeric timestamp] --------------------------------------------------------------------
orastat | ORACLE_SID=PD1 ORACLE_HOME=/oracle/PD1 orastat | Active Parameters (from v$parameter) Parm Value Desc. ======================================== =================================== ============================== O7_DICTIONARY_ACCESSIBILITY TRUE Version 7 Dictionary Accessibility Suppo _controlfile_enqueue_timeout 1800 control file enqueue timeout in seconds allow_partial_sn_results FALSE allow partial results when processing gv always_anti_join NESTED_LOOPS always use this anti-join when possible always_semi_join standard always use this semi-join when possible aq_tm_processes 0 number of AQ Time Managers to start ...
orastat | ORACLE_SID=PD1 ORACLE_HOME=/oracle/PD1 Tablespace Datafile Datafile Datafile FS Free Name Num Path Size(MB) Space(MB) ---------------- -------- ------------------------------------------------------- --------- --------- PSAPBTABD 73 /oracle/PD1/sapdata9/btabd_11/btabd.data11 2039 5584 "" 72 /oracle/PD1/sapdata1/btabd_10/btabd.data10 699 1663 "" 67 /oracle/PD1/sapdata7/btabd_8/btabd.data8 2039 2097 "" 40 /oracle/PD1/sapdata2/btabd_6/btabd.data6 1000 2076 "" 31 /oracle/PD1/sapdata8/btabd_3/btabd.data3 2000 2887 "" 14 /oracle/PD1/sapdata7/btabd_9/btabd.data9 2039 2097 ...
orastat | ORACLE_SID=PD1 ORACLE_HOME=/oracle/PD1 Tablespace NumDF Size(MB) Free(MB) Used(MB) Pct -------------------- ----- --------- --------- --------- ----- PSAPBTABD 11 15319 2800 12519 81.7 PSAPBTABI 9 10594 2138 8456 79.8 PSAPCLUD 5 2636 735 1900 72.1 PSAPCLUI 1 500 120 379 75.9 PSAPDDICD 3 939 411 528 56.3 PSAPDDICI 2 525 266 259 49.4 ...
orastat | ORACLE_SID=PD1 ORACLE_HOME=/oracle/PD1 Data Block Buffer Hit Ratio Data Consistent Gets: -1263179968 DB Block Gets: 18559496 Physical Reads: 164901273 Hit Ratio: 94.59% DB Block Size: 8192 DB Block Buffers: 23000 ...
orastat | ORACLE_SID=PD1 ORACLE_HOME=/oracle/PD1
Pct Of
1000's 1000's 1000's DB I/O
Datafile Reads Writes R+W Total
====================================================== ====== ====== ====== ======
/oracle/PD1/sapdata2/btabi_2/btabi.data2 63345 9 63354 38.08% ****
/oracle/PD1/sapdata2/btabd_1/btabd.data1 32739 5 32744 19.68% **
/oracle/PD1/sapdata5/btabi_4/btabi.data4 20648 54 20702 12.44% **
/oracle/PD1/sapdata5/btabi_1/btabi.data1 17678 119 17797 10.70% **
/oracle/PD1/sapdata5/btabi_5/btabi.data5 9444 17 9462 5.69% *
...
orastat | ORACLE_SID=PD1 ORACLE_HOME=/oracle/PD1 orastat | SGA Status SGA Pool Size(Bytes) Size(MB) ==================== =========== ======== Shared Pool (Total) 157954480 157.95 Shared Pool (Free) 82300800 82.30 47.9% Full DB Block Buffer 188416000 188.42 Redo Log Buffer 1048576 1.05 Fixed SGA 52200 0.05 ...
orastat | ORACLE_SID=PD1 ORACLE_HOME=/oracle/PD1 All Tables (From DBA_SEGMENTS) Owner.Table TableSpace Blocks MB Xtents MaxXtnt ========================================= ============ ====== ====== ====== ======== DBAMON.BACKUP_AGE PSAPUSER1D 4 0 3 (Unlim) OPS$MA2ADM.SAPUSER PSAPUSER1D 2 0 1 121 OPS$PD1ADM.SAPUSER PSAPUSER1D 2 0 1 (Unlim) SAPR3./SAPSMOSS/ADDID PSAPPOOLD 2 0 1 300 ...
orastat | ORACLE_SID=PD1 ORACLE_HOME=/oracle/PD1 All Users User Password Profile Dflt. TS Temp. TS Created ========== ========== ========== ============ ============== =================== DBAMON (Set) DEFAULT PSAPUSER1D PSAPTEMP 15-JUL-00 DBSNMP (Set) DEFAULT SYSTEM SYSTEM 11-MAR-00 DBSPI (Set) DEFAULT PSAPUSER1D PSAPTEMP 01-NOV-99 OPS$MA2ADM (Set) DEFAULT PSAPUSER1D PSAPTEMP 11-MAR-00 ...