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

最新推荐文章于 2024-08-21 23:41:45 发布

hijk139

最新推荐文章于 2024-08-21 23:41:45 发布

阅读量3.8k

点赞数 1

分类专栏：数据迁移 oracle 文章标签： database oracle 数据库 archive system file

本文链接：https://blog.youkuaiyun.com/hijk139/article/details/7835669

版权

oracle 同时被 2 个专栏收录

63 篇文章

订阅专栏

数据迁移

7 篇文章

订阅专栏

本文介绍如何使用Oracle 11g的DuplicateFromActiveDatabase特性来搭建Active Data Guard (ADG)，包括配置归档模式、启用强制日志记录、生成备用重做日志文件等步骤，并详细展示了通过RMAN命令创建物理备库的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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]