Oracle DBA by Example. Code samples, Scripts, Reference.: 2021

Sunday, December 26, 2021

Tuning Golden Extract Memory

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

General

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

Golden Gate Extract was running slow, lag increasing, until it was several days

GGSCI (geqaa-1-aps-01) 2> SEND EXT_P_07 status

Sending STATUS request to EXTRACT EXT_P_07 ...

EXTRACT EXT_P_07 (PID 5256)

Current status: Recovery complete: Processing data

Current read position:

Redo thread #: 1

Sequence #: 9610

RBA: 869956208

Timestamp: 2021-12-20 00:43:58.000000

SCN: 1.1180047274 (5475014570)

Current write position:

Sequence #: 73

RBA: 4711299

Timestamp: 2021-12-26 14:41:42.940217

Extract Trail: /software/ogg/191/dirdat/07/out/ep

No errors are in extract log.

Running top, iostat, sar, free - do not show that server is low on CPU/memory/IO

Why extract is slow?

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

Investigation

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

Extract was set with these parameters:

EXTRACT ext_p_07

setenv (ORACLE_SID="igt")

setenv (ORACLE_HOME="/software/oracle/193")

setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

USERID gg_user, PASSWORD AACAAAAAAAAAAAIAZEDCPHICXGPEQCED ENCRYPTKEY DEFAULT

EXTTRAIL /software/ogg/191/dirdat/07/out/ep

CACHEMGR CACHESIZE 1024M

NODYNAMICRESOLUTION

-- Bi-direction replication

TRANLOGOPTIONS EXCLUDEUSER ogg

TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 256)

include ./dirprm/PROVtables_07.inc

Per Oracle recommendation:

Optional Parameters for Integrated Modes

MAX_SGA_SIZE: Controls the amount of shared memory used by the logmining server.
The default is 1 GB.

So the current value of 256M looks too small

The system in question is running in total 6 Extract processes.

In order to make use of the MAX_SGA_SIZE parameter, need to set accordingly the STREAMS_POOL_SIZE

Per Oracle Note STREAMS_POOL_SIZE

Products and features that use the Streams pool include Oracle GoldenGate, XStream, Oracle Advanced Queuing, and Oracle Data Pump.

Oracle's Automatic Shared Memory Management feature manages the size of the Streams pool when the SGA_TARGET initialization parameter is set to a nonzero value.

If the STREAMS_POOL_SIZE initialization parameter also is set to a nonzero value,

then Automatic Shared Memory Management uses this value as a minimum for the Streams pool.

If both the STREAMS_POOL_SIZE and the SGA_TARGET initialization parameters are set to 0 (zero),

then, by default, on the first request for Streams pool memory in a database,

an amount of memory equal to 10% of the shared pool is transferred from the buffer cache to the Streams pool.

If SGA_TARGET is set to a nonzero value and STREAMS_POOL_SIZE is not specified or is set to a null value,

then Automatic Shared Memory Management uses 0 (zero) bytes as a minimum for the Streams pool.

If the STREAMS_POOL_SIZE initialization parameter is set to a nonzero value,

and the SGA_TARGET parameter is set to 0 (zero),

then the Streams pool size is the value specified by the STREAMS_POOL_SIZE parameter, in bytes.

In this installation, STREAMS_POOL_SIZE was set to zero, SGA_TARGET was set to zero, so Streams Pool was supposed to use 10% of buffer cache.

But when checking V$SGA_DYNAMIC_COMPONENTS, for current memory allocation, the actual allocation to streams pool is 134Mb out of 16Gb, which is close to 1%.

SELECT component, current_size, max_size

FROM V$SGA_DYNAMIC_COMPONENTS;

COMPONENT CURRENT_SIZE MAX_SIZE

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

shared pool 1879048192 1879048192

large pool 469762048 469762048

java pool 402653184 402653184

streams pool 134217728 134217728

DEFAULT buffer cache 16508780544 17716740096

Shared IO Pool 536870912 536870912

Estimate for STREAMS_POOL_SIZE

Per Oracle Technote "Integrated Extract / Replicat and STREAMS_POOL_SIZE (Doc ID 2078459.1)"

For Oracle GoldenGate - Version 11.2.1.0.0 and later

By default, one integrated capture Extract requests the logmining server to run with MAX_SGA_SIZE of 1GB and a PARALLELISM of 2.

Thus, if you are running three Extracts in integrated capture mode in the same database instance, you need at least 3 GB of memory allocated to the Streams pool.

As best practice, keep 25 percent of the Streams pool available.

For example, if there are three Extracts in integrated capture mode, set STREAMS_POOL_SIZE to the following:

3 GB + (3 GB * 0.25) = 3.75 GB

---------------------------------------
The Integrated replicat also needs to have 1GB STREAMS_POOL_SIZE per process and additional 25 percent though it is not been explicitly mentioned in the guides.

So in this installation, the estimate for STREAMS_POOL_SIZE would be:

5 Extract Processes
1Gb Per Extract

Additional 25% memory overhead

(1Gb*5) + (0.25Gb*5) = 6.25Gb

Setting STREAMS_POOL_SIZE

ALTER SYSTEM SET STREAMS_POOL_SIZE = 6250M SCOPE = BOTH;

The DB was low on memory, so SREAMS_POOL_SIZE was set to 4Gb

COMPONENT CURRENT_SIZE MAX_SIZE

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

shared pool 973078528 1006632960

large pool 33554432 33554432

java pool 33554432 33554432

streams pool 4227858432 4227858432

DEFAULT buffer cache 603979776 3355443200

DEFAULT 16K buffer cache 536870912 536870912

Shared IO Pool 134217728 134217728

In-Memory Area 4294967296 4294967296

The extract MAX_SGA_SIZE was increased to 1024

TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 1024)

After above changes + restart to extract, the lag was quickly reduced to zero:

GGSCI (geqaa-1-aps-01) 1> INFO ALL

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DPM_I_05 00:00:00 00:00:07

EXTRACT RUNNING DPM_P_05 00:00:00 00:00:05

EXTRACT RUNNING DPM_P_07 00:00:00 00:00:04

EXTRACT RUNNING DPM_S_05 00:00:00 00:00:07

EXTRACT RUNNING DPM_S_07 00:00:00 00:00:07

EXTRACT RUNNING EXT_I_05 00:00:00 00:00:08

EXTRACT RUNNING EXT_P_05 00:00:00 00:00:07

EXTRACT RUNNING EXT_P_07 00:00:00 00:00:01

EXTRACT RUNNING EXT_S_05 00:00:00 00:00:06

EXTRACT RUNNING EXT_S_07 00:00:00 00:00:03

REPLICAT RUNNING REP_I_05 00:00:00 00:00:02

REPLICAT RUNNING REP_P_05 00:00:00 00:00:07

REPLICAT RUNNING REP_P_07 00:00:00 00:00:09

REPLICAT RUNNING REP_S_05 00:00:00 00:00:02

REPLICAT RUNNING REP_S_07 00:00:00 00:00:09

Wednesday, December 22, 2021

Gather Stats Task

=================================
What Is The Default Gather Stats Task?
=================================

Out of the box, Oracle comes with a default job, that gathers statistics.

What is this task?

Statistics gathering is implemented using the Automatic Optimizer Statistics Collection Maintenance task.

The name of the task is ‘auto optimizer stats collection‘.

The task is scheduled to run during the maintenance window, and it is supposed to gather statistics on the objects with stale or missing stats.

Why are lots of people referring to this task as a job?
In Oracle 10G there was a default job that was gathering statistics (a dba_jobs job).
Starting with 11G, there is no gather stats job.

The information below applies to 11g and 12c.

=================================
Automatic Optimizer Statistics Collection.
=================================
Oracle has implemented maintenance tasks, and one of them is the Automatic Optimizer Statistics Collection.
This task runs a program, called gather_stats_prog, which in turn invokes the following plsql block:
dbms_stats.gather_database_stats_job_proc

SELECT client_name, task_name, status, program_action
FROM DBA_AUTOTASK_TASK,
DBA_SCHEDULER_PROGRAMS
WHERE UPPER(task_name)=UPPER(program_name)
AND client_name='auto optimizer stats collection';

client_name task_name
------------------------------- -------------------
auto optimizer stats collection gather_stats_prog

status program_action
--------- -----------------------------------------
ENABLED dbms_stats.gather_database_stats_job_proc

Reference

http://dbaparadise.com/2017/11/how-are-statistics-gathered-in-the-database/

In case DBMS_STATS.GATHER_DATABASE_STATS_JOB is slow and using a lot of CPU, it might be because of missing dictionary stats.

SOLUTION: Gather dictionary stats and fixed object stats by executing following:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

EXEC DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Sunday, December 12, 2021

When working with FETCH cursor BULK COLLECT INTO, where to put the EXIT WHEN cursor%NOTFOUND; ?

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

General

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

When working with FETCH cursor BULK COLLECT INTO, where to put the EXIT WHEN cursor%NOTFOUND; ?

For Example:

The Limit is 20000 but actual data is 20

Where do you put the EXIT WHEN cursor%NOTFOUND ?

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

Code Example 1 - not good

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

Example 1 - after the FETCH

This is not good - as the BEGIN Block of FORALL is never reached.

PROCEDURE purge_temp_keys(v_return_code OUT NUMBER,

v_affiliate_id IN NUMBER )IS

TYPE ARR_ROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
tb_rows ARR_ROWID;
row PLS_INTEGER;
v_counter INTEGER;

CURSOR purge_population_cur (v_affiliate_id in number) IS
SELECT rowid FROM SFI_SPARX_TEMP_KEYS
WHERE affiliate_id = v_affiliate_id;

v_bulk NUMBER(10):=0;
-- ora-24381 error(s) in array dml in oracle
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
v_msg_str varchar2(200):='';

BEGIN
v_return_code:=0;
OPEN purge_population(v_affiliate_id);
LOOP
v_bulk := v_bulk+1;
FETCH purge_population_cur bulk collect into tb_rows limit 20000;
EXIT WHEN purge_population_cur%NOTFOUND;
BEGIN
FORALL row IN 1 .. tb_rows.count()
DELETE SFI_SPARX_TEMP_KEYS WHERE rowid = tb_rows(row);
COMMIT;
EXCEPTION
WHEN dml_errors THEN
COMMIT;
RAISE;
WHEN others THEN -- other exceptions :
COMMIT;
END;
--EXIT WHEN purge_population_cur%NOTFOUND;
END LOOP;
CLOSE purge_population_cur;
commit;
v_return_code:=0;
EXCEPTION
WHEN OTHERS THEN
BEGIN
CLOSE purge_population_cur;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
RAISE;
v_return_code := -1;
END PURGE_SFI_SPARX_TEMP_KEYS;

Example 2

The EXIT is at the end of the LOOP

This is correct

Now code in BLOCK is looped once

PROCEDURE purge_temp_keys(v_return_code OUT NUMBER,

v_affiliate_id IN NUMBER )IS

TYPE ARR_ROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
tb_rows ARR_ROWID;
row PLS_INTEGER;
v_counter INTEGER;

CURSOR purge_population_cur(v_affiliate_id in number) IS
SELECT rowid FROM SFI_SPARX_TEMP_KEYS
WHERE affiliate_id = v_affiliate_id;

v_bulk NUMBER(10):=0;
-- ora-24381 error(s) in array dml in oracle
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
v_msg_str varchar2(200):='';

BEGIN
v_return_code:=0;
OPEN purge_population_cur(v_affiliate_id);
LOOP
v_bulk := v_bulk+1;
FETCH purge_population_cur bulk collect into tb_rows limit 20000;
--EXIT WHEN purge_population_cur%NOTFOUND;
BEGIN
FORALL row IN 1 .. tb_rows.count()
DELETE SFI_SPARX_TEMP_KEYS WHERE rowid = tb_rows(row);
COMMIT;
EXCEPTION
WHEN dml_errors THEN
COMMIT;
RAISE;
WHEN others THEN -- other exceptions :
COMMIT;
END;
EXIT WHEN purge_population_cur%NOTFOUND;
END LOOP;
CLOSE purge_population_cur;
commit;
v_return_code:=0;
EXCEPTION
WHEN OTHERS THEN
BEGIN
CLOSE purge_population_cur;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
RAISE;
v_return_code := -1;
END PURGE_SFI_SPARX_TEMP_KEYS;

Monday, December 6, 2021

Drop Obsolete Tablespaces and Datafiles

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

General

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

Drop Obsolete Tablespaces and Datafiles

In case table is partitioned, and a TBS is assigned to each partition, it might be that there are obsolete TBS which are assigned to already dropped partitions

Step 1 - Identify Obsolete Tablespaces

Step 2 - Drop Obsolete Tablespaces and Datafiles

Step 3 - Identify datafiles that are assigned to non existing tablespaces.

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

Code by Example

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

Step 1 - Identify Obsolete Tablespaces

SELECT 'DROP TABLESPACE '||obsolete_tbs||' INCLUDING CONTENTS AND DATAFILES;'

FROM (

SELECT DBA_DATA_FILES.tablespace_name as obsolete_tbs,

DBA_DATA_FILES.file_name as obsolete_data_file,

ROUND(DBA_DATA_FILES.bytes/1024/1024) as Mb

FROM (

SELECT tablespace_name as tablespace_name

FROM DBA_DATA_FILES

WHERE 1=1

AND file_name LIKE '%20%'

AND tablespace_name NOT LIKE '%YEARLY%'

MINUS

SELECT DISTINCT tablespace_name

FROM DBA_SEGMENTS

) OBSOLETE_TBS_LIST,

DBA_DATA_FILES

WHERE OBSOLETE_TBS_LIST.tablespace_name =

DBA_DATA_FILES.tablespace_name

);

Step 2 - Drop Obsolete Tablespaces and Datafiles

DROP TABLESPACE DWNG_REP_MONTHLY_201701 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE DWNG_REP_MONTHLY_201702 INCLUDING CONTENTS AND DATAFILES;

etc...

Step 3 - Identify datafiles that are assigned to non existing tablespaces.

SELECT *

FROM DBA_DATA_FILES

WHERE file_name LIKE '%DWNG_REP_MONTHLY_2016%'

ORDER BY file_name;

ls -ltr | grep 2016

rw-r----- 1 oracle dba 2105344 Dec 6 08:43 DWNG_DAILY_TB_MONTHLY_201601_1.dbf

-rw-r----- 1 oracle dba 2105344 Dec 6 08:43 DWNG_DAILY_TB_MONTHLY_201602_1.dbf

-rw-r----- 1 oracle dba 2105344 Dec 6 08:44 DWNG_DAILY_TB_MONTHLY_201603_1.dbf

-rw-r----- 1 oracle dba 2105344 Dec 6 08:44 DWNG_DAILY_TB_MONTHLY_201605_1.dbf

-rw-r----- 1 oracle dba 2105344 Dec 6 08:44 DWNG_DAILY_TB_MONTHLY_201604_1.dbf

In case the files from OS do not exist in DB - simply from these files with OS command - as there were assigned to a dropped tablespace.

Error ORA-14405: partitioned index contains partitions in a different tablespace

In case of error ORA-14405 need to find which objects use the same tablespace.

DROP TABLESPACE DWNG_REP_MONTHLY_201901 INCLUDING CONTENTS AND DATAFILES

ERROR at line 1:

ORA-14405: partitioned index contains partitions in a different tablespace

SELECT table_owner,

table_name,

partition_name,

tablespace_name,

DBA_TAB_PARTITIONS.*

FROM DBA_TAB_PARTITIONS

WHERE (table_owner, table_name) IN (

SELECT table_owner, table_name

FROM dba_tab_partitions DTB

WHERE DTB.tablespace_name = 'DWNG_REP_MONTHLY_201901'

AND EXISTS

(SELECT *

FROM DBA_TAB_PARTITIONS DTB_INNER

WHERE DTB.table_owner = DTB_INNER.table_owner

AND DTB.table_name = DTB_INNER.table_name

AND DTB_INNER.tablespace_name <> 'DWNG_REP_MONTHLY_201901'

)

GROUP BY table_owner, table_name)

ORDER BY 1, 2, partition_position;

SOME_USER REP_OUT_DAILY_SMS_TRANSACTIONS

SOME_USER REP_IN_DAILY_SMS_TRANSACTIONS

MY_USER REP_OUT_DAILY_TRANSACTIONS

MY_USER REP_IN_DAILY_DN_TRANSACTIONS

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

Check Tablespace Usage

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

SELECT 'TABLE' AS type, COUNT(*) AS segments

FROM DBA_TABLES T

WHERE T.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106'

UNION ALL

SELECT 'INDEX' AS type, COUNT(*) AS segments

FROM DBA_INDEXES I

WHERE I.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106'

UNION ALL

SELECT 'LOB' as type, COUNT(*) AS segments

FROM DBA_LOBS L

WHERE L.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106'

UNION ALL

SELECT 'TABLE_PARTITIONS' as type, COUNT(*) as segments

FROM DBA_TAB_PARTITIONS TP

WHERE TP.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106'

UNION ALL

SELECT 'INDEX_PARTITIONS' as type, COUNT(*) as segments

FROM DBA_IND_PARTITIONS IP

WHERE IP.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106'

UNION ALL

SELECT 'LOB_PARTITIONS' as type, COUNT(*) as segments

FROM DBA_LOB_PARTITIONS LP

WHERE LP.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106';

ORA-14404 and ORA-14405 When dropping a partition

DROP TABLESPACE CDROMETER_DATA_HIST_202107 INCLUDING CONTENTS AND DATAFILES;

ORA-14404: partitioned table contains partitions in a different tablespace

To solve this error, need to free move these objects to a different tablespace.

CREATE TABLESPACE CDROMETER_TEST_TBS DATAFILE '/oracle_db/db1/db_igt/cdrometer_test_data_01.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 10000M;

To generate the SQL:

SELECT 'ALTER TABLE '||TABLE_OWNER ||'.'||table_name||' MOVE PARTITION '||partition_name||' TABLESPACE NEW_TABLESPACE_NAME UPDATE INDEXES;'

FROM DBA_TAB_PARTITIONS

WHERE tablespace_name = 'CDROMETER_DATA_HIST_202107';

Actual SQL:

ALTER TABLE CDROMETER_TEST.CDROMETER_DATA_HISTORY MOVE PARTITION P_202107 TABLESPACE CDROMETER_TEST_TBS UPDATE INDEXES;

DROP TABLESPACE CDROMETER_DATA_HIST_202107 INCLUDING CONTENTS AND DATAFILES;

ORA-14405: partitioned index contains partitions in a different tablespace

SELECT COUNT(*) FROM DBA_SEGMENTS WHERE tablespace_name IN ('CDROMETER_DATA_HIST_202107');

COUNT(*)

----------

SELECT COUNT(*) FROM DBA_IND_PARTITIONS WHERE tablespace_name IN ('CDROMETER_DATA_HIST_202107');

  COUNT(*)

----------

3

To generate the SQL:

SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||partition_name||' TABLESPACE CDROMETER_TEST_TBS;' FROM DBA_IND_PARTITIONS WHERE tablespace_name IN ('CDROMETER_DATA_HIST_202107');

ALTER INDEX CDROMETER_TEST.CDROMETER_DATA_HISTORY_A2 REBUILD PARTITION P_202107  TABLESPACE CDROMETER_TEST_TBS;

DROP TABLESPACE CDROMETER_DATA_HIST_202107 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped

Checks before dropping tablespace

SELECT * FROM DBA_SEGMENTS       WHERE tablespace_name LIKE 'CDROMETER_DATA_HIST_2021%'
SELECT * FROM DBA_IND_PARTITIONS WHERE tablespace_name LIKE 'CDROMETER_DATA_HIST_2021%'
SELECT * FROM DBA_TAB_PARTITIONS WHERE tablespace_name LIKE 'CDROMETER_DATA_HIST_2021%'
SELECT * FROM DBA_DATA_FILES     WHERE tablespace_name LIKE 'CDROMETER_DATA_HIST_2021%'

DROP TABLESPACE CDROMETER_DATA_HIST_202109 INCLUDING CONTENTS AND DATAFILES;

Tuesday, November 30, 2021

ORA-23404: refresh group "MY_USER"."MY_TABLE" does not exist

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

General

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

Error when running the scheduled refresh for a snapshot

BEGIN
DBMS_REFRESH.refresh('"MY_USER"."MY_TABLE"');
END;
/

ORA-23404: refresh group "MY_USER"."MY_TABLE" does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REFRESH", line 23
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 3
View program sources of error stack?

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

How to Fix

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

Solution A

Replace DBMS_REFRESH with DBMS_SNAPSHOT - which does not require group configuration

BEGIN
DBMS_SNAPSHOT.refresh('"MY_USER"."MY_TABLE"');

END;
/

Solution B
Create a Refresh Group.

And refresh by Refresh group name.

Now:

SELECT rowner, rname, refgroup, job, interval

FROM USER_REFRESH

WHERE rname = 'MY_GROUP';

--0 rows selected

Create a new Refresh Group.

BEGIN

DBMS_REFRESH.MAKE(name => 'MY_USER.MY_GROUP',

list => '',

next_date => TRUNC(SYSDATE+1)+0.2/24 ,

interval => 'TRUNC(SYSDATE+1)+0.2/24 ',

implicit_destroy => TRUE,

lax => FALSE,

job => 299);

commit;

END;

Add Materialized View to the Refresh Group.

BEGIN

DBMS_REFRESH.ADD(name => 'MY_USER.MY_GROUP',

list => 'MY_USER.MY_TABLE');

commit;

END;

SELECT rowner, rname, refgroup, job, interval

FROM USER_REFRESH

WHERE rname = 'MY_GROUP';

ROWNER RNAME REFGROUP JOB INTERVAL

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

DWNG MY_GROUP 141 299 TRUNC(SYSDATE+1)+0.5/24

SELECT owner, name, type, rowner, rname, refgroup, job  

  FROM USER_REFRESH_CHILDREN 

WHERE refgroup = 141;

OWNER    NAME     TYPE     ROWNER  RNAME        REFGROUP JOB

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

MY_USER  MY_TABLE SNAPSHOT MY_USER MCCMNC_TABLE 11       299

Join USER_REFRESH and USER_REFRESH_CHILDREN

SELECT UR.rowner, UR.rname, URC.name, URC.type, UR.job
  FROM USER_REFRESH UR, USER_REFRESH_CHILDREN URC
 WHERE UR.refgroup = URC.refgroup;

ROWNER     RNAME    NAME      TYPE     JOB
---------- -------- --------- -------- ----
MY_USER    MY_GROUP MY_TABLE  SNAPSHOT 299

Fix the broken job

BEGIN

DBMS_JOB.broken(299, FALSE);

DBMS_JOB.next_date(299, TRUNC(SYSDATE+1)+0.2/24);

commit;

END;

To test the execution:

BEGIN

DBMS_JOB.next_date(299, SYSDATE+1/1440);

commit;

END;

Sunday, November 28, 2021

Golden gate DATAPUMP is not working

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

General

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

Golden Gate DATAPUMP is not working

In the status, DATAPUMP is RUNNING

But no data is getting replicated

How to troubleshoot and fix

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

Troubleshoot and Fix

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

GGSCI (my_server) 1> 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_N_01 00:00:00 00:00:06

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

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

EXTRACT RUNNING EXT_I_01 00:00:00 00:00:05

EXTRACT RUNNING EXT_N_01 00:00:00 00:00:01

EXTRACT RUNNING EXT_P_01 00:00:00 00:00:04

EXTRACT RUNNING EXT_S_01 00:00:03 00:00:04

REPLICAT RUNNING REP_I_01 00:00:00 00:00:06

REPLICAT RUNNING REP_N_01 00:00:00 00:00:03

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

REPLICAT RUNNING REP_S_01 00:00:00 00:00:08

GGSCI (my_server) 6> SEND EXTRACT DPM_N_01 STATS

Sending STATS request to EXTRACT DPM_N_01 ...

No active extraction maps.

GGSCI (my_server as OGG@igt) 10> INFO DPM_N_01

EXTRACT DPM_N_01 Last Started 2021-11-28 10:06 Status RUNNING

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

Process ID 31970

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

First Record RBA 0

GGSCI (my_server as OGG@igt) 11> DBLOGIN USERID OGG PASSWORD XXXXX

GGSCI (my_server as OGG@igt) 12> INFO EXT_N_01 DETAIL

Trail Name Seqno RBA Max MB Trail Type

/software/ogg/191/dirdat/01/out/en 10 198769 500 EXTTRAIL

GGSCI (my_server as OGG@igt) 14> SEND EXTRACT DPM_N_01, FORCESTOP

Sending FORCESTOP request to EXTRACT DPM_N_01 ...

STOP request will be executed immediately (recovery aborted).

GGSCI (my_server as OGG@igt) 15> STOP EXTRACT DPM_N_01

EXTRACT DPM_N_01 is already stopped.

GGSCI (my_server as OGG@igt) 16> ALTER EXTRACT DPM_N_01, EXTSEQNO 10 EXTRBA 198769

EXTRACT altered.

GGSCI (my_server as OGG@igt) 17> START DPM_N_01

Sending START request to MANAGER ...

EXTRACT DPM_N_01 starting

GGSCI (my_server as OGG@igt) 18> INFO DPM_N_01

EXTRACT DPM_N_01 Last Started 2021-11-28 13:02 Status RUNNING

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

Process ID 23935

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

First Record RBA 198769

GGSCI (my_server as OGG@igt) 19> INFO DPM_N_01

EXTRACT DPM_N_01 Last Started 2021-11-28 13:02 Status RUNNING

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

Process ID 23935

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

2021-11-28 13:02:07.000000 RBA 203030

Now the RBA in Datapump is advancing

Golden Gate REPLICAT is not working

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

General

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

Golden Gate REPLICAT is not working

In the status, REPLICATE is RUNNING

But no data is getting replicated

How to troubleshoot and fix

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

Troubleshoot and Fix

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

GGSCI (my_server) 1> 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_N_01 00:00:00 00:00:06

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

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

EXTRACT RUNNING EXT_I_01 00:00:00 00:00:05

EXTRACT RUNNING EXT_N_01 00:00:00 00:00:01

EXTRACT RUNNING EXT_P_01 00:00:00 00:00:04

EXTRACT RUNNING EXT_S_01 00:00:03 00:00:04

REPLICAT RUNNING REP_I_01 00:00:00 00:00:06

REPLICAT RUNNING REP_N_01 00:00:00 00:00:03

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

REPLICAT RUNNING REP_S_01 00:00:00 00:00:08

GGSCI (my_server) 1> SEND REPLICAT REP_N_01 STATS

Sending STATS request to REPLICAT REP_N_01 ...

No active replication maps.

Integrated Replicat Statistics:

Total transactions 0.00

Redirected 0.00

Replicated procedures 0.00

DDL operations 0.00

Stored procedures 0.00

Datatype functionality 0.00

Operation type functionality 0.00

Event actions 0.00

Direct transactions ratio 0.00%

GGSCI (my_server) 1> INFO REP_N_01

REPLICAT REP_N_01 Last Started 2021-11-28 10:06 Status RUNNING

INTEGRATED

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

Process ID 31883

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

First Record RBA 0

oracle@qanfv-2-dbs-01:/software/ogg/191/dirdat/01/in>% ls -ltr | grep en

-rw-r----- 1 oracle dba 499997601 Aug 1 08:01 en000007644

-rw-r----- 1 oracle dba 499999336 Aug 1 08:03 en000007645

-rw-r----- 1 oracle dba 499997863 Aug 1 08:04 en000007646

-rw-r----- 1 oracle dba 499999493 Aug 1 08:06 en000007647

-rw-r----- 1 oracle dba 499998290 Aug 1 08:08 en000007648

-rw-r----- 1 oracle dba 499995601 Aug 1 08:10 en000007649

-rw-r----- 1 oracle dba 499997099 Aug 1 08:11 en000007650

-rw-r----- 1 oracle dba 499996252 Aug 1 08:13 en000007651

-rw-r----- 1 oracle dba 499996558 Aug 1 08:16 en000007652

-rw-r----- 1 oracle dba 499997959 Aug 1 08:18 en000007653

-rw-r----- 1 oracle dba 499998548 Aug 1 08:19 en000007654

-rw-r----- 1 oracle dba 331578832 Aug 1 08:38 en000007655

-rw-r----- 1 oracle dba 499999030 Aug 1 08:46 en000007656

-rw-r----- 1 oracle dba 499999386 Aug 1 08:48 en000007657

-rw-r----- 1 oracle dba 159395611 Nov 28 10:06 en000007658

-rw-r----- 1 oracle dba 5460356 Nov 28 11:56 en000007659

GGSCI (my_server) 2> STOP REPLICAT REP_N_01

Sending STOP request to REPLICAT REP_N_01 ...

Request processed.

GGSCI (my_server) 3> ALTER REPLICAT REP_N_01, EXTSEQNO 7658

2021-11-28 11:59:33 INFO OGG-06594 Replicat REP_N_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_N_01 with NOFILTERDUPTRANSACTIONS option.

REPLICAT altered.

GGSCI (my_server) 8> START REPLICAT REP_N_01

Sending START request to MANAGER ...

REPLICAT REP_N_01 starting

GGSCI (my_server) 11> INFO REPLICAT REP_N_01

REPLICAT REP_N_01 Last Started 2021-11-28 12:02 Status RUNNING

INTEGRATED

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

Process ID 15945

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

First Record RBA 0

GGSCI (my_server) 12> SEND REPLICAT REP_N_01 STATS

Sending STATS request to REPLICAT REP_N_01 ...

Start of Statistics at 2021-11-28 12:03:13.

Integrated Replicat Statistics:

Total transactions 3.00

Redirected 0.00

Replicated procedures 0.00

DDL operations 0.00

Stored procedures 0.00

Datatype functionality 0.00

Operation type functionality 0.00

Event actions 0.00

Direct transactions ratio 0.00%

Replicating from LAB_QANFV_ALLQQ.SGA_W_IPN_SUBSCRIBER to LAB_QANFV_ALLQQ.SGA_W_IPN_SUBSCRIBER:

*** Total statistics since 2021-11-28 12:03:06 ***

Total inserts 0.00

Total updates 0.00

Total deletes 0.00

Total upserts 0.00

Total discards 0.00

Total ignores 1181.00

Total operations 0.00

*** Daily statistics since 2021-11-28 12:03:06 ***

Total inserts 0.00

Total updates 0.00

Total deletes 0.00

Total upserts 0.00

Total discards 0.00

Total ignores 1181.00

Total operations 0.00

*** Hourly statistics since 2021-11-28 12:03:06 ***

Total inserts 0.00

Total updates 0.00

Total deletes 0.00

Total upserts 0.00

Total discards 0.00

Total ignores 1181.00

Total operations 0.00

*** Latest statistics since 2021-11-28 12:03:06 ***

Total inserts 0.00

Total updates 0.00

Total deletes 0.00

Total upserts 0.00

Total discards 0.00

Total ignores 1181.00

Total operations 0.00

End of Statistics.

Issue is fixed.

Pages

Sunday, December 26, 2021

Wednesday, December 22, 2021

Sunday, December 12, 2021

Monday, December 6, 2021

Tuesday, November 30, 2021

Sunday, November 28, 2021