因为上了SSD,SSD对小文件连续写会有性能问题,因此ORACLE推荐把redo和control迁移到SAS盘上,因此要重建redo log和standby_log,在备库上新建了redo和standby后,想删除旧的log
发现问题,备库上新建的online和standby log都无法被ORACLE使用,因此备库上旧的日志也无法彻底删除
17:25:12 sys@DSEDI>show parameter standby

NAME TYPE VALUE
———————————— ———– ——————————
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO

17:25:47 sys@DSEDI>alter database recover managed standby database cancel ;

Database altered.

17:26:15 sys@DSEDI>ALTER DATABASE DROP LOGFILE GROUP 17 ;
ALTER DATABASE DROP LOGFILE GROUP 17
*
ERROR at line 1:
ORA-01275: Operation DROP LOGFILE is not allowed if standby file management is automatic.
17:26:25 sys@DSEDI>alter system set standby_file_management=’MANUAL’ ;

System altered.

17:26:33 sys@DSEDI>ALTER DATABASE DROP LOGFILE GROUP 17 ;
ALTER DATABASE DROP LOGFILE GROUP 17
*
ERROR at line 1:
ORA-01623: log 17 is current log for instance dsedidg (thread 1) – cannot drop
ORA-00312: online log 17 thread 1: ‘/ecdata/dsedi/oradata/dsedi/redo17.log’
17:26:38 sys@DSEDI>alter database clear logfile group 17 ;

Database altered.

17:26:45 sys@DSEDI>ALTER DATABASE DROP LOGFILE GROUP 17 ;
ALTER DATABASE DROP LOGFILE GROUP 17
*
ERROR at line 1:
ORA-01623: log 17 is current log for instance dsedidg (thread 1) – cannot drop
ORA-00312: online log 17 thread 1: ‘/ecdata/dsedi/oradata/dsedi/redo17.log’

===================================================
解决方案:把备库的online log以及standby log 建成和主库一模一样,包括组编号和大小

alter system set standby_file_management=’MANUAL’ ;
alter system set db_create_online_log_dest_1=’/tmpdata/dsedi_redo/’ ;
alter database recover managed standby database cancel ;

提前新建好新的redo和standby
ALTER DATABASE ADD LOGFILE GROUP 101 size 400M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 121 size 400M;

等日志状态不是ACTIVE和CURRENT的时候,就可以删除旧的日志了
ALTER DATABASE DROP LOGFILE GROUP 1 ;

删除后再开启日志恢复,已经恢复standby_file_management参数为AUTO