Oracle数据库ADG搭建实施报告

DB类型	主机名	DB_NAME	DB_UNIQUE_NAME	INSTANCE	ASM DG
主库	CRPLDAPDB01	Idmdb	Idmdb	Idmdb 1	DATA, +DATA/arch
主库	CRPLDAPDB02	Idmdb	Idmdb	Idmdb2	DATA, +DATA/arch
备库	dg-ldapdb	Idmdb	dg_ldapdb	dg_ldapdb

灾备网络地址

主机	灾备IP	灾备VIP
CRPLDAPDB01	10.59.1.42	10.59.1.41
CRPLDAPDB02	10.59.1.44	10.59.1.43
dg-ldapdb	10.61.0.230

2.2 准备工作

备注：无特殊说明所有操作均在备库服务器上

2.2.1 RMAN备份主库

备注：主库端操作

查看数据库大小
编写脚本进行全库备份

SQL> SELECT SUM(DS.BYTES)/1024/1024/1024 "SIZE(G)" FROM DBA_SEGMENTS ds;

SIZE(G)

----------

134.760681

idmdb1:/backup/fullback20200103@CRPLDAPDB01> vi fullbak.sh

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

ORACLE_SID=idmdb1; export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

export NLS_LANG=AMERICAN_CHINA.AL32UTF8

TMPDIR=/var/tmp; export TMPDIR

NLS_DATE_FORMAT="YYYY/MM/DD hh24:mi:ss"; export NLS_DATE_FORMAT

TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN DISPLAY

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

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

PS1=[`hostname`:$ORACLE_SID:\$PWD$]

BACKUP_DIR=/backup/fullback20200103

rman target / log $BACKUP_DIR/rman_db_full_$(date +%Y%m%d).log<<EOF

run{

allocate channel c1 device type disk;

allocate channel c2 device type disk;

allocate channel c3 device type disk;

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

configure channel device type disk maxpiecesize 4G;

BACKUP FORMAT='/backup/fullback20200103/data_%U_%T.dbf' DATABASE;

BACKUP SPFILE FORMAT '/backup/fullback20200103/spfile_%U_%T.ora';

backup current controlfile for standby format'/backup/fullback20200103/ctl_stand_con.ctl';

release channel c1;

release channel c2;

release channel c3;

}

EOF

idmdb1:/backup/fullback20200103@CRPLDAPDB01>chmod 755 fullbak.sh

idmdb1:/backup/fullback20200103@CRPLDAPDB01> nohup sh fullbak.sh > fullbak.log &

2.2.2备份文件传输至备库

备注：主库端操作

将全库备份通过灾备复制网络传输至备库服务器的/home/oracle/backup目录中。

2.2.3添加tnsnames.ora文件信息

配置主备端三个节点的tnsnames.ora，添加如下信息：

IDMDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.59.1.45)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.59.1.42)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.59.1.44)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME =idmdb)

)

dg_ldapdb =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.61.0.230)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME =dg_ldapdb)

)

#以上已添加可通过lsnrctl status查看状态

2.2.4配置静态监听

LISTENER=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=10.61.0.230)(PORT=1521))))

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=dg_ldapdb)

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

(SID_NAME=dg_ldapdb)))

2.2.5 拷贝密码文件

在备库端将主库传输过来的口令文件拷贝到$ORACLE_HOME/dbs下并重命名：

scp orapwidmdb1 oracle@10.61.0.230:$ORACLE_HOME/dbs/orapwdg_ldapdb

2.2.6配置hosts

vi /etc/hosts

10.61.0.230 dg-ldapdb

2.3主库检查

备注：主库端操作

2.3.1 是否安装相关组件

SQL> SELECT * FROM V$OPTION WHERE PARAMETER in ('Oracle Data Guard', 'Advanced Compression');

PARAMETER VALUE

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

Oracle Data Guard TRUE

Advanced Compression TRUE

2.3.2 FORCE_LOGGING模式

select force_logging from v$database;

(开启：alter database force logging;)

2.3.3 归档模式

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination +DATA/arch

Oldest online log sequence 3545

Next log sequence to archive 3547

Current log sequence 3547

2.3.4 remote_login_passwordfile配置

show parameter remote_login_passwordfile

(remote_login_passwordfile应为EXCLUSIVE)

2.4 修改参数文件

2.4.1 修改主库参数

备注：主库端操作

修改主库参数

alter system set log_archive_config='dg_config=( idmdb, dg_ldapdb)' scope=both sid='*';

alter system set log_archive_dest_state_2='defer' scope=both sid='*';

#等备库实例启动，再开启enabled

alter system set log_archive_dest_2='service=dg_ldapdb LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=dg_ldapdb' sid='*' scope=both;

alter system set fal_client=' idmdb' scope=both sid='*';

alter system set fal_server=' dg_ldapdb' scope=both sid='*';

alter system set standby_file_management=AUTO scope=both sid='*';

alter system set log_archive_max_processes = 8 scope=both sid='*';

2.4.2 修改备库参数

修改备库参数文件
创建文件夹
注意参数文件修改与核对
注意两个节点的口令文件的权限，位置和命名orapw dg_ldapdb
tnsping idmdb检查网络服务名是否ping通

[oracle@dg-ldapdb dbs]$ cat initdg_ldapdb.ora

*.aq_tm_processes=1

*.audit_file_dest='/u01/app/oracle/admin/dg_ldapdb/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/dg_ldapdb/current01.ctl','/u01/app/oracle/oradata/dg_ldapdb/current02.ctl'#Restore Controlfile

*.db_block_size=8192

*.db_cache_size=6453M

*.db_create_file_dest='/u01/app/oracle/oradata/dg_ldapdb'

*.db_domain=''

*.db_name='idmdb'

*.db_unique_name='dg_ldapdb'

*.db_recovery_file_dest='/u01/app/oracle/flashback'

*.db_recovery_file_dest_size=10g

*.dg_broker_start=FALSE

*.diagnostic_dest='/u01/app/oracle'

#*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg_ldapdbXDB)'

*.fal_client='dg_ldapdb'

*.fal_server='idmdb'

*.java_jit_enabled=TRUE

*.job_queue_processes=1000

*.large_pool_size=698M

*.log_archive_config='dg_config=(idmdb,dg_ldapdb)'

*.log_archive_dest_1='location=/arch'

*.log_archive_dest_2='service=idmdb LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 net_timeout=30 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=idmdb'

*.log_archive_dest_state_2='enable'

*.log_archive_format='%t_%s_%r.dbf'

*.log_archive_max_processes=8

*.log_archive_min_succeed_dest=1

*.log_archive_trace=0

*.nls_territory='CHINA'

*.open_cursors=1000

*.pga_aggregate_target=2g

*.processes=1200

*.query_rewrite_enabled='TRUE'

*.query_rewrite_integrity='TRUSTED'

*.remote_login_passwordfile='exclusive'

*.sessions=1325

*.sga_max_size=5g

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.db_file_name_convert='+DATA/idmdb','/u01/app/oracle/oradata/dg_ldapdb'

*.log_file_name_convert='+DATA/idmdb,'/u01/app/oracle/oradata/dg_ldapdb'

Mkdir -p /u01/app/oracle/admin/dg_ldapdb/adump

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

mkdir -p /arch

mkdir -p /u01/app/oracle/flashback

mkdir -p /u01/app/oracle/oradata/dg_ldapdb/datafile

mkdir -p /u01/app/oracle/oradata/dg_ldapdb/onlinelog

2.5 创建并恢复备库实例

2.5.1 恢复参数文件

sqlplus / as sysdba

SQL> create spfile from pfile;

SQL> startup nomount

#检查数据库是否使用spfile启动及参数：

show parameter spfile

show parameter name

show parameter standby

show parameter cluster

show parameter listerner

show parameter fal

show parameter manage

show parameter convert

show parameter log

2.5.2 恢复控制文件

rman target /

RMAN> restore standby controlfile from '/home/oracle/backup/fullback20200103/ctl_stand_con.ctl';

RMAN> alter database mount;

2.5.3 恢复数据库

restore database

RMAN> catalog start with '/home/oracle/backup/fullback20200103';

RMAN> crosscheck backupset;

RMAN> delete noprompt expired backup;

RMAN> restore database;

2.6创建standby redo

2.6.1 查看主库日志文件大小

备注：主/备库端操作

col group# for 99

col thread# for 99

col member for 99

col bytes for 99999999

col status for a10

col type for a20

set linesize 200

set pagesize 100

SELECT L.GROUP#, L.THREAD#, LF.MEMBER, L.BYTES/1024/1024, L.STATUS, LF.TYPE

FROM V$LOG L, V$LOGFILE LF

WHERE L.GROUP# = LF.GROUP#;

2.6.2 创建standby日志组

备注：若数据库已经有standby log的信息，主备库可不用重新删除添加。即忽略该步骤。以下为主备库均无standby log情况下操作。

添加主备库端两节点的standby日志组

主库：

alter database add standby logfile thread 1 group 13 ('+DATA','+DATA') size 200m;

alter database add standby logfile thread 2 group 14 ('+DATA','+DATA') size 200m;

备库：

alter database add standby logfile thread 1 group 7 ('/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby07a.log','/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby07b.log') size 200m;

alter database add standby logfile thread 1 group 8 ('/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby08a.log','/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby08b.log') size 200m;

alter database add standby logfile thread 1 group 9 ('/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby09a.log','/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby09b.log') size 200m;

alter database add standby logfile thread 2 group 10 ('/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby10a.log','/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby10b.log') size 200m;

alter database add standby logfile thread 2 group 11 ('/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby11a.log','/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby11b.log') size 200m;

alter database add standby logfile thread 2 group 12 ('/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby12a.log','/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby12b.log') size 200m;

alter database add standby logfile thread 1 group 13 ('/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby13a.log','/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby13b.log') size 200m;

alter database add standby logfile thread 2 group 14 ('/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby14a.log','/u01/app/oracle/flashback/DG_LDAPDB/onlinelog/standby14b.log') size 200m;

2.6.3 备库介质恢复

alter database recover managed standby database using current logfile

disconnect from session nodelay;

2.6.4 开启日志投递

备注：主库端操作

alter system set log_archive_dest_state_3='enable' scope=both sid='*';

alter system switch logfile;

2.7同步状态检查

2.7.1 查看备库警告日志

tail -100f alersid.log

2.7.2 mrp进程状态检查

select inst_id,process,status,thread#,sequence#,block# from gv$managed_standby where PROCESS like 'MRP%';

2.7.3 外部归档文件处理

注意：外部存档日志文件空间，如空间不足请及时删除(文档 ID 1617965.1)

SQL>select * from V$FLASH_RECOVERY_AREA_USAGE;

RMAN>delete foreign archivelog all;

2.7.4 日志接收与同步检查

主库：

select thread#,max(sequence#) "Last Primary Seq Generated" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# group by thread# order by 1;

备库：

-- 检查备库已经接收到的 sequence# 号

select thread#,max(sequence#) "Last Standby Seq Received" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# group by thread# order by 1;

-- 检查备库已经应用到的 sequence# 号

select thread#,max(sequence#) "Last Standby Seq Applied" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# and val.applied in ('YES','IN-MEMORY') group by thread# order by 1;

2.7.5 延迟查询

select name,value from v$dataguard_stats;

2.8 DG_BLOCK配置

2.8.1 配置监听

主库：

vi /u01/app/11.2.0/grid_1/network/admin/listener.ora

--Primary Node1

SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=idmdb1)(GLOBAL_DBNAME=idmdb__DGMGRL)(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)))

--Primary Node2

SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=idmdb2)(GLOBAL_DBNAME=idmdb__DGMGRL)(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)))

备库：

LISTENER=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=10.61.0.230)(PORT=1521))))

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=dg_ldapdb)

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

(SID_NAME=dg_ldapdb))

(SID_DESC=

(GLOBAL_DBNAME=dg_ldapdb_DGMGRL)

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

(SID_NAME=dg_ldapdb)))

2.8.2 修改配置参数

主库：

ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+data/IDMDB/DATAGUARDCONFIG/dgb_config1.ora' SCOPE=BOTH sid='*';

ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+data/IDMDB/DATAGUARDCONFIG2/dgb_config1.ora' SCOPE=BOTH sid='*';

alter system set dg_broker_start=true scope=both sid='*';

备库：

alter system set dg_broker_start=true scope=both sid='*';

2.8.3 配置DG_BLOCK

SQL> dgmgrl sys/oracle

在dgmgrl下创建配置：

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

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

DGMGRL> SHOW CONFIGURATION;

Configuration - idmdb

Protection Mode: MaxPerformance

Databases:

idmdb - Primary database

dg_ldapdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> show database verbose idmdb;

Database - idmdb

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

idmdb1

idmdb2

Properties:

DGConnectIdentifier = 'idmdb'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

DelayMins = '0'

Binding = 'optional'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '8'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = ''

LogFileNameConvert = ''

FastStartFailoverTarget = ''

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

ApplyLagThreshold = '0'

TransportLagThreshold = '0'

TransportDisconnectedThreshold = '30'

SidName(*)

StaticConnectIdentifier(*)

StandbyArchiveLocation(*)

AlternateLocation(*)

LogArchiveTrace(*)

LogArchiveFormat(*)

TopWaitEvents(*)

(*) - Please check specific instance for the property value

Database Status:

SUCCESS

DGMGRL> SHOW DATABASE VERBOSE dg_ldapdb;

Database - dg_ldapdb

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds (computed 0 seconds ago)

Apply Lag: 0 seconds (computed 0 seconds ago)

Apply Rate: 647.00 KByte/s

Real Time Query: ON

Instance(s):

dg_ldapdb

Properties:

DGConnectIdentifier = 'dg_ldapdb'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

DelayMins = '0'

Binding = 'OPTIONAL'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'auto'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '8'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = '+DATA/idmdb, /u01/app/oracle/oradata/dg_ldapdb'

LogFileNameConvert = '+DATA/idmdb,/u01/app/oracle/oradata/dg_ldapdb'

FastStartFailoverTarget = ''

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

ApplyLagThreshold = '0'

TransportLagThreshold = '0'

TransportDisconnectedThreshold = '30'

SidName = 'dg_ldapdb'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg-ldapdb)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dg_ldapdb_DGMGRL)(INSTANCE_NAME=dg_ldapdb)(SERVER=DEDICATED)))'

StandbyArchiveLocation = '/arch'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.dbf'

TopWaitEvents = '(monitor)'

Database Status:

SUCCESS