Archive for the ‘Microsoft Vista’ Category
Manual Oracle Service
Ruairi asked how you could disable automatic start of the Oracle Service for Oracle 11g on Windows 7 (a comment here). Ruairi also provided a nice Windows shell script that you can copy for starting and stopping the Oracle Service in his last comment.
The simplest way is to launch a command shell because I don’t want to provide all the navigation variations for different Windows versions.
Basically, you do that by clicking the Windows Start button and type cmd
word in the run entry box. This launches a command session. Type the following from the prompt. It launches the Windows Services console in all relevant versions:
C:\> services.msc |
Now you’ll see the Windows Services console. Navigate to the Oracle Service and right click on it. You choose Properties.
That will bring you to this screen. Click on the drop down for the Startup type and choose Manual. Click the OK button to complete the step. That’s it, the next time you start the machine the Oracle database won’t start automatically. You should do the same to the other Oracle Services.
If you don’t have a lot of memory and it’s a development machine, this makes a lot of sense.
iPad Thoughts …
This is probably defensive because I’ve had to answer the question about two dozen times since the iPad product announcement. The question is naturally, what do yo think about the iPad?
My perspective is biased by the fact that I’ve been using both DOS/Windows PCs and Macs since the 1980s. They each have merits but in short, unlike the media, I have a bias toward Apple products. In fact, I’m an old NeXT system administrator (software gone from the scene because as rumors have it, Steve wouldn’t think of letting the company become ONLY a software company).
I think the idea of the iPad for eBooks is awesome, the features are terrific. It clearly is a better opportunity for my digital movies but a bit awkward because of its size.
I can’t travel with an iPad by itself because it doesn’t support Microsoft Excel, Word, or Visio. That means I’d have to have my MacBook Pro and iPad. Ouch, the security folks will go nuts at the airport, and my bags are now heavier by about 2 pounds. The iPad is 1.5 pounds but the charger has weight too.
I understand all the logic for the device but there’s an underlying assumption in placing everything on the web. Some data can’t be on the web because of legal limits. This goes to my sticking point. Apple’s Office Suite isn’t as robust as Microsoft’s Office Suite. Keynote is easier to use and easily preferred over PowerPoint, but Numbers isn’t even close to Excel (here Apple fails). The problem with Pages is that many companies have templates built around Word and there’s no easy migration back and forth.
Perhaps Apple will reach out to Power Excel users and invest in Numbers to bring it into this millennium; and maybe they’ll also fix the portability between Word and Pages too. For example, one company I work with insists that I use Word 2003 because they’ve never updated their templates to Word 2007 (easy to do through VMWare Fusion). Then, all that’s needed is a rock solid replacement for Visio on Mac OS X.
I think that I might buy one to experiment with, just so I’m current with the product and new features. I’ve also got some product ideas that I’d like to explore but I don’t think this is a home-run like the iPod and iPhone without vitualization software to enable Windows. As an afterthought, maybe the announcement this summer will be “you can have it all now” when they port most features to the core OS X operating system. That would induce me to upgrade my MacBook Pro, wouldn’t it get you to do so too?
Black Screen of Death
Holiday Gift from Microsoft
Windows 7 ships. Then, we find it’s really Windows Vista+ (code base 6.1). Now, Microsoft give us a late year present, the Microsoft Black Screen of Death (their original post has been wiped by agreement with Microsoft it appears but only the shadow knows and rumors on the web). What better excuse to rush out and buy a MacBook Pro or use this fix?
Holiday Gift from Apple
Buying a MacBook Pro makes sense if you don’t already have one. Owning one, I’m hesitant to upgrade my MacBook Pro because the battery in the new one requires a service call when the battery wears out. An expensive item because the battery life is generally poor after 1,000 recharge cycles and that number of recharges may or may not occur before your Applecare service contract runs out.
Battery life/replacement is one of the reasons why I’ve stayed on my old MacBook Pro (purchased 16 months ago). The other reason is that I plan on getting by with a MacBook Pro for 4 to 5 years not Apple’s apparent plannned obsolescence of 3 years. While I’m in the gripe mode, the new Apple Cinema Display is attractive but not compatible with older MacBook Pro or MacBook computers. Also, the Altona DVI to Mini Display Port has mixed reviews out there and Apple seems disinterested in helping owners of older machines use the new Cinema screens. There aren’t any other alternatives to the Altona product (at least that I’ve found). I almost feel that somebody at Apple watched the movie Robots too often because it seems my 16 month old MacBook Pro is an outmode and there isn’t an upgrade option (only a new purchase).
Hiding MySQL Password
I always tell my students how to protect their Oracle credentials but hesitated to do the same for MySQL because it involves Windows batch shell scripting (albeit simple one line scripting). Oracle is nice and clean about hiding credentials. If you only provide the USER
name at the command prompt, then your PASSWORD
is never displayed. Only the user name is displayed in the DOS command prompt session’s window frame. You do that in Oracle with a student
user name, as follows:
C:\Data\Oracle> sqlplus student SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 21 09:20:25 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. Enter password: |
MySQL isn’t quite so convenient as a command-line tool. You must enter the -u
or --user
followed by the user name and -p
or --password
followed by the password when connecting to the database. For example, this shows a typical command prompt window frame:
As noted by Stephen in his comment, you can also mimic Oracle by using the following syntax:
C:\Data\MySQL> mysql -ustudent -p Enter password: |
Unless you use that syntax, anybody walking up can see your USER
and PASSWORD
in clear text. Not exactly the best security practice, eh? You can also suppress the USER
name from displaying by writing a small batch file with your text editor or at the command-line, like this:
C:\Data\MySQL>COPY CON mysqlcmd.bat @mysql -ustudent -p ^Z |
While you’ll need to enter your password because you really don’t want to store that in a file. The @
symbol suppresses echo of the command, which means you won’t display your USER
name or PASSWORD
when you start the mysql
client tool. It will only display the batch file name and the welcome message:
Hope this helps you when you’re working at the MySQL command-line. However, I’m often stunned by how few use it. They prefer phpMyAdmin or Quest’s Toad for MySQL.
Watch the Event Logs
It’s the end of our Spring term, and yes occasionally somebody can’t sign on to their Oracle instance because their event log is full. They get the following message on Winodws:
C:\>sqlplus / AS sysdba SQL*Plus: Release 11.1.0.7.0 - Production ON Wed Jul 15 10:19:37 2009 Copyright (c) 1982, 2008, Oracle. ALL rights reserved. Enter password: ERROR: ORA-28056: Writing audit records TO Windows Event Log failed |
The fix is simple, just delete your items from your Windows’ event log. 😉
Toad for MySQL Freeware
While SQL Developer is a nice tool and free, Toad is still an awesome development platform, albeit for Windows. In fact, it was annoying to have to install the Microsoft .NET Framework before installing it. It is free for MySQL!
Since my students have to do all their work in Oracle and then port it to MySQL, I demonstrate Quest’s Toad for MySQL’s at the end of the term. I don’t want them to leverage the automatic ERD diagramming while they’re learning how to do it.
There’s only one real trick to making automatic ERD diagramming work. That trick requires that you write your loading scripts for the Inno DB and use referential integrity constraints. My sample Video Store scripts for my database class are updated for MySQL referential integrity.
Unlike the friendly CASCADE CONSTRAINTS
clause you can use in Oracle, MySQL won’t let you create a re-runnable script with only DDL statements. Actually, the constraint comes from the InnoDB engine. You must issue a specialized InnoDB command before running your script:
11 12 13 | -- This enables dropping tables with foreign key dependencies. -- It is specific to the InnoDB Engine. SET FOREIGN_KEY_CHECKS = 0; |
Primary keys are a bit different from Oracle and it appears you can’t name them, at least I couldn’t see how to do it. Here’s an example of primary and foreign key constraints in MySQL. The primary key is inline and the foreign key constraints are out of line. This example from the downloadable scripts uses self referencing foreign key constraints.
24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | CREATE TABLE system_user ( system_user_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , system_user_name CHAR(20) NOT NULL , system_user_group_id INT UNSIGNED NOT NULL , system_user_type INT UNSIGNED NOT NULL , first_name CHAR(20) , middle_name CHAR(20) , last_name CHAR(20) , created_by INT UNSIGNED NOT NULL , creation_date DATE NOT NULL , last_updated_by INT UNSIGNED NOT NULL , last_update_date DATE NOT NULL , KEY system_user_fk1 (created_by) , CONSTRAINT system_user_fk1 FOREIGN KEY (created_by) REFERENCES system_user (system_user_id) , KEY system_user_fk2 (last_updated_by) , CONSTRAINT system_user_fk2 FOREIGN KEY (last_updated_by) REFERENCES system_user (system_user_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
Once installed (instructions are here) and connected to the MySQL database, you simply click the ERD icon in the top panel and drag the tables onto the canvas. You’ll see something like this (by the way click on the image to see its full size):
Have fun with it. It’ll be interesting to see how Oracle positions MySQL when they own it. My hunch is that they’ll continue to sell it and provide it as an open source product.
Configuring Zend Server
I got all the screen shots out last night, then I realized that the configuration instructions were missing. They’ve been added now, and you can check if you’re interested.
In the process, I noticed that Zend Server Community Edition is using connection pooling by default with Oracle. This means that the TNS alias must be qualified by a hostname
.
For example, on your local PC with Oracle Database 10g Express Edition, the default TNS alias is XE
. You can’t use that anymore when you’re running the Zend Server. You must qualify it as localhost/XE
or hostname/XE
as the connection string. A natural alternative is to insert a fully qualified TNS network map
Here are three possible connection patterns:
Localhost Connection ↓
This shows you how to connect with localhost/XE
.
1 2 3 4 5 6 7 8 9 10 11 12 | <?php // Attempt to connect to your database. $c = @oci_connect("student", "student", "localhost/xe"); if (!$c) { print "Sorry! The connection to the database failed. Please try again later."; die(); } else { print "Congrats! You've connected to an Oracle database!"; oci_close($c); } ?> |
If you connect with a localhost
string, your listener logs will show the following:
02-JUL-2009 23:32:11 * (CONNECT_DATA=(SERVICE_NAME=xe)(CID=(PROGRAM=C:\Program?Files\Zend\ZendServer\bin\php-cgi.exe)(HOST=MCLAUGHLINXP32)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1405)) * establish * xe * 0 |
The two things to point out with this are: (1) The host
is 127.0.0.1
; and (2) The TNS alias is lowercase.
Hostname Connection ↓
This shows you how to connect with hostname/XE
.
1 2 3 4 5 6 7 8 9 10 11 12 | <?php // Attempt to connect to your database. $c = @oci_connect("student", "student", "hostname/xe"); if (!$c) { print "Sorry! The connection to the database failed. Please try again later."; die(); } else { print "Congrats! You've connected to an Oracle database!"; oci_close($c); } ?> |
If you connect with a hostname
string, your listener logs will show the following:
02-JUL-2009 23:29:16 * (CONNECT_DATA=(SERVICE_NAME=xe)(CID=(PROGRAM=C:\Program?Files\Zend\ZendServer\bin\php-cgi.exe)(HOST=MCLAUGHLINXP32)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.153.138)(PORT=1403)) * establish * xe * 0 |
The two things to point out with this are: (1) the host
is the real IP address on the network; and (2) the TNS alias is lowercase.
Overriding TNS Connection ↓
This shows you how to connect with an overriding TNS connection.
Before you adopt this style, you may want to set a %TNS_ADMIN%
for your Windows OS. The suggestion is made since you may be running the client software and there’s a connection problem. You can click on the Setup a TNS_ADMIN Environment Variable menu to get at the details.
Set a TNS_ADMIN Environment Variable ↓
You can also set a %TNS_ADMIN%
environment variable, by taking the following steps.
- Open your System icon from the traditional Control Panel. Inside, click on the Advanced tab. Click the Environment Variable tab.
- The bottom window is where you set System variables. Click the New button to add a %TNS_ADMIN% variable.
- The New System Variable Window lets you enter the variable. Unless you’ve placed your
tnsnames.ora
in a different location, you find the file in the ORACLE_HOME\network\admin. If you’ve installed Oracle XE and Oracle on the same machine, you should know which contains both values because you entered them. The utilities don’t do that. If somebody has a question on that, let me know and I’ll put instructions out.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <?php $tns = "(DESCRIPTION = (ADDRESS=(PROTOCOL = TCP)(HOST = mclaughlinxp32)(PORT = 1521)) (CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = XE)))"; // Attempt to connect to your database. $c = @oci_connect("student", "student", $tns); if (!$c) { print "Sorry! The connection to the database failed. Please try again later."; die(); } else { print "Congrats! You've connected to an Oracle database!"; oci_close($c); } ?> |
If you connect with an overriding TNS connection, your listener logs will show the following:
02-JUL-2009 23:27:10 * (CONNECT_DATA=(SERVICE_NAME=XE)(CID=(PROGRAM=C:\Program?Files\Zend\ZendServer\bin\php-cgi.exe)(HOST=MCLAUGHLINXP32)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.153.138)(PORT=1401)) * establish * XE * 0 |
The two things to point out with this are: (1) the host
is the real network IP address; and (2) the TNS alias is uppercase, which is consistent with the TNS connection string.
I’m sure it was in the documents but then again, I didn’t read them. 😉
MySQL MSI Service Fails
While installing the MySQL 6.0 Alpha release, I encountered a failure running the configuration component. It shows the following dialog, which hangs until you cancel it. By the way, I’ve encountered this on other MySQL 5.0 and 5.1 installs from time to time.
Don’t uninstall and reinstall because nothing will change. The only problem appears to be setting the root password. This show you how to verify it and fix the missing configuration step. While the service says it failed, it actually started. You can check that by launching services.msc
from Start and Run.
You can verify the problem by attempting to connect to the MySQL server. My server is setup on localhost with port 3308 because there are multiple MySQL servers running on my virtual machine. A typically connection would look like this if your password was cangetin (the old Solaris training password):
C:\>mysql -uroot -pcangetin -P3308 |
If you get the following error message, it’s most likely a missing root password.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) |
Since my machine is running multiple MySQL servers and it’s my preference to associate their execution to their binaries, the paths to the installations aren’t loaded automatically on installation. A quick caution, my path statements are from the Windows XP 64-bit installation and they’ll differ from a 32-bit installation path. Specifically, the executable programs are in C:\Program Files (x86)
directory not C:\Program Files
. You can set the path like this:
C:\>set PATH=C:\Program Files (x86)\MySQL 6.0\MySQL Server 6.0\bin;%PATH% |
To verify and fix the problem requires you login without a password, connect to the mysql
database, and query the user
table. All those steps follow below, unless you’re on Microsoft Vista. If you’re running Microsoft Vista follow these instructions.
C:\>mysql -uroot -P3308 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 6.0.10-alpha-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> USE mysql; Database changed mysql> SELECT host, user, passowrd -> FROM user WHERE user='root' AND host='localhost'\G *************************** 1. row *************************** Host: localhost User: root Password: 1 row in set (0.00 sec) |
You fix this problem by running the following grant of privileges to the root
user:
mysql> GRANT ALL ON *.* TO 'root'@'localhost' -> IDENTIFIED BY 'cangetin' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) |
I learned this technique by attending the MySQL for Database Administrator’s course. I hope it solves a mystery for somebody along the way. I also hope that Oracle Education maintains the excellent folks that Sun Microsystems acquired when they snagged MySQL.
VMWare and PowerShell
There is a nasty little surprise Microsoft PowerShell can deliver when you try to install it in a virtual machine. You can’t do it if you’ve built the VMWare Fusion instance with the default SCSI hard drive option.
It appears that Microsoft PowerShell, a prerequisite for Microsoft SQL Server 2008 Express edition, can’t work with the SCSI driver. At least, when I rebuilt the Microsoft Vista instance with an IDE hard drive it worked.
If anybody knows the details of why it fails, please share it with me and other readers.
Another tidbit about installing Microsoft SQL Server 2008 Express without having installed the .NET Development Suite, you must patch it. These were required on Microsoft Vista (32-bit) before you could successfully install SQL Server 2008 Express Edition.
1. Microsoft .Net Framework 3.5 SP1 (even if you’re more current).
2. Microsoft PluginInstall (verifies you’ve a licensed copy).
3. Microsoft Installer 4.5.
4. Microsoft Windows PowerShell 1.0.
The list of files for the installation on Windows Vista 32-bit are:
The installation screen shots from my earlier install are here. You must also import the correct certificate. A pre-installation Microsoft .NET Application Security warning will stop the installation completely. If you click the warning, you’ll see this message:
Enter the URL in your browser and you’ll get the following if it was successful.
I choose not to enable automatic updates because it often runs at awkward times. Ultimately, the installation worked fine.
EMCA drops SYSMAN
I noted a bug in the emca
utility in this earlier blog on reconfiguring OEM. It manifest itself in Oracle 11g (11.1.0.6) on the Windows platform. Testing found that it’s fixed in the new Oracle 11g (11.1.0.7). This means you no longer have to manually drop the SYSMAN
schema because the following command does that now, like it should.
C:\Data> emca -deconfig dbcontrol db -repos DROP |