做为一个IT人，虽然经历了很多，但当时没记录故事，所以最后写文章就开始瞎编乱造了。今天就讲一个使用场景吧，A公司因为业务发展需求从机械盘换成了闪存卡，因为够大，所以想把数据库的整个挪到闪存卡上。。。
算了算了编不下去了，占时两个用法
1、redo挪位置
2、调整redo大小
3、调整redo每组个数
注意点：按照文档操作你需要更换路径，和大小根据实际情况调整。路径就不解释了，大小是根据业务量来的，如果业务量大redo小了会造成频繁切换归档造成不必要的资源消耗，如果太大了也会对性能、管理复杂度、恢复时间有影响。
主库删除redo步骤
1、确认不是CURRENT状态（如果是就在主库切换归档）
2、删除redo
3、重建redo
主库重建redo
select * from v$log;
select * from v$logfile;
alter database add logfile group 1 ('/u01/app/oracle/oradata/branch/redo01a.log', '/u01/app/oracle/oradata/branch/redo01b.log') size 500m reuse;
alter database add logfile group 2 ('/u01/app/oracle/oradata/branch/redo02a.log', '/u01/app/oracle/oradata/branch/redo02b.log') size 500m reuse;
alter database add logfile group 3 ('/u01/app/oracle/oradata/branch/redo03a.log', '/u01/app/oracle/oradata/branch/redo03b.log') size 500m reuse;
select * from v$log;
alter system switch logfile;
。。。。切换到下列查询状态，让4-7状态为inactive状态。。。。
select * from v$log;
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 add logfile group 4 ('/u01/app/oracle/oradata/branch/redo04a.log', '/u01/app/oracle/oradata/branch/redo04b.log') size 500m reuse;
主库重建redo操作日志记录
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
4 1 850222 524288000 512 1 NO CURRENT 1731592665009 2022/1/5 17 281474976710
5 1 850219 524288000 512 1 YES INACTIVE 1731592654557 2022/1/5 17 173159265807 2022/1/5 17
6 1 850220 524288000 512 1 YES INACTIVE 1731592658073 2022/1/5 17 173159266055 2022/1/5 17
7 1 850221 524288000 512 1 YES ACTIVE 1731592660556 2022/1/5 17 173159266500 2022/1/5 17
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
4 ONLINE /data/oradata/branch/redo4.log NO
5 ONLINE /data/oradata/branch/redo5.LOG NO
6 ONLINE /data/oradata/branch/redo6.LOG NO
7 ONLINE /data/oradata/branch/redo7.LOG NO
10 STANDBY /data/oradata/branch/STAN10.LOG NO
11 STANDBY /data/oradata/branch/STAN11.LOG NO
12 STANDBY /data/oradata/branch/STAN12.LOG NO
13 STANDBY /data/oradata/branch/STAN13.LOG NO
8 rows selected
SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/branch/redo01a.log', '/u01/app/oracle/oradata/branch/redo01b.log') size 500m reuse;
Database altered
SQL> alter database add logfile group 2 ('/u01/app/oracle/oradata/branch/redo02a.log', '/u01/app/oracle/oradata/branch/redo02b.log') size 500m reuse;
Database altered
SQL> alter database add logfile group 3 ('/u01/app/oracle/oradata/branch/redo03a.log', '/u01/app/oracle/oradata/branch/redo03b.log') size 500m reuse;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 0 524288000 512 2 YES UNUSED 0 0
2 1 0 524288000 512 2 YES UNUSED 0 0
3 1 0 524288000 512 2 YES UNUSED 0 0
4 1 850222 524288000 512 1 YES ACTIVE 1731592665009 2022/1/5 17 173159278561 2022/1/5 17
5 1 850223 524288000 512 1 NO CURRENT 1731592785612 2022/1/5 17 281474976710
6 1 850220 524288000 512 1 YES INACTIVE 1731592658073 2022/1/5 17 173159266055 2022/1/5 17
7 1 850221 524288000 512 1 YES INACTIVE 1731592660556 2022/1/5 17 173159266500 2022/1/5 17
7 rows selected
SQL> alter system switch logfile;
。。。。切换到下列查询状态，让4-7状态为inactive状态。。。。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 850245 524288000 512 2 YES INACTIVE 1731593072996 2022/1/5 18 173159307637 2022/1/5 18
2 1 850246 524288000 512 2 YES ACTIVE 1731593076370 2022/1/5 18 173159330135 2022/1/5 18
3 1 850247 524288000 512 2 NO CURRENT 1731593301355 2022/1/5 18 281474976710
4 1 850243 524288000 512 1 YES INACTIVE 1731593035979 2022/1/5 18 173159306774 2022/1/5 18
5 1 850244 524288000 512 1 YES INACTIVE 1731593067745 2022/1/5 18 173159307299 2022/1/5 18
6 1 850241 524288000 512 1 YES INACTIVE 1731593029082 2022/1/5 18 173159303460 2022/1/5 18
7 1 850242 524288000 512 1 YES INACTIVE 1731593034601 2022/1/5 18 173159303597 2022/1/5 18
7 rows selected
SQL> alter database drop logfile group 4;
Database altered
SQL> alter database drop logfile group 5;
Database altered
SQL> alter database drop logfile group 6;
Database altered
SQL> alter database drop logfile group 7;
Database altered
SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/branch/redo04a.log', '/u01/app/oracle/oradata/branch/redo04b.log') size 500m reuse;
Database altered
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- -------- ---------- ------------- ----------- ------------ ----------- ------------ -----------
10 UNASSIGNED 0 0 524288000 512 0 YES UNASSIGNED
11 UNASSIGNED 0 0 524288000 512 0 YES UNASSIGNED
12 UNASSIGNED 0 0 524288000 512 0 YES UNASSIGNED
13 UNASSIGNED 0 0 524288000 512 0 YES UNASSIGNED
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 850245 524288000 512 2 YES INACTIVE 1731593072996 2022/1/5 18 173159307637 2022/1/5 18
2 1 850246 524288000 512 2 YES ACTIVE 1731593076370 2022/1/5 18 173159330135 2022/1/5 18
3 1 850247 524288000 512 2 NO CURRENT 1731593301355 2022/1/5 18 281474976710
4 1 0 524288000 512 2 YES UNUSED 0 0
SQL>
后面这段一般不一定用的上，是有DG环境才会有的，而且主要是备库在一定模式下才会用的上，所以主库可以随意删除重建，后期我也会出一篇备库的调整文档吧。
主库重建 standby redo
select * from v$standby_log;
select * from v$log;
select * from v$standby_log;
alter database drop logfile group 10;
alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
select * from v$standby_log;
alter database add standby logfile group 10 ('/u01/app/oracle/oradata/branch/STAN010A.LOG','/u01/app/oracle/oradata/branch/STAN010B.LOG') size 500m;
alter database add standby logfile group 11 ('/u01/app/oracle/oradata/branch/STAN011A.LOG','/u01/app/oracle/oradata/branch/STAN011B.LOG') size 500m;
alter database add standby logfile group 12 ('/u01/app/oracle/oradata/branch/STAN012A.LOG','/u01/app/oracle/oradata/branch/STAN012B.LOG') size 500m;
alter database add standby logfile group 13 ('/u01/app/oracle/oradata/branch/STAN013A.LOG','/u01/app/oracle/oradata/branch/STAN013B.LOG') size 500m;
select * from v$standby_log;
select * from v$logfile order by GROUP#,MEMBER;
主库重建 standby redo操作日志记录
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- -------- ---------- ------------- ----------- ------------ ----------- ------------ -----------
10 UNASSIGNED 1 0 524288000 512 0 YES UNASSIGNED
11 UNASSIGNED 1 0 524288000 512 0 YES UNASSIGNED
12 UNASSIGNED 1 0 524288000 512 0 YES UNASSIGNED
13 UNASSIGNED 0 0 524288000 512 0 YES UNASSIGNED
SQL> alter database drop logfile group 10;
Database altered
SQL> alter database drop logfile group 11;
Database altered
SQL> alter database drop logfile group 12;
Database altered
SQL> alter database drop logfile group 13;
Database altered
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- -------- ---------- ------------- ----------- ------------ ----------- ------------ -----------
SQL> alter database add standby logfile group 10 ('/u01/app/oracle/oradata/branch/STAN010A.LOG','/u01/app/oracle/oradata/branch/STAN010B.LOG') size 500m;
Database altered
SQL> alter database add standby logfile group 11 ('/u01/app/oracle/oradata/branch/STAN011A.LOG','/u01/app/oracle/oradata/branch/STAN011B.LOG') size 500m;
Database altered
SQL> alter database add standby logfile group 12 ('/u01/app/oracle/oradata/branch/STAN012A.LOG','/u01/app/oracle/oradata/branch/STAN012B.LOG') size 500m;
Database altered
SQL> alter database add standby logfile group 13 ('/u01/app/oracle/oradata/branch/STAN013A.LOG','/u01/app/oracle/oradata/branch/STAN013B.LOG') size 500m;
Database altered
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- -------- ---------- ------------- ----------- ------------ ----------- ------------ -----------
10 UNASSIGNED 0 0 524288000 512 0 YES UNASSIGNED
11 UNASSIGNED 0 0 524288000 512 0 YES UNASSIGNED
12 UNASSIGNED 0 0 524288000 512 0 YES UNASSIGNED
13 UNASSIGNED 0 0 524288000 512 0 YES UNASSIGNED
SQL> select * from v$logfile order by GROUP#,MEMBER;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
1 ONLINE /u01/app/oracle/oradata/branch/redo01a.log NO
1 ONLINE /u01/app/oracle/oradata/branch/redo01b.log NO
2 ONLINE /u01/app/oracle/oradata/branch/redo02a.log NO
2 ONLINE /u01/app/oracle/oradata/branch/redo02b.log NO
3 ONLINE /u01/app/oracle/oradata/branch/redo03a.log NO
3 ONLINE /u01/app/oracle/oradata/branch/redo03b.log NO
4 ONLINE /u01/app/oracle/oradata/branch/redo04a.log NO
4 ONLINE /u01/app/oracle/oradata/branch/redo04b.log NO
10 STANDBY /u01/app/oracle/oradata/branch/STAN010A.LOG NO
10 STANDBY /u01/app/oracle/oradata/branch/STAN010B.LOG NO
11 STANDBY /u01/app/oracle/oradata/branch/STAN011A.LOG NO
11 STANDBY /u01/app/oracle/oradata/branch/STAN011B.LOG NO
12 STANDBY /u01/app/oracle/oradata/branch/STAN012A.LOG NO
12 STANDBY /u01/app/oracle/oradata/branch/STAN012B.LOG NO
13 STANDBY /u01/app/oracle/oradata/branch/STAN013A.LOG NO
13 STANDBY /u01/app/oracle/oradata/branch/STAN013B.LOG NO
16 rows selected
SQL>
