大菜狗の博客 - 维护类
type: channel
link: https://auxos.cn/category/ops/
items: Oracle数据库自启动,DG配置和备份脚本
items: Oracle11g单节点库通过搭建DG RAC的方式迁移至集群环境
items: NBU 异机恢复 rman-06026 man-06023 异常处理
items: 归档日志清理
items: zabbix通过ODBC驱动监控Oracle
items: Oracle 备份脚本
items: RMAN详解
items: Oracle检查死锁
items: Oracle 部分报错处理
items: Oracle 部分命令介绍

Oracle数据库自启动,DG配置和备份脚本
type: item
date: 2024-09-14T10:28:00+08:00
description: 数据库配置自启动修改/etc/oratab文件# # This file is used by ORACLE utilities. It is created by root.sh # and updated by either Database Configuration Assistant while creating # a database or ASM Configuration Assistant while creating ASM instance. # A colon, ':', is used as the field terminator. A new line terminates # the entry. Lines beginning with a pound sign, '#', are comments. # # Entries are of the form: # $ORACLE_SID:$ORACLE_HOME:<N|Y>: # # The first and second fields are the system identifier and home # directory of the database respectively. The third field indicates # to the dbstart utility that the database should , "Y", or should not, # "N", be brought up at system boot time. # # Multiple entries with the same $ORACLE_SID are not allowed. # # {ORACLE_SID}:{ORACLE_HOME}:Y #增加该行内容 sid 和 home替换为数据库的信息Oracle用户下修改"$ORACLE_HOME/bin/dbstart" 和 "$ORACLE_HOME/bin/dbshut".............. # Set path if path not set (if called from /etc/rc) SAVE_PATH=/bin:/usr/bin:/etc:${PATH} ; export PATH SAVE_LLP=$LD_LIBRARY_PATH # First argument is used to bring up Oracle Net Listener #ORACLE_HOME_LISTNER=$1 #注释掉改行 ORACLE_HOME_LISTNER=$ORACLE_HOME #添加该内容 if [ ! $ORACLE_HOME_LISTNER ]; then echo "Since ORACLE_HOME is not set, cannot auto-start Oracle Net Listener." echo "Usage: $0 ORACLE_HOME" else ..............root用户下增加以下内容到/etc/rc.localsu - oracle -lc "/u01/app/oracle/product/19.0.0/dbhome_1/bin/lsnrctl start" #启动监听 su - oracle -lc /u01/app/oracle/product/19.0.0/dbhome_1/bin/dbstart #启动数据库/etc/rc.local文件需要给可执行权限chmod +x /etc/rc.localDG库实现启动后自动应用归档日志Oracle用户下创建脚本文件/home/oracle/del_arch/autoapply.sh#!/bin/sh # 检查数据库是否启动的函数 check_db_is_up() { sqlplus -silent / as sysdba << EOF SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF SELECT 1 FROM DUAL; EXIT; EOF } # 循环直到数据库启动 while ! check_db_is_up; do echo "Database is not up yet. Waiting for 10 seconds..." sleep 10 done # 数据库已启动，设置环境变量并执行恢复脚本 export ORACLE_SID=webemr sqlplus / as sysdba << EOF >> /home/oracle/del_arch/autoapply.log 2>&1 …

Oracle11g单节点库通过搭建DG RAC的方式迁移至集群环境
type: item
date: 2024-08-14T16:31:00+08:00
description: 一、环境配置1.1、安装软件包1.1.1、配置本地源注:能通过公网安装软件包的情况下跳过该项mv /etc/yum.repos.d/* /tmp/ echo "[local_yum]" >> /etc/yum.repos.d/henry.repo echo "name = henry_repo" >> /etc/yum.repos.d/henry.repo echo "baseurl = file:///mnt/" >> /etc/yum.repos.d/henry.repo echo "enabled = 1" >> /etc/yum.repos.d/henry.repo echo "gpgcheck = 0" >> /etc/yum.repos.d/henry.repo mount /dev/cdrom /mnt/1.1.2、安装软件包yum -y install bc binutils elfutils-libelf elfutils-libelf-devel fontconfig-devel gcc gcc-c++ glibc glibc-devel ksh libgcc libstdc++ libstdc++-devel libaio libaio-devel libXtst libX11 libXau libxcb libXi libXrender make smartmontools sysstat kmod* sysstat unixODBC unixODBC-devel libnsl lm_sensors-libs ksh make glibc-devel libaio-devel libstdc++-devel nano需要单独安装的软件包yum -y localinstall https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/compat-libcap1-1.10-7.el7.x86_64.rpm yum -y localinstall https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/sysstat-10.1.5-20.0.3.el7_9.x86_64.rpm yum -y localinstall https://mirrors.aliyun.com/centos/7/os/x86_64/Packages/compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm yum -y localinstall http://vault.centos.org/5.11/os/x86_64/CentOS/pdksh-5.2.14-37.el5_8.1.x86_64.rpm1.2、内核参数调整,执行以下代码块自动完成export shmall=$(echo "`cat /proc/meminfo | grep "MemTotal" | awk '{print $2}'` / (`getconf PAGESIZE` / 1024)" | bc) export shmax=$(echo "`cat /proc/meminfo | grep "MemTotal" | awk '{print $2}'` * 1024 * 0.8" | bc | sed 's#\..*$##') echo """kernel.shmall = $shmall kernel.shmmax = $shmax kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 6815744 fs.aio-max-nr = 1048576 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 104857 vm.swappiness = 0 vm.dirty_background_ratio = 3 vm.dirty_ratio = 80 vm.dirty_expire_centisecs = 500 vm.dirty_writeback_centisecs = 100 net.ipv6.conf.all.disable_ipv6 = 1 net.ipv6.conf.default.disable_ipv6 = 1 net.ipv6.c…

NBU 异机恢复 rman-06026 man-06023 异常处理
type: item
date: 2024-04-10T17:23:00+08:00
description: 异常信息rman异机恢复时，报错RMAN-06026、RMAN-06023，如下所示：敏感信息做脱敏处理RMAN> run { ALLOCATE CHANNEL CH10 TYPE 'sbt_tape'; ALLOCATE CHANNEL CH11 TYPE 'sbt_tape'; ALLOCATE CHANNEL CH12 TYPE 'sbt_tape'; ALLOCATE CHANNEL CH13 TYPE 'sbt_tape'; ALLOCATE CHANNEL CH14 TYPE 'sbt_tape'; SEND 'NB_ORA_SERV=nbuServer, NB_ORA_CLIENT=xxxdb'; set newname for datafile 1 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 2 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 3 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 4 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 5 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 6 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 7 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 8 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 9 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 10 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 11 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 12 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 13 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 14 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 15 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 16 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 17 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 18 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 19 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 20 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 21 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 22 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 23 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 24 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 25 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 26 to '/u01/app/oracle/o…

归档日志清理
type: item
date: 2024-02-19T17:19:00+08:00
description: 归档日志及备份清理相关命令//检查所有归档日志 CROSSCHECK ARCHIVELOG ALL; //删除三天前的归档日志 DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-3'; //验证所有归档日志的备份是否有效 BACKUP VALIDATE ARCHIVELOG ALL; //检查备份一致性 CROSSCHECK BACKUP; //删除无效的备份 DELETE NOPROMPT EXPIRED BACKUP; //退出 EXIT;Windows下自动清理归档日志创建脚本执行命令注意修改脚本中的位置rman target / nocatalog log D:\arch\clear_arch.log cmdfile=D:\arch\clear_arch.txt创建一个文件存放清理归档日志的命令D:\arch\chear_arch.txt 文件位置根据情况选择CROSSCHECK ARCHIVELOG ALL; DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-3'; delete expired archivelog all; EXIT;windows 下打开 任务计划程序创建基本任务填写任务名称选择触发时间选择启动程序程序选择bat脚本直至完成运行任务测试结果

zabbix通过ODBC驱动监控Oracle
type: item
date: 2024-01-25T10:01:00+08:00
description: zabbix ODBC 驱动监控Oracle本文档参考位置Zabbix官网odbc文档 Oracle monitoring and integration with ZabbixOracle官网odbc文档 Oracle Instant Client ODBC 安装说明 | Oracle 中国目标数据库创建用于监视Oracle DB的用户以及赋权CREATE USER zabbix IDENTIFIED BY password; -- Grant access to the zabbix user. GRANT CONNECT, CREATE SESSION TO zabbix; GRANT SELECT ON v$instance TO zabbix; GRANT SELECT ON v$database TO zabbix; GRANT SELECT ON v$sysmetric TO zabbix; GRANT SELECT ON v$system_parameter TO zabbix; GRANT SELECT ON v$session TO zabbix; GRANT SELECT ON v$recovery_file_dest TO zabbix; GRANT SELECT ON v$active_session_history TO zabbix; GRANT SELECT ON v$osstat TO zabbix; GRANT SELECT ON v$restore_point TO zabbix; GRANT SELECT ON v$process TO zabbix; GRANT SELECT ON v$datafile TO zabbix; GRANT SELECT ON v$pgastat TO zabbix; GRANT SELECT ON v$sgastat TO zabbix; GRANT SELECT ON v$log TO zabbix; GRANT SELECT ON v$archive_dest TO zabbix; GRANT SELECT ON v$asm_diskgroup TO zabbix; GRANT SELECT ON sys.dba_data_files TO zabbix; GRANT SELECT ON DBA_TABLESPACES TO zabbix; GRANT SELECT ON DBA_TABLESPACE_USAGE_METRICS TO zabbix; GRANT SELECT ON DBA_USERS TO zabbix;lynx监控系统安装odbc包软件包对应选择数据库版本的yum -y install unixODBC unixODBC-devel rpm -ivh oracle-instantclient19.14-basic-19.14.0.0.0-1.x86_64.rpm rpm -ivh oracle-instantclient19.14-devel-19.14.0.0.0-1.x86_64.rpm rpm -ivh oracle-instantclient19.14-odbc-19.14.0.0.0-1.x86_64.rpm rpm -ivh oracle-instantclient19.14-sqlplus-19.14.0.0.0-1.x86_64.rpm修改配置文件## 修改Oracle tnsname配置文件 ## 编辑配置文件，在安装的版本路径下修改以下文件 ## vi /usr/lib/oracle/<version>/client64/network/admin/tnsnames.ora orcl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <IPAddress>)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) oracle = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <IPAddress>)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oracle) ) )修改系统环境变量增加以下内容 ## vi /etc/profile export ORACLE_HOME=/usr/lib/oracle/<version>/client64 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/…

Oracle 备份脚本
type: item
date: 2024-01-25T10:00:00+08:00
description: 完整备份run { #### 分配备份通道 allocate channel c1 type disk; allocate channel c2 type disk; # 备份数据文件 backup as compressed backupset database format 'D:\rmanbackup\db_%T_%U.bak'; # 备份数据文件完成后，关闭所有通道 release channel c1; release channel c2; # 重新分配通道备份控制文件和 SPFILE allocate channel c3 type disk; backup current controlfile format 'D:\rmanbackup\ctl_%T_%U.bak'; backup spfile format 'D:\rmanbackup\sp_%T_%U.bak'; # 释放备份控制文件和 SPFILE 的通道，然后重新分配通道用于 crosscheck 和 delete 操作 release channel c3; # 分配新通道 c4 用于 crosscheck 和 delete 操作 allocate channel c4 type disk; crosscheck archivelog all; crosscheck backup; # 备份所有归档日志文件 backup as compressed backupset archivelog all format 'D:\rmanbackup\arch_%T_%U.bak'; # 清理指定时间之前的归档日志和备份文件 delete noprompt archivelog all completed before 'sysdate-7'; delete noprompt backup completed before 'sysdate-1'; release channel c4; }增量备份run { # 分配备份通道 allocate channel c1 type disk; allocate channel c2 type disk; # 备份数据文件 backup incremental level 1 as compressed backupset database format 'D:\rmanbackup\inc1_db_%T_%U.bak'; # 备份数据文件完成后，关闭所有通道 release channel c1; release channel c2; # 重新分配通道备份控制文件和 SPFILE allocate channel c3 type disk; backup current controlfile format 'D:\rmanbackup\ctl_%T_%U.bak'; backup spfile format 'D:\rmanbackup\sp_%T_%U.bak'; # 释放备份控制文件和 SPFILE 的通道，然后重新分配通道用于 crosscheck 和 delete 操作 release channel c3; # 分配新通道 c4 用于 crosscheck 和 delete 操作 allocate channel c4 type disk; crosscheck archivelog all; crosscheck backup; # 备份所有归档日志文件 backup as compressed backupset archivelog all format 'D:\rmanbackup\arch_%T_%U.bak'; # 清理指定时间之前的归档日志和备份文件 delete noprompt archivelog all completed before 'sysdate-7'; delete noprompt backup completed before 'sysdate-1'; release channel c4; }差异备份run { # 分配备份通道 allocate channel c1 type disk; allocate channel c2 type disk; # 备份数据文件 backup incremental level 1 differential as compressed backupset database format 'D:\rmanbackup\diff_db_%T_%U.bak'; # 备份数据文件完成后，关闭所有通道 release channel c1; release channel c2; # 重新分配通道备份控制文件和 SPFILE all…

RMAN详解
type: item
date: 2024-01-25T10:00:00+08:00
description: RMAN详解一、备份与RMAN备份的概念数据库完全备份：按归档模式分为归档和非归档归档模式 打开状态，属于非一致性备份 关闭状态，可以分为一致性和非一致性非归档模式打开状态，非一致性备份无效 关闭状态，一致性备份，非一致性备份不被推荐RMAN备份RMAN使用会话来完成备份操作，从RMAN客户端连接到服务器将产生一个服务器会话RMAN备份内容包括：整个数据库,表空间,数据文件,指定的数据文件,控制文件,归档日志文件,参数文件等RMAN备份的类型完整备份(full)或增量备份(incremental)一致性备份(consistent)或不一致性备份(inconsistent)热备(open)或冷备(closed)，冷备时数据库必须处于mount状态，冷备可以为一致性备份或非一致性备份完整备份一个或多个数据文件的一个完整副本,包含从备份开始处所有的数据块.完整备份不能作为增量的基础增量备份包含从最近一次备份以来被修改或添加的数据块.可以分为差异增量备份和累计增量备份差异增量备份仅仅包含n级或n级以下被修改过的数据块。备份数据量小，时间长。累计增量备份仅仅包含n-1级或n-1级以下被修改过的数据块。备份数据量大，恢复时间短。0级增量备份相当于一个完整备份,该备份包含所有已用的数据块文件,与完整备份的差异是完整备份不能用作级增量备份的基础一致性备份备份所包含的各个文件中的所有修改都具备相同的系统变化编号(system change number，SCN)。也就是说，备份所包含的各个文件中的所有数据均来自同一时间点。一致性数据库完全备份(consis-tent whole backup)进行还原(restore)后，不需要执行恢复操作(recovery)非一致性备份在数据库处于打开(open)状态时，或数据库异常关闭(shut down abnormally)后，对一个或多个数据库文件进行的备份。非一致性备份需要在还原之后进行恢复操作备份集与镜像副本备份集是包含一个或多个数据文件,归档日志文件的二进制文件的集合.备份集由备份片组成,一个备份集中可以包含一个或多个备份片可以通过filesperset参数来设置备份集中可包含的备份片数，也可以设定参数maxpiecesize来制定每个备份片的大小。备份集中空闲的数据块将不会被备份，因此备份集可以支持压缩。备份集支持增量备份，可以备份到磁盘或磁带。镜像副本是数据文件或归档日志文件等的完整拷贝,未经过任何压缩等处理,不能备份到磁带,也不支持增量备份。恢复时可以立即使用实现快速恢复。等同于操作系统的复制命令。可以作为级增量备份。备份路径可以备份到磁盘目录可以备份到磁带闪回区备份限制数据库必须处于mount或open状态不能备份联机日志在非归档模式下仅仅能作干净备份，即在干净关闭且启动到mount状态下备份在归档模式下，current状态下数据文件可以备份二、使用RMAN进行备份1、备份数据库查看数据库当前工作模式,非ARCHIVELOG模式更改为ARCHIVELOG模式SQL> select dbid,name,log_mode from v$database;------查看当前数据库是不是archivelog模式 DBID NAME LOG_MODE ---------- --------------------------- ------------------------------------ 2816063020 ORADB NOARCHIVELOG SQL> shutdown immediate------关闭immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup nomount------启动到nomount模式 ORACLE instance started. Total System Global Area 1426059296 bytes Fixed Size 8896544 bytes Variable Size 352321536 bytes Database Buffers 1056964608 bytes Redo Buffers 7876608 bytes SQL> alter database mount;------挂载数据库 Database altered. SQL> archive log list------归档日志列表 Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/19c/…

Oracle检查死锁
type: item
date: 2024-01-25T10:00:00+08:00
description: 检查死锁检查死锁用sysdba用户执行以下语句select username,lockwait,status,machine,client_info,program from gv$session where sid in (select session_id from gv$locked_object);## 如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明： ## ## Username：死锁语句所用的数据库用户； ## ## Lockwait：死锁的状态，如果有内容表示被死锁。 ## ## Status： 状态，active表示被死锁 ## ## Machine： 死锁语句所在的机器。 ## ## Program： 产生死锁的语句主要来自哪个应用程序查看被死锁的语句select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object));死锁的解决办法查死锁进程SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;kill死锁的进程alter system kill session ‘sid,serial#’; (其中sid=l.session_id) ## 举例 sql > alter system kill session '71,1';生成Kill Session语句SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock" FROM v$session WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);开启session记录IP地址create or replace trigger login_on_record_ip after logon on database begin dbms_application_info.set_client_info(sys_context('userenv', 'ip_address')); end; / select username,lockwait,status,machine,client_info,program from v$session where sid = 1155; select trigger_name from all_triggers where trigger_name like '%RECORD%';杀掉所有客户端名称包含doctor.exe的sessionSELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "killcmd" FROM gv$session WHERE program like '%doctor.exe%'; set pagesize 1000 linesize 1000; spool conninfo select count(*), machine, program, username, osuser from gv$session group by machine, program, username, osuser order by count(*) desc; spool off exit

Oracle 部分报错处理
type: item
date: 2024-01-25T09:59:00+08:00
description: Oracle报错处理处理Oracle数据库启动报错 ORA-27104报错内容：system-defined limits for shared memory was misconfigured查看报错日志（我的日志位置，根据自身环境确定）cat /u01/app/oracle/diag/rdbms/oradb/oradb/alert/log.xml错误内容如下：<msg time='2021-02-20T02:44:26.252-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_id='oracle' host_addr='fd4a:8abb:ad4::cfb' pid='3810'> <txt>System cannot support SGA size of 2352 MB. </txt> </msg> <msg time='2021-02-20T02:44:26.252-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_id='oracle' host_addr='fd4a:8abb:ad4::cfb' pid='3810'> <txt>Total system memory configured is 1838 MB. </txt> </msg> <msg time='2021-02-20T02:44:26.252-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_id='oracle' host_addr='fd4a:8abb:ad4::cfb' pid='3810'> <txt>Instance maximum shared memory size should be less than 1654 MB. </txt>我的环境中，根据报错可以看出配置的SGA相关配置的内存太大了，要求小于1654MB解决办法：数据库不能启动，需要调整编辑参数文件来修改相关的SGA值我的spfile文件位置是：/u01/app/oracle/product/19c/dbs[oracle@oracle dbs]$ pwd /u01/app/oracle/product/19c/dbs [oracle@oracle dbs]$ ls hc_oradb.dat init20210220.ora init.ora lkORADB orapworadb spfileoradb.ora [oracle@oracle dbs]$先生成一个临时spfile文件来修改配置SQL> create pfile='/u01/app/oracle/product/19c/dbs/init20210220.ora' from spfile; File created. SQL> !vi /u01/app/oracle/product/19c/dbs/init20210220.ora打开后配置如下：oradb.__data_transfer_cache_size=0 oradb.__db_cache_size=1778384896 oradb.__inmemory_ext_roarea=0 oradb.__inmemory_ext_rwarea=0 oradb.__java_pool_size=0 oradb.__large_pool_size=16777216 oradb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment oradb.__pga_aggregate_target=822083584 oradb.__sga_target=2466250752 oradb.__shared_io_pool_size=134217728 oradb.__shared_pool_size=520093696 oradb.__streams_pool_size=0 oradb.__unified_pga_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/oradb/adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='/u01/app/oracle/oradata/ORADB/control01.ctl','/u01/app/oracle/oradata/ORADB/control02.ctl' …

Oracle 部分命令介绍
type: item
date: 2024-01-25T09:59:00+08:00
description: Oracle学习记录表空间和数据文件表空间分类系统表空间system表空间system表空间用来存储整个数据库的数据字典表（data dictionary table），该表空间不能被损坏，一旦损坏，数据库将无法打开。SQL> select ts#,name from v$tablespace where name='SYSTEM'; TS# NAME ---------- -------------------------------------------------------------------------------- 0 SYSTEM SQL>sysaux表空间从oracle 10g开始，oracle将工具放到SYSAUX,减轻system的压力。SYSAUX表空间不影响系统，但是会影响性能。SQL> select ts#,name from v$tablespace where name='SYSAUX'; TS# NAME ---------- -------------------------------------------------------------------------------- 1 SYSAUX SQL>非系统表空间undoundo表空间中会自动分配undo段，这些undo段用来保存事务中的DML语句的undo信息，也就是来保存数据在被修改之前的值。undo数据用来：1）事务的回滚；2）实例恢复(回滚)；3）一致性读时需要构造CR块；SQL> select ts#,name from v$tablespace where name='UNDOTBS1'; TS# NAME ---------- -------------------------------------------------------------------------------- 2 UNDOTBS1 SQL> show parameter undo_tablespace; NAME TYPE VALUE ------------------------------------ -------------------- ------------------------------ undo_tablespace string UNDOTBS1 SQL>查看回滚段信息：SQL> select * from v$rollname; USN NAME CON_ID ---------- -------------------- ---------- 0 SYSTEM 0 1 _SYSSMU1_1261223759$ 0 2 _SYSSMU2_27624015$ 0 3 _SYSSMU3_2421748942$ 0 4 _SYSSMU4_625702278$ 0 5 _SYSSMU5_2101348960$ 0 6 _SYSSMU6_813816332$ 0 7 _SYSSMU7_2329891355$ 0 8 _SYSSMU8_399776867$ 0 9 _SYSSMU9_1692468413$ 0 10 _SYSSMU10_930580995$ 0 11 rows selected. SQL>temptemp表空间，即临时表空间，用来存放用户排序，分组等操作时的数据信息。SQL> select ts#,name from v$tablespace where name='TEMP'; TS# NAME ---------- -------------------- 3 TEMP SQL>users从oracle 10g开始，oracle将用户数据信息单独存放到users表空间中。SQL> select ts#,name from v$tablespace where name='USERS'; TS# NAME ---------- -------------------- 4 USERS SQL>用户表空间SQL> select ts#,name from v$tablespace where name not in('SYSTEM','SYSAUX','TEMP','USERS','UNDOTBS1'); TS# NAME ---------- -------------------- 6 HR SQL>表空间操作创建表空间SQL> create tablespace ORA_TEST_DAT datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF' size 1G autoextend on next 200M maxsize 2G; Tabl…