Data Guard是Oracle的集成化灾难恢复解决方案。Data Guard具有两种灾难恢复备用数据库：物理数据库和逻辑数据库。从Oracle 7第7.3版就一直提供物理备用技术。Data Guard通过自动复制原始重做（redo）数据和将其应用到备用数据库，来保持物理备用数据库与生产数据的同步。这样可以得到一个备用数据库，它是主用生产数据库逐个数据块的精确复本。
逻辑备用在两个重要方面不同于物理备用：应用重做（redo）数据的方式和可用于报告编制的正常运行时间。逻辑备用数据库使用LogMiner和SQL Apply技术将主用数据库重做数据转换成SQL，然后将其应用于逻辑备用数据库。这使你的逻辑备用数据库可以全天候地用于查询，同时应用来自主用数据库的数据操作语言（DML）和数据库定义语言（DDL）。
该试验为配置DataGuard详细流程，从物理到逻辑以及三种保护模式全部实现！章节有限请大家在将环境架设成功后自己进行数据验证测试！Good Luck!
1.
ALTER DATABASE FORCE LOGGING;
2.
alter database add standby logfile 'D:\AuxDB\redo01B.log' size 50m;
alter database add standby logfile 'D:\AuxDB\redo02B.log' size 50m;
alter database add standby logfile 'D:\AuxDB\redo03B.log' size 50m;
3．拷贝主库口令文件并重新命名
4.修改主库orcl的参数文件initorcl.ora
*.audit_file_dest='F:\oracle\admin\orcl\adump'
*.background_dump_dest='F:\oracle\admin\orcl\bdump'
*.compatible='10.2.0.1.0'
*.control_files='F:\oracle\oradata\orcl\CONTROL01.CTL','F:\oracle\oradata\orcl\CONTROL02.CTL'
*.core_dump_dest='F:\oracle\admin\orcl\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='F:\oracle\flash_recovery_area'
*.db_unique_name='orcl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_config='dg_config=(orcl,auxdb)'
*.log_archive_dest_1='location=G:\10gArc VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_2='service=AUX LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=auxdb'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.log_archive_max_processes=4
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDO01'
*.user_dump_dest='F:\oracle\admin\orcl\udump'
*.fal_client='aux'
*.fal_server='orcl'
*.standby_file_management='AUTO'
*.standby_archive_dest='D:\AuxDB\standbyarchive'
5．备份主库
Rman target sys/oracle
backup database include current controlfile FOR STANDBY plus archivelog;
6．准备从库auxdb参数文件initauxdb.ora(用主库参数文件修改)
*.db_name=orcl
*.db_unique_name=auxdb
*.DB_FILE_NAME_CONVERT=('F:\oracle\oradata\orcl','D:\AuxDB')
*.LOG_FILE_NAME_CONVERT=('F:\oracle\oradata\orcl','D:\AuxDB')
*.control_files='D:\AuxDB\control01.CTL'
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.compatible='10.2.0.1.0'
*.db_block_size=8192
*.sga_target=250000000
*.background_dump_dest=D:\AuxDB\bdump
*.core_dump_dest=D:\AuxDB\cdump
*.user_dump_dest=D:\AuxDB\udump
#*.standby_archive_dest='D:\AuxDB\standbyarchive'
*.undo_management='AUTO'
*.undo_tablespace='UNDO01'
*.fal_server='orcl'
*.fal_client='aux'
*.standby_file_management='AUTO'
*.log_archive_config='dg_config=(orcl,auxdb)'
*.log_archive_dest_1='location=D:\AuxDB\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=auxdb'
*.LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_max_processes=4
7．创建从库服务(windiws系统)
oradim -NEW -SID AuxDB
8．为主库和从库配置网络
Listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
(SID_NAME = auxdb)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
Tnsnames.ora
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
aux =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = auxdb)
)
)
9．用rman 还原从库(以下内容为屏幕假脱机)
Set ORACLE_SID=auxdb
Sqlplus /nolog
@ > conn / as sysdba
Connected to an idle instance.
auxdb@ SYS AS SYSDBA> startup nomount --启动到nomount
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1248380 bytes
Variable Size 83886980 bytes
Database Buffers 163577856 bytes
Redo Buffers 2945024 bytes
auxdb@ SYS AS SYSDBA> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
连接到rman进行还原
C:\>rman target sys/oracle@orcl auxiliary sys/oracle
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jan 30 18:52:11 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1164625023)
connected to auxiliary database: ORCL (not mounted)
用duplicate命令生成从库，以下全是屏幕显示
RMAN> duplicate target database for standby;
Starting Duplicate Db at 2008-01-30 18:52:26
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK
contents of Memory Script:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting restore at 2008-01-30 18:52:27
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: restoring control file
ORA-19625: error identifying file D:\AUXDB\STANDBY.CTL
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。
ORA-19600: input file is control file (D:\AUXDB\STANDBY.CTL)
ORA-19601: output file is control file (D:\AUXDB\CONTROL01.CTL)
failover to previous backup
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece G:\RMANBK\ORCL_20080130_645386968.BK
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=G:\RMANBK\ORCL_20080130_645386968.BK tag=TAG20080130T180742
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output filename=D:\AUXDB\CONTROL01.CTL
Finished restore at 2008-01-30 18:52:30
sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
contents of Memory Script:
{
set newname for datafile 1 to
"D:\AUXDB\SYSTEM01.DBF";
set newname for datafile 3 to
"D:\AUXDB\SYSAUX01.DBF";
set newname for datafile 4 to
"D:\AUXDB\USERS01.DBF";
set newname for datafile 5 to
"D:\AUXDB\EXAMPLE01.DBF";
set newname for datafile 6 to
"D:\AUXDB\INDX01.DBF";
set newname for datafile 7 to
"D:\AUXDB\UNDO01";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2008-01-30 18:52:35
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\AUXDB\SYSTEM01.DBF
restoring datafile 00003 to D:\AUXDB\SYSAUX01.DBF
restoring datafile 00004 to D:\AUXDB\USERS01.DBF
restoring datafile 00005 to D:\AUXDB\EXAMPLE01.DBF
restoring datafile 00006 to D:\AUXDB\INDX01.DBF
restoring datafile 00007 to D:\AUXDB\UNDO01
channel ORA_AUX_DISK_1: reading from backup piece G:\RMANBK\ORCL_20080130_645386862.BK
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=G:\RMANBK\ORCL_20080130_645386862.BK tag=TAG20080130T180742
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:26
Finished restore at 2008-01-30 18:54:02
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=7 stamp=645389642 filename=D:\AUXDB\SYSTEM01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=8 stamp=645389642 filename=D:\AUXDB\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=9 stamp=645389642 filename=D:\AUXDB\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy recid=10 stamp=645389642 filename=D:\AUXDB\EXAMPLE01.DBF
datafile 6 switched to datafile copy
input datafile copy recid=11 stamp=645389642 filename=D:\AUXDB\INDX01.DBF
datafile 7 switched to datafile copy
input datafile copy recid=12 stamp=645389642 filename=D:\AUXDB\UNDO01
Finished Duplicate Db at 2008-01-30 18:54:03
RMAN>屏幕显示结束，duplicate成功，数据库已经由nomount到mount状态
在从库中进行管理恢复
aux@ SYS AS SYSDBA> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
Elapsed: 00:00:06.18
在主库校验归档路径是否有error
orcl@ SYS AS SYSDBA> select dest_name,status,error from v$archive_dest;
DEST_NAME STATUS ERROR
-------------------- --------- ------------------------------
LOG_ARCHIVE_DEST_1 VALID
LOG_ARCHIVE_DEST_2 VALID
LOG_ARCHIVE_DEST_3 INACTIVE
LOG_ARCHIVE_DEST_4 INACTIVE
LOG_ARCHIVE_DEST_5 INACTIVE
LOG_ARCHIVE_DEST_6 INACTIVE
LOG_ARCHIVE_DEST_7 INACTIVE
LOG_ARCHIVE_DEST_8 INACTIVE
LOG_ARCHIVE_DEST_9 INACTIVE
LOG_ARCHIVE_DEST_10 INACTIVE
10 rows selected.
Elapsed: 00:00:00.06
在主库中切换日志
orcl@ SYS AS SYSDBA> alter system switch logfile;
在从库中查看
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------------- -------------------
72 2008-01-22 17:19:40 2008-01-22 17:51:52
73 2008-01-22 17:51:52 2008-01-22 17:58:17
74 2008-01-22 17:58:17 2008-01-30 16:16:22
75 2008-01-30 16:16:22 2008-01-30 17:58:05
76 2008-01-30 17:58:05 2008-01-30 17:59:12
77 2008-01-30 17:59:12 2008-01-30 18:00:51
78 2008-01-30 18:00:51 2008-01-30 18:07:35
79 2008-01-30 18:07:35 2008-01-30 18:09:30
8 rows selected.
Elapsed: 00:00:00.08
在从库中校验归档日志是否被应用
aux@ SYS AS SYSDBA> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APP
---------- ---
72 NO
73 NO
74 NO
75 NO
76 NO
77 NO
78 YES
79 YES
至此主备配置成功！可以进行数据测试了！这里就不做脱机了，一般不会出错，在主库中create insert update delete 增加减少tablespace,datafile ，当从库的归档被应用以后在从库中校验数据是否成功同步
主从切换测试:
orcl@ SYS AS SYSDBA> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
Elapsed: 00:00:00.21
orcl@ SYS AS SYSDBA> select PROTECTION_MODE,DATABASE_ROLE from v$database;
PROTECTION_MODE DATABASE_ROLE
-------------------- ----------------
MAXIMUM PERFORMANCE PRIMARY
Elapsed: 00:00:00.05
主变从：
orcl@ SYS AS SYSDBA> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
Database altered.
Elapsed: 00:00:43.06
orcl@ SYS AS SYSDBA> select PROTECTION_MODE,DATABASE_ROLE from v$database;
select PROTECTION_MODE,DATABASE_ROLE from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
orcl@ SYS AS SYSDBA> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
orcl@ SYS AS SYSDBA> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 62915940 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
orcl@ SYS AS SYSDBA> select PROTECTION_MODE,DATABASE_ROLE from v$database;
PROTECTION_MODE DATABASE_ROLE
-------------------- ----------------
MAXIMUM PERFORMANCE PHYSICAL STANDBY –主变从成功
Elapsed: 00:00:00.04
orcl@ SYS AS SYSDBA> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
service_names string orcl
orcl@ SYS AS SYSDBA> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
Elapsed: 00:00:00.12
影响切换的进程有
进程名 描述 解决方法
CJQ0 job队列进程 将JOB_QUEUE_PROCESSES动态改为0，但是不要改spfile
QMN0 高级队列时间管理器 将AQ_TM_PROCESSES动态改为0，但是不要改spfile
DBSNMP oem 的代理 执行emctl stop agent停止代理
此时必须执行以下语句切换。
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
3、将主数据切换为备用数据库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
从变主：
aux@ SYS AS SYSDBA> select PROTECTION_MODE,DATABASE_ROLE from v$database;
PROTECTION_MODE DATABASE_ROLE
-------------------- ----------------
MAXIMUM PERFORMANCE PHYSICAL STANDBY
Elapsed: 00:00:00.01
aux@ SYS AS SYSDBA> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
Elapsed: 00:00:01.01
aux@ SYS AS SYSDBA> select PROTECTION_MODE,DATABASE_ROLE from v$database;
PROTECTION_MODE DATABASE_ROLE
-------------------- ----------------
MAXIMUM PERFORMANCE PRIMARY –从变主成功
Elapsed: 00:00:00.02
aux@ SYS AS SYSDBA> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string F:\oracle\oradata\orcl, D:\Aux
DB
db_name string orcl
db_unique_name string auxdb
global_names boolean FALSE
instance_name string auxdb
lock_name_space string
log_file_name_convert string F:\oracle\oradata\orcl, D:\Aux
DB
service_names string auxdb
aux@ SYS AS SYSDBA> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
Elapsed: 00:00:00.04
转换物理备库为逻辑备库：
- 停止物理备用库的管理恢复进程（orcl已经变成从）
orcl@ SYS AS SYSDBA> alter database recover managed standby database cancel;
Database altered.
- 在主库为logminer生成数据字典信息到日志中（aux已经变成主）
aux@ SYS AS SYSDBA> exec dbms_logstdby.build;
PL/SQL procedure successfully completed.
Elapsed: 00:00:29.09
3. 最好将主库归一次档，确保信息都能传到从库
aux@ SYS AS SYSDBA> alter system archive log current;
System altered.
Elapsed: 00:00:09.87
- 将物理standby转换为逻辑standby,同时将库名修改为auxdb，逻辑standby 的库名是不需要与主库相同的
orcl@ SYS AS SYSDBA> alter database recover to logical standby auxdb;
alter database recover to logical standby auxdb
*
ERROR at line 1:
ORA-16254: change db_name to AUXDB in the client-side parameter file (pfile)
--该提示是由于我使用的是pfile，db_name没有修改成功，需要管理员手工将参数文件中的db_name修改为auxdb
orcl@ SYS AS SYSDBA> shutdown immediate
orcl@ SYS AS SYSDBA> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 62915940 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes
ORA-01103: database name 'AUXDB' in control file is not 'ORCL'
报错了吧！修改db_name=auxdb再重新启动！
orcl@ SYS AS SYSDBA> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 62915940 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes
Database mounted.
以resetlogs方式open数据库
orcl@ SYS AS SYSDBA> alter database open resetlogs;
Database altered.
Elapsed: 00:00:23.79
重新启用apply
orcl@ SYS AS SYSDBA> alter database start logical standby apply;
Database altered.
Elapsed: 00:00:12.91
校验数据库模式：
orcl@ SYS AS SYSDBA> select NAME,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME PROTECTION_MODE DATABASE_ROLE
-------- -------------------- ----------------
AUXDB MAXIMUM PERFORMANCE LOGICAL STANDBY
Elapsed: 00:00:00.04
开启实时应用特性（从库需要联机日志支持）：
alter database add standby logfile 'F:\ORACLE\ORADATA\ORCL\redo01B.log' size 50m;
alter database add standby logfile 'F:\ORACLE\ORADATA\ORCL\redo02B.log' size 50m;
alter database add standby logfile 'F:\ORACLE\ORADATA\ORCL\redo03B.log' size 50m;
orcl@ SYS AS SYSDBA> alter database stop logical standby apply;
Database altered.
orcl@ SYS AS SYSDBA> alter database start logical standby apply immediate;
Database altered.
由物理standby象逻辑standby转换成功！
DATAGURAD三种保护模式：
最大的性能模式（默认模式）：
alter database set standby database TO MAXIMUM PERFORMANCE;
此模式下，也提供最搞的数据保护并且不映像主库性能，允许事务提交后，写到本地的在线日志可以恢复刚提交的事务
最大可用模式：
orcl@ SYS AS SYSDBA> alter database set standby database TO MAXIMIZE AVAILABILITY;
Database altered.
Elapsed: 00:00:00.01
orcl@ SYS AS SYSDBA> select NAME,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME PROTECTION_MODE DATABASE_ROLE
-------- -------------------- ----------------
AUXDB MAXIMUM AVAILABILITY LOGICAL STANDBY
当主库发生错误时，此模式下将没有数据丢失，提供此等级的保护，REDO数据要求能恢复任何一个事务。也就说REDO必须写入本地的在线日志和STANDBY日志在事务提交前，如果主库发生错误不能写 日志到备库，主库可以不关闭，此时数据库将处于最大性能模式直到错误解决，主库可以自动恢复到最大可用模式
最大保护模式（要在mount下转换）：
orcl@ SYS AS SYSDBA> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
Database mounted.
orcl@ SYS AS SYSDBA>
orcl@ SYS AS SYSDBA> alter database set standby database TO MAXIMIZE PROTECTION;
Database altered.
Elapsed: 00:00:00.11
orcl@ SYS AS SYSDBA> alter database open;
Database altered.
Elapsed: 00:00:13.47
orcl@ SYS AS SYSDBA> select NAME,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME PROTECTION_MODE DATABASE_ROLE
-------- -------------------- ----------------
AUXDB MAXIMUM PROTECTION LOGICAL STANDBY
Elapsed: 00:00:00.05
当主库发生错误时，此模式下将没有数据丢失，提供此等级的保护，REDO数据要求能恢复任何一个事务。也就说REDO必须写入本地的在线日志和STANDBY日志在事务提交前，如果主库发生错误不能写 日志到备库，那么主库将关闭
参考文档B19306_01\server.102\b14239.pdf
Oracle® Data Guard Concepts and Administration (page 71)
Table 5-1 LOG_ARCHIVE_DEST_STATE_n Initialization Parameter Attributes
739
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
