OCI Error ORA-00001: unique constraint violated
The replicate will be in a a banded state and when you get this error in the gg replicate then you have to check the data from the source table and the target table...
Some of the data which is present in the source table is already present in the target table
Some of the data which is present in the source table is already present in the target table
we need to check the records from two table on which the error is produced there is a solution for this ... by adding the rep parameter on the replicate parameter file
Step1
Once the process is getting abandoned we need to check the report file of the particular process
GGSCI (fahdb.) 2> view report TRANR1
and find the details of the error in the report file
2012-08-26 14:55:33 WARNING OGG-01004 Aborted grouped transaction on 'table_name', Database error 1 (OCI Error ORA-00001: unique constraint (constraint _name) violated (status = 1), <sql statment>
2012-08-26 14:55:33 WARNING OGG-01003 Repositioning to rba 846885 in seqno 150
Step 2
Conform from the source and the target side whether the records which is present in the source is present in the target and the source is trying to send the record which is already present in the target table,,, this cause's the ORA-00001 .. the unique constraint of the target table is violated because the data is already present in the table but the source is trying to insert the same record...
Query both the tables involved in and verify the record
Step 3
There are two ways to rectify the error we can do it globally of all the tables are we can do it only for the particular table
here we followed the global strategy ... if your replicate and the extract using more then one table and causing the error .. it is wise to follow the global strategy
By add in the parameter in the parameter file we can avoid this error and we can create the discard file to hold the discarded data[ if the data which is already present in the Target then that data will be moved into the discard file]
Step4
We need to add the parameter in the extract or replicate's parameter file
Go to the root directory of the gg and the navigate to dirprm which hold's all the parameter file's for the process
[oracle@fahdb dirprm]$ pwd
/u01/app/oracle/product/gg/dirprm
[oracle@fahdb dirprm]$ ls
defgen.prm mgr.prm setupi1.prm trane.prm tranr1.prm
setupe.prm setupp1.prm tranp1.prm
Parameter with discard file
ADD THE PARAMETER REPERROR (1, DISCARD)
Advantage of creating the discard file is we can check the duplicate record's which are tried to insert in to the target table all the duplicate records will be stored in the discard file
Parameter with out discard file
ADD THE PARAMETER REPERROR (1)
once the parameter is added we can start the replicate if the replicate start's fine and check the discard file and wait until the all the trail files are processed .. once all the trail files are processed the yo can comment out the parameter if it is not commented out also there is no harm in it
while starting the replicate if this kind of error appear's then we need to increase the discard file size to hold all the record's
ERROR OGG-01172 Discard file (./dirrpt/TRANR1.DSC) exceeded max bytes (1000000).
INCREASING THE DISCARD FILE SIZE
Step1
Go to the particular parameter file and add to the below parameter in the file
Here we are transferring the data two source table's to two target table's
Check the report file you can view this king of error message in it
2012-08-26 18:04:22 ERROR OGG-01172 Discard file (./dirrpt/TRANR1.DSC) exceeded max bytes (1000000).
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Reading ./dirdat/t1000150, current RBA 1325874, 37 records
Report at 2012-08-26 18:04:22 (activity since 2012-08-26 18:04:21)
From Table <source_table>to <target_table>: -- table 1 from source and target
# inserts: 0
# updates: 0
# deletes: 0
# discards: 275
From Table <source_table>to <target_table>:-- table 2 from source and target
# inserts: 37
# updates: 0
# deletes: 0
# discards: 0
Stored procedure fusion.gl_remote_transfer_pkg.do_import:
attempts: 37
successful: 37
From Table <source_table>to <target_table>:
Stored procedure tt3:
attempts: 37
successful: 37
Adding the discard file with the specific value
DISCARDFILE ./dirrpt/TRANR1.DSC, APPEND, MEGABYTES 100
DISCARDFILE -keyword
./dirrpt/TRANR1.DSC -File location
APPEND - it will append the discard file when the discard file contains already data in it
MEGABYTES <value> -estimate the volume of the duplicate data and according to ti we should give the size if you want to
Size in GB you have to specify the Parameter GIGABYTE [GB]
Adding the discard file with the PURGE
DISCARDFILE ./dirrpt/TRANR1.DSC, PURGE
if we add the discard file with the purge command then when ever you restart the replicate or extract the previous data which is residing in the discard file will be purged
Both the parameter's can't be added at the same time .. we have to choose nay one of the option's
Step 2
Start the replicate ...
Check the report file for how many discard's had happened
Reading ./dirdat/t1000151, current RBA 2111708, 2183 records
Report at 2012-08-26 23:27:51 (activity since 2012-08-26 18:09:41)
From Table <source_table>to <target_table>: -- table 1 from source and target
# inserts: 2133
# updates: 0
# deletes: 0
# discards: 364
From Table <source_table>to <target_table>:-- table 2 from source and target
# inserts: 82
# updates: 0
# deletes: 0
# discards: 0
Stored procedure fusion.gl_remote_transfer_pkg.do_import:
attempts: 125
successful: 125
From Table GL.GL_XFR_TRACKING to FUSION.GL_XFR_TRACKING:
Stored procedure tt3:
attempts: 125
successful: 125
Now check the discard file which is created on the location which have been specified in the parameter
[oracle@fahdb dirrpt]$ ll *.DSC
-rw-r--r-- 1 oracle oinstall 3642373 Aug 26 23:33 TRANR1.DSC
No comments:
Post a Comment