Mục đích: Chuyển Schema từ Oracle Database 11G sang 12c/19c PDB
NGUỒN (PROD)
0. Thiết lập môi trường
1. Lấy thông tin Schema trước khi export
2. Tạo thư mục
3. Export
4. Chuyển file dump sang đích
ĐÍCH (UAT)
5. Kiểm tra schema trên UAT
6. Backup schema trên UAT
7. Drop schema objects (KHÔNG thực hiện trên PROD)
8. Kiểm tra số object
9. Purge Recycle bin (user_Recycle)
10. Tạo thư mục
11. Import
12. Kiểm tra
13. Gather schema stats
___________________________________________________________________________
**********NGUỒN - PROD**********
0. THIẾT LẬP MÔI TRƯỜNG
SOURCE Hostname : RAC1 Database Name : DELL Schema Name : SCOTT TABLESPACE_NAME : USERS DB VERSION : 11.2.0.4.0 TARGET Hostname : RAC2 CDB Name : CDB1 PDB NAME : PDB1 Schema Name : SCOTT_UAT TABLESPACE_NAME : SCOTT_UAT_DATA DB VERSION : 12.2.0.1.0
1. LẤY THÔNG TIN SCHEMA TRƯỚC KHI EXPORT
sqlplus / as sysdba spool pre-verify.log set lines 180 select name,open_mode from v$database; select username,account_status,default_tablespace from dba_users where username='&owner'; select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner='&owner' group by owner; select owner,object_type,count(*) from dba_objects where owner='&owner' and object_name not like 'BIN$%' group by object_type,owner order by object_type; select count(*) from dba_objects where owner='&owner' and status='INVALID'; spool off SQL> set lines 180 SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- DELL READ WRITE SQL> select username,account_status,default_tablespace from dba_users where username='SCOTT'; USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE ------------------------------ -------------------------------- ------------------------------ SCOTT OPEN USERS SQL> select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner='SCOTT' group by owner; OWNER SIZE in MB ------------------------------ ---------- SCOTT .3125 SQL> select owner,object_type,count(*) from dba_objects where owner='SCOTT' and object_name not like 'BIN$%' group by object_type,owner order by object_type; OWNER OBJECT_TYPE COUNT(*) ------------------------------ ------------------- ---------- SCOTT INDEX 2 SCOTT TABLE 4 SQL> SQL> select count(*) from dba_objects where owner='SCOTT' and status='INVALID'; COUNT(*) ---------- 0 SQL>
[oracle@rac1 ~]$ mkdir -p /u02/DUMP_DIR [oracle@rac1 ~]$ chmod 775 /u02/DUMP_DIR [oracle@rac1 DUMP_DIR]$ df -h /u02/DUMP_DIR Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup-lv_home 43G 23G 19G 56% /u02 [oracle@rac1 DUMP_DIR]$ SQL> create or replace directory DUMP_DIR as '/u02/DUMP_DIR'; Directory created. SQL> SQL> SET LINES 190 SQL> col DIRECTORY_PATH for a40; SQL> select * from dba_directories where DIRECTORY_NAME='DUMP_DIR'; OWNER DIRECTORY_NAME DIRECTORY_PATH -------- --------------- ---------------- SYS DUMP_DIR /u02/DUMP_DIR SQL>
[oracle@srv ~]$ . oraenv ORACLE_SID = [DELL] ? [oracle@rac1 ~]$ nohup expdp \'/ as sysdba\' directory=DUMP_DIR dumpfile=scott_%U.dmp logfile=EXP_SCOTT.log schemas=scott parallel=4 & [oracle@rac1 ~]$ expdp \'/ as sysdba\' directory=DUMP_DIR dumpfile=scott_%U.dmp logfile=EXP_SCOTT.log schemas=scott parallel=4 Export: Release 11.2.0.4.0 - Production on Tue Aug 14 21:08:17 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_02": "/******** AS SYSDBA" directory=DUMP_DIR dumpfile=scott_%U.dmp logfile=EXP_SCOTT.log schemas=scott parallel=4 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB Processing object type SCHEMA_EXPORT/USER . . exported "SCOTT"."DEPT" 5.937 KB 4 rows Processing object type SCHEMA_EXPORT/SYSTEM_GRANT . . exported "SCOTT"."EMP" 8.570 KB 14 rows Processing object type SCHEMA_EXPORT/ROLE_GRANT . . exported "SCOTT"."SALGRADE" 5.867 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows 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/CONSTRAINT/REF_CONSTRAINT Master table "SYS"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_02 is: /u02/DUMP_DIR/scott_01.dmp /u02/DUMP_DIR/scott_02.dmp /u02/DUMP_DIR/scott_03.dmp Job "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at Tue Aug 14 21:08:39 2018 elapsed 0 00:00:21
[oracle@rac1 DUMP_DIR]$ scp scott*.dmp oracle@rac2:/u02/DUMP_DIR_12C
oracle@rac2's password:
scott_01.dmp 100% 32KB 32.0KB/s 00:00
scott_02.dmp 100% 36KB 36.0KB/s 00:00
scott_03.dmp 100% 172KB 172.0KB/s 00:00
[oracle@rac1 DUMP_DIR]$
********* ĐÍCH - UAT**********
5. KIỂM TRA CHI TIẾT SCHEMA
sqlplus sys@pdb1 as sysdba spool before_Drop_schema.log set lines 180 select name,open_mode from v$database; select username,account_status,default_tablespace from dba_users where username='&owner'; select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner='&owner' group by owner; select owner,object_type,count(*) from dba_objects where owner='&owner' and object_name not like 'BIN$%' group by object_type,owner order by object_type; -- Trước khi import kiểm tra tablepspace còn đủ dung lượng không set linesize 132 tab off trimspool on set pagesize 105 set pause off set echo off set feedb on column "TOTAL ALLOC (MB)" format 9,999,990.00 column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00 column "USED (MB)" format 9,999,990.00 column "FREE (MB)" format 9,999,990.00 column "% USED" format 990.00 select a.tablespace_name, a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)", a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)", nvl(b.tot_used,0)/(1024*1024) "USED (MB)", (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED" from ( select tablespace_name, sum(bytes) physical_bytes, sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc from dba_data_files group by tablespace_name ) a, ( select tablespace_name, sum(bytes) tot_used from dba_segments group by tablespace_name ) b where a.tablespace_name = b.tablespace_name (+) --and (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10 --and a.tablespace_name in ('NONEED') --and a.tablespace_name not like 'UNDO%' and a.tablespace_name='&tbs' --- like 'Noneed%' order by 1 --order by 5 / col username for a20 select * from dba_ts_quotas where username='&username'; select count(*) from dba_objects where owner='&owner' and status='INVALID'; spool off SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PDB1 READ WRITE NO SQL> select * from dba_ts_quotas where username='SCOTT_UAT'; TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO ------------------------------ -------------------- ---------- ---------- ---------- ---------- --- SCOTT_UAT_DATA SCOTT_UAT 0 -1 0 -1 NO SQL> select username,account_status,default_tablespace from dba_users where username='SCOTT_UAT'; USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE -------------------- -------------------------------- ------------------------------ SCOTT_UAT OPEN SCOTT_UAT_DATA SQL>
6. Backup schema scott trên UAT
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [CDB1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$
nohup expdp \"sys@pdb1 AS SYSDBA\" directory=DUMP_DIR dumpfile=scott_uat_%U.dmp logfile=EXP_SCOTT_UAT.log schemas=scott_uat parallel=4 &
7. Drop schema objects (KHÔNG THỰC HIỆN TRÊN PROD)
sqlplus sys@pdb1 as sysdba *** DROP SCHEMA OBJECTS set head off set pagesize 0 set linesize 300 spool schemaname_drop_obj.sql select 'drop '||object_type||' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') from dba_objects where owner in ('&owner') and object_type not in ('DATABASE LINK','JOB') order by object_type,object_name / spool off; set head on *** DROP OBJECTS KHÁC (Nếu cần) set head off set pagesize 0 set linesize 300 spool schemaname_drop.sql select 'DROP '||decode(object_type, 'PACKAGE BODY', 'PACKAGE',object_type)|| ' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') from dba_objects where owner=upper('&owner') and object_type not in ('TABLE') / spool off; set head on *** Chạy script !cat schemaname_drop_obj.sql @schemaname_drop_obj.sql
sqlplus sys@pdb1 as sysdba SQL> select owner,object_type,count(*) from dba_objects where owner='SCOTT' and object_name not like 'BIN$%' group by object_type,owner order by object_type; no rows selected SQL>
9. Purge Recycle bin (user_recyebin)
Users can purge the recycle bin of their own objects and release space for objects by using the following statement: *** KHÔNG THỰC HIỆN TRÊN USER SYS [oracle@rac1 ~]$ sqlplus sys@pdb1 as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 14 23:01:03 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> alter session set current_schema=SCOTT_UAT; Session altered. SQL> purge recyclebin; Recyclebin purged. SQL> SELECT * FROM USER_RECYCLEBIN; no rows selected SQL>
[oracle@rac2 ~]$ ls -ltr /u02/DUMP_DIR_12C -rw-r-----. 1 oracle dba 32768 Aug 14 21:44 scott_01.dmp -rw-r-----. 1 oracle dba 36864 Aug 14 21:44 scott_02.dmp -rw-r-----. 1 oracle dba 176128 Aug 14 21:44 scott_03.dmp [oracle@rac2 ~]$ sqlplus sys@pdb1 as sysdba SQL> create or replace directory DUMP_DIR_12C as '/u02/DUMP_DIR_12C'; Directory created. SQL> SQL> SET LINES 190 SQL> col owner for a20 SQL> col DIRECTORY_PATH for a40; SQL> col DIRECTORY_NAME for a20 SQL> select * from dba_directories where DIRECTORY_NAME='DUMP_DIR_12C'; OWNER DIRECTORY_NAME DIRECTORY_PATH -------- --------------- ---------------- SYS DUMP_DIR /u02/DUMP_DIR SQL>
-- Do source tablespace mình không biết nên không dùng REMAP_TABLESPACE. Trong trường hợp này bạn có 2 mệnh đề khi import "transform=segment_attributes:n transform=OID:n" thay thế cho dùng REMAP_TABLESPACE [oracle@rac2 ~]$ . oraenv ORACLE_SID = [CDB1] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@rac1 ~]$ -- Chạy nohup nohup impdp \"sys@pdb1 AS SYSDBA\" directory=DUMP_DIR_12C dumpfile=scott_%U.dmp logfile=IMP_SCOTT.log remap_schema=SCOTT:SCOTT_UAT transform=segment_attributes:n transform=OID:n parallel=4 & -- Hoặc chạy bình thường [oracle@rac2 ~]$ impdp \"sys@pdb1 AS SYSDBA\" directory=DUMP_DIR_12C dumpfile=scott_%U.dmp logfile=IMP_SCOTT.log remap_schema=SCOTT:SCOTT_UAT transform=segment_attributes:n transform=OID:n parallel=4 Import: Release 12.2.0.1.0 - Production on Tue Aug 14 22:35:34 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded import done in AL32UTF8 character set and AL16UTF16 NCHAR character set export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set Warning: possible data loss in character set conversions Starting "SYS"."SYS_IMPORT_FULL_01": "sys/********@pdb1 AS SYSDBA" directory=DUMP_DIR_12C dumpfile=scott_%U.dmp logfile=IMP_SCOTT.log remap_schema=SCOTT:SCOTT_UAT transform=segment_attributes:n transform=OID:n parallel=4 Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"SCOTT_UAT" 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 "SCOTT_UAT"."DEPT" 5.937 KB 4 rows . . imported "SCOTT_UAT"."EMP" 8.570 KB 14 rows . . imported "SCOTT_UAT"."SALGRADE" 5.867 KB 5 rows . . imported "SCOTT_UAT"."BONUS" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue Aug 14 22:35:44 2018 elapsed 0 00:00:08 [oracle@rac2 ~]$
sqlplus sys@pdb1 as sysdba select owner,object_type,count(*) from dba_objects where owner='SCOTT_UAT' and object_name not like 'BIN$%' group by object_type,owner order by object_type; OWNER OBJECT_TYPE COUNT(*) --------------- -------------------- ---------- SCOTT_UAT INDEX 2 SCOTT_UAT TABLE 4 SQL> SQL> select count(*) from dba_objects where owner='SCOTT_UAT' and status='INVALID'; COUNT(*) ---------- 0 <------- SQL>
exec dbms_stats.gather_schema_stats(ownname=>'&schema_name', CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4); [oracle@rac1 ~]$ sqlplus sys@pdb1 as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 14 22:42:24 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> exec dbms_stats.gather_schema_stats(ownname=>'&schema_name', CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4); Enter value for schema_name: SCOTT_UAT <---- PL/SQL procedure successfully completed. SQL>
Hy vọng hữu ích cho bạn.
=============================* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
hoặc
https://bit.ly/oaz_fp
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,khóa học pl/sql, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle dataguard, oracle goldengate, oracle weblogic, oracle exadata, hoc solaris, hoc linux, hoc aix
