In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-30 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
First, the redo log damage 1.1 archive mode that is not in use, is not currently in the log corruption, database open mode.
Simulated damage:
SQL > archive log list
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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *
/ u01/app/oracle/oradata/DBdb/redo01.log / u01/app/oracle/oradata/DBdb/redo02.log
Toggle logs:
SQL > alter system switch logfile
System altered.
SQL > alter system switch logfile
System altered.
SQL > /
Viewing the alert log indicates an error
Errors in file / u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_arc3_2018.trc:
ORA-00313: 1)?
ORA-00312: 31:'/ u01 *
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: 31:'/ u01 *
ORA-27037:??
Daily error indicates that the status of the file cannot be obtained, and the data cannot be switched to log.
Solution:
1. Note that there is no need to restart the database, you just need to clear the log.
SQL > alter database clear unarchived logfile group 3
Database altered.
SQL > SQL >
SQL >! ls / u01qqapp.oracleUnixoradataUniverse DBdbUniple 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
1.2 Archive mode, not currently in log corruption, database shutdown mode.
Demo process:
-- check the log status first:
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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *
/ u01/app/oracle/oradata/DBdb/redo01.log / u01/app/oracle/oradata/DBdb/redo02.log / u01/app/oracle/oradata/DBdb/redo03.log
-- close the database
SQL > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL >
-- delete the log
SQL >! rm-rf / u01/app/oracle/oradata/DBdb/redo01.log
SQL >
Restart the database:
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 >
Report an error and check the log, as follows:
Errors in file / u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_lgwr_14158.trc:
ORA-00313:? 1?
ORA-00312:? 1? 1:'/ u01OnOnOnPlacleUniverse oradataPlacement DBDBDB redo01.log'
ORA-27037:?
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Solution: clear the log when the database starts, and then open the database:
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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *
/ u01/app/oracle/oradata/DBdb/redo01.log / u01/app/oracle/oradata/DBdb/redo02.log / u01/app/oracle/oradata/DBdb/redo03.log
SQL >
Note that if the deleted log is not archived, add a parameter alter database clear (unarchived) logfile group 1
1.3 Database open mode, non-archive mode, non-current log corruption
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 >
-- query:
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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *
/ u01/app/oracle/oradata/DBdb/redo01.log / u01/app/oracle/oradata/DBdb/redo02.log / u01/app/oracle/oradata/DBdb/redo03.log
SQL >
-- Delete the log and simulate the fault:
SQL >! rm-rf / u01/app/oracle/oradata/DBdb/redo03.log
-- switch logs:
SQL > alter system switch logfile
System altered.
SQL > alter system switch logfile
System altered.
SQL > /
System altered.
SQL > /
System altered.
Note:
Both the dml statement and the log switch were successful, and the database log did not report an error as follows:
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
The log information is as follows:
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
However, after the database is started, it is as follows:
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:'/ u01OnOnOnPlacleUniverse oradataandDBDBDB 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:
Solution: only need to clear the log group of corrupted log files.
SQL > alter database clear logfile group 3
Database altered.
SQL > alter database open
Database altered.
-- verify:
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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *
/ u01/app/oracle/oradata/DBdb/redo01.log / u01/app/oracle/oradata/DBdb/redo02.log / u01/app/oracle/oradata/DBdb/redo03.log
SQL >
1.4 non-archived mode, database shutdown, corrupted log files that are not in use
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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple 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 >
-- Delete logs and simulate faults
SQL >! ls / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *
/ u01/app/oracle/oradata/DBdb/redo01.log / u01/app/oracle/oradata/DBdb/redo03.log
SQL >
-- restart:
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 >
Log error:
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:'/ u01OnOnOnPlacleUniverse oradataandDBDBDB redo02.log'
ORA-27037:?
Linux-x86_64 Error: 2: No such file or directory
Solution: clear log group 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 >
-- verify:
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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *
/ u01/app/oracle/oradata/DBdb/redo01.log / u01/app/oracle/oradata/DBdb/redo02.log / u01/app/oracle/oradata/DBdb/redo03.log
SQL >
To sum up, for archive log corruption that is not currently in use, the archive mode requires the alter database clear unarchived command to empty the log group. Alter system clear log filegroups are required for non-archive mode.
Second, the redo log corruption 2.1archiving mode currently in use, the database open status and the log files currently in use are corrupted.
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.
-- check:
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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *
/ u01/app/oracle/oradata/DBdb/redo01.log / u01/app/oracle/oradata/DBdb/redo02.log / u01/app/oracle/oradata/DBdb/redo03.log
-- trigger checkpoint
SQL > alter system checkpoint
System altered.
-- switch log groups
SQL > alter system switch logfile
System altered.
SQL > /
System altered.
An error is reported in the backend log, as follows:
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 *
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 *
ORA-27037:??
Linux-x86_64 Error: 2: No such file or directory
Solution: because at this time, although the current log is in use, we can switch the log first and then perform the clear operation.
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 database clear unarchived logfile group 1
Database altered.
-- verify:
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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *
/ u01/app/oracle/oradata/DBdb/redo01.log / u01/app/oracle/oradata/DBdb/redo02.log / u01/app/oracle/oradata/DBdb/redo03.log
SQL >
2.2 Archive mode, database open status, log files currently in use are corrupted, and the database is shut down normally
-- query:
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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *
/ u01/app/oracle/oradata/DBdb/redo01.log / u01/app/oracle/oradata/DBdb/redo02.log / u01/app/oracle/oradata/DBdb/redo03.log
SQL >
-- Delete log group 2 to simulate the failure:
SQL >! rm-rf / u01/app/oracle/oradata/DBdb/redo02.log
SQL >! ls / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *
/ u01/app/oracle/oradata/DBdb/redo01.log / u01/app/oracle/oradata/DBdb/redo03.log
-- close the database and restart:
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
An error is reported in the log. The information is as follows:
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:
ORA-00313: 2 (1)?
ORA-00312:? 2? 1:'/ u01OnOnOnPlacleUniverse oradataandDBDBDB redo02.log'
ORA-27037:?
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Solution: just boot to the mount state, and then clear corrupts the log.
SQL > conn / as sysdba
Connected to an idle instance.
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 >
SQL > alter database clear logfile group 2
Alter database clear logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of instance DBdb (thread 1) needs to be archived
ORA-00312: online log 2 thread 1:'/ u01Accord oradata online log redo02.log'
SQL > alter database clear unarchived logfile group 2
Database altered.
SQL > alter database open
Database altered.
SQL >
-- verify:
SQL > alter system switch logfile
System altered.
SQL > /
System altered.
SQL > select * from v$log
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
- -
1 1 1009 52428800 512 1 NO CURRENT 4079417 25-JAN-18 2.8147E+14
2 1 1008 52428800 512 1 YES ACTIVE 4079414 25-JAN-18 4079417 25-JAN-18
3 1 1007 52428800 512 1 YES ACTIVE 4078867 25-JAN-18 4079414 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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *
/ u01/app/oracle/oradata/DBdb/redo01.log / u01/app/oracle/oradata/DBdb/redo02.log / u01/app/oracle/oradata/DBdb/redo03.log
SQL >
2.3 Archive mode, database open status, log files currently in use are corrupted, and the database is shut down abnormally
-- check:
SQL > select * from v$log
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
- -
1 1 1009 52428800 512 1 NO CURRENT 4079417 25-JAN-18 2.8147E+14
2 1 1008 52428800 512 1 YES ACTIVE 4079414 25-JAN-18 4079417 25-JAN-18
3 1 1007 52428800 512 1 YES ACTIVE 4078867 25-JAN-18 4079414 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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *
/ u01/app/oracle/oradata/DBdb/redo01.log / u01/app/oracle/oradata/DBdb/redo02.log / u01/app/oracle/oradata/DBdb/redo03.log
-- Delete log group 1 to simulate failure
SQL >! rm-rf / u01/app/oracle/oradata/DBdb/redo01.log
SQL >! ls / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *
/ u01/app/oracle/oradata/DBdb/redo02.log / u01/app/oracle/oradata/DBdb/redo03.log
-- simulated abnormal shutdown:
SQL > shutdown abort
ORACLE instance shut down.
SQL >
-- restart:
SQL > conn / as sysdba
Connected to an idle instance.
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-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:'/ u01According to an oracle or oradata, or DBDBDB, redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL >
An error is reported in the acquired log:
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Started redo scan
Errors in file / u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_15477.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:'/ u01According to an oracle or oradata, or DBDBDB, redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Aborting crash recovery due to error 313
Solution: at this time, we have two ways, one is to use backup for recovery, and the other is to use implicit parameters. Introduce the second type:
SQL > conn / as sysdba
Connected.
SQL >
SQL > startup mount
ORA-01081: cannot start already-running ORACLE-shut it down first
SQL >
SQL > select status from v$instance
STATUS
-
MOUNTED
SQL > select ksppinm, ksppstvl from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and (translate (ksppinm,'_','#') like'_ allow_resetlogs_corruption'
KSPPINM KSPPSTVL
_ allow_resetlogs_corruption FALSE
SQL >
This parameter resets the log file if the database is inconsistent.
SQL > alter system set "_ allow_resetlogs_corruption" = true scope=spfile
System altered.
SQL > shutdown immediate
ORA-01109: database not open
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 >
-- check the query and log:
SQL > select ksppinm, ksppstvl from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and (translate (ksppinm,'_','#') like'_ allow_resetlogs_corruption'
KSPPINM KSPPSTVL
_ allow_resetlogs_corruption TRUE
SQL >
SQL > select * from v$log
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
- -
1 1 1009 52428800 512 1 NO CURRENT 4079417 25-JAN-18 2.8147E+14
3 1 1007 52428800 512 1 YES ACTIVE 4078867 25-JAN-18 4079414 25-JAN-18
2 1 1008 52428800 512 1 YES ACTIVE 4079414 25-JAN-18 4079417 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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *
/ u01/app/oracle/oradata/DBdb/redo02.log / u01/app/oracle/oradata/DBdb/redo03.log
-try 1:
SQL > alter database clear unarchived logfile group 1
Alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance DBdb (thread 1)
ORA-00312: online log 1 thread 1:'/ u01According to an oracle or oradata, or DBDBDB, redo01.log'
-- try 2:
SQL > alter database open resetlogs
Alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
-- restore in resetlogs:
SQL > recover database until cancel
ORA-00279: change 4079417 generated at 01/25/2018 05:53:46 needed for thread 1
ORA-00289: suggestion: / u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_1009_%u_.arc
ORA-00280: change 4079417 for thread 1 is in sequence # 1009
Specify log: {= suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log'/ u01ActionActionoracleUniverse fastfastDBBUBUBUBGUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBG
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log'/ u01ActionActionoracleUniverse fastfastDBBUBUBUBGUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBG
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:'/ u01Accord oradata system 01.dbf'
-- Open the database in resetlogs mode:
SQL > alter database open resetlogs
Database altered.
SQL >
-- check:
SQL > select * from v$log
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
- -
1 1 1 52428800 512 1 NO CURRENT 4079418 25-JAN-18 2.8147E+14
2 1 0 52428800 512 1 YES UNUSED 00
3 1 0 52428800 512 1 YES UNUSED 00
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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *
/ u01/app/oracle/oradata/DBdb/redo01.log / u01/app/oracle/oradata/DBdb/redo02.log / u01/app/oracle/oradata/DBdb/redo03.log
Rebuild the instance and then use expdp and impdp to export the data into the database
SQL > create directory expdp as'/ opt/app/oracle/oradata'
Directory created.
Then export the data to rebuild the database and import the data.
Summary: the damage to the logs currently in use is generally repaired by backup, and if not, you can only use the second setting implicit parameter _ allow_resetlogs_corruption to recover.
Summary
-- query redo information
Col member for a50
Set lines 120
SELECT thread#
A.sequence#
A.group#
TO_CHAR (first_change#, '999999999999999999') "SCN"
A.status
MEMBER
FROM v$log a, v$logfile b
WHERE a.group# = B.GROUP#
ORDER BY a.sequence# DESC
1) inactive redo exception
Alter database clear unarchived logfile group 1
ALTER DATABASE drop logfile group 1
Alter database open
Alter database add logfile group 1 ('/ oracle/app/oracle/oradata/cus/redo01.log') size 200m reuse
2) current/active redo is abnormal and the database is shut down normally
Alter database clear unarchived logfile group 1
ALTER DATABASE drop logfile group 1
Recover database until cancel
Alterdatabaseopenresetlogs
3) current/active redo exception and database shutdown exception
Alter database clear unarchived logfile group 1
ALTER DATABASE drop logfile group 1
Recover database until cancel
If you cannot restore it, you can only set the _ allow_resetlogs_corruption parameter to skip the check.
Alter system set "_ allow_resetlogs_corruption" = true scope=spfile
Shutdown immediate
Startup mount
Alter database open resetlogs
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.