AI 助理
备案控制台
开发者社区 数据库 文章 正文

实战不停机搭建ORACLE DataGuard

2014-03-23 1054
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 一、基础信息 操作系统：Red Hat Enterprise Linux Server release 6.3 (Santiago) 数据库：11.

一、基础信息

操作系统：Red Hat Enterprise Linux Server release 6.3 (Santiago)

数据库：11.2.0.3


配置信息：

说明

主机名

IP地址

db_name

sid

DB_UNIQUE_NAME

Service_name

主库

dg1

192.168.47.131

orcl

orcl

orcldg1

orcldg1

备库

dg2

192.168.47.132

orcl

orcl

orcldg2

orcldg2


二、准备工作

1、在主库和备库的host文件中添加相应的解析，/etc/hosts


2、按照要求创建相应的主数据库和备用数据库，并删除备用数据库的数据文件、控制文件，两个数据库的所有路径需要一致；


三、搭建数据库

主库的操作： 

1.确认主库参数

SQL> select name,open_mode,database_role,log_mode,force_logging from v$database;


NAME      OPEN_MODE            DATABASE_ROLE    LOG_MODE     FOR

--------- -------------------- ---------------- ------------ ---

OREO      READ WRITE           PRIMARY          ARCHIVELOG   NO


SQL>show parameter name


NAME                     TYPE     VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert             string

db_name                  string     orcl

db_unique_name                 string     orcldg1

global_names                 boolean     FALSE

instance_name                 string     orcl

lock_name_space              string

log_file_name_convert             string

processor_group_name             string

service_names                 string     orcldg1


2.设置数据库的归档模式

SQL>archive log list;

Database log mode           Archive Mode

Automatic archival           Enabled

Archive destination           /u01/app/oracle/oradata/arch

Oldest online log sequence     8

Next log sequence to archive   10

Current log sequence           10


归档模式的设置：

1、shutdown immediate；

2、alter database mount;

3、alter database archive log；

4、alter database open；


3.开启force logging

SQL> alter database force logging;

SQL> select name,DB_UNIQUE_NAME,database_role,log_mode,force_logging from v$database;

NAME      DB_UNIQUE_NAME         DATABASE_ROLE      LOG_MODE     FOR

--------- ------------------------------ ---------------- ------------ ----------------   ------

ORCL      orcldg1             PRIMARY      ARCHIVELOG   YES


4.配置监听

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date                20-FEB-2014 21:19:06

Uptime                    1 days 6 hr. 20 min. 13 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/OraDb11g_home1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/dg1/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1)(PORT=1521)))

Services Summary...

Service "dg_DGMGRL" has 1 instance(s).

  Instance "dg", status UNKNOWN, has 1 handler(s) for this service...

Service "orclXDB" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

Service "orcldg1" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

The command completed successfully


5.配置主库的TNSNAMES.ORA和密码文件到备库

[oracle@dg1 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/OraDb11g_home1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCLDG1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcldg1)

    )

  )


ORCLDG2 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcldg2)

       (UR=A)

    )

  )


传输监听文件到备库：

scp tnsnames.ora oreostb:/u01/app/oracle/products/11.2.0/network/admin

oracle@oreostb's password: 

tnsnames.ora                                                                                      100%  517     0.5KB/s   00:00  


6.修改主库参数

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcldg1,orcldg2)';                                                            

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/arch LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg1';  

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcldg2 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg2';     

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1='ENABLE';                                                                      

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='DEFER';                                                                                                                                             

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';            

ALTER SYSTEM SET FAL_SERVER=orcldg1;      

ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl/' scope=spfile;

ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl/' scope=spfile;


7.全备主库,并传输备份集到备库的相同位置

run

{

 allocate channel c0 device type disk;

 allocate channel c1 device type disk;

 CONFIGURE CONTROLFILE AUTOBACKUP ON;

 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/rman/%F';

 backup  database format '/u01/rman/ora11g_full_db_%d_%T_%u.bak';

 BACKUP ARCHIVELOG ALL FORMAT '/u01/rman/ora11g_arc_%s_%p_%t.bak';

 }   


8.修改备库参数

vi initoreo.ora

#DG CONFIG

*.log_archive_config='dg_config=(orcldg1,orcldg2)'

*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/arch LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg2'

*.log_archive_dest_2='SERVICE=orcldg1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg1'

*.standby_file_management='AUTO'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.fal_server=orcldg1

*.db_unique_name=orcldg2     


9.启动备库监听

[oracle@dg2 ~]$ lsnrctl start


LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 12-MAR-2014 19:34:22


Copyright (c) 1991, 2011, Oracle.  All rights reserved.


Starting /u01/app/oracle/product/OraDb11g_home1/bin/tnslsnr: please wait...


TNSLSNR for Linux: Version 11.2.0.3.0 - Production

System parameter file is /u01/app/oracle/product/OraDb11g_home1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/dg2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg2)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date                12-MAR-2014 19:34:23

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/OraDb11g_home1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/dg2/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "dg_DGMGRL" has 1 instance(s).

  Instance "dg", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully


10.进行备库的恢复

[oracle@dg1 admin]$ rman target /


Recovery Manager: Release 11.2.0.3.0 - Production on Sat Feb 22 01:57:57 2014


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


connected to target database: ORCL (DBID=1367831332)


RMAN>  connect auxiliary sys/oracle@orcldg2


connected to auxiliary database: ORCL (not mounted)


RMAN> duplicate target database for standby nofilenamecheck;


Starting Duplicate Db at 22-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK


contents of Memory Script:

{

   restore clone standby controlfile;

}

executing Memory Script


Starting restore at 22-FEB-14

using channel ORA_AUX_DISK_1


channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/rman/c-1367831332-20140220-00

channel ORA_AUX_DISK_1: piece handle=/u01/rman/c-1367831332-20140220-00 tag=TAG20140220T223858

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/orcl/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl

Finished restore at 22-FEB-14


contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script


sql statement: alter database mount standby database


contents of Memory Script:

{

   set newname for tempfile  1 to 

 "/u01/app/oracle/oradata/orcl/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to 

 "/u01/app/oracle/oradata/orcl/system01.dbf";

   set newname for datafile  2 to 

 "/u01/app/oracle/oradata/orcl/sysaux01.dbf";

   set newname for datafile  3 to 

 "/u01/app/oracle/oradata/orcl/undotbs01.dbf";

   set newname for datafile  4 to 

 "/u01/app/oracle/oradata/orcl/users01.dbf";

   restore

   clone database

   ;

}

executing Memory Script


executing command: SET NEWNAME


renamed tempfile 1 to /u01/app/oracle/oradata/orcl/temp01.dbf in control file


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


Starting restore at 22-FEB-14

using channel ORA_AUX_DISK_1


channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/rman/ora11g_full_db_ORCL_20140220_02p14lag.bak

channel ORA_AUX_DISK_1: piece handle=/u01/rman/ora11g_full_db_ORCL_20140220_02p14lag.bak tag=TAG20140220T223807

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/rman/ora11g_full_db_ORCL_20140220_01p14lag.bak

channel ORA_AUX_DISK_1: piece handle=/u01/rman/ora11g_full_db_ORCL_20140220_01p14lag.bak tag=TAG20140220T223807

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 22-FEB-14


contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script


datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=841956993 file name=/u01/app/oracle/oradata/orcl/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=2 STAMP=841956993 file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=3 STAMP=841956993 file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=4 STAMP=841956993 file name=/u01/app/oracle/oradata/orcl/users01.dbf

Finished Duplicate Db at 22-FEB-14


12.将主库的LOG_ARCHIVE_DEST_STATE_2设置为ENABLE

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='ENJABLE';


13.添加standby redo log

SQL> select member from v$logfile;


MEMBER

--------------------------------------------------------------------------------

/ORADATA/oreopdb/redo03.log

/ORADATA/oreopdb/redo02.log

/ORADATA/oreopdb/redo01.log


SQL>SELECT GROUP#, BYTES FROM V$STANDBY_LOG;

no rows selected


#主库增加 STANDBY LOGFILE

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/orcl/sredo04.log' size 50M;


ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/orcl/sredo05.log' size 50M;


ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/orcl/sredo06.log' size 50M;


ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/app/oracle/oradata/orcl/sredo07.log' size 50M;


ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 '/u01/app/oracle/oradata/orcl/sredo08.log' size 50M;




#备库增加STANDBY LOGFILE

SQL> select member from v$logfile;


MEMBER

--------------------------------------------------------------------------------

/ORADATA/oreostb/redo03.log

/ORADATA/oreostb/redo02.log

/ORADATA/oreostb/redo01.log



ALTER DATABASE ADD STANDBY LOGFILE  GROUP 4 '/u01/app/oracle/oradata/orcl/sredo04.log' size 50M;


ALTER DATABASE ADD STANDBY LOGFILE  GROUP 5 '/u01/app/oracle/oradata/orcl/sredo05.log' size 50M;


ALTER DATABASE ADD STANDBY LOGFILE  GROUP 6 '/u01/app/oracle/oradata/orcl/sredo06.log' size 50M;


ALTER DATABASE ADD STANDBY LOGFILE  GROUP 7 '/u01/app/oracle/oradata/orcl/sredo07.log' size 50M;


ALTER DATABASE ADD STANDBY LOGFILE  GROUP 8 '/u01/app/oracle/oradata/orcl/sredo08.log' size 50M;



14.开启active dataguard


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;  


SQL> ALTER DATABASE OPEN;


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;


至此物理DATAGUARD已经创建完毕，后续将陆续介绍DATAGUARD的日常管理和操作，欢迎各位ORACLE爱好者关注。。。。。。

------------------------------------------------------------------------------------------------

IT技术员的理想发展曲线：技术员――技术管理――运营管理――企业战略

个人理念：不做单纯的技术，技术就做管理的技术

ORACLE技术管理QQ群：367875324

------------------------------------------------------------------------------------------------

文章标签：
关系型数据库
Oracle
SQL
数据库
网络协议
关键词：
Oracle数据库一键上云dataguard
Oracle数据库一键上云实战
实战Oracle数据库一键上云
猎人笔记
目录
相关文章
pbeskoyq7ffm4
|
4月前
|
Oracle 关系型数据库 数据库
手把手教你Oracle DataGuard主备切换(switchover)
手把手教你Oracle DataGuard主备切换(switchover)
pbeskoyq7ffm4
619 4
老板这功能得加钱
|
7月前
|
运维 Oracle 容灾
Oracle dataguard 容灾技术实战（笔记），教你一种更清晰的Linux运维架构
Oracle dataguard 容灾技术实战（笔记），教你一种更清晰的Linux运维架构
老板这功能得加钱
120 2
pbeskoyq7ffm4
|
4月前
|
Oracle 网络协议 安全
Oracle 11g DataGuard搭建保姆级教程
Oracle 11g DataGuard搭建保姆级教程
pbeskoyq7ffm4
263 4
pbeskoyq7ffm4
|
4月前
|
Oracle 网络协议 关系型数据库
Oracle DataGuard主备切换之自动切换
Oracle DataGuard主备切换之自动切换
pbeskoyq7ffm4
201 2
老板这功能得加钱
|
7月前
|
SQL Oracle 前端开发
Oracle效率分析，Github标星25K+超火的前端实战项目
Oracle效率分析，Github标星25K+超火的前端实战项目
老板这功能得加钱
50 1
1941623231718325
|
7月前
|
存储 SQL Oracle
Oracle存储过程与自定义函数的调用：异同与实战场景
【4月更文挑战第19天】Oracle的存储过程与自定义函数各有特色，存储过程用于封装复杂SQL操作，常在批量处理和数据维护中使用，通过CALL或EXECUTE调用；而自定义函数则用于简单计算和查询，返回单一值，可直接在SQL语句中调用。了解两者异同，如返回值方式、调用方式和应用场景，能提升数据库管理效率。实战场景包括：使用存储过程定期清理过期数据，用自定义函数在查询中动态计算字段值。
1941623231718325
145 0
snowofsummer
|
7月前
|
Oracle 关系型数据库
oracle 19c 搭建dataguard 简要命令
通过service 完成dg 搭建。
snowofsummer
175 0
小Lee
|
Oracle 关系型数据库 数据库
Flink CDC中oracle dataguard模式下，有没有cdc备库的方案？
Flink CDC中oracle dataguard模式下，有没有cdc备库的方案？
小Lee
193 1
JamieChyi
|
Oracle 关系型数据库
Oracle日期加减运算实战演练
Oracle日期加减运算实战演练
JamieChyi
115 0
你可以叫我老白
|
SQL XML 运维
SpringBoot集成Oracle实战和坑
前段时间搞了一个oracle的项目，耗费了很多时间，现在项目整体上线了，在此记录下实战过程以及遇到的坑，有需要的网友也可以直接拿去使用。
你可以叫我老白
261 1

热门文章

最新文章

  • 1
    连接Oracle数据库失败（ORA-12514）故障排除
  • 2
    solaris X86-64下一个ORACLE战斗11.2.0.3.8在一波折叠补丁
  • 3
    Script:计算Oracle Streams进程所占用的内存大小
  • 4
    Oracle VARRAY的实际应用简介
  • 5
    MySQL和Oracle的添加字段的处理差别
  • 6
    PLSQL_Oracle簇表和簇表管理Index clustered tables（案例）
  • 7
    一篇文章带你了解Oracle 数据库中 CROSS JOIN（cross join） 语法的作用
  • 8
    Oracle数据文件和临时文件的管理
  • 9
    oracle 11g adrci 工具使用方法
  • 10
    oracle express 11g 创建表空间
  • 1
    解决Toad for Oracle显示乱中文码问题
    96
  • 2
    【服务器】python通过JDBC连接到位于Linux远程服务器上的Oracle数据库
    98
  • 3
    零基础入门 Oracle数据库：轻松上手
    218
  • 4
    java操作多数据源将oracle数据同步达梦数据库
    297
  • 5
    SuSE linux server 11通过SAP来安装oracle11g
    106
  • 6
    oracle11g SAP测试机归档日志暴增排查（二）
    337
  • 7
    oracle11g SAP测试机归档日志暴增排查（一）
    83
  • 8
    修改oracle11g的awr快照参数
    51
  • 9
    发现oracle10gSYSAUX空间没有了进行处理
    45
  • 10
    在centos7虚拟机上安装docker oracle11g
    292

    • 相关课程

    更多
  • 数据库备份DBS 产品功能介绍
  • 数据库常见问题排查
  • 从0到1数据库内核实战教程

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 相关实验场景

    更多
  • 搭建PostgreSQL主从架构
  • 使用DAS实现数据库自动SQL优化
  • 自建MySQL 5.7数据库备份上云
  • PolarDB MySQL通过DMS恢复误操作数据

    • 推荐镜像

    更多
  • oracle
  • mysql
  • postgresql
    • 下一篇
    fs.oss.accessKeyId和fs.oss.accessKeySecret。