范本：使用duplicate重建数据库

最新推荐文章于 2023-11-17 16:06:30 发布
最新推荐文章于 2023-11-17 16:06:30 发布
阅读量202 收藏
点赞数
文章标签： 数据库

环境：

一台服务器，上有一个产品数据库wmpmpp1以及一个仿真数据库wmpmperf。需要按照wmpmpp1重建wmpmperf。

[@more@]

需求：

===================================
Request: duplicate database wmpmpp1 to wmpmperf.
target: wmpmpp1
duplicate: wmpmperf
auxiliary: wmpmperf
server: same
path: different
binary: same
backup directory: same (don't need copy rman backup files)
===================================

特别考虑：

由于$duplicate已经存在，所以需要先删除再重建。

1，需要删除的文件

（1）数据文件

（2）redo文件

（3）临时文件

（4）控制文件

2，不需要重装Oracle软件

3，保留所有目录结构以及参数文件和密码文件

4，由于磁盘空间不足以装下两个$duplicate，需要先删除，否则可以先重命名

5，由于在同一台服务器且文件路径不同，所以需要转换数据文件和redo文件的路径。目录结构都像这样：

179G 134G 43G 76% /ora-pim/ts5

ls /ora-pim/ts5
lost+found wmpimp1 wmpmperf wmpmpp1

所以不能简单的设定：*.db_file_name_convert=('wmpmpp1','wmpmperf')

否则可能会把某个ts撑爆。

而是需要用 set newname 单独设定每个文件的位置。

6，考虑是否要备份所有用户的密码！

使用的脚本：

1，使用rman备份并保证归档日志从备份的时间点到现在是完整的。(rman: /ora-pim/bkup1/wmpmpp1/dbfiles1, /ora-pim/bkup1/wmpmpp1/dbfiles2; arch: /ora-pim/archive/wmpmpp1/arch)

2，检查rman备份：

ORACLE_SID=wmpmpp1
rman target /
catalog start with '/ora-pim/bkup1/wmpmpp1/dbfiles1';
catalog start with '/ora-pim/bkup1/wmpmpp1/dbfiles2';
list backup;
report schema;
exit

3，准备各种文件和脚本

（1）从spfile创建pfile：

create pfile='/opt/oracle-pim/product/1020/dbs/initwmpmperf.ora' from spfile;

（2）修改pfile

通常需要注意：

shared_pool > 200M,
db_file_name_convert,
log_file_name_convert
db_name
path

由于此次是在$duplicate数据库原有的参数文件上修改，保留了所有目录结构，并且使用set newname，故可以忽略 db_file_name_convert, log_file_name_convert，db_name，path。 只要保证 shared_pool > 200M 就行了（运行duplicate的限制）。

vi /opt/oracle-pim/product/1020/dbs/initwmpmperf.ora

========================================initwmpmperf.ora========================================
wmpmperf.__db_cache_size=1174405120
wmpmperf.__java_pool_size=218103808
wmpmperf.__large_pool_size=16777216
wmpmperf.__shared_pool_size=1694498816
wmpmperf.__streams_pool_size=33554432
*.audit_file_dest='/opt/oracle-pim/admin/wmpmperf/audit'
*.audit_trail='db'
*.background_dump_dest='/opt/oracle-pim/admin/wmpmperf/bdump'
*.compatible='10.2.0'
*.control_files='/ora-pim/ts1/wmpmperf/ctl/control01.ctl','/ora-pim/ts2/wmpmperf/ctl/control02.ctl'#Restore Controlfile
*.core_dump_dest='/opt/oracle-pim/admin/wmpmperf/cdump'
*.db_block_size=8192
*.db_file_multiblock_read_count=32
*.db_files=500
*.db_name='wmpmperf'
*.dbwr_io_slaves=0
*.instance_name='wmpmperf'
*.java_pool_size=209715200
*.job_queue_processes=0
*.log_archive_dest_1='LOCATION=/ora-pim/archive/wmpmperf/arch'
*.log_archive_format='wmpmperf_%t_%r_%s.arc'
*.log_buffer=5242880
*.log_checkpoint_interval=0
*.log_checkpoint_timeout=0
*.max_dump_file_size='10240'# limit trace file size to 10 Meg each
*.open_cursors=600
*.optimizer_features_enable='10.2.0.4'
*.optimizer_index_caching=90
*.optimizer_index_cost_adj=50
*.os_authent_prefix=''
*.parallel_max_servers=0
*.pga_aggregate_target=1000m
*.processes=400
*.query_rewrite_enabled='TRUE'
*.query_rewrite_integrity='TRUSTED'
*.service_names='wmpmperf'
*.session_cached_cursors=50
*.sga_max_size=3000M
*.sga_target=3000M
*.shared_pool_size=838860800
*.skip_unusable_indexes=TRUE
*.sort_area_size=5242880
*.standby_archive_dest='/ora-pim/archive/wmpmperf/arch'
*.statistics_level='typical'
*.streams_pool_size=33554432
*.timed_statistics=true
*.undo_management='auto'
*.undo_retention=10800
*.undo_tablespace='undorbs'
*.user_dump_dest='/opt/oracle-pim/admin/wmpmperf/udump'
*.utl_file_dir='/opt/oracle-pim/admin/utl_files'
*.workarea_size_policy='auto'
========================================

（3）准备rman命令脚本

vi /opt/oracle-pim/worker/clone/clone_20091020/duplicate_wmpmperf.rcv

====================duplicate_wmpmperf.rcv========================
connect targetsys/D***@wmpmpp1
connect auxiliary /
run{
allocate channel c1 device type disk format '/ora-pim/bkup1/wmpmpp1/dbfiles1/%d_c1_INCR_0_s%s_p%p';
allocate channel c2 device type disk format '/ora-pim/bkup1/wmpmpp1/dbfiles2/%d_c2_INCR_0_s%s_p%p';

allocate auxiliary channel t1 type disk;
allocate auxiliary channel t2 type disk;

set newname for datafile '/ora-pim/ts1/wmpmpp1/system/system01.dbf' to
'/ora-pim/ts1/wmpmperf/system/system01.dbf';

set newname for datafile '/ora-pim/ts4/wmpmpp1/undo/undorbs01.dbf' to
'/ora-pim/ts4/wmpmperf/undo/undorbs01.dbf';

set newname for datafile '/ora-pim/ts2/wmpmpp1/data/sysaux01.dbf' to
'/ora-pim/ts2/wmpmperf/data/sysaux01.dbf';

set newname for datafile '/ora-pim/ts2/wmpmpp1/tools/tools01.dbf' to
'/ora-pim/ts2/wmpmperf/tools/tools01.dbf';

set newname for datafile '/ora-pim/ts4/wmpmpp1/data/users01.dbf' to
'/ora-pim/ts4/wmpmperf/data/users01.dbf';

set newname for datafile '/ora-pim/ts2/wmpmpp1/data/users02.dbf' to
'/ora-pim/ts2/wmpmperf/data/users02.dbf';

set newname for datafile '/ora-pim/ts1/wmpmpp1/data/users03.dbf' to
'/ora-pim/ts7/wmpmperf/data/users03.dbf';

set newname for datafile '/ora-pim/ts4/wmpmpp1/index/indx01.dbf' to
'/ora-pim/ts4/wmpmperf/index/indx01.dbf';

set newname for datafile '/ora-pim/ts3/wmpmpp1/index/indx02.dbf' to
'/ora-pim/ts3/wmpmperf/index/indx02.dbf';

set newname for datafile '/ora-pim/ts2/wmpmpp1/index/indx03.dbf' to
'/ora-pim/ts2/wmpmperf/index/indx03.dbf';

set newname for datafile '/ora-pim/ts1/wmpmpp1/index/indx04.dbf' to
'/ora-pim/ts1/wmpmperf/index/indx04.dbf';

set newname for datafile '/ora-pim/ts5/wmpmpp1/data/blob_tbl_data_01.dbf' to
'/ora-pim/ts5/wmpmperf/data/blob_tbl_data_01.dbf';

set newname for datafile '/ora-pim/ts4/wmpmpp1/undo/undorbs02.dbf' to
'/ora-pim/ts4/wmpmperf/undo/undorbs02.dbf';

set newname for datafile '/ora-pim/ts2/wmpmpp1/tools/tools02.dbf' to
'/ora-pim/ts2/wmpmperf/tools/tools02.dbf';

set newname for datafile '/ora-pim/ts4/wmpmpp1/data/users04.dbf' to
'/ora-pim/ts4/wmpmperf/data/users04.dbf';

set newname for datafile '/ora-pim/ts5/wmpmpp1/data/users05.dbf' to
'/ora-pim/ts5/wmpmperf/data/users05.dbf';

set newname for datafile '/ora-pim/ts1/wmpmpp1/data/users06.dbf' to
'/ora-pim/ts7/wmpmperf/data/users06.dbf';

set newname for datafile '/ora-pim/ts2/wmpmpp1/data/users07.dbf' to
'/ora-pim/ts2/wmpmperf/data/users07.dbf';

set newname for datafile '/ora-pim/ts5/wmpmpp1/index/indx05.dbf' to
'/ora-pim/ts9/wmpmperf/index/indx05.dbf';

set newname for datafile '/ora-pim/ts1/wmpmpp1/index/indx06.dbf' to
'/ora-pim/ts1/wmpmperf/index/indx06.dbf';

set newname for datafile '/ora-pim/ts2/wmpmpp1/index/indx07.dbf' to
'/ora-pim/ts2/wmpmperf/index/indx07.dbf';

set newname for datafile '/ora-pim/ts3/wmpmpp1/index/indx08.dbf' to
'/ora-pim/ts3/wmpmperf/index/indx08.dbf';

set newname for datafile '/ora-pim/ts2/wmpmpp1/tools/tools03.dbf' to
'/ora-pim/ts2/wmpmperf/tools/tools03.dbf';

set newname for datafile '/ora-pim/ts3/wmpmpp1/index/indx09.dbf' to
'/ora-pim/ts3/wmpmperf/index/indx09.dbf';

set newname for datafile '/ora-pim/ts5/wmpmpp1/data/sysaux.02.dbf' to
'/ora-pim/ts5/wmpmperf/data/sysaux.02.dbf';

set newname for datafile '/ora-pim/ts3/wmpmpp1/index/indx.10.dbf' to
'/ora-pim/ts3/wmpmperf/index/indx.10.dbf';

set newname for datafile '/ora-pim/ts1/wmpmpp1/index/indx.11.dbf' to
'/ora-pim/ts1/wmpmperf/index/indx.11.dbf';

set newname for datafile '/ora-pim/ts5/wmpmpp1/index/indx.12.dbf' to
'/ora-pim/ts9/wmpmperf/index/indx.12.dbf';

set newname for datafile '/ora-pim/ts3/wmpmpp1/index/indx.13.dbf' to
'/ora-pim/ts3/wmpmperf/index/indx.13.dbf';

set newname for datafile '/ora-pim/ts5/wmpmpp1/index/indx.14.dbf' to
'/ora-pim/ts9/wmpmperf/index/indx.14.dbf';

set newname for datafile '/ora-pim/ts4/wmpmpp1/index/indx.15.dbf' to
'/ora-pim/ts4/wmpmperf/index/indx.15.dbf';

set newname for datafile '/ora-pim/ts5/wmpmpp1/data/users08.dbf' to
'/ora-pim/ts5/wmpmperf/data/users08.dbf';

set newname for datafile '/ora-pim/ts3/wmpmpp1/data/users09.dbf' to
'/ora-pim/ts8/wmpmperf/data/users09.dbf';

set newname for datafile '/ora-pim/ts1/wmpmpp1/tools/tools04.dbf' to
'/ora-pim/ts1/wmpmperf/tools/tools04.dbf';

set newname for datafile '/ora-pim/ts3/wmpmpp1/data/blob_tbl_data.02.dbf' to
'/ora-pim/ts8/wmpmperf/data/blob_tbl_data.02.dbf';

set newname for datafile '/ora-pim/ts4/wmpmpp1/data/blob_tbl_data.03.dbf' to
'/ora-pim/ts4/wmpmperf/data/blob_tbl_data.03.dbf';

set newname for datafile '/ora-pim/ts5/wmpmpp1/data/users.10.dbf' to
'/ora-pim/ts5/wmpmperf/data/users.10.dbf';

set newname for datafile '/ora-pim/ts3/wmpmpp1/data/users.11.dbf' to
'/ora-pim/ts8/wmpmperf/data/users.11.dbf';

set newname for datafile '/ora-pim/ts1/wmpmpp1/index/indx.16.dbf' to
'/ora-pim/ts1/wmpmperf/index/indx.16.dbf';

set newname for datafile '/ora-pim/ts2/wmpmpp1/index/indx.17.dbf' to
'/ora-pim/ts2/wmpmperf/index/indx.17.dbf';

set newname for datafile '/ora-pim/ts1/wmpmpp1/index/indx.18.dbf' to
'/ora-pim/ts1/wmpmperf/index/indx.18.dbf';

set newname for datafile '/ora-pim/ts2/wmpmpp1/index/indx.19.dbf' to
'/ora-pim/ts2/wmpmperf/index/indx.19.dbf';

set newname for datafile '/ora-pim/ts3/wmpmpp1/index/indx.20.dbf' to
'/ora-pim/ts3/wmpmperf/index/indx.20.dbf';

set newname for datafile '/ora-pim/ts4/wmpmpp1/index/indx.21.dbf' to
'/ora-pim/ts4/wmpmperf/index/indx.21.dbf';

set newname for datafile '/ora-pim/ts5/wmpmpp1/index/indx.22.dbf' to
'/ora-pim/ts9/wmpmperf/index/indx.22.dbf';

set newname for datafile '/ora-pim/ts4/wmpmpp1/data/users.12.dbf' to
'/ora-pim/ts4/wmpmperf/data/users.12.dbf';

set newname for datafile '/ora-pim/ts5/wmpmpp1/data/users.13.dbf' to
'/ora-pim/ts9/wmpmperf/data/users.13.dbf';

set newname for datafile '/ora-pim/ts1/wmpmpp1/data/users.14.dbf' to
'/ora-pim/ts7/wmpmperf/data/users.14.dbf';

set newname for datafile '/ora-pim/ts2/wmpmpp1/index/indx.23.dbf' to
'/ora-pim/ts2/wmpmperf/index/indx.23.dbf';

set newname for datafile '/ora-pim/ts4/wmpmpp1/index/indx.24.dbf' to
'/ora-pim/ts4/wmpmperf/index/indx.24.dbf';

set newname for datafile '/ora-pim/ts3/wmpmpp1/data/users.15.dbf' to
'/ora-pim/ts8/wmpmperf/data/users.15.dbf';

set newname for datafile '/ora-pim/ts5/wmpmpp1/index/indx.25.dbf' to
'/ora-pim/ts9/wmpmperf/index/indx.25.dbf';

set newname for datafile '/ora-pim/ts2/wmpmpp1/index/indx.26.dbf' to
'/ora-pim/ts2/wmpmperf/index/indx.26.dbf';

set newname for datafile '/ora-pim/ts1/wmpmpp1/data/users.16.dbf' to
'/ora-pim/ts7/wmpmperf/data/users.16.dbf';

set newname for datafile '/ora-pim/ts2/wmpmpp1/data/users.17.dbf' to
'/ora-pim/ts2/wmpmperf/data/users.17.dbf';

set newname for datafile '/ora-pim/ts3/wmpmpp1/index/indx.27.dbf' to
'/ora-pim/ts8/wmpmperf/index/indx.27.dbf';

set newname for datafile '/ora-pim/ts4/wmpmpp1/index/indx.28.dbf' to
'/ora-pim/ts4/wmpmperf/index/indx.28.dbf';

set newname for datafile '/ora-pim/ts5/wmpmpp1/index/indx.29.dbf' to
'/ora-pim/ts9/wmpmperf/index/indx.29.dbf';

set newname for datafile '/ora-pim/ts6/wmpmpp1/index/indx.30.dbf' to
'/ora-pim/ts6/wmpmperf/index/indx.30.dbf';

set newname for datafile '/ora-pim/ts7/wmpmpp1/index/indx.31.dbf' to
'/ora-pim/ts7/wmpmperf/index/indx.31.dbf';

set newname for datafile '/ora-pim/ts8/wmpmpp1/index/indx.32.dbf' to
'/ora-pim/ts8/wmpmperf/index/indx.32.dbf';

set newname for datafile '/ora-pim/ts9/wmpmpp1/index/indx.33.dbf' to
'/ora-pim/ts9/wmpmperf/index/indx.33.dbf';

set newname for datafile '/ora-pim/ts6/wmpmpp1/index/indx.34.dbf' to
'/ora-pim/ts6/wmpmperf/index/indx.34.dbf';

set newname for datafile '/ora-pim/ts7/wmpmpp1/index/indx.35.dbf' to
'/ora-pim/ts7/wmpmperf/index/indx.35.dbf';

set newname for datafile '/ora-pim/ts8/wmpmpp1/index/indx.36.dbf' to
'/ora-pim/ts8/wmpmperf/index/indx.36.dbf';

set newname for datafile '/ora-pim/ts9/wmpmpp1/index/indx.37.dbf' to
'/ora-pim/ts9/wmpmperf/index/indx.37.dbf';

set newname for datafile '/ora-pim/ts6/wmpmpp1/index/indx.38.dbf' to
'/ora-pim/ts6/wmpmperf/index/indx.38.dbf';

set newname for datafile '/ora-pim/ts7/wmpmpp1/index/indx.39.dbf' to
'/ora-pim/ts7/wmpmperf/index/indx.39.dbf';

set newname for datafile '/ora-pim/ts8/wmpmpp1/data/users.18.dbf' to
'/ora-pim/ts8/wmpmperf/data/users.18.dbf';

set newname for datafile '/ora-pim/ts9/wmpmpp1/data/users.19.dbf' to
'/ora-pim/ts9/wmpmperf/data/users.19.dbf';

set newname for datafile '/ora-pim/ts6/wmpmpp1/data/users.20.dbf' to
'/ora-pim/ts6/wmpmperf/data/users.20.dbf';

set newname for datafile '/ora-pim/ts7/wmpmpp1/data/users.21.dbf' to
'/ora-pim/ts7/wmpmperf/data/users.21.dbf';

set newname for datafile '/ora-pim/ts8/wmpmpp1/data/users.22.dbf' to
'/ora-pim/ts8/wmpmperf/data/users.22.dbf';

set newname for datafile '/ora-pim/ts9/wmpmpp1/data/users.23.dbf' to
'/ora-pim/ts9/wmpmperf/data/users.23.dbf';

set newname for datafile '/ora-pim/ts9/wmpmpp1/data/sysaux.03.dbf' to
'/ora-pim/ts9/wmpmperf/data/sysaux.03.dbf';

set newname for tempfile '/ora-pim/ts8/wmpmpp1/temp/temp01.dbf' to
'/ora-pim/ts10/wmpmperf/temp/temp01.dbf';

set newname for tempfile '/ora-pim/ts8/wmpmpp1/temp/temp02.dbf' to
'/ora-pim/ts10/wmpmperf/temp/temp02.dbf';

set newname for tempfile '/ora-pim/ts8/wmpmpp1/temp/temp03.dbf' to
'/ora-pim/ts10/wmpmperf/temp/temp03.dbf';

set newname for tempfile '/ora-pim/ts8/wmpmpp1/temp/temp04.dbf' to
'/ora-pim/ts10/wmpmperf/temp/temp04.dbf';

set newname for tempfile '/ora-pim/ts9/wmpmpp1/temp/temp06.dbf' to
'/ora-pim/ts10/wmpmperf/temp/temp06.dbf';

set newname for tempfile '/ora-pim/ts9/wmpmpp1/temp/temp07.dbf' to
'/ora-pim/ts10/wmpmperf/temp/temp07.dbf';

set newname for tempfile '/ora-pim/ts9/wmpmpp1/temp/temp08.dbf' to
'/ora-pim/ts10/wmpmperf/temp/temp08.dbf';

set newname for datafile '/ora-pim/ts6/wmpmpp1/data/tools05.dbf' to
'/ora-pim/ts10/wmpmperf/data/tools05.dbf';

duplicate target database to wmpmperf
logfile
GROUP 1 (
'/ora-pim/redo1/wmpmperf/redo/redo_01a.log',
'/ora-pim/redo2/wmpmperf/redo/redo_01b.log') SIZE 500M,
GROUP 2 (
'/ora-pim/redo1/wmpmperf/redo/redo_02a.log',
'/ora-pim/redo2/wmpmperf/redo/redo_02b.log') size 500M,
GROUP 3 (
'/ora-pim/redo1/wmpmperf/redo/redo_03a.log',
'/ora-pim/redo2/wmpmperf/redo/redo_03b.log') size 500m,
group 4(
'/ora-pim/redo1/wmpmperf/redo/redo_04a.log',
'/ora-pim/redo2/wmpmperf/redo/redo_04b.log') size 500m
;
}

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

3，删除旧的数据库。

(1) script. to drop datafiles:

SQL> set pages 1000
SQL> l
1* select 'rm -f ' || name from v$datafile
SQL> /

'RM-F'||NAME
--------------------------------------------------------------------------------
rm -f /ora-pim/ts1/wmpmperf/system/system01.dbf
rm -f /ora-pim/ts4/wmpmperf/undo/undorbs01.dbf
rm -f /ora-pim/ts2/wmpmperf/data/sysaux01.dbf
rm -f /ora-pim/ts2/wmpmperf/tools/tools01.dbf
rm -f /ora-pim/ts4/wmpmperf/data/users01.dbf
rm -f /ora-pim/ts2/wmpmperf/data/users02.dbf
rm -f /ora-pim/ts7/wmpmperf/data/users03.dbf
rm -f /ora-pim/ts4/wmpmperf/index/indx01.dbf
rm -f /ora-pim/ts3/wmpmperf/index/indx02.dbf
rm -f /ora-pim/ts2/wmpmperf/index/indx03.dbf
rm -f /ora-pim/ts1/wmpmperf/index/indx04.dbf
rm -f /ora-pim/ts5/wmpmperf/data/blob_tbl_data_01.dbf
rm -f /ora-pim/ts4/wmpmperf/undo/undorbs02.dbf
rm -f /ora-pim/ts2/wmpmperf/tools/tools02.dbf
rm -f /ora-pim/ts4/wmpmperf/data/users04.dbf
rm -f /ora-pim/ts5/wmpmperf/data/users05.dbf
rm -f /ora-pim/ts7/wmpmperf/data/users06.dbf
rm -f /ora-pim/ts2/wmpmperf/data/users07.dbf
rm -f /ora-pim/ts9/wmpmperf/index/indx05.dbf
rm -f /ora-pim/ts1/wmpmperf/index/indx06.dbf
rm -f /ora-pim/ts2/wmpmperf/index/indx07.dbf
rm -f /ora-pim/ts3/wmpmperf/index/indx08.dbf
rm -f /ora-pim/ts2/wmpmperf/tools/tools03.dbf
rm -f /ora-pim/ts3/wmpmperf/index/indx09.dbf
rm -f /ora-pim/ts5/wmpmperf/data/sysaux.02.dbf
rm -f /ora-pim/ts3/wmpmperf/index/indx.10.dbf
rm -f /ora-pim/ts1/wmpmperf/index/indx.11.dbf
rm -f /ora-pim/ts9/wmpmperf/index/indx.12.dbf
rm -f /ora-pim/ts3/wmpmperf/index/indx.13.dbf
rm -f /ora-pim/ts9/wmpmperf/index/indx.14.dbf
rm -f /ora-pim/ts4/wmpmperf/index/indx.15.dbf
rm -f /ora-pim/ts5/wmpmperf/data/users08.dbf
rm -f /ora-pim/ts8/wmpmperf/data/users09.dbf
rm -f /ora-pim/ts1/wmpmperf/tools/tools04.dbf
rm -f /ora-pim/ts8/wmpmperf/data/blob_tbl_data.02.dbf
rm -f /ora-pim/ts4/wmpmperf/data/blob_tbl_data.03.dbf
rm -f /ora-pim/ts5/wmpmperf/data/users.10.dbf
rm -f /ora-pim/ts8/wmpmperf/data/users.11.dbf
rm -f /ora-pim/ts1/wmpmperf/index/indx.16.dbf
rm -f /ora-pim/ts2/wmpmperf/index/indx.17.dbf
rm -f /ora-pim/ts1/wmpmperf/index/indx.18.dbf
rm -f /ora-pim/ts2/wmpmperf/index/indx.19.dbf
rm -f /ora-pim/ts3/wmpmperf/index/indx.20.dbf
rm -f /ora-pim/ts4/wmpmperf/index/indx.21.dbf
rm -f /ora-pim/ts9/wmpmperf/index/indx.22.dbf
rm -f /ora-pim/ts4/wmpmperf/data/users.12.dbf
rm -f /ora-pim/ts9/wmpmperf/data/users.13.dbf
rm -f /ora-pim/ts7/wmpmperf/data/users.14.dbf
rm -f /ora-pim/ts2/wmpmperf/index/indx.23.dbf
rm -f /ora-pim/ts4/wmpmperf/index/indx.24.dbf
rm -f /ora-pim/ts8/wmpmperf/data/users.15.dbf
rm -f /ora-pim/ts9/wmpmperf/index/indx.25.dbf
rm -f /ora-pim/ts2/wmpmperf/index/indx.26.dbf
rm -f /ora-pim/ts7/wmpmperf/data/users.16.dbf
rm -f /ora-pim/ts2/wmpmperf/data/users.17.dbf
rm -f /ora-pim/ts8/wmpmperf/index/indx.27.dbf
rm -f /ora-pim/ts4/wmpmperf/index/indx.28.dbf
rm -f /ora-pim/ts9/wmpmperf/index/indx.29.dbf
rm -f /ora-pim/ts6/wmpmperf/index/indx.30.dbf
rm -f /ora-pim/ts7/wmpmperf/index/indx.31.dbf
rm -f /ora-pim/ts8/wmpmperf/index/indx.32.dbf
rm -f /ora-pim/ts9/wmpmperf/index/indx.33.dbf
rm -f /ora-pim/ts6/wmpmperf/index/indx.34.dbf
rm -f /ora-pim/ts7/wmpmperf/index/indx.35.dbf
rm -f /ora-pim/ts8/wmpmperf/index/indx.36.dbf
rm -f /ora-pim/ts9/wmpmperf/index/indx.37.dbf
rm -f /ora-pim/ts6/wmpmperf/index/indx.38.dbf
rm -f /ora-pim/ts7/wmpmperf/index/indx.39.dbf
rm -f /ora-pim/ts8/wmpmperf/data/users.18.dbf
rm -f /ora-pim/ts9/wmpmperf/data/users.19.dbf
rm -f /ora-pim/ts6/wmpmperf/data/users.20.dbf
rm -f /ora-pim/ts7/wmpmperf/data/users.21.dbf
rm -f /ora-pim/ts8/wmpmperf/data/users.22.dbf
rm -f /ora-pim/ts9/wmpmperf/data/users.23.dbf
rm -f /ora-pim/ts9/wmpmperf/data/sysaux.03.dbf

(2) script. to drop temp data files:

SQL> select 'rm -f ' || name from v$tempfile;

'RM-F'||NAME
--------------------------------------------------------------------------------
rm -f /ora-pim/ts10/wmpmperf/temp/temp01.dbf
rm -f /ora-pim/ts10/wmpmperf/temp/temp02.dbf
rm -f /ora-pim/ts10/wmpmperf/temp/temp03.dbf
rm -f /ora-pim/ts10/wmpmperf/temp/temp04.dbf
rm -f /ora-pim/ts10/wmpmperf/temp/temp06.dbf
rm -f /ora-pim/ts10/wmpmperf/temp/temp07.dbf
rm -f /ora-pim/ts10/wmpmperf/temp/temp08.dbf

(3) script. to drop redo files:

1* select 'rm -f ' || member from v$logfile
SQL> /

'RM-F'||MEMBER
--------------------------------------------------------------------------------
rm -f /ora-pim/redo1/wmpmperf/redo/redo_04a.log
rm -f /ora-pim/redo2/wmpmperf/redo/redo_04b.log
rm -f /ora-pim/redo1/wmpmperf/redo/redo_03a.log
rm -f /ora-pim/redo2/wmpmperf/redo/redo_03b.log
rm -f /ora-pim/redo1/wmpmperf/redo/redo_02a.log
rm -f /ora-pim/redo2/wmpmperf/redo/redo_02b.log
rm -f /ora-pim/redo1/wmpmperf/redo/redo_01a.log
rm -f /ora-pim/redo2/wmpmperf/redo/redo_01b.log

(4) script. to drop control files:

SQL> select value from v$parameter where name = 'control_files';

VALUE
--------------------------------------------------------------------------------
/ora-pim/ts1/wmpmperf/ctl/control01.ctl, /ora-pim/ts2/wmpmperf/ctl/control02.ctl

(5) implement dropping operation

shutdown immediate
#run scripts above

4，创建auxiliary实例：

1)startup $auxiliary nomount and create spfile from pfile
ORACLE_SID=wmpmperf
startup nomount pfile='/opt/oracle-pim/product/1020/dbs/initwmpmperf.ora'
host mv /opt/oracle-pim/product/1020/dbs/spfilewmpmperf.ora /opt/oracle-pim/product/1020/dbs/spfilewmpmperf.ora.old
create spfile='/opt/oracle-pim/product/1020/dbs/spfilewmpmperf.ora' from pfile;

2)restart $auxiliary nomount to apply spfile and check parameters
shutdown immediate
startup nomount
show parameter spfile
show parameter control_files
show parameter db_name
show parameter db_file_name_convert
show parameter log_file_name_convert
exit

5，执行duplicate：

ORACLE_SID=wmpmperf
##currently the database is in nomount status
nohup rman cmdfile='duplicate_wmpmperf.rcv' log='duplicate_wmpmperf.log' &
tail -f duplicate_wmpmperf.log

6，检查数据库是否与源数据库相同。

compare 'select count(*) from dba_objects', 'select count(*) from dba_users' ...

出现的Issue

（1）运行 nohup rman cmdfile='duplicate_wmpmperf.rcv' log='duplicate_wmpmperf.log' & 之后，在restore数据文件的时候，有一个文件不能restore，rman报错退出。经查，是因为目的地的目录没有建，创建之后，再度运行 nohup rman cmdfile='duplicate_wmpmperf.rcv' log='duplicate_wmpmperf.log' &，rman跳过之前的步骤和已经restore的文件，把这个文件也restore了。

（2）resote了全部数据文件之后，在recover的时候报错，找不到某些日志：

LOG：

contents of Memory Script.:
{
set until scn 4204922072;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 27-OCT-09

starting media recovery

archive log thread 1 sequence 17213 is already on disk as file /ora-pim/archive/wmpmpp1/arch/wmpmpp1_1_635341270_17213.arc
archive log thread 1 sequence 17214 is already on disk as file /ora-pim/archive/wmpmpp1/arch/wmpmpp1_1_635341270_17214.arc
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/ora-pim/ts1/wmpmperf/system/system01.dbf'

released channel: c1
released channel: c2
released channel: t1
released channel: t2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/27/2009 22:31:48
RMAN-03015: error occurred in stored script. Memory Script
RMAN-06053: unable to perform. media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 17212 lowscn 4204762870 found to restore
RMAN-06025: no backup of log thread 1 seq 17211 lowscn 4204502109 found to restore

Recovery Manager complete.

又看到ORA-01152的错误了，以前最怕恢复的时候报这个错误，不知道怎么办。这次好在有

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8684388/viewspace-1045263/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8684388/viewspace-1045263/

coke0078
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘a3b6420a-6’ for key ‘callId’
12-14
"SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'a3b6420a-6724-11ea-b2a3-d773d1d6999f' for key 'callId'\nThe SQL being executed was: INSERT INTO `ly_call` (`call_id`, `mobile`, ...
RMAN新特性duplicate概述和使用
热门推荐
真水无香，大象无形，做本分的自己，记录技术点滴
06-06 1万+
RMANDUPLICATION复制概述   DUPLICATE命令可以创建完整的运行良好的数据库副本（clone DB）或Physical standby数据库。 DUPLICATE命令自动为duplicate数据库分配不同的DBID，以便它可以注册在与源数据库相同的catalog中。 DUPLICATION可以是数据库级别，也可以指定表空间级别。 RMAN必须执行基于时间点的恢复，即使
搭建dg在duplicate过程中报错ORA-17628: Oracle error 19505 returned by remote Oracle server
最新发布
zongzizz的博客
11-17 916
搭建dg在duplicate过程中报错ORA-17628: Oracle error 19505 returned by remote Oracle server
Oracle DUPLICATE复制数据库
一玩儿家的田地
01-16 1100
一、DUPLICATE复制数据库         1.使用duplicate命令可以在不影响目标数据库（Target/Primary Database）的情况下，依靠目标数         据库的备份集创建一个Duplicate数据库（数据库副本）。         在创建时既可以选择完整复制目标数据库，也可以仅包含目标数据库中的部分表空间，目标数据库         和Dupli
经验：使用duplicate重建数据库
coke0078的博客
10-28 444
环境： 一台服务器，上有一个产品数据库wmpmpp1以及一个仿真数据库wmpmperf。需要按照wmpmpp1重建wmpmperf。 需求： ===================================Reque...
使用RMAN传输数据_复制数据库
jetliu05的博客
01-26 3387
使用RMAN的DUPLICATE命令可以从源数据库复制全部或部分数据到复制数据库。
ORACLE Duplicate复制数据库
12-20
### ORACLE RMAN DUPLICATE 数据库复制详解 #### 概述 在Oracle环境中，通过RMAN（Recovery Manager）工具可以高效地复制整个数据库。本文将详细介绍如何利用RMAN的`DUPLICATE`命令来实现数据库的复制，并针对两种...
RMAN高级应用之Duplicate复制数据库.docx
10-25
RMAN高级应用之Duplicate复制数据库
Last_Errno: 1062，Last_Error: Error Duplicate entry
09-10
Last_Errno: 1062，Last_Error: Error Duplicate entry ... for key PRIMARY
IAR编译 Error[Li006]: duplicate definitions for
08-04
正确的做法是，将全局变量的定义放在一个.C文件中，然后在其他需要使用的.C文件中通过`extern`关键字声明其引用，但不赋值。 错误示例： ```c // File1.c int i = 0; // File2.c int i = 0; ``` 正确示例...
ORACLE 11g 用Duplicate恢复Data Guard 备库详细过程
csdn's blog
07-26 1万+
1、先查找备库控制文件路径 先在备库上找出控制文件的路径，通过和主库一样，不过为了以防万一，还是check为好。 SQL> select name from v$controlfile; NAME -------------------------------------------------------...
通过RMAN备份duplicate异机克隆恢复数据库
weixin_33794672的博客
08-21 248
说明：本来是想做notarget目标库通过目标库备份集创建dataguard测试的，发现从原理上不能实现，但它有一个非常有用的功能，可以实现快速的rman备份异机恢复。之前通过RMAN备份异机恢复要操作很多步骤，通过rmanduplicate方式来实现数据库将非常快速。将为今后有整库迁移工作任务提供又一种方案。本文将记录完整过程。一、环境介绍1.主数据库环境操作系统版本...
DG-duplicate操作注意事项(各种报错应对方法)
clp1532的博客
03-30 1737
DG-duplicate 操作注意事项 1、duplicate时用户必须使用sys用户 2、to standby的脚本 #!/bin/bash LOG=duplicate...
windows到linux的duplicate的DataGuard的db_file_name_convert参数不生效案例
dsc1245的博客
03-12 1363
今天配置windows-> linux的11.2.0.4的DataGuard duplicate，这中间配置了db_file_name_convert和log_file_name_convert，但是总是不生效，每次从windows下同步过来的datafile都被强行塞在了linux的$ORACLE_HOME/dbs/下。原因就是windows和linux的对待字符的区分大小写的态度：win...
Duplicate的一点总结
cjh81378066的博客
12-02 3877
目标端启动静态监听(源端的tns中关于目标端配置，增加(UR=A)是无效的，因为duplicate过程中会关闭再启动目标端，关闭的过程中目标端的监听不再有了，这样源端连接不到目标端，无法再启动目标端)，目标端实例启动到nomou...
Duplicate 复制数据库实验过程
cuiping5771的博客
02-27 393
Duplicate 复制数据库 本次实验通过duplicate命令，在本机环境中创建一个复制数据库。目标数据库为hongye、复制数据库为catdb。 环境别名设置： alias sql='rlwrap ...
oracle同步数据adg_Oracle 11g ADG 部署(duplicate)快速参考
weixin_36371906的博客
02-02 827
本文旨在指导客户完成Oracle 11g的ADG部署工作，主库环境已具备，备库环境已安装完成数据库软件。环境：RHEL 6.8 + Oracle 11.2.0.41.确认主库运行在归档模式这里设置归档存放路径为/u01/arch：mkdir /u01/archchown oracle:oinstall /u01/archSQL>archive log listalter system set...
oracle adg初始化的时候缺少几个数据文件
fanzhuozhuo的博客
11-24 542
oracle adg初始化的时候缺少几个数据文件基本环境恢复backup as copyasmcmd cprestore，switchbackup datafile oracle adg初始化的时候缺少几个数据文件，启动mrp进程报错，或者scn不一致，datafile n需要重新恢复的时候。我们是否想到现在又要重新duplicate一次，或者删掉，重新rman restore一次。如果数据量大，这样就会很浪费时间，那么我们有没有方法可以单独的restore这几个出问题的datafile哪？答案是有的，以下
DUPLICATE到底做了什么？(续)
henrybai的专栏--大亨利白--大码
11-04 1156
前几天对DUPLICATE到底做了什么一直倍感迷惑。。。其实ORACLE推出DUPLICATE命令的目的不过是想把复杂的东西隐藏起来。在RMAN中，所有命令都是基于DBID的，这样即使了DB＿NAME相同，如果DBID不同RMAN也会认为是不同的数据库。如restore controlfile from autobackup命令就是使用DBID来跟踪控制文件自动备份的。可以通过RMAN恢复过程中记
RMAN高级应用：利用Duplicate创建数据库副本的全面指南
"《三思笔记》系列文章深入探讨了RMAN (Recovery Manager) 高级应用中的Duplicate复制数据库技术。该系列由作者君三思于2007年10月15日至10月23日期间撰写，旨在帮助读者理解和实践如何利用RMAN的DUPLICATE命令在...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值