大约一周以前，做了一次升级迁移的测试，测试是没有问题的。今天有空把过程贴出来。
++++++++++++++++++++++++++++++++++++++迁移步骤+++++++++++++++++++++++++++++++++++++
源库 ： 11.1.0.7
新库 ： 11.2.0.4
源库上面已经执行utlu112i.sql，并进行了数据备份。
1 查看源库的文件路径
2 清理新库的文件，以便于再次测试
3 设置路径转换（在源库上执行），主要是为了写restore脚本方便。并将备份的数据scp到新库
4 模拟业务，源库建立表，插入数据，这个时候备份信息中是没有这些数据的
5 RMAN下恢复SPFILE为PFILE，修改PFILE路径，确认PFILE没有问题后创建SPFILE
6 RMAN下恢复控制文件，
7 注册备份的数据到控制文件。并且进行数据文件的恢复，确认数据文件路径是新库上的路径
-- 如果仅仅是为了测试升级过程，不保证数据一致性，则步骤8可以不做，直接open resetlogs upgrade
8 copy 源库的控制文件、online redo，archived log到新库。然后mount，然后更改online redo,datafile的路径，并RMAN注册archived log
9 recover database，测试是完全恢复的
10 启动DB到upgrade 状态
11 运行脚本@?/rdbms/admin/catupgrd.sql
12 检查升级结果 @?/rdbms/admin/utlu112s.sql
++++++++++++++++++++++++++++++++++++++迁移步骤+++++++++++++++++++++++++++++++++++++
前提准备
设置SQL提示符
set sqlp "_user'@'_connect_identifier'@'New>"
set sqlp "_user'@'_connect_identifier'@'OLD>"
将高版本的库上的$ORACLE_HOME/rdbms/admin/utlu112i.sql复制一份到低版本的库上，
在低版本的库上执行，然后对低版本的库进行备份，用备份内容进行异机恢复。
SQL> SPOOL upgrade_info.log
SQL> @$11g_ORACLE_HOME/rdbms/admin/utlu112i.sql
SQL> SPOOL OFF
备注： 备份源库完毕后，当前日志68 。备份归档日志到68（68已经备份）。
备份之前表t20171214里面两条数据，现在备份完毕后，再插入两条数据，以模拟业务的变化。切换日志，当前日志为79
1 查看源库（旧库）的文件路径
col rmstr for a60
set pagesize 500
select name file_name from v$controlfile
union all
select name from v$datafile
union all
select name from v$tempfile
union all
select member rmstr from v$logfile;
2 清理新库的信息，以便于再次做测试
col rmstr for a60
set pagesize 500
select 'rm '||name remove_sql from v$controlfile
union all
select 'rm '||file_name from dba_data_files
union all
select 'rm '||file_name from dba_temp_files
union all
select 'rm '||member rmstr from v$logfile;
3 设置路径转换（在源库上执行）
set linesize 200
set pagesize 1000
select 'set newname for datafile '||file#||' to '''||REPLACE(name,'/oracle/orames/db/apps_st/data/','/u01/app/orames/oradata/mesprod/')||''';' from v$datafile;
--
set linesize 200
set pagesize 1000
select 'set newname for datafile '||file#||' to '''||REPLACE(name,'/u01/app/oracle117/oradata/orcl','/u01/app/oracle/oradata/orcl')||''';' from v$datafile;
-- rename file ，如有必要
set pagesize 200
set linesize 200
select 'alter database rename file '''||name||''' to '''||replace(name,'/oracle/orames/db/apps_st/data/','/u01/app/orames/oradata/mesprod/')||''';' from v$tempfile where name like '/oracle%'
union all
select 'alter database rename file '''||name||''' to '''||replace(name,'/oracle/orames/db/apps_st/data/','/u01/app/orames/oradata/mesprod/')||''';' from v$datafile where name like '/oracle%';
4 使用12月13日的备份，和12月14日的备份，进行还原。为了测试数据一致性。12月14日创建1个表。切换几次日志。然后备份。
SQL> select * from t20171214;
ID
----------
1
2
SQL>
5 恢复spfile ，可以先恢复为pfile，修改里面参数，确认没有问题后，再创建spfile
先copy password 文件到新的库上
export ORACLE_SID=orcl
rman target /
set dbid=1490263797
startup nomount -- rman 下启动
恢复spfile
--RESTORE SPFILE TO PFILE '/home/oracle/backup/bak1214/initorcl.ora' FROM '/home/oracle/backup/bak1214/o1_mf_s_962704675_f33po4ph_.bkp';
RESTORE SPFILE TO PFILE '/home/oracle/initorcl.ora' FROM '/home/oracle/backup20171214/2017_12_14/o1_mf_s_962708944_f33ttld8_.bkp';
修改恢复的spfile里面的一些参数，比如文件路径等等（新库的文件路径，提前要在新库上建立好）
关闭实例，确认使用恢复到的spfile可以进行启动到nomount；
shutdown immediate
--startup nomount pfile='/home/oracle/backup/bak1214/initorcl.ora'
startup nomount pfile='/home/oracle/initorcl.ora';
shutdown immediate
--create spfile from pfile='/home/oracle/backup/bak1214/initorcl.ora'
create spfile from pfile='/home/oracle/initorcl.ora';
6 恢复控制文件
使用恢复到的spfile启动到nomuont，恢复控制文件
startup nomount;
--restore controlfile from '/home/oracle/backup/bak1214/o1_mf_s_962704675_f33po4ph_.bkp'
restore controlfile from '/home/oracle/backup20171214/2017_12_14/o1_mf_s_962708944_f33ttld8_.bkp';
alter database mount；
7 注册备份文件到控制文件，并进行数据文件恢复
--catalog start with '/home/oracle/bakall/'
catalog start with '/home/oracle/backup20171214/'
进行恢复
/*
run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
set newname for datafile 1 to '/u01/app/oracle124/oradata/orcl/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle124/oradata/orcl/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle124/oradata/orcl/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle124/oradata/orcl/users01.dbf';
set newname for datafile 5 to '/u01/app/oracle124/oradata/orcl/example01.dbf';
restore database;
switch datafile all;
release channel c1;
release channel c2;
}
*/
-- restore 完成，查看datafile的路径
/*
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
*/
-- 如果仅仅用于升级功能测试，步骤8不用做，直接recover，recover后，restlogs打开。测试功能即可。
-- 步骤8 主要是为了保持旧库和新库的数据的一致性。保证备份后到迁移前这段时间内的业务数据能够提现到新库上。
-- 方法就是copy 旧库的控制文件，redo文件，归档redo文件。然后更新控制文件中的路径，RMAN注册归档日志。然后recover 。
8 copy 源库的控制文件， online redo 到新库
先关闭新库
shutdown Immediate；
备份一下新库的控制文件，然后把旧库的控制文件cp过来
mv /u01/app/oracle/oradata/orcl/cr*.ctl /u01/app/oracle/oradata/orcl/bak_after_restore/
-- copy 控制文件，这个时候再向t20171214中插入数据，现在是5条数据，切换日志。当前日志是87 。归档日志到86 。
[oracle@vdedu2 ~]$ scp /u01/app/oracle117/oradata/orcl/control01.ctl vdedu1:/u01/app/oracle/oradata/orcl/
-- 将控制文件弄三份，和原来一样
cp control01.ctl control02.ctl
cp control01.ctl control03.ctl
--
-- copy online redo
scp /u01/app/oracle117/oradata/orcl/redo01.log vdedu1:/u01/app/oracle/oradata/orcl/
scp /u01/app/oracle117/oradata/orcl/redo02.log vdedu1:/u01/app/oracle/oradata/orcl/
scp /u01/app/oracle117/oradata/orcl/redo03.log vdedu1:/u01/app/oracle/oradata/orcl/
-- or
scp -r /u01/app/oracle117/oradata/orcl/redo0*.log vdedu1:/u01/app/oracle/oradata/orcl/
copy 源库的redo 到新库，
--
alter database rename file '/u01/app/oracle117/oradata/orcl/redo01.log' to '/u01/app/oracle124/oradata/orcl/redo01.log';
alter database rename file '/u01/app/oracle117/oradata/orcl/redo02.log' to '/u01/app/oracle124/oradata/orcl/redo02.log';
alter database rename file '/u01/app/oracle117/oradata/orcl/redo03.log' to '/u01/app/oracle124/oradata/orcl/redo03.log';
copy 源库的归档日志过来
scp -r /u01/app/oracle117/flash_recovery_area/ORCL/archivelog/2017_12_14/ vdedu1:/home/oracle/backup20171214/
-- 控制文件，online redo ，归档redo 都copy过来了，启动到mount，查看路径，需要转换； -- 写脚本
startup mount;
-- 转换db file
set pagesize 200
set linesize 200
select 'alter database rename file '''||name||''' to '''||replace(name,'/u01/app/oracle117/oradata/orcl/','/u01/app/oracle/oradata/orcl/')||''';' from v$tempfile
union all
select 'alter database rename file '''||name||''' to '''||replace(name,'/u01/app/oracle117/oradata/orcl/','/u01/app/oracle/oradata/orcl/')||''';' from v$datafile ;
union all
select 'alter database rename file '''||member||''' to '''||replace(member,'/u01/app/oracle117/oradata/orcl/','/u01/app/oracle/oradata/orcl/')||''';' from v$logfile ;
/*
alter database rename file '/u01/app/oracle117/oradata/orcl/temp01.dbf' to '/u01/app/oracle/oradata/orcl/temp01.dbf';
alter database rename file '/u01/app/oracle117/oradata/orcl/system01.dbf' to '/u01/app/oracle/oradata/orcl/system01.dbf';
alter database rename file '/u01/app/oracle117/oradata/orcl/sysaux01.dbf' to '/u01/app/oracle/oradata/orcl/sysaux01.dbf';
alter database rename file '/u01/app/oracle117/oradata/orcl/undotbs01.dbf' to '/u01/app/oracle/oradata/orcl/undotbs01.dbf';
alter database rename file '/u01/app/oracle117/oradata/orcl/users01.dbf' to '/u01/app/oracle/oradata/orcl/users01.dbf';
alter database rename file '/u01/app/oracle117/oradata/orcl/example01.dbf' to '/u01/app/oracle/oradata/orcl/example01.dbf';
alter database rename file '/u01/app/oracle117/oradata/orcl/redo03.log' to '/u01/app/oracle/oradata/orcl/redo03.log';
alter database rename file '/u01/app/oracle117/oradata/orcl/redo02.log' to '/u01/app/oracle/oradata/orcl/redo02.log';
alter database rename file '/u01/app/oracle117/oradata/orcl/redo01.log' to '/u01/app/oracle/oradata/orcl/redo01.log';
*/
-- 再次确认datafile 、online reco、tempfile路径没有问题 （不过现在还没有tempfile）
select name from v$datafile;
select member from v$logfile;
select name from v$tempfile;
9 recover database ；
--注册归档日志。刚copy过来的，从68到后来的86（这段时间插入了数据）
catalog start with '/home/oracle/backup20171214/2017_12_14/'
-- 开始还原
recover database; --测试结果。recover是正常的。
10 打开db
alter database open; -- 出错，DB自动关闭 需要upgrade参数
/*
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 6789
Session ID: 125 Serial number: 5
*/
--mount db，这个时候查看归档日志。是从86继续的。无论是查v$archived_log 还是archive Log list 命令。
startup mount;
-- open db -- 不需要resetlog
alter database open upgrade;
-- 一些数据或者参数的查看。 验证之前的数据，表t20171214里面的数据，是5条，和之前的是一样的。（也就是说，备份后发生的业务数据，也迁移过来了）
/*
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 6789
Session ID: 125 Serial number: 5
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1272213504 bytes
Fixed Size 1344680 bytes
Variable Size 721423192 bytes
Database Buffers 536870912 bytes
Redo Buffers 12574720 bytes
Database mounted.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 86
Next log sequence to archive 88
Current log sequence 88
SQL> alter database open upgrade;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 87
Next log sequence to archive 89
Current log sequence 89
SQL> select * from t20171214;
ID
----------
1
2
3
4
5
SQL>
*/
11 运行脚本
@?/rdbms/admin/catupgrd.sql
-- 在运行升级脚本前，查看下迁移后，新库的组件状态，还是11.1.0.7
/*
SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 12-14-2017 14:09:46
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.1.0.7.0 00:10:25
JServer JAVA Virtual Machine
. VALID 11.1.0.7.0 00:02:38
Oracle Workspace Manager
. VALID 11.1.0.7.0 00:00:51
OLAP Analytic Workspace
. VALID 11.1.0.7.0 00:00:18
OLAP Catalog
. VALID 11.1.0.7.0 00:00:42
Oracle OLAP API
. VALID 11.1.0.7.0 00:00:14
Oracle Enterprise Manager
. VALID 11.1.0.7.0 00:05:51
Oracle XDK
. VALID 11.1.0.7.0 00:00:36
Oracle Text
. VALID 11.1.0.7.0 00:00:33
Oracle XML Database
. VALID 11.1.0.7.0 00:02:46
Oracle Database Java Packages
. VALID 11.1.0.7.0 00:00:10
Oracle Multimedia
. VALID 11.1.0.7.0 00:01:39
Spatial
. VALID 11.1.0.7.0 00:01:56
Oracle Ultra Search
. VALID 11.1.0.7.0 00:00:29
Oracle Expression Filter
. VALID 11.1.0.7.0 00:00:09
Oracle Rules Manager
. VALID 11.1.0.7.0 00:00:13
Oracle Application Express
. VALID 3.0.1.00.12 00:01:31
Gathering Statistics
. 00:04:40
Total Upgrade Time: 00:35:50
PL/SQL procedure successfully completed.
*/
--运行脚本 14:11 15:01 完成。运行大约50分钟 ，运行过程会产生大量的归档日志
@?/rdbms/admin/catupgrd.sql
12 查看是否升级完毕
@?/rdbms/admin/utlu112s.sql
/*
[oracle@vdedu1 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 14 15:01:51 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1272213504 bytes
Fixed Size 1344680 bytes
Variable Size 754977624 bytes
Database Buffers 503316480 bytes
Redo Buffers 12574720 bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 169
Next log sequence to archive 171
Current log sequence 171
SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 12-14-2017 15:03:14
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.3.0 00:13:14
JServer JAVA Virtual Machine
. VALID 11.2.0.3.0 00:04:32
Oracle Workspace Manager
. VALID 11.2.0.3.0 00:00:33
OLAP Analytic Workspace
. VALID 11.2.0.3.0 00:00:17
OLAP Catalog
. VALID 11.2.0.3.0 00:00:35
Oracle OLAP API
. VALID 11.2.0.3.0 00:00:24
Oracle Enterprise Manager
. VALID 11.2.0.3.0 00:03:02
Oracle XDK
. VALID 11.2.0.3.0 00:00:19
Oracle Text
. VALID 11.2.0.3.0 00:00:32
Oracle XML Database
. VALID 11.2.0.3.0 00:02:25
Oracle Database Java Packages
. VALID 11.2.0.3.0 00:00:08
Oracle Multimedia
. VALID 11.2.0.3.0 00:03:11
Spatial
. VALID 11.2.0.3.0 00:03:19
Oracle Expression Filter
. VALID 11.2.0.3.0 00:00:08
Oracle Rules Manager
. VALID 11.2.0.3.0 00:00:15
Oracle Application Express
. VALID 3.2.1.00.12 00:08:45
Gathering Statistics
. 00:06:09
Total Upgrade Time: 00:48:18
PL/SQL procedure successfully completed.
SQL>
*/
END
---
有点乱，其实就是异机恢复，恢复完毕后，再进行升级.
如果需要和源库保持一致的话，则需要把源库关闭，控制文件，online redo，备份点到关机时间点这段archvielog 拷贝过来，并修改控制文件内记录的路径。recover即可保证和旧库的数据是一致的。
接下来的就是upgrade了，运行Oracle提供的脚本进行Upgrade。
end
扫一扫
1936
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
