试验环境:
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
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
Copyright (c) 1982, 2013, Oracle. All rights reserved.
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
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
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