Monitoring system using solaris commands.......

Monitoring CPU Usage

Method:1
We can use SAR command to monitor CPU usage. The SAR commands gives you a quick snapshot of haw heavily the CPU is bogged down.
# sar -u 5 5
SunOS flex-prod 5.10 Generic_118833-23 sun4u 09/12/2007

12:53:16 %usr %sys %wio %idle
12:53:21 33 2 0 66
12:53:26 33 1 0 66
12:53:31 36 2 0 63
12:53:36 55 4 0 40
12:53:41 59 7 0 34

Average 43 3 0 54
This is tell you:
%usr Percentage of CPU running in user mode
%sys Percentage of CPU running in system mode
%wio Percentage of CPU running in with a process waiting for block I/O
%idle Percentage of CPU that is IDLE
TIPS: Use the sar -u command to see how heavily the CPU is bogged down. The Low CPU idle time can be an I/O issue and not a CPU issue.

Method:2
# vmstat 5 5
procs memory page disk faults cpu
r b w avm fre re at pi po fr de sr d0 s1 d2 s3 in sy cs us sy id
0 0 0 0 1088 0 2 2 0 1 0 0 0 0 0 0 26 72 24 0 1 98
Note: The CPU is spending most of its time in IDLE mode (id). That means that the CPU is not being heavily used at all. There are no processes that are waiting to be run (r), blocked (b), or waiting for IO (w) in the RUN QUEUE.

Method:3
# sar -qu 5 5
Note: The CPU is spending most (94%) of its time in idle mode. This CPU is not being heavily used at all. if CPU is using heavily, two solutions to this are:
1. Obtain a faster processor
2. Use more CPU's.

Monitor I/O problem
Method :1
Use following command for Monitoring I/O
# sar -d 5 2
Note: This Command lists the % busy, avgue (average queue length), r+w/s, blks/s ( Number of block transfered), avwait and avserv.
Tips: A high % busy and high avque indicate a disk I/O bottleneck. if this condition persist, an analysis of disk should lead to a reorganised of information from heavy load to a less used disk.

Method: 2
# iostat -d 5 5
Note: iostat will display the number of kilobytes transferred per second, the number of transfers per second, and the milliseconds per average seek.
Tips: KPS rates over 30 indicate heavy usage of a particular disk. If only one disk shows heavy usage, consider moving some of your datafiles off it or striping your data across several disks.

Method: 3
# sar -b 5 5
15:52:57 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s
15:53:12 0 2 90 1 2 38 0 0
Note: The "-b" option indicates the overall health of the IO subsystem.


Tips: The %rcache should be greater than 90% and %wcache should be greater than 60%. If this is not the case, your system may be bound by disk IO. The sum of bread, bwrit, pread, and pwrit gives a good indicator of how well your file subsystem is doing. The sum should not be greater than 40 for 2 drives and 60 for 4-8 drives. If you exceed these values, your system may be IO bound.
When analyzing disk IO, make sure that you have balanced the load on your system. Here is a tips of steps for designing a disk layout for Oracle:

• Make sure that your logfiles and archived logfiles are NOT on the same disk as your datafiles. This is a basic safety precaution against disk failure.
• Allocate one disk for the User Data Tablespace.
• Place Rollback, Index, and System Tablespaces on separate disks.

Monitor Process which is using the most CPU
Use following command:
# ps -e -o pcpu,pid,user,args | sort -k 0,0 -r

This command list the %CPU used , PID, USER and Command that was executed. if the top user was Oracle User, you must to know the information on the process form oracle.

use following query for that:
SQL> select a.username,a.osuser,a.program, spid,sid,a.serial# from v$session a,v$process b where a.paddr=b.addr and spid='&pid' ;
Enter value for pid: 17929
old 3: and spid='&pid'
new 3: and spid='17929'

USERNAME OSUSER PROGRAM SPID SID SERIAL#
APPS applprod 17929 17 28394

APPS applprod 17929 192 20763

APPS applprod 17929 108 17788

SQL> select b.username,a.sql_text from v$sql a,v$session b where b.sql_address = a.address and b.sql_hash_value = a.hash_value and b.sid = '&SID' and b.serial# = '&PROCESS'

Tips: Enter SID and Serial# because the values retrieved in the first query.
SQL>select b.username,a.sql_text from v$open_cursor a, v$session b where b.sql_sddress = a.address and b.sql_hash_value=a.hash_value and b.sid= '&SID' and b.serial# = '&PROCESS'

Tips: If you had a ad-hoc query user problem and problem queries showed up in this result regularly.


Identify CPU Bottlenecks
Use following Command:

# mpstat 10 5
The mpstat command is tool that report per-processor statistics in tabular form. Each row of the table represent the activity of one processor.
Pay attention on smtx measurement, it measure the number of times the CPU faild to obtain a mutual exclusion lock.

TIPS: If the smtx column for the mpstat output is greater than 200, you are heading toward CPU bottleneck problems.
Monitoring Paging/Swaping

One of the most common problems when running large numbers of concurrent users on UNIX machines is lack of memory. In this case, a quick review memory management is useful to see what effect lack of RAM can have on performance.

When analyzing your machine, make sure that the machine is not swapping at all and at worst paging lightly. This indicates a system with a healthy amount of memory available. To analyze paging and swapping, use the following commands.


$ vmstat 5 5
procs memory page disk faults cpu
r b w avm fre re at pi po fr de sr d0 s1 d2 s3 in sy cs us sy id
0 0 0 0 1088 0 2 2 0 1 0 0 0 0 0 0 26 72 24 0 1 98

Note: There are NO pageouts (po) occurring on this system. There are also 1088 * 4k pages of free RAM available (4 Meg). It is OK and normal to have page out (po) activity. You should get worried when the number of page ins (pi) starts rising. This indicates that you system is starting to page.

$ sar -wpg 5 5
09:54:29 swpin/s pswin/s swpot/s pswot/s pswch/s
atch/s pgin/s ppgin/s pflt/s vflt/s slock/s
pgout/s ppgout/s pgfree/s pgscan/s %s5ipf
09:54:34 0.00 0.0 0.00 0.0 12
0.00 0.22 0.22 0.65 3.90 0.87
0.00 0.00 0.00 0.00 0.00


Note: There is absolutely no swapping or paging going on. (swpin,swpot,ppgin,ppgout).

$ sar -r 5 5
10:10:22 freemem freeswp
10:10:27 790 5862


This will give you a good indication of how much free swap and RAM you have on your machine. There are 790 pages of memory available and 5862 disk blocks of SWAP available.

USEFUL WEBSITES........

Migration
http://www.databasejournal.com/features/oracle/article.php/3581481
----------(migration from 9i rac to 10g rac)
http://www.dbasupport.com/oracle/ora10g/RAC_9i_to_10g01.shtml
------------(migration from 9i rac to 10g rac)

RAC:
https://www.metalink.oracle.com/metalink/plsql/f?p=130:14:1439445924588336520::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,254815.1,1,1,1,helvetica
----------------(installation of oracle9iRAC)
http://www.oracle.com/technology/products/database/clustering/index.html
http://www.oracle.com/database/rac_home.html
http://www.oracle.com/technology/pub/articles/chan-ubl-vmware.html
http://www.youtube.com/watch?v=_t7vmuDpdqI&mode=related&search=
http://www.4shared.com/network/search.jsp?sortType=1&sortOrder=1&sortmode=2&searchDCId=20&searchName=oracle&searchmode=2&searchName=oracle&searchDescription=&searchExtention=&start=40
http://www.oracle.com/technology/pub/articles/hunter_rac10gr2.html



Tuning:
http://www.quest-pipelines.com/newsletter-v5/0304_A.htm (tuning the pga_aggregate_target)
http://www.dbtune.com/oracle_tuning_scripts_free.html (PT tuning scripts)

Installation:
http://sabdarsyed.blogspot.com/2007_02_01_archive.html (oracle9.2.0.1 to 9.2.0.7)
http://www.dbasupport.com/oracle/ora9i/cookbook1_2.shtml (step by step installation of oracle 9.2.0.1 on solaris)
http://download.oracle.com/docs/html/A96167_01/toc.htm (Oracle9i Installation Guide Release 2 (9.2.0.1.0) for UNIX Systems: AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, and Sun Solaris)
Installation of Oracle clustware 10g2 on SUN Sparc (64b SunOS 5.8)


dbms_job
1. http://www.lifeaftercoffee.com/2006/02/15/more-job-scheduling-examples-with-dbms_job/
2. http://www.pafumi.net/dbms_job.htm
3. http://www.dba-oracle.com/tips_oracle_dbms_job.htm
4. http://www.praetoriate.com/oracle_tips_dbms_jobs.htm
5. http://www.jaredstill.com/content/dbms-job.html



Use full Web sites and forums


(Best Site ) http://www.animatedlearning.com/courseware/index.htm
http://www.skillbuilders.com/Tutorials-V2/SkillBuilders%20oracle%20Tutorials.cfm
asktom.oracle.com
www.petefinnigan.com
www.dbspecialists.com
www.orafaq.com
www.ixora.com.au
www.puschitz.com
www.bijoos.com/oracle
www.lazydba.com
www.oracle-base.com
www.dbapool.com/
http://www.dba-oracle.com/
http://www.ss64.com/ora/
www.dbahub.com
www.idevelopment.info
http://www.dbazine.com/oracle
www.ee.surrey.ac.uk/Teaching/Unix
http://dizwell.com/main/content/view/103/243/
http://www.oracle.com/technology/pub/articles/garg-netapp-simulator.html
http://dba.fyicenter.com/article/
http://www.oracle.com/technology/products/dataint/htdocs/streams_fo.html
http://www.kebook.com/5-1.htm
http://www.itbooks.msk.ru/
http://www.flaxstor.com/
http://www.oracle.com/technology/documentation/index.html
http://novian.web.ugm.ac.id/oracle.php
http://downloadeast.oracle.com/docs/cd/B10501_01/server.920/a96652/ch10.htm#1006689(sqlloader)
http://www.studentshangout.com/
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=50------
----------ocm
www.samoratech.com/TopicOfInterest/swCloneDB.htm
http://www.darklab.net/resources/oracle-mailinglist/author.html
http://www.akadia.com/services/ora_locks_survival_guide.html
http://www.skillbuilders.com/






Dataguard
Metalink note Note:132991.1 gives you the step by step installation on dataguard.
Notes
Note:189929.1

DBA’s personal oracle docs

http://hosted.filefront.com/frmchandan/
http://hosted.filefront.com/sachintugnayat/
http://hosted.filefront.com/sureshgnvs/
http://www.esnips.com/web/santosh1002sBusinessFiles?docsPage=1#files
http://www.esnips.com/web/santosh1002sBusinessFiles
www.more-database.blogspot.com
http://oracle-online-help.blogspot.com/index.html
http://www.ramsora.blogspot.com
http://oraclerojak.blogspot.com/2007/05/1z0-043-sample-questions.html
http://oraclerojak.blogspot.com/2007/05/1z0-042-and-1z0-043-sample-questions.html
http://rainbowonnet.blogspot.com/
http://rainbowonnet.blogspot.com/2006/12/important-hr-interview-questions-part-1.html
http://hosted.filefront.com/sureshgnvs/
http://sabdarsyed.blogspot.com
http://www.sadikhov.com/forum/index.php?s=7ed8dd3531d949165139b3de138d64d6&showforum=43
http://www.oracle.ask2ask.com/
http://oracle.ask2ask.com/
http://www.orcl-experts.info/
http://studentsmasti.net/index.php?board=21.40
http://www.ramsora.blogspot.com/
http://www.dizwell.com/prod/
http://askanantha.blogspot.com/2007/04/introduction-to-oracle-real-application.html
http://appsdbablog.com/blog/2006/12/top_50_racrelated_acronyms.html


Entertain:
http://www.kannadaaudio.com/home/index.php
http://www.musicindiaonline.com/
http://www.udbhava.com/udbhava/albums.jsp?id=3
http://www.musicindiaonline.com/
http://www.kannadaaudio.com/Songs/
http://contest25.blogspot.com/



Oracle 10g

http://www.flazx.com/search.php?p=ORacle+10g&s=25v |10g
http://www.flazx.com/ebook3361.php |10g
http://www.flazx.com/ebook3362.php |10g
http://www.flazx.com/ebook3363.php |10g
http://www.oracle-base.com/articles/10g/OracleDB10gR2RACInstallationOnWindows2003UsingVMware.php
http://www.oracle.com/technology/obe/obe10gdb/install/9i10g/9i10g.htm--upgrading

DYNAMIC VIEWS........

Oracle's V$ Views

To be finished...

The following views are part of the data dictionary.

See also Oracle's x$ tables

v$archive_dest
Shows all archived redo log destinations. Use this view to find out to which place archived redo logs are copied: select dest_id,destination from v$archive_dest
These values correspond to the init parameter log_archive_dest_n.

v$archive_dest_status
This view allows to find status and errors for each of the defined

v$archived_log
Displays successfully archived redo logs.
shows received logs on a primary standby database.

v$archive_gap
Lists sequence numbers of the archived los that are known to be missing for each thread on a (physical?) standby database (highest gap only).

v$archive_processes
This view provides information on the archive processes. It can be used to find out if an ARCH process is active or not.

v$controlfile
Displays the location and status of each controlfile in the database.
v$controlfile_record_section
See sections in a controlfile.

v$bh
This dynamic view has an entry for each block in the database buffer cache.
The column status can be:
• free
This block is not in use
• xcur
Block held exclusively by this instance
• scur
Block held in cache, shared with other instance
• cr
Block for consistent read
• read
Block being read from disk
• mrec
Block in media recovery mode
• irec
Block in instance (crash) recovery mode

v$buffer_pool
See buffer pools.
This view's column id can be joined with x$kcbwds.indx
See also x$kcbwbpd

v$buffer_pool_statistics
v$database
This view lets you access database information. For example, you can check (using log_mode) whether or not the database is in archivelog mode:
ADPDB>select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG
checkpoint_change# records the SCN of the last checkpoint.
switchover_status: can be used to determine if it is possible to perform a switchover operation Only available for physical standby databases. Can be:
• NOT ALLOWED,
• SESSIONS ACTIVE,
• SWITCHOVER PENDING,
• SWITCHOVER LATENT,
• TO PRIMARY,
• TO STANDBY or
• RECOVERY NEEDED.
See protection modes in data guard for the columns protection_mode and protection_level.
database_role determines if a database is a primary or a logical standby database or a physical standby database.
force_logging tells if a database is in force logging mode or not.

v$datafile
This view contains an entry for each datafile of the database.
This view can be used to find out which datafiles must be backed up in a cold backup: select name from v$datafile

v$datafile_header
Various information about datafile headers. For example, if you're interested in when the a file's last checkpoint was:
select name, checkpoint_change#, to_char(checkpoint_time, 'DD.MM.YYYY HH24:MI:SS') from v$datafile_header

v$dataguard_status
Shows error messages in a data guard environment.


v$db_object_cache
This view displays objects that are cached (pinned) in the library cache. See also dbms_shared_pool.


v$enqueue_stat
If there are a lot of enqueue waits "in" v$session_event or v$system_event, v$enqueue_stat allows to break down those enqueues in enqueue classes. For each such class, the gets, waits, failures and the cumulative sum of waited time can be found.
For a list of enqueue types, refer to enqueue types in x$ksqst.
The column cum_wait_time stems from x$ksqst.ksqstwtim.

v$eventmetric
This view is new in Oracle 10g and allows improved timing and statistics.

v$event_name
Contains a record for each wait event.

v$filemetric
This view is new in Oracle 10g and allows improved timing and statistics.

v$filestat
v$fixed_table
This view contains the name of all V$, X$ and GV$ tables. In oracle 8.1.7, there are 187 different v$ tables:
ORA81> select count(*) from v where name like 'V$%';

COUNT(*)
----------
185
If you want to know, which x$ tables there are, do a select name from v$fixed_table where name like 'X$%';

v$fixed_view_definition
Contains the defintion in its attribute view_definition for the views of v$fixed_table.

v$instance
instance_role can be used to determine if an instance is an active instance (=primary instance) or a secondary instance (in a standby environment.
dbms_utility.db_version can be used to retrieve the same version as the field version in v$instance.

v$latch
Oracle collects statistics for the activity of all latches and stores these in this view. Gets is the number of successful willing to wait requests for a latch. Similarly, misses is how many times a process didn't successfully request a latch. Spin_gets: number of times a latch is obtained after spinning at least once. Sleeps indicates how many times a willing to wait process slept. Waiters_woken tells how often a sleeping process was 'disturbed'.

v$librarycache

v$lock
This view stores all information relating to locks in the database. The interesting columns in this view are sid (identifying the session holding or aquiring the lock), type, and the lmode/request pair.
Important possible values of type are TM (DML or Table Lock), TX (Transaction), MR (Media Recovery), ST (Disk Space Transaction).
Exactly one of the lmode, request pair is either 0 or 1 while the other indicates the lock mode. If lmode is not 0 or 1, then the session has aquired the lock, while it waits to aquire the lock if request is other than 0 or 1. The possible values for lmode and request are:
• 1: null,
• 2: Row Share (SS),
• 3: Row Exclusive (SX),
• 4: Share (S),
• 5: Share Row Exclusive (SSX) and
• 6: Exclusive(X)
If the lock type is TM, the column id1 is the object's id and the name of the object can then be queried like so: select name from sys.obj$ where obj# = id1
A lock type of JI indicates that a materialized view is being refreshed.
A more detailed example can be found here
See also x$kgllk.

v$locked_object
Who is locking what:
select
oracle_username
os_user_name,
locked_mode,
object_name,
object_type
from
v$locked_object a,dba_objects b
where
a.object_id = b.object_id
v$log
Contains information on each log group. See also online redo log.
Comman values for the status column are:
• UNUSED:
Oracle8 has never written to this group,
• CURRENT:
This is the active group.
• ACTIVE:
Oracle has written to this log before, it is needed for instance recovery.
The active log is the one with the current log sequence number
• INACTIVE:
Oracle has written to this log before; it is not needed for instance recovery.
v$logfile
This view can be queried to find the filenames, group numbers and states of redo log files. For example, to find all files of group 2, use select member from v$logfile where group# = 2

v$logmnr_contents
See dbms_logmnr.

v$log_history
This view contains an entry for each Log Switch that occured. The column first_time indicates the time of the first entry???
On physical standby databases, this view shows applied logs.

v$logstdby
Can be used to verify that archived redo logs are being applied to standby databases.
v$managed_standby
Monitors the progress of a standby database in managed recovery mode, more exactly, it displays information about the activities of log transport service and log apply service.
see here
select process, pid, status, client_process, group# "Stdby Redo Log Gr", block# from v$managed_standby;
client_process: the corresponding primary database process. If lgwr log transmission is chosen, one row should have client_process=LGWR. If ARCH transmission is chosen, one row should have ARCH.

v$mystat
This view records statistical data about the session that accesses it. Join statistic# with v$statname.

v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates.
See also recording statistics with oracle.

v$nls_parameters
The NLS parameters that are in effect for the session quering this view. The view NLS_SESSION_PARAMETERS is based on v$nls_parameters. See also v$nls_valid_values.
v$nls_valid_values
This view can be used to obtain valid values for NLS parameters such as
• supported character sets
• languages
• territories
• sorting orders

v$object_usage
v$object_usage gathers information about used (accessed) indexes when an index is monitored using alter index ... monitoring usage.
See On verifying if an index is used.


v$open_cursor
v$option
This view lets you see which options are installed in the server.
See also dba_registry.

v$parameter
Lists the name-value pairs of the init.ora file (or their default, if not in the init.ora). For example, if you need to know what your block size is:
select value from v$parameter where name = 'db_block_size'
The columns isses_modifiable and issys_modifiable can be used to determine if a parameter can be changed at session level using alter session or at system level using alter system. A parameter is modifiable at session level if isses_modifiable = 'TRUE'. A parameter is modifiable at system level if issys_modifiable = 'DEFERRED' or issys_modifiable = 'IMMEDIATE'. However, if a parameter is changed at system level if issys_modifiable = 'DEFERRED' it only affects sessions that are started after chaning the parameter. Additionally, the alter system set ... deferred option must be used.
There are also some undocumented (or hidden?) parameters.

v$pgastat
See also pga.
Thanks to Oleg who notified me of a typo (v$pgastat instead of v$pga_stat).

v$process
Join v$process's addr with v$session paddr.
The column traceid is equal to the value used in alter session set .

v$pwfile_users
Lists all users who have been granted sysdba or sysoper privileges. See adding user to a password file.

v$recover_file
Useful to find out which datafiles need recovery.
Join with v$datafile to see filenames instead of numbers....

v$reserved_words
This view can be consulted if one is in doubt wheter a particular word is a reserved word (for example when writing PL/SQL Code or assigning a password to a user).
Until 9i (is this correct?), the view only consist of two columns: keyword and length. From 10g onwards (?), it has also the columns reserved, res_type, res_attr, res_semi and duplicate. Each of these new columns can only be either 'Y' (meaning: yes) or 'N' (meaning: no)

v$resource_limit
v$rollname
The names of online rollback segments. This view's usn field can be joined with v$rollstat's usn field and with v$transaction's xidusn field.
v$transaction can be used to track undo by session.

v$rollstat
Statistics for rollback segements

v$session
The column audsid can be joined with sys_context('userenv','SESSIONID') to find out which session is the "own one". Alternatively, dbms_support.mysid can be used.
The fields module and action of v$session can be set with dbms_application_info.set_module. (See v$session_longops for an example.
The field client_info can be set with dbms_application_info.set_client_info
Join sid with v$sesstat if you want to get some statistical information for a particular sesssion.
A record in v$session contains sid and serial#. These numbers can be used kill a session (alter system kill session).
What a session is waiting for can be queried with v$session_wait. However, with Oracle 10g, this is not nessessary anymore, as v$session_wait's information will be exposed within v$session as well.
See also sessions.

v$sessmetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$session_event
This views is similar to v$system_event. However, it breaks it down to currently connected sessions.
v$session_event has also the column max_wait that shows the maximum time waited for a wait event.

v$session_longops
Use v$session_longops if you have a long running pl/sql procedure and want to give feedback on how far the procedure proceeded.
If the following Procedure is run, it will report its progress in v$session_longops. The Procedure will also set the module attribute in v$session which makes it possible to find the sid and serial# of the session.
create table f(g number);

create or replace procedure long_proc as
rindex pls_integer := dbms_application_info.set_session_longops_nohint;
slno pls_integer;
-- Name of task
op_name varchar2(64) := 'long_proc';

target pls_integer := 0; -- ie. The object being worked on
context pls_integer; -- Any info
sofar number; -- how far proceeded
totalwork number := 1000000; -- finished when sofar=totalwork

-- desc of target
target_desc varchar2(32) := 'A long running procedure';

units varchar2(32) := 'inserts'; -- unit of sofar and totalwork
begin

dbms_application_info.set_module('long_proc',null);

dbms_application_info.set_session_longops (
rindex,
slno);

for sofar in 0..totalwork loop

insert into f values (sofar);

if mod(sofar,1000) = 0 then
dbms_application_info.set_session_longops (
rindex,
slno,
op_name,
target,
context,
sofar,
totalwork,
target_desc,
units);

end if;

end loop;
end long_proc;
If the procedure long_proc is run, you can issue the following query to get feedback on its progress:
select time_remaining,sofar,elapsed_seconds
from v$session_longops l, v$session s
where l.sid=s.sid and l.serial# = s.serial# and s.module='long_proc'
v$session_wait
This views shows what wait event each session is waiting for, or what the last event was that it waited for.
In contrast, v$session_event lists the cumulative history of events waited for in a session.
The columns P1, P2 and P3 are parameters that are dependant on the event. With Oracle 10g, v$session_wait's information will be exposed within v$session as well.
Since 10g, Oracle displays the v$session_wait information also in the v$session view.

v$session_wait_history
This view is new in Oracle 10g and allows improved timing and statistics.
v$sesstat
This view is similar to v$mystat except that it shows cumulated statistics for all sessions.
Join sid with v$session and join statistic# with v$statname.
v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates.

v$sga
Shows how much memory the shared global area uses. Selecting * from v$sga is roughly the same as typing show sga in sql plus with the exeption that the latter also show the total.


v$sgastat
Showing free space in the sga:
select * from v$sgastat where name = 'free memory'
v$sga_dynamic_components
Information about SGA resize operations since startup.
This view can also be used to find out the granule size of SGA components.


v$sga_resize_ops
v$sort_usage
See temporary tablespaces
v$sort_segment
See Temporary Tablespaces

v$spparameter
Returns the values for the spfile.
v$sql
v$sql is similar to v$sqlarea, the main difference being that v$sql drills down to select * from x$kglob whereas v$sqlarea drills down to select sum from x$kglob. See also here.
v$sqlarea
Join v$sqlarea's address with v$session's sql_address.
Find the sql text of currently running sql statements:
select sql_text from v$sqlarea where users_executing > 0;
The field version_count indicates how many versions an sql statement has.
v$sqltext
v$sql_plan
variable addr varchar2(20)
variable hash number
variable child number

exec :addr := '&sqladdr'; :hash := &hashvalue; :child := &childno;

select lpad(' ', 2*(level-1))||operation||' '||
decode(id, 0, 'Cost = '||position) "OPERATION",
options, object_name
from v$sql_plan
start with (address = :addr
and hash_value = :hash
and child_number = :child
and id=0 )
connect by prior id = parent_id
and prior address = address
and prior hash_value = hash_value
and prior child_number = child_number
order by id, position ;
In order to find valid values for sqladdr, hashvalue and childno, this SQL statement can be used:
select sql_text,address,hash_value,child_number from v$sql where users_executing > 0;
v$sql_text_with_newlines
This view can be used to construct the entire text for each session's actual SQL statement. Use the following statement to to that:
set serveroutput on size 1000000

declare
v_stmt varchar2(16000);
v_sql_text v$sqltext_with_newlines.sql_text%type;
v_sid v$session.sid%type;
begin
for r in (
select
sql_text,s.sid
from
v$sqltext_with_newlines t,
v$session s
where
s.sql_address=t.address
order by s.sid, piece) loop

v_sid := nvl(v_sid,r.sid);

if v_sid <> r.sid then
dbms_output.put_line(v_sid);
put_line(v_stmt,100);
v_sid := r.sid;
v_stmt := r.sql_text;
else
v_stmt := v_stmt || r.sql_text;
end if;

end loop;
dbms_output.put_line(v_sid);
dbms_output.put_line(v_stmt,100);

end;
/

Thanks to Sarmad Zafar who notified me of an error in this PL/SQL Block.
Note: the function put_line is found here and can be used to prevent ORU-10028.
v$sql_bind_data
Join cursor_num with cno of v$sql_cursor.

v$sql_bind_capture
New with Oracle 10g
This view captures bind variables for all sessions and is faster than setting 10046 on level 4.

v$sql_cursor
Join parent_handle with address of v$sql or v$sqlarea.

v$sql_workarea
v$sql_workarea can be joined with v$sqlarea on address and hash_value, and it can be joined with v$sql on address, hash_value and child_number.
v$standby_log

v$statname
Use this view to get decoded names for the statistic# field of v$mystat, v$sysstat and v$sesstat.
v$sysaux_occupants
v$sysaux_occupants doesn't exist in Oracle versions prior to Oracle 10g.
See occupants in the sysaux tablepsaces.
v$sysmetric
This view is new in Oracle 10g and allows improved timing and statistics.

v$sysmetric_history
This view is new in Oracle 10g and allows improved timing and statistics.

v$sysstat
v$sysstat is similar to v$sesstat. While v$sesstat displays statitics for the current session, v$sysstat displays the cumulated statitics since startup of the database.
For example, it is possible to find out the CPU time (name = 'CPU used by this session')
This view is (among others) used to calculate the Hit Ratio.

v$system_event
This view displays the count (total_waits) of all wait events since startup of the instance.
If timed_statistics is set to true, the sum of the wait times for all events are also displayed in the column time_waited.
The unit of time_waited is one hundreth of a second. Since 10g, an additional column (time_waited_micro) measures wait times in millionth of a second.
total_waits where event='buffer busy waits' is equal the sum of count in v$waitstat.
v$enqueue_stat can be used to break down waits on the enqueue wait event.
While this view totals all events in an instance, v$session_event breaks it down to all currently connected sessions.

v$undostat
undo tablespaces
v$tempfile
v$tempseg_usage
v$tempseg_usage is a public synonym for v$sort_usage.
v$tempstat
v$thread

The Oracle SID can be retrieved through select instance from v$thread
v$timer


This view has only one column (hsecs) which counts hundreths of seconds. Whenever it overflows four bytes, it starts again with 0.
v$transaction

Important fields of v$transaction are used_ublk and used_urec. They tell of how many blocks and records the undo for a transaction consists. In order to find out the name of the corresponding rollback segemnt, join the xidusn field with the usn field of ##A(v$rollname,#rollname). This is demonstrated in
Transactions generate undo

v$timezone_names
See also timezones for some values of tzabbrev.
v$transportable_platform
Which platforms are supported for cross platform transportable tablespaces.
v$version
Use this view to find out what version you actually work on:

select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

v$waitstat
total_waits where event='buffer busy waits' is equal the sum of count in v$system_event.

GENERAL-DAILY USE SQL QUERIES.....

1.select * from gv$license

2.select sid,serial#,status,program,terminal,machine,username from v$session where sql_hash_value='&value';

3.ALTER SYSTEM KILL SESSION 'sid,serial#'

4.cat alert_bvsbi1.log |grep ORA

5. col program for a25
col machine for a20
col terminal for a20
select program,machine,terminal,username,serial#,status from v$session where sid='&sid';

7.select * from v$lock where request>0 or block=1

8.select * from dba_blockers

9.select * from dba_waiters

10.select * from v$access where object='&tblename';

11.select SID,SERIAL#,MACHINE,program,TERMINAL,USERNAME from v$session where status='ACTIVE';

12.select p1 "file#",p2 "block#",p3 "reason code" from v$session_wait where event = 'buffer busy waits';
select sid from v$session where audsid=userenv('SESSIONID');

13.select sid,message from v$session_longops where sid='&sid' order by start_time;


14.alter session set nls_date_format = 'DD-MM-YY HH24:MI';


15.select a.sid,a.serial#,a.status,a.schemaname,a.osuser,a.username,a.machine,a.program,to_char(logon_time,'DD-HH24:MI:SS')
from v$session a,v$process b where a.paddr=b.addr and b.spid=&spid

16.select sequence#,applied,status,registrar from v$archived_log where thread#='&1'

select tablespace_name,sum(bytes/1024/1024) from sm$ts_used group by tablespace_name


select sid,username,machine,terminal,LOGON_TIME from v$session where username not in ('BVSBI','SYS','WAC') and status='ACTIVE';

-------------------------------------------------------------------------------------------------------------------------------

SPACE & TABLESPACE:

1.alter tablespace add datafile '/disk01/oradata01/bvsbi/ycs_data_01_03.dbf' size 1920

2.alter database datafile '/disk01/oradata02/bvsbi/sbi_filedata_hist_part_01_04.dbf' resize 1500m

3.select tablespace_name,sum(bytes)/1024/1024/1024 from dba_free_space where tablespace_name='&tbs' group by tablespace_name;

4.select file_name,bytes/1024/1024 from dba_data_files where tablespace_name='&tbs'

select tablespace_name,bytes_free/1024/1024,file_id from v$temp_space_header;

SELECT INDEX_NAME,TABLESPACE_NAME FROM USER_INDEXES WHERE TABLE_NAME='&TBL';



---------------------------------------------------------------------------------------------------------------------------------
DATAGUARD:

1.alter database recover managed standby database cancel

2.alter database recover managed standby database disconnect from session

3.select * from v$archive_gap

4.desc v$dataguard_status;
5.desc v$archive_dest;
6.desc v$archive_dest_status;
7.desc v$standby_log;
8.desc v$managed_standby;
9.select standby_logfile_count,standby_logfile_active from v$archive_dest_status;
10.select activation#,archivelog_change#,database_role,remote_archive,switchover_status from v$database;
select type from v$logfile;
select status from v$log;
select applied,backup_count,completion_time,creator,deleted,dest_id,dictionary_begin,dictionary_end,registrar,standby_dest,status,
end_of_redo,archival_thread# from v$archived_log;
select protection_mode from v$database;
select manifest,affirm,alternate,archiver,async_blocks,delay_mins,dependency,failure_count,max_failure,mountid,process,quota_size,quota_used,
register,schedule,transmit_mode,type from v$archive_dest;
select net_timeout,type from v$archive_dest;
select protection_mode,srl from v$archive_dset_status;
select guard_status,SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,FORCE_LOGGING,PROTECTION_LEVEL from v$database;


SELECT max(sequence#) from v$archived_log where applied='YES';

SELECT min(sequence#) FROM v$archived_log WHERE ( (sequence#+1) NOT IN
(SELECT sequence# FROM v$archived_log) ) AND (sequence# > laseq)

select * from v$archive_gap

select message from v$dataguard_status;

ALTER DATABASE RECOVER managed standby database ;
or
ALTER DATABASE RECOVER managed standby database nodelay disconnect;

SELECT PROTECTION_MODE FROM V$DATABASE;

ALTER DATABASE RECOVER automatic standby database;



----------------------------------------------------------------------------------------------------------------------------------

SEGMENT MANAGEMENT

1.select last_analyzed,TABLE_NAME FROM dba_tables where owner='&own' ORDER BY LAST_ANALYZED DESC;

2.select object_name,last_ddl_time from dba_objects where owner='&OWN' AND OBJECT_TYPE='TABLE' ORDER BY LAST_DDL_TIME DESC;

3.col owner for a20
col index_name for a20
select segment_name,segment_type,owner,sum(bytes/1024/1024)
from dba_segments
where
owner='&own'
order by bytes desc

select segment_name,segment_type,owner,bytes/1024/1024 from dba_segments
where segment_type='TABLE' and segment_name='SBICORP_ECHEQUE_MASTER'

4.SELECT SUM(BYTES) FROM USER_SEGMENTS WHERE SEGMENT_TYPE='TABLE';

SELECT SEGMENT_NAME,BYTES/1024/1024/1024 FROM USER_SEGMENTS WHERE SEGMENT_NAME='&TBL';
select name from sys.obj$ where obj# = 4145

select a.segment_name,a.bytes/1024/1024/1024,b.created from user_segments a,user_objects b where a.segment_name=b.object_name and b.last_ddl_time>='01-AUG-07' and b.object_type='TABLE'


--------------------------------------------------------------------------------------------------------------------------------------
PARTITIONS:

1.DESC user_tab_partitions;

2.user_part_tables;

select count(1) from SBI_FILE_DATA_HISTORY partition(DEC2006);

select table_name,PARTITION_NAME from user_tab_partitions where table_name='SBI_FILE_DATA_HISTORY';





WEBSITES:
http://www.orsweb.com/.
------------------------------------------------------------------------------------------------------------------------------------
EXPORT & IMPORT

1.imp backup/backup file=/disk02/backup01/expdump/Oct31_full_morn.dmp fromuser=YCSSTEP touser=YCSSTEP TABLES=REVERSAL ignore=y

2.exp backup/backup file=BVSBI.SBICORP_ECHEQUE_MASTER.dmp log=SBICORP_ECHEQUE_MASTER.log

tables=SBICORP_ECHEQUE_MASTER
exp bvsbi/ FILE=sbi_bp_biller_master.dmp log=sbi_bp_biller_master.log tables=bvsbi.sbi_bp_biller_master GRANTS=y INDEXES=y statistics=none buffer=60000000


----------------------------------------------------------------------------------------------------------------------------------------
USERS & PREVILEGES
1.grant all on sbi_txn_history_irctc to INTSBI
select GRANTEE,TABLE_NAMe,PRIVILEGE,GRANTABLE from dba_tab_privs where owner = 'WAC';
select grantee,table_name,grantor,PRIVILEGE,grantable from dba_tab_privs where owner='YCSSTEP';


---------------------------------------------------------------------------------------------------------------------------------------
INDEX:

1.col table_name for a20
select owner,index_name,table_name from dba_indexes where tablespace_name='&tbs';

2.analyze index SBICORP_RULE_MASTER_ACCNO_IDX compute statistics;

3.create index SBICORP_RULE_MASTER_STATUS_IDX on SBICORP_RULE_MASTER(STATUS)
TABLESPACE SMALL_MED_INDEX

4.select index_name,table_name,column_name from user_ind_columns where table_name='&tablename';

5.select index_name,TABLESPACE_NAME from dba_indexes where table_name='&tbname' and owner='&own';

6.select index_name,table_name,owner from dba_indexes where tablespace_name='&tbs';

SELECT index_name, column_name FROM user_ind_columns WHERE table_name ='&tb';


select table_name from all_indexes where index_name='&indname';

select * from all_ind_columns where index_name='&indname';

create index var_area_45_5_indx_name on inbs(substr(var_area,45,5)) tablespace USERS(function based index)
---------------------------------------------------------------------------------------------------------------------------------------
Jobs:

1. where job=746;


2. select sid from dba_jobs_running where job='&no';


ALTER SYSTEM SET job_queue_processes=10 SCOPE=BOTH
exec dbms_job.enable()
exec dbms_job.disable()

select job,LAST_DATE,NEXT_DATE, BROKEN,INTERVAL,FAILURES,INSTANCE,WHAT from dba_jobs where job=746;

select text from dba_source where name like 'OFFLINE%' and owner = 'BVSBI';

--------------------------------------------------------------------------------------------------
constraints

SELECT constraint_name FROM user_constraints WHERE table_name = '&tb' AND constraint_type = 'P'

select table_name, column_name from all_cons_columns where owner='&own' and constraint_name='&consname' order by position;


Audit:

1.desc sbi_user_login;

scp arch_1_7867.arc.Z oracle@10.192.24.66:/disk02/arch02/standby_archive/


exec dbms_stats.create_stat_table (ownname=>'BVSBI',stattab=>'STAGEDBBVSBISTATS');

DBA_VIEWS
1.DBA_ROLES
DBA_ROLE_PRIVS
DBA_SYS_PRIVS
ROLE_ROLE_PRIVS
ROLE_TAB_PRIVS
SESSION_ROLES
ROLE_SYS_PRIVS
DBA_SYS_PRIVS
SESSION_PRIVS
DBA_TAB_PRIVS
ALL_TAB_PRIVS
ALL_TAB_PRIVS_MADE
ALL_TAB)PRIVS_RECD
ALL_COL_PRIVS
DBA_COL_PRIVS
ALL_COL_PRIVS_MADE_RECD
DBA_PROFILES
USER_PASSWORD_LIMITS
USER_RESOURCE_LIMITS
RESOURCE_COST
DBA_TS_QUOTAS
AUDIT_ACTIONS
DBA_AUDIT_OBJECT
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRIAL
DBA_AUDIT_POLICIES


create table INBS
tablespace SBI_TXN_HISTORY_ARCHIVE_TS as select * from ayyu.INBS


select dbms_metadata.get_ddl('TABLE','EMP') FROM dual

SELECT * FROM v$sqlAREA WHERE sql_text LIKE 'INSERT%SBI_FILE_DATA_HISTORY%' ;

HOW TO CHECK GROWTH OF THE DATABASE......?

Step : 1 Calculate total Size of tablespace
select sum(bytes)/1024/1024 "TOTAL SIZE (MB)" from dba_Data_files
Step : 2 Calculate Free Space in Tablespace
select sum(bytes)/1024/1024 "FREE SPACE (MB)" from dba_free_space
Step : 3 Calculate total size , free space and used space in tablespace
select t2.total "TOTAL SIZE",t1.free "FREE SPACE",(t1.free/t2.total)*100 "FREE (%)" ,(1-t1.free/t2.total)*100 "USED (%)"
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 , (select sum(bytes)/1024/1024 total from dba_Data_files) t2
Step : 4 Create table which is store all free/use space related information of tablespace
create table db_growth
as select *
from (
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE% "
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
Step : 5 Insert free space information in DB_GROWTH table (if you want to populate data Manually)
insert into db_growth
select *
from (
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
Step : 6 Create View on DB_GROWTH based table ( This Steps is Required if you want to populate data automatically)
create view v_db_growth
as select *
from
(
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)

Step : 7 Insert data into DB_GROWTH table from V_DD_GROWTH view
insert into db_growth select *
from v_db_growth
Step : 8 Check everything goes fine.
select * from db_growth;
Check Result
Step : 9 Execute following SQL for more time stamp information
alter session set nls_date_format ='dd-mon-yyyy hh24:mi:ss' ;
Session altered.
Step : 10 Create a DBMS jobs which execute after 24 hours
declare
jobno number;
begin
dbms_job.submit(
jobno, 'begin insert into db_growth select * from v_db_growth;commit;end;', sysdate, 'SYSDATE+ 24', TRUE);
commit;
end;

PL/SQL procedure successfully completed.
Step: 11 View your dbms jobs and it's other information
select * from user_jobs;
TIPS: If you want to execute dbms jobs manually execute following command other wise jobs is executing automatically
exec dbms_job.run(ENTER_JOB_NUMBER)
PL/SQL procedure successfully completed.
Step: 13 Finally all data populated in db_growth table
select * from db_growth