[Email protected] ~]$ sqlplus "/As SYSDBA" Sql*plus:release 10.2.0.1.0-production on Wed Nov 14:41:53 2005Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to:oracle Database 10g Enterprise Edition Release 10.2.0.1.0-productionwith The partitioning, Oracle Label Sec Urity, OLAP and Data Mining scoring Engine optionssql> create tablespace eygle datafile '/dev/cciss/c0d0p11 ' size 1 0M; Tablespace created. Sql> CREATE TABLE Eygle tablespace eygle as SELECT * from Dba_objects; Table created. Sql> Select COUNT (*) from Eygle; COUNT (*)---------- 50420sql> alter tablespace eygle offline; Tablespace altered. Sql> exitdisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-productionwith the partitioning, Oracle Label Security, OLAP and Data Mining scoring Engine options
2. Backup raw device files to file system files using Rman's copy function
[email protected] ~]$Rman Target/
Recovery manager:release 10.2.0.1.0-production on Wed Nov 30 16:00:42 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to target Database:danaly (dbid=3965153484)
rman> copy datafile '/dev/cciss/c0d0p11 ' to '/opt/oracle/eygle01.dbf ';
Starting backup at 30-nov-05using target database control file instead of recovery catalogallocated Channel:ora_disk_1cha Nnel ora_disk_1:sid=137 devtype=diskchannel ora_disk_1:starting datafile copyinput datafile fno=00002 name=/dev/cciss/ C0d0p11output filename=/opt/oracle/eygle01.dbf tag=tag20051130t160137 recid=2 stamp=575740898channel ORA_DISK_1: DataFile copy complete, elapsed time:00:00:02finished backup at 30-nov-05
Rman> exitrecovery Manager complete.
3. Modify the file location by rename[email protected] ~]$ sqlplus "/As SYSDBA"sql*plus:release 10.2.0.1.0-production on Wed Nov 16:01:54 2005Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-productionwith the partitioning, Oracle Label Security, OLAP and Data Mining scoring Engine options
sql> ALTER DATABASE rename file '/dev/cciss/c0d0p11 ' to '/opt/oracle/eygle01.dbf ';
Database altered.
sql> alter tablespace eygle online;
tablespace altered.
sql> Select file_name from dba_data_files where tablespace_name= ' eygle ';
file_name--------------------------------------------------------------------------------/opt/oracle/eygle01.dbf
4. Making data Changessql> INSERT INTO Eygle as SELECT * from Eygle;INSERT INTO Eygle as SELECT * FROM Eygle *ERROR at line 1:ora-00926:missing VALUES keyword
sql> INSERT INTO Eygle select * from Eygle;INSERT INTO Eygle select * from Eygle *ERROR at line 1:ora-01653:unable to extend table SYS. Eygle by Tablespace Eygle
sql> ALTER DATABASE datafile '/OPT/ORACLE/EYGLE01.DBF ' resize 20m;Database altered.
sql> INSERT INTO Eygle select * from Eygle;50420 rows created.
sql> commit;Commit complete.
sql> alter tablespace eygle offline;tablespace altered.
sql> Exitdisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-productionwith the partitioning, Oracle Label Security, OLAP and Data Mining scoring Engine options
5. Migrating files from a file system to a bare device
[[email protected] ~]$ Rman target/
Recovery manager:release 10.2.0.1.0-production on Wed Nov 16:08:51 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target Database:danaly (dbid=3965153484)
rman> copy datafile '/opt/oracle/eygle01.dbf ' to '/dev/cciss/c0d0p11 ';
starting backup at 30-nov-05using target database control file instead of recovery catalogAllocated Channel:ora_disk_1Channel ora_disk_1:sid=135 Devtype=diskchannel ora_disk_1:starting datafile copyinput datafile fno=00002 name=/opt/oracle/eygle01.dbfoutput filename=/dev/cciss/c0d0p11 tag=tag20051130t160935 recid=3 stamp=575741376channel ora_disk_1:datafile copy complete, elapsed time:00:00:01finished backup at 30-nov-05
rman> Exit
Recovery Manager complete.[email protected] ~]$ sqlplus "/As SYSDBA"
sql*plus:release 10.2.0.1.0-production on Wed Nov 16:09:48 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-productionwith the partitioning, Oracle Label Security, OLAP and Data Mining scoring Engine options
sql> ALTER DATABASE rename file '/opt/oracle/eygle01.dbf ' to '/dev/cciss/c0d0p11 ';
Database altered.
sql> alter tablespace eygle online;
tablespace altered.
sql> Select COUNT (*) from Eygle;
COUNT (*)----------100840
sql>
From for notes (Wiz)
How to migrate data files between bare devices and file systems via Rman
