教学文章
Technology Exchange
热门课程
400电话

免费咨询热线
400-090-9964

教学文章

Linux下ORACLE 11G DATA GUARD搭建

时间：2020-04-10 来源：

1、环境

      System Version  Hostname  IP  Oracle Version    DB_NAME    DB_UNIQUE_NAME

Primary    RHEL6.2    dg1    192.168.230.129    11.2.0.1.0    dg1    dg1

Standby    RHEL6.2    dg2    192.168.230.130    11.2.0.1.0    dg1    dg2

Primary端数据库处于open状态、归档状态及强制归档状态，通过DBCA创建，已经有密码文件;standby端仅安装数据库软件，没有创建数据库。

2、网络配置

2.1 primary监听配置

[oracle@dg1 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = dg1)

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

(SID_NAME = dg1)

)

)

LISTENER =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

2.2 standby监听配置

[oracle@dg2 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = dg2)

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

(SID_NAME = dg2)

)

)

LISTENER =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

2.3 primary网络服务名配置

[oracle@dg1 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DG_129 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = dg1)

)

)

DG_130 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = dg2)

)

)

2.4 standby网络服务名配置

[oracle@dg2 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DG_129 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = dg1)

)

)

DG_130 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = dg2)

)

)

2.5 启动监听并进行测试

注意要进行防火墙放行或者关闭防火墙。

[oracle@dg1 ~]$ tnsping dg_130

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 01-FEB-2015 17:46:08

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.130)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dg2)))

OK (0 msec)

[oracle@dg2 ~]$ tnsping dg_129

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 01-FEB-2015 17:46:25

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.129)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dg1)))

OK (10 msec)

3、参数配置

3.1 primary参数配置

根据spfile.ora生成pfile.ora。

[oracle@dg1 ~]$ cat pfile.ora

dg1.__db_cache_size=58720256

dg1.__java_pool_size=4194304

dg1.__large_pool_size=4194304

dg1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

dg1.__pga_aggregate_target=197132288

dg1.__sga_target=247463936

dg1.__shared_io_pool_size=0

dg1.__shared_pool_size=167772160

dg1.__streams_pool_size=4194304

*.audit_file_dest='/u01/app/oracle/admin/dg1/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/DG1/controlfile/o1_mf_bdkgbx5j_.ctl','/u01/app/oracle/flash_recovery_area/DG1/controlfile/o1_mf_bdkgbxcc_.ctl'

*.db_block_size=8192

*.db_create_file_dest='/u01/app/oracle/oradata'

*.db_domain=''

*.db_name='dg1'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4070572032

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg1XDB)'

*.log_archive_config='dg_config=(dg1,dg2)'

*.log_archive_dest_2='service=dg_130 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dg2'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=444596224

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

3.2 standby参数配置

将primary的pfile.ora拷贝到standby，修改后生成spfiledg2.ora。

[oracle@dg2 ~]$ cat pfile.ora

dg1.__db_cache_size=100663296

dg2.__db_cache_size=155189248

dg1.__java_pool_size=4194304

dg2.__java_pool_size=4194304

dg1.__large_pool_size=4194304

dg2.__large_pool_size=4194304

dg1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

dg2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

dg1.__pga_aggregate_target=155189248

dg2.__pga_aggregate_target=180355072

dg1.__sga_target=289406976

dg2.__sga_target=264241152

dg1.__shared_io_pool_size=0

dg2.__shared_io_pool_size=0

dg1.__shared_pool_size=167772160

dg2.__shared_pool_size=92274688

dg1.__streams_pool_size=4194304

dg2.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/dg2/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/dg2/controlfile/o1_mf_bdkgbx5j_.ctl','/u01/app/oracle/flash_recovery_area/dg2/controlfile/o1_mf_bdkgbxcc_.ctl'#Set by RMAN

*.db_block_size=8192

*.db_create_file_dest='/u01/app/oracle/oradata'

*.db_domain=''

*.db_name='dg1'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4070572032

*.db_unique_name='dg2'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg1XDB)'

*.log_archive_config='dg_config=(dg1,dg2)'

*.log_archive_dest_1='location=/u01/app/oracle/oradata/dg2/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=dg2'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=444596224

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='auto'

*.undo_tablespace='UNDOTBS1'

4、系统设置

4.1 standby目录创建

根据参数文件创建相关目录。

mkdir -p /u01/app/oracle/admin/dg2/{adump,bdump,cdump,pfile,udump}

mkdir -p /u01/app/oracle/oradata/dg2/{controlfile,datafile,onlinelog,archivelog}

mkdir /u01/app/oracle/flash_recovery_area

4.2 standby密码文件

将primary端的密码文件复制到standby并重命名。

scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdg1 oracle@192.168.233.130:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

mv /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdg1 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdg2

5、创建standby数据库

将standby数据库启动到nomount状态，在primary端运行如下命令：

[oracle@dg1 ~]$ rman target / auxiliary sys/123456@dg_130

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Feb 1 16:22:59 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: DG1 (DBID=1851568413)

connected to auxiliary database: DG1 (not mounted)

RMAN> duplicate target database for standby nofilenamecheck from active database;

Starting Duplicate Db at 01-FEB-15

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdg1' auxiliary format

'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdg2' ;

}

executing Memory Script

Starting backup at 01-FEB-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=44 device type=DISK

Finished backup at 01-FEB-15

contents of Memory Script:

{

sql clone "alter system set control_files =

''/u01/app/oracle/oradata/dg2/controlfile/o1_mf_bdkgbx5j_.ctl'', ''/u01/app/oracle/flash_recovery_area/dg2/controlfile/o1_mf_bdkgbxcc_.ctl'' comment=

''Set by RMAN'' scope=spfile";

backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/dg2/controlfile/o1_mf_bdkgbx5j_.ctl';

restore clone controlfile to '/u01/app/oracle/flash_recovery_area/dg2/controlfile/o1_mf_bdkgbxcc_.ctl' from

'/u01/app/oracle/oradata/dg2/controlfile/o1_mf_bdkgbx5j_.ctl';

sql clone "alter system set control_files =

''/u01/app/oracle/oradata/dg2/controlfile/o1_mf_bdkgbx5j_.ctl'', ''/u01/app/oracle/flash_recovery_area/dg2/controlfile/o1_mf_bdkgbxcc_.ctl'' comment=

''Set by RMAN'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set control_files = ''/u01/app/oracle/oradata/dg2/controlfile/o1_mf_bdkgbx5j_.ctl'', ''/u01/app/oracle/flash_recovery_area/dg2/controlfile/o1_mf_bdkgbxcc_.ctl'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 01-FEB-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_dg1.f tag=TAG20150201T162457 RECID=4 STAMP=870539108

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

Finished backup at 01-FEB-15

Starting restore at 01-FEB-15

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 01-FEB-15

sql statement: alter system set control_files = ''/u01/app/oracle/oradata/dg2/controlfile/o1_mf_bdkgbx5j_.ctl'', ''/u01/app/oracle/flash_recovery_area/dg2/controlfile/o1_mf_bdkgbxcc_.ctl'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 442601472 bytes

Fixed Size 2214176 bytes

Variable Size 281020128 bytes

Database Buffers 155189248 bytes

Redo Buffers 4177920 bytes

contents of Memory Script:

{

sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

set newname for clone tempfile 1 to new;

set newname for clone tempfile 2 to new;

switch clone tempfile all;

set newname for clone datafile 1 to new;

set newname for clone datafile 2 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 4 to new;

set newname for clone datafile 5 to new;

backup as copy reuse

datafile 1 auxiliary format new

datafile 2 auxiliary format new

datafile 3 auxiliary format new

datafile 4 auxiliary format new

datafile 5 auxiliary format new

;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/DG2/datafile/o1_mf_temp_%u_.tmp in control file

renamed tempfile 2 to /u01/app/oracle/oradata/DG2/datafile/o1_mf_n6datasp_%u_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 01-FEB-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/DG1/datafile/o1_mf_system_bdkg85cs_.dbf

output file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_system_0dpu6nuh_.dbf tag=TAG20150201T162657

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/DG1/datafile/o1_mf_sysaux_bdkg85ft_.dbf

output file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_sysaux_0epu6o0i_.dbf tag=TAG20150201T162657

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/DG1/datafile/n6_data01.dbf

output file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_n6datasp_0fpu6o2a_.dbf tag=TAG20150201T162657

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/DG1/datafile/o1_mf_undotbs1_bdkg85g2_.dbf

output file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_undotbs1_0gpu6o3d_.dbf tag=TAG20150201T162657

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/DG1/datafile/o1_mf_users_bdkg85gt_.dbf

output file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_users_0hpu6o46_.dbf tag=TAG20150201T162657

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Finished backup at 01-FEB-15

sql statement: alter system archive log current

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=870539415 file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_system_0dpu6nuh_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=5 STAMP=870539415 file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_sysaux_0epu6o0i_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=870539415 file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_undotbs1_0gpu6o3d_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=7 STAMP=870539415 file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_users_0hpu6o46_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=8 STAMP=870539415 file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_n6datasp_0fpu6o2a_.dbf

Finished Duplicate Db at 01-FEB-15

6、standby端启动redo应用

SQL> alter database recover managed standby database disconnect from session;

数据库已更改。

7、验证

7.1 primary端切换日志

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

15

7.2 standby端查看日志

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

15

首页 | 尖端课程 | 高薪就业 | 金牌讲师 | 实训环境 | 云课堂 | 新闻中心 | 关于我们 | PG认证 | 快捷支付

版权所有@北京神脑资讯技术有限公司（CUUG，中国UNIX用户协会） Copyright ALL Rights Reserved 京ICP备11008061号-1

CUUG旗下网站：www.cuug.com.cn www.cuug.com oracle.cuug.com bbs.cuug.com www.cuug.net

电话：010-59426307 010-59426319 邮政编码：100089

地址：北京市海淀区北清路164号28-38号院