Oracle 数据库基础

Oracle 数据库基础知识与实践操作

Linux配置信息

1. 监听文件路径cd $ORACLE_HOME/network/admin/listener.ora
2. 网络配置文件路径cd $ORACLE_HOME/network/admin/tnsnames.ora

Linux常用命令

1. 监听操作

   su - oracle
   lsnrctl start
   lsnrctl stop

2. 管理员登录

   sqlplus / as sysdba
   shutdown  # 关闭oracle
   shutdown immediate # 立即关闭oracle
   startup # 启动oracle

建立数据库备份旧数据导入到新数据库

普通EXP IMP方式

1. 创建表空间

• 查看现在表空间路径

  select file_name , tablespace_name from dba_data_files;

• 创建表空间与临时表空间

  create temporary tablespace infotech_xw_en_db_temp 
  tempfile '/home/oracle/app/oracle/oradata/orcl/infotech_xw_en_db_temp.dbf' 
  size 100m 
  autoextend on 
  next 50m maxsize unlimited 
  extent management local; 
  create tablespace infotech_xw_en_db 
  logging 
  datafile '/home/oracle/app/oracle/oradata/orcl/infotech_xw_en_db.dbf' 
  size 100m 
  autoextend on 
  next 50m maxsize unlimited 
  extent management local; 

2. 创建新用户

create user infotech_xw_en identified by infotech15 
default tablespace infotech_xw_en_db 
temporary tablespace infotech_xw_en_db_temp; 

3. 授权

grant connect,resource to infotech_xw_en; 
grant create any view to infotech_xw_en;

4. 导出旧数据

exp infotechxw/infotech1@orcl file=/home/oracle/infotechxwbak.dmp owner=infotechxw
nohup exp infotechxw/infotech1@orcl file=/home/oracle/infotechxwbak.dmp owner=infotechxw & # 后台运行 或者命令放在sh脚本中

5. 导入到新建用户中

imp infotech/infotech1@orcl file=/home/oracle/infotechxwbak.dmp fromuser=infotechxw touser=infotech_xw_en ignore=y

需要使用dba权限用户进行导入，也可以授权infotech_xw_en权限为dba，然后用infotech_xw_en用户进行导入

IMPDP EXPDP方式

1. 建立文件存放目录并且授权

cd /home/oracle/abc
mkdir abc
create directory abc as '/home/oracle/abc'; 
grant read,write on directory abc to infotech;

2. 导出数据

expdp infotech/infotech1 schemas=infotech directory=abc dumpfile=bk.dmp
或者expdp infotech/infotech1 schemas=infotech directory=abc dumpfile=bk.dmp logfile=bk.log
chown oracle bk.dmp

3. 导入数据

REMAP_SCHEMA=source_schema:target_schema
切换用户名

impdp infotech/infotech1@orcl dumpfile=bk.dmp remap_schema=infotech:ipms

或者

impdp infotech/infotech1@orcl directory=abc dumpfile=infotech.dmp remap_schema=lteinfotech:infotech exclude=user

末尾增加exclude=user 可以忽略创建用户
如果需要切换表空间

impdp infotechhw/infotech1@orcl directory=backup dumpfile=infotech.dmp remap_schema=infotech:infotechhw remap_tablespace=infotechdb:infotechHW exclude=user

文件方式

新安装一台电脑安装环境与路径与旧数据库服务器一致，保持SID等配置一致，复制旧数据文件与控制文件/home/oracle/app/oracle/oradata/orcl到新安装数据库服务器

执行SQL文件方式

使用sqlplus执行sql时，中文有乱码解决方法
出现这种情况一般是因为Oracle客户端和服务端的字符集不一致导致的，也就是NLS_LANG环境变量和Oracle服务器端的字符集不同，可以通过修改服务端的字符集或修改NLS_LANG环境变量的值来解决
设置环境变量的方法：

1. 首先查看Oracle服务端的字符集

   SQL > select userenv('language') from dual;
   SIMPLIFIED CHINESE_CHINA.ZHS16GBK

2. export NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK 临时改变字符集。显示问题：SecureCRT终端的配置应该跟文件字符集一致才不会显示乱码：选项-会话选项-终端-外观的字符编码。SQL文件的格式应该要跟NLS_LANG对应上。如果是通过终端执行带中文的语句，就要保持SecureCRT终端编码格式跟NLS_LANG一致

   export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"　# Oracle客户端系统字符集  UTF8:SIMPLIFIED CHINESE_CHINA.AL32UTF8。ZHS16CGB231280 < ZHS16GBK < ZHS32GB18030 < AL32UTF8 左到右能显示字符集依次增加
   export LANG=zh_CN.UTF-8　# 操作系统的主语系环境

3. ~/.bash_profile 永久修改
4. 如果NLS_LANG与数据库服务端字符集是一致的还出现了乱码，可能就是SQL文件的字符集没跟NLS_LANG一致。所以建议数据库字符集、NLS_LANG、SQL文件字符集保持一致
5. 在执行导入导出时都会用到NLS_LANG字符集，会根据NLS_LANG进行转换
6. 数据库字符集应该是所有客户字符集的超集，可以避免很多错误，当然显示就会有些字符无法显示，所以尽量使用相同字符集
7. 在进行导入导出SQL的时候，SQL文件字符集必须与NLS_LANG保持一致，数据库会根据NLS_LANG与服务器编码格式对字符集进行转码存储，如果服务器是UTF8，NLS_LANG是GBK，进行导入的时候就会把导入的文件按GBK→UTF8的码表进行转码，比如“中国” → 167219 → 3224678 导出类似。（只有客户端操作系统字符集是数据库字符集子集的基础上才能正确转换，否则会出现乱码）
8. 如果导出DMP文件，导出的时候会取客户端NLS_LANG格式，导出字符集与客户端NLS_LANG一致。可以通过这种方式导出想要的字符集格式的DMP文件。

其他说明：

1. NLS_LANG环境变量中所指定的字符集其实就是数据库客户端应用程序所使用的字符集。如果客户端应用程序不支持NLS_LANG中所包含的字符集或者所支持的字符集与NLS_LANG中的字符集不兼容，则会出现乱码。
2. 此外，即使客户端字符集和数据库字符集相同，也可能会出现乱码。假如，Oracle数据库为AL32UTF8字符集，客户端使用UTF-8字符集。然而，现实情况总是很复杂的。如果客户端和数据库所支持的UNICODE标准不一样，则在很罕见的情况下仍有可能出现乱码。
3. 在查询数据时，客户端字符集应该等于数据库字符集或者是数据库字符集的超集。从而避免显示结果出现乱码的问题。
4. 在数据输入时，客户端字符集应该等于数据库字符集或者是数据库字符集的子集。从而使得输入字符能够在数据库中正确地保存。
5. 在创建数据库时，尽可能使用UNICODE字符集，从而避免字符数据不能正确存储的问题。

详细说明

PFILE与SPFILE

PFILE（Initialization Parameters Files）是文本文件的，而SPFILE（Server Parameter Files）是二进制格式的。

1. 两者可以互相转换：create pfile from spfile;
2. 查看是那种方式启动：
   SELECT NAME, VALUE, DISPLAY_VALUE FROM V$PARAMETER WHERE NAME ='spfile';如果value非空就是spfile启动;还有show parameter spfile;show parameters;也可以查看。
3. spfile参数的三种模式：
   scope=both 立即并永久生效，（默认模式）
   scope=spfile 下次启动才能生效。
   scope=memory 立即生效但下次启动时失效
4. 使用pfile/spfile启动startup pfile='XXXX\pfile\init.ora';
5. 执行startup时，按如下顺序寻找初始化参数文件：
   (1)spfile.ora
   (2)pfile.ora
   (3)都没找到，则在默认位置寻找默认名称的服务器端初始化参数文件。
   (4)还没找到，则在默认位置寻找默认名称的文本初始化参数文件。

DBA

监听信息

• lsnrctl status:查看监听,lsnrctl stop:关闭监听,lsnrctl start启动监听，会根据配置的主机名去/etc/hosts找ip进行监听，/etc/hosts可以配置成127.0.0.1 xxx
• 监听DESCRIPTION_LIST可以配置多个ip地址
• 如果配置成127.0.0.1就只能通过127.0.0.1访问，只有本机能访问
• 如果使用主机名，主机名配置了/etc/hosts127.0.0.1，对应的:::端口会被放开，外部可以访问。监听了ipv6地址默认也支持ipv4地址
• 如果配置成0.0.0.0 内外部都可以访问

查看与修改系统连接数

show parameter processes;
alter system set processes=1000 scope=spfile;

日志存放路径

• show parameter dump_dest命令查看
• select name,value from v$diag_info
• /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace路径
• /home/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/log路径
• 查询告警日志find -name "alert*.log"

修改Oracle系统默认密码过期时间

• 查看用户profile

  select username,profile from dba_users;

• 查看有效天数

  SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

• 修改密码期限

  ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME '100';--100天期限
  ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;--永久期限

手动创建新实例

存储过程

调用存储过程 call abcd();

应用版本升级ID冲突问题

比如生产环境更改开发环境的参数配置数据，增加数据字典等操作？导致我们发布的版本使用了1-5的ID。生产环境使用了6. 我们开发环境也使用了6. 最终两个环境的id冲突

解决方法，生产环境使用的主键ID段跟开发环境使用不同段，比如生产环境从200000000开始，开发环境从100000000开始。

比较有歧义的内容

Starting Redo Apply 备机执行Redo日志，三条语句的区别

To start Redo Apply in the foreground, issue the following SQL statement:前台执行
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
If you start a foreground session, control is not returned to the command prompt until recovery is canceled by another session.

To start Redo Apply in the background, include the DISCONNECT keyword on the SQL statement. For example:后台执行
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
This statement starts a detached server process and immediately returns control to the user. While the managed recovery process is performing recovery in the background, the foreground process that issued the RECOVER statement can continue performing other tasks. This does not disconnect the current SQL session.

To start real-time apply, include the USING CURRENT LOGFILE clause on the SQL statement. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

kill session,disconnect session

The KILL SESSION command doesn’t actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of “marked for kill”. It will then be killed as soon as possible.

The ALTER SYSTEM DISCONNECT SESSION syntax as an alternative method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.

The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are recovered immediately.

Data Guard Broker可以实现自动切换

性能测试

插入数据测试

create table T ( x int , y char(50), z char(50));

-- Oracle11G 存储过程，通过PLSQL Test Window可以执行。Dell730服务器 43秒执行完。字段越多速度越慢。字段不多每秒1-2万条是有的。MySQL慢很多能达到1千都很难。mysql详细例子在mysql文章
declare 
i NUMBER;
begin
for i in 1..1000000 loop
  INSERT /*+ append parallel(T, 4) nologging */ INTO T VALUES(i,i,i);
  if mod (i, 5000)=0 then -- 5000条提交一次
  commit;
  end if;
end LOOP;
END;

select * from T;

truncate table T;

常用SQL函数

• instr()函数 （俗称：字符查找函数），在字符串查找，字符串替换，字符串定位中经常组合使用
  • select substr('2020-09-09',0,instr('2020-09-09','-',-1)-1) from dual; 2020-09
  • select substr('2020-09-09',instr('2020-09-09','-',-1)+1) from dual; 09

根据条件切换select返回内容，切换返回字段

select nvl2(translate('20200101', '\1234567890', '\'), '0', '1')  from dual; -- 判断是否数字方法，数字返回1

select case nvl2(translate(note, '\1234567890', '\'), '0', '1') when '0' then note else 'is num' end AS text from item_info; -- 如果是数字返回'is num'否则返回note字段内容
select case nvl2(translate(note, '\1234567890', '\'), '0', '1') when '0' then note else substr(class_path,instr(class_path,'.',-1)+1) end AS text from item_info; --更复杂else中也加入函数

逗号分隔字符串转换成in能用的数据

-- 直接用字符串是会有问题的。需要转换成行。
SELECT REGEXP_SUBSTR('1,2,3,9','[^,]+', 1, LEVEL) FROM DUAL
CONNECT BY REGEXP_SUBSTR('1,2,3,9', '[^,]+', 1, LEVEL) IS NOT NULL

其他安装问题

• 缺失pdksh
  • 该包在RedHat上叫ksh，不是叫pdksh，可以忽略

运维

• 查询未提交事务语句：select SQL_TEXT from v$sql,v$transaction where LAST_ACTIVE_TIME=START_DATE;
• 查询锁：select * from V$LOCKED_OBJECT。 程序卡死可能是由于某些前置条件太慢导致，添加索引、分区、分表等方案解决，当然具体分析。

  select o.object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号 -- 有的环境不能使用 as xxx
  from v$locked_object l , dba_objects o , v$session s , v$process p
  where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;

• 出现过表无法添加字段问题，通过基于现有表数据重建新表后添加解决
• 查询所有表名：select * from all_tables where owner='TEST'; 用户名必须大写
• 查询正在执行的SQL 导致数据库某些表卡死：select a.username, a.sid,a.SERIAL#,b.SQL_TEXT, b.SQL_FULLTEXT from v$session a, v$sqlarea b where a.sql_address = b.address;
• 杀死SQL卡死的会话：alter system kill session '11,1234'; SID和serial# 或者linux直接kill进程号

  ps -ef|grep xxx # xxx可以是进程ID也可以是进程名称比如oracle
  kill -9 xxx # xxx是进程号 p.spid 。 如果是定时调用oracle存储过程会出现非常多的oracle进程

• 10条性能最差的SQL

  SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS, COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea order BY disk_reads DESC )where ROWNUM<10;

• 执行过的SQL,最近操作，存储的条数应该跟配置的某个存储区容量相关：

  select b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT from v$sqlarea b where b.FIRST_LOAD_TIME between '2020-01-01/00:00:00' and '2022-01-01/00:00:00' order by b.FIRST_LOAD_TIME;

修改varchar2到clob

不可用直接修改，需要通过新建字段复制旧数据到新字段的方式修改，然后删除旧字段，新字段重新命名。

数据库卸载

1.停止数据库

su - oracle
sqlplus / as sysdba
SQL>shutdown immediate
SQL>exit

或者

service oracle11g stop

2.停止https

service httpd stop

3.停止监听

lsnrctl stop
exit # 退回root用户，或者su到root用户

4.删除安装目录

rm -rf /home/oracle/app/oracle/
rm -rf /home/oracle/app/oraInventory/

5.删除bin

rm -rf /usr/local/bin/dbhome
rm -rf /usr/local/bin/oraenv
rm -rf /usr/local/bin/coraenv

6.删除oratab

rm -rf /etc/oratab

7.删除oraInst.loc

rm /etc/oraInst.loc

8.删除用户、用户组

userdel -r oracle
groupdel oinstall
groupdel dba

9.删除自启动服务

chkconfig --del oracle11g

删除归档日志

1. 删除文件find /archivelog -xdev -mtime +7 -name "*.dbf" -exec rm -f {} \;
2. 执行清空 RMAN 里留下未管理的归档文件

   rman
   RMAN> connect target /
   RMAN> crosscheck archivelog all;
   RMAN> delete expired archivelog all;

直接执行该命令并不会删除日志文件：RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

关闭归档日志

shutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;
archive log list;

日志查看

• 查看日志位置

  select * from v$diag_info;
  select * from v$logfile;

• 其他查看日志方法

  select * from v$sql; -- 最近操作
  select * from v$sqlarea; -- 最近操作

• 开启归档日志

• LogMiner分析日志

  select SUPPLEMENTAL_LOG_DATA_MIN from v$database; 
  alter database add supplemental log data;
  CREATE  USER  logminer IDENTIFIED  BY   logminer ;
  GRANT CONNECT, RESOURCE,DBA TO logminer;
  
  -- 切换到归档模式
  shutdown immediate
  startup mount;
  alter database archivelog;
  alter database open;

  cd /home/oracle/app/oracle/oradata
  mkdir logminer

  CREATE DIRECTORY utlfile AS '/home/oracle/app/oracle/oradata/logminer'; -- 删除 drop DIRECTORY utlfile;
  alter system set utl_file_dir='/home/oracle/app/oracle/oradata/logminer' scope=spfile;

  # 重启生效
  shutdown immediate;
  startup;
  show parameter utl_file_dir

  CREATE TABLE test(id varchar2(100));   
  INSERT INTO test (id) values  ('1');  
  INSERT INTO test (id) values  ('2');
  commit; -- 测试数据
  -- 分析在线日志
  EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/home/oracle/app/oracle/oradata/logminer'); -- 创建字典
  SELECT group#, sequence#, status, first_change#, first_time FROM V$log ORDER BY first_change#; -- 查看联机日志
  select * from v$logfile;
  exec dbms_logmnr.add_logfile('/home/oracle/app/oracle/oradata/orcl/redo01.log', dbms_logmnr.new); -- 在线日志加入，02,03也可以分析
  exec dbms_logmnr.start_logmnr( dictfilename=>'/home/oracle/app/oracle/oradata/logminer/dictionary.ora'); -- 启动分析
  SELECT sql_redo, sql_undo, seg_owner,TIMESTAMP FROM v$logmnr_contents WHERE seg_name='TEST' seg_owner='username'; -- 查看分析结果
  
  -- 分析归档日志
  ALTER SYSTEM SWITCH LOGFILE;
  select sequence#, FIRST_CHANGE#, NEXT_CHANGE#,name from v$archived_log order by sequence# desc; -- 查看归档日志
  exec dbms_logmnr.add_logfile('/home/oracle/app/oracle/fast_recovery_area/ORCL/archivelog/2021_04_01/o1_mf_1_748_j6dmbglm_.arc', dbms_logmnr.new);
  exec dbms_logmnr.start_logmnr( dictfilename=>'/home/oracle/app/oracle/oradata/logminer/dictionary.ora'); 

权限查看

-- DBA_* 描述的是数据库中的所有对象
-- ALL_* 描述的是当前用户有访问权限的所有对象
-- USER_* 描述的是当前用户所拥有的所有对象

select * from user_users; -- 查看当前用户详细信息

select * from user_role_privs;  --- 当前用户被授予的角色
select * from dba_role_privs;    --- 全部用户被授予的角色
select * from dba_roles;             --- 查看所有角色
select * from ROLE_ROLE_PRIVS;   --- 查看所有角色被赋予的角色

select * from session_privs; -- 当前用户所拥有的全部权限
select * from user_sys_privs; -- 当前用户具有的系统权限
select * from dba_sys_privs; -- 所有用户具有的系统权限

select * from dba_tab_privs; -- 查看所有用户的对象权限
select * from user_tab_privs; -- 当前用户的对象权限

-- directory
select * from all_directories; -- 查询当前directory目录权限所有文件名和路径
select * from all_directories where directory_name = 'INFOTECH';  --查询单个用户的路径和文件名， 目录文件名要大写，严格区分大小写 
select * from all_tab_privs where table_name = 'DIRECTORY_NAME'; -- 查看对Directory的授权信息

性能优化

• 增加索引

  create index IDX_TEST on H_TEST (ID, CREATE_AT)
  tablespace TEST_DB
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
  );

• 增加表分区（按时间等创建分区）

  partition by range (CREATE_AT)
  INTERVAL ( numtodsinterval (1, 'day') )
  (
  partition PART_01 values less than (TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
  );

• 最好建表的时候考虑好，建好表分区，不然后面数据量大了再增加还得迁移数据，还是比较麻烦的。对于频繁调用与数据量大的表应该考虑优化。
• oracle在没建分区、有建索引的情况，对于千万级别数据的表加减法处理还是比较快的。千万级别表抽取出百万级数据处理后更新回去估计要几分钟。

查询优化

in 大字符串参数，可以改成 exist instr等

错误

安装失败

/home/oracle/diag/rdbms/orcl/orcl/trace
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace
/home/oracle/app/oraInventory/logs/installActions*

tail -f /home/oracle/app/oraInventory/logs/installActions2022-02-12_10-08-19AM.log # 查看安装过程，可以使用静默安装

查看错误日志。

错误：软件包：glibc-2.17-317.el7.i686 需要：glibc-common

Oracle依赖包可以通过yum安装，也可以自己下载离线安装包。

yum源没有对应版本包匹配

wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
sed -i  's/$releasever/7/g' /etc/yum.repos.d/CentOS-Base.repo
yum repolist

# 重新执行
yum install elfutils-devel

70%出现Error in invoking target ‘agent nmhs’ of makefile

(1) cd /home/oracle/app/oracle/product/11.2.0/dbhome_1/sysman/lib/
(2) cp -p ins_emagent.mk ins_emagent.mk.bak #备份原文件
(3) vi ins_emagent.mk #修改文件
(4) 进入vi编辑器后 命令模式输入/NMECTL 或者:176进行查找，快速定位要修改的行。在后面追加参数-lnnz11， 第一个是字母l，后面两个是数字1。即将
$(MK_EMAGENT_NMECTL)改为
$(MK_EMAGENT_NMECTL) -lnnz11
(5):wq保存修改，回到安装界面，点击Retry按钮

ora-00119和ora-00132问题的解决方法

cat /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

vi /home/oracle/app/oracle/admin/orcl/pfile/init.ora.1122022101754
local_listener="(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))" # 修改为tnsnames.ora配置的值

startup pfile='/home/oracle/app/oracle/admin/orcl/pfile/init.ora.1122022101754'

参考

• 如何设置Oracle数据库客户端字符集以及系统中的NLS_LANG环境变量
• Oracle之 dmp导入/导出、数据库操作等过程中的字符集问题
• 彻底搞懂Oracle字符集
• linux中手工新建oracle实例
• oracle打补丁教程
• Rebuild the DataPump packages in Oracle
• ORA-31626: job does not exist and How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS