天在一台机器上模拟了dataguard,主备两个实例从物理上不共享任何归档文件路径。
主要有以下内容：
dataguard Physical standby的创建
protection mode的切换
switch over
模拟了两台机器oel1,oel2 主库的归档放在oel1里面，备库的放在oel2里面
创建的路径如下
./oel1:
orcl_pri_arch orcl_stdby_arch
./oel2:
standby_pri_arch standby_stdby_arch
--强制logging
SQL> alter database force logging;
alter database force logging
*
ERROR at line 1:
ORA-12920: database is already in force logging mode
SQL> select force_logging from v$database;
FOR
---
YES
--查看归档状态
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
--创建密码文件
[oracle@oel1 dbs]$ orapwd file=orapwstandby password=oracle entries=10
[oracle@oel1 dbs]$
listener.ora
LISTENER1 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = oel1.oracle.com)(PORT = 1521))
)
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl.oracle.com)
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = standby.oracle.com)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = standby)
)
)
tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = oel1.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = orcl.oracle.com)
)
)
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = oel1.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = stdby.oracle.com)
)
)
--创建相应的备库目录
[oracle@oel1 standby]$ mkdir adump bdump cdump udump
[oracle@oel1 standby]$ ll
total 16
drwxr-xr-x 2 oracle dba 4096 Oct 21 15:27 adump
drwxr-xr-x 2 oracle dba 4096 Oct 21 15:27 bdump
drwxr-xr-x 2 oracle dba 4096 Oct 21 15:27 cdump
drwxr-xr-x 2 oracle dba 4096 Oct 21 15:27 udump
参数文件
主库参数文件(ORCL)
db_unique_name='orcl'
log_archive_config='dg_config=(orcl,standby)'
log_archive_dest_1='location=/u01/app/oracle/dg/oel1/orcl_pri_arch valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'
log_archive_dest_2='service=standby valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=standby'
log_archive_dest_3='location=/u01/app/oracle/dg/oel1/orcl_stdby_arch valid_for=(STANDBY_LOGFILE,STANDBY_ROLE) db_unique_name=orcl'
db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/standby'
log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/standby'
standby_file_management=auto
备库参数文件(standby)
db_unique_name='standby'
log_archive_config='dg_config=(orcl,standby)'
log_archive_dest_1='location=/u01/app/oracle/dg/oel2/standby_pri_arch valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=standby'
log_archive_dest_2='service=orcl valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'
log_archive_dest_3='location=/u01/app/oracle/dg/oel2/standby_stdby_arch valid_for=(STANDBY_LOGFILE,STANDBY_ROLE) db_unique_name=standby'
db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/standby'
log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/standby'
standby_file_management=auto
主库运行
SQL> alter database create standby controlfile as '/u01/app/oracle/dg/standby.ctl';
Database altered.
SQL>
--错误的操作来生成控制文件
[oracle@oel1 standby]$ cp /u01/app/oracle/dg/standby.ctl control01.ctl
[oracle@oel1 standby]$ ll
total 21588
-rw-r----- 1 oracle dba 7356416 Oct 21 16:22 control01.ctl
-rw-r----- 1 oracle dba 7356416 Oct 21 16:23 control02.ctl
-rw-r----- 1 oracle dba 7356416 Oct 21 16:22 control03.ctl
--正确的方法
[oracle@oel1 standby]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 21 16:26:01 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220336 bytes
Variable Size 142606608 bytes
Database Buffers 373293056 bytes
Redo Buffers 7168000 bytes
SQL>
[oracle@oel1 standby]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Oct 21 16:27:44 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: orcl (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/dg/standby.ctl';
Starting restore at 21-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=39 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=/u01/app/oracle/oradata/standby/control01.ctl
output filename=/u01/app/oracle/oradata/standby/control02.ctl
output filename=/u01/app/oracle/oradata/standby/control03.ctl
Finished restore at 21-OCT-12
RMAN>
[oracle@oel1 standby]$ ll
total 21588
-rw-r----- 1 oracle dba 7356416 Oct 21 16:28 control01.ctl
-rw-r----- 1 oracle dba 7356416 Oct 21 16:28 control02.ctl
-rw-r----- 1 oracle dba 7356416 Oct 21 16:28 control03.ctl
[oracle@oel1 standby]$
将备库启动到Mount状态
--到此为止要保证网络是互通的，可以检查密码文件，listener等的配置是否合适
[oracle@oel1 admin]$ sqlplus as sysdba
[oracle@oel1 admin]$ sqlplus as sysdba
-standby库 使用下面命令来apply
SQL> alter database recover managed standby database disconnect from session;
Database altered.
--当然也可以取消apply
SQL> alter database recover managed standby database cancel;
Database altered.
--在取消apply的时候，添加standby logfile,强烈建议在apply之前能够创建几组standby logfile
SQL> alter database add standby logfile;
Database altered.
SQL> select member from v$logfile;
/u01/app/oracle/STANDBY/onlinelog/o1_mf_8_88807p3x_.log
--在主库切换日志
SQL> alter system switch logfile;
System altered.
SQL>
如果没有看到归档到指定的目录，查看primary database的log_archive_dest参数，很可能是修改了Pfile 之后没有同步到spfile.
Error 16047 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'standby'
ORA-16047: DGID mismatch between destination setting and standby
*** 2012-10-21 17:09:46.533 60679 kcrr.c
PING[ARC0]: Heartbeat failed to connect to standby 'standby'. Error is 16047.
*** 2012-10-21 17:09:46.533 58941 kcrr.c
kcrrfail: dest:2 err:16047 force:0 blast:1
查找了半天，原来是在参数文件中
db_unique_name='standb'
log_archive_config='dg_config=(orcl,standby)'
在主库中查看到如下的错误
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_arc0_16479.trc:
ORA-16047: DGID mismatch between destination setting and standby
Sun Oct 21 17:14:46 2012
PING[ARC0]: Heartbeat failed to connect to standby 'standby'. Error is 16047.
Sun Oct 21 17:18:50 2012
Thread 1 advanced to log sequence 15
Current log# 2 seq# 15 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Sun Oct 21 17:18:50 2012
ARC1: Archivelog destination LOG_ARCHIVE_DEST_2 disabled: Data Guard configuration identifier mismatch
可以查看
SQL> select dest_id,error from v$archive_dest;
SQL> select dest_id,error from v$archive_dest;
DEST_ID ERROR
---------- -----------------------------------------------------------------
1
2 ORA-16047: DGID mismatch between destination setting and standby
3 ORA-16053: DB_UNIQUE_NAME is not in the Data Guard Configuration
4
5
6
7
8
9
。。
就能查看到错误的原因了。
ORA-16053: DB_UNIQUE_NAME is not in the Data Guard Configuration
原因是在主库的参数配置中，db_unique_name
*.log_archive_dest_3='location=/u01/app/oracle/dg/oel1/orcl_stdby_arch valid_for=(STANDBY_LOGFILE,STANDBY_ROLE) db_unique_name=standby'
dataguard 有一个很好的优点就是如果之前的配置错误，导致文件不能正确apply,会向primary database 索取apply点之前的日志文件
如下：
[oracle@oel1 orcl_pri_arch]$ ll
total 2164
-rw-r----- 1 oracle dba 248832 Oct 21 16:46 1_13_796831768.dbf
-rw-r----- 1 oracle dba 1257984 Oct 21 17:18 1_14_796831768.dbf
-rw-r----- 1 oracle dba 690176 Oct 21 17:31 1_15_796831768.dbf
[oracle@oel1 orcl_pri_arch]$
--在主库的归档目录下只有三个文件
--但是在正确配置了参数之后，重启两个实例后，手动切换日志，
--主库日志如下：
Sun Oct 21 17:31:44 2012
Thread 1 advanced to log sequence 16
Current log# 3 seq# 16 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
Sun Oct 21 17:32:10 2012
ARC0: Standby redo logfile selected for thread 1 sequence 15 for destination LOG_ARCHIVE_DEST_2
--在备库，中会发现gap.然后索取之前的日志
Completed: alter database recover managed standby database disconnect from session
Sun Oct 21 17:31:36 2012
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/dg/oel2/standby_stdby_arch
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 19230
RFS[1]: Identified database type as 'physical standby'
Sun Oct 21 17:31:36 2012
RFS LogMiner: Client disabled from further notification
RFS[1]: Archived Log: '/u01/app/oracle/dg/oel2/standby_stdby_arch/1_1_796831768.dbf'
RFS[1]: Archived Log: '/u01/app/oracle/dg/oel2/standby_stdby_arch/1_2_796831768.dbf'
Sun Oct 21 17:31:37 2012
Fetching gap sequence in thread 1, gap sequence 11-13
FAL[client]: Error fetching gap sequence, no FAL server specified
Sun Oct 21 17:31:37 2012
RFS[1]: Archived Log: '/u01/app/oracle/dg/oel2/standby_stdby_arch/1_3_796831768.dbf'
Sun Oct 21 17:31:47 2012
RFS[1]: Archived Log: '/u01/app/oracle/dg/oel2/standby_stdby_arch/1_4_796831768.dbf'
Sun Oct 21 17:31:58 2012
RFS[1]: Archived Log: '/u01/app/oracle/dg/oel2/standby_stdby_arch/1_5_796831768.dbf'
RFS[1]: Archived Log: '/u01/app/oracle/dg/oel2/standby_stdby_arch/1_6_796831768.dbf'
RFS[1]: Archived Log: '/u01/app/oracle/dg/oel2/standby_stdby_arch/1_7_796831768.dbf'
RFS[1]: Archived Log: '/u01/app/oracle/dg/oel2/standby_stdby_arch/1_8_796831768.dbf'
RFS[1]: Archived Log: '/u01/app/oracle/dg/oel2/standby_stdby_arch/1_9_796831768.dbf'
Sun Oct 21 17:32:07 2012
Fetching gap sequence in thread 1, gap sequence 11-14
FAL[client]: Error fetching gap sequence, no FAL server specified
Sun Oct 21 17:32:07 2012
RFS[1]: Archived Log: '/u01/app/oracle/dg/oel2/standby_stdby_arch/1_10_796831768.dbf'
RFS[1]: Archived Log: '/u01/app/oracle/dg/oel2/standby_stdby_arch/1_11_796831768.dbf'
RFS[1]: Archived Log: '/u01/app/oracle/dg/oel2/standby_stdby_arch/1_12_796831768.dbf'
RFS[1]: Archived Log: '/u01/app/oracle/dg/oel2/standby_stdby_arch/1_13_796831768.dbf'
RFS[1]: Archived Log: '/u01/app/oracle/dg/oel2/standby_stdby_arch/1_14_796831768.dbf'
RFS[1]: Successfully opened standby log 4: '/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_4_85x8vjt5_.log'
Sun Oct 21 17:32:37 2012
Media Recovery Log /u01/app/oracle/dg/oel2/standby_stdby_arch/1_11_796831768.dbf
Media Recovery Log /u01/app/oracle/dg/oel2/standby_stdby_arch/1_12_796831768.dbf
Media Recovery Log /u01/app/oracle/dg/oel2/standby_stdby_arch/1_13_796831768.dbf
Media Recovery Log /u01/app/oracle/dg/oel2/standby_stdby_arch/1_14_796831768.dbf
Media Recovery Log /u01/app/oracle/dg/oel2/standby_stdby_arch/1_15_796831768.dbf
Media Recovery Waiting for thread 1 sequence 16
--在备库的归档路径下
[oracle@oel1 standby_stdby_arch]$ ll
total 242948
-rw-r----- 1 oracle dba 4096 Oct 21 17:32 1_10_796831768.dbf
-rw-r----- 1 oracle dba 1609728 Oct 21 17:32 1_11_796831768.dbf
-rw-r----- 1 oracle dba 2048 Oct 21 17:32 1_12_796831768.dbf
-rw-r----- 1 oracle dba 248832 Oct 21 17:32 1_13_796831768.dbf
-rw-r----- 1 oracle dba 1257984 Oct 21 17:32 1_14_796831768.dbf
-rw-r----- 1 oracle dba 690176 Oct 21 17:32 1_15_796831768.dbf
-rw-r----- 1 oracle dba 60416 Oct 21 17:31 1_1_796831768.dbf
-rw-r----- 1 oracle dba 8704 Oct 21 17:31 1_2_796831768.dbf
-rw-r----- 1 oracle dba 5632 Oct 21 17:31 1_3_796831768.dbf
-rw-r----- 1 oracle dba 100657664 Oct 21 17:31 1_4_796831768.dbf
-rw-r----- 1 oracle dba 100661248 Oct 21 17:31 1_5_796831768.dbf
-rw-r----- 1 oracle dba 16238592 Oct 21 17:32 1_6_796831768.dbf
-rw-r----- 1 oracle dba 1735680 Oct 21 17:32 1_7_796831768.dbf
-rw-r----- 1 oracle dba 450560 Oct 21 17:32 1_8_796831768.dbf
再次手动切换日志，一切正常
--从备库中查看
Sun Oct 21 17:38:38 2012
RFS[1]: Successfully opened standby log 4: '/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_4_85x8vjt5_.log'
Sun Oct 21 17:38:41 2012
Media Recovery Log /u01/app/oracle/dg/oel2/standby_stdby_arch/1_16_796831768.dbf
Media Recovery Waiting for thread 1 sequence 17
--change protection mode
orcl>select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
SQL> alter database set standby to maximize protection;
alter database set standby to maximize protection
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance
orcl>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
orcl>startup mount;
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220336 bytes
Variable Size 213909776 bytes
Database Buffers 301989888 bytes
Redo Buffers 7168000 bytes
Database mounted.
orcl>alter database set standby database to maximize protection;
Database altered.
orcl>alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
--主库就突然down了
--查看备库，模式也没有任何改变
SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
--日志 from primary database orcl
LGWR: Primary database is in MAXIMUM PROTECTION mode
LGWR: Destination LOG_ARCHIVE_DEST_2 is not serviced by LGWR
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
LGWR: Minimum of 1 LGWR standby database required
Sun Oct 21 17:53:11 2012
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_lgwr_21162.trc:
ORA-16072: a minimum of one standby database destination is required
Sun Oct 21 17:53:11 2012
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_lgwr_21162.trc:
ORA-16072: a minimum of one standby database destination is required
LGWR: terminating instance due to error 16072
Instance terminated by LGWR, pid = 21162
--先修复主库的问题
SQL> alter database set standby database to maximize performance;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter system set log_archive_dest_2='service=standby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=standby';
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220336 bytes
Variable Size 213909776 bytes
Database Buffers 301989888 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=standby LGWR SYNC AFFI
RM VALID_FOR=(ONLINE_LOGFILE,P
RIMARY_ROLE) db_unique_name=st
andby
SQL> alter database set standby database to maximize protection;
Database altered.
SQL> alter database open;
Database altered.
SQL>
--在open时，有的人遇到了一些问题。
可能是网络不好，需要设置timeout参数，改为
*.log_archive_dest_2='SERVICE=standby LGWR SYNC AFFIRM NET_TIMEOUT=120 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
就可以了。
--再次查看备库的状态
SQL> select database_role,protection_mode,protection_level from v$database;--最开始的
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
SQL> /
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL --修改后的
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PROTECTION MAXIMUM PROTECTION
SQL>
--在主库继续切换模式
SQL> alter database set standby database to maximize performance;
Database altered.
--备库也会相应的变化
SQL> /
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
SQL> alter database set standby database to maximize availability;
Database altered.
--在备库查看模式
SQL> /
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY RESYNCHRONIZATION
--在主库查看归档情况
SQL> select dest_id,status,error from v$archive_dest ;
DEST_ID STATUS
---------- ---------
ERROR
-----------------------------------------------------------------
1 VALID
2 ERROR
ORA-16086: standby database does not contain available standby
log files
3 BAD PARAM
ORA-16053: DB_UNIQUE_NAME is not in the Data Guard Configuration
在备库的日志中查看，原来是超时导致的。
ORA-16086: standby database does not contain available standby log files
*** 2012-10-21 22:13:23.151 60679 kcrr.c
LGWR: Error 16086 creating archivelog file 'standby'
*** 2012-10-21 22:13:23.151 58941 kcrr.c
kcrrfail: dest:2 err:16086 force:0 blast:1
Receiving message from LNSb
*** 2012-10-21 22:13:23.176 55444 kcrr.c
Making upidhs request to LNSb (ocis 0x0xb70a58b8). Begin time is <10/21/2012 22:13:23> and NET_TIMEOUT <180> seconds
NetServer pid:27564
*** 2012-10-21 22:13:27.195 55616 kcrr.c
upidhs done status 0
在主库尝试加入timeout
SQL> alter system set log_archive_dest_2='service=standby LGWR SYNC AFFIRM timeout=200 valid_for=(online_logfile,primary_role) db_unique_name=standby'
2 ;
alter system set log_archive_dest_2='service=standby LGWR SYNC AFFIRM timeout=200 valid_for=(online_logfile,primary_role) db_unique_name=standby'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16025: parameter LOG_ARCHIVE_DEST_2 contains repeated or conflicting
attributes
--是设置timeout时参数名错误导致的
SQL> alter system set log_archive_dest_2='service=standby LGWR SYNC AFFIRM NET_TIMEOUT=200 valid_for=(online_logfile,primary_role) db_unique_name=standby'
2 ;
System altered.
--switch over and failover
-primary database orcl
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE
--standby database standby
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
ORA-16416: Switchover target is not synchronized with the primary
2651
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
