背景：
搭建一个oracle镜像数据库,用于同步生产库数据,提供查询功能。
方案：
可使用的方案有有多种方式比如oracle adg,oracle goldengate以及DSG三种,如果镜像库不需要连接应用的话,采用adg方式比较合适。
ADG版本兼容
有关在异构平台搭建ADG的详细内容请参考文章：《在物理Data Guard 中对异构主备系统的支持(Doc ID 1602437.1)》
虽然这个案例需要跨平台，但从11g开始OracleDatabase支持有限制的跨平台配置DG，下面是配置异构平台DG的兼容性列表（物理Standby）：
1）从上面的兼容性列表可以看出，MicrosoftWindows (x86_64)(12)和Linux x86_64(13)能够兼容构建异构平台的DG，但需要注意的是OracleDatabase 需要是11g，并且需要应用Patch13104881，该Patch只存在于Linux平台，并且只在Windows（主库）向Linux（备库）同步时，需要在Linux平台应用此补丁（反向不会遇到相应的bug），更多内容请参考Patch13104881说明。
2）生产库和镜像库环境:(数据库软件版本必须一致,包括小版本)
环境：
生产库:
IP地址:10.10.10.10
操作系统:linux
数据库版本:oracle 11.2.0.3
实例名:cs
数据库名:cs
数据库db_unique_name:cs
镜像库:
IP地址:20.20.20.20
操作系统:windows server 2008
数据库版本:oracle 11.2.0.3
实例名:csb
数据库名:csb
数据库db_unique_name:csb
搭建步骤:
1.备份
为了避免手误或者疏忽弄坏数据库,开始动手之前先做备份,可使用expdp方式导出数据,同时正常关闭数据库,备份:spfile文件,controlfile文件
2.生产库进行以下配置
A.归档日志必须启用,并开启强制日志功能,添加standby日志组
startup mount;
alter database archivelog;
alter database force logging;
alter database add standby logfile group 4'/u01/app/oracle/oradata/standby04' size 100M;
alter database add standby logfile group 5'/u01/app/oracle/oradata/standby05' size 100M;
alter database add standby logfile group 6'/u01/app/oracle/oradata/standby06' size 100M;
alter database add standby logfile group 7'/u01/app/oracle/oradata/standby07' size 100M;
(日志文件大小设置和原redo文件一样,standby 尽量要比原redo数多一组)
B.创建pfile文件,并做以下修改
create pfile from spfile;
vim initcs (pfile文件的名称)
cs.__db_cache_size=8657043456
cs.__java_pool_size=67108864
cs.__large_pool_size=67108864
cs.__oracle_base='/u01/app/oracle'#ORACLE_BASE路径
cs.__pga_aggregate_target=21944598528
cs.__sga_target=12415139840
cs.__shared_io_pool_size=0
cs.__shared_pool_size=3288334336
cs.__streams_pool_size=134217728
*.audit_file_dest='/u01/app/oracle/admin/cs/adump'
*.audit_trail='db'
*.compatible='11.2.0.3.0'
*.control_files='/u01/app/oracle/oradatatest/cs/CONTROL01.ctl'#控制文件路径
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata','E:\app\Administrator\oradata\csb'
#生产库数据文件路径和镜像库数据文件路径,必须输入正确
*.db_name='cs'
*.db_unique_name='cs'
*.diagnostic_dest='/u01/app/oracle' #oracle_base路径
*.dispatchers='(PROTOCOL=TCP)(SERVICE=csXDB)'
*.fal_server='csb'
*.fal_client='cs'
*.log_archive_config='DG_CONFIG=(cs,csb)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/archivelogvalid_for=(all_logfiles,primary_role) db_unique_name=cs' #生产库归档路径,需要提前创建
*.log_archive_dest_2='SERVICE=csb lgwr syncvalid_for=(online_logfile,primary_role) db_unique_name=csb'
*.log_archive_dest_3='LOCATION=/u01/app/oracle/oradata/standbylogvalid_for=(standby_logfile,standby_role) db_unique_name=cs'#生产库standby路径,需要提前创建
*.log_archive_dest_state_3='defer'
*.log_file_name_convert='/u01/app/oracle/oradata','E:\app\Administrator\oradata\csb'#生产库redo文件路径和镜像库redo文件路径
*.memory_target=34359738368
*.open_cursors=300
*.processes=550
*.log_archive_format='%t_%s_%r.dbf'
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS02'
*.standby_file_management='auto'
C.配置静态监听和tnsnames
[oracle@zhujianting-db1 admin]$ catlistener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = zhujianting-db1)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cs)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = cs)
)
)
Tnsname.ora文件
CS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 221.207.30.118)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID=cs)
(SERVICE_NAME=cs)
)
)
CSB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.7.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = csb)
)
)
3.镜像库操作步骤
A.安装好软件创建一个实例(此案例中为csb)
B.修改监听和tnsnames
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = csb)
(ORACLE_HOME = D:\app\Administrator\product\11.2.0.3\dbhome_1)
(SID_NAME = csb)
(ENVS ="EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0.3\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = WIN-PQO0LK35F91)(PORT = 1521))
)
)
配置tnsnames.ora文件
CS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = cs)
(SERVICE_NAME = cs)
)
)
CSB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =20.20.20.20)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = csb)
)
)
C.复制生产库的pfile文件(文件名改为initcsb.ora),修改内容
cs.__db_cache_size=8657043456
cs.__java_pool_size=67108864
cs.__large_pool_size=67108864
cs.__oracle_base='D:\app\Administrator'#ORACLE_BASEset from environment
cs.__pga_aggregate_target=21944598528
cs.__sga_target=12415139840
cs.__shared_io_pool_size=0
cs.__shared_pool_size=3288334336
cs.__streams_pool_size=134217728
*.audit_file_dest='D:\app\Administrator\admin\csb\adump'
*.audit_trail='db'
*.compatible='11.2.0.3.0'
*.control_files='E:\app\Administrator\oradata\csb\CONTROL01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata','E:\app\Administrator\oradata\csb'
*.db_name='cs'
*.db_unique_name='csb'
*.diagnostic_dest='E:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=csXDB)'
*.fal_server='cs'
*.fal_client='csb'
*.log_archive_config='DG_CONFIG=(cs,csb)'
*.log_archive_dest_1='LOCATION=E:\app\Administrator\oradata\csb\archivelogvalid_for=(all_logfiles,primary_role) db_unique_name=csb'
*.log_archive_dest_2='SERVICE=cs lgwr syncvalid_for=(online_logfile,primary_role) db_unique_name=cs'
*.log_archive_dest_3='LOCATION=E:\app\Administrator\oradata\csb\standbylogvalid_for=(standby_logfile,standby_role) db_unique_name=csb'
*.log_archive_dest_state_3='enable'
*.log_file_name_convert='/u01/app/oracle/oradata','E:\app\Administrator\oradata\csb'
*.memory_target=34359738368
*.open_cursors=300
*.processes=550
*.log_archive_format='%t_%s_%r.dbf'
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS02'
*.standby_file_management='auto'
到此基本的文件参数配置以及完成,重新启动监听
4.生产库使用pfile文件启动,并创建spfile
startup pfile=’路径文件名’
create spfile from pfile=’路径文件名’;
镜像库使用pfile文件启动到nomount状态
startup nomount pfile=’路径文件名’;
create spfile from pfile=’路径文件名’;
5.开始数据库复制
rman target sys/oracle@csauxiliary sys/oracle@csb
duplicatetarget database for standby from active database;
这一个过程会持续很长时间,要有耐心(alter system archive log current)
6.等待数据库复制完成,登录镜像库
sqlplus sys/oracle@csb as sysdba
查看当前镜像库状态
select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
MOUNTED PHYSICAL STANDBY csb
启动数据库
alter database open;
再次查询镜像库状态
select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ ONLY PHYSICAL STANDBY csb
开启镜像库同步
alter database recover managed standbydatabase using current logfile disconnect from session;
查询镜像库状态,同步已经开启
select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ ONLY WITH APPLY PHYSICAL STANDBY csb
7、验证
可以查看生产库和主库归档文件序号是否一致
archive log list;
在生产库上插入数据或者创建测试表,在镜像库上查询是否同步过来.
长按下图识别二维码或微信扫描下图二维码来关注甲骨人的微信公众号：甲骨人，学习最实用的数据库技术，获取数据库顶级认证，成为oracle技术专家
