系统环境：
操作系统： Linux RH6
数据库： Oracle 11gR2
案例分析：
数据库中所有的控制文件被意外破坏，非归档的库，在有trace备份的情况下，重建控制文件。
1、控制文件trace脚本
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
[oracle@rh6 ~]$ cat crctr.sql
CREATE CONTROLFILE REUSE DATABASE
"TEST3"
NORESETLOGS NOARCHIVELOG
MAXLOGFILES 10
MAXLOGMEMBERS 5
MAXDATAFILES 300
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1
'/u01/app/oracle/oradata/test3/redo01a.log'
SIZE 100M BLOCKSIZE 512,
GROUP 2
'/u01/app/oracle/oradata/test3/redo02a.log'
SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/test3/system01.dbf'
,
'/u01/app/oracle/oradata/test3/sysaux01.dbf'
,
'/u01/app/oracle/oradata/test3/undotbs01.dbf'
,
'/u01/app/oracle/oradata/test3/users01.dbf'
CHARACTER SET ZHS16GBK；
|
2、启动Instance到nomount，重建controlfile
|
1
2
3
4
5
6
7
8
9
10
|
10:59:05 SYS@ test3 >startup nomount;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 213912664 bytes
Database Buffers 92274688 bytes
Redo Buffers 6336512 bytes
10:59:41 SYS@ test3 >@/home/oracle/crctr.sql
Control file created.
|
3、告警日志
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
......
CREATE CONTROLFILE REUSE DATABASE
"TEST3"
NORESETLOGS NOARCHIVELOG
MAXLOGFILES 10
MAXLOGMEMBERS 5
MAXDATAFILES 300
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1
'/u01/app/oracle/oradata/test3/redo01a.log'
SIZE 100M BLOCKSIZE 512,
GROUP 2
'/u01/app/oracle/oradata/test3/redo02a.log'
SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/test3/system01.dbf'
,
'/u01/app/oracle/oradata/test3/sysaux01.dbf'
,
'/u01/app/oracle/oradata/test3/undotbs01.dbf'
,
'/u01/app/oracle/oradata/test3/users01.dbf'
CHARACTER SET ZHS16GBK
WARNING: Default Temporary Tablespace not specified
in
CREATE DATABASE command
Default Temporary Tablespace will be necessary
for
a locally managed database
in
future release
Wed Jan 07 11:00:02 2015
Successful mount of redo thread 1, with mount id 991126251
Completed: CREATE CONTROLFILE REUSE DATABASE
"TEST3"
NORESETLOGS NOARCHIVELOG
MAXLOGFILES 10
MAXLOGMEMBERS 5
MAXDATAFILES 300
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1
'/u01/app/oracle/oradata/test3/redo01a.log'
SIZE 100M BLOCKSIZE 512,
GROUP 2
'/u01/app/oracle/oradata/test3/redo02a.log'
SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/test3/system01.dbf'
,
'/u01/app/oracle/oradata/test3/sysaux01.dbf'
,
'/u01/app/oracle/oradata/test3/undotbs01.dbf'
,
'/u01/app/oracle/oradata/test3/users01.dbf'
CHARACTER SET ZHS16GBK
Wed Jan 07 11:00:59 2015
......
|
3、查看数据库状态
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
11:00:03 SYS@ test3 >select status from v$instance;
STATUS
------------
MOUNTED
11:00:27 SYS@ test3 >select file#,name ,checkpoint_change# from v$datafile;
FILE
# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /u01/app/oracle/oradata/test3/system01.dbf 333365
2 /u01/app/oracle/oradata/test3/sysaux01.dbf 333365
3 /u01/app/oracle/oradata/test3/undotbs01.dbf 333365
4 /u01/app/oracle/oradata/test3/users01.dbf 333365
11:00:46 SYS@ test3 >select file#,name ,checkpoint_change# from v$datafile_header;
FILE
# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /u01/app/oracle/oradata/test3/system01.dbf 333365
2 /u01/app/oracle/oradata/test3/sysaux01.dbf 333365
3 /u01/app/oracle/oradata/test3/undotbs01.dbf 333365
4 /u01/app/oracle/oradata/test3/users01.dbf 333365
|
4、打开数据库
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
11:00:54 SYS@ test3 >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/test3/system01.dbf'
---打开数据库报错，需要做“media recovery”
执行介质恢复：
由于本库为非归档模式，只能通过current redolog来恢复
查看当前日志组：
[oracle@rh6 ~]$ sqlplus
'/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 7 11:02:12 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
11:02:12 SYS@ test3 >select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/test3/redo01a.
log
/u01/app/oracle/oradata/test3/redo02a.
log
11:02:22 SYS@ test3 >select group#,sequence#,status from v$
log
;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
2 12 INACTIVE
1 13 CURRENT
11:00:59 SYS@ test3 >recover database until cancel;
ORA-00279: change 333365 generated at 01/07/2015 10:30:26 needed
for
thread
1
ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_13_868275293.dbf
ORA-00280: change 333365
for
thread
1 is in sequence #13
11:01:42 Specify
log
: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/test3/redo01a.
log
Log applied.
Media recovery complete.
---恢复完成！
11:02:46 SYS@ test3 >alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option
for
database open
Elapsed: 00:00:00.01
11:02:52 SYS@ test3 >alter database open resetlogs;
Database altered.
---Database open成功！
|
查看告警日志：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
|
alter database open
Errors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_ora_3294.trc:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'/u01/app/oracle/oradata/test3/system01.dbf'
ORA-1113 signalled during: alter database open...
Wed Jan 07 11:01:40 2015
ALTER DATABASE RECOVER database until cancel
Media Recovery Start
Serial Media Recovery started
ORA-279 signalled during: ALTER DATABASE RECOVER database until cancel ...
Wed Jan 07 11:02:44 2015
ALTER DATABASE RECOVER LOGFILE
'/u01/app/oracle/oradata/test3/redo01a.log'
Media Recovery Log /u01/app/oracle/oradata/test3/redo01a.
log
Incomplete recovery applied all redo ever generated.
Recovery completed through change 334001
time
01/07/2015 10:51:13
Media Recovery Complete (test3)
Completed: ALTER DATABASE RECOVER LOGFILE
'/u01/app/oracle/oradata/test3/redo01a.log'
alter database open
Errors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_ora_3294.trc:
ORA-01589: must use RESETLOGS or NORESETLOGS option
for
database open
ORA-1589 signalled during: alter database open...
Wed Jan 07 11:03:04 2015
alter database open resetlogs
RESETLOGS after complete recovery through change 334001
Resetting resetlogs activation ID 990996637 (0x3b11689d)
Errors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_ora_3294.trc:
ORA-00367: checksum error in
log
file header
ORA-00322:
log
1 of
thread
1 is not current copy
ORA-00312: online
log
1
thread
1:
'/u01/app/oracle/oradata/test3/redo01a.log'
Wed Jan 07 11:03:05 2015
Errors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_m000_3336.trc:
ORA-00316:
log
1 of
thread
1, type 0 in header is not
log
file
ORA-00312: online
log
1
thread
1:
'/u01/app/oracle/oradata/test3/redo01a.log'
Errors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_ora_3294.trc:
ORA-00367: checksum error in
log
file header
ORA-00322:
log
2 of
thread
1 is not current copy
ORA-00312: online
log
2
thread
1:
'/u01/app/oracle/oradata/test3/redo02a.log'
Errors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_m000_3336.trc:
ORA-00316:
log
2 of
thread
1, type 0 in header is not
log
file
ORA-00312: online
log
2
thread
1:
'/u01/app/oracle/oradata/test3/redo02a.log'
Wed Jan 07 11:03:18 2015
Setting recovery target incarnation to 2
Wed Jan 07 11:03:20 2015
Checker run found 4
new
persistent data failures
Wed Jan 07 11:03:21 2015
Assigning activation ID 991126251 (0x3b1362eb)
Thread 1 opened at
log
sequence 1
Current
log
# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/test3/redo01a.
log
Successful open of redo
thread
1
Wed Jan 07 11:03:22 2015
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Jan 07 11:03:23 2015
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Tablespace
'TEMPTS1'
#3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Verifying file header compatibility
for
11g tablespace encryption..
Verifying 11g file header compatibility
for
tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done
using
the SQL statement:
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
Alternatively,
if
these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMPTS1
*********************************************************************
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Jan 07 11:03:27 2015
QMNC started with pid=19, OS id=3341
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open resetlogs
Wed Jan 07 11:13:27 2015
Starting background process SMCO
Wed Jan 07 11:13:27 2015
SMCO started with pid=22, OS id=3382
|
---至此，通过trace脚本，重建控制文件成功！
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1600356，如需转载请自行联系原作者
