DATAGUARD配置方案


方案背景:

        广州知而言教育机构,其数据库运行在单实例环境,为了保障数据的安全以及服务器的高可用稳定特性,我方为其设计了Dataguard双机热备方案,下面是方案的具体实施内容:

一、    DATAGUARD体系架构图

33.jpg

数据库节点IP

服务名

主备类型

192.168.0.154

orcl

主库(节点1

192.168.0.153

orclbak

备库(节点2



二、    配置步骤

1.        首先在节点1dbca创建实例,安装时启用归档模式,节点2不安装实例,只装数据库软件即可,如果节点1是现成运行的数据库,则不需要新建;


2.        把主节点1TNSNAMES.ORA配置成如下,并把该文件复制到备节点2的相同目录下:

    cd   /data/oracle/app/product/11.2.0/db_1/network/admin,打开TNSNAMES.ORA,配置如下:

ORCL=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.0.154)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = ORCL)

      (UR=A)

      )

  )


ORCLBAK =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.0.153)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orclbak)

      (UR=A)   

    )

  )

:这里要注意红色部分,要根据自己的情况对IP和服务名进行调整;


3.        配置两个节点的监听:

在同一目录下,打开listener.ora设置如下(用现成的监听文件即可,无需修改)

主节点1设置:

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = db_primary)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))

    )

  )


ADR_BASE_LISTENER =/data/oracle/app

红色部分位节点1的主机名,确认好配置后把这个监听文件拷贝到节点2相同的目录下(cd   /data/oracle/app/product/11.2.0/db_1/network/admin),并调整:

备节点2设置:

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL =TCP)(HOST = unsedu-app1)(PORT = 1521))

      (ADDRESS = (PROTOCOL =IPC)(KEY = EXTPROC1521))

    )

  )


ADR_BASE_LISTENER = /data/oracle/app

注:这里将红色的主机名改为节点2的主机名


4.        主节点设置强制写日志

SQL> selectFORCE_LOGGING from v$database;

NO

SQL> alter database force logging;

SQL> select FORCE_LOGGING from v$database;

YES


5.        把主节点的密码文件拷到备节点相同的目录下(名字要一样,别改名,本例是pwdorcl.ora文件):

cd   /data/oracle/app/product/11.2.0/db_1/dbs

scp   orapworcl   oracle@192.168.0.153:/data/oracle/app/product/11.2.0/db_1/dbs


6.        主节点创建PFILE并增加修改如下:

SQL>create pfile from spfile;

cd   /data/oracle/app/product/11.2.0/db_1/dbs

找到initorcl.ora文件:

orcl.__db_cache_size=10737418240

orcl.__java_pool_size=134217728

orcl.__large_pool_size=201326592

orcl.__oracle_base='/data/oracle/app'#ORACLE_BASE set fromenvironment

orcl.__pga_aggregate_target=8724152320

orcl.__sga_target=12952010752

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=1677721600

orcl.__streams_pool_size=67108864

*.audit_file_dest='/data/oracle/app/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/data/oracle/app/oradata/orcl/control01.ctl','/data/oracle/app/fast_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='/data/oracle/app/oradata/orcl/','/data/oracle/app/oradata/orcl/'

*.db_name='orcl'

*.db_recovery_file_dest='/data/oracle/app/fast_recovery_area'

*.db_recovery_file_dest_size=43851448320

*.diagnostic_dest='/data/oracle/app'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=1000

*.memory_target=21641560064

*.open_cursors=300

*.processes=1500

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=1655

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

--下面为增加的内容:

DB_UNIQUE_NAME=orcl

LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclbak)'

log_archive_dest_1='LOCATION=/data/oracle/app/fast_recovery_area/ORCL VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=orcl'

LOG_ARCHIVE_DEST_2='SERVICE=orclbak   LGWR ASYNC affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=orclbak'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=orclbak

FAL_CLIENT=orcl

STANDBY_FILE_MANAGEMENT=AUTO


7.        在备端创建几个跟主端同样的目录:

[oracle@unsedu-app1 admin]$ mkdir -p /data/oracle/app/fast_recovery_area/ORCL/archivelog

[oracle@unsedu-app1 admin]$mkdir -p /data/oracle/app/admin/orcl/adump

[oracle@unsedu-app1 admin]$mkdir -p /data/oracle/app/admin/orcl/dpdump

[oracle@unsedu-app1 admin]$mkdir -p /data/oracle/app/admin/orcl/pfile

[oracle@unsedu-app1 admin]$mkdir -p /data/oracle/app/oradata/orcl

[oracle@unsedu-app1 admin]$mkdir -p /data/oracle/app/oradata/standby_log

然后回到主库创建STANDBY控制文件并拷贝至备端的相同目录

SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/control01.ctl';

然后把/home/oracle/control01.ctl复制到备端的/data/oracle/app/oradata/orcl



8.        根据6的参数文件内容,拷贝到到备端,并增加修改内容如下

(记住pfile文件名要一样,也是叫initorcl.ora):

备库的参数文件如下:

orcl.__db_cache_size=10737418240

orcl.__java_pool_size=134217728

orcl.__large_pool_size=201326592

orcl.__oracle_base='/data/oracle/app'#ORACLE_BASE set fromenvironment

orcl.__pga_aggregate_target=8724152320

orcl.__sga_target=12952010752

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=1677721600

orcl.__streams_pool_size=67108864

*.audit_file_dest='/data/oracle/app/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/data/oracle/app/oradata/orcl/control01.ctl'   --这里注意,备端这里就一个控制文件路径即可

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='/data/oracle/app/oradata/orcl/','/data/oracle/app/oradata/orcl/'

*.db_name='orcl'

*.db_recovery_file_dest='/data/oracle/app/fast_recovery_area'

*.db_recovery_file_dest_size=43851448320

*.diagnostic_dest='/data/oracle/app'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=1000

*.memory_target=21641560064

*.open_cursors=300

*.processes=1500

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=1655

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

--调整以下内容:

DB_UNIQUE_NAME=orclbak

LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclbak)'

log_archive_dest_1='LOCATION=/data/oracle/app/fast_recovery_area/ORCLVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'

LOG_ARCHIVE_DEST_2= 'SERVICE=orcl   LGWR ASYNC affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=orcl'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=orcl

FAL_CLIENT=orclbak

STANDBY_FILE_MANAGEMENT=AUTO


9.        关闭主端数据库并把数据文件拷贝至备端的相同目录下:

SQL>shutdown immediate

然后把主端的/data/oracle/app/oradata/orcl/目录复制到备端同样的目录下,这里千万要注意,控制文件千万别复制过去,因为步骤7已将standby 控制文件复制过去;


10.    在主,备端同时建立standby重做日志:

主端此时OPEN数据库,然后创建以下日志:

Sqlplus / as sysdba

create spfile from pfile;   --启动前,这一步千万要先做

SQL>statup

创建standby重做日志:

ALTER DATABASE ADD STANDBY LOGFILE group 4('/data/oracle/app/oradata/standby_log/slog1.rdo') SIZE100M;

ALTER DATABASE ADD STANDBYLOGFILE group 5('/data/oracle/app/oradata/standby_log/slog2.rdo') SIZE 100M;

ALTER DATABASE ADD STANDBYLOGFILE group 6('/data/oracle/app/oradata/standby_log/slog3.rdo') SIZE 100M;

ALTER DATABASE ADD STANDBYLOGFILE group 7('/data/oracle/app/oradata/standby_log/slog4.rdo') SIZE 100M;



备端先mount起来再创建:

SQL>startupnomount

SQL>alterdatabase mount;

ALTER DATABASE ADD STANDBY LOGFILE group 4('/data/oracle/app/oradata/standby_log/slog1.rdo')SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE group 5('/data/oracle/app/oradata/standby_log/slog2.rdo')SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE group 6('/data/oracle/app/oradata/standby_log/slog3.rdo')SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE group 7('/data/oracle/app/oradata/standby_log/slog4.rdo')SIZE 100M;




11.     备端进行同步操作

SQL>shutdown immediate;   

SQL>startup nomount;

SQL>alter database mount standby database;

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASEUSING CURRENT LOGFILE DISCONNECT FROM SESSION;

然后退出SQL模式,启动监听:

lsnrctl   start (主备库也要检查,同时检查防火墙有没开启,确保关闭状态) –此步骤主要是看监听有没起来,可用 lsnrctl   status 进行状态检查;

至此,同步操作已完成,下面检查同步状态


此时先用步骤1213查看DG是否同步正常,如正常(这里判断的方法是:用步骤13语句在主端看是否有LNS字样的同步日志出现,如出现ERROR则需要诊断不同步的原因,另外用步骤12在备端查看最新日期归档日志是否处于YES的应用状态),如上述两个步骤的检查都正常,证明同步已生效,接下来可进行下列操作切换到OPEN状态下同步:


下面这步是可选的,不一定要open状态下同步(上述mount状态下同步也可以),但11G支持open状态下同步(增加了只读功能),以下是操作步骤:

要切换到OPEN READONLY状态下的操作:

SQL>alterdatabase recover managed standby database cancel;

SQL>alter database open read only;

接着在open readonly状态下进行日志同步:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENTLOGFILE DISCONNECT FROM SESSION;

这样就可以既可以实时查数据,也可以同步数据。

--到此配置完毕,以下为内容为维护DATAGUARD使用:


12.     查看备库日志应用状态:

SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,applied FROM V$ARCHIVED_LOGORDER BY SEQUENCE#;


13.     查看主库日志归档情况:

SQL>set lines 180

SQL>col message format a70

SQL>select* from V$DATAGUARD_STATUS;


14.     查看数据库主备状态:

SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;


15.     查看备库standby_log状态:

SQL>select thread#,sequence#,used,archived,status fromv$standby_log;


16.     查看归档文件是否连续

SQL> select thread#,low_sequence#,high_sequence# fromv$archive_gap;



17.     服务器重启主备库启动操作:

主库服务器重启:

1,   启动监听(oracle用户操作):lsnrctl   start

2,   启动数据库(oracle用户操作):

sqlplus / as sysdba   进入SQL模式下启动数据库:

SQL>startup


从库服务器重启:

1,   启动监听(oracle用户操作):lsnrctl   start

2,   数据库设置同步(oracle用户操作):

sqlplus / as sysdba   进入SQL模式下:

SQL>startup nomount;

SQL>alter database mount standbydatabase;

SQL>ALTER DATABASE RECOVER MANAGED STANDBYDATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

要切换到OPEN READ ONLY状态下的操作:

SQL>alter databaserecover managed standby database cancel;

SQL>alter database openread only;

接着在open read only状态下进行日志同步:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASEUSING CURRENT LOGFILE DISCONNECT FROM SESSION;

这样就可以既可以实时查数据,也可以同步数据。



18.     主从库切换操作

(一)     Switchover

注:用于有准备的、计划之中的切换,通常是系统升级、数据迁移等常态任务,可用于切换测试:

1、检查主备库状态

确认主、备库正常运行:

查看主库状态为OPEN,备库为MOUNT,备库能顺利接收到主库日志,详情可参考上述12,13步骤。

2、切换主库为备库(确保上述步骤1检查同步状态正常)

SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TO STANDBY (或sessionactive)

SQL> alter database commit to switchover to physical standbywith session shutdown;

Databasealtered.

执行这句后,此时主库会自动关闭!

3、切换备库为主库

SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TOPRIMARY(或sessionactive)

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Databasealtered.

SQL>SHUTDOWNIMMEDIATE

SQL>STARTUP

Databasemounted.

Databaseopened.

4、新备库启用日志应用同步(原来主库)

登录数据库Sqlplus / as sysdba 执行:

STARTUP MOUNT

ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

切换至open状态下同步(可选);

SQL>alter database recovermanaged standby database cancel;

SQL>alter database openread only;

接着在open read only状态下进行日志同步:

ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

5、最后查看主库状态

SQL>selectDATABASE_ROLE, PROTECTION_MODE, SWITCHOVER_STATUS, FLASHBACK_ON from v$database;

DATABASE_ROLE   PROTECTION_MODE      SWITCHOVER_STATUS   FLASHBACK_ON

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

PRIMARY         MAXIMUM PERFORMANCE   TO STANDBY          NO

(二)     Failover备库切换

注:用于意料之外的突发情况,比如异常掉电、自然灾难等等。不能用于测试,不能回切。

备库操作(此时主库已挂):

1、 终止日志reply操作

SQL>alter database recover managed standby databasefinish;

Databasealtered

2、 检查数据库的状态为“ TO PRIMARY”即可(TO PRIMARY说明是备库)

SQL>select open_mode, switchover_status from v$database;

OPEN_MODE           SWITCHOVER_STATUS

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

READONLY            TO PRIMARY

3、 切换备库为主库角色

SQL> alter database commit to switchover to primary withsession shutdown;

Databasealtered.

SQL>select open_mode from v$database;

OPEN_MODE

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

MOUNTED

SQL>alter database open;

Databasealtered.


至此,Oracle dataguard双机热备方案配置完毕!