Tag: database 12c

Database 12c to 21c, Journey of key feature sets

This presentation will look at 12c to 21c new features that should be investigated for use which include Multitenant, IN-Memory and Oracle 19c Automatic Indexing and many more. I will discuss the 12c to 21c Journey for these new features with examples and sample code. Attendees can expect brief introduction of all the new features with deep dive into Multitenant, IN-Memory and Automatic Indexing.

About the Speaker
Anuj Mohan is a seasoned database professional, with expertise in Engineered systems, Database performance and customer management. He is an Oracle ACE and a frequent speaker at various Oracle conferences like OOW, Collaborate, Great lakes, East coast and local user groups. He is also a regular speaker at Northern Kentucky University (NKU). He is the Co-Founder of Oracle19c SIG at Quest.

Database, Errors and Bugs

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!

Database, Security, SQL | PLSQL

12c New Feature: Unused Privileges

 , , , ,

Did you know 12c now has a mechanism to examine which privileges are actually being used by an user, module or for the database as a whole, as opposed to merely the privileges granted?
This is a great security improvement in order give users the privileges that they need precisely without granting too much.

Here’s how is works?

1. First you have to set up a capture process using the new package DBMS_PRIVILEGE_CAPTURE.

# Example:

BEGIN 
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE( 
        name         => 'all_priv_analysis_pol', 
        description  => 'database-wide policy to analyze all privileges', 
        type         => DBMS_PRIVILEGE_CAPTURE.G_DATABASE); 
END;
/

This starts the capture process database wide. Let it run for a bit.

2. Now you can either generate a report:

# Example

BEGIN 
DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT( 
        name         => 'all_priv_analysis_pol'); 
END;
/

Or examine the new views:

DBA_USED_PRIVS 
DBA_UNUSED_PRIVS 
DBA_USED_OBJPRIVS 
DBA_UNUSED_OBJPRIVS

3. To turn off you proceed with the following:

BEGIN 
DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE( 
        name         => 'all_priv_analysis_pol'); 
END;
/

So now you ensure that you use the best practice of least privilege for users.

Cheers!

ASM, Database, Linux, Shell Scripts

kernel.panic_on_oops: New Oracle 12c Installation Requirement

 , , , , ,

Hi all,
Do you know what mean the parameters on installing 12c?

This parameter controls the kernel’s behaviour when an oops or bug is encountered:

  • 0: try to continue operation
  • 1: panic immediately.  If the `panic’ sysctl is also non-zero then the machine will be rebooted.

OOPS is a deviation from correct behavior of the Linux kernel, one that produces a certain error log.
The better-known kernel panic condition results from many kinds of oops, but other instances of an oops event may allow continued operation with compromised reliability.

This is recommended in a system where we want to have node evicted in case of any hardware failure or any other issue.

To adjust as recommended by Oracle?
1. Put an entry in sysctl.conf for having it permanent:

kernel.panic_on_oops = 1

2. Refresh running command:

sysctl -p

KB: https://www.kernel.org/doc/Documentation/sysctl/kernel.txt

Matheus.