只有数据文件备份和归档日志如何恢复数据库

gdpr-dba

版本11.2.0.3

只有数据文件的rman备份和归档日志，有控制文件的创建脚本，数据文件和控制文件全部丢失，并且没有控制文件备份，有没有办法能恢复数据库？

[ 本帖最后由 gdpr-dba 于 2012-5-19 11:30 编辑 ]

Maclean Liu(刘相兵

在没有控制文件 无法mount db的情况下 可以利用内置的dbms_backup_restore 包从 rman备份中抽取文件


下面是例子：

ODM TEST:

1. RMAN> list backup of database;
   
2. 
   
3. using target database control file instead of recovery catalog
   
4. 
   
5. List of Backup Sets
   
6. ===================
   
7. 
   
8. BS Key  Type LV Size       Device Type Elapsed Time Completion Time
   
9. ------- ---- -- ---------- ----------- ------------ ---------------
   
10. 15      Full    2.46G      DISK        00:00:19     21-MAY-12      
    
11.         BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: TAG20120521T111811
    
12.         Piece Name: /s01/0jnbhhnj_1_1.bak
    
13.   List of Datafiles in backup set 15
    
14.   File LV Type Ckp SCN    Ckp Time  Name
    
15.   ---- -- ---- ---------- --------- ----
    
16.   1       Full 2706191    21-MAY-12 /s01/oradata/G10R25/datafile/o1_mf_system_7ch7d4mn_.dbf
    
17.   2       Full 2706191    21-MAY-12 /s01/oradata/G10R25/datafile/o1_mf_undotbs1_7ch7d4nt_.dbf
    
18.   3       Full 2706191    21-MAY-12 /s01/oradata/G10R25/datafile/o1_mf_sysaux_7ch7d4n2_.dbf
    
19.   4       Full 2706191    21-MAY-12 /s01/oradata/G10R25/datafile/o1_mf_users_7ch7d4nx_.dbf
    
20.   5       Full 2706191    21-MAY-12 /s01/oradata/G10R25/datafile/o1_mf_example_7ch7f9vx_.dbf
    
21.   6       Full 2706191    21-MAY-12 /s01/oradata/G10R25/datafile/o1_mf_bct_test_7tgwoyyd_.dbf
    
22.   7       Full 2706191    21-MAY-12 /s01/oradata/G10R25/datafile/o1_mf_bigs_7tnktkkv_.dbf
    
23.   8       Full 2706191    21-MAY-12 /s01/oradata/G10R25/datafile/o1_mf_testfree_7tt5m8om_.dbf
    
24. 
    
25. BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    
26. ------- ---- -- ---------- ----------- ------------ ---------------
    
27. 17      Full    2.46G      DISK        00:00:17     21-MAY-12      
    
28.         BP Key: 19   Status: AVAILABLE  Compressed: NO  Tag: TAG20120521T111914
    
29.         Piece Name: /s01/0lnbhhpi_1_1.bak
    
30.   List of Datafiles in backup set 17
    
31.   File LV Type Ckp SCN    Ckp Time  Name
    
32.   ---- -- ---- ---------- --------- ----
    
33.   1       Full 2706226    21-MAY-12 /s01/oradata/G10R25/datafile/o1_mf_system_7ch7d4mn_.dbf
    
34.   2       Full 2706226    21-MAY-12 /s01/oradata/G10R25/datafile/o1_mf_undotbs1_7ch7d4nt_.dbf
    
35.   3       Full 2706226    21-MAY-12 /s01/oradata/G10R25/datafile/o1_mf_sysaux_7ch7d4n2_.dbf
    
36.   4       Full 2706226    21-MAY-12 /s01/oradata/G10R25/datafile/o1_mf_users_7ch7d4nx_.dbf
    
37.   5       Full 2706226    21-MAY-12 /s01/oradata/G10R25/datafile/o1_mf_example_7ch7f9vx_.dbf
    
38.   6       Full 2706226    21-MAY-12 /s01/oradata/G10R25/datafile/o1_mf_bct_test_7tgwoyyd_.dbf
    
39.   7       Full 2706226    21-MAY-12 /s01/oradata/G10R25/datafile/o1_mf_bigs_7tnktkkv_.dbf
    
40.   8       Full 2706226    21-MAY-12 /s01/oradata/G10R25/datafile/o1_mf_testfree_7tt5m8om_.dbf
    
41. 
    
42. 
    
43. 
    
44. 
    
45. 
    
46. 
    
47. 
    
48. SQL> alter database backup controlfile to trace;
    
49. 
    
50. Database altered.
    
51. 
    
52. SQL> oradebug setmypid;
    
53. Statement processed.
    
54. SQL> oradebug tracefile_name
    
55. /s01/admin/G10R25/udump/g10r25_ora_5483.trc
    
56. SQL>
    
57. SQL>
    
58. SQL> shutdown immediate;
    
59. 
    
60. 
    
61. 
    
62. 
    
63. 
    
64. DECLARE
    
65.   v_dev  varchar2(50); -- device type allocated for restore
    
66.   v_done boolean := false; -- has the datafile been fully extracted yet
    
67.   type t_fileTable is table of varchar2(255) index by binary_integer;
    
68.   v_fileTable t_fileTable; -- Stores the backuppiece names
    
69.   v_maxPieces number := 1; -- Number of backuppieces in backupset
    
70. BEGIN
    
71. 
    
72.   -- Initialise the filetable and number of backup pieces in the backupset
    
73.   -- This section of code MUST be edited to reflect the customer's available
    
74.   -- backupset before the procedure is compiled and run. In this example, the
    
75.   -- backupset consists of 4 pieces:
    
76. 
    
77.   v_fileTable(1) := '/s01/0jnbhhnj_1_1.bak';
    
78.   v_maxPieces := 1;
    
79. 
    
80.   -- Allocate a device. In this example, I have specified 'sbt_tape' as I am
    
81.   -- reading backuppieces from the media manager. If the backuppiece is on disk,
    
82.   -- specify type=>null
    
83. 
    
84.   v_dev := sys.dbms_backup_restore.deviceAllocate(type  => NULL,
    
85.                                                   ident => 't1');
    
86. 
    
87. 
    
88. 
    
89.   -- Begin the restore conversation
    
90. 
    
91.   sys.dbms_backup_restore.restoreSetDatafile;
    
92. 
    
93.   -- Specify where the datafile is to be recreated
    
94. 
    
95.   sys.dbms_backup_restore.restoreDataFileTo(dfnumber => 1,
    
96.                                             toname   => '/s01/oradata/G10R25/datafile/o1_mf_system_7ch7d4mn_.dbf');
    
97. 
    
98.   -- Restore the datafile
    
99. 
    
100.   FOR i IN 1 .. v_maxPieces LOOP
     
101.     sys.dbms_backup_restore.restoreBackupPiece(done   => v_done,
     
102.                                                handle => v_fileTable(i),
     
103.                                                params => null);
     
104.     IF v_done THEN
     
105.       GOTO all_done;
     
106.     END IF;
     
107.   END LOOP;
     
108. 
     
109.   <<all_done>>
     
110. -- Deallocate the device
     
111.   sys.dbms_backup_restore.deviceDeallocate;
     
112. 
     
113. END;
     
114. /
     
115. 
     
116. 
     
117. 
     
118. [oracle@vrh8 datafile]$ cd /s01/oradata/G10R25/datafile/
     
119. 
     
120. [oracle@vrh8 datafile]$ ls -lh *
     
121. -rw-r----- 1 oracle oinstall 801M May 21 22:22 o1_mf_unknown_7voy2grh_.dbf
     
122. 
     
123. 
     
124. 
     
125. SQL> CREATE CONTROLFILE REUSE DATABASE "G10R25" NORESETLOGS  ARCHIVELOG
     
126.   2      MAXLOGFILES 16
     
127.   3      MAXLOGMEMBERS 3
     
128.   4      MAXDATAFILES 100
     
129.   5      MAXINSTANCES 8
     
130.   6      MAXLOGHISTORY 292
     
131.   7  LOGFILE
     
132.   8    GROUP 1 (
     
133.   9      '/s01/oradata/G10R25/onlinelog/o1_mf_1_7ch7f3w8_.log',
     
134. 10      '/s01/flash_recovery_area/G10R25/onlinelog/o1_mf_1_7ch7f4o2_.log'
     
135. 11    ) SIZE 50M,
     
136. 12    GROUP 2 (
     
137. 13      '/s01/oradata/G10R25/onlinelog/o1_mf_2_7ch7f599_.log',
     
138. 14      '/s01/flash_recovery_area/G10R25/onlinelog/o1_mf_2_7ch7f616_.log'
     
139. 15    ) SIZE 50M,
     
140. 16    GROUP 3 (
     
141. 17      '/s01/oradata/G10R25/onlinelog/o1_mf_3_7ch7f6kb_.log',
     
142. 18      '/s01/flash_recovery_area/G10R25/onlinelog/o1_mf_3_7ch7f7dj_.log'
     
143. 19    ) SIZE 50M
     
144. 20  -- STANDBY LOGFILE
     
145. 21  DATAFILE
     
146. 22    '/s01/oradata/G10R25/datafile/o1_mf_unknown_7voy2grh_.dbf'
     
147. 23    CHARACTER SET AL32UTF8;
     
148. 
     
149. Control file created.

复制代码

Maclean Liu(刘相兵

理论上可以从 backup piece中 抽取出datafile ， 这个问题 会在博客上 以文章形式说明

敬请期待

liuxbgiant

好像这个问题，要看数据文件的rman备份类型哟，具体还是请刘大回答。

gdpr-dba

原帖由 maclean 于 2012-5-19 10:10 发表
好的提问的 基本 要素    一个好的标题、  版本信息 、 日志信息、报错信息

刘大，我修改了一下标题和内容，谢谢。

Maclean Liu(刘相兵

好的提问的 基本 要素    一个好的标题、  版本信息 、 日志信息、报错信息