数据库物理结构
1. 参数文件
1. 参数类型
查看参数最好是直接show parameter 参数,参数可以进行模糊匹配
# 作用:记录oracle参数信息,类似mysql的my.cnf,在启动阶段读取初始化参数文件(init parameter files)。该文件管理实例相关启动参数。
# 参数类型大致以下几种:
基本初始化参数 大约 10-20 个左右(见联机文档)
初始化参数 300 个左右
隐含参数 Oracle 不推荐使用
总的来说也就是普通参数和隐含参数
# 查看普通参数:
select * from v$parameter;
# 动态参数和静态参数,及文件
动态参数:可以直接在内存中修改,并对当前 instance 立即生效
静态参数:必须修改参数文件,下次启动后生效
# 区分是静态参数还是动态参数:根据 ISSYS_MODIFIABLE 的值判断
SQL> select distinct issys_modifiable from v$parameter;
ISSYS_MOD
---------
IMMEDIATE #动态参数
DEFERRED #静态参数
FALSE #延迟参数,下次session连接有效
#实例
SQL> select name,value,ISSYS_MODIFIABLE from v$parameter where name ='undo_retention';
NAME VALUE ISSYS_MODIFIABLE
--------------- -----------------------------------
undo_retention 900 IMMEDIATE
2. 参数文件
# 参数文件可以分为文本参数文件(pfile)和 二进制参数文件(spfile)
# 静态参数文件
记录Oracle参数文本文件(默认已经不使用),默认的名称为“init+实例名.ora”,这是一个文本文件,可以用任何文本编辑工具打开。
# 位置
$ORACLE_HOME/dbs/init.ora
# 动态参数文件:记录Oracle参数二进制文件(默认使用的参数文件)
spfile 默认的名称为“spfile+例程名.ora”这是一个二进制文本形式存在,不能用vi编辑器对其中参数进行修改。
# 查看当前数据库使用的参数文件
SQL> show parameter spfile
# 两种参数文件:pfile和spfile
1. pfile(parameter file)
特点:
①必须通过文本编辑器修改参数,便于一次修改多个参数。
②缺省的路径及命名方式:$ORACLE_HOME/dbs/initSID.ora
2. spfile(system parameter file)
特点:
①二进制文件,不可以通过编辑器修改。通过 Linux 命令 strings 可以查看内容。
②路径及命名方式: $ORACLE_HOME/dbs/spfileSID.ora
# 参数的修改
1. 修改spfile的方法:
alter system set 参数=值 [scope=memory|spfile|both]
①scope=memory 参数修改立刻生效,但不修改 spfile 文件。
②scope=spfile 修改了 spfile 文件,重启后生效。
③scope=both 前两种都要满足。要求 spfile 参数文件存在
④不写 scope 限定词,缺省both。但不如③严格,即 spfile 如果不存在,仅仅修改内存中参数。
# 示例
SQL> select name,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='sql_trace';
NAME ISSES_MODIFIABLE ISSYS_MODIFIABLE
------------ ---------------- ----------------
sql_trace TRUE IMMEDIATE
#这个结果表示 sql_trace 参数在 session 级别可以改,在 system 级也可以 both 修改(动态参数)。
alter system set sga_target=400M ; ---动态参数
alter system set memory_max_target=600M scope=spfile; --静态参数
alter system set sga_target=400M scope=memory;
ALTER SYSTEM SET parameter_name=parameter_value scope=[memory|spfile|both]
# 参数文件的生成和相互转换
读取顺序:优先 spfile,其次 pfile
pfile 和 spfile 可以相互生成:
SQL>create pfile from spfile
SQL>create spfile from pfile
SQL>create pfile from memory;
SQL>create spfile from memory;
# 示例
#动态参数文件生成静态参数文件:
create pfile='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initweisi.ora' from spfile;
#静态参数文件生动态态参数文件:
create spfile from pfile ='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/pfile.ora';
注意:使用 spfile 启动后不能重写 spfile
尽可能使用 spfile,pfile 一般留做备用,特殊情况也可以使用 pfile 启动:
SQL> startup pfile=$ORACLE_HOME/dbs/initprod.ora
---如果 pfile 不是缺省命名或放在其他路径,则指定命令路径和文件名即可。
SQL> startup pfile=/home/oracle/mypfile
---怎样知道实例是 spfile 启动还是 pfile 启动的
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/oracle/dbs/spfile.ora
---如果 value 有值,说明数据库启动时读的是 spfile
---另一个办法是看 v$spparameter(spfile 参数视图)中的参数 memory_target 的 isspecified 字段值,如果是 TRUE 说明是 spfile 启动的
SQL> select name,value,isspecified from v$spparameter where name like 'memory_target';
NAME VALUE ISSPECIFIED
--------------- -----------------------------
memory_target 1528823808 TRUE
2. 控制文件
控制文件是数据库中最重要的文件,它是一个二进制文件,它记录了数据库名、实例信息、数据文件和日志文件的名字位置、建库日期等。一般由系统安装时自动创建。
实例启动时,先加载参数文件,然后分配sga内存,这时实例处于安装状态,然后访问控制文件,读出数据文件和日志文件信息,再打开数据文件和日志文件供用户访问。
# 存放路径
控制文件存放的路径由参数文件SPFILEsid.ora和crontrol参数值来确定。
# 查看控制文件信息可以在v$controlfile数据字典
col name for mat a60;
select name from v$controlfile;
3. 数据文件
数据文件是用与保存应用程序数据和系统内部数据的文件,在操作系统中是普通文件。oracle逻辑上由表空间组成,每个表空间可以包含一个或多个数据文件,一个数据文件只能属于一个表空间。
当修改数据时,也不会立即写入数据文件,而是先保存在内存的数据缓冲区,由检查点机制去触发后台进程DBWR去写入到相应数据文件。也就是脏数据落盘,是checkpoint激活时会触发数据库写进程(DBWR),将数据缓冲区里的脏数据块写到数据文件中。通过这样的方式,减少磁盘IO,提供系统性能。
# 查看数据文件信息,或者在操作系统目录查看,可以通过dba_data_files或v$datafile数据字典
SQL> col file_name for a50
SQL> set linesize 100;
SQL> select file_name,tablespace_name from dba_data_files;
SQL> select FILE#,name from v$datafile;
# 四种类型数据文件:
系统数据文件(system01.dbf、sysaux01.dbf)
回滚数据文件(undotbs01.dbf)
用户数据文件(users01.dbf)
临时数据文件(temp01.dbf)
系统数据用来管理用户数据和Oralce本身的数据,如用户建立表的名字、列的名字、字段类型等,这些数据被自动存放在系统表空间对应的system01.dbf数据文件中。oracle系统内部的数据字典、系统表等所存储的数据属于oracle系统内部数据,也存储在系统表空间对应的数据文件中。
回滚数据文件(撤销数据文件)用于临时存储修改前的旧数据
用户数据文件用于存储用户应用系统的数据,包括与应用系统相关的所有相关信息
临时数据文件用于存放和排序相关的数据,临时表数据文件和一般数据文件被Oracle做了区分,不能在dba_data_files中查询到。
# 临时数据文件查看,可以通过dba_temp_files或者v$tempfile数据字典查询
SQL> col file_name format a50
SQL> col tablespace_name format a20
SQL> select file_name,tablespace_name from dba_temp_files;
SQL> select name from v$tempfile;
4. 日志文件
日志文件主要功能是记录对数据所做的修改,出现问题时可以通过日志文件得到原始数据,从而不丢失操作成果。
日志文件主要分为两种:
重做日志文件(redo log file)
归档日志文件(archive log file)
对应着非归档模式和归档模式:
非归档模式:系统运行期间,所产生的日志信息不断记录到日志文件组中,所有重做日志组被写满后,又从第一个日志组开始覆写;
归档模式:各个日志文件都被写满而即将覆盖前,先由归档进程(ARCH)将即将覆盖的日志文件中的日志信息读出,然后写入归档日志文件中,这个过程被称为归档操作。
1. 重做日志文件
# 重做日志文件,也叫联机重做日志文件(online redo log file),用来记录数据库发生过的更改信息(修改、添加、删除)以及oracle内部行为(创建表、索引等)而引起的数据库变化信息。
在数据库恢复时,可以从该日志读取原始记录。每次用户commit时,数据库都先将原始记录通过数据库自己的方法记录在日志文件中,只有写入日志文件成功时,才会将新纪录传回给用户。所以可以通过日志文件读取原始记录或恢复数据。
当对表或表空间设置nologging属性时,对其的dml、ddl操作,不生成日志。
# 查看日志文件信息
SQL> col member for a50;
# 查看当前运行情况
select GROUP#, SEQUENCE#, MEMBERS, STATUS, ARCHIVED from v$log;
GROUP# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------------- ---
1 10 1 INACTIVE YES
2 11 1 INACTIVE YES
3 12 1 CURRENT NO
# 查看文件路径
SQL> select group#,status,type,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/ORCL/redo03.log
2 /u01/app/oracle/oradata/ORCL/redo02.log
1 /u01/app/oracle/oradata/ORCL/redo01.log
# oracle示例运行中产生的日志信息,首先被临时的存放在sga的重做日志缓冲区中(redo log buffer,sga中循环使用的一段内存区域),当发出commit命令(或日志缓冲区满)或是达到一些出触发条件时,例如:
用户提交
重做日志缓冲区满1/3时
有大于1M的重做日志缓冲区未被写入磁盘(不同版本可能不同,隐含参数_LOG_IO_SIZE控制)
每隔3 秒钟
DBWR 需要写入的数据的SCN大于LGWR记录的SCN,DBWR 触发LGWR写入。
LGWR进程将日志信息从重做日志缓冲区中读出,写入日志组中序列较小的文件里,在一个日志组写满后接着写入另一个日志组。在LGWR将所有能用的日志文件都使用过后,将会覆写第一个日志组。
2. 重做日志文件管理
1. 重做日志组管理
# 添加
SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/orcl/redo01_2.log','/u01/app/oracle/oradata/orcl/redo01_2.log') size 500M;
SQL> alter database add logfile ('/u01/app/oracle/oradata/orcl/redo01_2.log','/u01/app/oracle/oradata/orcl/redo01_2.log') size 500M;
# 验证是否添加成功
select * from v$logfile;
# 删除联机重做日志组
SQL> alter database drop logfile group 4,group 5;
2. 重做日志成员及维护
# 向重做日志组1、2、3添加一个重做日志成员
alter database add logfile member
'/oradata01/mlbdb1/redo03_c.log' to group 3,
'/oradata01/mlbdb1/redo02_c.log' to group 2,
'/oradata01/mlbdb1/redo01_c.log' to group 1;
# 删除日志组中的一个日志成员
alter database drop logfile member '/oradata01/mlbdb1/redo01_c.log';
# 验证是否删除成功
SQL> select * from v$logfile where group#=1;
3. 重建重做日志组1并修改重做日志文件的大小
# 修改大小
alter database add logfile group 1
(
'/oradata01/mlbdb1/redo01_a.log',
'/oradata01/mlbdb1/redo01_b.log'
) size 1024m;
# 验证是否添加成功,以及日志成员的大小
select group#,sequence#,bytes,members,status from v$log;
4. 清除、检查点
# 清除日志
alter database clear logfile group n;
# 日志切换
alter system switch logfile;
# 发生检查点
alter system checkpoint;
3. 归档日志文件
LOG_ARCHIVE_DEST:指定归档文件存放的路径,该路径只能是本地磁盘,默认为’’。
LOG_ARCHIVE_DEST_n:默认值为’’。Oracle最多支持把日志文件归档到10个地方,n从1到10。归档地址可以为本地磁盘,或者网络设备。
DB_RECOVERY_FILE_DEST:指定闪回恢复区路径。
三者关系:
1、 如果设置了DB_RECOVERY_FILE_DEST,就不能设置LOG_ARCHIVE_DEST,默认的归档日志存放于DB_RECOVERY_FILE_DEST指定的闪回恢复区中。可以设置LOG_ARCHIVE_DEST_n,如果这样,那么归档日志不再存放于DB_RECOVERY_FILE_DEST中,而是存放于LOG_ARCHIVE_DEST_n设置的目录中。如果想要归档日志继续存放在DB_RECOVERY_FILE_DEST中,可以通过如下命令:alter system set log_archive_dest_1=’location=USE_DB_RECOVERY_FILE_DEST’;
2、 如果设置了LOG_ARCHIVE_DEST,就不能设置LOG_ARCHIVE_DEST_n和DB_RECOVERY_FILE_DEST。如果设置了LOG_ARCHIVE_DEST_n,就不能设置LOG_ARCHIVE_DEST。也就是说,LOG_ARCHIVE_DEST参数和DB_RECOVERY_FILE_DEST、LOG_ARCHIVE_DEST_n都不共存。而DB_RECOVERY_FILE_DEST和LOG_ARCHIVE_DEST_n可以共存。
3、 LOG_ARCHIVE_DEST只能与LOG_ARCHIVE_DUPLEX_DEST共存。这样可以设置两个归档路径。LOG_ARCHIVE_DEST设置一个主归档路径,LOG_ARCHIVE_DUPLEX_DEST设置一个从归档路径。所有归档路径必须是本地的。
4、 如果LOG_ARCHIVE_DEST_n设置的路径不正确,那么Oracle会在设置的上一级目录归档。比如设置LOG_ARCHIVE_DEST_1=’location=C:\archive1’,而OS中并没有archive1这个目录,那么Oracle会在C盘归档。
# 由于所有的日志文件被写入一遍后,LGWR将覆写第一个日志组,从而导致一部分较早的日志信息被覆盖。所以数据库开启归档后,这些将被覆盖的日志将被保存在归档日志中。
在归档操作过程中,LGWR进程需要等待ARCH进程结束才能开始覆写日志文件,这也就延迟了系统响应时间,同时归档也占用了大量磁盘空间
# 查看是否开启归档
SQL> col name format a30;
SQL> select dbid,name,log_mode from v$database;
--或着
SQL> archive log list;
# 查看归档路径
SQL> archive log list;
--或者
SQL> set pagesize 30;
SQL> show parameter log_archive_dest;
# 修改为归档模式
(1)关闭数据库
shutdown immediate;
(2)启动到mount
startup mount;
(3)修改归档
alter database archivelog;
(4)将数据库打开
alter database open;
# 手动归档
alter system swicth logfile;
alter system archive log current;
(5)归档默认存储位置(闪回区),尽量不使用此路径,使用log_archive_dest_n
SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oracle/fast_recovery_area
db_recovery_file_dest_size big integer 2000M
(7)修改归档默认存储位置
SQL> alter system set log_archive_dest_1='location=/archlog/mlbdb1' scope=spfile;
SQL> alter system set log_archive_format='mlbdb1_%t_%s_%r.dbf' scope=spfile;
5. 密码文件、告警文件、跟踪文件
1. 跟踪文件
# 跟踪文件包括下面一些文件
1)用户进程跟踪文件(Udump)
用于记载与用户进程相关的信息,主要跟踪sql语句
_ora_14778.trm,例如:orcl_ora_14778.trc
2)后台进程的跟踪文件(Bdump)
用于后台进程的警告和错误信息
_processname_PID.trc 如:_orcl_lgwr_15941.trc
每个后台进程都有对应的后台进程跟踪文件,而系统会将着错误的用户和后台进程的trc文件,分别复制一份到user_dump_dest和background_dump_dest,所以并不是网上说的background_dump_dest和user_dump_dest来决定,这可能和版本有关系。
另外增加.trm(trace map)文件,记录 trc 文件的结构信息
# 每个后台进程都有对应的后台进程跟踪文件
# 查看跟踪文件路径
SQL> select name,value from v$diag_info where name='Diag Trace';
NAME VALUE
---------------------------------------------------------------- --------------------------------------------------------------------------------
Diag Trace /u01/app/oracle/diag/rdbms/orcl/orcl/trace
2. 口令(密码)文件
# 密码文件有时也被称为口令文件,是oracle系统用于验证具有sysdba系统权限的用户远程登陆的认证,它是一个二进制文件。
以19c为例,默认存储在dbs目录下,密码格式为orapw
# 创建密码文件语法
orapwd file=<filename> password=<password>
#由于版本不同,越老的版本语法会有差异,查看帮助即可
orapwd --help
#可能需要的参数
format=12 #默认12.2,使用12可以忽略新特性高要求的密码复杂度
force=y #覆盖
例子:
#删除后无法远程登陆
rm -f orapworcl
#重建
orapwd file=/u01/app/oracle/product/19.3/dbhome_1/dbs/orapworcl password=Oracle123 format=12
它受参数remote_login_passwordfile影响
SQL> show parameter remote_login_passwordfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile
它的值对应的效果:
EXCLUSIVE:(默认值)独占模式使用密码文件,官档中提到了“only one instance of one database”使用exclusive方式,在数据库中是可以执行对于sysdba用户的增加,修改,删除动作的,同样也可以修改sysdba用户的密码,这些更改会记录到密码文件中去。
1.查看被授予sysdba权限的用户
SELECT USERNAME FROM V$PWFILE_USERS WHERE SYSDBA='TRUE';
2.口令文件中添加/删除sysdba权限用户
REVOKE SYSDBA FROM non-SYS-user;
GRANT SYSDBA TO non-SYS-user;
NONE:禁用口令文件验证
设置成none将直接禁用口令文件验证,sysdba用户只能通过操作系统认证登录数据库。此时的登录将会收到报错:
# Oracle寻找口令文件的顺序:orapw$ORACLE_SID --> orapw --> Failure
3. 告警文件
# 告警文件(告警日志文件)是一个存储在oracle系统目录下的文本文件,名字为alert_.log,它用来记录Oracle运行信息和错误信息。运行信息包括实例的启动和关闭,建立表空间、增加数据文件等以及有关数据库物理结构变化的信息;错误信息包括实例启动失败,扩展空间失败和一些内部错误信息等。
# 告警文件位置:
background_dump_dest和user_dump_dest是带错误的确定告警文件和跟踪文件的归档
在 oracle 11g 中,故障诊断及跟踪的文件路径改变较大,告警文件分别 xml 的文件格式和普通文本格式两种形式存在。这两份文件的位置分别是 V$DIAG_INFO 中的 Diag Alert 和 Diag Trace 对应的目录中取来的。
# 跟踪文件和文本格式的告警文件放在同一文件夹
SQL> select * from V$DIAG_INFO;
XML格式的就存放在Diag Alert中,文本格式的告警文件就在Diag Trace中
# 其他
show parameter dump_dest;
show parameter diagnostic; //查看ADR base
select name,value from v$diag_info; //查看告警文件存储位置
alter system set diagnostic_dest = '/u02/app/oracle'; //修改诊断(错误)日志文件位置
ALTER SYSTEM|SESSION SET ENABLE_DDL_LOGGING=TRUE; // 开启DDL日志记录