Transport Oracle 11g (11.2.0.3) Database to Oracle 12c (12.1.0.2) Database
### Source DB Server (Oracle 11g)
# make non-system tablespaces read-only
[oracle@testsrv11g ~]$ . oraenv
ORACLE_SID = [test11g] ?
sqlplus / as sysdba
begin
for rec in (select tablespace_name as t from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP') loop
execute immediate 'alter tablespace '||rec.t||' read only';
end loop;
end;
/
test11g> SELECT COUNT(*) from DBA_TABLES where owner = 'TESTUSER';
COUNT(*)
----------
28
test11g> SELECT COUNT(*) from TESTUSER.TEST_TABLE;
COUNT(*)
----------
296
# Export Oracle 11g database
read -s mypwd
expdp system/$mypwd@test11g full=y dumpfile=test11g.dmp directory=DATA_PUMP_DIR transportable=always version=12 logfile=test11g.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 68.81 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
...
...
...
...
...
...
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/oracle/admin/test11g/dpdump/test11g.dmp
******************************************************************************
Datafiles required for transportable tablespace TEST_DATA:
+DATA/test11g/datafile/test_data.333.841251643
Datafiles required for transportable tablespace TEST_INDX:
+DATA/test11g/datafile/test_indx.334.841251649
Datafiles required for transportable tablespace USERS:
+DATA/test11g/datafile/users.335.841251653
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 11:28:52
### Destination DB Server (Oracle 12c)
CDB$ROOT@MY12C> create pluggable database test11g admin user pdbadmin identified by oracle;
CDB$ROOT@MY12C> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------- -------------------- ----------
2 PDB$SEED READ ONLY NO
3 PDBMY12C READ WRITE NO
4 MYPDB READ WRITE NO
5 MYPDB2 READ WRITE NO
6 test11g MOUNTED
CDB$ROOT@MY12C> alter pluggable database test11g open;
CDB$ROOT@MY12C> select file_name from cdb_data_files where con_id = 6;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------
+DATA/MY12C/2A741E0ACC3C1B87E0538501000A05C7/DATAFILE/system.426.902413891
+DATA/MY12C/2A741E0ACC3C1B87E0538501000A05C7/DATAFILE/sysaux.427.902413895
# Copy Tablespace Data Files
scp 168.95.1.15:/backup/dpdump/test11g/TEST*.* /backup/data_pump/test11g
scp 168.95.1.15:/backup/dpdump/test11g/USERS*.* /backup/data_pump/test11g
# Copy Data Pump Export Files
scp 168.95.1.15:/oracle/admin/test11g/dpdump/test11g*.* /backup/data_pump/test11g
ls -l cd /backup/data_pump/test11g
/backup/data_pump/test11g:
total 1189980
-rw-r----- 1 oracle dba 524296192 Jan 29 14:43 TEST_DATA.333.841251643
-rw-r----- 1 oracle dba 524296192 Jan 29 14:43 TEST_INDX.334.841251649
-rw-r----- 1 oracle dba 52436992 Jan 29 14:43 USERS.335.841251653
-rw-r----- 1 oracle dba 116240384 Jan 29 15:36 test11g_full.dmp
-rw-r--r-- 1 oracle dba 53423 Jan 29 15:36 test11g_full.log
su - grid
asmcmd -p
ASMCMD [+] > cd +DATA/MY12C/2A741E0ACC3C1B87E0538501000A05C7/DATAFILE
cp /backup/data_pump/test11g/TEST_DATA.333.841251643 +DATA/MY12C/2A741E0ACC3C1B87E0538501000A05C7/DATAFILE/TEST_DATA
cp /backup/data_pump/test11g/TEST_INDX.334.841251649 +DATA/MY12C/2A741E0ACC3C1B87E0538501000A05C7/DATAFILE/TEST_INDX
cp /backup/data_pump/test11g/USERS.335.841251653 +DATA/MY12C/2A741E0ACC3C1B87E0538501000A05C7/DATAFILE/USERS
ASMCMD [+data/my12c/2A741E0ACC3C1B87E0538501000A05C7/datafile] > ls -l
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE JAN 29 14:00:00 N TEST_DATA => +DATA/ASM/DATAFILE/TEST_DATA.429.902415199
DATAFILE MIRROR COARSE JAN 29 14:00:00 N TEST_INDX => +DATA/ASM/DATAFILE/TEST_INDX.430.902415229
DATAFILE MIRROR COARSE JAN 29 14:00:00 Y SYSAUX.427.902413895
DATAFILE MIRROR COARSE JAN 29 14:00:00 Y SYSTEM.426.902413891
DATAFILE MIRROR COARSE JAN 29 14:00:00 N USERS => +DATA/ASM/DATAFILE/USERS.431.902415293
CDB$ROOT@MY12C> alter session set container=test11g;
Session altered.
CDB$ROOT@MY12C> sho con_name
CON_NAME
------------------------------
test11g
CDB$ROOT@MY12C> create directory dump_dir as '/backup/data_pump/test11g';
# import Oracle 11g database dump file to Oracle 12c
export TWO_TASK=test11g
read -s mypwd
impdp system/$mypwd full=y directory=dump_dir dumpfile=test11g_full.dmp transport_datafiles='+DATA/ASM/DATAFILE/TEST_DATA.429.902415199','+DATA/ASM/DATAFILE/TEST_INDX.430.902415229','+DATA/ASM/DATAFILE/USERS.431.902415293'
sqlplus system/$mypwd
test11g@MY12C> sho con_name
CON_NAME
------------------------------
test11g
test11g@MY12C> select status from v$instance;
STATUS
------------------------------------
OPEN
# Verify the Result
test11g@MY12C> SELECT COUNT(*) from DBA_TABLES where owner = 'TESTUSER';
COUNT(*)
----------
28
test11g@MY12C> SELECT COUNT(*) from TESTUSER.TEST_TABLE;
COUNT(*)
----------
296
[Reference]
https://docs.oracle.com/database/121/ADMIN/transport.htm#ADMIN13726
留言列表
{{ article.title }}