ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle笔记

oracle笔记

原创 Linux操作系统 作者:U-Lee 时间:2009-07-04 14:57:02 0 删除 编辑
1.F:\oracle\product\10.2.0\db_1\install\portlist.ini 可以看到isqlplus,enterprise manager得端口。

日志文件

2.alter database add logfile group 4 ('','') size 100m;
3.alter database add logfile member '' size 100 to group 4;
4.alter database drop logfile '';
5.alter database rename file '' to '';
6.alter database clear logfile '';
7.alter database clear unarchived logfile group 1;
  recouver database until cancel;
  alter database open resetlogs;
8.alter system switch logfile;

控制文件和数据文件

9.alter database datafile '' resize 100m;
10.alter tablespace offline;(online,read only,read write)
11.alter database backup controlfile to trace;alter database backup controlfile to 目录;


12.select a.username, a.sid, a.serial#, b.id1 
   from v$session a, v$lock b
   where b.id1 in
                (select distinct e.id1 from v$session d,v$lock e where d.lockwait = e.kaddr)
   and a.sid = b.sid
   and b.sid = 0;
   alter system kill session 'sid,serial#';

数据库启动

13.nomount 只启动实例不启动数据库。用于控制文件出错,创建控制文件。
14.mount   启动数据库,但不打开数据库。用户修改数据库运行状态,数据库恢复。alter database archivelog;alter database noarchivelog;
15.alter database open read only;只能读,以scotte用户登陆。
16.alter database open read write;
17.startup restrict 只有具有restricted session权限的用户才能连上数据库

清理磁盘碎片

18. select * from dba_free_space;
    alter tablespace test coalesce;
19. analyze table test compute statistics;

表空间增加数据文件

20. alter tablespace test add datafile '' size 50m autoextend on next 1m maxsize unlimited;
21. alter tablespace test rename datafile '' to '';

大文件表空间

22. create bigfile tablespace test datafile '' size 50G extent management local segment space mangement auto;
23. alter tablespace test resize 80G;

回退段管理

24. v$undostat

大回退段使用过程

25.创建大回退段create rollback segment rbs1 tablespace tablespacename storge(initial 100m next 50m maxextents unlimited);
               alter rollbacksegment rbs1 online;
   conn hospital/test 
   set transaction use rollback segment rbs1;
   执行语句delete ....
   食物完成后删除回滚段、表空间
    alter rollback segment rbs1 offline;
    drop rollback segment rbs1;
    drop tablespace rbs1 including contents;

管理文件按参数

26. db_create_file_dest 设置数据文件的默认存储目录
27. db_create_online_log_dest_n 设置控制文件和日志文件的默认存储目录

监视用户

28. 常用数据字典
    v$session v$open_cursor v$session_wait v$process v$sesstat v$sess_io

归档
9i :alter database archivelog;alter system archivelog_dest='';archive log start;
29. log_archive_dest,log_archive_duplex_dest
30. log_archive_ 
31.在ORACLE 10G 中设置自动归档有以下三种方法:1、在使用DBCA安装数据库时 , 配置为自动归档模式,既在设置FLASHBACK中定义LOG_ARCHIVE_DEST_1,即可在建库后实现自动归档;2、在数据库启动后,SHUTDOWN,修改INIT.ORA,定义LOG_ARCHIVE_DEST_1,然后STARTUP PFILE=‘ORACLE_HOME/ADMIN/DBID/PFILE/INIT.ORA',也可以修改为自动归档模式,然后记得重建SPFILE;3、在OPEN 状态下设置LOG_ARCHIVE_DEST_1,SHUTDOWN ,STARTUP MOUNT,ALTER DATABASE ARCHIVE LOG;ATLER DATABASE OPEN。即可

linux中手动启动oracle
32.startup,lsnrctl start,emctl start dbconsole/emctl status dbconsole

33.解释执行计划explain plan set statement_id='test1' for SELECT a.soctermbegin,

FLASHBACK
34.flashback table test to before drop;
35.flashback table test to timestamp to_timestamp('2008-02-15 05:02:00','yyyy-mm-dd hh:mi:ss');使用该语句前必须启动航移动功能。alter table test enable row movement;

联机备份表空间

36.alter tablespace tablespacename begin backup; host copy '' to ''; alter tablespace tablespacename end backup;
13810493142

添加临时数据文件
37.alter tablespace temp add datafile '' size 100M reuse;

索引的类型
   B-树索引
   位图索引
   HASH索引
   索引编排表
   反转键索引
   基于函数的索引
   分区索引
   本地和全局索引

用户管理备份
38.热备数据库(非一致性备份)alter database begin backup; copy ' '  '' ;alter database end backup;备份完成后归档当前日志以保证数据文件备份的同步性。alter system archive log current;
39.热备表空间 查询表空间的数据文件 select * from dba_data_files where tablespace_name = ''; alter tablespace begin backup;  拷贝数据文件; alter tablespace end backup;
   冷备表空间 alter tablespace tablespacename offline; 拷贝数据文件; alter tablespace tablespacename online;
40,alter database [add/drop] logfile
   alter database ]add/drop] logfile member 
   alter database [add/drop] logfile group
   alter database  [noarchivelog/archivelog]\
   alter database rename file '' to '';
   alter tablespace [add/rename] datafile
   alter tablespace [read write/read only]
   drop tablespace
41.备份控制文件 创建副本alter database backup controlfile to '' reuse; 
                 备份到跟踪文件alter database backup controlfile to trace;
42.查看已经归档的日志 v$archived_log
43.指定恢复位置 recover from '' datafile 4;
44.自动选择恢复日志3种方法 1.根据提示操作。2.set autorecover on ;recover datafile 5; 3.recover automatic datafile 5;
45.查看需要恢复的数据文件 v$recover_file
45.查看恢复时需要的归档日志 v$recovery_log
46.如果没有数据文件备份,恢复时先创建数据文件。create tablespae user01 datafile '' size 1M; 
   恢复:alter database create datafile '';
   如果磁盘损坏:alter database create datafile '' as '';

不完全恢复数据库

47.scn:select current_scn from v$database;
    recover database until change 12345;
48.基于取消恢复 recover database until cancel;
49.基于备份控制文件的恢复 recover database until time (yyyy-mm-dd hh:mi:ss) using backup controlfile;
50.非当前日志文件损坏时: alter database clear unarchived logfile group 1;也可以在数据库关闭状态下删除日志,重新建立。
51.当前日志文件损坏时:用recover database until cancel恢复。然后用resetlogs打开数据库。
52.当不完全恢复数据库或用resetlogs打开数据库会导致临时文件不能用,重新添加临时文件
   alter tablespace temp add tempfile '' size 10M reuse;

RMAN

53. 不用恢复目录连接目标数据库 rman target sys/wjq@orcl nocatalog
54. 用恢复目录连接到目标数据库 rman target sys/wjq@orcl catalog rman/rman@rcat
55. 连接到主库和辅助库 raman target sys/oracle@orcl auxiliary sys/wjq@standby

56. 完全备份数据库 backup database format='e:\rmanbackup\%d_%s.bak'; sql 'alter system archive log current';
57. open 状态下backup database会备份除临时表空间外的其它所有表空间,如果不想备份某个表空间,用一下命令
    configure exclude for tablespace user03;
58. 表空间备份集 backup tablespace user03 format='';
59. 数据文件备份集 backup datafile 5 formate='';
60. 控制文件备份集 backup current controfile format='';
61. backup datafile 5 format='' include current controlfile;
62. spfile备份集 backup spfile format='';
63. 归档日志备份集 backup format='' archivelog from time='sysdate-1' until time='sysdate';
64. 并行化备份集 configure device type disk parallelism 3;
                 backup database format='';
                 configure device type disk clear;
65. 建立多重化备份 backup copies 3 tablespace users format='d:\bak1\%N_%s.dbf','d:\bak2\%N_%s.dbf','d:\bak3\%N_%s.dbf';
    多冲备份放在相同目录下必须带有%c
66. 备份备份集,备份编号为32的备份集 backup backupset 32 format='';
67. 建立多个备份片(必须指定%p) configure channel device type sbt maxpiecesize 4G;
                               backup device type sbt format='' database;
68.建立压缩备份集 backup as compressed backupset tablespace users format='';
69.备份数据文件同时备份归档日志文件 backup format '' tablespace user01 plus archivelog;
70.加标记bakcup tablespace users format='' tag='users';
71.增量备份 backup incremental level= 0 format='' tablespace users;
72.积累备份 backup incremental level= 2 cumulative database include current controlfile format='' ;

rman 恢复
73. v$recover_file查找需要恢复的数据文件
74. 不完全恢复
     run{startup force mount; set until time '2008-10-01 17:00:34'; restore database; recover database;sql 'alter database open resetlogs';}

DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
sys.dbms_backup_restore.restoreSetDatafile;
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>26,toname=>'/opt/ora9/oradata/nethall/NCWEB1.dbf');
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/home/oracle/job/fullbak/nethall/fullnethall_lev0_20090628_3340_1.bak', params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;



75.


rman管理
1.v$backup_datafile 显示数据文件和控制文件的备份集信息
2.v$bakcup_redolog 显示归档日志备份信息




查找低效sql语句

查找等待最多的事件

Select count(*), event
from v$session_wait 
where event not in ('smon timer','pmon timer','rdbms ipc message','SQL*Net message from client') 
group by event order by 1 desc;

进一步找出等待原因
Select count(*), p1, p2, p3 
from v$session_wait 
where event = ‘buffer busy waits’ group by p1,p2,p3;

通过以下SQL可以找到具体的object.

Select owner, segment_name, segment_type from dba_extents where file_id=P1 and P2 between block_id and block_id+blocks;

P1,P2是上面v$session_wait查出的具体的值



查找bad sql的方法: 

SELECT MODULE,sql_text,round(buffer_gets/EXECUTIONS,1) "ratio(%)",
       buffer_gets,disk_reads,parse_calls,executions,hash_value,OPTIMIZER_MODE,disk_reads/executions "average"
  FROM v$sqlarea
WHERE buffer_gets > 500000 AND EXECUTIONS>100
ORDER BY 10 DESC;

select * from (select buffer_gets, sql_text 
from v$sqlarea 
where buffer_gets >500000 
order by buffer_gets desc) where rownum<=30;
 

执行次数多的SQL 


select sql_text,executions,elapsed_time from 
(select sql_text,executions,elapsed_time from v$sqlarea order by executions desc) 
where rownum<81;
 

读硬盘多的SQL 


select sql_text,disk_reads from 
(select sql_text,disk_reads from v$sqlarea order by disk_reads desc) 
where rownum<21;
 

排序多的SQL 


select sql_text,sorts from 
(select sql_text,sorts from v$sqlarea order by sorts desc) 
where rownum<21;
 

分析的次数太多,执行的次数太少,要用绑变量的方法来写sql 


set pagesize 600; 
set linesize 120; 
select substr(sql_text,1,80) "sql", 
count(*), sum(executions) "totexecs" 
from v$sqlarea 
where executions < 5 
group by substr(sql_text,1,80) 
having count(*) > 30 
order by 2;

某个进程当前正在执行的sql语句
SELECT   /*+ ORDERED */
         sql_text
    FROM v$sqltext a
   WHERE (a.hash_value, a.address) IN (
            SELECT DECODE (sql_hash_value,
                           0, prev_hash_value,
                           sql_hash_value
                          ),
                   DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
              FROM v$session b
             WHERE b.paddr = (SELECT addr
                                FROM v$process c
                               WHERE c.spid = '14786'))
ORDER BY piece ASC;

游标的观察 


set pages 300; 
select sum(a.value), b.name 
from v$sesstat a, v$statname b 
where a.statistiC# = b.statistic# 
and b.name = 'opened cursors current' 
group by b.name; 
select count(0) from v$open_cursor; 
select user_name,sql_text,count(0) from v$open_cursor 
group by user_name,sql_text having count(0)>30;
 

查看当前用户&username执行的SQL 


select sql_text from v$sqltext_with_newlines 
where (hash_value,address) in 
(select sql_hash_value,
sql_address from v$session 
where username='&username') 
order by address,piece


//进程对应的sql
select sql_text,spid,v$session.program,process  from 
v$sqlarea,v$session,v$process
where v$sqlarea.address=v$session.sql_address
and v$sqlarea.hash_value=v$session.sql_hash_value
and v$session.paddr=v$process.addr
and v$process.spid in (PID);




76. alter session set sql_trace=true;

77.查锁表的语句
   SELECT /*+ rule */ s.username,
          decode(l.type,'TM','TABLE LOCK',
                 'TX','ROW LOCK',
                  NULL) LOCK_LEVEL,
          o.owner,o.object_name,o.object_type,
          s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
    FROM v$session s,v$lock l,dba_objects o
    WHERE l.sid = s.sid
    AND l.id1 = o.object_id(+)
    AND s.username is NOT Null;


   alter system kill session 'sid,SERIAL#';

   4、锁与等待,如果发生了锁等待,我们可能更想知道是谁锁了表而引起谁的等待 
以下的语句可以查询到谁锁了表,而谁在等待。


SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name, 
o.owner,o.object_name,o.object_type,s.sid,s.serial# 
FROM v$locked_object l,dba_objects o,v$session s 
WHERE l.object_id=o.object_id 
AND l.session_id=s.sid 
ORDER BY o.object_id,xidusn DESC 


78.解决ora-12541错误,把tnsname中的service_name=orcl改成sid=orcl

79.在做大量排序的时候如果PGA不够用就会使用到temp表空间进行排序。

   select * from v$sort_segment
   可以查看哪些段正在排序

   select * from v$sot_usage
   查看哪些session在排序。关联v$sqlarea可以找到哪些sql在执行排序操作。

80. 让索引暂时失效 ALTER   INDEX   idx   UNUSABLE;  
81. 察看索引使用情况
    alter index index_name monitoring usage; 
    select * from v$object_usage;
    alter index index_name nomonitoring usage;
82.分析表的行迁移
   执行rdbms/admin下边的utlchain.sql生成chained_rows表
   利用select 'analyze table'|| table_name|| 'list chained rows into chaind_rows;' from user_tables命令追个分析表
   然后在chained_rows表中察看有多少行连接和迁移行。
   select table_name,count(*) from chained_rows group by table_name;

83.查看系统所有段的有关buffer busy waits事件的统计:

   SELECT * 
     FROM v$segment_statistics s 
    WHERE s.statistic_name = 'buffer busy waits' 
      AND s.owner <> 'SYS'

84.STATSPACK查看系统dbwr最繁忙时间
   select distinct to_char(snap_time,'day'),
                   avg(a.value/b.value)
   from   stats$sysstat a,
          stats$sysstat b,
          stats$snapshot sn
   where  sn.snap_id = a.snap_id
   and    sn.snap_id = b.snap_id
   and    a.name = 'summed dirty queue length'
   and    b.name = 'write requests'
   and    a.value>0
   and    b.value>0
   group by to_char(snap_time,'day');

85.重建临时表空间和undo表空间
   create temporary tablespace  nchall_TEMP2 TEMPFILE '/opt/backup/nchall_temp2.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT  640K    MAXSIZE UNLIMITED;

   alter database default  temporary tablespace  nchall_temp2;

   drop tablespace nchall_temp including contents and datafiles;

   create temporary tablespace  nchall_TEMP TEMPFILE '/opt/backup/nchall_temp.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT  640K    MAXSIZE UNLIMITED; 

   alter database default  temporary tablespace  nchall_temp;


   drop tablespace nchall_temp2 including contents and datafiles;


   alter user nchall temporary tablespace  nchall_temp;

   ----------------------------------------------------------------------------------------------------
   -- 创建备用undo表空间
   SQL> create undo tablespace UNDOTBS2 datafile '/ora_disk/rewin/UNDOTBS2.dbf' size 1000m;
   需要设置一下自动增长??

   Tablespace created

   -- 切换undo表空间

   SQL> alter system set undo_tablespace=UNDOTBS2 scope=spfile;

   SQL> shutdown immediate

   - drop原表空间
   drop tablespace UNDOTBS1 including contents and datafiles;

   -- 创建原undo表空间  
   SQL> create undo tablespace UNDOTBS1 datafile '/ora_disk/rewin/UNDOTBS1.dbf' size 1000m;

   Tablespace created

   -- 切换undo表空间
   SQL> alter system set undo_tablespace=UNDOTBS1 scope=spfile;

   System altered.

   -- 关闭重起并把备用undo表空间drop
   SQL> shutdown immediate
   /
   SQL> startup 
   /
   SQL> drop tablespace UNDOTBS2 including contents and datafiles;

   有关缩小表空间的方法2:
   注意在使用OEM查看表空间时要看清是否只有一个dbf文件.
   alter database datafile '/u03/oradata/undotbs01.dbf' resize 2000M
---------------------------------------------------------------------------------------------

86.查看临时表空间信息
   
   SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
2),
'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC

87.查看隐含参数
   SELECT ksppinm, ksppstvl, ksppdesc  
     FROM x$ksppi x, x$ksppcv y  
    WHERE x.indx = y.indx  
      AND translate(ksppinm,'_','#') like '#%'
      and KSPPINM like '%%logging';

88.查看回滚和提交次数
   select * from v$syssate where name in ('user commits','transaction rollbacks');

89.查看占用某个过程的session

  SELECT DISTINCT '(' || s.sid || ')  -  ' || username AS "(session) - username"
FROM V$SESSION s, sys.x$kglob o, sys.x$kglpn p
WHERE upper(o.kglnaobj) LIKE upper('%p_test%')
AND p.kglpnhdl = o.kglhdadr
AND s.SADDR = p.kglpnuse;

 

(session) - username

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

(11)  -  TEST

(19)  -  SYS
 


 


9i性能优化概述

常用视图
1.dba_segment,dba_tables,dba_tab_columns
2.dba_indexes,dba_ind_columns,index_stats
3.dba_clusters
4.dba_tab_histograms
5.index_histogram,dba_tab_histograms
6.library cache命中率通常大于99%,如果小于可以调节shared_pool_size的大小
  select (sum(pins-reloads))/sum(pins) "lib cache" from v$librarycache";
7.sga大于128时分配sga大小要是16m的倍数,小于128时要是4m的倍数

监测索引
8.alter index index_name monitoring usage;
  select * from v$object_usage;



创建分区表
CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)   
  2 (PARTITION P1 VALUES LESS THAN (TO_DATE(’2004-7-1’, ’YYYY-MM-DD’)),   
  3 PARTITION P2 VALUES LESS THAN (TO_DATE(’2005-1-1’, ’YYYY-MM-DD’)),   
  4 PARTITION P3 VALUES LESS THAN (TO_DATE(’2005-7-1’, ’YYYY-MM-DD’)),   
  5 PARTITION P4 VALUES LESS THAN (MAXVALUE))  
  6 AS SELECT ID, TIME FROM T;




90.解决statspack的负数
   

   应该是restart database .


   不过,更常见的做法是,自己update stats$sysstat 表

   在statspack作完snap之后,运行
   UPDATE stats$sql_summary
   SET buffer_gets = (buffer_gets + POWER (2, 32))
   WHERE snap_id > last_snap_id AND buffer_gets < 0;
   commit;

   UPDATE stats$sql_summary
   SET cpu_time = (cpu_time + POWER (2, 32))
   WHERE snap_id > last_snap_id AND cpu_time < 0;
   commit;

   UPDATE stats$sql_summary
   SET disk_reads = (disk_reads + POWER (2, 32))
   WHERE snap_id > last_snap_id AND disk_reads < 0;

   update stats$sysstat 
   set value = (value + POWER (2, 32)) 
   where snap_id > last_snap_id AND value < 0;

   其中的last_snap_id 是值上次运行过本语句时的snap_id

91. set transaction use rollback segment rollback_segment;

92.alter table tb_name move tablespace tbs_name lob (col_lob1,col_lob2) store as      (tablesapce tbs_name);

93. analyze table t compute statistics for table for all indexes for all indexed columns;

    execute dbms_stats.gather_table_stats(ownname => 'DB315WEB',tabname => 'INFO_CLASS', estimate_percent => 100, block_sample => false,method_opt => 'FOR ALL COLUMNS SIZE 10 ', granularity => 'ALL',cascade => true);


94.日志切换时间
   select b.recid,to_char(b.first_time,'yyyy-MM-dd hh24:mi:ss') startime,
   a.recid,to_char(a.first_time,'yyyy-MM-dd hh24:mi:ss') 
   end_time,round(((a.first_time-b.first_time)*24)*60,2) minutes from v$log_history a,
   v$log_history b where a.recid=b.recid+1
   order by a.first_time

95.ALTER TABLE T STORAGE (BUFFER_POOL KEEP);
-------------------------------------------------------------------------------------------------------

96. 等待

select spid from v$process where addr in
(select paddr from v$session where sid in(select sid from v$session_wait where event='latch free'));


SELECT latch#, name, gets, misses, sleeps
FROM v$latch
WHERE sleeps>0
ORDER BY sleeps;


SELECT addr, latch#, gets, misses, sleeps
FROM v$latch_children  
WHERE sleeps>0          
and latch# = 97  
ORDER BY sleeps desc;


select distinct a.owner,a.segment_name,a.segment_type,tablespace_name from 
dba_extents a,
(select dbarfil,dbablk 
from x$bh 
where hladdr in
(select addr 
from (select addr 
from v$latch_children 
order by sleeps desc) 
where rownum < 5)) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk;


select sql_text,spid,v$session.program,process  from 
v$sqlarea,v$session,v$process
where v$sqlarea.address=v$session.sql_address
and v$sqlarea.hash_value=v$session.sql_hash_value
and v$session.paddr=v$process.addr
and v$process.spid in ('3886','2466','3006');


------------------------------------------------------------------------
97.
select s.opname,
       s.target,
       s.MESSAGE,
       s.START_TIME,
       s.LAST_UPDATE_TIME,
       t.SQL_TEXT
  from v$session_longops s, v$sql t
 where s.SQL_ADDRESS = t.ADDRESS

SELECT   se.SID, se.serial#, pr.spid, se.username, se.status, se.terminal,
         se.program, se.module, se.sql_address, st.event, st.p1text,
         si.block_gets, si.consistent_gets, si.physical_reads,
         si.block_changes
    FROM v$session se, v$session_wait st, v$sess_io si, v$process pr
   WHERE st.SID = se.SID
     AND st.SID = si.SID
     AND se.paddr = pr.addr
     AND se.SID > 6
     AND st.wait_time = 0
     AND st.event NOT LIKE '%SQL%'
ORDER BY physical_reads DESC


98    buffer busy wait

select b.file_id, b.file_name, a.countfrom   x$kcbfwait a, dba_data_files bwhere  a.indx   = b.file_id – 1and    a.count  > 0order by a.count;

--查找等待块类型
       SELECT 'segment Header' CLASS,
              a.Segment_Type,
              a.Segment_Name,
              a.Partition_Name
         FROM Dba_Segments   a,
              V$session_Wait b
        WHERE a.Header_File = b.P1
          AND a.Header_Block = b.P2
          AND b.Event = 'buffer busy waits'
       UNION
       SELECT 'freelist Groups' CLASS,
              a.Segment_Type,
              a.Segment_Name,
              a.Partition_Name
         FROM Dba_Segments   a,
              V$session_Wait b
        WHERE b.P2 BETWEEN a.Header_Block + 1 AND (a.Header_Block + a.Freelist_Groups)
          AND a.Header_File = b.P1
          AND a.Freelist_Groups > 1
          AND b.Event = 'buffer busy waits'
       UNION
       SELECT a.Segment_Type || ' Block' CLASS,
              a.Segment_Type,
              a.Segment_Name,
              a.Partition_Name
         FROM Dba_Extents    a,
              V$session_Wait b
        WHERE b.P2 BETWEEN a.Block_Id AND a.Block_Id + a.Blocks - 1
          AND a.File_Id = b.P1
          AND b.Event = 'buffer busy waits'
          AND NOT EXISTS (SELECT 1
                            FROM Dba_Segments
                           WHERE Header_File = b.P1
                             AND Header_Block = b.P2);

       ◆ 带有原因码130的数据块(类#1)争用
          如果buffer busy waits的等待事件主要集中在数据块(类#1)上,并且原因码130,则
       表明应用程序运行在同一时刻查询相同数据集的多个会话,采用如下三件事最小化问题:
          §减少并发级别或该表在运行现成直接内分区工作的方法。
          §优化SQL语句,减少物理读取和逻辑读取的数量。
          §增加freeLists和freeList Groups的数量。

       ◆ 带有原因码220的数据块(类#1)争用
          多个会话同时在相同的对象上DML。采用如下三件事最小化问题:
          §减少并发级别或改变划分部分的方法。
          §减少块中行的数量。
          §在另一个具有较小块尺寸的表空间中重新构建对象(Oracle 9i或以上版本)。
          
          可以使用较大的PCTFREE重新构建表或索引。可以使用命令改变表以最小化每个块的最
          小行数:
          ALTER TABLE table_name MINIMIZE RECORDS_PER_BLOCK;
          从Oracle 9i开始,可以在另外一个具有较小的块尺寸的表空间中移动或重新构建对象。
          虽然这些动作可以最小化buffer busy waits问题,但是他们无疑将增加全表扫描时间
          和磁盘空间利用率。常言道,世上没有免费的午餐。
          
       ◆ 数据段头(类#4)的争用
          如果buffer busy waits的等待时间主要集中在数据段头(即表或索引段头,并且不是
       插销段头)上,这意味着数据库中的一些表或索引具有高段头活动。如下解决问题:
          §增加已经确定对象的进程FreeLists和FreeList Groups的数量。
          §确保PctFree和PctUsed之间内的间隙不会太小。
          §确保下一个区尺寸不会太小。
          如果不希望混浠FreeLists和FreeList Groups,可以依靠自动段空间管理(Automatic
       Segment Space Management,ASSM)特性,以分散从插入语句中引入的数据(9i特性)。
                 
       ◆ 撤消段都(类#17)的争用
          如果buffer busy waits等待时间主要集中在撤消段头上,这表明数据库中的回滚段
       过少,或者他们的尺寸太小,从而造成对段头的频繁更新。如果在Oracle 9i中引入的系
       统管理撤消,就不需要处理这种问题,因为Oracle将根据需要增加额外的撤消段。
       
       ◆ 撤消块的争用(类#18)
          如果buffer busy waits等待时间主要集中在撤消块上,这通常意味着多个并发会话同
       时查询更新的数据。当应用程序可以在不同的时间内查询和DML时,这种问题就不会存在。
       
       ◆ 系统级诊断
          --文件等待次数
          SELECT b.File_Id,
                 b.File_Name,
                 a.COUNT
            FROM X$kcbfwait     a,
                 Dba_Data_Files b
           WHERE a.Indx = b.File_Id-1
             AND a.COUNT > 0
           ORDER BY a.COUNT; 



99. alter system set events '10046 trace name context forever,level 12';
    alter system set events '10046 trace name context off';

100.收集统计信息
    exec dbms_stats.gather_schema_stats(ownname=>'<用户名>',DEGREE=>CPU数目);
    如有多个用户,修改<用户名>,重复执行该过程。

101. 有关回滚段
     select a.NAME, b.EXTENTS, b.RSSIZE,b.XACTS,b.WAITS,b.GETS,b.OPTSIZE,b.STATUS
     from v$rollname a,v$rollstat b
     where a.USN = b.USN

     假如XACTS(活动事务)大于1,表示回滚段有争用,需要增加回滚段数目。

     假如WAITS大于1,也表示需要增加回滚段数目

     重建回滚段:   
      1、使回滚段离线:alter   rollback   segment   rbs01   offline;   
      2、删除回滚段:drop   rollback   segment   rbso01;   
      3、建立一个更大的回滚段:       
        create   public   rollback   segment   rbs01   
        tablespace   rbs   
        storage(initial   1024k   
                      next     1024k   
                      minextents     20   
                      maxextents   100   
                      optimal       20480k);   //大小还可调整,你的回滚段表空间必须足够         大,   
    
      4、使回滚段在线:alter   rollback   segment   rbs01   online

102.执行alter index index_name monitoring usage;后,通过查询select index_name,monitoring,used,start_monitoring,end_monitoring
  from v$object_usage;
可以看到你的索引是否有使用到

103.清楚行连接
    analyze table info_table list chained rows;
    create table info_table_temp1 as select * from info_table where 1=0;
    insert into info_table_temp1 select * from info_table where rowid in (select     head_rowid from chained_rows);
    commit;
    delete from info_table where rowid in (select head_rowid from chained_rows );
    commit;
    insert into info_table select * from info_table_temp1 ;
    commit;
    truncate table chained_rows;
    drop table info_table_temp1;

104--求归档日志的切换频率(生产系统可能时间会很长)

select start_recid,start_time,end_recid,end_time,minutes from (select test.*, rownum as rn
from (select b.recid start_recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') start_time,
a.recid end_recid,to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') end_time,round(((a.first_time-b.first_time)*24)*60,2) minutes
from v$log_history a,v$log_history b where a.recid=b.recid+1 and b.first_time > sysdate - 1
order by a.first_time desc) test) y where y.rn < 30

105--求回滚段正在处理的事务

select a.name,b.xacts,c.sid,c.serial#,d.sql_text
from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e
where a.usn=b.usn and b.usn=e.xidusn and c.taddr=e.addr
and c.sql_address=d.address and c.sql_hash_value=d.hash_value order by a.name,c.sid,d.piece;

106 切换standby模式
    alter database set standby database to maximize performance/availability;

107 查看字符集select * from sys.props$ where name='NLS_CHARACTERSET';

108  收回一个段中未使用的数据扩展:ALTER TABLE table_name DEALLOCATE UNUSED; 

109  回收表空间 alter table demo enable row movement; alter table demo shrink space; exec show_space('demo','auto');

110 监听格式

  SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /data/cache1/oracleDB/oracle/product/10.2.0/db_2)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /data/cache1/oracleDB/oracle/product/10.2.0/db_2)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      (ADDRESS = (PROTOCOL = TCP)(HOST = CCN-BJ-3-339)(PORT = 1521))
    )
  )

111. 添加分区
     ALTER TABLE sales ADD PARTITION sales2000_q1 VALUES LESS THAN (TO_DATE(‘2000-04-01’,’YYYY-MM-DD’) TABLESPACE ts_sale2000q1; 

112. alter system flush shared_pool


113. 表空间碎片
     select tablespace_name,sqrt(max(blocks)/sum(blocks))* (100/sqrt(sqrt(count     (blocks)))) FSFI  
     from dba_free_space  group by tablespace_name order by 1

最大值为100,如低于30很多就值得注意了
114. 空间碎片回收
     alter tablespace ... coalesce
     alter table ... deallocate unused;

115. exp-00091

     解决方法:

select * from nls_database_parameters t where t.parameter=’NLS_CHARACTERSET’

or

select * from v$nls_parameters  where parameter=’NLS_CHARACTERSET’;
查询值为:ZHS16CGB231280

然后vi /home/oracle/.bash_profile

export NLS_LANG=american_america.ZHS16CGB231280

source /home/oracle/.bash_profile


116. EXEC DBMS_STATS.GATHER_table_STATS (OWNNAME => 'NCHALL', TABNAME => 'NC_STEEL_INFO',METHOD_OPT => 'FOR ALL INDEXED COLUMNS ');



117. oracle共享服务器bug
     自动down库
     把dispatchers参数设为空,shared_servers修改为0



rac操作
1.启动/停止实例 srvctl start/stop -d orcl -i orcl1,orcl2
2.启动数据库    srvctl start database -d orcl -o open
3.自动策略和手动则略切换srvctl config database -d orcl -a
                        srvctl modify database -d orcl -y manual

4.启动asm实例 srvctl start asm -n clusnode1(rac01)
  停止asm实例 srvctl stop asm -n clusnode1 -o immediate
5.添加有关现有asm实例的ocr数据
  srvctl add asm -n clusnode1 -i +ASM -o /ora/ora10
  srvctl modify instance -d crm -i crm1 -s +asm1
6.禁用asm实例的oc管理
  srvctl modify instance -d crm -i crm1 -s +asm1


表空间恢复
run{
    sql 'alter tablespace users offline for recover';
    restore tablespace users;
    recover tablespace users;
    sql 'alter tablespace users online';
}




等待对象
select a.*
from dba_extents a,
     (select p1,p2 from v$session_wait where event='db file sequential read') b
where a.relative_fno=b.p1
and   b.p2 between a.block_id and a.block_id+a.blocks -1;

等待sql
select sql_text,spid,v$session.program,process  from 
v$sqlarea,v$session,v$process
where v$sqlarea.address=v$session.sql_address
and v$sqlarea.hash_value=v$session.sql_hash_value
and v$session.paddr=v$process.addr
and v$process.spid in (select spid from v$process where addr in
(select paddr from v$session where sid in(select sid from v$session_wait where event='latch free')));



dataguard

1. 主库不传输归档日志到备库。 select * from v$archive_dest发现归档目录监听有问题。修改监听

2. alter报错Shutting down archive processes
    ARCH shutting down
    ARC2: Archival stopped

   查看select * from v$archive_processes;

3. 归档应用
   实时应用 alter database recover managed standby database using current logfile;
   应用归档日志比主库延时 recover managed standby database delay 60;
                   不延时 alter database recover managed standby database nodelay;

4.
? PRIMARY_ROLE  Archive to this destination only when in the primary role.
? STANDBY_ROLE  Archive to this destination only when in the standby role.
? ALL_ROLES  Archive to this destination when in either primary or standby role.
Let's consider two examples to see further how the VALID_FOR attribute works. In the previous section, when creating our physical standby, we set the following parameters:
Orlando Database:
LOG_ARCHIVE_DEST_1=
 'LOCATION=/database/10gDR/Orlando/arch/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=Orlando'
LOG_ARCHIVE_DEST_2=
 'SERVICE=Orlando_hasun1 
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=Nashville'
Nashville Database:
LOG_ARCHIVE_DEST_1=
 'LOCATION=/database/10gDR/Orlando/arch/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=Nashville'
LOG_ARCHIVE_DEST_2=
 'SERVICE=Orlando_hasun1
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=Orlando'
5. switchover
   Step 1.  On the primary database, query the V$DATABASE view to verify that the SWITCHOVER_STATUS column indicates that a switchover is possible.
select switchover_status from v$database;
If SWITCHOVER_STATUS returns a value of TO_STANDBY, everything is good. If the query returns SESSIONS ACTIVE, you should perform. the SWITCHOVER command with the SESSION SHUTDOWN clause.
Step 2.  Convert the primary database into a physical standby.
alter database commit to switchover to physical standby;
If the SWITCHOVER_STATUS column in Step 1 returned SESSIONS ACTIVE, issue the following command:
alter database commit to switchover to physical standby with session shutdown;
Step 3.  Shut down and restart the old primary as a new standby.
shutdown immediate;
startup mount;
Congratulations, you now have two standbys.
Step 4.  When we converted the primary to a standby, we generated a marker in the redo stream and sent that marker to the standby. That marker states that no more redo has been generated. As soon as the standby receives and recovers that marker, it is eligible to become a primary database. Query the SWITCHOVER_STATUS column of V$DATABASE on the standby to ensure that the marker has been recovered and it is ready for the switchover to primary.
select switchover_status from v$database;
If SWITCHOVER_STATUS returns TO_PRIMARY, the marker has been recovered and you can proceed with the SWITCHOVER TO PRIMARY command. If the status is SESSIONS ACTIVE, you should either disconnect active sessions or issue the SWITCHOVER command with the SESSION SHUTDOWN clause. If the status states NOT ALLOWED, the marker has not been received and recovered by the standby, and switchover cannot proceed. 
Step 5.  Convert the standby to a primary database.
alter database commit to switchover to primary;
Or, if the SWITCHOVER_STATUS returned SESSIONS ACTIVE:
alter database commit to switchover to primary with session shutdown;
Step 6.  Shut down and restart the new primary database.
shutdown immediate;
startup;



6. failover
  Step 1.  Resolve any gaps that may exist on the standby. Query the V$ARCHIVE_GAP view on the standby database to identify any gaps. Attempt to resolve the gaps by copying archivelogs from the primary host or from other standbys. Copy any archivelogs to the standby host that have a higher sequence number than the last one to arrive at the standby chosen for failover.
Step 2.  Any archivelogs that have been copied to resolve a gap need to be registered in the standby controlfile. Register the archivelogs with the following command on the standby:
alter database register physical logfile '/database/10gDR/arch/1_217.arc';
Step 3.  Perform. terminal recovery on the standby by issuing managed recovery with the FINISH keyword.  The following command is to be used if you have standby redo logs that are configured and active:
alter database recover managed standby database finish;
If you do not have standby redo logs, or they are not active, you must enter the following command:
alter database recover managed standby database finish skip standby logfile;
Step 4.  Once the terminal recovery command completes, convert the standby into a primary database by entering the following command:
alter database commit to switchover to primary;
Step 5.  Restart the new primary database.
shutdown immediate;
startup;
Step 6.  Back up the new primary database.











 select ' SPFILE in use :'||value from v$parameter 
where name = 'spfile' 
and value is not null ;


select ' PFILE in use '||value from v$parameter 
where name = 'spfile' 
and value is null ;

















sql语句

1.SELECT   EXTRACT(MONTH   FROM   DATE   2005-03-07')   FROM   DUAL   


性能调整

1.alter session set events='10053 trace name context forever, level 1';

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

下一篇: linux笔记
请登录后发表评论 登录
全部评论

注册时间:2008-06-17

  • 博文量
    9
  • 访问量
    16963