Artículos IT, información y publicaciones sobre dba

Últimas publicaciones destacadas en Dataprix sobre esta temática TIC

The Oracle listener

 

In order to access to the same database where are you working outside the database server you must activate the service called listener, it has to be listening.

It can happens that the database is properly raised and can not connect from other servers, which are also set correctly (correct TNSNAMES, etc.).. 

In these cases could be that the listener has a problem, or simply has not been initiated.

To Check the status, start or stop it is very simple. Just open a command line session (console terminal, etc..) with the user that has installed the database, and run the lsnrctl command with the following parameters:

  • Check your state:
      > lsnrctl status

  • Stop the listener:
      > lsnrctl stop

  • Start the listener:
      > lsnrctl start

Keep in mind that when you stop the listener, the connections that are already in the database won't be closed, so a short stop is not very traumatic, only connections trying to enter while the listener is stopped are rejected, should not affect anyone who already has an opened session.

Login with SQLPlus as a DBA without entering password

 

If you have the system user who installed the database you can enter SQL Plus as DBA user, without entering a password as follows:

1. Enters the system with this user. 
2. From the command line, go into SQLPlus typing:

> sqlplus "/as sysdba"

If you need to enter using this way because you forgot the password of a user, you can easily change it: 

SQL> alter user user_name identified by new_password;

It can be more than one Database installed on the server, so you have to validate that the environment variables of the Oracle's user are pointing to your database.

For verifying that you has login into the correct database you can execute this statement: 

SQL> select name from v$database; 

Remote access using Oracle DBLINK

 

The easiest way to access from an Oracle database objects from another Oracle database is using a DBLINK (being the easiest does not mean that it is always the most desirable, the abuse of DBLINKS can create many problems, both of performance and safety)

To do this it's necessary a user with CREATE DATABASE LINK privilege, and create a DBLINK in the source database (A) by a simple statement such as:


Create database link LNK_from_A_to_B connect to USER identified by PASSWORD USING 'B'; 

'LNK_from_A_to_B' is the name of the link, 'USER' and 'PASSWORD' are the IDs of the user who will use the link to connect, which will inherit the permissions of all access through the link, and B is the name of the database's instance.

Using the DBLINK we can connect to the objects with the remote database's permissions that user has been provided in the creation statement.

To reference an object from the remote database should indicate the name of the object, concatenated with the character '@' and the name that we had given to the DBLINK.

Example: 

select * from TABLA@LNK_from_A_to_B 

How to connect to a remote MySQL database

 

 

MySQL has some special features when making a connection from a remote client that if we do not know can complicate access to a MySQL database from a different machine that hosts the database.

With other databases such as Oracle or SQL Server, once that no firewall or anything like that prevents us from the client machine access to the server, using normally data acces from a database user we can 'enter'.

With MySQL, although access to the port, usually 3306, is open, the database can be configured to shut out external connections, and the result is the same as if the port was closed by a firewall:

 

telnet mysql.dataprix.es 3306
Trying 188.166.233.199...
telnet: connect to address 188.166.233.199: Connection refused
telnet: Unable to connect to remote host

 

If you get this result should consult the file /etc/my.cnf, and checking for bind-address variable or skip-networking.

If skip-networking is and is not discussed, edit the file and delete it, or make a comment to have no effect and allow external connections:

 

Translation of terminology Oracle - DB2 LUW

 

With 9.7 DB2 LUW version, IBM makes a nod to all Oracle DBAs, much more numerous in the DB2 market.

For this reason, 9.7 version has introduced Oracle compatibility modes that let you perform tasks in DB2 with the ease and knowledge that all Oracle DBAs have. However, it is important to know the terminology's translation between Oracle and DB2 if you intend to get into the DB2 world.

In this first article, I relate a number of items from which this introduction is simple and can be read DB2 documentation easily, including general terminology, updates, utilities, and views.

 

Improve MySQL performance by adjusting some parameters

 

MySQL, like most database managers, can easily modify the parameters that control memory sizes engaged in certain tasks, resource utilization, concurrency limits, etc.

Properly adjusting these parameters can be obtained many performance improvements, especially if the server / s of the database is not about resources, and if the SQL optimization can not be improved more.

I've recently made some basic settings in a MySQL database, so I take this opportunity to explain some of the process I followed for those who seek an easy way to make a first optimization of parameters in the database. This is not to say that this is the best way to do just that to me has worked out well;)

The first comment that can be very helpful to look in phpMyAdmin to the sections 'Show information about MySQL runtime' and 'Show MySQL system variables', normally accessible from the home page of the application.

The first displays information and statistics collected and maintained the system since its beginning. Pay special attention to the values of variables that are displayed in red, and the advice provided to the right of these values.

Please refer to the variables used to determine the current values of the parameters that could later be modified to improve performance.

 

Update SQL Server table statistics dynamically throughout a database

 

In Oracle databases there is a table that allows to list all the tables in the database (table 'dba_tables') and we can use this 'dba_tables' to create maintenance scripts dynamically.  In SQL Server we can create also scripts of tables maintenace by querying the table [dataBase].dbo.sysobjects.

In the example below we use a T-SQL script to update statistics for all tables in a SQL Server database by querying dynamically the data dictionary (using the table dbo.sysobjects). This T-SQL code can be encapsulated in a stored procedure or in a job to be executed by the SQL Server Agent to automatically keep statistics updated on all tables of the dbo scheme in a SQL Server database.

Syndicate content