环境:
OS:Oracle Linux Server release 5.6
DB:Oracle 11.2.0.1
1.首先确认数据库为open状态并开启了归档;
itkaifang@SYS> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch Oldest online log sequence 30 Current log sequence 32 itkaifang@SYS> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. itkaifang@SYS> startup mount; ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2217912 bytes Variable Size 520095816 bytes Database Buffers 306184192 bytes Redo Buffers 2433024 bytes Database mounted. itkaifang@SYS> alter database archivelog; Database altered. itkaifang@SYS> alter database open; Database altered. itkaifang@SYS> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch Oldest online log sequence 30 Next log sequence to archive 32 Current log sequence 32 itkaifang@SYS>
2.创建新的表空间t_lob和新的表txt用于实验;
itkaifang@SYS> create tablespace t_a datafile'/u01/app/oracle/oradata/itkaifang/t_a01.dbf' size 50M autoextend on maxsize 2G; Tablespace created. itkaifang@SYS> create table t1 tablespace t_a as select * from dba_objects; Table created. itkaifang@SYS> select file#,name,status from v$datafile; FILE# NAME STATUS ---------- -------------------------------------------------- ------- 1 /u01/app/oracle/oradata/itkaifang/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/itkaifang/sysaux01.dbf ONLINE 3 /u01/app/oracle/oradata/itkaifang/undotbs01.dbf ONLINE 4 /u01/app/oracle/oradata/itkaifang/users01.dbf ONLINE 5 /u01/app/oracle/oradata/itkaifang/example01.dbf ONLINE 6 /u01/app/oracle/oradata/itkaifang/t_a01.dbf ONLINE 6 rows selected. itkaifang@SYS> select count(*) from t1; COUNT(*) ---------- 72562 itkaifang@SYS>
3.在sqlplus中进行热备;
itkaifang@SYS> alter tablespace t_a begin backup; Tablespace altered. itkaifang@SYS> !cp /u01/app/oracle/oradata/itkaifang/t_a01.dbf /home/oracle/t_a01.dbf itkaifang@SYS> !ls /home/oracle/t_a01.dbf /home/oracle/t_a01.dbf itkaifang@SYS> alter tablespace t_a end backup; Tablespace altered. itkaifang@SYS> alter system switch logfile; System altered. itkaifang@SYS>
4.模拟故障;
itkaifang@SYS> !rm /u01/app/oracle/oradata/itkaifang/t_a01.dbf itkaifang@SYS> !ls /u01/app/oracle/oradata/itkaifang/t_a01.dbf ls: /u01/app/oracle/oradata/itkaifang/t_a01.dbf: No such file or directory itkaifang@SYS>
5.1以shutdown abort方式模拟断电关闭数据库恢复;
itkaifang@SYS> shutdown abort; ORACLE instance shut down. itkaifang@SYS> startup ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2217912 bytes Variable Size 520095816 bytes Database Buffers 306184192 bytes Redo Buffers 2433024 bytes Database mounted. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: '/u01/app/oracle/oradata/itkaifang/t_a01.dbf' itkaifang@SYS> select status from v$instance; STATUS ------------ MOUNTED itkaifang@SYS> !cp /home/oracle/t_a01.dbf /u01/app/oracle/oradata/itkaifang/t_a01.dbf itkaifang@SYS> recover datafile 6; Media recovery complete. itkaifang@SYS> alter database open; Database altered. itkaifang@SYS> select count(*) from t1; COUNT(*) ---------- 72562 itkaifang@SYS> select file#,name,status from v$datafile; FILE# NAME STATUS ---------- -------------------------------------------------- ------- 1 /u01/app/oracle/oradata/itkaifang/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/itkaifang/sysaux01.dbf ONLINE 3 /u01/app/oracle/oradata/itkaifang/undotbs01.dbf ONLINE 4 /u01/app/oracle/oradata/itkaifang/users01.dbf ONLINE 5 /u01/app/oracle/oradata/itkaifang/example01.dbf ONLINE 6 /u01/app/oracle/oradata/itkaifang/t_a01.dbf ONLINE 6 rows selected. itkaifang@SYS>
5.2以shutdown immediate一致性关闭数据库恢复;
itkaifang@SYS> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. itkaifang@SYS> startup ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2217912 bytes Variable Size 520095816 bytes Database Buffers 306184192 bytes Redo Buffers 2433024 bytes Database mounted. Database opened. itkaifang@SYS> select count(*) from t1; select count(*) from t1 * ERROR at line 1: ORA-00376: file 6 cannot be read at this time ORA-01110: data file 6: '/u01/app/oracle/oradata/itkaifang/t_a01.dbf' itkaifang@SYS> select status from v$instance; STATUS ------------ OPEN itkaifang@SYS> select file#,name,status from v$datafile; FILE# NAME STATUS ---------- -------------------------------------------------- ------- 1 /u01/app/oracle/oradata/itkaifang/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/itkaifang/sysaux01.dbf ONLINE 3 /u01/app/oracle/oradata/itkaifang/undotbs01.dbf ONLINE 4 /u01/app/oracle/oradata/itkaifang/users01.dbf ONLINE 5 /u01/app/oracle/oradata/itkaifang/example01.dbf ONLINE 6 /u01/app/oracle/oradata/itkaifang/t_a01.dbf RECOVER 6 rows selected. itkaifang@SYS> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. itkaifang@SYS> startup mount ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2217912 bytes Variable Size 520095816 bytes Database Buffers 306184192 bytes Redo Buffers 2433024 bytes Database mounted. itkaifang@SYS> alter database datafile '/u01/app/oracle/oradata/itkaifang/t_a01.dbf' offline; Database altered. itkaifang@SYS> !cp /home/oracle/t_a01.dbf /u01/app/oracle/oradata/itkaifang/t_a01.dbf itkaifang@SYS> recover datafile 6; Media recovery complete. itkaifang@SYS> alter database open; Database altered. itkaifang@SYS> select file#,name,status from v$datafile; FILE# NAME STATUS ---------- -------------------------------------------------- ------- 1 /u01/app/oracle/oradata/itkaifang/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/itkaifang/sysaux01.dbf ONLINE 3 /u01/app/oracle/oradata/itkaifang/undotbs01.dbf ONLINE 4 /u01/app/oracle/oradata/itkaifang/users01.dbf ONLINE 5 /u01/app/oracle/oradata/itkaifang/example01.dbf ONLINE 6 /u01/app/oracle/oradata/itkaifang/t_a01.dbf OFFLINE 6 rows selected. itkaifang@SYS> alter database datafile '/u01/app/oracle/oradata/itkaifang/t_a01.dbf' online; Database altered. itkaifang@SYS> select count(*) from t1; COUNT(*) ---------- 72562 itkaifang@SYS>
---END---Good Luck---