If you are using Export/Import , Take care the storage in your destination database . You must create big enough tablespace under the destination user ( because the source user may have more than one tablespace and all the data will be merged and export to the default tablespace of destination user)
Assume the original database is called PROD1 and you want to create a CLONE1 duplicate database.Procedure - Find some disk space and create appropriate directories / file systems for you cloned database (conforming to the OFA guidelines)- Connect to the PROD1 instance and dump the controlfile using the SQL commandALTER DATABASE BACKUP CONTROLFILE TO TRACE;This will put a text copy of the controlfile in the USER_DUMP_DEST- Shutdown Normal PROD1 database- Perform an operating system copy of PROD1 to the new location where CLONE1 will reside.- Startup the PROD1 instance- Edit the controlfile you created and change all the path names of the database to the new location.- Set your environment to the CLONE1 instance and run Server Manager (svrmgrl)CONNECT INTERNAL STARTUP NOMOUNTCREATE CONTROLFILE REUSE SET DATABASE "CLONE1" RESETLOGS NOARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 2 MAXDATAFILES 64 MAXINSTANCES 8 MAXLOGHISTORY 800 LOGFILE GROUP 1 '/oracle/data/CLONE1/redos/log1CLONE1.dbf' SIZE 512K, GROUP 2 '/oracle/data/CLONE1/redos/log2CLONE1.dbf' SIZE 512K, GROUP 3 '/oracle/data/CLONE1/redos/log3CLONE1.dbf' SIZE 512K DATAFILE '/oracle/data/CLONE1/system_ts/systCLONE1.dbf' SIZE 25M, '/oracle/data/CLONE1/data_ts/data_CLONE1.dbf' SIZE 230M, '/oracle/data/CLONE1/index_ts/index_CLONE1.dbf' SIZE 230M, '/oracle/data/CLONE1/rbs_ts/rbs_CLONE1.dbf' SIZE 10M, '/oracle/data/CLONE1/temp_ts/temp_CLONE1.dbf' SIZE 10M, '/oracle/data/CLONE1/tools_ts/ts_tools_CLONE1_02.dbf' SIZE 15M, '/oracle/data/CLONE1/users_ts/ts_users_CLONE1.dbf' SIZE 1M, ;ALTER DATABASE OPEN RESETLOGS;- That's it, an exact duplicate of PROD1 and a painless procedure.
不过,如果数据库设置不复杂的话,倒一下数据库数据就可以了
copy file
create control file
ok
我作的系统因为还没有正式投入使用,所以就给客户做了两个数据库,一个是作程序测试时用的(因可能客户会输入大量的废数据),一个是正式的,两个数据库经常导来导去的,我用的都是Export和Import,每次操作都很麻烦,得把测试数据库先删掉,然后重建数据库表空间,在将从正式数据库导出的*.dmp文件导进去,很费时间,请问各位高手碰到这样的问题有什么好的解决办法。
- Find some disk space and create appropriate directories / file systems for you cloned database (conforming to the OFA guidelines)- Connect to the PROD1 instance and dump the controlfile using the SQL commandALTER DATABASE BACKUP CONTROLFILE TO TRACE;This will put a text copy of the controlfile in the USER_DUMP_DEST- Shutdown Normal PROD1 database- Perform an operating system copy of PROD1 to the new location where CLONE1 will reside.- Startup the PROD1 instance- Edit the controlfile you created and change all the path names of the database to the new location.- Set your environment to the CLONE1 instance and run Server Manager (svrmgrl)CONNECT INTERNAL
STARTUP NOMOUNTCREATE CONTROLFILE REUSE SET DATABASE "CLONE1" RESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 64
MAXINSTANCES 8
MAXLOGHISTORY 800
LOGFILE
GROUP 1 '/oracle/data/CLONE1/redos/log1CLONE1.dbf' SIZE 512K,
GROUP 2 '/oracle/data/CLONE1/redos/log2CLONE1.dbf' SIZE 512K,
GROUP 3 '/oracle/data/CLONE1/redos/log3CLONE1.dbf' SIZE 512K
DATAFILE
'/oracle/data/CLONE1/system_ts/systCLONE1.dbf' SIZE 25M,
'/oracle/data/CLONE1/data_ts/data_CLONE1.dbf' SIZE 230M,
'/oracle/data/CLONE1/index_ts/index_CLONE1.dbf' SIZE 230M,
'/oracle/data/CLONE1/rbs_ts/rbs_CLONE1.dbf' SIZE 10M,
'/oracle/data/CLONE1/temp_ts/temp_CLONE1.dbf' SIZE 10M,
'/oracle/data/CLONE1/tools_ts/ts_tools_CLONE1_02.dbf' SIZE 15M,
'/oracle/data/CLONE1/users_ts/ts_users_CLONE1.dbf' SIZE 1M,
;ALTER DATABASE OPEN RESETLOGS;- That's it, an exact duplicate of PROD1 and a painless procedure.