1\ 生成现有控制文件脚本:
alter database backup controlfile to trace;
到$ORACLE_BASE\admin\SID\udump目录下找最新生成的dumpfile;
2\ 编辑dumpfile生成noresetlogs下的创建控制文件脚本;
3\ 备份原有控制文件;
4\ 开启数据库到nomount状态,运行步骤2中创建的脚本,生成控制文件.
5\ recover database;
6\ alter database open;
7\ re-create temp file;
8\ shutdown immediate;
9\ startup
10\ 检查应用.
过程:
[oracle@crm2-db crmii]$ mv control0*.ctl /oracle/old/.
[oracle@crm2-db oracle]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Feb 6 10:49:10 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup NOMOUNT;
ORACLE instance started.
Total System Global Area 8589934592 bytes
Fixed Size 2086448 bytes
Variable Size 1358957008 bytes
Database Buffers 7214202880 bytes
Redo Buffers 14688256 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "CRMII" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 5000
5 MAXINSTANCES 8
6 MAXLOGHISTORY 9176
7 LOGFILE
8 GROUP 1 '/oracle/oradata/crmii/REDO1.LOG' SIZE 500M,
9 GROUP 2 '/oracle/oradata/crmii/REDO2.LOG' SIZE 500M,
10 GROUP 3 '/oracle/oradata/crmii/REDO3.LOG' SIZE 500M,
11 GROUP 4 '/oracle/oradata/crmii/REDO4.LOG' SIZE 500M,
12 GROUP 5 '/oracle/oradata/crmii/REDO5.LOG' SIZE 500M,
13 GROUP 6 '/oracle/oradata/crmii/REDO6.LOG' SIZE 500M
14 -- STANDBY LOGFILE
15 DATAFILE
16 '/oracle/oradata/crmii/system01.dbf',
17 '/oracle/oradata/crmii/undotbs01.dbf',
18 '/oracle/oradata/crmii/sysaux01.dbf',
19 '/oracle/oradata/crmii/users01.dbf',
20 '/oracle/oradata/crmii/example01.dbf',
21 '/oracle/oradata/crmii/CRMII.ORA',
22 '/oracle/oradata/crmii/CRMII_DATA_06.ORA',
23 '/oracle/oradata/crmii/CRMII_DATA_07.ORA',
24 '/oracle/oradata/crmii/CRMII_DATA_08.ORA',
25 '/oracle/oradata/crmii/CRMII_DATA_09.ORA',
26 '/oracle/oradata/crmii/CRMII_DATA_10.ORA',
27 '/oracle/oradata/crmii/CRMII_DATA.ORA',
28 '/oracle/oradata/crmii/DC_DEF.ORA',
29 '/oracle/oradata/crmii/DC_PRE_TAB.ORA',
30 '/oracle/oradata/crmii/DCLSBT_0601.ORA',
31 '/oracle/oradata/crmii/DCLSBT_0602.ORA',
32 '/oracle/oradata/crmii/DCLSBT_0603.ORA',
33 '/oracle/oradata/crmii/DCLSBT_0604.ORA',
34 '/oracle/oradata/crmii/DCLSBT_0605.ORA',
35 '/oracle/oradata/crmii/DCLSBT_0606.ORA',
36 '/oracle/oradata/crmii/DCLSBT_0607.ORA',
37 '/oracle/oradata/crmii/DCLSBT_0608.ORA',
38 '/oracle/oradata/crmii/DCLSBT_0609.ORA',
39 '/oracle/oradata/crmii/DCLSBT_0610.ORA',
40 '/oracle/oradata/crmii/DCLSBT_0611.ORA',
41 '/oracle/oradata/crmii/DCLSBT_0612.ORA',
42 '/oracle/oradata/crmii/DCLSBT_0701.ORA',
43 '/oracle/oradata/crmii/DCLSBT_0702.ORA',
44 '/oracle/oradata/crmii/DCLSBT_0703.ORA',
45 '/oracle/oradata/crmii/DCLSBT_0704.ORA',
46 '/oracle/oradata/crmii/DCLSBT_0705.ORA',
47 '/oracle/oradata/crmii/DCLSBT_0706.ORA',
48 '/oracle/oradata/crmii/DCLSBT_0707.ORA',
49 '/oracle/oradata/crmii/DCLSBT_0708.ORA',
50 '/oracle/oradata/crmii/DCLSBT_0709.ORA',
51 '/oracle/oradata/crmii/DCLSBT_0710.ORA',
52 '/oracle/oradata/crmii/DCLSBT_0711.ORA',
53 '/oracle/oradata/crmii/DCLSBT_0712.ORA',
54 '/oracle/oradata/crmii/DCLSBT_0801.ORA',
55 '/oracle/oradata/crmii/DCLSBT_0802.ORA',
56 '/oracle/oradata/crmii/DCLSBT_0803.ORA',
57 '/oracle/oradata/crmii/DCLSBT_0804.ORA',
58 '/oracle/oradata/crmii/DCLSBT_0805.ORA',
59 '/oracle/oradata/crmii/DCLSBT_0806.ORA',
60 '/oracle/oradata/crmii/DCLSBT_0807.ORA',
61 '/oracle/oradata/crmii/DCLSBT_0808.ORA',
62 '/oracle/oradata/crmii/DCLSBT_0809.ORA',
63 '/oracle/oradata/crmii/DCLSBT_0810.ORA',
64 '/oracle/oradata/crmii/DCLSBT_0811.ORA',
65 '/oracle/oradata/crmii/DCLSBT_0812.ORA',
66 '/oracle/oradata/crmii/DCLSBT_0901.ORA',
67 '/oracle/oradata/crmii/DCLSBT_0902.ORA',
68 '/oracle/oradata/crmii/DCLSBT_0903.ORA',
69 '/oracle/oradata/crmii/DCLSBT_0904.ORA',
70 '/ora_data/DCLSBT_0905.ORA',
71 '/ora_data/DCLSBT_0906.ORA',
72 '/ora_data/DCLSBT_0907.ORA',
73 '/oracle/oradata/crmii/DCLSBT_0908.ORA',
74 '/oracle/oradata/crmii/DCLSBT_0909.ORA',
75 '/oracle/oradata/crmii/DCLSBT_0910.ORA',
76 '/oracle/oradata/crmii/DCLSBT_0911.ORA',
77 '/oracle/oradata/crmii/DCLSBT_0912.ORA',
78 '/oracle/oradata/crmii/DCLSBT_1001.ORA',
79 '/oracle/oradata/crmii/DCLSBT_1002.ORA',
80 '/oracle/oradata/crmii/DCLSBT_1003.ORA',
81 '/oracle/oradata/crmii/DCLSBT_1004.ORA',
82 '/oracle/oradata/crmii/DCLSBT_1005.ORA',
83 '/oracle/oradata/crmii/DCLSBT_1006.ORA',
84 '/oracle/oradata/crmii/DCLSBT_1007.ORA',
85 '/oracle/oradata/crmii/DCLSBT_1008.ORA',
86 '/oracle/oradata/crmii/DCLSBT_1009.ORA',
87 '/oracle/oradata/crmii/DCLSBT_1010.ORA',
88 '/oracle/oradata/crmii/DCLSBT_1011.ORA',
89 '/oracle/oradata/crmii/DCLSBT_1012.ORA',
90 '/oracle/oradata/crmii/DCLSBT_DEF.ORA',
91 '/oracle/oradata/crmii/TAB_0601.ORA',
92 '/oracle/oradata/crmii/TAB_0607.ORA',
93 '/oracle/oradata/crmii/TAB_0701.ORA',
94 '/oracle/oradata/crmii/TAB_0707.ORA',
95 '/oracle/oradata/crmii/TAB_0801.ORA',
96 '/oracle/oradata/crmii/TAB_0807.ORA',
97 '/oracle/oradata/crmii/TAB_0901.ORA',
98 '/oracle/oradata/crmii/TAB_0907.ORA',
99 '/oracle/oradata/crmii/TAB_1001.ORA',
100 '/oracle/oradata/crmii/TAB_1007.ORA',
101 '/oracle/oradata/crmii/TAB_1101.ORA',
102 '/oracle/oradata/crmii/TAB_1107.ORA',
103 '/oracle/oradata/crmii/IND_0601.ORA',
104 '/oracle/oradata/crmii/IND_0607.ORA',
105 '/oracle/oradata/crmii/IND_0701.ORA',
106 '/oracle/oradata/crmii/IND_0707.ORA',
107 '/oracle/oradata/crmii/IND_0801.ORA',
108 '/oracle/oradata/crmii/IND_0807.ORA',
109 '/oracle/oradata/crmii/IND_0901.ORA',
110 '/oracle/oradata/crmii/IND_0907.ORA',
111 '/oracle/oradata/crmii/IND_1001.ORA',
112 '/oracle/oradata/crmii/IND_1007.ORA',
113 '/oracle/oradata/crmii/IND_1101.ORA',
114 '/oracle/oradata/crmii/IND_1107.ORA',
115 '/oracle/oradata/crmii/DCLSBT_1101.ORA',
116 '/oracle/oradata/crmii/DCLSBT_1102.ORA',
117 '/oracle/oradata/crmii/DCLSBT_1103.ORA',
118 '/oracle/oradata/crmii/DCLSBT_1104.ORA',
119 '/oracle/oradata/crmii/DCLSBT_1105.ORA',
120 '/oracle/oradata/crmii/DCLSBT_1106.ORA',
121 '/oracle/oradata/crmii/DCLSBT_1107.ORA',
122 '/oracle/oradata/crmii/DCLSBT_1108.ORA',
123 '/oracle/oradata/crmii/DCLSBT_1109.ORA',
124 '/oracle/oradata/crmii/DCLSBT_1110.ORA',
125 '/oracle/oradata/crmii/DCLSBT_1111.ORA',
126 '/oracle/oradata/crmii/DCLSBT_1112.ORA',
127 '/oracle/oradata/crmii/DCLSBT_1201.ORA',
128 '/oracle/oradata/crmii/DCLSBT_1202.ORA',
129 '/oracle/oradata/crmii/DCLSBT_1203.ORA',
130 '/oracle/oradata/crmii/DCLSBT_1204.ORA',
131 '/oracle/oradata/crmii/DCLSBT_1205.ORA',
132 '/oracle/oradata/crmii/DCLSBT_1206.ORA',
133 '/oracle/oradata/crmii/DCLSBT_1207.ORA',
134 '/oracle/oradata/crmii/DCLSBT_1208.ORA',
135 '/oracle/oradata/crmii/DCLSBT_1209.ORA',
136 '/oracle/oradata/crmii/DCLSBT_1210.ORA',
137 '/oracle/oradata/crmii/DCLSBT_1211.ORA',
138 '/oracle/oradata/crmii/DCLSBT_1212.ORA',
139 '/oracle/oradata/crmii/DCLSBT_1301.ORA',
140 '/oracle/oradata/crmii/DCLSBT_1302.ORA',
141 '/oracle/oradata/crmii/DCLSBT_1303.ORA',
142 '/oracle/oradata/crmii/DCLSBT_1304.ORA',
143 '/oracle/oradata/crmii/DCLSBT_1305.ORA',
144 '/oracle/oradata/crmii/DCLSBT_1306.ORA',
145 '/oracle/oradata/crmii/DCLSBT_1307.ORA',
146 '/oracle/oradata/crmii/DCLSBT_1308.ORA',
147 '/oracle/oradata/crmii/DCLSBT_1309.ORA',
148 '/oracle/oradata/crmii/DCLSBT_1310.ORA',
149 '/oracle/oradata/crmii/DCLSBT_1311.ORA',
150 '/oracle/oradata/crmii/DCLSBT_1312.ORA',
151 '/oracle/oradata/crmii/DC_DEF_01.ORA',
152 '/oracle/oradata/crmii/DC_DEF_02.ORA',
153 '/ora_data/DCLSBT_0906_01.ORA',
154 '/ora_data/DCLSBT_0907_01.ORA',
155 '/oracle/oradata/crmii/DCLSBT_0908_01.ORA',
156 '/oracle/oradata/crmii/DC_DEF_03.ORA',
157 '/oracle/oradata/crmii/DCLSBT_0909_01.ORA',
158 '/ora_data/CRMII_01.ORA',
159 '/ora_data/DCLSBT_0911_01.ORA',
160 '/ora_data/DCLSBT_0912_1.ORA'
161 CHARACTER SET ZHS16GBK
162 ;
Control file created.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
CRMII MOUNTED
SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
System altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/crmii/temp01.dbf'
2 SIZE 212860928 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
SQL> ALTER TABLESPACE CRMII_TMP ADD TEMPFILE '/oracle/oradata/crmii/CRMII_TMP.ORA'
2 SIZE 104857600 REUSE AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M;
ALTER TABLESPACE DC_TMP ADD TEMPFILE '/oracle/oradata/crmii/DC_TMP.ORA'
SIZE 524288000 REUSE AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M;
Tablespace altered.
SQL> 2
Tablespace altered.
SQL>
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 8589934592 bytes
Fixed Size 2086448 bytes
Variable Size 1358957008 bytes
Database Buffers 7214202880 bytes
Redo Buffers 14688256 bytes
Database mounted.
Database opened.
