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:
————————-
45.000054
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:
· Shutdown standby database
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
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;
Database altered.
· · 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;
System altered.
· 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’;
Database altered.
· Now let’s reset “standby_file_management” to AUTO.
SQL> alter system set standby_file_management=’AUTO’ scope=both;
System altered.
· And start the MRP process again.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
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.
Thanks,
Alfredo