oracledataguardbroker如何配置？主库环境对比充分利用主数据库原来环境，仅量不对主库参数配置做过多的

主库环境对比充分利用主数据库原来环境，仅量不对主库参数配置做过多的修改。

重新创建口令文件

su - oracle $ orapwd file='/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl' password=oracle entries=10 force=y

修改配置lisener监听文件说明：添加dgmgrl静态监听配置，为后面的dg broker配置打基础。

$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

LISTENER = (DESCRIPTION_LIST =

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.200)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )

SID_LIST_LISTENER = (SID_LIST =

(SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = orcl) ) (SID_DESC = (GLOBAL_DBNAME = orcl_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = orcl) ) )

ADR_BASE_LISTENER = /u01/app/oracle

其中的GLOBAL_DBNAME具有固定的格式：<db_unique_name>_DGMGRL.<db_domain>。修改配置tnsname.ora文件说明：ORCL是主库的服务名，DG是备库的服务名。

$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

ORCL = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.200)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )

SLAVE = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.150)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = slave) ) )

修改配置成规档模式 1)、检查数据库是否处于归档状态

SQL> archive log list; SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter database flashback on; SQL> alter database open;

2)、将主库设置为 FORCE LOGGING 模式

SQL> alter database force logging; SQL> select force_logging,flashback_on from v$database;

FOR FLASHBACK_ON

YES YES

修改主库参数文件 SQL> alter system set instance_name='orcl' scope=spfile; alter system set db_unique_name='orcl' scope=spfile; alter system set local_listener='orcl' scope=spfile; alter system set log_archive_config='DG_CONFIG=(orcl,slave)'; alter system set log_archive_dest_1='LOCATION=/u01/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=spfile; alter system set log_archive_dest_2='SERVICE=slave lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=slave' scope=spfile; alter system set log_archive_format='arch_%r_%t_%s.arc' scope=spfile; alter system set fal_client='orcl' scope=spfile; alter system set fal_server='slave' scope=spfile; alter system set standby_file_management=AUTO; alter database add standby logfile group 4 '/u01/app/oracle/oradata/orcl/standby_redo04.log' size 50M; alter database add standby logfile group 5 '/u01/app/oracle/oradata/orcl/standby_redo05.log' size 50M; alter database add standby logfile group 6 '/u01/app/oracle/oradata/orcl/standby_redo06.log' size 50M; alter database add standby logfile group 7 '/u01/app/oracle/oradata/orcl/standby_redo07.log' size 50M;

SQL> shutdown immediate; SQL> startup;

三、备库配置

备库环境操作系统版本 : OEL5.8 x64 数据库版本 : Oracle 11.2.0.3 x64 （只安装oracle数据库软件,no netca dbca）数据库名 : slave 数据库SID : slave db_unique_name: slave instance_name : slave DGMGRL : slave_DGMGRL

修改配置lisener监听文件 $ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

LISTENER = (DESCRIPTION_LIST =

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.150)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )

SID_LIST_LISTENER = (SID_LIST =

(SID_DESC = (GLOBAL_DBNAME = slave) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = slave) ) (SID_DESC = (GLOBAL_DBNAME = slave_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = slave) ) )

ADR_BASE_LISTENER = /u01/app/oracle

其中的GLOBAL_DBNAME具有固定的格式：<db_unique_name>_DGMGRL.<db_domain>。

修改配置tnsname.ora文件说明：ORCL是主库的服务名，DG是备库的服务名。

$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

ORCL = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.200)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )

SLAVE = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.150)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = slave) ) )

测试服务名连通性：

tnsping orcl tnsping slave

创建11g数据库基本目录 su - oracle mkdir -p /u01/app/oracle/admin/slave/{adump,dpdump,pfile,scripts} mkdir -p /u01/app/oracle/oradata/slave mkdir -p /u01/app/oracle/fast_recovery_area/slave mkdir -p /u01/archivelog

拷贝主库口令文件并改名注：10g DG环境只要求密码相同，11g DG则要求与主库完全一致。否则报无权限错误。

$cd /u01/app/oracle/product/11.2.0/db_1/dbs/$ $ORACLE_HOME/dbs/$ mv orapworcl orapwslave

测试远程登录

$sqlplus as sysdba;$ sqlplus as sysdba;

启动到nomount状态如果相对electron有更多直观理解的, 也可以参考其格式如下:

www.pizei.com

$echo 'db_name=slave' >$ ORACLE_HOME/dbs/initslave.ora $ sqlplus /nolog SQL> conn / as sysdba; SQL> startup nomount;

四、开始在RMAN duplicate数据库

RMAN同进连接主库与备库 $ rman target auxiliary

已连接到目标数据库: ORCL (DBID=1351417842) 已连接到辅助数据库: SLAVE (未装载)

开始duplicate数据库 RMAN>

run { allocate channel c1 type disk; allocate channel c2 type disk; allocate auxiliary channel stby type disk; duplicate target database for standby nofilenamecheck from active database dorecover spfile parameter_value_convert 'orcl','slave' set instance_name='slave' set db_unique_name='slave' set local_listener='slave' set db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/slave/' set log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/slave/' set control_files='/u01/app/oracle/oradata/slave/control01.ctl','/u01/app/oracle/oradata/slave/control02.ctl','/u01/app/oracle/oradata/slave/control03.ctl' set log_archive_dest_1='LOCATION=/u01/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=slave' set log_archive_dest_2='SERVICE=orcl lgwr async valid_for=(online_logfile,primary_role) db_unique_name=orcl' set log_archive_max_processes='5' set standby_file_management='AUTO' set fal_client='slave' set fal_server='orcl'; release channel c1; release channel c2; release channel stby; }

RMAN> quit 恢复管理器完成。

查看备库状态说明：duplicate数据库之后，备库只是处于mount状态，查看页游库状态。

$ sqlplus / as sysdba

查看备库状态 SQL> select open_mode,database_role,db_unique_name from v$database;

OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

MOUNTED PHYSICAL STANDBY slave

将备库置与应用日志模式状态 SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.

验证物理备库日志应用 1）主库上操作

SQL> conn / as sysdba; SQL> create user abc identified by abc ; SQL> grant dba to abc; SQL> conn abc/abc SQL> create tablwww.pizei.come abc ( id integer , name char(10)); SQL> insert into abc values ( 0 , 'aaa' ); SQL> commit;

SQL> conn / as sysdba;

SQL> archive log list; 数据库日志模式存档模式自动存档启用存档终点 /u01/archivelog/ 最早的联机日志序列 8 下一个存档日志序列 10 当前日志序列 10

2）备库上验证

SQL> archive log list 数据库日志模式存档模式自动存档启用存档终点 /u01/archivelog/ 最早的联机日志序列 9 下一个存档日志序列 0 当前日志序列 10

SQL> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;

SEQUENCE# FIRST_TIME NEXT_TIME APPLIED

 7 16-8月 -13     16-8月 -13     YES
 8 16-8月 -13     16-8月 -13     YES
 9 16-8月 -13     16-8月 -13     IN-MEMORY

经过测试，Oracle 11g dataguard物理备库创建成功。

将备库置于Active DataGuard模式

在Oracle 11g之前，物理备库（physical Standby）在应用redo的时候，数据库需要处于mount状态。从11g开始，应用redo的时候，物理备库可以处于read-only模式，这就称为Active Data Guard，这种状态可以实现实时查询功能。

备库上操作

查看备库当前状态 mount

SQL> select open_mode,database_role,db_unique_name from v$database;

OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

MOUNTED PHYSICAL STANDBY slave

取消备库的自动恢复

SQL> alter database recover managed standby database cancel; 数据库已更改。

OPEN备库为只读模式（Dataguard只能启动到readonly模式）

SQL> alter database open; 数据库已更改。

SQL> select open_mode,database_role,db_unique_name from v$database;

OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

READ ONLY PHYSICAL STANDBY slave

4）打开实时应用状态模式

SQL> alter database recover managed standby database using current logfile disconnect; 数据库已更改。

SQL> select open_mode,database_role,db_unique_name from v$database;

OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME