CentOS 5.11下Oracle 11G R2 Dataguard建立51CTO博客 - 威尼斯人

CentOS 5.11下Oracle 11G R2 Dataguard建立51CTO博客

2019年03月04日15时02分18秒 | 作者: 泽雨 | 标签: 文件,日志,修正 | 浏览: 2858

Datagard算是Oracle企业版的一种容灾计划,在企业中广泛使用,我就将树立进程记录下来以作备用。

主机名    数据库版别    实例名    IP

db1    Oracle 11G R2    member    172.16.1.250

db2    Oracle 11G R2    member    172.16.1.251

默许情况下以上都现已装置好了Oracle数据库,可是只在db1上树立了数据库和监听,db2只装置Oracle软件不建库,不建监听。


目录:

  1. 翻开强制归档日志

  2. 添加standby日志组

  3. 修正主备发动参数

  4. 密码文件的处理

  5. 修正监听

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

  7. 创立备库操控文件

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

  9. 初始化及配置备库做standby

  10. DataGuard测验

  11. 主备切换测验


根底作业:

    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

[oracle@db1 ~]$ 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的发动参数文件

[oracle@db1 ~]$ cp /tmp/member.pfile /tmp/db2.pfile

[oracle@db1 ~]$ 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]存在密码文件

[oracle@db1 dbs]$ ls $ORACLE_HOME/dbs

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

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

=

[2]不存在密码文件

假如没有的话能够手动创立一个。

[oracle@db1 dbs]$ cd $ORACLE_HOME/dbs

[oracle@db1 dbs]$ orapwd file=orapwmember password=123456 entries=3

#留意以上需求依据SID名树立的,file=orapwSID

=


5.修正监听

[oracle@db1 dbs]$ cd $ORACLE_HOME/network/admin

[oracle@db1 admin]$ mv listener.ora listener.ora.default

[oracle@db1 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

[oracle@db1 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)

    )

  )

重启下监听

[oracle@db1 admin]$ lsnrctl stop

[oracle@db1 admin]$ lsnrctl start


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

#留意一下我这边$ORACLE_HOME

[oracle@db1 ~]$ echo $ORACLE_HOME

/opt/oracle/product/11.2.0/db_1

#传输发动参数文件

[oracle@db1 ~]$ scp /tmp/db2.pfile db2:~

#传输密码文件

[oracle@db1 ~]$ scp /opt/oracle/product/11.2.0/db_1/dbs/orapwmember db2:/opt/oracle/product/11.2.0/db_1/dbs/orapwmember

#传输监听文件

[oracle@db1 ~]$ 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

[oracle@db2 ~]$ 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.仿制主库数据文件和日志文件到备库

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


9.初始化备库

在备机上运用standby的操控文件掩盖原有的操控文件,掩盖的途径能够通过上一步查找操控文件的途径了解到

[oracle@db2 ~]$ cp /opt/oracle/oradata/member/standby.ctl /opt/oracle/oradata/member/control01.ctl 

[oracle@db2 ~]$ 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 logfile disconnect 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,status from v$managed_standby;

PROCESS   STATUS

-

ARCH      CONNECTED

ARCH      CLOSING

ARCH      CLOSING

ARCH      CLOSING

LNS       WRITING

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

PROCESS   STATUS

-

ARCH      CLOSING

ARCH      CLOSING

ARCH      CONNECTED

ARCH      CONNECTED

MRP0      APPLYING_LOG

RFS       IDLE

RFS       IDLE


以上需求看到在主机上需求有LNS进程,在备机上需求RFS进程用来接纳redo日志,MRP0进程就是担任将日志写入数据库中


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

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

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

MAX(SEQUENCE#)

    11

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

(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   =》db2primary/db1standby


[oracle@db1 ~]$ 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 open read only;

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

//在履行这条的时分,假如呈现

ERROR at line 1:

ORA-01665: control file is not a standby control file

则是没有履行alter database commit to switchover to physical standby with session shutdown;


[oracle@db1 ~]$ lsnrctl start


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

留意:

若呈现ORA-16139: media recovery required,履行如下句子:

SQL> ALTER  DATABASE RECOVER MANAGED STANDBY  DATABASE DISCONNECT FROM SESSION;

SQL> alter database commit to switchover to primary;


假如呈现,则可能是已翻开了会话,加上with session shutdown强制封闭绘画

ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected


SQL> alter database commit to switchover to primary WITH SESSION SHUTDOWN;


(db2)SQL> shutdown immediate;

(db2)SQL> startup;


以上就是主备切换的流程

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表威尼斯人立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章