目录
这是一个从大型Oracle ERP系统升级迁移过程中总结出来的技术指引，对于数据量巨大（在我们的案例中数据库大小为20+TB）的oracle数据库的升级和跨平台迁移，参考它可以很大程度减少业务系统停机时间，从而降低对核心业务系统在软、硬件升级期间的业务连续性的影响。
简单来说，就是利用Oracle提供的Transportable Tablespaces功能，完成从低版本数据库到不同服务器平台的高版本数据库的迁移，我们根据Oracle官方文档梳理出来大致步骤如下图所示：
下面是我们在升级迁移过程中的步骤，通过这些步骤我们实现了Oracle数据库从11g到19c的升级，同时完成了从IBM小机平台到oracle一体机（X86 Linux）平台的数据库迁移。
需要补充说明一下，为了保障升级过程中有回退机制，我们在源系统数据库上部署了DG备库，我们的升级迁移其实是从备库上发起的（备库搭建过程对系统的业务连续性几乎没有影响）。关于Oracle DG的搭建以及19c数据库环境的搭建（包括CDB/PDB的创建）网上已经有很多的文档介绍，这里我们将从备库升级主库操作开始，直接介绍数据库迁移迁移的准备工作以及迁移的过程。
1. 打开备库读写：执行failover过程
登录备库的节点上，查看备库有没有缺失的Redo日志。
以oratest用户登录备库上
sqlplus / as sysdba
sql> select thread#, low_sequence#, high_sequence# from v$archive_gap;
如果有，将缺失的日志文件拷贝到备库，通过下面命令去手动注册
sql> alter database register physical logfile '/xxx /thread_1_seq_33569.log';
当前备库停止Redo应用
sql> alter database recover managed standby database cancel;
执行下面语句发起故障转移
sql> alter database recover managed standby database finish {force};
查看备库切换前状态
sql> select database_role,switchover_status from v$database;
将Standby Database 转为Primary Database
sql> alter database commit to switchover to primary;
备注：如果上面切换条件查看显示SESSION ACTIVE，那么在这里使用下面命令
sql> alter database commit to switchover to primary with session shutdown;
打开数据库
sql> alter database open;
查看数据库状态
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
PROD OPEN
2. 升级DST
应用补丁
Patch 28125601: RDBMS - PROACTIVE DSTV32 UPDATE - TZDATA2018E cd /u01/backup/patch/28125601
/u01/mrtdb/11.2.0/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./ /u01/mrtdb/11.2.0/OPatch/opatch apply
conn / as sysdba
spool upg_tzv_apply.log
@upg_tzv_apply.sql
spool off
SELECT version FROM v$timezone_file;
3. 设置数据库密码策略 sys/
su - oratest
sqlplus / as sysdba
SQL> alter profile DEFAULT limit password_verify_function Null;
SQL> alter profile DEFAULT limit password_reuse_time unlimited;
SQL> alter profile DEFAULT limit IDLE_TIME unlimited;
- 停止JOB
sqlplus apps/xxxxx
SQL> EXEC DBMS_JOB.BROKEN(48,true);
SQL> EXEC DBMS_JOB.BROKEN(104,true);
SQL> EXEC DBMS_JOB.BROKEN(2879,true);
SQL> commit;
- 删除客户化视图，升级后开发会再创建
drop view APPS.GL_INTERFACE;
drop view APPS.GL_JE_BATCHES;
drop view APPS.GL_RECURRING_BATCHES;
- long转化为clob
生产转换脚本：
select 'alter table ' || owner || '.' || table_name || ' modify ' ||
column_name || ' CLOB;'
from dba_tab_columns
where data_type in ('LONG')
and owner not in
('SYS', 'SYSTEM', 'APPS', 'OUTLN', 'ESISYS', 'DDD', 'EXFSYS')
union
select 'alter table ' || owner || '.' || table_name || ' modify ' ||
column_name || ' BLOB;'
from dba_tab_columns
where data_type in ('LONG RAW')
and owner not in
('SYS', 'SYSTEM', 'APPS', 'OUTLN', 'ESISYS', 'DDD', 'EXFSYS');
编辑并执行转换脚本，示例：
alter table AHM.AHM_DBA_CONSTRAINTS modify SEARCH_CONDITION CLOB;
- 更改不合规的索引名，示例：
alter index BIS.PKEY rename to UOIDMAPPING_U1;
alter index BIS.UKEY rename to UOIDMAPPING_U2;
- 更改约束名，示例：
alter table BIS.UOIDMAPPING rename constraint PKEY to UOIDMAPPING_U1; alter table BIS.UOIDMAPPING rename constraint UKEY to UOIDMAPPING_U2;
- 分析SYS模式
$ sqlplus / as sysdba
begin
dbms_stats.gather_schema_stats(
'SYS',
options=>'GATHER STALE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
cascade => TRUE,
degree => 30);
end;
/
$ sqlplus / as sysdba
exec dbms_stats.gather_fixed_objects_stats;
/
exec dbms_stats.gather_dictionary_stats;
/
exec dbms_stats.gather_table_stats('SYS','TABPART$');
exec dbms_stats.gather_table_stats('SYS','TABCOMPART$');
exec dbms_stats.gather_table_stats('SYS','OBJ$');
exec dbms_stats.gather_index_stats('SYS','I_FILE#_BLOCK#');
exec dbms_stats.gather_index_stats('SYS','I_OBJ2');
exec dbms_stats.gather_index_stats('SYS','I_DEPENDENCY2');
exec dbms_stats.gather_index_stats('SYS','I_OBJ1');
exec dbms_stats.gather_index_stats('SYS','I_OBJ#');
/
- 编译所有的UNUSABLE索引
select 'alter index ' || OWNER || '.' || INDEX_NAME || ' rebuild;'
from dba_indexes
where status = 'UNUSABLE';
- 编译所有无效对象
sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql
1. 创建工作目录
mkdir -p /u01/backup/expimp
2. 创建 transport_set_violations view
sqlplus "/ as sysdba"
exec dbms_tts.transport_set_check('APPS_TS_INTERFACE');
desc SYS.transport_set_violations;
3. 执行 auxttspre.sql
从补丁/u01/backup/patch/31405457/au/patch/115/sql中复制 --如果已存在，不执行
cp auxttspre.sql /u01/backup/expimp
删除索引
drop index apps.PBCATT_X;
drop index CTXSYS.PBCATC_X;
drop index CTXSYS.PBCATE_X;
drop index CTXSYS.PBCATF_X;
drop index CTXSYS.PBCATV_X;
$ sqlplus "/ as sysdba"
SQL> @auxttspre.sql
4. Record Advanced Queue settings
从补丁/u01/backup/patch/31405457/au/patch/115/sql中复制auque1.sql
cp /u01/backup/patch/31405457/au/patch/115/sql/auque1.sql /u01/backup/expimp
$ sqlplus "/ as sysdba"
SQL> @auque1.sql
执行完毕后生成 auque2.sql文件。
5. 移除 rebuild index parameter in spatial indexes
select * from dba_indexes where index_type='DOMAIN' and upper(parameters) like '%REBUILD%' and ityp_name='SPATIAL_INDEX';
示例：
alter index MST.MST_MD_ADM_BNDS_N1 rebuild parameters ('');
alter index MST.MST_MD_HYDROS_N1 rebuild parameters ('');
alter index HR.PER_ADDRESSES_SPT rebuild parameters ('');
alter index HR.HR_LOCATIONS_SPT rebuild parameters ('');
6. 同步Text indexes ，示例
$ sqlplus "/ as sysdba"
SQL> select pnd_index_owner,pnd_index_name,count(*) from ctxsys.ctx_pending group by pnd_index_owner,pnd_index_name;
exec ctx_ddl.sync_index('JTF.JTF_AMV_ITEMS_NAME_CTX');
exec ctx_ddl.sync_index('JTF.JTF_AMV_ITEMS_DESC_CTX');
exec ctx_ddl.sync_index('JTF.JTF_AMV_ITEMS_TEXT_CTX');
exec ctx_ddl.sync_index('IBC.IBC_ATTRIBUTE_BUNDLES_CTX');
7. Determine TCA DQM status apps用户执行
select hz_dqm_search_util.is_dqm_available from dual;
返回T，说明启用了TCA DQM
8. 导出源环境参数设置
Modify the export parameter file
编辑auexpfulltts.dat
parallel=8
增加version=12 参数
去掉logtime参数
cp /u01/backup/expimp.bak/auexpfulltts.dat /u01/backup/expimp
9. 创建导出目录和getfile目录
$ sqlplus system/xxxxx
SQL> create directory dmpdir as '/u01/backup/expimp';
SQL> create directory SOURCEDIR as '/u01/mrtdata';
10. 导出Gather statistics data into a table
$ sqlplus system/xxxxx
SQL> exec dbms_stats.create_stat_table ('SYSTEM','migrate_stat_table');
SQL> connect system/xxxxx
SQL> exec dbms_stats.export_database_stats('migrate_stat_table');
11. Purge recycle bin
$ sqlplus "/ as sysdba"
SQL> purge dba_recyclebin;
12. Grant privilege to source SYSTEM schema
$ sqlplus "/ as sysdba"
SQL> grant EXEMPT ACCESS POLICY to system;
13. Export OLAP analytical workspaces 导出eif文件
示例：
exec dbms_aw.execute('aw attach FPA.FPAPJP rw');
exec dbms_aw.execute('allstat');
exec dbms_aw.execute('export all to eif file ''DMPDIR/export_FPAPJP.eif''');
exec dbms_aw.execute('aw detach FPA.FPAPJP');
exec dbms_aw.execute('aw delete FPA.FPAPJP');
14. 设置表空间只读
cp /u01/backup/expimp.bak/auxttsread.sql /u01/backup/expimp
sqlplus "/ as sysdba" @auxttsread.sql
15. 导出元数据 --大约7小时
expdp system/xxxxx parfile=auexpfulltts.dat
16. 传输数据文件
在源11i库，Plsql Developer以system用户执行“获取数据文件脚本”创建getfile.sql，将结果分成getfile01.sql-getfile08.sql文件。
脚本示例：
declare
-- Local variables here
i varchar2(4000);
j number :=0;
cursor c is select
'BEGIN
DBMS_FILE_TRANSFER.GET_FILE(
source_directory_object => ''SOURCEDIR'',
source_file_name => '''||substr(file_name,14)||''',
source_database => ''IBMLINK'',
destination_directory_object => ''DESTDIR'',
destination_file_name => '''||substr(file_name,14)||''');
END;
/' h
from dba_data_files a where a.tablespace_name in
(
'APPS_TS_ARCHIVE',
'APPS_TS_INTERFACE',
'APPS_TS_MEDIA',
'APPS_TS_NOLOGGING',
'APPS_TS_QUEUES',
'APPS_TS_SEED',
'APPS_TS_SUMMARY',
'APPS_TS_TOOLS',
'APPS_TS_TX_DATA',
'APPS_TS_TX_IDX',
'BEI_TS_DATA',
'HELD',
'HELX',
'HISTORY',
'ODM',
'OWAPUB',
'PORTAL',
'USER_DATA',
'OLAP',
'CTXD'
);
begin
-- Test statements here
for k in c
loop
j := j+1;
dbms_output.put_line('---'||j);
dbms_output.put_line(k.h);
if j =66 then
dbms_output.put_line('----------getfile001.sql');
end if;
if j =130 then
dbms_output.put_line('----------getfile002.sql');
end if;
if j =195 then
dbms_output.put_line('----------getfile003.sql');
end if;
if j =260 then
dbms_output.put_line('----------getfile004.sql');
end if;
if j =325 then
dbms_output.put_line('----------getfile005.sql');
end if;
if j =390 then
dbms_output.put_line('----------getfile006.sql');
end if;
if j =455 then
dbms_output.put_line('----------getfile007.sql');
end if;
end loop;
end;
后面将使用getfile sql。
在目标库RAC1 节点dm01dbadm0执行
- 删除旧的PDB
su - oracle --CDBEBS
sqlplus / as sysdba
alter pluggable database PROD close immediate;
drop pluggable database PROD including datafiles;
2. 创建目标PDB
在10.98.199.237跳转到86种子环境备份服务器上：# ssh 10.98.199.86
[root@erptest backup]# scp -r /u01/backup/MRTBAK oracle@10.98.246.103:/u01/PROD/temp/PROD/"
Create the target database instance
su - oracle
ORACLE_SID=CDBEBS1
sqlplus / as sysdba
SQL> create pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initCDBEBS1.ora.bak20211213' from spfile;
这里需要根据情况扩展各个表空间包括undo，以确保后续操作有足够的可用空间。
create pluggable database PROD using '/home/oracle/MRTBAK.xml' COPY SOURCE_FILE_NAME_CONVERT=('/u01/TEST/oradata/CDBEBS/MRTBAK/','/u01/PROD/temp/PROD/MRTBAK/') FILE_NAME_CONVERT=('/u01/PROD/temp/PROD/MRTBAK/','+DATAC1/CDBEBS/PROD/'); -- CDBEBS COPY不覆盖，MOVE会删除"
SQL> alter pluggable database PROD open;
SQL> ALTER PLUGGABLE DATABASE PROD SAVE STATE;
SQL> alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=10.98.203.104)(PORT=1521))" scope=both sid='CDBEBS1'; --CDB 下，如已配置，则跳过
SQL> alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=10.98.203.105)(PORT=1521))" scope=both sid='CDBEBS2';
SQL> alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=10.98.203.106)(PORT=1521))" scope=both sid='CDBEBS3';
SQL> alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=10.98.203.107)(PORT=1521))" scope=both sid='CDBEBS4';
SQL> shutdown;
SQL> startup;
3． 运行datapatch
export ORACLE_SID=CDBEBS1
$ $ORACLE_HOME/OPatch/datapatch
4. 配置数据文件复制
创建到AIX源库的dblink
export ORACLE_PDB_SID=PROD
ibmsource =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (protocol = tcp)(host = 10.98.199.237)(port = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
)
)
export ORACLE_PDB_SID=PROD
sqlplus / as sysdba
drop directory DESTDIR;
drop public database link IBMLINK;
SQL> create directory DESTDIR as '+DATAC1/CDBEBS/PROD'; --生产 exadata 1
SQL> create public database link IBMLINK connect to system identified by xxxxx using 'ibmsource';
select * from dual@IBMLINK;
使用getfile.sql
将之前所有getfile sql文件上传到exadata1 10.98.246.103:/u01/backup/expimp/下
在目标库RAC1 节点dm01dbadm0 10.98.246.103 执行
新增:$TNS_ADMIN/tnsnames.ora
CDBEBS_PROD =
(DESCRIPTION_LIST =
(LOAD_BALANCE = YES)
(FAILOVER = YES)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 10.98.203.110)(PORT = 1521)) --scan1
(ADDRESS = (PROTOCOL = tcp)(HOST = 10.98.203.109)(PORT = 1521)) --scan2
(ADDRESS = (PROTOCOL = tcp)(HOST = 10.98.203.108)(PORT = 1521)) --scan3
(CONNECT_DATA =
(SERVICE_NAME = PROD)
(INSTANCE_NAME = CDBEBS1)
)
)
)
编辑/u01/backup/expimp/下的getfile01.sh-getfile0x.sh脚本文件：
[oracle@dm01dbadm01 expimp]$ vi getfile01.sh
#!/bin/sh
sqlplus -s system/xxxxxx@CDBEBS_PROD <<EOF
spool getfile01.log
@getfile01.sql
spool off
EOF
exit
……
oracle用户：
cd /u01/backup/expimp/
export ORACLE_PDB_SID=PROD
--sqlplus / as sysdba @getfile1.sql 至 getfile8.sql
--sqlplus / as sysdba @getfile8.sql
nohup ./getfile01.sh &
nohup ./getfile02.sh &
nohup ./getfile03.sh &
nohup ./getfile04.sh &
nohup ./getfile05.sh &
nohup ./getfile06.sh &
nohup ./getfile07.sh &
nohup ./getfile08.sh &
5. 目标环境导入元数据
复制10.98.199.237:/u01/backup/expimp/ 到/u01/backup/expimp/下：
$ scp /u01/backup/expimp/* oracle@10.98.246.103:/u01/backup/expimp/
$ export ORACLE_PDB_SID=PROD
$ sqlplus system/xxxxx
SQL> create directory dmpdir as '/u01/backup/expimp';
修改auimpfulltts.dat文件：
--'+datac1/cdbebs/prod/u_data09.dbf',
vi auimpfulltts.dat --添加一个缺少的文件：'?/dbf/ctxd01.dbf',
transport_datafiles=
'?/dbf/ctxd01.dbf',
'?/dbf/temp06.dbf',
…
sed "s/\?\/dbf/\+DATAC1\/CDBEBS\/PROD/g" auimpfulltts.dat >
auimpfulltts.dat.new2
$ impdp system/xxxxx parfile= /u01/backup/expimp/auimpfulltts.dat.new2
6. 导入分析数据
$ export ORACLE_PDB_SID=PROD
$ sqlplus system/xxxxxx
SQL> exec dbms_stats.upgrade_stat_table('SYSTEM','migrate_stat_table');
create index SYSTEM.IDX_migrate_stat_table on SYSTEM.migrate_stat_table( c1, c5, n13, c3, type, C4, c2, statid, version);
exec dbms_stats.gather_table_stats('SYS','TABPART$');
exec dbms_stats.gather_table_stats('SYS','TABCOMPART$');
exec dbms_stats.gather_table_stats('SYS','OBJ$');
exec dbms_stats.gather_index_stats('SYS','I_FILE#_BLOCK#');
exec dbms_stats.gather_index_stats('SYS','I_OBJ2');
exec dbms_stats.gather_index_stats('SYS','I_DEPENDENCY2');
exec dbms_stats.gather_index_stats('SYS','I_OBJ1');
exec dbms_stats.gather_index_stats('SYS','I_OBJ#');
exec dbms_stats.gather_table_stats('SYSTEM','migrate_stat_table');
SQL> exec dbms_stats.import_database_stats('migrate_stat_table');
7. 导入 OLAP analytical workspaces 示例
export ORACLE_SID=CDBEBS
export ORACLE_PDB_SID=PROD
sqlplus / as sysdba
exec dbms_aw.execute('aw create FPA.FPAPJP');
exec dbms_aw.execute('import all from eif file ''DMPDIR/export_FPAPJP.eif'' data dfns');
exec dbms_aw.execute('update');
commit;
exec dbms_aw.execute('aw detach FPA.FPAPJP');
8. 增加服务名 （EBS系统特殊需求）
export ORACLE_PDB_SID=PROD 或者 alter session set container=PROD;
exec dbms_service.create_service('ebs_PROD','ebs_PROD');
exec dbms_service.start_service('ebs_PROD');
exec dbms_service.create_service('SEED_ebs_patch','SEED_ebs_patch');
exec dbms_service.start_service('SEED_ebs_patch');
exec dbms_service.create_service('PROD_ebs_patch','PROD_ebs_patch');
exec dbms_service.start_service('PROD_ebs_patch');
ALTER PLUGGABLE DATABASE PROD SAVE STATE;
9. Reset Advanced Queues
sqlplus "/ as sysdba" @/u01/backup/expimp/auque2.sql
10. Run adgrants.sql
sqlplus "/ as sysdba" @/u01/backup/expimp/adgrants.sql apps
11. Gather system and fixed objects statistics
$ sqlplus "/ as sysdba"
SQL> exec dbms_stats.gather_schema_stats(ownname=>'SYS',options=>'GATHER AUTO');
SQL> exec dbms_stats.gather_schema_stats(ownname=>'SYSTEM',options=>'GATHER AUTO');
SQL> exec dbms_stats.gather_dictionary_stats;
SQL> exec dbms_stats.gather_fixed_objects_stats;
connect "/ as sysdba";
SQL> alter system enable restricted session;
SQL> @adstats.sql
sqlplus "/ as sysdba"
SQL> alter system disable restricted session;
SQL> exit;
12. Grant create procedure privilege on CTXSYS
sqlplus apps/xxxxx @/u01/backup/expimp/adctxprv.sql xxxxxx CTXSYS
说明adctxprv.sql需从R12应用层拷贝$AD_TOP/patch/115/sql/adctxprv.sql
13. 编译无效对象
sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql
至此，数据库的升级迁移工作就完成了，后续就是数据库升级后ERP应用系统的相关操作了。
