Thus Spoke Herman

I had some difficulty in getting WireShark to recognise any of the interfaces on my machine. A quick net search led to a rather longer one, but revealed that my problems were because of permissions on the devices themselves for my user. I am sorry to the person who posted this result originally, I cannot remember which site I got it off:

sudo chown <user> /dev/bpf*

This allows wireshark to see the connections. It doesn’t require a wireshark restart either.

In my time trawling through various systems’s DB code, I see we are using “select *” in many places. This has to stop, and we need to specify the columns we want explicitly every time. The reasons for this are:

- A change to the table structure can break the code
- We end up getting all the columns from the DB when we almost certainly don’t need to. Some of the columns can be enormous, and it is a waster of DB and network resource in getting this data.
- A change to the query can add many more columns to be returned.
- It can make a difference to the query plan the DB uses, and can be many orders of magnitude faster to specify columns individually.

Using “select *” is lazy, suboptimal and a coding time bomb.

Just a quick one today…

I have an XP installation running in Parallels and have found some problems using ftp from within it.

FTP is a strange protocol in that it creates two way connections on a number of ports. Even using the FTP variants which initialise all the connections from the client side (passive) I was having trouble using it successfully from within Parallels. I tried host-based and shared networking. What did work was using Bridged-Mode and this allowed the dhcp server on my network to assign the address to the Parallels VM and this then allowed the ftp client to successfully connect to the ftp server and upload files. The other 2 modes do not allow the ftp client to complete the strangeness of ftp properly.

In previous OEM posts I have been dropping the Repository in a variety of ways. In a moment of Instruction Reading, something I rarely do because I love to conform to stereotypes, I found this little obvious-when-you-know beauty:

$ORACLE_HOME/sysman/admin/emdrep/bin/RepManager

[oracle@bbmos01 ~]$ $ORACLE_HOME/sysman/admin/emdrep/bin/RepManager $HOST $PORT $SID -action drop
Enter SYS user’s password :
Enter repository user name : SYSMAN
Enter repository user password :
Getting temporary tablespace from database…
Found temporary tablespace: TEMP
Checking SYS Credentials … OK.
Dropping the repository..
Quiescing DB … Done.
Checking for Repos User … Exists.
Repos User exists..
Clearing EM Contexts … OK.
Dropping EM users …
Done.
Dropping Repos User … Done.
Dropping Roles/Synonymns/Tablespaces … Done.
Unquiescing DB … Done.
Dropped Repository Successfully.

Ho hum. It can do it all.

If you ever have the misfortune of downgrading an Oracle server, you can run into some silly problems. One such issue raised its head when I needed to downgrade a server for a different project. I downgraded a 10.2.0.3 server to 10.1.0.5 and had a 10.2.0.3 OEM repository already installed. I exported all the schemas and reimported once I had downgraded.

I started the DB console and everything seemed fine, but when browsing to the OEM, I logged on and got a screen showing that it had not connected to the instance. A quick look at the logs followed. You can see the following errors in the emagent.trc file:

2008-05-21 00:57:47 Thread-4120902576 ERROR upload: Failed to upload file A0000001.xml, ret = -2
2008-05-21 00:57:47 Thread-4120902576 WARN upload: FxferSend: received http error in header from repository: http://oracle.oakdene.net:5500/em/upload/
ERROR-400|ORA-06550: line 1, column 18:
PLS-00302: component ‘OBTAIN_LOCK_FOR_EMD_URL’ must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I decided that I would accept not having the old repository data for now, I could always import into another schema and do a post fix data import. I needed to drop the repository and recreate the 10.1.0.5 version (theemca commands are different between 10.2 and 10.1).

[oracle@oracle ~]$ emca -x OAKDENE_SID

STARTED EMCA at Wed May 21 01:54:52 PDT 2008
May 21, 2008 1:54:52 AM oracle.sysman.emcp.EMConfig stopOMS
INFO: Stopping the DBConsole …
Enterprise Manager configuration is completed successfully
FINISHED EMCA at Wed May 21 01:54:57 PDT 2008

I then tried installing the db console. I used a response file called emca.txt. This didn’t work properly as can be seen below:

[oracle@oracle ~]$ emca -f emca.txt

STARTED EMCA at Wed May 21 01:55:24 PDT 2008
Password for dbsnmp:
Password for sysman:
Password for sys:
May 21, 2008 1:55:32 AM oracle.sysman.emcp.EMConfig checkConfiguration SEVERE: Repository already exists. Fix the error(s) and run EM Configuration Assistant again in standalone mode. Could not complete the configuration. Refer to the log file for details

I decided to drop the repository manually by dropping the SYSMAN user:

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Wed May 21 01:55:42 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> drop user sysman cascade;

User dropped.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@oracle ~]$ emca -f emca.txt

STARTED EMCA at Wed May 21 02:01:35 PDT 2008
Password for dbsnmp: Password for sysman: Password for sys:
May 21, 2008 2:01:43 AM oracle.sysman.emcp.EMConfig updateReposVars
INFO: Updating file /home/oracle/oracle10db/sysman/emdrep/config/repository.variables …
May 21, 2008 2:01:48 AM oracle.sysman.emcp.EMConfig perform
SEVERE: Error creating the repository. Repository creation could not be completed successfully
May 21, 2008 2:01:48 AM oracle.sysman.emcp.EMConfig perform
INFO: Please refer to the log file at /home/oracle/oracle10db/cfgtoollogs/emca_repos_create<DATE>.log for more details.
Could not complete the configuration. Refer to the log file for details
[oracle@oracle ~]$ vi /home/oracle/oracle10db/cfgtoollogs/emca_repos_create2008-05-21_02-01-43-AM.log

But it still failed. In the log mentioned here is is quite obvious what has happened:

CREATE ROLE MGMT_USER
*
ERROR at line 1:
ORA-01921: role name ‘MGMT_USER’ conflicts with another user or role name

After fixing this issue (and others) by repeating, there are a series of other objects which need dropping as well:

[oracle@oracle ~]$ cat drop_sysman.sql

drop public synonym SETEMVIEWUSERCONTEXT;
drop role mgmt_user;
drop public synonym mgmt_target_blackouts;
drop user sysman cascade;
drop user mgmt_view cascade;
exit

[oracle@oracle ~]$ sqlplus ‘/ as sysdba’ @drop_sysman

SQL*Plus: Release 10.1.0.5.0 - Production on Wed May 21 02:02:16 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Synonym dropped.

Role dropped.

Synonym dropped.

User dropped.

User dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

And this works now, the repository gets create properly:

[oracle@oracle ~]$ emca -f emca.txt

STARTED EMCA at Wed May 21 02:24:32 PDT 2008
Password for dbsnmp: Password for sysman: Password for sys:
May 21, 2008 2:24:42 AM oracle.sysman.emcp.EMConfig updateReposVars
INFO: Updating file /home/oracle/oracle10db/sysman/emdrep/config/repository.variables …
May 21, 2008 2:26:18 AM oracle.sysman.emcp.EMConfig createRepository
INFO: Creating repository …
May 21, 2008 2:26:18 AM oracle.sysman.emcp.EMConfig perform
INFO: Repository was created successfully
May 21, 2008 2:26:19 AM oracle.sysman.emcp.EMConfig addPortEntries
INFO: Updating file /home/oracle/oracle10db/install/portlist.ini …
May 21, 2008 2:26:19 AM oracle.sysman.emcp.EMConfig updateEmdProps
INFO: Updating file /home/oracle/oracle10db/sysman/config/emd.properties …
May 21, 2008 2:26:19 AM oracle.sysman.emcp.EMConfig updateConfigFiles
INFO: targets.xml file is updated successfully
May 21, 2008 2:26:19 AM oracle.sysman.emcp.EMConfig updateEmomsProps
INFO: Updating file /home/oracle/oracle10db/sysman/config/emoms.properties …
May 21, 2008 2:26:19 AM oracle.sysman.emcp.EMConfig updateConfigFiles
INFO: emoms.properties file is updated successfully
May 21, 2008 2:26:20 AM oracle.sysman.emcp.EMConfig startOMS
INFO: Starting the DBConsole …
May 21, 2008 2:27:54 AM oracle.sysman.emcp.EMConfig perform
INFO: DBConsole is started successfully
May 21, 2008 2:27:54 AM oracle.sysman.emcp.EMConfig perform
INFO: >>>>>>>>>>> The Enterprise Manager URL is http://oracle.oakdene.net:5500/em <<<<<<<<<<<
Enterprise Manager configuration is completed successfully
FINISHED EMCA at Wed May 21 02:27:54 PDT 2008

Perseverance is the key :).