冷备克隆数据库,需要关闭源库。一般用于没有开启归档的测试库。
源库:srcdb 目标库:trgdb
1.将控制信息备份到trace文件(源端执行)
alter database backup controlfile to trace as '/export/home/oracle/ctrl_bkokp.sql';
2.记录下数据文件的位置(源端执行)
SQL> select file_name from dba_data_files; FILE_NAME --------------------------------------------------------------- /u03/oracle/oradata/SRCDB/system01.dbf /u03/oracle/oradata/SRCDB/sysaux01.dbf /u03/oracle/oradata/SRCDB/undotbs01.dbf /u03/oracle/oradata/SRCDB/users01.dbf /u03/oracle/oradata/SRCDB/CTLDATA_01.dbf /u03/oracle/oradata/SRCDB/CTLIDX_01.dbf /u03/oracle/oradata/SRCDB/catalog01.dbf /u03/oracle/oradata/SRCDB/catalog_idx01.dbf /u03/oracle/oradata/SRCDB/GGATE_01.dbf SQL> select file_name from dba_temp_files; FILE_NAME -------------------------------------------------------------- /u03/oracle/oradata/SRCDB/temp01.dbf /u03/oracle/oradata/SRCDB/catalog_temp01.dbf
3.关闭源库
SHUTDOWN IMMEDIATE;
4.将数据文件和临时文件拷贝到目标库
scp /u03/oracle/oradata/SRCDB/*dbf oracle@targret-host.dbclass.com:/u03/oracle/oradata/TRGDB/
5.目标库准备参数文件
从源库拷贝过来,改一下即可。
cat initTRGDB.ora *.audit_file_dest='/u01/app/oracle/admin/TRGDB/adump' *.audit_trail='D *.compatible='12.1.0.2.0' *.control_files='/u03/oracle/oradata/TRGDB/control01.ctl','/u03/oracle/oradata/TRGDB/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='TRGDB' *.diagnostic_dest='/u01/app/oracle/'*.event='' *.open_cursors=300 *.pga_aggregate_target=524288000 *.processes=1000 *.remote_login_passwordfile='EXCLUSIVE' *.sec_case_sensitive_logon=FALSE *.sessions=1536 *.sga_max_size=7373586432 *.sga_target=7373586432 *.undo_tablespace='UNDOTBS1'
6.将目标库启动到nomount状态
export ORACLE_SID=TRGDB
startup nomount pfile=initTRGDB.ora
7.目标库重建控制文件
使用步骤1创建的trace信息,重建控制文件
SQL>CREATE CONTROLFILE SET DATABASE "SRCDB" RESETLOGS FORCE LOGGING ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 2336 LOGFILE GROUP 1 '/u01/oracle/oradata/TRGDB/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/oracle/oradata/TRGDB/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/oracle/oradata/TRGDB/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/u01/oracle/oradata/TRGDB/system01.dbf', '/u01/oracle/oradata/TRGDB/CTLDATA_01.dbf', '/u01/oracle/oradata/TRGDB/sysaux01.dbf', '/u01/oracle/oradata/TRGDB/undotbs01.dbf', '/u01/oracle/oradata/TRGDB/CTLIDX_01.dbf', '/u01/oracle/oradata/TRGDB/users01.dbf', '/u01/oracle/oradata/TRGDB/catalog01.dbf', '/u01/oracle/oradata/TRGDB/catalog_idx01.dbf', '/u01/oracle/oradata/TRGDB/GGATE_01.dbf' CHARACTER SET AL32UTF8 ; / controlfile created;
这里使用了set database,因为克隆的是个新库。
CREATE CONTROLFILE SET DATABASE “SRCDB” RESETLOGS FORCE LOGGING ARCHIVELOG
如果想和源库一样,使用reuse database命令。
8.以resetlog模式打开数据库
ALTER DATABASE OPEN RESETLOGS;
9.添加temp文件
ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/oracle/oradata/TRGDB/temp01.dbf' SIZE 11529M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; ALTER TABLESPACE CATALOG_TEMP ADD TEMPFILE '/u03/oracle/oradata/TRGDB/catalog_temp01.dbf' SIZE 524288000 REUSE AUTOEXTEND OFF;