Oracle Life DBA的一天

/*****************************************************************************************************************
 Navicat Premium Data Transfer

 Source File           : Oracle Life DBA的一天.sql
 Source Server Type    : Oracle
 Source Server Version : 190000 (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)
 Source Host           : 10.16.10.10:1521
 Source Schema         : MCS

 Target Server Type    : Oracle
 Target Server Version : 190000 (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)
 File Encoding         : 65001

 Date: 2024-12-30
**********************************************************************************************************************/

-- 1. 活动状态检查：
/****************************************************
  通过查询基本视图，确认数据库和实例处于正常运行状
态，可以对外提供数据服务。
*****************************************************/
-- 1.1 实例状态
-- 查询返回实例名称、状态，正常状态应为Open。
SELECT instance_name,status FROM gv$instance;
SELECT * FROM gv$instance;

-- 集群状态
$crsctl status resource –t

-- PDB状态
SELECT  name,open_mode FROM v$PDBS;
SELECT  * FROM v$PDBS;

-- Sharding状态
GDSCTL> config shard

-- 1.2会话信息
-- 辅助查询，实例当前会话数和启动最高连接会话数量。
SELECT
SESSIONS_CURRENT,SESSIONS_HIGHWATER FROM v$license;

SELECT * FROM v$license;

-- 查询数据库连接数以实例和用户分组。
SELECT inst_id,username,COUNT(*) FROM gv$session GROUP BYinst_id,username;

SELECT * FROM gv$session;

-- 1.3参数检查
-- 查询给定参数的设置值，示例参数缺省值为300，通常中等规模数据库推荐设置为1000。
SELECT value FROM v$parameter WHERE name='open_cursors';
SELECT * FROM v$parameter ; 

-- 1.4参数修改   
-- 修改给定的初始化参数，RAC环境需要注意SID参数。
/*
参考：
http://www.eygle.com/archives/2006/09/undo_retention_need_change.html
*/   
ALTER SYSTEM SET undo_retention=3600 COMMENT='default 900' SID='*' SCOPE=both;

-- 1.5隐含参数 
-- 为了解决特殊问题，有时需要设置以下划线开头的隐含参数。示例关闭了11.2版本中引入的Cardinality Feedback - 基数反馈特性。   
ALTER SYSTEM SET "_optimizer_use_feedback"=FALSE SCOPE=spfile;
-- 示例关闭了12c中的并行LGWR特性
ALTER SYSTEM SET “_use_single_log_writer”=TRUE

-- 1.6实例异常
/*
当连接数据库实例出现缓慢、挂起等现象，需要进行诊断和分析，甚至可能需要重新启动数据库实例。
*/
-- 1.6.1信息采集
/*
示范命令，通过采集系统的Hang信息、系统状态信息等，可以分析系统挂起的原因，间隔采样，可以用于对比变化，辅助分析。
*/
SQL> sqlplus -prelim / as sysdba

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug hanganalyze 3

SQL> oradebug dump systemstate 266

/*
<间隔一定时间，如20秒，执行下一次数据采样. >
*/

SQL> oradebug hanganalyze 3

SQL> oradebug dump systemstate 266

-- 1.6.2 跟踪
/*
如果在数据库关闭、启动时遇到阻塞、挂起等，可以通过示范命令进行跟踪，获取跟踪文件进行分析。
*/
SQL> alter session set events '10046 trace name context forever,level 12';

SQL> shutdown immedaite;          

SQL> startup mount;

SQL> alter session set events '10046 trace name context forever,level 12';

SQL> alter database open;

-- 1.6.3 安全停库
/*
如果数据库出现异常需要重新启动，可以通过示范命令执行检查点、归档命令，然后尝试以立即方式关闭数据库。
*/
SQL> alter system checkpoint;

SQL> alter system archive log current;

SQL> shutdown immediate;

-- 1.6.4 强制停库
/*
如果立即方式不能顺利关闭数据库，强制的关闭方式为abort。示范命令可以通过分步骤的方式执行数据库启动。
*/
SQL> shutdown abort;

SQL> startup nomount;

SQL> alter database mount;

SQL> alter database open;

-- 1.7连接异常
/*
当连接数据库出现异常，需要检测包括网络连通性，监听器状态等信息。            
*/
-- 1.7.1连通性
/*
在安装具有Oracle客户端的环境，可以通过tnsping工具测试配置的服务名称，观察网络是否连通以及响应时间。
*/
tnsping tns_name

-- 1.7.2监听器
/*
在数据库服务器上，可以通过 lsnrctl 工具检查监听状态和服务信息，具体的监听服务名称可以在最后定义修改。
*/
lsnrctl status LISTENER

lsnrctl status LISTENER_SCAN1

lsnrctl service

-- 1.7.3监听日志检查
/*在服务器上，可以通过adrci工具，显示各类告警文件，检查监听器日志，可以诊断监听问题。*/
adrci> show alert

-- 2.日志信息检查:
/*
oracle的日志文件有几种：警告日志，trace日志，audit日志，redo日志，归档日志
警告日志：也就是 alert log，在系统初始化参数文件设置的 show  parameter background_dump_dest 对应的就是它的位置。
trace日志：一般放seesion追踪的信息，对应系统初始化参数文件参数 show parameter user_dump_dest 对应的就是它的位置。
audit日志：审计的信息，对应系统初始化参数文件参数 audit_file_dest对应的就是它的位置。
redo 日志：存放数据库的更改信息，select member from v$logfile;member就代表它的位置
归档日志：redo日志的历史备份，select * from v$parameter where name like 'log_archive_dest%'; 它的value的值就是位置

日志状态检查检查数据库各类日志信息，确认数据库实例、集群等是否出现错误、告警，如存在问题，则需要进一步分析和应对。
*/

-- 2.1 告警日志
-- 根据示例找到告警日志，检查实例是否存在 ORA- 错误提示等。
$ORACLE_BASE/diag/rdbms/<db_name>/$ORACLE_SID/trace/alert_$ORACLE_SID.log      

SQL> show parameter background_dump_dest

-- 2.2 集群日志
-- 在相应路径找到RAC集群日志，检查是否存在错误提示信息等。
$GRID_HOME/log/<nodename>/alert<nodename>.log

$GRID_HOME/log/<nodename>/(crsd、cssd、evmd、ohasd)/

-- 2.3 ASM日志
-- 在相应路径找到ASM日志，检查是否存在错误提示信息等。
$GRID_HOME/diag/asm/+asm/<ASM_instance_name>/trace/alert_<instance_name>.log

-- 2.4 Trace文件
-- 获取会话产生或全局转储位置，在诊断时需检查最近日期文件内容。
SQL> SELECT value FROM v$diag_info WHERE name='Default Trace File';

SQL> show parameter user_dump_dest

-- 2.5 errorstack分析
/*
当遇到 ORA- 错误，而数据库的输出信息不足时，可以采用errorstack进行跟踪，采集更详细的转储信息。
示例显示了对ORA-600错误设置跟踪，并关闭。
*/
SQL> alter system set events='600 trace name errorstack forever, level 10';

SQL>alter system set events='600 trace name errorstack off';

-- 3.重做日志维护:
/*
Oracle REDO日志是数据库的核心组件，检查其状态，维护其成员，监控其归档，审核其性能，是DBA的重要工作。
*/
-- 3.1 REDO组
-- 查询日志组号、序号,是否归档完成和状态信息，如多组日志显示ACTIVE状态，则可能说明数据库存在IO方面的性能问题。
SELECT group#,sequence#,archived,status FROM v$log;
SELECT * FROM v$log;

-- 3.2 REDO成员
-- 查看日志组和成员信息。
SELECT group#,member FROM v$logfile;
SELECT * FROM v$logfile;

-- 查看DataGuard 及ActiveDataGuard中standbyRedolog的信息
SELECT group#, member FROM v$logfile WHERE type='STANDBY';

SELECT group#, dbid,bytes FROM v$standby_log;

-- 3.3 增加日志组或成员
--在日志切换频繁时，可能需要增加日志组或者加大日志大小。
SQL> ALTER DATABASE ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M ;

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log3c.rdo'TO GROUP 10;

-- 增加RAC中的日志组
SQL> alter database add logfilethread 2 group 10

-- 3.4 切换日志
-- 切换日志组，开始写入下一个日志组。
SQL> ALTER SYSTEM SWITCH LOGFILE;

-- 3.5 执行归档
-- 对当前日志组执行归档，切换到下一个日志组,在RAC会对所有实例执行归档，Thread参数指定归档实例。
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

-- 3.6 删除日志组或成员
-- 删除指定日志组或日志成员，注意只能对INACTIVE状态的日志执行删除操作。
SQL> ALTER DATABASE DROP LOGFILE GROUP 10;
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/redo03.log';

-- 3.7 归档检查
-- 检查数据库是否处于归档模式。
SQL> archive log list;

-- 3.8 归档状态变更
-- 示例步骤在MOUNT状态改变归档模式，注意启动归档模式之后务必制订备份归档的日常策略，防止磁盘空间被耗尽。
SQL> ALTER database archivelog | noarchivelog;

-- 3.9 调整归档路径
-- 如果数据库因归档耗尽空间，可以指定另外的归档路径，以尽快归档日志，恢复数据库运行。
SQL> alter system set log_archive_dest_2='location=&path' sid='&sid';

-- 4. 空间信息检查
/*
确保数据存储空间可用，定期检查表空间余量，进行表空间和文件维护。
*/
--4.1 空间使用查询
-- 查看数据库表空间的使用信息。
SQL> SELECT * FROM sys.sm$ts_used;
-- 查看数据库表空间的剩余空间。         
SQL> SELECT * FROM sys.sm$ts_free;
-- ASM磁盘组的空间
SQL> SELECT group_number,name,total_mb,free_mb FROM v$asm_diskgroup;

-- 4.2 文件信息
-- 查看数据库表空间的数据文件信息。
SELECT tablespace_name,file_name FROM dba_data_files;      
SELECT * FROM dba_data_files ;

-- 4.3 文件维护
-- 对数据库的表空间容量进行扩容。
ALTER database datafile '&path' resize 900M;

ALTER tablespace &tbs_name ADD datafile '&path' SIZE 900M;

-- 5. 锁/闩信息检查
/*
Lock/Latch是数据库控制并发的核心手段，检查相关信息可以监控数据库的事务和运行状况。
*/
-- 5.1 锁信息
-- 查看锁会话ID，类型，持有时间等,注意如果block >1,可能意味着阻塞了其他会话。
SQL> SELECT sid, type, lmode, ctime, block FROM v$lock WHERE type not in ('MR','AE');
SQL> SELECT sid, type, lmode, ctime, block FROM v$lock WHERE type not in ('MR','AE') AND block > 1;
SELECT * FROM v$lock WHERE type not in ('MR','AE');
SELECT * FROM v$lock;

-- 5.2 锁故障排查
/*
在数据库出现锁竞争和阻塞时，需要排查和处理锁定，必要时通过 Kill 阻塞进程消除锁定。
*/
-- 5.2.1 查询阻塞会话
-- 查询当前锁事物中阻塞会话与被阻塞会话的sid,sql_id和状态信息
SQL> SELECT sid,sql_id,status,blocking_session FROM v$session WHERE sid in (SELECT session_id FROM v$locked_object);

-- 5.2.2 阻塞SQL文本
-- 通过sql_id查询得到SQL文本，例如通过sql_id查询出阻塞的SQL语句。
SQL> SELECT sql_id,sql_text FROM v$sqltext WHERE sql_id='&sql_id' ORDER BY piece;
SQL> SELECT sql_id,sql_text FROM v$sqltext;

-- 5.2.3 锁阻塞对象信息
-- 通过sid查询阻塞对象的详细信息如对象名称，所属用户等
SQL> SELECT owner,object_name,object_type FROM dba_objects WHERE object_id in (SELECT object_id FROM v$locked_object);

-- 5.2.杀阻塞会话
-- 在Oracle实例内杀死阻塞的会话进程,其中sid,serial# 为中止会话对应信息，来自v$session。
SQL> alter system kill session 'sid,serial#';

-- 5.2.5 杀系统进程
/*
有时对于活动进程，在系统层面中止更为快速安全，示例找到系统进程号，然后kill中止。
注意：无论何时，需要认真分析，并且避免误杀重要后台进程。
*/
SQL> SELECT pro.spid,pro.program FROM v$session ses,v$process pro WHERE ses.sid=&sid AND ses.paddr=pro.addr;

# kill -9 spid

-- 5.3 闩检查
-- 检查数据库闩的使用情况，misses、SPIN_GETS统计高的，需要关注。
SELECT name,gets,misses,immediate_gets,spin_gets FROM v$latch ORDER BY 2;

-- 5.3.1 闩使用检查
-- 仅供学习：通过获得Latch的地址，找到该Latch守护的X$BH中相关的Buffer。
SQL> SELECT addr,gets FROM v$latch_children WHERE name='cache buffers chains';

SQL> SELECT hladdr,file#,dbablk FROM x$bh WHERE hladdr in (SELECT addr FROM v$latch_children WHERE addr='&addr');

-- 6. 等待和统计数据
/*
Wait和Statistics数据分别代表了数据库的等待和运行数据，观察这些数据以了解数据库的等待瓶颈和健康程度。
*/

-- 6.1等待事件查询
/*
通过等待事件和等待时间，了解数据库当前连接会话的等待情况。
注意，如果会话众多，需要限定查询输出行数。
*/
SELECT sid,event,wait_time_micro FROM v$session_wait ORDER BY 3;

-- 6.2 TOP10等待事件
-- 查看当前数据中TOP10等待事件信息，需要分析和关注非空闲的显著等待。
SQL> 
SELECT * FROM (
SELECT EVENT,TOTAL_WAITS,AVERAGE_WAIT,TIME_WAITED
  FROM v$system_event 
 WHERE
    wait_class <> 'Idle'
ORDER BY time_waited desc) 
WHERE rownum <=10
;

-- 6.3 会话统计数据
-- 查询数据库会话的统计信息数据，示例查询了Redo的大小，SID需要提供。
SQL> 
SELECT s.sid,s.statistic#,n.name,s.value 
  FROM v$sesstat s,v$statname n
 WHERE s.statistic# = n.statistic# 
     AND n.name='redo size' 
     AND sid='&sid'
;

SELECT s.sid,s.statistic#,n.name,s.value 
  FROM v$sesstat s,v$statname n
 WHERE s.statistic# = n.statistic# 
     AND n.name='redo size' 
     AND sid='1'
;

SELECT * FROM v$sesstat l  limite 100;
SELECT * FROM v$statname  l;

-- 6.4 系统级统计数据
-- 查询整个系统的统计数据，示例显示数据库实例启动以来的REDO日志生成量。
SQL> SELECT * FROM v$sysstat WHERE name='redo size';

-- 7. 对象检查 :
/*
表、索引、分区、约束等是数据库的核心存储对象，其核心信息和对象维护是DBA重要的日常工作。
*/
-- 7.1 表信息数据
-- 查看表的基本信息数据：属主，表名，记录行数等。
SQL> 
SELECT * FROM (
SELECT owner,table_name,num_rows
FROM dba_tables 
ORDER BY num_rows DESC nulls last )
WHERE rownum < 11
;

-- 7.2 表结构查询
-- 根据提供的表名和用户（需大写），查询表的表结构信息(建表语句)。
SQL> set long 12000 

SQL> SELECT dbms_metadata.get_ddl ('TABLE','&table_name','&user') FROM dual ;

-- 7.3 表统计信息
-- 查询给定用户、给定表（需大写），查询最后的统计信息分析收集时间。统计信息影响执行计划，当SQL执行异常时，需要重点分析统计信息。
SQL> 
SELECT owner,table_name,last_analyzed 
  FROM dba_tab_statistics
 WHERE owner='&owner' 
   AND table_name='&table_name';

-- 7.4 表统计信息收集
-- 收集统计信息是一项复杂任务，需要详细设计，示例对给出用户、表名的对象采集统计信息。
SQL> exec dbms_stats.gather_table_stats(ownname=>'&owner',tabname=>'&table_name');

-- 7.5 索引信息数据
-- 查询索引的基本信息,示例输出包括叶块数和聚簇因子等，如聚簇因子过高接近行数可能代表索引效率不高。
SQL> 
SELECT * FROM (
SELECT index_name,table_name,num_rows,leaf_blocks,clustering_factor
FROM dba_indexes 
ORDER BY 5 desc nulls last
)
WHERE rownum <11
;

-- 7.6 索引定义查询
-- 根据提供的表名和用户名，查询索引的建立语句。
SQL> set long 12000

SQL> SELECT dbms_metadata.get_ddl('INDEX','&table_name','&user') FROM dual;

-- 7.7 索引统计信息及收集方法
-- 根据给定的用户名，表名称（需大写），查询索引信息，尤其应关注最后分析时间。
SQL> 
SELECT owner,index_name,last_analyzed FROM dba_ind_statistics
WHERE owner='&owner' and table_name='&table_name';

-- 收集统计信息是一项复杂任务，示例对给出用户、索引名的对象采集统计信息。
SQL> exec dbms_stats.gather_index_stats(ownname=>'&owner',indname=>'&index_name');

-- 7.8 Dblink信息：
-- Bethune对连入数据库的IP做链接趋势和孤立Ip分析，帮助工程师发现链接异常的情况。
SELECT * FROM DBA_DB_LINKS;

-- 7.9 分区对象检查
SQL> 
SELECT table_name,partitioning_type,partition_count,status
FROM dba_part_tables
;

-- 查看分区表的基本信息：分区类型，数量,边界值等。
SQL>
SELECT table_name,partition_name,high_value
FROM dba_tab_partitions 
WHERE rownum<11;

-- 7.10分区定义查询
-- 根据给定的分区表名、用户（需大写），查询分区表的结构信息（建表语句）。
SQL> 
SELECT dbms_metadata.get_ddl
('TABLE','&part_table_name','user')
FROM dual;

-- 7.11分区统计信息相关
SQL>
SELECT owner,table_name,partition_name,last_analyzed
FROM dba_tab_statistics
WHERE owner='&owner' AND table_name='&table_name';

-- 查看分区表的统计信息收集时间，以及对分区表进行手工收集统计信息，注意分区表统计信息收集非常复杂，需要深入研究做出正确策略，示例仅提供最简单的采集命令。
SQL>exec dbms_stats.gather_table_stats(ownname=>'&owner',tabname=>'&table_name');

-- 7.12 约束信息
-- 查询指定数据表的约束信息，包括名称和类型。
SQL> 
SELECT constraint_name,constraint_type 
FROM DBA_CONSTRAINTS
WHERE table_name='&table_name';

-- 7.13 失效对象检查
-- 检查数据库中的失效对象信息，通常运行健康的数据库中不应有失效的对象。
SQL> 
SELECT owner,object_name,object_type,status
FROM dba_objects 
WHERE status <>'VALID'
ORDER BY owner,object_name;

-- 7.14 闪回查询
/*
闪回查询功能对于恢复DML及部分DDL误操作非常便利，DBA必备技能。
*/
-- 7.13.1 时间闪回
-- 闪回表数据,基于时间点的表数据闪回查询。
SQL> 
SELECT * FROM &table_name as of timestamp
to_timestamp('2015-02-0400:02:09','yyyy-mm-dd hh24:mi:ss');

-- 7.13.2 SCN闪回
-- 闪回表数据，基于scn的表数据查询，需要提供SCN，如果不明确SCN，可以通过时间点闪回查询。
SQL> SELECT * FROM &table_name as of scn &scn;

-- 7.13.3 闪回DROP
-- 闪回删除操作，对已经删除的表进行闪回回复并重命名。
SQL> flashback table &old_table to before drop rename to &new_table;

 select dbms_flashback.get_system_change_number from dual;
/*
参考：
http://www.eygle.com/archives/2009/11/scn_to_timestamp.html
http://www.eygle.com/archives/2005/03/eoaoracle10gaef.html

上周在培训期间，一个客户的数据库出了误UPDATE的问题，修改错了数据，请求我帮忙恢复。
闪回查询在这类误操作中非常有用，只要发现的及时，数据可以快速的闪回回来，我想这是开发人员也应该掌握的SQL查询。

记录一下工作日志：
SQL> desc lm_mtrp_syn_channelinfo
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CHANNELID                                          NUMBER
 SGWID                                              VARCHAR2(50)
 SERVICETYPE                                        VARCHAR2(50)
 SPNUMBER                                           VARCHAR2(50)
 CMD                                                VARCHAR2(50)
 CREATETIME                                         DATE
 MORE                                               VARCHAR2(500)

SQL> select count(*) from lm_mtrp_syn_channelinfo;

  COUNT(*)
----------
        21

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
               708677858

SQL> select count(*) from lm_mtrp_syn_channelinfo as of scn 708600000;            

  COUNT(*)
----------
        21

SQL> select count(*) from lm_mtrp_syn_channelinfo as of scn 708000000;

  COUNT(*)
----------
        21

SQL> select scn_to_timestamp(708000000) from dual;

SCN_TO_TIMESTAMP(708000000)
---------------------------------------------------------------------------
05-NOV-09 01.37.08.000000000 PM

SQL> create table lm_temp as select * from lm_mtrp_syn_channelinfo as of scn 708000000;

Table created.

-The End-

-- 示例2：
Oracle10g对于闪回查询进行了增强，支持更简单的SQL操作，允许对误删除、误更新等DML操作进行闪回。
看一下以下测试:
1.原表记录

$ sqlplus eygle/eygle

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Mar 30 08:52:04 2005

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

EYGLE  on 30-MAR-05 >select count(*) from t1;

  COUNT(*)
----------
      9318

2.误删除所有记录
并且提交更改。

EYGLE  on 30-MAR-05 >delete from t1;

9318 rows deleted.

EYGLE  on 30-MAR-05 >commit;

Commit complete.

EYGLE  on 30-MAR-05 >select count(*) from t1;

  COUNT(*)
----------
         0

3.获得当前SCN
如果能够确切知道删除之前SCN最好，如果不知道，可以进行闪回查询尝试.

EYGLE  on 30-MAR-05 >select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                10671006

EYGLE  on 30-MAR-05 >select count(*) from t1 as of scn 10671000;

  COUNT(*)
----------
         0

EYGLE  on 30-MAR-05 >select count(*) from t1 as of scn 10670000;

  COUNT(*)
----------
      9318

我们看到在SCN=10670000时，数据都在。
4.恢复数据.

EYGLE  on 30-MAR-05 >insert into t1 select * from t1 as of scn 10670000;

9318 rows created.

EYGLE  on 30-MAR-05 >commit;

Commit complete.

EYGLE  on 30-MAR-05 >select count(*) from t1;

  COUNT(*)
----------
      9318

EYGLE  on 30-MAR-05 >
*/

-- 8. AWR报告检查:
/*
通过AWR报告了解日常高峰时段数据库各项指标和运行状况，通过对比报告观察和基线的变化，通过趋势分析持续关注数据库日常运行状态。
*/

-- 8.1 本地AWR
-- 生成本地AWR报告信息，需要根据提示输入相应的信息。
SQL>@?/rdbms/admin/awrrpt

-- 8.2 指定实例AWR
-- 生成指定实例AWR报告
SQL>@?/rdbms/admin/awrrpti

-- 8.3 RAC AWR：
@$ORACLE_HOME/rdbms/admin/awrgrpt.sql
@$ORACLE_HOME/rdbms/admin/awrgrpti.sql

-- 8.4 AWR对比报告
-- 生成本地AWR时间段对比报告
SQL>@?/rdbms/admin/awrddrpt

-- 8.5 指定实例对比
-- 生成指定实例AWR时间段对比报告
SQL>@?/rdbms/admin/awrddrpi

-- 8.6 系统性能集成报告：
-- 指定不同维度，生成系统的全局性能报告
@$ORACLE_HOME/rdbms/admin/perfhubrpt.sql

-- 8.7 AWR信息提取
-- 使用 awrextr 脚本将AWR性能数据导出，可以用于留错或者异地分析。
SQL>@?/rdbms/admin/awrextr

-- 8.8 AWR信息加载
-- 通过awrload，可以将导出的AWR性能数据导入到其他数据库中，便于集中和分析。
SQL>@?/rdbms/admin/awrload

-- 9. SQL报告检查
/*
对TOP SQL进行持续关注和分析，通过SQL报告分析SQL的效率、性能，并做出报告和优化建议等。
*/
-- 9.1 本地SQLReport
-- 生成本地SQLReport报告
SQL> @?/rdbms/admin/awrsqrpt

-- 9.2 指定实例SQLReport
-- 生成指定实例SQLRerport报告
SQL>@?/rdbms/admin/awrsqrpi

-- 9.3 当前会话SQL Monitor Report
-- 生成当前会话的SQL Monitor Report
SELECT
dbms_sqltune.report_sql_monitor(session_id=> '&sid',report_level=> 'ALL', TYPE=> '&type') as report
FROM dual;

-- 9.4 指定SQLID SQL Monitor Report
-- 生成指定SQL_ID的SQL Monitor Report
SELECT
dbms_sqltune.report_sql_monitor(sql_id=> '&sql_id',report_level=> 'ALL', TYPE=> '&type') as report FROM dual;
/*
示例：
参考：http://www.eygle.com/archives/2012/09/sqloracle_sql_monitor_report.html
这个报告会通过OTN获得展现的框架和JS脚本，如果不能连接到公网，你可以在本地构建相应的文件，我在自己的站点保存了这些脚本：
mkdir -p eygle.com/sqlmon
cd eygle.com/sqlmon
wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/flashver.js
wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/loadswf.js
wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/document.js
wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/sqlmonitor/11/sqlmonitor.swf
这样我在生成SQL报告时，就可以调用自己网站的脚本文件。

通过v$sql_monitor视图可以找到已经被记录的SQL_ID:
SQL> select sql_id from v$sql_monitor;

SQL_ID
-------------
6rqxj647ut9pn
f4kcr0dn9rv6z
f6cz4n8y72xdc
以下是通过脚本调用，生成了一个报告：
[eygle@enmoteam2 ~]$ sqlplus "/ as sysdba" @eygle.sql

SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 6 15:01:55 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
在脚本 eygle.sql 中定义了SQL_ID ,通过这个SQL_ID 生成了ACTIVE REPORT：
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL report_sql_monitor.htm
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => '6rqxj647ut9pn',
  type         => 'ACTIVE',
  report_level => 'ALL',
  base_path    => 'http://www.eygle.com/sqlmon') AS report
FROM dual;
SPOOL OFF
这样生成的报告较以前的SQL Report更为直观。
*/

-- 9.5 Explain SQL执行计划
-- 示例通过explain plan for方法获取SQL执行计划
explain plan FOR SELECT count(*) FROM user_objects;

SELECT * FROM table(dbms_xplan.display);

-- 9.6 Autotrace SQL执行计划
-- 通过SQL*Plus的 autotrace 功能获取SQL执行计划。
SQL> set autotrace traceonly explain;

SQL> SELECT count(*) FROM user_objects;

SQL> set autotrace off;

-- 9.7 DBMS_XPLAN SQL执行计划
-- 通过DBMS_XPLAN包获取SQL执行计划，sql_id 需要提供。
SQL> 
SELECT * FROM TABLE (dbms_xplan.display_cursor('&sql_id',null,'advanced');

-- 9.8 10053事件跟踪
-- 通过10053事件来查看执行计划和详细的SQL解析过程，trace文件提供了Oracle如何选择执行计划的原因。
SQL> alter session set tracefile_identifier='10053';

SQL> alter session set events '10053 trace name context forever ,level 1';

SQL> <execute sql statements>

SQL> alter session set events '10053 trace name context off';

-- 参考：http://www.eygle.com/archives/2011/02/dba_event_10046_10053.html

-- 9.9 绑定变量
SELECT
dbms_sqltune.extract_bind(bind_data, 1).value_string
FROM wrh$_sqlstat WHERE sql_id ='&sql_id';

-- 查询SQL语句的绑定变量以及历史绑定变量值信息,需要给定SQL_ID信息。
SELECT snap_id,name,position,value_string
FROM dba_hist_sqlbind WHERE sql_id= '&sql_id';

-- 参考：http://www.eygle.com/archives/2010/11/dbms_sqltune_bindata.html

-- 10. 定时任务检查:
/* 
检查数据库定时任务执行情况，确保后台任务正确执行，尤其应关注统计信息收集等核心任务。
*/
-- 10.1 用户定时任务
-- 查询用户的定时任务（job）信息，确保任务在期望的时间成功执行，这是DBA的重要工作之一。
SQL> 
SELECT job,log_user,last_date,next_date,interval,broken,failures
  FROM dba_jobs;

-- 10.2 系统定时任务
-- 查询系统定时调度信息，查询显示了任务名称、初始启动日期以及重复间隔。
SQL> 
SELECT job_name,start_date,repeat_interval
FROM dba_scheduler_jobs ;

-- 10.3 系统定时任务-11g
-- 11g之后增加的字典表，记录每个在7天和30天维护任务的统计信息，查询显示名称和平均执行时间。
SQL> 
SELECT client_name,mean_job_duration
FROM dba_autotask_client ;

-- 10.4 启停统计信息任务-10g
-- 关闭和开启oralce 10g 统计信息自动采集任务
SQL> exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');

SQL> exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');
-- 查看自动收集任务
SQL>
SELECT owner,table_name,object_type,stale_stats,last_analyzed FROM dba_tab_statistics ;
-- 查看每天自动信息采集时间
SELECT
    w.window_name,
    w.repeat_interval,
    w.duration,
    w.enabled 
FROM
    dba_autotask_window_clients c,
    dba_scheduler_windows w 
WHERE
    c.window_name = w.window_name 
    AND c.optimizer_stats = 'ENABLED'
;

-- 10.5 启停统计信息任务-11g
-- 关闭和开启Oralce 11g 统计信息自动采集任务
SQL> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=> 'auto optimizerstats collection', operation => NULL, window_name => NULL);

SQL> exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection', operation=> NULL, window_name => NULL);
-- 查看自动收集任务
SQL>
SELECT owner,table_name,object_type,stale_stats,last_analyzed FROM dba_tab_statistics ;
-- 查看每天自动信息采集时间
SELECT
    w.window_name,
    w.repeat_interval,
    w.duration,
    w.enabled 
FROM
    dba_autotask_window_clients c,
    dba_scheduler_windows w 
WHERE
    c.window_name = w.window_name 
    AND c.optimizer_stats = 'ENABLED'
;

-- 关闭单个调度时间窗口
/*
BEGIN
DBMS_AUTO_TASK_ADMIN.disable (
client_name   => 'auto optimizer stats collection',
operation     => NULL,
window_name   => 'MONDAY_WINDOW');
END;
> OK
> 查询时间: 0.122s
*/
-- 开启单个调度时间窗口及所有时间调度窗口，只需要使用enable过程
/*
BEGIN
DBMS_AUTO_TASK_ADMIN.enable (
client_name   => 'auto optimizer stats collection',
operation     => NULL,
window_name   => NULL);
END;
> OK
> 查询时间: 0.014s
*/

-- 11. 备份:
/*
数据备份重于一切，日常应检查备份执行情况，并检查备份的有效性，确保备份能够保障数据安全，备份安全加密也应兼顾。
*/

-- 11.1 全库exp/imp
-- 将数据库全库导出导入（示例以及以下示例显示的是非Windows环境），通过提供的用户，执行全库导出。需要注意存储位置和空间。
$ exp system/manager file=/full.dmp log=/full.log full=y

$ imp system/manager file=/full.dmp log=/full.log full=y

-- 11.2 用户模式exp/imp
-- 将数据库按指定用户导出
$exp enmo/enmo file=/enmo.dmp log=/enmo.log owner=enmo

$imp enmo/enmo file=/enmo.dmp log=/enmo.log fromuser=enmo touser=enmo

-- 11.3 表模式exp/imp
-- 将数据库按指定表导出
$ exp enmo/enmo file=/tables.dmp log=/tables.log tables=table_name

$ imp enmo/enmo file=/tables.dmp log=/tables.log tables=table_name

-- 11.4 全库模式expdp/impdp
-- 将数据库全库导出导入, 注意directory是数据库中创建的对象，指定服务器上的存储位置。
$ expdp system/manager directory=svr_dir dumpfile=full.dmp full=y;

$ impdb system/manager directory=svr_dir dumpfile=full.dmp full=y;

-- 11.5 用户模式-expdp/impdp
-- 将数据库进行按用户导出和导入示例，impdp示例中，分别重新映射了导入的Schema和表空间。
$ expdp system/manager directory=svr_dirschemas=scott dumpfile=expdp.dmp ;

$ impdp system/manager directory=svr_dir schemas=scott dumpfile=expdp.dmpremap_schema=scott:enmo remap_tablespace=users:testtbs;

-- 11.6 表模式-expdp/impdp
-- 将数据库进行按表导出和导入
$ expdp scott/tiger directory=svr_dir tables=emp,dept dumpfile=tables.dmp;

$ impdp scott/tiger directory=svr_dir dumpfile=tables.dmp tables=emp,dept;

-- 11.7 物理备份检查
-- 检查备份集信息，确保备份有效和及时是DBA的重要工作之一。RMAN的备份信息记录在控制文件中。
SQL> 
SELECT backup_type,start_time,completion_time,block_size
FROM v$backup_set;

-- 11.8 自动控制文件备份

-- 控制文件对数据库十分重要，建议启动控制文件的自动备份，示范显示的是通过RMAN的设置。

RMAN> show all;

RMAN> CONFIGURE CONTROLFILEAUTOBACKUP ON;

-- 11.9 手动控制文件备份
-- 通过RMAN或者SQL命令手动备份控制文件，备份的是控制文件的二进制拷贝。
RMAN> backup current controlfile;

SQL> alter database backup controlfile to '/back/control.bak';

-- 11.10 转储控制文件

-- 通过以上命令转储控制文件二进制信息到文本，研究这些信息，可以极大加深对于数据库的了解。
SQL> alter session set events 'immediate trace name controlf level 8';

-- 通过SQL命令转储控制文件到文本，可以用于重建控制文件。
SQL> alter database backupcontrolfile to trace;

-- 11.11 RMAN备份数据库
-- 对于DBA备份是第一重要的工作，在归档模式下，执行全库备份可以简化为示例的一个命令（需要根据容量进行分片）。
$ rman target/
RMAN> backup format'/home/oracle_back/%U' database plus archivelog;
/*
[oracle@MSTCDB02 ~]$ rman target/

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Dec 18 13:15:37 2024
Version 19.20.0.0.0

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

connected to target database: MSTCHIS (DBID=2969354461)

RMAN>  backup format'/home/oracle_back/%U' database plus archivelog;

Starting backup at 18-DEC-24
Oracle error from target database: 
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1266 instance=MSTCHIS2 device type=DISK
specification does not match any archived log in the repository
backup cancelled because there are no files to backup
Finished backup at 18-DEC-24

Starting backup at 18-DEC-24
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 12/18/2024 13:16:15
RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

RMAN> 
*/
/*
参考示例：
参考：http://www.eygle.com/archives/2004/11/use_rman_plusarchivelog_option.html
Oracle9i开始Rman提供plus archivelog选项，极大的简化了Rman备份的操作。

当你定义plus archivelog语句时，RMAN执行如下步骤的操作：
1。运行一个 ALTER SYSTEM ARCHIVELOG CURRENT 命令
2。运行 BACKUP ARCHIVELOG ALL 命令。注意如果备份优化被启用，RMAN只会备份未备份过的日志
3。备份 BACKUP 命令中定义的文件
4。运行 ALTER SYSTEM ARCHIVELOG CURRENT 命令
5。备份所有的剩下的归档日志

测试备份脚本：

backup database 
format '/opt/oracle/orabak/full_%d_%T_%s'
plus archivelog 
format '/opt/oracle/orabak/arch_%d_%T_%s'
delete all input;                      
 

测试日志:

 
[oracle@jumper oracle]$ rman target /

Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: CONNER (DBID=3152029224)

RMAN> run {
2> backup database 
3> format '/opt/oracle/orabak/full%d_%T_%s'
4> plus archivelog 
5> format '/opt/oracle/orabak/arch_%d_%T_%s'
6> delete all input; }

Starting backup at 04-NOV-04
current log archived
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=12 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=163 recid=6 stamp=541351580
channel ORA_DISK_1: starting piece 1 at 04-NOV-04
channel ORA_DISK_1: finished piece 1 at 04-NOV-04
piece handle=/opt/oracle/orabak/arch_CONNER_20041104_7 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/opt/oracle/oradata/conner/archive/1_163.dbf recid=6 stamp=541351580
Finished backup at 04-NOV-04

Starting backup at 04-NOV-04
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00004 name=/opt/oracle/oradata/conner/eygle01.dbf
input datafile fno=00001 name=/opt/oracle/oradata/conner/system01.dbf
input datafile fno=00002 name=/opt/oracle/oradata/conner/undotbs01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/conner/users01.dbf
channel ORA_DISK_1: starting piece 1 at 04-NOV-04
aaaachannel ORA_DISK_1: finished piece 1 at 04-NOV-04
piece handle=/opt/oracle/orabak/full_CONNER_20041104_8 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:04
Finished backup at 04-NOV-04

Starting backup at 04-NOV-04
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=164 recid=7 stamp=541351832
channel ORA_DISK_1: starting piece 1 at 04-NOV-04
channel ORA_DISK_1: finished piece 1 at 04-NOV-04
piece handle=/opt/oracle/orabak/arch_CONNER_20041104_9 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/opt/oracle/oradata/conner/archive/1_164.dbf recid=7 stamp=541351832
Finished backup at 04-NOV-04

RMAN> 
       
通过plus archivelog,简化了备份操作，同时确保需要的日志都被备份。

 
[oracle@jumper orabak]$ ls -l
total 751628
-rw-r-----    1 oracle   dba        156160  Nov  4 15:26 arch_CONNER_20041104_7
-rw-r-----    1 oracle   dba         60928  Nov  4 15:30 arch_CONNER_20041104_9
-rw-r-----    1 oracle   dba      768679936 Nov  4 15:30 full_CONNER_20041104_8      
 
*/

-- 12. 基本信息检查
/*
基本信息包括版本、组件、补丁集等信息，定期检查数据库信息并登记在案是数据库生命周期管理的重要内容之一。
*/

-- 12.1 版本检查
-- 查看数据库的版本信息
SQL> SELECT * FROM v$version;

-- 12.2 组件检查
-- 查看数据库的组件信息
SQL> SELECT * FROM v$option;

-- 12.3 容量检查
-- 如果使用了ASM管理，可以通过示例查看ASM磁盘及磁盘组容量等信息。
# asmcmd
ASMCMD> lsdg

SELECT group_number,disk_number,
mount_status,total_mb,free_mb
FROM v$asm_disk;

SELECT group_number,name,state,total_mb,free_mb
FROM v$asm_diskgroup;

-- 12.4 PSU检查

-- 查询数据库的版本升级历史信息。
SQL> SELECT * FROM dba_registry_history;

-- 查询数据库补丁历史信息，是系统级的命令工具。
# $ORACLE_HOME/OPatch/opatchlsinventory