最全的oracle运维命令
1.启停，链接Oracle
本文中假定 oracle sid=orcl , 版本为11.2.0。
1.1 启停Oracle
#start /stop oracle server:
SQL> shutdown immediate
SQL> startup -- startup nomount; startup mount;
ORACLE instance started.
Total System Global Area 8.5516E+10 bytes
Fixed Size 2262656 bytes
Variable Size 3.1139E+10 bytes
Database Buffers 5.4224E+10 bytes
Redo Buffers 151117824 bytes
Database mounted.
#start:
lsnrctl start
sqlplus /nolog
connect sys/oracle as sysdba
startup
emctl start dbconsole
#stop:
emctl stop dbconsole
lsnrctl stop
#安全停库
SQL>alter system checkpoint;
SQL>alter system archive log current;
SQL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
##查看监听listener
#在数据库服务器上，可以通过lsnrctl工具检查监听状态和服务信息，
su - oracle
echo $ORACLE_SID
Lnrctl start|stop [listener-name]
lsnrctl status LISTENER
lsnrctl status LISTENER_SCAN1
lsnrctl service
#
nc -z 127.0.0.1 1521
tnsping ORCL@127.0.0.1
locate listener.ora
lsnrctl start
lsnrctl status
1.2 SQLPlus
常用命令
su - oracle
env |grep ora -i
echo $ORACLE_HOME
#ls -l $ORACLE_HOME/bin
cat $ORACLE_HOME/install/portlist.ini
Enterprise Manager Console HTTP Port (orcl) = 1158
Enterprise Manager Agent Port (orcl) = 3938
#sqlplus
sqlplus sys/oracle as sysdba
sqlplus /nolog
set line 500;
set pagesize 200;
set tab off;
show user;
show parameter;
SQL Set命令设置:
- set wrap on/off 查询返回的纪录每行超过默认宽度时，可选择换行（on ）或不换行（off）,默认为换行；
- set linesize N 设置查询返回的纪录每行的宽度，超过这个宽度则截掉，不过这个宽度则补空格。
2. Oracle SID、用户、目录及session会话查看
1、ORACLE SID查看设置
查看SID、用户名
$ env|grep SID
SQL>select * from v$instance;
SQL>select instance_name,host_name,VERSION from v$instance;
DB-110095029 orcl 11.2.0.4.0
查看数据库所有用户及用户状态：
SQL> select username,account_status from dba_users where ACCOUNT_STATUS='OPEN'; ;
#设置SID
$ export ORACLE_SID=hisvhfs
查看数据库DBID：
SQL>select * from v$DBID
3、当前连接数，用户链接情况
查看系统最大session：
SQL>show parameter session
#查看当前连接数：
#v$process视图,是当前系统中所有进程信息，包括“后台进程”，也包括"服务器进程"
SQL> select addr, program from v$process;
ADDR PROGRAM
---------------- ------------------------------------------------
00000013C1728E70 PSEUDO
00000013B16C6A30 oracle@DB-110095029 (PMON)
00000013A169D4D8 oracle@DB-110095029 (PSP0)
00000013E1689D98 oracle@DB-110095029 (VKTM)
00000014516A1B90 oracle@DB-110095029 (GEN0)
00000013D1699BB8 oracle@DB-110095029 (DIAG)
00000013C1729F28 oracle@DB-110095029 (DBRM)
00000013B16C7AE8 oracle@DB-110095029 (DIA0)
00000013A169E590 oracle@DB-110095029 (MMAN)
00000013E168AE50 oracle@DB-110095029 (DBW0)
...
查看当前连接到数据库的用户：
SQL>select count(*) from v$session
查看当前数据库建立的会话情况：
SQL> select sid,serial#,username,program,machine,status from v$session;
SID SERIAL# USERNAME PROGRAM MACHINE STATUS
---------- ---------- ------------------------------ ------------------------------------------------ ---------------------------------------------------------------- --------
1 7 oracle@DB-110095029 (ARC0) DB-110095029 ACTIVE
190 1 oracle@DB-110095029 (ARC1) DB-110095029 ACTIVE
379 1 oracle@DB-110095029 (PMON) DB-110095029 ACTIVE
380 1 oracle@DB-110095029 (ARC2) DB-110095029 ACTIVE
查询应用的连接数SQL:
SQL> SELECT b.MACHINE, b.PROGRAM, COUNT (*)
FROM v$process a, v$session b
WHERE a.ADDR = b.PADDR AND b.USERNAME IS NOT NULL
GROUP BY b.MACHINE, b.PROGRAM
ORDER BY COUNT (*) DESC;
MACHINE PROGRAM COUNT(*)
---------------------------------------------------------------- ------------------------------------------------ ----------
DB-110095029 oracDB_exporter@DB-110095029 (TNS V1-V3) 2
DB-110095029 sqlplus@DB-110095029 (TNS V1-V3) 1
#查看正在运行的sql
select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
from v$session a, v$sqlarea b
where a.sql_address = b.address;
查看当前数据库的并发连接数
SQL> select count(*) from v$session where status='ACTIVE';
查看当前有哪些用户正在使用数据：
select osuser,a.username,cpu_time/executions/1000000||'s',sql_fulltext,machine
from v$session a,v$sqlarea b
where a.sql_address = b.address
order by cpu_time/executions desc;
#查看当前后台进程
SQL>select count(*) from v$bgprocess;
COUNT(*)
----------
296
4、查询、设置最大进程数及最大会话数
查看当前数据库的并发连接数
SQL> select count(*) from v$session where status='ACTIVE';
查看系统最大进程数：
SQL>show parameter process
查看数据库指定用户的连接情况
SQL> select sid,serial# from v$session where username='XX'; XX为用户
例如：
SQL> select sid,serial# from v$session where username='BSPDEV';
SID SERIAL#
---------- ----------
204 4609
399 5841
590 6041
清除用户下连接进程
SQL> alter system kill session '204,4609';
SQL> alter system kill session '399,5841';
SQL> alter system kill session '590,6041';
修改processes和sessions值
SQL> alter system set processes=300 scope=spfile;
系统已更改。
SQL> alter system set sessions=335 scope=spfile;
系统已更改。
修改processes和sessions值必须重启oracle服务器才能生效
ORACLE的连接数(sessions)与其参数文件中的进程数(process)有关，它们的关系如下：
sessions=(1.1*process+5)
5. 数据库控制文件,目录，日志
5.1 控制文件
各种文件查看
spfile
show parameter spfile;
查看控制文件：
SQL> select name from v$controlfile;
查看重做日志文件，简称日志文件：
SQL> select * from v$logfile;
指定开启某个监听：
SQL>lsnrctl start orc11_lisenter(此处是当初建监听时创建的监听名)
SQL>select * from product_component_version ;
SQL>select name,type,value from v$parameter where name='sga_max_size';
SQL> show parameter db_writer_processes;
SQL> show sga;
Total System Global Area 8.5516E+10 bytes
Fixed Size 2262656 bytes
Variable Size 3.1139E+10 bytes
Database Buffers 5.4224E+10 bytes
Redo Buffers 151117824 bytes
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
SQL> select * from all_directories;
5.2 日志文件
查看、配置ORACLE重做日志
日志文件
select * from v$log;
select * from v$logfile;
查看日志文件组
SQL> select group#,status,member from v$logfile;
GROUP# STATUS 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
查看日志文件大小
SQL> select GROUP#,BYTES,BLOCKSIZE,MEMBERS from v$log;
GROUP# BYTES BLOCKSIZE MEMBERS
---------- ---------- ---------- ----------
1 209715200 512 1
2 209715200 512 1
3 209715200 512 1
创建日志文件组：
SQL> alter database add logfile group 4
(
'/u01/app/oracle/oradata/orcl/redo04.log'
)
size 10M;
为日志文件组增加日志文件：
SQL> alter database add logfile member '/u01/app/oracle/oradata/redo05.log' to group 5;
5.3 ASM磁盘组信息
SQL> desc v$asm_disk；
SQL> desc v$asm_diskgroup;
6.查看数据库现有模式、是否归档
SQl>select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
ORCL ARCHIVELOG
也可以用下面的语句
archive log list；(该方法需要as sysdba)
查看数据库的创建日期和归档方式
SQL> Select Created, Log_Mode, Log_Mode From V$Database;
CREATED LOG_MODE LOG_MODE
--------- ------------ ------------
28-MAY-15 ARCHIVELOG ARCHIVELOG
归档日志
connect sys/wang as sysdba
archive log list
shutdown immediate
startup mount
alter database archivelog;
#alter system archive log start;
alter database open;
archive log list
alter system archive log current; #重新生成一个归档日志。
alter system switch logfile; #
archive log list
alter system set log_archive_max_processes=5;
show parameter log_archive_max_processes;
show parameter log_archive_dest;
alter system set log_archive_dest="LOCATION=f:xx" mandatory;
desc v$archive_dest;
select destination,binding,target,status from v$archive_dest; # ls -l /letv/archivelog_dest/
alter system switch logfile;
###
SQL> set line 100;
SQL> set pagesize 25;SQL> select * from v$logfile order by group#,member;
7. 用户管理
7.1 用户表信息查看
alter user hr identified by hr account unlock; grant resource,connect to hr;
select * from session_roles;
select table_name from tabs;
#select host_name,instance_name,version from v$instance;
select username,default_tablespace,ACCOUNT_STATUS from user_users;
select username,default_tablespace,ACCOUNT_STATUS from dba_users;
select * from all_users;
select table_name from user_tables;
查看当前用户的缺省表空间
select username,default_tablespace from user_users;
查看当前用户的角色
select * from user_role_privs;
查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;
7.2 创建用户命令
--快速创建账号
create user dbtest identified by dbtest;
grant connect,resource to dbtest;
--用户和其默认表空间
set linesize 300
select username,default_tablespace,temporary_tablespace from dba_users where DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM');
select username,default_tablespace,temporary_tablespace from dba_users where DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM') and USERNAME not in ('SCOTT','ORACLE_OCM','XS$NULL','MDDATA','DIP','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','MONITOR');
--查看当前用户所有表
select * from tab;
--查看某个用户模式下所有对象
SELECT owner,object_name, object_type FROM dba_objects WHERE owner= 'SCOTT';
--查看用户的所有role权限
select granted_role from dba_role_privs where grantee='BOSS_NEW_W';
--查看用户具有的具体权限
select privilege from dba_sys_privs where grantee='BOSS_NEW_W'
union
select privilege from dba_sys_privs where grantee in
(select granted_role from dba_role_privs where grantee='BOSS_NEW_W');
--补充
SELECT grantee,privilege,admin_option FROM dba_sys_privs WHERE grantee IN ('SCOTT') ORDER BY grantee;
--用户创建语句,权限语句
select dbms_metadata.get_ddl('USER','BOSS_NEW_W') from dual;
select dbms_metadata.get_granted_ddl('ROLE_GRANT','BOSS_NEW_W') from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','BOSS_NEW_W') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_tab_privs);
查看Role创建语句
set linesize 1800
set pages 10000
set long 90000
--设置按单词换行
col a for a200 wrapped word
查看Role创建语句
-- Create the roles
SELECT DBMS_METADATA.GET_DDL('ROLE', 'SSE_ROLE') a from dual;
-- Roles which are granted to roles
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'SSE_ROLE') a from dual;
-- System privileges granted to roles
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'SSE_ROLE') a from dual;
-- Table privileges granted to roles
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'SSE_ROLE') a from dual;
--解锁用户
select username,account_status from dba_users; --查看用户状态
alter user SCOTT account unlock; --解开LOCKED
alter user SCOTT identified by triger;--解开EXPIRED
oracle一个创建用户、创建表空间、授权、建表的完整过程
create user dbtest identified by dbtest;
alter user dbtest identified by dbtest;
select username,default_tablespace from dba_users;
create tablespace dbtest_tbs datafile '/orcl/app/oracle/oradata/orcl/dbtest.dbf' size 200M;
alter user dbtest default tablespace dbtest_tbs;
grant create session,create table,create view,create sequence,unlimited tablespace to dbtest;
conn dbtest/dbtest;
select *from session_privs;
drop user dbtest cascade;
drop tablespace dbtest_TBS including contents and datafiles;
7.3 解锁用户
解锁新用户：
SQL> alter user scott account unlock；
SQL> alter user scott identified by tiger;
删除oracle用户：
SQL>drop user username cascade; (删除与用户相关的所有对象)
这样该用户包括该用户下的表，试图，同义词，过程，索引，及相关的一切就删除了。
创建用户并赋权限以及设置默认表空间。
以sysdba用户登陆进行以下设置：
-- Create the user
create user VHFSM
identified by vhnj1fsm
default tablespace MGRVHFSTBSDEF 此处是设置默认表空间。
temporary tablespace TEMP
profile DEFAULT
quota unlimited on mgrvhfstbs2010此处是设置可操作的其他表空间
quota unlimited on mgrvhfstbsdef;
-- Grant/Revoke role privileges
grant connect to VHFSM;
grant dba to VHFSM;
-- Grant/Revoke system privileges
grant unlimited tablespace to VHFSM;
7.4 查看用户及角色权限
查看用户及角色权限
--1.查看所有用户：
select * from dba_users;
select * from all_users;
select * from user_users;
--2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;
--3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;
--4.查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
--5.查看所有角色：
select * from dba_roles;
--6.查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;
--7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS
--注意：
--1、以下语句可以查看Oracle提供的系统权限
select name from sys.system_privilege_map
--2、查看一个用户的所有系统权限(包含角色的系统权限)
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');
7.5 配置用户密码过期时间
alter profile "default" limit password_life_time unlimited; 配置用户密码永不过期
alter profile "default" limit password_life_time 100; 配置用户密码100天过期
8. 创建、管理Oracle表空间
查看表结构，分区表信息等
select dbms_metadata.get_ddl('TABLE','SMS_TO_ISMG7','SMS9885') FROM DUAL;
查看表空间建立语句
set long 90000
select dbms_metadata.get_ddl('TABLESPACE','SYSTEM') from dual;
查看视图定义
select * from user_views;
select dbms_metadata.get_ddl('VIEW','SMS_TO_ISMG7_ALL') FROM DUAL;
查看每个表index的信息
select * from dba_indexes where TABLE_NAME=upper('sms_from_ismg');
select * from dba_ind_columns where index_name=upper('UN_SMS_FROM_ISMG');
查看表空间情况
set linesize 200
select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used",c.contents,c.extent_management
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b,
(select tablespace_name,contents,extent_management from dba_tablespaces) c
where a.tablespace_name=b.tablespace_name and a.tablespace_name=c.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc;
表空间对应的物理文件
select * from dba_data_files where TABLESPACE_NAME='WMS';
扩容表空间
alter tablespace PTPRC add datafile '/data/oradata/orcl/ptprc02.dbf' size 500M autoextend on ;
查看表情况
select segment_name,bytes/1024/1024,tablespace_name,PARTITION_NAME
from user_segments
where segment_type = 'TABLE';
表占用空间由大到小
select tablespace_name,segment_name,bytes/1024/1024 usesize from dba_segments
order by usesize desc;
查看SYSAUX空间存储了那些非核心功能数据
select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME from V$SYSAUX_OCCUPANTS;
#
表空间&及其文件
col FILE_NAME for a50
select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024,STATUS from dba_data_files order by TABLESPACE_NAME,FILE_NAME;
8.1 创建、管理Oracle表空间
1、先查询空闲空间
select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space;
2、增加Oracle表空间
先查询数据文件名称、大小和路径的信息，语句如下：
select tablespace_name,file_id,bytes,file_name from dba_data_files;
SYSTEM 1 859832320 /u01/app/oracle/oradata/orcl/system01.dbf
SYSAUX 3 692060160 /u01/app/oracle/oradata/orcl/sysaux01.dbf
USERS 7 5242880 /u01/app/oracle/oradata/orcl/users01.dbf
UNDOTBS1 4 73400320 /u01/app/oracle/oradata/orcl/undotbs01.dbf
3、修改文件大小语句如下
alter database datafile
'需要增加的数据文件路径，即上面查询出来的路径
'resize 800M;
4、创建Oracle表空间
create tablespace test
datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 8M
autoextend on
next 5M
maxsize 10M;
create tablespace sales
datafile '/u01/app/oracle/oradata/orcl/sales01.dbf' size 800M
autoextend on
next 50M
maxsize unlimited
maxsize unlimited 是大小不受限制
create tablespace sales
datafile '/u01/app/oracle/oradata/orcl/sales01.dbf' size 800M
autoextend on
next 50M
maxsize 1000M
extent management local uniform;
unform表示区的大小相同，默认为1M
create tablespace sales
datafile '/u01/app/oracle/oradata/orcl/sales01.dbf' size 800M
autoextend on
next 50M
maxsize 1000M
extent management local uniform size 500K;
unform size 500K表示区的大小相同，为500K
create tablespace sales
datafile '/u01/app/oracle/oradata/orcl/sales01.dbf' size 800M
autoextend on
next 50M
maxsize 1000M
extent management local autoallocate;
autoallocate表示区的大小由随表的大小自动动态改变，大表使用大区小表使用小区
create tablespace sales
datafile '/u01/app/oracle/oradata/orcl/sales01.dbf' size 800M
autoextend on
next 50M
maxsize 1000M
temporary;
temporary创建字典管理临时表空间 ，要创建本地管理临时表空间要加temporary tablespace关键字
create temporary tablespace sales
tempfile '/u01/app/oracle/oradata/orcl/sales01.dbf' size 800M
autoextend on
next 50M
maxsize 1000M
创建本地管理临时表空间，如果是临时表空间，所有语句中的datafile都换为tempfile ，所有语句中的datafile都换为tempfile8i
为表空间增加数据文件：
alter tablespace sales add
datafile '/u01/app/oracle/oradata/orcl/sales02.dbf' size 800M
autoextend on next 50M
maxsize 1000M;
5、查看表空间是否自动扩展
SQL> select file_id,file_name,tablespace_name,autoextensible,increment_by from dba_data_files order by file_id desc;
更改自动扩展属性：
alter database datafile
'/u01/app/oracle/oradata/orcl/sales01.dbf',
'/u01/app/oracle/oradata/orcl/sales02.dbf',
'/u01/app/oracle/oradata/orcl/sales01.dbf'
autoextend off;
6、表空间的查看与修改
查看用户默认表空间
SQL> select username,default_tablespace from dba_users;查看所有用户的默认表空间
SQL> select username,default_tablespace from user_users;查看某个用户的默认表空间，前提需要connect该用户。
SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username = 'hr'; 查看用户对应的默认表空间
修改用户默认表空间
SQL> alter user zhanghr default tablespace test;
设置数据库的默认临时表空间:
SQL>Alter database default temporary tablespace temp_tbs_name;
查看用户和默认表空间的关系:
SQL>select username,default_tablespace from dba_users;
查看临时表空间：
SQL> select file_name,file_id,blocks,user_blocks from dba_temp_files；
SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;
查看undo表空间
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
查看undo表空间大小
SQL> select sum(bytes)/1024/1024 "current undo size(M)" from dba_data_files where tablespace_name='UNDOTBS1';
通过增加数据文件来改变undo表空间大小
SQL> alter tablespace undotbs1
add datafile '/oracle/oradata/orc6/undo02.dbf' size 10M;
通过resize更改数据文件大小
SQL> alter database datafile '/oracle/oradata/orc6/undo02.dbf' resize 100M;
查看某个表空间的数据文件
SQL> select file_name,tablespace_name,bytes/1024/1024 "bytes MB",maxbytes/1024/1024 "maxbytes MB" from dba_data_files where tablespace_name='ORA1TBS';指定表空间名要大写
查看所有表空间大小
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files
group by tablespace_name;
查看已使用的表空间大小
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space
group by tablespace_name;
8.2 查看表空间大小及使用率
方法一：
SELECT UPPER(F.TABLESPACE_NAME),D.TOT_GROOTTE_MB,D.TOT_GROOTTE_MB,F.TOTAL_BYTES,TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2),'990.99'), F.TOTAL_BYTES, F.MAX_BYTES FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC;
方法二：
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
"USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;
方法三：
SELECT *
FROM (SELECT a.tablespace_name,
to_char(a.bytes / 1024 / 1024, '99,999.999') total_bytes,
to_char(b.bytes / 1024 / 1024, '99,999.999') free_bytes,
to_char(a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024,
'99,999.999') use_bytes,
to_char((1 - b.bytes / a.bytes) * 100, '99.99') || '%' USE
FROM (SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
UNION ALL
SELECT c.tablespace_name,
to_char(c.bytes / 1024 / 1024, '99,999.999') total_bytes,
to_char((c.bytes - d.bytes_used) / 1024 / 1024, '99,999.999') free_bytes,
to_char(d.bytes_used / 1024 / 1024, '99,999.999') use_bytes,
to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' USE
FROM (SELECT tablespace_name, SUM(bytes) bytes
FROM dba_temp_files
GROUP BY tablespace_name) c,
(SELECT tablespace_name, SUM(bytes_cached) bytes_used
FROM v$temp_extent_pool
GROUP BY tablespace_name) d
WHERE c.tablespace_name = d.tablespace_name)
ORDER BY tablespace_name;
查看表空间使用大小情况一
SQL> select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",
b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name ) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc
;
TABLESPACE_NAME Sum MB used MB free MB percent_used
------------------------------ ---------- ---------- ---------- ------------
SYSAUX 490 461.0625 28.9375 94.09
UNDOTBS1 75 66.8125 8.1875 89.08
USERS 5 1.3125 3.6875 26.25
ORA1TBS 400 2 398 .5
ORA2TBS 400 2 398 .5
查看表空间使用大小情况二
SQL> select a.tablespace_name,total,free,total-free used from
2 ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name; 3 4 5 6
TABLESPACE_NAME TOTAL FREE USED
------------------------------ ---------- ---------- ----------
USERS 5 .9375 4.0625
TESTTB 500 499 1
SYSAUX 550 30 520
EXAMPLE 100 20.75 79.25
UNDOTBS1 110 96.625 13.375
在SQL命令行情况下将结果输出到指定文件中。
SQL> spool out.txt
SQL> select * from v$database;
SQL> spool off
9. 查看、管理ORACLE数据文件
SQL> select tablespace_name,status, contents from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS
------------------------------------------------------------ ------------------ ------------------------------------------
SYSTEM ONLINE PERMANENT
SYSAUX ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
查看数据文件：
SQL> select name from v$datafile;
更改数据文件大小：
SQL> alter database datafile '/oradata2/hisvhfs/undotbs01.dbf' resize 30G;
查看用户所有表空间及对应的数据文件和数据文件大小
SQL> select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
查看数据文件状态及大小
SQL> select name,BYTES,STATUS ,FILE# from v$datafile;
查看所有数据文件
SQL> select name from v$datafile;
查看所有表空间对应的数据文件
SQL> select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
10. 查看、管理ORACLE对象
10.1 segment及表空间
查看所有segment的大小。
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;
查看表的segment大小，即表实际占用的物理大小，无论是否使用。
SQL> select segment_name,partition_name,bytes,blocks,extents from user_segments where segment_name='JOBS'; （segment名，即表明）
SQL> select segment_name,partition_name,count(extent_id),sum(bytes),sum(blocks) from user_extents where segment_name='JOBS' group by segment_name,partition_name
查询表的大小和表空间的大小
或者
SQL> Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;
SEGMENT_NAME SUM(BYTES)/1024/1024
-------------------------------------------------------------------------------- --------------------
DEPT 0.0625
PK_DEPT 0.0625
...
另一种表实际使用的空间。这样查询：
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADEanalyze table emp compute statistics;
ITEMS
DEPT_COPY
DEPT_COPY2
SQL> analyze table DEPT_COPY2 compute statistics;
查看某个表属于哪个表空间：
SQL> select tablespace_name from all_tables where table_name='SYS_EXPORT_FULL_01';
创建表时指定表空间：
SQL>create table a (name varchar(10)) tablespace test;
10.2 表结构修改及查询
查看数据库表结构：
SQL> desc dept_copy2;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2) Y
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y
修改表的列名：
SQL> alter table users rename column ID to PID;
修改表的列的字符大小
SQL> alter table student modify class varchar2(10);
创建主键：
SQL> alter table users add constraint pk_users primary key(name);
修改表的列数据类型：
SQL> select * from users;
NAME AGE PID
---------- ---------- ----------
赵霞 3.2012E+18
李莉 3.2012E+18
SQL> alter table users add(newColumn varchar2(20));
SQL> update users set newColumn = PID;
SQL> commit;
SQL> alter table users drop column PID cascade constraints;
SQL> alter table users rename column newColumn to PID;
SQL> commit;
SQL> desc users;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
NAME NOT NULL VARCHAR2(10)
AGE NUMBER
PID VARCHAR2(20)
SQL> select * from users;
NAME AGE PID
---------- ---------- --------------------
赵霞 3201231988001010101
李莉 3201231988001010102
having子句与group by 子句一般一起使用，可以在汇总相关数据后进一步筛选汇总的数据。
SQL> select ID,avg(age),sum(age),max(age),count(*)
2 from job group by id having avg(age)>25;
ID AVG(AGE) SUM(AGE) MAX(AGE) COUNT(*)
---------- ---------- ---------- ---------- ----------
1690 26 26 26 1
1783 35 35 35 1
1689 26 26 26 1
1688 28 28 28 1
多表连接查询
SQL> select job.name,job.id,job.job,job.age,users.name,users.age from job,users
2 where job.name=users.name;
NAME ID JOB AGE NAME AGE
-------- ---------- ---------- ---------- ---------- ----------
陈灵灵 1783 KTV服务员 35 陈灵灵 38
李莉 1587 IT程序员 23 李莉
赵霞 1683 空姐 22 赵霞
desc 在oracle数据库里有两种用法，一是查询表的结构 格式：desc table_name
二是在用排序时(order by)是降序的关键字 格式 ：order by column_name desc;
10.3 索引及约束
查看:
查看表的索引：
SQL>select index_name,index_type,table_name from user_indexes where table_name='表名'；
查看约束:
select constraint_name,constraint_type,conlumn_name from user_constraints natural join user_cons_columns where table_name='DEPT';
创建:
#建立
create table dept(deptno number,dname varchar2(10));
create table emp(empno number ,surname varchar2(10) , forename varchar2(10), dob date,deptno number );
create unique index dept_idx1 on dept(deptno);
create unique index emp_idx1 on emp(empno);
create index emp_idx2 on emp(surname,forename);
create bitmap index emp_idx3 on emp(deptno);
alter table dept add constraint dept_pk primary key (deptno);
alter table emp add constraint emp_pk primary key(empno);
alter table emp add constraint emp_fk foreign key (deptno) references dept(deptno);
10.4 视图
create view staff_clerk as select * from employees where JOB_ID like '%CLERK%';
select * from staff_clerk where ROWNUM <=10 ;
drop view staff_clerk;
10.5 同义词
同义词可以对任何SQL起作用，而不用考虑模式等限制。
create synonym s1 for employees;
create public synonym s1 for departments;
desc s1; #该是employees
select * from s1 where rownum<10;
10.6 序列
创建序列：
create sequence order_seq start with 1000 maxvalue 10000 minvalue 1000
nocycle cache 100;
查看用户序列
SQL> select sequence_name from USER_SEQUENCES;
SEQUENCE_NAME
------------------------------
order_seq
#生成
select order_seq.nextval,order_seq.currval from dual
10.7 触发器
创建触发器：
SQL> create or replace trigger trigger_users
before insert on users
for each row
begin
select seq_PID.nextval into :new.PID from dual;
end;
/
查看用户触发器
SQL> select * from user_triggers where table_name = upper('job');
通过group by 语句使用rollup运算符汇总ID列
SQL> select ID,avg(age),sum(age),max(age),count(*)
2 from job group by rollup(ID);
ID AVG(AGE) SUM(AGE) MAX(AGE) COUNT(*)
---------- ---------- ---------- ---------- ----------
1583 25 25 25 1
1584 24 24 24 1
1585 25 25 25 1
### 10.8 数据库游标
数据库游标设置
SQL> show parameter open_cursors; 查看游标参数
SQL> select count(*) from vsysstat where name ='opened cursors current';查看当前打开的游标
SQL> alter system set open_cursors=3000 scope=both; 修改默认游标数;
### 10.9 desc的常用查询
SQL> desc dba_temp_files; 查询临时表空间
SQL> desc vFLASH_RECOVERY_AREA_USAGE; 查看flash_recovery_area 使用情况。
SQL> select sum(percent_space_used)*3/100 from v
process 通过v
process;
SQL> desc v
bgprocess视图,我们获得当前系统中启动的Oracle“后台进程”信息
SQL> select paddr, name from v
bgprocess.paddr与v
bgprocess a, v$process b where a.paddr=b.addr;
11. 时间
修改oracle时间格式：
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
数据库SCN及时间查询。
SQL> select sysdate from dual; 查看数据库时间
SYSDATE
-----------------
20131216 23:52:55
SQL> select dbms_flashback.get_system_change_number from dual; 查看当前数据库的SCN号
GET_SYSTEM_CHANGE_NUMBER
------------------------
1583042
SQL> select to_char(scn_to_timestamp(1583042),'yyyy-mm-dd hh24:mi:ss') from dual; 根据数据库的SCN号查找对应的数据库时间
TO_CHAR(SCN_TO_TIME
-------------------
2013-12-16 23:45:38
SQL> select timestamp_to_scn(to_date('2013-12-16 15:30:19','yyyy-mm-dd hh24:mi:ss')) from dual; 根据数据库的时间查找对应的数据库SCN号
TIMESTAMP_TO_SCN(TO_DATE('2013-12-1615:30:19','YYYY-MM-DDHH24:MI:SS'))
----------------------------------------------------------------------
1519388
SQL> select * from (select time_dp,scn from smon_scn_time order by time_dp desc)where rownum
TIME_DP SCN
----------------- ----------
20131216 15:53:02 1584574
20131216 15:46:45 1583098
9 rows selected.
12. 目录Directory
一般创建directory都是为了用数据泵导入/导出数据用 文档： docs.oracle.com/cd/B19306_0…
查看目录
select * from dba_directories;
创建目录
create or replace directory DUMP_DIR as '/u01/app/oracledump';
select * from dba_directories;
删除目录
drop directory DUMP_DIR;
授权
create user mytest identified by mytestpwd default tablespace mytest;
-- grant connect, resource, DATAPUMP_IMP_FULL_DATABASE, DATAPUMP_EXP_FULL_DATABASE
-- to mytest;
GRANT dba to mytest;
grant read, write on directory DUMP_DIR to mytest;
13. 数据导出/导入
13.1 数据导出
expdp语法:
today=`date +%Y%m%d-%H%M`
user=USERNAME
directory=test
expdp system/oracle directory=${directory} dumpfile=${user}-${today}-%U.dmp logfile=${user}-${today}.log parallel=4 schemas=${user} filesize=1G compression=all
##
说明：
directory要事先创建。
%U是01、02、03等等。
parallel并行命令不一定有用。
schemas可以理解为数据库用户名。
规定filesize可以防止文件过大。
compression=all或NONE，all的速度快，体积小。
####
准备前检查
--准备工作EXPNC_DIR路径创建
--查询数据库路径表
select * from DBA_DIRECTORIES;
--查询数据库用户表
select * from DBA_USERS;
--查询数据库表空间
select * from DBA_TABLESPACES;
--查询数据库数据文件信息表
select * from DBA_DATA_FILES;
###检查Oracle server端的字符集
expdp/impdp数据泵只依赖于服务器端字符集NLS_CHARACTERSET，查看源和目标端数据库字符集是否一致
select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
或者
#主要关注NLS_CHARACTERSET
SQL> set line 240 pagesize 1000
SQL> select * from v$nls_parameters;
PARAMETER VALUE
------------------------ ------------------------
NLS_CHARACTERSET AL32UTF8
检查表空间大小
select a.tablespace_name,a.bytes/1024/1024 "sum MB", (a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round (((a.bytes-b.bytes)/a.bytes)*100,2) "used%" from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name,sum(bytes) bytes,max (bytes) largest from dba_free_space group by tablespace_name)b where a.tablespace_name=b.tablespace_name order by ((a.bytes-b.bytes)/a.bytes) desc;
导出示例1:
1.清理环境:
DROP USER mytest cascade;
drop tablespace mytest including contents and datafiles;
2、创建表空间和用户及赋权
create or replace directory DUMP_DIR as '/u01/app/oracledump';
create TABLESPACE mytest DATAFILE '/u01/app/oracle/oradata/orcl/mytest.dbf' size 10M autoextend on maxsize 10G;
create user mytest identified by mytestpwd default tablespace mytest;
-- grant connect, resource, DATAPUMP_IMP_FULL_DATABASE, DATAPUMP_EXP_FULL_DATABASE
-- to mytest;
GRANT dba to mytest;
grant read, write on directory DUMP_DIR to mytest;
3.执行expdp
expdp mytest/mytestpwd directory=DUMP_DIR dumpfile=mytest.dmp logfile=mytest_expdp.log schemas=mytest
导出示例2
--1)按用户导
expdp scott/tiger@orcl DIRECTORY=DUMP_DIR dumpfile=scott.dmp schemas=scott
--2)并行进程parallel
expdp scott/tiger@orcl directory=DUMP_DIR dumpfile=scott2.dmp parallel=20 job_name=scott2
--3)按表名导
expdp scott/tiger@orcl TABLES=emp,dept dumpfile=emp.dmp DIRECTORY=DUMP_DIR
...
. . exported "SCOTT"."EMP" 8.773 KB 14 rows
. . exported "SCOTT"."DEPT" 6.023 KB 4 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/app/oracledump/emp.dmp
--4)按查询条件导 (好象没有成功)
expdp scott/tiger@orcl directory=DUMP_DIR dumpfile=emp2.dmp schemas=scott Tables=emp query='WHERE DEPTNO=20'
--5)按表空间导
GRANT EXP_FULL_DATABASE TO scott;
expdp scott/tiger DIRECTORY=DUMP_DIR DUMPFILE=temp_tablespace.dmp TABLESPACES=temp
--6)导整个数据库
GRANT EXP_FULL_DATABASE TO scott;
expdp scott/tiger DIRECTORY=DUMP_DIR DUMPFILE=full.dmp FULL=y
导出结果日志
expdp mytest/mytestpwd directory=DUMP_DIR dumpfile=mytest.dmp logfile=mytest_expdp.log schemas=mytest
Export: Release 12.2.0.1.0 - Production on Tue Dec 3 23:39:02 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "MYTEST"."SYS_EXPORT_SCHEMA_01": mytest/******** directory=DUMP_DIR dumpfile=mytest.dmp logfile=mytest_expdp.log schemas=mytest
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Master table "MYTEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MYTEST.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracledump/mytest.dmp
Job "MYTEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Dec 3 23:39:31 2019 elapsed 0 00:00:28
13.2 数据导入
导入命令 把mytest.dmp导入并且改为mytestnew。
impdp mytest/mytestpwd schemas=mytest directory=DUMP_DIR dumpfile=mytest.dmp logfile=mytest_imp1.log remap_schema=mytest:mytestnew
日志
$impdp mytest/mytestpwd schemas=mytest directory=DUMP_DIR dumpfile=mytest.dmp logfile=mytest_imp1.log remap_schema=mytest:mytestnew
Import: Release 12.2.0.1.0 - Production on Tue Dec 3 23:45:10 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "MYTEST"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MYTEST"."SYS_IMPORT_SCHEMA_01": mytest/******** schemas=mytest directory=DUMP_DIR dumpfile=mytest.dmp logfile=mytest_imp1.log remap_schema=mytest:mytestnew
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "MYTEST"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue Dec 3 23:45:14 2019 elapsed 0 00:00:03
检查结果
xx
14. Rman备份
14.1 备份命令
快速增量备份，要开启 块更改追踪功能 #BCT (Block change Tracking)
select filename,status,bytes from v$block_change_tracking;
alter database enable block change tracking using file '/u01/app/oracle/oradata/orcl/rman_change_track.f' reuse;
select filename,status,bytes from v$block_change_tracking;
select pid,username,program from v$process where program like '%CT%';
#快速恢复区
show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 20G
alter system set db_recovery_file_dest='/data1/xxx' scope=spfile;
###备份 ###
rman target /
show all;
backup incremental level 0 database; #级别为0备份
backup incremental level 1 database; #级别为1备份
backup incremental level 1 cumulative database; #级别为1增备
alter system archive log current;
backup database plus archivelog;
##
Select backup_type,START_TIME ,COMPLETION_TIME,BLOCK_SIZE from v;
SQL> shutdown immediate;
startup nomount
rman target /
rman target / log=/data1/oraclebackup/oracle_backinfo_20191205_181402.log
alter database mount;
#alter database open;
show all;
backup incremental level 0 database; #级别为0备份
backup incremental level 1 database; #级别为1备份
backup incremental level 1 cumulative database; #级别为1增备
sql 'alter system archive log current';
backup database plus archivelog; #先alter system archive log current
report obsolete;
crosscheck backup;
crosscheck archivelog all;
delete expired archivelog all;
delete expired backup;
list backup summary;
list backup of database;
LIST BACKUP OF CONTROLFILE;
LIST BACKUP OF ARCHIVELOG ALL;
restore database until scn 3929232;
restore database;
recover database;
or :
run{
set until time to_date('2019-07-23 15:00:00','yyyy-mm-dd hh24:mi:ss');
restore database;
recover database;
}
# 测试及重新backup
alter database open;
alter database open resetlogs;
rman target / log=/data1/oraclebackup/oracle_backinfo_20191205_181402.log
backup incremental level 0 database;
sql 'alter system archive log current';
backup database plus archivelog;
14.2 备份脚本
sh oracle_backup_rman.sh
#!/bin/bash
#######################################
# usage:oracle_rman_backup.sh <0,1,2,3>
########################################
# 先全备，然后在清理！！！
. /home/oracle/.bash_profile
#export CURRENT_TIME=`date "+%Y%m%d_%H%M%S"`
#export COMPRESS=compressed
export CURRENT_TIME=`date "+%Y%m%d_%H%M%S"`
export COMPRESS=
export CATALOG=
export BACKUP_BASE_PATH="/data1/backup_oracle"
#RETAIN_SETS保留最近几份备份集合（一个全备和对应的6个增备）
export RETAIN_SETS="2"
#归档文件保留时间
export RETAIN_TIME="7"
LOCAL_HOST_PORT="$2"_1521
which_day=`date +"%w"`
export FIRST_WEEK_DAY=`date -d "${which_day} days ago" +%Y%m%d`
sys_password=`cat /home/oracle/.sys_secret`
function mkdir_path {
DIR_PATH=$1
if [ ! -d "${DIR_PATH}" ]; then
mkdir -p ${DIR_PATH}
chown -R oracle:oinstall ${DIR_PATH}
fi
}
function do_rman_prepare {
# 判断磁盘是否还有空间可以备份数据库
# get oracle size
DATABASE_SIZE=`
sqlplus -s /nolog <<EOF
set echo off feedback off heading off underline off;
conn / as sysdba;
select sum(BYTES)/1024/1024 from dba_data_files;
exit;
EOF`
DATABASE_SIZE=`echo ${DATABASE_SIZE}|grep -v '^\s*$' `
#get backup disk free
BACKUP_BASE=`echo ${BACKUP_BASE_PATH}|awk -F'/' '{print $2}'`
BACKUP_DISK_FREE=`df -mP|grep ${BACKUP_BASE}|awk '{print $4}'`
if [[ ${DATABASE_SIZE} -gt ${BACKUP_DISK_FREE} ]]
then
echo 'backup disk not space!!!'
exit 0
fi
}
function do_rman_level0 {
mkdir_path ${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT}/${FIRST_WEEK_DAY}/level0_${CURRENT_TIME}
echo "backup incremental level 0 tag incr_L0 as $COMPRESS backupset database ${DB_F} filesperset 6;"
rman target sys/$sys_password $CATALOG log=${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT}/${FIRST_WEEK_DAY}/level0_${CURRENT_TIME}/${ORACLE_SID}_level0_$(date +"%Y%m%d%H%M").log <<EOF
sql "alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''";
run{
backup incremental level 0 tag "incr_L0" as $COMPRESS backupset database skip inaccessible format '${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT}/${FIRST_WEEK_DAY}/level0_${CURRENT_TIME}/db_%d_%U_%T' filesperset 6;
backup tag "arch" AS $COMPRESS backupset archivelog all delete input skip inaccessible format '${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT}/${FIRST_WEEK_DAY}/level0_${CURRENT_TIME}/arch_%d_%U_%T' filesperset 6;
backup current controlfile tag='control' format '${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT}/${FIRST_WEEK_DAY}/level0_${CURRENT_TIME}/control_%d_%U_%T';
backup spfile tag='spfile' format '${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT}/${FIRST_WEEK_DAY}/level0_${CURRENT_TIME}/spfile_%d_%U_%T';
}
EOF
}
function do_rman_level1 {
mkdir_path ${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT}/${FIRST_WEEK_DAY}/level1_${CURRENT_TIME}
echo "backup incremental level 1 tag incr_L1 as $COMPRESS backupset database ${DB_F} filesperset 6;"
rman target sys/$sys_password $CATALOG log=${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT}/${FIRST_WEEK_DAY}/level1_${CURRENT_TIME}/${ORACLE_SID}_level1_$(date +"%Y%m%d%H%M").log <<EOF
sql "alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''";
run {
backup incremental level 1 tag "incr_L1" as $COMPRESS backupset database skip inaccessible format '${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT}/${FIRST_WEEK_DAY}/level1_${CURRENT_TIME}/db_%d_%U_%T' filesperset 6;
backup tag "arch" AS $COMPRESS backupset archivelog all delete input skip inaccessible format '${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT}/${FIRST_WEEK_DAY}/level1_${CURRENT_TIME}/arch_%d_%U_%T' filesperset 6;
backup current controlfile tag='control' format '${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT}/${FIRST_WEEK_DAY}/level1_${CURRENT_TIME}/control_%d_%U_%T';
backup spfile tag='spfile' format '${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT}/${FIRST_WEEK_DAY}/level1_${CURRENT_TIME}/spfile_%d_%U_%T';
}
EOF
}
function do_rman_clear_backup {
mkdir_path "/var/log/oracle/rman"
for EXPIRED_DIR in `ls -t ${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT} | awk "{if(NR>${RETAIN_SETS}){print $NF}}"`
do
echo "delete expired backup sets dir: ${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT}/${EXPIRED_DIR}"
rm -rf ${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT}/${EXPIRED_DIR}
done
rman target sys/$sys_password $CATALOG log=/var/log/oracle/rman/${ORACLE_SID}_obsolete_$(date +"%Y%m%d%H%M").log <<EOF
sql "alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''";
RUN
{
crosscheck backup;
crosscheck archivelog all;
delete noprompt archivelog all completed before 'sysdate-${RETAIN_TIME}' device type disk;
delete noprompt expired backup;
}
sql 'alter database backup controlfile to trace';
EOF
}
function do_rman_clear_archive {
current_time=`date "+%Y:%m:%d %H:%M:%S"`
echo "${current_time}: cleaning oracle archivelog"
if [ ! -d "/var/log/oracle/delarch" ]; then
echo 'mkdir oracle log'
mkdir /var/log/oracle/delarch
chown -R oracle:oinstall /var/log/oracle/delarch
fi
rman target / log=/var/log/oracle/delarch/delarch_$(date +"%Y%m%d%H%M").log << EOF2
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog until time 'sysdate-7';
crosscheck archivelog all;
delete noprompt expired archivelog all;
exit;
EOF2
echo ""
current_time=`date "+%Y:%m:%d %H:%M:%S"`
echo "${current_time}: finish clean oracle archivelog"
}
function do_rman_arch {
mkdir_path ${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT}/arch_${CURRENT_TIME}
rman target sys/$sys_password $CATALOG log=${LOG_PATH}/${ORACLE_SID}_arch_$(date +"%Y%m%d%H%M").log <<EOF
sql "alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''";
RUN
{
backup tag "arch" AS $COMPRESS backupset archivelog all skip inaccessible format '${BACKUP_BASE_PATH}/${LOCAL_HOST_PORT}/arch_${CURRENT_TIME}/arch_%d_%U_%T' not backed up 1 times;
}
EOF
}
function do_help {
echo -e "\n"
echo -e "\t rman_ohsdba.sh 0 will do level 0 backup"
echo -e "\t rman_ohsdba.sh 1 will do level 1 backup"
echo -e "\t rman_ohsdba.sh 2 will do archivelog backup"
echo -e "\t rman_ohsdba.sh 3 will clear expired and obsolete backup"
echo -e "\t If any questions,please check with Wechat ohsdba"
echo -e "\n"
}
if [ x$1 = x ]; then
do_help
exit
else
expr $1 + 1 &>/dev/null
[ $? -ne 0 ] && { echo -e "\tArgs must be integer!";exit 1; }
fi
case $1 in
0)
#do_rman_prepare
do_rman_level0
;;
1)
do_rman_level1
;;
2)
do_rman_arch
;;
3)
do_rman_clear_backup
;;
4)
do_rman_clear_archive
;;
*)
do_help
;;
esac
exit 0
14.3 备份清理
#!/bin/bash
current_time=`date "+%Y:%m:%d %H:%M:%S"`
echo "${current_time}: cleaning oracle archivelog"
if [ ! -d "/var/log/oracle" ]; then
echo 'mkdir oracle log'
mkdir /var/log/oracle
chown -R oracle:oinstall /var/log/oracle
fi
su - oracle << EOF1
rman target / log=/var/log/oracle/delarch_$(date +"%Y%m%d%H%M").log << EOF2
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog until time 'sysdate-14';
crosscheck archivelog all;
delete noprompt expired archivelog all;
exit;
EOF2
EOF1
echo ""
current_time=`date "+%Y:%m:%d %H:%M:%S"`
echo "${current_time}: finish clean oracle archivelog"
chown -R oracle:oinstall /etc/dbcluster/oracle_clean_arch.sh
chmod 755 /etc/dbcluster/oracle_clean_arch.sh
chmod 644 /etc/cron.d/oracle_clean_arch_crontab
15. Data Guard高可用切换
Data Guard.（最主要的功能是冗灾）
# master
SQL> select open_mode from v$database;
READ WRITE
#slave
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
备库切换为主:
$ sqlplus / as sysdba
停止应用恢复模式
alter database recover managed standby database finish;
转换standbydb为primary db
alter database commit to switchover to primary;
开启数据库
alter database open;
select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY
shutdown /normoal/immediate/abort
参考:
- Oracle数据库操作www.jianshu.com/p/099c73d85…
- 表空间、用户、Schemawww.jianshu.com/p/840966067…
