我家小花儿

这个屌丝很懒，什么也没留下！

热门标签

如何快速实现Oracle数据库的版本升级以及跨平台迁移_oracle升级和迁移

作者：我家小花儿 | 2024-08-22 00:37:49

踩

oracle升级和迁移

1. 前言... 2

前言

这是一个从大型Oracle ERP系统升级迁移过程中总结出来的技术指引，对于数据量巨大（在我们的案例中数据库大小为20+TB）的oracle数据库的升级和跨平台迁移，参考它可以很大程度减少业务系统停机时间，从而降低对核心业务系统在软、硬件升级期间的业务连续性的影响。

简单来说，就是利用Oracle提供的Transportable Tablespaces功能，完成从低版本数据库到不同服务器平台的高版本数据库的迁移，我们根据Oracle官方文档梳理出来大致步骤如下图所示：

下面是我们在升级迁移过程中的步骤，通过这些步骤我们实现了Oracle数据库从11g到19c的升级，同时完成了从IBM小机平台到oracle一体机（X86 Linux）平台的数据库迁移。

需要补充说明一下，为了保障升级过程中有回退机制，我们在源系统数据库上部署了DG备库，我们的升级迁移其实是从备库上发起的（备库搭建过程对系统的业务连续性几乎没有影响）。关于Oracle DG的搭建以及19c数据库环境的搭建（包括CDB/PDB的创建）网上已经有很多的文档介绍，这里我们将从备库升级主库操作开始，直接介绍数据库迁移迁移的准备工作以及迁移的过程。

升级迁移准备工作

1.1.升级备库

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;

1.2.数据对象处理

删除客户化视图，升级后开发会再创建

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

数据库升级迁移

2.1.11g数据库节点操作

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

$ 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。

2.2.19C数据导入

在目标库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应用系统的相关操作了。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家小花儿/article/detail/1013937