oracle用户管理的完全恢复3：在ARCHIVELOG 模式(恢复关闭的数据库)
场景描述：恢复关闭的数据库
在以下情况中，通常将此恢复方法与RECOVER DATABASE命令或RECOVER DATAFIL命令一起使用：
a.恢复的文件属于系统表空间或回退段表空间。
b.需要恢复整个数据库或大部分数据文件。
c.数据库不是全天候（每周7 天、每天24 小时）运行。
前期场景描述图：表t创建在表空间sun01上，用户为user1。分别在不同的时间点插入3条数据，恢复要求三条数据不缺失。
1.查看环境：在ARCHIVELOG 模式下
1 -bash-3.00$ sqlplus /nolog 2 3 SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jan 21 16:26:40 2013 4 5 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. 6 7 SQL> conn /as sysdba 8 SQL> archive log list; 9 Database log mode Archive Mode 10 Automatic archival Enabled 11 Archive destination /u01/admin/sun/arch 12 Oldest online log sequence 1 13 Next log sequence to archive 1 14 Current log sequence 1 15 SQL> 16 SQL> alter database open; 17 18 Database altered. 19 20 SQL>
2.模拟生产库场景图
2.1 创建表空间sun01
1 SQL> create tablespace sun01 datafile '/u01/oradata/sunbak/sun01_1.dbf' size 10M 2 2 extent management local uniform 3 3 segment space management auto; 4 5 Tablespace created. 6 7 SQL> 8 #查看表空间 9 SQL> SELECT TS#,NAME FROM V$TABLESPACE; 10 11 TS# NAME 12 ---------- ------------------------------ 13 0 SYSTEM 14 1 UNDOTBS1 15 2 SYSAUX 16 4 USERS 17 3 TEMP 18 6 EXAMPLE 19 7 SUN01 20 21 7 rows selected. 22 23 SQL> 24 #查看表空间对应数据文件 25 SQL> select TS#,file#,name from v$datafile; 26 27 TS# FILE# NAME 28 ---------- ---------- -------------------------------------------------- 29 0 1 /u01/oradata/sunbak/system01.dbf 30 1 2 /u01/oradata/sunbak/undotbs01.dbf 31 2 3 /u01/oradata/sunbak/sysaux01.dbf 32 4 4 /u01/oradata/sunbak/users01.dbf 33 6 5 /u01/oradata/sunbak/example01.dbf 34 7 6 /u01/oradata/sunbak/sun01_1.dbf 35 36 6 rows selected. 37 38 SQL>
2.2 在sun01表空间上创建用户user1
1 SQL> create user user1 identified by user1 default tablespace sun01; 2 3 User created. 4 5 SQL> grant connect,resource to user1; 6 7 Grant succeeded. 8 9 SQL>
2.3 在user1上创建表t，并插入数据
1 SQL> conn user1/user1 2 Connected. 3 SQL> create table t ( id int, name varchar2(16));--表t存在sun01_1.dbf 上 4 5 Table created. 6 7 SQL> insert into t values(0,'oracle'); 8 9 1 row created. 10 11 SQL> commit; 12 13 Commit complete. 14 15 SQL>
2.4 冷备此时sun01_1.dbf 中一条记录
1 SQL> shutdown immediate 2 Database closed. 3 Database dismounted. 4 ORACLE instance shut down. 5 SQL> 6 7 bash-3.00$ pwd 8 /u01/oradata/sunbak 9 bash-3.00$ ls 10 control01.ctl control03.ctl redo01.log redo03.log sysaux01.dbf temp01.dbf users01.dbf 11 control02.ctl example01.dbf redo02.log sun01_1.dbf system01.dbf undotbs01.dbf 12 bash-3.00$ cp * /u01/backup/cold/ 13 14 bash-3.00$
2.5 在表t中插入数据
1 -bash-3.00$ sqlplus /nolog 2 3 SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jan 22 21:51:54 2013 4 5 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. 6 7 SQL> conn /as sysdba 8 Connected to an idle instance. 9 SQL> startup 10 ORACLE instance started. 11 12 Total System Global Area 289406976 bytes 13 Fixed Size 1279820 bytes 14 Variable Size 92276916 bytes 15 Database Buffers 192937984 bytes 16 Redo Buffers 2912256 bytes 17 Database mounted. 18 Database opened. 19 SQL> 20 SQL> conn user1/user1 21 Connected. 22 SQL> insert into t values (1,'oracle'); 23 24 1 row created. 25 26 SQL> commit; 27 28 Commit complete. 29 30 SQL>
2.6 热备 此时sun01_1.dbf 中两条记录
1 -bash-3.00$ sqlplus /nolog 2 3 SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jan 22 21:51:54 2013 4 5 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. 6 7 SQL> conn /as sysdba 8 Connected to an idle instance. 9 SQL> 10 SQL> alter tablespace sun01 begin backup; 11 12 Tablespace altered. 13 14 SQL> 15 16 SQL> !ls -l /u01/oradata/sunbak/sun01_1.dbf 17 -rw-r----- 1 oracle oinstall 10493952 Jan 22 21:58 /u01/oradata/sunbak/sun01_1.dbf 18 19 SQL> !cp /u01/oradata/sunbak/sun01_1.dbf /u01/backup/hot/ 20 21 SQL> !ls -l /u01/backup/hot/ 22 total 20512 23 -rw-r----- 1 oracle oinstall 10493952 Jan 22 22:00 sun01_1.dbf 24 25 SQL> alter tablespace sun01 end backup; 26 27 Tablespace altered. 28 29 SQL>
2.7 热备结束 继续向表t插入记录
1 SQL> insert into t values (2,'oracle'); 2 3 1 row created. 4 5 SQL> commit; 6 7 Commit complete. 8 9 SQL> 10 11 #手动切换日志 产生归档日志 12 SQL> alter system switch logfile; 13 14 System altered. 15 16 SQL> alter system switch logfile; 17 18 System altered. 19 20 SQL>
恢复场景描述图：
3.恢复场景：数据库在关闭的情况下做完全恢复情况
a.系统表空间损坏
b.undo表空间损坏
c.整个数据库损坏
3.1 损坏整个库的dbf数据文件，包括系统数据文件
1 bash-3.00$ cd /u01/oradata/sunbak 2 bash-3.00$ ls -l 3 total 2162512 4 -rw-r----- 1 oracle oinstall 7061504 Jan 22 23:07 control01.ctl 5 -rw-r----- 1 oracle oinstall 7061504 Jan 22 23:07 control02.ctl 6 -rw-r----- 1 oracle oinstall 7061504 Jan 22 23:07 control03.ctl 7 -rw-r----- 1 oracle oinstall 104865792 Jan 22 22:56 example01.dbf 8 -rw-r----- 1 oracle oinstall 52429312 Jan 22 22:52 redo01.log 9 -rw-r----- 1 oracle oinstall 52429312 Jan 22 22:52 redo02.log 10 -rw-r----- 1 oracle oinstall 52429312 Jan 22 23:07 redo03.log 11 -rw-r----- 1 oracle oinstall 10493952 Jan 22 22:56 sun01_1.dbf 12 -rw-r----- 1 oracle oinstall 251666432 Jan 22 23:07 sysaux01.dbf 13 -rw-r----- 1 oracle oinstall 503324672 Jan 22 23:07 system01.dbf 14 -rw-r----- 1 oracle oinstall 20979712 Jan 22 22:00 temp01.dbf 15 -rw-r----- 1 oracle oinstall 31465472 Jan 22 23:07 undotbs01.dbf 16 -rw-r----- 1 oracle oinstall 5251072 Jan 22 22:56 users01.dbf 17 bash-3.00$ rm -f *.dbf 18 bash-3.00$
3.2 强制关闭数据库
1 SQL> shutdown abort 2 ORACLE instance shut down. 3 SQL>
3.3 从冷备还原数据文件
1 bash-3.00$ cd oradata/sunbak 2 bash-3.00$ pwd 3 /u01/oradata/sunbak 4 bash-3.00$ ls 5 control01.ctl control02.ctl control03.ctl redo01.log redo02.log redo03.log 6 bash-3.00$ cp /u01/backup/cold/*.dbf . 7 bash-3.00$ 8 #注此时 dbf文件的scn号与clt文件的scn号并不一致，恢复需要归档日志文件
3.4 启动数据库 此时会报错
1 SQL> conn /as sysdba 2 Connected to an idle instance. 3 SQL> startup 4 ORACLE instance started. 5 6 Total System Global Area 289406976 bytes 7 Fixed Size 1279820 bytes 8 Variable Size 92276916 bytes 9 Database Buffers 192937984 bytes 10 Redo Buffers 2912256 bytes 11 Database mounted. 12 ORA-01113: file 1 needs media recovery 13 ORA-01110: data file 1: '/u01/oradata/sunbak/system01.dbf' 14 15 SQL>
3.5 查看有哪些文件需要恢复
1 SQL> desc v$recover_file; 2 Name Null? Type 3 ----------------------------------------- -------- ---------------------------- 4 FILE# NUMBER 5 ONLINE VARCHAR2(7) 6 ONLINE_STATUS VARCHAR2(7) 7 ERROR VARCHAR2(18) 8 CHANGE# NUMBER 9 TIME DATE 10 11 SQL> col error format a30; 12 SQL> select * from v$recover_file; 13 14 FILE# ONLINE ONLINE_ ERROR CHANGE# TIME 15 ---------- ------- ------- ------------------------------ ---------- --------- 16 1 ONLINE ONLINE 574367 21-JAN-13 17 2 ONLINE ONLINE 574367 21-JAN-13 18 3 ONLINE ONLINE 574367 21-JAN-13 19 4 ONLINE ONLINE 574367 21-JAN-13 20 5 ONLINE ONLINE 574367 21-JAN-13 21 6 ONLINE ONLINE 574367 21-JAN-13 22 23 6 rows selected. 24 25 SQL>
3.6 恢复
1 #关闭自动恢复 2 SQL> set autorecovery off;#关闭自动恢复 3 SQL> recover database; --此处单独恢复文件：recover datafile 1; 4 ORA-00279: change 574367 generated at 01/21/2013 21:21:47 needed for thread 1 5 ORA-00289: suggestion : /u01/admin/sun/arch/1_2_805319563.dbf 6 ORA-00280: change 574367 for thread 1 is in sequence #2 7 8 9 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 10 11 auto 12 ORA-00279: change 581083 generated at 01/22/2013 22:04:42 needed for thread 1 13 ORA-00289: suggestion : /u01/admin/sun/arch/1_3_805319563.dbf 14 ORA-00280: change 581083 for thread 1 is in sequence #3 15 ORA-00278: log file '/u01/admin/sun/arch/1_2_805319563.dbf' no longer needed 16 for this recovery 17 18 19 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 20 Log applied. 21 Media recovery complete. 22 SQL> 23 24 #查看恢复状态 25 SQL> select * from v$recover_file; 26 27 no rows selected 28 29 SQL>
3.7 打开数据库
1 SQL> alter database open; 2 3 Database altered. 4 5 SQL>
4.查看先前插入的三条数据会否存在
1 SQL> conn user1/user1 2 Connected. 3 SQL> select * from t; 4 5 ID NAME 6 ---------- ---------------- 7 1 oracle 8 2 oracle 9 0 oracle 10 11 恢复成功
