Thursday, June 21, 2012

Oracle Flashback Data Archive - Oracle Total Recall

Flashback Data archive is the new feature introduced in Oracle 11g. It provides the ability to track and store all transitional changes to a table over its lifetime. Flashback Data Archive (which can logically span one or more table spaces) specifies a space quota and retention period for the archive, and then creates the required tables in the archive. It allows long-term retention (for ex years) of changed data to a table or set of tables for a user-defined period of time.

The database can have multiple flashback data archives, but only a single default archive. The individual flashback archive consists of one or more tablespaces or parts of tablespace. Each flashback archive has a name, retention period and a quota on each associated tablespace.

When a DML transaction commits an operation on a flashback archive enabled table, the FBDA (Flashback Data Archiver) process stores the pre-image of the rows into a flashback archive, along with metadata of the current rows. The FBDA process is also responsible for managing the data within the flashback archives, such as purging data beyond the retention period.

To use this feature user should have below privileges
1.FLASHBACK ARCHIVE object privilege on the Flashback Data Archive that you want to use for that table
2.FLASHBACK ARCHIVE ADMINISTER system privilege to enable or disable Flashback data archive.

Creating a Flashback Data Archive
Create Flashback Data archive using below command
SQL> CREATE FLASHBACK ARCHIVE flashback_archive TABLESPACE flashback_archive_tbs QUOTA 15G RETENTION 5 YEARS;

Altering a Flashback Data Archive
You can add, modify and remove tablespaces, and change the retention, purge some or all of its data.

Add up to 2G of tablespace “flashback_archive_tbs1” to Flashback Data Archive flashback_archive1

SQL> ALTER FLASHBACK ARCHIVE flashback_archive1 ADD TABLESPACE flashback_archive_tbs1 QUOTA 2G;

Change the maximum space that Flashback Data Archive “flashback_archive1” can use in tablespace flashback_archive_tbs1 to 5G

SQL> ALTER FLASHBACK ARCHIVE flashback_archive1 MODIFY TABLESPACE flashback_archive_tbs1QUOTA 5G;

Remove tablespace “flashback_archive_tbs1” from Flashback Data Archive
SQL> ALTER FLASHBACK ARCHIVE flashback_archive1 REMOVE TABLESPACE flashback_archive_tbs1;

Dropping a Flashback Data Archive
Remove Flashback Data Archive flashback_archive1 and all its historical data
SQL> DROP FLASHBACK ARCHIVE flashback_archive1;

Enabling and Disabling Flashback Data Archive
By default, flashback archiving is disabled for any table and you need FLASHBACK ARCHIVE privilege to enable for any table. After flashback archiving is enabled for a table, you can disable it only if you either have SYSDBA or FLASHBACK ARCHIVE ADMINISTER privilege.

Create table EMP and store the historical data in the DEFAULT Flashback Data Archive
SQL> CREATE TABLE EMP (EMPNO NUMBER(5) NOT NULL, ENAME VARCHAR2(20)) FLASHBACK ARCHIVE;

Create table EMP and store the historical data in the Flashback_Archive1
SQL> CREATE TABLE EMP (EMPNO NUMBER(5) NOT NULL, ENAME VARCHAR2(20)) FLASHBACK ARCHIVE Flashback_Archive1;

Enable flashback archiving for the table EMP and store data in the default Flashback Data Archive
SQL> ALTER TABLE EMP FLASHBACK ARCHIVE;

Enable flashback archiving for the table EMP and store data in Flashback Data Archive Flashback_Archive1
SQL> ALTER TABLE EMP FLASHBACK ARCHIVE Flashback_Archive1;

Disable flashback archiving for the table employee:
SQL> ALTER TABLE EMP NO FLASHBACK ARCHIVE;

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Backup and Restore MySQL Database

The mysqldump is a backup program used to dump a database or collection of database for a backup
Syntax: Table level
$ mysqldump [options] db_name [tbble_name ...]
Database level
$ mysqldump [options] --databases database_name ...
All Databases
$ mysqldump [options] --all-databases

The mysqlimport is used to import the data into database.
Syntax:
$mysqlimport [options] db_name dumpfile1 [dumpfile2 ...]

You can load the dumpfile into server using mysql command.

Backup Mysql Database using mysqldump1. Take a backup of single database on the same server
$mysqldump -u user_name -d database_name –p > database.sql

2. Backup single database from remote server
$ mysqldump -u user_name -h host_name -d database_name –p > database.sql

3. If you are running more than one MYSQL instance on different ports them you need to mention the port to backup database
$ mysqldump -u user_name -h host_name -P port -d database_name –p > database.sql

4. Backup a table from a database
$ mysqldump -u username database_name table_name > table.sql

5. Backup all databases on the server as ROOT
$ mysqldump -u root --all-databases > all_databases.sql

Restore MySQL Database using “mysql” command
If you are moving the database from one server to another server or remotely then use below commands

1. Restore a database on the same server
$mysql -u user_name database_name –p < database.sql
Or
$ mysql -e "source /tmp/database.sql " database_name

2. Restore database from remote server
$ mysql -u user_name -h host_name database_name –p < database.sql

3. If you are running more than one MYSQL instance on different ports them you need to mention the port of Instance to restore
$ mysql -u user_name -h host_name -P port database_name –p < database.sql

4. Restore a table from a database
$ mysql -u username database_name < table.sql

5. Restore all databases from the dump as ROOT
$ mysql -u root < all_databases.sql

Restore database using mysqlimportThe database should exist in order to import dump using mysqlimport.
$ mysqlimport -u user_name -p password database_name /tmp/dumpfile.sql

Write MySQL table data to a file
Use below syntax to write table data/contents to a file
mysql> SELECT * FROM . ORDER BY DESC LIMIT 0,99000 INTO OUTFILE '/tmp/table_name.txt'

If you don’t have access to write from MySQL to a file then you will receive below error.
ERROR 1045 (28000): Access denied for user 'xxxxxxx'@'%' (using password: YES)

If you dont have permission the use below alternate option
$ echo "SELECT * FROM database_name.table_name" mysql -u -h --port= --password= > /tmp/table_name.txt

Rename Mysql Database
Either you can backup and restore the database into new database or follow below steps
mysql>create database new_database;
$ mysqldump -u user_name -d old_database -p | mysql -u user_name -d new_database -p
mysql> drop database old_database

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Tuesday, June 5, 2012

How to install and configure MySQL Database

Linux supports a number of different solutions for installing MySQL.
1. Install MySQL using RPM Package
2. Install MySQL using Native package manager
3. Install MySQL using Generic Binaries

If you are new to MySQL then i would recommend using RPM, otherwise install manually using Generic Binaries. The recommended way installing MySQL on PRM-based(RPM VsNative) Linux distribution is using RPM package.

Install MySQL using RPM PackageUse below command to see all files in an RPM package
$ rpm -qpl MySQL-server-VERSION.linux2.6.x86_64.rpm

Run the below command to install
$ rpm -i MySQL-server-VERSION.linux2.6.x86_64.rpm

The RPM installation creates a login account named mysql.

By default the RPM places the data under /var/lib/mysql directory and creates the appropriate entries in /etc/ini.d to start the server automatically at boot time.

MySQL Installation Layout for Linux RPM
/usr/bin - Client programs and scripts
/usr/sbin - The mysqld server
/var/lib/mysql - Log files, databases
/usr/share/info - Manual in Info format
/usr/share/man - Unix manual pages
/usr/include/mysql - Include (header) files
/usr/lib/mysql - Libraries
/usr/share/mysql - Miscellaneous support files, including error messages, character set files, sample configuration files, SQL for database installation
/usr/share/sql-bench – Benchmarks

Install My SQL using Native package managerThe native package installation will automatically take care of downloading and installing the software. Also it will take care of dependencies for MySQL Installation.

Use yum command to install MySQL.
# yum install mysql mysql-server mysql-libs mysql-server

An init script will be installed into /etc/init.d/mysqld, this script is used to stop and start the server. A configuration file will be created under /etc/mysql/my.cnf

Use below commands to Stop and Start MySQL service
# service mysql start
# service mysql stop

If you want to start MySQL server automatically during the boot run below command.
# chkconfig --levels 235 mysqld on

Here are some popular MySQL Download links

Download MySQL Community Serverhttp://dev.mysql.com/downloads/mysql/
Download MySQL Clusterhttp://dev.mysql.com/downloads/cluster/
Download MySQL Workbench GUI Toolhttp://dev.mysql.com/downloads/workbench/5.2.html
Download MySQL Proxyhttp://dev.mysql.com/downloads/mysql-proxy/
Download MySQL Connectorshttp://dev.mysql.com/downloads/connector/

Regards
Satishbabu Gunukulahttp://www.oracleracexpert.com/