How to Resolve Gaps in Data Guard Apply Using Incremental RMAN Backup

Suppose in between primary & standby database have many archives difference like 1000 archives, so we have need to recover it using incremental backup.

Action ON STANDBY:
Step-1. Check the current SCN number on standby using the following command but before it just cancels the recovery.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> SELECT CURRENT_SCN FROM V$DATABASE; 
      CURRENT_SCN
----------------------
       512520
SQL> SHU IMMEDIATE;

Manually resolve archive log gap

Step-2. After that, we need to take incremental backup on the primary server using the above SCN number, follow me.

Action ON PRIMARY

RMAN > BACKUP AS COMPRESSED BACKUPSET INCREMENTAL FROM SCN 512520 DATABASE;

Step-3. After taking incremental backup need to create a new standby control file using the following commands.

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/standby_contrlfile.ctl';

Then copy both (backup & controlfile) backup on standby.

Action ON STANDBY

Step-4. Start standby database up to nomount status.

SQL> STARTUP NOMOUNT;

Step-5. Now replace all existing controlfiles with the backup of controlfile be careful then start standby up to mount status.

SQL> ALTER DATABASE MOUNT;

Step-6. connect with RMAN and start recovery, using the following command.

$rman target sys/Oracle12c@ocpdr

RMAN> catalog start with '/u01/';

RMAN> recover database noredo;

Step-7. After successfully recover of your standby database, start the redo apply process using the following command.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

How to check MRP process

Check MRP process is running or not.

SQL> SELECT SEQUENCE#,PROCESS,STATUS FROM V$MANAGED_STANDBY;

Congratulations now you have successfully recovered your standby. 

Another Error related this ORA-00257: Archiver error. Connect AS SYSDBA only until resolved Error

Our 11.2.0.4 physical standby database was down for about 6 days and after starting it back up it and after a few hours I checked the status. Everything appears to be caught up with redo applying and all archive redo logs transferred to the physical standby database. The following query shows a status of RESOLVABLE GAP (query is run on primary database):

select switchover_status from v$database;

There are no errors and no gaps anywhere except from the switchover status.

There are various reason which cause this error-

1. One of the common issue here is archive destination of your database is 100% full.
2. The mount point/disk group assigned to archive destination or FRA is dismounted due to OS issue/Storage issue.
3. If db_recovery_file_dest_size is set to small value.
4. Human Error – Sometimes location doesn’t have permission or we set to location which doesn’t exists.

What are Different Ways to Understand if there is ORA-00257: Archiver error. Connect AS SYSDBA only until resolved Error

There are different ways to understand what is there issue. Usually end user doesn’t understand the ORA- code and they will rush to you with a Problem statement as -> DB is running slow or I am not able to login to the database.

Check the Alert Log First –

Always check alert log of the database to understand what is the problem here –

I have set the log_archive_dest_1 to a location which doesn’t exists to reproduce ORA-00257: Archiver error. Connect AS SYSDBA only until resolved Error. So alert log clearly suggest that

ORA-19504: failed to create file %s

ORA-27040: file create error, unable to create file

Linux-x86-64 Error: 13: Permission denied.

Check Space availability –

Once you rule out that there is no human error, and the archive log location exists Now you should check if mount point/ disk group has enough free space available, if it is available for writing and you can access it.

If your database is on ASM, then you can use following query – Check for free_mb/usable file mb and state column against your diskgroup name.

SQL> select name,state,free_mb,usable_file_mb,total_mb from v$asm_diskgroup;

If your database is on filesystem, then you can use following OS command –

For linux, sun solaris -

$df -kh 

For AIX -

$df -gt   

If case you have FRA been used for archive destination then we have additional query to identify space available and how much is allocated to it.

What Happens when ORA-00257: Archiver error. Connect AS SYSDBA only until resolved Error occurs

Lets us understand what end user see and understand there pain as well. So when a normal user try to connect to the database which is already in archiver error (ORA-00257: Archiver error. Connect AS SYSDBA only until resolved ) state then they directory receive the error –

ORA-00257: Archiver error. Connect AS SYSDBA only until resolved on the screen.

How to Resolve ORA-00257: Archiver error. Connect AS SYSDBA only until resolved error

It’s always better to know the environment before firing any command. Archive deletion can be destructive for DR setup or Goldengate Setup.

Solution 1:
Check if you have DR database and it’s in sync based on that take a call of clearing the archive until sequence. You can use following command on RMAN prompt.

delete archivelog until sequence <sequence> thread <thread no>;

Solution 2:
You can change destination to a location which has enough space.

SQL>archive log list
SQL>show parameter log_archive_dest_1 
(or whichever you are using it, usually we use dest_1)

Say your diskgroup  +ARCH is full and +DATA has lot of space then you can fire
SQL> alter system set log_archive_dest_1='location=+DATA reopen';

You might be wondering why reopen. So since your archive location was full. There are chances if you clear the space on OS level and archiver process still remain stuck. Hence we gave reopen option here.

Solution 3:
Other reason could be your db_recovery_file_dest_size is set to lower size. Sometimes we have FRA enabled for archivelog. And we have enough space available on the diskgroup/ filesystem level.
SQL>archive log list;
SQL>show parameter db_recovery_file_dest_size
SQL>alter system set db_recovery_file_dest_size=<greater size then current value please make note of filesystem/diskgroup freespace as well>

example -
Initially it was 20G
alter system set db_recovery_file_dest_size=100G sid='*';