Oracle DG常用SQL语句

由小麦苗 · 2022年11月16日

0 128 1

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

----------------------------------- 主库、物理备库、逻辑备库

----主库： 
--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进程实时应用

set line 9999
col name format a10 
col FS_FAILOVER_OBSERVER_HOST format a20 
select INST_ID, dbid,name,DB_UNIQUE_NAME,flashback_on,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

SELECT * FROM V$DATAGUARD_CONFIG;

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

------------------------------ 进程信息 通用
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
SELECT * FROM sys.vw_dg_infor_lhr;

col group_# format a5
col CLIENT_PID format a8
set line 9999 pagesize 9999
SELECT * FROM sys.VW_DG_PROCESS_LHR;

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;

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

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

------------------------------ 主备库信息
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#;

 --备库
select al.thrd "Thread",
      almax   "Last Seq Received",
      lhmax   "Last Seq Applied"
 from (select thread# thrd, max(sequence#) almax
         from v$archived_log
        where resetlogs_change# =
              (select resetlogs_change# from v$database)
        group by thread#) al,
      (select thread# thrd, max(sequence#) lhmax
         from v$log_history
        where resetlogs_change# =
              (select resetlogs_change# from v$database)
        group by thread#) lh
where al.thrd = lh.thrd;

---是否启用实时应用
--11g 主库： select RECOVERY_MODE from v$archive_dest_status;

-----------------------------------  物理备库
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE;

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

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

alter system set log_archive_dest_state_2='defer';  
alter system switch logfile;  
alter system set log_archive_dest_state_2='ENABLE';    
alter system switch 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;

---是否启用实时应用
ps -ef|grep ora_mrp
--以下视图结果为：OPEN_READ-ONLY、MANAGED REAL TIME APPLY
select a.DATABASE_MODE, a.RECOVERY_MODE
  from v$archive_dest_status a
 where a.DATABASE_MODE <> 'UNKNOWN'  and status='VALID'  and a.dest_name<>'STANDBY_ARCHIVE_DEST'
   and SRL = (SELECT CASE
                       WHEN NB.DATABASE_ROLE like '%STANDBY%' then
                        'NO'
                       ELSE
                        'YES'
                     END
                FROM V$DATABASE NB);

------------物理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,(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#;

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

------------物理dg日志应用情况(备库查询为准)--级联DG
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#;

--在主库或备库查询哪些归档日志已经应用到备库，这些日志可以被删除了：
SELECT THREAD#,
       NAME,
       SEQUENCE#,
       ARCHIVED,
       APPLIED,
       A.NEXT_CHANGE#,
       A.COMPLETION_TIME,
       'delete archivelog sequence ' || A.SEQUENCE# || ' thread ' ||
       A.THREAD# || ';' EXEC_SQL
FROM   V$ARCHIVED_LOG A
WHERE  (A.THREAD#, A.SEQUENCE#, a.RESETLOGS_CHANGE#) IN
       (SELECT b.THREAD#,
               b.SEQUENCE#,
               b.RESETLOGS_CHANGE#
        FROM   V$ARCHIVED_LOG B
        WHERE  B.APPLIED = 'YES'
        AND    b.COMPLETION_TIME <= SYSDATE - 3)      
AND    a.NAME NOT IN (SELECT b.DESTINATION
                      FROM   v$archive_dest b
                      WHERE  b.DESTINATION IS NOT NULL)
AND    A.COMPLETION_TIME <= SYSDATE - 3
ORDER  BY A.THREAD#,
          A.SEQUENCE#;

----物理备库应用进程日志
--select * from gv$dataguard_status d order by d.inst_id,d.timestamp,d.message_num;
set line 9999 pagesize 9999
col message format a85
SELECT inst_id, severity, FACILITY, TIMESTAMP, MESSAGE
  FROM (select d.inst_id,
               FACILITY,
               SEVERITY,
               TIMESTAMP,
               MESSAGE,
               rank() over(partition by d.inst_id ORDER BY d.message_num desc) rank_order
          from gv$dataguard_status d)
 where rank_order <= 5
 order by inst_id, rank_order desc;

----参数配置情况
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; 

----------- 查询备库归档应用是否有问题
select count(*) from (
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)
 WHERE (ERROR IS NOT NULL OR STATUS <> 'VALID' OR
       LAST_ARCHIVED > APPLIED_SEQ# + 2 OR Current_Seq# > LAST_ARCHIVED + 3 );

------------------------------------- 逻辑备库
ALTER DATABASE STOP LOGICAL STANDBY APPLY; ---停止应用，等待事务完成
ALTER DATABASE ABORT LOGICAL STANDBY APPLY;--不等待事务完成就停止

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; ---实时
ALTER DATABASE START LOGICAL STANDBY APPLY; --非实时

ALTER DATABASE START LOGICAL STANDBY APPLY  IMMEDIATE SKIP FAILED TRANSACTION; 

---是否启用实时应用
ps -ef|grep -i ora_lsp
11g：select RECOVERY_MODE from v$archive_dest_status;
---通用：
col REALTIME_APPLY format a15
col STATE format a20
select * from v$Logstdby_state;

------------日志应用情况
set line 9999 pagesize 9999
col FILE_NAME format a120
select sequence#, file_name, applied, TIMESTAMP from dba_logstdby_log d order by d.sequence#; 

SET LINE 9999 PAGESIZE 9999
COL FILE_NAME FORMAT A120
SELECT THREAD#,SEQUENCE#, FILE_NAME, APPLIED, TIMESTAMP 
FROM DBA_LOGSTDBY_LOG D 
WHERE D.SEQUENCE# >=(SELECT MAX(SEQUENCE#)-3 FROM  DBA_LOGSTDBY_LOG NB WHERE  NB.THREAD#=D.THREAD# AND NB.APPLIED='YES' ) 
ORDER BY THREAD#,D.SEQUENCE#; 

--查看备库是否有任何DDL/DML语句未成功应用
COL EVENT_TIMESTAMP FORMAT A30
COL EVENT FORMAT A40
COL EVENT_STATUS FORMAT A80
SELECT A.EVENT_TIME,
       A.CURRENT_SCN,
       A.COMMIT_SCN,
       XIDUSN,
       XIDSLT,
       XIDSQN,
       TO_CHAR(EVENT) EVENT,
       A.STATUS_CODE,
       STATUS EVENT_STATUS
  FROM DBA_LOGSTDBY_EVENTS A
 WHERE A.EVENT_TIME >= SYSDATE - 10 / 1660
 ORDER BY A.EVENT_TIME ;

----------------- 物理DG参数优化调整
alter system set parallel_execution_message_size=32768 scope=spfile;   --默认16384
alter system set filesystemio_options=setall scope=spfile;              --默认 none
alter system set disk_asynch_io=true scope=spfile;                                --默认就是true
alter system set db_lost_write_protect=typical scope=spfile;                --默认是full
alter system set db_block_checksum=false scope=spfile ;              --默认是TYPICAL
alter system set DB_BLOCK_CHECKING=false scope=spfile ;            --默认是false
alter system set db_writer_processes=8 scope=spfile;                            --默认是6个
shutdown immediate;
startup nomount;
alter database mount standby database;
alter database recover managed standby database parallel 4 disconnect from session;  ----并行度根据CPU核数*2设定

----------------------------------------------------------------------------------------------------------------------------------- 
 ---------------------------------------------------- 10g脚本
 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 
  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;

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, 
       (SELECT MAX(sequence#) FROM v$log na WHERE na.thread# = al.thread#) Current_Seq#,
       MAX(sequence#) Last_Archived,
       max(CASE
             WHEN al.APPLIED = 'YES' AND ads.TYPE <> 'LOCAL' THEN
              al.sequence#
           end) APPLIED_SEQ# 
  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'
 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 
 ORDER BY al.thread#, ads.dest_id;

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#)-5 from v$log b where b.THREAD#=a.THREAD# )
AND    resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
ORDER  BY a.THREAD#,
          a.sequence#;

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

---修改如下几个参数需要重启数据库：
 1、修改归档模式
 2、修改参数：db_unique_name
 3、修改参数：db_file_name_convert
 4、修改参数：log_file_name_convert

--- RAC到单实例需要注意以下几个参数
*.thread=1
*.undo_tablespace='UNDOTBS1'
*.instance_number=1
*.cluster_database=false

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

--------------- v$standby_log
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 ;

select  (select instance_name from gv$instance na where na.inst_id=a.INST_ID) instance_name, GROUP#, DBID db_id, THREAD#, SEQUENCE#, BYTES,  USED, ARCHIVED, STATUS, FIRST_CHANGE#,  NEXT_CHANGE#,LAST_CHANGE# from gv$standby_log a order by INST_ID,THREAD#,GROUP#;

alter database drop standby logfile group 4,group 5,group 6,group 7;
alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/oradg11g/standby_redo04.log') size 50m; 
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/oradg11g/standby_redo05.log') size 50m; 
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/oradg11g/standby_redo06.log') size 50m; 
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/oradg11g/standby_redo07.log') size 50m; 

alter database add  logfile thread 1 group 11 ('+DATA','+DATA') size 50M ,group 12 ('+DATA','+DATA') size 50M;
alter database add  logfile thread 2 group 13 ('+DATA','+DATA') size 50M ,group 14 ('+DATA','+DATA') size 50M;

alter database add standby logfile thread 1 group 15 ('+DATADG','+DATADG') size 50M ,group 16 ('+DATADG','+DATADG') size 50M,group 17 ('+DATADG','+DATADG') size 50M;
alter database add standby logfile thread 2 group 18 ('+DATADG','+DATADG') size 50M ,group 19 ('+DATADG','+DATADG') size 50M,group 20 ('+DATADG','+DATADG') size 50M;

set line 9999
col HOST_NAME format a10　
select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS,ACTIVE_STATE,INSTANCE_ROLE,DATABASE_STATUS from gv$INSTANCE;
select INST_ID,name , open_mode, log_mode,force_logging from gv$database;

select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

set line 9999
col name format a10 
col FS_FAILOVER_OBSERVER_HOST format a20 
select dbid,name,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,
       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;

SQL> select INST_ID, name , open_mode, log_mode,force_logging,DATABASE_ROLE from gv$database;

NAME      OPEN_MODE        LOG_MODE     FOR
--------- -------------------- ------------ ---
RACDB     READ WRITE           NOARCHIVELOG YES
RACDB     READ WRITE           NOARCHIVELOG YES

alter system set db_unique_name='lhrrac1' scope=spfile sid='*';
alter system set log_archive_config='DG_CONFIG=(lhrrac1,lhrrac1DG)' sid='*';
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=lhrrac1 valid_for=(ALL_LOGFILES,ALL_ROLES)' sid='*';
alter system set log_archive_dest_2='SERVICE=TNS_lhrrac1DG LGWR ASYNC db_unique_name=lhrrac1DG valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)' sid='*';
alter system set log_archive_dest_state_1=ENABLE sid='*';   
alter system set log_archive_dest_state_2=ENABLE sid='*';    
alter system set log_archive_max_processes=4 sid='*';  
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile sid='*';

--Add below item when DB turn to standby role  
alter system set db_file_name_convert='+DATADG','+DATA' scope=spfile sid='*'; 
alter system set log_file_name_convert='+DATADG','+DATA' scope=spfile sid='*';
alter system set standby_file_management='AUTO' sid='*';    
alter system set fal_server='TNS_lhrrac1DG' sid='*';    
alter system set fal_client='TNS_lhrrac1' sid='*';   

select  (select instance_name from gv$instance na where na.inst_id=a.INST_ID) instance_name, GROUP#, DBID db_id, THREAD#, SEQUENCE#, BYTES,  USED, ARCHIVED, STATUS, FIRST_CHANGE#,  NEXT_CHANGE#,LAST_CHANGE# from gv$standby_log a order by INST_ID,THREAD#,GROUP#;

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 ;

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

alter database add standby logfile thread 1 group 5 ('+DATA','+DATA') size 50M ,group 6 ('+DATA','+DATA') size 50M ,group 7 ('+DATA','+DATA') size 50M ;
alter database add standby logfile thread 2 group 8 ('+DATA','+DATA')  size 50M ,group 9 ('+DATA','+DATA') size 50M ,group 10 ('+DATA','+DATA') size 50M ;

startup mount
select name , open_mode, log_mode,force_logging from gv$database;
alter database archivelog;

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup database format '/rman_backup/FULL_%U.bak';
sql 'alter system archive log current';
backup archivelog all format '/rman_backup/ARC_%U.bak';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
 } 
backup device type disk format '/rman_backup/standby_%U.ctl' current controlfile for  standby;

create pfile ='/rman_backup/initphydb.ora' from spfile;

---rac 2个节点的sys用户的口令一样
alter user sys identified by lhr;

----rac1
scp /rman_backup/*.bak oracle@192.168.59.140:/rman_backup/
scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwracdb1 oracle@192.168.59.140:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwphydb

*/

----------dg 
[oracle@dg rman_backup]$ cp initphydb.ora $ORACLE_HOME/dbs/
[oracle@dg rman_backup]$ cd $ORACLE_HOME/dbs

物理备库初始化参数文件修改： 

phydb.__db_cache_size=255852544
phydb.__java_pool_size=4194304
phydb.__large_pool_size=4194304
phydb.__oracle_base='/u01/app/oracle'#ORACLE_BASE environment from set 
phydb.__pga_aggregate_target=419430400
phydb.__sga_target=423624704
phydb.__shared_io_pool_size=0
phydb.__shared_pool_size=146800640
phydb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/phydb/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='+DATA/phydb/controlfile/cont.ctl'
*.core_dump_dest='/u01/app/oracle/diag/rdbms/phydb/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA/racdb/','+DATA/phydb/'
*.db_name='racdb'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='phydb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=phydbXDB)' 
*.fal_client='phydb'
*.fal_server='racdb1','racdb2'
*.log_archive_config='dg_config=(racdb,phydb)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=phydb'
*.log_archive_dest_2='service=racdb1 valid_for=(online_logfiles,primary_role) db_unique_name=racdb'
*.log_archive_format='ARC_%t_%S_%r.arc'
*.log_file_name_convert='+DATA/racdb/','+DATA/phydb/'
*.memory_target=842006528
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.service_names='phydb'
*.standby_file_management='auto'
*.thread=1
*.undo_management='auto'
*.undo_tablespace='UNDOTBS1'

[oracle@dg dbs]$ mkdir -p /u01/app/oracle/admin/phydb/adump
[oracle@dg dbs]$ mkdir -p /u01/app/oracle/diag/rdbms/phydb/cdump
[oracle@dg dbs]$ 

[grid@dg ~]$ asmcmd
ASMCMD> ls
DATA/
FRA/
GRIDDG/
ASMCMD> cd DATA
ASMCMD> ls
ASMCMD> mkdir phydb
ASMCMD> ls
phydb/
ASMCMD> 

racdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.139)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED) 
      (SERVICE_NAME = racdb.lhr.com)
    )
  )

racdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.135)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED) 
      (SERVICE_NAME = racdb.lhr.com)
      (INSTANCE_NAME = racdb1)
    )
  )

racdb2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.136)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED) 
      (SERVICE_NAME = racdb.lhr.com)
      (INSTANCE_NAME = racdb2)
    )
  )

phydb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.140)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED) 
      (SERVICE_NAME = phydb) 
    )
  )

sqlplus sys/lhr@racdb as sysdba 
conn sys/lhr@racdb1 as sysdba 
conn sys/lhr@racdb2 as sysdba
exit

startup nomount

[oracle@dg dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 3 19:06:52 2014

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

connected to target database: RACDB (not mounted)

RMAN> restore standby controlfile from '/rman_backup/standby_0tpk53jq_1_1.ctl';

Starting restore at 2014-10-03 19:06:55
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
output file name=+DATA/phydb/controlfile/cont.ctl
Finished restore at 2014-10-03 19:07:22

RMAN> 

SQL> alter database mount;

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
restore database ;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
 } 

set line 9999
set pagesize 9999
col name format a100

select 'datafile' ,  name from v$datafile
union 
select 'controlfile',  name from v$controlfile
union
select  'logfile', member from v$logfile;

SQL> select name from v$datafile
  2  union 
  3  select name from v$controlfile
  4  union
  5  select member from v$logfile;

NAME
--------------------------------------------------------------------------------
+DATA/phydb/controlfile/cont.ctl
+DATA/phydb/datafile/example.258.860008433
+DATA/phydb/datafile/sysaux.260.860008695
+DATA/phydb/datafile/system.257.860008433
+DATA/phydb/datafile/undotbs1.261.860008697
+DATA/phydb/datafile/undotbs2.262.860008697
+DATA/phydb/datafile/users.259.860008435
+DATA/phydb/onlinelog/group_1.261.859849493
+DATA/phydb/onlinelog/group_2.262.859849495
+DATA/phydb/onlinelog/group_3.266.859850179
+DATA/phydb/onlinelog/group_4.267.859850183
+FRA/racdb/onlinelog/group_1.257.859849493
+FRA/racdb/onlinelog/group_2.258.859849495
+FRA/racdb/onlinelog/group_3.259.859850181
+FRA/racdb/onlinelog/group_4.260.859850187

15 rows selected.

SQL> 

select * from v$standby_log;

alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;
alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M ;

select * from v$standby_log;

ORA-15041: diskgroup "DATA" space exhausted
alter diskgroup DATA add disk '/dev/raw/raw5';

--rac1
alter system set log_archive_dest_2='service=phydb lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=phydb' sid='*';
alter system set log_archive_config='dg_config=(racdb,phydb)' sid='*';  

select sequence#,name,applied from v$archived_log;

Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE, 
and that the SYS password is same in the password files.
      returning error ORA-16191
------------------------------------------------------------
PING[ARC1]: Heartbeat failed to connect to standby 'phydb'. Error is 16191.
Sun Oct 05 16:25:17 2014
Error 1017 received logging on to the standby

---- 3 个节点
orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwracdb2 password=lhr force=y

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

select sequence#,name,applied from v$archived_log;
col name format a100
select sequence#,name,archived,applied from v$archived_log order by name,sequence#;

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

select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

-- 检查是否存在GAP
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;

SELECT 'alter database register or replace logical logfile ''/u04/arch/WDSTD/log_1_' || a ||
       ' _666200636.arc'';' logical_dg,
       'alter database register or replace  logfile ''/u04/arch/WDSTD/log_1_' || a ||
       ' _666200636.arc'';' physical_dg
  FROM (SELECT level a FROM dual connect by level <= 2000)
 where a >= 1980 ;

----------- 11g rman 
duplicate target database 
for standby 
from active database 
DORECOVER 
spfile 
set db_unique_name='ora11gdg'
set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST   valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=ora11gdg'
--set log_archive_dest_2='service=ora11g async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=ora11g'
set standby_file_management='AUTO'
set fal_server='ora11g'
set fal_client='ora11gdg'
set control_files='/u01/app/oracle/oradata/ora11gdg/crontal01.ctl','/u01/app/oracle/oradata/ora11gdg/control02.ctl'
set db_file_name_convert='ora11g','ora11gdg'
set log_file_name_convert='ora11g','ora11gdg'
set memory_target='400M'
set audit_file_dest='/u01/app/oracle/admin/ora11gdg/adump'
;

---------------- 完成

----  测试
SQL> set  line 200
SQL> select tablespace_name, file_name from dba_data_files;

------ 测试DATAGUARDSWITCHOVER功能
SQL> show parameter spfile

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
spfile                   string  +DATA/racdb/spfileracdb.ora
SQL> 

SQL> show parameter fal

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
fal_client               string
fal_server               string

alter system set fal_client='racdb1' sid='racdb1'; 
alter system set fal_client='racdb2' sid='racdb2'; 
alter system set fal_server='phydb' sid='*';

select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;
alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M ;

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

alter database commit to switchover to primary;

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

------ dgmgrl

CREATE CONFIGURATION 'DR1' AS primary database is 'racdb' connect identifier is racdb;

SHOW CONFIGURATION;

add database 'phydb' as connect identifier is phydb maintained as physical;

show database verbose 'racdb';
show database 'racdb';

enable database  'racdb';
enable database  'phydb';

standby库：
SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     32
Next log sequence to archive   0
Current log sequence       33
SQL> 

-------------------  物理转换为逻辑
alter database recover managed standby database cancel;

----------------------------------------------------------------------------------------------------- windows下搭建------------------------------------------------------------------

select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from v$database;
alter database force logging;
alter database archivelog;

select * from v$standby_log;
select group#,bytes/1024/1024 ||'M' from v$log ;
SELECT * FROM V$LOGFILE;

alter database add standby logfile group 5 ( 'Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo05A.log','Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo05B.log') size 100m;
alter database add standby logfile group 6 ( 'Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo06A.log','Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo06B.log') size 100m;
alter database add standby logfile group 7 ( 'Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo07A.log','Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo07B.log') size 100m;
alter database add standby logfile group 8 ( 'Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo08A.log','Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo08B.log') size 100m;
alter database add standby logfile group 9 ( 'Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo09A.log','Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo09B.log') size 100m;

 select * from v$standby_log;

alter system set db_unique_name='orcl' scope=spfile;  
alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)';  
alter system set log_archive_dest_1='LOCATION=Z:\archive db_unique_name=orcl valid_for=(ALL_LOGFILES,ALL_ROLES)';  
alter system set log_archive_dest_2='SERVICE=orcldg LGWR ASYNC db_unique_name=orcldg 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='orcldg','orcl' scope=spfile;  
alter system set log_file_name_convert='orcldg','orcl' scope=spfile;  
alter system set standby_file_management='AUTO';  
alter system set fal_server='orcldg';  
alter system set fal_client='orcl'; 

重启主库

---------备库操作
oradim -new -sid orcldg
主备库配置监听和tnsnames.ora  ---配置静态监听

-由于要求主库与备库sys使用相同的密码，在此处，我们直接复制了主库的密码文件到备库
echo db_name=orcl >$ORACLE_HOME/database/initorcldg.ora     而且db_name必须相同

创建相关路径
mkdir D:\app\Administrator\admin\orcldg\adump
mkdir Z:\archive
mkdir Z:\app\Administrator\oradata\orcldg\

set ORACLE_SID=orcldg
services.msc 中 启动服务
sqlplus sys/wxqhaey as sysdba
startup nomount

rman target sys/wxqhaey@orcl auxiliary sys/wxqhaey@orcldg

duplicate target database 
for standby  nofilenamecheck
from active database 
DORECOVER 
spfile 
set db_unique_name='orcldg'
set log_archive_dest_1='LOCATION=Z:\archive\orcldg  valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=orcldg'
set log_archive_dest_2='service=orcl async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=orcl'
set standby_file_management='AUTO'
set fal_server='orcl'
set fal_client='orcldg'
set control_files='Z:\app\Administrator\oradata\orcldg\control01.ctl','Z:\app\Administrator\oradata\orcldg\control02.ctl'
set db_file_name_convert='orcl','orcldg'
set log_file_name_convert='orcl','orcldg'
set memory_target='161061273600'
set audit_file_dest='D:\app\Administrator\admin\orcldg\adump'
;

-------校验
select open_mode from v$database;
show parameter instance_name
alter database open;

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 using current logfile disconnect;

--主库切换日志
ALTER SYSTEM SWITCH LOGFILE; 

orcl.__db_cache_size=104152956928
orcl.__java_pool_size=1879048192
orcl.__large_pool_size=1342177280
orcl.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=22548578304
orcl.__sga_target=138512695296
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=29796335616
orcl.__streams_pool_size=536870912
*.audit_file_dest='D:\app\Administrator\admin\orcl\adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_files='Z:\app\Administrator\oradata\orcl\control01.ctl','Z:\app\Administrator\oradata\orcl\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='D:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=1000
*.log_archive_dest_1='LOCATION=Z:\archive'
*.log_archive_format='ARC%S_%R.%T'
*.memory_target=161061273600
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=21474836480
*.processes=2500
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sessions=2755
*.sga_target=128849018880
*.undo_tablespace='UNDOTBS1'

RUN { 
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter system archive log current';
backup current controlfile for standby format='Z:\backup\20150724\control_%U';
BACKUP FORMAT 'Z:\backup\20150724\oradg_%U_%T.bak' skip inaccessible filesperset 5 DATABASE ; 
sql 'alter system archive log current';
BACKUP FORMAT 'Z:\backup\20150724\arch_%U_%T.bak' skip inaccessible filesperset 5 ARCHIVELOG ALL;
sql "alter database create standby controlfile as ''Z:\backup\20150724\control01.ctl'' reuse";
release channel c2;
release channel c1;
}

sys/wxqhaey
sqlplus sys/wxqhaey as sysdba

orapwd file="D:\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcl.ORA" password=wxqhaey force=y

oradg11g--oradgphy---oradgss--oradgss

oradg11g.__db_cache_size=125829120
oradg11g.__java_pool_size=4194304
oradg11g.__large_pool_size=4194304
oradg11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
oradg11g.__pga_aggregate_target=83886080
oradg11g.__sga_target=327155712
oradg11g.__shared_io_pool_size=0
oradg11g.__shared_pool_size=184549376
oradg11g.__streams_pool_size=0
audit_file_dest='/u01/app/oracle/admin/oradg11g/adump'
audit_trail='db'
compatible='11.2.0.0.0'
control_files='/u01/app/oracle/oradata//oradg11g/control01.ctl','/u01/app/oracle/flash_recovery_area/oradg11g/control02.ctl'
db_block_size=8192
db_domain=''

rman target sys/lhr@tns_oradgphy auxiliary sys/lhr@tns_oradg11g

duplicate target database 
for standby  nofilenamecheck
from active database 
DORECOVER 
spfile 
set db_unique_name='oradg11g'
set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=oradg11g valid_for=(ALL_LOGFILES,ALL_ROLES)'
set log_archive_dest_2='SERVICE=tns_oradgphy LGWR ASYNC db_unique_name=oradgphy valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'
set log_archive_dest_3='SERVICE=tns_oradgss LGWR ASYNC db_unique_name=oradgss valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'
set log_archive_dest_4='SERVICE=tns_oradgss LGWR ASYNC db_unique_name=oradgss valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'
set log_archive_dest_state_1='ENABLE'
set log_archive_dest_state_2='ENABLE'
set log_archive_dest_state_3='defer'
set log_archive_dest_state_4='defer'
set standby_file_management='AUTO'
set fal_client='oradg11g'
set fal_server='oradgphy'
set control_files='/u01/app/oracle/oradata/oradg11g/control01.ctl','/u01/app/oracle/flash_recovery_area/oradg11g/control02.ctl'
set db_file_name_convert='oradgphy','oradg11g'
set log_file_name_convert='oradgphy','oradg11g'
set memory_target='200M'
set audit_file_dest='/u01/app/oracle/admin/oradg11g/adump'
;

rman target sys/lhr@tns_oradg11g auxiliary sys/lhr@tns_oradglg
duplicate target database 
for standby nofilenamecheck
from active database 
DORECOVER 
spfile 
set db_unique_name='oradglg'
set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST   valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=oradglg'
set standby_file_management='AUTO'
set fal_server='oradg11g'
set fal_client='oradglg'
set control_files='/u01/app/oracle/oradata/oradglg/control01.ctl','/u01/app/oracle/oradata/oradglg/control02.ctl'
set db_file_name_convert='oradg11g','oradglg'
set log_file_name_convert='oradg11g','oradglg'
set memory_target='190M'
;

rman target sys/lhr@tns_oradg11g auxiliary sys/lhr@tns_oradgss
duplicate target database 
for standby nofilenamecheck
from active database 
DORECOVER 
spfile 
set db_unique_name='oradgss'
set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST   valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=oradgss'
set standby_file_management='AUTO'
set fal_server='oradg11g'
set fal_client='oradgss'
set control_files='/u01/app/oracle/oradata/oradgss/control01.ctl','/u01/app/oracle/oradata/oradgss/control02.ctl'
set db_file_name_convert='oradg11g','oradgss'
set log_file_name_convert='oradg11g','oradgss'
set memory_target='190M'
;

duplicate target database 
for standby  nofilenamecheck
from active database 
DORECOVER 
spfile 
set db_unique_name='orcldg'
set log_archive_dest_1='LOCATION=Z:\archive\orcldg  valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=orcldg'
set log_archive_dest_2='service=orcl async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=orcl'
set standby_file_management='AUTO'
set fal_server='orcl'
set fal_client='orcldg'
set control_files='Z:\app\Administrator\oradata\orcldg\control01.ctl','Z:\app\Administrator\oradata\orcldg\control02.ctl'
set db_file_name_convert='orcl','orcldg'
set log_file_name_convert='orcl','orcldg'
set memory_target='161061273600'
set audit_file_dest='D:\app\Administrator\admin\orcldg\adump'
;

alter system set db_unique_name='orcl' scope=spfile;  
alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)';  
alter system set log_archive_dest_1='LOCATION=Z:\archive db_unique_name=orcl valid_for=(ALL_LOGFILES,ALL_ROLES)';  
alter system set log_archive_dest_2='SERVICE=orcldg LGWR ASYNC db_unique_name=orcldg 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='orcldg','orcl' scope=spfile;  
alter system set log_file_name_convert='orcldg','orcl' scope=spfile;  
alter system set standby_file_management='AUTO';  
alter system set fal_server='orcldg';  
alter system set fal_client='orcl'; 

*.log_archive_config='DG_CONFIG=(oradg11g,oradgphy,oradgss,oradgss)'

------------------------------------------------------ 角色切换-------------------------------------------------
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;

---------------- 物理备库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切换

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
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的failover

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

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

------------------------------------------------------------ 归档丢失的恢复
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# >= 40
and    resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
ORDER  BY a.THREAD#,
          a.sequence#;

 SELECT (SELECT MIN(d.CHECKPOINT_CHANGE#) FROM v$datafile d) datafile_scn,
       (SELECT MIN(d.CHECKPOINT_CHANGE#)
        FROM   v$datafile_header d
        WHERE  rownum = 1) datafile_header_scn,
       (SELECT current_scn FROM v$database) current_scn,
       (SELECT min(b.NEXT_CHANGE#)
        FROM   v$archived_log b
        WHERE  b.SEQUENCE# in (71,45)
        AND    resetlogs_change# =
               (SELECT d.RESETLOGS_CHANGE# FROM v$database d)) NEXT_CHANGE#
FROM   dual;

run 
{ 
allocate channel d1 type disk; 
allocate channel d2 type disk; 
allocate channel d3 type disk; 
allocate channel d4 type disk; 
backup as compressed backupset incremental from SCN 12232942713886 database format '/archive/standby_new_%d_%T_%U.bak' include current controlfile for standby filesperset=5  tag 'FOR STANDBY new'; 
release channel d1; 
release channel d2; 
release channel d3;
release channel d4;
}

---记录备库数据文件地址
col name format a60
set line 9999 pagesize 9999
SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;

cp +DATA/lhrdgwl/controlfile/control01.ctl +DATA/lhrdgwl/controlfile/control01.ctl.ctl_bk
restore standby  controlfile  to '+DATA/oranetr/controlfile/control01.ctl' from '/archive/standby_ORANET_20160131_0rqsnulk_1_1.bak';
restore standby  controlfile  to '+DATA/oranetr/controlfile/control02.ctl' from '/archive/standby_ORANET_20160131_0rqsnulk_1_1.bak';
restore standby  controlfile  to '+DATA/oranetr/controlfile/control03.ctl' from '/archive/standby_ORANET_20160131_0rqsnulk_1_1.bak';

alter database mount; 
catalog start with '/archive/';

run 
{ 
allocate channel d1 type disk; 
allocate channel d2 type disk; 
allocate channel d3 type disk; 
allocate channel d4 type disk; 
recover DATABASE noredo;
release channel d1; 
release channel d2; 
release channel d3;
release channel d4;
}

---若主备库的数据文件名不同，则恢复控制文件后需要对数据文件重命名 alter database rename file 'AAA' to 'BBB';
---特别注意：A为原主库数据文件地址，BBB为原物理DG数据文件地址
col name format a60
set line 9999 pagesize 9999
SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;
alter system set standby_file_management=manual;
alter database rename file '+DATA/lhrdg/datafile/undotbs1.307.913547429' to '+DATA/lhrdgwl/datafile/undotbs1.276.913557921';
alter database rename file '+DATA/lhrdg/datafile/users.308.913547429' to '+DATA/lhrdgwl/datafile/users.274.913557925';
alter database rename file '+DATA/lhrdg/datafile/example.288.913547503' to '+DATA/lhrdgwl/datafile/example.277.913557913';
alter database rename file '+DATA/lhrdg/datafile/undotbs2.287.913547649' to '+DATA/lhrdgwl/datafile/undotbs2.275.913557923'; 
alter system set standby_file_management=auto;

--------重命名数据文件完成后再次执行恢复操作即可，恢复的过程中我们可以通过如下的SQL语句来查看恢复的进度：
SELECT a.USERNAME,
       (SELECT upper(nb.OSUSER) FROM v$session nb WHERE nb.SID = a.sid) OSUSER,
       (SELECT nb.sid || ',' || nb.SERIAL# || ',' || pr.SPID
          FROM v$process pr, v$session nb
         WHERE nb.PADDR = pr.ADDR
           and nb.sid = a.SID
           and nb.SERIAL# = a.SERIAL#) session_info,
       a.target,
       a.opname,
       to_char(a.START_TIME, 'YYYY-MM-DD HH24:MI:SS') start_time,
       round(a.SOFAR * 100 / a.TOTALWORK, 2) || '%' AS progress,
       (a.TIME_REMAINING) TIME_REMAINING,
       (a.sofar || ':' || a.TOTALWORK) sofar_TOTALWORK,
       (a.elapsed_seconds) elapsed_seconds,
       message message,
       (SELECT nb.EVENT FROM V$session_Wait nb WHERE nb.SID = a.SID) wait_event,
       (SELECT nb.STATUS FROM v$session nb WHERE nb.SID = a.SID) STATUS
  FROM v$session_longops a
 WHERE a.time_remaining <> 0
 ORDER BY status, a.TIME_REMAINING DESC, a.SQL_ID, a.sid;

alter database add standby logfile thread 1 group 7 size 1073741824 ,group 8 size 1073741824 ,group 9 size 1073741824,group 10 size 1073741824 ;

alter database add standby logfile thread 2 group 11 size 1073741824 ,group 12 size 1073741824 ,group 13 size 1073741824,group 14 size 1073741824 ;

-------------------------------------------------------------  DG  RAC + RAC

dbca -silent -createDatabase -templateName General_Purpose.dbc \
-gdbname LHRDG  -sid LHRDGZK \
-sysPassword lhr -systemPassword lhr \
-datafileDestination 'DATA/' -redoLogFileSize 50 -recoveryAreaDestination '/arch' \
-storageType ASM -asmsnmpPassword lhr  -diskGroupName 'DATA' -responseFile NO_VALUE \
-characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -automaticMemoryManagement true \
-totalMemory 1024  -nodeinfo ZFXDESKDB3,ZFXDESKDB4

set line 9999
select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from gv$database;

alter database force logging;

alter system set log_archive_dest_1='LOCATION=/arch'  sid='*';

host srvctl stop database -d LHRDG -o immediate
host srvctl status database -d LHRDG 
host srvctl start database -d LHRDG -o mount

alter database archivelog;

select * from v$standby_log;
select group#,THREAD#,MEMBERS  from v$log ;
alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;
alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M ;

set line 9999
select   GROUP# ,STATUS , TYPE  ,  MEMBER from v$logfile;

alter system set db_unique_name='LHRDGZK' scope=spfile;  
alter system set log_archive_config='DG_CONFIG=(LHRDGZK,LHRDGWL)' sid='*';  
alter system set log_archive_dest_1='LOCATION=/arch db_unique_name=LHRDGZK valid_for=(ALL_LOGFILES,ALL_ROLES)' sid='*';  
alter system set log_archive_dest_2='SERVICE=TNS_LHRDGWL LGWR ASYNC db_unique_name=LHRDGWL valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)' sid='*';    
alter system set log_archive_dest_state_1=ENABLE sid='*';   
alter system set log_archive_dest_state_2=ENABLE sid='*';    
alter system set log_archive_max_processes=4 sid='*';  
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;  

--Add below item when DB turn to standby role  
alter system set db_file_name_convert='LHRDGWL','LHRDGZK' scope=spfile;  
alter system set log_file_name_convert='LHRDGWL','LHRDGZK' scope=spfile;  
alter system set standby_file_management='AUTO' sid='*';    
alter system set fal_server='TNS_LHRDGWL' sid='*';    
alter system set fal_client='TNS_LHRDGZK' sid='*';   

vi /oracle/app/11.2.0/grid/network/admin/listener.ora

LISTENER_LHRDG =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.66)(PORT = 1523))
    )
  )
SID_LIST_LISTENER_LHRDG =
(SID_LIST =
   (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)
      (PROGRAM = extproc)
   )
    (SID_DESC =
     (GLOBAL_DBNAME = LHRDG)   
     (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)
     (SID_NAME = LHRDGWL2)  
    )
)
more $ORACLE_HOME/network/admin/listener.ora

lsnrctl start LISTENER_LHRDG
lsnrctl status LISTENER_LHRDG

[ZFXDESKDB3:grid]:/home/grid>srvctl add listener -l LISTENER_LHRDG -p 1523 -o $ORACLE_HOME
[ZFXDESKDB3:grid]:/home/grid>srvctl stop listener -l LISTENER_LHRDG
[ZFXDESKDB3:grid]:/home/grid>srvctl start listener -l LISTENER_LHRDG

l $ORACLE_HOME/dbs/orapwLHRDG*

scp /oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGZK1 oracle@22.188.194.32:/oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGZK2
scp /oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGZK1 oracle@22.188.194.64:/oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGWL1
scp /oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGZK1 oracle@22.188.194.66:/oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGWL1

vi $ORACLE_HOME/network/admin/tnsnames.ora
TNS_LHRDGZK =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.31)(PORT = 1523))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.32)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LHRDG)
    )
  )

TNS_LHRDGWL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.64)(PORT = 1523))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.66)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LHRDG)
    )
  )

mkdir -p  $ORACLE_BASE/admin/LHRDGWL/adump

export ORACLE_SID=LHRDGWL1
echo 'db_name=LHRDG' > $ORACLE_HOME/dbs/initLHRDGWL1.ora
sqlplus / as sysdba
startup nomount pfile=?/dbs/initLHRDGWL1.ora

--rman target sys/lhr@TNS_LHRDGPRI auxiliary sys/lhr@TNS_LHRDGPHY log=/tmp/dupl_log.log DEBUG trace=/tmp/dupl_debug.trc

rman target sys/lhr@TNS_LHRDGZK auxiliary sys/lhr@TNS_LHRDGWL 

duplicate target database 
for standby nofilenamecheck
from active database 
DORECOVER 
spfile 
set db_unique_name='LHRDGWL'
set log_archive_dest_1='LOCATION=/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=LHRDGWL'
set log_archive_dest_2='service=TNS_LHRDGZK async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=LHRDGZK'
set standby_file_management='AUTO'
set fal_server='TNS_LHRDGZK'
set fal_client='TNS_LHRDGWL'
set control_files='+DATA/LHRDGWL/controlfile/control01.ctl','+DATA/LHRDGWL/controlfile/control02.ctl'
set db_file_name_convert='LHRDGZK','LHRDGWL'
set log_file_name_convert='LHRDGZK','LHRDGWL'
set memory_target='2G'
set audit_file_dest='/oracle/app/oracle/admin/LHRDGWL/adump'
set db_create_file_dest = '+DATA'
set instance_number = '1'
set cluster_database='false'
;

create pfile='/tmp/aa.txt' from spfile;

修改 /tmp/aa.txt

create spfile='+DATA/LHRDGWL/PARAMETERFILE/spfileLHRDGWL.ora'  from pfile='/tmp/aa.txt';

echo  "SPFILE='+DATA/LHRDGWL/PARAMETERFILE/spfileLHRDGWL.ora'" >  $ORACLE_HOME/dbs/initLHRDGWL1.ora
more $ORACLE_HOME/dbs/initDGPHY1.ora

删除原spfile文件：/oracle/app/oracle/product/11.2.0/db/dbs/spfileLHRDGWL1.ora

*.cluster_database=true
DGPHY2.instance_number=2
DGPHY1.instance_number=1
DGPHY2.thread=2
DGPHY1.thread=1
DGPHY2.undo_tablespace='UNDOTBS2'
DGPHY1.undo_tablespace='UNDOTBS1'

----将备库加入srvctl中
srvctl stop database -d LHRDG -o immediate
srvctl status database -d LHRDG 
srvctl start database -d LHRDG -o mount

srvctl add db -d lhrrac1DG -c SINGLE -o /u01/app/oracle/product/11.2.0/dbhome_1 -p '+datadg/LHRRAC1DG/PARAMETERFILE/spfile.322.1014551675' -r physical_standby -n lhrrac1 -x raclhr-11gR2-N1

srvctl add database -d LHRDG -c RAC -o /oracle/app/oracle/product/11.2.0/db -p '+DATA/LHRDGWL/PARAMETERFILE/spfileLHRDGWL.ora' -r physical_standby -n LHRDG

srvctl add instance -d LHRDG -i LHRDGWL1 -n ZFXDESKDB1
srvctl add instance -d LHRDG -i LHRDGWL2 -n ZFXDESKDB2

srvctl stop database -d LHRDG
srvctl status database -d LHRDG
srvctl start database -d LHRDG

srvctl remove  database -d  LHRDG

srvctl config database -d LHRDG -a

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

select sequence#,name,applied from v$archived_log;
col name format a100
select sequence#,name,archived,applied from v$archived_log order by name,sequence#;

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

col name format a10
select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

检查是否存在GAP
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;

SELECT 'alter database register or replace logical logfile ''/u04/arch/WDSTD/log_1_' || a ||
       ' _666200636.arc'';' logical_dg,
       'alter database register or replace  logfile ''/u04/arch/WDSTD/log_1_' || a ||
       ' _666200636.arc'';' physical_dg
  FROM (SELECT level a FROM dual connect by level <= 2000)
 where a >= 1980 ;

-------------------------------------------------------------  DG  RAC + single db

dbca -silent -createDatabase -templateName General_Purpose.dbc \
-gdbname LHRDG  -sid LHRDGPRI \
-sysPassword lhr -systemPassword lhr \
-datafileDestination 'DATA/' -redoLogFileSize 50 -recoveryAreaDestination '/arch' \
-storageType ASM -asmsnmpPassword lhr  -diskGroupName 'DATA' -responseFile NO_VALUE \
-characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -automaticMemoryManagement true \
-totalMemory 1024  -nodeinfo ZFXDESKDB3,ZFXDESKDB4

set line 9999
select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from gv$database;

alter database force logging;

alter system set log_archive_dest_1='LOCATION=/arch'  sid='*';

host srvctl stop database -d LHRDGPRI -o immediate
host srvctl status database -d LHRDGPRI 
host srvctl start database -d LHRDGPRI -o mount

alter database archivelog;

select * from v$standby_log;
select group#,THREAD#,MEMBERS  from v$log ;
alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;
alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M ;

set line 9999
select   GROUP# ,STATUS , TYPE  ,  MEMBER from v$logfile;

alter system set db_unique_name='LHRDGPRI' scope=spfile;  
alter system set log_archive_config='DG_CONFIG=(LHRDGPRI,LHRDGPHY)' sid='*';  
alter system set log_archive_dest_1='LOCATION=/arch db_unique_name=LHRDGPRI valid_for=(ALL_LOGFILES,ALL_ROLES)' sid='*';  
alter system set log_archive_dest_2='SERVICE=TNS_LHRDGPHY LGWR ASYNC db_unique_name=LHRDGPHY valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)' sid='*';    
alter system set log_archive_dest_state_1=ENABLE sid='*';   
alter system set log_archive_dest_state_2=ENABLE sid='*';    
alter system set log_archive_max_processes=4 sid='*';  
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;  

--Add below item when DB turn to standby role  
alter system set db_file_name_convert='LHRDGPHY','LHRDGPRI' scope=spfile;  
alter system set log_file_name_convert='LHRDGPHY','LHRDGPRI' scope=spfile;  
alter system set standby_file_management='AUTO' sid='*';    
alter system set fal_server='TNS_LHRDGPHY' sid='*';    
alter system set fal_client='TNS_LHRDGPRI' sid='*';   

LISTENER_LHRDG =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.66)(PORT = 1523))
    )
  )
SID_LIST_LISTENER_LHRDG =
(SID_LIST =
   (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)
      (PROGRAM = extproc)
   )
    (SID_DESC =
     (GLOBAL_DBNAME = LHRDGPRI)
     (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)
     (SID_NAME = LHRDGPHY2)
    )
)
more $ORACLE_HOME/network/admin/listener.ora

lsnrctl start LISTENER_LHRDG
lsnrctl status LISTENER_LHRDG

l $ORACLE_HOME/dbs/orapwLHRDG*

scp /oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGPRI1 oracle@22.188.194.32:/oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGPRI2
scp /oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGPRI1 oracle@22.188.194.64:/oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGPHY1
scp /oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGPRI1 oracle@22.188.194.66:/oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGPHY2

vi $ORACLE_HOME/network/admin/tnsnames.ora
TNS_LHRDGPRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.31)(PORT = 1523))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.32)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LHRDGPRI)
    )
  )

TNS_LHRDGPHY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.64)(PORT = 1523))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.66)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LHRDGPRI)
    )
  )

mkdir -p  $ORACLE_BASE/admin/LHRDGPHY/adump

export ORACLE_SID=LHRDGPHY1
echo 'db_name=LHRDGPRI' > $ORACLE_HOME/dbs/initLHRDGPHY1.ora
sqlplus / as sysdba
startup nomount pfile=?/dbs/initLHRDGPHY1.ora

--rman target sys/lhr@TNS_LHRDGPRI auxiliary sys/lhr@TNS_LHRDGPHY log=/tmp/dupl_log.log DEBUG trace=/tmp/dupl_debug.trc

rman target sys/lhr@TNS_LHRDGPRI auxiliary sys/lhr@TNS_LHRDGPHY 

duplicate target database 
for standby nofilenamecheck
from active database 
DORECOVER 
spfile 
parameter_value_convert 'LHRDGPRI', 'LHRDGPHY'
set db_unique_name='LHRDGPHY'
set log_archive_dest_1='LOCATION=/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=LHRDGPHY'
set log_archive_dest_2='service=TNS_LHRDGPRI async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=LHRDGPRI'
set standby_file_management='AUTO'
set fal_server='TNS_LHRDGPRI'
set fal_client='TNS_LHRDGPHY'
set control_files='/oracle/app/oracle/oradata/LHRDGPHY/control01.ctl','/oracle/app/oracle/oradata/LHRDGPHY/control02.ctl'
set db_file_name_convert='+DATA','/oracle/app/oracle/oradata/LHRDGPHY'
set log_file_name_convert='+DATA','/oracle/app/oracle/oradata/LHRDGPHY'
set memory_target='1024M'
set audit_file_dest='/oracle/app/oracle/admin/LHRDGPHY/adump'
set db_create_file_dest = ''
set cluster_database='false'
;

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

select sequence#,name,applied from v$archived_log;
col name format a100
select sequence#,name,archived,applied from v$archived_log order by name,sequence#;

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

col name format a10
select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

检查是否存在GAP
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;

SELECT 'alter database register or replace logical logfile ''/u04/arch/WDSTD/log_1_' || a ||
       ' _666200636.arc'';' logical_dg,
       'alter database register or replace  logfile ''/u04/arch/WDSTD/log_1_' || a ||
       ' _666200636.arc'';' physical_dg
  FROM (SELECT level a FROM dual connect by level <= 2000)
 where a >= 1980 ;

----------------------------------------- 10g 物理DG搭建 单实例
dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
-gdbname mydg  -sid mydg \
-sysPassword lhr -systemPassword lhr \
-datafileDestination '/cds/oradata' -recoveryAreaDestination '/cds/oradata' \
-storageType FS \
-characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \
-sampleSchema true \
-memoryPercentage 20 \
-databaseType OLTP  \
-emConfiguration NONE

alter database force logging;

shutdown immediate;
startup mount
alter database archivelog;
alter database open;

select group#,bytes/1024/1024 ||'M' from v$log ;
select * from v$standby_log;
select * from v$logfile;

alter database add standby logfile 
group 4 ('/cds/oradata/mydg/standby_redo04.log') size 50m,
group 5 ('/cds/oradata/mydg/standby_redo05.log') size 50m,
group 6 ('/cds/oradata/mydg/standby_redo06.log') size 50m,
group 7 ('/cds/oradata/mydg/standby_redo07.log') size 50m;

vi $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.137.52)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/app/oracle/product/10.2.0/db)
      (PROGRAM = extproc)
    )
   (SID_DESC =
    (GLOBAL_DBNAME = mydg)
    (ORACLE_HOME = /oracle/app/oracle/product/10.2.0/db)
    (SID_NAME= mydg)
   )
  )

vi $ORACLE_HOME/network/admin/tnsnames.ora 

tns_mydg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.137.52)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydg)
    )
  )

tns_mydgwl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.137.62)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydgwl)
    )
  )

tnsping tns_mydg
tnsping tns_mydgwl

RUN { 
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter system archive log current';
backup current controlfile for standby format='/cds/oradata/control_%U';
BACKUP FORMAT '/cds/oradata/oradg_%U_%T.bak' skip inaccessible filesperset 5 DATABASE ; 
sql 'alter system archive log current';
BACKUP FORMAT '/cds/oradata/arch_%U_%T.bak' skip inaccessible filesperset 5 ARCHIVELOG ALL;
sql "alter database create standby controlfile as ''/cds/oradata/control01.ctl'' reuse";
release channel c2;
release channel c1;
}

alter system set db_unique_name='mydg' scope=spfile;  
alter system set log_archive_config='DG_CONFIG=(mydg,mydgwl)';  
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=mydg valid_for=(ALL_LOGFILES,ALL_ROLES)';  
alter system set log_archive_dest_2='SERVICE=tns_mydgwl LGWR ASYNC db_unique_name=mydgwl 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 standby_file_management='AUTO'; 

alter system set db_file_name_convert='mydg','mydgwl' scope=spfile;  
alter system set log_file_name_convert='mydg','mydgwl' scope=spfile;
alter system set fal_server='mydgwl';  
alter system set fal_client='mydg'; 

scp $ORACLE_HOME/dbs/orapworadg10g $ORACLE_HOME/dbs/orapworaljdg

create pfile='/cds/oradata/initoramydg.ora' from spfile;

mkdir -p /oracle/app/oracle/admin/mydg/udump
mkdir -p /gldb/oradata
mkdir -p /oracle/app/oracle/admin/mydg/adump
mkdir -p /oracle/app/oracle/admin/mydg/bdump 
mkdir -p /gldb/oradata/mydg
mkdir -p /oracle/app/oracle/admin/mydg/cdump

mkdir -p /cds/oradata/mydgwl/

*.audit_file_dest='/oracle/app/oracle/admin/mydg/adump'
*.background_dump_dest='/oracle/app/oracle/admin/mydg/bdump'
*.compatible='10.2.0.5.0'
*.control_files='/gldb/oradata/mydg/control01.ctl','/gldb/oradata/mydg/control02.ctl','/gldb/oradata/mydg/control03.ctl'
*.core_dump_dest='/oracle/app/oracle/admin/mydg/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='mydg','mydgwl'
*.db_name='mydg'
*.db_recovery_file_dest='/gldb/oradata'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='mydgwl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mydgXDB)'
*.fal_client='mydgwl'
*.fal_server='mydg'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(mydg,mydgwl)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=mydg valid_for=(ALL_LOGFILES,ALL_ROLES)' 
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=4
*.log_file_name_convert='mydg','mydgwl'
*.open_cursors=300
*.pga_aggregate_target=334495744
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1340080128
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/app/oracle/admin/mydg/udump'

cp /gldb/mydgbk/control.ctl /gldb/oradata/mydg/control01.ctl
cp /gldb/mydgbk/control.ctl /gldb/oradata/mydg/control02.ctl
cp /gldb/mydgbk/control.ctl /gldb/oradata/mydg/control03.ctl

ORACLE_SID=mydgwl
create spfile from pfile='?/dbs/initmydgwl.ora';
startup nomount;
alter database mount standby database;

catalog start with '/gldb/mydgbk/';
restore database;

alter database add standby logfile 
group 4 ('/gldb/oradata/mydg/standby_redo04.log') size 50m,
group 5 ('/gldb/oradata/mydg/standby_redo05.log') size 50m,
group 6 ('/gldb/oradata/mydg/standby_redo06.log') size 50m,
group 7 ('/gldb/oradata/mydg/standby_redo07.log') size 50m;

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

alter system set db_unique_name='mydgwl' scope=spfile;  
alter system set log_archive_config='DG_CONFIG=(mydg,mydgwl)';  
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=mydg valid_for=(ALL_LOGFILES,ALL_ROLES)';  
alter system set log_archive_dest_2='SERVICE=tns_mydgwl LGWR ASYNC db_unique_name=mydgwl 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 standby_file_management='AUTO'; 

alter system set db_file_name_convert='mydg','mydgwl' scope=spfile;  
alter system set log_file_name_convert='mydg','mydgwl' scope=spfile;
alter system set fal_server='mydgwl';  
alter system set fal_client='mydg'; 

--------------------------------------------------------------------------- 12c
----------基于同一个主机搭建

SELECT CDB FROM  V$DATABASE; 
alter database force logging;
select thread#,group#,bytes/1024/1024 SIZE_MB, status from v$log;
alter database add standby logfile thread 1 group 4 '/u04/oradata/lhr122/redo04.log'   size 50M ;

lhr122 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lhr122)
    )
  )

lhr122dg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lhr122dg)
    )
  )

LISTENER_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522))
  )

SID_LIST_LISTENER_DG =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = lhr122)
      (ORACLE_HOME = /u07/app/oracle/product/12.2.0/dbhome_1)
      (SID_NAME = lhr122)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = lhr122dg)
      (ORACLE_HOME = /u07/app/oracle/product/12.2.0/dbhome_1)
      (SID_NAME = lhr122dg)
    )
  )

dbca -silent -createDuplicateDB \
-gdbName lhr122 -sid lhr122dg \
-sysPassword lhr \
-primaryDBConnectionString 192.168.59.130:1522/lhr122 \
-createAsStandby -dbUniqueName lhr122dg \
-initParams db_create_file_dest=/u04/oradata/lhr122dg/, db_create_online_log_dest_1=/u04/oradata/lhr122dg/,local_listener="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1522)))"

--备库
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u04/oradata/lhr122dg/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lhr122dg';
alter system set db_file_name_convert='/u04/oradata/lhr122/','/u04/oradata/lhr122dg/' scope=spfile;
alter system set log_file_name_convert='/u04/oradata/lhr122/','/u04/oradata/lhr122dg/' scope=spfile;
alter system set standby_file_management=auto scope=spfile;
alter system set fal_client='lhr122dg'; 
alter system set fal_server='lhr122' sid='*';
shutdown immediate
startup
alter system register;

--主库
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u04/oradata/lhr122/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lhr122';
alter system set LOG_ARCHIVE_DEST_2='service=lhr122dg  VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=lhr122dg';
alter system set log_archive_config='dg_config=(lhr122,lhr122dg)';
alter system set db_file_name_convert='/u04/oradata/lhr122dg/','/u04/oradata/lhr122/' scope=spfile;
alter system set log_file_name_convert='/u04/oradata/lhr122dg/','/u04/oradata/lhr122/' scope=spfile;
alter system set standby_file_management=auto scope=spfile;
alter system set fal_client='lhr122'; 
alter system set fal_server='lhr122dg' sid='*';

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE;

alter system switch logfile;

 alter database recover managed standby database cancel;

set line 9999 pagesize 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 MAX(sequence#) FROM v$log na WHERE na.thread# = al.thread#) Current_Seq#,
       MAX(sequence#) Last_Archived,
       max(CASE
             WHEN al.APPLIED = 'YES' AND ads.TYPE <> 'LOCAL' 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'
 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#;

-------------------------------------------------- 定时
mkdir -p /home/oracle/lhr/log

more /home/oracle/lhr/deladgarc_lhr.sh

 对于单实例的数据库可以使用如下的脚本：

1、在Oracle用户下，创建归档日志删除文件del_OCPLHR1_arch.sh
文件位置：/home/oracle/crontabOra，内容如下：
#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export ORACLE_SID=OCPLHR1
export NLS_LANG="american_america.ZHS16GBK"
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:Mi:SS"
LOG_DIR=/home/oracle/crontabOra/log
DATEL=`date '+%Y-%m-%d'`
LOG_NAME=${LOG_DIR}/OCPLHR1_${DATEL}".log"

$ORACLE_HOME/bin/rman log=$LOG_NAME target sys/lhr@OCPLHR1 <<EOF
crosscheck archivelog all;
delete force noprompt archivelog all completed before 'sysdate-6';
exit;
EOF
2、赋可执行权限
chmod +x del_OCPLHR1_arch.sh
3、设定定时任务，在Oracle用户下，编辑配置文件
crontab -e
配置文件内容（每天下午5点执行删除任务）：
0 17 * * * /home/oracle/crontabOra/del_OCPLHR1_arch.sh
确保crond服务处于启动状态：
ps -ef | grep crond #判断定时服务是否启动
service crond start|stop|restart #启动、停止或重启服务

对于DG环境，需要删除已经应用到备库的归档日志，可以使用如下的脚本，在主备库都需要部署：

mkdir -p /home/oracle/lhr/log
more /home/oracle/lhr/deladgarc_lhr.sh
#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/18.3.0/dbhome_1
export ORACLE_SID=htzxdb1
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:Mi:SS"
LOG_DIR=/home/oracle/lhr/log
DATEL=`date '+%Y-%m-%d'`
LOG_NAME=${LOG_DIR}/deladgarc_${ORACLE_SID}_${DATEL}".log"
SQL_NAME=${LOG_DIR}/deladgarc_${ORACLE_SID}_${DATEL}".sql"
LINK_NAME=tns_htzxdbphy
$ORACLE_HOME/bin/sqlplus -S sys/oracle@${LINK_NAME} as sysdba <<EOF
set feedback off heading off pagesize 0 linesize 100
col exec_sql format a50
spool ${SQL_NAME}
SELECT 'delete archivelog sequence ' || A.SEQUENCE# || ' thread ' || A.THREAD# || ';' EXEC_SQL
FROM   V\$ARCHIVED_LOG A
WHERE  (A.THREAD#, A.SEQUENCE#, a.RESETLOGS_CHANGE#) IN
       (SELECT b.THREAD#,
               b.SEQUENCE#,
               b.RESETLOGS_CHANGE#
        FROM   V\$ARCHIVED_LOG B
        WHERE  B.APPLIED = 'YES'
        AND    b.COMPLETION_TIME <= SYSDATE - 8)      
AND    a.NAME NOT IN (SELECT b.DESTINATION
                      FROM   v\$archive_dest b
                      WHERE  b.DESTINATION IS NOT NULL)
AND    A.COMPLETION_TIME <= SYSDATE - 8
ORDER  BY A.THREAD#,
          A.SEQUENCE#;

spool off
exit
EOF
$ORACLE_HOME/bin/rman log=$LOG_NAME target sys/oracle@${LINK_NAME} <<EOF
 crosscheck archivelog all;
 delete noprompt expired archivelog all;
 @${SQL_NAME}
 exit;
EOF
2 、赋可执行权限

chmod +x /home/oracle/lhr/deladgarc_lhr.sh

3 、设定定时任务，在 Oracle 用户下，编辑配置文件

crontab -e

配置文件内容（每天下午 5 点执行删除任务）：

0 17 * * * /home/oracle/lhr/deladgarc_lhr.sh

确保 crond 服务处于启动状态：

ps -ef | grep crond # 判断定时服务是否启动

service crond start|stop|restart # 启动、停止或重启服务

----------------------- 主备单实例配置非默认端口
--如果主备有一端是rac，那么经过测试应该使用默认的监听器（名称为LISTENER，端口为1521），而且动态和静态监听都需要配置。

LISTENER_DG =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1526))
    )
  )

SID_LIST_LISTENER_DG =
(SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = oradg11g)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
     (SID_NAME = oradg11g)
    )
    (SID_DESC =
     (GLOBAL_DBNAME = oradgphy)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
     (SID_NAME = oradgphy)
    )
    (SID_DESC =
     (GLOBAL_DBNAME = oradg11g_DGMGRL)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
     (SID_NAME = oradg11g)
    )
    (SID_DESC =
     (GLOBAL_DBNAME = oradgphy_DGMGRL)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
     (SID_NAME = oradgphy)
    )
 )

tns_oradg11g =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradg11g)
    )
  )
tns_oradgphy =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradgphy)
    )
  )

alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1521)))','(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1526)))';

edit database oradgphy set property  StaticConnectIdentifier    ='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=oradgphy)(INSTANCE_NAME=oradgphy)(SERVER=DEDICATED)))';

edit database oradg11g set property  StaticConnectIdentifier    ='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=oradg11g)(INSTANCE_NAME=oradg11g)(SERVER=DEDICATED)))';

lsnrctl stop LISTENER_DG
srvctl add listener -l LISTENER_DG -p 1526
srvctl start listener -l LISTENER_DG

Service "oradg11g" has 2 instance(s).
  Instance "oradg11g", status UNKNOWN, has 1 handler(s) for this service...
  Instance "oradg11g", status READY, has 1 handler(s) for this service...
Service "oradg11gXDB" has 2 instance(s).
  Instance "oradg11g", status READY, has 1 handler(s) for this service...
  Instance "oradgphy", status READY, has 1 handler(s) for this service...
Service "oradg11g_DGB" has 1 instance(s).
  Instance "oradg11g", status READY, has 1 handler(s) for this service...
Service "oradg11g_DGMGRL" has 1 instance(s).
  Instance "oradg11g", status UNKNOWN, has 1 handler(s) for this service...

Service "oradgphy" has 2 instance(s).
  Instance "oradgphy", status UNKNOWN, has 1 handler(s) for this service...
  Instance "oradgphy", status READY, has 1 handler(s) for this service...
Service "oradgphy_DGB" has 1 instance(s).
  Instance "oradgphy", status READY, has 1 handler(s) for this service...
Service "oradgphy_DGMGRL" has 1 instance(s).
  Instance "oradgphy", status UNKNOWN, has 1 handler(s) for this service...

--------------------------------- 19c 单实例  dg  dbca方式
SELECT CDB FROM  V$DATABASE; 
alter database force logging;
alter database flashback on;
alter database open;
alter database archivelog;
alter pluggable database all open;
alter pluggable database  all save state;

select thread#,group#,bytes/1024/1024 SIZE_MB, status,members from v$log;
select member from v$logfile;
alter database add standby logfile thread 1 group 4 '/u01/app/oracle/oradata/LHR19C/standby_redo04.log'   size 50M ;
alter database add standby logfile thread 1 group 5 '/u01/app/oracle/oradata/LHR19C/standby_redo05.log'   size 50M ;
alter database add standby logfile thread 1 group 6 '/u01/app/oracle/oradata/LHR19C/standby_redo06.log'   size 50M ;
alter database add standby logfile thread 1 group 7 '/u01/app/oracle/oradata/LHR19C/standby_redo07.log'   size 50M ;

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

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

--------配置监听
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.3.0/dbhome_1)
      (SID_NAME = lhr19c)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = lhr19cdg)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = lhr19cdg)
    )
  )

LISTENER_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.52)(PORT = 1522))
  )

SID_LIST_LISTENER_DG =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = lhr19c)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = lhr19c)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = lhr19cdg)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = lhr19cdg)
    )
  )

lsnrctl start LISTENER_DG

dbca -silent -createDuplicateDB \
-gdbName lhr19c \
-sid lhr19cdg \
-sysPassword lhr \
-primaryDBConnectionString 192.168.59.52:1522/lhr19c \
-nodelist raclhr-18c-n1 \
-databaseConfigType SINGLE \
-createAsStandby -dbUniqueName lhr19cdg \
-datafileDestination '/u01/app/oracle/oradata/lhr19cdg/' \
-initParams db_create_file_dest=/u01/app/oracle/oradata/lhr19cdg/, db_create_online_log_dest_1=/u01/app/oracle/oradata/lhr19cdg/,local_listener="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.52)(PORT=1522)))"

--主库修改参数
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lhr19c';
alter system set LOG_ARCHIVE_DEST_2='service=lhr19cdg  VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=lhr19cdg';
alter system set log_archive_config='dg_config=(lhr19c,lhr19cdg)';
alter system set db_file_name_convert='/u01/app/oracle/oradata/LHR19CDG/','/u01/app/oracle/oradata/LHR19C/' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/LHR19CDG/','/u01/app/oracle/oradata/LHR19C/' scope=spfile;
alter system set standby_file_management=auto scope=spfile;
alter system set fal_client='lhr19c'; 
alter system set fal_server='lhr19cdg' sid='*';
alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.52)(PORT=1522)))','(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.52)(PORT=1521)))';
shutdown immediate
startup

--备库修改参数
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lhr19cdg' scope=spfile;
alter system set log_archive_config='dg_config=(lhr19c,lhr19cdg)';
alter system set db_file_name_convert='/u01/app/oracle/oradata/LHR19C/','/u01/app/oracle/oradata/LHR19CDG/' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/LHR19C/','/u01/app/oracle/oradata/LHR19CDG/' scope=spfile;
alter system set standby_file_management=auto scope=spfile;
alter system set fal_client='lhr19cdg'; 
alter system set fal_server='lhr19c' sid='*';
alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.52)(PORT=1522)))','(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.52)(PORT=1521)))';

shutdown immediate
startup
alter system register;

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

select INST_ID, dbid,name,DB_UNIQUE_NAME,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

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

create configuration lhr19c as
primary database is lhr19c
connect identifier is tns_lhr19c;

add database lhr19cdg as
connect identifier is tns_lhr19cdg
maintained as physical;

enable configuration

show database verbose lhr19c;
show database verbose lhr19cdg;

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 lhr19c set property  StaticConnectIdentifier    ='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=lhr122)(INSTANCE_NAME=lhr122)(SERVER=DEDICATED)))';

--------------------------------- 19c  rac  dg  dbca方式

SELECT CDB FROM  V$DATABASE; 
alter database archivelog;
alter database force logging;
alter database flashback on;
alter database open;
alter pluggable database all open;
alter pluggable database all save state;

select thread#,group#,bytes/1024/1024 SIZE_MB, status,members from v$log;
select thread#,group#,member from v$logfile;

set line 9999 pagesize 9999 
col  db_id  format a15
col GROUP# format 99999
select  (select instance_name from gv$instance na where na.inst_id=a.INST_ID) instance_name, GROUP#, DBID db_id, THREAD#, SEQUENCE#, BYTES,  USED, ARCHIVED, STATUS, FIRST_CHANGE#,  NEXT_CHANGE#,LAST_CHANGE# from gv$standby_log a order by INST_ID,THREAD#,GROUP#;

alter database add standby logfile thread 1 group 5 ('+DATA','+FRA')  size 200M ,group 6 ('+DATA','+FRA')  size 200M ,group 7 ('+DATA','+FRA')  size 200M ;
alter database add standby logfile thread 2 group 8 ('+DATA','+FRA')  size 200M ,group 9 ('+DATA','+FRA')  size 200M ,group 10 ('+DATA','+FRA')  size 200M ;

--------配置tns
tns_rac19c =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.54)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.55)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac19c)
    )
  )

tns_rac19cdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.54)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.55)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac19cdg)
    )
  )

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

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = rac19c)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = rac19c1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = rac19cdg)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = rac19cdg1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = rac19c_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = rac19c1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = rac19cdg_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = rac19cdg1)
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = rac19c)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = rac19c2)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = rac19cdg)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = rac19cdg2)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = rac19c_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = rac19c2)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = rac19cdg_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = rac19cdg2)
    )
  )

srvctl stop listener -l LISTENER
srvctl start listener -l LISTENER

dbca -silent -createDuplicateDB \
-gdbName rac19c \
-sid rac19cdg \
-sysPassword lhr \
-primaryDBConnectionString 192.168.59.54:1521/rac19c \
-nodelist raclhr-19c-n1,raclhr-19c-n2 \
-adminManaged \
-databaseConfigType RAC \
-createAsStandby -dbUniqueName rac19cdg \
-datafileDestination '+DATA' \
-initParams db_create_file_dest=+DATA, db_create_online_log_dest_1=+DATA

---全参数 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 system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=rac19c' sid='*';
alter system set LOG_ARCHIVE_DEST_2='service=tns_rac19cdg  VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=rac19cdg' sid='*';
alter system set log_archive_config='dg_config=(rac19c,rac19cdg)' sid='*';
alter system set db_file_name_convert='+DATA','+DATA' scope=spfile sid='*';
alter system set log_file_name_convert='+DATA','+DATA' scope=spfile sid='*';
alter system set standby_file_management=auto scope=spfile sid='*';
alter system set fal_client='tns_rac19c' sid='*'; 
alter system set fal_server='tns_rac19cdg' sid='*';

---创建路径
ASMCMD> mkdir +DATA/dgshare/

--主库
show parameter dg
alter system set dg_broker_config_file1='+DATA/dgshare/dr1rac19c.dat'  SID='*';
alter system set dg_broker_config_file2='+DATA/dgshare/dr2rac19c.dat'  SID='*';
alter system set dg_broker_start=true sid='*';

--备库
show parameter dg
alter system set dg_broker_config_file1='+DATA/dgshare/dr1rac19cDG.dat'  SID='*';
alter system set dg_broker_config_file2='+DATA/dgshare/dr2rac19cDG.dat'  SID='*';
alter system set dg_broker_start=true sid='*';

--备库修改参数
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=rac19cdg' scope=spfile sid='*';
alter system set log_archive_config='dg_config=(rac19c,rac19cdg)';
alter system set db_file_name_convert='+DATA','+DATA' scope=spfile sid='*';
alter system set log_file_name_convert='+DATA','+DATA' scope=spfile sid='*';
alter system set standby_file_management=auto scope=spfile sid='*';
alter system set fal_client='tns_rac19cdg' sid='*';
alter system set fal_server='tns_rac19c' sid='*';

srvctl stop db -d rac19c
srvctl start db -d rac19c
srvctl stop db -d rac19cdg
srvctl start db -d rac19cdg

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

select INST_ID, dbid,name,DB_UNIQUE_NAME,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

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

cp /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora /u01/app/19.3.0/grid/network/admin/tnsnames.ora
chown grid.oinstall /u01/app/19.3.0/grid/network/admin/tnsnames.ora

dgmgrl sys/lhr@rac19c
show configuration

create configuration rac19c as
primary database is rac19c
connect identifier is tns_rac19c;

add database rac19cdg as
connect identifier is tns_rac19cdg
maintained as physical;

enable configuration

show database verbose rac19c;
show database verbose rac19cdg;

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 rac19c set property 'FastStartFailoverTarget'='rac19cdg';
edit database rac19cdg set property 'FastStartFailoverTarget'='rac19c';

-------------------- failover后利用闪回重新加入DG
select STANDBY_BECAME_PRIMARY_SCN from v$database;

flashback database to scn 869428;
alter database convert to physical standby;

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

--------------------
alter system set log_archive_max_processes=4 scope=both sid='*';
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 data_guard_sync_latency=0 scope=both sid='*';

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

--------------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)))';

-------------------------- far sync配置
lhr122--》lhr122fs ---> lhr122dg

----参数配置情况
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';

---DG配置情况
SELECT * FROM V$DATAGUARD_CONFIG;

---主库

NAME                      VALUE
------------------------- ----------------------------------------------------------------------------------------------------
db_file_name_convert      /u04/oradata/lhr122dg/, /u04/oradata/lhr122/
log_file_name_convert     /u04/oradata/lhr122dg/, /u04/oradata/lhr122/
log_archive_dest_1        LOCATION=/u04/oradata/lhr122/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lhr122
log_archive_dest_2        SERVICE=lhr122fs SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lhr122fs
log_archive_dest_state_2  ENABLE
fal_client                lhr122
fal_server                lhr122dg
log_archive_config        DG_CONFIG=(lhr122,lhr122fs,lhr122dg)
standby_file_management   AUTO

---- Far Sync
NAME                      VALUE
------------------------- ----------------------------------------------------------------------------------------------------
db_file_name_convert      /u04/oradata/lhr122/, /u04/oradata/lhr122fs/, /u04/oradata/lhr122dg/, /u04/oradata/lhr122fs/
log_file_name_convert     /u04/oradata/lhr122/, /u04/oradata/lhr122fs/, /u04/oradata/lhr122dg/, /u04/oradata/lhr122fs/
log_archive_dest_1        LOCATION=/u04/oradata/lhr122fs/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lhr122fs
log_archive_dest_2        SERVICE=lhr122dg ASYNC NOAFFIRM VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=lhr122dg
log_archive_dest_state_2  ENABLE
fal_client                lhr122fs
fal_server                lhr122,lhr122dg
log_archive_config        DG_CONFIG=(lhr122,lhr122fs,lhr122dg)
standby_file_management   AUTO

---- 物理备库
NAME                      VALUE
------------------------- ----------------------------------------------------------------------------------------------------
db_file_name_convert      /u04/oradata/lhr122/, /u04/oradata/lhr122dg/
log_file_name_convert     /u04/oradata/lhr122/, /u04/oradata/lhr122dg/
log_archive_dest_1        LOCATION=/u04/oradata/lhr122dg/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lhr122dg
log_archive_dest_2        SERVICE=lhr122 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lhr122
log_archive_dest_state_2  ENABLE
fal_client                lhr122dg
fal_server                lhr122fs, lhr122
log_archive_config        DG_CONFIG=(lhr122,lhr122fs,lhr122dg)
standby_file_management   AUTO

----dgmgrl
remove configuration;
create configuration dgmgrl_lhr122 as primary database is lhr122 connect identifier is lhr122;

add far_sync lhr122fs as connect identifier is lhr122fs;

alter system set log_archive_dest_2='';
add database lhr122dg as connect identifier is lhr122dg maintained as physical;

--这里非常重要
edit database lhr122 set property  RedoRoutes = '(lhr122:lhr122fs SYNC)';
edit far_sync lhr122fs set property  RedoRoutes = '(lhr122:lhr122dg ASYNC)';

show database lhr122  RedoRoutes
show far_sync lhr122fs  RedoRoutes
show database lhr122dg  RedoRoutes

enable configuration

  Members:
  lhr122   - Primary database
    lhr122fs - Far sync instance 
      lhr122dg - Physical standby database 

edit database lhr122 set property 'FastStartFailoverTarget'='lhr122dg';
edit database lhr122dg set property 'FastStartFailoverTarget'='lhr122';

show database lhr122  StaticConnectIdentifier
show far_sync lhr122fs  StaticConnectIdentifier
show database lhr122dg  StaticConnectIdentifier

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

---- switchover 
dgmgrl sys/lhr@lhr122
switchover to lhr122dg;

-----切换完成后需要重新配置RedoRoutes
--若执行卡住，则重启一下备库即可
edit database lhr122dg set property  RedoRoutes = '(lhr122dg:lhr122fs SYNC)';
edit far_sync lhr122fs set property  RedoRoutes = '(lhr122dg:lhr122 ASYNC)';

---- failover 
dgmgrl sys/lhr@lhr122dg
failover to lhr122dg

edit database lhr122dg set property  RedoRoutes = '(lhr122dg:lhr122fs SYNC)';
edit far_sync lhr122fs set property  RedoRoutes = '(lhr122dg:lhr122 ASYNC)';

reinstate database  lhr122

---- FSFO
- 将Data Guard配置设置为MaxAvailability或MaxPerformance保护模式。
- 如果配置保护模式设置为MaxAvailability，请确保将主数据库和快速启动故障转移目标备用数据库的LogXptMode属性设置为SYNC。
- 如果配置保护模式设置为MaxPerformance，请确保将主数据库和快速启动故障转移目标备用数据库的LogXptMode属性设置为ASYNC。
- 确保主数据库和快速启动故障转移目标备用数据库都启用了闪回。
- 将主数据库FastStartFailoverTarget属性设置为所需目标备用数据库的DB_UNIQUE_NAME值，并将所需目标备用数据库FastStartFailoverTarget属性设置为主数据库的DB_UNIQUE_NAME值。

- Set the Oracle Data Guard configuration to either MaxAvailability
  or MaxPerformance protection mode.
- Ensure that the effective redo transport mode for both the
  primary database and the fast-start failover target standby
  database are either SYNC or FASTSYNC if the configuration
  protection mode is set to MaxAvailability.
- Ensure that the LogXptMode property for both the primary
  database and the fast-start failover target standby database
  are set to ASYNC if the configuration protection mode is set to
  MaxPerformance.
- Set the primary database FastStartFailoverTarget property to
  the DB_UNIQUE_NAME value of the desired target standby database
  and set the desired target standby database
  FastStartFailoverTarget property to the DB_UNIQUE_NAME value of
  the primary database.
- Ensure the RedoRoutes property of the primary includes the name
  of the fast-start failover target standby database when the
  protection mode is set to maximum performance mode.
- Ensure the RedoRoutes property of the primary database has only
  one route to the fast-start failover target standby database.

---注意：To enable fast-start failover when using a far sync, the configuration must be upgraded to the maximum availability mode. 

edit database lhr122 set property 'FastStartFailoverTarget'='lhr122dg';
edit database lhr122dg set property 'FastStartFailoverTarget'='lhr122';

show configuration
SHOW DATABASE lhr122 LogXptMode;
SHOW DATABASE lhr122dg LogXptMode;
SHOW DATABASE lhr122 FastStartFailoverTarget
SHOW DATABASE lhr122dg FastStartFailoverTarget
select flashback_on from v$database;
SHOW DATABASE lhr122 RedoRoutes;
SHOW DATABASE lhr122dg RedoRoutes;

edit configuration set protection mode as maxavailability;
enable fast_start failover;
show fast_start failover;

edit configuration set property FastStartFailoverThreshold=10;

dgmgrl sys/lhr@lhr122 "start observer obs_lhr122 LOGFILE IS '/tmp/observer.log'"  &
tailf /tmp/observer.log

ENABLE FAST_START FAILOVER;
SHOW FAST_START FAILOVER;
SHOW OBSERVER;
--stop OBSERVER ALL;

edit database lhr122dg set property  RedoRoutes = '(lhr122dg:lhr122fs SYNC)';
edit far_sync lhr122fs set property  RedoRoutes = '(lhr122dg:lhr122 ASYNC)';

reinstate database  lhr122

disable fast_start failover;
EDIT CONFIGURATION SET PROTECTION MODE MaxPerformance;

switchover to lhr122;
edit database lhr122 set property  RedoRoutes = '(lhr122:lhr122fs SYNC)';
edit far_sync lhr122fs set property  RedoRoutes = '(lhr122:lhr122dg ASYNC)';

------- 手工注册
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 <= 12)
 WHERE A >= 10 ;

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

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

807

808

809

810

811

812

813

814

815

816

817

818

819

820

821

822

823

824

825

826

827

828

829

830

831

832

833

834

835

836

837

838

839

840

841

842

843

844

845

846

847

848

849

850

851

852

853

854

855

856

857

858

859

860

861

862

863

864

865

866

867

868

869

870

871

872

873

874

875

876

877

878

879

880

881

882

883

884

885

886

887

888

889

890

891

892

893

894

895

896

897

898

899

900

901

902

903

904

905

906

907

908

909

910

911

912

913

914

915

916

917

918

919

920

921

922

923

924

925

926

927

928

929

930

931

932

933

934

935

936

937

938

939

940

941

942

943

944

945

946

947

948

949

950

951

952

953

954

955

956

957

958

959

960

961

962

963

964

965

966

967

968

969

970

971

972

973

974

975

976

977

978

979

980

981

982

983

984

985

986

987

988

989

990

991

992

993

994

995

996

997

998

999

1000

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1012

1013

1014

1015

1016

1017

1018

1019

1020

1021

1022

1023

1024

1025

1026

1027

1028

1029

1030

1031

1032

1033

1034

1035

1036

1037

1038

1039

1040

1041

1042

1043

1044

1045

1046

1047

1048

1049

1050

1051

1052

1053

1054

1055

1056

1057

1058

1059

1060

1061

1062

1063

1064

1065

1066

1067

1068

1069

1070

1071

1072

1073

1074

1075

1076

1077

1078

1079

1080

1081

1082

1083

1084

1085

1086

1087

1088

1089

1090

1091

1092

1093

1094

1095

1096

1097

1098

1099

1100

1101

1102

1103

1104

1105

1106

1107

1108

1109

1110

1111

1112

1113

1114

1115

1116

1117

1118

1119

1120

1121

1122

1123

1124

1125

1126

1127

1128

1129

1130

1131

1132

1133

1134

1135

1136

1137

1138

1139

1140

1141

1142

1143

1144

1145

1146

1147

1148

1149

1150

1151

1152

1153

1154

1155

1156

1157

1158

1159

1160

1161

1162

1163

1164

1165

1166

1167

1168

1169

1170

1171

1172

1173

1174

1175

1176

1177

1178

1179

1180

1181

1182

1183

1184

1185

1186

1187

1188

1189

1190

1191

1192

1193

1194

1195

1196

1197

1198

1199

1200

1201

1202

1203

1204

1205

1206

1207

1208

1209

1210

1211

1212

1213

1214

1215

1216

1217

1218

1219

1220

1221

1222

1223

1224

1225

1226

1227

1228

1229

1230

1231

1232

1233

1234

1235

1236

1237

1238

1239

1240

1241

1242

1243

1244

1245

1246

1247

1248

1249

1250

1251

1252

1253

1254

1255

1256

1257

1258

1259

1260

1261

1262

1263

1264

1265

1266

1267

1268

1269

1270

1271

1272

1273

1274

1275

1276

1277

1278

1279

1280

1281

1282

1283

1284

1285

1286

1287

1288

1289

1290

1291

1292

1293

1294

1295

1296

1297

1298

1299

1300

1301

1302

1303

1304

1305

1306

1307

1308

1309

1310

1311

1312

1313

1314

1315

1316

1317

1318

1319

1320

1321

1322

1323

1324

1325

1326

1327

1328

1329

1330

1331

1332

1333

1334

1335

1336

1337

1338

1339

1340

1341

1342

1343

1344

1345

1346

1347

1348

1349

1350

1351

1352

1353

1354

1355

1356

1357

1358

1359

1360

1361

1362

1363

1364

1365

1366

1367

1368

1369

1370

1371

1372

1373

1374

1375

1376

1377

1378

1379

1380

1381

1382

1383

1384

1385

1386

1387

1388

1389

1390

1391

1392

1393

1394

1395

1396

1397

1398

1399

1400

1401

1402

1403

1404

1405

1406

1407

1408

1409

1410

1411

1412

1413

1414

1415

1416

1417

1418

1419

1420

1421

1422

1423

1424

1425

1426

1427

1428

1429

1430

1431

1432

1433

1434

1435

1436

1437

1438

1439

1440

1441

1442

1443

1444

1445

1446

1447

1448

1449

1450

1451

1452

1453

1454

1455

1456

1457

1458

1459

1460

1461

1462

1463

1464

1465

1466

1467

1468

1469

1470

1471

1472

1473

1474

1475

1476

1477

1478

1479

1480

1481

1482

1483

1484

1485

1486

1487

1488

1489

1490

1491

1492

1493

1494

1495

1496

1497

1498

1499

1500

1501

1502

1503

1504

1505

1506

1507

1508

1509

1510

1511

1512

1513

1514

1515

1516

1517

1518

1519

1520

1521

1522

1523

1524

1525

1526

1527

1528

1529

1530

1531

1532

1533

1534

1535

1536

1537

1538

1539

1540

1541

1542

1543

1544

1545

1546

1547

1548

1549

1550

1551

1552

1553

1554

1555

1556

1557

1558

1559

1560

1561

1562

1563

1564

1565

1566

1567

1568

1569

1570

1571

1572

1573

1574

1575

1576

1577

1578

1579

1580

1581

1582

1583

1584

1585

1586

1587

1588

1589

1590

1591

1592

1593

1594

1595

1596

1597

1598

1599

1600

1601

1602

1603

1604

1605

1606

1607

1608

1609

1610

1611

1612

1613

1614

1615

1616

1617

1618

1619

1620

1621

1622

1623

1624

1625

1626

1627

1628

1629

1630

1631

1632

1633

1634

1635

1636

1637

1638

1639

1640

1641

1642

1643

1644

1645

1646

1647

1648

1649

1650

1651

1652

1653

1654

1655

1656

1657

1658

1659

1660

1661

1662

1663

1664

1665

1666

1667

1668

1669

1670

1671

1672

1673

1674

1675

1676

1677

1678

1679

1680

1681

1682

1683

1684

1685

1686

1687

1688

1689

1690

1691

1692

1693

1694

1695

1696

1697

1698

1699

1700

1701

1702

1703

1704

1705

1706

1707

1708

1709

1710

1711

1712

1713

1714

1715

1716

1717

1718

1719

1720

1721

1722

1723

1724

1725

1726

1727

1728

1729

1730

1731

1732

1733

1734

1735

1736

1737

1738

1739

1740

1741

1742

1743

1744

1745

1746

1747

1748

1749

1750

1751

1752

1753

1754

1755

1756

1757

1758

1759

1760

1761

1762

1763

1764

1765

1766

1767

1768

1769

1770

1771

1772

1773

1774

1775

1776

1777

1778

1779

1780

1781

1782

1783

1784

1785

1786

1787

1788

1789

1790

1791

1792

1793

1794

1795

1796

1797

1798

1799

1800

1801

1802

1803

1804

1805

1806

1807

1808

1809

1810

1811

1812

1813

1814

1815

1816

1817

1818

1819

1820

1821

1822

1823

1824

1825

1826

1827

1828

1829

1830

1831

1832

1833

1834

1835

1836

1837

1838

1839

1840

1841

1842

1843

1844

1845

1846

1847

1848

1849

1850

1851

1852

1853

1854

1855

1856

1857

1858

1859

1860

1861

1862

1863

1864

1865

1866

1867

1868

1869

1870

1871

1872

1873

1874

1875

1876

1877

1878

1879

1880

1881

1882

1883

1884

1885

1886

1887

1888

1889

1890

1891

1892

1893

1894

1895

1896

1897

1898

1899

1900

1901

1902

1903

1904

1905

1906

1907

1908

1909

1910

1911

1912

1913

1914

1915

1916

1917

1918

1919

1920

1921

1922

1923

1924

1925

1926

1927

1928

1929

1930

1931

1932

1933

1934

1935

1936

1937

1938

1939

1940

1941

1942

1943

1944

1945

1946

1947

1948

1949

1950

1951

1952

1953

1954

1955

1956

1957

1958

1959

1960

1961

1962

1963

1964

1965

1966

1967

1968

1969

1970

1971

1972

1973

1974

1975

1976

1977

1978

1979

1980

1981

1982

1983

1984

1985

1986

1987

1988

1989

1990

1991

1992

1993

1994

1995

1996

1997

1998

1999

2000

2001

2002

2003

2004

2005

2006

2007

2008

2009

2010

2011

2012

2013

2014

2015

2016

2017

2018

2019

2020

2021

2022

2023

2024

2025

2026

2027

2028

2029

2030

2031

2032

2033

2034

2035

2036

2037

2038

2039

2040

2041

2042

2043

2044

2045

2046

2047

2048

2049

2050

2051

2052

2053

2054

2055

2056

2057

2058

2059

2060

2061

2062

2063

2064

2065

2066

2067

2068

2069

2070

2071

2072

2073

2074

2075

2076

2077

2078

2079

2080

2081

2082

2083

2084

2085

2086

2087

2088

2089

2090

2091

2092

2093

2094

2095

2096

2097

2098

2099

2100

2101

2102

2103

2104

2105

2106

2107

2108

2109

2110

2111

2112

2113

2114

2115

2116

2117

2118

2119

2120

2121

2122

2123

2124

2125

2126

2127

2128

2129

2130

2131

2132

2133

2134

2135

2136

2137

2138

2139

2140

2141

2142

2143

2144

2145

2146

2147

2148

2149

2150

2151

2152

2153

2154

2155

2156

2157

2158

2159

2160

2161

2162

2163

2164

2165

2166

2167

2168

2169

2170

2171

2172

2173

2174

2175

2176

2177

2178

2179

2180

2181

2182

2183

2184

2185

2186

2187

2188

2189

2190

2191

2192

2193

2194

2195

2196

2197

2198

2199

2200

2201

2202

2203

2204

2205

2206

2207

2208

2209

2210

2211

2212

2213

2214

2215

2216

2217

2218

2219

2220

2221

2222

2223

2224

2225

2226

2227

2228

2229

2230

2231

2232

2233

2234

2235

2236

2237

2238

2239

2240

2241

2242

2243

2244

2245

2246

2247

2248

2249

2250

2251

2252

2253

2254

2255

2256

2257

2258

2259

2260

2261

2262

2263

2264

2265

2266

2267

2268

2269

2270

2271

2272

2273

2274

2275

2276

2277

2278

2279

2280

2281

2282

2283

2284

2285

2286

2287

2288

2289

2290

2291

2292

2293

2294

2295

2296

2297

2298

2299

2300

2301

2302

2303

2304

2305

2306

2307

2308

2309

2310

2311

2312

2313

2314

2315

2316

2317

2318

2319

2320

2321

2322

2323

2324

2325

2326

2327

2328

2329

2330

2331

2332

2333

2334

2335

2336

2337

2338

2339

2340

2341

2342

2343

2344

2345

2346

2347

2348

2349

2350

2351

2352

2353

2354

2355

2356

2357

2358

2359

2360

2361

2362

2363

2364

2365

2366

2367

2368

2369

2370

2371

2372

2373

2374

2375

2376

2377

2378

2379

2380

2381

2382

2383

2384

2385

2386

2387

2388

2389

2390

2391

2392

2393

2394

2395

2396

2397

2398

2399

2400

2401

2402

2403

2404

2405

2406

2407

2408

2409

2410

2411

2412

2413

2414

2415

2416

2417

2418

2419

2420

2421

2422

2423

2424

2425

2426

2427

2428

2429

2430

2431

2432

2433

2434

2435

2436

2437

2438

2439

2440

2441

2442

2443

2444

2445

2446

2447

2448

2449

2450

2451

2452

2453

2454

2455

2456

2457

2458

2459

2460

2461

2462

2463

2464

2465

2466

2467

2468

2469

2470

2471

2472

2473

2474

2475

2476

2477

2478

2479

2480

2481

2482

2483

2484

2485

2486

2487

2488

2489

2490

2491

2492

2493

2494

2495

2496

2497

2498

2499

2500

2501

2502

2503

2504

2505

2506

2507

2508

2509

2510

2511

2512

2513

2514

2515

2516

2517

2518

2519

2520

2521

2522

2523

2524

2525

2526

2527

2528

2529

2530

2531

2532

2533

2534

2535

2536

2537

2538

2539

2540

2541

2542

2543

2544

2545

2546

2547

2548

2549

2550

2551

2552

2553

2554

2555

2556

2557

2558

2559

2560

2561

2562

2563

2564

2565

2566

2567

2568

2569

2570

2571

2572

2573

2574

2575

2576

2577

2578

2579

2580

2581

2582

2583

2584

2585

2586

2587

2588

2589

2590

2591

2592

2593

2594

2595

2596

2597

2598

2599

2600

2601

2602

2603

2604

2605

2606

2607

2608

2609

2610

2611

2612

2613

2614

2615

2616

2617

2618

2619

2620

2621

2622

2623

2624

2625

2626

2627

2628

2629

2630

2631

2632

2633

2634

2635

2636

2637

2638

2639

2640

2641

2642

2643

2644

2645

2646

2647

2648

2649

2650

2651

2652

2653

2654

2655

2656

2657

2658

2659

2660

2661

2662

2663

2664

2665

2666

2667

2668

2669

2670

2671

2672

2673

2674

2675

2676

2677

2678

2679

2680

2681

2682

2683

2684

2685

2686

2687

2688

2689

2690

2691

2692

2693

2694

2695

2696

2697

2698

2699

2700

2701

2702

2703

2704

2705

2706

2707

2708

2709

2710

2711

2712

2713

2714

2715

2716

2717

2718

2719

2720

2721

2722

2723

2724

2725

2726

2727

2728

2729

2730

2731

2732

2733

2734

2735

2736

2737

2738

2739

2740

2741

2742

2743

2744

2745

2746

2747

2748

2749

2750

2751

2752

2753

2754

2755

2756

2757

2758

2759

2760

2761

2762

2763

2764

2765

2766

2767

2768

2769

2770

2771

2772

2773

2774

2775

2776

2777

2778

2779

2780

2781

2782

2783

2784

2785

2786

2787

2788

2789

2790

2791

2792

2793

2794

2795

2796

2797

2798

2799

2800

2801

2802

2803

2804

2805

2806

2807

2808

2809

2810

2811

2812

2813

2814

2815

2816

2817

2818

2819

2820

2821

2822

2823

2824

2825

2826

2827

2828

2829

2830

2831

2832

2833

2834

2835

2836

2837

2838

2839

2840

2841

2842

2843

2844

2845

2846

2847

2848

2849

2850

2851

2852

2853

2854

2855

2856

2857

2858

2859

2860

2861

2862

2863

2864

2865

2866

2867

2868

2869

2870

2871

2872

2873

2874

2875

2876

2877

2878

2879

2880

2881

2882

2883

2884

2885

2886

2887

2888

2889

2890

2891

2892

2893

2894

2895

2896

2897

2898

2899

2900

2901

2902

2903

2904

2905

2906

2907

2908

2909

2910

2911

2912

2913

2914

2915

2916

2917

2918

2919

2920

2921

2922

2923

2924

2925

2926

2927

2928

2929

2930

2931

2932

2933

2934

2935

2936

2937

2938

2939

2940

2941

2942

2943

2944

2945

2946

2947

2948

2949

2950

2951

2952

2953

2954

2955

2956

2957

2958

2959

2960

2961

2962

2963

2964

2965

2966

2967

2968

2969

2970

2971

2972

2973

2974

2975

2976

2977

2978

2979

2980

2981

2982

2983

2984

2985

2986

2987

2988

2989

2990

2991

2992

2993

2994

2995

2996

2997

2998

2999

3000

3001

3002

3003

3004

3005

3006

3007

3008

3009

3010

3011

3012

3013

3014

3015

3016

3017

3018

3019

3020

3021

3022

3023

3024

3025

3026

3027

3028

3029

3030

3031

3032

3033

3034

3035

3036

3037

3038

3039

3040

3041

3042

3043

3044

3045

3046

3047

3048

3049

3050

3051

3052

3053

3054

3055

3056

3057

3058

3059

3060

3061

3062

3063

3064

3065

3066

3067

3068

3069

3070

3071

3072

3073

3074

3075

3076

3077

3078

3079

3080

3081

3082

3083

3084

3085

3086

3087

3088

3089

3090

3091

3092

3093

3094

3095

3096

3097

3098

3099

3100

3101

3102

3103

3104

3105

3106

3107

3108

3109

3110

3111

3112

3113

3114

3115

3116

3117

3118

3119

3120

3121

3122

3123

3124

3125

3126

3127

3128

3129

3130

3131

3132

3133

3134

3135

3136

3137

3138

3139

3140

3141

3142

3143

3144

3145

3146

3147

3148

3149

3150

3151

3152

3153

3154

3155

3156

3157

3158

3159

3160

3161

3162

3163

3164

3165

3166

3167

3168

3169

3170

3171

3172

3173

3174

3175

3176

3177

3178

3179

3180

3181

3182

3183

3184

3185

3186

3187

3188

3189

3190

3191

3192

3193

3194

3195

3196

3197

3198

3199

3200

3201

3202

3203

3204

3205

3206

3207

3208

3209

3210

3211

3212

3213

3214

3215

3216

3217

3218

3219

3220

3221

3222

3223

3224

3225

3226

3227

3228

3229

3230

3231

3232

3233

3234

3235

3236

3237

3238

3239

3240

3241

3242

3243

3244

3245

3246

3247

3248

3249

3250

3251

3252

3253

3254

3255

3256

3257

3258

3259

----------------------------------- 主库、物理备库、逻辑备库

----主库：

--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进程实时应用

set line 9999

col name format a10

col FS_FAILOVER_OBSERVER_HOST format a20

select INST_ID, dbid,name,DB_UNIQUE_NAME,flashback_on,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

SELECT * FROM V$DATAGUARD_CONFIG;

-- 查询延迟

select name,value from gv$dataguard_stats;

------------------------------ 进程信息通用

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

SELECT * FROM sys.vw_dg_infor_lhr;

col group_# format a5

col CLIENT_PID format a8

set line 9999 pagesize 9999

SELECT * FROM sys.VW_DG_PROCESS_LHR;

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;

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

--DG报错

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

------------------------------ 主备库信息

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

--备库

select al.thrd "Thread",

almax "Last Seq Received",

lhmax "Last Seq Applied"

from (select thread# thrd, max(sequence#) almax

from v$archived_log

where resetlogs_change# =

(select resetlogs_change# from v$database)

group by thread#) al,

(select thread# thrd, max(sequence#) lhmax

from v$log_history

where resetlogs_change# =

(select resetlogs_change# from v$database)

group by thread#) lh

where al.thrd = lh.thrd;

---是否启用实时应用

--11g 主库： select RECOVERY_MODE from v$archive_dest_status;

----------------------------------- 物理备库

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;

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

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

alter database recover managed standby database cancel;

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

alter system set log_archive_dest_state_2='defer';

alter system switch logfile;

alter system set log_archive_dest_state_2='ENABLE';

alter system switch 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;

---是否启用实时应用

ps -ef|grep ora_mrp

--以下视图结果为：OPEN_READ-ONLY、MANAGED REAL TIME APPLY

select a.DATABASE_MODE, a.RECOVERY_MODE

from v$archive_dest_status a

where a.DATABASE_MODE <> 'UNKNOWN' and status='VALID' and a.dest_name<>'STANDBY_ARCHIVE_DEST'

and SRL = (SELECT CASE

WHEN NB.DATABASE_ROLE like '%STANDBY%' then

'NO'

ELSE

'YES'

END

FROM V$DATABASE NB);

------------物理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,(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#;

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 B.THREAD# = A.THREAD#

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

------------物理dg日志应用情况(备库查询为准)--级联DG

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

--在主库或备库查询哪些归档日志已经应用到备库，这些日志可以被删除了：

SELECT THREAD#,

NAME,

SEQUENCE#,

ARCHIVED,

APPLIED,

A.NEXT_CHANGE#,

A.COMPLETION_TIME,

'delete archivelog sequence ' || A.SEQUENCE# || ' thread ' ||

A.THREAD# || ';' EXEC_SQL

FROM V$ARCHIVED_LOG A

WHERE (A.THREAD#, A.SEQUENCE#, a.RESETLOGS_CHANGE#) IN

(SELECT b.THREAD#,

b.SEQUENCE#,

b.RESETLOGS_CHANGE#

FROM V$ARCHIVED_LOG B

WHERE B.APPLIED = 'YES'

AND b.COMPLETION_TIME <= SYSDATE - 3)

AND a.NAME NOT IN (SELECT b.DESTINATION

FROM v$archive_dest b

WHERE b.DESTINATION IS NOT NULL)

AND A.COMPLETION_TIME <= SYSDATE - 3

ORDER BY A.THREAD#,

A.SEQUENCE#;

----物理备库应用进程日志

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

set line 9999 pagesize 9999

col message format a85

SELECT inst_id, severity, FACILITY, TIMESTAMP, MESSAGE

FROM (select d.inst_id,

FACILITY,

SEVERITY,

TIMESTAMP,

MESSAGE,

rank() over(partition by d.inst_id ORDER BY d.message_num desc) rank_order

from gv$dataguard_status d)

where rank_order <= 5

order by inst_id, rank_order desc;

----参数配置情况

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;

----------- 查询备库归档应用是否有问题

select count(*) from (

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)

WHERE (ERROR IS NOT NULL OR STATUS <> 'VALID' OR

LAST_ARCHIVED > APPLIED_SEQ# + 2 OR Current_Seq# > LAST_ARCHIVED + 3 );

------------------------------------- 逻辑备库

ALTER DATABASE STOP LOGICAL STANDBY APPLY; ---停止应用，等待事务完成

ALTER DATABASE ABORT LOGICAL STANDBY APPLY;--不等待事务完成就停止

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; ---实时

ALTER DATABASE START LOGICAL STANDBY APPLY; --非实时

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE SKIP FAILED TRANSACTION;

---是否启用实时应用

ps -ef|grep -i ora_lsp

11g：select RECOVERY_MODE from v$archive_dest_status;

---通用：

col REALTIME_APPLY format a15

col STATE format a20

select * from v$Logstdby_state;

------------日志应用情况

set line 9999 pagesize 9999

col FILE_NAME format a120

select sequence#, file_name, applied, TIMESTAMP from dba_logstdby_log d order by d.sequence#;

SET LINE 9999 PAGESIZE 9999

COL FILE_NAME FORMAT A120

SELECT THREAD#,SEQUENCE#, FILE_NAME, APPLIED, TIMESTAMP

FROM DBA_LOGSTDBY_LOG D

WHERE D.SEQUENCE# >=(SELECT MAX(SEQUENCE#)-3 FROM DBA_LOGSTDBY_LOG NB WHERE NB.THREAD#=D.THREAD# AND NB.APPLIED='YES' )

ORDER BY THREAD#,D.SEQUENCE#;

--查看备库是否有任何DDL/DML语句未成功应用

COL EVENT_TIMESTAMP FORMAT A30

COL EVENT FORMAT A40

COL EVENT_STATUS FORMAT A80

SELECT A.EVENT_TIME,

A.CURRENT_SCN,

A.COMMIT_SCN,

XIDUSN,

XIDSLT,

XIDSQN,

TO_CHAR(EVENT) EVENT,

A.STATUS_CODE,

STATUS EVENT_STATUS

FROM DBA_LOGSTDBY_EVENTS A

WHERE A.EVENT_TIME >= SYSDATE - 10 / 1660

ORDER BY A.EVENT_TIME ;

----------------- 物理DG参数优化调整

alter system set parallel_execution_message_size=32768 scope=spfile; --默认16384

alter system set filesystemio_options=setall scope=spfile; --默认 none

alter system set disk_asynch_io=true scope=spfile; --默认就是true

alter system set db_lost_write_protect=typical scope=spfile; --默认是full

alter system set db_block_checksum=false scope=spfile ; --默认是TYPICAL

alter system set DB_BLOCK_CHECKING=false scope=spfile ; --默认是false

alter system set db_writer_processes=8 scope=spfile; --默认是6个

shutdown immediate;

startup nomount;

alter database mount standby database;

alter database recover managed standby database parallel 4 disconnect from session; ----并行度根据CPU核数*2设定

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

---------------------------------------------------- 10g脚本

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

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;

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,

(SELECT MAX(sequence#) FROM v$log na WHERE na.thread# = al.thread#) Current_Seq#,

MAX(sequence#) Last_Archived,

max(CASE

WHEN al.APPLIED = 'YES' AND ads.TYPE <> 'LOCAL' THEN

al.sequence#

end) APPLIED_SEQ#

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'

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

ORDER BY al.thread#, ads.dest_id;

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#)-5 from v$log b where b.THREAD#=a.THREAD# )

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

ORDER BY a.THREAD#,

a.sequence#;

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

---修改如下几个参数需要重启数据库：

1、修改归档模式

2、修改参数：db_unique_name

3、修改参数：db_file_name_convert

4、修改参数：log_file_name_convert

--- RAC到单实例需要注意以下几个参数

*.thread=1

*.undo_tablespace='UNDOTBS1'

*.instance_number=1

*.cluster_database=false

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

--------------- v$standby_log

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 ;

select (select instance_name from gv$instance na where na.inst_id=a.INST_ID) instance_name, GROUP#, DBID db_id, THREAD#, SEQUENCE#, BYTES, USED, ARCHIVED, STATUS, FIRST_CHANGE#, NEXT_CHANGE#,LAST_CHANGE# from gv$standby_log a order by INST_ID,THREAD#,GROUP#;

alter database drop standby logfile group 4,group 5,group 6,group 7;

alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;

alter database add standby logfile group 4 ('/u01/app/oracle/oradata/oradg11g/standby_redo04.log') size 50m;

alter database add standby logfile group 5 ('/u01/app/oracle/oradata/oradg11g/standby_redo05.log') size 50m;

alter database add standby logfile group 6 ('/u01/app/oracle/oradata/oradg11g/standby_redo06.log') size 50m;

alter database add standby logfile group 7 ('/u01/app/oracle/oradata/oradg11g/standby_redo07.log') size 50m;

alter database add logfile thread 1 group 11 ('+DATA','+DATA') size 50M ,group 12 ('+DATA','+DATA') size 50M;

alter database add logfile thread 2 group 13 ('+DATA','+DATA') size 50M ,group 14 ('+DATA','+DATA') size 50M;

alter database add standby logfile thread 1 group 15 ('+DATADG','+DATADG') size 50M ,group 16 ('+DATADG','+DATADG') size 50M,group 17 ('+DATADG','+DATADG') size 50M;

alter database add standby logfile thread 2 group 18 ('+DATADG','+DATADG') size 50M ,group 19 ('+DATADG','+DATADG') size 50M,group 20 ('+DATADG','+DATADG') size 50M;

set line 9999

col HOST_NAME format a10　

select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS,ACTIVE_STATE,INSTANCE_ROLE,DATABASE_STATUS from gv$INSTANCE;

select INST_ID,name , open_mode, log_mode,force_logging from gv$database;

select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

set line 9999

col name format a10

col FS_FAILOVER_OBSERVER_HOST format a20

select dbid,name,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,

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;

SQL> select INST_ID, name , open_mode, log_mode,force_logging,DATABASE_ROLE from gv$database;

NAME OPEN_MODE LOG_MODE FOR

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

RACDB READ WRITE NOARCHIVELOG YES

alter system set db_unique_name='lhrrac1' scope=spfile sid='*';

alter system set log_archive_config='DG_CONFIG=(lhrrac1,lhrrac1DG)' sid='*';

alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=lhrrac1 valid_for=(ALL_LOGFILES,ALL_ROLES)' sid='*';

alter system set log_archive_dest_2='SERVICE=TNS_lhrrac1DG LGWR ASYNC db_unique_name=lhrrac1DG valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)' sid='*';

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

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

alter system set log_archive_max_processes=4 sid='*';

alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile sid='*';

--Add below item when DB turn to standby role

alter system set db_file_name_convert='+DATADG','+DATA' scope=spfile sid='*';

alter system set log_file_name_convert='+DATADG','+DATA' scope=spfile sid='*';

alter system set standby_file_management='AUTO' sid='*';

alter system set fal_server='TNS_lhrrac1DG' sid='*';

alter system set fal_client='TNS_lhrrac1' sid='*';

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 ;

col member format a80

select * from v$log;

select * from v$logfile;

alter database add standby logfile thread 1 group 5 ('+DATA','+DATA') size 50M ,group 6 ('+DATA','+DATA') size 50M ,group 7 ('+DATA','+DATA') size 50M ;

alter database add standby logfile thread 2 group 8 ('+DATA','+DATA') size 50M ,group 9 ('+DATA','+DATA') size 50M ,group 10 ('+DATA','+DATA') size 50M ;

startup mount

select name , open_mode, log_mode,force_logging from gv$database;

alter database archivelog;

run {

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

backup database format '/rman_backup/FULL_%U.bak';

sql 'alter system archive log current';

backup archivelog all format '/rman_backup/ARC_%U.bak';

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

backup device type disk format '/rman_backup/standby_%U.ctl' current controlfile for standby;

create pfile ='/rman_backup/initphydb.ora' from spfile;

---rac 2个节点的sys用户的口令一样

alter user sys identified by lhr;

----rac1

scp /rman_backup/*.bak oracle@192.168.59.140:/rman_backup/

scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwracdb1 oracle@192.168.59.140:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwphydb

----------dg

[oracle@dg rman_backup]$ cp initphydb.ora $ORACLE_HOME/dbs/

[oracle@dg rman_backup]$ cd $ORACLE_HOME/dbs

物理备库初始化参数文件修改：

phydb.__db_cache_size=255852544

phydb.__java_pool_size=4194304

phydb.__large_pool_size=4194304

phydb.__oracle_base='/u01/app/oracle'#ORACLE_BASE environment from set

phydb.__pga_aggregate_target=419430400

phydb.__sga_target=423624704

phydb.__shared_io_pool_size=0

phydb.__shared_pool_size=146800640

phydb.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/phydb/adump'

*.audit_trail='db'

*.cluster_database=false

*.compatible='11.2.0.0.0'

*.control_files='+DATA/phydb/controlfile/cont.ctl'

*.core_dump_dest='/u01/app/oracle/diag/rdbms/phydb/cdump'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.db_file_name_convert='+DATA/racdb/','+DATA/phydb/'

*.db_name='racdb'

*.db_recovery_file_dest='+FRA'

*.db_recovery_file_dest_size=4070572032

*.db_unique_name='phydb'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP)(SERVICE=phydbXDB)'

*.fal_client='phydb'

*.fal_server='racdb1','racdb2'

*.log_archive_config='dg_config=(racdb,phydb)'

*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=phydb'

*.log_archive_dest_2='service=racdb1 valid_for=(online_logfiles,primary_role) db_unique_name=racdb'

*.log_archive_format='ARC_%t_%S_%r.arc'

*.log_file_name_convert='+DATA/racdb/','+DATA/phydb/'

*.memory_target=842006528

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='exclusive'

*.service_names='phydb'

*.standby_file_management='auto'

*.thread=1

*.undo_management='auto'

*.undo_tablespace='UNDOTBS1'

[oracle@dg dbs]$ mkdir -p /u01/app/oracle/admin/phydb/adump

[oracle@dg dbs]$ mkdir -p /u01/app/oracle/diag/rdbms/phydb/cdump

[oracle@dg dbs]$

[grid@dg ~]$ asmcmd

ASMCMD> ls

DATA/

FRA/

GRIDDG/

ASMCMD> cd DATA

ASMCMD> ls

ASMCMD> mkdir phydb

ASMCMD> ls

phydb/

ASMCMD>

racdb =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdb.lhr.com)

)

racdb1 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdb.lhr.com)

(INSTANCE_NAME = racdb1)

)

racdb2 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdb.lhr.com)

(INSTANCE_NAME = racdb2)

)

phydb =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = phydb)

)

sqlplus sys/lhr@racdb as sysdba

conn sys/lhr@racdb1 as sysdba

conn sys/lhr@racdb2 as sysdba

exit

startup nomount

[oracle@dg dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 3 19:06:52 2014

connected to target database: RACDB (not mounted)

RMAN> restore standby controlfile from '/rman_backup/standby_0tpk53jq_1_1.ctl';

Starting restore at 2014-10-03 19:06:55

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=16 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

output file name=+DATA/phydb/controlfile/cont.ctl

Finished restore at 2014-10-03 19:07:22

RMAN>

SQL> alter database mount;

run {

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

restore database ;

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

set line 9999

set pagesize 9999

col name format a100

select 'datafile' , name from v$datafile

union

select 'controlfile', name from v$controlfile

union

select 'logfile', member from v$logfile;

SQL> select name from v$datafile

2 union

3 select name from v$controlfile

4 union

5 select member from v$logfile;

NAME

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

+DATA/phydb/controlfile/cont.ctl

+DATA/phydb/datafile/example.258.860008433

+DATA/phydb/datafile/sysaux.260.860008695

+DATA/phydb/datafile/system.257.860008433

+DATA/phydb/datafile/undotbs1.261.860008697

+DATA/phydb/datafile/undotbs2.262.860008697

+DATA/phydb/datafile/users.259.860008435

+DATA/phydb/onlinelog/group_1.261.859849493

+DATA/phydb/onlinelog/group_2.262.859849495

+DATA/phydb/onlinelog/group_3.266.859850179

+DATA/phydb/onlinelog/group_4.267.859850183

+FRA/racdb/onlinelog/group_1.257.859849493

+FRA/racdb/onlinelog/group_2.258.859849495

+FRA/racdb/onlinelog/group_3.259.859850181

+FRA/racdb/onlinelog/group_4.260.859850187

15 rows selected.

SQL>

select * from v$standby_log;

alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;

alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M ;

select * from v$standby_log;

ORA-15041: diskgroup "DATA" space exhausted

alter diskgroup DATA add disk '/dev/raw/raw5';

--rac1

alter system set log_archive_dest_2='service=phydb lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=phydb' sid='*';

alter system set log_archive_config='dg_config=(racdb,phydb)' sid='*';

select sequence#,name,applied from v$archived_log;

Check that the primary and standby are using a password file

and remote_login_passwordfile is set to SHARED or EXCLUSIVE,

and that the SYS password is same in the password files.

returning error ORA-16191

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

PING[ARC1]: Heartbeat failed to connect to standby 'phydb'. Error is 16191.

Sun Oct 05 16:25:17 2014

Error 1017 received logging on to the standby

---- 3 个节点

orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwracdb2 password=lhr force=y

-------dg alter database recover managed standby database disconnect from session;

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

select sequence#,name,applied from v$archived_log;

col name format a100

select sequence#,name,archived,applied from v$archived_log order by name,sequence#;

alter database recover managed standby database cancel;

alter database open;

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

select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

-- 检查是否存在GAP

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

SELECT 'alter database register or replace logical logfile ''/u04/arch/WDSTD/log_1_' || a ||

' _666200636.arc'';' logical_dg,

'alter database register or replace logfile ''/u04/arch/WDSTD/log_1_' || a ||

' _666200636.arc'';' physical_dg

FROM (SELECT level a FROM dual connect by level <= 2000)

where a >= 1980 ;

----------- 11g rman

duplicate target database

for standby

from active database

DORECOVER

spfile

set db_unique_name='ora11gdg'

set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=ora11gdg'

--set log_archive_dest_2='service=ora11g async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=ora11g'

set standby_file_management='AUTO'

set fal_server='ora11g'

set fal_client='ora11gdg'

set control_files='/u01/app/oracle/oradata/ora11gdg/crontal01.ctl','/u01/app/oracle/oradata/ora11gdg/control02.ctl'

set db_file_name_convert='ora11g','ora11gdg'

set log_file_name_convert='ora11g','ora11gdg'

set memory_target='400M'

set audit_file_dest='/u01/app/oracle/admin/ora11gdg/adump'

;

---------------- 完成

---- 测试

SQL> set line 200

SQL> select tablespace_name, file_name from dba_data_files;

------ 测试DATAGUARDSWITCHOVER功能

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string +DATA/racdb/spfileracdb.ora

SQL>

SQL> show parameter fal

NAME TYPE VALUE

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

fal_client string

fal_server string

alter system set fal_client='racdb1' sid='racdb1';

alter system set fal_client='racdb2' sid='racdb2';

alter system set fal_server='phydb' sid='*';

select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;

alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M ;

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

alter database commit to switchover to primary;

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

alter database recover managed standby database parallel 2 disconnect from session;

------ dgmgrl

CREATE CONFIGURATION 'DR1' AS primary database is 'racdb' connect identifier is racdb;

SHOW CONFIGURATION;

add database 'phydb' as connect identifier is phydb maintained as physical;

show database verbose 'racdb';

show database 'racdb';

enable database 'racdb';

enable database 'phydb';

standby库：

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 32

Next log sequence to archive 0

Current log sequence 33

SQL>

------------------- 物理转换为逻辑

alter database recover managed standby database cancel;

----------------------------------------------------------------------------------------------------- windows下搭建------------------------------------------------------------------

select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from v$database;

alter database force logging;

alter database archivelog;

select * from v$standby_log;

select group#,bytes/1024/1024 ||'M' from v$log ;

SELECT * FROM V$LOGFILE;

alter database add standby logfile group 5 ( 'Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo05A.log','Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo05B.log') size 100m;

alter database add standby logfile group 6 ( 'Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo06A.log','Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo06B.log') size 100m;

alter database add standby logfile group 7 ( 'Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo07A.log','Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo07B.log') size 100m;

alter database add standby logfile group 8 ( 'Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo08A.log','Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo08B.log') size 100m;

alter database add standby logfile group 9 ( 'Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo09A.log','Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo09B.log') size 100m;

select * from v$standby_log;

alter system set db_unique_name='orcl' scope=spfile;

alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)';

alter system set log_archive_dest_1='LOCATION=Z:\archive db_unique_name=orcl valid_for=(ALL_LOGFILES,ALL_ROLES)';

alter system set log_archive_dest_2='SERVICE=orcldg LGWR ASYNC db_unique_name=orcldg 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='orcldg','orcl' scope=spfile;

alter system set log_file_name_convert='orcldg','orcl' scope=spfile;

alter system set standby_file_management='AUTO';

alter system set fal_server='orcldg';

alter system set fal_client='orcl';

重启主库

---------备库操作

oradim -new -sid orcldg

主备库配置监听和tnsnames.ora ---配置静态监听

-由于要求主库与备库sys使用相同的密码，在此处，我们直接复制了主库的密码文件到备库

echo db_name=orcl >$ORACLE_HOME/database/initorcldg.ora 而且db_name必须相同

创建相关路径

mkdir D:\app\Administrator\admin\orcldg\adump

mkdir Z:\archive

mkdir Z:\app\Administrator\oradata\orcldg\

set ORACLE_SID=orcldg

services.msc 中启动服务

sqlplus sys/wxqhaey as sysdba

startup nomount

rman target sys/wxqhaey@orcl auxiliary sys/wxqhaey@orcldg

duplicate target database

for standby nofilenamecheck

from active database

DORECOVER

spfile

set db_unique_name='orcldg'

set log_archive_dest_1='LOCATION=Z:\archive\orcldg valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=orcldg'

set log_archive_dest_2='service=orcl async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=orcl'

set standby_file_management='AUTO'

set fal_server='orcl'

set fal_client='orcldg'

set control_files='Z:\app\Administrator\oradata\orcldg\control01.ctl','Z:\app\Administrator\oradata\orcldg\control02.ctl'

set db_file_name_convert='orcl','orcldg'

set log_file_name_convert='orcl','orcldg'

set memory_target='161061273600'

set audit_file_dest='D:\app\Administrator\admin\orcldg\adump'

;

-------校验

select open_mode from v$database;

show parameter instance_name

alter database open;

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 using current logfile disconnect;

--主库切换日志

ALTER SYSTEM SWITCH LOGFILE;

orcl.__db_cache_size=104152956928

orcl.__java_pool_size=1879048192

orcl.__large_pool_size=1342177280

orcl.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=22548578304

orcl.__sga_target=138512695296

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=29796335616

orcl.__streams_pool_size=536870912

*.audit_file_dest='D:\app\Administrator\admin\orcl\adump'

*.audit_trail='NONE'

*.compatible='11.2.0.4.0'

*.control_files='Z:\app\Administrator\oradata\orcl\control01.ctl','Z:\app\Administrator\oradata\orcl\control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.deferred_segment_creation=FALSE

*.diagnostic_dest='D:\app\Administrator'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=1000

*.log_archive_dest_1='LOCATION=Z:\archive'

*.log_archive_format='ARC%S_%R.%T'

*.memory_target=161061273600

*.nls_language='SIMPLIFIED CHINESE'

*.nls_territory='CHINA'

*.open_cursors=300

*.pga_aggregate_target=21474836480

*.processes=2500

*.remote_login_passwordfile='EXCLUSIVE'

*.sec_case_sensitive_logon=FALSE

*.sessions=2755

*.sga_target=128849018880

*.undo_tablespace='UNDOTBS1'

RUN {

allocate channel c1 type disk;

allocate channel c2 type disk;

sql 'alter system archive log current';

backup current controlfile for standby format='Z:\backup\20150724\control_%U';

BACKUP FORMAT 'Z:\backup\20150724\oradg_%U_%T.bak' skip inaccessible filesperset 5 DATABASE ;

sql 'alter system archive log current';

BACKUP FORMAT 'Z:\backup\20150724\arch_%U_%T.bak' skip inaccessible filesperset 5 ARCHIVELOG ALL;

sql "alter database create standby controlfile as ''Z:\backup\20150724\control01.ctl'' reuse";

release channel c2;

release channel c1;

}

sys/wxqhaey

sqlplus sys/wxqhaey as sysdba

orapwd file="D:\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcl.ORA" password=wxqhaey force=y

oradg11g--oradgphy---oradgss--oradgss

oradg11g.__db_cache_size=125829120

oradg11g.__java_pool_size=4194304

oradg11g.__large_pool_size=4194304

oradg11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

oradg11g.__pga_aggregate_target=83886080

oradg11g.__sga_target=327155712

oradg11g.__shared_io_pool_size=0

oradg11g.__shared_pool_size=184549376

oradg11g.__streams_pool_size=0

audit_file_dest='/u01/app/oracle/admin/oradg11g/adump'

audit_trail='db'

compatible='11.2.0.0.0'

control_files='/u01/app/oracle/oradata//oradg11g/control01.ctl','/u01/app/oracle/flash_recovery_area/oradg11g/control02.ctl'

db_block_size=8192

db_domain=''

rman target sys/lhr@tns_oradgphy auxiliary sys/lhr@tns_oradg11g

duplicate target database

for standby nofilenamecheck

from active database

DORECOVER

spfile

set db_unique_name='oradg11g'

set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=oradg11g valid_for=(ALL_LOGFILES,ALL_ROLES)'

set log_archive_dest_2='SERVICE=tns_oradgphy LGWR ASYNC db_unique_name=oradgphy valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'

set log_archive_dest_3='SERVICE=tns_oradgss LGWR ASYNC db_unique_name=oradgss valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'

set log_archive_dest_4='SERVICE=tns_oradgss LGWR ASYNC db_unique_name=oradgss valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'

set log_archive_dest_state_1='ENABLE'

set log_archive_dest_state_2='ENABLE'

set log_archive_dest_state_3='defer'

set log_archive_dest_state_4='defer'

set standby_file_management='AUTO'

set fal_client='oradg11g'

set fal_server='oradgphy'

set control_files='/u01/app/oracle/oradata/oradg11g/control01.ctl','/u01/app/oracle/flash_recovery_area/oradg11g/control02.ctl'

set db_file_name_convert='oradgphy','oradg11g'

set log_file_name_convert='oradgphy','oradg11g'

set memory_target='200M'

set audit_file_dest='/u01/app/oracle/admin/oradg11g/adump'

;

rman target sys/lhr@tns_oradg11g auxiliary sys/lhr@tns_oradglg

duplicate target database

for standby nofilenamecheck

from active database

DORECOVER

spfile

set db_unique_name='oradglg'

set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=oradglg'

set standby_file_management='AUTO'

set fal_server='oradg11g'

set fal_client='oradglg'

set control_files='/u01/app/oracle/oradata/oradglg/control01.ctl','/u01/app/oracle/oradata/oradglg/control02.ctl'

set db_file_name_convert='oradg11g','oradglg'

set log_file_name_convert='oradg11g','oradglg'

set memory_target='190M'

;

rman target sys/lhr@tns_oradg11g auxiliary sys/lhr@tns_oradgss

duplicate target database

for standby nofilenamecheck

from active database

DORECOVER

spfile

set db_unique_name='oradgss'

set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=oradgss'

set standby_file_management='AUTO'

set fal_server='oradg11g'

set fal_client='oradgss'

set control_files='/u01/app/oracle/oradata/oradgss/control01.ctl','/u01/app/oracle/oradata/oradgss/control02.ctl'

set db_file_name_convert='oradg11g','oradgss'

set log_file_name_convert='oradg11g','oradgss'

set memory_target='190M'

;

duplicate target database

for standby nofilenamecheck

from active database

DORECOVER

spfile

set db_unique_name='orcldg'

set log_archive_dest_1='LOCATION=Z:\archive\orcldg valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=orcldg'

set log_archive_dest_2='service=orcl async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=orcl'

set standby_file_management='AUTO'

set fal_server='orcl'

set fal_client='orcldg'

set control_files='Z:\app\Administrator\oradata\orcldg\control01.ctl','Z:\app\Administrator\oradata\orcldg\control02.ctl'

set db_file_name_convert='orcl','orcldg'

set log_file_name_convert='orcl','orcldg'

set memory_target='161061273600'

set audit_file_dest='D:\app\Administrator\admin\orcldg\adump'

;

alter system set db_unique_name='orcl' scope=spfile;

alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)';

alter system set log_archive_dest_1='LOCATION=Z:\archive db_unique_name=orcl valid_for=(ALL_LOGFILES,ALL_ROLES)';

alter system set log_archive_dest_2='SERVICE=orcldg LGWR ASYNC db_unique_name=orcldg 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='orcldg','orcl' scope=spfile;

alter system set log_file_name_convert='orcldg','orcl' scope=spfile;

alter system set standby_file_management='AUTO';

alter system set fal_server='orcldg';

alter system set fal_client='orcl';

*.log_archive_config='DG_CONFIG=(oradg11g,oradgphy,oradgss,oradgss)'

------------------------------------------------------ 角色切换-------------------------------------------------

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;

---------------- 物理备库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切换

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

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的failover

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

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

------------------------------------------------------------ 归档丢失的恢复

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# >= 40

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

ORDER BY a.THREAD#,

a.sequence#;

SELECT (SELECT MIN(d.CHECKPOINT_CHANGE#) FROM v$datafile d) datafile_scn,

(SELECT MIN(d.CHECKPOINT_CHANGE#)

FROM v$datafile_header d

WHERE rownum = 1) datafile_header_scn,

(SELECT current_scn FROM v$database) current_scn,

(SELECT min(b.NEXT_CHANGE#)

FROM v$archived_log b

WHERE b.SEQUENCE# in (71,45)

AND resetlogs_change# =

(SELECT d.RESETLOGS_CHANGE# FROM v$database d)) NEXT_CHANGE#

FROM dual;

run

{

allocate channel d1 type disk;

allocate channel d2 type disk;

allocate channel d3 type disk;

allocate channel d4 type disk;

backup as compressed backupset incremental from SCN 12232942713886 database format '/archive/standby_new_%d_%T_%U.bak' include current controlfile for standby filesperset=5 tag 'FOR STANDBY new';

release channel d1;

release channel d2;

release channel d3;

release channel d4;

}

---记录备库数据文件地址

col name format a60

set line 9999 pagesize 9999

SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;

cp +DATA/lhrdgwl/controlfile/control01.ctl +DATA/lhrdgwl/controlfile/control01.ctl.ctl_bk

restore standby controlfile to '+DATA/oranetr/controlfile/control01.ctl' from '/archive/standby_ORANET_20160131_0rqsnulk_1_1.bak';

restore standby controlfile to '+DATA/oranetr/controlfile/control02.ctl' from '/archive/standby_ORANET_20160131_0rqsnulk_1_1.bak';

restore standby controlfile to '+DATA/oranetr/controlfile/control03.ctl' from '/archive/standby_ORANET_20160131_0rqsnulk_1_1.bak';

alter database mount;

catalog start with '/archive/';

run

{

allocate channel d1 type disk;

allocate channel d2 type disk;

allocate channel d3 type disk;

allocate channel d4 type disk;

recover DATABASE noredo;

release channel d1;

release channel d2;

release channel d3;

release channel d4;

}

---若主备库的数据文件名不同，则恢复控制文件后需要对数据文件重命名 alter database rename file 'AAA' to 'BBB';

---特别注意：A为原主库数据文件地址，BBB为原物理DG数据文件地址

col name format a60

set line 9999 pagesize 9999

SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;

alter system set standby_file_management=manual;

alter database rename file '+DATA/lhrdg/datafile/undotbs1.307.913547429' to '+DATA/lhrdgwl/datafile/undotbs1.276.913557921';

alter database rename file '+DATA/lhrdg/datafile/users.308.913547429' to '+DATA/lhrdgwl/datafile/users.274.913557925';

alter database rename file '+DATA/lhrdg/datafile/example.288.913547503' to '+DATA/lhrdgwl/datafile/example.277.913557913';

alter database rename file '+DATA/lhrdg/datafile/undotbs2.287.913547649' to '+DATA/lhrdgwl/datafile/undotbs2.275.913557923';

alter system set standby_file_management=auto;

--------重命名数据文件完成后再次执行恢复操作即可，恢复的过程中我们可以通过如下的SQL语句来查看恢复的进度：

SELECT a.USERNAME,

(SELECT upper(nb.OSUSER) FROM v$session nb WHERE nb.SID = a.sid) OSUSER,

(SELECT nb.sid || ',' || nb.SERIAL# || ',' || pr.SPID

FROM v$process pr, v$session nb

WHERE nb.PADDR = pr.ADDR

and nb.sid = a.SID

and nb.SERIAL# = a.SERIAL#) session_info,

a.target,

a.opname,

to_char(a.START_TIME, 'YYYY-MM-DD HH24:MI:SS') start_time,

round(a.SOFAR * 100 / a.TOTALWORK, 2) || '%' AS progress,

(a.TIME_REMAINING) TIME_REMAINING,

(a.sofar || ':' || a.TOTALWORK) sofar_TOTALWORK,

(a.elapsed_seconds) elapsed_seconds,

message message,

(SELECT nb.EVENT FROM V$session_Wait nb WHERE nb.SID = a.SID) wait_event,

(SELECT nb.STATUS FROM v$session nb WHERE nb.SID = a.SID) STATUS

FROM v$session_longops a

WHERE a.time_remaining <> 0

ORDER BY status, a.TIME_REMAINING DESC, a.SQL_ID, a.sid;

alter database add standby logfile thread 1 group 7 size 1073741824 ,group 8 size 1073741824 ,group 9 size 1073741824,group 10 size 1073741824 ;

alter database add standby logfile thread 2 group 11 size 1073741824 ,group 12 size 1073741824 ,group 13 size 1073741824,group 14 size 1073741824 ;

------------------------------------------------------------- DG RAC + RAC

dbca -silent -createDatabase -templateName General_Purpose.dbc \

-gdbname LHRDG -sid LHRDGZK \

-sysPassword lhr -systemPassword lhr \

-datafileDestination 'DATA/' -redoLogFileSize 50 -recoveryAreaDestination '/arch' \

-storageType ASM -asmsnmpPassword lhr -diskGroupName 'DATA' -responseFile NO_VALUE \

-characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -automaticMemoryManagement true \

-totalMemory 1024 -nodeinfo ZFXDESKDB3,ZFXDESKDB4

set line 9999

select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from gv$database;

alter database force logging;

alter system set log_archive_dest_1='LOCATION=/arch' sid='*';

host srvctl stop database -d LHRDG -o immediate

host srvctl status database -d LHRDG

host srvctl start database -d LHRDG -o mount

alter database archivelog;

select * from v$standby_log;

select group#,THREAD#,MEMBERS from v$log ;

alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;

alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M ;

set line 9999

select GROUP# ,STATUS , TYPE , MEMBER from v$logfile;

alter system set db_unique_name='LHRDGZK' scope=spfile;

alter system set log_archive_config='DG_CONFIG=(LHRDGZK,LHRDGWL)' sid='*';

alter system set log_archive_dest_1='LOCATION=/arch db_unique_name=LHRDGZK valid_for=(ALL_LOGFILES,ALL_ROLES)' sid='*';

alter system set log_archive_dest_2='SERVICE=TNS_LHRDGWL LGWR ASYNC db_unique_name=LHRDGWL valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)' sid='*';

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

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

alter system set log_archive_max_processes=4 sid='*';

alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;

--Add below item when DB turn to standby role

alter system set db_file_name_convert='LHRDGWL','LHRDGZK' scope=spfile;

alter system set log_file_name_convert='LHRDGWL','LHRDGZK' scope=spfile;

alter system set standby_file_management='AUTO' sid='*';

alter system set fal_server='TNS_LHRDGWL' sid='*';

alter system set fal_client='TNS_LHRDGZK' sid='*';

vi /oracle/app/11.2.0/grid/network/admin/listener.ora

LISTENER_LHRDG =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.66)(PORT = 1523))

)

SID_LIST_LISTENER_LHRDG =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = LHRDG)

(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)

(SID_NAME = LHRDGWL2)

)

more $ORACLE_HOME/network/admin/listener.ora

lsnrctl start LISTENER_LHRDG

lsnrctl status LISTENER_LHRDG

[ZFXDESKDB3:grid]:/home/grid>srvctl add listener -l LISTENER_LHRDG -p 1523 -o $ORACLE_HOME

[ZFXDESKDB3:grid]:/home/grid>srvctl stop listener -l LISTENER_LHRDG

[ZFXDESKDB3:grid]:/home/grid>srvctl start listener -l LISTENER_LHRDG

l $ORACLE_HOME/dbs/orapwLHRDG*

scp /oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGZK1 oracle@22.188.194.32:/oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGZK2

scp /oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGZK1 oracle@22.188.194.64:/oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGWL1

scp /oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGZK1 oracle@22.188.194.66:/oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGWL1

vi $ORACLE_HOME/network/admin/tnsnames.ora

TNS_LHRDGZK =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.31)(PORT = 1523))

(ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.32)(PORT = 1523))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = LHRDG)

)

TNS_LHRDGWL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.64)(PORT = 1523))

(ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.66)(PORT = 1523))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = LHRDG)

)

mkdir -p $ORACLE_BASE/admin/LHRDGWL/adump

export ORACLE_SID=LHRDGWL1

echo 'db_name=LHRDG' > $ORACLE_HOME/dbs/initLHRDGWL1.ora

sqlplus / as sysdba

startup nomount pfile=?/dbs/initLHRDGWL1.ora

--rman target sys/lhr@TNS_LHRDGPRI auxiliary sys/lhr@TNS_LHRDGPHY log=/tmp/dupl_log.log DEBUG trace=/tmp/dupl_debug.trc

rman target sys/lhr@TNS_LHRDGZK auxiliary sys/lhr@TNS_LHRDGWL

duplicate target database

for standby nofilenamecheck

from active database

DORECOVER

spfile

set db_unique_name='LHRDGWL'

set log_archive_dest_1='LOCATION=/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=LHRDGWL'

set log_archive_dest_2='service=TNS_LHRDGZK async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=LHRDGZK'

set standby_file_management='AUTO'

set fal_server='TNS_LHRDGZK'

set fal_client='TNS_LHRDGWL'

set control_files='+DATA/LHRDGWL/controlfile/control01.ctl','+DATA/LHRDGWL/controlfile/control02.ctl'

set db_file_name_convert='LHRDGZK','LHRDGWL'

set log_file_name_convert='LHRDGZK','LHRDGWL'

set memory_target='2G'

set audit_file_dest='/oracle/app/oracle/admin/LHRDGWL/adump'

set db_create_file_dest = '+DATA'

set instance_number = '1'

set cluster_database='false'

;

create pfile='/tmp/aa.txt' from spfile;

修改 /tmp/aa.txt

create spfile='+DATA/LHRDGWL/PARAMETERFILE/spfileLHRDGWL.ora' from pfile='/tmp/aa.txt';

echo "SPFILE='+DATA/LHRDGWL/PARAMETERFILE/spfileLHRDGWL.ora'" > $ORACLE_HOME/dbs/initLHRDGWL1.ora

more $ORACLE_HOME/dbs/initDGPHY1.ora

删除原spfile文件：/oracle/app/oracle/product/11.2.0/db/dbs/spfileLHRDGWL1.ora

*.cluster_database=true

DGPHY2.instance_number=2

DGPHY1.instance_number=1

DGPHY2.thread=2

DGPHY1.thread=1

DGPHY2.undo_tablespace='UNDOTBS2'

DGPHY1.undo_tablespace='UNDOTBS1'

----将备库加入srvctl中

srvctl stop database -d LHRDG -o immediate

srvctl status database -d LHRDG

srvctl start database -d LHRDG -o mount

srvctl add db -d lhrrac1DG -c SINGLE -o /u01/app/oracle/product/11.2.0/dbhome_1 -p '+datadg/LHRRAC1DG/PARAMETERFILE/spfile.322.1014551675' -r physical_standby -n lhrrac1 -x raclhr-11gR2-N1

srvctl add database -d LHRDG -c RAC -o /oracle/app/oracle/product/11.2.0/db -p '+DATA/LHRDGWL/PARAMETERFILE/spfileLHRDGWL.ora' -r physical_standby -n LHRDG

srvctl add instance -d LHRDG -i LHRDGWL1 -n ZFXDESKDB1

srvctl add instance -d LHRDG -i LHRDGWL2 -n ZFXDESKDB2

srvctl stop database -d LHRDG

srvctl status database -d LHRDG

srvctl start database -d LHRDG

srvctl remove database -d LHRDG

srvctl config database -d LHRDG -a

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

select sequence#,name,applied from v$archived_log;

col name format a100

select sequence#,name,archived,applied from v$archived_log order by name,sequence#;

alter database recover managed standby database cancel;

alter database open;

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

col name format a10

select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

检查是否存在GAP

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

SELECT 'alter database register or replace logical logfile ''/u04/arch/WDSTD/log_1_' || a ||

' _666200636.arc'';' logical_dg,

'alter database register or replace logfile ''/u04/arch/WDSTD/log_1_' || a ||

' _666200636.arc'';' physical_dg

FROM (SELECT level a FROM dual connect by level <= 2000)

where a >= 1980 ;

------------------------------------------------------------- DG RAC + single db

dbca -silent -createDatabase -templateName General_Purpose.dbc \

-gdbname LHRDG -sid LHRDGPRI \

-sysPassword lhr -systemPassword lhr \

-datafileDestination 'DATA/' -redoLogFileSize 50 -recoveryAreaDestination '/arch' \

-storageType ASM -asmsnmpPassword lhr -diskGroupName 'DATA' -responseFile NO_VALUE \

-characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -automaticMemoryManagement true \

-totalMemory 1024 -nodeinfo ZFXDESKDB3,ZFXDESKDB4

set line 9999

select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from gv$database;

alter database force logging;

alter system set log_archive_dest_1='LOCATION=/arch' sid='*';

host srvctl stop database -d LHRDGPRI -o immediate

host srvctl status database -d LHRDGPRI

host srvctl start database -d LHRDGPRI -o mount

alter database archivelog;

select * from v$standby_log;

select group#,THREAD#,MEMBERS from v$log ;

alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;

alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M ;

set line 9999

select GROUP# ,STATUS , TYPE , MEMBER from v$logfile;

alter system set db_unique_name='LHRDGPRI' scope=spfile;

alter system set log_archive_config='DG_CONFIG=(LHRDGPRI,LHRDGPHY)' sid='*';

alter system set log_archive_dest_1='LOCATION=/arch db_unique_name=LHRDGPRI valid_for=(ALL_LOGFILES,ALL_ROLES)' sid='*';

alter system set log_archive_dest_2='SERVICE=TNS_LHRDGPHY LGWR ASYNC db_unique_name=LHRDGPHY valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)' sid='*';

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

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

alter system set log_archive_max_processes=4 sid='*';

alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;

--Add below item when DB turn to standby role

alter system set db_file_name_convert='LHRDGPHY','LHRDGPRI' scope=spfile;

alter system set log_file_name_convert='LHRDGPHY','LHRDGPRI' scope=spfile;

alter system set standby_file_management='AUTO' sid='*';

alter system set fal_server='TNS_LHRDGPHY' sid='*';

alter system set fal_client='TNS_LHRDGPRI' sid='*';

LISTENER_LHRDG =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.66)(PORT = 1523))

)

SID_LIST_LISTENER_LHRDG =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = LHRDGPRI)

(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)

(SID_NAME = LHRDGPHY2)

)

more $ORACLE_HOME/network/admin/listener.ora

lsnrctl start LISTENER_LHRDG

lsnrctl status LISTENER_LHRDG

l $ORACLE_HOME/dbs/orapwLHRDG*

scp /oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGPRI1 oracle@22.188.194.32:/oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGPRI2

scp /oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGPRI1 oracle@22.188.194.64:/oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGPHY1

scp /oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGPRI1 oracle@22.188.194.66:/oracle/app/oracle/product/11.2.0/db/dbs/orapwLHRDGPHY2

vi $ORACLE_HOME/network/admin/tnsnames.ora

TNS_LHRDGPRI =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.31)(PORT = 1523))

(ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.32)(PORT = 1523))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = LHRDGPRI)

)

TNS_LHRDGPHY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST