Oracle庫(kù)操作
日常運(yùn)維(庫(kù)操作)
備庫(kù)導(dǎo)出
能oracle用戶登陸的情況（應(yīng)用于測(cè)試庫(kù)瓮栗，可以直接ssh連上數(shù)據(jù)庫(kù)）
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
說(shuō)明：
- directory要事先創(chuàng)建辩块。
- %U是01烙常、02、03等等召耘。
- parallel并行命令不一定有用百炬。
- schemas可以理解為數(shù)據(jù)庫(kù)用戶名。
- 規(guī)定filesize可以防止文件過(guò)大怎茫。
- compression=all或NONE收壕，all的速度快妓灌，體積小轨蛤。
不能直接登陸的情況（生產(chǎn)環(huán)境）
Windows批處理命令：
rem 設(shè)置年月日字符串
set "str_yymmdd=%date:~,4%%date:~5,2%%date:~8,2%"
set "user=USERNAME"
rem 導(dǎo)出庫(kù)
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
說(shuō)明：
- 首先建立本地的oracle庫(kù)蜜宪，用來(lái)備份。
- directory建立祥山，在Directories圃验。
- network_link建立，在database links里面缝呕。
備庫(kù)導(dǎo)入（linux端澳窑，ssh登錄）
-
刪除用戶
drop user USERNAME cascade;
--無(wú)法刪除用戶的情況
--先查看連接用戶，用戶名大寫(xiě)
select username, sid, serial# from v$session where username='USERNAME';
--殺掉進(jìn)程供常，先sid摊聋，后serial
alter system kill session'**,**';
--批量刪除進(jìn)程
SELECT 'ALTER SYSTEM KILL SESSION '||''''||SID||''''||','||''''||SERIAL#||''''||';' as KILLER FROM V$SESSION WHERE USERNAME='username';
--如果殺不掉，仍然drop不掉用戶
--關(guān)閉監(jiān)聽(tīng)器
lsnrctl stop
--打開(kāi)監(jiān)聽(tīng)器
lsnrctl start
alter system register
--關(guān)閉oracle
shutdown abort
--啟動(dòng)oracle
startup
-
新建用戶
-
導(dǎo)入備庫(kù)
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 &
說(shuō)明：
- transform=oid:n栈暇，這一個(gè)參數(shù)最好加上麻裁，涉及到用戶權(quán)限的問(wèn)題。
- remap_schema源祈，這個(gè)參數(shù)是導(dǎo)出的庫(kù)的用戶是第一個(gè)煎源，導(dǎo)入的庫(kù)的用戶是第二個(gè)。
- tables香缺，是要導(dǎo)入的表手销。
其它命令
Directory
--用于創(chuàng)建導(dǎo)入目錄，記住就可以
create directory dmp_import as ‘/home/oracle/dmp_import’;
--查看目錄
select * from dba_directories
查看各個(gè)表的占用空間
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')
查看各個(gè)用戶的表空間
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
啟動(dòng)數(shù)據(jù)庫(kù)
startup
啟動(dòng)監(jiān)聽(tīng)程序
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;
--表空間不夠時(shí)（最大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;
--執(zhí)行權(quán)限賦權(quán)
grant execute on UTL_RECOMP to es_dba;
鎖定用戶
--解鎖用戶
alter user USERNAME account unlock;
--鎖定用戶
alter user USERNAME account lock;
--Oracle密碼錯(cuò)誤次數(shù)過(guò)多賬號(hào)鎖定的問(wèn)題锋拖，修改為無(wú)限次
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
--將密碼設(shè)置成永不過(guò)期
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
賦權(quán)用戶
-- 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;
生產(chǎn)環(huán)境需要的命令
- archive log list;
Oracle數(shù)據(jù)庫(kù)表備份
-
create table as select方式
create table TABLENAME_YYYYMMDD as select * from TABLENAME;
缺點(diǎn)：
不能將原表中的default value也一同遷移過(guò)來(lái)。
not null約束祸轮，其他的約束和trigger是帶不過(guò)來(lái)了姑隅，嚴(yán)格說(shuō)來(lái)not null也是約束的一種。
-
exp/imp備份倔撞、導(dǎo)入單個(gè)表/多個(gè)表
--備份用戶
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
--導(dǎo)入表讲仰、用戶
imp USERNAME/PASSWORD fromuser=OLDUSERNAME touser=NEWUSERNAME commit=y ignore=y file=/oradata/dmp_import/zjqs.dmp log=file.log tables=TABLENAME
--導(dǎo)出導(dǎo)入
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服務(wù)名
創(chuàng)建服務(wù)名
ALTER SYSTEM SET service_names='tljjtg' SCOPE=BOTH;
lsnrctl status會(huì)看到多出一個(gè)service來(lái)
Oracle歸檔
創(chuàng)建歸檔目錄
mkdir /dbfile/archive
開(kāi)啟歸檔
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;--如果報(bào)錯(cuò)
alter system set log_archive_dest_1='localtion=/dbfile/archive' scope=spfile;--如果報(bào)錯(cuò)
create pfile='/home/oracle/pfile.ora' from spfile;
exit;
在pfile.ora文件增加如下語(yǔ)句
*.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;
創(chuàng)建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數(shù)據(jù)源配置
jdbc:oracle:thin:@168.168.201.136:1521:tgdb136
PL/SQL的數(shù)據(jù)源配置
數(shù)據(jù)庫(kù)填寫(xiě)168.168.201.136/tgdb136
Database Link
-
DBLINK創(chuàng)建
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安裝介質(zhì)
p102025301120——Linux-x86-64_2of7.zip database安裝介質(zhì)
p102025301120——Linux-x86-64_3of7.zip grid安裝介質(zhì)
p102025301120——Linux-x86-64_4of7.zip client安裝介質(zhì)
p102025301120——Linux-x86-64_5of7.zip gateways安裝介質(zhì)
p102025301120——Linux-x86-64_6of7.zip example
p102025301120——Linux-x86-64_7of7.zip deinstall
-
批量導(dǎo)庫(kù)腳本
#!/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
