Hello,
In relation to the set up of the Oracle CTA DB, our DBAs have the following questions:
What priviledges the DB account needs to have? Steve kindly informed me that it should have the “CREATE SEQUENCE” privilege.
Do we need anything like create table or create procedure?
Is there any naming convention for username or what tablespaces are needed?
Many thanks,
George
Hi George,
You will need CREATE TABLE and CREATE INDEX — see CTA/catalogue/common_catalogue_schema.sql and oracle_catalogue_schema_header.sql for a complete list of SQL commands executed when you create the Catalogue.
You do not need CREATE PROCEDURE. CTA does not use PL/SQL, in order to make it easy to port to a different DB implementation besides Oracle.
Regards,
Michael
Hi Michael,
Thanks for this. Whenever we carry out the CASTOR to CTA migration, do I understand correct that we need to create a CASTOR DB in this Oracle instance (where the CTA Catalogue has been created) importing a snapshot of the CASTOR NS and VMGR DB schemas?
Best
George
Hi George,
Your understanding is correct. The CASTOR NS and VMGR database schemas must be hosted on the same database server as the CTA schema.
I have a question:
I am asking you this because during the lasts steps of a CASTOR to CTA migration, the CASTOR NS entries that were migrated are marked to indicate that they have been exported to CTA, likewise the tapes entries in the VMGR database are marked as EXPORTED.
After upgrading to the latest version of CASTOR the Cns_file_metadata and Cns_seg_metadata tables will contain onCTA columns. The value of these columns is set to 1 when a file and tape segment are marked as being exported to CTA.
Regards,
Steve
Hi Steve,
Thanks for the confirmation.
To answer your question, yes indeed we have a CASTOR preprod instance and DB that we will use for practising the migration procedure.
By the last thing you mentioned, I gather that upagrade of the source CASTOR DB to 2.1.19 is a pre-requisite after all for the migration to CTA. I remember you told me that this
might not be the case and that you are investingating.
Best,
George
Hi George,
It is true that I said we could find a way to use the old CASTOR NS schema version used at RAL. This would have included adding the extra columns required by the CASTOR to CTA migration tools.
We did not investigate working with RAL’s older version of CASTOR any further because Rob said he wanted to upgrade CASTOR anyway.
We can always try a few tricks if RAL cannot upgrade to the latest schema version of the CASTOR NS, though Rob did seem very motivated to succeed.
Here’s part of the upgrade that provides support for the CASTOR to CTA migration tools:
[itctabuild02] ~ > egrep -B2 'ALTER.*onCta' CASTOR/upgrades/*.sql
CASTOR/upgrades/cns_2.1.17-26_to_2.1.17-42.sql-/* Schema changes to support the export to CTA */
CASTOR/upgrades/cns_2.1.17-26_to_2.1.17-42.sql-
CASTOR/upgrades/cns_2.1.17-26_to_2.1.17-42.sql:ALTER TABLE Cns_file_metadata ADD (onCta INTEGER);
CASTOR/upgrades/cns_2.1.17-26_to_2.1.17-42.sql:ALTER TABLE Cns_seg_metadata ADD (onCta INTEGER);
[itctabuild02] ~ >
Cheers,
Steve
Hi George,
Please tell us if your snapshot will not be the latest CASTOR release. Then we’ll have to “upgrade” / “modify” the snapshot so that the migration scripts work during your tests.
Cheers,
Steve
Hi Steve,
Sorry for the delay. This is what I got from the DBAs
Schema PREPROD_CUPV of type CUPV is 2_1_16_18 (schema version: 2_1_9_3)
Schema PREPROD_NS of type CNS is 2_1_17_42 (schema version: 2_1_15_0)
Schema PREPROD_SRM3 of type SRM is 2_1_17_18 (schema version: 2_14_0)
Schema PREPROD_STAGER of type STAGER is 2_1_19_2_1 (schema version: 2_1_15_18)
Schema PREPROD_VDQM of type VDQM is 2_1_16_18 (schema version: 2_1_12_0)
Schema PREPROD_VMGR of type VMGR is 2_1_16_18 (schema version: 2_1_14_2)
which should be the latest version. Can you please clarify what does the term “synonyms” exactly mean in the note “the migration scripts rely on the CASTOR DB (source) and the CTA DB (target) to be located on the same Oracle instance. This is required for performance but also because the migration scripts rely on synonyms”
Best,
George
Hi George,
I am currently working on CASTOR, CTA and FTS. I did not write the migration code nor its documentation. I can help you, but if you could please include the URL of the documentation with which you are having problems that would be of great help to me.
I am guessing that the documentation is referring to Oracle database synonyms and more specifically these ones:
[itctabuild02] ~ > grep -i synonym CTA/migration/./oracle_catalogue_castor_migration.sql
-- Create synonyms for all relevant entities
CREATE OR REPLACE SYNONYM CNS_CTAFilesHelper FOR &castornsSchema..CTAFilesHelper;
CREATE OR REPLACE SYNONYM CNS_CTAFiles2ndCopyHelper FOR &castornsSchema..CTAFiles2ndCopyHelper;
CREATE OR REPLACE SYNONYM CNS_CTADirsHelper FOR &castornsSchema..CTADirsHelper;
CREATE OR REPLACE SYNONYM CNS_CTAMigrationLog FOR &castornsSchema..CTAMigrationLog;
CREATE OR REPLACE SYNONYM CNS_Class_Metadata FOR &castornsSchema..Cns_class_metadata;
CREATE OR REPLACE SYNONYM CNS_Dirs_Full_Path FOR &castornsSchema..Dirs_Full_Path;
CREATE OR REPLACE SYNONYM CNS_filesForCTAExport FOR &castornsSchema..filesForCTAExport;
CREATE OR REPLACE SYNONYM CNS_zeroByteFilesForCTAExport FOR &castornsSchema..zeroByteFilesForCTAExport;
CREATE OR REPLACE SYNONYM CNS_dirsForCTAExport FOR &castornsSchema..dirsForCTAExport;
CREATE OR REPLACE SYNONYM CNS_ctaLog FOR &castornsSchema..ctaLog;
CREATE OR REPLACE SYNONYM CNS_getTime FOR &castornsSchema..getTime;
CREATE OR REPLACE SYNONYM Vmgr_tape_side FOR &vmgrSchema..Vmgr_tape_side;
CREATE OR REPLACE SYNONYM vmgr_tape_info FOR &vmgrSchema..Vmgr_tape_info;
CREATE OR REPLACE SYNONYM Vmgr_tape_dgnmap FOR &vmgrSchema..Vmgr_tape_dgnmap;
[itctabuild02] ~ >
Regards,
Steve
Hi Steve,
Thanks for this. The note I quoted was from a mitgration tutorial Teleworking Tips & Tricks CERN - CodiMD that Michael has also sent as a txt file.
I am also looking at the more updated procedure here
https://eoscta.docs.cern.ch/gitlab/migration/
Regards
George
Hi George,
Many thanks for the link! This is of course not your fault/problem, but I am context switching a lot. It just helps me attack your problem quicker.
Again, many thanks.
Regards,
Steve
Hi Steve,
Thanks for this. It sounds like SYNONYM is a standard DB term. If you could give me a one line explanation of it that would be great.
In case this is at all relevant, we take a snapshot of three CASTOR DB schemas
PREPROD_VMGR
PREPROD_STAGER
PREPROD_NS
and import them to the Oracle DB hosting the CTA schema. Will the three sql scripts
castorvmgr_ctamigration_schema.sql
castorns_ctamigration_schema.sql
oracle_catalogue_castor_migration.sql
work as they are (i.e. without modfication)?
George
Hi George,
An Oracle SYNONYM is a user defined name that is expanded to a schema name plus a table name (or sequence, view, procedure, etc …) which avoids users of the database having to remember which table belongs to which schema.
I have just read through the following three scripts and have found no requirement on you to make any modifications:
Cheers,
Steve
Hi Steve,
Thanks for this. I can see in migration/castor/castorns_ctamigration_schema.sql · master · cta / CTA · GitLab instances of the ‘/castor/cern.ch/’ string. Do we need to replace it in the sql script with our castor prefix of choice? I note that a castor.prefix is already defined in the /etc/cta/castor-migration.conf as
castor.prefix /castor/preprod.ral # will be stripped from the front of CASTOR paths
Best,
George
Hi George,
You are unfortunately correct. You will have to replace cern.ch by the RAL equivalent. This said, Oliver has spotted another problem. The migration script determines the Virtual Organization (VO) of a file by assuming the VO is located in the full path of the file either here /castor/cern.ch/VO or here /castor.cern.ch/grid/VO. I have no idea what your full path names are like at RAL.
At this point we are entering into the realm of the migration experts who are Michael and Giuseppe, both of which are on holiday. Michael will be back on Thursday 5th August and Giuseppe will be back tomorrow. I am already assuming that you are working with clones of your production databases and any mistakes that you may make will not harm production in any way. If you are in no danger of messing with production then please continue else waiting for Michael and Giuseppe would in my opinion be wiser.
Regards,
Steve
Hi Steve,
Thanks again. We will see what we can do until Michael and Giuseppe are back. You are right in your assumption: we are indeed working with copies of CASTOR DB schemas. And they are not even from a production DB (but from a preprod instance) so we can be as agressive as we want!
Our production namespace (which we dont touch as mentioned) does follow a simllar patternlike yours: /castor/ads.rl.ac.uk/prod/voname/…
but our preprod namespace is quite different (and much simpler)
nsls -l /castor/preprod.ral/
drwxrwxrwx 8 root root 0 Jan 10 2020 preprodDisk
drwxr-xr-x 6 root root 0 Jun 18 2020 preprodTape
drwxr-xr-x 3 root root 0 Nov 28 2018 preprodTape2
drw-r–r-- 1 root root 0 Oct 21 2016 preprodToBeMerged
drwxrwxrwx 5 dteam001 dteam 0 Apr 30 2020 test
Maybe we can get away with some kind of “mock” VO.
Best,
George
Hi Steve, Giuseppe
I replaced the '‘cern.ch’ with ‘preprod.ral’ in the castorns_ctamigration_schema.sql but I get some errors when I try to run it the first time
FROM Cns_file_metadata F, Dirs_Full_Path D
*
ERROR at line 6:
ORA-00942: table or view does not exist
…
ALTER TABLE Dirs_Full_Path PARALLEL
*
ERROR at line 1:
ORA-00942: table or view does not exist
when I tried for a second time, I get a lot of
ORA-00955: name is already used by an existing object
Best,
George