Shulou(Shulou.com)06/01 Report--

Online mobile oracle data files Oracle data files can be renamed or moved when the database OPEN, but at this time the tablespace must be read-only, which will allow users to query from the table, but prohibit them from doing so insert, update and delete, freeze the data file block when the tablespace is in a read-only state. Prevent updating the data file header before you can copy the data file online. "Note: except system table space, system table space cannot offline." this test takes TEST table space as 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. Make sure to move all the data files in the TEST tablespace 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. Make sure that the data file status in all TEST tablespaces 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. Place TEST tablespaces in read-only mode. SQL > ALTER TABLESPACE TEST READ ONLY; Tablespace altered.4. The query data dictionary determines that the TEST tablespace is read-only. SQL > SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'TEST'; TABLESPACE_NAME STATUS---TEST READ ONLY5. Copy all data files in the TEST tablespace to a new location After the copy is completed, make sure the permissions and size match the original data file [oracle@oracle orcl] $du-sh test.dbf 21m test.dbf [oracle@oracle orcl] $du-sh test03.dbf 41m test03.dbf [oracle@oracle orcl] $cp test.dbf / oracle/testdata/ [oracle@oracle orcl] $cp test03.dbf / oracle/testdata/ [oracle@oracle orcl] $cd / oracle/testdata/ [oracle@oracle 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 [oracle@oracle testdata] $du-sh test.dbf 21m test.dbf [oracle@oracle testdata] $du-sh test03.dbf41M test03.dbf [oracle@oracle 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. After all the data files are copied, the TEST tablespace is offline. At this time, none of the users can access the test tablespace. 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 > ALTER DATABASE RENAME FILE'/ oracle/oradata/orcl/test03.dbf' TO'/ oracle/testdata//test03.dbf'; Database altered.8. After all the data files are updated, the TEST tablespace ONLINE. SQL > ALTER TABLESPACE TEST ONLINE; 9. Put the TEST tablespace to read-write mode. SQL > ALTER TABLESPACE TEST READ WRITE; 10. Backup control files and 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' 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 The TEST tablespace data file has been relocated to 11. Check the TEST information in the data dictionary SQL > select file_name from dba_data_files where tablespace_name='TEST' The FILE_NAME----/oracle/testdata/test.dbf/oracle/testdata/test03.dbf data file has been moved to the specified location, and the data file has been moved. You can delete the data file in the original location of the TEST tablespace

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.