Oracle 11g R2 Data Guard 容灾系统构建S

2019-06-15 本文已影响47人 chimpansee

很少有企业很坦然面对这样的问题，“您的数据中心停运怎么办？“；”您的信息中心大楼遭到破坏怎么保证正常运营？”等等诸多的问题。在IT领域，业务连续性（BC）指的就是在主业务系统发生人为的或不可预知的故障时，为确保关键业务服务不中断而包含的一套标准、流程和系统。灾难恢复（DR）指的是在主数据中心遭受灾难时，导致核心业务系统停用，将IT资源复制到可继续运行的远程站点。

业务连续性和灾难恢复之间有不少交叉点，具体到企业时，往往面对成本和复杂性两方面挑战。

复杂性
灾难恢复由于涉及到太多的元素，覆盖企业全部流程管理几乎不可能。DR环节中关键的切换演练涉及到多方面的人，财，物协调。准备工作尤其复杂。近年来得益于虚拟化技术的快速演变，企业服务，桌面，存储的跨距离全面移动能力大大增强。在危机时刻，企业得以保持业务连续运营。
另一方面，自动化维运工具的不断成熟，也简化了，灾难恢复过程中面临的大量复杂操作，避免了进一步的人为错误和干扰。
成本
对于由人为错误或技术故障导致的数据丢失来说，本地恢复通常是最经济的选择。而对于自然灾害等导致的不可控灾难来说，远程恢复依然是最佳方案。当企业数据中心受到广泛灾难影响，其IT团队必须考虑所提供的服务的重要性等级问题。并非所有数据服务都是同等重要的，所以必须要弄清哪些服务是关键的第一级服务，哪些是第二级服务，以及这些应用程序关联了哪些不同的服务级别协议(SLA)。根据SLA对服务分级可确保所有业务关键应用程序的最佳正常运行时间，同时又能最有效地保证有限资源的投入。
普遍采用的WAN优化的复制可以消除物理数据运输，节省数据复制的开支。
为了达到最高保证的灾难恢复能力，需要投入可观的成本。下图是关于灾备的国家标准：

image.png

方案介绍

数据复制技术是容灾的关键，不同于传统的基于SAN镜像、基于LVM镜像的灾备方案，Oracle Data Gurad提供自底向上的有效的灾难恢复解决方案，该方案基于数据库的redo log复制。保证了用户在几分钟内就能恢复业务，最高能6级的灾难恢复能力。有如下示意图。

image.png

Data Gurad 通过在远端保存与原数据库事务一致的数据库备份，提供了有效的灾难恢复解决方案。按照redo log的应用方式不同，可以分为物理类型、逻辑类型和快照类型。我们这里先讨论物理类型的。
物理类型的有以下一些优点：

快速有效的错误恢复
能从主库卸载掉报表
能以只读方式打备用数据库。

模拟构建

本节将具体容灾系统构建，整个工作步骤分为以下几个部分：

容灾环境模拟准备
主数据库配置
物理备机创建

虚拟机：VMware® Workstation 12 Pro或更高版本。
操作系统:Oracle Linux 7.6
数据库：Oracle 11g R2

- 容灾环境模拟准备

主机环境配置
模拟了一个主备机的虚拟场景，如图：

image.png

实际应用中，要考虑到实际的网络，主机等情况进行适当相应调整，本方案仅仅是一个简化版本的data guard实现。

为模拟实现，我们用虚拟机搭建两台服务器，主机的基本系统的准备工作我们这里省略了。配置好的结果要求如下：
位于北京的主数据库服务器：
hostname :beijing
databasetype:primary
db_unique_name: BEIJING
db_name=orcl
tns alias:BEIJING
ipaddr:192.168.1.30

[oracle@beijing dbs]$ cat /etc/hostname
beijing
[root@beijing ~]# cat /etc/hosts
192.168.1.30    beijing
192.168.1.40    chengdu

防火墙关闭

[root@beijing ~]# systemctl stop firewalld

位于成都的备份数据库服务器：
hostname:chengdu
databasetype:standby
db_unique_name:chengdu
db_name:orcl
tns alias:chengdu

防火墙关闭

[root@chengdu ~]# systemctl stop firewalld

[oracle@chengdu dbs]$ cat /etc/hostname 
chengdu

[root@chengdu ~]# cat /etc/hosts
192.168.1.30    beijing
192.168.1.40    chengdu

2.数据库复制环境准备：

主备机oracle数据库软件的安装,我们这里选用11gr2版本进行测试和安装。（具体步骤省略）

安装中，主服务器安装并运行企业版数据库，备份服务器仅仅安装软件即可。
北京服务器oracle用户配置文件如下：

[oracle@beijing admin]$ cd
[oracle@beijing ~]$  cat .bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
export ORACLE_SID=orcl
export ORACLE_BASE=/home/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
PATH=$PATH:$ORACLE_HOME/bin
export PATH
export DISPLAY=localhost:10.0

成都服务器oracle用户配置文件如下：

[oracle@chengdu ~]$ cat .bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
export ORACLE_SID=orcl
export ORACLE_BASE=/home/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
PATH=$PATH:$ORACLE_HOME/bin
export PATH
export DISPLAY=localhost:10.0

为简化后面的数据库复制过程，我们设计主备的用户，目录完全相同。

静态侦听配置
考虑到使用RMAN来复制数据库，需要为数据库配置静态侦听。
成都服务器的静态侦听配置：

[oracle@chengdu admin]$ cat listener.ora 
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = chengdu)
    (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
    (SID_NAME = orcl)
  )
 )

配置口令文件

日志传输会用到口令文件。要求是主备机口令一致。
配置成都数据库口令文件

[oracle@chengdu dbs]$ cd $ORACLE_HOME/dbs
[oracle@chengdu dbs]$ orapwd file=orapworcl
Enter password for SYS:

配置北京数据库口令文件

[oracle@beijing ~]$ cd $ORACLE_HOME/dbs
[oracle@beijing dbs]$ orapwd file=orapworcl force=y
Enter password for SYS:

为备用机创建初始化参数文件
我们使用主数据库参数文件为模板，修改生成备用机的参数文件，具体见后面的主数据库参数配置中的具体内容。
配置tns alias
为北京服务器配置网络别名：

[oracle@beijing admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
beijing =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = beijing)
    )
  )
chengdu =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.40)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = chengdu)
    )
  )

为成都服务器配置网络别名：

[oracle@chengdu admin]$ cat tnsnames.ora 
beijing =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = beijing)
    )
  )
chengdu =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.40)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = chengdu)
    )
  )

两边实例都启动起来，测试下连通性。
北京服务器：
启动实例

[oracle@beijing ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 14 11:32:36 2019
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area  709836800 bytes
Fixed Size                  2231752 bytes
Variable Size             436208184 bytes
Database Buffers          264241152 bytes
Redo Buffers                7155712 bytes
Database mounted.
Database opened.
SQL> exit

启动侦听

[oracle@beijing ~]$ lsnrctl start
[oracle@beijing ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-JUN-2019 11:33:11
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                14-JUN-2019 11:26:53
Uptime                    0 days 0 hr. 6 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/beijing/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beijing)(PORT=1521)))
Services Summary...
Service "beijing" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

成都服务器启动实例：

[oracle@chengdu ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 14 11:36:13 2019
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup pfile=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora nomount
ORACLE instance started.
Total System Global Area  250560512 bytes
Fixed Size                  2227256 bytes
Variable Size             192938952 bytes
Database Buffers           50331648 bytes
Redo Buffers                5062656 bytes
SQL> exit

启动侦听：

[oracle@chengdu ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-JUN-2019 11:37:04
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                14-JUN-2019 11:33:42
Uptime                    0 days 0 hr. 3 min. 22 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/chengdu/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chengdu)(PORT=1521)))
Services Summary...
Service "chengdu" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

互通性测试
北京服务器：

[oracle@beijing ~]$ tnsping beijing

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-JUN-2019 11:45:23
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@beijing ~]$ tnsping chengdu
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-JUN-2019 11:45:27
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.40)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)
[oracle@beijing ~]$

成都服务器

[oracle@chengdu ~]$ tnsping beijing
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-JUN-2019 11:46:55
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@chengdu ~]$ tnsping chengdu
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-JUN-2019 11:46:58
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.40)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@chengdu ~]$

- 主数据库配置

1.启用归档模式

[oracle@beijing ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 14 11:54:50 2019
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>exit

[oracle@beijing ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 12 21:15:28 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  709836800 bytes
Fixed Size                  2231752 bytes
Variable Size             440402488 bytes
Database Buffers          260046848 bytes
Redo Buffers                7155712 bytes
Database mounted.
SQL> alter database archivelog;
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     60
Next log sequence to archive   62
Current log sequence           62
SQL>

强制日志模式启动

允许不进行日志记录的操作，但是改变仍然会放到重做日志流中。

[oracle@beijing dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 12 21:26:23 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database force logging;

Database altered.

SQL>

主数据库的初始化参数配置
可以直接创建pfile文件，然后修改，也可以使用alter system修改。

[oracle@beijing dbs]$ cat initorcl.ora
orcl.__db_cache_size=436207616
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=469762048
orcl.__sga_target=671088640
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=184549376
orcl.__streams_pool_size=0
*.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/home/oracle/app/oracle/oradata/ORCL/controlfile/o1_mf_gj7n6w3b                                                                                             _.ctl','/home/oracle/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_gj7n6w                                                                                             3q_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/app/oracle/oradata'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.db_unique_name='BEIJING'
*.diagnostic_dest='/home/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_config='DG_CONFIG=(beijing,chengdu)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST db_unique_name=beijing'
*.log_archive_dest_2='service=chengdu valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) d                                                                                             b_unique_name=chengdu'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='DEFER'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1135607808
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='BEIJING'
*.undo_tablespace='UNDOTBS1'

我们对data guard需要的几个参数做下说明：

*.db_unique_name='BEIJING'

用于在data guard中区分不同数据库。

*.log_archive_config='DG_CONFIG=(beijing,chengdu)'

用于定义Data Guard中的各个数据库的db_unique_name列表,也可用于控制向远端数据库发送日志或从远端接收日志。

*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST db_unique_name=beijing'

配置本地日志文件归档，这里使用默认的FRA。因为是location本地归档，所以db_unique_name是可选的，放这里可以清楚的表明各个数据库的关系。

*.log_archive_dest_2='service=chengdu valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) d                                                                                             
b_unique_name=chengdu'

配置远端的日志传输参数。

service指向远端的Net Service Name。我们通过chengdu这个Net Service Name将日志传送到成都远端。
valid_for基于数据库担当的角色和日志文件类型来决定是否生效，它的用法是：
valid_for=(redo_log_type,database_role)
其中redo_log_type=（ONLINE_LOGFILE | STANDBY_LOGFILE | ALL_LOGFILES）
database_role=（PRIMARY_ROLE | STANDBY_ROLE | ALL_ROLES）
这里参数的意义就是如果是主数据库，就将ONLINE_LOGFILES类型的日志发送到远端。ONLINE_LOGFILES也即是数据库正常工作时产生的日志。STANDBY_LOGFILE指的是额外建立的用于保存接收到的ONLINE_LOGFILES的日志。
db_unique_name 用于明确指出将发送至的备用数据库。

*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='DEFER'

分别表示dest_1和dest_2的启用状态，本地归档当然要启动，远程归档我们等备用数据库启用后，再启动。

备库的初始化参数配置
为省略部分参数的修改，简化安装，我们假设两台机器的目录都相同。我们采用基于主数据库配置文件进行修改，这样不容易出错。
修改好的pfile如下：

orcl.__db_cache_size=436207616
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=469762048
orcl.__sga_target=671088640
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=184549376
orcl.__streams_pool_size=0
*.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/home/oracle/app/oracle/oradata/ORCL/controlfile/o1_mf_gj7n6w3b_.ctl','/home/oracle/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_gj7n6w3q_.ctl'#Set by RMAN
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/app/oracle/oradata'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.db_unique_name='chengdu'
*.diagnostic_dest='/home/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='chengdu'
*.fal_server='beijing'
*.log_archive_config='DG_CONFIG=(beijing,chengdu)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST db_unique_name=chengdu'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1135607808
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='chengdu'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

需要保留和修改的参数我们说明下

*.log_archive_config='DG_CONFIG=(beijing,chengdu)'

用于表示data guard的组成，这个必须有的，而且每台机器都配置相同。如果有不一致，会提示 ORA-16047: DGID mismatch between destination setting and target database。

*.db_unique_name='chengdu'

data guard中用于唯一标识本数据库。

*.fal_client='chengdu'
*.fal_server='beijing'

配置Fetch Archive Log方法，用于消除可能出现的日志gap。
在这里我们从beijing取得日志，所以fal_server=beijing。同理fal_client=chengdu。

*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST db_unique_name=chengdu'
*.log_archive_dest_state_1='ENABLE'

配置本地的归档日志参数，我们定义为使用FRA。
最后还需要将相应的目录参数做一个调整，比如目录名中是beijing的要替换成chengdu。

使用rman复制备用数据库。
为简化过程，我们采用from active database直接从主数据库将控制文件，数据库文件推送到备用服务器，以创建一个新备用数据库。
考虑到我们已经配置好备用数据库的pfile了。所以我们就省略掉让rman拷贝spfile，然后修改这一步骤。

首先使用备用数据库参数文件启动实例

[oracle@chengdu dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 15 17:39:44 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup pfile='?/dbs/initorcl.ora' nomount;
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
SQL>

提示无法创建审计文件，因为没有建目录。

SQL> host mkdir -p /home/oracle/app/oracle/admin/orcl/adump
SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup pfile='?/dbs/initorcl.ora' nomount;
ORACLE instance started.
Total System Global Area 1135747072 bytes
Fixed Size                  2227664 bytes
Variable Size             687866416 bytes
Database Buffers          436207616 bytes
Redo Buffers                9445376 bytes
SQL>

好了，备库实例成功建立并处于nomount状态了。

复制数据库
复制之前我们检查下两边的侦听是否正常启动并运行。
检查备用数据库。

[oracle@chengdu dbs]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-JUN-2019 17:44:25

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                15-JUN-2019 17:43:54
Uptime                    0 days 0 hr. 0 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/chengdu/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chengdu)(PORT=1521)))
Services Summary...
Service "chengdu" has 2 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[oracle@chengdu dbs]$

检查下主数据库。

[oracle@beijing dbs]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-JUN-2019 17:45:01

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                15-JUN-2019 12:22:46
Uptime                    0 days 5 hr. 22 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/beijing/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beijing)(PORT=1521)))
Services Summary...
Service "BEIJING" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@beijing dbs]$

我们可以在主、备数据库来操作复制，启动rman。

[oracle@beijing dbs]$ rman
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jun 15 17:46:39 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect target sys/123456@beijing
connected to target database: ORCL (DBID=1538750300)
RMAN> connect auxiliary sys/123456@chengdu
connected to auxiliary database: ORCL (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
DORECOVER 
PFILE ?/dbs/initorcl.ora
NOFILENAMECHECK

过程提示错误，复制控制文件错误。

Starting backup at 15-JUN-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/15/2019 17:50:36
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/15/2019 17:50:36
ORA-17628: Oracle error 19505 returned by remote Oracle server

RMAN> exit

我们首先还是需要保证目录是创建好的，因为第一步就是将控制文件复制到备用数据库上，我们检查下目录，修补一下。

[oracle@chengdu dbs]$ ll /home/oracle/app/oracle/oradata/ORCL/controlfile/
ls: cannot access /home/oracle/app/oracle/oradata/ORCL/controlfile/: No such file or directory
[oracle@chengdu dbs]$ mkdir -p /home/oracle/app/oracle/oradata/ORCL/controlfile/
[oracle@chengdu dbs]$

重新来过。好了一切顺利，备用库创建起来了。

[oracle@beijing dbs]$ rman
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jun 15 18:02:08 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect target sys@beijing
target database Password:
connected to target database: ORCL (DBID=1538750300)
RMAN> connect auxiliary sys@chengdu
auxiliary database Password:
connected to auxiliary database: ORCL (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
PFILE ?/dbs/initorcl.ora
DORECOVER
NOFILENAMECHECK 2> 3> 4>
5>
6> ;
Starting Duplicate Db at 15-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl' auxiliary format
 '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl'   ;
}
executing Memory Script

中间日志省略掉了，继续到日志末尾了。

List of Cataloged Files
=======================
File Name: /home/oracle/app/oracle/fast_recovery_area/CHENGDU/archivelog/2019_06_15/o1_mf_1_12_0hu467b6_.arc

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=1011031399 file name=/home/oracle/app/oracle/oradata/CHENGDU/datafile/o1_mf_system_0du467al_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=1011031399 file name=/home/oracle/app/oracle/oradata/CHENGDU/datafile/o1_mf_sysaux_0eu467as_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=1011031399 file name=/home/oracle/app/oracle/oradata/CHENGDU/datafile/o1_mf_undotbs1_0fu467b3_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=1011031399 file name=/home/oracle/app/oracle/oradata/CHENGDU/datafile/o1_mf_users_0gu467b4_.dbf

contents of Memory Script:
{
   set until scn  1057096;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 15-JUN-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK
starting media recovery
archived log for thread 1 with sequence 12 is already on disk as file /home/oracle/app/oracle/fast_recovery_area/CHENGDU/archivelog/2019_06_15/o1_mf_1_12_0hu467b6_.arc
archived log file name=/home/oracle/app/oracle/fast_recovery_area/CHENGDU/archivelog/2019_06_15/o1_mf_1_12_0hu467b6_.arc thread=1 sequence=12
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-JUN-19
Finished Duplicate Db at 15-JUN-19
RMAN>

最后一步还根据命令行参数dorecover做了recovery。

我们检查下备用数据库状态。

[oracle@chengdu dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 15 18:05:52 2019
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>  select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/ORCL/controlfile/o1_mf_gj7n6w3b_.ctl
/home/oracle/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_gj7n6w3q_.ctl

SQL> select name from v$dbfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/CHENGDU/datafile/o1_mf_users_0gu467b4_.dbf
/home/oracle/app/oracle/oradata/CHENGDU/datafile/o1_mf_undotbs1_0fu467b3_.dbf
/home/oracle/app/oracle/oradata/CHENGDU/datafile/o1_mf_sysaux_0eu467as_.dbf
/home/oracle/app/oracle/oradata/CHENGDU/datafile/o1_mf_system_0du467al_.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/CHENGDU/onlinelog/o1_mf_3_gj9jm8sf_.log
/home/oracle/app/oracle/fast_recovery_area/CHENGDU/onlinelog/o1_mf_3_gj9jm8ym_.log

/home/oracle/app/oracle/oradata/CHENGDU/onlinelog/o1_mf_2_gj9jm8dd_.log
/home/oracle/app/oracle/fast_recovery_area/CHENGDU/onlinelog/o1_mf_2_gj9jm8kk_.log

/home/oracle/app/oracle/oradata/CHENGDU/onlinelog/o1_mf_1_gj9jm7yo_.log
/home/oracle/app/oracle/fast_recovery_area/CHENGDU/onlinelog/o1_mf_1_gj9jm83r_.log
6 rows selected.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           0
SQL>

除了没有归档，貌似所有文件都过来了。

启用归档传送

[oracle@beijing dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 15 18:10:47 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col error for a30
SQL> col dest_name for a40
SQL> set linesize 150
SQL> SELECT  DEST_ID, error, DEST_NAME,  STATUS,  TYPE,  SRL,  RECOVERY_MODE FROM  V$ARCHIVE_DEST_STATUS WHERE  DEST_ID in (1,2);

   DEST_ID ERROR                          DEST_NAME                                STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ ---------------------------------------- --------- -------------- --- -----------------------
         1                                LOG_ARCHIVE_DEST_1                       VALID     LOCAL          NO  IDLE
         2                                LOG_ARCHIVE_DEST_2                       DEFERRED  PHYSICAL       YES IDLE

SQL>
SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> SELECT  DEST_ID, error, DEST_NAME,  STATUS,  TYPE,  SRL,  RECOVERY_MODE FROM  V$ARCHIVE_DEST_STATUS WHERE  DEST_ID in (1,2);

   DEST_ID ERROR                          DEST_NAME                                STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ ---------------------------------------- --------- -------------- --- -----------------------
         1                                LOG_ARCHIVE_DEST_1                       VALID     LOCAL          NO  IDLE
         2                                LOG_ARCHIVE_DEST_2                       VALID     PHYSICAL       NO  IDLE

SQL>
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     12
Next log sequence to archive   14
Current log sequence           14
SQL>

好了，我们上备用数据库检查下日志情况。

[oracle@chengdu dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 15 18:17:19 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     13
Next log sequence to archive   0
Current log sequence           14
SQL>

好了，成功传送日志了。

启用托管恢复MRP

SQL>
SQL> alter database recover managed standby database disconnect;

Database altered.

SQL>

看看alert日志。

Sat Jun 15 18:20:04 2019
alter database recover managed standby database disconnect
Attempt to start background Managed Standby Recovery process (orcl)
Sat Jun 15 18:20:04 2019
MRP0 started with pid=27, OS id=22405
MRP0: Background Managed Standby Recovery process started (orcl)
 started logmerger process
Sat Jun 15 18:20:09 2019
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /home/oracle/app/oracle/fast_recovery_area/CHENGDU/archivelog/2019_06_15/o1_mf_1_13_gj9k9obc_.arc
Media Recovery Waiting for thread 1 sequence 14 (in transit)
Completed: alter database recover managed standby database disconnect

貌似成功结束了。

简单做个日志传输测试。
在主数据库上创建个测试表

[oracle@beijing dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 15 18:20:40 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table tttt as select * from dba_users;

Table created.

SQL> select count(*) from tttt;

  COUNT(*)
----------
        30

SQL>

我们上备机看看。咦，没有显示出期望的结果。

SQL>
SQL> select count(*) from tttt;
select count(*) from tttt
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

我们在主机上做个日志切换

SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     13
Next log sequence to archive   15
Current log sequence           15

再看看备机

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     13
Next log sequence to archive   0
Current log sequence           15
SQL> select count(*) from tttt;

  COUNT(*)
----------
        30

SQL>

终于有同步结果了，不过这需要日志切换后才能看结果呀。按照文章前面的灾难备份级别中也就差不多位于4级。
确实，缺省配置的备机，只能应用归档日志，也就无法实时同步主机的数据变化。日志切换期间如果出现故障，会丢掉所有数据，不能算是安全级别高的方案。而且本文中也没有配置主备切换的选项，无法实现主备的切换。
不过，下一篇data guard文章中，我们计划实现更多高级的data guard功能。

chimpansee 06-15

Oracle 11g R2 Data Guard 容灾系统构建S

目录

容灾概述

方案介绍

模拟构建

- 容灾环境模拟准备

- 主数据库配置

猜你喜欢

热点阅读