暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / oracle 11.2.0.4 rac dg主备切换

oracle 11.2.0.4 rac dg主备切换

原创 Leo 2022-11-29
961

文档课题：oracle 11.2.0.4 rac dg主备切换.

1、环境介绍

主库：Oracle 11.2.0.4.0 RAC(双节点)

备库：Oracle 11.2.0.4.0 RAC(双节点)

系统：Centos 7.9 64位

主库IP：

#Public IP (ens33)

192.168.133.210 hisdb1

192.168.133.211 hisdb2

#Private IP (ens37)

192.168.11.110 hisdb1-priv

192.168.11.111 hisdb2-priv

#Virtual IP (ens33)

192.168.133.212 hisdb1-vip

192.168.133.213 hisdb2-vip

#Scan IP (ens33)

192.168.133.214 hisdb-scan

 

备库IP：

#Public IP (ens33)

192.168.133.220 hisdb3

192.168.133.221 hisdb4

 

#Private IP (ens37)

192.168.11.120 hisdb3-priv

192.168.11.121 hisdb4-priv

 

#Virtual IP

192.168.133.222 hisdb3-vip

192.168.133.223 hisdb4-vip

 

#Scan IP

192.168.133.225 hisdb-scan

2、数据检查

主备切换前检查数据库情况.

--查活动会话分布，主库节点1执行.

SQL> set line 200 pagesize 200

SQL> select inst_id,username,status,count(*) from gv$session group by inst_id,username,status order by 3,4;

 

   INST_ID USERNAME                       STATUS     COUNT(*)

---------- ------------------------------ -------- ----------

         2 SYS                            ACTIVE            1

         1 SYS                            ACTIVE            2

         1                                ACTIVE           47

         2                                ACTIVE           47

         1 SYS                            INACTIVE          1

         2 SYS                            INACTIVE          2

 

6 rows selected.

--查长事务.

set linesize 200

set pagesize 5000

col transaction_duration format a45

with transaction_details as

 (select inst_id, ses_addr, sysdate - start_date as diff from gv$transaction)

select s.username,

       to_char(trunc(t.diff)) || ' days, ' ||

       to_char(trunc(mod(t.diff * 24, 24))) || ' hours, ' ||

       to_char(trunc(mod(t.diff * 24 * 60, 24))) || ' minutes, ' ||

       to_char(trunc(mod(t.diff * 24 * 60 * 60, 60))) || ' seconds' as transaction_duration,

       s.program,

       s.terminal,

       s.status,

       s.sid,

       s.serial#

  from gv$session s, transaction_details t

 where s.inst_id = t.inst_id

   and s.saddr = t.ses_addr

 order by t.diff desc

--检查事务

select undoblockstotal "Total",

       undoblocksdone "Done",

       undoblockstotal - undoblocksdone "ToDo",

       decode(cputime,

              0,

              'unknown',

              to_char(sysdate + (((undoblockstotal - undoblocksdone) /

                      (undoblocksdone / cputime)) / 86400),

                      'yyyy-mm-dd hh24:mi:ss')) "Estimated time to complete",

       to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')

  from v$fast_start_transactions;

--检查job

col schedule_owner for a20

col schedule_name for a30

col owner for a15

col job_name for a30

col job_creator for a15

set line 300

col JOB_ACTION for a40

col COMMENTS for a40

col last_start_date for a40

col last_run_duration for a35

col job_action for a70

select owner,job_name,enabled,JOB_ACTION,LAST_START_DATE,LAST_RUN_DURATION

--,comments

from dba_scheduler_jobs where owner <> 'SYS';

 

OWNER           JOB_NAME                       ENABL JOB_ACTION                                               LAST_START_DATE                      LAST_RUN_DURATION

--------------- ------------------------------ ----- ---------------------------------------------------------------------- ---------------------------------------- -----------------------------------

EXFSYS          RLM$EVTCLEANUP          TRUE  begin dbms_rlmgr_dr.cleanup_events; end;                               28-NOV-22 07.49.46.770281 AM -07:00      +000000000 00:00:00.015206

EXFSYS          RLM$SCHDNEGACTION      TRUE  begin dbms_rlmgr_dr.execschdactions('RLM$SCHDNEGACTION'); end;      28-NOV-22 10.21.18.027308 PM +08:00      +000000000 00:00:00.064587

ORACLE_OCM    MGMT_CONFIG_JOB         TRUE  ORACLE_OCM.MGMT_CONFIG.collect_config                         28-NOV-22 01.01.01.233236 AM -07:00      +000000000 00:00:00.709359

ORACLE_OCM    MGMT_STATS_CONFIG_JOB  TRUE  ORACLE_OCM.MGMT_CONFIG.collect_stats                           25-NOV-22 09.24.14.100917 PM -07:00      +000000000 00:00:00.216690

--查dg参数.

set linesize 500 pages 100

col value for a90

col name for a30

select name, value

  from v$parameter

 where name in ('db_name',

                'db_unique_name',

                'log_archive_config',

                'log_archive_dest_1',

                'log_archive_dest_2',

                'log_archive_dest_state_1',

                'log_archive_dest_3',

                'log_archive_dest_state_3',

                'log_archive_dest_state_2',

                'remote_login_passwordfile',

                'log_archive_format',

                'log_archive_max_processes',

                'fal_server',

                'db_file_name_convert',

                'log_file_name_convert',

                'standby_file_management');

 

NAME                           VALUE

------------------------------ ------------------------------------------------------------------------------------------

db_file_name_convert

log_file_name_convert

log_archive_dest_1             location=+fra

log_archive_dest_2             service=healdg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=healdg

log_archive_dest_3

log_archive_dest_state_1       ENABLE

log_archive_dest_state_2       ENABLE

log_archive_dest_state_3       enable

fal_server

log_archive_config             dg_config=(heal,healdg)

log_archive_format             %t_%s_%r.dbf

log_archive_max_processes      4

standby_file_management        AUTO

remote_login_passwordfile      EXCLUSIVE

db_name                        heal

db_unique_name                 heal

 

16 rows selected.

--查dg进程状态.

col dest_name for a30

col error for a50

set lin 200 pages 100

col applied_scn for 9999999999999

select dest_id,

       error,

       status,

       log_sequence,

       applied_scn,

       MAX_CONNECTIONS,

       NET_TIMEOUT,

       COMPRESSION

  from v$archive_dest

 where dest_id < 5;

 

   DEST_ID ERROR                       STATUS    LOG_SEQUENCE    APPLIED_SCN MAX_CONNECTIONS NET_TIMEOUT COMPRES

---------- -------------------------------------------------- --------- ------------ -------------- --------------- ----------- -------

         1                                VALID               31              0               1           0 DISABLE

         2                                VALID               32        1342449               1          30 DISABLE

         3                                INACTIVE             0              0               1           0 DISABLE

         4                                INACTIVE             0              0               1           0 DISABLE

 

select INST_ID, process, status, thread#, sequence#, block#, blocks

  from gv$managed_standby

 order by INST_ID;

 

   INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS

---------- --------- ------------ ---------- ---------- ---------- ----------

         1 ARCH      CLOSING               1         31          1        270

         1 ARCH      CLOSING               1         29      55296       1901

         1 ARCH      CLOSING               1         27          1      21810

         1 ARCH      CLOSING               1         30      36864       1824

         1 LNS       WRITING                1         32       7213          1

         2 ARCH      CLOSING               2         23      57344         28

         2 ARCH      CLOSING               2         21          1      21060

         2 ARCH      CLOSING               2         22          1        107

         2 ARCH      CLOSING               2         22          1        107

         2 LNS       WRITING                2         24       7624          1

 

10 rows selected.

--查主库状态.

SQL> select database_role,switchover_status from v$database;

 

DATABASE_ROLE    SWITCHOVER_STATUS

---------------- --------------------

PRIMARY          TO STANDBY

 

NOT ALLOWED             当前的数据库不是带有备用数据库的主数据库

PREPARING DICTIONARY    该逻辑备用数据库正在向一个主数据库和其他备用数据库发送它的重做数据，以便为切换做准备

PREPARING SWITCHOVER    接受用于切换的重做数据时，逻辑备用配置会使用它

RECOVERY NEEDED         备用数据库还没有接收到切换请求

SESSIONS ACTIVE        在主数据库中存在活动的SQL会话；在继续执行之前必须断开这些会话

SWITCHOVER PENDING      适用于那些已收到主数据库切换请求但是还没有处理该请求的备用数据库

SWITCHOVER LATENT       切换没有完成并返回到主数据库

TO LOGICAL STANDBY      主数据库已经收到了来自逻辑备用数据库的完整的字典

TO PRIMARY              该备用数据库可以转换为主数据库

TO STANDBY              该主数据库可以转换为备用数据库

3、切换前准备

3.1、关闭备库节点2

ssh 192.168.133.221

su - oracle

sqlplus / as sysdba

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

--主库验证gap.

SELECT LOG_ARCHIVED - LOG_APPLIED + 1 LOGGAP

  FROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVED

          FROM V$ARCHIVED_LOG

         WHERE DEST_ID = 1

           AND ARCHIVED = 'YES'

           AND RESETLOGS_CHANGE# =

               (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG)),

       (SELECT MAX(SEQUENCE#) LOG_APPLIED

          FROM V$ARCHIVED_LOG

         WHERE DEST_ID = 2

           AND APPLIED = 'YES'

           AND RESETLOGS_CHANGE# =

               (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG));

 

    LOGGAP

----------

         1

 

--备库节点1验证备库是否为rta模式，减少gap.

ssh 192.168.133.220

su - oracle

sqlplus / as sysdba

SQL> set line 200

SQL> col dest_name for a30

SQL> select dest_id,dest_name,recovery_mode from gv$archive_dest_status where recovery_mode<>'IDLE';

 

   DEST_ID DEST_NAME                      RECOVERY_MODE

---------- ------------------------------ -----------------------

         1 LOG_ARCHIVE_DEST_1             MANAGED REAL TIME APPLY

 

说明：RECOVERY_MODE=MANAGED REAL TIME APPLY  为 real time apply，否则不是.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

 

3.2、配置闪回

SQL> set line 200 pages 100

SQL> col flashback_on for a10

SQL> col current_scn for 9999999999

SQL> col open_mode for a10

SQL> col switchover_status for a20

SQL> col protection_mode for a20

SQL> col name for a20

SQL> select name,current_scn,protection_mode,database_role,force_logging,flashback_on,open_mode,switchover_status from v$database;

 

NAME                 CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR FLASHBACK_ OPEN_MODE  SWITCHOVER_STATUS

-------------------- ----------- -------------------- ---------------- --- ---------- ---------- --------------------

HEAL                     1402359 MAXIMUM PERFORMANCE  PRIMARY          YES NO         READ WRITE SESSIONS ACTIVE

--查看asm磁盘空间.

SQL> select name,total_mb,free_mb,round((total_mb-free_mb)/total_mb*100,2) "Used%" from v$asm_diskgroup;

 

NAME                   TOTAL_MB    FREE_MB      Used%

-------------------- ---------- ---------- ----------

DATA                      20479      13810      32.57

FRA                       10239       7470      27.04

OCRBK                     10239       9843       3.87

配置闪回路径.

SQL> alter system set db_recovery_file_dest_size=5g sid='*';

 

System altered.

 

SQL> alter system set db_recovery_file_dest='+fra' sid='*';

 

System altered.

 

注意：此处主库两个节点均需配置，否则会导致主备切换后节点2启动报错ORA-01677.

--开启闪回

SQL> alter database flashback on;

 

Database altered.

 

SQL> select name,current_scn,protection_mode,database_role,force_logging,flashback_on,open_mode,switchover_status from v$database;

 

NAME                 CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR FLASHBACK_ OPEN_MODE  SWITCHOVER_STATUS

-------------------- ----------- -------------------- ---------------- --- ---------- ---------- --------------------

HEAL                     1403389 MAXIMUM PERFORMANCE  PRIMARY          YES YES        READ WRITE TO STANDBY

 

SQL> create restore point prirest;

 

Restore point created.

--检查闪回点.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

 

Session altered.

 

SQL> col time for a35

SQL> select scn,time,name from v$restore_point

 

       SCN TIME                                NAME

---------- ----------------------------------- ------------------------------

   1403479 29-NOV-22 09.24.04.000000000 AM     PRIREST

4、正式切换

4.1、主切备

ssh 192.168.133.210

export ORACLE_SID=heal1

sqlplus / as sysdba

SQL> select trim(database_role) dbrole from v$database;

 

DBROLE

----------------

PRIMARY

--开启trace，用于发生问题时便于诊断.

SQL> alter system set log_archive_trace=8191 sid='*';

 

System altered.

 

SQL> show parameter log_archive_trace

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_trace                    integer     8191

--执行切换命令后，原主库会自动关闭实例.

SQL> alter database commit to switchover to physical standby with session shutdown;

 

Database altered.

 

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  855982080 bytes

Fixed Size                  2258040 bytes

Variable Size             352324488 bytes

Database Buffers          494927872 bytes

Redo Buffers                6471680 bytes

Database mounted.

说明：此时新备库节点2实例自动关闭.

4.2、备切主

说明：备库节点2在此前已关闭.

ssh 192.168.133.220

su - oracle

sqlplus / as sysdba

SQL> show parameter instance_name

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

instance_name                        string      healdg1

 

SQL> select trim(database_role) dbrole from v$database;

 

DBROLE

----------------

PHYSICAL STANDBY

 

SQL> alter system set log_archive_trace=8191 sid='*';

 

System altered.

--执行主备切换命令后，原备库自动启动到mount.

SQL> alter database commit to switchover to primary with session shutdown;

 

Database altered.

 

SQL> select open_mode from v$database;

 

OPEN_MODE

--------------------

MOUNTED

 

SQL> shutdown immediate;

ORA-01109: database not open

 

 

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  855982080 bytes

Fixed Size                  2258040 bytes

Variable Size             348130184 bytes

Database Buffers          499122176 bytes

Redo Buffers                6471680 bytes

Database mounted.

Database opened.

SQL> col line 200 pagesize 100

SQL> col flashback_on for a10

SQL> col current_scn for 999999999

SQL> col open_mode for a10

SQL> col switchover_status for a20

SQL> col protection_mode for a20

SQL> select current_scn,protection_mode,database_role,force_logging,flashback_on,open_mode,switchover_status from v$database;

 

CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR FLASHBACK_ OPEN_MODE  SWITCHOVER_STATUS

----------- -------------------- ---------------- --- ---------- ---------- --------------------

    1426063 MAXIMUM PERFORMANCE  PRIMARY          YES NO         READ WRITE RESOLVABLE GAP

 

SQL> alter system set log_archive_trace=0 sid='*';

 

System altered.

5、切换后操作

5.1、启动mrp进程

新备库启动mrp进程.

ssh 192.168.133.210

export ORACLE_SID=heal1

sqlplus / as sysdba

--此前已开启到mount阶段.

SQL> alter database open;

 

Database altered.

 

SQL> alter system set log_archive_trace=0 sid='*';

 

System altered.

 

SQL> alter database recover managed standby database using current logfile disconnect from session;

 

Database altered.

5.2、新备库启动节点2

因原主库执行主备切换后，会自动关闭实例，此处需重启新备库节点2实例.

ssh 192.168.133.211

export ORACLE_SID=heal2

[oracle@hisdb2 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 29 09:56:13 2022

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Connected to an idle instance.

SQL> startup

ORACLE instance started.

 

Total System Global Area  855982080 bytes

Fixed Size                  2258040 bytes

Variable Size             352324488 bytes

Database Buffers          494927872 bytes

Redo Buffers                6471680 bytes

Database mounted.

Database opened.

5.3、新主库启动节点2

ssh 192.168.133.221

export ORACLE_SID=healdg2

SQL> startup

ORACLE instance started.

 

Total System Global Area  855982080 bytes

Fixed Size                  2258040 bytes

Variable Size             348130184 bytes

Database Buffers          499122176 bytes

Redo Buffers                6471680 bytes

Database mounted.

Database opened.

SQL> show parameter instance_name

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

instance_name                        string      healdg2

6、切换后检查

6.1、新备库检查

ssh 192.168.133.210

export ORACLE_SID=heal1

sqlplus / as sysdba

SQL> select status from v$instance;

 

STATUS

------------

OPEN

 

SQL> set line 200 pagesize 200                                                         

SQL> select process,status,thread#,sequence#,block#,blocks from v$managed_standby;

 

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS

--------- ------------ ---------- ---------- ---------- ----------

ARCH     CLOSING               2         27          1         93

ARCH     CLOSING               1         36      12288        645

ARCH     CONNECTED             0          0          0          0

ARCH     CLOSING               2         26          1          1

RFS       IDLE                  0          0          0          0

RFS       IDLE                  0          0          0          0

RFS       IDLE                  1         37        238          1

RFS       IDLE                  0          0          0          0

MRP0     APPLYING_LOG          1         37        237     102400

RFS       IDLE                  0          0          0          0

RFS       IDLE                  0          0          0          0

RFS       IDLE                  2         28        232          1

RFS       IDLE                  0          0          0          0

 

13 rows selected.

 

set lin 200 pages 100

col FLASHBACK_ON for a10

col current_scn for 99999999999999

col open_mode for a20

col SWITCHOVER_STATUS for a20

col PROTECTION_MODE for a20

select current_scn,

       protection_mode,

       database_role,

       force_logging,

       FLASHBACK_ON,

       open_mode,

       switchover_status

  from v$database;

 

    CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR FLASHBACK_ OPEN_MODE            SWITCHOVER_STATUS

--------------- -------------------- ---------------- --- ---------- -------------------- --------------------

        1436775 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES YES        READ ONLY WITH APPLY NOT ALLOWED

6.2、新主库检查

ssh 192.168.133.220

export ORACLE_SID=healdg1

sqlplus / as sysdba

SQL> select status from v$instance;

 

STATUS

------------

OPEN

 

SQL> set lin 200 pages 100

SQL> col FLASHBACK_ON for a10

SQL> col current_scn for 99999999999999

SQL> col open_mode for a20

SQL> col SWITCHOVER_STATUS for a20

SQL> col PROTECTION_MODE for a20

SQL> select current_scn,

  2         protection_mode,

  3         database_role,

  4         force_logging,

  5         FLASHBACK_ON,

  6         open_mode,

  7         switchover_status

  8    from v$database;

 

    CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR FLASHBACK_ OPEN_MODE            SWITCHOVER_STATUS

--------------- -------------------- ---------------- --- ---------- -------------------- --------------------

        1437020 MAXIMUM PERFORMANCE  PRIMARY          YES NO         READ WRITE           SESSIONS ACTIVE

 

7、新备库添加参数

在切换成功后，新备库需添加参数以防止新主库添加数据文件时造成新备库宕机.

ssh 192.168.133.210

su - oracle

sqlplus / as sysdba

alter system set db_file_name_convert='+data/healdg/datafile/','+data/heal/datafile/','+data/healdg/tempfile/','+data/heal/tempfile/' sid='*' scope=spfile;

alter system set log_file_name_convert='+data/healdg/tempfile/','+data/heal/tempfile/','+data/healdg/onlinelog/','+data/heal/onlinelog/','+data/healdg/controlfile/','+data/heal/controlfile/' sid='*' scope=spfile;

alter system set standby_file_management='AUTO' sid='*';

alter system set fal_server='healdg' sid='*';

alter system set fal_client='heal' sid='*';

 

参考网址：

http://blog.itpub.net/29785807/viewspace-2874066/

墨力计划oracleselectlograc
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论