已索引
需求背景
目前主库 online redo 日志为 512M x 5 x 2。standby redo 日志为 512M x 6 x 2。备库为单节点RAC，online redo 和 standby redo 与主库一致。
现计划将主库 online redo 日志修改为 2G x 7 x 2。standby redo 日志修改为 2G x 8 x 2。备库为单节点RAC，与主库一致。
注意事项
Redo Transport Services
The synchronous and asynchronous redo transport modes require that a redo transport destination have a standby redo log. A standby redo log is used to store redo received from another Oracle database. Standby redo logs are structurally identical to redo logs, and are created and managed using the same SQL statements used to create and manage redo logs.
Redo received from another Oracle database via redo transport is written to the current standby redo log group by an RFS foreground process. When a log switch occurs on the redo source database, incoming redo is then written to the next standby redo log group, and the previously used standby redo log group is archived by an ARCn foreground process.
The standby redo log must have at least one more redo log group than the redo log at the redo source database, for each redo thread at the redo source database.
Whenever a redo log group is added to a primary database, a log group must also be added to the standby redo log of each standby database in the configuration. Otherwise, the standby database may become unsynchronized after a primary log switch, which could temporarily prevent a zero data loss failover or cause a primary database operating in maximum protection mode to shut down.
Please note if using Maximum Protection mode, you need to downgrade to Maximum availability mode first and then perform the below steps. Starting with 11gR2 this no longer needs a Primary database restart to downgrade to Max Availability mode and then upgrade to Max Protection mode.
注意：本次操作，ogg同步进程崩溃。
查看主库和备库的模式
SQL> select status,instance_name,database_role,protection_mode from v$instance,v$database;
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN lttsdb1 PRIMARY MAXIMUM AVAILABILITY
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN lttsdbstd PHYSICAL STANDBY MAXIMUM AVAILABILITY
将备库的standby_file_management设置为手动管理
SQL> show parameter standby_file_management;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> alter system set standby_file_management=manual sid='*';
SQL> show parameter standby_file_management;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL重建主库 standby 日志
查看主库Standby日志组情况
set pages 800;
select GROUP#,THREAD#,BYTES/1024/1024,STATUS from v$standby_log;
GROUP# THREAD# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------
11 1 512 ACTIVE
12 1 512 UNASSIGNED
13 1 512 UNASSIGNED
14 1 512 UNASSIGNED
15 1 512 UNASSIGNED
16 1 512 UNASSIGNED
17 2 512 ACTIVE
18 2 512 UNASSIGNED
19 2 512 UNASSIGNED
20 2 512 UNASSIGNED
21 2 512 UNASSIGNED
22 2 512 UNASSIGNED
12 rows selected.
重建主库的standby日志
不论 standby 日志组状态如何，如下命令都可以执行成功，直接执行即可。
alter database drop standby logfile group 11;
alter database add standby logfile thread 1 group 41 ('+MDATA','+MARCH') size 2g;
……
alter database drop standby logfile group 16;
alter database add standby logfile thread 1 group 46 ('+MDATA','+MARCH') size 2g;
alter database add standby logfile thread 1 group 47 ('+MDATA','+MARCH') size 2g;
alter database add standby logfile thread 1 group 48 ('+MDATA','+MARCH') size 2g;
alter database drop standby logfile group 17;
alter database add standby logfile thread 2 group 51 ('+MDATA','+MARCH') size 2g;
……
alter database drop standby logfile group 22;
alter database add standby logfile thread 2 group 56 ('+MDATA','+MARCH') size 2g;
alter database add standby logfile thread 2 group 57 ('+MDATA','+MARCH') size 2g;
alter database add standby logfile thread 2 group 58 ('+MDATA','+MARCH') size 2g;确认主库Standby日志组情况
set pages 800;
select GROUP#,THREAD#,BYTES/1024/1024,STATUS from v$standby_log;重建主库 online 日志
查看主库Online日志组情况
set pages 800;
select GROUP#,THREAD#,BYTES/1024/1024,STATUS from v$log;
GROUP# THREAD# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
1 1 512 INACTIVE
2 1 512 INACTIVE
3 1 512 ACTIVE
4 1 512 ACTIVE
5 1 512 CURRENT
6 2 512 INACTIVE
7 2 512 ACTIVE
8 2 512 ACTIVE
9 2 512 CURRENT
10 2 512 INACTIVE
10 rows selected.重建online日志
操作时，先添加没有的日志组(thread2 11~17)。
添加成功后，就可以删除 thread2 原来的状态为 INACTIVE 的日志组。
删除后，就可以将这些删除的日志组号重建为 thread1 的日志组。
thread1的日志组添加成功后，就可以删除thread1 原来的状态为 INACTIVE 的日志组。
删除后，就可以将这些删除的日志组号重建为 thread1 新的日志组。
对于 CURRENT 状态的日志组，可以做日志切换，对于 ACTIVE 状态的日志组，可以做检查点。
alter system switch logfile;
alter system checkpoint;
alter database add logfile thread 1 group 1 ('+MDATA','+MARCH') size 2g;
alter database add logfile thread 1 group 2 ('+MDATA','+MARCH') size 2g;
alter database add logfile thread 1 group 3 ('+MDATA','+MARCH') size 2g;
alter database add logfile thread 1 group 4 ('+MDATA','+MARCH') size 2g;
alter database add logfile thread 1 group 5 ('+MDATA','+MARCH') size 2g;
alter database add logfile thread 1 group 6 ('+MDATA','+MARCH') size 2g;
alter database add logfile thread 1 group 7 ('+MDATA','+MARCH') size 2g;
alter database add logfile thread 2 group 11 ('+MDATA','+MARCH') size 2g;
alter database add logfile thread 2 group 12 ('+MDATA','+MARCH') size 2g;
alter database add logfile thread 2 group 13 ('+MDATA','+MARCH') size 2g;
alter database add logfile thread 2 group 14 ('+MDATA','+MARCH') size 2g;
alter database add logfile thread 2 group 15 ('+MDATA','+MARCH') size 2g;
alter database add logfile thread 2 group 16 ('+MDATA','+MARCH') size 2g;
alter database add logfile thread 2 group 17 ('+MDATA','+MARCH') size 2g;
alter database drop logfile group 1 ;
alter database drop logfile group 2 ;
alter database drop logfile group 3 ;
alter database drop logfile group 4 ;
alter database drop logfile group 5 ;
alter database drop logfile group 6 ;
alter database drop logfile group 7 ;
alter database drop logfile group 8 ;
alter database drop logfile group 9 ;
alter database drop logfile group 10 ;确认主库 online 日志组情况：
select GROUP#,THREAD#,BYTES/1024/1024,STATUS from v$log;检查当前日志应用
在备库上执行：
select t.* from v$dataguard_stats t;
在主库上执行：
select nvl(max(la.lastarchived-appl.lastapplied),0)
from (select gvi.thread#, gvd.dest_id, MAX(gvd.log_sequence) currentsequence FROM gv$archive_dest gvd, gv$instance gvi WHERE gvd.status = 'VALID' AND gvi.inst_id = gvd.inst_id GROUP BY thread#, dest_id) cu,
(select thread#, dest_id, MAX(sequence#) lastarchived FROM gv$archived_log WHERE resetlogs_change# = (SELECT resetlogs_change# FROM v$database) AND archived = 'YES' GROUP BY thread#, dest_id) la,
(select thread#, dest_id, MAX(sequence#) lastapplied FROM gv$archived_log WHERE resetlogs_change# = (SELECT resetlogs_change# FROM v$database) AND applied = 'YES' GROUP BY thread#, dest_id) appl
where cu.thread# = la.thread# AND cu.thread# = appl.thread# AND cu.dest_id = la.dest_id AND cu.dest_id = appl.dest_id and cu.dest_id in(2,3);重建standby日志
查看当前日志情况
set pages 800;
select GROUP#,THREAD#,BYTES/1024/1024,STATUS from v$standby_log;
GROUP# THREAD# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------
11 1 512 ACTIVE
12 1 512 UNASSIGNED
13 1 512 UNASSIGNED
14 1 512 UNASSIGNED
15 1 512 UNASSIGNED
16 1 512 UNASSIGNED
17 2 512 ACTIVE
18 2 512 UNASSIGNED
19 2 512 UNASSIGNED
20 2 512 UNASSIGNED
21 2 512 UNASSIGNED
22 2 512 UNASSIGNED
12 rows selected.停止日志应用
如果使用了 broker：
dgmgrl sys/****
show configuration;
EDIT DATABASE lttsstd SET STATE = APPLY-OFF;如果没有用 broker：
alter database recover managed standby database cancel;重建日志
不论 standby 日志组状态如何，如下命令都可以执行成功，直接执行即可。
alter database drop standby logfile group 11;
alter database add standby logfile thread 1 group 41 ('+MDATA','+MARCH') size 2g;
……
alter database drop standby logfile group 16;
alter database add standby logfile thread 1 group 46 ('+MDATA','+MARCH') size 2g;
alter database add standby logfile thread 1 group 47 ('+MDATA','+MARCH') size 2g;
alter database add standby logfile thread 1 group 48 ('+MDATA','+MARCH') size 2g;
alter database drop standby logfile group 17;
alter database add standby logfile thread 2 group 51 ('+MDATA','+MARCH') size 2g;
……
alter database drop standby logfile group 22;
alter database add standby logfile thread 2 group 56 ('+MDATA','+MARCH') size 2g;
alter database add standby logfile thread 2 group 57 ('+MDATA','+MARCH') size 2g;
alter database add standby logfile thread 2 group 58 ('+MDATA','+MARCH') size 2g;确认备库Standby日志组情况
set pages 800;
select GROUP#,THREAD#,BYTES/1024/1024,STATUS from v$standby_log;重建online日志
查看当前日志情况
SQL> select GROUP#,THREAD#,BYTES/1024/1024,STATUS from v$log;
GROUP# THREAD# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
1 1 512 CLEARING
2 1 512 CLEARING
3 1 512 CLEARING
4 1 512 CLEARING
5 1 512 CURRENT
6 2 512 CURRENT
7 2 512 CLEARING
8 2 512 CLEARING
9 2 512 CLEARING
10 2 512 CLEARING
10 rows selected.命令可以直接跑，日志组为CURRENT会报错，主库做日志切换后再处理。
alter database clear logfile group 1;
alter database drop logfile group 1 ;
alter database add logfile thread 1 group 1 ('+SDATA','+SARCH') size 2g;
alter database clear logfile group 2 ;
alter database drop logfile group 2 ;
alter database add logfile thread 1 group 2 ('+SDATA','+SARCH') size 2g;
alter database clear logfile group 3 ;
alter database drop logfile group 3 ;
alter database add logfile thread 1 group 3 ('+SDATA','+SARCH') size 2g;
alter database clear logfile group 4 ;
alter database drop logfile group 4 ;
alter database add logfile thread 1 group 4 ('+SDATA','+SARCH') size 2g;
alter database clear logfile group 5 ;
alter database drop logfile group 5 ;
alter database add logfile thread 1 group 5 ('+SDATA','+SARCH') size 2g;
alter database clear logfile group 6 ;
alter database drop logfile group 6 ;
alter database add logfile thread 1 group 6 ('+SDATA','+SARCH') size 2g;
alter database clear logfile group 7 ;
alter database drop logfile group 7 ;
alter database add logfile thread 1 group 7 ('+SDATA','+SARCH') size 2g;
alter database clear logfile group 8 ;
alter database drop logfile group 8 ;
alter database clear logfile group 9 ;
alter database drop logfile group 9 ;
alter database clear logfile group 10;
alter database drop logfile group 10 ;
alter database add logfile thread 2 group 11 ('+SDATA','+SARCH') size 2g;
alter database add logfile thread 2 group 12 ('+SDATA','+SARCH') size 2g;
alter database add logfile thread 2 group 13 ('+SDATA','+SARCH') size 2g;
alter database add logfile thread 2 group 14 ('+SDATA','+SARCH') size 2g;
alter database add logfile thread 2 group 15 ('+SDATA','+SARCH') size 2g;
alter database add logfile thread 2 group 16 ('+SDATA','+SARCH') size 2g;
alter database add logfile thread 2 group 17 ('+SDATA','+SARCH') size 2g;修改管理方式为 auto
SQL> alter system set standby_file_management=auto sid='*';
SQL> show parameter standby_file_management;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL重启日志应用
如果使用了 broker：
dgmgrl sys/****
show configuration;
EDIT DATABASE lttsstd SET STATE = APPLY-OFF;如果没有用 broker：
SQL> alter database recover managed standby database disconnect from session using current logfile;