[原]一次使用duplicate创建测试数据库的过程
由于管理不善，测试机的环境和生产机的环境已经相差甚远了，于是一狠心重建测试机，重建的第一步，重建数据库，我采用rman的duplicate命令从生产数据库复制到测试数据库的方式，虽然过程不太顺利，还是有一定的参考性的。
1。找出数据库相关文件的位置
spfile/pfile
我用的是spfile，如果您用的是pfile，过程类似，这里就不熬了，先找到这个spfile，通常来说是 $ORACLE_HOME/dbs/spfile$ORACLE_SID 。
sys$ora8i@4.20 SQL> show parameter spfile NAME TYPE VALUE ----------------- ----------- ------------------------------------ spfile string /u01/app/oracle/dbs/spfileora8i.ora
数据文件、日志文件和控制文件
sys$ora8i@4.20 SQL> select name from v$datafile 2 union all 3 select name from v$controlfile 4 union all 5 select member from v$logfile; NAME -------------------------------------------- /u02/oradata/ora8i/system01.dbf /u02/oradata/ora8i/undotbs01.dbf /u02/oradata/ora8i/sysaux01.dbf /u02/oradata/ora8i/users01.dbf /u02/oradata/ora8i/E3.dbf /u02/oradata/ora8i/switch01.dbf /u02/oradata/ora8i/PSS.dbf /u02/oradata/ora8i/CTXSYS.dbf /u02/oradata/ora8i/e3_02.dbf /u02/oradata/ora8i/CMS_1.dbf /u02/oradata/ora8i/control01.ctl /u02/oradata/ora8i/control02.ctl /u02/oradata/ora8i/control03.ctl /u02/oradata/ora8i/redo01.log /u02/oradata/ora8i/redo02.log /u02/oradata/ora8i/redo03.log /u02/oradata/ora8i/redo04.log /u02/oradata/ora8i/redo05.log /u02/oradata/ora8i/redo06.log
从以上信息来看spfile放在/u01/app/oracle/dbs/ ，数据文件、重做日志文件和控制文件都放在/u02/oradata/ora8i/。
2。在测试机中建立对应的目录
在测试机中检查这些目录，确保这些目录都存在:
# 在测试机中确保这些目录都存在: [oracle@test-server ora8i]$ ls /u01/app/oracle/dbs/ hc_ora8i.dat initdw.ora init.ora lkORA8I old orapwora8i spfileora8i.ora [oracle@test-server ora8i]$ ls /u02/oradata/ora8i/ ls: /u02/oradata/ora8i/: 没有那个文件或目录 # 不存在的目录就创建它： [root@test-server ~]# cd / [root@test-server /]# mkdir u02 [root@test-server /]# chown oracle:dba /u02 [root@test-server /]# mkdir -p /u02/oradata/ora8i/ [root@test-server /]# chown -R oracle:dba /u02
3。清理好测试服务器
测试服务器装好的时候还有个数据，所以 /u01/app/oracle/dbs/ 中还有文件。
在测试机上关闭数据库，然后将相关的数据文件都备份好，由于这个不是重点，这里就不熬述了。
4。配置测试服务器
将spfile和密码文件copy到测试机上面，目录要对应好。
[oracle@localhost dbs]$ cd /u01/app/oracle/dbs [oracle@localhost dbs]$ scp ./spfileora8i.ora ./orapwora8i 10.168.4.74:`pwd` oracle@10.168.4.74's password: spfileora8i.ora 100% 3584 3.5KB/s 00:00 orapwora8i 100% 1536 1.5KB/s 00:00
在测试机中可以这样检测这两个文件是否可用：
检查spfile：将数据库启动到nomount;
由于测试机和生产机的存在硬件上的差异（主要是内容）要将SGA调小一点[跳过]：
SQL> show parameter sga; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 1600M sga_target big integer 1600M SQL> alter system set sga_target=400M; alter system set sga_target=400M * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00827: could not shrink sga_target to specified value SQL> alter system set sga_target=400M scope=spfile; System altered. SQL> alter system set sga_max_size=500M scope=spfile; System altered. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup nomount; ORA-00821: Specified value of sga_target 400M is too small, needs to be at least 788M -- 没办法，创建pfile,改吧 SQL> create pfile from spfile; [oracle@test-server dbs]$ cd /u01/app/oracle/dbs [oracle@test-server dbs]$ vim initora8i.ora -- 将开头那几行ASMM动态修改的参数去掉， -- 顺便把 pga_aggregate_target，db_cache_size，shared_pool_size 也改小， -- 总之就是改到能启动Oracle 就可以了。 SQL> startup nomount pfile='/u01/app/oracle/dbs/initora8i.ora' ORACLE instance started. Total System Global Area 524288000 bytes Fixed Size 2072472 bytes Variable Size 205521000 bytes Database Buffers 310378496 bytes Redo Buffers 6316032 bytes SQL> shutdown immediate ; ORA-01507: database not mounted ORACLE instance shut down. -- 参数没有问题后，可以创建spfile，以后启动的时候可以使用spfile了 SQL> create spfile from pfile; File created. SQL> startup ; ORACLE instance started. Total System Global Area 524288000 bytes Fixed Size 2072472 bytes Variable Size 205521000 bytes Database Buffers 310378496 bytes Redo Buffers 6316032 bytes ORA-00205: error in identifying control file, check alert log for more info -- 这个错误是肯定了，spfile只能把oracle带到nomount阶段。
检查密码文件: 先确定listener启动了，然后再用sys用户远程登录到测试机。
[oracle@test-server admin]$ sqlplus sys/oracle@ora8i_4_74 as sysdba SQL*Plus: Release 10.2.0.2.0 - Production on Mon May 10 14:01:47 2010 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. Connected to an idle instance. SQL>
如果上述两部有问题的话请认真调试。
5。准备数据
在生产机上面做一个全备，由于机器比较空闲，压一压，要依据实际情况写脚本哦。
RMAN> run
2> {
3> allocate channel c1 device type disk ;
4> backup
5> as compressed backupset
6> tag 'build_test_db'
7> database
8> INCLUDE CURRENT CONTROLFILE
9> format '/u01/app/backup/backup_4.20_%I_%T_%s' ;
10> release channel c1 ;
11> }
....
....
....
rman> list backup ;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
856 Full 824.35M DISK 00:06:14 2010-05-10 14:24:34
BP Key: 856 Status: AVAILABLE Compressed: YES Tag: BUILD_TEST_DB
Piece Name: /u01/app/backup/backup_4.20_57919146_20100510_888 *******************
List of Datafiles in backup set 856
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 121784138 2010-05-10 14:18:20 /u02/oradata/ora8i/system01.dbf
2 Full 121784138 2010-05-10 14:18:20 /u02/oradata/ora8i/undotbs01.dbf
3 Full 121784138 2010-05-10 14:18:20 /u02/oradata/ora8i/sysaux01.dbf
4 Full 121784138 2010-05-10 14:18:20 /u02/oradata/ora8i/users01.dbf
5 Full 121784138 2010-05-10 14:18:20 /u02/oradata/ora8i/E3.dbf
7 Full 121784138 2010-05-10 14:18:20 /u02/oradata/ora8i/switch01.dbf
8 Full 121784138 2010-05-10 14:18:20 /u02/oradata/ora8i/PSS.dbf
11 Full 121784138 2010-05-10 14:18:20 /u02/oradata/ora8i/CTXSYS.dbf
13 Full 121784138 2010-05-10 14:18:20 /u02/oradata/ora8i/e3_02.dbf
16 Full 121784138 2010-05-10 14:18:20 /u02/oradata/ora8i/CMS_1.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
857 Full 1.13M DISK 00:00:02 2010-05-10 14:24:37
BP Key: 857 Status: AVAILABLE Compressed: YES Tag: BUILD_TEST_DB
Piece Name: /u01/app/backup/backup_4.20_57919146_20100510_889 *******************
Control File Included: Ckp SCN: 121785222 Ckp time: 2010-05-10 14:24:35
SPFILE Included: Modification time: 2010-05-08 00:11:48
将这两个新生成的备份集的文件到copy到测试机对应的目录中，当然做之前要检查目录是否存在：
[oracle@localhost backup]$ cd /u01/app/backup/ [oracle@localhost backup]$ scp /u01/app/backup/backup_4.20_57919146_20100510_888 10.168.4.74:`pwd` oracle@10.168.4.74's password: backup_4.20_57919146_20100510_888 100% 824MB 14.7MB/s 00:56 [oracle@localhost backup]$ scp /u01/app/backup/backup_4.20_57919146_20100510_889 10.168.4.74:`pwd` oracle@10.168.4.74's password: backup_4.20_57919146_20100510_889 100% 1168KB 1.1MB/s 00:00
6。开始duplicate
确定一下测试机（在rman的角度来说是auxiliary）处于nomount 阶段，生产机（在rman的角度来说是target）处于mount或open阶段。在生产机的rman中执行：
[oracle@localhost backup]$ rman target / auxiliary sys/oracle@ora8i_4_74 关键是这两句，最后确定一下两个数据的状态： connected to target database: ORA8I (DBID=57919146) connected to auxiliary database: ORA8I (not mounted) RMAN> DUPLICATE TARGET DATABASE TO ORA8I NOFILENAMECHECK;
我建议，此时在生产机上作一次日志切换，并将从全备开始时到刚生成的所有日志文件复制到测试服务器相对应的目录中，否则有可能出现以下问题[跳过]
channel ORA_AUX_DISK_1: starting datafile backupset restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set ..... ..... channel ORA_AUX_DISK_1: reading from backup piece /u01/app/backup/backup_4.20_57919146_20100510_886 ORA-19870: error reading backup piece /u01/app/backup/backup_4.20_57919146_20100510_886 ORA-19505: failed to identify file "/u01/app/backup/backup_4.20_57919146_20100510_886" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 failover to previous backup channel ORA_AUX_DISK_1: starting datafile backupset restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set ..... ..... channel ORA_AUX_DISK_1: reading from backup piece /u01/app/backup/backup_4.20_57919146_20100509_884 ORA-19870: error reading backup piece /u01/app/backup/backup_4.20_57919146_20100509_884 ORA-19505: failed to identify file "/u01/app/backup/backup_4.20_57919146_20100509_884" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 failover to previous backup RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 05/10/2010 14:47:54 RMAN-03015: error occurred in stored script Memory Script RMAN-06026: some targets not found - aborting restore
原因是不能在测试机（auxiliary）找不到备份集，仔细看一下rman是从“最近”的备份集开始找的，但就是跳过了最最新的，这是由于在归档模式下的备份是非一致性备份，还需要redo的信息才能使之变成一致性的，而这些信息暂时还在online redo log中，简单来说就是还没有出生（归档），我们可以切换一下日志文件，使之生成归档日志文件，并传到测试服务器上，对应的目录下面。
将刚才duplicate在测试机上生成的文件统统删掉，再次duplicate
RMAN> DUPLICATE TARGET DATABASE TO ORA8I NOFILENAMECHECK;
........
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
rman停在这里了。此时可以从测试机的alert文件中找到这么一句话：
Active process 6260 user 'oracle' program 'oracle@test-server (TNS V1-V3)' SHUTDOWN: waiting for logins to complete.
现在只要登录到数据库中就大功告成了。
