ORA-03297: file contains used data beyond requested RESIZE value

ORA-03297: file contains used data beyond requested RESIZE value
At some stage a DBA will encounter the following error when resizing a datafile:

ORA-03297: file contains used data beyond requested RESIZE value

What may be more frustrating is that the datafile has plenty of free space.

So what can be done to solve this error and resize down the datafile? Why does it occur? Some might be tempted to just add more space to the filesystem and be done with it. Not everyone has that option.

A datafile cannot be resized down below the High Water Mark (HWM) and that's what causes the ORA-03297 error. . In the lifetime of the datafile some extents have been created bumping up the HWM. Others below the HWM have also been DELETED. 

I will show an example of a datafile encountering this error, what is happening inside the datafile and how to resolve the error. Let's create a tablespace first and the two tables, one with many rows and a second with a few rows.

CREATE TABLESPACE "TESTTBLSPC" DATAFILE '/oradata1/TESTDB//TESTTBLSPCdatafile01.dbf' SIZE 32M
AUTOEXTEND ON NEXT 1310720 MAXSIZE 64M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO

Tablespace created.

SQL> create table myuser.testtab1 tablespace TESTTBLSPC as
(select level id,dbms_random.string('a',dbms_random.value(1,4000)) str
from dual
connect by level < 20000
)
/
Table created.

SQL> create table myuser.testtab2 tablespace TESTTBLSPC as
(select level id,dbms_random.string('a',dbms_random.value(1,4000)) str
from dual
connect by level < 10
)
/

Table created.

How many MB is now in use by these tables?
select segment_name, bytes/1024/1024
from dba_segments
where segment_name in ('testtab1','testtab2')
and owner = 'MYUSER'
/

SEGMENT_NAME                    BYTES/1024/1024
-----------------------------------------------
testtab1                          54
testtab2                          .0625


To demonstrate how these two tables fit in the TESTTBLSPC tablespace I want to firstly show a few things about the datafile.

DFSIZEMB = the datafile size in MB on disk.
HWMMB = the location of the HWM in the datafile.
DFREEMB = the number of FREE MB in the datafile.
%FREE = the percentage of FREE space in the datafile.
RESIZEABLE = the amount of MB that can be gained from resizing.

Use the below SQL to see how much space can be gained from a RESIZE and the location of the HWM in a datafile. For a database with many datafiles this query may take a while.

QUERY 1 - datafile free space, HWM location, resizeable MB

column file_name format a50
column tablespace_name format a20
select
            tablespace_name,
            file_id,
            file_name,
            dfsizeMB,
            hwmMB,
            dffreeMB,
            trunc((dffreeMB/dfsizeMB)*100,2) "% Free",
            trunc(dfsizeMB-hwmMB,2) "Resizeble"
   from
   (
      select
           df.tablespace_name tablespace_name,
           df.file_id file_id,
           df.file_name file_name,
           df.bytes/1024/1024 dfsizeMB,
           trunc((ex.hwm*(ts.block_size))/1024/1024,2) hwmMB,
           dffreeMB
      from
           dba_data_files df,
           dba_tablespaces ts,
      (
           select file_id, sum(bytes/1024/1024) dffreeMB
           from dba_free_space
           group by file_id
      ) free,
      (
           select file_id, max(block_id+blocks) hwm
           from dba_extents
           group by file_id
      ) ex
      where df.file_id = ex.file_id
      and df.tablespace_name = ts.tablespace_name
      and df.file_id = free.file_id (+)
      order by df.tablespace_name, df.file_id
    )
/
TABLESPACE_NAME  FILE_ID File Name                            DFSIZEMB     HWMMB   DFFREEMB     % Free  Resizeble
-------------------- ---------- ------------------------------ ---------- ---------- ---------- ---------- ----------...
 TESTTBLSPC             14      /oradata1/TESTDB/TESTTBLSPCdatafile01.dbf        55.75      55.06      .6875       1.23        .69
...
Above, the datafile can only be resized by 0.69MB and the HWM is at the 55MB mark with the datafile size on disk being 55.75MB.

How do these two tables look like within the datafile? NOTE: This query may take a while for a large datafile with lots of extents.

QUERY 2 - location of segments within a datafile

column segment heading 'Segment Name' format a14
column file_name heading 'File Name' format a40
column segment_type heading 'Segment Type' format a10
select
                file_name,
                segment_type,
                owner||'.'||segment_name segment,
                block_id,
                blockIdMB
        from
        (
         select
              ex.owner owner,
              ex.segment_name segment_name,
              ex.segment_type segment_type,
              ex.block_id block_id,
              df.file_name file_name,
              trunc((ex.block_id*(ts.block_size))/1024/1024,2) blockIdMB
      from
              dba_extents ex, dba_data_files df, dba_tablespaces ts
              where df.file_id = &file_id
              and df.file_id = ex.file_id
              and df.tablespace_name = ts.tablespace_name
              order by ex.block_id desc
      )
      where rownum <= 100
/

Enter value for file_id: 14

File Name                         Segment Ty Segment Name     BLOCK_ID  BLOCKIDMB
--------------------------------- ---------- -------------- ---------- ----------
/oradata1/TESTDB/TESTTBLSPCdatafile01.dbf   TABLE      MYUSER.testtab2        7040        55
/oradata1/TESTDB/TESTTBLSPCdatafile01.dbf   TABLE      MYUSER.testtab1        6912        54 


We can see table testtab1 ends around the 54MB block in the datafile and testtab2 starts around the 55MB block.

So we now have two tables in the TESTTBLSPC tablespace created one after the other. Further, how about we create a situation where there is alot of free space in the datafile and cannot be resized resulting in "ORA-03297: file contains used data beyond requested RESIZE value". DELETING all the rows from the testtab1 table will not free up any space and will still have it allocated to testtab1, so let's TRUNCATE the table testtab1 and run the same SQL above again to see the result of the datafile.
SQL> truncate table myuser.testtab1;

Table truncated.
TABLESPACE_NAME         FILE_ID File Name                                            DFSIZEMB      HWMMB   DFFREEMB     % Free  Resizeble
-------------------- ---------- -------------------------------------------------- ---------- ---------- ---------- ---------- ----------
TESTTBLSPC                         14 /oradata1/TESTDB/TESTTBLSPCdatafile01.dbf                  55.75      55.06     54.625      97.98        .69
The datafile now has 97% and 54MB FREE space. The HWM is still at the 55MB mark. Inspecting the datafile, testtab1 is at block 1MB and testtab2 is still at of course 55MB.
File Name                         Segment Ty Segment Name     BLOCK_ID  BLOCKIDMB
--------------------------------- ---------- -------------- ---------- ----------
/oradata1/TESTDB/TESTTBLSPCdatafile01.dbf   TABLE      MYUSER.testtab2        7040        55
/oradata1/TESTDB/TESTTBLSPCdatafile01.dbf   TABLE      MYUSER.testtab1         128         1

So seeing we have 54MB FREE space in a 55MB datafile let's try to resize it down to 50MB.

SQL> alter database datafile '/oradata1/TESTDB/TESTTBLSPCdatafile01.dbf' resize 50M;
alter database datafile '/oradata1/TESTDB/TESTTBLSPCdatafile01.dbf' resize 50M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


The only way to resize the datafile is to lower the HWM. This is achieved by moving the extents located at the HWM either to another tablespace or the same tablespace. For the same tablespace ensure plenty of free space and run QUERY 2 after each MOVE to see if anything is achieved.

Of course for a production environment where there are many users accessing the table, index, LOB etc it might not be that easy and this sort of activity would need to be carefully planned, especially with LOB segments. Also, when moving a table the underlying INDEXES are marked UNUSABLE and need to be rebuilt.

SQL> alter table myuser.testtab2 move tablespace TESTTBLSPC;

Table altered.
Now the datafile shows that testtab2 is at block 1MB.

File Name                           Segment Ty Segment Name     BLOCK_ID  BLOCKIDMB
----------------------------------- ---------- -------------- ---------- ----------
/oradata1/TESTDB/TESTTBLSPCdatafile01.dbf   TABLE      MYUSER.testtab2         136       1.06
/oradata1/TESTDB/TESTTBLSPCdatafile01.dbf   TABLE      MYUSER.testtab1         128          1

What about the HWM?
TABLESPACE_NAME   FILE_ID File Name                                            DFSIZEMB      HWMMB   DFFREEMB     % Free  Resizeble
-------------------- ---------- -------------------------------------------------- ---------- ---------- ---------- ---------- ----------

TESTTBLSPC              14 /oradata1/TESTDB/TESTTBLSPCdatafile01.dbf                  55.75       1.12     54.625      97.98      54.63

Now the HWM is at the 1.12 MB mark and there is 54MB of reclaimable space in the datafile.

SQL> alter database datafile '/oradata1/TESTDB/TESTTBLSPCdatafile01.dbf' resize 2M;

Database altered.

Datafile now resized! In a production environment there may be many different objects extents near the HWM and simply moving one table might not be so common. It's best to see how many different objects are around the HWM and calculate how much space can be reclaimed via the MOVE. Unless there is a benefit in reclaiming a large amount of space, or moving tables, indexes, LOBs etc is something that just has to be done, then if possible get more space added to the filesystem.

Restore RMAN backup to different server with different database name

Here we will restore oracle database from backup to another server and do the database clone from rman hot backup.

We have taken RMAN backup of PROD and  will  restore rman backup to different server with different database name.

Lets take two servers as below:

SERVER1—DBPROD

SERVER2---DBTEST

ON SERVER1:


DB Name: DBPROD

–Take backup of DBPROD with archivelog,controlfile and spfile.

–Copy all backupsets from SERVER1(Location: /data1/bak/TEST/) to SERVER2(Location:/u01/backup/TEST/) , So that we will restore oracle database from cold backup.

ON SERVER2:

–Create pfile for database DBTEST.
DB Name: DBTEST

#export ORACLE_SID=TEST
#rman target /

RMAN>startup nomount;
RMAN>restore controlfile from '/u01/backup/backup_db_c-300166359-20120316-00';   --- complete path of backup
RMAN>alter database mount;

–Since backup location on SERVER2 is different from SERVER1, so we have to use catalog command to 
update new backup location in controlfile.
Use below command for all backuppieces:

RMAN> CATALOG START WITH '/my/backup/location';

Or

RMAN>catalog backuppiece '/u01/backup/ora_df_020412_21_14_NSNDB_1635_1';
RMAN>catalog backuppiece '/u01/backup/ora_df_020412_21_14_NSNDB_1636_1';
RMAN>catalog backuppiece '/u01/backup/ora_arch_230412_13_43_NSNDB_23_1';
RMAN>catalog backuppiece '/u01/backup/ora_arch_230412_13_43_NSNDB_24_1';

–Since datafile location on SERVER2 is different from SERVER1, So we have to update controlfile for same with SET NEWNAME clause:


Now restore database backup on SERVER2:

RMAN>
run
{
set newname for datafile '/data02/oradata/system01.dbf' to '/u01/oradata/testbak/system01.dbf';
set newname for datafile '/data02/oradata/undotbs01.dbf' to '/u01/oradata/ testbak /undotbs1.dbf';
set newname for datafile '/data02/oradata/sysaux01.dbf' to '/u01/oradata/ testbak /sysaux01.dbf';
set newname for datafile '/data02/oradata/users01.dbf' to '/u01/oradata/ testbak /users01.dbf';
restore database;
switch datafile all;
}

RMAN>exit;

–Also update redo log file in controlfile on SERVER2:

#sqlplus "/as sysdba"

SQL>alter database rename file '/data02/oradata/redo07.log' to '/u01/oradata/ testbak /redo07.log';
SQL>alter database rename file '/data02/oradata/redo08.log' to '/u01/oradata/ testbak /redo08.log';
SQL>alter database rename file '/data02/oradata//redo09.log' to '/u01/oradata/ testbak /redo09.log';
SQL>exit;

#rman target /

RMAN>
run
{
recover database;
alter database open resetlogs; }

RMAN>exit;

#sqlplus "/as sysdba"

SQL>alter database tempfile '/u01/home/oracle/PROD/tempfiles/temp01_PROD.dbf' drop including datafiles;

sql>ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/nsnbak/temp01.dbf' size 200m reuse/size 500M;

sql>select name from v$tempfile;

To change DB name follow the below steps:-

To change the database name in addition to the DBID, specify the DBNAME parameter. This example changes the name to test_db:

Ensure that the target database is mounted but not open, and that it was shut down consistently prior to mounting. For example:

SHUTDOWN IMMEDIATE

STARTUP MOUNT

$nid TARGET=SYS/password DBNAME=test_db

SQL>ALTER DATABASE OPEN RESETLOGS;

After that modify the parameter file as db_name

RMAN backup status check

This script will be run in the target database(not in the catalog).

Login as sysdba and issue the following script:

This script will report on all currently running RMAN backups like full, incremental & archivelog backups:

Script-1) Below script will give you RMAN backup status along with start and stop timing.

SELECT
    session_key,
    input_type,
    status,
    TO_CHAR(
        start_time,
        'mm/dd/yy hh24:mi'
    ) start_time,
    TO_CHAR(
        end_time,
        'mm/dd/yy hh24:mi'
    ) end_time,
    elapsed_seconds / 3600 hrs
FROM
    v$rman_backup_job_details
ORDER BY start_time DESC;


STEP-2 Below script will give you SID, Total Work, Sofar & % of completion.

SELECT
    sid,
    serial#,
    context,
    sofar,
    totalwork,
    round(
        sofar / totalwork * 100,
        2
    ) "% COMPLETE"
FROM
    v$session_longops
WHERE
        opname LIKE 'RMAN%'
    AND
        opname NOT LIKE '%aggregate%'
    AND
        totalwork != 0
    AND
        sofar <> totalwork;


STEP-3 Below script is used for historical backup status  :

SELECT
    session_key,
    input_type,
    status,
    TO_CHAR(
        start_time,
        'mm-dd-yyyy hh24:mi:ss'
    ) AS rman_bkup_start_time,
    TO_CHAR(
        end_time,
        'mm-dd-yyyy hh24:mi:ss'
    ) AS rman_bkup_end_time,
    elapsed_seconds / 3600 hours
FROM
    v$rman_backup_job_details
ORDER BY start_time desc;

ORACLE RMAN Backup types

RMAN - Sample Backup Scripts 10g

• Backup up Whole Database Backups with RMAN
• Backing up Individual Tablespaces with RMAN
• Backing up Individual Datafiles and Datafile Copies with RMAN
• Backing up Control Files with RMAN
• Backing up Server Parameter Files with RMAN
• Backing up Archived Redo Logs with RMAN
• Backing up the Whole database including archivelogs
=====================================================================================

Making Whole Database Backups with RMAN

You can perform whole database backups with the database mounted or open. To perform a whole database backup from the RMAN prompt the BACKUP DATABASE command can be used. The simplest form of the command requires no parameters, as shown in this example:

RMAN> backup database;
In the following example no backup location was specified meaning that the backups will automatically be placed in the Flash Recovery Area (FRA). If the FRA has not been setup then all backups default to $ORACLE_HOME/dbs.

How to check if the RFA has been setup:

SQL> show parameter recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /recovery_area
db_recovery_file_dest_size big integer 50G

If your FRA is not setup (ie values are null) please refer to the following note for assistance in setting it up.

Note 305648.1 What is a Flash Recovery Area and how to configure it ?

If you wish to place your backup outside the FRA then following RMAN syntax may be used.

RMAN> backup database format '/backups/PROD/df_t%t_s%s_p%p';

• Backing Up Individual Table-spaces with RMAN

RMAN allows individual tablespaces to be backed up with the database in open or mount stage.

RMAN> backup tablespace SYSTEM, UNDOTBS, USERS;

• Backing Up Individual Datafiles and Datafile Copies with RMAN

The flexibilty of being able to backup a single datafile is also available. As seen below you are able to reference the datafile via the file# or file name. Multiple datafiles can be backed up at a time.

RMAN> backup datafile 2;

RMAN> backup datafile 2 format '/backups/PROD/df_t%t_s%s_p%p';

RMAN> backup datafile 1,2,3,6,7,8;

RMAN> backup datafile '/oradata/system01.dbf';

• Backing Up the current controlfile & Spfile 

The controlfile and spfile are backed up in similar ways. Whenever a full database backup if performed, the controlfile and spfile are backed up. In fact whenever file#1 is backed up these two files are backed up also.

It is also good practise to backup the controlfile especially after tablespaces and datafiles have been added or deleted.

If you are not using an RMAN catalog it is more impotant that you frequently backup of your controlfile. You can also configure another method of controlfile backup which is referred to as 'autobackup of controlfile'.

Refer to the manual for more information regarding this feature.

RMAN> backup current controlfile;

RMAN> backup current controlfile format '/backups/PROD/df_t%t_s%s_p%p';

RMAN> backup spfile;

• Backing Up Archivelogs

It is important that archivelogs are backed up in a timely manner and correctly removed to ensure the file system does not fill up. Below are a few different examples. Option one backs up all archive logs to the FRA or default location. Option two backs up all archivelogs generate between 7 and 30 days and option three backs up archive logs from log sequence number XXX until logseq YYY then deletes the archivelogs. It also backups the archive logs to a specified location.

RMAN> backup archivelog all;

RMAN> backup archivelog from time 'sysdate-30' until time 'sysdate-7';

RMAN> backup archivelog from logseq=XXX until logseq=YYY delete input format '/backups/PROD/%d_archive_%T_%u_s%s_p%p';

• Backing up the Whole database including archivelogs

Below is an example of how the whole database can be backed up and at the same time backup the archive logs and purge them following a successful backup. The first example backups up to the FRA, however it you wish to redirect the output the second command shows how this is achieved.

RMAN> backup database plus archivelog delete input;

RMAN> backup database plus archivelog delete input format '/backups/PROD/df_t%t_s%s_p%p';

Processes & Sessions

How to increase PROCESSES initialization parameter

ORA-00020 maximum number of processes exceeded

ORA-00020 maximum number of processes exceeded
Cause: All process state objects are in use.
Action: Increase the value of the PROCESSES initialization parameter.

ORA-00020 comes under "Oracle Database Server Messages". These messages are generated by the Oracle database server when running any Oracle program.

How to increase PROCESSES initialization parameter:

1.    Login as sysdba
    sqlplus / as sysdba
  
2. Check Current Setting of Parameters
    sql> show parameter sessions
    sql> show parameter processes
    sql> show parameter transactions

3.    If you are planning to increase "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters
    A basic formula for determining  these parameter values is as follows:
  
        processes=x
        sessions=x*1.1+5
        transactions=sessions*1.1
      
4.    These paramters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.
    sql> alter system set processes=500 scope=spfile;
    sql> alter system set sessions=555 scope=spfile;
    sql> alter system set transactions=610 scope=spfile;
    sql> shutdown abort
    sql> startup

Uninstall the oracle software

STEP-1) Before uninstalling the oracle software, make sure you have deleted the oracle databases and if any services running from the ORACLE_HOME

STEP-2)Below methods describes about uninstallation of oracle software:

1)Using uninstall tool with runInstaller
2)Using uninstall tool under Oracle home location
3)Manual uninstall

Method 1:
Remember when you install oracle software, you use runInstaller from the installation. Using same runInstaller can be used to uninstall oracle software as well.

Go to runInstaller location and run below statement
./runInstaller -deinstall -home /u01/app/oracle/product/12.1.0/dbhome_1/

Method 2:
Under ORACLE_HOME location, we get a deinstall utility that you can use to remove the oracle software.
cd $ORACLE_HOME/deinstall/deinstall

Method 3:
Sometimes ORACLE software installation gets corrupted and in such case, above deinstallation utilities will not help you to remove oracle software. Use below method to remove the oracle home using Linux commands.

Stop all the oracle databases as well as processes running from the ORACLE_HOME.

Delete ORACLE_HOME
*** please be cautious while you are  using rm -Rf command
cd $ORACLE_HOME
rm -Rf *
Delete ORACLE_BASE

cd $ORACLE_BASE
rm -Rf *
Remove oratab file
rm /etc/oratab

Oracle 12cR2 silent installation

Step-1 : download theOracle 12cR2 software files, copied it to Linux server and unziped the files.
Step-2: Create response file under /tmp location with below details 
vi /tmp/12cR2_response_silentinstall.rsp

Note:  Please replace ORACLE_HOSTNAME, ORACLE_HOME and ORACLE_BASE location in the below file

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.2.0
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME= ip-172-3-16-9
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=dba
oracle.install.db.OSBACKUPDBA_GROUP=dba
oracle.install.db.OSDGDBA_GROUP=dba
oracle.install.db.OSKMDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true
oracle.installer.autoupdates.option=SKIP_UPDATES

STEP -3 : Go to 12cR2 installation software location and run the runInstaller in silent mode
cd  <12c software copied location>/database

STEP -4 :  run below script for silent installation.
./runInstaller -ignoreSysPrereqs -showProgress -silent -responseFile /tmp/12cR2_response_silentinstall.rsp