ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 同一台机子WINDOWS下DATAGUARD的配置

同一台机子WINDOWS下DATAGUARD的配置

原创 Linux操作系统 作者:BEANBLACK 时间:2009-05-12 22:30:37 0 删除 编辑

BEAN BLACK的傻瓜式DataGuard配置过程

此篇只需要简单的数据库的知识你也可以配置成功,不过大家在进行管理与维护的基本知识是不学不行的呀!不过希望此篇的配置成功,可以给大家带来学习、前进的动力!(以下是我在WINDOWS下,同一台机子下配置的完全步骤,可能大家配置时会有一些问题,不用怕,有我在大家一定会成功)

一、准备条件:

1.创建目录

D:\oracle\product\10.2.0\admin\standby

D:\oracle\product\10.2.0\admin\standby\adump

D:\oracle\product\10.2.0\admin\standby\bdump

D:\oracle\product\10.2.0\admin\standby\cdump

D:\oracle\product\10.2.0\admin\standby\udump

D:\oracle\product\10.2.0\admin\standby\pfile

D:\oracle\product\10.2.0\oradata\standby

D:\oracle\product\10.2.0\oradata\standby\archive

D:\oracle\product\10.2.0\oradata\orc1\archive

2. 修改LISTNER.ORA TNSNAMES.ORA

D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\LISTENER.ORA:

# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

      (PROGRAM = extproc)

    )

  (SID_DESC =

      (GLOBAL_NAME = orc1)

      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

      (SID_NAME= orc1)

    )

  (SID_DESC =

      (GLOBAL_NAME = standby)

      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

      (SID_NAME= standby)

    )

  )

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

      (ADDRESS = (PROTOCOL = TCP)(HOST = wangjunliang)(PORT = 1521))

    )

  )

D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\TNSNAMES.ORA:

# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

 

ORC1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = wangjunliang)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orc1)

    )

  )

 

standby =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = wangjunliang)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = standby)

    )

  )

 

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

3. 1)创建实例:

         Oradim –new –sid standby

(2)创建密码文件

orapwd pfile=D:\oracle\product\10.2.0\db_1\database\PWDstandby.ora password=oracle entries=30

 

二、主库操作

1.    主库(orc1

(1)    alter database force logging;

(2)    shutdown immediate;-----startup mount;

(3)    alter database archivelog;---alter database open;

(4)    修改主库PFILE参数文件:

##############################################################################

# Copyright (c) 1991, 2001, 2002 by Oracle Corporation

##############################################################################

 

###########################################

# SGA Memory

###########################################

sga_target=600834048

 

###########################################

# Job Queues

###########################################

job_queue_processes=10

 

###########################################

# Shared Server

###########################################

dispatchers="(PROTOCOL=TCP) (SERVICE=orc1XDB)"

 

###########################################

# Miscellaneous

###########################################

compatible=10.2.0.1.0

 

###########################################

# Security and Auditing

###########################################

audit_file_dest=D:\oracle\product\10.2.0/admin/orc1/adump

 

###########################################

# Sort, Hash Joins, Bitmap Indexes

###########################################

pga_aggregate_target=200278016

 

###########################################

# Database Identification

###########################################

db_domain=""

db_name=orc1

 

###########################################

# File Configuration

###########################################

control_files=("D:\oracle\product\10.2.0\oradata\orc1\control01.ctl", "D:\oracle\product\10.2.0\oradata\orc1\control02.ctl", "D:\oracle\product\10.2.0\oradata\orc1\control03.ctl")

db_recovery_file_dest=D:\oracle\product\10.2.0/flash_recovery_area

db_recovery_file_dest_size=2147483648

 

###########################################

# Cursors and Library Cache

###########################################

open_cursors=300

 

###########################################

# System Managed Undo and Rollback Segments

###########################################

undo_management=AUTO

undo_tablespace=UNDOTBS1

 

###########################################

# Diagnostics and Statistics

###########################################

background_dump_dest=D:\oracle\product\10.2.0/admin/orc1/bdump

core_dump_dest=D:\oracle\product\10.2.0/admin/orc1/cdump

user_dump_dest=D:\oracle\product\10.2.0/admin/orc1/udump

 

###########################################

# Processes and Sessions

###########################################

processes=150

 

###########################################

# Cache and I/O

###########################################

db_block_size=8192

db_file_multiblock_read_count=16

#wjl add primary

DB_UNIQUE_NAME=orc1

LOG_ARCHIVE_CONFIG='DG_CONFIG=(orc1,standby)'

LOG_ARCHIVE_DEST_1='LOCATION=D:\oracle\product\10.2.0\oradata\orc1\archive

                    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

                    DB_UNIQUE_NAME=orc1'

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

remote_login_passwordfile='EXCLUSIVE'

#####

 

FAL_SERVER=standby

FAL_CLIENT=orc1

db_file_name_convert=('D:\oracle\product\10.2.0\oradata\standby','D:\oracle\product\10.2.0\oradata\orc1')

log_file_name_convert=('D:\oracle\product\10.2.0\oradata\standby','D:\oracle\product\10.2.0\oradata\orc1')

STANDBY_FILE_MANAGEMENT=AUTO

(5)为主库创建SPFILE文件

create spfile from pfile=’ D:\oracle\product\10.2.0\admin\orc1\pfile’

6startup force;

7)备份主数据库(RMAN)

rman target /

执行脚本:

run{

allocate channel d1 device type disk;

backup as compressed backupset

incremental level=0

format='D:\backup\inc0_%d_%u'

tag='inc0'

channel=d1

database;

sql "alter system switch  logfile";

backup as compressed backupset

format='D:\backup\arch_%d_%u'

tag='arch'

channel=d1

archivelog all delete input;

backup as compressed backupset

format='D:\backup\standby.ctl';

tag='standby'

channel=d1

current controlfile for standby reuse;

}

三、STANDBY备数据库配置相关操作

1)配置D:\oracle\product\10.2.0\admin\standby\pfile

没有就新建:initstandby.ora

*.audit_file_dest='D:\oracle\product\10.2.0/admin/standby/adump'

*.background_dump_dest='D:\oracle\product\10.2.0/admin/standby/bdump'

*.compatible='10.2.0.1.0'

*.control_files='D:\oracle\product\10.2.0\oradata\standby\control01.ctl','D:\oracle\product\10.2.0\oradata\standby\control02.ctl','D:\oracle\product\10.2.0\oradata\standby\control03.ctl'

*.core_dump_dest='D:\oracle\product\10.2.0/admin/standby/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orc1'

*.db_recovery_file_dest='D:\oracle\product\10.2.0/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=standbyXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=200278016

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=600834048

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='D:\oracle\product\10.2.0/admin/standby/udump'

 

lock_name_space='standby'

*.LOG_ARCHIVE_DEST_1='LOCATION=D:\oracle\product\10.2.0\oradata\standby\archive

                    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

                    DB_UNIQUE_NAME=standby'

*.LOG_ARCHIVE_DEST_2='SERVICE=orc1 LGWR ASYNC

                    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

                    DB_UNIQUE_NAME=orc1'

*.LOG_ARCHIVE_DEST_STATE_1=enable

*.LOG_ARCHIVE_DEST_STATE_2=enable

 

#切换备库为主库是要应用

*.db_file_name_convert=('D:\oracle\product\10.2.0\oradata\orc1','D:\oracle\product\10.2.0\oradata\standby')

*.log_file_name_convert=('D:\oracle\product\10.2.0\oradata\orc1','D:\oracle\product\10.2.0\oradata\standby')

*.standby_archive_dest='D:\oracle\product\10.2.0\oradata\standby\archive'

*.standby_file_management='AUTO'

*.fal_client='standby'

*.fal_server='orc1'

*.service_names='standby'

*.local_listener='(ADDRESS_LIST= (ADDRESS=(PROTOCOL = TCP)(HOST = wangjunliang)(PORT = 1521))(ADDRESS = (PROTOCOL = IPC)(KEY = standby)))'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orc1,standby)'

STANDBY_FILE_MANAGEMENT=AUTO

2 set oracle_sid=standby

sqlplus / nolog

conn / as sysdba;

create spfile from

pfile=’D:\oracle\product\10.2.0\admin\standby\pfile\ initstandby.ora’;

(3)    启动备库到NOMOUNT状态

Startup nomount;

三、在主库上执行以下命令创建备库

    (1) set oracle_sid=orc1

        Sqlplus /nolog

        Conn / as sysdba;

Startup

    (2) set oracle_sid=orc1

   (3) rman target /

      Connect auxiliary sys/oracle@standby

(5)    执行脚本:

run{

allocate auxiliary channel aux1 device type disk;

allocate auxiliary channel aux2 device type disk;

allocate auxiliary channel aux3 device type disk;

set until sequence=7 thread=1;

duplicate target database for standby dorecover;

}

 

四、此时还有个小问题:要给备库建STANDBY REDO LOG,所以在备库上执行以下语句:

Alter database add standby logfile group 4(‘D:\oracle\product\10.2.0\oradata\standby\STANDBYRD04.LOG’) size 50m;

以此为例可以多建几个。

 

 

 

OK,现在备库以处在MOUNT状态了,要随时关注主和备数据库的告警日志文件,一切错误都会呈现在你的面前,有问题大家可以咨询ME,好了,大家可以进行一些基本的管理操作了(在进一步的完善中,看懂能看懂的就OK了,有的我也不懂):

 

 

.一些基本的管理

1.启动备用数据库

startup mount;

2.启动重做应用

alter database recover managed standby database disconnect from session;

3.测试归档操作到物理备数据库

主库上切换日志:

alter system switch logfile;

4.检验物理备用数据库正确执行(要检查备用数据库上的归档重做日志文件,在主数据库上强制日志切换并归档一些联机重做日志文件)

1)确认现有的归档重做日志文件

select sequence#, first_time,next_time from v$archived_log order by sequence#;

2)在主库上强制日志归档

alter system switch logfile;

3)在备库上检查新的重做数据已归档

select sequence#, first_time,next_time from v$archived_log order by sequence#;

4)检查新归档的重做日志文件已应用

select sequence#, applied from v$archived_log order by sequence#;

5)开始重做应用或实时应用

开始重做应用:

alter database recover managed standby database

disconnect from session;

开始实时应用:

alter database recover managed standby database

using current logfile;

在主数据库上,查询V$ARCHIVED_DEST_STATUS视图中的RECOVERY_MODE列它显示备数据库的操作,对于重做应用是MANAGED_RECOVERY,对于实时应用是ANAGED REAL TIME APPLY

5. 在备用库上查看备数据库是重做应用还是实时应用,执行下面查询以发现备数据库是执行重做应用还是实时应用。如果MRP0MRP进程存在,则备数据库应用重做。

select process,status from v$managed_standby;

6.取消重做应用

alter database recover managed standby database cancel;

7.打开备数据库以用于只读或读/写访问

alter database set transaction read only;

alter database open;(语句以只读模式打开物理备数据库)

.STANDBY_FILE_MANAGEMENT初始化参数设置为AUTO时添加数据文件到主备数据库所需的步骤:

1.添加新表空间到主数据库

create tablespace new_ts datafile 'D:\oracle\product\10.2.0\oradata\orc1\t_db2.dbf'

size 1m autoextend on maxsize unlimited;

2.归档当前的联机重做日志文件,使得重做数据将传送并在备用数据库上应用:

alter system archive log current;

3.检验新数据文件添加到主数据库:

select name from v$datafile;

4.检验新数据文件添加到备用数据库:

select name from v$datafile;

.日志文件

1.确定哪些日志文件被应用到备数据库

select sequence#, applied from v$archived_log order by sequence#;

SELECT THREAD#,MAX (SEQUENCE#)AS"LAST_APPLIED_LOG"

FROM V$LOG_HISTORY

GROUP BY THREAD#;

2.确定哪些日志文件还没有被备站点接收到(在主数据库操作)

SELECT LOCAL.THREAD#,LOCAL.SEQUENCE# FROM

(SELECT THREAD#,SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1)

LOCAL WHERE LOCAL.SEQUENCE# NOT IN

(SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND

THREAD#=LOCAL.THREAD#);

3.监控在物理备数据库上的日志应用服务

1)执行下述查询来显示保护模式、保护级别、数据库的角色、和切换状态相关的信息:

SELECT DATABASE_ROLE,DB_UNIQUE_NAME INSTANCE,OPEN_MODE,

PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS

FROM V$DATABASE;

2)执行下述查询来显示快速启动故障转移相关的信息:

SELECT FS_FAILOVER_STATUS FSFO_STATUS,

FS_FAILOVER_CURRENT_TARGET TARGET_STANDBY,

FS_FAILOVER_THRESHOLD THRESHOLD,

FS_FAILOVER_OBSERVER_PRESENT OBS_PRES

FROM V$DATABASE;

3)查询物理备数据库来在备站点监控重做应用和重做传输服务活动:

SELECT PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS

FROM V$MANAGED_STANDBY;

4)快速确定备数据库的同步级别,在物理备数据库上执行下述查询:

SELECT ARCHIVED_THREAD#,ARCHIVED_SEQ#,APPLIED_THREAD#,APPLIED_SEQ#

FROM V$ARCHIVE_DEST_STATUS;

四。为物理备数据库调优日志应用速度

??????????????

五、角色转换

利用EM允许快速启动故障转移,Data Guard broker确定是否需要故障转移并自动发起故障转移到指定的目标备数据库,而不需要DBA介入也没有数据丢失。

1.查看数据库当前角色:

select database_role from v$database;

2.估计每个备数据库的生存能力,用备数据库中数据的流通衡量,以及如果所有可用的重做数据库应用到备数据库,执行角色转换所需的时间。

select * from v$dataguard_stats;

3.如何调整数据库至最大性能模式:

alter database set standby database to maximize performance;

4.检验是否能切换

select switchover_status from v$database;

5.主数据库发起切换

alter database commit to switchover to physical standby;

或:

alter database commit to switchover to physical standby with session shutdown;(当检查为SESSION ACTIVE)

6.关闭并重启前主实例

shutdown immediate;----startup mount;

(此时两个数据库都配置为备数据库)

7.检验切换状态(通过查询目标备数据库上的V$DATABASE

select switchover_status from v$database;

8.切换目标物理数据库角色到主角色

alter database commit to switchover to primary;

否则执行:

alter database commit to switchover to primary with session shutdown;

9.打开新的主数据库(如果物理备数据库自从上次启动后曾经以只读模式打开,你必须关闭目标备数据库并重启)

alter database open;

(其它备数据库不需要重启,切换后仍正常做为备数据库)

10.好,可以开始发送重做到备数据库了

alter system switch logfile;

 

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

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

注册时间:2008-06-16

  • 博文量
    67
  • 访问量
    144069