Oracle 日常运维操作

备份

每台 Oracle 机器都已配置了每天凌晨 03:03 开始自动做备份。

[oracle@oracle-test-001:/home/oracle]
$ crontab -l
3 3 * * * sh /data/backup/backup.sh

开发童鞋若需临时备份，在需要备份的机器上直接跑一次备份脚本即可。

备份示例：

su - oracle
sh /data/backup/backup.sh

脚本正常执行完毕后，可以到存放备份的目录下在检查一下备份文件。

备份脚本 backup.sh 示例:

$ cat /data/backup/backup.sh
#!/bin/bash
DATE=`date +%d`
echo $DATE
export ORACLE_APP=/data/app/oracle
export ORACLE_BASE=$ORACLE_APP
export ORACLE_HOME=/data/app/oracle/product/11.2.0/db_1
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:/usr/lib:/usr/local/lib
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

del_date=`date -d "7 days ago" +%d`
echo $del_date
rm -f /data/backup/*$del_date.dmp

exp USER_ONE/USER_ONE  file=/data/backup/USER_ONE-$DATE.dmp owner=USER_ONE
exp USER_TWO/USER_TWO  file=/data/backup/USER_TWO-$DATE.dmp owner=USER_TWO

启动和关闭

启动：

su - oracle
sqlplus / as sysdba
startup

用参数文件启动 Oracle，比如：

SQL> startup pfile='/data/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora'

关闭：

su - oracle
sqlplus / as sysdba
shutdown immediate;

启动和关闭监听(oracle用户下执行)：

lsnrctl start
lsnrctl stop
lsnrctl status

删除用户和表空间

drop   user  wac_tmp CASCADE;
drop tablespace wac_tmp including contents cascade constraints;

创建用户和表空间

创建表空间：

create tablespace  wac_tmp 
datafile '/data/oradata/orcl/wac_tmp.dbf' 
size 100M
reuse
 autoextend on
    next 100M
    maxsize unlimited;

创建用户：

create user wac_tmp identified by "1" default tablespace wac_tmp;

删除用户和表空间

对于单个 user 和tablespace 来说，可以使用如下命令来完成。

1、删除 user ：

drop user ×× cascade

说明：删除了 user，只是删除了该 user 下的 schema objects ，是不会删除相应的 tablespace 的。

2、删除 tablespace

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

用户授权

-- Grant/Revoke role privileges 
grant connect,resource to 用户名;

-- Grant/Revoke system privileges 
grant create any sequence to 用户名;
grant select any table to 用户名;
grant update any table to 用户名;
grant unlimited tablespace to 用户名;
grant select on dba_tables to 用户名;
grant read, write on directory data_pump_dir to 用户名;

用户解锁

查看当前被锁账户及锁定时间：

select username,account_status,lock_date from dba_users;

若有业务用户被锁定，可以这样解锁：

alter user username account unlock;

用户已经被锁住了，一般是用户错误的密码尝试次数太多，我们也可以修改允许的最大密码输入错误次数，具体修改方法如下：

1、查看当前设置值

select * from dba_profiles where RESOURCE_NAME='FAILED_LOGIN_ATTEMPTS';

2、修改为30次

alter profile default limit FAILED_LOGIN_ATTEMPTS 30;

3、修改为无限次（为安全起见，不建议使用）

alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

修改用户密码

ALTER USER user_name IDENTIFIED BY new_password;

密码过期处理

使用 sqlplus 登录管理员账户，查看指定概要文件(如default)的密码有效期设置为 180 天：

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

PROFILE                RESOURCE_NAME            RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT                PASSWORD_LIFE_TIME       PASSWORD
180

将密码有效期由默认的 180 天修改成无限制，执行：

alter profile default limit password_life_time unlimited;

修改之后不需要重启动数据库，会立即生效。这会让密码没有过期限制。

11g 之前的 Oracle 版本的默认 profile 是没有密码过期限制的，而在 Oracle 11g 中，默认情况下 profile 启用的密码过期时间是 180 天。若登录账号的密码已经超过 180 天没有修改了，就会出现提示密码过期的问题，原来的账户就不能登录到 Oracle 数据库了。

另 2 种解决办法：

使用sqlplus登录出问题账户，会提示设置新密码，此时重新输入密码即可，新密码可以和原来的密码相同。
使用sqlplus登录管理员账户，执行 alter user 账户名 identified by 新密码，新密码可以和原来的密码相同。

查看锁表情况

查看锁表情况：

select t2.username, t2.sid,t2.serial#,t2.LOCKWAIT,t2.logon_time,t3.object_name,t1.LOCKED_MODE from v$locked_object t1, v$session t2, dba_objects t3 where t1.session_id = t2.sid and t1.object_id = t3.object_id order by t2.logon_time;

或

select t2.username,t2.sid,t2.serial#,t1.LOCKED_MODE,t2.logon_time 
from v$locked_object t1,v$session t2 
where t1.session_id=t2.sid order by t2.logon_time
/

查看锁表语句：

假设 sid 为 1306：

SELECT  sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),DECODE(sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.sid = '1306')   ORDER BY piece ASC;

查出原因后，解除数据库中被锁住的表(SID,SERIAL)：

SID=39，SERIAL=1390

alter system kill session '39,1390’;

查看连接数

select * from v$session where username is not null

select username,count(username) from v$session where username is not null group by username

查看不同用户的连接数:

select count(*) from v$session

并发连接数:

Select count(*) from v$session where status='ACTIVE'

查看大表有哪些

账户隔离，先切换到用户下，然后查看。

比如：查看 zilingzhi 用户下大于 10000 行的表

SQL> conn  zilingzhi/zilingzhi
SQL> select table_name,num_rows from user_tables where num_rows>10000;

以此类推。

查看表属于哪个用户

select owner from dba_tables where table_name=upper('表名');

这个要求当前登录的用户权限为 dba 或有查询这个视图的权限才行。
栗子：

SQL> select owner from dba_tables where table_name=upper('G_USERDATA_HISTORY');

OWNER
------------------------------
GENE_ICON2
GENE_ICON1

查看表结构

desc table_name

SQL> SET LONG 9999
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','table_name') FROM DUAL;

查看字符集

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

修改字符集

比如，将数据库的字符集修改为：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

1、关闭数据库，修改 oracle 用户的环境变量

shutdown immediate
vim  /home/oracle/.bash_profile 

将
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
修改为：
export NLS_LANG="Simplified Chinese_china”.ZHS16GBK

2、让修改的参数生效

source /home/oracle/.bash_profile

3、启动数据库

su - oracle
sqlplus / as sysdba
startup

查看表结构及大小

查看表结构:

desc tablename;

查看表大小（M）:

select segment_name,bytes/1024/1024 from dba_segments where segment_name in('G_USERDATA_HISTORY','G_AGENT_STATE_HISTORY','G_CALL_HISTORY','G_IR_HISTORY','G_PARTY_HISTORY');

truncate 表操作

conn usr_tmp1/usr_tmp1
truncate table     usr_tmp1.USERDATA_HISTORY;

查看用户权限

查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：

select * from dba_sys_privs;
select * from user_sys_privs;

查看角色(只能查看登陆用户拥有的角色)所包含的权限：

select * from role_sys_privs;

查看一个用户的所有系统权限(包含角色的系统权限)：

select privilege from dba_sys_privs where grantee='DATAUSER'  
union  
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='DATAUSER' );

导入和导出

通过 exp 和 imp 导出导入：

exp(导出dmp):

exp TMP_USER/1  file=/data/backup/TMP_USER-$DATE.dmp owner=TMP_USER

imp(导入dmp):

imp TMP_USER/1     file=/data/backup/TMP_USER-26.dmp

EXP  有三种主要的方式（完全、用户、表） 
A、完全：EXP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y 如果要执行完全导出，必须具有特殊的权限 
B、用户模式：EXP SONIC/SONIC    BUFFER=64000 FILE=C:\SONIC.DMP OWNER=SONIC 这样用户SONIC的所有对象被输出到文件中。 
C、表模式：EXP SONIC/SONIC    BUFFER=64000 FILE=C:\SONIC.DMP OWNER=SONIC TABLES=(SONIC) 这样用户SONIC的表SONIC就被导出

IMP 具有三种模式（完全、用户、表） 
A、完全：IMP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y
B、用户模式：IMP SONIC/SONIC BUFFER=64000 FILE=C:\SONIC.DMP FROMUSER=SONIC TOUSER=SONIC 这样用户SONIC的所有对象被导入到文件中。必须指定FROMUSER、TOUSER参数，这样才能导入数据。 
C、表模式：IMP SONIC/SONIC    BUFFER=64000 FILE=C:\SONIC.DMP OWNER=SONIC TABLES=(SONIC) 这样用户SONIC的表SONIC就被导入。

Oracle 11g 可以通过expdp/impdp导入导出

expdp 导出：

源库：SID=PROD2
用户：test/oracle
表：t1
sqlplus / as sysdba
建立导出目录（建立的目录在系统层面必须存在）并赋读写权限：
create directory exp_dir as '/home/oracle/backup';
grant read,write on directory exp_dir to public;

系统下（Oracle用户）进行导出：
expdp system/oracle file=t1.dmp directory=exp_dir schemas=test;

impdp 导入:

目标库：SID=PROD1
用户：test/oracle
原有表：test
注意：
1.按用户导出导入，目标库不须先建立相应用户，impdp导入会自动创建相应用户。如果系统中已有该用户，会把表导入到该用户下，并且有对该表原有的操作权限。
2.把导出来的dmp文件拷贝到目标库的任意一个目录（比如在/home/oracle/backup 目录）并在数据库中创建导入目录并赋权

create directory exp_dir as '/home/oracle/backup';
grant read,write on directory exp_dir to public;

系统下（Oracle用户）进行导入：
impdp system/oracle file=t1.dmp directory=exp_dir schemas=test;

注意：导入时不加路径会报错
提示导入成功后登陆到数据库进行验证：
sqlplus / as sysdba
conn test/oracle
select count(*) from t1;
发现t1表已经导入到目标库test用户下了，导入完成。

修改 undo，temp 表空间

当undo表空间容量不足时，可以考虑增加新的数据文件或改变数据文件的大小。

将 undo tablespace 里面的 undo.dbf 文件扩充为 10000M

alter database datafile '/data1/oradata/center/undo.dbf' resize 10000M;

将临时数据文件大小调整为2G

ALTER DATABASE TEMPFILE  '/data1/oradata/center/temp.dbf' RESIZE 2G;

为 undo tablespace 添加新的数据文件

alter tablespace UNDOTS add datafile '/data1/oradata/center/undotbs02.dbf' size 20000M;

表空间扩展，增加表空间数据文件（ORA-01653）

比如已有表空间 KTS_kd_sale_dx 写满了，需要给表空间 KTS_kd_sale_dx 增加数据文件，以便业务可以继续写入。

查看表空间对应的 datafile 的信息:

SELECT file_name,  
       tablespace_name,  
       bytes / 1024 / 1024    "bytes MB",  
       maxbytes / 1024 / 1024 "maxbytes MB"  
FROM   dba_data_files  
WHERE  tablespace_name = 'KTS_kd_sale_dx';  


FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME      bytes MB maxbytes MB
------------------------------ ---------- -----------
/data01/oradata/jinzheng/KTS_kd_sale_dx_01.DBF
KTS_kd_sale_dx          1323     0

/data01/oradata/jinzheng/KTS_kd_sale_dx_02.DBF
KTS_kd_sale_dx      1657.625     0

/data01/oradata/jinzheng/KTS_kd_sale_dx_03.DBF
KTS_kd_sale_dx        1267.5     0

查看表空间对应的datafile是否可以自动扩展（当然有些场景开启自动扩展功能可能会带来一些潜在的问题，所以不是所有的公司都开启这个）：

SELECT file_id,  
       file_name,  
       tablespace_name,  
       autoextensible,  
       increment_by  
FROM   dba_data_files  
WHERE  tablespace_name = 'KTS_kd_sale_dx'  
ORDER  BY file_id DESC;

添加数据文件：

alter   tablespace   KTS_kd_sale_dx  add   datafile   '/data01/oradata/jinzheng/KTS_kd_sale_dx_04.DBF'  
size   5G   autoextend   on  next 100m  maxsize   20G;
alter   tablespace   KTS_kd_sale_dx  add   datafile   '/data01/oradata/jinzheng/KTS_kd_sale_dx_05.DBF'  
size   5G   autoextend   on  next 100m maxsize   20G;
alter   tablespace   KTS_kd_sale_dx  add   datafile   '/data01/oradata/jinzheng/KTS_kd_sale_dx_06.DBF'  
size   5G   autoextend   on  next 100m   maxsize   20G;

该 tablespace创建时创建为普通表空间，普通表空间最大限制是32G，写满 32G 了就会报错 ORA-01653 无法继续写入。此时可以用如上方法，增加数据文件的方式以让业务先继续写入。

一般大数据量的用户所用表空间，可以创建为 bigfile tablespace ，这样一般不会有上面的问题。

create bigfile tablespace :

create bigfile tablespace  KTS_kd_sale_dx datafile '/data1/oradata/center/KTS_kd_sale_dx.dbf' size 50000M autoextend on next 10M maxsize unlimited;

删除数据文件：

 alter tablespace KTS_kd_sale_dx drop datafile '/data01/oradata/jinzheng/KTS_kd_sale_dx_03.DBF' ;

Tips:

在Oracle 10g中，推出了Bigfile tablespace的概念。表空间Tablespace从Oracle 10g以后就分为两个类型，smallfile tablespace和bigfile tablespace。过去一个表空间对应多个数据文件我们称为Smallfile Tablespace。

所谓Bigfile Tablespace最显著的差别就是一个表空间只能对应一个数据文件。Bigfile Tablespace虽只对应一个数据文件，但数据文件对应的最大体积大大增加。传统的small datafile每个文件中最多包括4M个数据块，按照一个数据块8K的大小核算，最大文件大小为32G。每个Small Tablespace理论上能够包括1024个数据文件，这样计算理论的最大值为32TB大小。而Bigfile Datafile具有更强大的数据块block容纳能力，最多能够包括4G个数据块。同样按照数据块8K计算，Bigfile Datafile大小为32KG=32TB。理论上small tablespace和big tablespace总容量相同。

查看 Oracle 最耗时的 SQL

查看总消耗时间最多的前 10 条 SQL 语句:

select *
  from (select v.sql_id,
               v.child_number,
               v.sql_text,
               v.elapsed_time,
               v.cpu_time,
               v.disk_reads,
               rank() over(order by v.elapsed_time desc) elapsed_rank
          from v$sql v) a
 where elapsed_rank <= 10;

查看CPU消耗时间最多的前 10 条 SQL 语句:

select *
  from (select v.sql_id,
               v.child_number,
               v.sql_text,
               v.elapsed_time,
               v.cpu_time,
               v.disk_reads,
               rank() over(order by v.cpu_time desc) elapsed_rank
          from v$sql v) a
 where elapsed_rank <= 10;

查看消耗磁盘读取最多的前 10 条 SQL 语句:

select *
  from (select v.sql_id,
               v.child_number,
               v.sql_text,
               v.elapsed_time,
               v.cpu_time,
               v.disk_reads,
               rank() over(order by v.disk_reads desc) elapsed_rank
          from v$sql v) a
 where elapsed_rank <= 10;

手动删除归档日志

进入 RMAN：

RMAN TARGET /

查看当前所有的归档日志：

RMAN> list archivelog all;

手工删除归档日志文件，保留 15 天的归档日志

RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-15';

自动删除归档日志

每台 Oracle 机器都已配置了每天凌晨自动删除不用的归档日志。

[oracle@oracle-test-001:/home/oracle]
$ crontab -l
1 1 * * * sh /data/backup/scripts/delete_archive_log.sh

删除归档日志脚本 delete_archive_log.sh 示例：

$ cat /data/backup/scripts/delete_archive_log.sh

#/data/backup/scripts/backup_full.sh
export ORACLE_APP=/data/app/oracle
export ORACLE_BASE=$ORACLE_APP
export ORACLE_HOME=/data/app/oracle/product/11.2.0/db_1
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:/usr/lib:/usr/local/lib
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

CMD_FILE=/data/backup/scripts/delete_archive_log.rman
LOG_FILE=/data/backup/logs/delete_archive_log.logs
rman target / nocatalog cmdfile=$CMD_FILE msglog=$LOG_FILE

$ cat /data/backup/scripts/delete_archive_log.rman
RUN {
  delete force noprompt archivelog until time 'sysdate-15';
}
EXIT;

RMAN 无法删除旧的归档日志解决办法

一套 Oracle 11g 数据库使用 RMAN 自动删除归档日志，却没办法删除旧的归档日志，而旧的归档日志占用了近 1T 空间，导致磁盘使用率很高。

使用 RMAN list archivelog all; 查看日志，没有显示旧归档日志的记录。

解决办法：
使用 catalog start with 命令重新注册这些文件到控制文件中。

RMAN 删除归档日志的依据是 controlfile 中记录的归档日志，而不是你磁盘上实际存在的归档日志
，如果你的归档日志已经不在 controlfile 中就无法被 RMAN 识别到，也就无法被删除。而致于归档信息何时在控制文件中被覆盖，这由一个参数 control_file_record_keep_time 来控制。
查看数据库中的参数是默认的7天。

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 20 11:32:26 2019

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter CONTROL_FILE_RECORD_KEEP_TIME

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7

难道是在控制文件中归档日志的记录条数只能保留7天的么？于是到 MOS 去找相关文档找到一篇 Why Are Controlfile Entries For Archived Logs Not Overwritten After CONTROL_FILE_RECORD_KEEP_TIME ? (文档 ID 1056085.1)，文档介绍 CONTROL_FILE_RECORD_KEEP_TIME 参数并不是强制在控制文件中保留多少天的归档日志条目，而是由 V$CONTROLFILE_RECORD_SECTION 视图中的 ARCHIVED LOG 条目去控制的，当 RECORDS_TOTAL=RECORDS_USED 时,旧的归档日志就会被覆盖掉。

查看到 RECORDS_TOTAL=RECORDS_USED=1792 :

SQL> select * from v$controlfile_record_section where type='ARCHIVED LOG';

TYPE                 RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
ARCHIVED LOG                 584      1792         1792     900
       899  11651


SQL> select count(*) from v$archived_log;

  COUNT(*)
----------
      1792

SQL> exit

这时应该可以推断出由于控制文件中只能保留 1792 条归档日志数据，所以以前的归档信息已经被控制文件刷出，RMAN 无法识别到这些归档文件，所以就无法删除这些归档日志。

那么现在如何使用 RMAN 删除这些旧的归档日志呢？

使用 catalog start with 命令重新注册这些旧的归档日志文件到控制文件中：

$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon May 20 11:40:29 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CENTER (DBID=xxxxxxxxx)

RMAN>  catalog start with '/data1/oradata/center/archive';

using target database control file instead of recovery catalog
searching for all files that match the pattern /data1/oradata/center/archive

List of Files Unknown to the Database
=====================================
File Name: /data1/oradata/center/archive/1_1_990215949.dbf
File Name: /data1/oradata/center/archive/1_5_990215522.arc
File Name: /data1/oradata/center/archive/1_4_990215522.arc
File Name: /data1/oradata/center/archive/1_6_990215522.arc
File Name: /data1/oradata/center/archive/1_7_990215522.arc
.....  


Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /data1/oradata/center/archive/1_5_990215522.arc
File Name: /data1/oradata/center/archive/1_4_990215522.arc
File Name: /data1/oradata/center/archive/1_6_990215522.arc
File Name: /data1/oradata/center/archive/1_7_990215522.arc
.....

此时，再 list archivelog all 就能看的旧的归档日志了，然后再删除 60 天之前的归档日志即可：

RMAN> list archivelog all; 
.....  

RMAN> delete force noprompt archivelog until time 'sysdate-60';

再次查询 v$controlfile_record_section 视图，RECORDS_TOTAL 也被“撑”大到了 7168 ：

SQL> select * from v$controlfile_record_section where type='ARCHIVED LOG';

TYPE                 RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
ARCHIVED LOG                 584      7168         5070    6011
      3912  29224


SQL>