13518219792
SQL> archive log list;
创新互联长期为超过千家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为爱辉企业提供专业的成都做网站、成都网站设计、成都外贸网站建设，爱辉网站改版等技术服务。拥有十余年丰富建站经验和众多成功案例,为您定制开发。
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 951
Next log sequence to archive 953
Current log sequence 953
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------ ------------ ------------
1 1 952 52428800 512 1 YES INACTIVE 4059877 24-JAN-18 4064774 24-JAN-18
2 1 953 52428800 512 1 NO CURRENT 4064774 24-JAN-18 2.8147E+14
3 1 951 52428800 512 1 YES INACTIVE 4021213 24-JAN-18 4059877 24-JAN-18
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL>SQL> !rm -rf /u01/app/oracle/oradata/DBdb/redo03.log
SQL> !ls /u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log
切换日志：
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
查看alert日志提示错误
Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_arc3_2018.trc:
ORA-00313: ( 1)?
ORA-00312: 3 1: '/u01/app/oracle/oradata/DBdb/redo03.log'
ORA-27037:??
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Master archival failure: 313
Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_arc0_2012.trc:
ORA-00313: ( 1)?
ORA-00312: 3 1: '/u01/app/oracle/oradata/DBdb/redo03.log'
ORA-27037:??
日常出现错误，提示不能获得该文件状态，数据无法进行日志切换
解决办法：
1、注意不需要重启数据库，只需要把日志清除即可。
SQL> alter databaseclear unarchived logfile group 3;
Database altered.
SQL> SQL>
SQL> !ls /u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL> select member from v$logfile;
MEMBER
----------------------------------------------------------------
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
演示过程：
--先检查日志状态：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------ ------------ ------------
1 1 961 52428800 512 1 YES INACTIVE 4075623 25-JAN-18 4075626 25-JAN-18
2 1 962 52428800 512 1 NO CURRENT 4075626 25-JAN-18 2.8147E+14
3 1 960 52428800 512 1 YES INACTIVE 4075620 25-JAN-18 4075623 25-JAN-18
SQL> select member from v$logfile;
MEMBER
---------------------------------------------------------
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls /u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
--关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
--删除日志
SQL> !rm -rf /u01/app/oracle/oradata/DBdb/redo01.log
SQL>
重启数据库：
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 14178
Session ID: 1 Serial number: 5
SQL>
报错，检查日志，如下：
Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_lgwr_14158.trc:
ORA-00313: ??????? 1 (???? 1) ???
ORA-00312: ???? 1 ?? 1: '/u01/app/oracle/oradata/DBdb/redo01.log'
ORA-27037: ????????
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
解决：在数据库启动的时候清除日志，然后open数据库：
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL>
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database open;
Database altered.
SQL>
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------ ------------ ------------
1 1 0 52428800 512 1 YES UNUSED 4075623 25-JAN-18 4075626 25-JAN-18
2 1 962 52428800 512 1 NO CURRENT 4075626 25-JAN-18 2.8147E+14
3 1 960 52428800 512 1 YES INACTIVE 4075620 25-JAN-18 4075623 25-JAN-18
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls /u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL>
注意，如果删除的日志未归档则加一个参数alter database clear(unarchived) logfile group 1;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL>
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL>
--查询：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------ ------------ ------------
1 1 0 52428800 512 1 YES UNUSED 4075623 25-JAN-18 4075626 25-JAN-18
2 1 962 52428800 512 1 NO CURRENT 4075626 25-JAN-18 2.8147E+14
3 1 960 52428800 512 1 YES INACTIVE 4075620 25-JAN-18 4075623 25-JAN-18
SQL> select member from v$logfile;
MEMBER
----------------------------------------------------------------------------------
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls /u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL>
--删除日志，模拟故障：
SQL> !rm -rf /u01/app/oracle/oradata/DBdb/redo03.log
--切换日志：
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
注意：
在执行dml语句，以及切换日志都成功，数据库日志也没有报错如下：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 14645
Session ID: 1 Serial number: 5
日志信息如下：
Thu Jan 25 05:18:19 2018
Thread 1 advanced to log sequence 995 (LGWR switch)
Current log# 2 seq# 995 mem# 0: /u01/app/oracle/oradata/DBdb/redo02.log
Thread 1 advanced to log sequence 996 (LGWR switch)
Current log# 1 seq# 996 mem# 0: /u01/app/oracle/oradata/DBdb/redo01.log
Thu Jan 25 05:18:30 2018
Thread 1 advanced to log sequence 997 (LGWR switch)
Current log# 3 seq# 997 mem# 0: /u01/app/oracle/oradata/DBdb/redo03.log
Thu Jan 25 05:19:52 2018
Thread 1 advanced to log sequence 998 (LGWR switch)
Current log# 2 seq# 998 mem# 0: /u01/app/oracle/oradata/DBdb/redo02.log
Thread 1 advanced to log sequence 999 (LGWR switch)
Current log# 1 seq# 999 mem# 0: /u01/app/oracle/oradata/DBdb/redo01.log
但是数据库启动后，如下：
ALTER DATABASE OPEN
Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_lgwr_14625.trc:
ORA-00313: ??????? 3 (???? 1) ???
ORA-00312: ???? 3 ?? 1: '/u01/app/oracle/oradata/DBdb/redo03.log'
ORA-27037: ????????
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_lgwr_14625.trc:
解决：只需要把损坏的日志文件清除日志组即可。
SQL> alter database clear logfile group 3;
Database altered.
SQL> alter database open;
Database altered.
--验证：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------ ------------ ------------
1 1 999 52428800 512 1 NO CURRENT 4077012 25-JAN-18 2.8147E+14
2 1 998 52428800 512 1 NO INACTIVE 4077009 25-JAN-18 4077012 25-JAN-18
3 1 0 52428800 512 1 NO UNUSED 4076978 25-JAN-18 4077009 25-JAN-18
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls /u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL>
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------ ------------ ------------
1 1 1002 52428800 512 1 NO CURRENT 4077582 25-JAN-18 2.8147E+14
2 1 1001 52428800 512 1 NO INACTIVE 4077579 25-JAN-18 4077582 25-JAN-18
3 1 1000 52428800 512 1 NO INACTIVE 4077575 25-JAN-18 4077579 25-JAN-18
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls /u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> !rm -rf /u01/app/oracle/oradata/DBdb/redo02.log
SQL>
--删除日志，模拟故障
SQL> !ls /u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL>
--重启：
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 14823
Session ID: 1 Serial number: 5
SQL>
日志报错：
ALTER DATABASE OPEN
Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_lgwr_14803.trc:
ORA-00313: ??????? 2 (???? 1) ???
ORA-00312: ???? 2 ?? 1: '/u01/app/oracle/oradata/DBdb/redo02.log'
ORA-27037: ????????
Linux-x86_64 Error: 2: No such file or directory
解决方案：clear日志组2
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL>
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database open;
Database altered.
SQL>
--验证：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------ ------------ ------------
1 1 1002 52428800 512 1 NO CURRENT 4077582 25-JAN-18 2.8147E+14
2 1 0 52428800 512 1 NO UNUSED 4077579 25-JAN-18 4077582 25-JAN-18
3 1 1000 52428800 512 1 NO INACTIVE 4077575 25-JAN-18 4077579 25-JAN-18
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls /u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL>
总结，对于不是当前使用的归档日志损坏，归档模式需要使用alter database clear unarchived命令清空日志 组即可。对于非归档模式需要使用alter system clear日志文件组即可。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL>
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
--检查：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------ ------------ ------------
1 1 1002 52428800 512 1 NO CURRENT 4077582 25-JAN-18 2.8147E+14
2 1 0 52428800 512 1 YES UNUSED 4077579 25-JAN-18 4077582 25-JAN-18
3 1 1000 52428800 512 1 YES INACTIVE 4077575 25-JAN-18 4077579 25-JAN-18
SQL> select member from v$logfile;
MEMBER
-------------------------------------------------------------------------
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls /u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
--触发检查点
SQL> alter system checkpoint;
System altered.
--切换日志组
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
后台日志报错，如下：
Thu Jan 25 05:41:44 2018
Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_arc2_15008.trc:
ORA-00313: ( 1)?
ORA-00312: 1 1: '/u01/app/oracle/oradata/DBdb/redo01.log'
ORA-27037:??
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Master archival failure: 313
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance DBdb - Archival Error
ORA-00313: ( 1)?
ORA-00312: 1 1: '/u01/app/oracle/oradata/DBdb/redo01.log'
ORA-27037:??
Linux-x86_64 Error: 2: No such file or directory
解决：由于这个时候，虽然当前日志是正在被使用的，但是我们可以先进行切换日志之后，然后执行clear操作。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------ ------------ ------------
1 1 1002 52428800 512 1 NO INACTIVE 4077582 25-JAN-18 4078722 25-JAN-18
2 1 1003 52428800 512 1 NO INACTIVE 4078722 25-JAN-18 4078725 25-JAN-18
3 1 1004 52428800 512 1 NO CURRENT 4078725 25-JAN-18 2.8147E+14
SQL> alter databaseclear unarchived logfile group 1;
Database altered.
--验证：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------ ------------ ------------
1 1 1005 52428800 512 1 NO CURRENT 4078821 25-JAN-18 2.8147E+14
2 1 1003 52428800 512 1 YES INACTIVE 4078722 25-JAN-18 4078725 25-JAN-18
3 1 1004 52428800 512 1 YES ACTIVE 4078725 25-JAN-18 4078821 25-JAN-18
SQL> select member from v$logfile;
MEMBER
----------------------------------------------------------------------
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls /u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL>
--查询：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------ ------------ ------------
1 1 1005 52428800 512 1 YES ACTIVE 4078821 25-JAN-18 4078866 25-JAN-18
2 1 1006 52428800 512 1 NO CURRENT 4078866 25-JAN-18 2.8147E+14
3 1 1004 52428800 512 1 YES ACTIVE 4078725 25-JAN-18 4078821 25-JAN-18
SQL> select member from v$logfile;
MEMBER
-----------------------------------------------------------------------------
/u01/app/oracle/oradata/DBdb/redo03.log
/u01/app/oracle/oradata/DBdb/redo02.log
/u01/app/oracle/oradata/DBdb/redo01.log
SQL> !ls /u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo02.log /u01/app/oracle/oradata/DBdb/redo03.log
SQL>
--删除日志组2，模拟故障：
SQL> !rm -rf /u01/app/oracle/oradata/DBdb/redo02.log
SQL> !ls /u01/app/oracle/oradata/DBdb/redo*
/u01/app/oracle/oradata/DBdb/redo01.log /u01/app/oracle/oradata/DBdb/redo03.log
--关闭数据库，重启：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 15253
Session ID: 1 Serial number: 5
日志报错，信息如下：
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
Thu Jan 25 05:50:23 2018
ARC0 started with pid=20, OS id=15255
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_lgwr_15233.trc:
