› Fóruns › Banco de dados Oracle › Restore de TABLESPACE com o banco no ar › Restore de TABLESPACE com o banco no ar
vieriPassei por esse problema de perder datafile com a base on-line! Mas foi proposital!! 😆
Acho que é exatamente este how to que vai ter que fazer.
Tenho feito varias simulações de crash/recovery… com RMAN.
É extremamente eficaz e eficiente!!!!
Recuperação com RMAN, após remoção “crash” de datafile com a base on-line.
[oracle@admbidev wis]$ rm -rf users.dbf
[oracle@admbidev wis]$ sqlplus / as sysdba ;
SQL*Plus: Release 10.2.0.3.0 – Production on Mon Jul 27 09:28:00 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate ;
ORA-01116: error in opening database file 35
ORA-01110: data file 35: ‘/u03/wis/users.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort ;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options
[oracle@admbidev wis]$ rman target /
Recovery Manager: Release 10.2.0.3.0 – Production on Mon Jul 27 09:28:23 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup mount ;
Oracle instance started
database mounted
Total System Global Area 536870912 bytes
Fixed Size 1262788 bytes
Variable Size 155192124 bytes
Database Buffers 373293056 bytes
Redo Buffers 7122944 bytes
RMAN> list backup ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
1 Full 6.05M DISK 00:00:00 21-JUL-09
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20090721T153433
Piece Name: /u03/wis/full_wis_692811273_2
Control File Included: Ckp SCN: 5256320567 Ckp time: 21-JUL-09
BS Key Type LV Size Device Type Elapsed Time Completion Time
2 Full 50.58G DISK 00:00:00 21-JUL-09
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20090721T163226
Piece Name: /u03/wis/full_wis_692814747_3
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
1 Full 5256322985 21-JUL-09 /u03/wis/system01.dbf
2 Full 5256322985 21-JUL-09 /u03/wis/undotbs1.dbf
3 Full 5256322985 21-JUL-09 /u03/wis/sysauy01.dbf
4 Full 5256322985 21-JUL-09 /u03/wis/data_entrega.dbf
5 Full 5256322985 21-JUL-09 /u03/wis/data_entrega_small.dbf
6 Full 5256322985 21-JUL-09 /u03/wis/gkoscf_dtau.dbf
7 Full 5256322985 21-JUL-09 /u03/wis/gkoscf_inau.dbf
8 Full 5256322985 21-JUL-09 /u03/wis/gkoscf_indy.dbf
9 Full 5256322985 21-JUL-09 /u03/wis/indy_entrega.dbf
10 Full 5256322985 21-JUL-09 /u03/wis/tbs_data_asstec.dbf
11 Full 5256322985 21-JUL-09 /u03/wis/tbs_data_cestqhist_32m.dbf
12 Full 5256322985 21-JUL-09 /u03/wis/tbs_data_detprep_32m.dbf
13 Full 5256322985 21-JUL-09 /u03/wis/tbs_data_hist_256k.dbf
14 Full 5256322985 21-JUL-09 /u03/wis/tbs_data_hist_32m.dbf
15 Full 5256322985 21-JUL-09 /u03/wis/tbs_data_hist_5m.dbf
16 Full 5256322985 21-JUL-09 /u03/wis/tbs_data_hist_64m.dbf
17 Full 5256322985 21-JUL-09 /u03/wis/tbs_data_inet.dbf
18 Full 5256322985 21-JUL-09 /u03/wis/tbs_data_sge.dbf
19 Full 5256322985 21-JUL-09 /u03/wis/tbs_data_trcmovestq_64m.dbf
20 Full 5256322985 21-JUL-09 /u03/wis/tbs_data_wis_256k.dbf
21 Full 5256322985 21-JUL-09 /u03/wis/tbs_data_wis_32m.dbf
22 Full 5256322985 21-JUL-09 /u03/wis/tbs_data_wis_5m.dbf
23 Full 5256322985 21-JUL-09 /u03/wis/tbs_indy_asstec.dbf
24 Full 5256322985 21-JUL-09 /u03/wis/tbs_indy_cestqhist_32m.dbf
25 Full 5256322985 21-JUL-09 /u03/wis/tbs_indy_detprep__32m.dbf
26 Full 5256322985 21-JUL-09 /u03/wis/tbs_indy_hist_256k.dbf
27 Full 5256322985 21-JUL-09 /u03/wis/tbs_indy_inet.dbf
28 Full 5256322985 21-JUL-09 /u03/wis/tbs_indy_sge.dbf
29 Full 5256322985 21-JUL-09 /u03/wis/tbs_indy_trcmovestq_64m.dbf
30 Full 5256322985 21-JUL-09 /u03/wis/tbs_indy_wis_256k.dbf
31 Full 5256322985 21-JUL-09 /u03/wis/tbs_indy_wis_32m.dbf
32 Full 5256322985 21-JUL-09 /u03/wis/tbs_indy_wis_5m.dbf
33 Full 5256322985 21-JUL-09 /u03/wis/tools.dbf
34 Full 5256322985 21-JUL-09 /u03/wis/undotbs2.dbf
35 Full 5256322985 21-JUL-09 /u03/wis/users.dbf
36 Full 5256322985 21-JUL-09 /u03/wis/gkoscf_data.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
3 Full 6.05M DISK 00:00:00 21-JUL-09
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20090721T163226
Piece Name: /u03/wis/full_wis_692824194_4
Control File Included: Ckp SCN: 5256327637 Ckp time: 21-JUL-09
BS Key Size Device Type Elapsed Time Completion Time
4 368.53M DISK 00:00:00 23-JUL-09
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: BKP_WIS_FULL_ARCHIVE
Piece Name: /u03/wis/arch1_692988571.rman
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
1 1184 5256216977 18-JUL-09 5256333244 21-JUL-09
1 1185 5256333244 21-JUL-09 5256367481 22-JUL-09
1 1186 5256367481 22-JUL-09 5256424192 23-JUL-09
1 1187 5256424192 23-JUL-09 5669379585 23-JUL-09
1 1188 5669379585 23-JUL-09 5669379591 23-JUL-09
BS Key Size Device Type Elapsed Time Completion Time
5 55.83M DISK 00:00:00 24-JUL-09
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: BKP_WIS_FULL_ARCHIVE
Piece Name: /u03/wis/arch1_693056610_0ckkud32_1_1.rman
List of Archived Logs in backup set 5
Thrd Seq Low SCN Low Time Next SCN Next Time
1 1189 5669379591 23-JUL-09 5669418823 24-JUL-09
1 1190 5669418823 24-JUL-09 5669418831 24-JUL-09
RMAN> restore tablespace users ;
Starting restore at 27-JUL-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00035 to /u03/wis/users.dbf
channel ORA_DISK_1: reading from backup piece /u03/wis/full_wis_692814747_3
channel ORA_DISK_1: restored backup piece 1
piece handle=/u03/wis/full_wis_692814747_3 tag=TAG20090721T163226
channel ORA_DISK_1: restore complete, elapsed time: 00:05:26
Finished restore at 27-JUL-09
RMAN> recover tablespace users ;
Starting recover at 27-JUL-09
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 1184 is already on disk as file /u03/wis/arch_1_1184_675348192.log
archive log thread 1 sequence 1185 is already on disk as file /u03/wis/arch_1_1185_675348192.log
archive log thread 1 sequence 1186 is already on disk as file /u03/wis/arch_1_1186_675348192.log
archive log thread 1 sequence 1187 is already on disk as file /u03/wis/arch_1_1187_675348192.log
archive log thread 1 sequence 1188 is already on disk as file /u03/wis/arch_1_1188_675348192.log
archive log thread 1 sequence 1189 is already on disk as file /u03/wis/arch_1_1189_675348192.log
archive log thread 1 sequence 1190 is already on disk as file /u03/wis/arch_1_1190_675348192.log
archive log thread 1 sequence 1 is already on disk as file /u03/wis/arch_1_1_693067879.log
archive log filename=/u03/wis/arch_1_1184_675348192.log thread=1 sequence=1184
archive log filename=/u03/wis/arch_1_1185_675348192.log thread=1 sequence=1185
archive log filename=/u03/wis/arch_1_1186_675348192.log thread=1 sequence=1186
archive log filename=/u03/wis/arch_1_1187_675348192.log thread=1 sequence=1187
archive log filename=/u03/wis/arch_1_1188_675348192.log thread=1 sequence=1188
archive log filename=/u03/wis/arch_1_1189_675348192.log thread=1 sequence=1189
archive log filename=/u03/wis/arch_1_1190_675348192.log thread=1 sequence=1190
archive log filename=/u03/wis/arch_1_1191_675348192.log thread=1 sequence=1191
archive log filename=/u03/wis/arch_1_1192_675348192.log thread=1 sequence=1192
archive log filename=/u03/wis/arch_1_1193_675348192.log thread=1 sequence=1193
archive log filename=/u03/wis/arch_1_1194_675348192.log thread=1 sequence=1194
media recovery complete, elapsed time: 00:00:08
Finished recover at 27-JUL-09
RMAN> alter database open ;
database opened