ORACLE 11G 搭建dataguard详细步骤（所有操作总结）
序言：
DATAGUARD是通过建立一个PRIMARY和STANDBY组来确立其参照关系。
STANDBY一旦创建，DATAGUARD就会通过将主数据库(PRIMARY)的REDO传递给STANDBY数据库，然后在STANDBY中应用REDO实现数据库的同步。
有两种类型的STANDBY：物理STANDBY和逻辑STANDBY
物理STANDBY提供与主数据库完全一样的拷贝（块到块），数据库SCHEMA，包括索引都是一样的。它是直接应用REDO实现同步的。
逻辑STANDBY则不是这样，在逻辑STANDBY中，逻辑信息是相同的，但物理组织和数据结构可以不同，它和主库保持同步的方法是将接收的REDO转换成SQL语句，然后在STANDBY上执行SQL语句。逻辑STANDBY除灾难恢复外还有其它用途，比如用于用户进行查询和报表。
1、安装环境
在primary搭建数据库软件，建立lsnrctl监听，采用dbca搭建实例，在standby上搭建数据库软件，建立监听，但是不需要采用dbca建立实例。
如何在linux上搭建oracle数据库，请参考以前的blog实验：http://blog.itpub.net/26230597/viewspace-1413242/
操作系统： 都是centos6.4
oracle软件版本： oracle 11.2.0.1.0
IP地址： primary库（192.168.121.217）、standby库（192.168.121.218）
db_unique_name： primary库（pdunq）、standby库（pdunq_dg）
2、准备工作 在primary上操作
2.1、打开Forced Logging 模式
先确认primary库处于归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 362
Next log sequence to archive 364
Current log sequence 364
SQL>
强制归档
SQL> alter database force logging;
Database altered.
SQL>
确认primary库是归档模式
添加standby文件
alter database add standby logfile group 4 (‘/home/oradata/powerdes/redo_dg_021.log‘) size 20M;
alter database add standby logfile group 5 (‘/home/oradata/powerdes/redo_dg_022.log‘) size 20M;
alter database add standby logfile group 6 (‘/home/oradata/powerdes/redo_dg_023.log‘) size 20M;
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
select * from v$logfile order by 1;
2.3 准备参数文件
2.3.1 生成pfile
create pfile from spfile;
shutdown immediate
2.3.2 修改pfile
cp $ORACLE_HOME/dbs/initpowerdes.ora $ORACLE_HOME/dbs/initpowerdes.ora.bak
vim $ORACLE_HOME/dbs/initpowerdes.ora
*.db_unique_name=pdunq
*.diagnostic_dest=‘/oracle/app/oracle‘
*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=powerdesXDB)‘
*.fal_client=‘pdunq‘
*.fal_server=‘pdunq_dg‘
*.standby_file_management=‘AUTO‘
*.db_file_name_convert=‘/home/oradata/powerdes‘,‘/home/oradata/pwerdes‘
*.log_file_name_convert=‘/home/oradata/powerdes‘,‘/home/oradata/powerdes‘
*.log_archive_config=‘DG_CONFIG=(pdunq,pdunq_dg)‘
*.log_archive_dest_2=‘SERVICE=pdunq_dg lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg‘
*.log_archive_dest_state_2=‘ENABLE‘
2.3.3 生成spfile
create spfile from pfile;
startup #这里可以启动也可以不启动，这里不启动，后面就要记得startup;让新的参数文件生效
2.4 修改监听文件
[ admin]$ vim listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = powerdes)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.217)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
ADR_BASE_LISTENER = /oracle/app/oracle
INBOUND_CONNECT_TIMEOUT_listener=10
2.5，修改tns配置文件
[ admin]$ vim tnsnames.ora
PD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.217)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdunq)
)
)
SC_SID =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.218)(PORT = 1521))
)
(CONNECT_DATA =
(SID = powerdes)
(SERVER = DEDICATED)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
2.6 监听服务重启
lsnrctl stop
lsnrctl start
2.7 primary上配置最大可用模式：
SQL>startup
SQL>alter database set standby database to maximize availability;
2.8 备份数据库
backup database plus archivelog;
backup current controlfile for standby;
exit;
备份结束后会在闪回区产生备份文件
3，数据库配置 standby上
3.1 建立相应的文件目录
包括dump文件目录，数据文件目录，通过show parameter dest;查看，保持和primary一样的路径地址
3.2 从primary上copy数据文件到standby上
在主库上执行：
ps：在primary上执行
copy闪回区内容
copy闪回文件
cd /oracle/app/oracle/flash_recovery_area/
scp -r ./* 192.168.121.218:/oracle/app/oracle/flash_recovery_area/
copy参数文件
cd /oracle/app/oracle/product/11.2.0/dbhome_1/dbs
scp -r ./* 192.168.121.218:/oracle/app/oracle/product/11.2.0/dbhome_1/dbs
copy监听文件
cd /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/
scp -r ./* 192.168.121.218:/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/
3.3 在standby库 修改配置文件 在standby上修改
[ admin]$ vim listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = powerdes)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.218)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
在standby修改tns文件
3.4，修改参数文件
*.db_unique_name=‘pdunq_dg‘
*.diagnostic_dest=‘/oracle/app/oracle‘
*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=powerdes)‘
*.fal_client=‘pdunq‘
*.fal_server=‘pdunq_dg‘
*.standby_file_management=‘AUTO‘
*.db_file_name_convert=‘/home/oradata/powerdes‘,‘/home/oradata/powerdes‘
*.log_file_name_convert=‘/home/oradata/powerdes‘,‘/home/oradata/powerdes‘
*.log_archive_config=‘DG_CONFIG=(pdunq,pdunq_dg)‘
*.log_archive_dest_2=‘SERVICE=pdunq_dg lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg‘
*.log_archive_dest_state_2=‘ENABLE‘
PS:将*.log_archive_dest_2=后面的DB_UNIQUE_NAME改成primary的DB_UNIQUE_NAME值改为pdunq，这样在做switchover的时候，新的primary能通过这个将redo日志传到新的standby上面去。
log_archive_dest_N 目的是告诉数据库，把归档放到那里去可选项，首先是本地，然后考虑远程的从库，所以，假设A是主库，B是从库，切换之后B是主库，A是从库，所以，log_archive_dest_N需要设置为对方
3.5，重启监听 standby
[ dbs]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-FEB-2015 15:41:36
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.121.218)(PORT=1521)))
The command completed successfully
[ dbs]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-FEB-2015 15:41:41
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/powerlong5/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.121.218)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.121.218)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 10-FEB-2015 15:41:41
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/powerlong5/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.121.218)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "powerdes" has 1 instance(s).
Instance "powerdes", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[ dbs]$
3.6，恢复数据库
在standby库上操作
[ admin]$ rman target sys/ auxiliary /
Argument Value Description
-----------------------------------------------------------------------------
target quoted-string connect-string for target database
catalog quoted-string connect-string for recovery catalog
nocatalog none if specified, then no recovery catalog
cmdfile quoted-string name of input command file
log quoted-string name of output message log file
trace quoted-string name of output debugging message log file
append none if specified, log is opened in append mode
debug optional-args activate debugging
msgno none show RMAN-nnnn prefix for all messages
send quoted-string send a command to the media manager
pipe string building block for pipe names
timeout integer number of seconds to wait for pipe input
checksyntax none check the command file for syntax errors
-----------------------------------------------------------------------------
Both single and double quotes (‘ or ") are accepted for a quoted-string.
Quotes are not required unless the string contains embedded white-space.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00552: syntax error in command line arguments
RMAN-01009: syntax error: found "end-of-file": expecting one of: "double-quoted-string, identifier, single-quoted-string, "
RMAN-01007: at line 0 column 0 file: command line arguments