Oracle库操作
日常运维(库操作)
备库导出
能oracle用户登陆的情况（应用于测试库，可以直接ssh连上数据库）
Linux的bash命令：
today=`date +%Y%m%d-%s`
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的速度快，体积小。
不能直接登陆的情况（生产环境）
Windows批处理命令：
rem 设置年月日字符串
set "str_yymmdd=%date:~,4%%date:~5,2%%date:~8,2%"
set "user=USERNAME"
rem 导出库
expdp system/orcl directory=tgdb_databack dumpfile=%str_yymmdd%_%U.dmp logfile=%str_yymmdd%.log network_link=GZHS_DBLINK filesize=1G parallel=4 schemas=%user% compression=all
说明：
- 首先建立本地的oracle库，用来备份。
- directory建立，在Directories。
- network_link建立，在database links里面。
备库导入（linux端，ssh登录）
-
删除用户
drop user USERNAME cascade;
--无法删除用户的情况
--先查看连接用户，用户名大写
select username, sid, serial# from v$session where username='USERNAME';
--杀掉进程，先sid，后serial
alter system kill session'**,**';
--批量删除进程
SELECT 'ALTER SYSTEM KILL SESSION '||''''||SID||''''||','||''''||SERIAL#||''''||';' as KILLER FROM V$SESSION WHERE USERNAME='username';
--如果杀不掉，仍然drop不掉用户
--关闭监听器
lsnrctl stop
--打开监听器
lsnrctl start
alter system register
--关闭oracle
shutdown abort
--启动oracle
startup
-
新建用户
-
导入备库
impdp system/oracle directory=dmp_import dumpfile=a_%U.dmp logfile=a.log remap_schema=OLDNAME:NEWNAME transform=oid:n tables=SCHEMANAME.TABLENAME
nohup impdp system/oracle directory=TEST1 dumpfile=20170805_%U.DMP logfile=20170805.log remap_tablespace=YSS_GZHS:YSS_GZHS_ZZS remap_schema=GZHS:GZHS_ZZS schemas=GZHS transform=oid:n &
说明：
- transform=oid:n，这一个参数最好加上，涉及到用户权限的问题。
- remap_schema，这个参数是导出的库的用户是第一个，导入的库的用户是第二个。
- tables，是要导入的表。
其它命令
Directory
--用于创建导入目录，记住就可以
create directory dmp_import as ‘/home/oracle/dmp_import’;
--查看目录
select * from dba_directories
查看各个表的占用空间
select segment_name, sum(bytes)/1024/1024/1024 from user_segments group by segment_name order by sum(bytes) desc
select sum(bytes)/1024/1024/1024 from user_segments where segment_name in ('TD_BALANCECHECK', 'MSG_MESSAGE', 'TD_GZBCHECK')
查看各个用户的表空间
select distinct(t.tablespace_name) from dba_tab_partitions t where t.table_owner in('EA_IPMP','ES_SYSTEM','ES_DBA');
select t.table_owner,t.tablespace_name from dba_tab_partitions t where t.table_owner in('EA_IPMP','ES_SYSTEM','ES_DBA') group by t.table_owner,t.tablespace_name;
登入sql命令
sqlplus / as sysdba
启动数据库
startup
启动监听程序
lsnrctl status
表空间
--查看表空间
select tablespace_name from dba_tablespaces;
--查看表空间文件
select file_name,tablespace_name from dba_data_files;
--新建表空间
create tablespace yss_gzhs datafile size 5G autoextend on next 1G maxsize unlimited;
create tablespace YSS_GZHS datafile '/u01/oradata/tgdb136/YSS_GZHS1.dbf' size 10G autoextend on next 1G maxsize unlimited;
--表空间不够时（最大32G），加表空间
alter tablespace yss_gzhs add datafile size 5G autoextend on next 1G maxsize unlimited;
--指定文件加表空间
alter tablespace YSS_TZJD add datafile '/dbfile/tsthttg/YSS_TZJD2.dbf' size 10G autoextend on next 1G maxsize unlimited;
--指定文件加temp表空间
ALTER TABLESPACE temp ADD tempfile '/dbfile/oradata/tstdb/temp02.dbf' SIZE 5G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
--删除表空间
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
新建用户
--查看用户
select username,password from dba_users;
--新建用户
create user xbrl identified by xbrl default tablespace YSS_XBRL;
grant connect,resource,dba to xbrl;
--新建查询用户
create user queryuser identified by query12345678
--grant unlimited tablespace to queryuser;
--grant create session to queryuser;
grant connect to queryuser;
grant select any table to queryuser;
grant select any dictionary to queryuser;
--修改用户密码
alter user system identified by oracle;
--执行权限赋权
grant execute on UTL_RECOMP to es_dba;
锁定用户
--解锁用户
alter user USERNAME account unlock;
--锁定用户
alter user USERNAME account lock;
--Oracle密码错误次数过多账号锁定的问题，修改为无限次
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
--将密码设置成永不过期
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
赋权用户
-- Create the user
create user gzhsfa
identified by Myjsy,Bjwqt_0
default tablespace USERS
temporary tablespace TEMP
profile PROFILEUSER;
-- Grant/Revoke object privileges
grant execute on ISSKDFY to gzhsfa;
-- Grant/Revoke system privileges
grant create session to gzhsfa;
grant select any table to gzhsfa;
grant select,insert,update,delete on gzhs.tp_shareholder to gzhsfa;
生产环境需要的命令
- archive log list;
Oracle数据库表备份
-
create table as select方式
create table TABLENAME_YYYYMMDD as select * from TABLENAME;
缺点：
不能将原表中的default value也一同迁移过来。
not null约束，其他的约束和trigger是带不过来了，严格说来not null也是约束的一种。
-
exp/imp备份、导入单个表/多个表
--备份用户
exp USERNAME/PASSWORD@IP:PORT/INSTANCENAME file=/oradata/dmp_import/zjqs.dmp log=file.log owner=USERNAME buffer=65535
--备份指定表
exp USERNAME/PASSWORD@IP:PORT/INSTANCENAME file=/oradata/dmp_import/zjqs.dmp log=file.log tables=TABLENAME buffer=65535
--导入表、用户
imp USERNAME/PASSWORD fromuser=OLDUSERNAME touser=NEWUSERNAME commit=y ignore=y file=/oradata/dmp_import/zjqs.dmp log=file.log tables=TABLENAME
--导出导入
exp userid=USERNAME/PASSWORD@IP/SERVICENAME tables=(tmtx_plan,tmtx_planmx,tmtx_plan_pk) file=d:\temp\tmtx.dmp
imp userid=USERNAME/PASSWORD@IP/SERVICENAME tables=(tmtx_plan,tmtx_planmx,tmtx_plan_pk) file=d:\temp\tmtx.dmp
Oracle服务名
创建服务名
ALTER SYSTEM SET service_names='tljjtg' SCOPE=BOTH;
lsnrctl status会看到多出一个service来
Oracle归档
创建归档目录
mkdir /dbfile/archive
开启归档
sqlplus / as sysdba;
shutdown immediate;
startup mount;
alter database archivelog;
show parameters dest;
alter system set log_archive_dest_1='localtion=/dbfile/archive' scope=both;--如果报错
alter system set log_archive_dest_1='localtion=/dbfile/archive' scope=spfile;--如果报错
create pfile='/home/oracle/pfile.ora' from spfile;
exit;
在pfile.ora文件增加如下语句
*.log_archive_dest_1='location=/dbfile/archive'
*.log_archive_dest_state_1=enable
*.log_archive_format=tstdb_%t_%s_%r.arc
sqlplus / as sysdba;
shutdown immediate;
create spfile from pfile='/home/oracle/pfile.ora';
startup mount;
show parameters archive;
select log_mode from v$database;
alter database open;
exit;
创建delarc.sh
#!/bin/bash
. ~/.bash_profile
export ORACLE_SID=tstdb
DATE1=`date '+%Y%m%d'_%H%M%S`
$ORACLE_HOME/bin/rman << EOF
connect target / ;
run {
allocate channel ch1 device type disk;
crosscheck archivelog all;
delete noprompt archivelog until time 'sysdate-0.05';
release channel ch1;
}
exit;
EOF
加入crontab
* * */24 * * sh /home/oracle/delarc.sh
模拟切换归档日志
sqlplus / as sysdba;
alter system archive log current;
exit;
其他Oracle配置
Weblogic的Oracle数据源配置
jdbc:oracle:thin:@168.168.201.136:1521:tgdb136
PL/SQL的数据源配置
数据库填写168.168.201.136/tgdb136
Database Link
-
DBLINK创建
create public database link LINKNAME connect to USERNAME identified by PASSWORD using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.147.129.29)(PORT=2521)))(CONNECT_DATA=(SERVICE_NAME=cdrdb)))';
-
DBLINK删除
drop database link LINKNAME;
Oracle官方文档
p102025301120——Linux-x86-64_1of7.zip database安装介质
p102025301120——Linux-x86-64_2of7.zip database安装介质
p102025301120——Linux-x86-64_3of7.zip grid安装介质
p102025301120——Linux-x86-64_4of7.zip client安装介质
p102025301120——Linux-x86-64_5of7.zip gateways安装介质
p102025301120——Linux-x86-64_6of7.zip example
p102025301120——Linux-x86-64_7of7.zip deinstall
-
批量导库脚本
#!/bin/sh
str_dumpfile="20170314_%U.DMP"
str_logfile="20170314.log"
str_schemas="gzhs,zjqs"
str_directory="dmp_import"
rm -rf sql1.log sql2.log
lsnrctl stop
sqlplus sys/oracle as sysdba 1>sql1.log 2>&1 <<EOF!
shutdown abort;
startup;
drop user gzhs cascade;
drop user zjqs cascade;
create user gzhs identified by 1 default tablespace YSS_GZHS;
create user zjqs identified by 1 default tablespace YSS_ZJQS;
grant connect,resource,dba to gzhs;
grant connect,resource,dba to zjqs;
exit;
EOF!
cat sql1.log
impdp system/oracle directory=${str_directory} dumpfile=${str_dumpfile} logfile=${str_l
ogfile} schemas=${str_schemas} transform=oid:n parallel=8
lsnrctl start
sqlplus sys/oracle as sysdba 1>sql2.log 2>&1 <<EOF!
set autocommit on;
CREATE OR REPLACE FUNCTION MD5(
id VARCHAR2)
RETURN VARCHAR2
IS
retval varchar2(32);
BEGIN
retval := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => id));
RETURN retval;
end;
/
update gzhs.tp_asset set fsetname=MD5(fsetname);
update gzhs.tp_asset set ffullname=MD5(ffullname);
update zjqs.t_para_product set fname=MD5(fname);
update zjqs.t_acct_fundaccount set fshort_name =SUBSTR(MD5(fshort_name),1,20) where fsh
ort_name is not null;update zjqs.t_deploy_server set FIPADDRESS='168.168.204.89',FPORT=7002,FURL='http://168
.168.204.89:7002/sofa' where FIPADDRESS='168.12.1.58';update gzhs.ts_user set FPASSWORD='670b14728ad9902aecba32e22fa4f6bd';
update zjqs.t_user set FPWD='670b14728ad9902aecba32e22fa4f6bd';
alter system register;
exit;
EOF!
cat sql2.log
