CRS-2412: The Cluster Time Synchronization Service detects that the local time is significantly different from the mean cluster time

In crs alert.log, below message is shown every 30 minutes in 12.2.0.1 GI.

[OCTSSD(9932)]CRS-2412: The Cluster Time Synchronization Service 
detects that the local time is significantly different from the mean cluster time

ctss is running in observer mode, because NTP is running.

$ crsctl check ctss
CRS-4700: The Cluster Time Synchronization Service is in Observer mode.

Verification of NTP clock Synchronization across the cluster nodes is successful.

$ cluvfy comp clocksync -n all -verbose

...
..
.

Verifying NTP daemon or service using UDP port 123 ...PASSED
Verifying NTP daemon is synchronized with at least one external time source ...PASSED
Verifying Network Time Protocol (NTP) ...PASSED
Verifying Clock Synchronization ...PASSED

Verification of Clock Synchronization across the cluster nodes was successful.

CVU operation performed: Clock Synchronization across the cluster nodes
Date: 20/07/2018 11:12:41 AM
CVU home: /u01/app/12.2.0.1/grid/
User: grid

So this message can be ignored, because The Cluster Time Synchronization Service is in Observer mode, and NTP Clock Synchronization across the cluster nodes is successful as well.

Advertisements
Posted in GI and RAC | Tagged , , , , | Leave a comment

Use emcli Command To Get Target Detailed Information

1) emcli login:

$ emcli login -username='sysman'
Enter password :

Login successful

2) To get all targets:

$ emcli get_targets

3) To get all the RAC databases only:

$ emcli get_targets -target='rac_database'

4) To list rac database TESTDB details:

$emcli list -resource="Targets" -search="TARGET_NAME='TESTDB'" 

or

$emcli list -resource="Targets" -search="TARGET_NAME='TESTDB'" 
 -format="name:csv"

5) Also we can run sql query to get target information. To check rac database TESTDB is primary or standby database:

$emcli list -sql="select TARGET_NAME,TARGET_TYPE,TYPE_QUALIFIER2 
 from sysman.mgmt\$target where TARGET_NAME='TESTDB'"

TARGET_NAME TARGET_TYPE  TYPE_QUALIFIER2
TESTDB      rac_database Physical Standby
Rows:1
Posted in OEM12c, OEM13c | Tagged , , , , | Leave a comment

How to Apply April 2018 RU Patches

Here is the GI environment to be patched:

GI HOME:  12.2.0.1
DB HOMES: 12.2.0.1
          12.1.0.2
          11.2.0.4

Patches downloaded :

GI HOME: 12.2.0.1 : Combo OJVM Update 12.2.0.1.180417 and 
                    GI Update 12.2.0.1.180417 Patch 27726454, or
- 27468969/
          27674384 DATABASE APR 2018 RU 12.2.0.1.180417
          27464465 OCW APR 2018 RU 12.2.0.1.0.180417
          27458609 ACFS APR 2018 RU 12.2.0.1.0.180417 Only Grid Home
          26839277 DBWLM RELEASE UPDATE 12.2.0.1.170913 Only Grid Home
          27144050 TOMCAT RELEASE UPDATE 12.2.0.1.171023.0830 Only Grid Home
- 27475613/Oracle JavaVM Component Release Update 12.2.0.1.180417

--
-- 12.1.0.2
Combo OJVM PSU 12.1.0.2.180417 and GI PSU 12.1.0.2.180417 Patch 27726478
- 27475603/
          Oracle JavaVM Component 12.1.0.2.180417 Database PSU
- 27468957/
          27338013
          26983807 
          27338020  OCW 
          27338041  Database PSU 12.1.0.2.180417 Patch 27726471

--
-- 11.2.0.4
Combo OJVM PSU 11.2.0.4.180417 and GI PSU 11.2.0.4.180417 Patch 27726505
- 27475913/
           27338049 Database PSU 11.2.0.4.180417 Patch 27338049
           27441052 OCW PSU 11.2.0.4.180417
           22502505 ACFS PSU 11.2.0.4.160419

-27475598/Oracle JavaVM (OJVM) Component Database PSU 11.2.0.4.180417

1) Apply Apr 2018 RU 12.2.0.1.180417 for 12.2.0.1 GI HOME and DB HOME.

$GI_HOME/OPatch/opatchauto apply /PATCHES/27726454/27468969

2) Apply April 2018 RU for 12.1.0.2 DB HOME.

-- DB   
$cd $PATCH_TOP_LOCATION/27726478/27468957/27338041
$ opatch apply

-- OCW
$ $PATCH_TOP_LOCATION/27726478/27468957/27338020/custom/scripts/prepatch.sh 
 -dbhome  /u01/app/oracle/product/12.1.0/dbhome_1
$ opatch napply $PATCH_TOP_LOCATION/27726478/27468957/27338020 -local 
 -oh /u01/app/oracle/product/12.1.0/dbhome_1 -id 27338020
$ $PATCH_TOP_LOCATION/27726478/27468957/27338020/custom/scripts/postpatch.sh 
  -dbhome -dbhome /u01/app/oracle/product/12.1.0/dbhome_1

--OJAVA
$cd $PATCH_TOP_LOCATION/27726478/27475603
$ opatch apply -local

3)Apply April 2018 RU for 11.2.04 DB HOME.

-- DB PSU
$cd 27338049
$opatch prereq CheckConflictAgainstOHWithDetail -ph ./
$opatch apply or opatch apply -local ( RAC )

-- OJVM
$cd <PATCH_TOP_DIR>/27475598
$opatch prereq CheckConflictAgainstOHWithDetail -ph ./
$opatch apply or  opatch apply -local (if RAC)

--OCW
--11g version 
--chmod 750 on both files below if permission is not correct.
 $ ls -ltr 27475913/27441052/custom/scripts/p*patch.sh
-rw-r--r--. 1 oracle oinstall 4566 Feb 5 20:05 27475913/27441052/custom/scripts/prepatch.sh
-rw-r--r--. 1 oracle oinstall 8417 Feb 5 20:05 27475913/27441052/custom/scripts/postpatch.sh
$ chmod 750 27475913/27441052/custom/scripts/prepatch.sh
$ chmod 750 27475913/27441052/custom/scripts/postpatch.sh
$ $PATCH_TOP_LOCATION/27726505/27475913/27441052/custom/scripts/prepatch.sh 
  -dbhome /u01/app/oracle/product/11.2.0/dbhome_1
$ opatch napply -oh /u01/app/oracle/product/11.2.0/dbhome_1 
 -local PATCH_TOP_LOCATION/27726505/27475913/27441052/custom/server/27441052
$ $PATCH_TOP_LOCATION/27726505/27475913/27441052/custom/scripts/postpatch.sh 
-dbhome /u01/app/oracle/product/11.2.0/dbhome_1

4)Run “datapatch” or post patch SQL as per instructions.

Posted in GI and RAC, Upgrading and Patching | Tagged , , , , , , , | Leave a comment

RAC Database Hanging with “gc buffer busy acquire” and “gc buffer busy request” Wait Events

There is a RAC database hanging for a short period of time with high “gc buffer busy acquire” and “gc buffer busy request” wait events by sql id =”4vs91dcv7u1p6″:

insert into sys.aud$( sessionid, entryid, statement, ntimestamp#, 
userid, userhost, terminal, action#, returncode, obj$creator, 
obj$name, auth$privileges, auth$grantee, new$owner, new$name, 
ses$actions, ses$tid, logoff$pread, logoff$lwrite, logoff$dead, 
comment$text, spare1, spare2, priv$used, clientid, sessioncpu, 
proxy$sid, user$guid, instance#, process#, xid, scn, auditid, 
sqlbind, sqltext, obj$edition, dbid) values(:1, :2, :3, 
SYS_EXTRACT_UTC(SYSTIMESTAMP), :4, :5, :6, :7, :8, :9, :10, :11, 
:12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, 
:25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36)

AWR report shows AUD$ table is the segment with the highest “Global Cache Buffer Busy” wait event:

CAUSE

With AUDIT_TRAIL set for database (AUDIT_TRAIL=db), and the AUD$ and FGA_LOG$ tables located in a dictionary segment space managed SYSTEM tablespace, “gc” wait events are sometimes observed during heavy periods of database logon activity.

SOLUTION

Move AUD$ and  FGA_LOG$ tables onto an ASSM tablespace like SYSAUX. On production, make sure to run it during off peak hours.

set pagesize 120
set linesize 180
SELECT T.TABLE_NAME, TS.SEGMENT_SPACE_MANAGEMENT 
FROM DBA_TABLES T, DBA_TABLESPACES TS 
WHERE TS.TABLESPACE_NAME = T.TABLESPACE_NAME 
AND T.TABLE_NAME = 'AUD$';

TABLE_NAME  SEGMEN
----------- ------
AUD$        MANUAL

select TABLESPACE_NAME,SEGMENT_SPACE_MANAGEMENT 
from DBA_TABLESPACES 
where TABLESPACE_NAME='SYSAUX';

TABLESPACE_NAME  SEGMEN
---------------- -----
SYSAUX           AUTO

select TABLE_NAME,TABLESPACE_NAME,SEGMENT_NAME 
from dba_lobs 
where OWNER='SYS' and TABLE_NAME='AUD$';

TABLE_NAME   TABLESPACE_NAME    SEGMENT_NAME
------------ ------------------ ---------------------------
AUD$         SYSTEM             SYS_LOB0000000384C00040$$
AUD$         SYSTEM             SYS_LOB0000000384C00041$$

select SEGMENT_NAME,segment_type,tablespace_name 
from dba_segments where segment_name in ( 
select INDEX_NAME from dba_indexes where OWNER='SYS' 
and TABLE_NAME='AUD$');

SEGMENT_NAME             SEGMENT_TYPE   TABLESPACE_NAME 
------------------------ ------------- -----------------
SYS_IL0000000384C00041$$ LOBINDEX      SYSTEM 
SYS_IL0000000384C00040$$ LOBINDEX      SYSTEM

Move AUD$ table:

SQL> EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE 
=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUX');

PL/SQL procedure successfully completed.

Move FGA_LOG$ table if needed:

SQL> EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE 
=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
   AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUX');

PL/SQL procedure successfully completed.
select TABLE_NAME,TABLESPACE_NAME,SEGMENT_NAME 
from dba_lobs 
where OWNER='SYS' and TABLE_NAME='AUD$';

TABLE_NAME   TABLESPACE_NAME    SEGMENT_NAME
------------ ------------------ ---------------------------
AUD$         SYSAUX             SYS_LOB0000000384C00040$$
AUD$         SYSAUX             SYS_LOB0000000384C00041$$

select SEGMENT_NAME,segment_type,tablespace_name 
from dba_segments where segment_name in ( 
select INDEX_NAME from dba_indexes where OWNER='SYS' 
and TABLE_NAME='AUD$');

SEGMENT_NAME            SEGMENT_TYPE    TABLESPACE_NAME 
------------------------ ------------- -----------------
SYS_IL0000000384C00041$$ LOBINDEX       SYSAUX 
SYS_IL0000000384C00040$$ LOBINDEX       SYSAUX 

select count(*) from dba_objects where status!='VALID';

COUNT(*)
----------
0
Posted in GI and RAC, Performance Tunning | Tagged , , , , , , , | Leave a comment

How to Create SQL Plan Baseline from Cursor Cache

Here is an example of how to create SQL plan baseline on a  12.1 Oracle database.

1)Get all the details of the sql:

SQL> select SQL_ID,HASH_VALUE,CHILD_NUMBER,PLAN_HASH_VALUE,
            EXECUTIONS,ELAPSED_TIME/EXECUTIONS/1000000 Sec_per_exec 
      from  v$sql 
      where sql_id='gjpdb42w841yt' 
      order by ELAPSED_TIME/EXECUTIONS/1000000;

SQL_ID        HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS SEC_PER_EXEC
------------- ---------- ------------ --------------- ---------- ------------
gjpdb42w841yt 3095529433 0            3153495478      26         .25 
gjpdb42w841yt 3095529433 3            3153495478      44        2.46 
gjpdb42w841yt 3095529433 1            3153495478       1        7.07 
gjpdb42w841yt 3095529433 2            1409003088      58     1831.29

2) For 12.1, there are below options for 12.1 database. choose one appropriate to your situation.

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   sql_text          IN  CLOB,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   sql_handle        IN  VARCHAR2,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   attribute_name   IN VARCHAR2,
   attribute_value  IN VARCHAR2,
   fixed            IN VARCHAR2 := 'NO',
   enabled          IN VARCHAR2 := 'YES')
  RETURN PLS_INTEGER;

3) Create a sql baseline for sql_id=’gjpdb42w841yt’ and PLAN_HASH_VALUE=3153495478  .

SQL>var cnt number
SQL> execute :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( -
     sql_id => 'gjpdb42w841yt', -
     plan_hash_value =>'3153495478');

4) To let newly created sql baseline be used immediately, purge the sql cursor from shared pool as per How To Flush an Object Out The Library Cache by Using DBMS_SHARED_POOL Package.

5) Check the sql baseline :

SQL> select SQL_HANDLE,substr(SQL_TEXT,1,30),ENABLED,ACCEPTED,FIXED,
     REPRODUCED,AUTOPURGE from DBA_SQL_PLAN_BASELINES;

SQL_HANDLE            SUBSTR(SQL_TE ENA   ACC  FIX  REP AUT
--------------------- ------------  ---  ---- ---- ---- ---
SQL_29078efcd1d51d34  select * from YES   YES  NO   YES YES

6) Check the execution plan for this baseline.

SET LONG 10000
SET LINES 200
SET PAGES 300
SELECT *
FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=> 
'SQL_PLAN_2k1wfzm8xa79n2943fd10'));

or

SET LONG 10000 
SET LINES 200 
SET PAGES 300 
SELECT * 
FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(sql_handle=>
'SQL_29078efcd1d51d34'));

 

Posted in Performance Tunning, SQL Scripts For DBA | Tagged , , , , , , , , , , | Leave a comment

SQL Script to Format Library Cache Lock/pin wait event p3 value

http://www.anbob.com/archives/2725.html

Thanks  author: anbob.com(weejar@gmail.com)

CREATE OR REPLACE PROCEDURE lbc_p3(P3 number)
is

-- purpose: format p3 value (maybe p3 value large than 100,000,000)
-- author: anbob.com(weejar@gmail.com)
-- date: 2016-5-1
-- note: 11.2 tested

v_hex varchar2(50);
v_hexoid varchar2(50);
v_oid number;
v_namespace number;
v_mode varchar2(50);
begin
select to_char(p3,'xxxxxxxxxxxxxxxxxxxxx') into v_hex from dual;
-- object id
select to_number(substr(v_hex,1,length(v_hex)-8),'xxxxxxxxxxxxxx') into v_oid from dual;
-- namespace
select to_number(substr(v_hex,-8,4),'xxxxxxxxxxxxxx') into v_namespace  from dual;
-- mode
select decode(to_number(substr(v_hex,-4),'xxxxxxxxxxxxxx'),3,'exclusive mode',2,'share mode',1,'null mode',0,'no lock/pin held','unknown') into v_mode  from dual;
dbms_output.put_line('---------------------------------------------');
dbms_output.put_line(lpad('Library cache P3 value: ',50,'.')||p3);
dbms_output.put_line(lpad('Library cache P3 value HEX: ',50,'.')||ltrim(v_hex));
dbms_output.put_line(lpad('Object id: ',50,'.')||v_oid);
dbms_output.put_line(lpad('Namespace: ',50,'.')||v_namespace );
dbms_output.put_line(lpad('RequestMode: ',50,'.')||v_mode);
dbms_output.put_line('Tip: you can query namespace using SQL command SELECT indx,kglstdsc FROM x$kglst  WHERE kglsttyp=''NAMESPACE'' and indx='||v_namespace );
end;
/


SQL> set serveroutput on
SQL> exec lbc_p3(1571747577004035);
---------------------------------------------
..........................Library cache P3 value: 1571747577004035
......................Library cache P3 value HEX: 5957f00010003
.......................................Object id: 365951
.......................................Namespace: 1
.....................................RequestMode: exclusive mode
Tip: you can query namespace using SQL command SELECT indx,kglstdsc FROM x$kglst  WHERE kglsttyp='NAMESPACE' and indx=1

PL/SQL procedure successfully completed.

SQL> exec lbc_p3(1571747577004034);
---------------------------------------------
..........................Library cache P3 value: 1571747577004034
......................Library cache P3 value HEX: 5957f00010002
.......................................Object id: 365951
.......................................Namespace: 1
.....................................RequestMode: share mode
Tip: you can query namespace using SQL command SELECT indx,kglstdsc FROM x$kglst  WHERE kglsttyp='NAMESPACE' and indx=1

PL/SQL procedure successfully completed.

 

Posted in SQL Scripts For DBA | Tagged , | Leave a comment

To Get Object Name and Type From File# and Block#

Given file id and block id, the object details can be queried by below SQL query:

 

SQL>select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME 
    from  dba_extents 
where FILE_ID=1 and 68619 BETWEEN block_id AND ( block_id + blocks);

OWNER    SEGMENT_NAME    SEGMENT_TYPE   TABLESPACE_NAME
-------- --------------- -------------- ----------------------------
SYS      C_OBJ#_INTCOL#  CLUSTER        SYSTEM
Posted in SQL Scripts For DBA | Tagged , , , , | Leave a comment

ORA-04021: timeout occurred while waiting to lock object

Application job failed with below trace file contents:

*** 2018-06-16 11:03:17.044
*** SESSION ID:(2437.25769) 2018-06-16 11:03:17.044
*** CLIENT ID:() 2018-06-16 11:03:17.044
*** SERVICE NAME:(BATCH) 2018-06-16 11:03:17.044
*** MODULE NAME:(DBMS_SCHEDULER) 2018-06-16 11:03:17.044
*** ACTION NAME:(IMMEDIATE16_06_2018_10_10_01) 2018-06-16 11:03:17.044

ORA-12012: error on auto execute of job "BATCH"."IMMEDIATE16_06_2018_10_10_01"
ORA-04021: timeout occurred while waiting to lock object
ORA-06512: at "BATCH.AP_DATA_PROCESS", line 652
ORA-06512: at line 1

Check the session details from ASH history:

set pagesize 100
set linesize 250
col INSTANCE_NUMBER format 99
col SESSION_ID format 99999
col BLOCKING_INST_ID format 99
col event format a20
col WAIT_CLASS format a15
col PROGRAM format a20
col MODULE format a16
col p3 format 99999999999999999999
select sql_id,to_char(SAMPLE_TIME,'YYYYMMDD-HH24:MI:SS'),
 INSTANCE_NUMBER,SESSION_ID,PROGRAM,MODULE,EVENT,CURRENT_OBJ#,
 WAIT_CLASS,BLOCKING_INST_ID,
 BLOCKING_SESSION, p3
from dba_hist_active_sess_history
where INSTANCE_NUMBER= 3 
and SESSION_ID = 2437 
and sample_time between to_date('16-06-18 11:00:00','DD-MM-YY HH24:MI:SS')
and to_date('16-06-18 11:04:00','DD-MM-YY HH24:MI:SS')
and event ='library cache lock'
order by sample_id;

SQL_ID TO_CHAR(SAMPLE_TI INSTANCE_NUMBER SESSION_ID PROGRAM MODULE EVENT CURRENT_OBJ# WAIT_CLASS BLOCKING_INST_ID BLOCKING_SESSION P3
------------- ----------------- --------------- ---------- -------------------- ---------------- -------------------- ------------ --------------- ---------------- ---------------- ---------------------
...
..
6cp7dp2xgxr6m 20180616-11:02:52 3 2437 oracle@hx614 (J001) DBMS_SCHEDULER library cache lock 27307391 Concurrency 2 5205 76355447552016387
6cp7dp2xgxr6m 20180616-11:03:02 3 2437 oracle@hx614 (J001) DBMS_SCHEDULER library cache lock 27307391 Concurrency 2 5205 76355447552016387
6cp7dp2xgxr6m 20180616-11:03:12 3 2437 oracle@hx614 (J001) DBMS_SCHEDULER library cache lock 27307391 Concurrency 2 5205 76355447552016387

20 rows selected.

The OBJ# matches the DDL command:

ALTER INDEX BATCH.CDR_IND REBUILD SUBPARTITION CDR_IND_SUB_213;
SQL>select OWNER,OBJECT_NAME,SUBOBJECT_NAME,
           OBJECT_TYPE,STATUS,LAST_DDL_TIME 
from dba_objects 
where OBJECT_ID=27307391;

OWNER   OBJECT_NAME  SUBOBJECT_NAME     OBJECT_TYPE         STATUS  LAST_DDL_
------- ------------ ------------------ ------------------- ------- ----------------
BATCH   CDR_IND       CDR_IND_SUB_213    INDEX SUBPARTITION  VALID   20180616-12:39:22

The P3 shows the waiting object details, we can see the session failed because of waiting for object CDR,  which is holding by blocking session 5205 on node 2.

SQL> select lpad('Library cache P3 value: ',50,'.')||76355447552016387 from dual;

..........................Library cache P3 value: 76355447552016387


SQL> select lpad('Library cache P3 value HEX: ',50,'.')||to_char(76355447552016387,'xxxxxxxxxxxxxxxxxxxxx') from dual;

.....................Library cache P3 value HEX: 10f44e000010003

SQL> select lpad('Object ID: ',50,'.')||to_number(substr('10f44e000010003',1,length('10f44e000010003')-8 ),'xxxxxxxxxxxxxx') from dual;

...........Object ID: 17777888
SQL> select lpad('Namespace: ',50,'.')||to_number(substr('10f44e000010003',-8,4),'xxxxxxxxxxxxxx') from dual;

.......................................Namespace: 1

SQL> select lpad('RequestMode: ',50,'.')||decode(to_number(substr('10f44e000010003',-4),'xxxxxxxxxxxxxx'),3,'exclusive mode',2,'share mode',1,'null mode',0,'no lock/pin held','unknown') from dual;

.....................................RequestMode: exclusive mode

SQL> SELECT indx,kglstdsc FROM x$kglst WHERE kglsttyp='NAMESPACE' and indx=1;

1 TABLE/PROCEDURE

SQL>select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE,NAMESPACE 
from dba_objects where object_id=17777888;

OWNER     OBJECT_NAME   SUBOBJECT_NAME      OBJECT_TYPE     NAMESPACE
---------------- -----------------------  ---------------------------------  ------------------------  -----------------
BATCH        CDR                                                               TABLE                   1

Check blocking session details on racnode2, and found it is running DBMS_STATS against waiting object CDR from racnode3 .

b6usrg82hwsa3 20180616-11:02:56 2 5205 oracle@racnode2 (J032) DBMS_SCHEDULER row cache lock 18 Concurrency 0 2999959 GLOBAL
b6usrg82hwsa3 20180616-11:03:06 2 5205 oracle@racnode2 (J032) DBMS_SCHEDULER row cache lock 18 Concurrency 0 0       GLOBAL
b6usrg82hwsa3 20180616-11:03:16 2 5205 oracle@racnode2 (J032) DBMS_SCHEDULER row cache lock 18 Concurrency 0 1953712 GLOBAL
4m477zx25xrgq 20180616-11:03:26 2 5205 oracle@racnode2 (J032) DBMS_SCHEDULER                27307236       321 0     NOT IN WAIT
4m477zx25xrgq 20180616-11:03:36 2 5205 oracle@racnode2 (J032) DBMS_SCHEDULER                27307242       1582 0    NOT IN WAIT
4m477zx25xrgq 20180616-11:03:46 2 5205 oracle@racnode2 (J032) DBMS_SCHEDULER                27307219       1537 0    NOT IN WAIT


SQL> select sql_text from v$sql where sql_id='b6usrg82hwsa3';

SQL_TEXT
--------------------------------------------------------------------
call dbms_stats.gather_database_stats_job_proc ( )

SQL>se long 10000
SQL> select SQL_FULLTEXT from v$sql where sql_id='4m477zx25xrgq';
SQL_FULLTEXT
--------------------------------------------------------------------------------
/* SQL Analyze(1) */ select /*+ full(t) no_parallel(t) no_parallel_index(t)
dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monito
....
from "BATCH"."CDR" t
 where TBL$OR$IDX$PART$NUM("BATCH"."CDR",0,3,0,"ROWID") = :objn /*
 NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL
,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV
,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,ACL,NIL,NIL*/

The blocking session ID 5205 on node 2 is running a weekend maintenance job –“Optimizer Statistics Gathering”. it is analysing partition table  CDR.

There are a lot of discussions about “library cache lock” from DBMS_STATS gather of stats for a subpartition.

19790972 – “library cache lock” waits due to DBMS_STATS gather of stats for a sub partition
20180204002507

Posted in AWR, dbms_scheduler, ORA- ERRORS | Tagged , , , , , | Leave a comment

ORA-27125: unable to create shared memory segment Linux-x86_64 Error: 28: No space left on device

When try to start up an Oracle database, the below errors occur:

ORA-27125: unable to create shared memory segment
Linux-x86_64 Error: 28: No space left on device
Additional information: 3773
Additional information: 3221225472

CAUSE

Kernel parameter kernel.shmall is not set properly.

RESOLUTION

Make sure kernel.shmall  is given the appropriate value.

1)  Calculate the value of shmall.

# getconf PAGE_SIZE
4096

Shmall=total size of the SGAs /PAGE_SIZE.
If the total SGA size is 480GB, then it would be 1024 * 1024 * 1024 * 480 / 4096 = 125829120

2) Edit /etc//etc/sysctl.conf

kernel.shmall = 125829120

3) Apply the change

# sysctl -p  

4) Check shmall value after change.

# sysctl -A | grep shmall

5) Start up database successfully.

Posted in Configuration, ORA- ERRORS | Tagged , , , , , | Leave a comment

How to Relink Oracle 12CR2 Grid Infrastrure ( GI ) Binary

1)Change ownership of some root owned files like extjobO and jssuO, etc, because the relink script will be run as “grid’ user to change the file permissions.

Otherwise, the relink will fail from the below commands in scripts:

...
..
.
chmod 600 /u01/app/12.2.0.1/grid/bin/extjoboO )
chmod 700 /u01/app/12.2.0.1/grid/bin/extjobo
chmod 600 /u01/app/12.2.0.1/grid/bin/extjobO )
...
..
.

2) As root user:

#umask
0022

#cd /u01/app/12.2.0.1/grid/crs/install
# ./rootcrs.sh -unlock

3) As grid user:

$ cd /u01/app/12.2.0.1/grid/bin
$ relink
writing relink log to: /u01/app/12.2.0.1/grid/install/relink.log

-- Check any errors ?
$ grep -i error /u01/app/12.2.0.1/grid/install/relink.log

4) As root user:

# cd /u01/app/12.2.0.1/grid/rdbms/install
# ./rootadd_rdbms.sh


# cd /u01/app/12.2.0.1/grid/crs/install
# ./rootcrs.sh -lock
Using configuration parameter file: /u01/app/12.2.0.1/grid/crs/install/crsconfig_params
The log of current session can be found at:
/u01/app/grid/crsdata/racnode1/crsconfig/crslock_racnode1_2018-05-25_11-58-39AM.log

5) Check cluster resources:

$/u01/app/12.2.0.1/grid/bin/crsctl stat res -t
Posted in GI and RAC | Tagged , , , , | Leave a comment