Oracle 11.2.0_Phy-DataGuard_CentOS7.2
介绍
背景：近期有一套系统需要做物理DG，新购置一台数据库服务。具体如下：
| IP地址 | 主机名称 | Oracle服务名 | 用途 |
| 172.16.1.173 | ora11g | prixcky | 系统主库（在线运行中） |
| 172.16.1.172 | slave2.cloudera | stdxcky | 系统备库（仅需装Oracle软件即可） |
1、配置流程梳理
- 主库强记日志
- 主库创建standby日志组
- 主、备配置修改initSID.ora
- 主、备配置静态监听和客户端TNS
- 从主库拷贝口令文件到备库
- 主库启动到mount，备库启动到nomount
- 主库开启归档，依据参数文件中配置，创建相关路径并授权
- 利用RMAN将主库duplicate到备库
- 备、主开库
2、搭建步骤
修改：
$(MK_EMAGENT_NMECTL)为如下（追加" -lnnz11"）
主库强制记日志
SQL>alter database force logging;
SQL> select force_logging from v$database;
FOR
------
YES
主库创建standby日志组
查看日志文件大小和路径
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/home/ora11g/11g/oradata/xcky/redo03.log
/home/ora11g/11g/oradata/xcky/redo02.log
/home/ora11g/11g/oradata/xcky/redo01.log
创建与online rego日志大小相同的stangby日志，组数为四组，比online redo多一组即可
SQL> alter database add standby logfile group 4 '/home/ora11g/11g/oradata/xcky/stdredo01.log' size 52428800;
Database altered.
SQL> alter database add standby logfile group 5 '/home/ora11g/11g/oradata/xcky/stdredo02.log' size 52428800;
Database altered.
SQL> alter database add standby logfile group 6 '/home/ora11g/11g/oradata/xcky/stdredo03.log' size 52428800;
Database altered.
SQL> alter database add standby logfile group 7 '/home/ora11g/11g/oradata/xcky/stdredo04.log' size 52428800;
Database altered.
主、备配置修改initSID.ora
主库：一般库是由spfile参数文件启动，确认下，spfile有路径值说明是由spfile启动：
SQL> show parameter spfile
NAME TYPE
VALUE
------------------------------------ ----------- ----------------
spfile string/home/ora11g/11g/product/11.2.
0/dbhome_1/dbs/spfilexcky.ora
SQL>create pfile from spfile; ---由当前spfile生成可编辑的pfile参数文件。
主库编辑initxcky.ora文件，追加如下内容：
db_unique_name='prixcky'
log_archive_config='dg_config=(prixcky,stdxcky)'
log_archive_dest_1='location=/home/ora11g/archlog valid_for=(all_logfiles,all_roles) db_unique_name=prixcky'
log_archive_dest_2='service=stdxcky LGWR async valid_for=(online_logfiles,primary_role) db_unique_name=stdxcky'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
log_archive_max_processes=30
fal_client='prixcky'
fal_server='stdxcky'
standby_file_management='AUTO'
log_file_name_convert=('/home/ora11g/11g/oradata/xcky/','/home/ora11g/11g/oradata/xcky/')
db_file_name_convert=('/home/ora11g/11g/oradata/xcky/','/home/ora11g/11g/oradata/xcky/','/opt/oradata/','/opt/oradata/')
将原参数文件拷贝到备库，做好对应配置的修改：
db_unique_name='stdxcky'
log_archive_config='dg_config=(prixcky,stdxcky)'
log_archive_dest_1='location=/home/ora11g/archlog valid_for=(all_logfiles,all_roles) db_unique_name=stdxcky'
log_archive_dest_2='service=prixcky LGWR async valid_for=(online_logfiles,primary_role) db_unique_name=prixcky'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
log_archive_max_processes=30
fal_client='stdxcky'
fal_server='prixcky'
standby_file_management='AUTO'
log_file_name_convert=('/home/ora11g/11g/oradata/xcky/','/home/ora11g/11g/oradata/xcky/')
db_file_name_convert=('/home/ora11g/11g/oradata/xcky/','/home/ora11g/11g/oradata/xcky/','/opt/oradata/','/opt/oradata/')
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/home/ora11g/11g/oradata/xcky/system01.dbf
/home/ora11g/11g/oradata/xcky/sysaux01.dbf
/home/ora11g/11g/oradata/xcky/undotbs01.dbf
/home/ora11g/11g/oradata/xcky/users01.dbf
/opt/oradata/PHOTO_INFO.dbf
/opt/oradata/XCKY_INFO.dbf
/opt/oradata/BUNCH_INFO.dbf
/opt/oradata/TRANSFER_INFO.dbf
/opt/oradata/OTHER_INFO.dbf
/opt/oradata/BULLETIN_INFO.dbf
/opt/oradata/SYSTEM_INFO.dbf
/opt/oradata/INDEX_INFO.dbf
/opt/oradata/VIEW_INFO.dbf
/opt/oradata/VIEW_LOG.dbf
/opt/oradata/XCKY.dbf
数据文件路径有/opt/oradata 和/home/ora11g/11g/oradata/xcky，如果备库与此相同，首先在备库创建这些目录并授权，同时也需要指定db_file_name_convert参数。
SQL> select member from v$logfile;
MEMBER
--------------------------------------------
/home/ora11g/11g/oradata/xcky/redo03.log
/home/ora11g/11g/oradata/xcky/redo02.log
/home/ora11g/11g/oradata/xcky/redo01.log
SQL> show parameter control_files;
NAME TYPE
VALUE
------------------------------------ ----------- ------------------------------------------------------------------------------------------------------------------
control_files string/home/ora11g/11g/oradata/xcky/control01.ctl, /home/ora11g/11g/oradata/xcky/control02.ctl
以上是日志文件、数据文件、控制文件的目录路径，需要根据各自类型的文件路径，指定主、备库间文件路径转换参数；
log_file_name_convert用于指定日志文件路径目录转换；
db_file_name_convert用于指定数据文件、控制文件目录转换；以主前备后的方式对应。
如果对应错误，duplicate时会导致如下报错：
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/30/2017 20:05:10
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 08/30/2017 20:05:08
ORA-17628: Oracle error 19505 returned by remote Oracle server
解决方案就是将不对应的路径纠正对应关系，重新duplicate。
使用scp将口令文件拷贝到固定路径下（安装备库软件时候，本次安装路径环境变量与主库是相同的）
[[email protected] dbs]$ ls
！ hc_xcky.dat init.ora lkPRIXCKY lkXCKY orapwxcky snapcf_xcky.f spfilexcky.ora
[[email protected] dbs]$ scp orapwxcky slave2.cloudera:$ORACLE_HOME/dbs
[email protected]'s password:
orapwxcky 100% 1536 1.5KB/s 00:00
主、备路静态监听和客户端TNS配置
listener.ora配置：
主库：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prixcky)
(ORACLE_HOME = /home/ora11g/11g/product/11.2.0/dbhome_1)
(SID_NAME = xcky)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora11g)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
备库：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdxcky)
(ORACLE_HOME = /home/ora11g/11g/product/11.2.0/dbhome_1)
(SID_NAME = xcky)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = slave2.cloudera)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
tnsnames.ora配置（主、备）：
prixcky =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora11g)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prixcky)
)
)
stdxcky =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = slave2.cloudera)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stdxcky)
)
)
主库启动到mount，备库无控制文件，只能启动到nomount
主库开启归档，依据参数文件中配置，备库创建相关路径并授权
*.audit_file_dest='/home/ora11g/11g/admin/xcky/adump'
*.control_files='/home/ora11g/11g/oradata/xcky/control01.ctl','/home/ora11g/11g/oradata/xcky/control02.ctl'
log_archive_dest_1='location=/home/ora11g/archlog valid_for=(all_logfiles,all_roles)
db_unique_name=stdxcky'
这些路径目录在备库是不存在的，需要手工创建。
测试，确保使用服务名能够连通主、备库
[[email protected] dbs]$ sqlplus sys/[email protected] as sysdba
[[email protected] ~]$ sqlplus sys/[email protected] as sysdba
利用RMAN将主库duplicate到备库
$rman target sys/[email protected] auxiliary sys/[email protected]
如果没有配置静态监听，那么这个过程连接auxiliary库时候会提示报错信息：
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
因为备库无法mount，pmon进程无法启动，不会注册动态监听。
RMAN>duplicate target database for standby from active database nofilenamecheck;
执行如下：
[[email protected] ~]$ rman target sys/[email protected] auxiliary sys/[email protected]
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Aug 30 20:30:07 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: XCKY (DBID=283416601, not open)
connected to auxiliary database: XCKY (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 30-AUG-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=156 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/home/ora11g/11g/product/11.2.0/dbhome_1/dbs/orapwxcky' auxiliary format
'/home/ora11g/11g/product/11.2.0/dbhome_1/dbs/orapwxcky' ;
}
executing Memory Script
Starting backup at 30-AUG-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=172 device type=DISK
Finished backup at 30-AUG-17
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/home/ora11g/11g/oradata/xcky/control01.ctl';
restore clone controlfile to '/home/ora11g/11g/oradata/xcky/control02.ctl' from
'/home/ora11g/11g/oradata/xcky/control01.ctl';
}
executing Memory Script
Starting backup at 30-AUG-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/home/ora11g/11g/product/11.2.0/dbhome_1/dbs/snapcf_xcky.f tag=TAG20170830T203028 RECID=2 STAMP=953411428
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-AUG-17
Starting restore at 30-AUG-17
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 30-AUG-17
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
Using previous duplicated file /home/ora11g/11g/oradata/xcky/system01.dbf for datafile 1 with checkpoint SCN of 2403114
Using previous duplicated file /home/ora11g/11g/oradata/xcky/sysaux01.dbf for datafile 2 with checkpoint SCN of 2403114
Using previous duplicated file /home/ora11g/11g/oradata/xcky/undotbs01.dbf for datafile 3 with checkpoint SCN of 2403114
Using previous duplicated file /home/ora11g/11g/oradata/xcky/users01.dbf for datafile 4 with checkpoint SCN of 2403114
contents of Memory Script:
{
set newname for tempfile 1 to
"/home/ora11g/11g/oradata/xcky/temp01.dbf";
set newname for tempfile 2 to
"/opt/oradata/XCKYTMP.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/home/ora11g/11g/oradata/xcky/system01.dbf";
set newname for datafile 2 to
"/home/ora11g/11g/oradata/xcky/sysaux01.dbf";
set newname for datafile 3 to
"/home/ora11g/11g/oradata/xcky/undotbs01.dbf";
set newname for datafile 4 to
"/home/ora11g/11g/oradata/xcky/users01.dbf";
set newname for datafile 5 to
"/opt/oradata/PHOTO_INFO.dbf";
set newname for datafile 6 to
"/opt/oradata/XCKY_INFO.dbf";
set newname for datafile 7 to
"/opt/oradata/BUNCH_INFO.dbf";
set newname for datafile 8 to
"/opt/oradata/TRANSFER_INFO.dbf";
set newname for datafile 9 to
"/opt/oradata/OTHER_INFO.dbf";
set newname for datafile 10 to
"/opt/oradata/BULLETIN_INFO.dbf";
set newname for datafile 11 to
"/opt/oradata/SYSTEM_INFO.dbf";
set newname for datafile 12 to
"/opt/oradata/INDEX_INFO.dbf";
set newname for datafile 13 to
"/opt/oradata/VIEW_INFO.dbf";
set newname for datafile 14 to
"/opt/oradata/VIEW_LOG.dbf";
set newname for datafile 15 to
"/opt/oradata/XCKY.dbf";
backup as copy reuse
datafile 5 auxiliary format
"/opt/oradata/PHOTO_INFO.dbf" datafile
6 auxiliary format
"/opt/oradata/XCKY_INFO.dbf" datafile
7 auxiliary format
"/opt/oradata/BUNCH_INFO.dbf" datafile
8 auxiliary format
"/opt/oradata/TRANSFER_INFO.dbf" datafile
9 auxiliary format
"/opt/oradata/OTHER_INFO.dbf" datafile
10 auxiliary format
"/opt/oradata/BULLETIN_INFO.dbf" datafile
11 auxiliary format
"/opt/oradata/SYSTEM_INFO.dbf" datafile
12 auxiliary format
"/opt/oradata/INDEX_INFO.dbf" datafile
13 auxiliary format
"/opt/oradata/VIEW_INFO.dbf" datafile
14 auxiliary format
"/opt/oradata/VIEW_LOG.dbf" datafile
15 auxiliary format
"/opt/oradata/XCKY.dbf" ;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /home/ora11g/11g/oradata/xcky/temp01.dbf in control file
renamed tempfile 2 to /opt/oradata/XCKYTMP.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 30-AUG-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/opt/oradata/BUNCH_INFO.dbf
output file name=/opt/oradata/BUNCH_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/opt/oradata/OTHER_INFO.dbf
output file name=/opt/oradata/OTHER_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=/opt/oradata/BULLETIN_INFO.dbf
output file name=/opt/oradata/BULLETIN_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/opt/oradata/SYSTEM_INFO.dbf
output file name=/opt/oradata/SYSTEM_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/opt/oradata/PHOTO_INFO.dbf
output file name=/opt/oradata/PHOTO_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00012 name=/opt/oradata/INDEX_INFO.dbf
output file name=/opt/oradata/INDEX_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
input datafile file number=00013 name=/opt/oradata/VIEW_INFO.dbf
output file name=/opt/oradata/VIEW_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/opt/oradata/XCKY_INFO.dbf
output file name=/opt/oradata/XCKY_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/opt/oradata/TRANSFER_INFO.dbf
output file name=/opt/oradata/TRANSFER_INFO.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00014 name=/opt/oradata/VIEW_LOG.dbf
output file name=/opt/oradata/VIEW_LOG.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00015 name=/opt/oradata/XCKY.dbf
output file name=/opt/oradata/XCKY.dbf tag=TAG20170830T203036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-AUG-17
contents of Memory Script:
{
catalog clone datafilecopy "/home/ora11g/11g/oradata/xcky/system01.dbf",
"/home/ora11g/11g/oradata/xcky/sysaux01.dbf",
"/home/ora11g/11g/oradata/xcky/undotbs01.dbf",
"/home/ora11g/11g/oradata/xcky/users01.dbf";
switch clone datafile 1 to datafilecopy
"/home/ora11g/11g/oradata/xcky/system01.dbf";
switch clone datafile 2 to datafilecopy
"/home/ora11g/11g/oradata/xcky/sysaux01.dbf";
switch clone datafile 3 to datafilecopy
"/home/ora11g/11g/oradata/xcky/undotbs01.dbf";
switch clone datafile 4 to datafilecopy
"/home/ora11g/11g/oradata/xcky/users01.dbf";
switch clone datafile all;
}
executing Memory Script
cataloged datafile copy
datafile copy file name=/home/ora11g/11g/oradata/xcky/system01.dbf RECID=2 STAMP=953411462
cataloged datafile copy
datafile copy file name=/home/ora11g/11g/oradata/xcky/sysaux01.dbf RECID=3 STAMP=953411462
cataloged datafile copy
datafile copy file name=/home/ora11g/11g/oradata/xcky/undotbs01.dbf RECID=4 STAMP=953411462
cataloged datafile copy
datafile copy file name=/home/ora11g/11g/oradata/xcky/users01.dbf RECID=5 STAMP=953411462
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=953411462 file name=/home/ora11g/11g/oradata/xcky/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=953411462 file name=/home/ora11g/11g/oradata/xcky/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=953411462 file name=/home/ora11g/11g/oradata/xcky/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=953411462 file name=/home/ora11g/11g/oradata/xcky/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=953411462 file name=/opt/oradata/PHOTO_INFO.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=953411462 file name=/opt/oradata/XCKY_INFO.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=953411462 file name=/opt/oradata/BUNCH_INFO.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=9 STAMP=953411462 file name=/opt/oradata/TRANSFER_INFO.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=953411462 file name=/opt/oradata/OTHER_INFO.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=11 STAMP=953411462 file name=/opt/oradata/BULLETIN_INFO.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=12 STAMP=953411462 file name=/opt/oradata/SYSTEM_INFO.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=13 STAMP=953411462 file name=/opt/oradata/INDEX_INFO.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=14 STAMP=953411462 file name=/opt/oradata/VIEW_INFO.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=15 STAMP=953411462 file name=/opt/oradata/VIEW_LOG.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=16 STAMP=953411462 file name=/opt/oradata/XCKY.dbf
Finished Duplicate Db at 30-AUG-17
备库自动mount，开备库
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
Database altered.
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY PHYSICAL STANDBY
应用主库传过来的日志，保证数据一致性。
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
开主库
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PRIMARY
SQL> alter database open;
Database altered.
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY
默认为最大性能模式
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
3、DG维护
解决备库不能及时应用主库日志
查看哪些从主库传输过来的日志被apply
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
80 YES
......
94 YES
95 YES
96 YES
97 IN-MEMORY
18 rows selected
正常情况，日志会在内存中直接应用传输过来的日志；如果备库重启，就不会及时应用日志，导致主备数据不一致：
解决方案：
备库执行命令重新应用未被应用的日志
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
此时，就开启了RTA(Real Time Apply)
SQL> select process,client_process,status,thread#,sequence#,block#,blocks from v$managed_standby;
PROCESS CLIENT_P STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH ARCH
CLOSING 1
111 1
1
ARCH ARCH
CONNECTED 0
0 0
0
ARCH ARCH
CLOSING 1
90 1
466
ARCH ARCH
CLOSING 1
93 1
17
ARCH ARCH
CLOSING 1
112 1
2
ARCH ARCH
CLOSING 1
95 1
308
ARCH ARCH
CLOSING 1
113 1
1
ARCH ARCH
CLOSING 1
114 1
2
ARCH ARCH
CLOSING 1
100 1
7
ARCH ARCH
CONNECTED 0
0 0
0
ARCH ARCH
CLOSING 1
115 1
1
ARCH ARCH
CLOSING 1
116 1
2
ARCH ARCH
CLOSING 1
102 1
1
ARCH ARCH
CONNECTED 0
0 0
0
ARCH ARCH
CLOSING 1
103 1
2
ARCH ARCH
CONNECTED 0
0 0
0
ARCH ARCH
CLOSING 1
104 1
2
ARCH ARCH
CONNECTED 0
0 0
0
ARCH ARCH
CLOSING 1
105 1
1
ARCH ARCH
CONNECTED 0
0 0
0
ARCH ARCH
CLOSING 1
106 1
2
ARCH ARCH
CONNECTED 0
0 0
0
ARCH ARCH
CLOSING 1
107 1
1
ARCH ARCH
CONNECTED 0
0 0
0
ARCH ARCH
CONNECTED 0
0 0
0
ARCH ARCH
CLOSING 1
108 1
2
ARCH ARCH
CLOSING 1
109 1
1
ARCH ARCH
CONNECTED 0
0 0
0
ARCH ARCH
CONNECTED 0
0 0
0
ARCH ARCH
CLOSING 1
110 1
2
RFS ARCH
IDLE 0
0 0
0
RFS UNKNOWN IDLE
0 0
0 0
RFS UNKNOWN IDLE
0 0
0 0
RFS UNKNOWN IDLE
0 0
0 0
RFS LGWR
IDLE 1
117 200
1
MRP0 N/A APPLYING_LOG1 117 200
102400
36 rows selected.
SQL> select dest_id,recovery_mode from v$archive_dest_status
DEST_ID RECOVERY_MODE
---------- ---------------------------
1 MANAGED REAL TIME APPLY
我配置的是log_archive_dest_1，应该看为1的recovery_mode。
SQL> show parameter archive
NAME TYPE
VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
log_archive_config string dg_config=(prixcky,stdxcky)
log_archive_dest_1 string location=/home/ora11g/archlog valid_for=(all_logfiles,all_roles) db_unique_name=stdxcky