LONG to VARCHAR2 - See text from a LONG field

See text from a LONG field

desc USER_MVIEWS

Name                   Type                                                                    

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

OWNER                  VARCHAR2(30) 

MVIEW_NAME             VARCHAR2(30) 

CONTAINER_NAME         VARCHAR2(30) 

QUERY                  LONG         

CREATE TABLE TEMP_SNAPSHOTS AS 

SELECT mview_name, TO_LOB(query) query 

FROM USER_MVIEWS;

To see the SQL:

SELECT to_CHAR(query) FROM TEMP_SNAPSHOTS WHERE ROWNUM = 1;

Now can see the sql text

set termout off

set verify off

set newpage none

set echo off

set heading off

set pagesize 1000

set linesize 400

set feedback off

set trimspool on

COLUMN sql_str FORMAT A300 WORD_WRAP

SPOOL create_snapshots.sql

SELECT 'DROP MATERIALIZED VIEW '||mview_name||';'||CHR(10)||

       CHR(10)||

       'CREATE MATERIALIZED VIEW '||mview_name||' REFRESH FAST ON DEMAND AS'||CHR(10)||

       DBMS_LOB.SUBSTR(query,4000,1)

       ||';'||CHR(10)||CHR(10) as sql_str

FROM TEMP_SNAPSHOTS

WHERE mview_name <> 'GATEWAY_SS';

spool off

SPOOL create_snapshot_gateway_ss.sql

SELECT 'DROP MATERIALIZED VIEW '||mview_name||';'||CHR(10)||

       CHR(10)||

       'CREATE MATERIALIZED VIEW '||mview_name||' REFRESH FAST ON DEMAND AS'||CHR(10)||

       DBMS_LOB.SUBSTR(query,3000,1)

       ||CHR(10) as sql_str

FROM TEMP_SNAPSHOTS

WHERE mview_name = 'GATEWAY_SS';

SELECT 'DROP MATERIALIZED VIEW '||mview_name||';'||CHR(10)||

       CHR(10)||

       'CREATE MATERIALIZED VIEW '||mview_name||' REFRESH FAST ON DEMAND AS'||CHR(10)||

       DBMS_LOB.SUBSTR(query,6000,3001)

       ||';'||CHR(10)||CHR(10) as sql_str

FROM TEMP_SNAPSHOTS

WHERE mview_name = 'GATEWAY_SS';

spool off

SPOOL create_synonyms.sql

SELECT 'CREATE OR REPLACE SYNONYM '||synonym_name||' FOR '||table_name||';'||CHR(10)

       as sql_str

 FROM USER_SYNONYMS

WHERE table_name IN (SELECT mview_name FROM TEMP_SNAPSHOTS);

spool off

create_snapshots.sql - will have all the snapshot SQLs

create_snapshot_gateway_ss.sql - special case, where sql is too long.

create_synonyms - will have all thew synonyms for the snapshots

Enable and Disable Constraints from script

Create Enable/Disable Constraints script

enable_cons.sql

set serverout on

exec dbms_output.enable(100000);

set heading off

set linesize 132

set pagesize 1000

set heading off

set feedback off

spool enable_cons.txt

prompt spool enable_cons.log

select 'alter table '|| table_name || ' enable constraint ' || constraint_name || ';' from user_constraints

where CONSTRAINT_TYPE='R';

prompt spool off

spool off

@enable_cons.txt

disable_cons.sql

set serverout on

exec dbms_output.enable(100000);

set heading off

set linesize 132

set pagesize 1000

set heading off

set feedback off

spool disable_cons.txt

prompt spool disable_cons.log

select 'alter table '|| table_name || ' disable constraint ' || constraint_name || ';' from user_constraints

where CONSTRAINT_TYPE='R'

/

prompt spool off

spool off

@disable_cons.txt

Create Enable/Disable Triggers script

enable_trigs.sql

set serverout on

exec dbms_output.enable(100000);

set heading off

set linesize 132

set pagesize 1000

set heading off

set feedback off

spool enable_trigs.txt

prompt spool enable_trigs.log

select 'alter trigger '|| trigger_name || ' enable ' || ';' from user_triggers where trigger_name!='REFRESH_VIEWS_TRG'

/

prompt spool off

spool off

@enable_trigs.txt

disable_trigs.sql

set serverout on

exec dbms_output.enable(100000);

set heading off

set linesize 132

set pagesize 1000

set heading off

set feedback off

spool disable_trigs.txt

prompt spool disable_trigs.log

select 'alter trigger '|| trigger_name || ' disable  '  || ';' from user_triggers

/

prompt spool off

spool off

@disable_trigs.txt

_optimizer_nlj_hj_adaptive_join in oracle 19.3

 _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

Or

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.

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) 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 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) 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 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.

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

or

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.