基于时间不完全恢复之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=822223538
archived log file name=/home/oracle/archivelog/1_2_822209838.dbf RECID=14 STAMP=822224196
archived log file name=/home/oracle/archivelog/1_7_822209838.dbf RECID=17 STAMP=822243364
archived log file name=/home/oracle/archivelog/1_8_822209838.dbf RECID=18 STAMP=822243364
archived log file name=/home/oracle/archivelog/1_9_822209838.dbf RECID=15 STAMP=822243364
archived log file name=/home/oracle/archivelog/1_10_822209838.dbf RECID=16 STAMP=822243364
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=19 STAMP=822249980
input archived log thread=1 sequence=2 RECID=20 STAMP=822263931
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_8zl8dkt6_.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_822243364.dbf RECID=19 STAMP=822249980
archived log file name=/home/oracle/archivelog/1_2_822243364.dbf RECID=20 STAMP=822263931
Finished backup at 31-JUL-13

Starting backup at 31-JUL-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/mdnss/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/mdnss/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/mdnss/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/mdnss/users01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/mdnss/t2a.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/mdnss/jf_data02.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/mdnss/jf_data01.dbf
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_nnndf_TAG20130731T223917_8zl8dpg6_.bkp tag=TAG20130731T223917 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:38
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
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_ncsnf_TAG20130731T223917_8zl8hvz0_.bkp tag=TAG20130731T223917 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 31-JUL-13

Starting backup at 31-JUL-13
current log archived
using channel ORA_DISK_1
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=3 RECID=21 STAMP=822264061
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_TAG20130731T224101_8zl8hxw0_.bkp tag=TAG20130731T224101 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/archivelog/1_3_822243364.dbf RECID=21 STAMP=822264061
Finished backup at 31-JUL-13

[oracle@node1 backupset]$ cd 2013_07_31/
[oracle@node1 2013_07_31]$ ls
o1_mf_annnn_TAG20130731T184620_8zktqws5_.bkp
o1_mf_annnn_TAG20130731T223852_8zl8cxq2_.bkp
o1_mf_annnn_TAG20130731T223852_8zl8dgfj_.bkp
o1_mf_annnn_TAG20130731T223852_8zl8dkt6_.bkp
o1_mf_annnn_TAG20130731T224101_8zl8hxw0_.bkp
o1_mf_ncsnf_TAG20130731T223917_8zl8hvz0_.bkp
o1_mf_nnndf_TAG20130731T223917_8zl8dpg6_.bkp
[oracle@node1 2013_07_31]$ ls -tl
total 1224012
-rw-r----- 1 oracle oinstall       3072 Jul 31 22:41 o1_mf_annnn_TAG20130731T224101_8zl8hxw0_.bkp
-rw-r----- 1 oracle oinstall    9830400 Jul 31 22:41 o1_mf_ncsnf_TAG20130731T223917_8zl8hvz0_.bkp
-rw-r----- 1 oracle oinstall 1080147968 Jul 31 22:40 o1_mf_nnndf_TAG20130731T223917_8zl8dpg6_.bkp--这个是数据文件的备份文件集
-rw-r----- 1 oracle oinstall   31120384 Jul 31 22:39 o1_mf_annnn_TAG20130731T223852_8zl8dkt6_.bkp
-rw-r----- 1 oracle oinstall   25585664 Jul 31 22:39 o1_mf_annnn_TAG20130731T223852_8zl8dgfj_.bkp
-rw-r----- 1 oracle oinstall  102524928 Jul 31 22:39 o1_mf_annnn_TAG20130731T223852_8zl8cxq2_.bkp
-rw-r----- 1 oracle oinstall    2908672 Jul 31 18:46 o1_mf_annnn_TAG20130731T184620_8zktqws5_.bkp

SQL> create table u2_table(id number,name varchar2(50),password varchar2(50));

Table created.

SQL> insert into u2_table(88,'cunxm','chenjian');
insert into u2_table(88,'cunxm','chenjian')
                     *
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> c/table/table values;--SQL写错了,可以这样替换喔
  1* insert into u2_table values(88,'cunxm','chenjian')
SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> commit;

Commit complete.

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
CJ
U2_TABLE

SQL> select * from cj;

        ID NAME
---------- --------------------------------------------------
         1 cj
         8 cj888

SQL> drop user u2 cascade;
drop user u2 cascade
*
ERROR at line 1:
ORA-01031: insufficient privileges--权限不够,切换管理员用户操作

SQL> conn / as sysdba
Connected.
SQL> dropt user u2 cascade;--关联删除
SP2-0734: unknown command beginning "dropt user..." - rest of line ignored.
SQL> drop user u2 cascade;

User dropped.

SQL> conn u1/u1;
Connected.
SQL> drop table cj purge;--干净删除,学过闪回表之前必作的动作

Table dropped.
SQL> purge recyclebin; --清除回收站,以免误解

SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

 

[oracle@node1 2013_07_31]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
[oracle@node1 2013_07_31]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 31 22:57:25 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.
--在准备恢复之前,在删除u2用户,以及删除u1表CJ;以便认识这些不完全恢复的时间点,是不是删除的表与用户都可以,但后来u2建立的表u2_table是无法重现了,但删除用户与表能找回来
SQL> startup mount;
ORACLE instance started.

Total System Global Area  517763072 bytes
Fixed Size                  2214896 bytes
Variable Size             394265616 bytes
Database Buffers          117440512 bytes
Redo Buffers                3842048 bytes
Database mounted.
SQL> restore database;  --注意这是SQLPLUS下面的环境,回到RMAN命令行操作
SP2-0734: unknown command beginning "restore da..." - rest of line ignored.
SQL> alert session nls_date_format='yyyy-mm-dd hh24:mi:ss';
SP2-0734: unknown command beginning "alert sess..." - rest of line ignored.
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 2013_07_31]$  export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
[oracle@node1 2013_07_31]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 31 23:01:09 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MDNSS (DBID=3864238845, not open)

--为了安全不会出差错,第二次进行设置时间格式
RMAN>  sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';

sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"

RMAN> restore database;

Starting restore at 2013-07-31 23:02:48
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/mdnss/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/mdnss/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/mdnss/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/mdnss/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/mdnss/jf_data01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/mdnss/jf_data02.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/mdnss/t2a.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/MDNSS/backupset/2013_07_31/o1_mf_nnndf_TAG20130731T223917_8zl8dpg6_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/MDNSS/backupset/2013_07_31/o1_mf_nnndf_TAG20130731T223917_8zl8dpg6_.bkp tag=TAG20130731T223917
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 2013-07-31 23:04:15

--一定理解时间点的概念,因为上面2013-07-31 22:36:51是我建立二个用户,以及在u1建立一张表后的动作,由于不太熟练,所以
--时间老以为可以恢复到记录这个时间之前是不对的,因为备份完全后起码在十分种左右,这一次是一致性关闭数据库,所以在这个时间上加十分钟就刚好了
RMAN> recover database until time '2013-07-31 22:36:51';

Starting recover at 2013-07-31 23:05:31
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/31/2013 23:05:32
RMAN-06555: datafile 1 must be restored from backup created before 2013-07-31 22:36:51

RMAN>  recover database until time '2013-07-31 22:34:51';--往前调是不认的

Starting recover at 2013-07-31 23:06:22
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/31/2013 23:06:22
RMAN-06555: datafile 1 must be restored from backup created before 2013-07-31 22:34:51

RMAN> recover database until time '2013-07-31 22:36:51';

Starting recover at 2013-07-31 23:08:36
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/31/2013 23:08:36
RMAN-06555: datafile 1 must be restored from backup created before 2013-07-31 22:36:51

RMAN> recover database until time '2013-07-31 22: 46: 51';-- 只有往后调时间点点才能恢复

Starting recover at 2013-07-31 23:09:31
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 2013-07-31 23:09:34

RMAN> alter database open resetlogs;

database opened