#### 备份迁移恢复
``` sql
--rman 迁移数据库，建议迁移前先检查无效对象等相关信息，清理olap catalog
run {
	allocate channel ch1 type disk;
	allocate channel ch2 type disk;
	sql 'alter system archive log current';
	sql 'alter system archive log current';
	backup format '/oracle/rmanbackup/orcl_full_%T_%s_%p' database plus archivelog delete all input;
	backup format '/oracle/rmanbackup/orcl_controlfile_%T_%s_%p' current controlfile;
	sql 'alter system archive log current';
	backup format '/oracle/rmanbackup/orcl_arch_%Y%M%D_%s_%p' archivelog all;
	release channel ch1;
	release channel ch2;
}

export ORACLE_SID=mytest
startup nomount pfile='/oracle/backup/pfile2017.ora';

restore controlfile from '/oracle/backup/orcl_controlfile_20171208_23_1';

--启动到mount阶段
sql 'alter database mount';

catalog start with '/oracle/backup';

run {
set newname for database to '/oracle/mytest/oradata/%U';
restore database;
switch datafile all;
}

recover database until sequence 155;

alter database rename file '/oracle/app/oracle/oradata/mytest/redo01.log' to '/oracle/mytest/oradata/redo01.log';
alter database rename file '/oracle/app/oracle/oradata/mytest/redo02.log' to '/oracle/mytest/oradata/redo02.log';
alter database rename file '/oracle/app/oracle/oradata/mytest/redo03.log' to '/oracle/mytest/oradata/redo03.log';

select group#,status from v$log;

alter database open resetlogs upgrade;

--删除临时表空间
alter database tempfile '/oracle/app/oracle/oradata/mytest/temp01.dbf' drop;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/mytest/oradata/TEMP01.DBF' SIZE 1G autoextend on;

create temporary tablespace temp01 tempfile '+DATA' SIZE 1G;
alter database default temporary tablespace temp01;
drop tablespace temp including contents and datafiles cascade constraints;
```
#### 升级
``` sql
升级
--upgrade
cd $ORACLE_HOME/bin
./dbupgrade

Oracle Database 12.2 Post-Upgrade Status Tool           12-08-2017 23:24:43

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS

Oracle Server                          UPGRADED      12.2.0.1.0  00:13:27
JServer JAVA Virtual Machine           UPGRADED      12.2.0.1.0  00:05:30
Oracle Workspace Manager               UPGRADED      12.2.0.1.0  00:01:32
OLAP Analytic Workspace                UPGRADED      12.2.0.1.0  00:00:39
OLAP Catalog                         OPTION OFF      11.2.0.4.0  00:00:00
Oracle OLAP API                        UPGRADED      12.2.0.1.0  00:00:22
Oracle XDK                             UPGRADED      12.2.0.1.0  00:00:42
Oracle Text                            UPGRADED      12.2.0.1.0  00:00:49
Oracle XML Database                    UPGRADED      12.2.0.1.0  00:02:48
Oracle Database Java Packages          UPGRADED      12.2.0.1.0  00:00:20
Oracle Multimedia                      UPGRADED      12.2.0.1.0  00:03:05
Spatial                                UPGRADED      12.2.0.1.0  00:06:10
Oracle Application Express             UPGRADED     5.0.4.00.12  00:12:24
Final Actions                                                    00:03:18
Post Upgrade                                                     00:17:03

Total Upgrade Time: 01:08:41

Database time zone version is 14. It is older than current release time
zone version 26. Time zone upgrade is needed using the DBMS_DST package.

Grand Total Upgrade Time:    [0d:1h:12m:16s]
~

startup  pfile='/oracle/backup/pfile2017.ora';

--Oracle components message
set lines 200
set pages 999
col comp_id format a10
col comp_name format a40
col status format a10
col version format a20
select comp_id,comp_name,status,substr(version,1,10) as version from dba_registry;

cd $ORACLE_HOME/rdbms/admin

$ sqlplus "/as sysdba"
SQL> STARTUP
SQL> @utlu122s.sql

@?/rdbms/admin/catuppst.sql 
@?/rdbms/admin/utlrp.sql

alter system set compatible='12.2.0.1.0' scope=spfile; 
alter system set max_string_size=EXTENDED scope=spfile;

alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.70)(PORT = 1521))' sid='*';

SELECT * FROM RCVER;

rman TARGET / CATALOG system/oracle@mytest
UPGRADE CATALOG;

create pluggable database mytest
as clone
using '/home/oracle/noncdb.xml'
file_name_convert=('/oracle/mytest/oradata','/oracle/app/oracle/oradata/firsoul/mytest') copy;

CREATE PLUGGABLE DATABASE mytest USING '/home/oracle/noncdb.xml' NOCOPY;

--清理无效对象

select 'drop '||object_type||' ' || owner || '.' ||object_name||';'
from dba_objects
where status='INVALID' AND OWNER = 'OLAPSYS';

select 'drop public synonym ' || synonym_name || ';'
       from dba_synonyms
       where owner='PUBLIC' and synonym_name in (select object_name from dba_objects
where status='INVALID' and object_type='SYNONYM');

--OLAP Catalog (OLAP AMD) 12c后不支持，可提前清理
@?/olap/admin/catnoamd.sql
@?/olap/admin/olapidrp.plb
@?/olap/admin/catnoaps.sql
@?/olap/admin/catnoxoq.sql

alter session set container=mytest;
SELECT version FROM v$timezone_file;
select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner,object_type;

shutdown immediate;
startup upgrade;
this NEED to be changed for a PDB to
ALTER pluggable DATABASE CLOSE immediate;
ALTER pluggable DATABASE OPEN upgrade;

drop pluggable database mytest including datafiles;

--timezone 升级 需要提前做
unzip DBMS_DST_scriptsV1.9.zip

@countstatsTSTZ.sql
@upg_tzv_check.sql 
@upg_tzv_apply.sql

SELECT version FROM v$timezone_file;

COMP_ID    COMP_NAME                                STATUS     VERSION
---------- ---------------------------------------- ---------- --------------------
CATALOG    Oracle Database Catalog Views            VALID      12.2.0.1.0
CATPROC    Oracle Database Packages and Types       VALID      12.2.0.1.0
JAVAVM     JServer JAVA Virtual Machine             VALID      12.2.0.1.0
XML        Oracle XDK                               VALID      12.2.0.1.0
CATJAVA    Oracle Database Java Packages            VALID      12.2.0.1.0
OWM        Oracle Workspace Manager                 VALID      12.2.0.1.0
CONTEXT    Oracle Text                              VALID      12.2.0.1.0
XDB        Oracle XML Database                      VALID      12.2.0.1.0
ORDIM      Oracle Multimedia                        VALID      12.2.0.1.0
SDO        Spatial                                  VALID      12.2.0.1.0
APEX       Oracle Application Express               VALID      5.0.4.00.1

11 rows selected.
```

#### no-cdb转为cdb
```sql
--no cdb - pdb
shutdown immediate 
startup mount exclusive 
alter database open read only;

exec dbms_pdb.describe(pdb_descr_file=>'/home/oracle/noncdb.xml');

shutdown immediate

--copy 为了备份  可以使用nocopy
create pluggable database mytest
as clone
using '/home/oracle/noncdb.xml'
file_name_convert=('/oracle/mytest/oradata','/oracle/app/oracle/oradata/firsoul/mytest')
copy;

alter session set container=mytest;
SELECT version FROM v$timezone_file;
alter database open;

--sqlplus sys/oracle@hol/mytest as sysdba
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

alter pluggable database mytest open;
alter pluggable database mytest close;
alter pluggable database mytest open;

alter pluggable database mytest open restricted;
alter session set container=mytest;
exec dbms_pdb.sync_pdb();

@$ORACLE_HOME/rdbms/admin/catclust.sql

@?/rdbms/admin/catupgrd.sql
```

ITPub博客

【RMAN】Oracle11g通过rman升级到12c

最新文章