|
本帖最后由 secoug 于 2019-11-30 11:45 编辑
闪回5:
- SQL> flashback table "BIN$lZGGzOMdKWjgU08AqMATcQ==$0" to before drop ;
- Flashback complete.
- SQL> select * from t05311_a;
- A
- -------------------
- 2019-10-23:17:29:15
- SQL> show recyclebin
- ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
- ---------------- ------------------------------ ------------ -------------------
- T05310_A BIN$lY1JRr25VYDgU08AqMC1vA==$0 TABLE 2019-10-23:12:24:00
- T05311_A BIN$lZGGzOMfKWjgU08AqMATcQ==$0 TABLE 2019-10-23:17:32:40
- T05311_A BIN$lZGGzOMcKWjgU08AqMATcQ==$0 TABLE 2019-10-23:17:27:32
- SQL> flashback table t05311_a to before drop ;
- flashback table t05311_a to before drop
- *
- ERROR at line 1:
- ORA-38312: original name is used by an existing object
- SQL> flashback table t05311_a to before drop rename to t05311_b;
- Flashback complete.
- SQL> select * from t05311_b;
- A
- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- AAAAA
- SQL>
复制代码- select * from dba_recyclebin;
- select * from dba_indexes i where i.table_name = 'T05311_B';
- alter index hr."BIN$lZGGzOMeKWjgU08AqMATcQ==$0" rename to i05311_b;
复制代码- SQL> rename t05311_a to t05311_c;
- Table renamed.
复制代码- SQL> flashback table t05311_a to before drop;
- Flashback complete.
- SQL> select * from t05311_a;
- A
- ----------
- 1
复制代码 ------闪回6:- select * from v$restore_point;
- create restore point rsp1 as of timestamp sysdate-10/1440;
- --4972916
- select * from v$flashback_database_log;
复制代码- SQL> shutdown abort
- ORACLE instance shut down.
- SQL> startup mount
- ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
- ORACLE instance started.
- Total System Global Area 1553305600 bytes
- Fixed Size 2253544 bytes
- Variable Size 922750232 bytes
- Database Buffers 620756992 bytes
- Redo Buffers 7544832 bytes
- Database mounted.
- SQL> flashback database to timestamp to_timestamp('2019-10-23:19:41:41','YYYY-MM-DD:HH24:MI:SS') ;
- Flashback complete.
- SQL> alter database open ;
- alter database open
- *
- ERROR at line 1:
- ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
- SQL> alter database open resetlogs;
复制代码
-------不完全恢复的对比:- rman>startup mount
- rman>run {
- sql "alter session set nls_date_format=''YYYY-MM-DD:HH24:MI:SS''";
- set until time '..........';
- restore database;
- recover database;
- }
复制代码 -------- [oracle@station79 ~]$ rman target /
- Recovery Manager: Release 11.2.0.4.0 - Production on Wed Oct 23 20:03:06 2019
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- connected to target database: ORCL (DBID=1545528092)
- RMAN> backup tag '1T-WHOLE-INCR0' incremental level 0 database plus archivelog delete all input;
复制代码
- Starting backup at 2019-10-23:20:04:51
- current log archived
- using target database control file instead of recovery catalog
- allocated channel: ORA_SBT_TAPE_1
- channel ORA_SBT_TAPE_1: SID=58 device type=SBT_TAPE
- channel ORA_SBT_TAPE_1: Oracle Secure Backup
- allocated channel: ORA_SBT_TAPE_2
- channel ORA_SBT_TAPE_2: SID=61 device type=SBT_TAPE
- channel ORA_SBT_TAPE_2: Oracle Secure Backup
- channel ORA_SBT_TAPE_1: starting archived log backup set
- channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
- input archived log thread=1 sequence=182 RECID=1 STAMP=1022408415
- input archived log thread=1 sequence=183 RECID=2 STAMP=1022408416
- input archived log thread=1 sequence=184 RECID=3 STAMP=1022408416
- input archived log thread=1 sequence=185 RECID=7 STAMP=1022439314
- input archived log thread=1 sequence=186 RECID=8 STAMP=1022439314
- channel ORA_SBT_TAPE_1: starting piece 1 at 2019-10-23:20:04:55
- channel ORA_SBT_TAPE_2: starting archived log backup set
- channel ORA_SBT_TAPE_2: specifying archived log(s) in backup set
- input archived log thread=1 sequence=187 RECID=6 STAMP=1022439314
- channel ORA_SBT_TAPE_2: starting piece 1 at 2019-10-23:20:04:55
- channel ORA_SBT_TAPE_1: finished piece 1 at 2019-10-23:20:05:30
- piece handle=01uf2fv6_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:35
- channel ORA_SBT_TAPE_1: deleting archived log(s)
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_182_1022315540.dbf RECID=1 STAMP=1022408415
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_183_1022315540.dbf RECID=2 STAMP=1022408416
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_184_1022315540.dbf RECID=3 STAMP=1022408416
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_185_1022315540.dbf RECID=7 STAMP=1022439314
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_185_1022315540.dbf RECID=4 STAMP=1022419850
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_186_1022315540.dbf RECID=8 STAMP=1022439314
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_186_1022315540.dbf RECID=5 STAMP=1022431844
- channel ORA_SBT_TAPE_1: starting archived log backup set
- channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
- input archived log thread=1 sequence=1 RECID=9 STAMP=1022442065
- input archived log thread=1 sequence=2 RECID=10 STAMP=1022442066
- input archived log thread=1 sequence=3 RECID=11 STAMP=1022442068
- input archived log thread=1 sequence=4 RECID=12 STAMP=1022442069
- input archived log thread=1 sequence=5 RECID=13 STAMP=1022442070
- input archived log thread=1 sequence=6 RECID=14 STAMP=1022442071
- input archived log thread=1 sequence=7 RECID=15 STAMP=1022442072
- input archived log thread=1 sequence=8 RECID=16 STAMP=1022442095
- input archived log thread=1 sequence=9 RECID=17 STAMP=1022442096
- input archived log thread=1 sequence=10 RECID=18 STAMP=1022442107
- channel ORA_SBT_TAPE_1: starting piece 1 at 2019-10-23:20:05:30
- channel ORA_SBT_TAPE_2: finished piece 1 at 2019-10-23:20:05:30
- piece handle=02uf2fv7_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:35
- channel ORA_SBT_TAPE_2: deleting archived log(s)
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_187_1022315540.dbf RECID=6 STAMP=1022439314
- channel ORA_SBT_TAPE_2: starting archived log backup set
- channel ORA_SBT_TAPE_2: specifying archived log(s) in backup set
- input archived log thread=1 sequence=1 RECID=25 STAMP=1022443492
- channel ORA_SBT_TAPE_2: starting piece 1 at 2019-10-23:20:05:30
- channel ORA_SBT_TAPE_1: finished piece 1 at 2019-10-23:20:05:45
- piece handle=03uf2g0a_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:15
- channel ORA_SBT_TAPE_1: deleting archived log(s)
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_1_1022439313.dbf RECID=9 STAMP=1022442065
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_2_1022439313.dbf RECID=10 STAMP=1022442066
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_3_1022439313.dbf RECID=11 STAMP=1022442068
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_4_1022439313.dbf RECID=12 STAMP=1022442069
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_5_1022439313.dbf RECID=13 STAMP=1022442070
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_6_1022439313.dbf RECID=14 STAMP=1022442071
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_7_1022439313.dbf RECID=15 STAMP=1022442072
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_8_1022439313.dbf RECID=16 STAMP=1022442095
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_9_1022439313.dbf RECID=17 STAMP=1022442096
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_10_1022439313.dbf RECID=18 STAMP=1022442107
- channel ORA_SBT_TAPE_1: starting archived log backup set
- channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
- input archived log thread=1 sequence=11 RECID=19 STAMP=1022442110
- input archived log thread=1 sequence=12 RECID=24 STAMP=1022442470
- input archived log thread=1 sequence=13 RECID=22 STAMP=1022442470
- input archived log thread=1 sequence=14 RECID=23 STAMP=1022442470
- channel ORA_SBT_TAPE_1: starting piece 1 at 2019-10-23:20:05:46
- channel ORA_SBT_TAPE_1: finished piece 1 at 2019-10-23:20:06:01
- piece handle=05uf2g0q_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:15
- channel ORA_SBT_TAPE_1: deleting archived log(s)
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_11_1022439313.dbf RECID=19 STAMP=1022442110
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_12_1022439313.dbf RECID=24 STAMP=1022442470
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_12_1022439313.dbf RECID=20 STAMP=1022442113
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_13_1022439313.dbf RECID=22 STAMP=1022442470
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_13_1022439313.dbf RECID=21 STAMP=1022442113
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_14_1022439313.dbf RECID=23 STAMP=1022442470
- channel ORA_SBT_TAPE_2: finished piece 1 at 2019-10-23:20:06:01
- piece handle=04uf2g0a_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:31
- channel ORA_SBT_TAPE_2: deleting archived log(s)
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_1_1022442470.dbf RECID=25 STAMP=1022443492
- Finished backup at 2019-10-23:20:06:01
- Starting backup at 2019-10-23:20:06:01
- using channel ORA_SBT_TAPE_1
- using channel ORA_SBT_TAPE_2
- channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set
- channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
- input datafile file number=00004 name=+DATA/orcl/datafile/users.259.1017747273
- channel ORA_SBT_TAPE_1: starting piece 1 at 2019-10-23:20:06:02
- channel ORA_SBT_TAPE_2: starting incremental level 0 datafile backup set
- channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
- input datafile file number=00001 name=+DATA/orcl/datafile/system.256.1017747273
- channel ORA_SBT_TAPE_2: starting piece 1 at 2019-10-23:20:06:03
- channel ORA_SBT_TAPE_2: finished piece 1 at 2019-10-23:20:06:38
- piece handle=07uf2g1a_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:35
- channel ORA_SBT_TAPE_2: starting incremental level 0 datafile backup set
- channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
- input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.1017747273
- channel ORA_SBT_TAPE_2: starting piece 1 at 2019-10-23:20:06:38
- channel ORA_SBT_TAPE_2: finished piece 1 at 2019-10-23:20:07:03
- piece handle=08uf2g2e_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:25
- channel ORA_SBT_TAPE_2: starting incremental level 0 datafile backup set
- channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
- input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/tbs3_a.dbf
- input datafile file number=00009 name=/u01/app/oracle/oradata/orcl/tbs3_b.dbf
- input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.264.1022254567
- input datafile file number=00005 name=+DATA/orcl/datafile/example.265.1017747369
- input datafile file number=00006 name=+DATA/orcl/datafile/tbs1.267.1017789135
- input datafile file number=00007 name=+DATA/orcl/datafile/tbs2.268.1022104451
- input datafile file number=00011 name=+DATA/orcl/datafile/tbs5.270.1022108063
- input datafile file number=00014 name=+DATA/orcl/datafile/tbs_nocompression.273.1022129913
- input datafile file number=00015 name=+DATA/orcl/datafile/tbs_basic.274.1022129945
- input datafile file number=00016 name=+DATA/orcl/datafile/tbs_oltp.275.1022129967
- channel ORA_SBT_TAPE_2: starting piece 1 at 2019-10-23:20:07:04
- channel ORA_SBT_TAPE_2: finished piece 1 at 2019-10-23:20:07:29
- piece handle=09uf2g38_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:25
- channel ORA_SBT_TAPE_2: starting incremental level 0 datafile backup set
- channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
- input datafile file number=00017 name=+DATA/orcl/datafile/tbs6.276.1022136411
- input datafile file number=00018 name=+DATA/orcl/datafile/tbsaudit.277.1022182039
- input datafile file number=00019 name=+HIGHDATA/orcl/datafile/tbshighdata1.256.1022396685
- input datafile file number=00020 name=+HIGHDATA/orcl/datadir/tbshighdata2.dbf
- input datafile file number=00021 name=+DATA/orcl/datafile/tbs7.258.1022401035
- input datafile file number=00022 name=+DATA/orcl/datadir/tbs8.dbf
- channel ORA_SBT_TAPE_2: starting piece 1 at 2019-10-23:20:07:29
- channel ORA_SBT_TAPE_2: finished piece 1 at 2019-10-23:20:07:44
- piece handle=0auf2g41_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:15
- channel ORA_SBT_TAPE_2: starting incremental level 0 datafile backup set
- channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
- input datafile file number=00010 name=+DATA/orcl/datafile/tbs4.269.1022107971
- channel ORA_SBT_TAPE_2: starting piece 1 at 2019-10-23:20:07:46
- channel ORA_SBT_TAPE_2: finished piece 1 at 2019-10-23:20:08:11
- piece handle=0buf2g4h_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:25
- channel ORA_SBT_TAPE_2: starting incremental level 0 datafile backup set
- channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
- input datafile file number=00012 name=+DATA/orcl/datafile/tbs4.271.1022108093
- channel ORA_SBT_TAPE_2: starting piece 1 at 2019-10-23:20:08:12
- channel ORA_SBT_TAPE_2: finished piece 1 at 2019-10-23:20:08:37
- piece handle=0cuf2g5c_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:25
- channel ORA_SBT_TAPE_2: starting incremental level 0 datafile backup set
- channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
- including current control file in backup set
- channel ORA_SBT_TAPE_2: starting piece 1 at 2019-10-23:20:08:38
- channel ORA_SBT_TAPE_2: finished piece 1 at 2019-10-23:20:08:53
- piece handle=0duf2g65_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:15
- channel ORA_SBT_TAPE_2: starting incremental level 0 datafile backup set
- channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
- input datafile file number=00013 name=+DATA/orcl/datafile/tbs16k.272.1022119203
- channel ORA_SBT_TAPE_2: starting piece 1 at 2019-10-23:20:08:56
- channel ORA_SBT_TAPE_1: finished piece 1 at 2019-10-23:20:09:11
- piece handle=06uf2g1a_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:03:09
- channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set
- channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
- including current SPFILE in backup set
- channel ORA_SBT_TAPE_1: starting piece 1 at 2019-10-23:20:09:11
- channel ORA_SBT_TAPE_2: finished piece 1 at 2019-10-23:20:09:12
- piece handle=0euf2g6n_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:16
- channel ORA_SBT_TAPE_1: finished piece 1 at 2019-10-23:20:09:36
- piece handle=0fuf2g77_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
- Finished backup at 2019-10-23:20:09:36
- Starting backup at 2019-10-23:20:09:36
- current log archived
- using channel ORA_SBT_TAPE_1
- using channel ORA_SBT_TAPE_2
- channel ORA_SBT_TAPE_1: starting archived log backup set
- channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
- input archived log thread=1 sequence=2 RECID=26 STAMP=1022443776
- channel ORA_SBT_TAPE_1: starting piece 1 at 2019-10-23:20:09:37
- channel ORA_SBT_TAPE_1: finished piece 1 at 2019-10-23:20:10:02
- piece handle=0guf2g81_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
- channel ORA_SBT_TAPE_1: deleting archived log(s)
- archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/use_db_recovery_dest1_2_1022442470.dbf RECID=26 STAMP=1022443776
- Finished backup at 2019-10-23:20:10:02
- RMAN>
复制代码
|
|