-
Oracle Dataguard跨版本数据迁移(11.2.0.4~19.13.0.0)
一、前期准备
按照DG部署步骤修改DG参数、添加standby redo log、配置静态监听、配置tnsnames文件、备端修改参数文件、创建所需目录等配置好部署环境，这里不再赘述，跟正常部署DG无区别。
环境配置好后，进行后面的操作。二、使用RMAN备份复制主端数据库
2.1.主端备份数据库
RMAN> run { allocate channel c1 type disk; allocate channel c2 type disk; backup incremental level 0 format '/home/oracle/orcl_full_%U' database; backup format '/home/oracle/orcl_full_stanctl_%U' current controlfile for standby; release channel c1; release channel c2; }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
2.2.备端启动至nomount
SQL> create spfile from pfile='/home/oracle/pfile.ora'; SQL> startup nomount- 1
- 2
2.3.恢复控制文件
RMAN> restore standby controlfile from '/home/oracle/orcl_full_stanctl_061vp2sb_1_1'; RMAN> alter database mount;- 1
- 2
2.4.恢复数据文件
RMAN> catalog backuppiece '/home/oracle/orcl_full_021vp2qu_1_1'; RMAN> catalog backuppiece '/home/oracle/orcl_full_031vp2qu_1_1'; RMAN> catalog backuppiece '/home/oracle/orcl_full_041vp2s7_1_1'; RMAN> catalog backuppiece '/home/oracle/orcl_full_051vp2s9_1_1'; RMAN> run { allocate channel d1 type disk; allocate channel d2 type disk; restore database; recover database; release channel d1; release channel d2; }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
三、只能mount同步，无法只读打开
3.1.备库开启同步
SQL> alter database recover managed standby database using current logfile disconnect from session;- 1
3.2.验证是否同步
## 备库查询MRP0恢复进程应用块数 SQL> SELECT PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS,DELAY_MINS FROM V$MANAGED_STANDBY; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS ------- ------------ --------- --------- ------ ------ ---------- DGRD ALLOCATED 0 0 0 0 0 ARCH CLOSING 1 34 1 342 0 DGRD ALLOCATED 0 0 0 0 0 ARCH CONNECTED 0 0 0 0 0 ARCH CONNECTED 0 0 0 0 0 ARCH CONNECTED 0 0 0 0 0 RFS IDLE 1 0 0 0 0 RFS IDLE 0 0 0 0 0 RFS IDLE 1 35 23 1 0 MRP0 APPLYING_LOG 1 35 23 102400 0 10 rows selected. SQL> / PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS ------- ----------- ------- --------- ------ ------ ---------- DGRD ALLOCATED 0 0 0 0 0 ARCH CLOSING 1 34 1 342 0 DGRD ALLOCATED 0 0 0 0 0 ARCH CONNECTED 0 0 0 0 0 ARCH CONNECTED 0 0 0 0 0 ARCH CONNECTED 0 0 0 0 0 RFS IDLE 1 0 0 0 0 RFS IDLE 0 0 0 0 0 RFS IDLE 1 35 27 1 0 MRP0 APPLYING_LOG 1 35 27 102400 0 10 rows selected. SQL> / PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS ------- ---------- ------- --------- ------ ------ ---------- DGRD ALLOCATED 0 0 0 0 0 ARCH CLOSING 1 34 1 342 0 DGRD ALLOCATED 0 0 0 0 0 ARCH CONNECTED 0 0 0 0 0 ARCH CONNECTED 0 0 0 0 0 ARCH CONNECTED 0 0 0 0 0 RFS IDLE 1 0 0 0 0 RFS IDLE 0 0 0 0 0 RFS IDLE 1 35 28 1 0 MRP0 APPLYING_LOG 1 35 28 102400 0 10 rows selected. ## 主库查询归档日志序列号 SQL> select max(sequence#),thread# from v$archived_log where RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT') GROUP BY THREAD#; MAX(SEQUENCE#) THREAD# -------------- ------- 34 1 ## 备端查询归档日志序列号 SQL> select max(sequence#),thread# from v$archived_log where applied='YES' and RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT') GROUP BY THREAD#; MAX(SEQUENCE#) THREAD# -------------- ------- 33 1 ## 检查alert日志是否正常 2023-06-27T16:44:48.145275+08:00 MRP0 (PID:24798): Media Recovery Waiting for T-1.S-36 2023-06-27T16:44:48.243077+08:00 rfs (PID:24785): Selected LNO:4 for T-1.S-36 dbid 1668400254 branch 1140619521 2023-06-27T16:44:48.243519+08:00 ARC1 (PID:24769): Archived Log entry 5 added for T-1.S-35 ID 0x6371a37e LAD:1 2023-06-27T16:44:49.181453+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 36 Reading mem 0 Mem# 0: /oradata/orcldg/standby_redo04.log ## 备库应用是否存在延迟 SQL> select name,value from v$dataguard_stats; NAME VALUE --------------------- ------------ transport lag +00 00:00:00 apply lag +00 00:00:00 apply finish time estimated startup time 5- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
四、主库创建测试表，备库升级后验证数据是否同步
SQL> create user test identified by admin; User created. SQL> grant create session,resource to test; Grant succeeded. SQL> conn test/admin Connected. SQL> create table t1 (id number); Table created. SQL> insert into t1 values(1); 1 row created. SQL> commit; Commit complete. SQL> select * from t1; ID ------ 1- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
五、备库进行故障转移
由于跨版本无法进行正常的主备切换，故采用故障转移的方式，将备库改为主库，进行升级操作。
## 备库 SQL> alter database recover managed standby database finish; SQL> alter database commit to switchover to primary; ## 升级模式打开数据库 SQL> shutdown immediate SQL> startup upgrade- 1
- 2
- 3
- 4
- 5
- 6
- 7
六、开始升级
## 切换后的新主库执行升级脚本 cd $ORACLE_HOME/bin ./dbupgrade 或 $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql- 1
- 2
- 3
- 4
- 5
七、升级后打开数据库，验证同步数据
## 启动数据库 SQL> startup SQL> conn test/admin Connected. SQL> select * from t1; ID ----- 1 升级后，数据正常，所以此方式可以作为升级迁移数据的一种方式。- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
八、部署过程中遇到的问题
8.1.不能使用rman duplicate方式复制主端数据库
因为主备端RMAN版本不一致造成此方式无法使用，报错现象如下：
[oracle@dg-19 ~]$ rman target sys/admin@orcl auxiliary sys/admin@orcldg nocatalog Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 27 15:59:11 2023 Version 19.13.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed RMAN-06429: TARGET database is not compatible with this version of RMAN RMAN-06618: RMAN client and database version mismatch; indicated database version is 11.2.0.4- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
8.2.升级后用户无法登录的问题
## 错误现象： SQL> conn test/admin ERROR:ORA-01017: invalid username/password; logon denied ## 原因 Oracle 19c SEC_CASE_SENSITIVE_LOGON参数对用户登录的影响： （1）设置为FALSE，关闭密码大小写限制，会造成用户无法登录。 （2）设置为TRUE，开启密码大小写限制，用户登录正常。 ## 解决方法 （1）alter system set sec_case_sensitive_logon=true; （2）如Oracle 19c 需要开启关闭密码大小写限制。需要在sqlnet.ora 文件添加参数： SQLNET.ALLOWED_LOGON_VERSION_SERVER=8。 然后重新设置用户密码： SQL> alter user test identified by admin; SQL> conn test/admin Connected. SQL> conn test/ADMIN Connected. 也可登录成功。- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
-
相关阅读:
Java 8 的新特性还没用起来，先不要想着升级到 Java 17
使用数据分析，识别设备异常
Spring的AOP开发-基于xml配置的AOP
圆柱继承圆， 属性分析数据合理性 C#
python进阶（26）collections标准库
SpringBoot 项目实战 ~ 1. 项目介绍及基础搭建
分布式秒杀方案--java
OpenHarmony3.0如何轻松连接华为云IoT设备接入平台？
大模型的演进之路：从萌芽到ChatGPT的辉煌
企业申报两化融合有哪些好处 申报两化融合需要准备哪些材料
- 原文地址：https://blog.csdn.net/loveLAxin/article/details/133742987
