Oracle DBA by Example. Code samples, Scripts, Reference.: March 2022

Wednesday, March 30, 2022

_optimizer_nlj_hj_adaptive_join in oracle 19.3

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

Symptoms

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

After upgrade to oracle 19.x running a complex query with nested joins can fail with the following error:

ORA-07445: exception encountered: core dump [qkacco()+91] [SIGSEGV]

The Call Stack Trace in the associated incident trace file shows:

... qkacco <- qkajoi <- qkaqkn <- qkadrv ...

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

Solution

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

Upgrade to oracle 19.10

ALTER SESSION SET "_optimizer_nlj_hj_adaptive_join"=FALSE;

Per oracle Technote ORA-7445 [qkacco] when Running a Complex Query with Nested Joins (Doc ID 2664281.1)

Other steps to try are:

1) "_optimizer_nlj_hj_adaptive_join"=FALSE; The queries will not use adaptive join features.
-OR-
2) optimizer_adaptive_plans=FALSE; The optimizer will not create adaptive plans.
-OR-
3) optimizer_features_enable = '11.2.0.4'; Optimizer features are run as they were in 11.2, avoiding this error.

Monday, March 21, 2022

Data Pump Extract STATS "No active extraction maps".

================
Issue
================

In Golden Gate

There is no error, but data is not replicating for one of tables defined for P flow

Checking EXTRACT DPM_S_01 - is not OK, returns "No active extraction maps".

Need to fix DPM_S_01

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

Details

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

GGSCI (my_host) 24> INFO ALL
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPM_I_01 00:00:00 00:00:00
EXTRACT RUNNING DPM_P_01 00:00:00 00:00:01
EXTRACT RUNNING DPM_S_01 00:00:00 00:00:01
EXTRACT RUNNING EXT_I_01 00:00:01 00:00:00
EXTRACT RUNNING EXT_P_01 00:00:01 00:00:03
EXTRACT RUNNING EXT_S_01 00:00:02 00:00:09
REPLICAT RUNNING REP_I_01 00:00:06 00:00:08
REPLICAT RUNNING REP_P_01 00:00:00 00:00:03
REPLICAT RUNNING REP_S_01 00:00:00 00:00:05

GGSCI (my_host) 26> SEND DPM_S_01 STATS

Sending STATS request to EXTRACT DPM_S_01 ...

No active extraction maps.

GGSCI (my_host) 17> INFO DPM_S_01

EXTRACT DPM_S_01 Last Started 2022-03-21 15:35 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:03 ago)

Process ID 3497

Log Read Checkpoint File /software/ogg/191/software/ogg/191/dirdat/01/out/es000000475

First Record RBA 0

The path is not correct.

Need to unregister and register the datapump

DBLOGIN USERID OGG PASSWORD PASSWD

GGSCI (my_host as OGG@igt) 58> STOP EXTRACT DPM_S_01

Sending STOP request to EXTRACT DPM_S_01 ...
Request processed.

GGSCI (my_host as OGG@igt) 60> DELETE EXTRACT DPM_S_01

2022-03-21 15:55:35  INFO    OGG-14052  No Heartbeat entries with [DPM_S_01], none deleted.
Deleted EXTRACT DPM_S_01.

GGSCI (my_host as OGG@igt) 61> ADD EXTRACT DPM_S_01 EXTTRAILSOURCE /software/ogg/191/dirdat/01/out/es
EXTRACT added.

GGSCI (qanfv-1-dbs-1a as OGG@igt) 63> ADD RMTTRAIL /software/ogg/191/dirdat/01/in/es EXTRACT DPM_S_01
RMTTRAIL added.

GGSCI (my_host as OGG@igt) 64> START EXTRACT DPM_S_01
Sending START request to MANAGER ...

EXTRACT DPM_S_01 starting

GGSCI (my_host as OGG@igt) 65> INFO DPM_S_01

EXTRACT    DPM_S_01  Last Started 2022-03-21 15:56   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Process ID           20942
Log Read Checkpoint  File /software/ogg/191/dirdat/01/out/es000000000
                     First Record  RBA 0

GGSCI (my_host  as OGG@igt) 66> SEND EXTRACT DPM_S_01 STATS

Sending STATS request to EXTRACT DPM_S_01 ...

No active extraction maps.

Now the path is correct

But the sequence is 0 instead of actual 475

oracle@my_host:/software/ogg/191/dirprm>% ls -ltr /software/ogg/191/dirdat/01/out/ | grep es

-rw-r----- 1 oracle dba 29207662 Mar 21 15:58 es000000475

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

Setting sequence to correct value

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

GGSCI (my_host as OGG@igt) 67> STOP EXTRACT DPM_S_01

Sending STOP request to EXTRACT DPM_S_01 ...

Request processed.

GGSCI (my_host as OGG@igt) 68> ALTER EXTRACT DPM_S_01, EXTSEQNO 475 EXTRBA 0

EXTRACT altered.

GGSCI (my_host as OGG@igt) 69> START DPM_S_01

Sending START request to MANAGER ...

EXTRACT DPM_S_01 starting

GGSCI (my_host as OGG@igt) 70> SEND DPM_S_01 STATS

Sending STATS request to EXTRACT DPM_S_01 ...

Start of Statistics at 2022-03-21 16:03:33.

Now the response is fine

Golden Gate - No Replication, Datapump is broken, "No active extraction maps."

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

Issue

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

In Golden Gate

There is no error, but data is not replicating for one of tables defined for P flow

Checking EXTRACT EXT_P_01 - seems OK

Checking EXTRACT DPM_P_01 - is not OK - returns No active extraction maps.

Need to fix DPM_P_01

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

Details

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

GGSCI (my_host) 26> SEND EXT_P_01 STATS

Sending STATS request to EXTRACT EXT_P_01 ...

Start of Statistics at 2022-03-21 13:50:18.

This return valid stats for Extract. EXT_P_01 seems to be OK

GGSCI (my_host) 10> SEND DPM_P_01 stats
Sending STATS request to EXTRACT DPM_P_01 ...
No active extraction maps.

Datapump DPM_P_01 is not handling transactions generated by EXT_P_01

GGSCI (my_host) 17> INFO DPM_P_01
EXTRACT DPM_P_01 Last Started 2022-03-15 09:26 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Process ID 5311
Log Read Checkpoint File /software/ogg/191/dirdat/01/out/ep000000000
First Record RBA 0

Actual files are with sequence 104 and higher

oracle@my_host:/software/ogg/191/dirdat/01/out>% ls -ltr | grep ep
-rw-r----- 1 oracle dba 467429 Mar 15 08:56 ep000000104
-rw-r----- 1 oracle dba 170266735 Mar 15 09:13 ep000000105
-rw-r----- 1 oracle dba 1084712 Mar 15 09:27 ep000000106
-rw-r----- 1 oracle dba 499999980 Mar 18 21:45 ep000000107
-rw-r----- 1 oracle dba 499999964 Mar 21 01:18 ep000000108
-rw-r----- 1 oracle dba 165503175 Mar 21 13:40 ep000000109

Need to sync datapump with actual files

STOP EXTRACT DPM_P_01
ALTER EXTRACT DPM_P_01, EXTSEQNO 104 EXTRBA 0
START DPM_P_01

GGSCI (my_host) 20> INFO DPM_P_01
EXTRACT DPM_P_01 Last Started 2022-03-21 13:43 Status RUNNING
Checkpoint Lag 148:46:38 (updated 00:00:01 ago)
Process ID 25666
Log Read Checkpoint File /software/ogg/191/dirdat/01/out/ep000000105
2022-03-15 08:57:01.000000 RBA 117859931

GGSCI (my_host) 21> INFO DPM_P_01
EXTRACT DPM_P_01 Last Started 2022-03-21 13:43 Status RUNNING
Checkpoint Lag 65:30:53 (updated 00:00:05 ago)
Process ID 25666
Log Read Checkpoint File /software/ogg/191/dirdat/01/out/ep000000107
2022-03-18 20:12:57.000000 RBA 491824952

GGSCI (my_host) 25> INFO DPM_P_01
EXTRACT DPM_P_01 Last Started 2022-03-21 13:43 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Process ID 25666
Log Read Checkpoint File /software/ogg/191/dirdat/01/out/ep000000109
2022-03-21 13:44:18.000000 RBA 166207382

Now it is running, and files sequence is advancing

Golden Gate - No Replication, REPLICAT is broken, (Timeout waiting for message)

================
Issue
================

In Golden Gate

There is no error, but data is not replicating for one of tables defined for P flow

Checking EXTRACT EXT_P_01 - on server A - OK

Checking EXTRACT DPM_P_01 - on server A - OK

Checking EXTRACT DPM_P_01 - on server B - not OK

Need to fix REP_P_01

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

Details

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

GGSCI (my_host) 26> SEND REP_P_01 STATS

Sending STATS request to REPLICAT REP_P_01 ...

2022-03-21 14:09:38 ERROR OGG-15163 There was a problem sending a message to REPLICAT REP_P_01 (Timeout waiting for message).

Option A. - Lets try to restart REPLICAT using the skip transactoin option

GGSCI (my_host) 2> KILL REPLICAT REP_P_01

Sending KILL request to MANAGER ...

Killed process (12600) for REPLICAT REP_P_01

GGSCI (my_host) 3> START REPLICAT REP_P_01 SKIPTRANSACTION

Sending START request to MANAGER ...

REPLICAT REP_P_01 starting

GGSCI (my_host) 6> INFO REP_P_01

REPLICAT REP_P_01 Last Started 2022-03-21 14:07 Status RUNNING

INTEGRATED

Checkpoint Lag 149:06:01 (updated 00:04:25 ago)

Process ID 13155

Log Read Checkpoint File /software/ogg/191/dirdat/01/in/ep000000000

2022-03-15 08:56:41.588988 RBA 26182419

GGSCI (my_host) 22> SEND REP_P_01 STATS

Sending STATS request to REPLICAT REP_P_01 ...

2022-03-21 14:09:38 ERROR OGG-15163 There was a problem sending a message to REPLICAT REP_P_01 (Timeout waiting for message).

Option B. - Lets try to restart REPLICAT using next file

Since this was a test system, I did not care for lost transactions.

oracle@my_host:/software/ogg/191/dirdat/01/in>% ls -ltr | grep ep
-rw-r----- 1 oracle dba 499999201 Mar 21 13:43 ep000000000
-rw-r----- 1 oracle dba 499999472 Mar 21 13:43 ep000000001
-rw-r----- 1 oracle dba 342178884 Mar 21 14:03 ep000000002

GGSCI (my_host) 2> KILL REPLICAT REP_P_01

Sending KILL request to MANAGER ...

Killed process (12600) for REPLICAT REP_P_01

GGSCI (my_host) 139> ALTER REPLICAT REP_P_01, EXTSEQNO 2 EXTRBA 0

2022-03-21 14:26:00 INFO OGG-06594 Replicat REP_P_01 has been altered. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start REP_P_01 with NOFILTERDUPTRANSACTIONS option.

REPLICAT (Integrated) altered.

GGSCI (my_host) 140> START REP_P_01

Sending START request to MANAGER ...

REPLICAT REP_P_01 starting

GGSCI (my_host) 158> INFO REP_P_01

REPLICAT REP_P_01 Last Started 2022-03-21 14:26 Status RUNNING

INTEGRATED

Checkpoint Lag 00:00:00 (updated 00:00:04 ago)

Process ID 15038

Log Read Checkpoint File /software/ogg/191/dirdat/01/in/ep000000002

First Record RBA 0

GGSCI (my_host) 161> INFO REP_P_01

REPLICAT REP_P_01 Last Started 2022-03-21 14:26 Status RUNNING

INTEGRATED

Checkpoint Lag 20:55:05 (updated 00:00:00 ago)

Process ID 15038

Log Read Checkpoint File /software/ogg/191/dirdat/01/in/ep000000002

2022-03-20 17:31:39.998169 RBA 57324931

GGSCI (my_host) 175> INFO ALL

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DPM_I_01 00:00:00 00:00:08

EXTRACT RUNNING DPM_P_01 00:00:00 00:00:07

EXTRACT RUNNING DPM_S_01 00:00:00 00:00:01

EXTRACT RUNNING EXT_I_01 00:00:01 00:00:04

EXTRACT RUNNING EXT_P_01 00:00:00 00:00:03

EXTRACT RUNNING EXT_S_01 00:00:01 00:00:04

REPLICAT RUNNING REP_I_01 00:00:00 00:00:04

REPLICAT RUNNING REP_P_01 14:02:42 00:00:08

REPLICAT RUNNING REP_S_01 00:00:00 00:00:06

GGSCI (my_host) 176> INFO ALL

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DPM_I_01 00:00:00 00:00:01

EXTRACT RUNNING DPM_P_01 00:00:00 00:00:00

EXTRACT RUNNING DPM_S_01 00:00:00 00:00:04

EXTRACT RUNNING EXT_I_01 00:00:01 00:00:07

EXTRACT RUNNING EXT_P_01 00:00:00 00:00:06

EXTRACT RUNNING EXT_S_01 00:00:01 00:00:07

REPLICAT RUNNING REP_I_01 00:00:00 00:00:07

REPLICAT RUNNING REP_P_01 13:03:38 00:00:01

REPLICAT RUNNING REP_S_01 00:00:00 00:00:09

GGSCI (my_host) 177> INFO ALL

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DPM_I_01 00:00:00 00:00:07

EXTRACT RUNNING DPM_P_01 00:00:00 00:00:07

EXTRACT RUNNING DPM_S_01 00:00:00 00:00:01

EXTRACT RUNNING EXT_I_01 00:00:03 00:00:04

EXTRACT RUNNING EXT_P_01 00:00:02 00:00:08

EXTRACT RUNNING EXT_S_01 00:00:02 00:00:08

REPLICAT RUNNING REP_I_01 00:00:00 00:00:05

REPLICAT RUNNING REP_P_01 00:00:06 00:00:02

REPLICAT RUNNING REP_S_01 00:00:00 00:00:06

GGSCI (my_host) 180> SEND REP_P_01 STATS

Sending STATS request to REPLICAT REP_P_01 ...

Start of Statistics at 2022-03-21 14:41:55.

Now, The backlog was cleared and REPLICAT REP_P_01 is running as expected

And SEND <REPLICAT> STATS command is returning a valid response.

Wednesday, March 16, 2022

ORA-32321: REFRESH FAST unsupported after detail table TRUNCATE; ORA-12034: materialized view log younger than last refresh

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

Issue

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

BEGIN

DBMS_REFRESH.REFRESH('MY_USER.GSM_COUNTRIES');

END;

ORA-32321: REFRESH FAST of "MY_USER"."GSM_COUNTRIES" unsupported after detail table TRUNCATE

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025

ORA-06512: at "SYS.DBMS_IREFRESH", line 689

ORA-06512: at "SYS.DBMS_REFRESH", line 195

ORA-06512: at line 2

ORA-12034: materialized view log on "NPPROV"."GSM_REGIONS" younger than last refresh

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025

ORA-06512: at "SYS.DBMS_IREFRESH", line 689

ORA-06512: at "SYS.DBMS_REFRESH", line 195

ORA-06512: at line 2

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

Solution

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

C - Stands for Complete

BEGIN

DBMS_MVIEW.REFRESH('MY_USER.GSM_COUNTRIES','C');

END;

PL/SQL procedure successfully completed.

BEGIN

DBMS_REFRESH.REFRESH('MY_USER.GSM_COUNTRIES');

END;

PL/SQL procedure successfully completed.

Sunday, March 6, 2022

Changing Oracle Archive Files Location

Need to change /oracle_db/db1/db_igt/arch to /oracle_db/db2/db_igt/arch/
Steps

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle_db/db1/db_igt/arch
Oldest online log sequence 417
Next log sequence to archive 419
Current log sequence 419

select destination,STATUS from v$archive_dest where statuS='VALID';

DESTINATION STATUS
------------------------------------- ------------
/oracle_db/db1/db_igt/arch VALID

SQL> alter system set log_archive_dest_1='LOCATION=/oracle_db/db2/db_igt/arch/' scope=both;
System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle_db/db2/db_igt/arch/
Oldest online log sequence 417
Next log sequence to archive 419
Current log sequence 419

SQL> alter system switch logfile;
System altered.

SQL> !ls -ltr
total 253952
-rw-r----- 1 oracle dba 260046336 Mar 6 13:59 arch0001_421_1097758151.arc

SQL> !pwd

/oracle_db/db2/db_igt/arch

Pages

Wednesday, March 30, 2022

_optimizer_nlj_hj_adaptive_join in oracle 19.3

Monday, March 21, 2022

Data Pump Extract STATS "No active extraction maps".

Golden Gate - No Replication, Datapump is broken, "No active extraction maps."

Golden Gate - No Replication, REPLICAT is broken, (Timeout waiting for message)

Wednesday, March 16, 2022

ORA-32321: REFRESH FAST unsupported after detail table TRUNCATE; ORA-12034: materialized view log younger than last refresh

Sunday, March 6, 2022

Changing Oracle Archive Files Location