Be careful when you resize a datafile in a Data Guard configuration

If you resize a system datafile on a primary database in a Data Guard configuration, the media recovery process on the standby will shutdown and stop applying redo ...

File system layout to minimize disk drive contention

This example configuration contains eight data areas, including disk drives, striped sets,
RAID sets, and placeholders for other new technologies to be developed in the future.
Separate the eight data areas as completely as possible. Ideally, operate from different
device controllers or channels to maximize throughput. The more disk drive heads are
moving at one time, the faster the database. To minimize disk drive contention, lay out
the file system disk drives as follows:

• AREA 1 – Oracle executables and a control file
• AREA 2 – Data: datafiles, index datafiles, system datafiles, tool datafiles, user datafiles,
  and a control file
• AREA 3 – Data datafiles, index datafiles, temporary datafiles, undo datafiles, and a
  control file
• AREA 4 – Archive log files, export files, backup staging area, and a control file
• AREA 5 – Redo log files
• AREA 6 – Redo log files
• AREA 7 – Redo log files
• AREA 8 – Redo log files

Online redo log files exist on four separate disk drives. Online redo log files are
multiplexed, and Oracle creates these log files in a circular fashion:
redo log 1 =>redo log 2 => redo log 3 => redo log 4 =>redo log 1
As a result, the I/O is evenly distributed. Therefore, when Oracle switches log file
groups, writing to the new redo log files does not impact reading the old redo log file
to create a new archive log file.

How PCTFREE and PCTUSED Work Together

PCTFREE and PCTUSED work together to optimize the use of space in the data blocks of the extents within a data segment

How to see what is really at the file level

Ideally all the data we want to work with are in memory and noI/O is needed. In reality, you usually can't count on this being the case. So, our goal is to try to minimize the disk physical movement for any given data request. For example, if the index and the data are on the same disk, there is movement needed for the index and then the same disk must move for the data. If the next read wants the next record, then we must move back for the index and back again for the data. We have made the read for the data and the read for the index get in each other's way.

All objects that might be used in the same transaction should be on different mount points.

• System tablespace

• ƒ Data tablespace

• ƒ Index tablespace

• ƒ Rollback segments

• ƒ Archive logs

• ƒ Temporary tablespace

These six mount points would give us our basic good system setup.

To check the current blocks in memory

SELECT   b.file_name, a.file#, a.cnt
    FROM   (  SELECT   file#, COUNT (1) cnt
                FROM   v$bh
            GROUP BY   file#) a, dba_data_files b
   WHERE   a.file# = b.file_id
ORDER BY   cnt DESC

The v$filestat tell us the time spent performing reads and writes, in hundredths of a second, if timed_statistics is set to true. When I look at a system that has been up for a longer time, I see that the average time to write a block of data is about ten times longer than the average time to read a block.

Take a look at your  v$filestat and v$tempstat views. Mine have shown me that even though Oracle works in memory as much as possible, I still need to be very aware of I/O-level contention. I also see that wherever I can, I will try to minimize the number of write actions performed.

Watch yours for a while to see what is really going on.

Where you can find Oracle Block Details

• DBA_SEGMENTS

                 –HEADER_FILE
                 –HEADER_BLOCK

• DBA_EXTENTS

                –EXTENT_ID
                –FILE_ID
                –BLOCK_ID

Both can be used to determine starting blocks of Index segments.
ASSM set to manual: Add 1 to BLOCK_ID to find Root Block
ASSM set to auto: Add 3 to BLOCK_ID to find Root Block (can vary)

Script to get the amount of free space in your tablespaces

select  tbs.tablespace_name, 
        tot.bytes/1024 total, 
        tot.bytes/1024-sum(nvl(fre.bytes,0))/1024 used, 
        sum(nvl(fre.bytes,0))/1024 free, 
        (1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct,
        decode(
            greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),
            90, '', '*'
        ) pct_warn
from    dba_free_space fre,
        (select tablespace_name, sum(bytes) bytes
        from    dba_data_files
        group by tablespace_name) tot,
        dba_tablespaces tbs
where   tot.tablespace_name    = tbs.tablespace_name
and     fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/1024, tot.bytes
order by 5, 1  ;

Oracle tablespace usage script

SELECT /* + RULE */  
df.tablespace_name AS "Tablespace",
df.bytes / (1024 * 1024 * 1024) AS "Size (GB)",
Trunc(fs.bytes / (1024 * 1024 * 1024)) AS "Free (GB)"       
FROM 
(
SELECT 
tablespace_name,
Sum(bytes) AS bytes 
FROM 
dba_free_space
GROUP BY
tablespace_name
) fs, 
( 
SELECT 
tablespace_name,
SUM(bytes) AS bytes
FROM 
dba_data_files
GROUP BY 
tablespace_name

) df
WHERE 
fs.tablespace_name = df.tablespace_name
ORDER BY 3 desc

Why you should use Oracle datapump

• Similar look and feel to the old exp/imp
• Can filter on the full range of object types
• Can re-map datafiles and or tablespaces on import
• Parallelizable
• Significantly faster than the traditional exp/imp
• PL/SQL interface - programmable
• Can import through a network link
• Tracking in V$session_longops
• Interruptible and restartable

Script to resize Oracle Datafiles

Over-allocation of space at the file level affects the backup/recovery window, file checking times and, most  painfully, limits the potential allocation of space to a tablespace that needs the extra room. A simpler solution would be to review the evolution of the script, which lets you know which files can and cannot be resized to create more space.

It's possible to release space from data files but only down to the first block of data. This is done with the 'alter database' command.

The following script allows to calculate the amount of space used by each tablespace

SELECT   tablespace_name, SUM (bytes) bytes_full
    FROM   dba_extents
GROUP BY   tablespace_name;

The following scripts allows to calculate the total space available for each tablespace

SELECT   tablespace_name, SUM (bytes) bytes_total
    FROM   dba_data_files
GROUP BY   tablespace_name;

The following script allows to find the last data block that has been inserted for each file

SELECT   tablespace_name, file_id, MAX (block_id) max_data_block_id
    FROM   dba_extents
GROUP BY   tablespace_name, file_id;

The following allows to find the free space in each file above the last data block inserted

SELECT   a.tablespace_name, a.file_id, b.bytes bytes_free
  FROM   (  SELECT   tablespace_name, file_id, MAX (block_id) max_data_block_id
              FROM   dba_extents
          GROUP BY   tablespace_name, file_id) a, dba_free_space b
 WHERE       a.tablespace_name = b.tablespace_name
         AND a.file_id = b.file_id
         AND b.block_id > a.max_data_block_id;

Finally the following will allow to generate alter statements to resize your datafiles

SELECT      'alter database '
         || a.name
         || ' datafile '''
         || b.file_name
         || ''''
         || ' resize '
         || GREATEST (TRUNC (bytes_full / .7), (bytes_total - bytes_free))
  FROM   v$database a,
         dba_data_files b,
         (  SELECT   tablespace_name, SUM (bytes) bytes_full
              FROM   dba_extents
          GROUP BY   tablespace_name) c,
         (  SELECT   tablespace_name, SUM (bytes) bytes_total
              FROM   dba_data_files
          GROUP BY   tablespace_name) d,
         (SELECT   a.tablespace_name, a.file_id, b.bytes bytes_free
            FROM   (  SELECT   tablespace_name,
                               file_id,
                               MAX (block_id) max_data_block_id
                        FROM   dba_extents
                    GROUP BY   tablespace_name, file_id) a,
                   dba_free_space b
           WHERE       a.tablespace_name = b.tablespace_name
                   AND a.file_id = b.file_id
                   AND b.block_id > a.max_data_block_id) e
 WHERE       b.tablespace_name = c.tablespace_name
         AND b.tablespace_name = d.tablespace_name
         AND bytes_full / bytes_total < .7
         AND b.tablespace_name = e.tablespace_name
         AND b.file_id = e.file_id;