本文介绍了11g active dataguard的详细配置步骤和数据保护模式的修改!
一:环境介绍
主库
IP地址:192.168.1.61/24
操作系统版本:rhel5.4 64bit
数据库版本:11.2.0.3 64bit
数据库sid名:dg
数据库名:dg
数据库db_unique_name:dg1
备库1 物理备库 (只安装oracle数据库软件,无需建库)
IP地址:192.168.1.62/24
操作系统版本:rhel5.4 64bit
数据库版本:11.2.0.3 64bit
数据库sid名:dg
数据库名:dg
数据库db_unique_name:dg2
二:修改主备库listener.ora,tnsnames.ora文件如下,备库根据自身情况修改
-
[oracle@dg1 ~]$ cat $TNS_ADMIN/listener.ora
-
SID_LIST_LISTENER =
-
(SID_LIST =
-
(SID_DESC =
-
(GLOBAL_DBNAME = dg1.yang.com)
-
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db1)
-
(SID_NAME = dg)
-
)
-
)
-
LISTENER =
-
(DESCRIPTION_LIST =
-
(DESCRIPTION =
-
(ADDRESS_LIST =
-
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1.yang.com)(PORT = 1521))
-
)
-
)
-
)
-
[oracle@dg1 ~]$ cat $TNS_ADMIN/tnsnames.ora
-
dg1 =
-
(DESCRIPTION =
-
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.61)(PORT = 1521))
-
(CONNECT_DATA =
-
(SERVER = DEDICATED)
-
(SERVICE_NAME = dg1.yang.com)
-
)
-
)
-
dg2 =
-
(DESCRIPTION =
-
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.62)(PORT = 1521))
-
(CONNECT_DATA =
-
(SERVER = DEDICATED)
-
(SERVICE_NAME = dg2.yang.com)
-
)
-
)
-
for_db =
-
(DESCRIPTION =
-
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.61)(PORT = 1521))
-
)
三:在主库上修改dataguard配置相关的各个参数,各参数的具体含义可以参考oracle在线文档
-
SQL> alter database force logging;
-
Database altered.
-
SQL> alter system set db_unique_name='dg1'scope=spfile;
-
System altered.
-
SQL> alter system set log_archive_config='DG_CONFIG=(dg1,dg2)';
-
System altered.
-
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog valid_for=
-
(all_logfiles,primary_role) db_unique_name=dg1' scope=spfile;
-
System altered.
-
SQL> alter system set log_archive_dest_2='SERVICE=dg2 lgwr sync valid_for=(online_logfile,primary_role)
-
db_unique_name=dg2';
-
System altered.
SQL> alter system set fal_client='dg1'; System altered. SQL> alter system set fal_server='dg2'; System altered. SQL> alter system set standby_file_management=auto; System altered. SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/dg/standby04.log' size 50M; Database altered. SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/dg/standby05.log' size 50M; Database altered. SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/dg/standby06.log' size 50M; Database altered. SQL> alter database add standby logfile group 7 '/u01/app/oracle/oradata/dg/standby07.log' size 50M; Database altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2228944 bytes Variable Size 285216048 bytes Database Buffers 121634816 bytes Redo Buffers 8466432 bytes Database mounted. Database opened. SQL> alter system set local_listener='for_db'; System altered. SQL> create pfile='/home/oracle/initdg.ora' from spfile; File created.
三:将生成的pfile文件修改后传递到备库,注意红色字体部分
[oracle@dg1 ~]$ cat /home/oracle/initdg.ora [oracle@dg1 ~]$ scp initdg.ora 192.168.1.62:/home/oracle/ |
四:将备库启动到nomount状态,然后连接主库进行duplicate操作
-
[oracle@dg2 ~]$ lsnrctl start
-
[oracle@dg2 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwdg password=123456entries=5
-
[oracle@dg2 ~]$ sqlplus /nolog
-
SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 22 13:36:53 2012
-
Copyright (c) 1982, 2011, Oracle. All rights reserved.
-
SQL> conn /as sysdba
-
Connected to an idle instance.
-
SQL> create spfile from pfile='/home/oracle/initdg.ora';
-
File created.
-
SQL> startup nomount
-
ORACLE instance started.
-
Total System Global Area 417546240 bytes
-
Fixed Size 2228944 bytes
-
Variable Size 285216048 bytes
-
Database Buffers 121634816 bytes
-
Redo Buffers 8466432 bytes
[oracle@dg2 ~]$ rman target sys/123456@dg1 auxiliary sys/123456@dg2 connected to target database: DG (DBID=1694605607) RMAN> duplicate target database for standby nofilenamecheck from active database; Starting Duplicate Db at 2012-04-22-13:39:25 contents of Memory Script: Starting backup at 2012-04-22-13:39:26 contents of Memory Script: '/u01/app/oracle/oradata/dg/control01.ctl'; Starting backup at 2012-04-22-13:39:29 STAMP=781277970 Starting restore at 2012-04-22-13:39:32 channel ORA_AUX_DISK_1: copied control file copy contents of Memory Script: sql statement: alter database mount standby database contents of Memory Script: executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/dg/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 2012-04-22-13:39:42 sql statement: alter system archive log current contents of Memory Script: datafile 1 switched to datafile copy RMAN> exit |
五:将备库置于active dataguard模式下
-
[oracle@dg2 ~]$ sqlplus /nolog
-
SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 22 13:47:17 2012
-
Copyright (c) 1982, 2011, Oracle. All rights reserved.
-
SQL> conn /as sysdba
-
Connected.
-
SQL> select open_mode,database_role,db_unique_name from v$database;
-
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-
-------------------- ---------------- ------------------------------
-
MOUNTED PHYSICAL STANDBY dg2
-
SQL> alter database open;
-
Database altered.
-
SQL> alter database recover managed standby database using current logfile disconnect from session;
-
Database altered.
-
SQL> select open_mode,database_role,db_unique_name from v$database;
-
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-
-------------------- ---------------- ------------------------------
-
READ ONLY WITH APPLY PHYSICAL STANDBY dg2
-
SQL> select status from v$standby_log;
-
STATUS
-
----------
-
ACTIVE
-
UNASSIGNED
-
UNASSIGNED
-
UNASSIGNED
-
SQL> select member from v$logfile;
-
MEMBER
-
--------------------------------------------------------------------------------
-
/u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_3_7s76qbhq_.log
-
/u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_2_7s76q94s_.log
-
/u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_1_7s76q5w1_.log
-
/u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_4_7s76qdpk_.log
-
/u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_5_7s76qhmy_.log
-
/u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_6_7s76qlhz_.log
-
/u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_7_7s76qp99_.log
-
7 rows selected.
六:修改dataguard的数据保护模式为最高可用性模式,根据oracle文档的解释,最高可用性数据保护模式需要先满足以下几个条件
-
SQL> select db_unique_name,protection_mode,protection_level from v$database;
-
DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
-
------------------------------ -------------------- --------------------
-
dg2 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
-
SQL> select db_unique_name,protection_mode,protection_level from v$database;
-
DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
-
------------------------------ -------------------- --------------------
-
dg1 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
-
SQL> alter database set standby database to maximize availability;
-
Database altered.
-
SQL> select db_unique_name,protection_mode,protection_level from v$database;
-
DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
-
------------------------------ -------------------- --------------------
-
dg1 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
-
SQL> select db_unique_name,protection_mode,protection_level from v$database;
-
DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
-
------------------------------ -------------------- --------------------
-
dg2 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
-
将备库shutdown后,主库的PROTECTION_LEVEL将变为RESYNCHRONIZATION
-
SQL> select db_unique_name,protection_mode,protection_level from v$database;
-
DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
-
------------------------------ -------------------- --------------------
-
dg2 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
-
SQL> alter database recover managed standby database cancel;
-
Database altered.
-
SQL> shutdown immediate
-
Database closed.
-
Database dismounted.
-
ORACLE instance shut down.
-
SQL> select db_unique_name,protection_mode,protection_level from v$database;
-
DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
-
------------------------------ -------------------- --------------------
-
dg1 MAXIMUM AVAILABILITY RESYNCHRONIZATION
本文转自东方之子736651CTO博客,原文链接: http://blog.51cto.com/ecloud/1381857,如需转载请自行联系原作者