Steps for Re-Instantiating Replication
- Clive Otten (Unlicensed)
The below article explains the steps by steps procedure to re-instantiate replication, when its broken.
Problem Details
Problem Statement
The replication may break due to various reasons , like missing archivelog, APPLY server crash etc.. in situations where we are not able to bring up the replication, best way to move forward is to re-instantiate.
Applicable Error Code
FATAL-9087: Could not find any valid archive or online redo log 123 (thread 1). See log file whether any files were rejected. ( We are not able to restore the archivelogs since its deleted).
Affected Versions
Any replicate versions , Provided steps for One Way Oracle-Oracle replication
Affected Platforms
Platform independent.
Description
We have a running replication setup, which is missing a archivelog. Below is the setup details.
Setup wizard is run for schema level replication with PL/SQL replication with the same schema. Replication name is
LIVE .while running setup wizard the schema had 2 tables with 1 procedure. Added three more tables after that and then one procedure. Below is the final list.
2. Source Schema name is VIJAY. TNS entry is ttorcl_src.
3. Target Schema name is VIJAY. TNS entry is ttorcl_trg.
4. Final list of objects in source and target.
SQL> col object_name format a20
SQL> /
OBJECT_NAME OBJECT_TYPE
-------------------- -------------------
RESULTS78 TABLE
TABLE8_PK INDEX
TABLE8 TABLE
TABLE7_PK INDEX
TABLE7 TABLE
DEPT TABLE
EMP TABLE
GEN_DATA PROCEDURE
CLONE PROCEDURE
Replication Status
\ Dbvisit Replicate 2.8.04_154_gf0659b1(MAX edition) - Evaluation License expires in 30 days
MINE is running. Currently at plog 822 and SCN 12716026 (05/05/2017 12:05:53).
APPLY is running. Currently at plog 822 and SCN 12716000 (05/05/2017 12:05:46).
Progress of replication LIVE:MINE->APPLY: total/this execution
--------------------------------------------------------------------------------------------------------------------------------------------
VIJAY.EMP: 100% Mine:1000/1000 Unrecov:0/0 Applied:1000/1000 Conflicts:0/0 Last:05/05/2017 11:41:52/OK
VIJAY.DEPT: 100% Mine:5/5 Unrecov:0/0 Applied:5/5 Conflicts:0/0 Last:05/05/2017 11:42:20/OK
VIJAY.TABLE7: 100% Mine:5837/5837 Unrecov:0/0 Applied:5837/5837 Conflicts:0/0 Last:05/05/2017 11:56:47/OK
VIJAY.TABLE8: 100% Mine:5820/5820 Unrecov:0/0 Applied:5820/5820 Conflicts:0/0 Last:05/05/2017 11:56:47/OK
VIJAY.RESULTS78: 100% Mine:11045/11045 Unrecov:0/0 Applied:11045/11045 Conflicts:0/0 Last:05/05/2017 11:56:47/OK
--------------------------------------------------------------------------------------------------------------------------------------------
Steps Performed
Below are the steps performed to re-initialise the replication.
Shutdown MINE and APPLY.
dbvrep> shutdown all
Upon restart MINE will go back to redolog 821 (thread 1), going back 1 log.
Upon restart APPLY will go back to plog 821, going back 1 plog.
Are you sure you want to shutdown? (Yes/No) [No] Yes
Dbvisit Replicate MINE process shutting down.
Dbvisit Replicate APPLY process shutting down.
dbvrep>
2. If you have any conflict handlers in your environment, check the below link and export the conflict handlers before running the ALL.sh script and import it later. The steps are provided in detail.
3. Run the all.sh script
4. Check the Nextsteps.txt file , you can ignore steps 1 to 5 , check the network link (if you are using impdp using the network link ).
5. Run the APPLY.sh , check the file you would see all the tables pertaining to the schema , for which replication is configured. Its a good practice to truncate the target tables, but the impdp does them as well with the parameter table_exists_action=TRUNCATE.
6. Check if the target has any foreign key constraints and triggers and disable them.
7. Start MINE and APPLY.
8. Check the console and replication is running fine.
9. The number of objects matches as well.