Migration en utilisant DataPump et Transportable_tablespaces
Je migre d'un serveur Oracle 11.2.0.4 su un autre serveur en 19.6, en multitenant
Une des tables prenant plus de 400Gb, je souhaite le faire via Datapump et Transportable tablespaces
Je passe mes tablespaces souces en lecture seule
Code:
1
2
3
4
5
6
7
8
9
| alter tablespace DVP_BLOB READ ONLY;
alter tablespace DVP_INDEX_SMALL READ ONLY;
alter tablespace DVP_SHBLOB READ ONLY;
alter tablespace DVP_SHINDEX_SMALL READ ONLY;
alter tablespace DVP_SHSTAMM_MEDIUM READ ONLY;
alter tablespace DVP_SHSTAMM_SMALL READ ONLY;
alter tablespace DVP_STAMM_MEDIUM READ ONLY;
alter tablespace DVP_STAMM_SMALL READ ONLY;
create directory dg as '+ORA_DBSOURCE_DAT/DBSOURCE/datafile' ; |
J'exporte le metamodèle
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
expdp userid=mondvp/*** transport_tablespaces=DVP_BLOB,DVP_INDEX_SMALL,DVP_SHBLOB,DVP_SHINDEX_SMALL,DVP_SHSTAMM_MEDIUM,DVP_SHSTAMM_SMALL,DVP_STAMM_MEDIUM,DVP_STAMM_SMALL dumpfile=dvp.dmp logfile=expdvp.log
Connect▒ ▒ : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
D▒marrage de "mondvp"."SYS_EXPORT_TRANSPORTABLE_01" : userid=mondvp/******** transport_tablespaces=DVP_BLOB,DVP_INDEX_SMALL,DVP_SHBLOB,DVP_SHINDEX_SMALL,DVP_SHSTAMM_MEDIUM,DVP_SHSTAMM_SMALL,DVP_STAMM_MEDIUM,DVP_STAMM_SMALL dumpfile=rola.dmp logfile=exprola.log
Traitement du type d'objet TRANSPORTABLE_EXPORT/PLUGTS_BLK
Traitement du type d'objet TRANSPORTABLE_EXPORT/TABLE
Traitement du type d'objet TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Traitement du type d'objet TRANSPORTABLE_EXPORT/INDEX/INDEX
Traitement du type d'objet TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Traitement du type d'objet TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Traitement du type d'objet TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Traitement du type d'objet TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Traitement du type d'objet TRANSPORTABLE_EXPORT/COMMENT
Traitement du type d'objet TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Traitement du type d'objet TRANSPORTABLE_EXPORT/TRIGGER
Traitement du type d'objet TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Traitement du type d'objet TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
Traitement du type d'objet TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
Traitement du type d'objet TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
Traitement du type d'objet TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW
Traitement du type d'objet TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Table ma▒tre "mondvp"."SYS_EXPORT_TRANSPORTABLE_01" charg▒e/d▒charg▒e avec succ▒s
******************************************************************************
L'ensemble de fichiers de vidage de mondvp.SYS_EXPORT_TRANSPORTABLE_01 est :
/export/DBSOURCE/rDVP.dmp
******************************************************************************
Fichiers de donn▒es requis pour le tablespace transportable DVP_BLOB :
+ORA_DBSOURCE_DAT/DBSOURCE/datafile/DVP_blob.281.848942105
Fichiers de donn▒es requis pour le tablespace transportable DVP_INDEX_SMALL :
+ORA_DBSOURCE_DAT/DBSOURCE/datafile/DVP_index_small.275.848942099
Fichiers de donn▒es requis pour le tablespace transportable DVP_SHBLOB :
+ORA_DBSOURCE_DAT/DBSOURCE/datafile/DVP_shblob.285.848942109
Fichiers de donn▒es requis pour le tablespace transportable DVP_SHINDEX_SMALL :
+ORA_DBSOURCE_DAT/DBSOURCE/datafile/DVP_shindex_small.284.848942107
Fichiers de donn▒es requis pour le tablespace transportable DVP_SHSTAMM_MEDIUM :
+ORA_DBSOURCE_DAT/DBSOURCE/datafile/DVP_shstamm_medium.282.848942107
Fichiers de donn▒es requis pour le tablespace transportable DVP_SHSTAMM_SMALL :
+ORA_DBSOURCE_DAT/DBSOURCE/datafile/DVP_shstamm_small.283.848942107
Fichiers de donn▒es requis pour le tablespace transportable DVP_STAMM_MEDIUM :
+ORA_DBSOURCE_DAT/DBSOURCE/datafile/DVP_stamm_medium.276.848942101
Fichiers de donn▒es requis pour le tablespace transportable DVP_STAMM_SMALL :
+ORA_DBSOURCE_DAT/DBSOURCE/datafile/DVP_stamm_small.277.848942101
L'ex▒cution du travail "mondvp"."SYS_EXPORT_TRANSPORTABLE_01" a abouti ▒ Lun. Juin 15 14:04:06 2020 elapsed 0 00:02:08 |
et effectue l'extraction de ASM à un share nfs
Code:
1
2
3
4
5
6
7
8
9
10
11
| BEGIN
DBMS_FILE_TRANSFER.COPY_FILE('dg','DVP_blob.281.848942105', 'DATA_PUMP_DIR','DVP_blob.281.848942105');
DBMS_FILE_TRANSFER.COPY_FILE('dg','DVP_index_small.275.848942099', 'DATA_PUMP_DIR','DVP_index_small.275.848942099');
DBMS_FILE_TRANSFER.COPY_FILE('dg','DVP_shblob.285.848942109', 'DATA_PUMP_DIR','DVP_shblob.285.848942109');
DBMS_FILE_TRANSFER.COPY_FILE('dg','DVP_shindex_small.284.848942107', 'DATA_PUMP_DIR','DVP_shindex_small.284.848942107');
DBMS_FILE_TRANSFER.COPY_FILE('dg','DVP_shstamm_medium.282.848942107', 'DATA_PUMP_DIR','DVP_shstamm_medium.282.848942107');
DBMS_FILE_TRANSFER.COPY_FILE('dg','DVP_shstamm_small.283.848942107', 'DATA_PUMP_DIR','DVP_shstamm_small.283.848942107');
DBMS_FILE_TRANSFER.COPY_FILE('dg','DVP_stamm_medium.276.848942101', 'DATA_PUMP_DIR','DVP_stamm_medium.276.848942101');
DBMS_FILE_TRANSFER.COPY_FILE('dg','DVP_stamm_small.277.848942101', 'DATA_PUMP_DIR','DVP_stamm_small.277.848942101');
END;
/ |
Après quelques heures, les fichiers sont présent sur mon share nfs. Je repasse en lecture écriture
Code:
1
2
3
4
5
6
7
8
| alter tablespace DVP_BLOB READ WRITE;
alter tablespace DVP_INDEX_SMALL READ WRITE;
alter tablespace DVP_SHBLOB READ WRITE;
alter tablespace DVP_SHINDEX_SMALL READ WRITE;
alter tablespace DVP_SHSTAMM_MEDIUM READ WRITE;
alter tablespace DVP_SHSTAMM_SMALL READ WRITE;
alter tablespace DVP_STAMM_MEDIUM READ WRITE;
alter tablespace DVP_STAMM_SMALL READ WRITE; |
J'effectue alors la procédure inverse sur ma nouvelle pluggable database que je crée ici
Code:
1
2
3
4
5
| create pluggable database DBCIBLE from WINDB ;
alter pluggable database DBCIBLE open instances=all ;
alter session set container=DBCIBLE ;
create or replace directory DATA_DIR as '+DATA/DBCIBLE/A72E45AA1AB31968E053AFE94D0AC00C/DATAFILE/' ;
create or replace directory DBSOURCE as '/export/DBSOURCE' ; |
Je recopie mes datafiles du share nfs à mon nouvel ASM
Code:
1
2
3
4
5
6
7
8
9
10
11
| BEGIN
DBMS_FILE_TRANSFER.COPY_FILE('DBSOURCE','DVP_blob.281.848942105', 'DATA_DIR','DVP_blob');
DBMS_FILE_TRANSFER.COPY_FILE('DBSOURCE','DVP_index_small.275.848942099', 'DATA_DIR','DVP_index_small');
DBMS_FILE_TRANSFER.COPY_FILE('DBSOURCE','DVP_shblob.285.848942109', 'DATA_DIR','DVP_shblob');
DBMS_FILE_TRANSFER.COPY_FILE('DBSOURCE','DVP_shindex_small.284.848942107', 'DATA_DIR','DVP_shindex_small');
DBMS_FILE_TRANSFER.COPY_FILE('DBSOURCE','DVP_shstamm_medium.282.848942107', 'DATA_DIR','DVP_shstamm_medium');
DBMS_FILE_TRANSFER.COPY_FILE('DBSOURCE','DVP_shstamm_small.283.848942107', 'DATA_DIR','DVP_shstamm_small');
DBMS_FILE_TRANSFER.COPY_FILE('DBSOURCE','DVP_stamm_medium.276.848942101', 'DATA_DIR','DVP_stamm_medium');
DBMS_FILE_TRANSFER.COPY_FILE('DBSOURCE','DVP_stamm_small.277.848942101', 'DATA_DIR','DVP_stamm_small');
END;
/ |
Après de nouvelles heures, les fichiers sont créés sur ASM
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
| ASMCMD> pwd
+data/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE
ASMCMD> ls -l
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE JUN 16 16:00:00 Y COPY_FILE.385.1043236217
DATAFILE MIRROR COARSE JUN 16 16:00:00 Y COPY_FILE.435.1043228731
DATAFILE MIRROR COARSE JUN 16 16:00:00 Y COPY_FILE.457.1043236225
DATAFILE MIRROR COARSE JUN 16 16:00:00 Y COPY_FILE.458.1043253433
DATAFILE MIRROR COARSE JUN 16 11:00:00 Y COPY_FILE.459.1043228815
DATAFILE MIRROR COARSE JUN 16 16:00:00 Y COPY_FILE.466.1043235847
DATAFILE MIRROR COARSE JUN 16 16:00:00 Y COPY_FILE.479.1043253679
DATAFILE MIRROR COARSE JUN 16 16:00:00 Y COPY_FILE.487.1043236221
DATAFILE MIRROR COARSE JUN 16 16:00:00 Y SYSAUX.471.1043158153
DATAFILE MIRROR COARSE JUN 15 14:00:00 Y SYSTEM.495.1043158153
DATAFILE MIRROR COARSE JUN 15 14:00:00 Y UNDOTBS1.541.1043158153
DATAFILE MIRROR COARSE JUN 15 14:00:00 Y UNDO_2.543.1043158203
DATAFILE MIRROR COARSE JUN 16 16:00:00 Y USERS.515.1043158153
DATAFILE MIRROR COARSE JUN 16 11:00:00 N dvp_blob => +DATA/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/COPY_FILE.459.1043228815
DATAFILE MIRROR COARSE JUN 16 16:00:00 N dvp_index_small => +DATA/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/COPY_FILE.466.1043235847
DATAFILE MIRROR COARSE JUN 16 16:00:00 N dvp_shblob => +DATA/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/COPY_FILE.385.1043236217
DATAFILE MIRROR COARSE JUN 16 16:00:00 N dvp_shindex_small => +DATA/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/COPY_FILE.487.1043236221
DATAFILE MIRROR COARSE JUN 16 16:00:00 N dvp_shstamm_medium => +DATA/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/COPY_FILE.457.1043236225
DATAFILE MIRROR COARSE JUN 16 16:00:00 N dvp_shstamm_small => +DATA/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/COPY_FILE.435.1043228731
DATAFILE MIRROR COARSE JUN 16 16:00:00 N dvp_stamm_medium => +DATA/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/COPY_FILE.458.1043253433
DATAFILE MIRROR COARSE JUN 16 16:00:00 N dvp_stamm_small => +DATA/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/COPY_FILE.479.1043253679 |
Je souhaite maintenant restaurer les métadonnées afin de retrouver toute ma base
Code:
impdp mondvp/***@//oradvp-scan:1521/DBCIBLE DIRECTORY=DBCIBLE DUMPFILE=dvp.dmp logfile=impDvp.log TRANSPORSPORT_DATAFILES=+data/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/dvp_index_small,+data/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/dvp/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/dvp_shindex_small,+data/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/dvp_shstamm_medium,+1F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/dvp_shstamm_small,+data/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/dvp_stamm_medium,+data/DBCIBLE/A3FE053AFE94D0AFA9D/DATAFILE/dvp_stamm_small
Déjà là premier malaise : il me demande de créer au préalable les utilisateurs... mais sans les tablspaces, il faut les créer sur USERS ?
Si je le fais, l'ordre passe mais crash
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
Import: Release 19.0.0.0.0 - Production on Tue Jun 16 16:57:09 2020
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "MONDVP"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source time zone is +01:00 and target time zone is +02:00.
Starting "MONDVP"."SYS_IMPORT_TRANSPORTABLE_01": MONDVP/********@//oradvp-scan:1521/DBCIBE DIRECTORY=DBCIBLE DUMPFILE=dvp.dmp logfile=impDvp.log TRANSPORT_DATAFILES=+data/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/dvp_index_small,+data/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/rota/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/dvp_shindex_small,+data/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/dvp_shstamm_medium,81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/dvp_shstamm_small,+data/DBCIBLE/A81F4A4BE83E2F3FE053AFE94D0AFA9D/DATAFILE/dvp_stamm_medium,+data/DBCIBLE/F3FE053AFE94D0AFA9D/DATAFILE/dvp_stamm_small
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-19721: Cannot find data file with absolute file number 23 in tablespace dvp_BLOB
Job "MONDVP"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at Tue Jun 16 16:57:18 2020 elapsed 0 00:00:07 |
