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.

 

 

Advertisements

Rebuild All Indexes on a Schema in Oracle

spool index_rebuild.sql

select ‘alter index ‘||owner||’.’||index_name ||’ rebuild online nologging;’
from dba_indexes
where owner=upper(‘SAMPLE’);
spool off

Move Table to Another Tablespace Online Oracle Database 12c

The new PL/SQL procedure REDEF_TABLE in Oracle 12c  lets you redefine a table online in a single step. It can do the below changes

  • Move table,partition or index to another tablespace
  • Compression type change on a table including for table,partition and index

More information on DBMS_REDFINITION can be accessed on Oracle Support : Master Note:Overview of Online Redefinition of Tables (DBMS_REDEFINITION) (Doc ID 1357825.1)

We will now go ahead and move the table HR.EMP to another tablespace called TBS01 online.

EXEC
BEGIN
DBMS_REDEFINITION.REDEF_TABLE(
uname => ‘HR’,
tname => ‘EMP’,
table_part_tablespace => ‘TBS01’
);
END;
/

uname –> Schema Name in which Table resides
tname –> Table Name
table_part_tablespace –> New Tablespace to which table will be moved
index_tablespace => If you need to move the indexes to another table it can be done using an additional arguement

Restore Table from RMAN Backup Oracle 12c

 

One of the amazing features of Oracle Database 12c is that now you can do a logical restore from a physical backup. Before 12c it was not possible to restore a table from a RMAN backup. This feature is amazing speciall when you have a large database and needs to restore a small table. One of the pre-requisites is you must have a Full Backup of the database and also archivelogs after the fully backup must be available. We can proceed to do the restore with below RMAN command

$ rman target /

RECOVER TABLE HR.EMP
UNTIL TIME ‘SYSDATE-0.22’
AUXILIARY DESTINATION ‘/backups/rmanbkp’
REMAP TABLE ‘HR’.’EMP’:’EMP_RECVR’

HR.EMP is the table to be recovered from the RMAN backup

SYSDATE-0.22 is the time till where the table will be rolled back; this is to specify the point in time for recovery: UNTIL TIME, UNTIL SCN, or UNTIL SEQUENCE can be used

Auxiliary Destination is destination where a instance is created for the recovery process

REMAP TABLE clause is used to specify to restore the table with another name

Using this method even individual table paritions can be restored.

EXPDP/IMPDP – ORA-04063 (view “SYS.KU$_RADM_FPTM_VIEW” Has Errors) on Full Export / Import

This is caused by a mismatch between the character set in which the table radm_fptm$ is created and in which the type ku$_radm_fptm$_t is created. This leads to the ORA-932 error when trying to recompile the view SYS.KU$_RADM_FPTM_VIEW, you have specified UTF8 for the national characaterset instead of the default AL16UTF16 national characaterset:

To Check Character set

col parameter for a35
col value for a40
select * from nls_database_parameters where parameter like ‘%SET’ order by 1;

 

Workaround is  to recompile the Recompile the View ku$_radm_fptm_t

alter type ku$_radm_fptm_t compile reuse settings;

Type altered.

SQL> alter view SYS.KU$_RADM_FPTM_VIEW compile;

View altered.

set lines 140
col status for a9
col object_type for a20;
col owner.object for a50
select con_id, status, object_id, object_type,
owner||’.’||object_name “OWNER.OBJECT”
3 from cdb_objects where object_name like ‘%KU$_RADM_FPTM_VIEW%’ order by 4,1;

 

CON_ID STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
———- ——— ———- ——————– ————————————————–
0 VALID 14188 VIEW SYS.KU$_RADM_FPTM_VIEW

 

 

 

Oracle 12c Transparent Data Encryption (TDE) on RAC Database with Physical Standby Database : Multiple Databases in Single Oracle Home

Oracle 12c TDE on RAC Database with Physical Standby RAC. The Oracle Database Home has Multiple Instances, due to which we have to use parameter $ORACLE_UNQNAME for ENCRYPTION_WALLET_LOCATION in sqlnet.ora file

In Oracle 12c ALTER SYSTEM and orapki has been deprecated and the correct method is to use ADMINISTER KEY MANAGEMENT commands. Many of the statements from the ALTER SYSTEM SQL statement correspond to the ADMINISTER KEY MANAGEMENT statement.

1. Create folder for wallets, since this is RAC cluster database ,it is better to have a shared filesystem. In our case it is ACFS
mkdir -p /ACFS/wallets/primeprd
mkdir -p /ACFS/wallets/onlnprd
chown -R oracle:oinstall /ACFS/wallets/primeprd
chown -R oracle:oinstall /ACFS/wallets/onlnprd
2. Create sqlnet.ora file with encryption wallet location, do on both nodes with oracle user
ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE) (METHOD_DATA =
(DIRECTORY=/ACFS/wallets/$ORACLE_UNQNAME/)))
3. Set DB Unique Name for the databases and verfiy the encryption wallet location, do on both nodes with oracle user

Also it is better to set the ORACLE_UNQNAME in the user profile

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d primeprd -T “ORACLE_UNQNAME=primeprd”
select * from v$encryption_wallet;

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d onlnprd -T “ORACLE_UNQNAME=onlnprd”
select * from v$encryption_wallet;

4. At Dr side, with Oracle user in DB home directory
mkdir -p /ACFS/wallets/primedr
mkdir -p /ACFS/wallets/onlndr
chown -R oracle:oinstall /ACFS/wallets/onlndr
chown -R oracle:oinstall /ACFS/wallets/primedr

Create sqlnet.ora file in DR side with encryption wallet location, do on both nodes with oracle user

ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE) (METHOD_DATA =
(DIRECTORY=/ACFS/wallets/$ORACLE_UNQNAME/)))

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d onlndr -T “ORACLE_UNQNAME=onlndr”

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d primedr -T “ORACLE_UNQNAME=primedr”

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED
0
SQL> administer key management create keystore ‘/ACFS/wallet/primeprd/’ identified by “Abc1234$#”;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
CLOSED UNKNOWN SINGLE UNDEFINED
0
SQL> administer key management set keystore open identified by Abc1234$#;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED
0
SQL> administer key management create key identified by Abc1234$# with backup;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED
0
SQL> select key_id from v$encryption_keys;

KEY_ID
——————————————————————————
AcVlvDzDNE8iv/rD38jpCtYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL> administer key management use key ‘AcVlvDzDNE8iv/rD38jpCtYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’ identified by Abc1234$# with backup;

keystore altered.

SQL> select key_id,activation_time from v$encryption_keys;

KEY_ID
——————————————————————————
ACTIVATION_TIME
—————————————————————————
AcVlvDzDNE8iv/rD38jpCtYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
23-NOV-16 09.53.46.485930 AM +00:00
SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN PASSWORD SINGLE NO
0

primeprd1::12c–>srvctl stop database -d primeprd
primeprd1::12c–>srvctl start database -d primeprd
primeprd1::12c–>sqld

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
CLOSED UNKNOWN SINGLE UNDEFINED
0
SQL> administer key management set keystore open identified by Abc1234$#;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN PASSWORD SINGLE NO
0
— Create Auto Login for the Keystore —

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE ‘/ACFS/wallet/primeprd/’ identified by Abc1234$#;

keystore altered.

primeprd1::12c–>srvctl stop database -d primeprd
primeprd1::12c–>srvctl start database -d primeprd
primeprd1::12c–>sqld

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 23 13:13:30 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN AUTOLOGIN SINGLE NO
0
Copy the Encryption key Files to Standby Site as “Oracle” user

primeprd2::12c–>cd /ACFS/wallet/primeprd/
primeprd2::12c–>scp -r * oracle@exadrdbadm01:/ACFS/wallets/primedr

The authenticity of host ‘exadrdbadm01 (10.5.238.11)’ can’t be established.
RSA key fingerprint is c8:c0:69:80:1c:c8:ea:e3:6c:46:6b:c1:05:86:aa:c9.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘exadrdbadm01,10.5.238.11’ (RSA) to the list of known hosts.
oracle@exadrdbadm01’s password:
cwallet.sso 100% 3893 3.8KB/s 00:00
ewallet_2016112309525312.p12 100% 2408 2.4KB/s 00:00
ewallet_2016112309534645.p12 100% 3656 3.6KB/s 00:00
ewallet.p12 100% 3848 3.8KB/s 00:00

— At DR Side on Both Nodes–
SQL> select * from v$encryption_wallet;
WRL_TYPE
——————–
WRL_PARAMETER
—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
—————————— ——————– ——— ——— ———-
FILE
/u03/app/oracle/admin/primedr/wallet
NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED
/home/oracle::12c–>cd /u03/app/oracle/product/12.1.0.2/teststandby/network/admin/
/u03/app/oracle/product/12.1.0.2/teststandby/network/admin::12c–>vi sqlnet.ora
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /ACFS/wallets/primedr/$ORACLE_UNQNAME/)))

/u03/app/oracle/product/12.1.0.2/teststandby/network/admin::12c–>sqld

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 23 13:27:22 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallets/primedr/
OPEN AUTOLOGIN SINGLE NO
0
SQL> exit

 

5. Create Encrypted Tablespace
CREATE TABLESPACE encrypt_ts
DATAFILE ‘+DATAC1’ SIZE 250M
ENCRYPTION USING ‘AES256’
DEFAULT STORAGE (ENCRYPT);

SYSAUX tablespace growing rapidly in Oracle, 12c Unified Audit Trail AUDSYS

The Unified Auditing in Oracle 12c can Grow the SYSAUX table to a large size. In our case it grew to about 100GB and We had to take action to make sure it did not grow any larger. First we query to identify what is residing inside the SYSAUX tablespace. In our case it was the AUDSYS schema which was taking the most space. All arrows pointed to Unified Auditing’s way

— Query to Check the SYSAUX Tablespace Occupants —

COLUMN “Item” FORMAT A25
COLUMN “Space Used (GB)” FORMAT 999.99
COLUMN “Schema” FORMAT A25
COLUMN “Move Procedure” FORMAT A40

SELECT occupant_name “Item”,
space_usage_kbytes/1048576 “Space Used (GB)”,
schema_name “Schema”,
move_procedure “Move Procedure”
FROM v$sysaux_occupants
ORDER BY 2
/
— Query to Check the SYSAUX Tablespace Largest Segments–

col owner for a6
col segment_name for a50
select * from
(select owner,segment_name||’~’||partition_name segment_name,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = ‘SYSAUX’ ORDER BY BLOCKS desc) where rownum < 11;
— Clean Up the Unified Audit Trail —

1. First set the Date, starting from where you need to have the Unified Auditing Records

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
last_archive_time => TO_TIMESTAMP(’15-SEP-2016 00:00:10.0′,’DD-MON-RRRR HH24:MI:SS.FF’),
rac_instance_number => 2);
END;
/
2. Since Unified Auditing caches the audit trail in memory to implement a ‘lazy write’ feature that helps performance, some of the records eligible for deletion may still linger in the cache, to also first flush this cache

BEGIN
DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
END;
/

3. Execute the Purge Procedure

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => TRUE);
END;
/

Useful Links:

https://docs.oracle.com/database/121/DBSEG/audit_admin.htm#DBSEG474

How To Purge The UNIFIED AUDIT TRAIL (Doc ID 1582627.1)