ora11g dataguard新特性尝试1-CSDN博客

本文详细介绍如何在Oracle 11g中配置DataGuard，包括Physical Standby数据库的设置步骤，从生产库的前期准备到Standby数据库的创建全过程。

了解下oracle11g在physical standby模式下可以在standby数据库打开状态下进行数据恢复，考虑这种方式可以实现使用standby数据库进行数据查询。自己做个测试，了解下

Normal07.8 磅02falsefalsefalseMicrosoftInternetExplorer4

介绍

参考文档

1) B28294

2) Oracle MetaLink note 413484.1

针对企业应用，dataguard提供physical standby database及logical standby database的技术手段来保障数据库高可用性，提供数据保护及数据恢复的解决方案。

判断DataGuard是否安装

col parameter for a40

col value for a40

set lines 120

set pages 0

select * from v$option where parameter = 'Oracle Data Guard';

PARAMETER VALUE

Oracle Data Guard TRUE

网络配置

SHAPE * MERGEFORMAT

192.168.111.2(primary)

（）

192.168.111.3(standby)

（）

PRIMARY配置

[oracle@server1 admin]$ cat listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

[oracle@server1 admin]$ cat tnsnames.ora

PRIMARY =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = ora11g)

)

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = ora11g)

)

STANDBY配置

[oracle@server2 admin]$ cat listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

[oracle@server2 admin]$ cat tnsnames.ora

PRIMARY =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = ora11g)

)

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = ora11g)

)

生产库前期准备

设置强制归档

[oracle@server1 admin]$ sqlplus "/ as sysdba"

SQL> select FORCE_LOGGING from v$database;

SQL> alter database force logging;

SQL> select FORCE_LOGGING from v$database;

YES

创建口令文件

由于DATAGUARD在使用过程中要使用口令文件，确认生产库上的口令文件存在。

设置生产库参数

查看PRIMARY库上关键参数

*.control_files='/usr/oradata/ora11g/control01.ctl','/usr/oradata/ora11g/control02.ctl','/usr/oradata/ora11g/control03.ctl'

DB_NAME=ora11g

DB_UNIQUE_NAME=chicago

LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'

LOG_ARCHIVE_DEST_1='LOCATION=/usr/oradata/arch1/chicago/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago'

LOG_ARCHIVE_DEST_2='SERVICE=boston ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=5

FAL_SERVER=boston

FAL_CLIENT=Chicago

STANDBY_FILE_MANAGEMENT=AUTO

修改数据库运行在归档模式下

修改下列参数，确定数据库归档模式

SQL> shutdown immediate;

SQL> startup mount pfile='/tmp/initchicago.ora';

SQL> alter database archivelog;

SQL> alter database open;

SQL> create spfile from pfile='/tmp/initchicago.ora';

配置Standby Redo Log

select * from v$Log

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME

1 1 23 52428800 1 NO INACTIVE 339587 11-APR-07

2 1 24 52428800 1 NO CURRENT 387954 12-APR-07

3 2 3 52428800 1 NO CURRENT 387953 12-APR-07

4 2 2 52428800 1 NO INACTIVE 319579 18-OCT-06

确定生产库上在线日志文件的大小，创建Standby Redo Log按下列规则创建(maximum number of logfiles for each thread + 1) * maximum number of threads

ALTER DATABASE ADD STANDBY LOGFILE('/usr/oradata/ora11g/standby01.dbf') size 50M;

ALTER DATABASE ADD STANDBY LOGFILE('/usr/oradata/ora11g/standby02.dbf') size 50M;

ALTER DATABASE ADD STANDBY LOGFILE('/usr/oradata/ora11g/standby03.dbf') size 50M;

ALTER DATABASE ADD STANDBY LOGFILE('/usr/oradata/ora11g/standby04.dbf') size 50M;

SQL> alter database create standby controlfile as '/tmp/stbycf.ctl';

scp -rp /tmp/stbycf.ctl server2:/usr/oradata/ora11g/control01.ctl

scp -rp /tmp/stbycf.ctl server2:/usr/oradata/ora11g/control02.ctl

scp -rp /tmp/stbycf.ctl server2:/usr/oradata/ora11g/control03.ctl

scp -rp /tmp/initboston.ora server2:/tmp/initboston.ora

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/48010/viewspace-1015566/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/48010/viewspace-1015566/

ora11g dataguard新特性尝试1

介绍

判断DataGuard是否安装

网络配置

PRIMARY配置

STANDBY配置

生产库前期准备

设置强制归档

创建口令文件

设置生产库参数

修改数据库运行在归档模式下

配置Standby Redo Log

创建Standby数据库

备份生产数据库

创建备份库需要的控制文件