oracle 11g dataguard

最新推荐文章于 2023-04-19 16:58:28 发布

星光之微

最新推荐文章于 2023-04-19 16:58:28 发布

阅读量559

点赞数

分类专栏：数据库文章标签： oracle 数据库

本文链接：https://blog.csdn.net/tangxingILoveyou/article/details/112564762

版权

数据库专栏收录该内容

31 篇文章 0 订阅

订阅专栏

一、安装准备

Oracle :Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

1、IP配置

主库IP:192.168.1.229

备库IP:192.168.1.55

2、Oracle安装要求

主库：安装oracle软件，并创建数据库

备库：仅安装oracle软件

3、安装位置

主库：

oracle 软件：D:\app\18125\product\11.2.0\dbhome_1

数据库 orcl：D:\app\18125\oradata\orcl

备库：

oracle 软件：E:\app\Administrator\product\11.2.0\dbhome_1

4、数据库实例预设

主库：数据库实例 orcl（已创建）

备库：数据库实例 djl （未创建）

二、配置dataguard主从库

1、检查数据库、开启归档模式（主库）

连接数据库

C:\Users\Administrator>sqlplus / as sysdba

查看是否是归档模式

SQL> archive log list

关闭数据库

SQL> shutdown immediate

启动到mount状态

SQL> startup mount

开启归档模式

SQL> alter database archivelog;

开启强制日志模式

SQL> alter database force logging;

2、为备库添加日志组（在主库mount状态下执行）

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

查询执行文件生成情况：

select * from v$logfile order by group#;

3、主备库配置网络监听

修改文件listener.ora, tnsnames.ora（D:\app\18125\product\11.2.0\dbhome_1\NETWORK\ADMIN）

主库：

listener.ora文件

（注意：空格字符也要对齐，否则报语法错误），自己可以用Net Configuration Assistant 创建监听，然后在细微调整。

# listener.ora Network Configuration File: d:\app\18125\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 = d:\app\18125\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:d:\app\18125\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = d:\app\18125\product\11.2.0\dbhome_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = d:\app\18125\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:d:\app\18125\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = d:\app\18125\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:d:\app\18125\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = d:\app\18125\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:d:\app\18125\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

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

ADR_BASE_LISTENER = d:\app\18125

tnsnames.ora文件

# tnsnames.ora Network Configuration File: d:\app\18125\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_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.229)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

djl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.55)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = djl)
    )
  )

备库

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 = djl)
      (ORACLE_HOME = E:\app\Administrator\product\11.2.0\dbhome_1)
      (SID_NAME = djl)
    )
  )

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

ADR_BASE_LISTENER = E:\app\Administrator

tnsnames.ora文件

# tnsnames.ora Network Configuration File: d:\app\18125\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)
    )
  )

djl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.55)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = djl)
    )
  )
  
orcl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.229)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

4、重启监听服务

主库：

C:\Users\Administrator>lsnrctl stop

C:\Users\Administrator>lsnrctl start

备注：直接运行指令会报错，解决方案

备库：

5、使用tnsping检查监听及服务命名

主库：

C:\Users\Administrator>tnsping orcl

C:\Users\Administrator>tnsping djl

备库

C:\Users\Administrator>tnsping orcl

C:\Users\Administrator>tnsping djl

6、主备库创建相关目录，详述如下

主库创建目录：

D:\oracle_db_arch\arch　　　　--归档日志位置
D:\oracle_db_arch\arch_std　　--主库转为备库时，归档日志位置

从库创建目录：

E:\oracle_db_arch\arch　　　　 ----归档日志位置
E:\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语句之后，在D:\app\18125\product\11.2.0\dbhome_1\database目录下会出现文件INITorcl.ORA,用笔记本打开进行编辑

orcl.__db_cache_size=1375731712
orcl.__java_pool_size=16777216
orcl.__large_pool_size=100663296
orcl.__oracle_base='d:\app\18125'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=637534208
orcl.__sga_target=1912602624
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=402653184
orcl.__streams_pool_size=0
*.audit_file_dest='d:\app\18125\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='d:\app\18125\oradata\orcl\control01.ctl','d:\app\18125\oradata\orcl\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='D:\app\18125\flash_recovery_area'
*.db_recovery_file_dest_size=4102029312
*.diagnostic_dest='d:\app\18125'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=2577399808
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=634388480
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1903165440
*.undo_tablespace='UNDOTBS1'

*.db_unique_name=orcl
*.log_archive_config='dg_config=(orcl,djl)'
*.log_archive_dest_1='LOCATION=D:\oracle_db_arch\arch valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.log_archive_dest_2='SERVICE=djl lgwr async affirm valid_for=(online_logfiles,primary_role) db_unique_name=djl'
*.log_archive_dest_3='LOCATION=D:\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=djl
*.fal_client=orcl
*.standby_file_management=auto
*.db_file_name_convert=('E:\app\Administrator\oradata\djl','D:\app\18125\oradata\orcl')
*.log_file_name_convert=('E:\app\Administrator\oradata\djl','D:\app\18125\oradata\orcl')

将主库initorcl.ora复制到备库对应目录下并修改内容如下

备库INITdjl.ORA（拷贝过去时，文件名称需要调整）

orcl.__db_cache_size=1375731712
orcl.__java_pool_size=16777216
orcl.__large_pool_size=100663296
orcl.__oracle_base='E:\app\Administrator'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=637534208
orcl.__sga_target=1912602624
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=402653184
orcl.__streams_pool_size=0
*.audit_file_dest='E:\app\Administrator\admin\djl\adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='E:\app\Administrator\oradata\djl\control01.ctl','E:\app\Administrator\oradata\djl\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
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=634388480
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1903165440
*.undo_tablespace='UNDOTBS1'

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

8、密码文件配置

1、生成密码文件

Orapwd file=D:\app\18125\product\11.2.0\dbhome_1\database\PWDorcl.ora password=orcl entries=5

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

9、控制文件配置

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

10、创建备库实例djl

C:\Users\Administrator>oradim -new -sid djl

11、主备库的启动

主库：

主库：登录sqlplus在showdown状态下创建参数文件
查看oracle 启动状态
SQL> select status from v$instance;
关闭数据库
SQL> shutdown immediate
创建参数文件
SQL> create spfile from pfile;
启动数据库
SQL> startup

备库：

C:\Users\Administrator>set oracle_sid=djl

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期日 8月 11 17:57:13 2019

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

已连接到空闲例程。

SQL>
创建参数文件

SQL> create spfile from pfile;

将数据库启动到nomount(必须是nomount状态)

SQL> startup nomount

12、使用rman连接主备库

在主库执行：

C:\Users\Administrator>rman target sys/orcl@orcl auxiliary sys/orcl@djl

注意：连接前使用rman登录主备库做测试

13、为备库备份参数文件

RMAN> backup current controlfile for standby database;

14、将主库数据库复制到备库

RMAN> duplicate target database for standby from active database;

15、将备库设置为接受日志模式

说明：此时，主库为open状态，备库为mounted

主库：

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL>

备库：

在备库执行以下语句，将备库修改为接收应用主库归档的模式

SQL> select status from v$instance;

STATUS
------------------------
MOUNTED

SQL> alter database recover managed standby database disconnect from session;

数据库已更改。

SQL>

16、日志验证dataguard：

17、备库后期配置

设置备库

SQL> alter database mount standby database;

SQL> select sequence#,applied from V$archived_log where applied='YES';
SQL> alter database recover managed standby database cancel;

设置备库只读
SQL> alter database open read only;
SQL> alter database recover managed standby database using current logfile disconnect from session;
说明：重启数据库后必须执行，这样数据才能同步。

查看日志应用情况
SQL> select sequence#,applied from V$archived_log where applied='IN-MEMORY';

如果看到IN-MEMORY说明当前正在进行实时同步，如果最后一个是YES，也是正常的，说明当前操作为0

SQL> select sequence#,applied from V$archived_log;

查询当前的保护模式
SQL> select open_mode,database_role,db_unique_name,PROTECTION_MODE from v$database;

到此，oracle 11g dataguard 配置完毕。

参考：Windows-server-2008-R2安装Oracle-11g-R2-dataguard - 阿小杜 - 博客园

星光之微

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 11g dataguard

一、安装准备Oracle :Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production1、IP配置主库IP:192.168.1.229备库IP:192.168.1.552、Oracle安装要求主库：安装oracle软件，并创建数据库备库：仅安装oracle软件3、安装位置主库： oracle 软件：D:\app\18125\product\11.2.0\dbhome_...
复制链接

扫一扫

专栏目录