容灾技术Data Guard搭建

Oracle Data Guard容灾技术是一种备份容灾策略，简称DG。以下是DG的搭建过程：

准备条件：两台虚拟机，一台装载主库，另一台装载备库（备库为空库，只有安装好oracle软件）
----主库： PROD/IP:192.168.2.6
----备库：  PROD/IP:192.168.2.4   # 备库名PROD（提前定义）要与主库名一致。

--关闭主库，从spfile创建pfile：
SQL> create pfile from spfile;

File created.

--在主库修改参数文件：
[oracle@enmo dbs]$ vi initPROD.ora
PROD.__db_cache_size=352321536
PROD.__java_pool_size=4194304
PROD.__large_pool_size=8388608
PROD.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PROD.__pga_aggregate_target=335544320
PROD.__sga_target=503316480
PROD.__shared_io_pool_size=0
PROD.__shared_pool_size=130023424
PROD.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'                    #审计文件目录
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/PROD/ora_control1.ctl','/u01/app/oracle/oradata/PROD/ora_control2.ctl'    #控制文件对象
*.db_block_size=8192
*.db_domain='oracle.com'            #domain名
*.db_name='PROD'
*.db_recovery_file_dest_size=3221225472
*.db_recovery_file_dest='/u01/app/FRA/'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.memory_target=800M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

DB_UNIQUE_NAME=PROD                       #主库唯一库名
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,ENMO)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/home/oracle/arch/PROD/        #主库本地归档日志存放目录
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=PROD'
LOG_ARCHIVE_DEST_2=
 'SERVICE=ENMO ASYNC                     #此处ENMO只是作为连接备库ENMO库的网络链接串（tnsnames)
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=ENMO'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc            #主库备库的归档日志文件命名方式的定义
LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=ENMO                            #备库库名
DB_FILE_NAME_CONVERT='ENMO','PROD'
LOG_FILE_NAME_CONVERT=
 '/home/oracle/arch/ENMO/','/home/oracle/arch/PROD/'              #备库主库存放日志文件目录的交换，可以简写为：'ENMO','PROD'
STANDBY_FILE_MANAGEMENT=AUTO


--从pfile生成spfile，测试打开主库，并强制开启force logging：
SQL> create spfile from pfile;
File created.

SQL> startup
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             541068368 bytes
Database Buffers          289406976 bytes
Redo Buffers                2371584 bytes
Database mounted.
Database opened.
SQL> 
SQL> alter database force logging;
Database altered.

SQL> select force_logging from v$database;

--查看主库的重做日志文件组：
SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/PROD/redo01.log
         2 /u01/app/oracle/oradata/PROD/redo02.log
         3 /u01/app/oracle/oradata/PROD/redo03.log

--在主库添加standby日志组（添加规则:普通日志文件<=standby日志文件，且文件大小对应一致 ：
SQL> alter database add standby logfile group 4
  2  ('/u01/app/oracle/oradata/PROD/redo04_staby.log',
  3   '/u01/app/oracle/oradata/PROD/redo05_staby.log')
  4  size 10M;
Database altered.

SQL> alter database add standby logfile group 5
  2  ('/u01/app/oracle/oradata/PROD/redo06_staby.log',
  3   '/u01/app/oracle/oradata/PROD/redo07_staby.log')
  4  size 10M;
Database altered.

SQL> alter database add standby logfile group 6
  2  ('/u01/app/oracle/oradata/PROD/redo08_staby.log',
  3   '/u01/app/oracle/oradata/PROD/redo09_staby.log')
  4  size 10M;
Database altered.

SQL> alter database add standby logfile group 7
  2  ('/u01/app/oracle/oradata/PROD/redo010_staby.log',
  3   '/u01/app/oracle/oradata/PROD/redo011_staby.log')
  4  size 10M;
Database altered.

--增加后查看所有的日志文件：
SQL> select group#,member from v$logfile;
    GROUP# MEMBER
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/PROD/redo01.log
         2 /u01/app/oracle/oradata/PROD/redo02.log
         3 /u01/app/oracle/oradata/PROD/redo03.log
         4 /u01/app/oracle/oradata/PROD/redo04_staby.log
         4 /u01/app/oracle/oradata/PROD/redo05_staby.log
         5 /u01/app/oracle/oradata/PROD/redo06_staby.log
         5 /u01/app/oracle/oradata/PROD/redo07_staby.log
         6 /u01/app/oracle/oradata/PROD/redo08_staby.log
         6 /u01/app/oracle/oradata/PROD/redo09_staby.log
         7 /u01/app/oracle/oradata/PROD/redo010_staby.log
         7 /u01/app/oracle/oradata/PROD/redo011_staby.log
                 
--从主库复制pfile参数文件与密码文件到备库的主机上：
[oracle@enmo dbs]$ ls
hc_OCMU.dat  initOCMU.ora  init.ora.bck  initPROD.ora.bck  lkPROD     snapcf_OCMU.f  spfileOCMU.ora
hc_PROD.dat  init.ora      initPROD.ora  lkORA11GR2        orapwPROD  snapcf_PROD.f  spfilePROD.ora
[oracle@enmo dbs]$ scp initPROD.ora orapwPROD 192.168.2.4:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
oracle@192.168.2.4's password: 
initPROD.ora                                                                            100% 1432     1.4KB/s   00:00    
orapwPROD                                                                               100% 1536     1.5KB/s   00:00

----配置静态监听，相互访问：
---主库PROD库静态监听配置：
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
     (GLOBAL_DBNAME=PROD.oracle.com)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
     (SID_NAME=PROD))
  )

--主库PROD库tns配置：
ENMO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ENMO.oracle.com)
    )
  )

--主库PROD主库启动并注册监听：
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                18-OCT-2016 16:40:46
Uptime                    1 days 3 hr. 2 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/enmo/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=enmo.oracle.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORCLXDB.oracle.com" has 1 instance(s).
  Instance "PROD", status READY, has 1 handler(s) for this service...
Service "PROD.oracle.com" has 2 instance(s).
  Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
  Instance "PROD", status READY, has 1 handler(s) for this service...
The command completed successfully

--备库ENMO库静态监听配置：
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
     (GLOBAL_DBNAME=ENMO.oracle.com)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
     (SID_NAME=ORA11GR2 ))
  )

--备库ENMO库tns配置:
PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.6)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =  PROD.oracle.com)
    )
  )

--备库ENMO备库启动并注册监听：
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                19-OCT-2016 19:51:01
Uptime                    0 days 0 hr. 2 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ENMO.oracle.com" has 2 instance(s).
  Instance "ENMO", status BLOCKED, has 1 handler(s) for this service...
  Instance "ORA11GR2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
监听的配置与备库参数文件的先后顺序没有要求，这都是自己安排设计的。

--在备库ENMO库的参数文件修改：
*.audit_file_dest='/u01/app/oracle/admin/enmo/adump'                #备库的审计文件目录
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/ENMO/ora_control1.ctl','/u01/app/oracle/oradata/ENMO/ora_control2.ctl'   #备库ENMO库控制文件对象
*.db_block_size=8192
*.db_domain='oracle.com'
*.db_name='PROD'                   #备库名与主库名保持一致
*.db_recovery_file_dest_size=3221225472
*.db_recovery_file_dest='/u01/app/FRA/'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.memory_target=800M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=ENMO             #备库的唯一库名
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,ENMO)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/home/oracle/arch/ENMO/                          #归档日志文件存放目录
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=ENMO'
LOG_ARCHIVE_DEST_2=
 'SERVICE=PROD ASYNC                                        #此处PROD只是作为连接备库PROD库的网络链接串（tnsnames)
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=PROD'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc                            #主库备库的归档日志文件命名方式的定义
LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=PROD                                            #备库库名
DB_FILE_NAME_CONVERT='PROD','ENMO'
LOG_FILE_NAME_CONVERT='PROD','ENMO'
STANDBY_FILE_MANAGEMENT=AUTO
在备库修改pfile参数文件后，从pfile文件生成spfile文件，并启动实例到nomount状态：

--在主库复制文件到备库：
[oracle@enmo ~]$ rman target / auxiliary sys/oracle@enmo
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Oct 19 20:22:10 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PROD (DBID=336361349)
connected to auxiliary database: PROD (not mounted)

RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;
Starting Duplicate Db at 19-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwPROD' auxiliary format 
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwENMO'   ;
}
executing Memory Script
Starting backup at 19-OCT-16

... ...

executing Memory Script
executing command: SET until clause
Starting recover at 19-OCT-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 96 is already on disk as file /home/oracle/arch/ENMO/1_96_924523013.arc
archived log for thread 1 with sequence 97 is already on disk as file /home/oracle/arch/ENMO/1_97_924523013.arc
archived log file name=/home/oracle/arch/ENMO/1_96_924523013.arc thread=1 sequence=96
archived log file name=/home/oracle/arch/ENMO/1_97_924523013.arc thread=1 sequence=97
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-OCT-16
Finished Duplicate Db at 19-OCT-16                        #完成把主库所有文件复制到备库
RMAN> 
完成文件的移动。

--备库同步数据：
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED

SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;  #打开使用日志文件功能，是主库备库保持同步。
Media recovery complete.
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

SQL> recover managed standby database cancel;                                         #关闭使用日志文件功能
Media recovery complete.

--查看备库使用日志的状况：
SQL> select SEQUENCE#,APPLIED from v$archived_log;
 SEQUENCE# APPLIED
---------- ---------
        97 YES
        96 YES
        98 YES
        99 YES
       100 YES
         1 NO
       101 YES
         2 NO
         3 NO
       105 YES
       106 YES
 SEQUENCE# APPLIED
---------- ---------
       103 YES
       102 YES
       104 YES
       107 YES
       108 YES
       109 YES
       110 YES
       111 NO
       111 YES
       112 NO
       112 YES
22 rows selected.

--snapshot standby：
--Oracle 11g物理Data Guard之Snapshot Standby数据库功能 ：

SQL> alter database convert to snapshot standby;
Database altered.
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;

OPEN_MODE            PROTECTION_MODE      SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE           MAXIMUM PERFORMANCE  NOT ALLOWED          SNAPSHOT STANDBY

备库的角色有两种：一种是PHYSICAL STANDBY，另一种是SNAPSHOT STANDBY。
备库的这两种角色可以通过alter database convert to snapshot standby;
与alter database convert to physical standby;相互转换。snapshot standby角色
只是作为测试角色，没有使用日志文件的功能，所以一般是保持physical standby角色。

--打开备库：
SQL> alter database open;
Database altered.

--备库的状态信息：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;

OPEN_MODE            PROTECTION_MODE      SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE           MAXIMUM PERFORMANCE  NOT ALLOWED          SNAPSHOT STANDBY

--主库的状态信息：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE            PROTECTION_MODE      SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE           MAXIMUM PERFORMANCE  TO STANDBY           PRIMARY

--备库不开启（或者监听不启动）时主库的状态：
SQL>  select open_mode,protection_mode,switchover_status,database_role from v$database;

OPEN_MODE            PROTECTION_MODE      SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE           MAXIMUM PERFORMANCE  FAILED DESTINATION   PRIMARY

到这里，Datab Guard（简称DG）已经搭建完成。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2126841/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31392094/viewspace-2126841/