Oracle 11g单主搭建物理DG

来源：这里教程网时间：2026-03-03 15:37:23 作者：
前提条件：完成Oracle单机搭建，Oracle软件和数据库安装完成。完成Oracle备机的Oracle软件安装。可以参考文章： http://blog.itpub.net/22996654/viewspace-2149816/ 完成上述安装后开始搭建物理DG：主机IP：172.16.0.21 备机IP：172.16.0.75 把两个主机都设置好/etc/hosts
[root@aa  ~]# cat /etc/hosts172.16.0.21  aa172.16.0.75  bb
在从库把对应主库的所有数据目录、日志目录、归档目录都建立好。
[oracle@bb db_1]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump[oracle@bb db_1]$ mkdir -p $ORACLE_BASE/oradata/WMSPROD[oracle@bb db_1]$ mkdir -p $ORACLE_BASE/flash_recovery_area[oracle@bb db_1]$ chown -R oracle:oinstall /usr/local/bin/*[oracle@bb oradata]$ mkdir -p standbylog[oracle@bb oradata]$ lltotal 8drwxr-xr-x 2 oracle oinstall 4096 May 15 15:45 standbylog
drwxr-xr-x 2 oracle oinstall 4096 May 14 20:25 WMSPROD



设置监听：主库：[oracle@aa admin]$ cat listener.ora
# listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = aa)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /data/oracle
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=wmsprod)
(ORACLE_HOME=/data/oracle/product/11.2.0/db_1)
(SID_NAME=wmsprod)
)
)
[oracle@aa admin]$ cat tnsnames.ora
WMSPROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = aa)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = WMSPROD)
)
)
WMSPRODDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = WMSPRODDG)
)
)
备库：
[oracle@bb admin]$ cat listener.ora
# listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bb)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /data/oracle
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=wmsproddg)
(ORACLE_HOME=/data/oracle/product/11.2.0/db_1)
(SID_NAME=wmsproddg)
)
)
[oracle@bb admin]$ cat tnsnames.ora
WMSPRODDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = WMSPRODDG)
)
)
WMSPROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = aa)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = WMSPROD)
)
)
重启监听。





在主库加standby的日志文件：比redo日志文件多一组。
SQL> alter database add standby logfile  2  GROUP 7 ('/data/oracle/oradata/standbylog/standby01.log') SIZE 600m,  3  GROUP 8 ('/data/oracle/oradata/standbylog/standby02.log') size 600m,  4  GROUP 9 ('/data/oracle/oradata/standbylog/standby03.log') size 600m,  5  GROUP 10 ('/data/oracle/oradata/standbylog/standby04.log') size 600m,  6  GROUP 11 ('/data/oracle/oradata/standbylog/standby05.log') size 600m,  7  GROUP 12 ('/data/oracle/oradata/standbylog/standby06.log') size 600m,  8  GROUP 13 ('/data/oracle/oradata/standbylog/standby07.log') size 600m;Database altered.


修改主从的init文件：
主库：
[oracle@aa dbs]$ cat initwmsprod.ora
*.db_name='WMSPROD'
*.memory_target=5G
*.processes = 2000
*.audit_file_dest='/data/oracle/admin/wmsprod/adump'
*.audit_trail ='db'
*.db_block_size=8192
*.db_domain=''
*.db_recovery_file_dest='/data/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2G
*.diagnostic_dest='/data/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=WMSPRODXDB)'
*.open_cursors=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.control_files = (/data/oracle/oradata/WMSPROD/ora_control01.ctl,/data/oracle/oradata/WMSPROD/ora_control02.ctl)
*.compatible ='11.2.0'
*.standby_file_management='AUTO'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(wmsprod,wmsproddg)'
*.log_archive_dest_1='location=/data/oracle/oradata/WMSPROD/archivelog
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=wmsprod'
*.DB_UNIQUE_NAME=wmsprod
*.DB_FILE_NAME_CONVERT='/data/oracle/oradata/WMSPROD/','/data/oracle/oradata/WMSPROD/'
*.LOG_FILE_NAME_CONVERT='/data/oracle/oradata/WMSPROD/','/data/oracle/oradata/WMSPROD/'
*.log_archive_dest_2='SERVICE=wmsproddg  LGWR ASYNC
valid_for=(online_logfiles,primary_role)
	db_unique_name=wmsproddg'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=wmsproddg
*.fal_client=wmspro
备库：
[oracle@bb dbs]$ cat initwmsproddg.ora
*.db_name='WMSPROD'
*.memory_target=5G
*.processes = 2000
*.audit_file_dest='/data/oracle/admin/wmsprod/adump'
*.audit_trail ='db'
*.db_block_size=8192
*.db_domain=''
*.db_recovery_file_dest='/data/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2G
*.diagnostic_dest='/data/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=WMSPRODXDB)'
*.open_cursors=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.control_files = (/data/oracle/oradata/WMSPROD/ora_control01.ctl,/data/oracle/oradata/WMSPROD/ora_control02.ctl)
*.compatible ='11.2.0'
*.standby_file_management='AUTO'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(wmsprod,wmsproddg)'
*.log_archive_dest_1='location=/data/oracle/oradata/WMSPROD/archivelog
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=wmsproddg'
*.DB_UNIQUE_NAME=wmsproddg
*.DB_FILE_NAME_CONVERT='/data/oracle/oradata/WMSPROD/','/data/oracle/oradata/WMSPROD/'
*.LOG_FILE_NAME_CONVERT='/data/oracle/oradata/WMSPROD/','/data/oracle/oradata/WMSPROD/'
*.log_archive_dest_2='SERVICE=wmsprod  LGWR ASYNC
valid_for=(online_logfiles,primary_role)
	db_unique_name=wmsprod'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=wmsprod
*.fal_client=wmsprodd

cp 主库的密码文件生成备库的密码文件并放到备库对应位置：
[oracle@aa dbs]$ cp orapwwmsprod orapwwmsproddg


创建备库的spfile
文件，启动备库到nomount
模式 
SQL> shutdown immediate;
SQL> create spfile from pfile;
SQL> startup nomount;




RMAN复制主库到备库首先从库RMAN
连接到目标数据库和辅助数据库rman target sys/password@wmsprod auxiliary sys/password@wmsproddg
 
使用RMAN
的duplicate
命令进行复制，两边目录结构相同，需要添加nofilenamecheck
参数 
RMAN> duplicate target database for standby from active database nofilenamecheck;
 
复制成功后，备库自动被加载为mount
模式，进入sqlplus
查看 
SQL> select status from v$instance;




备库执行过程：[oracle@bb admin]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Fri May 15 18:09:13 2020Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to an idle instance.SQL> create spfile from pfile;                    File created.SQL> startup nomount;ORACLE instance started.Total System Global Area 5344731136 bytesFixed Size		    2262656 bytesVariable Size		 3724544384 bytesDatabase Buffers	 1593835520 bytesRedo Buffers		   24088576 bytesSQL> ![oracle@bb admin]$ rman target sys/oracle@wmsprod auxiliary sys/oracle@wmsproddgRecovery Manager: Release 11.2.0.4.0 - Production on Fri May 15 18:10:47 2020Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: WMSPROD (DBID=1351169222)connected to auxiliary database: WMSPROD (not mounted)RMAN> duplicate target database for standby from active database nofilenamecheck;Starting Duplicate Db at 15-MAY-20using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=2376 device type=DISKcontents of Memory Script:{   backup as copy reuse   targetfile  '/data/oracle/product/11.2.0/db_1/dbs/orapwwmsprod' auxiliary format  '/data/oracle/product/11.2.0/db_1/dbs/orapwwmsproddg'   ;}executing Memory ScriptStarting backup at 15-MAY-20allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=96 device type=DISKFinished backup at 15-MAY-20contents of Memory Script:{   backup as copy current controlfile for standby auxiliary format  '/data/oracle/oradata/WMSPROD/ora_control01.ctl';   restore clone controlfile to  '/data/oracle/oradata/WMSPROD/ora_control02.ctl' from  '/data/oracle/oradata/WMSPROD/ora_control01.ctl';}executing Memory ScriptStarting backup at 15-MAY-20using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copycopying standby control fileoutput file name=/data/oracle/product/11.2.0/db_1/dbs/snapcf_wmsprod.f tag=TAG20200515T181846 RECID=1 STAMP=1040494726channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 15-MAY-20Starting restore at 15-MAY-20using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: copied control file copyFinished restore at 15-MAY-20contents of Memory Script:{   sql clone 'alter database mount standby database';}executing Memory Scriptsql statement: alter database mount standby databasecontents of Memory Script:{   set newname for tempfile  1 to  "/data/oracle/oradata/WMSPROD/temp01.dbf";   switch clone tempfile all;   set newname for datafile  1 to  "/data/oracle/oradata/WMSPROD/system01.dbf";   set newname for datafile  2 to  "/data/oracle/oradata/WMSPROD/sysaux01.dbf";   set newname for datafile  3 to  "/data/oracle/oradata/WMSPROD/undotbs01.dbf";   set newname for datafile  4 to  "/data/oracle/oradata/WMSPROD/users01.dbf";   backup as copy reuse   datafile  1 auxiliary format  "/data/oracle/oradata/WMSPROD/system01.dbf"   datafile  2 auxiliary format  "/data/oracle/oradata/WMSPROD/sysaux01.dbf"   datafile  3 auxiliary format  "/data/oracle/oradata/WMSPROD/undotbs01.dbf"   datafile  4 auxiliary format  "/data/oracle/oradata/WMSPROD/users01.dbf"   ;   sql 'alter system archive log current';}executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to /data/oracle/oradata/WMSPROD/temp01.dbf in control fileexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting backup at 15-MAY-20using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=/data/oracle/oradata/WMSPROD/users01.dbfoutput file name=/data/oracle/oradata/WMSPROD/users01.dbf tag=TAG20200515T181853channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35channel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=/data/oracle/oradata/WMSPROD/system01.dbfoutput file name=/data/oracle/oradata/WMSPROD/system01.dbf tag=TAG20200515T181853channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=/data/oracle/oradata/WMSPROD/sysaux01.dbfoutput file name=/data/oracle/oradata/WMSPROD/sysaux01.dbf tag=TAG20200515T181853channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=/data/oracle/oradata/WMSPROD/undotbs01.dbfoutput file name=/data/oracle/oradata/WMSPROD/undotbs01.dbf tag=TAG20200515T181853channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07Finished backup at 15-MAY-20sql statement: alter system archive log currentcontents of Memory Script:{   switch clone datafile all;}executing Memory Scriptdatafile 1 switched to datafile copyinput datafile copy RECID=1 STAMP=1040494790 file name=/data/oracle/oradata/WMSPROD/system01.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=2 STAMP=1040494790 file name=/data/oracle/oradata/WMSPROD/sysaux01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=3 STAMP=1040494790 file name=/data/oracle/oradata/WMSPROD/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=4 STAMP=1040494790 file name=/data/oracle/oradata/WMSPROD/users01.dbfFinished Duplicate Db at 15-MAY-20


查看备库归档模式：[oracle@bb admin]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Fri May 15 18:32:46 2020Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> archive log list;Database log mode	       Archive ModeAutomatic archival	       EnabledArchive destination	       /data/oracle/oradata/WMSPROD/archivelogOldest online log sequence     7Next log sequence to archive   0Current log sequence	       8如果没有建这个archivelog目录。会在上级目录生成同步的归档日志文件。
主库切日志组：切日志：SQL> alter system switch logfile  2  ;System altered.[oracle@aa archivelog]$ lltotal 105956-rw-r----- 1 oracle oinstall 56715776 May 14 20:50 1_2_1040413510.dbf-rw-r----- 1 oracle oinstall 47865344 May 15 17:33 1_3_1040413510.dbf-rw-r----- 1 oracle oinstall    44544 May 15 17:33 1_4_1040413510.dbf-rw-r----- 1 oracle oinstall  2842624 May 15 18:18 1_5_1040413510.dbf-rw-r----- 1 oracle oinstall    28160 May 15 18:19 1_6_1040413510.dbf-rw-r----- 1 oracle oinstall   151040 May 15 18:24 1_7_1040413510.dbf-rw-r----- 1 oracle oinstall   844800 May 15 18:50 1_8_1040413510.dbf备库日志同步：[oracle@bb archivelog]$ lltotal 828-rw-r----- 1 oracle oinstall 844800 May 15 18:50 1_8_1040413510.dbf查备库：SQL> select status from v$instance;STATUS------------MOUNTEDSQL> select name from v$database; NAME---------
WMSPROD


关闭审计：第1步：查看审计功能是否开启？SQL> show parameter audit;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------audit_file_dest                   string      /u01/app/oracle/admin/ORCL/adumpaudit_sys_operations      boolean     FALSEaudit_syslog_level            stringaudit_trail                            string      NONEaudit_trail 的value值为NONE表示不开启；audit_trail 的value值为FALSE表示不开启；audit_trail 的value值为DB表示开启；audit_trail 的value值为TURE表示开启；audit_trail 的value值为OS表示审计记录写入一个操作系统文件（不是特别理解）；第2步：开启审计功能SQL> alter system set audit_sys_operations=TRUE scope=spfile;--审计管理用户(以sysdba/sysoper角色登陆)SQL> alter system set audit_trail=db,extended scope=spfile;重启实例第3步：关闭审计功能SQL> conn /as sysdbaSQL> show parameter auditSQL> alter system set audit_trail = none scope=spfile;重启实例
OK。




SQL> show parameter audit;


NAME
				     TYPE
	 VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest 
		     string
	 /data/oracle/admin/wmsprod/adu
						 mp
audit_sys_operations
		     boolean
	 FALSE
audit_syslog_level
		     string
audit_trail
			     string
	 DB
SQL> alter system set audit_trail = none scope=spfile;


System altered.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.


Total System Global Area 5344731136 bytes
Fixed Size
		    2262656 bytes
Variable Size
		 3724544384 bytes
Database Buffers
	 1593835520 bytes
Redo Buffers
		   24088576 bytes
Database mounted.
Database opened.
SQL> show parameter audit;


NAME
				     TYPE
	 VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest 
		     string
	 /data/oracle/admin/wmsprod/adu
						 mp
audit_sys_operations
		     boolean
	 FALSE
audit_syslog_level
		     string
audit_trail
			     string
	 NONE





在备库开启实时日志应用SQL> alter database recover managed standby database using current logfile disconnect from session;
主备库角色状态查询SQL> select switchover_status,database_role from v$database;
--
主库显示：TO STANDBY/PRIMARY
，如果显示SESSION ACTIVE
表示还有活动的会话，需要关闭活动的会话再检查--
备库显示：NOT ALLOWED/PHYSICAL STANDBY



测试
DG
执行日志切换测试在主库端切换归档，在备库检查是否也发生了切换 
主库上执行日志切换 
SQL> archive log list;
SQL> alter system switch logfile;
SQL> archive log list;
备库上查看，日志的sequence
号也跟着变了 
SQL> archive log list;
1
查看备库启动的DG进程SQL> select PROCESS ,STATUS from   v$managed_standby;
 
PROCESS                       STATUS
--------------------------- ------------------------------------
ARCH                              CONNECTED
ARCH                              CONNECTED
ARCH                              CONNECTED
ARCH                              CLOSING
RFS                         IDLE
RFS                         IDLE
RFS                         IDLE
MRP0                              APPLYING_LOG
查看数据库的保护模式SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
 
DATABASE_ROLE                                           PROTECTION_MODE                                                PROTECTION_LEVEL                                                     OPEN_MODE
------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
PRIMARY                                               MAXIMUM PERFORMANCE                                             MAXIMUM PERFORMANCE                                                 READ WRITE
查看DG的日志信息SQL> select * from v$dataguard_status;
1
Open Read Only Standby数据库以只读方式打开备库，并开启实时日志应用 
SQL> shutdown immediate
SQL> startup
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
SQL> select process,client_process,sequence#,status from v$managed_standby;
SQL> alter database recover managed standby database using current logfile disconnect from session;


在DG上执行：

SQL> select database_role from v$database;

DATABASE_ROLE

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

PHYSICAL STANDBY


物理备库。

SQL> select status from v$instance;



STATUS

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

MOUNTED


原本DG是mount状态。

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

断应用同步。

SQL> alter database open read only;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

开启应用同步。

SQL> select RECOVERY_MODE from v$archive_dest_status where rownum<5;

RECOVERY_MODE

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

MANAGED REAL TIME APPLY

IDLE

IDLE

IDLE




SQL> SELECT NAME,VALUE,UNIT,TIME_COMPUTED
  2  FROM V$DATAGUARD_STATS
  3  WHERE NAME IN ('transport lag','apply lag');


NAME
	      VALUE
		   UNIT 
			  TIME_COMPUTED
------------- -------------------- ------------------------------ ------------------------------
transport lag +00 00:00:00
	   day(2) to second(0) interval   05/15/2020 19:41:55
apply lag     +00 00:00:00
	   day(2) to second(0) interval   05/15/2020 19:41:55



主库日志信息：

SQL> select thread#, max(sequence#) from v$archived_log group by thread#;


   THREAD# MAX(SEQUENCE#)
---------- --------------
	 1
	       14


SQL> archive log list;
Database log mode
	       Archive Mode
Automatic archival
	       Enabled
Archive destination
	       /data/oracle/oradata/WMSPROD/archivelog
Oldest online log sequence     10
Next log sequence to archive   15
Current log sequence
	       15


备库日志信息：

SQL> select thread#, max(sequence#) from v$archived_log where applied='YES' group by thread#;


   THREAD# MAX(SEQUENCE#)
---------- --------------
	 1
	       13


SQL> archive log list;
Database log mode
	       Archive Mode
Automatic archival
	       Enabled
Archive destination
	       /data/oracle/oradata/WMSPROD/archivelog
Oldest online log sequence     10
Next log sequence to archive   0
Current log sequence
	       15


主备同步正常。





以上完成物理DG搭建。