暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / oracle 19c rac aix to linux xtts 跨平台迁移

oracle 19c rac aix to linux xtts 跨平台迁移

原创 四九年入国军 2024-10-09
82


一、XTTS 预检查

--前提条件
  Before running XTTs scripts, set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1.  Other NLS_LANG settings may cause errors.
  当前版本不支持Windows，不管是源端还是目标端
  源数据库兼容参数COMPATIBLE不能大于目标数据库
  源端数据库必须归档模式
  RMAN 源端DISK类型不能配置压缩
  RMAN备份类型不能为TO COPY，源端必须为backup type to backupset
  RMAN 源端通道不能为SBT
  RMAN任意通道不能配置限制，例如MAXSETSIZE、MAXPIECESIZE
  表空间数据文件必须online，不能包含offline，表空间必须读写
  只读的表空间可以用普通的XTTS方法移动。不需要合并跨平台增量备份来移动始终为只读的表空间。
  首选为Linux，如果是非Linux系统，目标和源端必须运行12.1.0.1或更高版本
  源的Oracle版本必须低于或等于目标。因此，本程序可作为一种升级方法。将应用可传输表空间限制
  源端和目标端最低版本11.2.0.4
  ASM只能用于目标端最终的数据文件位置，不能在ASM上放置备份
  此过程不支持快照备用数据库 




1>源端开启 block change tracking,禁用回收站，禁用延时段创建特性

=》打开块跟踪
set linesize  1000
col filename for a50
col status for a10
select * from v$block_change_tracking;
alter database enable block change tracking using file '/oracle/app/oracle/oradata/TESTDB/track.f';

=》 禁用回收站

show parameter recyclebin;  --查看 recyclebin 功能状态
alter system set recyclebin='off' scope=spfile; --关闭回收站
show recyclebin;      --显示回收站信息
purge DBA_RECYCLEBIN;   --清空回收站：

=》 禁用延时段创建特性（XTTS 不会将空表导入目标库)
alter system set deferred_segment_creation=false sid='*' scope=spfile;
	   
	   


2>表空间自包含检查


--语句一:全库检查排除SYSAUX、SYSTEM和USERS
set serveroutput on;
declare
  tslist varchar2(32767);
  first  boolean := true;
begin
  for c in (select tablespace_name
              from dba_tablespaces
             where contents = 'PERMANENT'
               and tablespace_name != 'SYSTEM'
               and tablespace_name != 'SYSAUX'
			   and tablespace_name != 'USERS') loop
    if first = true then
      first := false;
    else
      tslist := tslist || ',';
    end if;
    tslist := tslist || c.tablespace_name;
  end loop;
  dbms_output.put_line('Checking: ' || tslist);
  dbms_tts.transport_set_check(to_clob(tslist), false, false);
  for c in (select violations from transport_set_violations) loop
    dbms_output.put_line(c.violations);
  end loop;
end;
/

Checking: USERS,TEST
PL/SQL procedure successfully completed


--语句二：

select distinct tablespace_name from dba_segments where owner in ('ETL','NCMIS','NCMISV');
TBS_ETL_INDEX
TBS_NCMIS_INDEX
TBS_NCMIS_MAIN
TBS_ETL_MAIN


exec dbms_tts.transport_set_check('TBS_ETL_INDEX,TBS_NCMIS_INDEX,TBS_NCMIS_MAIN,TBS_ETL_MAIN',true);

--查看结果

select * from transport_set_violations;



2>是否存在加密列和加密表空间

select tablespace_name,ENCRYPTED from dba_tablespaces where ENCRYPTED='YES';

--查看加密列
select * from dba_encrypted_columns;



3>表空间是否包含XML 类型数据
 select distinct p.tablespace_name 
from dba_tablespaces p, dba_xml_tables x, dba_users u, all_all_tables t 
where t.table_name=x.table_name and t.tablespace_name=p.tablespace_name and x.owner=u.username;

select  distinct p.tablespace_name
from  dba_tablespaces p,dba_xml_tab_cols x,dba_users u,all_all_tables t
where t.table_name=x.table_name and t.tablespace_name=p.tablespace_name and   x.owner=u.username;


4>用户对象依赖表空间检查
select owner, segment_name, segment_type
  from dba_segments
 where tablespace_name in ('SYSTEM', 'SYSAUX','USERS')
   and owner not in
       (select name from system.logstdby$skip_support where action = 0);
	

5>源端检查是否有  Cluster 对象

col cluster_name for a30
col tablespace_name for a30
select cluster_name,tablespace_name from dba_clusters;
如源库检查有 cluster  对象，XTTS 迁移后，在目标库执行：
analyze cluster &cluster_name validate structure cascade;



6>源端检查  Key Compression

select  owner,index_name,table_name  from  dba_indexes  i  where  compression='ENABLED'
order by owner;



7>源端检查索引组织表

col owner format a30
col table_name format a30
Select owner,table_name from dba_tables where iot_type is not null  order by owner;

	

二、源环境数据采集


1、临时表查看
由于 XTTS 最后导入元数据时候不支持临时表，所以需要提前查出系统临时表信息。
注意：需要有 DBA 权限用户执行查询：

set long 99999
exec  DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SQLTERMINATOR', TRUE);
select  dbms_metadata.get_ddl('TABLE',TABLE_NAME,owner)  from  dba_tables  where  TEMPORARY='Y' and owner in ('ETL','NCMIS','NCMISV');

目标端可手工创建临时表：
CREATE GLOBAL TEMPORARY TABLE aa.temptable_aa ("ID" NUMBER, "STATUS" CHAR(1)  ) ON  COMMIT PRESERVE ROWS ;
CREATE GLOBAL TEMPORARY TABLE temptable_global  ("ID" NUMBER, "STATUS" CHAR(1) ) ON COMMIT PRESERVE ROWS ;

set long 1000000
SET PAGESIZE 3000
set lines 200
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
set echo on
set timing off
set wrap On
SET LONGCHUNKSIZE 400
spool crt_temp_table.sql 
select dbms_metadata.get_ddl('TABLE',TABLE_NAME,OWNER)
from DBA_TABLES
where TEMPORARY='Y' and ownerin ('ETL','NCMIS','NCMISV');
spool off;


2、查询公共同义词
---查询当前公共同义词
set long 9999
EXEC  DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SQLTERMINATOR', TRUE);  
select  dbms_metadata.get_ddl('SYNONYM',SYNONYM_NAME,OWNER)  FROM  dba_synonyms where owner='PUBLIC' and table_owner in ('ETL','NCMIS','NCMISV');  
梳理出创建公共同义词脚本：crt_pub_synonym.sql


3、查看公共 public dblink

--  查询当前 public dblink
set long 9999
EXEC  DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SQLTERMINATOR', TRUE);
select dbms_metadata.get_ddl('DB_LINK',DB_LINK,OWNER)  FROM DBA_DB_LINKS;
梳理出创建脚本 crt_pub_dblink.sql
	


4、梳理用户创建脚本、角色


set linesize 180
set pages 999
set long 90000
select dbms_metadata.get_ddl('USER',upper('ETL')) from  dual;
select dbms_metadata.get_granted_ddl('OBJECT_GRANT',upper('ETL')) from   dual;
select dbms_metadata.get_granted_ddl('ROLE_GRANT',upper('NCMIS')) from   dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT',upper('NCMIS')) from   dual;



5、查看失效对象数

 
---查看失效对象数量
select owner, object_type, count(*) from dba_objects where status <> 'VALID' and owner in  ('ETL','NCMIS','NCMISV') group by owner, object_type order by 1, 2 desc;



6、用户数据类型统计
set linesize   1000
col owner for a20
select owner,object_type,count(1) from dba_objects  where  owner in ('ETL','NCMIS','NCMISV') group by owner,object_type order by owner,object_type;


7、禁用trc-xtts备份的时候diag目录下会生成很大的trc

alter system set trace_enabled=FALSE scope=both sid='*';




8、查看原库序列


--cr_tts_create_seq.sql
set heading off feedback off trimspool on escape off
set long 1000 linesize 1000 pagesize 0
col SEQDDL format A300
select regexp_replace(dbms_metadata.get_ddl('SEQUENCE',
                                            sequence_name,
                                            sequence_owner),
                      '^.*(CREATE SEQUENCE.*CYCLE).*$',
                      'DROP SEQUENCE "' || sequence_owner || '"."' ||
                      sequence_name || '";' || chr(10) || '\1;') SEQDDL
  from dba_sequences
 where sequence_owner not in
       (select name from system.logstdby$skip_support where action = 0);

 

9、非业务用下新增数据
需要确认，非业务用户下是否有业务数据，例如 SYS 用户是否有业务数据：
--非业务用户下是否有业务数据(问题，这不一定是业务数据)
SELECT owner,object_name,status  FROM dba_objects d where d.created >= sysdate - 10    
and d.object_name not like 'WR%'       and d.owner IN ('SYS','SYSTEM') order by d.created desc;

 

	   

三、平台字节信息查询

set linesize 1000  
select  name, platform_id,platform_name from v$database;

NAME      PLATFORM_ID PLATFORM_NAME
--------- ----------- -----------------------------------------------------------------------------------------------------
CMISDB              6 AIX-Based Systems (64-bit)




col PLATFORM_NAME for a50
select * from v$transportable_platform;         




四、配置xtts_V4

--V4版本不在支持DBMS_FILE_TRANSFER方式
--源节点先做
unzip -d xtts rman_xttconvert_VER4.3.zip 
cd xtts/
mv  xtt.properties  xtt.properties.bak
vi xtt.properties

tablespaces=TBS_ETL_INDEX,TBS_NCMIS_INDEX,TBS_NCMIS_MAIN,TBS_ETL_MAIN
platformid=6
src_scratch_location=/nfsdata/bak
dest_scratch_location=/nfsdata/bak
dest_datafile_location=+DATA
parallel=10
rollparallel=10
getfileparallel=10
usermantransport=1
asm_home=/u01/app/19.0.0/grid
asm_sid=+ASM1



--nfs配置
linux
vi /etc/exports
/backup/testdb *(rw,sync,all_squash,insecure,anonuid=777,anongid=777)

AIX:

mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,vers=3,timeo=600 10.195.17.47:/NAS/CAPFS/data/EDXDDBQY /nfsdata




2.rman 源节点备份

cd /oracle/xtts
vi full_backup.sh

export TMPDIR=/home/oracle/xtts
export XTTDEBUG=1
export ORACLE_SID=cmisdb
$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup  

chmod a+x full_backup.sh
nohup ./full_backup.sh > /home/oracle/xtts/full_backup.log &


  执行完确保xttplan.txt有内容（里面记录表空间和对应的scn信息）

 


--res.txt 里记录了增量信息，以下是做完两次增量的信息：
#0:::5,20,TEST_5_0j0qoe47_19_1_1.bkp,0,2461029,0,0,0,TEST,TEST_5.dbf
#1:::5,20,0k0qoelg_20_1_1,2461029,2463419,0,0,0,TEST_5.dbf,TEST_5.dbf
#2:::5,20,0l0qoet7_21_1_1,2463419,2464057,0,0,0,TEST_5.dbf,TEST_5.dbf

复制xtts 和备份文件到目标机器


scp  -r  /oracle/xtts  10.0.2.20:/oracle
scp /backup/testdb/* 10.0.2.20:/backup/testdb
 
 
 
--目标库做
--目标库处于open状态

cd /nfsdata/xtts1
vi full_restore.sh

export TMPDIR=/nfsdata/xtts1
export ORACLE_SID=cmisdb1
$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

chmod a+x full_restore.sh
nohup ./full_restore.sh > /nfsdata/xtts1/full_restore.log &



--xtts数据第一次增量



cp -r /oracle/xtts /oracle/xtts1
cd /oracle/xtts1
vi rman_lv1.sh
export TMPDIR=/oracle/xtts1
export XTTDEBUG=1
$ORACLE_HOME/perl/bin/perl xttdriver.pl  --backup

chmod a+x rman_lv1.sh
nohup ./rman_lv1.sh > /oracle/xtts1/rman_lv1.log &

 
--目标端
cd /oracle/xtts1
vi recovery_lv1.sh


export TMPDIR=/oracle/xtts1
export XTTDEBUG=1
$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

chmod a+x recovery_lv1.sh
nohup ./recovery_lv1.sh > /oracle/xtts1/recovery_lv1.log &



--xtts数据第二次增量(最终）

show parameter job_queue_processes
alter system set job_queue_processes=0 sid='*';
show parameter job_queue_processes



alter system checkpoint;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile; 
alter system switch logfile;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system checkpoint;


--设置表空间只读

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
TBS_NCMIS_MAIN                 ONLINE
TBS_NCMIS_INDEX                ONLINE
TBS_ETL_MAIN                   ONLINE
TBS_ETL_INDEX                  ONLINE




alter tablespace TBS_NCMIS_MAIN read only;
alter tablespace TBS_NCMIS_INDEX read only;
alter tablespace TBS_ETL_MAIN read only;
alter tablespace TBS_ETL_INDEX read only;



chown  -R  oracle:oinstall /nfsdata
cp -r /nfsdata/xtts1 /nfsdata/xtts2
cd /nfsdata/xtts2
vi rman_lv2.sh
export TMPDIR=/nfsdata/xtts2
$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup

chmod a+x rman_lv2.sh
nohup ./rman_lv2.sh >  ./rman_lv2.log &
 
scp  增量备份到目标端    10.0.2.20:/backup/testdb/
scp -r  /oracle/xtts2 10.0.2.20:/oracle
 

--目标端

chown -R  oracle:oinstall /nfsdata
 
cd /nfsdata/xtts2/
vi recovery_lv2.sh
export TMPDIR=/nfsdata/xtts2
$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

chmod a+x recovery_lv2.sh
nohup ./recovery_lv2.sh > ./recovery_lv2.log &


--记得复制tnsnames.ora


五、目标端用户新建



CREATE USER "ETL" IDENTIFIED by  etl_test TEMPORARY TABLESPACE TEMP;
CREATE USER "NCMIS" IDENTIFIED by  ncmis_test TEMPORARY TABLESPACE TEMP;
CREATE USER "NCMISV" IDENTIFIED by  NCMISV_test TEMPORARY TABLESPACE TEMP;
	  


六、元数据导入


1、表空间源数据迁移

源端：


create directory XTTS_DATA_PUMP_DIR as '/nfsdata/dump';

vi /nfsdata/dump/xtts_exp.par
dumpfile=xtts.dmp
directory=XTTS_DATA_PUMP_DIR
transport_full_check=y
exclude=statistics
metrics=yes
logtime=all
transport_tablespaces=TBS_ETL_INDEX,TBS_NCMIS_INDEX,TBS_NCMIS_MAIN,TBS_ETL_MAIN



--导出元数据 dump 文件 7min；
expdp \'/ as sysdba\'  parfile=/nfsdata/dump/xtts_exp.par  logfile=xtts_export_`date +%Y%m%d-%H%M%S`.log 




vi /nfsdata/dump/xtts_imp.par
dumpfile=xtts.dmp
directory=XTTS_DATA_PUMP_DIR
exclude=statistics
metrics=yes
logtime=all
transport_datafiles='+DATA/tbs_etl_index_102.dbf','+DATA/tbs_etl_index_105.dbf','+DATA/tbs_etl_index_108.dbf','+DATA/tbs_etl_index_15.dbf','+DATA/tbs_etl_index_39.dbf','+DATA/tbs_etl_index_43.dbf','+DATA/tbs_etl_index_47.dbf','+DATA/tbs_etl_index_51.dbf','+DATA/tbs_etl_index_55.dbf','+DATA/tbs_etl_index_59.dbf','+DATA/tbs_etl_index_63.dbf','+DATA/tbs_etl_index_67.dbf','+DATA/tbs_etl_index_71.dbf','+DATA/tbs_etl_index_75.dbf','+DATA/tbs_etl_index_79.dbf','+DATA/tbs_etl_index_83.dbf','+DATA/tbs_etl_index_87.dbf','+DATA/tbs_etl_index_91.dbf','+DATA/tbs_etl_index_95.dbf','+DATA/tbs_etl_index_99.dbf','+DATA/tbs_etl_main_101.dbf','+DATA/tbs_etl_main_104.dbf','+DATA/tbs_etl_main_107.dbf','+DATA/tbs_etl_main_110.dbf','+DATA/tbs_etl_main_112.dbf','+DATA/tbs_etl_main_114.dbf','+DATA/tbs_etl_main_116.dbf','+DATA/tbs_etl_main_118.dbf','+DATA/tbs_etl_main_120.dbf','+DATA/tbs_etl_main_122.dbf','+DATA/tbs_etl_main_124.dbf','+DATA/tbs_etl_main_126.dbf','+DATA/tbs_etl_main_13.dbf','+DATA/tbs_etl_main_35.dbf','+DATA/tbs_etl_main_38.dbf','+DATA/tbs_etl_main_42.dbf','+DATA/tbs_etl_main_46.dbf','+DATA/tbs_etl_main_50.dbf','+DATA/tbs_etl_main_54.dbf','+DATA/tbs_etl_main_58.dbf','+DATA/tbs_etl_main_62.dbf','+DATA/tbs_etl_main_66.dbf','+DATA/tbs_etl_main_70.dbf','+DATA/tbs_etl_main_74.dbf','+DATA/tbs_etl_main_78.dbf','+DATA/tbs_etl_main_81.dbf','+DATA/tbs_etl_main_85.dbf','+DATA/tbs_etl_main_89.dbf','+DATA/tbs_etl_main_93.dbf','+DATA/tbs_etl_main_97.dbf','+DATA/tbs_ncmis_index_148.dbf','+DATA/tbs_ncmis_index_149.dbf','+DATA/tbs_ncmis_index_150.dbf','+DATA/tbs_ncmis_index_151.dbf','+DATA/tbs_ncmis_index_152.dbf','+DATA/tbs_ncmis_index_153.dbf','+DATA/tbs_ncmis_index_154.dbf','+DATA/tbs_ncmis_index_155.dbf','+DATA/tbs_ncmis_index_156.dbf','+DATA/tbs_ncmis_index_157.dbf','+DATA/tbs_ncmis_index_171.dbf','+DATA/tbs_ncmis_index_172.dbf','+DATA/tbs_ncmis_index_173.dbf','+DATA/tbs_ncmis_index_174.dbf','+DATA/tbs_ncmis_index_196.dbf','+DATA/tbs_ncmis_index_197.dbf','+DATA/tbs_ncmis_index_198.dbf','+DATA/tbs_ncmis_index_199.dbf','+DATA/tbs_ncmis_index_204.dbf','+DATA/tbs_ncmis_index_205.dbf','+DATA/tbs_ncmis_index_207.dbf','+DATA/tbs_ncmis_index_208.dbf','+DATA/tbs_ncmis_index_224.dbf','+DATA/tbs_ncmis_index_225.dbf','+DATA/tbs_ncmis_index_226.dbf','+DATA/tbs_ncmis_index_227.dbf','+DATA/tbs_ncmis_index_228.dbf','+DATA/tbs_ncmis_index_229.dbf','+DATA/tbs_ncmis_index_230.dbf','+DATA/tbs_ncmis_index_254.dbf','+DATA/tbs_ncmis_index_255.dbf','+DATA/tbs_ncmis_index_256.dbf','+DATA/tbs_ncmis_index_257.dbf','+DATA/tbs_ncmis_index_258.dbf','+DATA/tbs_ncmis_index_30.dbf','+DATA/tbs_ncmis_index_32.dbf','+DATA/tbs_ncmis_index_34.dbf','+DATA/tbs_ncmis_index_37.dbf','+DATA/tbs_ncmis_index_41.dbf','+DATA/tbs_ncmis_index_45.dbf','+DATA/tbs_ncmis_index_49.dbf','+DATA/tbs_ncmis_index_53.dbf','+DATA/tbs_ncmis_index_57.dbf','+DATA/tbs_ncmis_index_61.dbf','+DATA/tbs_ncmis_index_65.dbf','+DATA/tbs_ncmis_index_69.dbf','+DATA/tbs_ncmis_index_73.dbf','+DATA/tbs_ncmis_index_77.dbf','+DATA/tbs_ncmis_index_82.dbf','+DATA/tbs_ncmis_index_86.dbf','+DATA/tbs_ncmis_index_9.dbf','+DATA/tbs_ncmis_index_90.dbf','+DATA/tbs_ncmis_index_94.dbf','+DATA/tbs_ncmis_index_98.dbf','+DATA/tbs_ncmis_main_10.dbf','+DATA/tbs_ncmis_main_100.dbf','+DATA/tbs_ncmis_main_103.dbf','+DATA/tbs_ncmis_main_106.dbf','+DATA/tbs_ncmis_main_109.dbf','+DATA/tbs_ncmis_main_11.dbf','+DATA/tbs_ncmis_main_111.dbf','+DATA/tbs_ncmis_main_113.dbf','+DATA/tbs_ncmis_main_115.dbf','+DATA/tbs_ncmis_main_117.dbf','+DATA/tbs_ncmis_main_119.dbf','+DATA/tbs_ncmis_main_12.dbf','+DATA/tbs_ncmis_main_121.dbf','+DATA/tbs_ncmis_main_123.dbf','+DATA/tbs_ncmis_main_125.dbf','+DATA/tbs_ncmis_main_133.dbf','+DATA/tbs_ncmis_main_134.dbf','+DATA/tbs_ncmis_main_135.dbf','+DATA/tbs_ncmis_main_136.dbf','+DATA/tbs_ncmis_main_137.dbf','+DATA/tbs_ncmis_main_138.dbf','+DATA/tbs_ncmis_main_139.dbf','+DATA/tbs_ncmis_main_14.dbf','+DATA/tbs_ncmis_main_140.dbf','+DATA/tbs_ncmis_main_141.dbf','+DATA/tbs_ncmis_main_142.dbf','+DATA/tbs_ncmis_main_143.dbf','+DATA/tbs_ncmis_main_144.dbf','+DATA/tbs_ncmis_main_145.dbf','+DATA/tbs_ncmis_main_146.dbf','+DATA/tbs_ncmis_main_147.dbf','+DATA/tbs_ncmis_main_158.dbf','+DATA/tbs_ncmis_main_159.dbf','+DATA/tbs_ncmis_main_16.dbf','+DATA/tbs_ncmis_main_160.dbf','+DATA/tbs_ncmis_main_161.dbf','+DATA/tbs_ncmis_main_162.dbf','+DATA/tbs_ncmis_main_163.dbf','+DATA/tbs_ncmis_main_164.dbf','+DATA/tbs_ncmis_main_165.dbf','+DATA/tbs_ncmis_main_166.dbf','+DATA/tbs_ncmis_main_167.dbf','+DATA/tbs_ncmis_main_168.dbf','+DATA/tbs_ncmis_main_169.dbf','+DATA/tbs_ncmis_main_17.dbf','+DATA/tbs_ncmis_main_170.dbf','+DATA/tbs_ncmis_main_175.dbf','+DATA/tbs_ncmis_main_176.dbf','+DATA/tbs_ncmis_main_177.dbf','+DATA/tbs_ncmis_main_178.dbf','+DATA/tbs_ncmis_main_179.dbf','+DATA/tbs_ncmis_main_18.dbf','+DATA/tbs_ncmis_main_180.dbf','+DATA/tbs_ncmis_main_181.dbf','+DATA/tbs_ncmis_main_182.dbf','+DATA/tbs_ncmis_main_183.dbf','+DATA/tbs_ncmis_main_184.dbf','+DATA/tbs_ncmis_main_185.dbf','+DATA/tbs_ncmis_main_186.dbf','+DATA/tbs_ncmis_main_187.dbf','+DATA/tbs_ncmis_main_188.dbf','+DATA/tbs_ncmis_main_189.dbf','+DATA/tbs_ncmis_main_19.dbf','+DATA/tbs_ncmis_main_190.dbf','+DATA/tbs_ncmis_main_191.dbf','+DATA/tbs_ncmis_main_192.dbf','+DATA/tbs_ncmis_main_193.dbf','+DATA/tbs_ncmis_main_194.dbf','+DATA/tbs_ncmis_main_195.dbf','+DATA/tbs_ncmis_main_2.dbf','+DATA/tbs_ncmis_main_20.dbf','+DATA/tbs_ncmis_main_200.dbf','+DATA/tbs_ncmis_main_201.dbf','+DATA/tbs_ncmis_main_202.dbf','+DATA/tbs_ncmis_main_203.dbf','+DATA/tbs_ncmis_main_206.dbf','+DATA/tbs_ncmis_main_209.dbf','+DATA/tbs_ncmis_main_21.dbf','+DATA/tbs_ncmis_main_210.dbf','+DATA/tbs_ncmis_main_211.dbf','+DATA/tbs_ncmis_main_212.dbf','+DATA/tbs_ncmis_main_213.dbf','+DATA/tbs_ncmis_main_214.dbf','+DATA/tbs_ncmis_main_215.dbf','+DATA/tbs_ncmis_main_216.dbf','+DATA/tbs_ncmis_main_217.dbf','+DATA/tbs_ncmis_main_218.dbf','+DATA/tbs_ncmis_main_219.dbf','+DATA/tbs_ncmis_main_22.dbf','+DATA/tbs_ncmis_main_220.dbf','+DATA/tbs_ncmis_main_221.dbf','+DATA/tbs_ncmis_main_222.dbf','+DATA/tbs_ncmis_main_223.dbf','+DATA/tbs_ncmis_main_23.dbf','+DATA/tbs_ncmis_main_232.dbf','+DATA/tbs_ncmis_main_233.dbf','+DATA/tbs_ncmis_main_234.dbf','+DATA/tbs_ncmis_main_235.dbf','+DATA/tbs_ncmis_main_236.dbf','+DATA/tbs_ncmis_main_237.dbf','+DATA/tbs_ncmis_main_238.dbf','+DATA/tbs_ncmis_main_239.dbf','+DATA/tbs_ncmis_main_24.dbf','+DATA/tbs_ncmis_main_240.dbf','+DATA/tbs_ncmis_main_241.dbf','+DATA/tbs_ncmis_main_242.dbf','+DATA/tbs_ncmis_main_243.dbf','+DATA/tbs_ncmis_main_244.dbf','+DATA/tbs_ncmis_main_245.dbf','+DATA/tbs_ncmis_main_246.dbf','+DATA/tbs_ncmis_main_247.dbf','+DATA/tbs_ncmis_main_248.dbf','+DATA/tbs_ncmis_main_249.dbf','+DATA/tbs_ncmis_main_25.dbf','+DATA/tbs_ncmis_main_250.dbf','+DATA/tbs_ncmis_main_251.dbf','+DATA/tbs_ncmis_main_252.dbf','+DATA/tbs_ncmis_main_253.dbf','+DATA/tbs_ncmis_main_26.dbf','+DATA/tbs_ncmis_main_27.dbf','+DATA/tbs_ncmis_main_28.dbf','+DATA/tbs_ncmis_main_29.dbf','+DATA/tbs_ncmis_main_31.dbf','+DATA/tbs_ncmis_main_33.dbf','+DATA/tbs_ncmis_main_36.dbf','+DATA/tbs_ncmis_main_40.dbf','+DATA/tbs_ncmis_main_44.dbf','+DATA/tbs_ncmis_main_48.dbf','+DATA/tbs_ncmis_main_52.dbf','+DATA/tbs_ncmis_main_56.dbf','+DATA/tbs_ncmis_main_60.dbf','+DATA/tbs_ncmis_main_64.dbf','+DATA/tbs_ncmis_main_68.dbf','+DATA/tbs_ncmis_main_72.dbf','+DATA/tbs_ncmis_main_76.dbf','+DATA/tbs_ncmis_main_8.dbf','+DATA/tbs_ncmis_main_80.dbf','+DATA/tbs_ncmis_main_84.dbf','+DATA/tbs_ncmis_main_88.dbf','+DATA/tbs_ncmis_main_92.dbf','+DATA/tbs_ncmis_main_96.dbf'



---导入元数据-15min
impdp   \'/ as sysdba\' parfile=/nfsdata/dump/xtts_imp.par logfile=xtts_import_`date +%Y%m%d-%H%M%S`.log



2、系统对象的元数据迁移




vi /nfsdata/dump/xtts_other_exp.par
dumpfile=xtts_other.dmp
directory=XTTS_DATA_PUMP_DIR
content=metadata_only
exclude=table,index,constraint
metrics=yes
logtime=all
schemas='ETL','NCMIS','NCMISV'

-- 导出 2min
expdp  \'/ as sysdba\'   parfile=/nfsdata/dump/xtts_other_exp.par logfile=xtts_other_export_`date +%Y%m%d-%H%M%S`.log

--导入 6min
impdp  \'/ as sysdba\'  parfile=/nfsdata/dump/xtts_other_exp.par logfile=xtts_other_import_`date +%Y%m%d-%H%M%S`.log




su - oracle
rman target sys/oracle@xttdb
RMAN> validate tablespace TBS_ETL_INDEX,TBS_NCMIS_INDEX,TBS_NCMIS_MAIN,TBS_ETL_MAIN check logical;
RMAN> quit
#####检查整个库
RMAN> validate database;


		

select tablespace_name,status from dba_tablespaces;


alter tablespace TBS_ETL_MAIN read write;
alter tablespace TBS_NCMIS_INDEX read write;
alter tablespace TBS_NCMIS_MAIN read write;
alter tablespace TBS_ETL_INDEX read write;

alter  user xx default tablespace  xxxx;

set linesize   1000
col owner for a20
select owner,object_type,count(1) from dba_objects  where  owner in ('ETL','NCMIS','NCMISV') group by owner,object_type order by owner,object_type;





六、收尾工作



1、全库统计分析

--40min 
vi dbms.sql

sqlplus '/as sysdba' << EOF
alter system set resource_manager_plan='default_plan' sid='*' scope=both;
exec dbms_stats.set_global_prefs('CONCURRENT','TRUE');
exec dbms_stats.gather_database_stats(cascade =>true,method_opt =>'for all columns size auto',degree =>2);
exec dbms_stats.set_global_prefs('CONCURRENT','FALSE');
exit
EOF

chmod a+x dbms.sql
nohup ./dbms.sql &


查询进度

select sid,
       serial#,
       context,
       sofar,
       totalwork,
       round(sofar / totalwork * 100, 2) "%complete"
  from v$session_longops
 where opname like 'Gather%'
   and opname not like '%aggregate%'
   and totalwork != 0
   and sofar <> totalwork;



2、失效对象检测

sqlplus / as sysdba
@?/rdbms/admin/utlrp.sql





复制
墨力计划oracle
最后修改时间：2024-10-09 19:21:00
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

相关阅读

Oracle RAC ASM 磁盘组满了，无法扩容怎么在线处理？
Lucifer三思而后行
907次阅读
2025-03-17 11:33:53
RAC 19C 删除+新增节点
gh
557次阅读
2025-03-14 15:44:18
Oracle DataGuard高可用性解决方案详解
孙莹
387次阅读
2025-03-26 23:27:33
墨天轮个人数说知识点合集
JiekeXu
328次阅读
2025-04-01 15:56:03
XTTS跨版本迁移升级方案（11g to 19c RAC for Linux）
zwtian
318次阅读
2025-04-08 09:12:48
Oracle SQL 执行计划分析与优化指南
Digital Observer
284次阅读
2025-04-01 11:08:44
切换Oracle归档路径后，不能正常删除原归档路径上的归档文件
dbaking
274次阅读
2025-03-19 14:41:51
风口浪尖！诚通证券扩容采购Oracle 793万...
Roger的数据库专栏
272次阅读
2025-03-24 09:42:53
oracle定时任务常用攻略
virvle
258次阅读
2025-03-25 16:05:19
Oracle NetSuite 客户说｜健合（H&H）集团部署 Oracle NetSuite，全面提升全球运营效率
甲骨文中国
249次阅读
2025-03-28 15:00:30