服务器环境

virtualbox搭建的两台虚拟机，操作系统为Windows Server 2008 R2

数据库为Oracle 11g

配置前准备

主库ip为192.168.0.17

备库ip为192.168.0.47

在安装oracle时，主库安装数据库，备库只安装软件（网上大多数都是这样说的，而我当时两台机器貌似已经都装上了oracle数据库，不过也没关系，只是后面配置路径的地方需要按照实际的安装之后的路径配置）

oracle安装的路径在D盘，具体安装路径是D:\app\Administrator\

实例名是orcl

数据文件放在D:\app\Administrator\oradata\orcl文件夹下，归档日志文件放在D:\app\interlib\log文件夹下，interlib文件夹是自己创建的

配置过程

主库的配置

在主库上启动数据库到mount模式，开启归档模式与force logging

sqlplus / as sysdba

shutdown immediate;

startup mount;

alter database archivelog;

alter database force logging;

查看当前日志文件组

select * from v$logfile order by group#;

ORACLE 11G DATA GUARD配置之配置过程

为备用库创建日志文件

alter database add standby logfile group 4 ('d:\app\Administrator\oradata\orcl') size 50m;
alter database add standby logfile group 5 ('d:\app\Administrator\oradata\orcl') size 50m;
alter database add standby logfile group 6 ('d:\app\Administrator\oradata\orcl') size 50m;

再次查询v$logfile

ORACLE 11G DATA GUARD配置之配置过程

会多出三组日志文件，这样备用日志就建好了

创建standby控制文件

alter database create standby controlfile as ‘d:\app\interlib\standby.ctl’;

创建参数文件

create pfile='d:\app\interlib\initora.ora' from spfile;

编辑initora.ora参数文件

orcl.__db_cache_size=310378496
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=310378496
orcl.__sga_target=528482304
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=201326592
orcl.__streams_pool_size=0
*.audit_file_dest='D:\app\Administrator\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\app\Administrator\oradata\orcl\control01.ctl','D:\app\Administrator\flash_recovery_area\orcl\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='D:\app\Administrator\flash_recovery_area'
*.db_recovery_file_dest_size=4102029312
*.diagnostic_dest='D:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'


#下面这部分是要添加的
*.db_unique_name='primary'
*.archive_lag_target=1800
*.fal_client='standby'
*.fal_server='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='LOCATION=D:\app\interlib\log\ VALID_FOR=(all_logfiles,all_roles) db_unique_name=primary'
*.log_archive_dest_2='service=standby arch async valid_for=(online_logfiles,primary_role) db_unique_name=standby'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.DB_FILE_NAME_CONVERT='D:\app\Administrator\oradata\orcl\','D:\app\Administrator\oradata\orcl\'
*.LOG_FILE_NAME_CONVERT='D:\app\interlib\log\','D:\app\interlib\log\'
*.standby_file_management='auto'

使用修改过的参数文件启动主库

startup pfile='d:\app\interlib\initora.ora' nomount;
create spfile from pfile='d:\app\interlib\initora.ora';
shutdown immediate;
startup;

创建密码文件

密码文件存放于D:\app\Administrator\product\11.2.0\dbhome_1\database目录,文件名为PWDorcl.ora(orcl为实例名)

ORACLE 11G DATA GUARD配置之配置过程

如果目录下找不到，则手动创建，创建命令为：

orapwd file=D:\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcl.ora password=123 entries=10

配置主库网络监听(备用库同理)

文件位置为D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora

修改listener.ora文件

加粗的是要添加和修改的内容

# listener.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
#在原来的基础上添加SID_DESC
    (SID_DESC = 
    (GLOBAL_DBNAME = orcl)
    (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
    (SID_NAME = orcl)
    )
  )
#添加一个LISTENER
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.17)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = D:\app\Administrator

修改tnsname.ora文件

# tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

#添加一个PRIMARY
PRIMARY = 
    (DESCRIPTION = 
    (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.17) (PORT = 1521))
    )
    (CONNECT_DATA = 
        (SERVICE_NAME = orcl)
    )
    )
#添加一个STANDBY
STANDBY= 
    (DESCRIPTION = 
    (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.47) (PORT = 1521))
    )
    (CONNECT_DATA = 
        (SERVICE_NAME = orcl)
    )
    )

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

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-QEDP6N8PVGK)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

测试监听

alter database open;
lsnrctl stop;
lsnrctl start;
sqlplus username/pwd@primary as sysdba

如果不能连接，请检查防火墙

关闭主数据库

shutdown immedite;

为了进行数据冷拷贝

备库建立文件夹

a. 将D:\app\Administrator目录下的admin,cfgtollogs,diag,flash_recover_area目录以及密码文件拷贝到备用库的相同路径，备库已经有的文件和文件夹直接覆盖掉吧
b. 将主库的listener.ora和tnsname.ora拷贝到备库相同路径
c. 将D:\app\interlib拷贝到备库相同路径
d. 将standby.ctl文件拷贝到备库D:\app\Administrator\oradata\orcl\和D:\app\Administrator\flash_recovery_area\orcl\下，并复制为control01.ctl,control02.ctl,control03.ctl
e. 主库和备库创建D:\app\interlib\tmp文件夹

修改备库listener.ora文件的Ip为备库Ip

ORACLE 11G DATA GUARD配置之配置过程

备库新建实例(如果备库也安装了数据库，实例也是orcl，这步可以不用管)

在备库上注册oracle实例到服务中，具体命令如下：

oradim -new -sid orcl(实例名)

ORACLE 11G DATA GUARD配置之配置过程

如果已经装了数据库，执行的话也会提示这个实例已经存在，接着继续下面的就可以

备库启动监听

lsnrctl start

修改备库参数文件

将刚才从主库拷贝过来的initora.ora作下面的修改：

orcl.__db_cache_size=310378496
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=310378496
orcl.__sga_target=528482304
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=201326592
orcl.__streams_pool_size=0
*.audit_file_dest='D:\app\Administrator\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\app\Administrator\oradata\orcl\control01.ctl','D:\app\Administrator\flash_recovery_area\orcl\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='D:\app\Administrator\flash_recovery_area'
*.db_recovery_file_dest_size=4102029312
*.diagnostic_dest='D:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

#下面是要修改的地方
*.db_unique_name='standby'
*.archive_lag_target=1800
*.fal_client='primary'
*.fal_server='standby'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='LOCATION=D:\app\interlib\log\ VALID_FOR=(all_logfiles,all_roles) db_unique_name=standby'
*.log_archive_dest_2='service=primary arch async valid_for=(online_logfiles,primary_role) db_unique_name=primary'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.DB_FILE_NAME_CONVERT='D:\app\Administrator\oradata\orcl\','D:\app\Administrator\oradata\orcl\'
*.LOG_FILE_NAME_CONVERT='D:\app\interlib\log\','D:\app\interlib\log\'
*.standby_file_management='auto'

用参数文件建立实例

startup nomount pfile=’d:\app\interlib\initora.ora’;
create spfile from pfile=’d:app\interlib\initora.ora’;
shutdown immediate;
startup nomount

使用Rman复制主库

主库startup

在cmd中执行

rman target /

RMAN> backup full database format='D:\app\interlib\tmp\FOR_STANDBY_%u%p%s,RMN' include current controlfile for standby;

上面执行完后，将当前archivelog归档，执行

RMAN> sql'alter system archive log current';

将在D:\app\interlib\tmp\下产生的的备份集拷贝到备库的相同路径下

复制数据库

继续在刚刚的RMAN中执行

RMAN> connect auxiliary sys/sys@standby

RMAN> duplicate target database for standby nofilenamecheck;

备库启动standby

sqlplus / as sysdba

alter database mount standby database;

alter database recover managed standby database disconnect from session;

做完上面的配置，dataguard环境就搭建好了

dataguard验证

主库和备库分别执行

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

如果查看从库可以看到归档日志,表示归档日志同步成功,如果APPLIED列的值为yes,表示重做应用成功.

然后可以手动切换归档日志

alter system switch logfile;

主备库再执行

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

看新增的归档日志是否同步，并且重做应用。

配置过程中遇到的问题

在按照网上的步骤做dataguard配置时，出现了一些问题，并通过查找也解决了，这里总结下

============dataguard遇到的问题==============

执行这句时alter database mount standby database;

报错：ORA-02232:??? MOUNT ??

查了下官网：

ORA-02232
invalid MOUNT mode
Section: 02140-02299 SQL Parsing Messages
Notes: This section lists some of the messages generated when SQL statements are parsed by the Oracle Server.
Cause: A mode other than SHARED or EXCLUSIVE follows the keyword MOUNT in an ALTER DATABASE statement.
Action: Specify either SHARED, EXCLUSIVE, or nothing following the keyword MOUNT in the ALTER DATABASE statement and try again.

解决：按步骤把主库生成的standby.ctl控制文件拷贝到initora.ora中控制文件指定的地方，再重新
使用initora.ora nomount启动数据库即可

执行SELECT SEQUENCE#,APPLIED FROM VARCHIVED_LOG ORDER BY SEQUENCE#;
发现备库没有记录，也就是没有同步主库的日志文件过来
而且在主库select switchover_status from vdatabase;的结果是failed的
而在备库是to primary 也就是正常的
然后想了下是不是网络的原因然后互相ping了下
果然，发现15可以ping通17，但是17ping不通15，
然后直接在17上配了下网络，不自动获取，直接写上ip地址和原来的默认网关
这样17就可以ping通15了，然后
在备库再执行SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
就看到归档日志文件被同步过来了~！

虽然归档日志文件可以同步了，但是又发现，applied都是no，
后来通过另外一个教程，在配置好备库之后，按照http://www.docin.com/p-453570668.html
用rman备份主库的数据库之后，备库再用initora.ora启动数据库，并且备库执行
alter database mount standby database;
alter database revover managed standby database disconnect from session；
就可以了，applied就变成yes了

按上面的弄完之后想试下在主库创建一个测试表插入一条数据，再在备库看下会不会同步数据过去，

主库创建了表和数据之后再执行

alter system switch logfile; 主库写盘才会触发MRP进程

然后在备库执行SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;也能看到新的归档日志
文件被同步过来，而且也是被应用了的applied为yes，

然后执行select * from dg_test，报错

ORA-10456: cannot open standby database; media recovery session may be in progress

后来找到解决办法：http://blog.csdn.net/sustwct/article/details/12855745

先alter database recover managed standby database cancel;

然后alter database open;

最后alter database recover managed standby database using current logfile disconnect ;

秒客网

ORACLE 11G DATA GUARD配置之配置过程

服务器环境

配置前准备

配置过程

主库的配置

查看当前日志文件组

为备用库创建日志文件

创建standby控制文件

创建参数文件

编辑initora.ora参数文件

使用修改过的参数文件启动主库

创建密码文件

配置主库网络监听(备用库同理)

测试监听

关闭主数据库

备库建立文件夹

备库新建实例(如果备库也安装了数据库，实例也是orcl，这步可以不用管)

备库启动监听

修改备库参数文件

用参数文件建立实例

使用Rman复制主库

将在D:\app\interlib\tmp\下产生的的备份集拷贝到备库的相同路径下

复制数据库

备库启动standby

dataguard验证

配置过程中遇到的问题

相关文章