DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Friday, November 30, 2018

Start / Stop / Relocate SCAN listener in Oracle 11gR2 RAC

Start/ Stop / Relocate SCAN listener in Oracle 11gR2 RAC

1) Check listener status ( login to grid home)

a) Check the cluster resource status

$ crsctl stat res -t

verify the output for listener
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS     
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       node1                                       
               ONLINE  ONLINE       node2                                       
ora.FRA.dg
               ONLINE  ONLINE       node1                                       
               ONLINE  ONLINE       node2                                       
ora.LISTENER.lsnr
               ONLINE  ONLINE       node1                                       
               ONLINE  ONLINE       node2                                       
ora.asm
               ONLINE  ONLINE       node1                    Started           
               ONLINE  ONLINE       node2                    Started           
ora.gsd
               OFFLINE OFFLINE      node1                                       
               OFFLINE OFFLINE      node2                                       
ora.net1.network
               ONLINE  ONLINE       node1                                       
               ONLINE  ONLINE       node2                                       
ora.ons
               ONLINE  ONLINE       node1                                       
               ONLINE  ONLINE       node2                                       
ora.registry.acfs
               ONLINE  ONLINE       node1                                       
               ONLINE  ONLINE       node2                                       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       node2                                       
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       node1                                       
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       node1                                       
ora.cvu
      1        ONLINE  ONLINE       node1                                       
ora.node1.vip
      1        ONLINE  ONLINE       node1                                       
ora.node2.vip
      1        ONLINE  ONLINE       node2                                       
ora.PROD.db
      1        ONLINE  ONLINE       node1                    Open               
      2        ONLINE  ONLINE       node2                    Open               
ora.oc4j
      1        ONLINE  ONLINE       node1                                       
ora.scan1.vip
      1        ONLINE  ONLINE       node2                                       
ora.scan2.vip
      1        ONLINE  ONLINE       node1                                       
ora.scan3.vip
      1        ONLINE  ONLINE       node1

b) Check the scan listener status

$ srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node node2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node node1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node node1

c) Check the listener home. That sholud run in grid home:

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 23-NOV-2018 14:02:27
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date                29-NOV-2017 21:46:52
Uptime                    115 days 16 hr. 15 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /u01/app/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/node1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.30.40)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.30.41)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "PROD" has 1 instance(s).
  Instance "PROD1", status READY, has 1 handler(s) for this service...
Service "PRODXDB" has 1 instance(s).
  Instance "PROD1", status READY, has 1 handler(s) for this service...
The command completed successfully

2) Start scan listener:

a) Start scan listener

$ srvctl start scan_listener

b) If lsnrctl status showing rdbms home, then do the following

$ lsnrctl stop
$ export ORACLE_HOME=/u01/app/grid
$ lsnrctl start


3) Relocate SCAN listener

a) To relocate
When you find all 3 scan listeners are running on single node, then you may relocate any one of the listener

$ srvctl relocate scan_LISTENER -i 1 -n node2

b) Check current status after Relocate SCAN_LISTENER:

bash-3.2$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node node2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node node1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node node1

c) inherit status

$ ps -ef|grep inherit
  oracle 49741838  9633998   0 14:10:00  pts/0  0:00 grep inherit
  oracle 18547030        1   0 13:26:56      -  0:00 /u01/app/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
  oracle 31588762        1   0 13:20:20      -  0:14 /u01/app/grid/bin/tnslsnr LISTENER -inherit


So What is difference is between relocating the SCAN using srvctl relocate scan and SCAN_LISTENER  by using srvctl relocate scan_listener command?

Regarding questions; The difference between a SCAN VIP and a normal RAC VIP, is that the RAC VIP has a node it want’s to run on and each node has one (whereas you only have 3 SCANs). If it fails over to another node, the normal VIP exists, but does not accept connections, whereas the SCAN is not fix to a node and can run on any node in the cluster (and will accept connections anytime).

Now that this works, the SCAN VIP will always move with the SCAN listener (otherwise it would not make any sense). Hence there is really no difference in moving the SCAN VIP (because this will trigger a relocate of the listener) or to move the SCAN_Listener (since this will move the VIP it depends on).

3) Checking SCAN IPs

$ srvctl config scan
SCAN name: scandb.production.com, Network: 1/10.20.30.0/255.255.255.192/en8
SCAN VIP name: scan1, IP: /scandb.production.com/10.20.30.42
SCAN VIP name: scan2, IP: /scandb.production.com/10.20.30.43
SCAN VIP name: scan3, IP: /scandb.production.com/10.20.30.44

Tablespace Thresholds and Alerts

How to set tablespace thresholds?

If you have very less no. of targets and you are using dbcontrol for each database, then use DBMS_SERVER_ALERT package to set. If you have OEM Cloud control 12c / 13c, then you can create a new Metric extension and and Rule and apply it. Here we ll discuss how to use DBMS_SERVER_ALERT package.

Use of the DBMS_SERVER_ALERT package as an early warning mechanism for space issues. The DBMS_SERVER_ALERT package as an early warning mechanism for space issues. These can be set database-wide, or for individual tablespaces. When the threshold is crossed warnings are sent by the Enterprise Manager (DB Control, Grid Control or Cloud Control).

Setting the OBJECT_NAME parameter to NULL sets the default threshold for all tablespace in the database. Setting the OBJECT_NAME parameter to a tablespace name sets the threshold for the specified tablespace and overrides any default setting.

There are two types of tablespace thresholds that can be set.

TABLESPACE_PCT_FULL : Percent full. 

When the warning or critical threshold based on percent full is crossed a notification occurs.

TABLESPACE_BYT_FREE : Free Space Remaining (KB). 

The constant name implies the value is in bytes, but it is specified in KB. When the warning or critical threshold based on remaining free space is crossed a notification occurs. When you view these thresholds in different tools the units may vary, for example

Cloud Control displays and sets these values in MB.
The thresholds are set using a value and an operator.

OPERATOR_LE : Less than or equal.
OPERATOR_GE : Greater than or equal.


Setting Thresholds:

Note:  You should know of your existing thresholds before changing them, so you know what to set them back to.

The following examples show how to set the different types of alerts.

Example-1:  Database-wide KB free threshold.

Begin
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_byt_free,
    warning_operator        => DBMS_SERVER_ALERT.operator_le,
    warning_value           => '1024000',
    critical_operator       => DBMS_SERVER_ALERT.operator_le,
    critical_value          => '102400',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => NULL);
end;
/

Example-2:    Database-wide percent full threshold.

Begin
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
    warning_operator        => DBMS_SERVER_ALERT.operator_ge,
    warning_value           => '80',
    critical_operator       => DBMS_SERVER_ALERT.operator_ge,
    critical_value          => '90',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => NULL);
end;
/

Example-3:  Tablespace-specific KB free threshold.

begin
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_byt_free,
    warning_operator        => DBMS_SERVER_ALERT.operator_le,
    warning_value           => '1024000',
    critical_operator       => DBMS_SERVER_ALERT.operator_le,
    critical_value          => '102400',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => 'USERS');
end;
/

Example-4:    Tablespace-specific percent full threshold.

begin
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
    warning_operator        => DBMS_SERVER_ALERT.operator_ge,
    warning_value           => '80',
    critical_operator       => DBMS_SERVER_ALERT.operator_ge,
    critical_value          => '90',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => 'USERS');
end;
/

Example-5: Tablespace-specific reset to defaults ( Set warning and critical values to NULL)

  --DBMS_SERVER_ALERT.set_threshold(
  --  metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
  --  warning_operator        => DBMS_SERVER_ALERT.operator_ge,
  --  warning_value           => NULL,
  --  critical_operator       => DBMS_SERVER_ALERT.operator_ge,
  --  critical_value          => NULL,
  --  observation_period      => 1,
  --  consecutive_occurrences => 1,
  --  instance_name           => NULL,
  --  object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
  --  object_name             => 'USERS');


>> Setting the warning and critical levels to '0' disables the notification.

Displaying Thresholds
The threshold settings can be displayed using the DBA_THRESHOLDS view.

SET LINESIZE 200

COLUMN tablespace_name FORMAT A30
COLUMN metrics_name FORMAT A30
COLUMN warning_value FORMAT A30
COLUMN critical_value FORMAT A15

SELECT object_name AS tablespace_name,
       metrics_name,
       warning_operator,
       warning_value,
       critical_operator,
       critical_value
FROM   dba_thresholds
WHERE  object_type = 'TABLESPACE'
ORDER BY object_name;

Monday, November 26, 2018

Removing a NODE from RAC


  1. Delete the instance on the node to be removed
  2. Clean up ASM
  3. Remove the listener from the node to be removed
  4. Remove the node from the database
  5. Remove the node from the clusterware
You can delete the instance by using the database creation assistant (DBCA), invoke the 
program choose the RAC database, choose instance management and then choose 
delete instance, enter the sysdba user and password then choose the instance to delete.

To clean up ASM follow the below steps
  1. From node 1 run the below command to stop ASM on the node to be removed

      
    srvctl stop asm -n rac3
      
    srvctl remove asm -n rac3
  2. Now run the following on the node to be removed

      
    cd $ORACLE_HOME/admin
      rm -rf +ASM
      
    cd $ORACLE_HOME/dbs
      
    rm -f *ASM*
  3. Check that /etc/oratab file has no ASM entries, if so remove them
Now remove the listener for the node to be removed
·         Login as user oracle, and set your DISPLAY environment variable, then start the Network 
         Configuration Assistant
     $ORACLE_HOME/bin/netca
·         Choose cluster management
·         Choose listener
·         Choose Remove
·         Choose the  name as LISTENER
Next we remove the node from the database
  1. Run the below script from the node to be removed
    cd $ORACLE_HOME/bin
    ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES={rac3}" -local
    ./runInstaller
  2. Choose to deinstall products and select the dbhome
  3. Run the following from node 1
      cd $ORACLE_HOME/oui/bin
      
    ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES={rac1,rac2,rac3}"
Lastly we remove the clusterware software
  1. Run the following from node 1, you obtain the port number from remoteport section in the ons.config file in $ORA_CRS_HOME/opmn/conf 
  2.   $CRS_HOME/bin/racgons remove_config rac3:6200
  3. Run the following from the node to be removed as user root
      cd $CRS_HOME/install
      
    ./rootdelete.sh
  4. Now run the following from node 1 as user root, obtain the node number first 
      $CRS_HOME/bin/olsnodes -n
      
    cd $CRS_HOME/install
      
    ./rootdeletenode.sh rac3,3
  5. Now run the below from the node to be removed as user oracle

       cd $CRS_HOME/oui/bin
      
    ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES={rac3}" CRS=TRUE -local
      ./runInstaller
  6. Choose to deinstall software and remove the CRS_HOME
  7. Run the following from node as user oracle

      cd $CRS_HOME/oui/bin 
      
    ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES={rac1,rac2,rac3}" CRS=TRUE
  8. Check that the node has been removed, the first should report "invalid node", the second you should not see any output and the last command you should only see nodes rac1 and rac2 

      
    srvctl status nodeapps -n rac3
      
    crs_stat |grep -i rac3
      
    olsnodes -n