Primary Database Server: server1.soumya.com
Standby Database Server: server2.soumya.com
Primary Database: prim
Standby database: stand
Database version:11.2.0.1.0
Both the database prim and stand are in sync. For the “Real Time Apply” implementation, we need to make
sure that we have created the Standby Redo Logs on the standby database with the size same as that of the
Online Redo logs in primary database. The Standby Redo Logs also needs to be created on the primary
database, but this is not mandatory. The Standby Redo Logs would not be used on the Primary database until
there is a switchover operation performed and the primary database starts behaving as a standby database.
At Primary Database:
[oracle@server1 ~]$ sqlplus sys/sys@stand as sysdba
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN stand PHYSICAL STANDBY
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
GROUP# Size in MB
---------- ----------
1 50
2 50
3 50
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
GROUP# size in MB
---------- ----------
6 50
4 50
5 50
7 50
At Standby Database:
[oracle@server2 ~]$ sqlplus sys/sys@prim as sysdba
SQL> select status,instance_name,database_role from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN prim PRIMARY
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
GROUP# Size in MB
---------- ----------
1 100
2 100
3 100
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
GROUP# Size in MB
---------- ----------
6 50
4 50
5 50
7 50
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.
At standby database:-
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string auto
SQL> alter system set standby_file_management=manual;
System altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string manual
On the primary database(prim), check the status of the Online Redo Logs and resize them by dropping
the INACTIVE redo logs and re-creating them with the new size.
At primary database:-
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
Here you can see that Online Redo Log groups 1 and 2 are INACTIVE. Hence we can drop them and re-create
with the new size.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 100M;
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 100M;
Database altered.
SQL>
SQL> select group#,status from v$log;
GROUP# STATUS
------ -------
1 UNUSED
2 UNUSED
3 CURRENT
Now that Online Redo Log Groups 1 and 2 are resized and the status of Group 3 is CURRENT, switch logfiles
manually until Group 3 becomes INACTIVE.
SQL> select group#,status from v$log;
GROUP# STATUS
------ -------
1 ACTIVE
2 CURRENT
3 INACTIVE
Now that Group 3 is INACTIVE, we can drop it and re-create it with the new size.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 100M;
Database altered.
Now, we have resized all the Online Redo Logs on the Primary Database from 50M to 100M.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;
GROUP# size in MB
------ -------------
1 100
2 100
3 100
Moving on to the Standby Redo Logs on the Primary Database:
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
GROUP# size in MB
---------- ----------
6 50
4 50
5 50
7 50
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ----------
4 UNASSIGNED
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED
The status of the Standby Redo Logs (SRL) on the Primary database would be UNASSIGNED as they would be
used only when the primary database starts behaving as a Standby (Switchover)
We can easily drop the UNASSIGNED SRLs and re-create them with the new size.
At standby database:-
SQL> alter database drop standby logfile group 4;
Now while using the above query i faced an error
SQL> alter database drop standby logfile group 4;
alter database drop standby logfile group 4
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
To solve this issue we have to cancel the recovery on standby database
At standy database:-
SQL> alter database recover managed standby database cancel ;
Database altered.
SQL> alter database drop standby logfile group 4;
Database altered.
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> alter database drop standby logfile group 6;
Database altered.
SQL> alter database drop standby logfile group 7;
Database altered.
SQL> alter database add standby logfile group 4 size 100M;
Database altered.
SQL> alter database add standby logfile group 5 size 100M;
Database altered.
SQL> alter database add standby logfile group 6 size 100M;
Database altered.
SQL> alter database add standby logfile group 7 size 100M;
Database altered.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
GROUP# size in MB
---------- ----------
6 100
4 100
5 100
7 100
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.
At standby database:-
SQL> alter system set standby_file_management=auto;
System altered.
To sync both databases we will start recovery process at standby database:-
At standby database:-
SQL> select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
29 28-DEC-14 28-DEC-14 NO
30 28-DEC-14 28-DEC-14 NO
31 28-DEC-14 28-DEC-14 NO
32 28-DEC-14 28-DEC-14 NO
33 28-DEC-14 28-DEC-14 NO
34 28-DEC-14 28-DEC-14 NO
35 28-DEC-14 28-DEC-14 NO
36 28-DEC-14 28-DEC-14 NO
37 28-DEC-14 28-DEC-14 NO
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
29 28-DEC-14 28-DEC-14 YES
30 28-DEC-14 28-DEC-14 YES
31 28-DEC-14 28-DEC-14 YES
32 28-DEC-14 28-DEC-14 YES
33 28-DEC-14 28-DEC-14 YES
34 28-DEC-14 28-DEC-14 YES
35 28-DEC-14 28-DEC-14 YES
36 28-DEC-14 28-DEC-14 YES
37 28-DEC-14 28-DEC-14 YES
SQL> alter database recover managed standby database cancel;
Database altered.
QL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
At primary database:-
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
37
At standby database:-
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
37
