各位大侠,请指教,问题如下:
10g的数据库,RMAN方式,备份代码如下:
backup copies 2 database format='d:\ora_backup\ora_bak\%d_%s.dbf','d:\ora_backup\ora_bak_M\%d_%s.dbf';
sql 'alter system archive log current';每次每个文件夹里会出现两个备份的文件。备份后,我对数据库进行了一些操作,增加数据,删除数据,增加表,删除表。
然后恢复,按照我的理解,前面我备份后所做的操作应该都无效才对,比如我增加了数据,恢复后,数据应该没增加,我删除了数据,恢复后,数据应该回来了。
操作如下:先连接RMAN,关闭数据库。进入SQLPLUS,startup force mount。然后连接RMAN,执行如下语句:
run{
restore database;
recover database;
sql 'alter database open';
}过程中没有出现任何错误,正常完成之后,我登录数据库进行查看,发现没有我需要的结果,增加的数据没有丢失,删除的数据也没有恢复,增加的表还在,删除的表也没有回来。请问怎么回事啊?该怎么办?
各位大侠请指教啊,我初学ORACLE,请说的详细一些,谢谢啊!
10g的数据库,RMAN方式,备份代码如下:
backup copies 2 database format='d:\ora_backup\ora_bak\%d_%s.dbf','d:\ora_backup\ora_bak_M\%d_%s.dbf';
sql 'alter system archive log current';每次每个文件夹里会出现两个备份的文件。备份后,我对数据库进行了一些操作,增加数据,删除数据,增加表,删除表。
然后恢复,按照我的理解,前面我备份后所做的操作应该都无效才对,比如我增加了数据,恢复后,数据应该没增加,我删除了数据,恢复后,数据应该回来了。
操作如下:先连接RMAN,关闭数据库。进入SQLPLUS,startup force mount。然后连接RMAN,执行如下语句:
run{
restore database;
recover database;
sql 'alter database open';
}过程中没有出现任何错误,正常完成之后,我登录数据库进行查看,发现没有我需要的结果,增加的数据没有丢失,删除的数据也没有恢复,增加的表还在,删除的表也没有回来。请问怎么回事啊?该怎么办?
各位大侠请指教啊,我初学ORACLE,请说的详细一些,谢谢啊!
解决方案 »
- help:windows重装ie后后oracle服务消失
- 救命!为何修改参数文件后一点反应也没有?
- 请高手达人来帮我看一下,我这个触发器有什么问题,第一次写不懂啊.
- Oracle同一个用户名,是否可以设两个不同的密码,对应不同的权限?
- 請教關於一個select的問題,用於統計;
- Ora-12154:TNS:无法处理服务名,如何解决?
- asp连结oracle的问题,请教…
- oracle插入date类型
- oracle认证
- ORA-29540: 类 oracle/jpub/runtime/dbws/DbwsProxy 不存在
- 急需帮助:在WindowsXp Pro下安装Oracle11g无法创建数据库
- 超级菜鸟求教 oracle 11g 中的isqlplus启动?
---------- ------------------------------
1 aaaa
2 bbbb
3 ccccRMAN> backup database include current controlfile;Starting backup at 23-JUL-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
input datafile fno=00001 name=C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00015 name=C:\ORACLE\ORADATA\ORCL\UNDOTBS.DBF
input datafile fno=00005 name=C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
input datafile fno=00011 name=C:\ORACLE\ORADATA\ORCL\ORA_BACKUP.DBF
input datafile fno=00010 name=C:\ORACLE\ORADATA\ORCL\XDB01.DBF
input datafile fno=00006 name=C:\ORACLE\ORADATA\ORCL\INDX01.DBF
input datafile fno=00009 name=C:\ORACLE\ORADATA\AUT\USERS01.DBF
input datafile fno=00003 name=C:\ORACLE\ORADATA\ORCL\CWMLITE01.DBF
input datafile fno=00004 name=C:\ORACLE\ORADATA\ORCL\DRSYS01.DBF
input datafile fno=00007 name=C:\ORACLE\ORADATA\ORCL\ODM01.DBF
input datafile fno=00008 name=C:\ORACLE\ORADATA\ORCL\TOOLS01.DBF
channel ORA_DISK_1: starting piece 1 at 23-JUL-08
channel ORA_DISK_1: finished piece 1 at 23-JUL-08
piece handle=C:\ORACLE\ORA92\DATABASE\2OJM7631_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
Finished backup at 23-JUL-08Starting Control File and SPFILE Autobackup at 23-JUL-08
piece handle=C:\ORACLE\ORA92\DATABASE\C-1186183048-20080723-07 comment=NONE
Finished Control File and SPFILE Autobackup at 23-JUL-08RMAN> exit
Recovery Manager complete.C:\>set oracle_sid=orcl
C:\>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jul 23 14:16:43 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select * from test.t; IDX CNAME
---------- ------------------------------
1 aaaa
2 bbbb
3 cccc
SQL> delete from test.t;
3 rows deleted.
SQL> commit;
Commit complete.SQL> select * from test.t;
no rows selectedSQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
C:\>rman target /Recovery Manager: Release 9.2.0.1.0 - ProductionCopyright (c) 1995, 2002, Oracle Corporation. All rights reserved.connected to target database: ORCL (DBID=1186183048)RMAN> list backup;using target database controlfile instead of recovery catalogList of Backup Sets
===================BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
81 Full 660M DISK 00:01:06 23-JUL-08
BP Key: 81 Status: AVAILABLE Tag: TAG20080723T141457
Piece Name: C:\ORACLE\ORA92\DATABASE\2OJM7631_1_1
Controlfile Included: Ckp SCN: 2407868 Ckp time: 23-JUL-08
List of Datafiles in backup set 81
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2407869 23-JUL-08 C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
3 Full 2407869 23-JUL-08 C:\ORACLE\ORADATA\ORCL\CWMLITE01.DBF
4 Full 2407869 23-JUL-08 C:\ORACLE\ORADATA\ORCL\DRSYS01.DBF
5 Full 2407869 23-JUL-08 C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
6 Full 2407869 23-JUL-08 C:\ORACLE\ORADATA\ORCL\INDX01.DBF
7 Full 2407869 23-JUL-08 C:\ORACLE\ORADATA\ORCL\ODM01.DBF
8 Full 2407869 23-JUL-08 C:\ORACLE\ORADATA\ORCL\TOOLS01.DBF
9 Full 2407869 23-JUL-08 C:\ORACLE\ORADATA\AUT\USERS01.DBF
10 Full 2407869 23-JUL-08 C:\ORACLE\ORADATA\ORCL\XDB01.DBF
11 Full 2407869 23-JUL-08 C:\ORACLE\ORADATA\ORCL\ORA_BACKUP.DBF
15 Full 2407869 23-JUL-08 C:\ORACLE\ORADATA\ORCL\UNDOTBS.DBFBS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
82 Full 1M DISK 00:00:01 23-JUL-08
BP Key: 82 Status: AVAILABLE Tag:
Piece Name: C:\ORACLE\ORA92\DATABASE\C-1186183048-20080723-07
SPFILE Included: Modification time: 10-JUL-08
RMAN> shutdown immediate;database closed
database dismounted
Oracle instance shut downRMAN> startup mount;connected to target database (not started)
Oracle instance started
database mountedTotal System Global Area 135338868 bytesFixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytesRMAN> run{
2> allocate channel t1 type disk;
3> set until scn 2407869;
4> restore database;
5> recover database;
6> alter database open resetlogs;
7> release channel t1;
8> }allocated channel: t1
channel t1: sid=11 devtype=DISKexecuting command: SET until clauseStarting restore at 23-JUL-08channel t1: starting datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00003 to C:\ORACLE\ORADATA\ORCL\CWMLITE01.DBF
restoring datafile 00004 to C:\ORACLE\ORADATA\ORCL\DRSYS01.DBF
restoring datafile 00005 to C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
restoring datafile 00006 to C:\ORACLE\ORADATA\ORCL\INDX01.DBF
restoring datafile 00007 to C:\ORACLE\ORADATA\ORCL\ODM01.DBF
restoring datafile 00008 to C:\ORACLE\ORADATA\ORCL\TOOLS01.DBF
restoring datafile 00009 to C:\ORACLE\ORADATA\AUT\USERS01.DBF
restoring datafile 00010 to C:\ORACLE\ORADATA\ORCL\XDB01.DBF
restoring datafile 00011 to C:\ORACLE\ORADATA\ORCL\ORA_BACKUP.DBF
restoring datafile 00015 to C:\ORACLE\ORADATA\ORCL\UNDOTBS.DBF
channel t1: restored backup piece 1
piece handle=C:\ORACLE\ORA92\DATABASE\2OJM7631_1_1 tag=TAG20080723T141457 params=NULL
channel t1: restore complete
Finished restore at 23-JUL-08Starting recover at 23-JUL-08starting media recovery
media recovery completeFinished recover at 23-JUL-08database openedreleased channel: t1RMAN> exit
Recovery Manager complete.C:\>sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jul 23 14:23:48 2008Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - ProductionSQL> select * from test.t; IDX CNAME
---------- ------------------------------
1 aaaa
2 bbbb
3 cccc
有一本《精通ORACLE.10G备份与恢复.pdf〉〉写得很好,有空下载读几遍先。
你前面的做法是完全恢复,就是指数据库不会丢失任何更改,当然包括你新增和删除更改操作。
现在你增加了数据,然后想恢复后丢掉这些增加的数据。就只有做不完全恢复,恢复到你新增数据之前的状态。
最简单的就是基于时间点的不完全恢复:
run{
sql "alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''";
set until time '2008-08-22 10:20:00';
restore database;
recover database;
alter database open resetlogs;
}如果想不要新增的数据,就把until time设成你新增数据前的时间点。
===================
还原数据库后,你又做了完全恢复,应用了备份之后的全部日志。recovery until 指定时间点