第七章：RMAN
1、rman的定义和功能:
1) Recovery Manager
2)通过oracle 提供的包，建立备份和恢复的server process，在oracle server 上做备份和恢复
3）rman 备份 database、tablespace、datafile、controlfile、spfile、archivelog
4）支持归档和非归档
5）在归档模式下支持一致性备份（冷备份）和非一致性备份（热备份）
6）非归档只支持一致性备份
2、rman的优点：
1）只备份数据文件中已经使用过的block（备份segment高水位线以下的block），节省空间
2）备份时自动检查数据文件是否有坏块，并可以标记坏块，跳过坏块
3)可以实现增量备份
4）备份和恢复都是用rman的命令来完成
3、rman的架构：
1）target database：需要做备份的生产库
2）存储设备：disk 、tape （sbt 磁带机）存放备份文件的设备
3）channel ：目标库和存储设备之间备份通道（服务进程）默认最少启动一个channel，可同时启动多个channel ，并行操作，加快备份和恢复的速度
4）server process：用于备份和恢复
5）rman备份的元数据：记录备份的信息（一般放在目标库的controlfile 里）
6）catalog database :集中管理、存放备份的元数据，还可以存储备份脚本
7} MML:media manage layer 介质管理层：用于管理磁带机的库文件或驱动
4、rman 链接目标库
1） $ORACLE_SID 和目标库一致
[oracle@work ~]$ env |grep SID
ORACLE_SID=prod
[oracle@work ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 18 05:06:08 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=170319990)
RMAN>
[oracle@work ~]$ rman
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 18 05:10:11 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: PROD (DBID=170319990)
RMAN>
2)远程连接
1）在target database 启动listeners ，在client配置tnsnames
C:\Documents and Settings\liqx>rman target sys/oracle@prod
恢复管理器: Release 10.2.0.1.0 - Production on 星期四 8月 18 09:51:24 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到目标数据库: PROD (DBID=170319990)
RMAN>
5、查看rman的默认配置，修改rman的配置信息
1）查看rman的默认配置
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_prod.f'; # default
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
------off:默认情况下，在备份system 表空间时，会备份控制文件和spfile
on: 在做任何文件的备份时，会自动备份控制文件和spfile，并且数据库的物理结构发生变化时，也自动备份controlfile。
2）format 指明备份文件的路径和文件名
RMAN备份文件格式
备份文件可以自定义各种各样的格式，如下：
%c 备份片的拷贝数
%D 位于该月中的第几天 (DD)
%M 位于该年中的第几月 (MM)
%F 一个基于DBID 唯一的名称,这个格式的形式为c-IIIIIIIIII-YYYYMMDD-QQ,
%d 数据库名称其中IIIIIIIIII 为该数据库的DBID，YYYYMMDD 为日期，QQ 是一个1-256 的序列
%n 数据库名称，向右填补到最大八个字符
%u 一个八个字符的名称代表备份集与创建时间
%p 该备份集中的备份片号，从1 开始到创建的文件数
%U 一个唯一的文件名，代表%u_%p_%c
%s 备份集的号
%t 备份集时间戳
%T 年月日格式(YYYYMMDD)
6、rman备份的分类：
1）backupset：只备份datafile里已经使用过的块（ segment HWM以下）、可以实现增量备份（可以备份到disk和tape）。如果只启用一个channel，默认会把所有备份的datafile放入到一个backupset里
，并且生成一个备份片（backup piece，在OS下看到的备份文件）
恢复时速度慢，需要从备份集里先释放数据文件
2）backup piece ：由backupset 生成的备份文件，保存在os下
3） copy(immage 镜像)：实际上和手工cp 是类似的，备份datafile 所有的数据块,不能实现增量备份（但可以作为增量备份的0级备份），只能备份到disk ，不能备份到tape，
恢复时速度快 ,不需要做restore。
6、rman的命令格式：
1）交互式
RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down
RMAN> startup force mount;
Oracle instance started
database mounted
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 67110288 bytes
Database Buffers 243269632 bytes
Redo Buffers 2973696 bytes
RMAN> alter database open;
database opened
RMAN> sql 'alter system switch logfile';
sql statement: alter system switch logfile
RMAN> sql 'select * from scott.emp';
sql statement: select * from scott.emp
---------对select 不显示结果
RMAN> backup datafile 2 format '/disk1/rman/prod/%d_%s_%t.bak';
Starting backup at 18-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=139 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00002 name=/u01/app/oracle/oradata/prod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-AUG-11
channel ORA_DISK_1: finished piece 1 at 18-AUG-11
piece handle=/disk1/rman/prod/PROD_2_759476271.bak tag=TAG20110818T053751 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 18-AUG-11
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 464.00K DISK 00:00:03 18-AUG-11
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20110818T053751
Piece Name: /disk1/rman/prod/PROD_2_759476271.bak
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 1316126 18-AUG-11 /u01/app/oracle/oradata/prod/users01.dbf
-----备份片的大小为464k ，只包含已经使用的block
RMAN>
2）批处理
RMAN> run {
2> shutdown immediate;
3> startup mount;
4> allocate channel c1 type disk;
5> allocate channel c2 type disk;
6> backup database format '/disk1/rman/prod/%d_%s.bak';
7> alter database open;
8> }
第八章： rman 备份
1、rman备份分类：
1）归档：可以做一致性和非一致性备份
2）非归档：一致性备份
2、rman备份的分类：
1）backupset
-------一致性备份
---查看数据库的物理结构
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 325 SYSTEM *** /u01/app/oracle/oradata/test2/system01.dbf
2 200 RTBS *** /u01/app/oracle/oradata/test2/rbs01.dbf
3 225 SYSAUX *** /u01/app/oracle/oradata/test2/sysaux01.dbf
4 100 USERS *** /u01/app/oracle/oradata/test2/users01.dbf
5 10 TEST1 *** /u01/app/oracle/oradata/test2/test1.dbf
8 11 SMALLUNDO *** /u01/app/oracle/oradata/test2/smallundo01.dbf
10 100 INDX *** /u01/app/oracle/oradata/test2/indx01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 100 TEMP 100 /u01/app/oracle/oradata/test2/temp01.dbf
2 4 TMP1 4 /u01/app/oracle/oradata/test2/tmp1.dbf
RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 /u01/app/oracle/oradata/test2/system01.dbf
2 0 /u01/app/oracle/oradata/test2/rbs01.dbf
3 0 /u01/app/oracle/oradata/test2/sysaux01.dbf
4 0 /u01/app/oracle/oradata/test2/users01.dbf
5 0 /u01/app/oracle/oradata/test2/test1.dbf
8 0 /u01/app/oracle/oradata/test2/smallundo01.dbf
10 0 /u01/app/oracle/oradata/test2/indx01.dbf
RMAN>
RMAN> run {
2> shutdown immediate;
3> startup mount;
4> allocate channel c1 type disk;
5> allocate channel c2 type disk;
6> backup database format '/disk1/rman/prod/%d_%s.bak';
7> alter database open;
8> }
---------非一致性备份
RMAN> backup database format='/disk1/rman/prod/prod_%s.bak' filesperset 3;
RMAN> backup tablespace users format '/disk1/rman/prod/users_%s.bak' tag=userbak;
RMAN> backup datafile 2,3,5 format '/disk1/rman/prod/%d_%s.bak';
-------备份归档日志
------查出第一个归档日志的的first scn
11:03:49 SQL> select name, SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from v$archived_log
11:04:05 2 where lower(name) like '%arch_1_1_783951639.log%';
NAME SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
-------------------------------------------------- ---------- ------------- ------------
/disk4/arch/testdb/arch_1_1_783951639.log 1 834562 843469
RMAN> backup archivelog from scn=834562;
2）snapshot 镜像
RMAN> copy datafile 2 to '/disk1/rman/prod/users_%s.bak';
RMAN> backup as copy tablespace users format='/disk1/rman/prod/%d_users_%s.bak'；
[oracle@work ~]$ ls -lth /disk1/rman/prod
total 101M
-rw-r----- 1 oracle oinstall 101M Aug 18 06:12 users_15.bak
[oracle@work ~]$
-------生成的备份文件的大小和原datafile大小相同
3、增量备份：只备份发生变化的block（rman通过在备份时记录datablock的scn，下次备份时通过比较上次备份的数据块上的scn来确定数据块是否发生变化）
1）差异增量备份（Differential incremental backup）：下次备份时比较上次以来同级别或低级别的备份，为备份基础
-----0级备份
RMAN> backup incremental level 0 format '/disk1/rman/prod/users_0_%s.bak' tablespace users;
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Incr 0 464.00K DISK 00:00:01 18-AUG-11
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20110818T064000
Piece Name: /disk1/rman/prod/users_0_19.bak
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 0 Incr 1319567 18-AUG-11 /u01/app/oracle/oradata/prod/users01.dbf
------2级备份
RMAN> backup incremental level 2 format '/disk1/rman/prod/users_2_%s.bak' tablespace users;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Incr 2 112.00K DISK 00:00:01 18-AUG-11
BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20110818T064135
Piece Name: /disk1/rman/prod/users_2_21.bak
List of Datafiles in backup set 18
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 2 Incr 1319644 18-AUG-11 /u01/app/oracle/oradata/prod/users01.dbf
------1级备份
RMAN> backup incremental level 1 format '/disk1/rman/prod/users_1_%s.bak' tablespace users;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22 Incr 1 176.00K DISK 00:00:01 18-AUG-11
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20110818T064323
Piece Name: /disk1/rman/prod/users_1_25.bak
List of Datafiles in backup set 22
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 1 Incr 1319760 18-AUG-11 /u01/app/oracle/oradata/prod/users01.dbf
2）累积增量备份的区别：只比较比自己级别低的作为备份基础
-------2级备份
RMAN> backup incremental level 2 cumulative format '/disk1/rman/prod/users_2cu_%s.bak' tablespace users;
-----1级备份
RMAN> backup incremental level 1 cumulative format '/disk1/rman/prod/users_1cu_%s.bak' tablespace users;
4、自动备份controlfile 和 spfile
我们可以用RMAN进行数据库、表空间和数据文件的增量备份。
如果数据库运行在归档模式，在数据库OPEN状态我们可以进行增量备份；如果数据库处于非归档方式，在数据库CLOSE状态我们也可以进行增量备份。增量备份只备份上次备份以来变化过的数据块。
每个数据块都包含一个system change number （SCN），记录着该数据块的最后改变。在进行增量备份的时候，RMAN将每个数据块的SCN和父级增量备份的CHECKPOINT SCN进行比较，如果该数据块的SCN大于等于父级的CHECKPOINT SCN，RMAN将备份该数据块。
为了提高数据库增量备份的性能，我们可以指定变化跟踪文件来监控数据块的改变：
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'd:/rman_change.f' REUSE;
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
可以通过以下命令查询：
SQL> SELECT FILENAME,STATUS from V$BLOCK_CHANGE_TRACKING
移动跟踪文件：
SQL>SELECT filename from V$BLOCK_CHANGE_TRACKING;
SQL>SHUTDOWN IMMEDIATE;
SQL>HOST COPY OLDFILE NEWFILE
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE RENAME FILE ‘OLDFILE' TO ‘NEWFILE’;
SQL>ALTER DATABASE OPEN;
如果不能关闭数据库，必须禁用跟踪文件然后移动跟踪文件：
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'new_location';
增量备份分为两种：差异增量备份和累积增量备份。缺省的增量备份是差异增量备份。
差异增量备份：备份同级或者上级（如果有同级备份，先找同级）备份（差异或者累积）以来变化的数据块。如果找不到同级或者上级（知道LEVEL0），如果ORACLE版本>=10.0.0，RMAN将COPY从数据文件创建开始所有的数据块作为LEVEL1备份；如果ORACLE版本<10.0.0，RMAN将生成LEVEL0备份。
第九章：rman 完全恢复
1、recover 恢复：
1）归档 ：完全恢复和不完全恢复
2）非归档：只能恢复到最后一次备份状态
2、完全恢复：
----先对数据库做冷备
RMAN> list backup;
RMAN> run {
2> shutdown immediate;
3> startup mount;
4> backup database format '/disk1/rman/prod/%d_%s.bak';
5> alter database open;
6> }
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
28 Full 589.39M DISK 00:00:50 18-AUG-11
BP Key: 28 Status: AVAILABLE Compressed: NO Tag: TAG20110818T071905
Piece Name: /disk1/rman/prod/PROD_31.bak
List of Datafiles in backup set 28
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1321672 18-AUG-11 /u01/app/oracle/oradata/prod/system01.dbf
2 Full 1321672 18-AUG-11 /u01/app/oracle/oradata/prod/users01.dbf
3 Full 1321672 18-AUG-11 /u01/app/oracle/oradata/prod/sysaux01.dbf
4 Full 1321672 18-AUG-11 /u01/app/oracle/oradata/prod/index01.dbf
5 Full 1321672 18-AUG-11 /u01/app/oracle/oradata/prod/example01.dbf
6 Full 1321672 18-AUG-11 /u01/app/oracle/oradata/prod/test01.dbf
7 Full 1321672 18-AUG-11 /u01/app/oracle/oradata/prod/undo_tbs01.dbf
8 Full 1321672 18-AUG-11 /u01/app/oracle/oradata/prod/test02.dbf
9 Full 1321672 18-AUG-11 /u01/app/oracle/oradata/prod/cuug01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29 Full 6.89M DISK 00:00:01 18-AUG-11
BP Key: 29 Status: AVAILABLE Compressed: NO Tag: TAG20110818T072001
Piece Name: /disk1/recovery/prod/PROD/autobackup/2011_08_18/o1_mf_s_759482322_74sxc274_.bkp
Control File Included: Ckp SCN: 1321672 Ckp time: 18-AUG-11
SPFILE Included: Modification time: 18-AUG-11
案例1：数据库大部分datafile丢失（recover database，必须在mount下）
1）测试环境
07:21:12 SQL> conn scott/tiger
Connected.
07:21:16 SQL>
07:21:16 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
SYS_TEMP_FBT TABLE
TEST TABLE
EMP1 TABLE
7 rows selected.
07:21:20 SQL> select * from test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
16 rows selected.
07:21:27 SQL> insert into test select * from test;
16 rows created.
07:21:43 SQL> commit;
Commit complete.
07:21:45 SQL> select count(*) from test;
COUNT(*)
----------
32
07:21:50 SQL> conn /as sysdba
Connected.
07:21:56 SQL>
07:22:03 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 8 52428800 1 NO CURRENT 1321561 18-AUG-11
2 1 7 52428800 1 YES INACTIVE 1321559 18-AUG-11
3 1 6 52428800 1 YES INACTIVE 1316062 18-AUG-11
07:22:09 SQL> alter system switch logfile;
System altered.
07:22:15 SQL>
07:22:15 SQL> shutdown abort
ORACLE instance shut down.
07:23:03 SQL> !
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/*.dbf
2)启动database
[oracle@work ~]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 18 07:23:17 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
07:23:17 SQL>
07:23:17 SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/prod/system01.dbf'
07:23:25 SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
1 FILE NOT FOUND
2 FILE NOT FOUND
3 FILE NOT FOUND
4 FILE NOT FOUND
5 FILE NOT FOUND
6 FILE NOT FOUND
7 FILE NOT FOUND
8 FILE NOT FOUND
9 FILE NOT FOUND
9 rows selected.
07:23:38 SQL>
3）利用rman 恢复database
[oracle@work ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 18 07:24:09 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=170319990, not open)
RMAN> run {
2> startup force mount;
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5> restore database;
6> recover database;
7> alter database open;
8> }
Oracle instance started
database mounted
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=157 devtype=DISK
allocated channel: c2
channel c2: sid=154 devtype=DISK
Starting restore at 18-AUG-11
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/prod/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/prod/users01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/prod/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/prod/index01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/prod/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/prod/test01.dbf
restoring datafile 00007 to /u01/app/oracle/oradata/prod/undo_tbs01.dbf
restoring datafile 00008 to /u01/app/oracle/oradata/prod/test02.dbf
restoring datafile 00009 to /u01/app/oracle/oradata/prod/cuug01.dbf
channel c1: reading from backup piece /disk1/rman/prod/PROD_31.bak
channel c1: restored backup piece 1
piece handle=/disk1/rman/prod/PROD_31.bak tag=TAG20110818T071905
channel c1: restore complete, elapsed time: 00:00:57
Finished restore at 18-AUG-11
Starting recover at 18-AUG-11
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 18-AUG-11
database opened
released channel: c1
released channel: c2
RMAN>
告警日志信息：
Full restore complete of datafile 6 /u01/app/oracle/oradata/prod/test01.dbf. Elapsed time: 0:00:01
checkpoint is 1321672
Full restore complete of datafile 8 /u01/app/oracle/oradata/prod/test02.dbf. Elapsed time: 0:00:02
checkpoint is 1321672
Full restore complete of datafile 9 /u01/app/oracle/oradata/prod/cuug01.dbf. Elapsed time: 0:00:01
checkpoint is 1321672
Thu Aug 18 07:25:40 2011
Full restore complete of datafile 2 /u01/app/oracle/oradata/prod/users01.dbf. Elapsed time: 0:00:16
checkpoint is 1321672
Full restore complete of datafile 4 /u01/app/oracle/oradata/prod/index01.dbf. Elapsed time: 0:00:18
checkpoint is 1321672
Full restore complete of datafile 5 /u01/app/oracle/oradata/prod/example01.dbf. Elapsed time: 0:00:20
checkpoint is 1321672
Full restore complete of datafile 7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf. Elapsed time: 0:00:21
checkpoint is 1321672
Thu Aug 18 07:26:00 2011
Full restore complete of datafile 3 /u01/app/oracle/oradata/prod/sysaux01.dbf. Elapsed time: 0:00:35
checkpoint is 1321672
Thu Aug 18 07:26:12 2011
Full restore complete of datafile 1 /u01/app/oracle/oradata/prod/system01.dbf. Elapsed time: 0:00:47
checkpoint is 1321672
Thu Aug 18 07:26:20 2011
alter database recover datafile list clear
Thu Aug 18 07:26:20 2011
Completed: alter database recover datafile list clear
Thu Aug 18 07:26:21 2011
alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9
Thu Aug 18 07:26:21 2011
alter database recover if needed
start
Media Recovery Start
Thu Aug 18 07:26:21 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 8 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo01.log
Thu Aug 18 07:26:22 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo03.log
Thu Aug 18 07:26:22 2011
Media Recovery Complete (prod)
4）验证：
07:28:10 SQL> select count(*) from scott.test;
COUNT(*)
----------
32
案例2：
--------部分数据文件丢失，恢复数据文件（mount 或 open）
1）测试环境
07:30:40 SQL> create table test (id int) tablespace test;
Table created.
07:30:52 SQL> conn /as sysdba
Connected.
07:30:57 SQL>
07:30:57 SQL> insert into scott.test values (1);
1 row created.
07:31:02 SQL> commit;
Commit complete.
07:31:04 SQL> alter system archive log current;
System altered.
07:31:10 SQL> insert into scott.test values (2);
1 row created.
07:31:13 SQL> commit;
Commit complete.
07:31:15 SQL> alter system archive log current;
System altered.
07:31:16 SQL> insert into scott.test values (3);
1 row created.
07:31:19 SQL> commit;
Commit complete.
07:31:20 SQL> alter system archive log current;
System altered.
07:31:24 SQL> insert into scott.test values (4);
1 row created.
07:31:26 SQL> insert into scott.test values (5);
1 row created.
07:31:28 SQL> commit;
Commit complete.
07:31:30 SQL> select * from scott.test;
ID
----------
1
2
3
4
5
07:31:34 SQL>
07:31:34 SQL> shutdown abort
ORACLE instance shut down.
07:33:23 SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/test0*.dbf
2）启动database
07:34:07 SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/prod/test01.dbf'
07:34:15 SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
6 FILE NOT FOUND
8 FILE NOT FOUND
3）利用rman 恢复datafile
RMAN> run {
2> startup force mount;
3> sql 'alter database datafile 6,8 offline';
4> alter database open;
5> restore datafile 6,8;
6> recover datafile 6,8;
7> sql 'alter database datafile 6,8 online';
8> }
Oracle instance started
database mounted
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
using target database control file instead of recovery catalog
sql statement: alter database datafile 6,8 offline
database opened
Starting restore at 18-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to /u01/app/oracle/oradata/prod/test01.dbf
restoring datafile 00008 to /u01/app/oracle/oradata/prod/test02.dbf
channel ORA_DISK_1: reading from backup piece /disk1/rman/prod/PROD_31.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/disk1/rman/prod/PROD_31.bak tag=TAG20110818T071905
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 18-AUG-11
Starting recover at 18-AUG-11
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 8 is already on disk as file /disk1/arch/prod/arch_8_1_759396736.log
archive log thread 1 sequence 9 is already on disk as file /disk1/arch/prod/arch_9_1_759396736.log
archive log thread 1 sequence 10 is already on disk as file /disk1/arch/prod/arch_10_1_759396736.log
archive log thread 1 sequence 11 is already on disk as file /disk1/arch/prod/arch_11_1_759396736.log
archive log thread 1 sequence 12 is already on disk as file /disk1/arch/prod/arch_12_1_759396736.log
archive log thread 1 sequence 13 is already on disk as file /disk1/arch/prod/arch_13_1_759396736.log
archive log filename=/disk1/arch/prod/arch_8_1_759396736.log thread=1 sequence=8
archive log filename=/disk1/arch/prod/arch_9_1_759396736.log thread=1 sequence=9
archive log filename=/disk1/arch/prod/arch_10_1_759396736.log thread=1 sequence=10
archive log filename=/disk1/arch/prod/arch_11_1_759396736.log thread=1 sequence=11
media recovery complete, elapsed time: 00:00:03
Finished recover at 18-AUG-11
sql statement: alter database datafile 6,8 online
RMAN>
4）验证：
[oracle@work ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 18 07:37:17 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
07:37:18 SQL>
07:37:18 SQL> select * from scott.test;
ID
----------
1
2
3
4
5
07:37:23 SQL>
案例3：
----------表空间数据文件全部丢失，恢复数据文件（open）
1）测试环境
07:37:23 SQL> insert into scott.test values (6);
1 row created.
07:39:18 SQL> insert into scott.test values (7);
1 row created.
07:39:20 SQL> insert into scott.test values (8);
1 row created.
07:39:21 SQL> commit;
Commit complete.
07:39:23 SQL> select * from scott.test;
ID
----------
6
7
8
1
2
3
4
5
8 rows selected.
07:40:06 SQL>
----在open 状态下datafile 被破坏
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/test0*.dbf
[oracle@work ~]$
07:41:19 SQL> alter system flush buffer_cache;
System altered.
07:41:25 SQL> conn scott/tiger
Connected.
07:41:28 SQL>
07:41:28 SQL> insert into scott.test values (10);
insert into scott.test values (10)
*
ERROR at line 1:
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/app/oracle/oradata/prod/test02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
07:41:31 SQL>
3)利用rman 恢复
RMAN> run {
2> sql 'alter database datafile 6,8 offline';
3> restore datafile 6,8;
4> recover datafile 6,8;
5> sql 'alter database datafile 6,8 online';
6> }
4）验证
07:41:31 SQL> insert into scott.test values (10);
1 row created.
07:45:58 SQL> commit;
Commit complete.
07:46:01 SQL> select * from test;
ID
----------
10
6
7
8
9
1
2
3
4
5
10 rows selected.
07:46:04 SQL>
案例3：
-----------恢复表空间（open状态）
1）测试环境
07:47:00 SQL> insert into emp1 select * from emp where rownum <3;
2 rows created.
07:47:14 SQL> commit;
Commit complete.
07:47:17 SQL> select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
07:47:20 SQL> conn /as sysdba
Connected.
07:47:23 SQL>
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/users01.dbf
07:47:23 SQL> alter system flush buffer_cache;
System altered.
07:47:41 SQL> conn scott/tiger
Connected.
07:47:44 SQL>
07:47:44 SQL> select * from emp1;
select * from emp1
*
ERROR at line 1:
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/u01/app/oracle/oradata/prod/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
07:47:48 SQL>
2）恢复表空间
RMAN> run {
2> sql'alter database datafile 2 offline';
3> restore tablespace users;
4> recover tablespace users;
5> sql 'alter database datafile 2 online';
6> }
3）验证
07:47:48 SQL> select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
07:50:31 SQL>
案例4：
---------------新建表空间没有备份，datafile 被破坏
1）测试环境
07:52:16 SQL> create tablespace lx
07:52:24 2 datafile '/u01/app/oracle/oradata/prod/lx01.dbf' size 10m;
Tablespace created.
07:52:38 SQL> conn scott/tiger
Connected.
07:52:40 SQL>
07:52:40 SQL> create table lx01 (id int) tablespace lx;
Table created.
07:52:49 SQL> insert into lx01 values (1);
1 row created.
07:52:55 SQL> insert into lx01 values (2);
1 row created.
07:52:57 SQL> insert into lx01 values (3);
1 row created.
07:52:59 SQL> commit;
Commit complete.
07:53:00 SQL> select * from lx01;
ID
----------
1
2
3
07:53:03 SQL>
07:53:03 SQL> conn /as sysdba
Connected.
07:53:16 SQL>
07:53:16 SQL> shutdown abort
ORACLE instance shut down.
07:53:19 SQL>
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/lx01.dbf
2）启动database
07:53:19 SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/app/oracle/oradata/prod/lx01.dbf'
07:54:15 SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
10 FILE NOT FOUND
07:54:25 SQL>
3）利用rman 恢复
RMAN> run {
2> sql 'alter database datafile 10 offline';
3> alter database open;
4> restore datafile 10;
5> recover datafile 10;
6> sql'alter database datafile 10 online';
7> }
using target database control file instead of recovery catalog
sql statement: alter database datafile 10 offline
database opened
Starting restore at 18-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
creating datafile fno=10 name=/u01/app/oracle/oradata/prod/lx01.dbf ；自动建立新的lx01.dbf 文件
restore not done; all files readonly, offline, or already restored
Finished restore at 18-AUG-11
Starting recover at 18-AUG-11
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 18-AUG-11
sql statement: alter database datafile 10 online
RMAN>
案例5：
--------利用image （镜像备份）恢复数据
1）建立镜像备份
RMAN> delete backup;
RMAN> list backup;
RMAN> backup as copy datafile 2 format '/disk1/rman/prod/users_%s.bak';
RMAN> list copy of datafile 2;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
19 2 A 18-AUG-11 1383523 18-AUG-11 /disk1/rman/prod/users_38.bak
2）测试环境
07:59:17 SQL> conn scott/tiger
Connected.
07:59:20 SQL>
07:59:20 SQL> select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
07:59:27 SQL> insert into emp1 select * from emp1;
2 rows created.
07:59:46 SQL> select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
07:59:48 SQL> commit;
Commit complete.
07:59:49 SQL> conn /as sysdba
Connected.
07:59:52 SQL>
07:59:52 SQL> shutdown abort
ORACLE instance shut down.
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/users01.dbf
3）启动数据库
07:59:56 SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/app/oracle/oradata/prod/users01.dbf'
08:00:37 SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
2 FILE NOT FOUND
08:00:48 SQL>
4）利用rman恢复
[oracle@work ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 18 08:01:15 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=170319990, not open)
RMAN> run {
2> restore datafile 2;
3> recover datafile 2;
4> alter database open;
5> }
Starting restore at 18-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: restoring datafile 00002
input datafile copy recid=19 stamp=759484683 filename=/disk1/rman/prod/users_38.bak
destination for restore of datafile 00002: /u01/app/oracle/oradata/prod/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00002
output filename=/u01/app/oracle/oradata/prod/users01.dbf recid=20 stamp=759484903
Finished restore at 18-AUG-11
Starting recover at 18-AUG-11
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 18-AUG-11
database opened
RMAN>
5）验证：
08:02:54 SQL> select * from scott.emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
08:02:59 SQL>
案例6：
----------利用增量备份进行恢复
1）建立增量备份
-------利用image 备份作为增量备份的0 级备份
RMAN> copy datafile 2 to '/disk1/rman/prod/users_%s.bak';
2）建立2 级差异备份
--------测试环境
08:05:52 SQL> conn scott/tiger
Connected.
08:05:58 SQL>
08:05:58 SQL> insert into emp1 select * from emp1;
4 rows created.
08:06:00 SQL> /
8 rows created.
08:06:01 SQL> commit;
Commit complete.
08:06:03 SQL>
RMAN> backup incremental level 2 format '/disk1/rman/prod/users_2_%s.bak' datafile 2;
RMAN> list backup of datafile 2;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
37 Incr 2 528.00K DISK 00:00:00 18-AUG-11
BP Key: 37 Status: AVAILABLE Compressed: NO Tag: TAG20110818T080650
Piece Name: /disk1/rman/prod/users_2_42.bak
List of Datafiles in backup set 37
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 2 Incr 1404665 18-AUG-11 /u01/app/oracle/oradata/prod/users01.dbf
3）建立 2级差异备份
08:06:03 SQL> insert into emp1 select * from emp1;
16 rows created.
08:07:49 SQL> /
32 rows created.
08:07:51 SQL> commit;
Commit complete.
08:07:53 SQL>
RMAN> backup incremental level 2 format '/disk1/rman/prod/users_2_%s.bak' datafile 2;
4）建立累计增量备份
08:07:53 SQL> insert into emp1 select * from emp1;
64 rows created.
08:08:55 SQL> /
128 rows created.
08:08:55 SQL> commit;
Commit complete.
08:08:57 SQL>
RMAN> backup incremental level 1 cumulative format '/disk1/rman/prod/users_1cu_%s.bak' datafile 2;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41 Incr 1 528.00K DISK 00:00:00 18-AUG-11
BP Key: 41 Status: AVAILABLE Compressed: NO Tag: TAG20110818T080942
Piece Name: /disk1/rman/prod/users_1cu_46.bak
List of Datafiles in backup set 41
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 1 Incr 1404815 18-AUG-11 /u01/app/oracle/oradata/prod/users01.dbf
--------------恢复
1）测试环境
08:08:57 SQL> select count(*) from emp1;
COUNT(*)
----------
256
08:10:33 SQL> shutdown abort
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/users01.dbf
2）启动数据库
08:10:52 SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/app/oracle/oradata/prod/users01.dbf'
08:11:35 SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
2 FILE NOT FOUND
3）恢复数据文件
RMAN> run {
2> restore datafile 2;
3> recover datafile 2;
4> alter database open;
5> }
Starting restore at 18-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/app/oracle/oradata/prod/users01.dbf ；先用0级restore
channel ORA_DISK_1: reading from backup piece /disk1/rman/prod/users_1cu_46.bak ；再用累积备份restore
channel ORA_DISK_1: restored backup piece 1
piece handle=/disk1/rman/prod/users_1cu_46.bak tag=TAG20110818T080942
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 18-AUG-11
Starting recover at 18-AUG-11
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 18-AUG-11
database opened
4）验证
08:14:01 SQL> select count(*) from scott.emp1;
COUNT(*)
----------
256
08:14:08 SQL>
----------------将数据文件恢复到新的位置
01:27:08 SQL> startup
ORACLE instance started.
Total System Global Area 440401920 bytes
Fixed Size 1219904 bytes
Variable Size 213910208 bytes
Database Buffers 222298112 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/test/lxtbs2.dbf'
01:27:17 SQL> select file# ,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
7 FILE NOT FOUND
01:27:25 SQL>
[oracle@work ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Oct 25 01:27:39 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST (DBID=2062097024, not open)
run {
startup force mount;
set newname for datafile 7 to '/disk1/oradata/test/lxtbs2.dbf';
restore datafile 7;
switch datafile 7;
recover datafile 7;
alter database open;
}
或者
run {
startup force mount;
switch datafile 7 to datafilecopy '/disk1/oradata/test/lxtbs2.dbf';
recover datafile 7;
alter database open;
}
Oracle instance started
database mounted
Total System Global Area 440401920 bytes
Fixed Size 1219904 bytes
Variable Size 213910208 bytes
Database Buffers 222298112 bytes
Redo Buffers 2973696 bytes
executing command: SET NEWNAME
Starting restore at 25-OCT-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK
datafile 7 is already restored to file /disk1/oradata/test/lxtbs2.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 25-OCT-11
datafile 7 switched to datafile copy
input datafile copy recid=14 stamp=765423007 filename=/disk1/oradata/test/lxtbs2.dbf
Starting recover at 25-OCT-11
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 25-OCT-11
database opened
RMAN>
第十章：
1、rman 不完全恢复
2、rman 不完全恢复：
1）基于时间和基于scn： 用于恢复过去某个时间被误操作的重要table
案例1：
-------恢复过去某个时间点误操作的table
1）测试环境
01:15:47 SQL> conn scott/tiger
Connected.
01:16:01 SQL> select * from lx01;
ID
----------
1
2
3
01:16:07 SQL> truncate table lx01;
Table truncated.
01:16:25 SQL> insert into lx01 values (111);
1 row created.
01:16:37 SQL> insert into lx01 values (222);
1 row created.
01:16:40 SQL> insert into lx01 values (333);
1 row created.
01:16:43 SQL> commit;
Commit complete.
01:16:44 SQL> select * from lx01;
ID
----------
111
222
333
01:16:49 SQL>
2）通过logmnr 找出误操作的时间点
------配置 utl 参数
01:17:55 SQL> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /home/oracle/logmnr
--------建立数据字典分析文件
01:18:31 SQL> execute dbms_logmnr_d.build('dict.ora','/home/oracle/logmnr',dbms_logmnr_d.store_in_flat_file);
PL/SQL procedure successfully completed.
--------添加日志分析
01:19:11 SQL> col member for a50
01:19:17 SQL>
1* select group#,member from v$logfile
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/prod/redo03.log
2 /u01/app/oracle/oradata/prod/redo02.log
1 /u01/app/oracle/oradata/prod/redo01.log
01:19:17 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 17 52428800 1 NO CURRENT 1424859 18-AUG-11
2 1 16 52428800 1 YES INACTIVE 1403618 18-AUG-11
3 1 15 52428800 1 YES INACTIVE 1383274 18-AUG-11
01:19:22 SQL>
01:19:22 SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/prod/redo01.log',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
----------执行分析
01:20:17 SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dict.ora',options=>dbms_logmnr.ddl_dict_tracking);
PL/SQL procedure successfully completed.
-----------查看分析结果
01:22:17 SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents
01:22:18 2 where lower(sql_redo) like '%table%' and username='SCOTT';
USERNAME SCN TIMESTAMP SQL_REDO
------------------------------ ---------- ------------------- --------------------------------------------------
SCOTT 1450811 2011-08-19 01:44:41 truncate table lx01;
---------结束logmnr
01:22:59 SQL> execute dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
01:23:23 SQL>
---------设置date 格式
01:24:31 SQL> alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
System altered.
3）恢复
[oracle@work ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Aug 19 01:49:04 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=170319990)
run {
shutdown immediate;
startup mount;
allocate channel c1 type disk;
allocate channel c2 type disk;
set until time '2011-08-19 01:44:41';
restore database;
recover database;
alter database open resetlogs;
}
4）验证
01:51:54 SQL> select * from scott.lx01;
ID
----------
1
2
3
01:51:58 SQL>
Startup mount;
Restore database until time "to_date('2010-07-05 14:02:00','yyyy-mm-dd hh24:mi:ss')";
Recover database until time "to_date('2010-07-05 14:02:00','yyyy-mm-dd hh24:mi:ss')";
Alter database open resetlogs;
案例2：基于scn
1）测试环境
01:34:32 SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1450438
--------在实际的生产环境中，scn要通过logmnr找出，这里只是实验
01:34:37 SQL> conn scott/tiger;
Connected.
01:34:41 SQL>
01:34:41 SQL> select * from lx01;
ID
----------
111
222
333
01:34:58 SQL> truncate table lx01;
Table truncated.
01:35:04 SQL> insert into lx01 values(1);
1 row created.
01:35:21 SQL> insert into lx01 values(2);
1 row created.
01:35:24 SQL> insert into lx01 values(4);
1 row created.
01:35:25 SQL> commit;
Commit complete.
01:35:27 SQL> select * from lx01;
ID
----------
1
2
4
01:35:31 SQL>
2）通过rman恢复 ，lx01 被truncate 之前的data
run {
shutdown immediate;
startup mount;
allocate channel c1 type disk;
allocate channel c2 type disk;
set until scn 1450438;
restore database;
recover database;
alter database open resetlogs;
10> }
database closed
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 96470416 bytes
Database Buffers 213909504 bytes
Redo Buffers 2973696 bytes
allocated channel: c1
channel c1: sid=157 devtype=DISK
allocated channel: c2
channel c2: sid=154 devtype=DISK
executing command: SET until clause
Starting restore at 19-AUG-11
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/prod/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/prod/users01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/prod/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/prod/index01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/prod/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/prod/test01.dbf
restoring datafile 00007 to /u01/app/oracle/oradata/prod/undo_tbs01.dbf
restoring datafile 00008 to /u01/app/oracle/oradata/prod/test02.dbf
restoring datafile 00009 to /u01/app/oracle/oradata/prod/cuug01.dbf
restoring datafile 00010 to /u01/app/oracle/oradata/prod/lx01.dbf
restoring datafile 00011 to /u01/app/oracle/oradata/prod/perfstat01.dbf
channel c1: reading from backup piece /disk1/rman/prod/PROD_54.bak
channel c1: restored backup piece 1
piece handle=/disk1/rman/prod/PROD_54.bak tag=TAG20110819T013156
channel c1: restore complete, elapsed time: 00:01:07
Finished restore at 19-AUG-11
Starting recover at 19-AUG-11
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 19-AUG-11
database opened
released channel: c1
released channel: c2
RMAN>
告警日志信息：
Completed: alter database mount
Fri Aug 19 01:38:47 2011
Full restore complete of datafile 6 /u01/app/oracle/oradata/prod/test01.dbf. Elapsed time: 0:00:01
checkpoint is 1450186
Full restore complete of datafile 8 /u01/app/oracle/oradata/prod/test02.dbf. Elapsed time: 0:00:02
checkpoint is 1450186
Full restore complete of datafile 9 /u01/app/oracle/oradata/prod/cuug01.dbf. Elapsed time: 0:00:02
checkpoint is 1450186
Full restore complete of datafile 10 /u01/app/oracle/oradata/prod/lx01.dbf. Elapsed time: 0:00:01
checkpoint is 1450186
Fri Aug 19 01:39:10 2011
Full restore complete of datafile 2 /u01/app/oracle/oradata/prod/users01.dbf. Elapsed time: 0:00:23
checkpoint is 1450186
Full restore complete of datafile 4 /u01/app/oracle/oradata/prod/index01.dbf. Elapsed time: 0:00:27
checkpoint is 1450186
Full restore complete of datafile 5 /u01/app/oracle/oradata/prod/example01.dbf. Elapsed time: 0:00:29
checkpoint is 1450186
Full restore complete of datafile 7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf. Elapsed time: 0:00:30
checkpoint is 1450186
Fri Aug 19 01:39:24 2011
Full restore complete of datafile 11 /u01/app/oracle/oradata/prod/perfstat01.dbf. Elapsed time: 0:00:38
checkpoint is 1450186
Fri Aug 19 01:39:36 2011
Full restore complete of datafile 3 /u01/app/oracle/oradata/prod/sysaux01.dbf. Elapsed time: 0:00:51
checkpoint is 1450186
Fri Aug 19 01:39:49 2011
Full restore complete of datafile 1 /u01/app/oracle/oradata/prod/system01.dbf. Elapsed time: 0:01:03
checkpoint is 1450186
Fri Aug 19 01:39:52 2011
alter database recover datafile list clear
Fri Aug 19 01:39:52 2011
Completed: alter database recover datafile list clear
Fri Aug 19 01:39:52 2011
alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11
Fri Aug 19 01:39:52 2011
alter database recover if needed
start until change 1450438
Media Recovery Start
Fri Aug 19 01:39:52 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 18 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo03.log
Fri Aug 19 01:39:53 2011
Incomplete Recovery applied until change 1450439
Fri Aug 19 01:39:53 2011
Media Recovery Complete (prod)
Completed: alter database recover if needed
start until change 1450438
Fri Aug 19 01:39:56 2011
alter database open resetlogs
3）验证：
01:42:00 SQL> select * from scott.lx01;
ID
----------
111
222
333
案例3：
-----------在做完全恢复时，归档日志或current redo log 不完整，不能实现完全恢复
----------基于redo log的sequence （相当于手工恢复：基于cancel）
1）测试环境
01:55:05 SQL> select * from scott.lx01;
ID
----------
1
2
3
01:55:22 SQL> insert into scott.lx01 values (4);
1 row created.
01:55:31 SQL> commit;
Commit complete.
01:55:32 SQL> alter system archive log current;
System altered.
01:55:40 SQL> insert into scott.lx01 values (5);
1 row created.
01:55:41 SQL> commit;
Commit complete.
01:55:44 SQL> insert into scott.lx01 values (6);
1 row created.
01:55:47 SQL> commit;
Commit complete.
01:55:51 SQL> alter system archive log current;
System altered.
01:55:54 SQL> insert into scott.lx01 values (7);
1 row created.
01:56:00 SQL> commit;
Commit complete.
01:56:02 SQL> alter system archive log current;
System altered.
01:56:07 SQL> insert into scott.lx01 values (8);
1 row created.
01:56:10 SQL> commit;
Commit complete.
01:56:11 SQL> alter system archive log current;
System altered.
01:56:16 SQL> insert into scott.lx01 values (9);
1 row created.
01:56:18 SQL> commit;
Commit complete.
01:56:20 SQL> select name from v$archived_log;
NAME
------------------------------------------------------------------------------------------------------------------------
/disk1/arch/prod/arch_2_1_759549082.log
/disk1/arch/prod/arch_3_1_759549082.log
/disk1/arch/prod/arch_4_1_759549082.log
/disk1/arch/prod/arch_5_1_759549082.log
/disk1/arch/prod/arch_6_1_759549082.log
01:57:19 SQL> shutdown abort;
ORACLE instance shut down.
01:57:28 SQL> !
[oracle@work ~]$ exit
exit
01:57:32 SQL> !
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/lx01.dbf
[oracle@work ~]$ mv /disk1/arch/prod/arch_5_1_759549082.log /disk1/arch/prod/arch_5_1_759549082.log.bak
2）启动database
01:58:23 SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/app/oracle/oradata/prod/lx01.dbf'
01:58:39 SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
10 FILE NOT FOUND
3)对datafile 做完全恢复
RMAN> run {
2> restore datafile 10;
3> recover datafile 10;
4> alter database open;
5> }
Starting restore at 19-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00010 to /u01/app/oracle/oradata/prod/lx01.dbf
channel ORA_DISK_1: reading from backup piece /disk1/rman/prod/PROD_54.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/disk1/rman/prod/PROD_54.bak tag=TAG20110819T013156
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 19-AUG-11
Starting recover at 19-AUG-11
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 18 is already on disk as file /disk1/arch/prod/arch_18_1_759396736.log
archive log thread 1 sequence 1 is already on disk as file /disk1/arch/prod/arch_1_1_759548396.log
archive log thread 1 sequence 2 is already on disk as file /disk1/arch/prod/arch_2_1_759548396.log
archive log thread 1 sequence 3 is already on disk as file /disk1/arch/prod/arch_3_1_759548396.log
archive log thread 1 sequence 1 is already on disk as file /disk1/arch/prod/arch_1_1_759549082.log
archive log thread 1 sequence 2 is already on disk as file /disk1/arch/prod/arch_2_1_759549082.log
archive log thread 1 sequence 3 is already on disk as file /disk1/arch/prod/arch_3_1_759549082.log
archive log thread 1 sequence 4 is already on disk as file /disk1/arch/prod/arch_4_1_759549082.log
archive log thread 1 sequence 6 is already on disk as file /disk1/arch/prod/arch_6_1_759549082.log
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/19/2011 01:59:56
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 5 lowscn 1451172 found to restore
---------恢复失败，因为 ‘log thread 1 seq 5’ 丢失
4）执行不完全恢复
RMAN> run {
2> startup force mount;
3> set until sequence 5;
4> restore database;
5> recover database;
6> alter database open resetlogs;
7> }
5）验证
02:04:04 SQL>
02:04:04 SQL> select * from scott.lx01;
ID
----------
4
5
6
1
2
3
6 rows selected.
案例4：
-------------误删除表空间（已备份），通过备份的控制文件进行恢复
---------基于 backup controlfile
1）测试环境
02:21:27 SQL> select * from scott.lx01;
ID
----------
4
5
6
1
2
3
6 rows selected.
-------备份当前控制文件
RMAN> backup current controlfile format '/disk1/rman/prod/control.bak';
02:21:50 SQL> insert into scott.lx01 values (7);
1 row created.
02:23:24 SQL> insert into scott.lx01 values (8);
1 row created.
02:23:26 SQL> insert into scott.lx01 values (9);
1 row created.
02:23:27 SQL> commit;
Commit complete.
02:23:29 SQL> select * from scott.lx01;
ID
----------
4
5
6
1
2
3
7
8
9
9 rows selected.
----------表空间被误删除
02:23:35 SQL> drop tablespace lx including contents and datafiles;
Tablespace dropped.
02:24:05 SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
8 /u01/app/oracle/oradata/prod/test02.dbf TESTS
3 /u01/app/oracle/oradata/prod/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/prod/users01.dbf USERS
1 /u01/app/oracle/oradata/prod/system01.dbf SYSTEM
5 /u01/app/oracle/oradata/prod/example01.dbf EXAMPLE
6 /u01/app/oracle/oradata/prod/test01.dbf TESTS
7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf UNDO_TBS
4 /u01/app/oracle/oradata/prod/index01.dbf INDEXES
9 /u01/app/oracle/oradata/prod/cuug01.dbf CUUG
11 /u01/app/oracle/oradata/prod/perfstat01.dbf PERFSTAT
10 rows selected.
查看告警日志，找到drop tablespace的时间点：
Fri Aug 19 02:24:01 2011
drop tablespace lx including contents and datafiles
2）恢复
02:25:32 SQL> select dbid from v$database;
DBID
----------
170319990
---------恢复备份控制文件
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 83887504 bytes
Database Buffers 226492416 bytes
Redo Buffers 2973696 bytes
----- RMAN> set dbid=170319990;
----- executing command: SET DBID
RMAN> restore controlfile from '/disk1/rman/prod/control.bak';
Starting restore at 19-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oracle/oradata/prod/control01.ctl
output filename=/u01/app/oracle/oradata/prod/control02.ctl
output filename=/u01/app/oracle/oradata/prod/control03.ctl
Finished restore at 19-AUG-11
-----------基于时间点的恢复
run {
startup force mount;
allocate channel c1 type disk;
allocate channel c2 type disk;
set until time '2011-08-19 02:24:01'; //删除tablespace的时间点
restore database;
recover database;
alter database open resetlogs;
9> }
3）验证
02:34:03 SQL> col file_name for a50
02:34:10 SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
8 /u01/app/oracle/oradata/prod/test02.dbf TESTS
3 /u01/app/oracle/oradata/prod/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/prod/users01.dbf USERS
1 /u01/app/oracle/oradata/prod/system01.dbf SYSTEM
5 /u01/app/oracle/oradata/prod/example01.dbf EXAMPLE
6 /u01/app/oracle/oradata/prod/test01.dbf TESTS
7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf UNDO_TBS
4 /u01/app/oracle/oradata/prod/index01.dbf INDEXES
9 /u01/app/oracle/oradata/prod/cuug01.dbf CUUG
10 /u01/app/oracle/oradata/prod/lx01.dbf LX
11 /u01/app/oracle/oradata/prod/perfstat01.dbf PERFSTAT
11 rows selected.
02:34:22 SQL> select * from scott.lx01;
ID
----------
4
5
6
1
2
3
7
8
9
9 rows selected.
02:34:32 SQL>
----------------非归档模式 RMAN 备份恢复
一、查看测试环境
[oracle@oracle ~]$ !sql
sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 28 08:40:15 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
08:40:17 SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes
Variable Size 201328380 bytes
Database Buffers 46137344 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
08:40:42 SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string lx01
db_unique_name string lx01
global_names boolean FALSE
instance_name string lx01
lock_name_space string
log_file_name_convert string
service_names string lx01
08:41:29 SQL> !clear
08:41:45 SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /disk4/arch/lx01
Oldest online log sequence 1
Current log sequence 3
08:41:57 SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string lx01
db_unique_name string lx01
global_names boolean FALSE
instance_name string lx01
lock_name_space string
log_file_name_convert string
service_names string lx01
08:42:10 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 1 10485760 2 NO INACTIVE 1278963 2011-07-22 12:00:15
4 1 0 10485760 2 YES UNUSED 0
3 1 3 10485760 2 NO CURRENT 1321065 2011-07-28 08:40:32
2 1 2 10485760 2 NO INACTIVE 1300729 2011-07-28 08:20:06
08:42:42 SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/lx01/system01.dbf
/u01/app/oracle/oradata/lx01/rtbs01.dbf
/u01/app/oracle/oradata/lx01/sysaux01.dbf
/u01/app/oracle/oradata/lx01/user01.dbf
/u01/app/oracle/oradata/lx01/small_undo01.dbf
/u01/app/oracle/oradata/lx01/test01.dbf
/u01/app/oracle/oradata/lx01/users02.dbf
/u01/app/oracle/oradata/lx01/undo01.dbf
/u01/app/oracle/oradata/lx01/indexes01.dbf
9 rows selected.
[oracle@oracle ~]$ !sql
sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 28 08:45:39 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
08:45:39 SQL> conn scott/tiger
Connected.
08:45:41 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ADMIN_EXT_EMPLOYEES TABLE
SYS_TEMP_FBT TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
EMP_EXT TABLE
EXCEPTIONS TABLE
SALGRADE TABLE
EMP1 TABLE
TEST TABLE
10 rows selected.
二、利用rman备份
RMAN> run {
2> shutdown immediate;
3> startup mount;
4> backup database format='/disk4/rman/lx/%d_%s.bak';
5> alter database open;
6> }
database closed
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes
Variable Size 201328380 bytes
Database Buffers 46137344 bytes
Redo Buffers 2973696 bytes
Starting backup at 22-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/lx01/system01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/lx01/rtbs01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/lx01/sysaux01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/lx01/user01.dbf
input datafile fno=00010 name=/u01/app/oracle/oradata/lx01/undo01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/lx01/test01.dbf
input datafile fno=00007 name=/u01/app/oracle/oradata/lx01/users02.dbf
input datafile fno=00011 name=/u01/app/oracle/oradata/lx01/indexes01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/lx01/small_undo01.dbf
channel ORA_DISK_1: starting piece 1 at 22-JUL-11
channel ORA_DISK_1: finished piece 1 at 22-JUL-11
piece handle=/disk4/rman/lx/LX01_115.bak tag=TAG20110722T114920 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 22-JUL-11
Starting Control File and SPFILE Autobackup at 22-JUL-11
piece handle=/disk4/recovery/lx01/LX01/autobackup/2011_07_22/o1_mf_s_757165750_72m71539_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-JUL-11
database opened
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
111 Full 319.06M DISK 00:00:29 22-JUL-11
BP Key: 111 Status: AVAILABLE Compressed: NO Tag: TAG20110722T114920
Piece Name: /disk4/rman/lx/LX01_115.bak
List of Datafiles in backup set 111
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/system01.dbf
2 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/rtbs01.dbf
3 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/sysaux01.dbf
4 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/user01.dbf
5 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/small_undo01.dbf
6 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/test01.dbf
7 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/users02.dbf
10 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/undo01.dbf
11 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/indexes01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
112 Full 7.27M DISK 00:00:01 22-JUL-11
BP Key: 112 Status: AVAILABLE Compressed: NO Tag: TAG20110722T114956
Piece Name: /disk4/recovery/lx01/LX01/autobackup/2011_07_22/o1_mf_s_757165750_72m71539_.bkp
Control File Included: Ckp SCN: 1278135 Ckp time: 22-JUL-11
SPFILE Included: Modification time: 22-JUL-11
三、模拟测试环境
08:45:48 SQL> select * from test order by 1;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected.
08:45:53 SQL> conn /as sysdba
Connected.
08:46:01 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 1 10485760 2 NO INACTIVE 1278963 2011-07-22 12:00:15
4 1 4 10485760 2 NO CURRENT 1321633 2011-07-28 08:45:01
3 1 3 10485760 2 NO ACTIVE 1321065 2011-07-28 08:40:32
2 1 2 10485760 2 NO INACTIVE 1300729 2011-07-28 08:20:06
08:46:06 SQL> insert into scott.test values (9);
1 row created.
08:46:17 SQL> insert into scott.test values (10);
1 row created.
08:46:20 SQL> commit;
Commit complete.
08:46:21 SQL> alter system switch logfile;
System altered.
08:46:25 SQL> /
System altered.
08:46:27 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 5 10485760 2 NO ACTIVE 1321688 2011-07-28 08:46:25
4 1 4 10485760 2 NO ACTIVE 1321633 2011-07-28 08:45:01
3 1 3 10485760 2 NO ACTIVE 1321065 2011-07-28 08:40:32
2 1 6 10485760 2 NO CURRENT 1321690 2011-07-28 08:46:27
08:46:31 SQL> insert into scott.test values (11);
1 row created.
08:46:48 SQL> insert into scott.test values (12);
1 row created.
08:46:50 SQL> commit;
Commit complete.
08:46:51 SQL> alter system switch logfile;
System altered.
08:46:56 SQL> /
System altered.
08:47:02 SQL> /
System altered.
08:47:03 SQL> /
System altered.
08:47:05 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 9 10485760 2 NO INACTIVE 1321706 2011-07-28 08:47:03
4 1 8 10485760 2 NO INACTIVE 1321704 2011-07-28 08:47:02
3 1 7 10485760 2 NO INACTIVE 1321702 2011-07-28 08:46:56
2 1 10 10485760 2 NO CURRENT 1321708 2011-07-28 08:47:05
/
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 9 10485760 2 NO INACTIVE 1321706 2011-07-28 08:47:03
4 1 8 10485760 2 NO INACTIVE 1321704 2011-07-28 08:47:02
3 1 7 10485760 2 NO INACTIVE 1321702 2011-07-28 08:46:56
2 1 10 10485760 2 NO CURRENT 1321708 2011-07-28 08:47:05
08:47:13 SQL> alter system switch logfile;
System altered.
08:47:16 SQL> select * from scott.test;
ID
----------
6
7
8
1
2
3
4
5
9
10
11
12
12 rows selected.
08:47:22 SQL> shutdown abort
ORACLE instance shut down.
08:47:41 SQL> !
[oracle@oracle ~]$
[oracle@oracle ~]$ rm /u01/app/oracle/oradata/lx01/*.dbf
[oracle@oracle ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 28 08:50:16 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
08:50:16 SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes
Variable Size 201328380 bytes
Database Buffers 46137344 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/lx01/system01.dbf'
08:50:25 SQL> select file# ,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
1 FILE NOT FOUND
2 FILE NOT FOUND
3 FILE NOT FOUND
4 FILE NOT FOUND
5 FILE NOT FOUND
6 FILE NOT FOUND
7 FILE NOT FOUND
10 FILE NOT FOUND
11 FILE NOT FOUND
9 rows selected.
08:50:37 SQL> exit
ERROR:
ORA-03135: connection lost contact
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options (with complications)
四、用rman restore database
08:52:45 SQL> select dbid from v$database;
DBID
----------
1559643052
08:54:04 SQL>
RMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
113 Full 142.50M DISK 00:00:26 28-JUL-11
BP Key: 113 Status: AVAILABLE Compressed: NO Tag: TAG20110728T084406
Piece Name: /disk4/rman/lx/lx01_118_757673047.bak
List of Datafiles in backup set 113
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/system01.dbf
5 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/small_undo01.dbf
10 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/undo01.dbf
11 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/indexes01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
114 Full 176.95M DISK 00:00:34 28-JUL-11
BP Key: 114 Status: AVAILABLE Compressed: NO Tag: TAG20110728T084406
Piece Name: /disk4/rman/lx/lx01_119_757673047.bak
List of Datafiles in backup set 114
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/rtbs01.dbf
3 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/sysaux01.dbf
4 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/user01.dbf
6 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/test01.dbf
7 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/users02.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
115 Full 7.27M DISK 00:00:01 28-JUL-11
BP Key: 115 Status: AVAILABLE Compressed: NO Tag: TAG20110728T084443
Piece Name: /disk4/recovery/lx01/LX01/autobackup/2011_07_28/o1_mf_s_757673037_732pfw39_.bkp
Control File Included: Ckp SCN: 1321454 Ckp time: 28-JUL-11
SPFILE Included: Modification time: 22-JUL-11
RMAN> shutdown
database dismounted
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes
Variable Size 201328380 bytes
Database Buffers 46137344 bytes
Redo Buffers 2973696 bytes
RMAN> set dbid=1559643052;
executing command: SET DBID
RMAN> restore controlfile from '/disk4/recovery/lx01/LX01/autobackup/2011_07_28/o1_mf_s_757673037_732pfw39_.bkp';
Starting restore at 28-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/disk1/lx01/oradata/control01.ctl
output filename=/disk1/lx01/oradata/control02.ctl
output filename=/disk2/lx01/oradata/control03.ctl
Finished restore at 28-JUL-11
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 28-JUL-11
Starting implicit crosscheck backup at 28-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 28-JUL-11
Starting implicit crosscheck copy at 28-JUL-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 28-JUL-11
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /disk4/recovery/lx01/LX01/autobackup/2011_07_28/o1_mf_s_757673037_732pfw39_.bkp
using channel ORA_DISK_1
skipping datafile 1; already restored to file /u01/app/oracle/oradata/lx01/system01.dbf
skipping datafile 5; already restored to file /u01/app/oracle/oradata/lx01/small_undo01.dbf
skipping datafile 10; already restored to file /u01/app/oracle/oradata/lx01/undo01.dbf
skipping datafile 11; already restored to file /u01/app/oracle/oradata/lx01/indexes01.dbf
skipping datafile 2; already restored to file /u01/app/oracle/oradata/lx01/rtbs01.dbf
skipping datafile 3; already restored to file /u01/app/oracle/oradata/lx01/sysaux01.dbf
skipping datafile 4; already restored to file /u01/app/oracle/oradata/lx01/user01.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/lx01/test01.dbf
skipping datafile 7; already restored to file /u01/app/oracle/oradata/lx01/users02.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 28-JUL-11
五、查看restore 结果
[oracle@oracle ~]$ !sql
sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 28 08:52:15 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
08:52:16 SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
1321454
08:52:31 SQL> select checkpoint_change#,file# from v$datafile;
CHECKPOINT_CHANGE# FILE#
------------------ ----------
1321454 1
1321454 2
1321454 3
1321454 4
1321454 5
1321454 6
1321454 7
1321454 10
1321454 11
9 rows selected.
08:52:38 SQL> select checkpoint_change#,file# from v$datafile_header;
CHECKPOINT_CHANGE# FILE#
------------------ ----------
1321454 1
1321454 2
1321454 3
1321454 4
1321454 5
1321454 6
1321454 7
1321454 10
1321454 11
9 rows selected.
六、用rman 进行恢复
RMAN> recover database;
Starting recover at 28-JUL-11
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 8 is already on disk as file /disk1/lx01/oradata/redo04b.log
archive log thread 1 sequence 9 is already on disk as file /disk1/lx01/oradata/redo01b.log
archive log thread 1 sequence 10 is already on disk as file /disk1/lx01/oradata/redo02b.log
archive log thread 1 sequence 11 is already on disk as file /disk1/lx01/oradata/redo03b.log
unable to find archive log
archive log thread=1 sequence=3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/28/2011 08:59:30
RMAN-06054: media recovery requesting unknown log: thread 1 seq 3 lowscn 1321454
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/28/2011 08:59:57
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> alter database open resetlogs;
database opened
RMAN>
alter_lx01.log 告警日志信息：
alter database mount
Thu Jul 28 08:58:15 2011
Setting recovery target incarnation to 20
Thu Jul 28 08:58:15 2011
Successful mount of redo thread 1, with mount id 1560169571
Thu Jul 28 08:58:15 2011
Database mounted in Exclusive Mode
Completed: alter database mount
Thu Jul 28 08:59:29 2011
alter database recover datafile list clear
Thu Jul 28 08:59:29 2011
Completed: alter database recover datafile list clear
Thu Jul 28 08:59:29 2011
alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6 , 7 , 10 , 11
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6 , 7 , 10 , 11
Thu Jul 28 08:59:29 2011
alter database recover if needed
start until cancel using backup controlfile
Media Recovery Start
ORA-279 signalled during: alter database recover if needed
start until cancel using backup controlfile
...
Thu Jul 28 08:59:29 2011
alter database recover cancel
Thu Jul 28 08:59:30 2011
Media Recovery Canceled
Completed: alter database recover cancel
第十一章： catalog database 目录库
1、catalog database 的功能
1）集中存放rman的资料库（备份的元数据），并且可以和target database 的controlfile同步
2）存放rman的备份脚本
2、catalog database 的配置
1）需要建立一个单独的database
02:43:21 SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string catdb
db_unique_name string catdb
global_names boolean FALSE
instance_name string catdb
lock_name_space string
log_file_name_convert string
service_names string catdb
02:43:30 SQL>
2）建立存放rman 元数据的tablespace
02:44:12 SQL> create tablespace cattbs
02:44:24 2 datafile '/u01/app/oracle/oradata/catdb/cattbs01.dbf' size 100m;
Tablespace created.
3)建立user，用于管理rman，并授权
02:45:10 SQL> create user rman identified by rman default tablespace cattbs;
User created.
02:45:33 SQL> grant connect ,resource ,recovery_catalog_owner to rman;
Grant succeeded.
4）启动listener ，链接catalog database
[oracle@work admin]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 19-AUG-2011 02:48:01
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=work)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 19-AUG-2011 02:47:31
Uptime 0 days 0 hr. 0 min. 29 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=work)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "prod" has 1 instance(s).
Instance "prod", status READY, has 1 handler(s) for this service...
Service "prodXDB" has 1 instance(s).
Instance "prod", status READY, has 1 handler(s) for this service...
Service "prod_XPT" has 1 instance(s).
Instance "prod", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@work admin]$ rman catalog rman/rman@catdb
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Aug 19 02:49:05 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> create catalog; // 建立catalog 的对象
recovery catalog created
RMAN>
--------建立以下对象，存储rman 元数据
02:45:54 SQL> conn rman/rman
Connected.
02:50:16 SQL>
02:50:16 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
NODE TABLE
DB TABLE
CONF TABLE
DBINC TABLE
CKP TABLE
TS TABLE
TSATT TABLE
DF TABLE
DFATT TABLE
TF TABLE
TFATT TABLE
OFFR TABLE
RR TABLE
RT TABLE
ORL TABLE
RLH TABLE
AL TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BS TABLE
BP TABLE
BCF TABLE
CCF TABLE
XCF TABLE
BSF TABLE
BDF TABLE
CDF TABLE
XDF TABLE
BRL TABLE
BCB TABLE
CCB TABLE
SCR TABLE
SCRL TABLE
CONFIG TABLE
XAL TABLE
RSR TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
FB TABLE
RC_DATABASE VIEW
RC_DATABASE_INCARNATION VIEW
RC_RESYNC VIEW
RC_CHECKPOINT VIEW
RC_TABLESPACE VIEW
RC_DATAFILE VIEW
RC_TEMPFILE VIEW
RC_REDO_THREAD VIEW
RC_REDO_LOG VIEW
RC_LOG_HISTORY VIEW
RC_ARCHIVED_LOG VIEW
RC_BACKUP_SET VIEW
RC_BACKUP_PIECE VIEW
RC_BACKUP_DATAFILE VIEW
RC_BACKUP_CONTROLFILE VIEW
RC_BACKUP_SPFILE VIEW
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RC_DATAFILE_COPY VIEW
RC_CONTROLFILE_COPY VIEW
RC_BACKUP_REDOLOG VIEW
RC_BACKUP_CORRUPTION VIEW
RC_COPY_CORRUPTION VIEW
RC_OFFLINE_RANGE VIEW
RC_STORED_SCRIPT VIEW
RC_STORED_SCRIPT_LINE VIEW
RC_PROXY_DATAFILE VIEW
RC_PROXY_CONTROLFILE VIEW
RC_RMAN_CONFIGURATION VIEW
RC_DATABASE_BLOCK_CORRUPTION VIEW
RC_PROXY_ARCHIVEDLOG VIEW
RC_RMAN_STATUS VIEW
ROUT TABLE
RC_RMAN_OUTPUT VIEW
RCVER TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RC_BACKUP_FILES VIEW
RC_RMAN_BACKUP_SUBJOB_DETAILS VIEW
RC_RMAN_BACKUP_JOB_DETAILS VIEW
RC_BACKUP_SET_DETAILS VIEW
RC_BACKUP_PIECE_DETAILS VIEW
RC_BACKUP_COPY_DETAILS VIEW
RC_PROXY_COPY_DETAILS VIEW
RC_PROXY_ARCHIVELOG_DETAILS VIEW
RC_BACKUP_DATAFILE_DETAILS VIEW
RC_BACKUP_CONTROLFILE_DETAILS VIEW
RC_BACKUP_ARCHIVELOG_DETAILS VIEW
RC_BACKUP_SPFILE_DETAILS VIEW
RC_BACKUP_SET_SUMMARY VIEW
RC_BACKUP_DATAFILE_SUMMARY VIEW
RC_BACKUP_CONTROLFILE_SUMMARY VIEW
RC_BACKUP_ARCHIVELOG_SUMMARY VIEW
RC_BACKUP_SPFILE_SUMMARY VIEW
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RC_BACKUP_COPY_SUMMARY VIEW
RC_PROXY_COPY_SUMMARY VIEW
RC_PROXY_ARCHIVELOG_SUMMARY VIEW
RC_UNUSABLE_BACKUPFILE_DETAILS VIEW
RC_RMAN_BACKUP_TYPE VIEW
90 rows selected.
02:50:19 SQL>
--------注册目标库（将目标库controlfile的rman 元数据 同步到 catalog database）
[oracle@work admin]$ rman target / catalog rman/rman@catdb
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Aug 19 02:52:19 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=170319990)
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
---------------查看注册信息
02:50:19 SQL> desc rc_database;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
DB_KEY NOT NULL NUMBER
DBINC_KEY NUMBER
DBID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(8)
RESETLOGS_CHANGE# NOT NULL NUMBER
RESETLOGS_TIME NOT NULL DATE
02:54:28 SQL> select * from rc_database;
DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
1 2 170319990 PROD 1452590 19-AUG-11
02:54:34 SQL>
----------利用catalog database存放rman 脚本
1）创建脚本（replace 是修改已经存在的脚本）
RMAN> create script users_bak {
2> backup datafile '/u01/app/oracle/oradata/prod/users01.dbf' format '/disk1/rman/prod/users_%s.bak';
3> }
created script users_bak
2）查看脚本信息
RMAN> print script users_bak;
printing stored script: users_bak
{backup datafile '/u01/app/oracle/oradata/prod/users01.dbf' format '/disk1/rman/prod/users_%s.bak';
}
------通过catalog database 查看
03:02:45 SQL> col SCRIPT_NAME for a30
03:02:54 SQL> col SCRIPT_COMMENT for a50
03:03:01 SQL>
1* select * from RC_STORED_SCRIPT
DB_KEY DB_NAME SCRIPT_NAME SCRIPT_COMMENT
---------- -------- ------------------------------ --------------------------------------------------
1 PROD users_bak
03:03:23 SQL> col text for a50
03:03:30 SQL>
1* select * from RC_STORED_SCRIPT_LINE
DB_KEY SCRIPT_NAME LINE TEXT
---------- ------------------------------ ---------- --------------------------------------------------
1 users_bak 1 {backup datafile '/u01/app/oracle/oradata/prod/us
ers01.dbf' format '/disk1/rman/prod/users_%s.bak';
1 users_bak 2 }
3）运行脚本
RMAN> run { execute script users_bak;}
4） 删除脚本
RMAN> delete script users_bak;
deleted script: users_bak
验证catalog的恢复功能：
1、模拟环境，target database 的controlfile 都丢失
17:38:54 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
17:39:40 SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@RH54 ~]$ rm /u01/app/oracle/oradata/test2/control01.ctl
[oracle@RH54 ~]$ rm /disk1/oradata/test2/control02.ctl
[oracle@RH54 ~]$ rm /disk2/oradata/test2/control03.ctl
[oracle@RH54 ~]$ !sql
sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jun 2 17:40:44 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
17:40:45 SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219160 bytes
Variable Size 109053352 bytes
Database Buffers 201326592 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
17:40:50 SQL>
17:40:50 SQL> select status from v$instance;
STATUS
------------
STARTED
17:41:58 SQL>
2、通过rman 连接到目标库恢复控制文件
[oracle@RH54 admin]$ rman target / catalog rman/rman@t1
Recovery Manager: Release 10.2.0.1.0 - Production on Sat Jun 2 17:41:02 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: test2 (not mounted)
connected to recovery catalog database
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
721 Full 329.22M DISK 00:00:48 02-JUN-12
BP Key: 722 Status: AVAILABLE Compressed: NO Tag: TAG20120602T173714
Piece Name: /disk3/rman/cold_bak/TEST2_7.bak
List of Datafiles in backup set 721
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 832486 02-JUN-12 /u01/app/oracle/oradata/test2/system01.dbf
2 Full 832486 02-JUN-12 /u01/app/oracle/oradata/test2/rbs01.dbf
3 Full 832486 02-JUN-12 /u01/app/oracle/oradata/test2/sysaux01.dbf
4 Full 832486 02-JUN-12 /u01/app/oracle/oradata/test2/users01.dbf
5 Full 832486 02-JUN-12 /u01/app/oracle/oradata/test2/test1.dbf
6 Full 832486 02-JUN-12 /u01/app/oracle/oradata/test2/tbs1.dbf
8 Full 832486 02-JUN-12 /u01/app/oracle/oradata/test2/smallundo01.dbf
10 Full 832486 02-JUN-12 /u01/app/oracle/oradata/test2/indx01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
735 Full 6.95M DISK 00:00:02 02-JUN-12
BP Key: 744 Status: AVAILABLE Compressed: NO Tag: TAG20120602T173812
Piece Name: /disk1/recovery/TEST2/autobackup/2012_06_02/o1_mf_s_784921007_7wmqr61r_.bkp
Control File Included: Ckp SCN: 832486 Ckp time: 02-JUN-12
SPFILE Included: Modification time: 02-JUN-12
RMAN>
RMAN> restore controlfile from autobackup;
Starting restore at 02-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=35 devtype=DISK
recovery area destination: /disk1/recovery
database name (or database unique name) used for search: TEST2
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /disk1/recovery/TEST2/autobackup/2012_06_02/o1_mf_s_784921007_7wmqr61r_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/app/oracle/oradata/test2/control01.ctl
output filename=/disk1/oradata/test2/control02.ctl
output filename=/disk2/oradata/test2/control03.ctl
Finished restore at 02-JUN-12
RMAN>
3、controlfile 恢复完成
17:41:58 SQL> alter database mount;
Database altered.
17:43:56 SQL> select name from v$controlfile;
NAME
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/test2/control01.ctl
/disk1/oradata/test2/control02.ctl
/disk2/oradata/test2/control03.ctl
17:44:04 SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
17:44:08 SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/test2/system01.dbf'
17:44:14 SQL> select file# ,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 832486
2 832486
3 832486
4 832486
5 832486
6 832486
8 832486
10 832486
8 rows selected.
17:44:42 SQL> select file# ,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 832681
2 832681
3 832681
4 832681
5 832681
6 832681
8 832681
10 832681
8 rows selected.
17:44:48 SQL>
4、恢复database
RMAN> restore database;
Starting restore at 02-JUN-12
released channel: ORA_DISK_1
Starting implicit crosscheck backup at 02-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=35 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 02-JUN-12
Starting implicit crosscheck copy at 02-JUN-12
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 02-JUN-12
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /disk1/recovery/TEST2/autobackup/2012_06_02/o1_mf_s_784917866_7wmnozor_.bkp
File Name: /disk1/recovery/TEST2/autobackup/2012_06_02/o1_mf_s_784921007_7wmqr61r_.bkp
File Name: /disk1/recovery/TEST2/autobackup/2012_06_02/o1_mf_s_784918355_7wmo2mtb_.bkp
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/test2/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/test2/rbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/test2/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/test2/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test2/test1.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test2/tbs1.dbf
restoring datafile 00008 to /u01/app/oracle/oradata/test2/smallundo01.dbf
restoring datafile 00010 to /u01/app/oracle/oradata/test2/indx01.dbf
channel ORA_DISK_1: reading from backup piece /disk3/rman/cold_bak/TEST2_7.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/disk3/rman/cold_bak/TEST2_7.bak tag=TAG20120602T173714
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 02-JUN-12
RMAN>
17:44:48 SQL> select file# ,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 832486
2 832486
3 832486
4 832486
5 832486
6 832486
8 832486
10 832486
8 rows selected.
17:46:49 SQL> select file# ,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 832486
2 832486
3 832486
4 832486
5 832486
6 832486
8 832486
10 832486
8 rows selected.
17:46:51 SQL>
17:46:51 SQL> alter database open resetlogs;
Database altered.
17:47:32 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 0 10485760 2 YES UNUSED 0
4 1 1 10485760 2 NO CURRENT 832487 2012-06-02 17:47:13
3 1 0 10485760 2 YES UNUSED 0
2 1 0 10485760 2 YES UNUSED 0
17:47:44 SQL> alter system switch logfile;
System altered.
17:47:52 SQL> /
System altered.
17:47:53 SQL> /
System altered.
17:47:57 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 2 10485760 2 YES ACTIVE 832633 2012-06-02 17:47:52
4 1 1 10485760 2 YES ACTIVE 832487 2012-06-02 17:47:13
3 1 4 10485760 2 NO CURRENT 832637 2012-06-02 17:47:57
2 1 3 10485760 2 YES ACTIVE 832635 2012-06-02 17:47:53
17:47:59 SQL>
第十二章： rman 维护
1、rman 使用和维护
2、list 查看备份信息
1）RMAN> list backup;
2）RMAN> list backup of tablespace users；
3）RMAN> list backup of datafile 2；
4）RMAN> list backup of controlfile;
5) RMAN> list backup of archivelog all;
6）RMAN> list backup of archivelog until time ='sysdate -1';
7）RMAN> list backupset 56;
8）RMAN> list copy；
9）RMAN> list copy of tablespace users；
10）RMAN> list copy of datafile 1；
11）RMAN> list datafilecopy 67；
12）RMAN> list copy of controlfile;
3、crosscheck：用于检测备份状态（AVAILABLE：可用；UNAVAILABLE ：不可用；expired 过期（备份已经被os 删除了）（防止备份在os下被误删除）
RMAN> crosscheck backup;
RMAN> crosscheck copy；
4、report
RMAN> report schema; 查看目标库的物理结构
RMAN> report need backup; 查看需要做备份的datafile
RMAN> report obsolete; 根据备份冗余策略来判断，那些备份是陈旧的（obsolete）
5、delete ；删除备份
RMAN> delete backup of datafile 2;
RMAN> delete backup of tablespace system；
RMAN> delete backup ；
RMAN> delete backup of controlfile；
RMAN> delete noprompt backup of controlfile; ；删除noprompt 不提示
RMAN> delete copy;
RMAN> delete copy of datafile 10;
RMAN> delete copy of tablespace users；
RMAN> delete expired backup； 删除过期的备份
RMAN> delete expired copy；
RMAN> delete obsolete ；删除陈旧的备份
RMAN> delete noprompt obsolete； 删除不加提示
6、catalog ：可以把手工备份的文件，转换到rman
-------将手工备份注册为image
RMAN> catalog datafilecopy '/disk1/backup/prod/hot_bak/users01.dbf';
cataloged datafile copy
datafile copy filename=/disk1/backup/prod/hot_bak/users01.dbf recid=68 stamp=759556193
RMAN> list copy of datafile 2;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
68 2 A 19-AUG-11 1457868 19-AUG-11 /disk1/backup/prod/hot_bak/users01.dbf
RMAN> delete copy of datafile 2;
-------将手工备份注册成备份集
RMAN> catalog backuppiece '/disk1/backup/prod/hot_bak/users01.dbf';
---------注册目录下所有的备份（image）
RMAN> catalog start with ‘/disk1/backup/prod/hot_bak/’；
7、change: 修改备份状态(用于对备份进行维护）
RMAN> change backupset 66 unAVAILABLE;
RMAN> change backupset 66 AVAILABLE;
第十三章：逻辑备份
1、逻辑备份和物理备份区别
1）物理备份：备份数据文件以及控制文件，备份整个库的物理结构，主要用于media recover
2）逻辑备份：备份database里的对象的数据结构和数据，作为物理备份的辅助，不能用于media recover ；先主要用于对单个表的备份或数据迁移
恢复时只能恢复到备份点
2、exp/imp的使用(expdp/impdp)
emp 导出
C:\Documents and Settings\liqx>exp -help
Export: Release 10.2.0.1.0 - Production on 星期五 8月 19 13:43:49 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
通过输入 EXP 命令和您的用户名/口令, 导出
操作将提示您输入参数:
例如: EXP SCOTT/TIGER
或者, 您也可以通过输入跟有各种参数的 EXP 命令来控制导出
的运行方式。要指定参数, 您可以使用关键字:
格式: EXP KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)
例如: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
或 TABLES=(T1:P1,T1:P2), 如果 T1 是分区表
USERID 必须是命令行中的第一个参数。
关键字 说明 (默认值) 关键字 说明 (默认值)
--------------------------------------------------------------------------
USERID 用户名/口令 FULL 导出整个文件 (N)
BUFFER 数据缓冲区大小 OWNER 所有者用户名列表
FILE 输出文件 (EXPDAT.DMP) TABLES 表名列表
COMPRESS 导入到一个区 (Y) RECORDLENGTH IO 记录的长度
GRANTS 导出权限 (Y) INCTYPE 增量导出类型
INDEXES 导出索引 (Y) RECORD 跟踪增量导出 (Y)
DIRECT 直接路径 (N) TRIGGERS 导出触发器 (Y)
LOG 屏幕输出的日志文件 STATISTICS 分析对象 (ESTIMATE)
ROWS 导出数据行 (Y) PARFILE 参数文件名
CONSISTENT 交叉表的一致性 (N) CONSTRAINTS 导出的约束条件 (Y)
OBJECT_CONSISTENT 只在对象导出期间设置为只读的事务处理 (N)
FEEDBACK 每 x 行显示进度 (0)
FILESIZE 每个转储文件的最大大小
FLASHBACK_SCN 用于将会话快照设置回以前状态的 SCN
FLASHBACK_TIME 用于获取最接近指定时间的 SCN 的时间
QUERY 用于导出表的子集的 select 子句
RESUMABLE 遇到与空格相关的错误时挂起 (N)
RESUMABLE_NAME 用于标识可恢复语句的文本字符串
RESUMABLE_TIMEOUT RESUMABLE 的等待时间
TTS_FULL_CHECK 对 TTS 执行完整或部分相关性检查
TABLESPACES 要导出的表空间列表
TRANSPORT_TABLESPACE 导出可传输的表空间元数据 (N)
TEMPLATE 调用 iAS 模式导出的模板名
成功终止导出, 没有出现警告。
exp
1）导出整个database
[oracle@work dat]$ exp system/oracle file=/home/oracle/dat/prod.dmp log=/home/oracle/dat/prod.log indexes=n full=y
2) 导出schema 所有的对象
[oracle@work dat]$ exp scott/tiger file=scott.dmp log=scott.log indexes=n owner=scott
3）导出单个表
[oracle@work dat]$ exp scott/tiger file=emp_dept.dmp log=table.log indexes=n tables=(emp,dept);
-------按条件导出表里的数据
[oracle@work dat]$ exp scott/tiger file=emp_30.dmp log=emp.log indexes=n tables=emp query=\' where deptno=30\'
imp 导入
C:\Documents and Settings\liqx>imp help=y
Import: Release 10.2.0.1.0 - Production on 星期五 8月 19 13:59:20 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
通过输入 IMP 命令和您的用户名/口令, 导入
操作将提示您输入参数:
例如: IMP SCOTT/TIGER
或者, 可以通过输入 IMP 命令和各种参数来控制导入
的运行方式。要指定参数, 您可以使用关键字:
格式: IMP KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)
例如: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N
或 TABLES=(T1:P1,T1:P2), 如果 T1 是分区表
USERID 必须是命令行中的第一个参数。
关键字 说明 (默认值) 关键字 说明 (默认值)
-----------------------------------------------------------------------
USERID 用户名/口令 FULL 导入整个文件 (N)
BUFFER 数据缓冲区大小 FROMUSER 所有者用户名列表
FILE 输入文件 (EXPDAT.DMP) TOUSER 用户名列表
SHOW 只列出文件内容 (N) TABLES 表名列表
IGNORE 忽略创建错误 (N) RECORDLENGTH IO 记录的长度
GRANTS 导入权限 (Y) INCTYPE 增量导入类型
INDEXES 导入索引 (Y) COMMIT 提交数组插入 (N)
ROWS 导入数据行 (Y) PARFILE 参数文件名
LOG 屏幕输出的日志文件 CONSTRAINTS 导入限制 (Y)
DESTROY 覆盖表空间数据文件 (N)
INDEXFILE 将表/索引信息写入指定的文件
SKIP_UNUSABLE_INDEXES 跳过不可用索引的维护 (N)
FEEDBACK 每 x 行显示进度 (0)
TOID_NOVALIDATE 跳过指定类型 ID 的验证
FILESIZE 每个转储文件的最大大小
STATISTICS 始终导入预计算的统计信息
RESUMABLE 在遇到有关空间的错误时挂起 (N)
RESUMABLE_NAME 用来标识可恢复语句的文本字符串
RESUMABLE_TIMEOUT RESUMABLE 的等待时间
COMPILE 编译过程, 程序包和函数 (Y)
STREAMS_CONFIGURATION 导入流的一般元数据 (Y)
STREAMS_INSTANTIATION 导入流实例化元数据 (N)
下列关键字仅用于可传输的表空间
TRANSPORT_TABLESPACE 导入可传输的表空间元数据 (N)
TABLESPACES 将要传输到数据库的表空间
DATAFILES 将要传输到数据库的数据文件
TTS_OWNERS 拥有可传输表空间集中数据的用户
------imp 在导入的时候，先创建对象，建立数据结构，然后再导入数据
Oracle在恢复数据的过程中，当恢复某个表时，该表已经存在，就要根据ignore参数的设置来决定如何操作。
若ignore=y，Oracle不执行CREATE TABLE语句，直接将数据插入到表中，如果插入的记录违背了约束条件，
比如主键约束，则出错的记录不会插入，但合法的记录会添加到表中。
若ignore=n，Oracle执行CREATE TABLE语句，同时也不会将数据插入到表中，而是忽略该表的错误，继续恢复下一个表。
[oracle@work dat]$ imp scott/tiger file=emp_dept.dmp tables=emp fromuser=scott touser=scott ignore=y
-------如果，两个对象之间有参考性约束（主外键），应该先导入父表，再导入子表
[oracle@work dat]$ imp scott/tiger file=emp_dept.dmp tables=dept fromuser=scott touser=scott ignore=y
[oracle@work dat]$ imp scott/tiger file=emp_dept.dmp tables=emp fromuser=scott touser=scott ignore=y
案例：
----------误删除user及所有的对象，恢复
1）创建用户，并分配权限
06:14:01 SQL> create user scott identified by tiger default tablespace users ;
User created.
06:15:55 SQL> grant connect ,resource to scott;
Grant succeeded.
06:16:06 SQL>
2）导入备份
[oracle@work dat]$ imp system/oracle file=scott.dmp ignore=y fromuser=scott touser=scott;
Import: Release 10.2.0.1.0 - Production on Fri Aug 19 06:16:36 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SCOTT, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. . importing table "BONUS" 0 rows imported
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported
. . importing table "EMP1" 512 rows imported
. . importing table "LX01" 9 rows imported
. . importing table "SALGRADE" 5 rows imported
. . importing table "TEST" 10 rows imported
About to enable constraints...
Import terminated successfully without warnings.
[oracle@work dat]$
3）验证
06:17:11 SQL> conn scott/tiger
Connected.
06:17:16 SQL>
06:17:16 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
EMP1 TABLE
LX01 TABLE
SALGRADE TABLE
SYS_TEMP_FBT TABLE
TEST TABLE
8 rows selected.
-------------利用exp/imp 传输表空间
1、建立表空间
17:08:41 SQL> col file_name for a50
17:08:48 SQL> select file_id,file_name,tablespace_name from dba_data_files
17:08:57 2 ;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
4 /u01/app/oracle/oradata/ocmtest1/users01.dbf USERS
3 /u01/app/oracle/oradata/ocmtest1/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/ocmtest1/undotbs01.dbf UNDOTBS1
1 /u01/app/oracle/oradata/ocmtest1/system01.dbf SYSTEM
5 /u01/app/oracle/oradata/ocmtest1/example01.dbf EXAMPLE
17:08:58 SQL> create tablespace test
17:09:05 2 datafile '/u01/app/oracle/oradata/ocmtest1/test01.dbf' size 100m;
Tablespace created.
17:09:18 SQL> conn scott/tiger
Connected.
17:09:23 SQL>
17:09:29 SQL> create table t01 (id int) tablespace test;
Table created.
17:09:33 SQL> insert into t01 values (1);
1 row created.
17:09:38 SQL> insert into t01 values (2);
1 row created.
17:09:39 SQL> insert into t01 values (3);
1 row created.
17:09:40 SQL> commit;
Commit complete.
17:09:41 SQL> conn /as sysdba
Connected.
17:10:12 SQL>
17:10:12 SQL> alter tablespace test read only;
Tablespace altered.
2、查看表空间是否自包含
17:10:20 SQL> exec sys.dbms_tts.transport_set_check('test',true);
PL/SQL procedure successfully completed.
17:11:37 SQL> select * from sys.transport_set_violations;
no rows selected
17:11:43 SQL>
将传输的tablespace 设置成 read only 模式。
15:37:37 SQL> alter tablespace test read only;
Tablespace altered.
3、导出可传输表空间（必须以sysdba权限）
[oracle@ocmtest1 ~]$ exp userid=\'sys/oracle as sysdba\' file=test.dmp log=test.log transport_tablespace=y tablespaces=test
Export: Release 10.2.0.1.0 - Production on Mon Aug 15 17:13:24 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TEST ...
. exporting cluster definitions
. exporting table definitions
. . exporting table T01
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
[oracle@ocmtest1 ~]$
4、启动另外一个数据库
[oracle@ocmtest1 ~]$ export ORACLE_SID=catdb
[oracle@ocmtest1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 15 17:15:25 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
17:15:25 SQL>
17:15:25 SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 75499764 bytes
Database Buffers 88080384 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
17:15:41 SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
CATTBS
7 rows selected.
5、在目标数据库下做imp操作，导入表空间
[oracle@ocmtest1 ~]$ cp /u01/app/oracle/oradata/ocmtest1/test01.dbf /u01/app/oracle/oradata/catdb/
-------------将表空间数据文件从源数据库拷贝到目标数据库下
[oracle@ocmtest1 ~]$ imp userid=\'sys/oracle as sysdba\' tablespaces=test file=test.dmp transport_tablespace=y datafiles=/u01/app/oracle/oradata/catdb/test01.dbf
Import: Release 10.2.0.1.0 - Production on Mon Aug 15 17:17:23 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table "T01"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
6、验证
[oracle@ocmtest1 ~]$
17:16:53 SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
CATTBS
TEST
8 rows selected.
17:17:28 SQL> select * from scott.t01;
ID
----------
1
2
3
17:17:33 SQL>
17:28:03 SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
CATTBS ONLINE
TEST READ ONLY
8 rows selected.
17:28:12 SQL> alter tablespace test read write;
Tablespace altered.
17:28:20 SQL>
expdp/impdp
1、建立目录
03:51:20 SQL> create directory dump_dir as '/home/oracle/data';
Directory created.
2、给用户使用目录授权
03:52:15 SQL> grant read,write on directory dump_dir to public;
Grant succeeded.
03:52:56 SQL> select * from dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
------------------------------------------------------------------------------------------------------------------------------------------------------
SYS DATA_PUMP_DIR
/u01/app/oracle/product/10.2.0/db_1/rdbms/log/
SYS DUMP_DIR
/home/oracle/data
03:53:06 SQL> !
3、导出表
[oracle@RH4 ~]$ expdp -help
Export: Release 10.2.0.1.0 - Production on Sunday, 22 April, 2012 3:53:42
Copyright (c) 2003, 2005, Oracle. All rights reserved.
The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:
Format: expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
COMPRESSION Reduce size of dumpfile contents where valid
keyword values are: (METADATA_ONLY) and NONE.
CONTENT Specifies data to unload where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dumpfiles and logfiles.
DUMPFILE List of destination dump files (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD Password key for creating encrypted column data.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
ESTIMATE_ONLY Calculate job estimates without performing the export.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FILESIZE Specify the size of each dumpfile in units of bytes.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Export entire database (N).
HELP Display Help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of export job to create.
LOGFILE Log file name (export.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile (N).
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to export a subset of a table.
SAMPLE Percentage of data to be exported;
SCHEMAS List of schemas to export (login schema).
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
TABLES Identifies a list of tables to export - one schema only.
TABLESPACES Identifies a list of tablespaces to export.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
The following commands are valid while in interactive mode.
Note: abbreviations are allowed
Command Description
------------------------------------------------------------------------------
ADD_FILE Add dumpfile to dumpfile set.
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
FILESIZE Default filesize (bytes) for subsequent ADD_FILE commands.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=
START_JOB Start/resume current job.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS[=interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.
[oracle@RH4 ~]$ expdp scott/tiger directory=dump_dir dumpfile=emp_dept_edp.dmp tables=(emp,dept)
Export: Release 10.2.0.1.0 - Production on Sunday, 22 April, 2012 3:55:10
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=dump_dir dumpfile=emp_dept_edp.dmp tables=(emp,dept)
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/AUDIT_OBJ
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.828 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/data/emp_dept_edp.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 03:55:34
4、导入数据到表
[oracle@RH4 data]$ impdp -help
Import: Release 10.2.0.1.0 - Production on Sunday, 22 April, 2012 3:57:10
Copyright (c) 2003, 2005, Oracle. All rights reserved.
The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:
Format: impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
USERID must be the first parameter on the command line.
Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to load where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dump, log, and sql files.
DUMPFILE List of dumpfiles to import from (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD Password key for accessing encrypted column data.
This parameter is not valid for network import jobs.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Import everything from source (Y).
HELP Display help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of import job to create.
LOGFILE Log file name (import.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile.
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to import a subset of a table.
REMAP_DATAFILE Redefine datafile references in all DDL statements.
REMAP_SCHEMA Objects from one schema are loaded into another schema.
REMAP_TABLESPACE Tablespace object are remapped to another tablespace.
REUSE_DATAFILES Tablespace will be initialized if it already exists (N).
SCHEMAS List of schemas to import.
SKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state.
SQLFILE Write all the SQL DDL to a specified file.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STREAMS_CONFIGURATION Enable the loading of Streams metadata
TABLE_EXISTS_ACTION Action to take if imported object already exists.
Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.
TABLES Identifies a list of tables to import.
TABLESPACES Identifies a list of tablespaces to import.
TRANSFORM Metadata transform to apply to applicable objects.
Valid transform keywords: SEGMENT_ATTRIBUTES, STORAGE
OID, and PCTSPACE.
TRANSPORT_DATAFILES List of datafiles to be imported by transportable mode.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.
The following commands are valid while in interactive mode.
Note: abbreviations are allowed
Command Description (Default)
------------------------------------------------------------------------------
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=
START_JOB Start/resume current job.
START_JOB=SKIP_CURRENT will start the job after skipping
any action which was in progress when job was stopped.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS[=interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.
[oracle@RH4 ~]$
03:56:34 SQL> conn scott/tiger
Connected.
03:56:39 SQL> drop table emp purge;
Table dropped.
03:56:48 SQL> drop table dept purge;
Table dropped.
CONTENT Specifies data to unload where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
[oracle@RH4 data]$ export ORACLE_SID=cuug
[oracle@RH4 data]$ impdp scott/tiger directory=dump_dir dumpfile=emp_dept_edp.dmp tables=dept
[oracle@RH4 data]$impdp scott/tiger directory=dmp_dir dumpfile=emp1.dmp tables=emp1 CONTENT=data_only
Import: Release 10.2.0.1.0 - Production on Sunday, 22 April, 2012 3:59:35
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=dump_dir dumpfile=emp_dept_edp.dmp tables=dept
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT" 5.656 KB 4 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 03:59:43
[oracle@RH4 data]$
content metadata_only 只导入表结构（创建空表，建立索引、约束等）
01:59:33 SQL> drop table emp purge;
Table dropped.
01:59:43 SQL> drop table dept purge;
Table dropped.
[oracle@RH54 data]$ impdp scott/tiger directory=dumpdir dumpfile=emp_dept.dmp tables=dept content=metadata_only
Import: Release 10.2.0.1.0 - Production on Tuesday, 19 June, 2012 2:00:17
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=dumpdir dumpfile=emp_dept.dmp tables=dept content=metadata_only
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 02:00:28
01:59:48 SQL> select * from dept;
no rows selected
02:00:42 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SYS_TEMP_FBT TABLE
BONUS TABLE
DEPT1 TABLE
DEPT2 TABLE
EMP1 TABLE
EMP2 TABLE
EXCEPTIONS TABLE
SALGRADE TABLE
DEPT TABLE
9 rows selected.
02:00:57 SQL> select index_name,table_name from user_indexes
02:01:18 2 where table_name='DEPT';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
PK_DEPT DEPT
02:01:29 SQL> SELECT constraint_name,table_name from user_constraints
02:01:57 2 where table_name='DEPT';
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
PK_DEPT DEPT
[oracle@RH54 data]$ impdp scott/tiger directory=dumpdir dumpfile=emp_dept.dmp tables=dept content=data_only
Import: Release 10.2.0.1.0 - Production on Tuesday, 19 June, 2012 2:02:17
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=dumpdir dumpfile=emp_dept.dmp tables=dept content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT" 5.656 KB 4 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 02:02:28
默认 content 为 all （包括metadata_only 和data_only）
[oracle@RH54 data]$ impdp scott/tiger directory=dumpdir dumpfile=emp_dept.dmp tables=emp
Import: Release 10.2.0.1.0 - Production on Tuesday, 19 June, 2012 2:02:39
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=dumpdir dumpfile=emp_dept.dmp tables=emp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP" 7.843 KB 14 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 02:02:52
[oracle@RH54 data]$
02:02:03 SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 1000 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 1000 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 1000 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 1000 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1000 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 1000 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 1000 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 1000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 1000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 1000 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 1000 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 1000 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 1000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 1000 10
14 rows selected.
02:03:06 SQL>
-----expdp默认将导出表的元数据、数据、索引、约束、对象权限
----以下参数用于排除索引
[oracle@RH54 data]$ expdp scott/tiger dumpfile=scottdp.dmp logfile=empdp.log directory=dmpdir tables=emp EXCLUDE=INDEX:"IN('PK_EMP')"
Export: Release 10.2.0.1.0 - Production on Wednesday, 18 July, 2012 3:51:32
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** dumpfile=scottdp.dmp logfile=empdp.log directory=dmpdir tables=emp EXCLUDE=INDEX:IN('PK_EMP')
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/data/scottdp.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 03:51:48
----在导出schema时，排除table
[oracle@RH54 data]$ expdp scott/tiger dumpfile=scottdp.dmp logfile=empdp.log directory=dmpdir schemaS=scott exclude=table:\"in\( \'EMP\',\'DEPT\' \)\"
Export: Release 10.2.0.1.0 - Production on Wednesday, 18 July, 2012 3:54:28
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** dumpfile=scottdp.dmp logfile=empdp.log directory=dmpdir schemaS=scott exclude=table:"in( 'EMP','DEPT' )"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT1" 5.75 KB 8 rows
. . exported "SCOTT"."DEPT2" 5.656 KB 4 rows
. . exported "SCOTT"."EMP1" 7.859 KB 15 rows
. . exported "SCOTT"."EMP2" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/data/scottdp.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 03:55:01
[oracle@RH54 data]$
------排除单个table
[oracle@RH54 data]$ expdp scott/tiger dumpfile=scottdp.dmp logfile=empdp.log directory=dmpdir schemaS=scott exclude=table:"in('EMP')"
Export: Release 10.2.0.1.0 - Production on Wednesday, 18 July, 2012 3:57:01
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** dumpfile=scottdp.dmp logfile=empdp.log directory=dmpdir schemaS=scott exclude=table:in('EMP')
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."DEPT1" 5.75 KB 8 rows
. . exported "SCOTT"."DEPT2" 5.656 KB 4 rows
. . exported "SCOTT"."EMP1" 7.859 KB 15 rows
. . exported "SCOTT"."EMP2" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/data/scottdp.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 03:57:28
[oracle@RH54 data]$
---------通过参数文件parfile ，做导出
[oracle@RH54 data]$ vi exl.par
userid=scott/tiger
directory=dmpdir
dumpfile=scottdp2.dmp
logfile=scott.log
exclude=table:"in('EMP','DEPT')"
~
[oracle@RH54 data]$ expdp parfile=exl.par
Export: Release 10.2.0.1.0 - Production on Wednesday, 18 July, 2012 4:01:21
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": parfile=exl.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT1" 5.75 KB 8 rows
. . exported "SCOTT"."DEPT2" 5.656 KB 4 rows
. . exported "SCOTT"."EMP1" 7.859 KB 15 rows
. . exported "SCOTT"."EMP2" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/data/scottdp2.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 04:01:46
[oracle@RH54 data]$
-----impdp 导入时，默认为content=all ，将建立table 和导入数据，metadata_only 只导入元数据（即建立空表）
[oracle@RH54 data]$ impdp scott/tiger directory=dmpdir dumpfile=empdp.dmp logfile=empdp.log tables=emp content=metadata_only
Import: Release 10.2.0.1.0 - Production on Wednesday, 18 July, 2012 4:08:28
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=dmpdir dumpfile=empdp.dmp logfile=empdp.log tables=emp content=metadata_only
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 04:08:40
-----data_only 只导入数据
[oracle@RH54 data]$ impdp scott/tiger directory=dmpdir dumpfile=empdp.dmp logfile=empdp.log tables=emp content=data_only
Import: Release 10.2.0.1.0 - Production on Wednesday, 18 July, 2012 4:09:05
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=dmpdir dumpfile=empdp.dmp logfile=empdp.log tables=emp content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP" 7.820 KB 14 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 04:09:18
[oracle@RH54 data]$
在同一台机器上，通过expdp/impdp 传输表空间
1、目标库 test3
TEST3 DATABASE:
02:12:52 SQL> col file_name for a50
02:12:59 SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
1 /u01/app/oracle/oradata/test3/system01.dbf SYSTEM
4 /u01/app/oracle/oradata/test3/users01.dbf USERS
3 /u01/app/oracle/oradata/test3/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/test3/rtbs01.dbf RTBS
5 /u01/app/oracle/oradata/test3/users02.dbf USERS
6 /u01/app/oracle/oradata/test3/test1.dbf TEST1
7 /u01/app/oracle/oradata/test3/test2.dbf TEST2
9 /u01/app/oracle/oradata/test3/indx01.dbf INDX
8 rows selected.
02:13:13 SQL>
02:17:48 SQL> conn tom/tom
Connected.
02:17:56 SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TOM SELECT ANY TABLE NO
TOM CREATE SESSION NO
TOM UNLIMITED TABLESPACE NO
02:18:02 SQL>
02:18:02 SQL> select userenv('LANGUAGE') FROM DUAL;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
02:19:59 SQL> create directory dumpdir as '/home/oracle/data';
Directory created.
02:20:34 SQL>
2、源库 test2
TEST2 DATABASE:
-------检测tablespace 的自包含
02:13:31 SQL> col file_name for a50
02:13:33 SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
1 /u01/app/oracle/oradata/test2/system01.dbf SYSTEM
10 /u01/app/oracle/oradata/test2/indx01.dbf INDX
8 /u01/app/oracle/oradata/test2/smallundo01.dbf SMALLUNDO
6 /u01/app/oracle/oradata/test2/tbs1.dbf TBS1
5 /u01/app/oracle/oradata/test2/test1.dbf TEST1
4 /u01/app/oracle/oradata/test2/users01.dbf USERS
3 /u01/app/oracle/oradata/test2/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/test2/rbs01.dbf RTBS
7 /u01/app/oracle/oradata/test2/rmantbs1.dbf RMANTBS
9 /u01/app/oracle/oradata/test2/test2.dbf TEST2
10 rows selected.
02:13:46 SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tbs1',true);
PL/SQL procedure successfully completed.
DBMS_TTS.TRANSPORT_SET_CHECK (
ts_list IN CLOB,
incl_constraints IN BOOLEAN DEFAULT FALSE, -- 是否包含约束
full_check IN BOOLEAN DEFAULT FALSE); -- false时 检查是否依赖其他表空间， true 反向也检查
02:15:55 SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
02:16:07 SQL> select table_name ,tablespace_name ,owner from dba_tables
02:16:27 2 where tablespace_name='TBS1';
TABLE_NAME TABLESPACE_NAME OWNER
------------------------------ ------------------------------ ------------------------------
TB1 TBS1 SCOTT
DEPT2 TBS1 SCOTT
02:16:36 SQL>
-------检测数据库的字符集（源库与目标库的字符集必须相同）
02:18:02 SQL> select userenv('LANGUAGE') FROM DUAL;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
02:19:59 SQL> create directory dumpdir as '/home/oracle/data';
Directory created.
02:20:34 SQL>
--------通过expdp 导出表空间元数据
[oracle@RH54 data]$ expdp system/oracle directory=dumpdir dumpfile=tbs1.dmp logfile=tbs.log TRANSPORT_TABLESPACES =tbs1
Export: Release 10.2.0.1.0 - Production on Tuesday, 19 June, 2012 2:23:01
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=dumpdir dumpfile=tbs1.dmp logfile=tbs.log TRANSPORT_TABLESPACES =tbs1
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/data/tbs1.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 02:23:24
[oracle@RH54 data]$ pwd
/home/oracle/data
[oracle@RH54 data]$ ls
tbs1.dmp tbs.log
[oracle@RH54 data]$
------将源库传输表空间的datafile 传送到另一个库上
test3 database 和 test2 database 在同一台机器上
[oracle@RH54 data]$ cp /u01/app/oracle/oradata/test2/tbs1.dbf /u01/app/oracle/oradata/test3/
3、在目标库上用impdp导入表空间（并且将表空间上的对象的schema 转换成另外一个schema ，如scott的对象转换到tom）
[oracle@RH54 data]$ impdp system/oracle directory=dumpdir dumpfile=tbs1.dmp TRANSPORT_DATAFILES='/u01/app/oracle/oradata/test3/tbs1.dbf' REMAP_SCHEMA=\(scott:tom\)
Import: Release 10.2.0.1.0 - Production on Tuesday, 19 June, 2012 2:32:56
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=dumpdir dumpfile=tbs1.dmp TRANSPORT_DATAFILES=/u01/app/oracle/oradata/test3/tbs1.dbf REMAP_SCHEMA=(scott:tom)
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 02:33:17
4、验证
sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 19 02:33:48 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
02:33:49 SQL> col file_name for a50
02:33:55 SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
1 /u01/app/oracle/oradata/test3/system01.dbf SYSTEM
4 /u01/app/oracle/oradata/test3/users01.dbf USERS
3 /u01/app/oracle/oradata/test3/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/test3/rtbs01.dbf RTBS
5 /u01/app/oracle/oradata/test3/users02.dbf USERS
6 /u01/app/oracle/oradata/test3/test1.dbf TEST1
7 /u01/app/oracle/oradata/test3/test2.dbf TEST2
8 /u01/app/oracle/oradata/test3/tbs1.dbf TBS1
9 /u01/app/oracle/oradata/test3/indx01.dbf INDX
9 rows selected.
02:34:15 SQL> select owner,table_name,tablespace_name from dba_tables
02:34:35 2 where tablespace_name='TBS1';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TOM DEPT2 TBS1
TOM TB1 TBS1
02:34:48 SQL> SELECT tablespace_name ,status,contents from dba_tablespaces
02:35:24 2 where tablespace_name='TBS1';
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
TBS1 READ ONLY PERMANENT
02:35:35 SQL> ALTER TABLESPACE TBS1 READ WRITE;
Tablespace altered.
02:35:50 SQL>
-------在源库上
02:36:03 SQL> ALTER TABLESPACE TBS1 READ WRITE;
Tablespace altered.
破坏数据块
dd if=/dev/zero of=/oradata/back_prod/users02.dbf bs=8192 skip=19 count=2
数据块损坏的检查与恢复
dbv file=/oradata/back_prod/users02.dbf blocksize=8192 feedback=100
创建检查表
1 begin
2 dbms_repair.admin_tables(
3 TABLE_NAME => 'REPAIR_LIST',
4 TABLE_TYPE => dbms_repair.repair_table,
5 ACTION => dbms_repair.create_action,
6 TABLESPACE => 'SYSAUX');
7* end;
SYS>/
执行检查
1 begin
2 dbms_repair.check_object(
3 SCHEMA_NAME => 'SCOTT',
4 OBJECT_NAME => 'EMP',
5 OBJECT_TYPE => dbms_repair.table_object,
6 REPAIR_TABLE_NAME => 'REPAIR_LIST',
7 CORRUPT_COUNT => :v_count);
8* end;
SYS>
恢复方式
RMAN> blockrecover datafile 8 block 20;