在Linux如何搭建Oracle11g Data Guard

钉钉、微博极速扩容黑科技，点击观看阿里云弹性计算年度发布会！>>>

RHEL6/CentOS6搭建Oracle Data Guard

一、工作原理

Oracle Data Guard是甲骨文推出的一种高可用性数据库方案，Data Guard确保企业数据的高可用性，数据保护和灾难恢复，Data Gurad 通过冗余数据来提供数据保护，通过日志同步机制保证冗余数据和主数之前的同步，这种同步可以是实时，延时，同步，异步多种形式。在Data Gurad环境中，至少有两个数据库，一个处于Open状态对外提供服务，这个数据库叫作Primary Database。第二个处于恢复状态，叫作Standby Database。运行时primary Database对外提供服务，用户在Primary Database上进行操作，操作被记录在联机日志和归档日志中，这些日志通过网络传递给Standby Database。这个日志会在Standby Database上重演，从而实现Primary Database和Standby Database的数据同步。

Data Guard 允许定义3种数据保护模式，分别是最大保护（Maximum Protection），最大可用（Maximum Availability）和最大性能（Maximum Performance）。

1.最大保护（Maximum Protection）

这种模式主备库之间数据是同步的。即主库提交的同时，备库会做相应的恢复。最大限度的保证了数据完整性。不允许数据的丢失。如果主备库之间网络，或者备库出现问题会直接影响主库操作。导致主库宕机。

2.最高可用性（Maximum availability）

这种模式和\”最大保护\”基本上差不多。正常情况下，主备库之间是同步的。当网络或者备库出现问题时，不会影响到主库的宕机，主库会自动转换库\”最大性能\”模式，等待备库可用时，将归档传输到备库做恢复。可以把这种模式理解为\”最大保护\”和\”最大性能\”两种模式的中间体。

3.最高性能（Maximum performance）

这种模式保证主库性能最大化，主备库之间数据是异步传输的。即，主库日志归档以后才会传输到备用库，在备库上使用归档日志文件做恢复操作。

二、搭建环境

首先设置主备服务器时间同步参考：Linux修改时间方法如下

1.配置时间同步

#同步时间ntpdate ntp.aliyun.com

2.切换图形模式

如果没有图形界面的，配置yum源

#清楚yum仓库缓存 yum makecacheyum clean all#列出可用yum仓库yum repolist#列出程序组yum grouplist#安装图形化程序组yum -y groupinstall \"Server with GUI\"#启动图形界面startx#REHL7以下修改配置文件，以下为REHL7修改命令#查看当前运行模式systemctl get-default#设置当前运行模式systemctl set-default graphical.target#删除当前运行模式rm /etc/systemd/system/default.target#默认级别转换为3(文本模式)：ln -sf /lib/systemd/system/multi-user.target /etc/systemd/system/default.target#或者默认级别转换为5(图形模式)：ln -sf /lib/systemd/system/graphical.target /etc/systemd/system/default.target#重启：Reboot

3.主备数据库安装

省略

三、主库配置

1.开启归档模式

SQL> archive log list;SQL> shutdown immediate;SQL> startup mount;SQL> alter database archivelog;SQL> alter database open;SQL> archive log list;SQL> alter database force logging;SQL> select name,log_mode,force_logging from v$database;

2.创建standby redolog日志组

查看当前线程与日志组的对应关系及日志组的大小：

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

如上，这里有三组redo log，所以至少需要创建4组Standby redo log，大小均为50M：

SQL> alter database add standby logfile group 4(\'/home/app/oracle/oradata/orcl/standbyredo01.log\') size 50m;SQL> alter database add standby logfile group 5(\'/home/app/oracle/oradata/orcl/standbyredo02.log\') size 50m;SQL> alter database add standby logfile group 6(\'/home/app/oracle/oradata/orcl/standbyredo03.log\') size 50m;SQL> alter database add standby logfile group 7(\'/home/app/oracle/oradata/orcl/standbyredo04.log\') size 50m;

若要删除组：

SQL> alter database drop standby logfile group x;

查看standy日志组的信息：

SQL> select group#,sequence#,status, bytes/1024/1024 from v$standby_log;

3.创建主库密码文件

su - oracleorapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle force=y

4.配置spfile文件

查看spfile文件路径：

SQL> show parameter spfile;

用spfile创建一个pfile,用于修改：

SQL> create pfile=\'/tmp/initorcl.ora\' from spfile;

修改pfile文件

vim /tmp/initorcl.oraorcl.__db_cache_size=327155712orcl.__java_pool_size=4194304orcl.__large_pool_size=4194304orcl.__oracle_base=\'/home/app/oracle\'#ORACLE_BASE set from environmentorcl.__pga_aggregate_target=335544320orcl.__sga_target=499122176orcl.__shared_io_pool_size=0orcl.__shared_pool_size=155189248orcl.__streams_pool_size=0*.audit_file_dest=\'/home/app/oracle/admin/orcl/adump\'*.audit_trail=\'db\'*.compatible=\'11.2.0.0.0\'*.control_files=\'/home/app/oracle/oradata/orcl/control01.ctl\',\'/home/app/oracle/flash_recovery_area/orcl/control02.ctl\'*.db_block_size=8192*.db_domain=\'\'*.db_name=\'orcl\'*.db_recovery_file_dest=\'/home/app/oracle/flash_recovery_area\'*.db_recovery_file_dest_size=4070572032*.diagnostic_dest=\'/home/app/oracle\'*.dispatchers=\'(PROTOCOL=TCP) (SERVICE=orclXDB)\'*.log_archive_format=\'%t_%s_%r.dbf\'*.memory_target=833617920*.open_cursors=300*.processes=150*.remote_login_passwordfile=\'EXCLUSIVE\'*.undo_tablespace=\'UNDOTBS1\'#添加以下内容*.db_unique_name=\'orclpr\'*.fal_client=\'orclpr\'*.fal_server=\'orcldg\'*.standby_file_management=\'AUTO\'*.log_archive_config=\'DG_CONFIG=(orclpr,orcldg)\'*.log_archive_dest_1=\'location=/home/app/oracle/oradata/orcl/archivelog\'*.log_archive_dest_2=\'SERVICE=orcldg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg\'*.log_archive_dest_state_1=\'ENABLE\'*.log_archive_dest_state_2=\'ENABLE\'

复制pfile文件到spfile:

SQL> shutdown immediate;SQL> create spfile from pfile=\'/tmp/initorcl.ora\';SQL> startup;

5.修改监听文件，添加静态监听

vim $ORACLE_HOME/network/admin/listener.oraLISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))(ADDRESS = (PROTOCOL = IPC)(KEY = orcl))))LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.176)(PORT = 1521))(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))))SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = orcl)(ORACLE_HOME = /home/app/oracle/product/11.2.0/dbhome_1)(SID_NAME = orcl)))ADR_BASE_LISTENER = /home/app/oracleSAVE_CONFIG_ON_STOP_LISTENER = ON

重启监听服务：

SQL> lsnrctl stopSQL> lsnrctl start

6.编辑网络服务名配置文件tnsnames.ora

vim $ORACLE_HOME/network/admin/tnsnames.oraorclpr =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.176)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))orcldg =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.177)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))

tnsping测试：

[oracle@primary ~]$ sqlplus sys/oracle@orclpr as sysdba[oracle@primary ~]$ sqlplus sys/oracle@orcldg as sysdba[oracle@primary ~]$ tnsping orcldg[oracle@primary ~]$ tnsping orclpr

四、备库配置

1.将主库中的密码文件、pfile文件、监听文件复制到备库中

cd /home/app/oracle/product/11.2.0/dbhome_1/dbsscp orapworcl 192.168.3.177:/home/app/oracle/product/11.2.0/dbhome_1/dbs/scp /tmp/initorcl.ora 192.168.3.177:/tmp/cd /home/app/oracle/product/11.2.0/dbhome_1/network/adminscp listener.ora 192.168.3.177:/home/app/oracle/product/11.2.0/dbhome_1/network/admin/scp tnsnames.ora 192.168.3.177:/home/app/oracle/product/11.2.0/dbhome_1/network/admin/

2.配置spfile文件

修改pfile文件：

vim /tmp/initorcl.oraorcl.__db_cache_size=327155712orcl.__java_pool_size=4194304orcl.__large_pool_size=4194304orcl.__oracle_base=\'/home/app/oracle\'#ORACLE_BASE set from environmentorcl.__pga_aggregate_target=335544320orcl.__sga_target=499122176orcl.__shared_io_pool_size=0orcl.__shared_pool_size=155189248orcl.__streams_pool_size=0*.audit_file_dest=\'/home/app/oracle/admin/orcl/adump\'*.audit_trail=\'db\'*.compatible=\'11.2.0.0.0\'*.control_files=\'/home/app/oracle/oradata/orcl/control01.ctl\',\'/home/app/oracle/flash_recovery_area/orcl/control02.ctl\'*.db_block_size=8192*.db_domain=\'\'*.db_name=\'orcl\'*.db_recovery_file_dest=\'/home/app/oracle/flash_recovery_area\'*.db_recovery_file_dest_size=4070572032*.diagnostic_dest=\'/home/app/oracle\'*.dispatchers=\'(PROTOCOL=TCP) (SERVICE=orclXDB)\'*.log_archive_format=\'%t_%s_%r.dbf\'*.memory_target=833617920*.open_cursors=300*.processes=150*.remote_login_passwordfile=\'EXCLUSIVE\'*.undo_tablespace=\'UNDOTBS1\'#添加以下内容*.db_unique_name=\'orcldg\'*.fal_client=\'orcldg\'*.fal_server=\'orclpr\'*.standby_file_management=\'AUTO\'*.log_archive_config=\'DG_CONFIG=(orclpr,orcldg)\'*.log_archive_dest_1=\'location=/home/app/oracle/oradata/orcl/archivelog\'*.log_archive_dest_2=\'SERVICE=orclpr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclpr\'*.log_archive_dest_state_1=\'ENABLE\'*.log_archive_dest_state_2=\'ENABLE\'

复制pfile文件到spfile：

SQL> create spfile from pfile=\'/tmp/initorcl.ora\';

3.修改监听文件

vim $ORACLE_HOME/network/admin/listener.oraSID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = orcl)(ORACLE_HOME = /home/app/oracle/product/11.2.0/dbhome_1)(SID_NAME = orcl)))LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.177)(PORT = 1521))(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))))ADR_BASE_LISTENER = /home/app/oracle

编辑网络服务名配置文件tnsnames.ora

vim $ORACLE_HOME/network/admin/tnsnames.oraorcldg =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.177)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))orclpr =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.176)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))

重启监听：

SQL> lsnrctl stopSQL> lsnrctl start

4.tnsping测试

[oracle@standby ~]$ tnsping orclpr[oracle@standby ~]$ tnsping orcldg

5.手工创建所需的目录

su - oraclemkdir -p /home/app/oracle/admin/orcl/adumpmkdir -p /home/app/oracle/admin/orcl/dpdumpmkdir -p /home/app/oracle/admin/orcl/pfilemkdir -p /home/app/oracle/oradata/orclmkdir -p /home/app/oracle/flash_recovery_area/orclmkdir -p /home/app/oracle/oradata/orcl/archivelog

6.启动备库到nomount

SQL> shutdown immediate;SQL> startup nomount;

五、数据恢复

1.配置参数检查

SQL> select status from v$instance;SQL> show parameter db_unique_name;

primary：orclpr
standby：orcldg

SQL> show parameter compatible;SQL> show parameter log_archive_config;SQL> show parameter log_archive_dest_1;SQL> show parameter log_archive_dest_state_2;SQL> show parameter db_file_name_convert;--SQL> alter system set db_file_name_convert=\'/home/app/oracle/oradata/orcl\',\'/home/app/oracle/oradata/orcl\' scope=spfile;SQL> show parameter log_file_name_convert;--SQL> alter system set log_file_name_convert=\'/home/app/oracle/oradata/orcl\',\'/home/app/oracle/oradata/orcl\' scope=spfile;SQL> show parameter standby;SQL> show parameter log_archive_format;--SQL> alter system set LOG_ARCHIVE_FORMAT=\'ARC_%T_%S_%R.arc\' scope=both;SQL> set pagesize 100;SQL> set linesize 120;SQL> shutdown immediate;SQL> startup mount;SQL> alter database archivelog;SQL> alter database open;SQL> archive log list;

2.利用RMAN在主库上恢复备库

rman target sys/oracle@orclpr auxiliary sys/oracle@orcldgduplicate target database for standby from active database nofilenamecheck;#备注：rman target sys/oracle@orclpr auxiliary sys/oracle@orcldg nocatalog#恢复过程如下：[oracle@oracle11gstandby admin]$ rman target sys/oracle@orclpr auxiliary sys/oracle@orcldgduplicate target database for standby from active database nofilenamecheck;

3.登陆备库并查看数据库当前状态

[oracle@standby ~]$ sqlplus / as sysdba

SQL> select status from v$instance;

RMAN恢复完直接就是mount状态。

4.备库启动日志应用

SQL> alter database recover managed standby database using current logfile disconnect from session;SQL> select sequence#,applied from v$archived_log order by 1;

5.分别查看主库和备库的归档序列号是否一致

先在主库手动切换一下日志再查看

SQL> alter system switch logfile;SQL> archive log list;

再在备库上查看：

SQL> archive log list;

6.查看备库中各文件如下

[oracle@standby orcl]$ ll[oracle@standby archivelog]$ ll

到此，dataguard已部署完成，可以测试是否成功！
建议用scott用户测试，insert、delete、update、select

六、自动启动

1.Oracle DG物理备库在数据库重启后，不能自动对日志进行应用，可通过以下触发器来让数据库应用日志。

CREATE OR REPLACE TRIGGER STANDBY_APPLY_LOGAFTER STARTUP ON DATABASEBEGINDECLAREDATABASE_ROLE VARCHAR(20);BEGINSELECT DATABASE_ROLE INTO DATABASE_ROLE FROM V$DATABASE;IF DATABASE_ROLE = \'PHYSICAL STANDBY\' THENEXECUTE IMMEDIATE \'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION\';ELSEDBMS_OUTPUT.PUT_LINE(DATABASE_ROLE);END IF;END;END STANDBY_APPLY_LOG;

七、主备切换

1.检查主备的环境

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

验证备库是否有gap

SQL> select t.status,t.gap_status from v$archive_dest_status t where t.dest_id=\'2\';

2.在主库上执行切换命令(primary节点)

SQL> alter database commit to switchover to physical standby;--备库启动日志应用SQL> alter database commit to switchover to physical standby with session shutdown;SQL> shutdown immediate;SQL> startup mount;SQL> select name,switchover_status,database_role from v$database;

查看目前primary角色的状态

3.在备库上执行(standby节点)

SQL> select name,switchover_status,database_role from v$database;--查看standby备库角色的状态SQL> alter database commit to switchover to primary with session shutdown;--将备库的角色修改为primarySQL> select name,switchover_status,database_role from v$database;

打开数据库

SQL> alter database open;--修改以前主库为日志应用--备库启动日志应用SQL> alter database recover managed standby database using current logfile disconnect from session;

4.测试主备节点是否切换成功

测试省略
如果是正式库，数据库运行时间长，切换的时间会很长。主备库：

SQL> alter system flush buffer_cache;SQL> alter system checkpoint;SQL> shutdown immediate;

八、参考说明

1.参考文档

参考文档是英文的，可以翻译成中文阅读，有时候翻译的不太准确…
http://docs.oracle.com/cd/B19306_01/server.102/b14239/create_ps.htm

2.常用命令

--Primary:SELECT T.NAME,T.OPEN_MODE,T.DATABASE_ROLE,T.PROTECTION_MODE,T.PROTECTION_LEVEL FROM V$DATABASE T;--Standby:SELECT T.NAME,T.OPEN_MODE,T.DATABASE_ROLE,T.PROTECTION_MODE,T.PROTECTION_LEVEL FROM V$DATABASE T;

备注：如果OPEN_MODE=WITH APPLY在备库上执行

SQL> alter database recover managed standby database using current logfile disconnect from session;SQL> SELECT * FROM V$MANAGED_STANDBY;

3.启动说明

关闭数据库：主库–>备库
启动数据库：备库–>主库