Category: Databases

The below MS Word Document contains the steps required to set up Flashback on a primary and standby RAC.

ORA Flashback RAC with Standby

This PL/SQL code identifies a list of tables that exist in a schema. For each table, it issues a count(*) dynamically.

set serveroutput on;
spool get_all_tables
 
declare
    dyn_sql varchar(300);
    count_x number (10);
    temp_tab_name char(40);
begin
for t in(select owner, substr(table_name,01,40) as table_name from dba_tables where owner = '' order by table_name)
  loop
     temp_tab_name := t.table_name;
     dyn_sql := 'select count(*) from ' || t.owner || '.' || t.table_name;
     execute immediate dyn_sql into count_x;
     dbms_output.put_line(t.owner || ' ' || temp_tab_name || ' = ' || count_x || ' rows');
  end loop;
end;
/
spool off

If a data file is added on a primary with a physical standby and there is no appropriate file name convert, the file gets created in the Oracle home. On a RAC this may also report as a certain node unable to lock a file as the file got created in the Oracle home (non ASM) of a different node.

Log on to the standby

Stop mrp

Set standby_file_management = ‘MANUAL’

Find the name of the “bad file”. In my case it was /oracle/product/11.2.0.3/db_1/dbs/UNNAMED00034

You can find the bad file with:

select     name 
from       v$datafile 
where not name like '+%'
;

Find the correct name of the file from the primary.

The file will not actually exist in /opt/oracle. Then

alter database create datafile '/oracle/product/11.2.0.3/db_1/dbs/UNNAMED00034' as '+ASM mount point/usually DB name/datafile/correct name of file from primary';

The goal is to create the file in the correct location as it would have been created if the correct file name convert was in place.

Check v$datafile again

Set standby_file_management = ‘AUTO’

Start MRP