Category: FLASHBACK

How to Drop an Orphan LOB in Oracle Database

In Oracle 11.2.0.3 database, after dropped some tables, there are still lob objects in dba_objects and dba_segments.

Find the table name the lob belongs to:

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
recyclebin                           string      on
SQL> select u.name, o.name TABLENAME, decode(bitand(c.property, 1), 1, ac.name, c.name) as column_name
  from sys.obj$ o, sys.col$ c, sys.attrcol$ ac,sys.lob$ l,sys.obj$ lo,sys.obj$ io,
sys.user$ u,sys.ts$ ts
 where o.owner# = u.user#
and o.obj# = c.obj#
 and c.obj# = l.obj# and c.intcol# = l.intcol#
and l.lobj# = lo.obj# and l.ind# = io.obj# and l.ts# = ts.ts# and c.obj# =
ac.obj#(+)
 and c.intcol# = ac.intcol#(+) and lo.name ='SYS_LOB0000542255C00004$$';

NAME                           TABLENAME
------------------------------ ------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP_USER                    BIN$FvVDUKyLUJLgYyshAgoDnQ==$0
DATATBS
SQL>  purge table APP_USER."BIN$FvVDUKwcUJLgYyshAgoDnQ==$0";

Table purged.

The the lob will dropped successfully.

In order to avoid this problem, for databases with recyclebin turned ON, we can:

SQL> drop table APP_USER.TABLENAME PURGE;

How to Relocate Flashback Logs to New Disk Group

we do not have any method to manually move old restore logs to new location via OS commands. Please drop any old restore points & create new restore points so that +FRAOLD gets cleared & new logs start generating in +FRANEW.

For restore point only:

SQL> select GUARANTEE_FLASHBACK_DATABASE,NAME from v$restore_point;

Drop Restore Point in primary database
On primary database:
SQL> drop RESTORE POINT restore_dg;

Drop Restore Point in standby database
Ensure the standby database is in mount state and drop
If Active Data Guard is used, open read only the standby database after dropping the Guaranteed restore point.
GUARANTEED RESTORE POINT :
SQL> drop restore point restore_dg;

For database flashback on:

When we modify FRA location, we need to flashback off & on the database so that flashback logs start generating in new location. When we flashback off & on, all the previously created restore points get void hence we need to drop them. We need to create a new restore point, this time as we have modified the FRA location, new flashback restore point logs start getting generated in new location.

Modify the flashback destinations ->
DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE
SQL> alter system set db_recovery_file_dest='/+FRANEW' scope=both;
SQL> alter system set db_recovery_file_dest_size='';

Drop restore point
SQL> select GUARANTEE_FLASHBACK_DATABASE,NAME from v$restore_point;
Drop Restore Point in primary database

On primary database:
SQL> drop RESTORE POINT restore_dg;
Drop Restore Point in standby database
Ensure the standby database is in mount state and drop
If Active Data Guard is used, open read only the standby database after dropping the Guaranteed restore point.
GUARANTEED RESTORE POINT :
SQL> drop restore point restore_dg;

Turn off and turn on flashback :
Since the Oracle DB version is 11g, we can perform the below activity with out downtime.
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;

Please do not run any OS command to remove flashback logs from +FRAOLD manually.

Oracle Flashback is ON or OFF in Oracle Database

Flashback is OFF

SQL>  select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
NO

Flashback is ON

SQL>  select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
YES

Flashback is OFF but with Restore Point

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

How To Enable Flash Recovery Area In 18c Oracle Database

1)Check and confirm Flash Recovery Area is not enabled.

SQL> show parameter db_recovery_file
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0

SQL> select * from V$RECOVERY_FILE_DEST;
no rows selected
SQL>

2) Enable Flash Recovery Area.

SQL> alter system set db_recovery_file_dest_size=10g scope=both;
System altered.

SQL>alter system set db_recovery_file_dest='/u01/app/oracle/fra' scope=both;
System altered.

SQL>  show parameter db_recovery_file
NAME                         TYPE        VALUE
---------------------------- ----------- ----------------------
db_recovery_file_dest        string      /u01/app/oracle/fra
db_recovery_file_dest_size   big integer 10G

SQL>  select * from V$RECOVERY_FILE_DEST;

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
---- ----------- ---------- ----------------- --------------- -------
/u01/app/oracle/fra 1.0737E+10       0       0         0       0

3) Configure  archive log destination to use flash recovery area.

SQL>alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 30
Next log sequence to archive 32
Current log sequence 32
SQL> select * from v$flash_recovery_area_usage;

FILE_TY PENT_SPACE_USED  PCENT_SPACE_REBLE NUMBER_OF_FILES    CON_ID
------------------------ ------------------ ---------------  ---------
CONTROL FILE     0                   0               0          0
REDO LOG         0                   0               0          0
ARCHIVED LOG     .59                 0               1          0
BACKUP PIECE     0                   0               0          0
IMAGE COPY       0                   0               0          0
FLASHBACK LOG    0                   0               0          0
FOREIGN ARCHIVED LOG  0              0               0          0
AUXILIARY DATAFILE COPY  0           0               0          0
8 rows selected.

Flashback RAC Database to Guarantee Restore Point ( GRP )

It is a good practice to create a guarantee restore point ( GRP ), before upgrading or patching databases.

Here is an example of how to create a guarantee  restore point ( GRP ), and how to flashback the RAC database to this guarantee  restore point ( GRP ).

1)Check and make sure database is running at archivelog mode.

SQL> set pagesize 120
SQL> set linesize 180
SQL> select LOG_MODE,FORCE_LOGGING,CURRENT_SCN,FLASHBACK_ON 
      from v$database;

LOG_MODE   FORCE_LOGGING CURRENT_SCN FLASHBACK_ON
---------- ------------- ----------- ------------
ARCHIVELOG YES           6622166     NO

2) Create the guarantee restore point.

SQL> select * from v$restore_point;
no rows selected

SQL> create table before_patching ( id number, name varchar2(30));
Table created.

SQL> create restore point BEFORE_PATCHING guarantee flashback database;
Restore point created.

SQL> create table after_GRP ( id number, name varchar2(30));
Table created.

SQL> select * from v$restore_point;

SCN       DT GUA STORAGE_SIZE TIME      REST NAME           CON_ID
-------- --- --- ------------ --------- ---- -------------- -------
 6623316 1   YES 4294967296   29-NOV-16 YES BEFORE_PATCHING 0

3) Applying the patches unsuccessfully.

 The rollback is required to flashback the database to guarantee restore point ( GRP ).

4) Shutdown RAC database,  and then start up current instance only in mount mode.

$ srvctl stop database -d RACTEST
$ srvctl start instance -d RACTEST -i RACTEST1 -o mount;

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> flashback database to restore point BEFORE_PATCHING;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select LOG_MODE,FORCE_LOGGING,CURRENT_SCN,FLASHBACK_ON 
       from v$database;

LOG_MODE   FORCE_LOGGING CURRENT_SCN  FLASHBACK_ON
---------- ------------  ----------  -----------------
ARCHIVELOG YES           6625602     RESTORE POINT ONLY

SQL> select count(*) from before_patching;

 COUNT(*)
----------
 0

SQL> select count(*) from after_GRP;  <---DB was flasged back to the correct point.
 select count(*) from after_GRP
 *
ERROR at line 1:
ORA-00942: table or view does not exist

5) Start up all the instances.

$ srvctl stop database -d RACTEST
$ srvctl start database -d RACTEST
$ srvctl status database -d RACTEST
Instance RACTEST1 is running on node racnode1
Instance RACTEST2 is running on node racnode2
Instance RACTEST3 is running on node racnode3
Instance RACTEST4 is running on node racnode4

6) Drop the guarantee  restore point ( GRP ).

SQL> drop restore point BEFORE_PATCHING;

Restore point dropped.

SQL> select * from v$restore_point;

no rows selected