Oracle11g Dataguard配置(使用RMAN创建物理standby)

物理STANDBY：提供与主数据库完全一样的拷贝（块到块），数据库SCHEMA，包括索引都是一样的。它是直接应用REDO实现同步的。

逻辑STANDBY：则不是这样，在逻辑STANDBY中，逻辑信息是相同的，但物理组织和数据结构可以不同，它和主库保持同步的方法是将接收的REDO转换成SQL语句，然后在STANDBY上执行SQL语句。逻辑STANDBY除灾难恢复外还有其它用途，比如用于用户进行查询和报表。

我们生产环境大多使用物理STANDBY来配置oracle DG

名称	主机1	主机2
OS	Redhat 6.4 Linux 64位	Redhat 6.4 Linux 64位
Hostname	Hq05	Hq07
角色	主库	备库
IP	172.16.10.15	172.16.10.17
Database version	Oracle 11.2.0.4	Oracle 11.2.0.4
ORACLE_BASE	/opt/ora11	/opt/ora11
ORACLE_HOME	$ORACLE_BASE/product/11.2/db_1	$ORACLE_BASE/product/11.2/db_1
db_name	gnntreport	gnntreport
db_unique_name	gnntreport	phystdby
instance_name||SID	gnntreport	phystdby
net service name	tnsgnntreport	tnsphystdby
安装软件	oracle软件及数据库	只安装oracle软件
数据目录	/opt/ora11/oradata/gnntreport	/opt/ora11/oradata/gnntreport

DG中的物理备库与主库有相同的db_name和db_domain。需要用db_unique_name来进行区别,db_unique_name会影响到Service_names，也会影响到动态监听的时候的service_name

主库和备库hosts文件配置/etc/hosts

172.16.10.15 Hq05

172.16.10.17 Hq07

主库oracle用户环境配置

export ORACLE_BASE=/opt/ora11

export ORACLE_HOME=$ORACLE_BASE/product/11.2/db_1

export ORACLE_SID=gnntreport

export PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin

stty erase ^h

umask 022

#export LANG=zh_CN.GB18030

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

alias sqlplus='rlwrap sqlplus'

alias rman='rlwrap rman'

备库oracle用户环境配置

export ORACLE_BASE=/opt/ora11

export ORACLE_HOME=$ORACLE_BASE/product/11.2/db_1

export ORACLE_SID=phystdby

export PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin

stty erase ^h

umask 022

#export LANG=zh_CN.GB18030

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

alias sqlplus='rlwrap sqlplus'

alias rman='rlwrap rman'

DataGuard主库配置

( 1 )主库启用强制记录日志功能

alter database force logging;

select force_logging from v$database;

( 2)启用归档

archive log list

( 3 )主库参数配置

alter system set db_unique_name = 'gnntreport' scope=spfile;

alter system set log_archive_config='DG_CONFIG=(gnntreport,phystdby)' scope=spfile;

alter system set log_archive_dest_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=gnntreport' scope=both;

alter system set log_archive_dest_2='SERVICE=tnsphystdby LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=phystdby' scope=spfile;

alter system set log_archive_dest_state_1 = ENABLE;

alter system set log_archive_dest_state_2 = ENABLE;

alter system set LOG_ARCHIVE_MAX_PROCESSES=5 scope=spfile; （最大ARCn进程数）

alter system set db_file_name_convert='/opt/ora11/oradata/gnntreport','/opt/ora11/oradata/gnntreport' scope=spfile;

alter system set log_file_name_convert='/opt/ora11/oradata/gnntreport','/opt/ora11/oradata/gnntreport' scope=spfile;

alter system set standby_file_management=auto scope=spfile;

alter system set fal_client='tnsgnntreport' scope=both;

alter system set fal_server='tnsphystdby' scope=both;

配置完后要重启数据库，以使配置生效

( 4 )主库静态监听配置

[oracle@Hq05 admin]$ vim /opt/ora11/product/11.2/db_1/network/admin/listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

)

ADR_BASE_LISTENER = /opt/ora11

( 5 )主库tnsnames.ora文件配置

[oracle@Hq05 admin]$ vim /opt/ora11/product/11.2/db_1/network/admin/tnsnames.ora

tnsgnntreport = /***Net service name*/

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = gnntreport)

)

)

tnsphystdby =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = phystdby)

)

)

(6)主库归档日志删除策略配置

RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

归档日志只有在备库应用后才可以删除！

DataGuard备库配置

（ 1 ）密码文件

在dataguard中，主库与备库sys密码需一致。可以将主库的密码文件拷贝到备库中，然后重命名或在备库上重新生成密码一致的文件

[oracle@Hq05 dbs]$ orapwd file=orapwgnntreport password=oracledemo entries=5;

[oracle@Hq05 dbs]$ scp orapwgnntreport [email protected]:/opt/ora11/product/11.2/db_1/dbs/

[oracle@Hq07 dbs]$mv orapwgnntreport orapwphystdby

（ 2 ）初始化参数文件

• 在主库生成初始化参数文件

主库：

SQL> create pfile from spfile;

• 拷贝主库的参数文件到备库并重命名

[oracle@Hq05 dbs]$ scp -p initgnntreport.ora oracle@hq07:/opt/ora11/product/11.2/db_1/dbs/initphystdby.ora

• 修改备库的pfile参数文件内容

*.diagnostic_dest='/opt/ora11'

gnntreport.__oracle_base='/opt/ora11'#ORACLE_BASE set from environment

*.db_unique_name='phystdby'

*.audit_file_dest='/opt/ora11/admin/gnntreport/adump'

*.control_files='/opt/ora11/oradata/gnntreport/control01.ctl','/opt/ora11/oradata/gnntreport/control02.ctl'

*.log_archive_dest_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=phystdby'

*.log_archive_dest_2='SERVICE=tnsgnntreport LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=gnntreport'

*.db_file_name_convert='/opt/ora11/oradata/gnntreport','/opt/ora11/oradata/gnntreport'

*.log_file_name_convert='/opt/ora11/oradata/gnntreport','/opt/ora11/oradata/gnntreport'

*.fal_client='tnsphystdby'

*.fal_server='tnsgnntreport'

注意：里面涉及到路径的需要手动创建 mkdir -p /archivelog mkdir -p /opt/ora11/admin/gnntreport/adump mkdir -p /rmanbak mkdir -p /opt/ora11/oradata/gnntreport

• 使用pfile文件创建spfile文件

--登陆到idle数据库 sqlplus / as sysdba

SQL> create spfile from pfile;

( 3 )配置静态监听

[oracle@Hq07 admin]$vim /opt/ora11/product/11.2/db_1/network/admin/listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

)

ADR_BASE_LISTENER = /opt/ora11

（ 4 ）配置tnsnames.ora文件

[oracle@Hq07 admin]$vim /opt/ora11/product/11.2/db_1/network/admin/tnsnames.ora

tnsgnntreport =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = gnntreport)

)

)

tnsgnntreport_standby =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = gnntreport_standby)

)

)

（ 7 ）使用RMAN创建物理standby

主库：RMAN备份（关闭应用服务器，停止监听，开始rman备份）

RMAN>backup full format '/rmanbak/full_%T_%s_%p.bak' database plus archivelog;

[oracle@Hq05 rmanbak]$ sqlplus / as sysdba

SQL>alter database create standby controlfile as '/rmanbak/stdby_control01.ctl';

$cd /rmanbak

$cp stdby_control01.ctl control01.ctl

$cp stdby_control01.ctl control02.ctl

$scp *.bak [email protected]:/rmanbak/

$scp control0*.ctl [email protected]:/opt/ora11/oradata/gnntreport/

备库：做rman恢复

SQL> startup nomount

SQL> alter database mount standby database;

$ rman target / (要求主备库rman备份文件的存放路径和文件名一致)

RMAN> restore database;

RMAN> restore archivelog all;

介质恢复后，rman 自动将standby 数据库打开到mount 状态。

（ 8 ）添加主库和备库的standby日志组

一定要确认一下现有redo大小，本例中原redo大小50M

• 添加standby日志组需要注意的事项

standby日志组个数：配置为redo日志组个数+1(备库要添加4个standby 日志组，比普通日志组多一个)

在主库与备库都添加standby日志组。主库可以不添加，但是如果后期发生主备切换，还是要添加，所以最好一次性添加。

只查询standby日志组： select * from v$standby_log ;

• 添加主库的standby日志组(在主库上操作）

alter database add standby logfile group 4 ('/opt/ora11/oradata/gnntreport/stdbyredo04.log') size 50M;

alter database add standby logfile group 5 ('/opt/ora11/oradata/gnntreport/stdbyredo05.log') size 50M;

alter database add standby logfile group 6 ('/opt/ora11/oradata/gnntreport/stdbyredo06.log') size 50M;

alter database add standby logfile group 7 ('/opt/ora11/oradata/gnntreport/stdbyredo07.log') size 50M;

• 添加备库的standby日志组（在备库上操作）

alter database add standby logfile group 4 ('/opt/ora11/oradata/gnntreport/stdbyredo04.log') size 50M;

alter database add standby logfile group 5 ('/opt/ora11/oradata/gnntreport/stdbyredo05.log') size 50M;

alter database add standby logfile group 6 ('/opt/ora11/oradata/gnntreport/stdbyredo06.log') size 50M;

alter database add standby logfile group 7 ('/opt/ora11/oradata/gnntreport/stdbyredo07.log') size 50M;

• 开始同步数据库，在备库上执行（在备库上操作）

开启实时同步,启动redo apply（使用联机redolog恢复数据)

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

SQL>alter database recover managed standby database cancel;

SQL>alter database open;

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

（五）DataGuard状态查看

备库：

SQL> select open_mode, database_role, protection_mode, protection_level from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL -------------------- ---------------- -------------------- -------------------- READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

主库：

SQL> select open_mode, database_role, protection_mode, protection_level from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL -------------------- ---------------- -------------------- -------------------- READ WRITE PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

（六）设置保护模式为最高可用性级别

maximize availability： 主库得到备库收到确认后，事务生效，通过online redlog传递

maximize performance: 主库无须备库收到确认，通过archive log传递，主库在日志切换后，备库通过archive log写进本地redo log(即备库在主库日志切换后，数据再写进备库）;

SQL> alter database set standby database to maximize availability; （在主库操作）

SQL> select open_mode, database_role, protection_mode, protection_level from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL -------------------- ---------------- -------------------- -------------------- READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

以下是各种保护模式相互切换

（七）DATAGUARD主库和备库启动和关闭顺序

1.1 启动顺序：先启动备库，再启动主库

------------启动备库

SQL>start nomount;

SQL>alter database mount standby database;

SQL>alter database open;

SQL>alter database recover managed standby database using current logfile disconnect from session; --启动日志应用

READ ONLY WITH APPLY：代表启动备库实时恢复MRP进程，Managed Standby Recovery starting Real Time Apply

------------启动主库

SQL>startup;

1.2 关闭顺序：先关闭主库，再关闭备库

------------关闭主库

SQL>shutdown immediate;

------------关闭备库

SQL>alter database recover managed standby database cancel; - --取消日志应用（停止同步）

SQL>shutdown immediate

（八）DATAGUARD主备库切换

1、正常切换（维护需要）

顺序：先将主切为备，再将备切换换为主

1）主库上执行：

SQL> alter database commit to switchover to physical standby; (此语句执行完，原主库数据库自动关闭）

SQL>select switchover_status from v$database；

switchover_status的值如果是To standby，可以直接switchover，如果是sessions active，则需要在switchover的命令后面加上with session shutdown

SQL>alter database commit to switchover to physical standby with session shutdown;

2）备库上执行：

SQL> alter database commit to switchover to primary; (此语句执行完，原备库数据库自动切换为mounted状态）

SQl> shutdown immediate;

3) 原主库上执行（现成为备库）

SQL> startup nomount;

SQL> alter database mount standby database;

SQL> alter database open;

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

此时原主库成为备库后，应用恢复日志，会自动清除原在线日志。

4)原备库上执行（现成为主库）

SQL> startup；

2、非正常切换（即主服务器宕机的情况）启动failover

备服务器

SQL >alter database recover managed standby database finish;

SQL >alter database commit to switchover to primary;

SQL> alter system set log_archive_dest_state_2='defer' scope=both; (主服务器不能恢复情况下，禁用日志传输）

SQL >shutdown immediate;

SQL >startup;

（九）客户端连接

主库配置 ：

SQL>alter system set service_names='gnntreport','reportserver' scope=both;

备库配置 ：

SQL> alter system set service_names='phystdby','reportserver' scope=both;

客户端配置

reportserver =

(DESCRIPTION =

(ADDRESS_LIST =

(LOAD_BALANCE = OFF)

(FAILOVER = ON)

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

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

)

(CONNECT_DATA =

(SERVICE_NAME = reportserver)

)

)

（十）创建数据文件注意的地方

主库创建新表空间及数据文件，备库会自动同步

主库创建新临时表空间及数据文件，备库只会同步临时表空间，不会同步临时表空间对应的数据文件，需要在备库上手动创建（即使不创建不影响启动及其它数据同步）

下一章，我们将使用duplicate创建物理standby。