oracle dba笔记,oracle DBA 笔记

当前位置:我的异常网» 数据库 » oracle DBA 笔记

oracle DBA 笔记

www.myexceptions.net  网友分享于：2013-09-29  浏览：9次

oracle DBA 札记

--dba 博客

蒙昭良

http://space.itpub.net/12778571

(一)处理等待sql

--1

select sid,username,blocking_session,blocking_session_status,blocking_instance,event,wait_time from

v$session where username='MZ_CRM'

--2

select sql_text from v$session a , v$sqltext_with_newlines b where decode

(a.sql_hash_value,0,prev_hash_value,sql_hash_value)=b.hash_value

and a.sid=&sid order by piece

--3

select t2.username,t2.sid,t2.serial#,t2.logon_time  from v$locked_object t1,v$session t2

where t1.session_id=t2.sid order by t2.logon_time

--4

alter session kill '&sid,&serial#';

(二)解决数据文件达到最大值

--1

select tablespace_name 表空间,file_name 数据文件名,round(bytes/1024/1024/1024) 实际大小GB,round

(maxbytes/1024/1024/1024) 最大值GB

from dba_data_files

--2

select * from dba_tablespace_usage_metrics

--3 添加数据文件

alter tablespace users add datafile '/u01/LSDBNEW/LSDBNEW/datafile/o1_mf_macc_100ycfby1_.dbf'

size 20G autoextend on maxsize unlimited;

--4 修改最大值为不限制

alter database datafile '/u01/LSDBNEW/LSDBNEW/datafile/o1_mf_macc_100ycfby1_.dbf'

autoextend on maxsize unlimited;

(三)临时表空间过大导致磁盘空间不足

--1 查询当前默认临时空间

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

--2 查看temp是否达到最大值

select file_name,tablespace_name,bytes/1024/1024/1024,autoextensible from dba_temp_files

select * from dba_tablespace_usage_metrics

--3 linux 查看磁盘空间情况

$df -lh

--4 新建临时表空间

create temporary tablespace temp02

tempfile '/u01/LSDBNEW/LSDBNEW/datafile/temp02.dbf'

size 20G autoextend on maxsize unlimited;

--5 替换原临时空间 temp

alter database default temporary tablespace temp02;

--6 查询是否已经更改过来

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

--7 删除temp 之前，先kill 掉运行在temp 中的sql ，这样的sql 大多为排序语句 如：

select se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value)) as space,

tablespace,segtype,sql_text

from v$sort_usage su , v$parameter p , v$session se, v$sql s

where p.name='db_block_size'

and su.session_addr=se.saddr

and s.hash_value=su.sqlhash

and s.address=su.SQLADDR

order by se.username,se.sid

--8 kill

alter system kill session 'sid,serial#';

--9 删除 temp

drop tablespace temp including contens and datafiles;

-- 提示这样操作不需要重启就能解决临时表空间写满的问题

(四)还原表空间过大导致磁盘空间不足

--原因

a.有较大的事物量让oracle Undo 自动扩展，产生过度占用磁盘空间的情况

b.有较大的事物没有收缩或者没有提交所致

c.还原空间用户存放数据库历史数据 ， 当执行 DML 是执行的旧数据会写入还原空间 10已经丢弃回滚段，完全使用还

原表空间

--1 查磁盘空间

$df -lh

--2 查看所有表空间占用率

select * from dba_tablespace_usage_metrics

select a.tablespace_name,

round((a.maxbytes/1024/1024),2) "sum MB",

round((a.bytes/1024/1024),2) "datafile MB",

round(((a.bytes-b.bytes)/1024/1024),2) "used MB",

round(((a.maxbytes-a.bytes+b.bytes)/1024/1024),2) "free MB",

round(((a.bytes-b.bytes)/a.maxbytes)*100,2) "percent_used"

from (select tablespace_name,sum(bytes) bytes,sum(maxbytes) maxbytes

from dba_data_files where maxbytes!=0

group by tablespace_name) a,

(select tablespace_name,sum(bytes) bytes,max(bytes) largest

from dba_free_space

group by tablespace_name) b

where a.tablespace_name=b.tablespace_name

order by  ((a.bytes - b.bytes) / a.maxbytes) desc

--3 查询还原表空间路径文件

select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like '%UNDO%'

--4 检查还原表空间状态 【查询数据行数表示 待 回滚的对象数】

select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize

--5 创建新的还原表空间

create undo tablespace undotbs2

datafile '/u01/LSDBNEW/LSDBNEW/datafile/o1_mf_undotbs1_81pnfs29_.dbf' size 30G autoextend on maxsize

unlimited;

--6 切换成新的还原空间

alter system set undo_tablespace=undotbs2 scope=both ;

--7 验证是否替换成功

show parameter undo;

--8 等待旧的还原表空间变成脱机状态  undo segement offline

select t.segment_name , t.tablespace_name,t.segment_id,t.status from dba_rollback_segs t

--9 上面查询 undo1状态均为 下线状态则删除旧还原空间

drop tablespace undotbs1 including contents and datafiles;

--10 再次查看磁盘空间

$df -lh

--数据四种关闭命令

--1 做检查点关闭数据文件，无论是否有事务和连接均可关闭，最常用

shutdown immediate ;

--2 不做检查点，启动是实例自动恢复， shutdown immediate 不能关闭可以使用 ， 快速,很少使用

shutdown abort ;

--3 做检查点，等待事务处理完毕,少用

shutdown transactional;

--4 做检查点，等待用户停止使用和事务停止，少用

shutdown normal;

--数据启动的几个状态

--查看当前实例状态

select instance_name,status from v$instance;

--1 nomount  涉及文件：初始文件 spfile 或 pfile 【对重要参数修改时使用，及恢复控制文件时】

startup nomount;

--查看spfile 或 pfile

show paramter spfile; 查找路径  linux : strings spfie; 查看

--2 mount    涉及文件：初始文件找控制文件 control file 【数据库恢复数据时，和数据配置备库，数据备库状态就是 mount 】

--nomount 转至 mount

alter database mount;

--查看控制文件

select name from v$controlfile; 查找路径

--3 open     涉及文件：控制文件找数据文件，重做日志文件【数据库开发访问】

--mount 转至 open

--4 重启数据库

startup force;

alter database open;

--数据文件

select name from v$datafile;

--日志文件

select member from v$logfile;

--操作是否允许远程登录

show paramter remote_login_passwordfile;  exclusive :允许  none 不允许

alter system set remote_login_passwordfile=none scope=spfile; --重启生效 startup force;

--oracle 使用  flashback (回闪)

恢复 表删除数据  删除表 事物前后状态 库恢复 等等问题

--1 数据删除恢复

select t.start_timestamp,

t.commit_timestamp,

t.logon_user,

t.operation,

t.table_name,

t.table_owner,

t.undo_sql

from flashback_transaction_query t where table_name='LIC_0817_01'

--复制 undo_sql 值 plsql 执行 即可

--2 恢复删除表

select * from recyclebin order by droptime desc

flashback table lic_0817_01 to before drop

--3 恢复指定时间表状态

--查询表 10 点状态

select * from lic_0817_01 as of timestamp to_timestamp('2011-4-3 10:00:00','yyyy-mm-dd hh24:mi:ss);

--恢复

flashback table lic_0817_01 to timestamp to_timestamp('2011-4-3 10:00:00','yyyy-mm-dd hh24:mi:ss);

select * from lic_0817_01

--flashback 以undo segment 内容为基础，受undo_retention参数限制要使用flashback必须使用自动撤销管理表空间

--查看

show parameter undo_retention

--默认为900秒即15分钟这个时间不是绝对时间，还是主要受限于undo空间是否足够大，如果空间足够大，undo_retention 值设置更小也能操作， 提示：该空间数据存储以"先进先出"的方式进行

文章评论