Shulou(Shulou.com)06/01 Report--

Current environment:

Oracle 11.0.2.4 primary+standby

3 redo log group groups (200m each), 4 standby redo log groups

Due to the increase in business, we plan to modify the redo logfile to 250m.

Select * from v$logfile

1 ONLINE / data/mes/redo01.log

2 ONLINE / data/mes/redo02.log

3 ONLINE / data/mes/redo03.log

11 STANDBY / data/mes/standbylog11.log

12 STANDBY / data/mes/standbylog12.log

13 STANDBY / data/mes/standbylog13.log

14 STANDBY / data/mes/standbylog14.log

The analysis shows that:

1 > since redo logfile cannot be directly modified, it is planned to add log group first, and then delete the original log group.

2 > because the dataguard environment involves the redo log and standby redo log of the main database and standby, it is divided into the following eight steps

Have the ability to walk:

1. Add new standby redo log to Standby

SQL > alter database recover managed standby database cancel

Database altered.

SQL > alter database add standby logfile group 31 ('/ data/mes/standbylog31.log') size 250m

Database altered.

SQL > alter database add standby logfile group 32 ('/ data/mes/standbylog32.log') size 250m

Database altered.

SQL > alter database add standby logfile group 33 ('/ data/mes/standbylog33.log') size 250m

Database altered.

SQL > alter database add standby logfile group 34 ('/ data/mes/standbylog34.log') size 250m

Database altered.

2. Delete the original standby redo log on Standby

SQL > alter database drop logfile group 11

Database altered.

SQL > alter database drop logfile group 12

Database altered.

SQL > alter database drop logfile group 13

Database altered.

SQL > alter database drop logfile group 14

Database altered.

3. Create a new redo log on the main database

SQL > alter database add logfile group 21 ('/ data/mes/standbylog21.log') size 250m

Database altered.

SQL > alter database add logfile group 22 ('/ data/mes/standbylog22.log') size 250m

Database altered.

SQL > alter database add logfile group 23 ('/ data/mes/standbylog23.log') size 250m

Database altered.

4. Delete the original redo log on the main database

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

Alter database drop logfile group 3

*

ERROR at line 1:

ORA-01623: log 3 is current log for instance mes (thread 1)-cannot drop

ORA-00312: online log 3 thread 1:'/ data/mes/redo03.log'

Note: cut the main database redo logfile and delete it when log group 3 is not current and active

SQL > alter system switch logfile

System altered.

SQL > alter database drop logfile group 3

Database altered.

5. Add new redo log to Standby

SQL > show parameter standby

NAME TYPE VALUE

-

Standby_archive_dest string? / dbs/arch

Standby_file_management string AUTO

SQL > alter system set standby_file_management=manual

System altered.

SQL > alter database add logfile group 21 ('/ data/mes/standbylog21.log') size 250m

Database altered.

SQL > alter database add logfile group 22 ('/ data/mes/standbylog22.log') size 250m

Database altered.

SQL > alter database add logfile group 23 ('/ data/mes/standbylog23.log') size 250m

Database altered.

Delete the original redo log on 6.Standby

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

Alter database drop logfile group 3

*

ERROR at line 1:

ORA-01623: log 3 is current log for instance mes (thread 1)-cannot drop

ORA-00312: online log 3 thread 1:

'/ u01Universe productAccording to recoveryAccording to MESDG Universe onlinelogAccording to onlinelogAccording to O1cm, mfin, to 3mm, to f46lgbqpwith.log'

Query v$log group 3 in standby because CURRENT status does not allow drop, so report

Run the following SQL on the main database and standby, respectively:

SELECT DISTINCT THREAD#,max (SEQUENCE#) OVER (PARTITION BY THREAD#) A FROM V$ARCHIVED_LOG

Main database SEQUENCE#: 67 standby SEQUENCE#:65

Standby uses the newly generated logs for switch current status group 3

SQL > alter database recover managed standby database using current logfile disconnect

Database altered.

Check that v$log 3 is not CURRENT in standby. Cancel MRP and drop group 3 is successful again.

SQL > alter database recover managed standby database cancel

Database altered.

SQL > alter database drop logfile group 3

Database altered.

7. Add a new standby redo log to the main database

SQL > alter database add standby logfile group 31 ('/ data/mes/standbylog31.log') size 250m

Database altered.

SQL > alter database add standby logfile group 32 ('/ data/mes/standbylog32.log') size 250m

Database altered.

SQL > alter database add standby logfile group 33 ('/ data/mes/standbylog33.log') size 250m

Database altered.

SQL > alter database add standby logfile group 34 ('/ data/mes/standbylog34.log') size 250m

Database altered.

8. Primary database delete original standby redo log

SQL > alter database drop logfile group 11

Database altered.

SQL > alter database drop logfile group 12

Database altered.

SQL > alter database drop logfile group 13

Database altered.

SQL > alter database drop logfile group 14

Database altered.

Finally, when MRP is opened in standby, all operations are completed

SQL > alter system set standby_file_management=auto

System altered.

SQL > alter database recover managed standby database using current logfile disconnect

Database altered.

Select * from v$logfile

21 ONLINE / data/mes/standbylog21.log

22 ONLINE / data/mes/standbylog22.log

23 ONLINE / data/mes/standbylog23.log

31 STANDBY / data/mes/standbylog31.log

32 STANDBY / data/mes/standbylog32.log

33 STANDBY / data/mes/standbylog33.log

34 STANDBY / data/mes/standbylog34.log

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.