小麥苗的常用程式碼(僅限自己使用)
點選(此處)摺疊或開啟
-
?
-
●
-
?
-
◆
-
※
-
☆
-
⊙
-
-
-
-
-
------GBK:
-
=E6=B5=B7=E6=BB=A8 (=E5=8F ---> LHR (=E5=8F
-
------3DUTF-8:
-
=E6=B5=B7=E6=BB=A8 (=E5=8F ---> =E5=B0=8F=E9=BA=A6=E8=8B=97 (=E5=8F
-
-
-
---小麥苗
-
3DUTF-8:=E5=B0=8F=E9=BA=A6=E8=8B=97
-
3DGBK: =D0=A1=C2=F3=C3=E7
-
-
-
-
-
-
-
-
-
----- editplus 替換空行: ^[ \t]*\n EDIT -> DELETE->DELETE BLANK LINES
-
-
----- editplus 替換以#開頭的行,多次執行: ^#[^\n]*\n
-
-
-
-
windows不支援的檔名:\ / : * ? " < > |
-
-
\ 反斜槓、捺斜槓
-
/ 正斜槓、撇斜槓
-
-
製表符 chr(9)
-
換行符 chr(10)
-
回車符 chr(13)
-
-
-
-
1s=1000ms(毫秒)=1000000(微秒)
-
-
-
-
-
-------------------------------常用日期
-
-
月份 全拼 簡拼 示例
-
1 January Jan
-
2 February Feb
-
3 March Mar
-
4 April Apr
-
5 May May
-
6 June Jun
-
7 July Jul
-
8 August Aug
-
9 September Sep
-
10 October Oct Sat Aug 13 10:54:45 2016
-
11 November Nov Tue Nov 29 02:56:59 2016
-
12 December Dec Tue Dec 06 08:51:57 2016
-
-
-
-
-
-
星期 全拼 簡拼 示例
-
1 Monday Mon Mon Dec 05 01:04:18 2016
-
2 Tuesday Tue Tue Dec 01 16:21:37 2016
-
3 Wednesday Web
-
4 Thursday Thu Thu Dec 01 08:36:03 2016
-
5 Friday Fri Fri Dec 02 16:17:17 2016
-
6 Saturday Sat Sat Dec 10 14:13:34 2016
-
7 Sunday Sun
-
-
-
-
-
-
-
---------------------------BBED
-
1.1 我的編譯程式碼
-
ls -l $ORACLE_HOME/rdbms/lib/*sbbd* */
-
ls -l $ORACLE_HOME/rdbms/mesg/bbed* */
-
chown oracle:dba /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/*sbbd* */
-
chown oracle:dba /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/bbed* */
-
-
--cd $ORACLE_HOME/rdbms/lib
-
--make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
-
-
--make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
-
make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed
-
-
1.2 我的使用程式碼
-
vi /home/oracle/file.txt
-
set line 9999 pagesize 9999
-
col name format a80
-
select file#||' '||name||' '||bytes name from v$datafile;
-
-
vi /home/oracle/bbed.par
-
blocksize=8192
-
listfile=/home/oracle/file.txt
-
mode=edit
-
-
bbed parfile=/home/oracle/bbed.par
-
bbed PASSWORD=blockedit mode=edit blocksize=8192 listfile=/home/oracle/file.txt
-
-
-
-
---------secureCRT中vi 顯示彩色
-
[root@rhel6_lhr ~]# vi .bashrc
-
alias vi='vim'
-
-
[root@rhel6_lhr ~]# vi /etc/profile
-
export TERM=xterm-color
-
-
-
-
secureCRT 中刪除用 shift+delete 鍵或 ctrl + backspace 鍵
-
-
ctrl+? 清理當前行命令
-
-
---------------------------- root 配置
-
-------------- AIX
-
chmod +w /etc/profile
-
echo "
-
umask 022
-
export ORACLE_HOME=/u01/app/11.2.0/grid
-
export PATH="\$PATH:\$ORACLE_HOME/bin"
-
export PS1='[\$LOGNAME@'\`hostname\`:'\$PWD'']# '
-
set -o vi
-
alias l=\""ls -l\""
-
alias ll=\""ls -l\""
-
" >> /etc/profile
-
. /etc/profile
-
-
echo "
-
umask 022
-
export ORACLE_HOME=/u01/app/11.2.0/grid
-
export PATH="\$PATH:\$ORACLE_HOME/bin"
-
export PS1='[\$LOGNAME@'\`hostname\`:'\$PWD'']# '
-
set -o vi
-
alias l=\""ls -l\""
-
alias ll=\""ls -l\""
-
" >> ~/.profile
-
. ~/.profile
-
-
-
-------------- Linux
-
chmod +w /etc/profile
-
echo "
-
umask 022
-
export ORACLE_HOME=/u01/app/11.2.0/grid
-
export PATH="\$PATH:\$ORACLE_HOME/bin"
-
export PS1='[\$LOGNAME@'\`hostname\`:'\$PWD'']# '
-
export TERM=xterm-color
-
set -o vi
-
alias l=\""ls -l\""
-
alias ll=\""ls -l\""
-
" >> /etc/profile
-
. /etc/profile
-
-
echo "
-
umask 022
-
export ORACLE_HOME=/u01/app/11.2.0/grid
-
export PATH="\$PATH:\$ORACLE_HOME/bin"
-
export PS1='[\$LOGNAME@'\`hostname\`:'\$PWD'']# '
-
set -o vi
-
alias l=\""ls -l\""
-
alias ll=\""ls -l\""
-
" >> ~/.bash_profile
-
. ~/.bash_profile
-
-
-
-
--export PS1="[\u@\h \W]\$ "
-
--export PS1='[$LOGNAME@'`hostname`:'$PWD'']# '
-
echo "export PS1='[\$LOGNAME@'\`hostname\`:'\$PWD'']# '" > /etc/profile
-
export PS1='[$LOGNAME@'`hostname`:'$PWD'']# '
-
-
-
-
-------------------------------------------------------------------------------- linux
-
-
-
export HISTTIMEFORMAT='%F %T '
-
export HISTSIZE=100000
-
export HISTFILESIZE=100000
-
-
-
Ctrl+R 搜尋命令歷史 ,當找到命令後,通常再按Enter鍵就可以執行該命令。如果想對找到的命令進行調整後再執行,則可以按一下左或右方向鍵。
-
-
-
-
-
-
-
-
------------------------------------------------------------------------------------------------- cmd 命令
-
-
mstsc 遠端桌面
-
firewall.cpl 和 wf.msc 防火牆設定
-
services.msc 服務
-
cmd
-
msconfig 開啟啟動項
-
-
-
dxdiag Direct11
-
-
-
-
-
----開啟遠端桌面
-
1、計算機——右鍵——屬性——遠端設定——遠端,選中“允許允許任意版本遠端桌面的計算機連線”
-
2、關閉防火牆
-
netsh firewall set opmode disable
-
netsh advfirewall set publicprofile state off
-
-
3、services.msc 開啟服務,然後 找到 Remote Desktop 相關的三個服務,確保狀態為“已啟動”
-
-
-
-
-
C:\Users\華榮>set /a a=8500*12
-
102000
-
C:\Users\華榮>
-
-
-
-
電腦設定豆沙綠: 視窗、活動視窗標題2
-
色度,飽和度,亮度:85 123 205 80 100 200 100 120 200 76 91 205
-
RGB:207,232,204
-
-
cmd介面:0,128,128
-
程式碼背景:141,179,226
-
-
-
------- cmd 介面字型和介面背景顏色
-
背景顏色: 0 128 128
-
HKEY_CURRENT_USER\Console\%SystemRoot%_system32_cmd.exe 修改%SystemRoot%_system32_cmd.exe下,若無該項可以修改HKEY_CURRENT_USER\Console下,將DWORD型別的CodePage項修改為十進位制值936,將字串型別的FaceName改為Lucida Console ,若沒有該項則可以新建該項
-
-
-
chcp 437
-
-
cmd 介面按下F7顯示歷史命令
-
-
-
----cmd 下不換行
-
host set /p=start...資料庫巡檢服務概要. <nul
-
host set /p=. <nul
-
host echo ..end
-
-
-
-
-
----- 鎖屏 windows + L 鍵
-
-
----------系統環境變數
-
-
Windows Registry Editor Version 5.00
-
-
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment]
-
"NLS_DATE_FORMAT" "YYYY-MM-DD HH24:mi:ss"
-
"NLS_LANG AMERICAN_CHINA.ZHS16GBK
-
"ORACLE10G D:\Program files\app\oracle\product\10.2.0\db_1
-
"ORACLE11G D:\Program files\app\oracle\product\11.2.0.1\dbhome_1
-
"ORACLE8I D:\Program files\app\oracle\product\ora8i
-
"ORACLE9I D:\Program files\app\oracle\product\ora92
-
"ORACLE_HOME D:\Program files\app\oracle\product\11.2.0.1\dbhome_1
-
"TNS_ADMIN D:\Program files\app\oracle\product\11.2.0.1\dbhome_1\NETWORK\ADMIN
-
-
----------使用者環境變數
-
Windows Registry Editor Version 5.00
-
-
[HKEY_CURRENT_USER\Environment]
-
"NLS_DATE_FORMAT"="YYYY-MM-DD HH24:MI:SS"
-
"NLS_LANG"="AMERICAN_CHINA.ZHS16GBK"
-
-
-
-
---------cmd下設定使用者環境變數
-
SETX "NLS_DATE_FORMAT" "YYYY-MM-DD HH24:MI:SS"
-
SETX "NLS_LANG" "AMERICAN_CHINA.ZHS16GBK"
-
SETX "ORACLE_HOME" "D:\Program files\app\oracle\product\11.2.0.1\dbhome_1"
-
SETX "TNS_ADMIN" "D:\Program files\app\oracle\product\11.2.0.1\dbhome_1\NETWORK\ADMIN"
-
SETX "PATH" "%ORACLE_HOME%\bin"
-
SETX "VBOX_MSI_INSTALL_PATH" "D:\Program Files\Oracle\VirtualBox"
-
-
SETX "ORACLE_HOME" "%cd%\oracle\product\11.2.0.1\dbhome_1"
-
-
-
-
SETX "ORACLE_HOME" "D:\Program files\app\oracle\product\11.2.0.1\dbhome_1"
-
SETX "TNS_ADMIN" "%%ORACLE_HOME%%\network\admin"
-
SETX "PATH" "%path%;%%ORACLE_HOME%%\bin"
-
SETX "NLS_DATE_FORMAT" "YYYY-MM-DD HH24:MI:SS"
-
SETX "NLS_LANG" "AMERICAN_CHINA.ZHS16GBK"
-
-
-
-
-
alter session set nls_language='SIMPLIFIED CHINESE';
-
alter system set nls_language='AMERICAN' scope=spfile;
-
-
export EDITOR=vi
-
export ORACLE_SID=orclasm
-
export ORACLE_BASE=/u01/app/oracle
-
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
-
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
-
export ORACLE_ALERT=$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace
-
export PATH=$ORACLE_HOME/bin:$PATH
-
export TNS_ADMIN=$ORACLE_HOME/network/admin
-
export ORACLE_PATH=.:$ORACLE_BASE/dba_scripts/sql:$ORACLE_HOME/rdbms/admin
-
umask 022
-
-
#export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK" SELECT userenv('LANGUAGE') db_NLS_LANG FROM DUAL;
-
#export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
-
export SQLPATH=$ORACLE_HOME/sqlplus/admin
-
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss';
-
-
#alias sqlplus='rlwrap sqlplus'
-
#alias rman='rlwrap rman'
-
#alias asmcmd='rlwrap asmcmd'
-
alias alert_log='tail -200f $ORACLE_ALERT/alert_$ORACLE_SID.log'
-
alias alert_xml='tail -200f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/alert/log.xml'
-
alias alert_listener='tail -200f $ORACLE_BASE/diag/tnslsnr/rhel6/listener/trace/listener.log'
-
-
-
-
-
-
-
-
----- 如何啟用 Administrator(xp、windows7)
-
net user Administrator /active:yes
-
單擊“開始→執行”,輸入regedit後回車,開啟登錄檔編輯器,依次展開 "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\SpecialAccounts\UserList" 分支
-
將右邊的Administrator的值改為1,即可讓Administrator賬戶出現在登入的歡迎螢幕上。(如果沒有Administrator的話就建立一個DWORD(32位)值型別,其它上級專案沒有的話也依次建立)
-
-
----- 在PE中修改源系統的登錄檔
-
-
啟動登錄檔編輯器,點選HKEY_LOCAL_MACHINE,然後點【檔案】-【載入配置單元】,瀏覽檔案,進入 Win7安裝的分割槽 " c:\Windows\System32\config" 裡面的SYSTEM、DEFAULT、SOFTWARE都可以選擇,選擇後讓你輸入名稱,隨便輸入,如byiu輸入後點確定就會在HKEY_LOCAL_MACHINE下面增加那個分支,然後就可以編輯了。
-
-
-
-
---新建administrator
-
點開HKEY_LOCAL_MACHINE,點開SAM,右擊SAM,點選許可權..在組或使用者名稱稱下點選 Administrators,點選完全控制對應的允許核取方塊,點選確定
-
在登錄檔編輯器視窗按F5重新整理。點開:"HKEY_LOCAL_MAICHINE\SAM\SAM\Domains\Account\Users\Names"
-
匯入如下注冊表,即Administrator使用者:
-
Windows Registry Editor Version 5.00
-
-
[HKEY_LOCAL_MACHINE\SAM\SAM\Domains\Account\Users\Names\Administrator]
-
@=hex(1f4):
-
-
-
----------------禁用U盤 啟用U盤
-
----- 啟用usb大容量儲存裝置 預設設定為“3”表示手動,“2”是表示自動,“4”是表示停用,一般設定為3
-
-
--禁用
-
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\USBSTOR" /v Start /d 4 /t reg_dword /f
-
-
--啟用
-
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\USBSTOR" /v Start /d 3 /t reg_dword /f
-
-
-
---------------------- 檢視網站是否通
-
-
telnet 192.168.59.130 1158
-
-
---怎樣從DOS/Linux的telnet中退出
-
-
我們經常要用到telnet來測試FTP埠,但是有一個問題估計大家都會遇到,在telnet進入某個埠後無法退出,沒辦法,為了繼續測試只好關掉這個dos視窗,重新開啟一個。 這裡教給你一個方法,按住ctrl+]就可以退出到
-
-
-
Welcome to Microsoft Telnet Client
-
Escape Character is 'CTRL+]'
-
Microsoft Telnet>
-
然後再輸入quit就可以完全退出了。
-
-
-
-
-
-
unzip /media/linux.x64_11gR2_/linux.x64_11gR2_database_1of2.zip -d /tmp && unzip /media/linux.x64_11gR2_/linux.x64_11gR2_database_2of2.zip -d /tmp > /dev/null 2>&1
-
unzip p10404530_112030_Linux-x86-64_1of7.zip -d /tmp && unzip p10404530_112030_Linux-x86-64_2of7.zip -d /tmp && unzip p10404530_112030_Linux-x86-64_3of7.zip -d /tmp > /dev/null 2>&1
-
-
mysql -u root -p
-
-
-
-
----------------------------- 科學計數法
-
12345678901,結果為 1.23E+10,即 1.23 乘以 10 的 10 次冪
-
12300000000
-
-
-
-
4.1E+11
-
410 000 000 000
-
-
-
-
----------------------------------------- crontab
-
每天0點02分 2 0 * * * /home/weblogic/lhr/ods_scripts/new_ods2/main_ods_entity_de_lhr.sh
-
每天凌晨1點3分 3 1 * * * /home/weblogic/bin/crm_address_intf_1.sh
-
每週日18點 0 18 * * 0 /home/weblogic/lhr/ods_scripts/new_ods2/main_weekly.sh
-
每週三18點 0 18 * * 3 /home/weblogic/bin/crm_inf_linkresource_monthly.sh
-
-
-
--root使用者可以檢視其它使用者的crontab
-
crontab -u zhangsan -l
-
crontab -u lisi -l
-
crontab -u wangwu -l
-
-
-
-
-
nohup sh rman_backup_full.sh 2>&1 &
-
-
-
-
-
MYDATE=`date +'%Y-%m-%d %H:%M:%S'`
-
-
-
sqlplus lhr/lhr@192.168.128.134:1521/orclasm.lhr.com
-
tnsping 192.168.0.123:1521/dev.us.oracle.com
-
-
drop user lhr cascade;
-
-
NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280"
-
NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
-
NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
-
NLS_LANG=AMERICAN_AMERICA.UTF8
-
-
alias alert_log='tail -200f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
-
-
-
-
drop tablespace temp including contents and datafiles;
-
ALTER TABLESPACE temp DROP TEMPFILE '/u01/app/oracle/oradata/orcl/orclasm/tempfile/temp.264.850260283';
-
ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 20M;
-
-
-
--表空間大小:
-
set pagesize 9999 line 9999
-
col TS_Name format a30
-
WITH wt1 AS
-
(SELECT ts.TABLESPACE_NAME,
-
df.all_bytes,
-
decode(df.TYPE,
-
'D',
-
nvl(fs.FREESIZ, 0),
-
'T',
-
df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ,
-
df.MAXSIZ,
-
ts.BLOCK_SIZE,
-
ts.LOGGING,
-
ts.FORCE_LOGGING,
-
ts.CONTENTS,
-
ts.EXTENT_MANAGEMENT,
-
ts.SEGMENT_SPACE_MANAGEMENT,
-
ts.RETENTION,
-
ts.DEF_TAB_COMPRESSION,
-
df.ts_df_count
-
FROM dba_tablespaces ts,
-
(SELECT 'D' TYPE,
-
TABLESPACE_NAME,
-
COUNT(*) ts_df_count,
-
SUM(BYTES) all_bytes,
-
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ
-
FROM dba_data_files d
-
GROUP BY TABLESPACE_NAME
-
UNION ALL
-
SELECT 'T',
-
TABLESPACE_NAME,
-
COUNT(*) ts_df_count,
-
SUM(BYTES) all_bytes,
-
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))
-
FROM dba_temp_files d
-
GROUP BY TABLESPACE_NAME) df,
-
(SELECT TABLESPACE_NAME,
-
SUM(BYTES) FREESIZ
-
FROM dba_free_space
-
GROUP BY TABLESPACE_NAME
-
UNION ALL
-
SELECT tablespace_name,
-
SUM(d.BLOCK_SIZE * a.BLOCKS) bytes
-
FROM gv$sort_usage a,
-
dba_tablespaces d
-
WHERE a.tablespace = d.tablespace_name
-
GROUP BY tablespace_name) fs
-
WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME
-
AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))
-
SELECT (SELECT A.TS#
-
FROM V$TABLESPACE A
-
WHERE A.NAME = UPPER(t.TABLESPACE_NAME)) TS#,
-
t.TABLESPACE_NAME TS_Name,
-
round(t.all_bytes / 1024 / 1024) ts_size_M,
-
round(t.freesiz / 1024 / 1024) Free_Size_M,
-
round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
-
round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 3) Used_per,
-
round(MAXSIZ / 1024 / 1024/1024, 3) MAX_Size_g,
-
round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 /
-
MAXSIZ,
-
3) USED_per_MAX,
-
round(t.BLOCK_SIZE) BLOCK_SIZE,
-
t.LOGGING,
-
t.ts_df_count
-
FROM wt1 t
-
UNION ALL
-
SELECT to_number('') TS#,
-
'ALL TS:' TS_Name,
-
round(SUM(t.all_bytes) / 1024 / 1024, 3) ts_size_M,
-
round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m,
-
round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
-
round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 3) Used_per,
-
round(SUM(MAXSIZ) / 1024 / 1024/1024) MAX_Size,
-
to_number('') "USED,% of MAX Size",
-
to_number('') BLOCK_SIZE,
-
'' LOGGING,
-
to_number('') ts_df_count
-
FROM wt1 t
-
order by TS#
-
;
-
-
-
WITH wt1 AS
-
(SELECT df.TABLESPACE_NAME,
-
df.all_bytes,
-
df.MAXSIZ,
-
nvl(fs.FREESIZ, 0) FREESIZ
-
FROM (SELECT TABLESPACE_NAME,
-
SUM(BYTES) all_bytes,
-
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ
-
FROM dba_data_files d
-
GROUP BY TABLESPACE_NAME) df,
-
(SELECT TABLESPACE_NAME, SUM(BYTES) FREESIZ
-
FROM dba_free_space
-
GROUP BY TABLESPACE_NAME) fs
-
WHERE fs.TABLESPACE_NAME(+) = df.TABLESPACE_NAME)
-
SELECT t.TABLESPACE_NAME TS_Name,
-
round(t.all_bytes / 1024 / 1024, 2) ts_size_m,
-
round(t.freesiz / 1024 / 1024, 2) Free_Size_m,
-
round((t.all_bytes - t.FREESIZ) / 1024 / 1024, 2) Used_Size_m,
-
round(MAXSIZ / 1024 / 1024 / 1024, 2) MAX_Size_g,
-
round((MAXSIZ - (t.all_bytes - t.FREESIZ)) / 1024 / 1024 / 1024, 2) MAX_Size_free_g,
-
(t.all_bytes) ts_size,
-
(t.freesiz) Free_Size,
-
(t.all_bytes - t.FREESIZ) Used_Size,
-
(MAXSIZ) MAX_Size,
-
((MAXSIZ - (t.all_bytes - t.FREESIZ))) MAX_Size_free
-
FROM wt1 t;
-
-
-
-
-
-
------ 資料檔案情況
-
SELECT d.FILE_ID,
-
d.TABLESPACE_NAME,
-
(SELECT round(SUM(nb.BYTES) / 1024 / 1024, 2)
-
FROM dba_data_files nb
-
WHERE nb.TABLESPACE_NAME = d.TABLESPACE_NAME) ts_size_m,
-
d.FILE_NAME,
-
round(d.BYTES / 1024 / 1024, 2) file_size_m,
-
round(d.MAXBYTES / 1024 / 1024 / 1024, 2) file_max_size_G,
-
d.AUTOEXTENSIBLE,
-
round(d.INCREMENT_BY * 8 * 1024 / 1024 / 1024, 2) INCREMENT_m,
-
round(d.BYTES * 100 / decode(d.MAXBYTES, 0, BYTES, d.MAXBYTES), 2) autoextend_ratio,
-
(SELECT b.CREATION_TIME
-
FROM sys.v_$datafile b
-
where b.FILE# = d.FILE_ID) CREATION_TIME,
-
d.INCREMENT_BY INCREMENT_BY_block,
-
d.BYTES,
-
d.blocks,
-
d.MAXBYTES,
-
d.MAXBLOCKS,
-
d.USER_BYTES,
-
d.USER_BLOCKS
-
FROM dba_data_files d
-
UNION ALL
-
SELECT d.FILE_ID,
-
d.TABLESPACE_NAME,
-
(SELECT round(SUM(nb.BYTES) / 1024 / 1024, 2)
-
FROM v$tempfile nb
-
WHERE nb.name = d.FILE_NAME) ts_size,
-
d.FILE_NAME,
-
round(d.BYTES / 1024 / 1024, 2) file_size_m,
-
round(d.MAXBYTES / 1024 / 1024 / 1024, 2) file_max_size_G,
-
d.AUTOEXTENSIBLE,
-
round(d.INCREMENT_BY * 8 * 1024 / 1024 / 1024, 2) INCREMENT_m,
-
round(d.BYTES * 100 / decode(d.MAXBYTES, 0, BYTES, d.MAXBYTES), 2) autoextend_ratio,
-
(SELECT b.CREATION_TIME
-
FROM sys.v_$datafile b
-
where b.FILE# = d.FILE_ID) CREATION_TIME,
-
d.INCREMENT_BY INCREMENT_BY_block,
-
d.BYTES,
-
d.blocks,
-
d.MAXBYTES,
-
d.MAXBLOCKS,
-
d.USER_BYTES,
-
d.USER_BLOCKS
-
FROM dba_temp_files d
-
ORDER BY TABLESPACE_NAME, file_id;
-
-
-
-
-----檢視字符集
-
select SYS_CONTEXT('USERENV', 'LANGUAGE') from dual;
-
select userenv('language') from dual;
-
select * from v$nls_parameters;
-
-
-
---密碼檔案 linux區分$ORACLE_SID大小寫 sysdba select* from v$pwfile_users;
-
--linux:orapw+$ORACLE_SID
-
--windows: pwd+$ORACLE_SID.ora
-
oradim -NEW -sid orcl9i -INTPWD admin -pfile d:\oracle\ora90\database\initstorm.ora;
-
C:\Users\Administrator> orapwd file="E:\oracle\ora8i\DATABASE\PWDortest.ORA" password=lhr
-
[oracle@robinson dbs]$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle force=y
-
-
sc delete OracleOraDb11g_home1TNSListener
-
sc create OracleOraDb11g_home1TNSListener binpath= "F:\app\oracle\product\BIN\TNSLSNR" start= auto displayname= "OracleOraDb11g_home1TNSListener"
-
-
-
--11g中密碼大小寫敏感
-
(1) sec_case_sensitive_logon引數可以指定使用者的口令是否區分大小寫,預設為true,表示區分大小寫
-
(2) 口令檔案中的ignorecase 引數僅僅針對建立時是否區分大小寫,簡言之,只能針對SYS使用者,預設為N,表示不忽略大小寫,即區分大小寫
-
-
-
--通過設定EVENTS 28401可以遮蔽密碼延遲驗證:
-
SQL> ALTER SYSTEM SET EVENT = '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' SCOPE = SPFILE;
-
設定該事件後重啟資料庫即可。
-
-
-
-
-
------------------------------------------------ selinux
-
-
檢視SELinux狀態:
-
1、/usr/sbin/sestatus -v ##如果SELinux status引數為enabled即為開啟狀態
-
SELinux status: enabled
-
2、getenforce ##也可以用這個命令檢查
-
關閉SELinux:
-
1、臨時關閉(不用重啟機器):
-
setenforce 0 ##設定SELinux 成為permissive模式
-
##setenforce 1 設定SELinux 成為enforcing模式
-
2、修改配置檔案需要重啟機器:
-
修改/etc/selinux/config 檔案
-
將SELINUX=enforcing改為SELINUX=disabled
-
重啟機器即可
-
-
-
-
------------------------------------------------------------ linux
-
儲存區域網路(Storage Area Network,SAN)
-
-
----設定密碼永不過期:
-
chage -M -1 oracle
-
chage -l oracle
-
-
-
-
-
-
-
-
lsb_release-a
-
cat /etc/issue
-
uname -a
-
cat /proc/version
-
-
---linux位數檢視
-
file /bin/ls
-
getconf LONG_BIT
-
arch
-
-
---AIX系統檢視
-
-
顯示AIX系統核心是32位還是64位:
-
-
bootinfo -K
-
-
顯示機器硬體是32位還是64位:
-
-
bootinfo -y
-
-
--- SUN:
-
-
$isainfo -bv
-
-
64表示核心是64位的,32表示核心是32位的
-
-
--- HPUX:
-
-
>getconf KERNEL_BITS
-
-
64表示核心是64位的,32表示核心是32位的
-
-
HPUX:
-
-
>getconf KERNEL_BITS
-
-
64表示核心是64位的,32表示核心是32位的
-
-
-
-
------------------ AIX系統使用者解鎖
-
3.1 AIX使用者賬戶鎖定與解鎖最佳方法
-
3004-303 There have been too many unsuccessful login attempts; please see
-
the system administrator.
-
-
與之相關的配置引數是/etc/security/login.cfg的以下配置項
-
-
logindisable=7 *7次失敗登入後鎖定埠
-
logininterval=120 *在120秒內7次失敗登入才鎖定埠
-
-
1、如果你可以登陸到ROOT賬戶,比較簡單
-
使用chsec命令即可解鎖,具體如下:
-
# chsec -f /etc/security/lastlog -a unsuccessful_login_count=0 -s username
-
通過重置未成功登陸的次數即可解鎖
-
-
2、如果是通過設定來鎖定的使用者,可以這樣解鎖
-
#【smitty user】-->【Lock / Unlock a User's Account】
-
或是
-
# chsec -f /etc/security/lastlog -a unsuccessful_login_count=0 -s username
-
-
3、命令解鎖
-
# chuser account_locked=TRUE username 給使用者加鎖
-
# chuser accout_locked=FALSE username 給使用者解鎖
-
-
-
-
------------大寫G跳到最後一行,o新插入一行
-
AIX開啟自動補全:
-
方法一:
-
set -o vi
-
自動補全 esc \
-
歷史命令 esc -
-
HJKL
-
左下上右
-
-
A 跳到行末,進入編輯模式
-
I 跳到行首,進入編輯模式
-
X 鍵刪除游標前一個字元停留在原來的那個字元
-
-
AIX開啟自動補全:
-
方法一:
-
set -o vi
-
自動補全 esc \
-
歷史命令 esc -
-
esc j
-
esc k
-
i a x 編輯
-
游標移動 : h l
-
HJKL
-
左下上右
-
-
A 跳到行末,進入編輯模式
-
I 跳到行首,進入編輯模式
-
X 鍵刪除游標前一個字元停留在原來的那個字元
-
-
-
-
-
方法二:
-
set -o emacs
-
自動補全 按兩次esc
-
歷史命令 ctrl-n 或 ctrl-p
-
-
-
-
-
-
more /etc/profile
-
more /etc/environment
-
export TMOUT=0;
-
-
-
oslevel -qs
-
-
-
-
---清磁碟頭
-
dd if=/dev/zero of=/dev/rhdisk5 bs=1024 count=1024
-
-
-
-
##檢視PV大小,單位M AIX 查詢磁碟大小 硬碟大小
-
for HDISK in `lspv | grep -v hdisk0 | awk '{print $1}'`;do
-
bootinfo -s $HDISK
-
done
-
for diskname in `lspv|grep -i none|cut -f "1" -d ' '`
-
do
-
echo "/dev/r$diskname" `getconf DISK_SIZE /dev/r$diskname`
-
done
-
-
-
-
-
-
---程式控制程式碼
-
-
lsof -p pid
-
-
---告警日誌位置
-
lsof | grep diag
-
lsof | grep bdump
-
-
-
show parameter background_dump_dest
-
$ORACLE_BASE/ADMIN/SID/BDUMP/ALERTSID.LOG
-
-
--根據實際情況決定是否加upper函式
-
SELECT VALUE || substr(d.VALUE, -6, 1) || 'alert_' || b.INSTANCE_Name ||'.log' alertname
-
FROM v$parameter d, v$instance b
-
WHERE d.NAME = 'background_dump_dest';
-
-
-
-
-
--1 埠是否佔用
-
netstat -apn | grep 1521
-
netstat -ano|grep 1521
-
netstat -lnp|grep 1521
-
-
----檢查包忽略大小寫
-
rpm -qa | grep -i AAA
-
-
--2 殺死所有程式
-
kill -9 `ps -ef|grep orcl| grep -v grep | awk '{print $2}'`
-
-
ps -ef |grep $ORACLE_SID|grep -v grep|awk '{print $2}' | xargs kill -9
-
ipcs -m | grep oracle | awk '{print $2}' | xargs ipcrm shm
-
-
-
-
-
--程式總數
-
ps -ef|grep orcl| wc -l
-
-
---統計行數
-
wc - lcw a.txt
-
-
- c 統計位元組數。
-
-
- l 統計行數。
-
-
- w 統計字數。
-
-
-
-- 匹配多個程式號
-
[root@node1 node1]# ps -ef | grep ' 27373 \| 27182 '
-
oracle 27182 1 0 14:50 ? 00:00:00 ora_pz99_jmrac1
-
oracle 27373 1 0 14:53 ? 00:00:00 ora_w000_jmrac1
-
root 27574 9150 0 14:56 pts/1 00:00:00 grep 27373 \| 27182
-
[oracle@orcltest shm]$ ps -ef | egrep '(12545|12543)' |grep -v grep
-
oracle 12543 1 0 07:41 ? 00:00:02 ora_pmon_ogg1
-
oracle 12545 1 0 07:41 ? 00:00:04 ora_psp0_ogg1
-
[oracle@orcltest shm]$
-
-
-
-
-- 匹配多個字串
-
[root@node1 node1]# ps -ef | grep -v grep | grep -E "ohasd.bin|crs|ocssd|evmd|oproc"
-
root 2372 1 0 09:33 ? 00:00:25 /u01/grid/bin/crsd.bin reboot
-
root 5051 1 0 05:36 ? 00:01:02 /u01/grid/bin/ohasd.bin reboot
-
grid 5313 1 0 05:37 ? 00:01:45 /u01/grid/bin/ocssd.bin
-
grid 5500 1 0 05:37 ? 00:00:13 /u01/grid/bin/evmd.bin
-
[root@node1 node1]#
-
-
-
--3 關閉防火牆
-
chkconfig iptables off ---永久
-
service iptables stop ---臨時
-
chkconfig iptables --list
-
/etc/init.d/iptables status ----會得到一系列資訊,說明防火牆開著。
-
/etc/rc.d/init.d/iptables stop ----------關閉防火牆
-
setup ----------圖形介面
-
-
--將/etc/sysconfig/iptables檔案新增一行,表示允許1521埠訪問:
-
[root@dcsopen2Node sysconfig]# vi /etc/sysconfig/iptables
-
# Firewall configuration written by system-config-firewall
-
# Manual customization of this file is not recommended.
-
*filter
-
:INPUT ACCEPT [0:0]
-
:FORWARD ACCEPT [0:0]
-
:OUTPUT ACCEPT [0:0]
-
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-
-A INPUT -p icmp -j ACCEPT
-
-A INPUT -i lo -j ACCEPT
-
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-
-A INPUT -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT
-
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
-
COMMIT
-
-
-
-
find / -type f -size +10000000c -exec du -sh {} \; 查詢大於10M的檔案
-
find . -name '*.phtml' -type f -mmin -30 查詢當前目錄下.phtml檔案中,最近30分鐘內修改過的檔案。
-
find . -name '*.phtml' -type f -mmin -30 -ls 查詢當前目錄下.phtml檔案中,最近30分鐘內修改過的檔案,的詳細情況。
-
find . -type f -mtime -1 查詢當前目錄下,最近1天內修改過的常規檔案
-
find . -type f -mtime +1 查詢當前目錄下,最近1天前(2天內)修改過的常規檔案。
-
-
find . -ctime +3 -exec rm -rf {} \; #刪除一個目錄下幾天前的檔案和目錄
-
-
find / -type f -size +10000000c -exec du -sh {} \; 2>/dev/null #查詢大於10M的檔案並列出檔案大小
-
find /home -size +10k #意思是說查詢/home目錄下大小為10k的檔案
-
-
find . -type f -mtime 0
-
find . -type f -mtime +1
-
find . -name '*.doc' -mtime 0
-
-
find / -name access_log 2>/dev/null
-
find . -name '*.doc' 2>/dev/null
-
-
-
find / -amin -10 # 查詢在系統中最後10分鐘訪問的檔案
-
find / -atime -2 # 查詢在系統中最後48小時訪問的檔案
-
-
find /tmp -size +10000000c -and -mtime +2
-
find /tmp -size +10000000c -or -mtime +2
-
-
find / -empty # 查詢在系統中為空的檔案或者資料夾
-
find / -group cat # 查詢在系統中屬於 groupcat的檔案
-
find / -mmin -5 # 查詢在系統中最後5分鐘裡修改過的檔案
-
find / -mtime -1 #查詢在系統中最後24小時裡修改過的檔案
-
find / -nouser #查詢在系統中屬於作廢使用者的檔案
-
find / -user fred #查詢在系統中屬於FRED這個使用者的檔案
-
-
-
find . -type f -mtime 0 -exec ls -lrt {} \; --檢視當天修改過的檔案
-
-
-
find . -type f -mtime 0 #最近24小時內修改過的檔案
-
find . -type f -mtime 1 #前48~24小時內修改過的檔案,而不是48小時以內修改過的檔案
-
---近3天內修改過的檔案
-
find . -type f -mtime 0 -o -mtime 1 -o -mtime 2
-
find . -type f -mtime 0 -or -mtime 1 -or -mtime 2
-
-
-
-
-
-
-
-
-
-
-
-
-
-
目錄大小: du -h --max-depth=1 . 2>&1
-
du -h --max-depth=0 /tmp/database/
-
du -sh database/
-
find . -ctime +3 -exec rm -rf {} \; 刪除一個目錄下幾天前的檔案和目錄
-
find / -type f -size +10000000c -exec du -sh {} \; 查詢大於10M的檔案
-
du -s /*|sort -rn 檢視目錄大小
-
-
-
/**/
-
-
-------- AIX資料夾大小
-
du -sg app/11.2.0/grid/* | sort -rn /* */
-
du -ag app/11.2.0/grid/* | sort -rn /* */
-
du -g /oracle/app/11.2.0/* | sort -rn | more /* */
-
-
-
-
-
系統啟動時間:
-
date -d "$(awk -F. '{print $1}' /proc/uptime) second ago" +"%Y-%m-%d %H:%M:%S"
-
-
-
-
-
create public database link dblink_ogg1
-
connect to lhr identified by lhr
-
using '(DESCRIPTION =
-
(ADDRESS_LIST =
-
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.129)(PORT = 1521))
-
)
-
(CONNECT_DATA =
-
(SERVICE_NAME = ogg1)
-
)
-
)';
-
-
-
create public database link DBLINK_OGG1
-
connect to LHR identified by lhr
-
using 'OGG1';
-
-
-
-
----linux檢視檔案的詳細時間
-
ls ctlfile*20150212* --full-time|awk '{print $6,$7,$9}'
-
-
-
-----------新增磁碟
-
fdisk -l
-
fdisk /dev/sdf
-
...
-
mkfs.ext4 /dev/sdf1
-
[root@rhel6_lhr ~]# mkdir /u03
-
[root@rhel6_lhr ~]# mount /dev/sdf1 /u03
-
[root@rhel6_lhr ~]# vi /etc/fstab
-
/dev/sdf1 /u03 ext4 defaults 0 0
-
-
-
chown oracle:oinstall /u04
-
-
-
linux 修改主機名,永久生效:
-
vim /etc/sysconfig/network
-
vim /etc/hosts
-
-
-
aix 修改主機名:
-
修改主機名暫時生效:
-
hostname NEW_HOSTNAME
-
永久生效 smit hostname
-
或者 smit tcpip - futher configureation - hostname -set the hostname
-
uname -S hostname
-
或者直接用命令 chdev -l inet0 -a hostname=NEW_HOSTNAME
-
-
||||||||||||||||||||
-
正確更改IP 地址是用 smit tcpip 進入選單之後,選擇further configuration 再選 Network Interfaces,再選 Network Interface Selection,
-
再選 Change /show characteristic of a network interface來更改 IP,這樣/etc/hosts就不會新加入一條記錄,只需更改檔案中相應的IP就行了。
-
-
-
-
-
-
-------------------------------------------------------------- 閃回恢復區滿
-
select * from v$flash_recovery_area_usage;
-
ALTER SYSTEM SET db_recovery_file_dest_size='2G';
-
-
--關閉閃回恢復區
-
alter system set db_recovery_file_dest='';
-
-
-
--EXPIRED可以理解為失效的備份集,即物理檔案丟失。OBSOLETE可以理解為過期的備份集。
-
CROSSCHECK ARCHIVELOG ALL;
-
LIST EXPIRED ARCHIVELOG ALL;
-
DELETE EXPIRED ARCHIVELOG ALL;
-
-
-
-
-
--錯誤記錄
-
v_error := SQLCODE || ',' || SQLERRM || chr(13) ||dbms_utility.format_error_backtrace;
-
--DML行數
-
v_count :=TO_CHAR(SQL%ROWCOUNT);
-
-
-
-----------塊改變跟蹤
-
alter system set db_create_file_dest = '/u01/bct/' scope=both sid='*';
-
alter database enable block change tracking;
-
-
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+FRA';
-
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/mydir/rman_change_track.f' REUSE;
-
-
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
-
COL STATUS FORMAT A8
-
COL FILENAME FORMAT A60
-
SELECT STATUS, FILENAME,BYTES FROM V$BLOCK_CHANGE_TRACKING;
-
-
-
-
SELECT file#,
-
AVG(datafile_blocks),
-
AVG(blocks_read),
-
AVG(blocks_read / datafile_blocks) * 100 AS PCT_READ_FOR_BACKUP,
-
AVG(blocks)
-
FROM v$backup_datafile
-
WHERE used_change_tracking = 'YES'
-
AND incremental_level > 0
-
GROUP BY file#;
-
-
-
-
-------------------------------------------------------------- 聯機重做日誌
-
----------清除未歸檔日誌
-
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
-
----------清空損壞的日誌檔案 成功執行前提:一致性關閉,解決ora-00392錯誤
-
ALTER DATABASE CLEAR LOGFILE GROUP 2;
-
-
-
----- 新增 redo
-
alter database add logfile group 4 'c:\oracle\oradata\orcl\redo04.log' size 100m;
-
alter database add logfile;
-
alter database add logfile [group n];
-
alter database add logfile member '<dir>' to group [n] /*add logfile member這個方法僅使用未使用OMF的日誌檔案,對於已經運用了OMF的日誌組,無法使用該功能新增日誌檔案*/
-
-
alter database add logfile thread 1 group 4 ('+DATA','+FRA') size 50M;
-
-
-
---rac庫可以在同一個例項下新增
-
alter database add logfile thread 1 group 7('+DATA_DG/querydb/redo07_1.log','+DATA_DG/querydb/redo07_2.log') size 1024m;
-
alter database add logfile thread 2 group 8('+DATA_DG/querydb/redo08_1.log','+DATA_DG/querydb/redo08_2.log') size 1024m;
-
-
-
-
-
--- 刪除
-
alter database drop logfile group 4;
-
alter database drop logfile member '';
-
-
-
-
--------重新命名redo
-
SQL> ho cp /u03/app/oracle/oradata/ora1024g/redo03.log /u03/app/oracle/oradata/ora1024g/redo04.log
-
SQL> alter database rename file '/u03/app/oracle/oradata/ora1024g/redo03.log' to '/u03/app/oracle/oradata/ora1024g/redo04.log';
-
-
-
--------重新命名 表空間 重新命名錶空間
-
alter tablespace users rename to users01;
-
-
-
-
-
-
----------------- 一個查詢慢的sql例子
-
select count(1) from dba_objects a
-
inner join user_objects b on 1=1
-
inner join user_objects c on 1=1
-
;
-
-
------------------------------------------------------------------------------------------------------------------------------------------------------------------- 構造大表
-
-
select level,level from dual connect by level<=1000;
-
-
-
-----------外部表
-
CREATE DIRECTORY EXT_LOG AS '/tmp';
-
DROP TABLE ALERT_LOG_lhr2;
-
CREATE TABLE ALERT_LOG_lhr2(
-
TEXT VARCHAR2(4000)
-
)ORGANIZATION EXTERNAL
-
(TYPE ORACLE_LOADER
-
DEFAULT DIRECTORY EXT_LOG
-
ACCESS PARAMETERS
-
(RECORDS DELIMITED BY NEWLINE CHARACTERSET utf8
-
nobadfile
-
nodiscardfile
-
nologfile
-
FIELDS TERMINATED BY 0X'0D' LDRTRIM
-
REJECT ROWS WITH ALL NULL FIELDS
-
)LOCATION('lhr1.txt')
-
) reject limit unlimited ;
-
-
-
-
----------檔案格式
-
df -hT
-
-
--------------ORA-00845: MEMORY_TARGET not supported on this system
-
辦法: 修改/etc/fstab
-
tmpfs /dev/shm tmpfs defaults,size=1.5G 0 0
-
-
[root@FWDB ~]# mount -o remount /dev/shm
-
-
-
-
簡單來說就是 MEMORY_MAX_TARGET 的設定不能超過 /dev/shm 的大小:
-
-
-
[oracle@FWDB FWDB]$ df -h | grep shm
-
tmpfs 2.0G 0 2.0G 0% /dev/shm
-
-
馬上把它加大:
-
-
-
[root@FWDB ~]# cat /etc/fstab | grep tmpfs
-
tmpfs /dev/shm tmpfs defaults,size=4G 0 0
-
現在可以通過重啟使這個配置生效,也可以通過重新掛載來修改其大小:
-
-
[root@FWDB ~]# mount -o remount,size=4G /dev/shm
-
[root@FWDB ~]# df -h | grep shm
-
tmpfs 4.0G 0 4.0G 0% /dev/shm
-
再次啟動資料庫,沒有報錯了。
-
-
二、修改/dev/shm大小
-
-
預設的最大一半記憶體大小在某些場合可能不夠用,並且預設的inode數量很低一般都要調高些,這時可以用mount命令來管理它。
-
#mount -o size=1500M -o nr_inodes=1000000 -o noatime,nodiratime -o remount /dev/shm
-
在2G的機器上,將最大容量調到1.5G,並且inode數量調到1000000,這意味著大致可存入最多一百萬個小檔案。
-
如果需要永久修改/dev/shm的值,需要修改/etc/fstab
-
tmpfs /dev/shm tmpfs defaults,size=1.5G 0 0
-
-
mount -o remount /dev/shm
-
-
-
umount tmpfs
-
mount -t tmpfs shmfs -o size=3000m /dev/shm
-
vi /etc/fstab
-
-
-
-
-
---red hat重啟網路卡
-
service network restart
-
/etc/rc.d/init.d/network restart
-
-
---suse重啟網路卡
-
service network restart
-
rcnetwork restart
-
/etc/rc.d/init.d/network restart
-
-
-
----解除安裝網路卡
-
ifconfig eth0 down
-
-
--- 單獨重啟網路卡
-
ifdown eth0 && ifup eth0
-
ifconfig eth0 down && ifconfig eth0 up
-
-
-
----------------------------------------------- 固定IP 配置靜態ip地址
-
ifconfig eth1 192.168.210.108 broadcast 192.168.210.254 netmask 255.255.255.0
-
ifconfig eth1 192.168.59.130 broadcast 192.168.59.1 netmask 255.255.255.0
-
ifconfig eth0 192.168.59.130 netmask 255.255.255.0 gw 192.168.129.1
-
-
-
-
chkconfig NetworkManager off
-
chkconfig network on
-
service NetworkManager stop
-
service network start
-
-
---若還有問題,可刪掉網路卡重新新增
-
vi /etc/sysconfig/network-scripts/ifcfg-eth0
-
vi /etc/udev/rules.d/70-persistent-net.rules
-
DEVICE=eth0
-
IPADDR=192.168.59.130
-
NETMASK=255.255.255.0
-
NETWORK=192.168.59.0
-
BROADCAST=192.168.59.255
-
GATEWAY=192.168.59.2
-
ONBOOT=yes
-
USERCTL=no
-
BOOTPROTO=static
-
#HWADDR=00:0c:29:97:f1:5b
-
TYPE=Ethernet
-
IPV6INIT=no
-
DNS1=202.96.209.5
-
DNS2=8.8.8.8
-
NAME="System eth0"
-
-
-
-
-
-
----------------- 動態ip地址
-
DEVICE=eth0
-
ONBOOT=yes
-
USERCTL=no
-
BOOTPROTO=dhcp
-
HWADDR=00:0c:29:97:f1:5b
-
TYPE=Ethernet
-
PEERDNS=yes
-
IPV6INIT=no
-
-
-
-
-
-
[root@rhel6 ~]# export LANG=C
-
[root@rhel6 ~]# setup
-
[root@rhel6 ~]#
-
-
-
-
-
------------------------------------------------- 修改主機名
-
永久生效:
-
[root@zijuan /]# vim /etc/sysconfig/network
-
NETWORKING=yes
-
NETWORKING_IPV6=yes
-
HOSTNAME=zijuan
-
-
HOSTNAME=zijuan表示主機設定為zijuan.
-
注意:修改主機名後,需要重啟系統後生效,或者切換個使用者然後切換回來就OK
-
-
-
檢視/etc/hosts檔案中必須包含a fully qualified name for the server
-
[root@localhost lhr]# cat /etc/hosts
-
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
-
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
-
192.168.59.140 dg
-
[root@localhost lhr]# hostname
-
localhost.localdomain
-
[root@localhost lhr]# hostname dg
-
[root@localhost lhr]# hostname
-
dg
-
-
-
-
-
-
-
-
-
-
-----歸檔格式
-
alter system set log_archive_format = "log_oradg10g_%d_%t_%s_%r.arc" scope=spfile;
-
-
-
-
---------------------------------------- drop database 刪除資料庫
-
-
1、dbca靜默刪庫:dbca -silent -deleteDatabase -sourceDB mydb
-
2、SQL視窗:
-
alter database close;
-
alter system enable restricted session;
-
drop database;
-
3、SQL視窗:
-
sql > startup force mount restrict;
-
sql > drop database;
-
注意:強烈推薦第一種辦法,以上2和3的辦法若是rac庫需要設定cluster_database為false後才可以執行drop database,命令為:alter system set cluster_database=false sid='*' scope=spfile;
-
-
-
-
-
-------------------------------------- 配置本地yum源
-
-
-----------rhel 6.5
-
mkdir -p /media/lhr/cdrom
-
mount /dev/sr0 /media/lhr/cdrom/
-
#設定開機自動掛載系統映象檔案 vi /etc/fstab 新增以下內容
-
/dev/sr0 /media/cdrom iso9660 defaults,ro,loop 0 0
-
-
cd /etc/yum.repos.d/
-
cp rhel-media.repo rhel-media.repo.bk
-
vi /etc/yum.repos.d/rhel-media.repo
-
[rhel-media]
-
name=Red Hat Enterprise Linux 6.5
-
baseurl=file:///media/cdrom
-
enabled=1
-
gpgcheck=1
-
gpgkey=file:///media/cdrom/RPM-GPG-KEY-redhat-release
-
-
-
yum install httpd #安裝命令
-
yum install -y *sz*
-
rpm -ivh lrzsz-0.12.20-27.1.el6.x86_64.rpm
-
-
yum -y remove mysql-libs-5.1.71
-
yum list | grep mysql
-
rpm -e --nodeps mysql-libs.x86_64
-
-
-----------------命令後rpm包被下載到了什麼地方
-
每次在執行完yum命令後,系統都會把需要用到的rpm包放在/var/cache/yum/這個目錄下,但下載源的不同還是會放在不同源目錄下。
-
find /var/cache/yum/ -name kmod-oracleasm*
-
-
-
-
-------------- rhel5.5
-
# mkdir /media/cdrom
-
編輯 /etc/fstab 檔案,在檔案尾部新增如下內容,以便開機自動掛載光碟:
-
[root@localhost ~]# tail -1 /etc/fstab
-
/dev/cdrom /media/cdrom iso9660 defaults 0 0
-
[root@localhost ~]#
-
[root@localhost ~]# mount -a
-
mount: block device /dev/sr0 is write-protected, mounting read-only
-
[root@localhost ~]#
-
清空並編輯 YUM 源配置檔案
-
清空 /etc/yum.repos.d/rhel-debuginfo.repo 檔案並新增以下內容:
-
[root@localhost ~]# cp /etc/yum.repos.d/rhel-debuginfo.repo /etc/yum.repos.d/rhel-debuginfo.repo.bak
-
[root@localhost ~]# cat /etc/yum.repos.d/rhel-debuginfo.repo
-
[rhel-debuginfo]
-
name=Red Hat Enterprise Linux $releasever - $basearch - Debug
-
baseurl=file:///media/cdrom
-
enabled=0
-
gpgcheck=0
-
-
# vi /etc/yum.repos.d/my.repo
-
[Oracle]
-
name=OEL-$releasever – Media
-
baseurl=file:///mnt/Server
-
gpgcheck=0
-
enabled=1
-
如果是RHEL或者CentOS,請先將/etc/yum.repos.d下面的檔案刪除或者移動到別的目錄下,RHEL建立方法和OEL一樣,CentOS則baseurl=file:///mnt/ 即可,因為CentOS的repodata目錄就在光碟根下。
-
-
-
-
-
------檢視資源的屬性值:
-
[root@rac2 ~]# crsctl stat res ora.cluster_interconnect.haip -p -init | grep ENABLED
-
ENABLED=0
-
[root@rac2 ~]# crsctl stat res ora.asm -p -init | grep START_DEPENDENCIES
-
START_DEPENDENCIES=hard(ora.cssd,ora.ctssd)pullup(ora.cssd,ora.ctssd)weak(ora.drivers.acfs)
-
[root@rac2 ~]#
-
-
-
---修改資源的屬性值
-
crsctl modify resource ora.<diskgroup>.dg -attr AUTO_START=always
-
-
---啟動磁碟組
-
srvctl start diskgroup -g data -n "rac2"
-
-
-
------------- 新增rac資料庫到叢集
-
srvctl add database -d DGPHY -c RAC -o /oracle/app/oracle/product/11.2.0/db -p '+DATA/TESTDGPHY/PARAMETERFILE/spfiledgphy.ora' -r physical_standby -n TESTDG
-
-
srvctl add instance -d DGPHY -i DGPHY1 -n ZFZHLHRDB1
-
srvctl add instance -d DGPHY -i DGPHY2 -n ZFZHLHRDB2
-
-
srvctl status database -d DGPHY
-
srvctl start database -d TESTDG
-
-
srvctl remove database -d DGPHY
-
-
--新增監聽到crs 必須是grid新增
-
[ZFZHLHRDB3:grid]:/home/grid>srvctl add listener -l LISTENER_LHRDG -p 1523 -o $ORACLE_HOME
-
srvctl config listener -l LISTENER_LHRDG -a
-
① rac新增LISTENER資源的時候需要使用grid使用者
-
② srvctl config檢視資源的具體配置情況
-
③ srvctl modify可以修改資源的配置
-
-
-
--新增單例項
-
srvctl add database -d LHRDGPRI -c SINGLE -o /oracle/app/oracle/product/11.2.0/db -p '/oracle/app/oracle/product/11.2.0/db/dbs/spfileLHRDGPHY1.ora' -r physical_standby -n LHRDGPRI -x ZFZHLHRDB1 -i LHRDGPRI
-
-
-
-
-
crsctl status resource ora.asm -f
-
crsctl modify resource ora.asm -attr "GEN_USR_ORA_INST_NAME@SERVERNAME(zfxdeskdb1)=+ASM1"
-
crsctl modify resource ora.asm -attr "GEN_USR_ORA_INST_NAME@SERVERNAME(zfxdeskdb2)=+ASM2"
-
srvctl stop asm -f
-
-
-
-
-------------- 11g rac 修改歸檔 alter system set log_archive_dest_1='LOCATION=/arch/DGPHY' scope=spfile sid='*';
-
SQL>alter system set log_archive_dest_1='LOCATION=+FRA/GUITAR/ARC1' scope=spfile sid='guitar1';
-
Diskgroup altered.
-
-
SQL>alter system set log_archive_dest_1='LOCATION=+FRA/GUITAR/ARC2' scope=spfile sid='guitar2';
-
Diskgroup altered.
-
-
乾淨關閉資料庫然後啟動庫到mount,在其中一個例項上執行alter database archivelog 然後開啟資料庫即可。
-
-
[root@node1 ~]# srvctl stop database -d jmrac -o immediate
-
[root@node1 ~]# srvctl start database -d jmrac -o mount
-
-
-
-
------------------ mgmtdb
-
srvctl stop mgmtdb
-
srvctl status mgmtdb
-
srvctl config mgmtdb
-
srvctl disable mgmtdb
-
srvctl disable mgmtlsnr
-
srvctl add mgmtdb
-
srvctl config mgmtdb
-
srvctl disable mgmtdb
-
srvctl enable mgmtdb
-
srvctl getenv mgmtdb
-
srvctl modify mgmtdb
-
srvctl relocate mgmtdb
-
srvctl remove mgmtdb
-
srvctl setenv mgmtdb
-
srvctl start mgmtdb
-
srvctl status mgmtdb
-
srvctl stop mgmtdb
-
srvctl unsetenv mgmtdb
-
srvctl add mgmtlsnr
-
srvctl config mgmtlsnr
-
srvctl disable mgmtlsnr
-
srvctl enable mgmtlsnr
-
srvctl getenv mgmtlsnr
-
srvctl modify mgmtlsnr
-
srvctl remove mgmtlsnr
-
srvctl setenv mgmtlsnr
-
srvctl start mgmtlsnr
-
srvctl status mgmtlsnr
-
srvctl stop mgmtlsnr
-
srvctl unsetenv mgmtlsnr
-
-
-
[grid@raclhr-12cR1-N1 ~]$ export ORACLE_SID=-MGMTDB
-
[grid@raclhr-12cR1-N1 ~]$ sqlplus / as sysdba
-
-
Database unique name: _mgmtdb
-
Database name: _mgmtdb
-
Oracle user: grid
-
Database instance: -MGMTDB
-
service_names:_mgmtdb
-
-
-
------------------------------- export display
-
export DISPLAY=192.168.59.1:0.0
-
xhost +
-
-
-
-
在linux系統中用Oracle帳號執行DBCA或其他JAVA圖形介面程式時,報錯:
-
-
Xlib: connection to ":0.0" refused by server
-
Xlib: No protocol specified
-
-
Error: Can't open display: :0.0
-
-
解決辦法:用root登陸,在#提示符後輸入:
-
-
xhost local:oracle
-
-
-
-----------------------------iSCSI target
-
more /etc/ietd.conf --配置檔案
-
service iscsi-target start #啟動iSCSI target
-
cat /proc/net/iet/volume #檢視iSCSI-target共享出的硬碟
-
cat /proc/net/iet/session #檢視客戶端(initiator端)登陸到target的情況
-
-
-
-----------------------------iSCSI initiator
-
more /etc/iscsi/initiatorname.iscsi
-
more /etc/iscsi/iscsid.conf
-
-
iscsiadm -m discovery -t sendtargets -p 192.168.59.200:3260
-
iscsiadm -m node -T iqn.2006-01.com.openfiler:tsn.5e423e1e4d90 -p 192.168.59.200:3260 -l
-
-
iscsiadm --mode discovery --type sendtargets --portal 192.168.59.200
-
iscsiadm --mode node --targetname iqn.2006-01.com.openfiler:tsn.5e423e1e4d90 –portal 192.168.59.200:3260 --login
-
iscsiadm -m session -P 3
-
-
-
service iscsi start #啟動iSCSI initiator
-
/etc/init.d/iscsi start
-
-
service iscsi start
-
service iscsid start
-
-
chkconfig iscsi on
-
chkconfig iscsid on
-
-
chkconfig --level 2345 iscsi on
-
chkconfig --list|grep iscsi
-
-
-
-
-
-
-------------------------- 多路徑常用命令
-
rpm -qa|grep multipath
-
modprobe dm-multipath
-
modprobe dm-round-robin
-
lsmod |grep multipath
-
chkconfig --level 2345 multipathd on
-
chkconfig --list|grep multipathd
-
chkconfig --list multipathd
-
multipath -ll
-
-
---------------獲取wwid
-
--在RHEL 6中,可以通過如下方式獲取磁碟wwid:
-
for i in `cat /proc/partitions | awk {'print $4'} |grep sd`; do echo "### $i: `scsi_id --whitelist /dev/$i`"; done
-
-- 在RHEL 5中,可以通過如下方式獲取磁碟wwid:
-
for i in `cat /proc/partitions | awk {'print $4'} |grep sd`; do echo "### $i: `scsi_id -g -u -s /block/$i`"; done
-
-
-
-
-------------------------------------------------------------- ASM
-
---------------------------- oracleasm常用命令
-
/usr/sbin/oracleasm configure -i
-
/usr/sbin/oracleasm createdisk DISK1 /dev/sdb1
-
/etc/init.d/oracleasm scandisks
-
/etc/init.d/oracleasm listdisks
-
/etc/init.d/oracleasm enable
-
/usr/sbin/oracleasm enable
-
/usr/sbin/oracleasm restart
-
/usr/sbin/oracleasm createdisk DISKNAME devicename
-
/usr/sbin/oracleasm deletedisk DISKNAME
-
/usr/sbin/oracleasm querydisk {DISKNAME | devicename}
-
/usr/sbin/oracleasm listdisks
-
/usr/sbin/oracleasm scandisks
-
-
-
-
-
-
------ ASM磁碟
-
$ORACLE_HOME/bin/kfod disk=asm s=true ds=true c=true
-
/grid/stage/ext/bin/kfod disk=asm s=true ds=true c=true
-
-
-
-
create diskgroup DATA external redundancy disk '/dev/raw/raw*';
-
create diskgroup FRA external redundancy disk '/dev/rhdisk3'; --建立磁碟組FRA
-
CREATE DISKGROUP ACFSDG external redundancy DISK '/dev/oracleasm/disks/VOL1' ATTRIBUTE 'compatible.asm' = '11.2','compatible.rdbms' = '11.2','compatible.advm'='11.2';
-
create diskgroup OCR exteRnal redundancy disk 'ORCL:OVDISK' attribute 'compatible.asm'='11.2','compatible.rdbms'='11.2';
-
-
-
-
-
---修改磁碟組的相容屬性
-
ALTER DISKGROUP asm_dg SET ATTRIBUTE 'compatible.asm' = '11.1';
-
ALTER DISKGROUP asm_dg SET ATTRIBUTE 'compatible.rdbms' = '11.1';
-
-
COLUMN name FORMAT A10
-
COLUMN compatibility FORMAT A20
-
COLUMN database_compatibility FORMAT A20
-
SELECT group_number, name, compatibility, database_compatibility FROM v$asm_diskgroup;
-
-
set line 9999
-
set pagesize 9999
-
col path format a60
-
SELECT a.group_number, disk_number,mount_status, a.name, path FROM v$asm_disk a order by a.disk_number;
-
select instance_name,status from v$instance;
-
-
set line 999
-
select name,state,free_mb,required_mirror_free_mb,usable_file_mb,a.group_number, disk_number,mount_status, path from v$asm_diskgroup a;
-
select a.group_number,name,TYPE,state,TOTAL_MB,free_mb from v$asm_diskgroup a;
-
select name,state,free_mb,required_mirror_free_mb,usable_file_mb,a.group_number from v$asm_diskgroup a;
-
-
-
-
-
alter diskgroup DG1 mount;
-
-
-
---nomount狀態下強制刪除磁碟組
-
drop diskgroup oradg force including contents;
-
alter diskgroup DG1 drop disk DG1_VOL5; --刪除磁碟組DG1中的磁碟VOL5
-
-
alter system set asm_diskstring='/dev/asm-disk*','/dev/raw/raw*';
-
alter diskgroup DATA add disk '/dev/raw/raw1';
-
-
-
-
-
------------------------ faking asmdisk asm磁碟
-
---- 新增loop裝置個數
-
第一種辦法:修改 /etc/modprobe.conf 檔案新增引數:options loop max_loop=20 可以通過 modprobe -v loop 命令立即載入該模組,或重啟
-
第二種辦法(通用):mknod -m 0660 /dev/loopX b 7 X
-
-
raw -qa
-
losetup -a
-
--mknod -m 0660 /dev/loopX b 7 X
-
mknod -m 0660 /dev/loop9 b 7 9
-
-
-
-
mkdir /asmdisk
-
dd if=/dev/zero of=/asmdisk/disk1 bs=1024k count=2000
-
dd if=/dev/zero of=/asmdisk/disk2 bs=1024k count=2000
-
dd if=/dev/zero of=/asmdisk/disk3 bs=1024k count=2000
-
dd if=/dev/zero of=/asmdisk/disk4 bs=1024k count=2000
-
dd if=/dev/zero of=/asmdisk/disk5 bs=1024k count=2000
-
-
/sbin/losetup /dev/loop1 /asmdisk/disk1
-
/sbin/losetup /dev/loop2 /asmdisk/disk2
-
/sbin/losetup /dev/loop3 /asmdisk/disk3
-
/sbin/losetup /dev/loop4 /asmdisk/disk4
-
/sbin/losetup /dev/loop5 /asmdisk/disk5
-
-
raw /dev/raw/raw1 /dev/loop1
-
raw /dev/raw/raw2 /dev/loop2
-
raw /dev/raw/raw3 /dev/loop3
-
raw /dev/raw/raw4 /dev/loop4
-
raw /dev/raw/raw5 /dev/loop5
-
-
chmod 660 /dev/raw/raw1
-
chmod 660 /dev/raw/raw2
-
chmod 660 /dev/raw/raw3
-
chmod 660 /dev/raw/raw4
-
chmod 660 /dev/raw/raw5
-
chown oracle:dba /dev/raw/raw1
-
chown oracle:dba /dev/raw/raw2
-
chown oracle:dba /dev/raw/raw3
-
chown oracle:dba /dev/raw/raw4
-
chown oracle:dba /dev/raw/raw5
-
-
-
------Add the following entries to the file "/etc/rc.local"
-
/sbin/losetup /dev/loop1 /asmdisk/disk1
-
/sbin/losetup /dev/loop2 /asmdisk/disk2
-
/sbin/losetup /dev/loop3 /asmdisk/disk3
-
/sbin/losetup /dev/loop4 /asmdisk/disk4
-
/sbin/losetup /dev/loop5 /asmdisk/disk5
-
-
raw /dev/raw/raw1 /dev/loop1
-
raw /dev/raw/raw2 /dev/loop2
-
raw /dev/raw/raw3 /dev/loop3
-
raw /dev/raw/raw4 /dev/loop4
-
raw /dev/raw/raw5 /dev/loop5
-
-
chmod 660 /dev/raw/raw1
-
chmod 660 /dev/raw/raw2
-
chmod 660 /dev/raw/raw3
-
chmod 660 /dev/raw/raw4
-
chmod 660 /dev/raw/raw5
-
chown oracle:dba /dev/raw/raw1
-
chown oracle:dba /dev/raw/raw2
-
chown oracle:dba /dev/raw/raw3
-
chown oracle:dba /dev/raw/raw4
-
chown oracle:dba /dev/raw/raw5
-
-
-
-
-
------------------------ Simulating Asm by faking hardware
-
-->Faking Hardware
-
-->Instaling ASM Lib
-
-->Configuring the disks
-
-->Install DB & ASM instance
-
-
---Faking Hardware: root 使用者
-
-
mkdir /asmdisk
-
dd if=/dev/zero of=/asmdisk/disk1 bs=1024k count=2000
-
dd if=/dev/zero of=/asmdisk/disk2 bs=1024k count=2000
-
-
/sbin/losetup /dev/loop1 /asmdisk/disk1
-
/sbin/losetup /dev/loop2 /asmdisk/disk2
-
-
raw /dev/raw/raw1 /dev/loop1
-
raw /dev/raw/raw2 /dev/loop2
-
-
chmod 660 /dev/raw/raw1
-
chmod 660 /dev/raw/raw2
-
chown grid:asmadmin /dev/raw/raw1
-
chown grid:asmadmin /dev/raw/raw2
-
-
-
------Add the following entries to the file "/etc/rc.local"
-
echo "/sbin/losetup /dev/loop1 /asmdisk/disk1" >>/etc/rc.local
-
echo "/sbin/losetup /dev/loop2 /asmdisk/disk2" >>/etc/rc.local
-
-
-
-
-
--------------------------------- oracle 日誌
-
-
oracleasm日誌: tail -f /var/log/oracleasm
-
oracle agent日誌: tail -f /u01/app/11.2.0/grid/log/rhel5/agent/ohasd/oraagent_grid/oraagent_grid.log
-
asm 告警日誌:alert_log='tail -200f $ORACLE_BASE/diag/asm/+asm/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
-
CRS 啟動日誌: more $ORACLE_HOME/log/$HOSTNAME/crsd/crsd.log
-
-
------查詢:find /u01/app/ -name crsd.log
-
crs日誌地址:/u01/app/11.2.0/grid/log/rac2/crsd/crsd.log
-
-
-
oracle 10g 告警日誌: /u02/app/oracle/admin/ora10g/bdump
-
oracle 11g 告警日誌: select value from v$diag_info where name='Default Trace File';
-
-
-
System Control Statement 系統控制語句 alter system
-
-
-
-
-
------------------------------------------------------------------------------------------- 恢復到new host
-
-
set pagesize 200 linesize 200
-
select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";'
-
from v$datafile a
-
union all
-
select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";'
-
from v$tempfile a
-
union all
-
SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''' ||
-
a.MEMBER || ''''' ";'
-
FROM v$logfile a;
-
-
-
-
RUN
-
{
-
# allocate a channel to the tape device
-
# ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS '...';
-
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
-
-
-
# rename the data files and online redo logs
-
SET NEWNAME FOR DATAFILE 1 TO '?/oradata/test/system01.dbf';
-
SET NEWNAME FOR DATAFILE 2 TO '?/oradata/test/undotbs01.dbf';
-
SET NEWNAME FOR DATAFILE 3 TO '?/oradata/test/cwmlite01.dbf';
-
SET NEWNAME FOR DATAFILE 4 TO '?/oradata/test/drsys01.dbf';
-
SET NEWNAME FOR DATAFILE 5 TO '?/oradata/test/example01.dbf';
-
SET NEWNAME FOR DATAFILE 6 TO '?/oradata/test/indx01.dbf';
-
SET NEWNAME FOR DATAFILE 7 TO '?/oradata/test/tools01.dbf';
-
SET NEWNAME FOR DATAFILE 8 TO '?/oradata/test/users01.dbf';
-
SET NEWNAME FOR TEMPFILE 1 TO '?/oradata/test/temp01.dbf';
-
-
SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo01.log''
-
TO ''?/oradata/test/redo01.log'' ";
-
SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo02.log''
-
TO ''?/oradata/test/redo02.log'' ";
-
-
# Do a SET UNTIL to prevent recovery of the online logs
-
SET UNTIL SCN 123456;
-
# restore the database and switch the data file names
-
RESTORE DATABASE;
-
SWITCH DATAFILE ALL;
-
SWITCH TEMPFILE ALL;
-
# recover the database
-
RECOVER DATABASE;
-
}
-
-
-
------------------------------------------------- 資料庫未掛掉的情況下的恢復
-
-
-
[root@orcltest ~]# ps -ef|grep ora_lgwr_
-
.oracle 32173 1 0 06:31 ? 00:00:00 ora_lgwr_oratest
-
root 33247 32901 0 10:19 pts/0 00:00:00 grep ora_lgwr_
-
[root@orcltest ~]# cd /proc/32173/fd
-
[root@orcltest fd]# ll | grep deleted
-
lrwx------ 1 root root 64 May 5 15:10 266 -> /u02/app/oracle/oradata/oratest/temp01.dbf (deleted)
-
cp 266 /u02/app/oracle/oradata/oratest/temp01.dbf
-
-
-
-
------------------------------------------------------------------------------------------- asm <=> os
-
----所有檔案列表 資料檔案
-
set line 9999 pagesize 9999
-
col FILE_NAME format a60
-
select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile
-
union all
-
select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile
-
union all
-
select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile
-
union all
-
select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile
-
;
-
-
-
-
-
set line 9999 pagesize 9999
-
col FILE_NAME format a50
-
select file#,name FILE_NAME,status,enabled from v$datafile;
-
--select file#,name FILE_NAME from v$dbfile;
-
-
col FILE_NAME format a50
-
select FILE_NAME,FILE_ID,a.TABLESPACE_NAME, b.status ts_status ,BYTES from dba_data_files a,dba_tablespaces b where a.TABLESPACE_NAME=b.TABLESPACE_NAME;
-
-
-
-------------------------------------------os--->>asm
-
-- answer 1 set newname 好
-
run{
-
crosscheck backup;
-
sql 'alter tablespace testdg offline immediate';
-
set newname for datafile 14 to'+DATA';
-
restore tablespace testdg;
-
switch datafile 14;
-
recover tablespace testdg;
-
sql 'alter tablespace testdg online';
-
}
-
-
-
-- answer 2 convert 好
-
rman下:
-
convert datafile '/home/oracle/testdg.dbf' format '+DATA';
-
sql 下:
-
alter tablespace testdg offline ;
-
alter tablespace testdg rename datafile '/home/oracle/testdg.dbf' to '+DATA/orclasm/datafile/testdg.284.868895277';
-
recover datafile 14;
-
alter tablespace testdg online;
-
-
-
-
-
-- answer 3 dbms_file_transfer
-
create directory asmsrc as'+DATA/orclasm/datafile/';
-
create directory osdesc as '/home/oracle/';
-
-
alter tablespace testdg offline;
-
-
exec dbms_file_transfer.copy_file('osdesc','testdg.dbf','ASMSRC','testdg.dbf');
-
-
alter database rename file'/home/oracle/testdg.dbf' to '+DATA/orclasm/datafile/testdg.dbf';
-
alter tablespace testdg online ;
-
-
-
-
-
-
-- answer 4 backup as copy
-
run{
-
shutdown immediate;
-
startup mount;
-
backup as copy datafile 14 format '+DATA';
-
}
-
SWITCH TABLESPACE testdg TO COPY;
-
alter database open;
-
-
-
-
-- answer 5 cp
-
-
alter tablespace testdg offline;
-
[root@rhel6_lhr ~]# cp /home/oracle/testdg.dbf /home/grid/testdg.dbf
-
[root@rhel6_lhr ~]# chown grid:oinstall /home/grid/testdg.dbf
-
[root@rhel6_lhr ~]# su - grid
-
ASMCMD> cp /home/grid/testdg.dbf +DATA/orclasm/datafile/testdg.dbf
-
copying /home/grid/testdg.dbf -> +DATA/orclasm/datafile/testdg.dbf
-
ASMCMD>
-
alter database rename file'/home/oracle/testdg.dbf' to '+DATA/orclasm/datafile/testdg.dbf';
-
alter tablespace testdg online ;
-
-
-
-
---生成standby controlfile
-
rman:backup device type disk format '/arch/standby_new_lhr_%U.ctl' current controlfile for standby;
-
sql:alter database create standby controlfile as '/arch/standby_new_lhr_contol.ctl'
-
-
-
-
--------------------控制檔案轉換
-
RMAN> catalog controlfilecopy '/home/oracle/rman_back/ctl_orastrac.ctl_bk';
-
-
cataloged control file copy
-
control file copy file name=/home/oracle/rman_back/ctl_orastrac.ctl_bk RECID=7 STAMP=881248289
-
-
RMAN> backup as copy controlfilecopy '/home/oracle/rman_back/ctl_orastrac.ctl_bk' format '+DATA';
-
-
Starting backup at 01-JUN-2015 15:11:44
-
using channel ORA_DISK_1
-
channel ORA_DISK_1: starting datafile copy
-
input control file copy name=/home/oracle/rman_back/ctl_orastrac.ctl_bk
-
output file name=+DATA/orastrac/controlfile/backup.331.881248305 tag=TAG20150601T111610 RECID=8 STAMP=881248307
-
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
-
Finished backup at 01-JUN-2015 15:11:51
-
-
RMAN-06497: WARNING: control file is not current, control file AUTOBACKUP skipped
-
-
RMAN> restore controlfile to '+DATA' FROM '+DATA/orastrac/controlfile/backup.331.881248305';
-
-
Starting restore at 01-JUN-2015 15:12:49
-
using channel ORA_DISK_1
-
-
channel ORA_DISK_1: copied control file copy
-
Finished restore at 01-JUN-2015 15:12:50
-
-
-
-
------------------------------------------------ asm --->> os
-
-
-
-- answer 1 好
-
rman下:
-
convert datafile '+DATA/orclasm/datafile/testdg.277.868887219' format '/home/oracle/testdg.dbf';
-
sql 下:
-
alter tablespace testdg offline ;
-
alter tablespace testdg rename datafile '+DATA/orclasm/datafile/testdg.277.868887219' to '/home/oracle/testdg.dbf';
-
recover datafile 14;
-
alter tablespace testdg online;
-
-
-
-- answer 2 dbms_file_transfer
-
create directory asmsrc as'+DATA/orclasm/datafile/';
-
create directory osdesc as '/home/oracle/';
-
-
alter tablespace testdg offline;
-
-
exec dbms_file_transfer.copy_file('ASMSRC','testdg.282.868891371','osdesc','testdg.dbf');
-
-
alter database rename file'+DATA/orclasm/datafile/testdg.282.868891371' to '/home/oracle/testdg.dbf';
-
alter tablespace testdg online ;
-
-
-
-
-- answer 3 mount
-
run{
-
shutdown immediate;
-
startup mount;
-
set newname for datafile 14 to '/home/oracle/testdg.dbf';
-
restore datafile 14;
-
switch datafile 14;
-
recover datafile 14;
-
alter database open;
-
}
-
-
-
-
-- answer 4 mount
-
run{
-
shutdown immediate;
-
startup mount;
-
backup as copy datafile 14 format '/home/oracle/testdg.dbf';
-
}
-
-
switch tablespace testdg to copy;
-
alter database open;
-
-
-
-
-- answer 5 cp
-
-
alter tablespace testdg offline;
-
[root@rhel6_lhr ~]# su - grid
-
ASMCMD> cp +DATA/orclasm/datafile/testdg.dbf /home/grid/testdg.dbf
-
copying +DATA/orclasm/datafile/testdg.dbf -> /home/grid/testdg.dbf
-
ASMCMD>
-
-
[root@rhel6_lhr ~]# cp /home/grid/testdg.dbf /home/oracle/testdg.dbf
-
[root@rhel6_lhr ~]# chown oracle:oinstall /home/oracle/testdg.dbf
-
[root@rhel6_lhr ~]#
-
-
-
alter database rename file'+DATA/orclasm/datafile/testdg.dbf' to '/home/oracle/testdg.dbf';
-
alter tablespace testdg online ;
-
-
-
-
-
create bigfile tablespace ts_dbm datafile '/home/oracle/ts_dbm01.dbf' size 10m autoextend on next 10M ;
-
alter user xxx default tablespace bbb;
-
-
-
-
------ smallfile tablespaces
-
單個資料檔案的大小由資料庫block_size的尺寸決定,例如:
-
block_size =8K 對應單個資料檔案最大為 32G
-
block_size =16K 對應單個據檔案最大為 64G
-
block_size =32K 對應單個據檔案最大為 128G
-
-
-
------Bigfile Tablespaces
-
block_size =8K 對應單個資料檔案最大為 32T
-
block_size =16K 對應單個據檔案最大為 64T
-
block_size =32K 對應單個據檔案最大為 128T
-
-
-
-
-
----如果刪除表空間之前刪除了表空間檔案,解決辦法:
-
-
如果資料庫已經啟動,則需要先執行下面這行:
-
SQL> shutdown abort
-
SQL> startup mount
-
SQL> alter database datafile 'filename' offline drop;
-
SQL> alter database open;
-
SQL> drop tablespace tablespace_name including contents;
-
-
-
alter database datafile '/u02/oracle/oradata/user01.dbf' offline drop;
-
alter tablespace test drop datafile '+DATA/orclasm/datafile/test.274.907173619';
-
-
-
-
------表空間預設型別
-
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like '%TBS%';
-
SQL> alter database set default bigfile tablespace;
-
--改回為預設值
-
SQL> alter database set default smallfile tablespace;
-
-
---控制檔案
-
--alter system set control_files='/u01/app/oracle/oradata/control01.ctl', '/u01/app/oracle/oradata/control02.ctl','/u01/app/oracle/oradata/control03.ctl' scope=spfile;
-
alter database backup controlfile to trace as '/home/oracle/oracle_bk/coolbak/ctl.sql';
-
select * from v$controlfile_record_section;
-
--轉儲控制檔案
-
alter system set events 'immediate trace name controlf level 12';
-
---檔案路徑
-
SELECT d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
-
p.spid || '.trc' trace_file_name
-
FROM (SELECT p.spid
-
FROM v$mystat m, v$session s, v$process p
-
WHERE m.statistic# = '1'
-
AND s.sid = m.sid
-
AND p.addr = s.paddr) p,
-
(SELECT t.instance
-
FROM v$thread t, v$parameter v
-
WHERE v.name = 'thread'
-
AND (v.value = '0' OR to_char(t.thread#) = v.VALUE)) i,
-
(SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
-
-
-
oradebug setmypid
-
SELECT a.SID,
-
b.SERIAL# ,
-
c.SPID ospid,
-
c.pid orapid
-
FROM v$mystat a,
-
v$session b ,
-
v$process c
-
WHERE a.SID = b.SID
-
and b.PADDR=c.ADDR
-
AND rownum = 1;
-
oradebug dump controlf 12;
-
-
-
16:09:17 SQL> oradebug setmypid
-
已處理的語句
-
16:09:55 SQL> oradebug tracefile_name
-
/u01/app/oracle/diag/rdbms/orclasm/orclasm/trace/orclasm_ora_21437.trc
-
-
-
oradebug event 1555 trace name errorstack level 3
-
-
-
-
-
-
-
熱備:
-
alter database backup controlfile to '<dir>'; --熱備份控制檔案 alter database backup controlfile to '/home/oracle/ora_bk/control.bk';
-
alter database backup controlfile to trace as '<dir>' ;--得到建立控制檔案的指令碼
-
RMAN:
-
backup current controlfile format '/home/oracle/oracle_bk/orclasm/ctl_%d_%T_%s_%p.bak';
-
backup database include current controlfile;
-
-- 或者設定RMAN 為自動備份
-
RMAN > configure controlfile autobackup on;
-
-
-
----預設false 忽略一致性檢察 隱含引數 隱藏
-
SELECT * FROM gv$parameter a WHERE a.NAME like '\_%' escape '\' ;
-
SELECT * FROM gv$parameter a WHERE a.NAME like '=_%' escape '=' ;
-
-
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
-
alter system set "_allow_resetlogs_corruption"=false scope=spfile; --預設
-
-
alter system reset "_allow_resetlogs_corruption" scope=spfile sid='*';
-
-
-
SQL> show parameter _allow_resetlogs_corruption
-
-
NAME TYPE VALUE
-
------------------------------------ ----------- ------------------------------
-
_allow_resetlogs_corruption boolean TRUE
-
-
set pagesize 9999
-
set line 9999
-
col NAME format a40
-
col KSPPDESC format a50
-
col KSPPSTVL format a20
-
SELECT a.INDX,
-
a.KSPPINM NAME,
-
a.KSPPDESC,
-
b.KSPPSTVL
-
FROM x$ksppi a,
-
x$ksppcv b
-
WHERE a.INDX = b.INDX
-
and lower(a.KSPPINM) like lower('%¶meter%');
-
-
-
-
-
-
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
-
recover database using backup controlfile until cancel;
-
alter database open resetlogs;
-
startup force
-
alter database open resetlogs;
-
alter system set "_allow_resetlogs_corruption"=false scope=spfile;
-
alter system reset "_allow_resetlogs_corruption" scope=spfile sid='*';
-
-
-
-
-
----------------incarnation
-
RMAN> list incarnation of database;
-
RMAN> reset database to incarnation 8;
-
-
SELECT * FROM V$DATABASE_INCARNATION;
-
-
-
alter system set log_archive_dest_1='LOCATION=/home/oracle' scope=spfile;
-
-
-
-
---------- 重建控制檔案
-
CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS DATAFILE '/u01/app/oracle/oradata/orcltest/system01.dbf';
-
-
-
STARTUP NOMOUNT
-
CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS ARCHIVELOG
-
MAXLOGFILES 16
-
MAXLOGMEMBERS 3
-
MAXDATAFILES 100
-
MAXINSTANCES 8
-
MAXLOGHISTORY 292
-
LOGFILE
-
GROUP 1 '/u02/app/oracle/oradata/orcltest/redo01.log' SIZE 50M BLOCKSIZE 512,
-
GROUP 2 '/u02/app/oracle/oradata/orcltest/redo02.log' SIZE 50M BLOCKSIZE 512,
-
GROUP 3 '/u02/app/oracle/oradata/orcltest/redo03.log' SIZE 50M BLOCKSIZE 512
-
-- STANDBY LOGFILE
-
DATAFILE
-
'/u02/app/oracle/oradata/orcltest/system01.dbf',
-
'/u02/app/oracle/oradata/orcltest/sysaux01.dbf',
-
'/u02/app/oracle/oradata/orcltest/undotbs01.dbf',
-
'/u02/app/oracle/oradata/orcltest/users01.dbf',
-
'/u02/app/oracle/oradata/orcltest/example01.dbf'
-
CHARACTER SET ZHS16GBK
-
;
-
-
-
-
select THREAD#, SEQUENCE#,FIRST_TIME from v$archived_log d where (( THREAD#=2 and SEQUENCE# between 10050 and 10060) or ( THREAD#=1 and SEQUENCE# between 9720 and 9725)) and d.DELETED!='YES' ORDER BY THREAD#, D.RECID;
-
run {
-
allocate channel c1 type disk;
-
allocate channel c2 type disk;
-
startup force mount;
-
sql 'alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:mi:ss"';
-
set until time = "to_date('2016-07-28 21:04:50','YYYY-MM-DD HH24:mi:ss')";
-
restore database;
-
recover database;
-
release channel c1;
-
release channel c2;
-
}
-
-
catalog start with '/u03/backup/' noprompt;
-
-
-
-
--restore Controlfile
-
DECLARE
-
devtype varchar2(256);
-
done boolean;
-
BEGIN
-
devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
-
sys.dbms_backup_restore.restoreSetDatafile;
-
sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/tmp/2015_04_30/control01.ctl');
-
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_04_30/o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp', params=>null);
-
sys.dbms_backup_restore.deviceDeallocate;
-
END;
-
/
-
-
-
-
-
--restore datafile
-
DECLARE
-
devtype varchar2(256);
-
done boolean;
-
BEGIN
-
devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
-
sys.dbms_backup_restore.restoreSetDatafile;
-
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/u02/app/oracle/oradata/orcltest/system01.dbf');
-
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/u02/app/oracle/oradata/orcltest/sysaux01.dbf');
-
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/u02/app/oracle/oradata/orcltest/undotbs01.dbf');
-
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'/u02/app/oracle/oradata/orcltest/users01.dbf');
-
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'/u02/app/oracle/oradata/orcltest/example01.dbf');
-
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp', params=>null);
-
sys.dbms_backup_restore.deviceDeallocate;
-
END;
-
/
-
-
注意:
-
在multisection backup 的情況下,我們需要考慮所有的backuppiece(也就是所有的section),使用initmsr函式來restore datafile
-
-
DECLARE
-
devtype varchar2(256);
-
done boolean;
-
BEGIN
-
devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
-
dbms_backup_restore.RestoreSetDatafile;
-
dbms_backup_restore.initmsr(1,'/ud1001/PROD/oradata/system01-test.dbf');
-
dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/ud1001/PROD/oradata/system01-test.dbf');
-
dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/backup/Oracle-DB-8-7-2014/db_L0_PROD_e6pf7too_1_1.rman', params => null);
-
dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/backup/Oracle-DB-8-7-2014/db_L0_PROD_e6pf7too_2_1.rman', params => null);
-
END;
-
/
-
-
-
-
--restore archived redolog
-
DECLARE
-
devtype varchar2(256);
-
done boolean;
-
BEGIN
-
devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'FUN');
-
dbms_backup_restore.RestoreSetArchivedLog(destination=>'D:\ORACLE_BASE\achive\');
-
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>1);
-
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>2);
-
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>3);
-
dbms_backup_restore.RestoreBackupPiece(done => done,handle => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_LOG_BCK0DH1JGND_1_1', params => null);
-
dbms_backup_restore.DeviceDeallocate;
-
END;
-
/
-
-
-
--清除控制檔案中關於v$archived_log的資訊
-
SQL> execute sys.dbms_backup_restore.resetCfileSection(11);
-
-
--再次查詢v$archived_log,資訊已經被清除
-
SQL> select dest_id,sequence#,name,blocks from v$archived_log;
-
-
---------------------------------------------------------------------------------------------------------------- 歸檔丟失
-
SQL> recover database ;
-
ORA-00279: change 1549336 generated at 01/15/2015 16:22:07 needed for thread 1
-
ORA-00289: suggestion :
-
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_22_865253186.dbf
-
ORA-00280: change 1549336 for thread 1 is in sequence #22
-
-
-
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-
cancel
-
Media recovery cancelled.
-
SQL> alter database open;
-
alter database open
-
*
-
ERROR at line 1:
-
ORA-01113: file 1 needs media recovery
-
ORA-01110: data file 1: '/u01/app/oracle/oradata/utf8test/system01.dbf'
-
-
-
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
-
-
System altered.
-
-
SQL> startup force;
-
ORACLE instance started.
-
-
Total System Global Area 1102344192 bytes
-
Fixed Size 2227584 bytes
-
Variable Size 738198144 bytes
-
Database Buffers 352321536 bytes
-
Redo Buffers 9596928 bytes
-
Database mounted.
-
ORA-01113: file 1 needs media recovery
-
ORA-01110: data file 1: '/u01/app/oracle/oradata/utf8test/system01.dbf'
-
-
---- recover database using backup controlfile;
-
SQL> recover database until cancel;
-
ORA-00279: change 1549336 generated at 01/15/2015 16:22:07 needed for thread 1
-
ORA-00289: suggestion :
-
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_22_865253186.dbf
-
ORA-00280: change 1549336 for thread 1 is in sequence #22
-
-
-
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-
cancel
-
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
-
ORA-01194: file 2 needs more recovery to be consistent
-
ORA-01110: data file 2: '/u01/app/oracle/oradata/utf8test/sysaux01.dbf'
-
-
-
ORA-01112: media recovery not started
-
-
-
SQL> alter database open resetlogs;
-
alter database open resetlogs
-
*
-
ERROR at line 1:
-
ORA-00603: ORACLE server session terminated by fatal error
-
ORA-00600: internal error code, arguments: [2662], [0], [1549349], [0],
-
[1550178], [12583040], [], [], [], [], [], []
-
ORA-00600: internal error code, arguments: [2662], [0], [1549348], [0],
-
[1550178], [12583040], [], [], [], [], [], []
-
ORA-01092: ORACLE instance terminated. Disconnection forced
-
ORA-00600: internal error code, arguments: [2662], [0], [1549346], [0],
-
[1550178], [12583040], [], [], [], [], [], []
-
Process ID: 7693
-
Session ID: 237 Serial number: 5
-
-
-
退出,重新登入
-
-
SQL> exit
-
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
-
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
[oracle@rhel6_lhr utf8test]$ sqlplus / as sysdba
-
-
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 16 10:13:53 2015
-
-
Copyright (c) 1982, 2011, Oracle. All rights reserved.
-
-
Connected to an idle instance.
-
-
SQL> startup
-
ORACLE instance started.
-
-
Total System Global Area 1102344192 bytes
-
Fixed Size 2227584 bytes
-
Variable Size 738198144 bytes
-
Database Buffers 352321536 bytes
-
Redo Buffers 9596928 bytes
-
Database mounted.
-
Database opened.
-
----------------------------------------------------------------------------------------------------------------
-
-
select * from v$fixed_view_definition a WHERE a.VIEW_NAME like 'X_$DIAG%' ;
-
SELECT * FROM V$FIXED_TABLE A WHERE A.NAME like 'X$DIAG%' ;
-
-
-
-
------- 十進位制轉十六進位制
-
select to_char(1985432,'xxxxxxxxxxxxxxx') FROM DUAL;
-
------- 十六進位制轉十進位制
-
select to_number('1e4b98','xxxxxxxxxxxxxxx') from dual;
-
-
-
-
-
--------------------------- exp和imp grant exp_full_database to lhr;
-
--EXP-00091的方法 select userenv('language') from dual; ---->>> NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
-
或加上: STATISTICS=NONE
-
-
------ query選項
-
exp \'/ AS SYSDBA\' tables=test_query_lhr file=/tmp/test_query_lhr_scott.dmp query=\" where owner=\'SCOTT\' \" log=/tmp/test_query_lhr_scott.log
-
[ZFZHLHRDB1:oracle]:/oracle>more /tmp/scottfile.par
-
query="where owner='SCOTT'"
-
[ZFZHLHRDB1:oracle]:/oracle> exp \'/ AS SYSDBA\' tables=test_query_lhr file=/tmp/test_query_lhr_scott_01.dmp parfile=/tmp/scottfile.par log=/tmp/test_query_lhr_scott_01.log
-
-
------ parfile選項
-
[ZFZHLHRDB1:oracle]:/oracle>more /tmp/scottfile.par
-
tables=scott.emp,scott.dept
-
exp \'/ AS SYSDBA\' file=/tmp/test_query_lhr_scott_01.dmp parfile=/tmp/scottfile.par log=/tmp/test_query_lhr_scott_01.log
-
-
-
-
-
strace exp n1/n1 tables=scott.emp file=a.dmp
-
-
-
exp cnydm/cnydm@DATAWDB_125 file=d:/oracle_bk/cnydm20150402.dmp log=d:/oracle_bk/cnydm20150402.log buffer=50000000 tables=PRD_CTGRY_D,DSCNT_TP_D,MKT_AND_PRD_CTGRY_D,MKT_CTGRY_D
-
imp cnydm/cnydm@DATAWDB_125 file=d:/oracle_bk/cnydm20150402.dmp log=d:/oracle_bk/imp_cnydm20150402.log buffer=50000000 full=y
-
-
-
-
exp system/lhr file=E:\expfull.dmp full=y log=E:\expfull.log
-
imp system/lhr file=E:\expfull.dmp full=y log=E:\impfull.log
-
-
exp lhr/lhr@orclasm tables=xb_log_lhr,xb_a,xb_b file=e:\e1.dmp log=E:\exp_table.log buffer=41943040
-
imp lhr/lhr@winxp tables=xb_log_lhr file=e:\e1.dmp log=E:\exp_table.log buffer=41943040
-
imp lhr/lhr@orclasm tables=(emp,dept) file=d:\e1.dmp log=E:\exp_table_.log buffer=41943040
-
-
-
-
exp system/lhr file=E:\expfull2.dmp log=E:\expfull2.log owner=(lhrexp,lhrimp)
-
imp system/lhr file=E:\expfull2.dmp full=y log=E:\expfull2.log
-
imp "sys as sysdba" file=testmv_full.dmp full=y buffer=41943040 feedback=10000 log=testmv_full.log
-
imp user2/pwd fromuser=user1 touser=user2 file=/tmp/imp_db_pipe1 feedback=10000 buffer=41943040 log=testmv_full.log
-
-
生產環境下,oracle 9I下sga大概8G,pga大概6g,需要匯入一個2.7g以上的大表到成產庫中,由於是同事著手運用imp工具的預設buffer=30K,用時大概一個小時還沒有結果,考慮到pga還是很大的,跟同事商議加上buffer=409600000設定buffer大概400M的,15分鐘內imp完成。當然運用impdp然後運用parallel=n效率當然更加理想了!
-
-
-
-------------------------------------------- 匯出ASH檢視的資料 ash資料
-
--- 方法1:ctas建表匯出 有的客戶不讓建表
-
CREATE TABLE ASH_TEMP_20161117 NOLOGGING AS
-
SELECT *
-
FROM DBA_HIST_ACTIVE_SESS_HISTORY D
-
WHERE D.SAMPLE_TIME BETWEEN
-
TO_DATE('2016-11-10 02:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
-
TO_DATE('2016-11-17 06:00:00', 'YYYY-MM-DD HH24:MI:SS')
-
;
-
-
exp \'/ AS SYSDBA\' tables=ASH_TEMP_20161117 file=/tmp/ASH_TEMP_20161117.dmp log=/tmp/ASH_TEMP_20161117.log buffer=41943040
-
imp lhr/lhr tables=ASH_TEMP_20161117 file=/tmp/ASH_TEMP_20161117.dmp log=/tmp/imp_ASH_TEMP_20161117.log buffer=41943040
-
-
-
--- 方法2:匯出基表的資料
-
---more /tmp/exp_ash_lhr_01.par
-
query="WHERE SAMPLE_TIME BETWEEN TO_DATE('2016-12-02 08:30:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2016-12-08 23:38:00', 'YYYY-MM-DD HH24:MI:SS')"
-
-
exp \'/ AS SYSDBA\' tables='WRH$_ACTIVE_SESSION_HISTORY' file=/tmp/exp_ash_lhr_01.dmp parfile=/tmp/exp_ash_lhr_01.par log=/tmp/exp_ash_lhr_01.log GRANTS=N CONSTRAINTS=N STATISTICS=NONE
-
exp \'/ AS SYSDBA\' tables='WRM$_SNAPSHOT','WRH$_EVENT_NAME','WRH$_SQLCOMMAND_NAME','WRH$_PLAN_OPERATION_NAME','WRH$_PLAN_OPTION_NAME','WRH$_TOPLEVELCALL_NAME' file=/tmp/exp_ash_lhr_02.dmp log=/tmp/exp_ash_lhr_02.log GRANTS=N CONSTRAINTS=N STATISTICS=NONE
-
-
imp lhr/lhr file=/tmp/exp_ash_lhr_01.dmp tables='WRH$_ACTIVE_SESSION_HISTORY' log=/tmp/imp_ash_lhr_01.log FROMUSER=SYS TOUSER=LHR
-
imp lhr/lhr file=/tmp/exp_ash_lhr_02.dmp tables='WRM$_SNAPSHOT','WRH$_EVENT_NAME','WRH$_SQLCOMMAND_NAME','WRH$_PLAN_OPERATION_NAME','WRH$_PLAN_OPTION_NAME','WRH$_TOPLEVELCALL_NAME' log=/tmp/imp_ash_lhr_02.log FROMUSER=SYS TOUSER=LHR
-
-
-
DROP TABLE LHR.WRH$_ACTIVE_SESSION_HISTORY PURGE;
-
DROP TABLE LHR.WRM$_SNAPSHOT PURGE;
-
DROP TABLE LHR.WRH$_EVENT_NAME PURGE;
-
DROP TABLE LHR.WRH$_SQLCOMMAND_NAME PURGE;
-
DROP TABLE LHR.WRH$_PLAN_OPERATION_NAME PURGE;
-
DROP TABLE LHR.WRH$_PLAN_OPTION_NAME PURGE;
-
DROP TABLE LHR.WRH$_TOPLEVELCALL_NAME PURGE;
-
-
create or replace view dh_ash_11g_lhr
-
(snap_id, dbid, instance_number, sample_id, sample_time, session_id, session_serial#, session_type, flags, user_id, sql_id, is_sqlid_current, sql_child_number, sql_opcode, sql_opname, force_matching_signature, top_level_sql_id, top_level_sql_opcode, sql_plan_hash_value, sql_plan_line_id, sql_plan_operation, sql_plan_options, sql_exec_id, sql_exec_start, plsql_entry_object_id, plsql_entry_subprogram_id, plsql_object_id, plsql_subprogram_id, qc_instance_id, qc_session_id, qc_session_serial#, px_flags, event, event_id, seq#, p1text, p1, p2text, p2, p3text, p3, wait_class, wait_class_id, wait_time, session_state, time_waited, blocking_session_status, blocking_session, blocking_session_serial#, blocking_inst_id, blocking_hangchain_info, current_obj#, current_file#, current_block#, current_row#, top_level_call#, top_level_call_name, consumer_group_id, xid, remote_instance#, time_model, in_connection_mgmt, in_parse, in_hard_parse, in_sql_execution, in_plsql_execution, in_plsql_rpc, in_plsql_compilation, in_java_execution, in_bind, in_cursor_close, in_sequence_load, capture_overhead, replay_overhead, is_captured, is_replayed, service_hash, program, module, action, client_id, machine, port, ecid, dbreplay_file_id, dbreplay_call_counter, tm_delta_time, tm_delta_cpu_time, tm_delta_db_time, delta_time, delta_read_io_requests, delta_write_io_requests, delta_read_io_bytes, delta_write_io_bytes, delta_interconnect_io_bytes, pga_allocated, temp_space_allocated)
-
as
-
select /* ASH/AWR meta attributes */
-
ash.snap_id, ash.dbid, ash.instance_number,
-
ash.sample_id, ash.sample_time,
-
/* Session/User attributes */
-
ash.session_id, ash.session_serial#,
-
decode(ash.session_type, 1,'FOREGROUND', 'BACKGROUND'),
-
ash.flags,
-
ash.user_id,
-
/* SQL attributes */
-
ash.sql_id,
-
decode(bitand(ash.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'),
-
ash.sql_child_number, ash.sql_opcode,
-
(select command_name from WRH$_SQLCOMMAND_NAME
-
where command_type = ash.sql_opcode
-
and dbid = ash.dbid) as sql_opname,
-
ash.force_matching_signature,
-
decode(ash.top_level_sql_id, NULL, ash.sql_id, ash.top_level_sql_id),
-
decode(ash.top_level_sql_id, NULL, ash.sql_opcode,
-
ash.top_level_sql_opcode),
-
/* SQL Plan/Execution attributes */
-
ash.sql_plan_hash_value,
-
decode(ash.sql_plan_line_id, 0, to_number(NULL), ash.sql_plan_line_id),
-
(select operation_name from WRH$_PLAN_OPERATION_NAME
-
where operation_id = ash.sql_plan_operation#
-
and dbid = ash.dbid) as sql_plan_operation,
-
(select option_name from WRH$_PLAN_OPTION_NAME
-
where option_id = ash.sql_plan_options#
-
and dbid = ash.dbid) as sql_plan_options,
-
decode(ash.sql_exec_id, 0, to_number(NULL), ash.sql_exec_id),
-
ash.sql_exec_start,
-
/* PL/SQL attributes */
-
decode(ash.plsql_entry_object_id,0,to_number(NULL),
-
ash.plsql_entry_object_id),
-
decode(ash.plsql_entry_object_id,0,to_number(NULL),
-
ash.plsql_entry_subprogram_id),
-
decode(ash.plsql_object_id,0,to_number(NULL),
-
ash.plsql_object_id),
-
decode(ash.plsql_object_id,0,to_number(NULL),
-
ash.plsql_subprogram_id),
-
/* PQ attributes */
-
decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_instance_id),
-
decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_id),
-
decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_serial#),
-
decode(ash.px_flags, 0, to_number(NULL), ash.px_flags),
-
/* Wait event attributes */
-
decode(ash.wait_time, 0, evt.event_name, NULL),
-
decode(ash.wait_time, 0, evt.event_id, NULL),
-
ash.seq#,
-
evt.parameter1, ash.p1,
-
evt.parameter2, ash.p2,
-
evt.parameter3, ash.p3,
-
decode(ash.wait_time, 0, evt.wait_class, NULL),
-
decode(ash.wait_time, 0, evt.wait_class_id, NULL),
-
ash.wait_time,
-
decode(ash.wait_time, 0, 'WAITING', 'ON CPU'),
-
ash.time_waited,
-
(case when ash.blocking_session = 4294967295
-
then 'UNKNOWN'
-
when ash.blocking_session = 4294967294
-
then 'GLOBAL'
-
when ash.blocking_session = 4294967293
-
then 'UNKNOWN'
-
when ash.blocking_session = 4294967292
-
then 'NO HOLDER'
-
when ash.blocking_session = 4294967291
-
then 'NOT IN WAIT'
-
else 'VALID'
-
end),
-
(case when ash.blocking_session between 4294967291 and 4294967295
-
then to_number(NULL)
-
else ash.blocking_session
-
end),
-
(case when ash.blocking_session between 4294967291 and 4294967295
-
then to_number(NULL)
-
else ash.blocking_session_serial#
-
end),
-
(case when ash.blocking_session between 4294967291 and 4294967295
-
then to_number(NULL)
-
else ash.blocking_inst_id
-
end),
-
(case when ash.blocking_session between 4294967291 and 4294967295
-
then NULL
-
else decode(bitand(ash.flags, power(2, 3)), NULL, 'N',
-
0, 'N', 'Y')
-
end),
-
/* Session's working context */
-
ash.current_obj#, ash.current_file#, ash.current_block#,
-
ash.current_row#, ash.top_level_call#,
-
(select top_level_call_name from WRH$_TOPLEVELCALL_NAME
-
where top_level_call# = ash.top_level_call#
-
and dbid = ash.dbid) as top_level_call_name,
-
decode(ash.consumer_group_id, 0, to_number(NULL),
-
ash.consumer_group_id),
-
ash.xid,
-
decode(ash.remote_instance#, 0, to_number(NULL), ash.remote_instance#),
-
ash.time_model,
-
decode(bitand(ash.time_model,power(2, 3)),0,'N','Y')
-
as in_connection_mgmt,
-
decode(bitand(ash.time_model,power(2, 4)),0,'N','Y')as in_parse,
-
decode(bitand(ash.time_model,power(2, 7)),0,'N','Y')as in_hard_parse,
-
decode(bitand(ash.time_model,power(2,10)),0,'N','Y')as in_sql_execution,
-
decode(bitand(ash.time_model,power(2,11)),0,'N','Y')
-
as in_plsql_execution,
-
decode(bitand(ash.time_model,power(2,12)),0,'N','Y')as in_plsql_rpc,
-
decode(bitand(ash.time_model,power(2,13)),0,'N','Y')
-
as in_plsql_compilation,
-
decode(bitand(ash.time_model,power(2,14)),0,'N','Y')
-
as in_java_execution,
-
decode(bitand(ash.time_model,power(2,15)),0,'N','Y')as in_bind,
-
decode(bitand(ash.time_model,power(2,16)),0,'N','Y')as in_cursor_close,
-
decode(bitand(ash.time_model,power(2,17)),0,'N','Y')as in_sequence_load,
-
decode(bitand(ash.flags,power(2,5)),NULL,'N',0,'N','Y')
-
as capture_overhead,
-
decode(bitand(ash.flags,power(2,6)), NULL,'N',0,'N','Y' )
-
as replay_overhead,
-
decode(bitand(ash.flags,power(2,0)),NULL,'N',0,'N','Y') as is_captured,
-
decode(bitand(ash.flags,power(2,2)), NULL,'N',0,'N','Y' )as is_replayed,
-
/* Application attributes */
-
ash.service_hash, ash.program,
-
ash.module module,
-
ash.action action,
-
ash.client_id,
-
ash.machine, ash.port, ash.ecid,
-
/* DB Replay info */
-
ash.dbreplay_file_id, ash.dbreplay_call_counter,
-
/* stash columns */
-
ash.tm_delta_time,
-
ash.tm_delta_cpu_time,
-
ash.tm_delta_db_time,
-
ash.delta_time,
-
ash.delta_read_io_requests,
-
ash.delta_write_io_requests,
-
ash.delta_read_io_bytes,
-
ash.delta_write_io_bytes,
-
ash.delta_interconnect_io_bytes,
-
ash.pga_allocated,
-
ash.temp_space_allocated
-
from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY ash, WRH$_EVENT_NAME evt
-
where ash.snap_id = sn.snap_id(+)
-
and ash.dbid = sn.dbid(+)
-
and ash.instance_number = sn.instance_number(+)
-
and ash.dbid = evt.dbid
-
and ash.event_id = evt.event_id;
-
-
-
----以下資料不能匯出
-
SELECT * FROM sys.Ku_Noexp_View d WHERE d.name LIKE '%WRH%' ;
-
SELECT * FROM DBA_OBJECTS d WHERE d.ORACLE_MAINTAINED='Y' AND D.object_name LIKE 'WR%';
-
-
-
-
-
-------------------預設使用者
-
-
SELECT d.username,d.default_tablespace,d.account_status, 'create user '|| d.username|| ' identified by '|| d.username ||' default tablespace '||d.default_tablespace||';' FROM dba_users d WHERE d.username not in ('ANONYMOUS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DBSNMP','DIP','DMSYS','DVSYS','EXFSYS','FLOWS_FILES','HR','IX','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','OE','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PM','REMOTE_SCHEDULER_AGENT','SCOTT','SH','SI_INFORMATN_SCHEMA','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','XS$NULL','CSMIG');
-
-
-
-
-
-------------------------------------expdp和impdp 資料泵
-
`date +%Y%m%d`
-
%date:~0,4%%date:~5,2%%date:~8,2%
-
-
set CurDate=%date:~0,4%%date:~5,2%%date:~8,2%
-
set hh=%time:~0,2%
-
if /i %hh% LSS 10 ( set hh=0%time:~1,1%)
-
set ms=%time:~3,2%%time:~6,2%
-
set my_date=%CurDate%%hh%%ms%
-
-
grant read,write on directory DATA_PUMP_DIR to LHR;
-
-
windows下用:expdp \"/ AS SYSDBA\"
-
-
-
-------------匯出到服務端
-
expdp scott/tiger@orclasm directory=DATA_PUMP_DIR TABLES=EMP,DEPT dumpfile=expdp_by_lhr_`date +%Y%m%d`.dmp LOGFILE=expdp_by_lhr_`date +%Y%m%d`.log
-
expdp scott/tiger@orclasm directory=DATA_PUMP_DIR TABLES=EMP,DEPT dumpfile=expdp_by_lhr_%date:~0,4%%date:~5,2%%date:~8,2%.dmp LOGFILE=expdp_by_lhr_%date:~0,4%%date:~5,2%%date:~8,2%.log
-
-
-
--表級別
-
expdp lhr/lhr@orclasm directory=DATA_PUMP_DIR dumpfile=exptable.dmp TABLES=TEST_TSPITR2,TEST_TSPITR3 LOGFILE=expdp_table.log
-
expdp scott/tiger@orclasm directory=DATA_PUMP_DIR dumpfile=exptable.dmp TABLES=EMP,DEPT LOGFILE=expdp_table.log
-
expdp system/lhr@orclasm DIRECTORY=DATA_PUMP_DIR DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\"IN ('EMP', 'DEPT')\"
-
impdp lhr/lhr@orclxp directory=DATA_PUMP_DIR dumpfile=exptable.dmp LOGFILE=impdp_table.log
-
-
--schema級別
-
expdp lhr/lhr@orclasm directory=DATA_PUMP_DIR dumpfile=HR.dmp SCHEMAS=HR,SCOTT,TESTUSER LOGFILE=HR.log
-
expdp system/oracle@orcl DIRECTORY=DATA_DUMP_DIR DUMPFILE=test_20140324.DMP SCHEMAS=test logfile=test_expdp_20111014.log status=10 parallel=4 CONTENT=ALL COMPRESSION=ALL
-
impdp lhr/lhr@orclasm directory=DATA_PUMP_DIR dumpfile=HR.dmp SCHEMAS=HR,SCOTT,TESTUSER parallel=4 LOGFILE=HR_20151125.log
-
-
-
-
-
--整個資料庫
-
expdp SYSTEM/ORACLE@ORCL DIRECTORY=DATA_DUMP_DIR DUMPFILE=TEST_20140324.DMP SCHEMAS=TEST LOGFILE=TEST_EXPDP_20111014.LOG STATUS=10 PARALLEL=1 CONTENT=ALL FLASHBACK_SCN=18341888 COMPRESSION=ALL
-
expdp \'/ AS SYSDBA\' DIRECTORY=DATA_DUMP_DIR FULL=Y DUMPFILE=FULLEXP.DMP LOGFILE=FULLEXP.LOG PARALLEL=2
-
impdp \'/ AS SYSDBA\' DIRECTORY=DATA_DUMP_DIR FULL=Y DUMPFILE=FULLEXP.DMP LOGFILE=FULLIMP.LOG PARALLEL=2 TABLE_EXISTS_ACTION=REPLACE EXCLUDE=STATISTICS,SCHEMA,TABLESPACE,ROLE,DIRECTORY,CONTEXT,PROFILE
-
impdp LHR/LHR@ORCLASM DIRECTORY=DATA_PUMP_DIR DUMPFILE=HR.DMP SCHEMAS=HR,SCOTT,TESTUSER PARALLEL=4 LOGFILE=HR_20151125.LOG
-
-
-
expdp SYSTEM/LHR DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_FULL_20150417.DMP FULL=Y LOGFILE=EXPDP_FULL_20150417.LOG EXCLUDE=STATISTICS
-
impdp SYSTEM/LHR DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_FULL_20150417.DMP FULL=Y LOGFILE=IMPDP_FULL_20150417.LOG PARALLEL=4 EXCLUDE=STATISTICS:"IN('')"
-
-
-
ORACLE_SID=ORA1024G
-
impdp \"/ AS SYSDBA\" DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_FULL_20150417.DMP FULL=Y LOGFILE=IMPDP_FULL_20150417.LOG JOB_NAME=IMPDP_LHR EXCLUDE=SCHEMA,TABLESPACE,ROLE,DIRECTORY, CONTEXT,PROFILE PARALLEL=2 TABLE_EXISTS_ACTION=REPLACE
-
impdp \"/ AS SYSDBA\" DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_FULL_20150417.DMP FULL=Y LOGFILE=IMPDP_FULL_20150417.LOG JOB_NAME=IMPDP_LHR EXCLUDE=TABLESPACE,ROLE,DIRECTORY, CONTEXT,PROFILE,USER,SCHEMA:"\=\'SYS'",SCHEMA:"\=\'IX'" PARALLEL=2 TABLE_EXISTS_ACTION=REPLACE
-
-
-
-
------ query選項
-
[ZFZHLHRDB1:oracle]:/oracle>more /tmp/scottfile.par
-
query=SCOTT.EMP:"WHERE DEPTNO=20",SCOTT.DEPT:"WHERE DNAME='SALES'"
-
[ZFZHLHRDB1:oracle]:/oracle> expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_query_lhr_scott_02.dmp parfile=/tmp/scottfile.par log=test_query_lhr_scott_02.log
-
-
----- include
-
expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_include_lhr_scott_03.dmp logfile=test_include_lhr_scott_03.log job_name=my_job_lhr include=procedure,function,sequence:"like '%TEST%'"
-
-
-
include=procedure,function,sequence:"like '%TEST%'"
-
-
include=procedure
-
include=function
-
include=sequence:"like '%TEST%'"
-
expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_include_lhr_scott_05.dmp logfile=test_include_lhr_scott_05.log job_name=my_job_lhr parfile=/tmp/parfile.par
-
-
expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile='expdp_by_lhr_%date:~0,4%%date:~5,2%%date:~8,2%.dmp' LOGFILE='expdp_by_lhr_%date:~0,4%%date:~5,2%%date:~8,2%.log' job_name=my_job_lhr parfile=/tmp/parfile.par
-
-
-------- trace
-
expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_query_lhr_scott_02.dmp parfile=/tmp/scottfile.par log=test_query_lhr_scott_02.log trace=4a0300
-
-
-
-
-
---------匯出到本地
-
expdp lhr/lhr@orclxp directory=DATA_PUMP_DIR dumpfile=exptable.dmp TABLES=xb_log_lhr network_link=dblk_orclasm LOGFILE=expdp_table.log
-
impdp lhr/lhr@orclxp directory=DATA_PUMP_DIR dumpfile=exptable.dmp LOGFILE=impdp_table.log
-
-
-
---- 直接匯入 不生成檔案
-
impdp lhr/lhr@orclxp network_link=dblk_orclasm directory=DATA_PUMP_DIR TABLES=xb_log_lhr PARALLEL=2 LOGFILE=impdp_table.log
-
-
-
-
---- 生成ddl語句 不會匯入資料
-
--expdp \'/ AS SYSDBA\' tables=lhr.exptest directory=DATA_PUMP_DIR dumpfile=exptest.dmp logfile=exp_exptest.dmp EXCLUDE=STATISTICS
-
--expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=lhrsql20161215.dmp logfile=lhrsql20161215.log content=metadata_only schemas=SCOTT EXCLUDE=STATISTICS
-
impdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=lhrsql20161215.dmp logfile=imp_exptest.log sqlfile=exptest.sql
-
-
-
exp \'/ AS SYSDBA\' tables=scott.emp file=/tmp/exp_ddl_lhr_01.dmp log=/tmp/exp_table.log buffer=41943040 rows=n compress=n
-
imp \'/ AS SYSDBA\' file=/tmp/exp_ddl_lhr_01.dmp show=y log=/tmp/get_ddl.sql buffer=20480000 full=y
-
-
set pagesize 0
-
set trimspool ON
-
SET linesize 10000
-
set long 90000
-
set feedback OFF
-
set feed off;
-
set echo off
-
spool schema_scott.sql
-
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME,U.owner)
-
FROM DBA_OBJECTS U
-
WHERE U.OBJECT_TYPE IN ('TABLE', 'INDEX', 'PROCEDURE', 'FUNCTION','PACKAGE','TRIGGER')
-
AND U.owner='SCOTT';
-
spool off;
-
-
-
----只匯出表結構
-
expdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=lhrsql20150515.log content=metadata_only schemas=TEST,SQCHECK,DWUSER
-
impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log sqlfile=lhrsql20150515.sql
-
impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=lhrsql20150515_imp.log
-
impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log TRANSFORM=storage:n TRANSFORM=SEGMENT_CREATION:n
-
impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log sqlfile=lhrsql20150515.sql TRANSFORM=segment_attributes:n
-
--transform=segment_attributes|storage|SEGMENT_CREATION|oid|pctspace:Y/N:object_type
-
-
-
-
----修改物件schema和tablespace
-
impdp test/test directory=exp_dump dumpfile=test.dmp logfile=test.log remap_schema=test1:test2 remap_tablespace=TBS_DAT_1:TBS_DAT_2,TBS_IDX_1:TBS_IDX_2
-
impdp test/test directory=exp_dump dumpfile=test.dmp logfile=test.log remap_schema=test1:test2 remap_tablespace=TBS_DAT_1:TBS_DAT_2 remap_tablespace=TBS_IDX_1:TBS_IDX_2
-
-
-
-
-
----顯示時間
-
expdp SCOTT/tiger@orclasm DIRECTORY=DATA_PUMP_DIR DUMPFILE=SCOTT%U.dmp SCHEMAS=SCOTT COMPRESSION=all metrics=y
-
-
--dmp檔案重用 reuse_dumpfiles=y
-
expdp \''sys/"l@h\r/0"'@LHRDB as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=dmp_SCOTT.dmp SCHEMAS=SCOTT reuse_dumpfiles=y
-
-
-
#scp -r root@10.0.24.103:/home2/backup/ /home/mover00/shadow_bak/sites/
-
拷貝遠端(10.0.24.103)的/home2/backup/ 到本地的 /home/mover00/shadow_bak/sites/
-
-
#scp -r /home2/backup/ root@10.0.24.99:/home/mover00/shadow_bak/sites/
-
拷貝本地的/home2/backup/ 到遠端(10.0.24.99)的 /home/mover00/shadow_bak/sites/
-
-
-
-
-
set line 9999
-
col owner_name for a10
-
col job_name for a25
-
col operation for a10
-
col job_mode for a10
-
col state for a15
-
col job_mode for a10
-
col state for a15
-
col osuser for a10
-
col "degree|attached|datapump" for a25
-
col session_info for a20
-
SELECT s.inst_id,
-
dj.owner_name,
-
dj.job_name,
-
dj.operation,
-
dj.job_mode,
-
dj.state,
-
dj.degree || ',' || dj.attached_sessions || ',' ||
-
dj.datapump_sessions "degree|attached|datapump",
-
ds.session_type,
-
s.osuser,
-
(SELECT s.SID || ',' || s.SERIAL# || ',' || p.SPID
-
FROM gv$process p
-
where s.paddr = p.addr
-
AND s.inst_id = p.inst_id) session_info
-
FROM DBA_DATAPUMP_JOBS dj --gv$datapump_job
-
full outer join dba_datapump_sessions ds --gv$datapump_session
-
on (dj.job_name = ds.job_name and dj.owner_name = ds.owner_name)
-
left outer join gv$session s
-
on (s.saddr = ds.saddr)
-
ORDER BY dj.owner_name, dj.job_name;
-
-
-
-
-
select * from GV$DATAPUMP_SESSION;
-
select * from GV$datapump_jobs;
-
select * From dba_datapump_jobs;
-
-
-
impdp \"/ as sysdba\" attach=IMPDP_LHR
-
-
-
------------- parfile
-
[root@rhel6_lhr dpdump]# more par.f
-
DUMPFILE=EXPDAT.DMP
-
DIRECTORY=DATA_PUMP_DIR
-
TRANSPORT_DATAFILES=
-
/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.DBF,
-
/u01/app/oracle/admin/orclasm/dpdump/APP2TBS.DBF,
-
/u01/app/oracle/admin/orclasm/dpdump/IDXTBS.DBF
-
LOGFILE=tts_import.log
-
[root@rhel6_lhr dpdump]#
-
-
[oracle@rhel6 ~]$ impdp system/lhr parfile='/u01/app/oracle/admin/orclasm/dpdump/par.f'
-
-
-
-
-
----檢視使用者的目錄許可權
-
column grantee format a10
-
column grantor format a10
-
column dir_name format a20
-
column dir_path format a50
-
column privilege format a10
-
-
break on dir_name
-
select
-
d.directory_name dir_name,
-
d.directory_path dir_path,
-
p.privilege,
-
p.grantee,
-
p.grantor
-
from
-
dba_tab_privs p,
-
dba_directories d
-
where
-
p.table_name = d.directory_name and
-
p.grantee = upper('&user')
-
order by
-
d.directory_name,
-
p.privilege
-
/
-
-
-
-
CREATE USER LHRSYS IDENTIFIED BY LHRSYS;
-
GRANT UPDATE (ENAME,SAL) ON SCOTT.EMP TO LHRSYS;
-
GRANT UPDATE (ENAME) ON SCOTT.EMP TO LHRSYS;
-
GRANT SELECT ON SCOTT.EMP TO LHRSYS;
-
GRANT CONNECT TO LHRSYS;
-
GRANT CREATE JOB TO LHRSYS;
-
-
SELECT * FROM DBA_COL_PRIVS D WHERE D.GRANTEE='LHRSYS';
-
SELECT * FROM DBA_TAB_PRIVS D WHERE D.GRANTEE='LHRSYS';
-
SELECT * FROM DBA_SYS_PRIVS D WHERE D.GRANTEE='LHRSYS';
-
SELECT * FROM DBA_ROLE_PRIVS D WHERE D.GRANTEE='LHRSYS';
-
-
-
-
-
--檢視建立表SQL語句:
-
SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;
-
SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME) FROM USER_TABLES U;
-
--檢視建立索引的SQL語句:
-
SELECT DBMS_METADATA.GET_DDL('INDEX','PK_DEPT','SCOTT') FROM DUAL;
-
SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME) FROM USER_INDEXES U;
-
--檢視建立主鍵的SQL語句:
-
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','EMP_PK') FROM DUAL;
-
--檢視建立外來鍵的SQL語句:
-
SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','EMP_FK_DEPT') FROM DUAL;
-
--檢視建立檢視(VIEW)的SQL語句:
-
SELECT DBMS_METADATA.GET_DDL('VIEW', 'MY_TABLES','SCOTT') FROM DUAL;
-
SELECT DBMS_METADATA.GET_DDL('VIEW', U.OBJECT_NAME)
-
FROM USER_OBJECTS U
-
WHERE OBJECT_TYPE = 'VIEW';
-
SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME=UPPER('&VIEW_NAME');
-
--檢視建立儲存過程(PROCEDURE)的SQL語句:
-
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', U.OBJECT_NAME)
-
FROM USER_OBJECTS U
-
WHERE OBJECT_TYPE = 'PROCEDURE';
-
--檢視建立觸發器(TRIGGER)的SQL語句:
-
SELECT DBMS_METADATA.GET_DDL('TRIGGER', U.OBJECT_NAME)
-
FROM USER_OBJECTS U
-
WHERE OBJECT_TYPE = 'TRIGGER';
-
--檢視建立函式(FUNCTION)的SQL語句:
-
SELECT DBMS_METADATA.GET_DDL('FUNCTION', U.OBJECT_NAME)
-
FROM USER_OBJECTS U
-
WHERE OBJECT_TYPE = 'FUNCTION';
-
--檢視建立包(PACKAGE)的SQL語句:
-
SELECT DBMS_METADATA.GET_DDL('PACKAGE', U.OBJECT_NAME)
-
FROM USER_OBJECTS U
-
WHERE OBJECT_TYPE = 'PACKAGE';
-
--檢視建立序列(SEQUENCE)的SQL語句:
-
SELECT DBMS_METADATA.GET_DDL('SEQUENCE', U.OBJECT_NAME)
-
FROM USER_OBJECTS U
-
WHERE OBJECT_TYPE = 'SEQUENCE';
-
--檢視建立同義詞(SYNONYM)的SQL語句:
-
SELECT DBMS_METADATA.GET_DDL('SYNONYM', U.OBJECT_NAME)
-
FROM USER_OBJECTS U
-
WHERE OBJECT_TYPE = 'SYNONYM';
-
--檢視建立表空間(TABLESPACE)的SQL語句:
-
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', U.TABLESPACE_NAME)
-
FROM USER_TABLESPACES U;
-
--檢視建立角色(ROLE)的SQL語句:
-
SELECT DBMS_METADATA.GET_DDL('ROLE', U.ROLE) FROM DBA_ROLES U;
-
--檢視建立使用者(USER)的SQL語句:
-
SELECT DBMS_METADATA.GET_DDL('USER','SYS') FROM DUAL;
-
-
------------------------- 得到表空間DDL語句
-
SELECT (SELECT b.NAME FROM v$tablespace b WHERE b.TS# = a.TS#) ts_name,
-
a.NAME datafilename,
-
'create tablespace '||(SELECT b.NAME FROM v$tablespace b WHERE b.TS# = a.TS#) || ' datafile ' || a.NAME ||' size ;'
-
FROM v$datafile a;
-
-
SELECT TABLESPACE_NAME,
-
substr(create_ts, 1, instr(create_ts, 'EXTENT') - 1) || ';'
-
FROM (SELECT a.TABLESPACE_NAME,
-
replace(to_char(DBMS_METADATA.GET_DDL('TABLESPACE',
-
a.tablespace_name)),
-
chr(10),
-
'') create_ts
-
FROM DBA_TABLESPACES a) v
-
where v.TABLESPACE_NAME not in
-
('SYSTEM', 'SYSAUX', 'UNDOTBS1', 'TEMP', 'USERS', 'EXAMPLE');
-
-
-
------------------------- 得到使用者及其許可權的DDL語句
-
SELECT DBMS_METADATA.GET_DDL('USER','LHRSYS') DDL_SQL FROM DUAL
-
UNION ALL
-
SELECT ((DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'LHRSYS'))) FROM DUAL
-
UNION ALL
-
SELECT ((DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'LHRSYS'))) FROM DUAL
-
UNION ALL
-
SELECT ((DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'LHRSYS'))) FROM DUAL;
-
-
-
drop table t_tmp_user_lhr;
-
create table t_tmp_user_lhr( id number, username varchar2(50), exec_sql varchar2(4000),create_type varchar2(20) );
-
DROP sequence s_t_tmp_user_lhr;
-
create sequence s_t_tmp_user_lhr;
-
-
begin
-
-
for cur in (SELECT d.username,
-
d.default_tablespace,
-
d.account_status,
-
'create user ' || d.username || ' identified by ' ||
-
d.username || ' default tablespace ' ||
-
d.default_tablespace || ' TEMPORARY TABLESPACE ' ||
-
D.temporary_tablespace || ';' CREATE_USER,
-
replace(to_char(DBMS_METADATA.GET_DDL('USER',
-
D.username)),
-
chr(10),
-
'') create_USER1
-
FROM dba_users d
-
WHERE d.username not in ('ANONYMOUS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DBSNMP','DIP','DMSYS','DVSYS','EXFSYS','FLOWS_FILES','HR','IX','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','OE','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PM','REMOTE_SCHEDULER_AGENT','SCOTT','SH','SI_INFORMATN_SCHEMA','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','XS$NULL','CSMIG')) loop
-
-
INSERT INTO t_tmp_user_lhr
-
(id, username, exec_sql, create_type)
-
values
-
(s_t_tmp_user_lhr.nextval, cur.username, cur.CREATE_USER, 'USER');
-
-
INSERT INTO t_tmp_user_lhr
-
(id, username, exec_sql, create_type)
-
SELECT s_t_tmp_user_lhr.nextval,
-
cur.username,
-
CASE
-
WHEN D.ADMIN_OPTION = 'YES' THEN
-
'GRANT ' || d.privilege || ' TO ' || d.GRANTEE ||
-
' WITH GRANT OPTION ;'
-
ELSE
-
'GRANT ' || d.privilege || ' TO ' || d.GRANTEE || ';'
-
END priv,
-
'DBA_SYS_PRIVS'
-
FROM dba_sys_privs d
-
WHERE D.GRANTEE = CUR.USERNAME;
-
-
INSERT INTO t_tmp_user_lhr
-
(id, username, exec_sql, create_type)
-
SELECT s_t_tmp_user_lhr.nextval,
-
cur.username,
-
CASE
-
WHEN D.ADMIN_OPTION = 'YES' THEN
-
'GRANT ' || d.GRANTED_ROLE || ' TO ' || d.GRANTEE ||
-
' WITH GRANT OPTION;'
-
ELSE
-
'GRANT ' || d.GRANTED_ROLE || ' TO ' || d.GRANTEE || ';'
-
END priv,
-
'DBA_ROLE_PRIVS'
-
FROM DBA_ROLE_PRIVS d
-
WHERE D.GRANTEE = CUR.USERNAME;
-
-
INSERT INTO t_tmp_user_lhr
-
(id, username, exec_sql, create_type)
-
SELECT s_t_tmp_user_lhr.nextval,
-
cur.username,
-
CASE
-
WHEN d.grantable = 'YES' THEN
-
'GRANT ' || d.privilege || ' ON ' || d.owner || '.' ||
-
d.table_name || ' TO ' || d.GRANTEE ||
-
' WITH GRANT OPTION ;'
-
ELSE
-
'GRANT ' || d.privilege || ' ON ' || d.owner || '.' ||
-
d.table_name || ' TO ' || d.GRANTEE || ';'
-
END priv,
-
'DBA_TAB_PRIVS'
-
FROM DBA_TAB_PRIVS d
-
WHERE D.GRANTEE = CUR.USERNAME;
-
end loop;
-
COMMIT;
-
end;
-
/
-
SELECT * FROM t_tmp_user_lhr;
-
-
-
-
------------------------------------------------------------------------------------------------------------------------------
-
------------------------------------------------------------------------------------------------------------------------------
-
-
-
---怎麼批量去除WORD裡表格中的超連結
-
-
全選文件。按"Ctrl+shift+F9"斷開連線就行了。
-
-
-
-
-
------------------------------------------------------------------------------------------------------------------------------
-
------------------------------------------------------------------------------------------------------------------------------
-
-
----linux下批量查詢/替換文字內容
-
--一般在本地電腦上批量替換文字有許多工具可以做到,比如sublime text ,但大多伺服器上都是無圖形介面的,為此收集了幾條針對linux命令列 實現批量替換文字內容的命令:
-
--1.批量查詢某個目下檔案的包含的內容,例如:
-
-
# grep -rn "要找查詢的文字" ./
-
-
[oracle@rhel6_lhr dpdump]$ grep -rn "ALTER SESSION SET EVENTS" ./
-
./spool_result.sql:2:ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
-
./spool_result.sql:3:ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
-
./spool_result.sql:4:ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
-
./spool_result.sql:5:ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
-
./spool_result.sql:6:ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
-
./spool_result.sql:7:ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-
-
-
--2.批量查詢並替換檔案內容。
-
# sed -i "s/要找查詢的文字/替換後的文字/g" `grep -rl "要找查詢的文字" ./`
-
-
例如替換 被病毒修改的一段指令碼:
-
sed -i "s/<script type=\"text\/javascript\" src='http:\/\/t.cn\/RhyQ1GN'><\/script>//g" `grep -rl "<script type=\"text\/javascript\" src='http:\/\/t.cn\/RhyQ1GN'><\/script>" ./`
-
-
----將STORAGE(INITIAL開頭的行整行替換為STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
-
-- 必須加 點和星號和最後的g ,否則不能整行替換
-
sed 's/^STORAGE(INITIAL.*/STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645/g' a.txt > b.txt
-
-
-
-
-
-
-
-------------------------------------------------------------------------------------------------------------- 修改日期的顯示格式
-
execute immediate 'alter session set NLS_DATE_FORMAT=''YYYY-MM-DD DY HH24:MI:SS''';
-
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
-
alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
-
-
-
----------------------------------------------------------------------------- sqlplus 設定
-
--$ORACLE_HOME/sqlplus/admin/glogin.sql
-
-
set linesize 9999 pagesize 9999
-
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
-
-
-
-
-
set time on;
-
set line 9999
-
set pagesize 9999;
-
set timing on;
-
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
-
-
-
-
set echo on;
-
set time on;
-
SET LONG 99999999;
-
SET LONGCHUNKSIZE 1000000;
-
set timing on;
-
set serveroutput on size 1000000;
-
set sqlblanklines on;
-
set linesize 800;
-
set pagesize 50000;
-
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
-
host color 02
-
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
-
set errorlogging on table lhr.sperrorlog;
-
set errorlogging on identifier LHR_SESSION
-
-
-
--- alter session set nls_date_language='american' ;
-
-
--------------------------------------------------------rman備份-----------------------------------------------------------
-
-
backup as backupset database format '/u05/oracle/oracle_bk/orclasm/full_%n_%T_%t_%s_%p.bak' include current controlfile plus archivelog;
-
backup as compressed backupset format '/arch/oracle_bk/ora2lhr/full_%n_%T_%t_%s.bak' database include current controlfile plus archivelog delete input ;
-
-
----------------------------- 歸檔 open 全備
-
export ORACLE_SID=ora11g
-
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
-
export PATH=$ORACLE_HOME/bin:$PATH
-
MYDATE=`date +'%Y%m%d%H%M%S'`
-
BACKUP_DIR=/home/oracle/oracle_bk/$ORACLE_SID
-
mkdir -p $BACKUP_DIR/log
-
-
-
rman target / log $BACKUP_DIR/log/rman_full_$MYDATE.log append <<EOF
-
run
-
{
-
allocate channel c1 type disk;
-
allocate channel c2 type disk;
-
backup database filesperset 4 format '$BACKUP_DIR/full_%n_%T_%t_%s_%p.bak';
-
backup spfile tag='bk_spfile_$MYDATE' format='$BACKUP_DIR/spfile_%n_%U_%T.bak';
-
sql 'alter system archive log current';
-
backup archivelog all format '$BACKUP_DIR/arch_%d_%T_%s_%p.bak' delete input;
-
backup current controlfile format '$BACKUP_DIR/ctl_%d_%T_%s_%p.bak';
-
release channel c1;
-
release channel c2;
-
}
-
EOF
-
-
rman target / log $BACKUP_DIR/log/rman_delete_$MYDATE.log append <<EOF
-
allocate channel for maintenance type disk;
-
allocate channel for maintenance type sbt_tape;
-
crosscheck archivelog all;
-
crosscheck backup;
-
delete noprompt obsolete;
-
delete noprompt expired archivelog all;
-
EOF
-
-
-
-
----------------------------- 非歸檔 mount 全備
-
export ORACLE_SID=oralhr
-
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
-
export PATH=$ORACLE_HOME/bin:$PATH
-
MYDATE=`date +'%Y%m%d%H%M%S'`
-
BACKUP_DIR=/home/oracle/oracle_bk/$ORACLE_SID
-
mkdir -p $BACKUP_DIR/log
-
-
-
rman target / log $BACKUP_DIR/log/rman_full_$MYDATE.log append <<EOF
-
run
-
{
-
shutdown immediate;
-
startup mount;
-
allocate channel c1 type disk;
-
allocate channel c2 type disk;
-
backup database filesperset 4 format '$BACKUP_DIR/full_%n_%T_%t_%s_%p.bak';
-
backup spfile tag='bk_spfile_$MYDATE' format='$BACKUP_DIR/spfile_%n_%U_%T.bak';
-
backup archivelog all format '$BACKUP_DIR/arch_%d_%T_%s_%p.bak' delete input;
-
backup current controlfile format '$BACKUP_DIR/ctl_%d_%T_%s_%p.bak';
-
release channel c1;
-
release channel c2;
-
alter database open;
-
}
-
EOF
-
-
rman target / log $BACKUP_DIR/log/rman_delete_$MYDATE.log append <<EOF
-
allocate channel for maintenance type disk;
-
allocate channel for maintenance type sbt_tape;
-
crosscheck archivelog all;
-
crosscheck backup;
-
delete noprompt obsolete;
-
EOF
-
-
-
-
-
-
--------------------------------------------------------冷備(最適合非歸檔)-----------------------------------------------------------
-
set feedback off
-
set heading off
-
set verify off
-
set trimspool off
-
set pagesize 0
-
set linesize 200
-
define dir = '/home/oracle/oracle_bk/coolbak'
-
define script = '/tmp/coolbak.sql'
-
spool &script
-
select 'ho cp ' || name || ' &dir' from v$controlfile
-
union all
-
select 'ho cp ' || name || ' &dir' from v$datafile
-
union all
-
select 'ho cp ' || member || ' &dir' from v$logfile
-
union all
-
select 'ho cp ' || name || ' &dir' from v$tempfile
-
/
-
create pfile = '&dir/initorcl.ora' from spfile;
-
ho cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwutf8test &dir
-
spool off
-
alter database backup controlfile to trace as '/home/oracle/oracle_bk/coolbak/ctl.sql';
-
shutdown immediate
-
start &script
-
--ho rm &script
-
startup
-
-
-
-
-
---------有壞塊情況下的備份 ORA-19566: exceeded limit of 0 corrupt blocks for file /oracle/app/oracle/oralhr/users01.dbf
-
run{
-
set maxcorrupt for datafile 4 to 2;
-
backup datafile 4 tag='2corruptblock';
-
}
-
-
-
-
-
-
--------------------------------------------------------熱備(歸檔)-----------------------------------------------------------
-
--------------------------- 熱備 基於database (歸檔)
-
set feedback off
-
set heading off
-
set verify off
-
set trimspool off
-
set pagesize 0
-
set linesize 200
-
define dir = '/home/oracle/oracle_bk/hotbak'
-
define script = '/tmp/hotbak.sql'
-
spool &script
-
select 'ho cp ' ||name|| ' &dir' from v$datafile;
-
spool off
-
alter database begin backup;
-
start &script
-
alter database end backup;
-
alter database backup controlfile to trace as '&dir/controlbak.sql';
-
alter database backup controlfile to '&dir/controlbak.ctl';
-
create pfile = '&dir/initorcl.ora' from spfile;
-
-
-
--------------------------熱備 基於表空間 (歸檔)
-
set feedback off
-
set heading off
-
set verify off
-
set trimspool off
-
set pagesize 0
-
set linesize 200
-
define dir = '/home/oracle/oracle_bk/hotbak'
-
define script = '/tmp/hotbak_tb.sql'
-
spool &script
-
select 'alter tablespace '|| tablespace_name ||' begin backup ;' ||
-
chr(10)||'ho cp ' || file_name || ' &dir ' ||
-
chr(10)||'alter tablespace '|| tablespace_name || ' end backup;'
-
from dba_data_files order by tablespace_name;
-
spool off
-
alter system switch logfile;
-
start &script
-
alter system switch logfile;
-
alter database backup controlfile to '&dir/controlbak.ctl';
-
alter database backup controlfile to trace as '&dir/controlbak.sql';
-
create pfile = '&dir/initorcl.ora' from spfile;
-
-
-
----------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------
-
----------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------
-
-------------------------------------- LogMiner 日誌挖掘
-
要安裝LogMiner工具,必須首先要執行下面這樣兩個指令碼:
-
$ORACLE_HOME/rdbms/admin/dbmslm.sql
-
$ORACLE_HOME/rdbms/admin/dbmslmd.sql
-
這兩個指令碼必須均以SYS使用者身份執行。其中第一個指令碼用來建立DBMS_LOGMNR包,該包用來分析日誌檔案。第二個指令碼用來建立DBMS_LOGMNR_D包,該包用來建立資料字典檔案。
-
-
-
---設定單獨的表空間
-
create tablespace ts_LOGMNR datafile '/sda4/u01/app/oracle/oradata/orcllinux/logmnr.dbf' size 1G autoextend on next 2M ;
-
exec dbms_logmnr_d.set_tablespace('ts_logmnr');
-
-
-
-
---------------- 附加日誌
-
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-
alter database add supplemental log data(primary key) columns;
-
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
-
SELECT SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_pk FROM V$DATABASE;
-
-
-
---找到需要進行日誌挖掘的歸檔日誌
-
list archivelog all completed between '2017-01-01 16:20:00' and '2017-01-01 16:25:00';
-
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss';
-
-
exec dbms_logmnr.add_logfile('歸檔日誌檔案', Options => dbms_logmnr.new);
-
exec dbms_logmnr.add_logfile('歸檔日誌檔案', Options => dbms_logmnr.ADDFILE);
-
-
--若歸檔日誌不在本地,則需要恢復相應的歸檔日誌到本地目錄。
-
run {allocate channel ci type disk;
-
set archivelog destination to '/tmp';
-
restore archvielog from logseq xxx until logseq xxx;
-
release channel ci;
-
};
-
-
-
-
-
-------------- 利用平面檔案作為資料字典
-
alter system set utl_file_dir='/home/oracle/' scope=spfile; --然後重啟庫
-
exec dbms_logmnr_d.build('log.ora','/home/oracle/',dbms_logmnr_d.store_in_flat_file);
-
exec dbms_logmnr.add_logfile('+FRA/orclasm/archivelog/2015_01_20/thread_1_seq_952.463.869481079',dbms_logmnr.new);
-
exec dbms_logmnr.add_logfile('+FRA/orclasm/archivelog/2015_01_20/thread_1_seq_953.462.869481107',dbms_logmnr.addfile);
-
exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/log.ora');
-
create table LHR.testlog as select * from v$logmnr_contents a;
-
EXEC DBMS_LOGMNR.END_LOGMNR;
-
-
-------------------------- redo 字典
-
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-
exec dbms_logmnr_d.build(options => dbms_logmnr_d.STORE_IN_REDO_LOGS);
-
select a.*
-
from v$archived_log a
-
WHERE a.name IS NOT NULL
-
and (a.DICTIONARY_BEGIN = 'YES' or a.DICTIONARY_END = 'YES');
-
-
-
-------------- 利用線上日誌作為資料字典
-
SELECT ' dbms_logmnr.add_logfile(''' || MEMBER || ''');' FROM v$logfile;
-
-
BEGIN
-
dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_3.263.850260263',dbms_logmnr.new) ;
-
dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_2.262.850260259',dbms_logmnr.ADDFILE) ;
-
dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_1.261.850260255',dbms_logmnr.ADDFILE)) ;
-
dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only);
-
END;
-
-
SELECT scn, sql_redo, a.SQL_UNDO, a.*
-
FROM v$logmnr_contents a
-
WHERE a.OPERATION = 'INSERT'
-
and a.TABLE_NAME = 'AABB';
-
-
create table testlog as select * from v$logmnr_contents a;
-
-
begin
-
dbms_logmnr.end_logmnr();
-
end;
-
-
-
-
EXECUTE DBMS_LOGMNR_D.BUILD (OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
-
-
-
-
-
-------------------------- online字典
-
begin
-
dbms_logmnr.start_logmnr(startScn => 23573690,
-
endScn => 23632671,
-
Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG + dbms_logmnr.CONTINUOUS_MINE);
-
end;
-
-
-
-
begin
-
dbms_logmnr.start_logmnr(startScn => 23573690,
-
endScn => 23632671,
-
Options => dbms_logmnr.DICT_FROM_REDO_LOGS +
-
dbms_logmnr.CONTINUOUS_MINE);
-
end;
-
-
-
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss';
-
list archivelog all completed between '2015-06-03 08:00:00' and '2015-06-03 09:00:00';
-
exec SYS.DBMS_LOGMNR.ADD_LOGFILE( '/u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2015_06_03/o1_mf_1_505_7w35gdnx_.arc', sys.dbms_logmnr.New);
-
BEGIN
-
DBMS_LOGMNR.START_LOGMNR(
-
STARTTIME => '2015-06-03 11:10:12',
-
ENDTIME => '2015-06-03 11:13:06',
-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
-
END;
-
/
-
-
---查詢挖掘到的結果
-
select a.SCN,a.TIMESTAMP,a.SQL_REDO from v$logmnr_contents A where table_name='XXXX' and OPERATION='INSERT' order by a.SCN;
-
-
-
-
-
--清除控制檔案中關於v$archived_log的資訊
-
execute sys.dbms_backup_restore.resetCfileSection(11);
-
-
---恢復歸檔
-
1.恢復全部歸檔日誌檔案
-
RMAN> restore archivelog all;
-
2.只恢復5到8這四個歸檔日誌檔案
-
RMAN> restore archivelog from logseq 5 until logseq 8;
-
3.恢復從第5個歸檔日誌起
-
RMAN> restore archivelog from logseq 5;
-
4.恢復7天內的歸檔日誌
-
RMAN> restore archivelog from time 'sysdate-7';
-
5. sequence between 寫法
-
RMAN> restore archivelog sequence between 1 and 3;
-
6.恢復到哪個日誌檔案為止
-
RMAN> restore archivelog until logseq 3;
-
7.從第五個日誌開始恢復
-
RMAN> restore archivelog low logseq 5;
-
8.到第5個日誌為止
-
RMAN> restore archivelog high logseq 5;
-
恢復指定的archivelog:restore archivelog sequence 18;
-
--若歸檔日誌不在本地,則需要恢復相應的歸檔日誌到本地目錄。
-
run {allocate channel ci type disk;
-
set archivelog destination to '/tmp';
-
restore archvielog from logseq xxx until logseq xxx;
-
release channel ci;
-
};
-
-
-
-
------------------------------------------------------------------------------------------ 詳細執行計劃
-
-
---------------Session級別:
-
ALTER SESSION SET STATISTICS_LEVEL=ALL;
-
----------------- 詫句級別 使用HINT
-
select /*+ gather_plan_statistics*/ ...
-
-
-
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f6cz4n8y72xdc',0,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
-
-
-
-
-
-
-------------------------------------------------------------------------------------------- 其他
-
-
-
-
SELECT * FROM DBA_STREAMS_UNSUPPORTED;
-
-
------------------- 鎖和事務關係
-
select * from v$lock a where type in ('TM','TX') ;
-
-
SELECT * from v$transaction;
-
-
select trunc(655385/power(2,16)) XIDUSN from dual;
-
10
-
-
select bitand(655385,to_number('ffff','xxxx'))+0 XIDSLOT from dual;
-
-
25
-
-
-
---------------------------------truncate 後的恢復-------------------------- 恢復的過程會多次執行 alter system flush buffer_cache;
-
-
-
------------ tmp目錄空閒空間需要比表的大小大
-
create table lhr.TRUNTAB as SELECT * FROM dba_objects where rownum<=10;
-
-
SELECT COUNT(1) FROM lhr.TRUNTAB;
-
-
truncate table lhr.TRUNTAB;
-
-
exec fy_recover_data.recover_truncated_table('LHR','TRUNTAB',1);
-
-
SELECT * FROM lhr.TRUNTAB$$;
-
insert into lhr.TRUNTAB SELECT * FROM lhr.TRUNTAB$$;
-
commit;
-
drop tablespace FY_REC_DATA including contents and datafiles;
-
drop tablespace FY_RST_DATA including contents and datafiles;
-
-
-
-
-
-
-
-
---閃回
-
alter table TRUNTAB enable row movement;
-
flashback table TRUNTAB to timestamp to_date('2014/12/31 17:33:00','YYYY/MM/DD HH24:MI/SS');
-
-
-
-
-
-
--------------長查詢
-
SELECT * FROM vw_active_session_lhr a where a.USERNAME IS NOT NULL ;
-
SELECT * FROM vw_longrun_lhr;
-
SET LINE 9999 PAGESIZE 9999
-
col username format a10
-
col session_info format a30
-
col target format a20
-
col opname format a35
-
col message format a80
-
col sofar_TOTALWORK format a20
-
col progress format a8
-
-
SELECT A.USERNAME,
-
(SELECT NB.SID || ',' || NB.SERIAL# || ',' || PR.SPID || ',' ||NB.OSUSER|| ',' ||nb.status|| ',' ||nb.EVENT
-
FROM GV$PROCESS PR, GV$SESSION NB
-
WHERE NB.PADDR = PR.ADDR
-
AND NB.SID = A.SID
-
AND NB.SERIAL# = A.SERIAL#
-
AND PR.INST_ID = NB.INST_ID) SESSION_INFO,
-
A.TARGET,
-
A.OPNAME,
-
TO_CHAR(A.START_TIME, 'YYYY-MM-DD HH24:MI:SS') START_TIME,
-
ROUND(A.SOFAR * 100 / A.TOTALWORK, 2) || '%' AS PROGRESS,
-
(A.SOFAR || ':' || A.TOTALWORK) SOFAR_TOTALWORK,
-
A.TIME_REMAINING TIME_REMAINING,
-
A.ELAPSED_SECONDS ELAPSED_SECONDS,
-
MESSAGE MESSAGE
-
FROM GV$SESSION_LONGOPS A
-
WHERE A.TIME_REMAINING <> 0
-
ORDER BY A.TIME_REMAINING DESC, A.SQL_ID, A.SID;
-
-
-
-
-
-
-
SELECT * FROM vw_tablespace_datafile_lhr ;
-
-
SELECT * FROM xb_audit_ddl_lhr a WHERE a.id >=2373180 ;
-
-
-
----------------------------------------------------------- 歸檔
-
alter system set log_archive_dest_1='location=D:\arch';
-
alter system set log_archive_dest='USE_DB_RECOVERY_FILE_DEST';
-
-
show parameter DB_RECOVERY_FILE_DEST
-
-
-
如果歸檔日誌為自動歸檔,則切換日誌(alter system switch logfile)會自動歸檔;
-
如果為手動歸檔模式,則不會歸檔,除非你執行下列命令手動歸檔:
-
alter system archive log sequence lognumber
-
alter system archive log all
-
alter system archive log current
-
-
檢視歸檔日誌模式:select log_mode from v$database;
-
NOARCHIVELOG-- 為不歸檔
-
ARCHIVELOG -- 為自動歸檔
-
MANUAL --手動歸檔模式
-
修改歸檔日誌模式:
-
alter database noarchivelog;
-
alter database archivelog;
-
alter database archivelog manual;
-
-
-
-
----------------通過移動資料檔案來均衡檔案I/O
-
col PHYRDS format 999999999
-
col PHYWRTS format 999999999
-
col READTIM format 999999999
-
col WRITETIM format 999999999
-
col name for a60
-
set line 9999 pagesize 9999
-
select name,phyrds,phywrts,readtim,writetim
-
from v$filestat a,v$datafile b
-
where a.file#=b.file#
-
union all
-
select name,PHYRDS,PHYWRTS,READTIM,WRITETIM from v$tempstat a,v$tempfile b where a.file#=b.file#
-
order by readtim desc;
-
-
-
-
-
-
-
/*檢視錶最後一次DML時間*/
-
select max(ora_rowscn), to_char(scn_to_timestamp(max(ora_rowscn)),'YYYY-MM-DD HH24:MI:SS') from aa;
-
select a.*,dbms_rowid.rowid_block_number(rowid),to_char(ora_rowscn),to_char(scn_to_timestamp(ora_rowscn),'YYYY-MM-DD HH24:MI:SS') from AA a;
-
-
SQL> select ename ,sal,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_relative_fno(rowid) as file# from scott.emp where empno=7839;
-
-
ENAME SAL BLOCK# FILE#
-
---------- ---------- ---------- ----------
-
KING 5000 32 4
-
-
SQL>
-
-
-
------------------------------------------ 日誌切換頻率
-
select b.SEQUENCE#,
-
b.FIRST_TIME,
-
a.SEQUENCE#,
-
a.FIRST_TIME,
-
round(((a.FIRST_TIME - b.FIRST_TIME) * 24) * 60, 2) min
-
from v$log_history a, v$log_history b
-
where a.SEQUENCE# = b.SEQUENCE# + 1
-
and b.THREAD# = 1
-
order by a.SEQUENCE# desc;
-
-
-
select sequence#,
-
first_time,
-
nexttime,
-
round(((first_time - nexttime) * 24) * 60, 2) diff
-
from (select sequence#,
-
first_time,
-
lag(first_time) over(order by sequence#) nexttime
-
from v$log_history
-
where thread# = 1)
-
order by sequence# desc;
-
-
-
select max (first_time) max_first_time,
-
to_char (first_time, 'yyyy-mm-dd') day,
-
count (recid) count_number,
-
count (recid) * 200 size_mb
-
from v$log_history
-
group by to_char (first_time, 'yyyy-mm-dd')
-
order by 1;
-
-
-
--------------------------------------------------------------------------------------------------- 開啟10046事件
-
-
10046事件級別:
-
-
level 0:禁用SQL_TRACE,等價於SQL_TRACE=FALSE
-
level 1:啟用標準的sql_trace功能跟蹤SQL語句,包括解析、執行、提取、提交和回滾等,等價於SQL_TRACE=TRUE
-
level 4:Level 1 +包括變數(bind values)的詳細資訊
-
level 8:Level 1 + 包括等待事件
-
level 12:包括繫結變數與等待事件,包含Level 1 + Level 4 + Level 8
-
-
-
-
alter session set SQL_TRACE=true;
-
alter session set SQL_TRACE=false;
-
alter session set events '10046 trace name context forever, level 12';
-
alter session set events '10046 trace name context off';
-
alter session set events '10046 trace name context off, LEVEL 12';
-
--對單個 SQL ID 開啟10046事件跟蹤
-
ALTER SYSTEM SET EVENTS 'SQL_TRACE [SQL:&&SQL_ID] BIND=TRUE,WAIT=TRUE';
-
ALTER SYSTEM SET EVENTS 'SQL_TRACE [SQL:C7452AGJ0S0T6] WAIT=TRUE,BIND=TRUE,PLAN_STAT=ALL_EXECUTIONS,LEVEL=12';
-
--關閉單個SQL的跟蹤命令如下所示:
-
ALTER SYSTEM SET EVENTS 'SQL_TRACE [SQL:&&SQL_ID] OFF';
-
-
-
exec dbms_session.set_sql_trace(true);
-
exec dbms_session.set_sql_trace(false);
-
exec dbms_session.session_trace_enable(waits=>true,binds=>true);
-
exec dbms_session.session_trace_enable();
-
-
-
-
-----跟蹤其它會話
-
SQL> exec dbms_system.set_ev(sid,serial#,10046,12,'');
-
SQL> exec dbms_system.set_ev(sid,serial#,10046,0,'');
-
-
exec dbms_system.set_sql_trace_in_session(9,437,true);
-
exec dbms_system.set_sql_trace_in_session(9,437,false);
-
-
-
SQL> exec dbms_monitor.session_trace_enable;
-
SQL> 執行sql
-
SQL> exec dbms_monitor.session_trace_disable;
-
跟蹤其他會話:
-
SQL> exec dbms_monitor.session_trace_enable(session_id=>sid,serial_num=>serial#,waits=>true,binds=>true);
-
SQL> exec dbms_monitor.session_trace_disable(session_id=>sid,serial_num=>serial#);
-
-
-
-
-
--跟蹤當前會話:
-
SQL> oradebug setmypid;
-
Statement processed.
-
SQL> oradebug unlimit;
-
Statement processed.
-
SQL> oradebug event 10046 trace name context forever,level 12;
-
Statement processed.
-
SQL> 執行sql
-
SQL> oradebug tracefile_name
-
SQL> oradebug event 10046 trace name context off;
-
Statement processed.
-
--跟蹤其他會話:
-
SQL> select spid,pid2 from v$process
-
2 where addr in (select paddr from v$session where sid=(select distinct sid from v$mystat));
-
SPID PID
-
------------ ----------
-
1457 313
-
SQL> oradebug setospid 1457;
-
Statement processed.
-
或者
-
SQL> oradebug setorapid 313;
-
Statement processed.
-
SQL> oradebug unlimit;
-
Statement processed.
-
SQL> oradebug event 10046 trace name context forever,level 12;
-
Statement processed.
-
SQL> oradebug tracefile_name
-
SQL> oradebug event 10046 trace name context off;
-
Statement processed.
-
-
-
-
-
-
SELECT a.SID,
-
b.SERIAL# ,
-
c.SPID
-
FROM v$mystat a,
-
v$session b ,
-
v$process c
-
WHERE a.SID = b.SID
-
and b.PADDR=c.ADDR
-
AND rownum = 1;
-
-
-
--啟用errorstack的跟蹤來找到出現問題的SQL語句
-
alter session set events '3001 trace name errorstack level 3';
-
-
---跟蹤1438的錯誤
-
alter system set events='1438 trace name errorstack forever,level 3';
-
alter system set events='1438 trace name errorstack off';
-
-
-
系統預設沒有安裝dbms_support這個包,可以手動執行$ORACLE_HOME/rdbms/admin/bmssupp.sql指令碼來建立該包跟蹤當前會話:
-
SQL> exec dbms_support.start_trace
-
SQL> 執行sql
-
SQL> exec dbms_support.stop_trace
-
跟蹤其他會話:等待事件+繫結變數,相當於level 12的10046事件。
-
SQL> select sid,serial#,username from v$session where ...;
-
SQL> exec dbms_support.start_trace_in_session(sid=>sid,serial=>serial#,waits=>true,binds=>true);
-
SQL> exec dbms_support.stop_trace_in_session(sid=>sid,serial=>serial#);
-
-
-
----select value from v$diag_info where name like '%Default%';
-
-
--轉儲檔案路徑
-
col TRACE_FILE_NAME format a100
-
SELECT d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
-
p.spid || '.trc' trace_file_name
-
FROM (SELECT p.spid
-
FROM v$mystat m,
-
v$session s,
-
v$process p
-
WHERE m.statistic# = '1'
-
AND s.sid = m.sid
-
AND p.addr = s.paddr) p,
-
(SELECT t.instance
-
FROM v$thread t,
-
v$parameter v
-
WHERE v.name = 'thread'
-
AND (v.value = '0' OR to_char(t.thread#) = v.VALUE)) i,
-
(SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
-
-
CREATE OR REPLACE VIEW VW_TRACEFILE_ALL_LHR AS
-
SELECT INST_ID,
-
SID,
-
SERIAL#,
-
SPID,
-
USERNAME,
-
D.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
-
P.SPID || '.trc' TRACE_FILE_NAME
-
FROM (SELECT S.INST_ID, S.SID, S.SERIAL#, P.SPID, S.USERNAME
-
FROM GV$SESSION S, GV$PROCESS P
-
WHERE P.ADDR = S.PADDR
-
AND S.INST_ID = P.INST_ID) P,
-
(SELECT T.INSTANCE
-
FROM GV$THREAD T, GV$PARAMETER V
-
WHERE V.NAME = 'thread'
-
AND (V.VALUE = '0' OR TO_CHAR(T.THREAD#) = V.VALUE)) I,
-
(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') D;
-
-
--建立公共同義詞:
-
CREATE OR REPLACE PUBLIC SYNONYM SYN_TRACEFILE_ALL_LHR FOR VW_TRACEFILE_ALL_LHR;
-
-
在Oracle 11g中可以直接查詢V$PROCESS獲取TRACE檔案:
-
CREATE OR REPLACE VIEW VW_TRACEFILE_LHR AS
-
SELECT S.INST_ID, S.SID, S.SERIAL#, P.SPID, S.USERNAME, P.TRACEFILE
-
FROM GV$SESSION S, GV$PROCESS P
-
WHERE P.ADDR = S.PADDR
-
AND S.INST_ID = P.INST_ID ;
-
-
--建立公共同義詞:
-
CREATE OR REPLACE PUBLIC SYNONYM SYN_TRACEFILE_LHR FOR VW_TRACEFILE_LHR;
-
-
-
-
--------------------------如何生成 systemstate dump systemdump systemstate
-
###sysdba可登陸時
-
$sqlplus "/as sysdba"
-
SQL>oradebug setmypid
-
SQL>--其中266表示dump的級別,不用調整,即把系統當前各個程式正在執行什麼、正在等待什麼全部抓下來
-
SQL>oradebug dump systemstate 266 --oradebug dump systemdump 266;
-
SQL>--等上30秒到1分鐘
-
SQL>oradebug dump systemstate 266
-
-
-
###sysdba不可登陸時
-
sqlplus -prelim "/as sysdba"
-
SQL>oradebug setmypid
-
SQL>--其中266表示dump的級別,不用調整,即把系統當前各個程式正在執行什麼、正在等待什麼全部抓下來
-
SQL>oradebug dump systemstate 266
-
SQL>--等上30秒到1分鐘
-
SQL>oradebug dump systemstate 266
-
SQL> oradebug tracefile_name
-
/u02/app/oracle/diag/rdbms/lhrdb/lhrdb/trace/lhrdb_ora_46679.trc
-
-
-
----------HANGANALYZE 分析
-
SQL> alter session set events 'immediate trace name HANGANALYZE level 3';
-
或者:
-
SQL>ORADEBUG hanganalyze 3 --for 單例項
-
-
------for RAC 例項
-
SQL>ORADEBUG setmypid
-
SQL>ORADEBUG setinst all
-
SQL>ORADEBUG -g def hanganalyze 3
-
-
The levels are defined as follows:
-
10 Dump all processes (IGN state)
-
5 Level 4 + Dump all processes involved in wait chains (NLEAF state)
-
4 Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)
-
3 Level 2 + Dump only processes thought to be in a hang (IN_HANG state)
-
1-2 Only HANGANALYZE output, no process dump at all
-
-
-
-
----------------------------------------------------------------------------------------------------- 層次查詢
-
SELECT LEVEL,
-
id,
-
parentid,
-
(lpad(' ', 8 * (LEVEL - 1)) || LEVEL || ':' || l.name) names,
-
substr(SYS_CONNECT_BY_PATH(NAME, '=>'), 3),
-
connect_by_root(NAME) root,
-
decode(LEVEL,
-
2,
-
NAME,
-
substr(SYS_CONNECT_BY_PATH(NAME, '=>'),
-
instr(SYS_CONNECT_BY_PATH(NAME, '=>'), '>', 1, 2) + 1,
-
(instr(SYS_CONNECT_BY_PATH(NAME, '=>'), '=', 1, 3) -
-
instr(SYS_CONNECT_BY_PATH(NAME, '=>'), '>', 1, 2) - 1))) root2,
-
decode(connect_by_isleaf, 1, 'Y', 0, 'N') is_leaf,
-
decode(connect_by_iscycle, 1, 'Y', 0, 'N') is_leaf
-
FROM xb_location l
-
START WITH l.parentid IS NULL
-
CONNECT BY nocycle PRIOR l.id = l.parentid;
-
-
-
-
-
----------------------------------------------------------------------------------------------------- 啟用系統觸發器
-
alter system set "_system_trig_enabled"=true; --預設
-
alter system reset "_system_trig_enabled" scope=spfile sid='*';
-
-
-
-
----------------------------------------------------------------------------------------------------- 閃回
-
-
select * from user_recyclebin t where t.original_name LIKE'TMP_AB%';
-
select * from dba_recyclebin;
-
-
--ddl
-
flashback table TMP_AB to before drop rename to old_t;
-
flashback table "BIN$B/HqKSpfWrvgU4I7qMATlg==$0" to before drop;
-
-
--dml
-
alter table old_t enable row movement;
-
flashback table old_t to timestamp to_date('2012/11/13 16:16:40','YYYY/MM/DD HH24:MI/SS');
-
-
-
-
--insert into xb_port
-
SELECT *
-
FROM xb_port AS OF TIMESTAMP TO_TIMESTAMP('2013-04-25 16:53:28', 'YYYY-MM-DD HH24:MI:SS')
-
WHERE id = 307247374 ;
-
commit;
-
-
-
-
SELECT timestamp_to_scn(TO_TIMESTAMP('2012-11-13 16:25:17',
-
'YYYY-MM-DD HH24:MI:SS')),
-
to_char(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) AS SCN
-
FROM dual;
-
-
----查詢
-
SELECT versions_starttime,
-
versions_startscn,
-
versions_endtime,
-
versions_endscn,
-
versions_xid,
-
versions_operation,
-
id,
-
NAME
-
FROM xb_location versions BETWEEN TIMESTAMP to_timestamp('2012-11-13 15:10:40', 'yyyy-mm-dd hh24:mi:ss') AND to_timestamp(to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
-
WHERE versions_xid IS NOT NULL
-
ORDER BY versions_starttime;
-
-
-
SELECT versions_starttime,
-
versions_startscn,
-
versions_endtime,
-
versions_endscn,
-
versions_xid,
-
versions_operation,
-
id,
-
NAME
-
FROM xb_location versions BETWEEN scn minvalue AND maxvalue
-
WHERE versions_xid IS NOT NULL
-
ORDER BY versions_starttime;
-
-
-
------------------------------------------------------- undo表空間
-
--1、系統段 2、非系統段 3、表空間離線後的defered段
-
SELECT d.segment_type,
-
COUNT(1)
-
FROM dba_segments d
-
GROUP BY d.segment_type;
-
-
select * from dba_segments d where d.segment_type in ('TYPE2 UNDO','ROLLBACK') ;
-
select * from dba_rollback_segs;
-
select * from dba_undo_extents;
-
select * from v$transaction;
-
select * from v$rollstat;
-
select * from v$rollname;
-
select * from dba_extents d where d.segment_name='_SYSSMU25_17381587$';
-
select * from v$undostat; ---mount狀態可查
-
--ssolderrcnt : snapshot too old error count
-
-
select status,count(*) from dba_rollback_segs group by status;
-
-
SELECT d.TABLESPACE_NAME,
-
d.STATUS,
-
SUM(bytes) / 1024 / 1024
-
FROM dba_undo_extents d
-
GROUP BY d.TABLESPACE_NAME,
-
d.status
-
ORDER BY d.TABLESPACE_NAME;
-
-
SELECT r.tablespace_name,
-
r.status "Status",
-
r.segment_name "Name",
-
s.extents "Extents",
-
TO_CHAR((s.bytes / 1024 / 1024), '99999990.000') "SizeM",
-
s.segment_type
-
FROM dba_rollback_segs r,
-
dba_segments s
-
WHERE r.segment_name = s.segment_name
-
AND s.segment_type IN ('ROLLBACK', 'TYPE2 UNDO')
-
ORDER BY r.tablespace_name,
-
5 DESC;
-
-
----估算undo需要的大小
-
SELECT (UR * (UPS * DBS)) AS "Bytes" FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'), (SELECT undoblks/((end_time-begin_time)*86400) AS UPS FROM v$undostat WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)), (SELECT block_size AS DBS FROM dba_tablespaces WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));
-
-
-
-------------------- 已用大小
-
set line 9999
-
select s.sid,
-
s.serial#,
-
s.sql_id,
-
v.usn,
-
segment_name,
-
r.status,
-
v.rssize / 1024 / 1024 mb
-
From dba_rollback_segs r, v$rollstat v, v$transaction t, v$session s
-
Where r.segment_id = v.usn
-
and v.usn = t.xidusn
-
and t.addr = s.taddr
-
order by segment_name;
-
-
-
------undo總大小
-
-
SET ECHO OFF
-
SET FEEDBACK 6
-
SET HEADING ON
-
SET LINESIZE 180
-
SET PAGESIZE 50000
-
SET TERMOUT ON
-
SET TIMING OFF
-
SET TRIMOUT ON
-
SET TRIMSPOOL ON
-
SET VERIFY OFF
-
-
COLUMN status FORMAT a9 HEADING 'Status'
-
COLUMN name FORMAT a30 HEADING 'Tablespace Name'
-
COLUMN type FORMAT a15 HEADING 'TS Type'
-
COLUMN extent_mgt FORMAT a10 HEADING 'Ext. Mgt.'
-
COLUMN segment_mgt FORMAT a10 HEADING 'Seg. Mgt.'
-
COLUMN ts_size FORMAT 9,999,999,999,999 HEADING 'Tablespace Size'
-
COLUMN used FORMAT 9,999,999,999,999 HEADING 'Used (in bytes)'
-
COLUMN free FORMAT 9,999,999,999,999 HEADING 'Free (in bytes)'
-
COLUMN pct_used FORMAT 999 HEADING 'Pct. Used'
-
-
-
SELECT
-
d.status status
-
, d.tablespace_name name
-
, d.contents type
-
, d.extent_management extent_mgt
-
, d.segment_space_management segment_mgt
-
, NVL(a.bytes, 0) ts_size
-
, NVL(a.bytes - NVL(f.bytes, 0), 0) used
-
, NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) pct_used
-
FROM
-
sys.dba_tablespaces d
-
, ( 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
-
) f
-
WHERE
-
d.tablespace_name = a.tablespace_name(+)
-
AND d.tablespace_name = f.tablespace_name(+)
-
AND d.tablespace_name like '%UNDO%'
-
ORDER BY
-
2;
-
-
-
-
--資料檔案管理引數: db_create_file_dest
-
CREATE TABLESPACE test DATAFILE SIZE 10m;
-
alter database datafile 3 resize 5G;
-
alter database tempfile 1 resize 2G;
-
alter database datafile 3 autoextend off;
-
alter database tempfile 1 autoextend off;
-
-
-
-
-
-
-------------- 重建undo表空間
-
create undo tablespace undotbs2 datafile '+DATA' size 100m reuse autoextend off;
-
alter system set undo_tablespace=undotbs2;
-
drop tablespace undotbs1 including contents and datafiles;
-
create undo tablespace undotbs1 datafile '+DATA' size 100m reuse autoextend off;
-
alter system set undo_tablespace=undotbs1;
-
-
alter database datafile 3 autoextend off;
-
alter database tempfile 1 autoextend off;
-
-
-
-
create undo tablespace undotbs2 datafile '+DATA' size 5M;
-
-
alter system set undo_tablespace=undotbs2;
-
alter tablespace undotabs2 retention guarantee;
-
-
-
select t.rowid,dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) bno, t.owner,t.object_name from t ;
-
-
alter system dump datafile 4 block 6643;
-
-
select * from vw_mysession_lhr;
-
-
-
UBA : undo block address
-
-
-
-
select * from v$obsolete_parameter;
-
-
-
alter database create datafile 3 as '/u03/app/oracle/oradata/ora1024g/undotbs01.dbf' size 50m;
-
-
-
------- undo
-
alter system set "_offline_rollback_segments"=true scope=spfile;
-
alter system set "_offline_rollback_segments"=false scope=spfile; ---預設
-
alter system reset "_offline_rollback_segments" scope=spfile sid='*';
-
-
-
*._offline_rollback_segments=('_SYSSMU154_3691636531$','_SYSSMU155_3686385895$','_SYSSMU156_3796802683$','_SYSSMU157_2723916652$','_SYSSMU158_1435464080$')
-
-
-
_offline_rollback_segment='_SYSSMU3$'
-
_newsort_enabled --排序
-
-
-
-------- 如果undo為recover狀態的話還需要加如下引數
-
alter system set "_corrupted_rollback_segments"='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$' scope=spfile;
-
-
alter system reset "_corrupted_rollback_segments" scope=spfile sid='*';
-
-
-
alter system set "_corrupted_rollback_segments"='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$','_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16$','_SYSSMU17$','_SYSSMU18$','_SYSSMU19$','_SYSSMU20$' scope=spfile;
-
-
*._corrupted_rollback_segments=('_SYSSMU10_3271578125','_SYSSMU11_125382609','_SYSSMU1_1240252155','_SYSSMU12_2245433549','_SYSSMU13_3242268464','_SYSSMU14_44821983','_SYSSMU15_1872739176','_SYSSMU16_1376564431','_SYSSMU17_1839632768','_SYSSMU18_3088942417','_SYSSMU19_2867910983','_SYSSMU20_948290921','_SYSSMU2_111974964','_SYSSMU3_4004931649','_SYSSMU4_1126976075','_SYSSMU5_2968973961','_SYSSMU6_2060978448','_SYSSMU7_4222772309','_SYSSMU8_3612859353','_SYSSMU9_2370500926')
-
-
-
drop rollback segment "drop rollback segment";
-
drop rollback segment "_SYSSMU154_3691636531$";
-
-
-
-
---- 查詢undo段
-
strings /u01/app/oracle/oradata/ora11g/system01.dbf | grep _SYSSMU | sort -u >/tmp/system.txt
-
-
more /tmp/system.txt
-
-
注意:通過system01.dbf查出了,正在使用的undo segment,以上按使用時間做了排序,注意只選擇那些排在最前面的(相同回滾段);預設每個undo tablespace 會應用10個undo segments。
-
-
-
-
------- 不能建立undo檔案(ORA-01178錯誤),無備份的情況下采用隱含引數啟動資料庫
-
set line 9999
-
col name format a100
-
select file#, name,status,enabled from v$datafile;
-
-
select * from v$recover_file;
-
-
alter system set undo_management=manual scope=spfile;
-
alter database datafile 3 offline;
-
alter system set undo_tablespace=SYSTEM scope=spfile;
-
alter system set "_offline_rollback_segments"=true scope=spfile;
-
! strings '/u01/app/oracle/oradata/orcltest/system01.dbf' | grep _SYSSMU | sort -u
-
alter system set
-
"_corrupted_rollback_segments"='_SYSSMU3_4004931649$','_SYSSMU3_4160240979$','_SYSSMU34_2573821980$','_SYSSMU35_3476245049$','_SYSSMU36_353473384$','_SYSSMU37_682878819$','_SYSSMU38_2521239011$','_SYSSMU39_1467520375$','_SYSSMU40_753689919$','_SYSSMU4_1126976075$','_SYSSMU4_348804819$','_SYSSMU5_2968973961$','_SYSSMU5_4011504098$','_SYSSMU6_2060978448$','_SYSSMU6_3654194381$','_SYSSMU7_4222772309$','_SYSSMU7_894058185$','_SYSSMU8_3612859353$','_SYSSMU8_87803851$','_SYSSMU9_2370500926$','_SYSSMU9_2370500926$','_SYSSMU9_3945653786$' scope=spfile;
-
shutdown immediate;
-
startup mount;
-
alter database open;
-
select segment_name,status,tablespace_name from dba_rollback_segs;
-
drop tablespace UNDOTBS1;
-
create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/orcltest/undotbs01.dbf' size 50m autoextend on;
-
alter system set undo_tablespace=UNDOTBS1 scope=spfile;
-
alter system set undo_management=auto scope=spfile;
-
alter system reset "_offline_rollback_segments" scope=spfile sid='*';
-
alter system reset "_corrupted_rollback_segments" scope=spfile sid='*';
-
shutdown immediate;
-
startup
-
-
-
-
---注意回滾段的命名規範:
-
11g: _SYSSMU1_1189172979$、 _SYSSMU2_1189172979$ 。。。_SYSSMU10_1189172979$
-
10g、9i:_SYSSMU1$、_SYSSMU2$、_SYSSMU3$ 。。。。_SYSSMU10$
-
8i 為rollbackspace 即RBS空間:RBS0、RBS1、RBS2 。。。。RBS6
-
7.3 :RB1、RB2.。。。。RB6
-
-
-
-
-
-
-
*.LOCAL_LISTENER='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523)))'
-
*.LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522))';
-
-
ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523)))';
-
ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522))';
-
-
ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523)))','(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522)))';
-
ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522))','(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523))';
-
-
-
-
ALTER SYSTEM REGISTER;
-
-
-
-
-
-
-
------------ 資料檔案自動擴充套件
-
-
alter database datafile 5 autoextend on next 5M;
-
如果是bigfile可以採用: ALTER TABLESPACE TBS2 AUTOEXTEND ON NEXT 20G;
-
-
修改表空間資料檔案大小為不限制的語句為:
-
alter database datafile '/oradata/orcl/demo01.dbf' autoextend on maxsize unlimited;
-
-
建立表空間資料檔案大小為不限制的語句為:
-
create tablespace demo2 datafile '/oradata/orcl/demo201.dbf' size 10M autoextend on maxsize unlimited;
-
-
----取消已有資料檔案的自動增長方式
-
alter database datafile 'i:\oracle\oradata\dmusertbs01.dbf' autoextend off;
-
-
-
-
------------ 根據檔案號和塊號查詢資料庫物件
-
SELECT tablespace_name,
-
segment_type,
-
owner,
-
segment_name,
-
partition_name
-
FROM dba_extents
-
WHERE file_id = &file_id
-
AND &block_id BETWEEN block_id AND block_id + blocks - 1
-
;
-
-
-
-
SELECT ROWID,
-
dbms_rowid.rowid_object(ROWID) object_id,
-
dbms_rowid.rowid_relative_fno(ROWID) file_id,
-
dbms_rowid.rowid_block_number(ROWID) block_id,
-
d.*
-
FROM scott.SALGRADE d
-
WHERE dbms_rowid.rowid_block_number(ROWID) = 163
-
AND dbms_rowid.rowid_relative_fno(ROWID) = 4;
-
-
-
SELECT DBMS_ROWID.ROWID_CREATE(1,
-
(SELECT DATA_OBJECT_ID
-
FROM DBA_OBJECTS
-
WHERE OBJECT_ID = ROW_WAIT_OBJ#),
-
ROW_WAIT_FILE#,
-
ROW_WAIT_BLOCK#,
-
ROW_WAIT_ROW#),
-
A.ROW_WAIT_OBJ#,
-
A.ROW_WAIT_FILE#,
-
A.ROW_WAIT_BLOCK#,
-
A.ROW_WAIT_ROW#,
-
(SELECT D.OWNER || '.' || D.OBJECT_NAME
-
FROM DBA_OBJECTS D
-
WHERE OBJECT_ID = ROW_WAIT_OBJ#) OBJECT_NAME
-
FROM V$SESSION A
-
WHERE A.ROW_WAIT_OBJ# <> -1;
-
-
-
SELECT * FROM SYS.COM$ A WHERE A.ROWID='AAAACJAABAAAARGAAA';
-
-
-
-
-
-
-
---------------------- 刪除主鍵及主鍵索引
-
-
alter table table_name drop primary key cascade drop index;
-
alter table table_name drop constraint constraint_name cascade drop index;
-
-
-
--------------------------------------------------------- awr
-
-
-
----- 生成awr
-
select * from table(dbms_workload_repository.awr_report_html(3424884828,1,1161,1165));
-
@$ORACLE_HOME/rdbms/admin/awrrpt.sql;
-
-
-
----檢視
-
-
-
select * from DBA_HIST_WR_CONTROL;
-
select * from DBA_HIST_SNAPSHOT;
-
select * from DBA_HIST_ACTIVE_SESS_HISTORY;
-
select * from DBA_HIST_ASH_SNAPSHOT;
-
-
select * from DBA_HIST_SEG_STAT;
-
select * from DBA_HIST_SQLBIND;
-
select * from DBA_HIST_SQLSTAT;
-
select * from DBA_HIST_SQLTEXT;
-
select * from DBA_HIST_SQL_BIND_METADATA;
-
select * from DBA_HIST_SQL_PLAN;
-
-
-
-
--------- sql部分
-
select &begin_snap || '~' || &end_snap snap_id_range,
-
(SELECT round(sum(db_time) / 1000000 / 60, 2) db_time_m
-
FROM (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time
-
from dba_hist_sys_time_model a, dba_hist_snapshot b
-
where a.snap_id = b.snap_id
-
and a.dbid = b.dbid
-
and a.instance_number = b.instance_number
-
and a.stat_name = 'DB time'
-
and a.snap_id between &begin_snap and &end_snap)
-
where db_time IS NOT NULL) "db_time(m)",
-
round(nvl((sqt.elap / 1000000), to_number(null)), 2) "Elapsed Time (s)",
-
round(nvl((sqt.cput / 1000000), to_number(null)), 2) "CPU Time (s)",
-
round(nvl((sqt.iowait_delta / 1000000), to_number(null)), 2) "User I/O Time (s)",
-
round(nvl((sqt.buffer_gets_delta), to_number(null)), 2) "Buffer Gets",
-
round(nvl((sqt.disk_reads_delta), to_number(null)), 2) "Physical Reads",
-
round(nvl((sqt.rows_processed_delta), to_number(null)), 2) "Rows Processed",
-
round(nvl((sqt.parse_calls_delta), to_number(null)), 2) "Parse Calls",
-
sqt.exec executions,
-
round(decode(sqt.exec,
-
0,
-
to_number(null),
-
(sqt.elap / sqt.exec / 1000000)),
-
2) "Elapsed Time per Exec (s)",
-
round(decode(sqt.exec,
-
0,
-
to_number(null),
-
(sqt.cput / sqt.exec / 1000000)),
-
2) "CPU per Exec (s)",
-
round(decode(sqt.exec,
-
0,
-
to_number(null),
-
(sqt.iowait_delta / sqt.exec / 1000000)),
-
2) "UIO per Exec (s)",
-
round(sqt.cput * 100 / sqt.elap, 2) "%CPU",
-
round(sqt.iowait_delta * 100 / sqt.elap, 2) "%IO",
-
round(sqt.elap * 100 /
-
(SELECT sum(db_time)
-
FROM (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time
-
from dba_hist_sys_time_model a, dba_hist_snapshot b
-
where a.snap_id = b.snap_id
-
and a.dbid = b.dbid
-
and a.instance_number = b.instance_number
-
and a.stat_name = 'DB time'
-
and a.snap_id between &begin_snap and &end_snap)
-
where db_time IS NOT NULL),
-
2) "elapsed/dbtime",
-
sqt.sql_id,
-
parsing_schema_name,
-
(decode(sqt.module, null, null, sqt.module)) module,
-
nvl((select dbms_lob.substr(st.sql_text, 2000, 1)
-
from dba_hist_sqltext st
-
WHERE st.sql_id = sqt.sql_id
-
and st.dbid = sqt.dbid),
-
(' ** SQL Text Not Available ** ')) sql_text
-
from (select sql_id,
-
a.dbid,
-
a.parsing_schema_name,
-
max(module || '--' || a.action) module,
-
sum(elapsed_time_delta) elap,
-
sum(cpu_time_delta) cput,
-
sum(executions_delta) exec,
-
SUM(a.iowait_delta) iowait_delta,
-
sum(a.buffer_gets_delta) buffer_gets_delta,
-
sum(a.disk_reads_delta) disk_reads_delta,
-
sum(a.rows_processed_delta) rows_processed_delta,
-
sum(a.parse_calls_delta) parse_calls_delta
-
from dba_hist_sqlstat a
-
where &begin_snap < snap_id
-
and snap_id <= &end_snap
-
group by sql_id, parsing_schema_name, a.dbid) sqt
-
order by nvl(sqt.elap, -1) desc, sqt.sql_id
-
;
-
-
--------- 資訊
-
select s.snap_date,
-
snap_time_range,
-
t.snap_id + 1 snap_id,
-
decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME",
-
startup_time,
-
to_char(round(s.seconds / 60, 2)) "elapse(min)",
-
round(t.db_time / 1000000 / 60, 2) "DB time(min)",
-
s.redosize redo,
-
round(s.redosize / s.seconds, 2) "redo/s",
-
round(s.redosize / s.transactions, 2) "redo/t",
-
s.logicalreads logical,
-
round(s.logicalreads / s.seconds, 2) "logical/s",
-
round(s.logicalreads / s.transactions, 2) "logical/t",
-
physicalreads physical,
-
round(s.physicalreads / s.seconds, 2) "phy/s",
-
round(s.physicalreads / s.transactions, 2) "phy/t",
-
s.executes execs,
-
round(s.executes / s.seconds, 2) "execs/s",
-
round(s.executes / s.transactions, 2) "execs/t",
-
s.parse,
-
round(s.parse / s.seconds, 2) "parse/s",
-
round(s.parse / s.transactions, 2) "parse/t",
-
s.hardparse,
-
round(s.hardparse / s.seconds, 2) "hardparse/s",
-
round(s.hardparse / s.transactions, 2) "hardparse/t",
-
s.transactions trans,
-
round(s.transactions / s.seconds, 2) "trans/s"
-
from (select curr_redo - last_redo redosize,
-
curr_logicalreads - last_logicalreads logicalreads,
-
curr_physicalreads - last_physicalreads physicalreads,
-
curr_executes - last_executes executes,
-
curr_parse - last_parse parse,
-
curr_hardparse - last_hardparse hardparse,
-
curr_transactions - last_transactions transactions,
-
round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds,
-
to_char(currtime, 'yyyy-mm-dd') snap_date,
-
to_char(currtime, 'hh24:mi') currtime,
-
to_char(lasttime, 'YYYY-MM-DD HH24:MI') || '~' ||
-
to_char(currtime, 'YYYY-MM-DD HH24:MI') snap_time_range,
-
currsnap_id endsnap_id,
-
to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time
-
from (select a.redo last_redo,
-
a.logicalreads last_logicalreads,
-
a.physicalreads last_physicalreads,
-
a.executes last_executes,
-
a.parse last_parse,
-
a.hardparse last_hardparse,
-
a.transactions last_transactions,
-
lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo,
-
lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads,
-
lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads,
-
lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes,
-
lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse,
-
lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse,
-
lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions,
-
b.end_interval_time lasttime,
-
lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime,
-
lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id,
-
b.startup_time
-
from (select snap_id,
-
dbid,
-
instance_number,
-
sum(decode(stat_name, 'redo size', value, 0)) redo,
-
sum(decode(stat_name,
-
'session logical reads',
-
value,
-
0)) logicalreads,
-
sum(decode(stat_name,
-
'physical reads',
-
value,
-
0)) physicalreads,
-
sum(decode(stat_name, 'execute count', value, 0)) executes,
-
sum(decode(stat_name,
-
'parse count (total)',
-
value,
-
0)) parse,
-
sum(decode(stat_name,
-
'parse count (hard)',
-
value,
-
0)) hardparse,
-
sum(decode(stat_name,
-
'user rollbacks',
-
value,
-
'user commits',
-
value,
-
0)) transactions
-
from dba_hist_sysstat
-
where stat_name in
-
('redo size',
-
'session logical reads',
-
'physical reads',
-
'execute count',
-
'user rollbacks',
-
'user commits',
-
'parse count (hard)',
-
'parse count (total)')
-
group by snap_id, dbid, instance_number) a,
-
dba_hist_snapshot b
-
where a.snap_id = b.snap_id
-
and a.dbid = b.dbid
-
and a.instance_number = b.instance_number
-
order by end_interval_time)) s,
-
(select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time,
-
lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id,
-
b.snap_id
-
from dba_hist_sys_time_model a, dba_hist_snapshot b
-
where a.snap_id = b.snap_id
-
and a.dbid = b.dbid
-
and a.instance_number = b.instance_number
-
and a.stat_name = 'DB time') t
-
where s.endsnap_id = t.endsnap_id
-
order by s.snap_date desc, snap_id desc, time asc;
-
-
-
----主機資訊
-
SELECT s.snap_id,
-
DB_NAME,
-
s.dbid,
-
INSTANCE_NAME,
-
s.instance_number,
-
s.startup_time,
-
Version Release,
-
PARALLEL RAC,
-
HOST_NAME,
-
di.platform_name,
-
v.cpus CPUS,
-
v.cores,
-
v.sockets,
-
v.Memory "Memory (GB)"
-
FROM DBA_HIST_DATABASE_INSTANCE di,
-
DBA_HIST_SNAPSHOT s,
-
(SELECT snap_id,
-
dbid,
-
instance_number,
-
SUM(CPUs) CPUs,
-
SUM(Cores) Cores,
-
SUM(Sockets) Sockets,
-
SUM(Memory) Memory
-
FROM (SELECT o.snap_id,
-
o.dbid,
-
o.instance_number,
-
decode(o.stat_name, 'NUM_CPUS', o.value) CPUs,
-
decode(o.stat_name, 'NUM_CPU_CORES', o.value) Cores,
-
decode(o.stat_name, 'NUM_CPU_SOCKETS', o.value) Sockets,
-
decode(o.stat_name,
-
'PHYSICAL_MEMORY_BYTES',
-
trunc(o.value / 1024 / 1024 / 1024, 2)) Memory
-
FROM dba_hist_osstat o
-
WHERE o.stat_name IN
-
('NUM_CPUS',
-
'NUM_CPU_CORES',
-
'NUM_CPU_SOCKETS',
-
'PHYSICAL_MEMORY_BYTES'))
-
GROUP BY snap_id,
-
dbid,
-
instance_number) v
-
WHERE s.instance_number = di.instance_number
-
AND s.startup_time = di.startup_time
-
AND s.dbid = di.dbid
-
AND s.snap_id = v.snap_id
-
AND s.dbid = s.dbid
-
AND s.instance_number = v.instance_number;
-
-
-
-
-
------------------------------------------------------------------------------------------ 臨時表
-
基於事務: create global temporary table lhr.cgtt_temp_sw on commit delete rows as select * from dba_objects;
-
建立索引:create index ind_cgtt_object_ID on cgtt_temp_sw(object_ID);
-
-
基於會話: create global temporary table lhr.cgtt_temp_hh on commit preserve rows as select * from dba_objects;
-
建立索引:需重開會話: create index ind_cgtt_object_ID2 on cgtt_temp_hh(object_ID);
-
-
-
select * from VW_TEMP_OBJECT_LHR;
-
-
Select se.username,
-
se.sid,
-
su.extents,
-
(su.blocks *
-
to_number((select rtrim(value)
-
from v$parameter p
-
WHERE p.NAME = 'db_block_size'))) / 1024 / 1024 as Size_m,
-
tablespace,
-
segtype,
-
(SELECT a.SQL_TEXT
-
FROM v$sql a
-
WHERE a.SQL_ID = su.SQL_ID
-
and rownum = 1) SQL_TEXT
-
from v$sort_usage su, v$session se
-
where su.session_addr = se.saddr
-
order by se.username, se.sid;
-
-
-------------------------------------- catalog庫
-
SQL> create tablespace rman_ts datafile '/lhrdata/u01/app/oracle/oradata/orcllinux/rman.dbf' size 1G;
-
表空間已建立。
-
SQL> create user rc identified by lhr default tablespace rman_ts quota unlimited on rman_ts;
-
使用者已建立。
-
SQL> grant recovery_catalog_owner to rc;---包含了connect的角色許可權
-
授權成功。
-
SQL> grant RESOURCE to rc;
-
授權成功。
-
SQL> HOST
-
[oracle@lhr_linux ~]$ rman catalog rc/lhr
-
恢復管理器: Release 11.2.0.1.0 - Production on 星期四 4月 10 15:08:22 2014
-
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
-
連線到恢復目錄資料庫
-
RMAN> create catalog tablespace rman_ts;
-
恢復目錄已建立
-
RMAN> connect target /
-
連線到目標資料庫: ORCLLINU (DBID=534927627)
-
RMAN> register database;
-
註冊在恢復目錄中的資料庫
-
正在啟動全部恢復目錄的 resync
-
完成全部 resync
-
RMAN> exit
-
-
-
---------- 使用者profile
-
alter user lhr profile default;
-
alter system set resource_limit=true;
-
alter profile default limit PASSWORD_LIFE_TIME UNLIMITED;
-
alter profile default limit FAILED_LOGIN_ATTEMPTS UNLIMITED;
-
-
-
/oracle/app/oraInventory/ContentsXML/inventory.xml
-
oracle執行:$ORACLE_HOME/oui/bin/attachHome.sh
-
-
-
-------------------------------------------- 解除安裝軟體 GRID
-
kill -9 `ps -ef|grep d.bin| grep -v grep | awk '{print $2}'`
-
-
-
-
解除安裝GRID軟體,grid使用者執行:$ORACLE_HOME/deinstall/deinstall
-
解除安裝ORACLE軟體,oracle使用者執行:$ORACLE_HOME/deinstall/deinstall
-
-
在Linux下手工解除安裝RAC的步驟:
-
① rm -rf /etc/ora*
-
② rm -rf /var/tmp/.oracle
-
③ 修改/etc/inittab刪除以下三行
-
h1:2:respawn:/etc/init.evmd run >/dev/null 2>&1 </dev/null
-
h2:2:respawn:/etc/init.cssd fatal >/dev/null 2>&1 </dev/null
-
h3:2:respawn:/etc/init.crsd run >/dev/null 2>&1 </dev/null
-
④ rm -rf /tmp/*
-
⑤ rm -rf /u01/app/grid/* ---刪除GRID目錄
-
⑥ rm -rf /u01/app/oracle/* --刪除ORACLE目錄
-
⑦ rm -rf /u01/app/oraInventory/*
-
⑧ 清除OCR、Voting及資料庫使用過的磁碟
-
dd if=/dev/zero of=/dev/raw/raw1 bs=104857600 count=1
-
dd if=/dev/zero of=/dev/raw/raw2 bs=104857600 count=1
-
最後,重啟2個節點*/
-
-
-------重建路徑
-
mkdir -p /u01/app/oracle
-
mkdir -p /u01/app/grid
-
mkdir -p /u01/app/12.1.0/grid
-
mkdir -p /u01/app/oracle/product/12.1.0/dbhome_1
-
chown -R grid:oinstall /u01/app/grid
-
chown -R grid:oinstall /u01/app/12.1.0
-
chown -R oracle:oinstall /u01/app/oracle
-
chmod -R 775 /u01
-
-
mkdir -p /u01/app/oraInventory
-
chown -R grid:oinstall /u01/app/oraInventory
-
chmod -R 775 /u01/app/oraInventory
-
-
-
在Windows下手工解除安裝RAC的步驟:
-
① 開始->設定->控制皮膚->管理工具->服務,或執行services.msc開啟服務,停止所有Oracle服務
-
② 刪除Oracle和GRID的安裝目錄
-
③ 刪除C:\Program Files\Oracle目錄
-
④ 刪除C:\windows\temp和C:\temp以及C:\Users\Administrator\Oracle下的檔案
-
⑤ 執行regedit,開啟登錄檔編輯器,選擇HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE,刪除該項
-
⑥ 執行regedit,開啟登錄檔編輯器,選擇HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services,滾動這個列表,刪除所有Oracle開頭的項
-
⑦ 執行regedit,開啟登錄檔編輯器,選擇HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application,刪除所有Oracle入口
-
⑧ 開始->設定->控制皮膚->系統->高階->環境變數,刪除環境變數CLASSPATH和PATH中有關Oracle的設定
-
⑨ 從桌面上、STARTUP(啟動)組、程式選單中,刪除所有有關Oracle的組和圖示
-
⑩ 重新啟動計算機,重起後才能完全刪除Oracle所在目錄
-
若個別檔案不能刪除,則說明該檔案與某個Windows服務相關聯,可以先把相關聯的服務停止後再刪除。
-
-
-
-
-- 加入常用命令
-
vi /etc/profile
-
export GRID_HOME=/u01/app/12.1.0/grid
-
export PATH=$PATH:$GRID_HOME/bin
-
-
-
---------------- 重新執行root.sh
-
---kill -9 `ps -ef|grep d.bin| grep -v grep | awk '{print $2}'`
-
---$ORACLE_HOME 為 GRID_HOME的路徑,執行之前最好先手動把資料庫資源關閉
-
日誌地址:$ORACLE_HOME/cfgtoollogs/crsconfig/
-
重置的日誌檔案:hadelete.log
-
root.sh指令碼日誌:rootcrs_rac2.log
-
-
-
--------------① 指令碼方式
-
---執行失敗,重新執行root.sh指令碼
-
$ORACLE_HOME/crs/install/crsconfig_params
-
$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose
-
--$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose -lastnode -keepdg
-
dd if=/dev/zero of=/dev/rhdiskN bs=1024k count=1024
-
lquerypv -h /dev/rhdisk5
-
$ORACLE_HOME/root.sh
-
-
-
$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose
-
--$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose -lastnode -keepdg
-
$ORACLE_HOME/root.sh
-
-
---$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose執行完成之後需要刪除如下的檔案
-
ls -l $ORACLE_BASE/Clusterware/ckptGridHA* */
-
find $ORACLE_HOME/gpnp/* -type f */
-
find $ORACLE_HOME/gpnp/* -type f -exec rm -rf {} \; */
-
-
-
-
-
--------------② 介面方式
-
---------------刪除兩節點crsconfig_params中的DATA1和磁碟 介面方式
-
$ORACLE_HOME/crs/install/crsconfig_params
-
ASM_DISK_GROUP=DATA1
-
ASM_DISKS=/dev/rhdisk5
-
--root
-
$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose
-
-- GRID
-
export DISPLAY=22.188.216.132:0.0
-
$ORACLE_HOME/crs/config/config.sh
-
-
-
-
-
-
CRS-4124: Oracle High Availability Services startup failed. -- 報錯
-
CRS-4000: Command Start failed, or completed with errors.
-
ohasd failed to start: Inappropriate ioctl for device
-
ohasd failed to start: Inappropriate ioctl for device at /u01/app/11.2.0/grid/crs/install/roothas.pl line 296.
-
/bin/dd if=/var/tmp/.oracle/npohasd of=/dev/null bs=1024 count=1
-
-
-
/nfs/software/db/install/clone/sh/11g/grid/runcluvfy.sh stage -pre crsinst -n ZFLHRDB1,ZFLHRDB2 -verbose -fixup
-
-
$ORACLE_HOME/bin/cluvfy stage -pre crsinst -n all -verbose -fixup
-
-
-
find . -name runcluvfy.sh
-
-
-
---GRID_HOME許可權修復
-
方法1:11gR2可以deconfig crs的配置,然後重新跑root.sh即可。重新跑root.sh指令碼並不影響資料庫,所以無需擔心(個人推薦的一種方式).
-
$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose
-
$ORACLE_HOME/root.sh
-
-
方法2:根據Mos文件提供的建議通過 $GRID_HOME/crs/install/rootcrs.pl -init 或 roothas.pl -init進行解決. rootcrs.pl –init是在PSU>11.2.0.3.6下執行的,如果PSU<11.2.0.3.6可以執行如下兩條命令來實現同樣的效果
-
<GRID_HOME>/crs/install/rootcrs.pl -unlock
-
<GRID_HOME>/crs/install/rootcrs.pl -patch
-
-
For 11.2:
-
For clustered Grid Infrastructure, as root user
-
# cd <GRID_HOME>/crs/install/
-
# ./rootcrs.pl -init
-
For Standalone Grid Infrastructure, as root user
-
# cd <GRID_HOME>/crs/install/
-
# ./roothas.pl -init
-
-
For 12c:
-
For clustered Grid Infrastructure, as root user
-
# cd <GRID_HOME>/crs/install/
-
# ./rootcrs.sh -init
-
For Standalone Grid Infrastructure, as root user
-
# cd <GRID_HOME>/crs/install/
-
# ./roothas.sh -init
-
-
-
-
-
-
-
-
-
-
---檢視psu
-
/nfs/software/db/install/chk/chkora.sh
-
opatch lsinventory -bugs_fixed | grep 'PSU'
-
opatch lsinv
-
[ZFCASSDB1:grid]:/home/grid>opatch lspatches
-
13343438;Database Patch Set Update : 11.2.0.3.1 (13343438)
-
13348650;Grid Infrastructure Patch Set Update : 11.2.0.3.1 (13348650)
-
[ZFCASSDB1:grid]:/home/grid>
-
-
col action_time for a30
-
col action for a10
-
col namespace for a10
-
col version for a10
-
col bundle_series for a10
-
col comments for a30
-
-
SELECT to_char(action_time, 'YYYY-MM-DD HH24:MI:SS') action_time,
-
action,
-
namespace,
-
version,
-
id,
-
bundle_series,
-
comments
-
FROM dba_registry_history D;
-
-
select action,comments from registry$history;
-
-
-
---grid和oracle分別回滾
-
$ORACLE_HOME/OPatch/opatch rollback -local -id 13348650 -oh /oracle/app/oracle/product/11.2.0/db
-
-
-
-
----------------------------- OCR備份
-
--邏輯備份恢復
-
ocrconfig -export /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/export_asm.bak
-
ocrconfig -import /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/export_asm_lhr.bak
-
crsctl stop crs
-
crsctl start crs -excl -nocrs
-
ocrconfig -import /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/export_asm_lhr.bak
-
crsctl start crs
-
-
-
-
--物理備份恢復
-
ocrconfig -manualbackup
-
ocrconfig -showbackup
-
-
cluvfy comp ocr -n all -verbose
-
cluvfy comp olr -verbose
-
-
-
crsctl stop crs -f
-
crsctl start crs -excl -nocrs
-
crsctl stop resource ora.crsd -init
-
ocrconfig -restore /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/backup_20160701_152358.ocr
-
crsctl stop has -f
-
crsctl start crs
-
-
-
crsctl query css votedisk
-
-
-
--------dd備份恢復OCR 注:11g不推薦使用dd來進行備份恢復,盤頭一般是前4K
-
--備份表決磁碟:
-
dd if=/dev/raw/raw3 of=/tmp/votedisk_lhr.bak bs=1024k count=4
-
--恢復表決磁碟:
-
dd if=/tmp/votedisk_lhr.bak of=/dev/raw/raw3 bs=1024k count=4
-
-
-
----------kfed修復磁碟頭
-
dd if=/dev/rhdisk2 of=/asm_rhdisk2_dd.bak bs=1024 count=4
-
dd if=/dev/zero of=/dev/rhdisk2 bs=1024 count=4
-
kfed repair /dev/rhdisk2
-
-
----md_backup修復磁碟頭
-
asmcmd md_backup /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/asm_md_backup.bak
-
asmcmd md_restore /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/asm_md_backup.bak
-
-
-
dd if=/dev/rhdisk2 of=/asm_rhdisk2_dd.bak bs=1024k count=4
-
dd if=/dev/zero of=/dev/rhdisk2 bs=1024k count=4
-
crsctl stop has -f
-
crsctl start has
-
ASMCMD [+] > startup force nomount;
-
ASMCMD [+] > md_restore /asm_rhdisk2_dd.bak
-
-
-
ASMCMD [+] > md_backup /rman/asm_md.bak
-
dd if=/dev/zero of=/dev/rhdisk2 bs=1024 count=4
-
crsctl stop has -f
-
crsctl start has
-
ASMCMD [+] > startup force nomount;
-
ASMCMD [+] > md_restore /rman/asm_md.bak
-
-
-
-------- OLR的備份恢復
-
<GI_HOME>/bin/ocrconfig -local -manualbackup
-
<GI_HOME>/bin/ocrconfig -local -showbackup
-
-
ps -ef| grep ohasd.bin
-
<GI_HOME>/bin/crsctl stop crs -f <========= for GI Cluster
-
<GI_HOME>/bin/crsctl stop has <========= for GI Standalone
-
<GI_HOME>/bin/ocrconfig -local -restore <olr-backup>
-
<GI_HOME>/bin/crsctl start crs <========= for GI Cluster
-
<GI_HOME>/bin/crsctl start has <========= for GI Standalone, this must be done as grid user.
-
-
-
-
-
-
vi crsstat_lhr.sh
-
awk 'BEGIN {printf "%-26s %-26s %-10s %-10s %-10s \n","Name ","Type ","Target ","State ","Host "; printf "%-30s %-26s %-10s %-10s %-10s\n","----------------------------------------","--------------------------","----------", "---------","----------";}'
-
crs_stat | awk 'BEGIN { FS="=| ";state = 0;} $1~/NAME/ {appname = $2; state=1}; state == 0 {next;} $1~/TYPE/ && state == 1 {apptype = $2; state=2;} $1~/TARGET/ && state == 2 {apptarget = $2; state=3;} $1~/STATE/ && state == 3 {appstate = $2; apphost = $4; state=4;} state == 4 {printf "%-40s %-26s %-10s %-10s %-10s\n", appname,apptype,apptarget,appstate,apphost; state=0;}'
-
-
-
-
-------------------------------------------------------------- OEM
-
--重建:
-
emca -config dbcontrol db -repos recreate
-
emca -config dbcontrol db -repos recreate -cluster
-
-
-
http://192.168.59.130:1158/em/
-
https://192.168.59.128:1158/em/
-
-
日誌:
-
$ORACLE_HOME/$hostname_$oracle_sid/sysman/log
-
-
4.安裝過程中出現問題的時候認真檢視日誌,安裝日誌路徑:$ORACLE_HOME/cfgtoollogs\emca\
-
5.OEM執行日誌:$ORACLE_HOME/$hostname_$oracle_sid/sysman/log
-
6.建議重新建立,在oracle使用者下:
-
單機: emca -config dbcontrol db -repos recreate
-
叢集: emca -config dbcontrol db -repos recreate -cluster
-
若是叢集環境,則在建立之前先在grid使用者下執行如下程式碼:
-
-----sqlplus / as sysasm ASM例項 GRID 使用者下執行
-
SYS@+ASM1> create user asmsnmp identified by xxx;
-
SYS@+ASM1> grant sysdba to asmsnmp;
-
SYS@+ASM1> alter user asmsnmp identified by xxx;
-
SYS@+ASM1> alter system set remote_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ZFZHLHRDB-scan)(PORT=1521))))' sid='*';
-
SYS@+ASM1> alter system register;
-
7.啟動和關閉維護命令,oracle使用者下:export ORACLE_UNQNAME=$DB_UNIQUE_NAME ,資料庫唯一名,程式:ps -ef| grep em
-
啟動: emctl stop dbconsole
-
關閉: emctl stop dbconsole
-
執行狀態:emctl status dbconsole
-
-
-
-------------------------------------------- dbca 靜默建庫 windows 和 linux 命令一樣
-
-
------歸檔
-
vi $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc
-
-
修改為:<archiveLogMode>true</archiveLogMode>
-
-
[oracle@rhel6_lhr ~]$ strings $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc | grep -i arch
-
<archiveLogMode>false</archiveLogMode>
-
-
-
-
-
-
----靜默安裝資料庫日誌路徑:
-
11g:$ORACLE_BASE/cfgtoollogs/dbca
-
10g:$ORACLE_HOME/cfgtoollogs/dbca
-
-
-
---dbca -silent整理 \ 後不能包含空格
-
dbca -silent -deleteDatabase -sourceDB mydb
-
dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
-
-gdbname mydb -sid mydb \
-
-sysPassword oracle -systemPassword oracle \
-
-datafileDestination 'DATA/' -recoveryAreaDestination 'DATA/' \
-
-redoLogFileSize 50 \
-
-storageType ASM -asmsnmpPassword oracle -diskGroupName 'DATA' \
-
-characterset AL32UTF8 -nationalCharacterSet AL16UTF16 \
-
-sampleSchema true \
-
-automaticMemoryManagement true -totalMemory 2048 \
-
-databaseType OLTP \
-
-emConfiguration NONE \
-
-nodeinfo ZFZHLHRDB1,ZFZHLHRDB2
-
-
dbca -silent -deleteDatabase -sourceDB mydb
-
dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
-
-gdbname mydb -sid mydb \
-
-sysPassword oracle -systemPassword oracle \
-
-datafileDestination '/u05/app/oracle' -recoveryAreaDestination '/u05/app/oracle' \
-
-storageType FS \
-
-characterset AL32UTF8 -nationalCharacterSet AL16UTF16 \
-
-sampleSchema false \
-
-memoryPercentage 10 \
-
-databaseType OLTP \
-
-emConfiguration NONE
-
-
-
---10g
-
dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
-
-gdbname mydg -sid mydg \
-
-sysPassword lhr -systemPassword lhr \
-
-datafileDestination '/cds/oradata' -recoveryAreaDestination '/cds/oradata' \
-
-storageType FS \
-
-characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \
-
-sampleSchema true \
-
-memoryPercentage 20 \
-
-databaseType OLTP \
-
-emConfiguration NONE
-
-
-
--12C
-
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname oradb.example.com -sid oradb -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration NONE
-
dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
-
-gdbname lhrdb -sid lhrdb \
-
-createAsContainerDatabase false \
-
-sysPassword lhr -systemPassword lhr -serviceUserPassword lhr \
-
-datafileDestination '/u01/app/oracle' -recoveryAreaDestination '/u01/app/oracle' \
-
-storageType FS \
-
-characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \
-
-sampleSchema true \
-
-memoryPercentage 30 \
-
-databaseType OLTP \
-
-emConfiguration NONE
-
-
--12C rac
-
dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
-
-gdbname lhrrac -sid lhrrac \
-
-createAsContainerDatabase false \
-
-sysPassword lhr -systemPassword lhr -serviceUserPassword lhr \
-
-datafileDestination 'DATA/' -recoveryAreaDestination 'FRA/' \
-
-storageType ASM -asmsnmpPassword oracle -diskGroupName 'DATA' \
-
-characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \
-
-sampleSchema true \
-
-memoryPercentage 30 \
-
-databaseType OLTP \
-
-emConfiguration NONE \
-
-nodeinfo raclhr-12cR1-N1,raclhr-12cR1-N2
-
-
-
-
--11g
-
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ora11g -sid ora11g -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata -redoLogFileSize 50 -recoveryAreaDestination /u01/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -totalMemory 200 -databaseType OLTP -emConfiguration NONE -automaticMemoryManagement true
-
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ora11g -sid ora11g -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata -characterSet AL32UTF8
-
-
----ASM 儲存 單例項
-
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ora11g -sid ora11g -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination 'DATA/' -redoLogFileSize 50 -recoveryAreaDestination 'TEST/' -storageType ASM -asmsnmpPassword lhr -diskGroupName 'DATA' -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -totalMemory 200 -databaseType OLTP -emConfiguration NONE -automaticMemoryManagement true
-
-
-
---預設
-
--- 閃回恢復區 建立
-
--storageType FS
-
--sampleSchema 預設建立
-
--em 預設不建立
-
-
-
-
--10g
-
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ora11g -sid ora11g -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata -recoveryAreaDestination /u01/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -databaseType OLTP -emConfiguration NONE
-
-
-
-
-
----------------根據模板檔案進行安裝
-
dbca -silent -responseFile $ORACLE_HOME/assistants/dbca/dbca.rsp
-
dbca -silent -cloneTemplate -responseFile $ORACLE_HOME/assistants/dbca/dbca.rsp -gdbName orcltest -sid orcltest -datafileDestination /u01/app/oracle/oradata
-
-
-
-
-------------------- 根據資料庫生成不帶資料檔案的模板
-
dbca -silent -createTemplateFromDB -sourceDB 192.168.59.130:1521:ora10g -templateName dbtemplate_ora10g2_lhr -sysDBAUserName sys -sysDBAPassword lhr
-
-
-------------------- 根據資料庫生成帶資料檔案的模板
-
dbca -silent -createCloneTemplate -sourceDB orcltest -sysDBAUserName lhr -sysDBAPassword lhr -templateName dbtemplate_orcltest_lhr -datafileJarLocation
-
-------------------- 利用帶資料檔案的模板生成克隆資料庫
-
dbca -silent -createDatabase -templateName dbtemplate_orcltest_lhr.dbc -gdbname orcl22 -sid orcl22 -sysPassword lhr -systemPassword lhr -datafileJarLocation $ORACLE_HOME/assistants/dbca/templates -datafileDestination /u01/app/oracle/oradata -responseFile NO_VALUE -characterset ZHS16GBK
-
-
-------------------- 利用不帶資料檔案的模板生成新的資料庫 慢,不推薦
-
----dbca -silent -createDatabase -templateName New_Database.dbt -gdbname test33 -sid test33 -datafileDestination /u01/app/oracle/oradata -responseFile NO_VALUE -characterset ZHS16GBK
-
-
-
-
---------------- 刪除資料庫
-
dbca -silent -deleteDatabase -sourceDB orclbb -sysDBAUserName sys -sysDBAPassword lhr
-
-
-
-
------------- linux、AIX下 rac 資料庫的建立
-
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname jmrac -sid jmrac -sysPassword lhr -systemPassword lhr -datafileDestination 'DATA/' -redoLogFileSize 50 -recoveryAreaDestination 'ARCH/' -storageType ASM -asmsnmpPassword lhr -diskGroupName 'DATA' -responseFile NO_VALUE -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -automaticMemoryManagement true -totalMemory 500 -nodeinfo node1,node2
-
-
----windows下建立rac庫,注意引數 diskGroupName 為 DATA ,不能帶有引號
-
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname winrac -sid winrac -sysPassword lhr -systemPassword lhr -datafileDestination 'DATA/' -redoLogFileSize 50 -recoveryAreaDestination 'FRA/' -storageType ASM -asmsnmpPassword lhr -diskGroupName DATA -responseFile NO_VALUE -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -automaticMemoryManagement true -totalMemory 300 -nodeinfo rac1,rac2
-
-
-
----- 或者:
-
[oracle@node1 dbca]$ more $ORACLE_HOME/assistants/dbca/dbca_rac.rsp
-
[GENERAL]
-
RESPONSEFILE_VERSION = "11.2.0"
-
OPERATION_TYPE = "createDatabase"
-
[CREATEDATABASE]
-
GDBNAME = "myrac"
-
SID = "myrac"
-
NODELIST=node1,node2
-
TEMPLATENAME = "General_Purpose.dbc"
-
SYSPASSWORD = "lhr"
-
SYSTEMPASSWORD = "lhr"
-
SYSMANPASSWORD = "lhr"
-
DBSNMPPASSWORD = "lhr"
-
STORAGETYPE=ASM
-
DISKGROUPNAME=DATA
-
ASMSNMP_PASSWORD="lhr"
-
RECOVERYGROUPNAME=ARCH
-
CHARACTERSET = "ZHS16GBK"
-
NATIONALCHARACTERSET= "UTF8"
-
[oracle@node1 dbca]$ dbca -silent -responseFile $ORACLE_HOME/assistants/dbca/dbca_rac.rsp
-
-
-
-
set line 9999
-
col HOST_NAME format a10
-
select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS,ACTIVE_STATE,INSTANCE_ROLE,DATABASE_STATUS from gv$INSTANCE;
-
select INST_ID,name , open_mode, log_mode,force_logging from gv$database;
-
-
-
------------------------------------------------------------------- 靜默安裝
-
---------- 單例項資料庫安裝
-
vi /tmp/database/response/db_install.rsp
-
ORACLE_HOSTNAME=192.168.59.129
-
UNIX_GROUP_NAME=oinstall
-
INVENTORY_LOCATION=/u02/app/oracle/oraInventory
-
SELECTED_LANGUAGES=en,zh_CN
-
ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1
-
ORACLE_BASE=/u02/app/oracle
-
oracle.install.db.InstallEdition=EE
-
oracle.install.db.EEOptionsSelection=false
-
oracle.install.db.DBA_GROUP=dba
-
oracle.install.db.OPER_GROUP=oper
-
oracle.install.db.isRACOneInstall=false
-
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
-
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
-
oracle.installer.autoupdates.option=SKIP_UPDATES
-
-
----軟體安裝
-
./runInstaller -silent -noconfig -responseFile /tmp/database/response/db_install.rsp -ignoreSysPrereqs -ignorePrereq
-
-
-
----靜默建立asm例項
-
/u01/app/11.2.0/grid/bin/asmca -silent -configureASM -sysAsmPassword lhr -asmsnmpPassword lhr -diskGroupName OCR -diskList /dev/rhdisk20 -redundancy EXTERNAL
-
-
-
-
----監聽配置
-
$ORACLE_HOME/bin/netca /silent /responsefile /u01/database/netca.rsp
-
netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp
-
-
$ORACLE_HOME/bin/netca /silent /responsefile /u01/database/netca.rsp
-
netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp
-
netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp -instype custom -listener LISTENER_LHR
-
crsctl delete resource ora.LISTENER.lsnr -f
-
rm $ORACLE_HOME/network/admin/listener.ora
-
-
-
-
---------------------------------- 單例項grid安裝
-
/softtmp/grid/runInstaller -silent -force -noconfig -IgnoreSysPreReqs -ignorePrereq -showProgress \
-
ORACLE_HOSTNAME=ZFFR4CB2101 \
-
INVENTORY_LOCATION=/u01/app/oraInventory \
-
SELECTED_LANGUAGES=en \
-
oracle.install.option=CRS_SWONLY \
-
ORACLE_BASE=/u01/app/grid \
-
ORACLE_HOME=/u01/app/11.2.0/grid \
-
oracle.install.asm.OSDBA=asmdba \
-
oracle.install.asm.OSOPER=asmoper \
-
oracle.install.asm.OSASM=asmadmin \
-
oracle.install.crs.config.storageOption=ASM_STORAGE \
-
oracle.install.crs.config.sharedFileSystemStorage.votingDiskRedundancy=EXTERNAL \
-
oracle.install.crs.config.sharedFileSystemStorage.ocrRedundancy=EXTERNAL \
-
oracle.install.crs.config.useIPMI=false \
-
oracle.install.asm.SYSASMPassword=lhr \
-
oracle.install.asm.diskGroup.name=OCR \
-
oracle.install.asm.diskGroup.redundancy=EXTERNAL \
-
oracle.install.asm.diskGroup.disks=/dev/rhdisk20 \
-
oracle.install.asm.monitorPassword=lhr \
-
oracle.installer.autoupdates.option=SKIP_UPDATES
-
-
-
---------------------------------- 單例項db安裝
-
/softtmp/database/runInstaller -silent -force -noconfig -IgnoreSysPreReqs -ignorePrereq -showProgress \
-
oracle.install.option=INSTALL_DB_SWONLY \
-
DECLINE_SECURITY_UPDATES=true \
-
UNIX_GROUP_NAME=oinstall \
-
INVENTORY_LOCATION=/u01/app/oraInventory \
-
SELECTED_LANGUAGES=en \
-
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 \
-
ORACLE_BASE=/u01/app/oracle \
-
oracle.install.db.InstallEdition=EE \
-
oracle.install.db.isCustomInstall=false \
-
oracle.install.db.DBA_GROUP=dba \
-
oracle.install.db.OPER_GROUP=dba \
-
oracle.install.db.isRACOneInstall=false \
-
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE \
-
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
-
oracle.installer.autoupdates.option=SKIP_UPDATES
-
-
-
---------------------------------- rac grid安裝
-
./runInstaller -silent -force -noconfig -IgnoreSysPreReqs -ignorePrereq -showProgress \
-
INVENTORY_LOCATION=/u01/app/oraInventory \
-
SELECTED_LANGUAGES=en \
-
ORACLE_BASE=/u01/app/grid \
-
ORACLE_HOME=/u01/app/11.2.0/grid \
-
oracle.install.asm.OSDBA=asmdba \
-
oracle.install.asm.OSOPER=asmoper \
-
oracle.install.asm.OSASM=asmadmin \
-
oracle.install.crs.config.storageOption=ASM_STORAGE \
-
oracle.install.crs.config.sharedFileSystemStorage.votingDiskRedundancy=EXTERNAL \
-
oracle.install.crs.config.sharedFileSystemStorage.ocrRedundancy=EXTERNAL \
-
oracle.install.crs.config.useIPMI=false \
-
oracle.install.asm.diskGroup.name=OCR \
-
oracle.install.asm.diskGroup.redundancy=EXTERNAL \
-
oracle.installer.autoupdates.option=SKIP_UPDATES \
-
oracle.install.crs.config.gpnp.scanPort=1521 \
-
oracle.install.crs.config.gpnp.configureGNS=false \
-
oracle.install.option=CRS_CONFIG \
-
oracle.install.asm.SYSASMPassword=lhr \
-
oracle.install.asm.monitorPassword=lhr \
-
oracle.install.asm.diskGroup.diskDiscoveryString=/dev/rhdisk* \
-
oracle.install.asm.diskGroup.disks=/dev/rhdisk10 \
-
oracle.install.crs.config.gpnp.scanName=ZFFR4CB2101-scan \
-
oracle.install.crs.config.clusterName=ZFFR4CB-cluster \
-
oracle.install.crs.config.autoConfigureClusterNodeVIP=false \
-
oracle.install.crs.config.clusterNodes=ZFFR4CB2101:ZFFR4CB2101-vip,ZFFR4CB1101:ZFFR4CB1101-vip \
-
oracle.install.crs.config.networkInterfaceList=en0:22.188.187.0:1,en1:222.188.187.0:2 \
-
ORACLE_HOSTNAME=ZFFR4CB2101
-
-
-
---------------------------------- rac db安裝
-
./runInstaller -silent -force -noconfig -IgnoreSysPreReqs -ignorePrereq -showProgress \
-
oracle.install.option=INSTALL_DB_SWONLY \
-
DECLINE_SECURITY_UPDATES=true \
-
UNIX_GROUP_NAME=oinstall \
-
INVENTORY_LOCATION=/u01/app/oraInventory \
-
SELECTED_LANGUAGES=en \
-
oracle.install.db.InstallEdition=EE \
-
oracle.install.db.isCustomInstall=false \
-
oracle.install.db.EEOptionsSelection=false \
-
oracle.install.db.DBA_GROUP=dba \
-
oracle.install.db.OPER_GROUP=asmoper \
-
oracle.install.db.isRACOneInstall=false \
-
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE \
-
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
-
oracle.installer.autoupdates.option=SKIP_UPDATES \
-
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 \
-
ORACLE_BASE=/u01/app/oracle \
-
ORACLE_HOSTNAME=ZFFR4CB2101 \
-
oracle.install.db.CLUSTER_NODES=zffr4cb2101,zffr4cb1101 \
-
oracle.install.db.isRACOneInstall=false
-
-
-
-
-
-
-
-
-
-
-
-
-------自動啟動 The Oracle system identifier(SID) "orcltest" already exists. Specify another SID.
-
vi /etc/oratab
-
-
-
---------啟動
-
-
1) lsnrctl start (啟動監聽) -------監聽停止: lsnrctl stop,lsnrctl是listener-control 監聽器的縮寫,檢視監聽的狀態(lsnrctl status)
-
-
2) net start OracleServiceORCL (COCL 我的SID,可以小寫,這是開啟資料庫例項)
-
或者用 oradim –startup –sid orcl -----net stop OracleServiceORACLE
-
-
-
-
-
/********************************************************************************
-
* SQL Scripts Name:
-
* SQL Scripts Desc:
-
* Author:
-
* Date:
-
* Inputs:
-
* Outputs:
-
* Return:
-
* History:
-
********************************************************************************/
-
-
-
-
--------------- 修改dbid和dbname
-
原: orcltest
-
修改後:DBID: 4270446895 Database Name: ORA11G
-
create pfile from spfile;
-
shutdown immediate;
-
startup mount;
-
nid target=sys/lhr dbname=ORA11G
-
cp initorcltest.ora initORA11G.ora
-
-
shutdown immediate;
-
startup open read only;
-
@chang_dbid_lhr.sql
-
create pfile from spfile;
-
shutdown immediate;
-
-
-
-
------------AUTHID CURRENT_USER
-
CREATE OR REPLACE PROCEDURE PRO_XXXX_LHR(p_flag in NUMBER DEFAULT 1,
-
p_result out varchar2)
-
AUTHID CURRENT_USER AS
-
-
begin
-
-
-
-
end ;
-
-
-
-
-
-
-
-
--------- oracle 使用者 解壓縮
-
-
gunzip -c 10201_database_linux_x86_64.cpio.gz > /tmp/10201_database_linux_x86_64.cpio
-
cpio -idmv < 10201_database_linux_x86_64.cpio
-
-
-
unzip p6810189_10204_Linux-x86-64.zip -d /tmp
-
-
壓縮當前的資料夾 zip -r ./xahot.zip ./* -r表示遞迴
-
zip [引數] [打包後的檔名] [打包的目錄路徑]*/
-
-
Linux下*.tar.gz檔案解壓縮命令
-
-
-
1.壓縮命令:
-
-
命令格式:tar -zcvf 壓縮檔名.tar.gz 被壓縮檔名
-
-
可先切換到當前目錄下。壓縮檔名和被壓縮檔名都可加入路徑。
-
-
-
2.解壓縮命令:
-
-
命令格式:tar -zxvf 壓縮檔名.tar.gz
-
-
解壓縮後的檔案只能放在當前的目錄。
-
-
-
Aix下*.tar.gz檔案解壓縮命令
-
gunzip -c gdul3.5.0.1.tar.gz | tar -xvf -
-
-
-
-
---------- 重建scott使用者
-
-
sqlplus / as sysdba
-
-
SQL>@$ORACLE_HOME/rdbms/admin/utlsampl.sql
-
-
-
-
-
-------------SQLNET跟蹤tnsping過程 sqlnet.ora中配置
-
-
Trace_level_client=16
-
Trace_directory_client=D:\Program files\app\oracle\product\11.2.0.1\dbhome_1\NETWORK\ADMIN
-
Trace_unique_client=on
-
Trace_timestamp_client=on
-
Diag_adr_enabled=off
-
tnsping.trace_directory=D:\Program files\app\oracle\product\11.2.0.1\dbhome_1\NETWORK\ADMIN
-
tnsping.trace_level=support
-
-
-
-
----------後設資料獲取
-
SELECT to_char(DBMS_METADATA.GET_DDL('TABLESPACE', a.tablespace_name))
-
FROM DBA_TABLESPACES a
-
where a.TABLESPACE_NAME = 'TS_LHR';
-
-
-
SELECT TO_CHAR(DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')) DDL_SQL FROM DUAL;
-
-
SELECT ((DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'LHRSYS'))) FROM DUAL
-
UNION ALL
-
SELECT ((DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'LHRSYS'))) FROM DUAL
-
UNION ALL
-
SELECT ((DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'LHRSYS'))) FROM DUAL;
-
-
-
--------------linux 跟蹤sqlplus程式
-
strace -o /tmp/output.txt -T -tt -e trace=all sqlplus / as sysdba
-
-
[root@rhel6_lhr ~]# strace -t -p 4545
-
-
-
-
------------- Unix 跟蹤sqlplus程式
-
truss -dfaie -o /tmp/sched_trace.out.02271 sqlplus '/as sysdba'
-
-
-
-----aix 修改系統時間
-
linux下用date -s "20131215 09:02:25"把時間設為2013年12月15日9點2分25秒。
-
而aix呢?它不認-s這個引數:
-
date -n mmddHHMMYY,mm表示月分,dd表示日期,HH表示小時,MM表示分鐘,YY表示年份。
-
如:date -n 1215090213表示把當前時間設為2013年12月15日9點2分,秒數無法修改。
-
-
-
-----自動同步linux時間
-
1、下載ntpdate
-
注:有些版本是沒有自帶ntpdate,因此需要下載
-
# yum install -y ntpdate
-
-
2、調整時區為上海,也就是北京時間+8區
-
注:想改其他時區也可以去看看/usr/share/zoneinfo目錄
-
# cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
-
# yes | cp -f /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
-
-
3、使用NTP來同步時間
-
# ntpdate us.pool.ntp.org
-
-
4、定時同步時間(每隔10分鐘同步時鐘)
-
# crontab -l >/tmp/crontab.bak
-
# echo "*/10 * * * * /usr/sbin/ntpdate us.pool.ntp.org | logger -t NTP" >> /tmp/crontab.bak
-
# crontab /tmp/crontab.bak
-
-
-
-
-
-----筆記本開啟:wlan
-
netsh wlan set hostednetwork mode=allow ssid=lhr-minPC key=lihuarong
-
netsh wlan start hostednetwork
-
netsh wlan show hostednetwork
-
-
-
------------ 福昕pdf
-
--右轉
-
crtl+shift++
-
-
-
-
-
------ Quote (q) 語法
-
-
select q''
-
from employees;
-
-
select q'\\'
-
from employees;
-
-
-
select q'\ \'
-
from employees;
-
-
-
-
-
-------------------------------------- OS 型別
-
-----臨時修改語言環境:
-
AIX:
-
export LANG=en_US
-
export LANG=zh_CN
-
Linux:
-
export LANG=en_US.UTF-8
-
export LANG=zh_CN.UTF-8
-
-
-
可用語言環境:
-
locale -a | grep zh_CN
-
-
--------- Linux
-
cpu : cat /proc/cpuinfo|grep name|cut -f2 -d:|uniq -c
-
memory :cat /proc/meminfo
-
-
os version : lsb_release -a
-
-
os hostname hostname
-
-
------aix
-
-
-
-
-------------- AIX 檢視CPU個數
-
1. smtctl
-
2. bindprocessor -q
-
3. prtconf
-
4.lsdev
-
5.vmstat
-
-
-
racle@DNSCDBS05:/home/oracle>hostname
-
DNSCDBS05
-
oracle@DNSCDBS05:/home/oracle>oslevel -r
-
7100-01
-
oracle@DNSCDBS05:/home/oracle>uname -vr
-
1 7
-
oracle@DNSCDBS05:/home/oracle>uname -s
-
AIX
-
oracle@DNSCDBS05:/home/oracle>uname -a
-
AIX DNSCDBS05 1 7 00F813B44C00
-
oracle@DNSCDBS05:/home/oracle>uname -v
-
7
-
oracle@DNSCDBS05:/home/oracle>pmcycles -m
-
CPU 0 runs at 3024 MHz
-
CPU 1 runs at 3024 MHz
-
CPU 2 runs at 3024 MHz
-
CPU 3 runs at 3024 MHz
-
CPU 4 runs at 3024 MHz
-
CPU 5 runs at 3024 MHz
-
CPU 6 runs at 3024 MHz
-
CPU 7 runs at 3024 MHz
-
oracle@DNSCDBS05:/home/oracle>prtconf|grep Processors
-
Number Of Processors: 2
-
-
prtconf
-
-
-
-
prtdiag
-
-
-
-
-
select userenv('LANGUAGE') from dual;
-
-
archive log list;
-
-
-
-
select name from v$datafile;
-
-
-
-
------------------------------- 表空間歷史增長量
-
select a.name, b.*
-
from v$tablespace a,
-
(select tablespace_id ts#,
-
trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) datetime,
-
round(max(tablespace_usedsize * 8 / 1024),2) ts_used_size_M,
-
round(max(v.tablespace_size * 8 / 1024),2) ts_size_MB,
-
round(max(tablespace_maxsize * 8 / 1024/1024)) ts_maxsize_G
-
from dba_hist_tbspc_space_usage v
-
where trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) >=
-
trunc(sysdate - 10)
-
group by tablespace_id,
-
trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))
-
order by tablespace_id,
-
trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))) b
-
where a.ts# = b.ts#
-
ORDER BY b.TS#,b.datetime;
-
-
-
-
-
-
-
-
-
--------------------------------------------- BIOS設定之UEFI BIOS 切換為 Legacy BIOS
-
-
1、OS Optimized Defaults系統預設優化設定 配置為DISABLE
-
2、CSM相容模組設定 配置為ENABLE
-
3、Boot Mode啟動方式選擇 配置為:Legacy only
-
4、Secure boot 配置為:DISABLE
-
-
-
-
-
---------------------------------- nmon
-
-
nmon -f -t -r nmon_lhr -s 10 -c 60
-
nmon -f -t -r nmon_lhr -s 30 -c 10
-
nmon -s10 -c60 -f -m /home/
-
-
-
上面命令的含義是:
-
-
-f :按標準格式輸出檔名稱:<hostname>_YYYYMMDD_HHMM.nmon
-
-t :輸出最耗資源的程式
-
-r : nmon生成的標題,監控記錄的標題
-
-s :每隔n秒抽樣一次,這裡為30秒
-
-c :取出多少個抽樣數量,這裡為10,即監控=10*30/60=5分鐘
-
-m : 生成的資料檔案的存放目錄。
-
-
-----------------自動按天採集資料:
-
在 crontab 中增加一條記錄:
-
0 0 * * * root nmon -s300 -c288 -f -m /home/ > /dev/null 2>&1
-
300*288=86400 秒,正好是一天的資料。
-
-
-
-
-
-
------------------ 根據相對位置建立快捷方式
-
%SystemRoot%\explorer.exe "一個絕對路徑或相對路徑"
-
如:
-
%SystemRoot%\explorer.exe "..\TEST\TEST\TEST.txt"
-
注意:得把起始位置清空
-
-
-
-
-
----------------- OGG
-
-
------- source端準備
-
SQL> col current_scn format 999999999999999
-
SQL> Select current_scn from v$database;
-
-
CURRENT_SCN
-
----------------
-
12242466771468
-
-
SQL>
-
-
-
-
expdp XPADB/XPADB directory=DMP dumpfile=xpadb_20160125_01.dmp LOGFILE=xpadb_20160125.log TABLES=BASE_ACTIONPOWER,BASE_BANK,BASE_BANKMERGE FLASHBACK_SCN=12242466771468
-
-
-
---------- target 備份
-
expdp xpadrpt/xpadrpt directory=OGGD dumpfile=xpadb_20160125_02.dmp LOGFILE=xpadb_20160125_2.log TABLES=BASE_ACTIONPOWER,BASE_BANK,BASE_BANKMERGE
-
-
-
impdp XPADRPT/xpadrpt DIRECTORY=OGGD DUMPFILE=xpadb_20160125_01.dmp LOGFILE=impdp.xpadb_20160125_01.log REMAP_SCHEMA=xpadb:xpadrpt REMAP_TABLESPACE=xpaddat:xpaddata table_exists_action=replace
-
-
-
start replicat ggsrep , aftercsn 12242466771468
-
-
-
------------- VirtualBox 修改磁碟大小 51200 為5G
-
vboxmanage list hdds
-
vboxmanage modifyhd "E:\My Virtual Machines\VirtualBox VMs\VirtualBox_XP\Windows XP.vdi" --resize 51200
-
-
-
--------------WPS 去掉文件漫遊
-
Windows Registry Editor Version 5.00
-
[HKEY_CURRENT_USER\Software\Kingsoft\Office\6.0\plugins\officespace]
-
"roaminghomepageguidedtag"="9.1.0.4715"
-
檔案另存為.reg格式。在wps關閉下,雙擊該檔案,按”是“和”確定“匯入登錄檔。開啟wps,沒有文件漫遊了。如果希望再次顯示”文件漫遊“,將該給檔案中的"9.1.0.4715"改為""儲存,執行就可了。
-
-
-
-
-
-
------------------------- Linux 許可權
-
755表示該檔案所有者對該檔案具有讀、寫、執行許可權,該檔案所有者所在組使用者及其他使用者對該檔案具有讀和執行許可權。
-
-
linux檔案許可權一般都以8進製表示,格式為abc的形式,其中a,b,c各為一個數字,分別表示User、Group、及Other對該檔案的操作許可權;
-
如果檔案許可權用二進位制表示那麼是9位bit,從左至右,1-3位數字代表檔案所有者的許可權,4-6位數字代表同組使用者的許可權,7-9數字代表其他使用者的許可權;
-
而具體的許可權是由數字來表示的,讀取的許可權等於4,用r表示;寫入的許可權等於2,用w表示;執行的許可權等於1,用x表示;
-
通過4、2、1的組合,得到以下幾種許可權:0(沒有許可權);4(讀取許可權);5(4+1 | 讀取+執行);6(4+2 | 讀取+寫入);7(4+2+1 | 讀取+寫入+執行)
-
常用的linux檔案許可權如下:
-
444 r--r--r--
-
600 rw-------
-
644 rw-r--r--
-
666 rw-rw-rw-
-
700 rwx------
-
744 rwxr--r--
-
755 rwxr-xr-x
-
777 rwxrwxrwx
-
這裡以755為例:
-
1-3位7等於4+2+1,rwx,所有者具有讀取、寫入、執行許可權;
-
4-6位5等於4+1+0,r-x,同組使用者具有讀取、執行許可權但沒有寫入許可權;
-
7-9位5,同上,也是r-x,其他使用者具有讀取、執行許可權但沒有寫入許可權。
-
rwxr-xr-x: 當前檔案 對 所屬使用者 為 可讀可寫可執行,對所屬組為可讀可執行,對其他使用者為 可讀可執行
-
Linux 中對於檔案的許可權 分為 可讀(r),可寫(w),可執行(x),其對應的 8進位制程式碼是
-
可讀(r):4,可寫(w):2,可執行(x):1 ,如果有多個許可權,那麼對應的8進位制數字就是各個單獨許可權數字相加,3個數字中,第一個代表檔案所屬使用者,第二個 代表檔案所屬組,第三個 代表 其他。 那麼 對於 所屬使用者為可讀可寫可執行 就是 4+2+1 = 7, 對於所屬組為 可讀可執行就是 4+1 = 5 ,對於其他使用者 為 可讀 可執行 就是 4+1 = 5, 合起來就是 755 的許可權
-
-
-
-
-------------- linux下檢視所有使用者
-
-
cat /etc/passwd |cut -f 1 -d :
-
-
-
-
---------- AIX檢視所有使用者及其id
-
lsuser ALL |cut -d ' ' -f 1
-
lsuser ALL |cut -d ' ' -f 2
-
lsuser ALL |cut -d ' ' -f 1-2
-
-
---------- AIX檢視所有使用者組及其id
-
lsgroup ALL |cut -d ' ' -f 1
-
lsgroup ALL |cut -d ' ' -f 2
-
lsgroup ALL |cut -d ' ' -f 1-2
-
-
-
---------- linux和AIX檢視所有使用者及其id
-
cat /etc/passwd |cut -d : -f 1
-
cat /etc/passwd |cut -d : -f 3
-
cat /etc/passwd |cut -d : -f 1,3
-
cat /etc/passwd | awk 'BEGIN{FS=":"} {printf "%-15s %-10s %-20s \n",$1,$3,$6}' | sort -n -k2
-
cat /etc/passwd | awk 'BEGIN{FS=":"} $3>=100 {printf "%-15s %-10s %-20s \n",$1,$3,$6}' | sort -n -k2
-
-
-
-
---------- linux和AIX檢視所有使用者組及其id
-
cat /etc/group |cut -d : -f 1
-
cat /etc/group |cut -d : -f 3
-
cat /etc/group |cut -d : -f 1,3
-
cat /etc/group | awk 'BEGIN{FS=":"} {printf "%-15s %-10s \n",$1,$3}' | sort -n -k2
-
cat /etc/group | awk 'BEGIN{FS=":"} $3>=100 {printf "%-15s %-10s \n",$1,$3}' | sort -n -k2
-
-
-
-
-
[root@redhat4 ~]# cat /etc/group | awk 'BEGIN{FS=":"} {printf "%-10s %-5s \n",$1,$3}'
-
root 0
-
bin 1
-
daemon 2
-
sys 3
-
adm 4
-
tty 5
-
xfs 43
-
ntp 38
-
gdm 42
-
stapdev 101
-
stapusr 102
-
pegasus 65
-
htt 103
-
oinstall 500
-
dba 501
-
-
---------- linux和AIX檢視所有使用者組及其id,且id>=100
-
[root@redhat4 ~]# cat /etc/group | awk 'BEGIN{FS=":"} $3>=100 {printf "%-10s %-5s \n",$1,$3}'
-
users 100
-
nfsnobody 4294967294
-
stapdev 101
-
stapusr 102
-
htt 103
-
oinstall 500
-
dba 501
-
[root@redhat4 ~]#
-
-
-
-
-
-
-
-------------------------------------------------------------------------------- 虛擬機器系列 --------------------------------------------------------------------------------
-
------------------------------------------- 最新的XP虛擬機器 Windows XP.vdi
-
虛擬機器安裝:無論是綠色還是安裝都需要有管理員的許可權
-
-
虛擬機器中安裝windows7: 需要進入PE系統後再安裝
-
-
c:
-
cd C:\Program Files (x86)\VMware\VMware Workstation\
-
-
-
----檔案合併
-
vmware-vdiskmanager -r "E:\My Virtual Machines\Windows XP Professional\Windows XP Professional.vmdk" -t 0 "E:\My Virtual Machines\Windows XP Professional\XP_LHR.vmdk"
-
-
-
---增大虛擬機器檔案 -x後的引數為磁碟擴充套件後的總大小
-
vmware-vdiskmanager -x 15Gb "G:\VMware Space\Red Hat Enterprise Linux 5.vmdk"
-
-
---壓縮磁碟空間,但是磁碟的最大值不變
-
vmware-vdiskmanager -k "G:\My Virtual Machines\RHEL6.5_LHRDB\RHEL6.5_LHRDB_DATA.vmdk"
-
-
-
-
-
-
{} 必須有
-
[] 可選項
-
| 選擇
-
<> 註釋或引數值
-
()
-
-
-
-
-------------------------------------------------------------------------------- CHM --------------------------------------------------------------------------------
-
chm檔案是由Windows目錄下的hh.exe這個檔案開啟的,但是它的搜尋功能卻不是這個檔案能辦到的, 所以, chm能開啟卻無法搜尋, 或者是MSDN能開啟卻無法搜尋, 即使重灌也無濟於事, 有的人遇到這個問題還從其他人的機器上重新拷貝了hh.exe這個檔案, 發現仍然不能解決這個問題。 其實解決這個問題,很簡單, 在執行裡面執行以下命令:
-
-
regsvr32 hhctrl.ocx
-
regsvr32 itss.dll
-
regsvr32 itircl.dll //這個很重要,是關於全文搜尋的。
-
-
問題就可以解決了。 最後一個命令特別重要, 以前我找到的解決方法只有前面兩個, 沒有最後一個, 還是解決不了問題的。
-
-
如果chm格式檔案出現“網頁不能瀏覽”的錯誤,在該文件上點選滑鼠右鍵,解除鎖定即可。
-
-
-
-
-
------------------------------------ shell中的引號
-
-
單引號和雙引號的區別。單引號告訴shell忽略所有特殊字元,而雙引號忽略大多數,但不包括$、\、`。
-
-
-
-
-
-
-------------------------------------------
-
Sub setpicsize() '批量選中圖片
-
-
Dim j '計數圖片個數
-
-
Application.ScreenUpdating = False
-
For j = 1 To ActiveDocument.InlineShapes.Count '檔案中圖片總個數,圖片型別為inlineshapes
-
'ActiveDocument.InlineShapes(j).Height = ActiveDocument.InlineShapes(j).Height '設定高度
-
'ActiveDocument.InlineShapes(j).Width = ActiveDocument.InlineShapes(j).Width '設定寬度
-
ActiveDocument.InlineShapes(j).Range.Editors.Add wdEditorEveryone
-
Next j
-
ActiveDocument.SelectAllEditableRanges (wdEditorEveryone)
-
ActiveDocument.DeleteAllEditableRanges (wdEditorEveryone)
-
Application.ScreenUpdating = True
-
-
End Sub
-
-
-
-
'ALTER SYSTEM KILL SESSION ''' || s.SID || ',' || s.SERIAL# ||',@'||A.INST_ID||' IMMEDIATE ;' kill_session,
-
'ALTER SYSTEM KILL SESSION ''' || s.SID || ',' || s.SERIAL# ||''' IMMEDIATE ;' kill_session,
-
ALTER SYSTEM KILL SESSION '1228,42549,@1';
-
SELECT 'ALTER SYSTEM DISCONNECT SESSION ''' || V.SID || ',' || V.SERIAL# || ',@' ||
-
V.INST_ID || ''' IMMEDIATE',
-
'ALTER SYSTEM DISCONNECT SESSION ''' || V.SID || ',' || V.SERIAL# ||
-
''' IMMEDIATE',
-
V.*
-
FROM GV$SESSION V;
-
-
-
-----清理killed的會話
-
-----方法1
-
select spid, program from v$process
-
where program!= 'PSEUDO'
-
and addr not in (select paddr from v$session)
-
and addr not in (select paddr from v$bgprocess)
-
and addr not in (select paddr from v$shared_server);
-
-
select INST_ID, spid, program,'kill -9 '|| spid kill9
-
from gv$process a
-
where program != 'PSEUDO'
-
and (INST_ID, addr) not in (select INST_ID, paddr from gv$session)
-
and (INST_ID, addr) not in (select INST_ID, paddr from gv$bgprocess)
-
and (INST_ID, addr) not in (select INST_ID, paddr from gv$shared_server)
-
and a.PNAME is null;
-
-
-----方法2
-
set line 9999
-
col sessionid format a20
-
col sessionid_killed format a20
-
col kill_session format a60
-
-
SELECT a.INST_ID,
-
a.SID || ',' || a.SERIAL# || ',' ||
-
(select spid
-
from gv$process b
-
where b.INST_ID = a.INST_ID
-
and A.creator_addr = b.ADDR --and decode(a.status,'KILLED',A.creator_addr,A.PADDR) = b.ADDR
-
) sessionid,
-
a.PADDR,
-
a.STATUS,
-
a.PROGRAM,
-
'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' kill_session
-
FROM gv$session a
-
WHERE a.USERNAME = 'SYS'
-
and a.STATUS = 'KILLED';
-
-
-
-----方法3
-
SELECT a.SID || ',' || a.SERIAL# || ',' ||
-
(select spid
-
from gv$process b
-
where b.INST_ID = a.INST_ID
-
and A.pid = b.pid) sessionid,
-
'alter system kill session ''' || sid || ',' || serial# || ''';' kill_session
-
FROM gV$DETACHED_SESSION a;
-
-
-
-----方法4
-
SELECT INST_ID, spid, program, 'kill -9 ' || spid kill9
-
FROM gv$process a
-
WHERE (a.INST_ID, a.addr) in (select INST_ID, p.addr
-
from gv$process p
-
where pid <> 1
-
minus
-
select INST_ID, s.paddr
-
from gv$session s)
-
and a.PNAME is null;
-
-
-
-
---------------- rac 配置SSH互信
-
sshUserSetup.sh在GI安裝介質解壓縮後的sshsetup目錄下。下面兩條命令在一個節點上執行即可,在root使用者下執行:
-
./sshUserSetup.sh -user grid -hosts "raclhr-12cR1-N2 raclhr-12cR1-N1" -advanced exverify –confirm
-
./sshUserSetup.sh -user oracle -hosts "raclhr-12cR1-N2 raclhr-12cR1-N1" -advanced exverify -confirm
-
-
-------- 指令碼配置
-
grep "^LoginGraceTime 0" /etc/ssh/sshd_config
-
[ $? -ne 0 ] && { cp -p /etc/ssh/sshd_config /etc/ssh/sshd_config.org; echo "LoginGraceTime 0" >>/etc/ssh/sshd_config; }
-
-
export hn=`hostname`
-
export oth=RACDB2
-
export p_pwd='/nfs/software/db/install/inst/sh/11g'
-
su - grid -c "$p_pwd/sshUserSetup.sh -user grid -hosts $oth -noPromptPassphrase"
-
su - grid -c "ssh $hn hostname"
-
su - grid -c "ssh $oth hostname"
-
-
su - oracle -c "$p_pwd/sshUserSetup.sh -user oracle -hosts $oth -noPromptPassphrase"
-
su - oracle -c "ssh $hn hostname"
-
su - oracle -c "ssh $oth hostname"
-
-
------分別配置grid和oracle使用者的ssh
-
-------- 手工配置
-
----------------------------------------------------------------------------------
-
[root@ZFLHRDB1 : /]# su - oracle
-
[oracle@ZFLHRDB1 ~]$ mkdir -p ~/.ssh
-
[oracle@ZFLHRDB1 ~]$ chmod 700 ~/.ssh
-
[oracle@ZFLHRDB1 ~]$ ssh-keygen -t rsa ->回車->回車->回車
-
[oracle@ZFLHRDB1 ~]$ ssh-keygen -t dsa ->回車->回車->回車
-
-
-----------------------------------------------------------------------------------
-
[root@ZFLHRDB2 : /]# su - oracle
-
[oracle@ZFLHRDB2 ~]$ mkdir ~/.ssh
-
[oracle@ZFLHRDB2 ~]$ chmod 700 ~/.ssh
-
[oracle@ZFLHRDB2 ~]$ ssh-keygen -t rsa ->回車->回車->回車
-
[oracle@ZFLHRDB2 ~]$ ssh-keygen -t dsa ->回車->回車->回車
-
-
-----------------------------------------------------------------------------------
-
-
[oracle@ZFLHRDB1 ~]$ cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
-
[oracle@ZFLHRDB1 ~]$ cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
-
[oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB2 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys ->輸入ZFLHRDB2密碼
-
[oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB2 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys ->輸入ZFLHRDB2密碼
-
[oracle@ZFLHRDB1 ~]$ scp ~/.ssh/authorized_keys ZFLHRDB2:~/.ssh/authorized_keys ->輸入ZFLHRDB2密碼
-
-
-----------------------------------------------------------------------------------
-
測試兩節點連通性:
-
-
[oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB1 date
-
[oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB2 date
-
[oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB1-priv date
-
[oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB2-priv date
-
-
[oracle@ZFLHRDB2 ~]$ ssh ZFLHRDB1 date
-
[oracle@ZFLHRDB2 ~]$ ssh ZFLHRDB2 date
-
[oracle@ZFLHRDB2 ~]$ ssh ZFLHRDB1-priv date
-
[oracle@ZFLHRDB2 ~]$ ssh ZFLHRDB2-priv date
-
-
-
-
-
-
-
-----如何清除Shared Pool中某條SQL語句?
-
如果資料庫版本為Oracle 10g以前,那麼只能清空整個Shared Pool,命令為:“ALTER SYSTEM FLUSH SHARED_POOL;”。在Oracle 10g中提供了一個包DBMS_SHARED_POOL,該包可以實現該功能。若該包沒有安裝,則可以通過$ORACLE_HOME/rdbms/admin/dbmspool.sql進行安裝。在Oracle 10.2.0.4中有BUG(MOS為:751876.1),需要通過設定事件來規避該問題,命令為:“ ALTER SESSION SET EVENTS '5614566 TRACE NAME CONTEXT FOREVER';”
-
使用這種方法,就可以精確的將一個SQL從共享池中刪除,從而使得Oracle為這個SQL重新生成執行計劃。這種方法只針對單個SQL語句,使得解決問題的同時不會造成任何的誤傷。
-
SYS@lhrdb> SELECT ADDRESS,HASH_VALUE FROM V$SQLAREA WHERE ROWNUM<=1;
-
ADDRESS HASH_VALUE
-
---------------- ----------
-
0000000092D263D0 3231842444
-
SYS@lhrdb> EXEC DBMS_SHARED_POOL.PURGE('0000000092D263D0,3231842444','C');
-
PL/SQL procedure successfull
-
-
-
--------------------------------------------- Linux 邏輯卷管理
-
pvcreate /dev/sda4
-
pvdisplay
-
pvs
-
vgcreate vg_orasoft /dev/sda4
-
vgdisplay
-
vgs
-
lvcreate -n lv_orasoft_u01 -L 1G vg_orasoft
-
lvdisplay
-
lvs
-
mkfs.ext4 /dev/vg_orasoft/lv_orasoft_u01
-
mkdir /u11
-
mount /dev/vg_orasoft/lv_orasoft_u01 /u11
-
--mv /u01/* /u11/
-
-
--擴充套件邏輯卷
-
vgextend vg_orasoft /dev/sdb3
-
--lvextend -L +9G /dev/vg_orasoft/lv_orasoft_u01
-
lvextend -L 20G /dev/vg_orasoft/lv_orasoft_u01
-
resize2fs /dev/vg_orasoft/lv_orasoft_u01 #更新檔案系統
-
--收縮邏輯卷
-
lvreduce -L -4G /dev/vg_orasoft/lv_orasoft_u01
-
--重新命名邏輯卷
-
lvrename /dev/vg_orasoft/lv_ora_soft_u01 /dev/vg_orasoft/lv_orasoft_u01
-
-
-
--- /etc/fstab
-
/dev/vg_orasoft/lv_orasoft_u01 /u01 ext4 defaults 0 0
-
/dev/vg_orasoft/lv_orasoft_u02 /u02 ext4 defaults 0 0
-
/dev/vg_orasoft/lv_orasoft_u03 /u03 ext4 defaults 0 0
-
/dev/vg_orasoft/lv_oradata_u04 /u04 ext4 defaults 0 0
-
-
---找邏輯卷
-
lvmdiskscan
-
vgchange -ay
-
-
-
-
--------------------------------------------- 查詢錶的歷史統計資訊
-
SELECT D.OWNER,
-
D.TABLE_NAME,
-
TO_CHAR(D.STATS_UPDATE_TIME, 'YYYY-MM-DD HH24:MI:SS') STATS_UPDATE_TIME
-
FROM DBA_TAB_STATS_HISTORY D
-
WHERE D.TABLE_NAME IN ('TPCCBOKBAL', 'TPCCBOKBAL_TMP', 'TPCCBOKBALJN')
-
ORDER BY D.owner,D.table_name, D.stats_update_time;
-
-
-
SELECT B.OWNER,
-
B.OBJECT_NAME TABLE_NAME,
-
TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
-
TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
-
D.ROWCNT
-
FROM SYS.WRI$_OPTSTAT_TAB_HISTORY D, DBA_OBJECTS B
-
WHERE D.OBJ# = B.OBJECT_ID
-
AND B.OBJECT_NAME IN
-
('TEST_STAT', 'TPCCBOKBAL_TMP', 'TPCCBOKBALJN', 'PK_TPCCBOKBAL')
-
ORDER BY D.OBJ#, D.SAVTIME;
-
-
-
----------------------- 查詢索引的歷史統計資訊
-
SELECT B.OWNER,
-
B.OBJECT_NAME INDEX_NAME,
-
TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
-
TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
-
D.ROWCNT,
-
D.BLEVEL,
-
D.LEAFCNT,
-
D.DISTKEY,
-
D.CLUFAC
-
FROM SYS.WRI$_OPTSTAT_IND_HISTORY D, DBA_OBJECTS B
-
WHERE D.OBJ# = B.OBJECT_ID
- AND B.OBJECT_NAME IN ('IND_TEST
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2122294/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 小麥苗的常用程式碼
- 【Oracle版本升級圖--小麥苗】Oracle
- 小麥苗微信公眾號文章連結地址
- 【OCP|052】OCP最新題庫解析(052)--小麥苗解答版
- 小麥苗資料庫巡檢指令碼V7.0,支援Oracle、MySQL、SQL Server和PG資料庫資料庫指令碼OracleMySqlServer
- 【爬蟲】利用Python爬蟲爬取小麥苗itpub部落格的所有文章的連線地址(1)爬蟲Python
- 不用程式碼,2小時搞定自己的網站網站
- 說說iOS中的常用的關鍵字static ,class(僅限Swift關鍵字)iOSSwift
- 僅供自己使用的javascipt輔助開發工具Java
- 自己做一個ChatGPT微信小程式(程式碼開源)ChatGPT微信小程式
- 使用wepy開發微信小程式01——常用的元件微信小程式元件
- 自己開發的油價小程式 (小程式原生+laravel)Laravel
- 小程式直播連麥的技術實現與解析
- 【爬蟲】利用Python爬蟲爬取小麥苗itpub部落格的所有文章的連線地址並寫入Excel中(2)爬蟲PythonExcel
- 編寫自己的程式碼庫:CSS3 常用動畫的實現CSSS3動畫
- 編寫自己的程式碼庫(css3常用動畫的實現)CSSS3動畫
- 小程式:無限自動滾動的Gallery
- 程式設計師不僅僅是寫程式碼程式設計師
- 小程式無限層級路由方案路由
- 改進c#程式碼的5個常用的小技巧C#
- 小程式 · 常用方法
- php常用小程式PHP
- 說說自己的程式小迷信吧。。。
- 我自己的程式碼編輯器-sublime text 3 及常用外掛
- 限時1元領,僅需6小時輕鬆進階大前端前端
- python創意小作品程式碼-Python學習,給自己的程式碼做個合集,定製自己的桌面軟體!...Python
- 拿工資不僅僅是讓你寫程式碼的
- 編寫自己的程式碼庫(javascript常用例項的實現與封裝)JavaScript封裝
- 使用思維導圖,優雅的完成自己的程式碼
- 常用的HTML程式碼
- 常用的JavaScript程式碼JavaScript
- 小程式開發進階:如何實現直播連麥
- 亞馬遜將推流媒體音樂服務 僅限Echo使用者使用亞馬遜
- AI降噪軟體的出現,不僅僅要終結手機雙麥克風時代AI
- 編寫自己的程式碼庫(javascript常用例項的實現與封裝–續)JavaScript封裝
- 精簡自己 20% 的程式碼
- SmartCode—不僅僅是功能強大的程式碼生成器
- 【Tips】【UE】總結自己常用的UltraEdit使用技巧