Wednesday, July 30, 2008
Tuesday, July 29, 2008
Monday, July 28, 2008
Friday, July 11, 2008
Script:How to check startup time for a database.
The startup time for the database can be viewed with V$INSTANCE view.
And the sql will be like this:
select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time"
from v$instance;
And the sql will be like this:
select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time"
from v$instance;
SCRIPT:To Check all Connected Users
Show all connected users:
select username, sid ',' serial# "ID",
status,last_call_et "Last Activity"
from v$session
where username is not null
order by status desc, last_call_et desc
select username, sid ',' serial# "ID",
status,last_call_et "Last Activity"
from v$session
where username is not null
order by status desc, last_call_et desc
SCRIPT:Time since last user activity in database
Time since last user activity :
select username,
floor(last_call_et / 60) " In Minutes", status
from v$session
where username is not null
order by last_call_et
select username,
floor(last_call_et / 60) " In Minutes", status
from v$session
where username is not null
order by last_call_et
SCRIPT:To check user information including OS Process_id
col username for a20
col osuser for a20
col program for a40
select a.sid ,a.serial# "SID/SERIAL",
a.username ,a.osuser, p.spid "OS PID",a.program
from v$session a, v$process p
where
a.paddr = p.addr
order by to_number(p.spid)
col osuser for a20
col program for a40
select a.sid ,a.serial# "SID/SERIAL",
a.username ,a.osuser, p.spid "OS PID",a.program
from v$session a, v$process p
where
a.paddr = p.addr
order by to_number(p.spid)
SCRIPT:To check users current sql
To check users current sql:
select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value
from v$session
where
username like '&username')
select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value
from v$session
where
username like '&username')
SCRIPT:To check Session status associated with the specified os process id
select a.username,a.sid , a.serial#,
p.spid ,last_call_et,status
from V$SESSION a, V$PROCESS p
where a.PADDR = p.ADDR
and p.spid='&pid'
p.spid ,last_call_et,status
from V$SESSION a, V$PROCESS p
where a.PADDR = p.ADDR
and p.spid='&pid'
SCRIPT:To Check All Active Sql
To check all active sql:
set feedback off
set serveroutput on size 9999
col username for a25
col sql_text format a60 word_wrapped
begin
for x in
(select username '('sid','serial#') ospid = ' process
' program = ' program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time, sql_address,
sql_hash_value
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null ) loop
for y in (select sql_text from v$sqlarea
where address = x.sql_address )
loop
if ( y.sql_text not like '%listener.get_cmd%'
and y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' )
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time ' ' x.current_time ' SQL#=' x.sql_hash_value);
dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
Note: Please check the select statement for v$session view in begin section of pl/sql block if in case its not executing and provide pipe characters to get exact results.
set feedback off
set serveroutput on size 9999
col username for a25
col sql_text format a60 word_wrapped
begin
for x in
(select username '('sid','serial#') ospid = ' process
' program = ' program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time, sql_address,
sql_hash_value
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null ) loop
for y in (select sql_text from v$sqlarea
where address = x.sql_address )
loop
if ( y.sql_text not like '%listener.get_cmd%'
and y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' )
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time ' ' x.current_time ' SQL#=' x.sql_hash_value);
dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
Note: Please check the select statement for v$session view in begin section of pl/sql block if in case its not executing and provide pipe characters to get exact results.
SCRIPT:To Display any long operations in the database Session
col username for a20
col message for a50
col remaining for 9999
select username,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started,
time_remaining remaining, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc
col message for a50
col remaining for 9999
select username,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started,
time_remaining remaining, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc
How to resize tablespaces and data files
To manually increase or decrease the size of a datafile using the command.
ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;
As you can change the sizes of datafiles, you can add more space to your database without adding more datafiles.
Manually reducing the sizes of datafiles allows you to reclaim unused space in the database. This is useful for correcting errors in estimations of space requirements.
Useful links :http://basic-oracledatabase.blogspot.com/2008/08/how-to-drop-tablespace-in-oracle.html and http://basic-oracledatabase.blogspot.com/2008/07/oracle-database-user-and-tablespace.html
ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;
As you can change the sizes of datafiles, you can add more space to your database without adding more datafiles.
Manually reducing the sizes of datafiles allows you to reclaim unused space in the database. This is useful for correcting errors in estimations of space requirements.
Useful links :http://basic-oracledatabase.blogspot.com/2008/08/how-to-drop-tablespace-in-oracle.html and http://basic-oracledatabase.blogspot.com/2008/07/oracle-database-user-and-tablespace.html
SCRIPT:Sql to Check Open Cursors in Oracle Database
Total Open Cursors By Session:
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
Total Open Cursors By username & HostMachine:
select sum(a.value) total_cur, avg(a.value) avg_cur,
max(a.value) max_cur, s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
group by s.username, s.machine order by 1 desc;
MAX allowed Cursors and total open Cursors:
select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic# and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;
--Session Cached Cursor's :
select cach.value cache_hits, prs.value all_parses,
prs.value-cach.value sess_cur_cache_not_used
from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
where cach.statistic# = nm1.statistic#
and nm1.name = 'session cursor cache hits'
and prs.statistic#=nm2.statistic#
and nm2.name= 'parse count (total)'
and cach.sid= &sid and prs.sid= cach.sid ;
---To get the % useage of all available open cursor
DECODE(value, 0, ' n/a', to_char(100 * used / value, '990') || '%' ) usage
FROM ( SELECT MAX(s.value) used
FROM v$statname n, v$sesstat s
WHERE
n.name = 'session cursor cache count' and
s.statistic# = n.statistic# ),
(SELECT value
FROM v$parameter
WHERE
name = 'session_cached_cursors' )
UNION ALL
SELECT 'open_cursors', LPAD(value, 5), to_char(100 * used / value, '990') || '%'
FROM
(SELECT MAX(sum(s.value)) used
FROM v$statname n, v$sesstat s
WHERE
n.name in ('opened cursors current', 'session cursor cache count') and s.statistic#=n.statistic#
GROUP BY
s.sid
),
(SELECT value
FROM v$parameter
WHERE
name = 'open_cursors' )
/
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
Total Open Cursors By username & HostMachine:
select sum(a.value) total_cur, avg(a.value) avg_cur,
max(a.value) max_cur, s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
group by s.username, s.machine order by 1 desc;
MAX allowed Cursors and total open Cursors:
select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic# and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;
--Session Cached Cursor's :
select cach.value cache_hits, prs.value all_parses,
prs.value-cach.value sess_cur_cache_not_used
from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
where cach.statistic# = nm1.statistic#
and nm1.name = 'session cursor cache hits'
and prs.statistic#=nm2.statistic#
and nm2.name= 'parse count (total)'
and cach.sid= &sid and prs.sid= cach.sid ;
---To get the % useage of all available open cursor
DECODE(value, 0, ' n/a', to_char(100 * used / value, '990') || '%' ) usage
FROM ( SELECT MAX(s.value) used
FROM v$statname n, v$sesstat s
WHERE
n.name = 'session cursor cache count' and
s.statistic# = n.statistic# ),
(SELECT value
FROM v$parameter
WHERE
name = 'session_cached_cursors' )
UNION ALL
SELECT 'open_cursors', LPAD(value, 5), to_char(100 * used / value, '990') || '%'
FROM
(SELECT MAX(sum(s.value)) used
FROM v$statname n, v$sesstat s
WHERE
n.name in ('opened cursors current', 'session cursor cache count') and s.statistic#=n.statistic#
GROUP BY
s.sid
),
(SELECT value
FROM v$parameter
WHERE
name = 'open_cursors' )
/
SCRIPT:To see the free available space in TABLESPACE’s
Free Space In Tablespace:
col tablespace_name format a20
col "size GB" format 999,999,999
col "% Used" format 999
col "free GB" format 99,999,999
select tsu.tablespace_name, ceil(tsu.used_gb) "size GB",
decode(ceil(tsf.free_gb), NULL, 0, ceil(tsf.free_gb)) "free GB",
decode(100 - ceil(tsf.free_gb/tsu.used_gb*100), NULL,
100,100 - ceil(tsf.free_gb/tsu.used_gb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024/1024 used_gb
from dba_data_files
group by tablespace_name
union all
select tablespace_name ,sum(bytes)/1024/1024/1024 used_gb
from dba_temp_files group by tablespace_name) tsu,
(select tablespace_name, sum(bytes)/1024/1024/1024 free_gb
from dba_free_space group by tablespace_name) tsf
where
tsu.tablespace_name = tsf.tablespace_name (+)
order by 4;
----TO Check Allocated Space and Quota by User:
select * from DBA_TS_QUOTAS
order by Tablespace_Name, Username;
col tablespace_name format a20
col "size GB" format 999,999,999
col "% Used" format 999
col "free GB" format 99,999,999
select tsu.tablespace_name, ceil(tsu.used_gb) "size GB",
decode(ceil(tsf.free_gb), NULL, 0, ceil(tsf.free_gb)) "free GB",
decode(100 - ceil(tsf.free_gb/tsu.used_gb*100), NULL,
100,100 - ceil(tsf.free_gb/tsu.used_gb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024/1024 used_gb
from dba_data_files
group by tablespace_name
union all
select tablespace_name ,sum(bytes)/1024/1024/1024 used_gb
from dba_temp_files group by tablespace_name) tsu,
(select tablespace_name, sum(bytes)/1024/1024/1024 free_gb
from dba_free_space group by tablespace_name) tsf
where
tsu.tablespace_name = tsf.tablespace_name (+)
order by 4;
----TO Check Allocated Space and Quota by User:
select * from DBA_TS_QUOTAS
order by Tablespace_Name, Username;
Wednesday, July 9, 2008
Basic Linux Commands
Basic Linux Commands:
| Command | Example | Description |
| cat | | Sends file contents to standard output. This is a way to list the contents of short files to the screen. It works well with piping. |
| | cat .bashrc | Sends the contents of the ".bashrc" file to the screen. |
| cd | | Change directory |
| | cd /home | Change the current working directory to /home. The '/' indicates relative to root, and no matter what directory you are in when you execute this command, the directory will be changed to "/home". |
| | cd httpd | Change the current working directory to httpd, relative to the current location which is "/home". The full path of the new working directory is "/home/httpd". |
| | cd .. | Move to the parent directory of the current directory. This command will make the current working directory "/home. |
| | cd ~ | Move to the user's home directory which is "/home/username". The '~' indicates the users home directory. |
| cp | | Copy files |
| | cp myfile yourfile | Copy the files "myfile" to the file "yourfile" in the current working directory. This command will create the file "yourfile" if it doesn't exist. It will normally overwrite it without warning if it exists. |
| | cp -i myfile yourfile | With the "-i" option, if the file "yourfile" exists, you will be prompted before it is overwritten. |
| | cp -i /data/myfile . | Copy the file "/data/myfile" to the current working directory and name it "myfile". Prompt before overwriting the file. |
| | cp -dpr srcdir destdir | Copy all files from the directory "srcdir" to the directory "destdir" preserving links (-p option), file attributes (-p option), and copy recursively (-r option). With these options, a directory and all it contents can be copied to another directory. |
| dd | dd if=/dev/hdb1 of=/backup/ | Disk duplicate. The man page says this command is to "Convert and copy a file", but although used by more advanced users, it can be a very handy command. The "if" means input file, "of" means output file. |
| df | | Show the amount of disk space used on each mounted filesystem. |
| less | less textfile | Similar to the more command, but the user can page up and down through the file. The example displays the contents of textfile. |
| ln | | Creates a symbolic link to a file. |
| | ln -s test symlink | Creates a symbolic link named symlink that points to the file test Typing "ls -i test symlink" will show the two files are different with different inodes. Typing "ls -l test symlink" will show that symlink points to the file test. |
| locate | | A fast database driven file locator. |
| | slocate -u | This command builds the slocate database. It will take several minutes to complete this command. This command must be used before searching for files, however cron runs this command periodically on most systems. |
| | locate whereis | Lists all files whose names contain the string "whereis". |
| logout | | Logs the current user off the system |
| ls | | List files |
| | ls | List files in the current working directory except those starting with . and only show the file name. |
| | ls -al | List all files in the current working directory in long listing format showing permissions, ownership, size, and time and date stamp |
| more | | Allows file contents or piped output to be sent to the screen one page at a time. |
| | more /etc/profile | Lists the contents of the "/etc/profile" file to the screen one page at a time. |
| | ls -al | more | Performs a directory listing of all files and pipes the output of the listing through more. If the directory listing is longer than a page, it will be listed one page at a time. |
| mv | | Move or rename files |
| | mv -i myfile yourfile | Move the file from "myfile" to "yourfile". This effectively changes the name of "myfile" to "yourfile". |
Instance Background Processes in Oracle Database
Instance Background Processes:
The background processes in an Oracle instance may include the following:
Archiver (ARCn)
Checkpoint (CKPT)
Database Writer (DBW0 or DBWn)
Dispatcher (Dnnn)
Lock Manager Server (LMS) - Real Application Clusters only
Log Writer (LGWR)
Process Monitor (PMON)
Queue Monitor (QMNn)
Recoverer (RECO)
System Monitor (SMON)
Server (Snnn)
These are created automatically when an instance is started, not all are present on every operating system.Each server and background process can write to an associated trace file.
DBWR
Although one database writer process (DBW0) is adequate for most systems, you can configure additional processes (DBW1 through DBW9) to improve write performance if your system modifies data heavily. On a server with just one processor - multiple DBWR processes will not improve performance (will probably make things worse).
Initialization parameter = DB_WRITER_PROCESSES
LGWR
The log writer process (LGWR) writes the redo log buffer to a redo log file on disk. If all the log files in a group are damaged, or the group is unavailable because it has not been archived, LGWR cannot continue to function.
When a user issues a COMMIT statement, LGWR puts a commit record in the redo log buffer and writes it to disk immediately, along with the transaction's redo entries. The corresponding changes to data blocks are deferred until it is more efficient to write them. This is called a fast commit mechanism.
A transaction has not been successfully committed until it's redo entry has been written to disk.
CKPT
When a checkpoint occurs the CKPT process must update the headers of all datafiles to record the details.
SMON
The system monitor process (SMON) is responsible for Crash recovery, if necessary, at instance startup. Cleaning up temporary segments that are no longer in use.Coalescing contiguous free extents within dictionary-managed tablespaces.
PMON
The process monitor (PMON) performs process recovery.When a user process fails PMON will:- Clean up the database buffer cache - Free resources that the user's process was using.- Register information about the instance and dispatcher processes with the network listener.
RECO
The recoverer process (RECO) is a background process used with the distributed database configuration, it automatically resolves failures involving distributed transactions.
Job queue
This provides a mechanism to execute user jobs. It can be viewed as a scheduler service used to schedule jobs as PLSQL statements or procedures on an Oracle instance.
ARCn
The archiver process (ARCn) copies online redo log files to disk/tape after each log switch. ARCn processes are present only when the database is in ARCHIVELOG mode.You can specify multiple archiver processes with the initialization parameter LOG_ARCHIVE_MAX_PROCESSES.
Alert log
The ALERT Log of a database is a chronological log of messages and errors.Many administrative operations will leave a completion message in the ALERT file along with a time stamp.
NOTE:This log is a very useful troubleshooting tool and should be monitored regularly
The background processes in an Oracle instance may include the following:
Archiver (ARCn)
Checkpoint (CKPT)
Database Writer (DBW0 or DBWn)
Dispatcher (Dnnn)
Lock Manager Server (LMS) - Real Application Clusters only
Log Writer (LGWR)
Process Monitor (PMON)
Queue Monitor (QMNn)
Recoverer (RECO)
System Monitor (SMON)
Server (Snnn)
These are created automatically when an instance is started, not all are present on every operating system.Each server and background process can write to an associated trace file.
DBWR
Although one database writer process (DBW0) is adequate for most systems, you can configure additional processes (DBW1 through DBW9) to improve write performance if your system modifies data heavily. On a server with just one processor - multiple DBWR processes will not improve performance (will probably make things worse).
Initialization parameter = DB_WRITER_PROCESSES
LGWR
The log writer process (LGWR) writes the redo log buffer to a redo log file on disk. If all the log files in a group are damaged, or the group is unavailable because it has not been archived, LGWR cannot continue to function.
When a user issues a COMMIT statement, LGWR puts a commit record in the redo log buffer and writes it to disk immediately, along with the transaction's redo entries. The corresponding changes to data blocks are deferred until it is more efficient to write them. This is called a fast commit mechanism.
A transaction has not been successfully committed until it's redo entry has been written to disk.
CKPT
When a checkpoint occurs the CKPT process must update the headers of all datafiles to record the details.
SMON
The system monitor process (SMON) is responsible for Crash recovery, if necessary, at instance startup. Cleaning up temporary segments that are no longer in use.Coalescing contiguous free extents within dictionary-managed tablespaces.
PMON
The process monitor (PMON) performs process recovery.When a user process fails PMON will:- Clean up the database buffer cache - Free resources that the user's process was using.- Register information about the instance and dispatcher processes with the network listener.
RECO
The recoverer process (RECO) is a background process used with the distributed database configuration, it automatically resolves failures involving distributed transactions.
Job queue
This provides a mechanism to execute user jobs. It can be viewed as a scheduler service used to schedule jobs as PLSQL statements or procedures on an Oracle instance.
ARCn
The archiver process (ARCn) copies online redo log files to disk/tape after each log switch. ARCn processes are present only when the database is in ARCHIVELOG mode.You can specify multiple archiver processes with the initialization parameter LOG_ARCHIVE_MAX_PROCESSES.
Alert log
The ALERT Log of a database is a chronological log of messages and errors.Many administrative operations will leave a completion message in the ALERT file along with a time stamp.
NOTE:This log is a very useful troubleshooting tool and should be monitored regularly
Oracle Server Parameter File management (SpFile)
Managing the SpFILE:
In Oracle9i and above, the SpFILE is stored in a binary format on the server.
You no longer need to have a local copy to start the database remotely.
This also means that changes made via ALTER SYSTEM may now persist
across server restarts - without any need to remember to update a separate init.ora file.
A PFILE (old style text init.ORA) can be created via the command:
CREATE PFILE = 'pfilename' FROM SpFILE = 'filename';
An SpFILE (binary) can be created via the command:
CREATE SpFILE = 'filename' FROM PFILE ='pfilename';
To create an SpFile, the database need not be started, the old pfile format is largely for backwards compatibility with older releases.
~If the system has both a pfile and an SpFile, then Oracle will always use the SpFile.
~If the system has both a generic init.ora and an SID specific parameter file,
Oracle will use the SID specific file.
SID specific:
spfileSID.ora or initSID.ora
Generic:
spfile.ora or init.ora
The location for all parameter files is:
ORACLE_HOME\database (Windows)ORACLE_HOME/dbs (Unix/Linux)
To startup a database with an SpFILE:
SQL> startup
To startup a database with a PFILE, use the pfile= option :
SQL> startup pfile=filename
Examples
ALTER SYSTEM SET log_archive_format = "live%S.ARC" SCOPE = spfile;
ALTER SYSTEM SET shared_pool_size=64M scope=both
SELECT name, value from v$parameter
where name = 'spfile';
show parameter spfile;
In Oracle9i and above, the SpFILE is stored in a binary format on the server.
You no longer need to have a local copy to start the database remotely.
This also means that changes made via ALTER SYSTEM may now persist
across server restarts - without any need to remember to update a separate init.ora file.
A PFILE (old style text init.ORA) can be created via the command:
CREATE PFILE = 'pfilename' FROM SpFILE = 'filename';
An SpFILE (binary) can be created via the command:
CREATE SpFILE = 'filename' FROM PFILE ='pfilename';
To create an SpFile, the database need not be started, the old pfile format is largely for backwards compatibility with older releases.
~If the system has both a pfile and an SpFile, then Oracle will always use the SpFile.
~If the system has both a generic init.ora and an SID specific parameter file,
Oracle will use the SID specific file.
SID specific:
spfileSID.ora or initSID.ora
Generic:
spfile.ora or init.ora
The location for all parameter files is:
ORACLE_HOME\database (Windows)ORACLE_HOME/dbs (Unix/Linux)
To startup a database with an SpFILE:
SQL> startup
To startup a database with a PFILE, use the pfile= option :
SQL> startup pfile=filename
Examples
ALTER SYSTEM SET log_archive_format = "live%S.ARC" SCOPE = spfile;
ALTER SYSTEM SET shared_pool_size=64M scope=both
SELECT name, value from v$parameter
where name = 'spfile';
show parameter spfile;
SCRIPT:To Display all datafiles, tempfiles and logfiles (and their sizes)
\http://www.fasttrackdba.com/
Tuesday, July 8, 2008
COLD and HOT Backups in Oracle Database
http://www.fasttrackdba.com/oracletips/coldandhotbackups.html
To put a database into ARCHIVELOG mode
Please visit http://www.fasttrackdba.com/oracletips/archivelogmode.html
Basics of import/export in Oracle Database
http://www.fasttrackdba.com/oracletips/oracleexportimport.html
Subscribe to:
Posts (Atom)
Posts
