RAC TroubleShoot : Cluster Process List

Ques : 
Define the cluster process list when cluster (crsd) is  running or not .

> When cluster is running :

[grid@primoda1 ~]$ ps -ef | grep d.bin
grid      2218     1  0 Jul06 ?        00:14:56 /u01/app/11.2.0.3/grid/bin/tnslsnr LISTENER -inherit
root     15481     1  0 Jun20 ?        03:28:25 /u01/app/11.2.0.3/grid/bin/ohasd.bin reboot
root     15998     1  0 Jun20 ?        01:16:03 /u01/app/11.2.0.3/grid/bin/ologgerd -m primoda2 -r -d /u01/app/11.2.0.3/grid/crf/db/primoda1
root     16018     1  0 Jun20 ?        02:10:04 /u01/app/11.2.0.3/grid/bin/octssd.bin reboot
grid     16130     1  0 Jun20 ?        02:02:20 /u01/app/11.2.0.3/grid/bin/evmd.bin
grid     16275     1  0 Jun20 ?        02:51:56 /u01/app/11.2.0.3/grid/bin/oraagent.bin
grid     16302     1  0 Jun20 ?        00:01:58 /u01/app/11.2.0.3/grid/bin/mdnsd.bin
grid     16452     1  0 Jun20 ?        00:25:09 /u01/app/11.2.0.3/grid/bin/gpnpd.bin
root     16568     1  0 Jun20 ?        06:10:24 /u01/app/11.2.0.3/grid/bin/orarootagent.bin
grid     16571     1  0 Jun20 ?        02:39:16 /u01/app/11.2.0.3/grid/bin/gipcd.bin
root     16586     1  2 Jun20 ?        1-11:29:22 /u01/app/11.2.0.3/grid/bin/osysmond.bin
root     16602     1  0 Jun20 ?        00:43:20 /u01/app/11.2.0.3/grid/bin/cssdmonitor
root     16635     1  0 Jun20 ?        00:42:51 /u01/app/11.2.0.3/grid/bin/cssdagent
root     16880     1  0 Jun20 ?        05:39:25 /u01/app/11.2.0.3/grid/bin/crsd.bin reboot
grid     17019 16130  0 Jun20 ?        00:00:00 /u01/app/11.2.0.3/grid/bin/evmlogger.bin -o /u01/app/11.2.0.3/grid/evm/log/evmlogger.info -l /u01/app/11.2.0.3/grid/evm/log/evmlogger.log
grid     17073     1  0 Jun20 ?        02:11:50 /u01/app/11.2.0.3/grid/bin/oraagent.bin
root     17082     1  0 Jun20 ?        06:05:06 /u01/app/11.2.0.3/grid/bin/orarootagent.bin
grid     17111     1  0 Jun20 ?        12:13:23 /u01/app/11.2.0.3/grid/bin/ocssd.bin
grid     17631     1  0 Jun20 ?        00:06:30 /u01/app/11.2.0.3/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
oracle   17672     1  0 Jun20 ?        06:22:05 /u01/app/11.2.0.3/grid/bin/oraagent.bin
grid     27397 23054  0 12:19 pts/1    00:00:00 grep d.bin
[grid@primoda1 ~]$ ps -ef | grep d.bin | wc -l
21
[grid@primoda1 ~]$

> When cluster is not running :

[oracle@ol5-112-rac1 ~]$ ps -ef | grep d.bin

oracle   30756 30727  0 12:21 pts/2    00:00:00 grep d.bin

[oracle@ol5-112-rac1 ~]$ 

2)  Output of the pmon process where cluster is up :

[oracle@ol5-112-rac2 ~]$ ps -ef | grep pmon

oracle    8085     1  0 Aug13 ?        00:00:00 asm_pmon_+ASM2

oracle   21636     1  0 11:38 ?        00:00:00 ora_pmon_RAC2

oracle   23646 22837  0 12:22 pts/1    00:00:00 grep pmon

See All Hidden parameter in oracle

All hidden parameter as 'SYS' user / SQLNET:

SELECT a.ksppinm Param , b.ksppstvl SessionVal , c.ksppstvl InstanceVal, a.ksppdesc Descr 

FROM x$ksppi a , x$ksppcv b , x$ksppsv c WHERE 

a.indx = b.indx AND a.indx = c.indx AND a.ksppin

To see all SQLNET avaiable parameters :

  sqlnet paramters

Consistent Expdp !

During Export (exdpd) a database in oracle10g and oracle11g , if any DML is executed then it will added with the export jobs .

For removing this problem and also taking a CONSISTENT backup we can use below script :

"  expdp system/sys123 directory=test_dir dumpfile=scott.dmp schemas=scott flashback_time=SYSTIMESTAMP"

---

Flashback in a Data Guard Environment

01) Flashback on PRIMARY database :

$ sqlplus "/ as sysdba"

SQL> alter system set cluster_database=false scope=spfile sid='pbldb1'; //recommended

$ srvctl stop database -d pbldb

$ sqlplus "/ as sysdba"

SQL> startup mount

$ rman target /

RMAN> FLASHBACK DATABASE TO RESTORE POINT BEFORE_EOD;

RMAN> ALTER DATABASE OPEN RESETLOGS;

SQL> alter system set cluster_database=true scope=spfile sid='pbldb1';

SQL> shutdown immediate

$ srvctl start database -d pbldb

$ srvctl start service -d pbldb

02) Using Flashback in STANDBY database 

Link : http://uhesse.com/2010/08/06/using-flashback-in-a-data-guard-environment/

Problem : Using Flashback , we restored database successfully on primary site . But the dataguard environment is not sync cause of resetlog command . 

Here , we enable flashback on dataguard site , also .

Solution :

0) SQL> select database_role,flashback_on from v$database;

DATABASE_ROLE    FLASHBACK_ON

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

PHYSICAL STANDBY YES

1) From Primary :

  select resetlogs_change# from v$database;

 9421383974

In Standby Database :

1) Shutdown and start as mount :

[oracle@droda1 ~]$ echo $ORACLE_SID

pbldg

[oracle@droda1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 2 01:21:58 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

*

ERROR at line 1:

ORA-16136: Managed Standby Recovery not active

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 3.4206E+10 bytes

Fixed Size                  2245480 bytes

Variable Size            1.7583E+10 bytes

Database Buffers         1.6576E+10 bytes

Redo Buffers               45674496 bytes

Database mounted.

SQL> 

2) Run the Flashback Command :

  flashback database to scn  9421383971;   //  (Make -3 from primary SCN .)

3) Finally Run the Apply Process :

  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

===

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            +RECO

Oldest online log sequence     5

Next log sequence to archive   0

Current log sequence           7

Consistent Backup During Expdp

During Export (exdpd) a database in oracle10g and oracle11g , if any DML is executed then it will added with the export jobs .

For removing this problem and also taking a CONSISTENT backup we can use below script :

"  expdp system/sys123 directory=test_dir dumpfile=scott.dmp schemas=scott flashback_time=SYSTIMESTAMP"

Difference between SCAN and VIP in RAC

http://satya-racdba.blogspot.com/2012/09/scan-in-oracle-rac-11g-r2.html

If you have ever been tasked with extending an Oracle RAC cluster by adding a new node (or shrinking a RAC cluster by removing a node), then you know the pain of going through a list of all clients and updating their SQL*Net or JDBC configuration to reflect the new or deleted node. To address this problem, Oracle 11g Release 2 introduced a new feature known as Single Client Access Name or SCAN for short. SCAN is a new feature that provides a single host name for clients to access an Oracle Database running in a cluster. Clients using SCAN do not need to change their TNS configuration if you add or remove nodes in the cluster. The SCAN resource and its associated IP address(s) provide a stable name for clients to use for connections, independent of the nodes that make up the cluster. You will be asked to provide the host name (also called the SCAN name in this document) and up to three IP addresses to be used for the SCAN resource during the interview phase of the Oracle Grid Infrastructure installation. For high availability and scalability, Oracle recommends that you configure the SCAN name for round-robin resolution to three IP addresses. At a minimum, the SCAN must resolve to at least one address.

The SCAN virtual IP name is similar to the names used for a node's virtual IP address, such as racnode1-vip. However, unlike a virtual IP, the SCAN is associated with the entire cluster, rather than an individual node, and can be associated with multiple IP addresses, not just one address.

During installation of the Oracle Grid Infrastructure, a listener is created for each of the SCAN addresses. Clients that access the Oracle RAC database should use the SCAN or SCAN address, not the VIP name or address. If an application uses a SCAN to connect to the cluster database, the network configuration files on the client computer do not need to be modified when nodes are added to or removed from the cluster. Note that SCAN addresses, virtual IP addresses, and public IP addresses must all be on the same subnet.

The SCAN should be configured so that it is resolvable either by using Grid Naming Service (GNS) within the cluster or by using the traditional method of assigning static IP addresses using Domain Name Service (DNS) resolution.

In this article, I will configure SCAN for round-robin resolution to three, manually configured static IP address using the DNS method.


racnode-cluster-scan IN A 192.168.1.187 
racnode-cluster-scan IN A 192.168.1.188 
racnode-cluster-scan IN A 192.168.1.189

Further details regarding the configuration of SCAN will be provided in the section "Verify SCAN Configuration" during the network configuration phase of this guide..

FAQ :

Que#1: Will three SCAN listeners have same same port to listen? 
ANS: Yes all three Scan Listeners will have same port and you can choose the port and the port will be different than the port used for VIP listeners. :-) Can't Share the port# which i used in my production. 

Que#2: Three SCAN listeners take the request parallel? 
ANS: Yes three Scan listeners will run in parallel and accept requests, DNS will route request to one of the SCAN Listener. 

Que#3: So from 11.2 onwards we are not using VIP? 
ANS: VIPs still exists on each node, But we dont use VIPs in our client connection string instead we we use one SCAN-Name. 

Que#4: It does seem VIPs are redudent in that case? 
ANS: There is difference between VIP and SCAN Listener, Let me try to explain and hope the example at the end will help to understand in more detail, First difference is VIP is always bound to a node and each node will specifically have one VIP Listener Where as Grid Services will start the SCAN Listener on any node e.g if you have three SCAN Listener and 2/3 RAC Nodes then it may be the case that 2 SCAN Listeners in one node and 1 SCAN Listerner on second node where as third node doesn't have any SCAN Listerner. For VIPs if a certain node is offline then that VIP will not be available in Cluster where as if certain node is offline then Cluster will relocate the SCAN to other surviving nodes. 
Advantage of SCAN: Previously we specified all the VIPs in our client connections but now you need to specify only one "SCAN Name" in your client address. If you Add/Remove Nodes in your RAC then you dont need to update your client connection strings, The only change will be done in DNS.

====

******************************Example Eleboration*********************** 
#######################Configuration Before SCAN######################### 
Number of RAC Nodes: 8 
Physical IPs: 8, One for each node 
Private IPs: atleast 8, One for each node (You can have two for each for redudency) 
Virtual IPs: 8, One assigned to each node ... Word "assigned" is important 
Number of VIP Listeners: 8, Each Bound to certain Node ... word "bound"/"assigned" 

Client Connection String: Mentioning address of all 8 VIP Listeners (Failover etc) 

Simplest Event: Add 4 new Nodes or Remove 4 Nodes 
Required Action: Update the Client connection string on all clients 
Connection Establishment: Client sends request to one of VIP Listeners mentioned in client connection string, if failed then client will send request on another VIP address until the connection is successful. So client sending requests directly to VIP listeners specific/bound to VIP on each node. 

#######################Configuration After SCAN########################## 
**********************************Understanding SCAN**************************************** 
SCAN: Single Client Access Name -- Allowing client to access whole cluster/grid through one name, rather specifying all VIP addresses individually 
SCAN Requirement: To be able to get SCAN Functionality, We need some service through which one name is resolved to multiple IPs/hosts. This is what we often call "DNS", Domain name service or if you dont have DNS in your environment then you can use Oracle's "GNS (Grid Naming Service)" 

SCAN Implementation/Configuration: In Our DNS We defined a ScanName and assigned three Virtual IPs e.g (OraProdScan=X.X.X.5, X.X.X.6 and X.X.X.7), Now when you do nslookup OraProdScan from your client it will give you list of three IPs specified as above and important thing, everytime you do nslookup it will give you the IP addresses in round robin, So every request to resolve the OraProdScan name will give different IP every time. One kind of load balancing at first place to reach to SCAN Listener so requests will be distributed on available SCAN Listeners. 

SCAN Usage: During Oracle Grid Installation, Specify mentioned IPs, Each VIP Listener on each node will register itself will Every SCAN Listener on whatever node the SCAN Listener is running. 


Number of RAC Nodes: 8 
Physical IPs: 8, One for each node 
Private IPs: atleast 8, One for each node (You can have two for each for redudency) 
Virtual IPs: 8, One assigned to each node ... Word "assigned" is important 
Number of VIP Listeners: 8, Each Bound to certain Node ... word "bound"/"assigned" 
Number of SCAN Listeners: 3, Grid will decide which nodes SCAN Listeners will be Allocated 
Client Connection String: Mentioning the name of SCAN "OraProdScan" 

Simplest Event: Add 4 new Nodes or Remove 4 Nodes 
Required Action: No update required. 
Connection Establishment: Client sends request to DNS using SCAN Name "OraProdScan", DNS will return one of three IPs, Client Sends request on that IP to the SCAN Listener, the SCAN Listener will route the request to one of the VIP Listeners among 8 VIP listeners in this case because each VIP listener has already registered itself with every SCAN Listener. Finally direct connection is established between client and one of the VIP Listeners. 
You see flexibility, adding/removing as many nodes in Grid/RAC doesn't require any change. 
Hope i have answered all questions, it was long post, if i still have missed anything please let me know!

(http://www.linkedin.com/groups/Difference-between-SCAN-VIP-in-77941.S.199795316?view=&gid=77941&type=member&item=199795316&trk=Skyline_click_NGDR&sl=NGDR%3B46807361%3A1398053120129%3B0%3B%3B)

Network Access Security 


1) open sqlnet.ora file and edit :

TCP.VALIDNODE_CHECKING = YES
TCP.EXCLUDED_NODES = (192.168.0.244,192.168.0.34)
#TCP.EXCLUDED_NODES = (172.20.211.25,172.20.210.0/24,192.168.1.0/24) --Full Network
#TCP.INVITED_NODES = (192.168.1.14)
#trace_level_server = support
#trace_directory_server = /tmp

2) lsnrctl restart

3 Access from Client End (From 192.168.0.244 ip):

 
[oracle@dataguard ~]$ sqlplus system/sys123@auditdb

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 18 12:11:10 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-12537: TNS:connection closed


Enter user-name:

source : http://www.solutionbeacon.com/best19.htm

Note :

[oracle@rptsvr ~]$ cat /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
TCP.VALIDNODE_CHECKING = YES
#TCP.EXCLUDED_NODES = (192.168.1.3)
TCP.INVITED_NODES = (192.168.1.12,192.168.1.218)

 TCP.INVITED_NODES : All ip are blocked except those two (12 and 218)

TCP.EXCLUDED_NODES : All  ip are accessed except this one (1.3)  

You cannot specify a range, wildcard, partial IP or subnet mask (ouch!)
TCP.INVITED_NODES takes precedence over the TCP.EXCLUDED_NODES if both lists are present
(although if a range cannot be specified, you will likely only utilize the invited nodes
 option).

Troubleshoot :

If the below error is occured ,

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.12)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.12)(PORT=1521)))
TNS-12547: TNS:lost contact
 TNS-12560: TNS:protocol adapter error
  TNS-00517: Lost contact
   Linux Error: 104: Connection reset by peer

Solution :
We need to add localhost entry with the INVITED HOST .