ITPub博客

首页 > Linux操作系统 > Linux操作系统 > VMware中配置ORACLE DATAGUARD步骤

VMware中配置ORACLE DATAGUARD步骤

原创 Linux操作系统 作者:lsm_3036 时间:2011-04-07 11:43:48 0 删除 编辑

Vmware 中配置和建立ORACLE 10.2.0.1physical standby数据库

一、 配置条件

1.1         软件条件

Vmware workstation  ORACLE 10.2.0.1

1.2         硬件条件

RAC2  (192.168.188.54 primary

RAC1  (192.168.188.58)   standby

1.3、服务器配置的监听

二、前提服务的配置

在(192.168.188.54primary上配置数据库软件和数据库SIDNACEC

在(192.168.188.58standby上配置和primaryORACLE安装的那些相同的目录

2.1、建立$ORACLE_BASE下的目录一样

由于我们的机器是安装RAC数据库的primarystandby的目录基本上是相同的。

我们为了省事,我们直接的从primary(RAC2)的机器上把/home/DBSoftware/oracle/admin/NACEC COPYstandby(RAC1)的机器上/home/DBSoftware/oracle/admin 目录下来

[oracle@rac2 admin]$ pwd

/home/DBSoftware/oracle/admin

[oracle@rac2 admin]$ ls

NACEC

RAC2机器上/home/DBSoftware/oracle/admin/NACEC COPY RAC1下的/home/DBSoftware/oracle/admin/NACEC

[oracle@rac1 admin]$ pwd

/home/DBData/admin

[oracle@rac1 admin]$ scp -r rac2: /home/DBSoftware/oracle/admin/NACEC

 [oracle@rac1 admin]$ ls

NACEC

$ORACLE_BASE的目录就这样建立无全一样了。

2.2、建立$ORACLE_DATA下的目录一样

由于primary(RAC2)的数据文件目录在/home/DBData/oradata/NACEC

[oracle@rac2 admin]$ ls

NACEC

[oracle@rac2 admin]$ cd ..

[oracle@rac2 DBData]$ ls

oradata 

[oracle@rac2 app]$ cd oradata

[oracle@rac2 oradata]$ ls

NACEC

[oracle@rac2 oradata]$ pwd

/home/DBData/oradata

那么我们就在standby(RAC1)上建立和primary(RAC2)相同的数据文件目录

[oracle@rac1 oradata]$ pwd

/home/DBData/oradata

[oracle@rac1 oradata]$ mkdir NACEC

[oracle@rac1 oradata]$ ls

NACEC

2.3、在primary(RAC2)上建立数据库的归档日志目录和rman备份文件的目录

[oracle@rac2 oradata]$ ls

NACEC

[oracle@rac2 oradata]$ mkdir archive    ----/放(RAC2)归档日志的

[oracle@rac2 oradata]$ mkdir backup    ----/放(RAC2)的RMAN备份文件

[oracle@rac2 oradata]$ ls

archive  backup  NACEC

那么我就在(RAC1standby建立和RAC2相同的目录结构

[oracle@rac1 oradata]$ mkdir archive

[oracle@rac1 oradata]$ mkdir backup

2.4、在主库上创建pfile参数,并且修改其参数

2.4.1、修改(RAC2primary数据库到归档方式

[oracle@rac2 dbs]$ export ORACLE_SID=NACEC

[oracle@rac2 dbs]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 9 16:45:29 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> conn / as sysdba

Connected.

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                  1260696 bytes

Variable Size              88081256 bytes

Database Buffers           75497472 bytes

Redo Buffers                2932736 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database force logging;

Database altered.

2.4.2、修改(RAC2primary数据库上的PFILE

SQL> create pfile='$ORACLE_HOME/dbs/initNACEC.ora' from spfile;

File created.

我们近来编辑$ORACLE_HOME/dbs/initNACEC.ora文件

NACEC.__db_cache_size=75497472

NACEC.__java_pool_size=4194304

NACEC.__large_pool_size=4194304

NACEC.__shared_pool_size=79691776

NACEC.__streams_pool_size=0

*.audit_file_dest='/home/DBSoftware/oracle/admin/NACEC/adump'

*.background_dump_dest='/home/DBSoftware/oracle/admin/NACEC /bdump'

*.compatible='10.2.0.1.0'

*.control_files='/home/DBData/oradata/NACEC/control01.ctl','/home/DBData/oradata/NACEC/control02.ctl','/home/DBData/oradata/NACEC /control03.ctl'

*.core_dump_dest='/home/DBSoftware/oracle/admin/NACEC/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='NACEC'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=NACECXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=167772160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/home/DBSoftware/oracle/admin/NACEC/udump'

 

###############################################

#####DATA GUARD ---primary database

###############################################

*.db_unique_name='nacecprim'

*.log_archive_config='DG_CONFIG=(primary,standby)'

*.log_archive_dest_1='location=/home/DBData/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=nacecprim

*.log_archive_format='log_%t_%s_%r.arc'

*.log_archive_dest_2='SERVICE=standby  LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=nacecstdb

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.log_file_name_convert='/home/DBData/oradata/archive','/home/DBData/oradata/archive'

*.db_file_name_convert='/home/DBData/oradata/NACEC','/home/DBData/oradata/NACEC'

#*.standby_archive_dest='/home/DBData/oradata/archive'

*.standby_file_management='AUTO'

*.fal_server='nacecprim'

*.fal_client= ’nacecstdb’

 

###############################################

#####DATA GUARD ---primary database参数的解释

###############################################

*.db_unique_name='primary'                         ####这个是主数据库的网络服务名(NET

*.log_archive_config='DG_CONFIG=(nacecprim,nacecstdb)'   ##是主库和备用库的网络服务名

*.log_archive_dest_1='location=/home/DBData/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=nacecprim'      ##本地的日志应该是放在本地的db_unique_name=主库的服务名

*.log_archive_format='log_%t_%s_%r.arc'

*.log_archive_dest_2='SERVICE=standby  LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=nacecstdb'   ##通过日志传输到备用库db_unique_name=备用库的网络服务名

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.log_file_name_convert='/home/DBData/oradata/archive','/home/ DBData /oradata/archive'

*.db_file_name_convert='/home/ DBData /oradata/NACEC,'/home/ DBData /oradata/NACEC

#*.standby_archive_dest='/home/ DBData /oradata/archive'

*.standby_file_management='AUTO'

*.fal_server='nacecprim'    ###主库的网络服务名

*.fal_client='nacecstdb'    ###备用库的网络服务名

保存,关闭数据库然后用这个参数重新启动数据库。OK

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1260696 bytes

Variable Size              92275560 bytes

Database Buffers           71303168 bytes

Redo Buffers                2932736 bytes

Database mounted.

Database opened.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/ DBData /oradata/archive

Oldest online log sequence     8

Next log sequence to archive   10

Current log sequence           10

发现参数正确。OK

2.5、在备用库上创建pfile参数,并修改其参数

同样的我们要在备用库(RAC1 standby数据库建立initNACEC.ora文件,并要编辑他

我们可以从(RAC2primaryCOPE一个initNACEC.ora文件到$ORACLE_HOME/dbs/下。

[oracle@rac1 dbs]$ scp rac2:$ORACLE_HOME/dbs/initNACEC.ora .

initNACEC.ora                                                                             

    100% 1555     1.5KB/s   00:00

现在我们就来修改standbyRAC1)上的initNACEC.ora文件,修改如下:

NACEC.__db_cache_size=75497472

NACEC.__java_pool_size=4194304

NACEC.__large_pool_size=4194304

NACEC.__shared_pool_size=79691776

NACEC.__streams_pool_size=0

*.audit_file_dest='/home/DBSoftware/oracle/admin/ NACEC /adump'

*.background_dump_dest='/home/DBSoftware/oracle/admin/ NACEC /bdump'

*.compatible='10.2.0.1.0'

*.control_files='/home/DBData/oradata/ NACEC /control01.ctl','/home/DBData/oradata/ NACEC /control02.ctl','/home/DBData/oradata/ NACEC /control03.ctl'

*.core_dump_dest='/home/DBSoftware/oracle/admin/ NACEC /cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='NACEC'

*.dispatchers='(PROTOCOL=TCP) (SERVICE= NACEC XDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=167772160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/home/DBSoftware/oracle/admin/ NACEC /udump'

 

###############################################

#####DATA GUARD --standby database

###############################################

*.db_unique_name='nacecstdb'

*.log_archive_config='DG_CONFIG=(nacecprim,nacecstdb)'

*.log_archive_dest_1='location=/home/DBData/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=nacecstdb'

*.log_archive_format='log_%t_%s_%r.arc'

*.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=nacecprim'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.log_file_name_convert='/home/DBData/oradata/archive','/home/ DBData /oradata/archive'

*.db_file_name_convert='/home/ DBData /oradata/NACEC,'/home/ DBData /oradata/ NACEC,

#*.standby_archive_dest='/home/ DBData /oradata/archive'

*.standby_file_management='AUTO'

*.fal_server='nacecstdb'

*.fal_client='nacecprim'

 

###############################################

#####DATA GUARD --standby database备用库参数的解释

###############################################

*.db_unique_name='standby'                          ###备用库网络服务名

*.log_archive_config='DG_CONFIG=(nacecprim,nacecstdb)'   ###主库和备用库的网络服务名

*.log_archive_dest_1='location=/home/DBData/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=nacecstdb' 

###备用库的网络服务名

*.log_archive_format='log_%t_%s_%r.arc'

*.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=nacecprim'    

##主库的网络服务名

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.log_file_name_convert='/home/DBData/oradata/archive','/home/ DBData /oradata/archive'

*.db_file_name_convert='/home/ DBData /oradata/NACEC','/home/ DBData /oradata/NACEC'

#*.standby_archive_dest='/home/ DBData /oradata/archive'

*.standby_file_management='AUTO'

*.fal_server='nacecstdb'    ####备用库的网络服务名

*.fal_client='nacecprim'    ####主库的网络服务名

2.6、在备用库上建立备用库的password文件

我们可以从(RAC2primaryCOPY 密码文件过来

[oracle@rac1 dbs]$ scp rac2:$ORACLE_HOME/dbs/orapwNACEC .

orapwNACEC                                                                                   

100% 1536     1.5KB/s   00:00  

三、通过rman备份primary数据库,然后恢复到standby数据库上

3.1、通过RMAN备份primary数据库

在主库上操作(primary

[oracle@rac2 dbs]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Dec 11 13:40:54 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: NACEC (DBID=1771818397)

为备用库,备份控制文件和全库。

RMAN> run {

2> allocate channel c1 type disk;

3> backup tag='fulldatabase' format '/home/DBData/oradata/backup/full_%U_%I.dbf' database;

4> backup current controlfile for standby tag='controlfile' format '/home/DBData/oradata/backup/control_%U_%I.ctl';

5> }

released channel: ORA_DISK_1

allocated channel: c1

channel c1: sid=143 devtype=DISK

Starting backup at 14-SEP-10

channel c1: starting full datafile backupset

channel c1: specifying datafile(s) in backupset

input datafile fno=00001 name=/home/DBData/oradata/NACEC/system01.dbf

input datafile fno=00003 name=/home/DBData/oradata/NACEC/sysaux01.dbf

input datafile fno=00002 name=/home/DBData/oradata/NACEC/undotbs01.dbf

input datafile fno=00004 name=/home/DBData/oradata/NACEC/users01.dbf

channel c1: starting piece 1 at 14-SEP-10

channel c1: finished piece 1 at 14-SEP-10

piece handle=/home/DBData/oradata/backup/full_05k206eg_1_1_1771818397.dbf tag=FULLDATABASE comment=NONE

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

channel c1: starting full datafile backupset

channel c1: specifying datafile(s) in backupset

including current control file in backupset

channel c1: starting piece 1 at 14-SEP-10

channel c1: finished piece 1 at 14-SEP-10

piece handle=/home/DBData/oradata/backup/full_06k206gh_1_1_1771818397.dbf tag=FULLDATABASE comment=NONE

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

Finished backup at 14-SEP-10

Starting backup at 14-SEP-10

channel c1: starting full datafile backupset

channel c1: specifying datafile(s) in backupset

including standby control file in backupset

channel c1: starting piece 1 at 14-SEP-10

channel c1: finished piece 1 at 14-SEP-10

piece handle=/home/DBData/oradata/backup/control_07k206gl_1_1_1771818397.ctl tag=CONTROLFILE comment=NONE

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

Finished backup at 14-SEP-10

released channel: c1

3.2、挂载主库(RAC2)的/home/DBData/oradata/backup/ NFS目录,以让(RAC1看见)

RAC2primary)上通过root用户编辑/etc/exports 文件,在其中加入一行

/home/DBData/oradata/backup *(sync,rw) 然后保存退出

然后在启动portmap服务

Service portmap start

Service nfs start

再在RAC1STANDBY)的机器上挂载这个目录到相同的目录

root用户操作

mount -t nfs -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 rac2:/home/DBData/oradata/backup /home/DBData/oradata/backup

注意 o 选项,如果有的选项不正确,将到导致/home/DBData/oradata/backup在执行rman的时候不好使。

3.3、恢复standby数据库

1、在备用库(RAC1STANDBY上操作

[oracle@rac1 ~]$ export ORACLE_SID=NACEC

[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 11 13:35:52 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1260696 bytes

Variable Size              92275560 bytes

Database Buffers           71303168 bytes

Redo Buffers                2932736 bytes

2、启动RMAN来执行恢复standby数据库的命令

[oracle@rac1 ~]$ export ORACLE_SID=NACEC

[oracle@rac1 ~]$ rman auxiliary sys/tadertader@nacecstdb target sys/tadertader@nacecprim

这里的standby是备用库的网络服务名

这里的primary是主库的网络服务名

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Sep 14 13:57:16 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: NACEC (DBID=1771818397)

connected to auxiliary database: NACEC (not mounted)

RMAN> run {

2> set until sequence=33 thread=1;

3> duplicate target database for standby nofilenamecheck dorecover;

4> }

###sequence=33 thread=1是在主库执行alter system switch logfile;

Select sequence# from v$archived_log order by sequence#;的最后一行。

executing command: SET until clause

using target database control file instead of recovery catalog

Starting Duplicate Db at 14-SEP-10

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=152 devtype=DISK

contents of Memory Script.:

{

   set until scn  482409;

   restore clone standby controlfile;

   sql clone 'alter database mount standby database';

}

executing Memory Script

executing command: SET until clause

Starting restore at 14-SEP-10

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /home/DBData/oradata/backup/control_07k206gl_1_1_1771818397.ctl

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/home/DBData/oradata/backup/control_07k206gl_1_1_1771818397.ctl tag=CONTROLFILE

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16

output filename=/home/DBData/oradata/NACEC/control01.ctl

output filename=/home/DBData/oradata/NACEC/control02.ctl

output filename=/home/DBData/oradata/NACEC/control03.ctl

Finished restore at 14-SEP-10

sql statement: alter database mount standby database

released channel: ORA_AUX_DISK_1

contents of Memory Script.:

{

   set until scn  482409;

   set newname for tempfile  1 to

 "/home/DBData/oradata/NACEC/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/home/DBData/oradata/NACEC/system01.dbf";

   set newname for datafile  2 to

 "/home/DBData/oradata/NACEC/undotbs01.dbf";

   set newname for datafile  3 to

 "/home/DBData/oradata/NACEC/sysaux01.dbf";

   set newname for datafile  4 to

 "/home/DBData/oradata/NACEC/users01.dbf";

   restore

   check readonly

   clone database

   ;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to /home/DBData/oradata/NACEC/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 14-SEP-10

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=152 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /home/DBData/oradata/NACEC/system01.dbf

restoring datafile 00002 to /home/DBData/oradata/NACEC/undotbs01.dbf

restoring datafile 00003 to /home/DBData/oradata/NACEC/sysaux01.dbf

restoring datafile 00004 to /home/DBData/oradata/NACEC/users01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /home/DBData/oradata/backup/full_05k206eg_1_1_1771818397.dbf

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/home/DBData/oradata/backup/full_05k206eg_1_1_1771818397.dbf tag=FULLDATABASE

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:07

Finished restore at 14-SEP-10

contents of Memory Script.:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=5 stamp=673192969 filename=/home/DBData/oradata/NACEC/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=6 stamp=673192969 filename=/home/DBData/oradata/NACEC/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=7 stamp=673192969 filename=/home/DBData/oradata/NACEC/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=8 stamp=673192969 filename=/home/DBData/oradata/NACEC/users01.dbf

contents of Memory Script.:

{

   set until scn  482409;

   recover

   standby

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 14-SEP-10

using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /home/DBData/oradata/archive/log_1_6_673190306.arc

archive log thread 1 sequence 7 is already on disk as file /home/DBData/oradata/archive/log_1_7_673190306.arc

archive log thread 1 sequence 8 is already on disk as file /home/DBData/oradata/archive/log_1_8_673190306.arc

archive log filename=/home/DBData/oradata/archive/log_1_6_673190306.arc thread=1 sequence=6

archive log filename=/home/DBData/oradata/archive/log_1_7_673190306.arc thread=1 sequence=7

archive log filename=/home/DBData/oradata/archive/log_1_8_673190306.arc thread=1 sequence=8

media recovery complete, elapsed time: 00:00:04

Finished recover at 14-SEP-10

Finished Duplicate Db at 14-SEP-10

3、然后在备用库上启动备用库到恢复模式。

[oracle@rac1 ~]$ export ORACLE_SID=NACEC

[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 14 13:35:52 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1260696 bytes

Variable Size              92275560 bytes

Database Buffers           71303168 bytes

Redo Buffers                2932736 bytes

SQL> conn / as sysdba

Connected.

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

至此,我们的dataguard就已经建立了。OK

四、启动DATAGUARD

4.1启动(RAC1standby备用数据库

[oracle@rac1 ~]$ export ORACLE_SID=NACEC

[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 14 14:33:44 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1260696 bytes

Variable Size              92275560 bytes

Database Buffers           71303168 bytes

Redo Buffers                2932736 bytes

SQL> alter database mount standby database;

Database altered.

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

Database altered.

检查日志的应用情况。

SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APP

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

        14 YES

        11 YES

        12 YES

        13 YES

        15 YES

SQL> select name,PROTECTION_MODE,PROTECTION_LEVEL,REMOTE_ARCHIVE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database

  2  ;

NAME      PROTECTION_MODE      PROTECTION_LEVEL     REMOTE_A DATABASE_ROLE

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

SWITCHOVER_STATUS

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

NACEC  MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  ENABLED  PHYSICAL STANDBY

SESSIONS ACTIVE

SQL> set linesize 140

SQL> /

NAME      PROTECTION_MODE      PROTECTION_LEVEL     REMOTE_A DATABASE_ROLE    SWITCHOVER_STATUS

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

NACEC  MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  ENABLED  PHYSICAL STANDBY SESSIONS ACTIVE

SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APP

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

        14 YES

        11 YES

        12 YES

        13 YES

        15 YES

启动(RAC2primary数据库

[oracle@rac2 ~]$ export ORACLE_SID=NACEC

[oracle@rac2 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 14 14:34:45 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1260696 bytes

Variable Size              92275560 bytes

Database Buffers           71303168 bytes

Redo Buffers                2932736 bytes

Database mounted.

Database opened.

SQL> select name,PROTECTION_MODE,PROTECTION_LEVEL,REMOTE_ARCHIVE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

NAME      PROTECTION_MODE      PROTECTION_LEVEL     REMOTE_A DATABASE_ROLE

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

SWITCHOVER_STATUS

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

NACEC  MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  ENABLED  PRIMARY

SESSIONS ACTIVE

SQL> set linesize 140

SQL> /

NAME      PROTECTION_MODE      PROTECTION_LEVEL     REMOTE_A DATABASE_ROLE    SWITCHOVER_STATUS

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

NACEC  MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  ENABLED  PRIMARY          SESSIONS ACTIVE

SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APP

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

        10 NO

        11 NO

        12 NO

        13 NO

        14 NO

        14 YES

        11 NO

        12 NO

        13 NO

        15 NO

        15 YES

11 rows selected.

五、做DATAGUARDPRIMARYSTANDBY的切换

5.1做主库(RAC2primary到备库(RAC1standby的切换

SQL> select name,PROTECTION_MODE,PROTECTION_LEVEL,REMOTE_ARCHIVE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

NAME      PROTECTION_MODE      PROTECTION_LEVEL     REMOTE_A DATABASE_ROLE

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

SWITCHOVER_STATUS

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

NACEC  MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  ENABLED  PRIMARY

SESSIONS ACTIVE

SQL> select status from v$instance;

STATUS

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

OPEN

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

Database altered.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1260696 bytes

Variable Size              92275560 bytes

Database Buffers           71303168 bytes

Redo Buffers                2932736 bytes

SQL> alter database mount standby database;

Database altered.

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

Database altered.

5.2从备库(RAC1standby到主库(RAC2primary的切换

SQL> select name,PROTECTION_MODE,PROTECTION_LEVEL,REMOTE_ARCHIVE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database

  2  ;

NAME      PROTECTION_MODE      PROTECTION_LEVEL     REMOTE_A DATABASE_ROLE

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

SWITCHOVER_STATUS

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

NACEC  MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  ENABLED  PHYSICAL STANDBY

SESSIONS ACTIVE

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> alter database recover managed standby database cancel;

alter database recover managed standby database cancel

*

ERROR at line 1:

ORA-16136: Managed Standby Recovery not active

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

Database altered.

SQL> alter database recover managed standby database cancel;

 

Database altered.

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

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

PHYSICAL STANDBY TO PRIMARY

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> alter database commit to switchover to primary;

Database altered.

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1260696 bytes

Variable Size              92275560 bytes

Database Buffers           71303168 bytes

Redo Buffers                2932736 bytes

Database mounted.

Database opened.

看看日志能不能传输过去。也是我们下一步的测试dataguard是否配置成功。

切换的顺序应该是先切换主库到备用库,然后从备用库再切换到主库。

六、测试我们的DATAGUARD是否成功。

RAC1STANDBY)数据库上切换日志,看日志能不能传到RAC2PRIMARY)数据库上。

RAC1STANDBY)上看日志的情况

SQL> select count(*) from left;

  COUNT(*)

----------

    458752

SQL> delete from left where rownum <200001;

200000 rows deleted.

SQL> commit;

Commit complete.

SQL> conn scott/tiger

Connected.

SQL> select count(*) from left;

  COUNT(*)

----------

    258752

SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APP

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

        14 YES

        11 YES

        12 YES

        13 YES

        15 YES

        16 YES

        17 YES

        18 NO

        18 YES

        19 NO

        20 NO

 SEQUENCE# APP

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

        19 NO

        21 NO

        22 NO

        20 YES

        23 NO

        21 NO

        22 NO

        24 NO

        23 NO

        24 YES

21 rows selected.

RAC2PRIMARY)上看到日志已经传输过来了

QL> select sequence#,applied from v$archived_log;

 SEQUENCE# APP

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

        10 YES

        11 YES

        12 YES

        13 YES

        14 YES

        14 YES

        11 NO

        12 NO

        13 NO

        15 YES

        15 YES

 SEQUENCE# APP

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

        16 YES

        16 YES

        17 YES

        17 NO

        18 YES

        19 YES

        20 YES

        21 YES

        22 YES

        23 YES

        24 YES

22 rows selected.

现在我们启动(RAC2PRIMARY数据库到open read only状态

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> conn scott/tiger

Connected.

SQL> select count(*) from left; 

  COUNT(*)

----------

458752

我们发现数据scott.left用户表中的数据库是正确的。证明我们的switchover 切换是成功的。

日志也传输成功。至此我们的DATAGUARD配置就完成了。

七、附件

7.1在主库切换到备用库的过程中,要注意的。

在切换之前,我们看正常的备用库和主库的switchover_status的状态

备用库切换之前。

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1260696 bytes

Variable Size              92275560 bytes

Database Buffers           71303168 bytes

Redo Buffers                2932736 bytes

SQL> alter database mount standby database;

Database altered.

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

Database altered.

SQL> select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    OPEN_MODE

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

PHYSICAL STANDBY SESSIONS ACTIVE      MOUNTED

在切换之后。

SQL> /

DATABASE_ROLE    SWITCHOVER_STATUS    OPEN_MODE

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

PHYSICAL STANDBY TO PRIMARY           MOUNTED

注意到在切换之后备用库中switchover_status session action变化到了 to primary的状态。

如在主库上切换之前。

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

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

PRIMARY          SESSIONS ACTIVE

SQL> select status from v$instance;

STATUS

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

OPEN

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

Database altered.

在切换的过程中,我们要注意SWITCHOVER_STATUS 他的值,如果是SESSIONS ACTIVE我在切换的语句要加with session shutdown,在这里执行完后,我们看到备用库上。

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1260696 bytes

Variable Size              92275560 bytes

Database Buffers           71303168 bytes

Redo Buffers                2932736 bytes

SQL> alter database mount standby database;

Database altered.

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

Database altered.

在主库切换到备用库后。

SQL> select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    OPEN_MODE

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

PHYSICAL STANDBY SESSIONS ACTIVE      MOUNTED

7.2在备用库切换到主库上。要注意的地方。

在备用库上:查看switchover_status的状态,

SQL> select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    OPEN_MODE

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

PHYSICAL STANDBY TO PRIMARY           MOUNTED

发现switchover_status的状态已经到了to primary

我们现在切换。

SQL> alter database commit to switchover to primary;

Database altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1260696 bytes

Variable Size              92275560 bytes

Database Buffers           71303168 bytes

Redo Buffers                2932736 bytes

Database mounted.

Database opened.

SQL> select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    OPEN_MODE

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

PRIMARY          SESSIONS ACTIVE      READ WRITE

至此,我们的切换就完成了。

测试日志是否传输。

在主库上执行

SQL> conn scott/tiger

Connected.

SQL> select count(*) from left;

  COUNT(*)

----------

    258752

SQL> delete from left where rownum < 200001;

200000 rows deleted.

SQL> commit;

Commit complete.

SQL> conn / as sysdba

Connected.

SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APP

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

        10 YES

        11 YES

        12 YES

        13 YES

        14 YES

        14 YES

        11 NO

        12 NO

        13 NO

        15 YES

        15 YES

 SEQUENCE# APP

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

        16 YES

        16 YES

        17 YES

        17 NO

        18 YES

        19 YES

        20 YES

        21 YES

        22 YES

        23 YES

        24 YES

 SEQUENCE# APP

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

        25 YES

        26 YES

        27 NO

        27 YES

        28 NO

        29 NO

        28 NO

        30 NO

        31 NO

        29 NO

        32 NO

 SEQUENCE# APP

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

        33 NO

        30 NO

        31 YES

        32 NO

        33 NO

38 rows selected.

SQL> conn scott/tiger

Connected.

SQL> select count(*) from left;

  COUNT(*)

----------

     58752

然后查看备用库:

SQL> /

 SEQUENCE# APP

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

        14 YES

        11 YES

        12 YES

        13 YES

        15 YES

        16 YES

        17 YES

        18 YES

        18 YES

        19 YES

        20 YES

 SEQUENCE# APP

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

        19 NO

        21 YES

        22 YES

        20 YES

        23 YES

        21 NO

        22 NO

        24 YES

        23 NO

        24 YES

        25 YES

 SEQUENCE# APP

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

        25 YES

        26 YES

        26 NO

        27 YES

        28 YES

        29 YES

        30 YES

        31 YES

        32 YES

        33 YES

32 rows selected.

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

alter database recover managed standby database disconnect from session

ERROR at line 1:

ORA-01153: an incompatible media recovery is active

SQL> alter database recover managed standby database finished;

alter database recover managed standby database finished

*

ERROR at line 1:

ORA-00274: illegal recovery option FINISHED

Alter database recover managed standby database finish;

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> conn scott/tiger

Connected.

SQL> select count(*) from left;

  COUNT(*)

----------

     58752

至此我们数据库两边是同步的。说明切换是成功的。OK

注意:在我们的数据库要切换或者是关闭的时候,我们最好,让主库的联机在线日志,进行切换。以使其归档,并且让这个归档传输到备用库上去。

八、经常用的命令解释

8.1备用库的启动命令中用到的

Conn / as sysdba

Startup nomount pfile=c:\oracle\ora92\database\initmyoracle.ora;

Alter database mount standby database;

Alter database recover managed standby database disconnect from session; (让数据库处于自动恢复模式)

Alter database recover managed standby database cancel;(让数据库结束恢复)

Alter database open read only; 

Shutdown immediate;(接可以关闭数据库了)

(依照此过程,standby数据库启动到read only状态)

8.2主库的启动和关闭命令

Conn / as sysdba

Startup pfile=’c:\oracle\ora92\database\initmyoracle.ora’;

Shutdown immediate;

8.3备用库切换到主库命令

在主库上执行命令

Conn / as sysdba

Alter system archive log current;

Alter system archive log current;

Alter system archive log current;

Alter system archive log current;多执行几次以让联机日志文件传输过去.

在备库上执行命令 备库切换到主库

Conn / as sysdba

Select database_role,switchover_status from v$database;

Select status from v$instance;

Alter database recover managed standby database disconnect from session;

Select sequence#,applied from v$archived_log;(以确保两边的日志同步.)

Alter database recover managed standby database cancel;

Alter database commit to switchover to primary with session shutdown;

Shutdown immediate;

Startup pfile=c:\oracle\ora92\database\initmyoracle.ora;

8.4主库切换到备用库命令

在主库上执行命令

Conn / as sysdba

Select database_role,switchover_status from v$database;

Select status from v$instance;

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

Shutdown immediate;

Startup pfile=c:\oracle\ora92\database\initmyoracle.ora;

九、监听器及本地服务的配置

9.1 primary服务器的监听

# listener.ora Network Configuration File: /home/DBSoftware/oracle/product/10.2.

0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = NACEC)

      (ORACLE_HOME = /home/DBSoftware/oracle/product/10.2.0/db_1)

      (SID_NAME = NACEC)

    )

  )

LISTENER =

  (DESCRIPTION =

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

  )

9.2 primary服务器的本地服务

# tnsnames.ora Network Configuration File: /home/DBSoftware/oracle/product/10.2.

0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

NACECPRIM =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = NACEC)

    )

  )

NACECSTDB =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = NACEC)

   )

  )

9.3 standby服务器的监听

# listener.ora Network Configuration File: /home/DBSoftware/oracle/product/10.2.

0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = NACEC)

      (ORACLE_HOME = /home/DBSoftware/oracle/product/10.2.0/db_1)

      (SID_NAME = NACEC)

    )

  )

 

LISTENER =

  (DESCRIPTION =

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

  )

9.4 standby服务器的本地服务

# tnsnames.ora Network Configuration File: /home/DBSoftware/oracle/product/10.2.

0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

NACECPRIM =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = NACEC)

    )

  )

NACECSTDB =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = NACEC)

    )

  )

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16978544/viewspace-691863/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-12-08

  • 博文量
    64
  • 访问量
    139691