设为首页收藏本站

Botang唐波's Oracle Station

查看: 502|回复: 0

活动15/16次(2018-05-05星期六上下午)

[复制链接]

744

主题

1117

帖子

8077

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
8077
发表于 2018-5-5 09:48:40 | 显示全部楼层 |阅读模式
改动磁盘组的属性:
  1. [oracle@station90 ~]$ . oraenv
  2. ORACLE_SID = [orcl] ? +ASM
  3. The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid is /u01/app/oracle
  4. [oracle@station90 ~]$ sqlplus /nolog

  5. SQL*Plus: Release 11.2.0.1.0 Production on Sat May 5 09:45:45 2018

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

  7. SQL> conn / as sysasm
  8. Connected.
  9. SQL> alter diskgroup highdata set attribute  'compatible.asm'='11.1';

  10. Diskgroup altered.

  11. SQL> alter diskgroup highdata set attribute  'compatible.asm'='10.2';
  12. alter diskgroup highdata set attribute        'compatible.asm'='10.2'
  13. *
  14. ERROR at line 1:
  15. ORA-15032: not all alterations performed
  16. ORA-15242: could not set attribute compatible.asm
  17. ORA-15238: 10.2 is not a valid value for attribute compatible.asm
  18. ORA-15243: 10.2.0.0.0 is not a valid version number
复制代码
创建2M AU的磁盘组:
  1. create diskgroup highdata high redundancy
  2. failgroup fg1 disk 'ORCL:ASMDISK1' name highdata_0001
  3. failgroup fg2 disk 'ORCL:ASMDISK2' name highdata_0002
  4. failgroup fg3 disk 'ORCL:ASMDISK3' name highdata_0003
  5. attribute 'au_size'='2M';
复制代码
ASM快速镜像重同步:
1. 要数据库实例配合:
  1. SQL> alter diskgroup data offline disk data_0000  drop after 1h;
  2. alter diskgroup data offline disk data_0000  drop after 1h
  3. *
  4. ERROR at line 1:
  5. ORA-15032: not all alterations performed
  6. ORA-15283: ASM operation requires compatible.rdbms of 11.1.0.0.0 or higher


  7. SQL> alter diskgroup data set attribute 'compatible.rdbms'='11.1.0.0.0';

  8. Diskgroup altered.

  9. SQL> alter diskgroup data offline disk data_0000  drop after 1h;

  10. Diskgroup altered.

复制代码

a.png

模拟盘被拔出,清理:

[root@station90 桌面]# chmod 000 /dev/raw/raw7
[root@station90 桌面]# ls -l /dev/raw/raw7
c--------- 1 oracle oinstall 162, 7  5月  5 10:33 /dev/raw/raw7
[root@station90 桌面]# chown root:root /dev/raw/raw7
[root@station90 桌面]#

同时打开另一窗口,模拟工作负载:
  1. SQL> conn / as sysdba
  2. Connected.
  3. SQL> create table hr.tbig as select  * from dba_source;

  4. Table created.

  5. SQL> conn hr/oracle_4U
  6. ERROR:
  7. ORA-28002: the password will expire within 7 days


  8. Connected.
  9. SQL> insert into tbig select  * from tbig;

  10. 623407 rows created.

  11. SQL> commit;

  12. Commit complete.

  13. SQL> insert into tbig select  * from tbig;

  14. 1246814 rows created.

  15. SQL> commit;

  16. Commit complete.

  17. SQL>
复制代码
b.png

模拟磁盘重新插回去:
[root@station90 桌面]# chown oracleinstall /dev/raw/raw7
[root@station90 桌面]# chmod 775 /dev/raw/raw7
[root@station90 桌面]#

结论:在一个小时之内不会有任何重平衡操作:

watch -n 0.3 "ps aux | grep arb"
Screenshot.png


迅速上线,没有任何重平衡操作:
c.png

快速镜像重同步,由于硬件大故障无法online磁盘时的解决办法:
(破坏磁盘的办法:dd if=/dev/zero of=/dev/raw/raw7 bs=100M; 或者 dd if=/dev/zero of=/dev/oracleasm/disks/ASMDISK01 bs=100M;)

  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.1.0 Production on Sat May 5 14:46:25 2018

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

  4. SQL> conn / as sysasm
  5. Connected.
  6. SQL> alter diskgroup data offline disk data_0000  drop after 1h;

  7. Diskgroup altered.

  8. SQL> alter diskgroup data online disk data_0000  ;
  9. alter diskgroup data online disk data_0000
  10. *
  11. ERROR at line 1:
  12. ORA-15032: not all alterations performed
  13. ORA-15281: not all specified disks were brought ONLINE
  14. ORA-15284: ASM terminated ALTER DISKGROUP ONLINE
  15. ORA-15282: ASM disk "DATA_0000" is not visible cluster-wide


  16. SQL> alter diskgroup data drop  disk data_0000  ;
  17. alter diskgroup data drop  disk data_0000
  18. *
  19. ERROR at line 1:
  20. ORA-15032: not all alterations performed
  21. ORA-15084: ASM disk "DATA_0000" is offline and cannot be dropped.


  22. SQL> alter diskgroup data drop  disk data_0000  force  ;

  23. Diskgroup altered.


  24. SQL> alter diskgroup data add   failgroup  data_0000  disk  '/dev/raw/raw7' name   data_0000  ;

  25. Diskgroup altered.

  26. SQL>
复制代码

在线扩大磁盘组的大小(通过扩大磁盘):
  1. SQL*Plus: Release 11.2.0.3.0 Production on Sat May 12 10:49:31 2018

  2. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  3. SQL> conn / as sysasm
  4. Connected.
  5. SQL> alter diskgroup data resize disk data_0000;

  6. Diskgroup altered.

  7. SQL> alter diskgroup data resize disk data_0001 disk data_0002 disk data_0003  disk data_0004 disk data_0005  ;

  8. Diskgroup altered.

  9. SQL> alter diskgroup fra resize disk fra_0000 disk fra_0001 disk fra_0002    ;

  10. Diskgroup altered.

  11. SQL>
复制代码



----------------------------------------------------------------------------------------------------------------------------
闪回事务查询,在flashback_transaction_query中,没提交的事务也看得到,因为这个视图反映的是整个UNDO表空间基于时间和事务把ROWID精细整理过的样子。
  1. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  2. SQL> conn hr/oracle_4U
  3. ERROR:
  4. ORA-28002: the password will expire within 7 days


  5. Connected.
  6. SQL> select  salary from employees where employee_id=100;

  7.     SALARY
  8. ----------
  9.      24000

  10. SQL> update employees  set salary=30000   where employee_id=100;

  11. 1 row updated.
复制代码
ftq.png

闪回版本查询,基于flashback_transaction_query,其中versions_startscn就是flashback_transaction_query中的commit_scn:
startscn.png

由于闪回版本查询的特点,它往往是闪回表的向导。

-----------------------------------------------------------------------------------------------
在进行版本查询时,em把versions_startscn都减1,所以图形界面查出来的每一行都没提交
versions.png

firefox.png

----------------------------------------------------------------------------
as of闪回、versions闪回和闪回表都能跨过添加型的DDL:

实验过程:
  1. SQL> create table t05310_a ( a number ) ;

  2. Table created.

  3. SQL> insert into t05310_a values ( 1 ) ;

  4. 1 row created.

  5. SQL> commit;

  6. Commit complete.

  7. SQL> update t05310_a set a=2 ;

  8. 1 row updated.

  9. SQL> commit;

  10. Commit complete.

  11. SQL> alter table t05310_a  add ( b varchar2(20))  ;

  12. Table altered.

  13. SQL> select  * from t05310_a;

  14.          A B
  15. ---------- --------------------
  16.          2

  17. SQL> update t05310_a  set a=3 , b='c'  ;

  18. 1 row updated.

  19. SQL> commit;

  20. Commit complete.

  21. SQL> update t05310_a  set a=4 , b='d'  ;

  22. 1 row updated.

  23. SQL> commit;

  24. Commit complete.

  25. SQL> select  * from t05310_a;

  26.          A B
  27. ---------- --------------------
  28.          4 d

  29. SQL>
复制代码
ddl1.png

但是as of闪回、versions闪回和闪回表不能跨过删除型的DDL:

实验过程:
  1. SQL> alter table t05310_a drop (b) ;

  2. Table altered.

  3. SQL>
复制代码

ddl2.png

  1. SQL> select  a from t05310_a as of timestamp systimestamp-5/1440  ;
  2. select        a from t05310_a as of timestamp systimestamp-5/1440
  3.                *
  4. ERROR at line 1:
  5. ORA-01466: unable to read data - table definition has changed


  6. SQL> select  a from t05310_a as of timestamp systimestamp-1/1440  ;

  7.          A
  8. ----------
  9.          4

  10. SQL> select  a from t05310_a as of timestamp systimestamp-2/1440  ;

  11.          A
  12. ----------
  13.          4

  14. SQL> select  a from t05310_a as of timestamp systimestamp-3/1440  ;

  15.          A
  16. ----------
  17.          4

  18. SQL> select  a from t05310_a as of timestamp systimestamp-4/1440  ;
  19. select        a from t05310_a as of timestamp systimestamp-4/1440
  20.                *
  21. ERROR at line 1:
  22. ORA-01466: unable to read data - table definition has changed


  23. SQL> select  a from t05310_a as of timestamp systimestamp-4/1440  ;
  24. select        a from t05310_a as of timestamp systimestamp-4/1440
  25.                *
  26. ERROR at line 1:
  27. ORA-01466: unable to read data - table definition has changed


  28. SQL> select  a from t05310_a as of timestamp systimestamp-4/1440  ;
  29. select        a from t05310_a as of timestamp systimestamp-4/1440
  30.                *
  31. ERROR at line 1:
  32. ORA-01466: unable to read data - table definition has changed


  33. SQL> alter table t05310_a enable row movement;

  34. Table altered.

  35. SQL> select  a from t05310_a as of timestamp systimestamp-4/1440  ;
  36. select        a from t05310_a as of timestamp systimestamp-4/1440
  37.                *
  38. ERROR at line 1:
  39. ORA-01466: unable to read data - table definition has changed


  40. SQL> flashback  table t05310_a to timestamp   systimestamp-4/1440;
  41. flashback  table t05310_a to timestamp         systimestamp-4/1440
  42.                  *
  43. ERROR at line 1:
  44. ORA-01466: unable to read data - table definition has changed


  45. SQL>
复制代码

Flashback Transaction Query实际上是有包含system undo段的信息,我们的DDL表现成对数据字典表的DML,里头显示的是对象号:

  1. select  o.object_id
  2.   from  dba_objects o where o.object_name='T05310_A';  
  3.   
  4. --74613
  5. select  d.SUPPLEMENTAL_LOG_DATA_MIN,
  6.              d.SUPPLEMENTAL_LOG_DATA_PK,
  7.              d.SUPPLEMENTAL_LOG_DATA_UI,
  8.              d.SUPPLEMENTAL_LOG_DATA_FK,
  9.              d.SUPPLEMENTAL_LOG_DATA_ALL
  10.    from v_$database  d;
  11.    
  12.   alter database add  SUPPLEMENTAL log data;

  13. ---hr drop table t05310_a;
  14. select  * from flashback_transaction_query f
  15. where f.table_name='TAB' order by 3 desc;
复制代码
undo_sql:
[/code]undo_sql:
  1. insert
  2. into "SYS"."TAB[        DISCUZ_CODE_10     
  3.    ]quot;("OBJ#","DATAOBJ#","TS#","FILE#","BLOCK#","BOBJ#","TAB#","COLS","CLUCOLS","PCTFREE[
  4.        DISCUZ_CODE_10        ]quot;,"PCTUSED[        DISCUZ_CODE_10     
  5.    ]quot;,"INITRANS","MAXTRANS","FLAGS","AUDIT[        DISCUZ_CODE_10  
  6.    
  7.    ]quot;,"ROWCNT","BLKCNT","EMPCNT","AVGSPC","CHNCNT","AVGRLN","AVGSPC_FLB","FLBCNT","ANALYZETIME","SAMPLESIZE","DEGREE","INSTANCES","INTCOLS","KERNELCOLS","PROPERTY","TRIGFLAG","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6")
  8. values
  9. ('74613','74613','4','4','650',NULL,NULL,'1',NULL,'10','40','1','255','1073872897','--------------------------------------',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1','1','536870912','0','736',NULL,NULL,NULL,NULL,TO_DATE('05-MAY-18',
  10. 'DD-MON-RR'));
复制代码
alter database add  SUPPLEMENTAL log data  (primary key )columns;

闪回事务实验用的脚本:
t05310.sql (388 Bytes, 下载次数: 98)
回复

使用道具 举报

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

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-11-18 01:57 , Processed in 0.125888 second(s), 27 queries .

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