2009年12月15日 星期二

終於了解什麼是合法/不合法IP

日前不了解什麼是合法IP,為什麼要申請合法ip,看了這個連結就大徹大悟了!解釋的好清楚!
如果大家跟我一樣的疑問~請看這個url吧!
不合法 IP Address 的選用

2009年12月10日 星期四

[SQL Server]序號(Sequence)產生器(可依不同需要開發)

需求:
1. 取得目前的CUR_SEQ
2. New CUR_SEQ= CUR_SEQ + 1 (超過6位數,會重置為1)
3. Update New CUR_STAN
4. 輸出 New CUR_STAN
限制: 確保每個Client取得的CUR_SEQ號碼牌為唯一...

需要如下方的結果:(A+B+SEQNO)
ID NO CUR_SEQ
----- ---- -------------
8 0 1
8 1 1
8 2 1
(3 個資料列受到影響)

想了一下,若要確保每個用戶端在同時間取同一組序號時,不得有重覆取得的問題,
但又不能影響其它組取得序號,因此覺得使用with(ROWLOCK)會是較好的決定:

With(ROWLOCK) : 若有table有規畫PK constraint或index時,則只會lock住該筆記錄,
不會影響其它組的資料存取。
ROWLOCK其實也並不是直接對該筆資料列作鎖定,而是針對其INDEX的KEY值作鎖定。
下方有online book說法
/*
Lock hints ROWLOCK, UPDLOCK, AND XLOCK that acquire row-level locks may place locks on index keys rather than the actual data rows. For example, if a table has a nonclustered index, and a SELECT statement using a lock hint is handled by a covering index, a lock is acquired on the index key in the covering index rather than on the data row in the base table.
*/

我的作法:
A.新增SEQUENCE Table: SEQNO



CREATE TABLE SEQNO(ID NVARCHAR(5) NOT NULL, NO NVARCHAR(2) NOT NULL, CUR_SEQ NUMERIC(6) NOT NULL, CONSTRAINT PK_SEQNO PRIMARY KEY (ID,NO));


B.新增SEQUENCE PROCEDURE:SP_SEQNO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_SEQNO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_SEQNO]
GO

CREATE PROCEDURE SP_SEQNO (
@ID nvarchar(5),
@NO nvarchar(2),
@MAXSEQ NUMERIC(6) output
)
AS
DECLARE @CURSEQ NUMERIC(6);
SET @CURSEQ = 0;
BEGIN
/* 初始值*/
IF NOT EXISTS (SELECT * FROM SEQNO WHERE ID=@ID AND NO=@NO )
BEGIN
INSERT INTO SEQNO (ID,NO,CUR_SEQ) VALUES(@ID,@NO,0);

UPDATE SEQNO WITH(ROWLOCK)
SET @MAXSEQ=CUR_SEQ= CUR_SEQ+1
WHERE ID = @ID
AND NO= @NO;
END
ELSE
BEGIN
/*抓出目前的SEQNO放至@CURSEQ,最大值限制判斷 */
SELECT @CURSEQ = CUR_SEQ
FROM SEQNO WITH(ROWLOCK)
WHERE ID = @ID
AND NO= @NO

/* 若超過6位數需重置為1*/
IF (@CURSEQ =999999)
BEGIN

UPDATE SEQNO WITH(ROWLOCK)
SET @MAXSEQ=CUR_SEQ=0 WHERE ID =@ID AND NO =@NO;

END
ELSE
IF (@CURSEQ<999999)
BEGIN
UPDATE SEQNO WITH(ROWLOCK)
SET @MAXSEQ=CUR_SEQ=CUR_SEQ+1 WHERE ID =@ID AND NO =@NO;
END
END
END

參考資料: url= lock explain:http://technet.microsoft.com/en-us/library/ms187373.aspx
recieve varaible output valus from procedure: 如何接收 Store Procedure 的傳回值

[MSSQL] IF EXISTS ...END寫法架構

IF EXISTS (SELECT * FROM Table1)BEGIN
...............
END
ELSE
BEGIN
.............
END

2009年12月8日 星期二

Oracle處理DATE及TIMESTAMP需注意的地方

oracle官方說法上的確證明了~
基本上Date 型態精準度只能到second,不含fractional seconds 或time zone,
而timestamp可支援frational seconds 0~9位,default是到小數點六位。請再往下看(可跳過例子)

舉例: Table: test( ttime DATE,ttimestamp TIMESTAMP)

利用"TO_DATE()"函數
一定要使用"to_timestamp()" 函數才能將含有毫秒或到奈秒的資料塞進timestamp型態的欄位中。


就目前實驗的結果就顯示
TO_TIMESTAMP才可支援到秒數小數點後9位。TO_DATE是不行的!

下面是oracle文件內建型態說明: 請看date及timestamp說明




若想知道oracle對日期的描述字元有哪些格式,請參考:
http://download.oracle.com/docs/cd/B13789_01/server.101/b10758.pdf
[7-3頁 Datetime Format Models]

由於oracle的session及database層級的參數設定,會以session層級為優先。
所以如果有權限的情況下想了解目前database及client端的format設定時,
可利用query下方指令。
Database層:
select * from nls_database_parameter;
 
Session層:
select * from nls_session_parameter;
 
若想修改session層的設定:
alter session set nls_data_format=yyyy-mm-dd;
 







2009年12月7日 星期一

Microsoft Offcie -Excel 數字的尾數自動調整為0

Excel 貼上數字型的格式時,例如像信用卡號的16個字元長度,excel會自動調整為0
例如 4394112233110022 -->會變成 4394112233110020
不管我將儲存格格式更改為數字去掉小數點,尾數都會自動調整為0,
後來用了好久,在office online book上面查詢後,才發現原來
office excel在數字的精準度上面,只能支援15位的有效數字,若要輸入信用卡號,且要完整顯示的話~有兩個作法
1.儲存格欄位格式需調整為是"文字"。
2.利用[ ' ]單引號加在數字前方,強迫指定為文字格式。
這樣就能正確顯示出信用卡號了!

參考url:http://office.microsoft.com/zh-tw/excel/HA102748231028.aspx#5

2009年7月27日 星期一

Oracle 9i for Windows Platform 32bit 解決Memory-SGA 最多有1.7G限制的設定(AWE)

環境 : Windows 2003 Enterprise Edition SP2 (32bit Windows platform)
使用windows cluster機制& Oracle Failsafe實現oracle instance cluster。
Oracle: Oracle Enterprise Edition 9i (9.2.0.1 patch to 9208)
Memory: 8G RAM


由於Oracle on 32-bit Windows platforms,限制預設使用的SGA設定最多不能2G(實作上1.6G),而metalink上有解決方法,在此提出並將實作結果記錄下來:

Metalink doc:

Subject: Implementing Address Windowing Extensions (AWE) or VLM on Windows Platforms
Doc Id: 225349.1


設置的方式有2,稱之為"PAE" (Physical Address Extensions) 或"AWE" (Address Windowing Extensions) 這兩種方式可以交替使用。
而AWE的設定前提條件: Physical RAM在4G以上。
可使用的平台有
* Windows 2000 Datacenter Server
* Windows 2000 Advanced Server
* Windows 2003 Data Center Edition (32-Bit)
* Windows 2003 Enterprise Edition (32-Bit)
在以上的OS 平台,AWE可內嵌設定於OS層,因此不需要特別指定driver去驅動,就可使用多餘的memory。
而不能使用的平台有:
* Windows 2000 Server (Standard)
* Windows 2000 Professional
* Windows XP Home Edition
* Windows XP Professional
* Windows 2003 Standard Edition
* Windows 2003 Web Edition

ps: 64bit的windows platform 不需要AWE的設定,因為64bit-Windows platforms上,單一process最高可使用到8 TeraBytes的memory。

AWE in 32Bit RDMS release 版本為:
* Oracle 8.1.6.x
* Oracle 8.1.7.x
* Oracle 9.2.x
* Oracle 10.1.x
* Oracle 10.2.x
ps: Oracle does NOT implement AWE support in release 9.0.1.x
而且只限制Enterprise Edition and Standard Edition 才可生效!

##設定步驟##
1. OS level:
  • C:\boot.ini 修改內容:
[operating systems]multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /3GB /PAE /noexecute=optout /fastdetect
"/3GB" 及"/PAE"同時設定的話,則Address space for ORACLE.exe會是3G,且要注意主機RAM是否最多到16G,若超過的話~則建議只使用一種即可。
  • 確認user的系統權限

在服務中設定的oracle SID 登入身份必需要被授與" Lock Page in Memory"的系統權限,一般oracle SID service登入身份會是" LocalSystem" account,請確認LocalSystem的帳號擁有此系統權限。

設定方式: 開始\控制台\系統管理工具\本機安全性原則-->選擇--> "本機原則\使用者權利指派\鎖定記憶體分頁",

在本機安全性設定新增使用者或群組,並加入該使用者。這個設定是為了保留process的實際記憶體分頁,阻止系統將資料分頁至virtual虛擬記憶體中,這種方式會減少實際記憶體的可使用量。設定後記得重啟service。

  • 基碼修改(新增: AWE_WINDOW_MEMORY)

在HKLM\Software\Oracle\HomeX 新增一個基碼(reg_sz格式),計算單位為bytes,default為1g,因此新增值 AWE_WINDOW_MEMORY=1073741824

2. Database/Instance level:

  • init.ora

Add [ USE_INDIRECT_DATA_BUFFERS=TRUE ]
Add [DB_BLOCK_BUFFERS= /8192(db_block_size)]

注意: 若DB_CACHE_SIZE與USE_INDIRECT_DATA_BUFFERS=TRUE 同時存在時,會出現ORA-00385: cannot enable Very Large Memory with new buffer cache 的錯誤發生。

因此只要設置DB_BLOCK_BUFFERS,移除DB_CACHE_SIZE的參數即可。其實buffer cache size的大小設定還要保留4G 的RAM下來給os或其它應用程式使用,因此可算出buffer cache最多可設定的值為多少。

舉例說明: 8G RAM,use Default value of 1G for AWE_WINDOW_MEMORY,buffer cache最多可以設至的公式

Max Buffer cache (Total Ram-4G + AWE_WINDOW_MEMORY) =8G-4G+1G=5G

而db block size=8192 ,則db_block_buffers的設定最大值為5G/8192 =5368709120/8192=655360

3. 重新啟動instance。

##完成設定##

總SGA size= ((db_block_buffers*buffer size ) + (shared_pool_size+large_pool_size +java_pool_size+log_buffers) +1MB

SGA +oracle 臨界值不能超過可用的虛擬記憶體,因為虛擬記憶體要計算buffer cache=db_block_buffer*db_block_size得出值。

註: 「監控mapping memory狀態」

可使用下列sql:select * from v$sysstat where statistic# in (154, 155);

10gr2: select statistic#, name from v$sysstat where name like '%map %';

若在10g要設定USER_INDIRECT_DATA_BUFFER,也就是使用AWE,則INIT.ORA不能使用SGA_TARGET的參數,SGA_TARGET=0。

2009年6月5日 星期五

ORA-12638 身份證明檢索失敗-解決原因與方法

今天在裝ap server時,遇到sqlplus id/pass@SID時出現 ORA-12638 身份證明檢索失敗的error。

後來在客戶端沒有外網,也沒遇這樣的問題~call friend幫忙查一下,將

Sqlnet.Authentication_Services=(NTS) 改為Sqlnet.Authentication_Services=(NONE)

就可以正常連線了!



後來查了一下官方的文件

SQLNET.AUTHENTICATION_SERVICES
Purpose


Use the parameter SQLNET.AUTHENTICATION_SERVICES to enable one or more authentication services. If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.
.Default
.None
.Values
Authentication Methods Available with Oracle Net Services:
none -- for no authentication methods. A valid username and password can be used to access the database.
all -- for all authentication methods
nts -- for Windows NT native authentication


Authentication Methods Available with Oracle Advanced Security:
kerberos5 for Kerberos authentication
cybersafe for Cybersafe authentication
radius for RADIUS authentication
dcegssapi for DCE GSSAPI authentication

See Also:
Oracle Advanced Security Administrator's Guide



表示這是一種認證方式,如果是NTS就是用windows的認證模式,表示登入sysdba時,不需要輸入帳號密碼即可登入,若對於Oracle on windows cluster來說,使用的一組virtual IP 本來就不能使用windows認證方式進入database,故解決辦法就是改為none(表示只要在oracle資料庫的使用者、密碼對了~就可以登入了)

要注意一件事!!這個設定只適合在AP Client端作設定,如果在DB server端作此設定的話,會造成"conn / as sysdba" 匿名登入時會失效!

2009年5月15日 星期五

Auto create ORA_DBA group on Windows when install Oracle Database.

居然是由同事告訴我一件oracle 權限大事!

原來是oracle會在Windows 環境上安裝時自動於系統內建立 「ORA_DBA」 group,
administrator也加入此群組中,
看到系統說明,這個群組是要讓本機的administrator登入者,可以利用 sqlplus "/ as sysdba"
登入sysdba角色而不需要帶任何password!



以下是在網址上找到的相關說明!
好詳細哦~

2009-06-30補充說明: 讓本機使用者可以利用匿名(windows帳戶認證)方式進入,還要多一項配合,在TNS_ADMIN\sqlnet.ora "authentication_service=(NTS)"才可以哦!若改為none則不行!
http://www.windowsitpro.com/Article/ArticleID/42280/42280.html?Ad=1 Database Server Windows Group Authentication When you install Oracle on a Windows server, the system creates an ORA_DBA Windows group and automatically adds to that group the Windows account used to install Oracle. The DBA can then add to the group other Windows users who need full Oracle DBA privileges. But be careful—Windows local and domain users within the ORA_DBA group don't have to supply an Oracle username and password. As the Description property for the ORA_DBA group says, Members can connect to the Oracle database as a DBA without a password. For Oracle to accept users in the ORA_DBA group as authenticated users, you need to properly configure the sqlnet.ora file, which Figure 1 shows. For Oracle9i and Oracle8i, this file is in the \%ORACLE_HOME%\network\admin folder, where %ORACLE_HOME% represents the path used to install the Oracle server software. The sqlnet.ora file lets you configure how connections to the Oracle server will be made. The NAMES.DIRECTORY_PATH parameter in the sqlnet.ora file specifies the methods that Oracle clients use to resolve the database connection string name alias. For example, when I type at the command line sqlplus /@test9 the SQL*Plus utility attempts to resolve the test9 alias by using the NAMES.DIRECTORY_PATH entries in the sqlnet.ora file. (For a description of the SQL*Plus tool and information about obtaining the tool, see the "Manipulate Oracle with SQL*Plus" sidebar.) In the sample sqlnet.ora file that Figure 1 shows, the client first attempts Oracle name resolution by using a tnsnames.ora text file, which can reside either locally or on a shared network resource. If the tnsnames.ora file doesn't contain the name, the client will attempt to resolve the name by using an Oracle Names server (Oracle now recommends using Lightweight Directory Access Protocol—LDAP—instead of Oracle Names servers). Finally, the client tries to resolve the name by using a host-name resolution method such as DNS or Network Information Service (NIS). The SQLNET.AUTHENTICATION_SERVICES parameter in the sqlnet.ora file specifies which authentication service Oracle should use when a user attempts to connect to the Oracle server. By default, Oracle9i and Oracle8i enable Windows authentication by means of the following setting: SQLNET.AUTHENTICATION_SERVICES=(NTS) Windows NT always uses NT LAN Manager (NTLM) authentication. Windows Server 2003, Windows XP, and Windows 2000 all use Kerberos authentication when the Oracle client machine is in a Windows 2003 or Win2K domain; otherwise, they use NTLM authentication. The default setting of enforcing Windows authentication isn't compatible with applications that use standard Oracle authentication. And many third-party vendors have applications that use standard Oracle usernames and passwords to connect to Oracle. To support both Oracle and Windows authentication, you can change the authentication service parameter in the server's sqlnet.ora file as follows: SQLNET.AUTHENTICATION_SERVICES= (NONE,NTS) Any changes that you make to the authentication methods can result in connection failures. To detect any such failures, whenever you change the authentication service parameter, use SQL*Plus first to perform basic connectivity testing, then test your Oracle client applications. Because the ORA_DBA group is a Windows group, the Oracle database server uses it only when SQLNET.AUTHENTICATION_SERVICES is using Windows authentication. For example, if Windows authentication is enabled and I go to the command line and type set oracle_sid=test9 sqlplus "/ as sysdba" I can create a SYSDBA privileged connection without supplying an Oracle username and password. The ORACLE_SID value shown in our example in the first command line (i.e., test9) identifies the database connection string alias for sqlplus.exe to use to connect to an Oracle database instance. The second command line specifies the authentication credentials. Double quotes are required for SQL*Plus to interpret the entire connect string, including the spaces, as one command-line parameter. The syntax "\ as sysdba" specifies that the client would like to connect to the Oracle database as the currently logged on Windows user with SYSDBA privileges. Upon entering both commands on my Oracle client machine, the system returned the results that Figure 2 shows. If an Oracle username and password are supplied to SQL*Plus when connecting as SYSDBA, SQL*Plus ignores them. This action isn't a security breach because the Oracle server has authenticated the Windows credentials and not the Oracle credentials.

2009年3月25日 星期三

使用Merge Into 语句实现 Insert/Update - Digest Net

EYGLE上面真的好多寶文章!!

使用MERGE INTO 的SQL 語法完成一些資料比對的處理!
應該會是個好方法~但是對於ROW LOCK是否有影響~還要評估一下~分享:)
使用Merge Into 语句实现 Insert/Update - Digest Net

Merge用法:Oracle 10g中对Merge语句的增强 - Digest Net

從EYGLE網誌查到的一個新語法~~可以試試~

Merge用法:Oracle 10g中对Merge语句的增强 - Digest Net

資料匹配或許會很方便~

Merge用法:Oracle 10g中对Merge语句的增强 - Digest Net

從EYGLE網誌查到的一個新語法~~可以試試~

Merge用法:Oracle 10g中对Merge语句的增强 - Digest Net

資料匹配或許會很方便~

2009年3月17日 星期二

MS SQL Server 如何取sysdate

若建立table,
oracle 寫法
-取日期yymmdd則使用 to_char (sysdate,'YYYYMMDD')
-取時間hh24miss則使用to_char(sysdate,'HH24MISS')

MS SQL Server中文版的預設日期datetime格式是yyyy-mm-dd hh:mm:ss.mmm
sql server寫法上非常不同
-取日期yymmdd,使用 CONVERT(VARCHAR(8), GETDATE(), 112)
-取時間yymmdd,使用REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')
取時間的時候需要多作一次轉換,將":"的地方去除。

另外~convert參數中的112、108,sql server在轉換日期格式時,所代表的編號。
附上Online book上描述的convert 格式編號:

url= http://msdn.microsoft.com/zh-tw/library/ms174450(SQL.90).aspx

一般來說,沒有標示世紀的話~就以yy為主,例如yymmdd的格式編號就是12,
但若要標示世紀的話,就要將12+100,要使用112的編號才能顯示 yyyymmdd。

2009年3月12日 星期四

Oracle HTTP Service 會自動安裝apache元件,開啟80port

最近在客戶那裡安裝oracle Server時,由於
Oracle HTTP Service 會自動安裝Apache元件,開啟80port,
而客戶在執行弱點掃描時,認為80port是安全性漏洞,所以要求要移除http service。

在移除http service後,發現無法完全移除http service,原因是因為Oracle OEM 工具也是需要http service的元件,所以一開始在安裝時,選擇自訂安裝,要disable 下列兩項安裝選項:
A. Disable --Oracle Enterprise Manager Products 9.2.0.1
B.Disable --Oracle HTTP Service 9.2.0.1
這樣才能完全不裝Apache的元件,並通過弱點掃描。

2009年2月18日 星期三

Monitoring Open Cursors & Troubleshooting ORA-1000 Errors

metalink doc. about ORA-01000 error

Subject:
Monitoring Open Cursors & Troubleshooting ORA-1000 Errors

Doc ID:
76684.1
Type:
BULLETIN

Modified Date:
13-JUN-2008
Status:
PUBLISHED
"Checked for relevance on 08-Oct-2007"
Overview
--------
This article contains information on how different cursors are managed and
monitored in PL/SQL. It addresses issues with the open_cursors parameter,
as well as the v$open_cursor view in the context of implicit, declared, and
dynamic cursors.
Monitoring and Managing Open Cursors
------------------------------------
The number of open cursors can limit operation in PL/SQL procedures and
SQL*Plus sessions. While the parameter open_cursors sets the limit,
programming issues can cause the following error:
ORA-1000 maximum open cursors exceeded
Three important values are the following:
o init.ora parameter open_cursors
o v$open_cursor view
o v$sysstat view
These values are similar, but differ in their accounting of Dynamic Cursors.
Note: Dynamic cursors are those opened using dbms_sql.open_cursor().
The means to compute those values are as follows:
>>> View v$open_cursor
'select count(*) from v$open_cursor' =>
implicit cursors used +
distinct explicit cursors opened +
dynamic cursors PARSED and NOT CLOSED.
- Accumulates dynamic cursors PARSED and NOT CLOSED over a session
- Available to system/manager
- Includes the text of open cursors - helpful for debugging
- Since this view does not track unparsed (but opened) dynamic cursors,
the count(*) may not show all cursors that count against open_cursors.
>>> View v$sysstat
'select value from v$sysstat where statistic# = 3' =>
implicit cursors used +
distinct explicit cursors opened +
dynamic cursors OPENED.
- Accumulates dynamic cursors OPENED and NOT CLOSED over a session
- Available to system/manager
- Since this view does track unparsed (but opened) dynamic cursors,
the statistic#3 shows all cursors that count against open_cursors.
>>> init.ora parameter open_cursors =
implicit cursors used +
distinct explicit cursors opened +
dynamic cursors OPENED.
- Accumulates dynamic cursors OPENED and NOT CLOSED over a session

ORA-1000
--------
The following are several items to check when encountering ORA-1000 in
PL/SQL:

1. Be sure that all dbms_sql cursors opened at DECLARE time are closed.
Every unclosed OPEN counts against open_cursors. The number of open
cursors can be determined as follows in SQL*Plus:
select value from v$sysstat where statistic# = 3

2. Be aware that v$open_cursor only tracks the CUMULATIVE number of
implicit + distinct explicit cursors in the procedure PLUS unclosed
dynamic cursors that have been PARSED in the session.
Note: It does not include any dynamic cursors that were opened but
not parsed.
The text of the parsed, open cursors can be determined as follows
in SQL*Plus:
select sql_text from v$open_cursor

3. Dynamic cursors persist from run-to-run in a session, but are not
closeable after a procedure has completed. This can accumulate and
error-out with open_cursors after a number of runs. They will not
appear in v$open_cursors after a session.
The following are two code snippets that can help diagnose ORA-1000. Text
lines are shown for each cursor.
-- snippet 1
declare
cursor opencur is select * from v$open_cursor;
ccount number;
begin
select count(*) into ccount from v$open_cursor;
dbms_output.put_line(' Num cursors open is 'ccount);
ccount := 0;
-- get text of open/parsed cursors
for vcur in opencur loop
ccount := ccount + 1;
dbms_output.put_line(' Cursor #'ccount);
dbms_output.put_line(' text: ' vcur.sql_text);
end loop;
end;
-- snippet 2
select value, name from v$sysstat where statistic# in (2,3);
Related Information
-------------------
"Oracle 8i Reference [Data Dictionary]", (A67790-01) page 3-70, page 3-119
Note 1012266.6 OVERVIEW OF ORA-01000 MAXIMUM NUMBER OF CURSORS
EXCEEDED
.

2009年2月17日 星期二

Toad 支援的Windows OS 及versions for Oracle

目前在windows 2008 64bit Standard Edition (With Oracle Client for 64bit 10.2.0.4)安裝toad 9.7後,在執行toad connection欲連線資料庫時,出現一個警示視窗,說未安裝oracle clients。

google了一下~在toad官網找不到相關的os supported及versions說明~但在wiki 上找到一段說明:
Supported Operating Systems and Versions
TOAD for Oracle 9.7 is available for Windows 32-bit platform 2000/XP/2003/Vista and for Windows 64-bit platform 2003/XP/Vista with 32-bit Oracle Client. For good performance it is enough to have 512 MB RAM whilst for 1GB workstation it presents platform for very comfortable work. Concerning the disk space, it depends on the choice of your distribution: for example, for Expert edition it will take between 75MB and 100MB. When it comes to supported versions of Oracle databases almost all the versions are supported, from 7.3.4 to the last 11g database. To connect you can use clients for Oracle SQL Net, Net 8 (32-bit only) Net 9, Net 10, Net 11 or Instant Client.
不確定這是否就是toad無法連接資料庫的原因?

2009年2月12日 星期四

2009年2月4日 星期三

[Oracle]IMP-00058&ORA-12541 error.

After create a new database ... I met an error when I tried to import data into the new database.



------------------------------------------

OS: Windows 2003 Enterprise Edition

CPU:2.80GHz

RAM:3.75GB

------------------------------------------



The error code as following:



imp bbvsvc/bbvsvc@testw
Import: Release 9.2.0.3.0 - Production on 星期三 2月 4 16:23:31 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
IMP-00058: ORACLE 錯誤 12541

發生ORA-12541: TNS:沒有監聽器

IMP-00000: 匯入作業異常終止



I checked the tnsname.ora and listener.ora, them seems okay...

So, I don't know why it happened...







following shows the part of tnsnames.ora contents:



testw =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.32)(PORT = 1521))

)

(CONNECT_DATA = (SERVICE_NAME = testw)

)

)



and here is the part of Listener.ora contents:



LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))

)

(DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)



SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = O:\oracle\ora92)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = TSCSVCT)

(ORACLE_HOME = O:\oracle\ora92)

(SID_NAME = TSCSVCT)

)

)


Already dealed with...

There are a client and a server on the machine. So ...

The TNS_ADMIN is not O:\oracle\ora92client\network\admin, the correct path is "O:\oracle\ora92\network\admin\".

And the connection information is no correctly, especially "Port" number on the tnsnames.ora.

Changed the Port number from "1526" to "1521", then the Export data process can run smoothly.