recreate spfile from pfile in asm location and conffigure srvctl

create pfile from spfile='+DATA_DG/SHODS/PARAMETERFILE/spfile.284.889287671';

Once pfile is created startup database with pfile

startup database pfile='xxxxxxxx';


once it starts up

go to asm instance

my case +ASM1

data_dg/shods/parameterfile/

rm spfile.284.889287671

sql> create spfile='+DATA_DG' from pfile;

shutdown


Go to ASM instance

data_dg/shods/parameterfile/

Note the new name of spfile (spfile.284.899043351)

Goto oracle_home/dbs

take a backup of existing pfile before you make any change

after that edit pfile

delete all contents and put spfile location in it

SPFILE='+DATA_DG/SHODS/PARAMETERFILE/spfile.284.899043351'

sqlplus > startup

show parameter spfile;

It should show new spfile location.

After that register spfile in srvctl

First check config

srvctl config database -d shods

Database unique name: SHODS

Database name: SHODS

Oracle home: /u01/app/oracle/product/11.2.0.4/dbhome_1

Oracle user: oracle

Spfile: +DATA_DG/SHODS/PARAMETERFILE/spfile.284.899043351

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: SHODS

Database instances: SHODS1,SHODS2

Disk Groups: DATA_DG,RECO_DG

Mount point paths:

Services:

Type: RAC

Database is administrator managed

Now point it to new spfile

srvctl modify db -d shods -p '+DATA_DG/SHODS/PARAMETERFILE/spfile.284.899043351'

Now you should be able to start db with srvctl

Hybrid columnar compression exadata

To Estimate compression ratio:

DECLARE

   l_blkcnt_cmp     BINARY_INTEGER;
   l_blkcnt_uncmp   BINARY_INTEGER;
   l_row_cmp         BINARY_INTEGER;
   l_row_uncmp      BINARY_INTEGER;
   l_cmp_ratio       NUMBER;
   l_comptype_str   VARCHAR2 (200);

BEGIN

   DBMS_COMPRESSION.get_compression_ratio (
      scratchtbsname   => 'HMA_DM_SALES_DATA',
      ownname           => 'HMA_DM_SALES',
      tabname            => 'SLM_TH_VEHICLE_MONTHLY',
      partname           => NULL,
      comptype           => DBMS_COMPRESSION.comp_for_query_low,
      blkcnt_cmp        => l_blkcnt_cmp,
      blkcnt_uncmp     => l_blkcnt_uncmp,
      row_cmp            => l_row_cmp,
      row_uncmp        => l_row_uncmp,
      cmp_ratio          => l_cmp_ratio,
      comptype_str      => l_comptype_str
   );

   DBMS_OUTPUT.put_line ('l_blkcnt_cmp=' || l_blkcnt_cmp);
   DBMS_OUTPUT.put_line ('l_blkcnt_uncmp=' || l_blkcnt_uncmp);
   DBMS_OUTPUT.put_line ('l_row_cmp=' || l_row_cmp);
   DBMS_OUTPUT.put_line ('l_row_uncmp=' || l_row_uncmp);
   DBMS_OUTPUT.put_line ('l_cmp_ratio=' || l_cmp_ratio);
   DBMS_OUTPUT.put_line ('l_comptype_str=' || l_comptype_str);

END;

**********************************************************************************
We can create compression using CTAS or Alter table move

After using Alter to compress tables indexes will become unusable. So rebuild them again

CREATE TABLE OI_HIGH TABLESPACE COMP COMPRESS FOR QUERY HIGH AS SELECT * FROM OE.OI;

ALTER TABLE OI_TABLE MOVE COMPRESS FOR ARCHIVE LOW;


To uncompress
ALTER TABLE EMP MOVE NOCOMPRESS;(UNTESTED)

We have 2 types of compression
Archive and DWH

DWH means Query high or Query low used in DWH.. High or low means compression rates
Archive also has high/low  and it is used on tables that are not queried much.. Gives maximum storage savings




Very important document regarding Compression
http://www.oracle.com/technetwork/server-storage/sun-unified-storage/documentation/problemsolver-hcc-52014-2202692.pdf


To see what tables are compressed
SELECT table_name, compression, compress_for
FROM all_tables where compression='ENABLED';

SELECT * FROM (
  SELECT
    OWNER, SEGMENT_NAME, round((BYTES/1024/1024/1024),2) SIZE_GB
  FROM
    DBA_SEGMENTS
WHERE
      SEGMENT_TYPE = 'TABLE'
  ORDER BY
    BYTES/1024/1024/1024  DESC ) WHERE ROWNUM <= 100 ;


select 'Alter index  '||owner||'.'||INDEX_NAME||' REBUILD;' from dba_indexes where status='UNUSABLE';
ALTER TABLE HMA_PROD.PERSON MOVE COMPRESS FOR QUERY HIGH;

online patching apply and rollback

Make sure the patch is online patchable. See read me document for the patch in MOS.

It is always good to install patches offline. If your instance cannot be down and you need to fix a bug urgently use online patch first and rollback online patch when you get a maintenance window and reapply using offline mode

first check pre reqs

[DDWH1]oracle@xxx2:/home/oracle/sv/patch/16444583> /u01/app/oracle/product/11.2.0.4/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Apply patch

oracle@xxx2:/home/oracle/sv/patch/16444583> /u01/app/oracle/product/11.2.0.4/dbhome_1/OPatch/opatch apply online -connectString DBNAME:sys:password

If its a rac after password put anotehr colon and add hostname.

Make sure you get instance name right case sensitive

Do a ps -ef |grep pmon and paste the exact name there. Else it says instance is down or not found.


For roll back:

oracle@xxx2:/home/oracle/sv/patch/16444583> /u01/app/oracle/product/11.2.0.4/dbhome_1/OPatch/opatch rollback -id 16444583 -connectString DBNAME:sys:password


To apply offline

Shutdown all services
goto patch folder and

opatch apply


check in lsinventory

if its a online patch it will mention after the patch number

all_tab_cols / dba_tab_cols and dba_dependencies

The following is to find tables or objects referenced in a procedure.

select
owner, name, type,referenced_owner,referenced_name,referenced_type
from
   dba_dependencies where type='PROCEDURE' and owner='HMA_TM_PROD_GEN2' and REFERENCED_OWNER not in ('SYS','PUBLIC') order by NAME;


Following is to get column details in all tables under a particular schema

select table_name, column_name, data_type,data_length, nullable from dba_tab_cols where owner='HMA_TM_PROD_GEN2' order by table_name;

deinstalling oem agent

Stop the agent

Remove targets from OEM

goto agent home directory

cd $AGENT_HOME/oui/bin

.runInstaller

click on deinstall products

First select plugins and remove
Next remove sbin home
Next remove agent

After that delete directories from agentbase directory


P.s: If you try selecting everything and remove then it wont work as it has dependencies. So remove in above order.


If you have any issues trying to remove target in OEM

use this

exec mgmt_admin.cleanup_agent('xxxxsubdb1:3872');

While deploying agent if you get any error.. check the below logs

/opt/app/oracle/agentHome/core/12.1.0.2.0/cfgtoollogs/agentDeploy/agentDeploy_2017-04-20_13-15-44-PM.log

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 

To change memory size in db when amm is enabled

to change sga, memory and everything..

Use

alter system set memory_target=628M;

datapump process check

select
   round(sofar/totalwork*100,2)  percent_completed, 
   v$session_longops.* 
from 
   v$session_longops 
where
   sofar <> totalwork 
order by
   target, 
   sid; 

Database Sample template

<?xml version = '1.0'?>
<DatabaseTemplate name="shicon_temp" description=" " version="11.2.0.0.0">
   <CommonAttributes>
      <option name="OMS" value="false"/>
      <option name="JSERVER" value="true"/>
      <option name="SPATIAL" value="true"/>
      <option name="IMEDIA" value="true"/>
      <option name="XDB_PROTOCOLS" value="true">
         <tablespace id="SYSAUX"/>
      </option>
      <option name="ORACLE_TEXT" value="true">
         <tablespace id="SYSAUX"/>
      </option>
      <option name="SAMPLE_SCHEMA" value="false"/>
      <option name="CWMLITE" value="true">
         <tablespace id="SYSAUX"/>
      </option>
      <option name="EM_REPOSITORY" value="true">
         <tablespace id="SYSAUX"/>
      </option>
      <option name="APEX" value="true"/>
      <option name="OWB" value="true"/>
      <option name="DV" value="false"/>
   </CommonAttributes>
   <Variables/>
   <CustomScripts Execute="false"/>
   <InitParamAttributes>
      <InitParams>
         <initParam name="db_name" value="shicon"/>
         <initParam name="db_domain" value=""/>
         <initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE={SID}XDB)"/>
         <initParam name="audit_file_dest" value="{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump"/>
         <initParam name="compatible" value="11.2.0.4.0"/>
         <initParam name="remote_login_passwordfile" value="EXCLUSIVE"/>
         <initParam name="processes" value="300"/>
         <initParam name="undo_tablespace" value="UNDOTBS1"/>
         <initParam name="control_files" value="(&quot;/oracle/origlogA/shicon/control01.ctl&quot;, &quot;/oracle/origlogB/shicon/control02.ctl&quot;, &quot;/oracle/mirrlogA/shicon/control03.ctl&quot;)"/>
         <initParam name="diagnostic_dest" value="{ORACLE_BASE}"/>
         <initParam name="audit_trail" value="db"/>
         <initParam name="memory_target" value="6192" unit="MB"/>
         <initParam name="sessions" value="335"/>
         <initParam name="db_block_size" value="8" unit="KB"/>
         <initParam name="open_cursors" value="300"/>
      </InitParams>
      <MiscParams>
         <databaseType>MULTIPURPOSE</databaseType>
         <maxUserConn>20</maxUserConn>
         <percentageMemTOSGA>1</percentageMemTOSGA>
         <customSGA>false</customSGA>
         <archiveLogMode>false</archiveLogMode>
         <initParamFileName>{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/pfile/init.ora</initParamFileName>
      </MiscParams>
      <SPfile useSPFile="true">{ORACLE_HOME}/dbs/spfile{SID}.ora</SPfile>
   </InitParamAttributes>
   <StorageAttributes>
      <DataFiles>
         <Location>{ORACLE_HOME}/assistants/dbca/templates/Seed_Database.dfb</Location>
         <SourceDBName>seeddata</SourceDBName>
         <Name id="1" Tablespace="SYSTEM" Contents="PERMANENT" Size="740" autoextend="true" blocksize="8192">/oracle/oradata2/shicon/system01.dbf</Name>
         <Name id="2" Tablespace="SYSAUX" Contents="PERMANENT" Size="470" autoextend="true" blocksize="8192">/oracle/oradata2/shicon/sysaux01.dbf</Name>
         <Name id="3" Tablespace="UNDOTBS1" Contents="UNDO" Size="25" autoextend="true" blocksize="8192">/oracle/undodata1/shicon/undotbs01.dbf</Name>
         <Name id="4" Tablespace="USERS" Contents="PERMANENT" Size="5" autoextend="true" blocksize="8192">/oracle/oradata2/shicon/users01.dbf</Name>
      </DataFiles>
      <TempFiles>
         <Name id="1" Tablespace="TEMP" Contents="TEMPORARY" Size="20">/oracle/tempdata1/shicon/temp01.dbf</Name>
      </TempFiles>
      <ControlfileAttributes id="Controlfile">
         <maxDatafiles>100</maxDatafiles>
         <maxLogfiles>16</maxLogfiles>
         <maxLogMembers>3</maxLogMembers>
         <maxLogHistory>1</maxLogHistory>
         <maxInstances>8</maxInstances>
         <image name="control01.ctl" filepath="/oracle/origlogA/shicon/"/>
         <image name="control02.ctl" filepath="/oracle/origlogB/shicon/"/>
         <image name="control03.ctl" filepath="/oracle/mirrlogA/shicon/"/>
      </ControlfileAttributes>
      <RedoLogGroupAttributes id="1">
         <reuse>false</reuse>
         <fileSize unit="KB">51200</fileSize>
         <Thread>1</Thread>
         <member ordinal="0" memberName="redo01a.rdo" filepath="/oracle/origlogA/shicon/"/>
         <member ordinal="2" memberName="redo01b.rdo" filepath="/oracle/mirrlogA/shicon/"/>
      </RedoLogGroupAttributes>
      <RedoLogGroupAttributes id="2">
         <reuse>false</reuse>
         <fileSize unit="KB">51200</fileSize>
         <Thread>1</Thread>
         <member ordinal="0" memberName="redo02a.rdo" filepath="/oracle/origlogB/shicon/"/>
         <member ordinal="1" memberName="redo02b.rdo" filepath="/oracle/mirrlogB/shicon/"/>
      </RedoLogGroupAttributes>
      <RedoLogGroupAttributes id="3">
         <reuse>false</reuse>
         <fileSize unit="KB">51200</fileSize>
         <Thread>1</Thread>
         <member ordinal="0" memberName="redo03a.rdo" filepath="/oracle/origlogA/shicon/"/>
         <member ordinal="1" memberName="redo03b.rdo" filepath="/oracle/mirrlogA/shicon/"/>
      </RedoLogGroupAttributes>
      <RedoLogGroupAttributes id="4">
         <reuse>false</reuse>
         <fileSize unit="KB">51200</fileSize>
         <Thread>1</Thread>
         <member ordinal="1" memberName="redo04a.rdo" filepath="/oracle/origlogB/shicon/"/>
         <member ordinal="2" memberName="redo04b.rdo" filepath="/oracle/mirrlogB/shicon/"/>
      </RedoLogGroupAttributes>
   </StorageAttributes>
</DatabaseTemplate>

Save its as somename.dbc

it should show up in dbca

use amm

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

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.
Thanks, Kirti


Dump file /u01/app/oracle/diag/rdbms/xxx/xxx1/incident/incdir_238166/xxx1_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: xxx1 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:(xxx) 2015-09-23 20:29:19.996 *** MODULE NAME:(sas@xxx(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/xxx/xxx1/trace/xxx1_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 -----
skdstdst <- ksedst1 <- ksedst <- dbkedDefDump <- ksedmp <- ssexhd <- sighandler <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- kkqojeanl <- 0000000009741CCE <- kkqojeanl <- kkqojeanlCB <- qksqbApplyToQbc <- kkqdrv <- opiSem <- opiprs <- kksParseChildCursor <- rpiswu2 <- kksLoadChild <- kxsGetRuntimeLock <- kksfbc <- kkspsc0 <- kksParseCursor <- opiosq0 <- kpooprx <- kpoal8 <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv <- sou2o <- opimai_real <- ssthrdmain <- main <- libc_start_main <- start












 Oracle Support - 12+ hours ago  [ODM Issue Clarification]




Following error in the alert log file :-
Wed Sep 23 20:29:19 2015 Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x7FFFDD95FFE8] [PC:0x9741CA4, kkqojeanl()+1080] [flags: 0x0, count: 1] Errors in file /u01/app/oracle/diag/rdbms/xxx/xxx1/trace/xxx1_ora_60184.trc (incident=238166): ORA-07445: exception encountered: core dump [kkqojeanl()+1080] [SIGSEGV] [ADDR:0x7FFFDD95FFE8] [PC:0x9741CA4] [Address not mapped to object] [] Incident details in: /u01/app/oracle/diag/rdbms/xxx/xxx1/incident/incdir_238166/xxx1_ora_60184_i238166.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Wed Sep 23 20:29:22 2015 Dumping diagnostic data in directory=[cdmp_20150923202922], requested by (instance=1, osid=60184), summary=[incident=238166]. Wed Sep 23 20:29:25 2015

Schema Size

select 
   sum(bytes)/1024/1024/1024 as size_in_gig, 
   segment_type
from 
   dba_segments
where 
   owner='SCOTT' 
group by 
   segment_type;

Scan listener down even if it is up

Some times oem shows scan listener status down even if it is up.

Problem is with configuration.

While creating a listener_scan target in oem it stores internally on which host the listener is active.

When a scan listener is failed over to another node and still up on oem it shows down as in oem config it is checking on a different node.

If that is the case

do the following

In my case listener_scan3 is showing down

bash-3.2$ ./srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node hmaistmsdb2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node hmaistmsdb1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node hmaistmsdb1

scan3 is running on node1 but in oem its pointing towards node  2

So we need to relocate the scan listener to node 2.

bash-3.2$ ./srvctl relocate scan_listener -i 3 -n hmaistmsdb2

3 refers to listener_scan3 in above

testing email solaris

mailx -v -s "test subject" john@example.com < /dev/null

Encrypted tablespace creation and moving tables to encrypted TBS

CREATE TABLESPACE HMA_ENCRYPT_DATA DATAFILE
  '+DATA_DG' SIZE 10G AUTOEXTEND OFF
LOGGING
ENCRYPTION USING 'AES256'
DEFAULT   STORAGE (ENCRYPT)
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

After that create table using following

create table test(id number) tablespace HMA_ENCRYPT_DATA;

or

We can move existing table using

alter table STG_VEHICLE_STATUS_HMMA_UPD move tablespace hma_encrypt_data;

After moving tables indexes become unsuable. So rebuild index as follows

alter index cool rebuild tablespace hma_encrypt_index;

kill oracle sql session at os level

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND' and SID=1444;



replace sid with your sid.

then login to server

kill -9 spid

rman point in time recovery

run
{
allocate channel ch1 type disk;
set until time "to_date('2015-08-28:09:57:14', 'yyyy-mm-dd:hh24:mi:ss')";
restore database;
recover database;
}

sql trace by session, sid and system

We can enable sql trace in various ways.. Here are a few

Credits to : https://dbaclass.com/article/tracing-sessions-in-oracle/

1. Enabling tracing for all session of a user.
For this we need to create a trigger.

CREATE OR REPLACE TRIGGER USER_TRACING_SESSION
AFTER LOGON ON DATABASE
BEGIN
IF USER = 'SIEBEL'THEN
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/


2. Enabling trace for a single session(using dbms_system)

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE)

---To disable

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

--- Get the tracefile name:

SELECT p.tracefile FROM   v$session s  JOIN v$process p ON s.paddr = p.addr WHERE  s.sid = 123;
TRACEFILE
------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9699.trc

-- Use tkprof to generate readable file

tkprof /u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9699.trc   trace_output.txt


3.  Enabling trace using oradebug.


--Get the spid from sid.

SELECT p.spid FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id and s.sid=1105;

SPID
-----------------
3248

--- Enable tracing for that spid

SQL> oradebug setospid 3248
Oracle pid: 92, Unix process pid: 3248, image: oracle@sec58-6
SQL> oradebug EVENT 10046 trace name context forever, level 12
Statement processed.

-- Find the trace file name

SQL> oradebug TRACEFILE_NAME

/oracle/app/oracle/diag/rdbms/b2crmd2/B2CRMD2/trace/B2CRMD2_ora_3248.trc

-- Disabling trace:



SQL> oradebug setospid 3248
Oracle pid: 92, Unix process pid: 3248, image: oracle@sec58-6
SQL> oradebug event 10046 trace name context off
Statement processed.


4. 10053 trace:
10053 trace is is known as optimizer trace. Below are steps generating 10053 trace for a sql statement.

Note: To generate 10053 trace, we need to hard parse the query, So flush the sql statement from shared pool .

--- set tracefile name

SQL>alter session set tracefile_identifier='TESTOBJ_TRC';

Session altered.

SQL>alter session set events '10053 trace name context forever ,level 1';

Session altered.

-- hard parse the statement

SQL>Select count(*) from TEST_OBJ;

COUNT(*)
----------
33091072

exit

-- trace file name:

/u01/app/oracle/admin/BBCRMST1/diag/rdbms/bbcrmst1/BBCRMST1/trace/BBCRMST1_ora_9046_TESTOBJ_TRC.trc

Alternatively you can generate the 10053 trace, without executing or without hardparsing the sql statement using DBMS_SQLDIAG

suppose sql_id = dmx08r6ayx800
output trace_file=TEST_OBJ3_TRC

begin
dbms_sqldiag.dump_trace(p_sql_id=>'dmx08r6ayx800',
                        p_child_number=>0,
                        p_component=>'Compiler',
                        p_file_id=>'TEST_OBJ3_TRC');
END;
/


-- Trace file

-bash-4.1$ ls -ltr BBCRMST1_ora_27439_TEST_OBJ3_TRC.trc
-rw-r-----   1 oracle   oinstall  394822 Jun 30 14:17 BBCRMST1_ora_27439_TEST_OBJ3_TRC.trc

Start session trace

To start a SQL trace for the current session, execute:

ALTER SESSION SET sql_trace = true;

You can also add an identifier to the trace file name for later identification:

ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = mysqltrace;

[edit]Stop session trace

To stop SQL tracing for the current session, execute:

ALTER SESSION SET sql_trace = false;

[edit]Tracing other user's sessions

DBA's can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to trace problematic database sessions. Steps:

• Get the SID and SERIAL# for the process you want to trace.

SQL> select sid, serial# from sys.v_$session where ...
       SID    SERIAL#
---------- ----------
         8      13607

• Enable tracing for your selected process:

SQL> ALTER SYSTEM SET timed_statistics = true;
SQL> execute dbms_system.set_sql_trace_in_session(8, 13607, true);

• Ask user to run just the necessary to demonstrate his problem.

• Disable tracing for your selected process:

SQL> execute dbms_system.set_sql_trace_in_session(8,13607, false);

• Look for trace file in USER_DUMP_DEST:

$ cd /app/oracle/admin/oradba/udump
$ ls -ltr
total 8
-rw-r-----    1 oracle   dba         2764 Mar 30 12:37 ora_9294.trc

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

Few other useful commands

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');

Tracing Individual SQL Statements

SQL trace can be initiated for an individual SQL statement by substituting the required SQL_ID into the following statement.

SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:sql_id]';

alter system set events '8103 trace name errorstack forever, level 3'; 

To turn it off 

alter system set events '8103 trace name errorstack off, level 3'; 

If you want to turn at session level( Helpful when you are able to reproduce error when executed from your session)

Please try to reproduce the error as we need a tracefile to investigate. If possible please run the procedures until we see it reproduce. 

oradebug setmypid 

alter session set max_dump_file_size=unlimited; 
alter session set db_file_multiblock_read_count=1; 
alter session set events 'immediate trace name trace_buffer_on level 1048576'; 
alter session set events '10200 trace name context forever, level 1'; 
alter session set events '8103 trace name errorstack level 3'; 
alter session set events '10236 trace name context forever, level 1'; 
alter session set tracefile_identifier='ORA8103'; 

run the query that produces the error ORA-8103 

alter session set events 'immediate trace name trace_buffer_off'; 
oradebug tracefile_name; 
exit 

When an error is reported please upload the tracefile identified above by oradebug tracefile_name 

Step 3:- Please upload your /var/adm/messages file