Without Standby Redo Logs, an Archived Redo Log is created by the RFS process and when it has completed,
this Archived Redo Log is applied to the Standby Database by the MRP process . An incomplete ArchiveLog file cannot be applied on the Standby Database and will not be used in a Failover situation. This causes a certain data loss.

If you have Standby Redo Logs, the RFS process will write into the Standby Redo Log ,when there record entered in online redolog and when a log switch occurs, the Archiver Process of the Standby Database will archive this Standby Redo Log to an Archived Redo Log, while the MRP process applies the information to the Standby Database. In a Failover situation, you will also have access to the information already written in the Standby Redo Logs, so the information will not be lost.

NOTE:

• Size of standby redolog should be same as that of primary online redolog.
• The standby redo log must have at least one more redo log group than the redo log at the redo source database.
• Standby redolog is mandatory for realtime apply
• In 12c DEFAULT MRP will go to REAL TIME APPLY mode.
• Oracle recommends to keep standby redologs on both primary and standby database.(Though SLR is not mandatory for primary, it will make the switchover quick without any additional DBA activity.

Steps to add standby redolog:

1. If you are setting up standby database newly, i.e standby database is not created yet, then add the standby redologs on primary database, So that after duplicating standby database from primary, similar standby logs will be created in standby.

Just add the SLR on primary:

alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo1.log' size 2G;
alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo2.log' size 2G;
alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo3.log' size 2G;

2. If the standby database is already created without standby redolog. Then add the standby redolog on both primary and standby db as below.

Primary:

alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo1.log' size 2G;
alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo2.log' size 2G;
alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo3.log' size 2G;

Standby database:

cancel recovery:

alter database recover managed standby database cancel;

Make standby_file_management Manual:

alter system set  standby_file_management='MANUAL' scope=both;

add standby redolog:

alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo1.log' size 2G;
alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo2.log' size 2G;
alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo3.log' size 2G;

Revert standby_file_management to AUTO:

alter system set  standby_file_management='AUTO' scope=both;

Start MRP with real time:

alter database recover managed standby database using current logfile disconnect from session;

The post Standby redologs in oracle dataguard appeared first on DBACLASS.

I was cloning a database from rman backup . After completion of cloning, when I did RESETLOG it failed with ORA-00392 error.
RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/25/2017 16:05:27
ORA-00392: log 7 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 7 thread 1: ‘/archive/CLONEDB/redo7a.log’
ORA-00312: online log 7 thread 1: ‘/archive/CLONEDB/redo7b.log’

SOLUTION:

First, check the status of REDO LOGS

RMAN> select group#,thread#,status from v$log;
 
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         5          1 CLEARING
         6          1 CLEARING
         7          1 CLEARING_CURRENT   ---------->>> 
         8          1 CLEARING
         9          2 CLEARING_CURRENT   --------->>>>> 
        10          2 CLEARING
        11          2 CLEARING
        12          2 CLEARING

Group 7 and 9 have status CLEARING_CURRENT. So clear them manually.

RMAN> alter database clear logfile group 7;
 
Statement processed
 
RMAN> alter database clear logfile group 9;
 
Statement processed
 
RMAN> select group#,thread#,status from v$log;
 
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         5          1 CLEARING
         6          1 CLEARING
         7          1 CURRENT
         8          1 CLEARING
         9          2 CURRENT
        10          2 CLEARING
        11          2 CLEARING
        12          2 CLEARING
 
8 rows selected

Now no group is in clearing_current mode. Lets do resetlog again.

RMAN> alter database open resetlogs;
 
Database altered

Voilla…

Meaning of different states of redolog:

UNUSED – Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
CURRENT – Current redo log. This implies that the redo log is active. The redo log could be open or closed.
ACTIVE – Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
CLEARING – Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
CLEARING_CURRENT – Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
INACTIVE – Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
INVALIDATED – Archived the current redo log without a log switch.

FOR ANY QUERIES, PLEASE POST IN FORUM.DBACLASS.COM

The post ORA-00392: log 7 of thread 1 is being cleared, operation not allowed appeared first on DBACLASS.

After restoring an RAC database to a standalone database through RMAN, we tried to drop the redolog groups belongs to thread 2. We hit the below error.

SQL> alter database drop logfile group 8;
alter database drop logfile group 8
*
ERROR at line 1:
ORA-00350: log 4 of instance DBATST2 (thread 2) needs to be archived
ORA-00312: online log 8 thread 2: '/u01/oracle/DBATST/onlinelog/redo08.log'

SOLUTION:

Before dropping, we need to clear the redolog using below command.

SQL> alter database clear unarchived logfile group 8;

Database altered.

Now try to drop again:

SQL> alter database drop logfile group 8;

Database altered.

Voilla..

The post ORA-00350: log 4 of instance needs to be archived appeared first on DBACLASS.

1. Current redo log status:

col member for a56 
set pagesize 299 
set lines 299 
select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) "Size (MB)" 
from  v$log l, v$logfile f  where f.group# = l.group# order by 1,2;

GROUP# THREAD# MEMBER                                                                 ARCHIVED   STATUS     Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- ---------- ---------
     1       1 +DATA/PREPROD/redo01.log                                                YES        ACTIVE            50
     2       1 +DATA/PREPROD/redo02.log                                                NO         CURRENT           50
     3       2 +DATA/PREPROD/redo03.log                                                YES        INACTIVE          50
     4       2 +DATA/PREPROD/redo04.log                                                NO         CURRENT           50

Currently, the redologs are under +DATA diskgroup, which we will multiplex to +REDOA,+REDOB diskgroup and resize to 5G each.

2. Add logfile groups for node 1 :(THREAD 1)

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('+REDOA','+REDOB') SIZE 5G;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 ('+REDOA','+REDOB') SIZE 5G;

3. Add logfile groups for node 2 : (THREAD 2)

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 9 ('+REDOA','+REDOB') SIZE 5G;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 10 ('+REDOA','+REDOB') SIZE 5G;

4. Drop the old redolog groups:

col member for a56 
set pagesize 299 
set lines 299 
select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) "Size (MB)" 
from  v$log l, v$logfile f  where f.group# = l.group# order by 1,2;


GROUP# THREAD# MEMBER                                                                 ARCHIVED   STATUS     Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- ---------- ---------
     1       1 +DATA/PREPROD/redo01.log                                                YES        INACTIVE          50  ---- >>>
     2       1 +DATA/PREPROD/redo02.log                                                NO         CURRENT           50  ----->>>
     3       2 +DATA/PREPROD/redo03.log                                                YES        INACTIVE          50  ----->>>
     4       2 +DATA/PREPROD/redo04.log                                                NO         CURRENT           50  ----->>>
     5       1 +REDOA/PREPROD/ONLINELOG/group_5.256.943101611                          YES        UNUSED          5120
     5       1 +REDOB/PREPROD/ONLINELOG/group_5.256.943101623                          YES        UNUSED          5120
     6       1 +REDOA/PREPROD/ONLINELOG/group_6.257.943101633                          YES        UNUSED          5120
     6       1 +REDOB/PREPROD/ONLINELOG/group_6.257.943101643                          YES        UNUSED          5120
     9       2 +REDOA/PREPROD/ONLINELOG/group_9.258.943101671                          YES        UNUSED          5120
     9       2 +REDOB/PREPROD/ONLINELOG/group_9.258.943101679                          YES        UNUSED          5120
    10       2 +REDOA/PREPROD/ONLINELOG/group_10.259.943101687                         YES        UNUSED          5120
    10       2 +REDOB/PREPROD/ONLINELOG/group_10.259.943101695                         YES        UNUSED          5120

The old redolog groups are 1,2,3,4. And We can drop only the redolog groups whose status is INACTIVE.
Currently group 1, 3 are INACTIVE. Let’s drop them first.

alter database drop logfile group 1;
alter database drop logfile group 3;

col member for a56 
set pagesize 299 
set lines 299 
select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) "Size (MB)" 
from  v$log l, v$logfile f  where f.group# = l.group# order by 1,2;

GROUP# THREAD# MEMBER                                                                 ARCHIVED   STATUS     Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- ---------- ---------
     2       1 +DATA/PREPROD/redo02.log                                                NO         CURRENT           50 ---->>>
     4       2 +DATA/PREPROD/redo04.log                                                NO         CURRENT           50 ---->>>
     5       1 +REDOA/PREPROD/ONLINELOG/group_5.256.943101611                          YES        UNUSED          5120
     5       1 +REDOB/PREPROD/ONLINELOG/group_5.256.943101623                          YES        UNUSED          5120
     6       1 +REDOA/PREPROD/ONLINELOG/group_6.257.943101633                          YES        UNUSED          5120
     6       1 +REDOB/PREPROD/ONLINELOG/group_6.257.943101643                          YES        UNUSED          5120
     9       2 +REDOA/PREPROD/ONLINELOG/group_9.258.943101671                          YES        UNUSED          5120
     9       2 +REDOB/PREPROD/ONLINELOG/group_9.258.943101679                          YES        UNUSED          5120
    10       2 +REDOA/PREPROD/ONLINELOG/group_10.259.943101687                         YES        UNUSED          5120
    10       2 +REDOB/PREPROD/ONLINELOG/group_10.259.943101695                         YES        UNUSED          5120

Now we need to drop group 2, 4. But their status is CURRENT. So any attempt to drop these loggroups will result in below error.

ORA-01623: log 1 is current log for instance test (thread 1) – cannot drop

So to make them inactive, switch logfiles multiple time, till the status changes to INACTIVE.

alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

col member for a56 
set pagesize 299 
set lines 299 
select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) "Size (MB)" 
from  v$log l, v$logfile f  where f.group# = l.group# order by 1,2;

GROUP# THREAD# MEMBER                                                                 ARCHIVED   STATUS     Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- ---------- ---------
     2       1 +DATA/PREPROD/redo02.log                                                YES        INACTIVE          50  --->>>
     4       2 +DATA/PREPROD/redo04.log                                                YES        INACTIVE          50  --->>>
     5       1 +REDOA/PREPROD/ONLINELOG/group_5.256.943101611                          NO         CURRENT         5120
     5       1 +REDOB/PREPROD/ONLINELOG/group_5.256.943101623                          NO         CURRENT         5120
     6       1 +REDOA/PREPROD/ONLINELOG/group_6.257.943101633                          YES        INACTIVE        5120
     6       1 +REDOB/PREPROD/ONLINELOG/group_6.257.943101643                          YES        INACTIVE        5120
     9       2 +REDOA/PREPROD/ONLINELOG/group_9.258.943101671                          YES        ACTIVE          5120
     9       2 +REDOB/PREPROD/ONLINELOG/group_9.258.943101679                          YES        ACTIVE          5120
    10       2 +REDOA/PREPROD/ONLINELOG/group_10.259.943101687                         NO         CURRENT         5120
    10       2 +REDOB/PREPROD/ONLINELOG/group_10.259.943101695                         NO         CURRENT         5120

As now both are in the INACTIVE state, Let’s drop them.

alter database drop logfile group 2;
alter database drop logfile group 4;

col member for a56 
set pagesize 299 
set lines 299 
select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) "Size (MB)" 
from  v$log l, v$logfile f  where f.group# = l.group# order by 1,2;



GROUP# THREAD# MEMBER                                                                 ARCHIVED   STATUS     Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- ---------- ---------
     5       1 +REDOA/PREPROD/ONLINELOG/group_5.256.943101611                          NO         CURRENT         5120
     5       1 +REDOB/PREPROD/ONLINELOG/group_5.256.943101623                          NO         CURRENT         5120
     6       1 +REDOA/PREPROD/ONLINELOG/group_6.257.943101633                          YES        INACTIVE        5120
     6       1 +REDOB/PREPROD/ONLINELOG/group_6.257.943101643                          YES        INACTIVE        5120
     9       2 +REDOA/PREPROD/ONLINELOG/group_9.258.943101671                          YES        ACTIVE          5120
     9       2 +REDOB/PREPROD/ONLINELOG/group_9.258.943101679                          YES        ACTIVE          5120
    10       2 +REDOA/PREPROD/ONLINELOG/group_10.259.943101687                         NO         CURRENT         5120
    10       2 +REDOB/PREPROD/ONLINELOG/group_10.259.943101695                         NO         CURRENT         5120

Now all the old the redolog groups were dropped. With this multiplexing activity completed.

For resizing redolog groups , we can follow the same above steps.

For any queries, please post in our FORUM.

The post Multiplex redolog in Oracle RAC appeared first on DBACLASS.