oracle中没有你说的这种功能,可以通过两种方式实现:
1、最好用的当然是exp和imp了
2、Transportable Tablespaces,它也是exp/imp的变形,细说一下这种方法,我这儿的资料是E文的,没时间翻译,慢慢看吧。
步骤:
Let’s discuss the steps required to transport tablespaces ACC_DATA and ACC_
INDEX from the production database to the test database. The following
steps are to be performed on the production (source) database:
1. After identifying the self-contained tablespaces, make them read-only.
ALTER TABLESPACE ACC_DATA READ ONLY;
ALTER TABLESPACE ACC_INDEX READ ONLY;
2. Generate the metadata export file. You must specify both the
TABLESPACES and the TRANSPORT_TABLESPACE parameters. The FILE
parameter defaults to expdat.dmp. The other parameters that can be
specified are TRIGGERS, CONSTRAINTS, and GRANTS. The default value
for these parameters is Y; if you specify N, triggers, constraints, and
grants will not be exported. If the tablespaces transported are not selfcontained,
the export will fail. Connect to the database using SYS AS
SYSDBA.
exp FILE=acc_tts.dmp TRANSPORT_TABLESPACE=Y
TABLESPACES=(ACC_DATA,ACC_INDEX) CONSTRAINTS=N
3. Copy the data files to the target server or directory using an OS copy
or ftp commands. The data files can be identified using the following
query.
SELECT FILE_NAME
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME IN ('ACC_DATA’, 'ACC_INDEX’);
4. After the copy is complete, change the tablespaces back to read-write
mode (optional step).
ALTER TABLESPACE ACC_DATA READ WRITE;
ALTER TABLESPACE ACC_INDEX READ WRITE;
5. Copy the dump file created in step 2 to the target server.
The following steps are to be performed on the test server (target database).
Make sure the data files are copied to the proper location.
6. Import the metadata information about the tablespace and its objects
to the target database (“plug in” the tablespace).
imp FILE=acc_ts.dmp TRANSPORT_TABLESPACE=Y
TABLESPACES=(ACC_DATA,ACC_INDEX) TTS_OWNERS=('JAMES’)
DATAFILES=('/u01/acc_data01.dbf’,
'/u01/acc_data02.dbf’,
'/u02/acc_index01.dbf’)
FROMUSER=('JAMES’) TOUSER=('JOHN’)
The only mandatory parameters are TRANSPORT_TABLESPACE and
DATAFILES. If you do not specify TABLESPACES or TTS_OWNERS,
Oracle will identify those values from the export file. If you do not
specify FROMUSER and TOUSER, Oracle will try to import the objects
to the same username that owned these objects in the source database.
The username must already exist in the target database.
7. Change the tablespaces to read-write mode.
ALTER TABLESPACE ACC_DATA READ WRITE;
ALTER TABLESPACE ACC_INDEX READ WRITE;
1、最好用的当然是exp和imp了
2、Transportable Tablespaces,它也是exp/imp的变形,细说一下这种方法,我这儿的资料是E文的,没时间翻译,慢慢看吧。
步骤:
Let’s discuss the steps required to transport tablespaces ACC_DATA and ACC_
INDEX from the production database to the test database. The following
steps are to be performed on the production (source) database:
1. After identifying the self-contained tablespaces, make them read-only.
ALTER TABLESPACE ACC_DATA READ ONLY;
ALTER TABLESPACE ACC_INDEX READ ONLY;
2. Generate the metadata export file. You must specify both the
TABLESPACES and the TRANSPORT_TABLESPACE parameters. The FILE
parameter defaults to expdat.dmp. The other parameters that can be
specified are TRIGGERS, CONSTRAINTS, and GRANTS. The default value
for these parameters is Y; if you specify N, triggers, constraints, and
grants will not be exported. If the tablespaces transported are not selfcontained,
the export will fail. Connect to the database using SYS AS
SYSDBA.
exp FILE=acc_tts.dmp TRANSPORT_TABLESPACE=Y
TABLESPACES=(ACC_DATA,ACC_INDEX) CONSTRAINTS=N
3. Copy the data files to the target server or directory using an OS copy
or ftp commands. The data files can be identified using the following
query.
SELECT FILE_NAME
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME IN ('ACC_DATA’, 'ACC_INDEX’);
4. After the copy is complete, change the tablespaces back to read-write
mode (optional step).
ALTER TABLESPACE ACC_DATA READ WRITE;
ALTER TABLESPACE ACC_INDEX READ WRITE;
5. Copy the dump file created in step 2 to the target server.
The following steps are to be performed on the test server (target database).
Make sure the data files are copied to the proper location.
6. Import the metadata information about the tablespace and its objects
to the target database (“plug in” the tablespace).
imp FILE=acc_ts.dmp TRANSPORT_TABLESPACE=Y
TABLESPACES=(ACC_DATA,ACC_INDEX) TTS_OWNERS=('JAMES’)
DATAFILES=('/u01/acc_data01.dbf’,
'/u01/acc_data02.dbf’,
'/u02/acc_index01.dbf’)
FROMUSER=('JAMES’) TOUSER=('JOHN’)
The only mandatory parameters are TRANSPORT_TABLESPACE and
DATAFILES. If you do not specify TABLESPACES or TTS_OWNERS,
Oracle will identify those values from the export file. If you do not
specify FROMUSER and TOUSER, Oracle will try to import the objects
to the same username that owned these objects in the source database.
The username must already exist in the target database.
7. Change the tablespaces to read-write mode.
ALTER TABLESPACE ACC_DATA READ WRITE;
ALTER TABLESPACE ACC_INDEX READ WRITE;
解决方案 »
- 求一分割函数
- 求条简单的SQL语句,当场验证,立马给分!谢谢
- Oracle 10g RAC集群心跳网络与共享存储网络
- bi publisher报表 中文名导出,求解
- oracle8.0.5和oracle9i导入导出,除了exp/imp还有别的方法吗?
- 请问在ORACLE8.16中是否不支持FLOAT数据类型?
- 请问在windows2000 server中怎样把OracleOraHome92ManagementServer设为自动启动?
- 有人听说过oracle弹性域吗?
- 一个存储大文件的问题,请高手指点!!!!!!
- delete 与 left join 怎么用?
- oracle下建库的简单问题
- 弱弱地问:如何在win2000下安装oracle??谢谢
当然你用EXP、IMP就不需要了,不过如果数据库数据很大你就麻烦了。
Transportable Tablespaces的话也行,但限制太多,不能有LOB这样的字段,不能把索引建在别的表空间等等限制。
要实现完全操作系统的拷贝来建库,可以在另一台机器上(相同的操作系统)建立一个目录相同,SID相同的数据库,然后把你要迁移的数据库上的数据文件、控制文件、日志文件、口令文件拷贝过来就完成了。
这样拷贝,新建的数据库的大小要不要和原来的一致,要不要关闭数据库等,请再详细一点行吗?谢谢。
你说的在oracle里面是冷备份恢复。
------------------------
| |
| 相逢何必曾相识 |
| |
------------------------
冷备份发生在数据库已经正常关闭的情况下,当正常关闭时会提供给我们一个完整的数据库。
数据库使用的每个文件都被备份下来,这些文件包括:
所有数据文件、所有控制文件、所有联机REDO LOG 文件、INIT.ORA文件(可选)
值得注意的是冷备份必须是数据库关闭的情况下完成,当数据库开着的时候,执行数据库文件系统备份无效。
作冷备份一般步骤是:
1:正常关闭要备份的实例(instance);当然,在关闭前应通知所有正在使用的用户。
2: 备份整个数据库到一个目录
3:启动数据库
恢复方法:只要将原来的冷备份文件铐回在关闭状态下的数据库即可。------------------------
| |
| 相逢何必曾相识 |
| |
------------------------
然后数据库使用的每个文件都被备份下来,这些文件包括:
所有数据文件、所有控制文件、所有联机REDO LOG 文件、INIT.ORA文件(可选)
很简单
Sql server 2000中要执行sp_attachdb
导出后你只需要重新建一个数据库,不必建新的表空间,导入就行了,会把你的表空间加到数据库中的。