Oracle/DB2/Postgresql/Mysql/Hadoop/Greenplum/Postgres-xl/Mongodb
分类： Oracle
2012-09-03 07:08:43
SQL> conn store/store_password
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
CUSTOMERS TABLE
PRODUCT_TYPES TABLE
PRODUCTS TABLE
PURCHASES TABLE
EMPLOYEES TABLE
SALARY_GRADES TABLE
PURCHASES_WITH_TIMESTAMP TABLE
PURCHASES_TIMESTAMP_WITH_TZ TABLE
PURCHASES_WITH_LOCAL_TZ TABLE
COUPONS TABLE
PROMOTIONS TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ORDER_STATUS TABLE
PRODUCT_CHANGES TABLE
MORE_PRODUCTS TABLE
MORE_EMPLOYEES TABLE
DIVISIONS TABLE
JOBS TABLE
EMPLOYEES2 TABLE
ALL_SALES TABLE
PRODUCT_PRICE_AUDIT TABLE
REG_EXPS TABLE
BINARY_TEST TABLE
22 rows selected.
SQL> col file_name format a40;
SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------------------
/u03/app/oracle/oradata/ora10g/users01.dbf
/u03/app/oracle/oradata/ora10g/sysaux01.dbf
/u03/app/oracle/oradata/ora10g/undotbs01.dbf
/u03/app/oracle/oradata/ora10g/system01.dbf
FILE_NAME
----------------------------------------
/u03/app/oracle/oradata/ora10g/example01.dbf
SQL>
/u03/app/oracle/oradata/ora10g
[oracle10g@ora9i ora10g]$ ll
total 1064596
-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:16 control01.ctl
-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:16 control02.ctl
-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:16 control03.ctl
-rw-r----- 1 oracle10g oinstall 104865792 Sep 2 19:08 example01.dbf
-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:16 redo01.log
-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:08 redo02.log
-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:08 redo03.log
-rw-r----- 1 oracle10g oinstall 251666432 Sep 2 19:13 sysaux01.dbf
-rw-r----- 1 oracle10g oinstall 503324672 Sep 2 19:13 system01.dbf
-rw-r----- 1 oracle10g oinstall 20979712 Sep 1 07:17 temp01.dbf
-rw-r----- 1 oracle10g oinstall 26222592 Sep 2 19:13 undotbs01.dbf
-rw-r----- 1 oracle10g oinstall 6561792 Sep 2 19:08 users01.dbf
[oracle10g@ora9i ora10g]$ rm -rf *.dbf
[oracle10g@ora9i ora10g]$
[oracle10g@ora9i ora10g]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 2 19:18:07 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba;
Connected.
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
四、 将数据文件分区挂为只读
[root@ora9i ~]# umount -f /u03
[root@ora9i ~]#
[root@ora9i ~]# fdisk -l
Disk /dev/sda: 26.8 GB, 26843545600 bytes
255 heads, 63 sectors/track, 3263 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 25 200781 83 Linux
/dev/sda2 26 286 2096482+ 82 Linux swap
/dev/sda3 287 3263 23912752+ 83 Linux
Disk /dev/sdb: 4294 MB, 4294967296 bytes
255 heads, 63 sectors/track, 522 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 522 4192933+ 83 Linux
Disk /dev/sdc: 1073 MB, 1073741824 bytes
255 heads, 63 sectors/track, 130 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdc1 1 130 1044193+ 83 Linux
Disk /dev/sdd: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdd1 1 2610 20964793+ 83 Linux
[root@ora9i ~]# mount -o ro /dev/sdd1 /u03/
[root@ora9i ~]#
cd /home/tank/ext3grep-0.10.1
./configure
make
make install
注意由于修复数据库文件会保存用户的当前目录下，请考虑当前目录的磁盘空间
[root@ora9i opt]# ext3grep /dev/sdd1 --ls --inode 2 // 一般/根目录的inode值为2
Running ext3grep version 0.10.0
Number of groups: 160
Loading group metadata... done
Minimum / maximum journal block: 1547 / 9749
Loading journal descriptors... sorting... done
The oldest inode block that is still in the journal, appears to be from 1346292905 = Thu Aug 30 10:15:05 2012
Number of descriptors in journal: 6294; min / max sequence numbers: 9918 / 13836
Inode is Allocated
Loading sdd1.ext3grep.stage2............................................ done
The first block of the directory is 1541.
Inode 2 is directory "".
Directory block 1541:
.-- File type in dir_entry (r=regular file, d=directory, l=symlink)
| .-- D: Deleted ; R: Reallocated
Indx Next | Inode | Deletion time Mode File name
==========+==========+----------------data-from-inode------+-----------+=========
0 1 d 2 drwxrwxrwx .
1 2 d 2 drwxrwxrwx ..
2 3 d 11 drwxrwxrwx lost+found
3 4 r 12 rrw-r--r-- 10201_database_linux32.zip
4 5 d 147457 drwxr-xr-x app
5 6 r 13 rrw-r--r-- sysstat-5.0.5-11.rhel4.i386.rpm
6 12 d 393217 drwxr-xr-x database
7 9 r 14 D 1346584012 Sun Sep 2 19:06:52 2012 rrw-r--r-- ext3grep-0.10.2.tar.gz
8 9 d 917507 D 1346584012 Sun Sep 2 19:06:52 2012 drwxr-xr-x ext3grep-0.10.2
9 12 r 15 D 1346584013 Sun Sep 2 19:06:53 2012 rrw-r--r-- ext3grep-0.7.0.tar.gz
10 12 d 2080771 D 1346584013 Sun Sep 2 19:06:53 2012 drwxr-xr-x ext3grep-0.7.0
11 12 r 16 D 1346584012 Sun Sep 2 19:06:52 2012 rrw-r--r-- ext3grep-0.10.0-1.el4.rf.i386.rpm
12 end r 17 rrw-r--r-- sda3.ext3grep.stage1
[root@ora9i opt]#
查找文件名：
[root@ora9i inode.147457]# ext3grep /dev/sdd1 --dump-name
app/oracle/oradata/ora10g/control01.ctl
app/oracle/oradata/ora10g/control02.ctl
app/oracle/oradata/ora10g/control03.ctl
app/oracle/oradata/ora10g/example01.dbf
app/oracle/oradata/ora10g/redo01.log
app/oracle/oradata/ora10g/redo02.log
app/oracle/oradata/ora10g/redo03.log
app/oracle/oradata/ora10g/sysaux01.dbf
app/oracle/oradata/ora10g/system01.dbf
app/oracle/oradata/ora10g/temp01.dbf
app/oracle/oradata/ora10g/undotbs01.dbf
app/oracle/oradata/ora10g/users01.dbf
app/oracle/product
Running ext3grep version 0.10.0
Number of groups: 160
Minimum / maximum journal block: 1547 / 9749
Loading journal descriptors... sorting... done
The oldest inode block that is still in the journal, appears to be from 1346292905 = Thu Aug 30 10:15:05 2012
Number of descriptors in journal: 6294; min / max sequence numbers: 9918 / 13836
Loading sdd1.ext3grep.stage2............................................ done
Restoring app/oracle/oradata/ora10g/example01.dbf
[root@ora9i opt]#
..............................................................................
逐个数据文个进恢复
[root@ora9i ora10g]# pwd
/opt/RESTORED_FILES/app/oracle/oradata/ora10g
[root@ora9i ora10g]# ll
total 890092
-rw-r----- 1 root root 104865792 Sep 2 19:08 example01.dbf
-rw-r----- 1 root root 251666432 Sep 2 19:17 sysaux01.dbf
-rw-r----- 1 root root 503324672 Sep 2 19:13 system01.dbf
-rw-r----- 1 root root 17899520 Sep 1 07:17 temp01.dbf
-rw-r----- 1 root root 26222592 Sep 2 19:13 undotbs01.dbf
-rw-r----- 1 root root 6561792 Sep 2 19:08 users01.dbf
[root@ora9i ora10g]#
八、 将恢复数据库文件复制到Oracle目录
注意重新将分区挂成可读可写，并修改数据库文件的权限
[root@ora9i ora10g]# umount /dev/sdd1
[root@ora9i ora10g]# mount /dev/sdd1 /u03/
[root@ora9i ora10g]# pwd
/opt/RESTORED_FILES/app/oracle/oradata/ora10g
[root@ora9i ora10g]# mv * /u03/app/oracle
oracle/ oracle_base/
[root@ora9i ora10g]# mv * /u03/app/oracle/oradata/ora10g/
[root@ora9i ora10g]# cd /u03/app/oracle/oradata/ora10g/
[root@ora9i ora10g]# ll
total 1064604
-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:18 control01.ctl
-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:18 control02.ctl
-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:18 control03.ctl
-rw-r----- 1 root root 104865792 Sep 2 19:08 example01.dbf
-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:18 redo01.log
-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:08 redo02.log
-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:08 redo03.log
-rw-r----- 1 root root 251666432 Sep 2 19:17 sysaux01.dbf
-rw-r----- 1 root root 503324672 Sep 2 19:13 system01.dbf
-rw-r----- 1 root root 17899520 Sep 1 07:17 temp01.dbf
-rw-r----- 1 root root 26222592 Sep 2 19:13 undotbs01.dbf
-rw-r----- 1 root root 6561792 Sep 2 19:08 users01.dbf
[root@ora9i ora10g]# chown oracle10g:oinstall *
[root@ora9i ora10g]# ll
total 1064604
-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:18 control01.ctl
-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:18 control02.ctl
-rw-r----- 1 oracle10g oinstall 7061504 Sep 2 19:18 control03.ctl
-rw-r----- 1 oracle10g oinstall 104865792 Sep 2 19:08 example01.dbf
-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:18 redo01.log
-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:08 redo02.log
-rw-r----- 1 oracle10g oinstall 52429312 Sep 2 19:08 redo03.log
-rw-r----- 1 oracle10g oinstall 251666432 Sep 2 19:17 sysaux01.dbf
-rw-r----- 1 oracle10g oinstall 503324672 Sep 2 19:13 system01.dbf
-rw-r----- 1 oracle10g oinstall 17899520 Sep 1 07:17 temp01.dbf
-rw-r----- 1 oracle10g oinstall 26222592 Sep 2 19:13 undotbs01.dbf
-rw-r----- 1 oracle10g oinstall 6561792 Sep 2 19:08 users01.dbf
[root@ora9i ora10g]#
[oracle10g@ora9i ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 2 20:03:14 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba;
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 230686720 bytes
Fixed Size 1218652 bytes
Variable Size 71305124 bytes
Database Buffers 150994944 bytes
Redo Buffers 7168000 bytes
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
SQL>
SQL> conn store/store_password
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
CUSTOMERS TABLE
PRODUCT_TYPES TABLE
PRODUCTS TABLE
PURCHASES TABLE
EMPLOYEES TABLE
SALARY_GRADES TABLE
PURCHASES_WITH_TIMESTAMP TABLE
PURCHASES_TIMESTAMP_WITH_TZ TABLE
PURCHASES_WITH_LOCAL_TZ TABLE
COUPONS TABLE
PROMOTIONS TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ORDER_STATUS TABLE
PRODUCT_CHANGES TABLE
MORE_PRODUCTS TABLE
MORE_EMPLOYEES TABLE
DIVISIONS TABLE
JOBS TABLE
EMPLOYEES2 TABLE
ALL_SALES TABLE
PRODUCT_PRICE_AUDIT TABLE
REG_EXPS TABLE
BINARY_TEST TABLE
22 rows selected.
SQL> select * from jobs;
JOB NAME
--- --------------------
WOR Worker
MGR Manager
ENG Engineer
TEC Technologist
PRE President
SQL>
－－－－－致此Oracle数据库已经可以正常访问
