Oracle環境變數、監聽listener.ora、tnsnames.ora、sqlnet.ora配置

lhrbest發表於2017-03-11
Oracle變數SQL

Oracle環境變數、監聽listener.ora、tnsnames.ora、sqlnet.ora配置


點選(此處)摺疊或開啟

  1. NLS_DATE_FORMAT=YYYY-MM-DD HH24:mi:ss
  2. NLS_LANG=AMERICAN_CHINA.ZHS16GBK
  3. ORACLE10G=D:\Program files\app\oracle\product\10.2.0\db_1
  4. ORACLE11G=D:\Program files\app\oracle\product\11.2.0.1\dbhome_1
  5. ORACLE8I=D:\Program files\app\oracle\product\ora8i
  6. ORACLE9I=D:\Program files\app\oracle\product\ora92
  7. ORACLE_HOME=D:\Program files\app\oracle\product\11.2.0.1\dbhome_1
  8. PATH=%ORACLE_HOME%\bin
  9. TNS_ADMIN=%ORACLE_HOME%\NETWORK\ADMIN




  14. ---------cmd下設定使用者環境變數
  15. SETX "NLS_DATE_FORMAT" "YYYY-MM-DD HH24:MI:SS"
  16. SETX "NLS_LANG" "AMERICAN_CHINA.ZHS16GBK"
  17. SETX "ORACLE10G" "D:\Program files\app\oracle\product\10.2.0\db_1"
  18. SETX "ORACLE11G" "D:\Program files\app\oracle\product\11.2.0.1\dbhome_1"
  19. SETX "ORACLE8I" "D:\Program files\app\oracle\product\ora8i"
  20. SETX "ORACLE9I" "D:\Program files\app\oracle\product\ora92"
  21. SETX "ORACLE_HOME" "D:\Program files\app\oracle\product\11.2.0.1\dbhome_1"
  22. SETX "TNS_ADMIN" "%ORACLE_HOME%\NETWORK\ADMIN"
  23. SETX "Path" "%ORACLE_HOME%\bin"
  24. SETX "VBOX_MSI_INSTALL_PATH" "D:\Program Files\Oracle\VirtualBox"



  28. alter session set nls_language='SIMPLIFIED CHINESE';
  29. alter system set nls_language='AMERICAN' scope=spfile;




  34. ------------------------------------ sqlnet.ora----------------------------------------------
  35. ---------- windows
  36. SQLNET.AUTHENTICATION_SERVICES= (NTS)
  37. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

  39. ---------- linux
  40. SQLNET.AUTHENTICATION_SERVICES= (ALL)
  41. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)



  45. --------------------------------------------------------------------- oracle
  46. # .bash_profile

  48. # Get the aliases and functions
  49. if [ -f ~/.bashrc ]; then
  50.     . ~/.bashrc
  51. fi

  53. # User specific environment and startup programs

  55. export PATH=$PATH:/sbin/:$HOME/bin

  57.   
  58. # for oracle user

  60. unset USERNAME

  62. export GLOBAL_DB_UNIQUE_NAME=orclasm
  63. export ORACLE_HOSTNAME=192.168.128.134
  64. export ORACLE_UNQNAME=orclasm

  66. export EDITOR=vi
  67. export ORACLE_SID=orclasm
  68. export ORACLE_BASE=/u01/app/oracle
  69. export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
  70. export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
  71. export ORACLE_ALERT=$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace
  72. export PATH=$ORACLE_HOME/bin:$PATH
  73. export TNS_ADMIN=$ORACLE_HOME/network/admin
  74. export ORACLE_PATH=.:$ORACLE_BASE/dba_scripts/sql:$ORACLE_HOME/rdbms/admin
  75. umask 022

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

  82. alias sqlplus='rlwrap sqlplus'
  83. alias rman='rlwrap rman'
  84. alias asmcmd='rlwrap asmcmd'
  85. alias alert_log='tail -200f $ORACLE_ALERT/alert_$ORACLE_SID.log'
  86. alias alert_xml='tail -200f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/alert/log.xml'
  87. alias alert_listener='tail -200f $ORACLE_BASE/diag/tnslsnr/rhel6/listener/trace/listener.log'


  90.  



  94. stty erase ^h
  95. ORACLE_HOME=D:\Program files\app\oracle\product\11.2.0.1\dbhome_1
  96. PATH=%ORACLE_HOME%\bin
  97. TNS_ADMIN=%ORACLE_HOME%\NETWORK\ADMIN
  98. ##NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
  99. NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
  100. #ORACLE_SCRIPT=%ORACLE_HOME%\RDBMS\ADMIN
  101. SQLPATH=.;%ORACLE_BASE%\dba_scripts\sql;%ORACLE_HOME%\rdbms\admin


  104. NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280"
  105. NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
  106. NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
  107. NLS_LANG=AMERICAN_AMERICA.UTF8
  108. NLS_LANG=AMERICAN_AMERICA.AL32UTF8
  109. NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
  110. NLS_LANG=AMERICAN_CHINA.ZHS16GBK


  113. NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss'




  118. --------------------------------------------------------------------- grid

  120. # .bash_profile

  122. # Get the aliases and functions
  123. if [ -f ~/.bashrc ]; then
  124.         . ~/.bashrc
  125. fi

  127. # User specific environment and startup programs

  129. export PATH=$PATH:$HOME/bin


  132. # for grid user
  133. export ORACLE_SID=+ASM
  134. export ORACLE_BASE=/u01/app/grid
  135. export ORACLE_HOME=/u01/app/grid/11.2.0
  136. export LD_LIBRARY_PATH=$ORACLE_HOME/lib
  137. export PATH=$ORACLE_HOME/bin:$PATH
  138. umask 022


  141. alias sqlplus='rlwrap sqlplus'
  142. alias asmcmd='rlwrap asmcmd'
  143. alias alert_log='tail -200f $ORACLE_BASE/diag/asm/+asm/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
  144. alias crsd_log='tail -200f $ORACLE_HOME/log/$HOSTNAME/crsd/crsd.log'



  148. 在linux和unix上有時不知道該使用LD_LIBRARY_PATH還是LIBPATH，經過搜尋和實際使用，實際的情況如下

  150. OS Environment Variable                Name
  151. Linux                        LD_LIBRARY_PATH
  152. Solaris 32-bit ruby                LD_LIBRARY_PATH_32 or LD_LIBRARY_PATH
  153. Solaris 64-bit ruby                LD_LIBRARY_PATH_64 or LD_LIBRARY_PATH
  154. HP-UX PA-RISC 32-bit ruby            SHLIB_PATH
  155. HP-UX PA-RISC 64-bit ruby            LD_LIBRARY_PATH
  156. HP-UX IA64                    LD_LIBRARY_PATH
  157. Mac OS X                    DYLD_LIBRARY_PATH
  158. AIX                        LIBPATH



  162. -----------------------------------------------------------------------------------------------
  163. ------------------------------------listener.ora--------------------------------------------
  164. --動態監聽： alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.157)(PORT = 1521))';
  165. -----------------------------------------------------------------------------------------------
  166. # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
  167. # Generated by Oracle configuration tools.

  169. LISTENER =
  170.   (DESCRIPTION_LIST =
  171.     (DESCRIPTION =
  172.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.129)(PORT = 1521))
  173.     )
  174.   )
  175. SID_LIST_LISTENER =
  176.   (SID_LIST =
  177.    (SID_DESC =
  178.       (SID_NAME = PLSExtProc)
  179.       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
  180.       (PROGRAM = extproc)
  181.     )
  182.    (SID_DESC =
  183.      (GLOBAL_DBNAME = orcl.lhr.com)
  184.      (SID_NAME = orcl)
  185.      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
  186.    )
  187.    (SID_DESC =
  188.      (GLOBAL_DBNAME = rman.lhr.com)
  189.      (SID_NAME = rman)
  190.      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
  191.    )
  192.   )

  194. LSNR2 =
  195.   (DESCRIPTION_LIST =
  196.     (DESCRIPTION =
  197.       (ADDRESS_LIST =
  198.         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.129)(PORT = 1526))
  199.       )
  200.     )
  201.   )
  202. SID_LIST_LSNR2 =
  203.   (SID_LIST =
  204.     (SID_DESC =
  205.       (SID_NAME = PLSExtProc)
  206.       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
  207.       (PROGRAM = extproc)
  208.     )
  209.   )

  211. ADR_BASE_LISTENER = /u01/app/oracle


  214. ------------windows 下
  215. SID_LIST_LISTENER =
  216.   (SID_LIST =
  217.     (SID_DESC =
  218.       (SID_NAME = CLRExtProc)
  219.       (ORACLE_HOME = D:\app\oracle\product\11.2.0.1)
  220.       (PROGRAM = extproc)
  221.       (ENVS = "EXTPROC_DLLS=ONLY:D:\app\oracle\product\11.2.0.1\bin\oraclr11.dll")
  222.     )
  223.     (SID_DESC =
  224.      (GLOBAL_DBNAME = orcl.lhr.com)
  225.      (SID_NAME = orcl)
  226.      (ORACLE_HOME = D:\app\oracle\product\11.2.0.1)
  227.    )
  228.   )


  231. -----------------------/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
  232. # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
  233. # Generated by Oracle configuration tools.

  235. orcl =
  236.   (DESCRIPTION =
  237.     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.133)(PORT = 1521))
  238.     (CONNECT_DATA =
  239.       (SERVER = DEDICATED)
  240.       (SERVICE_NAME = orcl.lhr.com)
  241.     )
  242.   )

  244. #### 動態監聽非預設埠
  245. LSNR_ORCL =
  246.   (DESCRIPTION =
  247.     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.129)(PORT = 1526))
  248.     (CONNECT_DATA =
  249.       (SERVICE_NAME = orcl)
  250.     )
  251.   )


  254. -------------------------------------------------------------------- rac
  255. --- TAF BASIC方式
  256. racdb_vip =
  257.   (DESCRIPTION =
  258.     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.132)(PORT = 1521))
  259.     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.131)(PORT = 1521))
  260.     (LOAD_BALANCE = on)
  261.     (CONNECT_DATA =
  262.       (SERVER = DEDICATED)
  263.       (SERVICE_NAME = jmrac)
  264.       (FAILOVER_MODE =
  265.          (TYPE = SELECT)
  266.          (METHOD = BASIC)
  267.          (RETRIES = 180)
  268.          (DELAY = 5)
  269.       )
  270.     )
  271.   )

  273. --- TAF preconnect方式
  274. racdb_vip1 =
  275.   (DESCRIPTION =
  276.     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.131)(PORT = 1521))
  277.     (CONNECT_DATA =
  278.       (SERVER = DEDICATED)
  279.       (SERVICE_NAME = jmrac)
  280.       (FAILOVER_MODE =
  281.          (TYPE = SELECT)
  282.          (METHOD = BASIC)
  283.          (BACKUP = preconnect)
  284.       )
  285.     )
  286.   )

  288. racdb_vip2 =
  289.   (DESCRIPTION =
  290.     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.132)(PORT = 1521))
  291.     (CONNECT_DATA =
  292.       (SERVER = DEDICATED)
  293.       (SERVICE_NAME = jmrac)
  294.       (FAILOVER_MODE =
  295.          (TYPE = SELECT)
  296.          (METHOD = BASIC)
  297.          (BACKUP = preconnect)
  298.       )
  299.     )
  300.   )

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

相關文章