Saturday, March 26, 2016

Failed to record pid for GPNPD / CRS-2302:Cannot get GPnP profile.

Failed to record pid for GPNPD
CRS-2302:Cannot get GPnP profile.
Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).

After restart Oracle Grid Infrastructure version 11.2.0.1/later or after rebooting a cluster node, the following error is encountered:

Error

[client(20896)]CRS-2302:Cannot get GPnP profile. Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).
2016-03-27 00:03:26.114
[client(20896)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /grid/app/11.2.0.3/grid/log/rac122/client/crsctl_oracle.log.



From gpnp.log

CLSGPNP_NO_PROFILE. GPnP profile 'profile.xml' does not exist in '/grid/app/11.2.0.3/grid/gpnp/rac122/profiles/peer/'

Grid Home : alertrac122.log

[gpnpd(20759)]CRS-2329:GPNPD on node rac122 shutdown.
2016-03-27 00:03:26.112
[client(20896)]CRS-2302:Cannot get GPnP profile. Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).
2016-03-27 00:03:26.114
[client(20896)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /grid/app/11.2.0.3/grid/log/rac122/client/crsctl_oracle.log.
2016-03-27 00:03:27.714
[client(20958)]CRS-2302:Cannot get GPnP profile. Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).
2016-03-27 00:03:27.715
[client(20958)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /grid/app/11.2.0.3/grid/log/rac122/client/crsctl_oracle.log.
2016-03-27 00:03:28.203
[client(20970)]CRS-2302:Cannot get GPnP profile. Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).
2016-03-27 00:03:28.204
[client(20970)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /grid/app/11.2.0.3/grid/log/rac122/client/crsctl_oracle.log.
2016-03-27 00:04:10.023
[/grid/app/11.2.0.3/grid/bin/oraagent.bin(19737)]CRS-5818:Aborted command 'start' for resource 'ora.gpnpd'. Details at (:CRSAGF00113:) {0:0:2} in /grid/app/11.2.0.3/grid/log/rac122/agent/ohasd/oraagent_oracle/oraagent_oracle.log.
2016-03-27 00:04:14.025
[ohasd(19619)]CRS-2757:Command 'Start' timed out waiting for response from the resource 'ora.gpnpd'. Details at (:CRSPE00111:) {0:0:2} in /grid/app/11.2.0.3/grid/log/rac122/ohasd/ohasd.log.
2016-03-27 00:04:14.416

gpnpd.log

[oracle@rac122 gpnpd]$ more /grid/app/11.2.0.3/grid/log/rac122/gpnpd/gpnpd.log

================================================================================
2016-03-27 00:06:19.082: [ default][3804149536]gpnpd START pid=21149 Oracle Grid Plug-and-Play Daemon
2016-03-27 00:06:19.082: [    GPNP][3804149536]clsgpnp_Init: [at clsgpnp0.c:585] '/grid/app/11.2.0.3/grid' in effect as GPnP home base.
2016-03-27 00:06:19.082: [    GPNP][3804149536]clsgpnp_Init: [at clsgpnp0.c:619] GPnP pid=21149, GPNP comp tracelevel=1, depcomp tracelevel=0, tlsrc:ORA_DAEMON_LOGGING_LEVELS, apitl:0, complog:1, tstenv:0, devenv:0, envopt:0, flags=3
2016-03-27 00:06:19.086: [    GPNP][3804149536]clsgpnpkwf_initwfloc: [at clsgpnpkwf.c:399] Using FS Wallet Location : /grid/app/11.2.0.3/grid/gpnp/rac122/wallets/peer/

[   CLWAL][3804149536]clsw_Initialize: OLR initlevel [70000]
[  clsdmt][3792512768]Listening to (ADDRESS=(PROTOCOL=ipc)(KEY=rac122DBG_GPNPD))
2016-03-27 00:06:19.098: [  clsdmt][3792512768]PID for the Process [21149], connkey 10
2016-03-27 00:06:19.098: [  clsdmt][3792512768]Creating PID [21149] file for home /grid/app/11.2.0.3/grid host rac122 bin gpnp to /grid/app/11.2.0.3/grid/gpnp/init/
2016-03-27 00:06:19.098: [  clsdmt][3792512768]Writing PID [21149] to the file [/grid/app/11.2.0.3/grid/gpnp/init/rac122.pid]
2016-03-27 00:06:19.098: [  clsdmt][3792512768]Failed to record pid for GPNPD
2016-03-27 00:06:19.098: [  clsdmt][3792512768]Terminating process
2016-03-27 00:06:19.098: [    GPNP][3792512768]CLSDM requested exit
2016-03-27 00:06:19.098: [ default][3792512768]GPNPD on node rac122 shutdown.



CAUSE :

[oracle@rac122 gpnpd]$ cd /grid/app/11.2.0.3/grid/gpnp/init/
[oracle@rac122 init]$ ls -ltrah
total 12K
drwxr-x--- 6 oracle oinstall 4.0K Mar 26 16:01 ..
-rw-r--r-- 1 oracle oinstall    0 Mar 26 16:01 rac122
drwxr-x--- 2 oracle oinstall 4.0K Mar 26 16:01 .
-rw-r--r-- 1 root   root        6 Mar 26 22:52 rac122.pid

File permission has been changed to root:root. Where this should have oracle:oinstall.


SOLUTION

To fix the issue, we have to move the rac122.pid to some different name  or correct the permission of files or create a new blank file with same name.


[oracle@rac122 init]$ cd /grid/app/11.2.0.3/grid/gpnp/init
[oracle@rac122 init]$ ls -l

-rw-r--r-- 1 oracle oinstall 0 Mar 26 16:01 rac122
-rw-r--r-- 1 oracle oinstall 6 Mar 26 23:10 rac122.pid

[oracle@rac122 init]$ mv rac122.pid rac122.pid_bkp

[oracle@rac122 init]$ touch rac122.pid

[oracle@rac122 init]$ cd /grid/app/11.2.0.3/grid/gpnp/init
[oracle@rac122 init]$ ls -l

-rw-r--r-- 1 oracle oinstall 0 Mar 26 16:01 rac122
-rw-r--r-- 1 oracle oinstall 6 Mar 27 00:32 rac122.pid
-rw-r--r-- 1 root   root     6 Mar 27 00:32 rac122.pid_bkp

Now stopping the cluster services on node rac122.

[oracle@rac122 init]$ sudo $ORACLE_HOME/bin/crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac122'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac122'
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'rac122'
CRS-2677: Stop of 'ora.cssdmonitor' on 'rac122' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'rac122' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac122' has completed
CRS-4133: Oracle High Availability Services has been stopped.

Start the cluster services on node rac122

[oracle@rac122 init]$ sudo $ORACLE_HOME/bin/crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

Validate the cluster services on node rac122

[oracle@rac122 init]$ crsctl check cluster
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online


NOTE:

This can happen to other daemons like crsd,ctssd,...

$GRID_HOME/<daemon>/init/<node_name>
$GRID_HOME/<daemon>/init/<node_name>.pid



References :

Unable To Start CRS 11gR2 Due to Missing Files in $GRID_HOME/<daemon>/init Directory (Doc ID 1146055.1)



Hope this help :)

CLSGPNP_NO_PROFILE. GPnP profile 'profile.xml' does not exist

LOSS OF GPnP Profile "profile.xml"
CLSGPNP_NO_PROFILE. GPnP profile 'profile.xml' does not exist

From gpnp.log

CLSGPNP_NO_PROFILE. GPnP profile 'profile.xml' does not exist in '/grid/app/11.2.0.3/grid/gpnp/rac122/profiles/peer/'

Grid Home : alertrac122.log

CRS-2302:Cannot get GPnP profile. Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).

gpnp.log

2016-03-26 22:52:32.562: [  clsdmt][1569007360]Writing PID [11790] to the file [/grid/app/11.2.0.3/grid/gpnp/init/rac122.pid]
2016-03-26 22:52:32.566: [    GPNP][1580644128]clsgpnpcf_readf: [at clsgpnpcf.c:523] Result: (26) CLSGPNP_NO_PROFILE. GPnP profile 'profile.xml' does not exist in '/grid/app/11.2.0.3/grid/gpnp/rac122/profiles/peer/'
2016-03-26 22:52:32.566: [    GPNP][1580644128]clsgpnpcf_readf: [at clsgpnpcf.c:525] SlfLengthn profile
Internal Error Information:
  Category: SLF_NOENT(-5)
  Operation: stat failed
  Location: SlfStat1
  Other:
  Dep: 2
  Dep Message: No such file or directory

2016-03-26 22:52:32.566: [    GPNP][1580644128]clsgpnpd_openLocalProfile: [at clsgpnpd.c:3440] Result: (26) CLSGPNP_NO_PROFILE. Got no local best profile from file cache provider (LCP-FS).

alertrac122.log

2016-03-26 22:13:29.492
[client(6080)]CRS-2302:Cannot get GPnP profile. Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).
2016-03-26 22:13:29.493
[client(6080)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /grid/app/11.2.0.3/grid/log/rac122/client/crsctl_oracle.log.
2016-03-26 22:13:34.001
[client(6154)]CRS-2302:Cannot get GPnP profile. Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).
2016-03-26 22:13:34.002
[client(6154)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /grid/app/11.2.0.3/grid/log/rac122/client/crsctl_oracle.log.
2016-03-26 22:13:37.383
[client(6174)]CRS-2302:Cannot get GPnP profile. Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).
2016-03-26 22:13:37.384
[client(6174)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /grid/app/11.2.0.3/grid/log/rac122/client/crsctl_oracle.log.
2016-03-26 22:13:47.942
[ohasd(6210)]CRS-2112:The OLR service started on node rac122.
2016-03-26 22:13:47.948
[ohasd(6210)]CRS-1301:Oracle High Availability Service started on node rac122.
2016-03-26 22:13:47.951
[ohasd(6210)]CRS-8017:location: /etc/oracle/lastgasp has 2 reboot advisory log files, 0 were announced and 0 errors occurred


CAUSE :

From the alert log file cluster services prior to the start of CSSD daemon, GPnP daemon on rac121/123 copies the GPnP profile from rac122 and file pending.xml is created in /grid/app/11.2.0.3/grid/gpnp/rac122/profiles/peer

[oracle@rac122 peer]$ ls -l
total 8
-rw-r--r-- 1 oracle oinstall 1970 Mar 26 22:52 pending.xml
-rw-r--r-- 1 oracle oinstall 1970 Mar 26 22:52 profile.xml

Below are logs details from gpnp.log files under /grid/app/11.2.0.3/grid/log/rac122/gpnpd/gpnpd.log

2016-03-26 22:52:33.542: [    GPNP][1580644128]clsgpnpdRCB: [at clsgpnpd.c:3933] GPnPD endpoint url "mdns:gpnp._tcp://rac122:59049/agent=gpnpd,cname=rac-cluster,host=rac122,pid=11790/gpnpd h:rac122 c:rac-cluster" successfully advertised with RD
2016-03-26 22:52:38.735: [    GPNP][1560835840]clsgpnp_profileCallUrlInt: [at clsgpnp.c:2104] put-profile call to url "tcp://rac123:11367" disco "mdns:service:gpnp._tcp.local.://rac123:11367/agent=gpnpd,cname=rac-cluster,host=rac123,pid=21853/gpnpd h:rac123 c:rac-cluster" [f=0 claimed- host:rac122 cname:rac-cluster seq:7 auth:CN=GPnP_peer]
2016-03-26 22:52:38.743: [    GPNP][1560835840]clsgpnp_profileCallUrlInt: [at clsgpnp.c:2234] Result: (25) CLSGPNP_DUPLICATE. Successful put-profile CALL to remote "tcp://rac123:11367" disco "mdns:service:gpnp._tcp.local.://rac123:11367/agent=gpnpd,cname=rac-cluster,host=rac123,pid=21853/gpnpd h:rac123 c:rac-cluster"
2016-03-26 22:52:38.744: [    GPNP][1560835840]clsgpnp_profileCallUrlInt: [at clsgpnp.c:2104] put-profile call to url "tcp://rac121:15322" disco "mdns:service:gpnp._tcp.local.://rac121:15322/agent=gpnpd,cname=rac-cluster,host=rac121,pid=25251/gpnpd h:rac121 c:rac-cluster" [f=0 claimed- host:rac122 cname:rac-cluster seq:7 auth:CN=GPnP_peer]
2016-03-26 22:52:38.792: [    GPNP][1560835840]clsgpnp_profileCallUrlInt: [at clsgpnp.c:2234] Result: (25) CLSGPNP_DUPLICATE. Successful put-profile CALL to remote "tcp://rac121:15322" disco "mdns:service:gpnp._tcp.local.://rac121:15322/agent=gpnpd,cname=rac-cluster,host=rac121,pid=25251/gpnpd h:rac121 c:rac-cluster"




[oracle@rac122 peer]$ ls -l pending*
total 8
-rw-r--r-- 1 oracle oinstall 1970 Mar 26 22:52 pending.xml



SOLUTION

Whenever GPnP profile is not present on surviving node, it will automatically copied from existing nodes by GPnPd as pending.xml . Whenever, profile is updated, it is automatically renamed to profile.xml.

Get the ASM_DISKSTRING parameter value  and try to change with the existing value in order to update the  GPnP profile on surviving node.

[oracle@rac122 gpnpd]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Sat Mar 26 23:03:37 2016

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 Real Application Clusters and Automatic Storage Management options

SQL> show parameter disk

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string      DG_DATA, DG_FLASH
asm_diskstring                       string      ORCL:*


SQL> alter system set asm_diskstring='ORCL:*';

System altered.


Now verify the existence of pending.xml files.  It's gone now.

-rw-r--r-- 1 oracle oinstall 1970 Mar 26 22:52 profile.xml

oracle@rac122 peer]$



Hope this help :)

Tuesday, March 22, 2016

ORA-13757: “SQL Tuning Set” “string” owned by user “string” is active.


ORA-13757: “SQL Tuning Set” “string”
owned by user “string” is active.

Demonstration

In today's demonstration, I am trying to recreate the SQL Tuning Set with existing name.  Approach we are using to complete this is delete the old STS and create the new one with existing name.  But when we try to drop the tuning set, we are getting below error. 

Reason for the error is dependencies existing on existing tuning set.  Goal is remove the dependency and the drop the existing tuning set.

Below are step by step workaround to solve this issue.


On Enterprise Manager :

We are getting below error while dropping from EM Console.

This SQL Tuning Set cannot be deleted because there are advisors tasks dependent on it.

And when we manually try to delete from SQLPLUS then getting below error.  This error clearly shows that this tuning set is having some dependency.

To resolve this issue first we need to remove the dependency then will delete this STS.

ORA-13757: “SQL Tuning Set” “string” owned by user “string” is active.

Workaround

Conn /as sysdba

SQL> set lin200 pages 200

SQL> select d.name
FROM wri$_sqlset_definitions d, wri$_sqlset_references r
WHERE d.name = 'STS_TEST'
AND r.sqlset_id = d.id;

NAME
------------------------------
STS_TEST

Try to remove tuning set.  Getting error "Active Tuning Set"

SQL> exec dbms_sqltune.drop_sqlset('STS_TEST');
BEGIN dbms_sqltune.drop_sqlset('STS_TEST'); END;

*
ERROR at line 1:
ORA-13757: "SQL Tuning Set" "STS_TEST" owned by user "SYS" is active.
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 13213
ORA-06512: at "SYS.DBMS_SQLTUNE", line 4563
ORA-06512: at line 1


Check the active tuning set related entry from WRI$_SQL_REFERENCES

SQL> SELECT count(*)
FROM wri$_sqlset_definitions d, wri$_sqlset_references r
WHERE d.name = 'STS_TEST'
AND r.sqlset_id = d.id;

  COUNT(*)
----------
         1

SQL> select d.name
FROM wri$_sqlset_definitions d, wri$_sqlset_references r
WHERE d.name = 'STS_TEST'
AND r.sqlset_id = d.id; 

NAME
------------------------------
STS_TEST

Manually delete The related entry from WRI$_SQL_REFERENCES

SQL> delete from wri$_sqlset_references
where sqlset_id in (select id
                  from wri$_sqlset_definitions
                  where name ='STS_TEST'
                  );
1 row deleted.


SQL> commit ;

Commit complete.


Now it's high time to drop the SQL Tuning Set.

SQL> exec dbms_sqltune.drop_sqlset('STS_TEST','SYS');

PL/SQL procedure successfully completed.

Validate the dropped SQL Tuning Set.

SQL> SELECT COUNT(*) FROM DBA_SQLSET WHERE  NAME = 'STS_TEST';


  COUNT(*)
----------
         0




Hope this help :)