Oracle基础维护01-常用管理命令总结

概览：

01.Oracle 内存管理
02.Oracle 数据库启动关闭
03.Oracle 参数文件
04.Oracle 控制文件
05.Oracle redo日志文件
06.Oracle undo表空间管理
07.Oracle 普通表空间和数据文件
08.Oracle 临时表空间
09.Oracle 锁管理
10.Oracle用户管理
11.Oracle等待事件

 

详述：

 

01.Oracle 内存管理

1.1 内存自动管理
11g：AMM   10g：ASMM

1.2 查看当前数据库内存设定情况
sys@ZTDR> show parameter memory

NAME                     　　　　　　　　TYPE    　　 VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address         　　 integer     　　0
memory_max_target             　　　  big integer 　　1584M
memory_target                 　　　  big integer 　　1584M
shared_memory_address             　 integer     　　 0
sys@ZTDR> show parameter sga;

NAME                     　　　　　　　　TYPE     　　　　VALUE
------------------------------------ ----------- ------------------------------
lock_sga                 　　　　　　　　boolean     　　FALSE
pre_page_sga                 　　　　　 boolean     　　FALSE
sga_max_size                 　　　　　 big integer 　　1584M
sga_target                 　　　　　　 big integer 　　0
sys@ZTDR> show parameter pga;

NAME                     　　　　　　   TYPE     　　VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big         integer 0

1.3 修改sga大小（待补充）

1.4 修改pga大小（待补充）

1.5 查看当前数据库内存设定情况（待补充）

 

02.Oracle 数据库启动关闭

##2.1 Oracle监听的启动
SQL>!lsnrctl start

sys@ZTDR> !lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-OCT-2018 21:40:13

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/OraLinux6/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=OraLinux6.9)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=OraLinux6.9)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                07-OCT-2018 21:40:13
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/OraLinux6/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=OraLinux6.9)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully


##2.2 Oracle数据库正常启动
SQL>startup

sys@ZTDR>startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size            2253784 bytes
Variable Size         1023413288 bytes
Database Buffers      620756992 bytes
Redo Buffers            7094272 bytes
Database mounted.
Database opened.


##2.3 Oracle数据库正常关闭
SQL>shutdown immediate

sys@ZTDR> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

 

03.Oracle 参数文件（待完善）

3.1参数文件pfile和spfile互相切换

需要重启库才可以切换。
startup默认以spfile参数文件启动库；startup pfile='$ORACLE_HOME/dbs/initztdr.ora'以pfile参数文件启动库。


3.2通过spfile创建pfile

create pfile from spfile;



3.3通过pfile创建spfile
#使用pfile启动Oracle

#通过pfile创建spfile
create spfile from pfile;

 

04.Oracle 控制文件

4.1 备份控制文件到trace

alter database backup controlfile to trace as '/tmp/control.ctl';

4.2 重建控制文件
根据/tmp/control.ctl文件的内容提取重建控制文件的sql。

 

05.Oracle redo日志文件

5.1 查询日志文件信息状态
select * from v$logfile
select * from v$log5.2 日志切换
alter system switch logfile ---强制手工切换日志
alter sytem archive log current5.3 添加日志组及日志组成员
alter database add logfile group 6 ('/u01/app/oracle/oradata/ztdr/redo06.log') size 100m;
alter database add logfile member '/u01/app/oracle/oradata/ztdr/redo62.log' to group 6;

5.4 删除日志组及日志组成员
alter database drop logfile group 6;
alter database drop logfile member '/u01/app/oracle/oradata/ztdr/redo62.log';

5.5 添加日志组镜像
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/ztdr/redo06.log') size 100m;
alter database add standby logfile member '/u01/app/oracle/oradata/ztdr/redo62.log' to group 6;

5.6 删除日志组镜像
alter database drop standby logfile group 6;
alter database drop standby logfile member '/u01/app/oracle/oradata/ztdr/redo62.log'5.7 清空日志文件
select * from v$log;
select * from v$logfile;
alter database clear logfile group 1;
alter database clear logfile group 3;
alter database clear logifle group n
alter database clear unarchived logfile group n

5.8 日志文件不一致或者丢失了怎么处理？
步骤：
alter system set "_allow_resetlogs_corruption" = true scope= spfile;
recover database using bakcup controlfile;
alter database open resetlogs;
shutdown immediate;
startup mount;
alter database open resetlogs;
alter system reset "_allow_resetlogs_corruption" scope= spfile sid= '*'5.9 修改redo文件大小(待详细完善)
删除老日志组，新建日志组。

 

06.Oracle undo表空间管理

6.1 新建undo表空间
create undo tablespace undo_eric datafile '/u01/app/oracle/oradata/ztdr/undo_eric01.dbf' size 3G autoextend off;

6.2 切换undo表空间
set linesize 150
show parameter undo
alter system set undo_tablespace=undo_eric;
show parameter undo

6.3 ORACLE在线切换undo表空间其他注意事项：

6.4 undo表空间的真实使用情况：
SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024 "Size(MB)", COUNT(*)  
FROM DBA_UNDO_EXTENTS GROUP BY STATUS; 

 

07.Oracle 普通表空间和数据文件

7.1 新建表空间
create tablespace dbs_d_eric datafile '/u01/app/oracle/oradata/ztdr/dbs_d_eric01.dbf' size 2G autoextend off;

7.2 表空间增加数据文件
alter tablespace dbs_d_eric add datafile '/u01/app/oracle/oradata/ztdr/dbs_d_eric02.dbf' size 3G autoextend off;

7.3 表空间删除数据文件
alter tablespace dbs_d_eric drop datafile '/u01/app/oracle/oradata/ztdr/dbs_d_eric02.dbf';

7.4 修改数据文件的大小
alter database  datafile '/u01/app/oracle/oradata/ztdr/dbs_d_eric01.dbf' resize 3G;
 
7.5 删除表空间及其包含的数据文件
drop tablespace dbs_d_eric including contents and datafiles;

 

08.Oracle 临时表空间

## 8.1 查看默认临时表空间
col property_value for a30
set linesize 180
select property_value, property_name from database_properties where property_name like '%TABLESPACE';

## 8.2 新建临时表空间
create temporary tablespace temp_eric tempfile '/home/oradata/JYZHAO/datafile/temp_eric01.tmp' size 3G;


## 8.3 临时表空间增加临时文件
alter tablespace temp_eric add tempfile '/home/oradata/JYZHAO/datafile/temp_eric02.tmp' size 3G;


## 8.4 切换数据库临时表空间为temp_eric
alter database default temporary tablespace temp_eric;

 

09.Oracle 锁管理

## 9.1 确定锁进程的sid

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;

## 9.2 通过sid获得它的sql，看是哪一条sql导致锁的占用

SELECT sql_text
  FROM v$sql t1, v$session t2
 WHERE t1.address = t2.sql_address
   AND t2.sid = &sid;  --&sid 就是上一条sql中查到的sid

## 9.3 如有记录则表示有lock，记录下SID和serial# ，执行下面的sql，即可解除锁

alter system kill session 'SID,serial#';

 

10.Oracle用户管理

## 10.1 新建用户
create user eric identified by eric default tablespace dbs_d_eric;

## 10.2.1 授权
grant connect, resource to eric;
 
grant dba to eric;

## 10.2.2 查看用户当前拥有的角色：
select * from user_role_privs;


## 10.3 密码永不过期
alter profile default limit PASSWORD_LIFE_TIME unlimited;

## 10.4 错误密码不锁定
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

 

11.Oracle等待事件

select event, count(1) from v$session group by event order by 2 desc;
select event, WAIT_CLASS, count(1) from v$session group by event, WAIT_CLASS order by 2,3 desc;

##Oracle 常见等待事件
针对Oracle数据库的等待事件，常见的分类方法是简单的分为2大类，一是非空闲等待，二是空闲等待。 一般来讲，对于空闲等待类的等待事件不需要过多关注

log file sync
log buffer space
log file switch
log file parallel write
buffer busy waits
free buffer waits
library cache pin
library cache lock
latch events
direct path read and direct path read temp
direct path write and direct path write temp
db file sequential read
db file scattered read
read by other session
cursor: pin S wait on X
SQL*Net Events