在 rhel5 上配置 Oracle 10g Data Guard
环境配置
主库SID(ddd)
Global Database Name(ddd)
Service Name(ddd)
Net Server Name(ddd)
IP：192.168.0.102
DB：Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
OS：rhel5
备库SID(ddd)
Global Database Name(ddd)
Service Name(ddd)
Net Service Name(dddsby)
IP：192.168.0.109
DB：Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
OS：rhel5
Primary为正在使用的生产数据库，standby安装oracle软件，但不建立数据库。
二、 建立物理备用数据库
1． 准备主库的oracle环境：
编辑oracle用户的~oracle/.bash_profile文件，oracle相关环境变量如下：
export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/product/10g
export ORACLE_SID=ddd
export PATH=$ORACLE_HOME/bin
PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LANG=zh_CN
#export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_LANG="SIMPLIFIED Chinese_CHINA.ZHS16GBK"
2． 修改主库为归档模式
建立归档目录：
mkdir -p /oracle/oradata/arch
修改归档模式：
alter database force logging;
archive log list;
create pfile from spfile;
编辑$ORACLE_HOME/dbs/initddd.ora
主库文件配置如下
红色部分为要修改的参数)
ddd.__db_cache_size=167772160
ddd.__java_pool_size=4194304
ddd.__large_pool_size=4194304
ddd.__shared_pool_size=83886080
ddd.__streams_pool_size=0
*.audit_file_dest='/oracle//admin/ddd/adump'
*.background_dump_dest='/oracle//admin/ddd/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle//oradata/ddd/control01.ctl','/oracle//oradata/ddd/control02.ctl','/oracle//oradata/ddd/control03.ctl'
*.core_dump_dest='/oracle//admin/ddd/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ddd'
*.db_recovery_file_dest='/oracle//flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dddXDB)'
*.fal_client='ddd' #这里的fal_client指的是net service name
*.fal_server='dddsby1' #这里的fal_server指的是net service name
*.job_queue_processes=10
*.log_archive_dest_1='location=/oracle/oradata/arch'
*.log_archive_dest_2='SERVICE=dddsby1 LGWR' #这里的SERVICE指的是net service name
*.open_cursors=300
*.pga_aggregate_target=88080384
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=264241152
*.standby_archive_dest='/oracle/oradata/arch'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle//admin/ddd/udump'
sqlplus / as sysdba;
shutdown immediate;
create spfile from pfile;
startup mount;
alter database archivelog;
alter database open;
3. 对主数据库做一次完整热备份，获得备用数据库数据
RMAN>connect target
RMAN> backup database format='/home/oracle/%U_%s.bak';
RMAN> sql "Alter System Archive Log Current";
RMAN> Backup filesperset 10 ArchiveLog all format='/home/oracle/%U_%s.bak';
cd /home/oracle
scp *.bak 192.168.200.109:/home/oracle/
或者直接打包数据库目录oradata,用ftp传到备库中在解包
4. 在standby服务器准备环境与primary相同
编辑oracle用户的~oracle/.bash_profile文件，oracle相关环境变量如下：
export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/product/10g
export ORACLE_SID=ddd
export PATH=$ORACLE_HOME/bin
PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LANG=zh_CN
#export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_LANG="SIMPLIFIED Chinese_CHINA.ZHS16GBK"
5. 准备相应目录，如日志文件路径，归档路径，参数文件路径，数据文件准备存放路径等
mkdir -p /oracle/oradata/ddd
mkdir -p /oracle/oradata/arch
mkdir -p $ORACLE_BASE/admin/ddd/adump
mkdir -p $ORACLE_BASE/admin/ddd/bdump
mkdir -p $ORACLE_BASE/admin/ddd/cdump
mkdir -p $ORACLE_BASE/admin/ddd/udump
6. 建立备用数据库参数文件
ddd.__db_cache_size=167772160
ddd.__java_pool_size=4194304
ddd.__large_pool_size=4194304
ddd.__shared_pool_size=83886080
ddd.__streams_pool_size=0
*.audit_file_dest='/oracle//admin/ddd/adump'
*.background_dump_dest='/oracle//admin/ddd/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle//oradata/ddd/control01.ctl','/oracle//oradata/ddd/control02.ctl','/oracle//oradata/ddd/control03.ctl'
*.core_dump_dest='/oracle//admin/ddd/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ddd'
*.db_recovery_file_dest='/oracle//flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dddXDB)'
*.fal_client='dddsby1' #这里的fal_client指的是net service name
*.fal_server='ddd' #这里的fal_server指的是net service name
*.job_queue_processes=10
*.log_archive_dest_1='location=/oracle/oradata/arch'
*.log_archive_dest_2='SERVICE=ddd LGWR' #这里的SERVICE指的是net service name
*.open_cursors=300
*.pga_aggregate_target=88080384
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=264241152
*.standby_archive_dest='/oracle/oradata/arch'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle//admin/ddd/udump'
7. 从主服务器拷贝口令文件到备用服务器
cd $ORACLE_HOME/dbs/
scp orapwddd 192.168.200.109:/oracle/product/10g/dbs
8. 配置网络连接
修改主服务器的
/oracle/product/10g/network/admin/listener.ora文件如下：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = ddd)
(ORACLE_HOME = /oracle/product/10g)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gull)(PORT = 1521))
)
修改主服务器的
/oracle/product/10g/network/admin/tnsnames.ora文件如下：
DDDSBY1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.109)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ddd)
)
)
DDD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ddd)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC1))
)
(CONNECT_DATA =
(SID = ddd)
(PRESENTATION = RO)
)
)
修改备用服务器的
/oracle/product/10g/network/admin/listener.ora文件如下：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/10g)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
修改备用服务器的
/oracle/product/10g/network/admin/tnsnames.ora文件如下：
DDDSBY1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.109)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ddd)
)
)
DDD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ddd)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = ddd)
(PRESENTATION = RO)
)
)
测试:
启动监听:
lsnrctl start
lsnrctl status 查看监听状态.
在主和备用机上分别执行
tnsping ddd
tnsping dddsby1
9. 在主数据库创建备用服务器控制文件
alter database create standby controlfile as '/home/oracle/standby.ctl';
创建后将控制文件cp(rcp or scp)到备用数据库所在的控制文件目录下。
scp standby.ctl 192.168.200.109:/oracle/oradata/ddd/
cp /oracle/oradata/ddd/standby.ctl /oracle/oradata/ddd/control02.ctl
cp /oracle/oradata/ddd/standby.ctl /oracle/oradata/ddd/control02.ctl
cp /oracle/oradata/ddd/standby.ctl /oracle/oradata/ddd/control03.ctl
10. 启动备用数据库
sqlplus / as sysdba
create spfile from pfile;
startup mount;
恢复数据库:
RMAN> connect target;
RMAN> restore database;
RMAN> restore archivelog all;
RMAN> recover database;
进入到后台管理恢复状态
SQL>alter database recover managed standby database disconnect from session;
三、采用Lgwr进程传递联日志机的最大性能模式
1. 在备用数据库上创建备用日志
alter database recover managed standby database cancel;
alter database add standby LOGFILE GROUP 5 ('/oracle/oradata/ddd/stdy_redo05.log') size 10m;
alter database add standby LOGFILE GROUP 6 ('/oracle/oradata/ddd/stdy_redo06.log') size 10m;
alter database add standby LOGFILE GROUP 7 ('/oracle/oradata/ddd/stdy_redo07.log') size 10m;
alter database add standby LOGFILE GROUP 8 ('/oracle/oradata/ddd/stdy_redo08.log') size 10m;
alter database recover managed standby database disconnect from session;
2. 修改主库的归档路径
alter system set LOG_ARCHIVE_DEST_2='SERVICE=dddsby1 LGWR' scope=both;
另外，如果考虑到以后该库可能被切换到备用数据库，也可以创建同样的备用日志
组:
alter database add standby LOGFILE GROUP 5 ('/oracle/oradata/ddd/stdy_redo05.log') size 10m;
alter database add standby LOGFILE GROUP 6 ('/oracle/oradata/ddd/stdy_redo06.log') size 10m;
alter database add standby LOGFILE GROUP 7 ('/oracle/oradata/ddd/stdy_redo07.log') size 10m;
alter database add standby LOGFILE GROUP 8 ('/oracle/oradata/ddd/stdy_redo08.log') size 10m;
四、验证备用服务器是否工作
在主库中执行
alter system switch logfile;
在备库中查看日志是否被传送过来
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
在主库上：
sqlplus / as sysdba
create user test identified by test;
grant connect,resource to test;
conn test/test@primary;
create table test(name varchar2(20));
insert into test values('Data Guard');
commit;
conn / as sysdba;
alter system switch logfile;
查看从库日志
以只读方式打开从库查看 insert into test values(Data Guard'); 已经生效。
sqlplus / as sysdba;
alter database recover managed standby database cancel;
alter database open read only;
conn test/test
select * from test;
再次设置从库在恢复模式：
alter database recover managed standby database disconnect from session;
五、日常管理
1. 备用服务器的管理模式与只读模式
（1）启动到管理模式
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database recover managed standby database disconnect from session;
（2）启动到只读方式
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database open read only;
（3）如果在管理恢复模式下到只读模式
SQL>recover managed standby database cancel;
SQL>alter database open read only;
这个时候，可以给数据库增加临时数据文件（这个在热备份的时候是没有备份过来的）
如
alter tablespace temp add tempfile '/oracle/oradata/ddd/temp01.dbf' size 100M;
（4）从只读方式到管理恢复方式
SQL>recover managed standby database disconnect from session;
