Rman based on incomplete recovery of time

Rman Based on Time Incomplete recovery 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;} -- command points, the VM will not move when the time is off, restore database; recover database until time '2017-07-31 22:46:51 '; alter database open resetlogs; SSH Secure Shell 3.2.9 (Build 7.31) Copyright (c) 2000-2003 SSH Communications Security Corp- http://www.ssh.com/This Copy of SSH Secure Shell is a non-essential cial 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 sysdbaSQL * Plus: Release 11.2.0.1.0 Production on Wed Jul 31 22:30:03 2013 Copyright (c) 1982,200 9, Oracle. all rights reserved. connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionWith the Partitioning, OLAP, data Mining and Real Application Testing options -- confirm that the database is in the archive state SQL> Archive log listDatabase log mode archive ModeAutomatic archival Enable DArchive destination/home/oracle/archivelogOldest online log sequence 1 Next log sequence to archive 2 Current log sequence 2SQL>! ClearSQL> 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/u1Connected. 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. select sysdate from dual; SYSDATE---------31-JUL -13 -- set the time format so that the time point format SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss' can be recognized later after time restoration '; session altered. SQL> select sysdate from dual; SYSDATE-------------------2013-07-31 22: 36: 51SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionWith the Partitioning, OLAP, data Mining and Real Application Testing options [oracle @ node1 ~] $ Cd/u01/app/oracle/flash_recovery_area/MDNSS/[oracle @ node1 MDNSS] $ lltotal 16drwxr-x --- 2 oracle oinstall 4096 Jul 31 archivelogdrwxr-x --- 3 oracle oinstall 4096 Jul 31 backupsetdrwxr-x --- 2 oracle oinstall 4096 Jul 31 flashbackdrwxr-x --- 2 oracle oinstall 4096 May 26 onlinelog [oracle @ node1 MDNSS] $ cd backupset/[oracle @ node1 backupset] $ lltotal 4drwxr-x --- 2 oracle oinstall 4096 Jul 31 22:43:29 2013_07_31 [oracle @ node1 backupset] $ dateWed Jul 31 CST 2013 -- set the date Environment [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,200 9, Oracle and/or its affiliates. all rights reserved. connected to target database: MDNSS (DBID = 3864238845) -- Start RMAN full-database backup below, including archiving logs, and then delete INPUT loading RMAN> backup database plus archivelog delete input in memory; starting backup at each log archivedusing target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID = 46 device type = DISKchannel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log (s) in backup setinput 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-13channel ORA_DISK_1: finished piece 1 at 31-JUL-13piece handle =/u01/app/oracle/flash_recovery_area/MDNSS/backupset/2013_07_31/snapshot _. bkp tag = TAG20130731T223852 comment = NONEchannel 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/41525_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/41527_816399680.dbf RECID = 4 STAMP = 822198990 archived log file name =/home/oracle/archivelog/1_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_recid = 9 STAMP = 822209838 archived log file name =/home/oracle/archivelog/12731_816399680.dbf RECID = 10 STAMP = 822209838 archived log file name =/home/oracle/archivelog/1_recid = 12 STAMP = 822209838 channel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log (s) in backup setinput 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-13channel ORA_DISK_1: finished piece 1 at 31-JUL-13piece handle =/u01/app/oracle/flash_recovery_area/MDNSS/backupset/2013_07_31/snapshot _. bkp tag = TAG20130731T223852 comment = NONEchannel 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/export 7_822209838.dbf RECID = 17 STAMP = 822243364 archived log file name =/home/oracle/archivelog/export RECID = 18 STAMP = 822243364 archived log file name =/home/oracle/archivelog/1_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 setchannel ORA_DISK_1: specifying archived log (s) in backup setinput 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-13channel ORA_DISK_1: finished piece 1 at 31-JUL-13piece handle =/u01/app/oracle/flash_recovery_area/MDNSS/backupset/2013_07_31/snapshot _. bkp tag = TAG20130731T223852 comment = NONEchannel 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-13Starting backup at 31-JUL-13using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile (s) in backup setinput datafile file number = 00001 name =/u01/app/oracle/oradata/mdnss/system01.dbfinput datafile file number = 00002 name =/u01/app/oracle/oradata/mdnss/ sysaux01.dbfinput datafile file number = 00003 name =/u01/app/oracle/oradata/mdnss/nvidatafile file number = 00004 name =/u01/app/oracle/oradata/mdnss/users01.dbfinput datafile file number = 00007 name =/u01/app/oracle/oradata/mdnss/t2a. dbfinput datafile file number = 00006 name =/u01/app/oracle/oradata/mdnss/export datafile file number = 00005 name =/u01/app/oracle/oradata/mdnss/jf_data01.dbfchannel ORA_DISK_1: starting piece 1 at 31-JUL-13channel ORA_DISK_1: finished piece 1 at 31-JUL-13piece handle =/u01/app/oracle/flash_recovery_area/MDNSS/backupset/2013_07_31/snapshot _. bkp tag = TAG20130731T223917 comment = NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00: 01: 38 channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile (s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 31-JUL-13channel ORA_DISK_1: finished piece 1 at 31-JUL-13piece handle =/u01/app/oracle/flash_recovery_area/MDNSS/backupset/2013_07_31/empty _. bkp tag = commoncomment = NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00: 00: 02 Finished backup at least backup at 31-JUL-13current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log (s) in backup setinput archived log thread = 1 sequence = 3 RECID = 21 STAMP = 822264061 channel ORA_DISK_1: starting piece 1 at either ORA_DISK_1: finished piece 1 at 31-JUL-13piece handle =/u01/app/oracle/flash_recovery_area/MDNSS/backupset/2013_07_31/empty _. bkp tag = TAG20130731T224101 comment = NONEchannel 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/Duplicate RECID = 21 STAMP = 822264061 Finished backup at 31-JUL-13 [oracle @ node1 backupset] $ cd 2013_07_31/[oracle @ node1 2013_07_31] $ lso1_mf_annnn_TAG20130731T184620_8zktqws5 _. bkpo1_mf_annnn_TAG20130731T223852_8zl8cxq2 _. bkpo1_mf_annnn_TAG20130731T223852_8zl8dgfj _. bkpo1_mf_annnn_TAG20130731T223852_8zl8dkt6 _. bkpo1_mf_annnn_tag20130731t224101088zl8hxw0 _. bkpo1_mf_ncsnf_TAG20130731T223917_8zl8hvz0 _. bkpow.mf_nnndf_tag20130731t223917_8zl8dpg6 _. bkp [oracle @ node1 2013_07_31] $ ls-tltotal 1224012-rw-r ----- 1 oracle oinstall 3072 Jul 31 o1_mf_annnn_TAG20130731T224101_8zl8hxw0 _. bkp-rw-r ----- 1 oracle oinstall 9830400 Jul 31 o1_mf_ncsnf_TAG20130731T223917_8zl8hvz0 _. bkp-rw-r ----- 1 oracle oinstall 1080147968 Jul 31 o1_mf_nnndf_TAG20130731T223917_8zl8dpg6 _. bkp -- this is the backup file set of the data file-rw-r ----- 1 oracle oinstall 31120384 Jul 31 o1_mf_annnn_TAG20130731T223852_8zl8dkt6 _. bkp-rw-r ----- 1 oracle oinstall 25585664 Jul 31 o1_mf_annnn_TAG20130731T223852_8zl8dgfj _. bkp-rw-r ----- 1 oracle oinstall 102524928 Jul 31 o1_mf_annnn_TAG20130731T223852_8zl8cxq2 _. bkp-rw-r ----- 1 oracle oinstall 2908672 Jul 31 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 values; -- the SQL statement is incorrect. You can replace 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------------------------------CJU2_TABLESQL> select * from cj; ID NAME ---------- expose 1 cj 8 cj888SQL> drop user u2 cascade; drop user u2 cascade * ERROR at line 1: ORA-01031: insufficient privileges -- the permission is insufficient. Switch to the administrator user to operate SQL> conn/as sysdbaConnected. SQL> dropt user u2 cascade; -- associate Delete 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; -- clean delete: The action Table dropped required before the flashback table is learned. SQL> purge recyclebin; -- clear the recycle bin to avoid misunderstanding SQL> shutdown immediateORA-01031: insufficient privilegesSQL> 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 sysdbaSQL * Plus: release 11.2.0.1.0 Production on Wed Jul 31 22:57:25 2013 Copyright (c) 1982,200 9, Oracle. all rights reserved. connected to an idle instance. -- before you prepare for restoration, delete the u2 user and the u1 table CJ so that you can know whether the table and user can be deleted at these Incomplete recovery time points, however, the u2_table created by u2 cannot be reproduced, but the user and table can be retrieved after deletion. SQL> startup mount; ORACLE instance started. total System Global Area 517763072 bytesFixed Size 2214896 bytesVariable Size 394265616 bytesDatabase Buffers 117440512 bytesRedo Buffers 3842048 bytesDatabase mounted. SQL> restore database; -- note that this is the environment under SQLPLUS, back to the RMAN command line operation 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> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionWith 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,200 9, Oracle and/or its affiliates. all rights reserved. connected to target database: MDNSS (DBID = 3864238845, not open) -- for security purposes, no errors will occur, the second time you set the time format 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_1channel ORA_DISK_1: SID = 20 device type = DISKchannel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile (s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to/u01/app/oracle/oradata/mdnss/system01.dbfchannel ORA_DISK_1: restoring datafile 00002 to/u01/app/oracle/oradata/mdnss/sysaux01.dbfchannel ORA_DISK_1: restoring datafile 00003 to/u01/app/oracle/oradata/mdnss/undotbs01.dbfchannel ORA_DISK_1: restoring datafile 00004 to/u01/app/oracle/oradata/mdnss/users01.dbfchannel ORA_DISK_1: restoring datafile 00005 to/u01/app/oracle/oradata/mdnss/jf_data01.dbfchannel ORA_DISK_1: restoring datafile 00006 to/u01/app/oracle/oradata/mdnss/jf_data02.dbfchannel ORA_DISK_1: restoring datafile 00007 to/u01/app/oracle/oradata/mdnss/t2a. dbfchannel ORA_DISK_1: reading from backup piece/u01/app/oracle/flash_recovery_area/MDNSS/backupset/2013_07_31/o1_mf_nnndf_TAG20130731T223917_8zl8dpg6 _. bkpchannel ORA_DISK_1: piece handle =/u01/app/oracle/flash_recovery_area/MDNSS/backupset/2013_07_31/o1_mf_nnndf_TAG20130731T223917_8zl8dpg6 _. bkp tag = TAG20130731T223917channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00: 01: 25 Finished restore at 23:04:15 -- understand the concept of time, because I created two users and created a table in u1 at 22:36:51 on, I was not very skilled, so -- the time is always wrong because the backup is at least about a very long time before the record can be recovered. This time, the database is shut down in a consistent manner, so add ten minutes on this time just happened to the RMAN> recover database until time '2017-07-31 22:36:51 '; Starting recover at 2013 23: 05: 31 using channel ORA_DISK_1RMAN-00571: ========================================================== ============================== RMAN-00569: ================ error message stack follows ============================ RMAN-00571: ========================================================== ============================== RMAN-03002: failure of recover command at 07/31/2013 23: 05: 32RMAN-06555: datafile 1 must be restored from backup created before 2013-07-31 22: 36: 51 RMAN> recover database until time '2017-07-31 22:34:51 '; -- Starting recover at 2013-07-31 23: 06: 22 using channel ORA_DISK_1RMAN-00571: ========================================================== ============================== RMAN-00569: ================ error message stack follows ============================ RMAN-00571: ========================================================== ============================== RMAN-03002: failure of recover command at 07/31/2013 23: 06: 22RMAN-06555: datafile 1 must be restored from backup created before 2013-07-31 22: 34: 51 RMAN> recover database until time '2017-07-31 22:36:51 '; Starting recover at 2013-07-31 23: 08: 36 using channel ORA_DISK_1RMAN-00571: ========================================================== ============================== RMAN-00569: ================ error message stack follows ============================ RMAN-00571: ========================================================== ============================== RMAN-03002: failure of recover command at 07/31/2013 23: 08: 36RMAN-06555: datafile 1 must be restored from backup created before 2013-07-31 22: 36: 51 RMAN> recover database until time '2017-07-31 22:46:51 '; -- Starting recover at 2013-07-31 23: 09 can be restored only after the time is adjusted: 31 using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00: 00: 03 Finished recover at 2013-07-31 23: 09: 34 RMAN> alter database open resetlogs; database opened