ORACLE 11g RAC-RAC DG Duplicate 搭建（生产操作文档）
环境：
rhel 6.7 64位
源库：ORACLE 11204 RAC 未打PSU
备库：ORACLE 11204 RAC PSU 20170718
一、停止中间件并做全库备份
1、在节点2做全备
2、首先要规划主、备库db_unique_name
主库： 备库：
============================================================
db_name mgrdb db_name mgrdb
db_unique_name mgrdb db_unique_name mgrdbdg
============================================================
3、vi /etc/hosts 修改主、备共计4个节点
=========================================================
#主库
由于是生产系统IP 省略
#备库
由于是生产系统IP 省略
============================================================
二、源库参数设置
1、检查源库每个节点至少3组redo
select group#,thread#,bytes/1024/1024,members,status from v$log;
GROUP# THREAD# BYTES/1024/1024 MEMBERS STATUS
---------- ---------- --------------- ---------- ----------------
5 1 500 2 INACTIVE
6 1 500 2 INACTIVE
7 1 500 2 INACTIVE
8 1 500 2 CURRENT
9 1 500 2 INACTIVE
10 1 500 2 INACTIVE
11 2 500 2 CURRENT
12 2 500 2 INACTIVE
13 2 500 2 INACTIVE
14 2 500 2 INACTIVE
15 2 500 2 INACTIVE
GROUP# THREAD# BYTES/1024/1024 MEMBERS STATUS
---------- ---------- --------------- ---------- ----------------
16 2 500 2 INACTIVE
1节点\2节点
alter database add logfile thread 1 group 5 ('+DATA1') size 500m;
alter database add logfile thread 2 group 6 ('+DATA1') size 500m;
2、添加Standby Redo Log
如果主库是Rac数据库，standby redo log组数=（所有节点中日志组数最大值+1）*RAC节点数；
#################################################################################################
--1节点
alter database add standby logfile thread 1 group 21('+DATA1','+DATA1') size 500m;
alter database add standby logfile thread 1 group 22('+DATA1','+DATA1') size 500m;
alter database add standby logfile thread 1 group 23('+DATA1','+DATA1') size 500m;
alter database add standby logfile thread 1 group 24('+DATA1','+DATA1') size 500m;
alter database add standby logfile thread 1 group 25('+DATA1','+DATA1') size 500m;
alter database add standby logfile thread 1 group 26('+DATA1','+DATA1') size 500m;
alter database add standby logfile thread 1 group 27('+DATA1','+DATA1') size 500m;
--2节点
alter database add standby logfile thread 2 group 31('+DATA1','+DATA1') size 500m;
alter database add standby logfile thread 2 group 32('+DATA1','+DATA1') size 500m;
alter database add standby logfile thread 2 group 33('+DATA1','+DATA1') size 500m;
alter database add standby logfile thread 2 group 34('+DATA1','+DATA1') size 500m;
alter database add standby logfile thread 2 group 35('+DATA1','+DATA1') size 500m;
alter database add standby logfile thread 2 group 36('+DATA1','+DATA1') size 500m;
alter database add standby logfile thread 2 group 37('+DATA1','+DATA1') size 500m;
##################################################################################################
select group#,thread#,sequence#,BYTES/1024/1024,archived,used,status from v$standby_log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 ARC USED STATUS
---------- ---------- ---------- --------------- --- ---------- ----------
21 1 0 500 YES 0 UNASSIGNED
22 1 0 500 YES 0 UNASSIGNED
23 1 0 500 YES 0 UNASSIGNED
24 1 0 500 YES 0 UNASSIGNED
25 1 0 500 YES 0 UNASSIGNED
26 1 0 500 YES 0 UNASSIGNED
27 1 0 500 YES 0 UNASSIGNED
31 2 0 500 YES 0 UNASSIGNED
32 2 0 500 YES 0 UNASSIGNED
33 2 0 500 YES 0 UNASSIGNED
34 2 0 500 YES 0 UNASSIGNED
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 ARC USED STATUS
---------- ---------- ---------- --------------- --- ---------- ----------
35 2 0 500 YES 0 UNASSIGNED
36 2 0 500 YES 0 UNASSIGNED
37 2 0 500 YES 0 UNASSIGNED
3、判断DG软件是否安装
select * from v$option where parameter = 'Oracle Data Guard';
4、允许Forced Logging
alter database force logging;
select INST_ID,name,force_logging from gv$database;
--备份原始参数文件
create pfile='/home/oracle/pfile0908.bak' from spfile;
!ls -ltr /home/oracle
5、设置主库初始化参数
##################################################################################################
select name,db_unique_name from v$database;
--alter system set db_unique_name=mgrdb scope=spfile sid='*';
alter system set log_archive_config='dg_config=(mgrdb,mgrdbdg)' scope=spfile sid='*';
alter system set log_archive_dest_1='LOCATION=+DATA1 valid_for=(ALL_LOGFILES, ALL_ROLES) db_unique_name=mgrdb' scope=spfile sid='*';
alter system set log_archive_dest_2='SERVICE=standby OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE) DB_UNIQUE_NAME=mgrdbdg' scope=spfile sid='*';
alter system set log_archive_format = '%t_%s_%r.arc' scope=spfile sid='*';
alter system set log_archive_dest_state_1=enable scope=spfile sid='*';
alter system set log_archive_dest_state_2=enable scope=spfile sid='*';
alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile sid='*';
alter system set log_archive_max_processes = 10 scope=spfile sid='*';
--Primary Database:Standby Role initialization parameters FAL参数定义的数据库名同样取自本地tnsnames.ora里配置的Oracle Net Service Name.
alter system set fal_server=standby scope=spfile sid='*';
alter system set standby_file_management=auto scope=spfile sid='*';
alter system set db_file_name_convert='+DATA1','+DATA1' scope=spfile sid='*'; --备端的磁盘组写前面
alter system set log_file_name_convert='+DATA1','+DATA1' scope=spfile sid='*'; --备端的磁盘组写前面
##################################################################################################
#为使修改生效，重启Primary Database:
alter system archive log current;
alter system archive log current;
alter system checkpoint;
alter system checkpoint;
ps -ef|grep LOCAL=NO
set pages 999
set lines 200
col machine for a30
select MACHINE,s.USERNAME, SID,s.SERIAL#,p.spid from v$session s,v$process p
where s.paddr = p.addr and s.username is not null
order by spid;
ps -ef|grep LOCAL=NO|grep -v grep|cut -c 9-15|xargs kill -9
#分别关闭2节点
shut immediate;
#srvctl stop database -d mgrdb
#srvctl start database -d mgrdb
#重启完后，可通过下面语句查看修改地方：
set lines 500 pages 0
col value for a90
col name for a50
select name,value
from v$parameter
where name in('db_name','db_unique_name',
'log_archive_config',
'log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_1',
'log_archive_dest_2',
'remote_login_passwordfile',
'log_archive_format',
'log_archive_max_processes',
'fal_server',
'db_file_name_convert',
'log_file_name_convert',
'standby_file_management')
/
6、将数据库备份、备份控制文件、备份参数文件
su - oracle
mkdir backup
rman target /
run
{allocate channel c1 type disk;
allocate channel c2 type disk;
backup filesperset 3 database format '/home/oracle/backup/full_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog from time 'sysdate-1/24' format '/home/oracle/backup/arch_%d_%T_%s_%p';
backup current controlfile for standby format '/home/oracle/backup/ctl_std';
}
7、创建备库的pfile
su - oracle
mkdir backup
sqlplus / as sysdba
CREATE PFILE='/home/oracle/backup/initstandby.ora' FROM SPFILE;
--查看备份出来的参数文件
cat /home/oracle/backup/initstandby.ora
#################################################################################
mgrdb1.__db_cache_size=14629732352
mgrdb2.__db_cache_size=14361296896
mgrdb1.__java_pool_size=939524096
mgrdb2.__java_pool_size=939524096
mgrdb1.__large_pool_size=134217728
mgrdb2.__large_pool_size=402653184
mgrdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
mgrdb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
mgrdb1.__pga_aggregate_target=39996882944
mgrdb2.__pga_aggregate_target=39460012032
mgrdb1.__sga_target=24427626496
mgrdb2.__sga_target=24964497408
mgrdb1.__shared_io_pool_size=0
mgrdb2.__shared_io_pool_size=0
mgrdb1.__shared_pool_size=7784628224
mgrdb2.__shared_pool_size=8321499136
mgrdb1.__streams_pool_size=268435456
mgrdb2.__streams_pool_size=268435456
*.audit_file_dest='/u01/app/oracle/admin/mgrdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA1/mgrdb/controlfile/current.260.834178123','+DATA1/mgrdb/controlfile/current.3076.947956273'
*.db_block_size=8192
*.db_create_file_dest='+DATA1'
*.db_domain=''
*.db_file_name_convert='+DATA1','+DATA1'
*.db_name='mgrdb'
*.diagnostic_dest='/u01/app/oracle'
*.fal_server='STANDBY'
mgrdb1.instance_number=1
mgrdb2.instance_number=2
*.job_queue_processes=1000
*.log_archive_config='dg_config=(mgrdb,mgrdbdg)'
*.log_archive_dest_1='LOCATION=+DATA1 valid_for=(ALL_LOGFILES, ALL_ROLES) db_unique_name=mgrdb'
*.log_archive_dest_2='SERVICE=standby OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE) DB_UNIQUE_NAME=mgrdbdg'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=10
*.log_buffer=306790400
*.log_file_name_convert='+DATA1','+DATA1'
*.memory_max_target=64424509440
*.memory_target=64424509440
*.open_cursors=300
*.parallel_force_local=FALSE
*.pga_aggregate_target=25769803776
mgrdb2.pga_aggregate_target=0
mgrdb1.pga_aggregate_target=0
*.processes=1000
*.remote_listener='scanip:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1600
*.sga_max_size=38654705664
mgrdb2.sga_max_size=64424509440
mgrdb1.sga_max_size=64424509440
*.sga_target=38654705664
mgrdb1.sga_target=0
mgrdb2.sga_target=0
*.shared_pool_reserved_size=288568115
mgrdb2.shared_pool_reserved_size=524288000
mgrdb1.shared_pool_reserved_size=524288000
*.shared_pool_size=805306368
mgrdb1.shared_pool_size=0
mgrdb2.shared_pool_size=0
*.standby_file_management='AUTO'
*.streams_pool_size=268435456
mgrdb2.thread=2
mgrdb1.thread=1
*.undo_retention=86400
mgrdb2.undo_tablespace='UNDOTBS2'
mgrdb1.undo_tablespace='UNDOTBS1'
*.undo_tablespace='UNDOTBS2'
#################################################################################
8、修改主库tnsnames.ora 【两个节点都要修改】
#修改1节点：
vi $ORACLE_HOME/network/admin/tnsnames.ora
#拷贝到2节点
scp $ORACLE_HOME/network/admin/tnsnames.ora HOSTNAME:$ORACLE_HOME/network/admin
########################################################################33
primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = public ip )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mgrdb)
)
)
primary1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mgrdb)
(SID = mgrdb1)
)
)
primary2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mgrdb)
(SID = mgrdb2)
)
)
########################################################################
三、准备备库RAC Database
1、复制参数文件
从主库备份出来的参数拷贝到备库上，选择一个节点
在主库上：
scp /home/oracle/backup/initstandby.ora hostname:/app/u01/oracle/product/11.2.0/db_1/dbs/initstandby.ora
2、复制源端密码文件
配置DG需要两边数据库密码保持一致，把主库的密码文件分别拷贝到【备库两个节点】
在主库上：
cd $ORACLE_HOME/dbs/
scp orapwmgrdb1 hostname:/app/u01/oracle/product/11.2.0/db_1/dbs/
scp orapwmgrdb1 hostname:/app/u01/oracle/product/11.2.0/db_1/dbs/
拷贝完后修改【备库第二节点】的密码文件名：
在备库2节点：
cd $ORACLE_HOME/dbs/
mv orapwmgrdb1 orapwmgrdb2
3、将主库备份文件拷贝到备端【因为用DUPLICATE方式，此步不做】
#scp -r /home/oracle/backup hostname:/home/oracle
4、按照参数文件中指定的目录去创建相关目录
【备端1节点】：
su - oracle
mkdir -p /app/u01/oracle/admin/mgrdbdg/adump
chmod 775 /app/u01/oracle/admin/mgrdbdg/adump
【备端2节点】：
mkdir -p /app/u01/oracle/admin/mgrdbdg/adump
chmod 775 /app/u01/oracle/admin/mgrdbdg/adump
5、【修改备库参数文件】
加注释的为修改的地方，其他保留即可
cd $ORACLE_HOME/dbs
vi initstandby.ora
##################################################################################################
*.audit_file_dest='/app/u01/oracle/admin/mgrdbdg/adump' =============
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA1/mgrdbdg/controlfile/control01.ctl' =============
*.db_block_size=8192
*.db_create_file_dest='+DATA1' =============
*.db_domain=''
*.db_file_name_convert='+DATA1','+DATA1' =============
*.db_name='mgrdb'
*.db_unique_name='mgrdbdg' =============
*.diagnostic_dest='/oracle/app/oracle' =============
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcldbXDB)'
*.fal_server='primary' =============
orcldb2.instance_number=2
orcldb1.instance_number=1
*.log_archive_config='dg_config=(mgrdb,mgrdbdg)'
*.log_archive_dest_1='LOCATION=+DATA1 valid_for=(ALL_LOGFILES, ALL_ROLES) db_unique_name=mgrdbdg' =============
*.log_archive_dest_2='SERVICE=primary OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE) DB_UNIQUE_NAME=mgrdb' =============
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=10
*.log_file_name_convert='+DATA1','+DATA1' =============
*.open_cursors=300
*.pga_aggregate_target=545259520 =============
*.processes=150
*.remote_listener='scanip:1521' =============
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=817889280
*.standby_file_management='AUTO'
orcldb2.thread=2
orcldb1.thread=1
orcldb2.undo_tablespace='UNDOTBS2'
orcldb1.undo_tablespace='UNDOTBS1'
##################################################################################################
*.audit_file_dest='/app/u01/oracle/admin/mgrdbdg/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA1/mgrdbdg/controlfile/control01.ctl','+DATA1/mgrdbdg/controlfile/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA1'
*.db_domain=''
*.db_file_name_convert='+DATA1','+DATA1'
*.db_name='mgrdb'
*.diagnostic_dest='/app/u01/oracle'
*.fal_server='primary'
mgrdb1.instance_number=1
mgrdb2.instance_number=2
*.job_queue_processes=1000
*.log_archive_config='dg_config=(mgrdb,mgrdbdg)'
*.log_archive_dest_1='LOCATION=+DATA1 valid_for=(ALL_LOGFILES, ALL_ROLES) db_unique_name=mgrdbdg'
*.log_archive_dest_2='SERVICE=primary OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE) DB_UNIQUE_NAME=mgrdb'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=10
*.log_buffer=306790400
*.log_file_name_convert='+DATA1','+DATA1'
*.memory_max_target=64424509440
*.memory_target=64424509440
*.open_cursors=300
*.parallel_force_local=FALSE
*.pga_aggregate_target=25769803776
mgrdb2.pga_aggregate_target=0
mgrdb1.pga_aggregate_target=0
*.processes=1000
*.remote_listener='scanip:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1600
*.sga_max_size=38654705664
mgrdb2.sga_max_size=64424509440
mgrdb1.sga_max_size=64424509440
*.sga_target=38654705664
mgrdb1.sga_target=0
mgrdb2.sga_target=0
*.shared_pool_reserved_size=288568115
mgrdb2.shared_pool_reserved_size=524288000
mgrdb1.shared_pool_reserved_size=524288000
*.shared_pool_size=805306368
mgrdb1.shared_pool_size=0
mgrdb2.shared_pool_size=0
*.standby_file_management='AUTO'
*.streams_pool_size=268435456
mgrdb2.thread=2
mgrdb1.thread=1
*.undo_retention=86400
mgrdb2.undo_tablespace='UNDOTBS2'
mgrdb1.undo_tablespace='UNDOTBS1'
*.undo_tablespace='UNDOTBS2'
##################################################################################################
6、【备库1节点】创建ASM路径
通过grid用户进入到asmcmd,在备库磁盘组下创建db_unique_name目录
su - grid
asmcmd
ASMCMD> mkdir DATA1/mgrdbdg
7、修改备库tnanames.ora 两个节点都要修改
修改【备库1、2节点】tnanames.ora
vi $ORACLE_HOME/network/admin/tnsnames.ora
scp $ORACLE_HOME/network/admin/tnsnames.ora hostname:$ORACLE_HOME/network/admin
##################################################################################################
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan-ip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mgrdbdg)
)
)
standby1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mgrdbdg)
(SID = mgrdb1)
)
)
standby2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
(SID = mgrdb2)
)
)
primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan-ip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mgrdb)
)
)
--标注的primary服务名必须添加，否则启动mrp时日志会报如下错误：
Error 12154 received logging on to the standby
FAL[client, MRP0]: Error 12154 connecting to orcldb fro fetching gap sequence
FAIL_CLIENT 和 FAIL_SERVER 这两个参数值都需指定网络服务器，并非是db_unique_name
FAL_SERVER=net_service_name
FAL_CLIENT=net_service_name
##################################################################################################
四、创建物理备库
1、使用修改好的参数备库实例启动到nomount
连接到oracle用户，使用上面已修改好的参数文件将备库实例启动到nomount
仅在【备库的1节点】操作：
echo $ORACLE_SID
mgrdb1
sqlplus / as sysdba
startup nomount pfile = '$ORACLE_HOME/dbs/initstandby.ora'
2、RMAN Duplicate复制数据库
使用Rman Duplicate 方式，须先配置监听，添加静态服务名。
Oracle 11gR2开始，所有高可用架构，如oracle restart,rac等，监听器的创建和管理
都是由grid用户完成。
在【备库1节点】：
su - grid
cd $ORACLE_HOME/network/admin
cp listener.ora listener.ora.bak
cp endpoints_listener.ora endpoints_listener.ora.bak
vi listener.ora
【添加】以下行：
===================================================
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = ANYTHING))
(ADDRESS = (PROTOCOL = TCP)(HOST = public ip)(PORT = 1521))
) )
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mgrdbdg)
(ORACLE_HOME = /app/u01/oracle/product/11.2.0/db_1)
(SID_NAME = mgrdb1)
) )
===================================================
#重启监听器并查看：
su - grid
grid> srvctl stop listener -n mgrdb1
grid> srvctl start listener -n mgrdb1
grid> lsnrctl status --正常应该显示如下信息
Service "mgrdbdg" has 1 instance(s).
Instance "mgrdb1", status UNKNOWN, has 1 handler(s) for this service...
Instance "mgrdb1", status BLOCKED, has 1 handler(s) for this service...
#在【主库1节点】 tnsnames.ora 中【添加】下面信息
su - oracle
cd $ORACLE_HOME/network/admin
vi tnsnames.ora
添加如下内容：
========================================================
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = public ip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mgrdbdg)
)
)
========================================================
启动监听，【主备库】共计4个节点都要测试
主库1节点
tnsping standby 3
tnsping primary 3
在主库1节点测试
sqlplus 'sys/"XXXX"'@standby as sysdba
在进制Rman Duplicate操作之前，我们还要修改oracle二进制命令的权限，参考下面：
----------------------------------------------------------------------------------
During 11.2 Gird Infrastructure installation, it prompts to select ASM admin group and AS
M dba group. Assume asmadmin is used for ASM admin group and asmdba is used for
ASM dba group.
Only users that are members of the asmadmin group have direct access to ASM disks an
d maintenance. For other database users (software owners or dba group users), the acce
ss is gained via the oracle executable ($ORACLE_HOME/bin/oracle). It should have a setg
id bit with group set to "asmadmin".
The 11.2 "oracle" binary is changed automatically via setasmgidwrap when the instance i
s started by the CRS daemon (e.g. srvctl start database/instance). But for earlier release,
the "oracle" binary group and ownership need to be set manually.
----------------------------------------------------------------------------------
#在【备库1节点】操作：
su - grid
ls -lrt /app/u01/oracle/product/11.2.0/db_1/bin/oracle
-rwsr-s--x 1 oracle oinstall 239626641 Dec 29 2015 /app/u01/oracle/product/11.2.0/db_1/bin/oracle
/app/u01/11.2.0/grid/bin/setasmgidwrap -o=/app/u01/oracle/product/11.2.0/db_1/bin/oracle
[grid@mgrdb1 ~]$ ls -l /app/u01/oracle/product/11.2.0/db_1/bin/oracle
-rwsr-s--x 1 oracle asmadmin 239869951 Sep 8 10:20 /app/u01/oracle/product/11.2.0/db_1/bin/oracle
如果从主库连备库
sqlplus 'sys/"XXX"'@standby as sysdba
报ORA-12537: TNS:connection closed
是因为$ORACLE_HOME/bin/oracle权限有问题，使用下面命令修改：
chmod u+s oracle
chmod g+s oracle
显示如下：
[oracle@mgrdb1 bin]$ pwd
/app/u01/oracle/product/11.2.0/db_1/bin
[oracle@mgrdb1 bin]$ ls -l oracle
-rwsr-x--x 1 oracle asmadmin 239869951 Sep 8 10:20 oracle
如果不做上面的操作，在进行下面 DUPLICATE 复制操作时候会报 ASMLib 驱动错误：
---------------------------------------------------------------------------
ORA-15183: ASMLIB initialization error [driver/agent not installed]
WARNING: FAILED to load library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so
Errors in file /u01/app/oracle/diag/rdbms/standby/standby1/trace/standby1_rbal_13010.trc:
ORA-15183: ASMLIB initialization error [driver/agent not installed]
Mon Sep 03 09:30:10 2012
SUCCESS: diskgroup SDATA was mounted
Errors in file /u01/app/oracle/diag/rdbms/standby/standby1/trace/standby1_ora_13390.trc
(incident=305):
ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [], [], [], [],
[], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/standby/standby1/incident/incdir_305/standb
y1_ora_13390_i305.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Sep 03 09:30:18 2012
Dumping diagnostic data in directory=[cdmp_20120903093018], requested by (instance=1,
osid=13390), summary=[incident=305].
Errors in file /u01/app/oracle/diag/rdbms/standby/standby1/trace/standby1_ora_13390.trc
(incident=306):
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/standby/standby1/incident/incdir_306/standb
y1_ora_13390_i306.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ERROR: unrecoverable error ORA-600 raised in ASM I/O path; terminating process 13390
Dumping diagnostic data in directory=[cdmp_20120903093019], requested by (instance=1,
osid=13390), summary=[incident=306].
Mon Sep 03 09:30:20 2012
---------------------------------------------------------------------------
#正式开始 RAMN DUPLICATE 复制
在【主库1节点上】操作 【************一定要用后台脚本执行************】：
#su - oracle
#rman target / auxiliary sys/XXX@standby
#RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;
#查看非OMF类型的文件，然后加到脚本里：
select file#,name from v$datafile;
自动传输脚本：dup-mgrdb.sh
注意，如果文件名不是ASM 自动命令格式，需要SET newname。
su - root
cd /home/oracle
touch dup-mgrdb.sh
chmod +x dup-mgrdb.sh
=================================================================
su - oracle -c "
rman target / auxiliary 'sys/"XXXX"'@standby << EOF
run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
allocate channel d7 type disk;
set newname for datafile 3 to '+DATA1';
set newname for datafile 5 to '+DATA1';
set newname for datafile 6 to '+DATA1';
set newname for datafile 7 to '+DATA1';
set newname for datafile 8 to '+DATA1';
set newname for datafile 9 to '+DATA1';
set newname for datafile 10 to '+DATA1';
set newname for datafile 11 to '+DATA1';
set newname for datafile 12 to '+DATA1';
set newname for datafile 16 to '+DATA1';
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;
}
EOF
"
=================================================================
nohup ./dup-mgrdb.sh > dup-mgrdb.log 2>&1 &
-----------------------------------------------------------------------------
完整输出内容：
Starting Duplicate Db at 2017-09-01 05:08:21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=33 instance=orcldb1 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/oracle/app/oracle/product/11.2.0/db_1/dbs/orapworcldb1' auxiliary format
'/oracle/app/oracle/product/11.2.0/db_1/dbs/orapworcldb1' ;
}
executing Memory Script
Starting backup at 2017-09-01 05:08:22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 instance=orcldb1 device type=DISK
Finished backup at 2017-09-01 05:08:23
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DATA1/standby/controlfile/control01.ctl';
}
executing Memory Script
Starting backup at 2017-09-01 05:08:23
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcldb1.f tag=TAG20170901T050824 RECID=1 STAMP=953528905
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2017-09-01 05:08:28
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 1 to
"+stdatadg";
switch clone tempfile all;
set newname for datafile 1 to
"+stdatadg";
set newname for datafile 2 to
"+stdatadg";
set newname for datafile 3 to
"+stdatadg";
set newname for datafile 4 to
"+stdatadg";
set newname for datafile 5 to
"+stdatadg";
set newname for datafile 6 to
"+stdatadg";
backup as copy reuse
datafile 1 auxiliary format
"+stdatadg" datafile
2 auxiliary format
"+stdatadg" datafile
3 auxiliary format
"+stdatadg" datafile
4 auxiliary format
"+stdatadg" datafile
5 auxiliary format
"+stdatadg" datafile
6 auxiliary format
"+stdatadg" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +stdatadg 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
Starting backup at 2017-09-01 05:08:37
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATADG/orcldb/datafile/undotbs1.261.946699483
output file name=+STDATADG/standby/datafile/undotbs1.257.953529099 tag=TAG20170901T050837
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATADG/orcldb/datafile/system.259.946699473
output file name=+STDATADG/standby/datafile/system.258.953529113 tag=TAG20170901T050837
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATADG/orcldb/datafile/sysaux.260.946699479
output file name=+STDATADG/standby/datafile/sysaux.259.953529129 tag=TAG20170901T050837
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATADG/orcldb/datafile/undotbs2.263.946699493
output file name=+STDATADG/standby/datafile/undotbs2.260.953529145 tag=TAG20170901T050837
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=+DATADG/primary/datafile/test.296.953464749
output file name=+STDATADG/standby/datafile/test.261.953529147 tag=TAG20170901T050837
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=+DATADG/orcldb/datafile/users.264.946699495
output file name=+STDATADG/standby/datafile/users.262.953529151 tag=TAG20170901T050837
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2017-09-01 05:09:30
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=953529154 file name=+STDATADG/standby/datafile/system.258.953529113
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=953529154 file name=+STDATADG/standby/datafile/sysaux.259.953529129
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=953529154 file name=+STDATADG/standby/datafile/undotbs1.257.953529099
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=953529154 file name=+STDATADG/standby/datafile/undotbs2.260.953529145
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=953529154 file name=+STDATADG/standby/datafile/users.262.953529151
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=953529154 file name=+STDATADG/standby/datafile/test.261.953529147
Finished Duplicate Db at 2017-09-01 05:09:40
RMAN>
-----------------------------------------------------------------------------
下面为复制数据库过程中 备库端alert输出的完整日志：
--------------------------------------------------------------------------
NOTE: Loaded library: System
Fri Sep 01 05:11:26 2017
SUCCESS: diskgroup STDATADG was mounted
Fri Sep 01 05:11:26 2017
ERROR: failed to establish dependency between database STANDBY and diskgroup resource ora.STDATADG.dg
Fri Sep 01 05:11:28 2017
RFS connections have been disallowed
alter database mount standby database
This instance was first to mount
Set as converted control file due to db_unique_name mismatch
Changing di2dbun from PRIMARY to STANDBY
Fri Sep 01 05:11:33 2017
NSS2 started with pid=34, OS id=11471
ARCH: STARTING ARCH PROCESSES
Fri Sep 01 05:11:36 2017
ARC0 started with pid=35, OS id=11473
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Fri Sep 01 05:11:37 2017
ARC1 started with pid=37, OS id=11475
Fri Sep 01 05:11:37 2017
ARC2 started with pid=38, OS id=11477
Fri Sep 01 05:11:37 2017
Successful mount of redo thread 1, with mount id 2389489025
Physical Standby Database mounted.
Lost write protection disabled
Fri Sep 01 05:11:37 2017
ARC3 started with pid=39, OS id=11479
Fri Sep 01 05:11:37 2017
ARC4 started with pid=40, OS id=11481
Fri Sep 01 05:11:37 2017
ARC5 started with pid=41, OS id=11483
Create Relation IPS_PACKAGE_UNPACK_HISTORY
Fri Sep 01 05:11:37 2017
ARC6 started with pid=42, OS id=11486
Fri Sep 01 05:11:37 2017
ARC7 started with pid=43, OS id=11488
Fri Sep 01 05:11:37 2017
ARC8 started with pid=44, OS id=11490
ARC1: Archival started
ARC2: Archival started
ARC3: Archival started
ARC4: Archival started
ARC5: Archival started
ARC6: Archival started
ARC7: Archival started
ARC8: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC2: Becoming the active heartbeat ARCH
ARC2: Becoming the active heartbeat ARCH
Fri Sep 01 05:11:37 2017
ARC9 started with pid=45, OS id=11492
Completed: alter database mount standby database
Error 12154 received logging on to the standby
FAL[client, ARC3]: Error 12154 connecting to PRIMARY for fetching gap sequence
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+STDATADG/orcldb/onlinelog/group_1.257.946699471'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_1.257.946699471
ORA-15012: ASM file '+STDATADG/orcldb/onlinelog/group_1.257.946699471' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+STDATADG/orcldb/onlinelog/group_1.257.946699471'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_1.257.946699471
ORA-15012: ASM file '+STDATADG/orcldb/onlinelog/group_1.257.946699471' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '+STDATADG/orcldb/onlinelog/group_2.258.946699473'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_2.258.946699473
ORA-15012: ASM file '+STDATADG/orcldb/onlinelog/group_2.258.946699473' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '+STDATADG/orcldb/onlinelog/group_2.258.946699473'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_2.258.946699473
ORA-15012: ASM file '+STDATADG/orcldb/onlinelog/group_2.258.946699473' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 3 of thread 2
ORA-00312: online log 3 thread 2: '+STDATADG/orcldb/onlinelog/group_3.265.946701385'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_3.265.946701385
ORA-15012: ASM file '+STDATADG/orcldb/onlinelog/group_3.265.946701385' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 3 of thread 2
ORA-00312: online log 3 thread 2: '+STDATADG/orcldb/onlinelog/group_3.265.946701385'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_3.265.946701385
ORA-15012: ASM file '+STDATADG/orcldb/onlinelog/group_3.265.946701385' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 4 of thread 2
ORA-00312: online log 4 thread 2: '+STDATADG/orcldb/onlinelog/group_4.266.946701385'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_4.266.946701385
ORA-15012: ASM file '+STDATADG/orcldb/onlinelog/group_4.266.946701385' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 4 of thread 2
ORA-00312: online log 4 thread 2: '+STDATADG/orcldb/onlinelog/group_4.266.946701385'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_4.266.946701385
ORA-15012: ASM file '+STDATADG/orcldb/onlinelog/group_4.266.946701385' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 11 of thread 1
ORA-00312: online log 11 thread 1: '+STDATADG/orcldb/onlinelog/group_11.268.953459033'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_11.268.953459033
ORA-15012: ASM file '+STDATADG/orcldb/onlinelog/group_11.268.953459033' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 11 of thread 1
ORA-00312: online log 11 thread 1: '+STDATADG/orcldb/onlinelog/group_11.268.953459033'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_11.268.953459033
ORA-15012: ASM file '+STDATADG/orcldb/onlinelog/group_11.268.953459033' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 12 of thread 1
ORA-00312: online log 12 thread 1: '+STDATADG/orcldb/onlinelog/group_12.269.953459033'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_12.269.953459033
ORA-15012: ASM file '+STDATADG/orcldb/onlinelog/group_12.269.953459033' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 12 of thread 1
ORA-00312: online log 12 thread 1: '+STDATADG/orcldb/onlinelog/group_12.269.953459033'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_12.269.953459033
ORA-15012: ASM file '+STDATADG/orcldb/onlinelog/group_12.269.953459033' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 13 of thread 1
ORA-00312: online log 13 thread 1: '+STDATADG/orcldb/onlinelog/group_13.270.953459035'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_13.270.953459035
ORA-15012: ASM file '+STDATADG/orcldb/onlinelog/group_13.270.953459035' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 13 of thread 1
ORA-00312: online log 13 thread 1: '+STDATADG/orcldb/onlinelog/group_13.270.953459035'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_13.270.953459035
ORA-15012: ASM file '+STDATADG/orcldb/onlinelog/group_13.270.953459035' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 18 of thread 2
ORA-00312: online log 18 thread 2: '+STDATADG/primary/onlinelog/group_18.273.953466059'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/primary/onlinelog/group_18.273.953466059
ORA-15012: ASM file '+STDATADG/primary/onlinelog/group_18.273.953466059' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 18 of thread 2
ORA-00312: online log 18 thread 2: '+STDATADG/primary/onlinelog/group_18.273.953466059'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/primary/onlinelog/group_18.273.953466059
ORA-15012: ASM file '+STDATADG/primary/onlinelog/group_18.273.953466059' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 19 of thread 2
ORA-00312: online log 19 thread 2: '+STDATADG/primary/onlinelog/group_19.272.953466059'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/primary/onlinelog/group_19.272.953466059
ORA-15012: ASM file '+STDATADG/primary/onlinelog/group_19.272.953466059' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 19 of thread 2
ORA-00312: online log 19 thread 2: '+STDATADG/primary/onlinelog/group_19.272.953466059'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/primary/onlinelog/group_19.272.953466059
ORA-15012: ASM file '+STDATADG/primary/onlinelog/group_19.272.953466059' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 20 of thread 2
ORA-00312: online log 20 thread 2: '+STDATADG/primary/onlinelog/group_20.271.953466059'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/primary/onlinelog/group_20.271.953466059
ORA-15012: ASM file '+STDATADG/primary/onlinelog/group_20.271.953466059' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_lgwr_6281.trc:
ORA-00313: open failed for members of log group 20 of thread 2
ORA-00312: online log 20 thread 2: '+STDATADG/primary/onlinelog/group_20.271.953466059'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/primary/onlinelog/group_20.271.953466059
ORA-15012: ASM file '+STDATADG/primary/onlinelog/group_20.271.953466059' does not exist
ARC9: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Fri Sep 01 05:12:34 2017
Switch of datafile 1 complete to datafile copy
checkpoint is 1261264
Switch of datafile 2 complete to datafile copy
checkpoint is 1261282
Switch of datafile 3 complete to datafile copy
checkpoint is 1261246
Switch of datafile 4 complete to datafile copy
checkpoint is 1261309
Switch of datafile 5 complete to datafile copy
checkpoint is 1261323
Switch of datafile 6 complete to datafile copy
checkpoint is 1261317
alter database clear logfile group 1
Clearing online log 1 of thread 1 sequence number 114
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+STDATADG/orcldb/onlinelog/group_1.257.946699471'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_1.257.946699471
ORA-15173: entry 'orcldb' does not exist in directory '/'
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+STDATADG/orcldb/onlinelog/group_1.257.946699471'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_1.257.946699471
ORA-15173: entry 'orcldb' does not exist in directory '/'
Deleted Oracle managed file +STDATADG/orcldb/onlinelog/group_1.257.946699471
Completed: alter database clear logfile group 1
alter database clear logfile group 2
Clearing online log 2 of thread 1 sequence number 115
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '+STDATADG/orcldb/onlinelog/group_2.258.946699473'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_2.258.946699473
ORA-15173: entry 'orcldb' does not exist in directory '/'
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '+STDATADG/orcldb/onlinelog/group_2.258.946699473'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_2.258.946699473
ORA-15173: entry 'orcldb' does not exist in directory '/'
Deleted Oracle managed file +STDATADG/orcldb/onlinelog/group_2.258.946699473
Completed: alter database clear logfile group 2
alter database clear logfile group 3
Clearing online log 3 of thread 2 sequence number 90
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 3 of thread 2
ORA-00312: online log 3 thread 2: '+STDATADG/orcldb/onlinelog/group_3.265.946701385'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_3.265.946701385
ORA-15173: entry 'orcldb' does not exist in directory '/'
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 3 of thread 2
ORA-00312: online log 3 thread 2: '+STDATADG/orcldb/onlinelog/group_3.265.946701385'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_3.265.946701385
ORA-15173: entry 'orcldb' does not exist in directory '/'
Deleted Oracle managed file +STDATADG/orcldb/onlinelog/group_3.265.946701385
Completed: alter database clear logfile group 3
alter database clear logfile group 4
Clearing online log 4 of thread 2 sequence number 91
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 4 of thread 2
ORA-00312: online log 4 thread 2: '+STDATADG/orcldb/onlinelog/group_4.266.946701385'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_4.266.946701385
ORA-15173: entry 'orcldb' does not exist in directory '/'
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 4 of thread 2
ORA-00312: online log 4 thread 2: '+STDATADG/orcldb/onlinelog/group_4.266.946701385'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_4.266.946701385
ORA-15173: entry 'orcldb' does not exist in directory '/'
Deleted Oracle managed file +STDATADG/orcldb/onlinelog/group_4.266.946701385
Completed: alter database clear logfile group 4
alter database clear logfile group 11
Clearing online log 11 of thread 1 sequence number 0
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 11 of thread 1
ORA-00312: online log 11 thread 1: '+STDATADG/orcldb/onlinelog/group_11.268.953459033'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_11.268.953459033
ORA-15173: entry 'orcldb' does not exist in directory '/'
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 11 of thread 1
ORA-00312: online log 11 thread 1: '+STDATADG/orcldb/onlinelog/group_11.268.953459033'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_11.268.953459033
ORA-15173: entry 'orcldb' does not exist in directory '/'
Deleted Oracle managed file +STDATADG/orcldb/onlinelog/group_11.268.953459033
Completed: alter database clear logfile group 11
alter database clear logfile group 12
Clearing online log 12 of thread 1 sequence number 0
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 12 of thread 1
ORA-00312: online log 12 thread 1: '+STDATADG/orcldb/onlinelog/group_12.269.953459033'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_12.269.953459033
ORA-15173: entry 'orcldb' does not exist in directory '/'
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 12 of thread 1
ORA-00312: online log 12 thread 1: '+STDATADG/orcldb/onlinelog/group_12.269.953459033'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_12.269.953459033
ORA-15173: entry 'orcldb' does not exist in directory '/'
Deleted Oracle managed file +STDATADG/orcldb/onlinelog/group_12.269.953459033
Completed: alter database clear logfile group 12
alter database clear logfile group 13
Clearing online log 13 of thread 1 sequence number 0
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 13 of thread 1
ORA-00312: online log 13 thread 1: '+STDATADG/orcldb/onlinelog/group_13.270.953459035'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_13.270.953459035
ORA-15173: entry 'orcldb' does not exist in directory '/'
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 13 of thread 1
ORA-00312: online log 13 thread 1: '+STDATADG/orcldb/onlinelog/group_13.270.953459035'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/orcldb/onlinelog/group_13.270.953459035
ORA-15173: entry 'orcldb' does not exist in directory '/'
Deleted Oracle managed file +STDATADG/orcldb/onlinelog/group_13.270.953459035
Completed: alter database clear logfile group 13
alter database clear logfile group 18
Clearing online log 18 of thread 2 sequence number 0
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 18 of thread 2
ORA-00312: online log 18 thread 2: '+STDATADG/primary/onlinelog/group_18.273.953466059'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/primary/onlinelog/group_18.273.953466059
ORA-15173: entry 'primary' does not exist in directory '/'
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 18 of thread 2
ORA-00312: online log 18 thread 2: '+STDATADG/primary/onlinelog/group_18.273.953466059'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/primary/onlinelog/group_18.273.953466059
ORA-15173: entry 'primary' does not exist in directory '/'
Deleted Oracle managed file +STDATADG/primary/onlinelog/group_18.273.953466059
Completed: alter database clear logfile group 18
alter database clear logfile group 19
Clearing online log 19 of thread 2 sequence number 0
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 19 of thread 2
ORA-00312: online log 19 thread 2: '+STDATADG/primary/onlinelog/group_19.272.953466059'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/primary/onlinelog/group_19.272.953466059
ORA-15173: entry 'primary' does not exist in directory '/'
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 19 of thread 2
ORA-00312: online log 19 thread 2: '+STDATADG/primary/onlinelog/group_19.272.953466059'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/primary/onlinelog/group_19.272.953466059
ORA-15173: entry 'primary' does not exist in directory '/'
Deleted Oracle managed file +STDATADG/primary/onlinelog/group_19.272.953466059
Completed: alter database clear logfile group 19
alter database clear logfile group 20
Clearing online log 20 of thread 2 sequence number 0
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 20 of thread 2
ORA-00312: online log 20 thread 2: '+STDATADG/primary/onlinelog/group_20.271.953466059'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/primary/onlinelog/group_20.271.953466059
ORA-15173: entry 'primary' does not exist in directory '/'
Errors in file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_11431.trc:
ORA-00313: open failed for members of log group 20 of thread 2
ORA-00312: online log 20 thread 2: '+STDATADG/primary/onlinelog/group_20.271.953466059'
ORA-17503: ksfdopn:2 Failed to open file +STDATADG/primary/onlinelog/group_20.271.953466059
ORA-15173: entry 'primary' does not exist in directory '/'
Deleted Oracle managed file +STDATADG/primary/onlinelog/group_20.271.953466059
Completed: alter database clear logfile group 20
RFS connections are allowed
Fri Sep 01 05:13:13 2017
RFS[1]: Assigned to RFS process 11610
RFS[1]: Opened log for thread 2 sequence 91 dbid -1912314293 branch 946699467
Archived Log entry 1 added for thread 2 sequence 91 rlc 946699467 ID 0x8e6c5f93 dest 2:
Fri Sep 01 05:13:14 2017
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Assigned to RFS process 11612
RFS[2]: Selected log 18 for thread 2 sequence 93 dbid -1912314293 branch 946699467
Fri Sep 01 05:13:14 2017
RFS[3]: Assigned to RFS process 11614
RFS[3]: Selected log 19 for thread 2 sequence 92 dbid -1912314293 branch 946699467
Fri Sep 01 05:13:14 2017
Archived Log entry 2 added for thread 2 sequence 92 ID 0x8e6c5f93 dest 1:
Fri Sep 01 05:13:22 2017
RFS[4]: Assigned to RFS process 11636
RFS[4]: Opened log for thread 1 sequence 115 dbid -1912314293 branch 946699467
Archived Log entry 3 added for thread 1 sequence 115 rlc 946699467 ID 0x8e6c5f93 dest 2:
Fri Sep 01 05:13:23 2017
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Assigned to RFS process 11638
RFS[5]: Selected log 11 for thread 1 sequence 117 dbid -1912314293 branch 946699467
Fri Sep 01 05:13:23 2017
RFS[6]: Assigned to RFS process 11641
RFS[6]: Selected log 12 for thread 1 sequence 116 dbid -1912314293 branch 946699467
Fri Sep 01 05:13:23 2017
Archived Log entry 4 added for thread 1 sequence 116 ID 0x8e6c5f93 dest 1:
---------------------------------------------------------------------------
3、修改主库的tnsnames.ora
修改【主库1、2节点】tnsnames.ora
su - oracle
vi $ORACLE_HOME/network/admin/tnsnames.ora
修改standby为如下内容：
================================================
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = vip2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mgrdbdg)
)
)
================================================
4、启动MRP进程
在Rman Duplicate操作过程中，【备库】自动切换到mount状态
select instance_name,status from v$instance;
在【备库1节点】上：
开启日志恢复进程
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
#测试应用日志过程，我们手动切换【主库1节点】的日志，并观察日志：
alter system switch logfile;
#备库1节点查询
select member from v$logfile union select name from v$datafile union select name from v$tempfile;
select group#,thread#,sequence#,BYTES/1024/1024,archived,used,status from v$standby_log;
5、创建spfile并使用spfile 启动备库RAC DATABASE
仅在【备库的1节点】执行：
create spfile='+DATA1/mgrdbdg/parameterfile/spfilestandby.ora' from pfile='$ORACLE_HOME/dbs/initstandby.ora';
在【备库的2节点】查看：
su - grid
asmcmd
ASMCMD> ls -l +DATA1/mgrdbdg/parameterfile
#将spfile路径分别添加到备库节点的init.ora里，如下：
在备库1节点上：
vi $ORACLE_HOME/dbs/initmgrdb1.ora
spfile='+DATA1/mgrdbdg/parameterfile/spfilestandby.ora'
在备库2节点上：
vi $ORACLE_HOME/dbs/initmgrdb2.ora
spfile='+DATA1/mgrdbdg/parameterfile/spfilestandby.ora'
6、备库注册到OCR里
添加备库和实例到OCR中，好让clusterware可以管理资源：
仅在【备库1节点上】操作：
su - oracle
srvctl add database -d mgrdbdg -n mgrdb -o $ORACLE_HOME -p +DATA1/mgrdbdg/parameterfile/spfilestandby.ora -r physical_standby -a DATA1
=================================================================================================
srvctl add database -h
Adds a database configuration to the Oracle Clusterware.
Usage: srvctl add database -d <db_unique_name> -o <oracle_home> [-c {RACONENODE | RAC | SINGLE}
[-e <server_list>] [-i <inst_name>] [-w <timeout>]] [-m <domain_name>] [-p <spfile>]
[-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] [-s <start_options>]
[-t <stop_options>] [-n <db_name>] [-y {AUTOMATIC | MANUAL | NORESTART}] [-g "<serverpool_list>"]
[-x <node_name>] [-a "<diskgroup_list>"] [-j "<acfs_path_list>"]
=================================================================================================
#添加实例
su - oracle
srvctl add instance -d mgrdbdg -i mgrdb1 -n mgrdb1
srvctl add instance -d mgrdbdg -i mgrdb2 -n mgrdb2
#查看配置
srvctl config database -d mgrdbdg
7、将【备库1节点】 状态为UNKNOW 状态的监听删掉
在【备库1节点】：
su - grid
cd $ORACLE_HOME/network/admin
cp listener.ora listener.ora.bak
vi listener.ora
lsnrctl reload
lsnrctl status
8、重新启动ADG
在【备库1节点】，重启备库
alter database recover managed standby database cancel;
shut immediate
srvctl stop instance -d mgrdbdg -i mgrdb1
srvctl start database -d mgrdbdg
9、查看备库启动状态：
set line 120
select name,db_unique_name,database_role,open_mode,SWITCHOVER_STATUS from gv$database;
启动MRP
alter database recover managed standby database using current logfile disconnect from session;
10、简单测试
主库1节点：
create tablespace test datafile size 50m;
create user test identified by test default tablespace test;
grant dba to test;
conn test/test
create table t as select * from dba_objects where rownum<=500;
主库2节点：
sqlplus test/test
insert into t select * from t;
commit;
备库：
sqlplus test/test
select count(*) from t;
五、DG传输过程监控
1、查看备库工作模式及状态
select inst_id,db_unique_name,database_role,open_mode,protection_mode,protection_level,switchover_status,force_logging from gv$database;
select db_unique_name,protection_mode,synchronization_status,SYNCHRONIZED from v$archive_dest_status;
2、日志恢复进程
archive log list;
select thread#,max(sequence#) from v$archived_log group by thread#;
select pid,process,client_process,client_pid,thread#,sequence#,status,DELAY_MINS from v$managed_standby;
--RFS进程从主数据库接收重做数据，并将其写入备用重做日志。
3、查看standbylog状态，如果是RAC两节点，那么每个节点至少有一个是ACTIVE的状态，否则不对
set lines 200
select group#,thread#,sequence#,bytes/1024/1024,archived,used,status,first_change#,last_change# from v$standby_log;
检查备库已恢复的最大归档日志序号
select thread#,max(sequence#),registrar,applied,status from v$archived_log where applied='YES' and registrar='RFS' and name is not null
group by thread#,registrar,applied,status;
4、检查应用率和活动率（PS）
--Redo Applied 值以MB衡量。剩余两个以KB/s计算。
select to_char(start_time,'DD-MON-RR HH24:MI:SS') start_time,ITEM,sofar
from v$recovery_progress
where item in ('Active Apply Rate','Average Apply Rate','Redo Applied');
5、审阅传输和应用滞后（PS+LS）
--transport lag 表明从主数据库到物理备用数据库的重做数据传输时间。
--apply lag 表明应用滞后时间，它反映了archive_log_dest_n参数中 DELAY 特性。
COL NAME FOR A13
COL VALUE FOR A20
COL UNIT FOR A30
SET LINES 200
select name,value,unit,time_computed
from v$dataguard_stats
where name like '%lag%';
6、查看Data Guard状态视图中的错误
set lines 132
col message for a80
col timestamp for a20
select error_code,severity,message,
to_char(timestamp,'DD-MON-RR HH24:MI:SS') timestamp
from v$dataguard_status
where callout='YES'
and timestamp > sysdate -1;
7、检查日志文件是否传输到备用数据库
select dest_name,status,error from v$archive_dest where dest_id=2;
DEST_NAME STATUS ERROR
-------------------- ---------- --------------------
LOG_ARCHIVE_DEST_2 VALID
status列如果为valid，说明归档成功，可以查看error列得到不能归档的原因
六、修改DG保护模式
1、查看当前保护模式 主库查询
select inst_id,db_unique_name,database_role,open_mode,protection_mode,protection_level,switchover_status,force_logging from gv$database;
2、将备库转换为最大可用模式
--主库数据库操作 --将最大性能转为最大可用
alter database set standby database to maximize availability;
七、Primary 和 Standby 数据库Switchover切换
1、检查主、备库alert日志是否有报错信息
2、Switchover 到 Physical Standby Database
保证主库只运行一个节点，先关闭2节点实例
su - oracle
sqlplus / as sysdba
shut immediate
srvctl status database -d orcldb
3、看主库是否满足切换条件：
主库操作：
1）
select inst_id,db_unique_name,database_role,open_mode,protection_mode,protection_level,switchover_status,force_logging from gv$database;
select db_unique_name,protection_mode,synchronization_status,SYNCHRONIZED from v$archive_dest_status;
select thread#,max(sequence#) from v$archived_log group by thread#;
select pid,process,client_process,client_pid,thread#,sequence#,status,DELAY_MINS from v$managed_standby;
2）select switchover_status from gv$database;
SWITCHOVER_STATUS
--------------------------
TO STANDBY
有时候会显示SESSION ACTIVE,这表示当前有活动的会话连接。
主库执行下面命令切换：
alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby WITH SESSION SHUTDOWN;
输出日志类似：
========================================
alter database commit to switchover to physical standby
ORA-1031 signalled during: alter database commit to switchover to physical standby...
Thu Aug 31 16:57:56 2017
alter database commit to switchover to physical standby
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 24425] (orcldb1)
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Waiting for all FAL entries to be archived...
All FAL entries have been archived.
Waiting for potential Physical Standby switchover target to become synchronized...
Active, synchronized Physical Standby switchover target has been identified
Switchover End-Of-Redo Log thread 1 sequence 64 has been fixed
Switchover: Primary highest seen SCN set to 0x0.0x105ae0
ARCH: Noswitch archival of thread 1, sequence 64
ARCH: End-Of-Redo Branch archival of thread 1 sequence 64
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Standby redo logfile selected for thread 1 sequence 64 for destination LOG_ARCHIVE_DEST_2
Archived Log entry 70 added for thread 1 sequence 64 ID 0x8e6b5733 dest 1:
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received alls redo
Final check for a synchronized target standby. Check will be made once.
LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
Active, synchronized target has been identified
Target has also received all redo
Backup controlfile written to trace file /oracle/app/oracle/diag/rdbms/standby/orcldb1/trace/orcldb1_ora_24425.trc
Clearing standby activation ID 2389399347 (0x8e6b5733)
The primary database controlfile was created using the
'MAXLOGFILES 192' clause.
There is space for up to 188 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 131072000;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 131072000;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 131072000;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 131072000;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 131072000;
Archivelog for thread 1 sequence 64 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover: Complete - Database shutdown required
USER (ospid: 24425): terminating the instance
Thu Aug 31 16:58:01 2017
ORA-1092 : opitsk aborting process
Instance terminated by USER, pid = 24425
Completed: alter database commit to switchover to physical standby
Shutting down instance (abort)
License high water mark = 8
Thu Aug 31 16:58:01 2017
Instance shutdown complete
如果不关闭2节点，会报如下错误：
Fri Sep 01 03:08:13 2017
Switchover in progress in another database instance - Database is shutdown automatically
LGWR (ospid: 9713): terminating the instance due to error 16456
Instance terminated by LGWR, pid = 9713
Fri Sep 01 03:08:13 2017
========================================
关闭源主库重启到mount
srvctl stop instance -d orcldb -i orcldb1
srvctl start database -d orcldb -o mount
用read only 模式打开源主库：
主库1节点：
alter database open read only;
主库2节点：
alter database open read only;
输出日志类似：
========================================
alter database open read only
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
This instance was first to open
Beginning Standby Crash Recovery.
Serial Media Recovery started
Thu Aug 31 17:00:39 2017
Managed Standby Recovery starting Real Time Apply
Media Recovery Log +STDATADG/standby/archivelog/2017_08_31/thread_1_seq_64.337.953485079
Identified End-Of-Redo (switchover) for thread 1 sequence 64 at SCN 0x0.105ae0
Media Recovery Log +STDATADG/standby/archivelog/2017_08_31/thread_2_seq_34.336.953484769
Media Recovery Log +STDATADG/standby/archivelog/2017_08_31/thread_2_seq_35.335.953484769
Resetting standby activation ID 0 (0x0)
Incomplete Recovery applied until change 1071840 time 08/31/2017 16:57:58
Completed Standby Crash Recovery.
Picked Lamport scheme to generate SCNs
Thu Aug 31 17:00:40 2017
SMON: enabling cache recovery
Dictionary check beginning
Dictionary check complete
Database Characterset is ZHS16GBK
No Resource Manager plan active
Starting background process GTX0
Thu Aug 31 17:00:41 2017
GTX0 started with pid=47, OS id=24790
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: alter database open read only
========================================
源主库启动 Redo 应用模式
alter database recover managed standby database using current logfile disconnect from session;
源主库切换后查看角色
select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY
源备库操作：
查看源备库是否满足切换条件
select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
-------------------------------- ----------------------------------------
PHYSICAL STANDBY TO PRIMARY
源备库执行下面命令进行切换：
alter database commit to switchover to primary;
输出日志类似：
========================================
alter database commit to switchover to primary
ALTER DATABASE SWITCHOVER TO PRIMARY (orcldb1)
Maximum wait for role transition is 15 minutes.
Switchover: Media recovery is still active
Forcing database into limbo
Role Change: Canceling MRP - no more redo to apply
Thu Aug 31 17:04:40 2017
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /oracle/app/oracle/diag/rdbms/primary/orcldb1/trace/orcldb1_mrp0_18559.trc:
ORA-16037: user requested cancel of managed recovery operation
Thu Aug 31 17:04:40 2017
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Thu Aug 31 17:04:41 2017
Reconfiguration started (old inc 5, new inc 7)
List of instances:
1 2 (myinst: 1)
Global Resource Directory frozen
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Thu Aug 31 17:04:41 2017
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Submitted all GCS remote-cache requests
Fix write in gcs resources
Reconfiguration complete
MRP0: Background Media Recovery process shutdown (orcldb1)
Role Change: Canceled MRP
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Thu Aug 31 17:04:42 2017
SMON: disabling cache recovery
Backup controlfile written to trace file /oracle/app/oracle/diag/rdbms/primary/orcldb1/trace/orcldb1_ora_4330.trc
SwitchOver after complete recovery through change 1071840
Online log +DATADG/orcldb/onlinelog/group_1.257.946699471: Thread 1 Group 1 was previously cleared
Online log +DATADG/orcldb/onlinelog/group_2.258.946699473: Thread 1 Group 2 was previously cleared
Online log +DATADG/orcldb/onlinelog/group_3.265.946701385: Thread 2 Group 3 was previously cleared
Online log +DATADG/orcldb/onlinelog/group_4.266.946701385: Thread 2 Group 4 was previously cleared
Standby became primary SCN: 1071838
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
Switchover: Complete - Database mounted as primary
Completed: alter database commit to switchover to primary
========================================
新主库打开数据库：
On Current On Primary Node1:
alter database open;
Current On Primary Node2:
alter database open;
输出日志类似：
========================================
alter database open
This instance was first to open
Picked broadcast on commit scheme to generate SCNs
Thu Aug 31 17:05:24 2017
Assigning activation ID 2389418825 (0x8e6ba349)
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION
Thu Aug 31 17:05:24 2017
ARC0: Becoming the 'no SRL' ARCH
Thread 1 advanced to log sequence 66 (thread open)
Thu Aug 31 17:05:24 2017
ARC1: Becoming the 'no SRL' ARCH
Thread 1 opened at log sequence 66
Current log# 2 seq# 66 mem# 0: +DATADG/orcldb/onlinelog/group_2.258.946699473
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Aug 31 17:05:24 2017
SMON: enabling cache recovery
Thu Aug 31 17:05:24 2017
minact-scn: Inst 1 is now the master inc#:7 mmon proc-id:18275 status:0x7
minact-scn status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000
minact-scn: Master returning as live inst:2 has inc# mismatch instinc:0 cur:7 errcnt:0
[4330] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:40587844 end:40588794 diff:950 (9 seconds)
Dictionary check beginning
ORACLE Instance orcldb1 - Cannot allocate log, archival required
Thread 1 cannot allocate new log, sequence 67
All online logs need archiving
Examine archive trace files for archiving errors
Current log# 2 seq# 66 mem# 0: +DATADG/orcldb/onlinelog/group_2.258.946699473
Archived Log entry 117 added for thread 1 sequence 65 ID 0x8e6ba349 dest 1:
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Thu Aug 31 17:05:25 2017
idle dispatcher 'D000' terminated, pid = (26, 1)
Starting background process SMCO
Thu Aug 31 17:05:26 2017
SMCO started with pid=46, OS id=5558
No Resource Manager plan active
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR: Standby redo logfile selected for thread 1 sequence 67 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 67 (LGWR switch)
Current log# 1 seq# 67 mem# 0: +DATADG/orcldb/onlinelog/group_1.257.946699471
Thu Aug 31 17:05:30 2017
Archived Log entry 119 added for thread 1 sequence 66 ID 0x8e6ba349 dest 1:
Starting background process QMNC
Thu Aug 31 17:05:30 2017
QMNC started with pid=52, OS id=5571
Thu Aug 31 17:05:30 2017
ARC4: Standby redo logfile selected for thread 1 sequence 66 for destination LOG_ARCHIVE_DEST_2
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Thu Aug 31 17:05:34 2017
Completed: alter database open
========================================
切换后查看角色
select name,open_mode from gv$database;
DATABASE_ROLE SWITCHOVER_STATUS
-------------------------------- ----------------------------------------
PRIMARY TO STANDBY
查看数据保护模式
On Current Primary Node1:
select protection_mode,protection_level from v$database;
select dest_id,database_mode,recovery_mode,protection_mode from v$archive_dest_status where dest_id=2;
备注：
Table 5-2 Minimum Requirements for Data Protection Modes
----------------------------------------------------------------------------------------------------------------------------------------------
Maximum Protection Maximum Availability Maximum Performance
Redo archival process LGWR LGWR LGWR or ARCH
Network transmission mode SYNC SYNC SYNC or ASYNC when using LGWR process. SYNC if using ARCH process
Disk write option AFFIRM AFFIRM AFFIRM or NOAFFIRM
Standby redo log required? Yes Yes No, but it is recommended
----------------------------------------------------------------------------------------------------------------------------------------------