ORACLE Database Data File Location migration

ORACLE Database Data File Location migration 1. there are two ways to move a data file www.2cto.com to move a data file: 1. Move SQL> select name from v $ datafile; ------ 2. shut down Database SQL> shutdown immediate Database closed. database dismounted. ORACLE instance shut down. 3. MOUNT to database SQL> startup mount ORACLE instance started. total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Red O Buffers 667648 bytes Database mounted. 4. cut the data file to be moved to the target directory. SQL> SELECT 'host music '| d. FILE_NAME | '/home/lc_orauser/mount/tablespace1/' | substr (d. FILE_NAME, 34) | ';' FROM Dba_Data_Files d; host mv/home/lc_orauser/oradata/niutest/users01.dbf/home/lc_orauser/mount/tablespace1/users01.dbf; host mv/home/lc_orauser/oradata/niutest/sysaux01.dbf/home/lc_orauser/mount/tablespace1/sysaux01.dbf ;.... ..... 5. alter database method move data file SQL> SELECT 'alter database rename file ''' | d. file_name | ''' to ''/home/lc_orauser/mount/tablespace1/'| substr (d. FILE_NAME, 34) | ''' FROM dba_tablespaces t, Dba_Data_Files d WHERE t. tablespace_name = d. TABLESPACE_NAME; alter database rename file '/home/lc_orauser/mount/tablespace1/users01.dbf' to '/home/lc_orauser/mount/tablespace1/1/users01.dbf '......... ..... 6. mobile Other data files are the same as above. however, we should note the TEMP data file. the command for listing data files above does not contain this file. and moving this file will report the following ERROR: * error at line 1: ORA-01511: ERROR in renaming log/data files ORA-01516: nonexistent log file, datafile, or tempfile "E: \ ORACLE \ ORADATA \ SLUMGA \ temp01.DBF "how to move it? Create a TEMP file: SQL> create temporary tablespace temp2 tempfile 'e: \ oracle \ tem2.dbf' size 20 M reuse autoextend on next 50 m maxsize unlimited; SQL> alter database default temporary tablespace temp2; the database is opened successfully. Method 2: Move a table in the unit of table space. SELECT 'alter tablespace' | t. tablespace_name | 'offline; 'FROM dba_tablespaces t; alter tablespace SYSTEM offline ;............................ 2. at the operating system level, you can move the data file to the target directory by using the above method. 3. rename all data files in the tablespace in the ORACLE database SQL> SELECT 'alter tablespace' | t. tablespace_name | 'rename datafile ''' | d. file_name | ''' to ''/home/lc_orauser/mount/tablespace1/'| substr (d. FILE_NAME, 34) | ''' FROM dba_tablespaces t, Dba_Data_Files d WHERE t. tablespace_name = d. TABLESPACE_NAME ;! Note that the syntax and method 1 are different! 4. Set the tablespace online SQL> SELECT 'alter tablespace' | t. tablespace_name | 'Online; 'FROM dba_tablespaces t; 5. Note! This method is not available for system tablespace system, undo tablespace, and temp tablespace. Restart the database. Test whether it is successful! Www.2cto.com 2. Mobile Control File 1. SQL> alter system set control_files = 'e: \ oracle \ oradata \ slumgabak \ CONTROL01.CTL ', 'e: \ oracle \ oradata \ slumgabak \ CONTROL02.CTL ', 'e: \ oracle \ oradata \ slumgabak \ CONTROL03.CTL 'scope = spfile; System altered. restart the database. SQL> create pfile from spfile; File created. 2. modify E: \ oracle \ ora92 \ database \ init <SID> in the database started with pfile. ora file to modify the value of the CONTROL_FILES parameter. Restart the database. If SPFIL is used to recreate spfile SQL> create spfile from pfile; 3. move online to recreate the log file. 1. stop the database www.2cto.com 2. move the log file to the new target directory. mount to the database and modify the log file name SQL> alter database rename file 'e: \ oracle \ oradata \ slumga \ redo01.log' to 'e: \ ora cle \ oradata \ slumgabak \ REDO01.LOG '; Database altered. SQL> alter database rename file 'e: \ oracle \ oradata \ slumga \ REDO02.LOG 'to 'e: \ ora cle \ oradata \ slumgabak \ REDO02.LOG'; Database altered. SQL> alter database rename file 'e: \ oracle \ oradata \ slumga \ redo03.log' 'E: \ ora cle \ oradata \ slumgabak \ REDO03.LOG '; Database altered. 4. OPEN database SQL> alter Database open; Modification complete!