oracle_catalogue_castor_migration.sql 10.7 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
-- 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;
30
CREATE OR REPLACE SYNONYM CNS_Class_Metadata FOR &castornsSchema..Cns_class_metadata;
31
32
33
CREATE OR REPLACE SYNONYM CNS_CTAMigrationLog FOR &castornsSchema..CTAMigrationLog;

CREATE OR REPLACE SYNONYM CNS_filesAndDirsForCTAExport FOR &castornsSchema..filesAndDirsForCTAExport;
34
CREATE OR REPLACE SYNONYM CNS_ctaLog FOR &castornsSchema..ctaLog;
35
36
37
38
39
40

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

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

46
47
48
49

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


143
144
-- Insert the file-level metadata for the given migration
CREATE OR REPLACE PROCEDURE populateCTAFromCASTOR(inEOSCTAInstance VARCHAR2, inTapePool VARCHAR2) AS
145
  varFullPath VARCHAR2(2048);
146
147
  CONSTRAINT_VIOLATED EXCEPTION;
  PRAGMA EXCEPTION_INIT(CONSTRAINT_VIOLATED, -1);
148
BEGIN
149
  -- Populate storage classes metadata if missing: do one by one and handle constraint violations
150
151
152
153
  FOR c IN (SELECT classid, name classname, nbcopies
              FROM CNS_Class_Metadata
             WHERE classid IN
              (SELECT classid FROM CNS_CTAFilesHelper)) LOOP
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
    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
169
      NULL;   -- it was already present, skip ASSUMING it's the correct one!
170
171
172
    END;
  END LOOP;
  COMMIT;
173
  CNS_ctaLog(inTapePool, 'Storage classes import completed');
174
175
176
  -- Populate the CTA catalogue with the CASTOR file metadata
  INSERT /*+ APPEND PARALLEL(Archive_File) */ INTO Archive_File
    (archive_file_id, disk_instance_name, disk_file_id, disk_file_path,
177
    disk_file_uid, disk_file_gid, size_in_bytes, checksum_adler32,
178
    storage_class_id, creation_time, reconciliation_time) (
179
180
    SELECT /*+ PARALLEL(F) PARALLEL(D) */
           F.fileid, inEOSCTAInstance, F.fileid,
181
           '/eos/cta' || inEOSCTAInstance || D.path || '/' || F.filename,
182
           F.disk_uid, F.disk_gid, F.filesize, F.classid, F.btime, 0
183
184
      FROM CNS_CTAFilesHelper F, CNS_CTADirsHelper D
     WHERE D.fileid = F.parent_fileid
185
           -- exclude the already existing files because of dual copies
186
       AND NOT EXISTS (SELECT 1 FROM Archive_File WHERE archive_file_id = F.fileid)
187
188
189
    );
  COMMIT;
  CNS_ctaLog(inTapePool, 'Archive files import completed');
190
  -- And now the tape file metadata
191
  INSERT /*+ APPEND PARALLEL(Tape_File) */ INTO Tape_File
192
    (archive_file_id, logical_size_in_bytes,
193
     vid, fseq, block_id, copy_nb, creation_time) (
194
195
196
    SELECT /*+ PARALLEL(F) */
           fileid, filesize, vid, fseq, blockId, copyno, s_mtime FROM CNS_CTAFilesHelper F
    );
197
  COMMIT;
198
  CNS_ctaLog(inTapePool, 'Tape files import completed');
199
200
201
202
203
204
205
206
  -- update data counter on the Tape table. This does NOT include deleted segments,
  -- but it's  the best approximation we have here.
  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;
207
  CNS_ctaLog(inTapePool, 'Updated Tape data counters');
208
209
210
211
212
213
214
215
END;
/


-- Mark tapes as exported in CASTOR VMGR
CREATE OR REPLACE PROCEDURE markTapePoolExported(inTapePool VARCHAR2) AS
BEGIN
  UPDATE Vmgr_tape_side
216
     SET status = status + 2 - BITAND(status, 2)   -- as BITOR does not exist
217
218
219
220
221
222
   WHERE poolName = inTapePool;
  COMMIT;
  CNS_ctaLog(inTapePool, 'VMGR Tapes marked as EXPORTED');
END;
/

223

224
-- Entry point to import metadata from the CASTOR namespace
225
CREATE OR REPLACE PROCEDURE importFromCASTOR(inTapePool VARCHAR2, inVO VARCHAR2, inEOSCTAInstance VARCHAR2, inDryRun INTEGER) AS
226
  nbFiles INTEGER;
227
BEGIN
228
229
230
231
232
233
234
  -- 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
235
    CNS_ctaLog(inTapePool, 'CASTOR metadata import started');
236
  ELSE
237
    CNS_ctaLog(inTapePool, 'CASTOR metadata import started [dry-run mode]');
238
  END IF;
239
240
  -- extract all relevant metadata; can raise exceptions
  CNS_filesAndDirsForCTAExport(inTapePool);
241
242
  -- import tapes; can raise exceptions
  importTapePool(inTapePool, inVO);
243
  -- import metadata into the CTA catalogue
244
  populateCTAFromCASTOR(inEOSCTAInstance, inTapePool);
245
246
247
  IF inDryRun = 0 THEN
    -- mark tapes as exported, only when executed for real
    markTapePoolExported(inTapePool);
248
    CNS_ctaLog(inTapePool, 'CASTOR metadata import completed successfully');
249
  ELSE
250
    CNS_ctaLog(inTapePool, 'CASTOR metadata import completed successfully [dry-run mode]');
251
  END IF;
252
253
END;
/