课程第32次
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
2.处理方法：
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; -------------------------------------------------------------10d实验：
用control10d-before.sql，会出现：
FILE_NAMEFILE_IDTABLESPACE_NAMEBYTESBLOCKSSTATUSRELATIVE_FNOAUTOEXTENSIBLEMAXBYTESMAXBLOCKSINCREMENT_BYUSER_BYTESUSER_BLOCKSONLINE_STATUS
1+DATA/orcl/datafile/example.265.10139609875EXAMPLE36306944044320AVAILABLE5YES3435972198441943028036202086444192ONLINE
2+DATA/orcl/datafile/undotbs1.258.10139608833UNDOTBS111010048013440AVAILABLE3YES34359721984419430264010905190413312ONLINE
3+DATA/orcl/datafile/sysaux.257.10139608832SYSAUX60817408074240AVAILABLE2YES343597219844194302128060712550474112ONLINE
4+DATA/orcl/datafile/system.256.10145042151SYSTEM79691776097280AVAILABLE1YES343597219844194302128079586918497152SYSTEM
5/u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING000044USERS AVAILABLE4 RECOVER
-------------------------------------------------------------补救措施：
$ . oraenv
ORACLE_SID = ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle
$ 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
$ . oraenv
ORACLE_SID = [+ASM] ? orcl
The Oracle base remains unchanged with value /u01/app/oracle
$ 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>
实验过程：
# su - oracle
$ 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_oracleTemplates
backup2 control11b.sql DocumentsPictures Videos
backupusers.rcv create_t04209_uname.sqlDownloadsPublic
control10d-after.sqldatapumpdir 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
$ 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.
$ 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
$ 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 KeySize Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
1876 256.00K SBT_TAPE 00:00:20 2019-07-27:11:29:30
BP Key: 1879 Status: AVAILABLECompressed: NOTag: 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.
$ 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>
以上是set2的错误和局限性：
补救：
SQL> alter database open resetlogs;
页:
[1]