Data migration between Oracle's different users in a production environment. Part III

Task Name: Schema Elon Data migration to schema TIAN in the production environment
########################################
Pre-Preparation:
1: Confirm the object status under the Elon user
Select owner,constraint_name,constraint_type,table_name,status,validated from dba_constraints where owner= ' ELON '
Owner Constraint_name constraint_type table_name Status validated
elonfk_dudect_result_agreement_id R T_dudect_resultdisablednot VALIDATED

There is a FOREIGN key constraint state of Disablednot VALIDATED

2: Confirm that the object of two users has no duplicate name
Sql>select distinct (object_type) from dba_objects where owner= ' ELON ';
SEQUENCE
TRIGGER
TABLE
INDEX
Sql>select Count (*) from dba_objects where owner= ' ELON ';
56
Sql>select Count (*) from dba_objects where owner= ' TIAN ';
283

[Email protected] ~]$ diff tian_object.txt Elon_object.txt
Or: SELECT COUNT (*) from dba_objects where owner= ' ELON ' and object_name not in (select object_name from Dba_objects where O Wner= ' TIAN ');
56
Conclusion: No Duplicate object

3: Determine whether the constraints of two users have duplicate names
Sql>select Count (*) from dba_constraints where owner= ' TIAN ';
1441
Sql>select Count (*) from dba_constraints where owner= ' ELON ';
179
[Email protected] ~]$ Sqlplus/as sysdba <tian_con.sql
[[Email protected] ~]$ diff tian_con1.txt elon_con1.txt > diff.txt
You can also troubleshoot this:
Sql>select Count (*) from dba_constraints where owner= ' ELON ' and constraint_name not in (select Constraint_name from DBA _constraints where owner= ' TIAN ');
179
Conclusion: No constraint with duplicate name

4: Determine Elon Default data table space, temp table space, Index table space
Sql>select username,default_tablespace,temporary_tablespace from dba_users where username = ' ELON ';
Elonelon_data01elon_temp
Sql>select username,default_tablespace,temporary_tablespace from dba_users where username = ' TIAN ';
Tiantian_data01tian_temp
Sql>select owner,index_name,tablespace_name from dba_indexes where owner= ' ELON ';
Elonidx_req_dateelon_data01
elonfk_id ELON_DATA01
Elonidx_t_du_res ELON_DATA01
elonpk_t_tempelon_idx01
Elonpk_t_sys_exception elon_idx01
Elonpk_t_message elon_idx01

5: Determine the Dump directory and directory size
Sql> select * from Dba_directories;
OWNER directory_name Directory_path origin_con_id
---------- ------------------------- -------------------------------------------------- -------------
SYS Expdp_dir/data/backup

6: Stop the service and make sure no new data is written:

7: Data pump export Elon data and backup Tian data:

7.1 Data Pump export Elon data and backup Tian data

EXPDP system/xxxxxx schemas=elon directory=expdp_dir dumpfile =elon_ ' date + '%y%m%d%h%m%s '. DMP logfile=ELON_ ' date + '%Y %m%d%h%m%s "' _exp.log

EXPDP system/xxxxxx Schemas=tian directory=expdp_dir dumpfile =tian_ ' date + '%y%m%d%h%m%s '. DMP logfile=TIAN_ ' date + '%Y %m%d%h%m%s "' _exp.log

7.2 to the Tian schema, use the data pump to import Elon data:

1) Query tablespace usage before importing data to Tian:
sql> Select Df.tablespace_name, Totalspace total_size, (totalspace-freespace) used_ Size,freespace Avail_size, round ((1-freespace/totalspace) *100) as Used_ratio
2 from (select Tablespace_name,round ( Sum (bytes)/1024/1024) Totalspace
from Dba_data_files Group by Tablespace_name) DF, (select Tablespace_name,round ( Sum (bytes)/1024/1024) FreeSpace
4 from Dba_free_space Group by Tablespace_name) FS where df.tablespace_name=fs.table Space_name;
Tablespace_name total_size used_size avail_size used_ratio
------------------------------------------------ ----------------------
tian_data01 20480 8087 12393, elon_idx01 10240, 10152 1
Sysaux 3072 1739 1333
UNDOTBS1 83910 8225 75685
elon_data01 20480 1979 18501
tian_idx01 10240 3591 6649
USERS 1024x768 2 1022 0
system 2048 845 1203

Rows selected.
Sql> Select a.use_sp tablespace,
Round ((use_size + free_size), 4) | | ' G ' total space,
Round (use_size,4) | | ' G ' has been used,
Round (free_size,4) | | ' G ' available,
Round (free_size/(Use_size + free_size), 4) * 100 | | '% ' available percentage
from (Select/*+rule (a) +*/tablespace_name use_sp,
sum (bytes)/1024/1024/1024 use_size
from Dba_ Segments
Group by Tablespace_name) A,
(select Tablespace_name use_sp,
sum (bytes)/1024/1024/1024 Free_siz E
from Dba_free_space
Group by tablespace_name) b
where a.use_sp = b.use_sp;

Total tablespace space is used as available percentage
---------------------------------------------------------------------------
Tian_ DATA01 20.0723G 7.9698G 12.1025G 60.29%
tian_idx01 10.0049G 3.5115G 6.4934G 64.9%
elon_data01 19.998G 1.9305G 18.06 76G 90.35%
elon_idx01 9.999G. 0852G 9.9138G 99.15%
Sysaux 3.0801G 1.7782G 1.3019G 42.27%
SYSTEM 1.999G. 8244G 1.1 746G 58.76%
UNDOTBS1 81.9404G 8.0297G 73.9107G 90.2%
USERS. 999G. 0007G. 9984G 99.93%
rows selected.

2) Import data
[[[email protected] ~] $IMPDP system/xxxxxx directory=expdp_dir dumpfile=elon_20180412224710.dmp Logfile=tian_ ' date + "%y%m%d%h%m%s" ' _impdp.log Remap_schema=elon:tian remap_tablespace=elon_data01:tian_data01, Elon_idx01:tian_idx01,elon_temp:tian_temp Table_exists_action=skip
2), when you are finished, look at tablespace utilization, data growth is normal
Sql> Select A.USE_SP tablespace,
Round ((use_size + free_size), 4) | | ' G ' total space,
Round (use_size,4) | | ' G ' has been used,
Round (free_size,4) | | ' G ' available,
Round (free_size/(Use_size + free_size), 4) * 100 | | '% ' available percentage
Group by Tablespace_name) b
from (Select/*+rule (a) +*/tablespace_name use_sp,
sum (bytes)/1024/10 24/1024 use_size
from Dba_segments
Group by Tablespace_name) A,
(select Tablespace_name use_sp,
sum (byte s)/1024/1024/1024 free_size
from Dba_free_space
Group by tablespace_name) b
where a.use_sp = b.use_sp;

Total tablespace space already used available percentage
--------------- ---------- --------------- --------------- --------------------
Tian_data01 20.0723G 9.9618G 10.1105G 50.37%
tian_idx01 10.0049G 3.6025G 6.4023G 63.99%
Elon_data01 19.998G 1.9305G 18.0676G 90.35%
elon_idx01 9.999G. 0852G 9.9138G 99.15%
Sysaux 3.0801G 1.7806G 1.2996G 42.19%
SYSTEM 1.999G. 8244G 1.1746G 58.76%
UNDOTBS1 81.9404G 8.1352G 73.8052G 90.07%
USERS. 999G. 0007G. 9984G 99.93%

Rows selected.

7.3 Monitor the working status of the data pump:
sql> select * from Dba_datapump_jobs;
Sql> select * from Dba_datapump_sessions;
sql> SELECT opname, Target_desc, Sofar, totalwork from V$session_longops;
8: Verify
Total number of objects Tian after migration
Sql> Select COUNT (*) from dba_objects where owner= ' TIAN ';
339
is: 283+56
ELON objects are migrated to Tian
Sql>select count (*) from dba_objects where owner= ' ELON ' and object_name Not in (select object_name from dba_objects where owner= ' TIAN ');
0
After migration TIAN total number of Constraints
Sql> select COUNT (*) from dba_constraints where owner= ' TIAN ';
1620
Select COUNT (*) from dba_constraints where owner= ' ELON ' and constraint_name not in (select Constraint_name from DB a_constraints where owner= ' TIAN ');
155

#因为ELON下包含155个非空约束, 17 primary KEY constraints, 7 foreign KEY constraints, non-null constraints, System generation, in the export import process, the constraint name changes (such as sys_c0017556)
9: Reclaim Elon Permissions
Sql> SELECT * from Dba_sys_privs where grantee= ' ELON '
Eloncreate Tablenono
Eloncreate Viewnono
Elondebug CONNECT Sessionnono
Elondebug any Procedurenono
Eloncreate DATABASE Linknono
Sql> Revoke

Data migration between Oracle's different users in a production environment. Part III