|
|
inactive日志组损坏：
1. 日志
- Sat Jul 27 08:18:37 2019
- SMCO started with pid=34, OS id=5531
- Sat Jul 27 09:03:56 2019
- alter database clear logfile group 3
- Clearing online log 3 of thread 1 sequence number 18
- Errors in file /u01/app/oracle/diag/rdbms/rcat/rcat/trace/rcat_ora_9487.trc:
- ORA-00313: 无法打开日志组 3 (用于线程 1) 的成员
- ORA-00312: 联机日志 3 线程 1: '/u01/app/oracle/oradata/rcat/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/rcat/rcat/trace/rcat_ora_9487.trc:
- ORA-00313: 无法打开日志组 3 (用于线程 1) 的成员
- ORA-00312: 联机日志 3 线程 1: '/u01/app/oracle/oradata/rcat/redo03.log'
- ORA-27037: 无法获得文件状态
- Linux-x86_64 Error: 2: No such file or directory
- Additional information: 3
- Sat Jul 27 09:03:57 2019
- Errors in file /u01/app/oracle/diag/rdbms/rcat/rcat/trace/rcat_m000_9759.trc:
- ORA-00316: log 3 of thread 1, type 0 in header is not log file
- ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/rcat/redo03.log'
- Checker run found 2 new persistent data failures
- Completed: alter database clear logfile group 3
- select * from v$log;
- select * from v$logfile;
- alter database clear logfile group 3;
--------------------------active
- alter system switch logfile;
- select * from v$log;
- select * from v$logfile;
- select * from v_$archived_log a
- order by a."FIRST_CHANGE#" ;
- alter database clear logfile group 2;
- alter system checkpoint;
- alter database clear logfile group 2;
-----------------------------current
- select * from v$log;
- select * from v$logfile;
- alter database clear logfile group 3;
- alter system switch logfile;
- alter system checkpoint;
- alter database clear unarchived logfile group 3;
用control10d-before.sql，会出现：
| | FILE_NAME | FILE_ID | TABLESPACE_NAME | BYTES | BLOCKS | STATUS | RELATIVE_FNO | AUTOEXTENSIBLE | MAXBYTES | MAXBLOCKS | INCREMENT_BY | USER_BYTES | USER_BLOCKS | ONLINE_STATUS | | 1 | +DATA/orcl/datafile/example.265.1013960987 | 5 | EXAMPLE | 363069440 | 44320 | AVAILABLE | 5 | YES | 34359721984 | 4194302 | 80 | 362020864 | 44192 | ONLINE | | 2 | +DATA/orcl/datafile/undotbs1.258.1013960883 | 3 | UNDOTBS1 | 110100480 | 13440 | AVAILABLE | 3 | YES | 34359721984 | 4194302 | 640 | 109051904 | 13312 | ONLINE | | 3 | +DATA/orcl/datafile/sysaux.257.1013960883 | 2 | SYSAUX | 608174080 | 74240 | AVAILABLE | 2 | YES | 34359721984 | 4194302 | 1280 | 607125504 | 74112 | ONLINE | | 4 | +DATA/orcl/datafile/system.256.1014504215 | 1 | SYSTEM | 796917760 | 97280 | AVAILABLE | 1 | YES | 34359721984 | 4194302 | 1280 | 795869184 | 97152 | SYSTEM | | 5 | /u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00004 | 4 | USERS | | | AVAILABLE | 4 | | | | | | | RECOVER |
-------------------------------------------------------------补救措施：
- [oracle@station76 ~]$ . oraenv
- ORACLE_SID = [orcl] ? +ASM
- The Oracle base remains unchanged with value /u01/app/oracle
- [oracle@station76 ~]$ asmcmd
- ASMCMD> ls
- DATA/
- FRA/
- ASMCMD> cd data
- ASMCMD> ls
- ASM/
- ORCL/
- ASMCMD> cd orcl
- ASMCMD> ls
- CONTROLFILE/
- DATAFILE/
- ONLINELOG/
- PARAMETERFILE/
- TEMPFILE/
- spfileorcl.ora
- ASMCMD> cd DATAFILE/
- ASMCMD> ls
- EXAMPLE.265.1013960987
- SYSAUX.257.1013960883
- SYSTEM.256.1014504215
- UNDOTBS1.258.1013960883
- USERS.259.1014330935
- ASMCMD> pwd
- +data/orcl/DATAFILE
- ASMCMD> exit
- [oracle@station76 ~]$ . oraenv
- ORACLE_SID = [+ASM] ? orcl
- The Oracle base remains unchanged with value /u01/app/oracle
- [oracle@station76 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 27 10:53:58 2019
- Copyright (c) 1982, 2013, Oracle. All rights reserved.
- SQL> conn / as sysdba
- Connected.
- SQL> alter database rename file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00004' to '+data/orcl/DATAFILE/USERS.259.1014330935';
- Database altered.
- SQL> alter tablespace users online ;
- alter tablespace users online
- *
- ERROR at line 1:
- ORA-01113: file 4 needs media recovery
- ORA-01110: data file 4: '+DATA/orcl/datafile/users.259.1014330935'
- SQL> recover datafile 4;
- ORA-00279: change 1349300 generated at 07/27/2019 10:31:51 needed for thread 1
- ORA-00289: suggestion :
- +FRA/orcl/archivelog/2019_07_27/thread_1_seq_111.286.1014720181
- ORA-00280: change 1349300 for thread 1 is in sequence #111
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- auto
- Log applied.
- Media recovery complete.
- SQL> alter tablespace users online ;
- Tablespace altered.
- SQL> select * from hr.t15863
- 2 ;
- A
- ----------
- 1
- 2
- 3
- 4
- 5
- SQL>
Oracle给的set 2 脚本只适用于正常关机（漏掉了until cancel）：
实验背景：
ASMCMD> cd orcl
ASMCMD> ls
ARCHIVELOG/
CHANGETRACKING/
CONTROLFILE/
FLASHBACK/
ASMCMD> cd ARCHIVELOG/
ASMCMD> ls
2019_07_27/
ASMCMD> cd 2019_07_27/
ASMCMD> ls
thread_1_seq_107.292.1014719509
thread_1_seq_108.290.1014719511
thread_1_seq_109.289.1014719511
thread_1_seq_110.288.1014719511
thread_1_seq_111.287.1014719515
thread_1_seq_116.285.1014723119
thread_1_seq_117.286.1014723119
thread_1_seq_118.291.1014723121
thread_1_seq_119.297.1014723125
thread_1_seq_120.300.1014723127
thread_1_seq_121.295.1014723131
ASMCMD>
[root@station76 real]# su - oracle
[oracle@station76 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 27 11:38:21 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1553305600 bytes
Fixed Size 2253544 bytes
Variable Size 956304664 bytes
Database Buffers 587202560 bytes
Redo Buffers 7544832 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> !ls
backup1 control10d-before.sql Desktop oradiag_oracle Templates
backup2 control11b.sql Documents Pictures Videos
backupusers.rcv create_t04209_uname.sql Downloads Public
control10d-after.sql datapumpdir Music RMasmcmd.sh
SQL> @control11b.sql
Control file created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@station76 ~]$ rman target / catalog u76/oracle_4U@rcat
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jul 27 11:43:32 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1541741703, not open)
connected to recovery catalog database
RMAN> restore database;
Starting restore at 2019-07-27:11:43:39
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=25 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=26 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Secure Backup
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=29 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=30 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=31 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=32 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=33 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=34 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=35 device type=DISK
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00002 to +DATA/orcl/datafile/sysaux.257.1013960883
channel ORA_SBT_TAPE_1: restoring datafile 00003 to +DATA/orcl/datafile/undotbs1.258.1013960883
channel ORA_SBT_TAPE_1: restoring datafile 00004 to +DATA/orcl/datafile/users.259.1014330935
channel ORA_SBT_TAPE_1: reading from backup piece 04u7msab_1_1
channel ORA_SBT_TAPE_2: starting datafile backup set restore
channel ORA_SBT_TAPE_2: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_2: restoring datafile 00001 to +DATA/orcl/datafile/system.256.1014504215
channel ORA_SBT_TAPE_2: restoring datafile 00005 to +DATA/orcl/datafile/example.265.1013960987
channel ORA_SBT_TAPE_2: reading from backup piece 03u7msab_1_1
channel ORA_SBT_TAPE_1: piece handle=04u7msab_1_1 tag=1T-WHOLE-INCR0
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:06
channel ORA_SBT_TAPE_2: piece handle=03u7msab_1_1 tag=1T-WHOLE-INCR0
channel ORA_SBT_TAPE_2: restored backup piece 1
channel ORA_SBT_TAPE_2: restore complete, elapsed time: 00:01:35
Finished restore at 2019-07-27:11:45:38
RMAN> exit
Recovery Manager complete.
[oracle@station76 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 27 11:45:52 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> recover database using backup controlfile;
ORA-00279: change 1374973 generated at 07/27/2019 11:28:11 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 1374973 for thread 1 is in sequence #115
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '+FRA'
ORA-17503: ksfdopn:2 Failed to open file +FRA
ORA-15045: ASM file name '+FRA' is not in reference form
ORA-00308: cannot open archived log '+FRA'
ORA-17503: ksfdopn:2 Failed to open file +FRA
ORA-15045: ASM file name '+FRA' is not in reference form
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@station76 ~]$ rman target / catalog u76/oracle_4U@rcat
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jul 27 11:48:03 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1541741703, not open)
connected to recovery catalog database
RMAN> list backup of archivelog sequence 115;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
1876 256.00K SBT_TAPE 00:00:20 2019-07-27:11:29:30
BP Key: 1879 Status: AVAILABLE Compressed: NO Tag: 1T-WHOLE-INCR0
Handle: 05u7msc6_1_1 Media: station76-000025
List of Archived Logs in backup set 1876
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 115 1374894 2019-07-27:11:27:13 1375014 2019-07-27:11:29:07
RMAN> restore archivelog sequence 115;
Starting restore at 2019-07-27:11:48:24
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=27 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=29 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Secure Backup
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=34 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=32 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=31 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=30 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=28 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=1 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=21 device type=DISK
channel ORA_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=115
channel ORA_SBT_TAPE_1: reading from backup piece 05u7msc6_1_1
channel ORA_SBT_TAPE_1: piece handle=05u7msc6_1_1 tag=1T-WHOLE-INCR0
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:35
Finished restore at 2019-07-27:11:49:17
RMAN> exit
Recovery Manager complete.
[oracle@station76 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 27 11:49:25 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
\Connected.
SQL>recover database using backup controlfile;
ORA-00279: change 1374973 generated at 07/27/2019 11:28:11 needed for thread 1
ORA-00289: suggestion :
+FRA/orcl/archivelog/2019_07_27/thread_1_seq_115.258.1014724145
ORA-00280: change 1374973 for thread 1 is in sequence #115
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1375014 generated at 07/27/2019 11:29:07 needed for thread 1
ORA-00289: suggestion :
+FRA/orcl/archivelog/2019_07_27/thread_1_seq_116.285.1014723119
ORA-00280: change 1375014 for thread 1 is in sequence #116
ORA-00278: log file
'+FRA/orcl/archivelog/2019_07_27/thread_1_seq_115.258.1014724145' no longer
needed for this recovery
ORA-00279: change 1375284 generated at 07/27/2019 11:31:58 needed for thread 1
ORA-00289: suggestion :
+FRA/orcl/archivelog/2019_07_27/thread_1_seq_117.286.1014723119
ORA-00280: change 1375284 for thread 1 is in sequence #117
ORA-00278: log file
'+FRA/orcl/archivelog/2019_07_27/thread_1_seq_116.285.1014723119' no longer
needed for this recovery
ORA-00279: change 1375296 generated at 07/27/2019 11:31:58 needed for thread 1
ORA-00289: suggestion :
+FRA/orcl/archivelog/2019_07_27/thread_1_seq_118.291.1014723121
ORA-00280: change 1375296 for thread 1 is in sequence #118
ORA-00278: log file
'+FRA/orcl/archivelog/2019_07_27/thread_1_seq_117.286.1014723119' no longer
needed for this recovery
ORA-00279: change 1375301 generated at 07/27/2019 11:32:01 needed for thread 1
ORA-00289: suggestion :
+FRA/orcl/archivelog/2019_07_27/thread_1_seq_119.297.1014723125
ORA-00280: change 1375301 for thread 1 is in sequence #119
ORA-00278: log file
'+FRA/orcl/archivelog/2019_07_27/thread_1_seq_118.291.1014723121' no longer
needed for this recovery
ORA-00279: change 1375309 generated at 07/27/2019 11:32:04 needed for thread 1
ORA-00289: suggestion :
+FRA/orcl/archivelog/2019_07_27/thread_1_seq_120.300.1014723127
ORA-00280: change 1375309 for thread 1 is in sequence #120
ORA-00278: log file
'+FRA/orcl/archivelog/2019_07_27/thread_1_seq_119.297.1014723125' no longer
needed for this recovery
ORA-00279: change 1375314 generated at 07/27/2019 11:32:07 needed for thread 1
ORA-00289: suggestion :
+FRA/orcl/archivelog/2019_07_27/thread_1_seq_121.295.1014723131
ORA-00280: change 1375314 for thread 1 is in sequence #121
ORA-00278: log file
'+FRA/orcl/archivelog/2019_07_27/thread_1_seq_120.300.1014723127' no longer
needed for this recovery
ORA-00279: change 1375320 generated at 07/27/2019 11:32:10 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 1375320 for thread 1 is in sequence #122
ORA-00278: log file
'+FRA/orcl/archivelog/2019_07_27/thread_1_seq_121.295.1014723131' no longer
needed for this recovery
ORA-00308: cannot open archived log '+FRA'
ORA-17503: ksfdopn:2 Failed to open file +FRA
ORA-15045: ASM file name '+FRA' is not in reference form
SQL> recover database using backup controlfile;
ORA-00279: change 1375320 generated at 07/27/2019 11:32:10 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 1375320 for thread 1 is in sequence #122
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '+DATA/orcl/datafile/system.256.1014504215'
SQL>
补救：
- SQL> alter database open resetlogs;
|
|
发表于 2019-7-27 08:41:38
