Datapump 12.2 New Featues – Part 1

MANDY SANDHU’S BLOG

Oracle 12.2 version has a lot of new features and this blog will highlight datapump enhancements in 12.2 release.

View original post 228 more words

Advertisements

Oracle Data Guard 12cR1 with Data Guard Broker

MANDY SANDHU’S BLOG

The purpose of this blog is to guide you through configuring Oracle Data Guard Fast-Start Failover (FSFO) using Data guard broker with physical standby database.

Oracle Data Guard

Oracle Data Guard is one of the software solutions provided by Oracle Corporation to maximize high availability of Oracle databases. Oracle Data Guard maintains one or many secondary databases as alternatives to the primary production database.

View original post 1,592 more words

Seamless Application Failover with Oracle Data Guard

MANDY SANDHU’S BLOG

In the event of a planned/unplanned outage, how efficiently can user sessions be directed to a secondary site/ Database with minimal disruption is called seamless application failover. This can be achieved when database failover is facilitated by oracle data guard.

View original post 1,115 more words

Running SQL Script for Multiple Databases SQL*PLUS | Mutiple Servers SQL*PLUS in Shell Script | BASH Script Looping SQL*PLUS commands

..:::: EasyOraDBA | Shadab Mohammad ::::..

For running a single SQL Command via sqlplus on multiple servers, we have to first do a few pre-requisites

1. Oracle client should be installed on the Unix/Linux Server

2. Create Local TNS entry in the tnsnames.ora file for the databases where you will run the commands

3. All databases should have one common user with a common password (this method is not the safest method
since the password will be in plain text in your Shell script)
Now let us proceed to first create a text file called dbnodes.txt which will have the TNSNAMES for the
database we will connect to..

boston
chicago
newyork

Above is the entries in the dbnodes.txt file, these are the databases where we will loop the sql commands

Create a sql script with the commands you have to run on all the databases, lets call the file script.sql

set echo on
set linesize 200

View original post 192 more words

ORA-00600: internal error code, arguments: [audins:Invalid_param_type_for_binding

Error in Alert Log:

Errors in file /u03/app/oracle/diag/rdbms/primeprd/primeprd1/trace/primeprd1_ora_303370.trc (incident=1070393):

ORA-00600: internal error code, arguments: [audins:Invalid_param_type_for_binding], [], [], [], [], [], [], [], [], [], [], []

 

Error in Broker :

DGMGRL> add database primedr as connect identifier is primedr maintained as physical;
Database “primedr” added

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primeprd – Primary database
primedr – Physical standby database
Error: ORA-16664: unable to receive the result from a database

DGM-17016: failed to retrieve status for database “primedr”
ORA-16664: unable to receive the result from a database

Broker logfile error:

11/22/2017 16:42:26
ENABLE CONFIGURATION
11/22/2017 16:42:55
Site primedr returned ORA-16664.

 

Description:

We had this error when we were adding a Standby database to the Dataguard Broker. After enabling the configuration always it used to give error ORA-16664: unable to receive the result from a database. After literally months of digging around and opening multiple SR’s we discovered it was related to AUDIT logs which are written in AUD$. It was clear after searching the trace logfiles that an application context package has some issues with the Audit in Oracle 12c.

Error Showing Up in TraceFile

DDE: Problem Key ‘ORA 600 [audins:Invalid_param_type_for_binding]’ was flood controlled (0x2) (incident: 1070379)
ORA-00600: internal error code, arguments: [audins:Invalid_param_type_for_binding], [], [], [], [], [], [], [], [], [], [], []
*********START PLSQL RUNTIME DUMP************
***Got internal error Exception caught in pl/sql run-time while running PLSQL***
***Got ORA-2002 while running PLSQL***
PACKAGE BODY TCTDBS.SV_DB_CONTEXT:
library unit=677604e58 line=225 opcode=86 static link=7fb08c2b4888 scope=1
FP=0x7fb08c2b4d10 PC=0x9e7cdec82 Page=0 AP=0x7fb08c2b4888 ST=0x7fb08c2b5158
DL0=0x7fb0872edd50 GF=0x7fb0872edf60 DL1=0x7fb0872ede48 DPF=0x7fb0872edf48
HS=0x9e7cd8ae8 AR=0x7fb0872e9fc8 DS=0x9e7ce0148
PB_PC=(nil) SV_PC=(nil)

Solution:

The solution is simply to turn off the auditing for the objects which is giving error. Since we are already running an auditing solution;  native auditing of Oracle was not required in this scenario.

— Dynamic SQL to generate and execute the no audit statements —

DECLARE
l_sql_stmt varchar2(1000);
BEGIN
FOR t IN (SELECT owner, table_name
FROM all_tables
WHERE owner = ‘TCTDBS’
and
iot_type IS NULL
or
iot_type != ‘IOT_OVERFLOW’
)
LOOP
l_sql_stmt := ‘NOAUDIT ALL ON ‘ || t.owner || ‘.’ || t.table_name;
EXECUTE IMMEDIATE l_sql_stmt;
END LOOP;
END;

Once this was done we added the Standby Database to the Dataguard Broker and Enabled the configuration without any issues.

 

 

ORA-00600: internal error code, arguments: [KGL-heap-size-exceeded]

Error :

ORA-00600: internal error code, arguments: [KGL-heap-size-exceeded],
Heap size 518675K exceeds notification threshold (51200K)
DDE: Problem Key ‘ORA 600 [KGL-heap-size-exceeded]’ was completely flood controlled (0x6)

We got recently a flood of message in the alert log as above and it was generating a lot of traces and choked our diagnostic_dest destination. We checked the trc files and were seeing like below message

Memory Notification: Library Cache Object loaded into SGA

After Googling around for a while i stumbled onto this blog entry with uncannily similar issues like what we were encountering

http://oraclequirks.blogspot.qa/2009/04/heap-size-nnnnnk-exceeds-notification.html

The suggested workaround as per Oracle Support note,is to increase the threshold level by executing the following statement (on both nodes if RAC)

alter system set “_kgl_large_heap_warning_threshold”=8388608 scope=spfile sid=’*’;

And restart the database giving the error

The errors stopped appearing in the alert log file and the trace file generation also stopped. Apparently setting the hidden parameter to value of 8 MB, so it will monitor only objects with size 8MB and larger

Divide and conquer the “true” mutex contention

Latch, mutex and beyond

Oracle 11.2.0.2 contains enhancements 9282521 and 9239863 named “Library cache: mutex X” for objects highly contended for. Part I and II. These enhancements introduce new interesting possibilities to tune some types of the mutex contention.

Contention for heavily accessed objects can now be divided between multiple copies of object in the library cache. According to notes 9282521.8 and 9239863.8 describing the patches, the enhancements should be used:
When there is true contention on a specific library cache object….
Let me investigate this deeper. I will use Oracle 11.2.0.2.2 for Solaris SPARC (64-bit) on 8Cores/32Threads Sun Fire T2000. I chose this platform in order to emphasize how the enhancements work.

View original post 1,665 more words