53.解析数据还原恢复的流程
1.分析文件丢失的原因以及临时表空间丢失故障处理
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 6 08:33:29 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-06 08:33:29> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/oradata/orcl10g/temp01.dbf
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 08:34:47> select distinct temporary_tablespace from dba_users;
TEMPORARY_TABLESPACE
------------------------------
TEMP
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 08:35:00> select file_name from dba_temp_files where tablespace_name = 'TEMP';
FILE_NAME
------------------------------------------------------------------------------------------------------
/oradata/orcl10g/temp01.dbf
Elapsed: 00:00:00.01
--临时表空间丢失
sys@ORCL10G 2023-03-06 08:42:19> select * from temp01 order by 1,2,3,4,5,6,7,8,9,10;
select * from temp01 order by 1,2,3,4,5,6,7,8,9,10
*
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/oradata/orcl10g/temp01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
sys@ORCL10G 2023-03-06 08:48:39> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/oradata/orcl10g/temp01.dbf
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 08:49:20> select distinct temporary_tablespace from dba_users;
TEMPORARY_TABLESPACE
------------------------------
TEMPOLD
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 08:49:53> select file_name from dba_temp_files where tablespace_name = 'TEMP';
select file_name from dba_temp_files where tablespace_name = 'TEMP'
*
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/oradata/orcl10g/temp01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 08:50:24> create temporary tablespace temp tempfile '/oradata/orcl10g/temp.dbf' size 10m autoextend on uniform size 1m;
Tablespace created.
Elapsed: 00:00:00.04
sys@ORCL10G 2023-03-06 08:50:54> alter database default temporary tablespace temp;
Database altered.
Elapsed: 00:00:00.02
sys@ORCL10G 2023-03-06 08:51:59> drop tablespace tempold;
Tablespace dropped.
Elapsed: 00:00:00.09
--估算临时表空间大小
sys@ORCL10G 2023-03-06 08:55:08> select * from temp01 order by 1,2,3,4,5,6,7,8,9,10;
1835008 rows selected.
Elapsed: 00:00:16.61
Execution Plan
----------------------------------------------------------
Plan hash value: 3728717109
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1868K| 3852M| | 850K (1)| 02:50:05 |
| 1 | SORT ORDER BY | | 1868K| 3852M| 4865M| 850K (1)| 02:50:05 |
| 2 | TABLE ACCESS FULL| TEMP01 | 1868K| 3852M| | 6108 (2)| 00:01:14 |
-------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
216 recursive calls
9 db block gets
27450 consistent gets
27570 physical reads
0 redo size
33399860 bytes sent via SQL*Net to client
1346155 bytes received via SQL*Net from client
122335 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
1835008 rows processed
--回收表空间（Oracle11g+版本数据库）
sys@ORCL11G 2023-03-06 09:00:57> alter tablespace temp shrink space keep 2m;
Tablespace altered.
Elapsed: 00:00:00.05
--Oracle12c+版本数据库增加临时表空间命令
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/orcl/temp01.dbf'
SIZE 34603008 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = PDB$SEED;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/orcl/pdbseed/temp012023-02-13_13-01-21-567-PM.dbf'
SIZE 67108864 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = ORCLPDB01;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/orcl/orclpdb01/temp01.dbf'
SIZE 67108864 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = CDB$ROOT;
2.数据库未关闭redo损坏丢失
--数据库未关闭
--redo是inactive状态
sys@ORCL10G 2023-03-06 09:22:26> select l.group#, lf.member, l.status from v$log l, v$logfile lf where l.group# = lf.group# order by 1;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
1 /oradata/orcl10g/redo01.log INACTIVE
2 /oradata/orcl10g/redo02.log INACTIVE
3 /oradata/orcl10g/redo03.log CURRENT
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 09:22:36> ho rm /oradata/orcl10g/redo01.log
sys@ORCL10G 2023-03-06 09:23:29> ho ls -lh /oradata/orcl10g/redo01.log
ls: cannot access /oradata/orcl10g/redo01.log: No such file or directory
sys@ORCL10G 2023-03-06 09:23:37> alter database clear logfile group 1;
Database altered.
Elapsed: 00:00:00.21
sys@ORCL10G 2023-03-06 09:24:03> ho ls -lh /oradata/orcl10g/redo01.log
-rw-r----- 1 oracle oinstall 51M Mar 6 09:24 /oradata/orcl10g/redo01.log
sys@ORCL10G 2023-03-06 09:25:18> alter system switch logfile;
System altered.
Elapsed: 00:00:00.00
--redo是active状态
sys@ORCL10G 2023-03-06 09:25:32> select l.group#, lf.member, l.status from v$log l, v$logfile lf where l.group# = lf.group# order by 1;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
1 /oradata/orcl10g/redo01.log CURRENT
2 /oradata/orcl10g/redo02.log INACTIVE
3 /oradata/orcl10g/redo03.log ACTIVE
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 09:25:33> ho rm /oradata/orcl10g/redo03.log
sys@ORCL10G 2023-03-06 09:25:55> ho ls -lh /oradata/orcl10g/redo03.log
ls: cannot access /oradata/orcl10g/redo03.log: No such file or directory
sys@ORCL10G 2023-03-06 09:26:17> alter system checkpoint;
System altered.
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 09:26:28> select l.group#, lf.member, l.status from v$log l, v$logfile lf where l.group# = lf.group# order by 1;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
1 /oradata/orcl10g/redo01.log CURRENT
2 /oradata/orcl10g/redo02.log INACTIVE
3 /oradata/orcl10g/redo03.log INACTIVE
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 09:26:31> alter database clear logfile group 3;
Database altered.
Elapsed: 00:00:00.19
sys@ORCL10G 2023-03-06 09:26:59> alter database clear unarchived logfile group 3;
Database altered.
Elapsed: 00:00:00.16
sys@ORCL10G 2023-03-06 09:27:05> ho ls -lh /oradata/orcl10g/redo03.log
-rw-r----- 1 oracle oinstall 51M Mar 6 09:26 /oradata/orcl10g/redo03.log
--redo是CURRENT状态
sys@ORCL10G 2023-03-06 09:28:55> select l.group#, lf.member, l.status from v$log l, v$logfile lf where l.group# = lf.group# order by 1;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
1 /oradata/orcl10g/redo01.log CURRENT
2 /oradata/orcl10g/redo02.log INACTIVE
3 /oradata/orcl10g/redo03.log UNUSED
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 09:28:57> ho rm /oradata/orcl10g/redo01.log
sys@ORCL10G 2023-03-06 09:29:12> ho ls -lh /oradata/orcl10g/redo01.log
ls: cannot access /oradata/orcl10g/redo01.log: No such file or directory
sys@ORCL10G 2023-03-06 09:29:21> select l.group#, lf.member, l.status from v$log l, v$logfile lf where l.group# = lf.group# order by 1;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
1 /oradata/orcl10g/redo01.log CURRENT
2 /oradata/orcl10g/redo02.log INACTIVE
3 /oradata/orcl10g/redo03.log UNUSED
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 09:29:25> alter system switch logfile;
System altered.
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 09:30:00> select l.group#, lf.member, l.status from v$log l, v$logfile lf where l.group# = lf.group# order by 1;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
1 /oradata/orcl10g/redo01.log ACTIVE
2 /oradata/orcl10g/redo02.log INACTIVE
3 /oradata/orcl10g/redo03.log CURRENT
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 09:30:02> alter system checkpoint;
System altered.
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 09:30:17> select l.group#, lf.member, l.status from v$log l, v$logfile lf where l.group# = lf.group# order by 1;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
1 /oradata/orcl10g/redo01.log INACTIVE
2 /oradata/orcl10g/redo02.log INACTIVE
3 /oradata/orcl10g/redo03.log CURRENT
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 09:30:19> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance orcl10g (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/oradata/orcl10g/redo01.log'
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 09:30:45> alter database clear unarchived logfile group 1;
Database altered.
Elapsed: 00:00:00.20
sys@ORCL10G 2023-03-06 09:30:59> ho ls -lh /oradata/orcl10g/redo01.log
-rw-r----- 1 oracle oinstall 51M Mar 6 09:30 /oradata/orcl10g/redo01.log
sys@ORCL10G 2023-03-06 09:31:12> alter system switch logfile;
System altered.
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 09:31:16>
sys@ORCL10G 2023-03-06 09:31:17> alter system switch logfile;
System altered.
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 09:31:18> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
Elapsed: 00:00:32.49
sys@ORCL10G 2023-03-06 09:31:52> sys@ORCL10G 2023-03-06 09:31:52>
sys@ORCL10G 2023-03-06 09:31:54>
sys@ORCL10G 2023-03-06 09:31:55>
sys@ORCL10G 2023-03-06 09:31:56> select l.group#, lf.member, l.status from v$log l, v$logfile lf where l.group# = lf.group# order by 1;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
1 /oradata/orcl10g/redo01.log ACTIVE
2 /oradata/orcl10g/redo02.log CURRENT
3 /oradata/orcl10g/redo03.log INACTIVE
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 09:32:00> alter system archive log all;
alter system archive log all
*
ERROR at line 1:
ORA-16038: log 3 sequence# 38 cannot be archived
ORA-00312: online log 3 thread 1: '/oradata/orcl10g/redo03.log'
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 09:32:15> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance orcl10g (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/oradata/orcl10g/redo03.log'
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 09:32:35> alter database clear unarchived logfile group 3;
Database altered.
Elapsed: 00:00:00.18
sys@ORCL10G 2023-03-06 09:32:43> alter system switch logfile;
System altered.
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 09:32:50> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
Elapsed: 00:00:10.39
sys@ORCL10G 2023-03-06 09:33:02> select l.group#, lf.member, l.status from v$log l, v$logfile lf where l.group# = lf.group# order by 1;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- ----------------
1 /oradata/orcl10g/redo01.log INACTIVE
2 /oradata/orcl10g/redo02.log ACTIVE
3 /oradata/orcl10g/redo03.log CURRENT
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 09:33:10> alter system archive log all;
alter system archive log all
*
ERROR at line 1:
ORA-16038: log 1 sequence# 39 cannot be archived
ORA-00312: online log 1 thread 1: '/oradata/orcl10g/redo01.log'
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 09:33:31> alter database clear unarchived logfile group 1;
Database altered.
Elapsed: 00:00:00.18
sys@ORCL10G 2023-03-06 09:33:37> alter system switch logfile;
System altered.
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 09:33:41> alter system switch logfile;
System altered.
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 09:33:43> alter system switch logfile;
System altered.
Elapsed: 00:00:59.59
sys@ORCL10G 2023-03-06 09:34:43> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
Elapsed: 00:00:10.66
sys@ORCL10G 2023-03-06 09:34:59>
sys@ORCL10G 2023-03-06 09:35:00>
sys@ORCL10G 2023-03-06 09:35:00> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORCL10G 2023-03-06 09:36:21> startup
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
Database mounted.
ORA-00314: log 1 of thread 1, expected sequence# doesn't match
ORA-00312: online log 1 thread 1: '/oradata/orcl10g/redo01.log'
sys@ORCL10G 2023-03-06 09:36:34> recover database using backup controlfile;
ORA-00279: change 984259 generated at 03/06/2023 09:35:09 needed for thread 1
ORA-00289: suggestion : /oradata/orcl10g/archivelog/1_44_1128661656.dbf
ORA-00280: change 984259 for thread 1 is in sequence #44
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
sys@ORCL10G 2023-03-06 09:37:26> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery if it was restored from backup, or END BACKUP if it was not
ORA-01110: data file 1: '/oradata/orcl10g/system01.dbf'
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 09:37:52> create pfile='/home/oracle/orcl10g.sql' from spfile;
File created.
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 09:38:26> shu immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
sys@ORCL10G 2023-03-06 09:38:56> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ ls
oradiag_oracle orcl10g.sql soft
-添加隐含参数
[oracle@yuanzj.com:/home/oracle]$ cat orcl10g.sql
orcl10g.__db_cache_size=855638016
orcl10g.__java_pool_size=16777216
orcl10g.__large_pool_size=16777216
orcl10g.__shared_pool_size=268435456
orcl10g.__streams_pool_size=0
*.audit_file_dest='/u01/app/ora10g/admin/orcl10g/adump'
*.background_dump_dest='/u01/app/ora10g/admin/orcl10g/bdump'
*.compatible='10.2.0.5.0'
*.control_files='/oradata/orcl10g/control01.ctl','/oradata/orcl10g/control02.ctl','/oradata/orcl10g/control03.ctl'
*.core_dump_dest='/u01/app/ora10g/admin/orcl10g/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl10g'
*.db_recovery_file_dest='/u01/app/ora10g/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl10gXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/oradata/orcl10g/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=389021696
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1167065088
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/ora10g/admin/orcl10g/udump'
*._allow_resetlogs_corruption=true
[oracle@yuanzj.com:/home/oracle]$ vi orcl10g.sql
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 6 09:40:07 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
idle 2023-03-06 09:40:07> startup pfile='/home/oracle/orcl10g.sql' mount
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
Database mounted.
idle 2023-03-06 09:40:42> alter database resetlogs;
alter database resetlogs
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
Elapsed: 00:00:00.00
idle 2023-03-06 09:40:55> alter database open resetlogs;
Database altered.
Elapsed: 00:00:00.89
idle 2023-03-06 09:41:50> col member for a45;
idle 2023-03-06 09:42:05> r
1* select l.group#, lf.member, l.status from v$log l, v$logfile lf where l.group# = lf.group# order by 1
GROUP# MEMBER STATUS
---------- --------------------------------------------- ----------------
1 /oradata/orcl10g/redo01.log CURRENT
2 /oradata/orcl10g/redo02.log UNUSED
3 /oradata/orcl10g/redo03.log UNUSED
Elapsed: 00:00:00.01
--重启数据库
idle 2023-03-06 09:42:06> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
idle 2023-03-06 09:43:46> startup
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
Database mounted.
Database opened.
idle 2023-03-06 09:43:53> alter system switch logfile;
System altered.
Elapsed: 00:00:00.00
idle 2023-03-06 09:44:16> alter system switch logfile;
System altered.
Elapsed: 00:00:00.00
idle 2023-03-06 09:44:18> alter system switch logfile;
System altered.
Elapsed: 00:00:02.89
idle 2023-03-06 09:44:22> select l.group#, lf.member, l.status from v$log l, v$logfile lf where l.group# = lf.group# order by 1;
GROUP# MEMBER STATUS
---------- --------------------------------------------- ----------------
1 /oradata/orcl10g/redo01.log CURRENT
2 /oradata/orcl10g/redo02.log INACTIVE
3 /oradata/orcl10g/redo03.log INACTIVE
Elapsed: 00:00:00.003.索引丢失的解决方案
sys@ORCL10G 2023-03-06 10:24:05> select TABLESPACE_NAME,FILE_NAME,STATUS from dba_data_files;
TABLESPACE_NAME FILE_NAME STATUS
------------------------------ --------------------------------------------- ---------
USERS /oradata/orcl10g/users01.dbf AVAILABLE
SYSAUX /oradata/orcl10g/sysaux01.dbf AVAILABLE
UNDOTBS1 /oradata/orcl10g/undotbs01.dbf AVAILABLE
SYSTEM /oradata/orcl10g/system01.dbf AVAILABLE
EXAMPLE /oradata/orcl10g/example01.dbf AVAILABLE
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 10:24:09> create tablespace indx datafile '/oradata/orcl10g/indx01.dbf' size 1024M autoextend on next 512m maxsize 30720M;
Tablespace created.
Elapsed: 00:00:04.50
sys@ORCL10G 2023-03-06 10:24:44> select TABLESPACE_NAME,FILE_NAME,STATUS from dba_data_files;
TABLESPACE_NAME FILE_NAME STATUS
------------------------------ --------------------------------------------- ---------
USERS /oradata/orcl10g/users01.dbf AVAILABLE
SYSAUX /oradata/orcl10g/sysaux01.dbf AVAILABLE
UNDOTBS1 /oradata/orcl10g/undotbs01.dbf AVAILABLE
SYSTEM /oradata/orcl10g/system01.dbf AVAILABLE
EXAMPLE /oradata/orcl10g/example01.dbf AVAILABLE
INDX /oradata/orcl10g/indx01.dbf AVAILABLE
6 rows selected.
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 10:26:11>
sys@ORCL10G 2023-03-06 10:26:27> create index scott.emp_ename on scott.emp(ename) tablespace indx;
Index created.
Elapsed: 00:00:00.01
scott@ORCL10G 2023-03-06 10:29:12> create table emp01 as select * from emp;
Table created.
Elapsed: 00:00:00.01
scott@ORCL10G 2023-03-06 10:29:37> truncate table emp;
Table truncated.
Elapsed: 00:00:00.04
scott@ORCL10G 2023-03-06 10:30:18> insert into emp select * from emp01;
14 rows created.
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 10:34:36> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES from DBA_SEGMENTS where SEGMENT_NAME = 'EMP_ENAME';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES
------------------------------ ------------------ ------------------------------ ----------
EMP_ENAME INDEX INDX 65536
Elapsed: 00:00:00.02
sys@ORCL10G 2023-03-06 10:27:20> ho rm /oradata/orcl10g/indx01.dbf
sys@ORCL10G 2023-03-06 10:37:21> ho ls -lh /oradata/orcl10g/indx01.dbf
ls: cannot access /oradata/orcl10g/indx01.dbf: No such file or directory
--登录scott插入数据
scott@ORCL10G 2023-03-06 10:40:35> insert into emp(empno,ename) values(8000,'YUANZJ');
insert into emp(empno,ename) values(8000,'YUANZJ')
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/oradata/orcl10g/indx01.dbf'
--登录sys用户
sys@ORCL10G 2023-03-06 10:51:37> select OWNER,index_name,TABLESPACE_NAME,status from dba_indexes where table_name = 'EMP';
OWNER INDEX_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
SCOTT PK_EMP USERS VALID
SCOTT EMP_ENAME INDX VALID
sys@ORCL10G 2023-03-06 10:47:59> select dbms_metadata.get_ddl('INDEX','EMP_ENAME','SCOTT') from dual;
DBMS_METADATA.GET_DDL('INDEX','EMP_ENAME','SCOTT')
--------------------------------------------------------------------------------
CREATE INDEX "SCOTT"."EMP_ENAME" ON "SCOTT"."EMP" ("ENAME
")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT
S 2147483645
PCTIN
Elapsed: 00:00:00.03
sys@ORCL10G 2023-03-06 10:50:10> drop index scott.EMP_ENAME;
Index dropped.
Elapsed: 00:00:00.04
sys@ORCL10G 2023-03-06 10:50:16> CREATE INDEX "SCOTT"."EMP_ENAME" ON "SCOTT"."EMP"("ENAME") tablespace users;
Index created.
Elapsed: 00:00:00.02
sys@ORCL10G 2023-03-06 10:51:30> drop tablespace indx including contents and datafiles;
Tablespace dropped.
Elapsed: 00:00:00.274.密码文件丢失丢失解决方案
##重建密码文件，不区分大小写
orapwd file=$ORACLE_HOME/dbs/orapworcl10g password=Oracle_4U force=y ignorecase=false5.只读表空间的恢复
[oracle@yuanzj.com:/oradata/orcl10g]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 6 11:13:13 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-06 11:13:13>
sys@ORCL10G 2023-03-06 11:13:13>
sys@ORCL10G 2023-03-06 11:13:13> alter tablespace example read only;
Tablespace altered.
Elapsed: 00:00:00.03
sys@ORCL10G 2023-03-06 11:13:31> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@yuanzj.com:/oradata/orcl10g]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 6 11:13:47 2023
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL10G (DBID=1313046744)
RMAN> backup database;
Starting backup at 2023-03-06 11:13:52
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata/orcl10g/system01.dbf
input datafile fno=00003 name=/oradata/orcl10g/sysaux01.dbf
input datafile fno=00005 name=/oradata/orcl10g/example01.dbf
input datafile fno=00002 name=/oradata/orcl10g/undotbs01.dbf
input datafile fno=00004 name=/oradata/orcl10g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2023-03-06 11:13:52
channel ORA_DISK_1: finished piece 1 at 2023-03-06 11:14:07
piece handle=/u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_nnndf_TAG20230306T111352_l0bpmjvm_.bkp tag=TAG20230306T111352 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2023-03-06 11:14:09
channel ORA_DISK_1: finished piece 1 at 2023-03-06 11:14:10
piece handle=/u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_ncsnf_TAG20230306T111352_l0bpn118_.bkp tag=TAG20230306T111352 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2023-03-06 11:14:10
RMAN> quit
Recovery Manager complete.
[oracle@yuanzj.com:/oradata/orcl10g]$ sas
[oracle@yuanzj.com:/oradata/orcl10g]$ rm /oradata/orcl10g/example01.dbf
[oracle@yuanzj.com:/oradata/orcl10g]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 6 11:14:41 2023
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL10G (DBID=1313046744)
RMAN> sql 'alter database datafile 5 offline' ;
using target database control file instead of recovery catalog
sql statement: alter database datafile 5 offline
RMAN> restore datafile 5;
Starting restore at 2023-03-06 11:15:56
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
skipping datafile 5; already restored to file /oradata/orcl10g/example01.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 2023-03-06 11:15:57
RMAN> sql 'alter database datafile 5 online' ;
sql statement: alter database datafile 5 online
RMAN> quit
Recovery Manager complete.6.读写表空间的恢复
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 6 11:27:36 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-06 11:27:36> col file_name for a40;
sys@ORCL10G 2023-03-06 11:27:44> select file_id,file_name from dba_data_files order by 1;
FILE_ID FILE_NAME
---------- ----------------------------------------
1 /oradata/orcl10g/system01.dbf
2 /oradata/orcl10g/undotbs01.dbf
3 /oradata/orcl10g/sysaux01.dbf
4 /oradata/orcl10g/users01.dbf
5 /oradata/orcl10g/example01.dbf
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 11:27:49> ho rm /oradata/orcl10g/users01.dbf
sys@ORCL10G 2023-03-06 11:29:22> alter system checkpoint;
System altered.
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-06 11:29:34> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/oradata/orcl10g/users01.dbf'
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-06 11:29:37> quit
[oracle@yuanzj.com:/home/oracle]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 6 11:30:39 2023
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL10G (DBID=1313046744)
RMAN> sql 'alter database datafile 4 offline';
using target database control file instead of recovery catalog
sql statement: alter database datafile 4 offline
RMAN> restore datafile 4;
Starting restore at 2023-03-06 11:31:18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /oradata/orcl10g/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_nnndf_TAG20230306T111352_l0bpmjvm_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/ora10g/flash_recovery_area/ORCL10G/backupset/2023_03_06/o1_mf_nnndf_TAG20230306T111352_l0bpmjvm_.bkp tag=TAG20230306T111352
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2023-03-06 11:31:19
RMAN> recover datafile 4;
Starting recover at 2023-03-06 11:31:27
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 2023-03-06 11:31:29
RMAN> sql 'alter database datafile 4 online';
sql statement: alter database datafile 4 online
RMAN> quit
Recovery Manager complete.
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 6 11:31:42 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-06 11:31:42> 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.007.控制文件的恢复流程
