本文包括oracle dataguard 在linux下的安装，版本为rhel5.6,oracle10g
10204_dataguard
安装RHEL 5.6
192.168.0.31 linux1
192.168.0.32 linux2
相关条件及操作：
(1)检查数据库是否支持Data Guard（企业版才支持）
SQL>
SQL> set linesize 180
SQL> select * from v$option where parameter='Managed Standby';
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Managed Standby TRUE
SQL>
(2)设置归档目录
startup mount
alter database archicelog
alter database open
SQL> alter system set log_archive_dest_1='location=/db/rhtst_arch';
================================================================================================================================
#####################################
## 10gR2 10204 DATAGUARD建立准备 ##
#####################################
---------------------------------------------------------------------------
Operating System： Red Hat Enterprise Linux Server release 5.2 (Tikanga)
Database Software: Oracle 10gR2 102040
在主服务器上创建数据库，两个服务器上的oracle环境变量都相同
主库：192.168.0.31 rhdb1
从库：192.168.0.32 rhdb2
数据库信息:
service_names rhtst
instance_name orcl
---------------------------------------------------------------------------
1.建立从库密码文件
Step 3. Enable force logging (optional).
Step 4. Create standby redo logs (optional).
Step 5. Configure the primary initialization parameters.
Step 5. 配置网络参数文件
Step 6. standby的密码文件
Step 7. standby 初始化文件
Step 8. 备用库的目录创建
Step 2. Create a password file.
=================================
从库需要建立密码文件
Due to new log transport security and authentication features, it is mandatory that
every database in a Data Guard configuration utilize a password file.
$cd $ORACLE_HOME/dbs
$orapwd file=orapworcl password=oracle
Once the password file is created, you must set the following parameter in the spfile
while the database is in the nomount state:
alter system set remote_login_passwordfile=exclusive scope=spfile;
默认的情况下是为exclusive的状态：
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL>
Step 3. Enable force logging (optional).
==========================================
To place the primary database in forced logging mode, enter the following as sys:
alter database force logging;
Step 4. Create standby redo logs (optional).
=============================================
注意：standby redo logs的数量要大于redo logs的数量, 而且大小一定要和在线redo logs相同。
Certain protection modes, such as maximum protection and maximum availability, mandate
that standby redo logs be present. Standby redo logs are highly recommended regardless
of your protection mode, as generally more data can be recovered during a failover than
without them. A best practice is to create the standby redo logs on both the primary and
the standby so as to make role transitions smoother.
By creating standby redo logs at this stage, we can assure that they exist on both the
primary and newly created standby. When creating the standby redo log groups, we should
have one more standby redo log file group than the number of online redo log file groups
on the primary database. In addition, the size of the standby redo logs must match exactly
with the size of online redo logs.
alter database add standby logfile '/opt/app/oracle/oradata/eall/stdy_redo01.log' size 20m;
-----------------------------------------
control: 1-20 (20)
redolog: 21-60 (40)
standby: 61-100 (40)
datafile: 101- ()
注意：
DATAGUARD: standby 保留给dataguard的联机传送日志使用， redolog在日志组含有1个日志文件时
可以达到40组日志，使用每组含有2个日志文件时可以达到20组日志。
RAC: control 组一同提供control、ocr、vote等的裸设备号，standby提供另一节点的裸设备号。
$ ln -s /dev/raw/raw24 redo04.log
SQL> alter database add logfile group 4 '/u02/oradata/rhtst/redo04.log' size 100m;
#
lvcreate -L 120M -n lvdb_stdy_redo01 vg00
lvcreate -L 120M -n lvdb_stdy_redo02 vg00
lvcreate -L 120M -n lvdb_stdy_redo03 vg00
lvcreate -L 120M -n lvdb_stdy_redo04 vg00
lvcreate -L 120M -n lvdb_stdy_redo05 vg00
/etc/rc.local
raw /dev/raw/raw61 /dev/vg00/lvdb_stdy_redo01
raw /dev/raw/raw62 /dev/vg00/lvdb_stdy_redo02
raw /dev/raw/raw63 /dev/vg00/lvdb_stdy_redo03
raw /dev/raw/raw64 /dev/vg00/lvdb_stdy_redo04
raw /dev/raw/raw65 /dev/vg00/lvdb_stdy_redo05
[oracle@rhdb1 rhtst]$ pwd
/u02/oradata/rhtst
ln -s /dev/raw/raw61 stdy_redo01.log
ln -s /dev/raw/raw62 stdy_redo02.log
ln -s /dev/raw/raw63 stdy_redo03.log
ln -s /dev/raw/raw64 stdy_redo04.log
ln -s /dev/raw/raw65 stdy_redo05.log
Step 5. 配置网络参数文件
=========================
(1) 配置网络文件
----------------
主库--原：
[oracle@rhdb1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/oracle/product/10.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 = /u01/app/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rhdb1.boomsun.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
[oracle@rhdb1 admin]$
主库--改后的文件:
[oracle@rhdb1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/oracle/product/10.2.0/db_1)
(GLOBAL_DBNAME = rhtst)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.31)(PORT = 1521))
)
)
[oracle@rhdb1 admin]$
从库--原：
[oracle@rhdb2 admin]$ cat listener.ora.bak
# listener.ora Network Configuration File: /u01/app/oracle/oracle/product/10.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 = /u01/app/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rhdb2.boomsun.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
[oracle@rhdb2 admin]$
从库--改后的文件:
[oracle@rhdb2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/oracle/product/10.2.0/db_1)
(GLOBAL_DBNAME = rhtst)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.32)(PORT = 1521))
)
)
[oracle@rhdb2 admin]$
主库和从库的 tnsnames.ora
添加：
DBPRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.31)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = rhtst)
)
)
DBSTANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.32)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = rhtst)
)
)
Step 6. standby的密码文件
==========================
新建或者拷贝(此部应该在step2已经完成)：
$cd $ORACLE_HOME/dbs
$orapwd file=orapworcl password=sun2moon
orapworcl
Step 7. standby 初始化文件
--------------------------
添加：
*.standby_file_management=AUTO
*.standby_archive_dest='/u02/rhtst_arch'
*.fal_server='DBPRIMARY'
*.fal_client='DBSTANDBY'
参数忽略
*.remote_archive_enable=TRUE
Step 8. 备用库的目录创建
-------------------------
cd $ORACLE_BASE
mkdir -p admin/rhtst/
cd admin/rhtst/
mkdir adump bdump cdump dpdump pfile scripts udump
[oracle@rhdb2 dbs]$ mkdir -p /u01/app/oracle/flash_recovery_area
##########
## 主库 ##
##########
[oracle@rhdb1 ~]$ rman target /
RMAN> backup format '/u01/backup/%d_%s_%t_%p_full.bus' database include current controlfile for standby;
注意：命令执行完后，警告日志中，将出现如下信息：（它指定了stndby logfile的大小）
Mon Jun 1 01:41:44 2009
Clearing standby activation ID 702116058 (0x29d970da)
The primary database controlfile was created using the
'MAXLOGFILES 36' clause.
There is space for up to 32 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 104857600;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 104857600;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 104857600;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 104857600;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 104857600;
$ bc
104857600 / 1024 / 1024
100
可见日志大小为100M
standby log的大小限制：
Ensure log file sizes are identical on the primary and standby databases.
The size of the current standby redo log files must exactly match the size of the current
primary database online redo log files.（此可作为从库创建STANDBY LOGFILE的参考）
redo log的数量：
Use the following equation to determine an
appropriate number of standby redo log file groups:
(maximum number of logfiles for each thread + 1) * maximum number of threads
##########
## 从库 ##
##########
[oracle@rhdb2 dbs]$ sqlplus "/as sysdba"
SQL> startup nomount pfile=?/dbs/initorcl.ora
[oracle@rhdb2 ~]$ rman target /
RMAN> restore standby controlfile from '/u01/backup/RHTST_15_688354904_1_full.bus';
Starting restore at 01-JUN-09
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/u02/oradata/rhtst/control01.ctl
output filename=/u02/oradata/rhtst/control02.ctl
output filename=/u02/oradata/rhtst/control03.ctl
Finished restore at 01-JUN-09
RMAN>
RMAN> sql 'alter database mount standby database';
sql statement: alter database mount standby database
released channel: ORA_DISK_1
RMAN>
RMAN> restore database;
Starting restore at 01-JUN-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/rhtst/system01.dbf
restoring datafile 00002 to /u02/oradata/rhtst/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/rhtst/sysaux01.dbf
restoring datafile 00004 to /u02/oradata/rhtst/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/RHTST_7_688343890_1_full.bus
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backup/RHTST_7_688343890_1_full.bus tag=TAG20090531T223810
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 01-JUN-09
RMAN>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
##########
## 主库 ##
##########
主库：最大性能
arch 归档的方式
SQL> alter system set log_archive_dest_2='SERVICE=dbstandby REOPEN=300' scope=both;
SQL> alter system set log_archive_dest_state_2=enable scope=both;
SQL> alter system switch logfile;
检验日志传送是否成功：
Next, perform a log switch on the primary and verify that the transmission of that log was successful:
SQL> select status,error from v$archive_dest where dest_id=2;
STATUS ERROR
--------- -----------------------------------------------------------------
VALID
====================================================================================
从库：
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;
standby的group 从11开始：
alter database add standby logfile group 11 ('/u02/oradata/rhtst/stdy_redo01.log') size 100m;
alter database add standby logfile group 12 ('/u02/oradata/rhtst/stdy_redo02.log') size 100m;
alter database add standby logfile group 13 ('/u02/oradata/rhtst/stdy_redo03.log') size 100m;
alter database add standby logfile group 14 ('/u02/oradata/rhtst/stdy_redo04.log') size 100m;
alter database add standby logfile group 15 ('/u02/oradata/rhtst/stdy_redo05.log') size 100m;
注意：为了日后切换的方便，建议在主库也设置一下。
-----------------------------------------------------------------
Example 3–2 Adding a Standby Redo Log File Group to a Specific Group Number
You can also specify a number that identifies the group using the GROUP clause:
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
2> ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;
Using group numbers can make administering standby redo log file groups easier.
However, the group number must be between 1 and the value of the MAXLOGFILES
clause. Do not skip log file group numbers (that is, do not number groups 10, 20, 30,
and so on), or you will use additional space in the standby database control file.
注意：跳跃是允许的，但需要额外的控制文件存储空间
-----------------------------------------------------------------
如何删除：
alter database drop standby logfile group 11;
alter database drop standby logfile group 12;
alter database drop standby logfile group 13;
alter database drop standby logfile group 14;
alter database drop standby logfile group 15;
从只读方式到管理恢复方式（如果出现其它问题，重启数据库至管理恢复模式）
SQ>recover managed standby database disconnect from session;
############################################################################################
主库：最大性能
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=dbstandby LGWR' scope=both;
System altered.
SQL> alter system set log_archive_dest_state_2=enable scope=both;
System altered.
========================================================================================
此时主库切日志与插入测试数据，从库查看警告文件：
主库：
SQL> alter system switch logfile;
System altered.
SQL> /
从库：
Primary database is in MAXIMUM PERFORMANCE mode
RFS[6]: Successfully opened standby log 11: '/u02/oradata/rhtst/stdy_redo01.log'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[6]: Successfully opened standby log 12: '/u02/oradata/rhtst/stdy_redo02.log'
检验测试数据是否传送：
从管理恢复模式转到只读模式
SQL>recover managed standby database cancel;
SQL>alter database open read only;
========================================================================================
主库：最大可用
SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
SQL> shutdown immediate
SQL> startup mount;
SQL> alter system set log_archive_dest_2='SERVICE=dbstandby LGWR SYNC AFFIRM' scope=both;
SQL> alter database set standby database to maximize AVAILABILITY;
SQL> alter database open;
SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY
开启从库实时应用：
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select dest_id, dest_name, status, recovery_mode from v$archive_dest_status;
DEST_ID DEST_NAME STATUS RECOVERY_MODE
---------- ------------------------------ --------- -----------------------
1 LOG_ARCHIVE_DEST_1 VALID MANAGED REAL TIME APPLY
2 LOG_ARCHIVE_DEST_2 INACTIVE MANAGED REAL TIME APPLY
3 LOG_ARCHIVE_DEST_3 INACTIVE MANAGED REAL TIME APPLY
4 LOG_ARCHIVE_DEST_4 INACTIVE MANAGED REAL TIME APPLY
5 LOG_ARCHIVE_DEST_5 INACTIVE MANAGED REAL TIME APPLY
6 LOG_ARCHIVE_DEST_6 INACTIVE MANAGED REAL TIME APPLY
7 LOG_ARCHIVE_DEST_7 INACTIVE MANAGED REAL TIME APPLY
8 LOG_ARCHIVE_DEST_8 INACTIVE MANAGED REAL TIME APPLY
9 LOG_ARCHIVE_DEST_9 INACTIVE MANAGED REAL TIME APPLY
10 LOG_ARCHIVE_DEST_10 INACTIVE MANAGED REAL TIME APPLY
11 STANDBY_ARCHIVE_DEST VALID MANAGED REAL TIME APPLY
11 rows selected.
SQL>
#################
## VALID_FOR ##
#################
使用VALID_FOR达到切换时无需更换参数文件的设置：
（1）主库上操作
alter system set log_archive_dest_1='LOCATION=/u02/rhtst_arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)';
alter system set log_archive_dest_2='SERVICE=dbstandby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)';
/* 注意这句只在数据库的角色为PRIMARY_ROLE时才有效 */
注意：如果是在一台服务器上做dataguard，还需要设置DB_UNIQUE_NAME，如下所示：
alter system set log_archive_dest_1='LOCATION=/u02/rhtst_arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=rhtst';
为了自动切换时的准备，此时在主库为PRIMARY时，下面的设置不起任何作用。
*.standby_file_management=AUTO
*.standby_archive_dest='/u02/rhtst_arch'
*.fal_server='DBSTANDBY'
*.fal_client='DBPRIMARY'
alter system set standby_file_management=AUTO;
alter system set standby_archive_dest='/u02/rhtst_arch';
alter system set fal_server='DBSTANDBY';
alter system set fal_client='DBPRIMARY';
-------------------------------------------------------------
（2）从库上操作
原从库添加：
*.standby_file_management=AUTO
*.standby_archive_dest='/u02/rhtst_arch'
*.fal_server='DBPRIMARY'
*.fal_client='DBSTANDBY'
alter system set log_archive_dest_1='LOCATION=/u02/rhtst_arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)';
alter system set log_archive_dest_2='SERVICE=dbprimary LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)';
alter system set log_archive_dest_state_2=enable;
#############################
## OPTIONAL 和 MANDATORY ##
#############################
例子：
alter system set log_archive_dest_2='SERVICE=dbstandby OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=rhtst';
Specifies the policy for reusing online redo log files:
MANDATORY—specifies that filled online log files must be successfully archived to
the destination before they can be reused.
OPTIONAL—specifies that successful archival to the destination is not required
before the online redo log file can be made available for reuse.
(没有指定时，默认是OPTIONAL)
If neither the MANDATORY nor the OPTIONAL attribute is specified, the default is OPTIONAL.
(默认的时候本地的归档目录是mandatory)
At least one local destination is operationally treated as mandatory, because the
minimum value for the LOG_ARCHIVE_MIN_SUCCEED_DEST parameter is 1.
##################################################################################################################
##################################### 备用服务器的管理模式与只读模式 #######################################
启动到管理模式
SQL>shutdown immediate
SQL>startup nomount pfile=?/dbs/initorcl.ora
SQL>alter database mount standby database
SQL>alter database recover managed standby database disconnect from session;
启动到只读方式
SQL>shutdown immediate
SQL>startup nomount pfile=?/dbs/initorcl.ora
SQL>alter database mount standby database
SQL>alter database open read only
如果在管理恢复模式下到只读模式
SQL>recover managed standby database cancel;
SQL>alter database open read only;
这个时候，可以给数据库增加临时数据文件（这个在热备份的时候是没有备份过来的,10gR2的rman不用重新添加）, 如:
alter tablespace temp add tempfile '/opt/app/oracle/oradata/eall/temp01.dbf' size 800m reuse;
从只读方式到管理恢复方式
SQ>recover managed standby database disconnect from session;
alter system set log_archive_dest_state_2 = 'enable';
注意：如果网络连接中断，也可以直接使用上面的语句（类似重启），不必等待默认的重连时间。
##############################################################################################################
欢迎关注我的公众号 扫描二维码或公众号搜索 “我的工作”
