ITPub博客

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

dataguard 配置 async 说明

原创 Linux操作系统 作者:season0891 时间:2011-08-05 11:09:26 0 删除 编辑

primary DB:
rman target /
crosscheck backup;
crosscheck archivelog all;

delete noprompt backup;
delete noprompt copy;

sqlplus /nolog
conn / as sysdba
alter system switch logfile;
----------------------------------------------------------------
primary DB:
1.
--设置每条操作必须生成日志
ALTER DATABASE FORCE LOGGING;

2.
alter database add standby logfile group 4 '/u01/app/oracle/bk/redo04.log' size 50m;
alter database add standby logfile group 5 '/u01/app/oracle/bk/redo05.log' size 50m;
alter database add standby logfile group 6 '/u01/app/oracle/bk/redo06.log' size 50m;
alter database add standby logfile group 7 '/u01/app/oracle/bk/redo07.log' size 50m;

SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

3.
拷贝主库数据文件复制到其它节点

/u01/app/oracle/admin/cash/udump
---主库beijing参数文件----
*.compatible='10.2.0.1.0'
*.audit_file_dest='/oracle/admin/orcl/adump'
*.background_dump_dest='/oracle/admin/orcl/bdump'
*.core_dump_dest='/oracle/admin/orcl/cdump'
*.user_dump_dest='/oracle/admin/orcl/udump'
*.control_files='/oracle/oradata/orcl/control01.ctl'
*.db_block_size=8192
*.pga_aggregate_target=20m
*.sga_target=160m
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest_size=2g
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.job_queue_processes=10
*.db_name='orcl' 
*.db_unique_name='orcl'
*.log_archive_config='dg_config=(orcl,standby)'
*.log_archive_dest_1='location=/oracle/arch/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_2='service=STANDBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.log_archive_max_processes=4
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_archive_dest='/u01/arcstd'
*.standby_file_management='AUTO'
*.DB_FILE_NAME_CONVERT=('/oracle/oradata/orcl/','/u01/app/oracle/oradata/standby/','/oracle/oradata/orcl/','/u01/app/oracle/oradata/standby/')
*.LOG_FILE_NAME_CONVERT=('/oracle/oradata/orcl/','/u01/app/oracle/oradata/standby/')
#------------------------------------------------------------------------------------------

创建从库参数文件
-------------------------------------------------
*.background_dump_dest='/u01/app/oracle/admin/standby/bdump'
*.core_dump_dest='/u01/app/oracle/admin/standby/cdump'
*.user_dump_dest='/u01/app/oracle/admin/standby/udump'
*.compatible='10.2.0.1.0'
*.db_name='cash'
*.db_unique_name='standby'
*.control_files='/u01/app/oracle/oradata/standby/control01.ctl'
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.db_block_size=8192
*.sga_target=160m
*.pga_aggregate_target=20m
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.standby_file_management='AUTO'
*.log_archive_config='dg_config=(cash,standby)'
*.log_archive_dest_1='location=/u01/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=beijing VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME='orcl'
*.standby_archive_dest='/u01/arcstd'
*.DB_FILE_NAME_CONVERT=('/u01/app/oracle/bk/','/u01/app/oracle/oradata/standby/','/u01/app/oracle/oradata/','/u01/app/oracle/oradata/standby/')
*.LOG_FILE_NAME_CONVERT=('/u01/app/oracle/bk/','/u01/app/oracle/oradata/standby/')

-------

select name,value from v$parameter where name in ('db_name','instance_name','db_unique_name','service_names')
/

listener和tnsnames的配置。
--primary DB listener.ora----

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.234)(PORT = 1521))
  )

SID_LIST_LISTENER =
  (SID_LIST =   
    (SID_DESC =
       (GLOBAL_DBNAME = cash)
       (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)  
       (SID_NAME = cash)
      )     
  )

--standby DB listener.ora----
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1521))
  )

SID_LIST_LISTENER =
  (SID_LIST =   
    (SID_DESC =
       (GLOBAL_DBNAME = standby)
       (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)  
       (SID_NAME = standby)
      )
  )

-----primary & standby DB's tnsnames.ora----

cash =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.234)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cash)
    )
  )
 
standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = standby)
    )
  )

--------test------------
192.168.1.234:
sqlplus /nolog
connsys/oracle@standbyas sysdba

192.168.1.111:
sqlplus /nolog
connsys/oracle@cashas sysdba


备份主库:
Rman target sys/oracle
backup database include current controlfile FOR STANDBY plus archivelog;

将主库的 RMAN 备份传到备用节点
scp /u01/app/oracle/rmanbk/*oracle@192.168.1.111:/u01/app/oracle/rmanbk/

export ORACLE_SID=standby
将备用节点数据库启动到nomount状态
sqlplus /nolog
startup nomount

rman targetsys/oracle@liuauxiliarysys/oracle@lll

RMAN> duplicate target database for standby;

在主库中切换日志
ALTER SYSTEM SWITCH LOGFILE;

在备库中查看
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

在从库中进行管理恢复
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

在主库中再切换日志
ALTER SYSTEM SWITCH LOGFILE;

在从库中再查看
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

在从库中校验归档日志是否被应用
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

检查归档是否能正确传输(primary DB):
col dest_name for a30
col error for a20
select dest_name,status,error from v$archive_dest where substr(dest_name,-1) in (1,2);

在从库中取消管理恢复的方法:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

alter database open;

--------------------------------------------------------------
主从切换测试:
orcl@ SYS AS SYSDBA> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

Elapsed: 00:00:00.21

orcl@ SYS AS SYSDBA> select PROTECTION_MODE,DATABASE_ROLE from v$database;

PROTECTION_MODE      DATABASE_ROLE
-------------------- ----------------
MAXIMUM PERFORMANCE  PRIMARY

Elapsed: 00:00:00.05

主变从:
orcl@ SYS AS SYSDBA> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

Database altered.

Elapsed: 00:00:43.06
orcl@ SYS AS SYSDBA> select PROTECTION_MODE,DATABASE_ROLE from v$database;
select PROTECTION_MODE,DATABASE_ROLE from v$database
                                          *
ERROR at line 1:
ORA-01507: database not mounted

orcl@ SYS AS SYSDBA> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
orcl@ SYS AS SYSDBA> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1247900 bytes
Variable Size              62915940 bytes
Database Buffers          100663296 bytes
Redo Buffers                2945024 bytes
Database mounted.
Database opened.
orcl@ SYS AS SYSDBA> select PROTECTION_MODE,DATABASE_ROLE from v$database;

PROTECTION_MODE      DATABASE_ROLE
-------------------- ----------------
MAXIMUM PERFORMANCE  PHYSICAL STANDBY –主变从成功

Elapsed: 00:00:00.04
orcl@ SYS AS SYSDBA> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
db_file_name_convert                 string
db_name                              string      orcl
db_unique_name                       string      orcl
global_names                         boolean     FALSE
instance_name                        string      orcl
lock_name_space                      string
log_file_name_convert                string
service_names                        string      orcl

orcl@ SYS AS SYSDBA> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

Elapsed: 00:00:00.12

影响切换的进程有
进程名 描述 解决方法
CJQ0 job队列进程 将JOB_QUEUE_PROCESSES动态改为0,但是不要改spfile
QMN0 高级队列时间管理器 将AQ_TM_PROCESSES动态改为0,但是不要改spfile
DBSNMP oem 的代理 执行emctl stop agent停止代理

此时必须执行以下语句切换。
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
3、将主数据切换为备用数据库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

从变主:
aux@ SYS AS SYSDBA> select PROTECTION_MODE,DATABASE_ROLE from v$database;

PROTECTION_MODE      DATABASE_ROLE
-------------------- ----------------
MAXIMUM PERFORMANCE  PHYSICAL STANDBY

Elapsed: 00:00:00.01
aux@ SYS AS SYSDBA> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

Elapsed: 00:00:01.01
aux@ SYS AS SYSDBA> select PROTECTION_MODE,DATABASE_ROLE from v$database;

PROTECTION_MODE      DATABASE_ROLE
-------------------- ----------------
MAXIMUM PERFORMANCE  PRIMARY –从变主成功

Elapsed: 00:00:00.02
aux@ SYS AS SYSDBA> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      F:\oracle\oradata\orcl, D:\Aux
                                                 DB
db_name                              string      orcl
db_unique_name                       string      auxdb
global_names                         boolean     FALSE
instance_name                        string      auxdb
lock_name_space                      string
log_file_name_convert                string      F:\oracle\oradata\orcl, D:\Aux
                                                 DB
service_names                        string      auxdb

aux@ SYS AS SYSDBA> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

Elapsed: 00:00:00.04

------参数简介-----------------------
db_unique_name 小名,用来区分主从数据库

remote_login_passwordfile= none忽略口令文件检查,EXCLUSIVE只有当前实例可以使用该口令文件但超级用户可以是多个,
                           SHARED多个实例可以共享口令文件但超级用户只能是SYS

log_archive_config='dg_config=(ORA10,STANDBY)' 允许发送和接收远程redo logs,参数值是DB_UNIQUE_NAME

log_archive_dest_1='location=G:\oracle\oradata\ORA10\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA10'
--VALID_FOR=(redo_log_type, database_role)
--默认值VALID_FOR=(ALL_LOGFILES, ALL_ROLES)


log_archive_dest_2='service=STANDBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA10'

standby_archive_dest= 只与备库管理恢复模式有关

standby_file_management={ MANUAL | AUTO } 主库有增加和减少物理文件的动作是否应用到备用数据库当中

----------------------------------------
fal_client='STANDBY' (Fetch Archive Log)
fal_server='PRIMARY'
这两个参数不加也能够追加断档的日志
----------------------------------------
log_archive_dest_2='service=STANDBY LGWR ASYNC=20480 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA10'
ARCH/LGWR 默认使用ARCH模式传送,即归档日志级别传送redo,最大可用和最大保护模式需要 LGWR模式传送,即语句级传送redo条目,需要
          建立备库的online redolog并且要使用日志的实时应用模式即LGWR SYNC AFFIM
SYNC/ASYNC 网络I/O操作方式,SYNC是实时模式,ASYNC异步模式,ASYNC=0 ~ 102400 blocks
AFFIM/NOAFFIM 磁盘I/O模式,AFFIM同步并且发送成功写操作到主库;NOAFFIM主库无需等待备库日志写成功与否

come from:http://space.itpub.net/40011/viewspace-674070


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

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

注册时间:2008-06-10

  • 博文量
    791
  • 访问量
    1949528