Bo's Oracle Station

查看: 1915|回复: 0

活动第17/18次(2018-05-12星期六上下午)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-5-12 10:02:42 | 显示全部楼层 |阅读模式
  1. create tablespace tbsfda1 datafile size 15M autoextend off;

  2. select  * from dba_tablespaces;

  3. select  * from dba_flashback_archive;

  4. create flashback archive fda1 tablespace tbsfda1
  5.    quota 10M  retention 3 year;

  6. select  * from dba_flashback_archive;
复制代码

  1. select  * from dba_flashback_archive_ts;

  2. select  * from dba_flashback_archive_tables;

  3. alter table hr.tbig flashback archive fda1;

  4. select  object_id  from dba_objects o where o.object_name='TBIG'
  5.    and o.owner='HR';

  6.   select  * from dba_flashback_archive_tables;
复制代码

闪回数据归档,quota满后会报错:
  1. SQL> conn hr/oracle_4U
  2. ERROR:
  3. ORA-28002: the password will expire within 7 days


  4. Connected.
  5. SQL> select  count(*) from hr.tbig;

  6.   COUNT(*)
  7. ----------
  8.     633188

  9. SQL> desc hr.tbig
  10. Name                                           Null?    Type
  11. ----------------------------------------- -------- ----------------------------
  12. OWNER                                                    VARCHAR2(30)
  13. NAME                                                    VARCHAR2(30)
  14. TYPE                                                    VARCHAR2(12)
  15. LINE                                                    NUMBER
  16. TEXT                                                    VARCHAR2(4000)

  17. SQL> update hr.tbig set LINE=10000 where rownum=1;

  18. 1 row updated.

  19. SQL> commit;

  20. Commit complete.

  21. SQL> select  * from tbig where rowid='_fbda
  22.   2  

  23. SQL> select  * from tbig where rowid='AAATrYAAEAAAAI7AAA';

  24. OWNER                               NAME                              TYPE
  25. ------------------------------ ------------------------------ ------------
  26.       LINE
  27. ----------
  28. TEXT
  29. --------------------------------------------------------------------------------
  30. SYS                               STANDARD                       PACKAGE
  31.      10000
  32. package STANDARD AUTHID CURRENT_USER is              -- careful on this line; SE
  33. D edit occurs!


  34. SQL> select  * from tbig   as of scn  2186345   where rowid='AAATrYAAEAAAAI7AAA';

  35. OWNER                               NAME                              TYPE
  36. ------------------------------ ------------------------------ ------------
  37.       LINE
  38. ----------
  39. TEXT
  40. --------------------------------------------------------------------------------
  41. SYS                               STANDARD                       PACKAGE
  42.      10000
  43. package STANDARD AUTHID CURRENT_USER is              -- careful on this line; SE
  44. D edit occurs!


  45. SQL> select  * from tbig   as of scn  2186344   where rowid='AAATrYAAEAAAAI7AAA';

  46. OWNER                               NAME                              TYPE
  47. ------------------------------ ------------------------------ ------------
  48.       LINE
  49. ----------
  50. TEXT
  51. --------------------------------------------------------------------------------
  52. SYS                               STANDARD                       PACKAGE
  53.          1
  54. package STANDARD AUTHID CURRENT_USER is              -- careful on this line; SE
  55. D edit occurs!


  56. SQL> show user
  57. USER is "HR"
  58. SQL> update tbig set line=10000 ;

  59. 633188 rows updated.

  60. SQL> commit;

  61. Commit complete.

  62. SQL> update tbig set line=20000 ;

  63. 633188 rows updated.

  64. SQL> commit;

  65. Commit complete.

  66. SQL> insert  into tbig select  * from tbig ;
  67. insert        into tbig select  * from tbig
  68.              *
  69. ERROR at line 1:
  70. ORA-55617: Flashback Archive "FDA1" runs out of space and tracking on "TBIG" is
  71. suspended


复制代码
这个时候,所有的旧值全部都堆在undo 表空间上:

a.png

这个时候应该加空间:

  1. alter database datafile '+DATA/orcl/datafile/tbsfda1.267.975923969'
  2.    autoextend on;
  3.    
  4.     select  * from dba_flashback_archive_ts;
  5.    
  6.     alter flashback archive fda1  modify  tablespace tbsfda1  quota 1000M;
  7.    
  8.     alter flashback archive fda1  add tablespace users quota 1000M;
复制代码

问题就解决了:
  1. SQL> update tbig set line=30000 where rownum=1;
  2. update tbig set line=30000 where rownum=1
  3.        *
  4. ERROR at line 1:
  5. ORA-55617: Flashback Archive "FDA1" runs out of space and tracking on "TBIG" is
  6. suspended


  7. SQL> update tbig set line=30000 where rownum=1;

  8. 1 row updated.

  9. SQL> commit;

  10. Commit complete.
复制代码

过一会查看这些表空间:

b.png

普通用户如何绑定闪回数据归档,以及闪回数据归档的默认:

  1. create flashback archive  default fda2  
  2.   tablespace tbsfda1  quota 50M  retention 40 day;
  3.   
  4.   select  * from dba_flashback_archive;
  5.   
  6.   alter  flashback archive fda1 set default;
  7.   
  8.   grant flashback archive on fda1 to hr;
复制代码

只有两个闪回查询直接跨过DDL操作,但是闪回表不行:
  1. 1  select    versions_xid,  versions_startscn ,  a  from t05311_a
  2.   2*  versions between scn minvalue and maxvalue
  3. SQL> /

  4. VERSIONS_XID         VERSIONS_STARTSCN            A
  5. ---------------- ----------------- ----------
  6.                                             1
  7. 08000B0048050000           2756094            2
  8.                            2756243            2

  9. SQL> select  * from t05311_a
  10.   2  as of scn 2756242;

  11.          A
  12. ----------
  13.          2

  14. SQL> select  * from t05311_a
  15.   2  as of scn 2756093;

  16.          A B
  17. ---------- --------------------
  18.          1 A

  19. SQL> select  * from t05311_a
  20.   2  as of scn 2756094;

  21.          A B
  22. ---------- --------------------
  23.          2 B

  24. SQL> select  * from t05311_a
  25.   2  as of scn 2756242;

  26.          A
  27. ----------
  28.          2

  29. SQL> alter table t05311_a enable row movement;

  30. Table altered.

  31. SQL> flashback table t05311_a to scn 2756094;
  32. flashback table t05311_a to scn 2756094
  33.                 *
  34. ERROR at line 1:
  35. ORA-01466: unable to read data - table definition has changed


  36. SQL>
复制代码

人为purge闪回数据归档:

  1. alter flashback archive fda2 purge before timestamp
  2.     (systimestamp - interval '1'  second  );
  3.    
  4.     alter system flush buffer_cache;
复制代码


有一些表和闪回数据归档相联系后,某些DDL操作做不了:

  1. create table part1 ( a number )
  2. partition by  range(a)
  3. (
  4. partition p1 values less than(100),
  5. partition p2 values less than (maxvalue)
  6. );
复制代码
  1. SQL> insert into part1 values (100) ;

  2. 1 row created.

  3. SQL> commit;

  4. Commit complete.

  5. SQL> select  * from part1 ;

  6.          A
  7. ----------
  8.        100

  9. SQL> select  * from part1 (p2 ) ;
  10. select        * from part1 (p2 )
  11.                      *
  12. ERROR at line 1:
  13. ORA-00933: SQL command not properly ended


  14. SQL> select  * from part1   partition (p2 ) ;

  15.          A
  16. ----------
  17.        100

  18. SQL> alter table part1  flashback archive fda2;

  19. Table altered.

  20. SQL> update part1 set a=90 where a=100;
  21. update part1 set a=90 where a=100
  22.        *
  23. ERROR at line 1:
  24. ORA-14402: updating partition key column would cause a partition change


  25. SQL> update part1 set a=900 where a=100;

  26. 1 row updated.

  27. SQL> commit;

  28. Commit complete.

  29. SQL> select  * from part1;

  30.          A
  31. ----------
  32.        900

  33. SQL> alter table part1 split partition p2 at (1000)  into ( partition p21 , partition p22) ;
  34. alter table part1 split partition p2 at (1000)        into ( partition p21 , partition p22)
  35. *
  36. ERROR at line 1:
  37. ORA-55610: Invalid DDL statement on history-tracked table


  38. SQL>
复制代码
  1. grant flashback archive on fda2 to hr;

  2. select  * from dba_flashback_archive_tables;

  3. select   * from hr.SYS_FBA_HIST_80616;

  4. select  * from dba_tab_partitions tp
  5. where tp.table_name='PART1';
复制代码

全局方案进化(Full Schema Evolution),顺便改了数据的历史:
  1. SQL> select  * from part1;

  2.          A
  3. ----------
  4.        900

  5. SQL> alter table part1 split partition p2 at (1000)  into ( partition p21 , partition p22) ;
  6. alter table part1 split partition p2 at (1000)        into ( partition p21 , partition p22)
  7. *
  8. ERROR at line 1:
  9. ORA-55610: Invalid DDL statement on history-tracked table


  10. SQL> select  * from part1;

  11.          A
  12. ----------
  13.        900

  14. SQL> select  * from part1 as of scn 2759049;

  15.          A
  16. ----------
  17.        900

  18. SQL> select  * from part1 as of scn 2759048;

  19.          A
  20. ----------
  21.        100

  22. SQL> alter table part1 split partition p2 at (1000)  into ( partition p21 , partition p22) ;

  23. Table altered.

  24. SQL> select  * from part1 as of scn 2759048;

  25.          A
  26. ----------
  27.        200

  28. SQL>

复制代码
  1. begin
  2.    dbms_flashback_archive.disassociate_fba(owner_name => 'HR',
  3.    table_name => 'PART1');
  4. end;

  5. select  * from dba_tab_partitions tp
  6. where tp.table_name='PART1';

  7. select  * from dba_flashback_archive_tables;
  8. select   * from hr.SYS_FBA_HIST_80616;

  9. update hr.SYS_FBA_HIST_80616 set a=200;

  10. commit;

  11. begin
  12.    dbms_flashback_archive.reassociate_fba(owner_name => 'HR',
  13.    table_name => 'PART1');
  14.    end;
  15.    





复制代码

-----------------------------------------------------
索引不在show recyclebin 显示之列:

Screenshot.png

flashback table ... to before drop;索引没有改回原来的名字:
Screenshot-1.png


  1. select  flashback_on from v$database;

  2. select  * from v$flashback_database_log;

  3. select  * from v$flashback_database_logfile;

  4. alter database flashback on;
复制代码
  1. SELECT addr FROM x$ksbdp WHERE ksbdpnam = 'RVWR';

  2. SELECT ksmfsnam,ksmfstyp  FROM x$ksmfsv WHERE ksmfsadr = '0000000060028DE8';


  3. SELECT bytes FROM v$sgastat
  4. WHERE pool = 'shared pool' AND name = 'flashback generation buff';
复制代码

闪回用日志(带上穿越化身命令):
  1. [oracle@station90 ~]$ rman target /

  2. Recovery Manager: Release 11.2.0.3.0 - Production on Sat May 12 18:28:54 2018

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

  4. connected to target database: ORCL (DBID=1343950367, not open)

  5. RMAN> flashback database to sequence 25 thread 1;

  6. Starting flashback at 12-MAY-18
  7. using target database control file instead of recovery catalog
  8. allocated channel: ORA_DISK_1
  9. channel ORA_DISK_1: SID=192 device type=DISK
  10. allocated channel: ORA_DISK_2
  11. channel ORA_DISK_2: SID=8 device type=DISK
  12. allocated channel: ORA_DISK_3
  13. channel ORA_DISK_3: SID=69 device type=DISK
  14. allocated channel: ORA_DISK_4
  15. channel ORA_DISK_4: SID=132 device type=DISK
  16. allocated channel: ORA_DISK_5
  17. channel ORA_DISK_5: SID=193 device type=DISK
  18. allocated channel: ORA_DISK_6
  19. channel ORA_DISK_6: SID=9 device type=DISK
  20. allocated channel: ORA_DISK_7
  21. channel ORA_DISK_7: SID=70 device type=DISK
  22. allocated channel: ORA_DISK_8
  23. channel ORA_DISK_8: SID=133 device type=DISK
  24. allocated channel: ORA_SBT_TAPE_1
  25. channel ORA_SBT_TAPE_1: SID=194 device type=SBT_TAPE
  26. channel ORA_SBT_TAPE_1: Oracle Secure Backup
  27. allocated channel: ORA_SBT_TAPE_2
  28. channel ORA_SBT_TAPE_2: SID=71 device type=SBT_TAPE
  29. channel ORA_SBT_TAPE_2: Oracle Secure Backup

  30. RMAN-00571: ===========================================================
  31. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  32. RMAN-00571: ===========================================================
  33. RMAN-03002: failure of flashback command at 05/12/2018 18:29:26
  34. RMAN-20206: log sequence not found in the repository

  35. RMAN> list incarnation of database;


  36. List of Database Incarnations
  37. DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
  38. ------- ------- -------- ---------------- --- ---------- ----------
  39. 1       1       ORCL     1343950367       PARENT  1          15-AUG-09
  40. 2       2       ORCL     1343950367       PARENT  945184     23-MAY-13
  41. 3       3       ORCL     1343950367       ORPHAN  2789761    12-MAY-18
  42. 4       4       ORCL     1343950367       PARENT  2789761    12-MAY-18
  43. 5       5       ORCL     1343950367       CURRENT 2791744    12-MAY-18

  44. RMAN> reset database to incarnation 4;

  45. database reset to incarnation 4

  46. RMAN> list incarnation of database;


  47. List of Database Incarnations
  48. DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
  49. ------- ------- -------- ---------------- --- ---------- ----------
  50. 1       1       ORCL     1343950367       PARENT  1          15-AUG-09
  51. 2       2       ORCL     1343950367       PARENT  945184     23-MAY-13
  52. 3       3       ORCL     1343950367       ORPHAN  2789761    12-MAY-18
  53. 4       4       ORCL     1343950367       CURRENT 2789761    12-MAY-18
  54. 5       5       ORCL     1343950367       ORPHAN  2791744    12-MAY-18

  55. RMAN> flashback database to sequence 25 thread 1;

  56. Starting flashback at 12-MAY-18
  57. using channel ORA_DISK_1
  58. using channel ORA_DISK_2
  59. using channel ORA_DISK_3
  60. using channel ORA_DISK_4
  61. using channel ORA_DISK_5
  62. using channel ORA_DISK_6
  63. using channel ORA_DISK_7
  64. using channel ORA_DISK_8
  65. using channel ORA_SBT_TAPE_1
  66. using channel ORA_SBT_TAPE_2


  67. starting media recovery

  68. archived log for thread 1 with sequence 1 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_1.388.975952827
  69. archived log for thread 1 with sequence 2 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_2.389.975952829
  70. archived log for thread 1 with sequence 3 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_3.390.975952831
  71. archived log for thread 1 with sequence 4 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_4.391.975952833
  72. archived log for thread 1 with sequence 5 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_5.392.975952833
  73. archived log for thread 1 with sequence 6 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_6.393.975952837
  74. archived log for thread 1 with sequence 7 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_7.394.975952839
  75. archived log for thread 1 with sequence 8 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_8.395.975952839
  76. archived log for thread 1 with sequence 9 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_9.396.975952839
  77. archived log for thread 1 with sequence 10 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_10.397.975952843
  78. archived log for thread 1 with sequence 11 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_11.398.975952843
  79. archived log for thread 1 with sequence 12 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_12.399.975952843
  80. archived log for thread 1 with sequence 13 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_13.400.975952843
  81. archived log for thread 1 with sequence 14 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_14.401.975952843
  82. archived log for thread 1 with sequence 15 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_15.402.975952847
  83. archived log for thread 1 with sequence 16 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_16.403.975952847
  84. archived log for thread 1 with sequence 17 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_17.404.975952849
  85. archived log for thread 1 with sequence 18 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_18.405.975952849
  86. archived log for thread 1 with sequence 19 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_19.406.975952851
  87. archived log for thread 1 with sequence 20 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_20.407.975952857
  88. archived log for thread 1 with sequence 21 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_21.408.975952857
  89. archived log for thread 1 with sequence 22 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_22.409.975952859
  90. archived log for thread 1 with sequence 23 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_23.410.975952881
  91. archived log for thread 1 with sequence 24 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_24.411.975952881
  92. archived log for thread 1 with sequence 25 is already on disk as file +FRA/orcl/archivelog/2018_05_12/thread_1_seq_25.412.975952891
  93. media recovery complete, elapsed time: 00:00:25
  94. Finished flashback at 12-MAY-18

  95. RMAN>
复制代码


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 06:36 , Processed in 0.039537 second(s), 27 queries .

快速回复 返回顶部 返回列表