归档模式已启用
SQL> ARCHIVE LOG LIST;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination G:\RMANTEST\
Oldest online log sequence 77
Next log sequence to archive 79
Current log sequence 79RMAN配置RMAN> SHOW ALL;RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'G:\RMANTEST\%F'
;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\ORA92\DATABASE\SNCFMPOS04.ORA'
; # default
控制文件自动备份也打开了;
归档模式下
先给数据库做了一下全备RMAN> BACKUP DATABASE FORMAT'G:\RMANTEST\full_%d_%T_%s'PLUS ARCHIVELOG FORMAT'AR
C_%d_%T_%s'DELETE ALL INPUT;
Starting backup at 29-SEP-11
current log archived
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=15 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=76 recid=1 stamp=763126145
channel ORA_DISK_1: starting piece 1 at 29-SEP-11
channel ORA_DISK_1: finished piece 1 at 29-SEP-11
piece handle=D:\ORACLE\ORA92\DATABASE\ARC_POS_8004_20110929_2 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: deleting archive log(s)
archive log filename=G:\RMANTEST\ARC_OCP_00076_001.ARC recid=1 stamp=763126145
Finished backup at 29-SEP-11Starting backup at 29-SEP-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=E:\TCCS\MPOS04\DATA\DATA01.DBF
input datafile fno=00004 name=E:\TCCS\MPOS04\DATA\DATA02.DBF
input datafile fno=00005 name=E:\TCCS\MPOS04\DATA\DATA03.DBF
input datafile fno=00006 name=E:\TCCS\MPOS04\DATA\DATA04.DBF
input datafile fno=00007 name=E:\TCCS\MPOS04\DATA\DATA05.DBF
input datafile fno=00008 name=E:\TCCS\MPOS04\INDX\INDX01.DBF
input datafile fno=00009 name=E:\TCCS\MPOS04\INDX\INDX02.DBF
input datafile fno=00010 name=E:\TCCS\MPOS04\INDX\INDX03.DBF
input datafile fno=00011 name=E:\TCCS\MPOS04\INDX\INDX04.DBF
input datafile fno=00012 name=E:\TCCS\MPOS04\INDX\INDX05.DBF
input datafile fno=00002 name=E:\TCCS\MPOS04\SYST\RBSG01.DBF
input datafile fno=00001 name=E:\TCCS\MPOS04\SYST\SYSTEM01.DBF
input datafile fno=00013 name=E:\TCCS\MPOS04\SYST\TOOLS01.DBF
channel ORA_DISK_1: starting piece 1 at 29-SEP-11
channel ORA_DISK_1: finished piece 1 at 29-SEP-11
piece handle=G:\RMANTEST\FULL_POS_8004_20110929_3 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:16:57
Finished backup at 29-SEP-11Starting backup at 29-SEP-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=77 recid=2 stamp=763127172
channel ORA_DISK_1: starting piece 1 at 29-SEP-11
channel ORA_DISK_1: finished piece 1 at 29-SEP-11
piece handle=D:\ORACLE\ORA92\DATABASE\ARC_POS_8004_20110929_4 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archive log(s)
archive log filename=G:\RMANTEST\ARC_OCP_00077_001.ARC recid=2 stamp=763127172
Finished backup at 29-SEP-11Starting Control File and SPFILE Autobackup at 29-SEP-11
piece handle=G:\RMANTEST\C-3755680630-20110929-01 comment=NONE
Finished Control File and SPFILE Autobackup at 29-SEP-11创建表SQL> CREATE TABLE TMEP1 AS SELECT * FROM TEMP;Table created.向表插入数据SQL> SELECT * FROM TMEP1; ID IDNAME
---------- --------------------
1 1
1 1
1 1
1 1
1 561
1 5456
1 123456
1 5487
1 5487
1 123456
1 545611 rows selected.
删除表
SQL>drop table tmep1;
SQL>commit;
之后该怎么操作呢?
SQL> ARCHIVE LOG LIST;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination G:\RMANTEST\
Oldest online log sequence 77
Next log sequence to archive 79
Current log sequence 79RMAN配置RMAN> SHOW ALL;RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'G:\RMANTEST\%F'
;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\ORA92\DATABASE\SNCFMPOS04.ORA'
; # default
控制文件自动备份也打开了;
归档模式下
先给数据库做了一下全备RMAN> BACKUP DATABASE FORMAT'G:\RMANTEST\full_%d_%T_%s'PLUS ARCHIVELOG FORMAT'AR
C_%d_%T_%s'DELETE ALL INPUT;
Starting backup at 29-SEP-11
current log archived
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=15 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=76 recid=1 stamp=763126145
channel ORA_DISK_1: starting piece 1 at 29-SEP-11
channel ORA_DISK_1: finished piece 1 at 29-SEP-11
piece handle=D:\ORACLE\ORA92\DATABASE\ARC_POS_8004_20110929_2 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: deleting archive log(s)
archive log filename=G:\RMANTEST\ARC_OCP_00076_001.ARC recid=1 stamp=763126145
Finished backup at 29-SEP-11Starting backup at 29-SEP-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=E:\TCCS\MPOS04\DATA\DATA01.DBF
input datafile fno=00004 name=E:\TCCS\MPOS04\DATA\DATA02.DBF
input datafile fno=00005 name=E:\TCCS\MPOS04\DATA\DATA03.DBF
input datafile fno=00006 name=E:\TCCS\MPOS04\DATA\DATA04.DBF
input datafile fno=00007 name=E:\TCCS\MPOS04\DATA\DATA05.DBF
input datafile fno=00008 name=E:\TCCS\MPOS04\INDX\INDX01.DBF
input datafile fno=00009 name=E:\TCCS\MPOS04\INDX\INDX02.DBF
input datafile fno=00010 name=E:\TCCS\MPOS04\INDX\INDX03.DBF
input datafile fno=00011 name=E:\TCCS\MPOS04\INDX\INDX04.DBF
input datafile fno=00012 name=E:\TCCS\MPOS04\INDX\INDX05.DBF
input datafile fno=00002 name=E:\TCCS\MPOS04\SYST\RBSG01.DBF
input datafile fno=00001 name=E:\TCCS\MPOS04\SYST\SYSTEM01.DBF
input datafile fno=00013 name=E:\TCCS\MPOS04\SYST\TOOLS01.DBF
channel ORA_DISK_1: starting piece 1 at 29-SEP-11
channel ORA_DISK_1: finished piece 1 at 29-SEP-11
piece handle=G:\RMANTEST\FULL_POS_8004_20110929_3 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:16:57
Finished backup at 29-SEP-11Starting backup at 29-SEP-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=77 recid=2 stamp=763127172
channel ORA_DISK_1: starting piece 1 at 29-SEP-11
channel ORA_DISK_1: finished piece 1 at 29-SEP-11
piece handle=D:\ORACLE\ORA92\DATABASE\ARC_POS_8004_20110929_4 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archive log(s)
archive log filename=G:\RMANTEST\ARC_OCP_00077_001.ARC recid=2 stamp=763127172
Finished backup at 29-SEP-11Starting Control File and SPFILE Autobackup at 29-SEP-11
piece handle=G:\RMANTEST\C-3755680630-20110929-01 comment=NONE
Finished Control File and SPFILE Autobackup at 29-SEP-11创建表SQL> CREATE TABLE TMEP1 AS SELECT * FROM TEMP;Table created.向表插入数据SQL> SELECT * FROM TMEP1; ID IDNAME
---------- --------------------
1 1
1 1
1 1
1 1
1 561
1 5456
1 123456
1 5487
1 5487
1 123456
1 545611 rows selected.
删除表
SQL>drop table tmep1;
SQL>commit;
之后该怎么操作呢?
flashback table TMEP1 to before drop;
但我想知道是的RMAN 该如何操作;
sql "alter session set nls_date_format=''yyyy-mm-dd:hh24:mi:ss''";
run {
set until time '...'; -- drop之前的时间,格式:2011-09-30:10:50:00
restore database;
recover database;
}
alter database open resetlogs;
RMAN 备份与恢复 实例
http://blog.csdn.net/tianlesoftware/article/details/4699320
对3楼补充一下,RMAN 完全恢复和不完全恢复都是可以的。 一般对于生产系统的数据,如果有数据误删除,并在在flashback 不可用的情况下,可以用RMAN 来恢复, 方法是将备份恢复到其他的服务器上,然后找到需要的数据,在insert 到源库。 这样, 可以不影响现有系统的运行。