Skip to main content

Moving Datafile From One Location To Another Location in Oracle 10G db.



The below are the steps. I have followed.

The database is in no archive log mode.

E:\Documents and Settings\KRISHNA>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 29 22:55:36 2014

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database;

NAME
---------
RISHI

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\SYSTEM01.DBF

Here I will be changing USERS01.DBF file location.

SQL> select open_mode,log_mode from v$database;

OPEN_MODE  LOG_MODE
---------- ------------
READ WRITE NOARCHIVELOG

SQL> select name from v$database;

NAME
---------
RISHI

SQL> shut immediate;

 Database closed.
Database dismounted.
ORACLE instance shut down.

NOTE: Once database shuts down, copy the data file USERS01.DBF from old location to new location & open the database in MOUNT stage & Issue the ALTER RENAME like as below.

SQL> startup mount

ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             171969412 bytes
Database Buffers          432013312 bytes
Redo Buffers                7135232 bytes
Database mounted.


SQL> alter database rename file  'E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\USERS01.DBF' to 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\USE
RS01.DBF';

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

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


SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\USERS01.DBF  - Here we can location change.
E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\RISHI\SYSTEM01.DBF

SQL> exit


Here we are done :)

Thank You !
Labels:

Comments

Post a Comment

Popular posts from this blog

ORA-39014: One or more workers have prematurely exited.ORA-00018: maximum number of sessions exceeded

ERROR: I was Performing a full database import and during the import I faced the below error. ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 6 with process name "DW07" prematurely terminated ORA-31672: Worker process DW07 died unexpectedly. Job "SYSTEM"."SYS_IMPORT_FULL_04" stopped due to fatal error at 00:59:40 ORA-39014: One or more workers have prematurely exited. SOLUTION:  Run the import with fewer parallel processes, like PARALLEL=2 instead of 8. I was able to run the import successfully. NOTE 1: This errors occurs when there are less session allocation in the database. check the session,process parameters and increase them accordingly. To avoid such errors again. NOTE 2 : Note: Increasing processes parameter increases the amount of shared memory that needs to be reserved & the OS must be configured to support the larger amount of shared memory. So here we first need to increase the Memory & SG...
Post a Comment
Read more

ORA-01143: cannot disable media recovery - file 1 needs media recovery

I got a request from the client - To flashback the database to the existing restore point & disable flashback and archive log mode for database UATB. Here I came a cross error - ORA-01143. I followed the below steps. 1. SQL> select name from v$database; NAME ------------ UATB 2. SQL> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ORDER BY TIME; NAME --------- UATB_COPY Here I'm going to restore the database to the above restore point. NOTE: The flashback database restore has to be done in MOUNT stage of the database. SQL> select name from v$database; NAME --------- UATB SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area  612368384 bytes Fixed Size                  1250428 bytes Variable Size             167775108 bytes ...
2 comments
Read more