oracle之 获取建表ddl语句

- 阅读剩余部分 -

adg修复
前言
一、介绍
二、实战
三、11G常规修复
a.在主库上创建一个备库的控制文件
b.以备库的当前SCN号为起点，在主库上做一个增量备份
c.将增量备份和控制文件拷贝到备库上
d.使用新的控制文件将备库启动到mount状态
e.增量备份注册到RMAN的catalog，取消日志应用，恢复增量备份
f.开启备库的恢复进程
四、12C新特性修复
a.记录备库当前SCN号
b.使用recover standby using service恢复
c.备库启动到nomount状态，恢复控制文件
d.备库恢复，修复GAP
e.开启备库日志应用，检查同步
五、18C新特性恢复
a、执行RECOVER STANDBY DATABASE FROM SERVICE刷新备库
b.备库修改standby log路径
c.主库切日志，备库开启日志应用

- 阅读剩余部分 -

1、实施反馈归档已满，请求处理。
2、查看空间，确实满了，执行清理脚本
3、没过多久，又快满了，查询归档日志切换频率
4、由于归档满，数据库停掉，快照没有自动生成，于是手动执行awr报告

exec dbms_workload_repository.create_snapshot();

5、检查每天的日志生成，非常均匀，分析8:00-9:00，一个小时内的AWR报告，发现如下：
每秒的redo size约为4MB，每小时就是15GB

检查日志来源于哪个对象，检查Segments by DB Blocks Changes

6、查看操作系统定时任务计划
发现有个任务

• 17 * /home/oracle/XXXX.sh

ps -ef | grep XXXX.sh |wc -l

发现改任务每分钟执行1次，目前还有17次未执行。

set linesize 999
set pagesize 999    
column h0 format 999
    column h1 format 999
    column h2 format 999
    column h3 format 999
    column h4 format 999
    column h5 format 999
    column h6 format 999
    column h7 format 999
    column h8 format 999
    column h9 format 999
    column h10 format 999
    column h11 format 999
    column h12 format 999
    column h13 format 999
    column h14 format 999
    column h15 format 999
    column h16 format 999
    column h17 format 999
    column h18 format 999
    column h19 format 999
    column h20 format 999
    column h21 format 999
    column h22 format 999
    column h23 format 999
    column avg format 999.99
    column day format a6
    
    SELECT   TRUNC (first_time) "Date", TO_CHAR (first_time, 'Dy') "Day", COUNT (1) "Total",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) h0,
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",
             SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23", ROUND (COUNT (1) / 24, 2) "Avg"
        FROM gv$log_history
       WHERE first_time >= trunc(SYSDATE) - 50
       and thread# = inst_id
    GROUP BY TRUNC (first_time), TO_CHAR (first_time, 'Dy')
    ORDER BY 1 DESC;

ORACLE物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。通常情况下，物化视图被称为主表（在复制期间）或明细表（在数据仓库中）。这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。

     当数据库中表的数据量比较大，而又需要对这些大表进行关联查询，不管语句如何调整，查询的速度总是不尽人意，使用物化视图，速度可以提高n倍。

- 阅读剩余部分 -