晚上在外面吃饭时接到一朋友电话，问我怎么把老数据库中的数据转移到新数据库中。新老数据库版本都是Oracle 10g R2 10201，新库是我一周前教他用冷备的方法由老库复制出来的，但没有立刻使用。后来老库的数据又发生了变化，他想用exp & imp的方法把最新的数据导入新库中。我问了一下数据的分布情况，他说应该集中在两个用户user1和user2（并非实际名称）中。一想这简单，只要在老库用exp按用户导出，再在新库用imp按用户导入就行了，让他等我回家后帮他解决。
到家又向他了解了一些具体情况，并登到老数据库的主机上查看用户的默认表空间、权限等信息。然后，帮他制定了具体的操作步骤，大致分为五步：
1.从老数据库将user1和user2分别导出，将导出文件传输到新数据库的主机上。
2.删除新数据库中的用户user1和user2。
3.在新数据库中重新创建这两个用户。
4.授予新创建的用户必要的权限、角色。
5.分别向新数据库中导入user1和user2的数据。
结果他执行第一步就碰到了问题，使用exp导出时提示用户名或口令不正确。一开始userid指定的是这两个用户本身，以为是朋友记错了口令；于是让他改用system用户，还是不行；修改system用户口令后再试，问题依旧。我尝试通过sqlplus以user1、user2和system用户连接数据库，均失败，提示“ORACLE initialization or shutdown in progress”（ORA-01033错误）。但是，以SYSDBA权限连接数据库没有问题，查看v$instance视图，status字段为OPEN。
[@more@]感觉数据库状态不正常，检查alert日志，发现最后一次启动数据库过程中出现了ORA-07445错误，数据库最终没有成功打开（没有“Completed: alter database open”的记录），日志片断如下：......
Fri Oct 23 17:54:22 2009
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Fri Oct 23 17:54:43 2009
Starting background process QMNC
QMNC started with pid=16, OS id=908
Fri Oct 23 17:54:51 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_3560.trc:
ORA-07445: 出现异常错误: 核心转储 [ILLEGAL_INSTRUCTION] [unable_to_trans_pc] [PC:0x62651D0C] [] [] []
Fri Oct 23 17:59:53 2009
Restarting dead background process QMNC
QMNC started with pid=15, OS id=2808
Fri Oct 23 18:05:24 2009
Restarting dead background process QMNC
QMNC started with pid=15, OS id=5160
Fri Oct 23 18:05:26 2009
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
继续检查alert日志，发现数据库倒数第二次启动的开始时间为17:42，并于17:46成功打开。而最后一次启动的开始时间为17:53，从 17:46到17:53没有数据库关闭的记录。也就是说，在这段时间内数据库发生了异常关闭。从朋友处证实了这一点，他在此时因嫌系统响应太慢，直接按 power键关闭了主机。看来数据库是因为这次异常关机发生了故障，而这时又有一个坏消息，朋友没有按照顺序进行操作，还没完成导出就将新库中的两个用户删掉了。我登时心里一凉，如果老库中的数据无法导出，而新库中的又删掉了，那不是数据彻底丢失了。我告诉朋友这个情况，把他吓坏了。
以前从没碰到过这样的问题，不过看一些高人介绍经验时说，发生类似故障时不要轻易尝试重启数据库，那样做很可能会让情况变得更糟糕。这时想起新库是用老库的冷备份建的，也许那些备份文件还保留着。一问，朋友很肯定地告诉我，他的移动硬盘里还存着当时拷贝的文件，真是如释重负。于是，让他将新库删掉，还按照上次的方法重建。本想趁这个时候尝试下用别的方法将老库中的数据导出，却发现数据库已经停了。检查alert日志发现，此前出现了一系列的ORA- 07445错误，最终在00:31的又一次错误后PMON进程终止，致使数据库关闭。数据库日志片断如下：
......
Fri Oct 23 18:33:21 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_5152.trc:
ORA-07445: exception encountered: core dump [ILLEGAL_INSTRUCTION] [_xsAlErrWSDestruct+0] [PC:0x2AED19C] [] [] []
......
Fri Oct 23 19:13:23 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_5020.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_ktbtfini+9] [PC:0x1815B4D] [ADDR:0xFFFFFFE0] [UNABLE_TO_READ] []
......
Fri Oct 23 19:38:47 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_432.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_ktbtfini+9] [PC:0x1815B4D] [ADDR:0xFFFFFFE0] [UNABLE_TO_READ] []
......
Fri Oct 23 20:26:18 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_10144.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_ktbtfini+9] [PC:0x1815B4D] [ADDR:0xFFFFFFE0] [UNABLE_TO_READ] []
Fri Oct 23 20:27:39 2009
Restarting dead background process QMNC
QMNC started with pid=15, OS id=5800
Fri Oct 23 20:29:20 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_8872.trc:
ORA-07445: exception encountered: core dump [ILLEGAL_INSTRUCTION] [_xsAlErrWSDestruct+0] [PC:0x2AED19C] [] [] []
......
Fri Oct 23 21:06:47 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_8420.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_klccbk+0] [PC:0x16D9988] [ADDR:0x0] [UNABLE_TO_WRITE] []
ORA-01033: ORACLE initialization or shutdown in progress
Fri Oct 23 21:10:33 2009
MMNL absent for 11842 secs; Foregrounds taking over
Fri Oct 23 21:12:09 2009
Restarting dead background process QMNC
QMNC started with pid=15, OS id=9780
Fri Oct 23 21:13:48 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_9492.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_ktbtfini+9] [PC:0x1815B4D] [ADDR:0xFFFFFFE0] [UNABLE_TO_READ] []
......
Fri Oct 23 22:17:16 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_11744.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_xsobjftc+2001] [PC:0x2881C15] [ADDR:0xF00000DF] [UNABLE_TO_WRITE] []
......
Fri Oct 23 22:35:01 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_13204.trc:
ORA-07445: exception encountered: core dump [ILLEGAL_INSTRUCTION] [unable_to_trans_pc] [PC:0x7C351EF3] [] [] []
Fri Oct 23 22:35:33 2009
MMNL absent for 16945 secs; Foregrounds taking over
Fri Oct 23 22:36:17 2009
Restarting dead background process QMNC
QMNC started with pid=15, OS id=13896
Fri Oct 23 22:36:33 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_14020.trc:
ORA-07445: exception encountered: core dump [ILLEGAL_INSTRUCTION] [_xsAlErrWSDestruct+0] [PC:0x2AED19C] [] [] []
......
Fri Oct 23 23:25:41 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_11820.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_klccbk+0] [PC:0x16D9988] [ADDR:0x0] [UNABLE_TO_WRITE] []
ORA-01033: ORACLE initialization or shutdown in progress
Fri Oct 23 23:27:05 2009
Restarting dead background process QMNC
QMNC started with pid=16, OS id=13832
Fri Oct 23 23:28:12 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_12644.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_ktbtfini+9] [PC:0x1815B4D] [ADDR:0xFFFFFFE0] [UNABLE_TO_READ] []
Fri Oct 23 23:29:33 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_12604.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_ktbtfini+9] [PC:0x1815B4D] [ADDR:0xFFFFFFE0] [UNABLE_TO_READ] []
......
Fri Oct 23 23:46:07 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_14976.trc:
ORA-07445: 出现异常错误: 核心转储 [ILLEGAL_INSTRUCTION] [_evaopn2+0] [PC:0x1BBDF10] [] [] []
......
Sat Oct 24 00:07:04 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_15984.trc:
ORA-00600: 内部错误代码, 参数: [qkaPobs1], [], [], [], [], [], [], []
......
Sat Oct 24 00:31:02 2009
Errors in file e:oracleproduct10.2.0adminhamobudumphamob_ora_12688.trc:
ORA-07445: exception encountered: core dump [ILLEGAL_INSTRUCTION] [_xsAlErrWSDestruct+0] [PC:0x2AED19C] [] [] []
Sat Oct 24 00:31:09 2009
Errors in file e:oracleproduct10.2.0adminhamobbdumphamob_pmon_3504.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_skgpalive+42] [PC:0x6038A38A] [ADDR:0xFFFFFFFF] [UNABLE_TO_READ] []
Sat Oct 24 00:31:18 2009
LGWR: terminating instance due to error 472
Sat Oct 24 00:31:18 2009
Errors in file e:oracleproduct10.2.0adminhamobbdumphamob_ckpt_3524.trc:
ORA-00472: PMON process terminated with error
Sat Oct 24 00:31:18 2009
Errors in file e:oracleproduct10.2.0adminhamobbdumphamob_mman_3512.trc:
ORA-00472: PMON process terminated with error
Sat Oct 24 00:31:18 2009
Errors in file e:oracleproduct10.2.0adminhamobbdumphamob_reco_3532.trc:
ORA-00472: PMON process terminated with error
Sat Oct 24 00:31:18 2009
Errors in file e:oracleproduct10.2.0adminhamobbdumphamob_smon_3528.trc:
ORA-00472: PMON process terminated with error
Sat Oct 24 00:31:18 2009
Errors in file e:oracleproduct10.2.0adminhamobbdumphamob_dbw0_3516.trc:
ORA-00472: PMON process terminated with error
Sat Oct 24 00:31:18 2009
Errors in file e:oracleproduct10.2.0adminhamobbdumphamob_psp0_3508.trc:
ORA-00472: PMON process terminated with error
Instance terminated by LGWR, pid = 3520
好在已经知道还有之前的备份文件，最坏的情况下也能把数据库恢复到一周前的状态，这时充分体会到老大所说的“备份重于一切”的意义。一会儿功夫，新库重建好了，不禁底气又足了一些。尝试启动老库，和前一次一样，数据库没有成功打开，可能因此无法对普通用户进行验证。在“Oracle9i 数据库管理基础”的SG中查到以SYSDBA权限使用exp和imp的方法，将userid指定为：
"sys/password as sysdba"
使用这种方法导出user1，这次可以执行，但导出完一部分表后就出错中断；另一个用户user2的情况类似，没有提示任何错误，导出过程戛然而止。考虑可能是SGA中的buffer cache等内存结构存在问题，导致exp无法成功执行。这时又想到exp的一个参数direct，通过直接路径进行导出，可以加快导出速度。猜想也许加上这个参数后，避开部分内存结构，导出就能顺利完成了。一试果然灵验，两个用户均能导出，并且过程中没有任何告警。
至此，总算获得了老库中的最新数据，这也可以算是direct这个参数的另类用法了吧。运气还是不错的，如果老库无法启动起来，又或是上面的方法无效，可能只有老大那个级别的人物才搞得定了。本来要做的是件简单的事情，因为一系列的人为失误险些造成难以挽回的损失，当然也不能怪我那位朋友，他并不是专门搞数据库管理的。但是，对于真正的DBA来讲，在做任何操作时都要严谨，正如老大强调的另一句话“Think thrice before you act.（三思而后行）”
愿所有DBA共勉！
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11662464/viewspace-1028109/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/11662464/viewspace-1028109/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
