Oracle DBA 知识点和操作命令

最新推荐文章于 2021-06-24 20:33:07 发布

aaron8219

最新推荐文章于 2021-06-24 20:33:07 发布

阅读量1.2k

点赞数

分类专栏： Oracle Fundamental 文章标签： Oracle DBA Command Knowledge Basic

Oracle Fundamental 专栏收录该内容

42 篇文章 0 订阅

订阅专栏

查看和修改sga
show parameter sga;
alter system set sga_target=’300M’ scope=both;

Linux下查看二进制文件spfile
strings spfile.ora

动态查看alert日志
tail -f alert.log
tail -10 alert.log

数据库四种状态
查看bdump下的alert_mydb.log
shutdown
nomount状态：只需要初始化参数文件pfile或spfile
mount状态：只需要控制文件，控制文件记录数据文件的路径和信息
open状态：需要日志文件和数据文件

pfile和spfile都丢失的时候启动数据库

通过alert日志文件找出初始化参数，新建初始化参数文件，修改即可。
数据库开归档
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 369098752 bytes
Fixed Size                  1219448 bytes
Variable Size             150996104 bytes
Database Buffers          209715200 bytes
Redo Buffers                7168000 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

查看归档日志
archive log list;

show parameter fast

show parameter sga

alter system set fast_start_mttr_target=600 scope=both;

检查监听
tnsping mydb

密码文件作用：监听方式连接的时候验证sys as sysdba 用户，没有密码文件sys用户无法登录
创建密码文件 orapwd file=oramydb password=oracle

查看数据库默认数据块大小
show parameter db_block_size;

查看表，索引等的大小
desc dba_segment;
select owner,segment_name table_name,sum(bytes)/1024 kb from dba_segments where owner=’SCOTT’ and table_name=’emp’;

静默安装

记录
$ ./runInstaller -record -destinationFile /home/oracle/install_database.rsp

使用
$ ./runInstaller -silent -responseFile /home/oracle/install_database.rsp
跑脚本
#. $ORACLE_BASE/oraInventory/orainstRoot.sh
#. $ORACLE_HOME/Root.sh

进程相关

查看Oracle进程
ps -ef |grep oracle
ps -ef |grep ora_

查看客户端进程
ps -ef |grep LOCAL=NO

[oracle@redhat admin]$ ps -ef |grep LOCAL=NO
oracle   15845     1 0 17:57 ?        00:00:04 oraclemaindb (LOCAL=NO)
oracle   19699     1 0 18:47 ?        00:00:07 oraclemaindb (LOCAL=NO)
oracle   19703     1 0 18:47 ?        00:00:06 oraclemaindb (LOCAL=NO)
oracle   19707     1 0 18:47 ?        00:00:01 oraclemaindb (LOCAL=NO)
oracle   19803     1 0 18:47 ?        00:00:08 oraclemaindb (LOCAL=NO)
oracle   23592     1 0 19:34 ?        00:00:01 oraclemaindb (LOCAL=NO)
oracle   24871     1 0 19:52 ?        00:00:00 oraclemaindb (LOCAL=NO)
oracle   25213     1 0 19:57 ?        00:00:01 oraclemaindb (LOCAL=NO)
oracle   28119 26046 0 20:48 pts/4    00:00:00 grep LOCAL=NO
[oracle@redhat admin]$ kill -9 23592
[oracle@redhat admin]$ ps -ef |grep LOCAL=NO
oracle   15845     1 0 17:57 ?        00:00:04 oraclemaindb (LOCAL=NO)
oracle   19699     1 0 18:47 ?        00:00:07 oraclemaindb (LOCAL=NO)
oracle   19703     1 0 18:47 ?        00:00:06 oraclemaindb (LOCAL=NO)
oracle   19707     1 0 18:47 ?        00:00:01 oraclemaindb (LOCAL=NO)
oracle   19803     1 0 18:47 ?        00:00:08 oraclemaindb (LOCAL=NO)
oracle   24871     1 0 19:52 ?        00:00:00 oraclemaindb (LOCAL=NO)
oracle   25213     1 0 19:57 ?        00:00:01 oraclemaindb (LOCAL=NO)
oracle   28171 26046 0 20:49 pts/4    00:00:00 grep LOCAL=NO

创建索引
create index t_id_x on t(id) tablespace usersx;

dba_tables->pct_free,pct_used;

看block块大小
show parameter db_block_size;

查看当前实例和状态
select instance_name,status from v$instance;
查看数据库库对象

select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

查看数据库的版本
Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)=’Oracle’;

查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode From V$Database;

手动建库
cd $ORACLE_HOME/dbs
cat init.ora |grep -v ^# > initmydb.ora

注释
db_block_buffers = 100
shares_pool_size = 3500000
log_buffer = 32768
修改
control_files = (‘/u02/oradata/mydb/control01.ctl’, ‘/u02/oradata/mydb/control02.crl’)
sga_target=300m
background_dump_dest=/u02/admin/mydb/bdump
core_dump_dest=/u02/admin/mydb/cdump
user_dump_dest=/u02/admin/mydb/udump
undo_management=auto
undo_tablespace=mydbtbs1
创建数据库密码文件
orapwd file=orapwmydb password=oracle

用户和权限相关
相关数据字典 user_users dba_users

显示当前连接用户：show user;

查询拥有sysdba权限的用户
select * from v$pwfile_users;

查看所有用户
SELECT USERNAME FROM DBA_USERS;

查询某用户状态
select username,account_status from dba_users where lower(username)=’adminru’;

查询当前会话或用户可以使用的系统权限
select * from session_privs;
select * from user_sys_privs;
select * from user_tab_privs;

查看当前用户的角色
select * from user_role_privs;

查看数据库的角色
select * from dba_role_privs where grantee=’DBA’;

查看角色包含的权限
select * from dba_sys_privs where grantee=’RESOURCE’;
select * from dba_tab_privs where grantee=’ADMINRU’;

创建用户
create user wang identified by wang
default tablespace users
temporary tablespace temp
quota 0 on system;

create user test identified by test
default tablespace users
temporary tablespace temp
quota unlimited on users;

修改用户

修改用户的默认和临时表空间
alter user scott default tablespace users;
alter user scott temporary tablespace temp;

修改用户磁盘限额：
alter user wbtest quota 100M on users;

修改用户口令：
alter user scott identified by tigerabc;

SQL> select username,password from dba_users where username=’USER001′;

USERNAME PASSWORD
—————————— ——————————
USER001 DD0643D7826752ED

SQL> alter user user001 identified by values ‘DD0643D7826752ED’;
User altered.

查询锁定用户：select username,account_status,lock_date from dba_users;
解锁用户：alter user mdsys account unlock;

删除用户
drop user wbtest cascade;

用户授权

grant connect,resource to wang;
grant drop and table to scott with admin option;
grant select,update,insert,delete on hr.jobs to rjb;
grant global query rewite to scott;
grant dba to scott;

撤销权限
revoke alter tablespace from wang;
revoke create any index from scott;

查看当前用户的缺省表空间
select username,default_tablespace from user_users;

select username,default_tablespace from dba_users where username=’adminru’;

查看所有用户的缺省表空间
select username,default_tablespace from dba_users;
select owner ,table_name,tablespace_name from dba_tables where owner=’adminru’ and table_name=’table_a’;

查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;

查看当前用户拥有的表
select * from tab;

查看角色
set pagesize 100
select * from dba_roles;

查看当前用户的角色
select * from user_role_privs;

权限查询
select * from role_sys_prive;

创建角色
create role teller identified by teller;

角色授权
grant create session,create database link to teller;
grant teller to wang;

角色生效
set role role1;
set role all;
sys用户可以创建数据库,可以关闭数据库 system不可以
查看数据字典：
desc dict;
select table_name from dict where table_name like ‘DBA_TAB%’;

desc dba_tab_privs;

连接相关

用系统管理员，查看当前数据库有几个用户连接：
select username,sid,serial# from v$session;

如果要停某个连接用
alter system kill session ‘sid,serial#’;

如果这命令不行,找它UNIX的进程数

select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;

说明：21是某个连接的sid数

然后用 kill 命令杀此进程号。

查看客户端连接
SQL> set line 100;
SQL> select SID,SERIAL#,MACHINE from v$session;
杀死客户端进程
SQL> alter system kill session ’50,11′;

表和视图相关

查看用户下所有的表
select * from user_tables;

查看名称包含log字符的表
select object_name,object_id from user_objects where instr(object_name,’LOG’)>0;

查看某表的创建时间
select object_name,created from user_objects where object_name=upper(‘&table_name’);

查看某表的大小
select sum(bytes)/(1024*1024) as “size(M)” from user_segments where segment_name=upper(‘&table_name’);

查看放在ORACLE的内存区里的表
select table_name,cache from user_tables where instr(cache,’Y')>0;

数据库审计

相关视图
select table_name from dict where table_name like ‘DBA_AUDIT%’;

DBA_AUDIT_TRAIL
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_OBJECT
DBA_AUDIT_EXISTS
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS

查看审计参数
SHOW PARAMETERS AUDIT

修改参数使生效
alter system set audit_file_dest=’/u02/maindb_audit’ scope=spfile
alter system set audit_trail=db,extended scope=spfile

设置登录审计

审计每一个登录用户：audit session;
连接成功的活动均和审计：audit session whenever successful
连接不成功的活动均和审计：audit session whenever not successful
取消登录审计：noaudit session

SQL记录审计
会话记录，只记录当前会话的第一条
audit select on adminru.table_a;
audit update on adminru.temp_table;
访问时记录，记录所有
audit update on adminru.table_a by access;
audit all on adminru.table_b by access;

取消审计
noaudit update on adminru.table_a;
noaudit all on adminru.table_b;

查询审计
select username,sql_text from dba_audit_trail;

set linesize 128;
set pagesize 100;
col os_username format a10;
col username for a10;
col userhost for a20;
col timestamp for a22;
col owner for a10;
col obj_name for a10;
col sql_text for a30;
select os_username,username,userhost,timestamp,owner,obj_name,sql_text from dba_audit_trail;

查看对象的审计
select owner,obj_name,username,action_name,sql_text from dba_audit_object;

系统时间格式
select sysdate from dual;
show parameter nls_date_format;
alter session set nls_date_format=’yyyy/mm/dd hh24:mi:ss’ 临时

alter system set nls_date_format=’yyyy/mm/dd hh24:mi:ss’ scope=spfile; 永久

查询错误
oerr ora 00600

数据库闪回
从回收站还原表
flashback table temp_table to before drop;

绕过回收站删除
drop table table_name purge;

drop tablespace ts_name including contents;

drop user user_name cascade;
查询回收站
show recyclebin;

select owner,original_name,object_name,type,ts_name,droptime,related,space from dba_recyclebin where can_undrop=’YES’;

select original_name,object_name,type,ts_name,droptime,related,space from user_recyclebin where can_undrop=’YES’;

查询是否启用闪回数据库
select flashback_on from v$database;

启用闪回数据库，mount状态下
alter database flashback on;

闪回数据库
RMAN> flashback database to time = “to_date(’2011-04-22 18:00:00′,’yyyy-mm-dd hh24:mi:ss’)”;

数据库日志文件Logfile
数据字典 v$logfile

日志文件分类：联机重做日志文件(redo log file)和归档日志文件。

查询日志文件
set linesize 180;
col member for a50;
con is_recovery_dest_file for a20;
select * from v$logfile;

查询日志文件组号、大小、状态
select group#,bytes,status,members from v$log;

创建新的日志文件
alter database add logfile member ‘/u01/app/oracle/oradata/maindb/redo02b.log’ to group 2;

删除日志文件组
alter database drop logfile group 1;

删除不活动的组成员
alter database drop logfile member ‘/u01/app/oracle/oradata/maindb/redo01b.log’;

切换日志当前组
alter system switch logfile;

日志由活动改变成不活动
alter system checkpoint;

数据库控制文件Conrolfile
相关数据字典 v$controlfile

查询控制文件
select * from v$controlfile;

备份控制文件
1.备份到二进制文件 alter database backup controlfile to ‘/oracle/backup/control.bkp’;
2.备份到脚本文件 alter database backup controlfile to trace;

恢复控制文件
1.关闭数据库
2.覆盖掉坏的控制文件
3.启动数据库

多路复用是指将同一个控制文件或其他文件的复本保持在多个磁盘上。分spfile和init.ora两种多路复用

init.ora
control_files=’/u01/app/oracle/oradata/mydb/control01.ctl’,'/u01/app/oracle/oradata/mydb/control02.ctl’,

‘/u01/app/oracle/oradata/mydb/control03.ctl’,'/home/oracle/oracle_bak/control01.ctl’,

‘/home/oracle/oracle_bak/control02.ctl’,'/home/oracle/oracle_bak/control02.ctl’

spfile
alter system set control_files=’/u01/app/oracle/oradata/mydb/control01.ctl’,'/u01/app/oracle/oradata/mydb/control02.ctl’,

‘/u01/app/oracle/oradata/mydb/control03.ctl’,'/home/oracle/oracle_bak/control01.ctl’,'/home/oracle/oracle_bak/control02.ctl’,

‘/home/oracle/oracle_bak/control02.ctl’ scope=spfile;

相关数据字典 dba_data_files

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

查看表空间物理文件的名称及大小

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

查看表空间的使用情况

select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;

查看回滚段名称及大小

查看用户对应的临时表空间和默认表空间
select username,temporary_tablespace,default_tablespace from dba_users order by username;

创建表空间，扩展大小128K
create tablespace tbs_1 datafile ‘/u01/app/oracle/oradata/mydb/tbs_1.dbf’ size 100M
extent management local uniform size 128k

更改表空间名
alter tablespace tbs_2 rename to tbs_02;

设置默认表空间
alter database default tablespace users;

删除表空间
drop tablespace tbs_1 [including contents];

增加新数据文件到表空间
alter tablespace tbs_1 add datafile ‘/u01/app/oracle/oradata/mydb/tbs_01.dbf’ size 50M;

删除表空间中无数据的数据文件
alter tablespace tbs_1 drop datafile ‘/u01/app/oracle/oradata/mydb/tbs_01.dbf’;

调整表空间数据文件大小
alter database datafile ‘/u01/app/oracle/oradata/maindb/main_data01′ resize 50000000;
撤销表空间 /UNDO表空间

相关参数：undo_management undo_tablespace undo_retention

查看当前实例的撤销表空间管理方式
show parameter undo_management;

查看当前实例的撤销表空间
show parameter undo_tablespace;

设置默认撤销表空间
alter system set undo_tablespace=undotbs2;

查看当前实例配置的撤销保留时间
show parameter undo_retention;

创建撤销表空间
create undo tablespace undotbs2 datafile ‘/u01/app/oracle/oradata/mydb/undotbs02.dbf’ size 100M;

删除撤销表空间
drop tablespace undotbs2;

查看和修改guarantee
select tablespace_name,status,retention from dba_tablespaces;
alter tablespace undotbs1 retention guarantee;

临时表空间
相关视图 v$tempfile dba_temp_files
oracle数据库如果把临时表空间删了可以自动创建

创建临时表空间
create temporary tablespace temp1 tempfile ‘/u01/app/oracle/oradata/mydb/temp1.dbf’ size 50M;

临时表空间增加数据文件
alter tablespace temp1 add tempfile ‘/u01/app/oracle/oradata/mydb/temp2.dbf’ size 50M;

查看临时表空间信息
select file_name,tablespace_name,bytes from dba_temp_files;

RMAN 备份恢复
备份的速度和主机性能和存储有关
启动RMAN本地连接
rman nocatalog target /

$rman target /
$rman target / nocatalog //没有恢复目录

远程连接
$rman target sys/target_pwd@DB01

查看RMAN的配置
show all;

更改配置参数
configure controlfile autobackup on;

更改备份有效期初始化参数
SQL> alter system set control_file_record_keep_time=30;

System altered.

SQL> show parameter control_file_record_keep_time;
NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 30
SQL>

非DBA用户关闭数据库
rman target scott/scott;
shutdown immediate;

开始备份

run{
allocate channel d1 type disk;
allocate channel d2 type disk;
backup full database format ‘/u02/rman/manindb0201104231955_%u.bak’ tag=maindb_full01;
release channel d1;
release channel d2;
}
演示破坏数据并恢复

删除数据文件
$rm -rf *.dbf
数据恢复
restore database;
recover database;
在恢复目录数据库创建用户
create user rman identified by cat
temporary tablespace temp default tablespace rcat_ts
quato unlimited on rcat_ts;

grant unlimited on rcat_ts to rman;

给RMAN授予维护和查询恢复目录的权限
grant recovery_catalog_owner to rman;

启动RMAN并以目标所有者登录
$rman catalog rman/cat@catdb
rman>create catalog tablespace rcat_ts;

使用RMAN连接到目标数据库，并在新创建的恢复目录中注册该目标数据库
$rman target sys/oracle@orcl catalog rman/cat@catdb
rman>register database;

快速恢复区初始化参数
control_file_record_keep_time
db_recovery_file_dest
db_recovery_file_dest_size

查询快速恢复区使用情况
select file_type,percent_space_used as used,percent_space_reclaimable as reclaimable,
number_of_files as number from v$flash_recovery_area_usage;

select * from v$flash_recovery_area_usage;
删除RMAN备份集
DELETE NOPROMPT BACKUPSET 11, 10, 9;

数据库冷备份和恢复
tar -zcvf oradata.tar.gz oradata/
tar -zxvf oradata.tar.gz

创建数据库示例

CREATE DATABASE andrew
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 (‘/u01/app/oracle/oradata/andrew/redo01.log’) SIZE 100M,
GROUP 2 (‘/u01/app/oracle/oradata/andrew/redo02.log’) SIZE 100M,
GROUP 3 (‘/u01/app/oracle/oradata/andrew/redo03.log’) SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘/u01/app/oracle/oradata/andrew/system01.dbf’ SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘/u01/app/oracle/oradata/andrew/sysaux01.dbf’ SIZE 325M REUSE
DEFAULT TABLESPACE USERS01
DATAFILE ‘/u01/app/oracle/oradata/andrew/user01.dbf’
SIZE 500M REUSE
DEFAULT TEMPORARY TABLESPACE temp01
TEMPFILE ‘/u01/app/oracle/oradata/andrew/temp01.dbf’
SIZE 100M REUSE
UNDO TABLESPACE undotbs
DATAFILE ‘/u01/app/oracle/oradata/andrew/undotbs01.dbf’
SIZE 300M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

listener .ora 配置示例

# listener.ora Network Configuration File: /u01/app/oracle/product/10.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.232.10)(PORT=1521))
)

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=maindb)
(SID_NAME=maindb)
(ORACLE_HOME=/u01/app/oracle/product/10.2/db_1)
)
)
tnsnames.ora 配置示例

# tnsnames.ora Network Configuration File: F:\Oracle\product\10.1.0\Client_2\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

MAINDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.232.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = maindb)
)
)

SQLPlus基本使用

a、近入SQL*Plus
$sqlplus 用户名/密码

退出SQL*Plus
SQL>exit

b、在sqlplus下得到帮助信息
列出全部SQL命令和SQL*Plus命令
SQL>help
列出某个特定的命令的信息
SQL>help 命令名

c、显示表结构命令DESCRIBE
SQL>DESC 表名

d、SQL*Plus中的编辑命令
显示SQL缓冲区命令
SQL>L

修改SQL命令
首先要将待改正行变为当前行
SQL>n
用CHANGE命令修改内容
SQL>c/旧/新
重新确认是否已正确
SQL>L

使用INPUT命令可以在SQL缓冲区中增加一行或多行
SQL>i
SQL>输入内容

e、调用外部系统编辑器
SQL>edit 文件名
可以使用DEFINE命令设置系统变量EDITOR来改变文本编辑器的类型，在login.sql文件中定义如下一行
DEFINE_EDITOR=vi

f、运行命令文件
SQL>START test
SQL>@test

aaron8219

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Oracle DBA 知识点和操作命令

查看和修改sgashow parameter sga;alter system set sga_target=’300M’ scope=both;Linux下查看二进制文件spfilestrings spfile.ora动态查看alert日志tail -f alert.logtail -10 alert.log 数据库四种状态查看bdump下
复制链接

扫一扫