April 5, 2005
使用Oracle10g新特性,跨越Resetlogs时间点进行恢复
作者:eygle
出处:http://blog.eygle.com
在Oracle10g以前，在进行了不完全恢复使用resetlogs选项打开数据库以后，Oracle建议你要立即进行全备份。因为日志序号会被置位，以防止后续日志被应用。resetlogs之前的备份将不能用于进行跨域resetlogs时间点的恢复。
在Oracle10g中，Oracle允许跨越resetlogs时间点进行完全/不完全恢复。
看一下测试的例子:
1.全备份数据库
$ rman target / Recovery Manager: Release 10.1.0.2.0 - 64bit Production Copyright (c) 1995, 2004, Oracle. All rights reserved. connected to target database: EYGLE (DBID=1337390772) RMAN> backup database plus archivelog delete all input; Starting backup at 2005-04-05 16:29:08 current log archived using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=143 devtype=DISK channel ORA_DISK_1: starting archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=3 recid=294 stamp=554823466 input archive log thread=1 sequence=4 recid=295 stamp=554828647 input archive log thread=1 sequence=5 recid=297 stamp=554833749 channel ORA_DISK_1: starting piece 1 at 2005-04-05 16:29:16 channel ORA_DISK_1: finished piece 1 at 2005-04-05 16:29:19 piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_annnn_TAG20050405T162914_154m2woc_.bkp comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04 channel ORA_DISK_1: deleting archive log(s) archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_3_154919qm_.arc recid=294 stamp=554823466 archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_4_154g3728_.arc recid=295 stamp=554828647 archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_5_154m2og4_.arc recid=297 stamp=554833749 Finished backup at 2005-04-05 16:29:20 Starting backup at 2005-04-05 16:29:21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00009 name=/data1/oradata/systemfile/bigtbs.dbf input datafile fno=00001 name=/opt/oracle/oradata/eygle/system01.dbf input datafile fno=00006 name=/opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_test_03xv34ny_.dbf input datafile fno=00002 name=/opt/oracle/oradata/eygle/undotbs01.dbf input datafile fno=00003 name=/opt/oracle/oradata/eygle/sysaux01.dbf input datafile fno=00010 name=/opt/oracle/oradata/eygle/dfmbrc.dbf input datafile fno=00008 name=/opt/oracle/oradata/eygle/trans01.dbf channel ORA_DISK_1: starting piece 1 at 2005-04-05 16:29:22 channel ORA_DISK_1: finished piece 1 at 2005-04-05 16:31:07 piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m33kf_.bkp comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00005 name=/data1/oradata/systemfile/eygle01.dbf input datafile fno=00004 name=/opt/oracle/oradata/eygle/users01.dbf input datafile fno=00007 name=/opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_itpub_03xv5g66_.dbf channel ORA_DISK_1: starting piece 1 at 2005-04-05 16:31:08 channel ORA_DISK_1: finished piece 1 at 2005-04-05 16:31:33 piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m6dxm_.bkp comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00011 name=/opt/oracle/oradata/eygle/t2k01.dbf channel ORA_DISK_1: starting piece 1 at 2005-04-05 16:31:34 channel ORA_DISK_1: finished piece 1 at 2005-04-05 16:31:41 piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m76kz_.bkp comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 Finished backup at 2005-04-05 16:31:41 Starting backup at 2005-04-05 16:31:41 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=6 recid=298 stamp=554833902 channel ORA_DISK_1: starting piece 1 at 2005-04-05 16:31:44 channel ORA_DISK_1: finished piece 1 at 2005-04-05 16:31:47 piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_annnn_TAG20050405T163142_154m7jpo_.bkp comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04 channel ORA_DISK_1: deleting archive log(s) archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_6_154m7gb3_.arc recid=298 stamp=554833902 Finished backup at 2005-04-05 16:31:47 Starting Control File and SPFILE Autobackup at 2005-04-05 16:31:48 piece handle=/data5/flash_recovery_area/EYGLE/autobackup/2005_04_05/o1_mf_s_554833908_154m7qps_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 2005-04-05 16:31:56 RMAN> exit Recovery Manager complete.
2.进行数据更改
$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 5 16:32:23 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SYS AS SYSDBA on 2005-04-05 16:32:23 >alter system switch logfile;
System altered.
SYS AS SYSDBA on 2005-04-05 16:32:30 >select count(*) from t;
COUNT(*)
----------
18956
SYS AS SYSDBA on 2005-04-05 16:33:03 >insert into t select * from t;
18956 rows created.
SYS AS SYSDBA on 2005-04-05 16:33:17 >commit;
Commit complete.
SYS AS SYSDBA on 2005-04-05 16:33:19 >alter system switch logfile;
System altered.
SYS AS SYSDBA on 2005-04-05 16:33:45 >truncate table t;
Table truncated.
SYS AS SYSDBA on 2005-04-05 16:34:58 >alter system switch logfile;
System altered.
此时的日志序列情况如下:
SYS AS SYSDBA on 2005-04-05 16:35:00 >select * from v$log_history where recid >1811;
RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME
---------- ---------- ---------- ---------- ------------- ------------------- ------------ ----------------- -------------------
1812 554813757 1 1 10923677 2005-04-05 09:54:53 10925720 10923677 2005-04-05 09:54:53
1813 554813974 1 2 10925720 2005-04-05 10:55:57 10925827 10923677 2005-04-05 09:54:53
1814 554823464 1 3 10925827 2005-04-05 10:59:34 10947409 10923677 2005-04-05 09:54:53
1815 554828646 1 4 10947409 2005-04-05 13:37:44 10950318 10923677 2005-04-05 09:54:53
1816 554833748 1 5 10950318 2005-04-05 15:04:06 10953123 10923677 2005-04-05 09:54:53
1817 554833901 1 6 10953123 2005-04-05 16:29:08 10953187 10923677 2005-04-05 09:54:53
1818 554833950 1 7 10953187 2005-04-05 16:31:41 10953213 10923677 2005-04-05 09:54:53
1819 554834005 1 8 10953213 2005-04-05 16:32:30 10953633 10923677 2005-04-05 09:54:53
1820 554834118 1 9 10953633 2005-04-05 16:33:25 10953726 10923677 2005-04-05 09:54:53
9 rows selected.
关闭数据库:
SYS AS SYSDBA on 2005-04-05 16:35:18 >shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS AS SYSDBA on 2005-04-05 16:37:30 >exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
3.执行不完全恢复
$ rman target /
Recovery Manager: Release 10.1.0.2.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 314572800 bytes
Fixed Size 1301704 bytes
Variable Size 261890872 bytes
Database Buffers 50331648 bytes
Redo Buffers 1048576 bytes
RMAN> run {
2> set until sequence 8 thread 1;
3> restore database;
4> recover database;
5> }
executing command: SET until clause
using target database controlfile instead of recovery catalog
Starting restore at 2005-04-05 16:39:17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
datafile 8 not processed because file is read-only
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/eygle/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/eygle/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/eygle/sysaux01.dbf
restoring datafile 00006 to /opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_test_03xv34ny_.dbf
restoring datafile 00009 to /data1/oradata/systemfile/bigtbs.dbf
restoring datafile 00010 to /opt/oracle/oradata/eygle/dfmbrc.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m33kf_.bkp tag=TAG20050405T162921
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /opt/oracle/oradata/eygle/users01.dbf
restoring datafile 00005 to /data1/oradata/systemfile/eygle01.dbf
restoring datafile 00007 to /opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_itpub_03xv5g66_.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m6dxm_.bkp tag=TAG20050405T162921
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /opt/oracle/oradata/eygle/t2k01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m76kz_.bkp tag=TAG20050405T162921
channel ORA_DISK_1: restore complete
Finished restore at 2005-04-05 16:41:45
Starting recover at 2005-04-05 16:41:46
using channel ORA_DISK_1
datafile 8 not processed because file is offline
starting media recovery
archive log thread 1 sequence 7 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_7_154m8z7k_.arc
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=6
channel ORA_DISK_1: restored backup piece 1
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_annnn_TAG20050405T163142_154m7jpo_.bkp tag=TAG20050405T163142
channel ORA_DISK_1: restore complete
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_6_154mtkfk_.arc thread=1 sequence=6
channel default: deleting archive log(s)
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_6_154mtkfk_.arc recid=302 stamp=554834513
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_7_154m8z7k_.arc thread=1 sequence=7
media recovery complete
Finished recover at 2005-04-05 16:41:57
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.
4.此时的数据库状态
$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 5 16:43:58 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SYS AS SYSDBA on 2005-04-05 16:43:58 >archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SYS AS SYSDBA on 2005-04-05 16:44:01 >select count(*) from t;
COUNT(*)
----------
18956
5.继续进行数据更改
SYS AS SYSDBA on 2005-04-05 16:44:08 >create table tt as select * from dba_users;
Table created.
SYS AS SYSDBA on 2005-04-05 16:45:51 >alter system switch logfile;
System altered.
SYS AS SYSDBA on 2005-04-05 16:45:56 >select count(*) from tt;
COUNT(*)
----------
12
SYS AS SYSDBA on 2005-04-05 16:46:01 >insert into tt select * from tt;
12 rows created.
SYS AS SYSDBA on 2005-04-05 16:46:13 >commit;
Commit complete.
SYS AS SYSDBA on 2005-04-05 16:46:16 >alter system switch logfile;
System altered.
此时的日志序列:
SYS AS SYSDBA on 2005-04-05 17:46:19 >select * from v$log_history where recid >1811;
RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME
---------- ---------- ---------- ---------- ------------- ------------------- ------------ ----------------- -------------------
1812 554813757 1 1 10923677 2005-04-05 09:54:53 10925720 10923677 2005-04-05 09:54:53
1813 554813974 1 2 10925720 2005-04-05 10:55:57 10925827 10923677 2005-04-05 09:54:53
1814 554823464 1 3 10925827 2005-04-05 10:59:34 10947409 10923677 2005-04-05 09:54:53
1815 554828646 1 4 10947409 2005-04-05 13:37:44 10950318 10923677 2005-04-05 09:54:53
1816 554833748 1 5 10950318 2005-04-05 15:04:06 10953123 10923677 2005-04-05 09:54:53
1817 554833901 1 6 10953123 2005-04-05 16:29:08 10953187 10923677 2005-04-05 09:54:53
1818 554833950 1 7 10953187 2005-04-05 16:31:41 10953213 10923677 2005-04-05 09:54:53
1819 554834005 1 8 10953213 2005-04-05 16:32:30 10953633 10923677 2005-04-05 09:54:53
1820 554834118 1 9 10953633 2005-04-05 16:33:25 10953726 10923677 2005-04-05 09:54:53
1821 554834756 1 1 10953214 2005-04-05 16:42:34 10953433 10953214 2005-04-05 16:42:34
1822 554834781 1 2 10953433 2005-04-05 16:45:56 10953445 10953214 2005-04-05 16:42:34
11 rows selected.
SYS AS SYSDBA on 2005-04-05 16:46:21 >shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS AS SYSDBA on 2005-04-05 16:47:38 >exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
6.再次执行恢复
$ rman target /
Recovery Manager: Release 10.1.0.2.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 314572800 bytes
Fixed Size 1301704 bytes
Variable Size 261890872 bytes
Database Buffers 50331648 bytes
Redo Buffers 1048576 bytes
RMAN> run {
2> restore database;
3> recover database;
4> }
Starting restore at 2005-04-05 16:49:54
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
datafile 8 not processed because file is read-only
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/eygle/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/eygle/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/eygle/sysaux01.dbf
restoring datafile 00006 to /opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_test_03xv34ny_.dbf
restoring datafile 00009 to /data1/oradata/systemfile/bigtbs.dbf
restoring datafile 00010 to /opt/oracle/oradata/eygle/dfmbrc.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m33kf_.bkp tag=TAG20050405T162921
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /opt/oracle/oradata/eygle/users01.dbf
restoring datafile 00005 to /data1/oradata/systemfile/eygle01.dbf
restoring datafile 00007 to /opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_itpub_03xv5g66_.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m6dxm_.bkp tag=TAG20050405T162921
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /opt/oracle/oradata/eygle/t2k01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m76kz_.bkp tag=TAG20050405T162921
channel ORA_DISK_1: restore complete
Finished restore at 2005-04-05 16:52:31
Starting recover at 2005-04-05 16:52:32
using channel ORA_DISK_1
datafile 8 not processed because file is offline
starting media recovery
archive log thread 1 sequence 7 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_7_154m8z7k_.arc
archive log thread 1 sequence 1 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_1_154n24v5_.arc
archive log thread 1 sequence 2 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_2_154n2y40_.arc
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=6
channel ORA_DISK_1: restored backup piece 1
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_annnn_TAG20050405T163142_154m7jpo_.bkp tag=TAG20050405T163142
channel ORA_DISK_1: restore complete
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_6_154ngror_.arc thread=1 sequence=6
channel default: deleting archive log(s)
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_6_154ngror_.arc recid=306 stamp=554835160
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_7_154m8z7k_.arc thread=1 sequence=7
media recovery complete
Finished recover at 2005-04-05 16:53:04
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
7.检查数据恢复情况
注意此次恢复跨越了resetlogs时间点
$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 5 16:53:56 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SYS AS SYSDBA on 2005-04-05 16:53:56 >select count(*) from t;
COUNT(*)
----------
18956
SYS AS SYSDBA on 2005-04-05 16:54:01 >select count(*) from tt;
COUNT(*)
----------
24
SYS AS SYSDBA on 2005-04-05 16:54:07 >
SYS AS SYSDBA on 2005-04-05 16:54:47 >
数据恢复完成.
Posted by eygle at 5:20 PM
| Comments (3)
如何把数据导入不同的表空间?
作者:eygle
出处:http://blog.eygle.com
很多人在进行数据迁移时，希望把数据导入不同于原系统的表空间，在导入之后却往往发现，数据被导入了原表空间。
本例举例说明解决这个问题:
1.如果缺省的用户具有DBA权限
那么导入时会按照原来的位置导入数据，即导入到原表空间
$ imp bjbbs/passwd file=bj_bbs.dmp fromuser=jive touser=bjbbs grants=n
Import: Release 8.1.7.4.0 - Production on Mon Sep 22 11:49:41 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
Export file created by EXPORT:V08.01.07 via conventional path
Warning: the objects were exported by JIVE, not by you
import done in ZHS16GBK character set and ZHS16GBK NCHAR character set
. . importing table "HS_ALBUMINBOX" 12 rows imported
. . importing table "HS_ALBUM_INFO" 47 rows imported
. . importing table "HS_CATALOG" 13 rows imported
. . importing table "HS_CATALOGAUTHORITY" 5 rows imported
. . importing table "HS_CATEGORYAUTHORITY" 0 rows imported
....
. . importing table "JIVEUSERPROP" 4 rows imported
. . importing table "JIVEWATCH" 0 rows imported
. . importing table "PLAN_TABLE" 0 rows imported
. . importing table "TMZOLDUSER" 3 rows imported
. . importing table "TMZOLDUSER2" 3 rows imported
About to enable constraints...
Import terminated successfully without warnings.
查询发现仍然导入了USER表空间
$ sqlplus bjbbs/passwd
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Sep 22 11:50:03 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
HS_ALBUMINBOX USERS
HS_ALBUM_INFO USERS
HS_CATALOG USERS
HS_CATALOGAUTHORITY USERS
HS_CATEGORYAUTHORITY USERS
HS_CATEGORYINFO USERS
HS_DLF_DOWNLOG USERS
...
JIVEWATCH USERS
PLAN_TABLE USERS
TMZOLDUSER USERS
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMZOLDUSER2 USERS
45 rows selected.
2.回收用户unlimited tablespace权限
这样就可以导入到用户缺省表空间
SQL> create user bjbbs identified by passwd
2 default tablespace bjbbs
3 temporary tablespace temp
4 /
User created.
SQL> grant connect,resource to bjbbs;
Grant succeeded.
SQL> grant dba to bjbbs;
Grant succeeded.
SQL> revoke unlimited tablespace from bjbbs;
Revoke succeeded.
SQL> alter user bjbbs quota 0 on users;
User altered.
SQL> alter user bjbbs quota unlimited on bjbbs;
User altered.
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
重新导入数据
$ imp bjbbs/passwd file=bj_bbs.dmp fromuser=jive touser=bjbbs grants=n
Import: Release 8.1.7.4.0 - Production on Mon Sep 22 12:00:51 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
Export file created by EXPORT:V08.01.07 via conventional path
Warning: the objects were exported by JIVE, not by you
import done in ZHS16GBK character set and ZHS16GBK NCHAR character set
. . importing table "HS_ALBUMINBOX" 12 rows imported
. . importing table "HS_ALBUM_INFO" 47 rows imported
. . importing table "HS_CATALOG" 13 rows imported
. . importing table "HS_CATALOGAUTHORITY" 5 rows imported
. . importing table "HS_CATEGORYAUTHORITY" 0 rows imported
. . importing table "HS_CATEGORYINFO" 9 rows imported
. . importing table "HS_DLF_DOWNLOG" 0 rows imported
....
. . importing table "JIVEUSER" 102 rows imported
. . importing table "JIVEUSERPERM" 81 rows imported
. . importing table "JIVEUSERPROP" 4 rows imported
. . importing table "JIVEWATCH" 0 rows imported
. . importing table "PLAN_TABLE" 0 rows imported
. . importing table "TMZOLDUSER" 3 rows imported
. . importing table "TMZOLDUSER2" 3 rows imported
About to enable constraints...
Import terminated successfully without warnings.
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
HS_ALBUMINBOX BJBBS
HS_ALBUM_INFO BJBBS
HS_CATALOG BJBBS
HS_CATALOGAUTHORITY BJBBS
....
JIVETHREAD BJBBS
JIVETHREADPROP BJBBS
JIVEUSER BJBBS
JIVEUSERPERM BJBBS
JIVEUSERPROP BJBBS
JIVEWATCH BJBBS
PLAN_TABLE BJBBS
TMZOLDUSER BJBBS
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMZOLDUSER2 BJBBS
45 rows selected.
现在数据被导入到正确的用户表空间中.
Posted by eygle at 2:52 PM
| Comments (18)
使用Rman进行不完全恢复
作者:eygle
出处:http://blog.eygle.com
1.模拟数据损失
$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 5 09:26:04 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SYS AS SYSDBA on 05-APR-05 >create table t as select * from dba_objects;
Table created.
SYS AS SYSDBA on 05-APR-05 >alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
记录一下时间(具体的生产环境，可能需要你估计一个恢复时间或者通过Logminer来分析日志得到故障时间)
SYS AS SYSDBA on 2005-04-05 09:26:48 >select sysdate from dual;
SYSDATE
-------------------
2005-04-05 09:26:55
SYS AS SYSDBA on 2005-04-05 09:26:55 >select count(*) from t;
COUNT(*)
----------
9478
drop table模拟一次事故(当然也可以选择闪回这张表，本例为了说明不完全恢复)
SYS AS SYSDBA on 2005-04-05 09:27:05 >drop table t;
Table dropped.
SYS AS SYSDBA on 2005-04-05 09:27:19 >exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
2.执行恢复
设置环境变量时间，执行until time的不完全恢复。
$ export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
$ rman target /
Recovery Manager: Release 10.1.0.2.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: EYGLE (DBID=1337390772)
RMAN> run {
2> set until time '2005-04-05 09:26:55';
3> restore database;
4> recover database;
5> }
executing command: SET until clause
using target database controlfile instead of recovery catalog
Starting restore at 2005-04-05 09:36:51
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
datafile 8 not processed because file is read-only
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/eygle/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/eygle/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/eygle/sysaux01.dbf
restoring datafile 00006 to /opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_test_03xv34ny_.dbf
restoring datafile 00009 to /data1/oradata/systemfile/bigtbs.dbf
restoring datafile 00010 to /opt/oracle/oradata/eygle/dfmbrc.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_03_31/o1_mf_nnndf_TAG20050331T104240_14prx29n_.bkp tag=TAG20050331T104240
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /opt/oracle/oradata/eygle/users01.dbf
restoring datafile 00005 to /data1/oradata/systemfile/eygle01.dbf
restoring datafile 00007 to /opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_itpub_03xv5g66_.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_03_31/o1_mf_nnndf_TAG20050331T104240_14ps0d47_.bkp tag=TAG20050331T104240
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /opt/oracle/oradata/eygle/t2k01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_03_31/o1_mf_nnndf_TAG20050331T104240_14ps15qb_.bkp tag=TAG20050331T104240
channel ORA_DISK_1: restore complete
Finished restore at 2005-04-05 09:39:17
Starting recover at 2005-04-05 09:39:18
using channel ORA_DISK_1
datafile 8 not processed because file is offline
starting media recovery
archive log thread 1 sequence 13 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_31/o1_mf_1_13_14psyb93_.arc
archive log thread 1 sequence 14 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_31/o1_mf_1_14_14q810df_.arc
archive log thread 1 sequence 15 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_31/o1_mf_1_15_14qp1w1q_.arc
archive log thread 1 sequence 16 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_31/o1_mf_1_16_14r5606b_.arc
archive log thread 1 sequence 17 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_17_14r7nf55_.arc
archive log thread 1 sequence 18 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_18_14rprqyz_.arc
archive log thread 1 sequence 19 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_19_14s79l9f_.arc
archive log thread 1 sequence 20 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_20_14sodfhx_.arc
archive log thread 1 sequence 21 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_21_14t6ysjt_.arc
archive log thread 1 sequence 22 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_22_14tnzplf_.arc
archive log thread 1 sequence 23 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_23_14tsobsv_.arc
archive log thread 1 sequence 24 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_24_14v6l6om_.arc
archive log thread 1 sequence 25 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_25_14vno1gg_.arc
archive log thread 1 sequence 26 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_26_14w67fn9_.arc
archive log thread 1 sequence 27 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_27_14wn8bj3_.arc
archive log thread 1 sequence 28 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_28_14x2cd0g_.arc
archive log thread 1 sequence 29 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_29_14xg2xts_.arc
archive log thread 1 sequence 30 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_30_14xmxog2_.arc
archive log thread 1 sequence 31 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_31_14y5j2n8_.arc
archive log thread 1 sequence 32 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_32_14yq2mps_.arc
archive log thread 1 sequence 33 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_33_14z8o5h9_.arc
archive log thread 1 sequence 34 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_34_14zxqc55_.arc
archive log thread 1 sequence 35 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_35_1502kl8x_.arc
archive log thread 1 sequence 36 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_36_150lvjz1_.arc
archive log thread 1 sequence 37 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_37_1514g562_.arc
archive log thread 1 sequence 38 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_38_151sjdq0_.arc
archive log thread 1 sequence 39 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_39_152gnlv0_.arc
archive log thread 1 sequence 40 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_40_152pytqn_.arc
archive log thread 1 sequence 41 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_41_1533pxgd_.arc
archive log thread 1 sequence 42 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_42_153rs55f_.arc
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_03_31/o1_mf_1_13_14psyb93_.arc thread=1 sequence=13
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_03_31/o1_mf_1_14_14q810df_.arc thread=1 sequence=14
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_03_31/o1_mf_1_15_14qp1w1q_.arc thread=1 sequence=15
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_03_31/o1_mf_1_16_14r5606b_.arc thread=1 sequence=16
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_17_14r7nf55_.arc thread=1 sequence=17
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_18_14rprqyz_.arc thread=1 sequence=18
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_19_14s79l9f_.arc thread=1 sequence=19
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_20_14sodfhx_.arc thread=1 sequence=20
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_21_14t6ysjt_.arc thread=1 sequence=21
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_22_14tnzplf_.arc thread=1 sequence=22
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_23_14tsobsv_.arc thread=1 sequence=23
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_24_14v6l6om_.arc thread=1 sequence=24
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_25_14vno1gg_.arc thread=1 sequence=25
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_26_14w67fn9_.arc thread=1 sequence=26
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_27_14wn8bj3_.arc thread=1 sequence=27
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_28_14x2cd0g_.arc thread=1 sequence=28
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_29_14xg2xts_.arc thread=1 sequence=29
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_30_14xmxog2_.arc thread=1 sequence=30
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_31_14y5j2n8_.arc thread=1 sequence=31
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_32_14yq2mps_.arc thread=1 sequence=32
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_33_14z8o5h9_.arc thread=1 sequence=33
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_34_14zxqc55_.arc thread=1 sequence=34
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_35_1502kl8x_.arc thread=1 sequence=35
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_36_150lvjz1_.arc thread=1 sequence=36
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_37_1514g562_.arc thread=1 sequence=37
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_38_151sjdq0_.arc thread=1 sequence=38
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_39_152gnlv0_.arc thread=1 sequence=39
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_40_152pytqn_.arc thread=1 sequence=40
media recovery complete
Finished recover at 2005-04-05 09:50:42
恢复完成，使用RESETLOGS选项打开数据库。
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.
3.检查数据恢复情况
$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 5 09:58:00 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SYS AS SYSDBA on 2005-04-05 09:58:00 >archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SYS AS SYSDBA on 2005-04-05 09:58:04 >select count(*) from t;
COUNT(*)
----------
9478
SYS AS SYSDBA on 2005-04-05 09:58:09 >
Posted by eygle at 10:21 AM
| Comments (3)
进京两周年记-Eygle在北京的生活之五
作者:eygle
出处:http://blog.eygle.com
2004年6月16日，开始制作自己的网站。
从此在网上安家，有了自己的站点(www.eygle.com)，开始关心网站技术，慢慢发现，想要做好一个网站实在不是一件容易的事。
自己的网站，目的很单纯，就是随意记下自己的一些文章以及心情点滴，所以主页上写下的是:Friends Life & Oracle.
有朋友，要认真生活，热爱Oracle，多么简单的生活.
当我跟朋友说这一年一无所为的时候，Seay总是提醒我:你有了自己的网站。
是的，通过这个简单的站点，结识了很多的朋友。
想起N年以前一个朋友反复在信中和我提到的一句歌词:
你们的关怀让我温暖...
有朋友的生活就不孤单。
对了，昨天还收到了Orion发来的周年礼物
http://www.eygle.com/music/wma/2.wmv
to be continued...
Posted by eygle at 9:47 AM
| Comments (1)