Oracle 19c RAC 到单实例 ADG 实战：Active Duplicate、双 Thread Redo 与归档清理

37962946;OCW RELEASE UPDATE 19.28.0.0.0
37960098;Database Release Update : 19.28.0.0.250715

CMSJS RAC primary
  sjdb1 / cmsjs1 / thread 1 ──┐
                              ├── ASYNC redo ──> cmsjsdg2 / cmsjsyddg
  sjdb2 / cmsjs2 / thread 2 ──┘                    ├─ thread 1 的 SRL
                                                  ├─ thread 2 的 SRL
                                                  └─ MRP0 应用 redo
                                                       ↓
                                               READ ONLY WITH APPLY

set lines 300 pages 100

select name, db_unique_name, dbid, cdb, database_role, open_mode,
       log_mode, force_logging, flashback_on, protection_mode,
       switchover_status
from v$database;

select inst_id, instance_name, host_name, thread#, status, version
from gv$instance
order by inst_id;

show parameter compatible
show parameter spfile
show parameter remote_login_passwordfile
show parameter dg_broker_start
show parameter db_recovery_file_dest
show parameter log_archive_dest

NAME              CMSJS
DB_UNIQUE_NAME    cmsjs
DATABASE_ROLE     PRIMARY
OPEN_MODE         READ WRITE
LOG_MODE          ARCHIVELOG
FORCE_LOGGING     NO
FLASHBACK_ON      YES
PROTECTION_MODE   MAXIMUM PERFORMANCE

INST_ID  INSTANCE_NAME  HOST_NAME  THREAD#  STATUS
1        cmsjs1         sjdb1      1        OPEN
2        cmsjs2         sjdb2      2        OPEN

alter database force logging;
select force_logging from v$database;

select thread#, group#, round(bytes / 1024 / 1024) mb, members, status
from v$log
order by thread#, group#;

select group#, type, member from v$logfile order by group#, member;

alter database add standby logfile thread 1 group 11 ('+DATA','+DATA') size 4G;
alter database add standby logfile thread 1 group 12 ('+DATA','+DATA') size 4G;
alter database add standby logfile thread 1 group 13 ('+DATA','+DATA') size 4G;
alter database add standby logfile thread 1 group 14 ('+DATA','+DATA') size 4G;
alter database add standby logfile thread 2 group 15 ('+DATA','+DATA') size 4G;
alter database add standby logfile thread 2 group 16 ('+DATA','+DATA') size 4G;
alter database add standby logfile thread 2 group 17 ('+DATA','+DATA') size 4G;
alter database add standby logfile thread 2 group 18 ('+DATA','+DATA') size 4G;

select thread#, group#, round(bytes / 1024 / 1024) mb, status
from v$standby_log
order by thread#, group#;

alter system set log_archive_config='DG_CONFIG=(cmsjs,cmsjsyddg)'
  scope=both sid='*';
alter system set standby_file_management='AUTO' scope=both sid='*';

export ORACLE_SID=cmsjsyddg
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1
export PATH="$ORACLE_HOME/bin:$PATH"

install -d -o oracle -g oinstall -m 750 \
  /u01/oradata/cmsjsyddg \
  /u01/fra/cmsjsyddg \
  /u01/app/oracle/admin/cmsjsyddg/adump

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cmsjsdg2)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = cmsjsyddg)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1)
      (SID_NAME = cmsjsyddg)
    )
  )

lsnrctl start
lsnrctl status
lsnrctl services

Service "cmsjsyddg" has 1 instance(s).
Instance "cmsjsyddg", status UNKNOWN

CMSJS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.87.180.14)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cmsjs)
    )
  )

CMSJSYDDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cmsjsdg2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cmsjsyddg)
    )
  )

tnsping CMSJS
tnsping CMSJSYDDG

tnsping CMSJSYDDG

+DATA/CMSJS/PASSWORD/pwdcmsjs.256.1243102711

asmcmd pwcopy \
  +DATA/CMSJS/PASSWORD/pwdcmsjs.256.1243102711 \
  /tmp/orapwcmsjs

chown oracle:oinstall /tmp/orapwcmsjs
chmod 600 /tmp/orapwcmsjs

scp /tmp/orapwcmsjs oracle@cmsjsdg2:/tmp/

mv /tmp/orapwcmsjs "$ORACLE_HOME/dbs/orapwcmsjsyddg"
chmod 600 "$ORACLE_HOME/dbs/orapwcmsjsyddg"

*.db_name='cmsjs'
*.db_unique_name='cmsjsyddg'
*.compatible='19.0.0'
*.cluster_database=FALSE
*.remote_login_passwordfile='EXCLUSIVE'

*.memory_target=0
*.memory_max_target=0
*.sga_target=64G
*.sga_max_size=64G
*.pga_aggregate_target=16G
*.pga_aggregate_limit=32G
*.processes=3000
*.sessions=4522
*.use_large_pages='FALSE'

*.audit_file_dest='/u01/app/oracle/admin/cmsjsyddg/adump'
*.diagnostic_dest='/u01/app/oracle'
*.db_create_file_dest='/u01/oradata/cmsjsyddg'
*.db_create_online_log_dest_1='/u01/oradata/cmsjsyddg'
*.db_create_online_log_dest_2='/u01/fra/cmsjsyddg'
*.db_recovery_file_dest='/u01/fra/cmsjsyddg'
*.db_recovery_file_dest_size=1000G

*.standby_file_management='AUTO'
*.log_archive_config='DG_CONFIG=(cmsjs,cmsjsyddg)'
*.fal_server='CMSJS'

sqlplus / as sysdba

startup nomount pfile='/u01/app/oracle/product/19.3.0/db_1/dbs/initcmsjsyddg.ora';
show parameter spfile
select instance_name, status from v$instance;

sqlplus 'sys@CMSJS as sysdba'

select name, db_unique_name, database_role from v$database;

sqlplus 'sys@CMSJSYDDG as sysdba'

select instance_name, status from v$instance;

select sid, name, value
from v$spparameter
where isspecified = 'TRUE'
  and (name in (
         'db_file_name_convert', 'log_file_name_convert',
         'control_files', 'remote_listener', 'local_listener',
         'memory_target', 'memory_max_target',
         'instance_number', 'thread', 'undo_tablespace')
       or name like 'log_archive_dest%'
       or name like 'db_create_online_log_dest%')
order by name, sid;

rman

CONNECT TARGET sys@CMSJS;
CONNECT AUXILIARY sys@CMSJSYDDG;

RUN {
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
  ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
  ALLOCATE CHANNEL c3 DEVICE TYPE DISK;
  ALLOCATE CHANNEL c4 DEVICE TYPE DISK;

  ALLOCATE AUXILIARY CHANNEL a1 DEVICE TYPE DISK;
  ALLOCATE AUXILIARY CHANNEL a2 DEVICE TYPE DISK;
  ALLOCATE AUXILIARY CHANNEL a3 DEVICE TYPE DISK;
  ALLOCATE AUXILIARY CHANNEL a4 DEVICE TYPE DISK;

  DUPLICATE TARGET DATABASE
    FOR STANDBY
    FROM ACTIVE DATABASE
    USING COMPRESSED BACKUPSET
    DORECOVER
    SPFILE
      SET db_unique_name='cmsjsyddg'
      SET cluster_database='FALSE'
      SET cluster_database_instances='1'
      SET remote_login_passwordfile='EXCLUSIVE'
      SET memory_target='0'
      SET memory_max_target='0'
      SET sga_target='64G'
      SET sga_max_size='64G'
      SET pga_aggregate_target='16G'
      SET pga_aggregate_limit='32G'
      SET processes='3000'
      SET sessions='4522'
      SET use_large_pages='FALSE'
      SET audit_file_dest='/u01/app/oracle/admin/cmsjsyddg/adump'
      SET diagnostic_dest='/u01/app/oracle'
      SET db_create_file_dest='/u01/oradata/cmsjsyddg'
      SET db_create_online_log_dest_1='/u01/oradata/cmsjsyddg'
      SET db_create_online_log_dest_2='/u01/fra/cmsjsyddg'
      SET db_recovery_file_dest='/u01/fra/cmsjsyddg'
      SET db_recovery_file_dest_size='1000G'
      SET standby_file_management='AUTO'
      SET log_archive_config='DG_CONFIG=(cmsjs,cmsjsyddg)'
      SET log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cmsjsyddg'
      SET log_archive_dest_state_1='ENABLE'
      SET log_archive_dest_2='SERVICE=CMSJS ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=cmsjs REOPEN=15'
      SET log_archive_dest_state_2='DEFER'
      SET fal_server='CMSJS'
      SET undo_tablespace='UNDOTBS1'
      RESET remote_listener
      RESET local_listener
      RESET control_files
    NOFILENAMECHECK;
}

Finished Duplicate Db at 2026-09-05 12:52:37

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

show parameter spfile
show parameter cluster_database
show parameter db_create_file_dest
show parameter db_recovery_file_dest
show parameter fal_server
show parameter remote_listener

select name from v$controlfile;
select file#, name from v$datafile order by file#;
select group#, type, member from v$logfile order by group#, member;
select thread#, group#, round(bytes / 1024 / 1024) mb, status
from v$standby_log order by thread#, group#;

主库数据文件：+DATA/CMSJS/DATAFILE/...
备库数据文件：/u01/oradata/cmsjsyddg/CMSJSYDDG/datafile/...

控制文件副本 1：/u01/oradata/cmsjsyddg/CMSJSYDDG/controlfile/...
控制文件副本 2：/u01/fra/cmsjsyddg/CMSJSYDDG/controlfile/...

alter system set log_archive_dest_1=
  'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cmsjsyddg'
  scope=both;
alter system set log_archive_dest_state_1=ENABLE scope=both;

alter system set log_archive_dest_2=
  'SERVICE=CMSJS ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=cmsjs REOPEN=15'
  scope=both;
alter system set log_archive_dest_state_2=ENABLE scope=both;
alter system set fal_server='CMSJS' scope=both;

alter database recover managed standby database disconnect from session;

alter system set log_archive_dest_1=
  'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cmsjs'
  scope=both sid='*';

alter system set log_archive_dest_2=
  'SERVICE=CMSJSYDDG ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=cmsjsyddg REOPEN=15'
  scope=both sid='*';
alter system set log_archive_dest_state_2=ENABLE scope=both sid='*';
alter system set fal_server='CMSJSYDDG' scope=both sid='*';

select inst_id, name, value
from gv$parameter
where name in ('log_archive_config', 'log_archive_dest_2',
               'log_archive_dest_state_2', 'fal_server')
order by name, inst_id;

select protection_mode, protection_level from v$database;

alter database recover managed standby database cancel;
alter database open read only;
alter database recover managed standby database disconnect from session;

select name, db_unique_name, database_role, open_mode from v$database;

NAME              CMSJS
DB_UNIQUE_NAME    cmsjsyddg
DATABASE_ROLE     PHYSICAL STANDBY
OPEN_MODE         READ ONLY WITH APPLY

set lines 300 pages 100
col destination for a25
col error for a80
col database_mode for a20
col recovery_mode for a40

select inst_id, dest_id, status, database_mode, recovery_mode,
       destination, error
from gv$archive_dest_status
where dest_id = 2
order by inst_id;

INST_ID  STATUS  DATABASE_MODE   RECOVERY_MODE
1        VALID   OPEN_READ-ONLY  MANAGED REAL TIME APPLY WITH QUERY
2        VALID   OPEN_READ-ONLY  MANAGED REAL TIME APPLY WITH QUERY

select instance_name, thread# from v$instance;
alter system switch logfile;

select thread#, group#, sequence#, status, archived
from v$standby_log
order by thread#, group#;

THREAD#  GROUP#  SEQUENCE#  STATUS
1        11      160        ACTIVE
2        15      50         ACTIVE

select name, role, action, thread#, sequence#
from v$dataguard_process
where name in ('RFS', 'MRP0')
order by name, thread#;

select process, status, thread#, sequence#
from v$managed_standby
where process in ('RFS', 'MRP0')
order by process, thread#;

set lines 250 pages 100
col name for a20
col value for a25
col time_computed for a25
col datum_time for a25

select name, value, time_computed, datum_time
from v$dataguard_stats
where name in ('transport lag', 'apply lag');

transport lag   +00 00:00:00
apply lag       +00 00:00:00

select thread#,
       max(sequence#) last_received,
       max(case when applied = 'YES' then sequence# end) last_applied,
       max(sequence#) -
       max(case when applied = 'YES' then sequence# end) archived_seq_delta
from v$archived_log
where registrar = 'RFS'
  and resetlogs_change# = (select resetlogs_change# from v$database)
group by thread#
order by thread#;

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

mkdir -p /home/oracle/scripts /home/oracle/log

#!/bin/bash
# 默认只预览；核对候选清单后，使用 --delete 执行删除。
set -Eeuo pipefail
umask 077
SCRIPT_VERSION=1.1

MODE=${1:---preview}
case "$MODE" in
    --preview|--delete) ;;
    *) printf 'Usage: %s [--preview|--delete]\n' "$0" >&2; exit 2 ;;
esac

export ORACLE_SID=cmsjsyddg
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1
export PATH="$ORACLE_HOME/bin:/usr/bin:/bin"
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
unset TWO_TASK ORACLE_PDB_SID

LOG_DIR=/home/oracle/log
mkdir -p "$LOG_DIR"
LOG_FILE="$LOG_DIR/clean_arch_${ORACLE_SID}_$(date +%Y%m%d).log"
exec >>"$LOG_FILE" 2>&1

# 防止同一脚本重叠运行；此锁不会阻止数据库角色切换。
exec 9>"$LOG_DIR/.clean_arch_${ORACLE_SID}.lock"
if ! flock -n 9; then
    printf '%s Another cleanup is running.\n' "$(date '+%F %T')"
    exit 75
fi

TMP_DIR=$(mktemp -d "$LOG_DIR/.clean_arch_${ORACLE_SID}.XXXXXX")
cleanup() {
    rc=$?
    trap - EXIT
    rm -f -- "$TMP_DIR/role.log" "$TMP_DIR/candidates.rman" "$TMP_DIR/rman.log"
    rmdir -- "$TMP_DIR"
    printf '%s Cleanup ended; exit=%s\n' "$(date '+%F %T')" "$rc"
    exit "$rc"
}
trap cleanup EXIT
printf '\n%s Cleanup started; version=%s; mode=%s\n' "$(date '+%F %T')" "$SCRIPT_VERSION" "$MODE"

check_identity() {
    if ! sqlplus -L -s / as sysdba >"$TMP_DIR/role.log" 2>&1 <<'SQL'
whenever oserror exit 2
whenever sqlerror exit 1
set heading off feedback off pagesize 0 verify off echo off
select case
         when database_role = 'PHYSICAL STANDBY'
          and lower(db_unique_name) = 'cmsjsyddg'
         then 'IDENTITY_OK'
         else 'IDENTITY_MISMATCH'
       end
from v$database;
exit success
SQL
    then
        cat "$TMP_DIR/role.log"
        return 1
    fi
    # 只接受预期输出，SQL*Plus 自身的 SP2 错误也不会被当成成功。
    if [[ "$(sed '/^[[:space:]]*$/d; s/^[[:space:]]*//; s/[[:space:]]*$//' "$TMP_DIR/role.log")" != 'IDENTITY_OK' ]]; then
        cat "$TMP_DIR/role.log"
        printf 'Database identity or role check failed.\n'
        return 1
    fi
}

check_identity

# 只生成完整文件名，不使用 LIKE；下划线和百分号不会扩大匹配范围。
# 同名活动记录若不满足条件，则跳过该文件；说明留在 Shell 层。
if ! sqlplus -L -s / as sysdba >"$TMP_DIR/candidates.rman" 2>&1 <<'SQL'
whenever oserror exit 2
whenever sqlerror exit 1
set heading off feedback off pagesize 0 verify off echo off
set linesize 32767 trimout on trimspool on tab off define off
select distinct 'delete noprompt archivelog ''' || a.name || ''';'
from v$archived_log a
where a.registrar = 'RFS'
  and a.applied = 'YES'
  and a.archived = 'YES'
  and a.deleted = 'NO'
  and a.status = 'A'
  and a.name is not null
  and a.completion_time < sysdate - 7
  and not exists (
      select 1
      from v$archived_log b
      where b.name = a.name
        and b.deleted = 'NO'
        and b.status = 'A'
        and (nvl(b.registrar, '?') <> 'RFS'
          or nvl(b.applied, '?') <> 'YES'
          or nvl(b.archived, '?') <> 'YES'
          or b.completion_time is null
          or b.completion_time >= sysdate - 7)
  )
order by 1;
exit success
SQL
then
    cat "$TMP_DIR/candidates.rman"
    exit 1
fi

# 拒绝诊断文本、换行文件名和含单引号的文件名，避免生成异常 RMAN 命令。
if grep -Ev "^(delete noprompt archivelog '[^']+';|[[:space:]]*)$" "$TMP_DIR/candidates.rman"; then
    printf 'Unexpected candidate output; no RMAN deletion was started.\n'
    exit 1
fi
ARCH_COUNT=$(grep -c '^delete noprompt archivelog ' "$TMP_DIR/candidates.rman" || true)
printf 'Eligible file names: %s\n' "$ARCH_COUNT"
cat "$TMP_DIR/candidates.rman"

# 只轮转脚本日志，不操作归档目录。
find "$LOG_DIR" -maxdepth 1 -type f \
    -name "clean_arch_${ORACLE_SID}_*.log" -mtime +90 -delete

if [[ "$MODE" == '--preview' || "$ARCH_COUNT" -eq 0 ]]; then
    printf 'Preview completed, or no eligible file was found.\n'
    exit 0
fi

# 缩小检查与删除之间的窗口；不能替代切换期间暂停调度。
check_identity
if rman target / cmdfile="$TMP_DIR/candidates.rman" >"$TMP_DIR/rman.log" 2>&1; then
    RMAN_RC=0
else
    RMAN_RC=$?
fi
cat "$TMP_DIR/rman.log"
if [[ "$RMAN_RC" -ne 0 ]]; then
    exit "$RMAN_RC"
fi

# RMAN 可能跳过受删除策略保护的文件；发现诊断码时要求检查日志。
if grep -Eq '(RMAN-|ORA-)[0-9]{5}' "$TMP_DIR/rman.log"; then
    printf 'Oracle/RMAN diagnostic messages were found; inspect the log.\n'
    exit 3
fi

printf 'RMAN command file completed. Check its output for actual deleted files.\n'

chmod 750 /home/oracle/scripts/clean_arch_cmsjsyddg.sh
bash -n /home/oracle/scripts/clean_arch_cmsjsyddg.sh
/home/oracle/scripts/clean_arch_cmsjsyddg.sh --preview
cat /home/oracle/log/clean_arch_cmsjsyddg_$(date +%Y%m%d).log

/home/oracle/scripts/clean_arch_cmsjsyddg.sh --delete

crontab -e