注意：log_file_name_convert如果不配置,将导致备库redo 文件不能被删除
检查主备库状态
主库：
|
select SEQUENCE#,first_time,next_time,APPLIED, THREAD# from v$archived_log order by SEQUENCE#,THREAD#;
49 31-MAR-16 31-MAR-16 YES 1
49 31-MAR-16 31-MAR-16 YES 1
49 31-MAR-16 31-MAR-16 NO 1
50 31-MAR-16 31-MAR-16 YES 1
50 31-MAR-16 31-MAR-16 NO 1
50 31-MAR-16 31-MAR-16 YES 1
51 31-MAR-16 31-MAR-16 NO 1
51 31-MAR-16 31-MAR-16 YES 1
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED THREAD#
---------- ------------ ------------ --------- -------
51 31-MAR-16 31-MAR-16 YES 1
52 31-MAR-16 31-MAR-16 YES 1
52 31-MAR-16 31-MAR-16 NO 1
53 31-MAR-16 31-MAR-16 NO 1
53 31-MAR-16 31-MAR-16 YES 1
54 31-MAR-16 31-MAR-16 YES 1
54 31-MAR-16 31-MAR-16 NO 1
55 31-MAR-16 01-APR-16 YES 1
55 31-MAR-16 01-APR-16 NO 1
56 01-APR-16 29-JUN-16 NO 1
56 01-APR-16 29-JUN-16 YES 1
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED THREAD#
---------- ------------ ------------ --------- -------
57 29-JUN-16 29-JUN-16 NO 1
57 29-JUN-16 29-JUN-16 YES 1
58 29-JUN-16 10-AUG-16 YES 1
58 29-JUN-16 10-AUG-16 NO 1
59 10-AUG-16 10-AUG-16 YES 1
59 10-AUG-16 10-AUG-16 NO 1
60 10-AUG-16 10-AUG-16 YES 1
60 10-AUG-16 10-AUG-16 NO 1
61 10-AUG-16 10-AUG-16 NO 1
61 10-AUG-16 10-AUG-16 YES 1
62 10-AUG-16 10-AUG-16 NO 1
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED THREAD#
---------- ------------ ------------ --------- -------
62 10-AUG-16 10-AUG-16 YES 1
SQL> set linesize 300
SQL> select LOG_MODE,CONTROLFILE_TYPE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
LOG_MODE CONTROL OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
------------ ------- -------------------- -------------------- ---------------- --------------------
ARCHIVELOG CURRENT READ WRITE MAXIMUM PERFORMANCE PRIMARY TO STANDBY
SQL> select GROUP#,BYTES/1024/1024 from v$log;
GROUP# BYTES/1024/1024
------ ---------------
1 50
2 50
3 50
SQL> select GROUP#,BYTES/1024/1024,STATUS from v$standby_log;
GROUP# BYTES/1024/1024 STATUS
------ --------------- ----------
4 50 UNASSIGNED
5 50 UNASSIGNED
6 50 UNASSIGNED
SQL>
|
备库：
|
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED THREAD#
---------- ------------ ------------ --------- ----------
35 31-MAR-16 31-MAR-16 YES 1
36 31-MAR-16 31-MAR-16 YES 1
37 31-MAR-16 31-MAR-16 YES 1
38 31-MAR-16 31-MAR-16 YES 1
39 31-MAR-16 31-MAR-16 YES 1
40 31-MAR-16 31-MAR-16 YES 1
41 31-MAR-16 31-MAR-16 YES 1
42 31-MAR-16 31-MAR-16 YES 1
43 31-MAR-16 31-MAR-16 YES 1
44 31-MAR-16 31-MAR-16 YES 1
45 31-MAR-16 31-MAR-16 YES 1
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED THREAD#
---------- ------------ ------------ --------- ----------
46 31-MAR-16 31-MAR-16 YES 1
47 31-MAR-16 31-MAR-16 YES 1
48 31-MAR-16 31-MAR-16 YES 1
49 31-MAR-16 31-MAR-16 YES 1
50 31-MAR-16 31-MAR-16 YES 1
51 31-MAR-16 31-MAR-16 YES 1
52 31-MAR-16 31-MAR-16 YES 1
53 31-MAR-16 31-MAR-16 YES 1
54 31-MAR-16 31-MAR-16 YES 1
55 31-MAR-16 01-APR-16 YES 1
56 01-APR-16 29-JUN-16 YES 1
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED THREAD#
---------- ------------ ------------ --------- ----------
57 29-JUN-16 29-JUN-16 YES 1
58 29-JUN-16 10-AUG-16 YES 1
59 10-AUG-16 10-AUG-16 YES 1
60 10-AUG-16 10-AUG-16 YES 1
61 10-AUG-16 10-AUG-16 YES 1
62 10-AUG-16 10-AUG-16 YES 1
SQL> select LOG_MODE,CONTROLFILE_TYPE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
LOG_MODE CONTROL OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
------------ ------- -------------------- -------------------- ---------------- --------------------
ARCHIVELOG STANDBY MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY SWITCHOVER PENDING
SQL> select GROUP#,BYTES/1024/1024 from v$log;
GROUP# BYTES/1024/1024
---------- ---------------
1 50
3 50
2 50
SQL> select GROUP#,BYTES/1024/1024,STATUS from v$standby_log;
GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------
4 50 UNASSIGNED
5 50 UNASSIGNED
6 50 UNASSIGNED
SQL>
|
断开备库日志应用和手工管理standby file参数
|
SQL> alter system set standby_file_management=manual;
System altered.
SQL> alter database recover managed standby database cancel;
Database altered.
|
主库添加新的redo日志组，删除旧redo日志组
|
SQL> alter database add logfile group 11 '/u01/oracle/oradata/orcl/redo11.log' size 100M;
Database altered.
SQL> alter database add logfile group 12 '/u01/oracle/oradata/orcl/redo12.log' size 100M;
Database altered.
SQL> alter database add logfile group 13 '/u01/oracle/oradata/orcl/redo13.log' size 100M;
Database altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance orcl (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/orcl/redo02.log'
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/orcl/redo02.log'
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
set linesize 500
col group# for 999
col mb for 999
col member for a100
col thread# for 999
col archived for a10
select a.group#,a.BYTES/1024/1024 mb,b.MEMBER,a.thread#,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time from gv$log a,gv$logfile b where a.GROUP#=b.GROUP# group by a.group#,a.thread#,a.BYTES/1024/1024,b.MEMBER,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time order by group#;
GROUP# MB MEMBER THREAD# SEQUENCE# MEMBERS ARCHIVED STATUS FIRST_TIME NEXT_TIME
------ ---- ---------------------------------------------------------------------------------------------------- ------- ---------- ---------- ---------- ---------------- ------------ ------------
11 100 /u01/oracle/oradata/orcl/redo11.log 1 64 1 YES INACTIVE 10-AUG-16 10-AUG-16
12 100 /u01/oracle/oradata/orcl/redo12.log 1 65 1 NO CURRENT 10-AUG-16
13 100 /u01/oracle/oradata/orcl/redo13.log 1 0 1 YES UNUSED
|
备库库添加新的redo日志组，删除旧redo日志组
|
SQL> alter database add logfile group 11 '/u01/oracle/oradata/orcl/redo11.log' size 100M;
Database altered.
SQL> alter database add logfile group 12 '/u01/oracle/oradata/orcl/redo12.log' size 100M;
Database altered.
SQL> alter database add logfile group 13 '/u01/oracle/oradata/orcl/redo13.log' size 100M;
Database altered.
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 orcl (thread 1)
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/orcl/redo01.log'
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>
GROUP# MB MEMBER THREAD# SEQUENCE# MEMBERS ARCHIVED STATUS FIRST_TIME NEXT_TIME
------ ---- ---------------------------------------------------------------------------------------------------- ------- ---------- ---------- ---------- ---------------- ------------ ------------
11 100 /u01/oracle/oradata/orcl/redo11.log 1 0 1 YES UNUSED
12 100 /u01/oracle/oradata/orcl/redo12.log 1 65 1 YES CURRENT 10-AUG-16
13 100 /u01/oracle/oradata/orcl/redo13.log 1 0 1 YES UNUSED
|
开启日志应用和standby file自动管理
|
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter system set standby_file_management=auto;
System altered.
|
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28572479/viewspace-2123965/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
