Preventing License Violation with CHOPT

Hi all,
So, in past weeks I’ve been publishing some material on how to prevent Licensing violations. Check below for my posts on last weeks:

Cool, but what about RAT, OLAP, Data Mining and Partitioning?

Well, we can use the chopt tool to disable the unlicensed options in the binaries. Check below:
Note: In a RAC with non shared Oracle homes, this has to be executed on every node.

For 12.1:

  1. Oracle Data Mining RDBMS Files (dm)
  2. Oracle OLAP (olap)
  3. Oracle Partitioning (partitioning)
  4. Oracle Real Application Testing (rat)

For 12.2:

  1. Oracle Advanced Analytics (oaa)
  2. Oracle OLAP (olap)
  3. Oracle Partitioning (partitioning)
  4. Oracle Real Application Testing (rat)

1. Shutdown all Oracle services running out of the Oracle RDBMS home (srvctl, SQL*Plus or lsnrctl as required)

srvctl stop home -oraclehome Oracle_home -statefile state_file

2 Disable the options as required

cd $ORACLE_HOME/bin
chopt disable dm # If Oracle version is 12.1
chopt disable oaa # If Oracle version is 12.2
chopt disable olap
chopt disable partitioning
chopt disable rat

3. Startup all Oracle services running out of the Oracle RDBMS home using either

srvctl, SQL*Plus or lsnrctl as required
srvctl start home -oraclehome Oracle_home -statefile state_file


EXTRA)
 It’s even easier for a 18c databases. The execution takes only a few seconds to complete.

[oracle@greporasrv ~]$ chopt disable oaa dm # If Oracle version is 18c

Writing to /opt/oracle/product/18c/dbhome_1/install/disable_oaa_2019-05-13_34-18-06PM.log...
/usr/bin/make -f /u01/oracle/product/18c/dbhome_1/rdbms/lib/ins_rdbms.mk dm_off ORACLE_HOME=/u01/oracle/product/18c/dbhome_1
/usr/bin/make -f /u01/oracle/product/18c/dbhome_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/oracle/product/18c/dbhome_1

How to Prevent Violate Active Dataguard License?

Hey folks,
As you all liked last week posts about preventing to violate Oracle Licensing, so let’s go another one, a quick one this time: Active Dataguard.

Matheus, is there a way to prevent using the Active Dataguard Option?

Well, there is. If you consider that this is basically a regular Dataguard that is opened in Read Only mode, here you go:

srvctl modify database -d GREPORADB_STBY -s MOUNT

By preventing the database open, we are preventing it to violate the Active Dataguard feature.

Quick reference on this:
– Which are Supported Methods to Prevent Active Data Guard Usage When License is Not Available? (Doc ID 2269239.1)

Hope it helps!

How to Disable AWR and Prevent Violating Diagnostic Tuning Pack License

Hey folks,
Seems you like it, so on same line that the post from last week: Let’s say you want to prevent yourself to violate Oracle licensing of your Enterprise Edition database… But as you know, Oracle is tricky sometimes. For example, all Diagnostic Pack features are enabled by default, you just need a JR DBA to go there and used for you to be stuck.

By the way, here is the first mistake: No, AWR is not part of Enterprise Edition, it also requires Diagnostic Pack!

Ok Matheus, so how to prevent it? Here it goes:

Disable AWR with Oracle provided script

Use the script dbmsnoawr.plb provided in MOS Doc ID 1909073.1 to disable AWR once the database has been created.

To install, run the package as SYS from SQL*Plus:
SQL> @dbmsnoawr.plb

To execute the package, use the command:
SQL> begin dbms_awr.disable_awr(); end;

Modify the CONTROL_MANAGEMENT_PACK_ACCESS init parameter to NONE and disable the AWR related advisors.

Disable AWR Related Options

ALTER SYSTEM SET control_management_pack_access=NONE;

SQL> select client_name, operation_name, status from dba_autotask_operation;

CLIENT_NAME                                                      OPERATION_NAME                                                   STATUS 
---------------------------------------------------------------- ---------------------------------------------------------------- --------
auto optimizer stats collection                                  auto optimizer stats job                                         ENABLED
auto space advisor                                               auto space advisor job                                           ENABLED
sql tuning advisor                                               automatic sql tuning task                                        ENABLED


BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);

  dbms_auto_task_admin.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);

END;
/

SQL> select client_name, operation_name, status from dba_autotask_operation;

CLIENT_NAME                                                      OPERATION_NAME                                                   STATUS 
---------------------------------------------------------------- ---------------------------------------------------------------- --------
auto optimizer stats collection                                  auto optimizer stats job                                         ENABLED
auto space advisor                                               auto space advisor job                                           DISABLED
sql tuning advisor                                               automatic sql tuning task                                        DISABLED

Some reference on it?
– How To Avoid the AWR Usage Without Having Diagnostic Pack License (Doc ID 2276199.1)
– Controlling Diagnostic and Tuning Pack Usage (Doc ID 436386.1)
– Disabling and Uninstalling AWR (Doc ID 1909073.1)

Hope it helps!

Licensing: Remove Options During DB Creation

Hi all,
So, let’s say you want to prevent yourself to violate Oracle licensing of your Enterprise Edition database… What a better moment to do it then when you are creating the database?

During database creation, there are several options that can be opted out. DBCA CLI has the option “-dbOptions” to disable options at will. For example, see below how to disable all the options.

dbca -silent -createDatabase -responseFile /home/oracle/dbca/dbca.rsp -dbOptions IMEDIA:false, CWMLITE:false, SAMPLE_SCHEMA:false, ORACLE_TEXT:false, APEX:false, OMS:false, DV:false, JSERVER:false, SPATIAL:false

If any of the options is required after the database has been created, let’s say you bought the license, you can simply look up on how to install them from Oracle Docs public documentation.

Hope it helps, cheers!

ORA-00600: internal error code, arguments: [13011], [7907] on SYS.DBMS_AQ_INV

Hi all,

So a couple weeks ago I was facing the following on a client environment. It was appearing every 10 mins on alert.log:

ORA-00600: internal error code, arguments: [13011], [7907], [12679954], [1], [13461738], [0], [], [], [], [], [], []
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_AQ_INV", line 1248

I could find several references to ORA-600 [13011], always related to some internal corruption. As this table is related to Advanced Queueing, decided to check on Scheduler Job table structures:

SQL> analyze table SYS.SCHEDULER$_EVENT_LOG validate structure cascade; 

Table analyzed. 

SQL> analyze table SYS.SCHEDULER$_JOB_RUN_DETAILS validate structure cascade; 
analyze table SYS.SCHEDULER$_JOB_RUN_DETAILS validate structure cascade 
* 
ERROR at line 1: 
ORA-01499: table/index cross reference failure - see trace file

SQL> analyze table SYS.SCHEDULER$_JOB_RUN_DETAILS validate structure;

Table analyzed.

Ahá!
Also on the generated trace file:

2019-03-01 22:26:37.736 
SESSION ID:(39.32751) 2019-03-01 22:26:37.736 
CLIENT ID) 2019-03-01 22:26:37.736 
SERVICE NAME:(SYS$USERS) 2019-03-01 22:26:37.736 
MODULE NAME:(sqlplus.exe) 2019-03-01 22:26:37.736 
CLIENT DRIVER:(SQL*PLUS) 2019-03-01 22:26:37.736 
ACTION NAME) 2019-03-01 22:26:37.736 
CONTAINER ID:(1) 2019-03-01 22:26:37.736 
Table/Index row count mismatch 
table 273184 : index 275017, 1832 
Index root = tsn: 1 rdba: 0x00c0128a

So ANALYZE on table SCHEDULER$_JOB_RUN_DETAILS fails with CASCADE but succeeds without CASCADE. This means that there is a problem with one of the index of this table. It has gone out of sync with the table.

SQL> select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where table_name='SCHEDULER$_JOB_RUN_DETAILS';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
alter index SYS.I_SCHEDULER_JOB_RUN_DETAILS rebuild;

SQL> alter index SYS.I_SCHEDULER_JOB_RUN_DETAILS rebuild online;

Index altered.

After this, error solved and no more recurrence of that ORA-600.

Also note I couldn’t find any document about this on MOS, so this is kind of exclusive by now. 🙂

Cheers!

Disabling PL/SQL Warnings

Hi all!
So, the DBA keep insisting that the Procedure need to compile without warnings? Easy!
This is actually a nice option if you are compiling a code in a client and don’t want to show that your code has warnings, which is kind of ok, once it’s almost impossible to code without warnings.

And this is not even new. Have a look on this documentation from 10.2.

Ok, so how to do it?

ALTER SESSION SET plsql_warnings = 'disable:all';

Have a look in the example below:

SQL> CREATE OR REPLACE PROCEDURE plw5001
  2  IS
  3     a   BOOLEAN;
  4     a   PLS_INTEGER;
  5  BEGIN
  6     a := 1;
  7     DBMS_OUTPUT.put_line ('Will not compile?');
  8  END plw5001;
  9  /
Warning: Procedure created with compilation errors.

SQL>
SQL> SHOW ERRORS
Errors for PROCEDURE PLW5001:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/4 PLW-05001: previous use of 'A' (at line 3) conflicts with this
use

6/4 PL/SQL: Statement ignored
6/4 PLS-00371: at most one declaration for 'A' is permitted
SQL>
SQL> ALTER SESSION SET plsql_warnings = 'disable:all';

Session altered.

SQL>
SQL> CREATE OR REPLACE PROCEDURE plw5001
2 IS
3 a BOOLEAN;
4 a PLS_INTEGER;
5 BEGIN
6 DBMS_OUTPUT.put_line ('Will not compile?');
7 END plw5001;
8 /

Procedure created.

 

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!

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

Oracle ACE Program

Hello all!
I’m glad share I got nominated as Oracle ACE Associate!
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 got nominated in 3 Expertises: Database Management & Performance, Cloud, MySQL.

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

So, for now on, you’ll see the following logo as a badge in our right menu:

NewScreenshot 2018-01-12 às 13.20.00

Thank you for all your support and for keeping me motivated to share and learn together all this time!

Cheers!

 

ORA-00932 to Export Statistics After 12c Upgrade – Undocumented

Hello All,
I have a client that use to Export and Import Dictionary Statistics using a stats table. However, after upgrading database to 12c, is started to raise error below to export data to a stats table (created in 11g) or even to update stats in this table.

SQL> exec dbms_stats.upgrade_stat_table('GREPORA','MYSTATSTABLE');
DECLARE
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BINARY got NUMBER
ORA-06512: at "SYS.DBMS_STATS", line 40183
ORA-06512: at line 28

SQL> EXEC DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS (stattab => 'MYSTATSTABLE',statid  => 'TEST_MATHEUS',statown => 'GREPORA' );
BEGIN DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS (stattab => 'MYSTATSTABLE',statid  => 'TEST_MATHEUS',statown => 'GREPORA' ); END;

*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BINARY got NUMBER
ORA-06512: at "SYS.DBMS_STATS", line 37085
ORA-06512: at line 1

I reviewed several references (all very interesting, by the way, recommend you to take a look):
– ORA-20002 on Importing Statistics using DBMS_STATS Procedures (Doc ID 740345.1)
– Datapump Export Fails With Ora-00932 (Doc ID 1300803.1)
– Error “ORA-00932 – Inconsistent Datatypes: Expected BINARY Got NUMBER” When Executing the Primavera Gather-Statistics Scripts on an Oracle 12c Database (Doc ID 2111114.1)
https://oracle-base.com/articles/12c/concurrent-statistics-collection-12cr1
– http://www.morganslibrary.org/reference/pkgs/dbms_stats.html
– How To Export and Import Dictionary Statistics (Doc ID 1450820.1)
– Fixed Objects Statistics (GATHER_FIXED_OBJECTS_STATS) Considerations (Doc ID 798257.1)

But no reference found to this specific issue, not even in MOS.

After some tests, I realized that maybe columns order in stats table can be changed in implementation between 11g and 12c. Bingo! The same columns and datatypes are in place, but in different order.

This is not documented in MOS neither on internet/independent blogs, but seems EXPORT% on DBMS_STATS presume stats table in it’s internal code without specified column orders.
As we know, a good development practice is to specify column names when performing any operation. Bad on you, Oracle!

Continue reading