一、 环境规划

--新增提示符

vi  $ORACLE_HOME/sqlplus/admin/glogin.sql

define _editor=vi

set sqlp "_user'@'_connect_identifier>"

二、 dg搭建

（一）enable Force Logging

SQL> select force_logging from v$database;

SQL> alter database force logging;

SQL> select force_logging from v$database;

（二）Enable Archivelog Mode

--关闭节点2

shutdown immediate;

--节点1开归档：

alter system set log_archive_dest_1='location=+DATA';

shutdown immediate;

startup mount

alter database archivelog;

alter database open;

--打开节点2：

startup

（三）Create Standby Redo Logs

1、调整当前日志组

select group#,THREAD#,bytes/1024/1024 as M from v$log;

    GROUP#    THREAD#          M

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

         1          1         50

         2          1         50

         3          2         50

         4          2         50

--默认是50M，删除，创建成1g的4组

alter database add logfile thread 1 group 11 '+DATA' size 1G;

alter database add logfile thread 1 group 12 '+DATA' size 1G;

alter database add logfile thread 1 group 13 '+DATA' size 1G;

alter database add logfile thread 1 group 14 '+DATA' size 1G;

alter database add logfile thread 2 group 15 '+DATA' size 1G;

alter database add logfile thread 2 group 16 '+DATA' size 1G;

alter database add logfile thread 2 group 17 '+DATA' size 1G;

alter database add logfile thread 2 group 18 '+DATA' size 1G;

alter system switch logfile;

alter database drop logfile group 1;

alter database drop logfile group 2;

alter database drop logfile group 3;

alter database drop logfile group 4;

2、新增standby log

--有一个推荐的公式可以做参考：(每线程的日志组数+1)*最大线程数(节点数)

--(4+1)*2=10  需要新建10个standby log（每个节点5个）

alter database add standby logfile thread 1 group 21 '+DATA' size 1g;

alter database add standby logfile thread 1 group 22 '+DATA' size 1g;

alter database add standby logfile thread 1 group 23 '+DATA' size 1g;

alter database add standby logfile thread 1 group 24 '+DATA' size 1g;

alter database add standby logfile thread 1 group 25 '+DATA' size 1g;

alter database add standby logfile thread 2 group 26 '+DATA' size 1g;

alter database add standby logfile thread 2 group 27 '+DATA' size 1g;

alter database add standby logfile thread 2 group 28 '+DATA' size 1g;

alter database add standby logfile thread 2 group 29 '+DATA' size 1g;

alter database add standby logfile thread 2 group 30 '+DATA' size 1g;

3、查看standby log

select group#,THREAD#, bytes/1024/1024 from v$standby_log;

（四）dg参数修改

--主库RAC的原有参数不变，添加下列参数：

--LOG_ARCHIVE_CONFIG后面的是服务名，不是tnsnames.ora的名字

--主库

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orclyl,orclyl_dg)';

alter system set LOG_ARCHIVE_DEST_1='LOCATION=+ARCH  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclyl';

alter system set LOG_ARCHIVE_DEST_2='SERVICE=tns_orclyl_dg compression=enable  LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclyl_dg';

alter system set  fal_server='tns_orclyl_dg';

alter system set fal_client='tns_orclyl';

alter system set standby_file_management=AUTO;

--以下参数设置后可以不立即重启（不生效只会导致自动创建数据文件失败，其余无影响）

alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

（五）修改tnsnames.ora文件

--oralce用户

--主备节点都加

vi $ORACLE_HOME/network/admin/tnsnames.ora

--新增

tns_orclyl =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.0.203)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.0.204)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orclyl)

    )

  )

tns_orclyl_dg =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.195.11.203)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.195.11.204)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orclyl_dg)

    )

  )

（六）配置静态监听

1>主库

--grid 用户下修改listener.ora文件，新增

--ORACLE_HOME 必须是oracle用户的，否则静态注册后用sys登录提示密码错误

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

    (GLOBAL_DBNAME = orcl)

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

    (SID_NAME = orcl1)

  )

)

srvctl stop  listener -n rac1

srvctl start  listener -n rac1

--节点2  grid 用户下修改listener.ora文件，新增

--ORACLE_HOME 必须是oracle用户的，否则静态注册后用sys登录提示密码错误

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

    (GLOBAL_DBNAME = orcl)

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

    (SID_NAME = orcl2)

  )

)

srvctl stop  listener -n rac2

srvctl start  listener -n rac2

2>备库

--节点1

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

    (GLOBAL_DBNAME = orclyl_dg)

    (ORACLE_HOME = /u01/app/oracle/product/19c/db_1)

    (SID_NAME = orclyl1)

  )

)

--节点2

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

    (GLOBAL_DBNAME = orclyl_dg)

    (ORACLE_HOME = /u01/app/oracle/product/19c/db_1)

    (SID_NAME = orclyl2)

  )

)

--以上都修改完一起重启监听

srvctl  stop listener

srvctl  start listener

（七）复制密码文件

--查看密码文件

[grid@rac1:/home/grid]$ srvctl config database -d orclyl

--把密码文件从asm里面复制出来

 asmcmd pwcopy +DATA/ORCLYL/PASSWORD/pwdorclyl.485.1166791353 /home/grid/orapworclyl1

--复制密码文件到备库oracle用户下的$ORACLE_HOME/dbs

--把密码文件恢复到asm中

--+DATA下面的子目录dgorcl如果不存在记得新建

--密码文件不允许直接放在+DATA/dgorcl目录下

su - grid

asmcmd pwcopy  /home/grid/orapworclyl1 +DATA/orclyl_dg/PASSWORD/pwdorclyl_dg

（八）复制并修改pfile文件

1>据spfile创建pfile

create pfile ='/home/oracle/standby.pfile' from spfile;

2>复制pfile到备库

scp standby.pfile 192.168.74.111:/home/oracle

3>根据实际环境修改pfile

cp standby.pfile  standby.pfile.old

vi standby.pfile

--1、把audit_file_dest路径的orcl替换成dgorcl

--2、把db_unique_name 修改成dgorcl

--3、control_files里路径的orcl修改成dgorcl

--4、修改dg参数,把原来的orcl1替换成dgorcl1,orcl2替换成dgorcl2

--修改后的参数如下

*.db_unique_name='orclyl_dg'

4>修改环境变量里的sid

vi .bash_profile

--更新SID

5>创建目录

--两个备节点都得创建目录

cat standby.pfile | grep  /u01/app

mkdir -p /u01/app/oracle/admin/orclyl_dg/adump

6>用pfile启动库

sqlplus / as sysdba

startup nomount  pfile='/home/oracle/standby.pfile'

create spfile='+DATA/orclyl_dg/spfileorclyl_dg.ora' from pfile='/home/oracle/standby.pfile';

shutdown immediate

--节点1

echo "SPFILE='+DATA/orclyl_dg/spfileorclyl_dg.ora'" > $ORACLE_HOME/dbs/initorclyl1.ora

--节点2

echo "SPFILE='+DATA/orclyl_dg/spfileorclyl_dg.ora'" > $ORACLE_HOME/dbs/initorclyl2.ora

7>备库修改DG参数

--备库

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orclyl_dg,orclyl)';

alter system set LOG_ARCHIVE_DEST_1='LOCATION=+ARCH  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclyl_dg';

alter system set  fal_server='tns_orclyl';

alter system set fal_client='tns_orclyl_dg';

alter system set LOG_ARCHIVE_DEST_2='SERVICE=tns_orclyl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclyl';

alter system set standby_file_management=AUTO;

--以下参数设置后可以不立即重启（不生效只会导致自动创建数据文件失败，其余无影响）

alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

（九）注册数据库到ASM

su - oracle

srvctl add database -d orclyl_dg -o $ORACLE_HOME -dbtype RAC -role PHYSICAL_STANDBY

srvctl add instance -d orclyl_dg -i orclyl1 -node zbhxzwdb01

srvctl add instance -d orclyl_dg -i orclyl2 -node zbhxzwdb02

srvctl modify database -d orclyl_dg -spfile '+DATA/orclyl_dg/spfileorclyl_dg.ora' -pwfile '+DATA/orclyl_dg/PASSWORD/pwdorclyl_dg' -diskgroup DATA,ARCH

srvctl config database -d orclyl_dg

（十）启动备库到nomount状态

--节点1操作

startup nomount

（十一）通过rman复制数据库

--测试

sqlplus "sys/oracle@ tns_orclyl as sysdba"

sqlplus "sys/oracle@ tns_orclyl_dg as sysdba"

--主库删除失效归档

rman target /

crosscheck archivelog all;

delete noprompt expired archivelog all;

1>方法一：在线复制

--建议备节点执行，这样对生产库的影响最小

cat  restore.sh

rman target  sys/oracle@tns_orclyl auxiliary  sys/oracle@tns_orclyl_dg

 <<eof

run{

  allocate channel prmy11 type disk;

  allocate channel prmy12 type disk;

  allocate channel prmy13 type disk;

  allocate channel prmy14 type disk;

  allocate auxiliary channel prmy21 type disk;

  allocate auxiliary channel prmy22 type disk;

  allocate auxiliary channel prmy23 type disk;

  allocate auxiliary channel prmy24 type disk;

  duplicate target database for standby  from active database nofilenamecheck;

  release channel prmy11;

  release channel prmy12;

  release channel prmy13;

  release channel prmy14;

  release channel prmy21;

  release channel prmy22;

  release channel prmy23;

  release channel prmy24;

}

eof

nohup ./restore.sh &

2>方法二：通过备份复制

--测试

sqlplus "sys/oracle@ tns_orclyl as sysdba"

sqlplus "sys/oracle@ tns_orclyl_dg as sysdba"

--1、先做个全备份

rman target /

run {

  allocate channel c0 type disk;

  allocate channel c1 type disk;

  backup as compressed backupset database format '/rmanbak/db_%d_Full_s.%s_p.%p_%T.bak';

  backup as compressed backupset format 'd:\rmanbak\L1_%d_ARC_s.%s_p.%p_%T.bak'  archivelog all;

  release channel c0;

  release channel c1;

}

--2、主库操作后连同之前的全备份复制到备库相同目录

alter database create standby controlfile as '/home/oracle/standby_orclyl.ctl';

rman target /

restore  controlfile  from '/home/oracle/standby_orclyl.ctl';

sql 'alter database mount';

--3、登录备库做恢复

#!/bin/sh

rman target /   <<eof

run {

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

allocate channel c5 type disk;

allocate channel c6 type disk;

allocate channel c7 type disk;

allocate channel c8 type disk;

allocate channel c9 type disk;

allocate channel c10 type disk;

allocate channel c11 type disk;

allocate channel c12 type disk;

allocate channel c13 type disk;

allocate channel c14 type disk;

allocate channel c15 type disk;

allocate channel c16 type disk;

allocate channel c17 type disk;

allocate channel c18 type disk;

allocate channel c19 type disk;

allocate channel c20 type disk;

set newname for database to '+DATA/ORCLYL_DG/DATAFILE/%b';

restore database;

switch datafile all;

switch tempfile  all;

recover database;

}

eof

--如果备份没备份归档，恢复这里可以不做recover database操作

nohup ./restore.sh &

sqlplus   /    as  sysdba

shutdown immediate;

startup nomount

alter  database  mount  standby database;

rman target  sys/oracle@tns_orclyl auxiliary  sys/oracle@tns_orclyl_dg

（十二）同步备库

--开启异步同步（归档）

alter database recover managed standby database disconnect from session;

--关闭MRP进程

recover managed standby database cancel;

alter database open;

--开启实时应用（通过在线redolog同步）

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

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

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

（十三）dg同步检查

--查看主备库状态

select NAME, OPEN_MODE, PROTECTION_MODE, DATABASE_ROLE, SWITCHOVER_STATUS

  from v$database;

--对比主备库执行结果是否一致

SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST

  FROM V$ARCHIVED_LOG;

--主备库都执行，这个结果只能说明归档有没有传送过去，不能说明是否应用

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

--备库执行

--name 如果指向的不是本端可以忽略

col  name for a20

select thread#, sequence#, name,first_time, next_time, applied

  from v$archived_log

 where applied = 'NO';

--查询同步延迟-主备节点操作

set pagesize 20;

column name format a13;

column value format a20;

column unit format a30;

column TIME_COMPUTED format a30;

select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');

（十四）备份恢复中归档保留策略

--强制归档检查

alter system set "_log_deletion_policy"=ALL scope=both sid='*';

--如果主库是最大性能或者最大可用性，即使这里指定了这个参数oracle也不强制检查，所以需要依赖上一个隐含参数

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO  '+DATA/xx/snapcf.f';

三、 主备库switchover切换

（一）检查

--查看主备库状态

select NAME, OPEN_MODE, PROTECTION_MODE, DATABASE_ROLE, SWITCHOVER_STATUS

  from v$database;

--对比主备库执行结果是否一致

SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST

  FROM V$ARCHIVED_LOG;

--主备库都执行 

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

--备库执行

col name for a10

select thread#, sequence#,name, first_time, next_time, applied

  from v$archived_log

 where applied = 'NO';

（二）切换

--主备库都关闭一个节点（不关闭数据库也会自动关闭）

--切换主库到备库

alter database commit to switchover to physical standby;

--or:

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

startup mount

--确保新主库open新备库能open

alter database recover managed standby database using current logfile disconnect;

--切换备库到主库,

alter database recover managed standby database cancel;

alter database commit to switchover to primary;

--or:

alter database commit to switchover to primary with session shutdown;

alter database open;

四、 主备库failover切换

--取消日志引用

alter databaserecover managed standby database cancel;

--or  如果正常取消不了就强制取消

alter database recover managed standby database finish;

--激活成主库

alter database activate physical standby database;

--打开

alter database open;

五、 DG故障检测用到的语句

--主库执行

--如果DG配置有问题，这里会显示INVALID，并且error会提示具体的错误原因且alert也会体现。

col dest_name for a20

select dest_name,status,process,error,transmit_mode  from v$archive_dest where target='STANDBY';

--查看最近的归档日志文件：

select max(sequence#),thread# from v$archived_log group by  thread#;

--查询同步延迟-主备节点操作

set pagesize 20;

column name format a13;

column value format a20;

column unit format a30;

column TIME_COMPUTED format a30;

select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');

--查看主备库状态

select NAME, OPEN_MODE, PROTECTION_MODE, DATABASE_ROLE, SWITCHOVER_STATUS

  from v$database;

--查看组件状态（主备都可以执行）

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