模擬11G單例項到12C的資料遷移過程

shilei1發表於2017-03-15
單例
在實際專案中，可能資料量、環境和客戶要求都比此次試驗規模龐大、情況複雜，但是萬變不離其宗，切記一定實現規劃好備份空間，密切的關注備份狀態。
下面是11G的單例項模擬遷移到12C
11G的環境資訊：
SQL> select * from cat;
TABLE_NAME        TABLE_TYPE
------------------------------ -----------
T        TABLE
SQL> show user
USER is "TEST"
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> alter table t move tablespace test;
Table altered.
12C的環境資訊：
[oracle@edsir4p1-PROD1 ~]$ expdp test/test directory=dir dumpfile=fullbak.dmp  logfile=fullbak.log full=y
Export: Release 11.2.0.1.0 - Production on Mon Apr 11 06:33:12 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_FULL_01":  test/******** directory=dir dumpfile=fullbak.dmp logfile=fullbak.log full=y 
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 119.7 MB
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
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT

[oracle@edsir4p1-PROD1 ~]$ expdp test/test directory=dir dumpfile=testbak.dmp  logfile=testbak.log schemas=test parallel=4

Export: Release 11.2.0.1.0 - Production on Mon Apr 11 07:02:50 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/******** directory=dir dumpfile=testbak.dmp logfile=testbak.log schemas=test parallel=4 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "TEST"."T"                                  5.046 KB       5 rows
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/FGA_POLICY
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/testbak.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 07:03:24

12C環境，建立PDB
SQL> create pluggable database PROD1 admin user test identified by test storage (maxsize 2G max_shared_temp_size 100M) default tablespace test datafile '/oradata/orcl12c/prod1/testdb.dbf' size 25m autoextend on path_prefix='/oradata/orcl12c/prod1/' file_name_convert=('/oradata/orcl12c/pdbseed/','/oradata/orcl12c/prod1/');
Pluggable database created.
SQL> alter pluggable database PROD1 open;
Pluggable database altered.
SQL> show pdbs
    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
 2 PDB$SEED   READ ONLY  NO
 3 PDB3    MOUNTED
 4 PROD1   READ WRITE NO

[oracle@host01 ~]$ tnsping prod1
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 11-APR-2016 03:00:20
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.60)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod1)))
OK (0 msec)
[oracle@host01 ~]$ sqlplus test/test@prod1
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 11 03:00:31 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Mon Apr 11 2016 02:57:59 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show con_id con_name
CON_ID
------------------------------
4
CON_NAME
------------------------------
PROD1
SQL> show user
USER is "TEST"

將兩個到處檔案傳遞到12C
[oracle@host01 ~]$ ls *.dmp
fullbak.dmp  testbak.dmp

建立目錄
SQL> create directory dir as '/home/oracle';
Directory created.
附權操作
sqlplus / as sysdba
alter session set  container=PROD1; 
grant dba to test;
select * from user_role_privs；
USERNAME        GRANTED_ROLE       ADM DEL DEF OS_
------------------------------ ------------------------------ --- --- --- ---
COM
---
TEST        CONNECT       NO  NO  YES NO
NO
TEST        DBA       NO  NO  YES NO
NO
TEST        PDB_DBA       YES NO  YES NO
NO
USERNAME        GRANTED_ROLE       ADM DEL DEF OS_
------------------------------ ------------------------------ --- --- --- ---
COM
---
TEST        RESOURCE        NO  NO  YES NO
NO
執行匯入：
[oracle@host01 ~]$ impdp test/test@PROD1 directory=dir dumpfile=fullbak.dmp logfile=imptab.log tables=t
Import: Release 12.1.0.2.0 - Production on Mon Apr 11 03:23:10 2016
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, OLAP, Advanced Analytics and Real Application Testing options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
WARNING: possible data loss in character set conversions
Starting "TEST"."SYS_IMPORT_TABLE_01":  test/********@PROD1 directory=dir dumpfile=fullbak.dmp logfile=imptab.log tables=t 
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "TEST"."T"                                  5.046 KB       5 rows
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at Mon Apr 11 03:23:45 2016 elapsed 0 00:00:33

[oracle@host01 ~]$ sqlplus test/test@prod1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 11 03:24:42 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Apr 11 2016 03:23:10 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select * from t;
ID
----------
 1
 2
 3
 4
 5
[oracle@host01 ~]$ impdp test/test@PROD1 directory=dir dumpfile=testbak.dmp logfile=imptest.log tables=t

Import: Release 12.1.0.2.0 - Production on Mon Apr 11 03:25:40 2016

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, OLAP, Advanced Analytics and Real Application Testing options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
WARNING: possible data loss in character set conversions
Starting "TEST"."SYS_IMPORT_TABLE_01":  test/********@PROD1 directory=dir dumpfile=testbak.dmp logfile=imptest.log tables=t 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T"                                  5.046 KB       5 rows
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at Mon Apr 11 03:25:48 2016 elapsed 0 00:00:05

[oracle@host01 ~]$ expdp test/test@prod1 estimate_only=y directory=dir   logfile=n full=y 

Export: Release 12.1.0.2.0 - Production on Mon Apr 11 03:29:53 2016

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, OLAP, Advanced Analytics and Real Application Testing options
Starting "TEST"."SYS_EXPORT_FULL_01":  test/********@prod1 estimate_only=y directory=dir logfile=n full=y 
Estimate in progress using BLOCKS method...
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
.  estimated "SYS"."KU$_USER_MAPPING_VIEW"                  16 KB
.  estimated "ORDDATA"."ORDDCM_DOCS"                      1.25 MB
.  estimated "WMSYS"."WM$CONSTRAINTS_TABLE$"               320 KB
.  estimated "SYS"."AUD$"                                  192 KB
.  estimated "WMSYS"."WM$LOCKROWS_INFO$"                   192 KB
.  estimated "WMSYS"."WM$UDTRIG_INFO$"                     192 KB
.  estimated "LBACSYS"."OLS$AUDIT_ACTIONS"                  64 KB
.  estimated "LBACSYS"."OLS$DIP_EVENTS"                     64 KB
.  estimated "LBACSYS"."OLS$INSTALLATIONS"                  64 KB
.  estimated "LBACSYS"."OLS$PROPS"                          64 KB
.  estimated "SYS"."DAM_CONFIG_PARAM$"                      64 KB
.  estimated "SYS"."TSDP_PARAMETER$"                        64 KB
.  estimated "SYS"."TSDP_POLICY$"                           64 KB
.  estimated "SYS"."TSDP_SUBPOL$"                           64 KB
.  estimated "SYSTEM"."REDO_DB"                             64 KB
.  estimated "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$"        64 KB
.  estimated "WMSYS"."WM$CONS_COLUMNS$"                     64 KB
.  estimated "WMSYS"."WM$ENV_VARS$"                         64 KB
.  estimated "WMSYS"."WM$EVENTS_INFO$"                      64 KB
.  estimated "WMSYS"."WM$HINT_TABLE$"                       64 KB
.  estimated "WMSYS"."WM$MODIFIED_TABLES$"                  64 KB
.  estimated "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$"        64 KB
.  estimated "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$"       64 KB
.  estimated "WMSYS"."WM$NESTED_COLUMNS_TABLE$"             64 KB
.  estimated "WMSYS"."WM$NEXTVER_TABLE$"                    64 KB
.  estimated "WMSYS"."WM$REMOVED_WORKSPACES_TABLE$"         64 KB
.  estimated "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$"         64 KB
.  estimated "WMSYS"."WM$RIC_LOCKING_TABLE$"                64 KB
.  estimated "WMSYS"."WM$RIC_TABLE$"                        64 KB
.  estimated "WMSYS"."WM$RIC_TRIGGERS_TABLE$"               64 KB
.  estimated "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$"            64 KB
.  estimated "WMSYS"."WM$VERSION_HIERARCHY_TABLE$"          64 KB
.  estimated "WMSYS"."WM$VERSION_TABLE$"                    64 KB
.  estimated "WMSYS"."WM$VT_ERRORS_TABLE$"                  64 KB
.  estimated "WMSYS"."WM$WORKSPACES_TABLE$"                 64 KB
.  estimated "WMSYS"."WM$WORKSPACE_PRIV_TABLE$"             64 KB
.  estimated "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$"       64 KB
.  estimated "LBACSYS"."OLS$AUDIT"                           0 KB
.  estimated "LBACSYS"."OLS$COMPARTMENTS"                    0 KB
.  estimated "LBACSYS"."OLS$DIP_DEBUG"                       0 KB
.  estimated "LBACSYS"."OLS$GROUPS"                          0 KB
.  estimated "LBACSYS"."OLS$LAB"                             0 KB
.  estimated "LBACSYS"."OLS$LEVELS"                          0 KB
.  estimated "LBACSYS"."OLS$POL"                             0 KB
.  estimated "LBACSYS"."OLS$POLICY_ADMIN"                    0 KB
.  estimated "LBACSYS"."OLS$POLS"                            0 KB
.  estimated "LBACSYS"."OLS$POLT"                            0 KB
.  estimated "LBACSYS"."OLS$PROFILE"                         0 KB
.  estimated "LBACSYS"."OLS$PROFILES"                        0 KB
.  estimated "LBACSYS"."OLS$PROG"                            0 KB
.  estimated "LBACSYS"."OLS$SESSINFO"                        0 KB
.  estimated "LBACSYS"."OLS$USER"                            0 KB
.  estimated "LBACSYS"."OLS$USER_COMPARTMENTS"               0 KB
.  estimated "LBACSYS"."OLS$USER_GROUPS"                     0 KB
.  estimated "LBACSYS"."OLS$USER_LEVELS"                     0 KB
.  estimated "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB
.  estimated "SYS"."DAM_CLEANUP_JOBS$"                       0 KB
.  estimated "SYS"."TSDP_ASSOCIATION$"                       0 KB
.  estimated "SYS"."TSDP_CONDITION$"                         0 KB
.  estimated "SYS"."TSDP_FEATURE_POLICY$"                    0 KB
.  estimated "SYS"."TSDP_PROTECTION$"                        0 KB
.  estimated "SYS"."TSDP_SENSITIVE_DATA$"                    0 KB
.  estimated "SYS"."TSDP_SENSITIVE_TYPE$"                    0 KB
.  estimated "SYS"."TSDP_SOURCE$"                            0 KB
.  estimated "SYSTEM"."REDO_LOG"                             0 KB
.  estimated "SYSTEM"."SCHEDULER_JOB_ARGS"                 128 KB
.  estimated "SYSTEM"."SCHEDULER_PROGRAM_ARGS"             128 KB
.  estimated "SYS"."AUDTAB$TBS$FOR_EXPORT"                  16 KB
.  estimated "SYS"."DBA_SENSITIVE_DATA"                     16 KB
.  estimated "SYS"."DBA_TSDP_POLICY_PROTECTION"             16 KB
.  estimated "SYS"."FGA_LOG$FOR_EXPORT"                     16 KB
.  estimated "SYS"."NACL$_ACE_EXP"                          16 KB
.  estimated "SYS"."NACL$_HOST_EXP"                         16 KB
.  estimated "SYS"."NACL$_WALLET_EXP"                       16 KB
.  estimated "WMSYS"."WM$EXP_MAP"                           16 KB
.  estimated "TEST"."T"                                     64 KB
Total estimation using BLOCKS method: 4.515 MB
Job "TEST"."SYS_EXPORT_FULL_01" successfully completed at Mon Apr 11 03:30:18 2016 elapsed 0 00:00:23

impdp test/test@PROD1 directory=dir dumpfile=testbak.dmp logfile=imptest.log TABLE_EXISTS_ACTION=replace

Import: Release 12.1.0.2.0 - Production on Mon Apr 11 03:35:40 2016

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, OLAP, Advanced Analytics and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
WARNING: possible data loss in character set conversions
Starting "TEST"."SYS_IMPORT_FULL_01":  test/********@PROD1 directory=dir dumpfile=testbak.dmp logfile=imptest.log TABLE_EXISTS_ACTION=replace 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T"                                  5.046 KB       5 rows
Processing object type SCHEMA_EXPORT/TABLE/FGA_POLICY
Job "TEST"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Mon Apr 11 03:35:49 2016 elapsed 0 00:00:07

此處注意：12c中斷點續傳的功能依舊可以使用
1，stop_job
2，attach=job_name 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/196700/viewspace-2135383/，如需轉載，請註明出處，否則將追究法律責任。

相關文章