Current Status Of Redo Logs
Primary Side
SQL> select group#,sum(bytes/1024/1024)”Size in MB” from v$log group by group#;
GROUP# Size in MB
——- ———-
1 750
2 750
3 750
SQL> select group#,sum(bytes/1024/1024)”Size in MB” from v$standby_log group by group#;
GROUP# Size in MB
——- ———-
4 750
5 750
6 750
7 750
Standby Side
SQL> select group#, sum(bytes/1024/1024)”Size in MB” from v$loggroup by group#;
GROUP# Size in MB
—— ————-
1 750
2 750
3 750
SQL> select group#,sum(bytes/1024/1024)”Size in MB” from v$standby_log group by group#;
GROUP# Size in MB
—— ———-
4 750
5 750
6 750
7 750
Auto File Management Disable on Standby Side
SQL> alter system set standby_file_management=manual
System altered.
Note: You can drop log file when status is INACTIVE or UNUSED. Check status of redo log files.
SQL> select group#,status from v$log;
GROUP# STATUS
—— ——————-
1 INACTIVE
2 INACTIVE
3 CURRENT
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 1000M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
—— ——-
1 UNUSED
2 INACTIVE
3 CURRENT
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 1000M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
—— ——-
1 UNUSED
2 UNUSED
3 CURRENT
Note : You can use switch logfile command to change status of group 3 redo log files.
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
—— ——-
1 CURRENT
2 UNUSED
3 INACTIVE
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 1000M;
Database altered.
SQL> select group#,sum(bytes/1024/1024)”size in MB” from v$log group by group#;
GROUP# size in MB
—— ————-
1 1000
2 1000
3 1000
Standby Redo Log Resizing
Note : If you do not switchover, your standby log file status on primary side should be UNASSIGNED
SQL> select group#,sum(bytes/1024/1024)”size in MB” from v$standby_log group by group#;
GROUP# size in MB
—— ————-
6 750
4 750
5 750
7 750
SQL> select group#,status from v$standby_log;
GROUP# STATUS
—— ————
4 UNASSIGNED
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED
SQL> alter database drop standby logfile group 4;
Database altered.
SQL> alter database add standby logfile group 4 size 1000M;
Database altered.
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> alter database add standby logfile group 5 size 1000M;
Database altered.
SQL> alter database drop standby logfile group 6;
Database altered.
SQL> alter database add standby logfile group 6 size 1000M;
Database altered.
SQL> alter database drop standby logfile group 7;
Database altered.
SQL> alter database add standby logfile group 7 size 1000M;
Database altered.
SQL> select group#,sum(bytes/1024/1024)”size in MB” from v$standby_log group by group#;
GROUP# size in MB
—— ————-
4 1000
5 1000
6 1000
7 1000
Change Redo Log Size Of Standby Side
Note : Firstly, cancel applying recovery from primary.
SQL> alter database recover managed standby database cancel;
Database altered.
Note : Redo log status have to be UNUSED. You can use clear command and then you can drop redo log files.
SQL> select group#,status from v$log;
GROUP# STATUS
—— ——————-
1 CURRENT
2 CLEARING
3 CLEARING
Note : If you attempt to drop redo log file when status is not UNUSED, you can take below error.
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files.
Note : You have to clear redo log file before drop.
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 1000M;
Database altered.
SQL> alter database clear logfile group 3;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 1000M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
—— ——————-
1 CURRENT
2 UNUSED
3 UNUSED
Note : You can use switch logfile command on primary side to change log file status of standby database.
On primary side
SQL> alter system switch logfile;
System altered.
Note : You can clear and drop redo log file on standby site.
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 1000M;
Database altered.
SQL> select group#,sum(bytes/1024/1024)”size in MB” from v$log group by group#;
GROUP# size in MB
—— ————-
1 1000
2 1000
3 1000
Standby Redo Log Resizing
SQL> select group#,sum(bytes/1024/1024)”size in MB” from v$standby_log group by group#;
GROUP# size in MB
—— ————–
4 750
5 750
6 750
7 750
SQL> select group#,status from v$standby_log;
GROUP# STATUS
—— ————-
4 UNASSIGNED
5 UNASSIGNED
6 UNASSIGNED
7 ACTIVE
SQL> alter database drop standby logfile group 4;
Database altered.
SQL> alter database add standby logfile group 4 size 1000M;
Database altered.
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> alter database add standby logfile group 5 size 1000M;
Database altered.
SQL> alter database drop standby logfile group 6;
Database altered.
SQL> alter database add standby logfile group 6 size 1000M;
Database altered.
Note : You can use switch logfile command on primary side to change log file status of standby database.
On primary side
SQL> alter system switch logfile;
System altered.
Note : You can clear and drop redo log file on standby site.
SQL> alter database drop standby logfile group 7;
Database altered.
SQL> alter database add standby logfile group 7 size 1000M;
Database altered.
SQL> select group#,sum(bytes/1024/1024)”size in MB” from v$standby_log group by group#;
GROUP# size in MB
—— ————-
4 1000
5 1000
6 1000
7 1000
Auto File Management Enable on Standby Side
SQL> alter system set standby_file_management=auto
System altered.
Enable Apply Redo From Primary Side
SQL> alter system set standby_file_management=auto
System altered.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
Check if the dataguard is in sync
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;
