Problem solving log is here

Dear Igor,

sorry for the late answer, I still have a few holidays, so I can read e-mail
only at some times in the day.
Not looking directly at your DB, but having to "hypothesize", I believe that
you are copying data using the OPERAPUB account, but OPERA (or some other
account). If so, this is the opposite of what I always recommend. OPERA is
an administrative account and should be used as such, not for common tasks.
The explanation for this hypothesis is the following:
IDs in TB_PROGRAMSETTINGS are automatically generated by Oracle using proper
triggers, as is the case for many other tables (as documented in the DB
documentation). This happens every time you INSERT rows there by using an
account that is not a registered publisher (as OPERAPUB is). Then, you
should ensure that:
1) you are logged on as OPERAPUB when you copy/publish data.
2) OPERAPUB is listed in LZ_PUBLISHERS (you can see this from the OPERA
account)
In order to fix your inconsistency, first try this:

update tb_programsettings a set id = (select id from
tb_programsettings@opita b where a.description = b.description)

This statement WILL NOT work if you have process operations attached to
these program settings. If this is the case, we have to try a three-step
fixup, by first disabling all constraints, then correcting them, finally
re-enabling the constraints.
Best regards

Cristiano



----- Original Message -----
From: "Igor Kreslo" <Igor.Kreslo@cern.ch>
To: "Cristiano Bozza" <kryss@sa.infn.it>
Sent: Sunday, August 24, 2008 10:26 PM
Subject: RE: Contact


Cristiano, it seems, that our DB didn't conserve programsettings ID from
OPITA, but generated it's own.
So now there are 101 clashes.
How this could happen and what I can do to fix it?
Regards,
Igor.


-----Original Message-----
From: Cristiano Bozza [mailto:kryss@sa.infn.it]
Sent: Sun 8/24/2008 9:25 PM
To: Igor Kreslo
Subject: Re: Contact

Dear Igor,

the primary key for tb_programsettings is not the Id but the Description.
Hence, if you did the following, you have the clash:
1) delete a local programsetting
2) create a new programsetting (so with a different ID)
3) use for the new programsetting the same description as for an old one,
published in OPITA
You can easily discover this:

select a.id, a.description, b.id, b.description from
(select id, description from tb_programsettings) a
inner join
(select id, description from tb_programsettings@opita) b
on (a.description = b.description and a.id <> b.id)

To solve the clash, change the description for the local programsetting that
clashes with the remote one.
Best regards

Cristiano



----- Original Message -----
From: "Igor Kreslo" <Igor.Kreslo@cern.ch>
To: "Cristiano Bozza" <kryss@sa.infn.it>
Sent: Saturday, August 23, 2008 12:17 PM
Subject: RE: Contact


Dear Cristiano,
something strange..

When trying to syncronize programsettings:

SQL> insert into tb_programsettings (select * from
tb_programsettings@OPITA01 a where not exists (select * from
tb_programsettings b where a.id=b.id));
insert into tb_programsettings (select * from tb_programsettings@OPITA01 a
where not exists (select * from tb_programsettings b where a.id=b.id))
*
ERROR at line 1:
ORA-00001: unique constraint (OPERA.PK_PROGRAMSETTINGS) violated


Content of tables:


Remote:
SQL> select id_programsettings from tb_proc_operations@OPITA01 where
id_eventbrick=3088929;

  ID_PROGRAMSETTINGS
--------------------
    9000000021003522
    9000000021003519
    9000000021003519
    9000000000044123
    9000000023703562

Local:
SQL> select id from tb_programsettings where id=9000000021003522;

no rows selected

SQL> select id from tb_programsettings where id=9000000021003519;

no rows selected

SQL> select id from tb_programsettings where id=9000000000044123;

                  ID
--------------------
    9000000000044123

SQL> select id from tb_programsettings where id=9000000023703562;

no rows selected


Settings 9000000000044123 have child operations, so can't be deleted.
What can I do to solve this clash?

This happened first time, for other four bricks there were no problem!
Maybe data in OPITA01 for this brick is corrupted?

Regards,
Igor.




-----Original Message-----
From: Cristiano Bozza [mailto:kryss@sa.infn.it]
Sent: Fri 8/22/2008 9:28 PM
To: Igor Kreslo
Subject: Re: Contact

Dear Igor,

FK_PROGSETS_PROCOPS ties process operations and program settings. Making
predictions, or scanning CS, are both operations, and they have settings.
You should PP_COPY_SYSTEM from OperaPublicationManager, or, alternatively,
update your
TB_SITES
TB_USERS
TB_MACHINES
TB_PROGRAMSETTINGS
(in the order) with all the rows that are in OPITA and that you don't have
in your local DB.
Something like:

insert into tb_sites (select * from tb_sites@opita a where not exists
(select * from tb_sites b where a.id = b.id));
insert into tb_machines (select * from tb_machines@opita a where not exists
(select * from tb_machines  b where a.id = b.id));
insert into tb_users (select * from tb_users@opita a where not exists
(select * from tb_users  b where a.id = b.id));
insert into tb_programsettings (select * from tb_programsettings@opita a
where not exists (select * from tb_programsettings  b where a.id = b.id));

Best regards

Cristiano



----- Original Message -----
From: "Igor Kreslo" <Igor.Kreslo@cern.ch>
To: "Cristiano Bozza" <kryss@sa.infn.it>
Sent: venerdì 22 agosto 2008 17.57.14 (GMT+0100) Europe/Berlin
Subject: RE: Contact

Dear Cristiano,
another problem appeared.
During attempt to downbload CS predictions:

Error starting at line 1 in command:
call pp_download_predictions(3088929,'OPITA01')
Error report:
SQL Error: ORA-02291: integrity constraint (OPERA.FK_PROGSETS_PROCOPS)
violated - parent key not found
ORA-06512: at "OPERAPUB.PP_DOWNLOAD_PREDICTIONS", line 39
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not
found"
*Cause:    A foreign key value has no matching primary key value.
*Action:   Delete the foreign key or add a matching primary key.

Never happened before. What can be wrong?
Best regards,
Igor.

________________________________

From: Cristiano Bozza [mailto:kryss@sa.infn.it]
Sent: Fri 8/22/2008 9:34 AM
To: Igor Kreslo
Subject: Contact



Dear Igor,

you can find me today till 12:30 at +39 089965344 (Lab).
Best regards

 Reply 	Reply 	Reply to all 	Reply to all 	Forward 	Forward 		Move 	Copy 	Delete 		Previous Item 	Next Item 		Close 			 Help 	 
 From:  	 Cristiano Bozza [kryss@sa.infn.it] 	 Sent:  	 Mon 8/25/2008 11:48 PM
 To:  	 Igor Kreslo
 Cc:  	 
 Subject:  	 Re: Contact
 Attachments:  	
View As Web Page


Cristiano

Then I realized that there are duplicate Descriptions of Programsettings already in OPITA01, so probably
the constraint PK_PROGRAMSETTINGS is already disabled!!
So I disabled it in the local DB.

After  that:
 SQL> insert into tb_programsettings (select * from
 2  tb_programsettings@OPITA01 a where not exists (select * from
 3  tb_programsettings b where a.id=b.id));
 157 rows created.
 commit;

After that 
call pp_download_predictions(88929,'OPITA01'); 
worked well;