Rehearse 12c clusterware with "predict"

Oracle 12c clusterware will actually let you rehearse with SRVCTL & CRSCTL commands using "predict" & "eval" options. Exploring the predict option in this post.

[oracle@ol6-121-rac1 bin]$ ./srvctl predict -h

The SRVCTL predict command evaluates the consequences of resource failure.

Usage: srvctl predict database -db <database_name> [-verbose]
Usage: srvctl predict service -db <database_name> -service <service_name> [-verbose]
Usage: srvctl predict asm [-node <node_name>] [-verbose]
Usage: srvctl predict diskgroup -diskgroup <diskgroup_name> [-verbose]
Usage: srvctl predict filesystem -device <volume_device> [-verbose]
Usage: srvctl predict vip -vip <vip_name> [-verbose]
Usage: srvctl predict network [-netnum <network_number>] [-verbose]
Usage: srvctl predict listener -listener <listener_name> [-verbose]
Usage: srvctl predict scan -scannumber <scan_ordinal_number> [-netnum <network_number>] [-verbose]
Usage: srvctl predict scan_listener -scannumber <scan_ordinal_number> [-netnum <network_number>] [-verbose]
Usage: srvctl predict oc4j [-verbose]

Lets take a snap of the current resources and its status before executing the predict commands so that we ll understand why it predicts so..

[oracle@ol6-121-rac1 bin]$ ./crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details    
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       ol6-121-rac1             STABLE
               ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       ol6-121-rac1             STABLE
               ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.asm
               ONLINE  ONLINE       ol6-121-rac1             Started,STABLE
               ONLINE  ONLINE       ol6-121-rac2             Started,STABLE
ora.net1.network
               ONLINE  ONLINE       ol6-121-rac1             STABLE
               ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.ons
               ONLINE  ONLINE       ol6-121-rac1             STABLE
               ONLINE  ONLINE       ol6-121-rac2             STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       ol6-121-rac1             169.254.149.190 192.
                                                             168.1.101,STABLE
ora.cdbrac.db
      1        ONLINE  ONLINE       ol6-121-rac1             Open,STABLE
      2        ONLINE  ONLINE       ol6-121-rac2             Open,STABLE
ora.cvu
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       ol6-121-rac1             Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.ol6-121-rac1.vip
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.ol6-121-rac2.vip
      1        ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
--------------------------------------------------------------------------------


Now lets see the usage in detail.The output will summarize the behavior in case of the resource failure for which the "predict" commands are run.

• For a local resource it will list out what are the associated resources that will be stopped along with it.
• For a cluster resource it will list out how the fail-over will happen to a remote node(very helpful for 3+ node cluster). This will also be a very easy way to understand the dependency among st the resources.

[oracle@ol6-121-rac1 bin]$ srvctl predict database -db cdbrac -verbose
Database cdbrac will be stopped on nodes ol6-121-rac1,ol6-121-rac2

[oracle@ol6-121-rac1 bin]$ srvctl predict asm -node ol6-121-rac1 -verbose
Resource ora.asm will be stopped
Resource ora.mgmtdb will be stopped
Resource ora.DATA.dg will be stopped
Resource ora.MGMTLSNR will be started on node ol6-121-rac2
Database cdbrac will be stopped on node ol6-121-rac1
Resource ora.mgmtdb will be stopped
Resource ora.mgmtdb will be started on node ol6-121-rac2

[oracle@ol6-121-rac1 bin]$ srvctl predict diskgroup -diskgroup DATA -verbose
Resource ora.DATA.dg will be stopped

[oracle@ol6-121-rac1 bin]$ srvctl predict vip -vip ol6-121-rac1
VIP ol6-121-rac1 will be started on node ol6-121-rac2
Listener LISTENER will be stopped

[oracle@ol6-121-rac1 bin]$ srvctl predict network -netnum 1
Listener LISTENER_SCAN3 will be started on node ol6-121-rac2
Listener LISTENER will be stopped
Listener LISTENER_SCAN1 will be stopped
Listener LISTENER_SCAN2 will be stopped
Listener LISTENER_SCAN3 will be stopped
Resource ora.cvu will be started on nodes ol6-121-rac2,ol6-121-rac1
Network number 1 will be started on node ol6-121-rac1
Network number 1 will be stopped
Resource ora.cvu will be stopped
VIP ol6-121-rac1 will be started on nodes ol6-121-rac2,ol6-121-rac1
VIP ol6-121-rac2 will be started on node ol6-121-rac1
Resource ora.ons will be stopped
VIP scan1 will be started on node ol6-121-rac1
VIP scan2 will be started on nodes ol6-121-rac2,ol6-121-rac1
VIP scan3 will be started on node ol6-121-rac2
Listener LISTENER_SCAN1 will be started on node ol6-121-rac1
Listener LISTENER_SCAN2 will be started on nodes ol6-121-rac2,ol6-121-rac1
VIP scan1 will be stopped
VIP scan2 will be stopped
VIP scan3 will be stopped
Listener LISTENER will be started on node ol6-121-rac1
Resource ora.ons will be started on node ol6-121-rac1

[oracle@ol6-121-rac1 bin]$ srvctl predict listener -listener LISTENER
Listener LISTENER will be stopped

[oracle@ol6-121-rac1 bin]$ srvctl predict listener -listener MGMTLSNR
Resource ora.MGMTLSNR will be started on node ol6-121-rac2
Resource ora.mgmtdb will be started on node ol6-121-rac2

[oracle@ol6-121-rac1 bin]$ srvctl predict scan -scannumber 1
VIP scan1 will be started on node ol6-121-rac1
Listener LISTENER_SCAN1 will be started on node ol6-121-rac1
[oracle@ol6-121-rac1 bin]$ srvctl predict scan -scannumber 2
VIP scan2 will be started on node ol6-121-rac2
Listener LISTENER_SCAN2 will be started on node ol6-121-rac2

[oracle@ol6-121-rac1 bin]$ srvctl predict scan_listener -scannumber 1
Listener LISTENER_SCAN1 will be started on node ol6-121-rac1
VIP scan1 will be started on node ol6-121-rac1
[oracle@ol6-121-rac1 bin]$ srvctl predict scan_listener -scannumber 2
Listener LISTENER_SCAN2 will be started on node ol6-121-rac2
VIP scan2 will be started on node ol6-121-rac2

[oracle@ol6-121-rac1 bin]$ srvctl predict oc4j
Resource ora.oc4j will be started on node ol6-121-rac1

From the above o/p except few exceptions the rest makes sense. For example, in case of the diskgroup failure the o/p says the diskgroup resource will be stopped. But it did not mention anything about the failure of the instances running the in the same node which is not correct.

Planning the same with "eval" in a different post.
Happy Reading!!

12c Clusterware - An outlook

Thanks to Tim Hall. With the help of his notes i was able to successfully install my 1st 12C Grid Infrastructure. Except for few tweaking to the network interface cards. I just followed his notes as it is. In virtual box my guest is oel 6.5 and host is Windows 8.I tried executing few 11.2 commands and 12.1 just takes it like a vanilla.

[oracle@ol6-121-rac1 bin]$ ./crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online


[root@ol6-121-rac1 bin]# crsctl stat res -t -init
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details    
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       ol6-121-rac1             Started,STABLE
ora.cluster_interconnect.haip
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.crf
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.crsd
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.cssd
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.cssdmonitor
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.ctssd
      1        ONLINE  ONLINE       ol6-121-rac1             ACTIVE:0,STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.drivers.acfs
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.evmd
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.gipcd
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.gpnpd
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.mdnsd
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.storage
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
--------------------------------------------------------------------------------

The only new addition to the init daemon is the ora.storage.  The following the o/p of resource status.

[oracle@ol6-121-rac1 bin]$ ./crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details      
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       ol6-121-rac1             STABLE
               ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       ol6-121-rac1             STABLE
               ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.asm
               ONLINE  ONLINE       ol6-121-rac1             Started,STABLE
               ONLINE  ONLINE       ol6-121-rac2             Started,STABLE
ora.net1.network
               ONLINE  ONLINE       ol6-121-rac1             STABLE
               ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.ons
               ONLINE  ONLINE       ol6-121-rac1             STABLE
               ONLINE  ONLINE       ol6-121-rac2             STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       ol6-121-rac1             169.254.149.190 192.
                                                             168.1.101,STABLE
ora.cdbrac.db
      1        ONLINE  ONLINE       ol6-121-rac1             Open,STABLE
      2        ONLINE  ONLINE       ol6-121-rac2             Open,STABLE
ora.cvu
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       ol6-121-rac1             Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.ol6-121-rac1.vip
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.ol6-121-rac2.vip
      1        ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
--------------------------------------------------------------------------------

We could see couple of new resources here as well.
1. ora.MGMTLSNR
2. ora.mgmtdb

One point i noticed the term STABLE in state_details does not have anything to represent the health of a resource. As per documentation this is the INTERNAL_STATE of a resource.

INTERNAL_STATE
An internally managed, read-only attribute that describes what, if any, action the policy engine is currently executing on the resource. Possible values and their meanings are as follows:

STARTING: The policy engine is currently starting the resource

STOPPING: The policy engine is currently stopping the resource

CLEANING: The policy engine is currently cleaning the resource

STABLE: The policy engine is not currently executing any action on the resource

Note, however, that the resource can still be locked as part of some other command.

After finishing this post i wanted to list all the daemons part of 12c CRS. While i was looking for further information on evmlogger, my bad!! i found out that a similar article is already published in Oracle support. 1517182.1
Happy reading!!!

Flushing a single statement from Shared Pool

While testing performance issue of a single SQL statement, we would often want to enforce a hard parse of the to SQL statement to check, how the plan is generated after any changes. Though the old method of flushing the complete shared_pool is still possible,

ALTER SYSTEM FLUSH SHARED_POOL;

From 11g, we have the option of just flushing a single SQL statement from the shared pool which restricts any impact only with that particular statement. This is possible with the package DBMS_SHARED_POOL. We need to create this package by calling the script dbmspool.sql.

SQL> @?\rdbms\admin\dbmspool.sql

Package created.

Grant succeeded.

Once the package is created, we may use the PURGE procedure of the same package to flush the SQL. To demonstrate, am running a statement,

SQL> select * from scott.emp;

Now i could see the sql loaded to the shared pool.

SQL> select sql_id from v$sql where sql_text like 'select * from scott.emp';

SQL_ID
-------------
ggqns3c1jz86c

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_text='select * from scott.emp';

ADDRESS  HASH_VALUE
-------- ----------
272BDF88   52404428

Now based on the ADDRESS & HASH_VALUE am purging this SQL from shared_pool using the below statement. Here 'C' stands for cursor.

SQL> exec dbms_shared_pool.purge ('272BDF88,52404428','C');

PL/SQL procedure successfully completed.

After the purge the SQL is flushed out of the pool and will be hard parsed when executed again.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_text='select * from scott.emp';

no rows selected

SQL> select sql_id from v$sql where sql_text like 'select * from scott.emp';

no rows selected

Additional references: Doc Id : 457309.1