Oracle_数据库克隆技术

数据库克隆

主机环境：RedHat 5.8 + Oracle 10g(10.2.0.5)

Oracle数据库：oracle10.2.0.5

主库库名：prod

克隆库库名： orcl

目标：通过数据库的克隆技术, 把原数据库克隆成一个新的数据库

1、主库prod ，用rman进行数据库的全备

SQL> show parameter name;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert string

db_name string prod

db_unique_name string prod

global_names boolean FALSE

instance_name string prod

lock_name_space string

log_file_name_convert string

service_names string prod

查看当前数据库的scn号

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

990619

备份主库prod

RMAN> run {

allocate channel c0 type disk ;

allocate channel c1 type disk ;

backup full database format '/u01/backup/prod/full_%t.bak' ;

backup current controlfile format ‘/u01/backup/prod/ctl_%U_%t.bak'

sql 'alter system archive log current' ;

backup archivelog all format '/u01/backup/prod/arch_%U_%t.bak';

release channel c0;

release channel c1;

}

2、为克隆库创建pfile 文件

SQL> create pfile from spfile;

File created.

3、为克隆库创建所需的目录

[oracle@ora10g dbs]$ mkdir -p $ORACLE_BASE/admin/orcl/bdump

[oracle@ora10g dbs]$ mkdir -p $ORACLE_BASE/admin/orcl/cdump

[oracle@ora10g dbs]$ mkdir -p $ORACLE_BASE/admin/orcl/udump

[oracle@ora10g dbs]$ mkdir -p $ORACLE_BASE/oradata/orcl

4、将主库pfile 拷贝生成clone 库的pfile，并且生成clone库的口令文件

[oracle@ora10g dbs]$ pwd

/u01/app/oracle/product/10.2.0/db_1/dbs

[oracle@ora10g dbs]$ orapwd file=orapworcl.ora password=oracle entries=3

[oracle@ora10g dbs]$ cp initprod.ora initorcl.ora

[oracle@ora10g dbs]$ vi initorcl.ora

修改db_name

将prod 替换为 orcl，并增加以下内容：

db_file_name_convert= ("/u01/app/oracle/oradata/prod" ,"/u01/app/oracle/oradata/orcl")

log_file_name_convert= ("/u01/app/oracle/oradata/prod" ,"/u01/app/oracle/oradata/orcl")

以下为克隆库的初始化参数文件initorcl.ora

[oracle@ora10g dbs]$ more initorcl.ora

orcl.__db_cache_size=163577856

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=109051904

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/or

cl/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.log_archive_dest='/u01/arch'

*.log_archive_format='%t_%s_%r.arch'

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=283115520

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

db_file_name_convert=('/u01/app/oracle/oradata/prod','/u01/app/oracle/oradata/orcl')

log_file_name_convert=('/u01/app/oracle/oradata/prod','/u01/app/oracle/oradata/orcl')

5、将clone 库启动到nomount

[oracle@ora10g dbs]$ export ORACLE_SID=orcl

[oracle@ora10g dbs]$ sqlplus “/as sysdba”

sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 23 09:11:22 2012

Connected to an idle instance.

09:11:22 SQL> startup pfile=$ORACLE_HOME/dbs/initorcl.ora nomount;

ORACLE instance started.

Total System Global Area 314572800 bytes

Fixed Size 1219160 bytes

Variable Size 113247656 bytes

Database Buffers 197132288 bytes

Redo Buffers 2973696 bytes

6、对主库的日志进行归档，并将归档日志拷贝到克隆库的归档目录下

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------

1 1 14 10485760 2 YES INACTIVE 1134075 2012-03-23 05:23:36

2 1 15 10485760 2 NO CURRENT 1141263 2012-03-23 08:57:57

3 1 12 10485760 2 YES INACTIVE 1123738 2012-03-23 02:54:16

4 1 13 10485760 2 YES INACTIVE 1126370 2012-03-23 03:23:17

09:55:18 SQL> alter system archive log current;

System altered.

09:57:35 SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------

1 1 14 10485760 2 YES INACTIVE 1134075 2012-03-23 05:23:36

2 1 15 10485760 2 YES ACTIVE 1141263 2012-03-23 08:57:57

3 1 16 10485760 2 NO CURRENT 1143553 2012-03-23 09:57:35

4 1 13 10485760 2 YES INACTIVE 1126370 2012-03-23 03:23:17

7、建立clone库的归档目录，并将主库的归档日志拷贝到clone 库下

[oracle@ora10g dbs]$ mkdir /u01/arch/

[oracle@ora10g dbs]$ cd /u01/arch/

[oracle@ora10g prod]$ ls

arch_1_10_778577586.log arch_1_13_778577586.log arch_1_5_778577586.log arch_1_8_778577586.log

arch_1_11_778577586.log arch_1_14_778577586.log arch_1_6_778577586.log arch_1_9_778577586.log

arch_1_12_778577586.log arch_1_15_778577586.log arch_1_7_778577586.log

[oracle@ora10g prod]$ cp *.* ../orcl/

8、用rman连接主库和克隆库

[oracle@ora10g admin]$ export ORACLE_SID=orcl

[oracle@ora10g admin]$ rman target sys/oracle@prod auxiliary sys/oracle

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Mar 23 10:03:55 2012

connected to target database: PROD (DBID=1408485026)

connected to auxiliary database: ORCL (not mounted)

RMAN> list backup;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

67 Full 344.21M DISK 00:01:44 23-MAR-12

BP Key: 67 Status: AVAILABLE Compressed: NO Tag: TAG20120323T085547

Piece Name: /disk2/rman/prod/hot_bak/PROD_75_778668947.bak

List of Datafiles in backup set 67

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

1 Full 1141202 23-MAR-12 /u01/app/oracle/oradata/prod/system01.dbf

2 Full 1141202 23-MAR-12 /u01/app/oracle/oradata/prod/rtbs01.dbf

5 Full 1141202 23-MAR-12 /u01/app/oracle/oradata/prod/lxtb1.dbf

8 Full 1141202 23-MAR-12 /u01/app/oracle/oradata/prod/undotbs1.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

68 Full 122.17M DISK 00:02:03 23-MAR-12

BP Key: 68 Status: AVAILABLE Compressed: NO Tag: TAG20120323T085547

Piece Name: /disk2/rman/prod/hot_bak/PROD_74_778668947.bak

List of Datafiles in backup set 68

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

3 Full 1141201 23-MAR-12 /u01/app/oracle/oradata/prod/sysaux01.dbf

4 Full 1141201 23-MAR-12 /u01/app/oracle/oradata/prod/users01.dbf

6 Full 1141201 23-MAR-12 /u01/app/oracle/oradata/prod/lxtbs2.dbf

11 Full 1141201 23-MAR-12 /u01/app/oracle/oradata/prod/tbsbig.dbf

12 Full 1141201 23-MAR-12 /u01/app/oracle/oradata/prod/indx01.dbf

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

69 29.16M DISK 00:00:03 23-MAR-12

BP Key: 69 Status: AVAILABLE Compressed: NO Tag: TAG20120323T085758

Piece Name: /disk2/rman/prod/hot_bak/arch_76.bak

List of Archived Logs in backup set 69

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 10 1100972 22-MAR-12 1121178 23-MAR-12

1 11 1121178 23-MAR-12 1123738 23-MAR-12

1 12 1123738 23-MAR-12 1126370 23-MAR-12

1 13 1126370 23-MAR-12 1134075 23-MAR-12

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

70 7.91M DISK 00:00:04 23-MAR-12

BP Key: 70 Status: AVAILABLE Compressed: NO Tag: TAG20120323T085758

Piece Name: /disk2/rman/prod/hot_bak/arch_77.bak

List of Archived Logs in backup set 70

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 14 1134075 23-MAR-12 1141263 23-MAR-12

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

71 5.83M DISK 00:00:01 23-MAR-12

BP Key: 71 Status: AVAILABLE Compressed: NO Tag: TAG20120323T085758

Piece Name: /disk2/rman/prod/hot_bak/arch_78.bak

List of Archived Logs in backup set 71

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 5 1095102 22-MAR-12 1100449 22-MAR-12

1 6 1100449 22-MAR-12 1100451 22-MAR-12

1 7 1100451 22-MAR-12 1100936 22-MAR-12

1 8 1100936 22-MAR-12 1100938 22-MAR-12

1 9 1100938 22-MAR-12 1100972 22-MAR-12

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

72 Full 7.14M DISK 00:00:01 23-MAR-12

BP Key: 72 Status: AVAILABLE Compressed: NO Tag: TAG20120323T085810

Piece Name: /disk2/rman/prod/hot_bak/ctl_79.bak

Control File Included: Ckp SCN: 1141277 Ckp time: 23-MAR-12

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

73 Full 7.17M DISK 00:00:02 23-MAR-12

BP Key: 73 Status: AVAILABLE Compressed: NO Tag: TAG20120323T085812

Piece Name: /disk1/flash/PROD/autobackup/2012_03_23/o1_mf_s_778669092_7pqlo5fo_.bkp

Control File Included: Ckp SCN: 1141281 Ckp time: 23-MAR-12

SPFILE Included: Modification time: 23-MAR-12

9、用rman 克隆库

RMAN> duplicate target database to orcl;

Starting Duplicate Db at 19-FEB-14

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:

{

set until scn 561676;

set newname for datafile 1 to

"/u01/app/oracle/oradata/orcl/system01.dbf";

set newname for datafile 2 to

"/u01/app/oracle/oradata/orcl/undotbs01.dbf";

set newname for datafile 3 to

"/u01/app/oracle/oradata/orcl/sysaux01.dbf";

set newname for datafile 4 to

"/u01/app/oracle/oradata/orcl/users01.dbf";

set newname for datafile 5 to

"/u01/app/oracle/oradata/orcl/example01.dbf";

restore

check readonly

clone database

;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 19-FEB-14

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00002 to /u01/app/oracle/oradata/orcl/undotbs01.dbf

restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/prod/full_06p115d7_1_1_839947687.bak

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u01/backup/prod/full_06p115d7_1_1_839947687.bak tag=DBFULL

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf

restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/prod/full_05p115d7_1_1_839947687.bak

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u01/backup/prod/full_05p115d7_1_1_839947687.bak tag=DBFULL

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/prod/full_04p115d7_1_1_839947687.bak

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u01/backup/prod/full_04p115d7_1_1_839947687.bak tag=DBFULL

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 19-FEB-14

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ( '/u01/app/oracle/oradata/orcl/redo01.log' ) SIZE 50 M REUSE,

GROUP 2 ( '/u01/app/oracle/oradata/orcl/redo02.log' ) SIZE 50 M REUSE,

GROUP 3 ( '/u01/app/oracle/oradata/orcl/redo03.log' ) SIZE 50 M REUSE

DATAFILE

'/u01/app/oracle/oradata/orcl/system01.dbf'

CHARACTER SET WE8ISO8859P1

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

released channel: ORA_AUX_DISK_1

datafile 2 switched to datafile copy

input datafile copy recid=1 stamp=839948824 filename=/u01/app/oracle/oradata/orcl/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=2 stamp=839948824 filename=/u01/app/oracle/oradata/orcl/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=3 stamp=839948825 filename=/u01/app/oracle/oradata/orcl/users01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=4 stamp=839948825 filename=/u01/app/oracle/oradata/orcl/example01.dbf

contents of Memory Script:

{

set until scn 561676;

recover

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 19-FEB-14

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=156 devtype=DISK

starting media recovery

archive log thread 1 sequence 2 is already on disk as file /u01/arch/1_2_836942536.arch

archive log thread 1 sequence 3 is already on disk as file /u01/arch/1_3_836942536.arch

archive log filename=/u01/arch/1_2_836942536.arch thread=1 sequence=2

archive log filename=/u01/arch/1_3_836942536.arch thread=1 sequence=3

media recovery complete, elapsed time: 00:00:05

Finished recover at 19-FEB-14

contents of Memory Script:

{

shutdown clone;

startup clone nomount ; (克隆库 密码文件有误

没有添加 entries=3，造成和主库的密码文件不一致从而造成主库不可以远程登录克隆库) 就是这个问题！！！

}

executing Memory Script

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 285212672 bytes

Fixed Size 2020224 bytes

Variable Size 117443712 bytes

Database Buffers 163577856 bytes

Redo Buffers 2170880 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ( '/u01/app/oracle/oradata/orcl/redo01.log' ) SIZE 50 M REUSE,

GROUP 2 ( '/u01/app/oracle/oradata/orcl/redo02.log' ) SIZE 50 M REUSE,

GROUP 3 ( '/u01/app/oracle/oradata/orcl/redo03.log' ) SIZE 50 M REUSE

DATAFILE

'/u01/app/oracle/oradata/orcl/system01.dbf'

CHARACTER SET WE8ISO8859P1

contents of Memory Script:

{

set newname for tempfile 1 to

"/u01/app/oracle/oradata/orcl/temp01.dbf";

switch clone tempfile all;

catalog clone datafilecopy "/u01/app/oracle/oradata/orcl/undotbs01.dbf";

catalog clone datafilecopy "/u01/app/oracle/oradata/orcl/sysaux01.dbf";

catalog clone datafilecopy "/u01/app/oracle/oradata/orcl/users01.dbf";

catalog clone datafilecopy "/u01/app/oracle/oradata/orcl/example01.dbf";

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/orcl/temp01.dbf in control file

cataloged datafile copy

datafile copy filename=/u01/app/oracle/oradata/orcl/undotbs01.dbf recid=1 stamp=839948859

cataloged datafile copy

datafile copy filename=/u01/app/oracle/oradata/orcl/sysaux01.dbf recid=2 stamp=839948860

cataloged datafile copy

datafile copy filename=/u01/app/oracle/oradata/orcl/users01.dbf recid=3 stamp=839948860

cataloged datafile copy

datafile copy filename=/u01/app/oracle/oradata/orcl/example01.dbf recid=4 stamp=839948860

datafile 2 switched to datafile copy

input datafile copy recid=1 stamp=839948859 filename=/u01/app/oracle/oradata/orcl/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=2 stamp=839948860 filename=/u01/app/oracle/oradata/orcl/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=3 stamp=839948860 filename=/u01/app/oracle/oradata/orcl/users01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=4 stamp=839948860 filename=/u01/app/oracle/oradata/orcl/example01.dbf

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 19-FEB-14

RMAN> exit

10、克隆成功，打开clone库，查看

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> select name from v$datafile;

NAME

------------------------------------------------------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/system01.dbf

/u01/app/oracle/oradata/orcl/rtbs01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/users01.dbf

/u01/app/oracle/oradata/orcl/lxtb1.dbf

/u01/app/oracle/oradata/orcl/lxtbs2.dbf

/u01/app/oracle/oradata/orcl/undotbs1.dbf

/u01/app/oracle/oradata/orcl/tbsbig.dbf

/u01/app/oracle/oradata/orcl/indx01.dbf

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------

1 1 0 10485760 2 YES UNUSED 0

2 1 0 10485760 2 YES UNUSED 0

3 1 0 10485760 2 YES UNUSED 0

4 1 1 10485760 2 NO CURRENT 1143554 2012-03-23 10:12:30

10:14:03 SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------

1 1 2 10485760 2 YES INACTIVE 1144304 2012-03-23 10:13:56

2 1 3 10485760 2 YES INACTIVE 1144306 2012-03-23 10:13:57

3 1 4 10485760 2 YES INACTIVE 1144308 2012-03-23 10:13:58

4 1 5 10485760 2 NO CURRENT 1144311 2012-03-23 10:14:03

ꄴ前一个：无

ꄲ后一个：无

Oracle_数据库克隆技术

全部评论 (0)