oracle 主从dataguard安装部署配置及测试

admin 数据库 2019-01-16 1812浏览 0

主库：10.168.193.234

从库：10.168.227.206

一.主库操作

1.设置force logging

SQL> select name from v$database;

NAME

---------

HELOWIN

SQL>  select force_logging from v$database;

FOR

---

NO

SQL>  alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR

---

YES

2.主库设为归档模式

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination           

Oldest online log sequence     1

Current log sequence           2

SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE

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

log_archive_dest_1                   string

log_archive_dest_10                  string

SQL> alter system set log_archive_dest_1='location=/opt/archivelog';

System altered.

SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE

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

log_archive_dest_1                   string      location=/opt/archivelog

log_archive_dest_10                  string

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  247463936 bytes

Fixed Size                  1218772 bytes

Variable Size              75499308 bytes

Database Buffers          167772160 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /opt/archivelog

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

SQL>

3. 添加redo log file

SQL> set wrap off

SQL> select * from v$logfile;

rows will be truncated

    GROUP# STATUS  TYPE    MEMBER

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

         3         ONLINE  /opt/oracle/oradata/dave/redo03.log

         2         ONLINE  /opt/oracle/oradata/dave/redo02.log

         1         ONLINE  /opt/oracle/oradata/dave/redo01.log

添加一个新的Standby Redologs组（注意组号不要与当前存在的Online Redologs组重复），并为该组指定一个成员：

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/opt/oracle/oradata/orcl/redo04.log') size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/opt/oracle/oradata/orcl/redo05.log') size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/opt/oracle/oradata/orcl/redo06.log') size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/opt/oracle/oradata/orcl/redo07.log') size 50M;

4 修改初始化参数文件

SQL> create pfile from spfile;

生成/opt/oracle/product/11.2.0/db_1/dbs/initorcl.ora文件，修改此文件

在initorcl.ora 添加如下内容：

*.DB_UNIQUE_NAME='hellowin_pd'

*.log_archive_config='dg_config=(hellowin_pd,hellowin_st)'

*.log_archive_dest_1='location=/opt/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=hellowin_pd'

*.log_archive_dest_2='service=hellowin_st reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=hellowin_st'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.standby_file_management='AUTO'

*.FAL_SERVER='hellowin_st'

*.FAL_CLIENT='hellowin_pd'

-- 注意：hellowin_st,hellowin_pd 是在tnsnames文件中配置的

SQL> create pfile from spfile;

File created.

SQL> create spfile from pfile='/opt/oracle/product/11.2.0/db_1/dbs/inithelowin.ora'; 

File created.

SQL> startup

SQL> show parameter log_archive_dest_1

NAME				     TYPE	 VALUE

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

log_archive_dest_1		     string	 location=/opt/archivelog valid

						_for=(all_logfiles,all_roles)

						db_unique_name=hellowin_pd

5 配置静态注册和监听

#建议用(net manager 工具设置)

cat /opt/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /opt/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

HELLOWIN_ST =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.168.227.206)(PORT = 1521))

)

    (CONNECT_DATA =

      (SERVICE_NAME = helowin)

)

)

HELLOWIN_PD =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.168.193.234)(PORT = 1521))

)

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = helowin)

)

)

[oracle@iZ23lhk0jmgZ ~]$ cat /opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

# listener.ora Network Configuration File: /opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = helowin)

      (ORACLE_HOME = /opt/oracle/product/11.2.0/db_1)

      (SID_NAME = helowin)

)

)

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.168.193.234)(PORT = 1521))

)

)

ADR_BASE_LISTENER = /opt/oracle

二. 备库的操作

1 创建相关目录（主库原有文件进行拷贝，没有为空即可）

$ mkdir -p  $ORACLE_BASE/oradata/helowin       

$ mkdir -p  $ORACLE_BASE/admin/helowin

$ mkdir -p  $ORACLE_BASE/admin/helowin /adump

$ mkdir -p  $ORACLE_BASE/admin/helowin /bdump

$ mkdir -p  $ORACLE_BASE/admin/helowin /cdump

$ mkdir -p  $ORACLE_BASE/admin/helowin /dpdump

$ mkdir -p  $ORACLE_BASE/admin/helowin /pfile

$ mkdir -p  $ORACLE_BASE/admin/helowin /udump

/opt/oracle/admin/helowin

[oracle@iZ23vwvnkpfZ helowin]$ ls

adump  bdump  cdump  dpdump  pfile  udump

2 创建pfile

#同主库配置，先创建pfile文件，然后修改，最后改回spfile (注意红色部分相反)

*.DB_UNIQUE_NAME='hellowin_st'

*.log_archive_config='dg_config=(hellowin_pd,hellowin_st)'

*.log_archive_dest_1='location=/opt/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=hellowin_st'

*.log_archive_dest_2='service=hellowin_pd reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=hellowin_pd'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.standby_file_management='AUTO'

*.FAL_SERVER='hellowin_pd'

*.FAL_CLIENT='hellowin_st'

#主备库目录结构不同的时候，设置如下参数进行转换

*.log_file_name_convert='/u02/oradata/orcl/','/u03/oradata/orcl/'

*.db_file_name_convert='/u02/oradata/orcl/','/u03/oradata/orcl/'

3 配置静态注册和监听

如上（同主库一致，可直接从主库拷贝至从库）

tnsnames.ora保持不变，listener.ora，只修改对应ip即可

4 创建口令文件 （直接拷贝过来就不用了）

$ orapwd file=?/dbs/orapwdave password=oracle

$ORACLE_HOME/dbs/orapwhelowin

(此密码文件主从一致) 

5. 添加redo log file

如上主数据库操作

三 .Oracle数据同步操作

（两种方法选一即可）

方法一：拷贝主库的数据文件至从库

将主库的密码文件，控制文件，数据文件，参数文件，日志文件copy到备库。

说明一点，这个控制文件是我们自己在主库创建的standby 控制文件。将copy过来的控制文件再复制三份就可以了。  主备的控制文件是不一样的。  这里除了采用直接copy 文件之外，还可以采用第二种方法 利用Rman 恢复来做。 直接copy 需要停数据库，如果采用RMAN 的话，就不需要停机了，。

从库操作：

$ cd /opt/oracle/oradata/helowin/

$ ls

control01.ctl  redo01.log  redo03.log       stdbyredo05.log  stdbyredo07.log  system01.dbf  undotbs01.dbf

example01.dbf  redo02.log  stdbyredo04.log  stdbyredo06.log  sysaux01.dbf     temp01.dbf    users01.db

删除数据文件

$rm –rf ./*

将如上主库的数据文件拷贝到从库，可通过scp拷贝

$ scp root@10.168.193.234:/opt/oracle/oradata/helowin/*.dbf ./

 $ scp root@10.168.193.234:/opt/oracle/oradata/helowin/*.ctl ./

拷贝控制文件

在主库创建备库控制文件（重点）

主库操作：

SQL> alter database create standby controlfile as '/tmp/control01.ctl';

-- 说明：判断一个数据库是Primary还是Standby，就是通过控制文件来判断的

切记注意控制文件2个位置，将控制文件拷贝至从库（从库操作）

  $ scp root@10.168.193.234: /tmp/control01.ctl /opt/oracle/oradata/helowin/control01.ctl

同时覆盖 /opt/oracle/flash_recovery_area/helowin/ control02.ctl

方法二：使用rman备份主库文件，然后在从库执行恢复操作 

在主库上执行rman操作备份数据库

RUN {

allocate channel c1 type disk;

allocate channel c2 type disk;

backup current controlfile for standby format='/opt/backup/control_%U';

BACKUP FORMAT '/opt/backup/helowin_%U_%T' skip inaccessible filesperset 5 DATABASE ;

sql 'alter system archive log current';

BACKUP FORMAT '/opt/backup/arch_%U_%T' skip inaccessible filesperset 5 ARCHIVELOG ALL DELETE INPUT;

release channel c2;

release channel c1;

}

#注释

建立c1 c2 通道

备份控制文件

备份数据库

执行归档命令

备份所有归档文件

释放c1 c2 通道

SKIP INACCESSIBLE：表示跳过不可读的文件。我们知道一些offline的数据文件只要存在于磁盘上就仍然可被读取，但是

可能有些文件已经被删除或移到它处造成不可读，加上这个参数就会跳过这些文件；

FILESPERSET 5: 表示一个备份集中有多少个文件，这个就是一个备份集有5个文件。

执行脚本（主库操作）

[oracle@iZ23lhk0jmgZ ~]$  rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Nov 18 15:23:03 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HELOWIN (DBID=2765967187)

RMAN> RUN {

2> allocate channel c1 type disk;

allocate channel c2 type disk;

backup current controlfile for standby format='/opt/backup/control_%U';

BACKUP FORMAT '/opt/backup/helowin_%U_%T' skip inaccessible filesperset 5 DATABASE ;

sql 'alter system archive log current';

BACKUP FORMAT '/opt/backup/arch_%U_%T' skip inaccessible filesperset 5 ARCHIVELOG ALL DELETE INPUT;

release channel c2;

release channel c1;

}3> 4> 5> 6> 7> 8> 9> 10>

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=414 device type=DISK

allocated channel: c2

channel c2: SID=21 device type=DISK

Starting backup at 18-NOV-15

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including standby control file in backup set

channel c1: starting piece 1 at 18-NOV-15

channel c1: finished piece 1 at 18-NOV-15

piece handle=/opt/backup/control_01qmj3bd_1_1 tag=TAG20151118T152532 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 18-NOV-15

Starting backup at 18-NOV-15

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00002 name=/opt/oracle/oradata/helowin/sysaux01.dbf

input datafile file number=00004 name=/opt/oracle/oradata/helowin/users01.dbf

input datafile file number=00005 name=/opt/oracle/oradata/helowin/example01.dbf

channel c1: starting piece 1 at 18-NOV-15

channel c2: starting full datafile backup set

channel c2: specifying datafile(s) in backup set

input datafile file number=00001 name=/opt/oracle/oradata/helowin/system01.dbf

input datafile file number=00003 name=/opt/oracle/oradata/helowin/undotbs01.dbf

channel c2: starting piece 1 at 18-NOV-15

channel c2: finished piece 1 at 18-NOV-15

piece handle=/opt/backup/helowin_03qmj3bf_1_1_20151118 tag=TAG20151118T152535 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:25

channel c2: starting full datafile backup set

channel c2: specifying datafile(s) in backup set

including current control file in backup set

channel c2: starting piece 1 at 18-NOV-15

channel c1: finished piece 1 at 18-NOV-15

piece handle=/opt/backup/helowin_02qmj3bf_1_1_20151118 tag=TAG20151118T152535 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:54

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel c1: starting piece 1 at 18-NOV-15

channel c2: finished piece 1 at 18-NOV-15

piece handle=/opt/backup/helowin_04qmj3c9_1_1_20151118 tag=TAG20151118T152535 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:25

channel c1: finished piece 1 at 18-NOV-15

piece handle=/opt/backup/helowin_05qmj3d5_1_1_20151118 tag=TAG20151118T152535 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 18-NOV-15

sql statement: alter system archive log current

Starting backup at 18-NOV-15

current log archived

channel c1: starting archived log backup set

channel c1: specifying archived log(s) in backup set

input archived log thread=1 sequence=4001 RECID=1 STAMP=896026051

input archived log thread=1 sequence=4002 RECID=2 STAMP=896026051

input archived log thread=1 sequence=4003 RECID=3 STAMP=896026055

input archived log thread=1 sequence=4004 RECID=4 STAMP=896026055

input archived log thread=1 sequence=4005 RECID=5 STAMP=896027502

channel c1: starting piece 1 at 18-NOV-15

channel c2: starting archived log backup set

channel c2: specifying archived log(s) in backup set

input archived log thread=1 sequence=4006 RECID=6 STAMP=896027502

input archived log thread=1 sequence=4007 RECID=7 STAMP=896027682

input archived log thread=1 sequence=4008 RECID=11 STAMP=896027721

input archived log thread=1 sequence=4009 RECID=14 STAMP=896040048

input archived log thread=1 sequence=4010 RECID=16 STAMP=896047222

channel c2: starting piece 1 at 18-NOV-15

channel c1: finished piece 1 at 18-NOV-15

piece handle=/opt/backup/arch_06qmj3d7_1_1_20151118 tag=TAG20151118T152631 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:03

channel c1: deleting archived log(s)

archived log file name=/opt/archivelog/1_4001_864574805.dbf RECID=1 STAMP=896026051

archived log file name=/opt/archivelog/1_4002_864574805.dbf RECID=2 STAMP=896026051

archived log file name=/opt/archivelog/1_4003_864574805.dbf RECID=3 STAMP=896026055

archived log file name=/opt/archivelog/1_4004_864574805.dbf RECID=4 STAMP=896026055

archived log file name=/opt/archivelog/1_4005_864574805.dbf RECID=5 STAMP=896027502

channel c1: starting archived log backup set

channel c1: specifying archived log(s) in backup set

input archived log thread=1 sequence=4011 RECID=18 STAMP=896047234

input archived log thread=1 sequence=4012 RECID=20 STAMP=896051648

input archived log thread=1 sequence=4013 RECID=22 STAMP=896064255

input archived log thread=1 sequence=4014 RECID=24 STAMP=896076855

channel c1: starting piece 1 at 18-NOV-15

channel c2: finished piece 1 at 18-NOV-15

piece handle=/opt/backup/arch_07qmj3d7_1_1_20151118 tag=TAG20151118T152631 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:03

channel c2: deleting archived log(s)

archived log file name=/opt/archivelog/1_4006_864574805.dbf RECID=6 STAMP=896027502

archived log file name=/opt/archivelog/1_4007_864574805.dbf RECID=7 STAMP=896027682

archived log file name=/opt/archivelog/1_4008_864574805.dbf RECID=11 STAMP=896027721

archived log file name=/opt/archivelog/1_4009_864574805.dbf RECID=14 STAMP=896040048

archived log file name=/opt/archivelog/1_4010_864574805.dbf RECID=16 STAMP=896047222

channel c2: starting archived log backup set

channel c2: specifying archived log(s) in backup set

input archived log thread=1 sequence=4015 RECID=26 STAMP=896083332

input archived log thread=1 sequence=4016 RECID=28 STAMP=896086361

channel c2: starting piece 1 at 18-NOV-15

channel c1: finished piece 1 at 18-NOV-15

piece handle=/opt/backup/arch_08qmj3da_1_1_20151118 tag=TAG20151118T152631 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:03

channel c1: deleting archived log(s)

archived log file name=/opt/archivelog/1_4011_864574805.dbf RECID=18 STAMP=896047234

archived log file name=/opt/archivelog/1_4012_864574805.dbf RECID=20 STAMP=896051648

archived log file name=/opt/archivelog/1_4013_864574805.dbf RECID=22 STAMP=896064255

archived log file name=/opt/archivelog/1_4014_864574805.dbf RECID=24 STAMP=896076855

channel c1: starting archived log backup set

channel c1: specifying archived log(s) in backup set

input archived log thread=1 sequence=4017 RECID=30 STAMP=896098561

input archived log thread=1 sequence=4018 RECID=32 STAMP=896109991

input archived log thread=1 sequence=4019 RECID=33 STAMP=896109991

channel c1: starting piece 1 at 18-NOV-15

channel c2: finished piece 1 at 18-NOV-15

piece handle=/opt/backup/arch_09qmj3dc_1_1_20151118 tag=TAG20151118T152631 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:02

channel c2: deleting archived log(s)

archived log file name=/opt/archivelog/1_4015_864574805.dbf RECID=26 STAMP=896083332

archived log file name=/opt/archivelog/1_4016_864574805.dbf RECID=28 STAMP=896086361

channel c1: finished piece 1 at 18-NOV-15

piece handle=/opt/backup/arch_0aqmj3dd_1_1_20151118 tag=TAG20151118T152631 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:02

channel c1: deleting archived log(s)

archived log file name=/opt/archivelog/1_4017_864574805.dbf RECID=30 STAMP=896098561

archived log file name=/opt/archivelog/1_4018_864574805.dbf RECID=32 STAMP=896109991

archived log file name=/opt/archivelog/1_4019_864574805.dbf RECID=33 STAMP=896109991

Finished backup at 18-NOV-15

released channel: c2

released channel: c1

#备库操作

将/opt/backup目录下的scp至从库的/opt/backup下，进入目录执行rman duplicate操作

Oracle启动至nomount状态（startup nomount）

$ rman target sys/oracle@hellowin_pd auxiliary /	

RMAN> duplicate target database for standby nofilenamecheck dorecover;

至此，Data Guard 的操作已经完成，下面来开始验证。

注意Data Guard 启动顺序：

启动顺序：先standby ,后primary;

	关闭顺序：先primary 后standby;

在备库将实例启动到mount 状态：

SQL> startup nomount;

SQL>alter database mount standby database ;

SQL>alter database recover managed standby database disconnect from session;

#也可以启动到只读方式

在备库启动监听：

$lsnrctl start

在主库启动实例：

SQL> startup;

在主库启动监听：

$lsnrctl start

在主库验证归档目录是否有效：

SQL> SELECT STATUS,DESTINATION, ERROR FROM V$ARCHIVE_DEST;

查看日志应用情况：

select sequence#,applied from v$archived_log;

检查DATAGUARD的运行是否正常

SELECT DEST_ID,ERROR FROM V$ARCHIVE_DEST;

如果有错误，要排查原因。

SQL> alter system switch logfile;

System altered.

主库：

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

4016

备库：

Connected.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

4016

四 注意事项：

（1） 备库监听不重启的情况下，重启备库实例后，主库能识别，如果备库监听重启，主库不能识别到备库，只有主库重启之后，才能识别。可以通过再主库执行 sqlplus sys/oracle@server_st as sysdba类似命令测试连接，很多时候不同步都与监听有关，需要重视！

（2）

启动顺序：先standby ,后primary;

闭顺序：先primary 后standby;

在备库将实例启动到mount 状态：

SQL> startup nomount;

SQL>alter database mount standby database ;

SQL>alter database recover managed standby database disconnect from session;

（3）如果在主库执行 alter database clear unarchived logfile或alter database open resetlogs ,　则dataguard要重建。

从库启动方式总结：

<1>.启动到管理模式

SQL>shutdown immediate;

SQL>startup nomount;

SQL>alter database mount standby database;

SQL>alter database recover managed standby database disconnect from session;

    <2>.启动到只读方式   

SQL>shutdown immediate;

SQL>startup nomount;

SQL>alter database mount standby database;

SQL>alter database open read only;

<3>如果在管理恢复模式下到只读模式

SQL> recover managed standby database cancel;

SQL> alter database open read only;

    <4>.从只读方式到管理恢复方式

     SQL> recover managed standby database disconnect from session;

从READ ONLY到 OPEN_MODE

alter database recover managed standby database using current logfile disconnect from session;

alter database recover managed standby database  disconnect from session ；

五 其他一些总结操作（从一些资料拿过来的）

1. 首先查看当前的保护模式   ---primary数据库操作

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

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

MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

2. 设置新的数据保护模式并重启数据库    --primary数据库操作

当保护模式更改顺序：

maximum protection --->  maximum availability ----> maximum performance

当在把dataguard的保护级别按这上面的顺序减低的时候， 不需要primary库在mount状态，否则primary 必须在mount 状态。

如：

SQL>  alter database set standby database to maximize availability;

alter database set standby database to maximize availability

*

ERROR at line 1:

ORA-01126: database must be mounted in this instance and not open in any

instance

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              79694068 bytes

Database Buffers           83886080 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL> alter database set standby database to maximize availability;

Database altered.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

报错了，这是因为最大可用性需要先修改日志传送方式为lgwr同步方式，否则，数据库是无法open.

Maximum protection/AVAILABILITY模式必须满足以下条件

Redo Archival Process: LGWR

Network Tranmission mode: SYNC

Disk Write Option: AFFIRM

Standby Redo Logs: Yes

standby database type: Physical Only

SQL> alter system set log_archive_dest_2='service=orcl_st lgwr sync AFFIRM';

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              79694068 bytes

Database Buffers           83886080 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL> alter database set standby database to maximize availability;

Database altered.

SQL> alter database open;

Database altered.

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

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

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

alter database set standby database to maximize performance;

提示：maximize后可跟{PROTECTION | AVAILABILITY | PERFORMANCE}，分别对应最大保护，最高可用性及最高性能。

在最大保护模式下，直接关闭备库是不行的，如果在备库上关闭数据库，会有如下提示：

SQL> shutdown immediate

ORA-01154: database busy. Open, close, mount, and dismount not allowed now

SQL>

在最大保护模式下，备库是不允许关闭的，此时首先关闭主库，然后备库就可以顺利关闭了。

注意： 主库的保护模式修改之后，备库的模式也会改变，和主库保持一致。

3. 查看日志归档情况

主库进行日志切换:

SQL>Alter system switch logfile;

select max(sequence#) from v$archived_log;

select max(sequence#) from v$log_history;

select group#,sequence#,archived,status from v$log;

select name,sequence#,applied from v$archived_log;

select sequence#,applied from v$archived_log;

若不同步，

1） 看log日志， archive是否有丢失

2）可以在备库坐如下操作：

alter database recover managed standby database cancel;

alter database recover managed standby database disconnect from session;

四.主备库切换

4. 1 Switchover

一般SWITCHOVER切换都是计划中的切换，特点是在切换后，不会丢失任何的数据，而且这个过程是可逆的，整个DATA GUARD环境不会被破坏，原来DATA GUARD环境中的所有物理和逻辑STANDBY都可以继续工作。

在进行DATA GUARD的物理STANDBY切换前需要注意：

1）确认主库和从库间网络连接通畅；

2）确认没有活动的会话连接在数据库中；

3）PRIMARY数据库处于打开的状态，STANDBY数据库处于MOUNT状态；

4）确保STANDBY数据库处于ARCHIVELOG模式；

5）如果设置了REDO应用的延迟，那么将这个设置去掉；

6）确保配置了主库和从库的初始化参数，使得切换完成后，DATA GUARD机制可以顺利的运行。 &shy;

主库切换到备库：

查看switchover 状态

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TO STANDBY

附： A:switchover_status出现session active/not allowed

当出现session active的时候表示还有活动的session,则运行

Alter database commit to switchover to physical standby with session shutdown;

     当出现not allowed时，在官方文档说转换会不成功，但是我测试的时候成功了。

     B.ora- 01153: an incompatible media recovery is active

        运行下面代码

        Alter database recover managed standby database finish;

        或者Alter database recover managed standby database finish force;

        Alter database recover managed standby database disconnect from session;

2 切换成备库

SQL>Alter database commit to switchover to physical standby with session shutdown;

或者

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

     Database altered.

3 启动到mount和应用日志状态

SQL> SHUTDOWN IMMEDIATE

SQL> startup nomount;

SQL> alter database mount standby database;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

4. 查看数据库模式

SQL>select dest_name,status,database_mode,recovery_mode,protection_mode from v$archive_dest_status;

SQL>select status,database_mode from v$archive_dest_status;

备库切换为主库：

1.查看switchover状态

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

    TO PRIMARY

附：若不是用此语句切换：ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown

补充：若出现：ORA-16139: media recovery required

是因为没有执行：alter database recover managed standby database disconnect from session;

2. 切换成主库

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

SQL> shutdown immediate;

SQL> startup;

SQL> alter system switch logfile;

3. 查看数据库模式

SQL>select dest_name,status,database_mode,recovery_mode,protection_mode from v$archive_dest_status;

SQL>select status,database_mode from v$archive_dest_status;

验证同步：

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

78

4.2.  Failovers：（主库无法启动，不可逆操作）

FAILOVER切换一般是PRIMARY数据库发生故障后的切换，这种情况是STANDBY数据库发挥其作用的情况。这种切换发生后，可能会造成数据的丢失。而且这个过程不是可逆的，DATA GUARD环境会被破坏。

由于PRIMARY数据库已经无法启动，所以FAILOVER切换所需的条件并不多，只要检查STANDBY是否运行在最大保护模式下，如果是的话，需要将其置为最大性能模式，否则切换到PRIMARY角色也无法启动。

1.　查看是否有日志GAP,没有应用的日志：

    SQL> SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG;

　　SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

　　如果有，则拷贝过来并且注册

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '路径';

重复查看直到没有应用的日志：

2. 然后停止应用归档：

　　SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

　　Database altered.

3. 下面将STANDBY数据库切换为PRIMARY数据库：

　　SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

或 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

　　Database altered.

　　SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

　　DATABASE_ROLE

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

　　PHYSICAL STANDBY

　　SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

　　Database altered.

    SQL> ALTER DATABASE OPEN; 或者 shutdown immediate+startup

　　Database altered.

　　检查数据库是否已经切换成功：

　　SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

　　DATABASE_ROLE

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

　　PRIMARY

至此，FAILOVER切换完成。这个时候应该马上对新的PRIMARY数据库进行备份。

转载请注明：梦绘设计 » 数据库 » oracle 主从dataguard安装部署配置及测试

版权声明

本站《作品展示》类文章均为原创，转载必须注明出处，技术分享类文章部分来源于网络，版权归原作者所有,若侵权请留言。

oracle 主从dataguard安装部署配置及测试

版权声明

最新文章

热门文章

热评文章

标签列表

oracle 主从dataguard安装部署配置及测试

版权声明

你可能喜欢:

最新文章

热门文章

热评文章

标签列表