Some days back checking an Oracle physical standby database found that the DB was some hours back than the primary database.
alter session set nls_date_format='DD-MM-yyyy HH24:MI:SS';
show parameter dest
select thread#,max(sequence#) from gv$log_history group by thread#;
select (a.amct-b.bmct)*24 "Hours Standby is Behind: "
from (select max(completion_time) amct from v$archived_log) a,
(select max(completion_time) bmct from v$archived_log where applied='YES') b;
Hours Standby is Behind:
The very next thing to check is what is going on with the MRP process.
select inst_id, process,status,sequence#, thread# from gv$managed_standby where process='MRP0';
no rows selected
So, the MRP process wasn’t running in the standby database. Let’s check the alert.log file.
MRP0: Background Media Recovery terminated with error 1237
ORA-01237: cannot extend datafile 13
The mount point where the datafile 13 resides is 100% full, that’s why the MRP couldn’t resize the datafile and was terminated by the instance.
In order to fix this you should increase the size of the mount point or if you have another mount point with enough free space you can do the following:
SQL> shutdown immediate
ORA-01109: database not open
ORACLE instance shut down.
· · Copy the datafile to the new location
#> cp –p users03.dbf /u02/oradata/test/users03.dbf
· Startup mount standby database
SQL> startup nomount
ORACLE instance started.
SQL> alter database mount standby database;
· · Modify “standby_file_management” parameter to manual
As per Oracle documentation:
STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database.
SQL> alter system set standby_file_management=’MANUAL’ scope=both;
· Rename the datafile in order to reflect the changes in the standby control file.
SQL> alter database rename file ‘/u01/oradata/test/users03.dbf’ to ‘/u02/oradata/test/users03.dbf’;
· Now let’s reset “standby_file_management” to AUTO.
SQL> alter system set standby_file_management=’AUTO’ scope=both;
· And start the MRP process again.
SQL> alter database recover managed standby database disconnect from session;
After this MRP was able to successfully apply archive logs from primary database.
We have to be sure that every time we increase the size of a datafile in the primary database, have enough free space in the standby server to fit the new size of the datafile.