show parameter name;
show parameter log_archive_config;
生产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) |
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,查看是否通。
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,查看是否通。
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/ |
配置规律为,对端的数据路径配置在前,本端路径在后,成对出现。
Select name from v$datafile;
Select name from v$tempfile;
show parameter standby_file_management
查看该参数是否设置为auto
生产及容灾确认设置为AUTO,如下:
standby_file_management string AUTO |
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 |
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> |
Select * from v$passwordfile_info;
查看FORMAT字段
确认FORMAT是否为12C,或者保证主及容灾一致;
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及端口、监听情况查看配置是否正确。
文件系统使用率:
df –g
磁盘组使用率:
Asmcmd lsdg
查看关键文件系统使用率未超过90%;
查看磁盘组使用率未超过90%或有足够空间;
确认文件系统及磁盘空间足够;
Select a.force_logging,a.log_mode,a.name from v$database a;
查看force_logging,log_mode设置情况。
数据库处于归档模式(ARCHIVELOG)且处于强制归档模式为YES。
Topas
查看cpu及内存使用率在50%以下;
评估容灾节点cpu及内存使用足够。
Date 查看主备时间是否一致
如果不一致,建议使用ntp逐步调整为一致;
使用如下命令查看:
Sqlplus / as sysdba
Show parameter name
使用如下命令查看:
More /etc/hosts
进入到root用户下,使用su – oracle,su – grid查看有无“you have mails”提示,如有,需提前处理;
set lines 300
select PROCESS,PID,STATUS, GROUP# , RESETLOG_ID,THREAD#,SEQUENCE# from gv$managed_standby;
需对两边的SEQUENCE#进行检查。
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
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;
col dest_name for a40
set lines 300
select DEST_NAME,status,error from v$archive_dest;
select THREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE# from v$archive_gap;
生产(源端)操作:
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> |
说明数据已经成功同步到容灾
Root用户:
root@testdb2[/u01/app/12.1/grid/bin]#./crsctl stop crs
查询同步延迟、准备切换状态正常后:
SQL> alter database switchover to dr_cbsdb verify; Database altered. SQL> alter database switchover to dr_cbsdb; Database altered. |
切换后,主库一节点自动停机,二节点变为mount状态
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. |
SQL> select status from v$instance; STATUS ------------ MOUNTED SQL> SQL> alter database open; Database altered. SQL> select status from v$instance; STATUS ------------ OPEN |
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; |
SQL> alter database switchover to cbsdb verify; Database altered. SQL> alter database switchover to cbsdb; Database altered. |
切换后,容灾库自动停机
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. |
SQL> select status from v$instance; STATUS ------------ MOUNTED SQL> SQL> alter database open; Database altered. SQL> select status from v$instance; STATUS ------------ OPEN |
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;