分类 IT运维 下的文章

我们使用method_opt => 'for all columns size auto'方式对表收集统计信息。

SQL> BEGIN
      DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                    tabname          => 'T_STATS',
                                    estimate_percent => 100,
                                    method_opt       => 'for all columns size auto',
                                    no_invalidate    => FALSE,
                                    degree           => 1,
                                    cascade          => TRUE);
    END

我们查看直方图信息。

SQL> select a.column_name,
  2         b.num_rows,
  3         a.num_nulls,
  4         a.num_distinct Cardinality,
  5         round(a.num_distinct / b.num_rows * 100, 2) selectivity,
  6         a.histogram,
  7         a.num_buckets
  8    from dba_tab_col_statistics a, dba_tables b
  9   where a.owner = b.owner
 10     and a.table_name = b.table_name
 11     and a.owner = 'SCOTT'
 12     and a.table_name = 'T_STATS';

COLUMN_NAME       NUM_ROWS  NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM   NUM_BUCKETS
--------------- ---------- ---------- ----------- ----------- --------------- -------
EDITION_NAME         72674      72674           0           0 NONE                  0
NAMESPACE            72674          1          21         .03 NONE                  1
SECONDARY            72674          0           2           0 NONE                  1
GENERATED            72674          0           2           0 NONE                  1
TEMPORARY            72674          0           2           0 NONE                  1
STATUS               72674          0           2           0 NONE                  1
TIMESTAMP            72674          1        1592        2.19 NONE                  1
LAST_DDL_TIME        72674          1        1521        2.09 NONE                  1
CREATED              72674          0        1472        2.03 NONE                  1
OBJECT_TYPE          72674          0          45         .06 NONE                  1
DATA_OBJECT_ID       72674      64833        7796       10.73 NONE                  1
OBJECT_ID            72674          1       72673         100 NONE                  1
SUBOBJECT_NAME       72674      72145         140         .19 NONE                  1
OBJECT_NAME          72674          0       44333          61 NONE                  1
OWNER                72674          0          31         .04 FREQUENCY            31

15 rows selected.

现有如下SQL。

select * from emp e,dept d where e.deptno=d.deptno;

刷新监控信息

SQL> begin
  2    dbms_stats.flush_database_monitoring_info;
  3  end;
  4  /

我们可以先用explain plan for命令，在plan_table中生成SQL的执行计划。

SQL> explain plan for select * from emp e,dept d where e.deptno=d.deptno;

Explained.
然后我们使用下面脚本检查SQL语句中所有的表的统计信息是否过期。

SQL> select owner, table_name, object_type, stale_stats, last_analyzed
      from dba_tab_statistics
     where (owner, table_name) in
           (select object_owner, object_name
              from plan_table
             where object_type like '%TABLE%'
            union
            select table_owner, table_name
              from dba_indexes
            where (owner, index_name) in
                  (select object_owner, object_name
                     from plan_table
                    where object_type like '%INDEX%'));

OWNER      TABLE_NAME OBJECT_TYP STALE_STATS     LAST_ANALYZED
---------- ---------- ---------- --------------- ------------------
SCOTT      DEPT       TABLE      NO              05-DEC-16
SCOTT      EMP        TABLE      YES             22-OCT-16

最后我们可以使用下面脚本检查SQL语句中表统计信息的过期原因。

select *
  from all_tab_modifications
 where (table_owner, table_name) in
       (select object_owner, object_name
          from plan_table
         where object_type like '%TABLE%'
        union
        select table_owner, table_name
          from dba_indexes
         where (owner, index_name) in
               (select object_owner, object_name
                  from plan_table
                 where object_type like '%INDEX%'));

恢复过程说明：
1）解压数据备份

安装qpress软件
    rpm -ivh qpress-11.1.el7.x86_64.rpm
解压备份
    Cd /backup/Data/backup/xtrabackup/202042
    tar -xvf 192.168.138.2_41667_20201021_full.tar
    tar -xvf 192.168.138.2_41667_20201022_incre.tar
qpress解压数据文件
    cd /backup/Data/backup/xtrabackup/202042/41667/full
        (for bf in `find . -iname "*\.qp"` ; do echo $bf && qpress -d $bf $(dirname $bf) && rm -rf $bf ; done)
    cd /backup/Data/backup/xtrabackup/202042/41667/incre
        (for bf in `find . -iname "*\.qp"` ; do echo $bf && qpress -d $bf $(dirname $bf) && rm -rf $bf ; done)

2）合并备份

perpare全备
    innobackupex --apply-log --redo-only /backup/Data/backup/xtrabackup/202042/41667/full/
合并incre到全备
    innobackupex --apply-log --incremental-dir=/backup/Data/backup/xtrabackup/202042/41667/incre //backup/Data/backup/xtrabackup/202042/41667/full
perpare整个备份
    innobackupex --apply-log /backup/Data/backup/xtrabackup/202042/41667/full

3）恢复数据

停止数据库
    systemctl stop mysqld
清除数据文件目录数据
    cd /Data/mysql/
    rm -rf *
应用备份
    mv /backup/Data/backup/xtrabackup/202042/41667/full/* /Data/mysql
更改目录权限
    chown -R mysql.mysql /Data/mysql
重启数据库
    systemctl start mysqld

4）截取binlog日志

获得position起点
    cat /backup/Data/backup/xtrabackup/202042/41667/incre/xtrabackup_binlog_info 
    mysql-bin.002244    194    230f7b03-e281-11e9-a531-0894ef952262:1-7
登录源数据库服务器获取bin-log日志，拷贝日志至恢复测试服务器/home/pmoopr/目录
获得position终点
    mysqlbinlog mysql-bin.002244
    mysqlbinlog mysql-bin.002245
    mysqlbinlog mysql-bin.002246
    mysqlbinlog mysql-bin.002247
截取日志
    mysqlbinlog --skip-gtids --start-position=194 /home/pmoopr/mysql-bin.002244 >/backup/Data/backup/xtrabackup/202042/2244.sql
    mysqlbinlog --skip-gtids --start-position=194 /home/pmoopr/mysql-bin.002245 >/backup/Data/backup/xtrabackup/202042/2245.sql
    mysqlbinlog --skip-gtids --start-position=194 /home/pmoopr/mysql-bin.002246 >/backup/Data/backup/xtrabackup/202042/2246.sql
    mysqlbinlog --skip-gtids --start-position=194 /home/pmoopr/mysql-bin.002247 >/backup/Data/backup/xtrabackup/202042/2247.sql

5）应用日志

mysql -uroot -p
停止记录binlog日志
    set sql_log_bin=0;
应用日志
    source /backup/Data/backup/xtrabackup/202042/2244.sql
    source /backup/Data/backup/xtrabackup/202042/2245.sql
    source /backup/Data/backup/xtrabackup/202042/2246.sql
    source /backup/Data/backup/xtrabackup/202042/2247.sql
开启记录binlog日志
    set sql_log_bin=1;

一、使用场景

在expdp备份不能满足要求的情况下，需要通过rman恢复单表数据，针对表空间恢复是一种比较合理的方式。

二、操作步骤

查询表空间信息

SQL> select distinct b.name from v$tablespace b;

复制表空间

SQL>
run {
allocate channel ch00 type sbt_tape;
allocate channel ch01 type sbt_tape;
send 'NB_ORA_SERV=hz-ps-bak01,NB_ORA_CLIENT=hwdb01';
set newname for datafile '+DATA/bydmes/datafile/system.260.1109607975' to '/u01/app/oradata/system.260.1109607975';
set newname for datafile '+DATA/bydmes/datafile/sysaux.261.1109607985' to '/u01/app/oradata/sysaux.261.1109607985';
restore tablespace SYSAUX,SYSTEM,UNDOTBS1,UNDOTBS2,USERS,YBWMS_KC;
switch datafile all;
release channel ch00;
release channel ch01;
}

注册归档日志

catalog start with '/opt/archbak20230114';

恢复表空间到指定时间点

run{
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
set until time '2023-01-14 09:30:00';
allocate channel ch00 type sbt_tape;
allocate channel ch01 type sbt_tape;
allocate channel ch02 type sbt_tape;
send 'NB_ORA_SERV=hz-ps-bak01,NB_ORA_CLIENT=hwdb01';
recover tablespace SYSAUX,SYSTEM,UNDOTBS1,UNDOTBS2,USERS,YBWMS_KC;;
switch datafile all;
release channel ch00;
release channel ch01;
release channel ch02;
}

打开数据库

SQL>alter database open resetlogs

备注：
问题处理过程中遇到了一下问题：

**1、使用restore指定数据文件方式恢复，导致undo表空间的数据文件恢复少了，把缺少的数据文件补充上即可。
2、spfile中sga和pga大小指定太小，导致数据库应用太慢，可以ctrl+c停止后，修改完参数再执行，同时操作系统中的sysctl.conf配置过低，也需要修改。**

  1. 1
  2. ...
  3. 4
  4. 5
  5. 6
  6. 7
  7. 8
  8. 9
  9. 10
  10. ...
  11. 29