This content has been marked as final. Show 8 replies
is showing where theres free space in that file.
but why do you want to know whether its full? what if theres a delete or an update? Are there only inserts in this datafile? how many tables are there? I had to do somethign similar recently, no down time allowed, but only 4 tables. I created new datafile in asm and online redefined the 4 tables so they were located in the new datafile.
Because if the data file is not full till now, it means it will allow DML activity on it, right? I dont know how much time is required to move the data file to ASM by using .sh script. Lets assume 1 minute for 300MB data file (not sure about the time it takes, may be less than that or more), during this time data file will not be available for read and write,etc. That of course I dont want in this Production DB. That's my concern right now :(
create a 2gb file and test and time the copy. I wouldnt have said minutes. I'd guess seconds.
it wont just be dml and it wont be just the datafile, the entire tablespace needs to come offline. have you got the steps?
create a test tablespace. create 1 test datafile
SQL> select tablespace_name, file_name from dba_data_files where file_id=10;
SQL> alter tablespace TEST offline;
-- connect to RMAN and copy the datafile to ASM
RMAN> copy datafile 10 to '+DATA';
RMAN> switch datafile 10 to copy;
-- back to sqlplus
SQL> alter tablespace TEST online;
Why whole tablespace need to be OFFLINE, and dont need to back and forth from RMAN to SQLPLUS. You said no downtime right? ...you are taking whole tablespace OFFLINE, ok even for seconds... My question was how Can I know how much space exactly is left in the data file to fill? :) I have already creaed this shell script to minimize the time taken to move the data file, instead of writing commands from RMAN and SQLplus.
/rman target / nocatalog sql "ALTER DATABASE DATAFILE 62 OFFLINE"; backup as copy datafile 62 format '+DATA01'; switch datafile 62 to copy; recover datafile 62; sql "alter database datafile 62 ONLINE"; exit;
Edited by: 910385 on May 10, 2012 3:42 AM
Edited by: 910385 on May 10, 2012 3:43 AM
How to move a datafile from a file system to ASM [ID 390274.1]
In order to move a datafile on a running active database the tablespace where the datafile resides must be placed offline.
SQL> create tablespace test01 2 datafile 'c:\test01.dbf' size 2g; Tablespace created. SQL> select file#, name from v$ddatafile 36 C:\TEST01.DBF
-- try again with tablespace, ran in seconds. but thats not the point. tablespace needed to be offline
rman target / sql "ALTER DATABASE DATAFILE 36 OFFLINE"; backup as copy datafile 36 format '+DATA'; switch datafile 36 to copy; recover datafile 36; sql "alter database datafile 36 ONLINE"; exit; -- fails with datafile as media recovery needs to be enabled RMAN> sql "ALTER DATABASE DATAFILE 36 OFFLINE"; using target database control file instead of recovery catalog sql statement: ALTER DATABASE DATAFILE 36 OFFLINE RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of sql command on default channel at 05/10/2012 12:18:46 RMAN-11003: failure during parse/execution of SQL statement: ALTER DATABASE DATAFILE 36 OF ORA-01145: offline immediate disallowed unless media recovery enabled
RMAN> sql "ALTER TABLESPACE TEST01 OFFLINE"; using target database control file instead of recovery catalog sql statement: ALTER TABLESPACE TEST01 OFFLINE RMAN> backup as copy datafile 36 format '+DATA'; Starting backup at 10-MAY-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=82 instance=dprd1 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00036 name=C:\TEST01.DBF output file name=+DATA/dprd/datafile/test01.331.782914827 tag=TAG20120510T122026 RECID=1 S channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26 Finished backup at 10-MAY-12 Starting Control File and SPFILE Autobackup at 10-MAY-12 piece handle=+FLASH/dprd/autobackup/2012_05_10/s_782914852.270.782914853 comment=NONE Finished Control File and SPFILE Autobackup at 10-MAY-12 RMAN> switch datafile 36 to copy; datafile 36 switched to datafile copy "+DATA/dprd/datafile/test01.331.782914827" RMAN> recover datafile 36; Starting recover at 10-MAY-12 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 10-MAY-12 RMAN> sql "ALTER TABLESPACE TEST01 ONLINE"; sql statement: ALTER TABLESPACE TEST01 ONLINE RMAN>