环境:
OS:Centos Linux7.3
DB:oracle11.2.0.4
在ARCHIVELOG模式下恢复丢失的索引表空间实验
首先数据库必须为归档模式;
centos@SYS> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /home/oracle/arch Oldest online log sequence 1 Current log sequence 3 centos@SYS> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. centos@SYS> startup mount ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 587205752 bytes Database Buffers 239075328 bytes Redo Buffers 2392064 bytes Database mounted. centos@SYS> alter database archivelog; Database altered. centos@SYS> alter database open; Database altered. centos@SYS> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /home/oracle/arch Oldest online log sequence 1 Next log sequence to archive 3 Current log sequence 3 centos@SYS>
1.创建一个新的表空间用于独立存储索引;
centos@SYS> create tablespace db_idx datafile'/u01/app/oracle/oradata/centos/db_idx01.dbf' size 10M autoextend on maxsize 2G; Tablespace created. centos@SYS> select file_name from dba_data_files; FILE_NAME -------------------------------------------------- /u01/app/oracle/oradata/centos/users01.dbf /u01/app/oracle/oradata/centos/undotbs01.dbf /u01/app/oracle/oradata/centos/sysaux01.dbf /u01/app/oracle/oradata/centos/system01.dbf /home/oracle/xman01.dbf /u01/app/oracle/oradata/centos/example01.dbf /u01/app/oracle/oradata/centos/t1.dbf /u01/app/oracle/oradata/centos/t2.dbf /u01/app/oracle/oradata/centos/db_idx01.dbf 9 rows selected. centos@SYS>
2.创建新表i1并插入数据,同时创建索引存放至新建的表空间db_idx,用于实验验证;
centos@SYS> create table i1(a int) tablespace users; Table created. centos@SYS> insert into i1 select rownum from dual connect by rownum<=10; 10 rows created. centos@SYS> commit; Commit complete. centos@SYS> alter table i1 add constraint pk_i1 primary key(a) using index tablespace db_idx; Table altered. centos@SYS> select index_name,status,tablespace_name from all_indexes where table_name like'I1'; INDEX_NAME STATUS TABLESPACE_NAME ------------------------------ -------- ------------------------------ PK_I1 VALID DB_IDX centos@SYS>
3.模拟故障删除表空间文件,并重启报错;
centos@SYS> !rm /u01/app/oracle/oradata/centos/db_idx01.dbf centos@SYS> !ls /u01/app/oracle/oradata/centos/db_idx01.dbf ls: cannot access /u01/app/oracle/oradata/centos/db_idx01.dbf: No such file or directory centos@SYS> shutdown immediate ORA-03113: end-of-file on communication channel Process ID: 10415 Session ID: 31 Serial number: 775 centos@SYS> startup ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 587205752 bytes Database Buffers 239075328 bytes Redo Buffers 2392064 bytes Database mounted. ORA-01157: cannot identify/lock data file 9 - see DBWR trace file ORA-01110: data file 9: '/u01/app/oracle/oradata/centos/db_idx01.dbf' centos@SYS> select status from v$instance; STATUS ------------ MOUNTED centos@SYS>
4.将故障的数据文件调整为offline状态,并open数据库;
centos@SYS> alter database datafile 8 offline; Database altered. centos@SYS> alter database open; Database altered. centos@SYS>
5.索引表空间数据文件为非重要数据文件,此处使用重建表空间恢复;
centos@SYS> create tablespace db_idx_1 datafile'/u01/app/oracle/oradata/centos/db_idx_101.dbf' size 10M autoextend on maxsize 2G; Tablespace created. centos@SYS> alter index pk_i1 rebuild tablespace db_idx_1; alter index pk_i1 rebuild tablespace db_idx_1 * ERROR at line 1: ORA-00376: file 8 cannot be read at this time ORA-01110: data file 8: '/u01/app/oracle/oradata/centos/db_idx01.dbf' centos@SYS> alter index PK_I1 unusable; Index altered. centos@SYS> select index_name,status,tablespace_name from dba_indexes where table_name='I1'; INDEX_NAME STATUS TABLESPACE_NAME ------------------------------ -------- ------------------------------ PK_I1 UNUSABLE DB_IDX centos@SYS> alter index pk_i1 rebuild tablespace db_idx_1; Index altered. centos@SYS> select index_name,status,tablespace_name from dba_indexes where table_name='I1'; INDEX_NAME STATUS TABLESPACE_NAME ------------------------------ -------- ------------------------------ PK_I1 VALID DB_IDX_1 centos@SYS> drop tablespace db_idx; Tablespace dropped. centos@SYS>
---END---Good Luck---