接上一篇oracle dataguard概念篇，現在進行下詳細的配置，此次配置的是單執行個體到單執行個體，也就是說主庫和備庫都是基于檔案系統的單執行個體。
主機環境：Red Hat Enterprise Linux Server release 6.3
Rdbms版本：11.2.0.3
其他詳細資訊如下所示：
需要注意的是，兩邊的/etc/hosts檔案保證都包含對方的ip對應關系
oracle安裝的目錄是相同的
編輯/etc/hosts，分别添加以下内容
192.168.1.241 db1
192.168.1.242 dg
主庫資料檔案位置/oradata/beijing/map 歸檔位置 /oradata/beijing/arch
備庫資料檔案位置/oradata/shanghai/map 歸檔位置 /oradata/shanghai/arch
本次配置的是11G 的active data guard，主要分為兩大部分：主庫操作和備庫操作，為了避免操作出現混亂，現列出以下幾個步驟：
主庫操作
1.打開Forced Logging
2.配置主庫參數檔案
3.打開主庫歸檔
4.建立主庫備份用來作為備庫恢複
5.建立standby資料庫的控制檔案
6.建立配置備庫參數檔案
7.拷貝所需檔案到備庫
備庫操作
1.設定備庫環境變量
1.1密碼檔案拷貝
1.2監聽配置
1.3建立網絡服務名
2.建立所需的目錄
3.參數檔案配置
4.恢複備庫
4.1恢複參數檔案
4.2恢複控制檔案
4.3恢複資料庫
5.建立standby redo
5.1主庫建立
5.2備庫建立
6.網絡服務名測試
6.1主庫測試
6.2備庫測試
7.開啟實時應用
8.測試
9.啟動關閉data guard
[oracle@db1 ~]$ mkdir /home/oracle/standby
[oracle@db1 ~]$ mkdir /oradata/beijing/arch
[oracle@db1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 14 14:19:10 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database force logging ;
Database altered. 這個語句可能會執行較長時間，需要等待所有未寫到日志中的日志操作完成。
log_archive_dest_n參數的配置參數詳解看下面的連接配接
log_archive_dest_n詳解
SQL> create pfile='/tmp/a.txt' from spfile ;
File created.
[oracle@db1 ~]$ cat /tmp/a.txt
*.audit_file_dest='/oracle/app/oracle/admin/map/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/beijing/map/control01.ctl','/oradata/beijing/map/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='map'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mapXDB)'
*.memory_target=629145600
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name=beijing
*.log_archive_config='dg_config=(beijing,shanghai)'
*.log_archive_dest_1='location=/oradata/beijing/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=beijing'
*.log_archive_dest_2='service=shanghai LGWR AFFIRM ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=shanghai'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.remote_login_passwordfile=EXCLUSIVE
*.log_archive_format=%t_%s_%r.arc
*.log_archive_max_processes=30
*.fal_server=shanghai
*.standby_file_management=auto
*.db_file_name_convert='/oradata/shanghai/map','/oradata/beijing/map'
*.log_file_name_convert='/oradata/shanghai/map','/oradata/beijing/map' 儲存退出
要注意的是對于db_file_name_convert和log_file_name_convert參數配置需要注意的是，如果兩台伺服器db1和db2組成dg環境，db1是主庫，db2是備庫，應該在db1伺服器的參數檔案中配置從db2伺服器轉換為db1伺服器的目錄，在db2伺服器中配置從伺服器db1轉換到伺服器db2的目錄。切記
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile='/tmp/a.txt' ;
File created.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 373294424 bytes
Database Buffers 243269632 bytes
Redo Buffers 7532544 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> [oracle@db1 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 14 16:06:48 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MAP (DBID=1528459940)
RMAN> run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup filesperset 2 database format '/home/oracle/standby/full_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '/home/oracle/standby/arch_%d_%T_%s_%p';
}2> 3> 4> 5> 6> 7> 8> 9>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=32 device type=DISK
allocated channel: c2
channel c2: SID=157 device type=DISK
Starting backup at 14-OCT-15
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/beijing/map/system01.dbf
input datafile file number=00004 name=/oradata/beijing/map/users01.dbf
channel c1: starting piece 1 at 14-OCT-15
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/oradata/beijing/map/sysaux01.dbf
input datafile file number=00003 name=/oradata/beijing/map/undotbs01.dbf
channel c2: starting piece 1 at 14-OCT-15
channel c2: finished piece 1 at 14-OCT-15
piece handle=/home/oracle/standby/full_MAP_20151014_2_1 tag=TAG20151014T160708 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:26
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
including current control file in backup set
channel c2: starting piece 1 at 14-OCT-15
channel c1: finished piece 1 at 14-OCT-15
piece handle=/home/oracle/standby/full_MAP_20151014_1_1 tag=TAG20151014T160708 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:30
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 14-OCT-15
channel c2: finished piece 1 at 14-OCT-15
piece handle=/home/oracle/standby/full_MAP_20151014_3_1 tag=TAG20151014T160708 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c1: finished piece 1 at 14-OCT-15
piece handle=/home/oracle/standby/full_MAP_20151014_4_1 tag=TAG20151014T160708 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-OCT-15
sql statement: alter system archive log current
sql statement: alter system archive log current
sql statement: alter system archive log current
Starting backup at 14-OCT-15
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=893088283
channel c1: starting piece 1 at 14-OCT-15
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=2 STAMP=893088284
input archived log thread=1 sequence=5 RECID=3 STAMP=893088459
input archived log thread=1 sequence=6 RECID=4 STAMP=893088459
channel c2: starting piece 1 at 14-OCT-15
channel c1: finished piece 1 at 14-OCT-15
piece handle=/home/oracle/standby/arch_MAP_20151014_5_1 tag=TAG20151014T160742 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:00
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=5 STAMP=893088462
input archived log thread=1 sequence=8 RECID=6 STAMP=893088462
channel c1: starting piece 1 at 14-OCT-15
channel c2: finished piece 1 at 14-OCT-15
piece handle=/home/oracle/standby/arch_MAP_20151014_6_1 tag=TAG20151014T160742 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:00
channel c1: finished piece 1 at 14-OCT-15
piece handle=/home/oracle/standby/arch_MAP_20151014_7_1 tag=TAG20151014T160742 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-OCT-15
released channel: c1
released channel: c2
RMAN> SQL> alter database create standby controlfile as '/home/oracle/standby/standby.ctl';
Database altered.
SQL> SQL> create pfile='/home/oracle/standby.txt' from spfile ;
File created.
SQL> [oracle@db1 ~]$ scp -r /home/oracle/standby/ dg:/home/oracle
oracle@dg's password:
a.txt 100% 1136 1.1KB/s 00:00
arch_MAP_20151014_6_1 100% 139KB 138.5KB/s 00:00
full_MAP_20151014_4_1 100% 96KB 96.0KB/s 00:00
full_MAP_20151014_2_1 100% 374MB 74.9MB/s 00:05
standby.ctl 100% 9520KB 9.3MB/s 00:00
arch_MAP_20151014_7_1 100% 4608 4.5KB/s 00:00
full_MAP_20151014_1_1 100% 597MB 39.8MB/s 00:15
full_MAP_20151014_3_1 100% 9568KB 9.3MB/s 00:00
arch_MAP_20151014_5_1 100% 161KB 161.0KB/s 00:00
[oracle@db1 ~]$ 1.設定備庫環境
[oracle@dg ~]$ scp db1:/oracle/app/oracle/product/11.2/db_1/dbs/orapwmap /oracle/app/oracle/product/11.2/db_1/dbs/orapwmap
oracle@db1's password:
orapwmap 100% 1536 1.5KB/s 00:00
[oracle@dg ~]$ 可以使用netca進行配置，或者使用nemgr
[oracle@dg ~]$ lsnrctl stop
[oracle@dg ~]$ lsnrctl start 要是rac或者grid管理的，配置是修改grid使用者下的grid_home/network/admin/listener.ora檔案，該檔案中指定的oracle_home是oracle使用者的，而不是grid使用者的oracle_home.
rac環境使用工具配置隻需在一個節點操作，會自動同步到其他節點
可以使用配置工具netmgr配置，此次直接複制以下檔案到指定位置
[oracle@dg ~]$ cd $ORACLE_BASE/diag
[oracle@dg admin]$ cat tnsnames.ora
SHANGHAI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.242)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = shanghai)
)
)
BEIJING =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.241)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = beijing)
)
) 拷貝相同的檔案内容到主庫的tnsnames.ora中
[root@dg ~]# mkdir /oradata
[root@dg ~]# chown -R oracle.oinstall /oradata/
[root@dg ~]# su - oracle
[oracle@dg ~]$ mkdir -p /oracle/app/oracle/admin/$ORACLE_SID/adump
[oracle@dg ~]$ mkdir -p /oradata/shanghai/arch
[oracle@dg ~]$ mkdir -p /oradata/shanghai/map [oracle@dg ~]$ vi /home/oracle/standby/a.txt
*.audit_file_dest='/oracle/app/oracle/admin/map/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/shanghai/map/control01.ctl','/oradata/shanghai/map/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='map'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mapXDB)'
*.memory_target=629145600
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name=shanghai
*.log_archive_config='dg_config=(beijing,shanghai)'
*.log_archive_dest_1='location=/oradata/shanghai/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=shanghai'
*.log_archive_dest_2='service=beijing LGWR AFFIRM ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=beijing'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.remote_login_passwordfile=EXCLUSIVE
*.log_archive_format=%t_%s_%r.arc
*.log_archive_max_processes=30
*.fal_server=beijing
*.standby_file_management=auto
*.db_file_name_convert='/oradata/beijing/map','/oradata/shanghai/map'
*.log_file_name_convert='/oradata/beijing/map','/oradata/shanghai/map' 儲存退出。
[oracle@dg ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 14 16:40:52 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/home/oracle/standby/a.txt' ;
File created.
SQL> startup nomount ;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 373294424 bytes
Database Buffers 243269632 bytes
Redo Buffers 7532544 bytes
SQL> [oracle@dg ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 14 16:43:09 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MAP (not mounted)
RMAN> restore standby controlfile from '/home/oracle/standby/standby.ctl';
Starting restore at 14-OCT-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=/oradata/shanghai/map/control01.ctl
output file name=/oradata/shanghai/map/control02.ctl
Finished restore at 14-OCT-15
RMAN>
SQL> alter database mount standby database ;
Database altered.
SQL> select controlfile_type from v$database ;
CONTROL
-------
STANDBY 由于此次是恢複到指定的目錄，而不是和主庫相同的目錄，是以不是簡單的restore
SQL> select 'set newname for datafile ''' || name ||
''' to ''/oradata/shanghai/gqxt/' ||
substr(name, instr(name, '/', -1) + 1) || ''';'
from v$datafile order by file#; 2 3 4
'SETNEWNAMEFORDATAFILE'''||NAME||'''TO''/ORADATA/SHANGHAI/GQXT/'||SUBSTR(NAME,IN
--------------------------------------------------------------------------------
set newname for datafile '/oradata/shanghai/map/system01.dbf' to '/oradata/shang
hai/gqxt/system01.dbf';
set newname for datafile '/oradata/shanghai/map/sysaux01.dbf' to '/oradata/shang
hai/gqxt/sysaux01.dbf';
set newname for datafile '/oradata/shanghai/map/undotbs01.dbf' to '/oradata/shan
ghai/gqxt/undotbs01.dbf';
set newname for datafile '/oradata/shanghai/map/users01.dbf' to '/oradata/shangh
ai/gqxt/users01.dbf';
SQL>
RMAN> run {
set newname for datafile '/oradata/beijing/map/system01.dbf' to '/oradata/shanghai/map/system01.dbf';
set newname for datafile '/oradata/beijing/map/sysaux01.dbf' to '/oradata/shanghai/map/sysaux01.dbf';
set newname for datafile '/oradata/beijing/map2> 3> 4> /undotbs01.dbf' to '/oradata/shanghai/map/undotbs01.dbf';
set newname for datafile '/oradata/beijing/map/users01.dbf' to '/oradata/shanghai/map/users01.dbf';
restore database;
switch datafile all;
}5> 6> 7> 8>
executing command: SET NEWNAME
released channel: ORA_DISK_1
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 14-OCT-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /oradata/shanghai/map/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata/shanghai/map/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/standby/full_MAP_20151014_2_1
channel ORA_DISK_1: piece handle=/home/oracle/standby/full_MAP_20151014_2_1 tag=TAG20151014T160708
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oradata/shanghai/map/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata/shanghai/map/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/standby/full_MAP_20151014_1_1
channel ORA_DISK_1: piece handle=/home/oracle/standby/full_MAP_20151014_1_1 tag=TAG20151014T160708
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 14-OCT-15
RMAN> recover database ;
Starting recover at 14-OCT-15
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file /oradata/shanghai/arch/1_9_893081127.arc
archived log for thread 1 with sequence 10 is already on disk as file /oradata/shanghai/arch/1_10_893081127.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_DISK_1: reading from backup piece /home/oracle/standby/arch_MAP_20151014_6_1
channel ORA_DISK_1: piece handle=/home/oracle/standby/arch_MAP_20151014_6_1 tag=TAG20151014T160742
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/oradata/shanghai/arch/1_5_893081127.arc thread=1 sequence=5
archived log file name=/oradata/shanghai/arch/1_6_893081127.arc thread=1 sequence=6
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_1: reading from backup piece /home/oracle/standby/arch_MAP_20151014_7_1
channel ORA_DISK_1: piece handle=/home/oracle/standby/arch_MAP_20151014_7_1 tag=TAG20151014T160742
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/oradata/shanghai/arch/1_7_893081127.arc thread=1 sequence=7
archived log file name=/oradata/shanghai/arch/1_8_893081127.arc thread=1 sequence=8
archived log file name=/oradata/shanghai/arch/1_9_893081127.arc thread=1 sequence=9
archived log file name=/oradata/shanghai/arch/1_10_893081127.arc thread=1 sequence=10
unable to find archived log
archived log thread=1 sequence=11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/14/2015 16:51:16
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 11 and starting SCN of 1018211
RMAN> Oracle推薦在主庫上配置standby redo日志，因為當主庫轉換為standby的時候，可以較少時間，能夠在切換之後順利的接收實時傳送的日志，是以提前在主庫配置standby redo日志還是較好的。
Oracle建議所有備庫和主庫建立的standby redo日志的大小和源庫保持一緻。
查詢主庫V$LOG檢視袁庫有幾組redo日志組，查詢V$THREAD視圖看袁庫有幾個redo thread。
主庫執行以下sql語句
SQL> select group#,bytes from v$log ;
備庫執行以下sql語句
SQL> select group#,bytes from v$standby_log ;
備庫的standby redo日志組至少要比源庫多一組，執行以下語句進行添加
alter database add standby logfile ('/oracle/dbs/slog1.log) size 50M ;
如果源庫是RAC，需要檢視有幾個thread，以下是在源庫上建立的standby redo日志(有兩個redo thread)
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 50M;
無論何時添加一個redo日志組到主庫，也必須添加一個日志組到每個備庫，否則，備庫可能會變得不同步，如果是最大保護模式會導緻主庫被關閉。
注意：如果主庫已經提前建立好了standby redo log file，備庫會根據LOG_FILE_NAME_CONVERT參數轉換後自動建立與之對應的standby redo log file日志組。是以此步驟可省略。
如有主庫未建立standby redo logfile，則備庫必須手工建立日志檔案組
公式如下：
如果主庫是單執行個體資料庫：Standby Redo Log組數=主庫日志組總數+1
如果主庫是RAC資料庫：StandbyRedo Log組數=(所有節點中日志組數最大值 + 1) * RAC節點數
SQL> alter database add standby logfile group 4 ('/oradata/beijing/map/standby01a.log','/oradata/beijing/map/standby01b.log') size 50M;
Database altered.
SQL> alter database add standby logfile group 5 ('/oradata/beijing/map/standby02a.log','/oradata/beijing/map/standby02b.log') size 50M;
Database altered.
SQL> alter database add standby logfile group 6 ('/oradata/beijing/map/standby03a.log','/oradata/beijing/map/standby03b.log') size 50M;
Database altered.
SQL> alter database add standby logfile group 7 ('/oradata/beijing/map/standby04a.log','/oradata/beijing/map/standby04b.log') size 50M;
Database altered.
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED
7 0 0 YES UNASSIGNED SQL> alter database add standby logfile group 4 ('/oradata/shanghai/map/standby01a.log','/oradata/shanghai/map/standby01b.log') size 50M;
Database altered.
SQL> alter database add standby logfile group 5 ('/oradata/shanghai/map/standby02a.log','/oradata/shanghai/map/standby02b.log') size 50M;
Database altered.
SQL> alter database add standby logfile group 6 ('/oradata/shanghai/map/standby03a.log','/oradata/shanghai/map/standby03b.log') size 50M;
Database altered.
SQL> alter database add standby logfile group 7 ('/oradata/shanghai/map/standby04a.log','/oradata/shanghai/map/standby04b.log') size 50M;
Database altered.
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED
7 0 0 YES UNASSIGNED [oracle@db1 ~]$ tnsping beijing
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-OCT-2015 17:14:10
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.241)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = beijing)))
OK (0 msec)
[oracle@db1 ~]$ tnsping shanghai
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-OCT-2015 17:14:13
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.242)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = shanghai)))
OK (0 msec)
[oracle@db1 ~]$ [oracle@dg ~]$ tnsping beijing
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-OCT-2015 17:14:49
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.241)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = beijing)))
OK (0 msec)
[oracle@dg ~]$ tnsping shanghai
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-OCT-2015 17:14:52
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.242)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = shanghai)))
OK (0 msec)
[oracle@dg ~]$ 測試隻是說明網絡是通的，最好使用
sqlplus system/oracle@beijing
檢視備庫狀态，以隻讀方式打開資料庫
SQL> select status from v$Instance ;
STATUS
------------
MOUNTED
SQL>
SQL> alter database open read only ;
Database altered. 啟動redo apply，在此過程中注意監控備庫日志的變化
SQL> alter database recover managed standby database using current logfile disconnect from session ;
Database altered.
[oracle@dg ~]$ cd /oracle/app/oracle/diag/rdbms/shanghai/map/trace/
[oracle@dg trace]$ tail -f alert_map.log
Media Recovery Waiting for thread 1 sequence 11 (in transit) 主庫切歸檔，檢視備庫和alert中變化
SQL> alter system switch logfile ;
System altered. 檢視備庫alert日志
Media Recovery Waiting for thread 1 sequence 11 (in transit)
Wed Oct 14 17:29:44 2015
Archived Log entry 7 added for thread 1 sequence 11 rlc 893081127 ID 0x5b1ab6a4 dest 2:
RFS[2]: Selected log 4 for thread 1 sequence 12 dbid 1528459940 branch 893081127
Wed Oct 14 17:29:44 2015
Media Recovery Log /oradata/shanghai/arch/1_11_893081127.arc
Media Recovery Waiting for thread 1 sequence 12 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 12 Reading mem 0
Mem# 0: /oradata/shanghai/map/standby01a.log
Mem# 1: /oradata/shanghai/map/standby01b.log 主庫檢視目前歸檔日志資訊
select sequence#,first_time,next_time from v$archived_log order by sequence# ;
備庫檢視是否和主庫保持一緻
備庫檢視應用狀态
SQL> select sequence#,applied from v$archived_log order by sequence# ;
要是是yes或者in memory表示是正常的，表示日志被正常應用
主庫建立表
SQL> create table testdg (num int) ;
Table created.
SQL> insert into testdg values(1) ;
1 row created.
SQL> commit ;
Commit complete.
SQL> 備庫檢視
SQL> select * from testdg ;
NUM
----------
1 主庫建立表空間，添加資料檔案
SQL> create tablespace testdg datafile '/oradata/beijing/map/testdg1.dbf' size 1m ;
Tablespace created.
SQL> select name from v$datafile ;
NAME
--------------------------------------------------------------------------------
/oradata/beijing/map/system01.dbf
/oradata/beijing/map/sysaux01.dbf
/oradata/beijing/map/undotbs01.dbf
/oradata/beijing/map/users01.dbf
/oradata/beijing/map/testdg1.dbf
SQL> 備庫檢視alert日志，檢視是否建立
alert日志
Wed Oct 14 17:33:50 2015
Recovery created file /oradata/shanghai/map/testdg1.dbf
Successfully added datafile 5 to media recovery
Datafile #5: '/oradata/shanghai/map/testdg1.dbf' SQL> select name from v$datafile ;
NAME
--------------------------------------------------------------------------------
/oradata/shanghai/map/system01.dbf
/oradata/shanghai/map/sysaux01.dbf
/oradata/shanghai/map/undotbs01.dbf
/oradata/shanghai/map/users01.dbf
/oradata/shanghai/map/testdg1.dbf 9.1dataguard正常關閉
首先停止redo日志的應用，執行以下sql停止
alter database recover managed standby database cancel ;
然後關閉主庫，執行以下sql
shutdown immediate ;
最後關閉備庫
9.2dataguard正常啟動
啟動standby資料庫
startup mount ;
alter database open read only ;
啟動主庫
startup
開啟備庫的redo應用
alter database recover managed standby database using current logfile disconnect from session ;
需要注意的是，預設的保護模式是最大性能模式(maximum
performance)
SQL> select database_role from v$database ;
DATABASE_ROLE
----------------
PHYSICAL STANDBY ![手把手教你DBCA搭建Oracle ADG[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)