【DB宝29】使用Docker搭建Oracle 11g的DG环境
【DB宝29】使用Docker搭建Oracle 11g的DG环境
AiDBA宝典
发布于 2020-11-25 12:52:35
发布于 2020-11-25 12:52:35
文章被收录于专栏：小麦苗的DB宝专栏
目录
一、初始化环境
二、主库操作
2.1 修改forcelogging、开闪回
2.2 添加standby log
2.3 修改参数
2.4 修改监听
2.5 修改tnsnames.ora
三、主库密码文件拷贝到备库
四、备库操作
4.1 修改监听
4.2 备库修改到NOMOUNT状态
4.3 duplicate复制搭建DG
4.4 实时应用
五、配置dgmgrl
5.1 基础配置
5.2 配置FSFO
5.3 重建DG一、初始化环境
DG环境规划
项目 | 主库 | 物理备库 |
|---|---|---|
db 类型 | 单实例 | 单实例 |
db version | 11.2.0.4.0 | 11.2.0.4.0 |
db 存储 | FS | FS |
OS版本 | RHEL7.6 64位 | CentOS7.6 64位 |
OS hostname | LHR11G | LHR11GDG |
IP地址 | 192.168.68.68 | 192.168.68.69 |
ORACLE_SID | LHR11G | LHR11GDG |
db_name/GLOBAL_DBNAME | LHR11G | LHR11G |
db_unique_name | LHR11G | LHR11GDG |
TNS_NAME | LHR11G | LHR11GDG |
监听端口 | 1521 | 1521 |
映射的主机端口 | 1528 | 1529 |
ORACLE_HOME | /u01/app/oracle/product/11.2.0.4/dbhome_1 | /u01/app/oracle/product/11.2.0.4/dbhome_1 |
dbid | 2007947551 | 2007947551 |
-- 创建DG的网络
docker network create --subnet=192.168.68.0/16 mhalhr
docker network inspect mhalhr
-- 创建2台主机
docker run -itd --name LHR11G -h LHR11G \
-p 1528:1521 -p 1128:1158 -p 228:22 -p 3398:3389 \
--network mhalhr --ip 192.168.68.68 \
--privileged=true \
lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 init
docker run -itd --name LHR11GDG -h LHR11GDG \
-p 1529:1521 -p 1129:1158 -p 229:22 -p 3399:3389 \
--network mhalhr --ip 192.168.68.69 \
--privileged=true \
lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 init
-- 添加网卡
docker network connect bridge LHR11G
docker network connect bridge LHR11GDG
-- 进入容器
docker exec -it LHR11G bash
docker exec -it LHR11GDG bash
--备库删除原有的数据库
dbca -silent -deleteDatabase -sourceDB LHR11G
二、主库操作
2.1 修改forcelogging、开闪回
alter database force logging;
alter database flashback on;
select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from v$database;
2.2 添加standby log
select * from v$standby_log;
select group#,bytes/1024/1024 ||'M' from v$log ;
SELECT * FROM V$LOGFILE;
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/LHR11G/standby_redo04.log') size 50m;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/LHR11G/standby_redo05.log') size 50m;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/LHR11G/standby_redo06.log') size 50m;
2.3 修改参数
alter system set db_unique_name='LHR11G' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(LHR11G,LHR11GDG)';
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=LHR11G valid_for=(ALL_LOGFILES,ALL_ROLES)';
alter system set log_archive_dest_2='SERVICE=LHR11GDG LGWR ASYNC db_unique_name=LHR11GDG valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)';
alter system set log_archive_dest_state_1=ENABLE;
alter system set log_archive_dest_state_2=ENABLE;
alter system set log_archive_max_processes=4;
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
alter system set db_file_name_convert='LHR11GDG','LHR11G' scope=spfile;
alter system set log_file_name_convert='LHR11GDG','LHR11G' scope=spfile;
alter system set standby_file_management='AUTO';
alter system set fal_server='LHR11GDG';
alter system set fal_client='LHR11G';
2.4 修改监听
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = LHR11G)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
(SID_NAME= LHR11G)
)
(SID_DESC =
(GLOBAL_DBNAME = LHR11G_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
(SID_NAME= LHR11G)
)
)
2.5 修改tnsnames.ora
LHR11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.68.68)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LHR11G)
)
)
LHR11GDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.68.69)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LHR11GDG)
)
)
三、主库密码文件拷贝到备库
docker cp LHR11G:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwLHR11G .
docker cp orapwLHR11G LHR11GDG:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwLHR11GDG
chown oracle.oinstall /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwLHR11GDG
四、备库操作
4.1 修改监听
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = LHR11GDG)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
(SID_NAME= LHR11GDG)
)
(SID_DESC =
(GLOBAL_DBNAME = LHR11GDG_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
(SID_NAME= LHR11GDG)
)
)
4.2 备库修改到NOMOUNT状态
mkdir -p /u01/app/oracle/oradata/LHR11GDG/
mkdir -p /u01/app/oracle/admin/LHR11GDG/adump
echo "db_name=LHR11G" > $ORACLE_HOME/dbs/initLHR11GDG.ora
ORACLE_SID=LHR11GDG
startup nomount
4.3 duplicate复制搭建DG
rman target sys/lhr@LHR11G auxiliary sys/lhr@LHR11GDG
duplicate target database
for standby nofilenamecheck
from active database
DORECOVER
spfile
set db_unique_name='LHR11GDG'
set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=LHR11GDG'
set standby_file_management='AUTO'
set fal_server='LHR11G'
set fal_client='LHR11GDG'
set control_files='/u01/app/oracle/oradata/LHR11GDG/control01.ctl'
set db_file_name_convert='LHR11G','LHR11GDG'
set log_file_name_convert='LHR11G','LHR11GDG'
set audit_file_dest='/u01/app/oracle/admin/LHR11GDG/adump'
set sga_max_size='346030080'
;
执行过程：
[oracle@lhr11gdg dbs]$ rman target sys/lhr@LHR11G auxiliary sys/lhr@LHR11GDG
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Oct 27 14:07:44 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: LHR11G (DBID=2007947551)
connected to auxiliary database: LHR11G (not mounted)
RMAN>
RMAN> duplicate target database
2> for standby nofilenamecheck
3> from active database
4> DORECOVER
5> spfile
6> set db_unique_name='LHR11GDG'
7> set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=LHR11GDG'
8> set standby_file_management='AUTO'
9> set fal_server='LHR11G'
10> set fal_client='LHR11GDG'
11> set control_files='/u01/app/oracle/oradata/LHR11GDG/control01.ctl'
12> set db_file_name_convert='LHR11G','LHR11GDG'
13> set log_file_name_convert='LHR11G','LHR11GDG'
14> set audit_file_dest='/u01/app/oracle/admin/LHR11GDG/adump'
15> set sga_max_size='346030080'
16> ;
Starting Duplicate Db at 2020-10-27 14:07:51
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=396 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwLHR11G' auxiliary format
'/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwLHR11GDG' targetfile
'/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileLHR11G.ora' auxiliary format
'/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileLHR11GDG.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileLHR11GDG.ora''";
}
executing Memory Script
Starting backup at 2020-10-27 14:07:53
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=161 device type=DISK
Finished backup at 2020-10-27 14:07:55
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileLHR11GDG.ora''
contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''LHR11GDG'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=LHR11GDG'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''LHR11G'' comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''LHR11GDG'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/LHR11GDG/control01.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''LHR11G'', ''LHR11GDG'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''LHR11G'', ''LHR11GDG'' comment=
'''' scope=spfile";
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/LHR11GDG/adump'' comment=
'''' scope=spfile";
sql clone "alter system set sga_max_size =
346030080 comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''LHR11GDG'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=LHR11GDG'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''LHR11G'' comment= '''' scope=spfile
sql statement: alter system set fal_client = ''LHR11GDG'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/LHR11GDG/control01.ctl'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''LHR11G'', ''LHR11GDG'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''LHR11G'', ''LHR11GDG'' comment= '''' scope=spfile
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/LHR11GDG/adump'' comment= '''' scope=spfile
sql statement: alter system set sga_max_size = 346030080 comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 346562560 bytes
Fixed Size 2253144 bytes
Variable Size 209718952 bytes
Database Buffers 130023424 bytes
Redo Buffers 4567040 bytes
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/LHR11GDG/control01.ctl';
}
executing Memory Script
Starting backup at 2020-10-27 14:08:04
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/snapcf_LHR11G.f tag=TAG20201027T140804 RECID=3 STAMP=1054908485
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2020-10-27 14:08:06
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/LHR11GDG/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/LHR11GDG/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/LHR11GDG/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/LHR11GDG/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/LHR11GDG/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/LHR11GDG/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/LHR11GDG/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/LHR11GDG/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/LHR11GDG/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/LHR11GDG/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/LHR11GDG/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/LHR11GDG/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 2020-10-27 14:08:12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/LHR11G/system01.dbf
output file name=/u01/app/oracle/oradata/LHR11GDG/system01.dbf tag=TAG20201027T140812
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/LHR11G/sysaux01.dbf
output file name=/u01/app/oracle/oradata/LHR11GDG/sysaux01.dbf tag=TAG20201027T140812
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/LHR11G/undotbs01.dbf
output file name=/u01/app/oracle/oradata/LHR11GDG/undotbs01.dbf tag=TAG20201027T140812
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/LHR11G/example01.dbf
output file name=/u01/app/oracle/oradata/LHR11GDG/example01.dbf tag=TAG20201027T140812
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/LHR11G/users01.dbf
output file name=/u01/app/oracle/oradata/LHR11GDG/users01.dbf tag=TAG20201027T140812
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2020-10-27 14:08:50
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_26_hshgbgyr_.arc" auxiliary format
"/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_%u_.arc" archivelog like
"/u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_27_hshgcl82_.arc" auxiliary format
"/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_%u_.arc" ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script
Starting backup at 2020-10-27 14:08:50
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=26 RECID=7 STAMP=1054908501
output file name=/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_0eve183i_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=27 RECID=8 STAMP=1054908530
output file name=/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_0fve183j_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 2020-10-27 14:08:52
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_0fve183j_.arc
File Name: /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_0eve183i_.arc
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_0fve183j_.arc
File Name: /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_0eve183i_.arc
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=1054908532 file name=/u01/app/oracle/oradata/LHR11GDG/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=1054908532 file name=/u01/app/oracle/oradata/LHR11GDG/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1054908532 file name=/u01/app/oracle/oradata/LHR11GDG/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1054908532 file name=/u01/app/oracle/oradata/LHR11GDG/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1054908532 file name=/u01/app/oracle/oradata/LHR11GDG/example01.dbf
contents of Memory Script:
{
set until scn 1138080;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 2020-10-27 14:08:52
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=33 device type=DISK
starting media recovery
archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_0eve183i_.arc
archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_0fve183j_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_0eve183i_.arc thread=1 sequence=26
archived log file name=/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_0fve183j_.arc thread=1 sequence=27
media recovery complete, elapsed time: 00:00:00
Finished recover at 2020-10-27 14:08:54
Finished Duplicate Db at 2020-10-27 14:08:59
RMAN> exit
Recovery Manager complete.
4.4 实时应用
select open_mode from v$database;
alter database open;
alter database flashback on;
select * from v$log;
set line 9999
select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;
alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect;
主备库常用脚本：
------------------------------ 主备库信息
set line 9999
col DEST_NAME format a20
col DESTINATION format a15
col GAP_STATUS format a10
col DB_UNIQUE_NAME format a15
col error format a10
col APPLIED_SCN for 999999999999999
SELECT al.thread#,
ads.dest_id,
ads.DEST_NAME,
(SELECT ads.TYPE || ' ' || ad.TARGET
FROM v$archive_dest AD
WHERE AD.DEST_ID = ADS.DEST_ID) TARGET,
ADS.DATABASE_MODE,
ads.STATUS,
ads.error,
ads.RECOVERY_MODE,
ads.DB_UNIQUE_NAME,
ads.DESTINATION,
ads.GAP_STATUS,
(SELECT CASE
WHEN NB.DATABASE_ROLE like '%STANDBY%' then
(SELECT MAX(sequence#)
FROM v$standby_log na
WHERE na.thread# = al.thread#)
ELSE
(SELECT MAX(sequence#)
FROM v$log na
WHERE na.thread# = al.thread#)
END
FROM V$DATABASE NB) Current_Seq#,
MAX(sequence#) Last_Archived,
MAX(CASE
WHEN al.APPLIED = 'YES' AND
aL.STANDBY_DEST =
(SELECT CASE
WHEN NB.DATABASE_ROLE like '%STANDBY%' then
'NO'
ELSE
'YES'
END
FROM V$DATABASE NB) THEN
al.sequence#
end) APPLIED_SEQ#,
(SELECT ad.applied_scn
FROM v$archive_dest AD
WHERE AD.DEST_ID = ADS.DEST_ID) applied_scn
FROM (SELECT *
FROM v$archived_log V
WHERE V.resetlogs_change# =
(SELECT d.RESETLOGS_CHANGE# FROM v$database d)) al,
v$archive_dest_status ads
WHERE al.dest_id(+) = ads.dest_id
AND ads.STATUS != 'INACTIVE'
AND DEST_NAME <> 'STANDBY_ARCHIVE_DEST'
GROUP BY al.thread#,
ads.dest_id,
ads.DEST_NAME,
ads.STATUS,
ads.error,
ads.TYPE,
ADS.DATABASE_MODE,
ads.RECOVERY_MODE,
ads.DB_UNIQUE_NAME,
ads.DESTINATION,
ads.GAP_STATUS
ORDER BY ads.dest_id, al.thread#;
------------物理dg日志应用情况(主备库都可以)
COL NAME FOR A80
SET LINESIZE 9999 PAGESIZE 9999
SELECT A.THREAD#, A.NAME, A.SEQUENCE#, A.APPLIED, A.FIRST_TIME
FROM V$ARCHIVED_LOG A,
(SELECT NB.THREAD#,NB.RESETLOGS_ID,NB.DEST_ID,(MAX(NB.SEQUENCE#) - 3) MAX_SEQUENCE#
FROM V$ARCHIVED_LOG NB
WHERE NB.APPLIED = 'YES'
and NB.DEST_ID in (SELECT NB.DEST_ID FROM V$ARCHIVE_DEST_STATUS NB where STATUS <>'INACTIVE' AND NB.RECOVERY_MODE like 'MANAGED%' )
and RESETLOGS_ID=(select max(nbb.RESETLOGS_ID) from V$ARCHIVED_LOG nbb)
GROUP BY NB.THREAD#,NB.RESETLOGS_ID,NB.DEST_ID) B
WHERE A.THREAD# = B.THREAD#
AND A.RESETLOGS_ID = B.RESETLOGS_ID
AND A.DEST_ID=B.DEST_ID
AND A.SEQUENCE# >= MAX_SEQUENCE#
and A.RESETLOGS_ID=(select max(nb.RESETLOGS_ID) from V$ARCHIVED_LOG nb)
AND A.STANDBY_DEST = (SELECT CASE WHEN NB.DATABASE_ROLE LIKE '%STANDBY%' THEN 'NO' ELSE 'YES' END FROM V$DATABASE NB)
ORDER BY A.THREAD#, A.SEQUENCE#;
执行过程：
[oracle@lhr11gdg dbs]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 27 14:11:21 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@LHR11GDG> select open_mode from v$database;
OPEN_MODE
----------------------------------------
MOUNTED
SYS@LHR11GDG> alter database open;
Database altered.
SYS@LHR11GDG> set line 9999
SYS@LHR11GDG> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FORCE_ OPEN_MODE SWITCHOVER_STATUS
---------- ------------------ ----------- ---------------------------------------- -------------------------------- ------ ---------------------------------------- ----------------------------------------
2007947551 LHR11G 1138079 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED
SYS@LHR11GDG> alter database recover managed standby database using current logfile disconnect;
Database altered.
SYS@LHR11GDG> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FORCE_ OPEN_MODE SWITCHOVER_STATUS
---------- ------------------ ----------- ---------------------------------------- -------------------------------- ------ ---------------------------------------- ----------------------------------------
2007947551 LHR11G 1138079 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWEDc
查询DG状态：
SYS@LHR11G> @dg_info.sql
THREAD# DEST_ID DEST_NAME TARGET DATABASE_MODE STATUS ERROR RECOVERY_MODE DB_UNIQUE_NAME DESTINATION GAP_STATUS CURRENT_SEQ# LAST_ARCHIVED APPLIED_SEQ# APPLIED_SCN
---------- ---------- -------------------- -------------------------------------------- ------------------------------ ------------------ ---------- ---------------------------------------------- --------------- --------------- ---------- ------------ ------------- ------------ ----------------
1 1 LOG_ARCHIVE_DEST_1 LOCAL PRIMARY OPEN VALID IDLE LHR11G 34 33 0
1 2 LOG_ARCHIVE_DEST_2 PHYSICAL STANDBY OPEN_READ-ONLY VALID MANAGED REAL TIME APPLY LHR11GDG LHR11GDG NO GAP 34 33 32 1142459
SYS@LHR11G> @dg_status
THREAD# NAME SEQUENCE# APPLIED FIRST_TIME
---------- ---------------------------------------------------------------------------------------------------- ---------- ------------------ -------------------
1 30 YES 2020-10-27 15:36:40
1 31 YES 2020-10-27 15:36:41
1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_39_hshmk43l_.arc 32 YES 2020-10-27 15:36:47
1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_40_hshmow9l_.arc 33 IN-MEMORY 2020-10-27 15:37:07
五、配置dgmgrl
5.1 基础配置
-- 主备库都修改
alter system set dg_broker_start=true scope=both;
-- 开始配置
CREATE CONFIGURATION 'LHR11G' AS primary database is 'LHR11G' connect identifier is LHR11G;
SHOW CONFIGURATION;
add database 'LHR11GDG' as connect identifier is LHR11GDG maintained as physical;
show database verbose 'LHR11G';
show database 'LHR11G';
enable configuration
EDIT DATABASE 'LHR11G' SET PROPERTY StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.68.68)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=LHR11G_DGMGRL)(INSTANCE_NAME=LHR11G)(SERVER=DEDICATED)))';
EDIT DATABASE 'LHR11GDG' SET PROPERTY StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.68.69)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=LHR11GDG_DGMGRL)(INSTANCE_NAME=LHR11GDG)(SERVER=DEDICATED)))';
show configuration
show database 'LHR11GDG' InconsistentProperties
show database 'LHR11GDG' statusreport;
alter system set archive_lag_target=0 scope=both sid='*';
alter system set log_archive_min_succeed_dest=1 scope=both sid='*';
alter system set Log_Archive_Trace=0 scope=both sid='*';
alter system set Log_Archive_Format='%t_%s_%r.dbf' scope=spfile sid='*';
配置完成后：
[oracle@lhr11g ~]$ dgmgrl sys/lhr@lhr11g
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration
Configuration - LHR11G
Protection Mode: MaxPerformance
Databases:
LHR11G - Primary database
LHR11GDG - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
5.2 配置FSFO
---- Fast-Start Failover FSFO配置
- 将Data Guard配置设置为MaxAvailability或MaxPerformance保护模式。
- 如果配置保护模式设置为MaxAvailability，请确保将主数据库和快速启动故障转移目标备用数据库的LogXptMode属性设置为SYNC。
- 如果配置保护模式设置为MaxPerformance，请确保将主数据库和快速启动故障转移目标备用数据库的LogXptMode属性设置为ASYNC。
- 确保主数据库和快速启动故障转移目标备用数据库都启用了闪回。
- 将主数据库FastStartFailoverTarget属性设置为所需目标备用数据库的DB_UNIQUE_NAME值，并将所需目标备用数据库FastStartFailoverTarget属性设置为主数据库的DB_UNIQUE_NAME值。
show resource verbose 'LHR11G' logxptmode on site 'LHR11G';
show resource verbose 'LHR11GDG' logxptmode on site 'LHR11GDG';
alter resource 'LHR11G' set property logxptmode='SYNC';
alter resource 'LHR11GDG' set property logxptmode='SYNC';
-- edit database LHR11GDG set property logxptmode='SYNC';
edit configuration set protection mode as maxavailability;
edit database 'LHR11G' set property 'FastStartFailoverTarget'='LHR11GDG';
edit database 'LHR11GDG' set property 'FastStartFailoverTarget'='LHR11G';
show database 'LHR11G' FastStartFailoverTarget
show database 'LHR11GDG' FastStartFailoverTarget
show database 'LHR11G' logxptmode
show database 'LHR11GDG' logxptmode
-- 10秒后开始自动切换
edit configuration set property FastStartFailoverThreshold=10;
-- 配置客户端自动故障转移的service
begin
DBMS_SERVICE.CREATE_SERVICE(service_name => 'dg_taf_lhr',
network_name => 'dg_taf_lhr',
aq_ha_notifications => TRUE,
failover_method => 'BASIC',
failover_type => 'SELECT',
failover_retries => 30,
failover_delay => 5);
end;
/
create or replace procedure dg_taf_proc_lhr is
v_role VARCHAR(30);
begin
select DATABASE_ROLE into v_role from V$DATABASE;
if v_role = 'PRIMARY' then
DBMS_SERVICE.START_SERVICE('dg_taf_lhr');
else
DBMS_SERVICE.STOP_SERVICE('dg_taf_lhr');
end if;
end;
/
create or replace TRIGGER dg_taf_trg_startup_lhr
after startup or db_role_change on database
begin
dg_taf_proc_lhr;
end;
/
exec dg_taf_proc_lhr ;
alter system switch logfile;
dg_taf =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.68.68)(PORT = 1521))
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.68.69)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg_taf_lhr)
(FAILOVER_MODE =
(TYPE = session)
(METHOD = basic)
(RETRIES = 180)
(DELAY = 5)
)
)
)
ENABLE FAST_START FAILOVER;
nohup dgmgrl -logfile '/tmp/observer_LHR11G.log' sys/lhr@LHR11GDG "start observer" &
tailf /tmp/observer_LHR11G.log
SHOW FAST_START FAILOVER;
配置结果：
DGMGRL> SHOW FAST_START FAILOVER;
Fast-Start Failover: ENABLED
Threshold: 10 seconds
Target: LHR11GDG
Observer: lhr11gdg
Lag Limit: 30 seconds
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Observer Reconnect: (none)
Observer Override: FALSE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Offline YES
Oracle Error Conditions:
(none)
5.3 重建DG
如果由于特殊原因导致备库不可用，必须进行重建，那么可以使用如下过程直接进行重建DG
startup force nomount
rman target sys/lhr@LHR11G auxiliary sys/lhr@LHR11GDG
duplicate target database
for standby nofilenamecheck
from active database
DORECOVER
;
alter database flashback on;
本文结束。若想学习DG其他更详细的内容，请咨询麦老师。
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2020-11-23，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！
腾讯云开发者
