分类： Oracle
2012-10-29 13:14:34
克隆一个oracle数据库
版本：10.2.0.1.0
源数据库： SID : bkeep
数据库文件存储在: Automatic Storage Management (ASM) disk group
目标数据库：SID : xunyiqq
数据库文件存储在: Automatic Storage Management (ASM) disk group
导读：
第一步，获取源库的参数文件
第二步，建立对应的日志、跟踪等文件的路径
第三步，获取源库的控制文件内容并修改
Tips：这里有个小技巧，如何获取oracle跟踪文件
第四步，拷贝源库的数据文件到目标库的对应路径
利用oracle10g新增的DBMS_FILE_TRANSFER包来完成数据文件的copy
说明：DBMS_FILE_TRANSFER.COPY_FILE('BKEEPFROM','ERP.266.713884285','XUNYIQQTO','ERP.266');
第五步，建立密码文件
第六步，启动目标数据库，创建控制文件
第七步，为新数据库添加临时表空间
第八步，更改新数据库的DB_UNIQUE_NAME
第九步，创建SPFILE
第十步，为新库添加UNDO段
第十一步，配置网络监听服务
第十二步，配置/etc/oratab
正文：
第一步，获取源库的参数文件SQL> create pfile='/tmp/init_temp.ora' from spfile;
File created.
注意修改里面的路径。banping说db_name不要修改，否则无法mount数据库，因为db_name要和数据文件头记录的db_name保持一致[root@kk tmp]# vi init_temp.ora
bkeep.__db_cache_size=838860800
bkeep.__java_pool_size=16777216
bkeep.__large_pool_size=16777216
bkeep.__shared_pool_size=268435456
bkeep.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/xunyiqq/adump'
*.background_dump_dest='/opt/oracle/admin/xunyiqq/bdump'
*.compatible='10.2.0.1.0'
*.control_files='+GROUP1/xunyiqq/control01.ctl','+GROUP1/xunyiqq/control02.ctl','+GROUP1/xunyiqq/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/xunyiqq/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='bkeep'
*.db_recovery_file_dest='+GROUP1'
*.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=1147483648
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/xunyiqq/udump'
[root@kk tmp]# cp init_temp.ora /opt/oracle/product/10.2.0/db_1/dbs/initxunyiqq.ora
第二步，建立对应的日志、跟踪等文件的路径
[oracle@kk ~]$ mkdir -p /opt/oracle/admin/xunyiqq/udump
[oracle@kk ~]$ mkdir -p /opt/oracle/admin/xunyiqq/bdump
[oracle@kk ~]$ mkdir -p /opt/oracle/admin/xunyiqq/adump
[oracle@kk ~]$ mkdir -p /opt/oracle/admin/xunyiqq/cdump
[oracle@kk ~]$ exit
第三步，获取源库的控制文件内容并修改 Tips：这里有个小技巧，如何获取oracle跟踪文件
SQL> alter database backup controlfile to trace;
Database altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/opt/oracle/admin/bkeep/udump/bkeep_ora_4126.trc
SQL> ! cat /opt/oracle/admin/bkeep/udump/bkeep_ora_4126.trc
CREATE CONTROLFILE REUSE DATABASE "BKEEP" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+GROUP1/bkeep/redo01.log' SIZE 100M,
GROUP 2 '+GROUP1/bkeep/redo02.log' SIZE 100M,
GROUP 3 '+GROUP1/bkeep/redo03.log' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'+GROUP1/bkeep/system01.dbf',
'+GROUP1/bkeep/undotbs01.dbf',
'+GROUP1/bkeep/sysaux01.dbf',
'+GROUP1/bkeep/erp01.dbf'
CHARACTER SET ZHS16GBK
;
注意，这里还是不要修改数据库名，只修改相关文件的路径bkeep--->xunyiqq
CREATE CONTROLFILE REUSE DATABASE "BKEEP" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+GROUP1/xunyiqq/redo01.log' SIZE 100M,
GROUP 2 '+GROUP1/xunyiqq/redo02.log' SIZE 100M,
GROUP 3 '+GROUP1/xunyiqq/redo03.log' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'+GROUP1/xunyiqq/system01.dbf',
'+GROUP1/xunyiqq/undotbs01.dbf',
'+GROUP1/xunyiqq/sysaux01.dbf',
'+GROUP1/xunyiqq/erp01.dbf'
CHARACTER SET ZHS16GBK
;
第四步，拷贝源库的数据文件到目标库的对应路径 新开一个ssh窗口[root@kk ~]# su - oracle
[oracle@kk ~]$ export ORACLE_SID=+ASM
[oracle@kk ~]$ asmcmd
ASMCMD> ls
GROUP1/
ASMCMD> cd group1
ASMCMD> ls
BKEEP/
ASMCMD> mkdir XUNYIQQ
下面查看group1底下的目录及其目录下的文件。将来这些文件都给它复制过去
ASMCMD> pwd
+group1/bkeep
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
control01.ctl
control02.ctl
control03.ctl
erp01.dbf
redo01.log
redo02.log
redo03.log
spfilebkeep.ora
sysaux01.dbf
system01.dbf
temp01.dbf
undotbs01.dbf
ASMCMD> cd datafile
ASMCMD> ls
ERP.266.713884285
SYSAUX.264.713884129
SYSTEM.262.713884099
UNDOTBS1.263.713884121
ASMCMD> pwd
+group1/bkeep/datafile
Tips：查看表a存在那个表空间里面
1 select TABLESPACE_NAME from dba_tables
2* where table_name = 'A'
TABLESPACE_NAME
------------------------------
SYSTEM
回到源库上操作 创建数据库目录
SQL> create directory BKEEPFROM as '+GROUP1/BKEEP/DATAFILE';
Directory created.
SQL> create directory XUNYIQQTO as '+GROUP1/XUNYIQQ/DATAFILE';
Directory created.
一则报错，一目了然！
SQL> BEGIN DBMS_FILE_TRANSFER.COPY_FILE('BKEEPFROM','SYSTEM.262.713884099','XUNYIQQTO','SYSTEM.262'); END;
*
ERROR at line 1:
ORA-19504: failed to create file "+GROUP1/XUNYIQQ/DATAFILE/SYSTEM.262"
ORA-17502: ksfdcre:4 Failed to create file +GROUP1/XUNYIQQ/DATAFILE/SYSTEM.262
ORA-15173: entry 'DATAFILE' does not exist in directory 'XUNYIQQ'
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193
ORA-06512: at line 1
亡羊补牢，创建xunyiqq目录
ASMCMD> ls
BKEEP/
XUNYIQQ/
ASMCMD> cd xunyiqq
ASMCMD> ls
ASMCMD> mkdir DATAFILE
利用oracle10g新增的DBMS_FILE_TRANSFER包来完成数据文件的copy
SQL> exec DBMS_FILE_TRANSFER.COPY_FILE('BKEEPFROM','ERP.266.713884285','XUNYIQQTO','ERP.266');
exec DBMS_FILE_TRANSFER.COPY_FILE('BKEEPFROM','SYSAUX.264.713884129','XUNYIQQTO','SYSAUX.264');
exec DBMS_FILE_TRANSFER.COPY_FILE('BKEEPFROM','SYSTEM.262.713884099','XUNYIQQTO','SYSTEM.262');
exec DBMS_FILE_TRANSFER.COPY_FILE('BKEEPFROM','UNDOTBS1.263.713884121','XUNYIQQTO','UNDOTBS1.263');
PL/SQL procedure successfully completed. <==这里要多敲几次回车
说明：DBMS_FILE_TRANSFER.COPY_FILE('BKEEPFROM','ERP.266.713884285','XUNYIQQTO','ERP.266'); 其中ERP.266.713884285拷贝到目标位置后不要跟.71388428，否则会报错的。 查看一下，数据文件copy过来了
ASMCMD> ls
ERP.266
SYSAUX.264
SYSTEM.262
UNDOTBS1.263
ASMCMD> pwd
+group1/xunyiqq/datafile
创建其它目录，这些目录其实可以不用创建，（从控制文件内容分析得知！）
ASMCMD> cd xunyiqq
ASMCMD> ls
DATAFILE/
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir PARAMETERFILE
ASMCMD> mkdir TEMPFILE
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
拷贝 redo等文件 重新建立一下数据库目录SQL> drop directory BKEEPFROM;
Directory dropped.
SQL> DROP DIRECTORY XUNYIQQTO;
Directory dropped.
SQL> create directory BKEEPFROM as '+GROUP1/BKEEP';
Directory created.
SQL> create directory XUNYIQQTO as '+GROUP1/XUNYIQQ';
Directory created.
SQL> exec DBMS_FILE_TRANSFER.COPY_FILE('BKEEPFROM','erp01.dbf','XUNYIQQTO','erp01.dbf');
exec DBMS_FILE_TRANSFER.COPY_FILE('BKEEPFROM','redo01.log','XUNYIQQTO','redo01.log');
exec DBMS_FILE_TRANSFER.COPY_FILE('BKEEPFROM','redo02.log','XUNYIQQTO','redo02.log');
exec DBMS_FILE_TRANSFER.COPY_FILE('BKEEPFROM','redo03.log','XUNYIQQTO','redo03.log');
exec DBMS_FILE_TRANSFER.COPY_FILE('BKEEPFROM','sysaux01.dbf','XUNYIQQTO','sysaux01.dbf');
exec DBMS_FILE_TRANSFER.COPY_FILE('BKEEPFROM','system01.dbf','XUNYIQQTO','system01.dbf');
exec DBMS_FILE_TRANSFER.COPY_FILE('BKEEPFROM','temp01.dbf','XUNYIQQTO','temp01.dbf');
exec DBMS_FILE_TRANSFER.COPY_FILE('BKEEPFROM','undotbs01.dbf','XUNYIQQTO','undotbs01.dbf');
PL/SQL procedure successfully completed.
banping：需要注意的是，copy过来的文件并不是文件存储的实际路径，而是按原有的路径组织的，新的文件名只是一个alias而已
ASMCMD> pwd
+group1/xunyiqq
ASMCMD> ls -l
Type Redund Striped Time Sys Name
N CONTROLFILE/
N DATAFILE/
N ONLINELOG/
N PARAMETERFILE/
N TEMPFILE/
N control01.ctl => +GROUP1/BKEEP/CONTROLFILE/Current.279.713936659
N control02.ctl => +GROUP1/BKEEP/CONTROLFILE/Current.280.713936659
N control03.ctl => +GROUP1/BKEEP/CONTROLFILE/Current.281.713936661
N erp01.dbf => +GROUP1/BKEEP/DATAFILE/COPY_FILE.272.713936017
N redo01.log => +GROUP1/BKEEP/ONLINELOG/group_0.273.713936019
N redo02.log => +GROUP1/BKEEP/ONLINELOG/group_0.274.713936025
N redo03.log => +GROUP1/BKEEP/ONLINELOG/group_0.275.713936029
N sysaux01.dbf => +GROUP1/BKEEP/DATAFILE/COPY_FILE.276.713936035
N system01.dbf => +GROUP1/BKEEP/DATAFILE/COPY_FILE.277.713936057
N temp01.dbf => +GROUP1/BKEEP/TEMPFILE/TEMPTBS1.282.713961311
N undotbs01.dbf => +GROUP1/BKEEP/DATAFILE/COPY_FILE.278.713936109
N undotbs02.dbf => +GROUP1/XUNYIQQ/DATAFILE/UNDOTBS2.283.713963285
第五步，建立密码文件SQL> !
[oracle@kk ~]$ orapwd file=/opt/oracle/product/10.2.0/db_1/dbs/orapwxunyiqq password=sys entries=5
第六步，启动目标数据库 先启动到nomount状态，这时只会用到参数文件 oracle会先找spfile，然后找pfile。
[oracle@kk ~]$ export ORACLE_SID=xunyiqq
[oracle@kk ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 18 03:38:59 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
更改数据库到mount状态
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01102: cannot mount database in EXCLUSIVE mode
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
错误1：为什么要先关闭源库呢？这是因为二者有相同的db_name，oracle通过一个lk开头的文件来在运行阶段lock某个库，如果不关闭会在alert日志中看到以下错误
ALTER DATABASE MOUNT
Thu Mar 18 10:36:41 2010
sculkget: failed to lock /opt/oracle//product/10.2.0/db_1/dbs/lkBKEEP exclusive
sculkget: lock held by PID: 6340
Thu Mar 18 10:36:41 2010
ORA-09968: unable to lock file
Linux Error: 11: Resource temporarily unavailable
我们先关闭bkeep数据库SQL> shutdown immediate;
然后再mount xunyiqqSQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
日志文件里面说找不到控制文件等等
Thu Mar 18 03:40:28 2010
ORA-00202: control file: '+GROUP1/xunyiqq/control01.ctl'
ORA-17503: ksfdopn:2 Failed to open file +GROUP1/xunyiqq/control01.ctl
ORA-15173: entry 'control01.ctl' does not exist in directory 'xunyiqq'
Thu Mar 18 03:40:28 2010
ORA-205 signalled during: alter database mount...
错误2：不到控制文件？？BANPING: mount时会找控制文件，没有则会出现如上所示的错误，可以在nomount阶段来建立控制文件，先关闭源库，然后在目标库启动到nomount阶段来建立控制文件
注意：创建控制文件是在nomount状态下进行的！ BANPING: 在创建控制文件的过程中会验证数据文件是否存在，如果不存在则报错
SQL> CREATE CONTROLFILE REUSE DATABASE "BKEEP" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+GROUP1/xunyiqq/redo01.log' SIZE 100M,
GROUP 2 '+GROUP1/xunyiqq/redo02.log' SIZE 100M,
GROUP 3 '+GROUP1/xunyiqq/redo03.log' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'+GROUP1/xunyiqq/system01.dbf',
'+GROUP1/xunyiqq/undotbs01.dbf',
'+GROUP1/xunyiqq/sysaux01.dbf',
'+GROUP1/xunyiqq/erp01.dbf'
CHARACTER SET ZHS16GBK
;
Control file created.
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '+GROUP1/xunyiqq/system01.dbf'
接下做介质恢复
SQL> recover database;
Media recovery complete.
BANPING：注意这种情况下启动的数据库会做实例恢复，因为redo和数据文件等都是copy过来的。这样就建立了一个db_name为bkeep，而instance_name和SID为xunyiqq的数据库SQL> alter database open;
Database altered.
查看数据库中几个有用的name
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------
db_name string bkeep
SQL> show parameter instance_name;
NAME TYPE VALUE
------------------ ---------- ------------------
instance_name string xunyiqq
SQL> select count(*) from b;
COUNT(*)
----------
7
第七步，为新数据库添加临时表空间
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
ERP
TEMPTBS1
SQL> alter tablespace temptbs1 add tempfile '+GROUP1/XUNYIQQ/temp01.dbf'
2 size 100M
3 reuse
4 autoextend on;
Tablespace altered.
第八步，更改新数据库的db_unique_name BANPING: db_unique_name 的重要作用
由于现在在同一个主机上有两个相同db_name的数据库，那么如何能同时启动呢，这需要指定参数db_unique_ name来区分开来，这个参数在standby环境也是必须设置的
SQL> alter system set db_unique_name=xunyiqq scope=spfile;
alter system set db_unique_name=xunyiqq scope=spfile
*
ERROR at line 1: <==因为我们刚才启动新数据库时使用的是pfile，所以接下来创建个spfile
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
第九步，创建spfile
SQL> create spfile from pfile; <==这个命令重启后生效
File created.
SQL> alter system set db_unique_name=xunyiqq scope=spfile;
alter system set db_unique_name=xunyiqq scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
SQL> shutdown immediate;
SQL> startup
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/oracle/product/10.2.0/db_1/dbs/spfilexunyiqq.ora
SQL> alter system set db_unique_name=xunyiqq scope=spfile;
System altered.
接下来启动bkeep数据库。正常！！
[oracle@kk ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 18 10:52:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
第十步，为新库添加undo段 下来看看xunyiqq的undo段
SQL> select ts.name ts_name,d.name d_name <==这个sql比较长，是我写的！
2 from v$tablespace ts,v$datafile d
3 where ts.ts#=d.ts#;
TS_NAME D_NAME
------------------ -------------------------------------------
SYSTEM +GROUP1/xunyiqq/system01.dbf
UNDOTBS1 +GROUP1/xunyiqq/undotbs01.dbf
SYSAUX +GROUP1/xunyiqq/sysaux01.dbf
ERP +GROUP1/xunyiqq/erp01.dbf
SQL> show parameter undo;
NAME TYPE VALUE
----------------------- --------- ---------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> create undo tablespace undotbs2 datafile '+GROUP1/XUNYIQQ/undotbs02.dbf' size 100M
Tablespace created.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
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
第十一步，配置网络监听服务
SQL> !
[oracle@kk ~]$ cd /opt/oracle/product/10.2.0/db_1/network/admin/
[oracle@kk admin]$ vi tnsnames.ora
BKEEP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.254.99)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bkeep)
)
)
XUNYIQQ =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.254.99)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xunyiqq)
)
)
[oracle@kk admin]$ lsnrctl
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 18-MAR-2010 11:18:57
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
第十二步，配置/etc/oratab
[root@kk ~]# vi /etc/oratab
--SID-- -----ORACLE_HOME------ --Y/N--
+ASM:/opt/oracle/product/10.2.0/db_1:Y
xunyiqq:/opt/oracle/product/10.2.0/db_1:Y
bkeep:/opt/oracle/product/10.2.0/db_1:Y
这里不是说操作系统启动时会启动实例，而已dbstart和dbshut会使用该配置文件
[oracle@kk ~]$ locate dbstart
/opt/oracle/product/10.2.0/db_1/bin/dbstart
