ORACLE11Gdataguard安装配置手册
DG的安装与三种保护配置的切换 一、配置初始化环境并恢复到备库 安装前规划： 环境：VBOX虚拟机，操作系统：OracleLinux Server release 5.7 数据库版本：OracleDatabase 11g Enterprise Edition Release 11.2.0.1.0 - dg1做为主库，dg2做为备库。dg1/dg2操
DG的安装与三种保护配置的切换
一、配置初始化环境并恢复到备库
安装前规划：
环境：VBOX虚拟机，操作系统：OracleLinux Server release 5.7
数据库版本：OracleDatabase 11g Enterprise Edition Release 11.2.0.1.0 -
dg1做为主库，dg2做为备库。dg1/dg2操作系统文件目录相同。
dg1:
IP：192.168.1.241
主机名：dg1
ORACLE_SID=dg
ORACLE_BASE=/u01
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
db_unique_name=dg1
dg2:
IP：192.168.1.242
主机名：dg2
ORACLE_SID=dg
ORACLE_BASE=/u01
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
db_unique_name=dg2
数据库软件安装配置：
dg1安装数据库软件，并创建数据库。
dg2安装数据库软件，不创建数据库。
查看主机名及hosts文件：dg1-dg2的HOSTS文件应该相同，保证互相PING主机名可通。
[root@dg1 ~]# cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
192.168.1.241 dg1 dg1.dg.com
192.168.1.242 dg2 dg2.dg.com
[root@dg1~]# cat /etc/sysconfig/network
NETWORKING=yes
NETWORKING_IPV6=no
HOSTNAME=dg1.dg.com
查看用户环境变量：
增加以下内容--与上面的规划对应：
ORACLE_BASE=/u01
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID=dg
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:$HOME/bin
export PATH ORACLE_BASE ORACLE_HOME ORACLE_SID
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
dg1上数据库配置
SQL>select instance_name,status from v$instance;
INSTANCE_NAMESTATUS
----------------------------------
dg OPEN
SQL>select name,db_unique_name,force_logging from v$database;
NAME DB_UNIQUE_NAME FOR
--------------------------------------- ---
DG dg NO
修改DG1初始化参数--部分已经修改，部分需要修改。
最终需要的参数如下：
[oracle@dg1~]$ sqlplus / as sysdba查询参数值：
SQL>select instance_name,status from v$instance;
INSTANCE_NAME STATUS
----------------------------
dg OPEN
SQL>select name,db_unique_name,force_logging from v$database;
NAME DB_UNIQUE_NAME FOR
--------------------------------------- ---
DG dg1 YES
SQL> showparameter log_archive_dest_state_1
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_archive_dest_state_1 string enable
SQL> showparameter log_archive_dest_state_2
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_archive_dest_state_2 string enable
SQL> showparameter remote_login_p
NAME TYPE VALUE
----------------------------------------------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL> showparameter log_archive_dest_1
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_archive_dest_1 string
SQL> showparameter log_archive_dest_2
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_archive_dest_2 string
SQL> showparameter log_archive_confi
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_archive_config string
SQL> showparameter standby_file_management
NAME TYPE VALUE
----------------------------------------------- ------------------------------
standby_file_management string MANUAL
需要进行修改参数值：
SQL>alter database force logging;
Databasealtered.
SQL>alter system set log_archive_config="DG_CONFIG=(dg1,dg2)"scope=spfile;
Systemaltered.
SQL>alter system set log_archive_dest_1="LOCATION=/u01/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1" scope=spfile;
Systemaltered.
SQL>alter system set log_archive_dest_2="SERVICE=dg2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg2" scope=spfile;
Systemaltered.
SQL>alter system set fal_server=dg2 scope=spfile;
System altered.
SQL>alter system set fal_client=dg1 scope=spfile;
Systemaltered.
SQL>alter system set standby_file_management=auto scope=spfile;
Systemaltered.
SQL>alter system set db_file_name_convert="/u01/oradata/dg","/u01/oradata/dg" scope=spfile;
Systemaltered.
SQL>alter system set log_file_name_convert="/u01/oradata/dg","/u01/oradata/dg" scope=spfile;
Systemaltered.
创建增加standby日志-
这一步是配置为最大保护和最大可用模式做准备。
SQL>select a.member,b.bytes/1024/1024 MB from v$logfile a,v$log b wherea.group#=b.group#;
MEMBER MB
----------------------------------------
/u01/oradata/dg/redo03.log 50
/u01/oradata/dg/redo02.log 50
/u01/oradata/dg/redo01.log 50
SQL>alter database add standby logfile group 4 '/u01/oradata/dg/standbyredo04.log'size 50M;
Databasealtered.
SQL>alter database add standby logfile group 5 '/u01/oradata/dg/standbyredo05.log'size 50M;
Databasealtered.
SQL>alter database add standby logfile group 6 '/u01/oradata/dg/standbyredo06.log'size 50M;
Databasealtered.
SQL>alter database add standby logfile group 7 '/u01/oradata/dg/standbyredo07.log'size 50M;
Databasealtered.
SQL>SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARC STATUS
-------------------- ---------- --- ----------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED
7 0 0 YES UNASSIGNED
SQL>shutdown immediate;
Databaseclosed.
Databasedismounted.
ORACLEinstance shut down.
SQL>startup;
ORACLEinstance started.
Total SystemGlobal Area 418484224 bytes
FixedSize 1336932 bytes
VariableSize 281020828 bytes
DatabaseBuffers 130023424 bytes
RedoBuffers 6103040 bytes
Databasemounted.
Databaseopened.
#################################################
创建pfile以及standby控制文件。
然后关机做备份，准备复制文件到dg2--standby
SQL>create pfile from spfile;
Filecreated.
[oracle@dg1dbs]$ ls -al initdg.ora
-rw-r--r-- 1oracle oinstall 1322 Jul 27 12:47 initdg.ora
SQL>alter database create standby controlfile as '/u01/oradata/dg/standctl01.ctl';
Databasealtered.
在$ORACLE_HOME/dbs/ 及 $ORACLE_HOME/network/admin/目录下传送相应文件到dg2
密码文件的生成—如已经有密码文件直接传，没有则新建。
orapwdfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdg1 password=oraclesysentries=3 force=y
scp initdg.ora dg2:$ORACLE_HOME/dbs/
scp orapwdgdg2:$ORACLE_HOME/dbs/
scp listener.ora tnsnames.ora dg2:$ORACLE_HOME/network/admin/
在备库上修改init初始化参数，注意标红的参数。结果如下：--其实需要修改的也就是 db_unique_name了，也可以打开数据库后再修改。
[oracle@dg2 dbs]$ cat initdg.ora
dg.__db_cache_size=130023424
dg.__java_pool_size=4194304
dg.__large_pool_size=4194304
dg.__oracle_base='/u01'#ORACLE_BASE set from environment
dg.__pga_aggregate_target=155189248
dg.__sga_target=264241152
dg.__shared_io_pool_size=0
dg.__shared_pool_size=113246208
dg.__streams_pool_size=4194304
*.audit_file_dest='/u01/admin/dg/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/oradata/dg/control01.ctl','/u01/flash_recovery_area/dg/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/oradata/dg','/u01/oradata/dg'
*.db_name='dg'
*.db_recovery_file_dest='/u01/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.db_unique_name='DG2'
*.diagnostic_dest='/u01'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgXDB)'
*.fal_client='DG2'
*.fal_server='DG1'
*.log_archive_config='DG_CONFIG=(dg1,dg2)'
*.log_archive_dest_1='LOCATION=/u01/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg2'
*.log_archive_dest_2='SERVICE=dg2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg1'
*.log_archive_format='arc_%t_%s_%r.arc'
*.log_file_name_convert='/u01/oradata/dg','/u01/oradata/dg'
*.memory_target=419430400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
在dg1 和dg2上配置监听
dg1上的配置：listener.ora tnsnames.ora
listener.ora可以使用NETCA图形界面配置生成，也可以手动创建。
[oracle@dg1admin]$ cat listener.ora
#listener.ora Network Configuration File:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generatedby Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
)
)
ADR_BASE_LISTENER= /u01
需要增加dg1 dg2的解析信息
[oracle@dg1admin]$ cat tnsnames.ora
dg1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =dg1.dg.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg1)
)
)
dg2=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =dg2.dg.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg2)
)
)
EXTPROC_CONNECTION_DATA=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
从监听的红色部分能看到监听已启动
用到命令有： lsnrctl start/stop/status/reload
[oracle@dg1admin]$ lsnrctl status
LSNRCTL forLinux: Version 11.2.0.1.0 - Production on 27-JUL-2013 13:34:05
Copyright(c) 1991, 2009, Oracle. All rightsreserved.
Connectingto (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1)(PORT=1521)))
STATUS ofthe LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version11.2.0.1.0 - Production
StartDate 27-JUL-2013 11:27:27
Uptime 0 days 2 hr. 6 min. 38 sec
TraceLevel off
Security ON: Local OS Authentication
SNMP OFF
ListenerParameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener LogFile /u01/diag/tnslsnr/dg1/listener/alert/log.xml
ListeningEndpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1)(PORT=1521)))
ServicesSummary...
Service"DG1" has 1 instance(s).
Instance "dg", status READY, has 1handler(s) for this service...
Service"dgXDB" has 1 instance(s).
Instance "dg", status READY, has 1handler(s) for this service...
The command completedsuccessfully
dg2上的配置 其中tnsnames.ora与dg1上完全相同，不贴出了。
Dg2的listener.ora需要配置静态注册。
[oracle@dg2admin]$ cat listener.ora
#listener.ora Network Configuration File:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generatedby Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dg2)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = dg)
)
)
ADR_BASE_LISTENER= /u01
[oracle@dg2admin]$ lsnrctl status
LSNRCTL forLinux: Version 11.2.0.1.0 - Production on 27-JUL-2013 21:42:14
Copyright(c) 1991, 2009, Oracle. All rightsreserved.
Connectingto (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg2)(PORT=1521)))
STATUS ofthe LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version11.2.0.1.0 - Production
StartDate 27-JUL-2013 21:39:19
Uptime 0 days 0 hr. 2 min. 55 sec
TraceLevel off
Security ON: Local OS Authentication
SNMP OFF
ListenerParameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener LogFile /u01/diag/tnslsnr/dg2/listener/alert/log.xml
ListeningEndpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2)(PORT=1521)))
ServicesSummary...
Service"dg2" has 1 instance(s).
Instance "dg", status UNKNOWN, has1 handler(s) for this service...
Thecommand completed successfully
#################################################
dg1和dg2互相登陆测试
从dg1登陆dg2
[oracle@dg1admin]$ sqlplus sys/oraclesys@dg2 as sysdba
SQL*Plus:Release 11.2.0.1.0 Production on Sat Jul 27 13:43:33 2013
Copyright(c) 1982, 2009, Oracle. All rightsreserved.
Connected toan idle instance.
SQL>
在dg2主机上测试能否登陆dg1的数据库--tnsnames.ora已经与dg1的相同。
[oracle@dg2admin]$ sqlplus sys/oraclesys@dg1 as sysdba
Connectedto:
OracleDatabase 11g Enterprise Edition Release 11.2.0.1.0 - Production
With thePartitioning, OLAP, Data Mining and Real Application Testing options
SQL> colhost_name for a10
SQL>select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
--------------------------
dg dg1.dg.com
虚拟机可以关机做下备份。
使用RMAN duplicate创建STANDBY数据库
使用RMAN时连接本地连也要用用户名密码方式。在 dg1上登陆RMAN进行以下操作：
[oracle@dg1~]$ rman target sys/oraclesys auxiliary sys/oraclesys@dg2
RecoveryManager: Release 11.2.0.1.0 - Production on Sat Jul 27 14:02:41 2013
Copyright(c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connectedto target database: DG (DBID=1735160627)
connectedto auxiliary database: DG (not mounted)
duplicate target database for standby nofilenamecheck from active database dorecover;
输出日志：
StartingDuplicate Db at 27-JUL-13
using targetdatabase control file instead of recovery catalog
allocatedchannel: ORA_AUX_DISK_1
channelORA_AUX_DISK_1: SID=19 device type=DISK
contents ofMemory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdg' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdg' ;
}
executingMemory Script
Startingbackup at 27-JUL-13
allocatedchannel: ORA_DISK_1
channelORA_DISK_1: SID=41 device type=DISK
Finishedbackup at 27-JUL-13
contents ofMemory Script:
{
backup as copy current controlfile forstandby auxiliary format '/u01/oradata/dg/control01.ctl';
restore clone controlfile to '/u01/flash_recovery_area/dg/control02.ctl'from
'/u01/oradata/dg/control01.ctl';
}
executingMemory Script
Startingbackup at 27-JUL-13
usingchannel ORA_DISK_1
channelORA_DISK_1: starting datafile copy
copyingstandby control file
output filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_dg.ftag=TAG20130727T140349 RECID=3 STAMP=821887430
channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:06
Finishedbackup at 27-JUL-13
Startingrestore at 27-JUL-13
usingchannel ORA_AUX_DISK_1
channelORA_AUX_DISK_1: copied control file copy
Finishedrestore at 27-JUL-13
contents ofMemory Script:
{
sql clone 'alter database mount standbydatabase';
}
executingMemory Script
sql statement:alter database mount standby database
contents ofMemory Script:
{
set newname for tempfile 1 to
"/u01/oradata/dg/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/oradata/dg/system01.dbf";
set newname for datafile 2 to
"/u01/oradata/dg/sysaux01.dbf";
set newname for datafile 3 to
"/u01/oradata/dg/undotbs01.dbf";
set newname for datafile 4 to
"/u01/oradata/dg/users01.dbf";
set newname for datafile 5 to
"/u01/oradata/dg/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/oradata/dg/system01.dbf" datafile
2 auxiliary format
"/u01/oradata/dg/sysaux01.dbf" datafile
3 auxiliary format
"/u01/oradata/dg/undotbs01.dbf" datafile
4 auxiliary format
"/u01/oradata/dg/users01.dbf" datafile
5 auxiliary format
"/u01/oradata/dg/example01.dbf" ;
sql 'alter system archive log current';
}
executingMemory Script
executingcommand: SET NEWNAME
renamedtempfile 1 to /u01/oradata/dg/temp01.dbf in control file
executingcommand: SET NEWNAME
executingcommand: SET NEWNAME
executingcommand: SET NEWNAME
executingcommand: SET NEWNAME
executingcommand: SET NEWNAME
Startingbackup at 27-JUL-13
usingchannel ORA_DISK_1
channelORA_DISK_1: starting datafile copy
inputdatafile file number=00001 name=/u01/oradata/dg/system01.dbf
output filename=/u01/oradata/dg/system01.dbf tag=TAG20130727T140405
channelORA_DISK_1: datafile copy complete, elapsed time: 00:02:35
channelORA_DISK_1: starting datafile copy
inputdatafile file number=00002 name=/u01/oradata/dg/sysaux01.dbf
output filename=/u01/oradata/dg/sysaux01.dbf tag=TAG20130727T140405
channelORA_DISK_1: datafile copy complete, elapsed time: 00:02:15
channelORA_DISK_1: starting datafile copy
inputdatafile file number=00003 name=/u01/oradata/dg/undotbs01.dbf
output filename=/u01/oradata/dg/undotbs01.dbf tag=TAG20130727T140405
channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channelORA_DISK_1: starting datafile copy
inputdatafile file number=00005 name=/u01/oradata/dg/example01.dbf
output filename=/u01/oradata/dg/example01.dbf tag=TAG20130727T140405
channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channelORA_DISK_1: starting datafile copy
inputdatafile file number=00004 name=/u01/oradata/dg/users01.dbf
output filename=/u01/oradata/dg/users01.dbf tag=TAG20130727T140405
channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finishedbackup at 27-JUL-13
sqlstatement: alter system archive log current
contents ofMemory Script:
{
backup as copy reuse
archivelog like "/u01/archivelog/arc_1_15_821829622.arc" auxiliary format
"/u01/archivelog/arc_1_15_821829622.arc" archivelog like
"/u01/archivelog/arc_1_16_821829622.arc"auxiliary format
"/u01/archivelog/arc_1_16_821829622.arc" ;
catalog clone archivelog "/u01/archivelog/arc_1_15_821829622.arc";
catalog clone archivelog "/u01/archivelog/arc_1_16_821829622.arc";
switch clone datafile all;
}
executingMemory Script
Startingbackup at 27-JUL-13
usingchannel ORA_DISK_1
channelORA_DISK_1: starting archived log copy
inputarchived log thread=1 sequence=15 RECID=10 STAMP=821887447
output filename=/u01/archivelog/arc_1_15_821829622.arc RECID=0 STAMP=0
channelORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channelORA_DISK_1: starting archived log copy
inputarchived log thread=1 sequence=16 RECID=11 STAMP=821887800
output filename=/u01/archivelog/arc_1_16_821829622.arc RECID=0 STAMP=0
channelORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finishedbackup at 27-JUL-13
catalogedarchived log
archived logfile name=/u01/archivelog/arc_1_15_821829622.arc RECID=1 STAMP=821916602
catalogedarchived log
archived logfile name=/u01/archivelog/arc_1_16_821829622.arc RECID=2 STAMP=821916602
datafile 1switched to datafile copy
inputdatafile copy RECID=3 STAMP=821916603 file name=/u01/oradata/dg/system01.dbf
datafile 2switched to datafile copy
inputdatafile copy RECID=4 STAMP=821916603 file name=/u01/oradata/dg/sysaux01.dbf
datafile 3switched to datafile copy
inputdatafile copy RECID=5 STAMP=821916603 file name=/u01/oradata/dg/undotbs01.dbf
datafile 4switched to datafile copy
inputdatafile copy RECID=6 STAMP=821916603 file name=/u01/oradata/dg/users01.dbf
datafile 5switched to datafile copy
inputdatafile copy RECID=7 STAMP=821916603 file name=/u01/oradata/dg/example01.dbf
contents ofMemory Script:
{
set until scn 897263;
recover
standby
clone database
delete archivelog
;
}
executingMemory Script
executingcommand: SET until clause
Startingrecover at 27-JUL-13
usingchannel ORA_AUX_DISK_1
startingmedia recovery
archived logfor thread 1 with sequence 15 is already on disk as file/u01/archivelog/arc_1_15_821829622.arc
archived logfor thread 1 with sequence 16 is already on disk as file/u01/archivelog/arc_1_16_821829622.arc
archived logfile name=/u01/archivelog/arc_1_15_821829622.arc thread=1 sequence=15
archived logfile name=/u01/archivelog/arc_1_16_821829622.arc thread=1 sequence=16
mediarecovery complete, elapsed time: 00:00:03
Finishedrecover at 27-JUL-13
FinishedDuplicate Db at 27-JUL-13
打开REDO应用：
1.RMAN恢复备库成功后，登陆dg2，此时dg2处于MOUNT状态,并启动redo应用。
[oracle@dg2~]$ sqlplus / as sysdba
Connectedto:
OracleDatabase 11g Enterprise Edition Release 11.2.0.1.0 - Production
With thePartitioning, OLAP, Data Mining and Real Application Testing options
SQL>select status,instance_name from v$instance;
STATUS INSTANCE_NAME
----------------------------
MOUNTED dg
SQL>select name,db_unique_name from v$database;
NAME DB_UNIQUE_NAME
---------------------------------------
DG DG2
SQL>alter database recover managed standby database disconnect from session;
Databasealtered.
#########################################
二、DG三种保护模式切换实践
最大性能模式-安装完DG时缺省是此模式。切换语句是：alter database set standby database to maximize PERFORMANCE;
[oracle@dg1~]$ sqlplus / as sysdba
SQL> settime on
14:29:04SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
18
14:29:12SQL> alter system switch logfile;
Systemaltered.
14:29:39SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
19
dg2上进行查看
[oracle@dg2~]$ sqlplus / as sysdba
SQL>alter database recover managed standby database disconnect from session;
Database altered.
14:29:58SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
19
一次切换当前REDO的ALERT日志
dg1--REDO切换到了LOG#1的--/u01/oradata/dg/redo01.log，
Sat Jul 2717:06:58 2013
Thread 1advanced to log sequence 25 (LGWR switch)
Current log# 1 seq# 25 mem# 0:/u01/oradata/dg/redo01.log
Sat Jul 2717:06:58 2013
LNS: Standbyredo logfile selected for thread 1 sequence 25 for destinationLOG_ARCHIVE_DEST_2
Sat Jul 2717:06:59 2013
Archived Logentry 27 added for thread 1 sequence 24 ID 0x676c9833 dest 1:
#######################
dg2上
Sat Jul 2717:07:25 2013
RFS[4]:Selected log 5 for thread 1 sequence 25 dbid 1735160627 branch 821829622
Sat Jul 2717:07:25 2013
Archived Logentry 10 added for thread 1 sequence 24 ID 0x676c9833 dest 1:
最大可用模式
说明：切换保护模式的操作必须在primay执行，且primay必须处于mount状态，如果在open状态执行，则报ORA-01126错。
ORA-01126:database must be mounted EXCLUSIVE and not open for this operation。
dg1的操作：
[oracle@dg1~]$ sqlplus / as sysdba
SQL>select protection_mode,database_role,protection_level from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL
------------------------------------ --------------------
MAXIMUMPERFORMANCE PRIMARY MAXIMUM PERFORMANCE
SQL> alter system set log_archive_dest_2="SERVICE=dg2 LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg2";
Systemaltered.
SQL>shutdown immediate;
Databaseclosed.
Databasedismounted.
ORACLEinstance shut down.
SQL>startup mount;
ORACLEinstance started.
Total SystemGlobal Area 418484224 bytes
FixedSize 1336932 bytes
VariableSize 348129692 bytes
DatabaseBuffers 62914560 bytes
RedoBuffers 6103040 bytes
Databasemounted.
SQL> alter database set standby database to maximize availability;
Databasealtered.
SQL>alter database open;
Databasealtered.
SQL>select protection_mode,database_role,protection_level from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL
------------------------------------ --------------------
MAXIMUMAVAILABILITY PRIMARY MAXIMUMAVAILABILITY
###############################################
dg2 的操作：
[oracle@dg2~]$ sqlplus / as sysdba
SQL>select protection_mode,database_role,protection_level from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL
------------------------------------ --------------------
MAXIMUMPERFORMANCE PHYSICAL STANDBY MAXIMUMPERFORMANCE
下面设置log_archive_dest_2是为了SWITCHOVER用，可以不用做。
SQL> showparameter log_archive_dest_2
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_archive_dest_2 string SERVICE=dg1 ARCH ASYNC VALID_
FOR=(ONLINE_LOGFILES,PRIMARY_R
OLE) DB_UNIQUE_NAME=dg1
SQL>alter system set log_archive_dest_2="SERVICE=dg1 LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=dg1";
Systemaltered.
节点1在mount时切换为最大可用性后再查看，节点2也已经改变。
SQL>select protection_mode,database_role,protection_level from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL
------------------------------------ --------------------
MAXIMUMAVAILABILITY PHYSICAL STANDBY MAXIMUM AVAILABILITY
SQL>alter database recover managed standby database disconnect from session;
Database altered.
SQL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
33
对应的DG1日志：
LGWR: Primarydatabase is in MAXIMUM AVAILABILITY mode
LGWR:Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
DestinationLOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
******************************************************************
LGWR:Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************…………………………………………
ARC0:STARTING ARCH PROCESSES COMPLETE
DestinationLOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR:Standby redo logfile selected to archive thread 1 sequence 34
LGWR:Standby redo logfile selected for thread 1 sequence 34 for destinationLOG_ARCHIVE_DEST_2
Shuttingdown archive processes
Thread 1advanced to log sequence 34 (LGWR switch)
Current log# 1 seq# 34 mem# 0:/u01/oradata/dg/redo01.log
ARCHshutting down
ARC4:Archival stopped
Archived Logentry 45 added for thread 1 sequence 33 ID 0x676c9833 dest 1:
Sat Jul 2720:52:26 2013
Startingbackground process CJQ0
Sat Jul 2720:52:26 2013
CJQ0 startedwith pid=26, OS id=6197
SettingResource Manager plan SCHEDULER[0x3008]:DEFAULT_MAINTENANCE_PLAN via schedulerwindow
SettingResource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sat Jul 2720:52:29 2013
Startingbackground process VKRM
Sat Jul 2720:52:29 2013
VKRM startedwith pid=27, OS id=6201
Sat Jul 2720:57:23 2013
Startingbackground process SMCO
Sat Jul 2720:57:24 2013
SMCO startedwith pid=28, OS id=6261
######################################################
节点2日志
Sat Jul 2720:49:32 2013
ALTER SYSTEMSET log_archive_dest_2='SERVICE=dg1 LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg1'SCOPE=MEMORY;
Sat Jul 2720:49:52 2013
RFS[13]:Assigned to RFS process 4488
RFS[13]:Identified database type as 'physical standby': Client is LGWR SYNC pid 2845
Primarydatabase is in MAXIMUM PERFORMANCE mode
RFS[13]:Selected log 4 for thread 1 sequence 31 dbid 1735160627 branch 821829622
Sat Jul 2720:49:52 2013
RFS[14]:Assigned to RFS process 4492
RFS[14]:Identified database type as 'physical standby': Client is ARCH pid 2929
RFS[14]:Selected log 5 for thread 1 sequence 30 dbid 1735160627 branch 821829622
Sat Jul 2720:49:52 2013
Archived Logentry 16 added for thread 1 sequence 30 ID 0x676c9833 dest 1:
Sat Jul 2720:49:52 2013
RFS[15]:Assigned to RFS process 4496
RFS[15]:Identified database type as 'physical standby': Client is ARCH pid 2925
Sat Jul 2720:50:02 2013
Archived Logentry 17 added for thread 1 sequence 31 ID 0x676c9833 dest 1:
RFS[13]:Possible network disconnect with primary database
Sat Jul 2720:52:22 2013
RFS[16]:Assigned to RFS process 4537
RFS[16]:Identified database type as 'physical standby': Client is LGWR SYNC pid 6084 ----这里可以看到dg2通过同步时，发现主库是在最大可用模式下，就更改了自己的备库STANDBY 控制文件为最大可用模式。最终达到备库STANDBY 控制文件与主库一致
Primarydatabase is in MAXIMUM AVAILABILITY mode
Changingstandby controlfile to MAXIMUM AVAILABILITY mode
Changingstandby controlfile to RESYNCHRONIZATION level
Standbycontrolfile consistent with primary
RFS[16]:Selected log 4 for thread 1 sequence 33 dbid 1735160627 branch 821829622
Sat Jul 2720:52:22 2013
RFS[17]:Assigned to RFS process 4541
RFS[17]: Identifieddatabase type as 'physical standby': Client is ARCH pid 6157
RFS[17]:Selected log 5 for thread 1 sequence 32 dbid 1735160627 branch 821829622
Sat Jul 2720:52:22 2013
Archived Logentry 18 added for thread 1 sequence 32 ID 0x676c9833 dest 1:
Sat Jul 2720:52:25 2013
Archived Logentry 19 added for thread 1 sequence 33 ID 0x676c9833 dest 1:
Changingstandby controlfile to MAXIMUM AVAILABILITY level
RFS[16]:Selected log 4 for thread 1 sequence 34 dbid 1735160627 branch 821829622
最大保护模式--此种模式在11G下，如备库DOWN掉，主库会HANG住，而不是重启。
dg1上的配置，此时是最大可用
[oracle@dg1~]$ sqlplus / as sysdba
SQL>select protection_mode,database_role,protection_level from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL
------------------------------------ --------------------
MAXIMUMAVAILABILITY PRIMARY MAXIMUMAVAILABILITY
SQL> showparameter log_archive_dest_2
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_archive_dest_2 string SERVICE=dg2 LGWR SYNC VALID_F
OR=(ONLINE_LOGFILES,PRIMARY_RO
LE) DB_UNIQUE_NAME=dg2
SQL>alter system set log_archive_dest_2="SERVICE=dg2 SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg2";
Systemaltered.
SQL>shutdown immediate;
SQL>startup mount;
更改保护模式为最大保护
SQL>alter database set standby database to maximize protection;
Databasealtered.
SQL>alter database open;
Databasealtered.
SQL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
35
SQL>select protection_mode,database_role,protection_level from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL
------------------------------------ --------------------
MAXIMUMPROTECTION PRIMARY MAXIMUM PROTECTION
#######################################################
dg2上的配置
[oracle@dg2~]$ sqlplus / as sysdba
SQL>select protection_mode,database_role,protection_level from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL
------------------------------------ --------------------
MAXIMUMAVAILABILITY PHYSICAL STANDBY MAXIMUM AVAILABILITY
SQL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
33
SQL> showparameter log_archive_dest_2;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_archive_dest_2 string SERVICE=dg1 LGWR SYNC VALID_F
OR=(ONLINE_LOGFILES,PRIMARY_RO
LE) DB_UNIQUE_NAME=dg1
SQL>alter system set log_archive_dest_2="SERVICE=dg1 SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg1";
Systemaltered.
dg1上重新打开数据库后在dg2查看信息如下：
SQL>alter database recover managed standby database disconnect from session;
Database altered.
SQL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
35
SQL>select protection_mode,database_role,protection_level from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL
------------------------------------ --------------------
MAXIMUMPROTECTION PHYSICAL STANDBY MAXIMUMPROTECTION
#######################################################
对应的ALERT日志：
dg1上的日志：
[oracle@dg1~]$ cat alert_dg.log
Sat Jul 2721:12:16 2013
alterdatabase set standby database to maximize protection
Completed:alter database set standby database to maximize protection
alterdatabase open
Sat Jul 2721:12:21 2013
LGWR:STARTING ARCH PROCESSES
Sat Jul 2721:12:22 2013
ARC0 startedwith pid=21, OS id=6568
ARC0:Archival started
LGWR:STARTING ARCH PROCESSES COMPLETE
ARC0:STARTING ARCH PROCESSES
LGWR:Primary database is in MAXIMUM PROTECTION mode
LGWR:Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
Sat Jul 2721:12:23 2013
ARC1 startedwith pid=22, OS id=6572
Sat Jul 2721:12:23 2013
ARC2 startedwith pid=20, OS id=6576
ARC1:Archival started
ARC2:Archival started
ARC1:Becoming the 'no FAL' ARCH
ARC1:Becoming the 'no SRL' ARCH
ARC2:Becoming the heartbeat ARCH
Sat Jul 2721:12:23 2013
ARC3 startedwith pid=23, OS id=6580
Sat Jul 2721:12:24 2013
NSS2 startedwith pid=24, OS id=6584
ARC3:Archival started
ARC0:STARTING ARCH PROCESSES COMPLETE
******************************************************************
LGWR:Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR:Standby redo logfile selected to archive thread 1 sequence 36
LGWR:Standby redo logfile selected for thread 1 sequence 36 for destinationLOG_ARCHIVE_DEST_2
Thread 1advanced to log sequence 36 (thread open)
ARC0: LGWRis actively archiving destination LOG_ARCHIVE_DEST_2
LGWR:Waiting for ORLs to be archived...
ARC0:Standby redo logfile selected for thread 1 sequence 35 for destinationLOG_ARCHIVE_DEST_2
Archived Logentry 48 added for thread 1 sequence 35 ID 0x676c9833 dest 1:
LGWR: ORLssuccessfully archived
Thread 1opened at log sequence 36
Current log# 3 seq# 36 mem# 0: /u01/oradata/dg/redo03.log
Successfulopen of redo thread 1
Sat Jul 2721:12:28 2013
MTTRadvisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Jul 2721:12:28 2013
SMON:enabling cache recovery
Successfullyonlined Undo Tablespace 2.
Verifyingfile header compatibility for 11g tablespace encryption..
Verifying11g file header compatibility for tablespace encryption completed
SMON:enabling tx recovery
DatabaseCharacterset is AL32UTF8
No ResourceManager plan active
replication_dependency_trackingturned off (no async multimaster replication found)
Startingbackground process QMNC
Sat Jul 2721:12:29 2013
QMNC startedwith pid=25, OS id=6588
Completed:alter database open
Sat Jul 2721:12:30 2013
db_recovery_file_dest_sizeof 3852 MB is 2.33% used. This is a
user-specifiedlimit on the amount of space that will be used by this
database forrecovery-related files, and does not reflect the amount of
spaceavailable in the underlying filesystem or ASM diskgroup.
Sat Jul 2721:12:31 2013
Startingbackground process CJQ0
Sat Jul 2721:12:31 2013
CJQ0 startedwith pid=28, OS id=6616
SettingResource Manager plan SCHEDULER[0x3008]:DEFAULT_MAINTENANCE_PLAN via schedulerwindow
SettingResource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sat Jul 2721:12:34 2013
Startingbackground process VKRM
Sat Jul 2721:12:34 2013
VKRM startedwith pid=26, OS id=6620
Sat Jul 2721:13:23 2013
ARC2:STARTING ARCH PROCESSES
Sat Jul 2721:13:23 2013
ARC4 startedwith pid=30, OS id=6646
ARC4:Archival started
ARC2:STARTING ARCH PROCESSES COMPLETE
Sat Jul 2721:14:24 2013
Shuttingdown archive processes
Sat Jul 2721:14:24 2013
ARCHshutting down
ARC4:Archival stopped
#######################################################
dg2上的日志：
[oracle@dg2~]$ cat alert_dg.log
Sat Jul 2721:07:54 2013
ALTER SYSTEMSET log_archive_dest_2='SERVICE=dg1 SYNCAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg1'SCOPE=MEMORY;
Sat Jul 2721:08:18 2013
Archived Logentry 20 added for thread 1 sequence 34 ID 0x676c9833 dest 1:
Sat Jul 2721:12:27 2013
RFS[18]:Assigned to RFS process 4639
RFS[18]:Identified database type as 'physical standby': Client is LGWR SYNC pid 6498
Primarydatabase is in MAXIMUM PROTECTION mode
Changingstandby controlfile to MAXIMUM PROTECTION mode
Sat Jul 2721:12:28 2013
RFS[19]:Assigned to RFS process 4643
RFS[19]:Identified database type as 'physical standby': Client is LGWR SYNC pid 6498
Primarydatabase is in MAXIMUM PROTECTION mode
Standbycontrolfile consistent with primary
Standbycontrolfile consistent with primary
RFS[19]:Selected log 4 for thread 1 sequence 36 dbid 1735160627 branch 821829622
Sat Jul 2721:12:28 2013
RFS[20]:Assigned to RFS process 4647
RFS[20]:Identified database type as 'physical standby': Client is ARCH pid 6568
RFS[20]:Selected log 5 for thread 1 sequence 35 dbid 1735160627 branch 821829622
Sat Jul 2721:12:28 2013
Archived Logentry 21 added for thread 1 sequence 35 ID 0x676c9833 dest 1:
Sat Jul 2721:12:28 2013
RFS[21]:Assigned to RFS process 4651
RFS[21]:Identified database type as 'physical standby': Client is ARCH pid 6576