ORA-00261: log 11 of thread 1 is being archived or modified

##########################
## Error
##########################

ORA-00261: log 11 of thread 1 is being archived or modified

### Full Error

ORA-00261: log 11 of thread 1 is being archived or modified
ORA-00312: online log 11 thread 1: '+REDO01/oralin/onlinelog/group_11.1611.951155487'
ORA-00312: online log 11 thread 1: '+REDO02/oralin/onlinelog/group_11.1611.951155489'
ORA-00312: online log 11 thread 1: '+REDO03/oralin/onlinelog/group_11.1611.951155489'

##########################
#  Error Occurred
##########################

Error occured while trying to drop standby redo log groups in dataguard standby database in 11.2.0.4.0 version

##########################
## Command Executed
##########################

Alter database drop logfile group 11, group 14, group 18

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Reproduce the Error
#########################################

SQL> Alter database drop logfile group 11, group 14, group 18;
Alter database drop logfile group 11, group 14, group 18
*
ERROR at line 1:
ORA-00261: log 11 of thread 1 is being archived or modified
ORA-00312: online log 11 thread 1: '+REDO01/oralin/onlinelog/group_11.1611.951155487'
ORA-00312: online log 11 thread 1: '+REDO02/oralin/onlinelog/group_11.1611.951155489'
ORA-00312: online log 11 thread 1: '+REDO03/oralin/onlinelog/group_11.1611.951155489'

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

The purpose of dropping the redo log group is to recreate the redo log groups with an increased log size.

Redo log member is in ACTIVE state due to which we are unable to drop the redo log groups.

=====================================================================================================================

#########################################
# 3) Verify MRP process is down
#########################################

Make sure MRP process is down in standby database.

=====================================================================================================================

##########################
## Solution
##########################

Clear the data stored in STANDBY Redo Log group to bring the REDO LOG MEMBER status from ACTIVE to INACTIVE state.

Alter database clear logfile group 11, group 14, group 18;

SQL> Alter database clear logfile group 11, group 14, group 18;
Database altered.

=====================================================================================================================
Once Redo logs are cleared. Drop command worked successfully.
=====================================================================================================================

SQL> Alter database drop logfile group 11, group 14, group 18;
Database altered.

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

ORA-10458: standby database requires recovery

We want to place the Standby database to READ ONLY mode which is in Managed recovery mode of state MOUNTED.

##########################
## Error
##########################

ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '+DATA/oralin/datafile/system.264.854321773'

### Full Error

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '+DATA/oralin/datafile/system.264.854321773'

##########################
#  Error Occurred
##########################

Error occured while placing a standby database which is in mount stage to READ ONLY mode in 11.2.0.3.0 version

##########################
## Command Executed
##########################

alter database open read only;

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check MRP is running
#########################################

SQL> !ps -ef|grep mrp
oracle   26516     1  0 15:09 ?        00:00:00 ora_mrp0_oralin2
oracle   26753 21120  0 15:10 pts/2    00:00:00 /bin/bash -c ps -ef|grep mrp
oracle   26755 26753  0 15:10 pts/2    00:00:00 grep mrp

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

To make standby database READ ONLY, all the datafiles should be consistent. Error is occuring because MRP is still running in the database which keeps the media recover on.

=====================================================================================================================

##########################
## Solution
##########################

#### Stop the Media recovery and then place the database in read only mode..

SQL> recover managed standby database cancel;
Media recovery complete.

SQL>  alter database open read only;
Database altered.

=====================================================================================================================
Then if you want to start the MRP, we can do like below,
=====================================================================================================================

SQL> Recover managed standby database disconnect from session using current logfile;
Media recovery complete.
SQL>

SQL> !ps -ef|grep mrp
oracle   26516     1  0 15:09 ?        00:00:00 ora_mrp0_oralin2
oracle   26753 21120  0 15:10 pts/2    00:00:00 /bin/bash -c ps -ef|grep mrp
oracle   26755 26753  0 15:10 pts/2    00:00:00 grep mrp

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

ORA-12154: TNS: could not resolve the connect identifier specified

##########################
## Error
##########################

ORA-12154: TNS: could not resolve the connect  identifier specified

### Full Error

Mon Nov 04 21:01:00 2013
Error 12154 received logging on to the standby

##########################
#  Error Occurred
##########################

Error occured while Primary database is trying to connect to a standby database for transfering archive logs in 11.2.0.3.0 version

##########################
## Command Executed
##########################

Primary trying to transfer Archive logs to Standby

##########################
## Issue Description / Troubleshooting
##########################

### 11.2.0.3.0

#########################################
# 1) Check Whether archive destination is showing any errors
#########################################

set lines 200
col dest_name for a20
col destination for a50
col error for a15
col db_unique_name for a15
col name for a100

select dest_id,dest_name,target,name_space,destination,status,error,db_unique_name from v$archive_dest where destination is not null;

   DEST_ID DEST_NAME            TARGET  NAME_SP DESTINATION                                        STATUS    ERROR           DB_UNIQUE_NAME
---------- -------------------- ------- ------- -------------------------------------------------- --------- --------------- ---------------
         1 LOG_ARCHIVE_DEST_1   PRIMARY SYSTEM  USE_DB_RECOVERY_FILE_DEST                          VALID                     NONE
         2 LOG_ARCHIVE_DEST_2   STANDBY SYSTEM  oralin_stdby                                        ERROR     ORA-12154:      oralin_stdby
                                                                                                             TNS:could not
                                                                                                             resolve the
                                                                                                             connect
                                                                                                             identifier
                                                                                                             specified

#########################################
# 2) TNS Names Used to Connect
#########################################


oralin_stdby.example.com =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = kstdbylorap08)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oralin_stdby.example.com)
    )
  )

#########################################
# 3) Check Service & Domain parameters
#########################################

In standby :

SQL> sho parameter servi

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      oralin_stdby
db_domain                            string

##########################
## Solution
##########################

From Troubleshooting 2) output, we can see from TNSNAMES that service name is oralin_stdby and Domain is example.com

From Troubleshooting 2) output, Service name is correct but there is no domain set at the database level.

So the problem is with the domain name. I have removed Domain (example.com) from TNSNAMES and the connection to database was successful.

Modified TNSNAMES :


oralin_stdby.example.com =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = kstdbylorap08)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oralin_stdby)
    )
  )

ORA-01031: insufficient privileges ==> GAPS in Dataguard Primary and Standby Databases

##########################
## Error
##########################

ORA-01031: insufficient privileges

### Full Error

ORA-01031: insufficient privileges

##########################
#  Error Occurred
##########################

Dataguard Primary and standby are not in SYNC and log_archive_dest of standby site is showing ORA-01031 error in 11.2.0.3.0 version

##########################
## Command Executed
##########################

In Sqlplus

select dest_id,dest_name,target,name_space,destination,status,error,db_unique_name from v$archive_dest where destination is not null;

##########################
## Issue Description.
##########################

### 11.2.0.3.0

#########################################
# Gaps in Primary n Standby
#########################################

In Primary :
--------------

set lines 200
col dest_name for a20
col destination for a50
col error for a15
col db_unique_name for a15
col name for a100

select dest_id,dest_name,target,name_space,destination,status,error,db_unique_name from v$archive_dest where destination is not null;


   DEST_ID DEST_NAME            TARGET  NAME_SP DESTINATION                                        STATUS    ERROR           DB_UNIQUE_NAME
---------- -------------------- ------- ------- -------------------------------------------------- --------- --------------- ---------------
         1 LOG_ARCHIVE_DEST_1   PRIMARY SYSTEM  USE_DB_RECOVERY_FILE_DEST                          VALID                     est01p_stl
         2 LOG_ARCHIVE_DEST_2   STANDBY SYSTEM  est01p_gvl                                         ERROR     ORA-01031:      est01p_gvl
                                                                                                             insufficient
                                                                                                             privileges

Select thread#,sequence# from v$thread;

   THREAD#  SEQUENCE#
---------- ----------
         1      15933
         2        231

Standby :
------------

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  15933                 15933          0
         2                    212                   212          0

##########################
## Solution
##########################

From the above output we can see that, In Standby Destination received archive log sequence is 212 of thread 2, but in primary its 231.

#########################################
# Check Password File in Standby
#########################################


est01p_1 @ hostorap01:/u01/app/oracle/product/11.2/db_6/dbs
> ls -ltr orapwe*
est01p_1 @ hostorap01:/u01/app/oracle/product/11.2/db_6/dbs

Password File is not available for est01p database.

Create a new password file ( or ) FTP the password file from Primary to STandby location.

After ftping the password file, GAPS are resolved automatically.

In Primary :
--------------

set lines 200
col dest_name for a20
col destination for a50
col error for a15
col db_unique_name for a15
col name for a100

select dest_id,dest_name,target,name_space,destination,status,error,db_unique_name from v$archive_dest where destination is not null;

   DEST_ID DEST_NAME            TARGET  NAME_SP DESTINATION                                        STATUS    ERROR           DB_UNIQUE_NAME
---------- -------------------- ------- ------- -------------------------------------------------- --------- --------------- ---------------
         1 LOG_ARCHIVE_DEST_1   PRIMARY SYSTEM  USE_DB_RECOVERY_FILE_DEST                          VALID                     est01p_stl
         2 LOG_ARCHIVE_DEST_2   STANDBY SYSTEM  est01p_gvl                                         VALID                     est01p_gvl
         3 LOG_ARCHIVE_DEST_3   PRIMARY SYSTEM  /u01/app/oracle/exp/est01p/arch02                  ALTERNATE                 est01p_Stl


Standby :
------------

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;


    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  15933                 15933          0
         2                    230                   230          0

ORA-19815 & ORA-16055 errors comes for different reasons also.

Whenever we see the error ORA-19815, we always assume that FRA is Nearing 100% and that is the reason we received the error.

But today i came across this and found that its not true all the times.

We used to get frequent ORA- errors from one of our Primary Dataguard database and the error recorded in Primary is ORA-16055 : FAL request rejected.

At the same time when we check the standby Alert log, error recorded in Standby is ORA-19815 which indicates that FRA is getting filled and reached 100%

##########################
#  Error
##########################

ORA-00270: error creating archive log
ORA-16055: FAL request rejected
ORA-16041: Remote File Server fatal error
ORA-19815: WARNING: db_recovery_file_dest_size of 424128020480 bytes is 96.93% used, and has 13021216768 remaining bytes available.

##########################
#  Errors Recorded in Primary 
##########################

Mon Jan  7 16:35:49 2013
Errors in file /opt/oracle/admin/primdb/bdump/primdb_arc2_5171.trc:
ORA-00270: error creating archive log
Mon Jan  7 16:35:49 2013
FAL[server, ARC2]: FAL archive failed, see trace file.
Mon Jan  7 16:35:49 2013
Errors in file /opt/oracle/admin/primdb/bdump/primdb_arc2_5171.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Mon Jan  7 16:35:49 2013
ORACLE Instance primdb - Archival Error. Archiver continuing.
Mon Jan  7 16:36:03 2013
Errors in file /opt/oracle/admin/primdb/bdump/primdb_arc1_5169.trc:
ORA-16041: Remote File Server fatal error

##########################
#  Errors Recorded in Standby 
##########################

Mon Jan  7 16:35:49 2013
Errors in file /opt/oracle/admin/primdb/udump/primdb_rfs_17693.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 424128020480 bytes is 96.93% used, and has 13021216768 remaining bytes available.
Mon Jan  7 16:35:49 2013
Errors in file /opt/oracle/admin/primdb/udump/primdb_rfs_17693.trc:
ORA-00270: error creating archive log +primdb_RECO
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 1062207488 bytes disk space from 424128020480 limit

##########################
#  Error Occurred
##########################

Error occurs randomly when RFS process tries to receive redo from Primary.

##########################
## Cause
##########################

Problem is actually caused by the parallel archive options set for Standby database destination in Primary Database.


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=stdbydb OPTIONAL ARCH SYNC NOAFFIRM REOPEN=30 MAX_CONNECTIONS=2 
NET_TIMEOUT=15 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdbydb

Eventhough we have log_archive_max_processes was 4 which can very well handle the parallel archiving option, error occurs.

I have also tried setting upto 10 archiver process which didnt help.

##########################

## Solution
##########################

As the problem is mainly because of the Parallel archiving option, i have modified to use only one connection for archiving by setting MAX_CONNECTIONS = 1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=stdbydb OPTIONAL ARCH SYNC NOAFFIRM REOPEN=30 MAX_CONNECTIONS = 1
 NET_TIMEOUT=15 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdbydb

1. Modify MAX_CONNECTIONS=1 in log_archive_dest_2
2. Defer and Enable log_archive_dest_state_2

This helped me and the false errors stopped recording in my primary and standby alert logs.

ORA-01274 - Resolve Archive Gaps in Standby Database

ORA-01274 Resolve Gaps in Standby Database when STANDBY_FILE_MANAGEMENT is set to MANUAL
============================================================

************** Step By Step Procedure **************

#########################################
# Issue
#########################################

Standby was not in SYNC with primary because STANDBY_FILE_MANAGEMENT is set to MANUAL. On 09-Nov-2012 two new datafiles were added in Primary, because of this manual

settings the files got created in $ORACLE_HOME/dbs location instead of Diskgroup.

#########################################
# Alert Log Errors
#########################################

Fri Nov 09 11:19:20 2012
File #6 added to control file as 'UNNAMED00006' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
MRP0: Background Media Recovery terminated with error 1274
Errors in file /u01/app/oracle/diag/rdbms/STDBY_gvl/STDBY_1/trace/STDBY_1_pr00_22594.trc:
ORA-01274: cannot add datafile '+STDBY_DATA/STDBY_stl/datafile/STDBY_data01.272.798895157' - file could not be created


#########################################
# 1) Database name & Mode
#########################################

set lines 200
col name for a15
col instance_name for a15
col log_mode for a10
col open_mode for a10
col database_Role for a20
col protection_mode for a20
col protection_level for a20
col remote_archive for a15
col flashback_on for a12

Select name,instance_name,dbid,log_mode,open_mode,database_role,protection_mode,protection_level,remote_archive,flashback_on from v$database,v$instance;

NAME            INSTANCE_NAME         DBID LOG_MODE   OPEN_MODE  DATABASE_ROLE        PROTECTION_MODE      PROTECTION_LEVEL     REMOTE_ARCHIVE  FLASHBACK_ON
--------------- --------------- ---------- ---------- ---------- -------------------- -------------------- -------------------- --------------- ------------
STDBY        STDBY_1      1579933494 ARCHIVELOG MOUNTED    PHYSICAL STANDBY     MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  ENABLED         NO

SQL> select * from v$dataguard_config;

DB_UNIQUE_NAME
------------------------------
STDBY_gvl
STDBY_stl


#########################################
# 2) Gaps in Primary & Standby
#########################################

###############
 Primary   >>>>>>>
###############

### Find GAps

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP

BY THREAD#) ORDER BY 1;

Select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             97


###############
 Standby   >>>>>>>
###############

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     97                    83         14

#########################################
# 3) Place Standby in MRP mode
#########################################

SQL> alter database recover managed standby database disconnect from session;

Database altered.

MRP process termined with the below error in Alert log.


Wed Nov 14 04:58:33 2012
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/app/oracle/diag/rdbms/STDBY_gvl/STDBY_1/trace/STDBY_1_pr00_32449.trc:
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u01/app/oracle/product/11.2/db_6/dbs/UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u01/app/oracle/product/11.2/db_6/dbs/UNNAMED00006'
Slave exiting with ORA-1111 exception


#########################################
# 4) Check datafile Status
#########################################

###############
 Primary   >>>>>>>
###############

set lines 200
col name for a75
Select file#,name,bytes/1024/1024 "Size in MB",creation_time,status,last_time from v$datafile;

     FILE# NAME                                                                        Size in MB CREATION_ STATUS  LAST_TIME
---------- --------------------------------------------------------------------------- ---------- --------- ------- ---------
         1 +STDBY_DATA/STDBY_stl/datafile/system.256.796313651                          760 17-SEP-11 SYSTEM
         2 +STDBY_DATA/STDBY_stl/datafile/sysaux.257.796313653                          840 17-SEP-11 ONLINE
         3 +STDBY_DATA/STDBY_stl/datafile/undotbs1.258.796313653                         80 17-SEP-11 ONLINE
         4 +STDBY_DATA/STDBY_stl/datafile/users.259.796313653                             5 17-SEP-11 ONLINE
         5 +STDBY_DATA/STDBY_stl/datafile/undotbs2.266.796313803                         25 10-OCT-12 ONLINE
         6 +STDBY_DATA/STDBY_stl/datafile/STDBY_omcbc_data01.272.798895451                   1024 09-NOV-12 ONLINE
         7 +STDBY_DATA/STDBY_stl/datafile/STDBY_omcbc_indx01.273.798895897                   1024 09-NOV-12 ONLINE


###############
 Standby   >>>>>>>
###############


set lines 200
col name for a75
Select file#,name,bytes/1024/1024 "Size in MB",creation_time,status,last_time from v$datafile;

     FILE# NAME                                                                        Size in MB CREATION_ STATUS  LAST_TIME
---------- --------------------------------------------------------------------------- ---------- --------- ------- ---------
         1 +STDBY_DATA/STDBY_gvl/datafile/system.259.796323701                          750 17-SEP-11 SYSTEM
         2 +STDBY_DATA/STDBY_gvl/datafile/sysaux.262.796323701                          830 17-SEP-11 RECOVER
         3 +STDBY_DATA/STDBY_gvl/datafile/undotbs1.260.796323701                         80 17-SEP-11 ONLINE
         4 +STDBY_DATA/STDBY_gvl/datafile/users.263.796323701                             5 17-SEP-11 ONLINE
         5 +STDBY_DATA/STDBY_gvl/datafile/undotbs2.261.796323701                         25 10-OCT-12 ONLINE
         6 /u01/app/oracle/product/11.2/db_6/dbs/UNNAMED00006                                   0 09-NOV-12 RECOVER


#########################################
# 5) Check the file which is wrongly created
#########################################


set lines 200
col name for a75
select file#, name, status, creation_time from v$datafile where name like '%UNNAMED%';

     FILE# NAME                                                                        STATUS  CREATION_
---------- --------------------------------------------------------------------------- ------- ---------
         6 /u01/app/oracle/product/11.2/db_6/dbs/UNNAMED00006                          RECOVER 09-NOV-12

SQL> sho parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL


select name,total_mb,free_mb from v$asm_diskgroup where state='CONNECTED';

NAME                                                                          TOTAL_MB    FREE_MB
--------------------------------------------------------------------------- ---------- ----------
STDBY_DATA                                                                   245823     242064
STDBY_RECO                                                                    65602      58545

#########################################
# 6) Rename/create Datafile to the correct Filename (Diskgroup)
#########################################

SQL>  alter database create datafile '/u01/app/oracle/product/11.2/db_6/dbs/UNNAMED00006' as '+STDBY_DATA\' size 1024m;

Database altered.

Verify the Filename is changed or not


set lines 200
col name for a75
Select file#,name,bytes/1024/1024 "Size in MB",creation_time,status,last_time from v$datafile;

     FILE# NAME                                                                        Size in MB CREATION_ STATUS  LAST_TIME
---------- --------------------------------------------------------------------------- ---------- --------- ------- ---------
         6 +STDBY_DATA/STDBY_gvl/datafile/STDBY_omcbc_data01.281.799315125                   1024 09-NOV-12 ONLINE



#########################################
# 7) Change the STANDBY_FILE_MANAGMENT to AUTO
#########################################

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;

System altered.


#########################################
# 8) Start the MRP
#########################################

Alter database recover managed standby database disconnect from session;

SQL> Alter database recover managed standby database disconnect from session;

Database altered.


#########################################
# 9) Check the MRP status
#########################################

set lines 200

Select process,client_process,thread#,sequence#,block#,blocks,status from v$managed_standby order by status;

PROCESS   CLIENT_P    THREAD#  SEQUENCE#     BLOCK#     BLOCKS STATUS
--------- -------- ---------- ---------- ---------- ---------- ------------
MRP0      N/A               1         84     106642     122977 APPLYING_LOG
ARCH      ARCH              1         97     124928       1886 CLOSING
ARCH      ARCH              1         96     131072        969 CLOSING
ARCH      ARCH              1         98     122880         98 CLOSING
ARCH      ARCH              0          0          0          0 CONNECTED
RFS       ARCH              0          0          0          0 IDLE
RFS       LGWR              1         99       1090          1 IDLE
RFS       UNKNOWN           0          0          0          0 IDLE

Now the logs started applying and GAPS are resolved now.

#########################################
# 10) Check the datafile status
#########################################

set lines 200
col name for a75
Select file#,name,bytes/1024/1024 "Size in MB",creation_time,status,last_time from v$datafile;

     FILE# NAME                                                                        Size in MB CREATION_ STATUS  LAST_TIME
---------- --------------------------------------------------------------------------- ---------- --------- ------- ---------
         1 +STDBY_DATA/STDBY_gvl/datafile/system.259.796323701                          760 17-SEP-11 SYSTEM
         2 +STDBY_DATA/STDBY_gvl/datafile/sysaux.262.796323701                          840 17-SEP-11 ONLINE
         3 +STDBY_DATA/STDBY_gvl/datafile/undotbs1.260.796323701                         80 17-SEP-11 ONLINE
         4 +STDBY_DATA/STDBY_gvl/datafile/users.263.796323701                             5 17-SEP-11 ONLINE
         5 +STDBY_DATA/STDBY_gvl/datafile/undotbs2.261.796323701                         25 10-OCT-12 ONLINE
         6 +STDBY_DATA/STDBY_gvl/datafile/STDBY_omcbc_data01.281.799315125                   1024 09-NOV-12 ONLINE
         7 +STDBY_DATA/STDBY_gvl/datafile/STDBY_omcbc_indx01.282.799315233                   1024 09-NOV-12 ONLINE



###############
 Standby   >>>>>>>
###############

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     98                    98          0


SQL> sho parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO

Data Guard Interview Questions.

-->

Hi, The below interview questions and answers are taken from other websites. If you come across any other questions please comment. I will add it to this.

Comments are always welcomed.

1. Can Oracle's Data Guard be used on Standard Edition, and if so how? How can you test that the standby database is in sync?

Oracle's Data Guard technology is a layer of software and automation built on top of the standby database facility. In Oracle Standard Edition it is possible to be a standby database, and update it *manually*. Roughly, put your production database in archivelog mode. Create a hotbackup of the database and move it to the standby machine. Then create a standby controlfile on the production machine, and ship that file, along with all the archived redolog files to the standby server. Once you have all these files assembled, place them in their proper locations, recover the standby database, and you're ready to roll. From this point on, you must manually ship, and manually apply those archived redologs to stay in sync with production.

To test your standby database, make a change to a table on the production server, and commit the change. Then manually switch a logfile so those changes are archived. Manually ship the newest archived redolog file, and manually apply it on the standby database. Then open your standby database in read-only mode, and select from your changed table to verify those changes are available. Once you're done, shutdown your standby and startup again in standby mode.


2. What is the difference between Active Dataguard, and the Logical Standby implementation of 10g dataguard?

Active dataguard is mostly about the physical standby.

Use physical standby for testing without compromising protection of the production system. You can open the physical standby read/write - do some destructive things in it (drop tables, change data, whatever - run a test - perhaps with real application testing). While this is happening, redo is still streaming from production, if production fails - you are covered. Use physical standby for reporting while in managed recovery mode. Since physical standby supports all of the datatypes - and logical standby does not (11g added broader support, but not 100%) - there are times when logical standby isn’t sufficient. It also permits fast incremental backups when offloading backups to a physical standby database.

3. What is a Dataguard?

Oracle Dataguard is a disaster recovery solution from Oracle Corporation that has been utilized in the industry extensively at times of Primary site failure, failover, switchover scenarios.


4. What are the uses of Oracle Data Guard?

a) Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data.

b) Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions.

c) With Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.


5. What is Redo Transport Services?

It control the automated transfer of redo data from the production database to one or more archival destinations.

Redo transport services perform the following tasks:

a) Transmit redo data from the primary system to the standby systems in the configuration.
b) Manage the process of resolving any gaps in the archived redo log files due to a network failure.
c) Automatically detect missing or corrupted archived redo log files on a standby system and automatically retrieve replacement archived redo log files from the
primary database or another standby database.


6. What is apply services?

Apply redo data on the standby database to maintain transactional synchronization with the primary database. Redo data can be applied either from archived redo log files, or, if real-time apply is enabled, directly from the standby redo log files as they are being filled, without requiring the redo data to be archived first at the standby database. It also allows read-only access to the data.

7. What is difference between physical and standby databases?

The main difference between physical and logical standby databases is the manner in
which apply services apply the archived redo data:

a) For physical standby databases, Data Guard uses Redo Apply technology, which applies redo data on the standby database using standard recovery techniques of
an Oracle database.

b) For logical standby databases, Data Guard uses SQL Apply technology, which first transforms the received redo data into SQL statements and then executes the
generated SQL statements on the logical standby database.


8. What is Data Guard Broker?

Data guard Broker manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager. It can be used to perform:

a) Create and enable Data Guard configurations, including setting up redo transport services and apply services
b) Manage an entire Data Guard configuration from any system in the configuration
c) Manage and monitor Data Guard configurations that contain Oracle RAC primary or standby databases
d) Simplify switchovers and failovers by allowing you to invoke them using either a single key click in Oracle Enterprise Manager or a single command in the DGMGRL command-line interface.
e) Enable fast-start failover to fail over automatically when the primary database becomes unavailable. When fast-start failover is enabled, the Data Guard broker determines if a failover is necessary and initiates the failover to the specified target standby database automatically, with no need for DBA intervention.

9. What are the Data guard Protection modes and summarize each?

Go for Dataguard Concept and administration Guide for good definition of each modes. Page no 33

Maximum availability :

This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one standby database.

Maximum performance :

This is the default protection mode. It provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log.

Maximum protection :

This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions.