迁移数据

并库oracle数据库操作流程
备份数据：    select * from dba_users u where u.username LIKE 'NF%'or u.username like '%DZDA%' or u.username LIKE '%FLOW%';
      排除用户NF_SWRYQX2
              FLOWS_FILES
              NF_CX
	  排除表  select * from dba_tables  t where t.TABLE_NAME like 'NF_SSSL_JYRZ%';
	          select * from dba_tables t where t.TABLE_NAME like 'NF_NFZCPT_QDJY%'


nohup expdp www/www parfile=/u01/QX.par &

QX.par
dumpfile=exp_%U.dmp
Schemas=NF_PZYX,NF_SWRYQX_LIAONING,NF_SFJR,DB_DZDA,NF_CXX,NF_WSBS,IS_FLOW,NF_NSRQX,NF_SWRYQX,NF_NFZCPT,NF_SJRZ,NF_CRYPT,NF_NFZC_FP,NF_SSSL,NF_SXGS,NF_NBB,NF_FXKZ,NF_ECM,NF_SMRZ,NF_PORTAL,NF_SWRYQX2,NF_NFZC,HZGS_IS_FLOW,NF_XTGL,NF_GXGL
DIRECTORY=DP_DIR
logfile=expdp_test.log
parallel=8
compression=all
#TABLES=
#EXCLUDE=INDEX,STATISTICS
exclude=TABLE:"IN(select table_name from dba_tables where table_name like 'NF_SSSL_JYRZ%' or table_name like  'NF_NFZCPT_QDJY%')"	  




1、比对和源端的表空间差异
select * from dba_tablespaces t where t.TABLESPACE_NAME not in('SYSTEM','SYSAUX','UNDOTBS1','TEMP','UNDOTBS2','USERS','GGSJ1','TEMP001','CSSBBFB');   --核心 91

2、在创建缺少的表空间
 select 'select dbms_metadata.get_ddl(' || '''TABLESPACE''' || ','|| '''' || a.tablespace_name || '''' ||
       ')||'';'' as sql_text from dual;'
  from DBA_TABLESPACES a
 where a.TABLESPACE_NAME not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','UNDOTBS2','USERS','GGSJ1','TEMP001','CSSBBFB');
 
3、 --创建表空间
 select 'create tablespace ' || t.TABLESPACE_NAME || ';' from  dba_tablespaces t where t.TABLESPACE_NAME not in('SYSTEM','SYSAUX','UNDOTBS1','TEMP','UNDOTBS2','USERS','GGSJ1','TEMP001','CSSBBFB'); 

 
4、获取核心导出的导出文件  SCN XXXX

5、 导入操作
nohup impdp www/www directory=DP_DIR  dumpfile=hxsj_%U.dmp logfile=impdp_hx20190121.log  parallel=8 table_exists_action=replace  > hx_20190121.out 2>&1  &

6、监控导入日志，出现表空间不足的，添加表空间
alter tablespace tablespace_name add datafile;
 
7、对比源端和的用户表数量和记录数
--核查表的数量
select t.OWNER,count(*)  from dba_tables t group by t.OWNER order by 2 desc;

--按用户统计数据量
SELECT T.OWNER, SUM(T.BYTES) / 1024 / 1024 / 1024 SUM_SIZE_G
      FROM DBA_SEGMENTS T
     WHERE T.OWNER NOT IN
           ('SCOTT', 'ORDSYS', 'OUTLN', 'DBSNMP', 'OE', 'SYS','MDSYS','CZ','SH','LX','SYSMAN','SYSTEM','PM','XDB','OLAPSYS','WMSYS','CTXSYS','EXFSYS','TEST')
     GROUP BY T.OWNER 
     ORDER BY 2 DESC

--等待索引创建完成

8、收集统计信息
	   select case b.PARTITIONED
          when 'YES' then
           'execute dbms_stats.gather_table_stats(ownname =>''' || a.owner ||
           ''',tabname =>''' || a.table_name || ''',partname =>''' ||
           partition_name ||
           ''',granularity=>''PARTITION'',estimate_percent =>10,degree =>8,cascade=>true);'
          when 'NO' then
           'execute dbms_stats.gather_table_stats(ownname =>''' || a.owner ||
           ''',tabname =>''' || a.table_name ||
           ''',estimate_percent =>10,degree=>8,cascade =>true);'
        end as stats_sql
   from dba_tab_statistics a, dba_tables b
  where a.owner = b.owner
    and a.table_name = b.table_name
    and b.TABLESPACE_NAME not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','UNDOTBS2','USERS','GGSJ1','TEMP001','CSSBBFB')
    and b.owner not in ('GOLDENGATE','WWW','UNICORN','BIZXFILES','OMNIUPDATE')
  order by a.owner, a.table_name, a.partition_name;
	 
9、 --统计失效对象
SELECT * FROM dba_invalid_objects;

    --重新编译失效对象
/home/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/admin
@utlrp.sql	 
	 
	 
10、 建立ogg进程	 
--给中软提供OGG生产库的IP地址和端口号  7809 
--查看推送文件的名称前缀
--配置OGG参数 
编写参数文件并添加进程
edit param re_dj

replicat re_dj
SETENV (ORACLE_SID="nfzcdb")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid goldengate,password goldengate
sqlexec "Alter session set commit_write=nowait"
REPORT AT 01:59
REPORTROLLOVER AT 02:00
HANDLECOLLISIONS
REPERROR DEFAULT,ABEND
--REPERROR DEFAULT, DISCARD
DISCARDFILE ./dirrpt/re_dj.dsc,append,megabytes 100
DISCARDROLLOVER AT 06:00
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
DYNAMICRESOLUTION
NUMFILES 3000
EOFDELAYCSECS 30
GETTRUNCATES
BATCHSQL BATCHESPERQUEUE 200,OPSPERBATCH 2000
CACHEMGR, CACHESIZE 256MB
MAP  hx_dj.*,     target hx_dj.*;
	 
add replicat re_dj,exttrail ./dirdat/zc,checkpointtable REP_ASM_CKPT	 
	 
11、启动进程并观察日志
start replicate re_dj，after scn xxxx	

--检查数据一致和OGG进程的状态