Posts Tagged ‘datafile’

Oracle – Move datafile from single-node into ASM using RMAN and recover (online mode)

November 2, 2010 Leave a comment

If you need to move a datafile from single-node to ASM then it is necessary to use RMAN to copy the datafile before renaming the datafile using SQLPLUS.

  1. Log into RMAN and copy the datafile to the required diskgroup:

    merlin:oracle@OASLIVE1 > rman
    Recovery Manager: Release - Production on Mon Nov 1 11:42:00 2010
    Copyright (c) 1982, 2007, Oracle. All rights reserved.
    RMAN> connect target /
    connected to target database: OASLIVE (DBID=3426612930)

    RMAN> copy datafile 19 to '+LIVEDATA';
    Starting backup at 01-NOV-10
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=122 instance=OASLIVE1 devtype=DISK
    channel ORA_DISK_1: starting datafile copy
    input datafile fno=00019 name=/u01/oracle/ora102/dbs/F:ORACLEORADATAOASLIVEOASLIVE_REPOS_01.DBF
    output filename=+LIVEDATA/oaslive/datafile/repository.288.733923757 tag=TAG20101101T114237 recid=1 stamp=733923758
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
    Finished backup at 01-NOV-10

  2. Take the datafile offline to prevent data from being written to it whilst the renaming is commencing

    merlin:oracle@OASLIVE1 > sqlplus '/as sysdba'
    SQL*Plus: Release - Production on Mon Nov 1 11:46:07 2010
    Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:
    Oracle Database 10g Enterprise Edition Release - 64bit Production
    With the Partitioning, Real Application Clusters, OLAP, Data Mining
    and Real Application Testing options

    SQL> alter database datafile 19 offline;
    Database altered.

  3. Rename the datafile

    SQL> alter database rename file '/u01/oracle/ora102/dbs/F:ORACLEORADATAOASLIVEOASLIVE_REPOS_01.DBF' to '+LIVEDATA/oaslive/datafile/repository.288.733923757';
    Database altered.

  4. If we try to put the datafile back online we will get an error message saying that the datafile requires recovery. This is because the copy of the datafile was completed whilst the datafile was still online. The following error message will occur:

    SQL> alter database datafile 19 online;
    alter database datafile 19 online
    ERROR at line 1:
    ORA-01113: file 19 needs media recovery
    ORA-01110: data file 19: '+LIVEDATA/oaslive/datafile/repository.288.733923757'

  5. Recover the datafile to restore

    SQL> recover datafile 19;
    Media recovery complete.

  6. Bring the datafile back online:

    SQL> alter database datafile 19 online;
    Database altered.

Oracle – Check tablespace sizes

October 19, 2010 Leave a comment

One of the tasks a DBA undertakes is to monitor the amount of space consumed by the database. If a tablespace becomes spacebound than the database will freeze up as data cannot be written down to disk.

Check Tablespace Free Space

The below script will identify the available free space for each tablespace.

SELECT a.tablespace_name
, a.fileCount
, a.MaxTSBytes
, b.TSDataBytes
, a.MaxTSBytes - b.TSDataBytes FreeSpace
, ROUND((a.TotalFilesizeMB/a.MaxTSBytes)*100,2) DFAlloc
, ROUND((b.TSDataBytes/a.MaxTSBytes)*100,2) AS DATAAlloc
(SELECT tablespace_name
, COUNT(*) filecount
, SUM(CASE WHEN maxbytes > bytes THEN maxbytes/1048576 ELSE bytes/1048576 end) AS MaxTSBytes
, SUM(bytes)/1048576 TotalFilesizeMB
FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name
, SUM(bytes)/(1048576) AS TSDataBytes
FROM dba_extents GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
--------------- --------- ---------- ----------- ---------  ------- ---------
INTERFACE       13        22250      21906.4375  343.5625   100      98.46
LOAD            6         8550       7629.125    920.875    100      89.23
MLOG            1         1250       617.5625    632.4375   100      49.41
NOTES           3         4200       3728.6875   471.3125   100      88.78
SYSTEM          1         500        406.625     93.375     100      81.33
UNDO            3         4500       323.085938  4176.91406 100       7.18
XDB             1         100        44.9375     55.0625    100      44.94
6 rows selected.

Query Datafile Sizes

The below script queries how much free space is available per datafile. In this example only the system tablespace has been queried:

SELECT a.file_id,
SUBSTR(a.tablespace_name,1,10) tablespace,
(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 ELSE bytes/1048576 END) MaxFileSize,
a.bytes/1048576 filesize,
(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 ELSE bytes/1048576 END) - nvl(b.usedbytes,0) free_Mb,
round(100*(1-(nvl(b.Usedbytes,0))/(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 else bytes/1048576 END)),2) "%_FREE",
FROM  dba_data_files a,
(SELECT file_id
, sum(bytes)/1048576 Usedbytes
FROM dba_extents GROUP BY file_id) b
WHERE a.file_id = b.file_id(+)
AND a.tablespace_name = '&tablespace_name'
------- ---------- --- ----------- -------- ------- ------ ---------
1       SYSTEM     NO  500         500      93.375  18.68 /u05/oracle/oradata/SYSTM_01.dbf

Once you have found the file_id for the datafile in the tablespace that needs expanding then you can increase the size of the tablespace.