备份与恢复:用rman方式基于时间的不完全恢复

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 10 10:28:39 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> create table test (a int);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Paul Yi>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: PUBTEST (DBID=796553641)
RMAN> run
2> {allocate channel c1 type disk;
3> backup full tag 'dbfull' format 'd:\backup\full%U_%s_%p' database
4> include current controlfile;
5> sql 'alter system archive log current';
6> release channel c1;
7> }
using target database controlfile instead of recovery catalog
allocated channel: c1
channel c1: sid=19 devtype=DISK
Starting backup at 10-APR-08
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=D:\ORACLE\ORADATA\PUBTEST\SYSTEM01.DBF
input datafile fno=00004 name=D:\ORACLE\ORADATA\PUBTEST\TOOLS01.DBF
input datafile fno=00002 name=D:\ORACLE\ORADATA\PUBTEST\UNDOTBS01.DBF
input datafile fno=00003 name=D:\ORACLE\ORADATA\PUBTEST\INDX01.DBF
input datafile fno=00005 name=D:\ORACLE\ORADATA\PUBTEST\USERS01.DBF
channel c1: starting piece 1 at 10-APR-08
channel c1: finished piece 1 at 10-APR-08
piece handle=D:\BACKUP\FULL02JDF9CK_1_1_2_1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:47
Finished backup at 10-APR-08
sql statement: alter system archive log current
released channel: c1
RMAN> exit

Recovery Manager complete.
C:\Documents and Settings\Paul Yi>exit
SQL> insert into test values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
A
----------
1
2
SQL> alter system switch logfile;
System altered.
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
-------------------
2008-04-10 10:36:14 --恢复基于这个时间点之前的
SQL> drop table test;
Table dropped.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 101785252 bytes
Fixed Size 454308 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
C:\Documents and Settings\Paul Yi>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: PUBTEST (DBID=796553641)
RMAN> run{
2> allocate channel c1 type disk;
3> restore database;
4> recover database until time '2008-04-10 10:36:14';
5> sql 'alter database open resetlogs';
6> release channel c1;
7> }
using target database controlfile instead of recovery catalog
allocated channel: c1
channel c1: sid=11 devtype=DISK
Starting restore at 10-APR-08
datafile 4 not processed because file is read-only
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\ORADATA\PUBTEST\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\PUBTEST\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\ORADATA\PUBTEST\INDX01.DBF
restoring datafile 00005 to D:\ORACLE\ORADATA\PUBTEST\USERS01.DBF
channel c1: restored backup piece 1
piece handle=D:\BACKUP\FULL02JDF9CK_1_1_2_1 tag=DBFULL params=NULL
channel c1: restore complete
Finished restore at 10-APR-08
Starting recover at 10-APR-08
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/10/2008 10:41:56
ORA-01861: literal does not match format string
RMAN> exit

Recovery Manager complete.
C:\Documents and Settings\Paul Yi>set nls_date_format='yyyy-mm-dd hh24:mi:ss';
C:\Documents and Settings\Paul Yi>set nls_date_format='yyyy-mm-dd hh24:mi:ss'
C:\Documents and Settings\Paul Yi>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-00604: error occurred at recursive SQL level 1
ORA-02248: invalid option for ALTER SESSION
C:\Documents and Settings\Paul Yi>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-00604: error occurred at recursive SQL level 1
ORA-02248: invalid option for ALTER SESSION
C:\Documents and Settings\Paul Yi>set nls_date_format=yyyy-mm-dd hh24:mi:ss
C:\Documents and Settings\Paul Yi>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: PUBTEST (DBID=796553641)
RMAN> run{
2> allocate channel c1 type disk;
3> restore database;
4> recover database until time '2008-04-10 10:36:14';
5> sql 'alter database open resetlogs';
6> release channel c1;
7> }
using target database controlfile instead of recovery catalog
allocated channel: c1
channel c1: sid=9 devtype=DISK
Starting restore at 2008-04-10 10:44:30
datafile 4 not processed because file is read-only
skipping datafile 1; already restored to file D:\ORACLE\ORADATA\PUBTEST\SYSTEM0
.DBF
skipping datafile 2; already restored to file D:\ORACLE\ORADATA\PUBTEST\UNDOTBS
1.DBF
skipping datafile 3; already restored to file D:\ORACLE\ORADATA\PUBTEST\INDX01.
BF
skipping datafile 5; already restored to file D:\ORACLE\ORADATA\PUBTEST\USERS01
DBF
restore not done; all files readonly, offline, or already restored
Finished restore at 2008-04-10 10:44:30
Starting recover at 2008-04-10 10:44:30
datafile 4 not processed because file is read-only
starting media recovery
archive log thread 1 sequence 2 is already on disk as file D:\ORACLE\ORA92\RDBM
\ARC00002.001
archive log thread 1 sequence 3 is already on disk as file D:\ORACLE\ORA92\RDBM
\ARC00003.001
archive log thread 1 sequence 4 is already on disk as file D:\ORACLE\ORA92\RDBM
\ARC00004.001
archive log filename=D:\ORACLE\ORA92\RDBMS\ARC00002.001 thread=1 sequence=2
media recovery complete
Finished recover at 2008-04-10 10:44:32
sql statement: alter database open resetlogs
released channel: c1
RMAN> exit

Recovery Manager complete.
C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 10 10:44:52 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> select * from test;
A
----------
1
2
SQL>

基于时间点的时间格式也可这样写　 run 　　 { 　　 set until time "to_date('2008042115:00:00','yyyymmdd hh24:mi:ss')" 　　 restore database; 　　 recover database; 　　 alter database open resetlogs; 　　 }