目录
某现场生产环境下prov库经常发生归档日志满，导致业务中断的情况。现场找过来后核查，在清理归档的时候检查dg环境发现该库没有搭建dg环境。跟现场确认，该库需要搭建dg环境。故整理该文档，用于prov库的dg搭建。
2.1、检查主备挂载点
2.1.1、主站点
provexppool/provexp 16T 3.8T 12T 25% /provexp
provexppool 20T 36K 16T 1% /provexppool
provdata02pool/provdata 6.0T 64G 5.9T 2% /provdata02
provredopool/provredo 200G 12G 188G 7% /provredo
provdata02pool 7.3T 31K 7.3T 1% /provdata02pool
provredopool 245G 31K 233G 1% /provredopool
provdata01pool/provdata 6.0T 239G 5.8T 4% /provdata01
provdata01pool 7.3T 31K 7.1T 1% /provdata01pool
provarchpool/provarch 600G 67G 533G 12% /provarch
provarchpool 733G 31K 667G 1% /provarchpool
2.1.2、容灾站点
provexppool/provexp 16T 902M 16T 1% /provexp
provdata02pool 7.3T 31K 7.3T 1% /provdata02pool
provdata01pool/provdata01 6.0T 72G 5.9T 2% /provdata01
provarchpool/provarch 600G 31K 600G 1% /provarch
provredopool/provredo 200G 3.0G 197G 2% /provredo
provexppool 20T 31K 20T 1% /provexppool
provdata01pool 7.3T 31K 7.3T 1% /provdata01pool
provredopool 245G 31K 242G 1% /provredopool
provarchpool 733G 31K 733G 1% /provarchpool
provdata01pool/provdata02 6.0T 20M 6.0T 1% /provdata02
2.1.3、结论
主站点和容灾站点prov库的挂载点完全一致。
2.2、主容站点数据文件位置
2.2.1、主站点
SQL> select file_name,TABLESPACE_NAME from dba_data_files ;
FILE_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
/provdata01/prov/system01.dbf SYSTEM
/provdata01/prov/sysaux01.dbf SYSAUX
/provdata01/prov/undotbs01.dbf UNDOTBS1
/provdata01/prov/users01.dbf USERS
/provdata01/prov/oss_index00.dbf OSS_INDEX
/provdata01/prov/oss_data00.dbf OSS_DATA
/provdata02/prov/oss_archive00.dbf OSS_ARCHIVE
/provdata02/prov/idx_cc00.dbf IDX_CC
/provdata01/prov/tab_cc00.dbf TAB_CC
9 rows selected.
SQL>
SQL> select file_name,TABLESPACE_NAME from dba_temp_files ;
FILE_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
/provdata01/prov/temp01.dbf TEMP
SQL>
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/provredo/prov/redo1_1.dbf
/provredo/prov/redo1_2.dbf
/provredo/prov/redo2_1.dbf
/provredo/prov/redo2_2.dbf
/provredo/prov/redo3_1.dbf
/provredo/prov/reod3_2.dbf
/provredo/prov/redo4_1.dbf
/provredo/prov/redo4_2.dbf
/provredo/prov/redo5_1.dbf
/provredo/prov/redo5_2.dbf
/provredo/prov/redo6_1.dbf
MEMBER
--------------------------------------------------------------------------------
/provredo/prov/redo6_2.dbf
/provredo/prov/redo7_1.dbf
/provredo/prov/redo7_2.dbf
/provredo/prov/redo8_1.dbf
/provredo/prov/redo8_2.dbf
16 rows selected.
SQL>
2.2.2、容灾站点
SQL> col file_name format a40
SQL> select file_name,TABLESPACE_NAME from dba_data_files ;
FILE_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
/provdata01/prov/system01.dbf SYSTEM
/provdata01/prov/sysaux01.dbf SYSAUX
/provdata01/prov/undotbs01.dbf UNDOTBS1
/provdata01/prov/users01.dbf USERS
SQL>
SQL> select file_name,TABLESPACE_NAME from dba_temp_files ;
FILE_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
/provdata01/prov/temp01.dbf TEMP
SQL>
SQL> select member from v$logfile;
MEMBER
---------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------/provredo/prov/redo1_1.dbf
/provredo/prov/redo1_2.dbf
/provredo/prov/redo2_1.dbf
/provredo/prov/redo2_2.dbf
/provredo/prov/redo3_1.dbf
/provredo/prov/redo3_2.dbf
6 rows selected.
SQL>
2.2.3、结论
主站点和容灾站点数据文件位置基本一致。
2.3、强制日志模式
2.3.1、主站点
检查主站点强制日志模式
SQL> select force_logging from v$database;
FOR
---
NO
SQL>
看到主站点prov库没有开启强制日志模式，需要开启。
2.3.2、结论
需要开启强制日志模式。
alter database force logging;
2.4、主库归档模式
2.4.1、主站点
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /provarch/prov
Oldest online log sequence 2301
Next log sequence to archive 2308
Current log sequence 2308
SQL>
2.4.2、结论
主库归档满足。
2.5、主容监听
2.5.1、主站点
ossdb02:[/oracle$]lsnrctl status LISTENER_prov
LSNRCTL for Solaris: Version 11.2.0.4.0 - Production on 17-MAR-2020 04:49:04
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=1xx.xxx.xxx.xxx)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER_prov
Version TNSLSNR for Solaris: Version 11.2.0.4.0 - Production
Start Date 15-SEP-2017 11:30:42
Uptime 416 days 14 hr. 50 min. 29 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/112/network/admin/listener.ora
Listener Log File /oracle/product/112/log/diag/tnslsnr/ossdb02/listener_prov/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1xx.xxx.xxx.xxx)(PORT=1522)))
Services Summary...
Service "prov" has 1 instance(s).
Instance "prov", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
ossdb02:[/oracle$]
2.5.2、容灾站点
dr-ossdb01:[/oracle/product/112/network/admin$]lsnrctl status LISTENER_prov
LSNRCTL for Solaris: Version 11.2.0.4.0 - Production on 17-MAR-2020 04:48:17
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=1xx.xxx.xxx.xxx)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER_prov
Version TNSLSNR for Solaris: Version 11.2.0.4.0 - Production
Start Date 09-JAN-2020 18:38:04
Uptime 67 days 10 hr. 10 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/112/network/admin/listener.ora
Listener Log File /oracle/product/112/log/diag/tnslsnr/dr-ossdb01/listener_prov/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1xx.xxx.xxx.xxx)(PORT=1522)))
Services Summary...
Service "prov" has 1 instance(s).
Instance "prov", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
2.5.3、结论
主容监听正常。
2.6、tns检查
2.6.1、主站点
[/oracle$]tnsping dr_prov
TNS Ping Utility for Solaris: Version 11.2.0.4.0 - Production on 17-MAR-2020 04:52:15
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 1xx.xxx.xxx.xxx)(PORT = 1522))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prov)
))OK (30 msec)
[/oracle$]tnsping pr_prov
TNS Ping Utility for Solaris: Version 11.2.0.4.0 - Production on 17-MAR-2020 04:52:23
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 1xx.xxx.xxx.xxx)(PORT = 1522))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prov)
))OK (10 msec)
2.6.2、容灾站点
[/oracle/product/112/network/admin$]tnsping PR_PROV
TNS Ping Utility for Solaris: Version 11.2.0.4.0 - Production on 17-MAR-2020 04:51:42
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 1xx.xxx.xxx.xxx)(PORT = 1522))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prov
)))OK (40 msec)
[/oracle/product/112/network/admin$]tnsping DR_PROV
TNS Ping Utility for Solaris: Version 11.2.0.4.0 - Production on 17-MAR-2020 04:51:54
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 1xx.xxx.xxx.xxx)(PORT = 1522))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prov
)))OK (10 msec)
2.6.3、结论
主容站点tns配置正常。
2.7、检查db_unique_name
2.7.1、主站点
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string prov
db_unique_name string prov
global_names boolean FALSE
instance_name string prov
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string prov
SQL>
2.7.2、容灾站点
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string prov
db_unique_name string prov
global_names boolean FALSE
instance_name string prov
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string prov
SQL>
2.7.3、结论
需要更改db_unique_name。
主站点：
alter system set db_unique_name=pr_prov scope=spfile ;
容灾站点：
alter system set db_unique_name=dr_prov scope=spfile ;
该更改需要重启数据库生效。
2.8、数据库dg相关参数
2.8.1、主站点
alter system set fal_client='pr_prov' scope=both;
alter system set fal_server ='dr_prov' scope=both;
alter system set log_archive_config='DG_CONFIG=(pr_prov,dr_prov)' ;
alter system set log_archive_dest_1='LOCATION=/provarch/prov valid_for=(all_logfiles,all_roles) db_unique_name=pr_prov' scope=both;
alter system set log_archive_dest_2='SERVICE=dr_prov LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=dr_prov' scope=both;
alter system set standby_file_management='AUTO' scope=both;
2.8.2、容灾站点
alter system set fal_client='dr_prov' scope=both;
alter system set fal_server ='pr_prov' scope=both;
alter system set log_archive_config='DG_CONFIG=(dr_prov,pr_prov)' ;
alter system set log_archive_dest_1='LOCATION=/provarch/prov valid_for=(all_logfiles,all_roles) db_unique_name=dr_prov' scope=both;
alter system set log_archive_dest_2='SERVICE=pr_prov LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=pr_prov' scope=both;
alter system set standby_file_management='AUTO' scope=both;
2.8.3、结论
以上参数可以直接改，无需重启数据库即可生效。
3.1、主库备份
3.1.1、主库全备
export ORACLE_SID=prov
rman target /
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
backup full filesperset 6 format '/provexp/rmanprovfull/db_%d_%U' database;
sql 'alter system switch logfile';
release channel t1;
release channel t2;
}
3.1.2、备份主库归档
RMAN>backup archivelog all format '/provexp/rmanprovfull/arch_%T_%s_%p.bak';
3.1.3、备份主库控制文件
RMAN>backup device type disk format '/provexp/rmanprovfull/ctl_%U' current controlfile for standby;
3.2、拷贝备份到备库
su - oracle
$scp /provexp/rmanprovfull/* 1xx.xxx.xxx.xxx:/provexp/rmanprovfull/
3.3、恢复备库控制文件
重启备库至nomount状态：
export ORACLE_SID=prov
sqlplus / as sysdba
sql>shutdown immediate;
sql>startup nomount;
Sql>quit
$rman target /
rman>restore controlfile from '/provexp/rmanprovfull/ctl_xxx';
启动备库到physical standby状态
Sql>alter database mount standby database;
3.4、恢复备库
export ORACLE_SID=prov
rman target /
Rman> run{
allocate channel t1 type disk;
allocate channel t2 type disk;
restore database;
release channel t1;
release channel t2;
}
3.5、启动备库应用日志
Sql>alter database recover managed standby database disconnect from session;
3.6、增加standby日志组
最好主库备库都加，大小和个数跟现有redo日志大小和个数一致。
SQL> select group#,SEQUENCE#,BYTES,MEMBERS,status from v$log ;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 40 52428800 2 INACTIVE
2 41 52428800 2 CURRENT
3 39 52428800 2 INACTIVE
SQL>
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata/prov/redo01_01.log
/oradata/prov/redo01_02.log
/oradata/prov/redo02_01.log
/oradata/prov/redo02_02.log
/oradata/prov/redo03_01.log
/oradata/prov/redo03_02.log
6 rows selected.
SQL> alter database add standby logfile group 4('/oradata/prov/stdredo04_01.log','/oradata/prov/stdredo04_02.log') size 512m;
Database altered.
SQL> alter database add standby logfile group 5('/oradata/prov/stdredo05_01.log','/oradata/prov/stdredo05_02.log') size 512m;
Database altered.
SQL> alter database add standby logfile group 6('/oradata/prov/stdredo06_01.log','/oradata/prov/stdredo06_02.log') size 512m;
Database altered.
3.7、验证
3.7.1、主容状态
数据库角色
主：
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
SQL>
容灾站点：
SQL> select database_role from v$database;
DATABASE_ROLE
------------------------------------------------
PHYSICAL STANDBY
SQL>
3.7.2、进程检查
主：
SQL> select process,block#,blocks ,status ,sequence# from v$managed_standby;
PROCESS BLOCK# BLOCKS STATUS SEQUENCE#
--------- ---------- ---------- ------------ ----------
ARCH 55296 1572 CLOSING 38
ARCH 22528 1866 CLOSING 39
ARCH 0 0 CONNECTED 0
ARCH 53248 1871 CLOSING 37
LNS 6768 1 WRITING 40
SQL>
ARCH进程： 用于主库上复制redo log，从而生成归档日志，当前状态为CLOSING表示该进程目前正在复制。
LNS进程： 用于在主库上将主库的归档日志同步到备库上，将归档日志投递给备库上的RFS进程。
容灾站点：
SQL> select process,block#,blocks ,status ,sequence# from v$managed_standby;
PROCESS BLOCK# BLOCKS STATUS SEQUENCE#
--------- ---------- ---------- ------------ ----------
ARCH 0 0 CONNECTED 0
ARCH 0 0 CONNECTED 0
ARCH 0 0 CONNECTED 0
ARCH 0 0 CONNECTED 0
RFS 0 0 IDLE 0
RFS 6833 1 IDLE 40
RFS 0 0 IDLE 0
MRP0 0 0 WAIT_FOR_LOG 40
RFS 0 0 IDLE 0
9 rows selected.
SQL>
FRS进程：用于备库接收从主库LNS进程或ARCH进程投递过来的归档日志。 ARCH： 用于复制从主库上同步过来的归档日志。
MRP0：用于应用归档日志。
3.7.3、备库检查裂缝
SQL> select * from v$archive_gap;
no rows selected
SQL>
如果DG环境日志同步正常，则不会查到任何记录，如果查出结果，则说明目前的DG环境归档日志有裂缝,需要检查相关日志是否存在后应用。
3.7.4、日志应用检查
备库查看当前日志应用：
SQL> select name,SEQUENCE#,APPLIED from v$archived_log order by sequence#;
NAME SEQUENCE# APPLIED
-------------------------------------------------- ---------- ---------
/oradata/provarch/1_34_1034939605.dbf 34 YES
/oradata/provarch/1_35_1034939605.dbf 35 YES
/oradata/provarch/1_36_1034939605.dbf 36 YES
/oradata/provarch/1_37_1034939605.dbf 37 YES
/oradata/provarch/1_38_1034939605.dbf 38 YES
/oradata/provarch/1_39_1034939605.dbf 39 YES
6 rows selected.
SQL>
应用到39号日志了。
主库：
SQL> alter system switch logfile ;
System altered.
SQL>
备库：
SQL> select name,SEQUENCE#,APPLIED from v$archived_log order by sequence#;
NAME SEQUENCE# APPLIED
-------------------------------------------------- ---------- ---------
/oradata/provarch/1_34_1034939605.dbf 34 YES
/oradata/provarch/1_35_1034939605.dbf 35 YES
/oradata/provarch/1_36_1034939605.dbf 36 YES
/oradata/provarch/1_37_1034939605.dbf 37 YES
/oradata/provarch/1_38_1034939605.dbf 38 YES
/oradata/provarch/1_39_1034939605.dbf 39 YES
/oradata/provarch/1_40_1034939605.dbf 40 YES
7 rows selected.
SQL>
看到备库日志应用到40号了，说明日志应用正常。
3.7.5、验证备库数据一致性
备库正常状态下都是mount状态，并且同步应用日志，此时无法查询数据。如果需要查询数据，可以先将备库临时修改为read-only，待查询完成之后再修改为mount状态。备库在readonly的状态下，如果要启动日志应用进程，则必须要有standby日志，否则会报错。这也就是需要3.6章节的原因。
修改为read-only需要执行两步：
取消备库日志应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY
启动日志应用进程
SQL> recover managed standby database using current logfile disconnect from session;
ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs
这个表示要有standby日志，在备库是readonly的状态下即可做3.6节的内容。
3.7.6、测试表
主：
SQL> select * from tmp_list_001 ;
ID NAME
---------- --------------------------------------------------
1 wufan
2 haha
SQL>
SQL> insert into tmp_list_001 values(3,'iwhalecloud');
1 row created.
SQL> commit ;
Commit complete.
SQL>
SQL> alter system switch logfile ;
System altered.
SQL>
容灾站点：
SQL> select * from tmp_list_001 ;
ID NAME
---------- --------------------------------------------------
1 wufan
2 haha
3 iwhalecloud
SQL>
3.7.7、此时双库的状态
主：
SQL> select database_role,OPEN_MODE from v$database ;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY READ WRITE
容：
SQL> select database_role,OPEN_MODE from v$database ;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
在修改db_unique_name的时候主库需要停机使修改生效，以便配置后续的dg。停机时间在几分钟至10几分钟内。
2410
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
