1、主库standbylog添加及删除

SQL> alter database add standby logfile group 8 (‘/u01/app/oracle/oradata/pridb/std_redo08.log’) size 50M;

Database altered.

SQL> col member for a60;

SQL> set linesize 200;

SQL> select group#,member from v$logfile where type=’STANDBY’;

GROUP# MEMBER

———- ————————————————————

4 /u01/app/oracle/oradata/pridb/std_redo04.log

5 /u01/app/oracle/oradata/pridb/std_redo05.log

6 /u01/app/oracle/oradata/pridb/std_redo06.log

7 /u01/app/oracle/oradata/pridb/std_redo07.log

8 /u01/app/oracle/oradata/pridb/std_redo08.log

SQL> alter database drop logfile group 4;

Database altered.

SQL> select group#,member from v$logfile where type=’STANDBY’;

GROUP# MEMBER

———- ————————————————————

5 /u01/app/oracle/oradata/pridb/std_redo05.log

6 /u01/app/oracle/oradata/pridb/std_redo06.log

7 /u01/app/oracle/oradata/pridb/std_redo07.log

8 /u01/app/oracle/oradata/pridb/std_redo08.log;

核对备库信息虽然在主库上操作standby log file，没有做同步

SQL> set linesize 200;

SQL> col member for a60;

SQL> select group#,type,member from v$logfile;

GROUP# TYPE MEMBER

———- ——- ————————————————————

1 ONLINE /u01/app/oracle/oradata/stddb/redo01.log

2 ONLINE /u01/app/oracle/oradata/stddb/redo02.log

3 ONLINE /u01/app/oracle/oradata/stddb/redo03.log

4 STANDBY /u01/app/oracle/oradata/stddb/std_redo04.log

5 STANDBY /u01/app/oracle/oradata/stddb/std_redo05.log

6 STANDBY /u01/app/oracle/oradata/stddb/std_redo06.log

7 STANDBY /u01/app/oracle/oradata/stddb/std_redo07.log

7 rows selected.

2、备库standby log添加及删除

这句命令很重要操作备库standby log时需要关闭日志应用，做完后开启

alter database recover managed standby database cancel;–关闭语句

alter database recover managed standby database disconnect from session;–开启语句

SQL> alter database add standby logfile group 9 (‘/u01/app/oracle/oradata/stddb/std_redo09.log’) size 50M;

alter database add standby logfile group 9 (‘/u01/app/oracle/oradata/stddb/std_redo09.log’) size 50M

*

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files<<<<<<<<<<<<<===不关闭applied log进程时错误

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database add standby logfile group 9 (‘/u01/app/oracle/oradata/stddb/std_redo09.log’) size 50M;

Database altered.

SQL> select group#,status,ARCHIVED from v$standby_log;

GROUP# STATUS ARC

———- ———- —

4 ACTIVE YES

6 UNASSIGNED NO

7 UNASSIGNED NO

9 UNASSIGNED YES

SQL> /

GROUP# STATUS ARC

———- ———- —

4 UNASSIGNED NO <<<<<<<<<<<<<<<<<<<<<<<

6 ACTIVE YES

7 UNASSIGNED NO

9 UNASSIGNED YES

SQL> alter database drop logfile group 4;

Database altered.

SQL> select group#,status,ARCHIVED from v$standby_log;

GROUP# STATUS ARC

———- ———- —

6 ACTIVE YES

7 UNASSIGNED NO

9 UNASSIGNED YES

SQL> alter database add standby logfile group 5 (‘/u01/app/oracle/oradata/stddb/std_redo05.log’) size 50M;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

3、主库redolog添加及删除

SQL> alter database add logfile group 4 (‘/u01/app/oracle/oradata/pridb/redo04.log’) size 50M;

Database altered.

SQL> select group#,type,member from v$logfile;

GROUP# TYPE MEMBER

———- ——- ————————————————————

1 ONLINE /u01/app/oracle/oradata/pridb/redo01.log

2 ONLINE /u01/app/oracle/oradata/pridb/redo02.log

3 ONLINE /u01/app/oracle/oradata/pridb/redo03.log

4 ONLINE /u01/app/oracle/oradata/pridb/redo04.log

5 STANDBY /u01/app/oracle/oradata/pridb/std_redo05.log

6 STANDBY /u01/app/oracle/oradata/pridb/std_redo06.log

7 STANDBY /u01/app/oracle/oradata/pridb/std_redo07.log

8 STANDBY /u01/app/oracle/oradata/pridb/std_redo08.log

8 rows selected.

SQL> select group#,status,archived from v$Log;

GROUP# STATUS ARC

———- —————- —

1 CURRENT NO

2 INACTIVE YES

3 INACTIVE YES

4 UNUSED YES

SQL> alter system archive log current;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select group#,status,archived from v$Log;

GROUP# STATUS ARC

———- —————- —

1 INACTIVE YES

2 INACTIVE YES

3 INACTIVE YES

4 CURRENT NO

SQL> alter database drop logfile group 1;

Database altered.

SQL> select group#,status,archived from v$Log;

GROUP# STATUS ARC

———- —————- —

2 INACTIVE YES

3 INACTIVE YES

4 CURRENT NO

主库的上redo logfile操作不会影响到备库，oracle内部有控制机制

4、备库redolog添加删除

Stop Redo apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Set STANDBY_FILE_MANAGEMENT to MANUAL.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=’MANUAL’;

Add Redolog File Group:

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 (‘\U01\oracle\modg1\redo04.log’) SIZE 50M;

Add as many Redo Logfile Groups (or Members) you want to add.

Set STANDBY_FILE_MANAGEMENT to AUTO.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=’AUTO’;

Check the Status of the Online Redolog Group.

SQL> SELECT GROUP#, STATUS FROM V$LOG;

GROUP# STATUS

———- —————-

1 CLEARING

2 CURRENT

3 CLEARING

If Status is CLEARING_CURRENT then you cannot drop Online Redolog Group.

You will get ORA-01623 if you try to drop a Redolog Group with Status CLEARING_CURRENT.