让开发最省心的adg主备切换

原创三石 All China Database Union 2024-08-12

1310

0、数据库信息

1、主备切换

1.1、切换前检查

查看主库是否有GAP

--主库（242）操作set line2222
col status for a10
col type for a10
col error for a20
col gap_status for a20
col synchronization_status for a30
col recovery_mode for a60
select inst_id,status,DEST_ID,TYPE,database_mode,ERROR,GAP_STATUS,SYNCHRONIZED,SYNCHRONIZATION_STATUS,RECOVERY_MODE from GV$ARCHIVE_DEST_STATUS where STatus <> 'INACTIVE' and type = 'PHYSICAL';

   INST_ID STATUS	 DEST_ID TYPE	    DATABASE_MODE   ERROR		 GAP_STATUS	      SYN SYNCHRONIZATION_STATUS	 RECOVERY_MODE
---------- ---------- ---------- ---------- --------------- -------------------- -------------------- --- ------------------------------ ------------------------------------------------------------
	 1 VALID	       2 PHYSICAL   OPEN_READ-ONLY			 NO GAP 	      NO  CHECK CONFIGURATION		 MANAGED REAL TIME APPLY

-- 注意主库 SWITCHOVER_STATUS 状态为 TO STANDBY 或者 SESSION ACTIVE 都代表可以切换

--主库（242）操作
set line2222
col name for a10
col database_role for a20
col switchover_status for a20
col PROTECTION_MODE for a25
select inst_id,name,database_role,protection_mode,switchover_status from gv$database;

   INST_ID NAME       DATABASE_ROLE	   PROTECTION_MODE	     SWITCHOVER_STATUS
---------- ---------- -------------------- ------------------------- --------------------
	 1 ORCL       PRIMARY		   MAXIMUM PERFORMANCE	     SESSIONS ACTIVE

查看是否有未应用的归档，如果有，请重新应用，或者查看时间段是否是无效信息。

--备库（243）操作
select count(*) from v$archived_log where applied='NO';

  COUNT(*)
----------
	 0

--备库（243）操作
select * from v$archive_gap;

no rows selected

主要是看MRP0进程是否存在，状态是否正常。

--备库（243）操作
select process,thread#,group#,sequence#,status from v$managed_standby;

PROCESS      THREAD# GROUP#				       SEQUENCE# STATUS
--------- ---------- ---------------------------------------- ---------- ------------
ARCH		   1 20 					     118 CLOSING
ARCH		   0 N/A					       0 CONNECTED
ARCH		   0 N/A					       0 CONNECTED
ARCH		   0 N/A					       0 CONNECTED
MRP0		   1 N/A					     119 APPLYING_LOG
RFS		   0 N/A					         0 IDLE
RFS		   0 N/A					         0 IDLE
RFS		   0 N/A					         0 IDLE
RFS		   1 5						       119 IDLE

注意备库 SWITCHOVER_STATUS 状态为 NOT ALLOWED 代表正常

--备库（243）操作
set line222
col database_role for a20
col switchover_status for a20
col PROTECTION_MODE for a25
select inst_id,name,database_role,protection_mode,switchover_status from gv$database;

   INST_ID NAME      DATABASE_ROLE	  PROTECTION_MODE	    SWITCHOVER_STATUS
---------- --------- -------------------- ------------------------- --------------------
	 1 ORCL      PHYSICAL STANDBY	  MAXIMUM PERFORMANCE	    NOT ALLOWED

1.2、主切换到备库

主库切换为备库（期间观察主库alter日志）

alter database commit to switchover to physical standby with session shutdown;
执行后看着无异样，但是执行其他命令会立即报错

select status from v$instance;

ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 6225
Session ID: 3395 Serial number: 9

这条是有外部会话的情况执行，执行其他的会失败。

1.3、检查备库信息

--备库（243）操作
set line222
col database_role for a20
col switchover_status for a20
col PROTECTION_MODE for a25
select name,database_role,protection_mode,switchover_status from gv$database;

NAME	     DATABASE_ROLE         PROTECTION_MODE		      SWITCHOVER_STATUS
--------- -------------------- ------------------------- --------------------
ORCL	     PHYSICAL STANDBY      MAXIMUM PERFORMANCE	     TO PRIMARY

1.4、备库执行切换

--备库（243）操作
无会话执行这个
alter database commit to switchover to primary;
或
有会话执行这个
alter database commit to switchover to primary with session shutdown;

1.5、切换后动作

启动新主库

--新主库（243）操作SQL> alter database open;

Database altered.

打开新的备库：

--新备库（242）startup;

开启实时应用

--新备库（242）
alter database recover managed standby database using current logfile disconnect;

查看数据库状态

--新备库（242）
SQL> select open_mode,database_role from v$database; 

OPEN_MODE	     DATABASE_ROLE
-------------------- ----------------
READ ONLY	     PHYSICAL STANDBY

1.6、验证是否切换成功

--新主库（243）操作set line2222
col status for a10
col type for a10
col error for a20
col gap_status for a20
col synchronization_status for a30
col recovery_mode for a60
select inst_id,status,DEST_ID,TYPE,database_mode,ERROR,GAP_STATUS,SYNCHRONIZED,SYNCHRONIZATION_STATUS,RECOVERY_MODE from GV$ARCHIVE_DEST_STATUS where STatus <> 'INACTIVE' and type = 'PHYSICAL';

   INST_ID    STATUS	   DEST_ID    TYPE	   DATABASE_MODE      ERROR		            GAP_STATUS	      SYN       SYNCHRONIZATION_STATUS	         RECOVERY_MODE
---------- ---------- ---------- ---------- --------------- -------------------- -------------------- --- ------------------------------ ------------------------------------------------------------
	 1          VALID	       2      PHYSICAL   OPEN_READ-ONLY		  NO GAP 	               NO            CHECK       CONFIGURATION	             	 MANAGED REAL TIME APPLY

--新备库（242）select process,thread#,group#,sequence#,status from gv$managed_standby;

PROCESS      THREAD# GROUP#				       SEQUENCE# STATUS
--------- ---------- ---------------------------------------- ---------- ----------
ARCH		   0 N/A					       0 CONNECTED
ARCH		   0 N/A					       0 CONNECTED
ARCH		   0 N/A					       0 CONNECTED
ARCH		   1 20 					     132 CLOSING
RFS		     0 N/A					       0 IDLE
RFS		     0 N/A				 	       0 IDLE
RFS		     0 N/A					       0 IDLE
RFS		     1 4						     133 IDLE
MRP0		   1 N/A					     133 APPLYING_LOG

--新主库（243）操作切换后的主库创建新用户
create user aa identified by aa;
主库切换日志
alter system switch logfile;
备库查询用户是否存在
select USERNAME,USER_ID,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where ACCOUNT_STATUS='OPEN' order by USERNAME;

如果同步没成功，尝试刷新状态（以报警日志为主进行判断）

--新主库（243）操作alter system set log_archive_dest_state_2=DEFER;
alter system set log_archive_dest_state_2=ENABLE;

2、中断主备

2.1、现主库中断归档同步

--新主库（243）操作SQL> show parameter log_archive_dest_state_2
NAME				                             TYPE	             VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2	              string	           ENABLE

SQL> alter system set log_archive_dest_state_2=defer scope=both sid='*';
System altered.

2.2、备库操作

--新备库（242）操作
--取消管理的备用数据库
SQL> alter database recover managed standby database cancel;

--关闭数据库
SQL> shu immediate

--启动数据库到mount
SQL> startup mount

--新备库（242）操作--激活备库
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

--新备库（242）操作SQL> select status from v$instance;

STATUS
----------
MOUNTED

--拉起数据库
SQL> alter database open;

SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
--------------------
PRIMARY

--新备库（242）操作
--重启数据库验证是否正常
SQL> shu immediate 

SQL> startup

--新备库（242）操作
--查询数据库状态（）
SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
--------------------
PRIMARY

2.3、修改主备库adg参数

--主备库都需要操作alter system set log_archive_dest_state_2=defer scope=both sid='*';
alter system set fal_client='' scope=both sid='*';
alter system set fal_server='' scope=both sid='*';
alter system set log_archive_config='' scope=both sid='*';
alter system set log_archive_dest_2='' scope=both sid='*';
alter system set standby_file_management=MANUAL scope=both sid='*';
alter system reset db_file_name_convert scope=spfile sid='*';
alter system reset log_file_name_convert scope=spfile sid='*';
alter system reset log_archive_dest_1 scope=spfile sid='*';

shutdown immediate;

startup;

--检查参数set pages 999 lines 999
col name for a40
col value for a40
select name,value from v$parameter where name in ('fal_client','fal_server','log_archive_config','log_archive_dest_2','standby_file_management','db_file_name_convert','log_file_name_convert','log_archive_dest_1');

3、关闭备库242（原主库）

--收集监听信息并关闭监听[oracle]$ lsnrctl status

Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...

[oracle]$ lsnrctl stop

--查看数据库涉及信息SQL> show parameter unique
NAME				        TYPE	       VALUE
----------------  ----------- ----------
db_unique_name		  string	      orcl


SQL> show parameter local_listener
NAME				         TYPE	        VALUE
----------------  ----------- -----------
local_listener		  string

--关闭数据库sqlplus / as sysdba
shutdown immediate;

--关机poweroff

如果是生产环境，是需要和云平台的同事沟通，以防有漏下的注意事项。

4、修改主库243（原备库）

--监听信息
Service "orcldg" has 1 instance(s).
  Instance "orcldg", status READY, has 1 handler(s) for this service...

SQL> show parameter unique
NAME				           TYPE	               VALUE
-------------------  ----------- ------------------------------
db_unique_name			   string	             orcldg

SQL> select instance_name, status from v$instance;
INSTANCE_NAME	      STATUS
---------------- ------------
orcldg		            OPEN

--查看实例名和数据库状态
SQL> select instance_name, status from v$instance;
INSTANCE_NAME	 STATUS
---------------- ------------
orcldg		 OPEN


--查看是哪种方式打开的
SQL> show parameter spfile;
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string	 /u01/app/oracle/product/11.2/db/dbs/spfileorcldg.ora
             
SQL> show parameter pfile;
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string	 /u01/app/oracle/product/11.2/db/dbs/spfileorcldg.ora

--关闭监听
lsnrctl stop

--创建参数文件
SQL> create pfile from spfile;
-rw-r--r-- 1 oracle oinstall    1703 Aug  9 09:34 initorcldg.ora

--关机
shutdown immediate;

--主要是修改数据库唯一名称vim /u01/app/oracle/product/11.2/db/dbs/initorcldg.ora

*.db_unique_name='orcl'

[root@orcldg ~]# vim /etc/oratab 
export ORACLE_SID=orcl

[oracle@orcldg:/home/oracle]$ source /home/oracle/.bash_profile 

[oracle@orcldg:/home/oracle]$ echo $ORACLE_SID
orcl

--修改参数文件名cd $ORACLE_HOME/dbs

cp spfileorcldg.ora spfileorcl.ora
mv spfileorcldg.ora spfileorcldg.ora.20240809

cp initorcldg.ora initorcl.ora
mv initorcldg.ora initorcl.ora.20240809

--创建密码文件orapwd file=orapworcl password=Oracle12#  entries=5 force=y;

--修改服务名，这样也是orcl了SQL> show parameter service_names
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
service_names			     string	 orcldg

SQL> alter system set service_names='orcl' scope=both;
System altered.

SQL> show parameter service_name
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
service_names			     string	 orcl

--如果不放心可以再次设置一下数据库唯一名称SQL> alter system set db_unique_name='orcl' scope=spfile;

shutdown immediate;

--修改服务器IP地址
vim /etc/sysconfig/network-scripts/ifcfg-ens192 
IPADDR=192.168.254.242

--修改服务器对比信息
vim /etc/hosts
192.168.254.242 orcldg
#192.168.254.243 orcldg

--查看是否需要修改监听信息
vim /u01/app/oracle/product/11.2/db/network/admin/listener.ora

--查看是否需要修改TNS信息
vim /u01/app/oracle/product/11.2/db/network/admin/tnsnames.ora

vi /etc/oratab
##修改为Y
orcl:/u01/app/oracle/product/11.2/db:Y

重启服务器

reboot

启动监听和数据库

su - oracle
lsnrctl start

sqlplus / as sysdba
startup;

查看数据库参数，这两个参数是组成监听日志的关键信息

select name,value from v$parameter where name in ('service_names','db_unique_name');

IP:端口/服务名

192.168.254.242:1521/orcl

确定是否切换成功还可以看主机名是否还是备库的orcldg

5、涉及额外命令

除了由数据库本身自带的服务名，其他的都可以删掉

select name from dba_services;
NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
orcldg
orclXDB
orcl

exec dbms_service.stop_service('orcldg');
PL/SQL procedure successfully completed.

exec dbms_service.delete_service('orcldg');
PL/SQL procedure successfully completed.

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者