8、故障问题解决
在之前的系列中,已经将Primary和Standby进行安装,并且redo apply过程已经测试成功。但是,在实验中,还是存在一系列问题需要完善补充。
笔者在测试之后,就直接关闭服务器。重新启动之后,首先启动standby服务实例。
[oracle@SimpleLinux ~]$ export ORACLE_SID=ora11gsy
[oracle@SimpleLinux ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sun May 25 11:36:52 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 372449280 bytes
Fixed Size 1364732 bytes
Variable Size 301993220 bytes
Database Buffers 62914560 bytes
Redo Buffers 6176768 bytes
ORA-00205: error in identifying control file, check alert log for more info
启动mount过程中,需要访问控制文件,报错控制文件不存在。控制文件信息是写入到spfile、pfile中的。所以查看一下控制文件信息:
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/dbs/cntrlora11
gsy.dbf
control_management_pack_access string DIAGNOSTIC+TUNING
在之前还原controlfile的时候,目录明显不是这样。当时信息如下:
RMAN> restore standby controlfile from '/standbybackup/o1_mf_ncnnf_TAG20140524T204716_9r156r7j_.bkp';
Starting restore at 24-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=/u01/app/oradata/ORA11GSY/controlfile/o1_mf_9r18tmv6_.ctl
output file name=/u01/app/fast_recovery_area/ORA11GSY/controlfile/o1_mf_9r18tpkf_.ctl
Finished restore at 24-MAY-14
对应目录中,也的确有对应的OMF文件。
[oracle@SimpleLinux ~]$ cd /u01/app/oradata/ORA11GSY/controlfile/
[oracle@SimpleLinux controlfile]$ ls -l
total 9520
-rw-r-----. 1 oracle oinstall 9748480 May 24 23:15 o1_mf_9r18tmv6_.ctl
这也就解释了为什么在之前创建之后,系统运行正常。重启之后,故障出现的原因。就是由于新的控制文件信息没有写入到spfile或者pfile中,Oracle选择了一个系统缺省控制文件信息而导致的。
此时,我们还发现Oracle还是再使用之前创建pfile启动ora11gsy实例。
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
[oracle@SimpleLinux ~]$ cd /u01/app/oracle/dbs
[oracle@SimpleLinux dbs]$ ls -l
total 19080
-rw-rw----. 1 oracle oinstall 1544 May 24 23:14 hc_ora11g.dat
-rw-rw----. 1 oracle oinstall 1544 May 25 11:37 hc_ora11gsy.dat
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r--r--. 1 oracle oinstall 774 May 24 21:17 initora11g.ora
-rw-r--r--. 1 oracle oinstall 774 May 24 21:18 initora11gsy.ora
-rw-r-----. 1 oracle oinstall 24 Apr 1 12:39 lkORA11G
当前最简单策略是修改initora11gsy.ora文件,加入控制文件目录信息:
control_files='/u01/app/oradata/ORA11GSY/controlfile/o1_mf_9r18tmv6_.ctl','/u01/app/fast_recovery_area/ORA11GSY/controlfile/o1_mf_9r18tpkf_.ctl'
重新启动数据库。
SQL> startup
ORACLE instance started.
Total System Global Area 372449280 bytes
Fixed Size 1364732 bytes
Variable Size 301993220 bytes
Database Buffers 62914560 bytes
Redo Buffers 6176768 bytes
Database mounted.
Database opened.
重新创建spfile,再次启动数据库。
SQL> create spfile from pfile;
File created.
SQL> startup force;
ORACLE instance started.
Total System Global Area 372449280 bytes
Fixed Size 1364732 bytes
Variable Size 301993220 bytes
Database Buffers 62914560 bytes
Redo Buffers 6176768 bytes
Database mounted.
Database opened.
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oradata/ORA11GSY/cont
rolfile/o1_mf_9r18tmv6_.ctl, /
u01/app/fast_recovery_area/ORA
11GSY/controlfile/o1_mf_9r18tp
kf_.ctl
启动standby数据库的redo apply过程。
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select open_mode, database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
9、同步测试
启动standby之后,可以启动primary进行测试。
[oracle@SimpleLinux dbs]$ export ORACLE_SID=ora11g
[oracle@SimpleLinux dbs]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sun May 25 12:07:36 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 372449280 bytes
Fixed Size 1364732 bytes
Variable Size 301993220 bytes
Database Buffers 62914560 bytes
Redo Buffers 6176768 bytes
Database mounted.
Database opened.
Primary端创建一个数据表T。
SQL> create table t as select * from dba_objects;
Table created.
到standby端,我们发现redo apply执行过程。
SQL> conn sys/oracle@ora11gsy as sysdba
Connected.
SQL> select count(*) from t;
COUNT(*)
----------
86032
同步执行成功!
10、参数补充
至此,DG环境已经搭建完成,还需要一些补充过程需要完成。首先,Primary端的standby log是缺失的。如果发生switchover或者failover,我们是没有办法在Primary端进行操作的。
SQL> select group#, dbid from v$standby_log;
GROUP# DBID
---------- ----------------------------------------
Primary端创建standby log日志。
SQL> alter database add standby logfile group 4 size 50m;
Database altered
SQL> alter database add standby logfile group 5 size 50m;
Database altered
SQL> select group#, dbid, bytes, status from v$standby_log;
GROUP# DBID BYTES STATUS
---------- ---------------------------------------- ---------- ----------
4 UNASSIGNED 52428800 UNASSIGNED
5 UNASSIGNED 52428800 UNASSIGNED
standby端同样,如果发生switchover或者failover,在传递日志上也有一些问题。
SQL> conn sys/oracle@ora11gsy as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as SYS
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=ora11gsy valid_for=(online_logfiles,primary_role) db_unique_name=ora11gsy
log_archive_dest_20 string
log_archive_dest_21 string
自己传给自己日志,显然有一些问题,需要进行修改。
SQL> alter system set log_archive_dest_2='SERVICE=ora11g valid_for=(online_logfiles,primary_role) db_unique_name=ora11g';
System altered
11、switchover实验
最后进行一下switchover实验。首先需要在Primary端进行操作,终止操作。
SQL> conn sys/oracle@ora11g as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as SYS
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered
启动primary到standby mount状态。
[oracle@SimpleLinux trace]$ export ORACLE_SID=ora11g
[oracle@SimpleLinux trace]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sun May 25 12:31:28 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 372449280 bytes
Fixed Size 1364732 bytes
Variable Size 327159044 bytes
Database Buffers 37748736 bytes
Redo Buffers 6176768 bytes
SQL> alter database mount standby database;
Database altered.
Standby端进行角色切换。
SQL> conn sys/oracle@ora11gsy as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as SYS
SQL> alter database commit to switchover to primary with session shutdown;
Database altered
SQL> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;
NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME
--------- ------------ -------------------- ---------------- -------------------- ------------------------------
ORA11G ARCHIVELOG MOUNTED PRIMARY NOT ALLOWED ora11gsy
启动服务器:
SQL> alter database open;
Database altered
standby到primary的日志传递是开始的,但是由于原Primary没有启用apply过程,所以applied状态为NO。
SQL> select recid, sequence#, STANDBY_DEST, ARCHIVED, APPLIED from v$archived_log where name='ora11g';
RECID SEQUENCE# STANDBY_DEST ARCHIVED APPLIED
---------- ---------- ------------ -------- ---------
9 13 YES YES NO
11 14 YES YES NO
将Primary端的Redo Apply过程加以应用。
SQL> conn sys/oracle@ora11g as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as SYS
SQL> select open_mode, database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PHYSICAL STANDBY –角色已经变化
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered
查看传递日志的应用情况。
SQL> select recid, sequence#, STANDBY_DEST, ARCHIVED, APPLIED from v$archived_log where sequence#>12;
RECID SEQUENCE# STANDBY_DEST ARCHIVED APPLIED
---------- ---------- ------------ -------- ---------
15 13 NO YES YES
16 14 NO YES YES
下面进行简单测试,在ora11gsy上,清理数据表T。
SQL> delete t;
86032 rows deleted
SQL> commit;
Commit complete
在ora11g上,应用启用,可以看到t数据表数据取值为0。
SQL> conn sys/oracle@ora11g as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as SYS
SQL> alter database recover managed standby database cancel;
Database altered
SQL> alter database open;
Database altered
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered
SQL> select count(*) from t;
COUNT(*)
----------
0
实验switchover成功。
12、结论
Oracle DG是目前比较常见的数据高可用策略,由于操作方式的不同,我们有很多的安装选择的。综合实际安装条件和要求进行选择,是我们需要掌握的要点。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-1173009/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-1173009/