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

Resource Busy Ora-00054

Use following query to find blockers

select 'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
/

Use following query to get info of session holding lock.

set lines 100 pages 999
col username  format a20
col sess_id  format a10
col object format a25
col mode_held format a10
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
 , 0, 'Not Blocking'
 , 1, 'Blocking'
 , 2, 'Global') status
, decode(v.locked_mode
 , 0, 'None'
 , 1, 'Null'
 , 2, 'Row-S (SS)'
 , 3, 'Row-X (SX)'
 , 4, 'Share'
 , 5, 'S/Row-X (SSX)'
 , 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where  v.object_id = d.object_id
and  v.object_id = l.id1
and  v.session_id = s.sid
order by oracle_username
, session_id
/

Show which row is locked

select do.object_name
, row_wait_obj#
, row_wait_file#
, row_wait_block#
, row_wait_row#
, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, 
    ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
from v$session s
, dba_objects do
where sid=&sid
and  s.ROW_WAIT_OBJ# = do.OBJECT_ID
/

Then select the row with that rowid...

select * from <table> where rowid=<rowid>;

List locks

column lock_type format a12
column mode_held format a10
column mode_requested format a10
column blocking_others format a20
column username format a10
SELECT session_id
, lock_type
, mode_held
, mode_requested
, blocking_others
, lock_id1
FROM dba_lock l
WHERE  lock_type NOT IN ('Media Recovery', 'Redo Thread')
/

RMAN Duplicate database

Using RMAN to copy database from one machine to another.

Node1-----SR or source database
Node2-----DT or destination database.

Backups are on SBT tapes and using EMC Networker Module for Oracle.

Initial requirements.

Oracle installation version must be same on both nodes and both nodes are having same OS version.

I am taking an example of refresh from PROD to QA environment in which most OS and DB versions are same.

Networker client is installed on both nodes and configured for backup

Networker settings for source database on networker server must have set remote access list so that the database backups can be accessed by destination node.(Very important)

RMAN catalog database is already configured and backups are done using catalog.

on Destination

Shutdown the destination database.

sql> shutdown immediate


Go to directory where datafiles ,redo , control files are located and delete them(take backup to a different location before deleting).

$ cd /oracle/oradata/DT/
$cp /oracle/oradata/DT/ *    /backup/DT/
$ rm -f   *

Do not delete initDT.ora file if its deleted then create one with appropriate parameters.

Edit initDT.ora file and update following parameters for conversion of paths when data files are copied.

DB_FILE_NAME_CONVERT=('/oracle/oradata/SR','/oracle/oradata/DT')
LOG_FILE_NAME_CONVERT=('/oracle/oradata/aSR','/oracle/oradata/DT')

Backup source database to tapes (Not required if backup already exists on tapes and want to restore old backups)

on SOURCE database

Login as oracle or db owner UID.

$export NSR_SERVER=NWSERVER
$export NSR_CLIENT=NODE1
$export NSR_KEEPALIVE_WAIT=10
$export NSR_DATA_VOLUME_POOL=oracle     (If  tape pool is used)

rman>
rman>connect target sys/oracle@SR
rman>connect rcvcat rmanprod/rmanprod@rmanprod
run {
allocate  channel t1 type 'SBT_TAPE';
allocate  channel t2 type 'SBT_TAPE';
send 'NSR_ENV=(NSR_SERVER=NWSERVER,NSR_CLIENT=NODE1, NSR_DATA_VOLUME_POOL=oracle)';
backup database plus archivelog;
release channel t1;
release channel t2;
}

 Once database backup is complete on production.

Now you can start restore on Destination as follows.

connect to NODE2 as oracle or oracle installation owner UID

set environment for destination database DT

$ sqlplus / as sysdba

SQL> startup nomount pfile=/oracle/dbs/initDT.ora                     (path to init param file created above)
SQL> exit

Here
target= source database (SR)
catalog = same database as used during source backup
auxillary = destination database (DT)


set environment for DT database and following parameters

$export NSR_SERVER=NWSERVER
$export NSR_CLIENT=NODE1------------------> (Very important this must be NODE1 only since backups are taken there)
$export NSR_KEEPALIVE_WAIT=10
$export NSR_DATA_VOLUME_POOL=oracle

$rman
rman> connect target sys/oracle@SR
rman>connect rcvcat rmanprod/rmanprod@rmanprod
rman>connect auxiliary /
run {
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
configure channel device type 'SBT_TAPE' parms  'ENV=(NSR_DEBUG_FILE=/tmp/nsr_channel1.log,NSR_DEBUG_LEVEL=2)';
allocate auxiliary channel t1 type 'SBT_TAPE' debug=5 trace=2 parms 'ENV=(NSR_DEBUG_FILE=/tmp/nsr_channel1.log,NSR_DEBUG_LEVEL=2,NSR_DATA_VOLUME_POOL=oracle)';
debug on;
send 'NSR_ENV=(NSR_SERVER=NWSERVER,NSR_CLIENT=NODE1)';
set until time "to_date('2011-06-07:17:00:00','YYYY-MM-DD:hh24:mi:ss')";
duplicate target database to DT;
debug off;
release channel t1;
}

you can remove debug and also set untill if you dont need them.

Once duplicate is complete you can comment out the file conversion parameters added in init param file of DT.

Additional commands


Restart networker client service as ROOT user

#/sbin/service networker stop

#/sbin/service networker status

#ps -ef|grep nsr

#/sbin/service networker start




 Errors encountered.

Could not locate the LNM save file 'him8opgk_1_1' on server 'kap-us-297nw2'. (2:9:0)

Solution. Make sure you have the right tape volumes accessible for networker server.