oracle_catalogue_castor_migration.sql 17.8 KB
Newer Older
1
/*****************************************************************************
2
 *              oracle_catalogue_castor_migration.sql
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 *
 * This file is part of the Castor/CTA project.
 * See http://cern.ch/castor and http://cern.ch/eoscta
 * Copyright (C) 2019  CERN
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 3
 * of the License, or (at your option) any later version.
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, see <http://www.gnu.org/licenses/>.
 *
 * This script adds the necessary PL/SQL code to an existing CTA Catalogue
 * schema in order to support the metadata migration from CASTOR.
 *
 * @author Castor Dev team, castor-dev@cern.ch
 *****************************************************************************/
24

25
26
27
28
-- Create synonyms for all relevant entities
UNDEF castornsSchema
ACCEPT castornsSchema CHAR DEFAULT 'castor' PROMPT 'Enter the name of the CASTOR Nameserver schema (default castor): ';
CREATE OR REPLACE SYNONYM CNS_CTAFilesHelper FOR &castornsSchema..CTAFilesHelper;
29
CREATE OR REPLACE SYNONYM CNS_CTAFiles2ndCopyHelper FOR &castornsSchema..CTAFiles2ndCopyHelper;
30
31
CREATE OR REPLACE SYNONYM CNS_CTADirsHelper FOR &castornsSchema..CTADirsHelper;
CREATE OR REPLACE SYNONYM CNS_CTAMigrationLog FOR &castornsSchema..CTAMigrationLog;
32
33
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;
34

35
36
37
CREATE OR REPLACE SYNONYM CNS_filesForCTAExport FOR &castornsSchema..filesForCTAExport;
CREATE OR REPLACE SYNONYM CNS_dirsForCTAExport FOR &castornsSchema..dirsForCTAExport;
CREATE OR REPLACE SYNONYM CNS_ctaLog FOR &castornsSchema..ctaLog;
38
39
40
41
42
43

UNDEF vmgrSchema
ACCEPT vmgrSchema CHAR DEFAULT 'vmgr' PROMPT 'Enter the name of the VMGR schema (default vmgr): ';
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;
44

45
46
-- Used by removeCASTORMetadata
CREATE OR REPLACE TYPE NUMLIST IS TABLE OF INTEGER;
47
48
CREATE GLOBAL TEMPORARY TABLE Temp_Remove_CASTOR_Metadata (archive_file_id INTEGER PRIMARY KEY)
ON COMMIT DELETE ROWS;
49

50
51
52
-- Enable parallel inserts
ALTER TABLE Archive_File PARALLEL;
ALTER TABLE Tape_File PARALLEL;
53
54


55
56
57
-- Import a tapepool and its tapes from CASTOR
-- Raises constraint_violation if the tapepool and/or some tapes were already imported
CREATE OR REPLACE PROCEDURE importTapePool(inTapePool VARCHAR2, inVO VARCHAR2) AS
58
59
60
  varUnused VARCHAR2(100);
  CONSTRAINT_VIOLATED EXCEPTION;
  PRAGMA EXCEPTION_INIT(CONSTRAINT_VIOLATED, -1);
61
  varTapePoolId Tape_Pool.tape_pool_id%TYPE := 0;
62
BEGIN
63
64
65
  FOR DGN IN (SELECT dgn FROM Vmgr_tape_dgnmap) LOOP
    -- The very first time insert the logical libraries found on VMGR
    BEGIN
66
      INSERT INTO Logical_Library (logical_library_id, logical_library_name, user_comment,
67
68
69
        creation_log_user_name, creation_log_host_name, creation_log_time,
        last_update_user_name, last_update_host_name, last_update_time)
      VALUES (
70
        Logical_Library_id_seq.NEXTVAL,
71
72
73
74
75
76
77
78
79
        DGN.dgn,
        'Imported from CASTOR',
        'CASTOR', 'CASTOR', getTime(),
        'CASTOR', 'CASTOR', getTime()
        );
    EXCEPTION WHEN CONSTRAINT_VIOLATED THEN
      NULL;   -- it was already present, skip
    END;
  END LOOP;
80

81
  BEGIN
82
    SELECT Tape_Pool_id_seq.NEXTVAL INTO varTapePoolId FROM Dual;
83
84
85
86
87
    INSERT INTO Tape_Pool (tape_pool_id, tape_pool_name, vo, nb_partial_tapes, is_encrypted, user_comment,
      creation_log_user_name, creation_log_host_name, creation_log_time, last_update_user_name,
      last_update_host_name, last_update_time)
    VALUES (
      varTapePoolId,
Giuseppe Lo Presti's avatar
Giuseppe Lo Presti committed
88
      inTapePool,
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
      inVO,
      0,    -- nb_partial_tapes, to be filled afterwards
      '0',  -- is_encrypted is assumed false in CASTOR
      'Imported from CASTOR',
      'CASTOR', 'CASTOR', getTime(),
      'CASTOR', 'CASTOR', getTime()
      );
  EXCEPTION WHEN CONSTRAINT_VIOLATED THEN
    -- The TapePool is already present, typically because of a previous import: override some values
    UPDATE Tape_Pool SET
           vo = inVO,
           user_comment = 'Re-imported from CASTOR',
           last_update_user_name = 'CASTOR',
           last_update_host_name = 'CASTOR',
           last_update_time = getTime()
104
105
     WHERE tape_pool_name = inTapePool
    RETURNING tape_pool_id INTO varTapePoolId;
106
  END;
107
108
109
110
111
112
  FOR T in (SELECT TI.vid, TI.density, TI.manufacturer, DGN.dgn, TS.status, TS.nbfiles,
                   TI.rcount, TI.wcount, TI.rhost, TI.whost, TI.rtime, TI.wtime
              FROM Vmgr_tape_info TI, Vmgr_tape_side TS, Vmgr_tape_dgnmap DGN
             WHERE TI.vid = TS.vid
               AND TI.library = DGN.library
               AND TI.model = DGN.model
113
               AND BITAND(TS.status, 2) = 0 AND BITAND(TS.status, 32) = 0   -- not already EXPORTED or ARCHIVED
114
               AND TS.poolname = inTapePool) LOOP
115
    INSERT INTO Tape (vid, media_type, vendor, logical_library_id, tape_pool_id,
116
      encryption_key_name, capacity_in_bytes, data_in_bytes, last_fseq,
117
      is_disabled, is_full, is_read_only, is_archived, is_exported, is_from_castor,
118
119
      label_drive, label_time, last_read_drive, last_read_time, read_mount_count,
      last_write_drive, last_write_time, write_mount_count,
120
      user_comment, creation_log_user_name, creation_log_host_name, creation_log_time,
121
122
      last_update_user_name, last_update_host_name, last_update_time)
    VALUES (
123
      T.vid,
124
      decode(T.density,    -- media type: only one of the options below (see #488)
125
126
127
128
129
130
131
        '7000GC', '3592JC',
        '8000GC', '3592JC',
        '9TC',    'LTO-7M',
        '10TC',   '3592JD',
        '12TC',   'LTO-8',
        '15TC',   '3592JD',
        'UNDEFINED'),
132
      T.manufacturer,
133
      (SELECT logical_library_id FROM Logical_Library WHERE logical_library_name = T.dgn),
134
      varTapePoolId,
135
      NULL,                -- empty encryption key name
136
      decode(T.density,    -- capacity: only one of the options below (see #488)
137
138
139
140
141
142
143
        '7000GC',  7000000000000,
        '8000GC',  8000000000000,
        '9TC',     9000000000000,
        '10TC',   10000000000000,
        '12TC',   12000000000000,
        '15TC',   15000000000000,
        0),
144
      0,      -- total data: will be filled by populateCTAFilesFromCASTOR()
145
      T.nbfiles,
146
147
148
      decode(BITAND(T.status, 1), 1, '1', '0'),    -- DISABLED flag
      decode(BITAND(T.status, 8), 8, '1', '0'),    -- FULL flag
      decode(BITAND(T.status, 16), 16, '1', '0'),  -- RDONLY flag
149
150
      '0',                                         -- ARCHIVED flag
      '0',                                         -- EXPORTED flag
151
152
      '1',                                         -- is_from_castor flag
      'CASTOR', 0,                  -- label drive and time (unknown)
153
154
      T.rhost, T.rtime, T.rcount,   -- last read drive/time and count
      T.whost, T.wtime, T.wcount,   -- last write drive/time and count
155
      'Imported from CASTOR',
156
157
      'CASTOR', 'CASTOR', getTime(),
      'CASTOR', 'CASTOR', getTime()
158
159
160
      );
  END LOOP;
  COMMIT;
161
  CNS_ctaLog(inTapePool, 'Tapes import completed');
162
163
END;
/
164
165


166
-- Insert the file-level metadata for the given migration
167
CREATE OR REPLACE PROCEDURE populateCTAFilesFromCASTOR(inEOSCTAInstance VARCHAR2, inTapePool VARCHAR2) AS
168
169
  CONSTRAINT_VIOLATED EXCEPTION;
  PRAGMA EXCEPTION_INIT(CONSTRAINT_VIOLATED, -1);
170
  varLenCastorPrefix INTEGER := length('/castor/cern.ch/');
Giuseppe Lo Presti's avatar
Giuseppe Lo Presti committed
171
  varNamespacePrefix VARCHAR2(20);
172
173
174
  varIs2ndCopy INTEGER;
  nbPreviousErrors INTEGER;
  nbMissingImports INTEGER;
175
BEGIN
176
  -- Populate storage classes metadata if missing: do one by one and handle constraint violations
177
178
179
180
  FOR c IN (SELECT classid, name classname, nbcopies
              FROM CNS_Class_Metadata
             WHERE classid IN
              (SELECT classid FROM CNS_CTAFilesHelper)) LOOP
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
    BEGIN
      INSERT INTO Storage_Class (storage_class_id, disk_instance_name,
        storage_class_name, nb_copies, user_comment,
        creation_log_user_name, creation_log_host_name, creation_log_time,
        last_update_user_name, last_update_host_name, last_update_time)
      VALUES (
        c.classid,
        inEOSCTAInstance,
        c.classname,
        c.nbcopies,
        'Imported from CASTOR',
        'CASTOR', 'CASTOR', getTime(),
        'CASTOR', 'CASTOR', getTime()
        );
    EXCEPTION WHEN CONSTRAINT_VIOLATED THEN
196
      NULL;   -- it was already present, skip ASSUMING it's the correct one!
197
198
199
    END;
  END LOOP;
  COMMIT;
200
  CNS_ctaLog(inTapePool, 'Storage classes import completed');
201

202
203
204
205
206
207
208
  -- Populate the CTA catalogue with the CASTOR file metadata
  IF instr(inEOSCTAInstance, 'eoscta') = 1 THEN
    varNamespacePrefix := substr(inEOSCTAInstance, 7);
  ELSIF instr(inEOSCTAInstance, 'eos') = 1 THEN
    varNamespacePrefix := substr(inEOSCTAInstance, 4);
  ELSE
    varNamespacePrefix := inEOSCTAInstance;
Giuseppe Lo Presti's avatar
Giuseppe Lo Presti committed
209
  END IF;
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
  INSERT /*+ APPEND PARALLEL(Archive_File) */ INTO Archive_File
    (archive_file_id, disk_instance_name, disk_file_id, disk_file_path,
    disk_file_uid, disk_file_gid, size_in_bytes, checksum_adler32,
    storage_class_id, creation_time, reconciliation_time, is_deleted) (
    SELECT /*+ PARALLEL(F) PARALLEL(D) */
          F.fileid, inEOSCTAInstance, F.fileid,
          '/eos/' || varNamespacePrefix
          || substr(D.path, varLenCastorPrefix) || '/' ||  F.filename,
          F.disk_uid, F.disk_gid, F.filesize, F.checksum, F.classid, F.btime, 0, '0'
      FROM CNS_CTAFilesHelper F, CNS_Dirs_Full_Path D
    WHERE F.parent_fileid = D.fileid
          -- no need to exclude already existing files because of dual copies
          -- as this is validated before hand in the cns_filesForCTAExport procedure
    );
  COMMIT;
  CNS_ctaLog(inTapePool, 'Archive files import completed');
226

227
  -- Import the tape file metadata: here we also store the constraint_violation
228
  -- errors for later manual analysis
229
  SELECT COUNT(*) INTO nbPreviousErrors FROM Err$_Tape_File;
230
  INSERT /*+ APPEND PARALLEL(Tape_File) */ INTO Tape_File
231
    (archive_file_id, logical_size_in_bytes,
232
     vid, fseq, block_id, copy_nb, creation_time) (
233
234
    SELECT /*+ PARALLEL(F) */
           fileid, filesize, vid, fseq, blockId, copyno, s_mtime FROM CNS_CTAFilesHelper F
235
    ) LOG ERRORS INTO Err$_Tape_File ('Importing '|| inTapePool) REJECT LIMIT UNLIMITED;
236
237
238
239
240
241
242
243
244
245
246
247
  -- Check if we're dealing with dual copy files
  SELECT COUNT(*) INTO varIs2ndCopy FROM Dual
   WHERE EXISTS (SELECT 1 FROM CNS_CTAFiles2ndCopyHelper);
  IF varIs2ndCopy = 1 THEN
    -- Yes, do the same import for the 2nd copies
    INSERT /*+ APPEND PARALLEL(Tape_File) */ INTO Tape_File
      (archive_file_id, logical_size_in_bytes,
      vid, fseq, block_id, copy_nb, creation_time) (
      SELECT /*+ PARALLEL(F) */
            fileid, filesize, vid, fseq, blockId, 2, s_mtime FROM CNS_CTAFiles2ndCopyHelper F
      ) LOG ERRORS INTO Err$_Tape_File ('Importing '|| inTapePool) REJECT LIMIT UNLIMITED;
  END IF;
248
  COMMIT;
249
  SELECT COUNT(*) INTO nbMissingImports FROM Err$_Tape_File;
250
  IF nbMissingImports = nbPreviousErrors THEN
251
    IF nbPreviousErrors > 0 THEN
Giuseppe Lo Presti's avatar
Giuseppe Lo Presti committed
252
253
      CNS_ctaLog(inTapePool, 'Tape files import completed, but there are still '|| nbPreviousErrors
                             ||' problematic cases from previous imports in the Err$_Tape_File table');
254
255
256
    ELSE
      CNS_ctaLog(inTapePool, 'Tape files import completed');
    END IF;
257
  ELSE
258
    CNS_ctaLog(inTapePool, 'Tape files import NOT fully completed, '|| (nbMissingImports-nbPreviousErrors)
259
                           ||' files are missing: please check the Err$_Tape_File table');
Giuseppe Lo Presti's avatar
Giuseppe Lo Presti committed
260
  END IF;
261

262
  -- Update data counters on the Tape table: this does NOT include deleted segments,
263
  -- but it's the best approximation we have here
264
265
266
  FOR v IN (SELECT vid, sum(filesize) totalpervid FROM CNS_CTAFilesHelper GROUP BY vid
            UNION ALL
            SELECT vid, sum(filesize) totalpervid FROM CNS_CTAFiles2ndCopyHelper GROUP BY vid) LOOP
267
268
269
270
271
    UPDATE Tape
       SET data_in_bytes = data_in_bytes + v.totalpervid
     WHERE vid = v.vid;
  END LOOP;
  COMMIT;
272
  CNS_ctaLog(inTapePool, 'Updated Tape data counters');
273
274
275
276
277
END;
/


-- Mark tapes as exported in CASTOR VMGR
278
CREATE OR REPLACE PROCEDURE markCASTORTapePoolExported(inTapePool VARCHAR2) AS
279
280
BEGIN
  UPDATE Vmgr_tape_side
281
     SET status = status + 2 - BITAND(status, 2)   -- read status = BITOR(status, 2), but BITOR does not exist
282
283
284
285
286
287
   WHERE poolName = inTapePool;
  COMMIT;
  CNS_ctaLog(inTapePool, 'VMGR Tapes marked as EXPORTED');
END;
/

288

289
-- Entry point to import metadata from the CASTOR namespace
290
CREATE OR REPLACE PROCEDURE importFromCASTOR(inTapePool VARCHAR2, inVO VARCHAR2, inEOSCTAInstance VARCHAR2, inDryRun INTEGER) AS
291
  nbFiles INTEGER;
292
  var2ndTapePool VARCHAR2(100);
293
BEGIN
294
295
296
297
  -- First check if there's anything already ongoing and fail early:
  SELECT COUNT(*) INTO nbFiles FROM CNS_CTAFilesHelper;
  IF nbFiles > 0 THEN
    raise_application_error(-20000, 'Another export of ' || nbFiles || ' files to CTA is ongoing, ' ||
298
                            'please terminate it with complete_tapepool_export.py before starting a new one.');
299
300
  END IF;
  IF inDryRun = 0 THEN
301
    CNS_ctaLog(inTapePool, 'CASTOR metadata import started');
302
  ELSE
303
    CNS_ctaLog(inTapePool, 'CASTOR metadata import started [dry-run mode]');
304
  END IF;
305
  BEGIN
306
307
308
309
    -- extract all relevant files metadata and work out if dual tape copies are to be imported; can raise exceptions
    CNS_filesForCTAExport(inTapePool, var2ndTapePool);
    -- extract all directories metadata
    CNS_dirsForCTAExport(inTapePool);
310
311
    -- import tapes; can raise exceptions
    importTapePool(inTapePool, inVO);
312
313
314
    IF var2ndTapePool IS NOT NULL THEN
      importTapePool(var2ndTapePool, inVO);
    END IF;
315
    -- import metadata into the CTA catalogue
316
    populateCTAFilesFromCASTOR(inEOSCTAInstance, inTapePool);
317
318
    IF inDryRun = 0 THEN
      -- mark tapes as exported, only when executed for real
319
      markCASTORTapePoolExported(inTapePool);
320
321
322
      IF var2ndTapePool IS NOT NULL THEN
        markCASTORTapePoolExported(var2ndTapePool);
      END IF;
323
324
325
326
327
328
329
330
331
      CNS_ctaLog(inTapePool, 'CASTOR metadata import completed successfully');
    ELSE
      CNS_ctaLog(inTapePool, 'CASTOR metadata import completed successfully [dry-run mode]');
    END IF;
  EXCEPTION WHEN OTHERS THEN
    -- any error is logged and raised to the caller
    CNS_ctaLog(inTapePool, 'Exception caught, terminating import: '|| SQLERRM ||' '|| dbms_utility.format_error_backtrace());
    RAISE;
  END;
332
333
END;
/
334
335


336
337
-- Entry point to remove the CASTOR imported metadata from the CTA catalogue
CREATE OR REPLACE PROCEDURE removeCASTORMetadata(inTapePool VARCHAR2) AS
338
339
  varTapePoolId INTEGER;
  varCount INTEGER;
340
341
342
  varIs2ndCopy INTEGER;
  var2ndTapePoolId INTEGER := 0;
  var2ndTapePool VARCHAR2(100);
343
  CURSOR temp_data_cur IS SELECT archive_file_id FROM Temp_Remove_CASTOR_Metadata;
344
  ids numList;
345
  varTempId INTEGER;
346
BEGIN
347
348
349
350
351
  BEGIN
    SELECT tape_pool_id INTO varTapePoolId
      FROM Tape_Pool
     WHERE tape_pool_name = inTapePool;
  EXCEPTION WHEN NO_DATA_FOUND THEN
352
    raise_application_error(-20000, 'No such tape pool found');
353
  END;
354
  -- prepare the list of files to be removed in a temporary table
355
356
357
358
359
360
  -- the NO_PARALLEL hints are required if we want to run this all in the same transaction,
  -- otherwise an ORA-12838 (cannot read/modify an object after modifying it in parallel) is raised
  INSERT /*+ NO_PARALLEL(T) */ INTO Temp_Remove_CASTOR_Metadata T
    (SELECT /*+ NO_PARALLEL(TF) */ archive_file_id FROM Tape_File TF WHERE vid IN
      (SELECT vid FROM Tape WHERE tape_pool_id = varTapePoolId AND is_from_castor = '1'));
  SELECT COUNT(*) INTO varCount FROM Temp_Remove_CASTOR_Metadata;
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
  CNS_ctaLog(inTapePool, 'Removal of CASTOR files and tapes metadata from CTA started, '|| varCount ||' files to go');
  -- check for dual copy case
  SELECT COUNT(*) INTO varIs2ndCopy FROM Dual
  WHERE EXISTS (
    SELECT 1 FROM Tape_File T, Temp_Remove_CASTOR_Metadata F
     WHERE T.archive_file_id = F.archive_file_id AND T.copy_nb = 2);
  IF varIs2ndCopy = 1 THEN
    -- yes, work out the other tapepool to be removed
    SELECT archive_file_id INTO varTempId
      FROM Temp_Remove_CASTOR_Metadata
     WHERE ROWNUM = 1;
    SELECT tape_pool_name, tape_pool_id
      INTO var2ndTapePool, var2ndTapePoolId
      FROM Tape_Pool
     WHERE tape_pool_id = (
        SELECT tape_pool_id FROM Tape
         WHERE tape_pool_id <> varTapePoolId
           AND vid IN (SELECT vid FROM Tape_File
                        WHERE archive_file_id = varTempId)
        );
    CNS_ctaLog(inTapePool, 'Dual tape copies detected, will ALSO remove files metadata from tape pool '|| var2ndTapePool);
382
    CNS_ctaLog(var2ndTapePool, 'Removal of CASTOR files and tapes metadata due to the removal of tape pool '|| inTapePool);
383
  END IF;
384
  -- efficiently delete all Tape_File and Archive_File entries in multiple bulks
385
  OPEN temp_data_cur;
386
  LOOP
387
    FETCH temp_data_cur BULK COLLECT INTO ids LIMIT 10000;
388
389
390
391
392
393
    EXIT WHEN ids.count = 0;
    FORALL i IN 1..ids.count
      DELETE FROM Tape_File WHERE archive_file_id = ids(i);
    FORALL i IN 1..ids.count
      DELETE FROM Archive_File WHERE archive_file_id = ids(i);
  END LOOP;
394
  CLOSE temp_data_cur;
395
396
  -- delete all CASTOR tapes but leave the tapepool(s) in the system
  DELETE FROM Tape WHERE (tape_pool_id = varTapePoolId OR tape_pool_id = var2ndTapePoolId) AND is_from_castor = '1';
397
398
399
400
401
402
403
  -- commit the entire operation: this will clean the temporary table
  COMMIT;
  CNS_ctaLog(inTapePool, 'Removal of CASTOR tapes metadata completed successfully');
END;
/


404
405
406
407
408
-- The following is to be executed at schema creation or before the first migration
BEGIN
  dbms_errlog.create_error_log(dml_table_name => 'Tape_File');
END;
/