-----------Resize Redo Log in Primary Database-------
alter database drop logfile group 2;
alter database add logfile group 2 '/u01/app/oracle/oradata/orcl/redo02.log' size 5m;
alter database drop logfile group 3;
alter database add logfile group 3 '/u01/app/oracle/oradata/orcl/redo03.log' size 5m;
alter system switch logfile
alter database drop logfile group 1;
--Error Come----
ORA-01624: log 1 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
select group#,status from v$log;
alter system checkpoint;
alter database drop logfile group 1;
alter database add logfile group 1 '/u01/app/oracle/oradata/orcl/redo01.log' size 5m;
alter database drop logfile group 4;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/orcl/stby04.log' size 5m;
alter database drop logfile group 5;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/orcl/stby05.log' size 5m;
alter database drop logfile group 6;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/orcl/stby06.log' size 5m;
-----------Resize Redo Log in Standby Database-------
Check Database Status
SQL> select status,instance_name,database_role from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN orcls PHYSICAL STANDBY
SQL>
Check if the parameter standby_file_management is set to AUTO or MANUAL on standby database. If it is not set to MANUAL, then set it.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string auto
SQL>
SQL> alter system set standby_file_management=manual;
System altered.
SQL>
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL>
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ----------
4 ACTIVE
5 UNASSIGNED
6 UNASSIGNED
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CLEARING
2 CLEARING
3 CURRENT
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
Now to solve this issue we have cancel the managed recovery session
and set "standby_file_management" to manual and drop the standby redolog file as
SQL> alter database recover managed standby database cancel ;
Database altered.
SQL>
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance orcls (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcls/redo01.log'
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CLEARING
2 CLEARING
3 CURRENT
If the status of standby redolog show the "clearing_current" then we cannot drop "clearing_current" status
logs,and for that we have to sync with Primary and clear the log first before dropping as
SQL> alter database clear logfile group 1;
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
2 CLEARING
3 CURRENT
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 '/u01/app/oracle/oradata/orcls/redo01.log' size 5m;
Database altered.
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 '/u01/app/oracle/oradata/orcls/redo02.log' size 5m;
SQL> alter database add logfile group 3 '/u01/app/oracle/oradata/orcls/redo03.log' size 5m;
---Now Remove Standby Redolog file---
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ----------
4 UNASSIGNED
5 ACTIVE
6 UNASSIGNED
SQL> alter database drop standby logfile group 4;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/orcls/stby04.log' size 5m;
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/orcls/stby05.log' size 5m;
SQL> alter database drop standby logfile group 6;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/orcls/stby06.log' size 5m;
----Now check the status of red log and standby redo log status----
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ----------
4 UNASSIGNED
5 UNASSIGNED
6 UNASSIGNED
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 UNUSED
-------Check the size of redo log and standby redo log file size
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
GROUP# size in MB
---------- ----------
6 5
4 5
5 5
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;
GROUP# size in MB
---------- ----------
1 5
2 5
3 5
Once all the Standby Redo Logs and Online Redo Logs have been resize on both Primary and standby database,
set the STANDBY_FILE_MANAGEMENT to AUTO on the standby database and start the recovery (MRP) on standby database.
SQL> alter system set standby_file_management=auto;
SQL> alter database recover managed standby database disconnect from session using current logfile;
Database altered.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
.
.
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 66
.
.
.
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 68
MRP0 APPLYING_LOG 68
41 rows selected.
SQL>
-----In Primary Database
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
67
-----In Standby Database
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
66
Cheers !!!!!!!!!!!!! If any confusion Please give me buzz !!!!!
