dg环境下修改redo大小

1）查看保护模式，一般默认最高性能
SQL> select protection_mode,protection_level from v$database;


PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE
如果是Maximum Protection mode需要先降级
      降级
 备库：
      alter database recover managed standby database cancel;
      主库：
      alter database set standby database to maximize PERFORMANCE;
 备库：
 alter database recover managed standby database using current logfile disconnect from session;
 
SQL> select status,instance_name,database_role from v$database,v$instance;


STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         jinan            PRIMARY
2）查询日志大小和数量
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#;


no rows selected
主机未定义standby 如果定义可按照备库方式


备库
SQL> select status,instance_name,database_role from v$database,v$Instance;


STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         qingdao          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
3）修改备库参数
查看standby file 参数是auto，修改为manual  
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> alter database recover managed standby database cancel;
Database altered.


4）修改主库online日志


SQL> select group#,status from v$log;


    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE
SQL> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------
/oradata/jinan/redo03.log
/oradata/jinan/redo02.log
/oradata/jinan/redo01.log


group 1、3是inactive 可以drop ;2 是current 需要切换后变为inactive 再drop
SQL> alter database drop logfile group 1;


Database altered.
#alter database add logfile group 1 size 60M;
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/oradata/jinan/redo001.log') SIZE 60M;


Database altered.






SQL> select group#,status from v$log;


    GROUP# STATUS
---------- ----------------
         1 UNUSED
         2 CURRENT
         3 INACTIVE
SQL> alter database drop logfile group 3;


Database altered.




SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/oradata/jinan/redo003.log') SIZE 60M;


Database altered.  


SQL> alter system switch logfile;


System altered.
SQL> select group#,status from v$log;


    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 ACTIVE
         3 UNUSED


SQL> alter system archive log current;


System altered.


SQL> select group#,status from v$log;


    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 CURRENT
         3 ACTIVE
SQL> alter system checkpoint;


System altered.


SQL> select group#,status from v$log;


      GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE
SQL> alter database drop logfile group 2;


Database altered.




SQL> ALTER DATABASE ADD LOGFILE GROUP 2 ('/oradata/jinan/redo002.log') SIZE 60M;


Database altered.
SQL> select group#, sum(bytes/1024/1024)"Size in MB" from v$log group by group#;


    GROUP# Size in MB
---------- ----------
         1         60
         2         60
         3         60
 
5）主库的standby redo(由于实验环境未配置，若配置也是直接drop 再add 可参照备库standby redo的方法)


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
以下是示例  mos上的示例
==================================================================
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
=====================================================================  
 
6）备库online redo
SQL> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------
/data/qingdao/redo03.log
/data/qingdao/redo01.log
/data/qingdao/stdby_redo4.log
/data/qingdao/stdby_redo5.log
/data/qingdao/stdby_redo6.log
/data/qingdao/stdby_redo7.log




SQL> select group#,status from v$log;


    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 CLEARING
         3 CLEARING
 
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




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
不管是current 还是clearing 直接drop都是报错的，需要取消应用
SQL> alter database recover managed standby database cancel;


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 ('/data/qingdao/redo002.log') SIZE 60M;


Database altered.


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 ('/data/qingdao/redo003.log') SIZE 60M;


Database altered.
主库切日志SQL> alter system switch logfile;


System altered.


SQL> select group#,status from v$log;


    GROUP# STATUS
---------- ----------------
         1 CLEARING
         2 CURRENT
         3 UNUSED
 
SQL> alter database clear logfile group 1;


Database altered.


SQL> alter database drop logfile group 1;


Database altered.


SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/data/qingdao/redo001.log') SIZE 60M;




Database altered.




SQL> SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;


    GROUP# size in MB
---------- ----------
         1         60
         2         60
         3         60
7）备库standby redo


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
都是unassigned，直接drop后add
SQL> select group#,status from v$standby_log;


    GROUP# STATUS
---------- ----------
         4 UNASSIGNED   
         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.


SQL> alter database drop standby logfile group 4;


Database altered


SQL> alter database add standby logfile group 4 ('/data/qingdao/stdby_redo04.log') size 60m;


Database altered.
SQL> alter database drop standby logfile group 5;


Database altered


SQL> alter database add standby logfile group 5 ('/data/qingdao/stdby_redo05.log') size 60m;


Database altered.


SQL> alter database drop standby logfile group 6;


Database altered


SQL> alter database add standby logfile group 6 ('/data/qingdao/stdby_redo06.log') size 60m;


Database altered.


SQL> alter database drop standby logfile group 7;


Database altered


SQL> alter database add standby logfile group 7 ('/data/qingdao/stdby_redo07.log') size 60m;


Database altered.




SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;


    GROUP# size in MB
---------- ----------
         6         60
         4         60
         5         60
         7         60
SQL> alter system set standby_file_management=auto;


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              24
ARCH      CLOSING              23
ARCH      CONNECTED             0
ARCH      CLOSING              29
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                 32
RFS       IDLE                  0
MRP0      WAIT_FOR_LOG         32


9 rows selected.


8）检查
主库
SQL>select max(sequence#) from v$archived_log;
备库
SQL>select max(sequence#) from v$archived_log where applied='YES';