windows 2008 R2中oracleDG最详细主备搭建，已测试通过

最新推荐文章于 2024-08-22 19:54:37 发布

梦懵啊

最新推荐文章于 2024-08-22 19:54:37 发布

阅读量1.7k

点赞数 1

分类专栏：运维搭建文章标签：运维数据库 windows

本文链接：https://blog.csdn.net/weixin_45634352/article/details/116130990

版权

运维搭建专栏收录该内容

2 篇文章 0 订阅

订阅专栏

windows 2008 R2中oracleDG最详细主备搭建，已测试通过

一、环境介绍
二、安装前要求配置
三、开始安装dataguard主从库

一、环境介绍

通过VMware虚拟机和windows镜像进行搭建
1、服务器环境：Windows server 2008 R2 x64 Standard 两台可用镜像搭建
2、软件版本 Oracle 64位 11.2.0.1.0

二、安装前要求配置

1、IP地址配置要求
主库IP：192.168.1.101
备库IP：192.168.1.102
注意：IP要处于同一网段下，通过ping命令保证主机可以互相ping通

2、oracle主备安装要求
主库：安装oracle软件，并创建数据库
备库：仅安装oracle软件
注意：备库不安装数据库是因为要把主库的数据库整个复制到备库

3、安装位置
主库
oracle 软件：C:\app\Administrator\product\11.2.0\dbhome_1
数据库 orcl：C:\app\Administrator\oradata\orcl
默认就行，磁盘看空间大小自行更换
备库：
oracle 软件：C:\app\Administrator\product\11.2.0\dbhome_1

4、数据库实例预设：
主库：数据库实例 orcl （已创建）
备库：数据库实例 orcl2 （未创建）
注意：数据库就相当于一个实例

三、开始安装dataguard主从库

1、主库安装要开启日志归档模式
连接数据库
C:\Users\Administrator>sqlplus / as sysdba
查看是否是归档模式
archive log list
关闭数据库
shutdown immediate
启动到mount状态
startup mount
开启归档模式
alter database archivelog;
开启强制日志模式
alter database force logging;
2、为备库添加日志组（在主库mount状态下执行）
注意：
oracle启动分为三步：
nomount --根据参数文件启动实例（instance）
mount --加载控制文件，让实例和数据库相关联
open --根据控制文件找到并打开数据文件和日志文件，从而打开数据库

查看当前主库日志组：
SQL> select * from v$logfile order by group#;
增加日志组

alter database add standby logfile group 4 'E:\app\Administrator\oradata\orcl\redo04.log' size 50m;
alter database add standby logfile group 5 'E:\app\Administrator\oradata\orcl\redo05.log' size 50m;
alter database add standby logfile group 6 'E:\app\Administrator\oradata\orcl\redo06.log' size 50m;
alter database add standby logfile group 7 'E:\app\Administrator\oradata\orcl\redo07.log' size 50m;

再次查看当前主库日志组：
SQL> select * from v$logfile order by group#;

3、主备库配置网络监听
修改文件listener.ora, tnsnames.ora
位置在E:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN目录下
（根据实际安装位置）
主库：
listener.ora文件

# listener.ora Network Configuration File: E:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = E:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = E:\app\Administrator\product\11.2.0\dbhome_1)
      (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = E:\app\Administrator

tnsnames.ora文件

# tnsnames.ora Network Configuration File: E:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

orcl2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl2)
    )
  )

备库：
listener.ora文件

# listener.ora Network Configuration File: E:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = E:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl2)
      (ORACLE_HOME = E:\app\Administrator\product\11.2.0\dbhome_1)
      (SID_NAME = orcl2)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = E:\app\Administrator

tnsnames.ora文件

# tnsnames.ora Network Configuration File: E:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

orcl2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl2)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

4、重启监听服务
主库：
C:\Users\Administrator>lsnrctl stop
C:\Users\Administrator>lsnrctl start

备库：
C:\Users\Administrator>lsnrctl stop
C:\Users\Administrator>lsnrctl start

tnsping检查监听及服务命名
主库
C:\Users\Administrator>tnsping orcl
C:\Users\Administrator>tnsping orcl2
备库
C:\Users\Administrator>tnsping orcl
C:\Users\Administrator>tnsping orcl2

6、主备库创建相关目录，详述如下
主库创建目录：
F:\oracle_db_arch\arch　　　　--主库归档日志位置
F:\oracle_db_arch\arch_std　　--主库转为备库时，归档日志位置
从库创建目录：
F:\oracle_db_arch\arch　　　　 ----归档日志位置
F:\oracle_db_arch\arch_std　　　　 --备库转为主库时，归档日志位置
E:\app\Administrator\oradata\djl　　　　 --数据文件位置
E:\app\Administrator\admin\djl\adump　　　 --审计文件位置
E:\app\Administrator\flash_recovery_area\djl\ONLINELOG　　　　--联机重做位置
注意文件名不能错

7、为主备库准备参数文件
主库
查看日志组
select * from v$logfile order by group#;
创建参数文件
SQL> create pfile from spfile;
注：执行create语句之后
在E:\app\Administrator\product\11.2.0\dbhome_1\database
目录下会出现文件INITorcl.ORA,用笔记本打开进行编辑主库INITorcl.ORA（看你具体安装位置）

orcl.__db_cache_size=1124073472
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='E:\app\Administrator'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=1040187392
orcl.__sga_target=1543503872
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=352321536
orcl.__streams_pool_size=0
*.audit_file_dest='E:\app\Administrator\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='E:\app\Administrator\oradata\orcl\control01.ctl','E:\app\Administrator\flash_recovery_area\orcl\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='E:\app\Administrator\flash_recovery_area'
*.db_recovery_file_dest_size=4102029312
*.diagnostic_dest='E:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=2577399808
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'


*.db_unique_name=orcl
*.log_archive_config='dg_config=(orcl,orcl2)'
*.log_archive_dest_1='LOCATION=F:\oracle_db_arch\arch valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.log_archive_dest_2='SERVICE=orcl2 lgwr async affirm valid_for=(online_logfiles,primary_role) db_unique_name=orcl2'
*.log_archive_dest_3='LOCATION=F:\oracle_db_arch\arch_std valid_for=(standby_logfiles,standby_role) db_unique_name=orcl'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_dest_state_3='enable'
*.fal_server=orcl2
*.fal_client=orcl
*.standby_file_management=auto
*.db_file_name_convert=('E:\app\Administrator\oradata\orcl2','E:\app\Administrator\oradata\orcl')
*.log_file_name_convert=('E:\app\Administrator\oradata\orcl2','E:\app\Administrator\oradata\orcl')

将主库initorcl.ora复制到备库对应目录下并修改内容如下
更改文件名 INITdjl.ORA
备库INITdjl.ORA

orcl.__db_cache_size=1124073472
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='E:\app\Administrator'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=1040187392
orcl.__sga_target=1543503872
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=352321536
orcl.__streams_pool_size=0
*.audit_file_dest='E:\app\Administrator\admin\orcl2\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='E:\app\Administrator\oradata\orcl2\control01.ctl','E:\app\Administrator\flash_recovery_area\orcl2\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='E:\app\Administrator\flash_recovery_area'
*.db_recovery_file_dest_size=4102029312
*.diagnostic_dest='E:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)','(address=(protocol=tcp)(host=192.168.1.102))(dispatchers=4)','(address=(protocol=ipc)(host=192.168.1.102))(dispatchers=2)'
*.memory_target=2577399808
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'


*.db_unique_name=orcl2
*.log_archive_config='dg_config=(orcl2,orcl)'
*.log_archive_dest_1='LOCATION=F:\oracle_db_arch\arch valid_for=(online_logfiles,primary_role) db_unique_name=orcl2'
*.log_archive_dest_2='SERVICE=orcl lgwr async affirm valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.log_archive_dest_3='LOCATION=F:\oracle_db_arch\arch_std valid_for=(standby_logfiles,standby_role) db_unique_name=orcl2'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_dest_state_3='enable'
*.fal_server=orcl
*.fal_client=orcl2
*.standby_file_management=auto
*.db_file_name_convert=('E:\app\Administrator\oradata\orcl','E:\app\Administrator\oradata\orcl2')
*.log_file_name_convert=('E:\app\Administrator\oradata\orcl','E:\app\Administrator\oradata\orcl2')

8、密码文件配置
将主库的参数文件PWDorcl.ora复制到备库对应目录下，并重命名为PWDdjl.ora
路径位置：E:\app\Administrator\product\11.2.0\dbhome_1\database

9、控制文件配置
将主库的参数文件control01.ctl和control02.ctl复制到备库参数文件中指定位置下

10、创建备库实例djl
备库操作：oradim -new -sid djl

11、主备库的启动
主库：
登录sqlplus在showdown状态下创建参数文件
查看oracle 启动状态
select status from v$instance;
关闭数据库
shutdown immediate
创建参数文件
create spfile from pfile;
启动数据库
startup
备库：
登录sqlplus会出现
ERROR:
ORA-12560: TNS: 协议适配器错误
解决办法：
set oracle_sid=djl
在进行登录
sqlplus / as sysdba

备库创建参数文件
SQL> create spfile from pfile;
将数据库启动到nomount(必须是nomount状态)
SQL> startup nomount

12、使用rman连接主备库
在使用Oracle的Active Database Duplicate功能
创建物理DataGuard的过程中，连接target数据库必须“显式地指定密码”
在主库执行：
C:\Users\Administrator>rman target sys/oracle@prod auxiliary sys/oracle@sbdb
@符后是实例名，orcl,orcl2

注意：连接前使用rman登录主备库做测试
13、为备库备份参数文件
RMAN> backup current controlfile for standby database;

14、将主库数据库复制到备库
RMAN> duplicate target database for standby from active database;
结尾出现：完成 Duplicate Db 于 27-8月 -19
退出RMAN:
RMAN>exit

15、将备库设置为接受日志模式
说明：此时，主库为open状态，备库为mounted
查看主备库状态
SQL> select status from v$instance;
备库：

在备库执行以下语句
将备库修改为接收应用主库归档的模式或改为可以实时同步主库的更新。
SQL> alter database recover managed standby database disconnect from session;
使用using current logfile参数，可以实时同步主库的更新。
alter database recover managed standby database using current logfile disconnect from session;
Database altered.

16、日志验证dataguard：
主库：
E:\app\Administrator\diag\rdbms\orcl\orcl\trace
LNS 正在使用日志序列