oracle dg 实例,单实例DG搭建(DUPLICATE)

实例名均为ora11g

1.主库添加静态监听和tnsname.ora条目如下

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ora11g)

(SID_NAME = ora11g)

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

)

)

stdbytns =

(DESCRIPTION =

(ADDRESS = (PROTOCOL =

TCP)(HOST = 192.168.3.106)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ora11g)

)

)

2.备库添加静态监听和tnsnames.ora配置主库记录

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ora11g)

(SID_NAME = ora11g)

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

)

)

prmtns =

(DESCRIPTION =

(ADDRESS = (PROTOCOL =

TCP)(HOST = 192.168.3.105)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ora11g)

)

)

3.主库调整配置参数如下：

主库ora11g配置为强制日志记录

startup mount;

alter database force logging;

设置归档位置

/u01/archive

主库设置归档位置

alter system set log_archive_dest_1='location=/u01/archive

valid_for=(all_logfiles,all_roles) db_unique_name=ora11g'

scope=both;

DG服务名配置

alter system set

log_archive_config='DG_CONFIG=(ora11g,ora11gdg)' scope=both;

设置归档位置和背库角色

alter system set log_archive_dest_2='service=STDBYTNS LGWR

ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180

valid_for=(online_logfiles,primary_role) db_unique_name=ora11gdg'

scope=both;

配置FAL_SERVER这个参数指定当日志传输出现问题时，备库到哪里去找缺少的归档日志。它用在备库接收的到的重做日志间有缺口的时候。

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

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

设置归档可用

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=both;

数据文件同步配置如果需要在主库添加或者删除数据文件时，这些文件是否会在备库添加或删除默认此参数是MANUAL手工方式(MANUAL不会同步到备库，AUTO自动同步)

alter system set standby_file_management='AUTO'

scope=both;

关机启动创建pfile

shutdown immediate;

create pfile from spfile;

4.主库ora11g添加standby log

主库创建standby日志(可以不创建，但在maxmize pretection，maxmize

availability中必须要有standby logfile)

(1)说明 在主库创建standby log file从库使用standby log

files来保存从主库接收到的重做日志。既然主要是从库在使用，那为什么需要在主库上也建立standby log

files?原因主要由两个：一是主库可能转换为备库，而备库是需要有standby log files的

二是如果主库建立了standby log files那备库会自动建立。

(2)日志个数说明

一般而言， standby redo 日志文件组数要比 primary 数据库的 online redo

日志文件组数至少多一个。推荐 standbyredo 日志组数量基于 primary 数据库的线程数(这里的线程数可以理解为 rac

结构中的 rac节点数)。

有一个推荐的公式可以做参考：(每线程的日志组数+1)*最大线程数 假设现在节点是1个，则=(3+1)*1=4

如果是双节点 则=(3+1)*2=8 这里我们创建4个standby logfile:

另：不建议组号group#紧挨着redo，因为后续redo有可能调整，这里我们从建立从11到14的standby

logfile

Startup;

alter database add standby logfile group 11

'/u01/oracle/oradata/ora11g/standby11.log' size 50M;

alter database add standby logfile group 12

'/u01/oracle/oradata/ora11g/standby12.log' size 50M;

alter database add standby logfile group 13

'/u01/oracle/oradata/ora11g/standby13.log' size 50M;

alter database add standby logfile group 14

'/u01/oracle/oradata/ora11g/standby14.log' size 50M;

查询

SQL> select a.* from v$standby_log a;

SQL> select group#,status,type,member from

v$logfile;

删除

SQL> alter database drop standby logfile group 11;

5.备库使用nomount参数启动数据库

echo 'db_name=ora11g' >

$ORACLE_HOME/dbs/initora11g.ora

pifle启动

create spfile from pfile

='/u01/oracle/product/11.2.0/dbs/initora11g.ora';

startup nomount

pfile='/u01/oracle/product/11.2.0/dbs/initora11g.ora;

6.主库开始复制数据到备库

方法一、如果分步骤做

<1>主库和备库开启监听

<2>把主库的pfile传到备库相应位置修改以下参数

*.db_unique_name='ora11gdg'

*.fal_server='prmtns'

*.fal_client=' stdbytns'

*.log_archive_config='dg_config=(ora11g,ora11gdg)'

*.log_archive_dest_1='location=/u01/archive

valid_for=(all_logfiles,all_roles) db_unique_name=ora11gdg'

*.log_archive_dest_2='service=PRMTNS LGWR ASYNC NOAFFIRM

max_failure=10 max_connections=5 reopen=180

valid_for=(online_logfiles,prim

ary_role) db_unique_name=ora11g'

<3>备库创建spfile文件

SQL> create spfile

frompfile='/u01/oracle/product/11.2.0/dbs/initora11g.ora ';

<4>备库创建密码文件(密码和主库一致)

$ orapwd file=$ORACLE_HOME/dbs/orapwora11gpassword=oracle

entries=5

<5>备库启动到nomount状态

<6>在primary端通过Rman Duplicate创建备库

$ rman target / auxiliary sys/oracle@stdbytns

RMAN> duplicate target database for standby from active

database nofilenamecheck;

方法二、以下为一步完成

rman target / auxiliary sys/oracle@stdbytns

run {

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate auxiliary channel stby1 type disk;

allocate auxiliary channel stby2 type disk;

duplicate target database for standby from active

database

dorecover

nofilenamecheck

spfile

parameter_value_convert 'ora11g','ora11g'

set db_unique_name='ora11gdg'

set control_files=

'/u01/oracle/oradata/ora11g/control01.ctl','/u01/oracle/fast_recovery_area/ora11g/control02.ctl'

set REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'

set fal_server='prmtns'

set fal_client=' stdbytns'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(ora11g,ora11gdg)'

set log_archive_dest_2='service=PRMTNS LGWR ASYNC NOAFFIRM

max_failure=10 max_connections=5 reopen=180

valid_for=(online_logfiles,primary_role)

db_unique_name=ora11g'

set log_archive_dest_1='location=/u01/archive

valid_for=(all_logfiles,all_roles) db_unique_name=ora11gdg'

reset REMOTE_LISTENER

reset local_listener;

release channel c1;

release channel c2;

release channel stby1;

release channel stby2;

}

7.备库打开数据库到只读

Sqlplus / as sysdba

alter database open;

alter database recover managed standby database using current

logfile disconnect from session;

8.配置删除归档日志脚本

oracle@hisdg:~$ crontab -l

0 0 * * * /export/home/oracle/rmarchive.sh >/dev/null

2>&1

Rmarchive.sh内容如下

#!/bin/sh

export ORACLE_BASE=/u01/app/oracle

export

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export ORACLE_SID=ora11g

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

$ORACLE_HOME/bin/rman target / nocatalog << EOF

run {

delete noprompt force archivelog all

completed before 'sysdate -3';

}

exit;

EOF

9.归档日志同步检查

主库输入命令

archive log list;确保 Current log sequence 主备库一致

备库执行命令

select SEQUENCE#,APPLIED, COMPLETION_TIME from

v$archived_log;

确保出现IN-MEMORY