ITPub博客

首页 > 数据库 > Oracle > oracle 11G dataguard配置

oracle 11G dataguard配置

原创 Oracle 作者:OGG-01161 时间:2014-03-21 10:15:05 0 删除 编辑
1、规划

源端    
 
        IP: 192.168.2.2       hostname: dgdb     sid:dgdb  dbname:dgdb  db_unique_name:dgdb_pd

目标端

        IP:192.168.2.4        hostname:sgdb      sid:dgdb  dbname:dgdb  db_unique_name:sgdb_st


2、生产端配置

a、设置归档模式
  archive log list; 
  alter system set log_archive_dest='/u01/app/oracle/arch' scope=both;
  shutdown immediate 
  startup mount 
  alter database archivelog; 
  archive log list;


b、设置强制模式


   select force_logging from v$database;
   alter database force logging;


c、配置监听,需要在主数据库和STANDBY数据库创建各自的监听,主数据库监听配置如下:


   [oracle@dgdb admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dgdb)(PORT = 1521))
    )
  )


ADR_BASE_LISTENER = /u01/app/oracle




SID_LIST_LISTENER = 
 (SID_LIST = 
 (SID_DESC = 
 (GLOBAL_DBNAME = dgdb) 
 (ORACLE_HOME = /u01/app/oracle/11.2.0/db_1) 
 (SID_NAME = dgdb) 
 ) 
 )


d、配置TNS,需要在主数据库和STANDBY数据库创建,主数据库TNS配置如下:

liu_dgdb =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = dgdb)
 )
 )


liu_sgdb =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = dgdb)
 )
 )


[oracle@dgdb admin]$ more tnsnames.ora
liu_dgdb =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = dgdb)
 )
 )


liu_sgdb =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = dgdb)
 )
 )


[oracle@dgdb admin]$ 


d、配置主库参数文件

 create pfile from spfile;

[oracle@dgdb dbs]$ more initdgdb.ora
dgdb.__db_cache_size=104857600
dgdb.__java_pool_size=4194304
dgdb.__large_pool_size=4194304
dgdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dgdb.__pga_aggregate_target=159383552
dgdb.__sga_target=251658240
dgdb.__shared_io_pool_size=0
dgdb.__shared_pool_size=130023424
dgdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dgdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/dgdb/dgdb/control01.ctl','/oradata/dgdb/dgdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dgdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgdbXDB)'
*.log_archive_dest='/u01/app/oracle/arch'   ---去掉该参数
*.memory_target=408944640
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@dgdb dbs]$ 




需要修改的参数:

*.db_name='dgdb'
*.db_unique_name='dgdb_pd' 
*.log_archive_config='dg_config=(dgdb_pd,sgdb_st)'
*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=dgdb_pd'
*.log_archive_dest_2='service=liu_sgdb reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sgdb_st'
*.log_archive_dest_state_1=enable 
*.log_archive_dest_state_2=enable 
*.standby_file_management='auto'
*.fal_server='liu_sgdb'
*.fal_client='liu_dgdb'


修改后的配置如下:
dgdb.__db_cache_size=104857600
dgdb.__java_pool_size=4194304
dgdb.__large_pool_size=4194304
dgdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dgdb.__pga_aggregate_target=159383552
dgdb.__sga_target=251658240
dgdb.__shared_io_pool_size=0
dgdb.__shared_pool_size=130023424
dgdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dgdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/dgdb/dgdb/control01.ctl','/oradata/dgdb/dgdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dgdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgdbXDB)'
*.memory_target=408944640
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='dgdb_pd' 
*.log_archive_config='dg_config=(dgdb_pd,sgdb_st)'
*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=dgdb_pd'
*.log_archive_dest_2='service=liu_sgdb reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sgdb_st'
*.log_archive_dest_state_1=enable 
*.log_archive_dest_state_2=enable 
*.standby_file_management='auto'
*.fal_server='liu_sgdb'
*.fal_client='liu_dgdb'





create spfile from pfile;

e、配置主库STANDBY REDO 日志
  
  select thread#,bytes/1024/1024,members from v$log;
  select member from v$logfile;
   
  


 alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo01.log' size 50m;
 alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo02.log' size 50m;
 alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo03.log' size 50m;
 alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo04.log' size 50m;




f、创建密码文件

 orapwd file=$ORACLE_HOME/dbs/orapwdgdb password=oracle entries=5

3、备库的配置

a、备数据库端配置监听和TNS


---监听---
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sgdb)(PORT = 1521))
    )
  )


ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER = 
 (SID_LIST = 
 (SID_DESC = 
 (GLOBAL_DBNAME = dgdb) 
 (ORACLE_HOME = /u01/app/oracle/11.2.0/db_1) 
 (SID_NAME = dgdb) 
 ) 
 )


---TNS----
liu_dgdb =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = dgdb)
 )
 )


liu_sgdb =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = dgdb)
 )
 )




b、备库创建参数文件


dgdb.__db_cache_size=104857600
dgdb.__java_pool_size=4194304
dgdb.__large_pool_size=4194304
dgdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dgdb.__pga_aggregate_target=159383552
dgdb.__sga_target=251658240
dgdb.__shared_io_pool_size=0
dgdb.__shared_pool_size=130023424
dgdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dgdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/dgdb/dgdb/control01.ctl','/oradata/dgdb/dgdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dgdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sgdbXDB)'
*.memory_target=408944640
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='sgdb_st' 
*.log_archive_config='dg_config=(dgdb_pd,sgdb_st)'
*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=sgdb_st'
*.log_archive_dest_2='service=liu_dgdb reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dgdb_pd'
*.log_archive_dest_state_1=enable 
*.log_archive_dest_state_2=enable 
*.standby_file_management='auto'
*.fal_server='liu_dgdb'


LOG_FILE_NAME_CONVERT='/oradata/dgdb/dgdb','/oradata/dgdb/dgdb'


c、备库建立相应的目录


   adump bdump cdump udump 
   归档位置等等






d、备库启动到NOMOUNT状态下


 SQL> startup nomount;
ORACLE instance started.


Total System Global Area  409194496 bytes
Fixed Size                  2213856 bytes
Variable Size             297797664 bytes
Database Buffers          104857600 bytes
Redo Buffers                4325376 bytes
SQL> 




e、创建密码文件


 ftp主库密码文件到备库相应的位置




4、主库备份


  run
   {
   allocate channel c1 device type disk format '/u01/backup/datafile/full_%u_%s_%p';
   backup database plus archivelog;
   }




 run
   {
   allocate channel c1 device type disk format '/u01/backup/archive/control%u_%s_%p';
   backup current controlfile for standby;
   }



oracle@dgdb datafile]$ rman target /


Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 18 16:15:53 2014


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


connected to target database: DGDB (DBID=2176221724)


RMAN>  run
2>    {
3>    allocate channel c1 device type disk format '/u01/backup/datafile/full_%u_%s_%p';
4>    backup database plus archivelog;
5>    }


using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=39 device type=DISK




Starting backup at 2014:03:18 16:16:02
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=1 STAMP=842541448
input archived log thread=1 sequence=8 RECID=2 STAMP=842541451
input archived log thread=1 sequence=9 RECID=3 STAMP=842544864
input archived log thread=1 sequence=10 RECID=4 STAMP=842544865
input archived log thread=1 sequence=11 RECID=5 STAMP=842544963
channel c1: starting piece 1 at 2014:03:18 16:16:04
channel c1: finished piece 1 at 2014:03:18 16:16:05
piece handle=/u01/backup/datafile/full_01p3gdq4_1_1 tag=TAG20140318T161603 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014:03:18 16:16:05


Starting backup at 2014:03:18 16:16:06
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/dgdb/dgdb/system01.dbf
input datafile file number=00002 name=/oradata/dgdb/dgdb/sysaux01.dbf
input datafile file number=00003 name=/oradata/dgdb/dgdb/undotbs01.dbf
input datafile file number=00004 name=/oradata/dgdb/dgdb/users01.dbf
channel c1: starting piece 1 at 2014:03:18 16:16:06
channel c1: finished piece 1 at 2014:03:18 16:17:31
piece handle=/u01/backup/datafile/full_02p3gdq6_2_1 tag=TAG20140318T161606 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:25
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 2014:03:18 16:17:35
channel c1: finished piece 1 at 2014:03:18 16:17:36
piece handle=/u01/backup/datafile/full_03p3gdss_3_1 tag=TAG20140318T161606 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014:03:18 16:17:36


Starting backup at 2014:03:18 16:17:36
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=6 STAMP=842545056
channel c1: starting piece 1 at 2014:03:18 16:17:37
channel c1: finished piece 1 at 2014:03:18 16:17:38
piece handle=/u01/backup/datafile/full_04p3gdt1_4_1 tag=TAG20140318T161736 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014:03:18 16:17:38
released channel: c1


RMAN> 




RMAN> run
2>    {
3>    allocate channel c1 device type disk format '/u01/backup/archive/control%u_%s_%p';
4>    backup current controlfile for standby;
5>    }


allocated channel: c1
channel c1: SID=39 device type=DISK


Starting backup at 2014:03:18 16:21:24
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including standby control file in backup set
channel c1: starting piece 1 at 2014:03:18 16:21:26
channel c1: finished piece 1 at 2014:03:18 16:21:27
piece handle=/u01/backup/archive/control05p3ge45_5_1 tag=TAG20140318T162124 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014:03:18 16:21:27
released channel: c1






5、传输到备库相应目录




6、备库恢复




a、恢复控制文件
    
    restore standby controlfile from '/u01/backup/archive/control05p3ge45_5_1';


    RMAN> restore standby controlfile from '/u01/backup/archive/control05p3ge45_5_1';


Starting restore at 2014:03:18 14:00:26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK


channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/oradata/dgdb/dgdb/control01.ctl
output file name=/oradata/dgdb/dgdb/control02.ctl
Finished restore at 2014:03:18 14:00:31


RMAN> 






b、备份启动到mount状态下,恢复数据文件




  alter database mount standby database;


 






c、恢复数据文件




RMAN> restore database;


Starting restore at 2014:03:18 14:24:23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK


channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oradata/dgdb/dgdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oradata/dgdb/dgdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata/dgdb/dgdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata/dgdb/dgdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/datafile/full_02p3gdq6_2_1


channel ORA_DISK_1: piece handle=/u01/backup/datafile/full_02p3gdq6_2_1 tag=TAG20140318T161606
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:16
Finished restore at 2014:03:18 14:25:41


RMAN> 
RMAN> 




RMAN> recover database;


Starting recover at 2014:03:18 14:54:35
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK


starting media recovery


channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=12
channel ORA_DISK_1: reading from backup piece /u01/backup/datafile/full_04p3gdt1_4_1
channel ORA_DISK_1: piece handle=/u01/backup/datafile/full_04p3gdt1_4_1 tag=TAG20140318T161736
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/arch/1_12_830232990.dbf thread=1 sequence=12
unable to find archived log
archived log thread=1 sequence=13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/18/2014 14:54:38
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 13 and starting SCN of 1027111


RMAN> 






7、备份创建STANDY REDO日志




alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
alter database drop standby logfile group 7;


 alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo01.log' size 50m;
 alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo02.log' size 50m;
 alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo03.log' size 50m;
 alter database add standby logfile '/oradata/dgdb/dgdb/stdbyredo04.log' size 50m;


alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;




8、开启同步应用




alter database recover managed standby database disconnect from session; -- 启动日志应用
alter database recover managed standby database using current logfile disconnect from session;







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

下一篇: 物理DG监控
请登录后发表评论 登录
全部评论

注册时间:2012-08-11

  • 博文量
    55
  • 访问量
    131211