mysqldump backup in MariaDB/mysql

As a DBA taking backup is a common task, and mysqldump utility works with MariadB , i find it a simplest way of taking backups.
Below are few examples of taking backups using mysqldump -

1. Taking full database backup :

mysqldump --all-databases -u root -p > /backup/full-backup.sql

The above will take full backup , backup of all databases

2. Taking backup with triggers,routines,events :

mysqldump --triggers --routines --events --all-databases -u root -p > /backup/full-backup.sql

The above command is useful in taking backups of triggers, routines,events for all the databases.

3. Backup individual databases with triggers,routines,events :

mysqldump --triggers --routines --events --all-databases -u root -p dbname > /backup/full-backup.sql

Replace dbname with your database name in above

4. Export database structure without any data :

mysqldump -u root -p --no-data dbname >  /backup/db-backup.sql

5. Backup a specific table in a database :

mysqldump -u username -p db1 table1 > /backup/db1-table.sql

6. Backup a specific database :

mysqldump - u username -p db1 > /backup/db1-backup.sql

MariaDB

I have been working on a mariadb from sometime now, earlier i have worked on Mysql but lately got an opportunity to work on Mariadb. I have found things similar to MYSQL (i use all the same commands as i have used in MYSQL). Will share new posts related to the same in upcoming days

MEMORY_TARGET in oracle 11g

In oracle 10g , we have worked upon SGA_TARGET wherein components of SGA only were allocated dynamically as per the workload requirement.

In Oracle 11g  , oracle further introduced a new parameter name MEMORY_TARGET, thereby automating management of SGA + PGA. When this parameter is set, the SGA and the PGA memory sizes are automatically determined by the instance based on the database workload

Like in case of SGA_TARGET the parameter that controlled the maximum allocation was SGA_MAX_SIZE, in MEMORY_TARGET the parameter is MEMORY_MAX_TARGET.

When MEMORY_TARGET is configured, the following components are auto tuned: DB BUFFER CACHE, SHARED POOL, JAVA POOL, LARGE POOL and STREAMS POOL.

Now to enable this parameter i will list down some steps with screenshot.

I tried the below parameters in vmware and  for testing purposes i am setting it to 300 MB only

503 Service Unavailable : Oracle Grid Control

This happened recently when I discovered that Grid Control is not working and the error displayed was:

503 service unavailable servlet error service is not initialized correctly.

I bounced all services from services.msc, our server is on windows .

However the error was still there. I searched on metalink and found note ID 418159.1

On reading the note and diagnosing i found a file emoms.properties was nullified, this file is used by oms when services start.

As per the document this happens when disk space gets full and server is restarted.

So i followed the document and created a new file as no backup was available.

After creating file, Services started nicely...:-)

High ENQ:SQ contention in database

This Happened recently when all of a sudden Database stopped responding.

Only option left was to bounce the DB. Since it is a RAC setup, we shutdown one of the instance first and fortunately after starting things came to normal.

Later on when i analyzed the AWR of Database, first instance i found ENQ:SQ contention in top events.

This was new for me, then i checked TOP SQL section of AWR.

Here all top queries were accessing a particular Sequence , when i checked this sequence it was newly created and the cache size was very less 10.

So as such i increased cache size to 100 initially, and till now things are working fine.

I think it was because of this particular sequence , and this should not occur again.


Will try to dig out more..about it...

ORA-00600 error while querying a table

This happened recently in one of the setups. When user was querying a table for some data he was getting ORA-00600 [4000] ...We got this issue for resolution. Well this was the first time i came across an issue like this related to table corruption, so we to diagnose furthur we did a count(*) from table first.

1. Select count(*) from table;

ORA-00600: internal error code, arguments:[4000]...

Now we did and export of the table and it went fine

2. In this step we did VALIDATE STRUCTURE with CASCADE option.

SQL> Analyze table table name validate structure cascade;

ORA-00600: internal error code, arguments:[4000]...

 This again failed with same error

3. Now just to check whether its an index corruption or table corruption we again ran VALIDATE STRUCTURE but without cascade and it ran fine without any errors.

SQL> Analyze table table name validate structure ;

Table Analyzed.

4. From this we concluded that table was intact but corruption is with indexes, Further diagnosis revealed that  there was one index that too primary key

5. an online rebuild of the index was done

SQL > alter index index name rebuild online parallel 10;

This solved the problem and the error was gone