Newer
Older
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
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
<head>
<title>Oracle Recovery Procedure</title>
</head>
<center><font face="TIMES"><font size=+4>Recovering Oracle</font></font></center>
<p><font face="TIMES"><font size=-1>Since an Oracle database consists of
several interrelated parts, recovering such a database is done through
a process of elimination. Identify which pieces work, then recover the
pieces that don't work. The following recovery guidefollows that logic
and works regardless of the chosen backup method. It consists of a flowchart
and a procedure whose numbered steps correspond to the elements in the
flowchart.</font></font>
<p><map NAME="flowchrt">
<area HREF="#Step12" SHAPE="RECT" COORDS="561,517,689,592">
<area HREF="#Step14" SHAPE="RECT" COORDS="565,605,689,656">
<area HREF="#Step18" SHAPE="POLY" COORDS="612,970,614,969,689,928,761,973,688,1014,612,970">
<area HREF="#Step19" SHAPE="POLY" COORDS="563,874,564,874,640,833,720,879,640,921,563,874">
<area HREF="#Step21" SHAPE="RECT" COORDS="644,740,755,813">
<area HREF="#Step20" SHAPE="RECT" COORDS="495,699,632,776">
<area HREF="#Step17" SHAPE="POLY" COORDS="407,844,408,844,483,804,558,847,484,889,407,844">
<area HREF="#Step22" SHAPE="POLY" COORDS="427,935,371,971,426,1002,485,969,427,935">
<area HREF="#Step23" SHAPE="RECT" COORDS="224,942,355,1002">
<area HREF="#Step25" SHAPE="POLY" COORDS="313,871,314,871,377,906,432,868,374,841,313,871">
<area HREF="#Step26" SHAPE="RECT" COORDS="170,847,289,894">
<area HREF="#Step24" SHAPE="RECT" COORDS="89,916,200,958">
<area HREF="#Step28" SHAPE="RECT" COORDS="177,776,289,831">
<area HREF="#Step10" SHAPE="RECT" COORDS="34,779,147,804">
<area HREF="#Step31" SHAPE="RECT" COORDS="57,727,193,769">
<area HREF="#Step27" SHAPE="POLY" COORDS="301,732,377,691,450,737,377,778,301,732">
<area HREF="#Step13" SHAPE="POLY" COORDS="389,660,466,618,540,662,465,705,389,660">
<area HREF="#Step29" SHAPE="POLY" COORDS="28,653,106,609,182,655,106,695,28,653">
<area HREF="#Step30" SHAPE="RECT" COORDS="198,590,365,647">
<area HREF="#Step11" SHAPE="POLY" COORDS="391,550,392,550,467,507,542,553,466,594,391,550">
<area HREF="#Step16" SHAPE="RECT" COORDS="210,518,365,575">
<area HREF="#Step15" SHAPE="POLY" COORDS="29,522,106,478,180,523,105,567,29,522">
<area HREF="#Step10" SHAPE="POLY" COORDS="129,450,130,450,207,409,279,455,204,496,129,450">
<area HREF="#Step8" SHAPE="RECT" COORDS="117,311,338,365">
<area HREF="#Step9" SHAPE="RECT" COORDS="358,308,633,368">
<area HREF="#Step31" SHAPE="RECT" COORDS="607,257,671,303">
<area HREF="#Step1" SHAPE="RECT" COORDS="662,152,773,178">
<area HREF="#Step4" SHAPE="POLY" COORDS="652,99,654,99,728,57,801,102,724,144,652,99">
<area HREF="#Step7" SHAPE="RECT" COORDS="545,163,656,208">
<area HREF="#Step6" SHAPE="POLY" COORDS="345,224,423,184,494,228,420,271,345,224">
<area HREF="#Step3" SHAPE="RECT" COORDS="367,54,502,152">
<area HREF="#Step4" SHAPE="POLY" COORDS="206,225,271,262,332,226,268,193,206,225">
<area HREF="#Step5" SHAPE="RECT" COORDS="141,140,245,197">
<area HREF="#Step2" SHAPE="POLY" COORDS="198,100,199,100,274,57,349,102,272,146,198,100">
<area HREF="#Step1" SHAPE="POLY" COORDS="27,100,107,55,175,102,103,143,27,100">
</map>
<img SRC="oracle.gif" NAME="flowchrt" USEMAP="#flowchrt" height=1061 width=827>
<p><a NAME="Step1"></a><b><font face="TIMES"><font size=+1>Step 1: Try
Startup Mount</font></font></b>
<p><font face="TIMES"><font size=-1>The first step in verifying the condition
of an Oracle database is to attempt to mount it. This works because mounting
a database (without opening it) reads the control files but does not open
the data files. If the control files are mirrored, Oracle attempts to open
each of the control files that are listed in the <i>initORACLE_SID.ora</i>
file. If any of them is damaged, the mount fails.</font></font>
<p><font face="TIMES"><font size=-1>To mount a database, simply run <i>svrmgrl</i>,
connect to the database, and enter <i>startup mount</i>.</font></font>
<dir><font face="Courier"><font size=-2>$ <b>svrmgrl</b></font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>connect internal;</b></font></font>
<p><font face="Courier"><font size=-2>Connected.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>startup mount;</b></font></font>
<p><font face="Courier"><font size=-2>Statement processed.</font></font></dir>
<font face="TIMES"><font size=-1>If it succeeds, the output looks something
like this:</font></font>
<dir><font face="Courier"><font size=-2>SVRMGR > <b>startup mount;</b></font></font>
<p><font face="Courier"><font size=-2>ORACLE instance started.</font></font>
<p><font face="Courier"><font size=-2>Total System Global Area 5130648
bytes</font></font>
<p><font face="Courier"><font size=-2>Fixed Size 44924 bytes</font></font>
<p><font face="Courier"><font size=-2>Variable Size 4151836 bytes</font></font>
<p><font face="Courier"><font size=-2>Database Buffers 409600 bytes</font></font>
<p><font face="Courier"><font size=-2>Redo Buffers 524288 bytes</font></font>
<p><font face="Courier"><font size=-2>Database mounted.</font></font>
<dir><font face="TIMES"><font size=-1>If the attempt to mount the database
succeeds, proceed to Step 10.</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>If the attempt to mount the database fails,
the output looks something like this:</font></font>
<dir><font face="Courier"><font size=-2>SVRMGR > <b>startup mount;</b></font></font>
<p><font face="Courier"><font size=-2>Total System Global Area 5130648
bytes</font></font>
<p><font face="Courier"><font size=-2>Fixed Size 44924 bytes</font></font>
<p><font face="Courier"><font size=-2>Variable Size 4151836 bytes</font></font>
<p><font face="Courier"><font size=-2>Database Buffer to s 409600 bytes</font></font>
<p><font face="Courier"><font size=-2>Redo Buffers 524288 bytes</font></font>
<p><font face="Courier"><font size=-2>ORACLE instance started.</font></font>
<p><font face="Courier"><font size=-2>ORA-00205: error in identifying controlfile,
check alert log for more info</font></font>
<dir><font face="TIMES"><font size=-1>If the attempt to mount the database
fails, proceed to Step 2.</font></font></dir>
</dir>
<a NAME="Step2"></a><b><font face="TIMES"><font size=+1>Step 2: Are All
Control Files Missing?</font></font></b>
<p><font face="TIMES"><font size=-1>Don't panic if the attempt to mount
the database fails. Control files are easily restored if they were mirrored,
and can even be rebuilt from scratch if necessary. The first important
piece of information is that one or more control files are missing.</font></font>
<p><font face="TIMES"><font size=-1>Unfortunately, since Oracle aborts
the mount at the first failure it encounters, it could be missing one,
two, or all of the control files, but so far you know only about the first
missing file. So, before embarking on a course of action, determine the
severity of the problem. In order to do that, do a little research.</font></font>
<p><font size=-1><font face="TIMES">First, determine the names of all of
the control files. Do that by looking at the <i>configORACLE_SID.ora </i>file
next to the word </font><font face="Courier">control files</font><font face="TIMES">.
It looks something like this:</font></font>
<dir><font face="Courier"><font size=-2>control_files = (/db/Oracle/a/oradata/crash/control01.ctl,</font></font>
<p><font face="Courier"><font size=-2>/db/Oracle/b/oradata/crash/control02.ctl,</font></font>
<p><font face="Courier"><font size=-2>/db/Oracle/c/oradata/crash/control03.ctl)</font></font></dir>
<font size=-1><font face="TIMES">It's also important to get the name of
the control file that Oracle is complaining about. Find this by looking
for the phrase </font><font face="Courier">control file:</font><font face="TIMES">
in the alert log. (The alert log can be found in the location specified
by the </font><font face="Courier">background_dump_dest</font><font face="TIMES">
value in the <i>configinstance.ora</i> file. (Typically, it is in the <i>ORACLE_BASE/ORACLE_SID/admin/bdump</i>
directory.) In that directory, there should be a file called <i>alert_ORACLE_SID.log</i>.
In that file, there should be an error that looks something like this:</font></font>
<dir><font face="Courier"><font size=-2>Sat Feb 21 13:46:19 1998</font></font>
<p><font face="Courier"><font size=-2>alter database mount exclusive</font></font>
<p><font face="Courier"><font size=-2>Sat Feb 21 13:46:20 1998</font></font>
<p><font face="Courier"><font size=-2>ORA-00202: controlfile: '/db/a/oradata/crash/control01.ctl'</font></font>
<p><font face="Courier"><font size=-2>ORA-27037: unable to obtain file
status</font></font>
<p><font face="Courier"><font size=-2>SVR4 Error: 2: No such file or directory</font></font>
<dir><font face="TIMES"><font size=-1>Warning! Some of the following procedures
may say to override a potentially corrupted control file. Since one never
knows which file may be needed, always make backup copies of all of the
control files before doing any of this. That offers an "undo" option that
isn't possible otherwise. (Also make copies of the online redo logs as
well.)</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>With the names of all of the control files
and the name of the damaged file, it's easy to determine the severity of
the problem. Do this by listing each of the control files and comparing
their size and modification time. (Remember the game "Which one of these
is not like the other," on Sesame Street?) The following scenarios assume
that the control files were mirrored to three locations, which is a very
common practice. The possible scenarios are:</font></font>
<p><font face="TIMES"><font size=-1>The damaged file is missing, and at
least one other file is present</font></font>
<dir><font face="TIMES"><font size=-1>If the file that Oracle is complaining
about is just missing, that's an easy thing to fix.</font></font>
<dir><font face="TIMES"><font size=-1>If this is the case, proceed to Step
3.</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>The damaged file is not missing. It is
corrupted</font></font>
<dir>
<dir><font face="TIMES"><font size=-1>This is probably the most confusing
one, since it's hard to tell if a file is corrupted. What to do in this
situation is a personal choice. Before going any farther, make backup copies
of all control files. Once you do that, try a "shell game" with the different
control files. The shell game consists of taking one of the three control
files and copying it to the other two files' locations. Then attempt to
mount the database again. The "shell game" is covered in Step 3.</font></font></dir>
<font face="TIMES"><font size=-1>However, if all the online redo logs are
present, it's probably easier at this point to just run the "create controlfile"
script discussed in Steps 6 and 7. This rebuilds the control file to all
locations automatically. (Before that, though, follow Steps 4 and 5 to
verify if all the data files and log files are present.)</font></font>
<dir><font face="TIMES"><font size=-1>To rebuild the control file using
the "create controlfile" script, proceed to Steps 4 through 7.</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>All of the control files are missing,
or they are all different sizes and/or times.</font></font>
<dir><font face="TIMES"><font size=-1>If all of the control files are corrupt
or missing, they must be rebuilt or the entire database must be restored.
Hopefully your backup system has been running the <i>backup control file
to trace</i> command on a regular basis. (The output of this command is
a SQL script that will rebuild the control files automatically.)</font></font>
<dir><font face="TIMES"><font size=-1>If the <i>backup control file to
trace</i> command has been running, proceed to Steps 4 through 7. If not,
then proceed to Step 8.</font></font></dir>
</dir>
<a NAME="Step3"></a><b><font face="TIMES"><font size=+1>Step 3: Replace
Missing Control File</font></font></b>
<p><font face="TIMES"><font size=-1>If the file that Oracle is complaining
about is either missing or appears to have a different date and time than
the other control files, this will be easy. Simply copy another one of
the mirrored copies of the control file to the damaged control file's name
and location. (The details of this procedure are below.) Once this is done,
just attempt to mount the database again.</font></font>
<dir>
<dir><font face="TIMES"><font size=-1>Warning! Make sure to make backup
copies of all of the control files before overwriting them!</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>The first thing to do is to get the name
of the damaged control file. Again, this is relatively easy. Look in the
alert log for a section like the one below:</font></font>
<dir><font face="Courier"><font size=-2>Sat Feb 21 13:46:19 1998</font></font>
<p><font face="Courier"><font size=-2>alter database mount exclusive</font></font>
<p><font face="Courier"><font size=-2>Sat Feb 21 13:46:20 1998</font></font>
<p><font face="Courier"><font size=-2>ORA-00202: controlfile: '/db/a/oradata/crash/control01.ctl'</font></font>
<p><font face="Courier"><font size=-2>ORA-27037: unable to obtain file
status</font></font>
<p><font face="Courier"><font size=-2>SVR4 Error: 2: No such file or directory</font></font></dir>
<font face="TIMES"><font size=-1>Always make backups of all the control
files before copying any of them on top of each other. The next step would
be to copy a known good control file to the damaged control file's location.</font></font>
<dir>
<dir><font face="TIMES"><font size=-1>Once that is done, return to Step
1 and try the startup mount again.</font></font></dir>
</dir>
<b><font face="TIMES"><font size=-1>"But I don't have a good control file!"</font></font></b>
<p><font face="TIMES"><font size=-1>It's possible that there may be no
known good control file, which is what would happen if the remaining control
files have different dates and/or sizes. If this is the case, it's probably
best to use the "create controlfile" script.</font></font>
<dir>
<dir><font face="TIMES"><font size=-1>To use the create controlfile script,
proceed to Steps 4 through 7.</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>If that's not possible or probable, try
the following procedure. First, make backups of all of the control files.
Then, one at a time, try copying every version of each control file to
all the other locations -- excluding the one that Oracle has already complained
about, since it's obviously damaged.</font></font>
<dir>
<dir><font face="TIMES"><font size=-1>Each time a new control file is copied
to multiple locations, return to Step 1.</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>For example, assume there are three control
files: <i>/a/control1.ctl</i>, <i>/b/control2.ctl</i>, and <i>/c/control3.ctl</i>.
The alert log says that the <i>/c/control3.ctl</i> is damaged, and since
<i>/a/control1.ctl</i>
and <i>/b/control2.ctl</i> have different modification times, there's no
way to know which one is good. Try the following steps:</font></font>
<p><font face="TIMES"><font size=-1>First, make backup copies of all the
files:</font></font>
<dir><font face="Courier"><font size=-2>$ <b>cp /a/control1.ctl /a/control1.ctl.sav</b></font></font>
<p><font face="Courier"><font size=-2>$ <b>cp /b/control2.ctl /b/control2.ctl.sav</b></font></font>
<p><font face="Courier"><font size=-2>$ <b>cp /c/control3.ctl /c/control3.ctl.sav</b></font></font></dir>
<font face="TIMES"><font size=-1>Second, try copying one file to all locations.
Skip control3.ctl, since it's obviously damaged. Try starting with control1.ctl:</font></font>
<dir><font face="Courier"><font size=-2>$ <b>cp /a/control1.ctl /b/control2.ctl</b></font></font>
<p><font face="Courier"><font size=-2>$ <b>cp /a/control1.ctl /c/control3.ctl</b></font></font></dir>
<font face="TIMES"><font size=-1>Now attempt a startup mount:</font></font>
<dir><font face="Courier"><font size=-2>$ <b>svrmgrl</b></font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>connect internal;</b></font></font>
<br><font face="Courier"><font size=-2>Connected.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>startup mount</b></font></font>
<p><font face="Courier"><font size=-2>Sat Feb 21 15:43:21 1998</font></font>
<p><font face="Courier"><font size=-2>alter database mount exclusive</font></font>
<p><font face="Courier"><font size=-2>Sat Feb 21 15:43:22 1998</font></font>
<p><font face="Courier"><font size=-2>ORA-00202: controlfile: '/a/control3.ctl'</font></font>
<p><font face="Courier"><font size=-2>ORA-27037: unable to obtain file
status</font></font></dir>
<font face="TIMES"><font size=-1>This error says that the file that was
copied to all locations is also damaged. Now try the second file, control2.ctl:</font></font>
<dir><font face="Courier"><font size=-2>$ <b>cp /b/control2.ctl /a/control1.ctl</b></font></font>
<p><font face="Courier"><font size=-2>$ <b>cp /b/control2.ctl /a/control3.ctl</b></font></font></dir>
<font face="TIMES"><font size=-1>Now attempt to do a startup mount:</font></font>
<dir><font face="Courier"><font size=-2>SVRMGR > <b>startup mount;</b></font></font>
<p><font face="Courier"><font size=-2>ORACLE instance started.</font></font>
<p><font face="Courier"><font size=-2>Total System Global Area 5130648
bytes</font></font>
<p><font face="Courier"><font size=-2>Fixed Size 44924 bytes</font></font>
<p><font face="Courier"><font size=-2>Variable Size 4151836 bytes</font></font>
<p><font face="Courier"><font size=-2>Database Buffers 409600 bytes</font></font>
<p><font face="Courier"><font size=-2>Redo Buffers 524288 bytes</font></font>
<p><font face="Courier"><font size=-2>Database mounted.</font></font></dir>
<font face="TIMES"><font size=-1>It appears that control2.ctl was a good
copy of the control file.</font></font>
<dir>
<dir><font face="TIMES"><font size=-1>Once the attempt to mount the database
is successful, proceed to Step 10.</font></font></dir>
</dir>
<a NAME="Step4"></a><b><font face="TIMES"><font size=+1>Step 4: Are All
Data Files and Redo Logs OK?</font></font></b>
<dir>
<dir><font face="TIMES"><font size=-1>Steps 4 and 5 are required only prior
to performing Step 6.</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>The "create controlfile" script described
in Step 7 works only if all the data files and online redo logs are in
place. The data files can be older versions that were restored from backup,
since they will be rolled forward by the media recovery. However, the online
redo logs must be current and intact for the "create controlfile" script
to work.</font></font>
<p><font face="TIMES"><font size=-1>The reason that this is the case is
that the rebuild process looks at each data file as it is rebuilding the
control file. Each data file contains a System Change Number (SCN) that
corresponds to a certain online redo log. If a data file shows that it
has an SCN that is more recent than the online redo logs that are available,
the control file rebuild process will abort.</font></font>
<dir>
<dir><font face="TIMES"><font size=-1>If it's likely that one or more of
the data files or online redo logs is damaged, go to Step 5. If it's more
likely that they are all intact, go to Step 6.</font></font></dir>
</dir>
<a NAME="Step5"></a><b><font face="TIMES"><font size=+1>Step 5: Recover
Damaged Data Files or Redo Logs</font></font></b>
<p><font face="TIMES"><font size=-1>If one or more of the data files or
online redo logs are definitely damaged, follow all the instructions below
to see if there are any other damaged files. (A little extra effort now
will save a lot of frustration later.) If it's possible that all the data
files and online redo logs are okay, another option would be to skip this
step and try to recreate the control file now. (An unsuccessful attempt
at this will not cause any harm.) If it fails, return to this step. If
there is plenty of time, go ahead and perform this step first.</font></font>
<dir>
<dir><font face="TIMES"><font size=-1>To try and recreate the control files
now, proceed to Step 6.</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>The first thing to find out is where all
of the data files and redo logs are. To determine this, run the following
command on the mounted, closed database:</font></font>
<dir><font face="Courier"><font size=-2>SVRMGR > <b>connect internal;</b></font></font>
<br><font face="Courier"><font size=-2>Connected.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>select name from v$datafile;</b></font></font>
<p><i><font face="Courier"><font size=-2>(Example output below)</font></font></i>
<p><font face="Courier"><font size=-2>SVRMGR > <b>select group#, member
from v$logfile;</b></font></font>
<p><i><font face="Courier"><font size=-2>(Example output below)</font></font></i></dir>
<font face="TIMES"><font size=-1>Figure B contains sample output from these
commands:</font></font>
<dir><font face="Courier"><font size=-2>SVRMGR > <b>select name from v$datafile;</b></font></font>
<p><font face="Courier"><font size=-2>NAME</font></font>
<p><font face="Courier"><font size=-2>--------------------------------------------------------------------------------</font></font>
<p><font face="Courier"><font size=-2>/db/Oracle/a/oradata/crash/system01.dbf</font></font>
<p><font face="Courier"><font size=-2>/db/Oracle/a/oradata/crash/rbs01.dbf</font></font>
<p><font face="Courier"><font size=-2>/db/Oracle/a/oradata/crash/temp01.dbf</font></font>
<p><font face="Courier"><font size=-2>/db/Oracle/a/oradata/crash/tools01.dbf</font></font>
<p><font face="Courier"><font size=-2>/db/Oracle/a/oradata/crash/users01.dbf</font></font>
<p><font face="Courier"><font size=-2>/db/Oracle/a/oradata/crash/test01.dbf</font></font>
<p><font face="Courier"><font size=-2>6 rows selected.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>select group#, member
from v$logfile;</b></font></font>
<p><font face="Courier"><font size=-2>MEMBER</font></font>
<p><font face="Courier"><font size=-2>--------------------------------------------------------------------------------</font></font>
<p><font face="Courier"><font size=-2>1 /db/Oracle/a/oradata/crash/redocrash01.log</font></font>
<p><font face="Courier"><font size=-2>3 /db/Oracle/c/oradata/crash/redocrash03.log</font></font>
<p><font face="Courier"><font size=-2>2 /db/Oracle/b/oradata/crash/redocrash02.log</font></font>
<p><font face="Courier"><font size=-2>1 /db/Oracle/b/oradata/crash/redocrash01.log</font></font>
<p><font face="Courier"><font size=-2>2 /db/Oracle/a/oradata/crash/redocrash03.log</font></font>
<p><font face="Courier"><font size=-2>3 /db/Oracle/c/oradata/crash/redocrash02.log</font></font>
<p><font face="Courier"><font size=-2>6 rows selected.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR ></font></font>
<dir>
<center><i><font face="TIMES"><font size=-1>Figure B: Sample v$datafile
and v$logfile output</font></font></i></center>
</dir>
</dir>
<i>Look at each of the files shown by the above command. First, look at
the data files. Each of the data files probably has the same modification
time, or there might be a group of them with one modification time and
another group with a different modification time. The main thing to look
for is a missing file or a zero length file. Something else to look for
is one or more files that have a modification time that is newer than the
newest online redo log file. If a data file meets any one of these conditions,
it must be restored from backup.</i>
<p><i>Redo log files, however, are a little different. Each redo log file
within a log group should have the same modification time. For example,
the output of the example command above shows that /db/Oracle/a/oradata/crash/redocrash01.log
and /db/Oracle/a/oradata/crash/redocrash01.log are in log group one. They
should have the same modification time and size. The same should be true
for groups two and three. There are a couple of possible scenarios:</i>
<p><i>One or more log groups has at least one good and one damaged log</i>
<dir><i>This is why redo logs are mirrored! Copy the good redo log to the
damaged redo log's location. For example, if /db/Oracle/a/oradata/crash/redocrash01.log
was missing, but /db/Oracle/a/oradata/crash/redocrash01.log was intact,
issue the following command:</i>
<p><font face="Courier"><font size=-2>$ <b>cp /db/Oracle/a/oradata/crash/redocrash01.log
\</b></font></font>
<p><b><font face="Courier"><font size=-2>/db/Oracle/a/oradata/crash/redocrash01.log</font></font></b></dir>
<font face="TIMES"><font size=-1>All redo logs in at least one log group
are damaged</font></font>
<dir><font face="TIMES"><font size=-1>This is a bad place to be. The "create
controlfile" script in Step 6 requires that all online redo logs be present.
If even one log group is completely damaged, it will not be able to rebuild
the control file. This means that the only option available now is to proceed
to Steps 23 and 24 -- a complete recovery of the entire database followed
by an <i>alter database open resetlogs</i>.</font></font>
<dir><font face="TIMES"><font size=-1>Warning! This is a drastic step!
Make sure that <i>all </i>members of at least one log group are missing.
(In the example above, if both <i>/db/Oracle/a/oradata/crash/redocrash01.log</i>
and <i>/db/Oracle/a/oradata/crash/redocrash01.log</i> were damaged, this
database would require a complete recovery.)</font></font>
<p><font face="TIMES"><font size=-1>If all the redo logs in at least one
group are damaged, and all the control files are damaged, proceed to Steps
23 and 24.</font></font>
<p><font face="TIMES"><font size=-1>If the redo logs are all right, but
all the control files are missing, proceed to Step 6.</font></font>
<p><font face="TIMES"><font size=-1>If the database will not open for some
other reason, proceed to Step 10.</font></font></dir>
</dir>
<a NAME="Step6"></a><b><font face="TIMES"><font size=+1>Step 6: Is There
a "create controlfile" Script?</font></font></b>
<dir>
<dir><font face="TIMES"><font size=-1>Steps 4 and 5must be taken prior
to this Step.</font></font></dir>
</dir>
<font size=-1><font face="TIMES">The <i>svrmgrl l </i>command <i>alter
database backup control file to trace</i> creates a trace file that contains
a "create controlfile" script. This command should be run from cron on
a regular basis. To find out if there is such a script available, follow
the instructions below. The first thing to find out is the destination
of the trace files. This is specified by the </font><font face="Courier">user_dump_dest</font><font face="TIMES">
value in the <i>configinstance.ora</i> file, usually located in <i>$ORACLE_HOME/dbs</i>.
(Typically, it is <i>$ORACLE_BASE/$ORACLE_SID/admin/udump</i>.) First <i>cd</i>
to that directory, then <i>grep </i>for the phrase </font><font face="Courier">CREATE
CONTROLFILE</font><font face="TIMES">. For example:</font></font>
<dir><font face="Courier"><font size=-2>$ <b>cd $ORACLE_HOME/dbs; grep
user_dump_dest configcrash.ora</b></font></font>
<p><font face="Courier"><font size=-2>user_dump_dest = /db/Oracle/admin/crash/udump</font></font>
<p><font face="Courier"><font size=-2>$ <b>cd /db/Oracle/admin/crash/udump
; grep 'CREATE CONTROLFILE' * \</b></font></font>
<p><b><font face="Courier"><font size=-2>|awk -F: '{print $1}'|xargs ls
-ltr</font></font></b>
<p><font face="Courier"><font size=-2>-rw-r----- 1 Oracle dba 3399 Oct
26 11:25 crash_ora_617.trc</font></font>
<p><font face="Courier"><font size=-2>-rw-r----- 1 Oracle dba 3399 Oct
26 11:25 crash_ora_617.trc</font></font>
<p><font face="Courier"><font size=-2>-rw-r----- 1 Oracle dba 1179 Oct
26 11:29 crash_ora_661.trc</font></font>
<dir>
<center><i><font face="TIMES"><font size=-1>Figure C: Locating the most
recent create controlfile script</font></font></i></center>
</dir>
</dir>
<i>In the example in Figure C, crash_ora_661.trc is the most recent file
to contain the "create controlfile" script.</i>
<dir>
<dir><i>If there is a create controlfile script, proceed to Step 7. If
there is not a create controlfile script, and all the control files are
missing, proceed to Step 8.</i></dir>
</dir>
<a NAME="Step7"></a><b><font face="TIMES"><font size=+1>Step 7: Run the
'create controlfile' Script</font></font></b>
<p><font size=-1><font face="TIMES">First, find the trace file that contains
the script. The instructions on how to do that are in Step 6. Once you
find it, copy it to another filename, such as rebuild.sql. Edit the file,
deleting everything above the phrase </font><font face="Courier"># The
following commands will create,</font><font face="TIMES"> and anything
after the last SQL command. The file should then look something like the
one in Figure D:</font></font>
<dir><font face="Courier"><font size=-2># The following commands will create
a new controlfile and use it</font></font>
<p><font face="Courier"><font size=-2># to open the database.</font></font>
<p><font face="Courier"><font size=-2># Data used by the recovery manager
will be lost. Additional logs may</font></font>
<p><font face="Courier"><font size=-2># be required for media recovery
of offline data files. Use this</font></font>
<p><font face="Courier"><font size=-2># only if the current version of
all online logs are available.</font></font>
<p><font face="Courier"><font size=-2>STARTUP NOMOUNT</font></font>
<p><font face="Courier"><font size=-2>CREATE CONTROLFILE REUSE DATABASE
"CRASH" NORESETLOGS ARCHIVELOG</font></font>
<p><font face="Courier"><font size=-2>MAXLOGFILES 32</font></font>
<p><font face="Courier"><font size=-2>MAXLOGMEMBERS 2</font></font>
<p><font face="Courier"><font size=-2>MAXDATAFILES 30</font></font>
<p><font face="Courier"><font size=-2>MAXINSTANCES 8</font></font>
<p><font face="Courier"><font size=-2>MAXLOGHISTORY 843</font></font>
<p><font face="Courier"><font size=-2>LOGFILE</font></font>
<p><font face="Courier"><font size=-2>GROUP 1 '/db/a/oradata/crash/redocrash01.log'
SIZE 500K,</font></font>
<p><font face="Courier"><font size=-2>GROUP 2 '/db/b/oradata/crash/redocrash02.log'
SIZE 500K,</font></font>
<p><font face="Courier"><font size=-2>GROUP 3 '/db/c/oradata/crash/redocrash03.log'
SIZE 500K</font></font>
<p><font face="Courier"><font size=-2>DATAFILE</font></font>
<p><font face="Courier"><font size=-2>'/db/a/oradata/crash/system01.dbf',</font></font>
<p><font face="Courier"><font size=-2>'/db/a/oradata/crash/rbs01.dbf',</font></font>
<p><font face="Courier"><font size=-2>'/db/a/oradata/crash/temp01.dbf',</font></font>
<p><font face="Courier"><font size=-2>'/db/a/oradata/crash/tools01.dbf',</font></font>
<p><font face="Courier"><font size=-2>'/db/a/oradata/crash/users01.dbf'</font></font>
<p><font face="Courier"><font size=-2>;</font></font>
<p><font face="Courier"><font size=-2># Recovery is required if any of
the data files are restored backups,</font></font>
<p><font face="Courier"><font size=-2># or if the last shutdown was not
normal or immediate.</font></font>
<p><font face="Courier"><font size=-2>RECOVER DATABASE</font></font>
<p><font face="Courier"><font size=-2># All logs need archiving and a log
switch is needed.</font></font>
<p><font face="Courier"><font size=-2>ALTER SYSTEM ARCHIVE LOG ALL;</font></font>
<p><font face="Courier"><font size=-2># Database can now be opened normally.</font></font>
<p><font face="Courier"><font size=-2>ALTER DATABASE OPEN;</font></font>
<p><font face="Courier"><font size=-2># Files in read only tablespaces
are now named.</font></font>
<p><font face="Courier"><font size=-2>ALTER DATABASE RENAME FILE 'MISSING00006'</font></font>
<p><font face="Courier"><font size=-2>TO '/db/a/oradata/crash/test01.dbf';</font></font>
<p><font face="Courier"><font size=-2># Online the files in read only tablespaces.</font></font>
<p><font face="Courier"><font size=-2>ALTER TABLESPACE "TEST" ONLINE;</font></font>
<dir>
<center><i><font face="TIMES"><font size=-1>Figure D: Example create controlfile
script</font></font></i></center>
</dir>
</dir>
<i>Once the file looks like the above example, add the following line just
above the "STARTUP NOMOUNT" line:</i>
<dir><font face="Courier"><font size=-2>connect internal;</font></font></dir>
<font face="TIMES"><font size=-1>After you add this line, run the following
command on the mounted, closed database, substituting <i>rebuild.sql</i>
with the appropriate name:</font></font>
<dir><font face="Courier"><font size=-2>$ <b>svrmgrl < rebuild.sql</b></font></font></dir>
<font face="TIMES"><font size=-1>If all of the data files and online redo
log files are in place, this will work without intervention and completely
rebuild the control files.</font></font>
<dir>
<dir><font face="TIMES"><font size=-1>If any of this instance's data files
are missing, return to Step 4. However, if any of this instance's online
redo logs are damaged or missing, <i>this option will not work</i>. Proceed
to Step 8.</font></font></dir>
</dir>
<a NAME="Step8"></a><b><font face="TIMES"><font size=+1>Step 8: Restore
Control Files and Prepare the Database for Recovery</font></font></b>
<dir>
<dir><font face="TIMES"><font size=-1>This Step is required only if Steps
2 through 7 have failed.</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>If the precautions mentioned elsewhere
in this chapter were followed, there is really only one scenario that would
result in this position -- loss of the entire system due to a cataclysmic
event. Loss of a disk drive (or even multiple disk drives) is easily handled
if the control files are mirrored. Even if all control files are lost,
they can be rebuilt using the trace file created by running the <i>backup
control file to trace</i> command. The only barrier to using that script
is if all members of an online log group are missing. The only time that
you could lose all mirrored control files and all members of a mirrored
log group would be a complete system failure, such as a fire or other natural
disaster. And if that is the case, then a complete database recovery would
be more appropriate.</font></font>
<p><b><font face="TIMES"><font size=-1>But I didn't mirror my control files
or my online redo logs</font></font></b>
<p><font face="TIMES"><font size=-1>Follow the steps below, starting with
restoring the control files from backup. Chances are that the database
files will need to be restored as well. This is because one cannot use
a control file that is older than the most recent database file. (Oracle
will complain and abort if this happens.) To find out if the control file
is newer than the data files, try the following steps without overwriting
the database files and see what happens.</font></font>
<p><font face="TIMES"><font size=-1>Restore control files from backup</font></font>
<dir><font size=-1><font face="TIMES">The very first step in this process
is to find and restore the most recent backup of the control file. This
would be the results of a <i>backup control file to filename</i> command.
This is the only supported method of backing up the control file. Some
people (<i>oraback.sh</i> included) also copy the control file manually.
If there is a manual copy of the control file that is more recent than
an "official" copy, try to use it first. However, if it doesn't work, use
a backup copy created by the <i>backup control file to filename</i> command.
Whatever backup control file is used, copy it to all of the locations and
filenames listed in the <i>configORACLE_SID.ora </i>file after the phrase
</font><font face="Courier">control_files</font><font face="TIMES">:</font></font>
<p><font face="Courier"><font size=-2>control_files = (/db/Oracle/a/oradata/crash/control01.ctl,</font></font>
<p><font face="Courier"><font size=-2>/db/Oracle/b/oradata/crash/control02.ctl,</font></font>
<p><font face="Courier"><font size=-2>/db/Oracle/c/oradata/crash/control03.ctl)</font></font>
<p><font face="TIMES"><font size=-1>Again, this backup control file must
be more recent than the most recent database file in the instance. If this
isn't the case, Oracle will complain.</font></font></dir>
<font face="TIMES"><font size=-1>Startup mount</font></font>
<dir><font face="TIMES"><font size=-1>To find out if the control file is
valid and has been copied to all of the correct locations, attempt to start
up the database with the <i>mount</i> option. (This is the same command
from Step 1.) To do this, run the following command on the mounted, closed
database:</font></font>
<dir><font face="Courier"><font size=-2>$ <b>svrmgrl</b></font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>connect internal;</b></font></font>
<br><font face="Courier"><font size=-2>Connected.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>startup mount;</b></font></font>
<br><font face="Courier"><font size=-2>Statement processed.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>quit</b></font></font>
<br>
<br> </dir>
</dir>
<font face="TIMES"><font size=-1>Take read-only tablespaces offline</font></font>
<dir><font face="TIMES"><font size=-1>Oracle does not allow read-only data
files to be online during a <i>recover database using backup control file</i>
action. Therefore, if there are any read-only data files, take them offline.
To find out if there are any read-only data files, issue the following
command on the mounted, closed database:</font></font>
<dir><font face="Courier"><font size=-2>$ <b>svrmgrl</b></font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>connect internal;</b></font></font>
<br><font face="Courier"><font size=-2>Connected.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>select enabled, name
from v$data file;</b></font></font>
<br><font face="Courier"><font size=-2>Statement processed.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>quit</b></font></font></dir>
<font face="TIMES"><font size=-1>For each read-only data file, issue the
following command on a mounted, closed database:</font></font>
<dir><font face="Courier"><font size=-2>$ <b>svrmgrl</b></font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>connect internal;</b></font></font>
<br><font face="Courier"><font size=-2>Connected.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>alter database data file
'filename' offline;</b></font></font>
<br><font face="Courier"><font size=-2>Statement processed.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>quit</b></font></font></dir>
</dir>
<a NAME="Step9"></a><font face="TIMES"><font size=+1>Step 9: Recover the
Database</font></font>
<dir>
<dir><font face="TIMES"><font size=-1>This step is required only if Steps
2 through 7 have failed.</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>Once the control file is restored with
a backup copy, attempt to recover the database using the backup control
file.</font></font>
<p><b><font face="TIMES"><font size=-1>Attempt to recover database normally</font></font></b>
<p><font face="TIMES"><font size=-1>Since recovering the database with
a backup control file requires the <i>alter database open resetlogs</i>
option, it never hurts to try recovering the database normally first:</font></font>
<dir><font face="Courier"><font size=-2>$ <b>svrmgrl</b></font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>connect internal;</b></font></font>
<br><font face="Courier"><font size=-2>Connected.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>recover database;</b></font></font></dir>
<font face="TIMES"><font size=-1>If the backup control file option is required,
Oracle will complain:</font></font>
<dir><font face="Courier"><font size=-2>SVRMGR > <b>recover database</b></font></font>
<p><font face="Courier"><font size=-2>ORA-00283: Recover session cancelled
due to errors</font></font>
<p><font face="Courier"><font size=-2>...</font></font>
<p><font face="Courier"><font size=-2>ORA-01207: file is more recent than
controlfile - old controlfile</font></font>
<dir><font face="TIMES"><font size=-1>If the recover database command works,
then proceed to Step 10. If it doesnt, proceed to the next heading, "Attempt
to recover database using backup control file."</font></font></dir>
</dir>
<b><font face="TIMES"><font size=-1>Attempt to recover database using backup
control file</font></font></b>
<p><font face="TIMES"><font size=-1>Attempt to recover the database using
the following command on the mounted, closed database:</font></font>
<dir><font face="Courier"><font size=-2>$ <b>svrmgrl</b></font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>connect internal;</b></font></font>
<br><font face="Courier"><font size=-2>Connected.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>recover database using
backup controlfile</b></font></font></dir>
<font face="TIMES"><font size=-1>If it works, the output looks will something
Figure E:</font></font>
<dir><font face="Courier"><font size=-2>ORA-00279: change 38666 generated
at 03/14/98 21:19:05 needed for thread 1</font></font>
<p><font face="Courier"><font size=-2>ORA-00289: suggestion : /db/Oracle/admin/crash/arch/arch.log1_494.dbf</font></font>
<p><font face="Courier"><font size=-2>ORA-00280: change 38666 for thread
1 is in sequence #494</font></font>
<dir>
<center><i><font face="TIMES"><font size=-1>Figure D: Sample output of
</font></font>recover
database command</i></center>
<p><br>
<br>
<br>
<br>
<br>
<p><i>If Oracle complains, there are probably some missing or corrupted
data files. If so, return to Steps 4 and 5. Once any missing or corrupted
data files are restored, return to this step and attempt to recover the
database again.</i>
<p><i>Sometimes one can get in a catch-22 when recovering databases, where
Oracle is complaining about data files being newer than the control file.
The only way to get around this is to use a backup version of the data
files that is older than the backup version of the control file. Media
recovery will roll forward any changes that this older file is missing.</i></dir>
</dir>
<b><i>Apply all archived redo logs</i></b>
<p><i>Oracle will request all archived redo logs since the time of the
oldest restored data file. For example, if the backup that was used to
restore the data files was from three days ago, Oracle will need all archived
redo logs created since then. Also, the first log file that it asks for
is the oldest log file that it wants.</i>
<p><i>The most efficient way to roll through the archived redo logs is
to have all of them sitting uncompressed in the directory that it suggests
as the location of the first file. If this is the case, simply enter auto
at the prompt. Otherwise, specify alternate locations or hit enter as it
asks for each one, giving time to compress or remove the files that it
no longer needs.</i>
<p><b><i>Apply online redo logs if they are available</i></b>
<p><i>If it is able to do so, Oracle will automatically roll through all
the archived redo logs and the online redo log. Then it says, "</i><font size=-1><font face="Courier">Media
recovery complete</font><font face="Courier New">.</font><font face="TIMES">"</font></font>
<p><font face="TIMES"><font size=-1>However, once Oracle rolls through
all the archived redo logs, it may prompt for the online redo log. It does
this by prompting for an archived redo log with a number that is higher
than the most recent archived redo log available. This means that it is
looking for the online redo log. Try answering its prompt with the names
of the online redo log files that you have. Unfortunately, as soon as you
give it a name it doesn't like, it will make you start the <i>recover database
using backup controlfile</i> command again.</font></font>
<p><font face="TIMES"><font size=-1>For example, suppose that you have
the following three online redo logs:</font></font>
<dir><font face="Courier"><font size=-2>/oracle/data/redolog01.dbf</font></font>
<p><font face="Courier"><font size=-2>/oracle/data/redolog02.dbf</font></font>
<p><font face="Courier"><font size=-2>/oracle/data/redolog03.dbf</font></font></dir>
<font face="TIMES"><font size=-1>When you are prompting for an archived
redo log that has a higher number than the highest numbered archived redo
log that you have, answer the prompt with one of these files (e.g., <i>/oracle/data/redolog01.dbf</i>).
If the file that you give it does not contain the recovery thread it is
looking for, you will see a message like the following:</font></font>
<dir><font face="Courier"><font size=-2>ORA-00310: archived log contains
sequence 2; sequence 3 required</font></font>
<p><font face="Courier"><font size=-2>ORA-00334: archive log: '/oracle/data/redolog01.dbf'</font></font></dir>
<font face="TIMES"><font size=-1>Oracle will cancel the recovery database,
requiring you to start it over. Once you get to the same prompt again,
respond with a different filename, such as /oracle/data/redolog02.dbf.
If it contains the recovery thread it is looking for, it will respond with
a message like the following:</font></font>
<dir><font face="Courier"><font size=-2>Log applied.</font></font>
<p><font face="Courier"><font size=-2>Media recovery complete.</font></font></dir>
<font face="TIMES"><font size=-1>If after trying all the online redo logs
it is still asking for a log that you do not have, simply enter <i>cancel</i>.</font></font>
<p><b><font face="TIMES"><font size=-1>Alter database open resetlogs</font></font></b>
<p><font face="TIMES"><font size=-1>Once the media recovery is complete,
the next step is to open the database. As mentioned earlier, when recovering
the database using a backup control file, it must be opened with the <i>resetlogs</i>
option. Do this by entering:</font></font>
<dir><font face="Courier"><font size=-2>$ <b>svrmgrl</b></font></font>
<p><font face="Courier"><font size=-2>SVRMGR ><b> connect internal;</b></font></font>
<br><font face="Courier"><font size=-2>Connected.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>alter database open resetlogs;</b></font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>quit</b></font></font></dir>
<font face="TIMES"><font size=-1>Take a backup immediately after recovering
the database with the resetlogs option! It is best if it is a cold backup
after shutting down the database. Perform a hot backup if absolutely necessary,
but realize that there is a risk that:</font></font>
<ul>
<li>
<font face="TIMES"><font size=-1>The entire recovery might need to be performed
again</font></font></li>
<li>
<font face="TIMES"><font size=-1>All changes made after using the <i>resetlogs</i>
option will be lost</font></font></li>
</ul>
<dir>
<dir><font face="TIMES"><font size=-1>If the database did not open successfully,
return to Step 1 and start over.</font></font>
<p><font face="TIMES"><font size=-1>If the database did open successfully,
perform a backup of the entire database immediately -- preferably a cold
one. Congratulations! You're done!</font></font></dir>
</dir>
<a NAME="Step10"></a><b><font face="TIMES"><font size=+1>Step 10: Does
"alter database open" work?</font></font></b>
<p><font face="TIMES"><font size=-1>If the <i>startup mount</i> worked,
this is actually only the second step that you will perform. Mounting the
database only checks the presence and consistency of the control files.
If that works, opening the database is the next step. Doing so will check
the presence and consistency of all data files, online redo log files,
and any rollback segments. To open the database, run the following command
on the mounted, closed database:</font></font>
<dir><font face="Courier"><font size=-2>$ <b>svrmgrl</b></font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>connect internal;</b></font></font>
<br><font face="Courier"><font size=-2>Connected.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>alter database open;</b></font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>quit</b></font></font></dir>
<font size=-1><font face="TIMES">If the attempt to open the database worked,
Oracle will simply say, "</font><font face="Courier">Statement processed.</font><font face="TIMES">"
If this is the first attempt to open the database, and no data files or
rollback segments were taken offline, You're done!</font></font>
<dir>
<dir><font face="TIMES"><font size=-1>If directed to this step by Steps
26 or 28 (damaged log groups), and the attempt at opening the database
failed, return to Step 23 to recover the entire database.</font></font>
<p><font face="TIMES"><font size=-1>If the database did open, proceed to
Step 15.</font></font>
<br>
<br> </dir>
</dir>
<font face="TIMES"><font size=-1>If the attempt to open the database did
<i>not</i>
work, the output will vary depending on the condition. Here is a listing
of what those conditions may be, accompanied by what the error might look
like when that condition occurs.</font></font>
<p><font face="TIMES"><font size=-1>Missing data file</font></font>
<dir>
<dir><font face="Courier"><font size=-2>ORA-01157: cannot identify data
file 1 - file not found</font></font>
<p><font face="Courier"><font size=-2>ORA-01110: data file 1: '/db/Oracle/a/oradata/crash/system01.dbf'</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>Corrupted data file</font></font>
<dir><font face="TIMES"><font size=-1>A corrupted data file can generate
a number of different errors. For instance, it may mimic a missing data
file:</font></font>
<dir><font face="Courier"><font size=-2>ORA-01157: cannot identify data
file 1 - file not found</font></font>
<p><font face="Courier"><font size=-2>ORA-01110: data file 1: '/db/Oracle/a/oradata/crash/system01.dbf'</font></font></dir>
<font face="TIMES"><font size=-1>It may also completely confuse Oracle:</font></font>
<dir><font face="Courier"><font size=-2>ORA-00600: internal error code,
arguments: [kfhcfh1_01], [0], [], [], [], [], [], []</font></font></dir>
<font face="TIMES"><font size=-1>A corrupted data file may also cause a
"failed verification check" error:</font></font>
<dir><font face="Courier"><font size=-2>ORA-01122: database file 1 failed
verification check</font></font>
<p><font face="Courier"><font size=-2>ORA-01110: data file 1: '/db/Oracle/a/oradata/crash/system01.dbf'</font></font>
<p><font face="Courier"><font size=-2>ORA-01200: actual file size of 1279
is smaller than correct size of 40960 blocks</font></font></dir>
<font face="TIMES"><font size=-1>These are just a few examples of the types
of errors that Oracle may give if a data file is corrupted.</font></font></dir>
<font face="TIMES"><font size=-1>Missing member of any online log group</font></font>
<dir><font face="TIMES"><font size=-1>If the redo logs are mirrored, one
or more of the mirrored copies are lost, but at least one good copy of
each online redo log remains, Oracle will open the database without any
errors displayed to the terminal. The only error will be a message like
the following one in the alert log:</font></font>
<dir><font face="Courier"><font size=-2>Errors in file /db/Oracle/admin/crash/bdump/crash_lgwr_10302.trc:</font></font>
<p><font face="Courier"><font size=-2>ORA-00313: open failed for members
of log group 2 of thread 1</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>All members of any online log group are
corrupted</font></font>
<dir><font face="TIMES"><font size=-1>However, if all members of any online
log group are corrupted, Oracle <i>will </i>complain and the database will
not open. The error might look something like this:</font></font>
<dir><font face="Courier"><font size=-2>ORA-00327: log 2 of thread 1, physical
size less than needed</font></font>
<p><font face="Courier"><font size=-2>ORA-00312: online log 2 thread 1:
'/db/Oracle/b/oradata/crash/redocrash02.log'</font></font>
<p><font face="Courier"><font size=-2>ORA-00312: online log 2 thread 1:
'/db/Oracle/a/oradata/crash/redocrash03.log'</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>Missing all members of any online log
group</font></font>
<dir><font face="TIMES"><font size=-1>A similar problem is if all members
of an online log group are missing. Oracle will complain and the database
will not open. The error looks something like this:</font></font>
<dir><font face="Courier"><font size=-2>ORA-00313: open failed for members
of log group 2 of thread 1</font></font>
<p><font face="Courier"><font size=-2>ORA-00312: online log 2 thread 1:
'/db/Oracle/b/oradata/crash/redocrash02.log'</font></font>
<p><font face="Courier"><font size=-2>ORA-00312: online log 2 thread 1:
'/db/Oracle/a/oradata/crash/redocrash03.log'</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>Damaged rollback segment</font></font>
<dir><font face="TIMES"><font size=-1>If a rollback segment is damaged,
the error will be like the following one:</font></font>
<dir><font face="Courier"><font size=-2>ORA-01545: rollback segment 'USERS_RS'
specified not available</font></font>
<p><font face="Courier"><font size=-2>Cannot open database if all rollback
segments are not available.</font></font></dir>
</dir>
<b><font face="TIMES"><font size=-1>Damaged datafile</font></font></b>
<p><b>A damaged data file is actually very easy to recover from. This is
a good thing, because this will occur more often than any other problem.
Remember that there is only one copy of each data file, unlike online redo
logs and control files that can be mirrored. So, statistically speaking,
it's easier to lose one data file than to lose all mirrored copies of a
log group or all mirrored copies of the control file.</b>
<p><b>Oracle also has the ability to recover parts of the database while
other parts of the database are brought online. Unfortunately, this helps
only if a partially functioning database is of any use to the users in
your environment. Therefore, a database that is completely worthless unless
all tables are available will not benefit from the partial online restore
feature. However, if the users can use one part of the database while the
damaged files are being recovered, this feature may help to save face by
allowing at least partial functionality during an outage.</b>
<p><b>There are three types of data files as far as recovery is concerned:</b>
<ul>
<li>
<b>The first is a data file that is not a part of the </b><font size=-1><font face="Courier">SYSTEM</font><font face="TIMES">
tablespace and does not contain any rollback segments. Recovering this
file (with the database online or offline) is very easy.</font></font></li>
<li>
<font face="TIMES"><font size=-1>The second type of data file is also a
non-system data file, but one that happens to contain a rollback segment.
Since rollback segments are needed to open the database, recovering such
a file with the database online is difficult.</font></font></li>
<li>
<font size=-1><font face="TIMES">The final type of data file is a file
contained within the </font><font face="Courier">SYSTEM</font><font face="TIMES">
tablespace. This data file cannot be recovered with the database online,
because the database cannot be brought online without it.</font></font></li>
</ul>
<b><font face="TIMES"><font size=-1>Damaged log group</font></font></b>
<p><font face="TIMES"><font size=-1>If all members of a log group are damaged,
there is a great potential for data loss. The entire database may have
to be restored, depending on the status of the log group that was damaged,
and the results of some attempts at fixing it. This may seem like a broken
record, but this is why mirroring the log groups is so important.</font></font>
<dir>
<dir><font face="TIMES"><font size=-1>If the error refers to a damaged
log group, one option is to proceed directly to Step 17. However, to verify
that nothing else is wrong, read the following notes and proceed to the
next step.</font></font></dir>
</dir>
<b><font face="TIMES"><font size=-1>Damaged rollback segment</font></font></b>
<p><font face="TIMES"><font size=-1>Since Oracle has to open the data files
that contain this rollback segment before it can verify that the rollback
segment is available, this error will not occur unless a data file has
been taken offline. If Oracle encounters a damaged data file (whether or
not it contains a rollback segment), it will complain about that data file
and abort the attempt to open the database.</font></font>
<p><font face="TIMES"><font size=-1>Remember that a <i>rollback segment
</i>is
a special part of a tablespace that stores <i>rollback </i>information.
Rollback information is needed in order to undo (or rollback) an uncommitted
transaction. Since a crashed database will almost always contain uncommitted
transactions, recovering a database with a damaged rollback segment is
a little tricky. As previously mentioned, a damaged data file may be taken
offline, but Oracle will not open the database without the rollback segment.</font></font>
<p><font face="TIMES"><font size=-1>The strategy for dealing with this
is to make Oracle believe that the rollback segment doesn't exist. That
will allow the database to be brought online. However, there will be transactions
that need to be rolled back that require this rollback segment. Since Oracle
believes this rollback segment is no longer available, these rollbacks
cannot occur. This means that the database may be online, but portions
of it will not be available.</font></font>
<p><font size=-1><font face="TIMES">For example, suppose that we created
a table called </font><font face="Courier">data1</font><font face="TIMES">
inside tablespace </font><font face="Courier">USERS</font><font face="TIMES">.
Tablespace </font><font face="Courier">USERS</font><font face="TIMES">
contains the data file <i>/db/oracle/a/oradata/crash/users01.dbf</i>. Unfortunately,
the database crashed before this transaction was committed, and the data
file that contains the rollback segment for this transaction was destroyed.
In the process of recovering this database, we took that data file offline,
convinced Oracle that the rollback segment it contained was not needed,
and opened the database. If we run the command <i>select * from data1</i>,
we will receive the error shown in Figure F:</font></font>
<dir><font face="Courier"><font size=-2>$ <b>svrmgrl</b></font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>connect internal;</b></font></font>
<br><font face="Courier"><font size=-2>Connected.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>select * from data1;</b></font></font>
<p><font face="Courier"><font size=-2>C1</font></font>
<p><font face="Courier"><font size=-2>------------</font></font>
<p><font face="Courier"><font size=-2>ORA-00376: file 7 cannot be read
at this time</font></font>
<p><font face="Courier"><font size=-2>ORA-01110: datafile 7: '/db/oracle/a/oradata/crash/users01.dbf'</font></font>
<dir>
<center><i><font face="TIMES"><font size=-1>Figure F: Sample data file
error</font></font></i></center>
</dir>
</dir>
<i>This is because Oracle does not know if the uncommitted transactions
in /db/oracle/a/oradata/crash/users01.dbf have been rolled back or not.
In order to make this database fully functional, the damaged data file
must be recovered and the rollback segment brought online.</i>
<p><i>Be aware, therefore, that if you bring a database online without
all of its rollback segments, the database may be online -- but it probably
will not be fully functional.</i>
<dir>
<dir><i>If the error indicates that there is a damaged rollback segment,
proceed to Step 18.</i></dir>
</dir>
<b><i>Before going any farther
</i></b>
<p><i>Remember that Oracle will stop attempting to open the database as
soon as it encounters an error with one file. This means, of course, that
there could be other files that are damaged. If there is at least one damaged
data file, now is a good time to check and see if there are other files
that are damaged.</i>
<dir>
<dir><i>Detailed instructions on how to do that are provided in Step 5.</i></dir>
</dir>
<i>Once you know the names of all the damaged files, proceed to the next
section.</i>
<p><b><i>How media recovery works</i></b>
<p><i>If any data files are restored from backup, the svrmgr recover command
will be needed. This command uses the archived and online redo logs to
"redo" any transactions that have occurred since the time that the backup
of a data file was taken. You can recover a complete database, a tablespace,
or a data file by issuing the commands recover database, recover tablespace
tablespace_name and recover data file data file_name, respectively. These
commands are issued inside a svrmgr shell. For example:</i>
<dir><font face="Courier"><font size=-2>$ <b>svrmgrl</b></font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>connect internal</b></font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>startup mount</b></font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>recover datafile '/db/Oracle/a/oradata/crash/datafile01.dbf'</b></font></font></dir>
<font face="TIMES"><font size=-1>These commands allow the restore of an
older version of a data file, and use redo to roll it forward to the point
of failure. For example, if we took a backup of a data file on Wednesday
night, and that data file was damaged on Thursday evening, we would restore
that data file from Wednesday night's backup. Of course many transactions
would have occurred since Wednesday night, making changes to the data files
that we restored. Running the command <i>recover </i>[<i>database</i>|<i>tablespace</i>|<i>data
file</i>]<i> </i>would reapply those transactions to the restored data
file, rolling them forward to Thursday evening.</font></font>
<p><font face="TIMES"><font size=-1>This recovery can work in a number
of ways. After receiving the <i>recover</i> command, Oracle prompts for
the name and location of the first archived redo log that it needs. If
that log, and all logs that have been made sinse that log, are online,
uncompressed, and in their original location, enter the word <i>AUTO</i>.
This tells Oracle to assume that all files that it needs are online. It
can therefore automatically roll through each log that it needs.</font></font>
<p><font face="TIMES"><font size=-1>In order to do this, all files that
Oracle will need must be online. First, get the name of the oldest file,
since that it is the first file it will need. That file name is displayed
immediately after issuing the <i>recover </i>command:</font></font>
<dir><font face="Courier"><font size=-2>ORA-00279: change 18499 generated
at 02/21/98 11:49:56 needed for thread 1</font></font>
<p><font face="Courier"><font size=-2>ORA-00289: suggestion : /db/Oracle/admin/crash/arch/arch.log1_481.dbf</font></font>
<p><font face="Courier"><font size=-2>ORA-00280: change 18499 for thread
1 is in sequence #481</font></font>
<p><font face="Courier"><font size=-2>Specify log: {<RET>=suggested
| filename | AUTO | CANCEL}</font></font></dir>
<font size=-1><font face="TIMES">In the example above, the first file that
Oracle needs is <i>/db/Oracle/admin/crash/arch/arch.log1_481.dbf</i>. Make
sure that this file is online and not compressed or deleted. If it is deleted,
restore it from backup. If it is compressed, uncompress it and any archived
redo log files in that directory that are newer than it. That is because
Oracle may need all of them to complete the media recovery. It might be
necessary to delete some of the older archived redo logs to make enough
room for the files that need to be uncompressed. Once all archived redo
logs that are newer than the one requested by Oracle have been restored
and uncompressed, enter <i>AUTO</i> at the "</font><font face="Courier">Specify