作者:ikmb@163.com
测试环境:
OS:Win 2008 EE R2
Oracle:11G R2 EE
测试过程:
1、两机同时open
主库插入commit后,备库能实时看到
2、主库open,备库down后
主库能正常插入commit
备库重记后,主备会变为maximize performance,重设主库为maximize availability,以只读打开备库,再重设备库为maximize availability
主
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect from session;
备
startup mount;
alter database open read only;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect from session;
3、主库down,备库open后
主
startup
备
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect from session;
备实时应用和就与主库同步了
4、将备库转为主库
转换standby 角色为primary
alter database recover managed standby database finish force;
alter database commit to switchover to primary;
alter database open;
5、备库转为主库后再创建备库(以下提到主库为以前备库)
确保有备库重做日志
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
查看主库归档路径
show parameter log_archive_dest;
主库
log_archive_dest_1='location=E:/app/Administrator/product/11.2.0/dbhome_1/database/archive MANDATORY'
LOG_ARCHIVE_DEST_2='SERVICE=ydgt LGWR SYNC AFFIRM REOPEN=1800 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ydgt'
确保数据库处于归档模式:
archive log list;
在主库上面创建从库的初始化参数文件ydgt.ora(将其拷贝回从库后转换成从库的 spfile):
create pfile='D:/backup/ydgt.ora' from spfile;
shutdown immediate;
将主库E:/app/Administrator/product/11.2.0/dbhome_1/database/PWDstandby.ora拷到d:/backup/PWDydgt.ora
将主库E:/app/Administrator/oradata/ydgt内除了ctl文件全部拷贝到d:/backup/standby
重设主库
create pfile='E:/app/Administrator/product/11.2.0/dbhome_1/database/initstandby.ora' from spfile;
修改以下内容:
FAL_SERVER='standby';
FAL_CLIENT='ydgt';
LOG_ARCHIVE_CONFIG='DG_CONFIG=(standby,ydgt)';
DB_FILE_NAME_CONVERT='E:/app/Administrator/oradata/standby','E:/app/Administrator/oradata/ydgt';
LOG_FILE_NAME_CONVERT='E:/app/Administrator/oradata/standby','E:/app/Administrator/oradata/ydgt';
shutdown immediate;
create spfile from pfile='E:/app/Administrator/product/11.2.0/dbhome_1/database/initstandby.ora';
在主库上面创建从库的控制文件ydgt.ctl :
startup
alter database create standby controlfile as 'D:/backup/ydgt.ctl';
在从库中创建目录:
E:/app/Administrator/oradata/ydgt
E:/app/Administrator/flash_recovery_area
E:/app/Administrator/admin/ydgt/adump
E:/app/Administrator/admin/ydgt/pfile
文件拷贝:
将主库d:/backup/ydgt/内文件拷贝到从库E:/app/Administrator/oradata/ydgt/
将主库d:/backup/PWDydgt.ora拷贝到从库e:/app/Administrator/product/11.2.0/dbhome_1/database/PWDydgt.ora
将主库D:/backup/ydgt.ora拷贝到从库e:/
将主库D:/backup/ydgt.ctl拷贝到从库E:/app/Administrator/oradata/ydgt/
修改两主机host
192.168.0.227 ydgt
192.168.0.228 standby
在备库创建实例:
oradim -new -sid ydgt -startmode manual
在备库上运行netca,将创建listener.ora、tnsnames.ora 和监听服务
修改两主机listener.ora中的host为ydgt和standby
修改主库listener.ora中SID_LIST_LISTENER
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = standby)
(ORACLE_HOME = E:/app/Administrator/product/11.2.0/dbhome_1)
)
)
修改备库listener.ora中SID_LIST_LISTENER
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = ydgt)
(ORACLE_HOME = E:/app/Administrator/product/11.2.0/dbhome_1)
)
)
重启两库listener
在主从库的tnsnames.ora内容为:
YDGT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.227)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ydgt)
)
)
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.228)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)
修改从库e:/ydgt.ora ,里面的全部内容如下(删除了原来的control_files参数,将instance_name改成了STANDBY):
*.audit_file_dest='E:/app/Administrator/admin/ydgt/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_cache_size=1543503872
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ydgt'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ydgtXDB)','(PROTOCOL=TCP)'
*.fast_start_mttr_target=300
*.instance_name='ydgt'
*.java_pool_size=16777216
*.job_queue_processes=10
*.large_pool_size=16777216
*.sga_target=2063597568
*.log_archive_dest_1='location=E:/app/Administrator/product/11.2.0/dbhome_1/database/archive MANDATORY'
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=1375731712
*.memory_target=3430940672
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=452984832
*.star_transformation_enabled='FALSE'
*.undo_tablespace='UNDOTBS1'
*.standby_file_management=AUTO
*.lock_name_space='ydgt'
*.control_files='E:/app/Administrator/oradata/ydgt/YDGT.CTL'
db_unique_name=ydgt
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(standby,ydgt)'
*.DB_FILE_NAME_CONVERT='E:/app/Administrator/oradata/standby','E:/app/Administrator/oradata/ydgt'
*.LOG_FILE_NAME_CONVERT='E:/app/Administrator/oradata/standby','E:/app/Administrator/oradata/ydgt'
*.LOG_ARCHIVE_FORMAT=ydgt%t_%s_%r.arc
*.LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR SYNC AFFIRM REOPEN=1800 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.FAL_SERVER=standby
*.FAL_CLIENT=ydgt
*.STANDBY_FILE_MANAGEMENT=AUTO
进入sqlplus将ydgt.ora创建成从库的spfile
sqlplus /nolog
conn / as sysdba;
shutdown immediate;
Sql>create spfile from pfile='e:/ydgt.ora';
可能会出现TNS: 协议适配器错误,解决:set oracle_sid=ydgt
创建备用重做日志
startup mount;
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
比主库多一组:
alter database add standby logfile group 9 ('E:/app/Administrator/oradata/ydgt/sby_redo06.rdo') size 50M;
重启备库:
shutdown immediate;
startup mount;
只读方式打开
alter database open read only;
后台实时应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect from session;