如何快速克隆一个Oracle10gASM数据库
来源：互联网 发布：个人域名 企业经营 编辑：程序博客网 时间：2024/05/08 00:40
如何快速克隆一个Oracle10gASM数据库
有时候为了测试方便，需要建立一个和源库一样的数据库来操作，这时候一个简单的做法是直接克隆，通过配置参数文件和直接复制数据文件来快速建立一个新的库，本文演示了基于10g版本ASM存储下的一个操作过程。
源库SID：erptest 目标库SID：erpoptm
第一步，首先获取源库的参数文件
SQL> CREATE PFILE='/tmp/init_temp.ora' from spfile;
File created.
参照以上文件建立一个目标库的参数文件initerpoptm.ora如下：
erptest.__db_cache_size=1828716544
erptest.__java_pool_size=16777216
erptest.__large_pool_size=16777216
erptest.__shared_pool_size=268435456
erptest.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/erpoptm/adump'
*.background_dump_dest='/u01/app/oracle/admin/erpoptm/bdump'
*.compatible='10.2.0.1.0'
*.control_files='+DGOPTM/erpoptm/control01.ctl','+DGOPTM/erpoptm/control02.ctl','+DGOPTM/erpoptm/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/erpoptm/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='erptest'
*.db_recovery_file_dest='+DGOPTM'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=erpoptmXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2147483648
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/u01/app/oracle/admin/erpoptm/udump'
改动的内容都是各种文件的路径，注意这里的db_name不要修改，否则无法mount数据库，因为db_name要和数据文件头记录的db_name一致。
第二步，建立对应的日志、跟踪等文件的路径
[oracle@erpdevdb tmp]$ cd /u01/app/oracle/admin/
[oracle@erpdevdb admin]$ mkdir erpoptm
[oracle@erpdevdb admin]$ cd erpoptm
[oracle@erpdevdb erpoptm]$ ll
total 0
[oracle@erpdevdb erpoptm]$ mkdir adump
[oracle@erpdevdb erpoptm]$ mkdir bdump
[oracle@erpdevdb erpoptm]$ mkdir udump
[oracle@erpdevdb erpoptm]$ mkdir cdump
[oracle@erpdevdb erpoptm]$ ll
total 32
drwxr-xr-x 2 oracle oinstall 4096 Mar 8 11:45 adump
drwxr-xr-x 2 oracle oinstall 4096 Mar 8 11:45 bdump
drwxr-xr-x 2 oracle oinstall 4096 Mar 8 11:45 cdump
drwxr-xr-x 2 oracle oinstall 4096 Mar 8 11:45 udump
第三步，获取源库的控制文件内容并修改
SQL> alter database backup controlfile to trace;
Database altered.
在udump目录下查看生成的trace文件，找到建立控制文件的部分脚本并修改如下：
CREATE CONTROLFILE REUSE DATABASE "ERPTEST" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 29200
LOGFILE
GROUP 1 '+DGOPTM/erpoptm/redo01.log' SIZE 50M,
GROUP 2 '+DGOPTM/erpoptm/redo02.log' SIZE 50M,
GROUP 3 '+DGOPTM/erpoptm/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'+DGOPTM/erpoptm/system01.dbf',
'+DGOPTM/erpoptm/undotbs01.dbf',
'+DGOPTM/erpoptm/sysaux01.dbf',
'+DGOPTM/erpoptm/users01.dbf',
'+DGOPTM/erpoptm/datafile/erp_ht.268',
'+DGOPTM/erpoptm/datafile/erp_ht.269',
'+DGOPTM/erpoptm/datafile/erp_index.270',
'+DGOPTM/erpoptm/datafile/erp_wl.271',
'+DGOPTM/erpoptm/datafile/erp.272',
'+DGOPTM/erpoptm/datafile/erp_sp.273',
'+DGOPTM/erpoptm/datafile/erp_cw.274',
'+DGOPTM/erpoptm/datafile/erp_zj.275',
'+DGOPTM/erpoptm/datafile/erp_xm.276',
'+DGOPTM/erpoptm/datafile/erp_ht.277'
CHARACTER SET ZHS16GBK
第四步，拷贝源库的数据文件到目标库的对应路径：
先要为目标库建立好ASM磁盘组，建立的详细步骤可参考这篇文章：
[root@erpdevdb ~]# oracleasm createdisk VOL5 /dev/sda10
Writing disk header: done
Instantiating disk: done[oracle@erpdevdb ~]$ export ORACLE_SID=+ASM
SQL> create diskgroup DGOPTM external redundancy disk 'ORCL:VOL5';
Diskgroup created.
[oracle@erpdevdb ~]$ asmcmd
ASMCMD> ls
DGDEV/
DGOPTM/
DGPAPER/
DGSTUDY/
DGTEST/
ASMCMD> cd DGOPTM/ASMCMD> mkdir ERPOPTM/
然后在源库的实例中用DBMS_FILE_TRANSFER.COPY_FILE包拷贝文件：
SQL> create directory DGFROM as '+DGTEST/ERPTEST/DATAFILE';
Directory created.
SQL> create directory DGTO as '+DGOPTM/ERPOPTM/DATAFILE';
Directory created.
执行以下命令拷贝数据文件：
exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','erp_ht.268.711907283','DGTO','erp_ht.268');
exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','erp_ht.269.711907397','DGTO','erp_ht.269');
exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','erp_ht.277.711972029','DGTO','erp_ht.277');
exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','erp_index.270.711907487','DGTO','erp_index.270');
exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','erp_wl.271.711907545','DGTO','erp_wl.271');
exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','erp.272.711907565','DGTO','erp.272');
exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','erp_sp.273.711907695','DGTO','erp_sp.273');
exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','erp_cw.274.711907757','DGTO','erp_cw.274');
exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','erp_xm.276.711907831','DGTO','erp_xm.276');
exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','erp_zj.275.711907801','DGTO','erp_zj.275');
要注意的是，这里的目标文件名不能带有ASM自身生成的那一串数字，否则会报错如下：
ORA-19504: failed to create file
"+DGOPTM/ERPOPTM/DATAFILE/erp_zj.275.711907801"
ORA-17502: ksfdcre:4 Failed to create file
+DGOPTM/ERPOPTM/DATAFILE/erp_zj.275.711907801
ORA-15046: ASM file name '+DGOPTM/ERPOPTM/DATAFILE/erp_zj.275.711907801' is not
in single-file creation form
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193
ORA-06512: at line 1
这是因为这串数字是ASM用来进行标识的信息，文档Doc ID: Note:452158.1中有说明。老杨的一篇文章描述了类似的问题。
然后再拷贝system等表空间和redo等文件：
SQL> drop directory DGFROM;
Directory dropped.
SQL> create directory DGFROM as '+DGTEST/ERPTEST';
Directory created.
SQL> drop directory DGTO;
Directory dropped.
SQL> create directory DGTO as '+DGOPTM/ERPOPTM';
Directory created.
SQL> exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','redo01.log','DGTO','redo01.log');
PL/SQL procedure successfully completed.
SQL> exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','redo02.log','DGTO','redo02.log');
PL/SQL procedure successfully completed.
SQL> exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','redo03.log','DGTO','redo03.log');
PL/SQL procedure successfully completed.
SQL> exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','system01.dbf','DGTO','system01.dbf');
PL/SQL procedure successfully completed.
SQL> exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','undotbs01.dbf','DGTO','undotbs01.dbf');
PL/SQL procedure successfully completed.
SQL> exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','sysaux01.dbf','DGTO','sysaux01.dbf');
PL/SQL procedure successfully completed.
SQL> exec DBMS_FILE_TRANSFER.COPY_FILE('DGFROM','users01.dbf','DGTO','users01.dbf');
PL/SQL procedure successfully completed.
需要注意的是，copy过来的文件并不是文件存储的实际路径，而是按原有的路径组织的，新的文件名只是一个alias而已：
ASMCMD> cd ERPOPTM/
ASMCMD> ls -l
Type Redund Striped Time Sys Name
N DATAFILE/
N control01.ctl => +DGOPTM/ERPTEST/CONTROLFILE/Current.273.713177855
N control02.ctl => +DGOPTM/ERPTEST/CONTROLFILE/Current.274.713177855
N control03.ctl => +DGOPTM/ERPTEST/CONTROLFILE/Current.275.713177855
N redo01.log => +DGOPTM/ERPTEST/ONLINELOG/group_0.266.713120427
N redo02.log => +DGOPTM/ERPTEST/ONLINELOG/group_0.267.713120435
N redo03.log => +DGOPTM/ERPTEST/ONLINELOG/group_0.268.713120441
N sysaux01.dbf => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.271.713120497
N system01.dbf => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.269.713120449
N undotbs01.dbf => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.270.713120467
N users01.dbf => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.272.713120509ASMCMD> cd DATAFILE/
ASMCMD> ls -l
Type Redund Striped Time Sys Name
N erp.272 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.261.713119325
N erp_cw.274 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.263.713119613
N erp_ht.268 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.257.713118643
N erp_ht.269 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.256.713118843
N erp_ht.277 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.258.713119001
N erp_index.270 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.259.713119135
N erp_sp.273 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.262.713119553
N erp_wl.271 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.260.713119275
N erp_xm.276 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.264.713119665
N erp_zj.275 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.265.713119693
至此文件拷贝完成。
第五步，建立密码文件
[oracle@erpdevdb dbs]$ orapwd file=/u01/app/oracle/oracle/product/10.2.0/db_1/dbs/orapwerpoptm password=erpoptm entries=5
第六步，启动数据库
先启动到nomount状态，这时会用到参数文件：
[oracle@erpdevdb dbs]$ sqlplus " / as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 8 14:01:08 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.Total System Global Area 2147483648 bytes
Fixed Size 2022144 bytes
Variable Size 486540544 bytes
Database Buffers 1644167168 bytes
Redo Buffers 14753792 bytes
SQL>
SQL> alter database mount
2 /
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
oracle会先找spfile，然后找pfile，找若没有参数文件，则会报错：
[oracle@erpdevdb dbs]$ export ORACLE_SID=erpoptm
[oracle@erpdevdb dbs]$ sqlplus " / as sysdba"SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 8 14:00:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/oracle/product/10.2.0/db_1/dbs/initerpoptm.ora'
mount时会找控制文件，没有则会出现如上所示的错误，可以在nomount阶段来建立控制文件，先关闭源库，然后在目标库启动到nomount阶段来建立控制文件：
SQL> CREATE CONTROLFILE REUSE DATABASE "ERPTEST" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 29200
7 LOGFILE
8 GROUP 1 '+DGOPTM/erpoptm/redo01.log' SIZE 50M,
9 GROUP 2 '+DGOPTM/erpoptm/redo02.log' SIZE 50M,
10 GROUP 3 '+DGOPTM/erpoptm/redo03.log' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '+DGOPTM/erpoptm/system01.dbf',
14 '+DGOPTM/erpoptm/undotbs01.dbf',
15 '+DGOPTM/erpoptm/sysaux01.dbf',
16 '+DGOPTM/erpoptm/users01.dbf',
17 '+DGOPTM/erpoptm/datafile/erp_ht.268',
18 '+DGOPTM/erpoptm/datafile/erp_ht.269',
19 '+DGOPTM/erpoptm/datafile/erp_index.270',
20 '+DGOPTM/erpoptm/datafile/erp_wl.271',
21 '+DGOPTM/erpoptm/datafile/erp.272',
22 '+DGOPTM/erpoptm/datafile/erp_sp.273',
23 '+DGOPTM/erpoptm/datafile/erp_cw.274',
24 '+DGOPTM/erpoptm/datafile/erp_zj.275',
25 '+DGOPTM/erpoptm/datafile/erp_xm.276',
26 '+DGOPTM/erpoptm/datafile/erp_ht.277'
27 CHARACTER SET ZHS16GBK
28 /Control file created.
为什么要先关闭源库呢，这是因为二者有相同的db_name，oracle通过一个lk开头的文件来在运行阶段lock某个库，如果不关闭会在alert日志中看到以下错误：
Tue Mar 9 08:53:20 2010
sculkget: failed to lock /u01/app/oracle/oracle/product/10.2.0/db_1/dbs/lkERPTEST exclusive
sculkget: lock held by PID: 29747
Tue Mar 9 08:53:21 2010
ORA-09968: unable to lock file
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 29747
Tue Mar 9 08:53:21 2010
Errors in file /u01/app/oracle/admin/erpoptm/udump/erpoptm_ora_26143.trc:
ORA-01158: database already mounted
由于现在在同一个主机上有两个相同db_name的数据库，那么如何能同时启动呢，这需要指定参数db_unique_ name来区分开来，这个参数在standby环境也是必须设置的。
修改源库的db_unique_name如下：
SQL> alter system set db_unique_name=erptest scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.SQL> startup
ORACLE instance started.
回到正题，在创建控制文件的过程中会验证数据文件是否存在，如果不存在则报以下错误：
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file '+DGOPTM/erpoptm/system01.dbf'
ORA-17503: ksfdopn:2 Failed to open file +DGOPTM/erpoptm/system01.dbf
ORA-15173: entry 'system01.dbf' does not exist in directory 'erpoptm'
也会检查参数文件中的db_name和数据文件头的db_name是否相同，否则报以下错误:
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name ERPTEST in file header does not match given name of
ERPOPTM
ORA-01110: data file 1: '+DGOPTM/erpoptm/system01.dbf'
接着打开数据库：
SQL> alter database open
2 /
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '+DGOPTM/erpoptm/system01.dbf'
这说明需要介质恢复，可以一个一个数据文件的来recover，也可以直接recover database：
SQL> recover database;
Media recovery complete.
SQL> alter database open;Database altered.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string erptest
SQL> show parameter instance_nameNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string erpoptm
注意这种情况下启动的数据库会做实例恢复，因为redo和数据文件等都是copy过来的。这样就建立了一个db_name为erptest，而instance_name和SID为erpoptm的数据库。
然后要马上建立临时表空间：
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DGOPTM/erpoptm/temp01.dbf' SIZE 8192M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
但是事情并没有结束，很快就发些这个新搭建的库会很快死掉，alert日志记录了如下的信息：
Tue Mar 9 10:11:24 2010
Errors in file /u01/app/oracle/admin/erpoptm/bdump/erpoptm_mmon_30564.trc:
ORA-00600: internal error code, arguments: [504], [0x06000AD18], [1], [0], [ksv instance], [0], [0], [0x0DCFCE590]
Tue Mar 9 10:11:26 2010
Errors in file /u01/app/oracle/admin/erpoptm/bdump/erpoptm_mmon_30564.trc:
ORA-00600: internal error code, arguments: [504], [0x06000AD18], [1], [0], [ksv instance], [0], [0], [0x0DCFCE590]
Tue Mar 9 10:11:26 2010
Errors in file /u01/app/oracle/admin/erpoptm/bdump/erpoptm_mmon_30564.trc:
ORA-00600: internal error code, arguments: [4193], [2887], [2890], [], [], [], [], []
碰到很多4193的600错误，这种一般和undo有关，重建一下undo即可：
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1SQL> create undo tablespace undotbs2 datafile '+DGOPTM/ERPOPTM/undotbs2.dbf' size 1024m;
Tablespace created.
SQL> alter system set undo_tablespace=undotbs2;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
再修改一下参数文件initerpoptm.ora，修改这里的undo：
*.undo_tablespace='UNDOTBS2'
重新启动数据库，设置db_unique_name和spfile：
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL>
SQL> create spfile from pfile;File created.
SQL> shutdown immediate
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.
SQL> show parameter spfileNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/oracle/product
/10.2.0/db_1/dbs/spfileerpoptm
.ora
SQL> alter system set db_unique_name=erpoptm scope=spfile;System altered.
SQL> shutdown immediate
SQL> startup
SQL>
SQL> show parameter db_nameNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string erptest
SQL>
SQL>
SQL>
SQL> show parameter instance_name;NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string erpoptm
SQL>
SQL>
SQL>
SQL> show parameter db_unique_nameNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ERPOPTM
可以看到spfile默认会放到dbs目录下。至此这个数据库应该是可用了，当然还要配置一下网络监听等服务：
链接：http://www.banping.com/2010/03/09/clone_database/[oracle@erpdevdb admin]$ vi tnsnames.ora
ERPOPTM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = erpdevdb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = erpoptm)
)
)[oracle@erpdevdb admin]$ lsnrctl
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:start stop status
services version reload
save_config trace spawn
change_password quit exit
set* show*LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully[oracle@erpdevdb admin]$ vi /etc/oratab
erpoptm:/u01/app/oracle/oracle/product/10.2.0/db_1:Y
- 如何快速克隆一个Oracle10gASM数据库
- 如何快速的克隆oracle数据库到另外的机器
- 如何快速克隆一个github项目到本地
- 如何在同一个服务器上克隆出一个备用数据库
- 如何克隆一个json对象
- 如何利用Direct NFS克隆数据库
- C#中如何深度克隆一个对象？
- 一个注射点如何最快速确认是什么类型数据库
- 克隆数据库
- 克隆数据库
- 克隆数据库
- 克隆数据库
- Oracle认证：学会使用DBCA克隆一个数据库
- 如何复制一个java对象（浅克隆与深度克隆）
- RH442 - 如何通过lvm快照克隆一个kvm虚拟机
- EBS快速克隆步骤
- 快速克隆网站
- 如何快速创建一个临时文件
- 根据ID填充文本框
- 我需要的是什么？
- Linux设备驱动程序学习之外设通信
- 慢启动 拥塞避免 快速重传 快速恢复 选择性应答SACK
- joomla 1.5 笔记
- 如何快速克隆一个Oracle10gASM数据库
- jsp页面UTF-8乱码解决办法
- linux之cut用法
- windows2003 ghost装机 安装 Microsoft .NET Framework 3.5 SP1
- 关于Vector
- PE文件头
- 规模是个大问题
- Visual Studio IDE 常用功能
- 使用arm-eabi-addr2line, ndk-stack工具跟踪Android调用堆栈
