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