Oracle DataGuard是Oracle自带的数据同步功能,基本原理是将日志文件从原数据库传输到目标数据库,然后在目标数据库上应用这些日志文件,从而使目标数据库与源数据库保持同步,是一种数据库级别的高可用性方案。

oracle dg 搭建方法两种:

    1.DB停机,拷贝所以文件到DG库,影响业务

    2.DB不停机,rman热备方式拷贝到DG库,不影响业务。

oracle dg 模式三种:

    1.最大保护 
    这种模式是默认的数据保护模式,在不影响源数据库性能的条件下提供尽可能高的数     
据保护等级。在该种模式下,一旦日志数据写到源数据库的联机日志文件,事务即可提交,不必等待日志写到目标数据库,如果网络带宽充足,该种模式可提供类似于最大可用模式的数据保护等级。 
    2.最大可用性 
这种模式和”最大保护”基本上差不多。正常情况下,主备库之间是同步的。 
当网络或者备库出现问题时,不会影响到主库的当机,主库会自动转换库”最大性能”模式,等待备库可用时,将归档传输到备库做恢复。 
    3.最大性能 
这种模式保证主库性能最大化,主备库之间数据是异步传输的。即,主备日志归档以 
后才会传输到备用库,在备库上使用归档日志文件做恢复操作。



热备方式搭建DG库:

主库配置:

startup mount;

开启归档:

alter database archivelog;

开启强制归档force logging(默认0秒):

alter database force logging; 

开启闪回(前提开启归档):

设置闪回区大小:#alter system set db_recovery_file_dest_size='5G'; 

设置闪回区目录:#alter system set db_recovery_file_dest='/data/db_recovery_file_dest/';

开启闪回:alter database flashback on; 



#设置归档日志,默认位置 USE_DB_RECOVERY_FILE_DEST,查询位置show parameter DB_RECOVERY_FILE_DEST  :

#alter system set  log_archive_dest_1='location=/data/JINGYU/archivelog';


#设置强制归档时间为30分钟:

#alter system set archive_lag_target=1800;


查看是否开启:

archive log list;

select  FLASHBACK_ON    from v$database;

select force_logging from v$database;


添加STANDBY 日志文件:

查询主库在线日志的大小和组数:

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

查询备库在线日志的大小和组数:

select group#,bytes/1024/1024 from v$standby_log;

创建standby logfile(主库log+1)

alter database add standby logfile group 4 '/data/zy/onlinelog/redo11_stb01.log' size 50M;

alter database add standby logfile group 5 '/data/zy/onlinelog/redo11_stb02.log' size 50M;

alter database add standby logfile group 6 '/data/zy/onlinelog/redo11_stb03.log' size 50M;

alter database add standby logfile group 7 '/data/zy/onlinelog/redo11_stb04.log' size 50M;


参数文件修改:

cat >> alterspfile.sql <<EOF

alter system set log_archive_config='DG_CONFIG=(primary,standby)';

alter system set log_archive_dest_2='SERVICE=standby arch   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=spfile;

alter system set log_archive_dest_state_1='enable';

alter system set log_archive_dest_state_2='enable';

alter system set db_file_name_convert='/data/zy/datafile','/data/zy/datafile' scope=spfile;

alter system set log_file_name_convert='/data/zy/onlinelog','/data/zy/onlinelog' scope=spfile;

alter system set fal_client='primary';

alter system set fal_server='standby';

alter system set standby_file_management='AUTO';

EOF


mkdir /data/zy/onlinelog /data/zy/datafile  -p

chown oracle.oinstall /data/zy/onlinelog /data/zy/datafile -R


shutdown immediate

startup


配置监听,TNS文件:

动态:listener.ora--测试数据库启动到nomount状态监听

LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))(ADDRESS=(PROTOCOL=TCP)(HOST=2.2.2.11)(PORT=1521))))

ADR_BASE_LISTENER=/u01/app/oracle


静态:listener.ora


SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=zy)(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)(SID_NAME=jingyu)))


TNS文件配置:tnsnames.ora--类似hosts,指定实例名与IP解析

primary=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.64.50)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=zy)))

standby=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.64.60)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=zy)))


lsnrctl stop ; lsnrctl start ;


创建pfile并拷贝到standby

create pfile='/tmp/initzy.ora' from spfile;

scp /tmp/initzy.ora 2.2.2.11:/u01/app/oracle/product/11.2.0/dbhome_1/dbs

拷贝密码文件到standby

scp orapwjingyu 2.2.2.11:/u01/app/oracle/product/11.2.0/dbhome_1/dbs



备库配置:


修改pfile文件:

*.fal_client='standby'

*.fal_server='primary'


*.log_archive_dest_2='SERVICE=primary VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'




#修改密码文件


#mv orapwjingyu orapwjingyus


创建pfile配置里相应文件夹


mkdir -p /u01/app/oracle/admin/zy/adump


mkdir -p /u01/app/oracle/oradata/zy/


mkdir -p /u01/app/oracle/fast_recovery_area/zy/


mkdir -p /data/zy/datafile


mkdir -p /u01/app/oracle/fast_recovery_area


mkdir -p /data/zy/onlinelog


chown oracle.oinstall  /u01/app/oracle/admin/zy/adump /u01/app/oracle/oradata/zy/  /u01/app/oracle/fast_recovery_area/zy/ /data/zy/datafile /u01/app/oracle/fast_recovery_area /data/zy/onlinelog






修改监听文件

SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=zy)(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)(SID_NAME=jingyu)))

LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))(ADDRESS=(PROTOCOL=TCP)(HOST=2.2.2.11)(PORT=1521))))

ADR_BASE_LISTENER = /u01/app/oracle

修改TNS




primary=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=2.2.2.10)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=zy)))


standby=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=2.2.2.11)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=zy)))


lsnrctl stop ; lsnrctl start ;


启动数据库到nomount状态


startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initjingyus.ora';


create spfile from pfile;   #创建spfile


验证监听和TNS配置


tnsping primaryt 


tnsping standby




主备执行


sqlplus sys/oracle@primary as sysdba


sqlplus sys/oracle@standby as sysdba


备库数据恢复:

 rman target sys/oracle@primary auxiliary sys/oracle@standby

     duplicate target database for standby nofilenamecheck dorecover;  

rman>duplicate target database for standby from active database nofilenamecheck;



Finished Duplicate Db at 2018-06-25 xx xx xx  ---------------正常完成.



备库开启归档,闪回,强制归档。

startup nomount

alter database mount standby database;  

alter database archivelog;

alter system set db_recovery_file_dest_size='5G'; 

alter database flashback on;  

alter database open read only;  

alter database recover managed standby database using current logfile disconnect from session;    #开启实时同步





验证:

归档日志有无报错

select dest_name,error from v$archive_dest; 

 查询主库最大归档序号(scn)一致即归档同步成功。

select max(sequence#) from v$archived_log;

日志切换

alter system archive log current;

select max(sequence#) from v$archived_log;

创建测试表:





切换测试


3.5.1 switchover


switchover是用户有计划的进行停机切换,能够保证不丢失数据,下面我们来看下switchover是怎样操作的:


主库上操作:


select switchover_status,database_role from v$database; 


SWITCHOVER_STATUS    DATABASE_ROLE


-------------------- ----------------


TO STANDBY      PRIMARY


 


SQL>


注意:上面查询结果为TO STANDBY 或 SESSIONS ACTIVE表明可以进行切换


 


SQL> alter database commit to switchover to physical standby;


 


Databasealtered.


SQL> startup mount


ORACLE instance started.


 


Total System Global Area  688959488 bytes


Fixed Size          2256432 bytes


Variable Size        566231504 bytes


Database Buffers     117440512 bytes


Redo Buffers           3031040 bytes


Database mounted.


SQL> select database_role from v$database;


 


DATABASE_ROLE


----------------


PHYSICAL STANDBY


 


备库上操作:


SQL> select switchover_status,database_role from v$database;


 


SWITCHOVER_STATUS    DATABASE_ROLE


------------------------------------


TO PRIMARY       PHYSICAL STANDBY


 


SQL>


注意:上面查询结果显示为TO PRIMARY 或 SESSIONS ACTIVE表明可以切换成主库;


现在可以把备库切换成主库:


SQL> alter database commit to switchover to primary with session shutdown;


 


Database altered.


 


SQL> alter database open;


 


Database altered.


 


SQL> select switchover_status,database_role,open_mode from v$database;


 


SWITCHOVER_STATUS    DATABASE_ROLE    OPEN_MODE


-------------------- ------------------------------------


SESSIONS ACTIVE      PRIMARY         READ WRITE


 


记住:这时候需要在现在的备库(原先的主库)开启实时同步


 


SQL> alter database open;


 


Database altered.


 


SQL> alter database recover managed standby database using current logfile disconnect from session;


 


Database altered.


 


到此DG switover切换完成,验证方法同上。