Oracle REDO generated By Hour

Query.

SELECTSUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DAY,THREAD#

, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
, COUNT(*) TOTALFROMv$log_history aWHERE
(TO_DATE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')>=sysdate - 7)AND(TO_DATE(substr(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')<=sysdate)GROUP
by SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5),THREAD# order by SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5),THREAD#;

Oracle Characterset.

1. The (ordinary) character set for a database can be determined with:

select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
2. The national character set for a database can be determined with:

select value from nls_database_parameters where parameter = 'NLS_NCHAR_CHARACTERSET';

Oracle DataGuard Status

1.Issue the following query to find out if the standby database is performing Redo Apply or real-time apply. If the MRP0 or MRP process exists, then the standby database is applying redo.
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
2.If Redo Apply is running, cancel it as shown in the following example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

To enable real-time apply, include the USING CURRENT LOGFILE clause:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect;


SELECT DEST_ID "ID", STATUS "DB_status",DESTINATION "Archive_dest", ERROR "Error" FROM V$ARCHIVE_DEST WHERE DEST_ID =2;


SELECT INST_ID, PROCESS, CLIENT_PROCESS, STATUS, THREAD#, SEQUENCE# FROM GV$MANAGED_STANDBY;
The query indicates that Redo Apply has not applied the last 4 seconds of redo received on the standby database.
SELECT * FROM V$DATAGUARD_STATS WHERE NAME='apply lag';

Last applied Archive log numbers ==> (on DR database)
================================
select thread#, max(sequence#), applied from v$archived_log where applied='YES' group by thread#, applied;
Latest Generated logs from Primary ==>
===================================
select thread#, max(sequence#) from v$archived_log where ARCHIVED='YES' GROUP BY thread# order by thread#;
Total Archives YET to be Applied on Standby database ==>
====================================================
SELECT THREAD#, count(*) FROM V$ARCHIVED_LOG where applied='NO' group by thread# ORDER BY THREAD#;

The rate the archives are getting generated at primary database ==>
=====================================================================
select thread#, count(sequence#) "Logs Generated" from v$archived_log where
to_char (COMPLETION_TIME,'dd-Mon-yyyy') = '27-Jun-2011' group by thread# order by 1;
The rate Archive logs are applied to Standby ==>
==================================================
select thread#, count(sequence#) "Logs Applied" from v$archived_log where
to_char (COMPLETION_TIME,'dd-Mon-yyyy') = '27-Jun-2011'
and applied='YES' group by thread# order by thread#
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
Archives YET to be Applied on Standby database ==>
====================================================
SELECT THREAD#, SEQUENCE#, FIRST_TIME, APPLIED
FROM V$ARCHIVED_LOG where applied='NO' ORDER BY THREAD#, FIRST_TIME;
SELECT THREAD#, count(*)
FROM V$ARCHIVED_LOG where applied='NO' group by THREAD# ORDER BY THREAD#;


SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"    FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  87565                 87554         11
         2                  96172                 96162         10
         3                 155321                155292         29
         4                  72849                 72826         23

Determining Which Logs Were Not Received by the Standby Site
 issue the following query on the primary database:
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE#  FROM
(SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
WHERE
LOCAL.SEQUENCE# NOT IN
(SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND
THREAD# = LOCAL.THREAD#);

 

Linux OS commands

Find Out The Top 10 Memory Consuming Process

# ps -auxf | sort -nr -k 4 | head -10

Find Out top 10 CPU Consuming Process

# ps -auxf | sort -nr -k 3 | head -10

#6: free - Memory Usage

The command free displays the total amount of free and used physical and swap memory in the system, as well as the buffers used by the kernel.
# free

pmap - Process Memory Usage
The command pmap report memory map of a process. Use this command to find out causes of memory bottlenecks.
# pmap -d PID


 iptraf - Real-time Network Statistics


 tcpdump - Detailed Network Traffic Analysis
The tcpdump is simple command that dump traffic on a network. However, you need good understanding of TCP/IP protocol to utilize this tool. For.e.g to display traffic info about DNS, enter:
# tcpdump -i eth1 'udp port 53'
To display all IPv4 HTTP packets to and from port 80, i.e. print only packets that contain data, not, for example, SYN and FIN packets and ACK-only packets, enter:
# tcpdump 'tcp port 80 and (((ip[2:2] - ((ip[0]&0xf)<<2)) - ((tcp[12]&0xf0)>>2)) != 0)'
To display all FTP session to 202.54.1.5, enter:
# tcpdump -i eth1 'dst 202.54.1.5 and (port 21 or 20'
To display all HTTP session to 192.168.1.5:
# tcpdump -ni eth0 'dst 192.168.1.5 and tcp and port http'
Use wireshark to view detailed information about files, enter:
# tcpdump -n -i eth1 -s 0 -w output.txt src or dst port 80




# cat /proc/cpuinfo
# cat /proc/meminfo
# cat /proc/zoneinfo
# cat /proc/mounts




Nagios - Server And Network Monitoring


Cacti - Web-based Monitoring Tool


KDE System Guard - Real-time Systems Reporting and Graphing


A few more tools:

• nmap - scan your server for open ports.
• lsof - list open files, network connections and much more.
• ntop web based tool - ntop is the best tool to see network usage in a way similar to what top command does for processes i.e. it is network traffic monitoring software. You can see network status, protocol wise distribution of traffic for UDP, TCP, DNS, HTTP and other protocols.
• Conky - Another good monitoring tool for the X Window System. It is highly configurable and is able to monitor many system variables including the status of the CPU, memory, swap space, disk storage, temperatures, processes, network interfaces, battery power, system messages, e-mail inboxes etc.
• GKrellM - It can be used to monitor the status of CPUs, main memory, hard disks, network interfaces, local and remote mailboxes, and many other things.
• vnstat - vnStat is a console-based network traffic monitor. It keeps a log of hourly, daily and monthly network traffic for the selected interface(s).
• htop - htop is an enhanced version of top, the interactive process viewer, which can display the list of processes in a tree form.
• mtr - mtr combines the functionality of the traceroute and ping programs in a single network diagnostic tool.

SQL to view OS process and Oracle session info

$ ps -ef | grep 22910

oracle 22910     1 14 09:16:59 ?       32:09 oracleDBNAME
(DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))

In sqlplus or TOAD run this query:

SELECT s.saddr, s.sid, s.serial#, s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status,
p.program, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.spid IN (22910);

MSSQL locks

Use following to find blocking process

############

use master
GO
select * from sysprocesses (nolock) where blocked = 0 and spid in (
  select blocked from sysprocesses (nolock) where blocked <> 0
)
GO
#####################

To kill process which is blocking use following.

KILL 81;
GO

Use Following to find sessions or processes in the server.

sp_who2

GO