How to Resync Tables and Schemas to re-instantiate OGG replicat process :-
Stop the Replicat process in the target environment if it is running by using the following command:
GGSCI> STOP REPLICAT RPOMPOTC
Truncate the tables in the target database that you are replicating using the following commands:
SQL> TRUNCATE TABLE KKANNAPPAN.EMP;
SQL> TRUNCATE TABLE KKANNAPPAN.DEPT;
SQL> TRUNCATE TABLE KKANNAPPAN.SALGRADE;
SQL> TRUNCATE TABLE KKANNAPPAN.BONUS;
Note down the current timestamp from the source database:
SQL> SELECT TO_CHAR(SYSDATE,’YYYY-MM-DD HH24:MI:SS’) from dual;
TO_CHAR(SYSDATE,’YY
——————-
2020-02-26 15:50:02
Note down the current_scn from the source database as follows:
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
———–
11547294
Create a /u01/app/ggate/expdp directory for taking a Datapump Export on both the servers, as follows:
mkdir /u01/app/ggate/expdp
Create a directory object in the source database as follows:
SQL> CREATE DIRECTORY EXPIMP as ‘/u01/app/ggate/expdp’;
Directory created.
SQL> GRANT READ, WRITE, EXECUTE on Directory EXPIMP to system;
Grant succeeded.
Run Datapump Export to backup the tables that we are replicating:
[oracle@prodora ggate]$ expdp userid=system schemas=KKANNAPPAN FLASHBACK_SCN=11547294 DIRECTORY=EXPIMP DUMPFILE=EXPDP.dmp LOGFILE=EXPDP.log
Export: Release 12.2.0.1.0 – Production on Tue Feb 26 16:03:39 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″: userid=system/******** schemas=KKANNAPPAN FLASHBACK_SCN=11547294 DIRECTORY=EXPIMP DUMPFILE=EXPDP.dmp LOGFILE=EXPDP.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
exported “KKANNAPPAN”.”DEPT” 5.929 KB 4 rows
exported “KKANNAPPAN”.”EMP” 8.648 KB 16 rows
exported “KKANNAPPAN”.”SALGRADE” 5.859 KB 5 rows
exported “KKANNAPPAN”.”BONUS” 0 KB 0 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
*********************************************************Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/ggate/expdp/EXPDP.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 16:05:08
Create a directory object in the database in the target database:
SQL> CREATE DIRECTORY EXPIMP AS ‘/u01/app/ggate/expdp’;
Directory created.
SQL> GRANT READ,WRITE,EXECUTE ON DIRECTORY EXPIMP TO SYSTEM;
Grant succeeded.
Copy the dump file from the source to the target server as follows:
scp /u01/app/ggate/expdp/expdp.dmp repora:/u01/app/ggate/expdp/
Import the dump file into the target database as follows:
[oracle@repora expdp]$ impdp userid=system directory=expimp dumpfile=EXPDP.dmp logfile=IMPDP.dmp full=y table_exists_action=REPLACE
Import: Release 12.2.0.1.0 – Production on Tue Feb 26 16:20:51 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: userid=system/******** directory=expimp dumpfile=EXPDP.dmp logfile=IMPDP.dmp full=y table_exists_action=REPLACE
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”KKANNAPPAN” already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
imported “KKANNAPPAN”.”DEPT” 5.929 KB 4 rows
imported “KKANNAPPAN”.”EMP” 8.648 KB 16 rows
imported “KKANNAPPAN”.”SALGRADE” 5.859 KB 5 rows
imported “KKANNAPPAN”.”BONUS” 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYSTEM”.”SYS_IMPORT_FULL_01″ completed with 1 error(s) at 16:21:05
Alter the Extract and Datapump on the source server as follows:
GGSCI> ALTER EXTRACT EGGTEST1, BEGIN 2020-02-26 15:50:02
GGSCI> ALTER EXTRACT PGGTEST1, BEGIN 2020-02-26 15:50:02
Remove all the trail files from the dirdat directory on the source server except the last generated trail file.
Start Extract and Datapump on server A as follows:
GGSCI> START EXTRACT EGGTEST1
GGSCI> START EXTRACT PGGTEST1
Start the Replicat process on server B from the SCN noted in step 4 as follows:
GGSCI> START REPLICAT RPOMPOTC, AFTERCSN 11547294
Check whether the data is getting replicated on both sides by running stats or not as follows:
GGSCI (prodora.localdomain) 3> STATS EGGTEST1, TOTAL, TABLE KKANNAPPAN.*
Sending STATS request to EXTRACT EGGTEST1 …
Start of Statistics at 2020-02-26 22:34:00.
Output to /u01/app/ggate/dirdat/st:
Extracting from KKANNAPPAN.EMP to KKANNAPPAN.EMP:
*** Total statistics since 2020-02-26 22:01:04 ***
Total inserts 0.00
Total updates 16.00
Total deletes 0.00
Total discards 0.00
Total operations 16.00
End of Statistics.
GGSCI (prodora.localdomain) 4> STATS PGGTEST1, TOTAL, TABLE KKANNAPPAN.*
Sending STATS request to EXTRACT PGGTEST1 …
Start of Statistics at 2020-02-26 22:34:18.
Output to /u01/app/ggate/dirdat/rt:
Extracting from KKANNAPPAN.EMP to KKANNAPPAN.EMP:
*** Total statistics since 2020-02-26 22:01:05 ***
Total inserts 0.00
Total updates 16.00
Total deletes 0.00
Total discards 0.00
Total operations 16.00
End of Statistics.
GGSCI (repora.localdomain) 8> STATS RPOMPOTC, TOTAL, TABLE KKANNAPPAN.*
Sending STATS request to REPLICAT RPOMPOTC …
Start of Statistics at 2020-02-26 22:36:54.
Replicating from KKANNAPPAN.EMP to KKANNAPPAN.EMP:
*** Total statistics since 2020-02-26 22:09:22 ***
Total inserts 0.00
Total updates 16.00
Total deletes 0.00
Total discards 0.00
Total operations 16.00
End of Statistics.
