1、错误场景，做完全恢复（归档模式），system 表空间出错
[oracle@cuugbgy ~]$!sql
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Mar 16 11:01:25 2012
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select file# ,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 996125
2 996125
3 996125
4 996125
5 996125
6 996125
6 rows selected.
SQL> select file# ,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 994105
2 996125
3 996125
4 996125
5 996125
6 996125
6 rows selected.
SQL> recover datafile 1;
ORA-00279: change 994105 generated at 03/16/2012 08:51:01 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_19_776098171.log
ORA-00280: change 994105 for thread 1 is in sequence #19
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 994744 generated at 03/16/2012 09:10:00 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_20_776098171.log
ORA-00280: change 994744 for thread 1 is in sequence #20
ORA-00278: log file '/disk1/arch/prod/arch_1_19_776098171.log' no longer needed for this recovery
ORA-00279: change 996065 generated at 03/16/2012 09:41:17 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_21_776098171.log
ORA-00280: change 996065 for thread 1 is in sequence #21
ORA-00278: log file '/disk1/arch/prod/arch_1_20_776098171.log' no longer needed for this recovery
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [1], [498], [1], [23], [378], [16], []
ORA-10567: Redo is inconsistent with data block (file# 1, block# 498)
ORA-10564: tablespace SYSTEM
ORA-01110: data file 1: '/u01/app/oracle/oradata/prod/system01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 68
ORA-01112: media recovery not started
2、recover 失败，检查controlfile 和 datafile 信息
SQL> select file# ,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 996125
2 996125
3 996125
4 996125
5 996125
6 996125
6 rows selected.
SQL> select file# ,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 996125
2 996125
3 996125
4 996125
5 996125
6 996125
6 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- -------------------
2 1 23 52428800 2 NO CURRENT 996125 2012-03-16 09:43:33
1 1 22 52428800 2 YES INACTIVE 996105 2012-03-16 09:42:39
----打开数据库失败
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/prod/system01.dbf'
------查看磁盘空间，不是空间问题，呵呵
SQL> !
[oracle@cuugbgy ~]$cp /disk1/backup/prod/cold_bak/*.dbf /u01/app/oracle/oradata/prod/
[oracle@cuugbgy ~]$df -h
Filesystem size used avail capacity Mounted on
/dev/dsk/c0t0d0s0 19G 5.2G 14G 28% /
/devices 0K 0K 0K 0% /devices
ctfs 0K 0K 0K 0% /system/contract
proc 0K 0K 0K 0% /proc
mnttab 0K 0K 0K 0% /etc/mnttab
swap 1.8G 988K 1.8G 1% /etc/svc/volatile
objfs 0K 0K 0K 0% /system/object
sharefs 0K 0K 0K 0% /etc/dfs/sharetab
/usr/lib/libc/libc_hwcap1.so.1
19G 5.2G 14G 28% /lib/libc.so.1
fd 0K 0K 0K 0% /dev/fd
swap 1.8G 84K 1.8G 1% /tmp
swap 1.8G 28K 1.8G 1% /var/run
/dev/dsk/c0t2d0s3 8.9G 9.1M 8.8G 1% /disk3
/dev/dsk/c0t2d0s1 4.9G 122M 4.8G 3% /disk2
/dev/dsk/c0t2d0s0 4.9G 2.0G 2.9G 42% /disk1
/dev/dsk/c0t0d0s3 15G 4.7G 9.7G 33% /u01
/dev/dsk/c0t0d0s7 14G 716M 14G 5% /export/home
3、重新进行recover（restore 数据文件）
[oracle@cuugbgy ~]$!sql
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Mar 16 11:04:35 2012
copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select file# ,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 994105
2 994105
3 994105
4 994105
5 994105
6 994105
6 rows selected.
SQL> select file# ,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 996125
2 996125
3 996125
4 996125
5 996125
6 996125
6 rows selected.
-------做不完全恢复
SQL> recover database until cancel;
ORA-00279: change 994105 generated at 03/16/2012 08:51:01 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_19_776098171.log
ORA-00280: change 994105 for thread 1 is in sequence #19
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 994744 generated at 03/16/2012 09:10:00 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_20_776098171.log
ORA-00280: change 994744 for thread 1 is in sequence #20
ORA-00278: log file '/disk1/arch/prod/arch_1_19_776098171.log' no longer needed for this recovery
ORA-00279: change 996065 generated at 03/16/2012 09:41:17 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_21_776098171.log
ORA-00280: change 996065 for thread 1 is in sequence #21
ORA-00278: log file '/disk1/arch/prod/arch_1_20_776098171.log' no longer needed for this recovery
ORA-00279: change 996105 generated at 03/16/2012 09:42:39 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_22_776098171.log
ORA-00280: change 996105 for thread 1 is in sequence #22
ORA-00278: log file '/disk1/arch/prod/arch_1_21_776098171.log' no longer needed for this recovery
ORA-00279: change 996125 generated at 03/16/2012 09:43:33 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_23_776098171.log
ORA-00280: change 996125 for thread 1 is in sequence #23
ORA-00278: log file '/disk1/arch/prod/arch_1_22_776098171.log' no longer needed for this recovery
ORA-00308: cannot open archived log '/disk1/arch/prod/arch_1_23_776098171.log'
ORA-27037: unable to obtain file status
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3
------利用当前日志组进行恢复
[oracle@cuugbgy prod]$!sql
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Mar 16 11:06:29 2012
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- -------------------
2 1 23 52428800 2 NO CURRENT 996125 2012-03-16 09:43:33
1 1 22 52428800 2 YES INACTIVE 996105 2012-03-16 09:42:39
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/redo02.log
/u01/app/oracle/oradata/prod/redo01.log
/u01/app/oracle/oradata/prod/redo01a.log
/u01/app/oracle/oradata/prod/redo02a.log
SQL> recover database until cancel;
ORA-00279: change 996125 generated at 03/16/2012 09:43:33 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_23_776098171.log
ORA-00280: change 996125 for thread 1 is in sequence #23
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/prod/redo02.log 贴出当前日志的绝对路径
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [1], [498], [4194802], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 1, block# 498)
ORA-10564: tablespace SYSTEM
ORA-01110: data file 1: '/u01/app/oracle/oradata/prod/system01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 68
ORA-01112: media recovery not started
------恢复报错，试着用resetlogs 打开库
SQL> alter database open resetlogs;
Database altered.
-------数据库被打开
SQL> select status from v$instance;
STATUS
------------------------------------
OPEN
SQL> select * from scott.emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
8 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- -------------------
1 1 0 52428800 2 YES UNUSED 0
2 1 1 52428800 2 NO CURRENT 996126 2012-03-16 11:08:36
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- -------------------
1 1 2 52428800 2 YES INACTIVE 996426 2012-03-16 11:12:40
2 1 3 52428800 2 NO CURRENT 996430 2012-03-16 11:12:47
理Oracle 手工恢复中的‘ORA-00600: internal error code, arguments: 错误
-
宋祖强
2015-07-29 10:24:07 -
Oracle
- 原创