按如下步骤做TSPITR测试
1、建立clone(auxiliary) database
2、recover database using backup control until time '2004-05-05 18:01:00'
3、alter database open resetlogs;
4、 exp sys/pwd transport_tablespace=y tablespaces=users tts_full_check=y file='o:\user.dmp';到了第4步就是通不过,出现如下错误Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
EXP-00008: ORACLE error 1001 encountered
ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_SYS_SQL", line 819
ORA-06512: at "SYS.DBMS_SQL", line 19
ORA-06512: at "SYS.DBMS_TTS", line 774
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_PLUGTS", line 1093
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
怀疑self-contained 用问题,做以下检查
execute sys.dbms_tts.transport_set_check('users',true);
出现错误SQL> execute sys.dbms_tts.transport_set_check('users',true);
BEGIN sys.dbms_tts.transport_set_check('users',true); END;*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_SYS_SQL", line 819
ORA-06512: at "SYS.DBMS_SQL", line 19
ORA-06512: at "SYS.DBMS_TTS", line 774
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_TTS", line 803
ORA-06512: at line 1但primary database 的
execute sys.dbms_tts.transport_set_check('users',true);
是通过的。不知问题何在?郁闷了一晚上,请指点。
另:9i的exp是不是没有 point_in_time_recover=y 和 recovery_tablespaces=users
1、建立clone(auxiliary) database
2、recover database using backup control until time '2004-05-05 18:01:00'
3、alter database open resetlogs;
4、 exp sys/pwd transport_tablespace=y tablespaces=users tts_full_check=y file='o:\user.dmp';到了第4步就是通不过,出现如下错误Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
EXP-00008: ORACLE error 1001 encountered
ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_SYS_SQL", line 819
ORA-06512: at "SYS.DBMS_SQL", line 19
ORA-06512: at "SYS.DBMS_TTS", line 774
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_PLUGTS", line 1093
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
怀疑self-contained 用问题,做以下检查
execute sys.dbms_tts.transport_set_check('users',true);
出现错误SQL> execute sys.dbms_tts.transport_set_check('users',true);
BEGIN sys.dbms_tts.transport_set_check('users',true); END;*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_SYS_SQL", line 819
ORA-06512: at "SYS.DBMS_SQL", line 19
ORA-06512: at "SYS.DBMS_TTS", line 774
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_TTS", line 803
ORA-06512: at line 1但primary database 的
execute sys.dbms_tts.transport_set_check('users',true);
是通过的。不知问题何在?郁闷了一晚上,请指点。
另:9i的exp是不是没有 point_in_time_recover=y 和 recovery_tablespaces=users
解决方案 »
- 通过COM+,DEXPRESS连接oracle,连接之后oracle,session里的username为空
- oracle10g 表闪回,提示名称为ORA-01555:快照过旧:回退段号3("SYSSMU3$")过小
- 数百张大数据量表创建分区、导数据效率问题
- 请大家帮忙解决一个统计上的问题!
- 重新求助一个oracle存储过程题目,谢谢!!!
- ORACLE如何实现ID自动增长?
- 怎么提高数据库更新的效率?
- ORA-02042: too many distributed transactions
- 求助ORACLE的数据库不会用
- 我的机器新安装win2k server+ oracle816,managerserver启不了,help?
- 不同的数据库之间如何实现两个表之间的数据交换?
- Oracle 中如何建立任务 定时启动服务器
就知道所有的参数
dependencies on objects outside the recovery set. For example, if a table is part of
the recovery set and its indexes are in a separate tablespace, then the recovery set
must include the tablespace containing the index. Alternatively, the index can be
dropped. The recovery set tablespaces can be checked for self-containment with the
procedure DBMS_TTS.TRANSPORT_SET_CHECK.Unplugging the Tablespaces from the Auxiliary Database 的步骤
In this step, you recover the auxiliary database to the desired noncurrent time, then
unplug the desired tablespaces.
To unplug the auxiliary database tablespaces:
1. Make the tablespaces in the recovery set read-only by running the ALTER
TABLESPACE ... READ ONLY statement. For example, make sales_1 and
sales_2 read-only as follows:
ALTER TABLESPACE sales_1 READ ONLY;
ALTER TABLESPACE sales_2 READ ONLY;
2. Ensure that the recovery set is self-contained. For example:
EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK(’sales_1,sales_2’,TRUE,TRUE);
3. Query the transportable tablespace violations table to manage any
dependencies. For example:
SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;
This query should return no rows after all dependencies are managed. Refer to
Oracle9i Database Administrator’s Guide for more information about this table.
4. Generate the transportable set by running the Export utility as described in
Oracle9i Database Administrator’s Guide. Include all tablespaces in the recovery
set, as in the following example:
% exp SYS/pwd TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2) TTS_FULL_CHECK=y
Performing TSPITR with Transportable Tablespaces
This command generates an export file named expdat.dmp.
我已经确认users 表空间是全新的只有一个测试表:test,没有index,没有和其他表空间有关联。
况且我在primary db的execute sys.dbms_tts.transport_set_check('users',true);
是通过的,为何我在clone db recover 后的 execute sys.dbms_tts.transport_set_check('users',true);就不能通过?
是不是这个引起的问题:临时表空间是不被backup/recover的,需要手工添加临时文件
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
EXP-00008: ORACLE error 29341 encountered
ORA-29341: The transportable set is not self-contained
ORA-06512: at "SYS.DBMS_PLUGTS", line 1093
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
怪!另:TEMP文件怎么加? TEMP表空间和数据文件都不能OFFLINE的,不知怎么处理。
recovery set through the DROP TABLESPACE statement. For example:
DROP TABLESPACE sales_1 INCLUDING CONTENTS;
DROP TABLESPACE sales_2 INCLUDING CONTENTS;
2. Restore the recovery set datafiles from the auxiliary database to the recovery set
file locations in the primary database. For example:
% cp /net/aux_host/aux/sales_1.f /net/primary_host/oracle/dbs/sales_1.f
% cp /net/aux_host/aux/sales_2.f /net/primary_host/oracle/dbs/sales_2.f
3. Move the export file expdat.dmp to the primary host. For example, enter:
% cp /net/aux_host/aux/expdat.dmp /net/primary_host/oracle/dbs/expdat.dmp
4. Plug in the transportable set into the primary database by running Import as
described in Oracle9i Database Administrator’s Guide. For example:
% imp TRANSPORT_TABLESPACE=y FILE=expat.dmp
DATAFILES=(’/oracle/dbs/sales_1.f’,’/oracle/dbs/sales_2.f’)
5. Make the recovered tablespaces read write by issuing the ALTER TABLESPACE
READ WRITE statement. For example:
ALTER TABLESPACE sales_1 READ WRITE;
ALTER TABLESPACE sales_2 READ WRITE;
大家可以参考Oracle9i User-Managed Backup and Recovery Guide在试验的过程中总觉得恢复不是很稳定,很容易出错,虽然后来成功了,但我还想挂两天,大家讨论讨论,两天后结帖。呵呵。