高可用性数据库方案DataGuard部署

DataGuard是甲骨文推出的一种高可用性数据库方案，是一种数据库级别的HA方案，最主要功能是容灾、数据保护、故障恢复等。

1，服务器资源-相同操作系统，硬件资源，安装Oracle
主服务器：xx.xx.xx.70
备服务器：xx.xx.xx.71

2，DB_UNIQUE_NAME区分主备库标识
db_unique_name： primary库（orcl）、standby库（orcls）

alter system set db_unique_name='orcls' scope=both;
show parameter db_unique_name

主服务器搭建测试数据库：taxtestdev

sqlplus / as sysdba
create tablespace taxtest datafile 'D:\app\administrator\oradata\orcl\taxtest.dbf' size 100m  autoextend on  next 50m maxsize unlimited  extent management local;
create user taxtestdev identified by FFF default tablespace taxtest temporary tablespace temp;
grant connect,resource,dba to taxtestdev;

3，配置监听器-略
4，配置tns文件
主库：

ORCLS=(DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.71)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = ORCL)
        )
    )

备库：

ORCLS=(DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.70)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = ORCL)
        )
    )
    

5，主库设置归档模式

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;

设置数据库口令文件的使用模式

alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
shutdown immediate;
startup;

6，授权处理

orapwd FILE=D:\app\administrator\product\11.2.0\dbhome_1\database\pwdorcl.ora password=pwdxxxx entries=5;

#将密钥文件传输到备库相同位置

7，创建与主库同样数量和大小的standby logfile
#查询主库

select thread#,group#,members,bytes/1024/1024 from v$log;

#备库创建standby logfile

alter database add standby logfile group 11('D:\app\administrator\oradata\orcl\redo01_stb.log') size 50M;
alter database add standby logfile group 12('D:\app\administrator\oradata\orcl\redo02_stb.log') size 50M;
alter database add standby logfile group 13('D:\app\administrator\oradata\orcl\redo03_stb.log') size 50M;
alter database add standby logfile group 14('D:\app\administrator\oradata\orcl\redo04_stb.log') size 50M;
select group#,thread#,sequence#,archived,status from v$standby_log;

显示格式,每行数据长度，每次显示的行数；

set linesize 300;
set pagesize 30;

8，参数设置
主库参数设置

alter system set log_archive_config='dg_config=(orcl,orcls)' scope=BOTH;
alter database set standby database to maximize availability;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=D:\app\administrator\oradata\orcl\archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=orcl' SCOPE=spfile;
alter system set log_archive_dest_2='SERVICE=orcls LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orcls' SCOPE=spfile;
alter system set log_archive_dest_state_1=enable scope=spfile;

--启用日志传送

alter system set log_archive_dest_state_2=enable scope=spfile;
alter system set FAL_SERVER='orcl' SCOPE=spfile;
alter system set FAL_CLIENT='orcl' SCOPE=spfile;

--进程数

alter system set log_archive_max_processes=30 scope=both;

--自动文件管理模式

alter system set standby_file_management=auto scope=spfile;

--启用OMF功能：

alter system set db_create_file_dest='D:\app\administrator\oradata\orcl\archivelog' scope=spfile;

--主备文件存放路径转换

alter system set db_file_name_convert='D:\app\administrator\oradata\orcl','D:\app\administrator\oradata\orcl' scope=spfile;
alter system set log_file_name_convert='D:\app\administrator\oradata\orcl','D:\app\administrator\oradata\orcl' scope=spfile;

--配置取消延迟

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

备库参数设置

alter system set LOG_ARCHIVE_DEST_1='' SCOPE=spfile;
alter system set log_archive_dest_2='SERVICE=orcls LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orcl' SCOPE=spfile;
alter system set log_archive_dest_state_1=enable scope=spfile;
alter system set log_archive_dest_state_2=enable scope=spfile;
alter system set FAL_SERVER='orcl' SCOPE=spfile;
alter system set FAL_CLIENT='orcl' SCOPE=spfile;
alter system set log_archive_max_processes=30 scope=both;
alter system set standby_file_management=auto scope=spfile;
alter system set db_create_file_dest='D:\app\administrator\oradata\orcl\archivelog' scope=spfile;
alter system set db_file_name_convert='D:\app\administrator\oradata\orcl','D:\app\administrator\oradata\orcl' scope=spfile;
alter system set log_file_name_convert='D:\app\administrator\oradata\orcl','D:\app\administrator\oradata\orcl' scope=spfile;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

显示参数

show parameter log_archive_dest_state_2

9，复制数据库
备库进入nomount：

shutdown immediate;
startup nomount;

主库目录下cmd执行：
 

rman target sys/pwdxxxx@orcl auxiliary sys/pwdxxxx@orcls nocatalog
RMAN>
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

10，备库启动实时应用

shutdown immediate
startup mount;
alter database recover managed standby database using current logfile disconnect from session;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

11，检查命令

set linesize 300
col name for a60

--查看主备库状态：

select database_role,open_mode from v$database;

--主备库检查当前日志序号一致

archive log list;

--切换日志检查是否同步

ALTER SYSTEM SWITCH LOGFILE;

--查询主备日志

select thread#,max(sequence#) from v$archived_log group by thread#;

--查询监控视图

select process,status,client_process,thread#,sequence# from v$managed_standby;

--检查同步进程执行情况

SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

--检查alert日志文件
-----------------------------------------------------------------------------------------------------
测试脚本

create table taxtestdev.test as select level id,'lottu' name from dual connect by level <= 3;
select * from taxtestdev.test;

insert into taxtestdev.test(aa) values('55');
commit;
select * from taxtestdev.test;

-----------------------------------------------------------------------------------------------------
问题：alert日志中出现Private Strand Flush Not Complete的处理方法
处理：

alter system set db_writer_processes=4 scope=spfile;

-----------------------------------------------------------------------------------------------------
问题：ORA-00600[kcratr_scan_lastbwr]错误处理
处理：恢复文档

shutdown immediate
startup mount
recover database
alter database open

-----------------------------------------------------------------------------------------------------
问题：开启实时应用redo报错ora-01153
处理：

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

-----------------------------------------------------------------------------------------------------
问题：ORA-16055: FAL request rejected问题出现，日志无法正常传输。
处理：检查log_archive_dest_2是否配置正确，网络是否畅通；
-----------------------------------------------------------------------------------------------------
问题：ORA-16047: DGID mismatch between destination setting and standby
处理：备库执行db_unique_name错误，修改

alter system set db_unique_name='orcls' scope=both;
show  parameter db_unique_name

重启数据库
-----------------------------------------------------------------------------------------------------
问题：ora-10456：cannot open standby database; media recovery session may be in progress
处理：备库执行

alter database recover managed standby database cancel;
ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

-----------------------------------------------------------------------------------------------------
问题：备库日志序号为0，备库日志无法接收，无法应用等
处理：主库执行，连接 RMAN 清空日志
删除所有归档日志

rman target /  
RMAN> crosscheck archivelog all;  
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE'; 

清除记录：
 

select count(*) from v$archived_log;
execute sys.dbms_backup_restore.resetCfileSection(11);
select count(*) from v$archived_log;