ITPub博客

首页 > 数据库 > Oracle > 小麦苗的常用代码(仅限自己使用)

小麦苗的常用代码(仅限自己使用)

原创 Oracle 作者:lhrbest 时间:2016-07-20 19:51:52 0 删除 编辑

点击(此处)折叠或打开

  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 搜索命令历史 ,当找到命令后,通常再按回车键就可以执行该命令。如果想对找到的命令进行调整后再执行,则可以按一下左或右方向键。







  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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1153
  • 访问量
    7092665