About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Friday, April 26, 2013

ONLINE Table Redefinition

Yesterday I have restructured one of my normal table to partitioned table. There are two ways to do that :-

1. Export - Import method.
2. Online table Redefinition

1. Export - Import Method :-

This method can be used when we can take downtime of that table :-

a. Export the normal table

expdp user/password directory=datapump dumpfile=expdp_table.dmp logfile=expdp_table.log tables=TEST compression=all &

b. create the partitioned table as the same structure of normal table

c. Drop the normal table

d . Import the dumpfile to the Partition table.

impdp user/password directory=datapump dumpfile=expdp_table.dmp logfile=impdp.log &

 2. Online table Redefinition :-

This method is used when downtime option is not available with us. But to redefine a table by online table redefinition , table must have a primary key.

Consider a table named test has to be redefined. PFB steps:-

1. create a partitioned table having same structure as TEST table has.

SQL> create table test_test (
   2  id number(23),
  3  name varchar2(12),
  4  systemdate date,
  5  id_old number(12)
  6  )
  7  partition by list(id)
  8  (
  9  partition par_01 values (1,2,3),
 10  partition par_02 values (4,5,6),
 11  partition par_03 values (7,8,9),
 12  partition par_04 values (10,11,12)
 13  );

Table created.

2. Check whether table TEST can be redefined or not.if table which has to be redefined don't have any issues then it will give you successful message else error message.

SQL> EXEC DBMS_REDEFINITION.can_redef_table('AMIT','TEST');

PL/SQL procedure successfully completed.
######### TEST table can be redefined #################


SQL> EXEC DBMS_REDEFINITION.can_redef_table('AMIT','TEST_TEST');
BEGIN DBMS_REDEFINITION.can_redef_table('AMIT','TEST_TEST'); END;

*
ERROR at line 1:
ORA-12089: cannot online redefine table "AMIT"."TEST_TEST" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 139
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1782
ORA-06512: at line 1
############## TEST_TEST table cannot be redefined due to above error##########

3. Start the redefinition :-

SQL> EXEC DBMS_REDEFINITION.start_redef_table('AMIT','TEST','TEST_TEST');

PL/SQL procedure successfully completed.


SQL> SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
SQL>   2    3  BEGIN
 4   DBMS_REDEFINITION.copy_table_dependents(
 5   uname             => 'AMIT',
 6   orig_table        => 'TEST',
 7   int_table         => 'TEST_TEST',
 8   copy_indexes      => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
 9   copy_triggers     => TRUE,  -- Default
10   copy_constraints  => TRUE,  -- Default
11   copy_privileges   => TRUE,  -- Default
12   ignore_errors     => FALSE, -- Default
13   num_errors        => l_num_errors);
14   DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
15  END;
16  /
l_num_errors=0

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.sync_interim_table('AMIT','TEST','TEST_TEST');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.finish_redef_table('AMIT','TEST','TEST_TEST');

PL/SQL procedure successfully completed.


SQL> SELECT object_name, object_type, status FROM dba_objects WHERE owner = 'AMIT' and object_name='TEST';

OBJECT_NAME                                  OBJECT_TYPE                         STATUS
---------------------------------         ---------------------------------          ---------------------


TEST                                                   TABLE                                        VALID

TEST                                                  TABLE PARTITION                     VALID

TEST                                                 TABLE PARTITION                     VALID

TEST                                                 TABLE PARTITION                     VALID

TEST                                                 TABLE PARTITION                     VALID


SQL> select index_name ,table_name,column_name from user_ind_columns where table_name='TEST';

INDEX_NAME                            TABLE_NAME                    
COLUMN_NAME

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


TEST_IDX                                   TEST                                   ID_OLD

TEST_PK                                    TEST                                   ID



SQL>  select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='TEST';

TABLE_NAME                       PARTITION_NAME                                             TABLESPACE_NAME                               NUM_ROWS
---------------------------------     ------------------------------------------           ------------------------------                                          ------------------------
TEST                                                     PAR_01                                                USERS
TEST                                                     PAR_02                                                USERS
TEST                                                     PAR_03                                                USERS
TEST                                                    PAR_04                                                 USERS



SQL> exec dbms_stats.gather_table_stats('AMIT','TEST',granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='TEST';


TABLE_NAME                           PARTITION_NAME                                TABLESPACE_NAME                                        NUM_ROWS
-----------------------------------     ------------------------------------------    --------------------------------------------                                          ------------------

TEST                                            PAR_01                                    USERS                                                                                        3
TEST                                            PAR_02                                    USERS                                                                                        1
TEST                                            PAR_03                                    USERS                                                                                        2
TEST                                            PAR_04                                     USERS                                                                                        0

Once data reflects in our partitioned table . Drop the newly created table i,e TEST_TEST

I hope this article helped you.

Regards,
Amit Rath

ORA-12547: TNS:lost contact

Yesterday I faced a issue in my Oracle database.When I am trying to connect through oracle user( user with which database installed) I am able to connect successfully, but when through a different user of the same group as of oracle user I am getting below mentioned error :-

bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 25 14:47:51 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: amit/amit
ERROR:
ORA-12547: TNS:lost contact

Enter user-name:
bash-3.2$ id
uid=207(amit) gid=205(oinstall) groups=206(dba)
bash-3.2$

But When I am trying to connect through oracle user :-

bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 26 12:37:51 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: amit
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
bash-3.2$ id
uid=212(oracle) gid=205(oinstall) groups=206(dba)
bash-3.2$

I checked all tnsnames.ora , listener.ora, sqlnet.ora , all are fine . Then I checked permission of oracle file in $ORACLE_HOME/bin directory and I get this :-

bash-3.2$ cd $ORACLE_HOME/bin
bash-3.2$ ls -ltr oracle
-rwxr-xr-x    1 oracle   oinstall  271812857 May 29 2012  oracle
bash-3.2$

Change the permission of oracle file with oracle user :-

bash-3.2$ id
uid=212(oracle) gid=205(oinstall) groups=206(dba)
bash-3.2$
bash-3.2$ cd $ORACLE_HOME/bin
bash-3.2$ ls -ltr oracle
-rwxr-xr-x    1 oracle   oinstall  271812857 May 29 2012  oracle

bash-3.2$ chmod 6751 oracle
bash-3.2$ ls -ltr oracle
-rwsr-s--x    1 oracle   oinstall  271812857 May 29 2012  oracle

Now try to connect through amit user 

bash-3.2$ id
uid=207(amit) gid=205(oinstall) groups=206(dba)
bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 26 13:23:54 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: amit
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Usually this operation solves this problem , still we further investigate permission of other files to avoid further problems. These special rights of Oracle binaries are set by $ORACLE_HOME/root.sh script on Unix/Linux after the Oracle Installation. We can run this script again as root user to set the permissions , if we see more files have wrong permissions or ownership.

I hope this article helped you.

Regards,
Amit Rath

Thursday, April 18, 2013

How to move datafiles to another location

Sometimes a DBA might need to move database datafiles from one location to another. There can be various reasons for this movement :-

1. Change/Rectify incorrect naming of the datafiles.
2. I/O balancing on File systems due to performance reasons.

Datafiles can be moved to another location in two ways :-

1. Database is in online mode :-

SQL> select open_mode from v$database;

OPEN_MODE
------------------------------------------------------------
READ WRITE

SQL> alter database datafile '/disk2/oracle/oradata/amit/amit/test.dbf' offline;

Database altered.


SQL> !
$ bash
bash-3.2$ cd /disk2/oracle/oradata/amit/amit/
bash-3.2$ mv test.dbf /disk1/oracle/oradata/amit/amit
bash-3.2$
bash-3.2$
bash-3.2$ exit
exit
$
$ exit

SQL> alter database rename file '/disk2/oracle/oradata/amit/amit/test.dbf' to '/disk1/oracle/oradata/amit/amit/test.dbf';

Database altered.

SQL> recover datafile '/disk1/oracle/oradata/amit/amit/test.dbf';
Media recovery complete.
SQL>
SQL> alter database datafile '/disk1/oracle/oradata/amit/amit/test.dbf' online;

Database altered.

Note :- IN online mode we cannot rename or move system datafiles. To do that PFB link :-
Renaming/Moving System datafiles

2. Database is in offline mode :-

SQL> select open_mode from v$database;

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> !
$ bash
bash-3.2$ cd /disk2/oracle/oradata/amit/amit/
bash-3.2$ mv test.dbf /disk1/oracle/oradata/amit/amit
bash-3.2$
bash-3.2$
bash-3.2$ exit
exit
$
$ exit

SQL> startup mount
ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             331350480 bytes
Database Buffers          197132288 bytes
Redo Buffers                5804032 bytes
Database mounted.


SQL> alter database rename file '/disk2/oracle/oradata/amit/amit/test.dbf' to '/disk1/oracle/oradata/amit/amit/test.dbf';

Database altered.

SQL> alter database open;

Database altered.


I hope this article helped you.

Regards,
Amit Rath