ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > ORACLE 11G 配置dataguard物理备库

ORACLE 11G 配置dataguard物理备库

原创 Oracle 作者：warren20 时间：2015-06-07 00:12:27 0 删除 编辑
    本实验主要是测试在同一台LINUX机器上配置dataguard的物理备库。这次实验的环境是主库在ASM, 备库准备创建在OS的文件系统上。
    下面是主库 和 备库的相关名称
parameter name db_name ORACLE_SID db_unique_name
 service_names
Primary database orclpr
 orclpr
 orclpr
 orclpr, orcl
Standby database orclpr
 orcldg
 orcldg
 orcldg

1、准备主数据库用于创建备用数据库
       1）查看主库是否已经开启归档模式
            SQL> archive log list;
            如果没有开启归档模式，则执行下面的语句
            SQL> shutdown immediate;
            SQL> startup mount;
            SQL> alter database archivelog;
            SQL> alter database open;
        2）查看数据库是否已经启用强日志模式
            SQL> select force_logging from v$database;        
            如果返回的是 “no”， 则执行下面的语句
            SQL> alter database force logging;

2、修改主库参数文件
        1）主库通过spfile 创建 pfile 文件
            SQL> create pfile from spfile;
        2）修改主库的参数pfile 文件（在$ORACLE_HOME/dbs/initorclpr.ora）
orclpr.__db_cache_size=423624704
orclpr.__java_pool_size=4194304
orclpr.__large_pool_size=4194304
orclpr.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orclpr.__pga_aggregate_target=209715200
orclpr.__sga_target=633339904
orclpr.__shared_io_pool_size=0
orclpr.__shared_pool_size=192937984
orclpr.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orclpr/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+FRA/ORCLPR/control01.ctl','+FRA/ORCLPR/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+FRA'
*.db_domain=''
*.db_name='orclpr'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclprXDB)'
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.service_names='orclpr','orcl'
*.sga_target=631242752
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=orclpr
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orclpr,orcldg)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/fast_recovery_area/ORCLPR/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclpr'
LOG_ARCHIVE_DEST_2='SERVICE=orcldg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=orcldg
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcldg','+FRA/orclpr/datafile'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcldg','+FRA/orclpr/onlinelog'
STANDBY_FILE_MANAGEMENT=AUTO


3、创建主库主库的密码文件
        移除 $ORACLE_HOME/dbs 目录下面的orapworclpr文件（[oracle@LINUX10 dbs]$ rm -f orapworclpr），再重新创建密码文件
        [oracle@LINUX10 dbs]$ orapwd file=/u01/app/oracle/product/11.2.3/oracle/dbs/orapworclpr password='oracle' entries=20;
4、为备库创建控制文件
  1. SQL> shutdow immediate;
  2. SQL> startup mount;
  3. SQL> alter database create standby controlfile as '/u01/app/oracle/oradata/orcldg/control01.ctl';
  4. SQL> alter database open;
5、文件拷贝
        由于这里是主库和备库都是公共使用的"$ORACLE_HOME/dbs" 目录，所以只需要在该目录进行参数文件和密码文件的拷贝即可
        1）拷贝参数文件
        [oracle@LINUX10 dbs]$ cp initorclpr.ora initorcldg.ora
        2）拷贝密码文件
        [oracle@LINUX10 dbs]$ cp orapworclpr orapworcldg
        3）由于控制文件拷贝
        [oracle@LINUX10 dbs]$ cp /u01/app/oracle/oradata/orcldg/control01.ctl /u01/app/oracle/oradata/orcldg/control02.ctl

6、修改备库的参数文件 initorcldg.ora
orclpr.__db_cache_size=423624704
orclpr.__java_pool_size=4194304
orclpr.__large_pool_size=4194304
orclpr.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orclpr.__pga_aggregate_target=209715200
orclpr.__sga_target=633339904
orclpr.__shared_io_pool_size=0
orclpr.__shared_pool_size=192937984
orclpr.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orclpr/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcldg/control01.ctl','/u01/app/oracle/oradata/orcldg/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata/orcldg'
*.db_domain=''
*.db_name='orclpr'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclprXDB)'
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.service_names='orcldg'
*.sga_target=631242752
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=orcldg
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcldg,orclpr)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/fast_recovery_area/ORCLDG/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg'
LOG_ARCHIVE_DEST_2='SERVICE=orclpr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclpr'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=orclpr
DB_FILE_NAME_CONVERT='+FRA/orclpr/datafile','/u01/app/oracle/oradata/orcldg'
LOG_FILE_NAME_CONVERT='+FRA/orclpr/onlinelog','/u01/app/oracle/oradata/orcldg'
STANDBY_FILE_MANAGEMENT=AUTO


7、启动备库到mount状态
  1. [oracle@LINUX10 archivelog]$ export ORACLE_SID=orcldg
  2. [oracle@LINUX10 archivelog]$ sqlplus / as sysdba

  4. SQL*Plus: Release 11.2.0.3.0 Production on Sun May 31 00:50:30 2015

  6. Copyright (c) 1982, 2011, Oracle. All rights reserved.

  8. Connected to an idle instance.

  10. SQL> create spfile from pfile;
  11. SQL> startup nomount;
  12. SQL> alter database mount
8、配置监听 和 tnsname
    1）配置监听
        我这里主库和备库使用的是同一个监听，配置完成后，重新启动监听
  1. [oracle@LINUX10 admin]$ vi listener.ora

  3. # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.3/oracle/network/admin/listener.ora
  4. # Generated by Oracle configuration tools.

  6. SID_LIST_LISTENER =
  7.   (SID_LIST =
  8.     (SID_DESC =
  9.       (GLOBAL_DBNAME = orclpr)
  10.       (ORACLE_HOME = /u01/app/oracle/product/11.2.3/oracle)
  11.       (SID_NAME = orclpr)
  12.     )
  13.     (SID_DESC =
  14.       (GLOBAL_DBNAME = orcldg)
  15.       (ORACLE_HOME = /u01/app/oracle/product/11.2.3/oracle)
  16.       (SID_NAME = orcldg)
  17.     )
  18.   )

  20. LISTENER =
  21.   (DESCRIPTION =
  22.     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
  23.   )

  25. ADR_BASE_LISTENER = /u01/app/oracle

    2）配置tnsnames.ora文件    
  1. [oracle@LINUX10 admin]$ vi tnsnames.ora

  3. # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.3/oracle/network/admin/tnsnames.ora
  4. # Generated by Oracle configuration tools.

  6. ORCLPR =
  7.   (DESCRIPTION =
  8.     (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX10)(PORT = 1521))
  9.     (CONNECT_DATA =
  10.       (SERVER = DEDICATED)
  11.       (SERVICE_NAME = orclpr)
  12.     )
  13.   )

  15. ORCL =
  16.   (DESCRIPTION =
  17.     (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX10)(PORT = 1521))
  18.     (CONNECT_DATA =
  19.       (SERVER = DEDICATED)
  20.       (SERVICE_NAME = orcl)
  21.     )
  22.   )

  24. ORCLDG =
  25.   (DESCRIPTION =
  26.     (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX10)(PORT = 1521))
  27.     (CONNECT_DATA =
  28.       (SERVER = DEDICATED)
  29.       (SERVICE_NAME = orcldg)
  30.     )
  31.   )
9、在主库添加备库日志组
    注意备库的standby日志组数，必须比主库的在线日志数多一个。
    用下面的sql检查主库的在线日志文件的组数standby logfile的组数比下面的结果多一组
    select distinct group# from v$logfile where type='ONLINE';

    使用下面的sql语句检查在线日志文件的大小，standby logfile 应该等于下面的结果
    select max(bytes)/1024/1024 from v$log;
    
  1. SQL> alter database add standby logfile group 20('+FRA') size 50M;
  2. SQL> alter database add standby logfile group 21('+FRA') size 50M;
  3. SQL> alter database add standby logfile group 22('+FRA') size 50M;
  4. SQL> alter database add standby logfile group 23('+FRA') size 50M;

10、重启备库到 nomount 状态

11、创建主库的spfile，并重启主库    
  1. SQL> shutdown immediate
  2. SQL> create spfile from pfile;
  3. SQL> startup
12、在主库上通过rman进行复制备库(注意在这一步之前必须退出备库的所有连接,否则会报错)    
  1. [oracle@LINUX10 admin]$ rman target sys/oracle@orclpr auxiliary sys/oracle@orcldg

  3. Recovery Manager: Release 11.2.0.3.0 - Production on Sun May 31 01:23:32 2015

  5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

  7. connected to target database: ORCLPR (DBID=542685180)
  8. connected to auxiliary database: ORCLPR (not mounted)

  10. RMAN> duplicate target database for standby nofilenamecheck from active database
13、备库打开应用日志
  1. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
  2. -- 或者
  3. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

  5. --备库以只读方式打开
  6. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
  7. SQL> ALTER DATABASE OPEN read only;
  8. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

主备切换的相关语句
  1. --前提是备库的参数文件中的 log_archive_dest_n进行了相关的设置，且db_file_name_convert，log_file_name_convert，fal_server 参数也进行了相关的设置。

  3. --查询主备是日志是否有缺口(主备都执行)
  4. select to_char(scn_to_timestamp(current_scn),\'yyyy-mm-dd hh24:mi:ss\') from v$database;
  5. select * from v$archive_gap;

  7. --在当前的主库上
  8. SQL> alter database commit to switchover to physical standby with session shutdown;
  9. SQL> shutdown immediate;
  10. SQL> startup mount;
  11. SQL> alter database recover managed standby database USING CURRENT LOGFILE disconnect from session；

  13. --在备库上
  14. SQL> alter database commit to switchover to primary;
  15. SQL> shutdown immediate;
  16. SQL> startup
 

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30150152/viewspace-1689999/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 【工具】cpu监控利器
下一篇： kettle调度mysql存储过程问题
请登录后发表评论 登录
全部评论
warren20

注册时间：2015-03-03

  • 博文量
    56
  • 访问量
    376708

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593