用户表空间的数据文件损坏
备份
SQL> alter tablespace users begin backup;
Tablespace altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/db254/system01.dbf
/db254/undotbs01.dbf
/db254/sysaux01.dbf
/db254/users01.dbf
/db254/mytbs01.dbf
SQL> ! cp /db254/users01.dbf /u01/oracle/
SQL> alter tablespace users end backup;
Tablespace altered.
SQL>
产生多次交易
update scott.emp set sal=sal+1;
commit;
alter system switch logfile;
.....
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 809 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1609 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1259 500 30
7566 JONES MANAGER 7839 02-APR-81 2984 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1259 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2859 30
7782 CLARK MANAGER 7839 09-JUN-81 2461.2 10
7788 SCOTT ANALYST 7566 19-APR-87 3009 20
7839 KING PRESIDENT 17-NOV-81 5011.2 10
7844 TURNER SALESMAN 7698 08-SEP-81 1509 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1109 20
7900 JAMES CLERK 7698 03-DEC-81 959 30
7902 FORD ANALYST 7566 03-DEC-81 3009 20
7934 MILLER CLERK 7782 23-JAN-82 1311.2 10
14 rows selected.
SQL> ! ls
1 1.txt afiedt.buf arc1 a.sh Desktop mystat2.sql mystat.sql oratab
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/db254/system01.dbf
/db254/undotbs01.dbf
/db254/sysaux01.dbf
/db254/users01.dbf
/db254/mytbs01.dbf
SQL> ! cp 1.txt /db254/users01.dbf
将数据文件损坏 还能查到数据 因为db_buffer_cache中有缓存块
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 809 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1609 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1259 500 30
7566 JONES MANAGER 7839 02-APR-81 2984 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1259 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2859 30
7782 CLARK MANAGER 7839 09-JUN-81 2461.2 10
7788 SCOTT ANALYST 7566 19-APR-87 3009 20
7839 KING PRESIDENT 17-NOV-81 5011.2 10
7844 TURNER SALESMAN 7698 08-SEP-81 1509 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1109 20
7900 JAMES CLERK 7698 03-DEC-81 959 30
7902 FORD ANALYST 7566 03-DEC-81 3009 20
7934 MILLER CLERK 7782 23-JAN-82 1311.2 10
14 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/db254/users01.dbf'
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ---------------------------------------- ---------- ---------
4 OFFLINE OFFLINE WRONG FILE TYPE 0
SQL> ! cp /u01/oracle/users01.dbf /db254/users01.dbf
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ---------------------------------------- ---------- ---------
4 OFFLINE OFFLINE 868427 28-FEB-11
SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/db254/users01.dbf'
SQL> select * from v$recovery_log;
THREAD# SEQUENCE# TIME ARCHIVE_NAME
---------- ---------- --------- ---------------------------------------------
1 67 28-FEB-11 /home/oracle/arc1/67_1_744004808.arc
1 68 28-FEB-11 /home/oracle/arc1/68_1_744004808.arc
1 69 28-FEB-11 /home/oracle/arc1/69_1_744004808.arc
SQL> recover datafile 4;
ORA-00279: change 868427 generated at 02/28/2011 17:38:17 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/67_1_744004808.arc
ORA-00280: change 868427 for thread 1 is in sequence #67
Specify log: {
auto
ORA-00279: change 868522 generated at 02/28/2011 17:41:32 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/68_1_744004808.arc
ORA-00280: change 868522 for thread 1 is in sequence #68
ORA-00278: log file '/home/oracle/arc1/67_1_744004808.arc' no longer needed for this recovery
ORA-00279: change 868527 generated at 02/28/2011 17:41:33 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/69_1_744004808.arc
ORA-00280: change 868527 for thread 1 is in sequence #69
ORA-00278: log file '/home/oracle/arc1/68_1_744004808.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
SQL> alter tablespace users online;
Tablespace altered.
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 809 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1609 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1259 500 30
7566 JONES MANAGER 7839 02-APR-81 2984 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1259 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2859 30
7782 CLARK MANAGER 7839 09-JUN-81 2461.2 10
7788 SCOTT ANALYST 7566 19-APR-87 3009 20
7839 KING PRESIDENT 17-NOV-81 5011.2 10
7844 TURNER SALESMAN 7698 08-SEP-81 1509 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1109 20
7900 JAMES CLERK 7698 03-DEC-81 959 30
7902 FORD ANALYST 7566 03-DEC-81 3009 20
7934 MILLER CLERK 7782 23-JAN-82 1311.2 10
14 rows selected.
SQL>