ITPub博客

首页 > 数据库 > Oracle > DBA常用命令之东北大乱炖

DBA常用命令之东北大乱炖

原创 Oracle 作者:chenoracle 时间:2021-09-05 10:22:50 1 删除 编辑

###一:执行计划篇###
###二:统计信息篇###
###三:AWR报告篇###
###四:格式篇###
###五:hint篇###
###六:会话篇###
###七:日志挖掘LOGMNR篇###
###八:备份与恢复###
###九:坏块###
###十:表空间###
###十一:日志文件###
###十二:RAC###
###十三:12c###
###十四:需要关闭的特性###
###十五:归档日志###
###十六:dbms_metadata.get_ddl###
###十七:Oracle游标溢出###
###十八:BBED###
###十九:大表删除###
###二十:OEM###
###二十一:创建测试数据###
###二十二:EXP-00056###
###二十三:ORA-01102###
###二十四:登录触发器###
###二十五:代码里SQL增加随机数###
###二十六:PLSQL乱码###
###二十七:SCN###
###二十八:数据初始化###
###二十九:DB2###
###三十:sqlserver###
###三十一:mysql###
###三十二:AIX###
###三十三:Linux###

###一:执行计划篇###

如何获取执行计划:

参考:How to Obtain a Formatted Explain Plan - Recommended Methods (Doc ID 235530.1)

版本10.2及更高版本,最后执行的SQL:

在10.2及更高版本中,如果已经执行了SQL,则可以从库缓存中提取执行计划(除了早期版本中的标准解释计划选项之外)。

要获取上次执行的SQL问题的计划,请执行以下操作:

set linesize 150

set pagesize 2000

select * from table(dbms_xplan.display_cursor(null,null, 'ALL'));

###SQL_ID 和 child number是已知的###

一个SQL_ID可以有多个具有不同特征的child number。您可以通过从V$SQL中选择来标识child number,如下所示:

SELECT sql_id, hash_value, child_number, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&Some_Identifiable_String%'
/

set linesize 150
set pagesize 2000
select * from TABLE(dbms_xplan.display_cursor('&SQL_ID'));
或
select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD));

###gather_plan_statistics hint###

带有一些附加选项的gather_plan_statistics hint也可能提供运行时统计信息:例如:

select /*+ gather_plan_statistics */ col1, col2 etc.....
set linesize 150
set pagesize 2000
select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD,'ALL IOSTATS LAST'))

###通过SQL_ID获取###

For SQL ID :
select * from table(dbms_xplan.display_cursor('&sql_id'));
select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'));
For SQL ID, Child Cursor :
select * from table(dbms_xplan.display_cursor('&sql_id', &child_number, 'ALL'));
For SQL Text :
select t.*
from v$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'ALL')) t
where s.sql_text like '%&querystring%';

###从AWR中获取执行计划###

awrgrpt.sql

AWR SQL report是从特定时间段获取执行计划的一种简单方法。

使用以下命令启动报告:

$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

输入sql_id

For SQL ID :
select * from table(dbms_xplan.display_awr('&sql_id')) ;
select * from table(dbms_xplan.display_awr('&sql_id', NULL, NULL, 'ALL')) ;
For SQL ID, Plan Hash Value in the current database :
select * from table(dbms_xplan.display_awr('&sql_id', '&plan_hash_value', NULL, 'ALL')) ;
For SQL ID, Plan Hash Value in a different database ID :
select * from table(dbms_xplan.display_awr('&sql_id', '&plan_hash_value', &dbid, 'ALL')) ;
For SQL ID that was executed during a certain period :
select t.*
from (select distinct sql_id, plan_hash_value, dbid
from dba_hist_sqlstat
where sql_id = '&sql_id'
and snap_id between &begin_snap and &end_snap) s,
table(dbms_xplan.display_awr(s.sql_id, s.plan_hash_value, s.dbid, 'ALL')) t;
For SQL Text :
select t.*
from (select distinct r.sql_id sql_id, r.plan_hash_value plan_hash_value, r.dbid dbid
from dba_hist_sqltext q, dba_hist_sqlstat r
where q.sql_id = r.sql_id
and q.sql_text like '%&querystring%') s,
table(dbms_xplan.display_awr(s.sql_id, s.plan_hash_value, s.dbid, 'ALL')) t;
###From SQL Tuning Set (STS)
Note : STS owner is the current user by default.
For SQL ID in a STS :
select * from table (dbms_xplan.display_sqlset( '&sts_name','&sql_id'));
For All Statements in a STS :
select t.*
from dba_sqlset_statements s, table(dbms_xplan.display_sqlset(s.sqlset_name, s.sql_id, s.plan_hash_value, 'ALL', s.sqlset_owner)) t
where s.sqlset_name = '&sts_name';
For SQL ID, Plan Hash Value in a STS :
select * from table (dbms_xplan.display_sqlset( '&sts_name','&sql_id', '&plan_hash_value', 'ALL'));
For SQL ID, Plan Hash Value, STS Owner :
select * from table (dbms_xplan.display_sqlset( '&sts_name','&sql_id', '&plan_hash_value', 'ALL', '&sts_owner'));
For SQL Text in a STS :
select t.*
from dba_sqlset_statements s, table(dbms_xplan.display_sqlset(s.sqlset_name, s.sql_id, s.plan_hash_value, 'ALL', s.sqlset_owner)) t
where s.sqlset_name = '&sts_name'
and s.sql_text like '%&querystring%';
###From SQL Plan Baseline
For SQL Handle :
select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle'));
For SQL Handle, Plan Name :
select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle', '&plan_name', 'ALL'));
For SQL Text :
select t.*
from (select distinct sql_handle, plan_name
from dba_sql_plan_baselines
where sql_text like '%&querystring%') s,
table(dbms_xplan.display_sql_plan_baseline(s.sql_handle, s.plan_name, 'ALL')) t;
Version 9.2 and higher
Dbms_xplan
With Oracle 9.2, Oracle supplies a utility called dbms_xplan. It is created by dbmsutil.sql which is called by catproc.sql. As such it should already be installed on most 9.2 databases.
To generate a formatted explain plan of the query that has just been 'explained':
SQL> set lines 130
SQL> set head off
SQL> spool
SQL> alter session set cursor_sharing=EXACT;
SQL> select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
SQL> spool off
Versions 8.1.7 and 9.0.1
From version 8.1.5, Oracle has supplied 2 scripts to extract formatted explain plans from plan_tables. 
One is for serial plans and the other is for parallel plans. 
Scripts can be found under $ORACLE_HOME/rdbms/admin.
Examples of their usage are below.
Serial Plans
To obtain a formatted execution plan for serial plans:
SQL> set lines 130
SQL> set head off
SQL> spool
SQL> @@?/rdbms/admin/utlxpls
SQL> spool off
Parallel Plans
To obtain a formatted execution plan for parallel plans:
SQL> set lines 130
SQL> set head off
SQL> spool
SQL> @@?/rdbms/admin/utlxplp
SQL> spool off
Autotrace
Autotrace can also be used. 
It shows more information about distributed queries but in earlier versions, the output is often badly formatted. If using autotrace, there is no need to 'explain' the query
SQL> set lines 130
SQL> set long 2000
SQL> set head off
SQL> spool >>spool file <<
SQL> set autotrace trace explain
SQL> @@ >> your query <<
SQL> spool off
Creating Plan Table
If required, the initial steps to create a plan table and make the optimizer populate the plan table with the execution plan are common to all supported versions of Oracle.
1. Use the utlxplan.sql script to create the plan table as instructed below.
SQL> @@?/rdbms/admin/utlxplan
2. On 10g and above there is a new script - catplan.sql - to create the plan table that  
    creates a public plan table as a global temporary table accessible from any schema
SQL> @@?/rdbms/admin/catplan
Identifying the SQL
sql_id:
The SQL_ID specifies the sql_id value for a specific SQL statement, and can be found in:
V$SQL.SQL_ID, 
V$SESSION.SQL_ID, 
V$SESSION.PREV_SQL_ID,
V$SQL.SQL_ID, 
V$SESSION.SQL_ID,  
V$SESSION.PREV_SQL_ID.
If no sql_id is specified, the last executed statement of the current session is shown.
cursor_child_no:
The child cursor number specifies the child number for a specific sql cursor, and can be found in:
V$SQL.CHILD_NUMBER
V$SESSION.SQL_CHILD_NUMBER
V$SESSION.PREV_CHILD_NUMBER.

1. explain plan命令

PL/SQL Developer中通过快捷键F5可以查看目标SQL的执行计划。

实际后台调用的就是explain plan命令,相当于封装了该命令。

explain plan使用方法:

(1) 执行explain plan for + SQL
(2) 执行select * from table(dbms_xplan.display);

2. DBMS_XPLAN包

SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE '%XXX%';
(1) select * from table(dbms_xplan.display);
(2) select * from table(dbms_xplan.display_cursor(null, null, 'advanced'));
(3) select * from table(dbms_xplan.display_cursor(null, null, 'all'));
其中:
'advanced'记录的信息要比'all'多,主要就是多一个Outline Data。
Outline Data主要是执行SQL时用于固定执行计划的内部HINT组合,可以将这部分内容摘出来加到目标SQL中以固定其执行计划。
(4) select * from table(dbms_xplan.display_cursor('sql_id/hash_value', child_cursor_number, 'advanced'));
(5) select * from table(dbms_xplan.display_awr('sql_id'));
select plan_table_output from table (dbms_xplan.display_awr('4bctzmucz7fxg',null,null,'ADVANCED +PEEKED_BINDS'));

(2)从awr性能视图里获取
SELECT * FROM TABLE(dbms_xplan.display_awr('SQL_ID'));
优点: 
(1)知道sql_id就可以得到执行计划,和explain plan for一样无须执行;
(2)可以得到真实执行计划;
缺点:
(1)没有输出运行时的相关统计信息(例如:逻辑读,递归调用次数,物理读等)
(2)无法判断处理多少上;
(3)无法判断表访问多少次;

如何找出SQL_ID:

How to Determine the SQL_ID for a SQL Statement (Doc ID 1627387.1)
For Example:
SELECT /* TARGET SQL */ * FROM dual;
SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text  
FROM  v$sql 
WHERE sql_text like 'SELECT /* TARGET SQL */%'
SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ----------------------------------------
0xzhrtn5gkpjs       272002086 SELECT /* TARGET SQL */ * FROM dual
SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&An_Identifiable_String%';

如果SQL_ID已经不在v$sql中,可以查看dba_hist_sqlstat和dba_hist_sqltext。

SELECT
    s.sql_id,
    s.plan_hash_value,
    t.sql_text,
    s.snap_id
FROM
    dba_hist_sqlstat   s,
    dba_hist_sqltext   t
WHERE s.dbid = t.dbid
AND   s.sql_id = t.sql_id
AND   sql_text LIKE 'SELECT /* TARGET SQL */%'
ORDER BY
    s.sql_id;

2 autotrace

SQL> conn scott/tiger
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
---SYS@PROD1>@?/sqlplus/admin/plustrce.sql
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL> grant plustrace to scott;

显示结果集以及执行计划

SQL> set autotrace on 

不显示结果集,显示执行计划

SQL> set autotrace traceonly

优点: 

(1)可以输出运行时的相关统计信息(产生多少逻辑读,多少次递归调用,多少次物理读)

(2)traceonly可以控制返回结果不输出;

缺点: 

(1)必须等到语句真正执行完毕后,才可以出结果;

(2)无法看到表被访问多少次;


设置Autotrace的命令

1 此为默认值,即关闭Autotrace
SET AUTOTRACE OFF
2 产生结果集和解释计划并列出统计
SET AUTOTRACE ON
3 显示结果集和解释计划不显示统计
SET AUTOTRACE ON EXPLAIN
4 显示解释计划和统计,尽管执行该语句但您将看不到结果集
SET AUTOTRACE TRACEONLY
5 只显示统计
SET AUTOTRACE TRACEONLY STATISTICS

statistics_level=all 

(1)alter session set statistics_level=all; 
(2)执行SQL; 
(3)select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
示例: 
select /*+ gather_plan_statistics */  * from t1 where exists (select 1 from scott.emp a where t1.empno=a.empno);
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

另注:

1. /*+ gather_plan_statistics */可以省略第一步骤;

2. 关键字解读 

(1)starts为该SQL执行的次数;
(2)E-Rows为执行计划预计的行数;
(3)A-Rows为实际返回的行数。A-Rows和E-Rows做比较,就可以确定哪一步执行计划出了问题。
(4)A-Time为每一步实际执行的时间(HH:MM:SS.FF),根据这一行可以知道该SQL耗时在哪个地方;
(5)buffers为每一步实际执行的逻辑读或一致性读;
(6)Reads为物理读;

优点:

(1)可以清晰的从STARTS得出表被访问多少次;
(2)可以清晰的从E-ROWS和A-ROWS中得到预测的行数和真实的行数,从而可以准确判断Oracle评估是否准确;
(3)BUFFER显示真实的逻辑读数量;

缺点:

(1)必须要等到语句真正执行完毕后,才能出结果;
(2)无法控制记录不打屏输出,没有类似autotrace的traceonly的功能;
(3)看不出递归调用的次数,看不出物理读;

4 10046

设置追踪标识符
alter session set tracefile_identifier='10046';
开启跟踪
alter session set events '10046 trace name context forever, level 12';
执行语句
select count(*) from all_objects;
关闭10046追踪
alter session set events '10046 trace name context off';
当退出当前会话的时候,Oracle就会将追踪的结果写入到trace文件目录
使用Oracle提供的tkprof来格式化打开追踪文件,进行分析,如下:
tkprof ora_2229_10046.trc 888.trc
vim 888.trc

---开启会话跟踪:alter system set events ‘10046 trace name context forever, level 12’;
---关闭会话跟踪:alter system set events ‘10046 trace name context off’;
---收集特定session的10046
SPID 是操作系统的进程标识符(os pid)
PID 是Oracle的进程标识符(ora pid)
假设需要被跟踪的OSPID是9834,以sysdba的身份登录到SQL*Plus并执行下面的命令:
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
注: 也可以通过oradebug使用 'setorapid'命令连接到一个session。
下面的例中, 使用PID(Oracle进程标识符)(而不是SPID), oradebug命令将被改为:
connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
跟踪过程完成以后,关闭oradebug跟踪:
oradebug event 10046 trace name context off
SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
       p.spid || '.trc' AS "trace_file_name"
  FROM (SELECT p.spid
          FROM v$mystat m, v$session s, v$process p
         WHERE m.statistic# = 1
           AND s.SID = m.SID
           AND p.addr = s.paddr) p,
       (SELECT t.INSTANCE
          FROM v$thread t, v$parameter v
         WHERE v.NAME = 'thread'
           AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
       (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
优点:
(1)可以看出SQL语句对应的等待事件;
(2)如果SQL语句中有函数调用,函数中又有SQL,将会被列出;
(3)可以方便的看出处理的行数,产生的物理读,逻辑读;
(4)可以方便的看出解析时间和执行计划;
(5)可以跟踪整个程序包;
缺点: 
(1)步骤繁琐;
(2)无法判断表被访问多少次; 
(3)执行计划中谓词部分不能清晰展现出来;
awrsqrpt.sql 
(1)@?/rdbms/admin/awrsqrpt.sql
(2)begin snap,end snap 
(3)输入sql_id 
---执行计划顺序
就是从第ID=0的向下看,一路向下,如果缩进被挡住了,就是最先执行的。。。如果同级,是在上面的先执行
除了标量子查询啥的,不符合这种规则。。
使用AUTOTRACE或者EXPLAIN PLAN FOR获取的执行计划来自于PLAN_TABLE。
PLAN_TABLE是一个会话级的临时表,里面的执行计划并不是SQL真实的执行计划,它只是优化器估算出来的。
真实的执行计划不应该是估算的,应该是真正执行过的。
SQL执行过的执行计划存在于共享池中,具体存在于数据字典V$SQL_PLAN中,带有A-Time的执行计划来自于V$SQL_PLAN,是真实的执行计划,
而通过AUTOTRACE、通过EXPLAIN PLAN FOR获取的执行计划只是优化器估算获得的执行计划。
---跟踪用户SQL
select sql_text, sql_fulltext, sql_id, first_load_time, LAST_ACTIVE_TIME
  from v$sqlarea t
 where t.PARSING_SCHEMA_NAME in ('K2_20181205')
 order by t.LAST_ACTIVE_TIME desc;

5 10053

SQL> alter session set events '10053 trace name context forever,level 1';
SQL> explain plan for select count(*) from obj$;

10046

a

lter session set tracefile_identifier='10046';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
...
ALTER SESSION SET EVENTS '10046 trace name context off';
tkprof 原文件.trc 目标文件

select p.PID, p.SPID, s.SID
  from v$process p, v$session s
 where s.paddr = p.addr
   and s.sid = &SESSION_ID
###oradebug
connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
跟踪过程完成以后,关闭oradebug跟踪:
oradebug event 10046 trace name context off
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL>--等上30秒到1分钟
SQL> oradebug dump systemstate 266
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/nctst/nctst/trace/nctst_ora_27983.trc
alter session set tracefile_identifier='10053';
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
......
ALTER SESSION SET EVENTS '10053 trace name context off';
select value from v$diag_info where name='Default Trace File';
====trace
--SQL 10046
alter session set tracefile_identifier='enmo10046';
alter session set events '10046 trace name context forever, level 12';
run your sql;
alter session set events '10046 trace name context off';
--如果会话已经运行了,可以用oradebug
conn / as sysdba
oradebug setospid 16835
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug event 10046 trace name context off
--systemstate dump
sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 266;
--wait for 1 min
oradebug dump systemstate 266;
--wait for 1 min
oradebug dump systemstate 266;
oradebug tracefile_name;
--hanganalyze
oradebug setmypid
oradebug unlimit;
oradebug dump hanganalyze 3
--wait for 1 min
oradebug dump hanganalyze 3
--wait for 1 min
oradebug dump hanganalyze 3
oradebug tracefile_name;

###查看SQL历史执行计划的变更

---DBA_HIST_SQL_PLAN,DBA_HIST_SQLSTAT,DBA_HIST_SNAPSHOT
select distinct SQL_ID,
                PLAN_HASH_VALUE,
                to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss') TIMESTAMP
  from dba_hist_sql_plan
 where SQL_ID = '2fjy1vnu83bwj'
 order by TIMESTAMP desc;
---
select plan_hash_value,
       id,
       operation,
       options,
       object_name,
       depth,
       cost,
       to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss')
  from DBA_HIST_SQL_PLAN
 where sql_id = '2fjy1vnu83bwj'
   and plan_hash_value in (4030773303,801583493)
 order by ID, TIMESTAMP;
---执行计划变更 
select distinct SQL_ID,
                PLAN_HASH_VALUE,
                to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss') TIMESTAMP
  from dba_hist_sql_plan
 where SQL_ID = 'afqfknn3nwwpw'
 order by TIMESTAMP;
 
select plan_hash_value,
       id,
       operation,
       options,
       object_name,
       depth,
       cost,
       to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss')
  from DBA_HIST_SQL_PLAN
 where sql_id = 'afqfknn3nwwpw'
   and plan_hash_value in (1542630049, 2754593971, 2620382595)
 order by ID, TIMESTAMP;

查看执行计划历史信息:

oracle 10G 以后可以通过下面的三个视图查询到sql执行计划的历史信息:

DBA_HIST_SQL_PLAN

DBA_HIST_SQLSTAT

DBA_HIST_SNAPSHOT

使用如下sql 可以发现某个sql的执行计划什么时候发生了变化!

select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss')  TIMESTAMP

from dba_hist_sql_plan 

where SQL_ID='68wnxdjxwwn2h' order by TIMESTAMP;

查看出来执行计划的变化之后 可以使用如下sql查看发生了那些变化!

col options for a15
col operation for a20
col object_name for a20
select plan_hash_value,id,operation,options,object_name,depth,cost,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss')
    from DBA_HIST_SQL_PLAN  
    where sql_id ='68wnxdjxwwn2h' 
    and plan_hash_value in (1542630049,2754593971,2620382595)
    order by ID,TIMESTAMP;

###固定执行计划###

http://blog.itpub.net/29785807/viewspace-2643074/

1.hint

2.存储大纲stored outline

3.sql_profile

4.spm


How to Use SQL Plan Management (SPM) - Plan Stability Worked Example (Doc ID 456518.1)

SQL计划管理(SPM)是一种预防性机制,它记录和评估SQL语句的执行计划。

SPM构建SQL计划基线,由一组已知有效的现有计划组成。

然后使用SQL计划基线来保持相应SQL语句的性能,而不管系统中发生什么变化,从而提供“计划稳定性”。


SQL计划管理可以提高或保持SQL性能的常见使用场景包括:

数据库升级
安装新优化器版本的数据库升级通常会导致一小部分SQL语句的计划更改,而大多数计划更改不会导致性能更改或改进。
但是,某些计划变更可能会导致绩效下降。
SQL计划基线的使用大大减少了数据库升级导致的潜在性能下降。
系统/数据更改
正在进行的系统和数据更改可能会影响某些SQL语句的计划,可能会导致性能下降。SQL计划基线的使用将有助于最小化性能退化并稳定SQL性能。
应用程序升级
部署新的应用程序模块意味着在系统中引入新的SQL语句。应用软件可以使用在标准测试配置下为新SQL语句开发的适当SQL执行计划。
如果您的系统配置与测试配置明显不同,那么SQL计划基线可以随着时间的推移而变化,以产生更好的性能。

如果您在游标缓存中有一个好的计划,那么您可以将这些计划加载到SPM中,以便可以使用此基线来保持性能。以下示例命令说明了这一点:
set serveroutput on
var n number
begin
:n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'&sql_id', plan_hash_value=>&plan_hash_value, fixed =>'NO', enabled=>'YES');
end;
/
EXEC dbms_output.put_line('Number of plans loaded: ' || :n);

启用SPM后,对于那些具有SQL计划管理基线的SQL ID,数据库将不会收集新的访问计划(即使在禁用SPM后-假设基线处于活动状态)

本文档旨在展示一个SQL计划管理示例。

下面是一个脚本,您可以运行该脚本来演示SPM如何操作,然后将基本原则应用到实际代码中。

脚本包含注释,用于解释运行时发生的情况。

该脚本最初是在SH模式的11.1.0.6.0上创建和测试的。其他版本的输出可能略有不同。

SCRIPT spm.sql


###绑定变量

SELECT snap_id, NAME, position , value_string, last_captured,WAS_CAPTURED
FROM dba_hist_sqlbind;

还有一个dba_hist_sqlbind也是记录了awr中绑定变量的内容

不过这两个视图中记录的绑定变量只对where条件后面的绑定进行捕获,这点需要使用的时候注意。

绑定变量

10g 数据倾斜严重时,使用绑定变量,导致所有同类SQL都走同一个执行计划,部分SQL效率低;

11g 游标自适应,可以自动从多个执行计划中找到最优的执行计划,避免这个问题;


###二:统计信息篇###

表级别统计信息

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'cjc',tabname => 't1',estimate_percent=>100,method_opt=> 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE,no_invalidate=> FALSE,degree=>);
EXEC DBMS_STATS.GATHER_TABLE_STATS('k2_20181211','t_BD_AccountView',estimate_percent=>100,method_opt=> 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('OWNER','TABLE',estimate_percent=>30,CASCADE=> TRUE);
dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);
EXEC DBMS_STATS.GATHER_TABLE_STATS('OWNER','TABLE',estimate_percent=>30,CASCADE=> TRUE);

用户级别统计信息

exec dbms_stats.gather_schema_stats('HR');


数据库级统计信息

exec DBMS_STATS.GATHER_DATABASE_STATS (estimate_percent=>10,Degree=>8, Cascade=>TRUE, Granularity=>'ALL');

收集用户下指定表

How To Run DBMS_STATS.GATHER_TABLE_STATS Using Dynamic Query (Doc ID 1072911.1)

create or replace procedure getstattest IS
str1 varchar2(1000);
cnt NUMBER;
CURSOR c_C1 IS select TABLE_NAME, OWNER from dba_tables where OWNER IN ('SCOTT') and TABLE_NAME IN ('EMP', 'DEPT','BONUS','SALGRADE');
begin
FOR tab_rec IN c_C1
LOOP
cnt:=0;
dbms_output.put_line('Checking OWNER: '||tab_rec.OWNER);
dbms_output.put_line('Checking TAB: '||tab_rec.TABLE_NAME);
execute immediate 'SELECT COUNT (*) FROM '|| tab_rec.TABLE_NAME INTO cnt;
if cnt > 2 then
str1:='BEGIN dbms_stats.gather_table_stats(ownname =>'''||tab_rec.OWNER||''',tabname =>'''||tab_rec.TABLE_NAME||''',cascade => true,degree=>
0); END;';
dbms_output.put_line(str1);
execute immediate str1;
end if;
cnt:=0;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
       DBMS_OUTPUT.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM||' '||str1);
END;
/
SQL> set serverout on;
SQL> exec  getstattest

自动统计信息收集 

10g

gather_stats_job Scheduler调度


select program_name, schedule_name, schedule_type, enabled, state
  from dba_scheduler_jobs
 where owner = 'SYS'
   and job_name = 'GATHER_STATS_JOB';
select program_action, number_of_arguments, enabled
  from dba_scheduler_programs
 where owner = 'SYS'
   and program_name = 'GATHER_STATS_PROG';
select w.window_name, w.repeat_interval, w.duration, w.enabled
  from dba_scheduler_wingroup_members m, dba_scheduler_windows w
 where m.window_name = w.window_name
   and m.window_group_name = 'MAINTENANCE_WINDOW_GROUP';

11g 自动维护任务

select task_name, status
  from dba_autotask_task
 where client_name = 'auto optimizer stats collection';
select program_action, number_of_arguments, enable
  from dba_scheduler_programs
 where owner = 'SYS'
   and program_name = 'GATHER_STATS_PROG';
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.ENABLE(client_name => 'auto optimizer stats collection',
                              operation   => NULL,
                              window_name => NULL);
END;
/

禁用

BEGIN

  DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',

                               operation   => NULL,

                               window_name => NULL);

END;

/

---收集某一列的直方图

analyze table gl_detail compute statistics for columns prepareddatev size 254;
EXEC DBMS_STATS.GATHER_TABLE_STATS('OWNER','TABLE',estimate_percent=>30,method_opt => 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE);
exec dbms_stats.gather_table_stats('chen','test1',method_opt=>'for columns (ACCYEAR,ACCMONTH) size AUTO');
或
begin
  dbms_stats.gather_table_stats('chen',
                                'test1',
                                method_opt => 'for columns status size AUTO');
end;
例如:
select count(distinct id) from t1; ---num_distinct=5001>num_buckets(default=75)
select count(distinct id) from t2; ---num_distinct=3<num_buckets(default=75) ---frequence histograms

---查看列的直方图信息

select table_name,column_name,density,num_distinct,num_buckets,histogram from user_tab_col_statistics;

频率直方图(frequence histograms)
频率直方图列中的不同值被划到相同数量的桶中.每一个桶中存储的都是相同的值,也就是说频率直方图的桶数等于列的不同值的个数.buckets=ndv
高度平衡直方图(height-balanced histograms)
在频率直方图中oracle给每一个不同值分配一个桶,然而桶的最大个数是254,因此如果表中的列有大量的不同值(超过254),将会创建一个高度平衡的直方图.
在高度平衡直方图中,因为我们的不同值超过了桶的个数,因此oracle首先分对列数据进行排序然后将数据集按桶数进行分类且除了最后一桶可能包含的数据比其它的桶少以外,
所有其它的桶包含相同数量的值(这就是为什么叫高度平等直方图的原因).

---列相关性

列的相关性:就是where子句中同时存在2个列以上条件时,CBO不做列与列之间相关性分析,这会导致CBO计算执行计划出现一种偏差
select count(*) from leo6 where object_type='TABLE' and owner='LEO1';
execute dbms_stats.gather_table_stats('LEO1','LEO6',method_opt=>'for columns (object_type,owner) size skewonly');

---收集统计信息(完全计算法): 

analyze table abc compute statistics;

---删除统计信息(级联删除列直方图)

begin
  dbms_stats.delete_table_stats(ownname => 'CHEN', tabname => 'TEST1');
end;
或
ANALYZE TABLE TEST1 DELETE STATISTICS;

###收集统计信息[抽样估算法(抽样20%)]: 

analyze table abc estimate statistics sample 20 percent;

###数据库级统计信息

rq="start time":`date +"%Y-%m-%d %H:%M:%S"`
echo $rq>>/oracle/scripts/stats.log
sqlplus /nolog <<EOF
connect / as sysdba;
exec DBMS_STATS.GATHER_DATABASE_STATS (estimate_percent=>10,Degree=>8, Cascade=>TRUE, Granularity=>'ALL');
EOF
rq="stop time":`date +"%Y-%m-%d %H:%M:%S"`echo $rq>>/oracle/scripts/stats.log

收集一下字典表的统计信息,递归SQL执行计划有问题

exec dbms_stats.gather_fixed_objects_stats;
exec dbms_stats.gather_dictionary_stats;
execute dbms_stats.gather_schema_stats(‘SYS’);

统计信息的导入和导出

(1) 首先创建一个分析表,该表是用来保存之前的分析值:

begin
  dbms_stats.create_stat_table(ownname => 'CHEN', stattab => 'STAT_TABLE',cascade => true);
end;

(2) 导出表分析信息到stat_table中

begin
  dbms_stats.export_table_stats(ownname => 'CHEN',
                                tabname => 'T1',
                                stattab => 'STAT_TABLE');
end;

(3)开始更新T1表统计信息

begin
  dbms_stats.gather_table_stats(ownname => 'CHEN', tabname => 'T1');
end;
/*
begin
  dbms_stats.gather_table_stats(ownname => 'CHEN',
                                tabname => 'T1',
                                cascade => true);
end;
*/

(4)删除分析信息

begin
  dbms_stats.delete_table_stats(ownname => 'CHEN', tabname => 'T1');
end;

(5)导入分析信息

begin
  dbms_stats.import_table_stats(ownname => 'CHEN',
                                tabname => 'T1',
                                stattab => 'STAT_TABLE');
end;

###自动收集统计信息

---这一步非常重要,需要显式地赋予用户建表权限
grant create any table to chen ;
---创建收集统计信息的存储过程
CREATE OR REPLACE PROCEDURE ANALYZE_TB AS
  OWNER_NAME  VARCHAR2(100);
  V_LOG       INTEGER;
  V_SQL1      VARCHAR2(800);
  V_TABLENAME VARCHAR2(50);
  CURSOR CUR_LOG IS
    SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = 'ANALYZE_LOG';
  --1
BEGIN
  --DBMS_OUTPUT.ENABLE (buffer_size=>100000);
  --1.1
  BEGIN
    OPEN CUR_LOG;
    FETCH CUR_LOG
      INTO V_LOG;
    IF V_LOG = 0 THEN
      EXECUTE IMMEDIATE 'CREATE TABLE ANALYZE_LOG (USER_NAME VARCHAR(20),OP_TIME CHAR(19) DEFAULT to_char(sysdate,''yyyy-mm-dd hh24:mi:ss''),ERROR_TEXT VARCHAR(200),TABLE_NAME VARCHAR(40))';
    END IF;
  END;
  SELECT USER INTO OWNER_NAME FROM DUAL;
  V_SQL1 := 'INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES (''' ||
            OWNER_NAME || ''',''ANALYZE BEGIN'',''ALL'')';
  EXECUTE IMMEDIATE V_SQL1;
  sys.dbms_stats.gather_schema_stats(ownname          => UPPER(OWNER_NAME),
                                     estimate_percent => 100,
                                     method_opt       => 'FOR ALL INDEXED COLUMNS',
                                     cascade          => TRUE);
  V_SQL1 := 'INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES (''' ||
            OWNER_NAME || ''',''ANALYZE END'',''ALL'')';
  EXECUTE IMMEDIATE V_SQL1;
  commit;
  --1.2 delete tmptb statitics and lock statistics
  BEGIN
    for x in (select a.table_name, a.last_analyzed, b.stattype_locked
                from user_tables a, user_tab_statistics b
               where a.temporary = 'Y'
                 and a.table_name = b.table_name
                 and (b.STATTYPE_LOCKED is null or
                     a.last_analyzed is not null)) LOOP
      IF x.last_analyzed IS NOT NULL THEN
        --delete stats
        dbms_stats.delete_table_stats(ownname => user,
                                      tabname => x.table_name,
                                      force   => TRUE);
      END IF;
    
      IF x.stattype_locked IS NULL THEN
        --lock stats
        dbms_stats.lock_table_stats(ownname => user,
                                    tabname => x.table_name);
      END IF;
    END LOOP;
  end;
EXCEPTION
  WHEN OTHERS THEN
    IF CUR_LOG%ISOPEN THEN
    
      CLOSE CUR_LOG;
    END IF;
    commit;
end;

- --下面提供的脚本示范如何创建定时任务,也是要在"sqlplus"中运行。当天的凌晨2点开始更新统计信息,以后每2天的凌晨2点更新统计信息。注意建立当前job时,使用NC的用户连接数据库执行。具体优化时间设置用户根据实际情况灵活调整。

SQL> VARIABLE JOBNO NUMBER;
SQL> VARIABLE INSTNO NUMBER;
SQL> 
SQL> BEGIN
  2    SELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE;
  3    DBMS_JOB.SUBMIT(:JOBNO,
  4                    'ANALYZE_TB; ',
  5                    TRUNC(SYSDATE) + 1 + 2 / 24,
  6                    'TRUNC(SYSDATE)+2+2/24',
  7                    TRUE,
  8                    :INSTNO);
  9    COMMIT;
 10  END;
 11  /
 
PL/SQL procedure successfully completed
INSTNO
---------
1
JOBNO
---------
5

---执行存储过程

SQL> exec ANALYZE_TB;
PL/SQL procedure successfully completed

---查看表信息
select table_name,tablespace_name,logging,last_analyzed,num_rows,temporary from user_tables;
---查看索引信息
select index_name,index_type,table_owner,table_name,table_type,last_analyzed,num_rows from user_indexes;
---查看统计信息日志
select * from analyze_log;
---注意关闭ORACLE自动更新统计信息的任务
---Oracle10g: 
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');
select job_name,
       schedule_name,
       enabled,
       last_start_date,
       last_run_duration,
       next_run_date
  from dba_scheduler_jobs a
 where job_name = 'GATHER_STATS_JOB';
 
SQL>Exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
    exec dbms_scheduler.disable('gather_stats_job'); 
---Oracle11g: 
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto optimizer stats collection',
                               operation   => NULL,
                               window_name => NULL);
END;
---SQL> select client_name,status from dba_autotask_client;
禁用自动收集统计信息的任务可以使用DBMS_AUTO_TASK_ADMIN包完成:
SQL> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
启用自动收集统计信息的任务
SQL> exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
ANALYZE TABLE wa_cacu_data DELETE STATISTICS;
SQL> execute dbms_stats.lock_table_stats(ownname=>'CHEN',tabname=>'WA_CACU_DATA');
SQL> execute dbms_stats.unlock_table_stats(ownname=>'CHEN',tabname=>'WA_CACU_DATA');

========
移除JOB
========
exec dbms_job.remove(1233)
========
停止JOB
========
select sid from dba_jobs_running;
select a.spid from v$process a ,v$session b where a.addr=b.paddr  and b.sid in (select sid from dba_jobs_running);
使用DBMS_JOB包来标识你的JOB为BROKEN。
SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);
注意:当执行完该命令你选择的这个JOB还是在运行着的。 
Kill 对应的Oracle Session
ALTER SYSTEM KILL SESSION 'sid,serial#';
或者直接KILL对应的操作系统的SESSION,如果使用ALTER SYSTEM KILL SESSION执行很长时间,其实可以使用OS的命令来快速KILL掉SESSION.
For Windows, at the DOS Prompt: orakill sid spid
For UNIX at the command line> kill –9 spid
---定制job
SQL> variable job1 number;
SQL> begin
  2  dbms_job.submit(:job1,'p_test;',sysdate,'sysdate+1/1440');
  3  end;
  4  /
---创建job
SQL> begin
  2  dbms_job.run(23);
  3  end;
  4  /
PL/SQL procedure successfully completed
---删除JOB
SQL> begin
  2  dbms_job.remove(23);
  3  end;
  4  /
 
PL/SQL procedure successfully completed
===============
停止JOB 
===============
ALTER SYSTEM KILL SESSION 'sid,serial#';
SQL> select name,value from v$parameter where name ='job_queue_processes';     
SQL> ALTER SYSTEM SET job_queue_processes = 0;
恢复job_queue_processes的原始值
SQL> ALTER SYSTEM SET job_queue_processes = original_value;
##查询jobs
set lin 200
col interval for a30
col last_sec for a10
col this_sec for a10
col next_sec for a10
col schema_user for a20
col log_user for a20
col what for a80
select job,schema_user,this_date,this_sec,next_date,next_sec,interval,what from dba_jobs where  schema_user not in ('APEX_030200');
--scheduler
set pages 200 lin 200
col owner for a10
col job_name for a30
col START_DATE for a40
select owner,job_name,START_DATE from dba_scheduler_jobs where owner not in ('EXFSYS','SYS','ORACLE_OCM');

###三:AWR报告篇###

SQL> Exec dbms_workload_repository.create_snapshot; 
SQL> SHOW PARAMETER STATISTICS_LEVEL

如果STATISTICS_LEVEL的值为TYPICAL或者 ALL,表示启用AWR;如果STATISTICS_LEVEL的值为BASIC,表示禁用AWR。

AWR报告,只是产生不同的AWR报告,需要运行不同的脚本。

--产生整个数据库的AWR报告,运行脚本awrrpt.sql。
@$ORACLE_HOME/rdbms/admin/awrrpt.sql 
--产生某个实例的AWR报告,运行脚本awrrpti.sql。
@$ORACLE_HOME/rdbms/admin/awrrpti.sql 
--产生某条SQL语句的AWR报告,运行脚本awrsqrpt.sql。
 @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

RAC AWR

在11.2以前,对于RAC数据库只能为多个实例分别生成AWR报告,而11.2中,Oracle终于可以将所有实例AWR报告汇总到一起。

@?/rdbms/admin/awrgrpt
1 awr
@?/rdbms/admin/awrrpt.sql
2 ash
@?/rdbms/admin/ashrpt.sql
3 addm
@?/rdbms/admin/addmrpt.sql
4 awrsqrpt
@?/rdbms/admin/awrsqrpt.sql
5 awrdd
@?/rdbms/admin/awrddrpt.sql

###三:TOP SQL篇###

--数据库繁忙程度----

set line 200 pages 2000
col DB_TIME_US for 9999999999999.9999
select b.instance_number,
       b.snap_id as begin_snap,
       lead(b.snap_Id,1,null) over (order by b.snap_Id) as end_snap,
       to_char(b.end_interval_time,'YYYY-MM-DD hh24:MI:SS') as begin_time ,
       to_char(lead(b.end_interval_time,1,null) over (order by b.snap_id),'YYYY-MM-DD hh24:MI:SS') as end_time,   
       extract(hour from  lead(b.end_interval_time,1,null) over (order by b.snap_id) - b.end_interval_time ) * 60 
       + extract(minute from  lead(b.end_interval_time,1,null) over (order by b.snap_id) - b.end_interval_time )  as Elapsed_minutes,       
       a.value as db_time_us ,
       round((lead(a.value,1,null) over (order by b.snap_id) -  a.value)/1000000/60,2) as DbTime_minutes
  from sys.WRH$_SYS_TIME_MODEL A, dba_hist_snapshot B, sys.wrh$_stat_name C
 where a.dbid = b.dbid
   and a.instance_number=b.instance_number
   and a.snap_id = b.snap_id
   and a.stat_id = c.stat_id
   and b.instance_number=1
   and c.stat_name = 'DB time'
   and b.end_interval_time>systimestamp -7
   ORDER BY 8 DESC;

--查看latch那个数据字典争用严重

set lin 200 pages 200
col cache# head "Cache|no" form 999
col parameter head "Parameter" form a25
col type head "Type" form a12
col subordinate# head "Sub|ordi|nate" form 9999
col rcgets head "Cache|Gets" form 999999999999
col rcmisses head "Cache|Misses" form 999999999999
col rcmodifications head "Cache|Modifica|tions" form 999999999999
col rcflushes head "Cache|Flushes" form 999999999999
col kqrstcln head "Child#" form 999
col lagets head "Latch|Gets" form 999999999999
col lamisses head "Latch|Misses" form 999999999999
col laimge head "Latch|Immediate|gets" form 999999999999
select dc.kqrstcid CACHE#, dc.kqrsttxt PARAMETER, decode(dc.kqrsttyp, 1,'PARENT','SUBORDINATE') type,
decode(dc.kqrsttyp, 2, kqrstsno, null) subordinate#,
dc.kqrstgrq rcgets, dc.kqrstgmi rcmisses, dc.kqrstmrq rcmodifications, dc.kqrstmfl rcflushes, dc.kqrstcln, 
la.gets lagets, la.misses lamisses, la.immediate_gets laimge from x$kqrst dc, v$latch_children  la 
where dc.inst_id = userenv('instance') 
and la.child# = dc.kqrstcln 
and la.name = 'row cache objects' 
order by rcgets desc
/

--查看历史数据增长量,通过数据文件判断

SELECT
    TO_CHAR(creation_time, 'RRRR-MM')  month
  , SUM(bytes)/1024/1024                         growth_MB
FROM     sys.v_$datafile
GROUP BY TO_CHAR(creation_time, 'RRRR-MM')
ORDER BY TO_CHAR(creation_time, 'RRRR-MM');
SELECT
    TO_CHAR(creation_time, 'RRRR-MM-DD')  month
  , SUM(bytes)/1024/1024                         growth_MB
FROM     sys.v_$datafile
GROUP BY TO_CHAR(creation_time, 'RRRR-MM-DD')
ORDER BY TO_CHAR(creation_time, 'RRRR-MM-DD');

###索引监控

set pagesize 0;
set feedback off;
spool monitor_index.sql; 
select 'alter index CJC.' || index_name || ' monitoring usage;' from dba_indexes where owner = 'CJC';
spool off;
rem start monitor_index.sql
SQL> alter index CJC.I_T2_ID monitoring usage;

取消监控

SQL> alter index <索引名> nomonitoring usage;
SQL> select * from v$object_usage
    INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
1 I_T2_ID T2 NO YES 06/17/2020 10:14:16 06/17/2020 10:16:25

###空间碎片

以下查询是碎片最严重的前100个表的脚本及结果

col frag format 999999.99
col owner format a30;
col table_name format a30;
select *
  from (select a.owner "用户名",
               a.table_name "表名",
               a.num_rows "记录数",
               a.avg_row_len * a.num_rows "需要空间",
               sum(b.bytes) "实际空间",
               (a.avg_row_len * a.num_rows) / sum(b.bytes) "碎片率"
          from dba_tables a, dba_segments b
         where a.table_name = b.segment_name
           and a.owner = b.owner
           and a.num_rows <> 0
           ---and a.table_name = 'XXX'
           and a.owner not in ('SYS',
                               'SYSTEM',
                               'OUTLN',
                               'DMSYS',
                               'TSMSYS',
                               'DBSNMP',
                               'WMSYS',
                               'EXFSYS',
                               'CTXSYS',
                               'XDB',
                               'OLAPSYS',
                               'ORDSYS',
                               'MDSYS',
                               'SYSMAN')
         group by a.owner, a.table_name, a.avg_row_len, a.num_rows
        having a.avg_row_len * a.num_rows / sum(b.bytes) < 0.7
         order by sum(b.bytes) desc)
 where rownum <= 100;

###监控,统计表DML次数

user_tab_modifications
dba_tab_modifications
select * from user_tab_modifications order by updates desc;
select table_name, last_analyzed, num_rows, modify_rows, modify_rate
  from (select t.table_name,
               t.last_analyzed,
               t.num_rows,
               m.modify_rows,
               round(m.modify_rows / t.num_rows * 100, 2) as modify_rate
          from user_tables t
         inner join (select table_name,
                           inserts + updates + deletes as modify_rows
                      from user_tab_modifications
                     where inserts + updates + deletes > 0) M
            on t.table_name = m.table_name
           and t.num_rows > 0)
 where modify_rate > 10
 order by modify_rate;

###top sql

---查看逻辑读前10的SQL:

SELECT *
  FROM (SELECT sql_fulltext AS sql,
               SQL_ID,
               buffer_gets / executions AS "Gets/Exec",
               buffer_gets,
               executions
          FROM V$SQLAREA
         WHERE buffer_gets > 10000
         ORDER BY "Gets/Exec" DESC)
 WHERE rownum <= 10;

---查看物理读前10的SQL:

SELECT *
  FROM (SELECT sql_fulltext AS sql,
               SQL_ID,
               disk_reads / executions AS "Reads/Exec",
               disk_reads,
               executions
          FROM V$SQLAREA
         WHERE disk_reads > 1000
         ORDER BY "Reads/Exec" DESC)
 WHERE rownum <= 10;

---elapsed_time

select a.*, elapsed_seconds / executions elapsed_per
  from (select sql_text,
               round(elapsed_time / 1000000, 2) elapsed_seconds,
               executions,
               sql_id,
               buffer_gets,
               disk_reads
          from (select * from v$sql order by elapsed_time desc)
         where rownum <= 100) a
 order by elapsed_per desc;

--查看pga占用最多的进程

select p.spid,p.pid,s.sid,s.serial#,s.status,p.pga_alloc_mem,s.username,s.osuser,s.program from v$process p,
v$session s where s.paddr(+)=p.addr order by p.pga_alloc_mem desc;

--查看登录时间最长的会话

select * from (select t.sid,t2.spid,t.program,t.status,t.sql_id,t.prev_sql_id,t.event,to_char(t.logon_time,'yyyymmdd hh24:mi:ss') logon,
trunc(sysdate-logon_time) from v$session t,v$process t2 where t.paddr=t2.addr and t.type<>'BACKGROUND' order by logon_time) where rownum<=20;

--查看逻辑读最多的SQL
select * from (select sql_id,sql_text,s.executions,s.last_load_time,s.first_load_time,s.disk_reads,s.buffer_gets from v$sql s  where 
buffer_gets >300 order by buffer_gets desc) where rownuM<20;
--物理读最多的SQL
select * from (select sql_id,sql_text,s.executions,s.last_load_time,s.first_load_time,s.disk_reads,s.buffer_gets,PARSE_CALLS from v$sql s  where 
disk_reads >300 order by disk_reads desc) where rownuM<20;
--执行最多的SQL
select * from (select sql_id,sql_text,s.executions,s.last_load_time,s.first_load_time,s.disk_reads,s.buffer_gets,PARSE_CALLS from v$sql s   X 
order by executions desc) where rownuM<20;
--查看解析次数最多的SQL
select * from (select sql_id,sql_text,s.executions,s.last_load_time,s.first_load_time,s.disk_reads,s.buffer_gets,PARSE_CALLS from v$sql s    
order by PARSE_CALLS desc) where rownuM<20;
--查看disk sort 严重的SQL
SELECT SESS.USERNAME,SQL.SQL_TEXT,sql.address,sort1.blocks from v$session sess,
v$sqlarea sql,v$sort_usage sort1 where sess.serial#=sort1.session_num and sort1.sqladdr=sql.address
and sort1.sqlhash=sql.hash_value and sort1.blocks>200 order by sort1.blocks desc;
--查看等待产生次数及相关会话,过分提交的SQL
select t1.sid,t1.value,t2.name from v$sesstat t1,v$statname t2 where 
t2.name like '%user commits%' and t1.statistic#=t2.statistic# and value >=10000;
select t.sid,t.program,t.machine,to_char(t.logon_time,'yyyymmdd hh24:mi:ss') logon,t.wait_class,t.wait_time,
t.seconds_in_wait,t.event,t.sql_id,t.prev_sql_id from v$session t where sid in (xxx,xx);
--查看共享内存使用大小
select count(*),round(sum(sharable_mem)/1024/1024,2) MB from v$db_object_cache a;
--可以获取系统中应该使用绑定变量的SQL
--检查是否有显著未释放的高水位表,一个块放5条记录,降低高水位通过:shrink table 、move table、rename table
select table_name,blocks,num_rows from dba_tables where blocks/num_rows>=0.2 and
num_rows is not null and num_rows<>0 and blocks>=1000;
/*+leading(t1) use_nl(t2)*/ 
  sql语句中from后第一个表为驱动表,执行计划中排序下面首个表为驱动表
  强制先访问t1表,即作为驱动表
  use_nl强制使用嵌套循环,小表做驱动表,不需要排序
  use_hash强制使用hash循环,小表做驱动表,需要排序
  use_merge强制使用排序合并,需要排序
  
--占用dbtime最高的SQL
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time 
(sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND  sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;
--等待最长的SQL
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name' 
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;
--占用CPU
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;
--查看会话占用内存
set lin 200 pages 199
col name for a20
col username for a20
SELECT   server, s.username, osuser, NAME, VALUE / 1024 / 1024 MB, s.SID, s.serial#, spid
    FROM v$session s, v$sesstat st, v$statname sn, v$process p
   WHERE st.SID = s.SID
     AND st.statistic# = sn.statistic#
     AND sn.NAME LIKE 'session pga memory'
     AND p.addr = s.paddr
   ORDER BY VALUE DESC;
--监控回滚信息
 set linesize 100 
  alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 
  select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",
         decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete" 
  from v$fast_start_transactions; 
  
###数据库性能
select s.snap_date,
       decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME",
       to_char(round(s.seconds / 60, 2)) "elapse(min)",
       round(t.db_time / 1000000 / 60, 2) "DB time(min)",
       s.redosize redo,
       round(s.redosize / s.seconds, 2) "redo/s",
       s.logicalreads logical,
       round(s.logicalreads / s.seconds, 2) "logical/s",
       physicalreads physical,
       round(s.physicalreads / s.seconds, 2) "phy/s",
       s.executes execs,
       round(s.executes / s.seconds, 2) "execs/s",
       s.parse,
       round(s.parse / s.seconds, 2) "parse/s",
       s.hardparse,
       round(s.hardparse / s.seconds, 2) "hardparse/s",
       s.transactions trans,
       round(s.transactions / s.seconds, 2) "trans/s"
  from (select curr_redo - last_redo redosize,
               curr_logicalreads - last_logicalreads logicalreads,
               curr_physicalreads - last_physicalreads physicalreads,
               curr_executes - last_executes executes,
               curr_parse - last_parse parse,
               curr_hardparse - last_hardparse hardparse,
               curr_transactions - last_transactions transactions,
               round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds,
               to_char(currtime, 'yy/mm/dd') snap_date,
               to_char(currtime, 'hh24:mi') currtime,
               currsnap_id endsnap_id,
               to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time
          from (select a.redo last_redo,
                       a.logicalreads last_logicalreads,
                       a.physicalreads last_physicalreads,
                       a.executes last_executes,
                       a.parse last_parse,
                       a.hardparse last_hardparse,
                       a.transactions last_transactions,
                       lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo,
                       lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads,
                       lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads,
                       lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes,
                       lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse,
                       lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse,
                       lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions,
                       b.end_interval_time lasttime,
                       lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime,
                       lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id,
                       b.startup_time
                  from (select snap_id,
                               dbid,
                               instance_number,
                               sum(decode(stat_name, 'redo size', value, 0)) redo,
                               sum(decode(stat_name,
                                          'session logical reads',
                                          value,
                                          0)) logicalreads,
                               sum(decode(stat_name,
                                          'physical reads',
                                          value,
                                          0)) physicalreads,
                               sum(decode(stat_name, 'execute count', value, 0)) executes,
                               sum(decode(stat_name,
                                          'parse count (total)',
                                          value,
                                          0)) parse,
                               sum(decode(stat_name,
                                          'parse count (hard)',
                                          value,
                                          0)) hardparse,
                               sum(decode(stat_name,
                                          'user rollbacks',
                                          value,
                                          'user commits',
                                          value,
                                          0)) transactions
                          from dba_hist_sysstat
                         where stat_name in
                               ('redo size',
                                'session logical reads',
                                'physical reads',
                                'execute count',
                                'user rollbacks',
                                'user commits',
                                'parse count (hard)',
                                'parse count (total)')
                         group by snap_id, dbid, instance_number) a,
                       dba_hist_snapshot b
                 where a.snap_id = b.snap_id
                   and a.dbid = b.dbid
                   and a.instance_number = b.instance_number
                 order by end_interval_time)) s,
       (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time,
               lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id
          from dba_hist_sys_time_model a, dba_hist_snapshot b
         where a.snap_id = b.snap_id
           and a.dbid = b.dbid
           and a.instance_number = b.instance_number
           and a.stat_name = 'DB time') t
 where s.endsnap_id = t.endsnap_id
 order by s.snap_date desc, time desc;

###四:格式篇###

spool常用的设置 
set colsep' ';    //域输出分隔符 
set echo off;    //显示start启动的脚本中的每个sql命令,缺省为on 
set feedback off;  //回显本次sql命令处理的记录条数,缺省为on 
set heading off;   //输出域标题,缺省为on 
set pagesize 0;   //输出每页行数,缺省为24,为了避免分页,可设定为0。 
set termout off;   //显示脚本中的命令的执行结果,缺省为on 
set trimout on;   //去除标准输出每行的拖尾空格,缺省为off 
set trimspool on;  //去除重定向(spool)输出每行的拖尾空格,缺省为off 
SQL> set timing on;     //设置显示“已用时间:XXXX”
SQL> set autotrace on-;   //设置允许对执行的sql进行分析
SQL> set trimout on; //去除标准输出每行的拖尾空格,缺省为off
SQL> set trimspool on; //去除重定向(spool)输出每行的拖尾空格,缺省为off
SQL> set echo on       //设置运行命令是是否显示语句
SQL> set echo off; //显示start启动的脚本中的每个sql命令,缺省为on
SQL> set feedback on;   //设置显示“已选择XX行”
SQL> set feedback off;   //回显本次sql命令处理的记录条数,缺省为on
SQL> set colsep''; //输出分隔符
SQL> set heading off;   //输出域标题,缺省为on
SQL> set pagesize 0;   //输出每页行数,缺省为24,为了避免分页,可设定为0。
SQL> set linesize 80;   //输出一行字符个数,缺省为80
SQL> set numwidth 12;   //输出number类型域长度,缺省为10
SQL> set termout off;   //显示脚本中的命令的执行结果,缺省为on
SQL> set serveroutput on;  //设置允许显示输出类似dbms_output
SQL> set verify off         //可以关闭和打开提示确认信息old 1和new 1的显示.
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

###bashrc
[oracle@uf2010813 ~]$ cat .bashrc 
alias sql='sqlplus / as sysdba'
[oracle@uf2010813 ~]$ source .bashrc

###glogin.sql
[oracle@uf2010813 ~]$ cd /opt/oracle/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/
[oracle@uf2010813 admin]$ cat glogin.sql 
set sqlprompt "_user'@'_connect_identifier> "
[oracle@uf2010813 admin]$ sqlplus / as sysdba
SYS@orcl>

###number显示科学计数的解决方法

SYS@orcl>  select current_scn from v$database;
CURRENT_SCN
-----------
 1.5249E+13
 
SYS@orcl> show numwidth
numwidth 10
SYS@orcl> set numwidth 20
SYS@orcl> select current_scn from v$database;
         CURRENT_SCN
--------------------
      15248666996607

###五:hint篇###

============

Oracle_HINT

============

select  /*+ full(t1) */ * from t1;--全表扫描
select  /*+ index(t1 idx_name) */ * from t1 where object_id>2;使用指定索引
select  /*+ no_index(t1 idx_name) */ * from t1 where object_id>2; --不使用指定索引
select  /*+ index_desc(t1 idx_name) */ * from t1 where object_id=2; --按索引降序顺序访问数据
select /*+ index_combine(t1 idx_name) */ * from t1;--选择位图索引
select /*+ index_ffs(t1  idx_name) */ from t1 where object_id <100; --索引快速全表扫描(把索引当作一个表看待)
select /*+ index_join(t1 idx_name1 idx_name2) */ * from t1 where object_id=5 and status='VALID'; --同时使用条件列上的相关索引
select /*+ index_ss(t1 index_name) */ * from t1 where object_id=99;  --跳跃式扫描 
select /*+ leading(t1,t) */ t.* from t,t1 where t1.object_id=t.object_id; --指定t1为驱动作,优化器先访问此表
select /*+ ordered */ t.* from t,t1 where t1.id=t.id;   --指定按from 后面表的顺序选择驱表,t作为驱动表
select /*+ use_nl(t1,t) */ t.* from t1,t where t1.object_id=t.object_id;--使用NEST LOOP表连接,适合含有小表数据关联,如一大一小(有别名,必须用别名)
select /*+ use_hash(t1,t) */ t.* from t1,t where t1.object_id=t.object_id;--使用HASH表连接,适合两个大表关联
select /*+ use_merge(t1,t) */t.* from t1,t where t1.object_id=t.object_id;--使用合并排序表连接
select /*+ no_use_nl(t1,t) */ t.* from t1,t where t1.object_id=t.object_id; --不使用NEST LOOP表连接
select /*+ no_use_hash(t1,t) */ t.* from t1,t where t1.object_id=t.object_id;--不使用HASH表连接
select /*+ no_use_merge(t1,t) */t.* from t1,t where t1.object_id=t.object_id; --不使用合并排序表连接
/*+OPTIMIZER_FEATURES_ENABLE('10.2.0.4')*/
SELECT /*+ PUSH_PRED (v1) */ t1.c1,v1.c1
/*+no_push_pred()*/
/*+ no_unnest */ 
/*+ unnest */

8.并行相关hint
select /*+ parallel(t 4) */ count(*) from t1; --开启表的4个并行度
select /*+ no_parallel(t) */ count(*) from t1; --不使用并行
表默认的degree(user_tables)如果不为0,默认开启并行针对select;
9.其它hint
select /*+ dynamic_sampling(t 4) */ * from t where id>134;--4为采样级别
select /*+ full(t1) cache(t1) */ object_id from t1;--将表t1放在LRU端最活跃处,相当于表属性的cache(keep);
--以上大部份资料来源于谭怀远《让ORACLE跑得更快》学习
/*+optimizer_features_enable('11.2.0.1')*/
alter system set "_optimizer_mjc_enabled" = false;

---基数反馈
SQL> alter system set “_optimizer_use_feedback”=false;
/*+ opt_param('_optimizer_use_feedback' 'false') cardinality(test,1) */
/*+ opt_param('_optimizer_use_feedback' 'false') */
还可以使用cardinality HINT来强制使用Cardinality Feedback 。
select /*+ cardinality(test,  1) */ count(*) from test;
ORA-08104: this index object 114615 is being online built or rebuilt

###六:会话篇###

---查看正在执行的SQL
select * from v$sql where address in (select sql_address from v$session);
---通过sql_id查看sql的历史执行计划
select * from table(dbms_xplan.display_cursor('sql_sid'));
---查看正在执行SQL和执行时间
select v.last_call_et,
       v.username,
       v.machine,
       v.program,
       v.module,
       v.sid,
       sql.sql_text,
       sql.sql_fulltext,
       sql.sql_id,
       sql.disk_reads,
       v.event
  from v$session v, v$sql sql
 where v.sql_address = sql.address
   and v.last_call_et > 0
   and v.status = 'ACTIVE'
   and v.username is not null;
   
---SQL> Exec dbms_workload_repository.create_snapshot; 
 
---SQLServer
select text, cpu,*
  from sys.sysprocesses a
 cross apply sys.dm_exec_sql_text(a.sql_handle) st
 where status <> 'sleeping'
 order by a.cpu desc;
 
---通过sql_id查看sql的历史执行计划
select * from table(dbms_xplan.display_cursor('sql_sid'));   
select sid,username,machine,program,module from v$session where module='ORACLE.EXE'
---查看绑定变量值
select * from V$SQL_BIND_CAPTURE where sql_id='';  
select name, position, last_captured, value_string
  from dba_hist_sqlbind
 where sql_id = 'd2rumw0mt86yh'
 order by last_captured desc, name

###查看阻塞信息

方法一:
---通过dba_blockers查看产生阻塞的会话
select * from dba_blockers;  ---391
 
---dba_waiters查看产生阻塞和被阻塞的会话
select waiting_session,holding_session from dba_waiters;
从dba_blockers视图中,可以看到,SID=391的session阻塞了别的session,而从dba_waiters可以看到,HOLDING_SESSION为391,等待的WAITING_SESSION为10。
方法二:
---通过v$session中的blocking_session字段查找
select sid,blocking_session from v$session where blocking_session is not null;
其中blocking_session=391是产生阻塞的会话,sid=10是被阻塞的会话
---如果可以确定产生阻塞的会话没用,可以手动杀掉
select sid,serial# from v$session where sid=391;
alter system kill session '391,34';
集群
alter system kill session '391,34,@1';
如果:v$session.status = 'KILLED'
查询spid:
SELECT s.sid, s.serial#, p.spid
FROM v$session s,v$process p
WHERE s.paddr = p.addr
/
ps -ef|grep <spid> would give something like:
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
kill -9 <spid>
orakill orcl 12345 Ps:这里要注意的是kill OS进程是在服务端操作,而不是你程序所在客户机。

###死锁

---1 查找产生阻塞会话的sid和serial#
select sid, serial#
  from v$session
 where sid in (select holdsid
                 from (select a.sid   holdsid,
                              b.sid   waitsid,
                              a.type,
                              a.id1,
                              a.id2,
                              a.ctime
                         from v$lock a, v$lock b
                        where a.id1 = b.id1
                          and a.id2 = b.id2
                          and a.block = 1and b.block = 0));
---2 kill会话(写入上面查到的sid和serial#)
alter system kill session 'SID,SERIAL#'; 
---3 
select a.spid, b.sid, b.serial#, b.username
  from v$process a, v$session b
 where a.addr = b.paddr
   and b.status = 'KILLED';
---WINDOWS 
orakill sid spid 
---Linux 
ps -ef|grep pid 
kill -9 xxx 
###查询会话信息
select inst_id,
       sid,
       serial#,
       username,
       status,
       machine,
       program,
       last_call_et
  from gv$session
 where machine in ('xxx')
   and status = 'INACTIVE'
   and type = 'USER';
###生成手动终止指定会话的SQL,根据实际情况之下kill_session列语句
select inst_id,
       sid,
       serial#,
       username,
       status,
       machine,
       program,
       last_call_et,
       'alter system kill session ' || '''' || sid || ',' || serial# || ',' || '@' ||
       inst_id || '''' || ' immediate;' as kill_session
  from gv$session
 where machine in ('xxx-db01', 'xxx-db02')
   and status = 'INACTIVE'
   and type = 'USER';
 
例如执行:
alter system kill session '1052,19,@1' immediate;
alter system kill session '1726,9,@1' immediate;
......
SELECT s.sid, s.serial#, p.spid
FROM v$session s,v$process p
WHERE s.paddr = p.addr
/

set line 300
set pagesize 100
col RESOURCE_NAME for a35
col INITIAL_ALLOCATION for a20
col LIMIT_VALUE for a20
select * from v$resource_limit;

xxx操作系统下查看当前来自xxxIP的连接

netstat -ant|grep 1521|grep 10.100.1.80
netstat -ant|grep 1521|grep 10.100.1.81
netstat -ant|grep 1521|grep 10.100.1.82
netstat -ant|grep 1521|grep 10.100.1.83
netstat -ant|grep 1521|grep 10.100.1.84

xx操作系统下查看历史来自分行大前置IP的连接时间和连接次数

cd /oracle/grid/diag/tnslsnr/xxx-db02/listener/trace
grep "HOST=.*establish.*\* 0" listener.log | awk -F'*' '{match($3,/[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+/); ip = substr($3,RSTART,RLENGTH);cnt[ip]+=1;last[ip]=$1;}END {for (i in cnt) printf "%-16s %9s %19s\n",i,cnt[i],last[i];}' | sort -k 1|grep 10.100.1.8
例如:
10.100.1.80              2 28-JAN-2021 19:08:45

查看监听连接明细

tail -300000 listener.log|grep 10.100.1.8

###session 
10G 
sessions=1.1*processes+5
11gR2
Default value
Derived: (1.5 * PROCESSES) + 22
set linesize 180
col username for a15
col owner for a15
col OBJECT_NAME for a30
col SPID for a10
--查询某个会话的锁
select /*+rule*/SESSION_ID,OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODE from gv$locked_object where session_id=&sid;
--查询TMTX锁
select /*+rule*/* from v$lock where ctime >100 and type in ('TX','TM') order by 3,9;
--查询数据库中的锁
select /*+rule*/s.sid,p.spid,l.type,round(max(l.ctime)/60,0) lock_min,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name from v$session s, v$process p,v$lock l,v$locked_object o,dba_objects b where  o.SESSION_ID=s.sid and s.sid=l.sid and o.OBJECT_ID=b.OBJECT_ID and s.paddr = p.addr and l.ctime >100 and l.type in ('TX','TM','FB') group by s.sid,p.spid,l.type,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name order by 9,1,3,4;
--查看会话突增情况:
set lin 200 pages 200
select to_char(LOGON_TIME,'yyyymmdd hh24:mi') LOGON,inst_id,username,count(*) from gv$session where LOGON_TIME>sysdate-0.1 group by to_char(LOGON_TIME,'yyyymmdd hh24:mi'),inst_id,username order by to_char(LOGON_TIME,'yyyymmdd hh24:mi') desc;
set lin 200 pages 200
select inst_id,username,machine,sql_id,count(*) from gv$session where LOGON_TIME>sysdate-0.1 group by to_char(LOGON_TIME,'yyyymmdd hh24:mi'),inst_id,username order by to_char(LOGON_TIME,'yyyymmdd hh24:mi') desc;
set lin 200 pages 200
select inst_id,username,machine,sql_id,count(*) from gv$session  group by inst_id,username,machine,sql_id order by count(*) desc;
select inst_id,username,machine,prev_sql_id,count(*) from gv$session  group by inst_id,username,machine,prev_sql_id order by count(*) desc;
--节点会话分布:
 select inst_id,count(*) from gv$session group by inst_id;
 --查看实例,会话分布
col machine for a20
col program for a40
set lin 200 pages 200
select username,machine,program,count(*) from v$session group by username,machine,program order by 4;
select username,machine,program,count(*) from v$session where program like 'oracle@gdytj-db1 (P0%' group by username,machine,program order by 4;

###七:日志挖掘LOGMNR篇###

LOGMNR
1.需要添加补充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
2.明确需要挖掘的归档
SELECT NAME FROM V$ARCHIVED_LOG
WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
+DATA/orcl/archivelog/2018_09_20/thread_1_seq_6.313.987329093
3.加入挖掘列表
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '+DATA/orcl/archivelog/2018_09_20/thread_1_seq_7.314.987330717', -
OPTIONS => DBMS_LOGMNR.NEW);
4.开始挖掘
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
 OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
5.查询挖掘结果
desc V$LOGMNR_CONTENTS
select count(*) from 
SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' ||  XIDSQN) AS XID, 
SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('TEST');
6.关闭挖掘
EXECUTE DBMS_LOGMNR.END_LOGMNR();
7.其他挖掘参数命令
只显示提交的数据
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY); 
格式化显示语句,使语句更可读
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
    DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
    DBMS_LOGMNR.PRINT_PRETTY_SQL);
8.构建dic字典
输出到文本
alter system set utl_file_dir='/home/oracle/scripts' scope=spfile;
exec dbms_logmnr_d.build(dictionary_filename=>'orcl.dic',dictionary_location=>'/home/oracle/scripts',options=>dbms_logmnr_d.STORE_IN_FLAT_FILE);
输出到归档
exec DBMS_LOGMNR_D.BUILD(dictionary_filename=>NULL,dictionary_location=>NULL,options=>dbms_logmnr_d.STORE_IN_REDO_LOGS);
查询包含LOGMNR字典的日志文件:
v$archived_log的dictionary_begin和dictionary_end列

###无数据字典logminer
配置异机恢复库
启动测试数据库1用于恢复归档,恢复控制文件,将数据库启动到mount
rman 
restore controlfile from '<controlfile file dir>';
alter database mount;
根据时间段确定需要挖的归档日志
select RECID,THREAD#,SEQUENCE#,
to_char(first_time,'yyyymmdd hh24:mi:ss'),
to_char(NEXT_TIME,'yyyymmdd hh24:mi:ss'),
to_char(COMPLETION_TIME,'yyyymmdd hh24:mi:ss')
from v$archived_log 
where first_time between to_date('20181228','yyyymmdd') and to_date('20181229','yyyymmdd') 
and COMPLETION_TIME between to_date('20181228','yyyymmdd') and to_date('20181229','yyyymmdd') 
order by 3,4;
根据thread和seq# 来restore归档日志
run{
allocate channel ch01 type 'sbt_tape';
send 'xxxxxx';
set archivelog destination to '/oracle11/lylog/20190115_logminer';
restore archivelog sequence 4252 thread 2;
restore archivelog sequence 4333 thread 1;
}
启动测试数据库2
用于挖掘日志
检查数据库补充日志
select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;
开启数据库补充日志
alter database add supplemental log data;
添加归档文件
exec dbms_logmnr.add_logfile('/oracle11/lylog/20190115_logminer/1_4333_981803066.dbf',dbms_logmnr.new);
exec dbms_logmnr.add_logfile('/oracle11/lylog/20190115_logminer/2_4252_981803066.dbf',dbms_logmnr.addfile);
检查归档日志是否添加
set line 200
col filename for a80
select log_id,filename,status from v$logmnr_logs;
挖掘日志
exec DBMS_LOGMNR.START_LOGMNR(DictFileName=>'',Options=>0);
查询挖掘结果
set line 150 pages 20000
col username for a20
col sql_redo for a80
spool /oracle11/lylog/20190115_logminer/logminer_1.log
select username,to_char(start_timestamp,'yyyymmdd hh24:mi:ss'),SQL_REDO from v$logmnr_contents where upper(sql_redo) like '%TRUNCATE %';
select username,to_char(start_timestamp,'yyyymmdd hh24:mi:ss'),SQL_REDO from v$logmnr_contents where upper(sql_redo) like '%GRANT %';
本次logminer需求是要挖掘到日志中的grant和truncate语句,所以条件如上述,实际使用需根据实际需要修改。
附注:
根据下方MOS
How to Setup LogMiner (文档 ID 111886.1)
LogMiner Dictionary Build Procedure Fails With ORA-01308 in 12.2 (文档 ID 2277747.1) 
12.2数据库推荐使用directory替换原来的utl_file_dir参数。
create directory logminer as '/oracle/soft/arch';
execute dbms_logmnr_d.build(dictionary_filename =>'dict.ora',dictionary_location =>'LOGMINER');

--11g
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/u01/app/oracle/archive/1_5673_863636484.dbf',Options=>dbms_logmnr.new);
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
SQL> create table test1 as select * from v$logmnr_contents;
SQL> exec dbms_logmnr.end_logmnr;
---查看归档大小及目录名
select block_size * blocks / 1024 / 1024 "size(M)",
name,
first_time,
next_time,
creator
from v$archived_log a
order by first_time desc;
---查看归档中执行次数多的SQL
select count(*),substr(sql_redo,1,100) from test.test1 group by substr(sql_redo,1,100) order by 1 desc;
--10g
SQL> exec dbms_logmnr.add_logfile('/opt/oracle/oradata/mmstest/redo02.log',dbms_logmnr.new);
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/ogg1/redo02.log',dbms_logmnr.new);
/
要生成数据字典文件,首先要修改一个utl_file_dir参数,修改为*或者想存放数据字典文件位置的目录:
SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string
SQL> alter system set utl_file_dir='*' scope=spfile;
SQL> shutdown immediate
SQL> startup
---
alter system set utl_file_dir='*' scope=spfile ;
3、生成数据字典文件
SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      *
生成数据字典文件dict20150822.chen到/home/oracle/chen目录下
SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dict20150822.chen',dictionary_location => '/home/oracle/chen');
PL/SQL procedure successfully completed.
4、可以先设置使用的表空间
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('test')  
PL/SQL procedure successfully completed.
5、填加要分析的日志文件
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/orabak/testarch/1_89802_640266118.dbf',Options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
6、可以继续填加,用dbms_logmnr.removefile可以删除
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/orabak/testarch/1_89807_640266118.dbf',Options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
7、开始分析日志
SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/chen/dict20150822.chen');
PL/SQL procedure successfully completed.
提取特定时间的日志:
dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/dict20150822.chen',
starttime=>to_date('2009-06-24 09:30:00','YYYY-MM-DD HH24:MI:SS'),
endtime=>to_date('2009-06-24 12:00:59','YYYY-MM-DD HH24:MI:SS'))
/*
使用在线数据字典(数据库版本低于10g或者logmner库和源库是同一个库时可以使用在线数据字典):
SQL> EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
*/
8、查看结果
select timestamp,username,session#,sql_redo,operation from v$logmnr_contents;
9、结束分析,释放PGA内存资源
SQL> exec dbms_logmnr.end_logmnr;
注意,v$logmnr_contents内容保存了日志的内容,只在当前会话有效,如果想长期保存分析,可以在当前会话用create table tablename as select * from v$logmnr_contents语句来持久保存。
/
select name,first_time,next_time from v$archived_log where deleted='NO' and to_char(first_time,'yyyymmdd')= '20170518';
select name,first_time,next_time from v$archived_log where deleted='NO' and to_char(first_time,'yyyymmdd')= '20170518';
as of timestamp (systimestamp - interval '12' day);
(systimestamp - interval '2' minute)
(systimestamp - interval '120' second);
(systimestamp - interval '12' hour);
---离线分析---
begin
  sys.dbms_logmnr.add_logfile(LogFileName => '/picclife1/arch/log1_233013_789829078.arc',
                              Options     => sys.dbms_logmnr.new);
  sys.dbms_logmnr.add_logfile(LogFileName => '/picclife1/arch/log1_233014_789829078.arc',
                              Options     => sys.dbms_logmnr.addfile);
end;
begin
  sys.dbms_logmnr.start_logmnr(options=>SYS.DBMS_LOGMNR.SKIP_CORRUPTION, startscn=>15805310267810);
end;
  select scn,xid, operation,sql_redo, data_obj#,SEG_NAME,SEG_OWNER,data_objv#,status, row_id, rs_id,ssn, csf,rollback,
     rbasqn,RBABLK,rbabyte,pxid,OPERATION_code, thread#,table_name, seg_type,info from v$logmnr_contents WHERE SEG_NAME='OBJ# 243664'
  
-------------- 
create table test0627 as select level as id from dual connect by level<=1000;
select * from test0627;
select * from v$log; 
select * from v$logfile; 
alter system switch logfile;
alter system checkpoint;
select * from v$log; ---CURRENT  group 2
select * from v$logfile; ---D:\APP\ADMINISTRATOR\ORADATA\EASTEST\REDO02.LOG
truncate table test0627; ---2018-06-27 11:39
---添加分析的日志文件
begin
   DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'D:\APP\ADMINISTRATOR\ORADATA\EASTEST\REDO02.LOG',Options=>dbms_logmnr.new);
   end;
---开始分析日志
begin
  dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
end;
create table test0627_log as select * from v$logmnr_contents;
begin
  dbms_logmnr.end_logmnr;
end;

select * from test0627_log where lower(sql_redo) like '%test0627%';

--------------------

###闪回查询

SQL> select * from t1 as of timestamp (systimestamp - interval '120' second); ---查询t1表120秒之前的数据
SQL> select * from t1 as of timestamp (systimestamp - interval '2' minute);   ---查询t1表2分钟之前的数据
SQL> select * from dept as of timestamp (systimestamp - interval '12' hour);  ---查询dept表12小时之前的数据
SQL> select * from dept as of timestamp (systimestamp - interval '12' day);   ---查询dept表12天之前的数据
SQL> select * from cardacct as of timestamp sysdate-2;                        ---查询cardacct表2天之前的数据
SQL> select timestamp_to_scn(to_timestamp('2014-08-24 05:15:22','yyyy-mm-dd hh24:mi:ss')) scn from dual;       --时间和scn之间的转换
select * from dba_users where username like 'K2_%' order by created desc;  ---16:56
select * from dba_users as of timestamp (systimestamp - interval '120' second) where username like 'K2_%'  order by created desc;

###recyclebin

SQL> show parameter recyclebin
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
---查看当前用户的回收站
select * from recyclebin;
---recyclebin是user_recyclebin的同义词
select * from user_recyclebin;
---查看整个数据库的回收站
select * from dba_recyclebin;
---闪回表test001
flashback table test001 to before drop;
---闪回表test001并重命名为test003
flashback table test001 to before drop rename to test003;
---清除具体的对像
purge table test002; 
---清除指定表空间
purge tablespace chen;  
---删除表空间指定用户下的所有对像
purge tablespace ORAPEL user orabpel;
---清空整个回收站
purge recyclebin;

###八:备份与恢复###

数据泵目录的创建与授权
###expdp/impdp
SQL> create directory chen_dir as '/home/oracle/chen';
SQL> grant read,write on directory chen_dir to scott;
1.数据泵按表导出导入
expdp chen/chen directory=chen_dir tables=dept dumpfile=deptcc.dmp
impdp chen/chen directory=chen_dir tables=dept dumpfile=deptcc.dmp
impdp+network link 跳过expdp直接导入目标库
impdp username/passwd@dbsname schema=userA remap_schema=userA:userB remap_tablespace=tbsA:tbsB network_link=dblink_to_userA_on_userB
优点:只是不再将数据导出后导入,而是直接将数据从源库导入到目的库。
expdp backup/backup directory=zs_dir tables=SM_USER dumpfile=zsbackup.dmp network_link='ncerp' 
2.按方案(用户)导出
expdp chen/chen directory=chen_dir schemas=chen dumpfile=chen.dmp
impdp chen/chen directory=chen_dir dumpfile=full.dmp schemas=chen remap_schema=chen:chen008
su - oralce -c 'nc633/1 directory=expdpdir dumpfile=nc63320180305.dmp log=nc63320180305.log'
expdp nc633/1 directory=expdpdir dumpfile=nc63320180305.dmp logfile=nc63320180305.log schemas=nc633
---排除表
expdp nc633/nc633 directory=backup dumpfile=nc633_expdp_20170905.dmp logfile=nc633_expdp_20170905.log schemas=nc633 
exclude=TABLE:\"IN\(\'INVP_PLAN_B\'\)\"
---version
impdp EXPDPTEST/EXPDPTEST directory=dump_dir1 dumpfile=c.dmp REMAP_SCHEMA=s:EXPDPTEST REMAP_TABLESPACE=USERS:C
impdp 用户名/密码 directory=dump_dir dumpfile=xx.dmp REMAP_SCHEMA=用户1:用户2  exclude=STATISTICS
---按表导入(不同用户间导入,需要在表名前指定用户名)
impdp chentest/a directory=dir_expdp dumpfile=K2_EXPDP_20181228223000.DMP logfile=xxx.LOG REMAP_SCHEMA=k2:chentest tables=k2.T_JOB_INST
---按用户导入
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
impdp chentest/a directory=dir_expdp dumpfile=K2_EXPDP_20181228223000.DMP logfile=xxx.LOG remap_schema=k2:k2_201812282230 ---exclude=STATISTICS
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
impdp test_20190115/a directory=expdp_dir dumpfile=SHUIWU_EXPDP_20190114223000.DMP logfile=SHUIWU_IMPDP_20190114223000.LOG remap_schema=shuiwu:test_20190115 remap_tablespace=EAS_D_SHUIWU_STANDARD:test_tbs remap_tablespace=EAS_D_SHUIWU_TEMP2:temp_tbs remap_tablespace=EAS_D_SHUIWU_INDEX:index_tbs
expdp k2_20190305/a directory=DIR_EXPDP dumpfile=T_BAS_ATTACHMENT_2019_0320.dmp logfile=T_BAS_ATTACHMENT_2019_0320.log  tables=T_BAS_ATTCHMENT query=T_BAS_ATTACHMENT:\"where to_char(FCreateTime,'yyyymm')='201903'\" exclude=STATISTICS 
expdp k2_20190305/a directory=DIR_EXPDP dumpfile=T_BAS_ATTACHMENT_2019_0320uu.dmp logfile=T_BAS_ATTACHMENT_2019_0320uu.log  tables=T_BAS_ATTACHMENT query=T_BAS_ATTACHMENT:\"where to_char(FCreateTime,'yyyymmdd')='20190101'\" exclude=STATISTICS
impdp chentest/a dumpfile=T_BAS_ATTACHMENT_2019_0320.dmp directory=DIR_EXPDP logfile=T_BAS_ATTACHMENT_impdp_2019_0320.log remap_schema=K2_20190305:chentest
impdp chentest/a dumpfile=T_BAS_ATTACHMENT_2019_0320uu.dmp directory=DIR_EXPDP logfile=T_BAS_ATTACHMENT_impdp_2019_0320uu.log remap_schema=K2_20190305:chentest table_exists_action=append
impdp K2_20190928/a directory=dir_expdp dumpfile=T_BAS_ATTACHMENT_2019_20191205220000.DMP logfile=T_BAS_ATTACHMENT_2019_20191205220000XXX.LOG  remap_schema=K2:K2_20190928 REMAP_TABLE=KT_BAS_ATTACHMENT:T_BAS_ATTACHMENT  table_exists_action=append
select * from dba_scheduler_job_log;
select * from dba_datapump_jobs;
select * from dba_datapump_sessions;
select * from dba_tables where table_name like 'SYS_EXPORT_SCHEMA%';
select * from k2.SYS_EXPORT_SCHEMA_01;
select object_type_path,object_name from k2.SYS_EXPORT_SCHEMA_01;
停止:
dba_datapump_jobs
expdp \"sys/oracle as sysdba\" attach=SYS_EXPORT_SCHEMA_02
stop_job--或者stop_job=immediate
yes
SELECT * from dba_datapump_sessions;
impdp test_20190115/a attach=SYS_IMPORT_FULL_01
stop_job--或者stop_immediate
impdp system/passwd attatch = sys.SYS_XXX_XX_68 进入 Import > 提示符,可以使用 kill_job 或 stop_job结束或停止Job , stop的job可以继续,kill的不行
SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
  FROM dba_datapump_jobs
 WHERE job_name NOT LIKE 'BIN$%'
 ORDER BY 1, 2;
 
With the Partitioning, OLAP and Data Mining options
ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted
解决方法:
1.在备份时指定一个在dba_datapump_jobs中没有不存在的一个job_name
2.清理dba_datapump_jobs表
1)查询可以清理的master table并生成SQL
select 'drop table ' || owner_name || '.' || job_name || ';'
  from dba_datapump_jobs
 where state = 'NOT RUNNING';
2)清理后再次dba_datapump_jobs确认清理成功
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
ORDER BY 1,2;
若不成功,按下面的方法再次清理
sqlplus oak/oak
exec dbms_datapump.stop_job(dbms_datapump_attach(‘SYS_EXPORT_TABLE_01’,’OAK’));

EXP

1)表模式
exp scott/tiger file=a.dmp log=a.log tables=emp compress=n
exp scott/tiger file=a.dmp log=a.log tables=(emp,dept) compress=n
exp scott/tiger file=a.dmp log=a.log tables=emp query="'where deptno=20 and ename!="SCOTT"'" compress=n
exp scott/tiger parfile=parameter.txt compress=n
vim parameter.txt
tables=emp
query='where deptno=20 and ename!="SCOTT"' 
2)用户模式
exp scott/tiger file=a.dmp log=a.log owner=scott compress=n
exp scott/tiger file a.dmp log=a.log owner=(scott,chen) compress=n
3)整库模式
exp scott/tiger file=full.dmp log=full.log full=y 
只是逻辑上的全库,只导出了和用户数据相关的对象。
自动删除
forfiles  /P D:\BACKUP\BACKUP_DB /m * /s /D -2 /c "cmd /c del @file"
expdp  username/password@234DB  directory=dump_dir  dumpfile=mydb_%U.dmp  logfile=mydb.log  filesize=200m  full=y
impdp  username/password  directory=dump_dir  dumpfile=mydb_%U.dmp  full=y;
$ exp user/pwd file=1.dmp,2.dmp,3.dmp,… filesize=1000m    log=xxx.log full=y
以多个固定大小文件方式导入
 $  imp  user/pwd  file=1.dmp,2.dmp,3.dmp,…  filesize=1000m  tables=xxx  fromuser=dbuser
touser=dbuser2
---只导入索引
impdp dvpt/dvpt DIRECTORY=imp_dir DUMPFILE=pdnc_bak_navy170915.dmp logfile=import_index.log REMAP_SCHEMA=pdnc:dvpt include='INDEX';

相关参数:

alter session set workarea_size_policy=MANUAL;
alter session set db_file_multiblock_read_count=512;
alter session set events '10351 trace name context forever, level 128';
alter session set sort_area_size=734003200;
alter session set "_sort_multiblock_read_count"=128;
alter session enable parallel ddl;

###临时表清理

通过存储过程加job,定时删除指定临时表。
1.先处理 TMPTI 开头的表。
DECLARE
 CURSOR a IS SELECT table_name FROM User_Tables s  WHERE s.temporary='Y' AND s.table_name LIKE  'TMPTI%';
BEGIN
FOR i IN a LOOP
    EXECUTE IMMEDIATE 'drop table '||i.table_name;
    --dbms_output.put_line(i.table_name);
 END LOOP;
END;
2,删除 TMPTA 开头的表
DECLARE
 CURSOR a IS SELECT table_name FROM User_Tables s  WHERE s.temporary='Y' AND s.table_name LIKE  'TMPTA%' and s.table_name <> 'TMPTABSUB';
BEGIN
 FOR i IN a LOOP
    EXECUTE IMMEDIATE 'drop table '||i.table_name;
 END LOOP;
END;
3,删除 FA_开头的表,不包含FA_DEPTSCALE
DECLARE
 CURSOR a IS SELECT table_name FROM User_Tables s  WHERE s.temporary='Y' AND s.table_name LIKE  'FA_ %' and s.table_name <>  'FA_DEPTSCALE';
BEGIN
 FOR i IN a LOOP
    EXECUTE IMMEDIATE 'drop table '||i.table_name;
 END LOOP;
END;
---创建删除临时表的存储过程
CREATE OR REPLACE PROCEDURE DROP_TEMPTAB AS
  CURSOR a IS
    select table_name
      from user_tables c, user_objects d
     where c.table_name = d.object_name
       and c.temporary = 'Y'
       and c.table_name like 'TEMPTABLE0000%'
       and d.object_type = 'TABLE'
       and d.temporary = 'Y'
       and d.CREATED < sysdate - 5;
BEGIN
  FOR i IN a LOOP
    EXECUTE IMMEDIATE 'drop table ' || i.table_name;
  END LOOP;
END;
删除临时表的任务是nc.bs.smart.db.TempTableSchdTask
自由报表删除临时表任务是注册在调度任务下面的,需要确认一下调度任务是否执行成功、调度配置是否有问题。
---20160826---
 declare 
          cursor tname is 
             select table_name from user_tables where table_name like 'TEMP%';
      begin
          for i in tname   
          loop
          execute immediate 'drop table '||i.table_name;
          end loop;
        end;
---20160826--- 
###删除临时表
1.进SQL命令行界面,运行
grant create any table to ychr;
grant select any table to ychr;
grant update any table to ychr;
grant delete any table to ychr;
grant drop any table to ychr;
2.进SQL界面,运行
create or replace procedure dropTempTable as
  mysql varchar2(4000);
begin
  for tablename in (select table_name
                      from user_tables t1, user_objects t2
                     where t1.temporary = 'Y'
                       and t1.table_name like 'TEMPTABLE%'
                       and t1.table_name = t2.object_name
                       and t2.object_type = 'TABLE'
                     and t2.created < sysdate - 3) loop
    begin
      mysql := 'drop table ' || tablename.table_name;
      execute immediate mysql;
    exception
      when others then
        NULL;
    end;
  end loop;
end;
3.进SQL命令行界面
VARIABLE JOBNO NUMBER; 
VARIABLE INSTNO NUMBER; 
BEGIN 
    SELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE; 
    DBMS_JOB.SUBMIT(:JOBNO,'dropTempTable;',TRUNC(SYSDATE)+1+4/24,'TRUNC(SYSDATE)+3+4/24',TRUE,:INSTNO); 
    COMMIT; 
    END; 
---临时表 
SQL> select * from (select to_char(created,'yyyymmdd'),count(*)
  2  from user_tables a,user_objects b
  3  where a.table_name=b.object_name
  4  and a.temporary='Y'
  5  and a.table_name like'TEM_%'
  6  group by to_char(created,'yyyymmdd')
  7  order by 1 desc
  8  )
  9  where rownum<=1000;
创建存储过程,删除5天之前的TEM_和TMPTABSUBJ开头的临时表;
---创建删除临时表的存储过程
CREATE OR REPLACE PROCEDURE DROP_TEMPTAB AS
  CURSOR a IS
    select table_name
      from user_tables c, user_objects d
     where c.table_name = d.object_name
       and c.temporary = 'Y'
       and (c.table_name like 'TEM_%' or c.table_name like 'TMPTABSUBJ%')
       and d.object_type = 'TABLE'
       and d.temporary = 'Y'
       and d.CREATED < sysdate - 5;
BEGIN
  FOR i IN a LOOP
    EXECUTE IMMEDIATE 'drop table ' || i.table_name;
  END LOOP;
END;
---查询临时表基本信息
select count(*) from user_tables;
select count(*) from user_tables where temporary='Y';  
select count(*),substr(table_name,1,5) from user_tables where temporary='Y' group by substr(table_name,1,5) order by 1 desc;
select * from user_tables where temporary='Y' and lower(table_name) like 'temptable_oa%'; 
select count(*) from user_tables where temporary='Y' and lower(table_name) like 'temptable_oa%'; 
select to_char(created, 'yyyymmdd'), count(*)
  from user_tables a, user_objects b
 where a.table_name = b.object_name
   and a.temporary = 'Y' and lower(a.table_name) like 'temptable_oa%'
 group by to_char(created, 'yyyymmdd')
 order by 1 desc;
首次删除临时表之前,最好做一次数据库全备;
创建存储过程,定期删除3天之前的TEMPTABLE000000开头的临时表;
---创建删除临时表的存储过程
CREATE OR REPLACE PROCEDURE DROP_TEMPTAB AS
  CURSOR a IS
    select table_name
      from user_tables c, user_objects d
     where c.table_name = d.object_name
       and c.temporary = 'Y'
       and c.table_name like 'TEMPTABLE_OA%'
       and d.object_type = 'TABLE'
       and d.temporary = 'Y'
       and d.CREATED < sysdate - 3;
BEGIN
  FOR i IN a LOOP
    EXECUTE IMMEDIATE 'drop table ' || i.table_name;
  END LOOP;
END;
---两种类型临时表
CREATE OR REPLACE PROCEDURE DROP_TEMPTAB AS
  CURSOR a IS
    select table_name
      from user_tables c, user_objects d
     where c.table_name = d.object_name
       and c.temporary = 'Y'
       and (c.table_name like 'TEM_%' or c.table_name like 'TMPTABSUBJ%')
       and d.object_type = 'TABLE'
       and d.temporary = 'Y'
       and d.CREATED < sysdate - 5;
BEGIN
  FOR i IN a LOOP
    EXECUTE IMMEDIATE 'drop table ' || i.table_name;
  END LOOP;
END;
------多种类型临时表
CREATE OR REPLACE PROCEDURE DROP_TEMPTAB_ALL AS
  CURSOR a IS
    select table_name
      from user_tables c, user_objects d
     where c.table_name = d.object_name
       and c.temporary = 'Y'
       and substr(table_name, 1, 9) in
           ('T_YDEPTID', 'T_ME_DEPT', 'T_BSCLASS')
       and d.object_type = 'TABLE'
       and d.temporary = 'Y'
       and d.CREATED < sysdate - 3;
BEGIN
  FOR i IN a LOOP
    EXECUTE IMMEDIATE 'drop table ' || i.table_name;
  END LOOP;
END;
---
create or replace procedure dropTempTable as
  mysql varchar2(4000);
begin
  for tablename in (select table_name
                      from user_tables t1, user_objects t2
                     where t1.temporary = 'Y'
                       and (t1.table_name like 'TEMPTABLE%' or t1.table_name like 'T_ILGROUP%')
                       and t1.table_name = t2.object_name
                       and t2.object_type = 'TABLE'
                     and t2.created < sysdate - 1.5) loop
    begin
      mysql := 'drop table ' || tablename.table_name;
      execute immediate mysql;
    exception
      when others then
        NULL;
    end;
  end loop;
end;
添加JOB,定期执行该存储过程,自动删除临时表, 每天3点执行JOB,每2天执行一次;
SQL> VARIABLE JOBNO NUMBER;
SQL> VARIABLE INSTNO NUMBER;
SQL> 
SQL> BEGIN
  2         SELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE;
  3         DBMS_JOB.SUBMIT(:JOBNO,
  4                         'DROP_TEMPTAB; ',
  5                         TRUNC(SYSDATE) + 1 + 3 / 24,
  6                         'TRUNC(SYSDATE)+2+3/24',
  7                         TRUE,
  8                         :INSTNO);
  9         COMMIT;
 10       END;
 11  /
 
PL/SQL procedure successfully completed
  CREATE GLOBAL TEMPORARY TABLE "CHENJCH_ZGMYHK_1103"."T_YDEPTID001610243" 
   ( "PK" VARCHAR2(60)
   ) ON COMMIT DELETE ROWS 
==============
temptable_  开头的临时表
在下面的代码中产生的
nc.bs.oa.oaco.basecomp.helper.TempTableUtils.createTable(String columns, String pkCol)

###RMAN备份

---最后备份控制文件,因为备份的元数据记录在控制文件中;

---GoldenGate企业级运维实践(65) 

###### rman.sh begin 
export NLS_DATE_FORMAT='yyyymmdd hh24:mi:ss'
export ORACLE_SID=orcl 
rman target / log=/goldengate/rman.log <<EOF 
crosscheck archivelog all;
run{
allocate channel ch1 type disk maxpiecesize 100M;
allocate channel ch2 type disk maxpiecesize 100M;
backup database tag 'full_epmln' format '/nas/backup/%d_full_%T_%U.bak';
sql 'alter system archive log current';
backup archivelog all tag 'arch_epmln' format '/nas/backup/%d_arch_%T_%U.bak';
backup current controlfile tag 'ctl_epmln' format '/nas/backup/%d_ctl_%T_%U.bak';
release channel ch1;
release channel ch2;
}
EOF
exit 
###### rman.sh end

---在后台执行

nohup sh rman.sh &

---如果rman脚本有语法问题可以使用rman checksyntax检查语法错误。

==========

RMAN恢复

==========

---备份 

RUN
{
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
allocate channel c1 type disk;
allocate channel c2 type disk;
backup full database tag='full_orcl' format 'E:\backup\rman\rman_full_%d_%T_%U.bak';
sql 'alter system archive log current';
backup archivelog all tag 'arch_orcl' format 'E:\backup\rman\rman_arc_%d_%T_%U.bak' delete all input;
backup current controlfile tag 'ctl_orcl' format 'E:\backup\rman\rman_ctl_%d_%T_%U.bak';
release channel c1;
release channel c2;
report obsolete;
crosscheck backup;
crosscheck copy;
delete noprompt expired backup;
delete noprompt expired copy;
delete noprompt obsolete;
delete noprompt expired archivelog all; 
delete noprompt archivelog until time 'sysdate-7';
}
exit;
conn chenjch/***
select * from user_tables;
create table test20180702 as select level as id from dual connect by level<=1000;
select * from test20180702;
alter system switch logfile;
alter system checkpoint;

---恢复 

---恢复数据库

startup nomount 

---恢复控制文件

run {
allocate channel d1 deviec type disk;
restore controlfile from 'controlfile_backuppiece_name';
release channel d1;
}
alter database mount;

---还原数据库

show all 
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
run {
allocate channel d1 device type disk;
restore database;
release channel d1; 
}

---还原归档

run {
allocate channel d1 device type disk;
restore archivelog from logseq 34503;
release channel d1;
}

---恢复数据库

run {
allocate channel d1 device type disk;
recover database using backup controlfile until cancel;
release channel d1;
}

---查询并记录数据文件SCN

select checkpoint_change#,file# from v$datafile_header;
###RMAN查询状态
SELECT start_time, end_time, operation, status
  FROM V$RMAN_STATUS
 order by start_time desc;
 
--查看rman备份信息
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
 col tag for a20
set lin 200 pages 200
col handle for a30
col SIZE_BYTES_DISPLAY for a10
 select BS.RECID,BS.START_TIME,BS.PIECES,BS.INCREMENTAL_LEVEL,BS.BACKUP_TYPE,BP.PIECE#,BP.HANDLE,BP.TAG,BP.STATUS,ROUND(BP.bytes/1024/1024/1024,2) gb
  from v$backup_set bs,v$backup_piece bp where bs.recid=bp.recid 
 --and tag like 'TAG20200406%'  
 and bp.HANDLE not like 'c-2051169266-%' and BS.BACKUP_TYPE!='L' and BS.START_TIME>to_date('20200401 08:00:00','yyyymmdd hh24:mi:ss');
 select 'change backupset '||BS.RECID||' unavailable;'
  from v$backup_set bs,v$backup_piece bp where bs.recid=bp.recid 
 and tag like 'TAG20200407%'  
 and bp.HANDLE not like 'c-2051169266-%' and BS.BACKUP_TYPE!='L' and BS.START_TIME>to_date('20200405 08:00:00','yyyymmdd hh24:mi:ss');
 1.查询过去和现在RMAN备份工作的详细情况
COL INPUT_TYPE FORMAT a20
COL STATUS FORMAT a40
COL hours    FORMAT 999.999
SELECT SESSION_KEY, INPUT_TYPE, STATUS,
       TO_CHAR(START_TIME,'yyyy-mm-dd hh24:mi') start_time,
       TO_CHAR(END_TIME,'yyyy-mm-dd hh24:mi')   end_time,
       ELAPSED_SECONDS/3600                   hours
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
2.RMAN备份的速度,in_sec表示每秒的input速度,output表示每秒的output速度
COL in_sec FORMAT a10
COL out_sec FORMAT a10
COL TIME_TAKEN_DISPLAY FORMAT 99999999999
SELECT SESSION_KEY, 
       OPTIMIZED, 
       COMPRESSION_RATIO, 
       INPUT_BYTES_PER_SEC_DISPLAY in_sec,
       OUTPUT_BYTES_PER_SEC_DISPLAY out_sec, 
       TIME_TAKEN_DISPLAY
FROM   V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
3.显示备份的尺寸。OUT_SIZE表示备份出的尺寸,IN_SIZE表示输入的数据总大小。
COL in_size  FORMAT a10
COL out_size FORMAT a10
SELECT SESSION_KEY, 
       INPUT_TYPE,
       COMPRESSION_RATIO, 
       INPUT_BYTES_DISPLAY in_size,
       OUTPUT_BYTES_DISPLAY out_size
FROM   V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
4.查看未完成的RMAN备份进度
set line 9999
col opname for a35
col start_time for a19
col TOTALWORK for 999999999999999999
SELECT SID,SERIAL#,opname, to_char(start_time,'yyyy-mm-dd HH24:MI:SS')  start_time, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%COMPLETE",ceil(ELAPSED_SECONDS/60) ELAPSED_MI
 FROM V$SESSION_LONGOPS where opname like 'RMAN%' AND SOFAR <> TOTALWORK and TOTALWORK<>0 order by start_time asc;
--recover进度
col type format a15
select START_TIME,TYPE,ITEM,TIMESTAMP from v$recovery_progress;

###九:坏块###

查看数据文件号及名称
select file_id,tablespace_name,file_name,bytes/1024/1024 from dba_data_files order by 1;
通过DBV检查文件是否有坏块
检查 nnc_index01.dbf 文件
dbv file='/oradata/nc/nnc_index01.dbf'
查看坏块所属段及类型
查看 file=6(459202) 对应段类型及名称
select tablespace_name, segment_type, owner, segment_name
  from dba_extents
 where file_id = 6
   and 459202  between block_id and block_id + blocks - 1;
   
SELECT * FROM dba_extents
WHERE file_id = &AFN
and &BL between block_id AND block_id + blocks - 1;
如果损失的是数据
exp备份的话,可以设置内部10231事件,该事件能够让Oracle在全表扫描时跳过损坏的数据块,也就是使exp跳过这些损坏的block
alter system set events='10231 trace name context forever,level 10';
--成功导出后记得要关闭10231内部事件
alter system set events='10231 trace name context off';
通过expdp 备份可以直接跳过坏块;
--rman恢复坏块
RMAN> backup validate datafile 5; 
SQL> select FILE#,BLOCK#,BLOCKS,CORRUPTION_CHANGE#,CORRUPTION_TYPE from v$database_block_corruption where file#=5;  
RMAN> blockrecover datafile 5 block  20869;  
RMAN> blockrecover datafile 5 block  20869,xxx,xxx;  ---可以指定多个块
87826, 文件 6, 块 15253 (2),
SELECT * FROM dba_extents
WHERE file_id = 6
and 15253 between block_id AND block_id + blocks - 1;
如果是表,可以使用10231事件忽略坏块,然后使用CTAS方式重建表最后rename table,别忘记rebuild index
alter session SET EVENTS '10231 trace name context forever,level 10';
create table tab_new as select * from tab;
rename tab to tab_bak;
rename tab_new to new;
select 'alter index '||index_name||' rebuild;' from user_ind_columns where table_name='T1';
alter session SET EVENTS '10231 trace name context off';
其他:
select segment_name,owner,tablespace_name from dba_extents where 388969 between BLOCK_ID and BLOCK_ID+BLOCKS-1 and FILE_ID=8; 
select tablespace_name, segment_type, owner, segment_name from dba_extents where file_id = 7 and 336465 between block_id and block_id + blocks - 1;
dbv file='D:\ORACLE\PRODUCT\10.2.0\ORADATA\CGJT\NNC_DATA01.DBF'
RMAN> backup validate datafile 7;
SQL> select * from v$database_block_corruption where file#=7;
RMAN> blockrecover datafile 6 block 459202,643867,1963727;
RMAN> blockrecover datafile 8 block 169072;
dbv file='/oradata/nc/nnc_index01.dbf'
查看file=6(459202)对应段类型及名称
select tablespace_name, segment_type, owner, segment_name
  from dba_extents
 where file_id = 6
   and 459202 between block_id and block_id + blocks - 1;
   恢复datafile=7的所有坏块
RMAN>backup validate datafile 7;
验证坏块位置
select * from v$database_block_corruption where file#=7;
进行恢复
RMAN>blockrecover datafile 7 block 336465;
  
SELECT tablespace_name, segment_type, owner, segment_name
  FROM dba_extents
 WHERE file_id = &fileid
   and &blockid between block_id AND block_id + blocks - 1;
(1)ALL ZERO:Block header on disk contained only zeros. The block may be valid ifit was never filled and if it is in an Oracle7 file. The buffer will bereformatted to the Oracle8 standard for an empty block.
(2)FRACTURED: Block header looks reasonable, but the front and back of the blockare different versions.
(3)CHECKSUM: optional check value shows that the block is not self-consistent.It is impossible to determine exactly why the check value fails, but itprobably fails because sectors in the middle of the block are from differentversions.
(4)CORRUPT: Block is wrongly identified or is not a data block (for example,the data block address is missing)
(5)LOGICAL: Specifies the range is for logically corrupt blocks.CORRUPTION_CHANGE# will have a nonzero value.

###十:表空间###

###查看表空间使用率
set pagesize 9999 
set linesize 132 
col tablespace_name for a35
select
a.tablespace_name,
a.Total_mb,
f.Free_mb,
round(a.total_MB-f.free_mb,2) Used_mb,
round((f.free_MB/a.total_MB)*100) "%_Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total_MB from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free_MB from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "%_Free"
/
---查看表空间使用情况
set line 200
col tablespace_name for a30
select tablespace_name,
       bytes / 1024 / 1024 / 1024 as b_byte,
       autoextensible,
       maxbytes / 1024 / 1024 / 1024 as m_byte
  from dba_data_files
 order by 1, 2 desc;
 
扩容百分比:
set pagesize 9999 
set pagesize 9999 
set linesize 132 
col tablespace_name for a30
select
a.tablespace_name,
a.Total_mb,
f.Free_mb,
' Add'||to_char((total_MB*(1-PCT/100)-Free_mb)/(PCT/100)/1024,'9990.9')||'GB to '||PCT||'%' "Add_Size(GB)"
from
(select (&pct) PCT from dual),
(select tablespace_name, sum(bytes/(1024*1024)) total_MB from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free_MB from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "Add_Size(GB)"
/
Enter value for pct: 50
查看temp临时表空间使用率
SET PAGESIZE 400
SET LINES 300
COL D.TABLESPACE_NAME FORMAT A15
COL D.TOT_GROOTTE_MB FORMAT A10
COL TS-PER FORMAT A15
SELECT d.tablespace_name "Name",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999')  "HWM (M)",
TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"
FROM sys.dba_tablespaces d,
    (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
    (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY';
---1 哪个sql语句占用大量temp空间
SELECT instance_number,
       ash.p1,
       ash.p2,
       ash.p3,
       ash.temp_space_allocated/1024/1024/1024 "USETEMP/G",
       ash.module,
       to_char(sample_time, 'hh24:mi:ss.ff') TIME,
       session_id sid,
       ash.sql_id,
       ash.program,
       en.event_name,
       ash.blocking_session
  FROM sys.WRH$_ACTIVE_SESSION_HISTORY ash, sys.wrh$_event_name en
 WHERE ash.event_id = en.event_id(+)
   AND sample_time >= to_timestamp('20210727 2300', 'yyyymmdd hh24mi')
   AND sample_time <= to_timestamp('20210728 0910', 'yyyymmdd hh24mi')
   and temp_space_allocated is not null
ORDER BY temp_space_allocated desc;
---2 哪些会话在等待临时表空间
SELECT instance_number,
       ash.p1,
       ash.p2,
       ash.p3,
       to_char(sample_time, 'hh24:mi:ss.ff') TIME,
       session_id sid,
       ash.sql_id,
       ash.program,
       en.event_name,
       ash.blocking_session
  FROM sys.WRH$_ACTIVE_SESSION_HISTORY ash, sys.wrh$_event_name en
 WHERE ash.event_id = en.event_id(+)
   AND sample_time >= to_timestamp('20210727 2300', 'yyyymmdd hh24mi')
   AND sample_time <= to_timestamp('20210728 0910', 'yyyymmdd hh24mi')
   and event_name like '%enq: SS - contention%'
 ORDER BY sample_time;
 
---3 该时间段内在做什么操作
SELECT instance_number,
       ash.p1,
       ash.p2,
       ash.p3,
       to_char(sample_time, 'hh24:mi:ss.ff') TIME,
       session_id sid,
       session_serial#,
       ash.sql_id,
       ash.program,
       en.event_name,
       ash.blocking_session
  FROM sys.WRH$_ACTIVE_SESSION_HISTORY ash, sys.wrh$_event_name en
 WHERE ash.event_id = en.event_id(+)
   AND sample_time >= to_timestamp('20210727 2300', 'yyyymmdd hh24mi')
   AND sample_time <= to_timestamp('20210728 0910', 'yyyymmdd hh24mi')
   and session_id = 第一步查询出的blocking_session
 ORDER BY sample_time;
 
###undo空间使用情况
set pagesize 999 linesize 300
col machine for a16
col program for a25
col status for a10
col sql_id for a16
col sql_text for a50 
select distinct s.machine,s.program,s.sid,round(t.used_ublk*8/1024,2) undo_MB,used_urec undo_records,s.status,l.sql_text
from v$transaction t,gv$session s,v$sqlstats l
where t.ses_addr=s.saddr
and s.sql_id=l.sql_id(+)order by undo_MB;
---查看undo恢复时间
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
select usn, state, undoblockstotal "Total", undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0,'unknown',sysdate+(((undoblockstotal-
undoblocksdone) / (undoblocksdone / cputime)) / 86400))
"Estimated time to complete"
from v$fast_start_transactions;
--查看undo分布
select distinct status,tablespace_name ,to_char(round(sum(bytes)/1024/1024)) sizeM,count(1) from dba_undo_extents group by tablespace_name,status;
---查看oracle隐含参数
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
  from x$ksppi a, x$ksppcv b
 where a.indx = b.indx
   and a.ksppinm like '_undo_autotune';
   
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
  from x$ksppi a, x$ksppcv b
 where a.indx = b.indx
   and a.ksppinm = 'event';
###resize
set line 300
col file_name for a50
col cmd for a90
set pagesize 1000
select a.file_id,
       a.file_name,
       a.filesize,
       b.freesize,
       (a.filesize - b.freesize) usedsize,
       c.hwmsize,
       c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
       a.filesize - c.hwmsize canshrinksize,
       'alter database datafile ' ||''''|| a.file_name ||''''|| ' resize ' || c.hwmsize || 'M;' cmd
  from (select file_id, file_name, round(bytes / 1024 / 1024) filesize
          from dba_data_files) a,
       (select file_id, round(sum(dfs.bytes) / 1024 / 1024) freesize
          from dba_free_space dfs
         group by file_id) b,
       (select file_id, round((max(block_id)+max(blocks)) * 8 / 1024) HWMsize
          from dba_extents
         group by file_id) c
 where a.file_id = b.file_id
   and a.file_id = c.file_id and file_name like '%ecds%'
 order by unsedsize_belowhwm desc;
select tablespace_name,file_id,(block_id+blocks)*(select value from v$parameter where name='db_block_size')/1024/1024 "Allo_MB"
from dba_extents
where (file_id,block_id) in
(select file_id,max(block_id) blkid from dba_extents group by file_id)
order by 1,2;

###十一:日志文件###

非归档模式,非当前日志损坏,数据库打开模式
startup mount;
alter database clear logfile group 2;
alter database open;
非归档模式、数据库关闭、不是正在使用的日志文件损坏。
startup mount; 
alter database clear logfile group 2;
alter database open;
归档模式,非当前日志损坏,数据库打开模式
注意不需要重启数据库,只需要把日志清除即可。
alter database clear unarchived logfile group 2;
归档模式,非当前日志损坏,数据库关闭模式。
startup mount;
alter database clear unarchived logfile group 2;
alter database open;
总结,对于不是当前使用的归档日志损坏,归档模式需要使用alter database clear unarchived 命令清空日志 组即可。
对于非归档模式需要使用alter system clear日志文件组即可。
归档模式,数据库open状态、当前正在使用的日志文件损坏
由于这个时候,虽然当前日志是正在被使用的,但是我们可以先进行切换日志之后,然后执行clear操作。
alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance RHYS (thread 1)
ORA-00312: online log 1 thread 1: '/opt/app/oracle/oradata/RHYS/redo01.log'
alter system switch logfile;
alter database clear unarchived logfile group 1;
shutdown immediate;
startup
归档模式,数据库open状态、当前正在使用的日志文件损坏,并且正常关闭数据库。
startup mount;
alter database clear unarchived logfile group 2;
alter database open;
归档模式,数据库open状态、当前正在使用的日志文件损坏,并且异常关闭数据库。
使用隐含参数。
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
---alter system reset "_allow_resetlogs_corruption" scope=spfile;
alter database open resetlogs;
重建实例然后使用expdp和impdp,将数据导出在导入数据库
总结:对于当前正在使用的日志的损坏,一般通过备份来修复,如果不行只能采用第二种设置隐含参数_allow_resetlogs_corruption来恢复。
---alter database add logfile group 1 'D:\APP_10.2.0.4\CHEN_DATAFILE\REDO01a.LOG' size 30M;
---alter database add logfile member 'D:\APP_10.2.0.4\CHEN_DATAFILE\REDO01a.LOG' to group 1;
1.alter session set events 'IMMEDIATE trace name ADJUST_SCN level x';
--需要数据库OPEN
2.通过10015事件
alter session set events '10015 trace name adjust_scn level x';
--在数据库无法打开,mount状态下。
注:level 1为增进SCN 10亿 (1 billion) (1024*1024*1024=1073741824)
#*._allow_resetlogs_corruption=TRUE
*._allow_error_simulation=TRUE
*._minimum_giga_scn=1047
_minimum_giga_scn=n的含义是把SCN往前推进到nG,但请注意,只有在SCN小于nG的时候才会用到这个隐含参数,反之则Oracle会置这个隐含参数于不顾。
比如_minimum_giga_scn设置为1
11.2.0.2.5 以后不再支持通过_minimum_giga_scn来调整scn
如果想调整scn 需要通过直接调整controlfile或者调整datafile header然后再重建控制文件
ORA-00600: internal error code, arguments: [2662]
---oradebug 
rman target / catalog rc_admin/RC_ADMIN@prod4
RMAN> list failure;
RMAN> advise failure;
RMAN> repair failure;
# database point-in-time recovery restore database until scn 1015002; 
recover database until scn 1015002; 
alter database open resetlogs;
--查看redo日志:
set lin 200 pages 299
col member for a60
col thread# for 999
col group# for 999
col members for 999
col status for a10
select l.thread#,l.group#,l.bytes/1024/1024 mb,l.sequence#,l.members,l.status,lf.member from v$Log l, v$Logfile lf where l.group#=lf.group# order by l.thread#,l.group#;

###十二:RAC###

[grid@node1 ~]$ srvctl config database
racdb
[grid@node1 ~]$ srvctl status database -d racdb
---[grid@node1 ~]$ srvctl start database -d racdb -o open
[grid@node1 ~]$ srvctl config database -d racdb -a
[grid@node1 ~]$ srvctl config scan
[grid@node1 ~]$ srvctl status scan_listener
[grid@node1 ~]$ srvctl status asm -a
[grid@node1 ~]$ crsctl query css votedisk
[grid@node1 ~]$ ocrcheck
crsctl status res -t
SQL> select inst_id,status from gv$instance order by inst_id;
SQL> select inst_id,username,count(*) from gv$session where type='USER' group by inst_id,username order by count(*) desc;
检查crs集群服务情况
crsctl check cluster -all
[grid@node1 ~]$ srvctl config database
racdb
[grid@node1 ~]$ srvctl status database -d racdb
---[grid@node1 ~]$ srvctl start database -d racdb -o open
[grid@node1 ~]$ srvctl config database -d racdb -a
[grid@node1 ~]$ srvctl config scan
[grid@node1 ~]$ srvctl status scan_listener
[grid@node1 ~]$ srvctl status asm -a
[grid@node1 ~]$ crsctl query css votedisk
[grid@node1 ~]$ ocrcheck
---[oracle@edbjr2p2 bin]$ ./srvctl start database -d EMREP -o open
---[oracle@edbjr2p2 bin]$ ./srvctl stop database -d EMREP -o immediate
检查集群状态: 
[grid@rac02 ~]$ crsctl check cluster 
位于磁盘组中的所有数据文件
select name from v$datafile
union
select member from v$logfile
union
select name from v$controlfile
union
select name from v$tempfile;
$ORA_CRS_HOME/bin/crs_stat –t
查看CRS状态
[oracle@node1 bin]$ ./crsctl check crs
查看CSS状态
[oracle@node1 bin]$ ./crsctl check css
oracle rac常用的命令
节点层:olsnodes
网络层: oifcfg
集群层:crsctl,ocrcheck,ocrdump,ocrconfig
应用层:srvctl,onsctl,crs_stat
[oracle@rac1 ~]$ srvctl status asm 
V$ASM_DISK
V$ASM_DISKGROUP
V$ASM_FILE
最后,我们对agent相关的trace文件进行简单的介绍。首先,agent的trace 文件位于路径GRID_HOME/log/<host>/agent下,以下是比较详细的信息。
GRID_HOME/log/<host>/agent /ohasd/orarootagent_root  <– ohasd产生的orarootagent日志
GRID_HOME/log/<host>/agent/ohasd/oraagent_grid  <– ohasd产生的oraagent日志
GRID_HOME/log/<host>/agent/ohasd/oracssdagent_root  <– ohasd产生的cssdagent日志
GRID_HOME/log/<host>/agent/ohasd/oracssdmonitor_root  <– ohasd产生的cssdmonitor日志
GRID_HOME/log/<host>/agent/crsd/oraagent_grid  <– crsd产生的oraagent日志,owner为grid
GRID_HOME/log/<host>/agent/crsd/oraagent_oracle  <– crsd产生的oraagent日志,owner为oracle
GRID_HOME/log/<host>/agent/crsd/orarootagent_root  <–crsd产生的orarootagent日志
另外,以下的文件对诊断agent相关的问题也很有帮助。
集群alert log(Grid_home/log/<hostname>/alert<hostname>.log)
Grid_home/log/<hostname>/ohasd/ohasd.log
Grid_home/log/<hostname>/crsd/crsd.log
--查看asm磁盘组空间
select name,total_mb,free_mb,round((total_mb-free_mb)/total_mb*100,2) "Used%" from v$asm_diskgroup;
--linux共享存储linux 7.X
emc查看存储盘:
[root@sy-wpl-db02 rules.d]# powermt display dev=all
华为emc:
[root@sy-wpl-db02 rules.d]# upadmin show vlun
--rhel 7配置华为存储
for i in  b c d e f g h i j k l m n o p q r s t u v w x y; 
do echo "KERNEL==\"sd*[!0-9]\", SUBSYSTEM==\"block\", PROGRAM==\"/usr/lib/udev/scsi_id -g -u -d /dev/%k\", RESULT==\"`/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/sd$i`\", SYMLINK+=\"asm-disk$i\", OWNER=\"grid\", GROUP=\"asmadmin\", MODE=\"0660\"" | grep -v 'RESULT==""';  
done >> /etc/udev/rules.d/99-oracleasm-disks.rules
--查看磁盘信息
set pages 200
col path for a35
col name for a20
col dgname for a25
col diskname for a25
col disknumber for 999
set lin 200
col FAILGROUP for a20
select dg.name dgname,ad.name diskname,ad.disk_number disknumber,ad.path,ad.header_status,ad.mode_status,ad.total_mb,ad.free_mb,ad.failgroup from 
v$asm_diskgroup dg,v$asm_disk ad where dg.GROUP_NUMBER(+)=ad.group_number  order by dg.name,ad.path;
--查看磁盘组信息
col COMPATIBILITY for a10
col DATABASE_COMPATIBILITY for a20
select name,state,type,total_mb/1024 GB,free_mb/1024 GB,COMPATIBILITY,DATABASE_COMPATIBILITY from v$asm_diskgroup;
select name,total_mb,free_mb,total_mb-free_mb used_mb,round((total_mb-free_mb)/total_mb*100,2) "Used%" from v$asm_diskgroup;
select name,total_mb,free_mb/1024 free_gb,total_mb-free_mb used_mb,round((total_mb-free_mb)/total_mb*100,2) "Used%" from v$asm_diskgroup;

###十三:12c###

###oracle12c命令
---查看容器ID,容器名,当前连接用户
SQL> show con_id con_name user  
或者
SQL> select sys_context('USERENV','CON_ID') as con_id,sys_context('USERENV','CON_NAME') as cur_container,sys_context('USERENV','SESSION_USER') as cur_user from dual;
SQL> show pdbs
SQL> select name, decode(cdb, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option" , open_mode, con_id from v$database;
SQL> select cdb from v$database;
SQL> select sys_context('userenv','con_name') "Container DB" from dual;
SQL> select con_id, dbid, guid, name , open_mode from v$pdbs;
SQL> select con_id,dbid,name,open_mode from v$pdbs;
SQL> alter pluggable database pdborcl open;
SQL> select con_id,dbid,name,open_mode from v$pdbs;
切换容器进入pdb数据库
SQL> alter session set container=pdborcl;
SQL> startup
SQL> alter pluggable database pdborcl close;
SQL> alter session set container=cdb$root;
SQL> select instance_name,status from v$instance;
---启动/关闭插件数据库
alert pluggable database all open;
alert pluggable database all close;
###ORA-28040
在数据库服务器上$ORACLE_HOME/network/admin目录创建sqlnet.ora文件,并写入: 
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8 
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8 
写入后不用重启监听,立即生效,已经可以连接了;
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
不用重启数据库或者监听,也不用重启应用。
区别如下:
SQLNET.ALLOWED_LOGON_VERSION_SERVER:控制可以连接到12c数据库的客户端版本(client --->orace 12c db )
SQLNET.ALLOWED_LOGON_VERSION_CLIENT:控制12c数据库可以连到哪些版本的数据库(orace 12c db  --->其它版本的oracle db),例如:控制通过DB LINK可连接到哪些版本的oracle库。
所以,该案例中主要起作用的是需要配置SQLNET.ALLOWED_LOGON_VERSION_SERVER。
在Oracle 12c中,虽然在sqlnet.ora加SQLNET.ALLOWED_LOGON_VERSION=8可以解决问题,但由于这个参数在12c已经废弃了,而是用SQLNET.ALLOWED_LOGON_VERSION_CLIENT和SQLNET.ALLOWED_LOGON_VERSION_SERVER代替。
如果继续使用该参数,会在告警日志中无穷无尽的报“Using deprecated SQLNET.ALLOWED_LOGON_VERSION parameter.”
需要检查
select username, account_status, default_tablespace, created, password_versions from dba_users;
alter user 服务名 identified by 密码;
oerr ora 28040
28040, 0000, "No matching authentication protocol"   //没有匹配的验证协议
// *Cause: There was no acceptable authentication protocol for 
// either client or server.
// *Action: The administrator should set the values of the
// SQLNET.ALLOWED_LOGON_VERSION_SERVER and 
// SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameters, on both the
// client and on the server, to values that match the minimum 
// version software supported in the system. 
// This error is also raised when the client is authenticating to 
// a user account which was created without a verifier suitable for
// the client software version. In this situation, that account's 
// password must be reset, in order for the required verifier to
// be generated and allow authentication to proceed successfully.
###28040
Oracle 12c连接时报错ORA-28040
问题描述:
客户端连接Oracle 12c的时候,报错误:
ORA-28040: No matching authentication protocol
问题原因:
Oracle 12c的参数SQLNET.ALLOWED_LOGON_VERSION默认等于11。当我们使用11g JDBC之前版本的thin驱动连接的时候,就会报错。
解决方法:
在数据库服务器上的oracle/network/admin/sqlnet.ora文件添加一行SQLNET.ALLOWED_LOGON_VERSION=8,重启数据库,重新连接数据库,可以成功连接,问题解决。
SQLNET.ALLOWED_LOGON_VERSION=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11
---
Oracle12c连接问题ORA-28040:没有匹配的验证协议
于是在MOS上搜索相关文档,查到这样一篇文章Client / Server Interoperability Support Matrix for Different Oracle Versions (文档 ID 207303.1)
从文章中可以看出只有11.2.0.3及以上的版本的客户端才能连接12.2版本的服务端。
再检查本地的Oracle客户端的版本是11.2.0.1,原来是客户端版本太低了,安装12.1版本的客户端,顺利登录。

###十四:需要关闭的特性###

密码过期
关于“Oracle 11g密码180天过期”,在拥有DBA权限的用户下执行:ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
一 查看默认密码到期时间
select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
二 修改密码到期时间为无限制
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
三 查看是否修改成功
select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
延时段
Oracle 11g  deferred_segment_creation  
11G中新特性,当表无数据时,不分配segment,以节省空间
设置deferred_segment_creation 参数
该参数值默认是TRUE,当改为FALSE时,无论是空表还是非空表,都分配segment
需注意的是:该值设置后对以前导入的空表不产生
作用,仍不能导出,只能对后面新增的表产生作用。
alter system set deferred_segment_creation=false;
SQL> show parameter FAILED_LOGIN_ATTEMPTS
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_max_failed_login_attempts        integer     10
查看资源使用
select * from V$RESOURCE_LIMIT;
---延时段创建解决方案
alter system set deferred_segment_creation=false;
select 'alter table ' || table_name || ' allocate extent;'
  from t1 (select table_name
             from user_tables
           minus
           select segment_name from user_segments where segment_type = 'Y') t1;
   
关于密码延时特性实验如下:
---密码连续输入次数为10次,同一个用户连续输入10次错误密码,用户会被锁定
select * from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS' and profile='DEFAULT';
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
等待事件均为row cache lock
ALTER system SET event='28401 trace name context forever, level 1' scope=spfile;
###禁用参数###
---基数反馈
alter session set "_optimizer_use_feedback"=false;
select c.child_number, c.use_feedback_stats, s.sql_text
  from v$sql_shared_cursor c, v$sql s
 where s.sql_id = c.sql_id
   and c.sql_id = 'g00wukhg516zd'
   and s.child_number = c.child_number;
   
---笛卡尔
---MERGE JOIN CARTESIAN
alter session set "_optimizer_mjc_enabled" = false;
alter system set "_optimizer_mjc_enabled" = false;
alter system set "_optimizer_mjc_enabled" = false;
---BITMAP CONVERSION TO ROWIDS
alter session set "_b_tree_bitmap_plans"=true;
alter system set "_b_tree_bitmap_plans"=false;
alter session set optimizer_features_enable='11.2.0.1';
alter system set "_b_tree_bitmap_plans"=false;
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
select /*+ opt_param('_b_tree_bitmap_plans','false') */ xxx from  xxx ...... ;
---BITMAP CONVERSION FROM ROWIDS  
###禁止生成监听日志
如果不想尽量监听日志,可以通过下面的方法关闭。
通过设置log_status参数为off实现禁止生成监听器日志
1)查看log_status参数内容
LSNRCTL> show log_status
2)通过调整log_status参数值为off禁止生成监听日志
LSNRCTL> set log_status off
然后保存设置即可:
save_config
###勒索病毒
---PROCEDURE "DBMS_CORE_INTERNAL        
---PROCEDURE "DBMS_SYSTEM_INTERNAL        
---PROCEDURE "DBMS_SUPPORT_INTERNAL     
select * from dba_procedures where procedure_name like '%DBMS_CORE_INTERNAL%';
select * from dba_procedures where procedure_name like '%DBMS_SYSTEM_INTERNAL%';   
select * from dba_procedures where procedure_name like '%DBMS_SUPPORT_INTERNAL%';
select * from dba_triggers where trigger_name like '%DBMS_CORE_INTERNAL%';
select * from dba_triggers where trigger_name like '%DBMS_SYSTEM_INTERNAL%';
select * from dba_triggers where trigger_name like '%DBMS_SUPPORT_INTERNAL%';

###十五:归档日志###

###查看数据库归档分布及频率
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 thread# = inst_id
 GROUP BY TRUNC(first_time), TO_CHAR(first_time, 'Dy')
 ORDER BY 1 desc;
 
###自动删除归档
[oracle@ncdbserver1 ~]$ crontab -l
01 01 * * * /home/oracle/rman/del_arch.sh
[oracle@ncdbserver1 rman]$ pwd
/home/oracle/rman
[oracle@ncdbserver1 rman]$ ls
del_arch.log  del_arch.sh
[oracle@ncdbserver1 rman]$ cat del_arch.sh 
#!/bin/bash
source /home/oracle/.bash_profile
rman target / nocatalog log=/home/oracle/rman/del_arch.log <<EOF 
crosscheck archivelog all;  
delete noprompt expired archivelog all;  
delete noprompt archivelog until time 'sysdate-7';  
exit;  
EOF 
[oracle@ncdbserver1 rman]$ ls
del_arch.log  del_arch.sh
[oracle@ncdbserver1 rman]$ cat del_arch.sh 
#!/bin/bash
source /home/oracle/.bash_profile
rman target / nocatalog log=/home/oracle/rman/del_arch.log <<EOF 
crosscheck archivelog all;  
delete noprompt expired archivelog all;  
delete noprompt archivelog until time 'sysdate-7';  
exit;  
EOF 
=============
del_arch.txt 
=============
connect target / 
crosscheck archivelog all;  
delete noprompt expired archivelog all;  
delete noprompt archivelog until time 'sysdate-2';  
exit;  
===================
rman_del_arch.bat 
===================
rman cmdfile=F:\del_arch\del_archlog.txt
select max(dbms_lob.getLength(sheetmodel) / 1024 / 1024),
       min(dbms_lob.getLength(sheetmodel) / 1024 / 1024),
       avg(dbms_lob.getLength(sheetmodel) / 1024 / 1024)
  from nc63prod.tb_taskshtmodel;
清理thread 1 sequence XX,thread 2 sequence XX;
delete noprompt archivelog until sequence 502132 thread 1 like '+BJ_HPL_GDDB_ARCH/arch/1_%'; 
delete noprompt archivelog until sequence 505872 thread 2 like '+BJ_HPL_GDDB_ARCH/arch/2_%';
sql 'alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS";
delete noprompt archivelog  from time "2020-10-25 08:00:00" until time "2020-10-25 20:00:00"  like '+BJ_HPL_GDDB_ARCH%';
###RMAN list
list backup;----备份集列表【归档日志文件、数据文件、spfile、control file】
list backupset;----备份集列表【归档日志文件、数据文件、spfile、control file】
list backup of database;--【数据文件】
list backup of tablespace 'SYSTEM','USERS';---【表空间】
list backup of datafile 1,3,5;---【数据文件对应的表空间信息】
list backup of controlfile;---【control file】
list backup of spfile;----【spfile】
list backup of archivelog all;---【归档日志文件】
list backup of archivelog from time|scn|logquence ..to time|scn|sequence...;---【时间段内的归档日志文件】
list backup completed after|before...[between...and...];-----【时间段内的备份集列表(归档日志文件、数据文件、spfile、control file)】
list backup tag=...;---【标记的备份集】
list backup device type disk;---【存放在磁盘中的备份集】
list backup recoverable;---【状态为available的备份集,这些备份集可以用于还原与恢复工作】
list backup summary;---【备份集列表统计信息】
RMAN> list backup of archivelog all;
RMAN> list backup of archivelog from scn 22727871375;
RMAN> list backup of archivelog until scn 22727813497;
RMAN> list backup of archivelog from scn 22727031113 until scn 22727268951;
RMAN> list backup of archivelog from logseq 25432;
RMAN> list backup of archivelog until logseq 25432;
RMAN> list backup of archivelog from logseq 25426 until logseq 25428;
RMAN> list archivelog until time 'sysdate-1';
登录到备份的数据库服务器上,执行
su - oracle
rman target /
---查询指定实例,指定sequence归档
list archivelog sequence 101424 thread 2 ;
---查询指定实例,指定sequence范围归档
list archivelog from sequence 101424 until sequence 101427 thread 2 ;
---查询指定时间前的归档
list archivelog until time 'sysdate-1';
---查询所有本地归档
list archivelog all;
select to_char(first_time,'yyyymmdd'),sum(blocks*512)/1024/1024/1024 from v$archived_log group by to_char(first_time,'yyyymmdd') order by 1 desc;
--每天归档量(个数)
select 
TO_CHAR(completion_time,'YYYY-MM-DD')
,count(*)  from v$archived_log group by  TO_CHAR(completion_time,'YYYY-MM-DD') order by TO_CHAR(completion_time,'YYYY-MM-DD');
select dest_id,SEQUENCE#,THREAD#,DELETED,status,to_char(FIRST_TIME,'yyyymmdd hh24:mi:ss') FIRST_TIME
 from v$archived_log where dest_id=1 and  THREAD#=1 and   SEQUENCE#  like '&sequence%';
 
select dest_id,SEQUENCE#,THREAD#,DELETED,status,to_char(FIRST_TIME,'yyyymmdd hh24:mi:ss') FIRST_TIME
 from v$archived_log where dest_id=1 and THREAD#=2 and   SEQUENCE#  like '&sequence%';
select dest_id,SEQUENCE#,THREAD#,DELETED,status,to_char(COMPLETION_TIME,'yyyymmdd hh24:mi:ss') complete
 from v$archived_log where   SEQUENCE#  like '5527%';
 
--按照sequence排序,指定时间
select dest_id,SEQUENCE#,thread#,status,to_char(COMPLETION_TIME,'yyyymmdd hh24:mi:ss') complete
 from v$archived_log where dest_id=1 and status='D' and COMPLETION_TIME>to_date('20201115 05:11:24','yyyymmdd hh24:mi:ss') and COMPLETION_TIME<to_date('20201115 06:11:24','yyyymmdd hh24:mi:ss') order by  SEQUENCE# ;
set lin 200 pages 200
select to_char(FIRST_TIME,'yyyymmdd hh24:mi:ss') FIRST_TIME,thread#,SEQUENCE#,status,to_char(COMPLETION_TIME,'yyyymmdd hh24:mi:ss') complete
 from v$archived_log where dest_id=1 and SEQUENCE# like '27110%' order by SEQUENCE#;
 
#每日归档日志量
select to_char(trunc(completion_time),'yyyy-mm-dd') as "Date",count(*) as "Count",sum(blocks * block_size)/1024/1024/1024 as "GB" from v$archived_log where CREATOR='ARCH' group by trunc(completion_time) order by 1; 
select to_char(trunc(completion_time),'yyyy-mm-dd') as "Date",count(*) as "Count",sum(blocks * block_size)/1024/1024/1024 as "GB" from v$archived_log    group by trunc(completion_time) order by 1; 
select NAME,DEST_ID,THREAD#,SEQUENCE#,CREATOR,STATUS,COMPLETION_TIME,COMPRESSED,BLOCKS from v$archived_log   where sequence# like '52202%' order by SEQUENCE#;
select NAME,DEST_ID,THREAD#,SEQUENCE#,CREATOR,STATUS,COMPLETION_TIME,COMPRESSED,BLOCKS from v$archived_log   where sequence# like '52202%' order by SEQUENCE#;
#每小时归档量
select to_char(a.completion_time, 'yyyy-mm-dd hh24') as "Date",
       count(*) as "Count",
       sum(blocks * block_size) / 1024 / 1024 / 1024 as "GB"
  from v$archived_log a
 group by to_char(a.completion_time, 'yyyy-mm-dd hh24')
 order by 1;

###十六:dbms_metadata.get_ddl###

###dbms_metadata.get_ddl###
select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_OWNER') from dual;
select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual;
select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual;
select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','PROCEDURE_OWNER') from dual;
select dbms_metadata.get_ddl('FUNCTION','FUNCTION_NAME','FUNCTION_OWNER') from dual;
select dbms_metadata.get_ddl('DB_LINK','LINKAB','SUNDS') from dual;
set pagesize 0
set long 90000
set feedback off
set echo off
spool schema_ddl.sql
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name,u.table_owner) FROM USER_INDEXES u;
spool off;

###十七:Oracle游标溢出###

Oracle 使用 OPEN_CURSORS 参数指定一个会话一次最多可以打开的游标的数量。
应该是游标溢出了,请在下回问题重现时,不要重启中间件,马上执行如下SQL,将查询结果导出成csv文件发给我: 
select s.sid, name, value,o.sql_id,t.SQL_TEXT 
from v$statname n, v$sesstat s, v$open_cursor o,v$sql t 
where o.sid = s.sid and o.sql_id=t.SQL_ID 
and n.name in ('opened cursors current') 
and s.statistic# = n.statistic# order by value desc;
如果数据库用了rac,请连接每个实例,各运行那条SQL,将查询结果发给我。 
下面的查询按降序显示用户“SCOTT”为每个会话打开的游标数。  
select o.sid, osuser, machine, count(*) num_curs
      from v$open_cursor o, v$session s
      where user_name = 'SCOTT' and o.sid=s.sid
     group by o.sid, osuser, machine
    order by   num_curs desc;
select q.sql_text
     from v$open_cursor o, v$sql q
     where q.hash_value=o.hash_value and o.sid = 217;
 
--查看游标使用情况
select b.name,sum(a.value)
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
group by b.name;
--查看会话游标
select a.value, 
 s.username, 
 s.sid, 
 s.serial# 
 from 
 v$sesstat a, 
 v$statname b, 
 v$session s 
 where 
 a.statistic# = b.statistic# and 
 s.sid=a.sid and 
 b.name = 'opened cursors current' and a.value>20 order by 1; 
select s.username,sum(a.value)
 from 
 v$sesstat a, 
 v$statname b, 
 v$session s 
 where 
 a.statistic# = b.statistic# and 
 s.sid=a.sid and 
 b.name = 'opened cursors current' group by s.username;
set linesize 1000
set pagesize 1000
SELECT SUM (a.VALUE) total_cur,
         AVG (a.VALUE) avg_cur,
         MAX (a.VALUE) max_cur,
         s.username,
         s.machine
    FROM v$sesstat a, v$statname b, v$session s
   WHERE     a.statistic# = b.statistic#
         AND s.sid = a.sid
         AND b.name = 'opened cursors current'
GROUP BY s.username, s.machine,s.sid
ORDER BY 1;
select sid , count(*) from v$open_cursor group by sid having count(*) > 50 order by 2 desc;
select inst_id,username,machine, count(machine) from gv$session  group by inst_id,username,machine order by 4; 
CREATE OR REPLACE PROCEDURE kill_session AS
  CURSOR a,b IS
    select distinct s.sid,i.serial#
      from v$statname n, v$sesstat s, v$open_cursor o, v$sql t, v$session i
     where o.sid = s.sid
       and o.sql_id = t.SQL_ID
       and n.name in ('opened cursors current')
       and s.statistic# = n.statistic#
       and s.sid = i.sid
       and value >= 15000;
BEGIN
  FOR i,j IN a LOOP
    EXECUTE IMMEDIATE 'alter system kill session ' ||'i.sid,j.serial#';
    exit when a%notfound;
  END LOOP;
END;
CREATE OR REPLACE PROCEDURE kill_spid AS
  CURSOR a IS
    select distinct spid
      from v$statname    n,
           v$sesstat     s,
           v$open_cursor o,
           v$sql         t,
           v$session     i,
           v$process     p
     where o.sid = s.sid
       and o.sql_id = t.SQL_ID
       and n.name in ('opened cursors current')
       and s.statistic# = n.statistic#
       and s.sid = i.sid
       and p.addr = i.paddr
       and value >= 2;
BEGIN
  FOR i IN a LOOP
    EXECUTE IMMEDIATE 'host orakill orcl '||i.spid;
  END LOOP;
END;
在DB里写个过程,捕捉要杀掉的进程ID,输出到OS下的一文本里,然后在OS下写脚本,调用该过程,输出要杀的进程后,再运行OS下的该文本,来杀掉该进程。
create or replace procedure kill_spid as
  cursor c_job is
    select distinct i.sid, i.serial#, p.spid
      from v$statname    n,
           v$sesstat     s,
           v$open_cursor o,
           v$sql         t,
           v$session     i,
           v$process     p
     where o.sid = s.sid
       and o.sql_id = t.SQL_ID
       and n.name in ('opened cursors current')
       and s.statistic# = n.statistic#
       and s.sid = i.sid
       and p.addr = i.paddr
       and value >= 1;
  c_row c_job%rowtype;
begin
  for c_row in c_job loop
    dbms_output.put_line(c_row.sid|| '-' ||c_row.serial#||'-'||c_row.spid);
  end loop;
end;
SQL>
CREATE OR REPLACE PROCEDURE kill_spid IS
  chen UTL_FILE.file_type;
BEGIN
  chen := UTL_FILE.FOPEN('AAA', 'chen.bat', 'W');
  FOR x IN (select distinct a
              from (select 'orakill orcl ' || p.spid as a
                      from v$statname    n,
                           v$sesstat     s,
                           v$open_cursor o,
                           v$sql         t,
                           v$session     i,
                           v$process     p
                     where o.sid = s.sid
                       and o.sql_id = t.SQL_ID
                       and n.name in ('opened cursors current')
                       and s.statistic# = n.statistic#
                       and s.sid = i.sid
                       and p.addr = i.paddr
                       and value >= 10000)) LOOP
    UTL_FILE.PUT_LINE(chen, x.a);
  END LOOP;
  UTL_FILE.FCLOSE(CHEN);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 2000));
END;
/

###十八:BBED###

###BBED
使用bbed修改scn/
这4个offset的位置内容,文档上是这样说的:
Oracleconsiders four attributes of this data structure when determining if a datafile is sync with the other data files of the database:
(1)kscnbas (at offset 484) - SCN of last change to the datafile.
(2)kcvcptim (at offset 492) -Time of the last change to the datafile.
(3)kcvfhcpc (at offset 140) - Checkpoint count.
(4)kcvfhccc (at offset 148) - Unknown, but is always 1 less than thecheckpoint point count.
oracle主要通过这4个来判断一致性,至于中文说明,我也写在上面了。

###十九:大表删除###

SM_BUSILOG_DEFAULT :中的数据是 业务操作日志 可以定期删除
TB_TASKSHTMODEL :这个是预算的缓存数据表。根据年度保存的,理论上去年的预算就没有用了,可以清理,但是清理后第一次打开预算样表会比较慢。
pfxx:为外部交换平台目录,pfxxtemp下文件可以删减
1.先创建SM_BUSILOG_DEFAULT_BAK表和FIP_MESSAGELOG_BAK表
   create table SM_BUSILOG_DEFAULT_BAK as select * from SM_BUSILOG_DEFAULT where 1=0;
   create table FIP_MESSAGELOG_BAK as select * from FIP_MESSAGELOG where 1=0;
2.创建如下存储过程
create or replace procedure cleanLogTable as
begin
   insert into table SM_BUSILOG_DEFAULT_BAK as select * from SM_BUSILOG_DEFAULT where ts<=to_char(sysdate-30,'YYYY-MM-DD');
   delete from SM_BUSILOG_DEFAULT where ts<=to_char(sysdate-30,'YYYY-MM-DD');
   commit;
   insert into table FIP_MESSAGELOG_BAK as select * from FIP_MESSAGELOG where ts<=to_char(sysdate-30,'YYYY-MM-DD');
   delete from FIP_MESSAGELOG where ts<=to_char(sysdate-30,'YYYY-MM-DD');
   commit;
end;
3.创建定时运行存储过程的Job,该存储过程每30天运行一次,凌晨3点运行
VARIABLE JOBNO NUMBER; 
VARIABLE INSTNO NUMBER; 
BEGIN 
    SELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE; 
    DBMS_JOB.SUBMIT(:JOBNO,'cleanLogTable;',TRUNC(SYSDATE)+1+3/24,'TRUNC(SYSDATE)+30+3/24',TRUE,:INSTNO); 
    COMMIT; 
    END; 
   /
   
###查询段大小
select a.table_name,
       a.column_name,
       b.segment_name,
       b.bytes / 1024 / 1024 as space_mb
  from dba_lobs a, dba_segments b
 where a.segment_name = b.segment_name
   and a.owner = 'PDNC'
union all
select a.table_name,
       a.column_name,
       b.segment_name,
       b.bytes / 1024 / 1024 as space_mb
  from dba_lobs a, dba_segments b
 where a.index_name = b.segment_name
   and a.owner = 'PDNC'
 order by space_mb desc;
 
SELECT (SELECT NVL(SUM(S.BYTES), 0) -- The Table Segment size  
          FROM DBA_SEGMENTS S
         WHERE S.OWNER = UPPER('NCDB')
           AND (S.SEGMENT_NAME = UPPER('SM_FILESTOREVIEW'))) +
       (SELECT NVL(SUM(S.BYTES), 0) -- The Lob Segment Size  
          FROM DBA_SEGMENTS S, DBA_LOBS L
         WHERE S.OWNER = UPPER('NCDB')
           AND (L.SEGMENT_NAME = S.SEGMENT_NAME AND
               L.TABLE_NAME = UPPER('SM_FILESTOREVIEW') AND
               L.OWNER = UPPER('NCDB'))) +
       (SELECT NVL(SUM(S.BYTES), 0) -- The Lob Index size  
          FROM DBA_SEGMENTS S, DBA_INDEXES I
         WHERE S.OWNER = UPPER('NCDB')
           AND (I.INDEX_NAME = S.SEGMENT_NAME AND
               I.TABLE_NAME = UPPER('SM_FILESTOREVIEW') AND
               INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('NCDB'))) "TOTAL TABLE SIZE"
  FROM DUAL;

###二十:OEM###

emca -deconfig dbcontrol -repos drop
emca -config dbcontrol db -repos create
emca -config dbcontrol db -repos create
emca -repos drop
emca -config dbcontrol db -repos create
emca -config dbcontrol db -repos create

###二十一:创建测试数据###

create table t0115(id number,name varchar2(100));
declare
begin
  for i in 1 .. 10000 loop
    insert into t0115 values (i*10000, '小陈');
    commit;
  end loop;
end;

###二十二:EXP-00056###

EXP-00056: ORACLE error 904 encountered
这个是ORACLE发布的BUG,我在从9.2.0.1升级到9.2.0.6的时候也遇见过! 
运行@$ORACLE_HOME\rdbms\admin\catexp.sql
@$ORACLE_HOME\rdbms\admin\catpatch.sql
步骤:
SQL>CONNECT SYS AS SYSDBA
SQL>@$ORACLE_HOME\rdbms\admin\catexp.sql
SQL>Shutdown immediate
SQL>startup migrate
SQL>@$ORACLE_HOME\rdbms\admin\catpatch.sql
SQL>shutdown immediate
SQL>start up
此过程时间比较常,大概2小时左右。
select sql_text from v$sql where sql_id in (select sql_id from gv$session where paddr in (select addr from gv$process where program = 'ORACLE.EXE (SHAD)'));

###二十三:ORA-01102###

ORA-01102: cannot mount database in EXCLUSIVE mode
查了ml,了解到  ORA-1102 错误原因:
1 在ORACLE_HOME/dbs/存在 "sgadef.dbf" 文件或者lk 文件。这两个文件是用来用于锁内存的。
2 oracle的 pmon, smon, lgwr and dbwr等进程未正常关闭。
3 数据库关闭后,共享内存或者信号量依然被占用。
lk说明DATABASE 已经是MOUNT状态了,不用再次MOUNT.当 DATABASE 被UNMOUNT 后会被自动删除,如果DATABASE没有MOUNT,却依然存在这个问题,只有手工将其删除。
具体解决ORA-01102问题的步骤:
[oracle@oracle ~]$ cd $ORACLE_HOME
[oracle@oracle db_1]$ cd dbs
[oracle@oracle dbs]$ ls
hc_orcl.dat  initdw.ora  init.ora  lkORCL  orapworcl  spfileorcl.ora
此时在数据库shutdown的情况下,仍然有不少进程关联着进程,显然是"死锁"
[oracle@oracle dbs]$ /sbin/fuser -u lkORCL
sculkget: failed to lock /orasoft/product/10.2.0/db_1/dbs/lkWWL exclusive   同一个进程被多个用户访问发生了独占模式
sculkget: lock held by PID: 26312                                           发生独占模式的进程号为pid:26312
ORA-09968: Message 9968 not found; No message file for product=RDBMS, facility=ORA  并且没有找到9968的数据信号,同时了我们该信号的类型
Linux Error: 11: Resource temporarily unavailable                           导致资源无法被正常利用
Additional information: 26312
Thu Nov 17 15:51:16 2011
ORA-1102 signalled during: ALTER DATABASE   MOUNT..
[oracle@ora10g dbs]$ ps -ef|grep 26312
oracle   26312     1  0 15:43 ?        00:00:02 ora_dbw0_wwl
报错ORA-01102,而且安装的时候也没有看到哪里有报错信息,一路都比较顺利,
而且这也是第一次我碰到这个问题,当时我首先就检查了alert日志文件,并把相关的错误信息在metalink上查看过了,
经过分析后判断是由于进程间通信被争用导致,以下是我处理该问题的一个思路,并在最后附上了metalink原文以及朋友对该
问题的一个理解和处理办法。
为什么会发生如下错误,原因是多个用户同时去访问同一个资源就会发生独占模式,
因为在Linux里面默认一个进程只被一个用户访问,要避免这个问题,在创建用户的时候
指定默认去指定不同于其它用户的优先级就可以避免此类问题的发生。

###二十四:登录触发器###

CREATE OR REPLACE TRIGGER logon_denied_to_alert
  AFTER servererror ON DATABASE
DECLARE
  message   VARCHAR2(168);
  ip        VARCHAR2(15);
  v_os_user VARCHAR2(80);
  v_module  VARCHAR2(50);
  v_action  VARCHAR2(50);
  v_pid     VARCHAR2(10);
  v_sid     NUMBER;
  v_program VARCHAR2(48);
BEGIN
  IF (ora_is_servererror(1017)) THEN
    -- get ip FOR remote connections :
    IF upper(sys_context('userenv', 'network_protocol')) = 'TCP' THEN
      ip := sys_context('userenv', 'ip_address');
    END IF;
    SELECT sid INTO v_sid FROM sys.v_$mystat WHERE rownum < 2;
    SELECT p.spid, v.program
      INTO v_pid, v_program
      FROM v$process p, v$session v
     WHERE p.addr = v.paddr
       AND v.sid = v_sid;
    v_os_user := sys_context('userenv', 'os_user');
    dbms_application_info.read_module(v_module, v_action);
  
    message := to_char(SYSDATE, 'YYYYMMDD HH24MISS') ||
               ' logon denied from ' || nvl(ip, 'localhost') || ' ' ||
               v_pid || ' ' || v_os_user || ' with ' || v_program || ' – ' ||
               v_module || ' ' || v_action;
    sys.dbms_system.ksdwrt(2, message);
  
  END IF;
END;
/
---登录信息(触发器)
---<<DBA手记>>
create table log$information
(
  logon_time timestamp,
  host_name varchar2(100),
  username varchar2(40),
  sechemaname varchar2(40),
  sessionuser varchar2(40),
  ip_address varchar2(100)
);
create or replace trigger tr_login_record
  after logon on database
begin
  insert into log$information
    select systimestamp,
           sys_context('USERENV', 'HOST'),
           sys_context('USERENV', 'CURRENT_USER'),
           sys_context('USERENV', 'CURRENT_SCHEMA'),
           sys_context('USERENV', 'SESSION_USER'),
           sys_context('USERENV', 'IP_ADDRESS')
      FROM dual;
  commit;
exception
  when others then
    null;
end;
/

###二十五:代码里SQL增加随机数###

---1
String sql="select /*+ "+new java.util.Random().nextDouble()+" */ col"
---2
StringBuffer strSql =
    new StringBuffer(" SELECT /*+ dbms_random.random() */ col...... ");
  if(isFreeItem)
  ---3
StringBuilder bf = new StringBuilder(" select ");
    bf.append("/*+dbms_random.value(100,0)*/");
    bf.append(this.getSelectFieldsPart());
    bf.append(" from ");
    bf.append(this.getJoinPart());
    bf.append(" where ");
    bf.append(this.fixWhere.toString());
    SqlUtil.andTowWhere(bf, this.getWhere());

###二十六:PLSQL乱码###

NLS_LANG
SIIMPLIFIED CHINESE_CHINA.ZHS16GBK
设置变量名:NLS_LANG,变量值:SIMPLIFIED CHINESE_CHINA.ZHS16GBK

###二十七:SCN###

---SCN 
SELECT A.FILE#,
       A.NAME,
       (SELECT CHECKPOINT_CHANGE# FROM V$DATABASE) SYSTEM_CKPT_SCN,
       A.CHECKPOINT_CHANGE# DF_CKPT_SCN,
       A.LAST_CHANGE# END_SCN,
       B.CHECKPOINT_CHANGE# START_SCN,
       B.RECOVER,
       A.STATUS
  FROM V$DATAFILE A, V$DATAFILE_HEADER B
 WHERE A.FILE# = B.FILE#;
系统检查点(System Checkpoint)SCN
当checkpoint完成后,ORACLE将System Checkpoint SCN号存放在控制文件中。我们可以通过下面SQL语句查询:
select checkpoint_change# from v$database;  ---13944498
数据文件检查点(Datafile Checkpoint)SCN
当checkpoint完成后,Oracle将Datafile Checkpoint SCN存放在控制文件中。我们可以通过下面SQL语句查询所有数据文件的Datafile Checkpoinnt SCN。
select name,checkpoint_change# from v$datafile;  ---13944498
开始SCN(Start SCN)
Oracle将StartSCN存放在数据文件头中。这个SCN用于检查数据库启动过程是否需要做media recovery。我们可以通过以下SQL语句查询:
select name,checkpoint_change# from v$datafile_header; ---13944498
结束SCN(Stop SCN)
ORACLE将StopSCN存放在控制文件中。这个SCN号用于检查数据库启动过程是否需要做instance recovery。我们可以通过以下SQL语句查询:
select name,last_change# from v$datafile; ---

###二十八:数据初始化###

---查看数据库SCN
select 'exp_scn' item,current_scn value from v$database 
union all
select 'Min_start_scn' item,min(start_scn) value from v$transaction;
---expdp导出数据
expdp scott/tiger@xxx schemas=scott directory=TEST_DIR dumpfile=scott.dmp logfile=scott.log flashback_scn=209914484
---导入到装载端
impdp scott/tiger schemas=scott directory=test_dir dumpfile=scott.dmp logfile=impdp.log
---编译无效对象
select 'alter ' || object_type || ' ' || owner || '.' || object_name ||
       ' compile;'
  from dba_objects
 where status = 'INVALID'
   and owner = 'SCOTT';
---禁止trigger
select 'alter trigger ' || OWNER || '.' || TRIGGER_NAME || ' enable;'
  from dba_triggers
 where owner = 'SCOTT'
   and status = 'ENABLED';
   
---启动capture
根据获取的SCN启动capture。
如果有Min_start_scn,那么用Min_start_scn启动Capture;
否则用exp_scn启动capture;
---启动loader
使用exp_scn指定scn启动loader;

###二十九:DB2###

DB2CMD
db2 connect to nc user DB2OFMIS using ufsoft*123 
-----查看当前实例下建立了哪些数据库?
db2 list database directory
db2look -tvf  aaa.sql
db2move  NC import -io insert -u DB2OFMIS -p ufsoft*123
db2 list tablespaces show detail
list tables
db2pd -d NC -tablespace NNC_INDEX01
ALTER TABLESPACE DMS1 AUTORESIZE YES
DB2 SQL error: SQLCODE: -964
update db cfg using LOGFILSIZ 20000   ---更改 日志文件大小
update db cfg using LOGPRIMARY 15     ---更改 主日志文件的数目
update db cfg using LOGSECOND  8      ---更改 辅助日志文件的数目 
CREATE regular TABLESPACE NNC_INDEX01 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC633\nnc_index01' 409600) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4
alter tablespace NNC_INDEX01 ADD ( FILE 'D:\DB2\NC633\nnc_index01A' 409600)
db2 connect reset 
db2 force application all
get db cfg [for dbname]
db2set DB2CODEPAEG=1386
---SQL1478W
db2set DB2_OVERRIDE_BPF=5000
db2stop force
db2start
========
DB2---NC
========
---db2move.lst
---ncdb.sql   ---create_forign.sql   ---create_tb.sql
---export.out
---import.out
---db2 =>force applications all
---drop database NC
---SQL1047N The appplication is already connected to another database.
---connect reset
CREATE DATABASE NC USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM CATALOG TABLESPACE MANAGED BY DATABASE USING (FILE 'D:\DB2\NC\Catalogdata1' 128000) USER TABLESPACE MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC\Userdata1' 512000 ) TEMPORARY TABLESPACE MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC\Tempspace1' 768000 )
 
---connect to nc user db2inst1 using ufsoft*123
connect to nc user DB2OFMIS using ufsoft*123 
CREATE Bufferpool NCUSED4 SIZE 102400 PAGESIZE 4K
CREATE Bufferpool NCUSED16 SIZE 38400 PAGESIZE 16K
CREATE Bufferpool NCTMPUSED16 SIZE 38400 PAGESIZE 16K
CREATE regular TABLESPACE NNC_DATA01 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC\nnc_data01' 409600) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16
CREATE regular TABLESPACE NNC_DATA02 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC\nnc_data02' 409600) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16
CREATE regular TABLESPACE NNC_DATA03 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC\nnc_data03' 409600) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16
---409600*16k=6.25G
---Alter tablespace NNC_DATA01 add (file ‘D:\DB2\NC\nnc_data01a’ 409600)
CREATE regular TABLESPACE NNC_INDEX01 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC\nnc_index01' 409600) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4
CREATE regular TABLESPACE NNC_INDEX02 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC\nnc_index02' 409600) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4
CREATE regular TABLESPACE NNC_INDEX03 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC\nnc_index03' 409600) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4
---409600*4k=1.5625G
---Alter tablespace NNC_INDEX01 add (file ‘D:\DB2\NC\nnc_index01b’ 409600)
CREATE USER TEMPORARY TABLESPACE USERTEMP PAGESIZE 16K MANAGED BY SYSTEM USING ( 'D:\DB2\NC\Usertemp1' ) EXTENTSIZE 32 OVERHEAD 10.67 PREFETCHSIZE 32 TRANSFERRATE 0.04 BUFFERPOOL NCTMPUSED16
CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 16K MANAGED BY SYSTEM USING ( 'D:\DB2\NC\Tempspace2' ) EXTENTSIZE 32 OVERHEAD 10.67 PREFETCHSIZE 32 TRANSFERRATE 0.04 BUFFERPOOL NCTMPUSED16
GRANT  DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE  TO USER db2inst1
GRANT  DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE  TO USER DB2OFMIS
GRANT  DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE  TO USER db2inst1
GRANT  DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE  TO USER DB2OFMIS
GRANT USE OF TABLESPACE NNC_DATA01 TO USER DB2OFMIS WITH GRANT OPTION
GRANT USE OF TABLESPACE NNC_DATA02 TO USER DB2OFMIS WITH GRANT OPTION
GRANT USE OF TABLESPACE NNC_DATA03 TO USER DB2OFMIS WITH GRANT OPTION
GRANT USE OF TABLESPACE NNC_INDEX01 TO USER DB2OFMIS WITH GRANT OPTION
GRANT USE OF TABLESPACE NNC_INDEX02 TO USER DB2OFMIS WITH GRANT OPTION
GRANT USE OF TABLESPACE NNC_INDEX03 TO USER DB2OFMIS WITH GRANT OPTION
GRANT USE OF TABLESPACE USERTEMP TO USER DB2OFMIS WITH GRANT OPTION
CONNECT RESET 
需要特别注意,本版支持的是DB2V10版本,在建库时,务必检查以下参数设置是否正确
db2set DB2_COMPATIBILITY_VECTOR=
db2set DB2_SKIPINSERTED=YES 
db2set DB2_INLIST_TO_NLJN=YES 
db2set DB2_MINIMIZE_LISTPREFETCH=YES 
db2set DB2_ANTIJOIN=EXTEND
db2stop force
db2start
DB2临时表空间要求
CREATE USER TEMPORARY TABLESPACE USERTEMP01 PAGESIZE 4K MANAGED BY SYSTEM USING ( 'D:\DB2\NC\usertemp01' ) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 16 TRANSFERRATE 0.9 BUFFERPOOL IBMDEFAULTBP
---GRANT USE OF TABLESPACE TEMPSPACE1 TO USER db2inst1 WITH GRANT OPTION
GRANT USE OF TABLESPACE TEMPSPACE1 TO USER DB2OFMIS WITH GRANT OPTION
---DB21034E
GRANT USE OF TABLESPACE USERTEMP01 TO USER DB2OFMIS WITH GRANT OPTION
db2 connect to nc user DB2OFMIS using ufsoft*123
db2 -tvf create_tb.sql 
db2move nc import –io insert -u DB2OFMIS -p ufsoft*123
db2 –tvf create_foreign.sql
---SQL3088N 指定要装入数据库列 名称 的源列与该数据库列不兼容,但数据库列不可为空。
---所有含有not null约束的表,导入时都报错SQL3088N,无法导入数据;----可能是导出的数据本身有问题
---SQL3306N An SQL error -964     ----日志满了
---导入报错
get db cfg for 数据库名
后来把这三个参数的大小相应调整了一下,调整后如下:
Log file size (4KB) (LOGFILSIZ) = 65536 
(logprimary + logsecond) * logfilsiz * 4096
先看增大日志的容量,注意红色的值为1024
$db2 update db cfg for zssqdb01 using logfilsiz 8192 将其增大到8192
然后停止应用,停库再启库就生效了
$db2 force applications all
$db2stop
$db2start
---ERROR -3304.Check message file tab1118.msg!     ---导入表结结构的文本中,没有这个表,只有表数据,没有表结构
SQLCODE:-3304 -sqlstate:
sql3304N The table does not exist.
---db2 -tvf a.sql
---报错SQLSTATE=42710   
---DB2 V10.5的数据库导入到低版本数据库时,ORGANIZE BY ROW是10.5的新特性
===============
DB2LOOK
===============
db2look -d cqyancao -e -o db.sql -i db2user -w psw 
           数据库名       要出文件名   用户名     密码 
示例: db2look -d DEPARTMENT -u walid -e -o db2look.sql 
===============
DB2---导入导出
===============
db2 backup database nc to d:\backup
如果数据库正在被使用,可能回报错“SQL1035N  
The database is currently in use.  SQLSTATE=57019”需要停掉服务,用命令:
db2 force application all
db2 backup db nc to d:\backup
恢复数据库 
命令格式:db2 restore db <数据库名> from <目录名> 
例:
db2 restore db nc from d:\backup
backup restore 使用情况说明
此类备份恢复在数据库是相同操作系统环境下进行,如果进行跨操作系统平台就不能使用了,备份恢复比较简单。
db2move的备份恢复
执行db2move 命令导出数据
命令格式: db2move <数据库名>  export –tc <用户名>   -u <用户名>   -p <用户密码>
参数 –tc 创建表的用户名
     -tn 用户的表名
     -sn 模式名 即导出该模式下的所有表
以上三个参数只适用于export命令
如:---导出sample 的org表
C:\ >db2move  sample  export  -tn   org  
执行db2move 命令导出数据
使用export出的逻辑数据集进行恢复
执行db2move命令,导入表的结构及数据。
命令格式:
db2move <数据库名> import  -io   insert    -u <用户名> -p <密码>
db2move <数据库名> import  -io  replace_create -u <用户名> -p <密码>
执行db2look 命令导出数据结构创建语句(即表、试图等的创建语句)
   命令格式:db2look  -d <数据库名>  
-u <用户名>  -e -o  d:\db2_bk\credb.sql  -i <用户名> -w <用户密码> 
db2look –d sample –e –o d:\db2_bk\credb.sql
NC产品通过db2move备份恢复一例:
1、数据库的导出
1) 首先连接到要导出的数据库上,如本例导出数据库nctest:
db2   connect  to   nctest   user   db2inst1  using   db2inst1 
其中的参数user后面跟指定用户,using后面指定用户的密码
2) 用db2look 导出数据对象的定义语句
db2look   –d   nctest  –e  –o  ncdb.sql   –i  db2inst1   –w  db2inst1
命令中的参数 –o 意思是将数据对象的定义导出到指定的文件中
3) 使用db2move 命令导出数据库对象的数据
db2move   nctest  export    –u   db2inst1  –p   db2inst1
–u 指定连接的用户 –p 指定用户的密码
3.在将数据库的数据结构定义文件 ncdb.sql 中语句中的创建表、索引、主键等的定义语句单独保存在一个文件中,如保存在create_tb.sql文件中。把创建外键、触发器、试图的语句单独放在另一个文件中,如保存在create_foreign.sql
保存方法:从文件头查看foreign 查看到第一个外键,包括它和下面的所有语句放到create_foreign.sql,前面的放到create_tb.sql。
-----导入数据库
4.连接到数据库
db2 Connect to ncdb 
5. 接下来执行创建数据库对象的文件
db2   -tvf   create_tb.sql 
6. 导入数据
创建完了数据库的基本对象后,开始导入数据,进入包含数据文件的目录,
操作系统的命令:cd 存放数据文件的目录
执行命令: 
db2move   nctest   import   –io   insert   -u   db2admin  -p  db2admin

###三十:sqlserver###

====================
SQLServer锁查询
====================
--查询数据库状态
select * from sys.databases;
 
--查询数据库状态
select name,user_access,user_access_desc,
    snapshot_isolation_state,snapshot_isolation_state_desc,
    is_read_committed_snapshot_on
from sys.databases
---禁用并行(如果报表查询并发量很小可不禁用)
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
---启用快照隔离等级
---此调整需要数据库上无连接
ALTER DATABASE 数据库名称 SET READ_COMMITTED_SNAPSHOT ON;
--进入TEST数据库
use TEST
--查看锁和会话信息
sp_who2
--查看锁和会话对应SQL
dbcc inputbuffer(52)
use TEST 
sp_who2
dbcc inputbuffer(57)
dbcc inputbuffer(52)
sp_who2
 --杀掉阻塞的SQL   
 kill 52
 
你好: 
查看附件,问题期间数据库出现死锁,SQLServer数据库在默认配置下很容易出现死锁, 
建议启用行版本快照隔离,启动过程如下: 
ALTER DATABASE database_name SET READ_COMMITTED_SNAPSHOT ON; 
注意: 
执行如上一行代码的时候,务必确保NC或者WAS中间件没有启动,数据库除了执行该行命令的SQL连接之外无任何别的连接, 
即SQL SERVER MANAGERMENT STUDIO工具只保留一个SQL窗口执行该行命令,关闭其余SQL窗口,或者直接通过kill命令杀掉该数据库上的其他连接; 
如果并发操作比较大,可以禁用并行 
---禁用并行(如果报表查询并发量很小可不禁用) 
sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE WITH OVERRIDE; 
GO 
sp_configure 'max degree of parallelism', 1; 
GO 
RECONFIGURE WITH OVERRIDE; 
GO 
如有问题,请加QQ3012898098;
 
###SQLSERVER 
---查询TOP_SQL
SELECT top 10  
    (total_elapsed_time / execution_count)/1000 N'平均时间ms'  
    ,total_elapsed_time/1000 N'总花费时间ms'  
    ,total_worker_time/1000 N'所用的CPU总时间ms'  
    ,total_physical_reads N'物理读取总次数'  
    ,total_logical_reads/execution_count N'每次逻辑读次数'  
    ,total_logical_reads N'逻辑读取总次数'  
    ,total_logical_writes N'逻辑写入总次数'  
    ,execution_count N'执行次数'  
    ,creation_time N'语句编译时间'  
    ,last_execution_time N'上次执行时间'  
    ,SUBSTRING(  
        st.text,   
        (qs.statement_start_offset/2) + 1,   
        (  
            (CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2  
        ) + 1  
    ) N'执行语句'  
    ,qp.query_plan  
FROM  sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp  
WHERE  
    SUBSTRING(  
        st.text,   
        (qs.statement_start_offset/2) + 1,  
        (  
            (CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2  
        ) + 1  
    ) not like '%fetch%'  
ORDER BY  total_elapsed_time / execution_count DESC;  
###sqlserver备份脚本
---1 backup.bat 
@echo off
set path=%path%;C:\Program Files\Microsoft SQL Server\100\Tools\Binn
set dates=%date% %time%
echo %dates% >> D:\backup_test\logs.txt
echo Sql_Back_Start >> D:\backup_test\logs.txt
echo . >> D:\backup_test\logs.txt
SQLCMD.exe  -S 127.0.0.1 -U sa -P Sa123456! -i D:\backup_test\new_dbbackup.sql 
set dates=%date% %time%
echo %dates% >> D:\backup_test\logs.txt
echo Sql_Back_Finish >> D:\backup_test\logs.txt
echo . >> D:\backup_test\logs.txt
echo . >> D:\backup_test\logs.txt
forfiles /p "D:\backup_test" /s /m *.bak /d -7 /c "cmd /c del @path"
forfiles /p "D:\backup_test" /s /m *.trn /d -7 /c "cmd /c del @path"
---2.new_dbbackup.sql 
DECLARE @name varchar(45)
DECLARE @datetime char(17)
DECLARE @path varchar(255)
DECLARE @bakfile varchar(290)
DECLARE @baklog varchar(290)
set @name='necology20180627'
set @datetime=CONVERT(char(11),getdate(),120) + REPLACE(CONVERT(char(8),getdate(),108),':','')
set @path='D:\backup_test'
set @bakfile=@path+'/'+@name+'_'+@datetime+'.bak'
set @baklog=@path+'/'+@name+'_'+@datetime+'.trn'
backup database @name to disk=@bakfile with name=@name,COMPRESSION
backup log @name to disk=@baklog with name=@name,COMPRESSION
go
---dbcc sqlperf(logspace)  ---0.3818648  ---11.051 ---0.1954396
---查看备份,恢复进度
SELECT   DB_NAME(er.[database_id]) [DatabaseName],  
er.[command] AS [CommandType],  
er.[percent_complete],  er.start_time,
CONVERT(DECIMAL(5, 2), er.[percent_complete]) AS [Complete_Percent]  
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]  
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m]  
FROM sys.dm_exec_requests AS er  
WHERE --DB_NAME(er.[database_id]) in ('xxxx') and
er.[command] in ( 'RESTORE DATABASE' ,'BACKUP DATABASE','BACKUP LOG')
order by er.start_time desc
select  text,cpu,* 
from sys.sysprocesses a cross apply sys.dm_exec_sql_text(a.sql_handle) st
where status<>'sleeping'
order by a.cpu desc
select  text,cpu,spid,physical_io,cpu,login_time,status,cmd
from sys.sysprocesses a cross apply sys.dm_exec_sql_text(a.sql_handle) st
where status<>'sleeping' and text like '%DECLARE @projguid VARCHAR(MAX)%'
order by physical_io desc
---批量kill spid 
kill spid1
kill spid2
kill spid3
......

###三十一:mysql###

备份数据库
rem *******************************backup Start*****************************
@echo off
set SrcDir=c:\edoc\
rem 指定天数
set DaysAgo=2
set PAT="C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe"
forfiles /p %SrcDir% /s /m thams_*.sql /d -%DaysAgo% /c "cmd /c del @file"
set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
%PAT% --opt -u root --password=ziguangruanjian --default-character-set=utf8 thams > %SrcDir%thams_%Ymd%.sql
@echo on
rem *******************************backup End*****************************
cd C:\Program Files\MySQL\MySQL Server 5.7\bin
mysql.exe -uroot -pziguangruanjian thams_0611 < D:\DB\thams_20180611.sql
mysqldump.exe -uroot -pziguangruanjian thams > D:\DB\thams_20180612.sql
mysqldump.exe -uroot -pziguangruanjian thams table1 > D:\DB\thams_tables1_20180612.sql
恢复数据库
---方法一
cd C:\Program Files\MySQL\MySQL Server 5.7\bin
create database thams_0611;
mysql.exe -uroot -pziguangruanjian thams_0611 < D:\DB\thams_20180611.sql
---方法二
1、首先建空数据库
mysql>create database thams_0611;
2、导入数据库
(1)选择数据库
mysql>use thams_0611;
(2)设置数据库编码
mysql>set names utf8;
(3)导入数据(注意sql文件的路径)
mysql> source D:\DB\thams_20180611.sql
SHOW VARIABLES LIKE 'log_%'; 
show binary logs;
show master logs;
show master status;
SHOW BINLOG EVENTS IN 'mysql-bin.000008'; 
show binlog events in 'mysql-bin.000009';
show variables like '%expire_logs_days%';  ---查看日志过期时间,默认0,即永远不过期;
set global expire_logs_days=10;---更改过期时间10天,修改后触发后自动清理,触发条件有(1.binlog大小超过max_binlog_size 2.手动执行flush logs 3.重新启动)
flush logs; ---手动切换日志;
---查看数据库大小
select TABLE_SCHEMA,
       concat(truncate(sum(data_length) / 1024 / 1024, 2), ' MB') as data_size,
       concat(truncate(sum(index_length) / 1024 / 1024, 2), 'MB') as index_size
  from information_schema.tables
 group by TABLE_SCHEMA
 order by data_length desc;
---查询单个库中所有表磁盘占用大小
select TABLE_NAME,
       concat(truncate(data_length / 1024 / 1024, 2), ' MB') as data_size,
       concat(truncate(index_length / 1024 / 1024, 2), ' MB') as index_size
  from information_schema.tables
 where TABLE_SCHEMA = 'thams_0613'
 group by TABLE_NAME
 order by data_length desc;
---查询单个库中所有表磁盘占用大小
select table_name,
       (data_length / 1024 / 1024) as data_mb,
       (index_length / 1024 / 1024) as index_mb,
       ((data_length + index_length) / 1024 / 1024) as all_mb,
       table_rows
  from information_schema.tables
 where table_schema = 'thams_0613'
 order by data_length desc;
---SHOW DATABASES;
SELECT schema_name FROM information_schema.schemata;
---SHOW TABLES;
SELECT table_name FROM information_schema.tables WHERE table_schema='THAMS_0613';
desc table_name;
mysql客户端SQLyog连接到mysql服务器时出现下面的问题:
Error No. 1130
Host '*.*.*.*' is not allowed to connect to this MySQL server
没有权限,默认只有localhost权限,授权如下:
grant all privileges on *.* to 'root'@'%' identified by '密码';
flush privileges;
---windows 2012 显示桌面
rundll32.exe shell32.dll,Control_RunDLL desk.cpl,,0
---远程连接mysql
ERROR 1130 (HY000): Host '192.168.70.249' is not allowed to connect to this MySQL server
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql备份
[root@cloud3 ~]# crontab -l
*/1 * * * * ntpdate cn.pool.ntp.org >/dev/null
01 01 * * * /dbbackup/mysql/mysql_bak.sh
[root@cloud3 ~]# cat /dbbackup/mysql/mysql_bak.sh
#!/bin/bash
db_user=root
db_pass="******"
db_time=$(date +"%Y_%m_%d_%H_%M_%S")
/usr/local/mysql/bin/mysqldump -u$db_user -p$db_pass opensys --default-character-set=utf8 --opt --single-transaction --flush-logs > /dbbackup/mysql/opensys_bak_$db_time.sql
/usr/local/mysql/bin/mysqldump -u$db_user -p$db_pass 3gol --default-character-set=utf8 --opt --single-transaction --flush-logs > /dbbackup/mysql/3gol_bak_$db_time.sql
/usr/local/mysql/bin/mysqldump -u$db_user -p$db_pass openauth --default-character-set=utf8 --opt --single-transaction --flush-logs > /dbbackup/mysql/openauth_bak_$db_time.sql
/usr/local/mysql/bin/mysqldump -u$db_user -p$db_pass pyqapp --default-character-set=utf8 --opt --single-transaction --flush-logs > /dbbackup/mysql/pyqapp_bak_$db_time.sql
find /dbbackup/mysql -mtime +10 -name "*.sql" -exec rm -rf {} \;
---CentOS下MySQL忘记root密码解决方法
[root@jumpserver001 mysql]# mysql -uroot -p123
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
# vim /etc/my.cnf 
在[mysqld]的段中加上一句:skip-grant-tables 
重启mariadb
[root@jumpserver001 mysql]# systemctl restart mariadb
[root@jumpserver001 mysql]# mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]> update mysql.user set Password=password('1') where User='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0
MariaDB [mysql]> flush privileges ; 
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> select user,host,password from user;
+------------+---------------+-------------------------------------------+
| user       | host          | password                                  |
+------------+---------------+-------------------------------------------+
| root       | localhost     | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| root       | jumpserver001 | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| root       | 127.0.0.1     | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| root       | ::1           | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| jumpserver | 127.0.0.1     | *AE74BF45BD4590B7A8BFE37AE47DAEDD238A3BF8 |
+------------+---------------+-------------------------------------------+
5 rows in set (0.00 sec)

###三十二:AIX###

---查看操作系统信息
oslevel -s或bootinfo -r
---查看补丁信息
oslevel -rq
---系统补安装方法
smitty update_all
---检查当前系统参数及调整
lsattr -El sys0
export.UTF-8
export
lsdev -Cc disk
bootinfo -s hdisk1
查看空间
lspv :查看系统硬盘
lspv hdisk# :查看硬盘hdisk#的空间分配情况
lsvg :查看系统VG
lsvg vg_name : 查看VG的空间分配情况
lsvg -l vg_name: 查看VG中LV分配情况以及对应的文件系统
df -k :查看文件系统使用情况。
bootinfo -K(位数)
pmcycles -m(cpu格式)
topas:(Network 按 n 键可关闭此区域。再按一次 n 键就会显示所有网络接口活动的报告摘要)
       (如果有多个CPU,按c键两次就可显示CPU列表。仅按c键一次会关闭此区域)
   (Disk:反映磁盘使用率的状况的区域,按 d 键可关闭这个区域。再按一次 d 键就会显示所有物理磁盘活动的报告摘要)
df -g
du -g
ping -s 20000 192.168.1.1
export JAVA_HOME=../../
nohup /ncapp/home/startup.sh &
nohup表示不挂断,即关闭终端,脚本依然运行,&表示以后台方式运行,如果不加前面的nohup,关闭终端,脚本会结束运行
查看告警日志
errpt
命令
set -o vi
AIX软件管理
系统管理界面工具(System Management Interface Tool,SMIT)是一种交互式的应用程序,它几乎可以用来简化 AIX? 系统管理中各方面的工作。
#smit
列出软件包(包括子包)名:
# installp -l -d ./X11.base
  Fileset Name                Level                     I/U Q Content
  ====================================================================
  X11.base.common             5.1.0.0                    I  N usr
#   AIXwindows Runtime Common Directories
  X11.base.lib                5.1.0.0                    I  N usr
#   AIXwindows Runtime Libraries
  X11.base.rte                5.1.0.0                    I  N usr,root
#   AIXwindows Runtime Environment
  X11.base.smt                5.1.0.0                    I  N usr,root
#   AIXwindows Runtime Shared Memory Transport
安装软件:
#installp -d X11.base all (全部安装)
#installp -d X11.base X11.base.common (只安装其中的X11.base.common)
卸软件载:
#installp -u X11.base.common 
列出已安装的软件:
#lslpp -l
列出安装的软件的文件清单:
#lslpp -f openssh.base.client 
查询文件属于哪个包:
#lslpp -w /usr/bin/ssh
  File                                        Fileset               Type
  ----------------------------------------------------------------------------
  /usr/bin/ssh                                openssh.base.client   File
AIX服务命令 
列出服务
#lssrc -a (全部)
#lssrc -s sshd (sshd状态)
停止服务
#stopsrc -s sshd
启动服务
#startsrc -s sshd

###三十三:Linux###

1.1 查看CPU个数
# 总核数 = 物理CPU个数 X 每颗物理CPU的核数 
# 总逻辑CPU数 = 物理CPU个数 X 每颗物理CPU的核数 X 超线程数
# 查看物理CPU个数
cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l
# 查看每个物理CPU中core的个数(即核数)
cat /proc/cpuinfo| grep "cpu cores"| uniq
# 查看逻辑CPU的个数
cat /proc/cpuinfo| grep "processor"| wc -l
# 查看CPU型号
# cat /proc/cpuinfo | grep 'model name' |uniq
model name : Intel(R) Xeon(R) CPU E5630 @ 2.53GHz
model name : Intel(R) Xeon(R) CPU E5-2683 v4 @ 2.10GHz
model name : Intel(R) Xeon(R) CPU E5-2609 v4 @ 1.70GHz
[root@bogon ~]# cat /proc/cpuinfo | grep "physical id" | uniq | wc -l
1
[root@bogon ~]# cat /proc/cpuinfo | grep "cpu cores" | uniq
cpu cores : 2
[root@bogon ~]# cat /proc/cpuinfo | grep 'model name' |uniq
model name : Intel(R) Pentium(R) CPU G3220 @ 3.00GHz
Linux查看物理CPU个数、核数、逻辑CPU个数
# 总核数 = 物理CPU个数 X 每颗物理CPU的核数 
# 总逻辑CPU数 = 物理CPU个数 X 每颗物理CPU的核数 X 超线程数
# 查看物理CPU个数
cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l
# 查看每个物理CPU中core的个数(即核数)
cat /proc/cpuinfo| grep "cpu cores"| uniq
# 查看逻辑CPU的个数
cat /proc/cpuinfo| grep "processor"| wc -l
复制代码
 查看CPU信息(型号)
cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
###查询CPU
---windows cpu核数,逻辑CPU
wmic---cpu get *    ---NumberOfCores(核数),NumberOfLogicalProcessors(逻辑CPU),如果核数=1/2逻辑CPU,是双核超线程
systeminfo ---物理CPU个数
devmgmt.msc
---任务管理器--性能
cat /proc/cpuinfo
dmesg|grep -i 'physical processor'
dmidecode | grep "Product Name"
Intel(R) Atom(TM) CPU N2800   @ 1.86GHz
通过free -m查看当前内存
可用内存:Available memory=free+buffers+cached,即31068=759+66+30243
已用内存:Used memory=used-buffers-cached,即1030=31339-66-30243
dmidecode | grep "Product Name"
ip addr 和 ifconfig 
---更换目录名称
/home目录更改为/kingdee 
df -h
umount /dev/mapper/VolGroup-lv_home
mount /dev/mapper/VolGroup-lv_home /kingdee 
cat /etc/fstab 
---ntp
date 
ntpdate ali.yun.org 
date 
service ip6tables stop   #停止IPV6服务
chkconfig ip6tables off  #禁止IPV6开机启动
service yum-updatesd stop   #关闭系统自动更新
chkconfig yum-updatesd off  #禁止开启启动
---centos7
[root@linux-node1 ~]# setenforce 0  # 可以设置配置文件永久关闭
[root@linux-node1 ~]# systemctl stop firewalld.service
[root@linux-node1 ~]# systemctl disable firewalld.service
[root@linux-node1 ~]# iptables -F
[root@linux-node1 ~]# iptables-save
1、方法一使用hostnamectl命令
更改主机名
[root@bogon ~]# hostnamectl set-hostname  NMServer-7.test.com
方法二:修改配置文件  /etc/hostname 保存退出
[root@bogon ~]# vi /etc/hostname 
---centos6.7 
[root@bogon ~]# chkconfig --list iptables
iptables        0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@bogon ~]# chkconfig iptables off
[root@bogon ~]# chkconfig --list iptables
iptables        0:off 1:off 2:off 3:off 4:off 5:off 6:off
yum install net-tools
yum install -y ntp
无法启动图形
yum groupinstall "X Window System"
---yum groupinstall "X Window System" --skip-broken
yum group list
yum -y groupinstall "Server with GUI"
###linux中文乱码
那么如何显示中文呢?
1、系统必须安装中文语言包才行
# yum -y groupinstall chinese-support
2、仅仅有语言包还不行,我们得设置相应的字符集
## 临时生效 
# export LANG="zh_CN.UTF-8"    # 设置为中文 
# export LANG="en_US.UTF-8"    # 设置为英文,我比较喜欢这样 export 
  
## 永久生效, 编辑/etc/sysconfig/i18n(最好reboot一下) 
LANG="zh_CN.UTF-8"
  
## 或者,编辑 /etc/profile配置文件,添加如下一行 
export LANG="zh_CN.UTF-8"
---LINUX下安装nmon
wget 
tar -xvfz nmon16e_mpginc.tar.gz
cd nmon16e_mpginc
# 授权运行权限
chmod +x nmon_x86_64_centos7
# 使nmon在任何地方都能运行
mv nmon_x86_64_centos7 /usr/bin/nmon
###windows_linux 格式问题
windows文件上传到linux或unix后格式可能会有问题;
vim xxx.txt 
Esc
:
set ff=unix或set fileformat=unxix
Esc
:
wq
回车
###find
find /u01/ora_arch/ -mtime +10 -name "*.dbf" -exec rm -rf {} \;
 
[oracle@ismorcdb2 ~]$ crontab -l
01 01 * * * /home/oracle/del_arch.sh
[oracle@ismorcdb2 ~]$ 
[oracle@ismorcdb2 ~]$ 
[oracle@ismorcdb2 ~]$ cat /home/oracle/del_arch.sh 
find /u01/ora_arch/ -mtime +10 -name "*.dbf" -exec rm -rf {} \;
[oracle@ismorcdb1 ~]$ crontab -l
00 03 * * * /home/oracle/backup_shell/expdp_ism.sh
[oracle@ismorcdb1 ~]$ cat /home/oracle/backup_shell/expdp_ism.sh 
#!/bin/bash
source ~/.bash_profile
days=`date +%Y%m%d`
expdp ism/ism@pdbism directory=dump_expdp dumpfile=ism_$days.dmp  logfile=ism_$days.log schemas=ism
find /u01/dump_expdp/ -mtime +2 -name "*.dmp" -exec rm -rf {} \;
find /u01/dump_expdp/ -mtime +2 -name "*.log" -exec rm -rf {} \;
find /u01/ora_arch/ -mtime +10 -name "*.dbf" -exec rm -rf {} \;
#!/bin/bash
source ~/.bash_profile
days=`date +%Y%m%d`
expdp ism/ism@pdbism directory=dump_expdp dumpfile=ism_$days.dmp  logfile=ism_$days.log schemas=ism
find /u01/dump_expdp/ -mtime +5 -name "*.dmp" -exec rm -rf {} \;
find /u01/dump_expdp/ -mtime +5 -name "*.log" -exec rm -rf {} \;
find /u01/ora_arch/ -mtime +10 -name "*.dbf" -exec rm -rf {} \;
###远程YUM
1. cd /etc/yum.repos.d
2. mv CentOS-Base.repo CentOS-Base.repo.backup
3. wget 
4. mv CentOS6-Base-163.repo CentOS-Base.repo
5.yum clean all
CentOS 6
https://blog.csdn.net/weixin_42167759/article/details/81143066
wget -O /etc/yum.repos.d/CentOS-Base.repo 
或者
curl -o /etc/yum.repos.d/CentOS-Base.repo 
CentOS 7
wget -O /etc/yum.repos.d/CentOS-Base.repo 
或者
curl -o /etc/yum.repos.d/CentOS-Base.repo 
之后运行yum makecache生成缓存
[root@Oracle11g /]# mount /dev/sr1 /mnt -o loop
[root@Oracle11g /]# mount |grep mnt
/dev/sr1 on /mnt type iso9660 (ro)
/dev/sr1 on /mnt type iso9660 (ro,loop=/dev/loop0)
[root@Oracle11g ~]# cd /etc/yum.repos.d/
[root@Oracle11g yum.repos.d]# cat yum.repo 
[Oralin6u3]
name=local yum
baseurl=file:///mnt
gpgcheck=0
enabled=1
###网卡配置
[root@cjcos01 network-scripts]# cat ifcfg-enp0s3 
TYPE="Ethernet"
PROXY_METHOD="none"
BROWSER_ONLY="no"
BOOTPROTO="none"
DEFROUTE="yes"
IPV4_FAILURE_FATAL="no"
IPV6INIT="yes"
IPV6_AUTOCONF="yes"
IPV6_DEFROUTE="yes"
IPV6_FAILURE_FATAL="no"
IPV6_ADDR_GEN_MODE="stable-privacy"
NAME="enp0s3"
UUID="b0b58151-2738-4a9b-8e49-30341e577a60"
DEVICE="enp0s3"
>
IPADDR="192.168.31.90"
PREFIX="24"
GATEWAY="192.168.31.1"
#GATEWAY="192.168.31.254"
DNS1="8.8.8.8"
#DNS1="192.168.31.1"
IPV6_PRIVACY="no"
###windows 禁ping
进入服务器后 点击 开始——运行 输入命令:
netsh firewall set icmpsetting 8
这样就可以在外部ping到服务器了 非常简单实用!
同样道理,如果想禁止Ping,那运行如下命令即可实现:
netsh firewall set icmpsetting 8 disable


###chenjuchao 20210904 23:00###

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-2790430/,如需转载,请注明出处,否则将追究法律责任。

上一篇: tidb集群部署
请登录后发表评论 登录
全部评论
Oracle ACE、OCMU 用户组成员、Oracle 11g OCM、微信公众号"IT小Chen"

注册时间:2014-08-05

  • 博文量
    642
  • 访问量
    1633122