Oracle数据库DG切换

[TOC]

Oracle数据库切换

• 检测是否有挂载磁盘

主库操作

• 关闭主库监听

su - oracle
lsnrctl stop（只在主库操作）

• 在主库端检查数据库可切换状态

sqlplus / as sysdba
SQL> select switchover_status from v$database;

如果SWITCHOVER_STATUS 的值为TO STANDBY 表示可以正常切换.

SQL>alter database commit to switchover to physical standby;

Database altered.

如果SWITCHOVER_STATUS 的值为SESSIONS ACTIVE：

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

Database altered.

tailf $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log 查看日志

• 修改新备库ip（原主库）如原主库服务器宕机可不在此操作

ping 10.100.0.201

sed -i 's/10.100.0.9/10.100.0.201/g' /etc/sysconfig/network-scripts/ifcfg-bond0
cat /etc/sysconfig/network-scripts/ifcfg-bond0

/etc/init.d/network restart

备库操作

• 将目标备库转换为主库

如果SWITCHOVER_STATUS 的值为TO PRIMARY 则:

SQL> alter database commit to switchover to primary;

Database altered.
如果SWITCHOVER_STATUS 的值为SESSIONS ACTIVE 则:

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

Database altered.

SQL> select FILE_NAME,TABLESPACE_NAME,status from dba_temp_files;

tailf $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log 查看日志

• 新主库操作（原备库） 更换ip 此ip 为业务ip

sed -i 's/10.100.0.18/10.100.0.9/g' /etc/sysconfig/network-scripts/ifcfg-bond0

sed -i 's/10.100.0.18/10.100.0.9/g' $ORACLE_HOME/network/admin/listener.ora
cat /etc/sysconfig/network-scripts/ifcfg-bond0

/etc/init.d/network restart

• 重启新主库监听（原备库）

lsnrctl stop
lsntctl start

Sql> shutdown immediate;
Sql> startup;

lsnrctl status 如果一直未注册到监听里面，需手动注册一下
Sql>ALTER SYSTEM REGISTER;

恢复DG同步状态

• 修改新主备库tnsname.ora文件 （此文件主备库完全一样）

cat << EOF > $ORACLE_HOME/network/admin/tnsnames.ora.bak 
DB_WENDING =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.18)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = WENDING)
    )
  )
DB_PHYSTDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.9)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PHYSTDBY)
    )
  )
EOF

• 修改新备库监听文件（原主库）

cat << EOF > $ORACLE_HOME/network/admin/listener.ora 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /home/oracle/oracle10g/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.9)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )
EOF

• 查看主备的/etc/hosts配置要正确

• 动新备库库监听 （原主库）

lsnrctl start

重启新备库

Sql>shutdown immediate;

Sql> startup;

• 新备库执行同步语句

SQL> alter database recover managed standby database disconnect from session; 

• 备库查看主备文件同步情况

select sequence#,dest_id,first_time,next_time,archived,applied from v$archived_log  order by   sequence#;

• 主库

set linesize 160 pagesize 999
col destination for a30;
col error for a60;
select status,destination,error from v$archive_dest;

• 主备库都检查

set linesize 160 pagesize 999
col destination for a30;
col error for a60;
select dbid,database_role from v$database;
select max(sequence#) from v$archived_log; 
select max(sequence#) from v$archived_log where applied='YES';

• 备库

select process,status,sequence#from v$managed_standby;

检查新主库脚本

• 检查rman 备份脚本，以及磁盘挂载目录
• 检查rman备份监控脚本
  备份放在哪台服务器上，监控脚本就放在哪台服务器上。数据量比较大的备份最好放在备库上备份。监控脚本不要放在存储上。
• check_rman_backup.sh

status=`sqlplus -silent  "/as sysdba " <<eof
set feedback off
set verify off
set heading off
set echo off
set pagesize 0
SELECT /*+ rule */ DECODE (
             TRUNC (SYSDATE - start_time),
             0, DECODE (
                   status,
                    'COMPLETED',  '0',
                    'COMPLETED WITH WARNINGS ', '1',
                    '2'),
              '2')
          backup_status
  FROM v\\$rman_backup_job_details
 WHERE start_time = (SELECT MAX(start_time) FROM v\\$rman_backup_job_details);
exit;
eof`
output=`sqlplus -silent  "/as sysdba " <<eof
set feedback off
set verify off
set heading off
set echo off
set pagesize 0
SELECT /*+ rule */ DECODE (
          TRUNC (SYSDATE - start_time),
          0, DECODE (status,
                      'COMPLETED',  'ccod：备份成功!',
                      'COMPLETED WITH WARNINGS',  'ccod：备份有警告，请检查!',
                      'ccod：备份失败，请检查!'),
           'ccod：无备份!')
          backup_status
  FROM v\\$rman_backup_job_details
 WHERE start_time = (SELECT MAX (start_time) FROM v\\$rman_backup_job_details);
exit;
eof`
hostip=$IP
checkname=Rman_Backup_$SID
if [ -z "$status" ]||[ "$status" != 0 ]
then
/bin/logger -p local0.crit "result=ERROR described:$status $output  ccod： 请及时RMAN检查备份情况"
fi

• 10.3检查dg 健康状态脚本
  • 主库是oracle_wending_check.sh

#!/bin/bash

. ~/.bash_profile

##指标1:alert日志文件告警##
b=`cat /home/oracle/b.log`
c=`grep -c ORA- /home/oracle/oracle10g/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log`
if [[ $c -gt $b ]];
then
echo "OraAlarm=false"
else
echo "OraAlarm=true"
fi
##将最新的值更新到b.log文件，用于下次比对##
echo $c >/home/oracle/b.log


##指标2:dg同步监控##
var=`sqlplus -s / as sysdba <<EOF
set trimspool on
set linesize 2000
set pagesize 0
set newpage 1
set heading off
set feedback off 
set term on
select (select max(SEQUENCE#) from v\\$archived_log where applied='NO')-(select max(SEQUENCE#) from v\\$archived_log where applied='YES') from dual;
EOF`

if [ -z "$var" ]||[ "$var" -gt 1 ]
then
echo "OracleDG=false"
elif [ "$var" -le 1 ]
then
echo "OracleDG=true"
fi

##指标3:监控数据库状态##
STA=`sqlplus -s / as sysdba <<EOF
set trimspool on
set linesize 2000
set pagesize 0
set newpage 1
set heading off
set feedback off 
set term on
select status from v\\$instance;
EOF`
##传输数据库状态至网管监控##
echo "OracleSID=$STA"

##指标4:监控连接数使用情况##
##获取当前会话数##
CONS=`sqlplus -s / as sysdba <<EOF
set trimspool on
set linesize 2000
set pagesize 0
set newpage 1
set heading off
set feedback off 
set term on
select count(*) from V\\$SESSION;
EOF`
##获取参数配置情况##
PROS=`sqlplus -s / as sysdba <<EOF
set trimspool on
set linesize 2000
set pagesize 0
set newpage 1
set heading off
set feedback off 
set term on
select value from v\\$parameter where name = 'processes';
EOF`
##获取连接数使用情况##
num=`expr $CONS \* 100 / $PROS`
##传输连接数使用情况至网管监控##
echo "OracleCON=$num"

• 备库是oracle_phystdby_check.sh

#!/bin/bash
. ~/bash_profile
##指标1:alert日志文件告警##
b=`cat /home/oracle/b.log`
c=`grep -c ORA- /home/oracle/oracle10g/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log`
if [[ $c -gt $b ]];
then
echo "OraAlarm = false"
else
echo "OraAlarm = true"
fi
##将最新的值更新到b.log文件，用于下次比对##
echo $c >/home/oracle/b.log

• 有需要布置check_dg.sh

#!/bin/bash
. /home/oracle/.bash_profile
##指标1:alert日志文件告警##
b=`cat /home/oracle/b.log`
c=`grep -c ORA- /home/oracle/oracle10g/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log`
if [[ $c -gt $b ]];
then
echo "OraAlarm=false"
/bin/logger -p local0.crit "OraAlarm=false described:OraAlarm=false  ccod： 请及时检查alert日志情况"
else
echo "OraAlarm=true"
fi
##将最新的值更新到b.log文件，用于下次比对##
echo $c >/home/oracle/b.log

##指标2:dg同步监控##
var=`$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF
set trimspool on
set linesize 2000
set pagesize 0
set newpage 1
set heading off
set feedback off 
set term on
select (select max(SEQUENCE#) from v\\$archived_log where applied='NO')-(select max(SEQUENCE#) from v\\$archived_log where applied='YES') from dual;
EOF`

if [ -z "$var" ]||[ "$var" -gt 1 ]
then
echo "OracleDG=false"
/bin/logger -p local0.crit "DG=ERROR described:OracleDG=false  ccod： 请及时DG同步情况"
elif [ "$var" -le 1 ]
then
echo "OracleDG=true"
fi
$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF
set trimspool on
set linesize 2000
set pagesize 0
set newpage 1
set heading off
set feedback off 
set term on
spool /home/oracle/oracle_gap.log
select * from v\$archive_gap;
exit
EOF
Gap=`cat /home/oracle/oracle_gap.log|wc -l`
if [ "$Gap" != 0 ];
then
echo "OracleDG=false"
/bin/logger -p local0.crit "DG=ERROR described:OracleDG=false  ccod:存在gap， 请及时DG同步情况"
else
echo "OracleDG=true"
fi
##指标3:监控数据库状态##
sqlplus -s / as sysdba <<EOF
set trimspool on
set linesize 2000
set pagesize 0
set newpage 1
set heading off
set feedback off 
set term on
spool /home/oracle/sta.log
select status from v\$instance;
EOF
STA=`cat /home/oracle/sta.log`
##传输数据库状态至网管监控##
echo "OracleSID=$STA"
if [ "$STA" = "OPEN" ]
then
echo "OracleSID=true"
else
echo "OracleSID=false"
/bin/logger -p local0.crit "ERROR described:OracleSID=false  ccod:数据库状态不是open"
fi


##指标4:监控连接数使用情况##
##获取当前会话数##
sqlplus -s / as sysdba <<EOF
set trimspool on
set linesize 2000
set pagesize 0
set newpage 1
set heading off
set feedback off 
set term on
spool /home/oracle/con.log
select count(*) from V\$SESSION;
EOF
CONS=`cat /home/oracle/con.log`
##获取参数配置情况##
sqlplus -s / as sysdba <<EOF
set trimspool on
set linesize 2000
set pagesize 0
set newpage 1
set heading off
set feedback off 
set term on
spool /home/oracle/pros.log
select value from V\$parameter where name = 'processes';
EOF
PROS=`cat /home/oracle/pros.log`
##获取连接数使用情况##
num=`expr $CONS \* 100 / $PROS`
##传输连接数使用情况至网管监控##
echo "OracleCON=$num"
if [ "$num" -gt 60 ]
then
echo "OracleCON=false"
/bin/logger -p local0.crit "ERROR described:OracleCON=false  ccod:数据库连接数超过阈值"
else
echo "OracleCON=true"
fi

• 10.4检查清理归档脚本
• 主库del_appl_standy_arc_wending.sh

#!/bin/bash
#########################################################################
#         This shell is for primary and standby database                #
#         to rm applied archivelog that before some day ago.            #
#                                                                       #
#       You can define "some day" in variables ${day_before}            #
#            This shell can be put in crontab for auto run              #
#                                                                       #
#            2008-01-18   writen by www.oracleblog.cn                   #
#########################################################################

## load profile file
. /home/oracle/.bash_profile

## Path Define
main_path=$ORACLE_BASE/del_appl_arc
bin_path=${main_path}/bin
log_path=${main_path}/log
arc_path=$ORACLE_BASE/arch1/WENDING

cd ${bin_path}

## Initial script
touch app_arc_name.sh
chmod +x app_arc_name.sh

## rm applied archivelog that before ${day_before} day ago
day_before=7

## Db info
dbuser=system
dbpwd=oracle
dbsid=db_WENDING

### Create shell for rm applied archive that before some day ago
sqlplus -s "/ as sysdba"<<EOF>/dev/null
set feedback off
set pages 0
set head off
set timing off
set echo off
spool app_arc_name.tmp
select 'rm -f '||name from v\$archived_log 
where DEST_ID=1 and name like '%.dbf' 
and SEQUENCE#<(select max(SEQUENCE#) from v\$archived_log where applied='YES')
and COMPLETION_TIME<=sysdate-${day_before};
spool
exit
EOF
## clear delete expired archivelog all;

## Exec the shell in background mode
cat app_arc_name.tmp |grep -v spooling>app_arc_name.sh
./app_arc_name.sh
chmod -x app_arc_name.sh
mv app_arc_name.sh rm_appl_arc_`date +"%Y%m%d%H%M"`.log
mv rm_appl_arc*.log ${log_path}
rm app_arc_name.tmp 


##clear alter log
export BACKUP_DATE=`date +%y%m%d`
cd $ORACLE_BASE/admin/$ORACLE_SID/bdump

if  [ -f alert_$ORACLE_SID.log ];  then
cat alert_$ORACLE_SID.log >> alert_$ORACLE_SID.$BACKUP_DATE
cat /dev/null > alert_$ORACLE_SID.log
fi

echo 0 > /home/oracle/b.log

• 备库del_appl_standy_arc_phystdby.sh

#!/bin/sh
#########################################################################
#         This shell is for primary and standby database                #
#         to rm applied archivelog that before some day ago.            #
#                                                                       #
#       You can define "some day" in variables ${day_before}            #
#            This shell can be put in crontab for auto run              #
#                                                                       #
#            2008-01-18   writen by www.oracleblog.cn                   #
#########################################################################

## load profile file
. /home/oracle/.bash_profile

## Path Define
main_path=$ORACLE_BASE/del_appl_arc
bin_path=${main_path}/bin
log_path=${main_path}/log
arc_path=$ORACLE_BASE/arch1/PHYSTDBY

cd ${bin_path}

## Initial script
touch app_arc_name.sh
chmod +x app_arc_name.sh

## rm applied archivelog that before ${day_before} day ago
day_before=7


### Create shell for rm applied archive that before some day ago
sqlplus -s "/ as sysdba"<<EOF>/dev/null
set feedback off
set pages 0
set head off
set timing off
set echo off
spool app_arc_name.tmp
select 'rm -f '||name from v\$archived_log 
where DEST_ID=1 and name like '%.dbf' 
and SEQUENCE#<(select max(SEQUENCE#) from v\$archived_log where applied='YES')
and COMPLETION_TIME<=sysdate-${day_before};
spool
exit
EOF
## clear delete expired archivelog all;



## Exec the shell in background mode
cat app_arc_name.tmp |grep -v spooling>app_arc_name.sh
./app_arc_name.sh
chmod -x app_arc_name.sh
mv app_arc_name.sh rm_appl_arc_`date +"%Y%m%d%H%M"`.log

mv rm_appl_arc*.log ${log_path}
rm app_arc_name.tmp 

##clear alter log
export BACKUP_DATE=`date +%y%m%d`
cd $ORACLE_BASE/admin/$ORACLE_SID/bdump

if  [ -f alert_$ORACLE_SID.log ];  then
cat alert_$ORACLE_SID.log >> alert_$ORACLE_SID.$BACKUP_DATE
cat /dev/null > alert_$ORACLE_SID.log
fi

echo 0 >/home/oracle/b.log

新主库启动后如有以下错误，请参考解决方法

• ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type Fri Apr 20 21:17:12 2018

解决方法： 
SQL> show parameter undo  
NAME                                 TYPE        VALUE  
------------------------------------ ----------- ------------------------------  
undo_management                      string      AUTO  
undo_retention                       integer     900  
undo_tablespace                      string      UNDOTBS1 

 SQL> select name from v$tablespace where name like '%UNDO%'; 
NAME  
------------------------------  
UNDOTBS3  

sql > create pfile from spfile;

cd $ORACLE_HOME/dbs

vi init$ORACLE_SID.ora
修改undo_tablespace=UNDOTBS3

然后startup mount pfile=$ORACLE_HOME/dbs/init$ORACLE_SID.ora  (此处请写绝对路径）

sql > create spfile from pfile;

sql> shutdown immediate;

sql> startup

• 数据库启动后，如应用报如下错误 ORA-01187 ORA-01110，或避免报次错，请预先检查一下临时表空间状态

SQL> select TABLESPACE_NAME,FILE_NAME,STATUS from dba_temp_files;

TABLESPACE_NAME            FILE_NAME              STATUS
------------------------------ ------------------------------ ---------
TEMP                   /oradata/ygdb/temp01.dbf       AVAILABLE

如果不是AVAILABLE，请执行以下语句
alter tablespace TEMP add tempfile '/oradata/ygdb/temp02.dbf' size 100m autoextend on;
alter tablespace TEMP drop tempfile '/oradata/ygdb/temp01.dbf';