分类 ORACLE 下的文章

11g

  SELECT a.snap_id,
       c.tablespace_name ts_name,
       to_char(to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd hh24:mi') rtime,
       round(a.tablespace_size * c.block_size / 1024 / 1024, 2) ts_size_mb,
       round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb,
       round((a.tablespace_size - a.tablespace_usedsize) * c.block_size / 1024 / 1024,
             2) ts_free_mb,
       round(a.tablespace_usedsize / a.tablespace_size * 100, 2) pct_used
  FROM dba_hist_tbspc_space_usage a, 
       (SELECT tablespace_id,
               substr(rtime, 1, 10) rtime,
               max(snap_id) snap_id
          FROM dba_hist_tbspc_space_usage nb
         group by tablespace_id, substr(rtime, 1, 10)) b,
         dba_tablespaces c,
         v$tablespace d
 where a.snap_id = b.snap_id
   and a.tablespace_id = b.tablespace_id
   and a.tablespace_id=d.TS#
   and d.NAME=c.tablespace_name  
     and  to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') >=sysdate-30
   order by a.tablespace_id,to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') desc;

12c

SELECT a.snap_id,
       a.con_id,
       e.name pdbname,
       c.tablespace_name ts_name,
       to_char(to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd hh24:mi') rtime,
       round(a.tablespace_size * c.block_size / 1024 / 1024, 2) ts_size_mb,
       round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb,
       round((a.tablespace_size - a.tablespace_usedsize) * c.block_size / 1024 / 1024,
             2) ts_free_mb,
       round(a.tablespace_usedsize / a.tablespace_size * 100, 2) pct_used
  FROM cdb_hist_tbspc_space_usage a, 
       (SELECT tablespace_id,
               nb.con_id,
               substr(rtime, 1, 10) rtime,
               max(snap_id) snap_id
          FROM dba_hist_tbspc_space_usage nb
         group by tablespace_id, nb.con_id,substr(rtime, 1, 10)) b,
         cdb_tablespaces c,
         v$tablespace d,
         V$CONTAINERS e
 where a.snap_id = b.snap_id
   and a.tablespace_id = b.tablespace_id
   and a.con_id=b.con_id
   and a.con_id=c.con_id
   and a.con_id=d.con_id
   and a.con_id=e.con_id
   and a.tablespace_id=d.TS#
   and d.NAME=c.tablespace_name
     and  to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') >=sysdate-30
   order by a.CON_ID,a.tablespace_id,to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') desc;

解决tomcat数据库连接池不释放连接数问题

weixin_34367257

于 2016-11-18 10:03:00 发布

3880
收藏 4
文章标签: java 数据库
版权
为什么80%的码农都做不了架构师?>>> hot3.png

一:场景

tomcat数据源连接oracle数据库,不断出现连接数爆满,导致数据库挂掉

二:解决办法

参考文章 http://www.tuicool.com/articles/AbUNj27

也就是在tomcat context.xml配置文件中,添加三个属性

maxWait="2000" //单位毫秒

removeAbandoned="true"

removeAbandonedTimeout="180" //单位秒

即增加连接池回收机制,活动连接,在超过时间内,没有使用,会被自动回收

转载于:https://my.oschina.net/baishi/blog/790138

有一个需求, 要将AWR生成报告的时间由默认的1小时改为10分钟,默认报错快照7天改为4天。

SQL> select * from dba_hist_wr_control;

  DBID            SNAP_INTERVAL              RETENTION            TOPNSQL

1657191512 +00000 00:60:00.0 +00007 00:00:00.0 DEFAULT

---INTERVAL表示每隔多少分钟收集一次AWR信息,retention表示保留时间多少分钟。

SQL> BEGIN
2 dbms_workload_repository.modify_snapshot_settings(INTERVAL =>60,retention => 4 24 60);
3 END;
4 /

PL/SQL 过程已成功完成。

SQL> select * from dba_hist_wr_control;

  DBID            SNAP_INTERVAL              RETENTION            TOPNSQL

1657191512 +00000 00:10:00.0 +00004 00:00:00.0 DEFAULT
————————————————
版权声明:本文为CSDN博主「深圳gg」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/stevendbaguo/article/details/8596653

Oracle数据库有时候不小心删除掉数据,想查询这些数据,或者恢复数据,就可以使用带有as of子句的select语句进行闪回查询。

闪回查询过去某个时间段的数据
select * from tb
as of timestamp to_timestamp('2017-11-07 08:50:00', 'yyyy-mm-dd hh:mi:ss')
where code = '001';

闪回恢复
insert into tb(
select * from tb
as of timestamp to_timestamp('2017-11-07 08:50:00', 'yyyy-mm-dd hh:mi:ss')
where code = '001'

Oracle ADG 切换场景及具体切换步骤 原创
mob604756f191852021-04-23 08:39:48
文章标签java文章分类Java编程语言阅读数1177

■ 计划内切换

(1)检查主库

select switchover_status from v$database;

SWITCHOVER_STATUS

SESSIONS ACTIVE

if [ switchover_status = "SESSIONS ACTIVE" | switchover_status = "TO STANDBY" ];

then { go to next step }

else { cancel switchover and check database }

(2)主库切为备库

alter database commit to switchover to physical standby with session shutdown;

shutdown immediate;

startup mount;

(3)检查备库

select switchover_status from v$database;

SWITCHOVER_STATUS

TO PRIMARY

if [ switchover_status = "SESSIONS ACTIVE" | switchover_status = "TO PRIMARY" ];

then { go to next step }

else { cancel switchover and check database }

(4)备库切为主库

alter database commit to switchover to PRIMARY with session shutdown;

(5)原主库恢复日志应用

alter database recover managed standby database using current logfile disconnect from session;

■ 计划外切换

一、主备库日志没有缺口的场景

(1)备库检查日志缺口

select thread#,low_sequence#,high_sequence# from v$archive_gap;

未选定行

(2)备库停止日志接受并完成已收日志应用

alter database recover managed standby database cancel;

alter database recover managed standby database finish;

(3)检查备库状态

select switchover_status from v$database;

SWITCHOVER_STATUS

TO PRIMARY

if [ switchover_status = "SESSIONS ACTIVE" | switchover_status = "TO PRIMARY" ];

then { go to next step }

else { cancel switchover and check database }

(4)备库切为主库

alter database commit to switchover to PRIMARY with session shutdown;

alter database open;

二、主库可以打开到 mount 状态

(1)将主库日志刷到备库

alter system flush redo to target_db_name;

(2)备库停止日志接受并完成已收日志应用

alter database recover managed standby database cancel;

alter database recover managed standby database finish;

(3)检查备库状态

select switchover_status from v$database;

SWITCHOVER_STATUS

TO PRIMARY

if [ switchover_status = "SESSIONS ACTIVE" | switchover_status = "TO PRIMARY" ];

then { go to next step }

else { cancel switchover and check database }

(4)备库切为主库

alter database commit to switchover to PRIMARY with session shutdown;

alter database open;

三、主备库间日志缺口无法纠正

(1)强制激活备库

alter database activate physical standby database;

alter database open;