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

首页 > Linux操作系统 > Linux操作系统 > Configure Data Guard--Physical Standby

Configure Data Guard--Physical Standby

原创 Linux操作系统 作者：hjianping 时间：2011-04-24 11:34:00 0 删除 编辑

1、Enable Archivelog Mode
SQL> archive log list
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;


2、Create a Password File
$ cd $ORACLE_HOME/dbs
$ orapwd file=orapwora10 password=password entries=3

SQL> show parameter remote_login_passwordfile
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
SQL> shutdown immediate


3、Enable Force Logging(optional)
SQL> select force_logging from v$database;
SQL> alter database force logging;


4、Configure the Primary Database Initialization Parameters
SQL> create pfile from spfile;
-----------------------------------------------------------------------
db_name=ora10
db_unique_name=ora10
instance_name=ora10
log_archive_config='dg_config=(ora10,oratest)'
log_archive_dest_1='location=use_db_recovery_file_dest'
log_archive_dest_2='service=oratest lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=oratest'
log_archive_dest_state_1=enable
log_archive_dest_state_2=defer

fal_server='ora10','oratest'
fal_client='ora10'
db_file_name_convert='oratest','ora10'
log_file_name_convert='oratest','ora10'
standby_file_management=auto
-----------------------------------------------------------------------
SQL> shutdown immediate
SQL> exit
SQL> startup pfile='/u01/oracle/app/product/dbs/initora10.ora'
SQL> create spfile from pfile;
SQL> shutdown immediate
SQL> startup


5、Create a Backup of the Primary Database and Create a Standby Controlfile
$ mkdir -p /backup/oracle/ --primary
$ mkdir -p /backup/oracle/ --standby

$ rman target sys/password nocatalog
RMAN> backup device type disk format '/backup/oracle/%d_%I_%T_%s' database plus archivelog;
RMAN> backup device type disk format '/backup/oracle/%d_%I_%T_%s' current controlfile for standby;


6、Prepare an Initialization Parameter for the Standby Database
$ cp /u01/oracle/app/product/dbs/initora10.ora /backup/oracle/initoratest.ora
-----------------------------------------------------------------------
db_name=ora10 ##
db_unique_name=oratest
instance_name=oratest
log_archive_config='dg_config=(ora10,oratest)' ##
log_archive_dest_1='location=use_db_recovery_file_dest' ##
log_archive_dest_2='service=ora10 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=ora10'
log_archive_dest_state_1=enable ##
log_archive_dest_state_2=enable

fal_server='ora10','oratest' ##
fal_client='oratest'
db_file_name_convert='ora10','oratest'
log_file_name_convert='ora10','oratest'
standby_file_management=auto ##
-----------------------------------------------------------------------


7、Transfer Files to the Standby Host
$ scp /backup/oracle/* 192.168.8.130:/backup/oracle/


8、Configure Oracle Net Components
Primary Host listener.ora
---------------------------------------------------------------------------
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/app/product)
      (PROGRAM = extproc)
    )
    (SID_DESC =
        (GLOBAL_DBNAME = ora10)
        (ORACLE_HOME = /u01/oracle/app/product)  
        (SID_NAME = ora10)
     )
  )

INBOUND_CONNECT_TIMEOUT_LISTENER = 0

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.120)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )
---------------------------------------------------------------------------

Standby Host listener.ora
---------------------------------------------------------------------------
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/app/product)
      (PROGRAM = extproc)
    )
    (SID_DESC =
        (GLOBAL_DBNAME = oratest)
        (ORACLE_HOME = /u01/oracle/app/product)  
        (SID_NAME = oratest)
     )
  )

INBOUND_CONNECT_TIMEOUT_LISTENER = 0

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.130)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )
---------------------------------------------------------------------------

Primary  and Standby Host tnsnames.ora
---------------------------------------------------------------------------
ora10 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.120)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora10)
    )
  )

oratest =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.130)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oratest)
    )
  )
---------------------------------------------------------------------------
$ lsnrctl reload --primary
$ lsnrctl reload --standby


9、Configure the Standby Database
$ mkdir -p /u01/oracle/app/admin/oratest/adump
$ mkdir -p /u01/oracle/app/admin/oratest/bdump
$ mkdir -p /u01/oracle/app/admin/oratest/cdump
$ mkdir -p /u01/oracle/app/admin/oratest/udump
$ mkdir -p /u01/oracle/app/oradata/oratest
$ mkdir -p /u01/oracle/app/flash_recovery_area/ORATEST/archivelog
$ mkdir -p /u01/oracle/app/flash_recovery_area/ORATEST/onlinelog

$ cd $ORACLE_HOME/dbs
$ orapwd file=orapworatest password=password entries=3
$ sqlplus / as sysdba
SQL> create spfile from pfile='/u01/oracle/app/product/dbs/initoratest.ora';


10、Create the Physical Standby Database
SQL> startup nomount

$ rman target sys/password@ora10 auxiliary sys/password@oratest
connected to target database: ORA10 (DBID=726726396)
connected to auxiliary database: ORA10 (not mounted)

RMAN> duplicate target database for standby;
RMAN> exit


11、Create Standby Redo Logs(standby database)
SQL> select group#, type, member from v$logfile order by group#, member;
SQL> select group#, thread#, bytes, members from v$log;
------------------------------------------------------
GROUP#    THREAD#      BYTES    MEMBERS
------ ---------- ---------- ----------
     1          1   52428800          2
     2          1   52428800          2
     3          1   52428800          2
------------------------------------------------------
日志数量--(online redo log groups on primary + 1) * maximum of threads
this example is (3 + 1) * 1 = 4 at 50MB

SQL> alter database add standby logfile thread 1 group 4 size 50m;
SQL> alter database add standby logfile thread 1 group 5 size 50m;
SQL> alter database add standby logfile thread 1 group 6 size 50m;
SQL> alter database add standby logfile thread 1 group 7 size 50m;

SQL> select * from v$standby_log;


12、Start Redo Apply on the Standby Database
SQL> alter database recover managed standby database disconnect;


13、Start Remote Archiving(start shipping redo data to the standby from the primary)
SQL> alter system set log_archive_dest_state_2=enable scope=both;

SQL> alter system archive log current;
SQL> alter system switch logfile;

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
SQL> select sequence#,first_time,next_time,archived,applied from v$archived_log order by sequence#;

$ cd /u01/oracle/app/admin/oratest/bdump
$ tail -f alert_oratest.log
====================================================================================================


二、启动standby database
$ sqlplus / as sysdba
SQL> startup mount
SQL> alter database recover managed standby database disconnect;

打开备库(则不能同步数据)
SQL> alter database recover managed standby database cancel;
SQL> alter database open;

关闭备库(同步数据)
SQL> alter database recover managed standby database disconnect from session;


三、设置最大保护模式
命令：alter database set standby database to maximize {protection | availability | performance }

1、standby database
SQL> alter system set log_archive_dest_2='service=ora10 lgwr sync affirm net_timeout=30 valid_for=(online_logfiles,primary_role) db_unique_name=ora10'

2、primary database
SQL> startup mount
SQL> select name,db_unique_name,protection_mode from v$database;
SQL> alter system set log_archive_dest_2='service=oratest lgwr sync affirm net_timeout=30 valid_for=(online_logfiles,primary_role) db_unique_name=oratest';
SQL> alter database set standby database to maximize protection;
SQL> alter database open;

最大保护模式下
至少要有一个备库设置为log_archive_dest_2='service=oratest lgwr sync affirm net_timeout=30'，主库设置为maximize protection；
备用库还是会等待主库切换日志后归档时或归档前才会恢复备用系统，而不是主数据库的操作适时反映在备用库，适时的仅仅是日志文件而已；
在主备库切换的时候可以执行recover managed standby database finish应用适时同步过来的日志信息，这样可以最大限度的保证数据。

********************************************************************************
备库设置好
SQL> show parameter log_archive_dest_1
NAME                          TYPE       VALUE
----------------------------- ---------- ------------------------------
log_archive_dest_1            string     location=e:\oracle\arch\test

SQL> show parameter standby_archive_dest
NAME                          TYPE       VALUE
----------------------------- ---------- ------------------------------
standby_archive_dest          string     e:\oracle\arch\test\standby
********************************************************************************


四、正常切换数据库(Switchover)

若是最大保护模式下，需要在主库上创建standby redo logs
SQL> select group#, thread#, bytes, members from v$log;
------------------------------------------------------
GROUP#    THREAD#      BYTES    MEMBERS
------ ---------- ---------- ----------
     1          1   52428800          2
     2          1   52428800          2
     3          1   52428800          2
------------------------------------------------------
日志数量--(online redo log groups on primary + 1) * maximum of threads
this example is (3 + 1) * 1 = 4 at 50MB

SQL> alter database add standby logfile thread 1 group 4 size 50m;
SQL> alter database add standby logfile thread 1 group 5 size 50m;
SQL> alter database add standby logfile thread 1 group 6 size 50m;
SQL> alter database add standby logfile thread 1 group 7 size 50m;


查看数据库类型
SQL> select name,database_role from v$database;

在主库上执行
SQL> alter database commit to switchover to physical standby with session shutdown;

SQL> shutdown immediate
$ vi initoratest.ora
------------------------------------------------------------------------------
fal_client='ora10'
fal_server='oratest','ora10'
------------------------------------------------------------------------------
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect;


在备库上执行
SQL> alter database commit to switchover to primary;
SQL> shutdown immediate
$ vi initoratest.ora
------------------------------------------------------------------------------
fal_client='oratest'
fal_server='oratest','ora10'
log_archive_dest_2='service=ora10 lgwr sync affirm net_timeout=30 valid_for=(online_logfiles,primary_role) db_unique_name=ora10'
------------------------------------------------------------------------------
SQL> startup pfile='/u01/oracle/app/product/dbs/initoratest.ora'


五、失败切换(Failover)

SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database finish; --有standby redo log
SQL> alter database recover managed standby database finish {force | wait | nowait};

SQL> alter database commit to switchover to primary;

SQL> shutdown immediate
SQL> startup

创建控制文件
SQL> alter database backup controlfile to trace;

$ cd /u01/oracle/app/admin/ora10/udump
===================================================================================================
CREATE CONTROLFILE REUSE DATABASE "ORA10" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oracle/app/oradata/ora10/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/oracle/app/oradata/ora10/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/oracle/app/oradata/ora10/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
--   GROUP 4 '/u01/oracle/app/flash_recovery_area/ORA10/onlinelog/o1_mf_4_6sk7l24t_.log'  SIZE 50M,
--   GROUP 5 '/u01/oracle/app/flash_recovery_area/ORA10/onlinelog/o1_mf_5_6sk7lbjs_.log'  SIZE 50M,
--   GROUP 6 '/u01/oracle/app/flash_recovery_area/ORA10/onlinelog/o1_mf_6_6sk7lz5o_.log'  SIZE 50M,
--   GROUP 7 '/u01/oracle/app/flash_recovery_area/ORA10/onlinelog/o1_mf_7_6sk7m6xc_.log'  SIZE 50M
DATAFILE
  '/u01/oracle/app/oradata/ora10/system01.dbf',
  '/u01/oracle/app/oradata/ora10/undotbs01.dbf',
  '/u01/oracle/app/oradata/ora10/sysaux01.dbf',
  '/u01/oracle/app/oradata/ora10/users01.dbf'
CHARACTER SET AL32UTF8
;
===================================================================================================

SQL> alter tablespace temp add tempfile '/u01/oracle/app/oradata/ora10/temp01.dbf' size 100M;

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

0
0
上一篇： 安装oracle 11g on oracle linux 5
下一篇： 安装ASM数据库 ASMLib方式
请登录后发表评论 登录
全部评论
hjianping

注册时间：2011-04-24

  • 博文量
    80
  • 访问量
    86747

最新文章

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

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

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

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