疯狂飞艇

当前位置:疯狂飞艇 > IT教程

oracle数据库DBA笔记

时间:2021-01-11 01:05:25来源:金橙教程网 作者:admin8 阅读:59次
 

oracle dba

Oracle分两个大块:

instance和DATabase

instance就是memory structures,一个instance只能对一个database,多个instance可以对应一个database。database就是文件,硬盘文件。oracle把database划分为除数据库服务器以外最大单位。

background Process structures就是进程,主要是oracle的内部进程。

instance组成:SGA-memory structure和background process structures。

connection先通过TCP连接Server process,server process主要处理客户端和服务器的连接,然后是Session连接oracle server。

oracle database:

a collection of file,三种基本文件;data files,control files,redo log files(重做日志,恢复data files)。

周围的文件:parameter file参数文件。archived log files 归档日志。

上机:ps -ef查看进程

进入u01目录,进入oradata目录,就可以看到control01.ctl就是control files,redo01.log,System01.dbf就是data files。可以看到385000000就是300多MB。

启动oracle:sqlplus /nolog

进入后SQL> conn / as sysdba

clear scr 清屏

startup就可以启动了。

!就可以切换到shell模式

ps -ef

oracle就可以看到instance。

memory structure:SGA and PGA。system global area SGA,在instance里只有一个SGA,很大的SGA。

program global area PGA,在server process里有一个PGA,PGA有很多,也就是一个用户连接数据库就会有一个PGA。

oracle很吃内存,会用掉所有可以用的内存。大量内存消耗到SGA上。

ipcs查看ipc状态。进程状态。

oracle使用的是shared memory segment,共享字节,相对应的show sga可以看到total system global area(SGA) 的大小和共享字节相似有关系。

SGA:shared pool\database buffer cache\redo log buffer\large pool\java pool

SGA:is dynamic。SGA_MAX_SIZE可以设置最大SGA。granules是最小单位。SGA小于128M,granule就是4M;SGA超过128M,granule就是16M。

查看granule:SELECT component,granule_size fROM v$sga_dynamic_components;

客户的数据库目前sga配置为64G,processes需要从6000提高到8000,因为根据ORACLE文档processes越大,_ksmg_granule_size设置的也要越大,把_ksmg_granule_size设置为64M。查看对应版本的联机文档得到这个值。

SGA:DB_CACHE_SIZE;LOG_BUFFER;SHARED_POOL_SIZE;LARGE_POOL_SIZE;JAVA_POOL_SIZE

oracle11后可以自动调整,大大简化了oracle的管理。

shared pool:解析后的SQL语句保存的位置,包含library cache和data dictionary cache。可以改变性能。library,是包含历史的sql语句编译。data dictionary是存储数据库控制信息。shared_pool_size参数。

更改shared pool大小:alter system set shared_pool_size = 64M;建议让oracle自动管理。

database buffer cache:访问或者即将写入数据的cache,最小单位是DB_BLOCK_SIZE来决定。

database buffer cache:DB_CACHE_SIZE;DB_KEEP_CACHE_SIZE;DB_RECYCLE_CACHE_ZISE。改变ALTER SYSTEM SET DB_CACHE_SIZE = 96M; 

收集的不同cache信息行为使用V$DB_CACHE_ADVICE统计信息。

redo entries就是每一个操作,都会记录等待恢复。大小由LOG_BUFFER。

large pool和jave pool都是辅助的可选项。

PGA:是每个进程都会有PGA。而且只用在一个进程。

process structure:user process连接服务器的客户端,可以有多个;server process负责连接客户端的服务器进程,可以有多个,连接后oraclewilson local=no;background process,也可以用很多个ora_xxx_wilson wilson是SID。

通过客户端和本机连接服务器的区别:两台计算机连接使用tcp/ip连接,同一个计算机连接使用ipc连接,同时也可以使用tcp/ip模拟连接通过l0的本地回路。

background process:mandatory和optional。

mandatory:DBWn,PMON,CKPT,LGWR,SMON.

optional:ARCn,LMDn,QMNn,CJQ0,LMON,RECO,Dnnn,LMS,Snnn,LCKn,Pnnn.

database writer DBWn:dbw0开始到dbwn,是最繁重的进程,把SGA中database buffer cache也就是dirty buffer写入到data files,oracle支持20个dbw。

log writer LGWR:和dbwn类似,是把redo log buffer写入到redo log files,before dbwn writes,and lgwr and redo log files is a circle always。

system monitor SMON:负责回收,清理作用。intance recovery。

process monitor PMON:负责监控进程。定期查看process,如果有问题重新生成process。

checkpoint CKPT:和LGWR相似,但是工作内容更琐碎。

Archiver ARCn:把redo log files归档到archived redo log files。

logical structure:1个server只有一个database,1个database可以有多个tablespace表空间,1个tablespace可以有多个segment或者多个datafile,segment可以横跨datafile,1个segment多个extent,1个extent是连续的blocks,block是最小的逻辑单位。1个block对应系统的block的整数倍。tablespace就是省,datafile就是省下划分各个区域,segment就是省下的领导。

=====================================

连接oracle的状态:1.先做安全检查 2.语句解析 3.binding 变量4.execute

SQL:queries return rows查询,DML修改,commit等,会有差异。

DB AdMinistration tools:很多工具,oracle universal installer(install,upgrade,remove components);oracle database configuration assistant(OUI);SQL Plus;oracle enterprise manager:变化比较大,需要另外学习。

批量安装:准备myrespfile

./runInstaller -responsefile myrespfile -silent

例子在安装文件内的response目录内的.rsp文件。

dbca工具,初学管理表创建表工具,目录bin下ll dbca可以看到dbca

./dbca

sys and system两个账号。sys相当于root,system仅次sys。

sys owner of the database data dictionary。password:change_on_install

system owner of additional INTERNAL tables and views used by oracle tools.password:manager

最好改密码并且多使用system。

SQL*Plus:

sqlplus /nolog

connect / as sysdba 

startup 启动oracle

lsnrctl start 启动Listener

oracle enterprise manager:oracle management server中增加agent(oracle server),同时对用户提供console,来控制oem。

oem暂时不稳定,还在迭代中。

======================================

initialization parameter files:启动oracle时,读取spfiledb01.ora,里面设置了oracle instance。

parameter:explicit显示参数,implicit隐式参数。

parameter可以有多个。对应的两种不同参数文件:PFILE和SPFILE,

desc v$parameter;

select name,value from v$parameter;

col name format a20 //整理为20个字符

col value format a30 //整理为30个字符

/ //重新打印两列

查找其中一个值,也就是筛选出来。

select name,value from v$parameter where name='pga_aggregate_target';

还可以模糊查找

show parameter pga_ag;

PFILE:是一个普通记事本文件,可以手动修改,无法立即生效,只能关闭oracle,再开启才能使用。目录:$oracle_home/dbs windows目录:database

env | grep oracle_home 查看$oracle_home

SID:site identifier,oracle_home and SID are hashed together in unix.then on the same oracle_home and uniquely identify SID.

推荐使用dbca工具来创建,如果手工创建,最好找一个created a sample in other server。

init.ora是事例。pfile的文件就是initSID.ora,但是spfile是没有initSID.ora

cp init.ora $oracle_home/dbs/initwelson.ora 拷贝一个数据库内的pfile事例。

实验:mkdir old

mv * old //把所有文件放到old文件夹内

cp init.ora $oracle_home/dbs/initwilson.ora

vi initwilson.ora

查看pfile内的说明可以看到modified看到修改日期,找到对应测试的版本。确定是否实用当前版本。

查看文件,如果目录:oradata/wilson/control01.ctl

那么修改pfile内control_files:

vi initwilson.ora

control_files = ('/u01/oradata/wilson/control01.ctl','/u01/oradata/wilson/control02.ctl')

修改db_name和db_block_size=8192

db_name = 'wilson' 就是目录:oradata/wilson

real application clusters:RAC

SPFILE:二进制文件,不允许直接修改,只能通过sqlplus,但是修改后马上可以启用,现在大部分都使用spfile。

通过pfile创建spfile

create spfile = '$oracle_home/dbs/spfileDBA01.ora'

from pfile = '$oracle_home/dbs/initDBA01.ora';

或者直接create spfile from pfile;

Strings spfilewilson.ora | more 就可以把二进制提取字符串,可以查看。

spfile也可以创建pfile,来vi查看内部信息修改。通过create pfile/spfile  from spile/pfile来进行相互转换

create pfile from spfile='spfileorcl.ora';

spfile中前面会有SID.或者*.是rac对于不同数据库不同instance对应一个数据库。

修改parameter pfile 或者 spfile:

alter system set parameter = value <comment='text'> <deferred> <scope=memory|spfile|both><sid='sid|*'>

comment 注释

deferred 下次登录再更改

scope 缺省为both

sid 为唯一标示

show parameter fast_ 查询fast_打头的参数,然后再下面改变值。

alter system set fast_start_mttr_target = 250;

desc v$parameter; //所有参数都保存在这个表内

deferred:desc v$parameter 中ISSYS_MODIFIABLE中为deferred就是deferred

select name,issys_modifiable from v$parameter where issys_modifiable = 'DEFERRED';

如果查看参数查看reference,如果查看语句查看SQL reference的帮助文档。

deleting or resetting values:删除或者使用oracle原本的缺省值。

alter system reset fast_start_mttr_target scope = both sid = '*';

oracle的启动

STARTUP顺序:spfileSID.ora or default spfile.ora or initSID.ora or default pfile

override default pfile:startup pfile = $oracle_home/dbs/initDBA1.ora

PFILE can indicate to use SPFILE.

PFILE.ora add : SPFILE = /database/startup/spfileDAB1.ora

最后pfile和spfile兼容就是在pfile内增加spfile,然后启动startup时 pfile启动。

most popular文档中concepts介绍概念,administrator's guide主要的DBA内。

查看用户信息,输入id

startup nomount //开启所有oracle的服务,启动ipcs进程,但是没有启动数据

alter database mount; //参数化中controlfile文件启动。普通用户还是无法访问

alert database open; //如果无法打开数据库,需要再次回到mount状态,进行recovery datafiles or redo log files。之前offline的tablespace还是会offline,需要手动打开。

startup [force][restrict]

open <read {ONLY | WRITE [RECOVER]} | RECOVER>

restrict是一个特权用户才能连接的状态

alert system enable restricted session; //system用户可以访问,同样进入restrict状态

alert database db01 mount;

startup mount;

alert database db01 open read only; //read-only

create user boobooke identified by bbk; //create boobooke/bbk

grant connect,resource to boobooke;//授权boobooke用户connect,resource权利,没有restricted

desc v$session; //查看所有session连接用户

select username,sid,serial# from v$session;//查看sid 和serial#,注意sys是本地用户

alter system kill session '15,6';//kill system用户的session

shutdown imMediate //关闭数据库,abort,immediate,transactional,normal

测试一下 select * from dual;可以使用oracle数据库。

diagnostic files

log file:包含alertSID.log file and background trace files and user trace files。

background:SID_processname_PID.trc

user:SID_ora_PID.trc

alertSID.log:查看ERROR即可

show parameter dump //参数包含dump

查看background_dump_dest的位置alertSID.log的位置、

在ORACLE 11g 以及ORACLE 12c中,告警日志文件的位置有了变化。主要是因为引入了ADR(Automatic Diagnostic Repository(自动诊断仓库):一个存放数据库诊断日志、跟踪文件的目录,关于ADR对应的目录位置可以通过查看v$diag_info系统视图。

select * from v$diag_info;

Diag Trace对应的目录为文本格式的告警日志文件所在的目录,

而Diag Alert对应的目录为xml格式的警告日志(对应为log_x.xml)

alert_walnut.log

vi alert_walnut.log 

/normal //search normal查找 instance启动

查看alert database close normal为正常关闭

show parameter dump //参数包含dump

background trace files,后台出现错误进程就会写入,目录也是background_dump_dest的位置

user trace files,只要有用户连接就写入。大小由MAX_DUMP_FILE_SIZE决定,位置user_dump_dest的位置

可以改变user trace files记录的情况:

尽量在session level范围内做:

alter session set sql_trace = true 

dbms_system.SET_SQL_TRACE_IN_SESSION

instance leve范围内做:

SQL_TRACE = TRUE

ps -ef 查看连接用户的PID。

sqlplus system/Aa1 //使用sqlplus 登录system

vi log //查看log 使用:5638可以跳到5638行,往回查看

show user;可以查看现在登录sqlplus的用户名

启动listener lsnrctl start

tail -f wilson_ora_3888.trc //查看动态的新增的信息

tail -f wilson_ora_3888.trc //查看新增的这个文件里面的内容

===============================================

creating a database

planning

define the purpose of the database:OLAP:big transation OLTP:more transation 优先OLTP多事务少处理时间

outline a database architectural design.

choose the database name.

OFA:optimal flexible arch

standard database architecture layout

SOFTWARE

oracle_base /u01/oracle/oracle12/

/product

/release_number

/bin

/dbs

/rdbms

/sqlplus

/admin /u01/oracle/admin/wilson

/inst_name

/pfile

Files e.g. /u01/oracle/oradata/wilson/

oradata/

db01/

system01.dbf

control01.ctl

redo0101.log

db02/

system01.dbf

control01.ctl

redo0101.log

authentication methods:

sys sysdba sysoper 必须要有,才能启动oracle

使用其他用户使用SecureCRT登陆,要先设置Linux-oracle的环境变量

export ORACLE_HOME=/u01/oracle

export ORACLE_SID=wilson

env | grep ora

/u01/oracle/bin/sqlplus /nolog 登录SQLplus端

这时一定要用用户和密码

conn sys/oracle as sysdba 不能通过sysdba,使用sys用户并使用密码。

远程连接oracle,判断是否为secure链接,如果是安全的连接(是否为httpS),再判断是否走操作系统的认证,不是最后走用户名和密码。

前提要把本地listener启动

lsnrctl start

否secure连接

sqlplus /nolog 登录到本地的SQLplus端

conn sys@oracle9ivm as sysdba //@后面是network\admin\tnsnames.ora连接别名

想要连接需要口令

password file  authentication:

cd /u02/oracle12/dbs //查看orapwwilson是密码文件

orapwd file=orapwtest password=boobooke entries=10 创建一个新的ora的password文件

如果使用这个文件还需要Set remote_login_passwordfile = exclusive in initialization parameter file.

add users to the password file.assign appropriate privileges to each user.

grant sysdba to hr; 把hr用户赋予sysdab权限

sys 用户的口令就可以是boobooke

创建chris数据库可以使用dbca创建,oracle database configuration assistant

export ORACLE_SID=chris

dbca 原来dbca是需要装显示video软件,来显示。可以装Xmanger。

environment:数据库环境变量

ORACLE_HOME

ORACLE_BASE

ORACLE_SID

ORA_NLS33 缺省:$ORACLE_HOME/ocommon/nls/admin/data 设置数据库字符集

PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH C语言的时候需要调用这个库,:代表把$ORACLE_HOME/lib加入到$LD_LIBRARY_PATH目录下

export LD_LIBRARY_PATH

creating a DB Manually...

删除上个数据库dbca启动后可以删除。或者手动删除:u01/dbs目录下把带chris删除,u01/oradata目录下带chris删除,u01/admin目录下带chris删除。

手动创建数据库也可以使用sql的脚本来创建crdb01.sql。如:

spool dbcreate.log; 创建并输出到这个log里

create database "bbk"

maxdatafiles 500

maxinstances 8

maxlogfiles 32

character set "utf8"

national character set al16utf16

archivelog

datafile

'/u01/oradata/bbk/system01.dbf' size 300m

extent management local

default temporary tablespace temp tempfile '/u01/oradate/bbk/tempts01.dbf' size 100m extent mangement local

undo tablespace "undotbs1"

datafile "/u01/oradata/bbk/undotbs01.dbf" size 200m

logfile

group 1(

'/u01/oradata/bbk/redo01a.rdo',

'/u01/oradata/bbk/redo01b.rdo') size 100m,

group 2(

'/u01/oradata/bbk/redo02a.rdo',

'/u01/oradata/bbk/redo02b.rdo') size 100m,

group 3(

'/u01/oradata/bbk/redo03a.rdo',

'/u01/oradata/bbk/redo03b.rdo') size 100m;

spool off //关闭log

设置环境变量的脚本bbk.env

ORACLE_SID=walnut

ORACLE_BASE=/oracle

ORACLE_HOME=$ORACLE_BASE/oracle

LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

PATH=$ORACLE_HOME/bin:$PATH

DISPLAY=192.168.25.1:0.0

TNS_ADMIN=$ORACLE_HOME/network/admin

LD_ASSUME_KERNEL=2.4.19 //如果使用redhat就需要设置linux使用标准的linux Threads

export ORACLE_SID ORACLE_BASE ORACLE_HOME LD_LIBRARY_PATH PATH DISPLAY TNS_ADMIN

. ./bbk.env 就把这个文件环境变量可以生效

@$oracle_home/dbs/crdb01.sql; //执行上面的SQL语句脚本

spool log1.log //准备下面的脚本,先创建一个log

@?/rdbms/admin/catalog.sql //如上面的SQL语句脚本一样,?代表$oracle_home

start the instance in nomount stage

create database

=======================================

using data dictionary and dynamic performance

other objects created with the database:

data dictionary

performance tables

PL/SQL packages

database event triggers

data dictionary:read-only,存储位置system tablespace,owned by the user sys。

DD:base tables,created with create database。data dictionary views。

cd oracle12/rdbms/admin

vi sql.bsq //就是基表

vi catalog.sql //视图表由这个创建

查看表结构

desc user_tables;

desc all_tables;

dynamic performance tables:owned by sys uer,synonyms begin with v$,listed in V$FIXED_TABLE

desc v$FIXED_TABLE;

control file:a small binary file,defines current state of physical database.loss may require recovery.sized initially by create database。

联机重做日志文件(online redo log file),数据文件,是最重要的而且无法恢复。

mounting the database:reading the control files to obtain the names and Status of the data files.

strings control01.ctl | more //查看control

multiplexing the control file多路复用

如果在spfile,内写了三个control,那么oracle肯定是同时并发写入三个control内。所以互相为备份,并且三个control放在不同位置。

但是如果control出现问题,其中一个出现问题,那么oracle就无法启动。保证存储在不同的物理磁盘内。

alter the spfile:如果原control_files只有一个:control_files = '$HOME/ORADATA/u01/ctrl01.ctl'需要再增加一个,可以安装如下步骤。

startup

alter system set control_files = '$HOME/ORADATA/u01/ctrl01.ctl',

'$HOME/ORADATA/u02/ctrl02.ctl' scope=spfile;

shutdown immediate

cp $home/oradata/u01/ctrl01.ctl

 $home/oradata/u02/ctrl02.ctl

startup

show parameter control

get control file information

v$controlfile

v$parameter

v$controlfile_record_section

using online redo log files

record all changes made to data

provide a recovery mechanism

第一遍写入联机存储日志,第二遍才写入磁盘,所以很吃内存。

must be organized into groups

at least two groups required,double file,quadra file.

online redo log file group,same group same file

online redo log file members,log sequence numbers,uniquely identify each other.

multiplexed redo log files:把一个组内的相同文件,分别放在不同的物理磁盘内。写满的日志文件会写入到磁盘,引发一个checkpoint。有一些信息还会写入到control文件内。

when will LGWR write redo?

a transaction commits,three seconds,redo log buffer one-third full,before the DBWn.

log switch:switch other group.

log sequence number 也就是lsn会把之前的log文件删除或者归档。

forcing a log switch:

alter system switch logfile;

desc v$log; //查看logfile

select group#,thread#,sequence#,status from v$log; //查看组

current,inactive,inactive

alter system switch logfile;

select group#,thread#,sequence#,status from v$log; //查看组

active,current,inactive

alter system switch logfile;

select group#,thread#,sequence#,status from v$log; //查看组

inactive,inactive,current

checkpoints can be forcing

FAST_START_MTTR_TARGET = 600 //second,设置为0则是表示关闭自动检查点功能

ALTER SYSTEM CHECKPOINT;

2组,写第一组一条,就写第二组,第二组写满后,是否覆盖第一组?

1.写完第一组后写第二组,触发log switch,log switch引发checkpoint

2.lgwr的频率快,但是dbwn比较慢,不能覆盖第一组。

所以如果出现,组小,组内文件小,lgwr频率快,dbwn比较慢,oracle发现不能覆盖第一组联机日志,最后只能hang住不动。也就是过一段时间强制checkpoint。

FAST_START_MTTR_TARGET保证恢复时间最大不超过600秒,但是dbwn写磁盘的时间,增加IO的大小。

show parameter fast //查看FAST_START_MTTR_TARGET

FAST_START_MTTR_TARGET = 600 scope=both; //同时修改

adding redo files groups:

desc v$logfile; //可以查看redo

alter database add logfile group 4

('/u01/oradata/wilson/redo04a.rdo',

'/u01/oradata/wilson/redo04b.rdo') size 50M;//创建50M的文件

alter database add logfile member

'/u01/oradata/wilson/redo04c.rdo' to group 4,

'/u01/oradata/wilson/redo04d.rdo' to group 4; //

这样4组会有4个rdo文件。

dropping redo file members:

删除前必须,forcing a log switch:保证切换到其他组。invalid,数据库无法访问,stale,可以切换valid,不完整或者错误。

alter database drop logfile member '/u01/oradata/wilson/redo04a.rdo';

但是文件没有被删除。

rm -f /u01/oradata/wilson/redo04a.rdo //删除文件

再删除只能删除组。

dropping redo files groups:

an active or current group cannot be dropped.

desc v$log;

select group#,members,archived,status from v$log;

alter database drop logfile group 3;

如果删除active or current,可以先forcing a log switch:

更改redo logs,最好complete back up。

最简单的是new members and drop old members

1.shut down

2.copy 

3.mount

4.execute command

clear redo files:

alter database clear logfile group 2;

redo file configuration

oltp:增长比较快的redo,多配几组,成员不能小,但是大多少需要测试。

redo log group 1 disk1 and disk3

redo log group 2 disk2 and disk4

archive:disk5 and optionally disk6

control file 应该每个磁盘都有。

manage redo files with omf...

group and member

v$log group,thread,...

v$logfile group,status,type,member

v$log_history

thread#:rack时,启用多thread。redo thread。

the redo log file groups of an instance are collectively called a 'thread'.

archived redo log files:

recommend in archivelog mode:

desc v$instance;

select archiver from v$instance;

desc v$database;

select log_mode from v$database;

tablespaces and data files

oracle stores data logically in tablespaces and physically in data files.

多个data files组成一个tablespace,一个tablespace只能有一个database。

schema:用户。

system tablespace:data dictionary,undo segment

non-system tablespace:eases space administration

permanent,undo,temporary tablespace.

主数据,管理数据

creating tablespaces

create tablespace userdata

datafile '/u01/oradata/userdata01.dbf'

select * from v$tablespace;

desc dba_data_files;

select file_name,tablespace_name from dba_data_files;

查看物理存储位置

col file_name format a40;

create tablespace paul datafile

'/u01/oradata/wilson/paul01.dbf' size 20m;表空间名字为paul

locally managed tablespace(自治区),dictionary-managed tablespace(中央集群),tablespace(省)。

1byte = 8bits 连续bits = bitMap

recommend:locally managed tablespace。

查看default temp TS

desc dba_tablespaces;

select tablespace_name,contents,extent_management from dba_tablespaces;

新建一个temporary

create temporary tablespace mytemp

tempfile '/u01/oradata/wilson/mytemp01.dbf' size 100M

extent management local;

创建后是假的容量100M

修改temporary

alter database default temporary tablespace mytemp;

read-only tablespaces:

alter tablespace userdata read only;

objects can be dropped from tablespace,但是不能删除。

创建表空间

create tablespace wenwen

datafile '/u01/oradata/allan/wenwen.dbf' size 20M

extent management local uniform size 128k;

创建用户,并设置默认表空间wenwen

create user dz identified by dz default tablespace wenwen;

赋予权利

grant connect,resource to dz;

使用dz登录

sqlplus dz/dz

create table t (id integer,name char(10));

insert into t values(0,'nicai');

commit;//确保写入到磁盘

select * from t

切换到sys

objects can be dropped from tablespace;

之前的语句没有commit,所以hang在这里。

再次进入dz,无法插入语句。

taking a tablespace offline:

alter tablespace userdata offline;

alter tablespace userdata online;

resizing a tablespace:

automatically using autoextend

manually using alter database

adding a data file using alter tablespace

recommend adding a data file using

通过sql脚本,查看tablespace的情况

Google:oracle tablespace usage sql

tsa.sql 拷贝到tmp目录下

@/tmp/tsa.sql;

auto:

alter database datafile 'data3.ora' autoextend on next 20m maxsize 1000M;

autoextend on next 20m maxsize 1000M;

desc dba_data_files;//查看autoextensible

select file_name,tablespace_name,autoextensible from dba_data_files;

storage clause precedence

oracle default>tablespace>segment

查看db_block_size=8192

strings spfileallan.ora | grep -i db_block //把block的行显示

four nonstandard block sizes :between 2kb and 32kb

standard block size:system and temporary must standard.

db_cache_size数据缓存的大小决定了默认buffer cache for standard block size

buffer cache minimum size = one granule(4MB or 16 MB)SGA最小尺寸是granule。默认是48MB

nonstandard block size:

dynamic parameters:

DB_nK_CACHE_SIZE is not allowed if nK is the standard block size.minimum size for eache cache is one granule.

方便转移数据而设立的nonstandard。把oltp转到olip适合nonstandard。olip是适合nonstandard

创建非标准的表空间:

create tablespace tbs_1

datafile 'tbs_1.dbf'

size 10M blocksize 4k;//创建后不能改blocksize

desc dab_tablespaces;

select tablespace_name,block_size from dba_tablespace;// 查看表空间的blocksize

database block contents:从data开始写freespace到header。

block space util parameter:INITRANS,MAXTRANS,PCTFREE,PCTUSED

INITRANS and MAXTRANS:初始事务槽,最大槽。

PCTFREE:性能调优,最少有多少空闲时间,用以已存在的记录进行扩张。

PCTUSED:性能调优

FREELISTS:性能调优,就是零钱块。

data block management:automatic segment-space management ASS,manual management.

lot of bitmap segments belong segment

配置ASS:

create tablespace data02

datafile '/u01/oradata/data02.dbf' size 5

extent management local uniform size 64k

segment space management auto;

manual data block management:

High-Water Mark:观测变化河水的水位。大数据确定不足才向右,走high-water mark。

desc user_segments;

select segment_name from user_segments;//no rows selected 无segment,干净的用户

create table m(id integer,name char(10));

select segment_name from user_segments;//M

select count(*) from m;//插入100W,删除100W,查看water mark

首先设置 atuotrace

设置好后 set autotrace on stat

set autot off

设置好后可以看到 physical reads 查看插入情况

begin

for i in 1 .. 1000000

loop

   insert into m values(i,'bookooke');

end loop

;

commit;

end;

/

select count(*) from m;

delete from m;

commit;

select count(*) from m;

可以看到physical reads还是要多。

truncate table m;//也是delete,但是会删除记录,所以physical reads少了很多。

如果是auto管理就是有一个low high-water mark可以自动管理high-water mark。

pctfree=20% 去掉block的头,剩下的未用的百分比,如果block的数据大于20%,优先放pctfree部分,如果小于等于20%,就会off freelist。20%是为了update。以后的插入和这个块没关系了。

pctused=40% 去掉block的头,剩下已经用的百分比。当block的数据下降到40%时,就会on freelist。

如果用auto pctfree会使用。如果使用manual,pctfree,pctused,freelist都会使用。

如果高修改,就要用high pctfree,low pctused,如果不修改,要么插入,要么删除,可以用low pctfree,high pctused。

getting storage information:

DBA_EXTENTS

DBA_SEGMENTS

DBA_TABLESPACES

DBA_DATA_FILES

DBA_FREE_SPACE

DBA是全局信息

desc user_segments;

col segment_name format a30

select segment_name,segment_type from user_segments;

Undo segment:从修改,到commit。原来的值需要保存在undo segment内。undo segment是可以覆盖旧的undo segment信息。

undo segment purpose:transaction recovery,transaction rollback,read consistency。

read consistency:scn only one。

insert into t(x,y) values (1,1);//purduct redo log buffer and undo 信息容量少and undo-redo and index-redo,如果断电,肯定数据是原始数据。如果commit只往redo上写信息。lgwr只有一个。

update t set x = x+1 where x =1;//purduct redo log buffer and undo 信息容量不多不少and undo-redo and index-redo,如果断电,肯定数据是原始数据。

delete from t where x =2;

commit;//如果commit只往redo上写信息。lgwr只有一个。而且最后commit执行的时间一定是一样的。

atuo undo segments:

undo tablespace=undotablespace

undo_management=auto

改变属性:

alter tablespace undotablespace

add datafile '/u01/oradata/undotbs2.dbf'

size 30m

autoextend on;(autoextend off如果这个关闭就无法扩展,也就无法管理,如果30m不够就会报错)

show parameter undo

desc dba_tablespaces;

select tablespace_name,contents from dba_tablespaces;

undo_retention:保存undo的时间,单位是s.这个决定了undo表空间的大小。

show parameter undo

查看undo_retention

alter system set undo_retention = 900 scope=both;

desc v$transaction;

查看used_ublk,未commit的undo。后来的人如果要这个资源,只能等第一个人commit后,最好undo占到所有的磁盘空间。

select addr,used_ublk from v$transaction;

desc v$unDOStat //查看undo的历史,大概7天左右的信息

undo重要参数:

undo_retention,db_block_size,number of undo ata blocks generated per second。三个参数

最后一个需要查看:select end_time,begin_time,undoblks from v$undostat;

undo / (end_time(换算秒) - begin_time(换算秒) )

select max(undoblks / ((end_time - begin_time)*24*3600)) from v$undostat;

14 undoblock/s

select sum(undoblks) / sum((end_time - begin_time)*24*3600) from v$undostat;

4 undoblock/s

show parameter undo

check undo_retention=900 900*15=undoblock

show parameter block

check db_block_size=8192 8kb 8192*900*15=size

you can add % 富余。

undo quota

防范恶意用户,设置UNDO_POOL,...

Https://www.cnblogs.com/vanwoos/p/10057904.html

desc dba_rollback_segs;

select segment_name,tablespace_name from dba_rollback_segs;

desc v$rollname;//在线segment的信息

select * from v$rollname;

desc v$rollstat;//统计信息

select usn,status from v$rollstat;

data types:built-in,(scalar,collection(复杂),relationship(复杂))

scalar:文本型,数值型,日期型,二进制。

char固定字符串,varchar2可变字符串,推荐varchar2。

number高精度小数点后32位。

date日期,timestamp时间戳。

声音或视频文件,为二进制。LONG。推荐LOB大对象。

ROWID每个记录的特殊标记。

一个表最多1000列。不超过254列,存储为一个块,超过了就存储为一个块。无限的行。

1024hash partitions * 342,926,295,040

1022files * 32GB files * 2,143,289,344blocks * 16kb

160rows of between 80 to 100bytes per block。

一般处理为上亿条记录。几千张表,一个表不超过254列。

indexes用来把所有表组合起来。

ROWID format:18个字符。rowid是变化的,导入导出会有变化。

select rowid,id,name from m;

create table hr.employee() tablespace users;

创建两个表空间,一个是手动管理,一个是自动管理。

pwd //查看tablespace地点路径为/u01/oradata/denver

create tablespace assm datafile '/u01/oradata/denver/ssm_1.dbf' size 100M extent management local uniform size 128K segment space management auto;

create tablespace mssm datafile '/u01/oradata/denver/ssm_1.dbf' size 100M extent management local uniform size 128K segment space management manual;

create user assm idenntified by assm default tablespace assm;

create user mssm identified by mssm default tablespace mssm;

grant connect,resource to assm;

grant connect,resource to mssm;

conn assm/assm;

show user

desc user_segments;

col segment_name format a30

select segemt_name,segment_type from user_segments;//开始没有数据

create table t(

id int,

name char(10)

)

storage(initial 128K next 128K pctincrease 0 minextents 1 maxextents 5)//存储参数:segment为128K,空间不足申请extent为128K,pctincrease为extent增长百分比,为0是所有的都一样,如果不为0则extent越来越大。miniextents最少1个。最多extents为5个。

tablespace assm;//创建表到assm空间上,可以把表放在不同的表空间上。也可以unlimited。

select segemt_name,segment_type from user_segments;//有t的表了。

FREELISTS:MSSM ONLY。如果并发比较高,提高freelists。

PCTFREE:MSSM and ASSM。update 多的时候使用比较高。

PCTUSED:MSSM ONLY。删除增加多的时候,可以重复再利用块,使用比较高。

INITRANS:ASSM and MSSM。小的话,并发插入修改就不足。

把表放在不同的表空间内。

creating temporary table:

create global temporary table hr.employess_temp...

临时表可以提前定义。

transaction or session

create global temporary table tmp_session on commit preserve  rows

as select * from t where 1=0;

从t表拷贝出来的session临时表的结构,并且on commit preserve rows指明是session基本有效。

create global temporary table tmp_transaction on commit delete rows

as select * from t where 1=0;

并且on commit delete rows指明transaction有效。

insert into tmp_session select * from t;//把t表拷贝到tmp_session

commit;

在commit之前两个表都有记录,但是commit结束后只有session表内还有记录。

pctfree=(average row size - lnitial row size) * 100 / average row size

修改storage and block

alter table ...只有新的数据会有影响,旧的数据没有影响。

手动增加extents,当插入大量信息,可以事先申请,加快插入速读,extents可以跨文件,但是不能跨tablespace。

alter table hr

allocate extent(size 500k

datafile '/disk3/data01.dbf');

Oracle 行链接(Row chaining) 与行迁移(Row Migration)是主要的维护性能部分。

desc user_tables //查看用户有多少张表

select tabel_name,tablespace_name from user_tables;

select dbms_metadata.get_ddl('TABLESPACE','TBS_NAME') from dual;//可以使用dbms_metadata.get_ddl来查看表t的详细信息。注意括号内为大写。

alter table t move tablespace mssm;//移动表就可以消除Row Migration问题。

create table m as select * from t;复制表t,再删除t,并且把m重命名t

truncate 是可以防止water mark问题。直接释放所有空间。index也会被删除。DDL语句,不是DML语句。

drop table hr

casCADe constraints;//删除表

unused option:打标签,最后统一删除列。

alter table hr

set unused column myname;

alter table hr

drop unused columns checkpoint 1000;

alter table hr

drop columns continue checkpoint 1000;

managing index

B-Tree index

值多,update就可以随便update,or时速度慢,OLTP,update多。

select count(*) from user_objects;//查看当前用户的表对象

select object_name,object_type from user_objects;

create index t_idx1 ON t(id);//创建索引

select object_name,object_type from user_objects;//查看索引

delete insert update 越多,与索引会抢占资源。

Bitmap indexes

值少,update就update一大堆,or时速度快,OLIP,update少。

表和索引放在不同表空间。使用一致的extent sizes。

index的initrans要高一些。方便疯狂飞艇的transaction访问。

e.g.

create unique index ind2

on orders (order_num)

tablespace user_index

pctfree 25

initrans 2

maxtrans 255

storage (initial 128k next 128k pctncrease 0

   minextents 1 maxextents 100

   freelists 1 freelist groups 1

   buffer_pool keep);

create bitmap index ...

alloc and dealloc index space

alter index idx

allocate extent(size 200k

datafile 'index01.dbf')//推荐手动增加

alter index idx

deallocate unused;

rebuilding indexes

alter index orders_region_id_idx rebuild

tablespace indx02;

rebuild也可以放在另外的表空间。

alter index orders_id_idx rebuild online;

可以online进行rebuild。

alter index t_idx1 coalesce;//碎片化合并

coalesce和rebuild

checking index validity:

analyze index orders_idx

validate structure;

desc index_stats //查看分析结果

select height,name,lf_rows,lf_blks,del_lf_rows from index_stats;

查看lf_rows,lf_blks,del_lf_rows

rebuild后del_lf_rows为0

drop index idx;

identifying unused indexes

alter index idx

monitoring usage;

desc v$object_usage

set lines 300

col index_name format a5

set autot on exp

set autot offs

alter index idx nomonitoring usage;

Data Integrity

最后application code,其次考虑database trigger,首先考虑integrity constraint

not null:

create table ...(... not null)or alter table modify ... not null;

check:通用化,column level or table level。

create table bonus(

emp_id varchar2(40) not null,

salary number(9,2),

bonus number(9,2),

constraint ck_bonus check (bonus > 0));//最后一行是定义了表的check,ck_bonus是约束名。

alter table bonus

add constraint ck_bonus2 check (bonus < salary);

unique:可以有null值,null不等于null。

alter table bonus

add constraint uq_emp_id unique (dept,emp_id)//dept和emp_id的合体是unique。

using index tablespace indx

storage();

alter table emp add

ssn varchar2(11) constraint uq_ssn unique;

primary key:not null and unique,唯一且不为空。

create table t(

id int primary key,

name char(10));

不定义primary key的名字,oracle就会自动创建。

foreign key:值是存储在其他表内的值。child table---foreign key column---NULL values,parent table---referenced key column。

也就是省级联动。市为外键且为空,省为参数键。不能删除父表记录delete no action。删除父表记录连同子表一同删除delete cascade。删除父表设置子表为null delete set null。

alter table city add constraint

foreign key (country_code, state_code)

references state (country_code, state_code)

on delete cascade;

creating disable constraints:

创建暂时不起作用的constraint。

alter table emp add primary key(emp_id) disable;

dropping constraints:删除索引

推荐随时输入数据的表,加constraint,可以先从disable novalidate,再enable novalidate,最后enable validate。

alter table t enable novalidate check;//如果是primary key就会报错,只有不是唯一性索引才能行。

alter table t enable validate check;

immediate vs deferred

deferred不会推迟检查,属性改成可以commit检查。set constraints 或者alter session 来设置deferrable的命令

alter table t add primary key(id) deferrable;//表内没有数据,加一个约束,但是是deferrable的约束。

那么就变成了非唯一性索引,可以查看是nonunique,但是不是unique。

select index_name,uniquenes from user_indexes;

发现为disable nonunique。

然后可以插入相同的记录。

alter table t enable nonunique primary key;

再次查询select index_name,uniquenes from user_indexes;为enable nonunique。

那么就可以直接从disable改enable novalidate。如果再次新插入相同记录就不可以了。

set constraints all deferred;//可以延期检查所有的,适合上面改状态,但是commit执行的时候,就恢复到原状并报错了。

也可以set constraint deferred的名字;

commit;

drop table table_dx cascade constraints;//可以删除父表连带的子表信息

drop tablespace tablespace including contents cascade constraints//可以删除tablespace

父表和子表的更新,最好对child的foreign key的列进行索引。

self-referencing foreign keys:

-define or enable foreign keys after the initial load.

-defer constraint checking.

对于脚本要求高。

enabling constraints:

alter table hr

enable novalidate constraint dept_pk;

alter table hr

enable validate constraint emp_dept_pk;

using the EXECEPTIONS Table

running the utlexpt1.sql /rdbms/admin

execute the alter table statement with exceptions option.

use subquery on EXCEPTIONS to locate rows with invalid data.

rectify the errors.

reexecute alter table to enable the constraint.

@?/rdbms/admin/utlexpt1.sql //home目录下

desc exceptions

create table t(id int primary key disable,name char(10));

select object_name,object_type from user_objects;

insert into t values(0, 'boobooke'); //插入0,1,1数据

alter table t enable validate primary key;//显示非法

alter table t enable validate primary key EXCEPTIONS INTO exceptions; //就可以了

select count(*) from exceptinos;//显示两个非法的数据

select rowid,id,name from t where rowid in (select row_id from exceptinos);//显示具体的两个非法数据

update t set id = 2 where rownum = 2;//然后在t表内修改

delete from t where id = 1;//或者删除

alter table t enable validate primary key ; //就可以最终改为enable validate

managing password security and resources...

managing users...

managing privileges...

managing roles.. .

managing auditing...

loading data into a database

在导入数据前最好把表重整理一下,减少highwater mark前面的free space

create table bt as select * from all_objects where 1=0;

select count(*) from all_objects;//通常使用这个all_objects表构建big table

insert into bt select * from all_objects;//传统插入方法

insert /*+ APPEND */into bt select * from all_objects;//新方法插入方法

Parallel direct load

alter session enable parallel dml;

insert /*+PARALLEL(hr.employees,2)*/ //多个partition内可以,2个partition

into hr.employees nologging

select * from hr.old_employess;

partition table:

create table emp

(empno int,

ename varchar2(20))

PARTITION BY HASH(empno)

(partition part1,

partition part2)

/

select object_name,object_type from user_objects;//可以看到partition表

实验:set autot

set auto stat

insert into bt select * from all_objects;

查看autot的redo size是2.8M

insert /*+ APPEND */into bt select * from all_objects;

查看autot的redo size是2.9M

insert /*+ APPEND */into bt nologging select * from all_objects;

查看autot还是有redo size是2.8M

想减少redo size可以

alter table bt nologging;

这样就会让表不产生过多redo,也就是之前redo是跟着表的logging进行的。

再次insert /*+ APPEND */into bt select * from all_objects;

发现确实产生了很少的redo信息。

SQL*Loader

查看文档Oracle Database Utilities

选择Part SQL*Loader

这个工具目录:/u01/oracle/bin/sqlldn

使用ls sql就可以查看到

在/u01/oracle/bin/直接输入sqlldr就可以看到。

进入/u01/oracle打开vi demo1.ctl最简单的输入文件

先按照demo1.ctl说的,创建dept表

create table dept

(deptno number(2) constraint dept_pk primary key,

dname varchar2(14),

loc varchar2(13)

)

/

sqlldr userid=u1/bbk_12345 control=demo1.ctl//使用sqlldr,使用用户名u1密码bbk_12345,control文件是demo1来插入

如果数据文件和控制文件分开,可以把* 改成demo.data,或者其他的数据文件,并把控制文件下面的数据删除。

查看短文件使用cat demo1.ctl

sqlldr的11个demo放在/u01/oracle/rdbms/demo下,ls ulc*就可以看到有三种文件,一个是数据文件,一个是控制文件,还有一个是数据库创建的环境准备文件。

举例常用data files formats:

load data

infile 'example.dat' "str '|\n'"

into table example

fields terminated by ',' optionally enclosed by '""'

(col1 char(5),

col2 char(7))

example.dat:

hello,word,|

james,bond,|

根据|回车来取值。

logical records

可以把physical records多个表合并在一起,插入数据库。

还支持parallel direct path load并发导入。

discarded or rejected rec

Bad file:细节的拒绝

discard file:大范围的拒绝

SQL*Loader Exit Code可以和shell编程融合导入。

parameter file

可以删除index然后再创建index方便largest导入数据。

using globalization support...

相关阅读

数据库系统概念

数据库系统概念复习笔记 基本介绍 数据库系统 数据库系统由一个相互关联的数据的集合和一组用以访问这些数据的程序组成。这个数

MySQL数据库下载与安装

原创不易,转载前请注明博主的链接疯狂飞艇:Blessy_Zhu http://blog.csdn.net/weixin_42555080这篇文章主要介绍MySQL数据库下载、安装

搞清楚数据库中的关系模型

最开始比较模糊,现在是有很大的感触。第一范式是没有重复的列,即列不可分割。第二范式是存在关键字,区分每一行。第三范式是确保表不

Oracle Sqlplus命令登录的几种方式

支持的功能很全,但常用的几种连接方式也就几种: 1. sqlplus / as sysdba   sqlplus / as sysdba 无需数据库进入可用状态,就可用

SQL中的Northwind数据库

前言   安装SQL Server时,系统会自动建立几个数据库,其中Northwind是一个用户数据库的示例,用于使用者自学。因为里面有很多的数据

分享到:

IT相关

程序相关

推荐文章

热门文章

疯狂飞艇官网疯狂飞艇网址疯狂飞艇平台疯狂飞艇app疯狂飞艇下载