IT开放社区

Oracle Data Guard环境搭建

 准备工作:


主机名
IP地址
db_name/SID
db_unique_name
tnsnames备注
主库
OL1
192.168.2.10
LDGG
ZHU
OL10

备库
OL2
192.168.2.20
LDGG
BEI
OL20只安装软件

【主库】数据库开启归档并强制生成日志;

[oracle@OL1 database]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 13 11:45:25 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> archive log list
Database log mode        No Archive Mode
Automatic archival        Disabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Current log sequence        6
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  830930944 bytes
Fixed Size      2257800 bytes
Variable Size    536874104 bytes
Database Buffers   289406976 bytes
Redo Buffers      2392064 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence        6
SQL> select force_logging from v$database;
FOR
---
YES
SQL>

【主库】创建standby logfile;(添加standby logfile原则,最大日志组数+1)

SQL> select group#,member from v$logfile;
    GROUP#
----------
MEMBER
---------------------------------------------
  3
/u01/app/oracle/oradata/LDGG/redo03.log
  2
/u01/app/oracle/oradata/LDGG/redo02.log
  1
/u01/app/oracle/oradata/LDGG/redo01.log

SQL> select group#,bytes/1024/1024 as m from v$log;                           
    GROUP#     M
---------- ----------
  1    50
  2    50
  3    50
SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/LDGG/sredo04.log') size 50m
;
Database altered.
SQL> alter database add standby logfile group 5('/u01/app/oracle/oradata/LDGG/sredo05.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 6('/u01/app/oracle/oradata/LDGG/sredo06.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 7('/u01/app/oracle/oradata/LDGG/sredo07.log') size 50m;
Database altered.
SQL> select group#,member from v$logfile;
    GROUP#
----------
MEMBER
---------------------------------------------
    3
/u01/app/oracle/oradata/LDGG/redo03.log
  2
/u01/app/oracle/oradata/LDGG/redo02.log
  1
/u01/app/oracle/oradata/LDGG/redo01.log
  4
/u01/app/oracle/oradata/LDGG/sredo04.log
  5
/u01/app/oracle/oradata/LDGG/sredo05.log
  6
/u01/app/oracle/oradata/LDGG/sredo06.log
  7
/u01/app/oracle/oradata/LDGG/sredo07.log

7 rows selected.
SQL>

【主库】修改参数;

追加内容可官方文档位置:Master Book List-->3 Creating a Physical Standby Database-->Example 3-1和Example 3-2中的内容,如下;

DB_NAME=chicago
DB_UNIQUE_NAME=chicagoLOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/chicago/ 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
 'SERVICE=boston ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=boston
DB_FILE_NAME_CONVERT='boston','chicago'
LOG_FILE_NAME_CONVERT='/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/' 
STANDBY_FILE_MANAGEMENT=AUTO

修改后追加内容:

DB_UNIQUE_NAME=ZHU
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ZHU,BEI)'
LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=ZHU'
LOG_ARCHIVE_DEST_2='SERVICE=OL20  ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=BEI'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=4
FAL_SERVER=OL20
DB_FILE_NAME_CONVERT='LDGG','LDGG'
LOG_FILE_NAME_CONVERT='LDGG','LDGG'
STANDBY_FILE_MANAGEMENT=AUTO

SQL> create pfile from spfile;
File created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@OL1 ~]$ cd $ORACLE_HOME/dbs
[oracle@OL1 dbs]$ ls
hc_OL1.dat  initOL1.ora  init.ora  lkOL1  orapwOL1  spfileOL1.ora
[oracle@OL1 dbs]$ vi initOL1.ora

 【主库】通过修改后pfile重新生成spfile并启动数据库;

[oracle@OL1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 13 12:29:31 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area  830930944 bytes
Fixed Size                  2257800 bytes
Variable Size             536874104 bytes
Database Buffers          289406976 bytes
Redo Buffers                2392064 bytes
Database mounted.
Database opened.
SQL> show parameter name
NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name       string
db_file_name_convert       string  LDGG, LDGG
db_name                    string  LDGG
db_unique_name             string  ZHU
global_names              boolean  FALSE
instance_name              string  LDGG
lock_name_space            string
log_file_name_convert      string  LDGG, LDGG
processor_group_name       string
service_names              string  ZHU
SQL>

 【主库】配置tnsnames;(默认service_name与db_unique_name相同)

[oracle@OL1 dbs]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@OL1 admin]$ ls
listener.ora  samples  shrept.lst  tnsnames.ora
[oracle@OL1 admin]$ vi tnsnames.ora
OL10 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ZHU)
    )
  )
OL20 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BEI)
    )
  )

【主库】拷贝相关文件至备库;

[oracle@OL1 admin]$ cd $ORACLE_HOME/dbs
[oracle@OL1 dbs]$ ls
hc_LDGG.dat  initLDGG.ora  init.ora  lkLDGG  lkZHU  orapwLDGG  spfileLDGG.ora
[oracle@OL1 dbs]$ scp orapwLDGG The authenticity of host '192.168.2.20 (192.168.2.20)' can't be established.
RSA key fingerprint is c9:55:8e:3e:dd:f5:0b:05:f5:4e:1d:ab:92:9a:de:6d.
Are you sure you want to continue connecting (yes/no)? y
Please type 'yes' or 'no': yes
Warning: Permanently added '192.168.2.20' (RSA) to the list of known hosts.
 password: 
orapwLDGG                                                          100% 1536     1.5KB/s   00:00    
[oracle@OL1 dbs]$ scp initLDGG.ora GG.ora 
oracle@192.168.2.20's
 password: 
initLDGG.ora                                                       100% 1415     1.4KB/s   00:00    
[oracle@OL1 dbs]$ scp /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora  oracle@192.168.2.20's
 password: 
tnsnames.ora                                                       100%  501     0.5KB/s   00:00    
[oracle@OL1 dbs]$

 【备库】创建静态监听并启动;

[oracle@OL2 admin]$ ls
listener.ora  samples  shrept.lst  tnsnames1707131PM0319.bak  tnsnames.ora
[oracle@OL2 admin]$ vi listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER =
  (sid_list=
    (sid_desc=
      (sid_name=LDGG)
      (oracle_home= /u01/app/oracle/product/11.2.0/db_1)
      (global_dbname= BEI)))

[oracle@OL2 admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JUL-2017 13:15:52
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/OL2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.20)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.20)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                13-JUL-2017 13:15:52
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/OL2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.20)(PORT=1521)))
Services Summary...
Service "BEI" has 1 instance(s).
  Instance "LDGG", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

 【备库】修改pfile参数文件;

[oracle@OL2 admin]$ cd $ORACLE_HOME/dbs
[oracle@OL2 dbs]$ ls
initLDGG.ora  init.ora  orapwLDGG
[oracle@OL2 dbs]$ vi initLDGG.ora

因主库备库DB_NAME和SID相同,修改后如下:

DB_UNIQUE_NAME=BEI
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ZHU,BEI)'
LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=BEI'
LOG_ARCHIVE_DEST_2='SERVICE=OL10  ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=ZHU'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=4
FAL_SERVER=OL10
DB_FILE_NAME_CONVERT='LDGG','LDGG'
LOG_FILE_NAME_CONVERT='LDGG','LDGG'
STANDBY_FILE_MANAGEMENT=AUTO

【备库】参照参数文件创建相应目录;

[oracle@OL2 dbs]$ mkdir -p /u01/app/oracle/admin/LDGG/adump
[oracle@OL2 dbs]$ mkdir -p /u01/app/oracle/oradata/LDGG
[oracle@OL2 dbs]$ mkdir -p /u01/app/oracle/FRA
[oracle@OL2 dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area
[oracle@OL2 dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/LDGG

【备库】通过pfile生成spfile启动数据库至nomount状态;

[oracle@OL2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 13 13:33:29 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount 
ORACLE instance started.
Total System Global Area  830930944 bytes
Fixed Size      2257800 bytes
Variable Size    536874104 bytes
Database Buffers   289406976 bytes
Redo Buffers      2392064 bytes
SQL>

【主库】使用RMAN auxiliary恢复数据库;(主库为open状态,备库为nomount状态,主库监听为READY状态,备库监听为UNKNOWN状态。)

[oracle@OL1 ~]$ rman target sys/oracle@OL10 auxiliary sys/oracle@OL20
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jul 13 15:03:09 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: LDGG (DBID=4049466052)
connected to auxiliary database: LDGG (not mounted)
RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;
。。。。。。。。。。。。。。。。过程省略不粘贴了。。。。。。。。。。。。。。。。。
executing Memory Script
executing command: SET until clause
Starting recover at 13-JUL-17
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/FRA/1_8_94923162
0.arcarchived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/FRA/1_9_94923162
0.arcarchived log file name=/u01/app/oracle/FRA/1_8_949231620.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/FRA/1_9_949231620.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-JUL-17
Finished Duplicate Db at 13-JUL-17
RMAN>

【备库】应用日志同步数据;

SQL> select database_role,open_mode from v$database;
DATABASE_ROLE  OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE  OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY
SQL>

 【备库】切换物理备库为Snapshot Standby;

SQL> select database_role,open_mode from v$database;
DATABASE_ROLE  OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
SQL> alter database convert to snapshot standby;
Database altered.
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE  OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED
SQL> alter database open;
Database altered.
SQL>

【备库】恢复至物理备库;

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  830930944 bytes
Fixed Size      2257800 bytes
Variable Size    536874104 bytes
Database Buffers   289406976 bytes
Redo Buffers      2392064 bytes
Database mounted.
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE  OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED
SQL> alter database convert to physical standby;
Database altered.
SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  830930944 bytes
Fixed Size      2257800 bytes
Variable Size    536874104 bytes
Database Buffers   289406976 bytes
Redo Buffers      2392064 bytes
Database mounted.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE  OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
SQL>

相关文章

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

网站分类
最新文章
    随机文章
      站点信息
      • 文章总数:113
      • 页面总数:2
      • 分类总数:3
      • 标签总数:0
      • 评论总数:0
      • 浏览总数:36173
      左邻右舍

      BlogPowerBy Z-BlogPHP 1.5 Zero ;Theme By 爱墙纸

      IT开放社区:京ICP备13044647号.初创于Oracle DBA实战班.邮箱:admin@itkaifang.com

      分享:

      支付宝

      微信