oracle 在线重做日志(online redo log)

*
1.redo日志[在线重做日志:online redo log]
oracle日志分为三类:
(1).Alert log files — 警报日志
(2).Trace files — 跟踪日志(用户和进程)
(3).redo log — 重做日志(记录数据库的更改)
redo日志[重做日志]分为:
(1).online Redo log files — 在线重做日志,又称联机重做日志
(2).Archive Redo log files — 归档重做日志,简称归档日志
2.redo日志的组成:[日志文件为循环工作]
(1).日志组至少两个,用于切换
(2).日志文件组的个数由控制文件里的MAXLOGFILES决定
(3).1个日志组至少包含1个组成员,最多由控制文件里的maxlogmembers决定
(4).一个成员对应一个日志文件
(5).LGWR进程并发的往日志组里所有重做日志文件写入相同信息
3.日志文件信息的查看:
(1). v$log:查询在线日志的总体信息

SYS> select * from v$log;
# 注:V$LOG中STATUS的状态值
UNUSED: 从未对该联机日志写入任何内容,一般为新增加联机日志文件或是使用resetlog后的状态
CURRENT:当前重做日志文件,表示该重做日志文件为活动状态,能够被打开和关闭
ACTIVE:处于活动状态,不属于当前日志,崩溃恢复需要该状态,可用于块恢复,可能归档,也可能未归档
CLEARING:表示在执行alter database clear logfile命令后正将该日志重建为一个空日志,重建后状态变为unused
CLEARING_CURRENT:当前日志处于关闭线程的清除状态。如日志某些故障或写入新日志标头时发生I/O错误
INACTIVE:实例恢复不在需要联机重做文件日志组,可能归档也可能未归档

(2).v$logfile:查询在线日志文件信息

SYS> select * from v$logfile;
# 注:V$LOGFILE中STATUS的状态值
INVALID :表明该文件不可访问
STALE :表明文件内容不完全
DELETED : 表明该文件不再使用
NULL :表明文件正在使用

*
4.日志文件的相关操作:
(1).添加日志文件组,并指定大小
SYS>ALTER DATABASE ADD LOGFILE ‘/u01/app/oracle/oradata/orcl/redo04.log’ SIZE 1M;
(2).添加组成员到日志文件组:[这里不需要指定大小,组里的所有日志文件拥有相同的大小]
SYS>ALTER DATABASE ADD LOGFILE MEMBER ‘/u01/app/oracle/oradata/orcl/redo04-1.log’ TO GROUP 4;
(3).删除日志组:[日志文件还需手动删除]
SYS>ALTER DATABASE DROP LOGFILE GROUP 4; # 删除4号日志组
# 注意:当前日志组不能删,活动的日志组不能删,没有归档的日志组不能删
(4).删除日志组成员:[日志文件还需手动删除]
SYS>ALTER DATABASE DROP LOGFILE MEMBER ‘/u01/app/oracle/oradata/orcl/redo04-1.log’;
# 注意:每个组至少要保留一个成员;
(5).格式化日志文件[清空日志文件]:
SYS>alter database clear logfile group 2; # 清除日志组2里文件的数据
SYS>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2; # 清除没有归档的日志组2文件的数据
SYS>ALTER DATABASE CLEAR LOGFILE ‘/u01/app/oracle/oradata/orcl/redo04-1.log’; # 清除指定日志文件的数据
(6).重命名日志文件:[原来的日志文件在修改成功后,需手动删除]
a.关闭数据库
b.复制日志文件到一个新的地方
c.数据库启动到mount模式
d.重命名日志文件:
SYS>ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/orcl/redo04.log’ TO ‘/u01/app/oracle/redo7.log’;
e.打开数据库:
(7).手动日志切换
SYS>ALTER SYSTEM SWITCH LOGFILE;
# 注:日志切换就是停止写当前组,转而写一个新组,系统可以自动发生,也可以手工完成
(8).手动生成checkpoint:[触发:脏数据写入磁盘]
SYS>ALTER SYSTEM CHECKPOINT;
# 注:除了手动切换,还可以通过一些参数来控制checkpoint:[ FAST_START_MTTR_TARGET(MTTR:数据恢复的时间间隔) ]
SYS> show parameter FAST_START_MTTR_TARGET
# 注: FAST_START_MTTR_TARGET(数据库通过它来改变后面3个参数),
【 fast_start_io_target,log_checkpoint_interval,log_checkpoint_timeout 】
(9).通过 OMF 管理 REDO LOG 文件[自动管理]

    SYS> SHOW PARAEMTERS DB_CREATE_ONLINE
    SYS> ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1='/ORACLE/ORALOG/OMF';
    SYS> ALTER DATABASE ADD LOGFILE GROUP 6;(名子由 ORACLE 派生,大小100M)
    SYS> ALTER DATABASE DROP LOGFILE GROUP 6;(自己删除)

oracle控制文件(controlfile)详解

*
1.控制文件的概述:
a.是一个二进制文件,记录了数据库的物理结构,同时也包含数据文件及日志文件的信息以及相关的状态,归档信息等等,因此,非常重要。通常采用分散存储,多路复用的原则。
b.在mount阶段被读取,在open阶段被使用,因此,丢失控制文件,数据库不能启动到mount状态
c.维护数据库一致性(数据库启动时会比较控制文件与联机日志文件中的 ckpt,即起始 scn 号,如相等则正常启动,否则需要介质恢复)
d.一个控制文件只能属于一个数据库
e.控制文件的任意修改将写入到初始化参数中指定的所有控制文件中,读取时则仅读取第一个控制文件
f.控制文件只能连接一个数据库,最多为多个[官方建议3个及以上],最少一个,互为镜像,不建议存有太多控制文件,不然会影响性能[信息写到每个控制文件内]
g.控制文件的大小一般不要超过100MB,初始化大小由 CREATE DATABASE 指定,主要影响参数:MAXDATAFILES,MAXLOGFILES,MAXLOGMEMBERS,MAXLOGHISTORY,MAXINSTANCES,这几个参数越大,控制文件越大,控制文件,可以使用 RMAN 备份
2.控制文件中包含的内容:
数据库的名字、ID、创建的时间戳         # 数据库的DBID是唯一的,可以在V$database中查询DBID
表空间的名字
联机日志文件、数据文件的位置、个数、名字
联机日志的 Sequence 号码
检查点的信息
撤销段的开始或结束
归档信息
备份信息
3.控制文件的创建,存储,查询,修改等操作
(1).查询控制文件的视图:

    V$CONTROLFILE                    --列出实例中所有控制文件的名字及状态信息
    V$PARAMETER                      --列出所有数据库参数的位置及状态信息
    V$CONTROLFILE_RECORD_SECTION     --列出控制文件中记录的部分信息
    SHOW PARAMETER CONTROL_FILES     --列出控制文件的名字、状态、位置等
    show parameter spfile            --如果启动参数为spfile文件,在这里也可以看到控制文件信息

*
(2).控制文件为二进制文件,可以使用strings命令[不建议],建议使用如下方法:
a.备份控制文件到平面文件(然后查看控制文件中的具体内容)

 SYS> alter database backup controlfile to trace as '/u01/app/oracle/ctl.ctl';

b.转储控制文件内容:

    SYS> alter session set events 'immediate trace name controlf level 12';
    SYS> show parameter dump;   # 查询文件存放位置[user_dump_dest]

# 进入上面显示的路径,查看进程号相对应的文件[ll -t:最上面一个],如果不清楚,可以使用下面命令查询:

    SYS> select p.spid from v$session s,v$process p where s.paddr=p.addr and s.username='SYS';

*
(3).修改控制文件:[分为两种,一种为启动参数为PFILE,一种为启动参数为SPFILE]
# 查询当前启动参数时spfile文件或pfile文件:[value:此值为空值为PFILE文件,否则,就是SPFILE文件]

SYS> show parameter spfile;
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string

a.启动参数为spfile文件,修改控制文件[删除方法同下,只需将控制文件减少到你需要的个数,关机,删除不要的控制文件]

   SYS> alter system set control_files='控制文件1的路径','控制文件2的路径','控制文件3的路径','控制文件4的路径' scope=spfile
   SYS> shutdown immediate;  # 关闭数据库,为了保证控制文件的一致性
   cp  控制文件1   控制文件4    # 使用系统命令cp复制一个控制文件到新位置,并重命名
   SYS> startup              # 开机在查看相应控制文件参数即可

b.启动参数为pfile文件,修改控制文件

   SYS> shutdown immediate;  # 关闭数据库,为了保证控制文件的一致性
   cp  控制文件1   控制文件4    # 使用系统命令cp复制一个控制文件到新位置,并重命名
   vim initorcl.ora      # 编辑参数文件中的control_files值,添加相应控制文件的路径[initorcl.ora存储在$oracle_home/dbms/*]
   SYS> startup              # 开机在查看相应控制文件参数即可

*
(4).数据库的启动流程:
a.Unmount
启动进程
读取$oracle_home/dbs 中的 spfile 文件
[参数文件读取顺序(三个只要存在一个即可),spfileorcl.ora –> spfile.ora –> initorcl.ora]
分配 sga
启动后台进程
打开 alter.log 和 trace 文件
b.mount:[此模式下,可以做恢复]
实例和启动的数据库绑定
加载并启动控制文件
读取控制文件中包含的数据库名字和数据文件和联机重做日志的状态
b.open:[数据库做一致性检查]
开启 redo 文件和数据文件

oracle 数据字典(dictionary)

*
1.数据字典的含义:
数据字典是存放有关数据库信息的地方,其用途是用来描述数据的。
数据字典是一组视图结构。它们存放在 SYSTEM 表空间
2.oracle数据字典内容包括:
(1).数据库中所有模式对象的信息,如表、视图、簇、及索引等。
(2).分配多少空间,当前使用了多少空间等。
(3).列的缺省值。
(4).约束信息的完整性:如:user_constraints
(5).Oracle 用户的名字:如:dba_users
(6).用户及角色被授予的权限:如:user_tab_privs/role_sys_privs
(7).用户访问或使用的审计信息。
(8).其它产生的数据库信息。
3.数据字典视图的分类:
数据字典创建脚本:$ORACLE_HOME/rdbms/admin/catalog.sql
数据字典分为:静态数据字典动态数据字典
数据字典中的表是不能直接被访问的,但是可以访问数据字典中的视图
静态数据字典视图又分为三类:【不会变化的,主要由表和视图组成,比如某用户创建的表】
user_*:该视图存放关于当前用户所拥有的对象的信息,后面跟复数名词,如user_tables
all_*:该视图不仅存放当前用户的对象信息还存放被授予权限的用户对象信息,后面也跟复数名词
dba_*:存放数据库所有对象信息,后面跟复数名词,如DBA_USERS,必须具有管理员权限,才可以查看
动态数据字典视图:【虚表,信息来自于内存和控制文件,用于监控和调优】
V$:动态性能视图;
GV$:全局动态性能视图;
查询所有动态性能视图:V$fixed_table;
查询所有全局动态性能视图:GV$fixed_table;
X$表:内部 RDBMS 表,可以称为“基表”,Oracle 数据库的运行基础,在数据库启动时由 Oracle 应用程序动态创建。只允许SYSDBA 用户直接访问,仅能查询
V$FIXED_TABLE 基于 GV$FIXED_TABLE 创建。
GV$FIXED_TABLE 视图的创建基于 X$表创建
具体流程:
数据库启动时,动态创建 X$,在 X$基础上创建 GV$,在 GV$基础上创建 V$
X$表 ==> GV$(视图) ==> V$(视图)
同义词[v$/v_$/gv$/gv_$]视图之间的关系:
V$(视图)–>V_$(视图)–>V$(公用同义词)
GV$(视图)–>GV_$(视图)–>GV$(公用同义词)
注:oracle允许 V_$视图权限可以授权给其他用户,但不允许任何对于 V$视图的直接授权
数据库访问顺序:先访问视图,再访问同义词
4.查询数据库所有数据字典:
SYS> select * from dictionary;

SYS> select * from dict;
5.几个重要的视图:
v$controlfile # 控制文件信息
v$datafile # 数据文件信息
v$log # 日志文件信息
v$logfile # 日志文件信息
v$process # 进程的信息
v$session # 会话信息
v$transaction # 事务信息
v$resource # 资源信息
v$sga # 系统全局区信息
6.下面为几个列子,测试一下: # 如需查询某个数据字典,可利用LIKE进行模糊查询

  1. # 查看当前用户的角色  
  2. SYS> select * from user_role_privs;  
  3. # 查看当前用户的系统权限  
  4. SYS> select * from user_sys_privs;  
  5. # 查看当前用户的表级权限  
  6. SYS> select * from user_tab_privs;  
  7. # 查看 DBA 权限管理下的用户  
  8. SYS> select * from dba_users;  
  9. # 查看当前用户下所有的表  
  10. SYS> select * from user_tables;  
  11. # 查看表的有无主键外键和主键外键的列名  
  12. SYS> select * from USER_CONS_COLUMNS;  
  13. # 看下索引被索引的字段  
  14. SYS> select * from user_ind_columns where index_name=upper(‘&index_name’);  
  15. # 查看当前用户的视图  
  16. SYS> select view_name from user_views;  
  17. # 查看某个用户的详细信息.(归档日志是否打开等)  
  18. SYS> select * from V$database;  
  19. # 列出当前会话的详细信息  
  20. SYS> select * from v$session;  
  21.   
  22. userenv() 函数  
  23.   
  24. SYS> select userenv(‘isdba’) from dual; –当前用户是否为DBA  
  25. SYS> select userenv(‘sessionid’) from dual; –sessionid  
  26. SYS> select userenv(‘TERMINAL’) from dual; –当前在哪个终端  
  27. SYS> select userenv(‘INSTANCE’) from dual; –实例数  
  28. SYS> select userenv(‘language’) from dual; –当前数据库字符集  

7.列出部分数据字典视图:

#### 表和列  ####
DBA_TABLES / ALL_TABLES / USER_TABLES                    # 显示了有关数据库表的一般信息
DBA_TAB_COLUMNS / ALL_TAB_COLUMNS / USER_TAB_COLUMNS     # 显示了每个数据库表的列的信息
注意: DBA_OBJECTS / ALL_OBJECTS / USER_OBJECTS           # 显示了模式对象的信息,包括表
#### 完整性约束  ####
DBA_CONSTRAINTS / ALL_CONSTRAINTS / USER_CONSTRAINTS    # 显示有关约束的一般信息
DBA_CONS_COLUMNS / ALL_CONS_COLUMNS / USER_CONS_COLUMNS  # 显示有关列的相关约束的一般信息
#### 视图  ####
DBA_VIEWS / ALL_VIEWS / USER_VIEWS
注意:DBA_OBJECTS / ALL_OBJECTS / USER_OBJECTS           # 显示了模式对象的信息,包括视图
#### 序列  ####
DBA_SEQUENCES / ALL_SEQUENCES / USER_SEQUENCES
注意:DBA_OBJECTS / ALL_OBJECTS / USER_OBJECTS           # 显示了模式对象的信息,包括序列
#### 同义词  ####
DBA_SYNONYMS / ALL_SYNONYMS / USER_SYNONYMS
注意:DBA_OBJECTS / ALL_OBJECTS / USER_OBJECTS           # 显示了模式对象的信息,包括同义词
#### 索引  ####
DBA_INDEXS / ALL_INDEXS / USER_INDEXS / DBA_IND_COLUMNS / ALL_IND_COLUMNS /  USER_IND_COLUMNS
#### 用户  ####
DBA_USERS
#### 角色  ####
DBA_ROLES
#### 表空间定额   ####
DBA_TS_QUOTAS
#### 配置表  ####
DBA_PROFILES
#### 表空间  ####
DBA_TABLESPACES
#### 数据文件  ####
DBA_DATA_FILES
#### 段  ####
DBA_SEGMENTS / USER_SEGMENT
#### 回滚段  ####
DBA_ROLLBACK_SEGS / V$ROLLNAME / V$ROLLSTAT

oracle初始化参数文件详解:initorcl.ora

*
1.初始化参数文件分类:
(1).静态初始化参数文件(pfile):
名字为:init实例名.ora(initorcl.ora)
(2).动态初始化参数文件(spfile):
名字为:spfile实例名.ora(spfileorcl.ora)
2.初始化参数文件存放位置:/u01/app/oracle/product/10.2.0/db_1/dbs/
3.查看数据库目前使用的初始化参数文件:
show parameter spfile
注:如果value部分为空,则表示是使用的是静态初始化参数文件,否则为动态初始化参数文件
4.查询数据库配置参数的详细信息:

select name,value from v$parameter
select * from nls_database_parameters

注:查看数据库部件信息:dba_registry
5.pfile文件和spfile文件的创建:

create pfile from spfile # pfile文件为文本文件,可直接打开修改
create spfile from pf1ile # spfile文件为二进制文件
create spfile from memory # 在11g中可以直接从内存使用[在数据库未关闭的情况下]

*
6.初始化参数文件为SPFILE文件时,使用以下命令修改参数[如果是pfile文件,可以直接使用文本编辑器编辑]

alter system set 参数名 = 值 scope=memory|spfile|both
注:memory:立即生效; spfile:重启生效; both:兼顾两者

*
7.告警日志位置:/u01/app/oracle/admin/orcl/bdump/alert_orcl.log
8.数据库启动顺序:[查看数据库状态:v$instance]
(1).nomount:[此模式下,读取初始化参数文件,可以创建数据库,重建控制文件]
参数文件查找顺序:spfileorcl.ora –> spfile.ora –> initorcl.ora
(2).mount:[读取控制文件信息,打开文件,读入内存]
可以做恢复(完全恢复/不完全恢复)等
(3).open:[一致性检查,根据控制文件对比数据文件头部是否一致]
9.初始化参数文件详解:

  1. # 指定数据缓冲区的大小,该值越大,可以减少对数据库文件的I/O次数,提高效率  
  2. orcl.__db_cache_size=855638016  
  3. # 指定JAVA存储池的大小,用于存储java的方法、类的定义和JAVA对象  
  4. orcl.__java_pool_size=16777216  
  5. # 指定大池的大小,用于共享服务器的会话内存、并行执行的消息缓冲区以及RMAN备份和恢复的磁盘 I/O 缓冲区。  
  6. orcl.__large_pool_size=16777216  
  7. # 指定共享池的大小,用于存储共享游标、存储的过程、控制结构和并行执行消息缓冲区等对象。较大的值能改善多用户系统的性能.  
  8. orcl.__shared_pool_size=285212672  
  9. # 指定stream池的大小  
  10. orcl.__streams_pool_size=0  
  11. # 指定追踪审计文件的位置  
  12. *.audit_file_dest=’/u01/app/oracle/admin/orcl/adump’  
  13. # 指定后台跟踪文件目录  
  14. *.background_dump_dest=’/u01/app/oracle/admin/orcl/bdump’  
  15. # 版本号  
  16. *.compatible=’10.2.0.1.0′  
  17. # 指定控制文件的路径及文件名  
  18. *.control_files=’/u01/app/oracle/oradata/orcl/control01.ctl’,’/u01/app/oracle/oradata/orcl/control02.ctl’,’/u01/app/oracle/oradata/orcl/control03.ctl’  
  19. # 内核信息目录  
  20. *.core_dump_dest=’/u01/app/oracle/admin/orcl/cdump’  
  21. # 指定数据块大小,默认为8k  
  22. *.db_block_size=8192  
  23. # 指定数据库的域名加上数据库名称就构成了全局数据库名称  
  24. *.db_domain=”  
  25. # 在涉及一个完全连续扫描的一次 I/O 操作过程中读取的块的最大数量。  
  26. *.db_file_multiblock_read_count=16  
  27. # 指定数据库名称  
  28. *.db_name=’orcl’  
  29. # 实例名称  
  30. *.instance_name=orcl  
  31. # 指定闪回目录  
  32. *.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’  
  33. # 指定闪回目录大小  
  34. *.db_recovery_file_dest_size=2147483648  
  35. # 多线程服务器配置  
  36. *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)’  
  37. # 作业队列只用于复制环境。它指定每个例程的 SNP 作业队列进程的数量,要自动更新表快照或执行由 DBMS_JOB 创建的请求,请将该参数设置为 1 或更大的值。  
  38. *.job_queue_processes=10  
  39. # 指定一个会话一次可以打开的游标的最大数量为300,应将该值设置得足够高,这样才能防止应用程序耗尽打开的游标  
  40. *.open_cursors=300  
  41. # 指定pga的大小  
  42. *.pga_aggregate_target=396361728  
  43. # 指定可同时连接到一个Oracle Server上的操作系统用户进程的最大数量为150  
  44. *.processes=150  
  45. # 指定操作系统或口令文件是否具有检查用户口令的权限。设置为EXCLUSIVE, 将使用数据库的口令文件对每个具有权限的用户进行验证  
  46. *.remote_login_passwordfile=’EXCLUSIVE’  
  47. # 指定sga的大小  
  48. *.sga_target=1190133760  
  49. # 指定系统使用的撤消空间管理方式为自动  
  50. *.undo_management=’AUTO’  
  51. # 指定回滚表空间  
  52. *.undo_tablespace=’UNDOTBS1′  
  53. # 指定用户进程跟踪文件目录  
  54. *.user_dump_dest=’/u01/app/oracle/admin/orcl/udump’  

Oracle数据库参数/表空间/控制文件/数据文件/日志文件等相关信息

1.查看当前使用的是哪个数据库:【两种方式】

SYS> select INSTANCE_NAME,HOST_NAME,STARTUP_TIME,STATUS from v$instance;
INSTANCE_NAME	           HOST_NAME			STARTUP_T STATUS
---------------- ------------------------------ --------- ------------
orcl		        localhost.localdomain		22-JUN-13 OPEN

SYS> show parameter name;    #  查看相关参数
NAME				     TYPE	 VALUE
-------------------- ----------- ---------
db_file_name_convert	 string
db_name 			     string	 orcl    # 数据库的名字
db_unique_name			 string	 orcl
global_names			 boolea  FALSE
instance_name			 string	 orcl    # 实例名
lock_name_space 		 string
log_file_name_convert	 string
service_names			 string	 orcl    # 服务名

2.查看控制文件:v$controlfile # 查看控制文件的路径,文件大小等

SYS> select NAME,BLOCK_SIZE,FILE_SIZE_BLKS from v$controlfile;
NAME							                      BLOCK_SIZE FILE_SIZE_BLKS
----------------------------------------------------- ---------- --------------
/u01/app/oracle/oradata/orcl/control01.ctl		        16384		  594
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl	16384		  594

3.查看数据文件:v$datafile # 查看数据文件的相关信息

SYS> select NAME,BLOCK_SIZE,BLOCKS,BYTES from v$datafile;
NAME					                      BLOCK_SIZE     BLOCKS	 BYTES
---------------------------------------------- ---------- ---------- ---------- 
/u01/app/oracle/oradata/orcl/system01.dbf	     8192      87040     713031680 
/u01/app/oracle/oradata/orcl/sysaux01.dbf	     8192      65280      534773760 
/u01/app/oracle/oradata/orcl/undotbs01.dbf	     8192      7040       57671680 
/u01/app/oracle/oradata/orcl/users01.dbf	     8192	   640        5242880 
/u01/app/oracle/oradata/orcl/example01.dbf	     8192      12800      104857600 

4.查看日志文件:v$logfile # 查询日志文件相关信息

SYS> select STATUS,TYPE,MEMBER from v$logfile;  
STATUS	     TYPE		MEMBER
------- --------------- --------------------------------------------------
ONLINE	             	/u01/app/oracle/oradata/orcl/redo03.log
ONLINE	            	/u01/app/oracle/oradata/orcl/redo02.log
ONLINE	            	/u01/app/oracle/oradata/orcl/redo01.log

5.查看表空间,块大小:dba_tablespaces

SYS> select TABLESPACE_NAME,BLOCK_SIZE,STATUS  from dba_tablespaces;
TABLESPACE_NAME 	    BLOCK_SIZE STATUS
----------------------- ---------- ---------
SYSTEM				     8192      ONLINE
SYSAUX				     8192      ONLINE
UNDOTBS1			     8192      ONLINE
TEMP				     8192      ONLINE
USERS				     8192      ONLINE
EXAMPLE 			     8192      ONLINE

6.还有一种查看块大小的参数:

SYS> show parameter block_size;
NAME				     TYPE		  VALUE
----------------- -------------- ------------------------------
db_block_size		  integer		  8192

7.查看数据库配置参数(字符….):nls_database_parameters

SYS> select * from nls_database_parameters;

PARAMETER		             VALUE
--------------------- --------------------
NLS_LANGUAGE		       AMERICAN    # 语言
NLS_TERRITORY		       AMERICA
NLS_CURRENCY		       $
NLS_ISO_CURRENCY	       AMERICA
NLS_NUMERIC_CHARACTERS	       .,
NLS_CHARACTERSET	       WE8MSWIN1252  # 字符编码
NLS_CALENDAR		       GREGORIAN
NLS_DATE_FORMAT 	       DD-MON-RR
NLS_DATE_LANGUAGE	       AMERICAN
NLS_SORT		           BINARY
NLS_TIME_FORMAT 	       HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT     DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT	     HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT  DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY	       $
NLS_COMP		          BINARY
NLS_LENGTH_SEMANTICS	   BYTE
NLS_NCHAR_CONV_EXCP	       FALSE
NLS_NCHAR_CHARACTERSET	  AL16UTF16
NLS_RDBMS_VERSION	       11.2.0.1.0    # 数据库版本

oracle数据库体系结构(二):实例

1、oracle实例
(1).Oracle实例是 内存结构 和 进程结构(后台进程) 的集合
(2).启动Oracle实例的过程,即:分配内存、启动后台进程
(3).Oracle实例的内存包括 SGA(系统全局区域–共享) PGA(进程全局区域–私有)
当启动实例的时候分配SGA;
当服务器进程建立时分配PGA;
一般情况下,SGA:PGA=8:1分配内存。
(4).Oracle实例的后台进程包括SMON、PMON、DBWR、LGWR、ARC、CKPT等。

2、SGA(System Global Area): 系统全局区
i0、数据库信息存储于SGA,由多个数据库进程共享。
i1、SGA包括:共享池、数据缓冲区、日志缓冲区、Large池、Java池、Stream池

i2、共享池(share pool)
1)、共享池是对SQL、PL/SQL程序进行语法分析、编译、执行的内存区域。
如 在执行SELECT * FROM emp语句时,会对sql语句进行语法分析->编译->生成执行计划->运行执行计划等,这些操作都在共享池中完成;如果再次执行 SELECT * FROM emp语句时,会在共享池中查找是否有相同的sql,如果存在则省去编译、生成执行计划操作步骤而是直接运行执行计划,因此养成良好的编码习惯对于提高 oracle执行效率非常有帮助。
2)、共享池由 库缓存[sql语句的存放,解析] 和 数据字典缓存 组成。
3)、共享池的大小直接影响数据库的性能

i3、数据缓冲区(buffer cache)
1)、用于存储从磁盘数据文件中读入的数据,所有用户共享。
2)、服务器进程将读入的数据保存在数据缓冲区中,当后续的请求需要这些数据时可以在内存中找到,不需要再从磁盘读取,提高了读取速度。
3)、数据缓冲区的大小对数据库的读取速度有直接的影响。
4)、硬盘上的数据文件中的数据,如何返回给用户的?
i、从硬盘取出来,直接返回给用户,使得获取数据的效率非常低。
ii、从硬盘取出来,放到数据缓冲区,从数据缓冲区中把数据返回给用户。下次读取同样的数据的话,直接从数据缓冲区中获取,不需要再从硬盘获取。

i4、日志缓冲区(log cache)
1)、日志记录数据库的所有修改信息,日志信息首先产生于日志缓冲区。
2)、当日志缓冲区的日志数据达到一定数量时,由后台进程将日志数据写入日志文件中。
3)、相对来说,日志缓冲区对数据库的性能影响较小。
Ex:UPDATE emp SET empno=7935 WHERE empno=7934;
首先在日志缓冲区中被记录,某个时候(三种情况;一、日志缓冲区中的记录达到1M 二、每隔3秒 三、日志缓冲区已经用了三分之一,满足其中任意一项就可以)把日志缓冲区中的这些记录写到在线日志中去。

i5、Large池(large pool)
为了进行大的后台进程操作而分配的内存空间[为了缓解share pool的压力],主要指备份恢复、大型IO操作、并行查询等。

i6、Stream池(stream pool)
为了stream应用而分配的内存空间,高级复制技术一部分。

i7、JAVA池(java pool)
为了java应用而分配的内存空间。

3、PGA(Process Global Area): 程序全局区
i0、程序全局区(PGA)包含单个服务器进程所需的数据和控制信息,比如每个session传入的SQL绑定变量、以及对该session进行控制的控制信息。如果该session进行了排序或hash连接操作,也会使用PGA中的内存。
i1、PGA是在用户进程连接到数据库并创建一个会话时自动分配的,保存每个与Oracle数据库连接的用户进程所需的信息
i2、共享连接模式和专用连接模式,PGA的管理方式不同。
大多情况下,采用专用连接模式。

4、oracle进程:用户进程服务器进程(影子进程,相对用户进程而言)后台进程
(1).用户进程是一个需要与oracle服务器进行交互的程序。当用户运行一个应用程序准备向数据库服务器发送请求时,即创建了用户进程。用户进程位于客户端,服务器进程、后台进程位于服务器端。
(2).服务器进程用于处理连接到该实例的用户进程的请求。当用户连接至oracle数据库实例创建会话时,即产生服务器进程。
(3).后台进程是Oracle数据库为了保持最佳系统性能和协调多个用户请求而设置的。Oracle实例启动时即创建一系列后台进程。

5、用户进程和服务器进程
(1).对专用连接来说,用户在客户端启动了一个应用程序,例如 sql*plus,就是在客户端启动一个用户进程;与oracle服务器端连接成功后,会在服务器端生成一个服务器进程,该服务器进程作为用户进程的代理 进程,代替客户端执行各种命令并把结果返回给客户端。9i、10g、11g都使用专用连接。
(2).用户进程一旦中止,服务器进程立刻中止。
(3).SGA对所有用户共享,PGA不共享。

6、会话
(1).会话是用户与 Oracle 服务器的单个连接
(2).当用户与服务器建立连接时创建会话
(3).当用户与服务器断开连接时关闭会话
(4).从v$session中查询会话信息,SELECT * FROM v$session;

7、后台进程
i0、PMON 进程监控进程,监控用户进程是否正常。
1)、清理出现故障的进程。
2)、释放所有当前挂起的锁定。
3)、释放故障进程使用的资源。
i1、SMON 系统监控进程,实时监控整个oracle状况。
1)、在实例失败之后,重新打开数据库时自动恢复实例。
2)、整理数据文件的自由空间,将相邻区域结合起来。
3)、释放不再使用的临时段。
i2、DBWR 数据写入进程
1)、管理数据缓冲区,将最近使用过的块保留在内存中。
2)、将修改后的缓冲区数据写入数据文件中。
i3、LGWR 日志写入进程
1)、负责将日志缓冲区中的日志数据写入日志文件。
2)、系统有多个日志文件,该进程以循环的方式将数据写入文件。
i4、CKPT 检查点进程
1)、为了防止实例崩溃,为了尽快进行实例恢复的进程。
2)、检查点包括两种:完全检查点、增量检查点。
3)、完全检查点只有下面两种情况下被触发:
1.用户发出alter system checkpoint;
2.除了shutdown abort以外的其他方式正常关闭数据库。
4)、增量检查点触发情况:
1.每隔三秒
2.在线日志切换
i5、查看进程信息
1)、从v$process中查询启动的后台进程信息
2)、查看启动了几个DBWR进程[ v$process/v$bgprocess ]
select * from v$process where program like ‘%DBW%’;
查看DBWR进程个数: show parameter db_wr
注:DBWR进程个数跟脏数据的产生有一定的影响。
修改DBWR进程个数:
alter system set db_writer_processes=3 scope=memory;
注:会报”无法修改指定的初始化参数”错误,需要使用下面的方法执行
alter system set db_writer_processes=3 scope=spfile;
注:修改成功,下次启动时才生效
3)、查看启动了几个ARC进程
select * from v$process where program like ‘%ARC%’;

8、9i中的自动PGA管理
(1).前提是:必须设置初始化参数workarea_size_policy为AUTO
(2).自动PGA管理,DBA根据数据库的负载情况,估计所有的session大概需要消耗的PGA的总的大小,然后把该值设置成初始化参数pga_aggregate_target,Oracle就会自动调整每个session的PGA的大小。

9、10g中的自动SGA管理
(1).自动SGA管理,即ASMM,Automatic Shared Memory Management。只为SGA分配一个总的大小就可以了,不必像9i那样具体分配SGA中的每一个部分的大小。
(2).在设置初始化参数statistics_level为typical或all,才可以启动ASMM。然后可以使用10g提供的一个新的初始化参数sga_target来定义了整个SGA的大小。
(3).为此,10g有一个新的后台进程MMAN,即memory manager,用于进行自动SGA管理。
(4).注意另一个后台进程MMON,即memory monitor,用于自动统计信息收集。

10、11g中的自动内存管理
(1).自动内存管理,即AMM,Automatic Memory Management。只为Oracle的使用整体分配一个总的内存大小就可以了,不必像10g那样具体分配SGA和PGA的大小。
(2).初始化参数statistics_level为typical或all,才可以启动AMM。
show parameter statistics_level;   –查看AMM
show parameter memory;         –查看整个oracle占用了多大内存
alter system set memory_max_target=900m;   –设置内存最大可以达到多大
(3).新的初始化参数memory_target来定义了整个内存的大小,即SGA加上PGA的总大小。
alter system set memory_target=500m;
注:memory_target设置的值要不大于memory_max_target
(4).新的初始化参数memory_max_target来定义了memory_target最大可以达到的值。
alter system set memory_max_target=900m scope=spfile;
注:下次启动生效,scope=spfile不能省略否则报错
(5).注意:如果使用AMM,则sga_target和pga_aggregate_target的值应该设置成0。
show parameter sga_target;
show parameter page_aggregate_target;
alter system set sga_target=0m;
alter system set pag_aggregate_target=0m;
(6).11g中的后台进程MMAN,用于进行自动内存管理。

SQL语句基础

SQL基本语句:
SQL结构化查询语言的五大类:【请熟记英文】
1、select语句,select语句是数据库调优的重点部分
2、DML:数据处理语句【 data manipulation language 】 ==> 将会产生事务
主要有:insertdeleteupdatemerge
3、DDL:数据定义语句【 data define language 】
主要用于描述元数据,修改数据字典;
主要有:createalterdroptruncate
4、DCL:数据控制语句【 data control language 】
主要有:grantrevokeset
5、TCL:事务控制语句【transaction control language 】
主要有:commitrollbacksavepoint
二、DBA常用的管理工具
1、sqlplus
2、PL/SQL developer
3、toad
三、select语句的使用:
1、使用逗号分隔查询
2、分号结束查询
3、使用where子句过滤输出
4、一般使用外键进行join查询
四、select语句格式:
SELECT *|{DISTINCT} column|expression [alias],…} FROM table;
# 注解: | :表示可选参数;distinct:去除重复;alias:是对列使用别名
范例: SELECT * FROM emp;
五、演示 SQL查询语句与运算以及处理空行,处理重复的行

SQL> select salary from employees where employee_id=100;
SQL> select salary+100 from employees where employee_id=100;
SQL> select (salary+100)*2 from hr.employees where employee_id=100;

使用连接串

SQL> select last_name||job_id "Employees" from employees;
SQL> select last_name || ' is a ' || job_id "Employee Details" from employees;

desc语句用来查看表结构,实际上查询的是数据字典中的user_tab_columns的相应字段
SQL> desc employees;
关系型数据库中必须包含事务,数据字典和第三范式;第三方式可总结为列不可重复,列不可再分,行不可再分;

oracle数据库体系结构(一):数据库

1、oracle数据库体系结构数据库的体系结构是指数据库的组成、工作过程与原理,以及数据在数据库中的组织与管理机制。

2、oracle服务器
(1).oracle服务器由 oracle数据库oracle实例 组成。
(2).Oracle 数据库包括 逻辑结构物理结构
(3).oracle实例由管理数据库的 后台进程内存结构 所构成。
(4).oracle数据库是一个数据的集合,存放在数据文件里,该集合被视为一个逻辑单元。
3、oracle工作原理:
1).在数据库服务器上启动Oracle实例;<== 启动实例
2).应用程序在客户端的用户进程中运行,启用Oracle网络服务驱动器与服务器建立连接; <== 用户进程
3).服务器运行Oracle网络服务驱动器,建立专用的服务器进程执行用户进程; <==服务进程
4).客户端提交事务;
5).服务器进程获取sql语句并检查共享池中是否有相似的sql语句,如果有,服务器进程再检查用户的访问权限;否则分配新的sql共享区分析并执行sql语句;
6).服务器从实际的数据文件或SGA中取得所需数据;
7).服务器进程在SGA中更新数据,进程DBWN在特定条件下将更新过的数据块写回磁盘,进程LGWR在重做日志文件中记录事务;
8).如果事务成功,服务器进程发送消息到应用程序中。
4、oracle数据库结构[ 这里先讨论体系结构中的数据库 ]
(1).Oracle数据库由操作系统文件组成,这些文件为数据库信息提供实际物理存储区。
(2).Oracle 数据库包括 逻辑结构物理结构
物理结构包含数据库中的一组操作系统文件[如数据文件,控制文件,日志文件等];
逻辑结构指数据库创建之后形成的逻辑概念之间的关系[如表空间]。
5、物理结构物理组件就是Oracle数据库所使用的操作系统物理文件。
我们可以在/u01/app/oracle/oradata/orcl目录下进行查看,在生产环境中不同的文件放在不同的目录,根据用户访问的频繁情况来做决定。
1)、主要物理文件可分为三类:
i、数据文件:数据文件用于存储数据库数据,如表、索引数据等。
ii、控制文件:控制文件是记录数据库物理结构的二进制文件。
iii、在线日志文件:记录对数据库的所有修改信息,用于故障恢复。
注意:丢失这些文件,数据库不可以继续运行,需要进行介质恢复。
2)、非主要物理文件包括:[ 参数文件很重要 ]
参数文件、密码文件、告警和跟踪文件、归档日志文件、备份文件。
a.参数文件分为pfile文件spfile文件,位于/u01/app/oracle/product/10.2.0/db_1/dbs目录下,9i开始默认不使用sfile文件,如spfileorcl.ora文件。
b.密码文件:sys用户密码,位于/u01/app/oracle/product/10.2.0/db_1/dbs目录下,如orapworcl.ora文件。
c.查看是否有归档日志:archive log list。
ORACLE_HOME: /u01/app/oracle/product/10.2.0/db_1/
3)、查询相关文件所在位置的视图或参数:
控制文件:v$controlfile
数据文件:v$datafile
日志文件:v$logfile
查询数据库相关信息:show parameter name
注意:丢失这些文件,数据库仍然可以继续运行,不需要进行介质恢复。
6、逻辑结构 数据库的逻辑结构是从逻辑的角度分析数据库的组成。
Oracle 的逻辑组件包括:数据库->表空间->段->区->块->模式
最小单位为:区,一个区由若干个块组成,每个块由两个操作系统块组成[操作系统块为4K]
查看块的大小:show parameter db_block_size
7、表空间
i1、表空间是数据库中最大的逻辑单位,一个 Oracle 数据库至少包含一个表空间,就是名为SYSTEM的系统表空间。
i2、每个表空间是由一个或多个数据文件组成的,一个数据文件只能与一个表空间相关联。
i3、表空间的大小等于构成该表空间的所有数据文件大小之和。
i4、创建表空间的语法是:
CREATE TABLESPACE tablespacename
DATAFILE ‘filename’ [SIZE integer [K|M]]
[AUTOEXTEND [OFF|ON]];
i5、默认表空间
1)、默认包含如下表空间(从v$tablespace中查看):SYSTEM、SYSAUX、TEMP、UNDOTBS1、USERS、EXAMPLE。
2)、SYSTEM是系统表空间,存放系统的最基本的信息,如果SYSTEM表空间坏掉,Oracle将无法启动。
3)、SYSAUX从10g中引入,作为SYSTEM的辅助表空间,用以减少SYSTEM表空间的负荷。以前其他表空间中的一些组件,现在放到SYSAUX表空间中了。比如,以前SYSTEM表空间中LogMiner、以前SYSTEM表空间中 Logical Standby、Spatial、以前DRSYS表空间中Oracle Text等。
i6、TEMP是临时表空间,当排序不能在分配的空间中完成时,就会使用磁盘排序的方式,即在Oracle实例中的临时表空间中进行。
UNDOTBS1是撤销表空间,是UNDO类型的表空间,保存用户进行DML操作中,修改前的数据。
USERS是数据库默认的永久表空间。
EXAMPLE是数据库测试用例所涉及的表的所属表空间。
i7、select * from dba_data_files; –查看表空间的具体信息
select * from v$tablespace; –查看表空间
8、段、区、数据块、模式
i、段,ASSM 自动段存储管理
1)、段是构成表空间的逻辑存储结构,段由一组区组成。
2)、按照段所存储数据的特征,将段分为若干种类型,主要有数据段、索引段、回退段和临时段。
ii、区
1)、区为段分配空间,它由连续的数据块组成
2)、当段中的所有空间已完全使用时,系统自动为该段分配一个新区。
3)、区不能跨数据文件存在,只能存在于一个数据文件中
iii、数据块
1)、数据块是Oracle服务器所能分配、读取或写入的最小存储单元。
2)、Oracle服务器以数据块为单位管理数据文件的存储空间。
3)、show parameter db_block_size; –查看数据块大小[ 默认是8k ]
iiii、模式,等同于用户
1)、模式是对用户所创建的数据库对象的总称。
2)、模式对象包括表、视图、索引、同义词、序列、过程和程序包等