I am a simple Senior Oracle DBA Consultant....

Oracle Certified (Database 9i/10g/11g
& Application 11i), dynamic, self-motivated and results-oriented
professional with over 16 years of broad- based experience in Database
Management, Administration, Backup & Recovery, Tuning and Oracle
Application Concepts and Architecture using Oracle Technologies (Database,
Oracle Application and Middleware). Expertise in high-technology time and cost
based solutions, quality customer service and commitment to excellence.

Wednesday, August 27, 2014

Oracle 12c - Move datafile / tempfile

Oracle 12c with another great feature, moving datafile / tempfile.

Unfortunately TEMPFILES can't be moved, but can be dropped and recreated OR recreated (with another file name) and dropped.

WHILE THE DB is ON-LINE,

1) alter database move datafile <source destination - full path within single quotes> to <target destination - full path within single quotes> ;

IT does everything on the fly, great feature and handy for all DBA's.

for more info http://docs.oracle.com/database/121/ADMIN/dfiles.htm#ADMIN13837




Oracle 12c - Rman Duplicate

Oracle 12c has lot of fantastic features, one of them is restart the failed RMAN DUPLICATE command.  This feature is available from 11g, but didn't have a oppurtunity to use that.

I was copying a big database (not very big, it is about 2TB size) from Production site to Disaster Recovery (DR) site.  I think, many of you know that or experienced the limited network bandwidth between Prod and DR sites.

RMAN Duplicate statement come in handy to create a Auxilary (target db) database from Source (source db).

I have used the SECTION SIZE parameter in my duplicate command which delivers good performance as per Oracle documentation (http://docs.oracle.com/database/121/BRADV/rcmdupdb.htm#BRADV427).

Actually I was doing copy from single filesystem to multiple file system (like data/index/redo/sort separate).  Somehow my calculation went wrong and my datafile size was more than what I have been allocated in my Auxiliary instance.

After copying 1.9TB, I see the Auxiliary file system was 100% full.  I didn't have luxury of time to restart the copy again, as the copy was running more than 12 hours and a team is testers waiting for me to complete.  Many of the DBA's may be this kind of situation often, but still enjoy as a DBA :()

So now the Duplicate failed with disk space/write error.  I have asked the UNIX admin to increase the 100% full file system.

NOW, All we need to RESUME (good to know there is) the duplicate where it is failed is do the following,

1) close your RMAN session - exit
2) shutdown immediate - Auxiliary instance
3) Startup Auxiliary with NOMOUNT option using the original INIT<DB_NAME>.ora file
4) Duplicate does everything as normal then it finds the Auxiliary already copied data files and starts where it failed last (what a great feature).
5) RMAN duplicate finishes fast.

Deliver the DB on time to testers...