邯郸推广网站建设哪个好,网站建设佰首选金手指三,免费查企业信息软件,济南网站建设首推企优互联不错Oracle数据泵跨大版本迁移数据库 source库导出元数据传输dump和数据文件到target库target库导入数据库迁移后的工作 #x1f988;场景#xff1a;将测试环境中一台Oracle 11g数据库迁移到另一台新搭建的19c数据库中。 #x1f914;分析#xff1a;由于是跨数据库大版本场景将测试环境中一台Oracle 11g数据库迁移到另一台新搭建的19c数据库中。 分析由于是跨数据库大版本无法通过RMAN进行备份恢复来迁移数据库。数据库整体数据量较小约1T左右source库有充足的停机时间因此可以采用数据泵技术来迁移数据库。 source库版本11.2.0.4target库版本: 19cOS版本: RHEL 7.6数据量1.2T source库导出元数据
⭐️Tolist-1. 在source库上修改用户自定义表空间为只读模式导出数据库系统数据。
设置用户自定义表空间为只读
--确认用户表空间清单
select tablespace_name,status from dba_tablespaces; --修改用户表空间为只读
ALTER TABLESPACE ts_sysmanage_idx READ ONLY;
ALTER TABLESPACE ts_sysmanage READ ONLY;ALTER TABLESPACE ts_aiia_idx READ ONLY;
ALTER TABLESPACE ts_aiia READ ONLY;ALTER TABLESPACE ts_abde_idx READ ONLY;
ALTER TABLESPACE ts_abde READ ONLY;ALTER TABLESPACE ts_src_idx READ ONLY;
ALTER TABLESPACE ts_src READ ONLY;ALTER TABLESPACE top_user READ ONLY;
ALTER TABLESPACE recon READ ONLY;ALTER TABLESPACE USERS READ ONLY;--确认只读的表空间
select tablespace_name,status from dba_tablespaces; --创建数据泵如果没有
select * from dba_directories where directory_nameDUMPDIR;
create directory dumpdir as /oradata/backup;导出数据库导出库是11g时需添加参数VERSION12
export ORACLE_SIDreconexpdp \/ as sysdba\ directorydumpdir fully \
dumpfiledump_${ORACLE_SID}_full_date %F_%U.dmp \
logfiledump_${ORACLE_SID}_full_date %F.log \
TRANSPORTABLEALWAYS VERSION12该过程只会导出元数据和SYSTEM、SYSAUX表空间的数据因此速度会比较快。
如果收到下面的报错
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list isORA-29335: tablespace USERS is not read only需要把USERS表空间也改为只读。
导出完成后检查输出信息中需要传输到target库的数据文件清单
******************************************************************************
...
Master table SYS.SYS_EXPORT_FULL_02 successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_02 is:/oradata/backup/dump_recon_full_2024-02-21_01.dmp
******************************************************************************
Datafiles required for transportable tablespace RECON:/oradata/RECON/datafile/o1_mf_recon_l06cyctt_.dbf/oradata/RECON/datafile/o1_mf_recon_l0682wv2_.dbf
Datafiles required for transportable tablespace TS_abde:/oradata/RECON/datafile/o1_mf_ts_abde_l064h30m_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l39kkof2_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l064h30s_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l064h30t_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l064h310_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l064h313_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l064h322_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l064h325_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l064h32d_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l064h34l_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l0682wrp_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l0682wrr_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l0682wsy_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l0682wt0_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l068s81z_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l068s821_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l068s822_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l068s83b_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l06cycrc_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l06cycrf_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l06cycrh_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l06cycsn_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l06dkx10_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l06dkx12_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l06dkx14_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l06dkx27_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l06dkx39_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l06dkx3d_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l39kkfjh_.dbf/oradata/RECON/datafile/o1_mf_ts_abde_l064h30p_.dbf
Datafiles required for transportable tablespace TS_abde_IDX:/oradata/RECON/datafile/o1_mf_ts_abde__l0682wrm_.dbf/oradata/RECON/datafile/o1_mf_ts_abde__l06cyctp_.dbf/oradata/RECON/datafile/o1_mf_ts_abde__l06cycsp_.dbf/oradata/RECON/datafile/o1_mf_ts_abde__l068s84d_.dbf
Datafiles required for transportable tablespace TS_aiia:/oradata/RECON/datafile/o1_mf_ts_aiia_l06cycvs_.dbf
Datafiles required for transportable tablespace TS_aiia_IDX:/oradata/RECON/datafile/o1_mf_ts_aiia__l0682ww2_.dbf
Datafiles required for transportable tablespace TS_SRC:/oradata/RECON/datafile/o1_mf_ts_src_l064h348_.dbf
Datafiles required for transportable tablespace TS_SRC_IDX:/oradata/RECON/datafile/o1_mf_ts_src_i_l064h33l_.dbf
Datafiles required for transportable tablespace TS_SYSMANAGE:/oradata/RECON/datafile/o1_mf_ts_sysma_l06dkx2b_.dbf/oradata/RECON/datafile/o1_mf_ts_sysma_l064h335_.dbf
Datafiles required for transportable tablespace TS_SYSMANAGE_IDX:/oradata/RECON/datafile/o1_mf_ts_sysma_l068s85g_.dbf
Datafiles required for transportable tablespace USERS:/oradata/RECON/datafile/o1_mf_users_l068s84g_.dbf
Job SYS.SYS_EXPORT_FULL_02 successfully completed at Wed Feb 21 09:07:48 2024 elapsed 0 00:01:08传输dump和数据文件到target库
⭐️Tolist-2. 将导出的dump文件拷贝到target库所在的服务器。
scp /oradata/backup/dump_recon_full_2024-02-21* oraclex.x.x.x:/oradata/backup将dump文件拷贝到target库服务器的dumpdir对应的路径下。
⭐️Tolist-3. 将source库上用户自定义表空间对应的数据文件拷贝到target库所在的服务器。
拷贝到target库的数据文件目录下。
cd /oradata/RECON/datafile/
scp *.dbf oraclex.x.x.x:/oradata/RECON_0/datatfile不需要拷贝系统表空间、TEMP和UNDO表空间
$ ls datafile/ | grep sysaux
$ ls datafile/ | grep system
$ ls datafile/ | grep temp
$ ls datafile/ | grep undo检查source库和target库所在平台的endian是否相同。如果不同的话需要进行表空间转换。
SELECT d.PLATFORM_NAME, ENDIAN_FORMATFROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE dWHERE tp.PLATFORM_NAME d.PLATFORM_NAME;如果操作系统是相同的比如都是Linux RHEL 7.6那么endian也应该是相同的。
⭐️Tolist-4. 将source库上用户自定义表空间恢复成可读写模式。
ALTER TABLESPACE ts_sysmanage_idx READ WRITE;
ALTER TABLESPACE ts_sysmanage READ WRITE;ALTER TABLESPACE ts_aiia_idx READ WRITE;
ALTER TABLESPACE ts_aiia READ WRITE;ALTER TABLESPACE ts_abde_idx READ WRITE;
ALTER TABLESPACE ts_abde READ WRITE;ALTER TABLESPACE ts_src_idx READ WRITE;
ALTER TABLESPACE ts_src READ WRITE;ALTER TABLESPACE top_user READ WRITE;
ALTER TABLESPACE recon READ WRITE;ALTER TABLESPACE USERS READ WRITE;target库导入数据库
⭐️Tolist-5. 在target库上导入dump文件。
创建数据泵
select * from dba_directories where directory_nameDUMPDIR;
create directory dumpdir as /oradata/backup;准备一个parfile写入impdp的参数:
fullY
directorydumpdir
dumpfiledump_recon_full_2024-02-21_01.dmp
logfileimport.log
transport_datafiles
/oradata/RECON_0/datafile/o1_mf_recon_l06cyctt_.dbf,
/oradata/RECON_0/datafile/o1_mf_recon_l0682wv2_.dbf,
...
/oradata/RECON_)/datafile/o1_mf_users_l068s84g_.dbf导入数据库dump文件
export ORACLE_SIDreconimpdp \/ as sysdba\ parfilepar.f如果收到以下报错
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29349: tablespace USERS already exists解决办法如下
--尝试删除target库的USERS表空间
sysRECON_0 drop tablespace users;
drop tablespace users
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace--尝试重命名已有的USERS表空间
alter tablespace USERS rename to USERS_OLD;
select tablespace_name,status from dba_tablespaces;重新导入
impdp \/ as sysdba\ parfilepar.f也可以给impdp加上以下参数来排除不导入USERS表空间
excludetablespace:IN (USERS)迁移后的工作
导入过程中可能遇到的报错
ORA-39083: Object type ALTER_PROCEDURE:ABDE.P8005030308 failed to create with error:
ORA-04052: error occurred when looking up remote object GP3USER.CALENDRIER_PORTEFEUILLEGP3DB
ORA-00604: error occurred at recursive SQL level 3
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from GP3DBFailing sql is:ALTER PROCEDURE ABDE.P8005030308 COMPILE PLSQL_OPTIMIZE_LEVEL 2 PLSQL_CODE_TYPE INTERPRETED PLSQL_DEBUG TRUE PLSCOPE_SETTINGS IDENTIFIERS:NONE NLS_LENGTH_SEMANTICS CHAR REUSE SETTINGS TIMESTAMP 2022-12-06 21:14:17ORA-39083: Object type ALTER_PROCEDURE:ABDE.P8002190101 failed to create with error:
ORA-04052: error occurred when looking up remote object AIIA.AIIA_MASTER_FILEABDE
ORA-00604: error occurred at recursive SQL level 3
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
...ORA-39082: Object type PROCEDURE:ABDE.P600200161011 created with compilation warningsJob SYS.SYS_IMPORT_FULL_01 completed with 68 error(s) at Wed Feb 21 15:38:55 2024 elapsed 0 00:03:03注在source库导出数据时可以排除DBLINK和STATISTICS。
在target导入完成后应用用户需要重建DBLINK并重新编译报错的存储过程。
Reference 【1】https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-data-pump-export-utility.html#GUID-BA07401C-6261-4B07-AD2C-06CD0A6E0BE9 【2】https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/transporting-data.html#GUID-05DFEA70-FDAF-4FAF-BF26-37225151A7D7
