Move Oracle data File Locations online

     Online mobile oracle  data files The Oracle data file can be renamed or moved while the database is open, but the tablespace must be read-only, which allows users to query from the table, but prevents them from inserting , update and delete, freezes the data file size when the table space is read-only. Block update of data files in order to copy data files online   Note: The System tablespace,system  table space cannot be offline the test table space is an example sql> select *  from v$version; BANNER----------------------------------------------------------------oracle database 10g  enterprise edition release 10.2.0.1.0 - 64bitpl/sql release 10.2.0.1.0  - productioncore    10.2.0.1.0      productiontns  for linux: version 10.2.0.1.0 - productionnlsrtl version 10.2.0.1.0  - Production1.  determines that you want to move all data files in the test table space sql > select file_name, status  from dba_data_files       where tablespace_name =  ' TEST ';  file_name                                            STATUS-------------------------------------------------- ---------/oracle/oradata/orcl/test.dbf                        AVAILABLE/oracle/oradata/orcl/test03.dbf                     AVAILABLE2.  determines that the data file status in all test table spaces is  AVAILABLE. SQL> SELECT FILE_NAME,STATUS FROM DBA_DATA_FILES WHERE  Tablespace_name= ' testfile_name                                                       STATUS------------------------------------------------------------ ---------/oracle/oradata/orcl/test.dbf                                  available/oracle/oradata/ orcl/test03.dbf                               AVAILABLE3.  . sql > alter tablespace test  read only;   the TEST table space as a read-only mode  Tablespace altered.4.  query data dictionary determines that the test table space is read-only.  sql > select tablespace_ name, status from dba_tablespaces       where  tablespace_name =  ' TEST ';  tablespace_name                 STATUS------------------------------ ---------test                             read only5.  Copy the test table space all data files to a new location, and after the copy is complete, determine the permissions and size to match the original data file [[Email protected] orcl]$ du  -sh test.dbf 21m     test.dbf[[email protected] orcl]$  du -sh test03.dbf 41m     test03.dbf[[email protected]  orcl]$ cp test.dbf    /oracle/testdata/[[email protected] orcl]$  cp test03.dbf  /oracle/testdata/[[email protected] orcl]$ cd /oracle/ Testdata/[[email protected] testdata]$ ls-rw-r----- 1 oracle oinstall  41951232 may  7 23:49 test03.dbf-rw-R----- 1 oracle oinstall 20979712 may  7 23:49 test.dbf[[email  protected] testdata]$ du -sh test.dbf 21m     test.dbf [[email protected] testdata]$ du -sh test03.dbf41m      Test03.dbf[[email protected] testdata]$  ls -al -rw-r----- 1 oracle  oinstall 41951232 may  7 23:49 test03.dbf-rw-r----- 1 oracle  oinstall 20979712 May  7 23:49 test.dbf6.  when all copies of the data file are complete, offline the test table space , the TEST tablespace is not accessible to all users at this time . sql > alter tablespace test offline;       Tablespace altered.7.  Update control file information . sql > alter database  rename file  '/oracle/oradata/orcl/test.dbf '  TO  '/oracle/testdata//test.dbf ';  Database  altered. sql >&nbsp alter database rename file  '/oracle/oradata/orcl/test03.dbf '  TO  '/oracle/ TESTDATA//TEST03.DBF '; database altered.8.  all data files are updated, the test table space online. sql >  alter tablespace test online; 9.  the TEST table space as read-write mode . sql > alter  tablespace test read write; 10.  backup control files, view control file information  SQL > ALTER  database backup controlfile to trace; database altered. $CD  /oracle/admin/ Orcl/udumpmore orcl_ora_8453.trcstartup nomountcreate controlfile reuse database   "ORCL"  NORESETLOGS  NOARCHIVELOG    MAXLOGFILES 16     MAXLOGMEMBERS 3    MAXDATAFILES 100     maxinstances 8    maxloghistory 292logfile  group 1  '/oracle /oradata/orcl/redo01.log '  &nbsP size 50m,  group 2  '/oracle/oradata/orcl/redo02.log '   SIZE 50M,   GROUP 3  '/oracle/oradata/orcl/redo03.log '   SIZE 50M-- STANDBY  logfiledatafile   '/oracle/oradata/orcl/system01.dbf ',   '/oracle/oradata/orcl/undotbs01.dbf ',   '/oracle/oradata/orcl/sysaux01.dbf ',   '/oracle/oradata/orcl/users01.dbf ',   ' /oracle/testdata/test.dbf ',   '/oracle/oradata/orcl/system02.dbf ',   '/oracle/oradata/orcl/ Test02.dbf ',   '/oracle/testdata/test03.dbf ',   '/oracle/oradata/orcl/jycq.dbf ' in the updated control file,  test tablespace data file has changed Location    11.  view data dictionary in test information sql> select  file_name  From dba_data_files where tablespace_name= ' TEST '; file_name--------------------------------------------------------------------------------/oracle/testdata/ The TEST.DBF/ORACLE/TESTDATA/TEST03.DBF data file has been moved to the specified location, and the data file has been moved so that the test table space can beData file deletion from the original location 

This article is from the "O Record" blog, so be sure to keep this source http://evils798.blog.51cto.com/8983296/1420919