oracle11g 060,Oracle11g Dataguard配置

原文链接：

说明：Oracle Dataguard配置文章多如牛毛，但就笔者来说大部分不能叫做文档，只是作者的笔记罢了，未免有很多新手看不明白的，所以做Dataguard的配置可能需要找许多文章才能完成，历时艰辛。本文旨在为希望快速清晰的理解并顺利完成配置：

一、环境介绍1.主数据库环境操作系统版本: CentOS5.8 x64数据库版本: Oracle 11.2.0 x64数据库名: orcl数据库SID    : orcl

db_unique_name: orcl

instance_name : orcl

DGMGRL        : orcl_DGMGRL

2.备库环境操作系统版本: CentOS5.8 x64数据库版本: Oracle 11.2.0 x64(只安装Oracle数据库软件，不创建数据库，切记)数据库名: standby数据库SID    : standby

db_unique_name: standby

instance_name : standby

DGMGRL        : standby_DGMGRL

3. DataGuard数据库启动顺序启动顺序：先启备库，后启主库关闭顺序：先关主库，后关备库二、主数据库环境准备

1.主库环境对比充分利用主数据库原来环境，仅量不对主库参数配置做过多的修改。重新创建口令文件# su - oracle

$ orapwd file='/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl'

password=oracle entries=10 force=y

2.修改配置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.161.130)(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具有固定的格式：_DGMGRL.。

4.修改配置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.161.130)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

STANDBY =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = standby)

)

)

5.修改配置成规档模式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

6.修改主库参数文件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,standby)';

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=standby lgwr async

valid_for=(online_logfiles,primary_role) db_unique_name=standby'

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='standby' 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;三、备库配置

1.备库环境操作系统版本: CENTOS5.8 x64数据库版本: Oracle 11.2.0.3 x64(只安装oracle数据库软件,no netca dbca)数据库名: standby数据库SID    : standby

db_unique_name: standby

instance_name : standby

DGMGRL        : standby_DGMGRL

2.修改配置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.161.131)(PORT =

1521))

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

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = standby)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = standby)

)

(SID_DESC =

(GLOBAL_DBNAME = standby_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = standby)

)

)

ADR_BASE_LISTENER = /u01/app/oracle

其中的GLOBAL_DBNAME具有固定的格式：_DGMGRL.。

3.修改配置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.161.130)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

STANDBY =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = standby)

)

)

测试服务名连通性：tnsping orcl

tnsping standby

3.创建11g数据库基本目录# su - oracle

mkdir -p /u01/app/oracle/admin/standby/{adump,dpdump,pfile,scripts}

mkdir -p /u01/app/oracle/oradata/standby

mkdir -p /u01/app/oracle/fast_recovery_area/standby

mkdir -p /u01/archivelog

4.拷贝主库口令文件并改名注：10g DG环境只要求密码相同，11g DG则要求与主库完全一致。否则报无权限错误。$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/

$ scp oracle@192.168.161.131:/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl

$ORACLE_HOME/dbs/

$ mv orapworcl orapwstandby

测试远程登录$ sqlplus sys/oracle@orcl as sysdba;

$ sqlplus sys/oracle@standby as sysdba;

5.启动到nomount状态$ echo 'db_name=standby' > $ORACLE_HOME/dbs/initstandby.ora

$ sqlplus /nolog

SQL> conn / as sysdba;

SQL> startup nomount;

四、 开始在RMAN duplicate数据库

1. RMAN同进连接主库与备库

在开始RMAN duplicate之前需要在从库进行以下操作

点击(此处)折叠或打开

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> create pfile from spfile;

File created.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstandby.ora';

$ rman target sys/oracle@orcl auxiliarysys/oracle/standby恢复管理器: Release 11.2.0.1.0 - Production on星期五11月15 41:58:10 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights

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

2.开始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','standby'

set

instance_name='standby'

set

db_unique_name='standby'

set

local_listener='standby'

set

db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/standby/'

set

log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/standby/'

set

control_files='/u01/app/oracle/oradata/standby/control01.ctl','/u01/app/oracle/oradata/standby/control02.ctl','/u01/app/oracle/oradata/standby/control03.ctl'

set

log_archive_dest_1='LOCATION=/u01/archivelog/

valid_for=(all_logfiles,all_roles) db_unique_name=standby'

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='standby'

set

fal_server='orcl';

release channel

c1;

release channel

c2;

release channel

stby;

}

恢复管理器完成。

3.查看备库状态说明：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 standby

4.将备库置与应用日志模式状态SQL> alter database recover managed standby database using current logfile

disconnect from session;

Database altered.

5.验证物理备库日志应用1)主库上操作SQL> conn / as sysdba;

SQL> create user abc identified by abc ;

SQL> grant dba to abc;

SQL> conn abc/abc

SQL> create table 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/最早的联机日志序列9下一个存档日志序列9当前日志序列10

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

SQL> select

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

SEQUENCE# FIRST_TIME    NEXT_TIME      APPLIED

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

7 15-11月-13

15-11月-13    YES

8 15-11月-13

15-11月-13    YES

9 15-11月-13

15-11月-13    IN-MEMORY

经过测试，Oracle 11g

dataguard物理备库创建成功。