oracle数据库使用dgbroker搭建DG以及一些常见的小问题

来源：互联网发布：java telnet 编辑：程序博客网时间：2024/06/05 12:06

oracle DG：

英文全称dataguard

提供备份和灾难恢复

dg----必须要有两台数据库，一台主一台备，主备可以来回切换

dg功能

1.备份（硬件可以低于主库，软件可以不一致）

2.提供容灾（硬件必须不低于主库，软件路径版本必须一致）

3.读写分离（主库读写对外服务。备库只读）逻辑备库可以读，11g物理备库也可以读

4.可以做测试（快照备库可以读写，测试完了在将其转为物理备库，这时所做的测试操作全部丢失）

dg又分物理备库和逻辑备库（具体可以网上查询，不做详细介绍）

dg介绍

1.日志传输服务：定义主库日志通过什么方式，如何传递给备库

主库日志传递给备库时，使用什么进程去传递，通过lgwr进程传还是arc进程传，主库日志是否和备一致，主库日志和备是否可以有时间差

log_archive_config='dg_config(db_unique_name主send/nosend|receive/noreceive,db_unique_name备send/nosend|receive/noreceive)';

默认主库和备库send+receive即接受有传送

10g一个主库最多9个备库

11g一个主库最多30个备库

log_archive_dest_1='location='/'db_unique_name=pri'

log_archive_dest_2='service=service_name(默认闪回路径) <lgwr sync|arc async|delay 7200s延迟7200s传送|compress压缩归档文件传送>db_unique_name=std'

log_archive_dest_state_1=enable/disable

2.角色转换服务

switchover；自动转换，主库变备库，备库变主库，主库和备库都是正常的。（假如主库要升级，执行switchover将备库变成主库

关闭原来的主库，升级硬件，将原来的主库启动成备库，在执行switchover。）

failover ：故障转换，主库不可用并且短时间无法恢复，这时需要failover切换，直接将备库做主库，原来的主库不在是dg中的成员，

必须重新搭建dg，如果使用的是dgbroker，这时原来的主库要成为备库必须重新实例化。

3.数据保护

a---最大保护模式：可以保证数据0丢失

开启最大保护模式时，当用户在主库进行操做，改操作记录到主库redo中，这时redo必须传递到备库，并且保证至少要有一个备库日志可用

该事务在主库才能完成。如果备库不可用，这时主库就会当机

v$database--protection_mode

b---最大可用性模式

处于最大可用性模式时，主库什么时候转日志，备库就什么时间接收

c---最大性能模式

保证数据库的性能，也保证数据0丢失，如果备库不可使用，主库转为最大可用模式，备库可用，恢复到最大性能模式

dg搭建的方式

1.操作系统命令方式（不做介绍）

2.rman的方式

duplicate克隆技术

3.oem（不做介绍）

搭建dg的前的准备

SID统一为up

主库：db_unique_name=pri 192.168.3.98

从库：db_unique_name=std 192.168.3.99

测试软件 vmware 10

测试环境 centos6.5

建议关闭selinux和iptables

环境变量：最好一致，写到bash_profile或者手动 source都可以

cat db

export ORACLE_BASE=/u01/oracle

export ORACLE_HOME=/u01/11g

export ORACLE_SID=up

export PATH=$ORACLE_HOME/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin

cat asm

export ORACLE_BASE=/u01/oracle

export ORACLE_HOME=/u01/grid

export ORACLE_SID=+ASM

export PATH=$ORACLE_HOME/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin

主库操作：（主库首先需要搭建好oracle库，不会的可以去看我的上一篇博客http://blog.csdn.net/freedompuge/article/details/45028961）

1.主库必须归档

启动到mount状态

alter databse archivelog开启归档

SQL> archive log list; ===》显示enabled表示开启
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10

2.强制日志记录

select force_logging from v$database；

alter database force logging;开启强制记录

3.网络配置

vim listener.ora

# listener.ora Network Configuration File:/u01/11g/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = up)

(ORACLE_HOME = /u01/11g)

(SID_NAME = up)

)

(SID_DESC =

(GLOBAL_DBNAME = pri)

(ORACLE_HOME = /u01/11g)

(SID_NAME = up)

)

(SID_DESC =

(GLOBAL_DBNAME = pri_DGMGRL) ---或者写pri_DGBROKER

(ORACLE_HOME = /u01/11g)

(SID_NAME = up)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.98)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/oracle

vim tnsname.ora-----tnsping ------如果不同可能是防火墙

# tnsnames.ora Network Configuration File:/u01/11g/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PRI =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.98)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pri)

)

STD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.99)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = std)

)

EXTPROC_CONNECTION_DATA=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL =TPC)(KEY = EXTPROCO))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

4.附加日志打开（可做可不做，逻辑备库必做）

alter database {add|drop} supplemental logdata

5.备份

rman全备一次

backup full database include current controlfile for

standby

6.闪回功能（不打开就是物理备库）

alter database flashback off;

7.修改参数

alter system set db_unique_name=pri scope=spfile;

重启数据库，创建pfile

Create pfile=’/……’ from spfile

alter system switch logfile;

8.在备份一次数据库

rman----backup database 在备份一次数据库

9.增加备库日志（备库日志比主库日志多一个）

group#---v$standby_log;---查看备库日志组

alter database add standby logfile group 5 '/u01/......sredo01.log' size 50m;

.....

备库操作：

1.安装一台和主库一模一样的环境

只装软件不建库（这里不详细介绍安装软件了）

2.scp主库密码，参数文件，监听等，拷贝到相应的路径下

scp root@ip:xxxx /...

3.准备目录

grep / /…../init$ORACLE_SID.ora

根据显示的目录，创建路径

mkdir /u01/oracle/oradata/updb

/u01/oracle/fast_recovery_area/updb

/u01/oracle/admin/updb/{a,dp}dump -p

启动到nomount状态创建spfile---create spfile from pfile;

在到nomount状态修改unique_name,在重新启动到nomount

alter system set db_unique_name=std scope=spfile;

4.配置网络

vim listener.ora

# listener.ora Network Configuration File:/u01/11g/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = up)

(ORACLE_HOME = /u01/11g)

(SID_NAME = up)

)

(SID_DESC =

(GLOBAL_DBNAME = std)

(ORACLE_HOME = /u01/11g)

(SID_NAME = up)

)

(SID_DESC =

(GLOBAL_DBNAME = std_DGMGRL) ----std_DGBROKER

(ORACLE_HOME = /u01/11g)

(SID_NAME = up)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.99)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/oracle )

Vim tnsname.ora---与主库一样---防火墙关闭

# tnsnames.ora Network Configuration File:/u01/11g/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PRI =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.98)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pri)

)

STD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.99)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = std)

)

EXTPROC_CONNECTION_DATA=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL =TPC)(KEY = EXTPROCO))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

之后在主库和从库执行 tnsping pri（std）看能否成功或者执行sqlplus sys/oracle@pri(std)

5.备库执行克隆

rman target sys/oracle@pri auxiliarysys/oracle@std

duplicate target database for standbynofilenamecheck from active database;

6.配置dgbroker

show parameter dg_broker_start---------true表示打开

alter system setdg_broker_start=true/false-----主库备库都打开

7.执行dgbroker配置dg

dgmgrl sys/oracle@pri---必须链接主库

CREATE CONFIGURATION dg_ps AS ----随便起名字，不要忘记就行

PRIMARY DATABASE IS pri ----主库名

CONNECT IDENTIFIER IS pri;

add databse std as connect identifier is std maintained as physical

show configuration

enable configuration

enabled失败，查看错误

DGMGRL> show configuration

Configuration - dg_ps

Protection Mode: MaxPerformance
Databases:
pri - Primary database
std - Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

查看告警日志：

Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.6.246)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=std_DGB)(CID=(PROGRAM=oracle)(HOST=oracle2)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 25-MAY-2016 13:52:29
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Wed May 25 13:52:43 2016
ARC0: Standby redo logfile selected for thread 1 sequence 150 for destination LOG_ARCHIVE_DEST_2

tns被拒绝，这个很不科学，既然duplicate都可以，怎么会是tns被拒绝呢？

接着查看：

主库：

DGMGRL> show database verbose pri

Database - pri

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
pu

Properties:
DGConnectIdentifier = 'pri'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'MANUAL'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u01/oracle/oradata/zhphpgg/, /u01/oracle/oradata/pu/'
LogFileNameConvert = 'zhphpgg, pu'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'pu'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PRI_DGMGRL)(INSTANCE_NAME=pu)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS

pri端没有问题

DGMGRL> show database verbose std

Database - std

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
pu
Warning: ORA-16714: the value of property StandbyFileManagement is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting

Database Error(s):
ORA-16766: Redo Apply is stopped

Properties:
DGConnectIdentifier = 'std'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'MANUAL'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'pu'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STD_DGMGRL)(INSTANCE_NAME=pu)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'

Database Status:
ERROR

从库查看dg信息

DGMGRL> show database verbose std
not logged on
DGMGRL> show database verbose pri
not logged on
DGMGRL>

从库的dg信息

DGMGRL> show database verbose std
not logged on
DGMGRL> show database verbose pri
not logged on
DGMGRL>

只有std出现警告，先修改warning的参数

add database std as connect identifier is std maintained as physical;
edit database std set property ArchiveLagTarget='0';
edit database std set property LogArchiveMaxProcesses='4';
edit database std set property LogArchiveMinSucceedDest='1';
edit database std set property LogArchiveTrace='0';
edit database std set property LogArchiveFormat='%t_%s_%r.dbf';
edit database std set property logxptmode=sync
edit database std set property standbyfilemanagement=auto

再次查看：

DGMGRL> show database verbose std;

Database - std

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
pu

Database Error(s):
ORA-16766: Redo Apply is stopped

Properties:
DGConnectIdentifier = 'std'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'auto'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'pu'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STD_DGMGRL)(INSTANCE_NAME=pu)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'

Database Status:
ERROR

这次出现error
ORA-16766: Redo Apply is stopped
再次enabled configuration

监控告警日志，这次没有出现错误

DGMGRL> enabled configuration
Unrecognized command "enabled", try "help"
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration - dg_ps

Protection Mode: MaxPerformance
Databases:
pri - Primary database
std - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

8.测试主库和备库是否同步

alter system switch logfile;

select max(sequence#) from v$archived_log;

9．查看从库的日志文件

备库日志一般比主库多一个组

group#---v$standby_log;---查看备库日志组

alter database add standby logfile group11'/u01/......sredo01.log' size 50m;在主库创建（一般不需要做会自动创建）

alter database add standby logfile group12'/u01/......sredo02.log' size 50m;

alter database add standby logfile group13'/u01/......sredo03.log' size 50m;

alter database add standby logfile group14'/u01/......sredo04.log' size 50m;

alter system dg_broker_start=false--主备一起关

把备库启动到nomount在执行克隆----这种备库可以达到实时同步

10.在主库建一个表，在从库查看是否同步

create table test(id number);

11.主从切换

switchover to std;

执行的时候夯住，查看

[oracle@oracle2 ~]$ dgmgrl sys/oracle@pri
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration
Error:

Configuration - dg_ps

Protection Mode: MaxPerformance
Databases:
std - Primary database
pri - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ORA-16570: database needs restart
ORA-06512: at "SYS.DBMS_DRS", line 157
ORA-06512: at line 1
DGM-17017: unable to determine configuration status

DGMGRL> enable configuration
Error:
ORA-16570: database needs restart
ORA-06512: at "SYS.DBMS_DRS", line 157
ORA-06512: at line 1

Configuration details cannot be determined by DGMGRL

提示数据库需要重启

SQL> archive log list;
ORA-16456: switchover to standby in progress or completed
SQL> shutdown immediate
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> startup
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist

数据库启动和关闭都出现了问题：

解决办法，重启一次监听或者退出会话，重新进入，启动数据库

启动后查看主库pri状态：

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

从库std

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

查看dgbroker的信息：

pri：

DGMGRL> show configuration

Configuration - dg_ps

Protection Mode: MaxPerformance
Databases:
std - Primary database
pri - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ORA-16597: Data Guard broker detects two or more primary databases
ORA-16625: cannot reach database "std"
DGM-17017: unable to determine configuration status

虽然出现错误，单还是切换了。

std端：

[oracle@oracle2 ~]$ dgmgrl sys/oracle@std
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration

Configuration - dg_ps

Protection Mode: MaxPerformance
Databases:
pri - Primary database
std - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ORA-16534: switchover, failover or convert operation in progress
DGM-17017: unable to determine configuration status

std端显示的是没有切换成功：

注意事项：

1、一开始使用了dgbroker配置dg，那么不管是在主库还是从库执行alter system set xxx=xxx scope=spfile;都不会记录到spfile里面

可以用dgmrl 里面的edit 修改参数

2、 show configuration 出现错误的时候

可以执行show database verbose std（pri）查看错误

需要注意的是在dgmgrl sys/oracle@pri和dgmgrl sys/oracle@std 两端看到的错误是不同的

如果看到Warning: ORA-16714: the value of property StandbyFileManagement isinconsistent with the database setting之类的错误

则修改edit database std set property StandbyFileManagement='auto';

3、多使用help查看命令的使用方法

4、下图是切换后的图，看到SUCCESS那么恭喜你DG搭建成功

DGMGRL> show configuration

Configuration - dg_ps

Protection Mode:MaxPerformance

Databases:

std - Primarydatabase

pri - Physicalstandby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

0 0