Oracle19c测试环境静默快速搭建可参看：
https://blog.csdn.net/bjmt_08/article/details/125561013
ADG的切换注意点：
1.确定需要故障切换操作？
1.如果可能，MOUNT主数据库并刷新所有未发送的归档和当前重做日志从主数据库到备用数据库。
如果这个操作成功，即使主数据库不处于零数据丢失的数据保护模式下，也可以实现零数据丢失的故障转移。
确保在目标备用数据库上激活重做应用。
MOUNT但不要打开主数据库。如果主数据库无法MOUNT，请进行第2步。
在主数据库上执行以下 SQL 语句：
SQL> ALTER SYSTEM FLUSH REDO TO target_db_name;
对于 target_db_name，请指定要从主数据库刷新重做的目标备用数据库的 DB_UNIQUE_NAME。
这个语句将所有未发送的重做日志从主数据库刷新到备用数据库，并等待这些重做日志被应用到备用数据库。
如果这个语句无误完成，请进行第5步。如果语句完成时出现任何错误，或者因为不能再等待语句完成而必须停止，继续进行第2步。
2.验证备用数据库是否拥有每个主数据库重做线程的最新归档重做日志文件。
为此，在目标备用数据库上查询 V$ARCHIVED_LOG 视图，以获取每个重做线程的最高日志序列号。
例如：
SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) -
OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
THREAD LAST
---------- ----------
1 100
如果可能，如果备用数据库上不存在，将每个主数据库重做线程的最新归档重做日志文件复制到备用数据库，并注册它。这必须为每个重做线程完成。
例如：
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
3.在目标备用数据库上查询 V$ARCHIVE_GAP 视图，以确定目标备用数据库上是否存在任何重做GAP。
例如：
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
1 90 92
在这个例子中，GAP包括了线程 1 的序列号为 90、91 和 92 的归档重做日志文件。
如果可能，从主数据库复制任何缺失的归档重做日志文件到目标备用数据库，并在目标备用数据库上注册它们。这必须为每个重做线程完成。
例如：
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
4.重复第3步，直到所有GAP都被解决。（第3步执行的查询仅显示最高GAP的信息，所以在解决一个GAP后，你必须重复查询，直到不再返回任何行。）
如果在执行第2步到第4步后，你无法解决归档重做日志文件中的所有GAP（例如，因为你无法访问托管失败主数据库的系统），那么在故障转移期间将发生一些数据丢失。
5.在目标备用数据库上执行以下 SQL 语句以停止重做应用：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
------------------------------------------------------
6.登录备库adgiihdb
oracle用户执行：
连接备库sqlplus / as sysdba
备库执行：ALTER DATABASE FAILOVER TO adgiihdb;（计划性无数据丢失）
ALTER DATABASE OPEN;
show pdbs;
如果失败，则尝试激活备库：
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;（非计划性可能数据丢失）
ALTER DATABASE OPEN;
后续备库需重建。
2.修改备库的IP为生产：
此时一体机上的生产ip已经不可用，启用生产IP到备机上：
root用户执行：
ifconfig team0:1 192.168.19.194 netmask 255.255.254.0 up
arping -I team0 -b -s 192.168.19.194 192.168.19.254 -c 4
参考示例：oracle用户连接验证：
[root@oracle19c ~]# ifconfig eth0:1 192.168.207.166 netmask 255.255.255.0 up
[root@oracle19c ~]# arping -I eth0 -b -s 192.168.207.166 192.168.207.1 -c 4
ARPING 192.168.207.1 from 192.168.207.166 eth0
Unicast reply from 192.168.207.1 [00:25:9E:A4:49:41] 3.888ms
Unicast reply from 192.168.207.1 [00:25:9E:A4:49:41] 4.903ms
Unicast reply from 192.168.207.1 [00:25:9E:A4:49:41] 3.903ms
Unicast reply from 192.168.207.1 [00:25:9E:A4:49:41] 4.109ms
Sent 4 probes (4 broadcast(s))
Received 4 response(s)
[root@oracle19c ~]# ip a|grep 207.1
inet 192.168.207.164/24 brd 192.168.207.255 scope global noprefixroute eth0
inet 192.168.207.166/24 brd 192.168.207.255 scope global secondary eth0:1
[root@oracle19c ~]# su - oracle
上一次登录：二 3月 19 09:32:10 CST 2024pts/0 上
[oracle@oracle19c ~]$ sqlplus jyc/jyc@192.168.207.166:1521/jyc
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 19 10:24:28 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Tue Mar 19 2024 10:19:49 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select * from t3;
ID
----------
1
2
2
SQL> insert into t3 values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t3;
ID
----------
1
2
2
3
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
^[[A[oracle@oracle19c ~]$ sqlplus jyc/jyc@192.168.207.164:1521/jyc
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 19 10:25:15 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Tue Mar 19 2024 10:24:28 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> insert into t3 values(4);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t3;
ID
----------
1
2
2
3
4
如果不生效，则重启监听：（注意监听的host需要是主机名，不是IP，一般可以不执行重启监听）
lsnrctl stop
lsnrctl start
3.官方切换参考：
3.1正常切换操作：
注意点：
计划性的正常切换只能在主库执行：
ALTER DATABASE SWITCHOVER TO target_db_name [FORCE] [VERIFY];
[oracle@oracle19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 18 14:28:52 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster_name string
cell_offloadgroup_name string
db_file_name_convert string /u01/app/oracle/oradata/DGORCL
, /u01/app/oracle/oradata/ORCL
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string /u01/app/oracle/oradata/DGORCL
, /u01/app/oracle/oradata/ORCL
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert string
processor_group_name string
service_names string orcl
SQL> ALTER DATABASE SWITCHOVER TO dgorcl verify;
Database altered.
SQL> ALTER DATABASE SWITCHOVER TO dgorcl;
Database altered.
SQL> select database_role,open_mode from v$database;
select database_role,open_mode from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 8891
Session ID: 9 Serial number: 32671
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oracle19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 18 14:30:30 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 3221223192 bytes
Fixed Size 9139992 bytes
Variable Size 704643072 bytes
Database Buffers 2499805184 bytes
Redo Buffers 7634944 bytes
Database mounted.
Database opened.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 JYC MOUNTED
SQL> alter pluggable database all open instances=all;
Pluggable database altered.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 JYC READ ONLY NO
切换完成后，新主库自动到mount模式，需要手动重启库。备库则在shutdown的状态，需要重启到恢复模式：
[oracle@dgjyc ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 18 14:30:17 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY MOUNTED
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 JYC MOUNTED
SQL> alter database open;
Database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 JYC MOUNTED
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 JYC MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 JYC READ WRITE NO
SQL> alter system switch logfile;
System altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 JYC READ WRITE NO
SQL> alter session set container=jyc;
Session altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/jyc/system01.dbf
/u01/app/oracle/oradata/ORCL/jyc/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/jyc/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/jyc/users01.dbf
/u01/app/oracle/oradata/DGORCL/jyc/users02.dbf
/u01/app/oracle/oradata/DGORCL/jyc/test01.dbf
6 rows selected.
SQL> create tablespace data datafile '/u01/app/oracle/oradata/DGORCL/jyc/data01.dbf' size 10M;
Tablespace created.
SQL> alter pluggable database all save state;
alter pluggable database all save state
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
SQL> conn / as sysdba
Connected.
SQL> alter pluggable database all save state;
Pluggable database altered.
SQL>
3.2灾难切换备库为主库：
ALTER DATABASE FAILOVER TO dgorcl;
注意此时备库需要重建。
ADG的快速搭建：
基本步骤如下：
1.主库：检查归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 21
Next log sequence to archive 22
Current log sequence 22
SQL> alter database FORCE LOGGING;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
SQL> show parameter db_recovery_file_dest;
SQL> !mkdir -p /u01/app/oracle/reco
SQL> show parameter reco;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
db_recovery_file_dest string
db_recovery_file_dest_size big integer 40G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/reco' scope=both;
System altered.
SQL> show parameter reco;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
db_recovery_file_dest string /u01/app/oracle/reco
db_recovery_file_dest_size big integer 40G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
remote_recovery_file_dest string
2.主库：增加standby_log
[oracle@~]$ more .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19c
export PATH=$PATH:$ORACLE_HOME/bin:/usr/local/bin
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/network/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib
[oracle@rac1 ~]$ echo $ORACLE_SID
orcl
如果不是orcl
则[oracle@rac1 ~]$ export ORACLE_SID=orcl
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 30 14:30:52 2020
Version 19.9.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
SQL> archive log list;
SQL> set line 160
SQL> set wrap off
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
1 1 21 209715200 512 1 YES INACTIVE 3782017 2020-07-29 12:22:29 3964886 2020-07-30 13:51:03
2 1 22 209715200 512 1 NO CURRENT 3964886 2020-07-30 13:51:03 9.2954E+18
3 2 9 209715200 512 1 YES INACTIVE 3964887 2020-07-30 13:51:04 3985738 2020-07-30 13:59:05
4 2 10 209715200 512 1 NO CURRENT 3987920 2020-07-30 14:28:03 9.2954E+18 2020-07-30 14:28:03
SQL> select * from v$standby_log;
no rows selected
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both sid='*';
alter database add standby logfile thread 1 group 4('/u01/app/oracle/oradata/ORCL/redo04.log') size 200M;
alter database add standby logfile thread 1 group 5('/u01/app/oracle/oradata/ORCL/redo05.log') size 200M;
alter database add standby logfile thread 1 group 6('/u01/app/oracle/oradata/ORCL/redo06.log') size 200M;
alter database add standby logfile thread 1 group 7('/u01/app/oracle/oradata/ORCL/redo07.log') size 200M;
SQL> select * from v$standby_log;
SQL> exit
3.主库：添加db_unique_name
SQL> alter system set db_unique_name='orcl' scope=spfile sid='*';
4.备库：配置静态监听
环境变量修改：
export ORACLE_SID=dgorcl
备库检查：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dgorcl)
(ORACLE_HOME = /u01/app/oracle/product/19c)
(SID_NAME = dgorcl)
)
)
sqlnet.ora添加低版本客户端兼容连接:
cd /oracle/app/oracle/product/19c/network/admin
$ more sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
启动备库监听：
lsnrctl start
lsnrctl status
5.主备库：创建tnsnames.ora内容
节点1和2都配置：
[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/network/admin/tnsnames.ora
# Generated by Oracle configuration tools. ###(instance_name=orcl/2)
tns_orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.207.164)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
tns_dgorcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.207.165)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dgorcl)
)
)
连主库测试：
sqlplus sys/abcd1234@tns_orcl as sysdba
连备库测试：
sqlplus sys/abcd1234@tns_dgorcl as sysdba
6.主库：密码文件拷贝到备库
登录主库任意节点拷贝密码文件并传输到备库：
[oracle@oracle19c ~]$ cd $ORACLE_HOME/dbs
[oracle@oracle19c dbs]$ scp orapworcl 192.168.207.165:/u01/app/oracle/product/19c/dbs/orapwdgorcl
asm的拷贝方式参考如下：
su - grid
asmcmd
pwcopy pwdorcl.256.1024323395 /oracle/app/grid/orapworcl
scp /oracle/app/grid/orapworcl oracle@192.168.207.165: /u01/app/oracle/product/19c/dbs/orapwdgorcl
7.主备库：联通性测试：
备库启动到nomount状态：
连主库测试：
sqlplus sys/abcd1234@tns_orcl as sysdba
连备库测试：
sqlplus sys/abcd1234@tns_dgorcl as sysdba
8.备库：duplicate创建备库dgorcl
创建完成后注意检查路径下文件：
ll /u01/app/oracle/reco
ll /u01/app/oracle/oradata
传统方法参考：
主库创建参数文件：
create pfile='/home/oracle/pfile.txt' from spfile;注意修改控制文件路径及相应目录。
*.audit_file_dest='/u01/app/oracle/admin/dgorcl/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/DGORCL/control01.ctl','/u01/app/oracle/oradata/DGORCL/control02.ctl'
*.db_block_size=8192
*.db_name='orcl'
*.db_recovery_file_dest_size=42949672960
*.db_unique_name='dgorcl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_ORCL'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgorcl'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=1024m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3072m
*.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest='/u01/app/oracle/reco'
*.recyclebin='OFF'
备库启动startup nomount;
在备库操作：(ok)
rman target sys/abcd1234@tns_orcl auxiliary sys/abcd1234@tns_dgorcl
run
{
allocate channel cl1 type disk;
allocate auxiliary channel c1 type disk;
duplicate target database for standby from active database nofilenamecheck dorecover;
release channel c1;
release channel cl1;
}
做成脚本后台执行：
tns_orcl为备库配置的指向主库，tns_dgorcl在主库配置的tnsnames.ora指向备库。
$ cat dg.sh
rman target sys/abcd1234@tns_orcl auxiliary sys/abcd1234@tns_dgorcl log /home/oracle/rman-`date +%Y%m%d-%H%M`.log <<EOF
run
{
allocate channel cl1 type disk;
allocate auxiliary channel c1 type disk;
duplicate target database for standby from active database nofilenamecheck dorecover;
release channel c1;
release channel cl1;
}
EOF
$ chmod +x dg.sh
$ nohup ./dg.sh &
查看日志：tail -f /home/oracle/rman-xxx.log
9.主备库：调整参数
检查主库文件路径：cdb和pdb下都要检查
select name from v$datafile;
select * from v$logfile;
根据路径确定db_file_name_convert和log_file_name_convert对应方式。
主库修改参数：
--alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='service=tns_dgorcl lgwr async VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=dgorcl' scope=both sid='*';
alter system set log_archive_config='dg_config=(orcl,dgorcl)' scope=both sid='*';
--alter system set db_file_name_convert='/u01/app/oracle/oradata/DGORCL','/u01/app/oracle/oradata/ORCL' scope=spfile sid='*';
--alter system set log_file_name_convert='/u01/app/oracle/oradata/DGORCL','/u01/app/oracle/oradata/ORCL' scope=spfile sid='*';
alter system set standby_file_management=auto scope=both sid='*';
alter system set fal_client='tns_orcl' scope=both sid='*';
alter system set fal_server='tns_dgorcl' scope=both sid='*';
备库修改参数：
--alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgorcl' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='service=tns_orcl VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=orcl' scope=both sid='*';
alter system set log_archive_config='dg_config=(orcl,dgorcl)' scope=both sid='*';
alter system set db_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/DGORCL' scope=spfile sid='*';
alter system set log_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/DGORCL' scope=spfile sid='*';
alter system set standby_file_management=auto scope=both sid='*';
alter system set fal_client='tns_dgorcl' scope=both sid='*';
alter system set fal_server='tns_orcl' scope=both sid='*';
10.备库：增加standby_log（如果主库添加过，备库就自动建上）
--建第二个备库的时候可能需手动建。
SQL> select * from v$standby_log;
注意点：
pdbseed和pdb的tempfile没有自动创建，原因是目录缺失。
在备库创建好指定目录后，重启备库可自动创建。
主库手动增加temp表空间文件后，备库不会自动同步，备库需要手动创建。
主库创建表空间数据文件，备库可自动同步。
11.主备库：启用同步测试：
主库：
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY READ WRITE
检查:
SELECT
(SELECT name FROM V$DATABASE
) name,
(SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
) Current_primary_seq,
(SELECT MAX (sequence#)
FROM v$archived_log
WHERE TRUNC(next_time) > SYSDATE - 1
AND dest_id = 2
) max_stby,
(SELECT NVL (
(SELECT MAX (sequence#) - MIN (sequence#)
FROM v$archived_log
WHERE TRUNC(next_time) > SYSDATE - 1
AND dest_id = 2
AND applied = 'NO'
), 0)
FROM DUAL
) "To be applied",
(
(SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
) -
(SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 2
)) "To be Shipped"
FROM DUAL;
备库：
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB MOUNTED
4 JYC MOUNTED
SQL> alter pluggable database all open instances=all;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ ONLY NO
4 JYC READ ONLY NO
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
取消日志应用操作：alter database recover managed standby database cancel;
备库检查：select process,status,sequence#,thread# from v$managed_standby;
检查alert日志。
检查主备库状态:
set line 160
set wrap off
col inst_id for 99
select INST_ID, dbid,name,DB_UNIQUE_NAME,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
备库：
SELECT 'Last Applied : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log WHERE applied='YES'
)
UNION
SELECT 'Last Received : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log );
12.主备库：rman参数配置：
防止未应用归档却被删除:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
定期删除归档策略部署参考:
[oracle@racd2 ~]$ crontab -l
0 12 * * * /home/oracle/clear_archivelog.sh
[oracle@racd2 ~]$ cat clear_archivelog.sh
#!/bin/sh
source ~/.bash_profile
export ORACLE_SID=xxx1
/u01/app/oracle/product/19c/bin/rman target / >> /home/oracle/clear_archive20190412.log << EOF
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE - 3';
exit
EOF
最后修改时间：2025-09-10 14:34:19
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
