oracle_catalogue_castor_migration.sql 12.4 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
29
30
-- 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;
CREATE OR REPLACE SYNONYM CNS_CTADirsHelper FOR &castornsSchema..CTADirsHelper;
CREATE OR REPLACE SYNONYM CNS_CTAMigrationLog FOR &castornsSchema..CTAMigrationLog;
31
32
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;
33

34
35
CREATE OR REPLACE SYNONYM cns_filesAndDirsForCTAExport FOR &castornsSchema..filesAndDirsForCTAExport;
CREATE OR REPLACE SYNONYM cns_ctaLog FOR &castornsSchema..ctaLog;
36
37
38
39
40
41

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;
42

43
44
45
46
-- Enable parallel inserts
ALTER TABLE Archive_File PARALLEL;
ALTER TABLE Tape_File PARALLEL;

47
48
49
50

-- 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
51
  varTapePoolName VARCHAR2(100) := inTapePool;
52
53
54
  varUnused VARCHAR2(100);
  CONSTRAINT_VIOLATED EXCEPTION;
  PRAGMA EXCEPTION_INIT(CONSTRAINT_VIOLATED, -1);
55
BEGIN
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
  FOR DGN IN (SELECT dgn FROM Vmgr_tape_dgnmap) LOOP
    -- The very first time insert the logical libraries found on VMGR
    BEGIN
      INSERT INTO Logical_Library (logical_library_name, 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 (
        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;
72
73
  INSERT INTO Tape_Pool (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,
74
75
    last_update_host_name, last_update_time)
  VALUES (
76
    varTapePoolName,
77
    inVO,
78
79
    0,    -- nb_partial_tapes, to be filled afterwards
    '0',  -- is_encrypted is assumed false in CASTOR
80
    'Imported from CASTOR',
81
82
    'CASTOR', 'CASTOR', getTime(),
    'CASTOR', 'CASTOR', getTime()
83
    );
84
85
86
87
88
89
  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
90
               AND BITAND(TS.status, 2) = 0 AND BITAND(TS.status, 32) = 0   -- not already EXPORTED or ARCHIVED
91
               AND TS.poolname = inTapePool) LOOP
92
    INSERT INTO Tape (vid, media_type, vendor, logical_library_name, tape_pool_name,
93
94
      encryption_key, capacity_in_bytes, data_in_bytes, last_fseq,
      is_disabled, is_full, is_read_only, is_from_castor,
95
96
      label_drive, label_time, last_read_drive, last_read_time, read_mount_count,
      last_write_drive, last_write_time, write_mount_count,
97
      user_comment, creation_log_user_name, creation_log_host_name, creation_log_time,
98
99
      last_update_user_name, last_update_host_name, last_update_time)
    VALUES (
100
      T.vid,
101
      decode(T.density,    -- media type: only one of the options below (see #488)
102
103
104
105
106
107
108
        '7000GC', '3592JC',
        '8000GC', '3592JC',
        '9TC',    'LTO-7M',
        '10TC',   '3592JD',
        '12TC',   'LTO-8',
        '15TC',   '3592JD',
        'UNDEFINED'),
109
      T.manufacturer,
110
      T.dgn,
111
      varTapePoolName,
112
113
      '-',                 -- blank encryption key
      decode(T.density,    -- capacity: only one of the options below (see #488)
114
115
116
117
118
119
120
121
        '7000GC',  7000000000000,
        '8000GC',  8000000000000,
        '9TC',     9000000000000,
        '10TC',   10000000000000,
        '12TC',   12000000000000,
        '15TC',   15000000000000,
        0),
      0,      -- total data: will be filled by populateCTAFromCASTOR()
122
      T.nbfiles,
123
124
125
126
127
      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
      '1',                                         -- is_from_castor flag
      'CASTOR', 0,                  -- label drive and time (unknown)
128
129
      T.rhost, T.rtime, T.rcount,   -- last read drive/time and count
      T.whost, T.wtime, T.wcount,   -- last write drive/time and count
130
      'Imported from CASTOR',
131
132
      'CASTOR', 'CASTOR', getTime(),
      'CASTOR', 'CASTOR', getTime()
133
134
135
      );
  END LOOP;
  COMMIT;
136
  CNS_ctaLog(inTapePool, 'Tapes import completed');
137
138
END;
/
139
140


141
142
-- Insert the file-level metadata for the given migration
CREATE OR REPLACE PROCEDURE populateCTAFromCASTOR(inEOSCTAInstance VARCHAR2, inTapePool VARCHAR2) AS
143
  nbPreviousErrors INTEGER;
144
  nbMissingImports INTEGER;
145
146
  CONSTRAINT_VIOLATED EXCEPTION;
  PRAGMA EXCEPTION_INIT(CONSTRAINT_VIOLATED, -1);
147
  varLenCastorPrefix INTEGER := length('/castor/cern.ch/');
Giuseppe Lo Presti's avatar
Giuseppe Lo Presti committed
148
  varNamespacePrefix VARCHAR2(20);
149
BEGIN
150
  -- Populate storage classes metadata if missing: do one by one and handle constraint violations
151
152
153
154
  FOR c IN (SELECT classid, name classname, nbcopies
              FROM CNS_Class_Metadata
             WHERE classid IN
              (SELECT classid FROM CNS_CTAFilesHelper)) LOOP
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
    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
170
      NULL;   -- it was already present, skip ASSUMING it's the correct one!
171
172
173
    END;
  END LOOP;
  COMMIT;
174
  CNS_ctaLog(inTapePool, 'Storage classes import completed');
175
  -- Populate the CTA catalogue with the CASTOR file metadata
Giuseppe Lo Presti's avatar
Giuseppe Lo Presti committed
176
177
178
179
180
181
182
  IF instr(inEOSCTAInstance, 'eoscta') = 1 THEN
    varNamespacePrefix := substr(inEOSCTAInstance, 7);
  ELSIF instr(inEOSCTAInstance, 'eos') = 1 THEN
    varNamespacePrefix := substr(inEOSCTAInstance, 4);
  ELSE
    varNamespacePrefix := inEOSCTAInstance;
  END IF;
183
184
  INSERT /*+ APPEND PARALLEL(Archive_File) */ INTO Archive_File
    (archive_file_id, disk_instance_name, disk_file_id, disk_file_path,
185
    disk_file_uid, disk_file_gid, size_in_bytes, checksum_adler32,
186
    storage_class_id, creation_time, reconciliation_time) (
187
188
    SELECT /*+ PARALLEL(F) PARALLEL(D) */
           F.fileid, inEOSCTAInstance, F.fileid,
Giuseppe Lo Presti's avatar
Giuseppe Lo Presti committed
189
           '/eos/' || varNamespacePrefix
190
           || substr(D.path, varLenCastorPrefix) || '/' ||  F.filename,
191
           F.disk_uid, F.disk_gid, F.filesize, F.checksum, F.classid, F.btime, 0
192
      FROM CNS_CTAFilesHelper F, CNS_Dirs_Full_Path D
193
     WHERE F.parent_fileid = D.fileid
194
           -- exclude the already existing files because of dual copies
195
       AND NOT EXISTS (SELECT 1 FROM Archive_File WHERE archive_file_id = F.fileid)
196
    );
197
198
  COMMIT;
  CNS_ctaLog(inTapePool, 'Archive files import completed');
199
200
201
  SELECT count(*) INTO nbPreviousErrors FROM Err$_Tape_File;
  -- And now the tape file metadata: here we also store the constraint_violation
  -- errors for later manual analysis
202
  INSERT /*+ APPEND PARALLEL(Tape_File) */ INTO Tape_File
203
    (archive_file_id, logical_size_in_bytes,
204
     vid, fseq, block_id, copy_nb, creation_time) (
205
206
    SELECT /*+ PARALLEL(F) */
           fileid, filesize, vid, fseq, blockId, copyno, s_mtime FROM CNS_CTAFilesHelper F
207
    ) LOG ERRORS INTO Err$_Tape_File ('Importing '|| inTapePool) REJECT LIMIT UNLIMITED;
208
  COMMIT;
209
  SELECT count(*) INTO nbMissingImports FROM Err$_Tape_File;
210
  IF nbMissingImports = nbPreviousErrors THEN
211
212
213
214
215
216
    IF nbPreviousErrors > 0 THEN
      CNS_ctaLog(inTapePool, 'Tape files import completed. Still '|| nbPreviousErrors
                             ||' cases from previous imports in the Err$_Tape_File table');
    ELSE
      CNS_ctaLog(inTapePool, 'Tape files import completed');
    END IF;
217
  ELSE
218
    CNS_ctaLog(inTapePool, 'Tape files import NOT fully completed, '|| (nbMissingImports-nbPreviousErrors)
219
                           ||' files are missing: please check the Err$_Tape_File table');
Giuseppe Lo Presti's avatar
Giuseppe Lo Presti committed
220
  END IF;
221
222
  -- update data counter on the Tape table: this does NOT include deleted segments,
  -- but it's the best approximation we have here
223
224
225
226
227
228
  FOR v IN (SELECT vid, sum(filesize) totalpervid FROM CNS_CTAFilesHelper GROUP BY vid) LOOP
    UPDATE Tape
       SET data_in_bytes = data_in_bytes + v.totalpervid
     WHERE vid = v.vid;
  END LOOP;
  COMMIT;
229
  CNS_ctaLog(inTapePool, 'Updated Tape data counters');
230
231
232
233
234
235
236
237
END;
/


-- Mark tapes as exported in CASTOR VMGR
CREATE OR REPLACE PROCEDURE markTapePoolExported(inTapePool VARCHAR2) AS
BEGIN
  UPDATE Vmgr_tape_side
238
     SET status = status + 2 - BITAND(status, 2)   -- as BITOR does not exist
239
240
241
242
243
244
   WHERE poolName = inTapePool;
  COMMIT;
  CNS_ctaLog(inTapePool, 'VMGR Tapes marked as EXPORTED');
END;
/

245

246
-- Entry point to import metadata from the CASTOR namespace
247
CREATE OR REPLACE PROCEDURE importFromCASTOR(inTapePool VARCHAR2, inVO VARCHAR2, inEOSCTAInstance VARCHAR2, inDryRun INTEGER) AS
248
  nbFiles INTEGER;
249
BEGIN
250
251
252
253
254
255
256
  -- 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, ' ||
                            'please terminate it with completeCTAExport() before starting a new one.');
  END IF;
  IF inDryRun = 0 THEN
257
    CNS_ctaLog(inTapePool, 'CASTOR metadata import started');
258
  ELSE
259
    CNS_ctaLog(inTapePool, 'CASTOR metadata import started [dry-run mode]');
260
  END IF;
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
  BEGIN
    -- extract all relevant metadata; can raise exceptions
    CNS_filesAndDirsForCTAExport(inTapePool);
    -- import tapes; can raise exceptions
    importTapePool(inTapePool, inVO);
    -- import metadata into the CTA catalogue
    populateCTAFromCASTOR(inEOSCTAInstance, inTapePool);
    IF inDryRun = 0 THEN
      -- mark tapes as exported, only when executed for real
      markTapePoolExported(inTapePool);
      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;
280
281
END;
/
282
283
284
285
286
287
288


-- 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;
/