Primary Database: DCPROC
Standby database: DRPROC
Primary Database Server: OPDB1
Standby Database Server: OPDB2
Database version: 11.2.0.2
I
have my physical standby database in sync with the primary database with the
logs being applied in the form of real time apply. As you might be aware, 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. 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. Here is a snippet of
the size of the redo logs (Online and Standby Redo logs) on the primary and
standby database
Primary Database:
[oracle@dev ~]$ sqlplus sys/oracle@DCPROC as sysdba
SQL*Plus:
Release 11.2.0.2.0 Production on Sat Sep 29 16:32:37 2012
Copyright
(c) 1982, 2010, Oracle. All rights reserved.
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With
the Partitioning, Automatic Storage Management, OLAP, DataMining
and
Real Application Testing options
SQL> select status,instance_name,database_role from
v$database,v$instance;
STATUS
INSTANCE_NAME DATABASE_ROLE
------
------------- --------------
OPEN DCPROC
PRIMARY
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
Standby Database:
[oracle@uat ~]$ sqlplus sys/oracle@DRPROC as sysdba
SQL*Plus:
Release 11.2.0.2.0 Production on Sat Sep 29 16:47:11 2012
Copyright
(c) 1982, 2010, Oracle. All rights reserved.
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With
the Partitioning, Automatic Storage Management, OLAP, DataMining
and
Real Application Testing options
SQL> select status,instance_name,database_role from
v$database,v$Instance;
STATUS INSTANCE_NAME
DATABASE_ROLE
-------
-------------- ----------------
MOUNTED
DRPROC PHYSICAL STANDBY
SQL> select group#, sum(bytes/1024/1024)"Size in MB" from
v$loggroup 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
------
----------
4 50
5 50
6 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.
SQL> show parameter standby_file_management
NAME TYPE VALUE
-----------------------
------- -------
standby_file_management
string AUTO
SQL>
SQL>
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, 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.
Primary:
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> 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
After
a couple of log switches, we can check the Status of Group 3
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.
SQL> alter database drop standby logfile group 4;
Database
altered.
SQL> alter database add standby logfile group 4 size 100M;
Database
altered.
Repeat
this for the remaining SRLs on the primary database and we can see below that
all the SRL on the Primary database have been resized.
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
Moving
on to the standby database:
Standby:
SQL> select group#,status from v$log;
GROUP#
STATUS
------
-------------------
1 CURRENT
3 CLEARING
2 CLEARING
Lets
try to drop Online Redo Log Group 2 as Group 1 is CURRENT status and it cannot
be
dropped.
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
Here
above, we faced ORA-01156 error, which is self-explainatory. The recovery (MRP
process) on the standby database is active and it needs to be cancelled before
we drop any Redo log groups.
SQL> alter database recover managed standby database cancel;
Database
altered.
Since
the status of Group 2 is still clearing, lets clear it manually before dropping
the group.
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 100M;
Database
altered.
SQL> select group#,status from v$log;
GROUP#
STATUS
------
-------------------
1 CURRENT
3 CLEARING
2 UNUSED
The
same is the case with Group 3 whose status is CLEARING. We’ll clear it
manually, drop it and create with the new size.
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 100M;
Database
altered.
To
drop online redo log group 1 that is in status CURRENT, generate an archive on
the primary database by switching logfile, and then clear the CURRENT online
redo log group on the standby database, drop it and re-create it with the
required size as done for groups 2 and 3 earlier.
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
Resizing
Standby Redo Logs on standby database:
SQL> select group#,sum(bytes/1024/1024)"size in MB" from
v$standby_log group by group#;
GROUP#
size in MB
------
--------------
4 50
5 50
6 50
7 50
SQL> select group#,status from v$standby_log;
GROUP#
STATUS
------
-------------
4 ACTIVE
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED
Clear
the SRL group whose status is ACTIVE as done earlier, drop the group and
re-create them with the size same as that of the Online Redo Logs.
For
the SRL groups whose status is UNASSIGNED, just drop the group and recreate
them with the size same as that of the Online Redo Logs.
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
------
-------------
4 100
5 100
6 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.
SQL> alter system set standby_file_management=auto;
System
altered.
SQL> alter database recover managed standby database disconnectfrom
session using current logfile;
Database
altered.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS
STATUS SEQUENCE#
-------
-------------- -------------
ARCH CONNECTED 0
ARCH CLOSING 66
ARCH CONNECTED 0
ARCH CLOSING 63
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 71
RFS IDLE 71
RFS IDLE 0
9
rows selected.
Primary:
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
70
Standby:
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
70
