Tuesday, October 6, 2015

exadata issues

ORA-700 [Offload issue job timed out] [21] [11] [0x60 
or
ORA-700 cell srv hang

Follow the following steps


Step #1:
 Set operating system kernel parameter rcu_delay=1 on all storage servers and Oracle Linux database servers.

  a. Set rcu_delay for runtime:
  # echo 1 > /proc/sys/kernel/rcu_delay

  b. Verify the setting:
  # cat /proc/sys/kernel/rcu_delay
   1

 c. Set rcu_delay in /etc/sysctl.conf for proper setting upon reboot:
  Add ""kernel.rcu_delay=1"" to /etc/sysctl.conf"
           
Step #2:
 Edit $OSSCONF/cellinit.ora on each cell and add the following line:
  _cell_oflsrv_heartbeat_timeout_sec=90
           

Step #3:
 Execute the following command in cellcli (enter the command in one line in cellcli).
 CELLCLI> alter cell events = "immediatecellsrv.cellsrv_setparam('_cell_oflsrv_heartbeat_timeout_sec','90')"
           
Step#4:
 Drop all the quarantines:
   CELLCLI> drop quarantine all
           
Step #5:
 Identify the offload groups and restart them:
CELLCLI> list offload group
CELLCLI> ALTER OFFLOADGROUP <SYS_XXXXX> restart

******************************************************************************

But here are links, which should be helpful for you to get in touch with Platinum team: 


How to create a Change Management Ticket for Planned/Scheduled Outages on Oracle Advanced Support Platform ( Doc ID 1663130.1 ) 
How to create a Change Management Ticket for Database Monitoring Adds/Delete on Oracle Advanced Support Platform ( Doc ID 1663133.1 ) 


How to order/schedule/setup exadata patching: 

http://www.oracle.com/us/support/premier/engineered-systems-solutions/platinum-services/overview/index.html 


************************************************************************************************************


For
ORA-07445: exception encountered: core dump [kkqojeanl()+1080] [SIGSEGV] [ADDR:0x7FFFDD95FFE8] [PC:0x9741CA4] [Address not mapped to object] [] 


We get an alert from oem saying an access violation occured.


The issue is because of bad queries.


For example here is one of the query used by our developer




Dump file /u01/app/oracle/diag/rdbms/sdwh/SDWH1/incident/incdir_238166/SDWH1_ora_60184_i238166.trc 
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security, 
OLAP, Data Mining, Oracle Database Vault and Real Application Testing option 
ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1 
System name: Linux 
Node name: xxx.hke.local 
Release: 2.6.39-400.128.17.el5uek 
Version: #1 SMP Tue May 27 13:20:24 PDT 2014 
Machine: x86_64 
Instance name: SDWH1 
Redo thread mounted by this instance: 1 
Oracle process number: 71 
Unix process pid: 60184, image: oracle@xxx.hke.local 




*** 2015-09-23 20:29:19.996 
*** SESSION ID:(1477.55347) 2015-09-23 20:29:19.996 
*** CLIENT ID:() 2015-09-23 20:29:19.996 
*** SERVICE NAME:(SDWH) 2015-09-23 20:29:19.996 
*** MODULE NAME:(sas@hmaipcapapp01 (TNS V1-V3)) 2015-09-23 20:29:19.996 
*** ACTION NAME:() 2015-09-23 20:29:19.996 


Dump continued from file: /u01/app/oracle/diag/rdbms/sdwh/SDWH1/trace/SDWH1_ora_60184.trc 
ORA-07445: exception encountered: core dump [kkqojeanl()+1080] [SIGSEGV] [ADDR:0x7FFFDD95FFE8] [PC:0x9741CA4] [Address not mapped to object] [] 


========= Dump for incident 238166 (ORA 7445 [kkqojeanl()+1080]) ======== 
----- Beginning of Customized Incident Dump(s) ----- 
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x7FFFDD95FFE8] [PC:0x9741CA4, kkqojeanl()+1080] [flags: 0x0, count: 1] 
Registers: 
%rax: 0x0000000000000000 %rbx: 0x00007f6e37379a98 %rcx: 0x0000000000000000 
%rdx: 0x00007fffdf953b00 %rdi: 0x00007f6e37379a98 %rsi: 0x00007f6e2e3ac2c0 
%rsp: 0x00007fffdd95fff0 %rbp: 0x00007fffdd960050 %r8: 0x0000000000000800 
%r9: 0x00007fffdf953f68 %r10: 0x0000000000000002 %r11: 0x0000000000000168 
%r12: 0x00007f6e2e3ac380 %r13: 0x00007fffdf953b00 %r14: 0x00007f6e37379a98 
%r15: 0x00007f6e3737bf60 %rip: 0x0000000009741ca4 %efl: 0x0000000000010206 
kkqojeanl()+1069 (0x9741c99) test %r13,%r13 
kkqojeanl()+1072 (0x9741c9c) je 0x9741cae 
kkqojeanl()+1074 (0x9741c9e) mov %rbx,%rdi 
kkqojeanl()+1077 (0x9741ca1) mov %r13,%rdx 
> kkqojeanl()+1080 (0x9741ca4) call 0x974186c 
kkqojeanl()+1085 (0x9741ca9) jmp 0x974189b 
kkqojeanl()+1090 (0x9741cae) pxor %xmm0,%xmm0 
kkqojeanl()+1094 (0x9741cb2) movaps %xmm0,-0x60(%rbp) 
kkqojeanl()+1098 (0x9741cb6) mov 0x10(%r12),%rsi 


*** 2015-09-23 20:29:19.998 
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0) 
----- Current SQL Statement for this session (sql_id=cd6jawx2cj0y8) ----- 


select * from REPAIR_ORDER_COMMENT where extract(YEAR from EFF_TO_DATE)=9999 and ( REPAIR_ORDER_ID in ( '668', '715', '716', '1596', '1763', '5397', '5508', '5509', '5510', '6314', '7680', '7681', '11218', '13071', '13072', '13600', '14145', '14155', '15487', '15488', '15729', '16881', '18101', '18282', '18486', '18487', '18673', '18740', '19232', '19368', '20210', '20345', '20953', '22525', '22526', '23292', '23972', '23973', '23974', '27713', '30020', '31079', '32982', '32983', '32984', '32985', '33040', '33041', '33193', '33194', '33195', '35543', '37184', '37191', '37275', '38414', '38415', '38416', '41028', '42358', '44795', '46743', '48008', '48361', '48362', '48929', '49420', '49741', '49742', '50875', '51888', '51889', '51890', '52323', '52324', '52665', '53325', '53604', '56251', '56252', '56253', '58806', '58973', '59153', '59595', '59596', '59676', '59756', '59776', '60320', '60613', '60614', '60615', '60951', '61342', '61343', '61752', '61753', '61772', '62773', '63085', '63605', '64975', '64976', '64977', '65323', '65554', '68067', '68068', '70924', '71218', '72071', '72072', '73185', '73520', '74489', '74557', '74888', '75019', '75020', '75021', '75022', '77706', '77825', '77826', ............................... 



----- Call Stack Trace ----- 


It is extremely poor practice to create such SQL statements with huge inlist. 

You should use a temp table to hold the inlist elements (so they just do 
select from table where column in (select column from temp table). Or you 
can change the sql to use range predicates as necessary, ie instead of IN 
(1,2,3....100) us between 1 and 100. 

Refer Note ORA-07445 [kkqojeanl()] Internal Error for Query with Large Number of Elements in an IN List ( Doc ID 1577011.1 ) for details.

*********************************************************************************

ORA-00600 [qksvcReplaceVC0]

1. Setting "_replace_virtual_columns" to false. 

You can set this parameter at both session (where automatic SQL Tuning Advisor starts) 
and system level with the following commands- 

SQL> alter session set "_replace_virtual_columns"=false; 

SQL> alter system set "_replace_virtual_columns"=false 

2. Since it is only failing in the SQL Tuning Advisor auto task and has no effect on the database the error can be ignored. 
You can disable that auto task and just run it manually when required: 

--check auto job status 

SQL> select client_name,status from dba_autotask_task; 

SQL> select client_name,status from dba_autotask_client; 

SQL> select client_name, operation_name, status from dba_autotask_operation; 


--disable SQL Tuning Advisor job 

SQL> exec dbms_auto_task_admin.disable ('sql tuning advisor', null, null); 

-OR- 

SQL> exec dbms_auto_task_admin.disable (client_name => 'sql tuning advisor', operation => null, window_name => null); 

--enable SQL Tuning Advisor job 

SQL> exec dbms_auto_task_admin.enable ('sql tuning advisor', null, null); 

-OR- 


SQL> exec dbms_auto_task_admin.enable (client_name => 'sql tuning advisor', operation => null, window_name => null); 


***************************************************************************
SR 3-11229905001 : expdp performance slow
SR 3-11493919521 : PLATINUM: An access violation detected 

No comments:

Post a Comment

Featured Post

Apply Patch 22191577 latest GI PSU to RAC and DB homes using Opatch auto or manual steps

Patch 22191577: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.4.160119 (JAN2016) Unzip the patch 22191577 Unzip latest Opatch Version in or...