How to resize Redo Logs in Dataguard Envrionment in oracle 11g


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
   

Nenhum comentário:

Postar um comentário

Related Posts Plugin for WordPress, Blogger...