Bardzo przydatne we wszelkiego rodzaju migracjach, oraz przy szybkim kopiowaniu danych do kilku baz. Artykuł pokazuje krok po kroku w jaki sposób łatwo skopiować przestrzeń zawierającą różnego rodzaju obiekty do drugiej bazy.
W zasadzie można sobie wyobrazić sytuację, że jednocześnie przestrzeń jest podłączona jako read-only do dwóch baz jednocześnie. Zatem:
baza źródłowa
- Tworzymy przestrzeń, schemat i kilka obiektów, które będziemy chcieli skopiować na docelową bazę i sprawdzić, czy obiekty po przeniesieniu dalej będą właściwie pełnić swoją rolę:
create tablespace newtbs datafile '/NEWTBS.dbf' size 100M autoextend on next 10m maxsize 200m;
create user newusr identified by newusr123 default tablespace newtbs;
grant connect, resource to newusr;
alter user newusr quota unlimited on newtbs;
a następnie utworzymy kilka obiektów, prosta tabela, tabela IOT, tabela z kolumną typu xml, tabela zawierająca lob, tabela z kolumną local TZ, puste procedury,
-- some procedures owned by newusr:
begin
for i in 1..10 loop
execute immediate 'create or replace procedure newusr.p' || i || ' as begin null; end;';
end loop;
end;
/
-- oridinary table
create table newusr.new_table (a number(1), b varchar2(10)) tablespace newtbs;
insert into new_table values (1,'one');
insert into new_table values (2,'two');
insert into new_table values (3,'three');
commit;
-- xml table
create table newusr.xmltest of xmltype tablespace newtbs;
insert into newusr.xmltest values ('');
commit;
-- index organized table
create table newusr.iot_test (
object_id,
owner,
object_name,
subobject_name,
object_type,
constraint pk_iot_test primary key (object_id))
organization index tablespace newtbs as
select object_id,owner,object_name,subobject_name,
object_type from dba_objects where object_id is not null;
-- table with lob
create table newusr.lobtest (
id number primary key,
l_data clob)
lob (l_data)
store as secrefile tablespace newtbs;
insert into newusr.lobtest values (1, 'firstlob');
commit;
-- table holding values timestamp with local time zone
create table newusr.tztest (id number primary key, d timestamp with local time zone) tablespace newtbs;
insert into newusr.tztest values (1, systimestamp);
commit;
select owner, object_name from dba_objects where owner='NEWUSR';
select table_name, column_name,in_row, tablespace_name
from dba_lobs where owner = 'NEWUSR'
Przygotowanie strony źródłowej do zmigrowania:
--create directory pointing to dbfiles:
create directory TRANSPORTDB_REMOTE as 'path-to-dbfiles';
grant read on directory TRANSPORTDB_REMOTE to system;
-- make migrated tablespace newtbs read only
alter tablespace newtbs read only;
baza docelowa
utwórz w bazie obiekt typu katalog (directory) wskazujący na lokalizację plików danych bazy docelowej
create directory TRANSPORTDB_LOCAL as 'path-to-dbfiles';
grant read on directory TRANSPORTDB_LOCAL to SYSTEM;
utwórz połączenie (link) z bazy docelowej do źródłowej, połącz się do bazy źródłowej jako użytkownik system
conn system/manager
create public database link to LINK_TO_SOURCE connect to system identified by manager using 'SOURCE-SID';
Tutaj drobna uwaga – czemu link jest publiczny. O ile na tym etapie uda nam się przekopiować pliki (z konwersją endianów o ile jest konieczna) – to później – podczas importu przestrzeni przez link – link ten nie będzie widoczny dla procesu importu (począwszy od wersji 12.1 – wcześniej z powodzeniem można było do importu użyć dblinku prywatnego). To było mocno zaskakujące dla mnie, długo szukałem rozwiązania tego (ewidentnego dla mnie) bug’a lecz jedyne co znalazłem to – że istotnie jest to bug… w dokumentacji, która twierdziła że taka operacja jest wykonalna przez dblink prywatny, po czym dokumentację zmodyfikowano.
ustaw przestrzeń tabel w tryb tylko do odczytu
alter tablespace NEWTBS read only;
skopiuj plik bazy danych – dokładniej plik przestrzeni tablic używając dbms_file_transfer.get_file (uruchom jako użytkownik system)
BEGIN
DBMS_FILE_TRANSFER.GET_FILE(
source_directory_object => 'DBFILES', /* directory in source DB */
source_file_name => 'NEWTBS.dbf', /* datafile that we will transfer */
source_database => 'LINK_TO_SOURCE', /* name of our Database Link */
destination_directory_object => 'TRANSPORTDB_LOCAL', /* directory in the target DB. */
destination_file_name => 'target-name-NEWTBS.dbf' /* datafile name that we will use in target DB (that name can be the same as source file). */
);
END;
/
zaimportuj przestrzeń do bazy używając impdp:
impdp system/manager TRANSPORT_TABLESPACES=NEWTBS transport_datafiles='/target-name-NEWTBS.dbf' TRANSPORT_FULL_CHECK=YES directory=TRANSPORTDB_LOCAL logfile=transportnewtbs.log network_link=LINK_TO_SOURCE
Jeśli schemat newtbs nie istnieje w bazie docelowej, otrzymasz błąd:
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user NEWUSR does not exist in the database
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at Wed Jan 24 14:41:33 2018 elapsed 0 00:00:06
tak więc – powinieneś utworzyć najpierw schemat w docelowej bazie danych
create user newusr identified by newusr123;
po czym powtórzyć import raz jeszcze:
impdp system/manager TRANSPORT_TABLESPACES=NEWTBS transport_datafiles='/target-name-NEWTBS.dbf' TRANSPORT_FULL_CHECK=YES directory=TRANSPORTDB_LOCAL logfile=transportnewtbs.log network_link=LINK_TO_SOURCE
output mniej więcej jak poniżej:
Import: Release 12.1.0.2.0 - Production on Wed Jan 24 14:45:56 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "system/" TRANSPORT_TABLESPACES=NEWTBS transport_datafiles=/target-name-NEWTBS.dbf TRANSPORT_FULL_CHECK=YES directory=TRANSPORTDB_LOCAL logfile=transportnewtbs.log network_link=LINK_TO_SOURCE
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39083: Object type TABLE:"NEWUSR"."XMLTEST" failed to create with error:
ORA-01031: insufficient privileges
Failing sql is:
CREATE TABLE "NEWUSR"."XMLTEST" OF XMLTYPE OID '6384E54EAA2E0248E0530100007FB4CB' XMLTYPE STORE AS SECUREFILE BINARY XML ( TABLESPACE "NEWTBS" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE(SEG_FILE 124 SEG_BLOCK 137 OBJNO_REUSE 993863 INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CAC
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
ORA-39112: Dependent object type PROCACT_INSTANCE skipped, base object type TABLE:"NEWUSR"."XMLTEST" creation failed
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" completed with 2 error(s) at Wed Jan 24 14:48:34 2018 elapsed 0 00:02:37
Jest już nieco lepiej, prawda? Ale zaskakująco: możesz importować w ten sposób obiekty, lecz nie uda Ci się zaimportować tablicy z kolumną typu XML jak również zaimportować perspektywy zmaterializowanej (ORA-39083 / ORA-01031 ’ insufficient privileges’ Error Using Data Pump to Import Data when the Schema Used to Import has the Necessary Privileges (Doc ID 2224183.1))
rozwiązanie:
albo:
1) Grant the CREATE TABLE and / or CREATE MATERIALIZED VIEW privileges to the owning schema at the source before the export is performed and rerun the export
2) Create the owning schema on the target prior to importing and grant the CREATE TABLE and / or CREATE MATERIALIZED VIEW privileges to this owning schema at the target before importing.
Tak więc usunąłem przestrzeń newtbs (including contents and datafiles), utworzyłem na nowo schemat newusr, jego obiekty, dodałem dla niego przywilej systemowy create table i ponowiłem całą operację (przestrzeń w read-only, transfer plików i import przestrzeni)
impdp system/manager TRANSPORT_TABLESPACES=NEWTBS transport_datafiles='/target-name-NEWTBS.dbf' TRANSPORT_FULL_CHECK=YES directory=TRANSPORTDB_LOCAL logfile=transportnewtbs.log network_link=LINK_TO_SOURCE
Import: Release 12.1.0.2.0 - Production on Wed Jan 24 15:23:30 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "system/" TRANSPORT_TABLESPACES=NEWTBS transport_datafiles=/target-name-NEWTBS.dbf TRANSPORT_FULL_CHECK=YES directory=TRANSPORTDB_LOCAL logfile=transportnewtbs.log network_link=LINK_TO_SOURCE
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Wed Jan 24 15:25:43 2018 elapsed 0 00:02:11
teraz wszystko gra – ale musisz pamiętać – ta przestrzeń jest wciąż w trybie read-only
Teraz możesz sprawdzić czy wszystkie obiekty się skopiowały:
connect newusr/newusr123
select * from new_table;
A B
--- ---
1 one
2 two
3 three
select * from xmltest;
SYS_NC_ROWINFO$
--------------
<toto/>
select count(1) from iot_test;
COUNT(1)
---------
137847
select * from lobtest;
ID L_DATA
---------- ---------
1 firstlob
select * from tztest;
ID D
---------- -----------------------------------
1 18/01/24 13:47:34,770536000
sprawdziliśmy zawartość tabel, więc teraz zalogujmy się jako dba na źródło:
column owner format a10
column object_name format a30
select owner, object_name from dba_objects where owner='NEWUSR';
OWNER OBJECT_NAME
---------- ------------------------------
NEWUSR NEW_TABLE
NEWUSR XMLTEST
NEWUSR TZTEST
NEWUSR SYS_LOB0000993862C00003$$
NEWUSR SYS_IL0000993892C00002$$
NEWUSR SYS_IL0000993862C00003$$
NEWUSR SYS_C00628507
NEWUSR SYS_C00628506
NEWUSR SYS_C00628492
NEWUSR SECREFILE
NEWUSR PK_IOT_TEST
NEWUSR P9
NEWUSR P8
NEWUSR P7
NEWUSR P6
NEWUSR P5
NEWUSR P4
NEWUSR P3
NEWUSR P2
NEWUSR P10
NEWUSR P1
NEWUSR LOBTEST
NEWUSR IOT_TEST
zaś identyczne zapytanie po stronie docelowej bazy:
column owner format a10
column object_name format a30
select owner, object_name from dba_objects where owner='NEWUSR';
OWNER OBJECT_NAME
---------- ------------------------------
NEWUSR NEW_TABLE
NEWUSR SYS_C005255
NEWUSR SYS_C005254
NEWUSR SECREFILE
NEWUSR SYS_IL0000993987C00002$$
NEWUSR LOBTEST
NEWUSR SYS_C005253
NEWUSR SYS_LOB0000993983C00003$$
NEWUSR SYS_IL0000993983C00003$$
NEWUSR XMLTEST
NEWUSR TZTEST
NEWUSR PK_IOT_TEST
NEWUSR IOT_TEST
13 rows selected
procedury jako obiekty nie przechowywane w kopiowanej przestrzeni nie zostały skopiowane, zauważ również, że część obiektów których nazwy zostały nadane przez system bazy – zostały nadane na nowo i różnią się od tych z bazy źródłowej (SYS_%)
Jeszcze rzut oka na loby – czy są w przenoszonej przestrzeni – faktycznie tak jest:
COLUMN table_name FORMAT A20
COLUMN column_name FORMAT A20
COLUMN tablespace_name FORMAT A20
select table_name, column_name,in_row, tablespace_name
from dba_lobs
where owner = 'NEWUSR';
TABLE_NAME COLUMN_NAME IN_ROW TABLESPACE_NAME
--------------- ------------------ ------ ---------------
XMLTEST XMLDATA YES NEWTBS
LOBTEST L_DATA YES NEWTBS
teraz możemy już zmienić tryb pracy przestrzeni na read-write:
alter tablespace newtbs read write;
tablespace NEWTBS altered.
Zauważ że importowaliśmy przestrzeń jako system, nie zaś sys. W zasadzie to mogliśmy to robić również z konta posiadającego niższe uprawnienia (role DATAPUMP_EXP_FULL_DATABASE i DATAPUMP_IMP_FULL_DATABASE)
robiliśmy to również z wiersza linii poleceń – zalecam jednak Ci korzystanie z pliku parametrów (parfile) – po pierwsze, jest to wygodniejsze – ale również – co jest bardzo ważne – hasło nie jest widoczne w listingu procesów serwera.
Gdybyśmy jednak uparli się aby uczynić to z konta sys to powinniśmy użyć polecenia:
expdp/impdp "/ as sysdba"
podziękowania za:
kod obiektów: https://martincarstenbach.wordpress.com/2015/10/08/example-of-full-transportable-export-to-create-a-12c-pdb/
support.oracle.com: Doc ID 2224183.1