oralogo_small

 

Last week i got following error-stack when trying to open the Enterpris-Manager:

503 Service Unavailable
Servlet error: Service is not initialized correctly. Verify that the repository connection information provided is correct.
conn.ConnectionService verifyRepositoryEx.818 - Invalid Connection Pool. ERROR = ORA-00376

When digging deeper into the error-logs i found the problem that a datafile was not online.

ORA-01110: Datendatei 5: 'C:\ORACLEHOMES\ORADATA\EMREP\MGMT.DBF'
ORA-06512: in "SYSMAN.MGMT_USER", Zeile 10174
ORA-06512: in "SYSMAN.SETEMUSERCONTEXT", Zeile 10
ORA-06512: in Zeile 1

I was asking our admin, and he tried a new file-backup that locked the datafile while oracle was trying to write to it.
So it went in "recover" mode.
Solution for this issue:

Select the files in recover-mode    : select file#,name,status,enabled from V$datafile;
recover them                                  : recover datafile 'XXX';
set them online                               : alter database datafile 'XXX' online;

Undecided If you get an "Io exception: The Network Adapter could not establish the connection" error try if the DNS works correctly.
In my case a nslookup to the host-adress NSLOOKUP TESTSERVER was possible and returned the IP-adress.
But in the other direction it did not work NSLOOKUP 123.123.123.123 the problem was a misconfiguration on one of our DNS-Server.

centos2A project i heared a short time ago is CENTOS. Centos is a recompiled Redhat Enterprise Edition.The difference is that CENTOS is free and Redhat Enterprise is just for paying subscribers. You can read more about this project at the project-homepage.

Sometimes it happens, that there are no more notifications from the Enterprise Manager.
This occurs often after problems on the database (out of space in the os...).
The notifications are one of the most important features of the Enterprise-Manager, you are warned if the space is too low or if something takes too much cpu, if the backup has succeeded and a lot of other things.

The sending of the notifications is done by jobs that are defined in the sysman schema.
To repair this jobs, just delete and resubmit them.
This can be done by logging in as "sysman" user, and executing following procedures:

    connect sysman/yoursysmanpassword@yourdatabase
    exec emd_maintenance.remove_em_dbms_jobs;
    exec emd_maintenance.submit_em_dbms_jobs;


Have you ever been in the situation to test a piece of code in Oracle and you don't have the required data.  With the WITH-clause it is now possible to create the testdata without a lot of effort to create other tables. Look Cool at this statement:

WITH myTestTab AS (
     SELECT 'Roger' FIRST_NAME,
            'Testo' SURE_NAME,
            TO_DATE('01.01.1970','DD.MM.YYYY')  DAY_OF_BIRTH FROM DUAL UNION
     SELECT 'Hoger' FIRST_NAME,
            'Aloah' SURE_NAME,
            TO_DATE('01.01.1980','DD.MM.YYYY')  DAY_OF_BIRTH FROM DUAL UNION
     SELECT 'Noger' FIRST_NAME,
            'Tarum' SURE_NAME,
            TO_DATE('01.01.1975','DD.MM.YYYY')  DAY_OF_BIRTH FROM DUAL UNION
     SELECT 'Doger' FIRST_NAME,
            'Herdo' SURE_NAME,
            TO_DATE('01.01.1985','DD.MM.YYYY')  DAY_OF_BIRTH FROM DUAL
     )

     SELECT *
         FROM myTestTab
            WHERE DAY_OF_BIRTH BETWEEN TO_DATE('01.01.1969','DD.MM.YYYY')
                                   AND TO_DATE('01.01.1976','DD.MM.YYYY')

The Blue section is the definition of the "virtual" table and the red section is the select-statement that accesses this "virtual" table.

You can easy simulate a table the table can also be named like the 'real' table where you have no Test-Data available.  You want the Oracle-Documentation for this clause? I can't help you, i looked a lot in the whole documentation and got a lot of hits, but mostly they are about another with-clause (for materialized views) Frown. If somebody finds the docu for that, please let me know!

debian3

 

 

Today i had time enough to finish the installation on debian. I started the Oracle-Installer and it started as used on windows, but it got stuck when recompiling SQLNet and the Database itself. The error was "Error in invoking target..." After looking in Metalink, it seems to be a problem of a wrong compilier. After looking in the web for such problems i found the hint that the following packages have to be installed: 

      • autoconf
  • automake
  • binutils
  • bzip2
  • doxygen
  • gcc
  • less
  • libc6-dev
  • make
  • perl-doc
  • unzip

there were some packages (i did not write them down Yell) that were not installed, and after retrying.... IT WORKED!

The installation finished without any problem, database started up and the Enterprise-Manager Console also worked  without any problems...

debian2

 

Installation of Gnome worked fine.... but how can i access this desktop remote? I decided to use VNC, because i already worked with it. So i installed the package "tightvncserver" with "aptitude install tightvncserver" and started it with vncserver. As VNC-Client i tried to use the tightvnc-client, but it was much slower than the ultra-vnc, i don't know exactly why, sometimes  misconfiguration. The next problem was that i got a console but no gnome-desktop. I forgot to configure the vncserver. I had to edit the file  /root/.vnc/xstartup disabled x-windows and added gnome. Now it looks like this:

xrdb $HOME/.Xresources xsetroot -solid grey
x-terminal-emulator -geometry 80x24+10+10 -ls -title "$VNCDESKTOP Desktop" &
#x-window-manager &
exec gnome-session &

For KDE your file would look like this:

xrdb $HOME/.Xresources
xsetroot -solid grey
x-terminal-emulator -geometry 80x24+10+10 -ls -title "$VNCDESKTOP Desktop" &
#x-window-manager &
startkde &

After starting with "vncserver" i was able to access the remote machine by my Windows-XP UltraVnc-client.

The next step was to get the linux installation package from the oracle-site. There are a lot of HOWTOS available on the net, i used this one. After successfully configuring my Debian i was starting the OracleInstaller...

Note:

 

debian

 

Last week i was testing to install Oracle on Debian. Linux is the most important operating system for Oracle Databases and so i was testing how far it would come if a Linux-dummy (ME!) is doing an Oracle-Installation on Linux. The first step was getting a grip on the operating-system. You have to know how to list files, find files and do other Operating System-Basics Undecided. I decided to make the installation on Debian because it is a easy and stable solution... and was recommended to me by a friend who is also hosting my Linux-Server. He did a basic-installation on a VM-Ware Server, i got the root account, and then i started to make my first experiences.

using aptitude
A very important thing is to install packages. And with Debian it is pretty easy you just have to use aptitude. That means executing the following code will install the unzip-package: aptitude install unzip 

The next step was to install a GUI. A GUI is the right thing for a beginner you can navigate, create files browse without a deeper knowledge of the operating-system. I decided to use gnome, reason ... none ... just need gui.  That means "aptitude install gnome".


ods10If you are using Oracle-Reports and you want to execute a report "asynchronous" that means you start the report, and you don't want to wait with processing until the report has finished, the easiest way is to use the SRW-API. The SRW-API is a simple PL/SQL package. You can start reports and look after them by checking the status. The installation-scripts can be found in the Reports-Server-Home\reports\admin\sql. There are two scripts one for installation and one for grant (srwapiins.sql, srwapigrant.sql). After reading the package-specification you should be able to call your reports really simple way. This simplicity is also the reason why i like it more than the forms-builtin procedures.

Last week a i defined a new materialized view, and the on-commit was not working. After hours reading manuals, i found out that in the definition if you define FAST and ON COMMIT, fast is taken Yell. That means

    "CREATE MATERIALIZED VIEW MV_TEST BUILD IMMEDIATE REFRESH FAST ON COMMIT AS...." does a fast-refresh

    "CREATE MATERIALIZED VIEW MV_TEST BUILD IMMEDIATE REFRESH ON COMMIT AS...." does a on-commit

materie

I am using materialized views a lot, and they are a handy thing (in older versions the were named SNAPSHOT). If you have for example a lot of statistic stuff on your customer (how many purchases the last 7 days, how many in average over the year, how many contacts.... it is a lot of CPU and IO you need just for information that are not so important to be actual in real-time. In this case you can use Materialized Views (MV). 

A creation of a materialized views would look like this (more infos in the manuals there are a lot of options!!!):

CREATE MATERIALIZED VIEW MV_TEST
START WITH SYSDATE NEXT SYSDATE + 1 AS SELECT * FROM .....

In Oracle also a "fast refreshe" is possible, that means just the changed rows of the "master" table are written to the materialized view (100000 customers, just 5000 purchased something today so just 5000 changes are written to the MV).

 In some cases you also need the "on commit" feature. This is basically a fast refresh every time a record gets commited to the master-table. 

characterset

 

 

If you want to know why some characters are not displayed directly and want to know the character-set of your Oracle-database just use the following select-statement:

select * from v$nls_parameters where parameter='NLS_CHARACTERSET';