（1）表空间单个数据文件无法恢复处理
其实现在不去管这个文件，继续添加别的文件，库也能正常工作，但是有这么一个offline的文件感觉很别扭，由于他的库是线上的库，所以我建议他还是别处理这个offline的文件了，继续添加别的数据文件即可。
某个表空间某个数据文件offline，影响现有的在这个存储在这个数据文件中的数据段，不影响后续其他数据库对象的dml以及ddl问题。
（2）数据文件offline测试
- 、包含在这个数据文件的对象
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 19 15:15:02 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> desc dba_extents
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
SEGMENT_NAME VARCHAR2(128)
PARTITION_NAME VARCHAR2(128)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
EXTENT_ID NUMBER
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
SQL> select distinct file_id from dba_extents where segment_name='T1';----- ## 可以知道T1表的extent分布在 数据文件5和7中。
FILE_ID
----------
5
7
SQL> select file_name from dba_Data_files where file_id=5;
FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/prodpri/users02.dbf
SQL> alter database datafile 5 offline; ---现在将数据文件5 offline
Database altered.
SQL> insert into t1 select * from dba_tables; ----测试向T1表中插入数据报错。
insert into t1 select * from dba_tables
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/oradata/prodpri/users02.dbf'
SQL> drop index idxa;
Index dropped.
SQL> insert into t1 select * from dba_tables;
insert into t1 select * from dba_tables *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/oradata/prodpri/users02.dbf
- 不包含在这个数据文件的对象
SQL> create table taa tablespace users as select * from dba_objects; --向表空间中新加数据库对象 ，然后对数据库对象进行dml操作。
COUNT(1)
----------
72647
SQL> insert into taa select *from taa;
72647 rows created.
SQL> commit;
Commit complete.
SQL> delete from taa;
145294 rows deleted.
SQL> commit;
Commit complete.
SQL>
SQL> insert into taa select *From dba_objects;
72648 rows created.
SQL> /
72648 rows created.
SQL> /
72648 rows created.
SQL> commit;
Commit complete.
SQL>
Alert会抛出错误，但是插入成功。
2019-06-19T15:36:14.485831-07:00
Errors in file /u01/app/oracle/diag/rdbms/prodpri/prodpri/trace/prodpri_m000_3423.trc:
ORA-01110: data file 5: '/u01/oradata/prodpri/users02.dbf'
- 其他数据文件的备份与恢复
[oracle@vm01 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jun 19 15:38:03 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
cnnected to target database: PRODPRI (DBID=2060108401)
RMAN> backup datafile 7 format '/u01/001.dbf'; --备份数据文件7
Starting backup at 19-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 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=00007 name=/u01/oradata/prodpri/users01.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUN-19
channel ORA_DISK_1: finished piece 1 at 19-JUN-19
piece handle=/u01/001.dbf tag=TAG20190619T153822 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 19-JUN-1
Starting Control File and SPFILE Autobackup at 19-JUN-19
piece handle=/u01/app/oracle/product/12.2/db_1/dbs/c-2060108401-20190619-01 comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUN-19
[oracle@vm01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 19 15:38:57 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select count(1)from taa;
COUNT(1)
----------
217944
SQL> insert into taa select *from taa;
217944 rows created.
SQL> commit;
Commit complete.
SQL> insert into taa select *from taa;
435888 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select count(1)from taa;
COUNT(1)
----------
871776
SQL> select file_name from dba_data_files where file_id=7;
FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/prodpri/users01.dbf
SQL> host rm -rf /u01/oradata/prodpri/users01.dbf --测试在线删除数据文件7
SQL> select file_name from dba_data_files where tablespace_name='USERS';
FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/prodpri/users01.dbf
/u01/oradata/prodpri/users02.dbf
SQL> ALTER SYSTEM CHECKPOINT; --果然进行checkpoint报错。
ALTER SYSTEM CHECKPOINT
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3507
Session ID: 72 Serial number: 53660
[oracle@vm01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 19 15:42:09 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 8620080 bytes
Variable Size 838862800 bytes
Database Buffers 352321536 bytes
Redo Buffers 8155136 bytes
SQL> ALTER DATABASE MOUNT; ---恢复数据文件7
Database altered.
SQL> ALTER DATABASE DATAFILE 7 OFFLINE;
atabase altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL>
[oracle@vm01 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jun 19 15:43:25 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRODPRI (DBID=2060108401)
RMAN> restore datafile 7;
Starting restore at 19-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
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 00007 to /u01/oradata/prodpri/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/001.dbf
channel ORA_DISK_1: piece handle=/u01/001.dbf tag=TAG20190619T153822
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 19-JUN-19
RMAN> recover datafile 7;
Starting recover at 19-JUN-19
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-JUN-19
RMAN> alter database datafile 7 online;
Statement processed
RMAN>
RMAN> select count(1)from taa;
COUNT(1)
----------
871776
RMAN>
RMAN> alter system checkpoint;
Statement processed
（4）删除数据文件
数据库级别的offline drop只会进行offline处于recover状态，
只能用表空间级别进行删除有问题的数据文件。
SQL> select distinct segment_name,segment_type from dba_extents where file_id=5;
1. alter database datafile 'file_name' offline drop
该命令不会删除数据文件，只是将数据文件的状态更改为recover。 offline drop命令相当于把一个数据文件至于离线状态，并且需要恢复，并非删除数据文件。 数据文件的相关信息还会存在数据字典和控制文件中。
1 alter tablesapce tablespace_name drop datafile 'datafile_name'
---实践，操作系统 文件也消失
注意，该语句只能是datafile online的时候才可以使用。如果说对应的数据文件已经是offline for drop，那么仅针对 dictionary managed tablespaces 可用。
3.1 删除办法：
删除基表file$,重建控制文件。
SQL> delete file1 where FILE#=5;
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter database backup controlfile to trace as '/home/oracle/a.ctl';
Database altered.
STARTUP NOMOUNT
SQL> CREATE CONTROLFILE REUSE DATABASE "PRODPRI" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/oradata/prodpri/redo01.log' SIZE 200M BLOCKSIZE 512,
9 GROUP 2 '/u01/oradata/prodpri/redo02.log' SIZE 200M BLOCKSIZE 512,
10 GROUP 3 '/u01/oradata/prodpri/redo03.log' SIZE 200M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/oradata/prodpri/system01.dbf',
14 '/u01/oradata/prodpri/sysaux01.dbf',
15 '/u01/oradata/prodpri/undotbs01.dbf',
16 '/u01/oradata/prodpri/users01.dbf'
17 CHARACTER SET ZHS16GBK
18 ;
Control file created.
ALTER DATABASE DATAFILE '/u01/oradata/prodpri/users02.dbf' OFFLINE;
少了这一步,我们将file#为5 的文件从file$中已经删除。
[oracle@vm01 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jun 19 16:08:45 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRODPRI (DBID=2060108401, not open)
RMAN> recover database;
Starting recover at 19-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 19-JUN-19
RMAN> ALTER DATABASE OPEN;
Statement processed
RMAN>
RMAN> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/prodpri/temp01.dbf'
2> SIZE 33554432 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Statement processed
RMAN> catalog start with '/u01/arch/prodpri/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/arch/prodpri/
List of Files Unknown to the Database
=====================================
File Name: /u01/arch/prodpri/1_6_994426542.dbf
File Name: /u01/arch/prodpri/1_8_994426542.dbf
File Name: /u01/arch/prodpri/1_5_994426542.dbf
File Name: /u01/arch/prodpri/1_12_994426542.dbf
File Name: /u01/arch/prodpri/1_10_994426542.dbf
File Name: /u01/arch/prodpri/1_9_994426542.dbf
File Name: /u01/arch/prodpri/1_7_994426542.dbf
File Name: /u01/arch/prodpri/1_11_994426542.dbf
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/arch/prodpri/1_6_994426542.dbf
File Name: /u01/arch/prodpri/1_8_994426542.dbf
File Name: /u01/arch/prodpri/1_5_994426542.dbf
File Name: /u01/arch/prodpri/1_12_994426542.dbf
File Name: /u01/arch/prodpri/1_10_994426542.dbf
File Name: /u01/arch/prodpri/1_9_994426542.dbf
File Name: /u01/arch/prodpri/1_7_994426542.dbf
File Name: /u01/arch/prodpri/1_11_994426542.dbf
RMAN>
Controlfile trace很详细
alter database backup controlfile to trace as '/home/oracle/a.ctl';
[oracle@vm01 ~]$ more a.ctl
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="prodpri"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?#/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch/prodpri'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PRODPRI" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/prodpri/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/prodpri/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/prodpri/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/oradata/prodpri/system01.dbf',
'/u01/oradata/prodpri/sysaux01.dbf',
'/u01/oradata/prodpri/undotbs01.dbf',
'/u01/oradata/prodpri/users02.dbf',
'/u01/oradata/prodpri/users01.dbf'
CHARACTER SET ZHS16GBK
;
-- Take files offline to match current control file.
ALTER DATABASE DATAFILE '/u01/oradata/prodpri/users02.dbf' OFFLINE;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/arch/prodpri/1_1_934293149.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/arch/prodpri/1_1_979496114.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/arch/prodpri/1_1_994426542.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/prodpri/temp01.dbf'
SIZE 33554432 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PRODPRI" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/prodpri/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/prodpri/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/prodpri/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/oradata/prodpri/system01.dbf',
'/u01/oradata/prodpri/sysaux01.dbf',
'/u01/oradata/prodpri/undotbs01.dbf',
'/u01/oradata/prodpri/users02.dbf',
'/u01/oradata/prodpri/users01.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/arch/prodpri/1_1_934293149.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/arch/prodpri/1_1_979496114.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/arch/prodpri/1_1_994426542.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/prodpri/temp01.dbf'
SIZE 33554432 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
[oracle@vm01 ~]$
825
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
