一次使用duplicate创建测试数据库的过程

由于管理不善，测试机的环境和生产机的环境已经相差甚远了，于是一狠心重建测试机，重建的第一步，重建数据库，我采用rman的duplicate命令从生产数据库复制到测试数据库的方式，虽然过程不太顺利，还是有一定的参考性的。

 

飞鸽传书：http://www.freeeim.com/

1。找出数据库相关文件的位置

spfile/pfile

我用的是spfile，如果您用的是pfile，过程类似，这里就不熬了，先找到这个spfile，通常来说是 $ORACLE_HOME/dbs/spfile$ORACLE_SID 。

view source

print?

1	sys$ora8i@4.20 SQL> show parameter spfile

2

3	NAME              TYPE        VALUE

4	----------------- ----------- ------------------------------------

5	spfile            string      /u01/app/oracle/dbs/spfileora8i.ora

数据文件、日志文件和控制文件

view source

print?

01	sys$ora8i@4.20 SQL> select name from v$datafile

02	2  union all

03	3  select name from v$controlfile

04	4  union all

05	5  select member from v$logfile;

06

07

NAME

08	--------------------------------------------

09	/u02/oradata/ora8i/system01.dbf

10	/u02/oradata/ora8i/undotbs01.dbf

11	/u02/oradata/ora8i/sysaux01.dbf

12	/u02/oradata/ora8i/users01.dbf

13	/u02/oradata/ora8i/E3.dbf

14	/u02/oradata/ora8i/switch01.dbf

15	/u02/oradata/ora8i/PSS.dbf

16	/u02/oradata/ora8i/CTXSYS.dbf

17	/u02/oradata/ora8i/e3_02.dbf

18	/u02/oradata/ora8i/CMS_1.dbf

19	/u02/oradata/ora8i/control01.ctl

20	/u02/oradata/ora8i/control02.ctl

21	/u02/oradata/ora8i/control03.ctl

22	/u02/oradata/ora8i/redo01.log

23	/u02/oradata/ora8i/redo02.log

24	/u02/oradata/ora8i/redo03.log

25	/u02/oradata/ora8i/redo04.log

26	/u02/oradata/ora8i/redo05.log

27	/u02/oradata/ora8i/redo06.log

从以上信息来看spfile放在/u01/app/oracle/dbs/ ，数据文件、重做日志文件和控制文件都放在/u02/oradata/ora8i/。

2。在测试机中建立对应的目录

在测试机中检查这些目录，确保这些目录都存在:

view source

print?

01	# 在测试机中确保这些目录都存在:

02	[oracle@test-server ora8i]$ ls  /u01/app/oracle/dbs/

03	hc_ora8i.dat  initdw.ora  init.ora  lkORA8I  old  orapwora8i  spfileora8i.ora

04	[oracle@test-server ora8i]$ ls /u02/oradata/ora8i/

05	ls: /u02/oradata/ora8i/: 没有那个文件或目录

06	# 不存在的目录就创建它：

07	[root@test-server ~]# cd /

08	[root@test-server /]# mkdir u02

09	[root@test-server /]# chown oracle:dba /u02

10	[root@test-server /]# mkdir -p /u02/oradata/ora8i/

11	[root@test-server /]# chown -R oracle:dba /u02

3。清理好测试服务器

测试服务器装好的时候还有个数据，所以 /u01/app/oracle/dbs/ 中还有文件。
在测试机上关闭数据库，然后将相关的数据文件都备份好，由于这个不是重点，这里就不熬述了。

4。配置测试服务器

将spfile和密码文件copy到测试机上面，目录要对应好。

view source

print?

1	[oracle@localhost dbs]$ cd /u01/app/oracle/dbs

2	[oracle@localhost dbs]$ scp ./spfileora8i.ora ./orapwora8i  10.168.4.74:`pwd`

3	oracle@10.168.4.74's password:

4	spfileora8i.ora                                                        100% 3584     3.5KB/s   00:00

5	orapwora8i                                                             100% 1536     1.5KB/s   00:00

在测试机中可以这样检测这两个文件是否可用：

检查spfile：将数据库启动到nomount;

由于测试机和生产机的存在硬件上的差异（主要是内容）要将SGA调小一点[跳过]：

view source

print?

01	SQL> show parameter sga;

02

03	NAME                                 TYPE                   VALUE

04	------------------------------------ ---------------------- ------------------------------

05	lock_sga                             boolean                FALSE

06	pre_page_sga                         boolean                FALSE

07	sga_max_size                         big integer            1600M

08	sga_target                           big integer            1600M

09	SQL> alter system set sga_target=400M;

10

11	alter system set sga_target=400M

12

*

13	ERROR at line 1:

14	ORA-02097: parameter cannot be modified because specified value is invalid

15	ORA-00827: could not shrink sga_target to specified value

16

17	SQL> alter system set sga_target=400M scope=spfile;

18

19	System altered.

20

21	SQL> alter system set sga_max_size=500M scope=spfile;

22

23	System altered.

24

25	SQL> shutdown immediate;

26	ORA-01507: database not mounted

27

28	ORACLE instance shut down.

29	SQL> startup nomount;

30	ORA-00821: Specified value of sga_target 400M is too small, needs to be at least 788M

31

32	-- 没办法，创建pfile,改吧

33	SQL> create pfile from spfile;

34	[oracle@test-server dbs]$ cd /u01/app/oracle/dbs

35	[oracle@test-server dbs]$ vim initora8i.ora

36	-- 将开头那几行ASMM动态修改的参数去掉，

37	-- 顺便把 pga_aggregate_target，db_cache_size，shared_pool_size 也改小，

38	-- 总之就是改到能启动Oracle 就可以了。

39

40	SQL> startup nomount pfile='/u01/app/oracle/dbs/initora8i.ora'

41	ORACLE instance started.

42

43	Total System Global Area  524288000 bytes

44	Fixed Size                  2072472 bytes

45	Variable Size             205521000 bytes

46	Database Buffers          310378496 bytes

47	Redo Buffers                6316032 bytes

48	SQL> shutdown immediate ;

49	ORA-01507: database not mounted

50

51

52	ORACLE instance shut down.

53

54	-- 参数没有问题后，可以创建spfile，以后启动的时候可以使用spfile了

55	SQL> create spfile from pfile;

56

57	File created.

58

59	SQL> startup ;

60	ORACLE instance started.

61

62	Total System Global Area  524288000 bytes

63	Fixed Size                  2072472 bytes

64	Variable Size             205521000 bytes

65	Database Buffers          310378496 bytes

66	Redo Buffers                6316032 bytes

67	ORA-00205: error in identifying control file, check alert log for more info

68	-- 这个错误是肯定了，spfile只能把oracle带到nomount阶段。

 

检查密码文件: 先确定listener启动了，然后再用sys用户远程登录到测试机。

view source

print?

1	[oracle@test-server admin]$ sqlplus sys/oracle@ora8i_4_74  as sysdba

2	SQL*Plus: Release 10.2.0.2.0 - Production on Mon May 10 14:01:47 2010

3	Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

4

5	Connected to an idle instance.

6

7

SQL>

如果上述两部有问题的话请认真调试。

5。准备数据

在生产机上面做一个全备，由于机器比较空闲，压一压，要依据实际情况写脚本哦。

view source

print?

01

RMAN> run

02

2> {

03	3>   allocate channel c1 device type disk ;

04	4>   backup

05	5>   as compressed backupset

06	6>   tag 'build_test_db'

07	7>   database

08	8>   INCLUDE CURRENT CONTROLFILE

09	9>   format '/u01/app/backup/backup_4.20_%I_%T_%s' ;

10	10>   release channel c1 ;

11

11> }

12

....

13

....

14

....

15	rman> list backup ;

16	BS Key  Type LV Size       Device Type Elapsed Time Completion Time

17	------- ---- -- ---------- ----------- ------------ -------------------

18	856     Full    824.35M    DISK        00:06:14     2010-05-10 14:24:34

19	BP Key: 856   Status: AVAILABLE  Compressed: YES  Tag: BUILD_TEST_DB

20	Piece Name: /u01/app/backup/backup_4.20_57919146_20100510_888 *******************

21	List of Datafiles in backup set 856

22	File LV Type Ckp SCN    Ckp Time            Name

23	---- -- ---- ---------- ------------------- ----

24	1       Full 121784138  2010-05-10 14:18:20 /u02/oradata/ora8i/system01.dbf

25	2       Full 121784138  2010-05-10 14:18:20 /u02/oradata/ora8i/undotbs01.dbf

26	3       Full 121784138  2010-05-10 14:18:20 /u02/oradata/ora8i/sysaux01.dbf

27	4       Full 121784138  2010-05-10 14:18:20 /u02/oradata/ora8i/users01.dbf

28	5       Full 121784138  2010-05-10 14:18:20 /u02/oradata/ora8i/E3.dbf

29	7       Full 121784138  2010-05-10 14:18:20 /u02/oradata/ora8i/switch01.dbf

30	8       Full 121784138  2010-05-10 14:18:20 /u02/oradata/ora8i/PSS.dbf

31	11      Full 121784138  2010-05-10 14:18:20 /u02/oradata/ora8i/CTXSYS.dbf

32	13      Full 121784138  2010-05-10 14:18:20 /u02/oradata/ora8i/e3_02.dbf

33	16      Full 121784138  2010-05-10 14:18:20 /u02/oradata/ora8i/CMS_1.dbf

34

35	BS Key  Type LV Size       Device Type Elapsed Time Completion Time

36	------- ---- -- ---------- ----------- ------------ -------------------

37	857     Full    1.13M      DISK        00:00:02     2010-05-10 14:24:37

38	BP Key: 857   Status: AVAILABLE  Compressed: YES  Tag: BUILD_TEST_DB

39	Piece Name: /u01/app/backup/backup_4.20_57919146_20100510_889 *******************

40	Control File Included: Ckp SCN: 121785222    Ckp time: 2010-05-10 14:24:35

41	SPFILE Included: Modification time: 2010-05-08 00:11:48

将这两个新生成的备份集的文件到copy到测试机对应的目录中，当然做之前要检查目录是否存在：

view source

print?

1	[oracle@localhost backup]$ cd /u01/app/backup/

2	[oracle@localhost backup]$ scp /u01/app/backup/backup_4.20_57919146_20100510_888 10.168.4.74:`pwd`

3	oracle@10.168.4.74's password:

4	backup_4.20_57919146_20100510_888                                      100%  824MB  14.7MB/s   00:56

5	[oracle@localhost backup]$ scp /u01/app/backup/backup_4.20_57919146_20100510_889 10.168.4.74:`pwd`

6	oracle@10.168.4.74's password:

7	backup_4.20_57919146_20100510_889                                      100% 1168KB   1.1MB/s   00:00

6。开始duplicate

确定一下测试机（在rman的角度来说是auxiliary）处于nomount 阶段，生产机（在rman的角度来说是target）处于mount或open阶段。在生产机的rman中执行：

view source

print?

1	[oracle@localhost backup]$ rman target /  auxiliary sys/oracle@ora8i_4_74

2	关键是这两句，最后确定一下两个数据的状态：

3	connected to target database: ORA8I (DBID=57919146)

4	connected to auxiliary database: ORA8I (not mounted)

5

6	RMAN> DUPLICATE TARGET DATABASE TO ORA8I NOFILENAMECHECK;

我建议，此时在生产机上作一次日志切换，并将从全备开始时到刚生成的所有日志文件复制到测试服务器相对应的目录中，否则有可能出现以下问题[跳过]

view source

print?

01	channel ORA_AUX_DISK_1: starting datafile backupset restore

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

03

.....

04

.....

05	channel ORA_AUX_DISK_1: reading from backup piece /u01/app/backup/backup_4.20_57919146_20100510_886

06	ORA-19870: error reading backup piece /u01/app/backup/backup_4.20_57919146_20100510_886

07	ORA-19505: failed to identify file "/u01/app/backup/backup_4.20_57919146_20100510_886"

08	ORA-27037: unable to obtain file status

09	Linux-x86_64 Error: 2: No such file or directory

10	Additional information: 3

11	failover to previous backup

12

13	channel ORA_AUX_DISK_1: starting datafile backupset restore

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

15

.....

16

.....

17	channel ORA_AUX_DISK_1: reading from backup piece /u01/app/backup/backup_4.20_57919146_20100509_884

18	ORA-19870: error reading backup piece /u01/app/backup/backup_4.20_57919146_20100509_884

19	ORA-19505: failed to identify file "/u01/app/backup/backup_4.20_57919146_20100509_884"

20	ORA-27037: unable to obtain file status

21	Linux-x86_64 Error: 2: No such file or directory

22	Additional information: 3

23	failover to previous backup

24

25	RMAN-00571: ===========================================================

26	RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

27	RMAN-00571: ===========================================================

28	RMAN-03002: failure of Duplicate Db command at 05/10/2010 14:47:54

29	RMAN-03015: error occurred in stored script Memory Script

30	RMAN-06026: some targets not found - aborting restore

原因是不能在测试机（auxiliary）找不到备份集，仔细看一下rman是从“最近”的备份集开始找的，但就是跳过了最最新的，这是由于在归档模式下的备份是非一致性备份，还需要redo的信息才能使之变成一致性的，而这些信息暂时还在online redo log中，简单来说就是还没有出生（归档），我们可以切换一下日志文件，使之生成归档日志文件，并传到测试服务器上，对应的目录下面。

将刚才duplicate在测试机上生成的文件统统删掉，再次duplicate

view source

print?

1	RMAN> DUPLICATE TARGET DATABASE TO ORA8I NOFILENAMECHECK;

2

3

........

4	contents of Memory Script:

5

{

6	Alter clone database open resetlogs;

7

}

8	executing Memory Script

 

rman停在这里了。此时可以从测试机的alert文件中找到这么一句话：

view source

print?

1	Active process 6260 user 'oracle' program 'oracle@test-server (TNS V1-V3)'

2	SHUTDOWN: waiting for logins to complete.

现在只要登录到数据库中就大功告成了。