基于时间不完全恢复之rman篇
SQL> select sysdate from dual;
SYSDATE
-------------------
2013-07-31 22:36:51
run{
sql 'alter session nls_date_format="yyyy-mm-dd hh24:mi:ss"'
set until time '2013-07-31 22:45:51'
restore database;
recover database;
alter database open resetlogs;
}
--命令要点,
虚拟机时间关了就不动了,还是7.31号的时间
restore database;
recover database until time '2013-07-31 22:46:51';
alter database open resetlogs;
SSH Secure Shell 3.2.9 (Build 283)
Copyright (c) 2000-2003 SSH Communications Security Corp - http://www.ssh.com/
This copy of SSH Secure Shell is a non-commercial version.
This version does not include PKI and PKCS #11 functionality.
Last login: Wed Jul 31 15:50:34 2013 from 192.168.1.100
[root@node1 ~]# su - oracle
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 31 22:30:03 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--确认
数据库已经处于归档状态下
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archivelog
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL> !clear
SQL> create user u1 identified by u1;
User created.
SQL> create user u2 identified by u2;
User created.
SQL> grant resource,connect to u1,u2;
Grant succeeded.
SQL> conn u1/u1
Connected.
SQL> create table cj(id number,name varchar2(50));
Table created.
SQL> insert into cj values(1,'cj');
1 row created.
SQL> insert into cj values (8,'cj888');
1 row created.
SQL> select sysdate from dual;
SYSDATE
---------
31-JUL-13
--设置时间格式,以便后面基于时间恢复时能识别到时间点的格式
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2013-07-31 22:36:51
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 ~]$ cd /u01/app/oracle/flash_recovery_area/MDNSS/
[oracle@node1 MDNSS]$ ll
total 16
drwxr-x--- 2 oracle oinstall 4096 Jul 31 22:41 archivelog
drwxr-x--- 3 oracle oinstall 4096 Jul 31 18:46 backupset
drwxr-x--- 2 oracle oinstall 4096 Jul 31 22:31 flashback
drwxr-x--- 2 oracle oinstall 4096 May 26 01:41 onlinelog
[oracle@node1 MDNSS]$ cd backupset/
[oracle@node1 backupset]$ ll
total 4
drwxr-x--- 2 oracle oinstall 4096 Jul 31 22:41 2013_07_31
[oracle@node1 backupset]$ date
Wed Jul 31 22:43:29 CST 2013
--设置日期环境
[oracle@node1 backupset]$ export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 31 22:38:12 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: MDNSS (DBID=3864238845)
--下面开始RMAN全库备份了,包括归档日志,之后删除INPUT在内存中的加载
RMAN> backup database plus archivelog delete input;
Starting backup at 31-JUL-13
current log archived
using target database control file instead of recovery cata