Drop And Recreate Undo Tablespace

==================================

https://docs.oracle.com/database/121/SQLRF/statements_7003.htm#SQLRF01403

1. Check the existing UNDO details:

SQL> show parameter undo

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

temp_undo_enabled                    boolean     FALSE

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS3

2. Create a new undo tablespace:

SQL>  CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u02/oradata/ARCH/undotbs101.dbf' SIZE 500M AUTOEXTEND ON RETENTION GUARANTEE;

3. Update undo_tablespace parameter

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1 SCOPE=BOTH;

 

System altered.

 

SQL>  show parameter undo

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------

temp_undo_enabled                    boolean     FALSE

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

4.Drop the old undo tablespace

SQL>  DROP TABLESPACE UNDOTBS3 INCLUDING CONTENTS AND DATAFILES;

-------------------------------------------------------------------------------------------------------------------

1. Check for the active sessions for rollback segment in old tablespace if drop unsuccessfull.

set pagesize 200

set lines 200

set long 999

col username for a9

SELECT a.name,b.status , d.username , d.sid , d.serial#

FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d

WHERE  a.usn = b.usn

AND    a.usn = c.xidusn

AND    c.ses_addr = d.saddr

AND    a.name IN (

  SELECT segment_name

  FROM dba_segments

 WHERE tablespace_name = 'UNDOTBS3'

);

NAME                           STATUS          USERNAME         SID    SERIAL#

------------------------------ --------------- --------- ---------- ----------

_SYSSMU10_46915872145$          PENDING OFFLINE DBACLASS         193      732119

5. Kill the active session(s) which is using old tablespace

SQL> alter system kill session '193,732119' immediate;

System altered.

ORA-01665: control file is not a standby control file


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE nodelay DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE nodelay DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01665: control file is not a standby control file
SQL>
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> SHU IMMEDIATE;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 629149632 bytes
Database Buffers 197132288 bytes
Redo Buffers 3952640 bytes
Database mounted.
SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
--------------------
PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL>

How To Change Flash Recovery Area Location

Below are the steps for changing the flash recovery area(FRA destination).

1. Check the current FRA destination.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> show parameter db_recovery_file   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest                string      /dbaclassdb/oradata/FRA db_recovery_file_dest_size           big integer 20G     SQL> select name from V$RECOVERY_FILE_DEST;   NAME                                   -------------------------------------- /dbaclassdb/oradata/FRA

2. Change db_recovery_file_dest parameter.

1 2 3 4 5 6 7 8 9 10 11 12

SQL> alter system set db_recovery_file_dest='/fradg/oradata/FRA' scope=both;   System altered.     select name from V$RECOVERY_FILE_DEST; NAME                                   SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID -------------------------------------- ----------- ---------- ----------------- --------------- ---------- /fradg/oradata/FRA               2.1475E+10          0                 0               0          0

From onwards new files will be created in this new location. However, the flashback logs will be c

For the FLASHBACK logfiles to be able to pick up the new ‘db_recovery_file_dest’ location, we just need to disable and enable the flashback, which needs a downtime.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

1.  Drop restore point if any   SQL> select GUARANTEE_FLASHBACK_DATABASE,NAME from v$restore_point; GUARANTEE_FLASHBACK_DATABASE     NAME -------------------------------- ----------------------------- YES                              RESTORE_UPGRDE     SQL > Drop restore point  restore_point RESTORE_UPGRDE ;   2. Turn off and turn on flashback :   ALTER DATABASE FLASHBACK OFF; ALTER DATABASE FLASHBACK ON;