系统层级、经验闲谈

Oracle库system和sysaux表空间情况分析

留下评论

    SYSAUX表空间被称为系统辅助表空间，是10g版本开始推出的新功能，主要的目的是为SYSTEM表空间减负，Oracle对SYSTEM表空间的维护有一套独立的体系，对SYSTEM表空间操作会占用额外的CPU资源，而且效率低下，在10g版本，增加了SYSAUX辅助表空间，将EM、AWR等组件的表从SYSTEM表空间挪到了SYSAUX表空间中，这样大大减少了SYSTEM表空间的消耗，也减少了Oracle对SYSTEM表空间维护的成本。

    但是有几个比较坑爹的组件需要的表并没有挪到SYSAUX表空间，比如常见的审计用到的AUD$表，很多DBA都可能遇到SYSTEM表空间使用率过高，查询发现是AUD$表很大导致的，我们的数据库正好打开了审计功能，正好可以拿来做实验。下面先查询下SYSTEM和SYSAUX表空间的使用率。

SELECT *
  FROM (SELECT D.TABLESPACE_NAME,
               SPACE || 'M' "SUM_SPACE(M)",
               BLOCKS "SUM_BLOCKS",
               SPACE – NVL(FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
               ROUND((1 – NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)",
               FREE_SPACE || 'M' "FREE_SPACE(M)"
          FROM (SELECT TABLESPACE_NAME,
                       ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
                       SUM(BLOCKS) BLOCKS
                  FROM DBA_DATA_FILES
                 GROUP BY TABLESPACE_NAME) D,
               (SELECT TABLESPACE_NAME,
                       ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
                  FROM DBA_FREE_SPACE
                 GROUP BY TABLESPACE_NAME) F
         WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
        UNION ALL
        SELECT D.TABLESPACE_NAME,
               SPACE || 'M' "SUM_SPACE(M)",
               BLOCKS SUM_BLOCKS,
               USED_SPACE || 'M' "USED_SPACE(M)",
               ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
               NVL(FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
          FROM (SELECT TABLESPACE_NAME,
                       ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
                       SUM(BLOCKS) BLOCKS
                  FROM DBA_TEMP_FILES
                 GROUP BY TABLESPACE_NAME) D,
               (SELECT TABLESPACE_NAME,
                       ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
                       ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
                  FROM V$TEMP_SPACE_HEADER
                 GROUP BY TABLESPACE_NAME) F
         WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
         ORDER BY 1)
 WHERE TABLESPACE_NAME IN ('SYSAUX', 'SYSTEM');

结果：

1    SYSAUX    684.69M    87640     598.56M      87.42%    86.13M
2    SYSTEM    12681M    1623168    11243.95M    88.67%    1437.05M

oracle数据库清理和回收system和sysaux表空间

http://m.blog.itpub.net/26736162/viewspace-2140644/

https://www.cnblogs.com/guilingyang/p/5753260.html

http://www.jiagulun.com/thread-271819-1-1.html

系统层级、经验闲谈

oracle审计功能如何实现启动关闭功能

留下评论

一 审计功能的参数控制
audit_trail 参数的值可以设置为以下几种

1. NONE：不开启
2. DB：开启审计功能
3. OS：审计记录写入一个操作系统文件。
4. TRUE：与参数DB一样
5. FALSE：不开启审计功能。
这个参数是写在spfile里，需要重启数据库。 

二 查看是否审计功能是否启动
SQL> show parameter audit
NAME                                 TYPE        VALUE
———————————— ———– ——————————
audit_file_dest                      string      /u01/app/oracle/admin/PROD/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      NONE

 

三 开启审计
SQL> conn /as sysdba
SQL> show parameter audit
NAME                                 TYPE        VALUE
———————————— ———– ——————————
audit_file_dest                      string      /u01/app/oracle/admin/PROD/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      NONE

SQL> alter system set audit_sys_operations=TRUE scope=spfile;–审计管理用户(以sysdba/sysoper角色登陆)
SQL> alter system set audit_trail=db,extended scope=spfile;

重启实例
SQL> show parameter audit
NAME                                 TYPE        VALUE
———————————— ———– ——————————
audit_file_dest                      string      /u01/app/oracle/admin/PROD/adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      DB, EXTENDED     
（完成）

四 关闭审计

SQL> conn /as sysdba
SQL> show parameter audit
SQL> alter system set audit_trail = none scope=spfile;
重启实例。

系统层级、经验闲谈

Oracle 生成AWR分析文件

留下评论

通过数据库用户：

cd $ORACLE_HOME/rdbms/admin

@$ORACLE_HOME/rdbms/admin/awrrpt.sql
SQL> @awrrpt.sql

输入相应的参数即可在admin目录下找到生成的文件。

说明：进入sqlplus前的目录，一定要是数据库用户能够有权限写入文件的目录。

 

参考：

–如何分析Oracle_AWR报告

https://blog.csdn.net/heqiyu34/article/details/52981580

https://blog.csdn.net/zq9017197/article/details/7345352

–awr多个实例名的情况处理

https://www.cnblogs.com/kerrycode/archive/2016/07/18/5681816.html

–利用addmrpt.sql

https://blog.csdn.net/leshami/article/details/70859672

–值得参考

https://blog.csdn.net/shipeng1022/article/details/52094355

 

 

 

 

 

 

 

 

 

 

 

 

成指定实例的SQL语句的AWR报告：

@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql

生成比较期间的AWR报告：

@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

生成指定实例比较期间的AWR报告：

@$ORACLE_HOME/rdbms/admin/awrddrpi.sql

生成活动会话历史(ASH)的AWR报告：

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

生成指定实例的活动会话历史(ASH)的AWR报告：

@$ORACLE_HOME/rdbms/admin/ashrpti.sql 

——ADDM

https://blog.csdn.net/leshami/article/details/70859672

Oracle性能调整的三把利剑–ASH,AWR,ADDM – wolf – CSDN博客

 

 

 

 

ASH (Active Session History)
ASH以V$SESSION为基础，每秒采样一次，记录活动会话等待的事件。不活动的会话不会采样，采样工作由新引入的后台进程MMNL来完成。
ASH buffers 的最小值为1MB,最大值不超过30MB。内存中记录数据。期望值是记录一小时的内容。

生成ASH报告：
SQLPLUS>@?/rdbms/ashrpt.sql

ASH 内存记录数据始终是有限的，为了保存历史数据，引入了自动负载信息库(AutomaticWorkload Repository ,AWR) 由后台进程MMON完成。ASH信息同样被采集写出到AWR负载库中。由于内存不是足够的，所以MMNL进程在ASH写满后会将信息写出到AWR负载库中。ASH全部写出是不可接受的，所以一般只写入收集的10%的数据量，而且使用direct-pathinsert完成，尽量减少日志的生成，从而最小化数据库性能影响。

写出到AWR负载库的ASH信息记录在AWR的基础表wrh$active_session_hist中，wrh$active_session_hist是一个分区表，Oracle会自动进行数据清理。

AWR(Automatic Workload Repository)自动工作负载信息库
AWR是Oracle 10g中的一个新特性，类似于10g以前的statspack。不过在使用上要比statspack简单，提供的性能指标要比statspack多很多，能更好的帮助DBA来发现数据库的性能瓶颈。
AWR 是Oracle安装好后自动启动的，不需要特别的设置。收集的统计信息存储在SYSAUX表空间SYS模式下，以WRM$_*和WRH$_*的格式命名，默认会保留最近7天收集的统计信息。每个小时将收集到的信息写到数据库中，这一系列操作是由一个叫MMON的进程来完成的。

AWR存储的数据分类：
WRM$表存储AWR的元数据（awrinfo.sql脚本）
WRH$表存储采样快照的历史数据（awrrpt.sql脚本）
WRI$表存储同数据库建议功能相关的数据（ADDM相关数据）

一.生成AWR报告：
SQL>@?/rdbms/admin/awrrpt

根据向导来完成AWR报告的生成。需要注意的是，在选择时间范围的时候，中间不能有停机(如果显示的时间中间有空白行，表示有停机情况)。在选择报告类型的时候一般使用默认的HTML，方便查看。

二.查看数据库的AWR的设置：
SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL
—————————————————————————
RETENTION
—————————————————————————
+00000 01:00:00.0(每小时收集一次)
+00007 00:00:00.0(保留7天)

三.修改默认设置：
begin
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 20,
retention => 2*24*60);
end;

修改成每20分钟收集一次统计量，保留最近的2天统计量信息。

四.手动收集一次数据库的统计信息：
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

我们还可以通过DBMS_WORKLOAD_REPOSITORY包完成对基线，默认设置的修改等操作。

ADDM (Automatic Database Diagnostic Monitor AWR)
是Oracle内部的一个顾问系统，能够自动的完成最数据库的一些优化的建议，给出SQL的优化，索引的创建，统计量的收集等建议。

ADDM报告生成：
SQLPLUS>@?/rdbms/addmrpt.sql

Oracle 性能调整最重要的就是对最影响性能的SQL的调整。在一个应用中，能够影响到数据库的只有SQL，也只能是SQL。我们不能一味依靠增强硬件，修改系统、数据库参数来提高数据库的性能。更多的应该关注那些最影响性能的SQL语句。ASH报告、AWR报告、ADDM报告都能够找出最影响性能的SQL的工具。在分析ASH报告、AWR报告的时候，最重要的就是关注SQL Statistics，SQL Statistics中最应该关注的是SQL ordered byGets和SQL ordered byReads两个指标。大量的Gets(逻辑读)会占用大量的CPU时间。大量的Reads(物理读)会引起IO的瓶颈出现。一般情况下，大量的Gets会伴随着大量的Reads出现。当然，我们可以通过增大SGA的大小来减少Reads的量。通过这两个指标找到了最影响性能的SQL，这是首要的，也是必要的。下一步就可以通过创建索引，调整SQL来提高SQL单独执行时的性能。减少SQL执行时出现的高Gets,Reads。当然整体的性能影响还和 excutions有关，如果这条SQL执行的次数过多，累加起来量还是很大的。那么就可以考虑通过在应用上缓存等手段来减少SQL执行的次数。另外还有一个需要注意的问题就是在开发过程中SQL一定要使用绑定变量，来减少硬解析(大量的硬解析也会消耗大量的CPU时间，占用大量的Latch)。在开发过程中有个原则就是：小事务。操作完成及时的提交。

我们使用这么多种方式、报告只有一个唯一的目的：找出最影响系统性能的SQL语句。找到SQL下一步就是对它进行调整了。

我们在监控数据库时，如果是当前正在发生的问题，我们可以通过v$session+v$sqlarea来找出性能最差的SQL语句。如果在一个小时以内发生的我们可以通过生成ASH报告来找出SQL。如果是1小时以上或几天我们可以通过AWR报告来找出几小时，几天以来最影响系统的SQL语句。ADDM报告基于AWR库，默认可以保存30天的ADDM报告。

我们也可以直接查询试图：
v$session                                     (当前正在发生)
v$session_wait                           (当前正在发生)
v$session_wait_history             (会话最近的10次等待事件)
v$active_session_history          (内存中的ASH采集信息，理论为1小时)
wrh$_active_session_history    (写入AWR库中的ASH信息，理论为1小时以上)
dba_hist_active_sess_history   (根据wrh$_active_session_history生成的视图)

手动执行一个快照：

Exec dbms_workload_repository.create_snapshot;


 

 

 

经验闲谈、转载文章（档）

Oracle 回滚(ROLLBACK)和撤销(UNDO)

留下评论

一、回滚(ROLLBACK)和撤销(UNDO)

    回滚和前滚是保证Oracle数据库中的数据处于一致性状态的重要手段。   

    在9i版本以前

        Oracle使用数据库中的回滚段来实现未提交数据或因系统故障导致实例崩溃时进行回滚操作

        每一个表空间需要创建回滚段，各个表空间对回滚段实现各自的管理

    在9i及后续版本

        提供了一种新的回滚数据的管理方式，即使用Oracle自动管理的撤销(Undo)表空间

        自动撤销管理表空间统一管理所有DML的回滚操作，简化了对于回滚工作的管理

        在9i,10g中的回滚段仅仅用作保留向后兼容

        撤销段代替了原有版本中的回滚段，因此本文所有描述均使用撤销   

    撤销的实质意味着将所作的修改退回到修改前的状态，即倒退所有DML语句。       

二、撤销段中的内容及相关特性

    对于任何DML操作而言，必须同时处理数据块和撤销块，并且还会生成重做信息

    在ACID中，A、C、I要求生成撤销，D则要求生成重做

    INSERT：

        撤销段记录插入记录的rowid,如果需要撤销，则根据rowid将该记录删除即可

    UPDATE:

        撤销段记录被更新字段的原始值，撤销时将原始值覆盖新值即可

    DELETE：

        撤销段记录整行的数据，撤销时执行反向操作将该记录插入原表

 

    由上可知，UNDO段中的内容总结如下：

        数据为修改之前的副本

        从每个改变数据的事务中获得

        在事务结束前一直被保留   

    UNDO段中数据的作用：

        用于回滚操作

        读一致性和闪回查询

        用于事务失败时的恢复       

    UNDO段与事务：

        一个事物的启动，Oracle将为其分配仅仅一个UNDO段,若该段用完,则Oracle会自动为该UNDO段添加另一个区间(extent)

        一个UNDO段能够同时为多个事务服务       

    UNDO段与UNDO表空间：

        UNDO段中的内容存储在UNDO表空间

        任意给定时刻只能使用一个UDNO表空间

        UNDO表空间必须被创建为持久的、本地管理、可自动扩展的表空间

        正在使用的UNDO表空间不能撤销或删除

        UNDO表空间使用循环写的方式，与联机日志文件写相似，不同的是UNDO中可以设置了undo_retention 保留时间   

    UNDO段的两种管理方式：

        AUTO    自动管理(推荐)

        MANUAL  手动管理(仅保留)           

三、与撤销相关的几个参数

    –查看本机中Oracle的版本

        SQL> SELECT * FROM v$version;

        BANNER

        —————————————————————-

        Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod

        PL/SQL Release 10.2.0.1.0 – Production

        CORE    10.2.0.1.0      Production

        TNS for Linux: Version 10.2.0.1.0 – Production

        NLSRTL Version 10.2.0.1.0 – Production

    –查看和UNDO相关的参数

        SQL> SHOW PARAMETER undo;

        NAME                                 TYPE        VALUE

        ———————————— ———– ——————————

        undo_management                      string      MANUAL

        undo_retention                       integer     900

        undo_tablespace                      string      UNDOTBS1

           

    undo_management：

        设置数据库的撤销段是否使用自动管理模式，值可以为auto或manual，当为manual时将不使用撤销段，即不使用自动管理模式

        该参数为静态参数，修改后需重启实例才能生效      

    undo_retention：

        指定撤销段数据在undo段中为非活动状态后被覆盖前保留的时间，单位为秒。在undo_management位auto时生效，为动态参数

       

    undo_tablespace：

        指定使用哪个表空间来实现数据的撤销，在undo_management位auto时生效，为动态参数       

    retention guarantee子句：

        保证撤销保留，使用下面的操作来实现

            ALTER TABLESPACE undo_tablespace_name RETENTION GUARANTEE;

    –下面的查询中是当undo_management为manual时的结果集，可以看出撤销表空间的撤销段都处于offline状态   

        SQL> SELECT segment_name,tablespace_name,status FROM dba_rollback_segs;

 

        SEGMENT_NAME                   TABLESPACE_NAME                STATUS

        —————————— —————————— —————-

        SYSTEM                         SYSTEM                         ONLINE

        _SYSSMU1$                      UNDOTBS1                       OFFLINE

        _SYSSMU2$                      UNDOTBS1                       OFFLINE

        _SYSSMU3$                      UNDOTBS1                       OFFLINE

        _SYSSMU4$                      UNDOTBS1                       OFFLINE

        _SYSSMU5$                      UNDOTBS1                       OFFLINE

        _SYSSMU6$                      UNDOTBS1                       OFFLINE

        _SYSSMU7$                      UNDOTBS1                       OFFLINE

        _SYSSMU8$                      UNDOTBS1                       OFFLINE

        _SYSSMU9$                      UNDOTBS1                       OFFLINE

        _SYSSMU10$                     UNDOTBS1                       OFFLINE

 

 

    –在undo_management 参数为manual时，对scott.emp插入一条新记录，收到了错误提示

    –非系统表空间不能够使用回滚段

        SQL> INSERT INTO scott.emp(empno,ename,salary)

          2  VALUES(6666,'Jenney',3000);

        INSERT INTO scott.emp(empno,ename,salary)

                          *

        ERROR at line 1:

        ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

 

    –查看段的类型，发现仅仅system表空间存在ROLLBACK 段，所以前一条插入语句收到错误提示

        SQL> SELECT DISTINCT segment_type,tablespace_name FROM dba_segments

          2  ORDER BY tablespace_name;

 

        SEGMENT_TYPE       TABLESPACE_NAME

        —————— ——————————

        INDEX              EXAMPLE

        INDEX PARTITION    EXAMPLE

        LOBINDEX           EXAMPLE

        LOBSEGMENT         EXAMPLE

        NESTED TABLE       EXAMPLE

        TABLE              EXAMPLE

        TABLE PARTITION    EXAMPLE

        INDEX              SYSAUX

        INDEX PARTITION    SYSAUX

        LOB PARTITION      SYSAUX

        LOBINDEX           SYSAUX

 

        SEGMENT_TYPE       TABLESPACE_NAME

        —————— ——————————

        LOBSEGMENT         SYSAUX

        NESTED TABLE       SYSAUX

        TABLE              SYSAUX

        TABLE PARTITION    SYSAUX

        CLUSTER            SYSTEM

        INDEX              SYSTEM

        LOBINDEX           SYSTEM

        LOBSEGMENT         SYSTEM

        NESTED TABLE       SYSTEM

        ROLLBACK           SYSTEM       –与之前的版本兼容的回滚段

        TABLE              SYSTEM

 

        SEGMENT_TYPE       TABLESPACE_NAME

        —————— ——————————

        TABLE              TBS1

        TYPE2 UNDO         UNDOTBS1     –9i之后使用的撤销段

        INDEX              USERS

        LOBINDEX           USERS

        LOBSEGMENT         USERS

        NESTED TABLE       USERS

        TABLE              USERS

 

        –下面将undo_management改为支持自动管理，需要重启实例

        SQL> ALTER SYSTEM SET undo_management = 'auto' SCOPE = SPFILE;

 

        System altered.

 

        SQL> SHUTDOWN IMMEDIATE;

        Database closed.

        Database dismounted.

        ORACLE instance shut down.

        SQL> STARTUP;

        ORACLE instance started.

 

        Total System Global Area  251658240 bytes

        Fixed Size                  1218796 bytes

        Variable Size              67110676 bytes

        Database Buffers          180355072 bytes

        Redo Buffers                2973696 bytes

        Database mounted.

        Database opened.

 

    –再次查看dba_rollback_segs视图所有的撤销段全部处于online状态

    –注意第一行为system表空间的撤销段，用于系统表空间的撤销

        SQL> SELECT segment_name,tablespace_name,status FROM dba_rollback_segs;

 

        SEGMENT_NAME                   TABLESPACE_NAME                STATUS

        —————————— —————————— —————-

        SYSTEM                         SYSTEM                         ONLINE 

        _SYSSMU1$                      UNDOTBS1                       ONLINE

        _SYSSMU2$                      UNDOTBS1                       ONLINE

        _SYSSMU3$                      UNDOTBS1                       ONLINE

        _SYSSMU4$                      UNDOTBS1                       ONLINE

        _SYSSMU5$                      UNDOTBS1                       ONLINE

        _SYSSMU6$                      UNDOTBS1                       ONLINE

        _SYSSMU7$                      UNDOTBS1                       ONLINE

        _SYSSMU8$                      UNDOTBS1                       ONLINE

        _SYSSMU9$                      UNDOTBS1                       ONLINE

        _SYSSMU10$                     UNDOTBS1                       ONLINE

 

 

    由上面的示例可知：

        ROLLBACK 段：      –与之前的版本兼容的回滚段

        TYPE2 UNDO 段：    –9i之后使用的撤销段

        关于回滚，一个时刻仅能使用一种类段类型，即要么使用与以前版本兼容的回滚段，要么使用撤销段

        事实上，在9i之后仅仅支持撤销段，从上面错误的提示即可证实

 

    –查看DML语句产生的事务

        SQL> SHOW USER;                                   

        USER is "SYS"

        SQL> SELECT * FROM scott.emp WHERE ename = 'SCOTT';

 

             EMPNO ENAME           JOB              MGR HIREDATE      SALARY     DEPTNO

        ———- ————— ——— ———- ——— ———- ———-

              7788 SCOTT           ANALYST         7566 19-APR-87       3500         20

             

        SQL> UPDATE scott.emp SET sal = sal * 2 WHERE  ename = 'SCOTT';

 

        1 row updated.

 

        SQL> SELECT addr,xidusn,status,start_time,used_ublk

          2  FROM v$transaction;

 

        ADDR         XIDUSN STATUS           START_TIME            USED_UBLK

        ——– ———- —————- ——————– ———-

        2DA2B17C          9 ACTIVE           07/10/10 20:29:08             1

 

    –查看当前哪些用户使用撤销段以及段的大小，启动时间，活动状态等

        SQL> SELECT t.xidusn,t.start_time,t.used_ublk,t.status,

                s.username,r.segment_name

             FROM v$transaction t

                JOIN v$session s

                    ON t.ses_addr = s.saddr

                JOIN dba_rollback_segs r

                    ON r.segment_id = t.xidusn ;

 

            XIDUSN START_TIME            USED_UBLK STATUS           USERNAME                       SEGMENT_NAME

        ———- ——————– ———- —————- —————————— ————-

                 9 07/10/10 20:29:08             1 ACTIVE           SYS                            _SYSSMU9$

   

四、UNDO表空间的创建与管理 

    创建UNDO表空间

        创建语法：

           CREATE UNDO TABLESPACE tablespace_name DATAFILE '…' SIZE n

 

        更多表空间的创建：

            请参照：Oracle 表空间与数据文件

 

    切换UNDO表空间 

        实例中允许多个UNDO表空间存在

        可以从一个UNDO表空间切换到另外一个UNDO表空间

        任一时刻只能有一个UNDO表空间被指定

        使用ALTER SYSTEM SET undo_tablespace = undo_tablespace_name实现切换

       

    删除UNDO表空间

        DROP TABLESPACE undo_tablespace_name

        任意实例的UNDO表空间在非活动状态可以删除

        对于活动状态的UNDO表空间，应当先将切换到其它表空间，在所有事务完成后再删除该表空间

       

    演示创建、切换及删除UNDO表空间

    –查看当前系统中的表空间   

        SQL> SELECT file_name,tablespace_name FROM dba_data_files;

 

        FILE_NAME                                                    TABLESPACE_NAME

        ———————————————————— ——————————

        /u01/app/oracle/oradata/orcl/tbs1_2.dbf                      TBS1

        /u01/app/oracle/oradata/orcl/tbs1_1.dbf                      TBS1

        /u01/app/oracle/oradata/orcl/example01.dbf                   EXAMPLE

        /u01/app/oracle/oradata/orcl/users01.dbf                     USERS

        /u01/app/oracle/oradata/orcl/sysaux01.dbf                    SYSAUX

        /u01/app/oracle/oradata/orcl/undotbs01.dbf                   UNDOTBS1

        /u01/app/oracle/oradata/orcl/system01.dbf                    SYSTEM

 

    –创建一个新的UNDO表空间undo2

        SQL> CREATE UNDO TABLESPACE undo2

          2  DATAFILE '/u01/app/oracle/oradata/orcl/undotbs02.dbf' SIZE 3M

          3  AUTOEXTEND ON;

 

        Tablespace created.

 

        SQL> SELECT file_name,tablespace_name FROM dba_data_files WHERE tablespace_name LIKE 'UNDO%';

 

        FILE_NAME                                                    TABLESPACE_NAME

        ———————————————————— ——————————

        /u01/app/oracle/oradata/orcl/undotbs01.dbf                   UNDOTBS1

        /u01/app/oracle/oradata/orcl/undotbs02.dbf                   UNDO2

 

    –查看当前系统使用的UNDO表空间为UNDOTBS1

        SQL> SELECT name,value FROM v$parameter WHERE name LIKE 'undo%';

 

        NAME                           VALUE

        —————————— ————————————————–

        undo_management                AUTO

        undo_tablespace                UNDOTBS1

        undo_retention                 900

 

    –创建一张表tb_test用于演示，假定该会话为session1

        SQL> CREATE TABLE tb_test

          2  (

          3      ID INT,

          4      Name VARCHAR2(20)

          5  );

 

        Table created.

 

    –插入一条记录到tb_test，此时未提交将产生UNDO 信息

        SQL> INSERT INTO tb_test SELECT 1,'Robinson' FROM dual;

 

        1 row created.

 

    –此时打开另外一个回话，假定为session2，在session2中切换表空间

        SQL> ALTER SYSTEM SET undo_tablespace = 'undo2'; 

 

        System altered.   –undotbs1中有未提交的事务，竟然可以成功切换？如此这般闪回时估计会有问题

 

        SQL> SHOW PARAMETER undo;

 

        NAME                                 TYPE        VALUE

        ———————————— ———– ——————————

        undo_management                      string      AUTO

        undo_retention                       integer     900

        undo_tablespace                      string      undo2

 

        –在session1中执行commit

        SQL> COMMIT;

 

        Commit complete.   –成功执行了commit,且下面的查询看到了提交后的结果

 

        SQL> SELECT * FROM tb_test;

 

                ID NAME

        ———- ——————————

                 1 Robinson

         

    –登出系统后再次查看，结果依然存在，比较纳闷       

        SQL> exit

        Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production

        With the Partitioning, OLAP and Data Mining options

        [uniread] Saved history (716 lines)

        [oracle@robinson ~]$ sqlplus / as sysdba;

 

        SQL*Plus: Release 10.2.0.1.0 – Production on Sat Jul 10 21:29:36 2010

 

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

 

 

        Connected to:

        Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production

        With the Partitioning, OLAP and Data Mining options

 

        SQL> SELECT * FROM tb_test;

 

                ID NAME

        ———- ——————–

                 1 Robinson

         

    –删除UNDO表空间

    –在session1中插入一条新记录

        SQL> INSERT INTO tb_test SELECT 2,'Jack' FROM DUAL;

 

        1 row created.

 

    –在session2中将撤销表空间切换为undotbs1

        SQL> ALTER SYSTEM SET undo_tablespace = 'undotbs1';

 

        System altered.

 

    –紧接着在该回话中删除undo2，提示正在使用

        SQL> DROP TABLESPACE undo2;

        DROP TABLESPACE undo2

        *

        ERROR at line 1:

        ORA-30013: undo tablespace 'UNDO2' is currently in use

 

    –在session1中提交事务

        SQL> COMMIT;

 

        Commit complete.

    –在session2中再次删除表空间undo2，收到了相同的错误提示

        SQL> /

        DROP TABLESPACE undo2

        *

        ERROR at line 1:

        ORA-30013: undo tablespace 'UNDO2' is currently in use

 

        SQL> SHOW PARAMETER undo   –查看的确是已切换到undotbs1

 

        NAME                                 TYPE        VALUE

        ———————————— ———– ——————————

        undo_management                      string      AUTO

        undo_retention                       integer     900

        undo_tablespace                      string      undotbs1

 

    –在session2中再次执行表空间切换到undotbs1

        SQL> ALTER SYSTEM SET undo_tablespace = 'undotbs1';

 

        System altered.

 

    –此时undo2成功删除，可以看出需要在将活动事务提交或回滚后，再切换之后才能成功删除撤销表空间

        SQL> DROP TABLESPACE undo2;

 

        Tablespace dropped.

 

        SQL> ho ls $ORACLE_BASE/oradata/orcl;

        control01.ctl  redo03.log   redo2.log     system01.dbf   undotbs02.dbf

        control02.ctl  redo04.log   redo3.log     tbs1_1.dbf     users01.dbf

        example01.dbf  redo07.log   redo7.log     tbs1_2.dbf

        redo01.log     redo08.log   redo8.log     temp01.dbf

        redo02.log     redo1.log    sysaux01.dbf  undotbs01.dbf

 

    –删除UNDO表空间的物理文件

        SQL> ho rm $ORACLE_BASE/oradata/orcl/undotbs02.dbf;

 

    –在session1中可以看到两条记录也被成功插入

        SQL> SELECT * FROM tb_test;                   

 

                ID NAME

        ———- ——————–

                 1 Robinson

                 2 Jack

                     

    –查看当前撤销表空间的大小

        SQL> SELECT tablespace_name,bytes/1024/1024 FROM dba_data_files

          2  WHERE tablespace_name = 'UNDOTBS1';

 

        TABLESPACE_NAME                BYTES/1024/1024

        —————————— —————

        UNDOTBS1                                    30

 

    –循环插入记录到tb_test后查看undo表空间的使用情况

        SQL> BEGIN

          2  FOR i IN 1..20000

          3  LOOP

          4     INSERT INTO tb_test VALUES(i,'Unkown Name');

          5  END LOOP;

          6  END;

          7  /

 

        PL/SQL procedure successfully completed.   

 

    –可以看到UNDO 表空间只用了个块

        SQL> SELECT addr,xidusn,used_ublk FROM v$transaction;

 

        ADDR         XIDUSN  USED_UBLK

        ——– ———- ———-

        2D9FC160          6        174

   

        SQL> SELECT 174 * 8 || 'KB' FROM dual;

 

        174*8|

        ——

        1392KB

   

五、计算UNDO表空间的大小

    计算公式：

        MAX(undoblks)/600 * MAX(maxquerylen)   位于v$undostat

           * db_block_size                     位于v$parameter

   

    –创建演示环境

        SQL> INSERT INTO tb_test SELECT employee_id,first_name FROM hr.employees;

 

        107 rows created

        SQL> INSERT INTO tb_test SELECT * from tb_test;

 

        109 rows created.

 

    –多次执行上述命令，下面是的tb_test表中的记录数

        SQL> /

 

        892928 rows created.

        SQL> COMMIT;

 

        Commit complete.

   

    –查看当前undo表空间的大小

        SQL> SELECT t.name,d.name,d.bytes/1024/1024 as TotalSize ,t.flashback_on,d.status

          2  FROM v$tablespace t

          3  JOIN v$datafile d

          4  USING (ts#)

          5  WHERE t.name LIKE 'UNDO%';

         

        NAME                              NAME                                         TOTALSIZE FLA STATUS

        ——————————— ——————————————- ———- — ——-

        UNDOTBS1                          /u01/app/oracle/oradata/orcl/undotbs01.dbf          30 YES ONLINE

 

    –将undo表空间修改为RETENTION GUARANTEE及关闭自动扩展

        SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

 

        Tablespace altered.

 

        SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/undotbs01.dbf' AUTOEXTEND OFF;

        Database altered.   

        SQL> SELECT tablespace_name,contents,retention FROM dba_tablespaces

          2  WHERE tablespace_name LIKE 'UNDO%';

 

        TABLESPACE_NAME                CONTENTS  RETENTION

        —————————— ——— ———–

        UNDOTBS1                       UNDO      GUARANTEE

 

    –修改保留时间为分钟

        SQL> ALTER SYSTEM SET undo_retention = 120;

 

        System altered.

   

    –循环删除tb_test中的记录，提示undo表空间空间容量不够

        SQL> BEGIN

          2      FOR i IN 1..1000    

          3          LOOP

          4              DELETE FROM tb_test WHERE rownum < 1001;

          5              COMMIT;

          6          END LOOP;

          7  END;

          8  /

        BEGIN

        *

        ERROR at line 1:

        ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

        ORA-06512: at line 4       

    –修改回话的时间参数

        SQL> ALTER SESSION SET nls_date_format='yyyy-mm-dd HH24:MI:SS';

        Session altered.

    –查看v$undostat视图，获得相关信息

        SQL> SELECT begin_time,end_time,undoblks,maxquerylen, ssolderrcnt,nospaceerrcnt

          2   FROM v$undostat;

 

        BEGIN_TIME          END_TIME              UNDOBLKS MAXQUERYLEN SSOLDERRCNT NOSPACEERRCNT

        ——————- ——————- ———- ———– ———– ————-

        2010-07-12 19:12:18 2010-07-12 19:22:18          6           0           0             0

        2010-07-12 19:02:18 2010-07-12 19:12:18          9           0           0             0

        2010-07-12 18:52:18 2010-07-12 19:02:18         47           0           0             0

        2010-07-12 18:42:18 2010-07-12 18:52:18       2136           0           0             1

        2010-07-12 18:32:18 2010-07-12 18:42:18          6           0           0             0

        2010-07-12 18:22:18 2010-07-12 18:32:18        413        1541           0             0

        2010-07-12 18:12:18 2010-07-12 18:22:18        179         938           0             0

        2010-07-12 18:02:18 2010-07-12 18:12:18          6           0           0             0

    –计算undo表空间所需的大小

        SQL> SELECT (

          2    (SELECT MAX(undoblks)/600 * MAX(maxquerylen) FROM v$undostat) *

          3      (SELECT value FROM v$parameter WHERE name = 'db_block_size'))/1024/1024 as Need_Size

          4  FROM dual;

 

         NEED_SIZE

        ———-

        42.8590625       

    –取消撤销保留选项

        SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

        Tablespace altered       

六、UNDO配额

        对于超长的事务或不当的SQL脚本将耗用大量的UNDO表空间，使用UNDO表空间配额可以提高资源的利用率

        对于不同组的用户可以分配不同的最大UNDO表空间配额

        当某个组超出了最大的资源限制，则该组不允许新的事务产生，直到当前组的UNDO表空间释放或终止       

七、撤销常见的两个错误

    1.ORA-1555 snapshot too old 快照过旧错误的解决

        配置合适的保留时间(undo_retention)

        调整undo表空间的大小

        考虑保证撤销保留的使用(retention guarantee)       

    2.ORA-30036 unable to extend segment in undo tablespace 无法扩展撤销表空间内的撤销段

        调整undo表空间的大小

        确保大量的事务能够周期性的提交       

八、UNDO涉及的几个相关视图：

        V$TRANSACTION

        V$SESSION

        DBA_ROLLBACK_SEGS   –显示所有的segments

        V$ROLLSTAT

        V$UNDOSTAT

        V$ROLLNAME          –显示当前在线的segments   

        关于UNDO涉及视图的更多信息，请参考oracle的在线文档

      转载自：http://blog.csdn.net/leshami/article/details/5731158

系统层级、经验闲谈

Oracle DataGuard与GoldenGate比较

留下评论

一、复制方式
• Golden Gate
可提供秒一级的大量数据实时捕捉和投递，无法实现同步复制；
• Data Guard
    最大保护—Maximum protection
    最大可用—Maximum availability
    最大性能—Maximum performance
最大保护，最大可用模式都需要同步传输日志，此时会大大加重OracleLGWR或ARCH进程的工作量，严重影响源数据库性能，因此使用DataGuard做容灾一般都采用其最大性能模式中的异步方式。Data Guard的异步日志传输方式有两种方式，一个是通过LGWR进程异步传输Redo Log，另外一种是通过ARCH进程只做归档日志传输。

二、性能比较

• GoldenGate解析Oracle日志，只抓取其中数据变化，大概为原日志量的四分之一左右；GoldenGate还集成了数据压缩功能，压缩比可以达到9:1左右，大大降低了需要在网络上传输的数据量。此外，GoldenGate传输数据是直接通过TCP/IP上进行，无需额外封装；

• Data Guard需要传输Oracle全部的日志，没有压缩功能，在网络上传输的数据量比GoldenGate大很多。它是通过Oracle Net传输数据，其握手信息比较多，相对直接GoldenGate的通过tcp/ip传输效率差很多。

• 综合上述原因，相同状况下GoldenGate的延时要比
Data Guard小很多，容灾系统的RPO会更理想。

三、接管效率

使用GoldenGate复制时，备份数据库是始终处于活动状态，可以随时接管业务；

• Data Guard的备份数据库是处于恢复或只读状态，（Oracle 11g ADG 可以实现恢复的同时只读）如果出现灾难接管业务需要经历两个阶段，第一个阶段是用户查询数据库等待数据库完成日志恢复（Oracle 9i Data Guard只能做归档日志的恢复，只有在10g加入了实时应用的功能可以对未归档日志作恢复）；另外一个阶段是将数据库由备份状态改成主数据库状态。这两个阶段根据实际经验一般至少需要5分钟以上时间。

• 由此可见，使用GoldenGate的容灾系统RTO相对更短，有利于保障业务的连续性。

                      

 

技术笔记（小潘的技术记录博客）