Exadata: Generate a Sundiag

Hello all!

Today’s post is a very simple one, once seems sometimes a simple and success case post is appreciated. So, how to generate a Sundiag Report for Oracle?

First, what is a Sundiag?
It consists in an Oracle hardware healthcheck list to be run on your environment. It doesn’t give us any report, but is usually requested by Oracle on SRs related to network, disk I/O or any other possible hardware/firmware related issue.

So, how to do it? Simple like this (the path is always the same):

[root@greporacel01 ~]# cd /opt/oracle.SupportTools/
[root@greporacel01 oracle.SupportTools]# /opt/oracle.SupportTools/sundiag.sh

Oracle Exadata Database Machine - Diagnostics Collection Tool

Gathering Linux information

Skipping ILOM collection. Use the ilom or snapshot options, or login to ILOM
over the network and run Snapshot separately if necessary.

/tmp/sundiag_greporacel01_1108FMM0FF_2017_01_01_02_17
Gathering Cell information

Generating diagnostics tarball and removing temp directory

====================================================================
Done. The report files are bzip2 compressed in /tmp/sundiag_greporacel01_1108FMM0FF_2017_01_01_02_17.tar.bz2
====================================================================
[root@greporacel01 oracle.SupportTools]#

Now you just need to pick this generated file and add to your SR. Simple right?

Hope it helps!

Script to Setup ADRCI Policies

Hi all!
So, today just sharing some useful scripts to configure, set and check on databases and users.

This is specially useful for environments with several databases under different users, considering a possible server consolidation strategy.

# Script to Check on Current Configuration:

[root@greporasrv ~]# cat adrci-check.sh
su - $1 -c 'export ORAENV_ASK=NO ; ORACLE_SID=$2 ; . oraenv ; for f in $(adrci exec="show homes" | grep -v "ADR Homes:" | grep -v "clients") ; do adrci exec="set home $f; show control;" ; done'

# Script to Set New Configuration
On this example: (SHORTP_POLICY = 168, LONGP_POLICY = 720).

[root@greporasrv ~]# cat adrci-set.sh
ORAUSER=$1
export SID=$2
su - $ORAUSER -c 'export ORAENV_ASK=NO ; ORACLE_SID='$SID' ; . oraenv ; for f in $(adrci exec="show homes" | grep -v "ADR Homes:" | grep -v "clients") ; do adrci exec="set home $f; set control \(SHORTP_POLICY = 168, LONGP_POLICY = 720\);" ; done'

# To run them informing OSUSER and SID:

./adrci-check.sh OWNER SID
./adrci-set.sh OWNER SID

# Master one: Script to set for all DBs/Users:

[root@greporasrv ~]# cat adrci-gen.sh
for h in $(grep -v "^#" /etc/oratab | awk 'BEGIN { FS=":";} {if (NF) print $2}' | sort -u)
do
ORAOWN=`ls -ld $h | cut -d " " -f 3`
# validate user?
# get a SID to use for this home
SID=`grep $h /etc/oratab | grep -v '^\*:' | cut -d ":" -f 1 | tail -1`

# Generating the code to check ADRCI settings on this OH
./adrci-check.sh $ORAOWN $SID

# Generating the code to change ADRCI settings on this OH
./adrci-set.sh $ORAOWN $SID
done

Nice, right?
Hope it helps. Cheers!

ORA 600 [kdt_bseg_srch_cbk PITL1] / ORA-00700 [PITL6] / ORA-00700 [PITL5]

Hi all,
So, right after a patching, a client environment started receiving entries on alert log like:

ORA-00700: soft internal error, arguments: [PITL6], [], [], [], [], [], [], [], [], [], [], []
ORA-00700: soft internal error, arguments: [kdt_bseg_srch_cbk PITL5], [11], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kdt_bseg_srch_cbk PITL1], [2], [], [], [], [], [], [], [], [], [], []

First a quick word regarding ORA-700: An ORA-700 is a so-called ‘soft’ assert. Soft asserts are triggered when the caller wants to make a note of the fact that something unexpected has happened, but would like to continue on because the failure is not fatal to the process or the instance. This was introduced in 12c and got some of ORA-600 messages (the informative ones), to leave ORA-600 for more critical issues.

Now to the errors: This is a clear match to Bug 28483184 (Bug 28483184 – ORA-600[PITL1] ON UPDATE TO COMPRESSED BLOCK WITH FIX FOR BUG 28364411 INSTALLED), which consists in a known defect in the Oracle code allows continued insertion of non-header block rows past the number of block ITLs in data blocks of OLTP-compressed tables.

More info: MOS Errors Noted in 12.2 and Above During DML on Compressed Tables: ORA-00600 [PITL1] / ORA-00600 [kdt_bseg_srch_cbk PITL1] / ORA-00700 [PITL6] / ORA-00700 [kdt_bseg_srch_cbk PITL5] (Doc ID 2420831.1)

To fix it: Patch on RDBMS for bug 28483184 and reorganize on involved tables. Seems to me that the patch only fixes the incorrect creation of non-header blocks, but doesn’t fix those that already have the problem in place.

My twists: I’d recommend you to increase a little bit the PCTFREE during the table reorganization, also based on Oracle’s recommendation. Recreating the tables with a larger PERCENT FREE will enable more space for ITLs. This is advantageous since the fix changes out a block that has the maximum amount of ITLs for one that has less ITLs.

If arranging a patching window is a big problem, note this patch is online installable. I’d recommend to do it offline using RAC Rolling however, as this seems a more consistent process in general.

So in summary, recommended actions are:
– Apply Patch 28483184 (RAC Rolling, Standby-First and online installable).
– Reorganize table, using methods mentioned by Suresh (ex: Create table as select (CTAS), Alter table move, Data Pump export / import, Online redefinition)

Hope that helps you!

Pluggable Database in Mount after Restart

Hi all,

So, I client reached me to fix the following: After restarting a database, all pluggable databases stay as mounted, instead of opening automatically.

Well, this was the quickest fix ever. After having all pluggable databases as they should be (open, in this case, but could have some in mount, depending on the configuration desired):

alter pluggable database all save state;

Easy, right?
We have some other good options like:

alter pluggable database pdb_name save state;
alter pluggable database all except pdb_name1, pdb_name2 save state;

I don’t really have to explain them, right?

Some good reference:

40.4.7 Preserving or Discarding the Open Mode of PDBs When the CDB Restarts
Preserve PDB Startup State (12.1.0.2 onward)

Cheers!

OEM: Quickly Ignore ORA Error on Agent Layer

Hey all,
So, I had a very specific situation to ignore an error from an agent. Turns that this seems even easier and quicker to ignore an specific error using an OEM Metric… How? Using agent parameter adrAlertLogErrorCodeExcludeRegex.

How to do it? Well, [AGENT_INST]/sysman/config/emd.properties, add a line with this parameters and the Regex to ignore the desired error or message.

To ignore all ORA-700, por example, it can be done by:

adrAlertLogErrorCodeExcludeRegex=.*700.*

Now to ignore, for example, ORA 700 [kskvmstatact: excessive swapping observed]

adrAlertLogErrorCodeExcludeRegex=.*kskvmstatact.*

After this, a restart on agent is required.

This is also well documented as per MOS EM 12c, 13c: How to Disable or Suppress OEM Alerts for Alert Log Error ORA-700 (Doc ID 2406779.1)

Hope it helps!

Auditing Logons with Triggers

Hello all!
So, I was attending a client who wanted to audit logon on database without Advanced Security pack. Complicated? Not at all.

Performatic? eehh, not really the best option, but Oracle is not really good for opening sessions anyway (this is why we have connection pools), so I consider this not bad…

Ok, how to do it?

A sequence for ID control:

create sequence sys.logon_capture_seq;
A Table for logon logging:
create table sys.logon_capture (
 id                     number,
 capture_time           date,
 authenticated_identity varchar2(30),
 authentication_method  varchar2(30),
 identification_type    varchar2(30),
 network_protocol       varchar2(30),
 session_user           varchar2(30),
 os_user                varchar2(30),
 host                   varchar2(30),
 ip_address             varchar2(30),
 program                varchar2(30),
 module                 varchar2(30),
 action                 varchar2(30),
 service_name           varchar2(30))
tablespace logon_capture;

* Here is an important point: Be always sure to have enough space on this tablespace, otherwise all new connections can be frozen.

Create the logon trigger:

create or replace trigger SYS.trg_capture_logons
after logon on database
when (SYS_CONTEXT ('USERENV', 'SESSION_USER') not in ('SYS'))
begin
  insert into sys.logon_capture (id,capture_time,authenticated_identity,authentication_method,identification_type,network_protocol,session_user,os_user,host,ip_address,program,module,action,service_name)
  select
    sys.logon_capture_seq.nextval,
    sysdate,
    substr(sys_context('userenv','authenticated_identity'),1,30),
    substr(sys_context('userenv','authentication_method'),1,30),
    substr(sys_context('userenv','identification_type'),1,30),
    substr(sys_context('userenv','network_protocol'),1,30),
    substr(sys_context('userenv','session_user'),1,30),
    substr(sys_context('userenv','os_user'),1,30),
    substr(sys_context('userenv','host'),1,30),
    substr(sys_context('userenv','ip_address'),1,30),
    substr(program,1,30),
    substr(sys_context('userenv','module'),1,30),
    substr(sys_context('userenv','action'),1,30),
    substr(sys_context('userenv','service_name'),1,30)
  from v$session
  where sid = sys_context('userenv','sid');
  commit;
exception
  when others then null;
end;
/

Know what is a good idea? a cleanup job, keeping only the last 6 month of “audit” data:

begin
  DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'SYS.PURGE_LOGON_CAPTURE',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'begin delete from sys.logon_capture where capture_time < add_months(sysdate, -6); commit; end;',    number_of_arguments  => 0,
   start_date           => trunc(sysdate+1) + 23/24,
   repeat_interval      => 'FREQ=DAILY;BYHOUR=23;BYMINUTE=40;BYSECOND=0',
   enabled              => false,
   auto_drop            => false,
   comments             => '');
end;
/
exec DBMS_SCHEDULER.ENABLE ( name => 'SYS.PURGE_LOGON_CAPTURE' );
select owner, enabled from dba_scheduler_jobs where job_name = 'PURGE_LOGON_CAPTURE'

Hope it helps you!

OUGN Spring Seminar 2019!

Hello all,

I’m pretty happy to share I got confirmed yesterday to participate on the OUGN Spring Seminar 2019!

This is going to take place in a boat (yes! that’s right!) on March 21th, 2020. Here are some pictures that may convince you to go… 🙂

 

 

How to participate?

Subscribe here!

More info about my (and other speakers) sessions?

https://guidebook.com/guide/153374/schedule/#date/03-21-2019

https://guidebook.com/guide/153374/schedule/#date/03-22-2019

See you there!
Cheers!

Changing initrans on a Partitioned Index

Hi all,

We were having high ITL waits and high number of deadlocks (due to the hanging transactions waiting on the ITL on the data block) on a table and index and part of the fix was to increase the INITRANS on the table and on the index.

INITRANS is a physical attribute that determines the initial number of concurrent transaction entries allocated within each data block for a given table/index/cluster. Every transaction that updates a block has to acquire an Interested Transaction List (ITL) slot, in order to store the transaction id, rollback information and ultimately lock the necessary row/index entry within the block.

For an existing object it needs to be rebuild as INITRANS is a physical attribute on the Oracle datablock. So if you change on a table and you want it to take effect for the current data you need to move the table or one partition for example.

If you are doing this type of change on a partitioned index you need to change the index default attribute as well rebuild the index partition or the entire index.

But one thing caught my eye while working on this as after setting the new INITRANS default attribute and rebuilt the index online it did not changed the INITRANS values.

See below the execution on a test system

Continue reading

ORA-01450 on Index Rebuild

Hello all,
So, I got this:

SQL> ALTER INDEX MATHEUS.INDEX_1 REBUILD online TABLESPACE NEW_TBS;
ALTER INDEX MATHEUS.INDEX_1 REBUILD online TABLESPACE NEW_TBS
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

So, is this a bug? Yes it is.

From MOS ALTER INDEX REBUILD ONLINE Fails with ORA-01450 (Doc ID 236329.1):
“This is caused by issue Bug:2525767. The online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. Their total length is greater than number reported in ORA-01450 error message. This is a feature of online rebuild.
Maximum key length is calculated with respect to the database block size. It means that current value of the initialization parameter db_block_size is not large enough so that the internal journal IOT can be created without errors.”

Wanna know more, there is a great article on how this works exactly, by Alex Gorbachev: ORA-01450 During Online Index Rebuild

The solution?
It simply cannot be done online as explained, so doing it offline is the solution:

SQL> ALTER INDEX MATHEUS.INDEX_1 REBUILD TABLESPACE NEW_TBS;

Index altered.

Hope it helps, cheers!

Oracle ACE Program [2]

Hi all!
So, I’m glad to let you know this week I got an ACE Upgrade from Oracle ACE Associate to Oracle ACE!
Here is the Link for my profile in the ACE Directory.

But what is an Oracle ACE?

The Oracle ACE Program is designed to recognize and reward members of the Oracle Technology and Applications communities for their contributions. These individuals are technically proficient and willingly share their knowledge and experiences through presentations, blog posts, articles, social media posts, community forum support and more.

This way, we have 3 levels of (active) Oracle ACEs:
ACE Associate: The entry level, where I am right now.
ACE: Proficient Community Advocate.
ACE Director: Expert Community Advocate.

I’m ACE in 3 Expertises: Database Management & Performance, Cloud, MySQL.

As additional info: Seems I’m still the youngest ACE in Brazil and also the first Brazilian ACE with Cloud Expertise.

So, I updated the logo as a badge in our right menu to the following:

NewScreenshot 2018-11-27 às 19.11.03.png