Ошибка oracle ccb 11003 11262

..
..
..

executing command: SET NEWNAME

Starting Duplicate Db at 18-FEB-20
current log archived at primary database

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area  178241142784 bytes

Fixed Size                    19249424 bytes
Variable Size             124017182448 bytes
Database Buffers           53687091200 bytes
Redo Buffers                 517619712 bytes
allocated channel: a1
channel a1: SID=1940 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''BRP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''BRT'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '+ARCH/BRT/cntrlbrt.dbf';
   restore clone primary controlfile to  '+DATA/BRT/cntrlbrt.dbf' from
 '+ARCH/BRT/cntrlbrt.dbf';
   restore clone primary controlfile to  '+RECO/BRT/cntrlbrt.dbf' from
 '+ARCH/BRT/cntrlbrt.dbf';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''BRP'' comment= ''Modified by RMAN duplicate'' scope=spfile
released channel: t1
released channel: t2
released channel: t3
released channel: t4
released channel: t5
released channel: t6
released channel: t7
released channel: t8
released channel: t9
released channel: t10
released channel: t11
released channel: t12
released channel: t13
released channel: t14
released channel: t15
released channel: t16
released channel: a1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/18/2020 09:17:41
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 02/18/2020 09:17:41
RMAN-11003: failure during parse/execution of SQL statement: alter system set  db_name =  'BRP' comment= 'Modified by RMAN duplicate' scope=spfile
ORA-32001: write to SPFILE requested but no SPFILE is in use

RMAN> **end-of-file**

RMAN> exit

-sh-4.2$ srvctl stop database -d BRT
-sh-4.2$ srvctl remove database -d BRT
Remove the database BRT? (y/[n]) y

..
..
..
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area  178241142784 bytes

Fixed Size                    19249424 bytes
Variable Size             124017182448 bytes
Database Buffers           53687091200 bytes
Redo Buffers                 517619712 bytes
allocated channel: a1
channel a1: SID=1826 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''BRP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''BRT'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '+ARCH/BRT/cntrlbrt.dbf';
   restore clone primary controlfile to  '+DATA/BRT/cntrlbrt.dbf' from
 '+ARCH/BRT/cntrlbrt.dbf';
   restore clone primary controlfile to  '+RECO/BRT/cntrlbrt.dbf' from
 '+ARCH/BRT/cntrlbrt.dbf';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''BRP'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''BRT'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area  178241142784 bytes

Fixed Size                    19249424 bytes
Variable Size             124017182448 bytes
Database Buffers           53687091200 bytes
Redo Buffers                 517619712 bytes
allocated channel: a1
channel a1: SID=1826 device type=DISK

Starting backup at 18-FEB-20
channel t1: starting datafile copy
copying current control file
output file name=/oracle/BRP/122/dbs/snapcf_BRP.f tag=TAG20200218T093756
channel t1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 18-FEB-20

Starting restore at 18-FEB-20

channel a1: copied control file copy
..
..
..

Once the duplicate is completed, you can add the service back to the CRS.

Happy Troubleshooting!!!

Неструктурированное перечисление некоторых ошибок

ORA-1555

Причины, анализ, методы разрешения, включая описание GV$UNDOSTAT и об анализе ошибки в RAC окружении: 389554.1 ORA-1555 Using Automatic Undo Management — How to troubleshoot 269814.1 ORA-01555 Using Automatic Undo Management — Causes and Solutions

Bug 10389818: SSKGXPRRGN, INVALID HEADER ON UPDATE MESSAGE TOSSING BAD MUPD MSG Oracle 10.2.0.4 RAC OEL 5.1 x86_64, в каталоге udump трейс файлы вида:

SSKGXPRRGN, invalid header on update message
 magic 0x47464544 t1 0x43424140 t2 0x0 index 0x4b4a
 SKGXPGPID 0x7fffacd309d8        Internet address 10.0.1.1       UDP port number 11630
 tossing bad MUPD msg
 bad update to
 SKGXPGPID 0x2aaaac83fc64        Internet address 10.0.1.2       UDP port number 42626
 bad update from
 SKGXPGPID 0x7fffacd30798        Internet address 10.0.1.1       UDP port number 11630
 header:START MEMORY DUMP, 0x7fffacd30768, for 48 bytes

в alert.log ошибок нет

Верхняя оценка необходимого размера undo при текущей нагрузке и сравнение установленного параметра undo_retention со статистикой использования undo сегментов — How To Size UNDO Tablespace For Automatic Undo Management [ID 262066.1]

Запрос V$UNDOSTAT

Ora-1555 Dbms_Refresh.Refresh or Dbms_Mview.Refresh On a Distributed Refresh (Doc ID 464524.1) — проявилась в 12.1, как и у Mikhail Velikikh

ORA-08104 / ORA-08106

ORA-08104: this index object 15634 is being online built or rebuilt -- object_id

ORA-08106: cannot create journal table SCOTT.SYS_JOURNAL_15634 -- object_id

 SQL> begin
 2    if dbms_repair.online_index_clean(15634) then null;
 3    end if;
 4  end;
 5  /

Bug 3805539: ORA-8106 AFTER FAILED ALTER INDEX REBUILD ONLINE, SMON SLOW IN CLEANING UP
http://www.dbaglobe.com/2010/10/how-to-reslove-ora-08104-this-index.html

FAL[server]: FAL archive blocked because of KCRR_LOCK_BUSY, will try later

• служебное сообщение о блокировки записи в controlfile, определяемое установкой недефолтного значения параметра, например: log_archive_trace = 71
• в primary alert.log может указывать на проблемы передачи логов на определённый log_archive_dest[ination], возникшие, например, из-за временных сетевых проблем. Soft workaround: после восстановления работоспособности standby рестартовать ARCH процессы на primary помощью kill -9:

Killing archive process on primary instance will not cause any harm to your running instance. It will autoamatically start within seconds. This new arch process initates FAL server and FAL client to fill the GAP

ORA-0311

документ MOS DIAGNOSING ORA-3113 ERRORS [ID 1020463.6]

Частный случай: Linux x86_64, Oracle 11.1.0.7, стабильная ошибка при выполнении конкретного запроса:

SQL> SELECT empno,
...
SELECT empno,
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Core Dump-ов и записей в alert.log нет, SQL трейс показывает получение от пользовательской сессии сигнала ORA-01013 : user requested cancel of current operation

ERROR #1:err=1013 tim=1332419144016887

SQL*Net Client trace:

nserror: nsres: id=0, op=68, ns=12535, ns2=12609; nt[0]=0, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
nsrdr: error exit
nsfull_pkt_rcv: nsctxrnk=0
nsfull_pkt_rcv: error exit
nioqrc:  wanted 1 got 0, type 0
nioqper:  error from nioqrc
nioqper:    ns main err code: 12535
nioqper:    ns (2)  err code: 12609

ORA-03113 TNS-12535 and TNS-12609 Errors Using TAF W Send/Recv Timeout Parameters [ID 782914.1] — причиной являются установленые в клиентском sqlnet.ora параметры:

SQLNET.SEND_TIMEOUT
SQLNET.RECV_TIMEOUT

проявление ошибки зависит также от версии Oracle | SQL*Net Client — попробовать обновить

ORA-12805: parallel query server died unexpectedly

может вызываться Bug 7111619 PQ slaves may get stuck or abort, при этом PX slave на соседней ноде ожидает IPC send completion sync длительное время (до 300 секунд), workaround:

SQL> alter system set parallel_force_local = true;

How to Diagnose ORA-12805 Parallel Query Server Died Unexpectedly Error [ID 1348002.1]

ORA-00932 для столбцов типа LONG

SQL> select * from user_views where text like '%SSS%';
select * from dba_views where text like '%SSS%'
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

SQL> set serveroutput on
SQL> BEGIN
2       FOR r IN (SELECT * FROM user_views) LOOP
3          IF lower(r.text) LIKE '%SSS%' THEN
4             DBMS_OUTPUT.PUT_LINE(
5                'view ' || r.view_name
6                );
7          END IF;
8       END LOOP;
9  END;
10  /
view MONITOR_VIEW1
view MONITOR_VIEW2

PL/SQL procedure successfully completed.

, или так (более полное описание — working with long columns):

SQL> select TO_LOB(high_value) from dba_tab_partitions where table_name = 'PART_TABLE'
  2  /
 
select TO_LOB(high_value) from dba_tab_partitions where table_name = 'PART_TABLE'
 
ORA-00932: inconsistent datatypes: expected - got LONG
SQL> WITH xml AS (
  2          SELECT DBMS_XMLGEN.GETXMLTYPE(
  3                    'SELECT partition_name, high_value FROM dba_tab_partitions where table_name = ''PART_TABLE'''
  4                    ) AS xml
  5          FROM   dual
  6          )
  7  SELECT xs.partition_name
  8  ,      xs.high_value
  9  FROM   xml x
 10  ,      XMLTABLE('/ROWSET/ROW'
 11                  PASSING x.xml
 12                  COLUMNS partition_name   VARCHAR2(30) PATH 'PARTITION_NAME',
 13                          high_value       VARCHAR2(100) PATH 'HIGH_VALUE') xs
 14  WHERE xs.high_value = 21
 15  /
 
PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
SYS_P943628                    21

ORA-01089: immediate shutdown in progress — no operations are permitted при запросах через db link

Bug 13724193 — ORA-1089 over database link [ID 13724193.8]: … для primary instance, кот.не был рестартован после преобразования из«open read only» standby

аналогично для Active Standby системы:

SQL> select version, open_mode from v$instance, v$database;

VERSION           OPEN_MODE
 ----------------- --------------------
 11.2.0.3.0        READ ONLY WITH APPLY

Workaround: primary instance | Active Standby restart

ORA-01436: CONNECT BY loop in user data

SQL> create table t1 as select 1 as c1, 1 as c2 from dual;

Table created.

SQL> select c2 from t1 CONNECT BY PRIOR c2 = c1 START WITH c1 = 1
  2  /
ERROR:
ORA-01436: CONNECT BY loop in user data

no rows selected

SQL> select c2, CONNECT_BY_ISCYCLE
  2    from t1
  3  CONNECT BY NOCYCLE PRIOR c2 = c1
  4   START WITH c1 = 1
  5  /

        C2 CONNECT_BY_ISCYCLE
---------- ------------------
         1                  1

ORA-01156

При попытке удалить standby redo log на physical standby:

11.1.0.7@ SQL> alter database drop standby logfile group 20;

alter database drop standby logfile group 20

ORA-01156: recovery in progress may need access to files

How to Drop/Rename Standby Redolog file in Oracle 11g

ORA-01157: cannot identify/lock data file 101

на любых запросах, использующих временные сегменты, при отсутствии временных файлов (101,102,…), например:

SQL> select file_name from dba_temp_files;
select file_name from dba_temp_files
                      *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 101 - see DBWR trace file
ORA-01110: data file 101: '+DATA'

FUTEX_WAIT_PRIVATE

наблюдались сессии, формально находятся состоянием On CPU / runqueue (v$session.state <> ‘WAITING’), процессы ОС:

# strace -p 28174
Process 28174 attached - interrupt to quit
futex(0x3e3c354594, FUTEX_WAIT_PRIVATE, 2, NULL^C <unfinished ...>
Process 28174 detached

на MOS несколько платформозависимых багов Linux x86_64 Oracle 11.1.0.7 типа Bug 6918493 Net DCD (sqlnet.expire_time>0) can cause OS level mutex hang, possible to get PMON failed to acquired latch

ORA-01031: недостаточно привилегий

If you are not the owner of the table, then you need the DROP ANY TABLE privilege in order to use the drop_table_partition or truncate_table_partition clause — до 12.1 включительно

11.2.0.3.@USER1 SQL> select * from user_tab_privs where table_name = 'T1';

GRANTEE  OWNER  TABLE_NAME  GRANTOR  PRIVILEGE GRANTABLE HIERARCHY
-------- ------ ----------- -------- --------- --------- ---------
USER1    USER2  T1          USER2    ALTER     NO        NO

11.2.0.3.@USER1 SQL> alter table USER2.T1 drop partition for (TO_DATE(' 2013-08-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') - 1);

alter table USER2.T1 drop partition for (TO_DATE(' 2013-08-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') - 1)

ORA-01031: insufficient privileges

11.2.0.3.@SYS SQL> grant drop any table to USER1; -- Drop or truncate tables or table partitions in any schema

Grant succeeded

11.2.0.3.@USER1 SQL> alter table USER2.T1 drop partition for (TO_DATE(' 2013-08-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') - 1);

Table altered

ORA-16000: database open for read-only access

11.2, для запросов с Temporary table transformation — Bug 13847666 — ORA-16000 in read only database — superseded (Doc ID 13847666.8), есть патч, исправлен в версии 11.2.0.4

Materialized View using a dblink from readonly standby database ->
Create a Distributed Materialized View Based on a Master Table on a Read-Only Database (Doc ID 276023.1):

create materialized view test1
   refresh complete
   as select s1.*
      from test@read-only-db s1 
WHERE ROWNUM > 0 -- to make materialized view complex. This enables not to consider fast refresh and no registration at remote site
/

ORA-12838 / ORA-12839

11.2.0.3.SCOTT@ SQL> create table t23 as select * from emp;

Table created.

SQL> alter session enable parallel dml;

Session altered.

SQL> delete /*+ PARALLEL */ from t23;

14 rows deleted.

SQL> insert into t23 select * from emp;
insert into t23 select * from emp
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL> rollback;

Rollback complete.

SQL> delete /*+ NO_PARALLEL */ from t23; -- после изменений в непараллельном режиме

14 rows deleted.

SQL> insert into t23 select * from emp;

14 rows created.                         -- ошибки не возникает

SQL> rollback;

Rollback complete.

SQL> delete /*+ NO_PARALLEL */ from t23;

14 rows deleted.

SQL> insert /*+ APPEND PARALLEL */ into t23 select * from emp;
insert /*+ APPEND PARALLEL */ into t23 select * from emp
                                  *
ERROR at line 1:                                                  -- , однако, невозможно выполнить PDML
ORA-12839: cannot modify an object in parallel after modifying it


SQL> insert /*+ APPEND NO_PARALLEL */ into t23 select * from emp;

14 rows created.                                                  --, но допускается непараллельный direct path insert

ORA-600 / ORA-7445

ORA-07445 [kghbshrt]
PMON failed to acquire latch, see PMON dump
ORA-07445 [opiaba]
ORA-00600 [kgh_heap_sizes:ds]
ORA-00600 [17147]
Instance terminated by PMON

11.1.0.7 Linux x86_64, причина — запрос с большим кол-вом связанных переменных:

----- Current SQL Statement for this session (sql_id=fbd4nkwxragfd) -----
DELETE /*+ PARALLEL */ FROM "ST"."CORE_STAT" WHERE ("FIELDDATE" = TO_DATE(:p1, 'YYYY-MM-DD HH24:MI:SS')) OR
...
OR ("FIELDDATE" = TO_DATE(:p240776, 'YYYY-MM-DD HH24:MI:SS')) OR ("FIELDDATE" = TO_DATE(:p240777, 'YYYY-MM-DD HH24:MI:SS')) OR ("FIELDDATE" = TO_DATE(:p240778, 'YYYY-MM-DD HH24:MI:SS'))

Instance crashed after ORA-7445 [opiaba] and ORA-600 [17147] [ID 1466343.1]

ORA-600 [12869]

$ sql

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Feb 26 11:56:53 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from local_entity_attr
  2  /
select * from local_entity_attr
                    *
ERROR at line 1:
ORA-00600: internal error code, arguments: [12869], [308614], [567942], [], [], [], [], [], [], [], [], []

SQL> ANALYZE TABLE local_entity_attr partition(EA_P003) VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE local_entity_attr partition(EA_P003) VALIDATE STRUCTURE CASCADE
                    *
ERROR at line 1:
ORA-00600: internal error code, arguments: [12869], [308614], [567942], [], [], [], [], [], [], [], [], []

Bug 11891554 : ORA-600 [12869], [220538], [223953] ON READ-ONLY PHYSICAL STANDBY DATABASE — всё как описано, 11.1.0.7, Linux x86-64, READ-ONLY STANDBY, лечится рестартом инстанса, в 11.2 не встречалось, предположительно, исправлено

ORA-00600 [krr_read_lwn_part]

MOS ID 1311095.1 — попытка накатить corrupted log file на стендбай

ORA-00600 [17016]

Наблюдалась после прерывания ALTER TABLE DROP UNUSED COLUMNS CHECKPOINT 1000 на партиционированной таблице в форме:

11.2.0.4@ SQL> alter table my_table DROP COLUMNS CONTINUE CHECKPOINT 1000;
 
alter table my_table DROP COLUMNS CONTINUE CHECKPOINT 1000
 
ORA-00600: internal error code, arguments: [17016], [0x68F54BD70], [], [], [], [], [], [], [], [], [], []
 
SQL> drop table my_table;
 
drop table my_table
 
ORA-00604: error occurred at recursive SQL level 1
ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE

Bug 13345070 : ALTER TABLE DROP COLUMN FAILS WITH ORA-600 [17016], под руководством поддержки лечится исправлением tab$.flags, для 11.2 есть патчи, исправлен в 12.1.0.2

ORA-00600 [25027]

Стабильно воспроизводилась на версии 11.2.0.2 при попытке вставки в конкретную партицию таблицы без LOB в виде:

ORA-00600: internal error code, arguments: [25027], [3987], [0], [], [], [], [], [], [], [], [], []

— где согласно ORA-600 [25027] (Doc ID 284433.1) [3987] — Tablespace Number в нашем случае связанный с номером проблемной партиции;
при этом в alert.log накануне появления ошибок были отмечены проблемы ASM:

ERROR: direct connection failure with ASM
NOTE: Deferred communication with ASM instance
Errors in file /opt/oracle/admin/diag/rdbms/orcl/orcl2/trace/orcl2_m002_12275.trc:
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes (115) exceeded
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes (115) exceeded
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/datafile/splog_2014_11_10.34660.863141129
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes (115) exceeded
ORA-01122: database file 120 failed verification check
ORA-01110: data file 120: '+DATA/orcl/datafile/splog_2014_11_10.34660.863141129'
ORA-01565: error in identifying file '+DATA/orcl/datafile/splog_2014_11_10.34660.863141129'
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/datafile/splog_2014_11_10.34660.863141129
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes (115) exceeded
NOTE: deferred map free for map id 2139183

Проблема была решена пересозданием (+перезаливкой данных) партиции в табличном пространстве № [3987]

ORA-08103: object no longer exists

наблюдалось в READ ONLY standby версии 12.1.0.2, подобно Bug 8740993 ORA-1410 / ORA-8103 on ADG STANDBY during table scan after DROP/TRUNCATE/SHRINK in PRIMARY, пофикшеному в 11.2.0.2 Patch Set, с подобным же лечением методом flush shared_pool

ORA-10237: simulate ^C (for testing purposes)

How To Cancel A Query Running In Another Session

ORA-04021: timeout occurred while waiting to lock object

ORA-04021 is a timeout error that will be thrown if an objected cannot be locked after a certain period. The hiddner Parameter _kgl_time_to_wait_for_locks controls this period and it is set to 15 minutes by default

SQL> @param_ _kgl_time_to_wait_for_locks
 
NAME                         VALUE IS_DEF   IS_MOD     IS_ADJ   DSC
---------------------------- ----- -------- ---------- -------- -------------------------------------------------
_kgl_time_to_wait_for_locks  15    TRUE     FALSE      FALSE    time to wait for locks and pins before timing out

наблюдалось в версии 12.1.0.2:

On demand
MV SCO.MY_TROUBLE_MVIEW was not refreshed successfully.
Number of MV refresh failures: 1.
Encountered error ORA-12008.
kkzifr3g: Encountered error ORA-12008.
Thu Oct 19 14:22:04 2017
Errors in file /opt/oracle/base/diag/rdbms/orcl/orcl/trace/orcl_j018_28936.trc:
ORA-12012: ошибка при автоисполнении задания 82266
ORA-12008: ошибка в пути обновления материализованного представления
ORA-00913: слишком много значений
ORA-00904: "F19"."SERVICES": недопустимый идентификатор
ORA-06512: на  "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: на  "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: на  "SYS.DBMS_IREFRESH", line 687
ORA-06512: на  "SYS.DBMS_REFRESH", line 195
ORA-06512: на  line 1
Thu Oct 19 14:22:34 2017

12.1.0.2@ SQL> drop materialized view "SCO"."MY_TROUBLE_MVIEW" preserve table;
drop materialized view "SCO"."MY_TROUBLE_MVIEW" preserve table
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object

SQL> alter materialized view SCO.MY_TROUBLE_MVIEW compile
/

Materialized view altered.

SQL> drop materialized view "SCO"."MY_TROUBLE_MVIEW" preserve table
/

Materialized view dropped.

ORA-08176: consistent read failure; rollback data not available

Bug 16438098 — ORA-8176 WHEN QUERYING TABLE VIA DATABASE LINK
This was closed as «not a bug», but expected behavior. Basically, if an index is being created at the time the query is accessing the remote table and tries to access the new index, the the ora-8176 is signalled

— например, при индексном доступе к interval partition table в момент добавления партиции

ORA-14278: column type or size mismatch in EXCHANGE [SUB]PARTITION

после Online Redefinition / DBMS_REDEFINITION using BY ROWID method:

SQL> alter table MY_TEMP_FOR_EXCHANGE add M_ROW$$ varchar2(255);

Table altered

SQL> alter table MY_TEMP_FOR_EXCHANGE set unused column M_ROW$$;

Table altered

SQL> ALTER TABLE MY_TARGET_FOR_EXCHANGE EXCHANGE SUBPARTITION FOR ((TO_DATE('2017-11-24T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS')) , 16) WITH TABLE MY_TEMP_FOR_EXCHANGE INCLUDING INDEXES WITHOUT VALIDATION
  2  /
 
Table altered

ORA-14097 column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Common Causes of ORA-14097 At Exchange Partition Operation (Doc ID 1418545.1)
ORA-14097 At Exchange Partition After Adding Column With Default Value (Doc ID 1334763.1)

ORA-12154: TNS:could not resolve the connect identifier specified

Troubleshooting — Heartbeat failed to connect to standby (Doc ID 1432367.1):
The given Connect Descriptor used for Log Transport Services cannot be resolved
…
— Verify if you modified the TNSNAMES.ORA since the Database ARCn and LNS-Processes started; they may not be aware of the Change. So you may have to kill those so that they get respawned again

ARC and LGWR(TT) processes depending

Oracle Linux: ORA-27301:OS Failure Message: No Buffer Space Available (Doc ID 2041723.1)

ORA-23413: table … does not have a materialized view log

SQL> select * FROM sys.slog$;

no rows selected

SQL> select * from dba_registered_mviews;

no rows selected

SQL> update MASTER_TABLE set ...

1 row updated

SQL> commit;

ORA-23413: table MASTER_TABLE does not have a materialized view log

SQL> CREATE MATERIALIZED VIEW LOG ON MASTER_TABLE ...

Materialized view log created

SQL> update MASTER_TABLE set ...

1 row updated

SQL> commit;

ORA-12034: materialized view log on MASTER_TABLE younger than last refresh

SQL> select count(*)
  2    from dba_mviews
  3   where refresh_method = 'FAST'
  4     and mview_name in
  5         (select name
  6            from dba_dependencies
  7           where referenced_name = 'MASTER_TABLE');

  COUNT(*)
----------
         1

ORA-00600: internal error code, arguments: [qcsfbdnp:1], [AWR_TOP_SQL], [], [0], [], [], [], [], [], [], [], []

Кроме патча Bug 25515215 — ORA-600 [qcsfbdnp:1] during with clause statement (Doc ID 25515215.8) в качестве workaround-а можно отказаться от использования связанных переменных/binds в запросе: [AWR_TOP_SQL] -наименование первого bind-а

ORA-942 error due to missing ora_debug_table… when trying events ‘trace[sql_compiler.*]

SQL> CREATE GLOBAL TEMPORARY TABLE system.ora_debug_table (
  2     time DATE,
  3     txt0 VARCHAR2(4000),
  4     txt1 VARCHAR2(4000),
  5     txt2 VARCHAR2(4000),
  6     txt3 VARCHAR2(4000),
  7     txt4 VARCHAR2(4000),
  8     num0 NUMBER,
  9     num1 NUMBER,
 10     num2 NUMBER,
 11     num3 NUMBER,
 12     num4 NUMBER,
 13     num5 NUMBER,
 14     num6 NUMBER,
 15     num7 NUMBER,
 16     num8 NUMBER,
 17     num9 NUMBER
 18  );

Table created

SQL> CREATE PUBLIC SYNONYM ora_debug_table FOR system.ora_debug_table;

Synonym created

SQL> GRANT SELECT, INSERT,DELETE ON  system.ora_debug_table TO public;

Grant succeeded

— and the same workaround found by Chinar Aliyev. A reason why SQL Plan Baseline is not used

ORA-28000: the account is locked on ADG/PHYSICAL STANDBY OPEN READ ONLY WITH APPLY

C:>sqlplus TESTUSER/TESTUSER_PWD@PRIMARY_DB

Last Successful login time: Thu Dec 26 2019 12:47:59 +03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

C:>sqlplus TESTUSER/TESTUSER_PWD@STANDTBY_READ_ONLY

ERROR:
ORA-28000: the account is locked

C:>sqlplus /@STANDTBY_READ_ONLY as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter user TESTUSER account unlock;

User altered.

C:>sqlplus TESTUSER/TESTUSER_PWD@STANDTBY_READ_ONLY

Last Successful login time: Thu Dec 26 2019 12:55:20 +03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Account locking in an Active Data Guard environment

ORA-22992: cannot use LOB locators selected from remote tables

SQL> insert into tmp_wr_contact_photo
  2    (contact_id, "CONTENT")
  3    select o.n_id as contact_id, p.fife as "CONTENT"
  4      from dw.tmp_contact_photo@vdc1w_dw_al_cl_if p
  5      join dw.tmp_office_contacts@vdc1w_dw_al_cl_if o
  6        on o.n_id_photo = p.n_id;
insert into tmp_wr_contact_photo
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables

SQL> insert into tmp_wr_contact_photo
  2    (contact_id, "CONTENT")
  3    select o.n_id as contact_id, p.fife as "CONTENT"
  4      from dw.tmp_contact_photo@vdc1w_dw_al_cl_if   p
  5          ,dw.tmp_office_contacts@vdc1w_dw_al_cl_if o
  6     where o.n_id_photo = p.n_id;

1 row created.

ORA-4031

ORA-04031: unable to allocate 11056 bytes of shared memory («shared pool»,»unknown object»,»parameter handle»,»dbgdCopyEvent: eventSpec«)
при попытке вызова PL/SQL over dblink to PHYSICAL STANDBY MOUNTED db (dbgd = DataBaseGuarD), не отражается в alert.log

ORA-04031 On Dataguard Environment With High Allocation In ‘KRSDICLE’ (Doc ID 2233468.1)

«The ‘KRSDICLE’ memory chunk is Data Guard specific and redo transport related . This issue was investigated in Bug 23722678 .., closed as not a bug.
The cause for this behavior is that log_archive_dest_n are configured incorrectly . In this particular case, log_archive_dest_2 on the primary database had the same service defined as log_archive_dest_2 from the standby instance.»

+ all cascade redo transport/DG configuration

Bug 32465193 — ORA-4031 Due to high SQL Monitoring allocations in Shared Pool (Doc ID 32465193.8)

 REDISCOVERY INFORMATION:
 Top 10 10 memory uses for SGA heap will show keomg allocations as a top user
 e.g.
 ----------------------------------------------
 "keomg: entry list            0001"   552 MB 16%

==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 4
----------------------------------------------
"free memory                    "  1503 MB 24%
"keomg: entry list          0001"   948 MB 15% -- *
"SQLA                       0003"   909 MB 15%
"KGLH0                      0003"   470 MB  8%
"gcs resources              0001"   365 MB  6%
"init_heap_kfsg             0001"   312 MB  5%
"gcs dynamic resources for  0001"   260 MB  4%
"gcs shadows                0001"   199 MB  3%
"SQLA                       0001"   120 MB  2%
"KGLHD                      0003"   106 MB  2%
     -----------------------------------------
free memory                        1503 MB
memory alloc.                      4641 MB
Sub total                          6144 MB
==============================================
TOP 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 4
----------------------------------------------
"free memory                    "  3526 MB
"SQLA                       0003"  1328 MB
"keomg: entry list          0001"   948 MB     -- *
...
==============================================
TOP 20 MEMORY USES ACROSS SGA HEAP 1 - 7
----------------------------------------------
"free memory                    "  8603 MB 24%
"SQLA                       0003"  8460 MB 24%
"KGLH0                      0003"  3664 MB 10%
"gcs resources              0001"  2552 MB  7%
"gcs dynamic resources for  0001"  1576 MB  4%
"gcs shadows                0001"  1392 MB  4%
"KGLHD                      0003"   962 MB  3%
"keomg: entry list          0001"   948 MB  3% -- *

ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

SQL> @inst

INST_ID VERSION_FULL  PLATFORM_NAME        DATABASE_STATUS DATABASE_ROLE    STATUS   OPEN_MODE
------- ------------- -------------------- --------------- ---------------- -------- ----------
1       19.10.0.0.0   Linux x86 64-bit     ACTIVE          PHYSICAL STANDBY OPEN     READ ONLY 
                                                                                     WITH APPLY

2*      19.10.0.0.0   Linux x86 64-bit     ACTIVE          PHYSICAL STANDBY OPEN     READ ONLY 
                                                                                     WITH APPLY

SQL> select /*+ monitor */ dummy from dual;

D
-
X

SQL> @sql

SQL_ID             CHILD SQL_TEXT
------------- ---------- ------------------------------------------------------------
fduq1uac6fhxy          0 select /*+ monitor */ dummy from dual

SQL> select DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id =>'fduq1uac6fhxy', report_level=>'all', type=>'text') SQL_Report from dual;
ERROR:
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: at "SYS.DBMS_SQLTUNE_UTIL1", line 1117
ORA-06512: at "SYS.DBMS_SQLTUNE", line 13808
ORA-06512: at "SYS.DBMS_SQLTUNE", line 19020
ORA-06512: at "SYS.DBMS_SQLTUNE", line 19351
ORA-06512: at line 1

SQL> alter session set "_parallel_syspls_obey_force" = FALSE;

Session altered.

SQL> select DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id =>'fduq1uac6fhxy', report_level=>'all', type=>'text') SQL_Report from dual;

SQL_REPORT
--------------------------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor */ dummy from dual
...

ORA-31186: Документ содержит чрезмерное число узлов

Xml Operations Give ORA-31186: Document Contains Too Many Nodes (Doc ID 2344551.1)

CAUSE
This is expected behavior as the limit for XOB has been reached.
There cannot be more than 64K nodes underneath a given node. 

SOLUTION
Please enable event="31153 trace name context forever, level 4096" in init.ora.

This event will boost the XSLT engine's node stack sizing
Since XSLT engine instances are cached in memory, this needs to be as a database-wide INIT.ORA setting, e.g.
event="31153 trace name context forever, level 1000"

Note that the parameter level value (1000 in the above example) is *not a hexadecimal bit flag*. It is a numeric size. Sizing for the node stack is in units of 1024 nodes, so the above example is a stack of approximately 1 million nodes. The upper limit is 4096 units, or a total of approximately 4 million nodes.

TBD