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
