ITPub博客

首页 > 数据库 > Oracle > Resize redo/standby log on Oracle 11.2.0.3 Active Dataguard

Resize redo/standby log on Oracle 11.2.0.3 Active Dataguard

原创 Oracle 作者:cyr1974 时间:2014-03-21 14:57:49 0 删除 编辑

Refer to Doc in Metalink and test in on Prod.

I have one 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 and have one more standby redo log group than redo group for better performance.  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 complete steps snippet of the resize of the redo logs (Online and Standby Redo logs) on the primary and standby database.

On Primary:
/oradata/home/oracle >$sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 20 02:30:30 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--Check if primary and standby are in sync or out of sync
SQL> set linesize 200
SQL> col dest_name for a30
SQL> select DEST_NAME,STATUS,PROTECTION_MODE,ARCHIVED_SEQ#,APPLIED_SEQ# from v$archive_dest_status;
DEST_NAME                      STATUS    PROTECTION_MODE      ARCHIVED_SEQ# APPLIED_SEQ#
------------------------------ --------- -------------------- ------------- ------------
LOG_ARCHIVE_DEST_1             INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_2             VALID     MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_3             VALID     MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_4             VALID     MAXIMUM AVAILABILITY          3474         3473
LOG_ARCHIVE_DEST_5             INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_6             INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_7             INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_8             INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_9             INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_10            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_11            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_12            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_13            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_14            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_15            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_16            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_17            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_18            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_19            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_20            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_21            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_22            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_23            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_24            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_25            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_26            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_27            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_28            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_29            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_30            INACTIVE  MAXIMUM PERFORMANCE           3474            0
LOG_ARCHIVE_DEST_31            INACTIVE  MAXIMUM PERFORMANCE           3474            0
31 rows selected.
--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.


On Standby server:
/oradata/home/oracle >$sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 20 02:29:24 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter standby;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO
SQL> alter system set standby_file_management=manual;
System altered.

SQL> show parameter standby;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL
On Primary :
--Below 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, and check the log file path as well.
SQL> select group#,thread#,status from v$log;
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 INACTIVE
         2          1 INACTIVE
         3          1 CURRENT
SQL> col member for a60
SQL> set linesize 200
SQL> select member,group#,status from v$logfile;
MEMBER                                                           GROUP# STATUS
------------------------------------------------------------ ---------- -------
/oradata/oraredo/isuite/dsk01/redo1_01_01.rdo                         1
/oradata/oraredo/isuite/dsk02/redo1_01_02.rdo                         1
/oradata/oraredo/isuite/dsk01/redo1_02_01.rdo                         2
/oradata/oraredo/isuite/dsk02/redo1_02_02.rdo                         2
/oradata/oraredo/isuite/dsk01/redo1_03_01.rdo                         3
/oradata/oraredo/isuite/dsk02/redo1_03_02.rdo                         3
/oradata/oraredo/isuite/dsk01/stdbylog01_04_01.rdo                    4
/oradata/oraredo/isuite/dsk02/stdbylog01_04_02.rdo                    4
/oradata/oraredo/isuite/dsk01/stdbylog01_05_01.rdo                    5
/oradata/oraredo/isuite/dsk02/stdbylog01_05_02.rdo                    5
/oradata/oraredo/isuite/dsk01/stdbylog01_06_01.rdo                    6
/oradata/oraredo/isuite/dsk02/stdbylog01_06_02.rdo                    6
/oradata/oraredo/isuite/dsk01/stdbylog01_07_01.rdo                    7
/oradata/oraredo/isuite/dsk02/stdbylog01_07_02.rdo                    7
14 rows selected.
SQL>  select group#,thread#,status from v$log;
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 INACTIVE
         2          1 INACTIVE
         3          1 CURRENT

SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile thread 1 group 1 ('/oradata/oraredo/isuite/dsk01/redo1_1.log','/oradata/oraredo/isuite/dsk02/redo1_2.log') size 200M;
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 thread 1 group 2 ('/oradata/oraredo/isuite/dsk01/redo2_1.log','/oradata/oraredo/isuite/dsk02/redo2_2.log') size 200M;
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> alter system switch logfile;
System altered.
SQL> select group#,thread#,status from v$log;
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 UNUSED
         3          1 ACTIVE
SQL> select group#,thread#,status from v$log;
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 UNUSED
         3          1 ACTIVE
SQL> alter system switch logfile;
System altered.
SQL> select group#,thread#,status from v$log;
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 CURRENT
         3          1 ACTIVE
SQL> alter system switch logfile;
System altered.
SQL> select group#,thread#,status from v$log;
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 ACTIVE
         3          1 CURRENT
SQL>  select group#,thread#,status from v$log;
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 ACTIVE
         3          1 CURRENT
SQL> select group#,thread#,status from v$log;
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 ACTIVE
         3          1 CURRENT
SQL> /
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 ACTIVE
         3          1 CURRENT
SQL> /
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 ACTIVE
         3          1 CURRENT
SQL> /
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 ACTIVE
         3          1 CURRENT
SQL> /
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 ACTIVE
         3          1 CURRENT
SQL> /
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 ACTIVE
         3          1 CURRENT
SQL> /
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 ACTIVE
         3          1 CURRENT
SQL> /
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 ACTIVE
         3          1 CURRENT
SQL> /
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 ACTIVE
         3          1 CURRENT
SQL> /
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 ACTIVE
         3          1 CURRENT
SQL> /
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 INACTIVE
         3          1 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 thread 1 group 3 ('/oradata/oraredo/isuite/dsk01/redo3_1.log','/oradata/oraredo/isuite/dsk02/redo3_2.log') size 200M;
Database altered.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;
    GROUP# size in MB
---------- ----------
         1        200
         2        200
         3        200
--Now, we have resized all the Online Redo Logs on the Primary Database from 50M to 200M.
--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 drop standby logfile group 5;
Database altered.
SQL> alter database add standby logfile thread 1 group 4('/oradata/oraredo/isuite/dsk01/standby4_1.log','/oradata/oraredo/isuite/dsk02/standby4_2.log') size 200M;
Database altered.
SQL> alter database add standby logfile thread 1 group 5('/oradata/oraredo/isuite/dsk01/standby5_1.log','/oradata/oraredo/isuite/dsk02/standby5_2.log') size 200M;
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 thread 1 group 6('/oradata/oraredo/isuite/dsk01/standby6_1.log','/oradata/oraredo/isuite/dsk02/standby6_2.log') size 200M;
alter database add standby logfile thread 1 group 7('/oradata/oraredo/isuite/dsk01/standby7_1.log','/oradata/oraredo/isuite/dsk02/standby7_2.log') size 200M;
Database altered.
SQL>
Database altered.
SQL>
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
    GROUP# size in MB
---------- ----------
         6        200
         4        200
         5        200
         7        200

--so far all standby log were resized.
Move on physical standby server:
Last login: Wed Mar 19 03:02:43 2014 from 10.35.24.211
#########################################################################
#       Please check that the time the system shows you last            #
#       logged in is correct.                                           #
#                                                                      #
#       If it is not please change your password and contact the        #
#       system administrator immediately                                #
#########################################################################
root@nycs-oradb01 # su - oracle
#########################################################################
#       Please check that the time the system shows you last            #
#       logged in is correct.                                           #
#                                                                       #
#       If it is not please change your password and contact the        #
#       system administrator immediately                                #
#########################################################################
SQL> recover managed standby database disconnect from session;
ORA-01153: an incompatible media recovery is active
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$log;
    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 CLEARING
         3 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-explanatory. 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 thread 1 group 2 ('/oradata/oraredo/isuite/dsk01/redo2_1.log','/oradata/oraredo/isuite/dsk02/redo2_2.log') size 200M;
Database altered.
SQL> select group#,status from v$log;
    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 UNUSED
         3 CLEARING
--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 thread 1 group 3 ('/oradata/oraredo/isuite/dsk01/redo3_1.log','/oradata/oraredo/isuite/dsk02/redo3_2.log') size 200M;
Database altered.
SQL> select group#,status from v$log;
    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 UNUSED
         3 UNUSED
-- 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.
Go to primary database:
SQL>  alter system switch logfile;
System altered.
On standby server:
SQL> select group#,status from v$log;
    GROUP# STATUS
---------- ----------------
         1 CLEARING
         2 CURRENT
         3 CLEARING
SQL>  alter database clear logfile group 1;
Database altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile thread 1 group 1 ('/oradata/oraredo/isuite/dsk01/redo1_1.log','/oradata/oraredo/isuite/dsk02/redo1_2.log') size 200M;
Database altered.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;
    GROUP# size in MB
---------- ----------
         1        200
         2        200
         3        200
SQL>  select group#,status from v$log;
    GROUP# STATUS
---------- ----------------
         1 UNUSED
         2 CURRENT
         3 CLEARING
--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
---------- ----------
         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
-- Clear the SRL group If 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. In our case none of standby log ‘s status is ACTIVE
--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> 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 thread 1 group 4('/oradata/oraredo/isuite/dsk01/standby4_1.log','/oradata/oraredo/isuite/dsk02/standby4_2.log') size 200M;
alter database add standby logfile thread 1 group 5('/oradata/oraredo/isuite/dsk01/standby5_1.log','/oradata/oraredo/isuite/dsk02/standby5_2.log') size 200M;
alter database add standby logfile thread 1 group 6('/oradata/oraredo/isuite/dsk01/standby6_1.log','/oradata/oraredo/isuite/dsk02/standby6_2.log') size 200M;
alter database add standby logfile thread 1 group 7('/oradata/oraredo/isuite/dsk01/standby7_1.log','/oradata/oraredo/isuite/dsk02/standby7_2.log') size 200M;
Database altered.
SQL>
Database altered.
SQL>

Database altered.
SQL>
Database altered.
SQL>
SQL>  select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
    GROUP# size in MB
---------- ----------
         6        200
         4        200
         5        200
         7        200
-- 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 system set standby_file_management=auto scope=both;
System altered.
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      CLOSING            3475
ARCH      CLOSING            3473
ARCH      CONNECTED             0
ARCH      CLOSING            3472
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
MRP0      WAIT_FOR_LOG       3480
8 rows selected.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING            3475
ARCH      CLOSING            3483
ARCH      CONNECTED             0
ARCH      CLOSING            3482
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
MRP0      APPLYING_LOG       3484
RFS       IDLE               3484
9 rows selected.
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
          3482
On primary:
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
          3483

On standby:
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
          3482
Hereto all steps has been completed
Note:
To clear the ACTIVE SRL, you can either clear it on the standby database by using:
SQL>alter database clear logfile group ;
drop it and recreate with a new size or you can do log switches on the primary database and check if the ACTIVE standby redo log group has turned out to be UNASSIGNED, later drop it and recreate with the new size. I prefer the second way (log switches on primary, dropping UNASSIGNED SRL on standby, recreating it with new size) of going for the ACTIVE SRL rather than clearing it, dropping and recreating.
You can refer to log clear for detailed explanation.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/36779/viewspace-1126732/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-03-16

  • 博文量
    37
  • 访问量
    256876