dg静默

up的配置为：

• 环境：VMware + CentOS7.9（IP：192.168.111.11）
• 已安装：Oracle 19c 单实例数据库（SID=oracle，DB_NAME=oracle）
• 状态：能正常 sqlplus / as sysdba 登录，能 select name,db_unique_name,open_mode from v$database; 查询

主库（henry）

• IP: 192.168.111.11

• ORACLE_HOME: /oracle/product/19.3.0/db_1

• SID: oracle

• DB_NAME: oracle

• DB_UNIQUE_NAME: oracle (将改为oracle_primary)

• LOG_MODE: NOARCHIVELOG (需改为ARCHIVELOG)

• 版本: Oracle 19c (19.3.0.0.0)

• 文件路径:

• 数据文件: /oradata/ORACLE/system01.dbf, sysaux01.dbf, undotbs01.dbf, users01.dbf

• 日志文件: /oradata/ORACLE/redo01.log, redo02.log, redo03.log (3组，每组200MB)

• 控制文件: /oradata/ORACLE/control01.ctl, control02.ctl

• FRA: 未配置（db_recovery_file_dest为空）

• 磁盘空间: 未提供，假设/oradata和/oracle有足够空间

备库（alice）

• IP: 192.168.111.12
• ORACLE_HOME: /oracle/product/19.3.0/db_1
• SID: oracle
• 状态: 已安装软件，未创建数据库，未配置监听
• DB_UNIQUE_NAME: 将设置为oracle_standby
• 磁盘空间: /oracle 36G可用

步骤1：主库启用归档模式和FORCE LOGGING

目的：DG要求主库启用ARCHIVELOG模式以归档事务日志，FORCE LOGGING确保所有操作记录到日志。

1. 检查当前模式

   [oracle@henry ~]$ sqlplus / as sysdba
   SQL> SELECT log_mode FROM v$database;

输出：NOARCHIVELOG（需更改）

1. 创建归档目录

   [oracle@henry ~]$ mkdir -p /oracle/arch

2. 启用归档模式

   SQL> shutdown immediate;
   SQL> startup mount;
   SQL> alter database archivelog;
   SQL> alter database open;
   SQL> archive log list;

检查输出：应显示“Database log mode: Archive Mode”和“Automatic archival: Enabled”。

1. 启用FORCE LOGGING

   SQL> alter database force logging;
   SQL> select log_mode, force_logging from v$database;

检查输出：FORCE_LOGGING应为YES。

1. 设置归档路径

   SQL> alter system set log_archive_dest_1=‘LOCATION=/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oracle_primary’;

步骤2：配置主库参数

目的：设置DG相关参数，支持日志传输和主备角色切换。

1. 创建pfile备份

   SQL> create pfile=‘/oracle/product/19.3.0/db_1/dbs/initoracle.ora’ from spfile;

2. 编辑pfile

   [oracle@henry ~]$ vi /oracle/product/19.3.0/db_1/dbs/initoracle.ora

添加或修改以下参数（已移除FRA相关参数）： *.db_name=‘oracle’ *.db_unique_name=‘oracle_primary’ *.log_archive_config=‘DG_CONFIG=(oracle_primary,oracle_standby)’ *.log_archive_dest_1=‘LOCATION=/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oracle_primary’ *.log_archive_dest_2=‘SERVICE=oracle_standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oracle_standby’ *.log_archive_dest_state_1=‘ENABLE’ *.log_archive_dest_state_2=‘ENABLE’ *.log_archive_format=‘%t_%s_%r.arc’ *.log_archive_max_processes=3 *.fal_server=‘oracle_standby’ *.db_file_name_convert=‘/oradata/oracle_standby’,‘/oradata/ORACLE’ *.log_file_name_convert=‘/oradata/oracle_standby’,‘/oradata/ORACLE’ *.standby_file_management=‘AUTO’ *.remote_login_passwordfile=‘EXCLUSIVE’

1. 创建spfile并重启

   SQL> shutdown immediate;
   SQL> create spfile from pfile=‘/oracle/product/19.3.0/db_1/dbs/initoracle.ora’;
   SQL> startup;
   SQL> show parameter db_unique_name;
   SQL> show parameter log_archive_dest;

检查输出：确保db_unique_name=oracle_primary，log_archive_dest_2指向oracle_standby。

步骤3：复制参数文件和密码文件到备库

目的：备库需要一致的密码文件和参数文件模板。

1. 复制文件

   [oracle@henry ~]$ scp /oracle/product/19.3.0/db_1/dbs/initoracle.ora oracle@192.168.111.12:/oracle/product/19.3.0/db_1/dbs/initoracle_standby.ora
   [oracle@henry ~]$ scp /oracle/product/19.3.0/db_1/dbs/orapworacle oracle@192.168.111.12:/oracle/product/19.3.0/db_1/dbs/orapworacle_standby

2. 修改备库参数文件

   [oracle@alice ~]$ vi /oracle/product/19.3.0/db_1/dbs/initoracle_standby.ora

替换为以下内容： *.db_name=‘oracle’ *.db_unique_name=‘oracle_standby’ *.log_archive_config=‘DG_CONFIG=(oracle_primary,oracle_standby)’ *.log_archive_dest_1=‘LOCATION=/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oracle_standby’ *.log_archive_dest_2=‘SERVICE=oracle_primary ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oracle_primary’ *.log_archive_dest_state_1=‘ENABLE’ *.log_archive_dest_state_2=‘ENABLE’ *.log_archive_format=‘%t_%s_%r.arc’ *.log_archive_max_processes=3 *.fal_server=‘oracle_primary’ *.db_file_name_convert=‘/oradata/ORACLE’,‘/oradata/oracle_standby’ *.log_file_name_convert=‘/oradata/ORACLE’,‘/oradata/oracle_standby’ *.standby_file_management=‘AUTO’ *.remote_login_passwordfile=‘EXCLUSIVE’ *.control_files=‘/oradata/oracle_standby/control01.ctl’,‘/oradata/oracle_standby/control02.ctl’ *.audit_file_dest=‘/oracle/admin/oracle_standby/adump’ *.diagnostic_dest=‘/oracle’

1. 创建备库目录

   [oracle@alice ~]$ mkdir -p /oradata/oracle_standby
   [oracle@alice ~]$ mkdir -p /oracle/admin/oracle_standby/adump
   [oracle@alice ~]$ mkdir -p /oracle/arch

2. 启动备库到nomount

   [oracle@alice ~]$ export ORACLE_SID=oracle
   [oracle@alice ~]$ sqlplus / as sysdba
   SQL> startup nomount pfile=‘/oracle/product/19.3.0/db_1/dbs/initoracle_standby.ora’;
   SQL> create spfile from pfile;
   SQL> shutdown immediate;
   SQL> startup nomount;
   SQL> show parameter db_unique_name;

检查输出：确保db_unique_name=oracle_standby。

步骤4：配置主备网络（监听和TNS）

目的：确保主备通过TNS服务名通信。

1. 主库配置静态监听

   [oracle@henry ~]$ vi /oracle/product/19.3.0/db_1/network/admin/listener.ora

内容： LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.11)(PORT = 1521)) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = oracle) (ORACLE_HOME = /oracle/product/19.3.0/db_1) (GLOBAL_DBNAME = oracle_primary) ) ) 重载监听：

[oracle@henry ~]$ lsnrctl reload
[oracle@henry ~]$ lsnrctl status

1. 备库配置静态监听

   [oracle@alice ~]$ vi /oracle/product/19.3.0/db_1/network/admin/listener.ora

内容： LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.12)(PORT = 1521)) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = oracle) (ORACLE_HOME = /oracle/product/19.3.0/db_1) (GLOBAL_DBNAME = oracle_standby) ) ) 启动监听：

[oracle@alice ~]$ lsnrctl start
[oracle@alice ~]$ lsnrctl status

1. 配置主备tnsnames.ora

• 主库：

  [oracle@henry ~]$ vi /oracle/product/19.3.0/db_1/network/admin/tnsnames.ora

内容： oracle_primary = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.11)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oracle_primary) ) ) oracle_standby = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.12)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oracle_standby) ) )

• 备库：

  [oracle@alice ~]$ vi /oracle/product/19.3.0/db_1/network/admin/tnsnames.ora

内容与主库相同。

1. 测试网络连通性

   [oracle@henry ~]$ sqlplus sys/your_password@oracle_primary as sysdba
   [oracle@henry ~]$ sqlplus sys/your_password@oracle_standby as sysdba
   [oracle@alice ~]$ sqlplus sys/your_password@oracle_primary as sysdba
   [oracle@alice ~]$ sqlplus sys/your_password@oracle_standby as sysdba

注意：替换your_password为主库SYS用户密码。如果报ORA-01017，检查密码文件是否正确复制。

步骤5：创建Standby Redo Log

目的：支持实时应用和同步传输，准备主备角色切换。

1. 确认主库日志组

   [oracle@henry ~]$ sqlplus / as sysdba
   SQL> select group#, bytes/1024/1024 AS MB from v$log;

输出：3组，每组200MB。

1. 主库添加Standby Redo Log（比在线日志多一组）

   SQL> alter database add standby logfile ‘/oradata/ORACLE/std_redo01.log’ size 200m;
   SQL> alter database add standby logfile ‘/oradata/ORACLE/std_redo02.log’ size 200m;
   SQL> alter database add standby logfile ‘/oradata/ORACLE/std_redo03.log’ size 200m;
   SQL> alter database add standby logfile ‘/oradata/ORACLE/std_redo04.log’ size 200m;
   SQL> select group#, bytes/1024/1024 AS MB, status from v$standby_log;

2. 备库添加Standby Redo Log

   [oracle@alice ~]$ sqlplus / as sysdba
   SQL> alter database add standby logfile ‘/oradata/oracle_standby/std_redo01.log’ size 200m;
   SQL> alter database add standby logfile ‘/oradata/oracle_standby/std_redo02.log’ size 200m;
   SQL> alter database add standby logfile ‘/oradata/oracle_standby/std_redo03.log’ size 200m;
   SQL> alter database add standby logfile ‘/oradata/oracle_standby/std_redo04.log’ size 200m;
   SQL> select group#, bytes/1024/1024 AS MB, status from v$standby_log;

步骤6：RMAN Duplicate创建备库

目的：从主库复制数据库到备库，创建物理Standby。

1. 启动告警日志监控

• 主库：

  [oracle@henry ~]$ tail -f /oracle/diag/rdbms/oracle/oracle_primary/trace/alert_oracle.log

• 备库：

  [oracle@alice ~]$ tail -f /oracle/diag/rdbms/oracle/oracle_standby/trace/alert_oracle.log

1. 确保状态

• 主库：OPEN

  [oracle@henry ~]$ sqlplus / as sysdba
  SQL> select open_mode from v$database;

• 备库：NOMOUNT

  [oracle@alice ~]$ sqlplus / as sysdba
  SQL> select status from v$instance;

1. 执行RMAN Duplicate

   [oracle@henry ~]$ rman target sys/your_password@oracle_primary auxiliary sys/your_password@oracle_standby
   RMAN> duplicate target database for standby from active database;

注意：

• 确保DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT无换行符（教程报错1：ORA-17628）。
• 如果报ORA-17629/ORA-01017，检查tnsnames.ora和密码文件（教程报错2）。
• 如果报ORA-01511，临时设置alter system set standby_file_management=MANUAL;（教程报错3）。

步骤7：验证备库

1. 检查主备状态

• 主库：

  SQL> select name, database_role, protection_mode, open_mode from v$database;

预期：database_role=PRIMARY, open_mode=READ WRITE

• 备库：

  SQL> select name, database_role, protection_mode, open_mode from v$database;

预期：database_role=PHYSICAL STANDBY, open_mode=MOUNTED

1. 启动备库日志应用

   [oracle@alice ~]$ sqlplus / as sysdba
   SQL> alter database recover managed standby database disconnect from session;

2. 测试数据同步

• 主库创建测试表：

  [oracle@henry ~]$ sqlplus / as sysdba
  SQL> create table test_dg (id number, name varchar2(50));
  SQL> insert into test_dg values (1, ‘Test1’);
  SQL> commit;
  SQL> alter system switch logfile;

• 备库检查：

  [oracle@alice ~]$ sqlplus / as sysdba
  SQL> alter database open read only;
  SQL> select * from test_dg;

预期：能看到id=1, name='Test1’的记录。

步骤8：启用实时应用（可选）

目的：让备库实时应用Standby Redo Log，减少同步延迟（无需FRA也可实现）。

1. 备库取消当前日志应用

   SQL> recover managed standby database cancel;

2. 启用实时应用

   SQL> recover managed standby database using current logfile disconnect;

3. 验证

   SQL> select dest_name, status, recovery_mode from v$archive_dest_status;

预期：RECOVERY_MODE包含“MANAGED REAL TIME APPLY”。