How to move all types of datafiles from one location to another in oracle 11g

Oracle Version: 11g
OS Version: Rhel 6

Step 1. Check the datafiles location & tablespace name .
SQL> column file_name format a50
SQL> select file_name , tablespace_name from dba_data_files;
FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/prim/users01.dbf           USERS
/u01/app/oracle/oradata/prim/undotbs01.dbf         UNDOTBS1
/u01/app/oracle/oradata/prim/sysaux01.dbf          SYSAUX
/u01/app/oracle/oradata/prim/system01.dbf          SYSTEM
/u01/app/oracle/oradata/prim/example01.dbf         EXAMPLE
/u01/app/oracle/oradata/prim/GHHSTORE_DATA_TBS.dbf GHHSTORE_DATA_TBS

SQL> select file_name , tablespace_name from dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/prim/temp01.dbf            TEMP

Step 2.Shutdown the database and  move the data files into new location.

SQL>shut immediate;
SQL>! mv /u01/app/oracle/oradata/prim/users01.dbf /u02/oradata/users01.dbf
SQL>! mv /u01/app/oracle/oradata/prim/undotbs01.dbf /u02/oradata/undotbs01.dbf
SQL>! mv /u01/app/oracle/oradata/prim/sysaux01.dbf  /u02/oradata/sysaux01.dbf
SQL>! mv /u01/app/oracle/oradata/prim/system01.dbf /u02/oradata/system01.dbf
SQL>! mv /u01/app/oracle/oradata/prim/GHHSTORE_DATA_TBS.dbf /u02/oradata/GHHSTORE_DATA_TBS.dbf
SQL>! mv /u01/app/oracle/oradata/prim/temp01.dbf /u02/oradata/temp01.dbf
Step 3. Start the database in mount point and rename the datafiles.

SQL> alter database rename file '/u01/app/oracle/oradata/prim/users01.dbf' to
'/u02/oradata/users01.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/prim/undotbs01.dbf' to
'/u02/oradata/undotbs01.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/prim/sysaux01.dbf' to
'/u02/oradata/sysaux01.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/prim/system01.dbf' to
'/u02/oradata/system01.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/prim/GHHSTORE_DATA_TBS.dbf' to
'/u02/oradata/GHHSTORE_DATA_TBS.dbf' ;

SQL> alter database rename file '/u01/app/oracle/oradata/prim/temp01.dbf' to
'/u02/oradata/temp01.dbf';

Step 4.Open the database and check datafile location.
SQL>alter database open;
SQL> column file_name format a50
SQL> select file_name , tablespace_name from dba_data_files;

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u02/oradata/users01.dbf             USERS
/u02/oradata/undotbs01.dbf           UNDOTBS1
/u02/oradata/sysaux01.dbf           SYSAUX
/u02/oradata/system01.dbf             SYSTEM
/u02/oradata/example01.dbf           EXAMPLE
/u02/oradata/GHHSTORE_DATA_TBS.dbf   GHHSTORE_DATA_TBS

SQL> select file_name , tablespace_name from dba_temp_files;

FILE_NAME                          TABLESPACE_NAME
---------------------------------- ------------------------------
/u02/oradata/temp01.dbf            TEMP

Done..

Incase if we cant shutdown the database, we can still perform the activity except for system datafile
and undo datafile.However we can change the default undo tablespace by creating a new undo tablespace
and drop the old one.

For users or any non-default system datafile:-
SQL> alter tablespace users offline;
SQL> alter database rename file '/u01/app/oracle/oradata/prim/users01.dbf' to
'/u02/oradata/users01.dbf';
SQL>  alter tablespace users online;

SQL> select file_name , online_status , tablespace_name from dba_data_files where tablespace_name='USERS';

FILE_NAME                                          ONLINE_ TABLESPACE_NAME
-------------------------------------------------- ------- ------------------------------
/u02/oradata/users01.dbf                           ONLINE  USERS

For sysaux datafile:-
SQL> alter tablespace sysaux offline;

Tablespace altered.

SQL> ! mv /u01/app/oracle/oradata/prim/sysaux01.dbf /u02/oradata/sysaux01.dbf;
SQL> alter database rename file '/u01/app/oracle/oradata/prim/sysaux01.dbf' to '/u02/oradata/sysaux01.dbf';

Database altered.

SQL> alter tablespace sysaux online;

SQL> select file_name , online_status , tablespace_name from dba_data_files where tablespace_name='SYSAUX';

FILE_NAME                                          ONLINE_ TABLESPACE_NAME
-------------------------------------------------- ------- ------------------------------
/u02/oradata/sysaux01.dbf                          ONLINE  SYSAUX


For temporary datafile:-

SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_TEMP_FILES;

FILE_NAME                                 TABLESPACE_NAME         STATUS
---------------------------------------- ----------------------  -------
/u01/app/oracle/oradata/prim/temp01.dbf      TEMP                   ONLINE

SQL> alter database tempfile '/u01/app/oracle/oradata/prim/temp01.dbf' offline;

Database altered.

SQL> ! mv /u01/app/oracle/oradata/prim/temp01.dbf /u02/oradata/temp01.dbf

SQL> alter database rename file '/u01/app/oracle/oradata/prim/temp01.dbf' to
'/u02/oradata/temp01.dbf';

Database altered.
SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_TEMP_FILES;

FILE_NAME                                 TABLESPACE_NAME         STATUS
---------------------------------------- ----------------------  -------
/u02/oradata/temp01.dbf       TEMP                    OFFINE

SQL> alter database tempfile '/u02/oradata/temp01.dbf' online;

Database altered.
SQL> select file_name, tablespace_name , status from dba_temp_files;

FILE_NAME                                TABLESPACE_NAME                STATUS
---------------------------------------- ------------------------------ -------
/u02/oradata/temp01.dbf                  TEMP                           ONLINE


For system Datafile:-
We cant offline a system tablespace. so for the we need to shutdown the database and then
move it to the new location as describe before.


For undo datafile:-
We cant drop or move default undo tablespace.
First create a new undo tablespace and then drop the old one.
SQL> create undo tablespace undotbs2 datafile '/u02/oradata/undotbs01.dbf' size 100M;

Tablespace created.
SQL>  alter system set undo_tablespace= undotbs2 ;
SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL>select file_name , online_status , tablespace_name from dba_data_files where tablespace_name like 'UNDO%';

FILE_NAME                                ONLINE_ TABLESPACE_NAME
---------------------------------------- ------- ------------------------------
/u02/oradata/undotbs01.dbf               ONLINE  UNDOTBS2





Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

How to move redolog files from one location to another in oracle 11g

Oracle Version :11g
OS: Rhel 6


Step 1:-Determine current location for redolog files.

SQL> column member format a40
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                   IS_RECOVERY_DEST_FILE
---------- ------- ------- ---------------------------------------- ---------------------
         3         ONLINE  /u01/app/oracle/oradata/prim/redo03.log  NO
         2         ONLINE  /u01/app/oracle/oradata/prim/redo02.log  NO
         1         ONLINE  /u01/app/oracle/oradata/prim/redo01.log  NO

Step 2.Shutdown the database and move the redolog files into new location.

SQL>shut immediate;
SQL>! mv /u01/app/oracle/oradata/prim/redo03.log /u02/redo03.log
SQL>! mv /u01/app/oracle/oradata/prim/redo02.log /u02/redo02.log
SQL>! mv /u01/app/oracle/oradata/prim/redo01.log /u02/redo01.log

Step 3.Start the database in mount point and rename the redolog files .

SQL> startup mount
SQL> alter database rename file '/u01/app/oracle/oradata/prim/redo03.log' to '/u02/redo03.log';
SQL> alter database rename file '/u01/app/oracle/oradata/prim/redo02.log' to '/u02/redo02.log';
SQL> alter database rename file '/u01/app/oracle/oradata/prim/redo01.log' to '/u02/redo01.log';

Step 4. Open the database and check new location of redolog files.

SQL>alter database open;
SQL> column member format a40
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER           IS_RECOVERY_DEST_FILE
---------- ------- ------- ---------------- ---------------------
         3         ONLINE  /u02/redo03.log  NO
         2         ONLINE  /u02/redo02.log  NO
         1         ONLINE  /u02/redo01.log  NO

Done..




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

bash_profile setting for different oracle versions installed in a single server.

vi /home/oracle/.bash_profile

umask 022
export ORACLE_BASE=/u01/app/oracle
export TNS_ADMIN=$ORACLE_BASE/product/11.2.0/db_1/network/admin

10g()
{       export ORACLE_SID=prim10g
        export ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
        export LD_LIBRARY_PATH=$ORACLE_HOME/lib
        export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
        export PS1='[\u@\h\W]\$ '
}

11g()
{       export ORACLE_SID=prim11g
        export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
        export LD_LIBRARY_PATH=$ORACLE_HOME/lib
        export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
        export PS1='[\u@\h\W]\$ '
}

12c()
{       export ORACLE_SID=prim12c
        export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
        export LD_LIBRARY_PATH=$ORACLE_HOME/lib
        export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
        export PS1='[\u@\h\W]\$ '
}

--save and quit (:wq)

[oracle@server1 ~]$ . .bash_profile

Simply type "10g" to set 10g environment, "11g" for 12c, and so on.

Table replication using Materialized View in oracle 11g

Source Database:prim
Target Database:-stand


Step 1.create the USERS table and add some sample records inside.

At source database:-
[oracle@server1 ~]$ sqlplus sys/system as sysdba@prim
SQL> create user appuser identified by appuser;
SQL> GRANT CONNECT,RESOURCE TO appuser;
SQL> conn appuser/appuser
Connected.

SQL> insert into users values (1 ,'RAM','DAS','REDHAT');
SQL> insert into users values (2, 'SHYAM','SINGH','SUSE');
SQL> insert into users values (3, 'ROHAN','SHARMA','UBUNTU');
SQL> COMMIT;
Commit complete.

At target database:-
Step 2.Now before creating database link we need to add entry inside tnsnames.ora file
so that the target database can connect to the source database.

[oracle@server1 ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
STAND =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server2.soumya.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stand)
    )
  )

prim =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1.soumya.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRIM)
    )
  )
 
 :wq
 so we added the tnsentry for source database i.e. prim inside tnsnames.ora file

Step 3.Now connect to the local database i.e. target and create a user appuser2

[oracle@server2 ~]$ sqlplus sys/system as sysdba@stand
SQL> create user appuser2 identified by appuser2;
SQL> grant connect , resource , create database link , create materialized view to appuser2;
SQL> conn appuser2/appuser2
Connected.

At target database:-
Step 3.Now create a database link to connect source database from appuser2
[oracle@server2 ~]$ sqlplus appuser2/appuser2@stand
SQL> CREATE DATABASE LINK stand CONNECT TO appuser IDENTIFIED BY appuser USING 'prim';
CREATE DATABASE LINK stand CONNECT TO appuser IDENTIFIED BY appuser USING 'prim'
                           *
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

While creating the database link i was facing the above error and i fixed it using the below steps
SQL>select * from global_name;
STAND
-- write down the current name;

SQL> Alter database rename global_name to tttt.xxx;

SQL> CREATE DATABASE LINK mylink CONNECT TO appuser IDENTIFIED BY appuser USING 'prim';

Rename global name back to original from the query above.

SQL> Alter database rename global_name to STAND.soumya.com;

Now You must be able to Select any table from user APP1 On Source Database from target database.
At target database:-
[oracle@server2 ~]$ sqlplus appuser/appuser@prim
SQL> select * from users;

   USER_ID FIRST_NAME LAST_NAME                      PASSWORD
---------- ---------- ------------------------------ --------------------
         1 RAM        DAS                            REDHAT
         2 SHYAM      SINGH                          SUSE
         3 ROHAN      SHARMA                         UBUNTU


Step 4.Now we will create materialized view On target Database:-
SQL> CREATE MATERIALIZED VIEW V_USERS REFRESH NEXT
  SYSDATE+INTERVAL '10' MINUTE WITH ROWID AS SELECT * FROM users@mylink;

Step 5. Now test the scenario:-
[oracle@server2 ~]$ sqlplus appuser2/appuser2@stand
At target database:-
SQL> select count(*) from V_USERS;
It Must Give you same Number Of Row In appuser@prim , If you want to test synchronization
All you have to do is

At source database:-
SQL> conn appuser/appuser
SQL> INSERT INTO USERS VALUES (4, 'ROBERT', 'ASPRIN', 'PASS4')

And then check at target database after 10mins.It would be in sync.

Error in invoking target 'rat_on part_on dm_on olap_on sdo_on' while installing oracle 11g

Today while i was browsing on OTN i found a thread related to oracle 11gR2 Installation , the OP faced an error like the below :-


The above error related to Space On Temp File system all you have to do make sure that there is enough space on temp ( tmp ) or use the below command :-


export TMP=/u01/Foldername
export TMPDIR=/u01/Foldername

Try again now.It should resolve the error