一，概念
data guard是高可用架构的一部分，他创建，维护，监控一个或多个备用数据库，保证企业数据结构不受故障，灾难崩溃等的影响。
一般分为两种，物理和逻辑，物理DG基于Redo Log应用，逻辑DG基于SQL应用。
1. Oracle Dataguard 的三种保护模式
1.1最大性能(maximize performance)模式
最大性能(maximize performance)是dataguard默认的保护模式。primay上的事务commit前不需要从standby上收到反馈信息，该模式在primary故障时可能丢失数据，但standby对primary的性能影响最小。
1.2最大可用(maximize availability)模式
在正常情况下，最大可用模式和最大保护模式一样；在standby不可用时，最大可用模式会自动降低成最大性能模式，所以standby故障不会导致primay不可用。只要至少有一个standby可用的情况下，即使primarydown机，也能保证不丢失数据。
1.3 最大保护(maximize protection)模式
最大保护(maximizeprotection)是最高级别的保护模式。primay上的事务在commit前必须确认redo已经传递到至少一个standby上，如果所有standby不可用，则primary会挂起。该模式能保证零数据丢失。
2 Oracle Dataguard两种日志传输方式
2.1Arch：传统的日志传送方式。现在只有在最大性能模式时才能采用。归档日志通过primary上的arch进程传送给standby的RFS进程。
2.2LGWR：oracle9i开始可以使用LGWR即时将日志传送到standby，而不再需要等到归档操作时才传送，已减少可能的数据丢失。在三种保护模式下都可以使用该方式传送日志。使用LGWR方式传送，在standby库上必须先建立standbyredo logfile。其中LGWR还分为LGWR ASYNC(异步)和LGWR SYNC(同步)两种系统环境：两台相同的centos
在主库(primary database)中提前安装好了Oracle 11gR2软件并创建了数据库，实例名为DB11G。
IP:192.168.9.69 hostname:robbiehost db_unique_name:DB11G
备库(standby database)(192.168.9.69)中只安装了Oracle 11gR2软件，未创建数据库。
IP:192.168.9.59 hostname:robbiehost2 db_unique_name:DB11G_STBY
二，简单配置
主库配置
1、确认主库开启了archivelog模式，并设置为force logging模式
sys@DB11G> ALTER DATABASE FORCE LOGGING; -- 取消使用 ALTER DATABASE NO FORCE LOGGING;
查看：select dbid,name,force_logging from v$database;
2，添加standby logfile，为主库添加多于一个的目前个数 logfile，比如之前三组，添加四组 -- 这个步骤可以不做，此时使用arch同步
alter database addstandby logfile group 4'/u01/app/oracle/oradata/DB11G/standby_redo04.log'size 50M;
3、登录到主库，了解一下数据文件的信息，之后安全关闭数据库，将全部数据文件拷贝到备库的相应目录下.
sys@DB11G> select name from v$datafile;
NAME
-----------------------------------
/data/oracle/oradata/DB11G/system01.dbf
/data/oracle/oradata/DB11G/sysaux01.dbf
/data/oracle/oradata/DB11G/undotbs01.dbf
/data/oracle/oradata/DB11G/users01.dbf
sys@DB11G> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /data/oracle/oradata/DB11G/
control01.ctl, /data/oracle
/flash_recovery_area/DB11G/con
trol02.ctlsys@DB11G> select group#,member from v$logfile;
GROUP# MEMBER
------ --------------------------------------------------
3 /data/oracle/oradata/DB11G/redo03.log
2 /data/oracle/oradata/DB11G/redo02.log
1 /data/oracle/oradata/DB11G/redo01.log
sys@DB11G> shutdown immediate
Database closed.Database dismounted.ORACLE instance shut down.
sys@DB11G> quit
[oracle@robbiehost~]$ cd /data/oracle/oradata/
[oracle@robbiehostoradata]$ scp -r DB11G/ oracle@192.168.9.59:/data/oracle/oradata/
oracle@192.168.1.201's password:
system01.dbf 100% 172MB 34.4MB/s 00:05
redo01b.rdo 100% 100MB 33.3MB/s 00:03
sysaux01.dbf 100% 100MB 50.0MB/s 00:02
undotbs01.dbf 100% 133MB 44.3MB/s 00:03
.......
当然这里可以给主库冷备tar包传递到备库，进入sqlplus
sqlplus format显示
set pages 0 不显示列标题，并且所有内容为一页，设置一页显示多少行
set feedback off 不显示结果集行数
注：默认只有结果大于6行时才显示结果的行数，set feedback off 一律不显示结果的行数
(3)编辑备份shell脚本
spool /home/oracle/db11g.sh 备份脚本
select name from v$datafile; 数据文件
select name from v$tempfile; 临时文件
select name from v$controlfile; 如果控制文件出错可以单独执行
select member from v$logfile; 重做日志
select value from v$parameter where name='spfile'; 参数文件
spool off 脚本创建完毕
修改db11g.sh脚本
把多行合并为一行
:%j 或 首位置按住J
把多个空格替换成一个空格
:%s/ \+/ /g或:%s# \+# #g (global全局替换)
关闭库，执行tar zcvf db11g.tar.gz 上边脚本内容，并将压缩后的文件复制到备库相应目录下，注意，一定要创建相应的目录结构（如果不同需要在配置文件中指定）
控制文件可以在主库mount下创建后复制到备库：
alter database create standby controlfile as '/home/oracle/control01.ctl';
4、主库修改初始化参数文件
sys@DB11G> create pfile from spfile;
File created.
sys@DB11G> !
[oracle@robbiehost oradata] cd $ORACLE_HOME/dbs
[oracle@robbiehost dbs] cat initDB11G.ora
DB11G.__db_cache_size=29360128
DB11G.__java_pool_size=16777216
DB11G.__large_pool_size=4194304
DB11G.__oracle_base='/data/oracle'#ORACLE_BASE set from environment
DB11G.__pga_aggregate_target=557842432
DB11G.__sga_target=230686720
DB11G.__shared_io_pool_size=0
DB11G.__shared_pool_size=167772160
DB11G.__streams_pool_size=0
*.audit_file_dest='/data/oracle/admin/DB11G/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data/oracle/oradata/DB11G/control01.ctl','/data/oracle/flash_recovery_area/DB11G/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DB11G'
*.db_recovery_file_dest='/data/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/data/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
*.local_listener=''DB11G_STBY '
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.memory_target=787480576
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
添加如下参数：
当为主库角色时的参数
官方文档：Data Guard Concepts and Administration->3 Creating a Physical Standby Database->3.1.4 Set Primary Database Initialization Parameters->Example 3-1 Primary Database: Primary Role Initialization Parameters
db_unique_name='DB11G' 指定数据库实例名
log_archive_config='DG_CONFIG=(DB11G,DB11G_STBY)' 一个DG所有的实例名包括主库和备库的
log_archive_dest_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DB11G' 指定主库归档日志目录路径
log_archive_dest_2='SERVICE=DB11G_STBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY' 指定使用redo异步方式，往哪个备库传递
LOG_ARCHIVE_DEST_STATE_1=ENABLE 是否允许传输日志到指定的目录>enable允许
LOG_ARCHIVE_DEST_STATE_2=ENABLE
当为备库角色时的参数
官方文档：Data Guard Concepts and Administration->3 Creating a Physical Standby Database->3.1.4 Set Primary Database Initialization Parameters->Example 3-2 Primary Database: Standby Role Initialization Parameters
fal_client='DB11G' fetch_archive_log_server=发送redo日志服务端，指向主库角色连接串名
fal_server='DB11G_STBY' fetch_archive_log_client=接收redo日志客户端，指向备库角色连接串名
DB_FILE_NAME_CONVERT='DB11G','DB11G_STBY1' 主备库数据文件路径转换，如果目录结构不同必须使用这个参数，备库放后面
LOG_FILE_NAME_CONVERT='DB11G','DB11G_STBY' 主备库重做日志文件路径转换，如果目录结构不同必须使用这个参数，备库放后面
STANDBY_FILE_MANAGEMENT=AUTO 当主库发生增加或者删除动作时相应备库是否自动更改
5、主库创建pfile和passwordfile后，拷贝到备库的相同路径下
[oracle@robbiehost dbs]orapwd file=orapwDB11G password=sys force=y ignorecase=y
[oracle@robbiehost dbs]scp -r orapwDB11G initDB11G.ora 192.168.1.201:/data/oracle/product/11.2.0/db_1/dbs
oracle@192.168.1.201's password:
orapwDB11G 100% 1536 1.5KB/s 00:00
initDB11G.ora 100% 1496 1.5KB/s 00:00
6、备库获取数据文件，初始化参数文件和口令文件后，对初始化参数文件进行相应修改。其实就将这几个参数依据备库，与主库做一个映像般的调换
[oracle@robbiehost2 dbs]$ cat initDB11G.ora
DB11G.__db_cache_size=92274688
DB11G.__java_pool_size=4194304
DB11G.__large_pool_size=4194304
DB11G.__oracle_base='/data/oracle'#ORACLE_BASE set from environment
DB11G.__pga_aggregate_target=507510784
DB11G.__sga_target=281018368
DB11G.__shared_io_pool_size=0
DB11G.__shared_pool_size=167772160
DB11G.__streams_pool_size=0
*.audit_file_dest='/data/oracle/admin/DB11G/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data/oracle/oradata/DB11G/standby.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DB11G'
*.db_recovery_file_dest='/data/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='DB11G_STBY'
*.diagnostic_dest='/data/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
*.fal_server='DB11G'
*.fal_client='DB11G_STBY'
*.local_listener='DB11G'
*.log_archive_config='DG_CONFIG=(DB11G,DB11G_STBY)'
*.log_archive_dest_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DB11G_STBY'
*.log_archive_dest_2='SERVICE=db11g LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.memory_target=787480576
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
7、配置主库和备库中的监听服务和tnsnames.ora
主库：
[oracle@robbiehost admin]$ cat listener.ora
# listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
#静态注册
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = DB11G)
(ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
(GLOBAL_DBNAME = db11g)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.59)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
ADR_BASE_LISTENER = /data/oracle
[oracle@robbiehost admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.69)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)
DB11G_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.59)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G_STBY)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
备库：
[sql] view plaincopy
[oracle@robbiehost2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = DB11G)
(ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
(GLOBAL_DBNAME = db11g)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
[oracle@robbiehost2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.69)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db11g)
)
)
DB11G_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.59)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G_STBY)
)
)
配置完成后，尝试从两台服务器以sysdba身份互相访问（也可用tnsping）
[oracle@robbiehost2 admin]$ sqlplus sys/robbie@DB11G as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 17:37:58 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
8、启动备库
[oracle@robbiehost2 admin]sqlplus / as sysdba
sys@DB11G> startup nomount;
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2217464 bytes
Variable Size 683674120 bytes
Database Buffers 92274688 bytes
Redo Buffers 6832128 bytes
sys@DB11G> alter database mount standby database;
Database altered.
sys@DB11G> alter database recover managed standby database disconnect from session;
Database altered.
sys@DB11G> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
查看保护模式，默认最大性能模式
elect name,database_role,protection_mode from v$database;
NAME DATABASE_ROLE PROTECTION_MODE
--------- ---------------- --------------------
PROD1 PRIMARY MAXIMUM PERFORMANCE
到这里基本已经配置完了
9、验证：主库中创建一个表，并插入若凡数据后，备库中能够同步出这些数据，则认为DG实施成功
主库：
sys@DB11G>create table test(id number);
Table created.
sys@DB11G> commit;
Commit complete.
sys@DB11G> begin
2 for i in 1..10 loop
3 insert into test values(i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
sys@DB11G> select * from test;
ID
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
sys@DB11G> alter system switch logfile;
System altered.
sys@DB11G> /
System altered.
备库
sys@DB11G> select name,database_role from v$database;
NAME DATABASE_ROLE
----------------------------------- ----------------
DB11G PHYSICAL STANDBY
sys@DB11G> select sequence#,first_time,next_time from v$archived_log;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
6 20-NOV-13 20-NOV-13
7 20-NOV-13 20-NOV-13
8 20-NOV-13 20-NOV-13
9 20-NOV-13 20-NOV-13
10 20-NOV-13 20-NOV-13
11 20-NOV-13 20-NOV-13
12 20-NOV-13 20-NOV-13
13 20-NOV-13 20-NOV-13
sys@DB11G> alter database recover managed standby database cancel;
Database altered.
sys@DB11G> alter database open read only;
Database altered.
sys@DB11G> select * from test;
ID
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
10 查看状态V$DATABASE V$MANAGED_STANDBY V$ARCHIVED_LOG V$LOG_HISTORY V$DATAGUARD_STATUS
主库：
select db_unique_name,open_mode,database_role,switchover_status,dataguard_broker,primary_db_unique_name from v$database;
select process,status,sequence#,delay_mins from v$managed_standby;
select sequence#,standby_dest,archived,applied,status from v$archived_log;
select dest_name,database_mode,recovery_mode,archived_seq#,applied_seq# from v$archive_dest_status;
备库：
select db_unique_name,open_mode,database_role,switchover_status,dataguard_broker,primary_db_unique_name from v$database;
select group#,bytes,status from v$standby_log;
注意：
一、确保两台服务器sid能tnsping通
二、Standby服务器可能需要手动启动RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
三、启动的时候，先备库的listener,再启动备库,再启动主库的listener,再启动主库，关闭的时候，先关闭主库，再关闭备库，还有备库不能正常关闭，必须abort方式关闭。而服务器及相关外设启动顺序
先开外设（磁带库、磁盘阵列等），后开主机，服务器及相关外设关闭顺序，先停数据库，再关主机，最后关外设
四、参数
1.DB_NAME：数据库名，其实这么说有点不准确，很容易让人理解错误，o小白干脆就把他理解成dg名算了，方正整个dg中所有的DB_NAME都必须是一致的。
2.DB_UNIQUE_NAME：真正的数据库名，用来标识在dg中不同的数据库的名字，以示区分。
3.DB_FILE_NAME_CONVERT/LOG_FILE_NAME_CONVERT：如果standby数据库和primary数据库在同一个系统中或者standby系统的数据文件或者是日志文件位置和primary库的不一样，那这个参数必须在standby库的参数文件中进行配置，以逗号分隔，将文件的路径用单引号括起来，以先primary库后standby库的方式配置。例如：db_file_name_convert='/u01/app/oracle/oradata/××××/datafile/','/u01/app/oracle/oradata/×××××/datafile/'。
4.CONTROL_FILES：控制文件的位置。
5.LOG_ARCHIVE_CONFIG：推荐参数。通过DG_CONFIG将dg中所有的DB_UNIQUE_NAME罗列出来，先primary库后standby库的方式配置。例如：LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago, boston)'。
6.LOG_ARCHIVE_DEST_n：归档文件的路径。最重要的参数之一，而且参数本身也十分复杂。那这里就趁势引入dataguard进程相关的一些工作机制。
dataguard中比较重要的进程有以下几个：
RFS：remote file server。该进程是standby库接受来自primary库lgwr进程触发的redo信息并且写入到standby redo log中。RFS进程无疑是要和其他进程配合的，也就是传输的进程。那这里就需要上篇的知识了，我们知道触发同步可能由ARCH或者是LGWR进程触发的，两者是不同的。如果是LGWR进程触发，那10g前的话也是由LGWR进程负责传输redo信息，RFS进程负责接收redo信息写入standby redo log中，10g之后则由LNSn进程完成；如果是ARCH进程触发，也就是归档日志传输的话，那就是由ARCH进程负责传输，RFS进程负责接收，然后写入指定的归档位置，然后再应用的。那这里不同的设置也决定了参数LOG_ARCHIVE_DEST_n的不同设置。详细见下。
LNSn：LGWR触发以后真正负责传输的进程，包括初始化网络I/O等一些列功能。
MRP：managed recovery process，简单来说就是物理standby是通过这个进程来实现数据的同步的，直接通过standby redo log或者是归档日志（取决于模式不同）来进行的一个数据恢复。
LSP:logical standby process：逻辑standby的方式，和上面的一样，只不过当中多了一步将redo信息转换成sql语句再恢复。也可以从这里看出逻辑standby和物理standby的不同。
那插播完了进程介绍，继续LOG_ARCHIVE_DEST_n，这个参数格式如下：
必须指定location/service：location代表的是本地的路径，在之后直接加入路径名例如LOG_ARCHIVE_DEST_1='LOCATION=/arch1/chicago/'
service代表的是net service name，通常就是standby数据库的net_service_name，可以在tnsnames.ora,定义NET SERVICE NAME.那根据前面所说的不同方式service的制定要复杂的多：LOG_ARCHIVE_DEST_2='SERVICE=net_service_name LGWR ASYNC'，那这里也可以是LGWR SYNC NET_TIMEOUT=××来设置同步并且指定超时时间。如果是应用归档的方式，那就直接LOG_ARCHIVE_DEST_2='SERVICE=net_service_name。
valid_for:这个参数的作用是指定该LOG_ARCHIVE_DEST_n的路径的redo log的type 和redo log的“身份”的，方便在转换的时候使用。VALID_FOR=(redo_log_type,database_role)type有下面这些： ONLINE_LOGFILE, STANDBY_LOGFILE, ALL_LOGFILES. “身份”是这些: PRIMARY_ROLE, STANDBY_ROLE, ALL_ROLES.
然后再附上DB_UNIQUE_NAME，完整例子如下：
LOG_ARCHIVE_DEST_1='LOCATION=/arch1/boston/VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston'
那这个参数应该是成对出现的LOCATION端一般都会指定，然后再指定standby端，我们在今后的实验中在看具体的例子。
7.LOG_ARCHIVE_DEST_STATE_n：和上面的LOG_ARCHIVE_DEST_n参数配置对应的LOG_ARCHIVE_DEST_n的状态：enable是可用，除此之外还有DEFER（不用）|ALTERNATE（备用）|RESET（不用）。
8.LOG_ARCHIVE_FORMAT：归档进程格式。
9.LOG_ARCHIVE_MAX_PRODUC：归档进程的数量，1-30，默认是4.
10.REMOTE_LOGIN_PASSWORD：三个选项，none（不实用密码文件，dg中不使用）exclusive（单个实例使用单个密码文件）或者shared（多个实例可以使用单个密码文件）。
11.FAL_SERVER：数据库的sid，通常是primary库。
12.FAL_CLIENT 指定一个数据库SID，通常该库为standby 角色。
13.STANDBY_FILE_MANAGEMENT：AUTO或者MANUAL。如果为AUTO则表示primary库的数据文件发生修改（新增，重命名），则在standby库会自动做相应的更改。如果是MANUAL，则表示手动。
以下是官方文档，参考下
官方连接http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php
Assumptions
· You have two servers (physical or VMs) with an operating system and Oracle installed on them. In this case I've used Oracle Linux 5.6 and Oracle Database 11.2.0.2.
· The primary server has a running instance.
· The standby server has a software only installation.
Primary Server Setup
Logging
Check that the primary database is in archivelog mode.
SELECT log_mode FROM v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL>
If it is noarchivelog mode, switch is to archivelog mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
Enabled forced logging by issuing the following command.
ALTER DATABASE FORCE LOGGING;
Initialization Parameters
Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to "DB11G" on the primary database.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DB11G
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string DB11G
SQL>
The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value "DB11G_STBY".
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';
Set suitable remote archive log destinations. In this case I'm using the fast recovery area for the local location, but you could specify an location explicitly if you prefer. Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive.
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the *_CONVERT parameters to account for your filename and path differences between the servers.
ALTER SYSTEM SET FAL_SERVER=DB11G_STBY;
--ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE;
--ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Remember, some of the parameters are not modifiable, so the database will need to be restarted before they take effect.
Service Setup
Entries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers. You can create these using the Network Configuration Utility (netca) or manually. The following entries were used during this setup.
DB11G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DB11G.WORLD)
)
)
DB11G_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DB11G.WORLD)
)
)
Backup Primary Database
If you are planning to use an active duplicate to create the standby database, then this step is unnecessary. For a backup-based duplicate, or a manual restore, take a backup of the primary database.
$ rman target=/
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
Create Standby Controlfile and PFILE
Create a controlfile for the standby database by issuing the following command on the primary database.
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/db11g_stby.ctl';
Create a parameter file for the standby database.
CREATE PFILE='/tmp/initDB11G_stby.ora' FROM SPFILE;
Amend the PFILE making the entries relevant for the standby database. I'm making a replica of the original server, so in my case I only had to amend the following parameters.
*.db_unique_name='DB11G_STBY'
*.fal_server='DB11G'
*.log_archive_dest_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'
Standby Server Setup (Manual)
Copy Files
Create the necessary directories on the standby server.
$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump
Copy the files from the primary to the standby server.
$ # Standby controlfile to all locations.
$ scp oracle@ol5-112-dga1:/tmp/db11g_stby.ctl /u01/app/oracle/oradata/DB11G/control01.ctl
$ cp /u01/app/oracle/oradata/DB11G/control01.ctl /u01/app/oracle/fast_recovery_area/DB11G/control02.ctl
$ # Archivelogs and backups
$ scp -r oracle@ol5-112-dga1:/u01/app/oracle/fast_recovery_area/DB11G/archivelog /u01/app/oracle/fast_recovery_area/DB11G
$ scp -r oracle@ol5-112-dga1:/u01/app/oracle/fast_recovery_area/DB11G/backupset /u01/app/oracle/fast_recovery_area/DB11G
$ # Parameter file.
$ scp oracle@ol5-112-dga1:/tmp/initDB11G_stby.ora /tmp/initDB11G_stby.ora
$ # Remote login password file.
$ scp oracle@ol5-112-dga1:$ORACLE_HOME/dbs/orapwDB11G $ORACLE_HOME/dbs
Notice, the backups were copied across to the standby server as part of the FRA copy. If your backups are not held within the FRA, you must make sure you copy them to the standby server and make them available from the same path as used on the primary server.
Start Listener
Make sure the listener is started on the standby server.
$ lsnrctl start
Restore Backup
Create the SPFILE form the amended PFILE.
$ export ORACLE_SID=DB11G
$ sqlplus / as sysdba
SQL> CREATE SPFILE FROM PFILE='/tmp/initDB11G_stby.ora';
Restore the backup files.
$ export ORACLE_SID=DB11G
$ rman target=/
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
Create Redo Logs
Create online redo logs for the standby. It's a good idea to match the configuration of the primary server.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo01.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo02.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo03.log') SIZE 50M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
In addition to the online redo logs, you should create standby redo logs on both the standby and the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following is standby redo logs must be created on both servers.
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;
Once this is complete, we can start the apply process.
Standby Server Setup (DUPLICATE)
Copy Files
Create the necessary directories on the standby server.
$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump
Copy the files from the primary to the standby server.
$ # Standby controlfile to all locations.
$ scp oracle@ol5-112-dga1:/tmp/db11g_stby.ctl /u01/app/oracle/oradata/DB11G/control01.ctl
$ cp /u01/app/oracle/oradata/DB11G/control01.ctl /u01/app/oracle/fast_recovery_area/DB11G/control02.ctl
$ # Parameter file.
$ scp oracle@ol5-112-dga1:/tmp/initDB11G_stby.ora /tmp/initDB11G_stby.ora
$ # Remote login password file.
$ scp oracle@ol5-112-dga1:$ORACLE_HOME/dbs/orapwDB11G $ORACLE_HOME/dbs
Start Listener
When using active duplicate, the standby server requires static listener configuration in a "listener.ora" file. In this case I used the following configuration.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DB11G.WORLD)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = DB11G)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga2.localdomain)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Make sure the listener is started on the standby server.
$ lsnrctl start
Create Standby Redo Logs on Primary Server
The DUPLICATE command automatically creates the standby redo logs on the standby. To make sure the primary database is configured for switchover, we must create the standby redo logs on the primary server.
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;
Create Standby Using DUPLICATE
Start the auxillary instance on the standby server by starting it using the temporary "init.ora" file.
$ export ORACLE_SID=DB11G
$ sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE='/tmp/initDB11G_stby.ora';
Connect to RMAN, specifying a full connect string for both the TARGET and AUXILLARY instances. DO not attempt to use OS authentication.
$ rman TARGET sys/password@DB11G AUXILIARY sys/password@DB11G_STBY
Now issue the following DUPLICATE command.
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='DB11G_STBY' COMMENT 'Is standby'
SET LOG_ARCHIVE_DEST_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'
SET FAL_SERVER='DB11G' COMMENT 'Is primary'
NOFILENAMECHECK;
A brief explanation of the individual clauses is shown below.
· FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
· FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.
· DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
· SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
· NOFILENAMECHECK: Destination file locations are not checked.
Once the command is complete, we can start the apply process.
Start Apply Process
Start the apply process on standby server.
# Foreground redo apply. Session never returns until cancel.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
# Background redo apply. Control is returned to the session once the apply process is started.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
If you need to cancel the apply process, issue the following command.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;
Provided you have configured standby redo logs, you can start real-time apply using the following command.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
Test Log Transport
On the primary server, check the latest archived redo log and force a log switch.
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time
FROM v$archived_log
ORDER BY sequence#;
ALTER SYSTEM SWITCH LOGFILE;
Check the new archived redo log has arrived at the standby server and been applied.
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#;
Protection Mode
There are three protection modes for the primary database:
· Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.
· Maximum Performance: Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.
· Maximum Protection: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.
By default, for a newly created standby database, the primary database is in maximum performance mode.
SELECT protection_mode FROM v$database;
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
SQL>
The mode can be switched using the following commands. Note the alterations in the redo transport attributes.
-- Maximum Availability.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
-- Maximum Performance.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
-- Maximum Protection.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;
Database Switchover
A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.
-- Convert primary database to standby
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
-- Shutdown primary database
SHUTDOWN IMMEDIATE;
-- Mount old primary database as standby database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
On the original standby database issue the following commands.
-- Convert standby database to primary
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
-- Shutdown standby database
SHUTDOWN IMMEDIATE;
STARTUP;
Once this is complete, test the log transport as before. If everything is working fine, switch the primary database back to the original server by doing another switchover. This is known as a switchback.
Failover
If the primary database is not available the standby database can be activated as a primary database using the following statements.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
Since the standby database is now the primary database it should be backed up immediately.
The original primary database can now be configured as a standby. If Flashback Database was enabled on the primary database, then this can be done relatively easily (shown here). If not, the whole setup process must be followed, but this time using the original primary server as the standby.
Flashback Database
It was already mentioned in the previous section, but it is worth drawing your attention to Flashback Database once more. Although a switchover/switchback is safe for both the primary and standby database, a failover renders the original primary database useless for converting to a standby database. If flashback database is not enabled, the original primary must be scrapped and recreated as a standby database.
An alternative is to enable flashback database on the primary (and the standby if desired) so in the event of a failover, the primary can be flashed back to the time before the failover and quickly converted to a standby database. That process is shown here.
Read-Only Standby and Active Data Guard
Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.
To switch the standby database into read-only mode, do the following.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
To resume managed recovery, do the following.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
In 11g, Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode, but still apply redo information. This means a standby can be available for querying, yet still be up to date. There are licensing implications for this feature, but the following commands show how active data guard can be enabled.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Since managed recovery continues with active data guard, there is no need to switch back to managed recovery from read-only mode in this case.
Snapshot Standby
Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.
If you are using RAC, turn off all but one of the RAC instances. Make sure the instance is in MOUNT mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
Make sure managed recovery is disabled.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Convert the standby to a snapshot standby. The following example queries the V$DATABASE view to show that flashback database is not enabled prior to the conversion operation.
SELECT flashback_on FROM v$database;
------------------
NO
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE OPEN;
SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
SQL>
You can now do treat the standby like any read-write database.
To convert it back to the physical standby, losing all the changes made since the conversion to snapshot standby, issue the following commands.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
NO
SQL>
The standby is once again in managed recovery and archivelog shipping is resumed. Notice that flashback database is still not enabled.
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
