mysql教程

ORACLE 11G dataguard安装配置手册

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:50 PM

oracle安裝手冊配置

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

陳述

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

您什麼時候應該使用複合索引與多個單列索引？Apr 11, 2025 am 12:06 AM

在數據庫優化中，應根據查詢需求選擇索引策略：1.當查詢涉及多個列且條件順序固定時，使用複合索引；2.當查詢涉及多個列但條件順序不固定時，使用多個單列索引。複合索引適用於優化多列查詢，單列索引則適合單列查詢。

如何識別和優化MySQL中的慢速查詢？（慢查詢日誌，performance_schema）Apr 10, 2025 am 09:36 AM

要優化MySQL慢查詢，需使用slowquerylog和performance_schema：1.啟用slowquerylog並設置閾值，記錄慢查詢；2.利用performance_schema分析查詢執行細節，找出性能瓶頸並優化。

MySQL和SQL：開發人員的基本技能Apr 10, 2025 am 09:30 AM

MySQL和SQL是開發者必備技能。 1.MySQL是開源的關係型數據庫管理系統，SQL是用於管理和操作數據庫的標準語言。 2.MySQL通過高效的數據存儲和檢索功能支持多種存儲引擎，SQL通過簡單語句完成複雜數據操作。 3.使用示例包括基本查詢和高級查詢，如按條件過濾和排序。 4.常見錯誤包括語法錯誤和性能問題，可通過檢查SQL語句和使用EXPLAIN命令優化。 5.性能優化技巧包括使用索引、避免全表掃描、優化JOIN操作和提升代碼可讀性。

描述MySQL異步主奴隸複製過程。Apr 10, 2025 am 09:30 AM

MySQL異步主從復制通過binlog實現數據同步，提升讀性能和高可用性。 1)主服務器記錄變更到binlog；2)從服務器通過I/O線程讀取binlog；3)從服務器的SQL線程應用binlog同步數據。

mysql：簡單的概念，用於輕鬆學習Apr 10, 2025 am 09:29 AM

MySQL是一個開源的關係型數據庫管理系統。 1）創建數據庫和表：使用CREATEDATABASE和CREATETABLE命令。 2）基本操作：INSERT、UPDATE、DELETE和SELECT。 3）高級操作：JOIN、子查詢和事務處理。 4）調試技巧：檢查語法、數據類型和權限。 5）優化建議：使用索引、避免SELECT*和使用事務。

MySQL：數據庫的用戶友好介紹Apr 10, 2025 am 09:27 AM

MySQL的安裝和基本操作包括：1.下載並安裝MySQL，設置根用戶密碼；2.使用SQL命令創建數據庫和表，如CREATEDATABASE和CREATETABLE；3.執行CRUD操作，使用INSERT,SELECT,UPDATE,DELETE命令；4.創建索引和存儲過程以優化性能和實現複雜邏輯。通過這些步驟，你可以從零開始構建和管理MySQL數據庫。

InnoDB緩衝池如何工作，為什麼對性能至關重要？Apr 09, 2025 am 12:12 AM

InnoDBBufferPool通過將數據和索引頁加載到內存中來提升MySQL數據庫的性能。 1)數據頁加載到BufferPool中，減少磁盤I/O。 2)臟頁被標記並定期刷新到磁盤。 3)LRU算法管理數據頁淘汰。 4)預讀機制提前加載可能需要的數據頁。

MySQL：初學者的數據管理易用性Apr 09, 2025 am 12:07 AM

MySQL適合初學者使用，因為它安裝簡單、功能強大且易於管理數據。 1.安裝和配置簡單，適用於多種操作系統。 2.支持基本操作如創建數據庫和表、插入、查詢、更新和刪除數據。 3.提供高級功能如JOIN操作和子查詢。 4.可以通過索引、查詢優化和分錶分區來提升性能。 5.支持備份、恢復和安全措施，確保數據的安全和一致性。

See all articles