Category Archives: Data Guard

使用rman备份做Data Guard

Posted on by

前两天公司的一套oracle dg因为维护人员的疏忽到值出现gap，无法恢复，只能重做，因为该库每天都有rman的增量备份，所以选择最近的一次全部进行重做dg

1、主库
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/tmp/control01.ctl’;
alter system archive log current;

2、修改pfile文件（因为我是重做，所以不需要做）
见11g data guard（PHYSICAL STANDBY）配置

3、拷贝控制文件/rman文件到备库的相同位置
备份全备的内容（/opt/ORBS/data /tmp/control01.ctl)

4、复制数据库做dg
启动到nomount状态
rman target sys/srtsysdb@zjch_Action auxiliary /
duplicate target database for standby dorecover nofilenamecheck;

出现以下错误
RMAN-06025: no backup of log thread 1 seq 675 lowscn 1096519 found to restore
RMAN-06025: no backup of log thread 1 seq 674 lowscn 1095666 found to restore
RMAN-06025: no backup of log thread 1 seq 673 lowscn 1095663 found to restore
RMAN-06025: no backup of log thread 1 seq 672 lowscn 1095656 found to restore
RMAN-06025: no backup of log thread 1 seq 671 lowscn 1095081 found to restore
RMAN-06025: no backup of log thread 1 seq 670 lowscn 1092545 found to restore
RMAN-06025: no backup of log thread 1 seq 669 lowscn 1090351 found to restore
RMAN-06025: no backup of log thread 1 seq 668 lowscn 1088837 found to restore
RMAN-06025: no backup of log thread 1 seq 667 lowscn 1088834 found to restore
RMAN-06025: no backup of log thread 1 seq 666 lowscn 1088721 found to restore
原因：因为控制文件和备份文件中有时间间隔，所以rman要恢复，但是归档日志没有传输过来，所以不能恢复。

–到主库的归档目录查看这些日志是否存在
ls -l（如果存在，下面的操作恢复日志的操作可以忽略）
–查看这些日志是否存在
list backup of archivelog from sequence 666 until sequence 675;
–如果存在rman备份中，使用rman进行恢复
restore archivelog from sequence 666 until sequence 675;

如果存在相关联机日志就不需要添加
–添加redo log
alter database add logfile group 1 (‘/opt/oracle/oradata/zjch/redo01.log’) size 50m;
alter database add logfile group 2 (‘/opt/oracle/oradata/zjch/redo02.log’) size 50m;
alter database add logfile group 3 (‘/opt/oracle/oradata/zjch/redo03.log’) size 50m;
–添加sandby log
ALTER DATABASE ADD STANDBY LOGFILE (‘/opt/oracle/oradata/zjch/std_redo04.log’) SIZE 50M reuse;
ALTER DATABASE ADD STANDBY LOGFILE (‘/opt/oracle/oradata/zjch/std_redo05.log’) SIZE 50M reuse;
ALTER DATABASE ADD STANDBY LOGFILE (‘/opt/oracle/oradata/zjch/std_redo06.log’) SIZE 50M reuse;
ALTER DATABASE ADD STANDBY LOGFILE (‘/opt/oracle/oradata/zjch/std_redo07.log’) SIZE 50M reuse;

5、开启日志应用
–开启实时应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
–开启应用（根据配置和需求）
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
到这一步会如果主机端有所需的归档日志，会自动传输过来，并进行恢复，不用人工干预。

–查看alter文件，查询日志传输是否正常
–查看日志是否传输完成
select thread#, low_sequence#, high_sequence# from v$archive_gap;

–主库切换日志
alter system switch logfile;
–查询最大sequence
select max(sequence#) from v$archived_log;

–备库查看alert文件
tail -100 alert.log
Media Recovery Log /opt/oracle/oradata/zjch/archive/stdarch/1_46544_681145105.dbf
Media Recovery Log /opt/oracle/oradata/zjch/archive/stdarch/1_46545_681145105.dbf 有类似内容
–查询最大sequence
select max(sequence#) from v$archived_log;

–主库建立一个测试表
create table SCOTT.t_test(id number,name varchar2(10));
insert into SCOTT.t_test values(1,’aaaa’);
commit;
alter system switch logfile;
–查询最大sequence
select max(sequence#) from v$archived_log;

–备库检查
–查询最大sequence
select max(sequence#) from v$archived_log;
–取消日志应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
–只读模式打开数据库
alter database open read only;
–查询刚刚建的表和插入数据是否通报不成功
select * from SCOTT.t_test;

–重启备库，设置为日志应用模式
shutdown immediate;
startup mount;
–开启实时应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
–开启应用（根据配置和需求）
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

–主库删除测试表
drop table SCOTT.t_test;

rman创建Standby Data Guard

Posted on by

一、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常用视图

Posted on by

查看进程的活动状况—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 switchover切换（Logical Standby Database）

Posted on by

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;

11g data guard（LOGICAL STANDBY）配置

Posted on by

1、Create a Physical Standby Database
11g data guard（PHYSICAL STANDBY）配置

2、Stop Redo Apply on the Physical Standby Database
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3、Prepare the Primary Database to Support a Logical Standby Database
1）Prepare the Primary Database for Role Transitions

*.log_archive_dest_1='LOCATION=/u01/archive valid_for=(online_logfiles,all_roles) db_unique_name=first'
*.log_archive_dest_3='LOCATION=/u01/archive/standby VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=first'
*.log_archive_dest_state_3='enable'
--其他不修改

2）Build a Dictionary in the Redo Data
EXECUTE DBMS_LOGSTDBY.BUILD;

4、Transition to a Logical Standby Database
1）Convert to a Logical Standby Database
ALTER DATABASE RECOVER TO LOGICAL STANDBY db_name;
Note：
(1)如果数据库是open状态，需要重启，然后置于mount状态
(2)取消使用：RECOVER MANAGED STANDBY DATABASE CANCEL;
(3)如果执行到一半，然后取消，导致原物理库损坏（不能同步），从主库中重新生成备库的control file，然后启动物理库同步

2）Adjust Initialization Parameters for the Logical Standby Database

SHUTDOWN;
STARTUP MOUNT;
alter system set log_archive_dest_3='LOCATION=/u01/archive/standby VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=second';
alter system set log_archive_dest_1='LOCATION=/u01/archive valid_for=(online_logfiles,all_roles) db_unique_name=second';
alter system set LOG_ARCHIVE_DEST_STATE_3='ENABLE';

5、Open the Logical Standby Database
ALTER DATABASE OPEN RESETLOGS;
–开启实时应用
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
–开启应用
ALTER DATABASE START LOGICAL STANDBY APPLY;
–关闭应用
ALTER DATABASE STOP LOGICAL STANDBY APPLY;