今天成功安装了一个测试环境，安装方法记录如下。
Oracle 11gR2 Active Data Guard 安装配置
一、测试环境
主库(primary):
os: oracle linux 5.8
IP:129.100.253.62
主机名：pc01
primary database:11.2.0.3 single node+asm
数据库名：sgerp5
从库(standby)
os: oracle linux 5.8
IP:129.100.253.91
主机名：pc02
standby database:11.2.0.3 single node+asm
数据库名：sgerp5
dataguard的保护模式：MAXIMUM PERFORMANCE
日志传输模式：归档日志
应用方法：redo apply
[root@pc01 ~]# uname -a
Linux pc01 2.6.32-300.10.1.el5uek #1 SMP Wed Feb 22 17:37:40 EST 2012 x86_64 x86_64 x86_64 GNU/Linux
[root@pc01 ~]#
前提：主从的数据文件放置的路径一致
在FAL request Failed with ORA-01009 on Standby MRP trace. [ID 1329119.1]文章中对这个问题进行了说明，从11.2以后，FAL_CLIENT不再需要设置，但是对应11.2之前的版本，缺少FAL_CLIENT的设置就会导致ORA-1009的错误。
二、调整主库
1.将主数据库改为强制产生日志模式
select force_logging from v$database;
alter database force logging;
2. 创建密码
orapwd file=$ORACLE_HOME/dbs/orapwtest password=test entries=3
3. 修改主库的初始化参数
建议参数文件使用spfile，这样可以在不停库使用命令修改参数。
db_name指定数据库的名称。
db_unique_name指定了数据库（主库和备库）的不同名称，是对db_name的唯一的标识。其实是为了区分db_name名一样的数据库，方便应用和管理。是10g以后的参数。
db_unique_name ='sg01'
log_archive_config='DG_CONFIG=(sg01,sg02)' # (sg01,sg02是db_unique_name)
log_archive_dest_1='location=/oraarch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sg01'
log_archive_dest_2='SERVICE=sg02ARCH ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sg02'
alter system set db_unique_name ='sg01' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(sg01,sg02)' scope=spfile;
alter system set log_archive_dest_1='location=/oraarch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sg01' scope=spfile;
alter system set log_archive_dest_2='SERVICE=sg02ARCH ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sg02' scope=spfile;
#确定log_archive_dest_state_1
log_archive_dest_state_1= enable #默认就是enable
log_archive_dest_state_2= enable #默认就是enable
ALTER SYSTEM SET log_archive_format='ARC_ERP5_%s_%r_%t.log' SCOPE=SPFILE;
/*以下部分为主机切换为备库使用*/
fal_server=sg02
fal_client=sg01
standby_file_management=auto
alter system set fal_server=sg02 scope=spfile;
alter system set fal_client=sg01 scope=spfile;
alter system set standby_file_management=auto scope=spfile;
#如果主备库文件或路径不同
db_file_name_convert='/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/test'
log_file_name_convert='/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/test'
4. 确保数据库运行在archivelog模式下
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Current log sequence 4
把数据库调整为archivelog模式下
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog ;
SQL> alter database open;
5.在主库上创建备用日志（为切换而用可选）
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
6. 配置listener.ora和tnsnames.ora
配置备库tnsnames.ora($ORACLE_HOME/network/admin/tnsnames.ora)
SG01=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=129.100.253.62)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=sg01)
)
)
SG02=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=129.100.253.91)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=sg02)
)
)
more /g01/app/grid_home/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = xezf)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(GLOBAL_DBNAME = xezf)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
)
)
7.在主库备份
[oraoms@LINUXIDC1 ~]$ rman target/
backup as compressed backupset database format '/orabak/DB0_%d_%T_%s_%t';
在主库上创建备库的控制文件/spfile文件
SQL> alter database create standby controlfile as '/home/oracle/ctl01.ctl';
三、创建备库
1. 创建pwdfile、spfile
orapwd file=$ORACLE_HOME/dbs/orapwtest password=test entries=3
密码要与primary database相同
$ORACLE_HOME/dbs/
/u01/app/oracle/product/11.2.0/db_1/dbs
ftp
2. 修改初始化参数
与primary database不同的参数如下:
db_unique_name=sg02
log_archive_dest_1=' location=/oraarch/valid_for=(all_logfiles,all_roles) db_unique_name=sg02'
log_archive_dest_2='service=sg01ARCH ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=sg01'
fal_client='sg02'
fal_server='sg01'
alter system set db_unique_name ='sg02' scope=spfile;
alter system set log_archive_dest_1='location=/oraarch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sg02' scope=spfile;
alter system set log_archive_dest_2='SERVICE=sg01ARCH ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sg01' scope=spfile;
alter system set fal_client='sg02' scope=spfile;
alter system set fal_server='sg01' scope=spfile;
db_file_name_convert='/u01/app/oracle/oradata/TEST01/datafile','/u01/app/oracle/oradata/TEST02/datafile'
log_file_name_convert='/u01/app/oracle/oradata/TEST01/onlinelog','/u01/app/oracle/oradata/TEST02/onlinelog', '/u01/app/oracle/flash_recovery_area/TEST01/onlinelog','/u01/app/oracle/flash_recovery_area/TEST02/onlinelog'
修改完之后用如下语句查看
SQL> select * from V$DATAGUARD_CONFIG;
DB_UNIQUE_NAME
------------------------------------------------------------------------------------------
sg01
sg02
恢复备库
[oraoms@LINUXIDC2 dbs]$ rman target/
RMAN> restore controlfile from '/home/oracle/ctl01.ctl';
Starting restore at 07-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/sg01/controlfile/current.257.809395691
output file name=+DATA/sg01/controlfile/current.258.809395691
Finished restore at 07-MAR-13
RMAN>
alter system set control_files='+DATA/sg01/controlfile/current.257.809395691','+DATA/sg01/controlfile/current.258.809395691' scope=spfile;
shutdown immediate;
startup mount;
RMAN> restore database;
SQL>shutdown immediate
SQL>startup nomount
SQL> alter database mount standby database;
只读打开模式：
SQL> alter database open read only;
让主备库同步：
SQL> alter database recover managed standby database disconnect from session;
2.检查备库运行在archivelog模式下
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Current log sequence 4
把数据库调整为archivelog模式下
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog ;
SQL> alter database open;
3.检查备库为强制产生日志模式
select force_logging from v$database;
alter database force logging;
4.创建好相应目录，dump文件目录，数据文件目录，归档目录
mkdir -p /u01/app/oracle/oradata
mkdir -p /u01/app/oracle/flash_recovery_area
mkdir -p /u01/app/oracle/arch/test
5. 配置的listener.ora和tnsnames.ora文件同主库
pc02:/orabak&more /g01/app/grid_home/network/admin/listener.ora
# listener.ora Network Configuration File: /g01/app/grid_home/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = pc02)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /g01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
pc02:/orabak&
pc02:/u01/app/oracle/product/11.2.0/db_1/network/admin>more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
SGERP5 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pc02)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sgerp5)
)
)
SG01=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=129.100.253.62)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=sg01)
)
)
SG02=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=129.100.253.91)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=sg02)
)
)
重起监听并检验是否可以tnsping通
6. 启动到nomount状态
SQL> startup nomount
7. 用RMAN的duplicate功能创建standby database，11g可以不做备份直接复制到备库：
Duplicate只适合库不大的情况。这次实验的库比较大没有采用这种方法，例如
RMAN> rman auxiliary /
connect target sys/test@test_db_01
duplicate target database for standby dorecover nofilenamecheck;
检查standby database的状态
SQL> select database_role,protection_mode,protection_level from v$database;
SQL> select sequence# ,applied from v$archived_log order by sequence#;
8.开始Redo应用
主库
SQL> alter system archive log current;
备库11g可以只读打开库应用日志
SQL> alter database open read only;
SQL> alter database recover managed standby database disconnect from session;
也可以使用alter database recover managed standby databaseusing current logfile disconnect from session;应用日志。
没有using current logfile是去apply standby archived log.
有using current logfile是当日志传到standby redo log的时候就去应用，是real-time的，必须要有standby redo log.
最后验证归档和数据都能过去，因为用的是归档的方式，所以数据不会实时同步，如一个事务太大，需要多个归档都传过去之后，才能应用到数据库中。
pc01:/oraarch>ll
total 4829632
-rw-r----- 1 oracle asmadmin 31847424 Mar 6 22:38 ARC_ERP5_37_809346321_1.log
-rw-r----- 1 oracle asmadmin 1264640 Mar 6 22:47 ARC_ERP5_38_809346321_1.log
-rw-r----- 1 oracle asmadmin 20992 Mar 6 22:53 ARC_ERP5_39_809346321_1.log
-rw-r----- 1 oracle asmadmin 4096 Mar 6 22:53 ARC_ERP5_40_809346321_1.log
-rw-r----- 1 oracle asmadmin 1024 Mar 6 22:54 ARC_ERP5_41_809346321_1.log
-rw-r----- 1 oracle asmadmin 23451648 Mar 7 13:14 ARC_ERP5_42_809346321_1.log
-rw-r----- 1 oracle asmadmin 1059840 Mar 7 13:15 ARC_ERP5_43_809346321_1.log
-rw-r----- 1 oracle asmadmin 11354624 Mar 7 15:45 ARC_ERP5_44_809346321_1.log
-rw-r----- 1 oracle asmadmin 28672 Mar 7 15:50 ARC_ERP5_45_809346321_1.log
-rw-r----- 1 oracle asmadmin 2072098816 Mar 7 16:02 ARC_ERP5_46_809346321_1.log
-rw-r----- 1 oracle asmadmin 1954824704 Mar 7 16:04 ARC_ERP5_47_809346321_1.log
-rw-r----- 1 oracle asmadmin 844691456 Mar 7 18:24 ARC_ERP5_48_809346321_1.log
pc01:/oraarch>
pc02:/oraarch>ll
total 4797220
-rw-r----- 1 oracle oinstall 23451648 Mar 7 13:15 ARC_ERP5_42_809346321_1.log
-rw-r----- 1 oracle oinstall 1059840 Mar 7 13:16 ARC_ERP5_43_809346321_1.log
-rw-r----- 1 oracle oinstall 11354624 Mar 7 15:46 ARC_ERP5_44_809346321_1.log
-rw-r----- 1 oracle oinstall 28672 Mar 7 15:51 ARC_ERP5_45_809346321_1.log
-rw-r----- 1 oracle oinstall 2072098816 Mar 7 16:07 ARC_ERP5_46_809346321_1.log
-rw-r----- 1 oracle oinstall 1954824704 Mar 7 16:09 ARC_ERP5_47_809346321_1.log
-rw-r----- 1 oracle oinstall 844691456 Mar 7 18:26 ARC_ERP5_48_809346321_1.log
pc02:/oraarch>
134
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
