oracle 9i 11g dataguard,Oracle 11g DataGuard使用总结

Oracle 11g的dataguard相比于10g来说，最优越的特性应该算就是active dataguard了，这一点改进在很大意义上促使用户需要把数据库从10g升级到11g,读写分离在这个时候得到了升华，而且在后台会根据需要进行数据的同步，相比于使用10g，想读数据的时候把数据库启动到read only 阶段，但这个时候不接受日志同步数据，如果需要同步数据还需要把数据库再启动到mount阶段，感觉还是比较繁琐的。

11g的active dataurad功能很强大，同时搭建的时候使用rman 的duplicate选项在11g也得到了很大的改进，我们不需要专门去做一个备份，就可以直接在备库上通过duplicate把数据文件从主库传送到备库，同时做恢复，整个过程更加简洁。

当然了一般的系统是一主一备，关键的系统甚至要一主两备，如果系统多了，手工管理很容易搞糊涂，这个时候还是用加强版的dg broker来做吧，以前在10g的时候感觉还是手工管理，手工switchover，failover比较踏实，因为也在10g的过程中switchover,failover出现过一些问题，算是留下了一些阴影，到了11g的时候，工作中大量使用，就尝试着自己试了试，发现确实很好，而且搭建备库步骤比手工搭建要轻松的多。所以还是需要拥抱变化，勇敢接受新事物，一方面可以给自己疗伤，一方面恢复使用的信心，至少在灾难发生的时候回临危不乱。

我们来简单看一个例子，怎么结合dg broker来搭建dataguard,步骤相比手工要简化很多，而且可靠性要高很多，因为人为去做的检查点，在dg broker的检查中都会去做。

出了设置主库为归档模式，force logging之外。

我们所要做的就是配置网络监听。当然这个也不是难点，listener.ora tnsnames.ora主备是很相似的。

比如我们使用1523的端口在primary和standby之间互联

listener.ora 主库和备库的host部分不一样

LISTENER_1523 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.xxxxx.45)(PORT = 1523))

)

SID_LIST_LISTENER_1523 =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = test11g)

(ORACLE_HOME = /DATA/app/oracle/product/11.2.0.4)

(SID_NAME = test11g)

)

tnsnames.ora 主库备库一致

TEST11G =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.xxxxx.45)(PORT = 1523))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test11g)

)

STEST11G =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.xxxxxx.46)(PORT = 1523))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test11g)

)

然后就是使用tnsping开始检查，检查防火墙，等等

添加备库日志

主库查看日志情况，可以使用下面的方法检查一下。

SQL> select member,group#,status from v$logfile

MEMBER GROUP# STATUS

-------------------------------------------------- ---------- -------

/DATA/app/oracle/oradata/test11g/redo03.log 3

/DATA/app/oracle/oradata/test11g/redo02.log 2

/DATA/app/oracle/oradata/test11g/redo01.log 1

SQL> select group#,status,bytes,blocksize,status from v$log;

GROUP# STATUS BYTES BLOCKSIZE STATUS

---------- ---------------- ---------- ---------- ----------------

1 INACTIVE 52428800 512 INACTIVE

2 CURRENT 52428800 512 CURRENT

3 INACTIVE 52428800 512 INACTIVE

添加备库日志的语句类似：

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/DATA/app/oracle/oradata/test11g/redo04.log' SIZE 524288000;

修改下面的数据库参数，

alter system set standby_file_management=auto scope=both;

alter system set dg_broker_start=TRUE scope=both;

alter system set local_listener=TEST11G scope=both;

检查dmon的情况

SQL> !ps -ef|grep dmon_test11g

oracle 8129 1 0 11:25 ? 00:00:00 ora_dmon_test11g

oracle 8138 8108 0 11:25 pts/0 00:00:00 /bin/bash -c ps -ef|grep dmon_test11g

oracle 8140 8138 0 11:25 pts/0 00:00:00 grep dmon_test11g

查看dg broker的日志，开始的时候没有配置文件，会重新创建。

DMON: cannot open configuration file "/DATA/app/oracle/product/11.2.0.4/dbs/dr2test11g.dat"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

DMON: cannot open configuration file "/DATA/app/oracle/product/11.2.0.4/dbs/dr2test11g.dat"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Configuration does not exist, Data Guard broker ready

数据库alert日志：

Mon Jul 27 11:25:15 2015

DMON started with pid=26, OS id=8129

Starting Data Guard Broker (DMON)

Mon Jul 27 11:25:23 2015

接着拷贝密码文件，参数文件(spfile)到备库

在备库生成pfile,然后修改下面的参数

修改db_unique_name为stest11g

修改local_listener为stest11g

配置 db_file_name_convert

log_file_name_convert

db_file_name_convert='/DATA/app/oracle/oradata/test11g','/DATA/app/oracle/oradata/test11g','/DATA/app/oracle/fast_recovery_area/test11g','/DATA/app/oracle/fast_recovery_area/test11g' log_file_name_convert='/DATA/app/oracle/oradata/test11g','/DATA/app/oracle/oradata/test11g','/DATA/app/oracle/fast_recovery_area/test11g','/DATA/app/oracle/fast_recovery_area/test11g'

检查db_recovery_file_dest 和其它的文件路径是否有效(adump,oradata)

fal_server

fal_client

生成spfile

这个时候被库的工作就基本完成了，剩下的就是使用rman来恢复，dg broker来配置了。

数据复制恢复

启动备库到nomount阶段

SQL> create spfile from pfile;

File created.

SQL> startup nomount

SQL> !ps -ef|grep dmon_test11g

oracle 17773 1 0 11:44 ? 00:00:00 ora_dmon_test11g

oracle 17779 17733 0 11:44 pts/0 00:00:00 /bin/bash -c ps -ef|grep dmon_test11g

oracle 17781 17779 0 11:44 pts/0 00:00:00 grep dmon_test11g

检查参数是否生效

备库通过rman复制数据文件

$ rman target sys/xxxx@test11g auxiliary sys/xxxxx@stest11g nocatalog

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jul 27 11:46:23 2015

connected to target database: TEST11G (DBID=1038061657)

using target database control file instead of recovery catalog

connected to auxiliary database: TEST11G (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

配置完成，数据复制工作就告一段落。

开始配置dg broker

主库执行：

$ dgmgrl /

DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL> create configuration dg_test11g as

> primary database is test11g

> connect identifier is test11g;

Configuration "dg_test11g" created with primary database "test11g"

DGMGRL> add database stest11g as

> connect identifier is stest11g

> maintained as physical;

Database "stest11g" added

这个时候配置还没有生效

DGMGRL> show configuration;

Configuration - dg_test11g

Protection Mode: MaxPerformance

Databases:

test11g - Primary database

stest11g - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

启用配置

DGMGRL> enable configuration;

然后再次查看，如果配置没有问题，就会生效。

DGMGRL> show configuration;

Configuration - dg_test11g

Protection Mode: MaxPerformance

Databases:

test11g - Primary database

stest11g - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

接着进行再次验证

DGMGRL> enable database test11g;

Enabled.

DGMGRL> enable database stest11g;

Enabled.

DGMGRL>

DGMGRL> show configuration;

Configuration - dg_test11g

Protection Mode: MaxPerformance

Databases:

test11g - Primary database

stest11g - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

dataguard的搭建就完成了，可以在主库切换日志查看是否在备库数据库日志中会应用日志

Media Recovery Waiting for thread 1 sequence 12 (in transit)

Recovery of Online Redo Log: Thread 1 Group 4 Seq 12 Reading mem 0

Mem# 0: /DATA/app/oracle/oradata/test11g/redo04.log

11g备库使用active dataguard特性。

SQL> alter database open;

Database altered.

SQL> recover managed standby database using current logfile disconnect from session;

Media recovery complete.

switchover的问题答疑

如果出现下面的错误，很可能当前操作系统用户下安装了多个数据库实例，取消默认的ORACLE_SID即可。

或者直接使用dgmgrl sys/oracle@test11g 这种方式连接

DGMGRL> switchover to 'stest11g';

Performing switchover NOW, please wait...

Operation requires a connection to instance "test11g" on database "stest11g"

Connecting to instance "test11g"...

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

connect to instance "test11g" of database "stest11g"

切换正常，但是需要手工启动备库，切换其实是正常的，需要手工启动一下主库

主要原因就是在listener.ora中需要配置一个xxxx_DGMGRL的global_name

DGMGRL> switchover to stest11g

Performing switchover NOW, please wait...

Operation requires a connection to instance "test11g" on database "stest11g"

Connecting to instance "test11g"...

Connected.

New primary database "stest11g" is opening...

Operation requires startup of instance "test11g" on database "test11g"

Starting instance "test11g"...

Unable to connect to database

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.

Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:

start up instance "test11g" of database "test11g"

如果配置完全正确，正常切换的日志就会如下：

DGMGRL> switchover to stest11g;

Performing switchover NOW, please wait...

Operation requires a connection to instance "test11g" on database "stest11g"

Connecting to instance "test11g"...

Connected.

New primary database "stest11g" is opening...

Operation requires startup of instance "test11g" on database "test11g"

Starting instance "test11g"...

ORACLE instance started.

Database mounted.

Database opened.

Switchover succeeded, new primary is "stest11g"

DGMGRL>

最后还是希望大家能够合理使用dataguard,一切顺利。

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 处理方法 http://www.linuxidc.com/Linux/2013-03/82009.htm

oracle 9i 11g dataguard,Oracle 11g DataGuard使用总结

相关文章

mysql可以使用dataguard,DataGuard相关参数

oracle dataguard详细,Oracle数据库DataGuard参数配置与参数详解

oracle11 dataguard,探索Oracle之11g DataGuard

oracle取消dataguard,Oracle Dataguard的强制切换

Oracle19c DataGuard

oracle dataguard延迟,dataguard延迟日志应用的问题

Oracle Dataguard（主库为 Oracle rac 集群）配置教程（01）—— dataguard 服务器安装 Oracle 软件

Oracle DataGuard

Data Guard原理

基于Java的员工工资管理系统

Java工程师工资水平出炉！真厉害了

Java培训出来后一般多少工资

2022上海Java工资收入概览

Java基础(员工工资管理系统)

2020年5大一线城市Java薪资水平汇总，你还差多少呢？

Redis LRU

LRU链表及LRU缓存

14.1 LRU链表

mysql lru_MySQL · 源码分析 · InnoDB LRU List刷脏改进之路

图解LRU算法