第十二章：DataGuard
点击数：1796发布日期：2020-04-19 15:11:02 来源：oracle实战与提高，清华大学出版社
第十二章：DataGuard
前言：
DataGuard是Oracle数据库最常使用的容灾应急方式，它不像GoldenGate那样还需要单独的购买软件许可，10g DataGuard已包含在数据库企业版当中，不需要再购买license。11g 是active DataGuard，目标端可以查询。
如果说RAC的主要作用是防止主机故障，防止主机损坏；那么DataGuard的主要作用就是防止存储损坏。它可以轻易的实时的将数据库复制到另外一个地方，可以同机房，如果网络带宽允许，也可以同城或者异地复制。
如果主库发生故障，备库可以实现快速的接管，接管的过程可以实现分钟级的接管，甚至更短的时间便可以实现接管主库。
DataGuard可以实现一对多，一个主库，多个备库，最多可以9个备库，一般情况下，有一个或两个备库也就够用了。也可以级联容灾，从A到B，然后再从B到C。配置方式灵活。
DataGuard配置分类
主库primary，即生产库，单机或多节点RAC都可以。
备库standby，即容灾库，单机或多节点都可以，但接收应用日志的节点只有一个。
逻辑standby，将日志解析成SQL语句，然后在备库上执行这些SQL语句。逻辑standby有些数据类型不支持，如bfile 、encrypted columns、rowid, urowid、XMLType、对象类型、varrays、嵌套表、自定义类型；还有的DML语句不被支持或存储类型不被支持。优势就是在9i,10g的时候，可以将数据库打开，做为查询库来使用。11g，物理standby也可以打开查询了，所以逻辑standby到了11g，竞争力就小了。
物理standby，将接收的归档日志恢复到备库上，备库和生产库完全一样，是主库的物理恢复，和RMAN备份恢复实质一样。是我们最常用的容灾模式。本书主要讨论物理standby.
DataGuard三种保护模式：
1、 最大保护模式maximum protection
所有事务提交前不仅被写到本地的online redo log中，同时还要提交到standby的redo log中，并确认其中一个standby可用，最后才会在primary上提交。
此种保护模式，因为备库的故障会影响主库的生产运营，所以几乎没人使用。
2、 最高性能maximum performance
事务可以随时提交，当前primary的redo也要至少写入一个standby数据库，但它可以是不同步的。这是默认的保护模式。
3、 最高可用性maximum availability
要求所有事务在提交前必须保障redo数据至少在一个standby数据库可用，不过与之不同的是，如果出现故障导入无法同时写入standby数据库redo log，primary数据库并不会shutdown，而是自动转为最高性能模式，等standby数据库恢复正常之后，它又会再自动转换成最高可用性模式
SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE
------------------------------------------------ ------------------------------------------------------------
PROTECTION_LEVEL
------------------------------------------------------------
PRIMARY MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
12.1配置一个最常用的物理DataGuard。
在我们的样例里面，主库的名字叫：yingshudb1，备库的唯一名叫：yingshudg1
12.1.1将主库改为归档模式
[root@yingshu /]# su - oracle
[oracle@yingshu ~]$ ss
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 14 08:59:08 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> alter system set log_archive_dest_1='location=/arch1' scope=spfile;
System altered.
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 810106880 bytes
Fixed Size 2257472 bytes
Variable Size 381685184 bytes
Database Buffers 419430400 bytes
Redo Buffers 6733824 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter system archive log current;
System altered.
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch1
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
注释：[oracle@yingshu ~]$ ss
我们在环境变量里设置了别名。以后就省去了每次再去敲sqlplus "/as sysdba".
[oracle@yingshu ~]$ pwd
/home/oracle
[oracle@yingshu ~]$ cat .bash_profile
... ...
export ORACLE_BASE=/u01/app/oracle/
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=yingshudb1
alias ss="sqlplus '/as sysdba'"
经过上面的步聚，我们的归档模式就设置好了。
12.1.2将主库改为强制归档
SQL> alter database force logging;
Database altered.
SQL>
SQL> select force_logging from v$database;
FORCE_LOG
---------
YES
12.1.3配置主库的tnsnames.ora
[oracle@yingshu admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
YINGSDG1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.20)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = yingsdg1)
)
)
YINGSDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = yingsdb1)
)
)
可以用netca配置，也可以手工把连接选项写进tnsnames.ora，我们要用YINGSDG1这个别名连接到远程数据库，将日志写到备库。
使用tnsping看看通不通。以下结果显示，我们的链路是通的。
[oracle@yingshu admin]$ tnsping yingsdg1
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2015 11:45:16
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.20)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = yingsdg1)))
OK (40 msec)
12.1.4配置主库的参数。
SQL> alter system set log_archive_config='dg_config=(yingsdb1,yingsdg1)' scope=both sid='*';
System altered.
SQL>
SQL> alter system set log_archive_dest_2='service=yingsdg1 lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=yingsdg1' scope=both sid='*';
System altered.
SQL> alter system set standby_file_management=auto scope=both sid='*';
System altered.
SQL> alter system set fal_server='yingsdg1' scope=both sid='*';
System altered.
SQL> alter system set fal_client='yingsdb1' scope=both sid='*';
System altered.
SQL> select * from v$dataguard_config;
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
yingsdb1
yingsdg1
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/yingsdb1','/u01/app/oracle/oradata/yingsdb1' scope=spfile sid='*';
System altered.
SQL>
SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/yingsdb1','/u01/app/oracle/oradata/yingsdb1' scope=spfile sid='*';
System altered.
注1：log_archive_dest_2='service=yingsdg1 lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=yingsdg1' scope=both sid='*';
这个参数非常重要，它是我们DataGuard实现最重要的参数。
service表示写到远程，如果是location一定是写到本地，如果是service一定是写到远程。
yingsdg1是连接字符串的别名，tnsnames.ora里面配置的别名，可以用tnsping测试是否通讯正常。
lgwr表示用什么写到远程，一般来说，推荐用Log Writer Process(LGWR)来写远程，当然也可以用archiver processes (ARCn)写到远程。也就是可以把lgwr改成arch。
sync，是否同步写。如果改成async就是非同步写。非同步写可以降低对主库的性能的影响，
affirm 等待归档日志文件和standby日志文件同步完成之后，主库log write才能继续。noaffirm主库的写进程，不等归档和备库日志写完便继续写主库日志。默认是noaffirm。
valid_for=(online_logfiles,primary_role)。括号中，逗号前面是“写什么”，逗号后面是“在什么角色的时候写”。我们这里显示的就是，当此数据库为主库的时候，写在线日志文件。
db_unique_name=yingsdg1 这里要用数据库唯一名，区别主备库，因为物理standby主库和备库的数据库名是一样的，需要用数据库唯一名区别主备库。
注2：db_file_name_convert参数，成对出现，只在此数据库为备库时起作用，逗号前面表示主库的数据文件所在的位置；逗号后面表示，传到备库之后，数据文件所放的位置。由此参数可以改变数据文件的位置。
12.1.5备份主库、备份控制文件。
注：如果CPU和存储允许，为加快备份速度，一定要多分几个通道。
[oracle@yingshu orabak]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jan 19 10:06:15 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: YINGSDB1 (DBID=4200332588)
RMAN> run {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> allocate channel c3 type disk;
5> backup database
6> include current controlfile format '/orabak/orclfull_%d_%T_%s';
7> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=1157 device type=DISK
allocated channel: c2
channel c2: SID=24 device type=DISK
allocated channel: c3
channel c3: SID=1159 device type=DISK
Starting backup at 19-JAN-15
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
... ...
piece handle=/orabak/orclfull_YINGSDB1_20150119_8 tag=TAG20150119T100656 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:15
Finished backup at 19-JAN-15
released channel: c1
released channel: c2
released channel: c3
备份控制文件：
控制文件一定是在全备之后备份，因为它里面要包含我们全备份时的信息。
RMAN> run {
2> allocate channel c1 type disk;
3> backup current controlfile for standby format '/orabak/control01.ctl.rman';
4> }
allocated channel: c1
channel c1: SID=1157 device type=DISK
Starting backup at 19-JAN-15
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including standby control file in backup set
channel c1: starting piece 1 at 19-JAN-15
channel c1: finished piece 1 at 19-JAN-15
piece handle=/orabak/control01.ctl.rman tag=TAG20150119T100832 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-JAN-15
released channel: c1
RMAN>
12.1.6将我们刚才的数据库全备份；备份的控制文件；参数文件；密码文件拷贝至备库。
[oracle@yingshu orabak]$ scp * oracle@192.168.1.20:/orabak/
Warning: Permanently added '192.168.1.20' (RSA) to the list of known hosts.
oracle@192.168.1.20's password:
control01.ctl.rman ... ...
[oracle@yingshu orabak]$
--建立对应的几个文件夹。使用oracle用户建立此文件夹
mkdir -p /u01/app/oracle/diag/rdbms/yingsdg1/yingsdg1/trace
mkdir -p /u01/app/oracle/diag/rdbms/yingsdg1/yingsdg1/cdump
mkdir -p /u01/app/oracle/oradata/yingsdb1
mkdir -p /u01/app/oracle/admin/yingsdg1/adump
mkdir -p /arch1
启动备库的listener
[oracle@yingshudg ~]$ lsnrctl
LSNRCTL> start
LSNRCTL> stat
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=yingshudg)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 19-JAN-2015 11:19:04
Uptime 0 days 0 hr. 0 min. 6 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/yingshudg/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yingshudg)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "yingsdb1" has 1 instance(s).
Instance "yingsdg1", status READY, has 1 handler(s) for this service...
The command completed successfully
修改备库的pfile
[oracle@yingshudg dbs]$ cat inityingsdg1.ora
yingsdb1.__oracle_base='/u01/app/oracle/'#ORACLE_BASE set from environment
*.audit_file_dest='/u01/app/oracle/admin/yingsdg1/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/yingsdb1/control01.ctl','/u01/app/oracle/oradata/yingsdb1/control02.ctl'
*.db_block_size=8192
*.db_cache_size=419430400
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/yingsdb1','/u01/app/oracle/oradata/yingsdb1'
*.db_name='yingsdb1'
*.diagnostic_dest='/u01/app/oracle'
*.fal_client='yingsdb1'
*.fal_server='yingsdg1'
*.java_pool_size=20971520
*.large_pool_size=52428800
*.log_archive_dest_1='location=/arch1'
*.log_file_name_convert='/u01/app/oracle/oradata/yingsdb1','/u01/app/oracle/oradata/yingsdb1'
*.open_cursors=300
*.pga_aggregate_target=268435456
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=304087040
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
########################
*.fal_server='yingsdb1'
*.fal_client='yingsdg1'
*.log_archive_dest_2='service=yingsdb1 LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=yingsdb1'
*.log_archive_config='dg_config=(yingsdg1,yingsdb1)'
*.db_unique_name='yingsdg1'
*.service_names='yingsdg1'
注1：####号以下部分，为配置dg重要参数，在备库的pfile中添加或修改正确。
注2：其中*.fal_server='yingsdb1'配置的是对方的别名。
*.fal_client='yingsdg1'中配置的是本机的别名。
这里配置的是数据库别名，也就是tnsnames.ora里面的连接字符串的别名。
注3：初始化参数LOG_ARCHIVE_CONFIG用于控制发送归档日志到远程位置、接收远程归档日志。*.log_archive_config='dg_config=(yingsdg1,yingsdb1)'。这里配置的是数据库唯一名。
注4：*.db_unique_name='yingsdg1'这里的数据库唯一名是用来和主库做区分的。和主库一定不能一样。
注5：*.db_name='yingsdb1'备库的数据库名和主库的数据库名必须一样。
SQL> startup nomount ;
ORACLE instance started.
Total System Global Area 810106880 bytes
Fixed Size 2257472 bytes
Variable Size 381685184 bytes
Database Buffers 419430400 bytes
Redo Buffers 6733824 bytes
恢复控制文件。
RMAN> restore controlfile from '/orabak/control01.ctl.rman';
Starting restore at 19-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/yingsdb1/control01.ctl
output file name=/u01/app/oracle/oradata/yingsdb1/control02.ctl
Finished restore at 19-JAN-15
恢复备库。
RMAN> alter database mount;
database mounted
RMAN> run {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> allocate channel c3 type disk;
5> restore database;
6> }
allocated channel: c1
channel c1: SID=13 device type=DISK
allocated channel: c2
channel c2: SID=1146 device type=DISK
allocated channel: c3
channel c3: SID=14 device type=DISK
Starting restore at 19-JAN-15
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00002 to /u01/app/oracle/oradata/yingsdb1/sysaux01.dbf
channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/yingsdb1/users01.dbf
channel c1: reading from backup piece /orabak/orclfull_YINGSDB1_20150119_6
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00001 to /u01/app/oracle/oradata/yingsdb1/system01.dbf
channel c2: restoring datafile 00003 to /u01/app/oracle/oradata/yingsdb1/undotbs01.dbf
channel c2: reading from backup piece /orabak/orclfull_YINGSDB1_20150119_5
channel c1: piece handle=/orabak/orclfull_YINGSDB1_20150119_6 tag=TAG20150119T100656
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:58
channel c2: piece handle=/orabak/orclfull_YINGSDB1_20150119_5 tag=TAG20150119T100656
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:01:29
Finished restore at 19-JAN-15
released channel: c1
released channel: c2
released channel: c3
启动数据库
startup;
SQL> alter database recover managed standby database disconnect from session;
Database altered.
验证DataGuard两边是否同步。
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
16
select max(sequence#) from v$log;
select max(sequence#) from v$log_history group by thread#;
DataGuard相关的几个重要视图。
select process,status,sequence# from v$managed_standby;
select sequence#,dest_id,archived,applied,deleted,status from v$archived_log order by sequence#
12.2、主备切换switch over
12.2.1准备工作
首先查询主库状态,确认可以做切换:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
TO STANDBY
12.2.2在备库上面确认所有的archive log 都已经apply:
select sequence#,first_time,next_time,applied from v$archived_log;
12.2.3在主库上switch logfile:
Alter system switch logfile;或
alter system archive log current;
(4)观察备库上已经 apply所有archive log:
select sequence#,first_time,next_time,applied from v$archived_log;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- --------------- --------------- ---------------------------
160 28-FEB-15 28-FEB-15 YES
161 28-FEB-15 28-FEB-15 YES
162 28-FEB-15 28-FEB-15 YES
163 28-FEB-15 28-FEB-15 YES
--切换:
(1)将主库转变为可切换状态primary主库上执行:
alter database commit to switchover to physical standby with session shutdown;
(2)standby备库
alter database commit to switchover to primary;
(3)primary主库
shutdown immediate;
(5)standby备库。
recover managed standby database cancel
shutdown immediate;
startup
(7)原来主库，新备库。
startup;
alter database recover managed standby database disconnect;
(8)新的primary
startup;
alter system switch logfile;多执行几次。
查看archive log是否自动传输
(9)原来的primary 新的standby
select sequence#,first_time,next_time,applied from v$archived_log;
select max(sequence#) from v$log;
12.3、FAILEOVER切换实验
生产库一旦发生故障，我们可能就需要应急切换。或我们平时在日常演练中，也需要进行切换实验，一备紧急时刻，我们的datagurad是完全可用的。下面我们就模拟生产库忽然损坏，而且不可恢复，由我们备库应急立刻顶住。整个切换过程也就几分种的时间，把损失降到最低，把停机时间影响降到最低。
在模拟生产down机之前，我们先看看两边日志的同步情况。
确认两边都是215。
SQL> select max(sequence#) from v$log_history group by thread#;
MAX(SEQUENCE#)
--------------
215
停止standby数据库。
connect / as sysdba
SQL> alter database recover managed standby database finish;
Database altered.
将standby数据库强制切换成主库。
SQL> alter database commit to switchover to primary;
Database altered.
关闭standby数据库。
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
启动切换后的数据库。
SQL> startup
ORACLE instance started.
Total System Global Area 810106880 bytes
Fixed Size 2257472 bytes
Variable Size 381685184 bytes
Database Buffers 419430400 bytes
Redo Buffers 6733824 bytes
Database mounted.
Database opened.
这时，我们的应急库，就变成主库了。可以上生产了。
我们原来的主库，修好之后，可以得新配置成DataGuard备库。配置的过程参见DataGuard配置。
下一篇：第十三章：Oracle GoldenGate实施参考
上一篇：第十一章：ASM
图文推荐
序言
2020-04-19 查看：2270
第十五章：Oracle 12c介绍
2020-04-19 查看：2207
第十四章：常用Oracle工具
2020-04-19 查看：1933
第十三章：Oracle Golde...
2020-04-19 查看：2199
第十二章：DataGuard
2020-04-19 查看：1796
