黄智中关系:DataGuard physical standby创建与维护

首先介绍一些概念
physical standby database ,即直接从主库接受archivedlog然后做基于block的物理恢复，所以physical standby在物理文件一级完全等同于主库。

Logical standby database是把主库上传过来的archived log通过logminer技术，解析为sql语句，直接在logical standby上作基于sql的同步。

从 9iR2开始的默认模式 maximum performace, 采用maximum protection和maximum Availability可以保证数据的零丢失，但代价是如果standby库出问题或standby库无法和primary库连接，则primary库 也会down 掉（以保证不产生redo log）。

重要的进程

• RFS (remote file server process) :接受由primary数据库的lgwr或arch通过oracle net传来的redo 数据，写入standby redo logs或standby archived redo logs
• MRP(managed Recovery Process): 管理恢复进程。
• FAL(fetch archive log): 在primary和standby数据库的两端同时配置。Primary端是fetch archive log server,standby端是fetch archive log client. FAL是自动探测primary/standby数据库之间archived logs是否有有间隔的一个进程。

开始做physical standby
1.1 主库上的准备工作
1.开启forced logging模式(alter database force logging;)
2.开启归档模式和设置归档路径 alter system set log_archive_dest_1=’LOCATION=/u02/oradata/archive’scope=both;
3.为备库创建控制文件(alter database create standby controlfile as ‘目标路径’;)
4.验证主库的数据文件(select * from v$datafile),关闭数据库,并拷贝到备库目录,再重启主库.
5．修改tnsnames.ora，加入备库信息：

STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.40.13.179)(PORT = 1521))
)

(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

6.为备库创建参数文件(create pfile=’目标路径’ from spfile;)然后拷贝到备库

1.2 创建备用数据库
1．修改从主库拷贝的备库参数文件
一般只须加这些参数：
standby_archive_dest=’d:oradataarchive’
standby_file_management=AUTO
remote_archive_enable=true
fal_server=’orcl’
fal_client=’standby’

具体参数详细信息：
db_name:不需要修改,和主数据库保持一致.
compatible:不需要修改,和主数据库保持一致,9.2.0.1
control_files:修改指向备库的控制文件
log_archive_start:不需要修改
standby_archive_dest:指向备库的归档日志目录(从主库接收归档日志)
db_file_name_convert:在目录结构改变时需要设置,格式为(’主库数据文件路径’,'从库数据文件路径’)
log_file_name_convert:在目录结构改变时需要设置,格式为(’主库日志文件路径’,'从库日志文件路径’)
log_archive_dest_1:应指向从库的归档日志目录
standby_file_management:设置为AUTO
remote_archive_enable:设置为TURE(测试中发现加上这条就无法生成spfile,可能是系统默认值不要设置)
instance_name:应该修改为从库的数据库实例名(如在同一主机时,备库肯定无法和主库相同,肯定要修改)
lock_name_space:设置为备库实例名

2、创建windows服务(oradim -NEW -SID 备库实例名 -STARTMODE manual)
3、使用Net Manager配置监听进程及tnsnames
以下是tnsnaes.ora

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.40.13.180)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.40.144.79)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)

(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)

4、创建备库SPfile(由修改过的Pfile生成)create spfile from Pfile=’备库路径’;
5、启动physical standby database
startup nomount;
alter database mount standby database;
6、 alter database recover managed standby database disconnect from session;

初始化Log Apply Services
7、在主库上设置到备库的归档(在主库上操作)
alter system set log_archive_dest_2=’SERVICE=备库实例名’ scope=both;
alter system set log_archive_dest_state_2=enable scope=both;

1.3 验证physical standby database
当创建了物理备份数据库和设置日志传输服务,可在备份数据库上验证修改是否生效
1.查询现有归档日志(select name,applied from v$archived_log
2.在主库上归档当前日志(alter system archive log current;)
3.验证新日志是否接收到(同步骤1)
4. 验证是否新的归档Redo日志已经被应用(select name,applied from v$archived_log ;)注:对于传递过来的归档日志,要定期到备库上执行recover standby database;来应用归档日志(查询applied显示为YES)也可以运行recover managed standby database;来自动运行(光标停留在下一行,连SQL都没显示,这是正常现象),然后恢复进程每15秒查询一次是否有新的归档日志并运行.另外可用 recover managed standby database cancel;取消备库的自管理模式

2 主备库切换
2.1 主库切到备库
1、 在主库

以备用模式启用主数据

2、 在从库

2.2 主库切换到备库后，切换回来
1、在主节点（原备库）

2、 在从库

2.2 主库切换到备库后，切换回来
1、在主节点（原备库）

2、在备用节点（原主库）

3 日常管理
3.1 关于启动关闭顺序
启动的时候，先从库的listener,再启动从库,再启动主库的listener,再主库

关闭的时候，先关闭主库，再启动从库。

3.2 检查当前Primary库的保护模式
select protection_mode from v$database;

转换数据库模式为MAX AVAILABILITY：

shutdown immediate;

startup mount;

alter database set standby database to maximize performance;

alter database open;

3.3 测试Primary的归档能否应用到Standby
.查看主数据库日志是否全部传送到副数据库，可查看v$archive_gap,更简单的方法是查看各自主机的日志归档目录的日志序号即可

select max(sequece#) from v$archived_log;
select file_name from v$datafile;
show parameter fal
show parameter lag
select * from v$archive_gap;

另外,alter database recover managed standby database cancel;是用来停止主备间的归档日志传输的

Step 1 Identify the existing archived redo log files.

On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME

2 FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME

———- —————— ——————

8 11-JUL-02 17:50:45 11-JUL-02 17:50:53

9 11-JUL-02 17:50:53 11-JUL-02 17:50:58

10 11-JUL-02 17:50:58 11-JUL-02 17:51:03

3 rows selected.

Step 2 Force a log switch to archive the current online redo log file.

On the primary database,

SQL> ALTER SYSTEM SWITCH LOGFILE;

Step 3 Verify the new redo data was archived on the standby database.

On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME

2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME

———- —————— ——————

8 11-JUL-02 17:50:45 11-JUL-02 17:50:53

9 11-JUL-02 17:50:53 11-JUL-02 17:50:58

10 11-JUL-02 17:50:58 11-JUL-02 17:51:03

11 11-JUL-02 17:51:03 11-JUL-02 18:34:11

4 rows selected.

The archived redo log files are now available to be applied to the physical standby database.

Step 4 Verify new archived redo log files were applied.

On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG

2 ORDER BY SEQUENCE#;

SEQUENCE# APP

——— —

8 YES

9 YES

10 YES

11 YES

4 rows selected.

3.4 设置归档频率
如果要强制Primary一分种归档一次，那么设置Primary的初始化参数ARCHIVE_LAG_TARGET：

alter system set ARCHIVE_LAG_TARGET=60 scope=both;

3.5 停止Standby
alter database recover managed standby database cancel;

shutdown immediate;

3.6 切换到只读模式
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE OPEN READ ONLY;

3.7 切换回管理恢复模式
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 8 DISCONNECT FROM SESSION;

3.8 哪些归档日志没有归档到备用库
假设local归档目录的ID为1，remote的归档目录为2。
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
(SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
WHERE
LOCAL.SEQUENCE# NOT IN
(SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND
THREAD# = LOCAL.THREAD#);

3.9 主备切换失败情况
During normal operations it is acceptable to see the following values for SWITCHOVER_STATUS on the primary to be SESSIONS ACTIVE or TO STANDBY.

.Switchover_status状态(V$database)

NOT ALLOWED - Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases.
SESSIONS ACTIVE - Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.
alter database commit to switchover to standby with session shutdown;

SWITCHOVER PENDING - This is a standby database and the primary database switchover request has been received but not processed.
SWITCHOVER LATENT - The switchover was in pending mode, but did not complete and went back to the primary database.
TO PRIMARY - This is a standby database, with no active sessions, that is allowed to switch over to a primary database.
TO STANDBY - This is a primary database, with no active sessions, that is allowed to switch over to a standby database.
RECOVERY NEEDED - This is a standby database that has not received the switchover request.

3.10 注意事项：
1、如果在主库执行 alter database clear unarchived logfile或alter database open resetlogs ,则dataguard要重建。

2、在连续恢复模式下工作之前，需要保证之前所有的归档日志己经应用到备用库上。因为在连续恢复模式的情况下，oracle不会应用之前的归档日志，而只会应用后面陆续到来的归档日志。

3、 出现归档日志gap时，需要找出相应的归档日志， 然后将这些归档日志copy到备用节点的standby_archive_dest和log_archive_dest目录下面。需要注意的是 log_archive_dest目录下也需要copy。然后ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;
Oracle Corporation recommends automatically applying the logs in the archive gap using the:

Alter database recovery manage standby database;

3、 修改控制文件后，应archive log current,关闭主库，拷到备库，再应用log.

4、 新建表、表空间、datafile都能通过日志应用到备库，但新建一个临时表空间，rename datafile 均不能应用到备库上.

5、应当实时察看standby库的alert文件，就能清晰明了地知道主副更新的情况。这也是排错的重要方法,切记!!

3.11 failover和switchover

关于failover和switchover （参照Rollingpig的总结）

——————————————– Rollingpig

我给个结论吧。在9i 的dataguad环境中：

1。Switch Over 必须是Primary 正常，并且是必须Primary 主动先Switch成 standby.然后standby 才能switch 成primary.

2。如果需要作成primary出问题，standby 能接管的话，必须作 failover ,而不是SwitchOver

Failover 和 Switchover 的区别
Failover ： 将主数据库offline，备用数据库online，这种操作由系统和软件失败引起。 即使在备用数据库上应用重做日志，也可能出现数据丢失的现象，除非备用数据库运行在 guaranteed protection 模式。 原主数据库重新使用时必须重新启动实例。 其它的备用数据库也需重新启动实例。 Switchover ： 故意将主数据库offline，而将另一备用数据库online，它能够切换到备用数据库而不需同步操作。如：可使用 Switchover 完成系统的平滑升级。 即使在备用数据库上不应用重做日志，也不会造成数据的丢失。 数据库不需重新启动实例。这使主数据库几乎能立即在备用数据库上恢复它的功能，因此可经常进行定期维护而不需中断操作。 　　Failover和Switchover的区别为：当Failover发生，备用数据库切换为主数据库之后，它丢失了备用数据库的所有能力，也就是 说，不能再返回到备用模式；而Switchover可以，备用数据库可切换为主数据库，也可从主数据库再切换回备用数据库。

3.12 相关视图
DBA_LOGSTDBY_EVENTS (Logical Standby Databases Only)
DBA_LOGSTDBY_LOG (Logical Standby Databases Only)
DBA_LOGSTDBY_NOT_UNIQUE (Logical Standby Databases Only)
DBA_LOGSTDBY_PARAMETERS (Logical Standby Databases Only)
DBA_LOGSTDBY_PROGRESS (Logical Standby Databases Only)
DBA_LOGSTDBY_SKIP (Logical Standby Databases Only)
DBA_LOGSTDBY_SKIP_TRANSACTION (Logical Standby Databases Only)
DBA_LOGSTDBY_UNSUPPORTED (Logical Standby Databases Only)
V$ARCHIVE_DEST
V$ARCHIVE_DEST_STATUS
V$ARCHIVE_GAP
V$ARCHIVED_LOG
V$DATABASE
V$DATAFILE
V$DATAGUARD_STATUS
V$LOG
V$LOGFILE
V$LOG_HISTORY
V$LOGSTDBY (Logical Standby Databases Only)
V$LOGSTDBY_STATS (Logical Standby Databases Only)
V$MANAGED_STANDBY (Physical Standby Databases Only)
V$STANDBY_LOG

--EOF--