Oracle物理结构（各类文件）详解

最新推荐文章于 2024-07-02 19:19:21 发布

renle0216

最新推荐文章于 2024-07-02 19:19:21 发布

阅读量951

点赞数

文章标签： oracle 数据库 postgresql

本文链接：https://blog.csdn.net/u012132164/article/details/128836677

版权

数据库物理结构

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日志记录