1、测试环境
03:57:50 SQL> conn scott/tiger
Connected.
03:57:57 SQL>
03:59:43 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EXCEPTIONS TABLE
LXTB1 TABLE
LXTB2 TABLE
SALGRADE TABLE
SYS_TEMP_FBT TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
8 rows selected.
03:59:45 SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EXCEPTIONS USERS
LXTB1 USERS
LXTB2 LXTBS2
SALGRADE USERS
BONUS USERS
DEPT USERS
EMP USERS
SYS_TEMP_FBT
8 rows selected.
04:00:08 SQL> select * from lxtb2;
ID
----------
1
2
3
4
5
6
7
8
10
11
10 rows selected.
2、对主库做热备份
04:00:09 SQL> conn /as sysdba
Connected.
04:00:14 SQL>
04:00:14 SQL> @/home/oracle/test_hot_bak
04:00:22 SQL> set feedback off pagesize 0 heading off verify off linesize 100 trimspool on echo off time off
***spooling to /disk1/backup/test/hot_cmd.sql
spool /disk1/backup/test/hot_bak/hot_bak.lst
alter system switch logfile;
alter tablespace SYSTEM begin backup;
host cp /u01/app/oracle/oradata/test/system01.dbf /disk1/backup/test/hot_bak
alter tablespace SYSTEM end backup;
alter tablespace RTBS begin backup;
host cp /u01/app/oracle/oradata/test/rtbs01.dbf /disk1/backup/test/hot_bak
alter tablespace RTBS end backup;
alter tablespace SYSAUX begin backup;
host cp /u01/app/oracle/oradata/test/sysaux01.dbf /disk1/backup/test/hot_bak
alter tablespace SYSAUX end backup;
alter tablespace USERS begin backup;
host cp /u01/app/oracle/oradata/test/users01.dbf /disk1/backup/test/hot_bak
alter tablespace USERS end backup;
alter tablespace LOB_16K begin backup;
host cp /u01/app/oracle/oradata/test/lob_16k01.dbf /disk1/backup/test/hot_bak
alter tablespace LOB_16K end backup;
alter tablespace UNDOTBS1 begin backup;
host cp /u01/app/oracle/oradata/test/undotbs1.dbf /disk1/backup/test/hot_bak
alter tablespace UNDOTBS1 end backup;
alter tablespace INDX begin backup;
host cp /u01/app/oracle/oradata/test/indx01.dbf /disk1/backup/test/hot_bak
alter tablespace INDX end backup;
alter tablespace LXTBS1 begin backup;
host cp /u01/app/oracle/oradata/test/lxtbs01.dbf /disk1/backup/test/hot_bak
alter tablespace LXTBS1 end backup;
alter tablespace LXTBS2 begin backup;
host cp /u01/app/oracle/oradata/test/lxtbs2.dbf /disk1/backup/test/hot_bak
alter tablespace LXTBS2 end backup;
archive log list;
spool off;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /disk4/arch/test
Oldest online log sequence 6
Next log sequence to archive 9
Current log sequence 9
3、备份控制文件，作为备库的controlfile
SQL> alter database backup controlfile to '/u01/app/oracle/oradata/test2/control01.ctl';
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
4、生成pfile 文件
SQL> create pfile from spfile；
5、表被误操作（truncate）
[oracle@work test]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 27 04:01:49 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
04:01:50 SQL>
04:01:50 SQL> insert into scott.lxtb2 values （9）；
1 row created.
04:02:15 SQL> insert into scott.lxtb2 values （12）；
1 row created.
04:02:17 SQL> insert into scott.lxtb2 values （13）；
1 row created.
04:02:19 SQL> commit;
Commit complete.
04:02:21 SQL> alter system switch logfile;
System altered.
04:02:39 SQL> /
System altered.
04:02:40 SQL> /
System altered.
04:02:41 SQL> truncate table scott.lxtb2;
Table truncated.
04:02:50 SQL> select * from scott.lxtb2;
no rows selected
04:02:56 SQL> !
6、生成备库的pfile 文件（inittest2.ora）和备库口令文件
[oracle@work dbs]$ orapwd file=orapwtest2 password=oracle entries=3 force=y
[oracle@work hot_bak]$ cp $ORACLE_HOME/dbs/inittest.ora $ORACLE_HOME/dbs/inittest2.ora
--------备库pfile 文件
[oracle@work dbs]$ cat inittest2.ora
test.__db_cache_size=251658240
test.__java_pool_size=4194304
test.__large_pool_size=4194304
test.__shared_pool_size=150994944
test.__streams_pool_size=0
*.audit_trail='NONE'
*.background_dump_dest='$ORACLE_BASE/admin/test/bdump'
*.core_dump_dest='$ORACLE_BASE/admin/test/cdump'
*.db_16k_cache_size=12582912
*.db_4k_cache_size=12582912
*.db_block_size=8192
*.db_cache_size=30M#DEMO
*.db_file_multiblock_read_count=16
*.db_name='test'
*.db_recovery_file_dest='/disk1/flash/test'
*.db_recovery_file_dest_size=2147483648
*.fast_start_mttr_target=900
*.fast_start_parallel_rollback='HIGH'
*.log_archive_dest_1='location=/disk4/arch/test'
*.log_archive_dest_2=''
*.log_archive_format='arch_%t_%s_%r.log'
*.log_checkpoints_to_alert=TRUE
*.nls_date_format='yyyy-mm-dd hh24:mi:ss'
*.O7_DICTIONARY_ACCESSIBILITY=FALSE
*.optimizer_mode='choose'
*.parallel_threads_per_cpu=4#SMALL
*.pga_aggregate_target=10485760
*.query_rewrite_enabled='true'
*.query_rewrite_integrity='trusted'
*.recovery_parallelism=4
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.sga_max_size=440401920
*.sga_target=418m
*.shared_pool_size=100M#DEMO
*.star_transformation_enabled='true'
*.undo_management='auto'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='$ORACLE_BASE/admin/test/udump'
*.utl_file_dir='/home/oracle/logmnr'
---------添加以下内容
*.control_files='/u01/app/oracle/oradata/test2/control01.ctl'
db_unique_name = test2
db_file_name_convert=（'/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/test2'）
log_file_name_convert=（'/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/test2','/disk1/oradata/test','/disk1/oradata/test2'）
7、拷贝主库备份数据文件到备库目录下
[oracle@work hot_bak]$ mkdir /u01/app/oracle/oradata/test2
[oracle@work hot_bak]$ mkdir /disk1/oradata/test2
[oracle@work test]$ cd /disk1/backup/test/hot_bak/
[oracle@work hot_bak]$ ls
hot_bak.lst indx01.dbf lob_16k01.dbf lxtbs01.dbf lxtbs2.dbf rtbs01.dbf sysaux01.dbf system01.dbf undotbs1.dbf users01.dbf
[oracle@work hot_bak]$ cp *.dbf /u01/app/oracle/oradata/test2/
8、启动备库到nomount
export ORACLE_SID=test2
[oracle@work hot_bak]$ export ORACLE_SID=test2
[oracle@work hot_bak]$ !sql
sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 27 04:05:28 2011
Copyright （c） 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
04:05:29 SQL>
04:05:29 SQL> startup nomount pfile=$ORACLE_HOME/dbs/inittest2.ora
ORACLE instance started.
Total System Global Area 440401920 bytes
Fixed Size 1219904 bytes
Variable Size 113246912 bytes
Database Buffers 322961408 bytes
Redo Buffers 2973696 bytes
9、启动备库到mount状态
04:05:43 SQL> alter database mount clone database;
Database altered.
04:06:25 SQL> col name for a50
04:06:32 SQL> select name ,file# ,status from v$datafile;
NAME FILE# STATUS
-------------------------------------------------- ---------- -------
/u01/app/oracle/oradata/test2/system01.dbf 1 SYSOFF
/u01/app/oracle/oradata/test2/rtbs01.dbf 2 OFFLINE
/u01/app/oracle/oradata/test2/sysaux01.dbf 3 OFFLINE
/u01/app/oracle/oradata/test2/users01.dbf 4 OFFLINE
/u01/app/oracle/oradata/test2/lob_16k01.dbf 5 OFFLINE
/u01/app/oracle/oradata/test2/lxtbs01.dbf 6 OFFLINE
/u01/app/oracle/oradata/test2/lxtbs2.dbf 7 OFFLINE
/u01/app/oracle/oradata/test2/undotbs1.dbf 9 OFFLINE
/u01/app/oracle/oradata/test2/indx01.dbf 14 OFFLINE
9 rows selected.
10、将数据文件联机
04:09:17 SQL> alter database datafile 1 online;
Database altered.
04:09:24 SQL> alter database datafile 2 online;
Database altered.
04:09:26 SQL> alter database datafile 3 online;
Database altered.
04:09:28 SQL> alter database datafile 4 online;
Database altered.
04:09:30 SQL> alter database datafile 5 online;
Database altered.
04:09:32 SQL> alter database datafile 6 online;
Database altered.
04:09:34 SQL> alter database datafile 7 online;
Database altered.
04:09:36 SQL> alter database datafile 9 online;
Database altered.
04:09:40 SQL> alter database datafile 14 online;
Database altered.
04:09:42 SQL> select name ,file# ,status from v$datafile
04:09:47 2 ;
NAME FILE# STATUS
-------------------------------------------------- ---------- -------
/u01/app/oracle/oradata/test2/system01.dbf 1 SYSTEM
/u01/app/oracle/oradata/test2/rtbs01.dbf 2 ONLINE
/u01/app/oracle/oradata/test2/sysaux01.dbf 3 ONLINE
/u01/app/oracle/oradata/test2/users01.dbf 4 ONLINE
/u01/app/oracle/oradata/test2/lob_16k01.dbf 5 ONLINE
/u01/app/oracle/oradata/test2/lxtbs01.dbf 6 ONLINE
/u01/app/oracle/oradata/test2/lxtbs2.dbf 7 ONLINE
/u01/app/oracle/oradata/test2/undotbs1.dbf 9 ONLINE
/u01/app/oracle/oradata/test2/indx01.dbf 14 ONLINE
9 rows selected.
11、在备库上做基于时间点的database recover（时间点就是truncate时的时间点，可以用logmnr找出）
04:09:48 SQL> recover database until time '2011-10-27 04:01:50' using backup controlfile;
ORA-00279: change 1354493 generated at 10/27/2011 04:00:23 needed for thread 1
ORA-00289: suggestion : /disk4/arch/test/arch_1_9_765501215.log
ORA-00280: change 1354493 for thread 1 is in sequence #9
04:10:30 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
12、open database 查看恢复
04:10:37 SQL> alter database open resetlogs;
Database altered.
04:10:58 SQL> select * from scott.lxtb2;
ID
----------
1
2
3
4
5
6
7
8
10
11
10 rows selected.
04:11:30 SQL>
---------------------恢复成功
13、将恢复后的表空间导出（lxtbs2 tablespace）
[oracle@work data]$ exp userid=\'sys/oracle as sydba\' point_in_time_recover=y tablespaces=lxtbs2 file=lxtbs2.dmp
Export: Release 10.2.0.1.0 - Production on Thu Oct 27 04:15:34 2011
Copyright （c） 1982, 2005, Oracle. All rights reserved.
EXP-00004: invalid username or password
Username: sys as sysdba
Password:
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 ZHS16GBK character set （possible charset conversion）
Note: table data （rows） will not be exported
About to export Tablespace Point-in-time Recovery objects…
For tablespace LXTBS2 …
. exporting cluster definitions
. exporting table definitions
. . exporting table LXTB2
EXP-00091: Exporting questionable statistics.
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully with warnings.
[oracle@work data]$
14、将表空间导入到主库
----------将主库表空间脱机 test （主库）
04:13:45 SQL> alter tablespace lxtbs2 offline;
Tablespace altered.
--------拷贝备库表空间datafile 到主库目录下
[oracle@work data]$ cp /u01/app/oracle/oradata/test2/lxtbs2.dbf /u01/app/oracle/oradata/test
[oracle@work data]$ export ORACLE_SID=test
-------------导入到主库
[oracle@work data]$ imp userid=\'sys/oracle as sysdba\' point_in_time_recover=y file=lxtbs2.dmp ignore=y
Import: Release 10.2.0.1.0 - Production on Thu Oct 27 04:38:29 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 Tablespace Point-in-time Recovery objects…
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set （possible charset conversion）
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table "LXTB2"
. importing SYS's objects into SYS
Import terminated successfully without warnings
15、验证
04:30:39 SQL> alter tablespace lxtbs2 online;
Tablespace altered.
04:38:51 SQL> select * from scott.lxtb2;
ID
----------
1
2
3
4
5
6
7
8
10
11
10 rows selected.
-----------数据文件恢复到truncate 之前
oracle视频教程请关注: