How to verify that a certain commit has been transmitted to standby

dataguardoracle

Scenario:

Oracle Data guard 12c with 1 primary and 1 standby, Max. Av. protection mode
Primary open, standby mounted (all green in OEM)
Perform a dummy CTAS in primary

Question:

Without opening it, how can I be sure the data has been received by standby? Is there a log, a table, anything, that I can consult in Primary?

select sequence#, applied from v$archived_log;

shows a lot of not applied logs but I'm not sure it is relevant. Also, it does not really identify the query.

Thank you

Best Answer

Without opening the standby for read-only, you won't be able to identify specific changes to specific physical objects, instead you'll have to verify that a specific set of changes have been applied to the remote standby. Views including v$standby_apply_snapshot, v$archived_log, v$archive_gap, v$dataguard_stats, v$managed_standby are all ones that I use on a regular basis to verify standby apply health.

Additionally, you can use the dgmgrl command line interface to show the status of your log apply on the standby.

Related Solutions

DBF file exist while user has been dropped

Datafiles are allocated to tablespaces, not users. Dropping the users will remove the tables etc. from the tablespace, but the underlying storage is not affected.

Don't delete the dbf files from the filesystem directly, you'll get your database into a mess. To remove them, find which tablespace the files belong to using the following statement:

select tablespace_name
from   dba_data_files
where  file_name = <file name with full path>;

You can then remove the file(s) by dropping the tablespace:

drop tablespace <tablespace_name> including contents and datafiles;

Before doing this you should verify that there aren't any objects still allocated to the tablespace for other users however. You can find this by running:

select * from dba_segments
where  tablespace_name = <tablespace to drop>;

If this returns anything, move the objects to another tablespace if you want to keep them before dropping the tablespace.

Windows – How to delete a file that has been locked by UTL_FILE

Is the session dead?

SELECT sid, serial# FROM v$session WHERE /* conditions */;

-- ALTER SYSTEM KILL SESSION '<sid>, <serial>';
ALTER SYSTEM KILL SESSION '123, 456';

If killing the session doesn't work, you can explicitly close the file handle with a SysInternals utility called Handle.

https://technet.microsoft.com/en-us/sysinternals/bb896655

I also found a SuperUser thread on this which mentions LockHunter and Process Explorer as other tools that can do the job:

https://superuser.com/questions/425727/how-to-unlock-file-without-kill-related-process

Best Answer

Related Solutions

DBF file exist while user has been dropped

Windows – How to delete a file that has been locked by UTL_FILE

Related Question