ORA-38753: Cannot flashback data file ; no flashback log data. ORA-01110: data file

CAUTION !!!! DO NOT DO THIS ON YOUR PRIMARY. IF YOU DO THEN YOU WOULD REQUIRE MEDIA RECOVERY ON THE DATAFILE YOU BROUGHT OFFLINE !!!

If your getting this error when you converting a Snapshot Standby Database (11gR2) back to Physical Standby Database.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

ERROR at line 1: ORA-38753: Cannot flashback data file 4; no flashback log data. ORA-01110: data file 4: ‘+DATA/swxstandby/datafile/swx.271.780160475’

When you get this error do as below :
SQL> select a.file#, a.name file_name, b.ts#, b.name ts_name, b.flashback_on from v$datafile a, v$tablespace b where a.ts#=b.ts# ;

TS# TS_NAME                        FLA ———- ——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————— ———- —————————— —          1 +DATA/swxstandby/datafile/system.270.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             0 SYSTEM                         YES          2 +DATA/swxstandby/datafile/sysaux.269.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             1 SYSAUX                         YES          3 +DATA/swxstandby/datafile/undotbs1.268.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           2 UNDOTBS1                       YES          5 +DATA/swxstandby/datafile/undotbs2.267.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           5 UNDOTBS2                       YES          4 +DATA/swxstandby/datafile/swx.271.780160475                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                7 SWX                            NO          6 +DATA/swxstandby/datafile/osms.266.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               9 OSMS                           YES

6 rows selected.

See which datafile has flashback_on colmn set as NO

SQL> alter database datafile 4 offline;

Database altered.

SQL> select flashback_on from v$database ;

FLASHBACK_ON —————— RESTORE POINT ONLY

SQL> select name, flashback_on from v$tablespace ;

NAME                           FLA —————————— — SYSTEM                         YES SYSAUX                         YES UNDOTBS1                       YES SWX                            NO TEMP                           YES UNDOTBS2                       YES OSMS                           YES

SQL> alter tablespace SWX flashback on;

Tablespace altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 6415597568 bytes

Fixed Size                  2171296 bytes
Variable Size            1979719264 bytes
Database Buffers         4429185024 bytes
Redo Buffers                4521984 bytes

Database mounted.

SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount ORACLE instance started.

Total System Global Area 6415597568 bytes

Fixed Size                  2171296 bytes
Variable Size            1979719264 bytes
Database Buffers         4429185024 bytes
Redo Buffers                4521984 bytes Database mounted.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select flashback_on from v$database ;

FLASHBACK_ON —————— NO

SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE            SWITCHOVER_STATUS    DATABASE_ROLE ——————– ——————– —————- MOUNTED              RECOVERY NEEDED      PHYSICAL STANDBY

Category: DatabaseRMANSQL

Tags:

Leave a Reply

Article by: Shadab Mohammad