INFOR-Conseils / RemoteDBA
The Best DBA in Town!
2013-07-26
Could You Live Like a DBA?
2007-12-04
The SQL / PLSQL developper mantra...
I have a pretty simple mantra when it comes to developing database software, and I have written this many times over the years:
- You should do it in a single SQL statement if at all possible.
- If you cannot do it in a single SQL statement, do it in PL/SQL.
- If you cannot do it in PL/SQL, try a Java stored procedure.
- If you cannot do it in Java, do it in a C external procedure.
- If you cannot do it in a C external procedure, you might want to seriously think about why it is you need to do it.
If you can do it in a single SQL statement, by all means do it in a single SQL statement. Do not waste time, energy, and CPU cycles writing procedural code that will run slower than regular SQL.
2007-10-04
Oracle 10g R2 RAC & iScsi on Windows Server 2003
Oracle Agent 10g and multiple Oracle Inventories
exactly at here
There, we find "14.2 Enabling Multi-Inventory Support for Configuration Management"
Every time you install an Oracle software product on a host computer, Oracle Universal Installer saves information about the software installation on your hard disk. The directories and files that contain this software configuration information are referred to as the Oracle Universal Installer inventory.
When you use Enterprise Manager to monitor your Oracle software installations, Enterprise Manager takes advantage of information saved in the Universal Installer inventory.
As it gathers information about the configuration of your host computer, by default, Enterprise Manager assumes that you have one Oracle Universal Installer inventory on the host. Specifically, Enterprise Manager recognizes the inventory that Oracle Universal Installer uses when you run the Universal Installer on the host.
However, in some cases, you may have more than one inventory. For example, you may have worked with Oracle Support to clone your Oracle software installations. For those cases, you can use the following procedure to be sure that Enterprise Manager can track and manage the software information in multiple inventories on the same host.
Caution:
Enabling support for multiple inventories is optional and available only for advanced users who are familiar with the Oracle Universal Installer inventory architecture and who have previously worked with multiple inventories on a managed host. This procedure is not required for hosts where normal installations have been performed.
To set up Enterprise Manager so it can read multiple inventories on a host:
- Locate the OUIinventories.add file in the following directory $ORACLE_HOME/
_ /sysman/config
The Management Agent state listed in this example represents an installation for Database Control. For more information about the Management Agent state to use for other installations, see Section 14.2.1, "AGENT_HOME Versus AGENT_STATE Directories" . - Open OUIinventories.add using a text editor.
Instructions within the file describe the format to use when identifying multiple inventories, as well other techniques for mapping Oracle Homes. - Carefully review the instructions within the file.
- Add entries to the file for each additional inventory on the managed host.
- Save your changes and close the file.
During its next collection of host configuration information, Enterprise Manager will start gathering software configuration information from the inventories that you identified in the OUIinventories.add file, in addition to the default inventory that Enterprise Manager normally collects.
Alternatively, to see the data gathered from the additional inventories before the next regularly-scheduled collection, navigate to the Host home page in the Grid Control Console, click the Configuration tab, and click the Refresh Data icon next to the page timestamp.
Note:
If there any irrecoverable problems during the collection of the default inventory (for example, if the inventory file or directory protections prevent Enterprise Manager from reading the inventory), inventories listed in OUIinventories.add file are also not collected.
If the Enterprise Manager is able to read the default inventory, but there is a problem reading an additional inventory listed in the OUIinventories.add file, Enterprise Manager issues a collection warning for those inventories. However, Enterprise Manager does collect the configuration information for the other inventories.
Before the script :
- note on the "(for example, if the inventory file or directory protections prevent Enterprise Manager from reading the inventory)"
Be carrefull of this... The IAS guys that have installed some of our ias instances, have installed them with specific users/groups... and my user (part of the dba group) hasn't the rigths to read the ias files... and then my agent discovers nothing... :-( - $HOME/OUIinventories.add.orig is the original copy of $ORACLE_HOME/sysman/config/OUIinventories.add
Just to begin with a "clean" file each time we regenerate.... Even if original the file contains only comments... ;-) - iagt10g@uxhades2da > cat $HOME/oraInventories.txt
/opt/app/oracle/app/oradev8i/oraInventory
/opt/app/oracle/app/oradev9i/oraInventory
/opt/app/ias/oraInventory
/opt/app/ias/infra/oraInventory
/opt/app/ias/Inventory/oraInventoryForms
/opt/app/ias/Inventory/oraInventoryA916dev1012
/opt/app/ias/Inventory/oraInventoryA927dev1012
/opt/app/ias/Inventory/oraInventoryAsInfradev1012
/opt/app/ias/Inventory/oraInventoryas1012infra
/opt/app/ias/Inventory/oraInventoryas1012mid
/opt/app/ias/Inventory/oraInventoryrepca_101203
/opt/app/ias/Inventory/oraInventorya918dev101202
/opt/app/ias/Inventory/oraInventorya917dev101202
/opt/app/ias/Inventory/oraInventorya916dev101202
/opt/app/ias/Inventory/oraInventoryaCOMAD00D
/opt/app/ias/Inventory/oraInventorydevappn1013
/opt/app/ias/Inventory/oraInventoryA949dev101202
iagt10g@uxhades1da > cat $HOME/oraInventories.txt
/opt/app/ias/iagt10g/oraInventory
/opt/oracle/app/iagt10g/oraInventory
/opt/oracle/app/iagtacc/oraInventory
/opt/oracle/app/iagtadm/oraInventory
/opt/oracle/app/iagtdev/oraInventory
/opt/oracle/app/oraacc10g/oraInventory
/opt/oracle/app/oraacc8i/oraInventory
/opt/oracle/app/oraacc9i/oraInventory
/opt/oracle/app/oraadm10g/oraInventory
/opt/oracle/app/oraadm8i/oraInventory
/opt/oracle/app/oraadm9i/oraInventory
/opt/oracle/app/oradev10g/oraInventory
/opt/oracle/app/oradev8i/oraInventory
/opt/oracle/app/oradev9i/oraInventory
/opt/oracle/app/oraedu8i/oraInventory
/opt/oracle/app/oraedu9i/oraInventory
/opt/oracle/app/oratst8i/oraInventory
###############################################################
# Christian Clemmen - 2005/07 - v1.0 - creation
# info@infor-conseils.be
###############################################################
#!/bin/ksh
cp $HOME/OUIinventories.add.orig $ORACLE_HOME/sysman/config/OUIinventories.add
for inv in `cat $HOME/oraInventories.txt`
do
loc=$HOME/oraInst.`echo $inv | cut -d/ -f6`.loc
echo "inventory_loc=$inv" > $loc
echo "inst_group=dba" >> $loc
echo "inventory: $loc" >> $ORACLE_HOME/sysman/config/OUIinventories.add
done
In the $HOME, we have now:
-rw-r--r-- 1 iagt10g dba 68 May 23 08:16 oraInst.oraacc10g.loc
-rw-r--r-- 1 iagt10g dba 66 May 23 08:16 oraInst.iagtdev.loc
-rw-r--r-- 1 iagt10g dba 66 May 23 08:16 oraInst.iagtadm.loc
-rw-r--r-- 1 iagt10g dba 66 May 23 08:16 oraInst.iagtacc.loc
-rw-r--r-- 1 iagt10g dba 66 May 23 08:16 oraInst.iagt10g.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oratst8i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraedu9i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraedu8i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oradev9i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oradev8i.loc
-rw-r--r-- 1 iagt10g dba 68 May 23 08:16 oraInst.oradev10g.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraadm9i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraadm8i.loc
-rw-r--r-- 1 iagt10g dba 68 May 23 08:16 oraInst.oraadm10g.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraacc9i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraacc8i.loc
Each of them contains something like :
iagt10g@uxhades1da > cat oraInst.oraacc8i.loc
inventory_loc=/opt/oracle/app/oraacc8i/oraInventory
inst_group=dba
Content of $ORACLE_HOME/sysman/config/OUIinventories.add after script:
inventory: /home/iagt10g/oraInst.iagt10g.loc
inventory: /home/iagt10g/oraInst.iagtacc.loc
inventory: /home/iagt10g/oraInst.iagtadm.loc
inventory: /home/iagt10g/oraInst.iagtdev.loc
inventory: /home/iagt10g/oraInst.oraacc10g.loc
inventory: /home/iagt10g/oraInst.oraacc8i.loc
inventory: /home/iagt10g/oraInst.oraacc9i.loc
inventory: /home/iagt10g/oraInst.oraadm10g.loc
inventory: /home/iagt10g/oraInst.oraadm8i.loc
inventory: /home/iagt10g/oraInst.oraadm9i.loc
inventory: /home/iagt10g/oraInst.oradev10g.loc
inventory: /home/iagt10g/oraInst.oradev8i.loc
inventory: /home/iagt10g/oraInst.oradev9i.loc
inventory: /home/iagt10g/oraInst.oraedu8i.loc
inventory: /home/iagt10g/oraInst.oraedu9i.loc
inventory: /home/iagt10g/oraInst.oratst8i.loc
Mount ISO - Virtual cdrom
Microsoft has a free, 60kb program that does it! Of course, it is not supported... and it's not as friendly as the software listed above; however, it works and it's free.
You can download it here
Here's the readme:
Readme for Virtual CD-ROM Control Panel v2.0.1.1
THIS TOOL IS UNSUPPORTED BY MICROSOFT PRODUCT SUPPORT SERVICES
System Requirements
===================
- Windows XP Home or Windows XP Professional
Installation instructions
=========================
1. Copy VCdRom.sys to your %systemroot%\system32\drivers folder.
2. Execute VCdControlTool.exe
3. Click "Driver control"
4. If the "Install Driver" button is available, click it. Navigate to the %systemroot%\system32\drivers folder, select VCdRom.sys, and click Open.
5. Click "Start"
6. Click OK
7. Click "Add Drive" to add a drive to the drive list. Ensure that the drive added is not a local drive. If it is, continue to click "Add Drive" until an unused drive letter is available.
8. Select an unused drive letter from the drive list and click "Mount".
9. Navigate to the image file, select it, and click "OK". UNC naming conventions should not be used, however mapped network drives should be OK.
You may now use the drive letter as if it were a local CD-ROM device. When you are finished you may unmount, stop, and remove the driver from memory using the driver control.
2007-04-19
Bug 5458753 SQL may execute in wrong schema
Waaw... completely crazy!!!!
This issue is very very rare in 10.1 but can occur
much more easily in 10.2.0.2 .
If different schemas have tables / views etc.. with identical names
and sessions from different schemas execute IDENTICAL SQL
statements then it is possible for such SQL sentences to get
executed against the wrong schema objects. This can result
in:
- Wrong results from SELECT SQL
- Logical data corruption for DML SQL
- Unexpected errors such as ORA-2291 / ORA-1 from DML
- Parse errors if the underlying table definition differs.
eg:
Consider an application with seperate sessions executing
"select mycol from mytable where key=:B1" as user A and user B
where both users have a table called "MYTABLE".
In this case it is possible for user A to end up executing the
SQL against B.MYTABLE instead of against A.MYTABLE.
Workaround:
There is no simple workaround which can avoid this fully.
The issue can be avoided by prefixing object names with the schema name.
eg: In the above example change the SQL to use
"select mycol from A.mytable" for user A and
"select mycol from B.mytable" for user B.
If SQL cannot be changed then it can help to reduce shared pool
load (as the problem occurs when cursors are reloaded having
been aged out / invalidated). Using DBMS_SHARED_POOL.KEEP for
affected cursors can also help.
More info in Metalink note: 392673.1
2006-05-04
2006-04-28
Invalid public synonyms on Oracle 10g
I write a little script to regenerate them:
/******************************************
2006-04-18 - V1.0 - Creation of the script
(c) INFOR-Conseils
oracle _at_ infor-conseils.be
Regenerate invalid public synonyms
******************************************/
prompt system@&&database
conn system@&&database
set pages 0
set feed off
set linesize 1000
set trimsp on
spool _your_disk_
select 'CREATE OR REPLACE PUBLIC SYNONYM 'object_name' for 'object_name';' from dba_objects where status = 'INVALID'and object_type = 'SYNONYM' and owner = 'PUBLIC';
spool off
@@_your_disk_
host _the_remove_command_ _your_disk_
exit;
e.g d:\ on Windows or /home/oracle on Linux/Unix.
The _the_remove_command_
2006-04-26
ORA-29531: no method get_crlf in class oracle/plsql/net/TCPConnection
When trying to send a mail with the oracle provided package UTL_SMTP you can get the error ORA-29531: no method get_crlf in class oracle/plsql/net/TCPConnection:
- Error: ORA-29531: no method get_crlf in class oracle/plsql/net/TCPConnection
- ORA-06512: at "SYS.UTL_TCP", line 678
- ORA-06512: at "SYS.UTL_TCP", line 671
- ORA-06512: at "SYS.UTL_SMTP", line 99
- ORA-06512: at "SYS.UTL_SMTP", line 121
- ORA-06512: at "S716.S724_PA_UTIL", line 2470
- ORA-06512: at line 2
Metalink Note 160798.1 says:
UTL_SMTP Fails with 'ORA-29531 no Method get_crlf' After Applying Patchset 8.1.7.2 or Above
- fact: Oracle Server - Enterprise Edition 8.1.7
- symptom: Error when using UTL_SMTP
- symptom: ORA-29531: no method get_crlf in class oracle/plsql/net/TCPConnection
- symptom: ORA-6512: at "SYS.UTL_TCP"
- symptom: ORA-6512: at "SYS.UTL_SMTP"
- change: patchset 8.1.7.2 or above applied
- cause: The steps in the patchset readme for 8172+ does not include re-running initplsj or manually loading this jar.
- Original problem is caused by Bug 1721293 UTL_SMTP FAILS WITH ORA-29531 .
fix:
1. connect as SYS
2. run $ORACLE_HOME/rdbms/admin/initplsj.sql
Results of the script:
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Apr 26 10:15:39 2006
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
SQL> @$ORACLE_HOME/rdbms/admin/initplsj.sql
Call completed.
Call completed.
SQL> exit
And now all is running succesfully :-)
2006-04-25
La dernière mise à jour de Microsoft renferme bien des surprises!
- L’impossibilité d’accéder à certains dossiers particuliers comme « Mes Documents » ou « Mes Images »
- Un arrêt du fonctionnement des applications Office lorsque l’on essaye d’ouvrir ou enregistrer un fichier dans « Mes Documents »
- L’impossibilité d’ouvrir un fichier Office depuis « Mes Documents »
- Un arrêt de certains logiciels lorsque l’on utilise la fonction Fichier -> Ouvrir
- L’impossibilité d’afficher une page dans Internet Explorer après avoir écrit dans la barre d’adresses
- Utiliser la fonction du menu contextuel « Envoyer vers » n’a aucun effet
- La touche « + » dans l’explorateur Windows n’a aucun effet
- Certaines applications cessent de fonctionner lorsque l’on ouvre ou enregistre des fichiers dans « Mes Documents »
Ces problèmes peuvent être causés (ce n’est pas automatique) par un nouvel exécutable nommé VERCLSID.EXE qui doit valider les extensions du shell avant leur mise en marche. Sur certaines machines, dans certaines conditions, cet exécutable cesse en fait de répondre et provoque, par effet boule de neige, un arrêt du fonctionnement de certaines applications, notamment :
- Le logiciel Share-to-Web de HP : le service Hpgs2wnd.exe se retrouve notamment avec le logiciel HP PhotoSmart, les imprimantes DeskJet qui incluent un lecteur de cartes, les scanners HP, certains graveurs de CD et les appareils photo de la marque.
- Kerio Personal Firewall qui peut empêcher VERCLSID.EXE de fonctionner en essayant de marquer le fichier lorsqu’il est appelé.
Ce problème se retrouve sur toutes les versions de Windows depuis 2000 (XP et 2003 Server) et peut être réglé pour les deux applications nommées ci-dessus.
Pour HP Share-to-Web :
- Lancer l’éditeur de registre (regedit)
- Se rendre à la clé : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Shell Extensions\Cached
- Faire un clic droit sur la clé « Cached» et cliquer sur Nouveau -> Valeur DWord et la nommer: {A4DF5659-0801-4A60-9607-1C48695EFDA9} {000214E6-0000-0000-C000-000000000046} 0x401
- Donner à cette valeur le chiffre 1
- Fermer l’éditeur puis relancer la machine ou tuer le processus VERCLSID.EXE dans le gestionnaire des tâches
Pour le firewall Kerio, il faut configurer le logiciel pour que VERCLSID.EXE puisse s’exécuter sans être contrôlé.
Source Microsoft
Article original ici