Chinaunix首页 | 论坛 | 博客
  • 博客访问: 666083
  • 博文数量: 168
  • 博客积分: 2928
  • 博客等级: 中校
  • 技术积分: 1904
  • 用 户 组: 普通用户
  • 注册时间: 2010-01-04 09:56
文章分类

全部博文(168)

文章存档

2010年(168)

我的朋友

分类:

2010-04-12 22:39:27

【oracle】克隆oracle 10G 数据库

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.

注意修改里面的路径。banpingdb_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_nameoracle通过一个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 xunyiqq

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...

错误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_namebkeep,而instance_nameSIDxunyiqq的数据库

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

下来看看xunyiqqundo

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

 

这里不是说操作系统启动时会启动实例,而已dbstartdbshut会使用该配置文件

[oracle@kk ~]$ locate dbstart

/opt/oracle/product/10.2.0/db_1/bin/dbstart

阅读(793) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~