Month: May 2017

Mysql ibdata1 file recovery

Goutham Leave a comment

Steps to recover data when ibdata file gets corrupted or removed

ibdata is really important file in Mysql because it contains following data –

data dictionary – metadata for Innodb related objects.

double write buffer – protection against a crash in the middle of writing a block to disk.

rollback segments and MVCC data – Undo logs which are required if any transaction needs to be rolled back and also useful if any other transaction needs to see the original data (consistent reads).

And if we accidentally dropped the ibdata file, we definitely cannot rebuild it but we can safely recover all the data onto a new Mysql instance.

  • First shut down mysql if its already running :
mysqladmin -uroot -p shut
  • Let’s try to start it up to see the error we get

Read More

Migrate oracle database from Non ASM to ASM

Goutham Leave a comment

 Steps to migrate database files from local disk/SAN to ASM :

First create ASM disks and diskgroup – here

Migrating all data (Datafiles, Controlfiles, logfiles) to DATA diskgroup.

Startup mount and restore SPFILE to ASM

SYS @ test > startup mount

[[email protected] dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Feb 8 11:15:51 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: TEST (DBID=2104987387, not open)

RMAN> RESTORE SPFILE TO '+DATA/spfiletest.ora';

Starting restore at 08-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA/spfiletest.ora
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbs/01o1et80_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbs/01o1et80_1_1 tag=TAG20130208T111407
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 08-FEB-17

Copy and switch database files to ASM Read More

Mysql innobackupex full and incremental backups

Goutham 1 Comment

Mysql backup strategy using Innobackupex/Xtrabackup :

If we are planning to automate backup of our Mysql production instance, we can think of two approaches :

  1. A full backup followed by cumulative backups i.e All incremental backups with last full backup as base directory.
  2. A full backup followed by differential backups i.e All incremental backups with last incremental/Full backup as base directory.

Both approaches have their own benefits and drawbacks.

The first approach would need little bit more backup storage since we would always perform incremental backup from the last full backup checkpoint. Also the time required to perform incremental backup would be more compared to the second approach.

The main drawback of having the second approach is that we would be spending more time during backup restore process, mainly we need to to prepare the full backup by applying all the subsequent incrementals to it.

A better approach would be definitely the first one since it saves our time while we need to restore the database to a particular point, we just need to apply required incremental to the base backup.

Below are few innobackupex examples :

Full Backup :

/usr/bin/innobackupex --slave-info --user=${User} --password=${Password} --defaults-file=${DefaultsFile} --compress /u01/mysql/backup/fullbackup/

We are using compress option which would compress our backups and save us atleast 2x times of backup storage. slave-info useful while backing up the slave server, generates xtrabackup_slave_info file with master information.

Incremental Backups : Read More

Postgres grant select access on all tables in a database

Goutham Leave a comment

It is not possible to create a user with read only access on a particular database. Instead we can do it at schema level.

1) Grant connect to database

GRANT CONNECT ON DATABASE <db name> to <user name>;

2) Grant usage on schema

GRANT USAGE ON SCHEMA <schema name> TO <user name>;

3) Grant select on all tables in a schema using

GRANT SELECT ON ALL TABLES IN SCHEMA <schema name> TO <user name>;

4) Also make this privilege available for new tables added in future using

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema name> GRANT SELECT ON TABLES TO <user name>;

 

So, if we have many schemas we can simply generate ‘GRANT’ statements as mentioned below : Read More

Difference between undo segment and redo logs

Goutham Leave a comment

A point of confusion for a DBA would be the distinction between undo and redo.

–> Basically undo tablespace stores the before values of changed data blocks whenever we issue a insert, update or delete statement (DML operation) . When we issue any DML statement the changed blocks are stored in buffer cache and the before values for those changed blocks in UNDO segments. Whenever we issue a ROLLBACK command it uses the undo segment to rollback to previous value and even UNDO provides the read consistency to a user as he can only see the previous values until a transaction is committed, for example a user 1 may run select statement on a table being modified by user 2, while user 1 can only see the unchanged values until user 2 commits his DML statement.

How long a undo data stored in the database?
Oracle provides flexibility of how long should undo data be stored with the help of undo_retention parameter. We can set undo_management parameter to automatic for oracle to manage undo retention(default), or even set this value manually and it’s value should be greater than the time taken by the longest running query in your database. Read More