左归丸右归丸的功效:DATA GUARD搭建

——利用RMAN创建physical standby
—执行手工、自动switchover与failover
李娇华
摘要：搭建data guard环境，利用RMAN DUPLICATE命令创建physical standby。 创建备库之前需要有备库的参数文件；同时连接主数据库和备用数据库，启动备库到nomount状态。备库必须工作在recover managed模式下才能应用主库的redo数据，配置broker的命令行管理工具，实现手工、自动switchover与failover，具体创建过程如下：
整体系统结构包含三部分：
1.oracle数据库服务器： 作为主节点，承担用户连接和数据库服务。
2.oracle备用数据库：承担针对主节点数据跟踪、实市时备份责任，在系统灾难情况下直接转移到这台服务器操作，实现几分钟内的灾难恢复。
3.专用网络：连接数据库服务器和备用数据库服务器之间的专用网络，避免公用网络出现故障时主库与备库之间无法通信。
本实验涉及的配置环境：
主、备库：  物理内存1G，本地存储
操作系统Red Hat Enterprise Linux AS release 4
Kernel 2.6.9-42.ELsmp on an i686
数据库软件版本ORACLE  10g release 10.2.0.1.0
---------------------------------------------------
主数据库机器名：linux4
内网IP：192.168.0.100
外网IP：192.168.1.100
---------------------------------------------
备用数据库机器名：linux_sdb
内网IP：192.168.0.200
外网IP：192.168.1.200
----------------------------------------------
用户/口令
操作系统根用户：root
Oracle数据库属主：oracle 主组：oinstall 用户属组：dba
数据库：sys/oracle
System/oracle
Scott/tiger
-----------------------------------------
Oracle环境变量：
Pdb:
PATH=$PATH:$HOME/bin
$ORACLE_BASE=/ora/u01
$ORACLE_HOME=$ORACLE_BASE/product/10.2.0.1/db_1
$ORACLE_SID=db（主库）
PATH=$ORACLE_HOME/bin:/sbin:/$PATH:.
Sdb:
PATH=$PATH:$HOME/bin
$ORACLE_BASE=/ora/u01
$ORACLE_HOME=$ORACLE_BASE/product/10.2.0.1/db_1
$ORACLE_SID=db（备库）
PATH=$ORACLE_HOME/bin:/sbin:/$PATH:.
Export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
数据库结构：
文件类型
主库文件位置
备库文件位置
数据文件
控制文件
参数文件
日志文件
警告文件
用户跟踪文件
后台跟踪文件
闪回区
存档日志
第一步：primary database的创建。
1. 使用OUI安装数据库软件
2. 使用DBCA创建数据库（注意查看数据库文件是否为设计的位置）,全局数据库名：db 实例名：db
3. 以oracle身份登录系统，连接主库，启动到 mount状态，
修改数据库强制记录日志
SQL>alter database force logging;
如果创建数据库时没有选择归档，将数据库改为归档模式
SQL>alter database archivelog;
开启闪回数据库（不是必须的）
SQL> alter database flashback on;
Database altered.
4．根据spfile文件创建pfile，关闭数据库。
5．修改pfile 文件
db.__db_cache_size=134217728
db.__java_pool_size=4194304
db.__large_pool_size=4194304
db.__shared_pool_size=58720256
db.__streams_pool_size=0
*.audit_file_dest='/ora/u01/admin/db/adump'
*.background_dump_dest='/ora/u01/admin/db/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/ora/u01/oradata/pdb/control01.ctl',
'/ora/u01/oradata/db/control02.ctl','/ora/u01/oradata/db/control03.ctl'
*.core_dump_dest='/ora/u01/admin/db/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='db'
*.db_recovery_file_dest='/ora/u01/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='pdb'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=pdbXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=68157440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=205520896
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/ora/u01/admin/db/udump'
='dg_config=(pdb,sdb)'
fal_server=sdb
fal_client=pdb
log_archive_dest_1='location=/ora/u01/flash_recovery_area/DB/archivelog'
log_archive_dest_2='service=sdb lgwr sync affirm
valid_for=(online_logfiles,primary_role)
db_unique_name=sdb'
log_archive_dest_state_1=ENABLE
log_archive_dest_state_2=ENABLE
db_file_name_convert='/ora/u01/oradata/sdb','/ora/u01/oradata/pdb'  ----先远程后本地
log_file_name_convert='/ora/u01/oradata/sdb','/ora/u01/oradata/pdb'----先远程后本地
standby_file_management=AUTO
表中标注为黄色的参数最为重要，特别是标有双色的参数，无论哪个出现错误都可能导致redo log无法应用到备库，
5．采用pfile文件启动数据库。
6．为主数据库创建监听
[oracle@linux4 ~]$ netca
点击next根据提示输入内容，直到完毕。
7．为主数据库创建网络服务命名
[oracle@linux4 ~]$ netmgr
8．编写数据库的hosts文件，路径为/etc/hosts内容如下：
[oracle@linux4 ~]$ vi /etc/hosts
# do not remove the following line,or various programs
# that require network functionality will fall.
127.0.0.1  linuxserv1 localhost.localdomain localhost
192.168.1.100    linux4
192.168.1.200    linux_sdb
注意：（有pdb,sdb网络服务名即可）/etc/init.d/iptables stop两边主机防火墙关闭
否则tnsping不通
第二步：使用RMAN备份数据库
1.创建备份集的存放位置
[oracle@linux4 ~]$ cd /ora/u02
[oracle@linux4 u02]$ mkdir  backup
2.备份当前的控制文件
[oracle@linux4 backup]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 11 17:37:31 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: DB (DBID=1434261387)
RMAN> backup format '/ora/u02/backup/bk_%U' current controlfile for standby;
Starting backup at 11-NOV-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 11-NOV-08
channel ORA_DISK_1: finished piece 1 at 11-NOV-08
piece handle=/ora/u02/backup/bk_0bjves1b_1_1 tag=TAG20081111T173851 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 11-NOV-08
3．备份数据文件和归档文件
RMAN> backup format '/ora/u02/backup/backup_%U' database plus archivelog; ------〉
这里备份的是dbf文件和归档文件(没有日志文件)
Starting backup at 11-NOV-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=670448182
input archive log thread=1 sequence=3 recid=2 stamp=670509782
input archive log thread=1 sequence=4 recid=3 stamp=670512673
input archive log thread=1 sequence=5 recid=4 stamp=670512755
input archive log thread=1 sequence=6 recid=5 stamp=670522358
input archive log thread=1 sequence=7 recid=6 stamp=670524539
input archive log thread=1 sequence=8 recid=7 stamp=670524623
input archive log thread=1 sequence=9 recid=8 stamp=670527555
channel ORA_DISK_1: starting piece 1 at 11-NOV-08
channel ORA_DISK_1: finished piece 1 at 11-NOV-08
piece handle=/ora/u02/backup/backup_0cjves24_1_1 tag=TAG20081111T173916 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 11-NOV-08
Starting backup at 11-NOV-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/ora/u01/oradata/pdb/system01.dbf
input datafile fno=00003 name=/ora/u01/oradata/pdb/sysaux01.dbf
input datafile fno=00005 name=/ora/u01/oradata/pdb/example01.dbf
input datafile fno=00002 name=/ora/u01/oradata/pdb/undotbs01.dbf
input datafile fno=00004 name=/ora/u01/oradata/pdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-NOV-08
channel ORA_DISK_1: finished piece 1 at 11-NOV-08
piece handle=/ora/u02/backup/backup_0djves29_1_1 tag=TAG20081111T173921 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 11-NOV-08
channel ORA_DISK_1: finished piece 1 at 11-NOV-08
piece handle=/ora/u02/backup/backup_0ejves4l_1_1 tag=TAG20081111T173921 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 11-NOV-08
Starting backup at 11-NOV-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=10 recid=9 stamp=670527639
channel ORA_DISK_1: starting piece 1 at 11-NOV-08
channel ORA_DISK_1: finished piece 1 at 11-NOV-08
piece handle=/ora/u02/backup/backup_0fjves4o_1_1 tag=TAG20081111T174039 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 11-NOV-08
--
第三步：physical standby database创建
1.安装数据库软件，本实验选择安装软件的路径与主库相同
2.参考主库创建备库上相应的文件目录（注意目录名及位置不能写错，否则创建过程会失败）
[oracle@linux_sdb~]$ mkdir /ora/u01/admin
[oracle@linux_sdb~]$ cd /ora/u01/admin
[oracle@linux_sdb admin]$ mkdir db
[oracle@linux_sdb sdb]$ mkdir adump
[oracle@linux_sdb sdb]$ mkdir bdump
[oracle@linux_sdb sdb]$ mkdir cdump
[oracle@linux_sdb sdb]$ mkdir udump
[oracle@linux_sdb~]$ cd /ora/u01
[oracle@linux_sdb u01]$ mkdir flash_recovery_area
[oracle@linux_sdb u01]$ cd flash_recovery_area
[oracle@linux_sdb flash_recovery_area]$ mkdir db
[oracle@linux_sdb~]$ cd /ora/u01
[oracle@linux_sdb u01]$ mkdir oradata
[oracle@linux_sdb oradata]$ mkdir db
[oracle@linux_sdb~]$ cd /ora/u02
[oracle@linux_sdb u02]$ mkdir backup  备份集在备库的存放目录
3.拷贝备份集到相应位置（备份过程中FORMAT后面的路径），如果没有拷贝到相应的位置会报错。
[oracle@linux4~]$scp
/ora/u02/backup/*
oracle@192.168.0.200:/ora/u02/backup
oracle@192.168.0.200's password:
backup_0cjves24_1_1                           100%   13MB   6.5MB/s   00:02
backup_0djves29_1_1                           100%  570MB   6.4MB/s   01:29
backup_0ejves4l_1_1                           100% 6944KB   3.4MB/s   00:02
backup_0fjves4o_1_1                           100%   37KB  37.0KB/s   00:00
bk_0bjves1b_1_1                               100% 6944KB   6.8MB/s   00:01
[oracle@linux4~]$scp
/ora/u01/product/10.2.0.1/db_1/dbs/initdb.ora    ----〉注意口令文件名称
oracle@192.168.0.200:/ora/u01/product/10.2.0.1/db_1/dbs/initdb.ora
[oracle@linux4~]$scp
/ora/u01/product/10.2.0.1/db_1/dbs/orapwdb
oracle@192.168.0.200:/ora/u01/product/10.2.0.1/db_1/dbs/orapwdb
3.修改参数文件initsdb.ora
db.__db_cache_size=134217728
db.__java_pool_size=4194304
db.__large_pool_size=4194304
db.__shared_pool_size=58720256
db.__streams_pool_size=0
*.audit_file_dest='/ora/u01/admin/db/adump'
*.background_dump_dest='/ora/u01/admin/db/bdump'
*.compatible='10.2.0.1.0'------〉注意版本，两台机器oracle版本不一样，实例起不来，也无法作duplicate
*.control_files='/ora/u01/oradata/db/control01.ctl',
'/ora/u01/oradata/db/control02.ctl','/ora/u01/oradata/db/control03.ctl'
*.core_dump_dest='/ora/u01/admin/db/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='db'
*.db_recovery_file_dest='/ora/u01/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='sdb'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sdbXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=68157440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=205520896
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/ora/u01/admin/db/udump'
log_archive_config='dg_config=(pdb,sdb)'
fal_server=pdb
fal_client=sdb
log_archive_dest_1='location=/ora/u01/flash_recovery_area/db/archivelog'
log_archive_dest_2='service=pdb lgwr sync affirm
valid_for=(online_logfiles,primary_role)
db_unique_name=pdb'
log_archive_dest_state_1=ENABLE
log_archive_dest_state_2=ENABLE
db_file_name_convert='/ora/u01/oradata/pdb','/ora/u01/oradata/[k1] '
log_file_name_convert='/ora/u01/oradata/pdb','/ora/u01/oradata/sdb'
standby_file_management=AUTO
具体修点为黄色部分
4. 为备库创建监听（方法同主库）
5. 为备库创建网络服务命名（方法同主库）
6．编写备库的hosts文件，路径为/etc/hosts（内容与主库的相同）
4．使用initsdb.ora启动数据库到nomount状态。
5. 启动rman使用 rman duplicate 创建备用数据库
[oracle@linux_sdb ~]$ rman target sys/oracle@pdb auxiliary /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 11 18:01:00 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: DB (DBID=1434261387)
connected to auxiliary database: DB (not mounted)
RMAN>[k2]
Starting Duplicate Db at 11-NOV-08
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK
contents of Memory Script:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting restore at 11-NOV-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 /ora/u02/backup/bk_0bjves1b_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/ora/u02/backup/bk_0bjves1b_1_1 tag=TAG20081111T173851
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/ora/u01/oradata/sdb/control01.ctl
output filename=/ora/u01/oradata/sdb/control02.ctl
output filename=/ora/u01/oradata/sdb/control03.ctl
Finished restore at 11-NOV-08
sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
contents of Memory Script:
{
set newname for tempfile  1 to
"/ora/u01/oradata/sdb/temp01.dbf";
switch clone tempfile all;
set newname for datafile  1 to
"/ora/u01/oradata/sdb/system01.dbf";
set newname for datafile  2 to
"/ora/u01/oradata/sdb/undotbs01.dbf";
set newname for datafile  3 to
"/ora/u01/oradata/sdb/sysaux01.dbf";
set newname for datafile  4 to
"/ora/u01/oradata/sdb/users01.dbf";
set newname for datafile  5 to
"/ora/u01/oradata/sdb/example01.dbf";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /ora/u01/oradata/sdb/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 11-NOV-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 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 /ora/u01/oradata/sdb/system01.dbf
restoring datafile 00002 to /ora/u01/oradata/sdb/undotbs01.dbf
restoring datafile 00003 to /ora/u01/oradata/sdb/sysaux01.dbf
restoring datafile 00004 to /ora/u01/oradata/sdb/users01.dbf
restoring datafile 00005 to /ora/u01/oradata/sdb/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /ora/u02/backup/backup_0djves29_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/ora/u02/backup/backup_0djves29_1_1 tag=TAG20081111T173921
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 11-NOV-08
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=7 stamp=670529070 filename=/ora/u01/oradata/sdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=8 stamp=670529070 filename=/ora/u01/oradata/sdb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=9 stamp=670529070 filename=/ora/u01/oradata/sdb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=10 stamp=670529070 filename=/ora/u01/oradata/sdb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=11 stamp=670529070 filename=/ora/u01/oradata/sdb/example01.dbf
Finished Duplicate Db at 11-NOV-08
注：仔细观看屏幕的输出信息，以确定如果不成功错误出现在哪里。
备库已经启动到了mount状态
6．启动主备库监听：
[oracle@linux4 ~]$  lsnrclt start
[oracle@linux_sdb ~]$  lsnrclt start
开启闪回数据库（不是必须的）
SQL> alter database flashback on;
Database altered.
7．修改数据库为恢复管理模式，以使备库能够应用主库的redo 数据，达到同步。
SQL> alter database recover managed standby database disconnect from session;
或者 alter database recover managed standby database using current logfie disconnect from session
8．检查主库归档目标状态：
SQL> col error format a20
SQL> col archive_dest format a30
SQL> select dest_id,status,destination,error from v$archive_dest where dest_id<=5;
DEST_ID STATUS
---------- ---------
DESTINATION
--------------------------------------------------------------------------------ERROR
--------------------
1 VALID
/ora/u01/flash_recovery_area/PDB/archivelog
2 VALID
sdb
DEST_ID STATUS
---------- ---------
DESTINATION
--------------------------------------------------------------------------------ERROR
--------------------
3 INACTIVE
如果出现下面的信息；
SQL> col error format a20
SQL> col archive_dest format a30
SQL> select dest_id,status,destination,error from v$archive_dest where dest_id<=5;
DEST_ID STATUS
---------- ---------
DESTINATION
--------------------------------------------------------------------------------ERROR
--------------------
1 VALID
/ora/u01/flash_recovery_area/PDB/archivelog
2 ERROR
sdb
ORA-01031:
DEST_ID STATUS
---------- ---------
DESTINATION
--------------------------------------------------------------------------------ERROR
--------------------
insufficient
privileges
上述错误出现因为口令文件未创建
9．查看备用数据库的日志应用情况：
SQL>  select sequence#,first_time,next_time,applied from v$archived_log order by   sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APP
---------- --------- --------- ---
13 11-NOV-08 11-NOV-08 YES
14 11-NOV-08 12-NOV-08 YES
15 12-NOV-08 12-NOV-08 YES
16 12-NOV-08 12-NOV-08 YES
17 12-NOV-08 12-NOV-08 YES
18 12-NOV-08 12-NOV-08 YES
19 12-NOV-08 12-NOV-08 YES
20 12-NOV-08 12-NOV-08 YES
21 12-NOV-08 14-NOV-08 YES
22 14-NOV-08 14-NOV-08 YES
23 14-NOV-08 14-NOV-08 YES ----yes代表日志已经应用，也就是同步了。如果为no,表示日至已经传输到备库了，但是还没有应用到备库
10．主数据库切换日志：
SQL>alter system switch logfile;
11．再次查看备库日志应用情况
SQL> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APP
---------- --------- --------- ---
13 11-NOV-08 11-NOV-08 YES
14 11-NOV-08 12-NOV-08 YES
15 12-NOV-08 12-NOV-08 YES
16 12-NOV-08 12-NOV-08 YES
17 12-NOV-08 12-NOV-08 YES
18 12-NOV-08 12-NOV-08 YES
19 12-NOV-08 12-NOV-08 YES
20 12-NOV-08 12-NOV-08 YES
21 12-NOV-08 14-NOV-08 YES
22 14-NOV-08 14-NOV-08 YES
23 14-NOV-08 14-NOV-08 YES
SEQUENCE# FIRST_TIM NEXT_TIME APP
---------- --------- --------- ---
24 14-NOV-08 14-NOV-08 YES
12．为两数据库分别创建standby日志文件
主库
SQL> alter database add standby logfile thread 1 '/ora/u01/oradata/pdb/standbylog01.log' size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 '/ora/u01/oradata/pdb/standbylog02.log' size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 '/ora/u01/oradata/pdb/standbylog03.log' size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 '/ora/u01/oradata/pdb/standbylog04.log' size 50m;
Database altered.
备库
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database add standby logfile thread 1 '/ora/u01/oradata/sdb/standbylog01.log' size 50m;
Database altered.
SQL>  alter database add standby logfile thread 1 '/ora/u01/oradata/sdb/standbylog02.log' size 50m;
Database altered.
SQL>  alter database add standby logfile thread 1 '/ora/u01/oradata/sdb/standbylog03.log' size 50m;
Database altered.
SQL>  alter database add standby logfile thread 1 '/ora/u01/oradata/sdb/standbylog04.log' size 50m;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered
13．关闭主库，重新打开到mount状态，查看此时主库的保护模式及保护级别：
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE     UNPROTECTED
14．修改保护模式为最大可用
SQL> alter database set standby database to maximize availability;
Database altered.
15．打开主库查看保护模式与级别
SQL>alter database open;
Database altered.
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
16．在备库查看保护模式与级别
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
17．为主备数据库分别创建spfile文件
SQL> create spfile from pfile;
File created.
注意：ORA-03113: end-of-file on communication channel
SQL> startup mount
ORACLE instance started.
Total System Global Area  603979776 bytes
Fixed Size                  1220820 bytes
Variable Size             176164652 bytes
Database Buffers          423624704 bytes
Redo Buffers                2969600 bytes
Database mounted.
SQL> alter database set standby database  to maximize PERFORMANCE;
Database altered.
SQL> alter database open;
第四步：双机远程灾备数据库系统状态检查
1. 主备库监听状态
[oracle@linux4 ~]$  lsnrclt status
[oracle@linux_sdb ~]$  lsnrclt status
2.如未启动可用
[oracle@linux4 ~]$  lsnrclt start
[oracle@linux_sdb ~]$  lsnrclt start
3.查看主数据库归档目标的状态
4.查看备库日志应用情况
5.查看备库是否存在日志gap
SQL> select * from v$archive_gap;
no rows selected
6.查看备库警告文件
7.使用scott用户连接主库创建表t1插入一条数据
SQL> connect scott/tiger@pdb
Connected.
SQL> create table t1 (a1 number(10),a2 varchar2(10)) tablespace users;
Table created.
SQL> insert into t1 values(1 ,'fg');
1 row created.
SQL> commit;
Commit complete.
8.主库切换日志
9.如果备库此时处于恢复管理状态，则将备库以只读模式打开，查看刚才创建的t1表的内容
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> connect scott/tiger@sdb
Connected.
SQL> select * from t1;
A1 A2
---------- ----------
1 fg
10.将备库还原为恢复管理模式
SQL> connect / as sysdba;
Connected.
SQL>  alter database recover managed standby database disconnect from session;
Database altered.
第五步：主备节点互换(switchover)
1.查看主数据库可转换的状态
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
2.执行切换命令转换linux4 primary 到 physical standby
SQL>alter database commit to switchover to physical standby with session shutdown
Database altered.
如果查询到的可转换状态为to standby，则切换命令为
SQL>alter database commit to switchover to physical standby
3.关闭数据库并启动到mount状态
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> shutdown immeidate;
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  205520896 bytes
Fixed Size                  1218532 bytes
Variable Size              71305244 bytes
Database Buffers          130023424 bytes
Redo Buffers                2973696 bytes
Database mounted.
4．查看主数据库可转换的状态
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
此时原主库已转换为新备库
5. 查看备数据库可转换的状态
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
6.将备库linux_sdb 转换为primary
SQL> alter database commit to switchover to primary;
Database altered.
7．查看备数据库可转换的状态并打开数据库
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL>shutdown immediate;
SQL> startup ;
ORACLE instance started.
Total System Global Area  205520896 bytes
Fixed Size                  1218532 bytes
Variable Size              67110940 bytes
Database Buffers          134217728 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
注：在打开新的主数据库时，如果新的主数据库在作为备用库时曾以READ ONLY的方式打开过，则应重启数据库，否则可直接执行如下操作
SQL> alter database open;
Database altered.
8．Linux4作为新备库启动，设置恢复管理模式
SQL> alter database recover managed standby database disconnect from session;
Database altered.
9．查看新备库pdb的日志应用情况
第六步：灾难发生时将备用节点升级为主节点（failover）
1.对备用数据库进行失败转移初始化
SQL> alter database recover managed standby database finish force;
Database altered.
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE
当进行失败转移后，数据库的保护模式将由最大可用模式变为最大性能模式
2.将备库转换为主库并打开新的主库
SQL> alter database commit to switchover to primary;
Database altered.
SQL> alter database open;
Database altered.
Failover操作之后原来的主库将不能作为新备库使用必须重新创建。
二、broker的配置与使用
第一步：配置
1. Prerequisites:
1)必须采用SPFILE文件启动数据库
2)DG_BROKER_START=TRUE(所有参与broker管理的服务器），可以使用如下语句修改：
alter system set dg_broker_start=true scope=both;
3)确保COMPATIBE的值在主备数据库上相同。如果此值不同可能就不能从主库传输redo 到备库。
2. 配置：
1）Invoke DGMGRL.
[oracle@linux4 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
2）连接主库
DGMGRL> connect sys/oracle （也可以使用 DGMGRL> connect sys/oracle@pdb ）
DGMGRL> show configuration;
Error: ORA-16532: Data Guard broker configuration does not exist 显示此错误因为还没有进行配置
3）创建 broker配置.
DGMGRL> create configuration 'DRsolution' as primary database is 'pdb' connect
> identifier is 'pdb';
Configuration "DRsolution" created with primary database "pdb"
4）添加备库到配置.
DGMGRL> add database 'sdb' as connect identifier is sdb maintained as physical;
Database "sdb" added
5）显示配置信息.
DGMGRL> show configuration;
Configuration
Name:                DRsolution
Enabled:             NO
Protection Mode:     MaxAvailability
Fast-Start Failover: DISABLED
Databases:
pdb - Primary database
sdb - Physical standby database
Current status for "DRsolution":
DISABLED
6）使配置及数据库生效
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration
Name:                DRsolution
Enabled:             YES
Protection Mode:     MaxAvailability
Fast-Start Failover: DISABLED
Databases:
pdb - Primary database
sdb - Physical standby database
Current status for "DRsolution":
SUCCESS
===================================
DGMGRL> ENABLE DATABASE 'pdb';
Enabled.
DGMGRL> SHOW DATABASE 'pdb';
Database
Name:            pdb
Role:            PHYSICAL STANDB
Enabled:         YES
Intended State:  ONLINE
Instance(s):
DR_Sales1
Current status for "pdb":
SUCCESS
7) 设置配置的保护模式[F3]
DGMGRL> edit database 'pdb' set property 'logxptmode'='sync';
Property "logxptmode" updated
DGMGRL> edit database 'sdb' set property 'logxptmode'='sync';
Property "logxptmode" updated
8) 指定 FastStartFailoverTarget 值.
DGMGRL> edit database 'pdb' set property faststartfailovertarget='sdb';
Property "faststartfailovertarget" updated
DGMGRL> edit database 'sdb' set property faststartfailovertarget='pdb';
Property "faststartfailovertarget" updated
9) 在主备库开启闪回数据库功能,
ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;
ALTER SYSTEM SET UNDO_MANAGEMENT=’AUTO’ SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
SHOW PARAMETER UNDO;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4200 SCOPE=BOTH;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;[F4]
9) 使 Fast-Start Failover 生效并开启 Observer
DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.
DGMGRL> start observer
Observer started
10）验证 fast-start failover 配置.
DGMGRL> show configuration;
Configuration
Name:                DRsolution
Enabled:             YES
Protection Mode:     MaxAvailability
Fast-Start Failover: ENABLED
Databases:
pdb - Primary database
sdb - Physical standby database
- Fast-Start Failover target
Current status for "DRsolution":
SUCCESS
DGMGRL> show configuration verbose;
Configuration
Name:                DRsolution
Enabled:             YES
Protection Mode:     MaxAvailability
Fast-Start Failover: ENABLED
Databases:
pdb - Primary database
sdb - Physical standby database
- Fast-Start Failover target
Fast-Start Failover
Threshold: 30 seconds
Observer:  linux4
Current status for "DRsolution":
SUCCESS
第二步：主备节点互换（switchover）
1.检查主库的状态、健康和属性
DGMGRL> show database verbose pdb;
Database
Name:            pdb
Role:            PRIMARY
Enabled:         YES
Intended State:  ONLINE
Instance(s):
pdb
Properties:
InitialConnectIdentifier        = 'pdb'
LogXptMode                      = 'sync'
Dependency                      = ''
DelayMins                       = '0'
Binding                         = 'OPTIONAL'
MaxFailure                      = '0'
MaxConnections                  = '1'
ReopenSecs                      = '300'
NetTimeout                      = '180'
LogShipping                     = 'ON'
PreferredApplyInstance          = ''
ApplyInstanceTimeout            = '0'
ApplyParallel                   = 'AUTO'
StandbyFileManagement           = 'AUTO'
ArchiveLagTarget                = '0'
LogArchiveMaxProcesses          = '2'
LogArchiveMinSucceedDest        = '1'
DbFileNameConvert               = '/ora/u01/oradata/sdb, /ora/u01/oradata/pd b'
LogFileNameConvert              = '/ora/u01/oradata/sdb, /ora/u01/oradata/pd b'
FastStartFailoverTarget         = 'sdb'
StatusReport                    = '(monitor)'
InconsistentProperties          = '(monitor)'
InconsistentLogXptProps         = '(monitor)'
SendQEntries                    = '(monitor)'
LogXptStatus                    = '(monitor)'
RecvQEntries                    = '(monitor)'
HostName                        = 'linux4'
SidName                         = 'pdb'
LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=linux4)(PORT =1521))'
StandbyArchiveLocation          = '/ora/u01/flash_recovery_area/PDB/archivel og'
AlternateLocation               = ''
LogArchiveTrace                 = '0'
LogArchiveFormat                = '%t_%s_%r.dbf'
LatestLog                       = '(monitor)'
TopWaitEvents                   = '(monitor)'
Current status for "pdb":
SUCCESS
2.检查备库的状态、健康和属性
DGMGRL> show database verbose sdb;
Database
Name:            sdb
Role:            PHYSICAL STANDBY
Enabled:         YES
Intended State:  ONLINE
Instance(s):
sdb
Properties:
InitialConnectIdentifier        = 'sdb'
LogXptMode                      = 'sync'
Dependency                      = ''
DelayMins                       = '0'
Binding                         = 'OPTIONAL'
MaxFailure                      = '0'
MaxConnections                  = '1'
ReopenSecs                      = '300'
NetTimeout                      = '180'
LogShipping                     = 'ON'
PreferredApplyInstance          = ''
ApplyInstanceTimeout            = '0'
ApplyParallel                   = 'AUTO'
StandbyFileManagement           = 'AUTO'
ArchiveLagTarget                = '0'
LogArchiveMaxProcesses          = '2'
LogArchiveMinSucceedDest        = '1'
DbFileNameConvert               = '/ora/u01/oradata/pdb, /ora/u01/oradata/sdb'
LogFileNameConvert              = '/ora/u01/oradata/pdb, /ora/u01/oradata/sdb'
FastStartFailoverTarget         = 'pdb'
StatusReport                    = '(monitor)'
InconsistentProperties          = '(monitor)'
InconsistentLogXptProps         = '(monitor)'
SendQEntries                    = '(monitor)'
LogXptStatus                    = '(monitor)'
RecvQEntries                    = '(monitor)'
HostName                        = 'linux_sdb'
SidName                         = 'sdb'
LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=linux_sdb)(PORT=1521))'
StandbyArchiveLocation          = '/ora/u01/flash_recovery_area/sdb/archivelog'
AlternateLocation               = ''
LogArchiveTrace                 = '0'
LogArchiveFormat                = '%t_%s_%r.dbf'
LatestLog                       = '(monitor)'
TopWaitEvents                   = '(monitor)'
Current status for "sdb":
SUCCESS
3．执行切换命令（开启一个新broker命令行管理窗口，注意不要关闭broker 的 observer窗口）
[oracle@linux4 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle （也可以使用 DGMGRL> connect sys/oracle@pdb ）
DGMGRL> switchover to sdb;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "pdb" on database "pdb"
Shutting down instance "pdb"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "sdb" on database "sdb"
Shutting down instance "sdb"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "pdb" on database "pdb"
Starting instance "pdb"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "sdb" on database "sdb"
Starting instance "sdb"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "sdb"
如过出现如下信息
DGMGRL> switchover to sdb;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "pdb" on database "pdb"
Shutting down instance "pdb"...
ORA-03113: end-of-file on communication channel
Unable to shut down instance "pdb"
You must shut down instance "pdb" manually
Operation requires shutdown of instance "sdb" on database "sdb"
You must shut down instance "sdb" manually
Operation requires startup of instance "pdb" on database "pdb"
You must start instance "pdb" manually
Operation requires startup of instance "sdb" on database "sdb"
You must start instance "sdb" manually
Switchover succeeded, new primary is "sdb"
是因为GLOBAL_DBNAME参数未修改,打开主备库上的文件
/ora/u01/product/10.2.0.1/db_1/network/listener.ora
分别修改为
主库
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /ora/u01/product/10.2.0.1/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = pdb_dgmgrl)
(ORACLE_HOME = /ora/u01/product/10.2.0.1/db_1)
(SID_NAME = pdb)
)
)
备库
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /ora/u01/product/10.2.0.1/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = sdb_dgmgrl)
(ORACLE_HOME = /ora/u01/product/10.2.0.1/db_1)
(SID_NAME = sdb)
)
)
分别重新启动主备的监听，重新执行切换命令。
4．查看两数据库的角色
SQL> select database_role from v$database;
select database_role from v$database
*
ERROR at line 1:
ORA-03135: connection lost contact
注意：执行完主、备切换后数据库的连接有可能会超时，此时需要重新在连接数据库（主、备）
SQL> connect sys/oracle as sysdba;
Connected.
DGMGRL> show configuration;
Configuration
Name:                DRsolution
Enabled:             YES
Protection Mode:     MaxAvailability
Fast-Start Failover: ENABLED
Databases:
pdb - Physical standby database
- Fast-Start Failover target
sdb - Primary database
Current status for "DRsolution":
SUCCESS
执行主、备数据库切换操作-----可以在主库、也可以在备库
第三步：执行手工failover操作
DGMGRL> connect sys/oracle （也可以使用 DGMGRL> connect sys/oracle@pdb ）
如果备用数据没有以READ ONLY方式打开过，那么屏幕输出信息为
DGMGRL> failover to pdb;
Performing failover NOW, please wait...
Failover succeeded, new primary is "pdb"
如果备用数据有以READ ONLY方式打开过，那么屏幕输出信息为
DGMGRL> failover to pdb;
Performing failover NOW. Please wait...
Operation requires shutdown of instance "pdb " on database
"pdb".
Shutting down instance "pdb"...
database not mounted
ORACLE instance shut down.
Operation requires startup of instance "pdb" on database "pdb".
Starting instance "pdb"...
ORACLE instance started.
Database mounted.
Failover succeeded. New primary is "pdb"
第四步：自动failover
关闭主库pdb，执行如下命令
SQL> shutdown abort;
ORACLE instance shut down.
在observer的监视窗口看到
01:07:17.99  Wednesday, November 19, 2008
Initiating fast-start failover to database "sdb"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "sdb"
01:07:25.01  Wednesday, November 19, 2008
在原备库sdb上查看数据库角色
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
产生自动failover 的条件：
1.主库宕机：Shutdown abort
shutdown (NORMAL, IMMEDIATE, TRANSACTIONAL)这些选项不可以；
2.网络终断：Broken network connection between the observer and the primary database；
3.Instance failures；
4.Offline datafiles；
不能产生failover 情况：
1．Fast-start failover is no longer enabled；
2．The observer cannot connect to the target standby database；
总结：
1.在实验过程中注意备用数据库的建立;
2.主要参数值的设定如:
DB_NAME(对于primary database与physical standby databse组合机制, 两数据库的DB_NAME必须相同)
DB_UNIQUE_NAME(对于primary database与physical standby databse组合机制, 两数据库的DB_UNIQUE_NAM必须不相同)
LOG_ARCHIVE_CONFIG、LOG_ARCHIVE_DEST_1、FAL_SERVER、FAL_CLIENT。
LOG_ARCHIVE_DEST_2（注意在配置了broker 后此参数值会发生改变）
3．理解数据库在三种不同打开模式（read-only、 read-write、 recover managed）下情况，本实验主要是创建过程，未详细描述。
4．Failover之后，原primary数据库默认不再是data guard配置的一部分。多数情况下，其他不直接参与failover过程的逻辑/物理standby 数据库，不需要做任何操作。
5．数据库三种保护模式下LOG_ARCHIVE_DEST_n参数应该设置的属性：
最大保护
最高可能用
最高性能
REDO写进程
LGWR
LGWR
LGWR或ARCH
网络传输模式
SYNC
SYNC
LGWR进程时SYNC或ASYNC，ARCH进程时SYNC
磁盘写操作
AFFIRM
AFFIRM
AFFIRM或NOAFFIRM
是否需要standby redologs
YES
YES
可没有但推荐有
6．转换primary数据库与standby数据库：switchover可以确保不会丢失数据；在最大保护模式或最高可用性模式下，failover可以保证不会丢失数据。
[k1]如果2台主机文件路径不同
如果主备目录不同
duplicate target database for standby ;
如果文件目录相同，必须指定
nofilenamecheck
为主备添加standby redo
主：
SQL> alter database add standby logfile thread 1 '/soft/oracle/oradata/db/standbylog01.log' size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 '/soft/oracle/oradata/db/standbylog02.log' size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 '/soft/oracle/oradata/db/standbylog3.log' size 50m;
Database altered.
备：
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database add standby logfile thread 1 '/soft/oracle/oradata/db/standbylog01.log' size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 '/soft/oracle/oradata/db/standbylog02.log' size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 '/soft/oracle/oradata/db/standbylog3.log' size 50m;
Database altered.
SQL>  alter database recover managed standby database disconnect from session;
Database altered.
备库：
SQL> alter  system set undo_management='AUTO' scope=spfile;
System altered.
SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer    3600
undo_tablespace                      string      UNDOTBS1
SQL> alter system set db_flashback_retention_target=4200 scope=both;
System altered.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database flashback on;
Database altered.