Oracle ADG切换检查及操作

admin2024-08-29  4

Oracle ADG切换检查及操作,第1张

配置检查

1、数据库名称及log_archive_config检查

使用命令

show parameter name;

show parameter log_archive_config;

查看点

  1. 查看数据库db_unique_name、db_name、service_names 设置
  2. 查看log_archive_config是否配置了正确的生产及容灾db_unique_name

确认点

生产show parameter name;输出如下:

一般db_unique_name、db_name、service_names 三者一致:

SQL> show parameter name;

NAME                                 TYPE        VALUE

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

cell_offloadgroup_name               string

db_file_name_convert                 string      /oradata/DATAFILE/, +DATA/CBSD

                                                 B/DATAFILE/, /oradata/TEMPFILE

                                                 /, +DATA/CBSDB/TEMPFILE/

db_name                              string      cbsdb

db_unique_name                       string      cbsdb

global_names                         boolean     FALSE

instance_name                        string      cbsdb1

lock_name_space                      string

log_file_name_convert                string      /oradata/LOGFILE/, +DATA/CBSDB

                                                 /ONLINELOG/

pdb_file_name_convert                string

processor_group_name                 string

service_names                        string      cbsdb

SQL> show parameter log_archive_config;

NAME                                 TYPE        VALUE

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

log_archive_config                   string      dg_config=(cbsdb, dr_cbsdb)

容灾侧:

根据命名规则,一般容灾侧db_unique_name为 dr_dbname,同时,为保障服务名称的一致性,在service_names中,应存在一个与生产一致的servername,即下文的CBSDB

SQL> show parameter name;

NAME                                 TYPE                             VALUE

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

cell_offloadgroup_name               string

db_file_name_convert                 string                           +DATA/CBSDB/DATAFILE/, /oradat

                                                                      a/DATAFILE/, +DATA/CBSDB/TEMPF

                                                                      ILE/, /oradata/TEMPFILE/

db_name                              string                           cbsdb

db_unique_name                       string                           dr_cbsdb

global_names                         boolean                          FALSE

instance_name                        string                           cbsdb1

lock_name_space                      string

log_file_name_convert                string                           +DATA/CBSDB/ONLINELOG/, /orada

                                                                      ta/LOGFILE/

pdb_file_name_convert                string

processor_group_name                 string

service_names                        string                           DR_CBSDB, CBSDB

SQL> show parameter log_archive_config;

NAME                                 TYPE                             VALUE

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

log_archive_config                   string                           dg_config=(cbsdb,dr_cbsdb)

2、查看log_archive_dest_2 、 log_archive_dest_state_2配置

使用命令

show parameter log_archive_dest_2;

show parameter log_archive_dest_state_2;

如有必要,对1 3也进行检查;

查看点

查看log_archive_dest_2 中的service、db_unique_name、log_archive_dest_state_2配置

确认点

(1)service应为到对端的tns、db_unique_name是否为对端的db_unique_name,如不是,需要修改。

(2)确认log_archive_dest_state_2处于enable状态

示例如下:

生产侧:

SQL> show parameter log_archive_dest_2;

NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string      service="DR_CBSDB", LGWR ASYNC

                                                  AFFIRM delay=0 optional compr

                                                 ession=disable max_failure=0 m

                                                 ax_connections=1 reopen=300 db

                                                 _unique_name="dr_cbsdb" net_ti

                                                 meout=30, valid_for=(all_logfi

                                                 les,primary_role)

service="DR_CBSDB" 是TNS中配置到容灾的TNS,db _unique_name="dr_cbsdb"是配置的容灾的db_unique_name

SQL>  show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE

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

log_archive_dest_state_2             string      ENABLE

VALUE处于ENABLE状态。

容灾侧:

SQL> show parameter log_archive_dest_2;

NAME                                 TYPE                             VALUE

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

log_archive_dest_2                   string                           service="PR_CBSDB", LGWR ASYNC

                                                                       AFFIRM delay=0 optional compr

                                                                      ession=disable max_failure=0 m

                                                                      ax_connections=1 reopen=300 db

                                                                      _unique_name="cbsdb" net_timeo

                                                                      ut=30, valid_for=(all_logfiles

                                                                      ,primary_role)

service="PR_CBSDB" 是TNS中配置到生产的TNS,db _unique_name="cbsdb"是配置的生产db_unique_name。

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE                             VALUE

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

log_archive_dest_state_2             string                           enable

VALUE处于enable或者ENABLE状态。

使用TNSPING命令对上述两个TNS进行PING,查看是否通。

3、查看fal_server参数

使用命令:

show parameter fal_server

查看点

fal_server参数配置

确认点

fal_server是否设置为容灾端TNS,并使用tnsping进行确认

生产配置示例如下:

SQL> show parameter fal_server

NAME                                 TYPE        VALUE

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

fal_server                           string      DR_CBSDB

DR_CBSDB生产到容灾的tns。

容灾库配置示例如下:

SQL> show parameter fal_server

NAME                                 TYPE                             VALUE

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

fal_server                           string                           PR_CBSDB

PR_CBSDB容灾生产的tns。

使用TNSPING命令对上述两个TNS进行PING,查看是否通。

4、查看db_file_name_convertlog_file_name_conver参数

使用命令

show parameter convert

查看点

查看db_file_name_convert、log_file_name_convert参数设置

确认点

确认配置了数据文件、临时文件、在线日志文件映射。

生产配置示例如下:

db_file_name_convert                 string      /oradata/DATAFILE/, +DATA/CBSD

                                                 B/DATAFILE/, /oradata/TEMPFILE

                                                 /, +DATA/CBSDB/TEMPFILE/

log_file_name_convert                string      /oradata/LOGFILE/, +DATA/CBSDB

                                                 /ONLINELOG/

SQL> show parameter convert

NAME                                 TYPE                             VALUE

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

_convert_set_to_join                 boolean                          FALSE

db_file_name_convert                 string                           +DATA/CBSDB/DATAFILE/, /oradat

                                                                      a/DATAFILE/, +DATA/CBSDB/TEMPF

                                                                      ILE/, /oradata/TEMPFILE/

log_file_name_convert                string                           +DATA/CBSDB/ONLINELOG/, /orada

                                                                      ta/LOGFILE/

配置规律为,对端的数据路径配置在前,本端路径在后,成对出现。

5、检查数据文件及临时文件路径

Select name from v$datafile;

Select name from v$tempfile;

6、查看standby_file_management参数

使用命令

show parameter standby_file_management

查看点

查看该参数是否设置为auto

确认点

生产及容灾确认设置为AUTO,如下:

standby_file_management              string      AUTO

6、查看STANDBY日志组配置

使用命令

Set lines 300

col MEMBER for a60

select * from v$logfile;

select * from v$standby_log;

查看点

查看是否配置了STANDBY日志组:数量是每个实例logfile数量+1有thread、size大小

确认点

确认配置了STANDBY日志组,且每个实例的STANDBY日志做均比重做日子组多一个。

示例如下:

   GROUP# STATUS  TYPE    MEMBER                                                       IS_     CON_ID

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

         2         ONLINE  +DATA/CBSDB/ONLINELOG/group_2.263.1042394509                 NO           0

         2         ONLINE  +ARCH/CBSDB/ONLINELOG/group_2.258.1042394509                 YES          0

         1         ONLINE  +DATA/CBSDB/ONLINELOG/group_1.262.1042394509                 NO           0

         1         ONLINE  +ARCH/CBSDB/ONLINELOG/group_1.257.1042394509                 YES          0

         3         ONLINE  +DATA/CBSDB/ONLINELOG/group_3.266.1042394981                 NO           0

         3         ONLINE  +ARCH/CBSDB/ONLINELOG/group_3.259.1042394981                 YES          0

         4         ONLINE  +DATA/CBSDB/ONLINELOG/group_4.267.1042394981                 NO           0

         4         ONLINE  +ARCH/CBSDB/ONLINELOG/group_4.260.1042394981                 YES          0

         9         STANDBY +DATA/CBSDB/ONLINELOG/group_9.368.1049382807                 NO           0

        10         STANDBY +DATA/CBSDB/ONLINELOG/group_10.369.1049382807                NO           0

        11         STANDBY +DATA/CBSDB/ONLINELOG/group_11.370.1049382809                NO           0

    GROUP# STATUS  TYPE    MEMBER                                                       IS_     CON_ID

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

        12         STANDBY +DATA/CBSDB/ONLINELOG/group_12.371.1049382809                NO           0

        13         STANDBY +DATA/CBSDB/ONLINELOG/group_13.372.1049382811                NO           0

        14         STANDBY +DATA/CBSDB/ONLINELOG/group_14.373.1049382811                NO           0

        15         STANDBY +DATA/CBSDB/ONLINELOG/group_15.374.1049382811                NO           0

        16         STANDBY +DATA/CBSDB/ONLINELOG/group_16.375.1049382813                NO           0

        17         STANDBY +DATA/CBSDB/ONLINELOG/group_17.376.1049382813                NO           0

7、查看sys远程登录参数

使用命令

show parameter remote remote_login_passwordfile

查看点

查看remote_login_passwordfile参数配置

确认点

确认参数设置为EXCLUSIVE,保障sys用户可以远程登录,示例如下:

SQL> show parameter remote_login_passwordfile

NAME                                 TYPE        VALUE

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

remote_login_passwordfile            string      EXCLUSIVE

SQL>

8查看密码文件FORMAT版本信息(12.2以上版本查看)

使用命令

Select * from v$passwordfile_info;

查看点

查看FORMAT字段

确认点

确认FORMAT是否为12C,或者保证主及容灾一致;

9、检查ADG同步使用监听及TNS

使用命令

Grid用户操作:

监听:

ps –ef|grep tns

lsnrctl status listenername

cat  $ORACLE_HOME/network/admin/listener.ora

oracle用户:

TNS:

cat  $ORACLE_HOME/network/admin/tnsnames.ora

查看同步使用的TNS,并进行tnsping

查看点

监听查看:

1、是否配置为静态监听;

2、端口配置、服务是否注册、状态是否正常

TNS:

Tnsping 是否可以ping通,tns中配置的ip地址是否正确

确认点

监听配置确认如下:

(1)静态监听配置检查,示例如下:

LISTENER_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)))

ADR_BASE_LISTENER_DG = /u01/app/oracle

SID_LIST_LISTENER_DG =

(SID_LIST =

  (SID_DESC =

    (GLOBAL_DBNAME = CBSDB)

    (SID_NAME = cbsdb1)

    (ORACLE_HOME = /u01/app/oracle/product/12.1/db_1)

  )

)

查看监听是否启动,确认IP、端口、service、status,示例如下:

status UNKNOWN 静态监听配置,状态为UNKNOWN

(2)TNS配置

cat  $ORACLE_HOME/network/admin/tnsnames.ora

查看FAL_SERVER中配置的TNS是否可以TNSPING 通,示例如下:

SQL> show parameter fal

NAME                                 TYPE        VALUE

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

fal_client                           string

fal_server                           string      DR_CBSDB

SQL> host tnsping DR_CBSDB

TNS Ping Utility for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production on 10-MAY-2021 13:01:16

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE = YES) (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DR_CBSDB)))

OK (0 msec)

SQL>

Ping通为正常,否则需要结合对端IP及端口、监听情况查看配置是否正确。

10、磁盘组使用率及文件系统使用

使用命令

文件系统使用率:

df –g

磁盘组使用率:

Asmcmd lsdg

查看点

查看关键文件系统使用率未超过90%;

查看磁盘组使用率未超过90%或有足够空间;

确认点

确认文件系统及磁盘空间足够;

11、检查数据库是否FORCE LOGGING

使用命令

Select a.force_logging,a.log_mode,a.name from v$database a;

查看点

查看force_logging,log_mode设置情况。

确认点

数据库处于归档模式(ARCHIVELOG)且处于强制归档模式为YES。

12、查看CPU及内存使用率

使用命令

Topas

查看点

查看cpu及内存使用率在50%以下;

确认点

评估容灾节点cpu及内存使用足够。

13主备时钟检查

Date 查看主备时间是否一致

如果不一致,建议使用ntp逐步调整为一致;

14、查看数据库db_name,db_unique_name名称

使用如下命令查看:

Sqlplus / as sysdba

Show parameter name

15、查看hosts配置是否正确

使用如下命令查看:

More /etc/hosts

16、切换root、oracle、grid用户查看是否有mail提示

进入到root用户下,使用su – oracle,su – grid查看有无“you have mails”提示,如有,需提前处理;

、复制状态检查

1、主备同步进程状态检查  

set lines 300

select PROCESS,PID,STATUS, GROUP# , RESETLOG_ID,THREAD#,SEQUENCE# from gv$managed_standby;

需对两边的SEQUENCE#进行检查。

2、备库同步延迟                                                                                                           

set echo off                                                                                                             

set lines 300 pages 50                                                                                           

set heading on                                                                                                   

set verify off                                                                                                   

col name for a30                                                                                                 

col value for a30                                                                                                

col TIME_COMPUTED for a20                                                                                        

col datum_time for a20 heading 'LAST_RECEIVED_TIME'                                                              

col inst_id for 99 heading 'ID'                                                                                  

break on inst_id                                                                                                 

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

select inst_id,name,value,time_computed,DATUM_TIME,sysdate from gv$dataguard_stats order by inst_id;

同步延迟为0

3、主备数据库ADG状态检查                                                                                                          

set lines 200                                                                                                            

col DATABASE_ROLE for a18                                                                                                

col PROTECTION_MODE for a20                                                                                              

col PROTECTION_LEVEL for a20                                                                                             

col FLASHBACK_ON for a10                                                                                                 

col NAME for a10                                                                                                         

col SWITCHOVER_STATUS for a15                                                                                            

select NAME,DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS,FLASHBACK_ON,OPEN_MODE from v$database;     

4、主备查看同步有错在哪里                                                                                                       

col dest_name for a40                                                                                                    

set lines 300                                                                                                            

select DEST_NAME,status,error from v$archive_dest;

5、备库查看是否有GAP

select THREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE# from v$archive_gap;

生产到容灾数据复制验证

1、生产端产生数据库

生产(源端)操作:

create user pdv_test identified by pqvds_998;

grant connect to pdv_test;

grant create table to pdv_test;

grant UNLIMITED tablespace to pdv_test;

create table pdv_test.pr_dr_verifition(id varchar(10),name varchar(30),name2 varchar(30),name3 varchar(30));

declare 

  i number := 1;

 begin 

   for i in 1 .. 10000 loop 

     insert into pdv_test.pr_dr_verifition

        (id,name,name2,name3)

        values

        (i,'1000001'+i,'1000002'+i,'1000002'+i);

        end loop;

        commit;

end;

/

select count(1) from pdv_test.pr_dr_verifition;

SQL> select count(1) from pdv_test.pr_dr_verifition;

  COUNT(1)

----------

     10000

SQL>

2、容灾端验证

SQL> select count(1) from pdv_test.pr_dr_verifition;

  COUNT(1)

----------

     10000

SQL>

说明数据已经成功同步到容灾

四、容灾切换准备

1、关闭生产中心节点

Root用户:

root@testdb2[/u01/app/12.1/grid/bin]#./crsctl stop crs

容灾切换

查询同步延迟、准备切换状态正常后:

1、主库一节点执行切换

SQL> alter database switchover to dr_cbsdb verify;

Database altered.

SQL> alter database switchover to dr_cbsdb;

Database altered.

切换后,主库一节点自动停机,二节点变为mount状态

2、启动生产一节点数据库

SQL> startup

ORACLE instance started.

Total System Global Area 7113539584 bytes

Fixed Size                  5373576 bytes

Variable Size            1845494136 bytes

Database Buffers         5251268608 bytes

Redo Buffers               11403264 bytes

Database mounted.

Database opened.

3、将原容灾库从mount状态启动open状态

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL>

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS

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

OPEN

4、在原生产端一节点启动同步

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

六、容灾切换数据库验证

在容灾建表:

create table pdv_test.dr_pr_verifition(id varchar(10),name varchar(30),name2 varchar(30),name3 varchar(30));

declare 

  i number := 1;

 begin 

   for i in 1 .. 1000 loop 

     insert into pdv_test.dr_pr_verifition

        (id,name,name2,name3)

        values

        (i,'1000001'+i,'1000002'+i,'1000002'+i);

        end loop;

        commit;

end;

/

select count(1) from pdv_test.dr_pr_verifition;

在生产确认:

select count(1) from pdv_test.dr_pr_verifition;

七、反向切换

1、容灾库一节点执行切换

SQL> alter database switchover to cbsdb verify;

Database altered.

SQL> alter database switchover to cbsdb;

Database altered.

切换后,容灾库自动停机

2、启动容灾数据库

SQL> startup

ORACLE instance started.

Total System Global Area 7113539584 bytes

Fixed Size                  5373576 bytes

Variable Size            1845494136 bytes

Database Buffers         5251268608 bytes

Redo Buffers               11403264 bytes

Database mounted.

Database opened.

3、将原生产从mount状态启动open状态

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL>

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS

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

OPEN

4、在容灾端一节点启动同步

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

生产实际切换注意事项

1、应用停止完成后,开始运行脚本前,首先停止二节点

Sh 10_product_node2_database_shutdown_oracle.sh

2、VIP及SCAN

3、PR_LISTENER监听

其它常用命令

alter system set log_archive_dest_state_2=defer sid='*';                                                                 

alter system set log_archive_dest_state_2=enable sid='*';                                                            

alter system set log_archive_dest_state_3=defer sid='*';                                                                 

alter system set log_archive_dest_state_3=enable sid='*';

alter database recover managed standby database cancel;

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

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明原文出处。如若内容造成侵权/违法违规/事实不符,请联系SD编程学习网:675289112@qq.com进行投诉反馈,一经查实,立即删除!