Oracle之DataGurd(DG)环境搭建

来源：互联网发布：美国人一生知乎编辑：程序博客网时间：2024/06/07 02:25

DataGurd(DG)这个是什么就无需在这里费周张了，自行百度。公司会经常用的哦。

文章参考：http://www.linuxidc.com/Linux/2015-03/115166.htm 这哥们的写的不错，放头头。

配置最大性能模式DG：
最大性能模式：
1、默认模式，提供主库的最高可用性能
2、保证主库在运行之中不会受到从库的干扰
优点：避免从库对主库的影响
缺点：不能保证数据不丢失（通过归档的方式，一直查一个arch，如果主库彻底没了，那么redo中的数据会丢失），最低的情况，丢失一个redo组

步骤如下

1.前期规划
2.配置网络监听
3.修改参数文件
4.rman duplicate 复制数据库
5.启动调试数据库
6.测试

1.规划

主库(1.1.1.11)：
db_name orcl
db_unique_name orcl_pd
service_name orcl
instance_name orcl
本地归档路径 /home/Oracle/orcl_arclog
tnsnames to_sd --配置去备库的tns

备库(1.1.1.10)：
db_name orcl 数据库名要一样
db_unique_name orcl_sd 在一个dg环境里面，每一个成员的名字。
servicer_name vicdb
instance_name vicdb 实例名可以不一样
本地归档路径 /home/oracle/vicdb_arclog
tnsnames to_pd --配置去主库的tns

2.配置网络监听

基础配置，配置两边的oracle net，保证两边能互通（需要使用静态注册），这步不过下面的基本没戏。
位置：/oracle/app/db/network/admin。

主库上的listener配置

[html] view plain copy

cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =orcl)
(ORACLE_HOME = /oracle/app/db)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.11)(PORT = 1521))
)
ADR_BASE_LISTENER = /oracle/app

从库上的listener配置

[html] view plain copy

cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = vicdb)
(ORACLE_HOME = /oracle/app/db)
(SID_NAME = vicdb)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.10)(PORT = 1521))
)
ADR_BASE_LISTENER = /oracle/app

两边使用相同的tnsnames.ora

[html] view plain copy

cat tnsnames.ora
to_sd =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = vicdb)
)
)
to_pd =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.11)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

3.修改参数文件

在主库上的配置(必须在归档模式)

创建目录

[html] view plain copy

mkdir /home/oracle/orcl_arclog

配置force logging

[html] view plain copy

alter database force logging;
SQL> select FORCE_LOGGING from v$database;
FORCE_
------
YES

添加参数
样板需要替换

[html] view plain copy

db_unique_name='主库唯一名'
log_archive_config='dg_config=(主库唯一名,从库唯一名)'
log_archive_dest_1='location=本地归档路径 VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=主库唯一名' --就是说不管你是主库还是备库，都把你的online放到“本地归档路径”里面去
log_archive_dest_2='service=去从库的TNS valid_for=(online_logfiles,primary_role) db_unique_name=从库唯一名' --当角色是主库的时候，使用"去从库的tns“发送online_redolog
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
standby_file_management='auto'

[html] view plain copy

SQL> create pfile from spfile;
vi initorcl.ora
*.audit_file_dest='/oracle/app/admin/vicdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oracle/app/oradata/vicdb/control01.ctl','/oracle/app/flash_recovery_area/vicdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/oracle/app/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/oracle/app'
*.memory_target=0
*.open_cursors=300
*.pga_aggregate_target=10485760
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=532676608
*.undo_tablespace='UNDOTBS1'
db_unique_name='orcl_pd'
log_archive_config='dg_config=(orcl_pd,orcl_sd)'
log_archive_dest_1='location=/home/oracle/orcl_arclog VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_pd'
log_archive_dest_2='service=to_sd valid_for=(online_logfiles,primary_role) db_unique_name=orcl_sd'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
standby_file_management='auto'
重启据库然后使用pfile启动；
也可以如下
SQL> CREATE SPFILE FROM PFILE;
SQL> STARTUP OPEN;

从库上的配置如下：

[html] view plain copy

添加如下参数，需要替换
db_name='需要同主库一样'
db_unique_name='从库唯一名'
log_archive_config='dg_config=(主库唯一名,从库唯一名)'
log_archive_dest_1='location=本地归档路径 valid_for=(all_logfiles,all_roles) db_unique_name=从库唯一名' ## 这里的这个all_logfiles主要是为后面最大可用模式做准备
log_archive_dest_state_1=enable
standby_file_management='auto'
log_file_name_convert='主库中日志文件的路径','从库上日志文件的路径（自己定义）' ## 这个是用在rman复制时需要的，设置了这个就不需要做set new..
db_file_name_convert='主库中数据文件的路径','从库上数据文件的路径（自己定义）'

启动到nomount状态

[html] view plain copy

vi initvicdb.ora
db_name='orcl'
db_unique_name='orcl_sd'
log_archive_config='dg_config=(orcl_pd,orcl_sd)'
log_archive_dest_1='location=/home/oracle/vicdb_arclog valid_for=(all_logfiles,all_roles) db_unique_name=orcl_sd'
log_archive_dest_state_1=enable
standby_file_management='auto'
log_file_name_convert='/oracle/app/oradata/vicdb','/oradata/vicdb'
db_file_name_convert='/oracle/app/oradata/vicdb','/oradata/vicdb'
SQL> CREATE SPFILE FROM PFILE;
SQL> STARTUP nomount pfile='initvicdb.ora';

注意：由于主库原来的名字是vicdb，修改名字之后未有修改数据文件位置，故还是/oracle/app/oradata/vicdb

[html] view plain copy

创建目录
mkdir /home/oracle/vicdb_arclog
mkdir /oradata/vicdb
创建从库密码文件
cd $ORACLE_HOME/dbs
orapwd file=orapwvicdb password=oracle force=y

4.rman duplicate 复制数据库

使用新创建的参数文件启动主库到open状态，启动从库到nomount状态（注意spfile的影响，可以mv掉对应的spfile）。

[html] view plain copy

rman target sys/oracle@to_pd auxiliary sys/oracle@to_sd nocatalog
RMAN> duplicate target database for standby from active database; 一般默认参数
RMAN> duplicate target database for standby from active database nofilenamecheck; 如果从库的数据库的位置和主库的位置一样的话就要添加
nofilenamecheck文件名不检查

看到这里然后卡在这里就可以在从机那里看传输过来的文件记得开双通道，多channel。

[html] view plain copy

Starting backup at 30-JUL-15
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oradata/vicl/system01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=/oradata/vicl/sysaux01.dbf
.
.
.
.
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=886440698 file name=/oradata/vicl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=886440698 file name=/oradata/vicl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=886440698 file name=/oradata/vicl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=886440698 file name=/oradata/vicl/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=886440698 file name=/oradata/vicl/test_tspitr01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=886440698 file name=/oradata/vicl/vic01
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=886440698 file name=/oradata/vicl/vic02
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=886440698 file name=/oradata/vicl/test01.dbf
Finished Duplicate Db at 30-JUL-15
完成

问题

[html] view plain copy

常见问题：
（1）如果使用的是非catalog，在rman 连接时，加上nocatalog关键字,否则可能会报TARGET> begin :fhdbi := dbms_rcvcat.getDbid; end;的错误
（2）在执行duplicate的时候，如果源库和目标库目录相同，那么在duplicate 时，需要加上nofilenamecheck
否则报错
auxiliary file name/u01/app/oracle/oradata/dg/sysaux01.dbf conflicts with a file used by
thetarget database
（3）如果从库的参数文件是从主库cp的，特别要注意controlfiles这个参数指定的路径是否正确
（4）主库要在归档模式，否则报错ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
（5）如果报如下错误，注意log_file_name_convert，及db_file_name_convert参数是否正确
RMAN-03002: failure of Duplicate Db command at 07/03/2014 21:21:23
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/03/2014 21:21:23
ORA-17628: Oracle error 19505 returned by remote Oracle server
alter database open read only;
6.如果报错
RMAN-03002: failure of Duplicate Db command at 08/13/2014 17:20:29
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 08/13/2014 17:20:29
RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby database
ORA-01103: database name 'PROD' in control file is not 'PROD_PD'
检查备库中的db_name是否和主库一致
7、如果有多个目录需要做映射可以使用
db_file_name_convert='/oracle/app/oradata/vicdb','/oradata/vicdb','/oradata/xxx','/oradata/vicdb'
8. 如报用户秘密错误，检查两个数据库的sys密码是否一样

5.启动调试数据库

从库现在已经是mount的状态

SQL> alter database open;

查看后台进程

[html] view plain copy

SQL> select process,client_process,sequence#,status from v$managed_standby ;
PROCESS CLIENT_PROCESS SEQUENCE# STATUS
------------------ ---------------- ---------- ------------------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED

启动用于同步的进程mrp

[html] view plain copy

--启动mrp服务
SQL> alter database recover managed standby database disconnect from session;
--如果想停止mrp服务使用
SQL> alter database recover managed standby database cancel;

再次检查后台服务

[html] view plain copy

SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
RFS LGWR 27 IDLE
RFS UNKNOWN 0 IDLE
RFS N/A 0 IDLE
MRP0 N/A 27 WAIT_FOR_LOG

6.测试

[html] view plain copy

主库上
SQL> create table dg_test as select * from scott.emp;
Table created.
SQL> insert into dg_test select * from dg_test;
14 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile; 主库写盘才会触发MRP进程
System altered.
SQL>
从库上
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ------------------
5 YES
4 YES
6 YES
SQL> select * from dg_test; 数据同步过来了

0 0