DataGuard安装配置
1、 安装Windows2003标准版
DataGuard主数据库服务器:内存3GB,硬盘646GB,IP:172.10.1.11,机器名:dbserver01,创建D:\ORACLE_DATA\Arch目录
DataGuard备数据库服务器:内存3GB,硬盘646GB,IP:172.10.1.12,机器名:dbserver02,创建D:\ORACLE_DATA\Arch目录
2、 注意:关闭防火墙,设置->控制面板->管理工具->本地安全策略->本地策略->用户权利指派->作为批处理作业登录->添加帐号
3、 在主备服务器安装Oracle10g软件
注意:只安装软件,不安装数据库
4、 创建主备数据库
全局数据库名:sbnetdb,SID:sbnetdb,快速恢复区大小:2048MB,启用归档:D:\ORACLE_DATA\Arch,数据库字符集,国家字符集默认,配置监听和net服务名
配置listener.ora和tnsnames.ora
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = sbnetdb)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = sbnetdb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver01)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
tnsnames.ora
sbnetdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.10.1.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sbnetdb)
)
)
sbnetdbS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.10.1.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sbnetdb)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
5、 配置主备数据库
1、 设置数据库为force logging 模式
alter database force logging;
2、创建口令文件
D:\>orapwd file=D:\oracle\product\10.2.0\db_1\database\PWDsbnetdb.ora password=sys entries=20
3、创建备重做日志
SQL> alter database add standby logfile group 4 ('d:\ORACLE_DATA\Data\sbnetdb\sredo1_41.log', 'd:\ORACLE_DATA\Data\sbnetdb\sredo1_42.log')size 50m;
SQL> alter database add standby logfile group 5 ('d:\ORACLE_DATA\Data\sbnetdb\sredo1_51.log', 'd:\ORACLE_DATA\Data\sbnetdb\sredo1_52.log')size 50m;
SQL> alter database add standby logfile group 6 ('d:\ORACLE_DATA\Data\sbnetdb\sredo1_61.log', 'd:\ORACLE_DATA\Data\sbnetdb\sredo1_62.log')size 50m;
SQL> alter database add standby logfile group 7 ('d:\ORACLE_DATA\Data\sbnetdb\sredo1_71.log', 'd:\ORACLE_DATA\Data\sbnetdb\sredo1_72.log')size 50m;
select * from v$logfile;
select group#, thread#, sequence#, archived, status from v$standby_log;
6、 设置主数据库初始化参数
使用spfile创建pfile
create pfile from spfile;
注意:Data Guard中所有数据库DB_NAME相同;log_archive_config的dg_config为db_unique_name;log_archive_dest_2的service为Oracle Net服务名
主数据库初始化参数中增加:
log_archive_config='dg_config=(sbnetdb,sbnetdbs)'
*.log_archive_dest_1='LOCATION=D:\ORACLE_DATA\ARCH valid_for=(all_logfiles,all_roles) db_unique_name=sbnetdb'
log_archive_dest_2='service=sbnetdbs lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=sbnetdbs'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='defer'
db_unique_name='sbnetdb'
fal_server='sbnetdbs'
fal_client='sbnetdb'
standby_file_management='auto'
log_file_name_convert='D:\ORACLE_DATA\DATA\sbnetdb','D:\ORACLE_DATA\DATA\sbnetdb'
7、 在主数据库创建备数据库控制文件
SQL> alter database create standby controlfile as 'c:\standby.ctl';
将standby.ctl拷贝到备数据库并重命名为control01,control02,control03
8、 将主数据库数据文件、初始化参数文件拷贝到备数据库。
shutdown immediate 两个数据库
9、 修改备数据库初始化参数文件
db_unique_name='sbnetdbs'
log_archive_dest_1='LOCATION=e:\oracle_data\arch valid_for=(all_logfiles,all_roles) db_unique_name=sbnetdbs'
log_archive_dest_2='service=sbnetdb lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=sbnetdb'
log_archive_dest_state_2='enable'
fal_server='sbnetdb'
fal_client='sbnetdbs'
10、 在主数据库创建spfile并启动主数据库
SQL> create spfile from pfile='D:\oracle\product\10.2.0\db_1\database\INITsbnetdb.ora';
startup
11、 在备数据库创建spfile并启动备数据库
create spfile from pfile='D:\oracle\product\10.2.0\db_1\database\INITsbnetdb.ora';
startup mount
12、 将主数据库的重做日志传输给备用数据库
alter system set log_archive_dest_state_2='enable' scope=both;
13、 在备数据库启动重做应用
alter database recover managed standby database disconnect from session;
14、 在主数据库检查日志文件是否成功传输
select status,error from v$archive_dest where dest_id=2;
select sequence#, first_time, next_time from v$archived_log order by sequence#;
select sequence#,applied from v$archived_log order by sequence#;
15、设置主库为最大可用模式
shutdown immediate
startup mount
alter database set standby to maximize availability;
alter database open;
select protection_mode, protection_level from v$database;
主备切换测试
在主执行:
select switchover_status from v$database; 应为session active
alter database commit to switchover to physical standby with session shutdown;
shutdown immediate
startup mount
alter database recover managed standby database disconnect from session;
然后,在备数据库执行:
alter database commit to switchover to primary;
shutdown immediate
startup