ORACLE 10g DATA GUARD的配置和建立

最新推荐文章于 2024-06-04 11:17:42 发布

cuanlielian4510

最新推荐文章于 2024-06-04 11:17:42 发布

阅读量143

点赞数

文章标签：数据库网络运维

Vmware 中配置和建立ORACLE 10.2.0.3的physical standby数据库
一、配置条件
1、软件条件
Vmware workstation ORACLE 10.2.0.1
2、硬件条件
RAC2 （200.200.200.238） primary
RAC1 (200.200.200.239) standby
3、服务器配置的监听
二、前提服务的配置
1、在（200.200.200.238）primary上配置数据库软件和数据库SID（myoracle）
2、在（200.200.200.239）standby上配置和primary上ORACLE安装的那些相同的目录
2．1、建立$ORACLE_BASE下的目录一样
由于我们的机器是安装RAC数据库的primary和standby的目录基本上是相同的。
我们为了省事，我们直接的从primary(RAC2)的机器上把/home/oracle/app/admin/myoracle COPY到standby(RAC1)的机器上/home/oracle/app/admin/ 目录下来
[oracle@rac2 admin]$ pwd
/home/oracle/app/admin
[oracle@rac2 admin]$ ls
devdb myoracle
把RAC2机器上的/home/oracle/app/admin/myoracle COPY 到RAC1下的/home/oracle/app/admin/myoracle
[oracle@rac1 admin]$ pwd
/home/oracle/app/admin
[oracle@rac1 admin]$ scp -r rac2:/home/oracle/app/admin/myoracle .
[oracle@rac1 admin]$ ls
devdb myoracle
$ORACLE_BASE的目录就这样建立无全一样了。
2．2、建立$ORACLE_DATA下的目录一样
由于primary(RAC2)的数据文件目录在/home/oracle/app/oradata/myoracle下
[oracle@rac2 admin]$ ls
devdb myoracle
[oracle@rac2 admin]$ cd ..
[oracle@rac2 app]$ ls
admin oradata oraInventory product
[oracle@rac2 app]$ cd oradata
[oracle@rac2 oradata]$ ls
myoracle
[oracle@rac2 oradata]$ pwd
/home/oracle/app/oradata
那么我们就在standby(RAC1)上建立和primary(RAC2)相同的数据文件目录
[oracle@rac1 oradata]$ pwd
/home/oracle/app/oradata
[oracle@rac1 oradata]$ ls
devdb
[oracle@rac1 oradata]$ mkdir myoracle
[oracle@rac1 oradata]$ ls
devdb myoracle
2．3、在primary(RAC2)上建立数据库的归档日志目录和rman备份文件的目录
[oracle@rac2 oradata]$ ls
myoracle
[oracle@rac2 oradata]$ mkdir archive ----/放（RAC2）归档日志的
[oracle@rac2 oradata]$ mkdir backup ----/放（RAC2）的RMAN备份文件
[oracle@rac2 oradata]$ ls
archive backup myoracle
那么我就在（RAC1）standby建立和RAC2相同的目录结构
[oracle@rac1 oradata]$ mkdir archive
[oracle@rac1 oradata]$ mkdir backup
3、在主库上创建pfile参数，并且修改其参数
3．1、修改（RAC2）primary数据库到归档方式
[oracle@rac2 dbs]$ export ORACLE_SID=myoracle
[oracle@rac2 dbs]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Dec 9 16:45:29 2008

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.
3．2、修改（RAC2）primary数据库上的PFILE
SQL> create pfile='$ORACLE_HOME/dbs/initMYORACLE.ora' from spfile;
File created.
我们近来编辑$ORACLE_HOME/dbs/initMYORACLE.ora文件
myoracle.__db_cache_size=75497472
myoracle.__java_pool_size=4194304
myoracle.__large_pool_size=4194304
myoracle.__shared_pool_size=79691776
myoracle.__streams_pool_size=0
*.audit_file_dest='/home/oracle/app/admin/myoracle/adump'
*.background_dump_dest='/home/oracle/app/admin/myoracle/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/home/oracle/app/oradata/myoracle/control01.ctl','/home/oracle/app/oradata/myoracle/control02.ctl','/home/oracle/app/oradata/myoracle/control03.ctl'
*.core_dump_dest='/home/oracle/app/admin/myoracle/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='myoracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=myoracleXDB)'
*.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/oracle/app/admin/myoracle/udump'

###############################################
#####DATA GUARD ---primary database
###############################################
*.db_unique_name='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=/home/oracle/app/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primary'
*.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=standby'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.log_file_name_convert='/home/oracle/app/oradata/archive','/home/oracle/app/oradata/archive'
*.db_file_name_convert='/home/oracle/app/oradata/myoracle','/home/oracle/app/oradata/myoracle'
#*.standby_archive_dest='/home/oracle/app/oradata/archive'
*.standby_file_management='AUTO'
*.fal_server='primary'
*.fal_client='standby'

###############################################
#####DATA GUARD ---primary database参数的解释
###############################################
*.db_unique_name='primary' ####这个是主数据库的网络服务名（NET）
*.log_archive_config='DG_CONFIG=(primary,standby)' ##是主库和备用库的网络服务名
*.log_archive_dest_1='location=/home/oracle/app/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primary' ##本地的日志应该是放在本地的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=standby' ##通过日志传输到备用库db_unique_name=备用库的网络服务名
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.log_file_name_convert='/home/oracle/app/oradata/archive','/home/oracle/app/oradata/archive'
*.db_file_name_convert='/home/oracle/app/oradata/myoracle','/home/oracle/app/oradata/myoracle'
#*.standby_archive_dest='/home/oracle/app/oradata/archive'
*.standby_file_management='AUTO'
*.fal_server='primary' ###主库的网络服务名
*.fal_client='standby' ###备用库的网络服务名
保存，关闭数据库然后用这个参数重新启动数据库。OK。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='$ORACLE_HOME/dbs/initMYORACLE.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/oracle/app/oradata/archive
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
发现参数正确。OK。
4、在备用库上创建pfile参数，并修改其参数
同样的我们要在备用库（RAC1） standby数据库建立initMYORACLE.ora文件，并要编辑他
我们可以从（RAC2）primary上COPE一个initMYORACLE.ora文件到$ORACLE_HOME/dbs/下。
[oracle@rac1 dbs]$ scp rac2:$ORACLE_HOME/dbs/initMYORACLE.ora .
initMYORACLE.ora
100% 1555 1.5KB/s 00:00
现在我们就来修改standby（RAC1）上的initMYORACLE.ora文件，修改如下：
myoracle.__db_cache_size=75497472
myoracle.__java_pool_size=4194304
myoracle.__large_pool_size=4194304
myoracle.__shared_pool_size=79691776
myoracle.__streams_pool_size=0
*.audit_file_dest='/home/oracle/app/admin/myoracle/adump'
*.background_dump_dest='/home/oracle/app/admin/myoracle/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/home/oracle/app/oradata/myoracle/control01.ctl','/home/oracle/app/oradata/myoracle/control02.ctl','/home/oracle/ap
p/oradata/myoracle/control03.ctl'
*.core_dump_dest='/home/oracle/app/admin/myoracle/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='myoracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=myoracleXDB)'
*.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/oracle/app/admin/myoracle/udump'

###############################################
#####DATA GUARD --standby database
###############################################
*.db_unique_name='standby'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=/home/oracle/app/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=standby'
*.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=primary'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.log_file_name_convert='/home/oracle/app/oradata/archive','/home/oracle/app/oradata/archive'
*.db_file_name_convert='/home/oracle/app/oradata/myoracle','/home/oracle/app/oradata/myoracle'
#*.standby_archive_dest='/home/oracle/app/oradata/archive'
*.standby_file_management='AUTO'
*.fal_server='standby'
*.fal_client='primary'

###############################################
#####DATA GUARD --standby database备用库参数的解释
###############################################
*.db_unique_name='standby' ###备用库网络服务名
*.log_archive_config='DG_CONFIG=(primary,standby)' ###主库和备用库的网络服务名
*.log_archive_dest_1='location=/home/oracle/app/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=standby'
###备用库的网络服务名
*.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=primary'
##主库的网络服务名
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.log_file_name_convert='/home/oracle/app/oradata/archive','/home/oracle/app/oradata/archive'
*.db_file_name_convert='/home/oracle/app/oradata/myoracle','/home/oracle/app/oradata/myoracle'
#*.standby_archive_dest='/home/oracle/app/oradata/archive'
*.standby_file_management='AUTO'
*.fal_server='standby' ####备用库的网络服务名
*.fal_client='primary' ####主库的网络服务名
5、在备用库上建立备用库的password文件
我们可以从（RAC2）primary上COPY 密码文件过来
[oracle@rac1 dbs]$ scp rac2:$ORACLE_HOME/dbs/orapwmyoracle .
orapwmyoracle
100% 1536 1.5KB/s 00:00
三、通过rman备份primary数据库，然后恢复到standby数据库上
1、通过RMAN备份primary数据库
在主库上操作（primary）
[oracle@rac2 dbs]$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Dec 11 13:40:54 2008

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

connected to target database: MYORACLE (DBID=1771818397)
为备用库，备份控制文件和全库。
RMAN> run {
2> allocate channel c1 type disk;
3> backup tag='fulldatabase' format '/home/oracle/app/oradata/backup/full_%U_%I.dbf' database;
4> backup current controlfile for standby tag='controlfile' format '/home/oracle/app/oradata/backup/control_%U_%I.ctl';
5> }

released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=143 devtype=DISK

Starting backup at 11-DEC-08
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=/home/oracle/app/oradata/myoracle/system01.dbf
input datafile fno=00003 name=/home/oracle/app/oradata/myoracle/sysaux01.dbf
input datafile fno=00002 name=/home/oracle/app/oradata/myoracle/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/app/oradata/myoracle/users01.dbf
channel c1: starting piece 1 at 11-DEC-08
channel c1: finished piece 1 at 11-DEC-08
piece handle=/home/oracle/app/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 11-DEC-08
channel c1: finished piece 1 at 11-DEC-08
piece handle=/home/oracle/app/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 11-DEC-08

Starting backup at 11-DEC-08
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 11-DEC-08
channel c1: finished piece 1 at 11-DEC-08
piece handle=/home/oracle/app/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 11-DEC-08
released channel: c1
2、挂载主库（RAC2）的/home/oracle/app/oradata/backup/ 为NFS目录，以让（RAC1看见）
在RAC2（primary）上通过root用户编辑/etc/exports 文件，在其中加入一行
/home/oracle/app/oradata/backup *(sync,rw) 然后保存退出
然后在启动portmap服务
Service portmap start
Service nfs start
再在RAC1（STANDBY）的机器上挂载这个目录到相同的目录
以root用户操作
mount -t nfs -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 rac2:/home/oracle/app/oradata/backup /home/oracle/app/oradata/backup
注意 –o 选项，如果有的选项不正确，将到导致/home/oracle/app/oradata/backup在执行rman的时候不好使。
3、恢复standby数据库
○1、在备用库（RAC1）STANDBY上操作
[oracle@rac1 ~]$ export ORACLE_SID=myoracle
[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Dec 11 13:35:52 2008

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initMYORACLE.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=myoracle
[oracle@rac1 ~]$ rman auxiliary sys/stu01@standby target sys/stu01@primary
这里的standby是备用库的网络服务名
这里的primary是主库的网络服务名

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Dec 11 13:57:16 2008

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

connected to target database: MYORACLE (DBID=1771818397)
connected to auxiliary database: MYORACLE (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 11-DEC-08
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 11-DEC-08
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/oracle/app/oradata/backup/control_07k206gl_1_1_1771818397.ctl
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/app/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/oracle/app/oradata/myoracle/control01.ctl
output filename=/home/oracle/app/oradata/myoracle/control02.ctl
output filename=/home/oracle/app/oradata/myoracle/control03.ctl
Finished restore at 11-DEC-08

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/oracle/app/oradata/myoracle/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/home/oracle/app/oradata/myoracle/system01.dbf";
set newname for datafile 2 to
"/home/oracle/app/oradata/myoracle/undotbs01.dbf";
set newname for datafile 3 to
"/home/oracle/app/oradata/myoracle/sysaux01.dbf";
set newname for datafile 4 to
"/home/oracle/app/oradata/myoracle/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/oracle/app/oradata/myoracle/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 11-DEC-08
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/oracle/app/oradata/myoracle/system01.dbf
restoring datafile 00002 to /home/oracle/app/oradata/myoracle/undotbs01.dbf
restoring datafile 00003 to /home/oracle/app/oradata/myoracle/sysaux01.dbf
restoring datafile 00004 to /home/oracle/app/oradata/myoracle/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/app/oradata/backup/full_05k206eg_1_1_1771818397.dbf
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/app/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 11-DEC-08

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/oracle/app/oradata/myoracle/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=6 stamp=673192969 filename=/home/oracle/app/oradata/myoracle/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=7 stamp=673192969 filename=/home/oracle/app/oradata/myoracle/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=8 stamp=673192969 filename=/home/oracle/app/oradata/myoracle/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 11-DEC-08
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /home/oracle/app/oradata/archive/log_1_6_673190306.arc
archive log thread 1 sequence 7 is already on disk as file /home/oracle/app/oradata/archive/log_1_7_673190306.arc
archive log thread 1 sequence 8 is already on disk as file /home/oracle/app/oradata/archive/log_1_8_673190306.arc
archive log filename=/home/oracle/app/oradata/archive/log_1_6_673190306.arc thread=1 sequence=6
archive log filename=/home/oracle/app/oradata/archive/log_1_7_673190306.arc thread=1 sequence=7
archive log filename=/home/oracle/app/oradata/archive/log_1_8_673190306.arc thread=1 sequence=8
media recovery complete, elapsed time: 00:00:04
Finished recover at 11-DEC-08
Finished Duplicate Db at 11-DEC-08
○3、然后在备用库上启动备用库到恢复模式。
[oracle@rac1 ~]$ export ORACLE_SID=myoracle
[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Dec 11 13:35:52 2008

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initMYORACLE.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启动（RAC1）standby备用数据库
[oracle@rac1 ~]$ export ORACLE_SID=myoracle
[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Dec 10 14:33:44 2008

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initMYORACLE.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
--------------------
MYORACLE 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
--------- -------------------- -------------------- -------- ---------------- --------------------
MYORACLE 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
启动（RAC2）primary数据库
[oracle@rac2 ~]$ export ORACLE_SID=myoracle
[oracle@rac2 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Dec 10 14:34:45 2008

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='$ORACLE_HOME/dbs/initMYORACLE.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
--------------------
MYORACLE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE ENABLED PRIMARY
SESSIONS ACTIVE

SQL> set linesize 140
SQL> /

NAME PROTECTION_MODE PROTECTION_LEVEL REMOTE_A DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- -------------------- -------- ---------------- --------------------
MYORACLE 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.
五、做DATAGUARD的PRIMARY和STANDBY的切换
5．1做主库（RAC2）primary到备库（RAC1）standby的切换
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
--------------------
MYORACLE 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/initMYORACLE.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从备库（RAC1）standby到主库（RAC2）primary的切换
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
--------------------
MYORACLE 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/initMYORACLE.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是否成功。
在RAC1（STANDBY）数据库上切换日志，看日志能不能传到RAC2（PRIMARY）数据库上。
在RAC1（STANDBY）上看日志的情况
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.
在RAC2（PRIMARY）上看到日志已经传输过来了
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.
现在我们启动（RAC2）PRIMARY数据库到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/initMYORACLE.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/initMYORACLE.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/initMYORACLE.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。
注意：在我们的数据库要切换或者是关闭的时候，我们最好，让主库的联机在线日志，进行切换。以使其归档，并且让这个归档传输到备用库上去。

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

转载于:http://blog.itpub.net/31448824/viewspace-2138155/