Easy to Learn Oracle Database and Solve Your Problem.

Tuesday, September 26, 2017

ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn]

Cause: This Errors May Occur after a switchover Physical Standby database, and it is due to Oracle Bug 8895202.

Solution: Apply the patch where this issue is fixed , Patch 8895202 or Patch Sets >= 11.2.0.2 or Patch Bundles >= 11.1.0.7. If Your database version is >=11.2.0.2 then just change the parameter _ktb_debug_flags to 8. There is no danger in keeping _ktb_debug_flags=8. It gets only activated by invalid scn in itl.

After change the parameter analyze the object which has been affected.

bash-4.3$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 26 14:31:11 2017

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, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> alter system set "_ktb_debug_flags"=8 scope=both sid='*';

System altered.

SQL> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
bash-4.3$
bash-4.3$

ORA-01555 caused by SQL statement below (SQL ID: 89w0nk4mvs89k, Query Duration=0 sec, SCN: 0x0b0e.183c53aa):

Dump continued from file: /u01/app/oracle/diag/rdbms/erpdcdb/erpdcdb1/trace/erpdcdb1_ora_7798850.trc
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], [], [], [], [], []

========= Dump for incident 5041023 (ORA 600 [ktbdchk1: bad dscn]) ========
----- Beginning of Customized Incident Dump(s) -----
[ktbdchk] -- ktbgcl4 -- bad dscn
dependent scn: 0x0b0e.6ada21f5 recent scn: 0x0b0e.183bdd5c current scn: 0x0b0e.183bdd5c
----- End of Customized Incident Dump(s) -----

ANALYZE TABLE TABLE_NAME VALIDATE STRUCTURE CASCADE;

Dump continued from file: /u01/app/oracle/diag/rdbms/erpdcdb/erpdcdb2/trace/erpdcdb2_ora_5767956.trc
ORA-00600: internal error code, arguments: [2663], [2830], [698760342], [2830], [1792623805], [], [], [], [], [], [], []

========= Dump for incident 2720876 (ORA 600 [2663]) ========

*** 2017-09-26 13:52:19.908
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=2pbup9vshvk0u) -----
ANALYZE TABLE TABLE_NAME VALIDATE STRUCTURE CASCADE;

Sunday, September 24, 2017

ORA-12519: TNS:no appropriate service handler found

ORA-12519: TNS: no appropriate service handler found

Cause: Number of session and process reached the MAX_UTILIZATION level when listener find the current number of connections has reached maximum load it may set the state of the service handler for an instance to "blocked" and begin refusing incoming client connections.

Solution: Check the value of process parameter and increase the value of the parameter.

[grid@DB ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-SEP-2017 13:32:38

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1158 refused:0 state:ready
LOCAL SERVER
Service "SPFTLDB" has 1 instance(s).
Instance "SPFTLDB", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:73880 refused:0 state:blocked
LOCAL SERVER
Service "SPFTLDBXDB" has 1 instance(s).
Instance "SPFTLDB", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: DB.spftl.com, pid: 18815>
(ADDRESS=(PROTOCOL=tcp)(HOST=db.spftl.com)(PORT=43078))
The command completed successfully

SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 3
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 150
SQL> ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;

System altered.

SQL>

Bounce the database.

Wednesday, July 26, 2017

Hanging ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Cause: Archive log are not applying into standby database but recovery process (MRP) are active, in this situation I decided to restart recovery process but whenever I am applying ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL, the command itself hanging situation.

Solution: Rather than bouncing the standby database just find and kill the MRP process and start the media recovery process.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL>

Log Details:

Archived Log entry 20997 added for thread 1 sequence 11504 ID 0xffffffffaec2031b dest 1:

Thu Jul 27 10:33:23 2017

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Thu Jul 27 10:45:35 2017

Managed Standby Recovery Canceled (erpdb)

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Thu Jul 27 10:46:23 2017

Recovery coordinator died, shutting down parallel recovery

Thu Jul 27 10:46:25 2017

RFS[516]: Selected log 13 for thread 1 sequence 11506 dbid -********** branch 928567359

Thu Jul 27 10:46:28 2017

Archived Log entry 20998 added for thread 1 sequence 11505 ID 0xffffffffaec2031b dest 1:

Thu Jul 27 10:46:41 2017

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

ORA-1153 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION...

Thu Jul 27 10:46:59 2017

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Thu Jul 27 10:47:24 2017

RFS[514]: Selected log 21 for thread 2 sequence 11340 dbid -********** branch 928567359

Thu Jul 27 10:47:27 2017

Archived Log entry 20999 added for thread 2 sequence 11339 ID 0xffffffffaec2031b dest 1:

Thu Jul 27 10:47:35 2017

Logmerger died, shutting down parallel recovery slaves

Recovery interrupted!

Thu Jul 27 10:47:37 2017

Managed Standby Recovery Canceled (erpdb)

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Thu Jul 27 10:48:08 2017

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

Attempt to start background Managed Standby Recovery process (erpdb)

Thu Jul 27 10:48:08 2017

MRP0 started with pid=38, OS id=9764900

MRP0: Background Managed Standby Recovery process started (erpdb)

started logmerger process

Thu Jul 27 10:48:14 2017

Managed Standby Recovery starting Real Time Apply

Parallel Media Recovery started with 32 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

Media Recovery Log +DG_FRA/erpdb/archivelog/2017_07_25/thread_2_seq_11299.1849.950272469

Media Recovery Log +DG_FRA/erpdb/archivelog/2017_07_25/thread_1_seq_11451.1854.950272385

Query

Search Blog