标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,655)
- DB2 (22)
- MySQL (71)
- Oracle (1,519)
- Data Guard (51)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (65)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (553)
- Oracle安装升级 (90)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (76)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- RAC默认服务配置优先节点
- Oracle 19c RAC 替换私网操作
- 监听报TNS-12541 TNS-12560 TNS-00511错误
- drop tablespace xxx including contents恢复
- Linux 8 修改网卡名称
- 如何修改集群的公网信息(包括 VIP) (Doc ID 1674442.1)
- 如何在 oracle 集群环境下修改私网信息 (Doc ID 2103317.1)
- ORA-600 [kcvfdb_pdb_set_clean_scn: cleanckpt] 相关bug
- ORA-600 krhpfh_03-1210故障处理
- 19c库启动报ORA-600 kcbzib_kcrsds_1
- DBMS_SESSION.set_context提示ORA-01031问题解决
- redo写丢失导致ORA-600 kcrf_resilver_log_1故障
- 硬件故障导致ORA-01242 ORA-01122等错误
- 200T 数据库非归档无备份恢复
- 利用flashback快速恢复failover 的备库
- [comingback2022@cock.li].eking和[tsai.shen@mailfence.com].faust扩展名勒索病毒数据库可以完美恢复
- opatch auto 出现unable to get oracle owner for 错误
- Oracle 23ai 表和视图的列最多支持到4096个
- 断电引起redo和数据文件不一致故障恢复
- ORA-03113: 通信通道的文件结尾
标签归档:xtts迁移
XTTS实战分享
迁移目标
源库:hp-unix RAC 裸设备 10.2.0.5
目标库:Linux RAC ASM 11.2.0.4
停机时间:8小时
数据量:16T
迁移方法
使用基于rman备份的xtts实现(因为使用裸设备,无法使用dbms_file_transfer方法实现)
迁移步骤
1)迁移之前检查
--查询无效对象 select owner, object_name, object_type from dba_objects where status !='VALID' order by owner, object_type, object_name; --检查无效index select owner, index_name, status from dba_indexes where status='UNUSABLE' order by 1,2; select i.owner, i.index_name, p.partition_name, p.status from dba_ind_partitions p,dba_indexes i where p.index_name=i.index_name and p.status='UNUSABLE' order by 1,2,3; select i.owner,i.index_name,s.subpartition_name,s.status from dba_ind_subpartitions s,dba_indexes i where s.index_name=i.index_name and s.status='UNUSABLE' order by 1,2,3; --compatible Advanced Queues检查 select owner,queue_table,recipients,compatible from dba_queue_tables where recipients='MULTIPLE' and compatible like '%8.0%'; --基于XMLSchema的XMLType对象检查 SELECT distinct OWNER FROM DBA_XML_SCHEMAS; 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; --SPATIAL空间组件对象检查 select owner,index_name from dba_indexes where ityp_name = 'SPATIAL_INDEX'; select owner, table_name, column_name from dba_tab_columns where data_type = 'SDO_GEOMETRY' and owner != 'MDSYS' order by 1,2,3; --外部表检查 select distinct owner from DBA_EXTERNAL_TABLES; --IOT表检查 select distinct owner from dba_tables where IOT_TYPE is not null; --检查临时表 SELECT owner,table_name FROM DBA_TABLES WHERE TEMPORARY='Y' AND OWNER IN(用户列表); --物化视图检查 select owner,count(*) from dba_mviews group by owner; --检查永久表空间 select t.TABLESPACE_NAME TABLESPACE_NAME,count(f.FILE_ID), sum(f.bytes/1024/1024/1024) GB FROM dba_tablespaces t, dba_data_files f where t.TABLESPACE_NAME=f.TABLESPACE_NAME and t.tablespace_name not in (根据需求排除) and t.contents = 'PERMANENT' group by t.TABLESPACE_NAME order by 2; --检查命令进行自包含检查 EXEC sys.dbms_tts.transport_set_check('需要迁移的表空间列表',TRUE); select * from transport_set_violations; --回收站检查 select count(*) from dba_recyclebin; Purge dba_recyclebin; --检查是否存在应用户使用TSTZ 字段 select c.owner || '.' || c.table_name || '(' || c.column_name || ') -' || c.data_type || ' ' col from dba_tab_cols c, dba_objects o where c.data_type like '%WITH TIME ZONE' and c.owner=o.owner and c.table_name = o.object_name and o.object_type = 'TABLE' order by col; --检查表空间是否加密 select tablespace_name,ENCRYPTED from dba_tablespaces; --检查是否存在加密字段 select * from DBA_ENCRYPTED_COLUMNS; --检查Opaque Types类型字段 select distinct owner ,DATA_TYPE from dba_tab_columns where owner in (需要迁移用户列表); --检查表空间和数据文件状态 select tablespace_name,status from dba_tablespaces; select STATUS,ONLINE_STATUS,count(*) from dba_data_files group by STATUS,ONLINE_STATUS; --比对新旧环境profile是否一致 select distinct(t.pro) from (select s.profile pro, l.profile pro2 from dba_profiles@XTTS_DBLINK s, dba_profiles l where s.profile = l.profile(+)) t where t.pro2 is null order by t.pro;
2)迁移之前新库创建
其他类似profile,不需要xtts迁移的东西,均可以创建
--在新环境中比对并创建角色 select 'create role '||role ||';' from dba_roles@XTTS_DBLINK minus select 'create role '||role ||';' from dba_roles; --在新环境中比对并创建用户 select 'create user "'||a.username ||'" identified by values '''||b.password|| ''' default tablespace USERS '|| 'temporary tablespace '|| a.TEMPORARY_TABLESPACE||';' from dba_users@XTTS_DBLINK a,sys.user$@XTTS_DBLINK b, dba_users c,sys.user$ d where a.username=b.name and a.username=d.name(+) and a.username = c.username(+) and c.username is null order by a.username;
3)停业务之前xtts迁移操作
源库
备注:由于源库的perl的版本不能满足需求,下载新的perl版本
--xtt.properties文件编辑 tablespaces=需要迁移表空间列表 platformid=4 dfcopydir=/backup/temp1/rmanback backupformat=/backup/temp1/rmanback stageondest=/temp1/rmanback storageondest=+DATA/xifenfei/datafile backupondest=/temp3/covntemp parallel=16 rollparallel=16 getfileparallel=16 --xtts全备发起 cat /home/oracle/xttsscript/full_backup.sh export TMPDIR=/home/oracle/tmpxtts cd /home/oracle/xtts export ORACLE_SID=xifenfei2 /home/oracle/perl/bin/perl xttdriver.pl -p -d nohup /home/oracle/xttsscript/full_backup.sh > /home/oracle/xttsscript/full_backup.log & --完成之后拷贝rmanconvert.cmd到共享目录 cp /home/oracle/tmpxtts/rmanconvert.cmd /backup/temp1/tempfile/
目标库
--拷贝源库的rmanconvert.cmd到目标库 cp /temp1/tempfile/rmanconvert.cmd /home/oracle/tmpxtts/ --目标库启动到mount shutdown immediate; startup mount --xtts转换数据文件 cd /home/oracle/xttsscript/ cat > /home/oracle/xttsscript/full_restore.sh export TMPDIR=/home/oracle/tmpxtts export ORACLE_SID=xifenfei2 cd /home/oracle/xtts $ORACLE_HOME/perl/bin/perl xttdriver.pl -c -d chmod +x /home/oracle/xttsscript/full_restore.sh nohup /home/oracle/xttsscript/full_restore.sh > /home/oracle/xttsscript/full_restore.log &
源库第一次增量备份
cd /home/oracle/xttsscript cat /home/oracle/xttsscript/incre_backup.sh export XTTDEBUG=1 export TMPDIR=/home/oracle/tmpxtts cd /home/oracle/xtts export ORACLE_SID=xifenfei2 /home/oracle/perl/bin/perl xttdriver.pl -i -d nohup /home/oracle/xttsscript/incre_backup.sh > /home/oracle/xttsscript/incre_backup1.log & --拷贝文件到共享目录 cp /home/oracle/tmpxtts/xttplan.txt /backup/temp1/tempfile/ cp /home/oracle/tmpxtts/tsbkupmap.txt /backup/temp1/tempfile/
目标库第一次增量恢复
--拷贝共享目录文件 cp /temp1/tempfile/xttplan.txt /home/oracle/tmpxtts/xttplan.txt cp /temp1/tempfile/tsbkupmap.txt /home/oracle/tmpxtts/tsbkupmap.txt --xtts增量还原 cd /home/oracle/xttsscript cat /home/oracle/xttsscript/ince_restore.sh export XTTDEBUG=1 export TMPDIR=/home/oracle/tmpxtts export ORACLE_SID=xifenfei1 cd /home/oracle/xtts $ORACLE_HOME/perl/bin/perl xttdriver.pl -r -d nohup /home/oracle/xttsscript/ince_restore.sh > /home/oracle/xttsscript/ince_restore1.log &
源端更新scn
cd /home/oracle/xttsscript/ cat /home/oracle/xttsscript/getscn.sh export XTTDEBUG=1 export TMPDIR=/home/oracle/tmpxtts cd /home/oracle/xtts export ORACLE_SID=xifenfei2 date /home/oracle/perl/bin/perl xttdriver.pl -s -d nohup /home/oracle/xttsscript/getscn.sh >> /home/oracle/xttsscript/getscn.log &
在停业务之前,依次进行上面三步的增量备份,恢复,更新scn,一直持续到最后停业务,表空间只读。
4)正式停业务后xtts操作
原库
--设置表空间只读 select 'alter tablespace '||t.TABLESPACE_NAME||' read only;' FROM dba_tablespaces t, dba_data_files f where t.TABLESPACE_NAME=f.TABLESPACE_NAME and t.tablespace_name not in (选择排除的表空间) and t.contents = 'PERMANENT' group by t.TABLESPACE_NAME order by count(f.FILE_ID); --xtts增量备份 cd /home/oracle/xttsscript nohup /home/oracle/xttsscript/incre_backup.sh > /home/oracle/xttsscript/incre_backup_end.log & --拷贝文件到共享目录 cp /home/oracle/tmpxtts/xttplan.txt /backup/temp1/tempfile/ cp /home/oracle/tmpxtts/tsbkupmap.txt /backup/temp1/tempfile/
目标库
--拷贝共享目录文件 cp /temp1/tempfile/xttplan.txt /home/oracle/tmpxtts/xttplan.txt cp /temp1/tempfile/tsbkupmap.txt /home/oracle/tmpxtts/tsbkupmap.txt cd /home/oracle/xttsscript nohup /home/oracle/xttsscript/ince_restore.sh > /home/oracle/xttsscript/ince_restore_end.log &
5)元数据迁移
--源库(导出表空间和用户元数据) nohup expdp "'/ as sysdba'" parfile=/archive2/dmpdir/expdp_xtts_tbs_metadata.par & nohup expdp "'/ as sysdba'" parfile=/archive2/dmpdir/expdp_xtts_users_metadata.par & --目标库(导入表空间和用户元数据) nohup impdp "'/ as sysdba'" parfile=/temp3/dmpdir/impdp_xtts_tbs_metadata.par & nohup impdp "'/ as sysdba'" parfile=/temp3/dmpdir/impdp_xtts_users_metadata.par &
6)后续操作
--设置表空间读写 select 'alter tablespace '||t.TABLESPACE_NAME||' read write;' FROM dba_tablespaces t, dba_data_files f where t.TABLESPACE_NAME=f.TABLESPACE_NAME and t.tablespace_name not in (排除表空间列表) and t.contents = 'PERMANENT' group by t.TABLESPACE_NAME order by count(f.FILE_ID); --编译无效对象 @?/rdbms/admin/utlprp.sql 32 --对比无效对象 select r.owner, r.object_type, r.remote_cnt Source_Cnt, l.local_cnt Target_Cnt from ( select owner, object_type, count(owner) remote_cnt from dba_objects@XTTS_DBLINK where owner not in (select name from system.logstdby$skip_support where action=0) group by owner, object_type ) r , ( select owner, object_type, count(owner) local_cnt from dba_objects where owner not in (select name from system.logstdby$skip_support where action=0) group by owner, object_type ) l where l.owner (+) = r.owner and l.object_type (+) = r.object_type and nvl(l.local_cnt,-1) != r.remote_cnt order by 1, 3 desc; --对比sequence大小 select * from( select a.SEQUENCE_OWNER,a.sequence_name,a.last_number prod_number, b.last_number dr_number,(b.last_number - a.last_number) gap_than_zero from dba_sequences@XTTS_DBLINK a,dba_sequences b where a.sequence_owner not in ('SYS','SYSTEM','XDB','WMSYS','TSMSYS','SYSMAN','PRECISE1', 'ORDSYS','OUTLN','OLAPSYS','ORDPLUGINS','MDSYS','EXFSYS', 'DMSYS','DSG','DBSNMP','PRECISE2','SI_INFORMTN_SCHEMA','SPA','TSMSYS','PUBLIC','GOLDENGATE', 'ORDDATA','PRECISE4','PRECISE3','CTXSYS','SCOTT','PERFSTAT') and a.sequence_owner=b.sequence_owner (+) and a.sequence_name=b.sequence_name(+) order by 5,1,2 desc) where gap_than_zero < 0 ; --检查无效index select owner, index_name, status from dba_indexes where status='UNUSABLE' order by 1,2; select i.owner, i.index_name, p.partition_name, p.status from dba_ind_partitions p,dba_indexes i where p.index_name=i.index_name and p.status='UNUSABLE' order by 1,2,3; select i.owner,i.index_name,s.subpartition_name,s.status from dba_ind_subpartitions s,dba_indexes i where s.index_name=i.index_name and s.status='UNUSABLE' order by 1,2,3; --创建迁移之前删除的物化视图,index,临时表等
由于xtts迁移是一种物理方法结合逻辑方法的迁移,本身是一个复杂的过程,这里只是列举出来了主要的操作过程和步骤,可能涉及很多细节工作需要考虑比如public对象,统计信息,dblink,权限等所有逻辑迁移中需要注意的问题,在这里易于需要注意,另外还要关注xtts本身物理转换所带来的各种bug.
特别感谢Lunar,Oracle的Gary Zhou,存储的黄长老等所有朋友的帮助和支持