小麥苗的常用程式碼(僅限自己使用)

lhrbest發表於2016-07-20

點選(此處)摺疊或開啟

  1. ?

  2. ?








  3. ------GBK:
  4. =E6=B5=B7=E6=BB=A8 (=E5=8F ---> LHR (=E5=8F
  5. ------3DUTF-8:
  6. =E6=B5=B7=E6=BB=A8 (=E5=8F ---> =E5=B0=8F=E9=BA=A6=E8=8B=97 (=E5=8F


  7. ---小麥苗
  8. 3DUTF-8:=E5=B0=8F=E9=BA=A6=E8=8B=97
  9. 3DGBK: =D0=A1=C2=F3=C3=E7








  10. ----- editplus 替換空行: ^[ \t]*\n EDIT -> DELETE->DELETE BLANK LINES

  11. ----- editplus 替換以#開頭的行,多次執行: ^#[^\n]*\n



  12. windows不支援的檔名:\ / : * ? " < > |

  13. \ 反斜槓、捺斜槓
  14. / 正斜槓、撇斜槓

  15.  製表符 chr(9)
  16.  換行符 chr(10)
  17.  回車符 chr(13)



  18. 1s=1000ms(毫秒)=1000000(微秒)




  19. -------------------------------常用日期

  20. 月份    全拼        簡拼    示例
  21. 1    January        Jan    
  22. 2    February    Feb    
  23. 3    March        Mar    
  24. 4    April        Apr    
  25. 5    May        May    
  26. 6    June        Jun    
  27. 7    July        Jul    
  28. 8    August        Aug    
  29. 9    September    Sep    
  30. 10    October        Oct    Sat Aug 13 10:54:45 2016
  31. 11    November    Nov    Tue Nov 29 02:56:59 2016
  32. 12    December    Dec    Tue Dec 06 08:51:57 2016
  33.             
  34.             
  35.             
  36.             
  37.             
  38. 星期    全拼        簡拼    示例
  39. 1    Monday        Mon    Mon Dec 05 01:04:18 2016
  40. 2    Tuesday        Tue    Tue Dec 01 16:21:37 2016
  41. 3    Wednesday    Web    
  42. 4    Thursday    Thu    Thu Dec 01 08:36:03 2016
  43. 5    Friday        Fri    Fri Dec 02 16:17:17 2016
  44. 6    Saturday    Sat    Sat Dec 10 14:13:34 2016
  45. 7    Sunday        Sun    






  46. ---------------------------BBED
  47. 1.1 我的編譯程式碼
  48. ls -l $ORACLE_HOME/rdbms/lib/*sbbd*                                                                */
  49. ls -l $ORACLE_HOME/rdbms/mesg/bbed*                                                                */
  50. chown oracle:dba /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/*sbbd*     */
  51. chown oracle:dba /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/bbed*                                            */
  52.  
  53. --cd $ORACLE_HOME/rdbms/lib
  54. --make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
  55.  
  56. --make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
  57. make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed
  58.  
  59. 1.2 我的使用程式碼
  60. vi /home/oracle/file.txt
  61. set line 9999 pagesize 9999
  62. col name format a80
  63. select file#||' '||name||' '||bytes name from v$datafile;
  64.  
  65. vi /home/oracle/bbed.par
  66. blocksize=8192
  67. listfile=/home/oracle/file.txt
  68. mode=edit
  69.  
  70. bbed parfile=/home/oracle/bbed.par
  71. bbed PASSWORD=blockedit mode=edit blocksize=8192 listfile=/home/oracle/file.txt



  72. ---------secureCRT中vi 顯示彩色
  73. [root@rhel6_lhr ~]# vi .bashrc
  74. alias vi='vim'

  75. [root@rhel6_lhr ~]# vi /etc/profile
  76. export TERM=xterm-color



  77. secureCRT 中刪除用 shift+delete 鍵或 ctrl + backspace 鍵

  78. ctrl+? 清理當前行命令

  79. ---------------------------- root 配置
  80. -------------- AIX
  81. chmod +w /etc/profile
  82. echo "
  83. umask 022
  84. export ORACLE_HOME=/u01/app/11.2.0/grid
  85. export PATH="\$PATH:\$ORACLE_HOME/bin"
  86. export PS1='[\$LOGNAME@'\`hostname\`:'\$PWD'']# '
  87. set -o vi
  88. alias l=\""ls -l\""
  89. alias ll=\""ls -l\""
  90. " >> /etc/profile
  91. . /etc/profile

  92. echo "
  93. umask 022
  94. export ORACLE_HOME=/u01/app/11.2.0/grid
  95. export PATH="\$PATH:\$ORACLE_HOME/bin"
  96. export PS1='[\$LOGNAME@'\`hostname\`:'\$PWD'']# '
  97. set -o vi
  98. alias l=\""ls -l\""
  99. alias ll=\""ls -l\""
  100. " >> ~/.profile
  101. . ~/.profile


  102. -------------- Linux
  103. chmod +w /etc/profile
  104. echo "
  105. umask 022
  106. export ORACLE_HOME=/u01/app/11.2.0/grid
  107. export PATH="\$PATH:\$ORACLE_HOME/bin"
  108. export PS1='[\$LOGNAME@'\`hostname\`:'\$PWD'']# '
  109. export TERM=xterm-color
  110. set -o vi
  111. alias l=\""ls -l\""
  112. alias ll=\""ls -l\""
  113. " >> /etc/profile
  114. . /etc/profile

  115. echo "
  116. umask 022
  117. export ORACLE_HOME=/u01/app/11.2.0/grid
  118. export PATH="\$PATH:\$ORACLE_HOME/bin"
  119. export PS1='[\$LOGNAME@'\`hostname\`:'\$PWD'']# '
  120. set -o vi
  121. alias l=\""ls -l\""
  122. alias ll=\""ls -l\""
  123. " >> ~/.bash_profile
  124. . ~/.bash_profile



  125. --export PS1="[\u@\h \W]\$ "
  126. --export PS1='[$LOGNAME@'`hostname`:'$PWD'']# '
  127. echo "export PS1='[\$LOGNAME@'\`hostname\`:'\$PWD'']# '" > /etc/profile
  128. export PS1='[$LOGNAME@'`hostname`:'$PWD'']# '



  129. -------------------------------------------------------------------------------- linux


  130. export HISTTIMEFORMAT='%F %T '
  131. export HISTSIZE=100000
  132. export HISTFILESIZE=100000


  133.  Ctrl+R 搜尋命令歷史 ,當找到命令後,通常再按Enter鍵就可以執行該命令。如果想對找到的命令進行調整後再執行,則可以按一下左或右方向鍵。







  134. ------------------------------------------------------------------------------------------------- cmd 命令

  135. mstsc 遠端桌面
  136. firewall.cpl 和 wf.msc 防火牆設定
  137. services.msc 服務
  138. cmd
  139. msconfig 開啟啟動項


  140. dxdiag Direct11




  141. ----開啟遠端桌面
  142. 1、計算機——右鍵——屬性——遠端設定——遠端,選中“允許允許任意版本遠端桌面的計算機連線”
  143. 2、關閉防火牆
  144.    netsh firewall set opmode disable
  145.    netsh advfirewall set publicprofile state off

  146. 3、services.msc 開啟服務,然後 找到 Remote Desktop 相關的三個服務,確保狀態為“已啟動”




  147. C:\Users\華榮>set /a a=8500*12
  148. 102000
  149. C:\Users\華榮>



  150. 電腦設定豆沙綠: 視窗、活動視窗標題2
  151. 色度,飽和度,亮度:85 123 205 80 100 200 100 120 200 76 91 205
  152. RGB:207,232,204

  153. cmd介面:0,128,128
  154. 程式碼背景:141,179,226


  155. ------- cmd 介面字型和介面背景顏色
  156. 背景顏色: 0 128 128
  157. HKEY_CURRENT_USER\Console\%SystemRoot%_system32_cmd.exe 修改%SystemRoot%_system32_cmd.exe下,若無該項可以修改HKEY_CURRENT_USER\Console下,將DWORD型別的CodePage項修改為十進位制值936,將字串型別的FaceName改為Lucida Console ,若沒有該項則可以新建該項


  158. chcp 437

  159. cmd 介面按下F7顯示歷史命令


  160. ----cmd 下不換行
  161. host set /p=start...資料庫巡檢服務概要. <nul
  162. host set /p=. <nul
  163. host echo ..end




  164. ----- 鎖屏 windows + L 鍵
  165.  
  166. ----------系統環境變數

  167. Windows Registry Editor Version 5.00

  168. [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment]
  169. "NLS_DATE_FORMAT" "YYYY-MM-DD HH24:mi:ss"
  170. "NLS_LANG AMERICAN_CHINA.ZHS16GBK
  171. "ORACLE10G D:\Program files\app\oracle\product\10.2.0\db_1
  172. "ORACLE11G D:\Program files\app\oracle\product\11.2.0.1\dbhome_1
  173. "ORACLE8I D:\Program files\app\oracle\product\ora8i
  174. "ORACLE9I D:\Program files\app\oracle\product\ora92
  175. "ORACLE_HOME D:\Program files\app\oracle\product\11.2.0.1\dbhome_1
  176. "TNS_ADMIN D:\Program files\app\oracle\product\11.2.0.1\dbhome_1\NETWORK\ADMIN

  177. ----------使用者環境變數
  178. Windows Registry Editor Version 5.00

  179. [HKEY_CURRENT_USER\Environment]
  180. "NLS_DATE_FORMAT"="YYYY-MM-DD HH24:MI:SS"
  181. "NLS_LANG"="AMERICAN_CHINA.ZHS16GBK"



  182. ---------cmd下設定使用者環境變數
  183. SETX "NLS_DATE_FORMAT" "YYYY-MM-DD HH24:MI:SS"
  184. SETX "NLS_LANG" "AMERICAN_CHINA.ZHS16GBK"
  185. SETX "ORACLE_HOME" "D:\Program files\app\oracle\product\11.2.0.1\dbhome_1"
  186. SETX "TNS_ADMIN" "D:\Program files\app\oracle\product\11.2.0.1\dbhome_1\NETWORK\ADMIN"
  187. SETX "PATH" "%ORACLE_HOME%\bin"
  188. SETX "VBOX_MSI_INSTALL_PATH" "D:\Program Files\Oracle\VirtualBox"

  189. SETX "ORACLE_HOME" "%cd%\oracle\product\11.2.0.1\dbhome_1"



  190. SETX "ORACLE_HOME" "D:\Program files\app\oracle\product\11.2.0.1\dbhome_1"
  191. SETX "TNS_ADMIN" "%%ORACLE_HOME%%\network\admin"
  192. SETX "PATH" "%path%;%%ORACLE_HOME%%\bin"
  193. SETX "NLS_DATE_FORMAT" "YYYY-MM-DD HH24:MI:SS"
  194. SETX "NLS_LANG" "AMERICAN_CHINA.ZHS16GBK"




  195. alter session set nls_language='SIMPLIFIED CHINESE';
  196. alter system set nls_language='AMERICAN' scope=spfile;

  197. export EDITOR=vi
  198. export ORACLE_SID=orclasm
  199. export ORACLE_BASE=/u01/app/oracle
  200. export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
  201. export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
  202. export ORACLE_ALERT=$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace
  203. export PATH=$ORACLE_HOME/bin:$PATH
  204. export TNS_ADMIN=$ORACLE_HOME/network/admin
  205. export ORACLE_PATH=.:$ORACLE_BASE/dba_scripts/sql:$ORACLE_HOME/rdbms/admin
  206. umask 022

  207. #export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"    SELECT userenv('LANGUAGE') db_NLS_LANG FROM DUAL;
  208. #export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
  209. export SQLPATH=$ORACLE_HOME/sqlplus/admin
  210. export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss';

  211. #alias sqlplus='rlwrap sqlplus'
  212. #alias rman='rlwrap rman'
  213. #alias asmcmd='rlwrap asmcmd'
  214. alias alert_log='tail -200f $ORACLE_ALERT/alert_$ORACLE_SID.log'
  215. alias alert_xml='tail -200f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/alert/log.xml'
  216. alias alert_listener='tail -200f $ORACLE_BASE/diag/tnslsnr/rhel6/listener/trace/listener.log'


  217.  




  218. ----- 如何啟用 Administrator(xp、windows7)
  219. net user Administrator /active:yes
  220. 單擊“開始→執行”,輸入regedit後回車,開啟登錄檔編輯器,依次展開 "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\SpecialAccounts\UserList" 分支
  221. 將右邊的Administrator的值改為1,即可讓Administrator賬戶出現在登入的歡迎螢幕上。(如果沒有Administrator的話就建立一個DWORD(32位)值型別,其它上級專案沒有的話也依次建立)

  222. ----- 在PE中修改源系統的登錄檔

  223. 啟動登錄檔編輯器,點選HKEY_LOCAL_MACHINE,然後點【檔案】-【載入配置單元】,瀏覽檔案,進入 Win7安裝的分割槽 " c:\Windows\System32\config" 裡面的SYSTEM、DEFAULT、SOFTWARE都可以選擇,選擇後讓你輸入名稱,隨便輸入,如byiu輸入後點確定就會在HKEY_LOCAL_MACHINE下面增加那個分支,然後就可以編輯了。



  224. ---新建administrator
  225. 點開HKEY_LOCAL_MACHINE,點開SAM,右擊SAM,點選許可權..在組或使用者名稱稱下點選 Administrators,點選完全控制對應的允許核取方塊,點選確定
  226. 在登錄檔編輯器視窗按F5重新整理。點開:"HKEY_LOCAL_MAICHINE\SAM\SAM\Domains\Account\Users\Names"
  227. 匯入如下注冊表,即Administrator使用者:
  228. Windows Registry Editor Version 5.00

  229. [HKEY_LOCAL_MACHINE\SAM\SAM\Domains\Account\Users\Names\Administrator]
  230. @=hex(1f4):


  231. ----------------禁用U盤 啟用U盤
  232. ----- 啟用usb大容量儲存裝置 預設設定為“3”表示手動,“2”是表示自動,“4”是表示停用,一般設定為3

  233. --禁用
  234. reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\USBSTOR" /v Start /d 4 /t reg_dword /f

  235. --啟用
  236. reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\USBSTOR" /v Start /d 3 /t reg_dword /f


  237. ---------------------- 檢視網站是否通

  238. telnet 192.168.59.130 1158

  239. ---怎樣從DOS/Linux的telnet中退出

  240. 我們經常要用到telnet來測試FTP埠,但是有一個問題估計大家都會遇到,在telnet進入某個埠後無法退出,沒辦法,為了繼續測試只好關掉這個dos視窗,重新開啟一個。 這裡教給你一個方法,按住ctrl+]就可以退出到

  241.  
  242. Welcome to Microsoft Telnet Client
  243. Escape Character is 'CTRL+]'
  244. Microsoft Telnet>
  245. 然後再輸入quit就可以完全退出了。





  246. 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
  247. 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

  248. mysql -u root -p



  249. ----------------------------- 科學計數法
  250.  12345678901,結果為 1.23E+10,即 1.23 乘以 10 的 10 次冪
  251.  12300000000



  252.  4.1E+11
  253.  410 000 000 000



  254. ----------------------------------------- crontab
  255. 每天0點02分 2 0 * * * /home/weblogic/lhr/ods_scripts/new_ods2/main_ods_entity_de_lhr.sh
  256. 每天凌晨1點3分    3 1 * * * /home/weblogic/bin/crm_address_intf_1.sh
  257. 每週日18點    0 18 * * 0 /home/weblogic/lhr/ods_scripts/new_ods2/main_weekly.sh
  258. 每週三18點    0 18 * * 3 /home/weblogic/bin/crm_inf_linkresource_monthly.sh
  259.             

  260. --root使用者可以檢視其它使用者的crontab
  261. crontab -u zhangsan -l
  262. crontab -u lisi -l
  263. crontab -u wangwu -l




  264. nohup sh rman_backup_full.sh 2>&1 &




  265. MYDATE=`date +'%Y-%m-%d %H:%M:%S'`


  266. sqlplus lhr/lhr@192.168.128.134:1521/orclasm.lhr.com
  267. tnsping 192.168.0.123:1521/dev.us.oracle.com

  268. drop user lhr cascade;

  269. NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280"
  270. NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
  271. NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
  272. NLS_LANG=AMERICAN_AMERICA.UTF8

  273. alias alert_log='tail -200f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log'



  274. drop tablespace temp including contents and datafiles;
  275. ALTER TABLESPACE temp DROP TEMPFILE '/u01/app/oracle/oradata/orcl/orclasm/tempfile/temp.264.850260283';
  276. ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 20M;


  277. --表空間大小:
  278. set pagesize 9999 line 9999
  279. col TS_Name format a30
  280. WITH wt1 AS
  281.  (SELECT ts.TABLESPACE_NAME,
  282.          df.all_bytes,
  283.          decode(df.TYPE,
  284.                 'D',
  285.                 nvl(fs.FREESIZ, 0),
  286.                 'T',
  287.                 df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ,
  288.          df.MAXSIZ,
  289.          ts.BLOCK_SIZE,
  290.          ts.LOGGING,
  291.          ts.FORCE_LOGGING,
  292.          ts.CONTENTS,
  293.          ts.EXTENT_MANAGEMENT,
  294.          ts.SEGMENT_SPACE_MANAGEMENT,
  295.          ts.RETENTION,
  296.          ts.DEF_TAB_COMPRESSION,
  297.          df.ts_df_count
  298.   FROM dba_tablespaces ts,
  299.          (SELECT 'D' TYPE,
  300.                  TABLESPACE_NAME,
  301.                  COUNT(*) ts_df_count,
  302.                  SUM(BYTES) all_bytes,
  303.                  SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ
  304.           FROM dba_data_files d
  305.           GROUP BY TABLESPACE_NAME
  306.           UNION ALL
  307.           SELECT 'T',
  308.                  TABLESPACE_NAME,
  309.                  COUNT(*) ts_df_count,
  310.                  SUM(BYTES) all_bytes,
  311.                  SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))
  312.           FROM dba_temp_files d
  313.           GROUP BY TABLESPACE_NAME) df,
  314.          (SELECT TABLESPACE_NAME,
  315.                  SUM(BYTES) FREESIZ
  316.           FROM dba_free_space
  317.           GROUP BY TABLESPACE_NAME
  318.           UNION ALL
  319.           SELECT tablespace_name,
  320.                  SUM(d.BLOCK_SIZE * a.BLOCKS) bytes
  321.           FROM gv$sort_usage a,
  322.                  dba_tablespaces d
  323.           WHERE a.tablespace = d.tablespace_name
  324.           GROUP BY tablespace_name) fs
  325.   WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME
  326.   AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))
  327. SELECT (SELECT A.TS#
  328.         FROM V$TABLESPACE A
  329.         WHERE A.NAME = UPPER(t.TABLESPACE_NAME)) TS#,
  330.        t.TABLESPACE_NAME TS_Name,
  331.        round(t.all_bytes / 1024 / 1024) ts_size_M,
  332.        round(t.freesiz / 1024 / 1024) Free_Size_M,
  333.        round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
  334.        round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 3) Used_per,
  335.        round(MAXSIZ / 1024 / 1024/1024, 3) MAX_Size_g,
  336.        round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 /
  337.              MAXSIZ,
  338.              3) USED_per_MAX,
  339.        round(t.BLOCK_SIZE) BLOCK_SIZE,
  340.        t.LOGGING,
  341.        t.ts_df_count
  342. FROM wt1 t
  343. UNION ALL
  344. SELECT to_number('') TS#,
  345.        'ALL TS:' TS_Name,
  346.        round(SUM(t.all_bytes) / 1024 / 1024, 3) ts_size_M,
  347.        round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m,
  348.        round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
  349.        round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 3) Used_per,
  350.        round(SUM(MAXSIZ) / 1024 / 1024/1024) MAX_Size,
  351.        to_number('') "USED,% of MAX Size",
  352.        to_number('') BLOCK_SIZE,
  353.        '' LOGGING,
  354.        to_number('') ts_df_count
  355. FROM wt1 t
  356. order by TS#
  357. ;


  358. WITH wt1 AS
  359.  (SELECT df.TABLESPACE_NAME,
  360.          df.all_bytes,
  361.          df.MAXSIZ,
  362.          nvl(fs.FREESIZ, 0) FREESIZ
  363.     FROM (SELECT TABLESPACE_NAME,
  364.                  SUM(BYTES) all_bytes,
  365.                  SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ
  366.             FROM dba_data_files d
  367.            GROUP BY TABLESPACE_NAME) df,
  368.          (SELECT TABLESPACE_NAME, SUM(BYTES) FREESIZ
  369.             FROM dba_free_space
  370.            GROUP BY TABLESPACE_NAME) fs
  371.    WHERE fs.TABLESPACE_NAME(+) = df.TABLESPACE_NAME)
  372. SELECT t.TABLESPACE_NAME TS_Name,
  373.        round(t.all_bytes / 1024 / 1024, 2) ts_size_m,
  374.        round(t.freesiz / 1024 / 1024, 2) Free_Size_m,
  375.        round((t.all_bytes - t.FREESIZ) / 1024 / 1024, 2) Used_Size_m,
  376.        round(MAXSIZ / 1024 / 1024 / 1024, 2) MAX_Size_g,
  377.        round((MAXSIZ - (t.all_bytes - t.FREESIZ)) / 1024 / 1024 / 1024, 2) MAX_Size_free_g,
  378.        (t.all_bytes) ts_size,
  379.        (t.freesiz) Free_Size,
  380.        (t.all_bytes - t.FREESIZ) Used_Size,
  381.        (MAXSIZ) MAX_Size,
  382.        ((MAXSIZ - (t.all_bytes - t.FREESIZ))) MAX_Size_free
  383.   FROM wt1 t;





  384. ------ 資料檔案情況
  385. SELECT d.FILE_ID,
  386.        d.TABLESPACE_NAME,
  387.        (SELECT round(SUM(nb.BYTES) / 1024 / 1024, 2)
  388.           FROM dba_data_files nb
  389.          WHERE nb.TABLESPACE_NAME = d.TABLESPACE_NAME) ts_size_m,
  390.        d.FILE_NAME,
  391.        round(d.BYTES / 1024 / 1024, 2) file_size_m,
  392.        round(d.MAXBYTES / 1024 / 1024 / 1024, 2) file_max_size_G,
  393.        d.AUTOEXTENSIBLE,
  394.        round(d.INCREMENT_BY * 8 * 1024 / 1024 / 1024, 2) INCREMENT_m,
  395.        round(d.BYTES * 100 / decode(d.MAXBYTES, 0, BYTES, d.MAXBYTES), 2) autoextend_ratio,
  396.        (SELECT b.CREATION_TIME
  397.           FROM sys.v_$datafile b
  398.          where b.FILE# = d.FILE_ID) CREATION_TIME,
  399.      d.INCREMENT_BY INCREMENT_BY_block,
  400.        d.BYTES,
  401.        d.blocks,
  402.        d.MAXBYTES,
  403.        d.MAXBLOCKS,
  404.        d.USER_BYTES,
  405.        d.USER_BLOCKS
  406.   FROM dba_data_files d
  407. UNION ALL
  408. SELECT d.FILE_ID,
  409.        d.TABLESPACE_NAME,
  410.        (SELECT round(SUM(nb.BYTES) / 1024 / 1024, 2)
  411.           FROM v$tempfile nb
  412.          WHERE nb.name = d.FILE_NAME) ts_size,
  413.        d.FILE_NAME,
  414.        round(d.BYTES / 1024 / 1024, 2) file_size_m,
  415.        round(d.MAXBYTES / 1024 / 1024 / 1024, 2) file_max_size_G,
  416.        d.AUTOEXTENSIBLE,
  417.        round(d.INCREMENT_BY * 8 * 1024 / 1024 / 1024, 2) INCREMENT_m,
  418.        round(d.BYTES * 100 / decode(d.MAXBYTES, 0, BYTES, d.MAXBYTES), 2) autoextend_ratio,
  419.        (SELECT b.CREATION_TIME
  420.           FROM sys.v_$datafile b
  421.          where b.FILE# = d.FILE_ID) CREATION_TIME,
  422.      d.INCREMENT_BY INCREMENT_BY_block,
  423.        d.BYTES,
  424.        d.blocks,
  425.        d.MAXBYTES,
  426.        d.MAXBLOCKS,
  427.        d.USER_BYTES,
  428.        d.USER_BLOCKS
  429.   FROM dba_temp_files d
  430.  ORDER BY TABLESPACE_NAME, file_id;



  431. -----檢視字符集
  432. select SYS_CONTEXT('USERENV', 'LANGUAGE') from dual;
  433. select userenv('language') from dual;
  434. select * from v$nls_parameters;


  435. ---密碼檔案 linux區分$ORACLE_SID大小寫 sysdba select* from v$pwfile_users;
  436. --linux:orapw+$ORACLE_SID
  437. --windows: pwd+$ORACLE_SID.ora
  438. oradim -NEW -sid orcl9i -INTPWD admin -pfile d:\oracle\ora90\database\initstorm.ora;
  439. C:\Users\Administrator> orapwd file="E:\oracle\ora8i\DATABASE\PWDortest.ORA" password=lhr
  440. [oracle@robinson dbs]$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle force=y

  441. sc delete OracleOraDb11g_home1TNSListener
  442. sc create OracleOraDb11g_home1TNSListener binpath= "F:\app\oracle\product\BIN\TNSLSNR" start= auto displayname= "OracleOraDb11g_home1TNSListener"


  443. --11g中密碼大小寫敏感
  444. (1) sec_case_sensitive_logon引數可以指定使用者的口令是否區分大小寫,預設為true,表示區分大小寫
  445. (2) 口令檔案中的ignorecase 引數僅僅針對建立時是否區分大小寫,簡言之,只能針對SYS使用者,預設為N,表示不忽略大小寫,即區分大小寫


  446. --通過設定EVENTS 28401可以遮蔽密碼延遲驗證:
  447. SQL> ALTER SYSTEM SET EVENT = '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' SCOPE = SPFILE;
  448. 設定該事件後重啟資料庫即可。




  449. ------------------------------------------------ selinux

  450. 檢視SELinux狀態:
  451. 1、/usr/sbin/sestatus -v      ##如果SELinux status引數為enabled即為開啟狀態
  452. SELinux status:                 enabled
  453. 2、getenforce                 ##也可以用這個命令檢查
  454. 關閉SELinux:
  455. 1、臨時關閉(不用重啟機器):
  456. setenforce 0                  ##設定SELinux 成為permissive模式
  457.                               ##setenforce 1 設定SELinux 成為enforcing模式
  458. 2、修改配置檔案需要重啟機器:
  459. 修改/etc/selinux/config 檔案
  460. 將SELINUX=enforcing改為SELINUX=disabled
  461. 重啟機器即可



  462. ------------------------------------------------------------ linux
  463. 儲存區域網路(Storage Area Network,SAN)

  464. ----設定密碼永不過期:
  465. chage -M -1 oracle
  466. chage -l oracle







  467. lsb_release-a
  468. cat /etc/issue
  469. uname -a
  470. cat /proc/version

  471. ---linux位數檢視
  472. file /bin/ls
  473. getconf LONG_BIT
  474. arch

  475. ---AIX系統檢視
  476.   
  477.   顯示AIX系統核心是32位還是64位:

  478.   bootinfo -K

  479.   顯示機器硬體是32位還是64位:

  480.   bootinfo -y

  481.  --- SUN:

  482.   $isainfo -bv

  483.   64表示核心是64位的,32表示核心是32位的

  484.  --- HPUX:

  485.   >getconf KERNEL_BITS

  486.   64表示核心是64位的,32表示核心是32位的

  487.   HPUX:

  488.   >getconf KERNEL_BITS

  489.   64表示核心是64位的,32表示核心是32位的



  490. ------------------ AIX系統使用者解鎖
  491. 3.1 AIX使用者賬戶鎖定與解鎖最佳方法
  492. 3004-303 There have been too many unsuccessful login attempts; please see
  493.         the system administrator.

  494. 與之相關的配置引數是/etc/security/login.cfg的以下配置項

  495. logindisable=7              *7次失敗登入後鎖定埠
  496. logininterval=120            *在120秒內7次失敗登入才鎖定埠

  497. 1、如果你可以登陸到ROOT賬戶,比較簡單
  498. 使用chsec命令即可解鎖,具體如下:
  499. # chsec -/etc/security/lastlog -a unsuccessful_login_count=0 -s  username
  500. 通過重置未成功登陸的次數即可解鎖

  501. 2、如果是通過設定來鎖定的使用者,可以這樣解鎖
  502. #【smitty user】-->【Lock / Unlock a User's Account】
  503. 或是
  504. # chsec -/etc/security/lastlog -a unsuccessful_login_count=0 -s  username

  505. 3、命令解鎖
  506. # chuser account_locked=TRUE username  給使用者加鎖
  507. # chuser accout_locked=FALSE username   給使用者解鎖



  508. ------------大寫G跳到最後一行,o新插入一行
  509. AIX開啟自動補全:
  510. 方法一:
  511. set -o vi
  512.   自動補全 esc \
  513.   歷史命令 esc -
  514. HJKL
  515. 左下上右

  516. A 跳到行末,進入編輯模式
  517. I 跳到行首,進入編輯模式
  518. X 鍵刪除游標前一個字元停留在原來的那個字元

  519. AIX開啟自動補全:
  520. 方法一:
  521. set -o vi
  522.   自動補全 esc \
  523.   歷史命令 esc -
  524.      esc j
  525.      esc k
  526. i a x 編輯
  527. 游標移動 : h l
  528. HJKL
  529. 左下上右

  530. A 跳到行末,進入編輯模式
  531. I 跳到行首,進入編輯模式
  532. X 鍵刪除游標前一個字元停留在原來的那個字元




  533. 方法二:
  534. set -o emacs
  535.    自動補全 按兩次esc
  536.     歷史命令 ctrl-n 或 ctrl-p





  537. more /etc/profile
  538. more /etc/environment
  539. export TMOUT=0;


  540. oslevel -qs



  541. ---清磁碟頭
  542. dd if=/dev/zero of=/dev/rhdisk5 bs=1024 count=1024



  543. ##檢視PV大小,單位M AIX 查詢磁碟大小 硬碟大小
  544. for HDISK in `lspv | grep -v hdisk0 | awk '{print $1}'`;do
  545.     bootinfo -s $HDISK
  546. done
  547. for diskname in `lspv|grep -i none|cut -f "1" -d ' '`
  548. do
  549.  echo "/dev/r$diskname" `getconf DISK_SIZE /dev/r$diskname`
  550. done





  551. ---程式控制程式碼

  552. lsof -p pid

  553. ---告警日誌位置
  554. lsof | grep diag
  555. lsof | grep bdump


  556. show parameter background_dump_dest
  557. $ORACLE_BASE/ADMIN/SID/BDUMP/ALERTSID.LOG

  558. --根據實際情況決定是否加upper函式
  559. SELECT VALUE || substr(d.VALUE, -6, 1) || 'alert_' || b.INSTANCE_Name ||'.log' alertname
  560.    FROM v$parameter d, v$instance b
  561.   WHERE d.NAME = 'background_dump_dest';




  562. --1 埠是否佔用
  563. netstat -apn | grep 1521
  564. netstat -ano|grep 1521
  565. netstat -lnp|grep 1521

  566. ----檢查包忽略大小寫
  567. rpm -qa | grep -i AAA

  568. --2 殺死所有程式
  569. kill -9 `ps -ef|grep orcl| grep -v grep | awk '{print $2}'`

  570. ps -ef |grep $ORACLE_SID|grep -v grep|awk '{print $2}' | xargs kill -9
  571. ipcs -m | grep oracle | awk '{print $2}' | xargs ipcrm shm




  572. --程式總數
  573. ps -ef|grep orcl| wc -l

  574. ---統計行數
  575. wc - lcw a.txt

  576.  - c 統計位元組數。

  577.   - l 統計行數。

  578.   - w 統計字數。


  579. -- 匹配多個程式號
  580. [root@node1 node1]# ps -ef | grep ' 27373 \| 27182 '
  581. oracle 27182 1 0 14:50 ? 00:00:00 ora_pz99_jmrac1
  582. oracle 27373 1 0 14:53 ? 00:00:00 ora_w000_jmrac1
  583. root 27574 9150 0 14:56 pts/1 00:00:00 grep 27373 \| 27182
  584. [oracle@orcltest shm]$ ps -ef | egrep '(12545|12543)' |grep -v grep
  585. oracle 12543 1 0 07:41 ? 00:00:02 ora_pmon_ogg1
  586. oracle 12545 1 0 07:41 ? 00:00:04 ora_psp0_ogg1
  587. [oracle@orcltest shm]$



  588. -- 匹配多個字串
  589. [root@node1 node1]# ps -ef | grep -v grep | grep -E "ohasd.bin|crs|ocssd|evmd|oproc"
  590. root 2372 1 0 09:33 ? 00:00:25 /u01/grid/bin/crsd.bin reboot
  591. root 5051 1 0 05:36 ? 00:01:02 /u01/grid/bin/ohasd.bin reboot
  592. grid 5313 1 0 05:37 ? 00:01:45 /u01/grid/bin/ocssd.bin
  593. grid 5500 1 0 05:37 ? 00:00:13 /u01/grid/bin/evmd.bin
  594. [root@node1 node1]#


  595. --3 關閉防火牆
  596. chkconfig iptables off ---永久
  597. service iptables stop ---臨時
  598. chkconfig iptables --list
  599. /etc/init.d/iptables status ----會得到一系列資訊,說明防火牆開著。
  600. /etc/rc.d/init.d/iptables stop ----------關閉防火牆
  601. setup ----------圖形介面

  602. --將/etc/sysconfig/iptables檔案新增一行,表示允許1521埠訪問:
  603. [root@dcsopen2Node sysconfig]# vi /etc/sysconfig/iptables
  604.  # Firewall configuration written by system-config-firewall
  605.  # Manual customization of this file is not recommended.
  606.  *filter
  607.  :INPUT ACCEPT [0:0]
  608.  :FORWARD ACCEPT [0:0]
  609.  :OUTPUT ACCEPT [0:0]
  610.  -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
  611.  -A INPUT -p icmp -j ACCEPT
  612.  -A INPUT -i lo -j ACCEPT
  613.  -A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
  614.  -A INPUT -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT
  615.  -A INPUT -j REJECT --reject-with icmp-host-prohibited
  616.  -A FORWARD -j REJECT --reject-with icmp-host-prohibited
  617.  COMMIT



  618. find / -type f -size +10000000c -exec du -sh {} \; 查詢大於10M的檔案
  619. find . -name '*.phtml' -type f -mmin -30 查詢當前目錄下.phtml檔案中,最近30分鐘內修改過的檔案。
  620. find . -name '*.phtml' -type f -mmin -30 -ls 查詢當前目錄下.phtml檔案中,最近30分鐘內修改過的檔案,的詳細情況。
  621. find . -type f -mtime -1 查詢當前目錄下,最近1天內修改過的常規檔案
  622. find . -type f -mtime +1 查詢當前目錄下,最近1天前(2天內)修改過的常規檔案。

  623. find . -ctime +3 -exec rm -rf {} \; #刪除一個目錄下幾天前的檔案和目錄

  624. find / -type f -size +10000000c -exec du -sh {} \; 2>/dev/null #查詢大於10M的檔案並列出檔案大小
  625. find /home -size +10k #意思是說查詢/home目錄下大小為10k的檔案

  626. find . -type f -mtime 0
  627. find . -type f -mtime +1
  628. find . -name '*.doc' -mtime 0

  629. find / -name access_log 2>/dev/null
  630. find . -name '*.doc' 2>/dev/null


  631. find / -amin -10 # 查詢在系統中最後10分鐘訪問的檔案
  632. find / -atime -2 # 查詢在系統中最後48小時訪問的檔案

  633. find /tmp -size +10000000c -and -mtime +2
  634. find /tmp -size +10000000c -or -mtime +2

  635. find / -empty # 查詢在系統中為空的檔案或者資料夾
  636. find / -group cat # 查詢在系統中屬於 groupcat的檔案
  637. find / -mmin -5 # 查詢在系統中最後5分鐘裡修改過的檔案
  638. find / -mtime -1 #查詢在系統中最後24小時裡修改過的檔案
  639. find / -nouser #查詢在系統中屬於作廢使用者的檔案
  640. find / -user fred #查詢在系統中屬於FRED這個使用者的檔案


  641. find . -type f -mtime 0 -exec ls -lrt {} \; --檢視當天修改過的檔案


  642. find . -type f -mtime 0 #最近24小時內修改過的檔案
  643. find . -type f -mtime 1 #前48~24小時內修改過的檔案,而不是48小時以內修改過的檔案
  644. ---近3天內修改過的檔案
  645. find . -type f -mtime 0 -o -mtime 1 -o -mtime 2
  646. find . -type f -mtime 0 -or -mtime 1 -or -mtime 2













  647. 目錄大小: du -h --max-depth=1 . 2>&1
  648.            du -h --max-depth=0 /tmp/database/
  649.      du -sh database/
  650.      find . -ctime +3 -exec rm -rf {} \; 刪除一個目錄下幾天前的檔案和目錄
  651. find / -type f -size +10000000c -exec du -sh {} \; 查詢大於10M的檔案
  652. du -s /*|sort -rn 檢視目錄大小


  653. /**/

  654. -------- AIX資料夾大小
  655. du -sg app/11.2.0/grid/* | sort -rn                                /* */
  656. du -ag app/11.2.0/grid/* | sort -rn                                /* */
  657. du -g /oracle/app/11.2.0/* | sort -rn | more                                 /* */




  658. 系統啟動時間:
  659. date -d "$(awk -F. '{print $1}' /proc/uptime) second ago" +"%Y-%m-%d %H:%M:%S"




  660. create public database link dblink_ogg1
  661. connect to lhr identified by lhr
  662.  using '(DESCRIPTION =
  663.    (ADDRESS_LIST =
  664.    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.129)(PORT = 1521))
  665.    )
  666.    (CONNECT_DATA =
  667.            (SERVICE_NAME = ogg1)
  668.        )
  669.    )';


  670. create public database link DBLINK_OGG1
  671.   connect to LHR identified by lhr
  672.   using 'OGG1';



  673. ----linux檢視檔案的詳細時間
  674. ls ctlfile*20150212* --full-time|awk '{print $6,$7,$9}'


  675. -----------新增磁碟
  676. fdisk -l
  677. fdisk /dev/sdf
  678. ...
  679. mkfs.ext4 /dev/sdf1
  680. [root@rhel6_lhr ~]# mkdir /u03
  681. [root@rhel6_lhr ~]# mount /dev/sdf1 /u03
  682. [root@rhel6_lhr ~]# vi /etc/fstab
  683.  /dev/sdf1 /u03 ext4 defaults 0 0


  684.   chown oracle:oinstall /u04


  685. linux 修改主機名,永久生效:
  686. vim /etc/sysconfig/network
  687. vim /etc/hosts


  688. aix 修改主機名:
  689. 修改主機名暫時生效:
  690. hostname NEW_HOSTNAME
  691. 永久生效 smit hostname
  692. 或者 smit tcpip - futher configureation - hostname -set the hostname
  693. uname -S hostname
  694. 或者直接用命令 chdev -l inet0 -a hostname=NEW_HOSTNAME

  695. ||||||||||||||||||||
  696. 正確更改IP 地址是用 smit tcpip 進入選單之後,選擇further configuration 再選 Network Interfaces,再選 Network Interface Selection,
  697. 再選 Change /show characteristic of a network interface來更改 IP,這樣/etc/hosts就不會新加入一條記錄,只需更改檔案中相應的IP就行了。





  698. -------------------------------------------------------------- 閃回恢復區滿
  699. select * from v$flash_recovery_area_usage;
  700. ALTER SYSTEM SET db_recovery_file_dest_size='2G';

  701. --關閉閃回恢復區
  702. alter system set db_recovery_file_dest='';
  703.     

  704. --EXPIRED可以理解為失效的備份集,即物理檔案丟失。OBSOLETE可以理解為過期的備份集。
  705. CROSSCHECK ARCHIVELOG ALL;
  706. LIST EXPIRED ARCHIVELOG ALL;
  707. DELETE EXPIRED ARCHIVELOG ALL;
  708.     



  709. --錯誤記錄
  710. v_error := SQLCODE || ',' || SQLERRM || chr(13) ||dbms_utility.format_error_backtrace;
  711. --DML行數
  712. v_count :=TO_CHAR(SQL%ROWCOUNT);


  713. -----------塊改變跟蹤
  714. alter system set db_create_file_dest = '/u01/bct/' scope=both sid='*';
  715. alter database enable block change tracking;

  716. ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+FRA';
  717. ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/mydir/rman_change_track.f' REUSE;

  718. ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
  719. COL STATUS FORMAT A8
  720. COL FILENAME FORMAT A60
  721. SELECT STATUS, FILENAME,BYTES FROM V$BLOCK_CHANGE_TRACKING;



  722. SELECT file#,
  723.        AVG(datafile_blocks),
  724.        AVG(blocks_read),
  725.        AVG(blocks_read / datafile_blocks) * 100 AS PCT_READ_FOR_BACKUP,
  726.        AVG(blocks)
  727. FROM v$backup_datafile
  728. WHERE used_change_tracking = 'YES'
  729. AND incremental_level > 0
  730. GROUP BY file#;



  731. -------------------------------------------------------------- 聯機重做日誌
  732. ----------清除未歸檔日誌
  733. ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
  734. ----------清空損壞的日誌檔案 成功執行前提:一致性關閉,解決ora-00392錯誤
  735. ALTER DATABASE CLEAR LOGFILE GROUP 2;


  736. ----- 新增 redo
  737. alter database add logfile group 4 'c:\oracle\oradata\orcl\redo04.log' size 100m;
  738. alter database add logfile;
  739. alter database add logfile [group n];
  740. alter database add logfile member '<dir>' to group [n] /*add logfile member這個方法僅使用未使用OMF的日誌檔案,對於已經運用了OMF的日誌組,無法使用該功能新增日誌檔案*/

  741. alter database add logfile thread 1 group 4 ('+DATA','+FRA') size 50M;


  742. ---rac庫可以在同一個例項下新增
  743. alter database add logfile thread 1 group 7('+DATA_DG/querydb/redo07_1.log','+DATA_DG/querydb/redo07_2.log') size 1024m;
  744. alter database add logfile thread 2 group 8('+DATA_DG/querydb/redo08_1.log','+DATA_DG/querydb/redo08_2.log') size 1024m;



  745.  
  746. --- 刪除
  747. alter database drop logfile group 4;
  748. alter database drop logfile member '';



  749. --------重新命名redo
  750. SQL> ho cp /u03/app/oracle/oradata/ora1024g/redo03.log /u03/app/oracle/oradata/ora1024g/redo04.log
  751. SQL> alter database rename file '/u03/app/oracle/oradata/ora1024g/redo03.log' to '/u03/app/oracle/oradata/ora1024g/redo04.log';


  752. --------重新命名 表空間 重新命名錶空間
  753. alter tablespace users rename to users01;





  754. ----------------- 一個查詢慢的sql例子
  755. select count(1) from dba_objects a
  756. inner join user_objects b on 1=1
  757. inner join user_objects c on 1=1
  758. ;

  759. ------------------------------------------------------------------------------------------------------------------------------------------------------------------- 構造大表

  760. select level,level from dual connect by level<=1000;


  761. -----------外部表
  762. CREATE DIRECTORY EXT_LOG AS '/tmp';
  763. DROP TABLE ALERT_LOG_lhr2;
  764. CREATE TABLE ALERT_LOG_lhr2(
  765.   TEXT VARCHAR2(4000)
  766.   )ORGANIZATION EXTERNAL
  767.   (TYPE ORACLE_LOADER
  768.   DEFAULT DIRECTORY EXT_LOG
  769.   ACCESS PARAMETERS
  770.   (RECORDS DELIMITED BY NEWLINE CHARACTERSET utf8
  771.     nobadfile
  772. nodiscardfile
  773. nologfile
  774. FIELDS TERMINATED BY 0X'0D' LDRTRIM
  775. REJECT ROWS WITH ALL NULL FIELDS
  776.   )LOCATION('lhr1.txt')
  777. ) reject limit unlimited ;



  778. ----------檔案格式
  779. df -hT

  780. --------------ORA-00845: MEMORY_TARGET not supported on this system
  781. 辦法: 修改/etc/fstab
  782.  tmpfs /dev/shm tmpfs defaults,size=1.5G 0 0

  783. [root@FWDB ~]# mount -o remount /dev/shm



  784.  簡單來說就是 MEMORY_MAX_TARGET 的設定不能超過 /dev/shm 的大小:

  785.  
  786. [oracle@FWDB FWDB]$ df -h | grep shm
  787. tmpfs 2.0G 0 2.0G 0% /dev/shm

  788.       馬上把它加大:

  789.  
  790. [root@FWDB ~]# cat /etc/fstab | grep tmpfs
  791. tmpfs /dev/shm tmpfs defaults,size=4G 0 0
  792.       現在可以通過重啟使這個配置生效,也可以通過重新掛載來修改其大小:
  793.  
  794. [root@FWDB ~]# mount -o remount,size=4G /dev/shm
  795. [root@FWDB ~]# df -h | grep shm
  796. tmpfs 4.0G 0 4.0G 0% /dev/shm
  797.       再次啟動資料庫,沒有報錯了。

  798. 二、修改/dev/shm大小
  799.  
  800. 預設的最大一半記憶體大小在某些場合可能不夠用,並且預設的inode數量很低一般都要調高些,這時可以用mount命令來管理它。
  801. #mount -o size=1500M -o nr_inodes=1000000 -o noatime,nodiratime -o remount /dev/shm
  802. 在2G的機器上,將最大容量調到1.5G,並且inode數量調到1000000,這意味著大致可存入最多一百萬個小檔案。
  803.  如果需要永久修改/dev/shm的值,需要修改/etc/fstab
  804.  tmpfs /dev/shm tmpfs defaults,size=1.5G 0 0

  805. mount -o remount /dev/shm


  806. umount tmpfs
  807. mount -t tmpfs shmfs -o size=3000m /dev/shm
  808. vi /etc/fstab




  809. ---red hat重啟網路卡
  810. service network restart
  811. /etc/rc.d/init.d/network restart

  812. ---suse重啟網路卡
  813. service network restart
  814. rcnetwork restart
  815. /etc/rc.d/init.d/network restart


  816. ----解除安裝網路卡
  817. ifconfig eth0 down

  818. --- 單獨重啟網路卡
  819. ifdown eth0 && ifup eth0
  820. ifconfig eth0 down && ifconfig eth0 up


  821. ----------------------------------------------- 固定IP 配置靜態ip地址
  822. ifconfig eth1 192.168.210.108 broadcast 192.168.210.254 netmask 255.255.255.0
  823. ifconfig eth1 192.168.59.130 broadcast 192.168.59.1 netmask 255.255.255.0
  824. ifconfig eth0 192.168.59.130 netmask 255.255.255.0 gw 192.168.129.1



  825. chkconfig NetworkManager off
  826. chkconfig network on
  827. service NetworkManager stop
  828. service network start

  829. ---若還有問題,可刪掉網路卡重新新增
  830. vi /etc/sysconfig/network-scripts/ifcfg-eth0
  831. vi /etc/udev/rules.d/70-persistent-net.rules
  832. DEVICE=eth0
  833. IPADDR=192.168.59.130
  834. NETMASK=255.255.255.0
  835. NETWORK=192.168.59.0
  836. BROADCAST=192.168.59.255
  837. GATEWAY=192.168.59.2
  838. ONBOOT=yes
  839. USERCTL=no
  840. BOOTPROTO=static
  841. #HWADDR=00:0c:29:97:f1:5b
  842. TYPE=Ethernet
  843. IPV6INIT=no
  844. DNS1=202.96.209.5
  845. DNS2=8.8.8.8
  846. NAME="System eth0"





  847. ----------------- 動態ip地址
  848. DEVICE=eth0
  849. ONBOOT=yes
  850. USERCTL=no
  851. BOOTPROTO=dhcp
  852. HWADDR=00:0c:29:97:f1:5b
  853. TYPE=Ethernet
  854. PEERDNS=yes
  855. IPV6INIT=no





  856. [root@rhel6 ~]# export LANG=C
  857. [root@rhel6 ~]# setup
  858. [root@rhel6 ~]#




  859. ------------------------------------------------- 修改主機名
  860. 永久生效:
  861. [root@zijuan /]# vim /etc/sysconfig/network
  862. NETWORKING=yes
  863. NETWORKING_IPV6=yes
  864. HOSTNAME=zijuan

  865. HOSTNAME=zijuan表示主機設定為zijuan.
  866. 注意:修改主機名後,需要重啟系統後生效,或者切換個使用者然後切換回來就OK


  867. 檢視/etc/hosts檔案中必須包含a fully qualified name for the server
  868.  [root@localhost lhr]# cat /etc/hosts
  869. 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
  870. ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
  871. 192.168.59.140 dg
  872. [root@localhost lhr]# hostname
  873. localhost.localdomain
  874. [root@localhost lhr]# hostname dg
  875. [root@localhost lhr]# hostname
  876. dg









  877. -----歸檔格式
  878. alter system set log_archive_format = "log_oradg10g_%d_%t_%s_%r.arc" scope=spfile;



  879. ---------------------------------------- drop database 刪除資料庫

  880. 1、dbca靜默刪庫:dbca -silent -deleteDatabase -sourceDB mydb
  881. 2、SQL視窗:
  882. alter database close;
  883. alter system enable restricted session;
  884. drop database;
  885. 3、SQL視窗:
  886. sql > startup force mount restrict;
  887. sql > drop database;
  888. 注意:強烈推薦第一種辦法,以上2和3的辦法若是rac庫需要設定cluster_database為false後才可以執行drop database,命令為:alter system set cluster_database=false sid='*' scope=spfile;




  889. -------------------------------------- 配置本地yum源

  890. -----------rhel 6.5
  891. mkdir -p /media/lhr/cdrom
  892. mount /dev/sr0 /media/lhr/cdrom/
  893. #設定開機自動掛載系統映象檔案 vi /etc/fstab 新增以下內容
  894. /dev/sr0 /media/cdrom iso9660 defaults,ro,loop 0 0

  895. cd /etc/yum.repos.d/
  896. cp rhel-media.repo rhel-media.repo.bk
  897. vi /etc/yum.repos.d/rhel-media.repo
  898. [rhel-media]
  899. name=Red Hat Enterprise Linux 6.5
  900. baseurl=file:///media/cdrom
  901. enabled=1
  902. gpgcheck=1
  903. gpgkey=file:///media/cdrom/RPM-GPG-KEY-redhat-release


  904. yum install httpd  #安裝命令
  905. yum install -y *sz*
  906. rpm -ivh lrzsz-0.12.20-27.1.el6.x86_64.rpm

  907. yum -y remove mysql-libs-5.1.71
  908. yum list | grep mysql
  909. rpm -e --nodeps mysql-libs.x86_64

  910. -----------------命令後rpm包被下載到了什麼地方
  911. 每次在執行完yum命令後,系統都會把需要用到的rpm包放在/var/cache/yum/這個目錄下,但下載源的不同還是會放在不同源目錄下。
  912. find /var/cache/yum/ -name kmod-oracleasm*



  913. -------------- rhel5.5
  914. # mkdir /media/cdrom
  915. 編輯 /etc/fstab 檔案,在檔案尾部新增如下內容,以便開機自動掛載光碟:
  916. [root@localhost ~]# tail -1 /etc/fstab
  917. /dev/cdrom /media/cdrom iso9660 defaults 0 0
  918. [root@localhost ~]#
  919. [root@localhost ~]# mount -a
  920. mount: block device /dev/sr0 is write-protected, mounting read-only
  921. [root@localhost ~]#
  922. 清空並編輯 YUM 源配置檔案
  923. 清空 /etc/yum.repos.d/rhel-debuginfo.repo 檔案並新增以下內容:
  924. [root@localhost ~]# cp /etc/yum.repos.d/rhel-debuginfo.repo /etc/yum.repos.d/rhel-debuginfo.repo.bak
  925. [root@localhost ~]# cat /etc/yum.repos.d/rhel-debuginfo.repo
  926. [rhel-debuginfo]
  927. name=Red Hat Enterprise Linux $releasever - $basearch - Debug
  928. baseurl=file:///media/cdrom
  929. enabled=0
  930. gpgcheck=0

  931. # vi /etc/yum.repos.d/my.repo
  932.  [Oracle]
  933.  name=OEL-$releasever – Media
  934.  baseurl=file:///mnt/Server
  935.  gpgcheck=0
  936.  enabled=1
  937.  如果是RHEL或者CentOS,請先將/etc/yum.repos.d下面的檔案刪除或者移動到別的目錄下,RHEL建立方法和OEL一樣,CentOS則baseurl=file:///mnt/ 即可,因為CentOS的repodata目錄就在光碟根下。




  938. ------檢視資源的屬性值:
  939. [root@rac2 ~]# crsctl stat res ora.cluster_interconnect.haip -p -init | grep ENABLED
  940. ENABLED=0
  941. [root@rac2 ~]# crsctl stat res ora.asm -p -init | grep START_DEPENDENCIES
  942. START_DEPENDENCIES=hard(ora.cssd,ora.ctssd)pullup(ora.cssd,ora.ctssd)weak(ora.drivers.acfs)
  943. [root@rac2 ~]#


  944. ---修改資源的屬性值
  945. crsctl modify resource ora.<diskgroup>.dg -attr AUTO_START=always

  946. ---啟動磁碟組
  947. srvctl start diskgroup -g data -n "rac2"


  948. ------------- 新增rac資料庫到叢集
  949. 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

  950. srvctl add instance -d DGPHY -i DGPHY1 -n ZFZHLHRDB1
  951. srvctl add instance -d DGPHY -i DGPHY2 -n ZFZHLHRDB2

  952. srvctl status database -d DGPHY
  953. srvctl start database -d TESTDG

  954. srvctl remove database -d DGPHY

  955. --新增監聽到crs 必須是grid新增
  956. [ZFZHLHRDB3:grid]:/home/grid>srvctl add listener -l LISTENER_LHRDG -p 1523 -o $ORACLE_HOME
  957. srvctl config listener -l LISTENER_LHRDG -a
  958. ① rac新增LISTENER資源的時候需要使用grid使用者
  959. ② srvctl config檢視資源的具體配置情況
  960. ③ srvctl modify可以修改資源的配置


  961. --新增單例項
  962. 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


  963.  

  964. crsctl status resource ora.asm -f
  965. crsctl modify resource ora.asm -attr "GEN_USR_ORA_INST_NAME@SERVERNAME(zfxdeskdb1)=+ASM1"
  966. crsctl modify resource ora.asm -attr "GEN_USR_ORA_INST_NAME@SERVERNAME(zfxdeskdb2)=+ASM2"
  967. srvctl stop asm -f



  968. -------------- 11g rac 修改歸檔 alter system set log_archive_dest_1='LOCATION=/arch/DGPHY' scope=spfile sid='*'; 
  969. SQL>alter system set log_archive_dest_1='LOCATION=+FRA/GUITAR/ARC1' scope=spfile sid='guitar1'; 
  970. Diskgroup altered.  
  971.   
  972. SQL>alter system set log_archive_dest_1='LOCATION=+FRA/GUITAR/ARC2' scope=spfile sid='guitar2'; 
  973. Diskgroup altered.  
  974.   
  975. 乾淨關閉資料庫然後啟動庫到mount,在其中一個例項上執行alter database archivelog 然後開啟資料庫即可。

  976. [root@node1 ~]# srvctl stop database -d jmrac -o immediate
  977. [root@node1 ~]# srvctl start database -d jmrac -o mount



  978. ------------------ mgmtdb
  979. srvctl stop mgmtdb
  980. srvctl status mgmtdb
  981. srvctl config mgmtdb
  982. srvctl disable mgmtdb
  983. srvctl disable mgmtlsnr
  984. srvctl add mgmtdb
  985. srvctl config mgmtdb
  986. srvctl disable mgmtdb
  987. srvctl enable mgmtdb
  988. srvctl getenv mgmtdb
  989. srvctl modify mgmtdb
  990. srvctl relocate mgmtdb
  991. srvctl remove mgmtdb
  992. srvctl setenv mgmtdb
  993. srvctl start mgmtdb
  994. srvctl status mgmtdb
  995. srvctl stop mgmtdb
  996. srvctl unsetenv mgmtdb
  997. srvctl add mgmtlsnr
  998. srvctl config mgmtlsnr
  999. srvctl disable mgmtlsnr
  1000. srvctl enable mgmtlsnr
  1001. srvctl getenv mgmtlsnr
  1002. srvctl modify mgmtlsnr
  1003. srvctl remove mgmtlsnr
  1004. srvctl setenv mgmtlsnr
  1005. srvctl start mgmtlsnr
  1006. srvctl status mgmtlsnr
  1007. srvctl stop mgmtlsnr
  1008. srvctl unsetenv mgmtlsnr


  1009. [grid@raclhr-12cR1-N1 ~]$ export ORACLE_SID=-MGMTDB
  1010. [grid@raclhr-12cR1-N1 ~]$ sqlplus / as sysdba

  1011. Database unique name: _mgmtdb
  1012. Database name: _mgmtdb
  1013. Oracle user: grid
  1014. Database instance: -MGMTDB
  1015. service_names:_mgmtdb


  1016. ------------------------------- export display
  1017. export DISPLAY=192.168.59.1:0.0
  1018. xhost +



  1019. 在linux系統中用Oracle帳號執行DBCA或其他JAVA圖形介面程式時,報錯:

  1020. Xlib: connection to ":0.0" refused by server
  1021.  Xlib: No protocol specified

  1022.  Error: Can't open display: :0.0

  1023. 解決辦法:用root登陸,在#提示符後輸入:

  1024. xhost local:oracle


  1025. -----------------------------iSCSI target
  1026. more /etc/ietd.conf --配置檔案
  1027. service iscsi-target start #啟動iSCSI target
  1028. cat /proc/net/iet/volume #檢視iSCSI-target共享出的硬碟
  1029. cat /proc/net/iet/session #檢視客戶端(initiator端)登陸到target的情況


  1030. -----------------------------iSCSI initiator
  1031. more /etc/iscsi/initiatorname.iscsi
  1032. more /etc/iscsi/iscsid.conf

  1033. iscsiadm -m discovery -t sendtargets -p 192.168.59.200:3260
  1034. iscsiadm -m node -T iqn.2006-01.com.openfiler:tsn.5e423e1e4d90 -p 192.168.59.200:3260 -l

  1035. iscsiadm --mode discovery --type sendtargets --portal 192.168.59.200
  1036. iscsiadm --mode node --targetname iqn.2006-01.com.openfiler:tsn.5e423e1e4d90 –portal 192.168.59.200:3260 --login
  1037. iscsiadm -m session -P 3


  1038. service iscsi start #啟動iSCSI initiator
  1039. /etc/init.d/iscsi start

  1040. service iscsi start
  1041. service iscsid start

  1042. chkconfig iscsi on
  1043. chkconfig iscsid on

  1044. chkconfig --level 2345 iscsi on
  1045. chkconfig --list|grep iscsi





  1046. -------------------------- 多路徑常用命令
  1047. rpm -qa|grep multipath
  1048. modprobe dm-multipath
  1049. modprobe dm-round-robin
  1050. lsmod |grep multipath
  1051. chkconfig --level 2345 multipathd on
  1052. chkconfig --list|grep multipathd
  1053. chkconfig --list multipathd
  1054. multipath -ll

  1055. ---------------獲取wwid
  1056. --在RHEL 6中,可以通過如下方式獲取磁碟wwid:
  1057. for i in `cat /proc/partitions | awk {'print $4'} |grep sd`; do echo "### $i: `scsi_id --whitelist /dev/$i`"; done
  1058. -- 在RHEL 5中,可以通過如下方式獲取磁碟wwid:
  1059. for i in `cat /proc/partitions | awk {'print $4'} |grep sd`; do echo "### $i: `scsi_id -g -u -s /block/$i`"; done



  1060. -------------------------------------------------------------- ASM
  1061. ---------------------------- oracleasm常用命令
  1062. /usr/sbin/oracleasm configure -i
  1063. /usr/sbin/oracleasm createdisk DISK1 /dev/sdb1
  1064. /etc/init.d/oracleasm scandisks
  1065. /etc/init.d/oracleasm listdisks
  1066. /etc/init.d/oracleasm enable
  1067. /usr/sbin/oracleasm enable
  1068. /usr/sbin/oracleasm restart
  1069. /usr/sbin/oracleasm createdisk DISKNAME devicename
  1070. /usr/sbin/oracleasm deletedisk DISKNAME
  1071. /usr/sbin/oracleasm querydisk {DISKNAME | devicename}
  1072. /usr/sbin/oracleasm listdisks
  1073. /usr/sbin/oracleasm scandisks





  1074. ------ ASM磁碟
  1075. $ORACLE_HOME/bin/kfod disk=asm s=true ds=true c=true
  1076. /grid/stage/ext/bin/kfod disk=asm s=true ds=true c=true



  1077. create diskgroup DATA external redundancy disk '/dev/raw/raw*';
  1078. create diskgroup FRA external redundancy disk '/dev/rhdisk3'; --建立磁碟組FRA
  1079. CREATE DISKGROUP ACFSDG external redundancy DISK '/dev/oracleasm/disks/VOL1' ATTRIBUTE 'compatible.asm' = '11.2','compatible.rdbms' = '11.2','compatible.advm'='11.2';
  1080. create diskgroup OCR exteRnal redundancy disk 'ORCL:OVDISK' attribute 'compatible.asm'='11.2','compatible.rdbms'='11.2';




  1081. ---修改磁碟組的相容屬性
  1082. ALTER DISKGROUP asm_dg SET ATTRIBUTE 'compatible.asm' = '11.1';
  1083. ALTER DISKGROUP asm_dg SET ATTRIBUTE 'compatible.rdbms' = '11.1';

  1084. COLUMN name FORMAT A10
  1085. COLUMN compatibility FORMAT A20
  1086. COLUMN database_compatibility FORMAT A20
  1087. SELECT group_number, name, compatibility, database_compatibility FROM v$asm_diskgroup;

  1088. set line 9999
  1089. set pagesize 9999
  1090. col path format a60
  1091. SELECT a.group_number, disk_number,mount_status, a.name, path FROM v$asm_disk a order by a.disk_number;
  1092. select instance_name,status from v$instance;

  1093. set line 999
  1094. 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;
  1095. select a.group_number,name,TYPE,state,TOTAL_MB,free_mb from v$asm_diskgroup a;
  1096. select name,state,free_mb,required_mirror_free_mb,usable_file_mb,a.group_number from v$asm_diskgroup a;




  1097. alter diskgroup DG1 mount;


  1098. ---nomount狀態下強制刪除磁碟組
  1099. drop diskgroup oradg force including contents;
  1100. alter diskgroup DG1 drop disk DG1_VOL5; --刪除磁碟組DG1中的磁碟VOL5

  1101. alter system set asm_diskstring='/dev/asm-disk*','/dev/raw/raw*';
  1102. alter diskgroup DATA add disk '/dev/raw/raw1';




  1103. ------------------------ faking asmdisk asm磁碟
  1104. ---- 新增loop裝置個數
  1105. 第一種辦法:修改 /etc/modprobe.conf 檔案新增引數:options loop max_loop=20 可以通過 modprobe -v loop 命令立即載入該模組,或重啟
  1106. 第二種辦法(通用):mknod -m 0660 /dev/loopX b 7 X

  1107. raw -qa
  1108. losetup -a
  1109. --mknod -m 0660 /dev/loopX b 7 X
  1110. mknod -m 0660 /dev/loop9 b 7 9



  1111. mkdir /asmdisk
  1112. dd if=/dev/zero of=/asmdisk/disk1 bs=1024k count=2000
  1113. dd if=/dev/zero of=/asmdisk/disk2 bs=1024k count=2000
  1114. dd if=/dev/zero of=/asmdisk/disk3 bs=1024k count=2000
  1115. dd if=/dev/zero of=/asmdisk/disk4 bs=1024k count=2000
  1116. dd if=/dev/zero of=/asmdisk/disk5 bs=1024k count=2000

  1117. /sbin/losetup /dev/loop1 /asmdisk/disk1
  1118. /sbin/losetup /dev/loop2 /asmdisk/disk2
  1119. /sbin/losetup /dev/loop3 /asmdisk/disk3
  1120. /sbin/losetup /dev/loop4 /asmdisk/disk4
  1121. /sbin/losetup /dev/loop5 /asmdisk/disk5

  1122. raw /dev/raw/raw1 /dev/loop1
  1123. raw /dev/raw/raw2 /dev/loop2
  1124. raw /dev/raw/raw3 /dev/loop3
  1125. raw /dev/raw/raw4 /dev/loop4
  1126. raw /dev/raw/raw5 /dev/loop5

  1127. chmod 660 /dev/raw/raw1
  1128. chmod 660 /dev/raw/raw2
  1129. chmod 660 /dev/raw/raw3
  1130. chmod 660 /dev/raw/raw4
  1131. chmod 660 /dev/raw/raw5
  1132. chown oracle:dba /dev/raw/raw1
  1133. chown oracle:dba /dev/raw/raw2
  1134. chown oracle:dba /dev/raw/raw3
  1135. chown oracle:dba /dev/raw/raw4
  1136. chown oracle:dba /dev/raw/raw5


  1137. ------Add the following entries to the file "/etc/rc.local"
  1138. /sbin/losetup /dev/loop1 /asmdisk/disk1
  1139. /sbin/losetup /dev/loop2 /asmdisk/disk2
  1140. /sbin/losetup /dev/loop3 /asmdisk/disk3
  1141. /sbin/losetup /dev/loop4 /asmdisk/disk4
  1142. /sbin/losetup /dev/loop5 /asmdisk/disk5

  1143. raw /dev/raw/raw1 /dev/loop1
  1144. raw /dev/raw/raw2 /dev/loop2
  1145. raw /dev/raw/raw3 /dev/loop3
  1146. raw /dev/raw/raw4 /dev/loop4
  1147. raw /dev/raw/raw5 /dev/loop5

  1148. chmod 660 /dev/raw/raw1
  1149. chmod 660 /dev/raw/raw2
  1150. chmod 660 /dev/raw/raw3
  1151. chmod 660 /dev/raw/raw4
  1152. chmod 660 /dev/raw/raw5
  1153. chown oracle:dba /dev/raw/raw1
  1154. chown oracle:dba /dev/raw/raw2
  1155. chown oracle:dba /dev/raw/raw3
  1156. chown oracle:dba /dev/raw/raw4
  1157. chown oracle:dba /dev/raw/raw5




  1158. ------------------------ Simulating Asm by faking hardware
  1159. -->Faking Hardware
  1160. -->Instaling ASM Lib
  1161. -->Configuring the disks
  1162. -->Install DB & ASM instance

  1163. ---Faking Hardware: root 使用者

  1164. mkdir /asmdisk
  1165. dd if=/dev/zero of=/asmdisk/disk1 bs=1024k count=2000
  1166. dd if=/dev/zero of=/asmdisk/disk2 bs=1024k count=2000

  1167. /sbin/losetup /dev/loop1 /asmdisk/disk1
  1168. /sbin/losetup /dev/loop2 /asmdisk/disk2

  1169. raw /dev/raw/raw1 /dev/loop1
  1170. raw /dev/raw/raw2 /dev/loop2

  1171. chmod 660 /dev/raw/raw1
  1172. chmod 660 /dev/raw/raw2
  1173. chown grid:asmadmin /dev/raw/raw1
  1174. chown grid:asmadmin /dev/raw/raw2


  1175. ------Add the following entries to the file "/etc/rc.local"
  1176. echo "/sbin/losetup /dev/loop1 /asmdisk/disk1" >>/etc/rc.local
  1177. echo "/sbin/losetup /dev/loop2 /asmdisk/disk2" >>/etc/rc.local




  1178. --------------------------------- oracle 日誌

  1179. oracleasm日誌: tail -f /var/log/oracleasm    
  1180. oracle agent日誌: tail -f /u01/app/11.2.0/grid/log/rhel5/agent/ohasd/oraagent_grid/oraagent_grid.log
  1181. asm 告警日誌:alert_log='tail -200f $ORACLE_BASE/diag/asm/+asm/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
  1182. CRS 啟動日誌: more $ORACLE_HOME/log/$HOSTNAME/crsd/crsd.log

  1183. ------查詢:find /u01/app/ -name crsd.log
  1184. crs日誌地址:/u01/app/11.2.0/grid/log/rac2/crsd/crsd.log


  1185. oracle 10g 告警日誌: /u02/app/oracle/admin/ora10g/bdump
  1186. oracle 11g 告警日誌: select value from v$diag_info where name='Default Trace File';


  1187. System Control Statement 系統控制語句 alter system


  1188.  

  1189. ------------------------------------------------------------------------------------------- 恢復到new host

  1190. set pagesize 200 linesize 200
  1191. select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";'
  1192.   from v$datafile a
  1193. union all
  1194. select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";'
  1195.   from v$tempfile a
  1196. union all
  1197. SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''' ||
  1198.        a.MEMBER || ''''' ";'
  1199.   FROM v$logfile a;



  1200. RUN
  1201. {
  1202.   # allocate a channel to the tape device
  1203.   # ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS '...';
  1204.   ALLOCATE CHANNEL c1 DEVICE TYPE DISK;


  1205.   # rename the data files and online redo logs
  1206.   SET NEWNAME FOR DATAFILE 1 TO '?/oradata/test/system01.dbf';
  1207.   SET NEWNAME FOR DATAFILE 2 TO '?/oradata/test/undotbs01.dbf';
  1208.   SET NEWNAME FOR DATAFILE 3 TO '?/oradata/test/cwmlite01.dbf';
  1209.   SET NEWNAME FOR DATAFILE 4 TO '?/oradata/test/drsys01.dbf';
  1210.   SET NEWNAME FOR DATAFILE 5 TO '?/oradata/test/example01.dbf';
  1211.   SET NEWNAME FOR DATAFILE 6 TO '?/oradata/test/indx01.dbf';
  1212.   SET NEWNAME FOR DATAFILE 7 TO '?/oradata/test/tools01.dbf';
  1213.   SET NEWNAME FOR DATAFILE 8 TO '?/oradata/test/users01.dbf';
  1214.   SET NEWNAME FOR TEMPFILE 1 TO '?/oradata/test/temp01.dbf';

  1215.   SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo01.log''
  1216.       TO ''?/oradata/test/redo01.log'' ";
  1217.   SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo02.log''
  1218.       TO ''?/oradata/test/redo02.log'' ";

  1219.   # Do a SET UNTIL to prevent recovery of the online logs
  1220.   SET UNTIL SCN 123456;
  1221.   # restore the database and switch the data file names
  1222.   RESTORE DATABASE;
  1223.   SWITCH DATAFILE ALL;
  1224.   SWITCH TEMPFILE ALL;
  1225.   # recover the database
  1226.   RECOVER DATABASE;
  1227. }
  1228.  

  1229. ------------------------------------------------- 資料庫未掛掉的情況下的恢復


  1230. [root@orcltest ~]# ps -ef|grep ora_lgwr_
  1231. .oracle 32173 1 0 06:31 ? 00:00:00 ora_lgwr_oratest
  1232. root 33247 32901 0 10:19 pts/0 00:00:00 grep ora_lgwr_
  1233. [root@orcltest ~]# cd /proc/32173/fd
  1234. [root@orcltest fd]# ll | grep deleted
  1235. lrwx------ 1 root root 64 May 5 15:10 266 -> /u02/app/oracle/oradata/oratest/temp01.dbf (deleted)
  1236. cp 266 /u02/app/oracle/oradata/oratest/temp01.dbf



  1237. ------------------------------------------------------------------------------------------- asm <=> os
  1238. ----所有檔案列表 資料檔案
  1239. set line 9999 pagesize 9999
  1240. col FILE_NAME format a60
  1241. select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile
  1242. union all
  1243. select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile
  1244. union all
  1245. select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile
  1246. union all
  1247. select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile
  1248. ;




  1249. set line 9999 pagesize 9999
  1250. col FILE_NAME format a50
  1251. select file#,name FILE_NAME,status,enabled from v$datafile;
  1252. --select file#,name FILE_NAME from v$dbfile;

  1253. col FILE_NAME format a50
  1254. 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;


  1255. -------------------------------------------os--->>asm
  1256. -- answer 1 set newname 好
  1257. run{
  1258. crosscheck backup;
  1259. sql 'alter tablespace testdg offline immediate';
  1260. set newname for datafile 14 to'+DATA';
  1261. restore tablespace testdg;
  1262. switch datafile 14;
  1263. recover tablespace testdg;
  1264. sql 'alter tablespace testdg online';
  1265. }


  1266. -- answer 2 convert 好
  1267. rman下:
  1268. convert datafile '/home/oracle/testdg.dbf' format '+DATA';
  1269. sql 下:
  1270. alter tablespace testdg offline ;
  1271. alter tablespace testdg rename datafile '/home/oracle/testdg.dbf' to '+DATA/orclasm/datafile/testdg.284.868895277';
  1272. recover datafile 14;
  1273. alter tablespace testdg online;




  1274. -- answer 3 dbms_file_transfer
  1275. create directory asmsrc as'+DATA/orclasm/datafile/';
  1276. create directory osdesc as '/home/oracle/';

  1277. alter tablespace testdg offline;
  1278.  
  1279. exec dbms_file_transfer.copy_file('osdesc','testdg.dbf','ASMSRC','testdg.dbf');

  1280. alter database rename file'/home/oracle/testdg.dbf' to '+DATA/orclasm/datafile/testdg.dbf';
  1281. alter tablespace testdg online ;





  1282. -- answer 4 backup as copy
  1283. run{
  1284. shutdown immediate;
  1285. startup mount;
  1286. backup as copy datafile 14 format '+DATA';
  1287. }
  1288. SWITCH TABLESPACE testdg TO COPY;
  1289. alter database open;



  1290. -- answer 5 cp

  1291. alter tablespace testdg offline;
  1292. [root@rhel6_lhr ~]# cp /home/oracle/testdg.dbf /home/grid/testdg.dbf
  1293. [root@rhel6_lhr ~]# chown grid:oinstall /home/grid/testdg.dbf
  1294. [root@rhel6_lhr ~]# su - grid
  1295. ASMCMD> cp /home/grid/testdg.dbf +DATA/orclasm/datafile/testdg.dbf
  1296. copying /home/grid/testdg.dbf -> +DATA/orclasm/datafile/testdg.dbf
  1297. ASMCMD>
  1298. alter database rename file'/home/oracle/testdg.dbf' to '+DATA/orclasm/datafile/testdg.dbf';
  1299. alter tablespace testdg online ;



  1300. ---生成standby controlfile
  1301. rman:backup device type disk format '/arch/standby_new_lhr_%U.ctl' current controlfile for standby;
  1302. sql:alter database create standby controlfile as '/arch/standby_new_lhr_contol.ctl'



  1303. --------------------控制檔案轉換
  1304. RMAN> catalog controlfilecopy '/home/oracle/rman_back/ctl_orastrac.ctl_bk';

  1305. cataloged control file copy
  1306. control file copy file name=/home/oracle/rman_back/ctl_orastrac.ctl_bk RECID=7 STAMP=881248289

  1307. RMAN> backup as copy controlfilecopy '/home/oracle/rman_back/ctl_orastrac.ctl_bk' format '+DATA';

  1308. Starting backup at 01-JUN-2015 15:11:44
  1309. using channel ORA_DISK_1
  1310. channel ORA_DISK_1: starting datafile copy
  1311. input control file copy name=/home/oracle/rman_back/ctl_orastrac.ctl_bk
  1312. output file name=+DATA/orastrac/controlfile/backup.331.881248305 tag=TAG20150601T111610 RECID=8 STAMP=881248307
  1313. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
  1314. Finished backup at 01-JUN-2015 15:11:51

  1315. RMAN-06497: WARNING: control file is not current, control file AUTOBACKUP skipped

  1316. RMAN> restore controlfile to '+DATA' FROM '+DATA/orastrac/controlfile/backup.331.881248305';

  1317. Starting restore at 01-JUN-2015 15:12:49
  1318. using channel ORA_DISK_1

  1319. channel ORA_DISK_1: copied control file copy
  1320. Finished restore at 01-JUN-2015 15:12:50



  1321. ------------------------------------------------ asm --->> os


  1322. -- answer 1 好
  1323. rman下:
  1324. convert datafile '+DATA/orclasm/datafile/testdg.277.868887219' format '/home/oracle/testdg.dbf';
  1325. sql 下:
  1326. alter tablespace testdg offline ;
  1327. alter tablespace testdg rename datafile '+DATA/orclasm/datafile/testdg.277.868887219' to '/home/oracle/testdg.dbf';
  1328. recover datafile 14;
  1329. alter tablespace testdg online;


  1330. -- answer 2 dbms_file_transfer
  1331. create directory asmsrc as'+DATA/orclasm/datafile/';
  1332. create directory osdesc as '/home/oracle/';

  1333. alter tablespace testdg offline;
  1334.  
  1335. exec dbms_file_transfer.copy_file('ASMSRC','testdg.282.868891371','osdesc','testdg.dbf');

  1336. alter database rename file'+DATA/orclasm/datafile/testdg.282.868891371' to '/home/oracle/testdg.dbf';
  1337. alter tablespace testdg online ;
  1338.  


  1339. -- answer 3 mount
  1340. run{
  1341. shutdown immediate;
  1342. startup mount;
  1343. set newname for datafile 14 to '/home/oracle/testdg.dbf';
  1344. restore datafile 14;
  1345. switch datafile 14;
  1346. recover datafile 14;
  1347. alter database open;
  1348. }



  1349. -- answer 4 mount
  1350. run{
  1351. shutdown immediate;
  1352. startup mount;
  1353. backup as copy datafile 14 format '/home/oracle/testdg.dbf';
  1354. }

  1355. switch tablespace testdg to copy;
  1356. alter database open;



  1357. -- answer 5 cp

  1358. alter tablespace testdg offline;
  1359. [root@rhel6_lhr ~]# su - grid
  1360. ASMCMD> cp +DATA/orclasm/datafile/testdg.dbf /home/grid/testdg.dbf
  1361. copying +DATA/orclasm/datafile/testdg.dbf -> /home/grid/testdg.dbf
  1362. ASMCMD>

  1363. [root@rhel6_lhr ~]# cp /home/grid/testdg.dbf /home/oracle/testdg.dbf
  1364. [root@rhel6_lhr ~]# chown oracle:oinstall /home/oracle/testdg.dbf
  1365. [root@rhel6_lhr ~]#


  1366. alter database rename file'+DATA/orclasm/datafile/testdg.dbf' to '/home/oracle/testdg.dbf';
  1367. alter tablespace testdg online ;




  1368. create bigfile tablespace ts_dbm datafile '/home/oracle/ts_dbm01.dbf' size 10m autoextend on next 10M ;
  1369. alter user xxx default tablespace bbb;



  1370.  ------ smallfile tablespaces
  1371. 單個資料檔案的大小由資料庫block_size的尺寸決定,例如:
  1372. block_size =8K 對應單個資料檔案最大為 32G
  1373. block_size =16K 對應單個據檔案最大為 64G
  1374. block_size =32K 對應單個據檔案最大為 128G


  1375.  ------Bigfile Tablespaces
  1376. block_size =8K 對應單個資料檔案最大為 32T
  1377. block_size =16K 對應單個據檔案最大為 64T
  1378. block_size =32K 對應單個據檔案最大為 128T




  1379. ----如果刪除表空間之前刪除了表空間檔案,解決辦法:

  1380. 如果資料庫已經啟動,則需要先執行下面這行:
  1381. SQL> shutdown abort
  1382. SQL> startup mount
  1383. SQL> alter database datafile 'filename' offline drop;
  1384. SQL> alter database open;
  1385. SQL> drop tablespace tablespace_name including contents;


  1386. alter database datafile '/u02/oracle/oradata/user01.dbf' offline drop;
  1387. alter tablespace test drop datafile '+DATA/orclasm/datafile/test.274.907173619';
  1388.  


  1389. ------表空間預設型別
  1390. SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like '%TBS%';
  1391. SQL> alter database set default bigfile tablespace;
  1392. --改回為預設值
  1393. SQL> alter database set default smallfile tablespace;

  1394. ---控制檔案
  1395. --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;
  1396. alter database backup controlfile to trace as '/home/oracle/oracle_bk/coolbak/ctl.sql';
  1397. select * from v$controlfile_record_section;
  1398. --轉儲控制檔案
  1399. alter system set events 'immediate trace name controlf level 12';
  1400. ---檔案路徑
  1401. SELECT d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
  1402.        p.spid || '.trc' trace_file_name
  1403.   FROM (SELECT p.spid
  1404.           FROM v$mystat m, v$session s, v$process p
  1405.          WHERE m.statistic# = '1'
  1406.            AND s.sid = m.sid
  1407.            AND p.addr = s.paddr) p,
  1408.        (SELECT t.instance
  1409.           FROM v$thread t, v$parameter v
  1410.          WHERE v.name = 'thread'
  1411.            AND (v.value = '0' OR to_char(t.thread#) = v.VALUE)) i,
  1412.        (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;


  1413. oradebug setmypid
  1414. SELECT a.SID,
  1415.        b.SERIAL# ,
  1416.        c.SPID ospid,
  1417.        c.pid orapid
  1418. FROM v$mystat a,
  1419.        v$session b ,
  1420.        v$process c
  1421. WHERE a.SID = b.SID
  1422. and b.PADDR=c.ADDR
  1423. AND rownum = 1;
  1424. oradebug dump controlf 12;


  1425. 16:09:17 SQL> oradebug setmypid
  1426. 已處理的語句
  1427. 16:09:55 SQL> oradebug tracefile_name
  1428. /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace/orclasm_ora_21437.trc


  1429. oradebug event 1555 trace name errorstack level 3






  1430. 熱備:
  1431.     alter database backup controlfile to '<dir>'; --熱備份控制檔案 alter database backup controlfile to '/home/oracle/ora_bk/control.bk';
  1432.     alter database backup controlfile to trace as '<dir>' ;--得到建立控制檔案的指令碼
  1433. RMAN:
  1434.     backup current controlfile format '/home/oracle/oracle_bk/orclasm/ctl_%d_%T_%s_%p.bak';
  1435.     backup database include current controlfile;
  1436.  -- 或者設定RMAN 為自動備份
  1437.    RMAN > configure controlfile autobackup on;


  1438. ----預設false 忽略一致性檢察 隱含引數 隱藏
  1439. SELECT * FROM gv$parameter a WHERE a.NAME like '\_%' escape '\' ;
  1440. SELECT * FROM gv$parameter a WHERE a.NAME like '=_%' escape '=' ;

  1441. alter system set "_allow_resetlogs_corruption"=true scope=spfile;
  1442. alter system set "_allow_resetlogs_corruption"=false scope=spfile; --預設

  1443. alter system reset "_allow_resetlogs_corruption" scope=spfile sid='*';


  1444. SQL> show parameter _allow_resetlogs_corruption

  1445. NAME TYPE VALUE
  1446. ------------------------------------ ----------- ------------------------------
  1447. _allow_resetlogs_corruption boolean TRUE

  1448. set pagesize 9999
  1449. set line 9999
  1450. col NAME format a40
  1451. col KSPPDESC format a50
  1452. col KSPPSTVL format a20
  1453. SELECT a.INDX,
  1454.        a.KSPPINM NAME,
  1455.        a.KSPPDESC,
  1456.        b.KSPPSTVL
  1457. FROM x$ksppi a,
  1458.        x$ksppcv b
  1459. WHERE a.INDX = b.INDX
  1460. and lower(a.KSPPINM) like lower('%&parameter%');





  1461. alter system set "_allow_resetlogs_corruption"=true scope=spfile;
  1462. recover database using backup controlfile until cancel;
  1463. alter database open resetlogs;
  1464. startup force
  1465. alter database open resetlogs;
  1466. alter system set "_allow_resetlogs_corruption"=false scope=spfile;
  1467. alter system reset "_allow_resetlogs_corruption" scope=spfile sid='*';




  1468. ----------------incarnation
  1469. RMAN> list incarnation of database;
  1470. RMAN> reset database to incarnation 8;

  1471. SELECT * FROM V$DATABASE_INCARNATION;


  1472. alter system set log_archive_dest_1='LOCATION=/home/oracle' scope=spfile;



  1473. ---------- 重建控制檔案
  1474. CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS DATAFILE '/u01/app/oracle/oradata/orcltest/system01.dbf';


  1475. STARTUP NOMOUNT
  1476. CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS ARCHIVELOG
  1477.     MAXLOGFILES 16
  1478.     MAXLOGMEMBERS 3
  1479.     MAXDATAFILES 100
  1480.     MAXINSTANCES 8
  1481.     MAXLOGHISTORY 292
  1482. LOGFILE
  1483.   GROUP 1 '/u02/app/oracle/oradata/orcltest/redo01.log' SIZE 50M BLOCKSIZE 512,
  1484.   GROUP 2 '/u02/app/oracle/oradata/orcltest/redo02.log' SIZE 50M BLOCKSIZE 512,
  1485.   GROUP 3 '/u02/app/oracle/oradata/orcltest/redo03.log' SIZE 50M BLOCKSIZE 512
  1486. -- STANDBY LOGFILE
  1487. DATAFILE
  1488.   '/u02/app/oracle/oradata/orcltest/system01.dbf',
  1489.   '/u02/app/oracle/oradata/orcltest/sysaux01.dbf',
  1490.   '/u02/app/oracle/oradata/orcltest/undotbs01.dbf',
  1491.   '/u02/app/oracle/oradata/orcltest/users01.dbf',
  1492.   '/u02/app/oracle/oradata/orcltest/example01.dbf'
  1493. CHARACTER SET ZHS16GBK
  1494. ;



  1495. 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;
  1496. run {
  1497.  allocate channel c1 type disk;
  1498.  allocate channel c2 type disk;
  1499.  startup force mount;
  1500.  sql 'alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:mi:ss"';
  1501.  set until time = "to_date('2016-07-28 21:04:50','YYYY-MM-DD HH24:mi:ss')";
  1502.  restore database;
  1503.  recover database;
  1504.  release channel c1;
  1505.  release channel c2;
  1506. }

  1507. catalog start with '/u03/backup/' noprompt;



  1508. --restore Controlfile
  1509. DECLARE
  1510.  devtype varchar2(256);
  1511.  done boolean;
  1512.  BEGIN
  1513.  devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
  1514.  sys.dbms_backup_restore.restoreSetDatafile;
  1515.  sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/tmp/2015_04_30/control01.ctl');
  1516.  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_04_30/o1_mf_annnn_TAG20150430T160239_bn3rdzd7_.bkp', params=>null);
  1517.  sys.dbms_backup_restore.deviceDeallocate;
  1518.  END;
  1519. /




  1520. --restore datafile
  1521. DECLARE
  1522.  devtype varchar2(256);
  1523.  done boolean;
  1524. BEGIN
  1525.  devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
  1526.  sys.dbms_backup_restore.restoreSetDatafile;
  1527.  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/u02/app/oracle/oradata/orcltest/system01.dbf');
  1528.  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/u02/app/oracle/oradata/orcltest/sysaux01.dbf');
  1529.  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/u02/app/oracle/oradata/orcltest/undotbs01.dbf');
  1530.  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'/u02/app/oracle/oradata/orcltest/users01.dbf');
  1531.  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'/u02/app/oracle/oradata/orcltest/example01.dbf');
  1532.  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp', params=>null);
  1533.  sys.dbms_backup_restore.deviceDeallocate;
  1534. END;
  1535. /

  1536. 注意:
  1537.  在multisection backup 的情況下,我們需要考慮所有的backuppiece(也就是所有的section),使用initmsr函式來restore datafile
  1538.  
  1539. DECLARE
  1540. devtype varchar2(256);
  1541. done boolean;
  1542. BEGIN
  1543. devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
  1544. dbms_backup_restore.RestoreSetDatafile;
  1545. dbms_backup_restore.initmsr(1,'/ud1001/PROD/oradata/system01-test.dbf');
  1546. dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/ud1001/PROD/oradata/system01-test.dbf');
  1547. dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/backup/Oracle-DB-8-7-2014/db_L0_PROD_e6pf7too_1_1.rman', params => null);
  1548. dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/backup/Oracle-DB-8-7-2014/db_L0_PROD_e6pf7too_2_1.rman', params => null);
  1549. END;
  1550. /
  1551.  


  1552. --restore archived redolog
  1553. DECLARE
  1554. devtype varchar2(256);
  1555. done boolean;
  1556. BEGIN
  1557. devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'FUN');
  1558. dbms_backup_restore.RestoreSetArchivedLog(destination=>'D:\ORACLE_BASE\achive\');
  1559. dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>1);
  1560. dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>2);
  1561. dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>3);
  1562. dbms_backup_restore.RestoreBackupPiece(done => done,handle => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_LOG_BCK0DH1JGND_1_1', params => null);
  1563. dbms_backup_restore.DeviceDeallocate;
  1564. END;
  1565. /


  1566. --清除控制檔案中關於v$archived_log的資訊
  1567. SQL> execute sys.dbms_backup_restore.resetCfileSection(11);

  1568. --再次查詢v$archived_log,資訊已經被清除
  1569. SQL> select dest_id,sequence#,name,blocks from v$archived_log;

  1570. ---------------------------------------------------------------------------------------------------------------- 歸檔丟失
  1571. SQL> recover database ;
  1572. ORA-00279: change 1549336 generated at 01/15/2015 16:22:07 needed for thread 1
  1573. ORA-00289: suggestion :
  1574. /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_22_865253186.dbf
  1575. ORA-00280: change 1549336 for thread 1 is in sequence #22


  1576. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  1577. cancel
  1578. Media recovery cancelled.
  1579. SQL> alter database open;
  1580. alter database open
  1581. *
  1582. ERROR at line 1:
  1583. ORA-01113: file 1 needs media recovery
  1584. ORA-01110: data file 1: '/u01/app/oracle/oradata/utf8test/system01.dbf'


  1585. SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

  1586. System altered.

  1587. SQL> startup force;
  1588. ORACLE instance started.

  1589. Total System Global Area 1102344192 bytes
  1590. Fixed Size         2227584 bytes
  1591. Variable Size         738198144 bytes
  1592. Database Buffers     352321536 bytes
  1593. Redo Buffers         9596928 bytes
  1594. Database mounted.
  1595. ORA-01113: file 1 needs media recovery
  1596. ORA-01110: data file 1: '/u01/app/oracle/oradata/utf8test/system01.dbf'

  1597. ---- recover database using backup controlfile;
  1598. SQL> recover database until cancel;
  1599. ORA-00279: change 1549336 generated at 01/15/2015 16:22:07 needed for thread 1
  1600. ORA-00289: suggestion :
  1601. /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_22_865253186.dbf
  1602. ORA-00280: change 1549336 for thread 1 is in sequence #22


  1603. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  1604. cancel
  1605. ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
  1606. ORA-01194: file 2 needs more recovery to be consistent
  1607. ORA-01110: data file 2: '/u01/app/oracle/oradata/utf8test/sysaux01.dbf'


  1608. ORA-01112: media recovery not started


  1609. SQL> alter database open resetlogs;
  1610. alter database open resetlogs
  1611. *
  1612. ERROR at line 1:
  1613. ORA-00603: ORACLE server session terminated by fatal error
  1614. ORA-00600: internal error code, arguments: [2662], [0], [1549349], [0],
  1615. [1550178], [12583040], [], [], [], [], [], []
  1616. ORA-00600: internal error code, arguments: [2662], [0], [1549348], [0],
  1617. [1550178], [12583040], [], [], [], [], [], []
  1618. ORA-01092: ORACLE instance terminated. Disconnection forced
  1619. ORA-00600: internal error code, arguments: [2662], [0], [1549346], [0],
  1620. [1550178], [12583040], [], [], [], [], [], []
  1621. Process ID: 7693
  1622. Session ID: 237 Serial number: 5


  1623. 退出,重新登入

  1624. SQL> exit
  1625. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  1626. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  1627. [oracle@rhel6_lhr utf8test]$ sqlplus / as sysdba

  1628. SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 16 10:13:53 2015

  1629. Copyright (c) 1982, 2011, Oracle. All rights reserved.

  1630. Connected to an idle instance.

  1631. SQL> startup
  1632. ORACLE instance started.

  1633. Total System Global Area 1102344192 bytes
  1634. Fixed Size         2227584 bytes
  1635. Variable Size         738198144 bytes
  1636. Database Buffers     352321536 bytes
  1637. Redo Buffers         9596928 bytes
  1638. Database mounted.
  1639. Database opened.
  1640. ----------------------------------------------------------------------------------------------------------------

  1641. select * from v$fixed_view_definition a WHERE a.VIEW_NAME like 'X_$DIAG%' ;
  1642. SELECT * FROM V$FIXED_TABLE A WHERE A.NAME like 'X$DIAG%' ;



  1643. ------- 十進位制轉十六進位制
  1644. select to_char(1985432,'xxxxxxxxxxxxxxx') FROM DUAL;
  1645. ------- 十六進位制轉十進位制
  1646. select to_number('1e4b98','xxxxxxxxxxxxxxx') from dual;




  1647. --------------------------- exp和imp grant exp_full_database to lhr;
  1648. --EXP-00091的方法 select userenv('language') from dual; ---->>> NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
  1649. 或加上: STATISTICS=NONE

  1650. ------ query選項
  1651. 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
  1652. [ZFZHLHRDB1:oracle]:/oracle>more /tmp/scottfile.par
  1653. query="where owner='SCOTT'"
  1654. [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

  1655. ------ parfile選項
  1656. [ZFZHLHRDB1:oracle]:/oracle>more /tmp/scottfile.par
  1657. tables=scott.emp,scott.dept
  1658. exp \'/ AS SYSDBA\' file=/tmp/test_query_lhr_scott_01.dmp parfile=/tmp/scottfile.par log=/tmp/test_query_lhr_scott_01.log




  1659. strace exp n1/n1 tables=scott.emp file=a.dmp


  1660. 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
  1661. imp cnydm/cnydm@DATAWDB_125 file=d:/oracle_bk/cnydm20150402.dmp log=d:/oracle_bk/imp_cnydm20150402.log buffer=50000000 full=y



  1662. exp system/lhr file=E:\expfull.dmp full=y log=E:\expfull.log
  1663. imp system/lhr file=E:\expfull.dmp full=y log=E:\impfull.log

  1664. exp lhr/lhr@orclasm tables=xb_log_lhr,xb_a,xb_b file=e:\e1.dmp log=E:\exp_table.log buffer=41943040
  1665. imp lhr/lhr@winxp tables=xb_log_lhr file=e:\e1.dmp log=E:\exp_table.log buffer=41943040
  1666. imp lhr/lhr@orclasm tables=(emp,dept) file=d:\e1.dmp log=E:\exp_table_.log buffer=41943040



  1667. exp system/lhr file=E:\expfull2.dmp log=E:\expfull2.log owner=(lhrexp,lhrimp)
  1668. imp system/lhr file=E:\expfull2.dmp full=y log=E:\expfull2.log
  1669. imp "sys as sysdba" file=testmv_full.dmp full=y buffer=41943040 feedback=10000 log=testmv_full.log
  1670. imp user2/pwd fromuser=user1 touser=user2 file=/tmp/imp_db_pipe1 feedback=10000 buffer=41943040 log=testmv_full.log

  1671. 生產環境下,oracle 9I下sga大概8G,pga大概6g,需要匯入一個2.7g以上的大表到成產庫中,由於是同事著手運用imp工具的預設buffer=30K,用時大概一個小時還沒有結果,考慮到pga還是很大的,跟同事商議加上buffer=409600000設定buffer大概400M的,15分鐘內imp完成。當然運用impdp然後運用parallel=n效率當然更加理想了!


  1672. -------------------------------------------- 匯出ASH檢視的資料 ash資料
  1673. --- 方法1:ctas建表匯出 有的客戶不讓建表
  1674. CREATE TABLE ASH_TEMP_20161117 NOLOGGING AS
  1675. SELECT *
  1676.   FROM DBA_HIST_ACTIVE_SESS_HISTORY D
  1677.  WHERE D.SAMPLE_TIME BETWEEN
  1678.        TO_DATE('2016-11-10 02:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
  1679.        TO_DATE('2016-11-17 06:00:00', 'YYYY-MM-DD HH24:MI:SS')
  1680. ;

  1681. exp \'/ AS SYSDBA\' tables=ASH_TEMP_20161117 file=/tmp/ASH_TEMP_20161117.dmp log=/tmp/ASH_TEMP_20161117.log buffer=41943040
  1682. imp lhr/lhr tables=ASH_TEMP_20161117 file=/tmp/ASH_TEMP_20161117.dmp log=/tmp/imp_ASH_TEMP_20161117.log buffer=41943040


  1683. --- 方法2:匯出基表的資料
  1684. ---more /tmp/exp_ash_lhr_01.par
  1685. 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')"

  1686. 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
  1687. 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

  1688. 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
  1689. 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


  1690. DROP TABLE LHR.WRH$_ACTIVE_SESSION_HISTORY PURGE;
  1691. DROP TABLE LHR.WRM$_SNAPSHOT PURGE;
  1692. DROP TABLE LHR.WRH$_EVENT_NAME PURGE;
  1693. DROP TABLE LHR.WRH$_SQLCOMMAND_NAME PURGE;
  1694. DROP TABLE LHR.WRH$_PLAN_OPERATION_NAME PURGE;
  1695. DROP TABLE LHR.WRH$_PLAN_OPTION_NAME PURGE;
  1696. DROP TABLE LHR.WRH$_TOPLEVELCALL_NAME PURGE;

  1697. create or replace view dh_ash_11g_lhr
  1698. (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)
  1699. as
  1700. select /* ASH/AWR meta attributes */
  1701.        ash.snap_id, ash.dbid, ash.instance_number,
  1702.        ash.sample_id, ash.sample_time,
  1703.        /* Session/User attributes */
  1704.        ash.session_id, ash.session_serial#,
  1705.        decode(ash.session_type, 1,'FOREGROUND', 'BACKGROUND'),
  1706.        ash.flags,
  1707.        ash.user_id,
  1708.        /* SQL attributes */
  1709.        ash.sql_id,
  1710.        decode(bitand(ash.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'),
  1711.        ash.sql_child_number, ash.sql_opcode,
  1712.        (select command_name from WRH$_SQLCOMMAND_NAME
  1713.         where command_type = ash.sql_opcode
  1714.         and dbid = ash.dbid) as sql_opname,
  1715.        ash.force_matching_signature,
  1716.        decode(ash.top_level_sql_id, NULL, ash.sql_id, ash.top_level_sql_id),
  1717.        decode(ash.top_level_sql_id, NULL, ash.sql_opcode,
  1718.               ash.top_level_sql_opcode),
  1719.        /* SQL Plan/Execution attributes */
  1720.        ash.sql_plan_hash_value,
  1721.        decode(ash.sql_plan_line_id, 0, to_number(NULL), ash.sql_plan_line_id),
  1722.        (select operation_name from WRH$_PLAN_OPERATION_NAME
  1723.         where operation_id = ash.sql_plan_operation#
  1724.           and dbid = ash.dbid) as sql_plan_operation,
  1725.        (select option_name from WRH$_PLAN_OPTION_NAME
  1726.         where option_id = ash.sql_plan_options#
  1727.           and dbid = ash.dbid) as sql_plan_options,
  1728.        decode(ash.sql_exec_id, 0, to_number(NULL), ash.sql_exec_id),
  1729.        ash.sql_exec_start,
  1730.        /* PL/SQL attributes */
  1731.        decode(ash.plsql_entry_object_id,0,to_number(NULL),
  1732.               ash.plsql_entry_object_id),
  1733.        decode(ash.plsql_entry_object_id,0,to_number(NULL),
  1734.               ash.plsql_entry_subprogram_id),
  1735.        decode(ash.plsql_object_id,0,to_number(NULL),
  1736.               ash.plsql_object_id),
  1737.        decode(ash.plsql_object_id,0,to_number(NULL),
  1738.               ash.plsql_subprogram_id),
  1739.        /* PQ attributes */
  1740.        decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_instance_id),
  1741.        decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_id),
  1742.        decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_serial#),
  1743.        decode(ash.px_flags, 0, to_number(NULL), ash.px_flags),
  1744.        /* Wait event attributes */
  1745.        decode(ash.wait_time, 0, evt.event_name, NULL),
  1746.        decode(ash.wait_time, 0, evt.event_id, NULL),
  1747.        ash.seq#,
  1748.        evt.parameter1, ash.p1,
  1749.        evt.parameter2, ash.p2,
  1750.        evt.parameter3, ash.p3,
  1751.        decode(ash.wait_time, 0, evt.wait_class, NULL),
  1752.        decode(ash.wait_time, 0, evt.wait_class_id, NULL),
  1753.        ash.wait_time,
  1754.        decode(ash.wait_time, 0, 'WAITING', 'ON CPU'),
  1755.        ash.time_waited,
  1756.        (case when ash.blocking_session = 4294967295
  1757.                then 'UNKNOWN'
  1758.              when ash.blocking_session = 4294967294
  1759.                then 'GLOBAL'
  1760.              when ash.blocking_session = 4294967293
  1761.                then 'UNKNOWN'
  1762.              when ash.blocking_session = 4294967292
  1763.                then 'NO HOLDER'
  1764.              when ash.blocking_session = 4294967291
  1765.                then 'NOT IN WAIT'
  1766.              else 'VALID'
  1767.         end),
  1768.        (case when ash.blocking_session between 4294967291 and 4294967295
  1769.                then to_number(NULL)
  1770.              else ash.blocking_session
  1771.         end),
  1772.        (case when ash.blocking_session between 4294967291 and 4294967295
  1773.                then to_number(NULL)
  1774.              else ash.blocking_session_serial#
  1775.         end),
  1776.        (case when ash.blocking_session between 4294967291 and 4294967295
  1777.                then to_number(NULL)
  1778.              else ash.blocking_inst_id
  1779.           end),
  1780.        (case when ash.blocking_session between 4294967291 and 4294967295
  1781.                then NULL
  1782.              else decode(bitand(ash.flags, power(2, 3)), NULL, 'N',
  1783.                          0, 'N', 'Y')
  1784.           end),
  1785.        /* Session's working context */
  1786.        ash.current_obj#, ash.current_file#, ash.current_block#,
  1787.        ash.current_row#, ash.top_level_call#,
  1788.        (select top_level_call_name from WRH$_TOPLEVELCALL_NAME
  1789.         where top_level_call# = ash.top_level_call#
  1790.         and dbid = ash.dbid) as top_level_call_name,
  1791.        decode(ash.consumer_group_id, 0, to_number(NULL),
  1792.               ash.consumer_group_id),
  1793.        ash.xid,
  1794.        decode(ash.remote_instance#, 0, to_number(NULL), ash.remote_instance#),
  1795.        ash.time_model,
  1796.        decode(bitand(ash.time_model,power(2, 3)),0,'N','Y')
  1797.                                                          as in_connection_mgmt,
  1798.        decode(bitand(ash.time_model,power(2, 4)),0,'N','Y')as in_parse,
  1799.        decode(bitand(ash.time_model,power(2, 7)),0,'N','Y')as in_hard_parse,
  1800.        decode(bitand(ash.time_model,power(2,10)),0,'N','Y')as in_sql_execution,
  1801.        decode(bitand(ash.time_model,power(2,11)),0,'N','Y')
  1802.                                                          as in_plsql_execution,
  1803.        decode(bitand(ash.time_model,power(2,12)),0,'N','Y')as in_plsql_rpc,
  1804.        decode(bitand(ash.time_model,power(2,13)),0,'N','Y')
  1805.                                                        as in_plsql_compilation,
  1806.        decode(bitand(ash.time_model,power(2,14)),0,'N','Y')
  1807.                                                        as in_java_execution,
  1808.        decode(bitand(ash.time_model,power(2,15)),0,'N','Y')as in_bind,
  1809.        decode(bitand(ash.time_model,power(2,16)),0,'N','Y')as in_cursor_close,
  1810.        decode(bitand(ash.time_model,power(2,17)),0,'N','Y')as in_sequence_load,
  1811.        decode(bitand(ash.flags,power(2,5)),NULL,'N',0,'N','Y')
  1812.                                                        as capture_overhead,
  1813.        decode(bitand(ash.flags,power(2,6)), NULL,'N',0,'N','Y' )
  1814.                                                            as replay_overhead,
  1815.        decode(bitand(ash.flags,power(2,0)),NULL,'N',0,'N','Y') as is_captured,
  1816.        decode(bitand(ash.flags,power(2,2)), NULL,'N',0,'N','Y' )as is_replayed,
  1817.        /* Application attributes */
  1818.        ash.service_hash, ash.program,
  1819.        ash.module module,
  1820.        ash.action action,
  1821.        ash.client_id,
  1822.        ash.machine, ash.port, ash.ecid,
  1823.        /* DB Replay info */
  1824.        ash.dbreplay_file_id, ash.dbreplay_call_counter,
  1825.        /* stash columns */
  1826.        ash.tm_delta_time,
  1827.        ash.tm_delta_cpu_time,
  1828.        ash.tm_delta_db_time,
  1829.        ash.delta_time,
  1830.        ash.delta_read_io_requests,
  1831.        ash.delta_write_io_requests,
  1832.        ash.delta_read_io_bytes,
  1833.        ash.delta_write_io_bytes,
  1834.        ash.delta_interconnect_io_bytes,
  1835.        ash.pga_allocated,
  1836.        ash.temp_space_allocated
  1837. from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY ash, WRH$_EVENT_NAME evt
  1838. where ash.snap_id = sn.snap_id(+)
  1839.       and ash.dbid = sn.dbid(+)
  1840.       and ash.instance_number = sn.instance_number(+)
  1841.       and ash.dbid = evt.dbid
  1842.       and ash.event_id = evt.event_id;


  1843. ----以下資料不能匯出
  1844. SELECT * FROM sys.Ku_Noexp_View d WHERE d.name LIKE '%WRH%' ;
  1845. SELECT * FROM DBA_OBJECTS d WHERE d.ORACLE_MAINTAINED='Y' AND D.object_name LIKE 'WR%';




  1846. -------------------預設使用者

  1847. 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');




  1848. -------------------------------------expdp和impdp 資料泵
  1849. `date +%Y%m%d`
  1850. %date:~0,4%%date:~5,2%%date:~8,2%

  1851. set CurDate=%date:~0,4%%date:~5,2%%date:~8,2%
  1852. set hh=%time:~0,2%
  1853. if /i %hh% LSS 10 ( set hh=0%time:~1,1%)
  1854. set ms=%time:~3,2%%time:~6,2%
  1855. set my_date=%CurDate%%hh%%ms%

  1856. grant read,write on directory DATA_PUMP_DIR to LHR;

  1857. windows下用:expdp \"/ AS SYSDBA\"


  1858. -------------匯出到服務端
  1859. 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
  1860. 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


  1861. --表級別
  1862. expdp lhr/lhr@orclasm directory=DATA_PUMP_DIR dumpfile=exptable.dmp TABLES=TEST_TSPITR2,TEST_TSPITR3 LOGFILE=expdp_table.log
  1863. expdp scott/tiger@orclasm directory=DATA_PUMP_DIR dumpfile=exptable.dmp TABLES=EMP,DEPT LOGFILE=expdp_table.log
  1864. expdp system/lhr@orclasm DIRECTORY=DATA_PUMP_DIR DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\"IN ('EMP', 'DEPT')\"
  1865. impdp lhr/lhr@orclxp directory=DATA_PUMP_DIR dumpfile=exptable.dmp LOGFILE=impdp_table.log

  1866. --schema級別
  1867. expdp lhr/lhr@orclasm directory=DATA_PUMP_DIR dumpfile=HR.dmp SCHEMAS=HR,SCOTT,TESTUSER LOGFILE=HR.log
  1868. 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
  1869. impdp lhr/lhr@orclasm directory=DATA_PUMP_DIR dumpfile=HR.dmp SCHEMAS=HR,SCOTT,TESTUSER parallel=4 LOGFILE=HR_20151125.log




  1870. --整個資料庫
  1871. 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
  1872. expdp \'/ AS SYSDBA\' DIRECTORY=DATA_DUMP_DIR FULL=Y DUMPFILE=FULLEXP.DMP LOGFILE=FULLEXP.LOG PARALLEL=2
  1873. 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
  1874. impdp LHR/LHR@ORCLASM DIRECTORY=DATA_PUMP_DIR DUMPFILE=HR.DMP SCHEMAS=HR,SCOTT,TESTUSER PARALLEL=4 LOGFILE=HR_20151125.LOG


  1875. expdp SYSTEM/LHR DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_FULL_20150417.DMP FULL=Y LOGFILE=EXPDP_FULL_20150417.LOG EXCLUDE=STATISTICS
  1876. impdp SYSTEM/LHR DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_FULL_20150417.DMP FULL=Y LOGFILE=IMPDP_FULL_20150417.LOG PARALLEL=4 EXCLUDE=STATISTICS:"IN('')"


  1877. ORACLE_SID=ORA1024G
  1878. 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
  1879. 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



  1880. ------ query選項
  1881. [ZFZHLHRDB1:oracle]:/oracle>more /tmp/scottfile.par
  1882. query=SCOTT.EMP:"WHERE DEPTNO=20",SCOTT.DEPT:"WHERE DNAME='SALES'"
  1883. [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

  1884. ----- include
  1885. 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%'"


  1886. include=procedure,function,sequence:"like '%TEST%'"

  1887. include=procedure
  1888. include=function
  1889. include=sequence:"like '%TEST%'"
  1890. 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

  1891. 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

  1892. -------- trace
  1893. 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




  1894. ---------匯出到本地
  1895. expdp lhr/lhr@orclxp directory=DATA_PUMP_DIR dumpfile=exptable.dmp TABLES=xb_log_lhr network_link=dblk_orclasm LOGFILE=expdp_table.log
  1896. impdp lhr/lhr@orclxp directory=DATA_PUMP_DIR dumpfile=exptable.dmp LOGFILE=impdp_table.log


  1897. ---- 直接匯入 不生成檔案
  1898. impdp lhr/lhr@orclxp network_link=dblk_orclasm directory=DATA_PUMP_DIR TABLES=xb_log_lhr PARALLEL=2 LOGFILE=impdp_table.log



  1899. ---- 生成ddl語句 不會匯入資料
  1900. --expdp \'/ AS SYSDBA\' tables=lhr.exptest directory=DATA_PUMP_DIR dumpfile=exptest.dmp logfile=exp_exptest.dmp EXCLUDE=STATISTICS
  1901. --expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=lhrsql20161215.dmp logfile=lhrsql20161215.log content=metadata_only schemas=SCOTT EXCLUDE=STATISTICS
  1902. impdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=lhrsql20161215.dmp logfile=imp_exptest.log sqlfile=exptest.sql


  1903. exp \'/ AS SYSDBA\' tables=scott.emp file=/tmp/exp_ddl_lhr_01.dmp log=/tmp/exp_table.log buffer=41943040 rows=n compress=n
  1904. imp \'/ AS SYSDBA\' file=/tmp/exp_ddl_lhr_01.dmp show=y log=/tmp/get_ddl.sql buffer=20480000 full=y

  1905. set pagesize 0
  1906. set trimspool ON
  1907. SET linesize 10000
  1908. set long 90000
  1909. set feedback OFF
  1910. set feed off;
  1911. set echo off
  1912. spool schema_scott.sql
  1913. SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME,U.owner)
  1914. FROM DBA_OBJECTS U
  1915. WHERE U.OBJECT_TYPE IN ('TABLE', 'INDEX', 'PROCEDURE', 'FUNCTION''PACKAGE','TRIGGER')
  1916. AND U.owner='SCOTT';
  1917. spool off;


  1918. ----只匯出表結構
  1919. expdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=lhrsql20150515.log content=metadata_only schemas=TEST,SQCHECK,DWUSER
  1920. impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log sqlfile=lhrsql20150515.sql
  1921. impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=lhrsql20150515_imp.log
  1922. impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log TRANSFORM=storage:n TRANSFORM=SEGMENT_CREATION:n
  1923. impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log sqlfile=lhrsql20150515.sql TRANSFORM=segment_attributes:n
  1924. --transform=segment_attributes|storage|SEGMENT_CREATION|oid|pctspace:Y/N:object_type



  1925. ----修改物件schema和tablespace
  1926. 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
  1927. 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




  1928. ----顯示時間
  1929. expdp SCOTT/tiger@orclasm DIRECTORY=DATA_PUMP_DIR DUMPFILE=SCOTT%U.dmp SCHEMAS=SCOTT COMPRESSION=all metrics=y

  1930. --dmp檔案重用 reuse_dumpfiles=y
  1931. expdp \''sys/"l@h\r/0"'@LHRDB as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=dmp_SCOTT.dmp SCHEMAS=SCOTT reuse_dumpfiles=y


  1932. #scp -r root@10.0.24.103:/home2/backup/ /home/mover00/shadow_bak/sites/
  1933. 拷貝遠端(10.0.24.103)的/home2/backup/ 到本地的 /home/mover00/shadow_bak/sites/

  1934. #scp -r /home2/backup/ root@10.0.24.99:/home/mover00/shadow_bak/sites/
  1935. 拷貝本地的/home2/backup/ 到遠端(10.0.24.99)的 /home/mover00/shadow_bak/sites/




  1936. set line 9999
  1937. col owner_name for a10
  1938. col job_name for a25
  1939. col operation for a10
  1940. col job_mode for a10
  1941. col state for a15
  1942. col job_mode for a10
  1943. col state for a15
  1944. col osuser for a10
  1945. col "degree|attached|datapump" for a25
  1946. col session_info for a20
  1947. SELECT s.inst_id,
  1948.        dj.owner_name,
  1949.        dj.job_name,
  1950.        dj.operation,
  1951.        dj.job_mode,
  1952.        dj.state,
  1953.        dj.degree || ',' || dj.attached_sessions || ',' ||
  1954.        dj.datapump_sessions "degree|attached|datapump",
  1955.        ds.session_type,
  1956.        s.osuser,
  1957.        (SELECT s.SID || ',' || s.SERIAL# || ',' || p.SPID
  1958.           FROM gv$process p
  1959.          where s.paddr = p.addr
  1960.            AND s.inst_id = p.inst_id) session_info
  1961.   FROM DBA_DATAPUMP_JOBS dj --gv$datapump_job
  1962.   full outer join dba_datapump_sessions ds --gv$datapump_session
  1963.     on (dj.job_name = ds.job_name and dj.owner_name = ds.owner_name)
  1964.   left outer join gv$session s
  1965.     on (s.saddr = ds.saddr)
  1966.  ORDER BY dj.owner_name, dj.job_name;




  1967. select * from GV$DATAPUMP_SESSION;
  1968. select * from GV$datapump_jobs;
  1969. select * From dba_datapump_jobs;


  1970. impdp \"/ as sysdba\" attach=IMPDP_LHR


  1971. ------------- parfile
  1972. [root@rhel6_lhr dpdump]# more par.f
  1973. DUMPFILE=EXPDAT.DMP
  1974. DIRECTORY=DATA_PUMP_DIR
  1975. TRANSPORT_DATAFILES=
  1976. /u01/app/oracle/admin/orclasm/dpdump/APP1TBS.DBF,
  1977. /u01/app/oracle/admin/orclasm/dpdump/APP2TBS.DBF,
  1978. /u01/app/oracle/admin/orclasm/dpdump/IDXTBS.DBF
  1979. LOGFILE=tts_import.log
  1980. [root@rhel6_lhr dpdump]#

  1981. [oracle@rhel6 ~]$ impdp system/lhr parfile='/u01/app/oracle/admin/orclasm/dpdump/par.f'




  1982. ----檢視使用者的目錄許可權
  1983. column grantee format a10
  1984. column grantor format a10
  1985. column dir_name format a20
  1986. column dir_path format a50
  1987. column privilege format a10

  1988. break on dir_name
  1989. select
  1990.     d.directory_name dir_name,
  1991.     d.directory_path dir_path,
  1992.     p.privilege,
  1993.     p.grantee,
  1994.     p.grantor
  1995. from
  1996.     dba_tab_privs p,
  1997.     dba_directories d
  1998. where
  1999.     p.table_name = d.directory_name and
  2000.     p.grantee = upper('&user')
  2001. order by
  2002.     d.directory_name,
  2003.     p.privilege
  2004. /


  2005.  
  2006. CREATE USER LHRSYS IDENTIFIED BY LHRSYS;
  2007. GRANT UPDATE (ENAME,SAL) ON SCOTT.EMP TO LHRSYS;
  2008. GRANT UPDATE (ENAME) ON SCOTT.EMP TO LHRSYS;
  2009. GRANT SELECT ON SCOTT.EMP TO LHRSYS;
  2010. GRANT CONNECT TO LHRSYS;
  2011. GRANT CREATE JOB TO LHRSYS;

  2012. SELECT * FROM DBA_COL_PRIVS D WHERE D.GRANTEE='LHRSYS';
  2013. SELECT * FROM DBA_TAB_PRIVS D WHERE D.GRANTEE='LHRSYS';
  2014. SELECT * FROM DBA_SYS_PRIVS D WHERE D.GRANTEE='LHRSYS';
  2015. SELECT * FROM DBA_ROLE_PRIVS D WHERE D.GRANTEE='LHRSYS';




  2016. --檢視建立表SQL語句:
  2017. SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;
  2018. SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME) FROM USER_TABLES U;
  2019. --檢視建立索引的SQL語句:
  2020. SELECT DBMS_METADATA.GET_DDL('INDEX','PK_DEPT','SCOTT') FROM DUAL;
  2021. SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME) FROM USER_INDEXES U;
  2022. --檢視建立主鍵的SQL語句:
  2023. SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','EMP_PK') FROM DUAL;
  2024. --檢視建立外來鍵的SQL語句:
  2025. SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','EMP_FK_DEPT') FROM DUAL;
  2026. --檢視建立檢視(VIEW)的SQL語句:
  2027. SELECT DBMS_METADATA.GET_DDL('VIEW', 'MY_TABLES','SCOTT') FROM DUAL;
  2028. SELECT DBMS_METADATA.GET_DDL('VIEW', U.OBJECT_NAME)
  2029.   FROM USER_OBJECTS U
  2030.  WHERE OBJECT_TYPE = 'VIEW';
  2031. SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME=UPPER('&VIEW_NAME');
  2032. --檢視建立儲存過程(PROCEDURE)的SQL語句:
  2033. SELECT DBMS_METADATA.GET_DDL('PROCEDURE', U.OBJECT_NAME)
  2034.   FROM USER_OBJECTS U
  2035.  WHERE OBJECT_TYPE = 'PROCEDURE';
  2036. --檢視建立觸發器(TRIGGER)的SQL語句:
  2037. SELECT DBMS_METADATA.GET_DDL('TRIGGER', U.OBJECT_NAME)
  2038.   FROM USER_OBJECTS U
  2039.  WHERE OBJECT_TYPE = 'TRIGGER';
  2040. --檢視建立函式(FUNCTION)的SQL語句:
  2041. SELECT DBMS_METADATA.GET_DDL('FUNCTION', U.OBJECT_NAME)
  2042.   FROM USER_OBJECTS U
  2043.  WHERE OBJECT_TYPE = 'FUNCTION';
  2044. --檢視建立包(PACKAGE)的SQL語句:
  2045. SELECT DBMS_METADATA.GET_DDL('PACKAGE', U.OBJECT_NAME)
  2046.   FROM USER_OBJECTS U
  2047.  WHERE OBJECT_TYPE = 'PACKAGE';
  2048. --檢視建立序列(SEQUENCE)的SQL語句:
  2049. SELECT DBMS_METADATA.GET_DDL('SEQUENCE', U.OBJECT_NAME)
  2050.   FROM USER_OBJECTS U
  2051.  WHERE OBJECT_TYPE = 'SEQUENCE';
  2052. --檢視建立同義詞(SYNONYM)的SQL語句:
  2053. SELECT DBMS_METADATA.GET_DDL('SYNONYM', U.OBJECT_NAME)
  2054.   FROM USER_OBJECTS U
  2055.  WHERE OBJECT_TYPE = 'SYNONYM';
  2056. --檢視建立表空間(TABLESPACE)的SQL語句:
  2057. SELECT DBMS_METADATA.GET_DDL('TABLESPACE', U.TABLESPACE_NAME)
  2058.   FROM USER_TABLESPACES U;
  2059. --檢視建立角色(ROLE)的SQL語句:
  2060. SELECT DBMS_METADATA.GET_DDL('ROLE', U.ROLE) FROM DBA_ROLES U;
  2061. --檢視建立使用者(USER)的SQL語句:
  2062. SELECT DBMS_METADATA.GET_DDL('USER','SYS') FROM DUAL;

  2063. ------------------------- 得到表空間DDL語句
  2064. SELECT (SELECT b.NAME FROM v$tablespace b WHERE b.TS# = a.TS#) ts_name,
  2065.        a.NAME datafilename,
  2066.       'create tablespace '||(SELECT b.NAME FROM v$tablespace b WHERE b.TS# = a.TS#) || ' datafile ' || a.NAME ||' size ;'
  2067.   FROM v$datafile a;
  2068.  
  2069.  SELECT TABLESPACE_NAME,
  2070.         substr(create_ts, 1, instr(create_ts, 'EXTENT') - 1) || ';'
  2071.    FROM (SELECT a.TABLESPACE_NAME,
  2072.                 replace(to_char(DBMS_METADATA.GET_DDL('TABLESPACE',
  2073.                                                       a.tablespace_name)),
  2074.                         chr(10),
  2075.                         '') create_ts
  2076.            FROM DBA_TABLESPACES a) v
  2077.   where v.TABLESPACE_NAME not in
  2078.         ('SYSTEM', 'SYSAUX', 'UNDOTBS1', 'TEMP', 'USERS', 'EXAMPLE');
  2079.              

  2080. ------------------------- 得到使用者及其許可權的DDL語句
  2081. SELECT DBMS_METADATA.GET_DDL('USER','LHRSYS') DDL_SQL FROM DUAL
  2082. UNION ALL
  2083. SELECT ((DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'LHRSYS'))) FROM DUAL
  2084. UNION ALL
  2085. SELECT ((DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'LHRSYS'))) FROM DUAL
  2086. UNION ALL
  2087. SELECT ((DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'LHRSYS'))) FROM DUAL;


  2088. drop table t_tmp_user_lhr;
  2089. create table t_tmp_user_lhr( id number, username varchar2(50), exec_sql varchar2(4000),create_type varchar2(20) );
  2090. DROP sequence s_t_tmp_user_lhr;
  2091. create sequence s_t_tmp_user_lhr;

  2092. begin

  2093.   for cur in (SELECT d.username,
  2094.                      d.default_tablespace,
  2095.                      d.account_status,
  2096.                      'create user ' || d.username || ' identified by ' ||
  2097.                      d.username || ' default tablespace ' ||
  2098.                      d.default_tablespace || ' TEMPORARY TABLESPACE ' ||
  2099.                      D.temporary_tablespace || ';' CREATE_USER,
  2100.                      replace(to_char(DBMS_METADATA.GET_DDL('USER',
  2101.                                                            D.username)),
  2102.                              chr(10),
  2103.                              '') create_USER1
  2104.                 FROM dba_users d
  2105.   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
  2106.   
  2107.     INSERT INTO t_tmp_user_lhr
  2108.       (id, username, exec_sql, create_type)
  2109.     values
  2110.       (s_t_tmp_user_lhr.nextval, cur.username, cur.CREATE_USER, 'USER');
  2111.       
  2112.     INSERT INTO t_tmp_user_lhr
  2113.       (id, username, exec_sql, create_type)
  2114.       SELECT s_t_tmp_user_lhr.nextval,
  2115.              cur.username,
  2116.              CASE
  2117.                WHEN D.ADMIN_OPTION = 'YES' THEN
  2118.                 'GRANT ' || d.privilege || ' TO ' || d.GRANTEE ||
  2119.                 ' WITH GRANT OPTION ;'
  2120.                ELSE
  2121.                 'GRANT ' || d.privilege || ' TO ' || d.GRANTEE || ';'
  2122.              END priv,
  2123.              'DBA_SYS_PRIVS'
  2124.         FROM dba_sys_privs d
  2125.        WHERE D.GRANTEE = CUR.USERNAME;

  2126.     INSERT INTO t_tmp_user_lhr
  2127.       (id, username, exec_sql, create_type)
  2128.       SELECT s_t_tmp_user_lhr.nextval,
  2129.              cur.username,
  2130.              CASE
  2131.                WHEN D.ADMIN_OPTION = 'YES' THEN
  2132.                 'GRANT ' || d.GRANTED_ROLE || ' TO ' || d.GRANTEE ||
  2133.                 ' WITH GRANT OPTION;'
  2134.                ELSE
  2135.                 'GRANT ' || d.GRANTED_ROLE || ' TO ' || d.GRANTEE || ';'
  2136.              END priv,
  2137.              'DBA_ROLE_PRIVS'
  2138.         FROM DBA_ROLE_PRIVS d
  2139.        WHERE D.GRANTEE = CUR.USERNAME;

  2140.     INSERT INTO t_tmp_user_lhr
  2141.       (id, username, exec_sql, create_type)
  2142.       SELECT s_t_tmp_user_lhr.nextval,
  2143.              cur.username,
  2144.              CASE
  2145.                WHEN d.grantable = 'YES' THEN
  2146.                 'GRANT ' || d.privilege || ' ON ' || d.owner || '.' ||
  2147.                 d.table_name || ' TO ' || d.GRANTEE ||
  2148.                 ' WITH GRANT OPTION ;'
  2149.                ELSE
  2150.                 'GRANT ' || d.privilege || ' ON ' || d.owner || '.' ||
  2151.                 d.table_name || ' TO ' || d.GRANTEE || ';'
  2152.              END priv,
  2153.              'DBA_TAB_PRIVS'
  2154.         FROM DBA_TAB_PRIVS d
  2155.        WHERE D.GRANTEE = CUR.USERNAME;
  2156.   end loop;
  2157.   COMMIT;
  2158. end;
  2159. /
  2160.  SELECT * FROM t_tmp_user_lhr;


  2161.  
  2162. ------------------------------------------------------------------------------------------------------------------------------
  2163. ------------------------------------------------------------------------------------------------------------------------------


  2164. ---怎麼批量去除WORD裡表格中的超連結

  2165. 全選文件。按"Ctrl+shift+F9"斷開連線就行了。




  2166. ------------------------------------------------------------------------------------------------------------------------------
  2167. ------------------------------------------------------------------------------------------------------------------------------

  2168. ----linux下批量查詢/替換文字內容
  2169. --一般在本地電腦上批量替換文字有許多工具可以做到,比如sublime text ,但大多伺服器上都是無圖形介面的,為此收集了幾條針對linux命令列 實現批量替換文字內容的命令:
  2170. --1.批量查詢某個目下檔案的包含的內容,例如:

  2171. # grep -rn "要找查詢的文字" ./

  2172. [oracle@rhel6_lhr dpdump]$ grep -rn "ALTER SESSION SET EVENTS" ./
  2173. ./spool_result.sql:2:ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
  2174. ./spool_result.sql:3:ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
  2175. ./spool_result.sql:4:ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
  2176. ./spool_result.sql:5:ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
  2177. ./spool_result.sql:6:ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
  2178. ./spool_result.sql:7:ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';


  2179. --2.批量查詢並替換檔案內容。
  2180. # sed -i "s/要找查詢的文字/替換後的文字/g" `grep -rl "要找查詢的文字" ./`

  2181. 例如替換 被病毒修改的一段指令碼:
  2182. 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>" ./`

  2183. ----將STORAGE(INITIAL開頭的行整行替換為STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  2184. -- 必須加 點和星號和最後的g ,否則不能整行替換
  2185. sed 's/^STORAGE(INITIAL.*/STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645/g' a.txt > b.txt






  2186. -------------------------------------------------------------------------------------------------------------- 修改日期的顯示格式
  2187. execute immediate 'alter session set NLS_DATE_FORMAT=''YYYY-MM-DD DY HH24:MI:SS''';
  2188. alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
  2189. alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;


  2190. ----------------------------------------------------------------------------- sqlplus 設定
  2191.  --$ORACLE_HOME/sqlplus/admin/glogin.sql
  2192.  
  2193. set linesize 9999 pagesize 9999
  2194. set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "



  2195.  
  2196. set time on;
  2197. set line 9999
  2198. set pagesize 9999;
  2199. set timing on;
  2200. set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "



  2201. set echo on;
  2202. set time on;
  2203. SET LONG 99999999;
  2204. SET LONGCHUNKSIZE 1000000;
  2205. set timing on;
  2206. set serveroutput on size 1000000;
  2207. set sqlblanklines on;
  2208. set linesize 800;
  2209. set pagesize 50000;
  2210. set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
  2211. host color 02
  2212. alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
  2213. set errorlogging on table lhr.sperrorlog;
  2214. set errorlogging on identifier LHR_SESSION


  2215. --- alter session set nls_date_language='american' ;

  2216. --------------------------------------------------------rman備份-----------------------------------------------------------

  2217. backup as backupset database format '/u05/oracle/oracle_bk/orclasm/full_%n_%T_%t_%s_%p.bak' include current controlfile plus archivelog;
  2218. backup as compressed backupset format '/arch/oracle_bk/ora2lhr/full_%n_%T_%t_%s.bak' database include current controlfile plus archivelog delete input ;

  2219. ----------------------------- 歸檔 open 全備
  2220. export ORACLE_SID=ora11g
  2221. export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
  2222. export PATH=$ORACLE_HOME/bin:$PATH
  2223. MYDATE=`date +'%Y%m%d%H%M%S'`
  2224. BACKUP_DIR=/home/oracle/oracle_bk/$ORACLE_SID
  2225. mkdir -p $BACKUP_DIR/log

  2226.  
  2227. rman target / log $BACKUP_DIR/log/rman_full_$MYDATE.log append <<EOF
  2228. run
  2229. {
  2230.  allocate channel c1 type disk;
  2231.  allocate channel c2 type disk;
  2232.  backup database filesperset 4 format '$BACKUP_DIR/full_%n_%T_%t_%s_%p.bak';
  2233.  backup spfile tag='bk_spfile_$MYDATE' format='$BACKUP_DIR/spfile_%n_%U_%T.bak';
  2234.  sql 'alter system archive log current';
  2235.  backup archivelog all format '$BACKUP_DIR/arch_%d_%T_%s_%p.bak' delete input;
  2236.  backup current controlfile format '$BACKUP_DIR/ctl_%d_%T_%s_%p.bak';
  2237.  release channel c1;
  2238.  release channel c2;
  2239. }
  2240. EOF

  2241. rman target / log $BACKUP_DIR/log/rman_delete_$MYDATE.log append <<EOF
  2242.  allocate channel for maintenance type disk;
  2243.  allocate channel for maintenance type sbt_tape;
  2244.  crosscheck archivelog all;
  2245.  crosscheck backup;
  2246.  delete noprompt obsolete;
  2247.  delete noprompt expired archivelog all;
  2248. EOF



  2249. ----------------------------- 非歸檔 mount 全備
  2250. export ORACLE_SID=oralhr
  2251. export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
  2252. export PATH=$ORACLE_HOME/bin:$PATH
  2253. MYDATE=`date +'%Y%m%d%H%M%S'`
  2254. BACKUP_DIR=/home/oracle/oracle_bk/$ORACLE_SID
  2255. mkdir -p $BACKUP_DIR/log

  2256.  
  2257. rman target / log $BACKUP_DIR/log/rman_full_$MYDATE.log append <<EOF
  2258. run
  2259. {
  2260.  shutdown immediate;
  2261.  startup mount;
  2262.  allocate channel c1 type disk;
  2263.  allocate channel c2 type disk;
  2264.  backup database filesperset 4 format '$BACKUP_DIR/full_%n_%T_%t_%s_%p.bak';
  2265.  backup spfile tag='bk_spfile_$MYDATE' format='$BACKUP_DIR/spfile_%n_%U_%T.bak';
  2266.  backup archivelog all format '$BACKUP_DIR/arch_%d_%T_%s_%p.bak' delete input;
  2267.  backup current controlfile format '$BACKUP_DIR/ctl_%d_%T_%s_%p.bak';
  2268.  release channel c1;
  2269.  release channel c2;
  2270.  alter database open;
  2271. }
  2272. EOF

  2273. rman target / log $BACKUP_DIR/log/rman_delete_$MYDATE.log append <<EOF
  2274.  allocate channel for maintenance type disk;
  2275.  allocate channel for maintenance type sbt_tape;
  2276.  crosscheck archivelog all;
  2277.  crosscheck backup;
  2278.  delete noprompt obsolete;
  2279. EOF





  2280. --------------------------------------------------------冷備(最適合非歸檔)-----------------------------------------------------------
  2281. set feedback off
  2282. set heading off
  2283. set verify off
  2284. set trimspool off
  2285. set pagesize 0
  2286. set linesize 200
  2287. define dir = '/home/oracle/oracle_bk/coolbak'
  2288. define script = '/tmp/coolbak.sql'
  2289. spool &script
  2290. select 'ho cp ' || name || ' &dir' from v$controlfile
  2291. union all
  2292. select 'ho cp ' || name || ' &dir' from v$datafile
  2293. union all
  2294. select 'ho cp ' || member || ' &dir' from v$logfile
  2295. union all
  2296. select 'ho cp ' || name || ' &dir' from v$tempfile
  2297. /
  2298. create pfile = '&dir/initorcl.ora' from spfile;
  2299. ho cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwutf8test &dir
  2300. spool off
  2301. alter database backup controlfile to trace as '/home/oracle/oracle_bk/coolbak/ctl.sql';
  2302. shutdown immediate
  2303. start &script
  2304. --ho rm &script
  2305. startup




  2306. ---------有壞塊情況下的備份 ORA-19566: exceeded limit of 0 corrupt blocks for file /oracle/app/oracle/oralhr/users01.dbf
  2307. run{
  2308.   set maxcorrupt for datafile 4 to 2;
  2309.   backup datafile 4 tag='2corruptblock';
  2310. }





  2311. --------------------------------------------------------熱備(歸檔)-----------------------------------------------------------
  2312. --------------------------- 熱備 基於database (歸檔)
  2313. set feedback off
  2314. set heading off
  2315. set verify off
  2316. set trimspool off
  2317. set pagesize 0
  2318. set linesize 200
  2319. define dir = '/home/oracle/oracle_bk/hotbak'
  2320. define script = '/tmp/hotbak.sql'
  2321. spool &script
  2322. select 'ho cp ' ||name|| ' &dir' from v$datafile;
  2323. spool off
  2324. alter database begin backup;
  2325. start &script
  2326. alter database end backup;
  2327. alter database backup controlfile to trace as '&dir/controlbak.sql';
  2328. alter database backup controlfile to '&dir/controlbak.ctl';
  2329. create pfile = '&dir/initorcl.ora' from spfile;


  2330. --------------------------熱備 基於表空間 (歸檔)
  2331. set feedback off
  2332. set heading off
  2333. set verify off
  2334. set trimspool off
  2335. set pagesize 0
  2336. set linesize 200
  2337. define dir = '/home/oracle/oracle_bk/hotbak'
  2338. define script = '/tmp/hotbak_tb.sql'
  2339. spool &script
  2340. select 'alter tablespace '|| tablespace_name ||' begin backup ;' ||
  2341.  chr(10)||'ho cp ' || file_name || ' &dir ' ||
  2342.  chr(10)||'alter tablespace '|| tablespace_name || ' end backup;'
  2343.  from dba_data_files order by tablespace_name;
  2344. spool off
  2345. alter system switch logfile;
  2346. start &script
  2347. alter system switch logfile;
  2348. alter database backup controlfile to '&dir/controlbak.ctl';
  2349. alter database backup controlfile to trace as '&dir/controlbak.sql';
  2350. create pfile = '&dir/initorcl.ora' from spfile;


  2351. ----------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------
  2352. ----------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------
  2353. -------------------------------------- LogMiner 日誌挖掘
  2354. 要安裝LogMiner工具,必須首先要執行下面這樣兩個指令碼:
  2355. $ORACLE_HOME/rdbms/admin/dbmslm.sql
  2356. $ORACLE_HOME/rdbms/admin/dbmslmd.sql
  2357. 這兩個指令碼必須均以SYS使用者身份執行。其中第一個指令碼用來建立DBMS_LOGMNR包,該包用來分析日誌檔案。第二個指令碼用來建立DBMS_LOGMNR_D包,該包用來建立資料字典檔案。


  2358. ---設定單獨的表空間
  2359. create tablespace ts_LOGMNR datafile '/sda4/u01/app/oracle/oradata/orcllinux/logmnr.dbf' size 1G autoextend on next 2M ;
  2360. exec dbms_logmnr_d.set_tablespace('ts_logmnr');



  2361. ---------------- 附加日誌
  2362. ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
  2363. alter database add supplemental log data(primary key) columns;
  2364. ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
  2365. SELECT SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_pk FROM V$DATABASE;


  2366. ---找到需要進行日誌挖掘的歸檔日誌
  2367. list archivelog all completed between '2017-01-01 16:20:00' and '2017-01-01 16:25:00';
  2368. export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss';

  2369. exec dbms_logmnr.add_logfile('歸檔日誌檔案', Options => dbms_logmnr.new);
  2370. exec dbms_logmnr.add_logfile('歸檔日誌檔案', Options => dbms_logmnr.ADDFILE);

  2371. --若歸檔日誌不在本地,則需要恢復相應的歸檔日誌到本地目錄。
  2372.    run {allocate channel ci type disk;
  2373.       set archivelog destination to '/tmp';
  2374.       restore archvielog from logseq xxx until logseq xxx;
  2375.       release channel ci;
  2376.     };


  2377.    

  2378. -------------- 利用平面檔案作為資料字典
  2379. alter system set utl_file_dir='/home/oracle/' scope=spfile; --然後重啟庫
  2380. exec dbms_logmnr_d.build('log.ora','/home/oracle/',dbms_logmnr_d.store_in_flat_file);
  2381. exec dbms_logmnr.add_logfile('+FRA/orclasm/archivelog/2015_01_20/thread_1_seq_952.463.869481079',dbms_logmnr.new);
  2382. exec dbms_logmnr.add_logfile('+FRA/orclasm/archivelog/2015_01_20/thread_1_seq_953.462.869481107',dbms_logmnr.addfile);
  2383. exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/log.ora');
  2384. create table LHR.testlog as select * from v$logmnr_contents a;
  2385. EXEC DBMS_LOGMNR.END_LOGMNR;

  2386. -------------------------- redo 字典
  2387. ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
  2388. exec dbms_logmnr_d.build(options => dbms_logmnr_d.STORE_IN_REDO_LOGS);
  2389.  select a.*
  2390.    from v$archived_log a
  2391.   WHERE a.name IS NOT NULL
  2392.     and (a.DICTIONARY_BEGIN = 'YES' or a.DICTIONARY_END = 'YES');


  2393. -------------- 利用線上日誌作為資料字典
  2394. SELECT ' dbms_logmnr.add_logfile(''' || MEMBER || ''');' FROM v$logfile;

  2395. BEGIN
  2396.   dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_3.263.850260263',dbms_logmnr.new) ;
  2397.   dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_2.262.850260259',dbms_logmnr.ADDFILE) ;
  2398.   dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_1.261.850260255',dbms_logmnr.ADDFILE)) ;
  2399.   dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only);
  2400. END;

  2401. SELECT scn, sql_redo, a.SQL_UNDO, a.*
  2402.   FROM v$logmnr_contents a
  2403.  WHERE a.OPERATION = 'INSERT'
  2404.    and a.TABLE_NAME = 'AABB';

  2405. create table testlog as select * from v$logmnr_contents a;

  2406. begin
  2407.   dbms_logmnr.end_logmnr();
  2408. end;



  2409. EXECUTE DBMS_LOGMNR_D.BUILD (OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);




  2410. -------------------------- online字典
  2411. begin
  2412.  dbms_logmnr.start_logmnr(startScn => 23573690,
  2413.                           endScn => 23632671,
  2414.                           Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG + dbms_logmnr.CONTINUOUS_MINE);
  2415. end;



  2416. begin
  2417.  dbms_logmnr.start_logmnr(startScn => 23573690,
  2418.                                    endScn => 23632671,
  2419.                                    Options => dbms_logmnr.DICT_FROM_REDO_LOGS +
  2420.                                                dbms_logmnr.CONTINUOUS_MINE);
  2421. end;


  2422. export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss';
  2423. list archivelog all completed between '2015-06-03 08:00:00' and '2015-06-03 09:00:00';
  2424. 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);
  2425. BEGIN
  2426.     DBMS_LOGMNR.START_LOGMNR(
  2427.     STARTTIME => '2015-06-03 11:10:12',
  2428.     ENDTIME => '2015-06-03 11:13:06',
  2429.     OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
  2430. END;
  2431. /

  2432. ---查詢挖掘到的結果
  2433. select a.SCN,a.TIMESTAMP,a.SQL_REDO from v$logmnr_contents A where table_name='XXXX' and OPERATION='INSERT' order by a.SCN;




  2434. --清除控制檔案中關於v$archived_log的資訊
  2435. execute sys.dbms_backup_restore.resetCfileSection(11);

  2436. ---恢復歸檔
  2437.    1.恢復全部歸檔日誌檔案
  2438.      RMAN> restore archivelog all;
  2439.    2.只恢復5到8這四個歸檔日誌檔案
  2440.      RMAN> restore archivelog from logseq 5 until logseq 8;
  2441.    3.恢復從第5個歸檔日誌起
  2442.      RMAN> restore archivelog from logseq 5;
  2443.    4.恢復7天內的歸檔日誌
  2444.      RMAN> restore archivelog from time 'sysdate-7';
  2445.    5. sequence between 寫法
  2446.      RMAN> restore archivelog sequence between 1 and 3;
  2447.    6.恢復到哪個日誌檔案為止
  2448.      RMAN> restore archivelog until logseq 3;
  2449.    7.從第五個日誌開始恢復
  2450.      RMAN> restore archivelog low logseq 5;
  2451.    8.到第5個日誌為止
  2452.      RMAN> restore archivelog high logseq 5;
  2453. 恢復指定的archivelog:restore archivelog sequence 18;
  2454. --若歸檔日誌不在本地,則需要恢復相應的歸檔日誌到本地目錄。
  2455.    run {allocate channel ci type disk;
  2456.       set archivelog destination to '/tmp';
  2457.       restore archvielog from logseq xxx until logseq xxx;
  2458.       release channel ci;
  2459.     };



  2460. ------------------------------------------------------------------------------------------ 詳細執行計劃

  2461. ---------------Session級別:
  2462. ALTER SESSION SET STATISTICS_LEVEL=ALL;
  2463. ----------------- 詫句級別 使用HINT
  2464. select /*+ gather_plan_statistics*/ ...


  2465. SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f6cz4n8y72xdc',0,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));





  2466. -------------------------------------------------------------------------------------------- 其他


  2467.  
  2468.  SELECT * FROM DBA_STREAMS_UNSUPPORTED;
  2469.  
  2470.   ------------------- 鎖和事務關係
  2471. select * from v$lock a where type in ('TM','TX') ;

  2472. SELECT * from v$transaction;

  2473.  select trunc(655385/power(2,16)) XIDUSN from dual;
  2474. 10

  2475.   select bitand(655385,to_number('ffff','xxxx'))+0 XIDSLOT from dual;

  2476. 25
  2477.  
  2478.  
  2479.  ---------------------------------truncate 後的恢復-------------------------- 恢復的過程會多次執行 alter system flush buffer_cache;


  2480. ------------ tmp目錄空閒空間需要比表的大小大
  2481. create table lhr.TRUNTAB as SELECT * FROM dba_objects where rownum<=10;

  2482. SELECT COUNT(1) FROM lhr.TRUNTAB;

  2483. truncate table lhr.TRUNTAB;

  2484. exec fy_recover_data.recover_truncated_table('LHR','TRUNTAB',1);

  2485. SELECT * FROM lhr.TRUNTAB$$;
  2486. insert into lhr.TRUNTAB SELECT * FROM lhr.TRUNTAB$$;
  2487. commit;
  2488. drop tablespace FY_REC_DATA including contents and datafiles;
  2489. drop tablespace FY_RST_DATA including contents and datafiles;

  2490.  





  2491.  ---閃回
  2492. alter table TRUNTAB enable row movement;
  2493. flashback table TRUNTAB to timestamp to_date('2014/12/31 17:33:00','YYYY/MM/DD HH24:MI/SS');


  2494.  


  2495. --------------長查詢
  2496. SELECT * FROM vw_active_session_lhr a where a.USERNAME IS NOT NULL ;
  2497. SELECT * FROM vw_longrun_lhr;
  2498. SET LINE 9999 PAGESIZE 9999
  2499. col username format a10
  2500. col session_info format a30
  2501. col target format a20
  2502. col opname format a35
  2503. col message format a80
  2504. col sofar_TOTALWORK format a20
  2505. col progress format a8

  2506. SELECT A.USERNAME,
  2507.        (SELECT NB.SID || ',' || NB.SERIAL# || ',' || PR.SPID || ',' ||NB.OSUSER|| ',' ||nb.status|| ',' ||nb.EVENT
  2508.           FROM GV$PROCESS PR, GV$SESSION NB
  2509.          WHERE NB.PADDR = PR.ADDR
  2510.            AND NB.SID = A.SID
  2511.            AND NB.SERIAL# = A.SERIAL#
  2512.            AND PR.INST_ID = NB.INST_ID) SESSION_INFO,
  2513.        A.TARGET,
  2514.        A.OPNAME,
  2515.        TO_CHAR(A.START_TIME, 'YYYY-MM-DD HH24:MI:SS') START_TIME,
  2516.        ROUND(A.SOFAR * 100 / A.TOTALWORK, 2) || '%' AS PROGRESS,
  2517.        (A.SOFAR || ':' || A.TOTALWORK) SOFAR_TOTALWORK,
  2518.        A.TIME_REMAINING TIME_REMAINING,
  2519.        A.ELAPSED_SECONDS ELAPSED_SECONDS,
  2520.        MESSAGE MESSAGE
  2521.   FROM GV$SESSION_LONGOPS A
  2522.  WHERE A.TIME_REMAINING <> 0
  2523.  ORDER BY A.TIME_REMAINING DESC, A.SQL_ID, A.SID;






  2524. SELECT * FROM vw_tablespace_datafile_lhr ;

  2525. SELECT * FROM xb_audit_ddl_lhr a WHERE a.id >=2373180 ;
  2526.  
  2527.  
  2528.  ----------------------------------------------------------- 歸檔
  2529. alter system set log_archive_dest_1='location=D:\arch';
  2530. alter system set log_archive_dest='USE_DB_RECOVERY_FILE_DEST';

  2531. show parameter DB_RECOVERY_FILE_DEST


  2532.  如果歸檔日誌為自動歸檔,則切換日誌(alter system switch logfile)會自動歸檔;
  2533.  如果為手動歸檔模式,則不會歸檔,除非你執行下列命令手動歸檔:
  2534. alter system archive log sequence lognumber
  2535.  alter system archive log all
  2536.  alter system archive log current

  2537. 檢視歸檔日誌模式:select log_mode from v$database;
  2538.  NOARCHIVELOG-- 為不歸檔
  2539. ARCHIVELOG -- 為自動歸檔
  2540. MANUAL --手動歸檔模式
  2541.  修改歸檔日誌模式:
  2542. alter database noarchivelog;
  2543. alter database archivelog;
  2544. alter database archivelog manual;
  2545.  
  2546.  
  2547.  
  2548.  ----------------通過移動資料檔案來均衡檔案I/O
  2549. col PHYRDS format 999999999
  2550. col PHYWRTS format 999999999
  2551. col READTIM format 999999999
  2552. col WRITETIM format 999999999
  2553. col name for a60
  2554. set line 9999 pagesize 9999
  2555. select name,phyrds,phywrts,readtim,writetim
  2556. from v$filestat a,v$datafile b
  2557. where a.file#=b.file#
  2558. union all
  2559. select name,PHYRDS,PHYWRTS,READTIM,WRITETIM from v$tempstat a,v$tempfile b where a.file#=b.file#
  2560. order by readtim desc;
  2561.  




  2562.  
  2563.   /*檢視錶最後一次DML時間*/
  2564. select max(ora_rowscn), to_char(scn_to_timestamp(max(ora_rowscn)),'YYYY-MM-DD HH24:MI:SS') from aa;
  2565. 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;

  2566. 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;

  2567. ENAME SAL BLOCK# FILE#
  2568. ---------- ---------- ---------- ----------
  2569. KING 5000 32 4

  2570. SQL>

  2571.  
  2572.    ------------------------------------------ 日誌切換頻率
  2573. select b.SEQUENCE#,
  2574.        b.FIRST_TIME,
  2575.        a.SEQUENCE#,
  2576.        a.FIRST_TIME,
  2577.        round(((a.FIRST_TIME - b.FIRST_TIME) * 24) * 60, 2) min
  2578.   from v$log_history a, v$log_history b
  2579.  where a.SEQUENCE# = b.SEQUENCE# + 1
  2580.    and b.THREAD# = 1
  2581.  order by a.SEQUENCE# desc;

  2582.    
  2583.    select sequence#,
  2584.           first_time,
  2585.           nexttime,
  2586.           round(((first_time - nexttime) * 24) * 60, 2) diff
  2587.      from (select sequence#,
  2588.                   first_time,
  2589.                   lag(first_time) over(order by sequence#) nexttime
  2590.              from v$log_history
  2591.             where thread# = 1)
  2592.     order by sequence# desc;
  2593.         
  2594.         
  2595. select max (first_time) max_first_time,
  2596.          to_char (first_time, 'yyyy-mm-dd') day,
  2597.          count (recid) count_number,
  2598.          count (recid) * 200 size_mb
  2599.  from v$log_history
  2600. group by to_char (first_time, 'yyyy-mm-dd')
  2601. order by 1;


  2602. --------------------------------------------------------------------------------------------------- 開啟10046事件

  2603. 10046事件級別:

  2604. level 0:禁用SQL_TRACE,等價於SQL_TRACE=FALSE
  2605. level 1:啟用標準的sql_trace功能跟蹤SQL語句,包括解析、執行、提取、提交和回滾等,等價於SQL_TRACE=TRUE
  2606. level 4:Level 1 +包括變數(bind values)的詳細資訊
  2607. level 8:Level 1 + 包括等待事件
  2608. level 12:包括繫結變數與等待事件,包含Level 1 + Level 4 + Level 8



  2609. alter session set SQL_TRACE=true;
  2610. alter session set SQL_TRACE=false;
  2611. alter session set events '10046 trace name context forever, level 12';
  2612. alter session set events '10046 trace name context off';
  2613. alter session set events '10046 trace name context off, LEVEL 12';
  2614. --對單個 SQL ID 開啟10046事件跟蹤
  2615. ALTER SYSTEM SET EVENTS 'SQL_TRACE [SQL:&&SQL_ID] BIND=TRUE,WAIT=TRUE';
  2616. ALTER SYSTEM SET EVENTS 'SQL_TRACE [SQL:C7452AGJ0S0T6] WAIT=TRUE,BIND=TRUE,PLAN_STAT=ALL_EXECUTIONS,LEVEL=12';
  2617. --關閉單個SQL的跟蹤命令如下所示:
  2618. ALTER SYSTEM SET EVENTS 'SQL_TRACE [SQL:&&SQL_ID] OFF';


  2619. exec dbms_session.set_sql_trace(true);
  2620. exec dbms_session.set_sql_trace(false);
  2621. exec dbms_session.session_trace_enable(waits=>true,binds=>true);
  2622. exec dbms_session.session_trace_enable(); 



  2623. -----跟蹤其它會話
  2624. SQL> exec dbms_system.set_ev(sid,serial#,10046,12,'');
  2625. SQL> exec dbms_system.set_ev(sid,serial#,10046,0,'');

  2626. exec dbms_system.set_sql_trace_in_session(9,437,true);
  2627. exec dbms_system.set_sql_trace_in_session(9,437,false);


  2628. SQL> exec dbms_monitor.session_trace_enable;
  2629. SQL> 執行sql
  2630. SQL> exec dbms_monitor.session_trace_disable;
  2631. 跟蹤其他會話:
  2632. SQL> exec dbms_monitor.session_trace_enable(session_id=>sid,serial_num=>serial#,waits=>true,binds=>true);
  2633. SQL> exec dbms_monitor.session_trace_disable(session_id=>sid,serial_num=>serial#);




  2634. --跟蹤當前會話:
  2635. SQL> oradebug setmypid;
  2636. Statement processed.
  2637. SQL> oradebug unlimit;
  2638. Statement processed.
  2639. SQL> oradebug event 10046 trace name context forever,level 12;
  2640. Statement processed.
  2641. SQL> 執行sql
  2642. SQL> oradebug tracefile_name
  2643. SQL> oradebug event 10046 trace name context off;
  2644. Statement processed.
  2645. --跟蹤其他會話:
  2646. SQL> select spid,pid2 from v$process
  2647.   2 where addr in (select paddr from v$session where sid=(select distinct sid from v$mystat));
  2648. SPID PID
  2649. ------------ ----------
  2650. 1457 313
  2651. SQL> oradebug setospid 1457;
  2652. Statement processed.
  2653. 或者
  2654. SQL> oradebug setorapid 313;
  2655. Statement processed.
  2656. SQL> oradebug unlimit;
  2657. Statement processed.
  2658. SQL> oradebug event 10046 trace name context forever,level 12;
  2659. Statement processed.
  2660. SQL> oradebug tracefile_name
  2661. SQL> oradebug event 10046 trace name context off;
  2662. Statement processed.





  2663. SELECT a.SID,
  2664.        b.SERIAL# ,
  2665.        c.SPID
  2666. FROM v$mystat a,
  2667.        v$session b ,
  2668.        v$process c
  2669. WHERE a.SID = b.SID
  2670. and b.PADDR=c.ADDR
  2671. AND rownum = 1;


  2672. --啟用errorstack的跟蹤來找到出現問題的SQL語句
  2673. alter session set events '3001 trace name errorstack level 3';

  2674. ---跟蹤1438的錯誤
  2675. alter system set events='1438 trace name errorstack forever,level 3';
  2676. alter system set events='1438 trace name errorstack off';


  2677. 系統預設沒有安裝dbms_support這個包,可以手動執行$ORACLE_HOME/rdbms/admin/bmssupp.sql指令碼來建立該包跟蹤當前會話:
  2678. SQL> exec dbms_support.start_trace
  2679. SQL> 執行sql
  2680. SQL> exec dbms_support.stop_trace
  2681. 跟蹤其他會話:等待事件+繫結變數,相當於level 12的10046事件。
  2682. SQL> select sid,serial#,username from v$session where ...;
  2683. SQL> exec dbms_support.start_trace_in_session(sid=>sid,serial=>serial#,waits=>true,binds=>true);
  2684. SQL> exec dbms_support.stop_trace_in_session(sid=>sid,serial=>serial#);


  2685. ----select value from v$diag_info where name like '%Default%';

  2686. --轉儲檔案路徑
  2687. col TRACE_FILE_NAME format a100
  2688. SELECT d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
  2689.        p.spid || '.trc' trace_file_name
  2690. FROM (SELECT p.spid
  2691.         FROM v$mystat m,
  2692.                v$session s,
  2693.                v$process p
  2694.         WHERE m.statistic# = '1'
  2695.         AND s.sid = m.sid
  2696.         AND p.addr = s.paddr) p,
  2697.        (SELECT t.instance
  2698.         FROM v$thread t,
  2699.                v$parameter v
  2700.         WHERE v.name = 'thread'
  2701.         AND (v.value = '0' OR to_char(t.thread#) = v.VALUE)) i,
  2702.        (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;

  2703. CREATE OR REPLACE VIEW VW_TRACEFILE_ALL_LHR AS
  2704. SELECT INST_ID,
  2705.        SID,
  2706.        SERIAL#,
  2707.        SPID,
  2708.        USERNAME,
  2709.        D.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
  2710.        P.SPID || '.trc' TRACE_FILE_NAME
  2711.   FROM (SELECT S.INST_ID, S.SID, S.SERIAL#, P.SPID, S.USERNAME
  2712.           FROM GV$SESSION S, GV$PROCESS P
  2713.          WHERE P.ADDR = S.PADDR
  2714.            AND S.INST_ID = P.INST_ID) P,
  2715.        (SELECT T.INSTANCE
  2716.           FROM GV$THREAD T, GV$PARAMETER V
  2717.          WHERE V.NAME = 'thread'
  2718.            AND (V.VALUE = '0' OR TO_CHAR(T.THREAD#) = V.VALUE)) I,
  2719.        (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') D;

  2720. --建立公共同義詞:
  2721. CREATE OR REPLACE PUBLIC SYNONYM SYN_TRACEFILE_ALL_LHR FOR VW_TRACEFILE_ALL_LHR;

  2722. 在Oracle 11g中可以直接查詢V$PROCESS獲取TRACE檔案:
  2723. CREATE OR REPLACE VIEW VW_TRACEFILE_LHR AS
  2724. SELECT S.INST_ID, S.SID, S.SERIAL#, P.SPID, S.USERNAME, P.TRACEFILE
  2725.   FROM GV$SESSION S, GV$PROCESS P
  2726.  WHERE P.ADDR = S.PADDR
  2727.    AND S.INST_ID = P.INST_ID ;

  2728. --建立公共同義詞:
  2729. CREATE OR REPLACE PUBLIC SYNONYM SYN_TRACEFILE_LHR FOR VW_TRACEFILE_LHR;



  2730. --------------------------如何生成 systemstate dump systemdump systemstate
  2731. ###sysdba可登陸時
  2732. $sqlplus "/as sysdba"
  2733. SQL>oradebug setmypid
  2734. SQL>--其中266表示dump的級別,不用調整,即把系統當前各個程式正在執行什麼、正在等待什麼全部抓下來
  2735. SQL>oradebug dump systemstate 266 --oradebug dump systemdump 266;
  2736. SQL>--等上30秒到1分鐘
  2737. SQL>oradebug dump systemstate 266


  2738. ###sysdba不可登陸時
  2739. sqlplus -prelim "/as sysdba"
  2740. SQL>oradebug setmypid
  2741. SQL>--其中266表示dump的級別,不用調整,即把系統當前各個程式正在執行什麼、正在等待什麼全部抓下來
  2742. SQL>oradebug dump systemstate 266
  2743. SQL>--等上30秒到1分鐘
  2744. SQL>oradebug dump systemstate 266
  2745. SQL> oradebug tracefile_name
  2746. /u02/app/oracle/diag/rdbms/lhrdb/lhrdb/trace/lhrdb_ora_46679.trc


  2747. ----------HANGANALYZE 分析
  2748. SQL> alter session set events 'immediate trace name HANGANALYZE level 3';
  2749. 或者:
  2750. SQL>ORADEBUG hanganalyze 3 --for 單例項
  2751.        
  2752. ------for RAC 例項
  2753. SQL>ORADEBUG setmypid
  2754. SQL>ORADEBUG setinst all
  2755. SQL>ORADEBUG -g def hanganalyze 3     
  2756.        
  2757. The levels are defined as follows:
  2758. 10    Dump all processes (IGN state)
  2759. 5     Level 4 + Dump all processes involved in wait chains (NLEAF state)
  2760. 4     Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)
  2761. 3     Level 2 + Dump only processes thought to be in a hang (IN_HANG state)
  2762. 1-2     Only HANGANALYZE output, no process dump at all



  2763. ----------------------------------------------------------------------------------------------------- 層次查詢
  2764.  SELECT LEVEL,
  2765.         id,
  2766.         parentid,
  2767.         (lpad(' ', 8 * (LEVEL - 1)) || LEVEL || ':' || l.name) names,
  2768.         substr(SYS_CONNECT_BY_PATH(NAME, '=>'), 3),
  2769.         connect_by_root(NAME) root,
  2770.         decode(LEVEL,
  2771.                2,
  2772.                NAME,
  2773.                substr(SYS_CONNECT_BY_PATH(NAME, '=>'),
  2774.                       instr(SYS_CONNECT_BY_PATH(NAME, '=>'), '>', 1, 2) + 1,
  2775.                       (instr(SYS_CONNECT_BY_PATH(NAME, '=>'), '=', 1, 3) -
  2776.                       instr(SYS_CONNECT_BY_PATH(NAME, '=>'), '>', 1, 2) - 1))) root2,
  2777.         decode(connect_by_isleaf, 1, 'Y', 0, 'N') is_leaf,
  2778.         decode(connect_by_iscycle, 1, 'Y', 0, 'N') is_leaf
  2779.  FROM xb_location l
  2780.  START WITH l.parentid IS NULL
  2781.  CONNECT BY nocycle PRIOR l.id = l.parentid;




  2782. ----------------------------------------------------------------------------------------------------- 啟用系統觸發器
  2783. alter system set "_system_trig_enabled"=true; --預設
  2784. alter system reset "_system_trig_enabled" scope=spfile sid='*';


  2785.  
  2786. ----------------------------------------------------------------------------------------------------- 閃回

  2787. select * from user_recyclebin t where t.original_name LIKE'TMP_AB%';
  2788. select * from dba_recyclebin;

  2789. --ddl
  2790. flashback table TMP_AB to before drop rename to old_t;
  2791. flashback table "BIN$B/HqKSpfWrvgU4I7qMATlg==$0" to before drop;

  2792. --dml
  2793. alter table old_t enable row movement;
  2794. flashback table old_t to timestamp to_date('2012/11/13 16:16:40','YYYY/MM/DD HH24:MI/SS');


  2795.  
  2796. --insert into xb_port
  2797. SELECT *
  2798. FROM xb_port AS OF TIMESTAMP TO_TIMESTAMP('2013-04-25 16:53:28', 'YYYY-MM-DD HH24:MI:SS')
  2799. WHERE id = 307247374 ;
  2800. commit;



  2801. SELECT timestamp_to_scn(TO_TIMESTAMP('2012-11-13 16:25:17',
  2802.                                      'YYYY-MM-DD HH24:MI:SS')),
  2803.        to_char(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) AS SCN
  2804. FROM dual;

  2805. ----查詢
  2806. SELECT versions_starttime,
  2807.        versions_startscn,
  2808.        versions_endtime,
  2809.        versions_endscn,
  2810.        versions_xid,
  2811.        versions_operation,
  2812.        id,
  2813.        NAME
  2814. 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')
  2815. WHERE versions_xid IS NOT NULL
  2816. ORDER BY versions_starttime;


  2817. SELECT versions_starttime,
  2818.        versions_startscn,
  2819.        versions_endtime,
  2820.        versions_endscn,
  2821.        versions_xid,
  2822.        versions_operation,
  2823.        id,
  2824.        NAME
  2825. FROM xb_location versions BETWEEN scn minvalue AND maxvalue
  2826. WHERE versions_xid IS NOT NULL
  2827. ORDER BY versions_starttime;

  2828.  
  2829. ------------------------------------------------------- undo表空間
  2830.  --1、系統段 2、非系統段 3、表空間離線後的defered段
  2831. SELECT d.segment_type,
  2832.        COUNT(1)
  2833. FROM dba_segments d
  2834. GROUP BY d.segment_type;

  2835. select * from dba_segments d where d.segment_type in ('TYPE2 UNDO','ROLLBACK') ;
  2836. select * from dba_rollback_segs;
  2837. select * from dba_undo_extents;
  2838. select * from v$transaction;
  2839. select * from v$rollstat;
  2840. select * from v$rollname;
  2841. select * from dba_extents d where d.segment_name='_SYSSMU25_17381587$';
  2842. select * from v$undostat; ---mount狀態可查
  2843. --ssolderrcnt : snapshot too old error count

  2844. select status,count(*) from dba_rollback_segs group by status;

  2845. SELECT d.TABLESPACE_NAME,
  2846.        d.STATUS,
  2847.        SUM(bytes) / 1024 / 1024
  2848. FROM dba_undo_extents d
  2849. GROUP BY d.TABLESPACE_NAME,
  2850.           d.status
  2851. ORDER BY d.TABLESPACE_NAME;

  2852. SELECT r.tablespace_name,
  2853.        r.status "Status",
  2854.        r.segment_name "Name",
  2855.        s.extents "Extents",
  2856.        TO_CHAR((s.bytes / 1024 / 1024), '99999990.000') "SizeM",
  2857.        s.segment_type
  2858. FROM dba_rollback_segs r,
  2859.        dba_segments s
  2860. WHERE r.segment_name = s.segment_name
  2861. AND s.segment_type IN ('ROLLBACK', 'TYPE2 UNDO')
  2862. ORDER BY r.tablespace_name,
  2863.           5 DESC;

  2864. ----估算undo需要的大小
  2865. 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'));


  2866. -------------------- 已用大小
  2867. set line 9999
  2868. select s.sid,
  2869.        s.serial#,
  2870.        s.sql_id,
  2871.        v.usn,
  2872.        segment_name,
  2873.        r.status,
  2874.        v.rssize / 1024 / 1024 mb
  2875.   From dba_rollback_segs r, v$rollstat v, v$transaction t, v$session s
  2876.  Where r.segment_id = v.usn
  2877.    and v.usn = t.xidusn
  2878.    and t.addr = s.taddr
  2879.  order by segment_name;


  2880.  ------undo總大小
  2881.  
  2882. SET ECHO OFF
  2883. SET FEEDBACK 6
  2884. SET HEADING ON
  2885. SET LINESIZE 180
  2886. SET PAGESIZE 50000
  2887. SET TERMOUT ON
  2888. SET TIMING OFF
  2889. SET TRIMOUT ON
  2890. SET TRIMSPOOL ON
  2891. SET VERIFY OFF

  2892. COLUMN status FORMAT a9 HEADING 'Status'
  2893. COLUMN name FORMAT a30 HEADING 'Tablespace Name'
  2894. COLUMN type FORMAT a15 HEADING 'TS Type'
  2895. COLUMN extent_mgt FORMAT a10 HEADING 'Ext. Mgt.'
  2896. COLUMN segment_mgt FORMAT a10 HEADING 'Seg. Mgt.'
  2897. COLUMN ts_size FORMAT 9,999,999,999,999 HEADING 'Tablespace Size'
  2898. COLUMN used FORMAT 9,999,999,999,999 HEADING 'Used (in bytes)'
  2899. COLUMN free FORMAT 9,999,999,999,999 HEADING 'Free (in bytes)'
  2900. COLUMN pct_used FORMAT 999 HEADING 'Pct. Used'


  2901. SELECT
  2902.     d.status status
  2903.   , d.tablespace_name name
  2904.   , d.contents type
  2905.   , d.extent_management extent_mgt
  2906.   , d.segment_space_management segment_mgt
  2907.   , NVL(a.bytes, 0) ts_size
  2908.   , NVL(a.bytes - NVL(f.bytes, 0), 0) used
  2909.   , NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) pct_used
  2910. FROM
  2911.     sys.dba_tablespaces d
  2912.   , ( select tablespace_name, sum(bytes) bytes
  2913.       from dba_data_files
  2914.       group by tablespace_name
  2915.     ) a
  2916.   , ( select tablespace_name, sum(bytes) bytes
  2917.       from dba_free_space
  2918.       group by tablespace_name
  2919.     ) f
  2920. WHERE
  2921.       d.tablespace_name = a.tablespace_name(+)
  2922.   AND d.tablespace_name = f.tablespace_name(+)
  2923.   AND d.tablespace_name like '%UNDO%'
  2924. ORDER BY
  2925.   2;



  2926. --資料檔案管理引數: db_create_file_dest
  2927. CREATE TABLESPACE test DATAFILE SIZE 10m;
  2928. alter database datafile 3 resize 5G;
  2929. alter database tempfile 1 resize 2G;
  2930. alter database datafile 3 autoextend off;
  2931. alter database tempfile 1 autoextend off;





  2932. -------------- 重建undo表空間
  2933. create undo tablespace undotbs2 datafile '+DATA' size 100m reuse autoextend off;
  2934. alter system set undo_tablespace=undotbs2;
  2935. drop tablespace undotbs1 including contents and datafiles;
  2936. create undo tablespace undotbs1 datafile '+DATA' size 100m reuse autoextend off;
  2937. alter system set undo_tablespace=undotbs1;
  2938.         
  2939. alter database datafile 3 autoextend off;
  2940. alter database tempfile 1 autoextend off;



  2941. create undo tablespace undotbs2 datafile '+DATA' size 5M;

  2942. alter system set undo_tablespace=undotbs2;
  2943. alter tablespace undotabs2 retention guarantee;


  2944. select t.rowid,dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) bno, t.owner,t.object_name from t ;

  2945. alter system dump datafile 4 block 6643;

  2946. select * from vw_mysession_lhr;


  2947. UBA : undo block address



  2948. select * from v$obsolete_parameter;


  2949. alter database create datafile 3 as '/u03/app/oracle/oradata/ora1024g/undotbs01.dbf' size 50m;


  2950. ------- undo
  2951. alter system set "_offline_rollback_segments"=true scope=spfile;
  2952. alter system set "_offline_rollback_segments"=false scope=spfile; ---預設
  2953. alter system reset "_offline_rollback_segments" scope=spfile sid='*';


  2954. *._offline_rollback_segments=('_SYSSMU154_3691636531$','_SYSSMU155_3686385895$','_SYSSMU156_3796802683$','_SYSSMU157_2723916652$','_SYSSMU158_1435464080$')


  2955. _offline_rollback_segment='_SYSSMU3$'
  2956. _newsort_enabled --排序

  2957.  
  2958. -------- 如果undo為recover狀態的話還需要加如下引數
  2959. alter system set "_corrupted_rollback_segments"='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$' scope=spfile;

  2960. alter system reset "_corrupted_rollback_segments" scope=spfile sid='*';


  2961.  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;

  2962. *._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')


  2963.  drop rollback segment "drop rollback segment";
  2964. drop rollback segment "_SYSSMU154_3691636531$";



  2965. ---- 查詢undo段
  2966. strings /u01/app/oracle/oradata/ora11g/system01.dbf | grep _SYSSMU | sort -u >/tmp/system.txt

  2967. more /tmp/system.txt

  2968. 注意:通過system01.dbf查出了,正在使用的undo segment,以上按使用時間做了排序,注意只選擇那些排在最前面的(相同回滾段);預設每個undo tablespace 會應用10個undo segments。



  2969. ------- 不能建立undo檔案(ORA-01178錯誤),無備份的情況下采用隱含引數啟動資料庫
  2970. set line 9999
  2971. col name format a100
  2972. select file#, name,status,enabled from v$datafile;

  2973. select * from v$recover_file;

  2974. alter system set undo_management=manual scope=spfile;
  2975. alter database datafile 3 offline;
  2976. alter system set undo_tablespace=SYSTEM scope=spfile;
  2977. alter system set "_offline_rollback_segments"=true scope=spfile;
  2978. ! strings '/u01/app/oracle/oradata/orcltest/system01.dbf' | grep _SYSSMU | sort -u
  2979.  alter system set
  2980. "_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;
  2981. shutdown immediate;
  2982. startup mount;
  2983. alter database open;
  2984. select segment_name,status,tablespace_name from dba_rollback_segs;
  2985. drop tablespace UNDOTBS1;
  2986. create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/orcltest/undotbs01.dbf' size 50m autoextend on;
  2987. alter system set undo_tablespace=UNDOTBS1 scope=spfile;
  2988. alter system set undo_management=auto scope=spfile;
  2989. alter system reset "_offline_rollback_segments" scope=spfile sid='*';
  2990. alter system reset "_corrupted_rollback_segments" scope=spfile sid='*';
  2991. shutdown immediate;
  2992. startup



  2993. ---注意回滾段的命名規範:
  2994. 11g: _SYSSMU1_1189172979$、 _SYSSMU2_1189172979$ 。。。_SYSSMU10_1189172979$
  2995. 10g、9i:_SYSSMU1$、_SYSSMU2$、_SYSSMU3$ 。。。。_SYSSMU10$
  2996. 8i 為rollbackspace 即RBS空間:RBS0、RBS1、RBS2 。。。。RBS6
  2997. 7.3 :RB1、RB2.。。。。RB6






  2998. *.LOCAL_LISTENER='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523)))'
  2999. *.LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522))';

  3000. ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523)))';
  3001. ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522))';

  3002. 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)))';
  3003. 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))';



  3004. ALTER SYSTEM REGISTER;






  3005. ------------ 資料檔案自動擴充套件

  3006. alter database datafile 5 autoextend on next 5M;
  3007. 如果是bigfile可以採用: ALTER TABLESPACE TBS2 AUTOEXTEND ON NEXT 20G;

  3008. 修改表空間資料檔案大小為不限制的語句為:
  3009. alter database datafile '/oradata/orcl/demo01.dbf' autoextend on maxsize unlimited;

  3010. 建立表空間資料檔案大小為不限制的語句為:
  3011. create tablespace demo2 datafile '/oradata/orcl/demo201.dbf' size 10M autoextend on maxsize unlimited;

  3012. ----取消已有資料檔案的自動增長方式
  3013. alter database datafile 'i:\oracle\oradata\dmusertbs01.dbf' autoextend off;



  3014. ------------ 根據檔案號和塊號查詢資料庫物件
  3015. SELECT tablespace_name,
  3016.        segment_type,
  3017.        owner,
  3018.        segment_name,
  3019.        partition_name
  3020. FROM dba_extents
  3021. WHERE file_id = &file_id
  3022. AND &block_id BETWEEN block_id AND block_id + blocks - 1
  3023. ;



  3024. SELECT ROWID,
  3025.        dbms_rowid.rowid_object(ROWID) object_id,
  3026.        dbms_rowid.rowid_relative_fno(ROWID) file_id,
  3027.        dbms_rowid.rowid_block_number(ROWID) block_id,
  3028.        d.*
  3029. FROM scott.SALGRADE d
  3030. WHERE dbms_rowid.rowid_block_number(ROWID) = 163
  3031. AND dbms_rowid.rowid_relative_fno(ROWID) = 4;


  3032. SELECT DBMS_ROWID.ROWID_CREATE(1,
  3033.                                (SELECT DATA_OBJECT_ID
  3034.                                   FROM DBA_OBJECTS
  3035.                                  WHERE OBJECT_ID = ROW_WAIT_OBJ#),
  3036.                                ROW_WAIT_FILE#,
  3037.                                ROW_WAIT_BLOCK#,
  3038.                                ROW_WAIT_ROW#),
  3039.        A.ROW_WAIT_OBJ#,
  3040.        A.ROW_WAIT_FILE#,
  3041.        A.ROW_WAIT_BLOCK#,
  3042.        A.ROW_WAIT_ROW#,
  3043.        (SELECT D.OWNER || '.' || D.OBJECT_NAME
  3044.           FROM DBA_OBJECTS D
  3045.          WHERE OBJECT_ID = ROW_WAIT_OBJ#) OBJECT_NAME
  3046.   FROM V$SESSION A
  3047.  WHERE A.ROW_WAIT_OBJ# <> -1;
  3048.  
  3049.  
  3050.  SELECT * FROM SYS.COM$ A WHERE A.ROWID='AAAACJAABAAAARGAAA';






  3051. ---------------------- 刪除主鍵及主鍵索引
  3052.  
  3053. alter table table_name drop primary key cascade drop index;
  3054. alter table table_name drop constraint constraint_name cascade drop index;


  3055. --------------------------------------------------------- awr


  3056. ----- 生成awr
  3057. select * from table(dbms_workload_repository.awr_report_html(3424884828,1,1161,1165));
  3058. @$ORACLE_HOME/rdbms/admin/awrrpt.sql;


  3059. ----檢視


  3060. select * from DBA_HIST_WR_CONTROL;
  3061. select * from DBA_HIST_SNAPSHOT;
  3062. select * from DBA_HIST_ACTIVE_SESS_HISTORY;
  3063. select * from DBA_HIST_ASH_SNAPSHOT;

  3064. select * from DBA_HIST_SEG_STAT;
  3065. select * from DBA_HIST_SQLBIND;
  3066. select * from DBA_HIST_SQLSTAT;
  3067. select * from DBA_HIST_SQLTEXT;
  3068. select * from DBA_HIST_SQL_BIND_METADATA;
  3069. select * from DBA_HIST_SQL_PLAN;



  3070. --------- sql部分
  3071. select &begin_snap || '~' || &end_snap snap_id_range,
  3072.        (SELECT round(sum(db_time) / 1000000 / 60, 2) db_time_m
  3073.           FROM (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time
  3074.                   from dba_hist_sys_time_model a, dba_hist_snapshot b
  3075.                  where a.snap_id = b.snap_id
  3076.                    and a.dbid = b.dbid
  3077.                    and a.instance_number = b.instance_number
  3078.                    and a.stat_name = 'DB time'
  3079.                    and a.snap_id between &begin_snap and &end_snap)
  3080.          where db_time IS NOT NULL) "db_time(m)",
  3081.        round(nvl((sqt.elap / 1000000), to_number(null)), 2) "Elapsed Time (s)",
  3082.        round(nvl((sqt.cput / 1000000), to_number(null)), 2) "CPU Time (s)",
  3083.        round(nvl((sqt.iowait_delta / 1000000), to_number(null)), 2) "User I/O Time (s)",
  3084.        round(nvl((sqt.buffer_gets_delta), to_number(null)), 2) "Buffer Gets",
  3085.        round(nvl((sqt.disk_reads_delta), to_number(null)), 2) "Physical Reads",
  3086.        round(nvl((sqt.rows_processed_delta), to_number(null)), 2) "Rows Processed",
  3087.        round(nvl((sqt.parse_calls_delta), to_number(null)), 2) "Parse Calls",
  3088.        sqt.exec executions,
  3089.        round(decode(sqt.exec,
  3090.                     0,
  3091.                     to_number(null),
  3092.                     (sqt.elap / sqt.exec / 1000000)),
  3093.              2) "Elapsed Time per Exec (s)",
  3094.        round(decode(sqt.exec,
  3095.                     0,
  3096.                     to_number(null),
  3097.                     (sqt.cput / sqt.exec / 1000000)),
  3098.              2) "CPU per Exec (s)",
  3099.        round(decode(sqt.exec,
  3100.                     0,
  3101.                     to_number(null),
  3102.                     (sqt.iowait_delta / sqt.exec / 1000000)),
  3103.              2) "UIO per Exec (s)",
  3104.        round(sqt.cput * 100 / sqt.elap, 2) "%CPU",
  3105.        round(sqt.iowait_delta * 100 / sqt.elap, 2) "%IO",
  3106.        round(sqt.elap * 100 /
  3107.              (SELECT sum(db_time)
  3108.                 FROM (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time
  3109.                         from dba_hist_sys_time_model a, dba_hist_snapshot b
  3110.                        where a.snap_id = b.snap_id
  3111.                          and a.dbid = b.dbid
  3112.                          and a.instance_number = b.instance_number
  3113.                          and a.stat_name = 'DB time'
  3114.                          and a.snap_id between &begin_snap and &end_snap)
  3115.                where db_time IS NOT NULL),
  3116.              2) "elapsed/dbtime",
  3117.        sqt.sql_id,
  3118.        parsing_schema_name,
  3119.        (decode(sqt.module, null, null, sqt.module)) module,
  3120.        nvl((select dbms_lob.substr(st.sql_text, 2000, 1)
  3121.              from dba_hist_sqltext st
  3122.             WHERE st.sql_id = sqt.sql_id
  3123.               and st.dbid = sqt.dbid),
  3124.            (' ** SQL Text Not Available ** ')) sql_text
  3125.   from (select sql_id,
  3126.                a.dbid,
  3127.                a.parsing_schema_name,
  3128.                max(module || '--' || a.action) module,
  3129.                sum(elapsed_time_delta) elap,
  3130.                sum(cpu_time_delta) cput,
  3131.                sum(executions_delta) exec,
  3132.                SUM(a.iowait_delta) iowait_delta,
  3133.                sum(a.buffer_gets_delta) buffer_gets_delta,
  3134.                sum(a.disk_reads_delta) disk_reads_delta,
  3135.                sum(a.rows_processed_delta) rows_processed_delta,
  3136.                sum(a.parse_calls_delta) parse_calls_delta
  3137.           from dba_hist_sqlstat a
  3138.          where &begin_snap < snap_id
  3139.            and snap_id <= &end_snap
  3140.          group by sql_id, parsing_schema_name, a.dbid) sqt
  3141.  order by nvl(sqt.elap, -1) desc, sqt.sql_id
  3142. ;

  3143. --------- 資訊
  3144. select s.snap_date,
  3145.        snap_time_range,
  3146.        t.snap_id + 1 snap_id,
  3147.        decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME",
  3148.        startup_time,
  3149.        to_char(round(s.seconds / 60, 2)) "elapse(min)",
  3150.        round(t.db_time / 1000000 / 60, 2) "DB time(min)",
  3151.        s.redosize redo,
  3152.        round(s.redosize / s.seconds, 2) "redo/s",
  3153.        round(s.redosize / s.transactions, 2) "redo/t",
  3154.        s.logicalreads logical,
  3155.        round(s.logicalreads / s.seconds, 2) "logical/s",
  3156.        round(s.logicalreads / s.transactions, 2) "logical/t",
  3157.        physicalreads physical,
  3158.        round(s.physicalreads / s.seconds, 2) "phy/s",
  3159.        round(s.physicalreads / s.transactions, 2) "phy/t",
  3160.        s.executes execs,
  3161.        round(s.executes / s.seconds, 2) "execs/s",
  3162.        round(s.executes / s.transactions, 2) "execs/t",
  3163.        s.parse,
  3164.        round(s.parse / s.seconds, 2) "parse/s",
  3165.        round(s.parse / s.transactions, 2) "parse/t",
  3166.        s.hardparse,
  3167.        round(s.hardparse / s.seconds, 2) "hardparse/s",
  3168.        round(s.hardparse / s.transactions, 2) "hardparse/t",
  3169.        s.transactions trans,
  3170.        round(s.transactions / s.seconds, 2) "trans/s"
  3171.   from (select curr_redo - last_redo redosize,
  3172.                curr_logicalreads - last_logicalreads logicalreads,
  3173.                curr_physicalreads - last_physicalreads physicalreads,
  3174.                curr_executes - last_executes executes,
  3175.                curr_parse - last_parse parse,
  3176.                curr_hardparse - last_hardparse hardparse,
  3177.                curr_transactions - last_transactions transactions,
  3178.                round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds,
  3179.                to_char(currtime, 'yyyy-mm-dd') snap_date,
  3180.                to_char(currtime, 'hh24:mi') currtime,
  3181.                to_char(lasttime, 'YYYY-MM-DD HH24:MI') || '~' ||
  3182.                to_char(currtime, 'YYYY-MM-DD HH24:MI') snap_time_range,
  3183.                currsnap_id endsnap_id,
  3184.                to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time
  3185.           from (select a.redo last_redo,
  3186.                        a.logicalreads last_logicalreads,
  3187.                        a.physicalreads last_physicalreads,
  3188.                        a.executes last_executes,
  3189.                        a.parse last_parse,
  3190.                        a.hardparse last_hardparse,
  3191.                        a.transactions last_transactions,
  3192.                        lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo,
  3193.                        lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads,
  3194.                        lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads,
  3195.                        lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes,
  3196.                        lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse,
  3197.                        lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse,
  3198.                        lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions,
  3199.                        b.end_interval_time lasttime,
  3200.                        lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime,
  3201.                        lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id,
  3202.                        b.startup_time
  3203.                   from (select snap_id,
  3204.                                dbid,
  3205.                                instance_number,
  3206.                                sum(decode(stat_name, 'redo size', value, 0)) redo,
  3207.                                sum(decode(stat_name,
  3208.                                           'session logical reads',
  3209.                                           value,
  3210.                                           0)) logicalreads,
  3211.                                sum(decode(stat_name,
  3212.                                           'physical reads',
  3213.                                           value,
  3214.                                           0)) physicalreads,
  3215.                                sum(decode(stat_name, 'execute count', value, 0)) executes,
  3216.                                sum(decode(stat_name,
  3217.                                           'parse count (total)',
  3218.                                           value,
  3219.                                           0)) parse,
  3220.                                sum(decode(stat_name,
  3221.                                           'parse count (hard)',
  3222.                                           value,
  3223.                                           0)) hardparse,
  3224.                                sum(decode(stat_name,
  3225.                                           'user rollbacks',
  3226.                                           value,
  3227.                                           'user commits',
  3228.                                           value,
  3229.                                           0)) transactions
  3230.                           from dba_hist_sysstat
  3231.                          where stat_name in
  3232.                                ('redo size',
  3233.                                 'session logical reads',
  3234.                                 'physical reads',
  3235.                                 'execute count',
  3236.                                 'user rollbacks',
  3237.                                 'user commits',
  3238.                                 'parse count (hard)',
  3239.                                 'parse count (total)')
  3240.                          group by snap_id, dbid, instance_number) a,
  3241.                        dba_hist_snapshot b
  3242.                  where a.snap_id = b.snap_id
  3243.                    and a.dbid = b.dbid
  3244.                    and a.instance_number = b.instance_number
  3245.                  order by end_interval_time)) s,
  3246.        (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time,
  3247.                lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id,
  3248.                b.snap_id
  3249.           from dba_hist_sys_time_model a, dba_hist_snapshot b
  3250.          where a.snap_id = b.snap_id
  3251.            and a.dbid = b.dbid
  3252.            and a.instance_number = b.instance_number
  3253.            and a.stat_name = 'DB time') t
  3254.  where s.endsnap_id = t.endsnap_id
  3255.  order by s.snap_date desc, snap_id desc, time asc;


  3256.  ----主機資訊
  3257.  SELECT s.snap_id,
  3258.        DB_NAME,
  3259.        s.dbid,
  3260.        INSTANCE_NAME,
  3261.        s.instance_number,
  3262.        s.startup_time,
  3263.        Version Release,
  3264.        PARALLEL RAC,
  3265.        HOST_NAME,
  3266.        di.platform_name,
  3267.        v.cpus CPUS,
  3268.        v.cores,
  3269.        v.sockets,
  3270.        v.Memory "Memory (GB)"
  3271. FROM DBA_HIST_DATABASE_INSTANCE di,
  3272.        DBA_HIST_SNAPSHOT s,
  3273.        (SELECT snap_id,
  3274.                dbid,
  3275.                instance_number,
  3276.                SUM(CPUs) CPUs,
  3277.                SUM(Cores) Cores,
  3278.                SUM(Sockets) Sockets,
  3279.                SUM(Memory) Memory
  3280.         FROM (SELECT o.snap_id,
  3281.                        o.dbid,
  3282.                        o.instance_number,
  3283.                        decode(o.stat_name, 'NUM_CPUS', o.value) CPUs,
  3284.                        decode(o.stat_name, 'NUM_CPU_CORES', o.value) Cores,
  3285.                        decode(o.stat_name, 'NUM_CPU_SOCKETS', o.value) Sockets,
  3286.                        decode(o.stat_name,
  3287.                               'PHYSICAL_MEMORY_BYTES',
  3288.                               trunc(o.value / 1024 / 1024 / 1024, 2)) Memory
  3289.                 FROM dba_hist_osstat o
  3290.                 WHERE o.stat_name IN
  3291.                        ('NUM_CPUS',
  3292.                         'NUM_CPU_CORES',
  3293.                         'NUM_CPU_SOCKETS',
  3294.                         'PHYSICAL_MEMORY_BYTES'))
  3295.         GROUP BY snap_id,
  3296.                   dbid,
  3297.                   instance_number) v
  3298. WHERE s.instance_number = di.instance_number
  3299. AND s.startup_time = di.startup_time
  3300. AND s.dbid = di.dbid
  3301. AND s.snap_id = v.snap_id
  3302. AND s.dbid = s.dbid
  3303. AND s.instance_number = v.instance_number;




  3304. ------------------------------------------------------------------------------------------ 臨時表
  3305. 基於事務: create global temporary table lhr.cgtt_temp_sw on commit delete rows as select * from dba_objects;
  3306. 建立索引:create index ind_cgtt_object_ID on cgtt_temp_sw(object_ID);

  3307. 基於會話: create global temporary table lhr.cgtt_temp_hh on commit preserve rows as select * from dba_objects;
  3308. 建立索引:需重開會話: create index ind_cgtt_object_ID2 on cgtt_temp_hh(object_ID);


  3309. select * from VW_TEMP_OBJECT_LHR;

  3310. Select se.username,
  3311.         se.sid,
  3312.         su.extents,
  3313.         (su.blocks *
  3314.         to_number((select rtrim(value)
  3315.                      from v$parameter p
  3316.                     WHERE p.NAME = 'db_block_size'))) / 1024 / 1024 as Size_m,
  3317.         tablespace,
  3318.         segtype,
  3319.         (SELECT a.SQL_TEXT
  3320.            FROM v$sql a
  3321.           WHERE a.SQL_ID = su.SQL_ID
  3322.             and rownum = 1) SQL_TEXT
  3323.    from v$sort_usage su, v$session se
  3324.   where su.session_addr = se.saddr
  3325.   order by se.username, se.sid;

  3326. -------------------------------------- catalog庫
  3327. SQL> create tablespace rman_ts datafile '/lhrdata/u01/app/oracle/oradata/orcllinux/rman.dbf' size 1G;
  3328. 表空間已建立。
  3329. SQL> create user rc identified by lhr default tablespace rman_ts quota unlimited on rman_ts;
  3330. 使用者已建立。
  3331. SQL> grant recovery_catalog_owner to rc;---包含了connect的角色許可權
  3332. 授權成功。
  3333. SQL> grant RESOURCE to rc;
  3334. 授權成功。
  3335. SQL> HOST
  3336. [oracle@lhr_linux ~]$ rman catalog rc/lhr
  3337. 恢復管理器: Release 11.2.0.1.0 - Production on 星期四 4月 10 15:08:22 2014
  3338. Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
  3339. 連線到恢復目錄資料庫
  3340. RMAN> create catalog tablespace rman_ts;
  3341. 恢復目錄已建立
  3342. RMAN> connect target /
  3343. 連線到目標資料庫: ORCLLINU (DBID=534927627)
  3344. RMAN> register database;
  3345. 註冊在恢復目錄中的資料庫
  3346. 正在啟動全部恢復目錄的 resync
  3347. 完成全部 resync
  3348. RMAN> exit


  3349. ---------- 使用者profile
  3350. alter user lhr profile default;
  3351. alter system set resource_limit=true;
  3352. alter profile default limit PASSWORD_LIFE_TIME UNLIMITED;
  3353. alter profile default limit FAILED_LOGIN_ATTEMPTS UNLIMITED;


  3354. /oracle/app/oraInventory/ContentsXML/inventory.xml
  3355. oracle執行:$ORACLE_HOME/oui/bin/attachHome.sh


  3356. -------------------------------------------- 解除安裝軟體 GRID
  3357. kill -9 `ps -ef|grep d.bin| grep -v grep | awk '{print $2}'`



  3358. 解除安裝GRID軟體,grid使用者執行:$ORACLE_HOME/deinstall/deinstall
  3359. 解除安裝ORACLE軟體,oracle使用者執行:$ORACLE_HOME/deinstall/deinstall

  3360. 在Linux下手工解除安裝RAC的步驟:
  3361. ① rm -rf /etc/ora*
  3362. ② rm -rf /var/tmp/.oracle
  3363. ③ 修改/etc/inittab刪除以下三行
  3364.     h1:2:respawn:/etc/init.evmd run >/dev/null 2>&1 </dev/null
  3365.     h2:2:respawn:/etc/init.cssd fatal >/dev/null 2>&1 </dev/null
  3366.     h3:2:respawn:/etc/init.crsd run >/dev/null 2>&1 </dev/null
  3367. ④ rm -rf /tmp/*
  3368. ⑤ rm -rf /u01/app/grid/* ---刪除GRID目錄
  3369. ⑥ rm -rf /u01/app/oracle/* --刪除ORACLE目錄
  3370. ⑦ rm -rf /u01/app/oraInventory/*
  3371. ⑧ 清除OCR、Voting及資料庫使用過的磁碟
  3372.     dd if=/dev/zero of=/dev/raw/raw1 bs=104857600 count=1
  3373.     dd if=/dev/zero of=/dev/raw/raw2 bs=104857600 count=1
  3374. 最後,重啟2個節點*/

  3375. -------重建路徑
  3376. mkdir -p /u01/app/oracle
  3377. mkdir -p /u01/app/grid
  3378. mkdir -p /u01/app/12.1.0/grid
  3379. mkdir -p /u01/app/oracle/product/12.1.0/dbhome_1
  3380. chown -R grid:oinstall /u01/app/grid
  3381. chown -R grid:oinstall /u01/app/12.1.0
  3382. chown -R oracle:oinstall /u01/app/oracle
  3383. chmod -R 775 /u01

  3384. mkdir -p /u01/app/oraInventory
  3385. chown -R grid:oinstall /u01/app/oraInventory
  3386. chmod -R 775 /u01/app/oraInventory


  3387. 在Windows下手工解除安裝RAC的步驟:
  3388. ① 開始->設定->控制皮膚->管理工具->服務,或執行services.msc開啟服務,停止所有Oracle服務
  3389. ② 刪除Oracle和GRID的安裝目錄
  3390. ③ 刪除C:\Program Files\Oracle目錄
  3391. ④ 刪除C:\windows\temp和C:\temp以及C:\Users\Administrator\Oracle下的檔案
  3392. ⑤ 執行regedit,開啟登錄檔編輯器,選擇HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE,刪除該項
  3393. ⑥ 執行regedit,開啟登錄檔編輯器,選擇HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services,滾動這個列表,刪除所有Oracle開頭的項
  3394. ⑦ 執行regedit,開啟登錄檔編輯器,選擇HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application,刪除所有Oracle入口
  3395. ⑧ 開始->設定->控制皮膚->系統->高階->環境變數,刪除環境變數CLASSPATH和PATH中有關Oracle的設定
  3396. ⑨ 從桌面上、STARTUP(啟動)組、程式選單中,刪除所有有關Oracle的組和圖示
  3397. ⑩ 重新啟動計算機,重起後才能完全刪除Oracle所在目錄
  3398. 若個別檔案不能刪除,則說明該檔案與某個Windows服務相關聯,可以先把相關聯的服務停止後再刪除。



  3399. -- 加入常用命令
  3400. vi /etc/profile
  3401. export GRID_HOME=/u01/app/12.1.0/grid
  3402. export PATH=$PATH:$GRID_HOME/bin


  3403. ---------------- 重新執行root.sh
  3404. ---kill -9 `ps -ef|grep d.bin| grep -v grep | awk '{print $2}'`
  3405. ---$ORACLE_HOME 為 GRID_HOME的路徑,執行之前最好先手動把資料庫資源關閉
  3406. 日誌地址:$ORACLE_HOME/cfgtoollogs/crsconfig/
  3407. 重置的日誌檔案:hadelete.log
  3408. root.sh指令碼日誌:rootcrs_rac2.log


  3409. --------------① 指令碼方式
  3410. ---執行失敗,重新執行root.sh指令碼
  3411. $ORACLE_HOME/crs/install/crsconfig_params
  3412. $ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose
  3413. --$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose -lastnode -keepdg
  3414. dd if=/dev/zero of=/dev/rhdiskN bs=1024k count=1024
  3415. lquerypv -h /dev/rhdisk5
  3416. $ORACLE_HOME/root.sh


  3417. $ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose
  3418. --$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose -lastnode -keepdg
  3419. $ORACLE_HOME/root.sh

  3420. ---$ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose執行完成之後需要刪除如下的檔案
  3421. ls -l $ORACLE_BASE/Clusterware/ckptGridHA* */
  3422. find $ORACLE_HOME/gpnp/* -type f */
  3423. find $ORACLE_HOME/gpnp/* -type f -exec rm -rf {} \; */




  3424. --------------② 介面方式
  3425. ---------------刪除兩節點crsconfig_params中的DATA1和磁碟 介面方式
  3426. $ORACLE_HOME/crs/install/crsconfig_params
  3427. ASM_DISK_GROUP=DATA1
  3428. ASM_DISKS=/dev/rhdisk5
  3429. --root
  3430. $ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose
  3431. -- GRID
  3432. export DISPLAY=22.188.216.132:0.0
  3433. $ORACLE_HOME/crs/config/config.sh





  3434. CRS-4124: Oracle High Availability Services startup failed. -- 報錯
  3435. CRS-4000: Command Start failed, or completed with errors.
  3436. ohasd failed to start: Inappropriate ioctl for device
  3437. ohasd failed to start: Inappropriate ioctl for device at /u01/app/11.2.0/grid/crs/install/roothas.pl line 296.
  3438. /bin/dd if=/var/tmp/.oracle/npohasd of=/dev/null bs=1024 count=1


  3439. /nfs/software/db/install/clone/sh/11g/grid/runcluvfy.sh stage -pre crsinst -n ZFLHRDB1,ZFLHRDB2 -verbose -fixup

  3440. $ORACLE_HOME/bin/cluvfy stage -pre crsinst -n all -verbose -fixup


  3441. find . -name runcluvfy.sh


  3442. ---GRID_HOME許可權修復
  3443. 方法1:11gR2可以deconfig crs的配置,然後重新跑root.sh即可。重新跑root.sh指令碼並不影響資料庫,所以無需擔心(個人推薦的一種方式).
  3444. $ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose
  3445. $ORACLE_HOME/root.sh

  3446. 方法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可以執行如下兩條命令來實現同樣的效果
  3447. <GRID_HOME>/crs/install/rootcrs.pl -unlock
  3448. <GRID_HOME>/crs/install/rootcrs.pl -patch

  3449. For 11.2:
  3450. For clustered Grid Infrastructure, as root user
  3451. # cd <GRID_HOME>/crs/install/
  3452. # ./rootcrs.pl -init
  3453. For Standalone Grid Infrastructure, as root user
  3454. # cd <GRID_HOME>/crs/install/
  3455. # ./roothas.pl  -init

  3456. For 12c:
  3457. For clustered Grid Infrastructure, as root user
  3458. # cd <GRID_HOME>/crs/install/
  3459. # ./rootcrs.sh -init
  3460. For Standalone Grid Infrastructure, as root user
  3461. # cd <GRID_HOME>/crs/install/
  3462. # ./roothas.sh -init









  3463. ---檢視psu
  3464. /nfs/software/db/install/chk/chkora.sh
  3465. opatch lsinventory -bugs_fixed | grep 'PSU'
  3466. opatch lsinv
  3467. [ZFCASSDB1:grid]:/home/grid>opatch lspatches
  3468. 13343438;Database Patch Set Update : 11.2.0.3.1 (13343438)
  3469. 13348650;Grid Infrastructure Patch Set Update : 11.2.0.3.1 (13348650)
  3470. [ZFCASSDB1:grid]:/home/grid>

  3471. col action_time for a30
  3472. col action for a10
  3473. col namespace for a10
  3474. col version for a10
  3475. col bundle_series for a10
  3476. col comments for a30

  3477. SELECT to_char(action_time, 'YYYY-MM-DD HH24:MI:SS') action_time,
  3478.        action,
  3479.        namespace,
  3480.        version,
  3481.        id,
  3482.        bundle_series,
  3483.        comments
  3484.   FROM dba_registry_history D;

  3485. select action,comments from registry$history;


  3486. ---grid和oracle分別回滾
  3487. $ORACLE_HOME/OPatch/opatch rollback -local -id 13348650 -oh /oracle/app/oracle/product/11.2.0/db



  3488. ----------------------------- OCR備份
  3489. --邏輯備份恢復
  3490. ocrconfig -export /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/export_asm.bak
  3491. ocrconfig -import /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/export_asm_lhr.bak
  3492. crsctl stop crs
  3493. crsctl start crs -excl -nocrs
  3494. ocrconfig -import /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/export_asm_lhr.bak
  3495. crsctl start crs



  3496. --物理備份恢復
  3497. ocrconfig -manualbackup
  3498. ocrconfig -showbackup

  3499. cluvfy comp ocr -n all -verbose
  3500. cluvfy comp olr -verbose


  3501. crsctl stop crs -f
  3502. crsctl start crs -excl -nocrs
  3503. crsctl stop resource ora.crsd -init
  3504. ocrconfig -restore /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/backup_20160701_152358.ocr
  3505. crsctl stop has -f
  3506. crsctl start crs


  3507. crsctl query css votedisk

  3508.  
  3509. --------dd備份恢復OCR 注:11g不推薦使用dd來進行備份恢復,盤頭一般是前4K
  3510. --備份表決磁碟:
  3511. dd if=/dev/raw/raw3 of=/tmp/votedisk_lhr.bak bs=1024k count=4
  3512. --恢復表決磁碟:
  3513. dd if=/tmp/votedisk_lhr.bak of=/dev/raw/raw3 bs=1024k count=4


  3514. ----------kfed修復磁碟頭
  3515. dd if=/dev/rhdisk2 of=/asm_rhdisk2_dd.bak bs=1024 count=4
  3516. dd if=/dev/zero of=/dev/rhdisk2 bs=1024 count=4
  3517. kfed repair /dev/rhdisk2

  3518. ----md_backup修復磁碟頭
  3519. asmcmd md_backup /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/asm_md_backup.bak
  3520. asmcmd md_restore /oracle/app/11.2.0/grid/cdata/ZFTPCCDB-crs/asm_md_backup.bak


  3521. dd if=/dev/rhdisk2 of=/asm_rhdisk2_dd.bak bs=1024k count=4
  3522. dd if=/dev/zero of=/dev/rhdisk2 bs=1024k count=4
  3523. crsctl stop has -f
  3524. crsctl start has
  3525. ASMCMD [+] > startup force nomount;
  3526. ASMCMD [+] > md_restore /asm_rhdisk2_dd.bak


  3527. ASMCMD [+] > md_backup /rman/asm_md.bak
  3528. dd if=/dev/zero of=/dev/rhdisk2 bs=1024 count=4
  3529. crsctl stop has -f
  3530. crsctl start has
  3531. ASMCMD [+] > startup force nomount;
  3532. ASMCMD [+] > md_restore /rman/asm_md.bak


  3533. -------- OLR的備份恢復
  3534. <GI_HOME>/bin/ocrconfig -local -manualbackup
  3535. <GI_HOME>/bin/ocrconfig -local -showbackup

  3536. ps -ef| grep ohasd.bin
  3537. <GI_HOME>/bin/crsctl stop crs -f <========= for GI Cluster
  3538. <GI_HOME>/bin/crsctl stop has <========= for GI Standalone
  3539. <GI_HOME>/bin/ocrconfig -local -restore <olr-backup>
  3540. <GI_HOME>/bin/crsctl start crs <========= for GI Cluster
  3541. <GI_HOME>/bin/crsctl start has <========= for GI Standalone, this must be done as grid user.





  3542. vi crsstat_lhr.sh
  3543. awk 'BEGIN {printf "%-26s %-26s %-10s %-10s %-10s \n","Name ","Type ","Target ","State ","Host "; printf "%-30s %-26s %-10s %-10s %-10s\n","----------------------------------------","--------------------------","----------", "---------","----------";}'
  3544. 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;}'



  3545. -------------------------------------------------------------- OEM
  3546.  --重建:
  3547.  emca -config dbcontrol db -repos recreate
  3548.  emca -config dbcontrol db -repos recreate -cluster


  3549. http://192.168.59.130:1158/em/
  3550. https://192.168.59.128:1158/em/

  3551. 日誌:
  3552. $ORACLE_HOME/$hostname_$oracle_sid/sysman/log

  3553. 4.安裝過程中出現問題的時候認真檢視日誌,安裝日誌路徑:$ORACLE_HOME/cfgtoollogs\emca\
  3554. 5.OEM執行日誌:$ORACLE_HOME/$hostname_$oracle_sid/sysman/log
  3555. 6.建議重新建立,在oracle使用者下:
  3556. 單機: emca -config dbcontrol db -repos recreate
  3557. 叢集: emca -config dbcontrol db -repos recreate -cluster
  3558. 若是叢集環境,則在建立之前先在grid使用者下執行如下程式碼:
  3559. -----sqlplus / as sysasm ASM例項 GRID 使用者下執行
  3560. SYS@+ASM1> create user asmsnmp identified by xxx;
  3561. SYS@+ASM1> grant sysdba to asmsnmp;
  3562. SYS@+ASM1> alter user asmsnmp identified by xxx;
  3563. SYS@+ASM1> alter system set remote_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ZFZHLHRDB-scan)(PORT=1521))))' sid='*';
  3564.     SYS@+ASM1> alter system register;
  3565. 7.啟動和關閉維護命令,oracle使用者下:export ORACLE_UNQNAME=$DB_UNIQUE_NAME ,資料庫唯一名,程式:ps -ef| grep em
  3566.     啟動: emctl stop dbconsole
  3567. 關閉: emctl stop dbconsole
  3568. 執行狀態:emctl status dbconsole


  3569. -------------------------------------------- dbca 靜默建庫 windows 和 linux 命令一樣

  3570. ------歸檔
  3571. vi $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc

  3572. 修改為:<archiveLogMode>true</archiveLogMode>

  3573. [oracle@rhel6_lhr ~]$ strings $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc | grep -i arch
  3574.          <archiveLogMode>false</archiveLogMode>





  3575. ----靜默安裝資料庫日誌路徑:
  3576. 11g:$ORACLE_BASE/cfgtoollogs/dbca
  3577. 10g:$ORACLE_HOME/cfgtoollogs/dbca


  3578. ---dbca -silent整理 \ 後不能包含空格
  3579. dbca -silent -deleteDatabase -sourceDB mydb
  3580. dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
  3581. -gdbname mydb -sid mydb \
  3582. -sysPassword oracle -systemPassword oracle \
  3583. -datafileDestination 'DATA/' -recoveryAreaDestination 'DATA/' \
  3584. -redoLogFileSize 50 \
  3585. -storageType ASM -asmsnmpPassword oracle -diskGroupName 'DATA' \
  3586. -characterset AL32UTF8 -nationalCharacterSet AL16UTF16 \
  3587. -sampleSchema true \
  3588. -automaticMemoryManagement true -totalMemory 2048 \
  3589. -databaseType OLTP \
  3590. -emConfiguration NONE \
  3591. -nodeinfo ZFZHLHRDB1,ZFZHLHRDB2

  3592. dbca -silent -deleteDatabase -sourceDB mydb
  3593. dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
  3594. -gdbname mydb -sid mydb \
  3595. -sysPassword oracle -systemPassword oracle \
  3596. -datafileDestination '/u05/app/oracle' -recoveryAreaDestination '/u05/app/oracle' \
  3597. -storageType FS \
  3598. -characterset AL32UTF8 -nationalCharacterSet AL16UTF16 \
  3599. -sampleSchema false \
  3600. -memoryPercentage 10 \
  3601. -databaseType OLTP \
  3602. -emConfiguration NONE


  3603. ---10g
  3604. dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
  3605. -gdbname mydg -sid mydg \
  3606. -sysPassword lhr -systemPassword lhr \
  3607. -datafileDestination '/cds/oradata' -recoveryAreaDestination '/cds/oradata' \
  3608. -storageType FS \
  3609. -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \
  3610. -sampleSchema true \
  3611. -memoryPercentage 20 \
  3612. -databaseType OLTP \
  3613. -emConfiguration NONE


  3614. --12C
  3615. dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname oradb.example.com -sid oradb -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration NONE
  3616. dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
  3617. -gdbname lhrdb -sid lhrdb \
  3618. -createAsContainerDatabase false \
  3619. -sysPassword lhr -systemPassword lhr -serviceUserPassword lhr \
  3620. -datafileDestination '/u01/app/oracle' -recoveryAreaDestination '/u01/app/oracle' \
  3621. -storageType FS \
  3622. -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \
  3623. -sampleSchema true \
  3624. -memoryPercentage 30 \
  3625. -databaseType OLTP \
  3626. -emConfiguration NONE

  3627. --12C rac
  3628. dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
  3629. -gdbname lhrrac -sid lhrrac \
  3630. -createAsContainerDatabase false \
  3631. -sysPassword lhr -systemPassword lhr -serviceUserPassword lhr \
  3632. -datafileDestination 'DATA/' -recoveryAreaDestination 'FRA/' \
  3633. -storageType ASM -asmsnmpPassword oracle -diskGroupName 'DATA' \
  3634. -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \
  3635. -sampleSchema true \
  3636. -memoryPercentage 30 \
  3637. -databaseType OLTP \
  3638. -emConfiguration NONE \
  3639. -nodeinfo raclhr-12cR1-N1,raclhr-12cR1-N2



  3640. --11g
  3641. 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
  3642. dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ora11g -sid ora11g -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata -characterSet AL32UTF8

  3643. ----ASM 儲存 單例項
  3644. 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


  3645. ---預設
  3646. --- 閃回恢復區 建立
  3647. --storageType FS
  3648. --sampleSchema 預設建立
  3649. --em 預設不建立



  3650. --10g
  3651. 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




  3652. ----------------根據模板檔案進行安裝
  3653. dbca -silent -responseFile $ORACLE_HOME/assistants/dbca/dbca.rsp
  3654. dbca -silent -cloneTemplate -responseFile $ORACLE_HOME/assistants/dbca/dbca.rsp -gdbName orcltest -sid orcltest -datafileDestination /u01/app/oracle/oradata



  3655. -------------------- 根據資料庫生成不帶資料檔案的模板
  3656. dbca -silent -createTemplateFromDB -sourceDB 192.168.59.130:1521:ora10g -templateName dbtemplate_ora10g2_lhr -sysDBAUserName sys -sysDBAPassword lhr

  3657. -------------------- 根據資料庫生成帶資料檔案的模板
  3658. dbca -silent -createCloneTemplate -sourceDB orcltest -sysDBAUserName lhr -sysDBAPassword lhr -templateName dbtemplate_orcltest_lhr -datafileJarLocation
  3659. -------------------- 利用帶資料檔案的模板生成克隆資料庫
  3660. 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

  3661. -------------------- 利用不帶資料檔案的模板生成新的資料庫 慢,不推薦
  3662. ----dbca -silent -createDatabase -templateName New_Database.dbt -gdbname test33 -sid test33 -datafileDestination /u01/app/oracle/oradata -responseFile NO_VALUE -characterset ZHS16GBK



  3663. ---------------- 刪除資料庫
  3664. dbca -silent -deleteDatabase -sourceDB orclbb -sysDBAUserName sys -sysDBAPassword lhr



  3665. ------------- linux、AIX下 rac 資料庫的建立
  3666. 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

  3667. ----windows下建立rac庫,注意引數 diskGroupName 為 DATA ,不能帶有引號
  3668. 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


  3669. ----- 或者:
  3670. [oracle@node1 dbca]$ more $ORACLE_HOME/assistants/dbca/dbca_rac.rsp
  3671. [GENERAL]
  3672. RESPONSEFILE_VERSION = "11.2.0"
  3673. OPERATION_TYPE = "createDatabase"
  3674. [CREATEDATABASE]
  3675. GDBNAME = "myrac"
  3676. SID = "myrac"
  3677. NODELIST=node1,node2
  3678. TEMPLATENAME = "General_Purpose.dbc"
  3679. SYSPASSWORD = "lhr"
  3680. SYSTEMPASSWORD = "lhr"
  3681. SYSMANPASSWORD = "lhr"
  3682. DBSNMPPASSWORD = "lhr"
  3683. STORAGETYPE=ASM
  3684. DISKGROUPNAME=DATA
  3685. ASMSNMP_PASSWORD="lhr"
  3686. RECOVERYGROUPNAME=ARCH
  3687. CHARACTERSET = "ZHS16GBK"
  3688. NATIONALCHARACTERSET= "UTF8"
  3689. [oracle@node1 dbca]$ dbca -silent -responseFile $ORACLE_HOME/assistants/dbca/dbca_rac.rsp



  3690. set line 9999
  3691. col HOST_NAME format a10 
  3692. select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS,ACTIVE_STATE,INSTANCE_ROLE,DATABASE_STATUS from gv$INSTANCE;
  3693. select INST_ID,name , open_mode, log_mode,force_logging from gv$database;


  3694. ------------------------------------------------------------------- 靜默安裝
  3695. ---------- 單例項資料庫安裝
  3696. vi /tmp/database/response/db_install.rsp
  3697. ORACLE_HOSTNAME=192.168.59.129
  3698. UNIX_GROUP_NAME=oinstall
  3699. INVENTORY_LOCATION=/u02/app/oracle/oraInventory
  3700. SELECTED_LANGUAGES=en,zh_CN
  3701. ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1
  3702. ORACLE_BASE=/u02/app/oracle
  3703. oracle.install.db.InstallEdition=EE
  3704. oracle.install.db.EEOptionsSelection=false
  3705. oracle.install.db.DBA_GROUP=dba
  3706. oracle.install.db.OPER_GROUP=oper
  3707. oracle.install.db.isRACOneInstall=false
  3708. oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
  3709. SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
  3710. oracle.installer.autoupdates.option=SKIP_UPDATES

  3711. ----軟體安裝
  3712. ./runInstaller -silent -noconfig -responseFile /tmp/database/response/db_install.rsp -ignoreSysPrereqs -ignorePrereq


  3713. ----靜默建立asm例項
  3714. /u01/app/11.2.0/grid/bin/asmca -silent -configureASM -sysAsmPassword lhr -asmsnmpPassword lhr -diskGroupName OCR -diskList /dev/rhdisk20 -redundancy EXTERNAL



  3715. ----監聽配置
  3716. $ORACLE_HOME/bin/netca /silent /responsefile /u01/database/netca.rsp
  3717. netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp

  3718. $ORACLE_HOME/bin/netca /silent /responsefile /u01/database/netca.rsp
  3719. netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp
  3720. netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp -instype custom -listener LISTENER_LHR
  3721. crsctl delete resource ora.LISTENER.lsnr -f
  3722. rm $ORACLE_HOME/network/admin/listener.ora



  3723. ---------------------------------- 單例項grid安裝
  3724. /softtmp/grid/runInstaller -silent -force -noconfig -IgnoreSysPreReqs -ignorePrereq -showProgress \
  3725. ORACLE_HOSTNAME=ZFFR4CB2101 \
  3726. INVENTORY_LOCATION=/u01/app/oraInventory \
  3727. SELECTED_LANGUAGES=en \
  3728. oracle.install.option=CRS_SWONLY \
  3729. ORACLE_BASE=/u01/app/grid \
  3730. ORACLE_HOME=/u01/app/11.2.0/grid \
  3731. oracle.install.asm.OSDBA=asmdba \
  3732. oracle.install.asm.OSOPER=asmoper \
  3733. oracle.install.asm.OSASM=asmadmin \
  3734. oracle.install.crs.config.storageOption=ASM_STORAGE \
  3735. oracle.install.crs.config.sharedFileSystemStorage.votingDiskRedundancy=EXTERNAL \
  3736. oracle.install.crs.config.sharedFileSystemStorage.ocrRedundancy=EXTERNAL \
  3737. oracle.install.crs.config.useIPMI=false \
  3738. oracle.install.asm.SYSASMPassword=lhr \
  3739. oracle.install.asm.diskGroup.name=OCR \
  3740. oracle.install.asm.diskGroup.redundancy=EXTERNAL \
  3741. oracle.install.asm.diskGroup.disks=/dev/rhdisk20 \
  3742. oracle.install.asm.monitorPassword=lhr \
  3743. oracle.installer.autoupdates.option=SKIP_UPDATES


  3744. ---------------------------------- 單例項db安裝
  3745. /softtmp/database/runInstaller -silent -force -noconfig -IgnoreSysPreReqs -ignorePrereq -showProgress \
  3746. oracle.install.option=INSTALL_DB_SWONLY \
  3747. DECLINE_SECURITY_UPDATES=true \
  3748. UNIX_GROUP_NAME=oinstall \
  3749. INVENTORY_LOCATION=/u01/app/oraInventory \
  3750. SELECTED_LANGUAGES=en \
  3751. ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 \
  3752. ORACLE_BASE=/u01/app/oracle \
  3753. oracle.install.db.InstallEdition=EE \
  3754. oracle.install.db.isCustomInstall=false \
  3755. oracle.install.db.DBA_GROUP=dba \
  3756. oracle.install.db.OPER_GROUP=dba \
  3757. oracle.install.db.isRACOneInstall=false \
  3758. oracle.install.db.config.starterdb.type=GENERAL_PURPOSE \
  3759. SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
  3760. oracle.installer.autoupdates.option=SKIP_UPDATES


  3761. ---------------------------------- rac grid安裝
  3762. ./runInstaller -silent -force -noconfig -IgnoreSysPreReqs -ignorePrereq -showProgress \
  3763. INVENTORY_LOCATION=/u01/app/oraInventory \
  3764. SELECTED_LANGUAGES=en \
  3765. ORACLE_BASE=/u01/app/grid \
  3766. ORACLE_HOME=/u01/app/11.2.0/grid \
  3767. oracle.install.asm.OSDBA=asmdba \
  3768. oracle.install.asm.OSOPER=asmoper \
  3769. oracle.install.asm.OSASM=asmadmin \
  3770. oracle.install.crs.config.storageOption=ASM_STORAGE \
  3771. oracle.install.crs.config.sharedFileSystemStorage.votingDiskRedundancy=EXTERNAL \
  3772. oracle.install.crs.config.sharedFileSystemStorage.ocrRedundancy=EXTERNAL \
  3773. oracle.install.crs.config.useIPMI=false \
  3774. oracle.install.asm.diskGroup.name=OCR \
  3775. oracle.install.asm.diskGroup.redundancy=EXTERNAL \
  3776. oracle.installer.autoupdates.option=SKIP_UPDATES \
  3777. oracle.install.crs.config.gpnp.scanPort=1521 \
  3778. oracle.install.crs.config.gpnp.configureGNS=false \
  3779. oracle.install.option=CRS_CONFIG \
  3780. oracle.install.asm.SYSASMPassword=lhr \
  3781. oracle.install.asm.monitorPassword=lhr \
  3782. oracle.install.asm.diskGroup.diskDiscoveryString=/dev/rhdisk* \
  3783. oracle.install.asm.diskGroup.disks=/dev/rhdisk10 \
  3784. oracle.install.crs.config.gpnp.scanName=ZFFR4CB2101-scan \
  3785. oracle.install.crs.config.clusterName=ZFFR4CB-cluster \
  3786. oracle.install.crs.config.autoConfigureClusterNodeVIP=false \
  3787. oracle.install.crs.config.clusterNodes=ZFFR4CB2101:ZFFR4CB2101-vip,ZFFR4CB1101:ZFFR4CB1101-vip \
  3788. oracle.install.crs.config.networkInterfaceList=en0:22.188.187.0:1,en1:222.188.187.0:2 \
  3789. ORACLE_HOSTNAME=ZFFR4CB2101


  3790. ---------------------------------- rac db安裝
  3791. ./runInstaller -silent -force -noconfig -IgnoreSysPreReqs -ignorePrereq -showProgress \
  3792. oracle.install.option=INSTALL_DB_SWONLY \
  3793. DECLINE_SECURITY_UPDATES=true \
  3794. UNIX_GROUP_NAME=oinstall \
  3795. INVENTORY_LOCATION=/u01/app/oraInventory \
  3796. SELECTED_LANGUAGES=en \
  3797. oracle.install.db.InstallEdition=EE \
  3798. oracle.install.db.isCustomInstall=false \
  3799. oracle.install.db.EEOptionsSelection=false \
  3800. oracle.install.db.DBA_GROUP=dba \
  3801. oracle.install.db.OPER_GROUP=asmoper \
  3802. oracle.install.db.isRACOneInstall=false \
  3803. oracle.install.db.config.starterdb.type=GENERAL_PURPOSE \
  3804. SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
  3805. oracle.installer.autoupdates.option=SKIP_UPDATES \
  3806. ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 \
  3807. ORACLE_BASE=/u01/app/oracle \
  3808. ORACLE_HOSTNAME=ZFFR4CB2101 \
  3809. oracle.install.db.CLUSTER_NODES=zffr4cb2101,zffr4cb1101 \
  3810. oracle.install.db.isRACOneInstall=false











  3811. -------自動啟動 The Oracle system identifier(SID) "orcltest" already exists. Specify another SID.
  3812. vi /etc/oratab


  3813. ---------啟動

  3814.   1)    lsnrctl start     (啟動監聽) -------監聽停止: lsnrctl stop,lsnrctl是listener-control 監聽器的縮寫,檢視監聽的狀態(lsnrctl status)
  3815.    
  3816.   2)    net start OracleServiceORCL (COCL 我的SID,可以小寫,這是開啟資料庫例項)
  3817. 或者用 oradim –startup –sid orcl -----net stop OracleServiceORACLE




  3818. /********************************************************************************
  3819. *    SQL Scripts Name:    
  3820. *    SQL Scripts Desc:    
  3821. *    Author:    
  3822. *    Date:    
  3823. *    Inputs:    
  3824. *    Outputs:    
  3825. *    Return:    
  3826. *    History:    
  3827. ********************************************************************************/



  3828. --------------- 修改dbid和dbname
  3829. 原: orcltest
  3830. 修改後:DBID: 4270446895 Database Name: ORA11G
  3831. create pfile from spfile;
  3832. shutdown immediate;
  3833. startup mount;
  3834. nid target=sys/lhr dbname=ORA11G
  3835. cp initorcltest.ora initORA11G.ora

  3836. shutdown immediate;
  3837. startup open read only;
  3838. @chang_dbid_lhr.sql
  3839. create pfile from spfile;
  3840. shutdown immediate;



  3841. ------------AUTHID CURRENT_USER
  3842. CREATE OR REPLACE PROCEDURE PRO_XXXX_LHR(p_flag in NUMBER DEFAULT 1,
  3843.                                                   p_result out varchar2)
  3844.   AUTHID CURRENT_USER AS

  3845.   begin
  3846.   


  3847. end ;







  3848. --------- oracle 使用者 解壓縮

  3849. gunzip -c 10201_database_linux_x86_64.cpio.gz > /tmp/10201_database_linux_x86_64.cpio
  3850. cpio -idmv < 10201_database_linux_x86_64.cpio


  3851. unzip p6810189_10204_Linux-x86-64.zip -d /tmp

  3852. 壓縮當前的資料夾 zip -r ./xahot.zip ./* -r表示遞迴
  3853. zip [引數] [打包後的檔名] [打包的目錄路徑]*/

  3854. Linux下*.tar.gz檔案解壓縮命令


  3855. 1.壓縮命令:

  3856.   命令格式:tar -zcvf 壓縮檔名.tar.gz 被壓縮檔名

  3857.       可先切換到當前目錄下。壓縮檔名和被壓縮檔名都可加入路徑。
  3858.  

  3859. 2.解壓縮命令:

  3860.   命令格式:tar -zxvf 壓縮檔名.tar.gz

  3861.   解壓縮後的檔案只能放在當前的目錄。


  3862. Aix下*.tar.gz檔案解壓縮命令
  3863. gunzip -c gdul3.5.0.1.tar.gz | tar -xvf -



  3864. ---------- 重建scott使用者

  3865. sqlplus / as sysdba

  3866. SQL>@$ORACLE_HOME/rdbms/admin/utlsampl.sql




  3867. -------------SQLNET跟蹤tnsping過程 sqlnet.ora中配置

  3868. Trace_level_client=16
  3869. Trace_directory_client=D:\Program files\app\oracle\product\11.2.0.1\dbhome_1\NETWORK\ADMIN
  3870. Trace_unique_client=on
  3871. Trace_timestamp_client=on
  3872. Diag_adr_enabled=off
  3873. tnsping.trace_directory=D:\Program files\app\oracle\product\11.2.0.1\dbhome_1\NETWORK\ADMIN
  3874. tnsping.trace_level=support



  3875. ----------後設資料獲取
  3876. SELECT to_char(DBMS_METADATA.GET_DDL('TABLESPACE', a.tablespace_name))
  3877.   FROM DBA_TABLESPACES a
  3878.  where a.TABLESPACE_NAME = 'TS_LHR';


  3879. SELECT TO_CHAR(DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')) DDL_SQL FROM DUAL;

  3880. SELECT ((DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'LHRSYS'))) FROM DUAL
  3881. UNION ALL
  3882. SELECT ((DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'LHRSYS'))) FROM DUAL
  3883. UNION ALL
  3884. SELECT ((DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'LHRSYS'))) FROM DUAL;


  3885. --------------linux 跟蹤sqlplus程式
  3886. strace -o /tmp/output.txt -T -tt -e trace=all sqlplus / as sysdba

  3887. [root@rhel6_lhr ~]# strace -t -p 4545



  3888. ------------- Unix 跟蹤sqlplus程式
  3889. truss -dfaie -o /tmp/sched_trace.out.02271 sqlplus '/as sysdba'


  3890. -----aix 修改系統時間
  3891. linux下用date -s "20131215 09:02:25"把時間設為2013年12月15日9點2分25秒。
  3892. 而aix呢?它不認-s這個引數:
  3893. date -n mmddHHMMYY,mm表示月分,dd表示日期,HH表示小時,MM表示分鐘,YY表示年份。
  3894. 如:date -n 1215090213表示把當前時間設為2013年12月15日9點2分,秒數無法修改。


  3895. -----自動同步linux時間
  3896. 1、下載ntpdate
  3897. 注:有些版本是沒有自帶ntpdate,因此需要下載
  3898. # yum install -y ntpdate

  3899. 2、調整時區為上海,也就是北京時間+8區
  3900. 注:想改其他時區也可以去看看/usr/share/zoneinfo目錄
  3901. # cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
  3902. # yes | cp -f /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

  3903. 3、使用NTP來同步時間
  3904. # ntpdate us.pool.ntp.org

  3905. 4、定時同步時間(每隔10分鐘同步時鐘)
  3906. # crontab -l >/tmp/crontab.bak
  3907. # echo "*/10 * * * * /usr/sbin/ntpdate us.pool.ntp.org | logger -t NTP" >> /tmp/crontab.bak
  3908. # crontab /tmp/crontab.bak




  3909. -----筆記本開啟:wlan
  3910. netsh wlan set hostednetwork mode=allow ssid=lhr-minPC key=lihuarong
  3911. netsh wlan start hostednetwork
  3912. netsh wlan show hostednetwork


  3913. ------------ 福昕pdf
  3914. --右轉
  3915. crtl+shift++




  3916. ------ Quote (q) 語法

  3917. select q''
  3918.   from employees;

  3919. select q'\\'
  3920.   from employees;


  3921. select q'\ \'
  3922.   from employees;




  3923. -------------------------------------- OS 型別
  3924. -----臨時修改語言環境:
  3925. AIX:
  3926. export LANG=en_US
  3927. export LANG=zh_CN
  3928. Linux:
  3929. export LANG=en_US.UTF-8
  3930. export LANG=zh_CN.UTF-8


  3931. 可用語言環境:
  3932. locale -a | grep zh_CN

  3933. --------- Linux
  3934. cpu : cat /proc/cpuinfo|grep name|cut -f2 -d:|uniq -c
  3935. memory :cat /proc/meminfo

  3936. os version : lsb_release -a

  3937. os hostname hostname

  3938. ------aix


  3939.  
  3940. -------------- AIX 檢視CPU個數
  3941.  1. smtctl
  3942.  2. bindprocessor -q
  3943.  3. prtconf
  3944.  4.lsdev
  3945.  5.vmstat
  3946.  

  3947. racle@DNSCDBS05:/home/oracle>hostname
  3948. DNSCDBS05
  3949. oracle@DNSCDBS05:/home/oracle>oslevel -r
  3950. 7100-01
  3951. oracle@DNSCDBS05:/home/oracle>uname -vr
  3952. 1 7
  3953. oracle@DNSCDBS05:/home/oracle>uname -s
  3954. AIX
  3955. oracle@DNSCDBS05:/home/oracle>uname -a
  3956. AIX DNSCDBS05 1 7 00F813B44C00
  3957. oracle@DNSCDBS05:/home/oracle>uname -v
  3958. 7
  3959. oracle@DNSCDBS05:/home/oracle>pmcycles -m
  3960. CPU 0 runs at 3024 MHz
  3961. CPU 1 runs at 3024 MHz
  3962. CPU 2 runs at 3024 MHz
  3963. CPU 3 runs at 3024 MHz
  3964. CPU 4 runs at 3024 MHz
  3965. CPU 5 runs at 3024 MHz
  3966. CPU 6 runs at 3024 MHz
  3967. CPU 7 runs at 3024 MHz
  3968. oracle@DNSCDBS05:/home/oracle>prtconf|grep Processors
  3969. Number Of Processors: 2

  3970. prtconf



  3971. prtdiag




  3972. select userenv('LANGUAGE') from dual;

  3973. archive log list;



  3974. select name from v$datafile;



  3975. ------------------------------- 表空間歷史增長量
  3976. select a.name, b.*
  3977.   from v$tablespace a,
  3978.        (select tablespace_id ts#,
  3979.                trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) datetime,
  3980.                round(max(tablespace_usedsize * 8 / 1024),2) ts_used_size_M,
  3981.                round(max(v.tablespace_size * 8 / 1024),2) ts_size_MB,
  3982.                round(max(tablespace_maxsize * 8 / 1024/1024)) ts_maxsize_G
  3983.           from dba_hist_tbspc_space_usage v
  3984.          where trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) >=
  3985.                trunc(sysdate - 10)
  3986.          group by tablespace_id,
  3987.                   trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))
  3988.          order by tablespace_id,
  3989.                   trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))) b
  3990.  where a.ts# = b.ts#
  3991.  ORDER BY b.TS#,b.datetime;








  3992. --------------------------------------------- BIOS設定之UEFI BIOS 切換為 Legacy BIOS

  3993. 1、OS Optimized Defaults系統預設優化設定 配置為DISABLE
  3994. 2、CSM相容模組設定 配置為ENABLE
  3995. 3、Boot Mode啟動方式選擇 配置為:Legacy only
  3996. 4、Secure boot 配置為:DISABLE




  3997. ---------------------------------- nmon

  3998. nmon -f -t -r nmon_lhr -s 10 -c 60
  3999. nmon -f -t -r nmon_lhr -s 30 -c 10
  4000. nmon -s10 -c60 -f -m /home/


  4001. 上面命令的含義是:

  4002. -f :按標準格式輸出檔名稱:<hostname>_YYYYMMDD_HHMM.nmon
  4003. -t :輸出最耗資源的程式
  4004. -r : nmon生成的標題,監控記錄的標題
  4005. -s :每隔n秒抽樣一次,這裡為30秒
  4006. -c :取出多少個抽樣數量,這裡為10,即監控=10*30/60=5分鐘
  4007. -m : 生成的資料檔案的存放目錄。
  4008.  
  4009. -----------------自動按天採集資料:
  4010. 在 crontab 中增加一條記錄:
  4011. 0 0 * * * root nmon -s300 -c288 -f -m /home/ > /dev/null 2>&1
  4012. 300*288=86400 秒,正好是一天的資料。





  4013. ------------------ 根據相對位置建立快捷方式
  4014. %SystemRoot%\explorer.exe "一個絕對路徑或相對路徑"
  4015. 如:
  4016. %SystemRoot%\explorer.exe "..\TEST\TEST\TEST.txt"
  4017. 注意:得把起始位置清空




  4018. ----------------- OGG

  4019. ------- source端準備
  4020. SQL> col current_scn format 999999999999999
  4021. SQL> Select current_scn from v$database;

  4022.      CURRENT_SCN
  4023. ----------------
  4024.   12242466771468

  4025. SQL>

  4026.  
  4027.    
  4028. expdp XPADB/XPADB directory=DMP dumpfile=xpadb_20160125_01.dmp LOGFILE=xpadb_20160125.log TABLES=BASE_ACTIONPOWER,BASE_BANK,BASE_BANKMERGE FLASHBACK_SCN=12242466771468

  4029.  
  4030.  ---------- target 備份
  4031. expdp xpadrpt/xpadrpt directory=OGGD dumpfile=xpadb_20160125_02.dmp LOGFILE=xpadb_20160125_2.log TABLES=BASE_ACTIONPOWER,BASE_BANK,BASE_BANKMERGE


  4032. 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


  4033. start replicat ggsrep , aftercsn 12242466771468


  4034. ------------- VirtualBox 修改磁碟大小 51200 為5G
  4035. vboxmanage list hdds
  4036. vboxmanage modifyhd "E:\My Virtual Machines\VirtualBox VMs\VirtualBox_XP\Windows XP.vdi" --resize 51200


  4037. --------------WPS 去掉文件漫遊
  4038. Windows Registry Editor Version 5.00
  4039. [HKEY_CURRENT_USER\Software\Kingsoft\Office\6.0\plugins\officespace]
  4040. "roaminghomepageguidedtag"="9.1.0.4715"
  4041. 檔案另存為.reg格式。在wps關閉下,雙擊該檔案,按”是“和”確定“匯入登錄檔。開啟wps,沒有文件漫遊了。如果希望再次顯示”文件漫遊“,將該給檔案中的"9.1.0.4715"改為""儲存,執行就可了。





  4042. ------------------------- Linux 許可權
  4043. 755表示該檔案所有者對該檔案具有讀、寫、執行許可權,該檔案所有者所在組使用者及其他使用者對該檔案具有讀和執行許可權。

  4044. linux檔案許可權一般都以8進製表示,格式為abc的形式,其中a,b,c各為一個數字,分別表示User、Group、及Other對該檔案的操作許可權;
  4045. 如果檔案許可權用二進位制表示那麼是9位bit,從左至右,1-3位數字代表檔案所有者的許可權,4-6位數字代表同組使用者的許可權,7-9數字代表其他使用者的許可權;
  4046. 而具體的許可權是由數字來表示的,讀取的許可權等於4,用r表示;寫入的許可權等於2,用w表示;執行的許可權等於1,用x表示;
  4047. 通過4、2、1的組合,得到以下幾種許可權:0(沒有許可權);4(讀取許可權);5(4+1 | 讀取+執行);6(4+2 | 讀取+寫入);7(4+2+1 | 讀取+寫入+執行)
  4048. 常用的linux檔案許可權如下:
  4049. 444 r--r--r--
  4050. 600 rw-------
  4051. 644 rw-r--r--
  4052. 666 rw-rw-rw-
  4053. 700 rwx------
  4054. 744 rwxr--r--
  4055. 755 rwxr-xr-x
  4056. 777 rwxrwxrwx
  4057. 這裡以755為例:
  4058. 1-3位7等於4+2+1,rwx,所有者具有讀取、寫入、執行許可權;
  4059. 4-6位5等於4+1+0,r-x,同組使用者具有讀取、執行許可權但沒有寫入許可權;
  4060. 7-9位5,同上,也是r-x,其他使用者具有讀取、執行許可權但沒有寫入許可權。
  4061. rwxr-xr-x: 當前檔案 對 所屬使用者 為 可讀可寫可執行,對所屬組為可讀可執行,對其他使用者為 可讀可執行
  4062. Linux 中對於檔案的許可權 分為 可讀(r),可寫(w),可執行(x),其對應的 8進位制程式碼是
  4063. 可讀(r):4,可寫(w):2,可執行(x):1 ,如果有多個許可權,那麼對應的8進位制數字就是各個單獨許可權數字相加,3個數字中,第一個代表檔案所屬使用者,第二個 代表檔案所屬組,第三個 代表 其他。 那麼 對於 所屬使用者為可讀可寫可執行 就是 4+2+1 = 7, 對於所屬組為 可讀可執行就是 4+1 = 5 ,對於其他使用者 為 可讀 可執行 就是 4+1 = 5, 合起來就是 755 的許可權



  4064. -------------- linux下檢視所有使用者

  4065. cat /etc/passwd |cut -f 1 -d :



  4066. ---------- AIX檢視所有使用者及其id
  4067. lsuser ALL |cut -d ' ' -f 1
  4068. lsuser ALL |cut -d ' ' -f 2
  4069. lsuser ALL |cut -d ' ' -f 1-2

  4070. ---------- AIX檢視所有使用者組及其id
  4071. lsgroup ALL |cut -d ' ' -f 1
  4072. lsgroup ALL |cut -d ' ' -f 2
  4073. lsgroup ALL |cut -d ' ' -f 1-2


  4074. ---------- linux和AIX檢視所有使用者及其id
  4075. cat /etc/passwd |cut -d : -f 1
  4076. cat /etc/passwd |cut -d : -f 3
  4077. cat /etc/passwd |cut -d : -f 1,3
  4078. cat /etc/passwd | awk 'BEGIN{FS=":"} {printf "%-15s %-10s %-20s \n",$1,$3,$6}' | sort -n -k2
  4079. cat /etc/passwd | awk 'BEGIN{FS=":"} $3>=100 {printf "%-15s %-10s %-20s \n",$1,$3,$6}' | sort -n -k2



  4080. ---------- linux和AIX檢視所有使用者組及其id
  4081. cat /etc/group |cut -d : -f 1
  4082. cat /etc/group |cut -d : -f 3
  4083. cat /etc/group |cut -d : -f 1,3
  4084. cat /etc/group | awk 'BEGIN{FS=":"} {printf "%-15s %-10s \n",$1,$3}' | sort -n -k2
  4085. cat /etc/group | awk 'BEGIN{FS=":"} $3>=100 {printf "%-15s %-10s \n",$1,$3}' | sort -n -k2




  4086. [root@redhat4 ~]# cat /etc/group | awk 'BEGIN{FS=":"} {printf "%-10s %-5s \n",$1,$3}'
  4087. root 0
  4088. bin 1
  4089. daemon 2
  4090. sys 3
  4091. adm 4
  4092. tty 5
  4093. xfs 43
  4094. ntp 38
  4095. gdm 42
  4096. stapdev 101
  4097. stapusr 102
  4098. pegasus 65
  4099. htt 103
  4100. oinstall 500
  4101. dba 501

  4102. ---------- linux和AIX檢視所有使用者組及其id,且id>=100
  4103. [root@redhat4 ~]# cat /etc/group | awk 'BEGIN{FS=":"} $3>=100 {printf "%-10s %-5s \n",$1,$3}'
  4104. users 100
  4105. nfsnobody 4294967294
  4106. stapdev 101
  4107. stapusr 102
  4108. htt 103
  4109. oinstall 500
  4110. dba 501
  4111. [root@redhat4 ~]#






  4112. -------------------------------------------------------------------------------- 虛擬機器系列 --------------------------------------------------------------------------------
  4113. ------------------------------------------- 最新的XP虛擬機器 Windows XP.vdi
  4114. 虛擬機器安裝:無論是綠色還是安裝都需要有管理員的許可權

  4115. 虛擬機器中安裝windows7: 需要進入PE系統後再安裝

  4116. c:
  4117. cd C:\Program Files (x86)\VMware\VMware Workstation\


  4118. ----檔案合併
  4119. 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"


  4120. ---增大虛擬機器檔案 -x後的引數為磁碟擴充套件後的總大小
  4121. vmware-vdiskmanager -x 15Gb "G:\VMware Space\Red Hat Enterprise Linux 5.vmdk"

  4122. ---壓縮磁碟空間,但是磁碟的最大值不變
  4123. vmware-vdiskmanager -k "G:\My Virtual Machines\RHEL6.5_LHRDB\RHEL6.5_LHRDB_DATA.vmdk"




  4124.  
  4125. {} 必須有
  4126. [] 可選項
  4127. | 選擇
  4128. <> 註釋或引數值
  4129. ()



  4130. -------------------------------------------------------------------------------- CHM --------------------------------------------------------------------------------
  4131.  chm檔案是由Windows目錄下的hh.exe這個檔案開啟的,但是它的搜尋功能卻不是這個檔案能辦到的, 所以, chm能開啟卻無法搜尋, 或者是MSDN能開啟卻無法搜尋, 即使重灌也無濟於事, 有的人遇到這個問題還從其他人的機器上重新拷貝了hh.exe這個檔案, 發現仍然不能解決這個問題。 其實解決這個問題,很簡單, 在執行裡面執行以下命令:

  4132. regsvr32 hhctrl.ocx
  4133. regsvr32 itss.dll
  4134. regsvr32 itircl.dll //這個很重要,是關於全文搜尋的。

  4135. 問題就可以解決了。 最後一個命令特別重要, 以前我找到的解決方法只有前面兩個, 沒有最後一個, 還是解決不了問題的。

  4136. 如果chm格式檔案出現“網頁不能瀏覽”的錯誤,在該文件上點選滑鼠右鍵,解除鎖定即可。




  4137. ------------------------------------ shell中的引號

  4138. 單引號和雙引號的區別。單引號告訴shell忽略所有特殊字元,而雙引號忽略大多數,但不包括$、\、`。





  4139. -------------------------------------------
  4140. Sub setpicsize() '批量選中圖片

  4141. Dim j '計數圖片個數

  4142. Application.ScreenUpdating = False
  4143. For j = 1 To ActiveDocument.InlineShapes.Count '檔案中圖片總個數,圖片型別為inlineshapes
  4144.     'ActiveDocument.InlineShapes(j).Height = ActiveDocument.InlineShapes(j).Height '設定高度
  4145.     'ActiveDocument.InlineShapes(j).Width = ActiveDocument.InlineShapes(j).Width '設定寬度
  4146.     ActiveDocument.InlineShapes(j).Range.Editors.Add wdEditorEveryone
  4147. Next j
  4148. ActiveDocument.SelectAllEditableRanges (wdEditorEveryone)
  4149. ActiveDocument.DeleteAllEditableRanges (wdEditorEveryone)
  4150. Application.ScreenUpdating = True

  4151. End Sub



  4152. 'ALTER SYSTEM KILL SESSION ''' || s.SID || ',' || s.SERIAL# ||',@'||A.INST_ID||' IMMEDIATE ;' kill_session,
  4153. 'ALTER SYSTEM KILL SESSION ''' || s.SID || ',' || s.SERIAL# ||''' IMMEDIATE ;' kill_session,
  4154. ALTER SYSTEM KILL SESSION '1228,42549,@1';
  4155. SELECT 'ALTER SYSTEM DISCONNECT SESSION ''' || V.SID || ',' || V.SERIAL# || ',@' ||
  4156.        V.INST_ID || ''' IMMEDIATE',
  4157.        'ALTER SYSTEM DISCONNECT SESSION ''' || V.SID || ',' || V.SERIAL# ||
  4158.        ''' IMMEDIATE',
  4159.        V.*
  4160.   FROM GV$SESSION V;


  4161. -----清理killed的會話
  4162. -----方法1
  4163. select spid, program from v$process
  4164.     where program!= 'PSEUDO'
  4165.     and addr not in (select paddr from v$session)
  4166.     and addr not in (select paddr from v$bgprocess)
  4167.     and addr not in (select paddr from v$shared_server);

  4168. select INST_ID, spid, program,'kill -9 '|| spid kill9
  4169.   from gv$process a
  4170.  where program != 'PSEUDO'
  4171.    and (INST_ID, addr) not in (select INST_ID, paddr from gv$session)
  4172.    and (INST_ID, addr) not in (select INST_ID, paddr from gv$bgprocess)
  4173.    and (INST_ID, addr) not in (select INST_ID, paddr from gv$shared_server)
  4174.    and a.PNAME is null;

  4175. -----方法2
  4176. set line 9999
  4177. col sessionid format a20
  4178. col sessionid_killed format a20
  4179. col kill_session format a60

  4180. SELECT a.INST_ID,
  4181.        a.SID || ',' || a.SERIAL# || ',' ||
  4182.        (select spid
  4183.           from gv$process b
  4184.          where b.INST_ID = a.INST_ID
  4185.            and A.creator_addr = b.ADDR --and decode(a.status,'KILLED',A.creator_addr,A.PADDR) = b.ADDR
  4186.         ) sessionid,
  4187.        a.PADDR,
  4188.        a.STATUS,
  4189.        a.PROGRAM,
  4190.        'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' kill_session
  4191.   FROM gv$session a
  4192.  WHERE a.USERNAME = 'SYS'
  4193.    and a.STATUS = 'KILLED';


  4194. -----方法3
  4195. SELECT a.SID || ',' || a.SERIAL# || ',' ||
  4196.        (select spid
  4197.           from gv$process b
  4198.          where b.INST_ID = a.INST_ID
  4199.            and A.pid = b.pid) sessionid,
  4200.        'alter system kill session ''' || sid || ',' || serial# || ''';' kill_session
  4201.   FROM gV$DETACHED_SESSION a;


  4202. -----方法4
  4203. SELECT INST_ID, spid, program, 'kill -9 ' || spid kill9
  4204.   FROM gv$process a
  4205.  WHERE (a.INST_ID, a.addr) in (select INST_ID, p.addr
  4206.                                  from gv$process p
  4207.                                 where pid <> 1
  4208.                                minus
  4209.                                select INST_ID, s.paddr
  4210.                                  from gv$session s)
  4211.     and a.PNAME is null;



  4212. ---------------- rac 配置SSH互信
  4213. sshUserSetup.sh在GI安裝介質解壓縮後的sshsetup目錄下。下面兩條命令在一個節點上執行即可,在root使用者下執行:
  4214. ./sshUserSetup.sh -user grid -hosts "raclhr-12cR1-N2 raclhr-12cR1-N1" -advanced exverify –confirm
  4215. ./sshUserSetup.sh -user oracle -hosts "raclhr-12cR1-N2 raclhr-12cR1-N1" -advanced exverify -confirm

  4216. -------- 指令碼配置
  4217. grep "^LoginGraceTime 0" /etc/ssh/sshd_config
  4218. [ $? -ne 0 ] && { cp -p /etc/ssh/sshd_config /etc/ssh/sshd_config.org; echo "LoginGraceTime 0" >>/etc/ssh/sshd_config; }

  4219. export hn=`hostname`
  4220. export oth=RACDB2
  4221. export p_pwd='/nfs/software/db/install/inst/sh/11g'
  4222. su - grid -c "$p_pwd/sshUserSetup.sh -user grid -hosts $oth -noPromptPassphrase"
  4223. su - grid -c "ssh $hn hostname"
  4224. su - grid -c "ssh $oth hostname"

  4225. su - oracle -c "$p_pwd/sshUserSetup.sh -user oracle -hosts $oth -noPromptPassphrase"
  4226. su - oracle -c "ssh $hn hostname"
  4227. su - oracle -c "ssh $oth hostname"

  4228. ------分別配置grid和oracle使用者的ssh
  4229. -------- 手工配置
  4230. ----------------------------------------------------------------------------------
  4231. [root@ZFLHRDB1 : /]# su - oracle
  4232. [oracle@ZFLHRDB1 ~]$ mkdir -p ~/.ssh
  4233. [oracle@ZFLHRDB1 ~]$ chmod 700 ~/.ssh
  4234. [oracle@ZFLHRDB1 ~]$ ssh-keygen -t rsa ->回車->回車->回車
  4235. [oracle@ZFLHRDB1 ~]$ ssh-keygen -t dsa ->回車->回車->回車

  4236. -----------------------------------------------------------------------------------
  4237. [root@ZFLHRDB2 : /]# su - oracle
  4238. [oracle@ZFLHRDB2 ~]$ mkdir ~/.ssh
  4239. [oracle@ZFLHRDB2 ~]$ chmod 700 ~/.ssh
  4240. [oracle@ZFLHRDB2 ~]$ ssh-keygen -t rsa ->回車->回車->回車
  4241. [oracle@ZFLHRDB2 ~]$ ssh-keygen -t dsa ->回車->回車->回車

  4242. -----------------------------------------------------------------------------------

  4243. [oracle@ZFLHRDB1 ~]$ cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
  4244. [oracle@ZFLHRDB1 ~]$ cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
  4245. [oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB2 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys ->輸入ZFLHRDB2密碼
  4246. [oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB2 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys ->輸入ZFLHRDB2密碼
  4247. [oracle@ZFLHRDB1 ~]$ scp ~/.ssh/authorized_keys ZFLHRDB2:~/.ssh/authorized_keys ->輸入ZFLHRDB2密碼

  4248. -----------------------------------------------------------------------------------
  4249. 測試兩節點連通性:
  4250.   
  4251. [oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB1 date
  4252. [oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB2 date
  4253. [oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB1-priv date
  4254. [oracle@ZFLHRDB1 ~]$ ssh ZFLHRDB2-priv date

  4255. [oracle@ZFLHRDB2 ~]$ ssh ZFLHRDB1 date
  4256. [oracle@ZFLHRDB2 ~]$ ssh ZFLHRDB2 date
  4257. [oracle@ZFLHRDB2 ~]$ ssh ZFLHRDB1-priv date
  4258. [oracle@ZFLHRDB2 ~]$ ssh ZFLHRDB2-priv date






  4259. -----如何清除Shared Pool中某條SQL語句?
  4260. 如果資料庫版本為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';”
  4261. 使用這種方法,就可以精確的將一個SQL從共享池中刪除,從而使得Oracle為這個SQL重新生成執行計劃。這種方法只針對單個SQL語句,使得解決問題的同時不會造成任何的誤傷。
  4262. SYS@lhrdb> SELECT ADDRESS,HASH_VALUE FROM V$SQLAREA WHERE ROWNUM<=1;
  4263. ADDRESS HASH_VALUE
  4264. ---------------- ----------
  4265. 0000000092D263D0 3231842444
  4266. SYS@lhrdb> EXEC DBMS_SHARED_POOL.PURGE('0000000092D263D0,3231842444','C');
  4267. PL/SQL procedure successfull


  4268. --------------------------------------------- Linux 邏輯卷管理
  4269. pvcreate /dev/sda4
  4270. pvdisplay
  4271. pvs
  4272. vgcreate vg_orasoft /dev/sda4
  4273. vgdisplay
  4274. vgs
  4275. lvcreate -n lv_orasoft_u01 -L 1G vg_orasoft
  4276. lvdisplay
  4277. lvs
  4278. mkfs.ext4 /dev/vg_orasoft/lv_orasoft_u01
  4279. mkdir /u11
  4280. mount /dev/vg_orasoft/lv_orasoft_u01 /u11
  4281. --mv /u01/* /u11/

  4282. --擴充套件邏輯卷
  4283. vgextend vg_orasoft /dev/sdb3
  4284. --lvextend -L +9G /dev/vg_orasoft/lv_orasoft_u01
  4285. lvextend -L 20G /dev/vg_orasoft/lv_orasoft_u01
  4286. resize2fs /dev/vg_orasoft/lv_orasoft_u01 #更新檔案系統
  4287. --收縮邏輯卷
  4288. lvreduce -L -4G /dev/vg_orasoft/lv_orasoft_u01
  4289. --重新命名邏輯卷
  4290. lvrename /dev/vg_orasoft/lv_ora_soft_u01 /dev/vg_orasoft/lv_orasoft_u01


  4291. --- /etc/fstab
  4292. /dev/vg_orasoft/lv_orasoft_u01 /u01 ext4 defaults 0 0
  4293. /dev/vg_orasoft/lv_orasoft_u02 /u02 ext4 defaults 0 0
  4294. /dev/vg_orasoft/lv_orasoft_u03 /u03 ext4 defaults 0 0
  4295. /dev/vg_orasoft/lv_oradata_u04 /u04 ext4 defaults 0 0

  4296. ---找邏輯卷
  4297. lvmdiskscan
  4298. vgchange -ay



  4299. --------------------------------------------- 查詢錶的歷史統計資訊
  4300. SELECT D.OWNER,
  4301.        D.TABLE_NAME,
  4302.        TO_CHAR(D.STATS_UPDATE_TIME, 'YYYY-MM-DD HH24:MI:SS') STATS_UPDATE_TIME
  4303.   FROM DBA_TAB_STATS_HISTORY D
  4304.  WHERE D.TABLE_NAME IN ('TPCCBOKBAL', 'TPCCBOKBAL_TMP', 'TPCCBOKBALJN')
  4305.  ORDER BY D.owner,D.table_name, D.stats_update_time;


  4306. SELECT B.OWNER,
  4307.        B.OBJECT_NAME TABLE_NAME,
  4308.        TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
  4309.        TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
  4310.        D.ROWCNT
  4311.   FROM SYS.WRI$_OPTSTAT_TAB_HISTORY D, DBA_OBJECTS B
  4312.  WHERE D.OBJ# = B.OBJECT_ID
  4313.    AND B.OBJECT_NAME IN
  4314.        ('TEST_STAT', 'TPCCBOKBAL_TMP', 'TPCCBOKBALJN', 'PK_TPCCBOKBAL')
  4315.  ORDER BY D.OBJ#, D.SAVTIME;


  4316. ----------------------- 查詢索引的歷史統計資訊
  4317.   SELECT B.OWNER,
  4318.        B.OBJECT_NAME INDEX_NAME,
  4319.        TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
  4320.        TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
  4321.        D.ROWCNT,
  4322.        D.BLEVEL,
  4323.              D.LEAFCNT,
  4324.              D.DISTKEY,
  4325.              D.CLUFAC
  4326.   FROM SYS.WRI$_OPTSTAT_IND_HISTORY D, DBA_OBJECTS B
  4327.  WHERE D.OBJ# = B.OBJECT_ID
  4328.    AND B.OBJECT_NAME IN ('IND_TEST

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2122294/,如需轉載,請註明出處,否則將追究法律責任。

相關文章