Thứ Tư, 18 tháng 8, 2021

Chuyển Schema từ Oracle Database 11G sang 12C PDB

Mục đích: Chuyển Schema từ Oracle Database 11G sang 12C 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>


2. TẠO THƯ MỤC

[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>


3. EXPORT SCHEMA

[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


4. CHUYỂN FILE DUMP SANG ĐÍCH

[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


8. Verify số lượng object

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>


10. TẠO THƯ MỤC

[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>


11. Import

-- 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 ~]$


12. KIỂM TRA

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>


13. Gather schema stats

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

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master