Oracle 数据库基础

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

Linux配置信息

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

Linux常用命令

  1. 监听操作

    1
    2
    3
    su - oracle
    lsnrctl start
    lsnrctl stop
  2. 管理员登录

    1
    2
    3
    4
    sqlplus / as sysdba
    shutdown # 关闭oracle
    shutdown immediate # 立即关闭oracle
    startup # 启动oracle

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

普通EXP IMP方式

1. 创建表空间

  • 查看现在表空间路径

    1
    select file_name , tablespace_name from dba_data_files;
  • 创建表空间与临时表空间

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    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. 创建新用户

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

3. 授权

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

4. 导出旧数据

1
exp infotechxw/infotech1@orcl file=/home/oracle/infotechxwbak.dmp owner=infotechxw

5. 导入到新建用户中

1
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. 建立文件存放目录并且授权

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

2. 导出数据

1
2
3
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
切换用户名

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

或者

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

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

1
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服务端的字符集

    1
    2
    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一致

    1
    2
    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 内外部都可以访问

查看与修改系统连接数

1
2
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

    1
    select username,profile from dba_users;
  • 查看有效天数

    1
    SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
  • 修改密码期限

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

手动创建新实例

应用版本升级ID冲突问题

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

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

比较有歧义的内容

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

1
2
3
4
5
6
7
8
9
10
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

1
2
3
4
5
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可以实现自动切换

性能测试

插入数据测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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;

参考