Oracle DB ADG（198.196.1.1與198.196.1.2）主備切換

工作项目

• atuo delete archive logfile ，
• auto start oracle
• atuo expdp and sent to SFTP
• 主备切换

[oracle@VM_Main-os ~]$ mkdir /data/expbak
SQL> select * from dba_directories;	
SQL> create directory expbak as '/data/expbak/';     --在SQLPlus 环境下指定目录

定时任务： 删除/备份/上传FTP

[oracle@VM_Main-os ~]$ crontab -l
	5 0 * * * sh /home/oracle/run/rman_delete_archivelog.sh
	10 0 * * 6 sh /home/oracle/run/fullbak.sql

删除脚本

[oracle@VM_Main-os ~]$ cat /home/oracle/run/rman_delete_archivelog.sh
export ORACLE_BASE=/home/oracle
export ORACLE_SID=db
export ORACLE_HOME=/home/oracle/product/11.2.3/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
rman target / log=/home/oracle/run/del_archivelog.log <<EOF
crosscheck archivelog all;
DELETE NOPROMPT ARCHIVELOG all completed before 'sysdate-5';
exit
EOF

expdb 备份脚本 上传 ftp 脚本

[oracle@VM_Main-os ~]$ cat /home/oracle/run/fullbak.sql
export ORACLE_BASE=/home/oracle
export ORACLE_SID=db
export ORACLE_HOME=/home/oracle/product/11.2.3/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

cd /data/expbak
rm -f db_fullbak*
NOW=`date +%F`

expdp system/syspwd directory=expbak dumpfile=db_fullbak_%U_$NOW.dmp logfile=db_fullbak_$NOW.log compression=ALL full =y reuse_dumpfiles=y parallel=4
gzip db_fullbak_*.dmp

echo "ftp start    === `date`"
HOST=ftpipxxx
USER=sftpuser
PASSWD=test123

sshpass -p $PASSWD sftp -o StrictHostKeyChecking=no $USER@$HOST << !
lcd /data/expbak
cd /test_198.196.1.2
put db*
bye
!

#OR   yum -y install lftp
#lftp -u ${USER},${PASSWD} sftp://${HOST} << !
#lcd /data/expbak/
#cd /agile9_10.66.12.176
#mput all_database_agile9*
#bye
#!

echo "ftp finished  === `date`"

rm -fr  /data/expbak/*.gz

OS 重启后自动化启动Oracle

[root@VM_Main-os ~]# cat /etc/rc.local
#!/bin/bash
# THIS FILE IS ADDED FOR COMPATIBILITY PURPOSES
#
# It is highly advisable to create own systemd services or udev rules
# to run scripts during boot instead of using this file.
#
# In contrast to previous versions due to parallel execution during boot
# this script will NOT be run after all other services.
#
# Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure
# that this script will be executed during boot.

touch /var/lock/subsys/local
su - oracle -c 'sh /home/oracle/run/startDG.sh' 1>/home/oracle/run/startDG.log 2>/home/oracle/run/startDG.err

启动 Oracle脚本

[root@VM_Main-os ~]# cat /home/oracle/run/startDG.sh
#!/bin/sh

export ORACLE_HOME=/home/oracle/product/11.2.3/dbhome_1
/home/oracle/product/11.2.3/dbhome_1/bin/lsnrctl start

export ORACLE_SID=db
/home/oracle/product/11.2.3/dbhome_1/bin/sqlplus '/as sysdba' <<EOF
startup;
archive log list;
exit;
EOF

[root@VM_Slave-os ~]# cat /home/oracle/run/startDG.sh
#!/bin/sh

export ORACLE_HOME=/home/oracle/product/11.2.3/dbhome_1
/home/oracle/product/11.2.3/dbhome_1/bin/lsnrctl start

export ORACLE_SID=db
/home/oracle/product/11.2.3/dbhome_1/bin/sqlplus '/as sysdba' <<EOF
#startup;
#archive log list;
startup mount;
alter database open read only;
alter database recover managed standby database using current logfile disconnect;
exit;
EOF

主备切换：

1. 查看有几个实例：

[oracle@VM_Main-os ~]$ cat $ORACLE_HOME/network/admin/

2. 进入主备 sqlplus 环境

[oracle@VM_Main-os ~]$ sqlplus / as sysdba

set linesize 1000;
set pagesize 1000;

3. 查看主备最后的 Current log sequence 号是否一致

SQL> archive log list;

4. 查看數據庫当前我角色， 切换目标为互换
   a如果switchover_status为TO_PRIMARY 说明标记恢复可以直接转换为primary库,即当前行待转成主库
   b 如果switchover_status为SESSION ACTIVE 就应该断开活动会话
   C 如果switchover_status为NOT ALLOWED 说明切换标记还没收到，此时不能执行转换
   OPEN_MODE ： 主库为 Read Write,备库为 Read only

	M_SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
				NAME      OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
				--------- -------------------- -------------------- ---------------- --------------------
				db     READ WRITE           MAXIMUM PERFORMANCE  PRIMARY          TO STANDBY
		
	S_SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
				NAME      OPEN_MODE            		PROTECTION_MODE      	DATABASE_ROLE    	 SWITCHOVER_STATUS
				--------- -------------------- -------------------- ---------------- --------------------
				db     READ ONLY WITH APPLY 	MAXIMUM PERFORMANCE   PHYSICAL STANDBY	  NOT ALLOWED

5. 查看是否有未同步的 sequence ,如果备库有值为No，则提示有未应用的Log，此时无法切换

	S_SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
				SEQUENCE# APP  如果有NO 则提示有未应用的Log，此时无法切换
			--------- ---
				8 YES
				9 YES
			   10 YES
			   11 IN-MEMORY

7. 查看并比对主/备库的DB Link， 需要在主/备库同样

	M/S_SQL> select * from dba_db_links;

8. 查看备库与主库是否都有 logfile ，主意文件目录，当主库
   如果备库没有 logfile 则需要先创建

	S_SQL>alter database add logfile thread 1 group 4  size 52428800
	S_SQL>ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 5 '/data/pcasolap/standby_redo05.log' size 209715200	
	S_SQL>alter database drop logfile group 5
		   
	M_SQL> show parameter standby
				NAME                                 TYPE        VALUE
				------------------------------------ ----------- ------------------------------
				standby_archive_dest                 string      ?/dbs/arch
				standby_file_management              string      auto
	M_SQL> alter system set standby_file_management='manual';
	S_SQL> select group#,type, member from v$logfile;
			ONLINE  /data/oradata/db_FIXO/onlinelog/o1_mf_1_jp2j1hhj_.log    ## 从o1_mf_1_jp2j1hhj_.log 来看是使用OMM自动管理的，扩充文件创建时不需要指定目录
	M_SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 5  size 209715200	
	M_SQL> alter system set standby_file_management='manual'; 	

9. 主库：切换当前Primary DB 为Standby DB

		MSQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;    -- 等，此处完成已完成切换;

	重啟一庫到mount狀態：
		MSQL>shutdown immediate;
		MSQL>startup mount;
		MSQL> select database_role,switchover_status from v$database;
		MSQL>  alter database open read only;

10. S 退出自动recover模式

		SSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE Cancel;

11. S切换当前Standby DB为Primary DB

		SSQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; -- WAIT;
		SSQL> alter database open;
		SSQL> alter system switch logfile;
		
		MSQL> alter database recover managed standby database using current logfile disconnect from session;

以上完成主备切换

验证：

SQL> create table testc(id number(10),name varchar2(20));  
  
Table created.  
  
SQL> insert into testc values(1,'testc');  
  
1 row created.  
  
SQL> insert into testc values(2,'testc');  
  
1 row created.  
  
SQL> commit;  
  
Commit complete.  
  
SQL> alter system switch logfile;   
SQL> archive log list;