最近因為工作的需求關係，
芝麻電腦診所有一篇MS SQL移轉MySQL，是透過MySQL Migration Toolkit來轉的，不過ㄚ琪沒找到。
最後在SourceForge找到了MSSQL to MySQL Exporter，它是用C#的.NET平台寫得，可以資料表，
不過ㄚ琪實際在使用時發現了一個問題，就是不知哪位天兵，
ㄚ琪算是比較龜吧，就開啟原始碼來改了，
警告 1 ‘System.Data.SqlClient.
另外因為Function是關鍵字，所以ㄚ琪需要將cm = new SqlCommand(“select * from “+tablename, objConn);
改成cm = new SqlCommand(“select * from \””+tablename+”\””, objConn);
這樣程式才能正確執行。
好了，這個程式現在不用錢ㄚ琪奉送給各位免費使用。下載=>MSSQLtoMySQL
公司裝了sql 2005 express後啟動有錯誤,在事件檢視器發現了這個第一個錯誤
錯誤訊息如下:
TDSSNIClient初始化失敗，其錯誤為0xd，狀態碼為0×4。
TDSSNIClient初始化失敗，其錯誤為0xd，狀態碼為0×1。
由於網路程式庫發生內部錯誤而無法啟動網路程式庫。若要確定其原因，請檢閱錯誤紀錄檔中此錯誤的前一個錯誤。
SQL Server無法繁衍FRunCM執行緒。請檢查SQL Server錯誤紀錄檔與Windows事件紀錄檔，以取得可能發生之相關問題的詳細資訊。
Monday, March 13, 2006 1:18 PM by SQL Protocols
Paulo,
Looks like you disabled all protocols. Without a protocol enabled, sql server won’t be able accept requests and process queries. You need to enable at least one protocol.
原來是要開啟MSSQLSERVER的通訊協定至少一個協定喔,真是給他有夠笨的!哈
這個問題是出自經濟部所屬事業機構 100年新進職員甄試試題，資訊管理、程式設計(pdf)的一題，這個問題ㄚ琪其實在設計PHP程式，搭配資料庫所設計的應用程式，都會面臨到這樣的難題，因為ㄚ琪本身對安全概念倒是沒有那麼的完整，所以有些程式為了求快，常會捨安全而不顧，不過如果你在開發一個重要的程式時，應該要謹慎這方面的問題，好了，我們先從維基的解釋來看吧。
SQL攻擊（SQL injection，中國大陸稱作SQL注入攻击，台灣稱作SQL资料隐码攻击），簡稱隱碼攻擊，是發生於應用程式之資料庫層的安全漏洞。簡而言之，是在輸入的字串之中夾帶SQL指令，在設計不良的程式當中忽略了檢查，那麼這些夾帶進去的指令就會被資料庫伺服器誤認為是正常的SQL指令而執行，因此遭到破壞。
有部份人認為SQL隱碼攻擊是只針對Microsoft SQL Server而來，但只要是支援批次處理SQL指令的資料庫伺服器，都有可能受到此種手法的攻擊。
在應用程式中若有下列狀況，則可能應用程式正暴露在SQL Injection的高風險情況下：
某個網站的登入驗證的SQL查詢代碼為
strSQL = "SELECT * FROM users WHERE (name = '" + userName + "') and (pw = '"+ passWord +"');"
惡意填入
userName = "' OR '1'='1";
與
passWord = "' OR '1'='1";
時，將導致原本的SQL字串被填為
strSQL = "SELECT * FROM users WHERE (name = '' OR '1'='1') and (pw = '' OR '1'='1');"
也就是實際上執行的SQL命令會變成下面這樣的
strSQL = "SELECT * FROM users;"
因此達到無帳號密碼，亦可登入網站。所以SQL隱碼攻擊被俗稱為駭客的填空遊戲。
這裡頭有提到原因、作用原理、可能造成的傷害以及避免的方法。但是ㄚ琪覺得對一個新手程式設計師而言，有一個協助自動檢查程式的機制或許會更好，不然誰會知道自己寫的程式沒問題，自我感覺良好的人，一定覺得沒問題吧，呵呵，就像我這樣。
查到PHP+MySQL环境下SQL Injection攻防总结，似乎是ㄚ琪開始注意寫PHP程式時，要注意SQL injection了，共勉之，另外這個部落格也有MySQL的最佳化，很可以看喔。
今天在讀深入淺出SQL這書到請看，你的資料表這裡，發現了一個之前沒使用過的語法：
{DESCRIBE | DESC} tbl_name [col_name | wild]
可以用來提供資料表內的欄位資訊，當然這個應該不是標準的SQL語法，應該只是MySQL的語法，因為工作上需求，ㄚ琪也順便查一下MS SQL、DB2及Oracle等常用的資料庫，是否也有類似的語法。
MS SQL：
sp_tables [ [ @table_name = ] 'name' ]
[ , [ @table_owner = ] 'owner' ]
[ , [ @table_qualifier = ] 'qualifier' ] [ , [ @table_type = ] "type" ]
[ , [@fUsePattern = ] 'fUsePattern'];
DB2：
>>-DESCRIBE-----------------------------------------------------> .-OUTPUT-. >--+-+--------+--+-select-statement-+-------------------+------>< | '-call-statement---' | '-+-TABLE--table-name-------------+--+-------------+-' '-INDEXES FOR TABLE--table-name-' '-SHOW DETAIL-'
Oracle：
DESC table DESC view DESC synonym DESC function DESC package
看起來MySQL、Db2跟Oracle都很類似，就只有MS SQL跟別人差比較多，而且從字面上又很難解讀這個命令，唉。
根據這一篇Oracle Database 10g Rel 2 on Ubuntu (Breezy Badger and Dapper Drake) (Howard Rogers)，可以安裝，但是安裝介面會有亂碼!
註：原址已掛掉，特轉在於工作達人供參閱
使用一些大陸那邊說的修改font.properties的方法沒試成功
但是Installing Oracle 10g on RHEL AS 3 Step-by-Step 這一篇介紹
export LC_CTYPE=en_US.UTF-8
這個方式變成英文介面的安裝是暫行的解決方式
I will confess right at the outset that I am very definitely more of a Suse or Red hat/Fedora man than an Ubuntu one, and that my familiarity with the Debian-based distro that has recently been garnering all accolades is merely skin-deep -so long-time Debian users should not get too upset if they see me doing hilariously clumsy things in the course of this article! Nevertheless, I wanted to document how to install Oracle 10g Release 2 onto Ubuntu precisely because Ubuntu appears to have a lot of momentum behind it in the ‘Linux press’, and appears to me at least to be well on the way to becoming the distro against which all others are measured, at least in mindshare terms.
So, despite my usual ‘house rule’ that nothing but “Enterprise-class” distros get the ‘how to install Oracle’ treatment (because they’re the only distros you’re going to meet in a real-world server room); and despite my fondness for all things Suse; this article will indeed explore how you go about performing an Oracle 10g Release 2 installation onto the hottest ‘home distro’ of the moment.
I should mention that although this article was originally written using the Breezy Badger release of Ubuntu, it has subsequently been successfully tested against the newer Dapper Drake (that is, version 6.06, released in June 2006) without any alteration to the steps or instructions being required at all. Some of the Flash movies accompanying the article therefore show Breezy Badger being installed on a machine called Vitellius and some show Dapper Drake being installed on Trajan: but whatever the machine name and whatever Ubuntu version is being depicted, the Oracle-related steps apply equally well in both directions.
Ubuntu can be downloaded for free from the official Ubuntu download site.
Ubuntu’s hardware requirements are fairly minimal (officially, 128MB of RAM and 2GB of disk space for a desktop system) but Oracle’s are not, so make sure your PC actually has at least 512MB of RAM and 6GB of free hard disk space, minimum. Ideally, you will want to install Oracle onto a fresh Ubuntu installation, since “pre-loved” installations will quite probably have a pile of library and version incompatibilities that will play havoc with Oracle. This article is certianly going to use a completely fresh Ubuntu installation, anyway, straight off the CD and without any prior patching or updating. It’s strongly recommended that you do likewise for repeatable, predictable results.
I’m not going to detail the Ubuntu installation, which looks and feels quite different between the 5.10 and 6.06 releases anyway. If you want to see how I went about installing the 5.10 Breezy Badger release, you’re welcome to view a short Flash Movie of the process at your leisure. Version 6.06 is rather different, in that you boot up directly off the installation CD into a fully-functioning graphical O/S and then click the “Install” icon that is located on the desktop. The graphical installer presented to you at that point, though, is very similar to the text-based one you got in 5.10, and the answers you provide are very similar too. In either case, the important point is to perform a default Ubuntu install, let the installer wipe the entire hard disk and partition it as it likes, and create yourself as a non-root user. Nowhere does either install routine ask you about networking details or what packages should be installed, which certainly makes things very simple, but means the installation leaves your PC in a very Oracle-unfriendly state, and that needs fixing before you can proceed much further.
As I just mentioned, the default Ubuntu installation leaves a number of things to be fixed, principally the issue of DHCP and the installed packages.
First, to assign a fixed IP address instead of a DHCP-assigned one, click the System -> Administration -> Networking menu options. Choose the appropriate network interface from the first dialog, and then alter its connection settings. Assign it whatever fixed IP address is appropriate for your networking environment:
You should probably open a terminal (Applications -> Accessories -> Terminal) to make sure you can ping other machines on your network after you’ve made this change. You should also ensure you can ping your own machine by its proper name at this time:
hjr@vitellius:~$ ping vitellius
PING localhost.localdomain (127.0.0.1) 56(84) bytes of data.
64 bytes from localhost.localdomain (127.0.0.1): icmp_seq=1 ttl=64 time=2.39 ms
64 bytes from localhost.localdomain (127.0.0.1): icmp_seq=2 ttl=64 time=0.087 ms
When Oracle is configured, the ability to resolve the local machine name will be crucial, so it’s good to know that it’s resolvable now.
The default Ubuntu installation includes an impressively comprehensive set of packages, but they still don’t include everything that an Oracle installation will require inorder to be successful. To put that right, you will manually have to install a handful of extra packages using the apt-get utility. Before you can meaningfully use that tool, however, you need to make sure that universe apt repository is available for use.
So, at the command prompt, issue the following commands:
sudo cp /etc/apt/sources.list /etc/apt/sources.list_backup
sudo gedit /etc/apt/sources.list
The first command backs up the existing apt repository configuration file, and the second opens that configuration file in the gedit text editor. You need to find the two lines (roughly in the middle of the file) which refer to the universe repository, and uncomment them. Instead of this, therefore:
#deb http://au.archive.ubuntu.com/ubuntu breezy universe
#deb-src http://au.archive.ubuntu.com/ubuntu breezy universe
…we want this:
deb http://au.archive.ubuntu.com/ubuntu breezy universe
deb-src http://au.archive.ubuntu.com/ubuntu breezy universe
The actual URLs provided in your configuration will most probably different from these, of course… and they’ll mention “dapper” rather than “breezy” if you’re using version 6.06… but you get the general idea, I hope.
To then get the new repositories populated with information about what packages exist, you have to issue this command:
sudo apt-get update
The system will respond with this sort of thing:
Get:19 http://au.archive.ubuntu.com breezy-updates/main Sources [17.5kB]
Get:20 http://au.archive.ubuntu.com breezy-updates/restricted Sources [14B]
Fetched 4248kB in 49710d 0h50m20s (0B/s)
Reading package lists… Done
When all the package lists have been fetched, you can then actually install the necessary packages by issuing this command:
sudo apt-get install gcc libaio1 lesstif2 lesstif2-dev make rpm libc6 libstdc++5
Once again the system will respond with a lot of information, and an all-important question:
Reading package lists… Done
Building dependency tree… Done
libc6 is already the newest version.
The following extra packages will be installed:
binutils gcc-4.0 ldso libc5 libc6-dev libexpat1-dev libfontconfig1-dev libfreetype6-dev libice-dev
librpm4 libsm-dev libx11-dev libxau-dev libxext-dev libxft-dev libxi-dev libxrender-dev libxt-dev
linux-kernel-headers x-dev x11proto-core-dev x11proto-input-dev x11proto-kb-dev x11proto-render-dev
x11proto-xext-dev zlib1g-dev
Suggested packages:
binutils-doc manpages-dev autoconf automake1.9 libtool flex bison gcc-doc gcc-4.0-doc gcc-4.0-locales
libc6-dev-amd64 lib64gcc1 glibc-doc alien
Recommended packages:
libmudflap0-dev
The following NEW packages will be installed:
binutils gcc gcc-4.0 ldso lesstif2 lesstif2-dev libaio1 libc5 libc6-dev libdb1 libexpat1-dev
libfontconfig1-dev libfreetype6-dev libice-dev librpm4 libsm-dev libx11-dev libxau-dev libxext-dev
libxft-dev libxi-dev libxrender-dev libxt-dev linux-kernel-headers make rpm x-dev x11proto-core-dev
x11proto-input-dev x11proto-kb-dev x11proto-render-dev x11proto-xext-dev zlib1g-dev
0 upgraded, 33 newly installed, 0 to remove and 71 not upgraded.
Need to get 6000kB/12.2MB of archives.
After unpacking 47.4MB of additional disk space will be used.
Do you want to continue [Y/n]?
When you answer ‘Y” to that question at the end, the system will retrieve whatever packages it needs from the Internet, and will install them entirely automatically. At that point, your system has all that it requires to support an Oracle installation.
Note: if you are running apt-get behind a proxy server, you will need to set the http_proxy environment variable before issuing any of the apt-get commands shown here. Do that by issuing the command export http_proxy=http://192.168.1.254:80 -or whatever the correct IP address and port number should be for your particular environment.
We now have to prepare the Ubuntu machine for the Oracle installation in much the same way as you would any Linux server. In essence, that means: creating a user to own the installation; configuring kernel parameters; and configuring user environment variables. Taking those three things one at a time, therefore…
Somebody must own the Oracle installation, and we therefore have to create a new user account for that purpose. This account is generically referred to (in this document and most others you’ll ever read on the subject) as the oracle user. He is created at the command line like so:
sudo groupadd oinstall
sudo groupadd dba
sudo groupadd nobody
sudo useradd -m oracle -g oinstall -G dba -s /bin/bash
sudo passwd oracle
The first three commands create three new operating system groups that need to exist. The fourth command is the one that creates the oracle user and makes him a member of two of those new groups. Finally, you set a new password for the oracle user (and make sure you don’t forget what you set it to!)
So that you know, the oinstall group is the one which officially “owns” the Oracle installation, and which has rights to upgrade and patch it. The dba group is the one that has the rights to administer a database (starting it up, shutting it down, backing it up and recovering it when necessary). In many large organisations, the membership of the two groups would be quite distinct: SYSADMINs in the one, and DBAs in the other. However, in this case (and quite commonly done in the real world, too) the one user has been made a member of both groups, and will thus be able to perform both sorts of function. It could reasonably be argued that, if this is so, there was no point in creating both groups in the first place: one would have done. True, but it’s best practice to allow you to have as much flexibility for the future as you can, and therefore even if you choose (as here) not to use the two-groups-split right now, at least you have the chance to do so in the future.
Oracle instances consume memory and CPU resources on servers, and Ubuntu doesn’t come configured to allow the quantity of resources to be consumed that we’ll end up needing. To fix that, we therefore have to specify a set of new Kernerl Parameters which the operating system should use. That’s easily done by editing one file, like so:
sudo gedit /etc/sysctl.conf
You can substitute in the name of your preferred text editor instead of gedit if you like, but whatever editor you use, you now need to add these lines to the end of that file:
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
Don’t make any typing mistakes (it’s best to cut-and-paste from the above, really) and make sure you leave a blank line after the last line of the file (otherwise, the last setting tends not to get read and implemented at all). Normally, the sysctl.conf file is only read at each machine startup, so you’d have to reboot the server to implement the changes, but you can force the server to re-read the file without need for a reboot by now issuing the command:
sudo /sbin/sysctl -p
Finally, we need to set some new security limits for the system, too. Do that by issuing this command:
sudo vi /etc/security/limits.conf
..and then appending to the end of that file these new values:
* soft nproc 2047
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536
You now need to configure the environment settings for the oracle user, and set up some locations where the Oracle installation can actually take place. First, issue the following commands:
sudo ln -s /usr/bin/awk /bin/awk
sudo ln -s /usr/bin/rpm /bin/rpm
sudo ln -s /lib/libgcc_s.so.1 /lib/libgcc_s.so
sudo ln -s /usr/bin/basename /bin/basename
sudo mkdir /oracle
sudo mkdir /oracle/10g
sudo chown -R oracle:oinstall /oracle
sudo chmod -R 775 /oracle
The first four commands make the Ubuntu/Debian environment look a bit less alien to the Oracle installer, which is expecting a Red hat or Suse environment. The next two commands then create a directory structure into which the Oracle installation will take place. The official guidelines on this are that you should create quite a complicated path (along the lines of /u01/app/oracle), but that is really only needed for production servers that use Oracle’s official Optimal Flexible Architecture (OFA, for short). It’s certainly a good idea to use the OFA guidelines, but it’s not actually a requirement to do so, and home learning environments really have no need to do so. Which explains my very simple path of plain old /oracle/10g here, of course.
Finally, issue this command:
sudo gedit /etc/profile
Again, use whichever text editor you prefer, but whatever editor that turns out to be, add these lines to the end of the file:
export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/10g
export ORACLE_SID=orcl10
export PATH=$PATH:$ORACLE_HOME/bin
Make sure to leave a blank line after the last of those lines.
The ORACLE_BASE environment variable will tell the installer where the root of all possible installations can be. For me, that’s the /oracle directory, but if you were being more elaborate or following OFA guidelines, you’d specify whatever directory path you’d actually created.
The ORACLE_HOME environment variable tells the installer where, within the ORACLE_BASE, the specific installation is to be performed. For me, that’s the 10g subdirectory I earlier created within the /oracle directory. The official line from Oracle is that you neither need nor should set ORACLE_HOME when performing 10g installations, but I find the installer can propose rather long-winded ORACLE_HOMEs if left to its own devices (which again would not be a problem in a production setting, but is overkill for a home learning environment).
The ORACLE_SID environment variable is used to give Oracle instances their names, and also to identify which instance it is that you want to connect to when you don’t say explicitly. The point here is that I am shortly going to be creating a database called orcl10, and that’s therefore the reason I’ve set that value here. If you are planning on creating a database instead called SALES or products, then you’d use one of those values instead -and note, by the way, that like everything else in Linux, the thing is case-sensitive (so an instance called SALES is different and distinguishable from one called sales).
I finally modify the PATH environment variable for the oracle user so that he can find the Oracle executables -once they’re installed- without actually having to travel to the directory they’re stored in.
With all four of those values safely stored in the modified file, you should now become the oracle user for the first time and check that the values are actually being picked up and applied correctly:
hjr@vitellius:~$ su – oracle
Password:
oracle@vitellius:~$ echo $ORACLE_BASE
/oracle
oracle@vitellius:~$ echo $ORACLE_HOME
/oracle/10g
oracle@vitellius:~$ exit
logout
hjr@vitellius:~$
Note that I become the oracle user by including a minus sign in the su command. If I didn’t the environment variables would not be set correctly. In short, be careful how you become the oracle user: minus signs at all times! Note also that I’ve quit being the oracle user after this test: the exit command effectively logs off the oracle user and returns me to being me.
There are just two other things you now have to do. The first is to issue this command:
sudo gedit /etc/redhat-release
This will cause a completely empty file to appear in the editor, and you should paste the following text into it:
Red Hat Enterprise Linux AS release 3 (Taroon)
You can then save the modified file. This step is needed on any Linux platform that Oracle doesn’t officially certify, because the Oracle Installer is hard-coded to bomb out if it discovers it’s not running on a certified platform. The file is enough to persuade the installer that it’s running on Red Hat Enterprise Server version 3, though -and that is a certified platform, which means the instalaltion will be allowed to proceed. An alternative approach to fooling the installer is to invoke it with the switch ignoreSysPrereqs, and I’ve documented doing that in many of my other Oracle-on-Linux installation guides. Just to be different, I thought I’d use this method here, though!
The other thing you have to do before you can proceed is pretty obvious, if you think about it: obtain the Oracle software! You might already have an official CD containing the necessary software, of course, in which case feel free to install from that. In my case, though, I needed to download the software from the Oracle website. When you do that, make sure you pick the right version (that is, 10g Release 2 or Release 1 or even 9i) and the right O/S & architecture (that is, Windows, Linux, Solaris etc, and x86 versus 64-bit). This article uses 10g Release 2 Enteprise Edition for the Linux x86 architecture.
The download will be of a single file, called 10201_database_linux32.zip, and you should house it somewhere appropriate, and make it usable by the oracle user. For me, that meant downloading it to the /home/hjr directory, and then doing the following:
sudo chown oracle:oinstall /home/hjr/*.zip
sudo chmod 775 /home/hjr/*.zip
sudo mv /home/hjr/*.zip /home/oracle
With that done, I then became the oracle user, and unpacked the download:
su – oracle
unzip 10201_database_linux32.zip
rm 10201_database_linux32.zip
The unzipping operation creates a database subdirectory in which all the software is located, and it’s from there that the installation will be performed. There’s therefore no point in hanging on to the original zip file, and I therefore delete it. At this point, you’re finally ready to begin the Oracle installation.
The secret to any installation of Oracle on Linux is in getting the preparation right, and if you’ve been following this article carefully so far, you’ve already done that. So from this point on, everything should be pretty much plain sailing.To initiate the installation, you’ll have to log right out of the system, and log in directly as the oracle user (alternatively, you can fiddle around with DISPLAY environment variables, but logging off and back on again is simpler and less prone to making mistakes!).
Once you’re logged on as the oracle user, open a new terminal window and issue the command:
/home/oracle/database/runInstaller
After that, it’s just a question of not letting the installer create a database for you (that is a separate task and should be thought about and considered separately). Another Flash movie is available that shows me performing such an installation. Note that the movie shows two installation errors occurring. These were a ‘feature’ of an earlier version of this installation guide. They DO NOT happen with the current version, so you can safely skip past those two points in the movie. At the end of the installation process, you’ll want to create a database properly, and that’s the next thing this article will describe how to do. If you are running short on disk space and would like to get some back, then you can now delete the source files for the Oracle installation, since they won’t be needed any longer. In my case, that means I can delete the /home/oracle/database directory and all its contents.
It’s not a requirement to create a Listener before you create a database, but it makes sense to do so. A Listener is a process which listens on a well-known port for requests from remote users seeking to connect to the Oracle database. Without one, therefore, you’d only ever be able to connect to the database whilst directly logged onto the server itself, which is obviously a bit of a show-stopper!
To create a Listener, we use the Network Configuration Assistant. To invoke the Assistant, just issue the command netca as the oracle user in a new terminal session, and be prepared to click [Next] lots of times when the wizard pages finally appears! If you need rather more detailed instructions than that, there’s another Flash movie you can watch showing what to do at this point, and even though it happens to show an example of creating a Listener on a Windows XP machine, you’ll find the screens identical on Ubuntu.
You are now ready to create your new Oracle Database. We do so as the Oracle user, invoking the graphical tool Oracle now recommends as the preferred method of creating any Oracle database: the Database Configuration Assistant, or DBCA.for short. Just issue this command at the command line whist logged in as the oracle user:
dbca
This is not a difficult thing to do: mostly, in our case, it involves clicking [Next] to walk through the wizard, accepting all defaults. Just be sure to specify the correct database name (it should match what is set as your ORACLE_SID, but with a proper domain extension -so in my case, orcl10.dizwell.local is right). I suggest you also select the option to include the Sample Schemas and to create a Flash Recovery Area. For those of you who want to see a database actually being created, I’ve got yet another Flash movie you can watch that shows you the entire process.
Once you’ve created your database, there’s really only one more thing to worry about, and that’s how you arrange for the database to be opened automatically every time the server itself starts up, and for it to be closed cleanly every time the server shuts down.
Oracle themselves provide part of the answer in the form of two scripts called dbstart and dbshut, both found in the ORACLE_HOME/bin directory. Those scripts read another file called /etc/oratab to determine what databases actually need the automated startup and shutdown treatment. All you have to do, therefore, is write a script of your own that calls dbstart or dbshut, and then integrate this new script into Ubuntu’s standard runlevel/service control mechanism.
And incidentally, I might just point out that the 10g Release 2 dbstart script automatically takes care of starting the Listener, so there’s no need to include that as a separate step in your own script. I notice a number of Ubuntu/Oracle how-to’s on the web that have overlooked this basic point, and thus busily try and start Listeners that are already running!
Anyway… let’s break this operation down into its components steps.
You should log out as the oracle user, and log yourself back on as yourself (that is, the user you created during the Ubuntu install). That’s a full logout and logon, not merely the use of the su command to become another account, by the way.
Once you’ve done that, you should edit the contents of the /etc/oratab file in the text editor of your choice. You’ll have to have root privileges to edit a file in the /etc directory, of course, and that means it will be necessary to issue a command such as:
sudo gedit /etc/oratab
You’ll find at the moment that the file contains this one line (apart from all the commented-out ones, that is):
orcl10:/oracle/10g:N
The three parts of that line tell the system that you have an instance/database called orcl10 (your name might be different of course, but that’s what I’ve used all the way through this article); it’s ORACLE_HOME is /oracle/10g, so that’s where the executables to run the database can be found; and right now it’s Not to be automatically started or stopped. Well, from that description it’s pretty obvious, I hope, what you need do: that last ‘N’ has got to change to a ‘Y’, so you end up with the line of text reading:
orcl10:/oracle/10g:Y
Once you’ve made the change and saved the modified file, you can close down your text editor.
The two scripts supplied by Oracle don’t need much editing, but the little that they might is very important. First off, we’ll tackle the ORACLE_HOME/bin/dbstart script. Open the file in the text editor of your choice -and this time, you’ll have to become the oracle user to be able to make the required edits, so the command would be:
su – oracle
vi $ORACLE_HOME/bin/dbstart
Towards the beginning of that file, you’ll find these lines:
# Set this to bring up Oracle Net Listener
ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle
if [ ! $ORACLE_HOME_LISTNER ] ; then
echo “ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener”
else
LOG=$ORACLE_HOME_LISTNER/listener.log
I’ve said this on other occasions, but it remains true: whoever was responsible for writing this stuff at Oracle deserves to hang his head in shame. Not only has a completely non-standard location for an ORACLE_HOME been hard-coded into the script ( he might at least have assumed a default installation’s location!), but he couldn’t even spell the word LISTENER correctly! Yes, it has an ‘E’ in it!
But I digress: what you need to do here is change the path assigned to the ORACLE_HOME_LISTNER variable to whatever path is assigned to your ORACLE_HOME environment variable. In my case, that means changing the all-important line so that it reads:
ORACLE_HOME_LISTNER=/oracle/10g
The subtle point going on here is that you might have a server running many different versions of Oracle, but using only one Listener. A 10g Listener can listen on behalf of 9i databases, for example. So the place where the Listener’s own executables should be found might be different from where the database’s own executables are found -and that’s why the two things are separately configurable. In my case, though, I’ve only got one database, and it’s the same version as my Listener, so one’s home is the other’s, too -and that’s why I set my ORACLE_HOME_LISTNER to the same value as my ORACLE_HOME.
So much for dbstart. The second script, ORACLE_HOME/bin/dbshut might also need editing in a production environment, but we probably won’t need to change it for the purposes of this article. By all means open it up in a text editor and have a look at it: you’ll find when you do that the script issues shutdown immediate commands. A shutdown immediate, however, is a bit brutal: no matter what anyone is doing, they are all summarily kicked off the system, and whatever work they were in the middle of is thus lost. Cautious DBAs who don’t enjoy upsetting their users might well want to consider altering the script to issue shutdown transactional or shutdown normal commands instead, since these both allow users to finish what they are doing before the database closes (with the unfortunate side-effect that if a user refuses to finish what they are doing, the database never closes!)
You now need to write a script which itself calls the dbshut and dbstart scripts. This script is usually stored in the /etc/init.d directory (and thus will require root privileges to create) and gets called dbora (though it can actually be called anything you like. The script should look like this:
#!/bin/bash
#
# /etc/init.d/dbora
#
# Startup script for Oracle databases
export ORACLE_HOME=/oracle/10g
export ORACLE_SID=orcl10
export PATH=$PATH:$ORACLE_HOME/bin
case “$1″ in
start)
echo -n “Starting Oracle: ”
su oracle -c $ORACLE_HOME/bin/dbstart
touch /var/lock/oracle
su oracle -c “$ORACLE_HOME/bin/emctl start dbconsole”
echo “OK”
;;
stop)
echo -n “Shutdown Oracle: ”
su oracle -c $ORACLE_HOME/bin/dbshut
rm -f /var/lock/oracle
echo “OK”
;;
*)
echo “Usage: ‘basename $0′ start|stop”
exit 1
esac
exit 0
Essentially, the script creates two ‘procedures’, one called “start” and one called “stop”. The start procedure calls the dbstart script as the oracle user, whilst the stop procedure calls the dbshut script as the oracle user. I again draw to your attention the fact that nowhere in the script do I attempt to start the Listener -for the simple reason that in 10g, dbstart does that for us anyway. So all you now have to do is run a text editor with sudo privileges, cut out that sample script and paste it into the text editor, and save it as a file called /etc/init.d/dbora. Once that’s done, you need to make the file executable:
sudo chmod 775 /etc/init.d/dbora
You then need to link the new calling script into the runlevel scripts that Ubuntu uses to control general service startup and shutdown. That can be done by issuing the one command:
sudo update-rc.d dbora defaults 99
If you now reboot your server, you should find that immediately it comes back up, you can become the oracle user and connect to the database via SQL*Plus without any hassles:
hjr@vitellius:~$ su – oracle
Password:
oracle@vitellius:~$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Mar 16 14:25:57 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
Had the thing not been started correctly, however, I would have seen something more like this:
oracle@vitellius:~$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Mar 16 14:29:06 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
So, provided you don’t get the ‘idle instance’ message after a server reboot, the automation scripts are working as intended!
The last thing I want to do in this article is to show you how to get the primary GUI database administration tool, called Enterprise Manager, running. I’m not going to show you much more than that, though: Enteprrise Manager is so capable a tool that it would take many articles of their own to discuss even a fraction of its functionality.
The first thing you’ll need to do before you can actually use Enterprise Manager is get its management agent running. This involves another trip to the command line as the oracle user:
hjr@vitellius:~$ su – oracle
Password:
oracle@vitellius:~$ emctl start dbconsole
TZ set to Australia/Sydney
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ……………. started.
——————————————————————
Logs are generated in directory /oracle/10g/localhost.localdomain_orcl10/sysman/log
The command takes quite a while to complete (at least a minute or two), so have patience. Incidentally, there’s a emctl stop dbconsole command to shut the thing down again if you ever feel it necessary.
Once the Management Agent is running, it’s time to open a standard web browser (Firefox is shipped by default with Ubuntu and will do nicely, though it’s not an ‘officially endorsed’ broswer (Internet Explorer, Netscape and Mozilla are the only ones that qualify on that score!). The URL or address you want is simply http://your-machine-name:1158/em. In my case, for example, I’d type http://vitellius:1158/em.
If you’re using a proxy server, your browser might do what mine did the first time I tried this -which was to take a trip out onto the Internet to try and find a website called vitellius.com! If that’s the case, just use the Edit -> Preferences -> Connection Settings dialog in Firefox to specify that no proxy should be used for URLs containing your machine name. In my case, that means my ‘No proxy for’ window reads: localhost, 127.0.0.1, vitellius -and that was enough to fix the problem.
If all is working as intended, you should be rewarded with this:
You should log on as SYS there, with whatever password you assigned to that account (and the other in-built ones) when you were creating the database. You must also specify the SYSDBA privilege in the last drop-down box, because SYS can only log onto the database by default when claiming that privilege. Once you click the Login button, you will be asked to sign your life away as you agree to the rather onerous licensing conditions that afflicts Enterprise Manager. In a production environment, pause for thought at that point and contact your Oracle Sales represenative to complain about the licensing terms. But at home, just while you’re learning, feel free to agree to anything that gets the software working!
Finally, you’ll get to this sort of thing:
If you see the various bar charts as you see them here, then at least Enterprise Manager is working as advertised (though if your bar charts similarly show CPU consumption at the 100% mark and the big, brown waits as large as I’ve got them, you’ve got a performance tuning issue on your hands!)
At this point, all I can tell you is that most of the routine administration tasks can be found by clicking the Administration link towards the top of the page; backup and recovery options can be found under the Maintenance link; and performance tuning diagnostics can be found under (surprise, surprise!) the Performance link. More than that, I don’t want to say because I’ll be here until Christmas if I start! Just click around and experiment: the joys of a home learning environment is that you can’t do too much damage whatever you click, and what damage you do inadvertently inflict will be part of a useful learning experience! So have fun!
I can’t say that I love Ubuntu, though I don’t intend by that to mean that I think it in anyway a poor distro. It’s not: it’s robust, easy to install, painless to configure …and I very much like its choice of default display font! But it’s not Red Hat, or Suse and when you’re trying to install Oracle, it shows: you do things (create sympbolic links and issue lots of sudo commands, basically) which are very much non-standard. Clearly, given where we got to at the end of this article, such differences are mostly superficial and make no difference to the outcome. But it still leaves me feeling vaguely uncomfortable, and I can’t therefore recommend using Ubuntu as your Oracle platform.
So, whilst I can understand the desire to run Ubuntu as your distro of choice (it’s good-looking, works well and appears to have a good future ahead of it), I’d much rather you ran VMware on your Ubuntu host (it’s entirely free these days, you know!) and ran Oracle in a virtual machine where Red Hat (or one of its free clones) was installed. That would be the much more realistic -in the sense of more like what you’d find in a production server room- way of running Oracle on Ubuntu (but admittedly it would be slower!).
But I don’t want to end on a depressing note: just bear in mind that by the end of this article I was able to select * from scott.emp natively on Ubuntu, and can get tangled up in the Enterprise Manager licensing terms just as effectively on Ubuntu as I can on any other distro! Oracle on Ubuntu is possible, therefore, if you want it badly enough! And my suspicion is that there are an awful lot of committed Ubuntu users out there who do want it badly enough… so I hope I’ve made them happy!
科技新聞 – 頭條新聞 – Yahoo!奇摩新聞
最新的回應