数据库常用命令

1.物理删除归档：

ls -lrt | grep "10月  9" | awk '{print $9}' | xargs rm

2.删除过期备份

    过期归档备份：

物理删除归档备份后，从控制文件中删除过期归档：

rman target /

crosscheck archivelog all;  检查控制文件和实际物理文件的差别

delete expired archivelog all;  同步控制文件的信息和实际文件的信息

    过期数据备份：

RMAN>report obsolete; 报告过期备份

RMAN>delete obsolete; 删除过期备份

RMAN>delete noprompt obsolete; 不提示，删除过期的备份集

在执行“delete obsolete”时，报错：“RMAN-06214”

RMAN> crosscheck backup;

在执行如下命令：

RMAN>delete obsolete; 删除过期备份

 

3.为表空间添加数据文件：

alter tablespace 表名 add datafile '/opt/oracle/product/10.2.0/oradata/表名_01.dbf' size 400M autoextend on next 100M maxsize 30000M;

select * from v$database_block_corruption;

dba_data_files

dba_extents

4.查询asm磁盘组状态：

select name, state from v$asm_diskgroup;

查看裸设备和asm磁盘组的对应关系：

select name , path from v$asm_disk_stat;

5.查看表空间：

select file_name , tablespace_name, bytes/1024/1024 ,maxbytes/1024/1024 , increment_by from dba_data_files;

select file_name , tablespace_name, bytes/1024/1024 ,maxbytes/1024/1024 , increment_by from dba_data_files where tablespace_name = tablespace_name;

set linesize 150

设置表空间offline：

alter tablespace tablespace_name offline;

设置数据文件offline：

alter database datafile 'xxxxxxx.dbf' offline;

create pfile = '/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl-20121030.ora' from spfile= '+orcl_data1/orcl/spfileorcl.ora';

startup mount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl1.ora';

6.使用dbv校验：

dbv file='+ORCL_DATA1/orcl/datafile/数据文件名.dbf' userid=user/passwod

7.SQL> alter system set log_archive_dest_1 = 'location=+FLASH_RECOVERY_AREA' sid='*';

SQL> alter system set log_archive_dest_2 = '' sid = '*';

8.杀LOCAL=NO进程前10个：

ps aux | grep oracle| grep -v grep | grep LOCAL=NO | head -10 | xargs kill -9

9.块恢复：

RMAN> backup validate  datafile 39 ;  检查数据文件39是否存在坏块

RMAN> blockrecover datafile 39 block 795 from backupset;

SQL> select * from v$database_block_corruption;

10.查询进程的具体执行时间：

ps -eo pid,tty,user,comm,stime,etime

 

select sum from dba_free_space

dba_datafiles;

v$sql_area

11.addmrpti调优：

sqlplus / as sysdba;

@?/rdbms/admin/addmrpti.sql
 
12.kill session

select sql_id,sid,PADDR,serial# from v$session where sql_id = 'b6ywaqwcnyjgg';

alter system kill session '66,33177';

13.数据库TRACE文件报错：  解决：隐藏参数：alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile; 需要重启实例 load大对象导致，新版本改至50m

[oracle@localhost bdump]$ more /opt/oracle/admin/orcl/udump/orcl_ora_20534.trc
/opt/oracle/admin/orcl/udump/orcl_ora_20534.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/oracle/product/10.2.0
System name: Linux
Node name: localhost.localdomain
Release: 2.6.9-42.ELlargesmp
Version: #1 SMP Wed Jul 12 23:46:39 EDT 2006
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 20534, image: oracleorcl@localhost.localdomain

*** SERVICE NAME:(orcl) 2012-11-18 08:00:04.418
*** SESSION ID:(158.697) 2012-11-18 08:00:04.418
Memory Notification: Library Cache Object loaded into SGA
Heap size 3673K exceeds notification threshold (2048K)-------------重点
LIBRARY OBJECT HANDLE: handle=15b8d3d60 mutex=0x15b8d3e90(0)
name=XXXXXXXXXXX

hash=8dfdc3aaecd6d3a82a017595ee8d466f timestamp=05-31-2012 23:56:31
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0741-0749 lock=X pin=X latch#=5 hpc=0014 hlc=0014
lwt=0x15b8d3e08[0x15b8d3e08,0x15b8d3e08] ltm=0x15b8d3e18[0x15b8d3e18,0x15b8d3e18]
pwt=0x15b8d3dd0[0x15b8d3dd0,0x15b8d3dd0] ptm=0x15b8d3de0[0x15b8d3de0,0x15b8d3de0]
ref=0x15b8d3e38[0x15b8d3e38,0x15b8d3e38] lnd=0x15b8d3e50[0x15b8c3588,0x15b8d7570]
  LOCK OWNERS:
      lock     user  session count mode flags
  -------- -------- -------- ----- ---- ------------------------
  153aebac8 158178440 158178440     1 X    [00]
  153b793d8 158178440 158178440     0 N    [4000]
  PIN OWNERS:
       pin     user  session     lock count mode mask
  -------- -------- -------- -------- ----- ---- ----

解决：隐藏参数：alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile; 需要重启实例 load大对象导致，新版本改至50m

 

14.select sql_id,sid,PADDR,serial# from v$session where sql_id = 'b6ywaqwcnyjgg';

15.重建索引：

select index_name , status , table_name from user_indexes where status = 'UNUSABLE'; 查看不可用索引

alter index index_name rebuild;