Datagard算是Oracle企业版的一种容灾方案,在企业中广泛应用,我就将搭建过程记录下来以作备用。

主机名版本实例名IP

db1 11G R2member172.16.1.250

db2Oracle 11G R2member172.16.1.251

默认情况下以上都已经安装好了Oracle数据库,但是只在db1上建立了数据库和监听,db2只安装Oracle软件不建库,不建监听。

目录:

打开强制归档日志

增加standby日志组

修改主备启动参数

密码文件的处理

修改监听

复制监听文件、参数文件、密码文件到备库

创建备库控制文件

复制主库数据文件和日志文件到备库

初始化及配置备库做standby

DataGuard测试

主备切换测试

基础工作:

a.安装CentOS 5.11 x86_64,关闭selinux,iptables,自动对时

b.安装Oracle 11G R2,db1安装软件、监听及建库,db2只安装软件不建库

在db1的/etc/hosts里增加

127.0.0.1 db1

172.16.1.251 db2

在db2的/etc/hosts里增加

127.0.0.1 db2

172.16.1.250 db1

  1. 打开强制归档(db1)

    (db1)SQL >shutdown immediate;

    (db1)SQL >startup mount;

    (db1)SQL >alter database force logging;

    (db1)SQL >alter database archivelog;

    2.创建重做日志组(必须要比原来的redo log多一组或多组,standby redo log是使用Real Time Apply的必要条件)

    (db1)SQL> select group#,member from v$logfile;

    GROUP#MEMBER

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

    3/opt/oracle/oradata/member/redo03.log

    2/opt/oracle/oradata/member/redo02.log

    1/opt/oracle/oradata/member/redo01.log

    从上面可以看出现在已经有3组redo log.

    (db1)SQL >alter database add standby logfile ('/opt/oracle/oradata/member/standby04.log') size 50m;

    (db1)SQL >alter database add standby logfile ('/opt/oracle/oradata/member/standby05.log') size 50m;

    (db1)SQL >alter database add standby logfile ('/opt/oracle/oradata/member/standby06.log') size 50m;

    (db1)SQL >alter database add standby logfile ('/opt/oracle/oradata/member/standby07.log') size 50m;

    再查下日志组是否创建成功

    (db1)SQL> select group#,member from v$logfile;

    GROUP#MEMBER

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

    3/opt/oracle/oradata/member/redo03.log

    2/opt/oracle/oradata/member/redo02.log

    1/opt/oracle/oradata/member/redo01.log

    4/opt/oracle/oradata/member/standby04.log

    5 /opt/oracle/oradata/member/standby05.log

    6 /opt/oracle/oradata/member/standby06.log

    7 /opt/oracle/oradata/member/standby07.log

    可以看到我们创建的4组日志

     

    3.修改主备库的启动参数

    生成参数文件

    (db1)SQL> create pfile='/tmp/member.pfile' from spfile;

    退出sqlplus,用编辑器打开/tmp/member.pfile

    [[email protected] ~]$ vi /tmp/member.pfile

    member.__db_cache_size=331350016

    member.__java_pool_size=4194304

    member.__large_pool_size=4194304

    member.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment

    member.__pga_aggregate_target=339738624

    member.__sga_target=503316480

    member.__shared_io_pool_size=0

    member.__shared_pool_size=150994944

    member.__streams_pool_size=0

    *.audit_file_dest='/opt/oracle/admin/member/adump'

    *.audit_trail='db'

    *.compatible='11.2.0.0.0'

    *.control_files='/opt/oracle/oradata/member/control01.ctl','/opt/oracle/flash_recovery_area/member/control02.ctl'

    *.db_block_size=8192

    *.db_domain=''

    *.db_name='member'

    *.db_recovery_file_dest='/opt/oracle/flash_recovery_area'

    *.db_recovery_file_dest_size=4070572032

    *.diagnostic_dest='/opt/oracle'

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

    *.log_archive_format='%t_%s_%r.dbf'

    *.memory_target=839909376

    *.open_cursors=300

    *.processes=150

    *.remote_login_passwordfile='EXCLUSIVE'

    *.undo_tablespace='UNDOTBS1'

    #增加一下部分

    *.db_unique_name='db1'

    *.archive_lag_target=1800

    *.fal_client='db1'

    *.fal_server='db2'

    *.log_archive_config='DG_CONFIG=(db1,db2)'

    *.log_archive_dest_1='location=/opt/oracle/flash_recovery_area/ VALID_FOR=(all_logfiles,all_roles) db_unique_name=db1'

    *.log_archive_dest_2='service=db2 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=db2'

    *.log_archive_dest_state_1='enable'

    *.log_archive_dest_state_2='enable'

    *.log_archive_format='%t_%s_%r.dbf'

    *.standby_file_management='auto'

    *.db_file_name_convert='/opt/oracle/flash_recovery_area',' /opt/oracle/flash_recovery_area'

    *.log_file_name_convert='/opt/oracle/flash_recovery_area ','/opt/oracle/flash_recovery_area'

    并拷贝一个作为备机db2的启动参数文件

    [[email protected] ~]$ cp /tmp/member.pfile /tmp/db2.pfile

    [[email protected] ~]$ vim /tmp/db2.pfile

    则将上面增加的部分修改为

    *.db_unique_name='db2'

    *.archive_lag_target=1800

    *.fal_client='db2'

    *.fal_server='db1'

    *.log_archive_config='DG_CONFIG=(db1,db2)'

    *.log_archive_dest_1='location=/opt/oracle/flash_recovery_area/ VALID_FOR=(all_logfiles,all_roles) db_unique_name=db2'

    *.log_archive_dest_2='service=db1 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=db1'

    *.log_archive_dest_state_1='enable'

    *.log_archive_dest_state_2='enable'

    *.log_archive_format='%t_%s_%r.dbf'

    *.standby_file_management='auto'

    *.db_file_name_convert='/opt/oracle/flash_recovery_area',' /opt/oracle/flash_recovery_area'

    *.log_file_name_convert='/opt/oracle/flash_recovery_area ','/opt/oracle/flash_recovery_area'

    在db1上以修改过的/tmp/member.pfile启动

    (db1)SQL >shutdown immediate;

    (db1)SQL> startup pfile='/tmp/member.pfile' nomount;

    (db1)SQL> create spfile from pfile='/tmp/member.pfile';

    (db1)SQL >shutdown immediate;

    (db1)SQL> startup;

    4.主库密码文件:

    [1]存在密码文件

    [[email protected] dbs]$ ls $ORACLE_HOME/dbs

    hc_DBUA0.dat hc_member.dat init.ora lkDB1 lkMEMBER orapwmember spfilemember.ora

    看到上面有一个密码文件orapwmember,在建库的时候默认会创建一个

    =================================================================================

    [2]不存在密码文件

    如果没有的话可以手动创建一个。

    [[email protected] dbs]$ cd $ORACLE_HOME/dbs

    [[email protected] dbs]$ orapwd file=orapwmember password=123456 entries=3

    #注意以上需要根据SID名建立的,file=orapwSID

    ===================================================================================

    5.修改监听

    [[email protected] dbs]$ cd $ORACLE_HOME/network/admin

    [[email protected] admin]$ mv listener.ora listener.ora.default

    [[email protected] admin]$ vim listener.ora

    LISTENER =

    (DESCRIPTION =

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

    )

    SID_LIST_LISTENER =

    (SID_LIST =

    (SID_DESC =

    (GLOBAL_DBNAME = member)

    (ORACLE_HOME = /opt/oracle/product/11.2.0/db_1)

    (SID_NAME = member)

    )

    )

    #注意以上的HOST,就是HOSTNAME

    [[email protected] admin]$ vim tnsnames.ora

    MEMBER =

    (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))

    (CONNECT_DATA =

    (SERVER = DEDICATED)

    (SERVICE_NAME = member)

    )

    )

    db1 =

    (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

    (SERVER = DEDICATED)

    (SERVICE_NAME = db1)

    )

    )

    db2 =

    (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

    (SERVER = DEDICATED)

    (SERVICE_NAME = db2)

    )

    )

    重启下监听

    [[email protected] admin]$ lsnrctl stop

    [[email protected] admin]$ lsnrctl start

    6.复制监听文件、参数文件、密码文件到备库

    #注意一下我这边$ORACLE_HOME

    [[email protected] ~]$ echo $ORACLE_HOME

    /opt/oracle/product/11.2.0/db_1

    #传输启动参数文件

    [[email protected] ~]$ scp /tmp/db2.pfile db2:~

    #传输密码文件

    [[email protected] ~]$ scp /opt/oracle/product/11.2.0/db_1/dbs/orapwmember db2:/opt/oracle/product/11.2.0/db_1/dbs/orapwmember

    #传输监听文件

    [[email protected] ~]$scp -r /opt/oracle/product/11.2.0/db_1/network/admin/{listener.ora,tnsnames.ora} db2:/opt/oracle/product/11.2.0/db_1/network/admin/

    在db2上修改/opt/oracle/product/11.2.0/db_1/network/admin/listener.ora,将db1修改为db2

    [[email protected] ~]$ vim /opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

    LISTENER =

    (DESCRIPTION =

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

    )

    SID_LIST_LISTENER =

    (SID_LIST =

    (SID_DESC =

    (GLOBAL_DBNAME = member)

    (ORACLE_HOME = /opt/oracle/product/11.2.0/db_1)

    (SID_NAME = member)

    )

    )

    #只需要修改listener.ora即可,tnsnames.ora不需要动

    7.主库创建standby控制文件,我们这边利用scp传送全部文件

    查看下控制文件的路径

    (db1)SQL> select name from v$controlfile;

    NAME

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

    /opt/oracle/oradata/member/control01.ctl

    /opt/oracle/flash_recovery_area/member/control02.ctl

    (db1)SQL> shutdown immediate;

    (db1)SQL>startup mount;

    (db1)SQL> alter database create standby controlfile as '/opt/oracle/oradata/member/standby.ctl';

    #在/opt/oracle/oradata/member/目录下创建standby.ctl备机控制文件

    8.复制主库数据文件和日志文件到备库

    [[email protected] ~]$scp -r /opt/oracle/flash_recovery_area/ /opt/oracle/admin/ /opt/oracle/diag/ /opt/oracle/oradata/ db2:/opt/oracle

    9.初始化备库

    在备机上使用standby的控制文件覆盖原有的控制文件,覆盖的路径可以通过上一步查找控制文件的路径了解到

    [[email protected] ~]$ cp /opt/oracle/oradata/member/standby.ctl /opt/oracle/oradata/member/control01.ctl

    [[email protected] ~]$ cp /opt/oracle/oradata/member/standby.ctl /opt/oracle/flash_recovery_area/member/control02.ctl

    使用db2.pfile之前修改过的参数文件进行db2

    (db2)SQL> startup pfile='/home/oracle/db2.pfile' nomount;

    (db2)SQL> create spfile from pfile='/home/oracle/db2.pfile';

    (db2)SQL> shutdown immediate;

    (db2)SQL>startup nomount;

    (db2)SQL> alter database mount standby database;

    (db2)SQL> alter database open read only;

    以下3种应用日志的方法:(a和b选一)

    a.开启实时应用日志,这样在主库插入立马就可以在备机上查找到

    (db2)SQL> alter database recover managed standby database using current logfiledisconnect from session;

    b.开启redolog应用日志,时间较长才能查询到

    (db2)SQL>alter database recover managed standby database disconnect from session;

    c.停止应用redolog,只接受日志,不重做

    (db2)SQL> alter database recover managed standby database cancel;

    至此,DataGuard搭建成功,在db1上创建表并插入数据,然后在db2上进行查询就可以查到了。一开始做的时候总是查不到数据,最后发现是上面应用日志的方法问题。

     

    10.Dataguard测试:

    查看Standby管理进程

    (db1)SQL> select process from v$managed_standby;

    PROCESS

    ---------

    ARCH

    ARCH

    ARCH

    ARCH

    LNS

    (db2)SQL> select process from v$managed_standby;

    PROCESS

    ---------

    ARCH

    ARCH

    ARCH

    ARCH

    RFS

    RFS

    RFS

    RFS

    MRP0

    以上需要看到在主机上需要有LNS进程,在备机上需要RFS进程用来接收redo日志,MRP0进程就是负责将日志写入数据库中

    在db1上进行切换日志,然后在db2上查看日志是否正常

    (db1)SQL> select max(sequence#) from v$archived_log;

    MAX(SEQUENCE#)

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

    11

    (db2)SQL> select max(sequence#) from v$archived_log;

    MAX(SEQUENCE#)

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

    11

    (db1)SQL> alter system switch logfile;

    (db1)SQL> select max(sequence#) from v$archived_log;

    MAX(SEQUENCE#)

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

    12

    (db2)SQL> select max(sequence#) from v$archived_log;

    MAX(SEQUENCE#)

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

    12

    从上面可以看出日志切换成功,DataGuard正常运行

    11.主备切换测试:

    db1---primary/db2---standby ===》db2--primary/db1--standby

    [[email protected] ~]$ lsnrctl stop

    (db1)SQL> alter database commit to switchover to physical standby with session shutdown;

    (db1)SQL> shutdown immediate;

    (db1)SQL> startup mount;

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

    (db1)SQL> alter database open;

    [[email protected] ~]$ lsnrctl start

    (db2)SQL> alter database commit to switchover to primary;

    (db2)SQL> shutdown immediate;

    (db2)SQL> startup;

    以上就是主备切换的流程