客户一套核心系统由一台Oracle Database 11.2.0.3.4单机和一台Active Data Guard组成,分别运行在两台PC服务器上,Oracle Linux 5.8 x86_64bit操作系统,两台服务器都未接存储设备;由于原有设备老旧等原因,现在要将这套Oracle数据库系统(主库和ADG库)迁移到新采购的两台服务器上,不跨版本,也不跨平台。为了最小化停机时间,我们先用目前最新的RMAN 0级备份在两台新服务器上restore database,之后将到目前为止的所有1级备份和归档日志restore和recover到两个数据库上,在主数据库正常停机之后把剩余的归档和在线Redo日志文件应用到两个新数据库,使他们的数据到最新,且是一致的,最后打开主数据库,恢复ADG的同步,整个过程从凌晨0点开始停机,一直持续到了4:20才迁移成功,之间遇到了不少小的问题,再次进行记录:
1.RMAN报错。 RMAN在应用部分归档日志之后收到如下报错: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 11/13/2014 00:03:03 ORA-00283: recovery session canceled due to errors RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/oradata/bak/archivelog/2014_11_12/o1_mf_1_62193_b65oryl5_.arc' ORA-00283: recovery session canceled due to errors ORA-19755: could not open change tracking file ORA-19750: change tracking file: '/u01/app/oracle/block_change_file' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3
2.resetlogs之后检查主库和备库的日志同步情况。 通常在主库执行以下的SQL语句可以用于检查主库和备库日志同步情况: SQL> select dest_id,thread#,max(sequence#) from v$archived_log where resetlogs_change#=936497858 group by dest_id,thread#;
由于主数据库在打开的时候执行了alter database open resetlogs,所以查询v$archived_log要跟上resetlogs_change#,确保查看的是现在数据库的归档情况,resetlogs_change#可以通过v$database.resetlogs_change#获得,另外,由于resetlogs打开了数据库,所以sequence#重新开始计数。
3.对活动的Standby LogFile的处理。
停止主数据库之后,我们是想将原来的所有Online Redo Logfile和Standby Logfile都拷贝到新服务器,通过alter databae rename file ... to ...的方式进行重命名,没想到的是Active的Standby Logfile无法进行重命名(收到报错:ORA-01511: error in renaming log/data files),但又必须将Standby Logfile文件放在指定的目录下,下面是查询v$logfile的状态:
SQL> select group#,member from v$logfile;
GROUP# ---------- MEMBER -------------------------------------------------------------------------------- 3 /oradata/orcl/REDO03.LOG
2 /oradata/orcl/REDO02.LOG
1 /oradata/orcl/REDO01.LOG
GROUP# ---------- MEMBER -------------------------------------------------------------------------------- 4 /u01/app/oracle/oradata/orcl/sredo01.log
5 /oradata/orcl/sredo02.log
6 /oradata/orcl/sredo03.log
GROUP# ---------- MEMBER -------------------------------------------------------------------------------- 7 /oradata/orcl/sredo04.log
SQL> alter database drop logfile group 4; alter database drop logfile group 4 * ERROR at line 1: ORA-00315: log 4 of thread 1, wrong thread # 0 in header ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/orcl/sredo01.log'
尝试DROP GROUP组失败。
SQL> alter database add logfile member '/oradata/orcl/sredo01.log' to group 4; alter database add logfile member '/oradata/orcl/sredo01.log' to group 4 * ERROR at line 1: ORA-16161: Cannot mix standby and online redo log file members for group 4
尝试添加成员失败。
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4; ALTER DATABASE CLEAR LOGFILE GROUP 4 * ERROR at line 1: ORA-00350: log 4 of instance orcl (thread 1) needs to be archived ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/orcl/sredo01.log'
由于未归档所以直接CLEAR失败。
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4;
注意:以上的操作可能在备库上无法完成,解决方法是,在主库完成Standby Logfile迁移之后,主库在MOUNT状态下创建新的for Standby Controlfile(alter database create standby controlfile as '/tmp/controlf.ctl'; ),将新的Standby Controlfile和Standby Logfile传递到相同的位置,恢复备库到一致状态,打开备库,开始应用日志即可。可以参考文章《Oracle Active Data Guard调整案例[2]》:http://blog.itpub.net/23135684/viewspace-1262326/ Data Guard备库一定要是一致的状态才能open read only打开,否者执行alter database recover managed standby database应用日志,恢复到一致性的状态,再open read only。
4.主库和备用库无法实时同步。 原有的主数据库和备用数据库配置的时最高性能模式下的实时同步,完成迁移后无法进行实时同步,但归档切换后的同步正常。 首先主数据库的LOG_ARCHIVE_DEST_2配置好了LGWR AFFIRM SYNC参数,备库执行了如下应用命令: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; 且主库和备用库的日志传输服务正常工作。
在主数据库和备用数据库执行如下SQL语句: SQL> select group#,thread#,status,bytes/1024/1024 mb from v$standby_log;
SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 4 ('/oradata/orcl/sredo01.log') SIZE 100M reuse;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 5 ('/oradata/orcl/sredo02.log') SIZE 100M reuse;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 6 ('/oradata/orcl/sredo03.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 7 ('/oradata/orcl/sredo04.log') SIZE 100M reuse; Database altered. SQL> select group#,thread#,status,bytes/1024/1024 mb from v$standby_log;