RMAN DUPLICATE ADG DEMO

短命女 2022-01-09 04:37 184阅读 0赞

## RMAN DUPLICATE ADG DEMO ##

**生产环境谨慎使用，建议生产环境采用RMAN****备份恢复的方式。**

本演示案例所用环境：

<table> 
 <tbody> 
 <tr> 
 <td> &nbsp; </td> 
 <td> primary </td> 
 <td> standby </td> 
 </tr> 
 <tr> 
 <td> OS Hostname </td> 
 <td> pry </td> 
 <td> std </td> 
 </tr> 
 <tr> 
 <td> OS Version </td> 
 <td> RHEL6.5 </td> 
 <td> RHEL6.5 </td> 
 </tr> 
 <tr> 
 <td> DB Version </td> 
 <td> 11.2.0.4 </td> 
 <td> 11.2.0.4 </td> 
 </tr> 
 <tr> 
 <td> db_name </td> 
 <td> stephen </td> 
 <td> stephen </td> 
 </tr> 
 <tr> 
 <td> db_unique_name </td> 
 <td> stephen </td> 
 <td> standby </td> 
 </tr> 
 <tr> 
 <td> service_names </td> 
 <td> stephen </td> 
 <td> standby </td> 
 </tr> 
 <tr> 
 <td> instance_name </td> 
 <td> stephen </td> 
 <td> standby </td> 
 </tr> 
 </tbody> 
</table>

### Primary database configure ###

#### 1.启用primary force logging ####

SQL> select force\_logging from v$database;

SQL> alter database force logging;

#### 2.配置redo传输认证 ####

**(1) tnsnames.ora**

STEPHEN =

(DESCRIPTION =

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

(CONNECT\_DATA =

(SERVER = DEDICATED)

(SERVICE\_NAME = stephen)

)

STANDBY =

(DESCRIPTION =

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

(CONNECT\_DATA =

(SERVER = DEDICATED)

(SERVICE\_NAME = standby)

(UR = A)

)

**(2) tnsping****命令解析网络服务名,命令须返回OK.**

此时，standby端未配置监听，无法解析，可以standby配置后再测试。

$ tnsping standby

**(3)** **密码文件**

若无密码文件，则创建密码文件。格式：orapw<*ORACLE\_SID*>

cd $ORACLE\_HOME/dbs

orapwd file=orapwstephen password=oracle ignorecase=y force=y

#### 3.添加standby logfiles ####

添加standby logfile 的要求：

(1) 确保主和备数据库上的日志文件大小是相同的

(2) 确定备库重做日志文件组的适当数目

Standby logfile数 = （每个线程的logfile数＋1）\* 线程数

(3) 检查create database时指定的MAXLOGFILES和MAXLOGMEMBERS参数，可以通过trace controlfile出来查看

(4) RAC环境创建standby logfile,指定thread\#创建

每个thread的standby logfile数 = 每个thread的logfile数 + 1

检查当前环境的logfile：

set lines 200 pages 300

col member for a60

select a.thread\#,a.group\#,b.member,b.type,a.bytes/1024/1024 MB from v$log a,v$logfile b where a.group\#=b.group\#

union all

select a.thread\#,a.group\#,b.member,b.type,a.bytes/1024/1024 MB from v$standby\_log a,v$logfile b where a.group\#=b.group\#;

根据查询的logfile 信息上文添加standby logfile的要求，添加合理的standby logfile,然后用上面的sql再次查看当前环境的logfile：

SQL> alter database add standby logfile

group 4 '/u01/oradata/stephen/redo04.log' size 50M,

group 5 '/u01/oradata/stephen/redo05.log' size 50M,

group 6 '/u01/oradata/stephen/redo06.log' size 50M,

group 7 '/u01/oradata/stephen/redo07.log' size 50M;

#### 4.修改primary参数文件 ####

备份spfile：

create pfile='/tmp/pfile.bak' from spfile;

修改以下参数，其中stephen,standby分别为primary,standby的db\_unqiue\_name/TNS-Alias,详见参数详解部分：

alter system set log\_archive\_config='dg\_config=(stephen,standby)';

alter system set log\_archive\_dest\_1='location=/oradata/arch valid\_for=(all\_logfiles,all\_roles) db\_unique\_name=stephen';

alter system set log\_archive\_dest\_2='service=standby lgwr async valid\_for=(online\_logfiles,primary\_role) db\_unique\_name=standby';

alter system set log\_archive\_max\_processes=30; *\#**根据需求调整个数*

*--**确认以下参数，若不为默认值则修改：*

alter system set remote\_login\_passwordfile=exclusive scope=spfile;

\# remote\_login\_passwordfile=exclusive/shared

alter system set log\_archive\_dest\_state\_1=enable;

alter system set log\_archive\_dest\_state\_2=enable;

*--**可修改归档日志格式，与datafile区分：*

alter system set log\_archive\_format='%t\_%s\_%r.arc' scope=spfile;

*--**备角色参数：*

alter system set fal\_server=standby;

alter system set standby\_file\_management=auto;

alter system set db\_file\_name\_convert='/oradata/standby','/oradata/stephen' scope=spfile;

alter system set log\_file\_name\_convert='/oradata/standby','/oradata/stephen' scope=spfile;

注：以上参数，指定spfile修改的，在实例重启后生效。

#### 5.打开归档模式 ####

首先确认当前数据库是否是归档模式，如果不是，则打开归档模式。

archive log list;

shutdown immediate;

startup mount;

alter database archivelog;

archive log list;

alter database open;

### Standby database configure ###

#### 1.配置环境变量 ####

export ORACLE\_BASE=/oracle/app/oracle

export ORACLE\_HOME=/oracle/app/oracle/product/11.2.0/db\_1

export ORACLE\_SID=standby

export NLS\_LANG=AMERICAN\_AMERICA.ZHS16GBK

export PATH=$ORACLE\_HOME/bin:$PATH

export LD\_LIBRARY\_PATH=$ORACLE\_HOME/lib *\#**不同平台变量名不一样*

#### 2.配置redo传输认证 ####

**(1) listener.ora**

配置静态监听：

LISTENER =

(ADDRESS\_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=std)(PORT=1521))

)

SID\_LIST\_LISTENER=

(SID\_LIST=

(SID\_DESC=

(GLOBAL\_DBNAME=standby)

(SID\_NAME=standby)

(ORACLE\_HOME=/oracle/app/oracle/product/11.2.0/db\_1)

)

启用监听：

lsnrctl start

lsnrctl status

**(2) tnsnames.ora**

STEPHEN =

(DESCRIPTION =

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

(CONNECT\_DATA =

(SERVER = DEDICATED)

(SERVICE\_NAME = stephen)

)

STANDBY =

(DESCRIPTION =

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

(CONNECT\_DATA =

(SERVER = DEDICATED)

(SERVICE\_NAME = standby)

(UR = A)

)

**(2) tnsping****命令解析网络服务名,命令须返回OK.**

$ tnsping stephen

**(3)** **密码文件**

使用主库的密码文件：

scp pry:$ORACLE\_HOME/dbs/orapwstephen $ORACLE\_HOME/dbs/orapwstandby

#### 3.创建相关目录 ####

mkdir -p $ORACLE\_BASE/admin/standby/\{adump,dpdump,pfile\}

mkdir -p /oradata/\{standby,arch\}

#### 4.参数文件 ####

只需要设置一个参数：db\_name

echo '\*.db\_name=standby’>$ORACLE\_HOME/dbs/initstandby.ora

#### 5.RMAN duplicate ADG ####

启动数据库到nomount阶段：

startup nomount;

在primary端执行以下SQL进行 RMAN duplicate创建ADG,Oracle自动mount standby db:

$ rman target / auxiliary sys/oracle@standby

RMAN> run \{allocate channel pry1 type disk;

allocate channel pry2 type disk;

allocate channel pry3 type disk;

allocate channel pry4 type disk;

allocate auxiliary channel stby type disk;

duplicate target database for standby from active database

spfile

parameter\_value\_convert 'stephen','standby'

set db\_unique\_name='standby'

set instance\_name='standby'

set service\_names='standby'

set db\_file\_name\_convert='/oradata/stephen','/oradata/standby'

set log\_file\_name\_convert='/oradata/stephen','/oradata/standby'

set control\_files='/oradata/standby/control01.ctl'

set log\_archive\_max\_processes='4'

set fal\_server='stephen'

set standby\_file\_management='AUTO'

set log\_archive\_config='dg\_config=(stephen,standby)'

set log\_archive\_dest\_2='service=stephen lgwr async valid\_for=(ONLINE\_LOGFILE,PRIMARY\_ROLE) db\_unique\_name=stephen'

;

#### 6.Fetch Gap ####

如果打开standby db至read only时,报以下错误:

SQL> alter database open read only;

alter database open read only

ERROR at line 1:

ORA-10458: standby database requires recovery

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/oradata/standby/system01.dbf'

只需要在mount阶段,执行归档日志应用,Fetch Archive Gap即可,primary端切换几次日志,确保standby一致后再打开standby db至read only:

SQL> recover managed standby database disconnect from session;

#### 7.应用日志 ####

打开standby至read only模式，实时应用日志：

alter database open read only;

recover managed standby database using current logfile disconnect;

#### 8.观察日志传输 ####

跟踪primary,standby database的alert log,观察是否有错误发生，也可以看到应用日志的相关信息。

确认standby是否应用日志：

*--primary**端多切几次日志，观察alert log信息。*

alter system switch logfile;

*--**观察主备库日志序列号*

archive log list;

*--primary**端查询v$archived\_log视图，确认日志是否被应用：*

set lines 300 pages 300

col name for a20

select name,dest\_id,thread\#,sequence\#,standby\_dest,applied,registrar,completion\_time from v$archived\_log

where standby\_dest='YES'

order by thread\#,sequence\#;

*--primary**端查询primary,standby的最大日志序列号是否一致：*

select 'Primary :' "DB Role",thread\#,max(sequence\#)

from v$archived\_log

where standby\_dest='NO'

group by thread\#

union

select 'Standby :' "DB Role",thread\#,max(sequence\#)

from v$archived\_log

where standby\_dest='YES' and applied='YES'

group by thread\#

order by thread\#;

#  #

转载于:https://www.cnblogs.com/liang545621/p/9407138.html

RMAN DUPLICATE ADG DEMO

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 RMAN-05501 RMAN-05001

相关 Oracle11gR2使用RMAN duplicate复制数据库

相关 RMAN duplicate报错

相关 ADG传输模式

相关 ADG - Active

相关 ADG学习记录

相关 Oracle11gR2使用RMAN duplicate复制数据库

相关 ORACLE之搭建DG（RMAN Duplicate方式）

相关 RMAN DUPLICATE ADG DEMO

相关 Oracle 11gR2使用RMAN duplicate复制数据库