Archive for category Oracle Database

PostgreSQL 9.6 on RHEL 7.2 -FATAL: semctl(SETVAL) failed: Invalid argument

PostgreSQL 9.6 on RHEL 7.2 -FATAL: semctl(SETVAL) failed: Invalid argument

The error below started occurring once we upgraded to PostgreSQL 9.6 on RHEL 7.2. It was strange because all we did was had a user to login, check the status of pgsql and logout.

 Dec 7 22:50:01 hostname systemd: Created slice user-1004.slice.
 Dec 7 22:50:01 hostname systemd: Starting user-1004.slice.
 Dec 7 22:50:01 hostname systemd: Started Session 2243 of user postgres.
 Dec 7 22:50:01 hostname systemd: Starting Session 2243 of user postgres.
 Dec 7 22:50:01 hostname systemd: Created slice user-0.slice.
 Dec 7 22:50:01 hostname systemd: Starting user-0.slice.
 Dec 7 22:50:01 hostname systemd: Started Session 2244 of user root.
 Dec 7 22:50:01 hostname systemd: Starting Session 2244 of user root.
 Dec 7 22:50:01 hostname postgres[12064]: [432-1] [unknown]: [local]: 58481389.2f20: LOG: connection received: host=[local]
 Dec 7 22:50:01 hostname postgres[12064]: [433-1] postgres: [local]: 58481389.2f20: FATAL: semctl(153976863, 2, SETVAL, 0) failed: Invalid argument
 Dec 7 22:50:01 hostname postgres[21799]: [432-1] : : 58444abd.5527: LOG: server process (PID 12064) exited with exit code 1
 Dec 7 22:50:01 hostname postgres[21799]: [433-1] : : 58444abd.5527: LOG: terminating any other active server processes
 Dec 7 22:50:01 hostname postgres[11514]: [434-1] repmgr: <IP_ADDRESS>(41171): 58480edb.2cfa: WARNING: terminating connection because of crash of another server process
 Dec 7 22:50:01 hostname postgres[11514]: [434-2] repmgr: <IP_ADDRESS>(41171): 58480edb.2cfa: DETAIL: The postmasteIP_ADDRESSr has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
 Dec 7 22:50:01 hostname postgres[11514]: [434-3] repmgr: <IP_ADDRESS>(41171): 58480edb.2cfa: HINT: In a moment you should be able to reconnect to the database and repeat your command.
 Dec 7 22:50:01 hostname postgres[12074]: [434-1] [unknown]: ::1(48077): 58481389.2f2a: LOG: connection received: host=::1 port=48077
 Dec 7 22:50:01 hostname postgres[12074]: [435-1] repmgr: ::1(48077): 58481389.2f2a: FATAL: the database system is in recovery mode
 Dec 7 22:50:01 hostname Backup: [INFO]DB check start.
 Dec 7 22:50:01 hostname postgres[21799]: [434-1] : : 58444abd.5527: LOG: all server processes terminated; reinitializing
 Dec 7 22:50:01 hostname postgres[21799]: [435-1] : : 58444abd.5527: LOG: could not remove shared memory segment "/PostgreSQL.1721076088": No such file or directory
 Dec 7 22:50:01 hostname postgres[21799]: [436-1] : : 58444abd.5527: LOG: semctl(152961024, 0, IPC_RMID, ...) failed: Invalid argument
 Dec 7 22:50:01 hostname postgres[21799]: [437-1] : : 58444abd.5527: LOG: semctl(152993793, 0, IPC_RMID, ...) failed: Invalid argument

After researching on the issue, we found this bug :).

Resolution

1) Set RemoveIPC=no in /etc/systemd/logind.conf

2) Reboot the server or restart systemd-logind as follows:
 # systemctl daemon-reload
 # systemctl restart systemd-logind

Reference:

  1. https://bugzilla.redhat.com/show_bug.cgi?id=1287618
  2. https://bugzilla.redhat.com/show_bug.cgi?id=1264533

Note:

  • Please note that I have seen this RHEL 7 issue with Oracle database too. It’s a RHEL 7 bug so it should applies to most database using IPC calls.

Hope this helps.

Regards,
Wei Shan

Leave a comment

Oracle 11gR2 – Default Audit Policies

Oracle 11gR2 – Default Audit Policies

By default, when we create a new database instance in Oracle, the default value for AUDIT_TRAIL is set to “none”. When you set it to “DB” or “DB,EXTENDED”, it will have a set of default logging policies.

SQL> select * from dba_priv_audit_opts;

USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
 CREATE EXTERNAL JOB BY ACCESS BY ACCESS
 CREATE ANY JOB BY ACCESS BY ACCESS
 GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
 EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
 CREATE ANY LIBRARY BY ACCESS BY ACCESS
 GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
 DROP PROFILE BY ACCESS BY ACCESS
 ALTER PROFILE BY ACCESS BY ACCESS
 DROP ANY PROCEDURE BY ACCESS BY ACCESS
 ALTER ANY PROCEDURE BY ACCESS BY ACCESS
 CREATE ANY PROCEDURE BY ACCESS BY ACCESS
 ALTER DATABASE BY ACCESS BY ACCESS
 GRANT ANY ROLE BY ACCESS BY ACCESS
 CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
 DROP ANY TABLE BY ACCESS BY ACCESS
 ALTER ANY TABLE BY ACCESS BY ACCESS
 CREATE ANY TABLE BY ACCESS BY ACCESS
 DROP USER BY ACCESS BY ACCESS
 ALTER USER BY ACCESS BY ACCESS
 CREATE USER BY ACCESS BY ACCESS
 CREATE SESSION BY ACCESS BY ACCESS
 AUDIT SYSTEM BY ACCESS BY ACCESS
 ALTER SYSTEM BY ACCESS BY ACCESS

By default, it will log ALL sessions. This will increase the audit log size by a tremendous amount. Thus, unless you have a unique business requirement, it will be wiser if we just log failure login attempts only.

Disable audit logging on all session creation. Enable audit logging on unsuccessful session creation.

SQL> noaudit create session;
SQL> audit create session whenever not successful;

Verify Settings.

SQL> select * from dba_priv_audit_opts;

USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
 CREATE SESSION NOT SET BY ACCESS

Hope this helps!

Regards,
Wei Shan

Leave a comment

Investigating Oracle Database Memory Pressure Behavior

Investigating Oracle Database Memory Pressure Behavior

Oracle database will grab all and release as the O/S sees memory pressure.

I came across the above statement recently during a discussion., which led to this blog post. I wasn’t very sure if it was true so I did not reply to it. I’m aware of the behavior when it face CPU pressure but not memory pressure. So, this has definitely piqued my curiosity to find out more.


In Oracle 11gR2 and later, we can choose to use have the following options:

  • Automatic Memory Management (AMM)
    • We configure MEMORY_TARGET and MEMORY_MAX _TARGET and let Oracle database manage the SGA and PGA for us
  • Automatic Shared Memory Management (ASMM)
    • We configure SGA_TARGET and SGA_MAX_SIZE and let Oracle manage SGA for us.
  • Automatic PGA Memory (Usually used together with ASMM)
    • We configure PGA_AGGREGATE_TARGET and let Oracle manage PGA for us.

So, we can be sure that the parameters below affect Oracle database memory usage on a system. (Of course, we are not referring to manual memory tuning.)

  1. MEMORY_MAX _TARGET
  2. MEMORY_TARGET
  3. SGA_TARGET
  4. SGA_MAX_SIZE
  5. PGA_AGGREGATE_TARGET

It is widely understood that the above parameters are merely a a target. It may exceed the optimum size due to other circumstances. For example, the number of users increases beyond limit or insane SQL queries that exceed PGA value. PGA size is the unknown factor here, since we do not know how many users or what kind of queries will be running in the database.

The question here is, when it uses more memory than the target value, does it start to swap or does it grab the free memory from the non-allocated memory from the O/S. I would think that it will use free memory then swap space since it will ask the memory from O/S until it has memory pressure which then, it will start to swap.

Let’s test it out!

Let’s login and check all the baseline numbers.

[oracle@primary ~]$ sqlplus / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter pga_aggregate_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 200M

#free -m

total used free shared buffers cached
Mem: 2002 721 1281 183 24 451
-/+ buffers/cache: 245 1757
Swap: 4031 0 4031


select a.name, to_char(b.value, '999,999,999') bytes,
to_char(round(b.value/1024/1024,1), '99,999.9' ) mbytes
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name like '%ga memory%';

NAME BYTES MBYTES
-------- ------------ ---------
session uga memory 2,515,088 2.4
session uga memory max 2,515,088 2.4
session pga memory 3,555,880 3.4
session pga memory max 22,889,000 21.8

The PGA_AGGREGATE_TARGET is configure to use 200MB. Currently it is only using 2.4MB. We have 4GB of swap space configured as well.

Let’s create a package and generate the workload

SQL> create or replace package demo_pkg
 as
 type array is table of char(2000) index by binary_integer;
 g_data array;
 end;
 /

SQL> begin
 for i in 1 .. 2000000
 loop
 demo_pkg.g_data(i) := 'x';
 end loop;
 end;
 /
 ERROR at line 1:
 ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi,pl/sql vc2)

The above SQL script definitely uses more than 2GB of memory for sorting, which lead to ORA-04030 error.

From another separate SSH session, let’s monitor the memory usage via vmstat

[root@primary ~]# vmstat 1 6000000
 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r b swpd free buff cache si so bi bo in cs us sy id wa st
 0 0 0 1311424 24952 462624 0 0 1611 58 303 486 6 10 83 1 0
 0 0 0 1311392 24952 462628 0 0 0 16 151 282 0 1 99 0 0
 0 0 0 1311384 24952 462628 0 0 0 0 118 228 0 0 100 0 0
 0 0 0 1311384 24952 462628 0 0 0 32 151 287 0 0 100 0 0
 0 0 0 1311384 24952 462628 0 0 0 0 112 220 0 0 100 0 0
 0 0 0 1311384 24952 462628 0 0 0 0 141 263 0 1 99 0 0
 0 0 0 1311384 24960 462628 0 0 0 52 135 267 0 0 99 1 0
 0 0 0 1311384 24960 462628 0 0 0 0 141 271 0 0 100 0 0
 0 0 0 1311384 24960 462628 0 0 0 0 109 215 0 0 100 0 0
 0 0 0 1311384 24960 462628 0 0 0 32 150 283 0 0 100 0 0
 0 0 0 1311384 24960 462628 0 0 0 0 123 232 0 0 100 0 0
 0 0 0 1311384 24968 462624 0 0 0 20 144 276 0 1 99 0 0
 0 0 0 1311392 24968 462628 0 0 0 32 121 250 0 0 100 0 0
 0 0 0 1311136 24968 462628 0 0 0 0 167 329 1 1 98 0 0
 0 0 0 1311136 24968 462628 0 0 0 0 109 211 0 0 100 0 0
 1 0 0 226260 24968 463068 0 0 280 32 998 310 22 67 10 0 0
 2 4 86096 13744 60 105252 0 93124 12636 93144 2101 1698 13 86 0 1 0
 3 2 314548 14296 60 82612 1596 226304 29708 226304 2234 1695 9 91 0 0 0
 3 4 552488 13732 60 83128 9568 244640 35048 244664 2549 2140 8 90 0 2 0
 2 0 806904 13888 60 82228 1920 267312 34484 267344 2438 1904 9 89 0 2 0

The highlighted values above are the period where the SQL query executed. Noticed that the free memory was at 1.3GB until it drop to 200MB before it actually start to swap.

Summary

From the above, I have highlighted the important points:

  1. It is not possible to “overuse” SGA as it uses LRU algorithm to remove unnecessary objects under memory pressure.
  2. Although we configure PGA to only use a maximum of 200MB, it is possible to exceed this value due to user queries or sessions.
  3. Under memory pressure, the database will try to use available memory until it doesn’t have a choice, which it will choose to use swap space.
  4. When space space run out, the database will hit ORA-04030 error.

Regards,
Wei Shan

References: Expert Oracle Database Architecture -Thomas Kyte

 

 

 

 

Leave a comment

WARNING – Granting of psql/sqlplus executables to sudoers in Linux/Unix

WARNING – Granting of psql/sqlplus executables to sudoers in Linux/Unix

I discovered this behavior accidentally during my project work recently. I needed psql access to a RHEL 6 system. However, the company policy enforces a minimum access policy (Well done!). Thus, my account was given a sudoer permission to only use psql executable. I needed to perform DBA tasks on this system.

Let me show you the issue.

[weishan]$ sudo su -
[sudo] password for weishan:
ユーザー user は'/bin/su -' を root として  上で実行することは許可されていません。すみません。
(This means user not allowed to execute su -)

[weishan]$ tail -n 10 /var/log/messages
tail: `messages' を 読み込み用でオープンできません: 許可がありません
(This means access denied)

[weishan]$ sudo /home/postgres/pgsql/bin/psql -h localhost -p 5432 -U postgres postgres
[sudo] password for weishan:
psql (9.2.4)
Type "help" for help.

Looking at the output above, the sudoer was configured correctly! Now, let’s see..

psql> \!
[root@gsdb03s log]# tail -n 5 /var/log/messages
May 10 15:34:47  snmpd[22564]: Connection from UDP: [ip]:63484->[ip]
May 10 15:34:47 snmpd[22564]: Connection from UDP: [ip]:63484-> [ip]
May 10 15:34:47 snmpd[22564]: Connection from UDP: [ip]:63484-> [ip]
May 10 15:34:47 snmpd[22564]: Connection from UDP: [ip]:63484-> [ip]

From the above, we can show that it is technically possible for unintended DBAs to get root access even if you did not allow them access explicitly. Thus, we should be mindful while granting psql or sqlplus executable.

I did not test this on sqlplus, but the behaviour should be the same since both psql and sqlplus allow the user to “temporarily” exit the console to OS level access.

Moral of the story:

  1. Do not configure sudo for user to access psql/sqlplus to login to postgres as root user directly.
  2. Configure sudo to only allow weishan user to “sudo su -” to postgres user.
  3. Configure logging for sudo access

Regards,
Wei Shan

 

 

Leave a comment

Oracle 11gR2 – DBA_DATA_FILES MAXBYTES value 0

Oracle 11gR2 – DBA_DATA_FILES MAXBYTES value 0

When we query the DBA_DATA_FILES table (for scripting), we need to take extra care while getting data from MAXBYTES column. Notice the MAXBYTES value is 0 for the last 2 datafiles. The reason for this is because during the creation of the datafile, the MAXSIZE was not specified.

SQL> select TABLESPACE_NAME,FILE_NAME,MAXBYTES/1024/1024/1024 “GB” from dba_data_files;

TABLESPACE_NAME FILE_NAME GB
------------------------------ -------------------------------------------------- ----------
SYSTEM +DATA/testdb/datafile/system.269.894474543 10
AUDIT_TBS +DATA/testdb/datafile/audit_tbs.270.900889041 0
AUDIT_TBS +DATA/testdb/datafile/audit_tbs.271.909620769 0

Now, when we create the datafile is MAXSIZE specified:

SQL> alter tablespace AUDIT_TBS add datafile '+DATA' size 20G autoextend on maxsize 30G;

Let’s review the DBA_DATA_FILES output again:

SQL> select TABLESPACE_NAME,FILE_NAME,MAXBYTES/1024/1024/1024 "GB" from dba_data_files;

TABLESPACE_NAME FILE_NAME GB
------------------------------ -------------------------------------------------- ----------
SYSTEM +DATA/spa/datafile/system.269.894474543 10
AUDIT_TBS +DATA/spa/datafile/audit_tbs.270.900889041 0
AUDIT_TBS +DATA/spa/datafile/audit_tbs.271.909620769 0
AUDIT_TBS +DATA/spa/datafile/audit_tbs.272.909622999 30

That’s all for the time being!

Regards,
Wei Shan

Leave a comment

Oracle RAC 11gR2- Configure job scheduler to run on a preferred node

Oracle RAC 11gR2- Configure job scheduler to run on a preferred node

In Oracle RAC, a job that is defined in the scheduler may run on either node by default. However, there are JOB_SCHEDULER properties you can configure to change that.

INSTANCE_STICKINESS

This attribute should only be used for a database running in an Oracle Real Application Clusters (Oracle RAC) environment. By default, it is set to TRUE. If you set instance_stickiness to TRUE, jobs start running on the instance with the lightest load and the Scheduler thereafter attempts to run on the instance that it last ran on. If that instance is either down or so overloaded that it does not start new jobs for a significant period of time, another instance runs the job. If the interval between runs is large, instance_stickiness is ignored and the job is handled as if it were a non-sticky job.

If instance_stickiness is set to FALSE, each instance of the job runs on the first instance available. For environments other than Oracle RAC, this attribute is not useful because there is only one instance.

INSTANCE_ID

Valid only in an Oracle Real Application Clusters environment. Indicates the instance on which the job is to be run.

Basic Example

SQL> select OWNER,JOB_NAME,INSTANCE_STICKINESS,INSTANCE_ID from dba_scheduler_jobs where JOB_NAME like 'JOB%';
OWNER JOB_NAME INSTA INSTANCE_ID
------------------------------ ------------------------------ ----- --------
USER DBA_JOB_1 TRUE

Use the DBMS_SCHEDULER.SET_ATTRIBUTE package

SQL> exec dbms_scheduler.set_attribute(name => 'USER.DBA_JOB_1' ,attribute=>'INSTANCE_ID', value=>'1');
PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.set_attribute(name => 'USER.DBA_JOB_1' ,attribute=>'INSTANCE_STICKINESS', value=>FALSE);
PL/SQL procedure successfully completed.

Results

SQL> select OWNER,JOB_NAME,INSTANCE_STICKINESS,INSTANCE_ID from dba_scheduler_jobs where JOB_NAME like '%JOB%';
OWNER JOB_NAME INSTA INSTANCE_ID
------------------------------ ------------------------------ ----- --------
USER DBA_JOB_1 FALSE 1

Regards,
Wei Shan

Leave a comment

Oracle RAC Database – CRS-4535/CRS-4536/CRS-4534 Error

Oracle RAC Database – CRS-4535/CRS-4536/CRS-4534 Error

1 of the RAC node crashed due to device full error. Upon starting up the RAC node, it hit the following error.

node1# /u01/app/11.2.0/grid/bin/crsctl check cluster
 CRS-4535: Cannot communicate with Cluster Ready Services
 CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
 CRS-4534: Cannot communicate with Event Manager

From octssd.log (Note the bolded errors)

2016-03-06 14:39:17.943: [ CRSCCL][4036015872]USING GIPC ============ 
2016-03-06 14:39:17.943: [ CRSCCL][4036015872]clsCclGipcListen: Attempting to listen on gipcha://node1:CTSSGROUP_1. 
2016-03-06 14:39:17.943: [GIPCHGEN][4036015872] gipchaInternalRegister: Initializing HA GIPC 
2016-03-06 14:39:17.944: [GIPCHGEN][4036015872] gipchaNodeCreate: adding new node 0x7f6fe4031900 { host '', haName 'b8b8-843d-c412-444e', srcLuid 1d17e809-00000000, dstLuid 00000000-00000000 numInf 0, contigSeq 0, lastAck 0, lastValidAck 0, sendSeq [0 : 0], createTime 917676, sentRegister 0, localMonitor 0, flags 0x1 } 
2016-03-06 14:39:17.944: [GIPCHTHR][3935278848] gipchaWorkerThread: starting worker thread hctx 0x70b900 [0000000000000010] { gipchaContext : host 'node1', name 'b8b8-843d-c412-444e', luid '1d17e809-00000000', numNode 0, numInf 0, usrFlags 0x0, flags 0xc000 } 
2016-03-06 14:39:17.944: [GIPCHDEM][3933177600] gipchaDaemonThread: starting daemon thread hctx 0x70b900 [0000000000000010] { gipchaContext : host 'node1', name 'b8b8-843d-c412-444e', luid '1d17e809-00000000', numNode 0, numInf 0, usrFlags 0x0, flags 0xc000 } 
2016-03-06 14:39:17.945: [GIPCXCPT][3933177600] gipchaDaemonProcessConnect: connection to daemon failed for endp 0x731c20 [0000000000000118] { gipcEndpoint : localAddr 'ipc', remoteAddr 'ipc://gipcd_node1', numPend 0, numReady 0, numDone 0, numDead 0, numTransfer 0, objFlags 0x0, pidPeer 0, readyRef (nil), ready 0, wobj 0x71a660, sendp 0x71a4a0flags 0x8861a, usrFlags 0x24020 }, hctx 0x70b900 [0000000000000010] { gipchaContext : host 'node1', name 'b8b8-843d-c412-444e', luid '1d17e809-00000000', numNode 0, numInf 0, usrFlags 0x0, flags 0x5 }, ret gipcretConnectionRefused (29) 
2016-03-06 14:39:17.945: [GIPCHDEM][3933177600] gipchaDaemonThreadEntry: EXCEPTION[ ret gipcretConnectionRefused (29) ] terminating daemon thread due to exception 
2016-03-06 14:39:17.945: [GIPCHDEM][3933177600] gipchaDaemonThreadEntry: daemon thread exiting state gipchaThreadStateFailed (5) 
2016-03-06 14:39:17.945: [GIPCXCPT][4036015872] gipchaInternalResolve: failed to resolve ret gipcretDaemonLost (34), host 'node1', port 'CTSSGROUP_1', hctx 0x70b900 [0000000000000010] { gipchaContext : host 'node1', name 'b8b8-843d-c412-444e', luid '1d17e809-00000000', numNode 0, numInf 0, usrFlags 0x0, flags 0xd }, ret gipcretDaemonLost (34) 
2016-03-06 14:39:17.945: [GIPCHGEN][4036015872] gipchaResolveF [gipcmodGipcResolve : gipcmodGipc.c : 806]: EXCEPTION[ ret gipcretDaemonLost (34) ] failed to resolve ctx 0x70b900 [0000000000000010] { gipchaContext : host 'node1', name 'b8b8-843d-c412-444e', luid '1d17e809-00000000', numNode 0, numInf 0, usrFlags 0x0, flags 0xd }, host 'node1', port 'CTSSGROUP_1', flags 0x0 
2016-03-06 14:39:17.946: [GIPCXCPT][4036015872] gipchaProcessClientRequest: request failed due to failure in ha threads req 0x7ffc6a994200,req type 1, hctx 0x70b900 [0000000000000010] { gipchaContext : host 'node1', name 'b8b8-843d-c412-444e', luid '1d17e809-00000000', numNode 0, numInf 0, usrFlags 0x0, flags 0xd }, ret gipcretDaemonLost (34) 
2016-03-06 14:39:17.946: [GIPCHGEN][4036015872] gipchaPublishF [gipcmodGipcBind : gipcmodGipc.c : 884]: EXCEPTION[ ret gipcretDaemonLost (34) ] failed to publish ctx 0x70b900 [0000000000000010] { gipchaContext : host 'node1', name 'b8b8-843d-c412-444e', luid '1d17e809-00000000', numNode 0, numInf 0, usrFlags 0x0, flags 0xd }, endp 0x7f6fe40375b0 [0000000000000126] { gipchaEndpoint : port 'CTSSGROUP_1', peer ':', srcCid 00000000-00000000, dstCid 00000000-00000000, numSend 0, maxSend 100, groupListType 1, hagroup 0x7f6fe4032310, usrFlags 0x4000, flags 0x0 }, port 'CTSSGROUP_1', flags 0x4000 
2016-03-06 14:39:17.946: [GIPCXCPT][4036015872] gipcBindF [clsCclGipcListen : clsCclCommHandler.c : 3553]: EXCEPTION[ ret gipcretDaemonLost (34) ] failed to bind endp 0x7f6fe402fcc0 [0000000000000101] { gipcEndpoint : localAddr 'gipcha://node1:CTSSGROUP_1', remoteAddr '', numPend 0, numReady 0, numDone 0, numDead 0, numTransfer 0, objFlags 0x0, pidPeer 0, readyRef (nil), ready 0, wobj 0x7f6fe4024fa0, sendp (nil)flags 0x30400, usrFlags 0x20 }, addr 0x7f6fe402f230 [00000000000000ff] { gipcAddress : name 'gipcha://node1:CTSSGROUP_1', objFlags 0x0, addrFlags 0x0 }, flags 0x0 
2016-03-06 14:39:17.946: [ CRSCCL][4036015872]gipcBind() failed. rc= 34. 
2016-03-06 14:39:17.946: [ CRSCCL][4036015872]cclLibShutdown called
2016-03-06 14:39:17.946: [ CRSCCL][4036015872]ccllibShutdown done.
2016-03-06 14:39:17.946: [ CTSS][4036015872](:ctss_ccl_init1:): Fails to initialize CCL [2]. Returns [16] 
2016-03-06 14:39:17.946: [ CTSS][4036015872]ctss_main: CCL init failed [16] 
2016-03-06 14:39:17.946: [ CTSS][4036015872]ctss_main: CTSS daemon aborting [16]. 
2016-03-06 14:39:17.946: [ CTSS][4036015872]CTSS daemon aborting

From gipcd.log (Note the bolded errors)

2016-03-06 14:39:16.979: [GIPCDCLT][3731310336] gipcdClientThread: gipcListen() failed (1) on endp 000000000000010c 
2016-03-06 14:39:16.979: [ GIPCD][3731310336] gipcdSetThreadState: changing the status of clientThread. current status gipcdThreadStatusInit desired status gipcdThreadStatusOffline 
2016-03-06 14:39:16.979: [ GIPCLIB][3731310336] gipclibMapSearch: gipcMapSearch() -> gipcMapGetNodeAddr() failed: ret:gipcretKeyNotFound (36), ht:0x5a72e0, idxPtr:0x7f61df29a8c0, key:0x7f61de6725b0, flags:0x0 
2016-03-06 14:39:16.979: [GIPCXCPT][3731310336] gipcObjectLookupF [gipcPostF : gipc.c : 2022]: search found no matching oid 0000000000000000, ret gipcretKeyNotFound (36), ret gipcretInvalidObject (3) 
2016-03-06 14:39:16.979: [GIPCXCPT][3731310336] gipcPostF [gipcdClientThread : gipcdClientThread.c : 3485]: EXCEPTION[ ret gipcretInvalidObject (3) ] failed to post obj 0000000000000000, flags 0x0 
2016-03-06 14:39:16.980: [GIPCDCLT][3731310336] gipcdClientThread: Client thread has exited 
2016-03-06 14:39:16.980: [ GIPCD][3727107840] gipcdThreadWait: GIPCD received a shutdown msg from agent framework or client/node/monitor thread died 
2016-03-06 14:39:16.980: [GIPCDMON][3727107840] gipcdMonitorThread: clientThread/nodeThread did not came into 'READY' state 
2016-03-06 14:39:16.980: [ GIPCD][3727107840] gipcdSetThreadState: changing the status of monitorThread. current status gipcdThreadStatusReady desired status gipcdThreadStatusOffline 
2016-03-06 14:39:16.980: [GIPCDMON][3727107840] gipcdMonitorThread: Monitor thread is exiting.. 
2016-03-06 14:39:16.980: [ GIPCD][3831981856] gipcdMain: invalid status of gipcd Threads. Status of clientThread gipcdThreadStatusOffline Status of nodeThread gipcdThreadStatusInit Status of monitorThread gipcdThreadStatusOffline 
2016-03-06 14:39:16.986: [ GIPC][3722905344] gipcCheckInitialization: possible incompatible non-threaded init from [clsgpnp0.c : 769], original from [gipcd.c : 177] 
2016-03-06 14:39:16.986: [ GIPCLIB][3722905344] gipclibSetTraceLevel: to set level to 0 
[ CLWAL][3722905344]clsw_Initialize: OLR initlevel [30000] 
2016-03-06 14:39:16.987: [ GIPC][3722905344] gipcCheckInitialization: possible incompatible non-threaded init from [prom.c : 690], original from [gipcd.c : 177] 
2016-03-06 14:39:16.987: [ GIPCLIB][3722905344] gipclibSetTraceLevel: to set level to 0 
2016-03-06 14:39:17.007: [ GPNP][3722905344]clsgpnp_getCachedProfileEx: [at clsgpnp.c:613] Result: (26) CLSGPNP_NO_PROFILE. Can't get offline GPnP service profile: local gpnpd is up and running. Use getProfile instead. 
2016-03-06 14:39:17.007: [ GPNP][3722905344]clsgpnp_getCachedProfileEx: [at clsgpnp.c:623] Result: (26) CLSGPNP_NO_PROFILE. Failed to get offline GPnP service profile. 
2016-03-06 14:39:17.015: [ GIPCLIB][3722905344] gipclibGetClusterGuid: retrieved cluster guid 770e66ca03467f5bbfbba3432ee2aa7b 
2016-03-06 14:39:17.025: [ GIPCLIB][3722905344] gipclibSetTraceLevel: to set level to 0 
[ CLWAL][3722905344]clsw_Initialize: OLR initlevel [70000] 
2016-03-06 14:39:17.025: [ GIPCLIB][3722905344] gipclibSetTraceLevel: to set level to 0 
2016-03-06 14:39:17.048: [ CLSINET][3722905344] Returning NETDATA: 1 interfaces 
2016-03-06 14:39:17.048: [ CLSINET][3722905344] # 0 Interface 'bond0',ip='192.168.0.5',mac='40-f2-e9-25-b6-93',mask='255.255.255.0',net='192.168.0.0',use='cluster_interconnect' 
2016-03-06 14:39:17.048: [GIPCHGEN][3722905344] gipchaNodeAddInterface: adding interface information for inf 0x9530d0 { host '', haName 'gipcd_ha_name', local (nil), ip '192.168.0.5', subnet '192.168.0.0', mask '255.255.255.0', mac '40-f2-e9-25-b6-93', ifname 'bond0', numRef 0, numFail 0, idxBoot 0, flags 0x1841 } 
2016-03-06 14:39:17.048: [GIPCHTHR][3725006592] gipchaWorkerCreateInterface: created local interface for node 'node1', haName 'gipcd_ha_name', inf 'udp://192.168.0.5:42229' 
2016-03-06 14:39:17.049: [GIPCHTHR][3725006592] gipchaWorkerCreateInterface: created local bootstrap multicast interface for node 'node1', haName 'gipcd_ha_name', inf 'mcast://224.0.0.251:42424/192.168.0.5' 
2016-03-06 14:39:17.049: [GIPCHTHR][3725006592] gipchaWorkerCreateInterface: created local bootstrap multicast interface for node 'node1', haName 'gipcd_ha_name', inf 'mcast://230.0.1.0:42424/192.168.0.5' 
2016-03-06 14:39:17.049: [GIPCHTHR][3725006592] gipchaWorkerCreateInterface: created local bootstrap broadcast interface for node 'node1', haName 'gipcd_ha_name', inf 'udp://192.168.0.255:42424' 
2016-03-06 14:39:17.049: [GIPCDNDE][3729209088] gipcdNodeThread: gipcdNodeThread started 
2016-03-06 14:39:17.049: [ GIPCD][3729209088] gipcdSetThreadState: changing the status of nodeThread. current status gipcdThreadStatusInit desired status gipcdThreadStatusReady 
2016-03-06 14:39:17.050: [ GIPCD][3729209088] gipcdThreadWait: GIPCD received a shutdown msg from agent framework or client/node/monitor thread died 
2016-03-06 14:39:17.050: [GIPCDNDE][3729209088] gipcdNodeThread: clientThread/monitorThread did not came into 'READY' state 
2016-03-06 14:39:17.050: [GIPCDNDE][3729209088] gipcdNodeThreadShutdown: deleting all the peer connections 
2016-03-06 14:39:17.050: [ GIPCD][3729209088] gipcdSetThreadState: changing the status of nodeThread. current status gipcdThreadStatusReady desired status gipcdThreadStatusOffline 
2016-03-06 14:39:17.050: [GIPCDNDE][3729209088] gipcdNodeThread: Node thread has exited 
2016-03-06 14:39:17.050: [ GIPCD][3831981856] gipcdMain: All threads terminated 
2016-03-06 14:39:17.150: [ GIPCD][3831981856] gipcdMain: GIPCD terminated

Solution:

Issue was due to network socket files issue. When the node crashes previously, it did not clean up the socket files. Thus, when the node started, it couldn’t create the socket files to establish connectivity to the peer node.

root# crsctl stop crs -f
root# rm -rf /usr/tmp/.oracle/* /var/tmp/.oracle/* /tmp/.oracle/*
root# crsctl start crs

Regards,
Wei Shan

Leave a comment

Oracle Database – ORA 12014: table does not contain a primary key constraint

ORA-12014: table does not contain a primary key constraint

While creating a Materialized View (MVIEW) in the Oracle database, I hit the above error. It seems kinda strange to me. I did a oerr command to try and find out more information.

[oracle@localhost ~]$ oerr ora 12014
12014, 00000, "table '%s' does not contain a primary key constraint"
// *Cause: The CREATE MATERIALIZED VIEW LOG command was issued with the
// WITH PRIMARY KEY option and the master table did not contain
// a primary key constraint or the constraint was disabled.
// *Action: Reissue the command using only the WITH ROWID option, create a
// primary key constraint on the master table, or enable an existing
// primary key constraint.

Table that I wanted to create the MVIEW on

SQL> desc cust_id_sales_aggr
 Name Null? Type
 -------------------------------- -------- ------------------------
 CUST_ID NOT NULL NUMBER
 DOLLAR_SALES NUMBER
 CNT_DOLLARS NUMBER
 CNT NUMBER

How I created the MVIEW LOG

CREATE MATERIALIZED VIEW LOG ON cust_id_sales_aggr WITH ROWID
(CUST_ID,DOLLAR_SALES,CNT_DOLLARS,CNT)
INCLUDING NEW VALUES;

How I created the MVIEW

CREATE MATERIALIZED VIEW cust_id_sales_aggr_mv
PCTFREE 0 TABLESPACE users
STORAGE (INITIAL 8M)
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS SELECT cust_id,dollar_sales,cnt_dollars,cnt FROM cust_id_sales_aggr;

The error from the above MVIEW creation command.

ERROR at line 7:
ORA-12014: table 'CUST_ID_SALES_AGGR' does not contain a primary key constraint

How to resolve the error (Obvious from the oerr output)

  1. Create PK on the source table (CUST_ID_SALES_AGGR)
  2. While creating the MVIEW, use the option with ROWID.

I chose option #1 🙂

CREATE MATERIALIZED VIEW cust_id_sales_aggr_mv
PCTFREE 0 TABLESPACE users
STORAGE (INITIAL 8M)
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH ROWID
ENABLE QUERY REWRITE
AS SELECT cust_id,dollar_sales,cnt_dollars,cnt FROM cust_id_sales_aggr;

Regard,
Wei Shan

 

Leave a comment