Problem Key 'ORA 1578' was completely flood controlled (0x2)

 Problem Key 'ORA 1578' was completely flood controlled (0x2)

============================

Options to identify the Corruption

============================

Using SQL

SELECT FILE#, 

       BLOCK#, 

       BLOCKS, 

       TO_CHAR(CORRUPTION_CHANGE#) CORRUPTION_CHANGE#, 

       CORRUPTION_TYPE 

  FROM V$DATABASE_BLOCK_CORRUPTION;

SQL> SELECT FILE#,

       BLOCK#,

       BLOCKS,

       TO_CHAR(CORRUPTION_CHANGE#) CORRUPTION_CHANGE#,

       CORRUPTION_TYPE

  FROM V$DATABASE_BLOCK_CORRUPTION;  2    3    4    5    6

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE#

---------- ---------- ---------- ----------------------------------------

CORRUPTIO

---------

       325     196462          1 33745505699576

CORRUPT

SQL>

SELECT e.owner, 

       e.segment_type, 

       e.segment_name, 

       e.partition_name, 

       c.file#,

       greatest(e.block_id, c.block#) corr_start_block#,

       least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#,

       least(e.block_id+e.blocks-1, c.block#+c.blocks-1),

       corruption_type description

  FROM DBA_EXTENTS e, 

       V$DATABASE_BLOCK_CORRUPTION c

 WHERE e.file_id = c.file#

   AND e.block_id <= c.block# + c.blocks - 1

   AND e.block_id + e.blocks - 1 >= c.block#

SQL> SELECT e.owner,

       e.segment_type,

       e.segment_name,

       e.partition_name,

       c.file#,

       greatest(e.block_id, c.block#) corr_start_block#,

       least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#,

       least(e.block_id+e.blocks-1, c.block#+c.blocks-1),

       corruption_type description

  FROM DBA_EXTENTS e,

       V$DATABASE_BLOCK_CORRUPTION c

 WHERE e.file_id = c.file#

   AND e.block_id <= c.block# + c.blocks - 1

   AND e.block_id + e.blocks - 1 >= c.block#  2    3    4    5    6    7    8    9   10   11   12   13   14  ;

OWNER                                                                                                                            SEGMENT_TYPE       SEGMENT_NAME

-------------------------------------------------------------------------------------------------------------------------------- ------------------ --------------------------------------------------------------------------------------------------------------------------------

PARTITION_NAME                                                                                                                        FILE# CORR_START_BLOCK# CORR_END_BLOCK# LEAST(E.BLOCK_ID+E.BLOCKS-1,C.BLOCK#+C.BLOCKS-1) DESCRIPTI

-------------------------------------------------------------------------------------------------------------------------------- ---------- ----------------- --------------- ------------------------------------------------ ---------

IBANKING                                                                                                                         INDEX              IDX_DT_ACT_STATEMENT

                                                                                                                                        325            196462          196462                                               196462 CORRUPT

SELECT s.owner, 

       s.segment_type, 

       s.segment_name, 

       s.partition_name, 

       c.file#,

       header_block corr_start_block#,

       header_block corr_end_block#,

       1 blocks_corrupted,

       corruption_type||' Segment Header' description

  FROM DBA_SEGMENTS S, 

       V$DATABASE_BLOCK_CORRUPTION C

 WHERE s.header_file = c.file#

   AND s.header_block between c.block# and c.block# + c.blocks - 1

SELECT null owner, 

       null segment_type, 

       null segment_name, 

       null partition_name, 

       c.file#,

       greatest(f.block_id, c.block#) corr_start_block#,

       least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#,

       least(f.block_id+f.blocks-1, c.block#+c.blocks-1)

 - greatest(f.block_id, c.block#) + 1 blocks_corrupted,

       'Free Block' description

  FROM DBA_FREE_SPACE F, 

       V$DATABASE_BLOCK_CORRUPTION C

 WHERE f.file_id = c.file#

   AND f.block_id <= c.block# + c.blocks - 1

   AND f.block_id + f.blocks - 1 >= c.block#

ORDER BY file#, corr_start_block#;

Solution:

Options to fix Corruption

Rebuild Index Online

SQL> ALTER INDEX IBANKING.IDX_DT_ACT_STATEMENT REBUILD ONLINE;

Index altered.

SQL>

ORA-00704: bootstrap process failure; ORA-03171: Recovery was terminated during query execution

[oracle@qpaystddb ~]$
[oracle@qpaystddb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 15 12:55:40 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4.0353E+10 bytes
Fixed Size 2261928 bytes
Variable Size 1.9730E+10 bytes
Database Buffers 2.0535E+10 bytes
Redo Buffers 85204992 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-03171: Recovery was terminated during query execution
Process ID: 20598
Session ID: 198 Serial number: 3


Solution
=================
startup mount
recover managed standby database disconnect
-- Let managed recovery apply a few logs
recover managed standby database cancel;
alter database open;

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

alter system set local_listener = '(address_list=(address=(protocol=tcp)(host=drp4db-vip.nblbd.com)(port=1521)))';

As soon as I set the value of local_listener, PMON will know the port number on which listener is running and it will dynamically register the instance with listener.

ORA-12514: TNS:listener does not currently know of service requested in connect

Problem
=========
$
$ sqlplus nt24db/xxxx@racdb

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 18 16:33:15 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:
$
$

Solution

=========

SQL> alter system set local_listener = '(address_list=(address=(protocol=tcp)(host=drp4db-vip.nblbd.com)(port=1521)))';

As soon as set the value of local_listener, PMON will know the port number on which listener is running and it will dynamically register the instance with listener.

Check Connection:

==============

$ sqlplus nt24db@racdb

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 18 17:01:42 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>

SQL>

SQL>

ORA-03137: TTC protocol internal error : [3147] [] [] [] [] [] [] []

ORA-03137: TTC protocol internal error : [3147] [] [] [] [] [] [] []

This error is only telling us that the client and server process communication was terminated due to some "event".

Client / server message exchanges use an Oracle specific protocol (TTC). ORA-3137 is reported if something unexpected is seen that does not conform to the expected protocol. The error can be raised by the client or server side of a connection.

Solution-1:

Disable bind peeking by setting:

SQL> alter system set "_optim_peek_user_binds"=false;

Restart the database and listener services.

This hidden parameter was introduced since 9i in order to help optimizer makes better decisions for producing better executions plans. This hidden parameter lets Optimizer peeks the values of binds at hard parsing phase using them for looking for the best exec plans based on current stats and values of binds.

When bind variables are used in a statement, it is assumed that cursor sharing is intended and that different invocations are supposed to use the same execution plan.

The parameter ( "_optim_peek_user_binds") controls this feature's behavior, default value is TRUE.

Solution-2:

If error raise continuously, then you can install the following patch:

Patch Number# 18841764

Note: Bug 18841764 Network related error like ORA-12592 or ORA-3137 or ORA-3106 may be signaled

SHUTDOWN: waiting for active calls to complete

Solution 

1. Shutdown database with ABORT option

SQL> shutdown abort;

2. Open database with RESTRICT option

SQL> startup restrict

3. Shutdown database with NORMAL option

SQL> shutdown normal;

Implementation 

ORA-03113: end-of-file on communication channel (Error: ora-03113 at time of database open)

[oracle@techtest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 27 11:11:47 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area 3.2002E+10 bytes

Fixed Size                  2269032 bytes

Variable Size            1.4026E+10 bytes

Database Buffers         1.7918E+10 bytes

Redo Buffers               55541760 bytes

Database mounted.

SQL>

SQL>

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 3065

Session ID: 66 Serial number: 3

Solution:

Step-1: First look at [alert.log] file the find the actual error

[oracle@techtest alert]$ tail -f log.xml

<msg time='2016-09-27T11:12:09.555+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module='sqlplus@techtest.nblbd.com (TNS V1-V3)'

 pid='3065'>

 <txt>ARCH: Error 19809 Creating archive log file to &apos;/fra/TECHDB/archivelog/2016_09_27/o1_mf_1_16404_%u_.arc&apos;

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.555+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''

 pid='3069'>

 <txt>ARC1: Error 19809 Creating archive log file to &apos;/fra/TECHDB/archivelog/2016_09_27/o1_mf_1_16405_%u_.arc&apos;

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.614+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''

 pid='3069'>

 <txt>ARCH: Archival stopped, error occurred. Will continue retrying

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.614+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''

 pid='3069'>

 <txt>ORACLE Instance techdb - Archival Error

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.620+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module='sqlplus@techtest.nblbd.com (TNS V1-V3)'

 pid='3065'>

 <txt>Errors in file /u01/app/oracle/diag/rdbms/techdb/techdb/trace/techdb_ora_3065.trc:

ORA-16038: log 3 sequence# 16404 cannot be archived

ora-19809:limit exceeded for recovery files

ORA-00312: online log 3 thread 1: &apos;/techtestdb/techdb/redo03.log&apos;

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.620+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.x.12' module=''

 pid='3069'>

 <txt>ORA-16038: log 2 sequence# 16405 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 2 thread 1: &apos;/techtestdb/techdb/redo02.log&apos;

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.688+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module='sqlplus@techtest.nblbd.com (TNS V1-V3)'

 pid='3065'>

 <txt>USER (ospid: 3065): terminating the instance due to error 16038

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.710+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''

 pid='3031'>

 <txt>System state dump requested by (instance=1, osid=3065), summary=[abnormal instance termination].

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.729+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''

 pid='3031'>

 <txt>System State dumped to trace file /u01/app/oracle/diag/rdbms/techdb/techdb/trace/techdb_diag_3031_20160927111209.trc

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.870+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''

 pid='3031'>

 <txt>Dumping diagnostic data in directory=[cdmp_20160927111209], requested by (instance=1, osid=3065), summary=[abnormal instance termination].

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.951+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module='sqlplus@techtest.nblbd.com (TNS V1-V3)'

 pid='3065'>

 <txt>Instance terminated by USER, pid = 3065

 </txt>

</msg>

 Step-2:  After look the alert log file, We have found two point on it and that are-

1. Error to create archive log

2. Limit exceeded for recovery files.

>>Step-3:  First try to delete the archive log to overcome error

[oracle@techtest ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Sep 27 11:15:23 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> delete noprompt archivelog all;

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of delete command at 09/27/2016 11:15:29

RMAN-06403: could not obtain a fully authorized session

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

It is not possible to delete archive log as database is not open and limit exceeded for the recovery file.

>>Step-4:  Increase the size of DB_RECOVERY_FILE_DEST_SIZE

4.1: current value

SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest_size           big integer 50000M

4.2: Increase size

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE =55g;

System altered.

>>Step-5:  Database Open

[oracle@techtest ~]$

[oracle@techtest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 27 11:18:30 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

MOUNTED

SQL>

SQL> startup

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL>

SQL> alter database open;

Database altered.

SQL> exit

ORA-16047: DGID mismatch between destination setting and target database

ORA-16047: DGID mismatch between destination setting and target database

Step-1: Error check in in v$archive_dest.

select dest_id,status,error from v$archive_dest;

DEST_ID  STATUS       ERROR

------  ------------- -----------------------------------------------------------------

1       VALID

2       VALID

3       DISABLED      ORA-16047: DGID mismatch between destination setting and target database

Step-2: Check db_name, db_unique_name and log_archive_config parameter on Primary and standby database.

On PRIMARY

SQL> show parameter db_name;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_name                              string      remitdb

SQL>

SQL> show parameter db_unique_name;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_unique_name                       string      remitdb

SQL>

SQL> show parameter log_archive_config;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_config                   string      DG_CONFIG=(remitdb,remitdbstd,

                                                 remitdbdr)

SQL>

On STANDBY

SQL> show parameter db_name;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_name                              string      remitdb

SQL>

SQL> show parameter db_unique_name;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_unique_name                       string      remitdb

SQL>

SQL> show parameter log_archive_config;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_config                   string      DG_CONFIG=(remitdb,remitdbstd,

                                                 remitdbdr)

SQL>

Problem Identification and Solution: In STANDBY DATABASE db_unique_name is incorrect. In STANDBY DATABASE db_unique_name will remitdbdr instead of remitdb.

ORA-00245: control file backup failed; target is likely on a local file system, RMAN-03009: failure of Control File and SPFILE Autobackup command, RMAN backup fail

At the time of RMAN backup, below error raise and backup fail. 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of Control File and SPFILE Autobackup command on c1 channel at 07/13/2016 11:04:09

ORA-00245: control file backup failed; target is likely on a local file system

CAUSE:

The reason for the error is clear ORA-00245 - in a RAC environment the location of the Snapshot Controlfile must be on a shared location

CURRENT:

RMAN> SHOW SNAPSHOT CONTROLFILE NAME;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name DCPDB are:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/app/oracle/product/11.2.0.4/dbhome_1/dbs/snapcf_dcpdb1.f'; # default

RMAN>

SOLUTION:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/DCPDB/snapcf_dcpdb1.f';

CURRENT:

RMAN> SHOW SNAPSHOT CONTROLFILE NAME;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name DCPDB are:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/DCPDB/snapcf_dcpdb1.f';

RMAN>

Generic Operational Error OR Operational error () detected in EM OR Process 0x0xc0000013d3dbf638 appears to be hung in Auto SQL Tuning task

Reason:

The Automatic SQL Tuning Task (SYS_AUTO_SQL_TUNING_TASK)  has been over-running, therefore it has auto killed to protect the system from harm caused by such over-running.This is a protective measure purely to avoid the task from over-running its time limit because of a single task and  protects the system from harm caused by such over-running.

Solution:

To disable this job and messages will not appear anymore. Eventually manually execute it when needed.   

BEGIN

   DBMS_AUTO_TASK_ADMIN.DISABLE(

   client_name => ‘sql tuning advisor’,

   operation => NULL,

   window_name => NULL);

 END;

 /

 BEGIN

   DBMS_AUTO_TASK_ADMIN.ENABLE(

   client_name => ‘sql tuning advisor’,

   operation => NULL,

   window_name => NULL);

 END;

 /

Implementation:

oracle@dcpdb1 [/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 26 14:39:25 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>

SQL>

SQL> SELECT client_name, status from dba_autotask_client;

CLIENT_NAME                                                      STATUS

---------------------------------------------------------------- --------

auto optimizer stats collection                                  ENABLED

auto space advisor                                               ENABLED

sql tuning advisor                                               ENABLED

SQL>

SQL>

SQL>

SQL>

SQL> BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

  2    3  client_name => 'sql tuning advisor',

  4  operation => NULL,

  5  window_name => NULL);

  6  END;

  7  /

PL/SQL procedure successfully completed.

SQL>

SQL>

SQL> SELECT client_name, status from dba_autotask_client;

CLIENT_NAME                                                      STATUS

---------------------------------------------------------------- --------

auto optimizer stats collection                                  ENABLED

auto space advisor                                               ENABLED

sql tuning advisor                                               DISABLED

SQL>

SQL>

SQL>