Golden Gate 101

DBLOGIN

DBLOGIN USER XXX, PASSWORD YYY

Remove + Install DataPump

STOP EXTRACT DPM_I_01
DELETE EXTRACT DPM_I_01
ADD EXTRACT DPM_I_01 EXTTRAILSOURCE /software/ogg/191/dirdat/01/out/ei
ADD RMTTRAIL /software/ogg/191/dirdat/01/in/ei EXTRACT DPM_I_01
START EXTRACT DPM_I_01
INFO EXTRACT DPM_I_01

Resync  DataPump after Install

STOP DPM_I_01
ALTER EXTRACT DPM_I_01, EXTSEQNO 9313
START EXTRACT DPM_I_01
INFO EXTRACT DPM_I_01
SEND DPM_I_01  STATS

Resync  Replicat
STOP REPLICAT REP_I_01
ALTER REPLICAT REP_I_01, EXTSEQNO 4616
START REPLICAT REP_I_01
INFO REP_I_01
SEND REP_I_01 STATS

Oracle COMMIT options from 11gR2

COMMIT_LOGGING and COMMIT_WAIT

Since Oracle 11gR2 new options to COMMIT were added:

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

COMMIT_LOGGING

===================
COMMIT_LOGGING specifies whether the log writer writes redo data in batches or at commit.

Options are IMMEDIATE and BATCH

IMMEDIATE is the default behavior.

COMMIT_LOGGING=IMMEDIATE;
The IMMEDIATE parameter causes the log writer process (LGWR) to write the transaction's redo information to the log. 

This operation option forces a disk I/O and Oracle does the log writer operation for each commit. 

COMMIT_LOGGING=BATCH;
The BATCH parameter causes the redo to be buffered to the redo log, along with other concurrently executing transactions. 
When sufficient redo information is collected, a disk write of the redo log is initiated. 
This behavior is called "group commit", as redo for multiple transactions is written to the log in a single I/O operation.

This causes less traffic, but indices risk because an instance crash may cause loss of data that is being batched inside the log buffer.

===================
COMMIT_WAIT

===================
Options are WAIT and NOWAIT
WAIT is the default behavior.

This clause specify when control returns to the user.


COMMIT_WAIT=WAIT
The WAIT parameter ensures that the commit will return only after the corresponding redo is persistent in the online redo log. 
Whether in BATCH or IMMEDIATE mode, when the client receives a successful return from this COMMIT statement, the transaction has been committed to durable media. 

COMMIT_WAIT=NOWAIT
The NOWAIT parameter causes the commit to return to the client whether or not the write to the redo log has completed. 
This behavior can increase transaction throughput.
With NOWAIT, a crash occurring after the commit message is received, but before the redo log record(s) are written, 
Can falsely indicate to a transaction that its changes are persistent.

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

Syntax

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

ALTER SESSION | SYSTEM SET COMMIT_WAIT=NOWAIT;
ALTER SESSION | SYSTEM SET COMMIT_WAIT=WAIT;

ALTER SESSION | SYSTEM SET COMMIT_LOGGING=BATCH;
ALTER SESSION | SYSTEM SET COMMIT_LOGGING=IMMEDIETE;

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

Example

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

SQL> show parameter COMMIT_WAIT

NAME                    TYPE                  VALUE

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

commit_wait             string

SQL> ALTER SYSTEM SET COMMIT_WAIT=NOWAIT;

System altered.

SQL> show parameter COMMIT_WAIT

NAME                    TYPE                  VALUE

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

commit_wait             string                NOWAIT

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

Golden Gate

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

In Golden Gate Replicat:

REPLICAT SOME_REPLICAT

USERID xxxxxx, PASSWORD xxxxxx

INSERTALLRECORDS

DBOPTIONS INTEGRATEDPARAMS(parallelism 4)

SQLEXEC "alter session set commit_wait = 'NOWAIT'";

MAP....

TRUNCATE PARTITION by example

Example to Truncate a partition with indexes.
When doing Truncate to a partition, must do it with UPDATE GLOBAL INDEXES;

Code example:

SQL> SELECT index_name, status, global_stats 

FROM USER_INDEXES 

WHERE table_name = 'SFI_CUSTOMER_PROFILE';

INDEX_NAME                         STATUS   GLOBAL_STATS
---------------------------------- -------- ------------
SCP_MSISDN_IDX                     VALID    YES
SFI_CUSTOMER_PROFILE_PK            VALID    YES

Code example without UPDATE GLOBAL INDEXES:

ALTER TABLE SFI_CUSTOMER_PROFILE TRUNCATE PARTITION AFFILIATE_82;

SQL> SELECT index_name, status, global_stats 

FROM USER_INDEXES 

WHERE table_name = 'SFI_CUSTOMER_PROFILE';

 
INDEX_NAME                         STATUS   GLOBAL_STATS
---------------------------------- -------- ------------
SCP_MSISDN_IDX                     UNUSABLE YES
SFI_CUSTOMER_PROFILE_PK            UNUSABLE YES
 
ALTER INDEX SCP_MSISDN_IDX REBUILD ONLINE;
ALTER INDEX SFI_CUSTOMER_PROFILE_PK REBUILD ONLINE;

SQL> SELECT index_name, status, global_stats 

FROM USER_INDEXES 

WHERE table_name = 'SFI_CUSTOMER_PROFILE';

 

INDEX_NAME                         STATUS   GLOBAL_STATS
---------------------------------- -------- ------------
SCP_MSISDN_IDX                     VALID    YES
SFI_CUSTOMER_PROFILE_PK            VALID    YES

Code example with UPDATE GLOBAL INDEXES:
ALTER TABLE SFI_CUSTOMER_PROFILE TRUNCATE PARTITION AFFILIATE_82 UPDATE GLOBAL INDEXES;
 
Table truncated
 

SQL> SELECT index_name, status, global_stats 

FROM USER_INDEXES 

WHERE table_name = 'SFI_CUSTOMER_PROFILE';

 

INDEX_NAME                         STATUS   GLOBAL_STATS
---------------------------------- -------- ------------
SCP_MSISDN_IDX                     VALID    YES
SFI_CUSTOMER_PROFILE_PK            VALID    YES