oracle杂记

1、Oracle系统结构

SGA:system global area包括
共享池
数据库缓存
重做日志缓存
其他结构（大缓存池、java池等）是可选的
PGA:program global area
instance:
   memory structures
   background structures
database:
   data file
   log file
   control file
可以在sqlplus里查询SGA
show sga
查询后台进程：
select * from v$bgprocess;
查看共享参数：
show parameter shared
查看数据参数：
show parameter db
查看共享日志：
show parameter log
当提示信息是？时，可以改变语言环境，让它输出英文：
alter session set nls_language=american;
更改db_cache_size：总合不能超过sga_max_size
alter system set db_cache_size=100m;

更改shared_pool_size：
alter system set shared_pool_size=100m;
shared pool包括：
dirctionary cache=row cache（用户权限等对象）
library cache(最近常用的cmd)
查看归档日志：
achieve log list
oralce逻辑结构包括：表空间、段、分区、块

2、Oracle的一些工具

OUI:oracle universal installer
Unix下非交互模式安装：
./runInstaller -responsefile myrespfile -silent
可以在命令行下启动数据库配置助手(database configuration assistant):cmd->odca
odca具有4个功能：
创建数据库、配置数据库选项、删除数据库、管理模板
OFA(Optimal Flexibel Architecture)
os权限审核步骤：
create os usesr id
create os group -ora_dba,ora_mym_dba
add os user id to ora_dba group
edit sqlnet.ora sqlnet.authentication_services=(nts)
口令文件审核(是修改sys用户的密码，entries表示可以授权的用户数)：
cmd->orapwd file=d:/oracle/ora90/database/pwdmym.ora password=admin entries=10;
查询系统用户：
select * from v$pwfile_users;
启动数据库步骤：
startup nomount
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

3、参数文件、启动关闭与诊断

系统参数文件——spfilesid,spfile
初始参数文件——initsid.ora
系统启动查找顺序：
spfilesid.ora--spfile.ora--initsid.ora
察看系统参数：
select name,value from v$system_parameter
更改系统参数:
alter system set x=y scope=memory|spfile|both;
[在spfile范围内，可以通过shutdown startup生效]
手动创建spfile
create spfile='d:/spfile.ora' from pfile;
Oracle的启动过程：shutdown-nomount-mount-open
nomount--startup instance
         used to create db,create controlfile
mount--open controlfile
       used to change log mode,rename file,db recovery...
startup [nomount|mount|open]
        pfile=
        restrict|recovery|force
启动系统限制：
alter system enable restricted session;
这个时候可以授权给其他用户：
grant restricted session to sb.
关闭系统限制：
alter system disable restricted session;
只读打开：
alter database open read only;
关闭模式：shutdown +
Normal
Transactional
immediate
abort
OMF:Oracle Managed Files
默认系统里有些参数是未指定的，指定db_create_file_dest后，create tablespace test1就不会出错了：
alter system set db_create_file_dest='d:/oradb';
默认在线的日志最多6个，不过可以自定义增加：
alter database add logfile group 6;//新加的在d:/oradb下
这个时候drop tablespace test1能自动删除系统创建的日志，手动设置的日志可以用alter database drop logfile group 6;
传统的管理表空间方式：
1）创建：create tablespace test1
datafile 'd:/oradb/test1.dbf' size 2M;
2）丢弃：drop tablespace test1
这个时候数据文件并没有被丢弃，故可以重用：
3）重用：create tablespace test1
datafile 'd:/oradb/test1.dbf' reuse;
3)完全丢弃：drop tablespace test1
including contents and datafiles;
诊断文件包括：
alertSID.log file 系统警告记录
Background trace files 后台进程跟踪
user trace files 用户跟踪，缺省关闭
可以用下面命令打开和关闭用户跟踪：
1)Session Level by using the alter session command:
alter session set sql_trace = true
2)Session level by executing DBMS procedure:
dbms_system.SET_SQL_TRACE_IN_SESSION
3)Instance level by setting the initialization parameter:
SQL_TRACE = TRUE

4、创建数据库
有两种方式：
一是Oracle Database Configuratioin Assistant
二是Create Database 命令手工建立，需要注意的是：
在unix平台下，需要设置下面环境变量：
ORACLE_BASE--->D:/oralce
ORACLE_HOME--->D:/oracle/oralce90
ORACLE_SID
ORA_NLS33 字符集
PATH
LD_LIBRARY_PATH
在windows平台下，只要在cmd->set oracle_sid=xxx 就可以了。
手工建立步骤：
1）决定唯一的实例和数据库名
2）选择一个数据库字符集
3）设置操作系统变量
4）编辑/创建初始化参数文件
$ cp init.ora $ORACLE_HOME/dbs/initdb01.ora
5）在nomount模式下启动实例
6）执行create database命令
7）运行脚本生成数据字典和后期的数据库完成过程
windows平台下手工建立步骤：
1)设置环境变量：set oracle_sid=xxx
2)创建例程：oradim -new -sid xxx -intpwd yyy -pfile d:/oralce/ora90/database/initxxx.ora
手工创建d:/oralce/ora90/database/initxxx.ora文件，内容只有一行，就是指定初始参数文件的位置:
IFILE='D:/oralce/admin/xxx/pfile/init.ora'（该文件在下一步中建立）
3)准备初始参数文件：把ora90/admin下的sample文件夹拷贝到oralce/admin下，并改名为xxx
把xxx下面的pfile下的initsmp.ora文件改为init.ora,打开编辑
dbname=xxx
control_files=('d:/oracle/oradata/xxx/control01.ctl','d:/oracle/oradata/xxx/control01.ctl')
db_block_size=8192
其他参数可以缺省，保存。
4)创建spfile
启动sqlplus /nolog
conn sys/admin as sysdba
create spfile form pfile
5）startup pfile=d:/ora90/database/initxxx.ora nomount
6）create database xxx
   maxlogfiles 10
   maxdatafiles 1024
   maxinstances 2
   datafile 'd:/oracle/oradata/xxx/system01.dbf' size 50m
   logfile group 1 'd:/oralce/oradata/xxx/redo01.ora'size 10m,
           group 2 'd:/oralce/oradata/xxx/redo02.ora size 10m'
具体写法参照d:/oracle/ora90/rdbms/admin/build_db.sql
当然也可以用OMF定义初始化参数DB_CREATE_FILE_DEST,DB_CREATE_ONLINE_DEST_n

5、数据字典及其用途
数据字可以提供的信息包括：
逻辑和物力的数据库结构
对象定义和空间分配
完整性约束
用户
角色
权限
审计
数据字典视图分类层次：
DBA:in all schemas(访问该层需要具备DBA权限)
ALL:user can access
USER:user's schema
例如：
select * from dba_users;
select * from all_objects;
select * from user_tables;
动态性能表是在运行过程中不断被改变的，属于SYS用户拥有，不运行DML操作。
察看动态性能表
conn sys/admin as sysdba
shutdown immediate
startup nomount
select * from v$instance;(可以）
select * from v$sga;（可以）
select * from v$tablespace;（不可以,需要mount才可以）
alter database mount;
select * from v$database;（可以）
select * from v$tablespace;（可以）
select * from dba_users;(不可以，还没open)
alter database open;
select * from dba_users;(可以)
open以后还能查询数据字典和动态性能表(又称为固定表)：
desc dictionary
desc v$fixed_table
select * from dict
select * from v$fixed_table;

执行内部过程：
set serveroutput on
execute dbms_output.putline('test string1');

6、管理和维护控制文件
控制文件的作用：是一个二进制文件，定义了物理数据库的当前状态，是连接实例和数据库的桥梁。丢失控制文件需要恢复，在mount阶段才可读，连到一个控制文件只能连到单一的数据库，故应该有多路控制文件，它也维护了数据库的完整性约束，大小是在创建数据库的时候初始化，在nomount状态下才可以更变大小。
控制文件的内容：
数据库名字和ID v$database
数据库创建的时间v$database
表空间名称v$tablespace
数据和重做文件的名称和路径v$logfile
当前重做文件的序列号v$log
检查点信息
undo段的开始和结尾
redo归档信息v$archived_log;
备份信息v$backup
(默认是不备份的，即status=not active;
备份表空间：alter tablespace user begin backup;
备份后user表空间的status=active
可以用alter tablespace user end backup;中止备份)

用spfile多路控制文件步骤:
1）查看当前控制文件信息：
select * from v$controlfile;
2)alter the spfile:
alter system set control_files=
'$HOME/ORADATA/u01/ctr101.ctl',
'$HOME/ORADATA/u02/ctrl02.ct1'
scope=spfile;
3)正常关闭数据库
shutdown immediate
4)create additional control files
$ cp $HOME/ORADATA/u01/ctrl01.ctl
     $HOME/ORADATA/u02/ctrl02.ctl
5)再重起
startup

用init.ora多路控制文件步骤:
1)shutdown immediate
2)cp control01.ctl .../disk3/control02.ctl
3)add the new control file name to init.ora:
control_files=(/disk1/control01.ctl,
               /disk3/control02.ctl)
4)starup pfile=$HOME/ORADATA/initSID.ora

管理控制文件OMF
条件是初始参数文件里没有指定control_files的值；
OMF创建的控制文件路径由db_create_online_log_dest_n指定
OMF创建的控制文件名字是独一的，同时记录于alertSID.log文件中

获取控制文件信息:
select * from v$controlfile;
select * from v$parameter where name like 'control%';
show parameter control
select * from v$controlfile_record_section;

运行时备份control file:
alter databse back controlfile to 'c:/control.bak';
或者(放入oracle/admin/SID/udump,该参数位于user_dump)
alter databse back controlfile to trace;
trace文件是一个具有备份功能的脚本。

7、redo日志文件
oracle日志文件包括：
在线日志文件
归档日志文件
察看当前日志模式：archive log list
启动自动存档：alter system archive log start;
强制要求switch:alter system switch logfile;
强制要求check point 操作：
方法1）alter system checkpoint;
方法2）setting fast_start_mttr_target parameter
(mttr:mean time to recovery恢复平均时间)

redo log的目的：为一个系统错误提供了恢复机制。

redo log的结构：
分成组，1个数据库至少要2个组，每个组的每个redo log称为一个成员.redo log是按组循环使用的，当一个redo log用完了，LGWR将移动到下一个log组，这称为日志switch,检查点操作也在这个时候发生，同时信息将写入data file 以及control file.

add online redo log groups:
alter database add logfile group 6
('d:/oracle/oradata/SID/redo06a.ora',
'd:/oracle/oradata/SID/redo06b.ora')
size 10m；

add online redo log members:(这里不能指定size)
alter database add logfile member
'd:/oracle/oradata/SID/redo06c.ora' to group 6;

drop online redo log groups:
alter database drop logfile group 6;(文件并没有被删除，只是没有关联了)
删除限制条件：current log group/active log group /not archived log group

drop online redo log members:
alter database drop logfile memger
'd:/oracle/oradata/SID/redo06c.ora';
限制：除了组删除限制条件外，一个组至少要有一个member!

clearing,relocating or renaming online redo log files:
alter database clear logfile group 6;(组清空)
alter database clear logfile
'd:/oracle/oradata/SID/redo06a.ora'；（成员清空）
限制：当前组
清空没有归档的日志组:
alter database clear unarchiveed logfile 5;
relocating or renaming可以用添加成员后再删除旧成员，也可以copy一个文件后，使用rename命令：
alter database rename file
'd:/oracle/oradata/SID/redo06c.ora' to
'd:/oracle/oradata/SID/redo06d.ora'

online redo log configuration:
每个组成员最好一致；
组成员放置到不同的disk上；

用OMF管理日志
可以设置的5个参数：show parameter db_create
让系统增加一个日志组，并自动生成物理文件
alter system set
db_create_online_log_dest_1='d:/oracle/oralog';
alter database add logfile group 6;
删除日志组，同时删除日志文件：
alter database drop logfile group 6;

获取日志组/成员信息：
v$log--log group information/status(current|active|inactive|unused等)
v$logfile--status:blank-in use
                  stale-incomplet
                  delete

归档redo日志文件
功能:1)recovery2)online backup(数据库打开时备份)
数据库默认是处于非归档模式的.非归档模式只能做全备或者离线的备份.

archivelog 转换 noarchivelog步骤:
1)shutdown immediate;
2)starup mount;
3)alter database archivelog/noarchivelog;
4)alter database open;
5)full db backup

察看归档日志文件:
select * from v$archived_log;

日志挖掘步骤:
1)set directory:
utl_file_dir=d:/oracle/admin/sid/cdump
2)restartup
3)create directory file:dbms_logmnr_d.build
4)add/remove log file:dbms_logmnr.add_logfile/remove_logfile
5)start logmnr:dbms_logmnr.start_logmnr
6)v$logmnr_content--sqlredo/sqlundo

select * from v$logmnr_contents;

示例:
desc dbms_logmnr
desc dbms_logmnr_d
execute dbms_logmnr_d.build('siddirc.ora','d:/oracle/admin/sid/cdump')
execute dbms_logmnr.add_logfile('d:/oracle/oradata/sid/redocurrent.log',dbms_logmnr.new)
execute dbms_logmnr.start_logmnr(dictfilename=>'d:/oracle/admin/sid/cdump/siddic.ora')

8、表空间与数据文件

描述数据库的逻辑结构
database
tablespace--logical area
segment--specific logical structure occupied space collection,including table/index/temp/rollback extent--extent--allocated unit
block--use and store unit

查询表空间及其对应的数据文件：
select * from v$tablespace;
select * from v$datafile;
select t1.name ,t2.name
from v$tablespace t1,v$datafile t2
where t1.ts#=t2.ts#

增加表空间对应的数据文件：
alter tablespace users
add datafile 'd:/oracle/oradata/sid/users02.dbf' size 10m;

系统表空间：创建数据库时创建，包括数据字典和系统回滚段:
select * from dba_rollback_segs;
非系统表空间：隔离段，简化空间管理，控制分配给用户的空间大小：
alter user xxx quota 10M on users;

创建表空间:
create tablespace xxx
[datafile 'yyy']--OMF
[size zzz]--kb,mb
[extent management local/dictionary]
[default storage(sss)]--initial/next/pctincrease/min extents...
eg1:create tablespace userdata datafile '/u01/oradata/userdata01.dbf' size 100m
autoextend on next 5m maxsize 200m;
eg2:create tablespace kong
datafile 'd:/oracle/oradata/sid/kong.dbf' 5m;
extent management dictionary
default storage(
initial 100k
next 100k
pctincrease 10)
offline

表空间管理
1)本地管理:在位图中记录空闲分区记录,减少了数据字典表的内容,当空间分配或回收的时候不会产生undo数据,不会产生碎片压缩.包括
autoallocate 和 uniform size xxM;两种指定方式.
2)数据字典管理:默认,(不推荐)分区记录在数据字典表中,存储在表空间中的每个段可以有不同的存储参数,会产生碎片压缩.

undo tablespace:
存储undo段(注意系统回滚段是放在系统表空间的)
不能包含任何数据对象
分区是本地管理的
9i里已经是自动管理
只可以使用datafile/extent management 语句来创建:
create undo tablespace undo1
datafile '/u01/oradata/undo01.dbf' size 40m
extent management local

临时表空间:
用来支持排序
不能包括任何永久数据对象
建议使用本地管理

创建临时段空间:
create temporary tablespace kong4
temfile 'd:/oracle/oradata/sid/kong4.dbf' size 5m
extent management local;

缺省临时表空间:
允许指定数据库范围内的默认临时表空间,避免了使用系统表空间去存储数据,能够使用create database /alter database命令建立,缺省是本地管理,不能被offline,不能read only;也不能更改成永久的表空间,一旦指定就无法删除.
更改缺省临时表空间:
alter dababase default temporary tablesacpe kong4

offline状态:
使得数据无法访问
system/undo/default temporary 表空间必须online;

更变表空间状态
alter tablespace userdata offline;
alter tablespace userdata online;

更改表空间的存储设置
eg1:alter tablespace userdata
minimum extent 10;
eg2:alter tablespace kong
default storage(
initial 1m
next 1m
maxextents 10m);
本地管理的表空间参数基本是不能修改的.

只读表空间:
使得只能进行读操作,但是可以删除数据对象
alter tablespace userdata read only;

删除表空间:
从数据字典中删除表空间
从数据字典中删除内容(可选)
OS文件可以被删除(可选)
drop tablespace userdata
including contents and datafiles;

更改表空间大小
1)add a data file
alter tablespace app_data
add datafile 'd:/oracle/oradata/sid/kong1.dbf'
size 200M;
2)change the size of a data file
a)automaticlly:
alter database datafile
'd:/oracle/oradata/sid/kong.dbf'
autoexened on;
b)maunully
alter database datafile
'd:/oracle/oradata/sid/kong.dbf'
resize 200M;

移动数据文件:
表空间必须offline
目标数据文件必须存在
alter tablespace userdata
rename
datafile '/u01/oradata/userdata01.dbf'
to     '/u01/oradata/userdata02.dbf';
由于系统表空间无法offline,故要移动系统表空间
数据库必须处于mounted状态才可以.

配置OMF来创建表空间:
alter system set
db_create_file_dest='d:/oracle/oralog';
create tablespace omftb;
drop tablespace omftb;

获取表空间信息
1)tablespace info:
dba_tablespace
v$tablespace
2)datafile info:
dba_data_files
v$datafile
3)temp file info:
dba_temp_files
v$tempfile