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

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

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

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

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

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#)

--------------

14:29:12SQL> alter system switch logfile;

Systemaltered.

14:29:39SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

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#)

--------------

一次切换当前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#)

--------------

对应的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#)

--------------

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#)

--------------

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#)

--------------

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

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

Kenyataan Laman Web ini

Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn