Wednesday, November 27, 2013

Standby archive apply hang due to space issue

Ideally at designing stage we should have allocated sufficient space for both PROD and STANDBY and they should match. For example, if a file system U05 is 500GB on PROD you should size the file system to 500GB on STANDBY as well. 

Well but not always. :)

We hang on issue when a DBA had added a datafile in Prod at U05 file system but due to lack space at DR Side for file system U05, archive apply stopped.

Standby_file_management is set to auto.

It was noted when archive apply was stopped due to space issue. 

Because is the steps taken to carried away: 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL; 

SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/11.2.0/db_1/dbs/UNNAMED00143' AS '/u06/realfilename/gdata05.dbf'; 

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 

or, If you are using real time apply (it makes use of standby redo log file) 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; 

Hope this helps...