rman创建Standby Data Guard

发表于 2011 年 3 月 25 日 由 惜分飞

一、primary数据库
1、主库准备工作
1.1)设置强制日志
alter database force logging;

1.2)开启归档模式
ALTER DATABASE ARCHIVELOG;

1.3)添加standby logfile
–添加standby log文件（数量取决于(redo file number+1)*(thread number)）
alter database add standby logfile group 3 ‘/u01/oradata/orcl/st_redo03.log’ size 5m reuse;
alter database add standby logfile group 4 ‘/u01/oradata/orcl/st_redo04.log’ size 5m reuse;
alter database add standby logfile group 5 ‘/u01/oradata/orcl/st_redo05.log’ size 5m reuse;

2、修改参数文件（主要参考oracle 官方文档）

DB_UNIQUE_NAME=orcl
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,xff)'
LOG_ARCHIVE_DEST_1=
'location=/u01/archive/orcl/
 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
 DB_UNIQUE_NAME=orcl'
LOG_ARCHIVE_DEST_2=
'SERVICE=xff ASYNC
 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
 DB_UNIQUE_NAME=xff'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
FAL_SERVER=xff
DB_FILE_NAME_CONVERT='/u01/oradata/xff/','/u01/oradata/orcl/'
LOG_FILE_NAME_CONVERT='/u01/oradata/xff/','/u01/oradata/orcl/'
STANDBY_FILE_MANAGEMENT=AUTO

3、rman备份database和控制文件(主库)
3.1）使用rman 复制库方式建立dg
rman target /
backup full format=’/tmp/FULL_%U’ database include current controlfile for standby
plus archivelog format=’/tmp/arch_%U’;

3.2）使用rman恢复方式建立dg
rman target /
backup full format=’/tmp/FULL_%U’ database plus archivelog;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/tmp/control01.ctl’;–sqlplus中

3.3)拷贝备份文件到备份库与主库相同位置
Note:如3.2中的备库控制文件需要拷贝到备库的参数文件指定位置

4、使用修改的参数文件启动数据库之open

二、standby数据库
1、准备密码文件
直接拷贝/u01/oracle/dbs/orapwdorcl 并重命名为orapwdxff
注：密码必须和primary数据库的sys的相同

2、创建相关文件夹 mkdir

3、创建参数文件（注意db_name 要和primary的相同）

*.db_name='orcl'
DB_UNIQUE_NAME=xff
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,xff)'
LOG_ARCHIVE_DEST_1=
'location=/u01/archive/xff/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=xff'
LOG_ARCHIVE_DEST_2=
'SERVICE=orcl ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=orcl'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
FAL_SERVER=orcl
DB_FILE_NAME_CONVERT='/u01/oradata/orcl/','/u01/oradata/xff/'
LOG_FILE_NAME_CONVERT='/u01/oradata/orcl/','/u01/oradata/xff/'
STANDBY_FILE_MANAGEMENT=AUTO
--修改文件夹路径的没有标出

4、配置tns和LISTENER相关参数

5、建立data guard
5.1)使用rman 复制库方式建立dg
5.1.1)使用修改的参数文件启动standby至nomount状态
5.1.2)使用rman恢复数据库
rman target sys/xifenfei@orcl auxiliary /
duplicate target database for standby dorecover nofilenamecheck;

5.2)使用rman恢复方式建立dg
5.2.1）使用修改的参数文件启动standby至nomount状态
5.2.2）开启数据库至mount状态
alter database mount standby database;
5.2.3）使用rman开始还原数据库
restore database；

6、备库添加必要文件
查看v$standby_log决定是否需要添加standby logfile
–添加standby log文件（数量取决于(redo file number+1)*(thread number)）
alter database add standby logfile group 3 ‘/u01/oradata/xff/st_redo03.log’ size 5m reuse;
alter database add standby logfile group 4 ‘/u01/oradata/xff/st_redo04.log’ size 5m reuse;
alter database add standby logfile group 5 ‘/u01/oradata/xff/st_redo05.log’ size 5m reuse;

7、开启日志应用
alter database open;–11g支持redo apply状态下open
–开启归档日志应用
alter database recover managed standby database disconnect from session;
–开启实时应用
alter database recover managed standby database using current logfile disconnect from session;

发表在 Data Guard, Oracle备份恢复 | 评论关闭

Data Guard常用视图

发表于 2011 年 3 月 24 日 由 惜分飞

查看进程的活动状况—v$managed_standby
select process,client_process,sequence#,status from v$managed_standby;
PROCESS列显示进程信息
CLIENT_PROCESS列显示对应的主数据库中的进程
SEQUENCE#列显示归档redo的序列号
STATUS列显示的进程状态

确认redo应用进度—v$archive_dest_status
select dest_name,archived_thread#,archived_seq#,applied_thread#,recovery_mode, applied_seq#,db_unique_name from v$archive_dest_status where status=’VALID’;

检查归档文件路径及创建信息—v$archived_log
select name,creator,sequence#,applied,completion_time from v$archived_log;
select dest_id,max(sequence#) from v$archived_log group by dest_id;

查询归档历史—v$log_history

查询当前数据的基本信息—v$database信息

Data guard事件—v$dataguard_status
select message from v$dataguard_status;

发表在 Data Guard | 评论关闭

Data Guard switchover切换（Logical Standby Database）

发表于 2011 年 3 月 23 日 由 惜分飞

1)Verify it is possible to perform a switchover on the primary database.
SELECT SWITCHOVER_STATUS FROM V$DATABASE;–TO STANDBY/SESSIONS ACTIVE

2)Prepare the current primary database for the switchover
ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;–PREPARING SWITCHOVER
ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;–cancel（终止操作）

3)Prepare the target logical standby database for the switchover
ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;
–PREPARING DICTIONARY–>PREPARING SWITCHOVER
ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;–cancel（终止操作）

4)Ensure the current primary database is ready for the future primary
database’s redo stream
SELECT SWITCHOVER_STATUS FROM V$DATABASE;–TO LOGICAL STANDBY

5)Switch the primary database to the logical standby database role
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

6)Ensure all available redo has been applied to the target logical standby
database that is about to become the new primary database
SELECT SWITCHOVER_STATUS FROM V$DATABASE;–TO PRIMARY

7)Switch the target logical standby database to the primary database role
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

8)Start SQL Apply on the new logical standby database
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

发表在 Data Guard | 评论关闭