ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DataGuard配置说明

DataGuard配置说明

原创 Linux操作系统 作者:marvinoracle 时间:2011-06-01 15:38:01 0 删除 编辑

1. Enable Forced Logging 强制日志  主数据库
      force logging(强制日志)模式alter database force logging来使得Oracle无论什么操作都进行redo的写入,通过select force_logging from v$database可以看到当前数据库强制日志模式的状态

  SQL> ALTER DATABASE FORCE LOGGING;(ALTER DATABASE no FORCE LOGGING;)

2. Create a Password File

 orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl password=oracle entries=30 force=y;

3. Configure a Standby Redo Log(创建多一个日子)

 SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/primary/redo4.log') SIZE 5M;

  SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/primary/redo5.log') SIZE 5M;

  SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/primary/redo6.log') SIZE 5M;

  SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/app/oracle/oradata/primary/redo7.log') SIZE 5M; 

  SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
  SQL> select group#,type,member from v$logfile;

4. Set Primary Database Initialization Parameters

    DB_NAME=orcl

    DB_UNIQUE_NAME=primary  注意:加唯一名字

    LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'

    LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'

    LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' 网络服务名

    LOG_ARCHIVE_DEST_STATE_1=ENABLE

    LOG_ARCHIVE_DEST_STATE_2=ENABLE

    LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

    FAL_SERVER=standby

    FAL_CLIENT=primary

    DB_FILE_NAME_CONVERT='standby','primary'   如果路径一样就不需要这个参数了

    LOG_FILE_NAME_CONVERT='/u01/app/oracle/arch/','/u01/app/oracle/arch/' 如果路径一样就不需要这个参数了

    STANDBY_FILE_MANAGEMENT=AUTO

 

5. Enable Archiving

 

   SQL> SHUTDOWN IMMEDIATE;

   SQL> STARTUP MOUNT;

   SQL> ALTER DATABASE ARCHIVELOG;

   SQL> ALTER DATABASE OPEN;

 

 

6. Create a Backup Copy of the Primary Database Datafiles

  

   SQL>SHUTDOWN IMMEDIATE

   scp datafile

 

7. Create a Control File for the Standby Database

 

   SQL> STARTUP MOUNT;

   SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/standby.ctl';

   SQL> ALTER DATABASE OPEN;

在备用数据库下作

8. Prepare an Initialization Parameter File for the Standby Database 修改参数文件

 

    DB_NAME=orcl

    DB_UNIQUE_NAME=standby

    CONTROL_FILE='/../../standby.ctl'     LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'

    LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby' 倒过来了

    LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'

    LOG_ARCHIVE_DEST_STATE_1=ENABLE

    LOG_ARCHIVE_DEST_STATE_2=ENABLE

    LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

    FAL_SERVER=standby

    FAL_CLIENT=primary

    DB_FILE_NAME_CONVERT='standby','primary'倒过来了

    LOG_FILE_NAME_CONVERT='/u01/app/oracle/arch/','/u01/app/oracle/arch/'

    STANDBY_FILE_MANAGEMENT=AUTO


9. Copy Files from the Primary System to the Standby System
包括所有目录结构~~~主库生成的standby files等等

10. Configure listeners for the primary and standby databases

primary 数据库
vi listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = stu134.uplooking.com)(PORT = 1521))
  )

vi tnsnames.ora
standby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = stu195.uplooking.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


standby 数据库

vi listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = stu195.uplooking.com)(PORT = 1521))
  )


vi tnsnames.ora
primary =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = stu134.uplooking.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
  )

11. Start the Physical Standby Database启动物理备用数据库到mount

    

    SQL> STARTUP MOUNT;

 

12. Start Redo Apply

 

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;   变为后台进程DISCONNECT    (开始接收主库传输redo数据)


over

 

13. Verify the Physical Standby Database Is Performing Properly

     Step 1 Identify the existing archived redo log files in standby database

            SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; 

     Step 2 Force a log switch to archive the current online redo log file in primary database

            SQL> ALTER SYSTEM SWITCH LOGFILE;

     Step 3 Verify the new redo data was archived on the standby database.

            SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
 FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;  

     Step 4 Verify new archived redo log files were applied on the standby database.

            SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
 ORDER BY SEQUENCE#;

 standby 查看命令
 ====================================================
 = startup nomount
 = alter database  mount standby database;
 = select name,database_role from v$database;
 = alter database recover managed standby database disconnect from session;   接收日志
 = alter system archive log current;
 = alter database recover managed standby database cancel;        取消接收日志
 = alter database open read only;
 = select sequence#,first_time,next_time from v$archived_log order by sequence#;
 = select sequence#,applied from v$archived_log order by sequence#;
 =
 ====================================================
switchover

 

in primary database

  1 select switchover_status from v$database;

  2 to_standby

     alter database commit to switchover to physical standby;

   session active

    alter database commit to switchover to physical standby with session shutdown;

  3 shutdown immediate

    startup mount

in standby database

  1 select switchover_status from v$database;

  2 alter database commit to switchover to primary;

   alter database commit to switchover to primary with session shutdown;

  3 alter database open

   shutdown immediate

    startup
主动切换后以前的主库变为从库,状态为需要恢复,要恢复之后才能接收现在主库的日志

保护数据库通过日志的传输
物理备用:按快恢复的  (起恢复进程)应用日志必须在mount下,不作恢复可以在open read  only,可以备份
逻辑备用:在日志中挖掘出的sql恢复的在备用数据库上执行的叫逻辑备用
需要有数据库角色管理
角色的切换(不是自动的)
三中保护模式

Maximum protection最大保护模式:不会丢失数据


数据库的版本必须一致,一定要归档模式,
RFS远程文件服务器
MRP管理恢复进程
LSP逻辑服务器进程
FAL让日志连续起来可以手动拷贝
日志文件数量必须>=主数据库的日志
测试结果:
在primary数据库查询结果如下:是双份的才算配置成功
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         1 15-APR-11 15-APR-11
         1 15-APR-11 15-APR-11
         2 15-APR-11 15-APR-11
         2 15-APR-11 15-APR-11
         3 15-APR-11 15-APR-11
         3 15-APR-11 15-APR-11
         4 15-APR-11 15-APR-11
         4 15-APR-11 15-APR-11
         5 15-APR-11 15-APR-11
         5 15-APR-11 15-APR-11
         6 15-APR-11 15-APR-11

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         6 15-APR-11 15-APR-11
         7 15-APR-11 15-APR-11
         7 15-APR-11 15-APR-11
         8 15-APR-11 15-APR-11
         8 15-APR-11 15-APR-11
         9 15-APR-11 15-APR-11
         9 15-APR-11 15-APR-11
        10 15-APR-11 15-APR-11
        10 15-APR-11 15-APR-11
        11 15-APR-11 15-APR-11
        11 15-APR-11 15-APR-11

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
        12 15-APR-11 15-APR-11
        12 15-APR-11 15-APR-11
        13 15-APR-11 15-APR-11
        13 15-APR-11 15-APR-11

26 rows selected.

    
在standby库查询结果如下
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         1 15-APR-11 15-APR-11
         2 15-APR-11 15-APR-11
         3 15-APR-11 15-APR-11
         4 15-APR-11 15-APR-11
         5 15-APR-11 15-APR-11
         6 15-APR-11 15-APR-11
         7 15-APR-11 15-APR-11
         8 15-APR-11 15-APR-11
         9 15-APR-11 15-APR-11
        10 15-APR-11 15-APR-11
        11 15-APR-11 15-APR-11

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
        12 15-APR-11 15-APR-11
        13 15-APR-11 15-APR-11

13 rows selected.

SQL>

 

 


 

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

请登录后发表评论 登录
全部评论

注册时间:2010-09-02

  • 博文量
    67
  • 访问量
    181820