基于时间不完全恢复之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 catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=24 RECID=1 STAMP=816470585
input archived log thread=1 sequence=25 RECID=2 STAMP=816470822
input archived log thread=1 sequence=26 RECID=3 STAMP=816486445
input archived log thread=1 sequence=27 RECID=4 STAMP=822198990
input archived log thread=1 sequence=28 RECID=7 STAMP=822208660
input archived log thread=1 sequence=29 RECID=11 STAMP=822209838
input archived log thread=1 sequence=30 RECID=9 STAMP=822209838
input archived log thread=1 sequence=31 RECID=10 STAMP=822209838
input archived log thread=1 sequence=32 RECID=12 STAMP=822209838
channel ORA_DISK_1: starting piece 1 at 31-JUL-13
channel ORA_DISK_1: finished piece 1 at 31-JUL-13
piece handle=/u01/app/oracle/flash_recovery_area/MDNSS/backupset/2013_07_31/o1_mf_annnn_TAG20130731T223852_8zl8cxq2_.bkp tag=TAG20130731T223852 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/MDNSS/archivelog/2013_05_26/o1_mf_1_24_8t435s00_.arc RECID=1 STAMP=816470585
archived log file name=/home/oracle/archivelog/1_25_816399680.dbf RECID=2 STAMP=816470822
archived log file name=/home/oracle/archivelog/1_26_816399680.dbf RECID=3 STAMP=816486445
archived log file name=/home/oracle/archivelog/1_27_816399680.dbf RECID=4 STAMP=822198990
archived log file name=/home/oracle/archivelog/1_28_816399680.dbf RECID=7 STAMP=822208660
archived log file name=/home/oracle/archivelog/1_29_816399680.dbf RECID=11 STAMP=822209838
archived log file name=/home/oracle/archivelog/1_30_816399680.dbf RECID=9 STAMP=822209838
archived log file name=/home/oracle/archivelog/1_31_816399680.dbf RECID=10 STAMP=822209838
archived log file name=/home/oracle/archivelog/1_32_816399680.dbf RECID=12 STAMP=822209838
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=13 STAMP=822223538
input archived log thread=1 sequence=2 RECID=14 STAMP=822224196
input archived log thread=1 sequence=7 RECID=17 STAMP=822243364
input archived log thread=1 sequence=8 RECID=18 STAMP=822243364
input archived log thread=1 sequence=9 RECID=15 STAMP=822243364
input archived log thread=1 sequence=10 RECID=16 STAMP=822243364
channel ORA_DISK_1: starting piece 1 at 31-JUL-13
channel ORA_DISK_1: finished piece 1 at 31-JUL-13
piece handle=/u01/app/oracle/flash_recovery_area/MDNSS/backupset/2013_07_31/o1_mf_annnn_TAG20130731T223852_8zl8dgfj_.bkp tag=TAG20130731T223852 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/archivelog/1_1_822209838.dbf RECID=13 STAMP=822223
