通过 RMAN DUPLICATE...FROM ACTIVE DATABASE创建dataguard(for oracle 11g)

oracle 10g可以通过基于备份的rman DUPLICATE实现dataguard，通过步骤需要对数据库进行备份，并在standby侧进行数据库的恢复。而到了11g，oracle推出了Duplicate From Active Database技术，不需要再对数据库进行rman备份恢复，一切动作都通过网络自动完成。

下面是具体的实现例子：

primary db：hrdbprim

standby db:standby(由于是三个节点的rac，实例名为standby1）

一、primary侧的环境准备：
　　1，确保数据库归档状态
　　

SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG



2，Enable force logging
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.


3，生成standby redolog　　
　　

SQL> alter database add standby logfile '/oracle/app/oracle/oradata/hrdbprim/redo11.log' size 50m;
Database altered.


4，修改primary参数文件spfile，需要设置以下8个参数　　
　　

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(hrdbprim,standby)';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/oracle/app/oracle/oradata/hrdbprim/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=hrdbprim';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.
SQL> alter system set FAL_SERVER=standby;
System altered.
SQL> alter system set FAL_CLIENT=standby;
System altered.
SQL> alter system set DB_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/hrdbprim/','+DATA/standby/datafile/' scope=spfile;
System altered.
SQL> alter system set LOG_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/hrdbprim/','+DATA/standby/onlinelog/' scope=spfile;
System altered.
二、修改sql*net相关文件，确保网络环境准备,确保互相tnsping通　　
　　listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = standby)

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

(SID_NAME = standby3)

)

)


LISTENER =

(DESCRIPTION =

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

)
　　
tnsnames.ora


hrdbprim =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = hrdbprim)

)

)

standby =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = standby)(UR=A)

)

)
三、创建standby数据库
　　1，password密码文件；既可以从primary复制改名，也可以重新生成一个，主要要保持sys口令一致，这里采用复制方式

sftp ...

mv orapwhrdbprim orapwstandby

2,新建pfile文件，注意pfile要放在$ORACLE_HOME/dbs目录,否则启动时需要指定pfile文件,注意启动时必须使用pfile文件启动，否者无法复制

vi initstandby3.ora


DB_NAME=standby

DB_UNIQUE_NAME=standby

*.audit_file_dest='/oracle/app/oracle/admin/standby/adump'

*.control_files='+DATA/standby/controlfile/control01.ctl','+FRA/standby/controlfile/control02.ctl'

*.db_create_file_dest='+DATA'

*.db_block_size=8192

*.db_recovery_file_dest='+FRA'

*.db_recovery_file_dest_size=10G
　　3，创建相关目录，用来放datafile和trace file

mkdir -p /oracle/app/oracle/admin/standby/adump

ASMCMD> mkdir standby

ASMCMD> cd standby

ASMCMD> mkdir controlfile

ASMCMD> pwd

+data/standby

ASMCMD> cd controlfile

ASMCMD> pwd

+data/standby/controlfi
　　4，启动数据库到nomount状态


standby>startup nomount pfile = '/oracle/app/oracle/product/11.2.0/db_1/dbs/initstandby3.ora';

ORACLE 例程已经启动。


Total System Global Area 304861184 bytes

Fixed Size 2225872 bytes

Variable Size 159385904 bytes

Database Buffers 134217728 bytes

Redo Buffers 9031680 bytes
　　5，测试数据库连接问题

SQL> connect sys/"pl,12345"@standby as sysdba;

ERROR:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

说明数据库没有启动到mount状态，监听器blocked

通过tnsnames.ora中添加(UR=A)解决，且最好使用listener.ora静态注册
　　6，primary主机运行如下命令，此处连接必须使用网络连接符，否者报错
　　

RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'hrdbprim','standby'
set db_unique_name='standby'
set db_file_name_convert='/oracle/app/oracle/oradata/hrdbprim/','+DATA/standby/datafile/'
set log_file_name_convert='/oracle/app/oracle/oradata/hrdbprim/','+DATA/standby/onlinelog/'
set control_files='+DATA/standby/controlfile/control01.ctl','+FRA/standby/controlfile/control02.ctl'
set log_archive_max_processes='5'
set fal_client='standby'
set fal_server='hrdbprim'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(hrdbprim,standby)'
set log_archive_dest_1='service=hrdbprim ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=hrdbprim'
;
}
.........7,standby数据库，开启dataguard


sys@STANDBY3(dtydb5)> alter database recover managed standby database disconnect from session;


数据库已更改。


8，对于active dataguard，可以再使用如下命令

sys@STANDBY3(dtydb5)> alter database recover managed standby database cancel;
数据库已更改。
sys@STANDBY3(dtydb5)> alter database open;
数据库已更改。
sys@STANDBY3(dtydb5)> alter database recover managed standby database disconnect;
数据库已更改。
sys@STANDBY3(dtydb5)> alter database recover managed standby database using current logfile disconnect from session;
数据库已更改。
　　

　　激活备库到read/write状态

SQL> alter database activate standby database;

数据库已更改。

SQL> alter database open;





四、测试ADG结果


恢复单节点到rac数据库，注册到CRS，参见上篇文章


备注：注意事项：

a、standby监听器必须是静态监听

b、db_file_name_convert要正确设置，否者会报错ORA-17628, ORA-19505



参考资料：

RMAN 'Duplicate From Active Database' Feature in 11G [ID 452868.1]

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE [ID 1075908.1]

ORA-17628, ORA-19505 during RMAN DUPLICATE FROM ACTIVE [ID 1331986.1]