对部分表空间作不完全恢复

ft，前两天因为程序的bug，有个服务人员把客户的资料给删了，3天后才知道。然后他们领导找到我，让我搞定。
整个库有170g，他们提供了一个机器只有50g的空间，做全库的不完全恢复是不可能了。后来大师在metalink上找到了这么一个方法，
可以只恢复被删除记录的表所在的表空间，偶搞了一把，比TSPITR 好用多了。

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=223543.1

在恢复用的机器上配置好与源数据库相同的目录，环境变量设好ORACLE_SID和ORACLE_HOME。
拷贝主库的control file过来，将存储rman备份的nsf直接mount过来，也使用相同的目录，这样方便restore。

run
{
--强制所有的数据文件offline，只是为了安全的目的。
sql "alter database mount clone database";

--为需要恢复的数据文件指定新的路径，也是安全考虑，防止在主库上恢复的时候，覆盖主库文件。
--因为这里是在另外一个库上作恢复，所以可以设成和主库一样。
--注意，undo和system是必须要恢复的，呵呵
set newname for datafile 2 to '/disk2/oradata/crmtemp/undotbs01.dbf';
set newname for datafile 69 to '/disk2/oradata/crmtemp/undotbs03.dbf';
set newname for datafile 79 to '/disk4/oradata/crmtemp/undotbs04.dbf';
set newname for datafile 93 to '/data2/oradata/crmtemp/undotbs05.dbf';
set newname for datafile 1 to '/disk1/oradata/crmtemp/system01.dbf';
set newname for datafile 45 to '/disk1/oradata/crmtemp/appdata01.dbf';
set newname for datafile 46 to '/disk1/oradata/crmtemp/appdata02.dbf';

--restore 表空间
restore tablespace system,UNDOTBS1,APPDATA1M;

--其他的数据文件offline drop
sql "alter database datafile 3,4,5,6,7,8,13,14,15,16,18,19,20,23,25,26,27,28,29,30,31,33,35,134 offline drop";

--将newname的变化应用的controlfile
switch datafile all;

--把需要恢复的数据文件online
sql "alter database datafile 2, 69, 79, 93, 1, 45, 46 online";

--不完全恢复，关键就是这里使用了skip tablespace,把不需要恢复的ts跳过
recover database until TIME "TO_DATE('2005-6-7 15:00:00', 'yyyy-mm-dd hh24:mi:ss')"
skip tablespace TEMP,
INDX,
TOOLS,
ADMIN_DATA1M,
TPCRM,
...;
}

恢复完后，需要重建controlfile，因为目前的controlfile是clone状态的，需要
alter database backup controlfile to trace;
然后提取创建脚本。因为没有online redo，所以使用resetlog的那种方式。

创建完毕后：
alter database open resetlogs;

数据库打开，马上把需要的表exp出来：
exp userid=zeus/zeus file=/home/oracle/xiaojinyu.dmp tables=(CS_COL_CONTENT,CS_COLUMNS,SAMPLE,SAMPLE_GROUP) rows=Y

然后imp到开发库上，交给应用处理。

另外，大家有时间可以测试一下，能否直接在sqlplus 里面直接作
recover database until TIME "TO_DATE('2005-6-7 15:00:00', 'yyyy-mm-dd hh24:mi:ss')"
skip tablespace TEMP.....;

真的没时间搞了。

这和RAMN来进行TSPITR有些类似,利用RMAN本来提供TSPITR方法也很容易实现你的这个要求.

利用RMAN进行TSPITR步骤简单,也非常清晰.命令的写法也较为简单. 而手工进行TSPITR步骤实在太复杂.

作了个试验,不是RMAN的操作

SQL> conn hr/hr@backup
已连接。
SQL> set time on

13:52:34 SQL> select count(*) from operator;

COUNT(*)
----------
1293

已用时间: 00: 00: 00.00
13:53:14 SQL> delete operator;

已删除1293行。

已用时间: 00: 00: 00.00
13:53:19 SQL> commit;

提交完成。

已用时间: 00: 00: 00.00
13:53:21 SQL> conn scott/tiger@backup;
已连接。
13:53:48 SQL> delete emp;

已删除12行。

已用时间: 00: 00: 00.00
13:53:54 SQL> delete dept;

已删除4行。

已用时间: 00: 00: 00.00
13:53:58 SQL> commit;
13:54:00 SQL> conn hr/hr@backup;
已连接。
13:54:10 SQL> alter system switch logfile;

系统已更改。

已用时间: 00: 00: 00.01
13:54:16 SQL> /

系统已更改。

已用时间: 00: 00: 00.01
13:54:17 SQL> /

系统已更改。

已用时间: 00: 00: 05.02
13:54:23 SQL> /

系统已更改。

已用时间: 00: 00: 00.01
13:54:25 SQL> alter database backup controlfile to 'e:ct.ctl';

数据库已更改。

已用时间: 00: 00: 00.05
13:58:18 SQL>

+++++++++++++++++++++++++++++++++++++++++++++++++
copy需要的三个数据文件，和备份的控制文件到本机进行恢复。

14:00:53 SQL> startup nomount;
ORACLE 例程已经启动。

Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
14:01:01 SQL> alter database mount;
alter database mount
*
ERROR 位于第 1 行:
ORA-01991: ???????'C:oracleora92DATABASEPWDbackup.ORA'

14:01:12 SQL> alter database rename file 'H:ORADATAU2SYSTEM01.DBF'
14:01:44 2 to 'C:oraclebackORADATAu2system01.dbf';

数据库已更改。

14:01:54 SQL> alter database rename file 'H:ORADATAU2undotbs01.DBF'
14:02:01 2 to 'C:oraclebackORADATAu2undotbs01.dbf';

数据库已更改。

14:02:07 SQL> alter database rename file 'H:ORADATAU2user_data01.DBF'
14:02:19 2 to 'C:oraclebackORADATAu2user_data01.dbf';

数据库已更改。

14:02:26 SQL> alter database datafile 'H:ORADATAU2USERS01.DBF' offline drop;

数据库已更改。

14:02:34 SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

C:ORACLEBACKORADATAU2SYSTEM01.DBF
C:ORACLEBACKORADATAU2UNDOTBS01.DBF
H:ORADATAU2USERS01.DBF
C:ORACLEBACKORADATAU2USER_DATA01.DBF

14:02:42 SQL> recover database until time '2005-08-10 12:25:13' skip tablespace
temp,users;
ORA-00905: ?????

14:03:05 SQL> alter session set nls_language='english';

Session altered.

14:03:21 SQL> recover database until time '2005-08-10 12:25:13' skip tablespace
temp,users;
ORA-00905: missing keyword

14:03:24 SQL> recover database until time '2005-08-10 12:25:13';
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

14:03:31 SQL> recover database until time '2005-08-10 13:52:33';
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

14:05:24 SQL> recover database until time '2005-08-10 13:52:33' using backup con
trolfile;
ORA-00279: change 52768 generated at 08/10/2005 11:09:48 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_124.DBF
ORA-00280: change 52768 for thread 1 is in sequence #124

14:05:48 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 52997 generated at 08/10/2005 11:15:46 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_125.DBF
ORA-00280: change 52997 for thread 1 is in sequence #125
ORA-00278: log file 'C:ORACLEBACKORADATAARC1_124.DBF' no longer needed for
this recovery

14:06:02 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 53050 generated at 08/10/2005 11:15:47 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_126.DBF
ORA-00280: change 53050 for thread 1 is in sequence #126
ORA-00278: log file 'C:ORACLEBACKORADATAARC1_125.DBF' no longer needed for
this recovery

14:06:04 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 53113 generated at 08/10/2005 11:15:48 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_127.DBF
ORA-00280: change 53113 for thread 1 is in sequence #127
ORA-00278: log file 'C:ORACLEBACKORADATAARC1_126.DBF' no longer needed for
this recovery

14:06:06 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 53169 generated at 08/10/2005 11:15:50 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_128.DBF
ORA-00280: change 53169 for thread 1 is in sequence #128
ORA-00278: log file 'C:ORACLEBACKORADATAARC1_127.DBF' no longer needed for
this recovery

14:06:07 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 53191 generated at 08/10/2005 11:16:33 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_129.DBF
ORA-00280: change 53191 for thread 1 is in sequence #129
ORA-00278: log file 'C:ORACLEBACKORADATAARC1_128.DBF' no longer needed for
this recovery

14:06:08 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 53195 generated at 08/10/2005 11:16:40 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_130.DBF
ORA-00280: change 53195 for thread 1 is in sequence #130
ORA-00278: log file 'C:ORACLEBACKORADATAARC1_129.DBF' no longer needed for
this recovery

14:06:09 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 54618 generated at 08/10/2005 12:26:57 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_131.DBF
ORA-00280: change 54618 for thread 1 is in sequence #131
ORA-00278: log file 'C:ORACLEBACKORADATAARC1_130.DBF' no longer needed for
this recovery

14:06:10 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 54620 generated at 08/10/2005 12:26:58 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_132.DBF
ORA-00280: change 54620 for thread 1 is in sequence #132
ORA-00278: log file 'C:ORACLEBACKORADATAARC1_131.DBF' no longer needed for
this recovery

14:06:11 Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.
14:06:12 SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log 'H:ORADATAU3REDO01.LOG'
ORA-27040: skgfrcre: create error, unable to create file
OSD-04002: N^7(4r?*ND6!#

14:06:23 SQL> alter database rename file 'H:ORADATAU3REDO01.LOG'
14:06:32 2 to 'C:oraclebackORADATAu3redo01.log';

Database altered.

14:06:39 SQL> alter database rename file 'H:ORADATAU3REDO02.LOG'
14:06:45 2 to 'C:oraclebackORADATAu3redo02.log';

Database altered.

14:06:50 SQL> alter database rename file 'H:ORADATAU3REDO03.LOG'
14:06:55 2 to 'C:oraclebackORADATAu3redo03.log';

Database altered.

14:07:01 SQL> alter database open resetlogs;

Database altered.

14:07:13 SQL> conn hr/hr
已连接。
14:07:32 SQL> select count(*) from ias_parameter_mst;
select count(*) from ias_parameter_mst
*
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在

14:07:34 SQL> select count(*) from operator;

COUNT(*)
----------
1293

14:07:51 SQL> conn scott/tiger
已连接。
14:08:02 SQL> select count(*) from dept;
select count(*) from dept
*
ERROR 位于第 1 行:
ORA-00376: 此时无法读取文件 3
ORA-01110: 数据文件 3: 'H:ORADATAU2USERS01.DBF'

14:08:34 SQL>

c:>dir C:oraclebackORADATAu2
驱动器 C 中的卷是 S3A1611D001
卷的序列号是 6833-4937

C:oraclebackORADATAu2 的目录

2005-08-10 13:55 .
2005-08-10 13:55 ..
2005-08-10 14:07 262,152,192 SYSTEM01.DBF
2005-08-10 14:07 20,979,712 UNDOTBS01.DBF
2005-08-10 14:07 20,979,712 USER_DATA01.DBF
3 个文件 304,111,616 字节
2 个目录 22,824,153,088 可用字节

c:>

路径怎么斜杠 没有了....

Bug，
反斜杠不能显示。''
正斜杠可以显示 。'/'

blog好久没更新了。

a

gawehreee

erabearba

this

goughdydt

lesbiche mpeg hardcore cazzi eretti spagnoletta nel sesso taglio cameriera merda biancheria sexy bukkake bukkake frecuensia latina fantasie erotiche femminili leccare gli stivali video di puttane

bello scopata collettiva anale hombres masturbandose ditalini in figa bagnata di sborra cavallo che trombata donna sesso troia fotos tetarelli anale

gay boy chat carlo bo small group dental insurance gay and lesbian chat quadro diritto privato bocchino mulinello a bobina rotante web cash advance decoder gay joe chat california dental insurance

[url= ] [/url] [url= ] [/url] [url= ] [/url] [url= ] [/url] [url= ] [/url] [url= ] [/url] [url= ] [/url]

[url=][/url]

[url=][/url]