odb无法启动数据库恢复 mongo数据库删除集合恢复 mongodb数据库误删除恢复

客户名称保密

数据类型 mongodb 4.x

数据容量 15 GB

故障类型用户误操作导致集合被删除，客户用数据恢复软件已经无法找到原始文件。

修复结果使用极佳mongo数据库恢复工具从客户分区收集全部集合json数据完成恢复,恢复完整度接近100%

耗费工时 3 小时

深圳极佳数据救援中心友情提醒：重要数据一定要勤备份，遇到数据丢失数据损坏等问题，要第

一时间联系专业人士。对于各类主流数据库，我们可以做最底层的数据恢复及数据修复，对于数据库

的某些特定故障我们保证数据库100%原模原样恢复，无论多大的数据库都立等可取。

ASH与AWR性能调优脚本

下面列出了一些我认为对性能调优非常有用的SQL查询。这些查询利用了Active Session History V$视图来获取当前的性能状况，同时还会使用DBA_HIST_* AWR历史表来获取过去某一时间段内的性能数据。

我想补充一点：这些查询语句是我根据从同事和同行那里获得的SQL脚本自行定制的。如果我的做法侵犯了任何人的版权，请告诉我，我会立即删除相关内容。另外，如果有人有类似且实用的开源脚本愿意分享给大家使用，也请发给我，我会把它们添加到这个页面上。

最近的等待事件排行

col EVENT format a60 

select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/

自实例启动以来的最高等待事件数

col event format a60

select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and n.wait_class !='Idle'
and n.wait_class = (select wait_class from v$session_wait_class
 where wait_class !='Idle'
 group by wait_class having
sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
where wait_class !='Idle'
group by (wait_class)))
order by 3;

当前正在等待的用户列表

col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120

select s.sid, s.username, se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by se.wait_time;

查找特定时间间隔内的主要数据库等待事件

首先确定所涉时段的快照ID值。

In this example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;

set verify off
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between &ssnapid and &esnapid
group by active_session_history.event
order by 2 desc)
where rownum

Top CPU Consuming SQL During A Certain Time Period

Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM

select * from (
select
SQL_ID,
 sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum

Which Database Objects Experienced the Most Number of Waits in the Past One Hour

set linesize 120
col event format a40
col object_name format a40

select * from 
(
  select dba_objects.object_name,
 dba_objects.object_type,
active_session_history.event,
 sum(active_session_history.wait_time +
  active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
    dba_objects
 where 
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
 group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
 order by 4 desc)
where rownum < 6;

按物理读取量排序的顶级细分领域

col segment_name format a20
col owner format a10 
select segment_name,object_type,total_physical_reads
 from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
 where statistic_name in ('physical reads')
 order by total_physical_reads desc)
 where rownum

过去一小时中最常用的5条SQL语句

select * from (
select active_session_history.sql_id,
 dba_users.username,
 sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
 dba_users
where 
active_session_history.sample_time between sysdate -  1/24  and sysdate
  and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
 group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
 order by 4 desc )
where rownum

过去一天中I/O量最大的SQL查询

select * from 
(
SELECT /*+LEADING(x h) USE_NL(h)*/ 
       h.sql_id
,      SUM(10) ash_secs
FROM   dba_hist_snapshot x
,      dba_hist_active_sess_history h
WHERE   x.begin_interval_time > sysdate -1
AND    h.SNAP_id = X.SNAP_id
AND    h.dbid = x.dbid
AND    h.instance_number = x.instance_number
AND    h.event in  ('db file sequential read','db file scattered read')
GROUP BY h.sql_id
ORDER BY ash_secs desc )
where rownum

过去一天中CPU占用最高的查询

select * from (
select 
	SQL_ID, 
	sum(CPU_TIME_DELTA), 
	sum(DISK_READS_DELTA),
	count(*)
from 
	DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
 s.snap_id = a.snap_id
 and s.begin_interval_time > sysdate -1
	group by 
	SQL_ID
order by 
	sum(CPU_TIME_DELTA) desc)
where rownum

查询在特定报告时间点上排名最高的SQL语句是什么。

首先确定所涉时段的快照ID值。

在那个示例中，我们需要找出2012年11月14日晚上10点至11点这一时段的SNAP_ID。

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;

select * from
 (
select
 sql.sql_id c1,
sql.buffer_gets_delta c2,
sql.disk_reads_delta c3,
sql.iowait_delta c4
 from
dba_hist_sqlstat sql,
dba_hist_snapshot s
 where
 s.snap_id = sql.snap_id
and
 s.snap_id= &snapid
 order by
 c3 desc)
 where rownum < 6 
/

分析特定的SQL ID，查看过去一天的趋势。

select
 s.snap_id,
 to_char(s.begin_interval_time,'HH24:MI') c1,
 sql.executions_delta c2,
 sql.buffer_gets_delta c3,
 sql.disk_reads_delta c4,
 sql.iowait_delta c5,
sql.cpu_time_delta c6,
 sql.elapsed_time_delta c7
 from
 dba_hist_sqlstat sql,
 dba_hist_snapshot s
 where
 s.snap_id = sql.snap_id
 and s.begin_interval_time > sysdate -1
 and
sql.sql_id='&sqlid'
 order by c7
 /

对于同一个SQL ID，我们是否有多个计划哈希值？如果是这样的话，那么可能是变更后的计划导致了性能下降。

select 
  SQL_ID 
, PLAN_HASH_VALUE 
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS
from DBA_HIST_SQLSTAT 
where SQL_ID in (
'&sqlid') 
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;

根据ADDM的建议，过去一周内的前5大查询词

/*
Top 10 SQL_ID's for the last 7 days as identified by ADDM
from DBA_ADVISOR_RECOMMENDATIONS and dba_advisor_log
*/

col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit" 
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b 
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7 
and  a.dbid = (select dbid from v$database) 
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) 
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24') 
and b.advisor_name = 'ADDM' 
and b.task_id = l.task_id 
and l.status = 'COMPLETED') 
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;

Half Sized Blog Element (Single Author Style)

Half Sized Blog Element (Multi Author Style)

mongodb无法启动数据库恢复

oracle ASH与AWR性能调优脚本

mongodb无法启动数据库恢复

oracle ASH与AWR性能调优脚本

Full Sized Blog Element (Big Preview Pic)

mongodb无法启动数据库恢复

oracle ASH与AWR性能调优脚本

ASH与AWR性能调优脚本

最近的等待事件排行

自实例启动以来的最高等待事件数

当前正在等待的用户列表

查找特定时间间隔内的主要数据库等待事件

Top CPU Consuming SQL During A Certain Time Period

Which Database Objects Experienced the Most Number of Waits in the Past One Hour

按物理读取量排序的顶级细分领域

过去一小时中最常用的5条SQL语句

过去一天中I/O量最大的SQL查询

过去一天中CPU占用最高的查询

查询在特定报告时间点上排名最高的SQL语句是什么。

分析特定的SQL ID，查看过去一天的趋势。

对于同一个SQL ID，我们是否有多个计划哈希值？如果是这样的话，那么可能是变更后的计划导致了性能下降。

根据ADDM的建议，过去一周内的前5大查询词

服务范围 / 方式 / 愿景

本站技术标签

技术、商务、投稿、侵权等联系！