1.备库online redo log管理
1.1.停止备库的redo apply
SQL> alter database recover managed standby database cancel;or
DGMGRL> edit database s_airdb set state=apply-off;
1.2.修改standby_file_management为手动,最后要改回自动:standby_file_management=auto;
SQL> alter system set standby_file_management=manual;
or
DGMGRL>edit database s_airdb set property StandbyFileManagement=manual;
整个操作过程如下：
SQL> select GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS from v$log;
GROUP# SEQUENCE# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
1 55 256 CURRENT
2 54 256 CLEARING
3 51 256 CLEARING
4 52 256 CLEARING
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
DGMGRL> edit database s_airdb set state=apply-off;
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01275: Operation DROP LOGFILE is not allowed if standby file management is automatic.
DGMGRL>edit database s_airdb set property StandbyFileManagement=manual;
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 airdb (thread 1)
ORA-00312: online log 2 thread 1: '/U01/app/oracle/oradata/airdb/redo02_01.log'
强势插入：
对于已经完成归档的redo group，即状态为inactive的redo group：
执行： alter database clear logfile group
因为这种情况下，redo 已经完成归档，所以恢复时不会造成数据的丢失。
当redo 为active时，即表示正在归档，这个时候，只能执行：
alter database clear unarchived logfile group
这个时候，如果进行恢复，是会有数据丢失的。 一般除非redo 损坏才会用到这个命令。具体的情况还是需要具体对待。 如果的Data Guard 环境，执行了alter database clear unarchived logfile group，那么DG 就需要重新搭建了----------------------------Dave。
一、 V$LOG:STATUS 指的是GROUP的状态
UNUSED：
尚未记录change的空白group（一般会出现在，loggroup刚刚被添加，或者刚刚使用了resetlogs打开数据库，或者使用clear logfile后）
CURRENT:
当前正在被LGWR使用的group(同时是ACTIVE状态)
ACTIVE：
未被LGWR写，instance recovery需要的。还没有完成checkpoint。
INACTIVE：
online,未被LGWR写，对instance recovery不再有用了。
CLEARING：
正在被clear的group（Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.）
CLEARING_CURRENT:
一个正在被clear的CURRENT group（Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.）出现在中状态一般是有错误的。
二、V$LOGFILE:STATUS指的是member的状态
INVALID:
不能使用的member,可能损坏了(另外，刚加入到redolog group的日志文件在被使用之前也是这个状态)
STALE:
metalink上对stale的解释：
Log files may have a status of stale regardless of redo log mirroring or
archival mode.
Solution Description:
=====================
In general, the stale status of a redo log member should not be a cause for
great concern, unless you observe that this happens frequently or
systematically. Keep in mind that a stale log is not necessarily an invalid
log, but more of an "in-doubt" one. Once the corresponding redo group becomes
the current one again, the stale status will go away by itself.
DELETED：
被alter database drop logfile member的member
BLANK:
正常的都是blank，不管是否被写。
Database altered.
Database altered.
SQL> select GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS from v$log;
GROUP# SEQUENCE# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
1 55 256 CURRENT
3 51 256 CLEARING
4 52 256 CLEARING
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 '/U01/app/oracle/oradata/airdb/redo02_01.log' SIZE 128M reuse;
Database altered.
SQL> select GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS from v$log;
GROUP# SEQUENCE# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
1 55 256 CURRENT
2 0 128 UNUSED
3 51 256 CLEARING
4 52 256 CLEARING
SQL> alter database clear logfile group 3;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database clear logfile group 4;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> select GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS from v$log;
GROUP# SEQUENCE# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
1 55 256 CURRENT
2 0 128 UNUSED
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 '/U01/app/oracle/oradata/airdb/redo03_01.log' SIZE 128M reuse;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 '/U01/app/oracle/oradata/airdb/redo04_01.log' SIZE 128M reuse;
Database altered.
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance airdb (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/U01/app/oracle/oradata/airdb/redo01_01.log'
主库切换日志
SQL> alter system switch logfile;
System altered.
回到备库
SQL> select GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS from v$log;
GROUP# SEQUENCE# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
1 0 256 CLEARING
2 56 128 CURRENT
3 0 128 UNUSED
4 0 128 UNUSED
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 '/U01/app/oracle/oradata/airdb/redo01_01.log' SIZE 128M reuse;
Database altered.
SQL> select GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS from v$log;
GROUP# SEQUENCE# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
1 0 128 UNUSED
2 56 128 CURRENT
3 0 128 UNUSED
4 0 128 UNUSED
2.备库standby redo log管理
备库standby redo管理只需要停止apply就可以了SQL> show parameter stand
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string auto
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> select GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS from v$standby_log;
GROUP# SEQUENCE# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------
5 0 256 UNASSIGNED
6 56 256 ACTIVE
7 0 256 UNASSIGNED
8 0 256 UNASSIGNED
9 0 256 UNASSIGNED
SQL> alter database drop standby logfile group 5;
alter database drop standby logfile group 5
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
DGMGRL> edit database s_airdb set state=apply-off;
Succeeded.
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/U01/app/oracle/oradata/airdb/std_redo05_01.log' SIZE 128M reuse;
Database altered.
SQL> alter database drop standby logfile group 6;
alter database drop standby logfile group 6
*
ERROR at line 1:
ORA-00261: log 6 of thread 1 is being archived or modified
ORA-00312: online log 6 thread 1:'/U01/app/oracle/oradata/airdb/stredo06_01.log'
6 56 256 ACTIVE，，，，上面的查询状态为active，需要切换日志，先处理其他组
SQL> alter database drop standby logfile group 7;
Database altered.
SQL> alter database drop standby logfile group 8;
Database altered.
SQL> alter database drop standby logfile group 9;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/U01/app/oracle/oradata/airdb/std_redo07_01.log' SIZE 128M reuse;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 '/U01/app/oracle/oradata/airdb/std_redo08_01.log' SIZE 128M reuse;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 '/U01/app/oracle/oradata/airdb/std_redo09_01.log' SIZE 128M reuse;
Database altered.
SQL> select GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS from v$standby_log;
GROUP# SEQUENCE# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------
5 0 128 UNASSIGNED
6 56 256 ACTIVE
7 0 128 UNASSIGNED
8 0 128 UNASSIGNED
9 0 128 UNASSIGNED
主库切换日志
SQL> alter system switch logfile;
System altered.
回到备库
SQL> select GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS from v$standby_log;
GROUP# SEQUENCE# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------
5 57 128 ACTIVE
6 0 256 UNASSIGNED
7 0 128 UNASSIGNED
8 0 128 UNASSIGNED
9 0 128 UNASSIGNED
SQL> alter database drop standby logfile group 6;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/U01/app/oracle/oradata/airdb/std_redo06_01.log' SIZE 128M reuse;
Database altered.
SQL> select GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS from v$standby_log;
GROUP# SEQUENCE# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------
5 57 128 ACTIVE
6 0 128 UNASSIGNED
7 0 128 UNASSIGNED
8 0 128 UNASSIGNED
9 0 128 UNASSIGNED
=========================================================================================
3.Error is 16143
Client logon and security negotiation successful!
Error 16143 attaching RFS server to standby instance at host 'steve89'
Error 16143 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'steve89'
ORA-16143: RFS connections not allowed during or after terminal recovery
*** 2009-03-25 18:54:52.597 60679 kcrr.c
PING[ARC1]: Heartbeat failed to connect to standby 'steve89'. Error is 16143.
*** 2009-03-25 18:54:52.597 58941 kcrr.c
kcrrfail: dest:2 err:16143 force:0 blast:1
sql> alter system set log_archive_dest_2='service=steve89 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=steve89' scope=both;
alter database recover managed standby database finish; 这是failover,主库崩溃的时候才用，用了它意味着备库成了主库,.原来的主库没了
RC0: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARC1: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
LGWR: Error 16143 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'steve89'
Wed Mar 25 19:33:35 2009
Thread 1 advanced to log sequence 54
Current log# 2 seq# 54 mem# 0: /u01/oradata/steve/redo02.log
Wed Mar 25 19:34:12 2009
Thread 1 advanced to log sequence 55
Current log# 3 seq# 55 mem# 0: /u01/oradata/steve/redo03.log
Thread 1 cannot allocate new log, sequence 56
Checkpoint not complete
Current log# 3 seq# 55 mem# 0: /u01/oradata/steve/redo03.log
Thread 1 advanced to log sequence 56
Current log# 1 seq# 56 mem# 0: /u01/oradata/steve/redo01.log
Wed Mar 25 19:34:54 2009
Errors in file /u01/admin/steve/bdump/steve_arc1_6155.trc:
ORA-16143: RFS connections not allowed during or after terminal recovery
Wed Mar 25 19:34:54 2009
PING[ARC1]: Heartbeat failed to connect to standby 'steve89'. Error is 16143.
Wed Mar 25 19:35:19 2009
Thread 1 cannot allocate new log, sequence 57
Checkpoint not complete
Current log# 1 seq# 56 mem# 0: /u01/oradata/steve/redo01.log
Thread 1 advanced to log sequence 57
Current log# 2 seq# 57 mem# 0: /u01/oradata/steve/redo02.log
试着解决:
--主库上SQL> alter database set standby to maximize performance;
--备库上sql>alter database recover standby database disconnect;
再看是否有错误出现
---主库上alter system switch logfile
同样出错
Redo shipping client performing standby login
*** 2009-03-25 20:36:03.240 64561 kcrr.c
Logged on to standby successfully
Client logon and security negotiation successful!
Error 16143 attaching RFS server to standby instance at host 'steve89'
Error 16143 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'steve89'
ORA-16143: RFS connections not allowed during or after terminal recovery
*** 2009-03-25 20:36:03.267 60679 kcrr.c
PING[ARC0]: Heartbeat failed to connect to standby 'steve89'. Error is 16143.
*** 2009-03-25 20:36:03.267 58941 kcrr.c
kcrrfail: dest:2 err:16143 force:0 blast:1
kcrrwkx: nothing to do (end)
*** 2009-03-25 20:37:03.266
kcrrwkx: nothing to do (end)
----查了一下备库报出如下错误\
lter database recover managed standby database disconnect from session
Sat Mar 28 23:19:36 2009
Attempt to start background Managed Standby Recovery process (steve89)
MRP0 started with pid=18, OS id=2855
Sat Mar 28 23:19:36 2009
MRP0: Background Managed Standby Recovery process started (steve89)
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 16157
Sat Mar 28 23:19:41 2009
Errors in file /u01/admin/steve/bdump/steve89_mrp0_2855.trc:
ORA-16157: media recovery not allowed following successful FINISH recovery
Sat Mar 28 23:19:41 2009
Errors in file /u01/admin/steve/bdump/steve89_mrp0_2855.trc:
ORA-16157: media recovery not allowed following successful FINISH recovery
Sat Mar 28 23:19:41 2009
MRP0: Background Media Recovery process shutdown (steve89)
Sat Mar 28 23:19:42 2009
Completed: alter database recover managed standby database disconnect from session
Sat Mar 28 23:22:14 2009
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/oradata/steve/arch/
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
----在备库上设STANDBY_ARCHIVE_DEST
注(STANDBY_ARCHIVE_DEST is used when logs are transmitted automatically from the primary database. We use
this parameter to determine where these logs should be placed.
LOG_ARCHIVE_DEST is the location where we look for logfiles by default when recovering the standby database.
It is recommended to set the values of STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST to the same values in most
cases.)
SQL> alter system set standby_archive_dest='/u01/oradata/steve/standlog' scope=both;
---主库上SQL> alter system switch logfile;再次查看
还是报错 晕
--现在是最大性能模式
--主库上改下
SQL> alter system set log_archive_dest_2='service=steve89 lgwr async valid_for=(online_logfiles,primary_role)
db_unique_name=steve89' scope=both; 改成async
--重启下备库再看看
同样报错(再晕)
试着恢复一下standby database
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;查不一至的log
cp到备库alter database register physical logfile '/u01/oradata/steve/arch/1_55_654475338.dbf';(注册日志文件)
--还是不行
--从主库上件一个控制文件再试试
--sql>alter database create standby controlfile as 'standbycto.ctl';
---控制文件COPY到备库盖旧的控制文件
--重启备库
--sql>startup nomout
--sql>alter database mount standby database;
---sql>alter database recover managed standby database disconnect from session;
--查看OKselect sequence#,applied,creator from v$archived_Log;可以接LOG文件了
----主库上切到最大可用模式
SQL> alter system set log_archive_dest_2='service=steve89 optional lgwr sync affirm valid_for=
(online_logfiles,primary_role) db_unique_name=steve89';
sq>alter database set standby database to maximize availability;
--测试主库上:
SQL> create user panhua identified by panhua;
SQL> alter system switch logfile;
--备库上steve89
--SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> select * from all_users;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
PANHUA 61 25-MAR-09
BI 60 11-MAY-08
PM 59 11-MAY-08
SH 58 11-MAY-08
SQL> alter database recover managed standby database disconnect from session;
----ok
----总结
SQL> alter database recover managed standby database finish不能随便用,finish相当于failover,执行后就不再送日志
恢复只能重建备库的控制文件或FLASHBACK finish前(select FLASHBACK_ON from v$database;要打开),或重建备库
289
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
