oracle 11g dataguard物理备库的创建步骤（最高可用lgwr sycn）
本文介绍了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文件如下，备库根据自身情况修改
$ 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))
)
)
)
$ 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 log_archive_dest_3='LOCATION=/u01/app/oracle/standbylog valid_for=
(standby_logfile,standby_role) db_unique_name=dg1' scope=spfile;
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 Area417546240 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文件修改后传递到备库，注意红色字体部分
$ cat /home/oracle/initdg.ora
dg.__db_cache_size=121634816
dg.__java_pool_size=4194304
dg.__large_pool_size=4194304
dg.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dg.__pga_aggregate_target=167772160
dg.__sga_target=251658240
dg.__shared_io_pool_size=0
dg.__shared_pool_size=109051904
dg.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dg/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/dg/control01.ctl','/u01/app/oracle/fast_recovery_area/dg/control02.ctl'
*.db_block_size=8192
*.db_domain='yang.com'
*.db_name='dg'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.db_unique_name='dg2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgXDB)'
*.fal_client='dg2'
*.fal_server='dg1'
*.local_listener='for_db'
*.log_archive_config='DG_CONFIG=(dg1,dg2)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog valid_for=(all_logfiles,primary_role)db_unique_name=dg2'
*.log_archive_dest_2='SERVICE=dg1 lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=dg1'
*.log_archive_dest_3='LOCATION=/u01/app/oracle/standbylog valid_for=(standby_logfile,standby_role)db_unique_name=dg2'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=419430400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
$ scp initdg.ora 192.168.1.62:/home/oracle/
四：将备库启动到nomount状态，然后连接主库进行duplicate操作
$ lsnrctl start
$ orapwd file=$ORACLE_HOME/dbs/orapwdg password=123456 entries=5
$ 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 Area417546240 bytes
Fixed Size 2228944 bytes
Variable Size 285216048 bytes
Database Buffers 121634816 bytes
Redo Buffers 8466432 bytes
$ rman target sys/123456@dg1 auxiliary sys/123456@dg2
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Apr 22 13:38:33 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.All rights reserved.
connected to target database: DG (DBID=1694605607)
connected to auxiliary database: DG (not mounted)
RMAN> duplicate target database for standby nofilenamecheck from active database;
Starting Duplicate Db at 2012-04-22-13:39:25
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile'/u01/app/oracle/product/11.2.0/db1/dbs/orapwdg' auxiliary format
'/u01/app/oracle/product/11.2.0/db1/dbs/orapwdg' ;
}
executing Memory Script
Starting backup at 2012-04-22-13:39:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Finished backup at 2012-04-22-13:39:28
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format
'/u01/app/oracle/oradata/dg/control01.ctl';
restore clone controlfile to'/u01/app/oracle/fast_recovery_area/dg/control02.ctl' from
'/u01/app/oracle/oradata/dg/control01.ctl';
}
executing Memory Script
Starting backup at 2012-04-22-13:39:29
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/db1/dbs/snapcf_dg.f tag=TAG20120422T133929 RECID=1
STAMP=781277970
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2012-04-22-13:39:32
Starting restore at 2012-04-22-13:39:32
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 2012-04-22-13:39:34
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 tempfile1 to
"/u01/app/oracle/oradata/dg/temp01.dbf";
switch clone tempfile all;
set newname for datafile1 to
"/u01/app/oracle/oradata/dg/system01.dbf";
set newname for datafile2 to
"/u01/app/oracle/oradata/dg/sysaux01.dbf";
set newname for datafile3 to
"/u01/app/oracle/oradata/dg/undotbs01.dbf";
set newname for datafile4 to
"/u01/app/oracle/oradata/dg/users01.dbf";
backup as copy reuse
datafile1 auxiliary format
"/u01/app/oracle/oradata/dg/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/dg/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/dg/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/dg/users01.dbf" ;
sql 'alter system archive log current';
}
executing 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
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/dg/system01.dbf
output file name=/u01/app/oracle/oradata/dg/system01.dbf tag=TAG20120422T133943
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/dg/sysaux01.dbf
output file name=/u01/app/oracle/oradata/dg/sysaux01.dbf tag=TAG20120422T133943
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/dg/undotbs01.dbf
output file name=/u01/app/oracle/oradata/dg/undotbs01.dbf tag=TAG20120422T133943
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/dg/users01.dbf
output file name=/u01/app/oracle/oradata/dg/users01.dbf tag=TAG20120422T133943
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2012-04-22-13:45:05
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=1 STAMP=781278308 file name=/u01/app/oracle/oradata/dg/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=781278308 file name=/u01/app/oracle/oradata/dg/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=781278308 file name=/u01/app/oracle/oradata/dg/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=781278308 file name=/u01/app/oracle/oradata/dg/users01.dbf
Finished Duplicate Db at 2012-04-22-13:45:29
RMAN> exit
Recovery Manager complete.
五：将备库置于active dataguard模式下
$ 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 PERFORMANCEMAXIMUM PERFORMANCE
SQL> select db_unique_name,protection_mode,protection_level from v$database;
DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
------------------------------ -------------------- --------------------
dg1 MAXIMUM PERFORMANCEMAXIMUM 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
支持原创！ 感谢分享
页:
[1]