How to change the location of Data file used by a Table space in Oracle?

 How to change the location of Datable used by a Table space in Oracle?
Process to change the location of TABLE SPACE file in oracle :
Note : The below steps are very sensitive; not run by normal users. It is recommended to run with the help of Oracle DBA prior taking backups. If any problem happens the old data might be lost.
 1. Confirm and note down the existing Data file location for both  CDS_TEMP_TS and CDS_TS by running the SQL commands.
     Ex : current Data file locations are
  •      C:\ORACLE\PRODUCT\10.2.0\DB\DATABASE\CDS_TEMP_TS.DBF
  •      C:\ORACLE\PRODUCT\10.2.0\DB\DATABASE\CDS_TS.DBF
 2. Down the Oracle instance by running below command in the SQL prompt.
  • SQL > shutdown immediate;
 3. Now identify the new location where you want to maintain your new data files; and move the CDS_TEMP_TS.DBF and CDS_TS.DBF files from old location to new location manually.
     Ex : For new location of Data files.
  • C:\DCD_DB\CDS_TEMP_TS.DBF
  • C:\DCD_DB\CDS_TS.DBF 
 4. Come out of the sql prompt and re login to SQL prompt separately
slplus sys as sysdba; Provide password and connect to idle instance.
 5.start the Oracle and mount it with the below command
  • SQL> startup mount;ORACLE instance started.Total System Global Area  612368384 bytes
    Fixed Size                  1250428 bytes
    Variable Size             188746628 bytes
    Database Buffers          415236096 bytes
    Redo Buffers                7135232 bytes
    Database mounted.
     6. Now run the below commands at SQL> to associate the new data file location with database.
  • alter database rename file 'C:\oracle\product\10.2.0\db\database\CDS_TEMP_TS.DBF' to 'C:\DCD_DB\CDS_TEMP_TS.DBF';
  • alter database rename file 'C:\oracle\product\10.2.0\db\database\CDS_TS.DBF' to 'C:\DCD_DB\CDS_TS.DBF';
  • alter database open;
 7. Now exit the existing SQL prompt and relogin to new SQL prompt.
  • sqlplus "sys/oracle@orcl as sysdba" 
 8. Now check the new Data file locations by running the below commands at SQL prompt; to confirm the usage of new Data file locations by your database.
  •  SQL> select tablespace_name,file_name from dba_temp_files where tablespace_name LIKE 'CDS%';
  •  SQL> select tablespace_name,file_name from dba_data_files where tablespace_name LIKE 'CDS%'; 
 9. Now Start your DCD and check the working of DCD and observe the old data as well.

Where are Oracle logs Reflecting this ? 

SQL> show parameter dump;
NAME                                 TYPE        VALUE
----------------------------------- --------- -----------------------------
background_core_dump                 string      partial
background_dump_dest                 string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \ORCL\BDUMP
core_dump_dest                       string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \ORCL\CDUMP
max_dump_file_size                   string      UNLIMITED
shadow_core_dump                     string      partial
user_dump_dest                       string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \ORCL\UDUMP
SQL>
C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\bdump
check the " alert_orcl.log " which shows the commands and details used to create CDS_TEMP_TS and CDS_TS.

Reference : http://www.orafaq.com/wiki/Move_datafile_to_different_location 

Read more

Popular Posts

Enter your email address:

Buffs ...

Tags


Powered by WidgetsForFree