原 Oracle中的DG日常维护SQL脚本整理（持续更新）

发布日期 2022年11月16日 · 已更新 2024年8月19日

0 383 1

👉 本文共约659个字，系统预计阅读时间或需3分钟。

常用脚本
进程信息
参数查询
手工注册
Fast-Start Failover FSFO配置
dgmgrl Data Guard Broker 配置
主备切换
物理备库switchover
物理备库failover切换
逻辑dg的switchover
DG搭建
级联备库
dbca搭建
far sync配置
19c rac dg dbca方式
19c 单实例 dg dbca方式
主备单实例配置非默认端口
基于同一个主机搭建
10g 物理DG搭建单实例
DG RAC + RAC
windows下搭建
10g脚本
归档丢失的恢复
物理DG参数优化调整
其它
定时

常用脚本

-- 备库启动实时应用功能
alter database recover managed standby database cancel;  
alter database recover managed standby database using current logfile disconnect from session;

alter database register or replace  logfile '/oradata/archivelog/2_1443988_931019082.dbf';

-- 不同步的原因，报错信息
select dest_id,error,status,log_sequence,applied_scn from v$archive_dest a where a.status<>'INACTIVE';

-- 查询应用日志序列号
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,(MAX(NB.SEQUENCE#) - 3) MAX_SEQUENCE#
          FROM V$ARCHIVED_LOG NB
         WHERE NB.APPLIED = 'YES'
and RESETLOGS_ID=(select max(nbb.RESETLOGS_ID) from  V$ARCHIVED_LOG nbb)
      GROUP BY NB.THREAD#,NB.RESETLOGS_ID) B
 WHERE B.THREAD# = A.THREAD#
   AND B.RESETLOGS_ID = A.RESETLOGS_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#;

set line 9999 pagesize 9999 
col  db_id  format a15
col GROUP# format 99999
select  GROUP# , DBID db_id, THREAD#, SEQUENCE#, BYTES,  USED, ARCHIVED, STATUS, FIRST_CHANGE#,  NEXT_CHANGE#,LAST_CHANGE# from v$standby_log ;

set line 1000
col member format a80
select * from v$log;
select * from v$logfile;

-- 主库触发
alter system set log_archive_dest_state_2='defer' sid='*';  
alter system archive log current;
alter system set log_archive_dest_state_2='ENABLE' sid='*';    
alter system archive log current;

-- 查询延迟
select name,value from gv$dataguard_stats;

-- gap
select thread#,low_sequence#,high_sequence# from v$archive_gap;

select * from gv$dataguard_status d order by d.inst_id,d.timestamp,d.message_num;

-- 主备库查询
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#;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

-- 备库启动实时应用功能

alter database recover managed standby database cancel;

alter database recover managed standby database using current logfile disconnect from session;

alter database register or replace logfile '/oradata/archivelog/2_1443988_931019082.dbf';

-- 不同步的原因，报错信息

select dest_id,error,status,log_sequence,applied_scn from v$archive_dest a where a.status<>'INACTIVE';

-- 查询应用日志序列号

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,(MAX(NB.SEQUENCE#) - 3) MAX_SEQUENCE#

FROM V$ARCHIVED_LOG NB

WHERE NB.APPLIED = 'YES'

and RESETLOGS_ID=(select max(nbb.RESETLOGS_ID) from V$ARCHIVED_LOG nbb)

GROUP BY NB.THREAD#,NB.RESETLOGS_ID) B

WHERE B.THREAD# = A.THREAD#

AND B.RESETLOGS_ID = A.RESETLOGS_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#;

set line 9999 pagesize 9999

col db_id format a15

col GROUP# format 99999

select GROUP# , DBID db_id, THREAD#, SEQUENCE#, BYTES, USED, ARCHIVED, STATUS, FIRST_CHANGE#, NEXT_CHANGE#,LAST_CHANGE# from v$standby_log ;

set line 1000

col member format a80

select * from v$log;

select * from v$logfile;

-- 主库触发

alter system set log_archive_dest_state_2='defer' sid='*';

alter system archive log current;

alter system set log_archive_dest_state_2='ENABLE' sid='*';

alter system archive log current;

-- 查询延迟

select name,value from gv$dataguard_stats;

-- gap

select thread#,low_sequence#,high_sequence# from v$archive_gap;

select * from gv$dataguard_status d order by d.inst_id,d.timestamp,d.message_num;

-- 主备库查询

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#;

进程信息

----主库： 
--ps -ef|grep -v grep|grep -E "ora_lns|ora_nsa|ora_nss"    
--10g: ps -ef|grep ora_lns  
--11g async: ps -ef|grep ora_nsa 
--11g sync:  ps -ef|grep ora_nss
----备库：RFS 进程
-----物理MRP进程实时应用

col group_# format a5
col CLIENT_PID format a8
set line 9999 pagesize 9999
SELECT a.PROCESS,
       a.client_process,
       a.client_pid,
       a.STATUS,
       a.GROUP# group_#,
       a.thread#,
       a.SEQUENCE#,
       a.DELAY_MINS,
       a.RESETLOG_ID, 
       c.SID,
       c.SERIAL#, 
       a.PID spid
  FROM V$MANAGED_STANDBY a,v$process b,v$session c
   WHERE a.PID=b.SPID
  and b.ADDR=c.PADDR;

--------rac 下 所有进程 ps -ef|grep -v grep|grep -E "ora_lns|ora_nsa|ora_nss"
col group_# format a5
col PROCESS format a8
col CLIENT_PID format a8
set line 9999 pagesize 9999
SELECT a.INST_ID,
       a.PROCESS,
       a.client_process,
       a.client_pid,
       a.STATUS,
       a.GROUP#         group_#,
       a.thread#,
       a.SEQUENCE#,
       a.DELAY_MINS,
       a.RESETLOG_ID,
       c.SID,
       c.SERIAL#,
       a.PID            spid,
       b.PNAME
  FROM gV$MANAGED_STANDBY a, gv$process b, gv$session c
 WHERE a.PID = b.SPID
   and b.ADDR = c.PADDR
   and a.INST_ID = b.INST_ID
   and b.INST_ID = c.INST_ID
order by a.INST_ID,b.PNAME;

----主库：

--ps -ef|grep -v grep|grep -E "ora_lns|ora_nsa|ora_nss"

--10g: ps -ef|grep ora_lns

--11g async: ps -ef|grep ora_nsa

--11g sync: ps -ef|grep ora_nss

----备库：RFS 进程

-----物理MRP进程实时应用

col group_# format a5

col CLIENT_PID format a8

set line 9999 pagesize 9999

SELECT a.PROCESS,

a.client_process,

a.client_pid,

a.STATUS,

a.GROUP# group_#,

a.thread#,

a.SEQUENCE#,

a.DELAY_MINS,

a.RESETLOG_ID,

c.SID,

c.SERIAL#,

a.PID spid

FROM V$MANAGED_STANDBY a,v$process b,v$session c

WHERE a.PID=b.SPID

and b.ADDR=c.PADDR;

--------rac 下所有进程 ps -ef|grep -v grep|grep -E "ora_lns|ora_nsa|ora_nss"

col group_# format a5

col PROCESS format a8

col CLIENT_PID format a8

set line 9999 pagesize 9999

SELECT a.INST_ID,

a.PROCESS,

a.client_process,

a.client_pid,

a.STATUS,

a.GROUP# group_#,

a.thread#,

a.SEQUENCE#,

a.DELAY_MINS,

a.RESETLOG_ID,

c.SID,

c.SERIAL#,

a.PID spid,

b.PNAME

FROM gV$MANAGED_STANDBY a, gv$process b, gv$session c

WHERE a.PID = b.SPID

and b.ADDR = c.PADDR

and a.INST_ID = b.INST_ID

and b.INST_ID = c.INST_ID

order by a.INST_ID,b.PNAME;

参数查询


----参数配置情况
col name format a25
col VALUE format a100
SELECT a.NAME,
       a.VALUE
FROM   v$parameter a
WHERE  (a.NAME LIKE '%file_name_convert' OR a.NAME LIKE 'fal%' OR
       a.NAME LIKE 'standby_file%' OR a.NAME = 'log_archive_config' OR
       A.NAME LIKE 'log_archive_dest_%')
AND    A.VALUE IS NOT NULL
and a.VALUE!='enable';

---全参数 单实例
set line 1000
set pagesize 1000
col name format a25
col VALUE format a100
SELECT a.NAME,
       a.VALUE
FROM   v$parameter a
WHERE  a.name in ('dg_broker_start','db_name','db_unique_name','log_archive_config','log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2','log_archive_max_processes','remote_login_passwordfile','db_file_name_convert','log_file_name_convert','standby_file_management','fal_server','fal_client','dg_broker_config_file1','dg_broker_config_file2');

---全参数 rac环境
set line 1000
set pagesize 1000
col name format a25
col VALUE format a100
SELECT a.NAME,
       i.instance_name,
       a.VALUE
FROM   gv$parameter a, gv$instance i
WHERE  a.inst_id = i.inst_id and a.name in ('dg_broker_start','db_name','db_unique_name','log_archive_config','log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2','log_archive_max_processes','remote_login_passwordfile','db_file_name_convert','log_file_name_convert','standby_file_management','fal_server','fal_client','dg_broker_config_file1','dg_broker_config_file2')
 ORDER BY a.name, i.instance_name;

----参数配置情况

col name format a25

col VALUE format a100

SELECT a.NAME,

a.VALUE

FROM v$parameter a

WHERE (a.NAME LIKE '%file_name_convert' OR a.NAME LIKE 'fal%' OR

a.NAME LIKE 'standby_file%' OR a.NAME = 'log_archive_config' OR

A.NAME LIKE 'log_archive_dest_%')

AND A.VALUE IS NOT NULL

and a.VALUE!='enable';

---全参数单实例

set line 1000

set pagesize 1000

col name format a25

col VALUE format a100

SELECT a.NAME,

a.VALUE

FROM v$parameter a

WHERE a.name in ('dg_broker_start','db_name','db_unique_name','log_archive_config','log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2','log_archive_max_processes','remote_login_passwordfile','db_file_name_convert','log_file_name_convert','standby_file_management','fal_server','fal_client','dg_broker_config_file1','dg_broker_config_file2');

---全参数 rac环境

set line 1000

set pagesize 1000

col name format a25

col VALUE format a100

SELECT a.NAME,

i.instance_name,

a.VALUE

FROM gv$parameter a, gv$instance i

WHERE a.inst_id = i.inst_id and a.name in ('dg_broker_start','db_name','db_unique_name','log_archive_config','log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2','log_archive_max_processes','remote_login_passwordfile','db_file_name_convert','log_file_name_convert','standby_file_management','fal_server','fal_client','dg_broker_config_file1','dg_broker_config_file2')

ORDER BY a.name, i.instance_name;

手工注册

------- 手工注册
ALTER DATABASE REGISTER OR REPLACE  LOGFILE '/arch/thread1_dest/arch_1_10.arc';
ALTER DATABASE REGISTER OR REPLACE  PHYSICAL LOGFILE '/arch/thread1_dest/arch_1_11.arc';

在物理备库上注册这些日志文件之后，重新打开重做应用进程。如果断档的归档日志较多，那么可以使用如下的SQL语句来生成要执行的SQL语句（如下的SQL也适用于逻辑DG）：
SELECT 'ALTER DATABASE REGISTER OR REPLACE LOGICAL LOGFILE ''/arch/thread1_dest/arch_1_' || a ||'.arc'';' LOGICAL_DG,
        'ALTER DATABASE REGISTER OR REPLACE  LOGFILE ''/arch/thread1_dest/arch_1_' || a ||'.arc'';' PHYSICAL_DG
  FROM (SELECT LEVEL A FROM DUAL CONNECT BY LEVEL <= 120)
 WHERE A >= 100;

------- 手工注册

ALTER DATABASE REGISTER OR REPLACE LOGFILE '/arch/thread1_dest/arch_1_10.arc';

ALTER DATABASE REGISTER OR REPLACE PHYSICAL LOGFILE '/arch/thread1_dest/arch_1_11.arc';

在物理备库上注册这些日志文件之后，重新打开重做应用进程。如果断档的归档日志较多，那么可以使用如下的SQL语句来生成要执行的SQL语句（如下的SQL也适用于逻辑DG）：

SELECT 'ALTER DATABASE REGISTER OR REPLACE LOGICAL LOGFILE ''/arch/thread1_dest/arch_1_' || a ||'.arc'';' LOGICAL_DG,

'ALTER DATABASE REGISTER OR REPLACE LOGFILE ''/arch/thread1_dest/arch_1_' || a ||'.arc'';' PHYSICAL_DG

FROM (SELECT LEVEL A FROM DUAL CONNECT BY LEVEL <= 120)

WHERE A >= 100;

Fast-Start Failover FSFO配置


------------------------------------------------------------------  Fast-Start Failover  FSFO配置

show resource verbose 'oradg11g'  logxptmode on site 'oradg11g';
show resource verbose 'oradgphy' logxptmode on site 'oradgphy';
alter resource 'oradg11g' set property logxptmode='SYNC';
alter resource 'oradgphy' set property logxptmode='SYNC';
-- edit database oradgphy set property logxptmode='SYNC';
edit configuration set protection mode as maxavailability;

edit database 'oradg11g' set property 'FastStartFailoverTarget'='oradgphy';
edit database 'oradgphy' set property 'FastStartFailoverTarget'='oradg11g';

show database oradg11g FastStartFailoverTarget
show database oradgphy FastStartFailoverTarget
show database oradg11g logxptmode
show database oradgphy logxptmode

edit configuration set property FastStartFailoverThreshold=10;

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;
/

ENABLE FAST_START FAILOVER;
dgmgrl  -logfile '/tmp/observer_oradg11g.log' sys/lhr@tns_oradgphy "start observer"  &
tailf /tmp/observer_oradg11g.log

------------------------------------------------------------------ Fast-Start Failover FSFO配置

show resource verbose 'oradg11g' logxptmode on site 'oradg11g';

show resource verbose 'oradgphy' logxptmode on site 'oradgphy';

alter resource 'oradg11g' set property logxptmode='SYNC';

alter resource 'oradgphy' set property logxptmode='SYNC';

-- edit database oradgphy set property logxptmode='SYNC';

edit configuration set protection mode as maxavailability;

edit database 'oradg11g' set property 'FastStartFailoverTarget'='oradgphy';

edit database 'oradgphy' set property 'FastStartFailoverTarget'='oradg11g';

show database oradg11g FastStartFailoverTarget

show database oradgphy FastStartFailoverTarget

show database oradg11g logxptmode

show database oradgphy logxptmode

edit configuration set property FastStartFailoverThreshold=10;

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;

ENABLE FAST_START FAILOVER;

dgmgrl -logfile '/tmp/observer_oradg11g.log' sys/lhr@tns_oradgphy "start observer" &

tailf /tmp/observer_oradg11g.log

dgmgrl Data Guard Broker 配置


------------------------------------------------------------------  Data Guard Broker 配置
set line 9999
col name format a10 
col FS_FAILOVER_OBSERVER_HOST format a20 
col DB_UNIQUE_NAME format a15
select dbid,name, DB_UNIQUE_NAME,RESETLOGS_CHANGE#,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
SELECT d.DBID,
       d.DB_UNIQUE_NAME,
       d.FORCE_LOGGING,
       d.FLASHBACK_ON,
       DATAGUARD_BROKER,
       d.FS_FAILOVER_STATUS,
       d.FS_FAILOVER_CURRENT_TARGET,
       d.FS_FAILOVER_THRESHOLD,
       d.FS_FAILOVER_OBSERVER_PRESENT,
       d.FS_FAILOVER_OBSERVER_HOST
  FROM v$database d;

col name for a100
set linesize 9999  pagesize 9999
SELECT dest_id,
       THREAD#,
       NAME,
       sequence#,
       archived,
       applied, 
       a.NEXT_CHANGE#
FROM   v$archived_log a
WHERE  a.sequence# >= 150
AND    resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
ORDER  BY a.THREAD#,
          a.sequence#,
      a.dest_id;

alter system set dg_broker_start=true scope=both;

dgmgrl  connect sys/lhr@tns_oradg11g

 create configuration 'fsf_oradg11g_lhr' as
 primary database is 'oradg11g'
 connect identifier is tns_oradg11g_dgmgrl;

 add database 'oradgphy' as
 connect identifier is tns_oradgphy_dgmgrl
 maintained as physical;

ADD DATABASE oradglg AS CONNECT IDENTIFIER IS tns_oradglg MAINTAINED AS LOGICAL;

enable configuration

show database verbose oradg11g
show database verbose oradgphy
show database verbose oradglg

EDIT DATABASE oradg11g SET PROPERTY StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradg11g)(INSTANCE_NAME=oradg11g)(SERVER=DEDICATED)))';
EDIT DATABASE oradgphy SET PROPERTY StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradgphy)(INSTANCE_NAME=oradgphy)(SERVER=DEDICATED)))';
EDIT DATABASE oradglg SET PROPERTY StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradglg)(INSTANCE_NAME=oradglg)(SERVER=DEDICATED)))';
show configuration

------------------------------------------------------------------ Data Guard Broker 配置

set line 9999

col name format a10

col FS_FAILOVER_OBSERVER_HOST format a20

col DB_UNIQUE_NAME format a15

select dbid,name, DB_UNIQUE_NAME,RESETLOGS_CHANGE#,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

SELECT d.DBID,

d.DB_UNIQUE_NAME,

d.FORCE_LOGGING,

d.FLASHBACK_ON,

DATAGUARD_BROKER,

d.FS_FAILOVER_STATUS,

d.FS_FAILOVER_CURRENT_TARGET,

d.FS_FAILOVER_THRESHOLD,

d.FS_FAILOVER_OBSERVER_PRESENT,

d.FS_FAILOVER_OBSERVER_HOST

FROM v$database d;

col name for a100

set linesize 9999 pagesize 9999

SELECT dest_id,

THREAD#,

NAME,

sequence#,

archived,

applied,

a.NEXT_CHANGE#

FROM v$archived_log a

WHERE a.sequence# >= 150

AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)

ORDER BY a.THREAD#,

a.sequence#,

a.dest_id;

alter system set dg_broker_start=true scope=both;

dgmgrl connect sys/lhr@tns_oradg11g

create configuration 'fsf_oradg11g_lhr' as

primary database is 'oradg11g'

connect identifier is tns_oradg11g_dgmgrl;

add database 'oradgphy' as

connect identifier is tns_oradgphy_dgmgrl

maintained as physical;

ADD DATABASE oradglg AS CONNECT IDENTIFIER IS tns_oradglg MAINTAINED AS LOGICAL;

enable configuration

show database verbose oradg11g

show database verbose oradgphy

show database verbose oradglg

EDIT DATABASE oradg11g SET PROPERTY StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradg11g)(INSTANCE_NAME=oradg11g)(SERVER=DEDICATED)))';

EDIT DATABASE oradgphy SET PROPERTY StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradgphy)(INSTANCE_NAME=oradgphy)(SERVER=DEDICATED)))';

EDIT DATABASE oradglg SET PROPERTY StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradglg)(INSTANCE_NAME=oradglg)(SERVER=DEDICATED)))';

show configuration

主备切换

物理备库switchover


---------------- 物理备库switchover 永远先主库后备库
select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;
alter system switch logfile;
---日志应用
col name for a100
set linesize 9999  pagesize 9999
select THREAD#,name,sequence#,archived,applied from v$archived_log a where a.sequence#>=10 AND    resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d) order by THREAD#,sequence#;

--主库
alter database commit to switchover to physical standby with session shutdown; 
稍等。。。。等待数据库挂掉后再启动到mount，千万别shutdown immediate,用shutdown abort
--Switchover: Primary controlfile converted to standby controlfile succesfully.
--Switchover: Complete - Database shutdown required
--Completed: alter database commit to switchover to physical standby with session shutdown
--Process (ospid 26918) is suspended due to switchover to physical standby operation.
--idle dispatcher 'D000' terminated, pid = (25, 6)
startup  mount;

--备库
select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name, flashback_on  from v$database; 
alter database commit to switchover to primary with session shutdown; 
alter database open;

--新备库即源主库
alter database open; 
alter database recover managed standby database using current logfile disconnect;

---------------- 物理备库switchover 永远先主库后备库

select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

alter system switch logfile;

---日志应用

col name for a100

set linesize 9999 pagesize 9999

select THREAD#,name,sequence#,archived,applied from v$archived_log a where a.sequence#>=10 AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d) order by THREAD#,sequence#;

--主库

alter database commit to switchover to physical standby with session shutdown;

稍等。。。。等待数据库挂掉后再启动到mount，千万别shutdown immediate,用shutdown abort

--Switchover: Primary controlfile converted to standby controlfile succesfully.

--Switchover: Complete - Database shutdown required

--Completed: alter database commit to switchover to physical standby with session shutdown

--Process (ospid 26918) is suspended due to switchover to physical standby operation.

--idle dispatcher 'D000' terminated, pid = (25, 6)

startup mount;

--备库

select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name, flashback_on from v$database;

alter database commit to switchover to primary with session shutdown;

alter database open;

--新备库即源主库

alter database open;

alter database recover managed standby database using current logfile disconnect;

物理备库failover切换


---------------- 物理备库failover切换

select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name, flashback_on  from v$database; 
--主库若可以启动到mount状态，则可以进行日志刷新，以保障数据不丢失：
alter system flush redo to 'ora11gsy'; 
 --注意：这个过程并不会经常成功执行，而且在10g这样的版本下也没有办法自动flush redo。解决的方法也是有的，就是从Primary目录中，将日志拷贝到Standby端，手工去加载。 
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1'; 
--如果我们不能拿到Primary端的日志，比如磁盘存储故障，数据不能恢复。同时数据保护模式没有确保日志及时传递到Standby端，这个时候就会丢数据。此时，我们需要手工的进行激活standby。 
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE; 

--备库直接操作
select * from v$archive_gap; 
--若下边2个操作顺利进行，没有额外错误报出。说明这个过程中没有数据损失情况发生，也就意味着虽然发生了failover，但是不会有数据损失。 
alter database recover managed standby database cancel; 
alter database recover managed standby database finish force; 

alter database commit to switchover to primary with session shutdown;
alter database open; 

---备库切主库（物理备库failover切换）
alter database recover managed standby database finish force;
alter database commit to switchover to primary with session shutdown; 
alter database open; 
select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

--将备库强制启动为主库：
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

---------------- flashback 后切换主库为备库
alter database convert to physical standby;

---------------- 物理备库failover切换

select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name, flashback_on from v$database;

--主库若可以启动到mount状态，则可以进行日志刷新，以保障数据不丢失：

alter system flush redo to 'ora11gsy';

--注意：这个过程并不会经常成功执行，而且在10g这样的版本下也没有办法自动flush redo。解决的方法也是有的，就是从Primary目录中，将日志拷贝到Standby端，手工去加载。

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

--如果我们不能拿到Primary端的日志，比如磁盘存储故障，数据不能恢复。同时数据保护模式没有确保日志及时传递到Standby端，这个时候就会丢数据。此时，我们需要手工的进行激活standby。

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

--备库直接操作

select * from v$archive_gap;

--若下边2个操作顺利进行，没有额外错误报出。说明这个过程中没有数据损失情况发生，也就意味着虽然发生了failover，但是不会有数据损失。

alter database recover managed standby database cancel;

alter database recover managed standby database finish force;

alter database commit to switchover to primary with session shutdown;

alter database open;

---备库切主库（物理备库failover切换）

alter database recover managed standby database finish force;

alter database commit to switchover to primary with session shutdown;

alter database open;

select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

--将备库强制启动为主库：

ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

---------------- flashback 后切换主库为备库

alter database convert to physical standby;

逻辑dg的switchover


---------------- 逻辑dg的switchover
col name format a25
col VALUE format a20
SELECT a.NAME,
       a.VALUE
FROM   v$parameter a
WHERE  (a.NAME LIKE '%file_name_convert' OR a.NAME LIKE 'fal%' OR
       a.NAME LIKE 'standby_file%' OR a.NAME = 'log_archive_config' OR
       A.NAME LIKE 'log_archive_dest_%')
AND    A.VALUE IS NOT NULL;

select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name, flashback_on  from v$database;

--主
alter database prepare to switchover to logical standby;
--备
alter database prepare to switchover to primary;

--主
select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name, flashback_on  from v$database;
alter database commit to switchover to logical standby;

--备
select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name, flashback_on  from v$database;
alter database commit to switchover to primary;

---------------- 逻辑dg的switchover

col name format a25

col VALUE format a20

SELECT a.NAME,

a.VALUE

FROM v$parameter a

WHERE (a.NAME LIKE '%file_name_convert' OR a.NAME LIKE 'fal%' OR

a.NAME LIKE 'standby_file%' OR a.NAME = 'log_archive_config' OR

A.NAME LIKE 'log_archive_dest_%')

AND A.VALUE IS NOT NULL;

select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name, flashback_on from v$database;

--主

alter database prepare to switchover to logical standby;

--备

alter database prepare to switchover to primary;

--主

select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name, flashback_on from v$database;

alter database commit to switchover to logical standby;

--备

select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name, flashback_on from v$database;

alter database commit to switchover to primary;

DG搭建

级联备库


--------------18c dbca创建级联备库

--------配置tns
CDBLHR18cdg2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDBLHR18cdg2)
    )
  )  

--------配置监听

    (SID_DESC =
      (GLOBAL_DBNAME = CDBLHR18cdg2)
      (ORACLE_HOME = /u08/app/oracle/product/18.0.0/dbhome_1)
      (SID_NAME = CDBLHR18cdg2)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = CDBLHR18cdg2_DGMGRL)
      (ORACLE_HOME = /u08/app/oracle/product/18.0.0/dbhome_1)
      (SID_NAME = CDBLHR18cdg2)
    )

--搭建过程是：备库和第2备库的操作
dbca -silent -createDuplicateDB \
-gdbName CDBLHR18cdg2 \
-sid CDBLHR18cdg2 \
-sysPassword lhr \
-primaryDBConnectionString 192.168.59.130:1521/CDBLHR18cdg \
-nodelist rhel6lhr \
-databaseConfigType SINGLE \
-createAsStandby -dbUniqueName CDBLHR18cdg2 \
-datafileDestination '/u01/app/oracle/oradata/CDBLHR18cdg2/' \
-initParams db_create_file_dest=/u01/app/oracle/oradata/CDBLHR18cdg2/,db_create_online_log_dest_1=/u01/app/oracle/oradata/CDBLHR18cdg2/,sga_target=800M,memory_max_target=0,memory_target=0

---全参数 rac环境
set line 1000
set pagesize 1000
col name format a25
col VALUE format a100
SELECT a.NAME,
       i.instance_name,
       a.VALUE
FROM   gv$parameter a, gv$instance i
WHERE  a.inst_id = i.inst_id and a.name in ('dg_broker_start','db_name','db_unique_name','log_archive_config','log_archive_dest_1','log_archive_dest_2','log_archive_dest_3','log_archive_dest_state_1','log_archive_dest_state_2','log_archive_dest_state_3','log_archive_max_processes','remote_login_passwordfile','db_file_name_convert','log_file_name_convert','standby_file_management','fal_server','fal_client','dg_broker_config_file1','dg_broker_config_file2')
ORDER BY a.name, i.instance_name;

--主库修改参数
Alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(CDBLHR18c,CDBLHR18cdg,CDBLHR18cdg2)';
alter system set LOG_ARCHIVE_DEST_3= 'SERVICE=CDBLHR18cdg2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=CDBLHR18cdg2';

--一级备库修改参数
alter system set log_archive_config='dg_config=(CDBLHR18c,CDBLHR18cdg,CDBLHR18cdg2)';
alter system set LOG_ARCHIVE_DEST_3= 'SERVICE=CDBLHR18cdg2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=CDBLHR18cdg2';

-- 二级备库修改参数
alter system set log_archive_config='dg_config=(CDBLHR18c,CDBLHR18cdg,CDBLHR18cdg2)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDBLHR18cdg2';

alter system set db_file_name_convert='/u01/app/oracle/oradata/CDBLHR18cdg/','/u01/app/oracle/oradata/CDBLHR18cdg2/' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/CDBLHR18cdg/','/u01/app/oracle/oradata/CDBLHR18cdg2/' scope=spfile;

alter system set fal_client='CDBLHR18cdg2'; 
alter system set fal_server='CDBLHR18cdg';

shutdown immediate
startup
alter system register;

--备库查询实时应用
alter database recover managed standby database cancel;
ALTER DATABASE flashback on;
alter database recover managed standby database using current logfile disconnect;
! ps -ef|grep ora_mrp

COL NAME FOR A100
SET LINESIZE 9999  PAGESIZE 9999
COL NEXT_CHANGE# FOR 999999999999999
SELECT THREAD#, NAME, SEQUENCE#, ARCHIVED, APPLIED, A.NEXT_CHANGE#
  FROM V$ARCHIVED_LOG A
 WHERE A.SEQUENCE# >= (SELECT MAX(B.SEQUENCE#) - 3
                         FROM V$ARCHIVED_LOG B
                        WHERE B.THREAD# = A.THREAD#
                          AND B.RESETLOGS_CHANGE# = A.RESETLOGS_CHANGE#
                          AND B.RESETLOGS_CHANGE# =
                              (SELECT D.RESETLOGS_CHANGE# FROM V$DATABASE D)
                          AND B.APPLIED = 'YES'  
              AND B.DEST_ID=A.DEST_ID
                          GROUP BY B.THREAD#)       
  AND A.STANDBY_DEST='NO'
 ORDER BY A.THREAD#, A.SEQUENCE#;

alter system set dg_broker_start=true sid='*';

dgmgrl sys/lhr@CDBLHR18cdg
show configuration

add database CDBLHR18cdg2 as
connect identifier is CDBLHR18cdg2;

show database verbose CDBLHR18c;
show database verbose CDBLHR18cdg;
show database verbose CDBLHR18cdg2;

alter database recover managed standby database cancel;

--必须配置主库
edit database cdblhr18c set property  RedoRoutes    ='(cdblhr18c:cdblhr18cdg ASYNC)';
edit database cdblhr18cdg set property  RedoRoutes    ='(cdblhr18c:cdblhr18cdg2 ASYNC)';

show database cdblhr18c  RedoRoutes
show database cdblhr18cdg  RedoRoutes
show database cdblhr18cdg2  RedoRoutes

enable database CDBLHR18cdg2

edit database CDBLHR18cdg2 set property  StaticConnectIdentifier    ='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CDBLHR18cdg2)(INSTANCE_NAME=CDBLHR18cdg2)(SERVER=DEDICATED)))';

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

--------------18c dbca创建级联备库

--------配置tns

CDBLHR18cdg2 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = CDBLHR18cdg2)

)

--------配置监听

(SID_DESC =

(GLOBAL_DBNAME = CDBLHR18cdg2)

(ORACLE_HOME = /u08/app/oracle/product/18.0.0/dbhome_1)

(SID_NAME = CDBLHR18cdg2)

)

(SID_DESC =

(GLOBAL_DBNAME = CDBLHR18cdg2_DGMGRL)

(ORACLE_HOME = /u08/app/oracle/product/18.0.0/dbhome_1)

(SID_NAME = CDBLHR18cdg2)

)

--搭建过程是：备库和第2备库的操作

dbca -silent -createDuplicateDB \

-gdbName CDBLHR18cdg2 \

-sid CDBLHR18cdg2 \

-sysPassword lhr \

-primaryDBConnectionString 192.168.59.130:1521/CDBLHR18cdg \

-nodelist rhel6lhr \

-databaseConfigType SINGLE \

-createAsStandby -dbUniqueName CDBLHR18cdg2 \

-datafileDestination '/u01/app/oracle/oradata/CDBLHR18cdg2/' \

-initParams db_create_file_dest=/u01/app/oracle/oradata/CDBLHR18cdg2/,db_create_online_log_dest_1=/u01/app/oracle/oradata/CDBLHR18cdg2/,sga_target=800M,memory_max_target=0,memory_target=0

---全参数 rac环境

set line 1000

set pagesize 1000

col name format a25

col VALUE format a100

SELECT a.NAME,

i.instance_name,

a.VALUE

FROM gv$parameter a, gv$instance i

WHERE a.inst_id = i.inst_id and a.name in ('dg_broker_start','db_name','db_unique_name','log_archive_config','log_archive_dest_1','log_archive_dest_2','log_archive_dest_3','log_archive_dest_state_1','log_archive_dest_state_2','log_archive_dest_state_3','log_archive_max_processes','remote_login_passwordfile','db_file_name_convert','log_file_name_convert','standby_file_management','fal_server','fal_client','dg_broker_config_file1','dg_broker_config_file2')

ORDER BY a.name, i.instance_name;

--主库修改参数

Alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(CDBLHR18c,CDBLHR18cdg,CDBLHR18cdg2)';

alter system set LOG_ARCHIVE_DEST_3= 'SERVICE=CDBLHR18cdg2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=CDBLHR18cdg2';

--一级备库修改参数

alter system set log_archive_config='dg_config=(CDBLHR18c,CDBLHR18cdg,CDBLHR18cdg2)';

alter system set LOG_ARCHIVE_DEST_3= 'SERVICE=CDBLHR18cdg2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=CDBLHR18cdg2';

-- 二级备库修改参数

alter system set log_archive_config='dg_config=(CDBLHR18c,CDBLHR18cdg,CDBLHR18cdg2)';

alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDBLHR18cdg2';

alter system set db_file_name_convert='/u01/app/oracle/oradata/CDBLHR18cdg/','/u01/app/oracle/oradata/CDBLHR18cdg2/' scope=spfile;

alter system set log_file_name_convert='/u01/app/oracle/oradata/CDBLHR18cdg/','/u01/app/oracle/oradata/CDBLHR18cdg2/' scope=spfile;

alter system set fal_client='CDBLHR18cdg2';

alter system set fal_server='CDBLHR18cdg';

shutdown immediate

startup

alter system register;

--备库查询实时应用

alter database recover managed standby database cancel;

ALTER DATABASE flashback on;

alter database recover managed standby database using current logfile disconnect;

! ps -ef|grep ora_mrp

COL NAME FOR A100

SET LINESIZE 9999 PAGESIZE 9999

COL NEXT_CHANGE# FOR 999999999999999

SELECT THREAD#, NAME, SEQUENCE#, ARCHIVED, APPLIED, A.NEXT_CHANGE#

FROM V$ARCHIVED_LOG A

WHERE A.SEQUENCE# >= (SELECT MAX(B.SEQUENCE#) - 3

FROM V$ARCHIVED_LOG B

WHERE B.THREAD# = A.THREAD#

AND B.RESETLOGS_CHANGE# = A.RESETLOGS_CHANGE#

AND B.RESETLOGS_CHANGE# =

(SELECT D.RESETLOGS_CHANGE# FROM V$DATABASE D)

AND B.APPLIED = 'YES'

AND B.DEST_ID=A.DEST_ID

GROUP BY B.THREAD#)

AND A.STANDBY_DEST='NO'

ORDER BY A.THREAD#, A.SEQUENCE#;

alter system set dg_broker_start=true sid='*';

dgmgrl sys/lhr@CDBLHR18cdg

show configuration

add database CDBLHR18cdg2 as

connect identifier is CDBLHR18cdg2;

show database verbose CDBLHR18c;

show database verbose CDBLHR18cdg;

show database verbose CDBLHR18cdg2;

alter database recover managed standby database cancel;

--必须配置主库

edit database cdblhr18c set property RedoRoutes ='(cdblhr18c:cdblhr18cdg ASYNC)';

edit database cdblhr18cdg set property RedoRoutes ='(cdblhr18c:cdblhr18cdg2 ASYNC)';

show database cdblhr18c RedoRoutes

show database cdblhr18cdg RedoRoutes

show database cdblhr18cdg2 RedoRoutes

enable database CDBLHR18cdg2

edit database CDBLHR18cdg2 set property StaticConnectIdentifier ='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CDBLHR18cdg2)(INSTANCE_NAME=CDBLHR18cdg2)(SERVER=DEDICATED)))';

dbca搭建


-------------------- dbca搭建级联备库

--------配置tns
lhr19c =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.52)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lhr19c)
    )
  )

lhr19cdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.52)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lhr19cdg)
    )
  )

lhr19dg2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.52)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lhr19dg2)
    )
  )  

--------配置监听
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.52)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = lhr19c)
      (ORACLE_HOME = /u01/app/oracle/product/19.2.0/dbhome_1)
      (SID_NAME = lhr19c)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = lhr19cdg)
      (ORACLE_HOME = /u01/app/oracle/product/19.2.0/dbhome_1)
      (SID_NAME = lhr19cdg)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = lhr19dg2)
      (ORACLE_HOME = /u01/app/oracle/product/19.2.0/dbhome_1)
      (SID_NAME = lhr19dg2)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = lhr19c_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/19.2.0/dbhome_1)
      (SID_NAME = lhr19c)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = lhr19cdg_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/19.2.0/dbhome_1)
      (SID_NAME = lhr19cdg)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = lhr19dg2_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/19.2.0/dbhome_1)
      (SID_NAME = lhr19dg2)
    )
  )

--搭建过程依然是：主库和第2备库的操作
dbca -silent -createDuplicateDB \
-gdbName lhr19c \
-sid lhr19dg2 \
-sysPassword lhr \
-primaryDBConnectionString 192.168.59.52:1521/lhr19c \
-nodelist raclhr-18c-n1 \
-databaseConfigType SINGLE \
-createAsStandby -dbUniqueName lhr19dg2 \
-datafileDestination '/u01/app/oracle/oradata/lhr19dg2/' \
-initParams db_create_file_dest=/u01/app/oracle/oradata/lhr19dg2/,db_create_online_log_dest_1=/u01/app/oracle/oradata/lhr19dg2/,sga_max_target=800M,memory_max_target=0,memory_target=0

---全参数 rac环境
set line 1000
set pagesize 1000
col name format a25
col VALUE format a100
SELECT a.NAME,
       i.instance_name,
       a.VALUE
FROM   gv$parameter a, gv$instance i
WHERE  a.inst_id = i.inst_id and a.name in ('dg_broker_start','db_name','db_unique_name','log_archive_config','log_archive_dest_1','log_archive_dest_2','log_archive_dest_3','log_archive_dest_state_1','log_archive_dest_state_2','log_archive_dest_state_3','log_archive_max_processes','remote_login_passwordfile','db_file_name_convert','log_file_name_convert','standby_file_management','fal_server','fal_client','dg_broker_config_file1','dg_broker_config_file2')
ORDER BY a.name, i.instance_name;

--主库修改参数
Alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(lhr19c,lhr19cdg,lhr19dg2)';

--一级备库修改参数
alter system set log_archive_config='dg_config=(lhr19c,lhr19cdg,lhr19dg2)';
alter system set LOG_ARCHIVE_DEST_3= 'SERVICE=lhr19dg2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=lhr19dg2';

-- 二级备库修改参数
alter system set log_archive_config='dg_config=(lhr19c,lhr19cdg,lhr19dg2)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lhr19dg2';

alter system set db_file_name_convert='/u01/app/oracle/oradata/LHR19CDG/','/u01/app/oracle/oradata/LHR19DG2/' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/LHR19CDG/','/u01/app/oracle/oradata/LHR19DG2/' scope=spfile;

alter system set fal_client='lhr19dg2'; 
alter system set fal_server='lhr19cdg';

shutdown immediate
startup
alter system register;

--备库查询实时应用
alter database recover managed standby database cancel;
ALTER DATABASE flashback on;
alter database recover managed standby database using current logfile disconnect;
! ps -ef|grep ora_mrp

COL NAME FOR A100
SET LINESIZE 9999  PAGESIZE 9999
COL NEXT_CHANGE# FOR 999999999999999
SELECT THREAD#, NAME, SEQUENCE#, ARCHIVED, APPLIED, A.NEXT_CHANGE#
  FROM V$ARCHIVED_LOG A
 WHERE A.SEQUENCE# >= (SELECT MAX(B.SEQUENCE#) - 3
                         FROM V$ARCHIVED_LOG B
                        WHERE B.THREAD# = A.THREAD#
                          AND B.RESETLOGS_CHANGE# = A.RESETLOGS_CHANGE#
                          AND B.RESETLOGS_CHANGE# =
                              (SELECT D.RESETLOGS_CHANGE# FROM V$DATABASE D)
                          AND B.APPLIED = 'YES' 
  GROUP BY B.THREAD#)
 ORDER BY A.THREAD#, A.SEQUENCE#;

alter system set dg_broker_start=true sid='*';

dgmgrl sys/lhr@lhr19c
show configuration

add database lhr19dg2 as
connect identifier is lhr19dg2
maintained as physical;

enable configuration
enable database lhr19dg2

show database verbose lhr19c;
show database verbose lhr19cdg;
show database verbose lhr19dg2;

alter database recover managed standby database cancel;

alter database flashback on;
select flashback_on,force_logging from v$database;
alter database recover managed standby database using current logfile disconnect;

edit database lhr19c set property 'FastStartFailoverTarget'='lhr19cdg';
edit database lhr19cdg set property 'FastStartFailoverTarget'='lhr19c';

edit database lhr19cdg set property  StaticConnectIdentifier    ='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.52)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=lhr19cdg_DGMGRL)(INSTANCE_NAME=lhr19cdg)(SERVER=DEDICATED)))';
edit database lhr19c set property  StaticConnectIdentifier    ='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.52)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=lhr19c_DGMGRL)(INSTANCE_NAME=lhr19c)(SERVER=DEDICATED)))';

--必须配置主库
edit database cdblhr18c set property  RedoRoutes    ='(cdblhr18c:cdblhr18cdg ASYNC)';
edit database cdblhr18cdg set property  RedoRoutes    ='(cdblhr18c:cdblhr18cdg2 ASYNC)';

show database cdblhr18c  RedoRoutes
show database cdblhr18cdg  RedoRoutes
show database cdblhr18cdg2  RedoRoutes

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

-------------------- dbca搭建级联备库

--------配置tns

lhr19c =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = lhr19c)

)

lhr19cdg =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = lhr19cdg)

)

lhr19dg2 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = lhr19dg2)

)

--------配置监听

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = lhr19c)