安装与还原 - Win2008搭建DataGuard - 《Oracle》

一、环境
服务器系统：Windows20008 server
软件版本：Oracle 11.2.0.1
主机：192.168.30.174(primary), 实例名：orcl
备机：192.168.30.113(standby)，不带实例
primary 和 standby 是数据库唯一名。不是机器名。

二、准备条件
分别在primary上安装oracle 和数据库，standby安装oracle不带实例；安装路径、数据库实例名（orcl）和sys和其他用户的密码设置为klym2014

三、 Primary库操作
1、设置主数据库为force logging 模式

C:>sqlplus / as sysdba
SQL:>alter database force logging;

2、设置主数据库为归档模式，并以mount 启动数据库，在dos中执行。

archive log list
shutdown immediate
startup mount
alter database archivelog
archive log list

3、添加”备用联机日志文件”

SQL>select* from  v$logfile;

再添加：

Alter  database add standby logfile group 4 ('  E:\app\Administrator\ORADATA\DBTEST\redo04.log')size 50m;
Alter  database add standby logfile group 5 ('  E:\app\Administrator\ORADATA\DBTEST\redo05.log')size 50m;
Alter  database add standby logfile group 6 ('  E:\app\Administrator\ORADATA\DBTEST\redo06.log')size 50m;
Alter  database add standby logfile group 7 ('  E:\app\Administrator\ORADATA\DBTEST\redo07.log')size 50m;

4、创建主库的初始化参数给备库用

SQL>Create pfile from spfile; (产生的文件名为initorcl.ora 存放目录默认放在$ORACLE_HOME/database下)

5、在主库创建监听listener.ora和配置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= orcl)
       (ORACLE_HOME= E:\app\Administrator\product\11.2.0\dbhome_1)
       (SID_NAME= orcl)
     )
  ) 
LISTENER=
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.30.174)(PORT = 1521))
       (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))
    )
  )
ADR_BASE_LISTENER=  E:\app\Administrator

tnsnames.ora配置如下:

ORACLR_CONNECTION_DATA  =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY =  EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =  192.168.30.175)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
primary=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =  192.168.30.175)(PORT= 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
     )
  )
standby=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =  192.168.30.176)(PORT= 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
     )
  )

修改完成后重启监听：
C:>lsnrctl stop
lsnrctl start

6、在initorcl.ora中添加以下内容：
文件位置：(D:\app\Administrator\product\11.2.0\dbhome_1\database\INITSID_NAME.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\arch VALID_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'

接下来关闭数据库，并启动，让修改生效

shutdown  immediate
create spfile  from pfile;
startup
exit

主库用Rman备份,不用停机
$rman target /
RMAN>backup full format ‘E:/FULL%d%T%s.bak’ database include current controlfile for standby;
RMAN>sql’alter system archive log current’;
RMAN>Backup ArchiveLog all format=’E:/arch%d%T%s.bak’;
备份完后将3个备份文件拷到standby上同样的目录，强调：同样的目录（E盘），以便在standby进行rman恢复

四、Standby库操作
对于备机因为只安装软件,没有创建数据库实例。因此在进行下面操作前需要首先创建同名的空闲实例
cmd> oradim -new -sid orcl（后面不加；）
之后即可使用:
cmd> set oracle_sid=orcl
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.30.113)(PORT = 1521))
     (ADDRESS  = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
 )

修改完成后重启监听：
lsnrctl stop
lsnrctl start

3、拷贝initorcl.ora和密码文件到备机并修改iniorcl.ora
E:\app\Administrator\product\11.2.0\dbhome_1\database\initorcl.ora
E:\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcl.ora

然后编辑$ORACLE_HOME/database目录下的initorcl.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\arch VALID_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\orcl

5、启动备用数据库
SQL>startup nomount;
SQL>create spfile from pfile;
SQL>shutdown immediate;
SQL>starup nomount;

6、用Rman还原主库数据库
cmd> set oracle_sid=orcl
cmd>rman target sys/klym2014@orcl auxiliary /

恢复数据：
RMAN>duplicate target database for standby dorecover nofilenamecheck;

数据恢复完成后，执行下面的命令（进入sqlplus）。

shutdown immediate;
startup nomount;
alter database  mount standby database;
alter database  add standby logfile;
alter database  add standby logfile;
alter database  add standby logfile;
alter database  add standby logfile;
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 搭建结束。