Tales From A Lazy Fat DBA

Fan of Oracle DB & Performance, PostgreSQL & Cassandra … \,,/

  • Likes

    • 236,633
  • Archives

  • Categories

  • Subscribe

  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Posts Tagged ‘standby’

Real-Time Apply Error: ORA-38500: ‘USING CURRENT LOGFILE option not available without stand’

Posted by FatDBA on October 25, 2012

Hi Folks,

Going to discuss about one of the most recent problem that i faced while configuring/activating the Real-Time Apply service on one of the standby database.

(Using Oracle 10g r2)
Prior Status:
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2 ;

RECOVERY_MODE
———————–
MANAGED

To start real-time apply i used CURRENT LOGFILE clause but received below provided error message:

SQL> alter database recover managed standby database disconnect using current logfile;
alter database recover managed standby database disconnect using current logfile
*
ERROR at line 1:
ORA-38500: USING CURRENT LOGFILE option not available without stand

Resolution:
1. I created Log Groups and Log Files for Standby Database to try fix this issue and as per my expectations it worked.

Added Three log groups of same size 50MB each on Primary Database for Standby (Log Groups should be always be of same size – What you have on Primary DB, Mine was – 52428800 (50MB)):

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 SIZE 50M;
Database Altered

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
Database Altered

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
Database Altered

SQL> select group#, member, type from v$logfile;

GROUP#         MEMBER                                                                        TYPE
———- ———————————————————————————————————————————–
4       /u01/app/oracle/flash_recovery_area/DOOM/onlinelog/o1_mf_4_88j4601r_.log                      STANDBY
3       /u01/app/oracle/flash_recovery_area/DOOM/onlinelog/o1_mf_3_88j4859l_.log                      STANDBY
5       /u01/app/oracle/flash_recovery_area/DOOM/onlinelog/o1_mf_5_88j4d4t5_.log                      STANDBY

On Standby Database:
2. Added a standby logfile on Std DB end:

SQL> alter database add standby logfile ‘/u01/app/oracle/admin/doom/stdy_log.log’ size 10m;
Database altered.

SQL> alter database recover managed standby database disconnect using current logfile;
Database altered.

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2 ;
RECOVERY_MODE
———————–
MANAGED REAL TIME APPLY

SQL> select RECOVERY_MODE, DATABASE_MODE, PROTECTION_MODE from v$archive_dest_status;

RECOVERY_MODE           DATABASE_MODE   PROTECTION_MODE
———————– ————— ——————–
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE

11 rows selected.

Posted in Advanced | Tagged: , , | Leave a Comment »

Data Guard – Adding and Resizing a datafile on the Primary database

Posted by FatDBA on October 13, 2012

Standby Database (Data Guard) is slowly and steadily turning out to be my best topic/subject among others in High Availability. It attracts me to experiment to improve the HA features.

Alright, today I’ve fixed one of the problem i was facing from past few days and discovered that i was doing a stupid mistake and that i failed to catch too till the moment i rechecked some parameters in initialization file minute ago.

I tried to add/modify some data files and was not getting at the Standby End which resulted in several errors at Sb DB and lead to Standby Database crash. Otherwise i have to manual port log files from primary destination to the Standby party to maintain the sync between the two after datafiles addition.

Below were the error messages i was getting when tested the standby status:

SQL>  select message from v$dataguard_status;

MESSAGE
——————————————————————————–
ARC0: Archival stopped
ARC1: Archival stopped
Media Recovery Waiting for thread 1 seq# 197

Solution:
I’ve check and found Standby_File_Management was set to ‘MANUAL’
and both the db_file_name_convert and log_file_name_conver were different.

I’ve set all the three parameters to below provided values
Standby_File_Management=AUTO  (Both on Primary and Standby)
db_file_name_convert          ( I’ve left it unchanged on both sides – it was empty (default))
log_file_name_convert         ( I’ve left it unchanged on both sides – it was empty (default))

Snippets from my machine:
SQL> show parameter standby_file_management    (On both Primary and Standby)

NAME                                 TYPE        VALUE
———————————— ———– ——————————
standby_file_management              string      AUTO

SQL> show parameter db_file_name_convert

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_file_name_convert                 string

SQL> show parameter log_file_name_convert

NAME                                 TYPE        VALUE
———————————— ———– ——————————
log_file_name_convert                string

 

  1. Add a new tablespace to the primary database:
    SQL> CREATE TABLESPACE new_ts DATAFILE '/disk1/oracle/oradata/payroll/t_db2.dbf'
      2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;
  2. Archive the current online redo log file so the redo data will be transmitted to and applied on the standby database:
    SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
  3. Verify the new datafile was added to the primary database:
    SQL> SELECT NAME FROM V$DATAFILE;
    NAME
    ----------------------------------------------------------------------
    /disk1/oracle/oradata/payroll/t_db1.dbf
    /disk1/oracle/oradata/payroll/t_db2.dbf
  4. Verify the new datafile was added to the standby database:
    SQL> SELECT NAME FROM V$DATAFILE;
    NAME
    ----------------------------------------------------------------------
    /disk1/oracle/oradata/payroll/s2t_db1.dbf
    /disk1/oracle/oradata/payroll/s2t_db2.dbf

Posted in Advanced | Tagged: , | Leave a Comment »

RFS Process Missing – Alert Log Trace reads * Error in File sid_rfs_3724.trc *

Posted by FatDBA on September 4, 2012

Problem: If Alert Log File Reads – “Errors in file /u01/app/oracle/admin/qrcl/udump/qrcl_rfs_3724.trc” and RFS process is missing at the Standby end and you discovered difference in Archived Log Sequence.

Resolution: Export Archive logs from Primary DB side to Standby location and start recovery process on the Auxiliary end.

Exit managed recovery mode, add the logs, and then reinitiate managed recovery:

alter database recover managed standby database cancel;

[oracle@prashant1 2012_09_04]$ pwd
/u01/app/oracle/flash_recovery_area/QRCL/archivelog/2012_09_04
[oracle@prashant1 2012_09_04]$ scp * oracle@prashant2:/u01/app/oracle/flash_recovery
oracle@prashant2’s password:
o1_mf_1_293_84d32nq5_.arc
o1_mf_1_294_84d32qfx_.arc
o1_mf_1_295_84d3gr6s_.arc
o1_mf_1_296_84d3gz6b_.arc
o1_mf_1_297_84d3wt62_.arc
o1_mf_1_298_84d3x749_.arc
o1_mf_1_299_84d45g5x_.arc
o1_mf_1_300_84d52r4t_.arc
o1_mf_1_301_84d61r6n_.arc
o1_mf_1_302_84d61scr_.arc
o1_mf_1_303_84d61zfd_.arc
o1_mf_1_304_84d62c0v_.arc alter database recover managed standby database disconnect;

Start the managed recovery operation:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 

Recheck Log Sequence and Status/Process from v$Managed_standby and you’ll find the RFS process back again along with ARCH & MRP/MRP0.

Posted in Advanced | Tagged: , | Leave a Comment »

 
%d bloggers like this: