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库拷贝了一个。万幸没有出什么问题。
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/，如需转载，请注明出处，否则将追究法律责任。
下一篇：
online redo file读书笔记
请登录后发表评论
登录
全部评论
