记录一次linux崩溃，oracle数据库的恢复

        周一刚上班就接到香港机房那边通知，我们的一台linux数据 库服务器 系统崩溃，无法使用。经过一天的等待，第二天临时挂载系统，让我可以把数据库的数据备份出来，登陆系统进行检查还好oracle相关的文件都在。 Linux环境下安装oracle软件，由于远程服务器，采用静默安装。
以前存在的oracle文件目录之类全部重新命名（oracle_dir_old），然后新建一样的目录路径去安装。安装步骤网上很多，随便去搜。下面我开始具体解决思路：

安装完成之后把新的目录重新(oracle_dir_new),把 （oracle_dir_old ）重新还原成目录的名字。
sqlplus / as sysdba
1.首先启动到nomount状态用以前pfile文件
SQL>startup nomount pfile=$ORACLE_HOME/dbs/initora11g1.ora
SQL>alter database mount;
SQL>alter database open;
数据库报错
Job "PORTALADM"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jan 3 00:04:52 2008 elapsed 0 00:00:05


oracle@localhost trace]$ tail -400f alert_ora11g.log 
Errors in file /home/app/oracle/admin/ora11g/adump/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_11709.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/data/app/oracle/oradata/ora11g/system01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /home/app/oracle/admin/ora11g/adump/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_11709.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/data/app/oracle/oradata/ora11g/sysaux01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /home/app/oracle/admin/ora11g/adump/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_11709.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/data/app/oracle/oradata/ora11g/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /home/app/oracle/admin/ora11g/adump/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_11709.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/data/app/oracle/oradata/ora11g/users01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /home/app/oracle/admin/ora11g/adump/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_11709.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/data/o12c/index/payadm_index_o1.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /home/app/oracle/admin/ora11g/adump/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_11709.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/data/o12c/payadm_data01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /home/app/oracle/admin/ora11g/adump/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_11709.trc:


2. 路径变了需要数据文件迁移；在mount状态部分视图是可以查询的,修改redo log的位置
旧的数据文件移到新的位置
SQL> col MEMBER for a60
SQL> /


    GROUP# STATUS  TYPE    MEMBER IS_
---------- ------- ------- ------------------------------------------------------------ ---
5   ONLINE  /redolog/app/oracle/oradata/ora11g/redo15.log NO
6   ONLINE  /redolog/app/oracle/oradata/ora11g/redo16.log NO
7   ONLINE  /redolog/app/oracle/oradata/ora11g/redo17.log NO
8   ONLINE  /redolog/app/oracle/oradata/ora11g/redo18.log NO
9   ONLINE  /redolog/app/oracle/oradata/ora11g/redo19.log NO
10   ONLINE  /redolog/app/oracle/oradata/ora11g/redo11.log NO
11   ONLINE  /redolog/app/oracle/oradata/ora11g/redo12.log NO
12   ONLINE  /redolog/app/oracle/oradata/ora11g/redo13.log NO
13   ONLINE  /redolog/app/oracle/oradata/ora11g/redo14.log NO

写一个批量修改的sql;

SQL> select 'alter database rename file '||''''||member||''''||' to '||chr(39)||replace(member,'/redolog/app/oracle/oradata/ora11g/','/home/redolog/app/oracle/oradata/ora11g/')||''';'   
from v$logfile  where member like '/redolog/app/oracle/oradata/ora11g/%';   2   


'ALTERDATABASERENAMEFILE'||''''||MEMBER||''''||'TO'||CHR(39)||REPLACE(MEMBER,'/REDOLOG/APP/ORACLE/ORADATA/ORA11G/','/HOME/REDOLOG/APP/ORACLE/ORADATA/ORA11G/')||''';'
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter database rename file '/redolog/app/oracle/oradata/ora11g/redo15.log' to '/home/redolog/app/oracle/oradata/ora11g/redo15.log';
alter database rename file '/redolog/app/oracle/oradata/ora11g/redo16.log' to '/home/redolog/app/oracle/oradata/ora11g/redo16.log';
alter database rename file '/redolog/app/oracle/oradata/ora11g/redo17.log' to '/home/redolog/app/oracle/oradata/ora11g/redo17.log';
alter database rename file '/redolog/app/oracle/oradata/ora11g/redo18.log' to '/home/redolog/app/oracle/oradata/ora11g/redo18.log';
alter database rename file '/redolog/app/oracle/oradata/ora11g/redo19.log' to '/home/redolog/app/oracle/oradata/ora11g/redo19.log';
alter database rename file '/redolog/app/oracle/oradata/ora11g/redo11.log' to '/home/redolog/app/oracle/oradata/ora11g/redo11.log';
alter database rename file '/redolog/app/oracle/oradata/ora11g/redo12.log' to '/home/redolog/app/oracle/oradata/ora11g/redo12.log';
alter database rename file '/redolog/app/oracle/oradata/ora11g/redo13.log' to '/home/redolog/app/oracle/oradata/ora11g/redo13.log';
alter database rename file '/redolog/app/oracle/oradata/ora11g/redo14.log' to '/home/redolog/app/oracle/oradata/ora11g/redo14.log';


9 rows selected.

批量执行上面的语句，查看状态；

SQL> select * from v$logfile;


    GROUP# STATUS  TYPE    MEMBER IS_
---------- ------- ------- ------------------------------------------------------------ ---
5   ONLINE  /home/redolog/app/oracle/oradata/ora11g/redo15.log NO
6   ONLINE  /home/redolog/app/oracle/oradata/ora11g/redo16.log NO
7   ONLINE  /home/redolog/app/oracle/oradata/ora11g/redo17.log NO
8   ONLINE  /home/redolog/app/oracle/oradata/ora11g/redo18.log NO
9   ONLINE  /home/redolog/app/oracle/oradata/ora11g/redo19.log NO
10   ONLINE  /home/redolog/app/oracle/oradata/ora11g/redo11.log NO
11   ONLINE  /home/redolog/app/oracle/oradata/ora11g/redo12.log NO
12   ONLINE  /home/redolog/app/oracle/oradata/ora11g/redo13.log NO
13   ONLINE  /home/redolog/app/oracle/oradata/ora11g/redo14.log NO


9 rows selected.

已经成功改变。

3. 接着修改临时文件的位置

查询以前临时文件相关信息

SQL> select TS,RFILE, STATUS,ENABLED,BYTES,BLOCKS,CREATE_BYTES,BLOCK_SIZE,NAME from v$tempfile;


          TS#     RFILE# STATUS  ENABLED      BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- ------------------------------------------------------------
3    1 ONLINE  READ WRITE  0     0 6442450944  8192 /data/app/oracle/oradata/ora11g/
7    1 ONLINE  READ WRITE  0     0 5368709120  8192 /data/temdata/ssayadm_temp.dbf
10    1 ONLINE  READ WRITE  0     0 5368709120  8192 /data/temdata/ssabyadm_temp.dbf
16    1 ONLINE  READ WRITE  0     0 4294967296  8192 /data/temdata/ssrtabyadm_temp.dbf
13    1 ONLINE  READ WRITE  0     0 5368709120  8192 /data/temdata/psssrtabyadm_temp.dbf

批量语法修改如下
SQL>select 'alter database rename file '||''''||name||''''||' to '||chr(39)||replace(name,'/data/temdata/','/home/temdata/')||''';'  from v$tempfile   
  where name like '/data/temdata/%';
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter database rename file '/data/temdata/ssayadm_temp.dbf' to '/home/temdata/ssayadm_temp.dbf';
alter database rename file '/data/temdata/ssabyadm_temp.dbf' to '/home/temdata/ssabyadm_temp.dbf';
alter database rename file '/data/temdata/ssrtabyadm_temp.dbf' to '/home/temdata/ssrtabyadm_temp.dbf';
alter database rename file '/data/temdata/psssrtabyadm_temp.dbf' to '/home/temdata/psssrtabyadm_temp.dbf';
alter database rename file '/data/app/oracle/oradata/ora11g/temp01.dbf' to '/home/app/oracle/oradata/ora11g/temp01.dbf';

执行上面的语，在此查询状态
SQL> select * from v$tempfile;


     FILE# CREATION_CHANGE# CREATION_TIME       TS#     RFILE# STATUS  ENABLED      BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- ------------------------------------------------------------
1       2879 2015-01-21 09:02:31 3    1 ONLINE  READ WRITE 6442450944 786432 6442450944  8192 /home/app/oracle/oradata/ora11g/temp01.dbf
2     196947 2015-01-21 09:56:09 7    1 ONLINE  READ WRITE 5368709120 655360 5368709120  8192 /home/temdata/ssayadm_temp.dbf
3     198182 2015-01-21 10:07:20 10    1 ONLINE  READ WRITE 5368709120 655360 5368709120  8192 /home/temdata/ssabyadm_temp.dbf
4     199461 2015-01-21 10:15:51 16    1 ONLINE  READ WRITE 4294967296 524288 4294967296  8192 /home/temdata/ssrtabyadm_temp.dbf
5     199676 2015-01-21 10:18:10 13    1 ONLINE  READ WRITE 5368709120 655360 5368709120  8192 /home/temdata/psssrtabyadm_temp.dbf

临时也已经好了。

4 . 数据文件的恢复
  --查询以前的状态
SQL> select name from v$datafile;


NAME
------------------------------------------------------------
/data/app/oracle/oradata/ora11g/system01.dbf
/data/app/oracle/oradata/ora11g/sysaux01.dbf
/data/app/oracle/oradata/ora11g/undotbs01.dbf
/data/app/oracle/oradata/ora11g/users01.dbf
/data/o12c/index/payadm_index_o1.dbf
/data/o12c/cps_payadm_data01.dbf
/data/o12c/cps_gwadm_data01.dbf
/data/o12c/index/cps_gwadm_index_o1.dbf
/data/o12c/cps_portaladm_data01.dbf
/data/o12c/index/cps_portaladm_data01.dbf
/data/o12c/cps_rptadm_data01.dbf


NAME
------------------------------------------------------------
/data/o12c/index/cps_rptadm_index_o1.dbf

批量修改  
 SQL> select 'alter database rename file '||''''||name||''''||' to '||chr(39)||replace(name,'/data/app/oracle/oradata/ora11g/','/home/app/oracle/oradata/ora11g/')||''';' 
from v$datafile 
where name like '/data/app/oracle/oradata/ora11g/%';
  
'ALTERDATABASERENAMEFILE'||''''||NAME||''''||'TO'||CHR(39)||REPLACE(NAME,'/DATA/APP/ORACLE/ORADATA/ORA11G/','/HOME/APP/ORACLE/ORADATA/ORA11G/')||''';'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter database rename file '/data/app/oracle/oradata/ora11g/system01.dbf' to '/home/app/oracle/oradata/ora11g/system01.dbf';
alter database rename file '/data/app/oracle/oradata/ora11g/sysaux01.dbf' to '/home/app/oracle/oradata/ora11g/sysaux01.dbf';
alter database rename file '/data/app/oracle/oradata/ora11g/undotbs01.dbf' to '/home/app/oracle/oradata/ora11g/undotbs01.dbf';
alter database rename file '/data/app/oracle/oradata/ora11g/users01.dbf' to '/home/app/oracle/oradata/ora11g/users01.dbf';
  
  
SQL>select 'alter database rename file '||''''||name||''''||' to '||chr(39)||replace(name,'/data/o12c/','/home/o12c/') ||''';' from v$datafile  where name like '/data/o12c/%';
  
 'ALTERDATABASERENAMEFILE'||''''||NAME||''''||'TO'||CHR(39)||REPLACE(NAME,'/DATA/O12C/','/HOME/O12C/')||''';'
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter database rename file '/data/o12c/index/payadm_index_o1.dbf' to '/home/o12c/index/payadm_index_o1.dbf';
alter database rename file '/data/o12c/cps_payadm_data01.dbf' to '/home/o12c/cps_payadm_data01.dbf';
alter database rename file '/data/o12c/cps_gwadm_data01.dbf' to '/home/o12c/cps_gwadm_data01.dbf';
alter database rename file '/data/o12c/index/cps_gwadm_index_o1.dbf' to '/home/o12c/index/cps_gwadm_index_o1.dbf';
alter database rename file '/data/o12c/cps_portaladm_data01.dbf' to '/home/o12c/cps_portaladm_data01.dbf';
alter database rename file '/data/o12c/index/cps_portaladm_data01.dbf' to '/home/o12c/index/cps_portaladm_data01.dbf';
alter database rename file '/data/o12c/cps_rptadm_data01.dbf' to '/home/o12c/cps_rptadm_data01.dbf';
alter database rename file '/data/o12c/index/cps_rptadm_index_o1.dbf' to '/home/o12c/index/cps_rptadm_index_o1.dbf';


8 rows selected.
归档目录在mmount状态早就切换过了，这个简单就不说了。
到此恢复完成
SQL>alter database open;
这是后台打印的日志
Beginning crash recovery of 1 threads
 parallel recovery started with 23 processes
Started redo scan
Completed redo scan
 read 1498 KB redo, 158 data blocks need recovery
Started redo application at
 Thread 1: logseq 977, block 116576
Recovery of Online Redo Log: Thread 1 Group 7 Seq 977 Reading mem 0
  Mem# 0: /home/redolog/app/oracle/oradata/ora11g/redo17.log
Completed redo application of 0.68MB
Completed crash recovery at
 Thread 1: logseq 977, block 119572, scn 70319221
 158 data blocks read, 158 data blocks written, 1498 redo k-bytes read
Wed Jan 02 23:05:02 2008
Expanded controlfile section 9 from 584 to 5840 records
Requested to grow by 5256 records; added 18 blocks of records
Thread 1 advanced to log sequence 978 (thread open)
Thread 1 opened at log sequence 978
  Current log# 8 seq# 978 mem# 0: /home/redolog/app/oracle/oradata/ora11g/redo18.log
Successful open of redo thread 1
Starting background process QMNC
Wed Jan 02 23:05:07 2008
QMNC started with pid=49, OS id=13282 
Completed: alter database open
Wed Jan 02 23:05:10 2008
Starting background process CJQ0
Wed Jan 02 23:05:10 2008
CJQ0 started with pid=51, OS id=13296 
Setting Resource Manager plan SCHEDULER[0x32CF]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Wed Jan 02 23:05:13 2008
Starting background process VKRM
Wed Jan 02 23:05:13 2008
VKRM started with pid=50, OS id=13298 

现在就可以登录数据库，把自己需要的资料备份出来。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26689862/viewspace-1719767/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26689862/viewspace-1719767/