一、部署前准备
主机信息：
???? 以下几点需要注意： • db_unique_name 主备库不能相同 • db_name主备库需保持一致 • 主备库DB版本需保持一致、备库只安装软件
二、主备库tns及listener配置
2.1主库tns配置
[oracle@master admin]$ more tnsnames.ora
misdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.107)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = misdb)
)
)
misdg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.108)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = misdb)
(UR=A)
)
)
注意： ERROR:ORA-12528: TNS:listener: all appropriate instances are blocking new connections ，使用duplicate 复制数据库时，目标实例只启动到nomount状态。此时，监听 中对应实例的状态就是“BLOCKED”. 解决方法是在TSN配置添加特殊标记(UR = A)
2.2主库listener配置
[oracle@master admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = misdb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = master)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
2.3备库tns配置和主库一直
2.4备库listener配置
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(SID_NAME=misdb)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
)
)
三、 主库环境配置及状态
3.1主库开启归档和强制日志
SQL>
SQL> alter system set log_archive_dest_1='location=/home/oracle/arch' scope=both;
System altered.
SQL> alter database force logging;
3.2主要参数：
alter system set log_archive_config='dg_config=(misdg,misdb)' scope=both;
alter system set log_archive_dest_1='location=/home/oracle/arch valid_for=(all_logfiles,all_roles)db_unique_name=misdb' scope=both ;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=misdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=misdg' scope=both ;
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
alter system set FAL_SERVER='misdg';
alter system set fal_client='misdb';
alter system set standby_file_management=auto scope=both;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata','/u01/app/oracle/oradata/misdb' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata','/u01/app/oracle/oradata/misdb' SCOPE=SPFILE;
说明： 列出主备库上的DB_UNIQUE_NAME 参数。默认情况下，定义该参数能确保主备库数据库能够互相识别对方 Primary与Standby端的db_unique_name不一致时.
3.3主库添加 stanby log 文件
SQL>
alter database add standby logfile group 4 '/u01/app/oracle/oradata/misdb/redo04.log' size 50M;
alter database add standby logfile group 5 '/u01/app/oracle/oradata/misdb/redo05.log' size 50M;
alter database add standby logfile group 6 '/u01/app/oracle/oradata/misdb/redo06.log' size 50M;
alter database add standby logfile group 7 '/u01/app/oracle/oradata/misdb/redo07.log' size 50M;
???? 注意： • stanby log 日志大小与 redo log 日志保持一致 • stanby log 数量： standby logfile=(1+logfile组数)*thread=(1+3)*1=4 组,需要加 4 组 standby logfile • thread 要与 redo log 保持一致，如果是 rac，需要增加多个 thread 对应的 standby log
3.4、复制参数文件和密码文件至备库
SQL>create pfile from spfile ;
复制参数文件至备库（备库执行），要在 oracle 用户下复制：(init文件及密码文件)
scp orapwmisdb initmisdb.ora oracle@192.168.137.108:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
四、备库配置
4.1备库创建目录：
在oracle用户下执行
mkdir -p /u01/app/oracle/admin/misdb/adump
mkdir -p /u01/app/oracle/oradata/misdg
mkdir -p /home/oracle/arch
4.2修改参数
*.db_name='misdb'
*.db_unique_name='misdg'
*.fal_client='misdg'
*.fal_server='misdb'
*.log_archive_config='dg_config=(misdb,misdg)'
*.log_archive_dest_1='location=/home/oracle/arch'
*.log_archive_dest_2='SERVICE=misdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=misdb'
*.service_names=misdb
*.standby_file_management='AUTO'
*.db_file_name_convert='/u01/app/oracle/oradata/misdb','/u01/app/oracle/oradata/'
*.log_file_name_convert='/u01/app/oracle/oradata/misdb','/u01/app/oracle/oradata'
###4. 3启动备库到nomount
注意 oracle_sid和主库一致
SQL> startup nomunt
4.4RMAN DUPLICATE
rman target sys/system@misdb AUXILIARY sys/system@misdg
run {
allocate channel cl1 type disk;
allocate channel cl2 type disk;
allocate channel cl3 type disk;
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;
release channel c1;
release channel c2;
release channel c3;
}
或者后台执行 vi create_sb.sh
rman target sys/system@misdb AUXILIARY sys/system@misdg <<EOF
run {
allocate channel cl1 type disk;
allocate channel cl2 type disk;
allocate channel cl3 type disk;
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;
release channel c1;
release channel c2;
release channel c3;
}
exit
EOF
chmod +x create_sb.sh
nohup sh create_sb.sh > c1.log &
4.5备库开启日志应用
-- 首先开启备库至只读状态
alter database open;
-- 开启主备同步进程
RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
-- 检查备库角色
select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
-- 检查保护模式，最大性能
SQL> SELECT protection_mode FROM v$database;
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
五、基本维护
5.1查看主备日志同步情况
检查real-time apply 应用日志的进度
SET feed off
SET lines 300
SET pages 999
col name format a30
col value format a45
SELECT to_char(sysdate, 'yyyymmdd hh24:mi:ss') ctime,
name,
value,
datum_time
FROM v$dataguard_stats
WHERE NAME LIKE '%lag';
CTIME NAME VALUE DATUM_TIME
----------------- ------------------------------ --------------------------------------------- ------------------------------
20250111 22:19:44 transport lag +00 00:00:00 01/11/2025 22:19:43
20250111 22:19:44 apply lag +00 00:00:00 01/11/2025 22:19:43
其中如果apply lag对应的value大于0，那么就需要注意检查是否同步正常。如果相差时间非常多，value值等于几分钟，甚至达到1个小时，那可能实时同步有问题，需要检查 alert log 文件，其中会提示是否启动了real time apply。
5.2查看主备日志同步情况
SELECT registrar,
creator,
thread#,
applied,
sequence#,
first_change#,
next_change#,
completion_timE
FROM v$archived_log
WHERE completion_time > TRUNC(SYSDATE);
5.3启用MRP 进程，后台恢复进程应用standby redo logfile进行恢复:
SQL> alter database recover managed standby database using current logfile disconnect from session;
取消MRP进程应用：
SQL> alter database recover managed standby database cancel;
5.4查询主库日志传输序列号、备库日志应用序列号
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;
Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
1 83 83
如果相等则表示主、备库数据一致。反之则主、备库数据不一致。
5.5 检查备库是否有MRP0进程以及进程的活动状态
SELECT process,client_process,sequence#,status
FROM v$managed_standby;
特别说明： RFS（remote file server）：运行在备库上的进程，用于在备库上进行主库的日志恢复。默认，这个进程用于接收从主库传送过来的归档日志。 MRP：当物理备库启用了实时应用的时候，会创建这个进程，用于日志的实时应用。
5.6检查是否有GAP
select * from v$archive_gap
5.7查看相应进程是否起来
select process from v$managed_standby;
至此简单的adg配置完成。
