从Oracle9i RMAN全库备份迁移到 Oracle10g
1. 创建以下目录:
mkdir -pv $ORACLE_BASE/admin/$ORACLE_SID/{{a,b,c,dp,u}dump,pfile}
mkdir -pv $ORACLE_BASE/flash_recovery_area
2. 创建Oracle10g的init$ORACLE_SID.ora
dbinfo.__db_cache_size=339738624
dbinfo.__java_pool_size=4194304
dbinfo.__large_pool_size=4194304
dbinfo.__shared_pool_size=243269632
dbinfo.__streams_pool_size=0
*.aq_tm_processes=0
*.audit_file_dest='/home/oracle10/admin/dbinfo/adump'
*.background_dump_dest='/home/oracle10/admin/dbinfo/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/oracle10/oradata/dbinfo/control01.ctl','/home/oracle10/oradata/dbinfo/control02.ctl','/home/oracle10/oradata/dbinfo/control03.ctl'
*.core_dump_dest='/home/oracle10/admin/dbinfo/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dbinfo'
*.db_recovery_file_dest='/home/oracle10/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbinfoXDB)'
*.job_queue_processes=0
*.open_cursors=300
*.pga_aggregate_target=199229440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=598736896
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/oracle10/admin/dbinfo/udump'
3. 恢复控制文件:
rman target /
startup nomount;
restore controlfile from '$BAKDIR/controlfile_bak_20150624_DBINFO';
alter database mount;
出现下面错误:
ORA-19905: log_archive_format must contain %s, %t and %r
ORA-32004: obsolete and/or deprecated parameter(s) specified.(说使用的已经废弃的参数.)
解决方法：
Oracle9i的init$ORACLE_SID.ora 不能直接用于 Oracle10g. 必须使用Oracle10g的init$ORACLE_SID.ora
上面贴出的模板就是Oracle10g的init$SID.ora模板。
4. 恢复归档日志文件:
crosscheck archivelog all;
crosscheck backup of archivelog all;
#此步骤是删除源主机上归档存储的信息,这样才能从RMAN备份文件中恢复归档到目标机上新指定的位置。
delete expired archivelog all;
restore archivelog from sequence=4260;
出现下面错误:
ORA-19870 : error reading backup piece /orabackup/archivelog_back_20150731_DBINFO
ORA-19809 : limit exceeded for recovery files
ORA-19804 : cannot reclaim 22348234 bytes disk space from 23487289 limit
解决方法:
确保init$ORACLE_SID.ora 里面 "db_recovery_file_dest" 指定的目录存在.
并且“db_recovery_file_dest_size”的大小要足够恢复归档日志。
5. 恢复数据文件:
rman target /
RMAN> run {
allocate channel a1 type disk;
allocate channel a2 type disk;
set newname for datafile 1 to '/oracle/oradata/dbinfo/1024m_system01.dbf';
set newname for datafile 2 to '/oracle/oradata/dbinfo/8192m_undotbs01.dbf';
set newname for datafile 3 to '/oracle/oradata/dbinfo/256m_users01.dbf';
set newname for datafile 4 to '/oracle/oradata/dbinfo/256m_tools01.dbf';
set newname for datafile 5 to '/oracle/oradata/dbinfo/256m_perf.dbf';
set newname for datafile 6 to '/oracle/oradata/dbinfo/app_data01.dbf';
set newname for datafile 7 to '/oracle/oradata/dbinfo/app_data02.dbf';
set newname for datafile 8 to '/oracle/oradata/dbinfo/app_data03.dbf';
set newname for datafile 9 to '/oracle/oradata/dbinfo/app_index01.dbf';
set newname for datafile 10 to '/oracle/oradata/dbinfo/indx01.dbf';
restore database; //这里就是直接采用默认RMAN备份路径中恢复数据文件。
release channel a1;
release channel a2;
}
出现以下错误:
ORA-01180: 无法创建数据文件 1
ORA-01110: 数据文件 1: '/home/oracle/orabase/oradata/ORACLE/system01.dbf'
解决方法:
crosscheck backupset; #检查过期 或 无效的备份集.
delete expired backup; #删除过期的备份集.
另注:
此位先辈遇到此问题的解决方法:
他是通过Catalog重新注册备份集,使其有效,来解决的:http://www.linuxidc.com/Linux/2014-08/104999.htm
6. 恢复Redo log:
rman target /
RMAN> run {
allocate channel a1 type disk;
allocate channel a2 type disk;
sql "alter database rename file ''/dev/dbinfo/lv_redo11_256m'' to ''/oracle/oradata/dbinfo/256m_redo11.log''";
sql "alter database rename file ''/dev/dbinfo/lv_redo12_256m'' to ''/oracle/oradata/dbinfo/256m_redo12.log''";
sql "alter database rename file ''/dev/dbinfo/lv_redo21_256m'' to ''/oracle/oradata/dbinfo/256m_redo21.log''";
sql "alter database rename file ''/dev/dbinfo/lv_redo22_256m'' to ''/oracle/oradata/dbinfo/256m_redo22.log''";
sql "alter database rename file ''/dev/dbinfo/lv_redo31_256m'' to ''/oracle/oradata/dbinfo/256m_redo31.log''";
sql "alter database rename file ''/dev/dbinfo/lv_redo32_256m'' to ''/oracle/oradata/dbinfo/256m_redo32.log''";
sql "alter database rename file ''/dev/dbinfo/lv_redo41_256m'' to ''/oracle/oradata/dbinfo/256m_redo41.log''";
sql "alter database rename file ''/dev/dbinfo/lv_redo42_256m'' to ''/oracle/oradata/dbinfo/256m_redo42.log''";
sql "alter database rename file ''/dev/dbinfo/lv_redo51_256m'' to ''/oracle/oradata/dbinfo/256m_redo51.log''";
sql "alter database rename file ''/dev/dbinfo/lv_redo52_256m'' to ''/oracle/oradata/dbinfo/256m_redo52.log''";
sql "alter database rename file ''/dev/dbinfo/lv_redo61_256m'' to ''/oracle/oradata/dbinfo/256m_redo61.log''";
sql "alter database rename file ''/dev/dbinfo/lv_redo62_256m'' to ''/oracle/oradata/dbinfo/256m_redo62.log''";
switch datafile all;
release channel a1;
release channel a2;
}
7. 进行数据库恢复:
rman target /
recover database;
出现下面错误，可忽略:
RMAN-03002: failure of recover command at 06/24/2015 16:02:25
RMAN-06054: media recovery requesting unknown log: thread 1 scn 277200603
sqlplus / as sysdba
alter database open resetlogs;
执行此命令后, 后Oracle将报错退出,并在alter log中提示要求使用upgrade来启动.
8. 开始进行数据库升级.
sqlplus / as sysdba
startup upgrade
1. 成功进入Oracle后,第一步需要先创建系统辅助表空间.
create tablespace SYSAUX datafile '/home/oracle/oradata/dbinfo/sysaux01.dbf'
size 100M reuse
extent management local
segment space management auto
online;
2. 适当增加临时表空间
alter tablespace TEMP add tempfile '/home/oracle/oradata/dbinfo/temp_200M.dbf' size 200M;
3. 建议关闭归档模式,一方面加快速度，另一方面避免后面操作执行HUNG。
alter database noarchivelog;
archive log list;
4. 修改共享池 和 Java池大小.
shutdown immediate
startup nomount
create spfile from pfile;
ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;
ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;
shutdown immediate
startup upgrade
5. 升级数据字典：
SQL> spool updict.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
SQL> spool off
SQL> shutdown immediate
6. 编译无效应用程序对象:
SQL> startup
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> set pagesize 100
SQL> set linesize 200
SQL> col comp_id for a15
SQL> col status for a10
SQL> col version for a20
#检查组件的版本是否都已经成功升级到10g:
SQL> select comp_id,status,version from dba_registry;
#检查是否存在无效对象(没有输出,为正常.):
SQL> select * from dba_objects where OWNER='GISTAR' AND status='INVALID';
#以SYSDBA身份登录上，执行授权脚本：
SQL> grant select on sys.v_$session to public;
SQL> @?/rdbms/admin/utlrp.sql
#最后检查：
SQL> select * from v$Option;
