#### 備份遷移恢復
``` 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
```

【RMAN】Oracle11g透過rman升級到12c

相關文章