- 论坛徽章:
- 0
|
9.主辅数据库的切换(停止主数据库,启用备用数据库)
修改primary的参数文件initoracle.ora(先做好备份)
增加
*.standby_archive_dest='/home/oracle/oradata/standbyarch'
*.fal_server='standby'
*.fal_client='primary'
*.DB_FILE_NAME_CONVERT=("/home/oracle/oradata/primary","/home/oracle/oradata/oracle")
*.LOG_FILE_NAME_CONVERT=("/home/oracle/oradata/archivelog","/home/oracle/oradata/archivelog")
*.STANDBY_FILE_MANAGEMENT='AUTO'
删除 log_archive_dest_2参数
修改standby的参数文件initoracle.ora(先做好备份)
增加
*.log_archive_dest_2='service=primary mandatory reopen=60'
*.log_archive_dest_state_2='ENABLE'
删除
*.STANDBY_ARCHIVE_DEST=/home/oracle/oradata/archivelog
*.fal_server='standby'
*.fal_client='primary'
*.DB_FILE_NAME_CONVERT=("/home/oracle/oradata/primary","/home/oracle/oradata/oracle")
*.LOG_FILE_NAME_CONVERT=("/home/oracle/oradata/archivelog","/home/oracle/oradata/archivelog")
*.STANDBY_FILE_MANAGEMENT=AUTO
在primary主机上执行
SQL> alter database commit to switchover to physical standby with session shutdown ;
Database altered.
察看primary主机上的后台日志
…………………………….
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
以备用模式(standby)启用主数据
SQL> create spfile from pfile;
SQL> startup nomount;
SQL>show parameter standby_file_management;
SQL> alter database mount standby database;
Database altered.
打开备用数据库(在standby主机上执行)
[oracle@standby oracle]$ sqlplus "/ as sysdba"
SQL> alter database commit to switchover to primary with session shutdown ;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down
SQL>create spfile from pfile;
SQL> startup;
……
Database mounted.
Database opened.
SQL> select SEQUENCE#,GROUP#,STATUS from v$log;
SQL> select sequence#,group#,status from v$log;
SEQUENCE# GROUP# STATUS
---------- ---------- --------------------------------
72 1 INACTIVE
73 2 INACTIVE
74 3 CURRENT
SQL> alter system switch logfile;
System altered.
SQL> select sequence#,group#,status from v$log;
SEQUENCE# GROUP# STATUS
---------- ---------- --------------------------------
75 1 CURRENT
73 2 INACTIVE
74 3 ACTIVE
在primary主机上执行
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
在primary主机上观察日志应用情况
[oracle@primary bdump]$ tail -f alert_oracle.log
Starting datafile 2 recovery in thread 1 sequence 93
Datafile 2: '/opt/oracle/oradata/primary/undotbs01.dbf'
Starting datafile 3 recovery in thread 1 sequence 93
Datafile 3: '/opt/oracle/oradata/primary/users01.dbf'
……………………………………………….
Media recover
10.现在可做一个测试,在standby主机上进行数据修改(standby主机现在做primary)
SQL> create table t as select * from dba_users;
Table created.
SQL> alter system switch logfile;
System altered.
在从库上(primary主机上)以read only打开数据库,执行查询
SQL> select username from t;
select username from t
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.
SQL> select username from t;
USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
OUTLN
WMSYS
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
常见问题
至此,主副的配置已基本完成,可做以下实验来验证配置的准确性。
1. 在主库上新建一个表,看修改时候能马上传到副库上
2. 在主库上新建一个表空间,新增加一个datafile,看修改时候能马上传到副库上(应该是只要在副库上执行恢复模式,就能在副库上看到新建的表空间
3. 在主库上新建一个临时表空间,rename datafile 均不能应用到副库上.
4. 应当实时察看standby库的alert文件,就能清晰明了地知道主副更新的情况。
5. 关于启动关闭顺序
启动的时候,先从库的listener,再启动从库,再启动主库的listener,再主库
关闭的时候,先关闭主库,再启动从库。
6. 8i副数据库切换为主的话,将无法再切为副。主数据库也是! 也就是说,只能完成一次切换,这叫failover!9i可实现主副数据库任意切换,这叫switchover
------------------------------------------------------------------------------------------------------------------
第六点我理解得有些问题,看了版主Rollingpig在itpub上的结论,觉得他说的应该是对的,现将他的陈述列在下面!
Rollingpig
我给个结论吧。在9i 的dataguad环境中:
1。Switch Over 必须是Primary 正常,并且是必须Primary 主动先Switch 成 standby. 然后standby 才能switch 成primary.
2。如果需要作成primary出问题,standby 能接管的话,必须作 failover ,而不是Switch Over
-------------------------------------------------------------------------------------------------------------------
7. 察看主机当前的运行状态:
SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ---------- -------------------- ----------------
PRIMARY MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY
8.查看主数据库日志是否全部传送到副数据库,可查看v$archive_gap,更简单的方法是查看各自主机的日志归档目录的日志序号即可。
11. 常用的一些方法:
可在副数据库上运行一些脚本,确保主数据库上的更新能及时在副数据库上得到实现。可将以下脚本放在crontab表中。
oracle@standby $cat refresh (刷新脚本)
ORACLE_SID=oracle
ORACLE_HOME=/home/oracle/product/9.2.0
export ORACLE_SID ORACLE_HOME
DATE=`date '+%Y%m%d'`
touch /home/oracle/refresh_$DATE.log
$ORACLE_HOME/bin/sqlplus /nolog << EOF
spool /home/oracle/refresh_$DATE.log
connect sys/abc123 as sysdba
shutdown immediate;
quit
EOF
$ORACLE_HOME/bin/sqlplus /nolog << EOF2
spool /home/oracle/refresh_2_$DATE.log
connect sys/abc123 as sysdba
startup nomount pfile=$ORACLE_HOME/dbs/initoracle.ora;
alter database mount standby database;
alter database set standby database to maximize performance;
alter database recover managed standby database disconnect from session;
spool off
EOF2
oracle@standby $cat readonly (更新脚本)
#!/bin/sh
ORACLE_SID=oracle
ORACLE_HOME=/home/oracle/product/9.2.0
export ORACLE_SID ORACLE_HOME
$ORACLE_HOME/bin/sqlplus /nolog << EOF
spool /home/oracle/refresh-read.log
connect sys/abc123 as sysdba
rem change from recover mode to read-only
alter database recover managed standby database cancel;
alter database open read only ;
spool off
EOF
switchover过程:
12.把数据库切换回到主节点
先将standby此时的initoracle.ora恢复为以前是standby时的参数。
在主节点(standby主机上)
SQL> alter database commit to switchover to physical standby;
Database altered.
SQL> shutdown immediate
ORA-01507: database not mounted
statORACLE instance shut down.
SQL> startup nomount pfile=/home/oracle/product/9.2.0/dbs/initoracle.ora;
;
ORACLE instance started.
Total System Global Area 135337420 bytes
Fixed Size 452044 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
在备用节点(在primary主机上)
先将primary此时的initoracle.ora恢复为以前是primary时的参数。
SQL> alter database commit to switchover to primary;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup pfile=/home/oracle/product/9.2.0/dbs/initoracle.ora;
ORACLE instance started.
Total System Global Area 135337420 bytes
Fixed Size 452044 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
衷心致谢: CU oracle斑竹 sshd ,我在配置dataguard时得到他的很多帮助!!
参考文献
Dataguard配置Step by step (eygle大作)
http://www.eygle.com/ha/dataguard-step-by-step.htm
ITPUB 出的第一本书DBA精粹 万正勇(ITPUB ID wzy25) 写的dataguard配置文档,要想深入学习,强烈建议好好看看!
想要word格式的请留下你的email!!
请各位大牛多指教!!
[ 本帖最后由 wangyl1977 于 2006-10-6 14:51 编辑 ] |
|