Diagnose Oracle Database

1. Is the listener resolvable? C:\>tnsping IP:port/service(Confirms: the database listener is running on serverside)
2. Is there a firewall issue? C:\>telnet hostdb port
3. Can I login? C:\>sqlplus system/pass@MYDB or C:\>sqlplus system/pass@hostname:1521/MYDB(Confirms: The database is open)
4. Finally, chances of having a successful login with your favorite dev-tool has increased by 100%

To be able to Telnet the host you need to go to control panel, programs and add services/functionalities then telnet client.

When using Windows Telnet if the request is succesful then it won't display anything, if the request fails it will send the failure message.

ODI 1425 Error

This is a common error that occurs with the Oracle Data Integrator Agent.

The solution is to delete and configure again the agent through the ODI console, these are the steps to follow:

1.- Enter the odi console:

http://ServerName:Port/odiconsole

Example:
http://MyServer:8010/odiconsole

2.- You have to right click the agent that is presenting the problem and delete it.

3.- Create the agent again but with the correct informatión this time.

4.- Remember to restart the managed web logic server for the changes to take effect.

Collating Spanish accents in PL-SQL

The following command in PL-SQL is used to remove latin accents from names in Oracle PL-SQL programming language, it is useful when you have to insert words in Spanish that usually contain tittles.


SELECT NAME_USER FROM USERS
WHERE Convert(LOWER(NAME_USER ),'us7ascii')=Convert(LOWER('JESÚS'),'us7ascii')


The previous query will remove the tittles from the data and convert it to lowercase text, for example 'JESÚS' will be converted to 'jesus' without the tittle on the U letter.

The recommended approach is to format the text when you insert it so that you don't have to do it everytime you search for it, in a small database this might not be a problem but when you're searchings millions or billions of records this formatting will massively decrease performance.

You also have to take into consideration whether the information has to have those tittles for presentation purposes for formal documents and reports. In that scenario the best solution would be to add a second column with the raw data and one with the formatted data, the searches should be done against the formatted data column and it should be the one indexed.

Linux Command guide 1

Command:
ps -ef |grep odi117

This command shows all the processes currently running for the specified group


Command:

crontab -L

This command shows all the programming for crontab.

Diagramming Tool XMIND

During my most recent project I have been in charge of generating and validating documentation and so I came across this very useful tool XMIND for generating diagrams, Organizational charts, flow charts amongst other. I want to document this because despite working in this kind of projects this is the first time I come across this nice useful program.

It has a free edition that has all the core elements required but if you wish extra functionality it also has some pro editions with extra stuff.

Here´s the link to their website:

http://www.xmind.net/download/win/

Using RMAN to delete Archivelog files in Oracle 11g

The following process is very useful when you're working with express Oracle database version, due to the limited size for log archives in this versions when you reach the limit the listener service will continue to operate correctly but the databases won't be able to start.

Entering RMAN and cleaning all the log archives:

1.- Open RMAN tool from CMD, you just need to execute the instruction rman without moving to any directory:

2.-Login to your database:

CONNECT TARGET /

Using this command you login using windows authentication.

3.- View the archive log files:

RMAN>list archivelog all;
RMAN>list copy of archivelog until time ‘SYSDATE-10′;
RMAN>list copy of archivelog from time ‘SYSDATE-10′
RMAN>list copy of archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN>list copy of archivelog from sequence 1000;
RMAN>list copy of archivelog until sequence 1500;
RMAN>list copy of archivelog from sequence 1000 until sequence 1500;

4.- Delete the archive log files:

RMAN>delete archivelog all;
RMAN>delete archivelog until time ‘SYSDATE-10′;
RMAN>delete archivelog from time ‘SYSDATE-10′
RMAN>delete archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN>delete archivelog from sequence 1000;
RMAN>delete archivelog until sequence 1500;
RMAN>delete archivelog from sequence 1000 until sequence 1500;