1.查看数据库运行模式
SQL> archive log list
Database log mode
Archive Mode
Automatic archival
Enabled
Archive destination
USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
2.scott用户默认表空间为users,在scott用户下创建一个表
SQL> desc book;
Name
Null?Type
----------------------------------------- -------- -------------------
ID
NUMBER(38)
NAME
VARCHAR2(10)
插入数据
SQL> select * from book;
ID NAME
---------- ----------
1 oracle
13 jsp
14 java
3.做一个users表空间offline备份
SQL> select tablespace_name,file_name from dba_data_files
2where tablespace_name='USERS';
TABLESPACE_NAME
FILE_NAME
------------------------------ ------------------------------------------
USERS
D:\ORACLE\ORADATA\ORCL\USERS01.DBF
SQL> alter tablespace users offline;
D:\>copy D:\ORACLE\ORADATA\ORCL\USERS01.DBF d:\hotbak\users01%date:~0,10%.dbf
SQL> alter tablespace users online;
继续插入数据(15-19)
SQL> select * from book;
ID NAME
---------- ----------
1 oracle
13 jsp
14 java
15 net
16 ocp
17 ccie
18 ocm
19 ccnp
一条数据放在一个日志文件
4.恢复(数据库关闭情况下恢复)
1)模拟删除user01.dbf
shutdown immediate;
删除user01.dbf
2)
SQL> startup
ORACLE instance started.
Total System Global Area314572800 bytes
Fixed Size
1248768 bytes
Variable Size
88080896 bytes
Database Buffers
218103808 bytes
Redo Buffers
7139328 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:\ORACLE\ORADATA\ORCL\USERS01.DBF'
由于数据文件损坏不能打开数据库
3)查看要恢复的文件
SQL> select * from v$recover_file;
FILE# ONLINEONLINE_ ERROR
CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------
4 ONLINEONLINEFILE NOT FOUND
0
4)把之前offline备份的数据文件拷回原来位置并重命名为user01.dbf
或copy d:\hotbak\users012014-02-22.dbf D:\ORACLE\ORADATA\ORCL\USERS01.DBF
SQL> select * from v$recover_file;
FILE# ONLINEONLINE_ ERROR
CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------
4 ONLINEONLINE
7284383 22-FEB-14
再查看要恢复的文件错误信息消失
5)查看需要哪些归档日志文件
SQL> select * from v$recovery_log;
THREAD#SEQUENCE# TIME ARCHIVE_NAME
---------- ---------- ------------ ----------------------------------------------------------------------------------------------------
1 15 22-FEB-14C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_02_22\O1_MF_1_15_9JJ3QMVC_.ARC
1 16 22-FEB-14C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_02_22\O1_MF_1_16_9JJ3RGWB_.ARC
SQL> select * from v$log;
GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1
1 17 52428800
1 YES INACTIVE
7285006 22-FEB-14
3
1 19 52428800
1 NOCURRENT
7285144 22-FEB-14
2
1 18 52428800
1 YES INACTIVE
7285016 22-FEB-14
SQL> select name,first_change#,next_change# from v$archived_log;
6)恢复
SQL> recover tablespace users;
ORA-00279: change 7284519 generated at 02/22/2014 10:43:44 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_02_22\O1_MF_1_15_%U_.ARC
ORA-00280: change 7284519 for thread 1 is in sequence #15
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 7284986 generated at 02/22/2014 10:44:03 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_02_22\O1_MF_1_16_%U_.ARC
ORA-00280: change 7284986 for thread 1 is in sequence #16
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_02_22\O1_MF_1_15_9JJ3QMVC_.ARC' no longer needed for this recov
ery
Specify log: {=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
虽然17, 18已经归档，但在恢复的时候没有找归档日志，而是找在线日志文件
SQL> select * from v$log;
GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1
1 17 52428800
1 YES INACTIVE
7285006 22-FEB-14
2
1 18 52428800
1 YES INACTIVE
7285016 22-FEB-14
3
1 19 52428800
1 NOCURRENT
7285144 22-FEB-14
7)打开数据库
SQL> alter database open;
Database altered.
8)scott用户查看数据
SQL> select * from book;
ID NAME
---------- ----------
1 oracle
13 jsp
14 java
15 net
16 ocp
17 ccie
18 ocm
19 ccnp
8 rows selected.
问题:
3.如果归档文件不在原来的位置，要怎么恢复?
4.一般生产库都要对外工作，表空间都不能offline,什么情况下使用offline表空间备份?
|
