Oracle physical dataguard with broker部署

Wesley13
• 阅读 540

一、环境说明

主库:10.110.96.88

备库:10.110.96.87

数据库实例:gisc

二、主库操作

1.开启force logging
ALTER DATABASE FORCE LOGGING;
2.开启日志归档

a.查看日志归档是否开启

ARCHIVE LOG LIST;

b.如果没有开始归档则开启

1 SHUTDOWN IMMEDIATE;
2 STARTUP MOUNT;
3 ALTER DATABASE ARCHIVELOG;
4 ALTER DATABASE OPEN;
3.创建standby redo logs

a.查看重做日志文件组信息的两个视图v$log,v$logfile

b.修改重做日志文件大小

默认重做日志文件组有3个,因为重做日志文件组至少为2个,所以采用删除一个再创建一个的方式进行依次修改。

1 ALTER DATABASE DROP LOGFILE GROUP 1;
2 ALTER DATABASE ADD LOGFILE GROUP 1('/home/oracle/app/oradata/gisc/redo01.log') SIZE 1G;
3 ALTER DATABASE DROP LOGFILE GROUP 2;
4 ALTER DATABASE ADD LOGFILE GROUP 2('/home/oracle/app/oradata/gisc/redo02.log') SIZE 1G;
5 ALTER DATABASE DROP LOGFILE GROUP 3;
6 ALTER DATABASE ADD LOGFILE GROUP 3('/home/oracle/app/oradata/gisc/redo03.log') SIZE 1G;

因为日志文件组3在使用,所以修改时报了异常:

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance gisc (thread 1)
ORA-00312: online log 3 thread 1: '/home/oracle/app/oradata/gisc/redo03.log'

解决方法是执行:ALTER SYSTEM CHECKPOINT,如下代码示:

1 ALTER SYSTEM CHECKPOINT;2 ALTER DATABASE DROP LOGFILE GROUP 3; 3 ALTER DATABASE ADD LOGFILE GROUP 3('/home/oracle/app/oradata/gisc/redo03.log') SIZE 1G;

c.The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs.

d.创建standby redo logs操作语句:

1 ALTER DATABASE ADD STANDBY LOGFILE ('/home/oracle/app/oradata/gisc/standby_redo01.log') SIZE 1g;
2 ALTER DATABASE ADD STANDBY LOGFILE ('/home/oracle/app/oradata/gisc/standby_redo02.log') SIZE 1g;
3 ALTER DATABASE ADD STANDBY LOGFILE ('/home/oracle/app/oradata/gisc/standby_redo03.log') SIZE 1g;
4 ALTER DATABASE ADD STANDBY LOGFILE ('/home/oracle/app/oradata/gisc/standby_redo04.log') SIZE 1g;
4.开启闪回数据库

该操作需在日志归档模式开启后操作。

ALTER DATABASE FLASHBACK ON;

5.设置初始化参数

a.参数设置命令格式

alter system set parameter_name=value  [scope=both/spfile/memory]

memory:仅对内存中参数有效,数据库重启后失效;

spfile:仅对spfile参数文件有效,对当前运行的实例无效,重启数据库后生效;

both:对内存和参数文件都有效。

If a server parameter file was used to start up the database, then BOTH is the default. If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify.

b.设置参数内容

 1 DB_NAME=gisc
 2 DB_UNIQUE_NAME=gisc
 3 LOG_ARCHIVE_CONFIG='DG_CONFIG=(gisc,gisc_stdby)'
 4 CONTROL_FILES='/home/oracle/app/oradata/gisc/control01.ctl', '/home/oracle/app/fast_recovery_area/gisc/control02.ctl'#日志本地归档
 5 LOG_ARCHIVE_DEST_1=
 6  'LOCATION=/home/oracle/app/oradata/archive_logs
 7   VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
 8   DB_UNIQUE_NAME=gisc'#日志发往备库,配置broker时LOG_ARCHIVE_DEST_2需设置为空,配置broker成功后会自动配置
 9 #LOG_ARCHIVE_DEST_2=
10 #'SERVICE=GISC87 ASYNC
11 #  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
12 #  DB_UNIQUE_NAME=gisc_stdby'
13 REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
14 LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile
15 
16 #以下参数主库切换为备库时发挥作用
18 FAL_SERVER=gisc_stdby#数据文件和日志文件目录转换路径,主备库可不一样,前面是备库路径,后面是主库路径。
19 DB_FILE_NAME_CONVERT='/home/oracle/app/oradata/gisc','/home/oracle/app/oradata/gisc' scope=spfile
20 LOG_FILE_NAME_CONVERT='/home/oracle/app/oradata/gisc','/home/oracle/app/oradata/gisc'  scope=spfile
21 STANDBY_FILE_MANAGEMENT='AUTO'

6.为备库创建初始化参数文件

执行如下命令从spfile创建pfile,以便以文本方式为备库修改参数

create pfile='/home/oracle/dg/initgisc.ora' from spfile;

7.修改监听参数$ORACLE_HOME/network/admin/listener.ora

 1 LISTENER =
 2   (DESCRIPTION_LIST =
 3     (DESCRIPTION =
 4       (ADDRESS = (PROTOCOL = TCP)(HOST = gisc-db2)(PORT = 1521))
 5       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
 6     )
 7   )
 8 SID_LIST_LISTENER =
 9   (SID_LIST =
10     (SID_DESC =#注意加_DGMGRL后缀,配置broker时用到
11       (GLOBAL_DBNAME = gisc_DGMGRL)
12       (ORACLE_HOME = /home/oracle/app/oracle/product/12.1.0/dbhome)
13       (SID_NAME = gisc)
14     )
15   )
16 
17 ADR_BASE_LISTENER = /home/oracle/app

8.修改客户端连接参数$ORACLE_HOME/network/admin/tnsnames.ora,备库需执行同样的操作

 1 gisc88 =
 2   (DESCRIPTION =
 3     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.110.96.88)(PORT = 1521))
 4     (CONNECT_DATA =
 5       (SERVER = DEDICATED)
 6       (SERVICE_NAME = gisc)
 7     )
 8   )
 9 gisc87 =
10   (DESCRIPTION =
11     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.110.96.87)(PORT = 1521))
12     (CONNECT_DATA =
13       (SERVER = DEDICATED)
14      # (SERVICE_NAME = gisc_stdby)
15 
16   (SID = gisc)
17 
18   (UR=A)
19     )
20   )

三、备库操作

1.拷贝主库密码文件到备库相应位置

scp oracle@10.0.96.88:/home/oracle/app/oracle/product/12.1.0/dbhome/dbs/orapwgisc $ORACLE_HOME/dbs

2.拷贝一.6中生成的参数文件到备库,并修改为备库中的相应参数值。

 a.拷贝到备库

scp oracle@10.0.96.88:/home/oracle/dg/initgisc.ora  $ORACLE_HOME/dg

b. 修改备库中的相应参数,其中涉及备库中的路径,应改为相应备库中的路径,如基路径、数据文件路径、日志文件路径、恢复区路径、控制文件路径等。

这里主备库路径一致,所以相应路径就不用改了。

 1 gisc.__oracle_base='/home/oracle/app'  2  3 *.audit_file_dest='/home/oracle/app/admin/gisc/adump'  4  5 *.control_files='/home/oracle/app/oradata/gisc/control01.ctl','/home/oracle/app/fast_recovery_area/gisc/control02.ctl'  6  7 *.db_file_name_convert='/home/oracle/app/oradata/gisc','/home/oracle/app/oradata/gisc'  8  9 *.log_file_name_convert='/home/oracle/app/oradata/gisc','/home/oracle/app/oradata/gisc' 10 11 *.db_recovery_file_dest='/home/oracle/app/fast_recovery_area' 12 13 *.diagnostic_dest='/home/oracle/app' 14 15 DB_UNIQUE_NAME=gisc_stdby 16 LOG_ARCHIVE_DEST_1= 17 'LOCATION=/home/oracle/app/oradata/archive_logs 18  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 19  DB_UNIQUE_NAME=gisc_stdby'#日志发往主库,配置broker时LOG_ARCHIVE_DEST_2需设置为空,转换为主库时会自动配置 20 #LOG_ARCHIVE_DEST_2= 21 #'SERVICE=gisc88 ASYNC 22 # VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 23 # DB_UNIQUE_NAME=gisc' 24 25 FAL_SERVER=gisc

3.创建相应目录

mkdir -p /home/oracle/app/oradata/gisc/pdbseed
mkdir -p /home/oracle/app/oradata/gisc/pdb1
mkdir -p /home/oracle/app/fast_recovery_area/gisc
mkdir -p /home/oracle/app/admin/gisc/adump

4.确保设置了环境变量:

export ORACLE_SID=gisc

5.确保配置了监听,如果没有可用netca图形界面进行配置。

6.修改监听参数$ORACLE_HOME/network/admin/listener.ora

 1 LISTENER =
 2   (DESCRIPTION_LIST =
 3     (DESCRIPTION =
 4       (ADDRESS = (PROTOCOL = TCP)(HOST = gisc-db2)(PORT = 1521))  5 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))  6  )  7  )  8 SID_LIST_LISTENER =  9 (SID_LIST = 10 (SID_DESC =#注意加_DGMGRL后缀,配置broker时用到 11 (GLOBAL_DBNAME = gisc_stdby_DGMGRL) 12 (ORACLE_HOME = /home/oracle/app/oracle/product/12.1.0/dbhome) 13 (SID_NAME = gisc) 14  ) 15  ) 16 17 ADR_BASE_LISTENER = /home/oracle/app 

7.修改客户端连接参数$ORACLE_HOME/network/admin/tnsnames.ora

 1 gisc88 =
 2   (DESCRIPTION =
 3     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.110.96.88)(PORT = 1521))  4 (CONNECT_DATA =  5 (SERVER = DEDICATED)  6 (SERVICE_NAME = gisc)  7  )  8  )  9 gisc87 = 10 (DESCRIPTION = 11 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.110.96.87)(PORT = 1521)) 12 (CONNECT_DATA = 13 (SERVER = DEDICATED) 14 # (SERVICE_NAME = gisc_stdby) 16     (SID = gisc) 18     (UR=A) 19  ) 20 )

8.为备库创建服务器参数文件spfile

create spfile from pfile='/home/oracle/dg/initgisc.ora' ;

9.启动备库到nomount状态

startup nomount;

10.rman连接主备库,并执行同步

a0:确保主备库防火墙开通了oralce 1521端口,否则执行主备同步时会出现以下错误:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/24/2019 22:58:26
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/24/2019 22:58:26
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12543: TNS:destination host unreachable
ORA-17629: Cannot connect to the remote database server

a1:主备都要重启监听和数据库?

a.连接主备库

rman target sys/*****@GISC88 auxiliary sys/****@GISC87

b.执行主备同步

DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  NOFILENAMECHECK;
11. 启动归档日志应用进程
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

四.配置broker

1.确认主备库log_archive_dest_2设置为空,如果不为空设置为空

show parameter log_archive_dest_2;alter system set log_archive_dest_2='';

2.在主备库开启dg_broker_start

ALTER SYSTEM SET dg_broker_start=true;

3.连接主库配置broker

a.dgmgrl连接主库

dgmgrl sys/****@GISC88

b.创建配置,指定主库

CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS gisc CONNECT IDENTIFIER IS gisc88;

c.添加备库

ADD DATABASE gisc_stdby AS CONNECT IDENTIFIER IS gisc87 MAINTAINED AS PHYSICAL;

d.使配置生效

ENABLE CONFIGURATION;

e.查看主备库状态

DGMGRL> SHOW CONFIGURATION;

Configuration - dg_config

  Protection Mode: MaxPerformance
  Members:
  gisc       - Primary database
    gisc_stdby - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 32 seconds ago)

DGMGRL> SHOW DATABASE GISC;

Database - gisc

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    gisc

Database Status:
SUCCESS

DGMGRL> SHOW DATABASE gisc_stdby;

Database - gisc_stdby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 39.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    gisc

Database Status:
SUCCESS

五.主库中确认主备库重做日志传输和应用情况

SQL> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM
  2  V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0'; CLIENT_P PROCESS THREAD# SEQUENCE# STATUS -------- --------- ---------- ---------- ------------ N/A MRP0 1 326 APPLYING_LOG LGWR RFS 1 326 IDLE

 六.DGMGRL中正常运行时的主备切换SWITCHOVER

a.主库切到87

DGMGRL>SWITCHOVER TO gisc_stdby;

b.主库切回到88

DGMGRL>SWITCHOVER TO gisc;

七.DGMGRL中主库故障时的主备切换FAILOVER

假设主库故障,需切换备库为主库。DGMGRL连接到备库进行如下操作:

a.切换备库为主库

DGMGRL>FAILOVER TO gisc_stdby;

b.原主库故障恢复后,重做其状态,使其为备库,该操作需在闪回数据库开启的状态下进行。

DGMGRL>REINSTATE DATABASE gisc;

 c.如果闪回数据库没有开启,原主数据库故障恢复后,要恢复其为备库,需按以下步骤重做

c1. Cleanup the old instance,可把以下代码写成bash脚本。

 1 rm -Rf $ORACLE_BASE/oradata/$ORACLE_SID/*
 2 rm -Rf $ORACLE_BASE/fast_recovery_area/$ORACLE_SID
 3 rm -Rf $ORACLE_BASE/fast_recovery_area/$ORACLE_SID_stdby
 4 rm -Rf $ORACLE_BASE/admin/$ORACLE_SID
 5 rm $ORACLE_HOME/dbs/spfile*.ora
 6 rm $ORACLE_HOME/dbs/init*.ora
 7 
 8 mkdir -p $ORACLE_BASE/fast_recovery_area/$ORACLE_SID
 9 mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
10 mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID/pdbseed
11 mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID/pdb1

c2. Connect to RMAN.

$ rman TARGET sys/***@gisc_stby AUXILIARY sys/***@gisc

c3.Duplicate the database.

DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  NOFILENAMECHECK;

c4. Connect to DGMDRL on the current primary.

$ dgmgrl sys/***@gisc_stby

c5. Enable the new standby.

DGMGRL> ENABLE DATABASE gisc;

其他:

1.oralce服务器查看错误号命令:oerr ora 错误号,如下所示:

[oracle@gisc-db1 data_guard]$ oerr ora 01624
01624, 00000, "log %s needed for crash recovery of instance %s (thread %s)"
// *Cause:  A log cannot be dropped or cleared until the thread's checkpoint
//          has advanced out of the log.
// *Action: If the database is not open, then open it. Crash recovery will
//          advance the checkpoint. If the database is open force a global
//          checkpoint. If the log is corrupted so that the database cannot
//          be opened, it may be necessary to do incomplete recovery until
//          cancel at this log.

主要参考:

1.Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle Database 12c Release 1

2.修改oracle日志文件大小

  1. Data Guard Command-Line Interface Reference

4.ORA-01624: log 3 needed for crash recovery of instance orcl (thread 1)问题解决

5.ORA-01078: failure in processing system parameters 问题的解决方法(oracle 11g)

6.ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting的解决方法

7.RMAN active cloning in oracle 12C

8.Oracle Silent Mode, Part 3: Cloning Software and Databases

9.oracle数据库DB_NAME、DBID、DB_UNIQUE_NAME等的区别

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
2年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Wesley13 Wesley13
2年前
java中比较两个时间的差值
项目背景1.某篇文稿的发布时间是publishDate,例如:2020072118:00:41。2.现要求判断该篇文稿的发布时间是否在近30天之内。publicstaticlongdayDiff(DatecurrentDate,DatepublishDate){LongcurrentTimecurrentDat
Wesley13 Wesley13
2年前
MySQL总结(十一)子查询
!(https://oscimg.oschina.net/oscnet/upa344f41e81d3568e3310b5da00c57ced8ea.png)子查询1\.什么是子查询需求:查询开发部中有哪些员工selectfromemp;通
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
2年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
3个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这