具体说明可参考其他文档或官方文档， 主要命令如下
备份迁移恢复
--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;
升级
升级
--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
--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