Good Read for PostgreSQL DBA

PostgreSQL 9 High Availability Cookbook

Link to Amazon

Good PostgreSQL HA guide

Several good guidelines for designing PostgreSQL databases and HA Clusters.
For example recipes for counting storage size, IOPS, cpu, memory etc...
Also very specific guides how to use different replications and their monitoring + management and HA solutions.

ORAchk to check databases (single and RAC)

Oracle ORAchk utility will replace RACchk utility and allow you to check also single instance databases. You can also send notification emails throught it. And schedule automatic check via cron jobs.

Here is quick guide to install and use ORAchk utility:

1. Download from My Oracle Support (you'll need MOS account for this):
Doc ID 1268927.2

NOTE! here you can also find user guide pdf!

2.  Install as oracle user in host you want to check:

      - unzip orachk.zip

3. Set notifications Email and automatic cron checks:
      - ./orachk -set "AUTORUN_SCHEDULE=3 1 * *; NOTIFICATION_EMAIL=<your_email_address>"

NOTE! Outlook (+ virus scanner) may not work proberly with these notification emails. So you might need some testing to get this work with it.
cron parameters in this set are (left to right):  hour (0 - 23), day of month (1 - 31), month (1 - 12), day of week (0 - 6) (0 to 6 are Sunday to Saturday)

4. Check settings:
./orachk -get all

5. Start daemon:
./orachk -d start

NOTE! with RAC this ask's root pwd for audit checks

6.  Stop daemon:
./orachk -d stop


7. To see all parameters:
./orachk -help

NOTE! you can also set auto restart. So script will be started automatically on node restarts.

Oracle DBA_TABLESPACE_USAGE_METRICS vs DBA_DATA_FILE with autoextent tablespaces

If you compare tablespace sizes with following two queries:

SQL> select * from dba_tablespace_usage_metrics

and

SQL> select x.tablespace_name,
    sum(x.bytes/1024/1024) "Total Space (Mb)" ,
    NVL((sum(x.bytes/1024/1024)-round(y.free,2)), sum(x.bytes/1024/1024)) "Used (Mb)",
     NVL(round(y.free,2), 0.00) "Free (Mb)",
     NVL(round(y.free/sum(x.bytes/1024/1024)*100,2), 0) "Free %"
from dba_data_files x,
    (select tablespace_name,sum(bytes/1024/1024) free
         from dba_free_space
         group by tablespace_name) y
where x.tablespace_name = y.tablespace_name (+)
group by x.tablespace_name,y.free;


They are looking very different especially if the tablespaces are using autoextent.
This is because :
Tablespace_size in DBA_TABLESPACE_USAGE_METRICS takes the maximum file size for autoextensible tablespace which corresponds to maxblocks in dba_data_files.
So basically DBA_TABLESPACE_USAGE_METRICS shows the maximum size to which a datafile can grow. And DBA_DATA_FILES (+ DBA_FREE_SPACE) current size of tablespace.
 

Look more from MOS (My Oracle Support) document:
Difference in Tablespace Size Values From dba_data_files and dba_tablespace_usage_metrics/V$Filespace_usage (Doc ID 455715.1)

Oracle Grid Control Agent autostart in RHEL / CentOS

When you install Grid Control Agent in server then the installer also create /etc/init.d/gcstartup script in the server. This init script wont automatically start Agent in server boot. But you can change it to do so. Do following as a root user.

1. Add following in the start of the script:

#!/bin/sh
# chkconfig: 35 10 90
# description: oracle grid control agent stop/start script on system up/down
#
# Use chkconfig command to start this script in appropriate run level.
# --------------------------------------------------------------------

#Source Function Library



2. And run following commands:

chkconfig --add gcstartup
chkconfig --levels 2345 gcstartup on
chkconfig --list gcstartup


This will work at least with Grid Control 11g agents and RHEL / Centos.

Oracle next extent check for tablespace fails and recyclebin (Grid Control)

If you using maintenance jobs to check does next extent fit in the tablespace and you get errors that they does not.

That means of course that your tablespace (or datafile) is so full that next extent would not fit into it.
"Quite like error ora-01653 unable to extend table in tablespace."

Sometimes this kind of errors can be confusing if you look tablespace usage from DBA_FREE_SPACE (or DBA_EXTENTS) . And this is because DBA_FREE_SPACE does not show dba_recyclebin usage. So if you remove database objects (for example table) from database but does not "purge" it then it will go into recyclebin and still takes same amount of space from same tablespace. This recyclebin used space can be taken into use if space is needed for new data. But it cannot be seen in DBA_FREE_SPACE as used space because it is reclaimable. But you can see this used space if you check for example tablespace (or datafile) usage from DBA_SEGMENTS.

Same thing is with the Grid Control (at least version 11G). If you check tablespaces usage it shows that there is free space to use in tablespace (this does not contain recyclebin space usage) but if you look the datafile space usage it will show real data space usage + recyclebin space usage.

NOTE! You wont get ORA-01653 errors in this kind of situations because Oracle can start to use space used by recyclebin when needed. But is is good to remember that you might get different space usage info if you wont purge dropped objects. 

MySQL MyISAM table repair.

MySQL MyISAM tables might break down from time to time.
You might get following errors in database error log:
--
[ERROR] mysqld: Incorrect key file
 for table '<TABLE_INDEX_FILE_NAME_AND_PATH>.MYI'; try to repair it

[ERROR] mysqld: Table '<TABLE_NAME_AND_PATH>' is marked as crashed and should be repaired--

If you get only error number from application then you can use perror utility from command prompt to get more info about error number (error numbers related to MyISAM tables usually are:
126 127 132 134 135 136 141 144 145) :

mysql/bin/perror 144

MySQL error code 144: Table is crashed and last repair failed



Check and fix MyISAM tables:

With myisamchk utility you can try to check and fix these tables/index files. Before run myisamchk  commands you need to stop MySQL server and go into database directory (or specify directory in the command). I prefer myisamchk because it garentees that no one is not trying to use tables when doing the repair (MySQL server need to be stopped when running it).


To Check table errors:
 
To check all tables (to check only one table specify that table name):
myisamchk *.MYI

To do slower and more extend-check check run:
myisamchk -e *.MYI

You can also check tables from SQL (when server is running):
CHECK TABLE <TABLE_NAME> <OPTIONS>;
CHECK TABLE <TABLE_NAME> QUICK;


To Fixing Tables or Index files:

Repairing tables might sometimes lead to lost of data so it is good practice to take backup of files or database before running these if it is possible.
 
Fix only those tables that are broken ( -q makes quick repair that repairs only index files):
mysql/bin/myisamchk -r -q <TABLE_NAME>


With big tables (and index files) and to get more performance for fixing use following options (you need to tune these values suitable for your environment) with myisamchk (to see all myisamchk options you can use myisamchk --help ):
mysql/bin/myisamchk --sort_buffer_size=2G \
           --key_buffer_size=2G \
           --read_buffer_size=512M \
           --write_buffer_size=512M \
      --tmpdir=/data/myisam_repair \
      --recover --quick \
     <TABLE_NAME>

You can also try to fix tables from SQL (when server is running) :
REPAIR TABLE <TABLE_NAME> <OPTIONS> ;
REPAIR TABLE <TABLE_NAME> QUICK;


NOTE: If some reason myisamchk did not fix your table/index files or you want to get more detailed guide and info about MyISAM table repair check these links:
http://dev.mysql.com/doc/refman/5.7/en/myisam-repair.html
http://dev.mysql.com/doc/refman/5.7/en/check-table.html
http://dev.mysql.com/doc/refman/5.1/en/repair-table.html

Oracle ORA-12564 TNS:connection refused ( shared server )

If you get "ORA-12564: TNS:connection refused" errors from your database connections.
Reason is typically misspelling in tnsnames.ora file.

But if you are using shared server connections these errors can be seen also if you have not enough dispatchers or your dispatcher max session limit is reached.

Usually with ORA-12564 you can also find these kind of errors in your listener logs:
"TNS-12520: TNS:listener could not find available handler for requested type of server"

This way you can check your connections via listener (this shows both dedicated and dispatcher connections):
lsnrctl services

With following sql you can check your dispatcher settings (these settings can be changed with ALTER SYSTEM SET dispatchers= ... commands):
SQL> select * from V$DISPATCHER;
SQL> select * from V$DISPATCHER_CONFIG;


If you does not get these errors in normal usage but only in occasionally then you also might want to check which users are doing most connections when this problem is on. You can do it with this sql (this uses RAC gv$session view so it shows cluster all instances connections. If you want only one instance connections you can use v$session):
SQL>  select INST_ID, USERNAME, count(SID) from gv$session group by USERNAME, INST_ID order by count(SID);

Oracle "opidcl aborting process unknown ospid (xxx) as a result of ORA-28" error.

If you get following ORA errors in your database alert log:
"opidcl aborting process unknown ospid (xxxx) as a result of ORA-28"

This usually means that some privileged user (dba) has killed sessions from database.

But if you see these errors a lot or all the time then this can be bug. This bug is affected
older (Oracle 11.1.0.6 and 11.1.0.7) versions. With bug error messages look like this:
"ORA-28 : opiodr aborting process unknown ospid (xxxxx_xxxxxxxxxxx) "

NOTE! If some application or user kill several (for example all one user) sessions at the same time there can be several errors in a row in alert log and it is still a normal situation.

Oracle 12c timestamps for datapump output file and console.

Starting on Oracle 12c you can set timestamps on for datapump (expdp and impdp) output file and console messages. With new LOGTIME option you can control timestamps printing. This option can have 4 different values (NONE, STATUS, LOGFILE, ALL).

NONE is default value. With it there is no additional timestamps in the output file or in the console. 

STATUS With this value timestamps are printed in the console but not in the output file.

LOGFILE With this value timestamps are printed in output file but not in the console. 

ALL With this value timestamps are printed in the output file and in the console.

Oracle 12c nologging for impdp

There is very useful new feature in Oracle 12c impdp which you can use to get rid of logging when you are doing import.

For example big bulk imports you might want to take logging of (both table and index) this way archivelog disk is not getting full and you can save some time.  You can do this with impdp "TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y" option.

If you want you can also choose to remove only table or index logging:
only table data logging off during import:
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE
only index data logging off during import:
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX 

Default value for this parameter is TRANSFORM=DISABLE_ARCHIVE_LOGGING:N which is doing normal logging during import.