rman数据迁移测试

1.以oracle用户身份，备份源数据库，脚本如下：
run{
allocate channel c1 type disk
format '/oracle/rman_bk/lev_0_%d_%T%s%p%u.bak';
BACKUP  database ;
sql 'alter system switch logfile';
}
备份完毕后，修改备份集文件的权限为777.
chmod 777 lev_0_PROD_200802236106j9e8fo.bak

2.创建新用户ora9208，并以该用户身份安装oracle软件。安装完毕后，参照oracle用户的初始化参数文件，为ora9208用户创建初始化参数文件。如下：
*.background_dump_dest='/ora9208/admin/prod/bdump'
*.compatible='9.2.0.4'
*.control_files='/ora9208/oradata/prod/control01.ctl','/ora9208/oradata/prod/control02.ctl'
*.core_dump_dest='/ora9208/admin/prod/cdump'
*.db_16k_cache_size=30M
*.db_block_size=8192
*.db_cache_size=30M#DEMO
*.db_file_multiblock_read_count=16
*.db_name='prod'
*.job_queue_processes=1
*.log_archive_dest='/ora9208/arch'
*.log_archive_max_processes=2
*.log_archive_start=TRUE
*.max_dump_file_size='100000'
*.nls_date_format='yyyy-mm-dd hh24:mi:ss'
*.optimizer_mode='CHOOSE'
*.oracle_trace_enable=TRUE
*.parallel_automatic_tuning=true
*.parallel_threads_per_cpu=4#SMALL
*.partition_view_enabled=true
*.pga_aggregate_target=30M#DEMO
*.query_rewrite_enabled='true'
*.query_rewrite_integrity='trusted'
*.remote_login_passwordfile='exclusive'
*.shared_pool_size=20M#DEMO
*.sql_trace=FALSE
*.star_transformation_enabled='true'
*.undo_management='auto'
*.undo_retention=180
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='/ora9208/admin/prod/udump'
*.utl_file_dir='/oracle/admin/log'
#*.db_file_name_convert='/oracle/oradata/prod','/ora9208/oradata/prod'
*.log_file_name_convert='/oracle/oradata/prod','/ora9208/oradata/prod'

创建完毕后，将oracle用户下的密码文件copy到ora9208用户的相应目录下。

同时，需要将自动备份的控制文件copy到ora9208用户的$ORACLE_HOME/dbs下（rman恢复时，会自动到这个路径下寻找自动备份的控制文件）
[ora9208@ora-as4 prod]$ cp /oracle/product/9.2.0.4/dbs/c-59186099-20080223-03 $ORACLE_HOME/dbs

3.在ora9208用户下，将备份恢复到新的路径下，恢复脚本如下：
rman target /<<EOF
startup force nomount;
set DBID=51059446;
run{
restore controlfile from autobackup;
sql 'alter database mount';
set newname for datafile  1  to    '/ora9208/oradata/prod/system01.dbf';
set newname for datafile  2  to    '/ora9208/oradata/prod/undotbs01.dbf';
set newname for datafile  3  to    '/ora9208/oradata/prod/users01.dbf';
set newname for datafile  4  to    '/ora9208/oradata/prod/idx01.dbf';
set newname for datafile  5  to    '/ora9208/oradata/prod/tools01.dbf';
set newname for datafile  6  to    '/ora9208/oradata/prod/stress_test_tables01.dbf';
set newname for datafile  7  to    '/ora9208/oradata/prod/stress_test_indexes01.dbf';
restore database;
switch datafile 1;
switch datafile 2;
switch datafile 3;
switch datafile 4;
switch datafile 5;
switch datafile 6;
switch datafile 7;
recover database ;
}
EOF

4.将源数据库的归档日志copy到目标数据库的归档路径下，做recover。
[ora9208@ora-as4 arch]$ sqlplus '/as sysdba'
 
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Feb 23 12:39:34 2008
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
 
SQL> recover database using backup controlfile;
ORA-00279: change 2929288 generated at 02/23/2008 12:07:20 needed for thread 1
ORA-00289: suggestion : /ora9208/arch/1_130.dbf
ORA-00280: change 2929288 for thread 1 is in sequence #130
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2929319 generated at 02/23/2008 12:08:49 needed for thread 1
ORA-00289: suggestion : /ora9208/arch/1_131.dbf
ORA-00280: change 2929319 for thread 1 is in sequence #131
ORA-00278: log file '/ora9208/arch/1_130.dbf' no longer needed for this
recovery
 
 
ORA-00279: change 2929613 generated at 02/23/2008 12:23:23 needed for thread 1
ORA-00289: suggestion : /ora9208/arch/1_132.dbf
ORA-00280: change 2929613 for thread 1 is in sequence #132
ORA-00278: log file '/ora9208/arch/1_131.dbf' no longer needed for this
recovery
 
 
ORA-00279: change 2929615 generated at 02/23/2008 12:23:23 needed for thread 1
ORA-00289: suggestion : /ora9208/arch/1_133.dbf
ORA-00280: change 2929615 for thread 1 is in sequence #133
ORA-00278: log file '/ora9208/arch/1_132.dbf' no longer needed for this
recovery
 
 
ORA-00279: change 2929618 generated at 02/23/2008 12:23:24 needed for thread 1
ORA-00289: suggestion : /ora9208/arch/1_134.dbf
ORA-00280: change 2929618 for thread 1 is in sequence #134
ORA-00278: log file '/ora9208/arch/1_133.dbf' no longer needed for this
recovery
 
 
ORA-00279: change 2929620 generated at 02/23/2008 12:23:25 needed for thread 1
ORA-00289: suggestion : /ora9208/arch/1_135.dbf
ORA-00280: change 2929620 for thread 1 is in sequence #135
ORA-00278: log file '/ora9208/arch/1_134.dbf' no longer needed for this
recovery
 
 
ORA-00308: cannot open archived log '/ora9208/arch/1_135.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
 
 
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
[ora9208@ora-as4 arch]$

5.当所有的归档日志都被apply后，将源数据库shutdown，并copy所有剩下的归档日志及redo日志到目标数据库的归档路径下，
继续执行recover
[ora9208@ora-as4 arch]$ sqlplus '/as sysdba'
 
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Feb 23 12:42:57 2008
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
 
SQL> recover database using backup controlfile;
ORA-00279: change 2929620 generated at 02/23/2008 12:23:25 needed for thread 1
ORA-00289: suggestion : /ora9208/arch/1_135.dbf
ORA-00280: change 2929620 for thread 1 is in sequence #135
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/oradata/prod/redo03.log
Log applied.
Media recovery complete.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

6.由于备份的控制文件中，redo log的存放位置仍然指向源数据库的redo log，需要重建控制文件，将redo log的存放位置指向新的路径。将control file备份为trace文件，并修改。

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 200
    MAXLOGMEMBERS 5
    MAXDATAFILES 500
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/ora9208/oradata/prod/redo01.log'  SIZE 20M,
  GROUP 2 '/ora9208/oradata/prod/redo02.log'  SIZE 20M,
  GROUP 3 '/ora9208/oradata/prod/redo03.log'  SIZE 20M
-- STANDBY LOGFILE
DATAFILE
  '/ora9208/oradata/prod/system01.dbf',
  '/ora9208/oradata/prod/undotbs01.dbf',
  '/ora9208/oradata/prod/users01.dbf',
  '/ora9208/oradata/prod/idx01.dbf',
  '/ora9208/oradata/prod/tools01.dbf'
CHARACTER SET ZHT16BIG5
;

7.重建控制文件并打开数据库。
[ora9208@ora-as4 arch]$ sqlplus '/as sysdba'
 
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Feb 23 12:44:36 2008
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
 
SQL> shutdown immediate
ORA-01109: database not open
 
 
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 200
    MAXLOGMEMBERS 5
    MAXDATAFILES 500
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/ora9208/oradata/prod/redo01.log'  SIZE 20M,
  GROUP 2 '/ora9208/oradata/prod/redo02.log'  SIZE 20M,
  GROUP 3 '/ora9208/oradata/prod/redo03.log'  SIZE 20M
-- STANDBY LOGFILE
DATAFILE
  '/ora9208/oradata/prod/system01.dbf',
  '/ora9208/oradata/prod/undotbs01.dbf',
  '/ora9208/oradata/prod/users01.dbf',
  '/ora9208/oradata/prod/idx01.dbf',
  '/ora9208/oradata/prod/tools01.dbf'
CHARACTER SET ZHT16BIG5
;ORACLE instance started.
 
Total System Global Area  131143096 bytes
Fixed Size                   452024 bytes
Variable Size              62914560 bytes
Database Buffers           67108864 bytes
Redo Buffers                 667648 bytes
SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20 
 
Control file created.
 
SQL> alter database open resetlogs;
 
Database altered.