分类:
2010-04-12 22:39:27
ORACLE 2010-03-22 16:44:48 阅读27 评论0 字号:大中小
参考文档:
克隆一个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.
[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
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
;
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
;
[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
ASMCMD> pwd
+group1/bkeep
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ON
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
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
ASMCMD> ls
BKEEP/
XUNYIQQ/
ASMCMD> cd xunyiqq
ASMCMD> ls
ASMCMD> mkdir DATAFILE
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. <==这里要多敲几次回车
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 ON
ASMCMD> mkdir PARAMETERFILE
ASMCMD> mkdir TEMPFILE
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ON
PARAMETERFILE/
TEMPFILE/
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.
ASMCMD> pwd
+group1/xunyiqq
ASMCMD> ls -l
Type Redund Striped Time Sys Name
N CONTROLFILE/
N DATAFILE/
N ON
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/ON
N redo02.log => +GROUP1/BKEEP/ON
N redo03.log => +GROUP1/BKEEP/ON
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
[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.
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
这是因为二者有相同的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
SQL> shutdown immediate;
SQL> 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...
BANPING: mount时会找控制文件,没有则会出现如上所示的错误,可以在nomount阶段来建立控制文件,先关闭源库,然后在目标库启动到nomount阶段来建立控制文件
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: da
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
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_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
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.
[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.
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_DA
(SERVER = DEDICATED)
(SERVICE_NAME = bkeep)
)
)
XUNYIQQ =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.254.99)(PORT = 1521))
(CONNECT_DA
(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
[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
[oracle@kk ~]$ locate dbstart
/opt/oracle/product/10.2.0/db_1/bin/dbstart