搭建11.2.0.4版本 DG(单实例对单实例)

雅冰石

已于 2024-04-24 17:50:26 修改

阅读量730

点赞数

分类专栏： # ORACLE 安装配置 1 ORACLE 学习文章标签：数据库

于 2019-11-09 20:20:25 首次发布

本文链接：https://blog.csdn.net/yabingshi_tech/article/details/102991141

版权

1 ORACLE 学习同时被 2 个专栏收录

296 篇文章 4 订阅

订阅专栏

ORACLE 安装配置

21 篇文章 0 订阅

订阅专栏

试验环境：

1.1 安装主库

可参考：CentOS下安装Oracle 11.2.0.4(静默安装)_oracle11.2.0.4-CSDN博客

1.2 主库准备工作

1.2.1 启用Forced Logging

select force_logging from v$database; #查看是否启用force logging

ALTER DATABASE FORCE LOGGING; #启用force logging

1.2.2 配置主库接收Redo Data

此选项可选，但是Oracle推荐实施该步骤。

1.2.2.1 创建和管理Standby Redo Log

Standby redo log大小应该和源端的redo log一样

Standby redo log组应该比对源端的redo log组至少多一个

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/orcl/standby01.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/orcl/standby02.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/orcl/standby03.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/orcl/standby04.log') SIZE 50M;

#检查是否创建成功

select group#,thread#,sequence#,bytes,status from v$standby_log;

1.2.2.2 配置Standby Redo Log Archival

1.2.2.2.1 开启归档

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

1.2.3 设置主库初始化参数

1.2.3.1 创建pfile文件

create pfile from spfile;

1.2.3.2 修改pfile文件

cd $ORACLE_HOME/dbs

vi initorcl.ora

DB_NAME=orcl
DB_UNIQUE_NAME=orcl
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,bakorcl)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/u01/app/oracle/archlog/ 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=orcl'
LOG_ARCHIVE_DEST_2=
 'SERVICE=bakorcl ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=bakorcl'
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=bakorcl
DB_FILE_NAME_CONVERT='bakorcl','orcl'
LOG_FILE_NAME_CONVERT='bakorcl','orcl' 
STANDBY_FILE_MANAGEMENT=AUTO

参数含义介绍：

DB_UNIQUE_NAME:为每个数据库指定唯一的名称此名称保留在数据库中，并且不会更改，即使主数据库和备用数据库反转角色也是如此。

log_archive_config:启用或禁用将重做日志发送到远程目标和接收远程重做日志，并为数据保护配置中的每个数据库指定唯一的数据库名称（db_unique_name）。

LOG_ARCHIVE_DEST_n指定redo data在主系统和备用系统上的归档位置。

LOG_ARCHIVE_DEST_1将主数据库从本地联机重做日志文件生成的重做数据归档到本地归档日志文件/arch1/orcl/。

LOG_ARCHIVE_DEST_2：该选项只对主库有效。它将重做数据传输到远程物理备库目的地bakorcl。

LOG_ARCHIVE_DEST_STATE_n：指定enable以允许传输redo data到目标端FAL_SERVER：指定fal服务器的oracle net服务名称（通常这是以主要角色运行的数据库）。当Orcl数据库以备用角色运行时，它使用Bakorcl数据库作为FAL服务器，如果Bakorcl无法自动发送丢失的日志文件，则从中获取（请求）丢失的已存档重做日志文件。

1.2.3.3 以pfile方式重启主库

shutdown immediate;

startup pfile='/u01/app/oracle/product/11.2.0.4/db_1/dbs/initorcl.ora';

1.2.3.4 创建spfile

create spfile from pfile;

shutdown immediate;

startup; #用spfile方式启动数据库

1.2.4 开启归档

如若未开启归档，则需要执行：

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

1.3 备库准备工作

1.3.1 检查确认硬件和软件是否满足需求

可参考主库安装文档里的‘1. 检查硬件要求’及‘2. 检查软件要求’

1.3.2 对OS进行配置

1.3.2.1 创建相关用户和用户组

groupadd oinstall

groupadd oper

groupadd dba

useradd -g oinstall -G dba,oper oracle

passwd oracle

1.3.2.2 创建相关目录

mkdir -p /u01/app/oracle/product/11.2.0.4/db_1

mkdir -p /u01/app/oracle/oradata/bakorcl

mkdir -p /u01/app/oracle/flash_recovery_area

mkdir -p /u01/app/oracle/fast_recovery_area/bakorcl

mkdir -p /u01/app/oracle/admin/bakorcl/adump

mkdir -p /u01/app/oracle/archlog

chown -R oracle:oinstall /u01

1.3.2.3 配置内核参数

vi /etc/sysctl.conf

修改以下各参数，最小设置如下(如果参数值比下面的大，则保持不变即可)：

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 536870912

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

--注意，这里kernel.shmmax 仅设置的500M，请根据实际情况调大些(

初始化参数MEMORY_TARGET或MEMORY_MAX_TARGET不能大于共享内存,所以该参数值需要大于MEMORY_TARGET

)。

运行sysctl -p应用以上参数

/sbin/sysctl -p

1.3.2.4 配置资源限制

vi /etc/security/limits.conf

新增如下内容：

oracle soft nofile 65536

oracle hard nofile 65536

oracle soft nproc 16384

oracle hard nproc 16384

oracle stack nproc 10240

1.3.2.5 配置oracle用户环境变量

su - oracle

vi .bash_profile

添加：

# Oracle Settings

COLUMNS=132; export COLUMNS

LINES=47; export LINES

EDITOR=vi; export EDITOR

TMP=/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1; export ORACLE_HOME

ORACLE_SID=bakorcl; export ORACLE_SID

SHLIB_PATH=$LD_LIBRARY_PATH;export SHLIB_PATH

NLS_LANG=American_America.zhs16gbk;export NLS_LANG

ORA_NLS10=$ORACLE_HOME/nls/data;export ORA_NLS10

TNS_ADMIN=$ORACLE_HOME/network/admin;export TNS_ADMIN

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

alias glance=/opt/perf/bin/glance:x

alias gohome='cd /u01/app/oracle/product/11.2.0.4'

alias cdb='cd /u01/app/oracle/admin/bakorcl/bdump'

alias cdu='cd /u01/app/oracle/admin/bakorcl/udump'

修改完执行：source .bash_profile生效。

--注意：这里的ORACLE_SID一定要确保设置正确，不能和主库名称相同。否则无法配置DG。且从主库连接备库时，会提示账号密码错误。

1.3.3 安装软件

从主库上将database安装包拷贝到备库上

scp -r /download/database/ 10.192.203.109:/download/

1.3.3.1 新建静默安装的应答文件

从主库将配置过的文件拷贝到备库

cd /download/database/response/

scp db_install.rsp 10.192.203.109:/download/database/response/db_install.rsp

改下db_install.rsp里的ORACLE_HOSTNAME。

1.3.3.2 静默安装Oracle

su - oracle

cd /download/database/

./runInstaller -silent -responseFile /download/database/response/db_install.rsp

执行成功后，按提示执行下面两个脚本：

sh /u01/app/oraInventory/orainstRoot.sh

sh /u01/app/oracle/product/11.2.0.4/db_1/root.sh

1.4 创建Physical Standby Database

1.4.1 拷贝主库的密码文件和pfile文件到备库

cd $ORACLE_HOME/dbs

scp orapworcl oracle@10.192.203.109:/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwbakorcl

scp initorcl.ora oracle@10.192.203.109:/u01/app/oracle/product/11.2.0.4/db_1/dbs/initbakorcl.ora

1.4.2 修改备库的pfile文件

确保主备的实例名正确设置(有些地方需要将主库实例名替换成备库实例名；有些地方需要颠倒下主备实例名)：

[oracle@ZooKeeper-node3 dbs]$ cat initbakorcl.ora

bakorcl.__db_cache_size=79691776
bakorcl.__java_pool_size=4194304
bakorcl.__large_pool_size=71303168
bakorcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
bakorcl.__pga_aggregate_target=163577856
bakorcl.__sga_target=255852544
bakorcl.__shared_io_pool_size=0
bakorcl.__shared_pool_size=92274688
bakorcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/bakorcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/bakorcl/control01.ctl','/u01/app/oracle/fast_recovery_area/bakorcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='bakorcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=417333248
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

LOG_ARCHIVE_CONFIG='DG_CONFIG=(bakorcl,orcl)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/u01/app/oracle/archlog/ 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=bakorcl'
LOG_ARCHIVE_DEST_2=
 'SERVICE=orcl ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=orcl'
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=orcl
DB_FILE_NAME_CONVERT='orcl','bakorcl'
LOG_FILE_NAME_CONVERT='orcl','bakorcl' 
STANDBY_FILE_MANAGEMENT=AUTO

1.4.3 启动备库实例

将备库实例启动到nomount

[oracle@cloudp-dxpt-08 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 4 16:08:52 2018

Connected to an idle instance.

SQL> create spfile from pfile

File created.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size 2260088 bytes

Variable Size 2936013704 bytes

Database Buffers 1325400064 bytes

Redo Buffers 12107776 bytes

1.4.4 配置监听

cd $ORACLE_HOME/network/admin

[oracle@ZooKeeper-node3 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.



LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = ZooKeeper-node3)(PORT = 1521))

    )

  )



ADR_BASE_LISTENER = /u01/app/oracle



SID_LIST_LISTENER=

  (SID_LIST=

    (SID_DESC=

      (GLOBAL_DBNAME=bakorcl)

      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)

      (SID_NAME=bakorcl)))

#启动监听

lsnrctl start

1.4.5 配置TNS

在主库上配置

[oracle@ZooKeeper-node2 admin]$ cat tnsnames.ora

bakorcl =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ZooKeeper-node3 )(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = bakorcl

)

在备库上配置
orcl =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ZooKeeper-node2 )(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl

)

在主从上分别tnsping下看能否ping通，假如报错：

TNS-12545: Connect failed because target host or object does not exist

需要修改/etc/hosts文件，添加下对方的ip及主机名对应关系，如在备库的/etc/hosts添加：

10.192.203.108 ZooKeeper-node2

这里需要重启下主数据库，否则会影响后面的主备同步(主库产生归档日志后，无法同步到备库上)。

原因：

归档进程在数据库启动后，只会在初始化过程中读取一次tnsnames.ora的信息，以后tnsnames.ora发生变化等情况，arch进程并不知晓。

1.4.6 主备数据同步

在主库上通过rman duplicate方式进行备库恢复

[oracle@ZooKeeper-node2 admin]$ rman target / auxiliary sys/orcl@bakorcl nocatalog

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Nov 8 20:06:56 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1551494972)

using target database control file instead of recovery catalog

connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;

Starting Duplicate Db at 08-NOV-19

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.4/db_1/dbs/orapworcl' auxiliary format

 '/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwbakorcl'   ;

}

executing Memory Script

Starting backup at 08-NOV-19

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=32 device type=DISK

Finished backup at 08-NOV-19

contents of Memory Script:

{

   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/bakorcl/control01.ctl';

   restore clone controlfile to  '/u01/app/oracle/fast_recovery_area/bakorcl/control02.ctl' from

 '/u01/app/oracle/oradata/bakorcl/control01.ctl';

}

executing Memory Script

Starting backup at 08-NOV-19

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_orcl.f tag=TAG20191108T200711 RECID=4 STAMP=1023826031

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 08-NOV-19

Starting restore at 08-NOV-19

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 08-NOV-19

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

 "/u01/app/oracle/oradata/bakorcl/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/bakorcl/system01.dbf";

   set newname for datafile  2 to

 "/u01/app/oracle/oradata/bakorcl/sysaux01.dbf";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/bakorcl/undotbs01.dbf";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/bakorcl/users01.dbf";

   backup as copy reuse

   datafile  1 auxiliary format

 "/u01/app/oracle/oradata/bakorcl/system01.dbf"   datafile

 2 auxiliary format

 "/u01/app/oracle/oradata/bakorcl/sysaux01.dbf"   datafile

 3 auxiliary format

 "/u01/app/oracle/oradata/bakorcl/undotbs01.dbf"   datafile

 4 auxiliary format

 "/u01/app/oracle/oradata/bakorcl/users01.dbf"   ;

   sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/bakorcl/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 08-NOV-19

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf

output file name=/u01/app/oracle/oradata/bakorcl/system01.dbf tag=TAG20191108T200722

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

output file name=/u01/app/oracle/oradata/bakorcl/sysaux01.dbf tag=TAG20191108T200722

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

output file name=/u01/app/oracle/oradata/bakorcl/undotbs01.dbf tag=TAG20191108T200722

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf

output file name=/u01/app/oracle/oradata/bakorcl/users01.dbf tag=TAG20191108T200722

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 08-NOV-19

sql statement: alter system archive log current

contents of Memory Script:

{

   backup as copy reuse

   archivelog like  "/u01/app/oracle/archlog/1_6_1023714239.arc" auxiliary format

 "/u01/app/oracle/fast_recovery_area/BAKORCL/archivelog/2019_11_08/o1_mf_1_6_%u_.arc"   archivelog like

 "/u01/app/oracle/archlog/1_7_1023714239.arc" auxiliary format

 "/u01/app/oracle/fast_recovery_area/BAKORCL/archivelog/2019_11_08/o1_mf_1_7_%u_.arc"   ;

   catalog clone recovery area;

   switch clone datafile all;

}

executing Memory Script

Starting backup at 08-NOV-19

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=6 RECID=5 STAMP=1023826044

output file name=/u01/app/oracle/fast_recovery_area/BAKORCL/archivelog/2019_11_08/o1_mf_1_6_09ugcm7a_.arc RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=7 RECID=6 STAMP=1023826153

output file name=/u01/app/oracle/fast_recovery_area/BAKORCL/archivelog/2019_11_08/o1_mf_1_7_0augcm7b_.arc RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

Finished backup at 08-NOV-19

searching for all files in the recovery area

List of Files Unknown to the Database

=====================================

File Name: /u01/app/oracle/fast_recovery_area/BAKORCL/archivelog/2019_11_08/o1_mf_1_7_0augcm7b_.arc

File Name: /u01/app/oracle/fast_recovery_area/BAKORCL/archivelog/2019_11_08/o1_mf_1_6_09ugcm7a_.arc

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app/oracle/fast_recovery_area/BAKORCL/archivelog/2019_11_08/o1_mf_1_7_0augcm7b_.arc

File Name: /u01/app/oracle/fast_recovery_area/BAKORCL/archivelog/2019_11_08/o1_mf_1_6_09ugcm7a_.arc

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=1023826156 file name=/u01/app/oracle/oradata/bakorcl/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=5 STAMP=1023826156 file name=/u01/app/oracle/oradata/bakorcl/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=1023826156 file name=/u01/app/oracle/oradata/bakorcl/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=7 STAMP=1023826156 file name=/u01/app/oracle/oradata/bakorcl/users01.dbf

contents of Memory Script:

{

   set until scn  975497;

   recover

   standby

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 08-NOV-19

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/fast_recovery_area/BAKORCL/archivelog/2019_11_08/o1_mf_1_6_09ugcm7a_.arc

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/BAKORCL/archivelog/2019_11_08/o1_mf_1_7_0augcm7b_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/BAKORCL/archivelog/2019_11_08/o1_mf_1_6_09ugcm7a_.arc thread=1 sequence=6

archived log file name=/u01/app/oracle/fast_recovery_area/BAKORCL/archivelog/2019_11_08/o1_mf_1_7_0augcm7b_.arc thread=1 sequence=7

media recovery complete, elapsed time: 00:00:01

Finished recover at 08-NOV-19

Finished Duplicate Db at 08-NOV-19

恢复完后，备库自动启动到了mount状态。

1.4.7 启动redo apply

备库开启应用日志：

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

--DISCONNECT FROM SESSION表示在后台运行。USING CURRENT LOGFILE确保一接收redo log就能被应用。

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE

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

PHYSICAL STANDBY MOUNTED

1.4.8 将数据库启动到read only状态

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> 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 READ ONLY WITH APPLY

1.4.9 检查standby数据库是否正常运行

1.4.9.1 检查log_archive_dest_state_n状态

需要确保log_archive_dest_state_1/2的状态是ENABLE。我遇到过一次状态是RESET，导致主库的归档日志无法传输到备库上。

show parameter log_archive_dest_state_1;

show parameter log_archive_dest_state_2;

1.4.9.2 检查standby log的状态是否正常

SQL> select group#,sequence#,first_change#,next_change#,status,used from v$standby_log;

如果都为UNASSIGNED，则说明备库异常，是active状态方为正常。

1.4.9.3 检查主库中归档位置配置是否有误

select error from v$archive_dest where target='STANDBY';

1.4.9.4 检查DG状态

SELECT MESSAGE FROM V$DATAGUARD_STATUS;

1.4.9.5 检查主库的switchover_status

SQL> select switchover_status from v$database ;

SWITCHOVER_STATUS

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

TO STANDBY

如果结果为FAILED DESTINATION，则不正常，需要检查下主备的告警日志，排查下原因。

1.4.9.6 检查主库归档日志能否实时同步到备库

1.4.9.6.1 查看备库现有归档日志

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME

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

6 08-NOV-19 08-NOV-19

7 08-NOV-19 08-NOV-19

8 08-NOV-19 08-NOV-19

1.4.9.6.2 在主库上切换日志

SQL> ALTER SYSTEM SWITCH LOGFILE;

1.4.9.6.3 检查备库是否能接收到新产生的归档日志并应用

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APPLIED

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

6 YES

7 YES

8 YES

最近收到的日志文件的APPLIED列的值将是IN-MEMORY，如果该日志文件已应用，则为YES。

1.4.9.7 测试能否实时同步数据

在主库上插入一条数据，提交后，在备库上检查下是否能实时同步过来。

1.5 安装配置DG BROKER

要实现自动故障转移，需要启动dg broker，并运行observer软件。Oracle建议在独立于主系统和备用系统的计算机系统上运行观察程序。

但是若启动了自动故障转移，有时自动切到从库上去了，也会影响业务使用，所以一般不开启自动故障转移。主库有问题的话，确定需要切了，从库没问题，可以切了，再手动切。

最佳实践是RAC+DG，平时让RAC集群作为主库提供服务，整个RAC有问题了，再手动切到DG从库上。

1.5.1 确保主备是以spfile形式启动的

show parameter spfile;

1.5.2 开启主备闪回

配置主从数据库的flashback为on

alter database flashback on;

其中备库需要先停止应用日志，再开启闪回，然后再应用日志

SQL> alter database recover managed standby database cancel;

SQL> alter database flashback on;

SQL> alter database recover managed standby database using current logfile disconnect from session;

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

1.5.3 启动DG_BROKER

主备均需执行：

alter system set dg_broker_start=true;

启动后，就能看到dmon进程了：

示例：

1.5.4 配置DG BROKER

[oracle@ZooKeeper-node1 database]$ dgmgrl sys/密码@orcl

DGMGRL> CREATE CONFIGURATION 'DRSolution' AS PRIMARY DATABASE IS 'orcl' CONNECT IDENTIFIER IS orcl;

Configuration "DRSolution" created with primary database "orcl"

DGMGRL> ADD DATABASE 'bakorcl' AS CONNECT IDENTIFIER IS bakorcl;

Database "bakorcl" added

DGMGRL> ENABLE CONFIGURATION;

Enabled.

DGMGRL> ENABLE DATABASE 'bakorcl';

Enabled.

#查看配置

show configuration

雅冰石

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
搭建11.2.0.4版本 DG(单实例对单实例)

试验环境：1.1 安装主库1.2 主库准备工作1.2.1启用Forced Loggingselect force_logging from v$database; #查看是否启用force loggingALTER DATABASE FORCE LOGGING; #启用force logging1.2.2 配置主库接收Redo Data此选项可选，但是Or...
复制链接

扫一扫