删除oracle的onlinelog,ORACLE DG在线日志修改

ORACLE DG在线日志修改

SQL>select SEQUENCE#,first_time,next_time,APPLIED, THREAD# from v$archived_log order by  SEQUENCE#,THREAD#;

select * from v$logfile order by GROUP# ;

SQL> select GROUP#,BYTES/1024/1024 from v$log;

SQL> select GROUP#,BYTES/1024/1024,STATUS from v$standby_log;

REDO ONLINE LOG状态

SQL>select * from v$logfile where type<>'STANDBY'; #ONLINE LOG 为三组

GROUP# STATUS TYPE MEMBER IS_ CON_ID

---------- ------- ------- ------------------------------------------------------------ --- ----------

1 ONLINE /u01/app/oracle/oradata/elon/st_redo01a.log NO 0

1 ONLINE /u01/app/oracle/oradata/elon/st_redo01b.log NO 0

2 ONLINE /u01/app/oracle/oradata/elon/st_redo02a.log NO 0

2 ONLINE /u01/app/oracle/oradata/elon/st_redo02b.log NO 0

3 ONLINE /u01/app/oracle/oradata/elon/st_redo03a.log NO 0

3 ONLINE /u01/app/oracle/oradata/elon/st_redo03b.log NO 0

STANDBY LOG状态

SQL>select * from v$logfile where type='STANDBY';　#standby log　四组

GROUP# STATUS TYPE MEMBER IS_ CON_ID

---------- ------- ------- ------------------------------------------------------------ --- ----------

4 STANDBY /u01/app/oracle/oradata/elon/st_redo04a.log NO 0

4 STANDBY /u01/app/oracle/oradata/elon/st_redo04b.log NO 0

5 STANDBY /u01/app/oracle/oradata/elon/st_redo05a.log NO 0

5 STANDBY /u01/app/oracle/oradata/elon/st_redo05b.log NO 0

6 STANDBY /u01/app/oracle/oradata/elon/st_redo06a.log NO 0

6 STANDBY /u01/app/oracle/oradata/elon/st_redo06b.log NO 0

7 STANDBY /u01/app/oracle/oradata/elon/st_redo07a.log NO 0

7 STANDBY /u01/app/oracle/oradata/elon/st_redo07b.log NO 0

1.备库添加standby log

SQL>alter database recover managed standby database cancel;#取消APPLIED

SQL>alter database add standby logfile group ８ ('/u01/app/oracle/oradata/elon/st_redo08a.log','/u01/app/oracle/oradata/elon/st_redo08b.log') size 300M;

SQL>alter database add standby logfile group 9 ('/u01/app/oracle/oradata/elon/st_redo09a.log','/u01/app/oracle/oradata/elon/st_redo09b.log') size 300M;

SQL>alter database add standby logfile group 10 ('/u01/app/oracle/oradata/elon/st_redo10a.log','/u01/app/oracle/oradata/elon/st_redo10b.log') size 300M;

SQL>alter database add standby logfile group 11 ('/u01/app/oracle/oradata/elon/st_redo11a.log','/u01/app/oracle/oradata/elon/st_redo11b.log') size 300M;

2.删除备库旧的standby log

SQL>alter database drop logfile group 4;

SQL>alter database drop logfile group 5;

SQL>alter database drop logfile group 6;

SQL>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/elon/st_redo04b.log'

3.主库添加standby log

SQL>alter database add standby logfile group ８ ('/u01/app/oracle/oradata/elon/st_redo08a.log','/u01/app/oracle/oradata/elon/st_redo08b.log') size 300M;

SQL>alter database add standby logfile group 9 ('/u01/app/oracle/oradata/elon/st_redo09a.log','/u01/app/oracle/oradata/elon/st_redo09b.log') size 300M;

SQL>alter database add standby logfile group 10 ('/u01/app/oracle/oradata/elon/st_redo10a.log','/u01/app/oracle/oradata/elon/st_redo10b.log') size 300M;

SQL>alter database add standby logfile group 11 ('/u01/app/oracle/oradata/elon/st_redo11a.log','/u01/app/oracle/oradata/elon/st_redo11b.log') size 300M;

4.删除主库旧的standby log

SQL>alter database drop logfile group 4;

SQL>alter database drop logfile group 5;

SQL>alter database drop logfile group 6;

SQL>alter database drop logfile group 7;

5．主库添加新的ONLINE REDO LOG

SQL>alter database add logfile group 12 ('/u01/app/oracle/oradata/elon/st_redo12a.log','/u01/app/oracle/oradata/elon/st_redo12b.log') size 300M;

SQL>alter database add logfile group 13 ('/u01/app/oracle/oradata/elon/st_redo13a.log','/u01/app/oracle/oradata/elon/st_redo13b.log') size 300M;

SQL>alter database add logfile group 14 ('/u01/app/oracle/oradata/elon/st_redo14a.log','/u01/app/oracle/oradata/elon/st_redo14b.log') size 300M;

6.主库删除旧的ONLINE REDOLOG，INACTIVE状态下进行删除，查看是不是INACTIVE，不是INACTIVE 多切几次归档

SQL>alter system logfile switch

SQL>alter system checkpoint

SQL>alter database drop logfile group 1;

SQL>alter database drop logfile group 2;

SQL>alter database drop logfile group 3;

7.备库添加新的ONLINE REDO LOG

alter system set standby_file_management='MANUAL';

alter database add logfile group 12 ('/u01/app/oracle/oradata/elon/st_redo12a.log','/u01/app/oracle/oradata/elon/st_redo12b.log') size 300M;

alter database add logfile group 13 ('/u01/app/oracle/oradata/elon/st_redo13a.log','/u01/app/oracle/oradata/elon/st_redo13b.log') size 300M;

alter database add logfile group 14 ('/u01/app/oracle/oradata/elon/st_redo14a.log','/u01/app/oracle/oradata/elon/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 elon (thread 1)

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/elon/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/elon/redo01.log'

[oracle@oracle10g-dg1-213-100 elon]$ 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/elon/','/u01/app/oracle/oradata/elon/' 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.