You created undo tablespace using AUTOEXTEND ON MAXSIZE UNLIMITED to avoid error.

Your have just done puring (deleting millions of rows) on your production database and you noticed that your undo tablespace datafile is huge in size.

You tried to use “ALTER DATABASE DATAFILE .. RESIZE”, and this command failed with ORA-3297 error. , i.e.: “file contains ~~ blocks of data beyond requested RESIZE value”.

So you should drop and recreate undo tablespace using the following commands:

1, Connect to Oracle

sqlplus /nolog

connect sys/xxx@crm as sysdba

2, Find out which undo tablespace is being used:

select name,value from v$parameter where name in ('undo_management','undo_tablespace');

NAME VALUE
——————- ——————-
undo_management AUTO
undo_tablespace UNDOTBS1

3, Create new undo tablespace UNDOTBS2 with smaller size.

SQL> create undo tablespace UNDOTBS2 datafile K:\oradata\CRM\UNDOTBS2_01.DBF' size 1000m reuse;

4, Tell Oracle to use new undo tablespace.

SQL> alter system set undo_tablespace=UNDOTBS2;

5, Now you can safely drop and recreate tablespace UNDOTBS1.

SQL> drop tablespace UNDOTBS1 including contents;

Tablespace dropped.

SQL> create undo tablespace UNDOTBS1 datafile ‘K:\oradata\CRM\UNDOTBS1_01.DBF’ size 2000m reuse;

Tablespace created.

If you accidently dropped a table, a schema, or a data file, and you want to restore the entire database to an earlier time, here is what you should do:

1, Log in to RMAN

rman catalog rmancrm/rman@rman target sys/xxx@crm

2, Shutdown and startup mount database

shutdown immediate;

startup mount;

3, Restore and recover.

run
{
SET UNTIL TIME “to_date(’15-04-2007 05:00:00′,’dd-mm-yyyy hh24:mi:ss’)”;
RESTORE DATABASE;
RECOVER DATABASE;
}

4, Alter database open resetlogs.

alter database open resetlogs;

5, Do a full database backup.

The following scripts will let you recover your database to the last SCN in the archived redo logs.

1, Connect to RMAN

rman catalog rman/rman@rman target sys/change@prod

2, Restore Control file

startup nomount;
restore controlfile;
alter database mount;

3. Find out the last SCN

SQL> SELECT archivelog_change#-1 FROM v$database;

ARCHIVELOG_CHANGE#-1
——————–
2028371

1 row selected.

4. Restore and Recover using RMAN

run {
set until scn 2028371;
restore database;
recover database;
alter database open resetlogs;
}

5. Add temporary files.

sql "ALTER TABLESPACE TEMP ADD
TEMPFILE ”P:\Oracle\oradata\crm\temp01.dbf”
SIZE 2000M
AUTOEXTEND ON NEXT 64K”;

6. Register the newly recovered database as a new incarnation.

list incarnation;
reset database to incarnation x;


7. Backup your database now!