DG在线日志组大小修改
环境(单实例,Centos 6.5 X64,oracle 10.2.0.5,filesystem存储)
REDO ONLINE LOG
select * from v$logfile where type='STANDBY'; ＃查询为standby log 三组
STANDBY LOG
select * from v$logfile where type<>'STANDBY'; #查询ONLINE LOG 为四组 1.备库standby log 添加
alter database recover managed standby database cancel;#取消APPLIED
alter database add standby logfile group ８
('/u01/app/oracle/oradata/netdata/st_redo08a.log','/u01/app/oracle/oradata/netdata/st_redo08b.log') size 300M;
alter database add standby logfile group 9
('/u01/app/oracle/oradata/netdata/st_redo09a.log','/u01/app/oracle/oradata/netdata/st_redo09b.log') size 300M;
alter database add standby logfile group 10
('/u01/app/oracle/oradata/netdata/st_redo10a.log','/u01/app/oracle/oradata/netdata/st_redo10b.log') size 300M;
alter database add standby logfile group 11
('/u01/app/oracle/oradata/netdata/st_redo11a.log','/u01/app/oracle/oradata/netdata/st_redo11b.log') size 300M; 2.删除备库standby 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; 如果出现以下错误，在主库上切换一下日志
ERROR at line 1:
ORA-00261: log 4 of thread 1 is being archived or modified
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/netdata/st_redo04b.log' 3.主库standbylog添加
alter database add standby logfile group ８
('/u01/app/oracle/oradata/netdata/st_redo08a.log','/u01/app/oracle/oradata/netdata/st_redo08b.log') size 300M;
alter database add standby logfile group 9
('/u01/app/oracle/oradata/netdata/st_redo09a.log','/u01/app/oracle/oradata/netdata/st_redo09b.log') size 300M;
alter database add standby logfile group 10
('/u01/app/oracle/oradata/netdata/st_redo10a.log','/u01/app/oracle/oradata/netdata/st_redo10b.log') size 300M;
alter database add standby logfile group 11
('/u01/app/oracle/oradata/netdata/st_redo11a.log','/u01/app/oracle/oradata/netdata/st_redo11b.log') size 300M; 4.删除主库旧的standby 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; 5．主库添加新的ONLINE REDO LOG
alter database add standby logfile group 12
('/u01/app/oracle/oradata/netdata/st_redo12a.log','/u01/app/oracle/oradata/netdata/st_redo12b.log') size 300M;
alter database add standby logfile group 13
('/u01/app/oracle/oradata/netdata/st_redo13a.log','/u01/app/oracle/oradata/netdata/st_redo13b.log') size 300M;
alter database add standby logfile group 14
('/u01/app/oracle/oradata/netdata/st_redo14a.log','/u01/app/oracle/oradata/netdata/st_redo14b.log') size 300M; 6.主库删除旧的ONLINE REDOLOG
删除之前切文档，首先查看是不是ACTIVE或者是INACTIVE
不是INACTIVE 多切几次归档
alter system logfile switch
alter system checkpoint
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3; 7.备库添加新的ONLINE REDO LOG
alter system set standby_file_management='MANUAL'; alter database add standby logfile group 12
('/u01/app/oracle/oradata/netdata/st_redo12a.log','/u01/app/oracle/oradata/netdata/st_redo12b.log') size 300M;
alter database add standby logfile group 13
('/u01/app/oracle/oradata/netdata/st_redo13a.log','/u01/app/oracle/oradata/netdata/st_redo13b.log') size 300M;
alter database add standby logfile group 14
('/u01/app/oracle/oradata/netdata/st_redo14a.log','/u01/app/oracle/oradata/netdata/st_redo14b.log') size 300M; 8.删除备库旧的ONLINE REDO LOG
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance netdata (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/netdata/redo01.log' [oracle@oracle10g-dg1-213-100 ~]$ oerr ora 01624
01624, 00000, "log %s needed for crash recovery of instance %s (thread %s)"
// *Cause: A log cannot be dropped or cleared until the thread's checkpoint
// has advanced out of the log.
// *Action: If the database is not open, then open it. Crash recovery will
// advance the checkpoint. If the database is open force a global
// checkpoint. If the log is corrupted so that the database cannot
// be opened, it may be necessary to do incomplete recovery until
// cancel at this log. 尝试clean logfile
ALTER DATABASE CLEAR LOGFILE GROUP 1
*
ERROR at line 1:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/netdata/redo01.log' [oracle@oracle10g-dg1-213-100 netdata]$ oerr ora 19527
19527, 00000, "physical standby redo log must be renamed"
// *Cause: The CLEAR LOGFILE command was used at a physical standby
// database. This command cannot be used at a physical standby
// database unless the LOG_FILE_NAME_CONVERT initialization
// parameter is set. This is required to avoid overwriting
// the primary database's logfiles.
// *Action Set the LOG_FILE_NAME_CONVERT initialization parameter.
LOG_FILE_NAME_CONVERT参数未初始化 SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/netdata/','/u01/app/oracle/oradata/netdata/' scope=spfile; System altered. SQL> shutdown immediate;
ORA-01109: database not open Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started. Total System Global Area 704643072 bytes
Fixed Size 2098912 bytes
Variable Size 184551712 bytes
Database Buffers 511705088 bytes
Redo Buffers 6287360 bytes
SQL> alter database mount standby database; Database altered. SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1; Database altered. SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2; Database altered. SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3; Database altered. SQL> alter database drop logfile group 1; Database altered. SQL> alter database drop logfile group 2; Database altered. SQL> alter database drop logfile group 3; Database altered. SQL> alter system set standby_file_management='AUTO' scope=both; System altered. SQL> alter database recover managed standby database disconnect from session; Database altered.