Category: Database

Linux, MySQL

MySQL Network Connections on ‘TIME_WAIT’

Hi all!
Recently I caught a bunch of connections in ‘TIME_WAIT’ on a MySQL Server through ‘netstat – antp 3306’…
After some time, we identified this was caused by the environment not using DNS, only fixed IPS (uuugh!)…

As you know, for security measures MySQL maintains a host cache for connections established. From MySQL docs:

“For each new client connection, the server uses the client IP address to check whether the client host name is in the host cache. If not, the server attempts to resolve the host name. First, it resolves the IP address to a host name and resolves that host name back to an IP address. Then it compares the result to the original IP address to ensure that they are the same. The server stores information about the result of this operation in the host cache. If the cache is full, the least recently used entry is discarded.”
9.12.6.2 DNS Lookup Optimization and the Host Cache

For this reason, there is a DNS ‘reverse’ lookup for each login was hanging this connections.

The solution?
Right way: Add an A type registry in DNS for the hosts. Use DNS!
Quick way: Add on /etc/hosts from database server the mapping for the connected hosts, avoiding the DNS Lookup.
Quicker way: Setting the skip-name-resolve variable at /etc/my.cnf. This variable avoids this behavior in database layer for new connections and solve the problem.

This is a good (portuguese) post about it: http://wagnerbianchi.com/blog/?p=831

See ya!
Matheus.

Database, Linux

RAC on AIX: Network Best Practices

 , , ,

Hi all!
A few time ago I passed by some performance issues on AIX working with instances with different configuration (proc/mem). The root cause was basically the inefficient configuration of networking for interconnect (UDP).
As you know, the UDP is a non-response (for that reason with less metadata and faster) protocol. By the default, every server have a pool to send udp (and tcp) messages and another to recieve.
In my situation, once there was an ‘inferior’ instance, the pools were automatically set smaller in this one, and it was causing a high interconnection block sending statistics from other instances. In deed, it was lots of resends caused by overflows in this smaller instance…

There is one one to evaluete how much loss are you having for UDP in your AIX server:

netstat -s | grep 'socket buffer overflows'

If you are having considerable number of overflows, it’s recommended to reavaluate the sized of your udp_recvspace. And, of course, maintain the calculation of pools.

Oracle recommends, at least:

tcp_recvspace = 65536
tcp_sendspace = 65536
udp_sendspace = ((DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT) + 4 KB) but no lower than 65536
udp_recvspace = 655360 (Minimum recommended value is 10x udp_sendspace, parameter value must be less than sb_max)
rfc1323 = 1
sb_max = 4194304
ipqmaxlen = 512

This and others details about configuring RAC on AIX ban be found in note: RAC and Oracle Clusterware Best Practices and Starter Kit (AIX) (Doc ID 811293.1)

I’d recommend you take a look too.

Have a nice day!
Matheus.

Database, SQL | PLSQL

Compilation Impact: Object Dependencies

 ,

Hi all!
It’s not necessarily the DBA function, but how often someone of business came and ask you wich is the impact on recompiling one or other procedure?
It probably happen because the DBA usually make some magic and have a better understanding about objects relationship. It happens specially in cases there is no code governance…

So, you don’t have to handle all responsability and can switch some of that with developer, through DBA_DEPENDENCIES view.

The undertstanding is easy: The depended objects and the refered objects. If ou change the refered, all depended will be impacted by.

GREPORADB> @dependencies
Enter value for owner: GREPORA
Enter value for obj_name: TABLE_EXAMPLE
OWNER              Name                                TYPE       DEPE REFERENCED REFERENCED_OWNER   REFERENCED_NAME                         
------------------ ----------------------------------- ---------- ---- ---------- ------------------ -----------------------------------     
GREPORA            TOTALANSWEREDQUESTIONS              FUNCTION   HARD TABLE      GREPORA            TABLE_EXAMPLE                        
GREPORA            USERRESPONSESTATUS                  FUNCTION   HARD TABLE      GREPORA            TABLE_EXAMPLE                        
GREPORA            VW_INPROGRESSFEEDBACKOPTS           VIEW       HARD TABLE      GREPORA            TABLE_EXAMPLE                        
GREPORA            EVENTSTARTDT                        FUNCTION   HARD TABLE      GREPORA            TABLE_EXAMPLE                        
GREPORA            HAVEUSERANSWEREDANYTHING            FUNCTION   HARD TABLE      GREPORA            TABLE_EXAMPLE

Nice, hãn?

## @dependencies
col owner for a18
col name for a35
col type for a10
col referenced_owner for a18
col referenced_name for a35
col referenced_type for a10
select owner,name,type,dependency_type,referenced_type,referenced_owner,referenced_name from dba_dependencies
where referenced_owner like upper('%&owner%') and referenced_name like upper('%&OBJ_NAME%');

See ya!
Matheus.

Backup and Restore, Database, Errors and Bugs, RAC | Data Guard

ORA-01153: an incompatible media recovery is active

 , ,

When trying to start or increase parallel of recover manager on datagauard (MRP):

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

I simply happen because there already are a process runnning, let’s check:

More“ORA-01153: an incompatible media recovery is active”

Database, SQL | PLSQL

Oracle – Lost user’s password?

Hi everyone,

Sometimes we need to connect to the database using an unusual schema that we don’t even know the password, maybe because it was created through a script in a release or the latest DBA never stored it somewhere public or maybe due to any other reason (whatever, right? you just need to connect there and end of the story), but anyhow, you need to login using this schema specifically (to create/delete synonyms, dblinks, jobs etc…). How would you do that without the password?

shaneharrisonquestionmark1

More“Oracle – Lost user’s password?”

Database, Errors and Bugs

x$kglob: ORA-02030: can only select from fixed tables/views

 , ,

Hi all!
While selecting on x$kglob with DBA credentials hanging on:

SQL> select count(*) from sys.x$kglob;
ERROR at line 1:
ORA-00942: a tabela ou view não existe

But with sys it succeed. Ok, let’s grant privilege:

SQL> grant select on sys.x$kglob to dba;
grant select on sys.x$kglob to dba
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

What a hell! I couldn’t grant it any way!
So the MCGayver solution was:

More“x$kglob: ORA-02030: can only select from fixed tables/views”

Database, RAC | Data Guard

Saving database space with ASSM

 , ,

It’s good way reclaim WASTED space from tables and index using  the Segment Advisor.

To perform an database reclaim procedure using Automatic Segment Space Management (ASSM) it is preferred to create tablespaces with below option:

grepdb> CREATE TABLESPACE HR
DATAFILE '+GREPORADG/'
SIZE 10M EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Only tablespaces with segment space auto are eligible to Segment Advisor.

To manually run the Segment Advisor on OEM:

guid-65f07e4f-0482-47df-bdf9-8d34b625093a-default

It will save some database storage area, and make it more effective cause by LHWM/HHWM.

Maiquel.

Database, Errors and Bugs, Shell Scripts, SQL | PLSQL

Searching entries on Alert.log: A Better Way

 ,

Hi all!
As the oldest readers know, someday I had to found some entries in the alertlog and I had a really big log. So I jerrry-ringed some scripts for grepping alert with auxiliar files and etc.
I can see the posts here: Grepping Alert by Day  and Grepping Entries from Alert.log.

So… They are functional, but probably the worst ways to get it. I didn’t know and was innocent to not search by the view x$dbgalertext.
There is also possible to write on alert through the procedure SYS.DBMS_SYSTEM.KSDWRT.

Ok, so let me fix this situation with theese two good guys: @write_alert and @find_alert

More“Searching entries on Alert.log: A Better Way”

Database, Tools

PL/SQL Developer Taking 100% of Database CPU

 , , ,

When using PL/SQL Developer (Allround Automations), a internal query is taking a lot of cpu cycles on database server (100% of a CPU).
Is this your problem? Please check if the query is like this:

select s.synonym_name object_name, o.object_type
from sys.all_synonyms s,
sys.all_objects o
where s.owner in ('PUBLIC', user)
and o.owner = s.table_owner
and o.object_name = s.table_name
and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')

It’s caused by the Describe Context Option of Code Assistant. To disable it:
Tools > Preferences > Code Assistant and disable the “Describe Context” option.

More“PL/SQL Developer Taking 100% of Database CPU”

Database, Errors and Bugs

ORA-02296: cannot enable (string.) – null values found

 , , , ,

Hi all!
Found the error below?

greporadb> alter table TABLE_TEST modify COLUMN_TEST not null;
alter table TABLE_TEST modify COLUMN_TEST not null
*
ERROR at line 1:
ORA-02296: cannot enable (MATHEUSDBA.) - null values found

It happen basically because you have null values for this column. Let’s check:
More“ORA-02296: cannot enable (string.) – null values found”