Oracle 12c升级 Oracle 19c

Oracle 12c 升级 Oracle 19c

本次升级是以原库备份，恢复到目标数据库修改配置后执行dbua升级

备份脚本

run {
    allocate channel ch1 device type disk;
    allocate channel ch2 device type disk;
    sql 'alter system archive log current';
    backup as compressed backupset database format '/u03/backup/fullbackup_%U_%T_%D';
    release channel ch1;
    release channel ch2;
    allocate channel ch1 device type disk;
    allocate channel ch2 device type disk;
    backup as compressed backupset archivelog all format '/u03/backup/archive_%U_%T_%D';
    release channel ch1;
    release channel ch2;
    allocate channel ch1 device type disk;
    backup format '/u03/backup/control_%U_%T_%D' current controlfile;
    release channel ch1;
    allocate channel ch1 device type disk;
    backup spfile format '/u03/backup/spfile_%U_%T_%D';
    release channel ch1;
}

恢复脚本

##  dafafile 和 数据文件编号查询
##  select file#,name from v$datafile;
##  查询出的原数据文件编号，恢复到指定的目标位置
SQL> shutdown immediate;
SQL> startup nomount;
SQL> set dbid XXXXXXXXXX;
SQL> restore controlfile from "xxxxxxxxx";
SQL> alter database mount;
RMAN> run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
set newname for datafile  x to "xxxxxxxx.dbf";
restore database;
switch datafile all;
release channel c1;
release channel c2;
}

备份归档日志

##创建指定归档日志目录
mkdir /u03/backup/archivelog
##创建一张测试表
create table testtable (id number(10));
insert into testtable values(1);
insert into testtable values(2);
insert into testtable values(3);
##执行检查点
alter system chekpoint;
##切换日志
alter system switch logfile;
##备份全部归档日志
backup archivelog all format '/u03/backup/archivelog/rman_arch_%T_%u';

恢复新的归档日志

##注册归档日志
RMAN> catalog start with '/u03/backup/archivelog/';
##恢复归档日志
RMAN> restore archivelog all;
RMAN> restore archivelog from logseq 78574;
RMAN> recover database until scn 86327108973;
##设置输出格式
SQL> set pagesize 1000 linesize 1000;
SQL> col member for a50;
##查看redolog
SQL> select a.group#,a.member,b.status from v$logfile a left join v$log b on a.group#=b.group#;
##重命名redolog
SQL> alter database rename file '/u02/oradata/xxx/redo04.log' to '/u01/app/oracle/oradata/xxx/redo04.log';
##删除redolog
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
##新建redolog
alter database add logfile group 1 ('/u01/app/oracle/oradata/xxx/redo01.log') size 256m;
alter database add logfile group 2 ('/u01/app/oracle/oradata/xxx/redo02.log') size 256m;
alter database add logfile group 3 ('/u01/app/oracle/oradata/xxx/redo03.log') size 256m;
##清理redolog中的信息
alter database clear logfile group x;
##升级方式启动
SQL> alter database open resetlogs upgrade;

执行DBUA进行升级

SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/xxx/xxx/temp1.dbf' size 30G autoextend on;
SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
SQL> alter database default temporary tablespace temp1;
SQL> drop tablespace temp including contents and datafiles;
SQL> exit
[oracle@db ~]# dbua

验证

##查询数据库中最后添加进去的表是否存在，存在则所有数据都还原并升级成功
SQL> select * from testtables;