oracle11g RAC修改日志组

最新推荐文章于 2021-04-13 14:39:50 发布

csknt26844

最新推荐文章于 2021-04-13 14:39:50 发布

阅读量143

点赞数

文章标签：数据库

1、查看现有联机日志情况（每个实例有两组日志，每组日志包含两个成员文件（50M））
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 31 52428800 512 2 YES INACTIVE 1511477 19-MAR-16 1519973 19-MAR-16
2 1 32 52428800 512 2 NO CURRENT 1519973 19-MAR-16 2.8147E+14
3 2 25 52428800 512 2 YES INACTIVE 1511442 19-MAR-16 1511453 19-MAR-16
4 2 26 52428800 512 2 YES ACTIVE 1512128 19-MAR-16 1514981 19-MAR-16

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
2 ONLINE +DATA/orcl/onlinelog/group_2.262.893084027 NO
2 ONLINE +FLASHDG/orcl/onlinelog/group_2.258.893084031 YES
1 ONLINE +DATA/orcl/onlinelog/group_1.261.893084021 NO
1 ONLINE +FLASHDG/orcl/onlinelog/group_1.257.893084025 YES
3 ONLINE +DATA/orcl/onlinelog/group_3.265.893084135 NO
3 ONLINE +FLASHDG/orcl/onlinelog/group_3.259.893084137 YES
4 ONLINE +DATA/orcl/onlinelog/group_4.266.893084141 NO
4 ONLINE +FLASHDG/orcl/onlinelog/group_4.260.893084143 YES

2、备份必要文件
create pfile='/home/oracle/bak_pfile_20130427_add_redolog.ora' from spfile;

alter database backup controlfile to '/home/oracle/bak_controlfile_20130427_add_redolog.ora';

3、增加日志组，并查询确认
alter database add logfile thread 1 group 5('+DATA/orcl/onlinelog/group5_1','+FLASHDG/orcl/onlinelog/group5_2') size 100M;
alter database add logfile thread 2 group 6('+DATA/orcl/onlinelog/group6_1','+FLASHDG/orcl/onlinelog/group6_2') size 100M;
alter database add logfile thread 1 group 7('+DATA/orcl/onlinelog/group7_1','+FLASHDG/orcl/onlinelog/group7_2') size 100M;
alter database add logfile thread 2 group 8('+DATA/orcl/onlinelog/group8_1','+FLASHDG/orcl/onlinelog/group8_2') size 100M;

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 31 52428800 512 2 YES INACTIVE 1511477 19-MAR-16 1519973 19-MAR-16
2 1 32 52428800 512 2 NO CURRENT 1519973 19-MAR-16 2.8147E+14
3 2 25 52428800 512 2 YES INACTIVE 1511442 19-MAR-16 1511453 19-MAR-16
4 2 26 52428800 512 2 YES ACTIVE 1512128 19-MAR-16 1514981 19-MAR-16
5 1 0 104857600 512 2 YES UNUSED 0 0
6 2 0 104857600 512 2 YES UNUSED 0 0
7 1 0 104857600 512 2 YES UNUSED 0 0
8 2 0 104857600 512 2 YES UNUSED 0 0

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
2 ONLINE +DATA/orcl/onlinelog/group_2.262.893084027 NO
2 ONLINE +FLASHDG/orcl/onlinelog/group_2.258.893084031 YES
1 ONLINE +DATA/orcl/onlinelog/group_1.261.893084021 NO
1 ONLINE +FLASHDG/orcl/onlinelog/group_1.257.893084025 YES
3 ONLINE +DATA/orcl/onlinelog/group_3.265.893084135 NO
3 ONLINE +FLASHDG/orcl/onlinelog/group_3.259.893084137 YES
4 ONLINE +DATA/orcl/onlinelog/group_4.266.893084141 NO
4 ONLINE +FLASHDG/orcl/onlinelog/group_4.260.893084143 YES
5 ONLINE +DATA/orcl/onlinelog/group5_1 NO
5 ONLINE +FLASHDG/orcl/onlinelog/group5_2 NO
6 ONLINE +DATA/orcl/onlinelog/group6_1 NO
6 ONLINE +FLASHDG/orcl/onlinelog/group6_2 NO
7 ONLINE +DATA/orcl/onlinelog/group7_1 NO
7 ONLINE +FLASHDG/orcl/onlinelog/group7_2 NO
8 ONLINE +DATA/orcl/onlinelog/group8_1 NO
8 ONLINE +FLASHDG/orcl/onlinelog/group8_2 NO

4、删除原来的日志(在删除之前一定要切换日志，确认日志组为INACTIVE状态)
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;

5、添加1、2、3、4日志组
alter database add logfile thread 1 group 1('+DATA/orcl/onlinelog/group1_1','+FLASHDG/orcl/onlinelog/group1_2') size 100M;
alter database add logfile thread 2 group 2('+DATA/orcl/onlinelog/group2_1','+FLASHDG/orcl/onlinelog/group2_2') size 100M;
alter database add logfile thread 1 group 3('+DATA/orcl/onlinelog/group3_1','+FLASHDG/orcl/onlinelog/group3_2') size 100M;
alter database add logfile thread 2 group 4('+DATA/orcl/onlinelog/group4_1','+FLASHDG/orcl/onlinelog/group4_2') size 100M;

6、查看新的日志切换是否正常

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

转载于:http://blog.itpub.net/30373263/viewspace-2060588/