DG的安装与三种保护配置的切换 一、配置初始化环境并恢复到备库 安装前规划： 环境：VBOX虚拟机，操作系统：OracleLinux Server release 5.7 数据库版本：OracleDatabase 11g Enterprise Edition Release 11.2.0.1.0 - dg1做为主库，dg2做为备库。dg1/dg2操
DG的安装与三种保护配置的切换
一、配置初始化环境并恢复到备库
安装前规划：
数据库版本：OracleDatabase 11g Enterprise Edition Release 11.2.0.1.0 -
dg1做为主库，dg2做为备库。dg1/dg2操作系统文件目录相同。
dg1:
主机名：dg1
ORACLE_SID=dg
ORACLE_BASE=/u01
db_unique_name=dg1
dg2:
ORACLE_SID=dg
ORACLE_BASE=/u01
数据库软件安装配置：
dg1安装数据库软件，并创建数据库。
dg2安装数据库软件，不创建数据库。
查看主机名及hosts文件：dg1-dg2的HOSTS文件应该相同，保证互相PING主机名可通。
[root@dg1 ~]# cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.1.241 dg1 dg1.dg测试数据
192.168.1.242 dg2 dg2.dg测试数据
[root@dg1~]# cat /etc/sysconfig/network
NETWORKING=yes
NETWORKING_IPV6=no
HOSTNAME=dg1.dg测试数据
查看用户环境变量：
增加以下内容--与上面的规划对应：
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/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.
密码文件的生成—如已经有密码文件直接传，没有则新建。
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'
*测试数据patible='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))
)
)
需要增加dg1 dg2 的解析信息
[oracle@dg1admin]$ cat tnsnames.ora
dg1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =dg1.dg测试数据)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg1)
)
)
dg2=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =dg2.dg测试数据)(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
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)
)
)
[oracle@dg2admin]$ lsnrctl status
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
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互相登陆测试
[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>select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
--------------------------
dg dg1.dg测试数据
虚拟机可以关机做下备份。
使用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;
输出日志：
using targetdatabase control file instead of recovery catalog
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
channelORA_DISK_1: SID=41 device type=DISK
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
channelORA_DISK_1: starting datafile copy
copyingstandby control file
Finishedbackup at 27-JUL-13
usingchannel ORA_AUX_DISK_1
channelORA_AUX_DISK_1: copied control file copy
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
channelORA_DISK_1: starting datafile copy
inputdatafile file number=00001 name=/u01/oradata/dg/system01.dbf
channelORA_DISK_1: starting datafile copy
inputdatafile file number=00002 name=/u01/oradata/dg/sysaux01.dbf
channelORA_DISK_1: starting datafile copy
inputdatafile file number=00003 name=/u01/oradata/dg/undotbs01.dbf
channelORA_DISK_1: starting datafile copy
inputdatafile file number=00005 name=/u01/oradata/dg/example01.dbf
channelORA_DISK_1: starting datafile copy
inputdatafile file number=00004 name=/u01/oradata/dg/users01.dbf
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
channelORA_DISK_1: starting archived log copy
channelORA_DISK_1: starting archived log copy
catalogedarchived log
archived logfile name=/u01/archivelog/arc_1_15_821829622.arc RECID=1 STAMP=821916602
catalogedarchived log
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
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
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
---------------------------------------
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，
Thread 1advanced to log sequence 25 (LGWR switch)
Current log# 1 seq# 25 mem# 0:/u01/oradata/dg/redo01.log
Archived Logentry 27 added for thread 1 sequence 24 ID 0x676c9833 dest 1:
#######################
dg2上
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
******************************************************************
******************************************************************…………………………………………
ARC0:STARTING ARCH PROCESSES COMPLETE
DestinationLOG_ARCHIVE_DEST_2 is SYNCHRONIZED
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:
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
Startingbackground process VKRM
VKRM startedwith pid=27, OS id=6201
Startingbackground process SMCO
SMCO startedwith pid=28, OS id=6261
######################################################
节点 2 日志
ALTER SYSTEMSET log_archive_dest_2='SERVICE=dg1 LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg1'SCOPE=MEMORY;
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
Archived Logentry 16 added for thread 1 sequence 30 ID 0x676c9833 dest 1:
Archived Logentry 17 added for thread 1 sequence 31 ID 0x676c9833 dest 1:
RFS[13]:Possible network disconnect with primary database
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
Archived Logentry 18 added for thread 1 sequence 32 ID 0x676c9833 dest 1:
Archived Logentry 19 added for thread 1 sequence 33 ID 0x676c9833 dest 1:
Changingstandby controlfile to MAXIMUM AVAILABILITY level
最大保护模式--此种模式在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
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
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
alterdatabase set standby database to maximize protection
Completed:alter database set standby database to maximize protection
alterdatabase open
LGWR:STARTING ARCH PROCESSES
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
ARC1 startedwith pid=22, OS id=6572
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
ARC3 startedwith pid=23, OS id=6580
NSS2 startedwith pid=24, OS id=6584
ARC3:Archival started
ARC0:STARTING ARCH PROCESSES COMPLETE
******************************************************************
******************************************************************
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
MTTRadvisory is disabled because FAST_START_MTTR_TARGET is not set
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
No ResourceManager plan active
replication_dependency_trackingturned off (no async multimaster replication found)
Startingbackground process QMNC
QMNC startedwith pid=25, OS id=6588
Completed:alter database open
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.
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
Startingbackground process VKRM
VKRM startedwith pid=26, OS id=6620
ARC2:STARTING ARCH PROCESSES
ARC4 startedwith pid=30, OS id=6646
ARC4:Archival started
ARC2:STARTING ARCH PROCESSES COMPLETE
Shuttingdown archive processes
ARCHshutting down
ARC4:Archival stopped
#######################################################
dg2 上的日志：
[oracle@dg2~]$ cat alert_dg.log
ALTER SYSTEMSET log_archive_dest_2='SERVICE=dg1 SYNCAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg1'SCOPE=MEMORY;
Archived Logentry 20 added for thread 1 sequence 34 ID 0x676c9833 dest 1:
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
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
Archived Logentry 21 added for thread 1 sequence 35 ID 0x676c9833 dest 1:
RFS[21]:Identified database type as 'physical standby': Client is ARCH pid 6576
查看更多关于ORACLE11Gdataguard安装配置手册的详细内容...