学校网站开发文档,建设部领导干部官方网站,潍坊网站制作培训,做视频怎么去除网站环境#xff1a;
有一个2节点RAC每一个节点2个logfile group每一个group含2个member每一个member的大小为200M
目标#xff1a;将每一个member的大小有200M扩充到1G。
先来看下redo log的配置#xff1a;
SQL select * from v$log;GROUP# THREAD# SEQUENCE# …环境
有一个2节点RAC每一个节点2个logfile group每一个group含2个member每一个member的大小为200M
目标将每一个member的大小有200M扩充到1G。
先来看下redo log的配置
SQL select * from v$log;GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
_________ __________ ____________ ____________ ____________ __________ ___________ ___________ ________________ _____________ ______________________ ____________ _________1 1 337 209715200 512 2 NO INACTIVE 2820063 14-DEC-23 2838640 14-DEC-23 02 1 338 209715200 512 2 NO CURRENT 2838640 14-DEC-23 9295429630892703743 03 2 1 209715200 512 2 NO INACTIVE 2195630 13-DEC-23 2713793 14-DEC-23 04 2 2 209715200 512 2 NO CURRENT 2713793 14-DEC-23 9295429630892703743 0SQL select * from v$logfile order by group#;GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE CON_ID
_________ _________ _________ ________________________________________________ ________________________ _________1 ONLINE RECOC1/ORCL/ONLINELOG/group_1.624.1155482177 YES 01 ONLINE DATAC1/ORCL/ONLINELOG/group_1.693.1155482175 NO 02 ONLINE RECOC1/ORCL/ONLINELOG/group_2.625.1155482177 YES 02 ONLINE DATAC1/ORCL/ONLINELOG/group_2.694.1155482175 NO 03 ONLINE DATAC1/ORCL/ONLINELOG/group_3.697.1155482693 NO 03 ONLINE RECOC1/ORCL/ONLINELOG/group_3.626.1155482693 YES 04 ONLINE DATAC1/ORCL/ONLINELOG/group_4.698.1155482693 NO 04 ONLINE RECOC1/ORCL/ONLINELOG/group_4.627.1155482693 YES 08 rows selected.有几个实例就有几个thread
SQL select thread#,status from v$thread;THREAD# STATUS
__________ _________1 OPEN2 OPEN从SQL Developer中看到的配置 直接删掉group重建是不行的因为每一个实例必须保证最少2个group。
SQL ALTER DATABASE DROP LOGFILE GROUP 1;Error starting at line : 1 in command -
ALTER DATABASE DROP LOGFILE GROUP 1
Error report -
ORA-01567: dropping log 1 would leave less than 2 log files for instance orcl1 (thread 1)
ORA-00312: online log 1 thread 1: DATAC1/ORCL/ONLINELOG/group_1.693.1155482175
ORA-00312: online log 1 thread 1: RECOC1/ORCL/ONLINELOG/group_1.624.1155482177
01567. 00000 - dropping log %s would leave less than 2 log files for instance %s (thread %s)
*Cause: Dropping all the logs specified would leave fewer than the requiredtwo log files per enabled thread.
*Action: Either drop fewer logs or disable the thread before deleting thelogs. It may be possible to clear the log rather than drop it.方法其实简单就是先加一个临时的redo log file group然后就可以删除重建了最终再把这个临时的删除就好。
先处理2个状态为Inactive的log file group即#1和#3因为他们可以直接删。
先对实例1上的#1进行操作
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 (DATAC1) SIZE 200M;
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 (DATAC1, RECOC1) SIZE 1G;
ALTER DATABASE DROP LOGFILE GROUP 5;再对实例2上的#3进行操作
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 5 (DATAC1) SIZE 200M;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 (DATAC1, RECOC1) SIZE 1G;
ALTER DATABASE DROP LOGFILE GROUP 5;现在logfile group 1和3都改好了还剩#2和#4。
先对实例1上的#2进行操作
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 (DATAC1) SIZE 200M;此时无法删除#2因为他的状态是current
SQL ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance orcl1 (thread 1) - cannot drop
ORA-00312: online log 2 thread 1:
DATAC1/ORCL/ONLINELOG/group_2.694.1155482175
ORA-00312: online log 2 thread 1:
RECOC1/ORCL/ONLINELOG/group_2.625.1155482177先做一次log switch
SQL alter system switch logfile;System altered.此时#2的状态变为Active但仍无法删除因为其要用于实例恢复
SQL ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance orcl1 (thread 1)
ORA-00312: online log 2 thread 1:
DATAC1/ORCL/ONLINELOG/group_2.694.1155482175
ORA-00312: online log 2 thread 1:
RECOC1/ORCL/ONLINELOG/group_2.625.1155482177过一会其状态变为Inactive就可以删除了。如果实在等不急也可以运行命令ALTER SYSTEM CHECKPOINT
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 (DATAC1, RECOC1) SIZE 1G;
ALTER DATABASE DROP LOGFILE GROUP 5;实例2也可以照此操作连接到实例2运行
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 5 (DATAC1) SIZE 200M;
alter system switch logfile;
ALTER SYSTEM CHECKPOINT;
ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 (DATAC1, RECOC1) SIZE 1G;
ALTER DATABASE DROP LOGFILE GROUP 5;好了修改成功。
