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...

dbms_stats.gather_table_stats(....degree=>6....,cascade=>true)

Hi all,

Today I was given with a question, why a dbms_stats with degree=>6 created 12 parallel process instead of 6 process.

I was wondering, how come Oracle create 12 process where as degree is only 6 process.  I think many of you might know already.

Oracle creates 6 parallel process as requested, but because of "cascade=>true", it spawns another 6 process to collect statistics from Index.

Oracle uses the following hint to run the stats collect process.

/*+ PARALLEL(t, 6) PARALLEL_INDEX(t,6) */

ORA-00001: unique constraint(schema.table_name) violated

I faced an error from a production ERP system, which the trace file shows "Unique constraint violated".  Actually this process inserts rows from temporary table to target table.  The temporary table has got 1000 of rows.  Easy way to check the error,

1) check which columns are part of the unique constraints

    select constraint_name,constraint_type,search_condition from dba_constraints where table_name=<TABLE_NAME>;

In my case the target table has got check constraints, so can't find it in dba_constraints.

2) check unique index for the target table

    select index_name,uniqueness from dba_indexes where table_name=<TABLE_NAME>;
    select column_name,column_position from dba_ind_columns where index_name=<PREVIOUS STEP VALUE>;

3) check target table rows compare with temporary table rows for the specific unique index columns.

4) Correct the data.

Modify table with few additional columns with not null constraint and default value

There was a new requirement to add few columns to a big table (few gigs in size) with not null constraints and default value.

The developer came up with a script and asked me to review,

alter table T1 add F1 varchar2(10);
alter table T1 add F2 varchar2(10);
alter table T1 add F3 varchar2(10);

update T1 set F1=' ',F2=' ',F3=' ';


alter table T1 modify F1 not null;
alter table T1 modify F2 not null;
alter table T1 modify F3 not null;



Oracle has fantastic feature to do all the above steps in one go ....:()   Here you go....

alter table T1 add (F1 varchar2(10) default ' ' not null, F2 varchar2(10) default ' ' not null, F3 varchar2(10) default ' ' not null);

The above DDL ran faster and easy to manage it.

How to move a index from one tablespace to another

How to move index from tablespace to another tablespace online.

SQL>

OWNER                          INDEX_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------ ---------------

SYS                                  TEST_SC                               OLD_INDEX

I want to move TEST_SC which is wrongly created in OLD_INDEX tablespace.  User want to move to NEW_INDEX tablespace,

SQL>alter index <INDEX_NAME> rebuild tablespace <NEW TABLESPACE> PARALLEL NOLOGGING;

SQL>alter index TEST_SC rebuild tablespace NEW_INDEX PARALLEL;

SQL>

OWNER                          INDEX_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------ ---------------

SYS                                  TEST_SC                               NEW_INDEX

User carefully with NOLOGGING option if you have DR. 

If you are moving large index, check in V$SESSION_LONGOPS to see how much it progressed and how long it will take (approx).

Interesting in GoldenGate, I have requirement from a client to store the source transaction ROWID in target table. I have been through the documentation and reference guides, end up not finding anything interesting and stright forward. But managed to find the solution and here you go and enjoy,

It is managed by GG tokens, those who don't know GG, please refer the best Oracle Documentation

I have created the source extract parameter file with

TKN-ROW_ID = @GETENV("RECORD", "rowid")

and created my traget replicat process parameter file with

ROW_ID = @TOKEN("TKN-ROW_ID"))

It did the magic and I could see the target records as follows,

SCHEMA ROW_ID EMP_ID-------------------- ------------------------------ ----------SRC AACZoBAAXAAAACVAAF 550001334SRC AACZoBAAXAAAACVAAG 440001334SRC AACZoBAAXAAAACVAAH 2220001334SRC AACZoBAAXAAAACWAAA 990001334SRC AACZoBAAXAAAACWAAB 100001334SRC AACZoBAAXAAAACWAAC 70001334

Enojoy.....if you find this update useful....leave your comment.....