ITPub博客

dataguard读书笔记

原创 Linux操作系统 作者:foxmile 时间:2007-12-23 15:12:29 0 删除 编辑
1、主从库均Install Oracle 9i,且只选择安装软件,不创建数据库

2. 在主库上使用DBCA,创建dbguard实例

3. 创建测试环境,主库创建之后将以前有的一个逻辑备份导入主库。

4. 修改数据库为归档方式

SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 10月 9 20:02:24 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys/sys as sysdba;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter system set log_archive_dest_1='c:\arc_zero' scope=both;

SQL> alter system set log_archive_dest_2='service=standby' scope=both;

SQL> alter system set log_archive_start=true scope=spfile;

SQL> alter database open;

SQL> archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            C:\arc_zero
最早的概要日志序列     29
下一个存档日志序列   31
当前日志序列           31
SQL> show parameter archive

NAME                                 TYPE                 VALUE
------------------------------------ -------------------- --------------------
archive_lag_target                   integer              0
log_archive_dest                     string
log_archive_dest_1                   string               LOCATION=C:\arc_zero
log_archive_dest_10                  string
log_archive_dest_2                   string               service=standby
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string

NAME                                 TYPE                 VALUE
------------------------------------ -------------------- --------------------
log_archive_dest_9                   string
log_archive_dest_state_1             string               enable
log_archive_dest_state_10            string               enable
log_archive_dest_state_2             string               enable
log_archive_dest_state_3             string               enable
log_archive_dest_state_4             string               enable
log_archive_dest_state_5             string               enable
log_archive_dest_state_6             string               enable
log_archive_dest_state_7             string               enable
log_archive_dest_state_8             string               enable
log_archive_dest_state_9             string               enable

NAME                                 TYPE                 VALUE
------------------------------------ -------------------- --------------------
log_archive_duplex_dest              string
log_archive_format                   string               ARC%S.%T
log_archive_max_processes            integer              2
log_archive_min_succeed_dest         integer              1
log_archive_start                    boolean              FALSE
log_archive_trace                    integer              0
remote_archive_enable                string               true
standby_archive_dest                 string               %ORACLE_HOME%\RDBMS
SQL>
SQL> shutdown immediate;
 


5. 创建standby 控制文件和便于修改的pfile

SQL> alter database force logging;

数据库已更改。

SQL> select name,force_logging from v$database;

NAME FOR

--------- ---

DBGUARD YES

SQL> create pfile='c:\standbypfile.ora' from spfile;

文件已创建。

SQL> alter database create standby controlfile as 'c:\oraclecontrol01.ctl';


6. 确定需要复制相应的数据文件、日志文件等,并关闭数据库

SQL> select file_name from dba_data_files;

SQL> select file_name from dba_data_files;

FILE_NAME
----------------------------------------------
D:\ORACLE\ORADATA\ZERO\SYSTEM01.DBF
D:\ORACLE\ORADATA\ZERO\UNDOTBS01.DBF
D:\ORACLE\ORADATA\ZERO\DRSYS01.DBF
D:\ORACLE\ORADATA\ZERO\INDX01.DBF
D:\ORACLE\ORADATA\ZERO\TOOLS01.DBF
D:\ORACLE\ORADATA\ZERO\USERS01.DBF
D:\ORACLE\ORADATA\ZERO\XDB01.DBF
D:\ORACLE\ORADATA\ZERO\MOF.ORA

已选择8行。


SQL> select member from v$logfile;

MEMBER
------------------------------------------------
D:\ORACLE\ORADATA\ZERO\REDO03.LOG
D:\ORACLE\ORADATA\ZERO\REDO02.LOG
D:\ORACLE\ORADATA\ZERO\REDO01.LOG

7. 创建standby服务器相应的Oracle目录,并把主库文件复制到standby机器的指定目录下

Mkdir d:\oracle\admin\zero\bdump

Mkdir d:\oracle\admin\zero\cdump

Mkdir d:\oracle\admin\zero\create

Mkdir d:\oracle\admin\zero\pfile

Mkdir d:\oracle\admin\zero\udump

Mkdir D:\oracle\oradata\zero

Mkdir C:\arc_zero
 


8. 创建dbguard的window服务

Oradim –NEW –SID zero –STARDMODE manual
 
复制通过主库创建的standby控制文件,并分别复制为control02.ctl,control03.ctl,并拷贝到相应的目录下

复制通过主库创建的参数文件并加以修改,添加以下信息

*.standby_archive_dest='c:\arc_zero'
*.fal_client='satndby'
*.fal_server='primary'
*.standby_file_management='auto'
*.remote_archive_enable='true'

9. 创建密码文件

C:>orapwd file=d:\oracle\ora92\database\PWDzero.ora password=test
或者直接拷贝主库的pwd文件

10. 配置主从服务器的listener.ora

--主库

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

)

)

--从库
LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

)

)


11. 分别配置主从服务器的tnsname.ora保持一致

STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.38)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = zero)
      (SERVER = DEDICATED)
    )
  )

PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.45)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = zero)
      (SERVER = DEDICATED)
    )
  )


12. 侦听查看主从机是否能够监听


C:\Documents and Settings\zero>tnsping standby

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 16-12月-2007 01:39:33

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

已使用的参数文件:
D:\oracle\ora92\network\admin\sqlnet.ora


已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.38)(PORT = 1521))) (CO
NNECT_DATA = (SID = zero) (SERVER = DEDICATED)))
OK(30毫秒)

C:\Documents and Settings\zero>
Tnsping primary
 
C:\Documents and Settings\zero>tnsping standby

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 16-12月-2007 01:39:33

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

已使用的参数文件:
D:\oracle\ora92\network\admin\sqlnet.ora


已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.45)(PORT = 1521))) (CO
NNECT_DATA = (SID = zero) (SERVER = DEDICATED)))
OK(30毫秒)

C:\Documents and Settings\zero>

13.启动物理Standby数据库

SQL> conn sys/sys@standby as sysdba
已连接到空闲例程。
SQL> startup nomount;
ORACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> alter database mount standby database;

数据库已更改。


14. 在Standby数据库上,初始化Log Apply 服务

SQL> alter database recover managed standby database disconnect from session;

数据库已更改。

15. 安装完的的验证

在primary上

SQL> select sequence#,first_time,next_time from v$archived_log;

SEQUENCE# FIRST_TIME NEXT_TIME

 SEQUENCE# FIRST_TIME NEXT_TIME
---------- ---------- ----------
        16 10-12月-07 10-12月-07
        17 10-12月-07 10-12月-07
        18 10-12月-07 11-12月-07
        19 11-12月-07 11-12月-07
        20 11-12月-07 12-12月-07
        21 12-12月-07 12-12月-07
        22 12-12月-07 13-12月-07
        23 13-12月-07 14-12月-07
        25 14-12月-07 15-12月-07
        24 14-12月-07 14-12月-07
        26 15-12月-07 16-12月-07

 SEQUENCE# FIRST_TIME NEXT_TIME
---------- ---------- ----------
        27 16-12月-07 16-12月-07
        28 16-12月-07 16-12月-07

已选择79行。

在standby上

SQL> select sequence#,first_time,next_time from v$archived_log;

未选定行



在primary上

SQL>  alter system archive log current;

系统已更改。

SQL> select sequence#,first_time,next_time from v$archived_log;

SEQUENCE# FIRST_TIME NEXT_TIME

---------- ---------- ----------

 SEQUENCE# FIRST_TIME NEXT_TIME
---------- ---------- ----------
        16 10-12月-07 10-12月-07
        17 10-12月-07 10-12月-07
        18 10-12月-07 11-12月-07
        19 11-12月-07 11-12月-07
        20 11-12月-07 12-12月-07
        21 12-12月-07 12-12月-07
        22 12-12月-07 13-12月-07
        23 13-12月-07 14-12月-07
        25 14-12月-07 15-12月-07
        24 14-12月-07 14-12月-07
        26 15-12月-07 16-12月-07

 SEQUENCE# FIRST_TIME NEXT_TIME
---------- ---------- ----------
        27 16-12月-07 16-12月-07
        28 16-12月-07 16-12月-07
        29 16-12月-07 16-12月-07

已选择80行。


在standby上

SQL> select sequence#,first_time,next_time from v$archived_log;

 SEQUENCE# FIRST_TIME NEXT_TIME
---------- ---------- ----------
        29 16-12? -07 16-12? -07
 


SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APP
---------- ---
        29 YES
        26 YES
        27 YES
        28 YES

SQL>


--Yes即OK
 


在standby上

SQL> select process,status from v$managed_standby;

PROCESS STATUS
------- ------------
MRP0    WAIT_FOR_LOG
RFS     ATTACHED
RFS     RECEIVING

SQL>

16、看到有个文档说standby的redo log要比主库多,就添加了两组重做日志
SQL> alter database recover managed standby database finish;

数据库已更改。

SQL> alter database add standby logfile group 4 ('d:/oracle/oradata/zero/standby04.redo') size 100m;

数据库已更改。


SQL> alter database add standby logfile group 5 ('d:/oracle/oradata/zero/standby05.redo') size 100m;

数据库已更改。


附:standby的pfile
*.background_dump_dest='D:\oracle\admin\zero\bdump'
*.compatible='9.2.0.0.0'
*.control_files='D:\oracle\oradata\zero\control01.ctl','D:\oracle\oradata\zero\control02.ctl','D:\oracle\oradata\zero\control03.ctl'
*.core_dump_dest='D:\oracle\admin\zero\cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='zero'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=zeroXDB)'
*.fal_client='satndby'
*.fal_server='primary'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='zero'
*.java_pool_size=33554432
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=C:\arc_zero'
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_archive_enable='true'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=50331648
*.sort_area_size=524288
*.sql_trace=TRUE
*.standby_archive_dest='c:\arc_zero'
*.standby_file_management='auto'
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\zero\udump'


维护注意:
1、如何增加standby库的standby redo log

SQL> alter database recover managed standby database finish;

Database altered.

如果没有停止自动恢复状态就添加standby logfile,会报错:
ORA-01156: recovery in progress may need access to files

SQL> alter database add standby logfile group 4 ('d:/oracle/oradata/test/standby04.redo') size 10m;

2、如何让dataguard从max performance转为max available模式
主库的init文件修改:
*.log_archive_dest_2='service=STANDBY LGWR SYNC AFFIRM'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

从库的init文件修改:
*.log_archive_dest_2='service=PRIMARY LGWR SYNC AFFIRM'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
同时从库需要将自动归档设置为true:
SQL>alter system set log_archive_start=true scope=spfile;

3、切换角色
查看当前数据库的角色:
select database_role from v$database;
主库操作:
查看主库是否已经准备好切换:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
1 row selected

如果为SESSIONS ACTIVE,检查是否有活动的会话并杀掉它
确认为TO STANDBY之后,执行:

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
SQL> shutdown immediate;
ORA-01507: 未安装数据库


ORACLE 例程已经关闭。

SQL> startup nomount;
ORACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> alter database mount standby database;

数据库已更改。

SQL> alter database recover managed standby database disconnect from session;
数据库已更改。
SQL>

备库操作:
SQL> select switchover_status from v$database;

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

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
------------------
SESSIONS ACTIVE

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

数据库已更改。

SQL> alter database open;
alter database open
*
ERROR 位于第 1 行:
ORA-01507: 未安装数据库


SQL> shutdown immediate;
ORA-01507: 未安装数据库


ORACLE 例程已经关闭。
SQL> startup;
ORACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
数据库已经打开。

SQL>
SQL> select count(1) from t1;

  COUNT(1)
----------
     56622

SQL>

角色切换成功

期间我把PWDzero.ora不小心给删除了。结果主库报错:

*
ERROR 位于第 1 行:
ORA-01990: ??????'D:\oracle\ora92\DATABASE\PWDzero.ORA'??
ORA-27041: ??????
OSD-04002: N^7(4r?*ND<~
O/S-Error: (OS 2) O5M3UR2;5=V86(5DND<~!#

吓我一跳。后来查看了原因,从standby库拷贝了一个。万幸没有出什么问题。

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

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

注册时间:2007-12-10

  • 博文量
    107
  • 访问量
    196430