Export table in oracle

Export table in oracle : 

Command for expdp a single table in oracle :-

vi Export.par

USERID='/ as sysdba'

DIRECTORY=EXPORT

DUMPFILE=15Mar_SCOTT_SCHEDULED_REQUESTS%U.dmp

logfile=15Mar_SCOTT_SCHEDULED_REQUESTS.log

tables=SCOTT.SCOTT_SCHEDULED_REQUESTS

filesize=2g

flashback_time="to_timestamp(to_char(SYSDATE-1/8640,'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS')"

Export DB_LINKS in oracle : 

userid='/ AS SYSDBA'
directory=CLONING_8FEB
content=metadata_only
full=y
include=db_link
logfile=expdp_db_link_8Feb_cloning.log

How to Export full Database FAST :

USERID='/ as sysdba'

DIRECTORY=qa_test

DUMPFILE=XYZ%U.dmp

logfile=XYZ.log

schemas=QA_TEST

filesize=2g

flashback_time="to_timestamp(to_char(SYSDATE-1/8640,'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS')"

exclude=statistics  --- this will reduce the time of export

After import gather stats of the database.

Understanding undo in oracle database

Undo in oracle is one of the most confusing concept, which most DBA's find difficult to understand. First of all what is undo...?... In simple terms to revert/undo changes that you have done.

But first as per Oracle definition " Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it"

In simple terms undo means to revert/undo changes that you have done, but when we are talking about undo w.r.t the database it has many more things.

Undo is the old data which database requires for read consistency. We will try to understand this with an example :

1) User A has created a table Employee.
2) User A has inserted 10 rows in the table and commit the database. Now after committing this data has been written by LGWT  in redo log i.e. this becomes permanent.
3) User A again inserted 10 more rows but haven't committed yet.
4) As we know oracle is an OLTP system and many users can manipulate the same data at same time.
    User B wants to read the data of Employee table.

Here comes the concept of read consistency. For User A Employee table has 20 Rows but up to the first 10 rows data is committed i.e permanent but what about the other 10 rows, these are not yet committed. What if User A deletes that row and then again enter a commit.

Oracle can only show User B data that is committed/permanent. This is read consistency.

Oracle definition " Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it"

Here 10 rows data is before the image of the employee table which oracle has to maintain at some place so that while user is changing that data still another user can read/access that particular data. For this Oracle manage Undo records.

This is the basic crux of Undo.

Thanks and Enjoy Learning!!

Block Chain Tracking in oracle

Hello Everyone

This is my first blog. Through this blog, I will try to share my learning as Oracle DBA. In my first ever blog, I am going to discuss something that is a new concept I learned recently.

The topic is BlockChain Tracking:

We have encountered a problem in our day to day operations, where the application team was facing slowness in some program/PL-SQL code that the program used to finish at 3:00 AM but from past 2 to 3 days, it is not completing at the usual time i.e. 3:00 AM. So we started to dig the problem, in our organization we don't have Tuning and Diagnostics pack so the only way to dig down the issue was through spreports. Application teams gave us a Good time and Bad time.

So after going through the spreports of good as well as a bad time, we came to know that the most time-consuming wait event was Db Sequential file read. This wait event is IO wait, it means there is some issue related to Disk. Upon thorough checking of disks Using OS Watcher, we saw high Disk IO.

In meeting the Storage team was asked to look into the storage i.e. Disks.  They after investigation told us that two rman cron jobs were taking lots of time and consuming disk IO. After comparing old backup time and recent backup time we saw a huge difference in the backup completion time.

Here we have done the troubleshooting part. Now its time for performance tuning. How to tune Rman Backups so that there are fewer IO.

Steps that were taken by us :


1) We rescheduled cron jobs timing, so that no two backups run at the same time.


2) Second, we enabled Blockchain Tracking in the Database.

Now Question arises what is Blockchain tracking and How to enable that?

What Blockchain Tracking does is, it makes a file in oracle ASM, whose work is to store the information of changed block in the datafiles. All the changed block information is stored in this file and rman doesn't have to read all the changed blocks in all the datafiles, now rman has to read a single file and store that information of changed blocks for the Incremental 1 backup.

Once we enable the Blockchain tracking in the database. The first time Level 0 Incremental backup takes the usual time and stores all the blocks information in the blockchain trace file. Once we have the information of blocks from level 0 backup, we will start getting the fruits of the blockchain tracking file.


Here from now onwards whenever any block in any datafile is updated or changed. That changed block information will get stored in the Blockchain track file and during Incremental level 1 rman backup, rman only have to read this blockchain track file resulting in the reduction of Disk IO.


Now How to enable Blockchain tracking on the database :

alter database enable block change tracking;

Query to check whether Blockchain tracking got enabled or not :

select status, filename from v$block_change_tracking;


Thanks and Enjoy Reading!!