ITPub博客

首页 > Linux操作系统 > Linux操作系统 > recover database until time

recover database until time

原创 Linux操作系统 作者:aaqwsh 时间:2011-04-19 22:16:12 0 删除 编辑
1  建立测试表
 
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 19 21:46:40 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from test;
         I
----------
         1
         3
SQL>
SQL> exit
 
2    全备份数据库

RMAN> backup database;
Starting backup at 19-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=142 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/data/ora10g/RACDBSTD/system.259.727735501
input datafile fno=00005 name=/data/ora10g/RACDBSTD/users.264.727735605
channel ORA_DISK_1: starting piece 1 at 19-APR-11
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00002 name=/data/ora10g/RACDBSTD/undotbs1.260.727735547
input datafile fno=00003 name=/data/ora10g/RACDBSTD/sysaux.261.727735559
input datafile fno=00006 name=/data/ora10g/RACDBSTD/TEST01.DBF
channel ORA_DISK_2: starting piece 1 at 19-APR-11
channel ORA_DISK_1: finished piece 1 at 19-APR-11
piece handle=/data/ora10g/0rma6r2b_1_1 tag=TAG20110419T215315 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 19-APR-11
channel ORA_DISK_2: finished piece 1 at 19-APR-11
piece handle=/data/ora10g/0sma6r2b_1_1 tag=TAG20110419T215315 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:08
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
channel ORA_DISK_1: finished piece 1 at 19-APR-11
piece handle=/data/ora10g/0tma6r4d_1_1 tag=TAG20110419T215315 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
including current SPFILE in backupset
channel ORA_DISK_2: starting piece 1 at 19-APR-11
channel ORA_DISK_2: finished piece 1 at 19-APR-11
piece handle=/data/ora10g/0uma6r4f_1_1 tag=TAG20110419T215315 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:02
Finished backup at 19-APR-11
RMAN> exit
 
3  模拟操作,最后drop表test
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 19 21:54:55 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from test;
         I
----------
         1
         3
SQL> insert into test values(666);
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> insert into test values(9990;
insert into test values(9990
                           *
ERROR at line 1:
ORA-00917: missing comma

SQL> insert into test values(999);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2011-04-19 21:56:27
SQL>
SQL> drop table test purge;
Table dropped.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
 
4 不完全恢复,恢复test表的数据
RMAN> restore controlfile from '/data/ora10g/0tma6r4d_1_1';
Starting restore at 19-APR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/data/ora10g/RACDBSTD/control01.ctl
output filename=/data/ora10g/RACDBSTD/control02.ctl
output filename=/data/ora10g/RACDBSTD/control03.ctl
Finished restore at 19-APR-11
RMAN> shutdown immediate
Oracle instance shut down
RMAN> exit

Recovery Manager complete.
[oracle@node1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Apr 19 22:04:02 2011
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     167772160 bytes
Fixed Size                     1218316 bytes
Variable Size                 62916852 bytes
Database Buffers             100663296 bytes
Redo Buffers                   2973696 bytes
RMAN> restore database;
Starting restore at 19-APR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=155 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /data/ora10g/RACDBSTD/system.259.727735501
restoring datafile 00005 to /data/ora10g/RACDBSTD/users.264.727735605
channel ORA_DISK_1: reading from backup piece /data/ora10g/0rma6r2b_1_1
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /data/ora10g/RACDBSTD/undotbs1.260.727735547
restoring datafile 00003 to /data/ora10g/RACDBSTD/sysaux.261.727735559
restoring datafile 00006 to /data/ora10g/RACDBSTD/TEST01.DBF
channel ORA_DISK_2: reading from backup piece /data/ora10g/0sma6r2b_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/data/ora10g/0rma6r2b_1_1 tag=TAG20110419T215315
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
channel ORA_DISK_2: restored backup piece 1
piece handle=/data/ora10g/0sma6r2b_1_1 tag=TAG20110419T215315
channel ORA_DISK_2: restore complete, elapsed time: 00:01:06
Finished restore at 19-APR-11
RMAN> run{
2> sql 'alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"';
3> set until time  '2011-04-19 21:56:27';
4> recover database;
5> }
sql statement: alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"
executing command: SET until clause
Starting recover at 19-APR-11
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
archive log thread 1 sequence 14 is already on disk as file /data/ora10g/RACDBSTD/group_2.258.727735493
archive log thread 1 sequence 15 is already on disk as file /data/ora10g/RACDBSTD/group_1.257.727735489
archive log filename=/data/ora10g/RACDBSTD/arch/1_12_745800569.dbf thread=1 sequence=12
archive log filename=/data/ora10g/RACDBSTD/arch/1_13_745800569.dbf thread=1 sequence=13
archive log filename=/data/ora10g/RACDBSTD/group_2.258.727735493 thread=1 sequence=14
archive log filename=/data/ora10g/RACDBSTD/group_1.257.727735489 thread=1 sequence=15
media recovery complete, elapsed time: 00:00:03
Finished recover at 19-APR-11
RMAN> alter database open RESETLOGS;
database opened
 
5  验证数据,可expdp等导入
With the Partitioning, OLAP and Data Mining options
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 19 22:12:09 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from test;
         I
----------
         1
         3
       666
       666
       999

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/758322/viewspace-692919/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-24

  • 博文量
    132
  • 访问量
    262228