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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s