IT技术互动交流平台

oracle 11g dataguard环境搭建

作者:一起走过的日子  来源:IT165收集  发布日期:2016-11-09 20:21:46

硬件和系统软件说明:

操作系统:Oracle Linux 5.8 64位

oracle软件:oracle 11.2.0.1

 

角色           主机名      IP地址             数据库       服务名

primary        dg1      192.168.3.55       tong          tong

standby       dg2      192.168.3.56       tong          cheng

 

一.primary主库操作

1.在primary服务器安装oracle软件,并创建数据库.在standby服务器只安装oracle软件,不创建数据库.

 

2.配置监听

[oracle@dg1 dbs]$ cd /u01/product/11.2.0.1/db_1/network/admin/

[oracle@dg1 admin]$ vim listener.ora 

 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = tong)    --primary库的名字

      (ORACLE_HOME = /u01/product/11.2.0.1/db_1)

      (SID_NAME = tong)

    )

  )

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.55)(PORT = 1521))  --prmary库的IP地址

  )

ADR_BASE_LISTENER = /u01

[oracle@dg1 admin]$ vim tnsnames.ora 

 

tong =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.55)(PORT = 1521)) --primary库的IP地址

    )

    (CONNECT_DATA =

      (SERVICE_NAME = tong)  --primary库的服务名

    )

  )

cheng =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.56)(PORT = 1521)) --standby库的IP地址

    )

    (CONNECT_DATA =

      (SERVICE_NAME = cheng)   --standby库的服务名

    ) 

  )

[oracle@dg1 admin]$  lsnrctl stop

[oracle@dg1 admin]$  lsnrctl start

 

3.在primary库启用归档和日志强行写入redo文件

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

SQL>  alter database force logging;

SQL>  select force_logging from v$database;

 

4.在primary 库添加standbt日志文件(standby文件和redo文件大小一至,文件个数比redo多一个)

SQL> alter database add standby logfile group 4 ('/u01/oradata/tong/sredo04.log') size 50m;

SQL> alter database add standby logfile group 4 ('/u01/oradata/tong/sredo04.log') size 50m;

SQL> alter database add standby logfile group 4 ('/u01/oradata/tong/sredo04.log') size 50m;

SQL> alter database add standby logfile group 4 ('/u01/oradata/tong/sredo04.log') size 50m;

SQL> select * from v$logfile order by 1;

 

5.利用spfile文件内容生成pfile文件

SQL> create pfile from spfile;

SQL> shutdown immediate

 

6.修改pfile文件的内容

[oracle@dg1 dbs]$ vim inittong.ora 

*.db_name='tong'                       --数据库名

 

*.db_unique_name=tong            --数据库服务名

*.fal_server='cheng'     --primary数据库服务名,是监听文件里面的名字(在standby库名字要和fal_client对调)

*.fal_client='tong'         --standby数据库服务名,是监听文件里面的名字

*.standby_file_management=auto

*.log_archive_start=true

*.db_file_name_convert='/u01/oradata/tong/','/u01/oradata/tong/'

*.log_file_name_convert='/u01/oradata/tong/','/u01/oradata/tong/'

*.log_archive_config='dg_config=(tong,cheng)' 

*.log_archive_dest_1='LOCATION=/u01/oradata/tong/archive valid_for=(all_logfiles,all_roles) db_unique_name=tong'   --归档文件存放的路径  

*.log_archive_dest_2='service=cheng LGWR SYNC AFFIRM valid_for=(online_logfiles,all_roles) db_unique_name=cheng'   --cheng是standby库的服务名

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

[oracle@dg1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 9 11:56:26 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;         --用pfile文件创建spfile文件  
SQL> 

 

7.启动主库

 

[oracle@dg1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 9 11:56:26 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instancestarted

SQL> alterdatabase set standby database to maximize availability;

Databasealtered.

SQL> exit

 

8.备份数据库(备份文件在闪恢复区)

[oracle@dg1 dbs]$ rman target /

RMAN> backup database plus archivelog;

RMAN> backup current controlfile for standby;

 

9.考贝文件到standby服务器

[oracle@dg1 dbs]$ cd /u01/flash_recovery_area/     --考贝闪回恢复区的备份文件

[oracle@dg1 flash_recovery_area]$ scp *  oracle@dg2;/u01/flash_recovery_area/ 

[oracle@dg1 flash_recovery_area]$ cd /u01/product/11.2.0.1/db_1/dbs/

[oracle@dg1 dbs]$ scp *.ora   oracle@dg2:/u01/product/11.2.0.1/db_1/dbs/  --考贝pfile和spfile参数文件

[oracle@dg1 dbs]$ cd /u01/product/11.2.0.1/db_1/network/admin/   

[oracle@dg1 admin]$ scp listener.ora tnsnames.ora oracle@dg2:/u01/product/11.2.0.1/db_1/network/admin/   --考贝监听文件

[oracle@dg1 admin]$

 

二.standby从库操作

10.修改监听的地址

[oracle@dg2 ~]$ cd /u01/product/11.2.0.1/db_1/network/admin/

[oracle@dg2 admin]$ vim listener.ora 

 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = cheng)

      (ORACLE_HOME = /u01/product/11.2.0.1/db_1)

      (SID_NAME = cheng)

    )

  )

LISTENER =

  (DESCRIPTION =

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

  )

ADR_BASE_LISTENER = /u01

[oracle@dg2 admin]$ vim tnsnames.ora 

 

tong =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = tong)

    )

  )

cheng =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = cheng)

    )

  )

[oracle@dg2 admin]$  lsnrctl stop

[oracle@dg2 admin]$  lsnrctl start

 

11.修改pfile和spfile文件名

[oracle@dg2 admin]$ cd /u01/product/11.2.0.1/db_1/dbs/

[oracle@dg2 dbs]$ mv inittong.ora  initcheng.ora     --修改pfile文件名

[oracle@dg2 ~]$ cp  /u01/flash_recovery_area/tong/control02.ctl /u01/oradata/tong/control01.ctl   --新的控制文件替换旧控制文件

 

12.恢复数据库

[oracle@dg2 ~]$  rman target sys/system@tong  auxiliary /

RMAN> duplicate target database for standby nofilenamecheck;

 

13.修改pfile参数文件

SQL> shutdown immediate

[oracle@dg2 dbs]$ cd /u01/product/11.2.0.1/db_1/dbs/

[oracle@dg2 dbs]$ vim initcheng.ora 

*.db_name='tong'

*.db_recovery_file_dest='/u01/flash_recovery_area'

*.db_recovery_file_dest_size=4070572032

*.diagnostic_dest='/u01'

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

*.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=cheng

*.fal_server='tong'

*.fal_client='cheng'

*.standby_file_management=auto

*.db_file_name_convert='/u01/oradata/tong/','/u01/oradata/tong/'

*.log_file_name_convert='/u01/oradata/tong/','/u01/oradata/tong/'

*.log_archive_config='dg_config=(tong,cheng)'

*.log_archive_dest_1='LOCATION=/u01/oradata/tong/archive valid_for=(all_logfiles,all_roles) db_unique_name=cheng'

*.log_archive_dest_2='service=cheng LGWR SYNC AFFIRM valid_for=(online_logfiles,all_roles) db_unique_name=cheng'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

[oracle@dg2 dbs]$

 

14.启动standby库

SQL> startup nomount;

SQL> alter database mount standby database;

SQL> alter database add standby logfile;

SQL> alter database add standby logfile;

SQL> alter database add standby logfile;

SQL> alter database add standby logfile;

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

 

15.检查standby的日志

[oracle@dg2 dbs]$ tailf  /u01/diag/rdbms/cheng/cheng/trace/alert_cheng.log 

Tue Nov 08 22:47:42 2016

SMON: enabling cache recovery

Tue Nov 08 22:47:43 2016

Dictionary check beginning

Dictionary check complete

Database Characterset is AL32UTF8

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Physical standby database opened for read only access.

Completed: alter database open read only

 

Tag标签: 环境  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规