本文参考网络部分资料编写,作为参考笔记
环境: WINDOWS2003+Oralcle11.2.0.1
建议使用 虚拟机来模拟,这样主备两边的环境完全一样。
假设主库的机器名为PRIMARY , IP地址为192.168.1.10
备库的机器名为STANDBY , IP地址为192.168.1.20
主库:
1. 设置Listener.ora 文件,增加红色内容,目的是实现监听的静态注册。
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhsot)(PORT = 1521))
)
)
ADR_BASE_LISTENER = D:\app\Administrator
监听的静态注册主要是用于数据库不在open 状态时,也可以通过监听连接数据库。
2. 设置TNSMAE.ORA 文件
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.20)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
3. 设置SQLNET.ORA文件
SQLNET.AUTHENTICATION_SERVICES= (NONE)
4. 设置密码文件
$ORACLE_HONE\database\目录下的orapwd.ora 文件
5. 建立归档目录 D:\archive
将此时的主机做个clone, 克隆后的机器ip为192.168.1.20, 机器名为standby
此时达到的目的就是两个机器有相同的Listener.ora、TNSMAE.ORA、SQLNET.ORA和文件。备库上虽然有数据库,但不重要,可以在关闭数据库的时候,删除数据文件。
设置主库的归档和force logging 属性,增加standby redo log(假设redo log 原来只有3组,文件大小为50M)
Startup mount
alter database force logging ;
alter database archivelog;
alter database add standby logfile group 4 'D:\app\Administrator\oradata\orcl\redo04.log' size 50M ;
alter database add standby logfile group 5 'D:\app\Administrator\oradata\orcl\redo05.log' size 50M ;
alter database add standby logfile group 6 'D:\app\Administrator\oradata\orcl\redo06.log' size 50M ;
修改spfile,中的参数:
*.db_name='orcl'
*.log_archive_format='ARC_%T%S%r.ARC'
*.db_unique_name='orcl'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=D:\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primary'
*.log_archive_dest_2='SERVICE=standby VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLES) db_unique_name=standby'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.fal_server='standby'
*.fal_client='primary'
可以通过create pfile from spfile 方式导出pfile 文件,修改pfile 后,再 create spfile from pfile 方式完成。fal_server 据说在11GR2 已不使用,但由于是转自网上资料,先没有去除。
备库:
Startup nomount pfile=…
随便什么pfile ,只要能启动即可。
执行:
rman target sys/oracle@primary auxiliary sys/oracle@standby
运行
run
{
duplicate target database for standby from active database nofilenamecheck spfile
set db_unique_name='standby'
set control_files='D:\app\Administrator\oradata\orcl\CONTROL01.CTL'
set log_archive_max_processes='20'
set fal_server='primary'
set fal_client='standby'
set log_archive_dest_1='location=D:\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=standby'
set log_archive_dest_2='SERVICE=primary VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLES) db_unique_name=primary'
set standby_file_management='auto';
}
备库:
此时是mount 状态
alter database recover managed standby database using current logfile disconnect from session;
如果要打开数据库:
alter database recover managed standby database cancel;
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;
此时的备库就是一个只读的实时镜像。
切换:
Primary->standby
alter database commit to switchover to physical standby with session shutdown;
shutdown immediate
startup mount;
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;
standby->primary
alter database commit to switchover to primary with session shutdown;
alter database open;