I have an Oracle 19c environment, where the primary has a standby and the standby has its own standby (Cascading DR). Somehow the standby's DR's incarnation changed to a new one. In the logs, I get this:

rfs (PID:1286351): A new recovery destination branch has been registered
 rfs (PID:1286351): Standby in the future of new recovery destination branch(resetlogs_id) 1177883943
 rfs (PID:1286351): Incomplete Recovery SCN:0x000000003b52f9a6
 rfs (PID:1286351): Resetlogs SCN:0x0000000039031be8
 rfs (PID:1286351): SBPS:0x0000000039031be5
 rfs (PID:1286351): Flashback database to SCN:0x0000000039031be5 (956505061) to follow new branch
 rfs (PID:1286351): New Archival REDO Branch(resetlogs_id): 1177883943  Prior: 1115909325
 rfs (PID:1286351): Archival Activation ID: 0x9cc516c2 Current: 0x9c3156ad
 rfs (PID:1286351): Effect of primary database OPEN RESETLOGS
 rfs (PID:1286351): Managed Standby Recovery process is active

there has been no such activity on the primary database which has resulted in such behavior. Can someone explain this?

Is it possible to exclude unchanged fields in Flashback query resultset? Consider I have following table

create table first_table
(
    id int generated as identity,
    name NVARCHAR2(1024),
    age smallint,
    notebook nclob,
    userpic clob,
    salary float
)

If the table has very frequent updates (e.g. on notebook field) following versioned query

select ROWID, VERSIONS_OPERATION, VERSIONS_STARTSCN, VERSIONS_STARTTIME, VERSIONS_XID, id, name, age, notebook, userpic, salary
from FIRST_TABLE versions between scn 1469193 and 1482882;

will pull heavy userpic value for every row even though it's the same. Can I somehow avoid that and instead get NULLs for unchanged values ?

i'm a programmer, not a dba. i need a mechanism to quickly make a snapshot of a database (oracle 11g XE) before i run migration. migration changes data, grants, DDL (tables, triggers, lexers, views etc). in case migration fails, i want to quickly revert to the saved state. all this will be done on non-production environment. it's just to speed up the development process i've heard about full backups, flashbacks, snapshots but i have no idea what's the difference and which one is best for my task

I have seen some recommendations in the Oracle Docs about what to do if the FRA is full. For example I have this config also: CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE'; The Databases have Dataguard and RAC. Oracle says: 1. Make more disk space - I cannot BACKUP RECOVERY AREA - I am interrested in this option. What does it that is not the same as the standard full backup where FRA also gets cleaned? Is it faster? I see it deletes the flashback logs, maybe I need them? If you use some other alternatives, I would be much appreciated. Thanks in advance.

I dropped a view 20 minutes ago. Can I recover it? If yes, how?

We have a requirement to read data as of certain time (say T1) from, say, schema 1, fill some staging tables in schema 2 with that data. And then read data from some other tables of schema 1 as of T1 based on the current data in the schema 2 staging tables (i.e. schema 1 and schema 2 are joined in the subsequent queries). Is there a way of achieving this without having to use AS OF in each of the (500+) queries in the stored procedure, wherever schema 1 tables appear? Also there are lots of commits in the stored procedure due to the large amount of being inserted into schema 2. Business need is to have read consistency across all the queries on Schema 1 so it can be as of the start of the stored procedure execution. All the tables in the schema 1 are enabled for flashback queries. Issue we are facing is having to read the tables of schema 1 as of certain time, simultaneously with reading current data from tables in schema 2.

In Oracle (and probably elsewhere), executing an ALTER TABLE statement will do an implicit commit on the current transaction. We have a tool (written in Java) that should modify a schema by: * adding some columns * removing some columns * updating a description table with the new schema layout The tool as written will display the current schema layout to the user and allow him to modify it. (Basically adding or removing custom "attribute" columns to some tables) Once he's satisfied, he can apply his changes. Please note: The basic schema layout, and the fact that you need to ALTER TABLE to change some things, is predefined by a standard and cannot be changed by us, as other tools wouldn't work anymore. The problem now is that we cannot run these changes in a single transaction since, AFAIK, it's not possible to do multiple ALTER TABLE statements within a transaction. *What options do we have to "roll back" to the initial state if something goes wrong while applying the changes?* Note: Someone here proposed RESTORE POINT + FLASHBACK -- is it a good idea to call this from this (Java) tool? (We do not fully control the database instance at some sites where the tool should be used.) Note: Oracle 10g2 and above

In my scenario, I have a database with no flashback on, Only Archivelogs are active. No Auditing applied on database. 3 days ago some one drop all triggers, views, sequence from a specific user. Now is there any way to find out who did this? Can I go 3 days back without flashback on?

I can run this flashback query with no problem: select x from a as of timestamp sysdate; But if I use a table alias I get an error. select foo.x from a foo as of timestamp sysdate; ORA-00933: SQL command not properly ended How can I use "as of" with table aliases?

I have accidentally deleted a flashback point in a oracle 11g database,Is there a way to recover the restore point PS:If I have a **flashback point A** which was created **first**, and **flashback point B** which was created **later**.If I drop **flashback point B** and flashback to **flashback point A** will I be able to recover **flashback point B**. PPS: there are no other backups to recover from.

I am looking for information on recyclebin extents. When dropping a table its segments are renamed to 'BIN$...' system generated names and the extents are hidden. Will those extents still stop me from shrinking a data file (or tablespace)? If so I would like to account for them in the script. Currently I am using a query inspired by AskTom's maxshrink.sql: select tbs.TABLESPACE_NAME, df.FILE_NAME, round(MAX(e.BLOCKS+e.BLOCK_ID+1)*tbs.BLOCK_SIZE/1024/1024,2) "MinMB", round(MAX(df.BYTES)/1024/1024,2) "FileMB" from DBA_TABLESPACES tbs LEFT JOIN DBA_DATA_FILES df on tbs.TABLESPACE_NAME = df.TABLESPACE_NAME LEFT JOIN DBA_EXTENTS e on df.FILE_ID = e.FILE_ID AND df.RELATIVE_FNO = e.RELATIVE_FNO -- WHERE -- tbs.TABLESPACE_NAME like 'MY%' GROUP BY df.FILE_ID, df.RELATIVE_FNO, tbs.TABLESPACE_NAME, tbs.BLOCK_SIZE, df.FILE_NAME ORDER BY 1,2; *Update*: Initially my question also asked why I no longer see the 'BIN$' segments created by Flashback Drop Table anymore in 12.2 anymore. However that was an error on my side, my test tables had simply no segments to begin with.

We have a specific table that has a lot of activity and it creates a lot of change records. The consequence is that the flashback data only goes back a couple of days. That is OK for many cases but it would be beneficial to have access to more historical data. We would like to either restrict logging on that table. Or disable it completely. I imagine that we may be able to do this by tablespace, I just have not found much on how to make these changes.

I'm not finding a lot of examples online of how to use Oracle Flashback Query and I'm hoping somebody can clarify syntax. I'm trying to execute a query with a large number of tables in it, but the syntax seems to require me to specify a timestamp for every single table involved in the query. For example, the following query gets me consistent data, but it requires me to specify a timestamp for each table: select t1.Field1, t2.Field2 from table1 as of timestamp to_timestamp('2016-11-01 02:00:00','yyyy-mm-dd hh24:mi:ss') t1 join table2 as of timestamp to_timestamp(2016-11-01 02:00:00','yyyy-mm-dd hh24:mi:ss') t2 on t1.somekey = t2.somekey The following example (which is how most examples online look) gives past data for table1, but joined to current data from table2 - they don't line up: select t1.Field1, t2.Field2 from table1 as of timestamp to_timestamp('2016-11-01 02:00:00','yyyy-mm-dd hh24:mi:ss') t1 join table2 t2 on t1.somekey = t2.somekey Since I have a large number of tables, I'm hoping for a way I can execute the entire query against a flashback timestamp - something like this: select (as of timestamp to_timestamp('2016-11-01 02:00:00','yyyy-mm-dd hh24:mi:ss')) t1.Field1, t2.Field2 from table1 t1 join table2 t2 on t1.somekey = t2.somekey Anybody know if this is possible, or if the current syntax doesn't allow it? I'm using 11.2 if it matters, but nothing I see online suggests that this has changed in 12.

If I issue the following statement: drop table DontDrop purge; It's going to be flushed out of my recycle bin so flashback statement won't work: select * from DontDrop as of timestamp to_timestamp(sysdate-1); Now, can I use a flashback database statement to restore it? flashback database to timestamp (sysdate-1);

I am using Oracle 11.1 and would like to figure out if two rows in one table where inserted by the same transaction. Is there an easy sql statement to map a row to an transactions id? The table is an insert only table. What are my options? Some baisc solution would do, I don't really want some extra tool to analyze the log files. Thanks

I set db_flashback_retention_target=1440min,Why the flashback log kept 7 days? Thanks! ---------- closed as unclear what you're asking by Phil, Michael Green, Tom V, Shanky, Andriy M Jan 5 at 12:53 Why the sql-server users can close my question? They know what flashback_log is?

We're creating a new version of a 20 years old database, in which we used to keep version history by creating new rows in each table (SCD Type 2). **SCD Type 2** is not that hard when you need to keep history on a single table. However, if a table has dependencies that go all the way up (or down) in the database structure, you need to keep history of the whole structure. It is difficult to keep this clean enough, so I am looking at alternatives. **1rst alternative** I am aware of solutions like Oracle's flashback. What I am not sure about is if it is flexible when it comes to small schema changes (adding a column)? We won't have big changes to our schema, but in 5 years we might discover that we need some additional info. **2nd alternative** We're thinking of creating a history table where we keep the affected structures as XML. If the schema changes, we will create the tools that are going to update the history to include the new schema. Any pros and cons of these solutions? Any suggestions are welcome (for other alternatives too)! PS: As you might have guessed we're using **Oracle**.

I should execute following tasks: 1. test system and during test change Oracle data and Create New/Remove Old datafiles and tablespace. 2. at the end of test rollback to behviour before of point 1. I'm working on Oracle 11.2 on two different sites: on single instance configuration and another in DataGuard environment. To reach the scope I think following possibilities: 1) a- execute dump before starting. b- recreate manually datafiles/tablespaces and import data from dump at the end. 2) a- backup via RMAN all the DB. b- restore via RMAN the backup at the end. 3) a- create recovery-point. b- flashback at the end ....???? (I'm not sure about this chance with change on Datafiles and tablespace. What your opinion on it? Thanks, Ste

Just a general question about flashback and the recycle bin in Oracle. I tried doing this on a demo oracle database I have but the recycle bin does not seem to want to be populated; and I want a quick answer. One can do the following: flashback table table_name to before drop; alter index "BIN$U/9fvJKaASzgQKjAYAIWhw==$0" rename to sys_c1113050; Can you do this then, to restore a table: RENAME TABLE BIN$U/9fvJKaASzgQKjAYAIWhw==$0 TO TABLE_NAME

I have a requirement to retrieve (extract) data from an Oracle database (either 9i or 10g) as at a given point in time. I will need access to the data at this point of time for several days afterwards (as the database is large enough that the extract process will take several days to complete). The database is also in constant use. For example, on January 3rd, I may want to query data as it was on January 1st and I may need access to the data as it was on January 1st for several days (maybe until about January 7th). What is the best way to achieve this? I am not a DBA, but have looked into a number of solutions, that may form the basis of a solution, but would appreciate any advice on my best course of action. I am aware of flashback and the ability to query a table as it was at a given point in time. I am, however aware that keeping the amount of history I'd need to (probably at least 7 days worth) is likely to make this infeasible based on the size and high usage of the database. I am also aware that when you set flashback to record a given number of days worth of data, you may end up with significantly less than this based on high usage, so this would seem like a risky approach anyway? I am also aware of creating a Guaranteed Restore Point, but that this will only be an option if my database is 10g or above(?) If I did this, I believe I would have to turn Flashback off (?) as if I don't, flashback will have to keep logs all the way back to the time of the guaranteed restore point (and so in this example I'd end up with at least 7 days worth of history and risk the database running out of disk space). With a guaranteed restore point in place, is it possible to run a SELECT query on a table as at this restore point using something like the following: CREATE RESTORE POINT test_restore_point GUARANTEE FLASHBACK DATABASE; SELECT * FROM tbl1 AS OF test_restore_point; The only examples I have seen involve actually restoring the database to the restore point (which I don't want to do) rather than querying from it. I am unable to test this for myself as I currently only have access to a 9i database. I'd be very grateful for any advice on how I can achieve my objectives using this functionality or any other. Thanks