56.数据库的自我诊断
[oracle@yuanzj.com:/home/oracle]$ rm /oradata/orcl11g/users01.dbf
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 7 10:47:47 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
sys@ORCL11G 2023-03-07 10:47:47> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oradata/orcl11g/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Elapsed: 00:00:00.04
sys@ORCL11G 2023-03-07 10:47:56> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Mar 7 10:48:07 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL11G (DBID=1235547844)
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
162 HIGH OPEN 2023-03-07 10:47:56 One or more non-system datafiles are missing
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
162 HIGH OPEN 2023-03-07 10:47:56 One or more non-system datafiles are missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /oradata/orcl11g/users01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 4
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/ora11g/diag/rdbms/orcl11g/orcl11g/hm/reco_4172161585.hm
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/ora11g/diag/rdbms/orcl11g/orcl11g/hm/reco_4172161585.hm
contents of repair script:
# restore and recover datafile
sql 'alter database datafile 4 offline';
restore datafile 4;
recover datafile 4;
sql 'alter database datafile 4 online';
Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script
sql statement: alter database datafile 4 offline
Starting restore at 2023-03-07 10:50:32
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /oradata/orcl11g/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/ora11g/fast_recovery_area/ORCL11G/backupset/2023_03_07/o1_mf_nnndf_TAG20230307T104606_l0f9ch1v_.bkp
channel ORA_DISK_1: piece handle=/u01/app/ora11g/fast_recovery_area/ORCL11G/backupset/2023_03_07/o1_mf_nnndf_TAG20230307T104606_l0f9ch1v_.bkp tag=TAG20230307T104606
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2023-03-07 10:50:33
Starting recover at 2023-03-07 10:50:33
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2023-03-07 10:50:33
sql statement: alter database datafile 4 online
repair failure complete
RMAN> quit
Recovery Manager complete.
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 7 10:51:56 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
sys@ORCL11G 2023-03-07 10:51:56> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
Elapsed: 00:00:00.02
sys@ORCL11G 2023-03-07 10:51:57> [oracle@yuanzj.com:/home/oracle]$ rm /oradata/orcl11g/undotbs01.dbf
[oracle@yuanzj.com:/home/oracle]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Mar 7 10:55:18 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL11G (DBID=1235547844)
RMAN> list failure;
using target database control file instead of recovery catalog
no failures found that match specification
RMAN> quit
Recovery Manager complete.
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 7 10:56:15 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
sys@ORCL11G 2023-03-07 10:56:15> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.12
sys@ORCL11G 2023-03-07 10:56:51> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.01
sys@ORCL11G 2023-03-07 10:57:10> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Mar 7 10:57:24 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL11G (DBID=1235547844)
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
162 HIGH OPEN 2023-03-07 10:57:15 One or more non-system datafiles are missing
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
162 HIGH OPEN 2023-03-07 10:57:15 One or more non-system datafiles are missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
1. If file /oradata/orcl11g/undotbs01.dbf was unintentionally renamed or moved, restore it
2. Automatic repairs may be available if you shutdown the database and restart it in mount mode
3. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
no automatic repair options available
RMAN> shutdown abort;
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 989858976 bytes
Database Buffers 603979776 bytes
Redo Buffers 7319552 bytes
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
162 HIGH OPEN 2023-03-07 10:57:15 One or more non-system datafiles are missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /oradata/orcl11g/undotbs01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 3
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/ora11g/diag/rdbms/orcl11g/orcl11g/hm/reco_276587347.hm
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/ora11g/diag/rdbms/orcl11g/orcl11g/hm/reco_276587347.hm
contents of repair script:
# restore and recover datafile
restore datafile 3;
recover datafile 3;
sql 'alter database datafile 3 online';
Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script
Starting restore at 2023-03-07 11:00:21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /oradata/orcl11g/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/ora11g/fast_recovery_area/ORCL11G/backupset/2023_03_07/o1_mf_nnndf_TAG20230307T104606_l0f9ch1v_.bkp
channel ORA_DISK_1: piece handle=/u01/app/ora11g/fast_recovery_area/ORCL11G/backupset/2023_03_07/o1_mf_nnndf_TAG20230307T104606_l0f9ch1v_.bkp tag=TAG20230307T104606
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2023-03-07 11:00:22
Starting recover at 2023-03-07 11:00:22
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2023-03-07 11:00:22
sql statement: alter database datafile 3 online
repair failure complete
Do you want to open the database (enter YES or NO)? YES
database opened
RMAN> 
scott@ORCL11G 2023-03-07 11:43:11> select distinct dbms_rowid.rowid_block_number(rowid) b# from emp;
B#
----------
151
Elapsed: 00:00:00.01
scott@ORCL11G 2023-03-07 11:43:19> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ dd if=/dev/zero of=/oradata/orcl11g/users01.dbf seek=151 bs=8192 count=1 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000174565 s, 46.9 MB/s
[oracle@yuanzj.com:/home/oracle]$ dbv /oradata/orcl11g/users01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Tue Mar 7 11:47:39 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
LRM-00108: invalid positional parameter value '/oradata/orcl11g/users01.dbf'
DBV-00001: Illegal command line syntax - parse error = (108)
[oracle@yuanzj.com:/home/oracle]$ 11g
[oracle@yuanzj.com:/home/oracle]$ dbv /oradata/orcl11g/users01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Tue Mar 7 11:47:45 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
LRM-00108: invalid positional parameter value '/oradata/orcl11g/users01.dbf'
DBV-00001: Illegal command line syntax - parse error = (108)
[oracle@yuanzj.com:/home/oracle]$ dbv file=/oradata/orcl11g/users01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Tue Mar 7 11:48:17 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /oradata/orcl11g/users01.dbf
Page 151 is marked corrupt
Corrupt block relative dba: 0x01000097 (file 4, block 151)
Completely zero block found during dbv:
DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 90
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 39
Total Pages Failing (Index): 0
Total Pages Processed (Other): 492
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 18
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 953578 (0.953578)
[oracle@yuanzj.com:/home/oracle]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Mar 7 11:51:00 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL11G (DBID=1235547844)
RMAN> backup validate database;
Starting backup at 2023-03-07 11:51:29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/orcl11g/system01.dbf
input datafile file number=00002 name=/oradata/orcl11g/sysaux01.dbf
input datafile file number=00005 name=/oradata/orcl11g/example01.dbf
input datafile file number=00003 name=/oradata/orcl11g/undotbs01.dbf
input datafile file number=00004 name=/oradata/orcl11g/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 15313 97295 1538100
File Name: /oradata/orcl11g/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 63526
Index 0 13240
Other 0 5201
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 20994 69199 1538098
File Name: /oradata/orcl11g/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 11508
Index 0 8028
Other 0 28590
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 30 14094 1538100
File Name: /oradata/orcl11g/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 14050
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 FAILED 0 18 667 953578
File Name: /oradata/orcl11g/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 90
Index 0 39
Other 1 493
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 31439 40083 978755
File Name: /oradata/orcl11g/example01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 4432
Index 0 1159
Other 0 3050
validate found one or more corrupt blocks
See trace file /u01/app/ora11g/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_30946.trc for details
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 594
Finished backup at 2023-03-07 11:51:37
RMAN> quit
Recovery Manager complete.
[oracle@yuanzj.com:/home/oracle]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Mar 7 11:55:07 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL11G (DBID=1235547844)
RMAN> recover datafile 4 block 151;
Starting recover at 2023-03-07 11:56:20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u01/app/ora11g/fast_recovery_area/ORCL11G/backupset/2023_03_07/o1_mf_nnndf_TAG20230307T104606_l0f9ch1v_.bkp
channel ORA_DISK_1: piece handle=/u01/app/ora11g/fast_recovery_area/ORCL11G/backupset/2023_03_07/o1_mf_nnndf_TAG20230307T104606_l0f9ch1v_.bkp tag=TAG20230307T104606
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2023-03-07 11:56:22
自动诊断资料档案库
[oracle@yuanzj.com:/home/oracle]$ adrci
ADRCI: Release 11.2.0.4.0 - Production on Tue Mar 7 12:06:42 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/ora11g"
adrci> help
HELP [topic]
Available Topics:
CREATE REPORT
ECHO
EXIT
HELP
HOST
IPS
PURGE
RUN
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL
There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list
adrci> SHOW ALERT
Choose the alert log from the following homes to view:
1: diag/tnslsnr/yuanzj/listener
2: diag/rdbms/orcl11g/orcl11g
Q: to quit
Please select option: 2
Output the results to file: /tmp/alert_33273_1397_orcl11g_1.ado
Please select option: q
adrci>
adrci> exit
