Oracle11g 单机搭建DataGuard环境的测试

原创 eygle 2019-11-28

622

由于Oracle不断改进DataGuard技术，这一备用容灾、备份方案被越来越广泛的使用，通过传递重做日志（Redo Log）来保证主库和备库的同步，是非常清晰简易的安全解决方案。任何一位DBA都应该了解和实践DataGuard的工作机制。

本文通过在单机环境中搭建一个DataGuard环境向读者介绍这一技术，并且希望每位读者都能够测试和熟悉一下这一解决方案，由于单机需要的环境简单，但是在同一主机上存在主库和备库两套数据库实体，也需要读者能够分清两者，避免理解上的混淆。

以下测试使用的是Windows操作系统、Oracle Database 11g。主机操作系统主要信息为：hostname=myjohn，IP=192.168.0.102，数据库软件版本：oracle 11.2.0.1.0 ；创建数据库DBID=PRIMARY，SID=PRIMARY作为主库实例；数据库home环境于E:\oracle\11.2.0\。

测试首先需要准备好参数文件，设定一些必要的参数。
通过spfile生成pfile文件：

SQL> create pfile='E:\oracle\11.2.0\database\INITprimary.ORA' from spfile;

然后修改主库参数文件INITprimary.ORA添加参数，log_archive_dest_2指定远程归档路径，其中的服务名standby通过tnsnames.ora文件的配置，指向备用数据库,日志传输使用ARCH进程异步模式：

*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=E:\oracle\oradata\primary\archive'
*.log_archive_dest_2='service=standby ARCH ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' 
*.fal_client = 'primary'
*.fal_server = 'standby'
*.standby_file_management = 'auto'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='ARC%S_%R.%T.dbf'
*.local_listener='LISTENER_PRIMARY'
*.DB_FILE_NAME_CONVERT='E:\oracle\oradata\primary\','E:\oracle\oradata\standby\'
*.LOG_FILE_NAME_CONVERT='E:\oracle\oradata\primary\','E:\oracle\oradata\standby\'

修改参数文件之后，可以通过这个参数文件重建主库的spfile（以上参数也可以在命令行中修改，直接写入参数文件）

SQL> create spfile= 'E:\oracle\11.2.0\database\SPFILEPRIMARY.ORA’
   2 from pfile=‘E:\oracle\11.2.0\database\INITprimary.ORA’;

接下来通过主库的参数文件，创建备库需要的pfile文件：

SQL> create pfile='E:\oracle\11.2.0\database\INITstandby.ora' from spfile;

编辑INITstandby.ora，设置对应standby数据库的初始化参数（以下为standby参数文件全文）。

standby.__db_cache_size=239075328
standby.__java_pool_size=4194304
standby.__large_pool_size=4194304
standby.__oracle_base='E:\oracle'#ORACLE_BASE set from environment
standby.__pga_aggregate_target=243269632
standby.__sga_target=364904448
standby.__shared_io_pool_size=0
standby.__shared_pool_size=109051904
standby.__streams_pool_size=0
*.audit_file_dest=' E:\oracle\oradata\standby\adump '
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='E:\oracle\oradata\standby\standby01.ctl'
*.db_block_size=8192
*.db_domain=''
*.DB_FILE_NAME_CONVERT=('E:\oracle\oradata\primary\','E:\oracle\oradata\standby\')
*.db_name='primary'
*.diagnostic_dest='E:\app\oracle'
*.fal_client='standby'
*.fal_server='primary'
*.local_listener=’LISTENER_PRIMARY’
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=E:\oracle\oradata\standby\archivelog'
*.log_archive_dest_2='service=primary ARCH ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='ARC%S_%R.%T.dbf'
*.LOG_FILE_NAME_CONVERT=('E:\oracle\oradata\primary\','E:\oracle\oradata\standby\')
*.memory_target=605028352
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
db_unique_name=standby
instance_name=standby
service_names=standby

由于STANDBY数据库和PRIMARY处于同一个服务器上，因此还需要增加DB_UNIQUE_NAME、INSTANCE_NAME、SERVICE_NAMES、LOG_FILE_NAME_CONVERT和DB_FILE_NAME_CONVERT参数。

保存参数文件后创建备库spfile：

SQL> create spfile='E:\oracle\11.2.0\database\SPFILESTANDBY.ORA'
2 from pfile='E:\oracle\11.2.0\database\INITstandby.ora';

创建备库所需要的常规目录结构：

E:\oracle\oradata\standby\archivelog
E:\oracle\oradata\standby\adump

在命令行创建stnadby数据库的服务名：

C:\oracle>oradim -new -sid standby

配置tnsnames.ora文件，设定服务名（注意，如果是在两台服务器配置，则需要在主备库的tnsnames.ora文件中同时修改）：

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myjohn)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myjohn)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )

LISTENER_PRIMARY =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myjohn)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = myjohn)(PORT = 1521))
  )

配置listener.ora文件，为备库新建监听listener1，监听端口1522。

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myjohn)(PORT = 1522))
    )
  )

SID_LIST_LISTENER1 =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = E:\oracle\11.2.0)
      (SID_NAME = standby)
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = E:\oracle\11.2.0)
      (SID_NAME = primary)
    )
  )

ADR_BASE_LISTENER1 = E:\app\oracle

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myjohn)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ADR_BASE_LISTENER= E:\app\oracle

配置完成之后，重新启动监听器，

C:\oracle>lsnrctl start

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 20-JAN-2011 11:47:07
Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
System parameter file is E:\oracle\11.2.0\network\admin\listener.ora
Log messages written to e:\app\oracle\diag\tnslsnr\myjohn\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myjohn)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myjohn)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                20-JAN-2011 11:47:13
Uptime                    0 days 0 hr. 0 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   E:\oracle\11.2.0\network\admin\listener.ora
Listener Log File         e:\app\oracle\diag\tnslsnr\myjohn\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myjohn)(PORT=1521)))
Services Summary...
Service "primary" has 1 instance(s).
  Instance "primary", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

创建口令文件，注意，主备库口令文件的口令需要相同，否则无法通过认证：

C:\oracle>orapwd file=E:\oracle\11.2.0\database\pwdprimary.ora password=oracle

C:\oracle>orapwd file=E:\oracle\11.2.0\database\pwdstandby.ora password=oracle

确认tnsnames.ora文件配置正确，主备库能够互相连通：

C:\oracle>tnsping standby
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myjohn)
(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby)))
OK (30 msec)

C:\oracle>tnsping primary
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myjohn)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary)))
OK (30 msec)

并且确认sys用户能用设定的密码oracle以sysdba身份登录sqlplus操作。

C:\oracle>sqlplus sys/oracle@primary as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 12 16:25:15 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Copyright (c) 1997, 2010, Oracle.  All rights reserved.
SQL>

C:\oracle>sqlplus sys/oracle@standby as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 12 16:27:35 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.
SQL>

修改主库开启归档模式和force logging，主要命令如下：

SQL> alter database archivelog;
SQL> alter database force logging;

创建DataGuard可以使用rman进行，先通过RMAN创建备份，然后启动STANDBY备用数据库实例，即可开始创建：

C:\oracle>set oracle_sid=standby
C:\oracle>sqlplus sys/oracle@standby as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 2月 17 14:39:40 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
已连接到空闲例程。

SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             289407440 bytes
Database Buffers          239075328 bytes
Redo Buffers                5804032 bytes

在主库上为STANDBY创建备用控制文件：

C:\oracle>set ORACLE_SID=primary  
C:\oracle>rman target/
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jan 19 09:55:48 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: PRIMARY (DBID=1578247313)
RMAN> run
2> {
3> allocate channel c1 device type disk format 'E:\oracle\oradata\primary\backup\%U';
4> backup current controlfile for standby;
5> release channel c1;
6> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=141 device type=DISK

Starting backup at 19-JAN-11
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including standby control file in backup set
channel c1: starting piece 1 at 19-JAN-11
channel c1: finished piece 1 at 19-JAN-11
piece handle=E:\ORACLE\ORADATA\PRIMARY\BACKUP\08M2GFK6_1_1 tag=TAG20110119T112910 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-JAN-11

released channel: c1

接下来通过RMAN进行备库的恢复创建可以通过一条命令完成（注意：如果是不同的服务器，备份集存放于和主库相同的路径即可），首先连接代理实例，通过DUPLICATE命令实现：

RMAN> connect auxiliary sys/oracle@standby

connected to auxiliary database: PRIMARY (not mounted)
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> allocate auxiliary channel ac1 device type disk;
5> duplicate target database for standby;
6> release channel c1;
7> release channel ac1;
8> }

allocated channel: c1
channel c1: SID=141 device type=DISK

allocated channel: ac1
channel ac1: SID=10 device type=DISK

Starting Duplicate Db at 19-JAN-11

contents of Memory Script:
{
   restore clone standby controlfile;
}
executing Memory Script

Starting restore at 19-JAN-11

channel ac1: starting datafile backup set restore
channel ac1: restoring control file
channel ac1: reading from backup piece E:\ORACLE\ORADATA\PRIMARY\BACKUP\08M2GFK6_1_1
channel ac1: piece handle=E:\ORACLE\ORADATA\PRIMARY\BACKUP\08M2GFK6_1_1 tag=TAG20110119T112910
channel ac1: restored backup piece 1
channel ac1: restore complete, elapsed time: 00:00:01
output file name=E:\ORACLE\ORADATA\STANDBY\STANDBY01.CTL
Finished restore at 19-JAN-11

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile 1 to
 "E:\ORACLE\ORADATA\STANDBY\TEMP01.DBF";
   switch clone tempfile all;
   set newname for datafile 1 to
 "E:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF";
   set newname for datafile 2 to
 "E:\ORACLE\ORADATA\STANDBY\SYSAUX01.DBF";
   set newname for datafile 3 to
 "E:\ORACLE\ORADATA\STANDBY\UNDOTBS01.DBF";
   set newname for datafile 4 to
 "E:\ORACLE\ORADATA\STANDBY\USERS01.DBF";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET NEWNAME
renamed tempfile 1 to E:\ORACLE\ORADATA\STANDBY\TEMP01.DBF in control file

executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 19-JAN-11
。。。
。。。
released channel: c1
released channel: ac1

至此DATA GUARD环境创建成功，可以将STANDBY处于恢复状态，主备库归档日志同步。

SQL> alter database recover managed standby database disconnect from session;
Database altered.

查看主备库的archive路径参数

SQL> select dest_name,status from v$archive_dest;

归档路径参数LOG_ARCHIVE_DEST_1和LOG_ARCHIVE_DEST_2状态应该为“VALID”,说明路径有效。可以查询V$ARCHIVED_LOG检查备库归档状况：

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
 SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
        16 19-JAN-11 19-JAN-11
        17 19-JAN-11 19-JAN-11
        18 19-JAN-11 19-JAN-11

在主库强制归档，观察备库的归档情况：

SQL>ALTER SYSTEM SWITCH LOGFILE;

查询备库信息，可以看到日志成功传递过来：

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
 SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
        16 19-JAN-11 19-JAN-11
        17 19-JAN-11 19-JAN-11
        18 19-JAN-11 19-JAN-11
        19 19-JAN-11 19-JAN-11

归档日志同步成功，主备库间归档日志能正常传送。查看主库switchover参数，处于可切换状态。

SQL> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- ----------------
TO STANDBY           PRIMARY

查看备库switchover参数，处于可切换状态。

SQL> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- ----------------
TO PRIMARY           PHYSICAL STANDBY

DG搭建工作至此初步完成，可以通过测试验证其功能，本测试中数据库处于最大性能模式（MAXIMUM PERFORMANCE），仅作为一个学习起点供读者参考。

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle11g 单机搭建DataGuard环境的测试

评论