配置过程中注意事项
一、备库配置完静态监听和tns文件后要注册oracle服务到services.msc
添加服务进程 oradim -new -sid 实例名 -startmode auto
二、备库的pfile中的dB_name要是主库的名字，不要写成备库的名字
三、sqlnet.ora配置添加如下内容
1.设置windows下允许使用windows nt本地认证
SQLNET.AUTHENTICATION_SERVICES=(nts)
2.设置Oracle客户端连接串命名查找顺序
NAMES.DIRECTORY_PATH=(tnsnames, ezconnect)
实战操作
| PROD1 | 192.168.44.81 | windows 2012 r2 | oracle 11.2.0.4 |
| SBDB1 | 192.168.44.82 | windows 2012 r2 | oracle 11.2.0.4 |
1、 主库：开启归档模式
SYS@PROD1> archive log list;
SYS@PROD1> show parameter recovery;
SYS@PROD1> alter system set db_recovery_file_dest_size=4g;
SYS@PROD1> alter system set db_recovery_file_dest='D:\app\flash' scope=spfile;
SYS@PROD1> shutdown immediate;
SYS@PROD1> startup mount;
SYS@PROD1> alter database archivelog;
SYS@PROD1> alter database open;
SYS@PROD1> archive log list;
2、主库：开启强制写日志功能并关闭闪回，如果有外部表要删除外部表。
SYS@PROD1> alter database force logging;
SYS@PROD1> show parameter diagnostic_dest;
SYS@PROD1> alter database flashback off;
3、主库：配置静态监听，编辑D:\App\administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
#注意监听书写的格式
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 主库主机名或者IP)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(ORACLE_HOME=D:\App\administrator\product\11.2.0\dbhome_1)
(SID_NAME=PROD1)))
主库重启监听
lsnrctl stop
lsnrctl start
lsnrctl status
如下图例子：
4、主库：配置 tnsnames 文件：
注意tns文件书写格式
SBDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 备库主机名或者IP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SBDB1)
)
)
5、主库：增加 standby logfile 文件
查询redo所在路径
SYS@PROD1> select group#, member from v$logfile;
查询redo文件大小，standby logfile要与redo log大小一致并且数量要大于等于redo log数量
SYS@PROD1> select bytes/1024/1024 "Size MB" from v$log;
创建standby logfile
alter database add standby logfile 'D:\app\Administrator\oradata\PROD1\redo04.log' size 50m;
alter database add standby logfile 'D:\app\Administrator\oradata\PROD1\redo05.log' size 50m;
alter database add standby logfile 'D:\app\Administrator\oradata\PROD1\redo06.log' size 50m;
查询创建好的standby logfile
SYS@PROD1> select group#, member, type from v$logfile where type='STANDBY';
6、修改主库参数文件pfile，如下例
SYS@PROD1> create pfile from spfile;
SYS@PROD1> shutdown immediate;
编辑参数文件：
D:\app\Administrator\product\11.2.0\dbhome_1\database\INIT+SID.ORA
主库参数文件内容如下：
PROD1.__db_cache_size=356515840
PROD1.__java_pool_size=4194304
PROD1.__large_pool_size=8388608
PROD1.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
PROD1.__pga_aggregate_target=343932928
PROD1.__sga_target=515899392
PROD1.__shared_io_pool_size=0
PROD1.__shared_pool_size=130023424
PROD1.__streams_pool_size=4194304
*.audit_file_dest='C:\app\Administrator\admin\PROD1\adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='D:\app\Administrator\oradata\PROD1\control01.ctl','D:\app\Administrator\fast_recovery_area\PROD1\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PROD1'
*.db_recovery_file_dest='D:\app\Administrator\fast_recovery_area'
*.db_recovery_file_dest_size=4294967296
*.diagnostic_dest='D:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PROD1XDB)'
*.local_listener='LISTENER_PROD1'
*.memory_target=858783744
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=PROD1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD1,SBDB1)'
LOG_ARCHIVE_DEST_1=
'LOCATION=D:\app\Administrator\flash
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PROD1'
LOG_ARCHIVE_DEST_2=
'SERVICE=SBDB1 ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=SBDB1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=SBDB1
DB_FILE_NAME_CONVERT='SBDB1','PROD1'
LOG_FILE_NAME_CONVERT='SBDB1','PROD1'
STANDBY_FILE_MANAGEMENT=AUTO
7、配置备库standby的监听
D:\App\administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
#注意监听书写的格式
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 备库主机名或者IP)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(ORACLE_HOME=D:\App\administrator\product\11.2.0\dbhome_1)
(SID_NAME=SBDB1)))
重启监听
lsnrctl stop
lsnrctl start
lsnrctl status
8、备库：配置 tnsnames 文件
注意tns文件书写格式
PROD1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 主库主机名或者IP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD1)
)
)
9、备库：主库服务器拷贝主库参数文件、密码文件到备库主机对应路径并改名
INIT+主库SID.ORA ========> INIT+备库SID.ORA
PWD+主库SID ========> PWD+备库SID
10、备库：修改备库参数文件pfile：（将原始的 PROD1 和 SBDB1 位置进行调换）
注意：参数文件中的DB_NAME要是主库name也就是PROD1
备库参数文件内容如下：
SBDB1.__db_cache_size=356515840
SBDB1.__java_pool_size=4194304
SBDB1.__large_pool_size=8388608
SBDB1.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
SBDB1.__pga_aggregate_target=343932928
SBDB1.__sga_target=515899392
SBDB1.__shared_io_pool_size=0
SBDB1.__shared_pool_size=130023424
SBDB1.__streams_pool_size=4194304
*.audit_file_dest='D:\app\Administrator\admin\SBDB1\adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='D:\app\Administrator\oradata\SBDB1\control01.ctl','D:\app\Administrator\fast_recovery_area\SBDB1\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PROD1' ####################注意db_name要是主库名###################
*.db_recovery_file_dest='D:\app\Administrator\fast_recovery_area'
*.db_recovery_file_dest_size=4294967296
*.diagnostic_dest='D:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SBDB1XDB)'
*.local_listener='LISTENER_SBDB1'
*.memory_target=858783744
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=SBDB1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(SBDB1,PROD1)'
LOG_ARCHIVE_DEST_1=
'LOCATION=D:\app\Administrator\flash
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=SBDB1'
LOG_ARCHIVE_DEST_2=
'SERVICE=PROD1 ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PROD1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=PROD1
DB_FILE_NAME_CONVERT='PROD1','SBDB1'
LOG_FILE_NAME_CONVERT='PROD1','SBDB1'
STANDBY_FILE_MANAGEMENT=AUTO
11、备库：备库主机根据参数文件创建相应的目录
查看备库参数文件中相关路径。
12、主库：通过 rman duplicate 方式进行备库恢复
备库启动到no mount状态
在主库服务器上登录
rman target auxiliary sys/oracle@SBDB1
RMAN> duplicate target database for standby from active database;
输出内容太多不粘贴了。
13、验证DG是否搭建成功
主库验证
SYS@PROD1> select DATABASE_ROLE from v$database;
备库验证
SYS@SBDB1> select DATABASE_ROLE from v$database;
SYS@SBDB1> select open_mode from v$database;
14、备库：同步数据
登录备库
sqlplus as sysdba@SBDB1 as sysdba
SYS@SBDB1> recover managed standby database using current logfile disconnect from session;
SYS@SBDB1> recover managed standby database cancel;
SYS@SBDB1> alter database open;
SYS@SBDB1> recover managed standby database using current logfile disconnect from session;
SYS@SBDB1> select open_mode from v$database;
在主库上创建一个表并插入数据，在备库上查询新建表和数据。如果有证明测试成功。
