本文参考了网上几篇文章,然后经过修改,测试成功。
一、 环境
Windows20008 server
Oracle 11.2.0.1
主机:192.168.70.170(primary), 实例名:dbtest
备机:192.168.70.164(standby),不带实例
primary 和 standby 是数据库唯一名。不是机器名。
二、 准备条件
分别在primary上安装oracle 和数据库,standby安装oracle不带实例;安装路径、数据库实例名(dbtest)和sys和其他用户的密码设置为admin
三、 Primary操作
1、 设置主数据库为force logging 模式
SQL>sqlplus"/as sysdba"
SQL>alterdatabase force logging;
2、 设置主数据库为归档模式,并以mount 启动数据库
SQL>archivelog list
SQL>shutdownimmediate
SQL>startupmount
SQL>alterdatabase archivelog;
SQL>archivelog list
3、 添加"备用联机日志文件"
SQL>select* from v$logfile;
再添加:
alterdatabase add standby logfile group 4 (' E:\app\Administrator \ORADATA\DBTEST\redo04.log')size 50m;
alterdatabase add standby logfile group 5 ('E:\app\Administrator \ORADATA\DBTEST\redo05.log') size 50m;
alterdatabase add standby logfile group 6 (' E:\app\Administrator \ORADATA\DBTEST\redo06.log')size 50m;
alterdatabase add standby logfile group 7 (' E:\app\Administrator \ORADATA\DBTEST\redo07.log')size 50m;
4、 创建主库的初始化参数给备库用
SQL>Createpfile from spfile;
产生的文件名为initdbtest.ora 存放目录默认放在$ORACLE_HOME/database下
5、 在主库创建监听和配置tnsnams.ora
listener.ora配置如下:
SID_LIST_LISTENER中添加红色部分:这里要特别注意了
红色部分是添加的内容
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME =E:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS ="EXTPROC_DLLS=ONLY:E:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC=
(GLOBAL_DBNAME= dbtest)
(ORACLE_HOME= E:\app\Administrator\product\11.2.0\dbhome_1)
(SID_NAME= dbtest)
)
)
LISTENER=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =WIN-32I7T8HE0VE)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))
)
)
ADR_BASE_LISTENER= E:\app\Administrator
tnsnames.ora配置如下:
添加:
primary=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.70.170)(PORT= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbtest)
)
)
standby=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.70.164)(PORT= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbtest)
)
)
修改完成后重启监听:
lsnrctl stop
lsnrctl start
6、 在initdbtest.ora中添加以下内容:
*.log_archive_format='%T%S%r.ARC'
*.DB_UNIQUE_NAME='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=E:\app\Administrator\oradata\archVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
*.log_archive_dest_2='SERVICE=standby LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='standby'
*.FAL_CLIENT='primary'
接下来关闭数据库,并启动,让修改生效
SQL> shutdown immediate
SQL> create spfile from pfile;
SQL> startup
7、 用Rman备份,不用停机
$rman target /
RMAN>backupfull format 'E:/FULL_%d_%T_%s.bak' database include current controlfile forstandby;
RMAN>sql'alter system archive log current';
RMAN>BackupArchiveLog all format='E:/arch_%d_%T_%s.bak';
备份完后将3个备份文件拷到standby上同样的目录,强调:同样的目录(E盘),以便在standby进行rman恢复
四、 Standby操作
对于备机因为只安装软件,没有创建数据库实例。因此在进行下面操作前需要首先创建同名的空闲实例
oradim -new -sid dbtest
之后即可使用:
cmd> set oracle_sid=dbtest
SQL>sqlplus / as sysdba来连接到这个空闲实例。
其中cmd表示command窗体下的命令行
拷贝闪回区内容
1、 拷贝闪回区内容
将主机的闪回目录 e:\app\Administrator\flash_recovery_area下所有内容拷贝到备机
e:\app\Administrator\flash_recovery_area下,注意备机此时还没有flash_recovery_area目录
2、 拷贝配置文件
拷贝主机E:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\ listener.ora 和
tnsnames.ora 到备机相应的位置下。
并且修改listener.ora文件中的IP地址为备机IP,如下标红部分,其余不变
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.70.164)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
且修改tnsnames.ora文件中的IP地址,如下标红部分,其余不变
DBTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.70.164)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbtest)
)
)
修改完成后重启监听:
lsnrctl stop
lsnrctl start
3、 拷贝initdbtest.ora和密码文件到备机并修改initdbtest.ora
E:\app\Administrator\product\11.2.0\dbhome_1\database\initdbtest.ora
E:\app\Administrator\product\11.2.0\dbhome_1\database\PWDdbtest.ora
然后编辑$ORACLE_HOME/database目录下的initdbtest.ora相应部分为以下内容
*.log_archive_format='%T%S%r.ARC'
*.DB_UNIQUE_NAME='standby'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=E:\app\Administrator\oradata\archVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.log_archive_dest_2='SERVICE=primary LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'
4、 手动创建下面的目录
E:\app\Administrator\oradata\dbtest
5、 启动备用数据库
SQL>startup nomount;
SQL>create spfile from pfile;
SQL>shutdown immediate;
SQL>starup nomount;
6、 用Rman还原数据库
Cmd> set oracle_sid=dbtest
cmd>rman target sys/admin@primary auxiliary /
此步骤如果报下面的错误:
C:\Users\Administrator>rman target sys/admin@primary auxiliary/
恢复管理器: Release 11.2.0.1.0 -Production on星期四 4月 716:05:56 2016
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
RMAN-00571:===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS===============
RMAN-00571:===========================================================
RMAN-00554: 内部恢复管理器程序包初始化失败
RMAN-04005: 目标数据库中存在错误:
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务
那么就是前面备端listener.ora配置有问题 ,或者是主机端没有重启监听
rman target sys/admin@primary auxiliary /这个命令的含义是连接2个目标,一个为primary,一个为本备机的dbtest
可通过在主机端上执行rman target sys/admin@primary,在备机上执行rman target /来定位分析问题。只有2个目标都成功连接,上述命令才会成功。
恢复数据:
RMAN>duplicatetarget database for standby dorecover nofilenamecheck;
数据恢复完成后,执行下面的命令,准备测试
SQL>shutdownimmediate;
SQL>startupnomount;
SQL>alter database mount standby database;
SQL>alter database add standby logfile;
SQL>alter database add standby logfile;
SQL>alter database add standby logfile;
SQL>alter database add standby logfile;
SQL>alter database recover managed standby database using current logfile disconnect from session;
五、 测试
1、 在主库:
SQL>alter system switch logfile;
SQL>select max(sequence#) from v$archived_log;
2、 在备库检查日志是否和主库一致
SQL>select max(sequence#) from v$archived_log;
注:主备查询结果一致,Data Guard 搭建结束。