【ORACLE】Oracle常用SQL及重点功能说明

来源：这里教程网时间：2026-03-03 16:32:43 作者：

本文档主要内容，依据Oracle11g，介绍Oracle数据库重点功能，包含运维管理、性能分析、备份恢复、RAC、Dataguard、数据迁移等
**版本变更**
- 2021/02/20 ： v1.0 初始版本 -- Firsouler
---
**目录**


  - [1.运维管理](#1运维管理)
    - [1.1 表空间相关](#11-表空间相关)
    - [1.2 用户相关](#12-用户相关)
    - [1.3 表相关](#13-表相关)
    - [1.4 资源限制](#14-资源限制)
    - [1.5 其他](#15-其他)
- [2.性能分析](#2性能分析)
  - [2.1 等待相关](#21-等待相关)
    - [长事务](#长事务)
    - [会话相关](#会话相关)
  - [2.2 执行计划](#22-执行计划)
    - [explain](#explain)
    - [autotrace](#autotrace)
    - [10046](#10046)
    - [10053 优化器选择](#10053-优化器选择)
    - [DBMS_XPLAN](#dbms_xplan)
    - [AWR性能报告相关](#awr性能报告相关)
    - [sqlplus无法登陆](#sqlplus无法登陆)
- [3 集群管理](#3-集群管理)
  - [3.1 节点HANG](#31-节点hang)
  - [3.2 负载均衡](#32-负载均衡)
  - [3.3 问题排查](#33-问题排查)
- [4.ASM管理](#4asm管理)
  - [4.1 空间及磁盘路径](#41-空间及磁盘路径)
  - [4.2 磁盘修复检查](#42-磁盘修复检查)
  - [4.3 ASM相关视图](#43-asm相关视图)
  - [4.4 ASM内部视图X$KFFXP](#44-asm内部视图xkffxp)
  - [4.5 条带化原理和rebalance](#45-条带化原理和rebalance)
    - [条带化](#条带化)
    - [重平衡](#重平衡)
- [5.备份恢复](#5备份恢复)
    - [5.1 RMAN](#51-rman)
    - [5.2 逻辑备份](#52-逻辑备份)
- [6.数据迁移/灾备](#6数据迁移灾备)
  - [6.1 数据泵](#61-数据泵)
    - [命令参考](#命令参考)
    - [部分参数说明](#部分参数说明)
  - [6.2 RMAN](#62-rman)
    - [一次性迁移](#一次性迁移)
    - [增量方式(减少停机时间)](#增量方式减少停机时间)
  - [6.3 Dataguard](#63-dataguard)
  - [6.4 XTTS](#64-xtts)
    - [1.准备](#1准备)
    - [2.初始化](#2初始化)
    - [4.dbms_file_transfer方式初始化](#4dbms_file_transfer方式初始化)
    - [5.增量备份](#5增量备份)
    - [5.收尾](#5收尾)
    - [附](#附)

> 常用sql不再描述
### 1.运维管理
#### 1.1 表空间相关
``` sql
--查看某个表空间数据文件信息
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
col file_name format a65
col tablespace_name for a25
col fid for 9999
select d.file_id fid,d.tablespace_name,d.file_name,d.bytes/1024/1024/1024 as "bytes(Gb)",d.status,d.autoextensible,f.creation_time 
from dba_data_files d,v$datafile f where d.file_id=f.file# and d.tablespace_name like 'CWBASE001%'  order by f.creation_time;
--添加数据文件（建议非自动扩展），请根据增量频率酌情添加
alter tablespace xxx add datafile '+DATA' size 20g;
--ASM磁盘组使用情况  used_percent
col name format a15
set lines 200
with  temp_asm as (select name,trunc(free_mb/1024,2) free_gb,trunc(total_mb/1024,2) total_gb,trunc(REQUIRED_MIRROR_FREE_MB/1024,2) req_free_gb,trunc(USABLE_FILE_MB/1024,2) uf_gb,type,state from v$asm_diskgroup)
select * from (
select t.*,trunc((total_gb-free_gb)/total_gb*100,2) as used_percent from temp_asm t where type='EXTERN' union all
select t.*,trunc(((total_gb-free_gb)/2)/((total_gb-req_free_gb)/2)*100,2) as used_percent from temp_asm t where type='NORMAL' union all
select t.*,trunc(((total_gb-free_gb)/3)/((total_gb-req_free_gb)/3)*100,2) as used_percent from temp_asm t where type='HIGH')
order by used_percent desc;
```
#### 1.2 用户相关
``` sql
--查询用户名、锁定状态、表空间、配置文件、权限---
set pagesize 999
set linesize 150
col username for a25
col ACCOUNT_STATUS for a20
col DEFAULT_TABLESPACE for a20
col PROFILE for a20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select username,ACCOUNT_STATUS,DEFAULT_TABLESPACE,PROFILE,CREATED from dba_users order by created;
--profile 资源限制
select * from dba_profiles where profile='DEFAULT';
alter profile default limit FAILED_LOGIN_ATTEMPTS 10;
alter profile default limit PASSWORD_LIFE_TIME UNLIMITED;
--权限 角色查询
select granted_role,ADMIN_OPTION from dba_role_privs where grantee='AA'; 
select PRIVILEGE,ADMIN_OPTION from DBA_SYS_PRIVS where grantee='SYSTEM';
--查看某个角色包含哪些权限
select role,privilege from role_sys_privs where role='IMP_FULL_DATABASE';
```
#### 1.3 表相关
``` sql
--取行迁移超过3%的表
select table_name,num_rows,CHAIN_CNT,ROUND((CHAIN_CNT/num_rows)*100,2) as "RT%" 
from (select * from dba_tables where num_rows>0 and CHAIN_CNT>0)
where (CHAIN_CNT/num_rows)*100>3
order by CHAIN_CNT/num_rows desc;
-查看表的信息
set lines 150
select a.table_name,a.owner,a.tablespace_name,status,b.bytes/1024/1024 from dba_tables a,dba_segments b where a.table_name=b.segment_name and a.table_name='XYSOUL';
--查看表对应的索引信息
set lines 150
select a.index_name,a.owner,a.table_name,a.table_type,b.bytes/1024/1024 from dba_indexes a,dba_segments b and a.table_name='';
--查看表是否有主键
select CONSTRAINT_NAME,owner,table_name from dba_constraints where CONSTRAINT_TYPE='P' and table_name='ZFI_GDZC_SJ';
--查看表实际大小
select table_name,round((blocks*8)/1024/1024,2) "size (gb)" , 
                            round((num_rows*avg_row_len/1024/1024/1024),2) "actual_data (gb)",
                            (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2))/1024/1024 "wasted_space (gb)"
from dba_tables
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2)) and table_name='T1' and owner='TT'
order by 4 desc;
--查询带有lob表的大小
$vi lob_table_size.sql
ACCEPT SCHEMA PROMPT 'Table Owner: '
ACCEPT TABNAME PROMPT 'Table Name:  '
SELECT
 (SELECT SUM(S.BYTES/1024/1024/1024)                                                                                                 -- The table segment size
  FROM DBA_SEGMENTS S
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (S.SEGMENT_NAME = UPPER('&TABNAME'))) +
 (SELECT SUM(S.BYTES/1024/1024/1024)                                                                                                 -- The Lob Segment Size
  FROM DBA_SEGMENTS S, DBA_LOBS L
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&TABNAME') AND L.OWNER = UPPER('&SCHEMA'))) +
 (SELECT SUM(S.BYTES/1024/1024/1024)                                                                                                 -- The Lob Index size
  FROM DBA_SEGMENTS S, DBA_INDEXES I
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&SCHEMA')))
  "TOTAL TABLE SIZE"
FROM DUAL;
```
#### 1.4 资源限制
``` sql
--设置空闲超过300秒的会话终止
begin
  dbms_resource_manager.create_pending_area;
  dbms_resource_manager.create_plan(
    plan => 'idle_mgmt_plan',
    comment => 'Detects and kills idle sessions');
  dbms_resource_manager.create_plan_directive(
    plan => 'idle_mgmt_plan',
    group_or_subplan => 'other_groups',
    max_idle_time => 300,
    comment => 'Limits idle time for all sessions to 300 seconds');
  dbms_resource_manager.submit_pending_area;
end;
/
alter system set resource_manager_plan = idle_mgmt_plan;
--此查询显示到目前为止已被杀死的空闲会话数
select name, idle_sessions_killed, idle_blkr_sessions_killed from v$rsrc_consumer_group;
--删除资源管理器
begin
  dbms_resource_manager.create_pending_area;
dbms_resource_manager.delete_plan('idle_mgmt_plan');
  dbms_resource_manager.submit_pending_area;
end;
/
alter system set resource_manager_plan ='';
select name from v$rsrc_plan;
--查看每个计划组的限制时长
select d.group_or_subplan, d.max_idle_time, d.max_idle_blocker_time from dba_rsrc_plan_directives d, v$rsrc_plan p
 where d.plan = p.name and (d.max_idle_time is not null or d.max_idle_blocker_time is not null) and d.status is null;
--参考：Using Resource Manager to Detect and Kill Idle Sessions (Doc ID 1557657.1)
```
**profile资源限制**
``` sql
select * from dba_profiles where profile='DEFAULT';
alter profile default limit FAILED_LOGIN_ATTEMPTS 10;
alter profile default limit PASSWORD_LIFE_TIME UNLIMITED;
alter profile default limit PASSWORD_REUSE_MAX 10;
alter profile default limit PASSWORD_LOCK_TIME 1;
alter profile default limit PASSWORD_GRACE_TIME 5;
```
#### 1.5 其他
``` sql
--数据库创建时间，实例启动时间
col instace_start_time for a30
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select (select created from v$database) as "db_create_time",(select startup_time||'-SID'||INSTANCE_NUMBER from gv$instance) as "instace_start_time" from dual;
--控制文件转储
alter session set events 'immediate trace name controlf level 8';
select value from v$diag_info where name='Default Trace File';
--修改快照间隔
col for SNAP_INTERVAL a20
col RETENTION for a30
select * from dba_hist_wr_control;
exec dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>8*24*60);
--查看数据库版本，升级信息
set pages 100 lines 120  
col action format a16  
col namespace format a10  
col version format a10  
col comments format a26  
col action_time format a30  
col bundle_series format a15  
set line 200  
alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';  
select * from dba_registry_history; 
```
## 2.性能分析
### 2.1 等待相关
``` sql
--等待事件个数大于2的
set lines 100
col event for a40
select inst_id,event,count(*) from gv$session group by inst_id,event having count(*) >= 2 order by 2,1;
--非空闲等待
select inst_id,event,count(1) from gv$session where wait_class#<> 6 group by inst_id,event order by 1,3;
--0 Other
--1 Application
--3 Administrative
--4 Concurrency
--6 Idle
--7 Network
--8 User I/O
--9 System I/O
select sid,serial#,sql_id,event,username,paddr from v$session where event='enq: US - contention' order by sql_id desc;
```
#### 长事务
``` sql
set lines 999
col PHY_IO for 99999999
col NAME for a20
select inst_id,addr,status,start_time,START_SCNB,LOG_IO,PHY_IO,name,SES_ADDR from gv$transaction order by START_TIME;
--or
select a.INST_ID,a.addr,a.request,a.type,a.lmode,b.start_time,b.SES_ADDR  from gv$transaction_enqueue a,gv$transaction b where a.addr=b.addr order by b.start_time;
--长事务及spid
set lines 999
col PHY_IO for 99999999
col NAME for a20
col MACHINE for a15
col username for a15
col PROGRAM for a15
select gt.inst_id,gs.sid,gs.serial#,gp.spid,gs.USERNAME,gs.MACHINE,gs.PROGRAM,gt.status,gt.start_time,gt.LOG_IO,gt.PHY_IO,gt.SES_ADDR 
from gv$transaction gt,gv$session gs ,gv$process gp 
where gs.saddr=SES_ADDR and gs.paddr=gp.addr order by START_TIME;
--长事物详细资源
SELECT r.NAME rollname,s.sid SID,s.serial# Serial,
        s.username,s.machine,
        t.start_time,t.status,
        t.used_ublk,USED_UREC,
        t.cr_get,t.cr_change,
        t.log_io,t.phy_io,
        t.noundo NoUndo,g.extents Extents,substr(s.program, 1, 50) program
  FROM v$session s, v$transaction t, v$rollname r,v$rollstat g
 WHERE t.addr = s.taddr
    AND t.xidusn = r.usn
    AND r.usn = g.usn
 ORDER BY t.used_ublk desc;
--根据spid找sql 
select dbms_Lob.substr(s.SQL_FULLTEXT) from v$sql s,v$session se,v$process p where se.paddr=p.addr and s.sql_id=se.sql_id and p.spid='11153';
--or
 set lines 999
 set longc 999
 set long 999
 set pages 999
 col username for a10
 col sql_text for a140
 select se.inst_id,se.username,s.sql_text from gv$sql s,gv$session se,gv$process p where se.paddr=p.addr and s.sql_id=se.sql_id
 and p.spid='13251'; 
```
#### 会话相关
``` sql
--统计活动、非活动会话
select inst_id,status,count(1) from gv$session where type <> 'BACKGROUNND' group by inst_id,status order by 3;
--查看没有会话的进程
col tracefile for a66
select spid,tracefile from v$process where addr not in (select paddr from v$session  where type <> 'BACKGROUNND') and pname is null; 
```
### 2.2 执行计划
#### explain
``` sql
explain plan for ....;
select * from table(dbms_xplan.display);
```
#### autotrace
``` sql
set autotrace on--跟踪实际执行计划，有物理读逻辑读
--or
alter session set statistics_level=all;
sql...
select * from table(dbms_xplan.display_cursor(null,null,'allstats +alias +outline'));
```
#### 10046
``` sql
oradebug setospid 3525; --or oradebug setmypid
oradebug event 10046 trace name context forever,level 12;
oradebug event 10046 trace name context off  
oradebug tracefile_name
```
#### 10053 优化器选择
``` sql
oradebug setmypid
oradebug unlimit
oradebug event 10053 trace name context forever, level 1
...enter your query here...
oradebug event 10053 trace name context off
oradebug tracefile_name
```
#### DBMS_XPLAN
``` sql
--查看sqlplus刚执行过的sql
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
--指定sql id 游标查看
select * from table(dbms_xplan.displaly_cursor('sql_id/hash_value',child_cursor_number,'advanced'));
--or 查看sql_id
select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like '';
--sql历史执行支计划
select * from table(dbms_xplan.display_awr('sql_id'));
```
#### AWR性能报告相关
- @?/rdbms/admin/addmrpt    --某个时间段性能诊断报告
- @?/rdbms/admin/addmrpti   --某个实例的某个节点性能诊断报告
- @?/rdbms/admin/awrgrpt     --RAC 总体性能诊断报告
- @?/rdbms/admin/awrrpti     -- 某个实例（注意i相关的都是可以指定导出某个实例）
- @?/rdbms/admin/ashrpti
- @?/rdbms/admin/awrddrpt    -- 两个不同时间段内性能报告的区别
- @?/rdbms/admin/awrgdrpt     --总体
- @?/rdbms/admin/awrgdrpti    --某个实例
- @?/rdbms/admin/awrsqrpt      --某个sql 具体情况
- @?/rdbms/admin/awrsqrpi       --指定某个实例的sql具体情况
- @?/rdbms/admin/sqltrpt.sql   --sql优化建议
#### sqlplus无法登陆
``` sql
sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 10
```
## 3 集群管理
### 3.1 节点HANG
该问题常见于小型机，操作系统假死（因资源紧张，如内存、cpu），造成该节点实例无法使用，业务连接有两种可能，一个是通过 SCAN IP，另一个是通过VIP ，当通过SCAN IP连接时，如果scan ip恰好操作系统假死节点上，会影响业务使用，或者由于问题节点实例hang住造成阻塞，对业务也有影响。此时应对方式有如下：
- 由于无法登陆操作系统，建议强制关闭问题节点，保障业务正常运行
- 如果暂时无法进入机房，可断开心跳网络，可能会造成正常节点重启，重启后业务可恢复（SCAN IP可能无法使用）
- 设置操作系统资源使用阈值，避免因操作系统资源耗尽造成系统hang住
### 3.2 负载均衡
Oracle RAC11g 中SCAN IP有负载均衡功能，其主要是分发，将客户端连接平均分配到各个节点，但无法保障事务大小、长短，可能会造成每个节点资源使用不均的情况出现，如想合理利用RAC及将每个节点资源发挥更好，需配合业务进行调整，尽量减少RAC节点间数据传输交互。
### 3.3 问题排查
RAC出现问题，排除BUG及服务器问题，主要原因有两个心跳网络和共享磁盘。业务繁忙期间的节点驱逐大多也因为网络流量较大造成心跳超时。排查RAC相关问题，需要检查集群日志、数据库/ASM相关日志、操作系统日志及系统资源使用情况。
```
#集群日志主要如下：
tail -600 $ORACLE_HOME/log/`hostname`/ctssd/octssd.log
tail -600 $ORACLE_HOME/log/`hostname`/crsd/crsd.log
tail -600 $ORACLE_HOME/log/`hostname`/cssd/ocssd.log
tail -600 $ORACLE_HOME/log/`hostname`/ohasd/ohasd.log
tail -600 $ORACLE_HOME/log/`hostname`/alert`hostname`.log
#asm
tail -200 $ORACLE_BASE/diag/asm/+*/+*/trace/alert_+ASM*.log
#监听日志
du -sm $ORACLE_BASE/diag/tnslsnr/*/listener/trace/*
du -sm $ORACLE_HOME/log/diag/tnslsnr/*/listener_scan1/trace/*
```
## 4.ASM管理
### 4.1 空间及磁盘路径
``` sql
--ASM磁盘组使用情况  used_percent, asm实例无法使用计算
col name format a15
set lines 200
with  temp_asm as (select name,trunc(free_mb/1024,2) free_gb,trunc(total_mb/1024,2) total_gb,trunc(REQUIRED_MIRROR_FREE_MB/1024,2) req_free_gb,trunc(USABLE_FILE_MB/1024,2) uf_gb,type,state from v$asm_diskgroup)
select * from (
select t.*,trunc((total_gb-free_gb)/total_gb*100,2) as used_percent from temp_asm t where type='EXTERN' union all
select t.*,trunc(((total_gb-free_gb)/2)/((total_gb-req_free_gb)/2)*100,2) as used_percent from temp_asm t where type='NORMAL' union all
select t.*,trunc(((total_gb-free_gb)/3)/((total_gb-req_free_gb)/3)*100,2) as used_percent from temp_asm t where type='HIGH')
order by used_percent desc;
--or
select name,free_mb,total_mb,type,state from v$asm_diskgroup;
--检查磁盘信息
set pages 999
col path format a20;
col group_name format a15
col name format a20
col FAILGROUP_TYPE format a15
col FAILGROUP format a20
col state for a10
select a.group_number,b.name as group_name,a.FAILGROUP,a.name,a.path,a.state,a.total_mb from v$asm_disk a,v$asm_diskgroup b where a.group_number=b.group_number;
```
### 4.2 磁盘修复检查
``` 
#验证asm磁盘组
kfod asm_diskstring='/dev/mapper/*' disks=all
kfed read /dev/mapper/data1 |grep grpname
#asm 读取磁盘头
kfed read /dev/raw/raw2 text=/tmp/asmdisk_raw2.txt  
kfed read /dev/raw/raw2 aun=0 blkn=0
kfed read /dev/raw/raw2  blkn=510 > /tmp/1.txt 
kfed read /dev/raw/raw2 aun=1 blkn=254
#修复磁盘头
kfed write /dev/raw/raw2 aunum=0 blknum=0 text=/tmp/asmdisk_raw2.txt
#or
dd if=/dev/raw/raw2 of=/tmp/raw2.txt   skip=510 bs=4096 count=1
dd if=/tmp/raw2.txt of=/dev/raw/raw2  
```
### 4.3 ASM相关视图
View Name | X$ Table name  |  Description
---|---|---
V$ASM_DISKGROUP | X$KFGRP  |  performs disk discovery and lists diskgroups
V$ASM_DISKGROUP_STAT | X$KFGRP_STAT  |  diskgroup stats without disk discovery
V$ASM_DISK | X$KFDSK, X$KFKID  |  performs disk discovery, lists disks and their usage metrics
V$ASM_DISK_STAT | X$KFDSK_STAT, X$KFKID  |  lists disks and their usage metrics
V$ASM_FILE | X$KFFIL  |  lists ASM files, including metadata/asmdisk files
V$ASM_ALIAS | X$KFALS  |  lists ASM aliases, files and directories
V$ASM_TEMPLATE | X$KFTMTA  |  lists the available templates and their properties
V$ASM_CLIENT | X$KFNCL  |  lists DB instances connected to ASM
V$ASM_OPERATION | X$KFGMG  |  lists rebalancing operations
N.A. | X$KFKLIB  |  available libraries, includes asmlib path
N.A. | X$KFDPARTNER  |  lists disk-to-partner relationships
N.A. | X$KFFXP  |  extent map table for all ASM files
N.A. | X$KFDAT  |  extent list for all ASM disks
N.A. | X$KFBH  |  describes the ASM cache (buffer cache of ASM in blocks of 4K (_asm_blksize)
N.A. | X$KFCCE  |  a linked list of ASM blocks. to be further investigated
### 4.4 ASM内部视图X$KFFXP
X$KFFXP是ASM(Automatic Storage Management)自动存储管理特性的重要内部视图，该视图反应了File Extent Map映射关系，ASM会将文件split成多个多个piece分片，这些分片被称为Extents。 在Disk上存放这些Extent的位置，就是我们常说的”Allocation Unit”。
KFF意为Kernel File，X$KFFXP即Kernel File Extent Maps， 该内部视图的一条记录代表一个Extent
字段含义如下：
```
GROUP_KFFXP        diskgroup number (1 - 63) ASM disk group number. Join with v$asm_disk and v$asm_diskgroup
NUMBER_KFFXP      file number for the extent ASM file number. Join with v$asm_file and v$asm_alias
COMPOUND_KFFXP    (group_kffxp << 24) + file # File identifier. Join with compound_index in v$asm_file
INCARN_KFFXP      file incarnation number File incarnation id. Join with incarnation in v$asm_file
PXN_KFFXP            physical extent number  Extent number per file
XNUM_KFFXP          extent number bit 31 set if indirect Logical extent
number per file (mirrored extents have the same value)
LXN_KFFXP            logical extent number 0,1 used to identify primary/mirror extent,
2 identifies file header allocation unit (hypothesis) used in the query such that
we go after only the primary extents, not secondary extents 
DISK_KFFXP          disk on which AU is located  Disk number where the extent is allocated.
Join with v$asm_disk Relative position of the allocation unit from the beginning of the disk. 
AU_KFFXP              AU number on disk of AU allocation unit size (1 MB) in v$asm_diskgroup
#从11g开始加入了CHK_KFFXP SIZE_KFFXP 2个新的字段
CHK_KFFXP   未知 可能是范围为[0-256]的某种校验值
SIZE_KFFXP  size_kffxp is used such that we account for variable sized extents. 
sum(size_kffxp) provides the number of AUs that are on that disk.
```
在实例级别控制ASM Diskgroup AU 和 stripe size的是2个隐藏参数 _asm_ausize 1048576 以及 _asm_stripesize 131072。从11g开始一个Extent可能包含多个AU。
可以通过以下脚本查询文件与Extent等ASM属性的映射关系:
``` sql
set linesize 140 pagesize 1400
col "FILE NAME" format a40
set head on
select NAME         "FILE NAME",
       NUMBER_KFFXP "FILE NUMBER",
       XNUM_KFFXP   "EXTENT NUMBER",
       DISK_KFFXP   "DISK NUMBER",
       AU_KFFXP     "AU NUMBER",
       SIZE_KFFXP   "NUMBER of AUs"
  from x$kffxp, v$asm_alias
 where GROUP_KFFXP = GROUP_NUMBER
   and NUMBER_KFFXP = FILE_NUMBER
   and system_created = 'Y'
   and lxn_kffxp = 0
 order by name;
```
### 4.5 条带化原理和rebalance
#### 条带化
ASM的条带化有两种：coarse和fine-gained 。AU是最小空间分配单元，缺省是1M，每个AU缺省由8个128K条带空间组成。
- 在coarse条带化中，一个磁盘对应一个AU
所以该条带化适合连续的I/O读写，比如全表扫描表
- 在fine-gained条带化里，一个AU中的8个128K条带空间，平均打散在磁盘上
也就是说，4个磁盘，1个AU是平均分布在4个磁盘，每个磁盘2个128K
适合于对数据读写延迟比较敏感的文件。如redo日志，控制文件，spfile等
#### 重平衡
从diskgroup中添加或删除disk时候，将触发RBAL进程创建rebalance计划、计算时间开销.而后、发消息给ARBn进程（ASM Reblance）处理该请求
ARBn进程对每个extents进行locked, relocated和unlocked操作，执行过程中可以参考v$asm_operation视图.
ARBn进程的数量由参数ASM_POWER_LIMIT决定,
COD (Continuting Operation Directory)用于记录rebalances情况
如果rebalance失败，在重启instance时候，将从COD读取记录，重新启动rebalance
注意事项：
- rebalance仅仅在diskgroup发生改变时候才进行的，并不是定时执行
如果rebalance执行过程中，server宕机，重启后会自动进行rebalance
- 每个disk的大小必须是相同，如果存在一个小盘，因为rebalance将对每个盘的分配相同比例的空间，可能造成rebalance时候空间不足
如果执行过程中，空闲空间不足，造成rebalance失败，将出现ORA-15041错误，需要再添加disk
如果需要频繁添加disk，每一次都可能造成数据的频繁移动，为提高效率，最好批量添加
如果磁盘大小一样，仍然没有进行rebalance，需要查看asm_power_limit
**参考**
- https://www.askmaclean.com/archives/know-oracle-asm-basic-html.html
- https://blog.csdn.net/iteye_4537/article/details/82130124
## 5.备份恢复
**RMAN叫物理备份，数据泵等叫逻辑备份** 
- RMAN又分为冷备和热备，所谓冷备需要数据库实例为mount状态，此时数据库无法使用且只能还原到当时备份时间点，热备就是数据库需要开归档，根据归档日志，可以恢复至保留期内的任意时间点。
- 逻辑备份(expdp/exp)方式支持跨平台，但无法做到一致性备份，且数据只能恢复到逻辑备份时的时间点。
> 除了Dataguard外，其他灾备均采用的是逻辑方式，如HVR、OGG、DSG等 
#### 5.1 RMAN
1.检查rman备份情况
```
--rman 检查备份情况，类似list backup summary
--LV=L:redo log  A:archivelog F:full  0/1 incremental
--S= A:AVAILABLE D:delete  E:expired
col path for a40
col LV for a2
col D_Type for a6
col Tag for a18
col "Size(G)" for 9999
col E_Hours for 999
col S for a2
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
SELECT distinct A.RECID "BACKUP SET",A.SET_STAMP,
        DECODE (B.INCREMENTAL_LEVEL,
                '', DECODE (BACKUP_TYPE, 'L', 'A', 'F'),
                1, '1',
                0, '0',
                B.INCREMENTAL_LEVEL)
           "LV",
--        B.CONTROLFILE_INCLUDED "INcL_CTL", --备份默认会备份控制文件，可不写
        DECODE (A.STATUS,
                'A', 'A',
                'D', 'D',
                'X', 'E',
                'ERROR')
           "S",
        A.DEVICE_TYPE "D_Type",
        A.START_TIME "Start Time",
        A.COMPLETION_TIME "Completion Time",
       round(A.ELAPSED_SECONDS/60/60,2) "E_Hours",
        round(A.BYTES/1024/1024/1024,2) "Size(G)",
        A.COMPRESSED,
        A.TAG "Tag",
        A.HANDLE "Path"
   FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
  WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO' and a.COMPLETION_TIME>=sysdate-30 
  ORDER BY A.COMPLETION_TIME;
```
2.RMAN参考命令
```
--开启块跟踪
alter database enable block change tracking using file 'd:\oradata\block_change_track';
--rman验证备份、数据文件等
list backup summary;
validate backupset 9;
VALIDATE DATAFILE 1 BLOCK 10;
validate database; --可开并行
validate tablespace users;
backup validate check logical database;
--检查是否有坏块
validate check logical database；
select * from V$DATABASE_BLOCK_CORRUPTION;
--恢复
--对数据文件重命名查询语句
select 'set newname for datafile '||d.file#||' to '''||d.name||''';' from v$datafile d,v$tablespace t where d.ts#=t.ts# and t.INCLUDED_IN_DATABASE_BACKUP='YES';
```
3.注意事项
- 备份是为了恢复，需要定期恢复测试
- 能多开并行就多开，尤其在压缩备份情况下，提高恢复效率
- RMAN也可以用于升级，升级路线参考数据库升级路线
- RMAN不支持跨平台（linux-windows可以，但无法应用归档）
- 备份与数据文件物理隔开，避免因存储问题造成备份也无法使用
- 开并行一定要注意格式，并行下文件需要使用统一名字后缀加%U等相关，不能在分配通道上添加文件名字。
#### 5.2 逻辑备份
逻辑备份又分为两个工具，exp和expdp，exp支持远程方式，但效率较慢；数据泵是Oracle10g以后才有的功能，需要在服务器本地执行（或通过其他数据库dblink方式远程，也属于服务器本地）。数据泵的效率高很多，而且涉及到更换用户、表空间等更方便。
> 数据泵不支持sys用户下的对象导出,数据泵可以，逻辑备份支持对象、用户、表空间、全库方式，还支持只导出元数据或只导出表数据内容。Oracle12c之后导出pdb，支持tnsnames方式，也就是mytest/test@mypdb
主要以数据泵为主，数据泵作业默认以后台执行，可根据作业号终止、暂停、继续等。
```
-- 查找数据泵作业:
set lines 150
set pages 999
col owner_name for a15
col job_name for a30
col operation for a40
col job_mode for a30
col state for a15
col attached_sessions for 20
SELECT owner_name, job_name, operation, job_mode, 
state
--, attached_sessions 
FROM dba_datapump_jobs 
WHERE job_name NOT LIKE 'BIN$%' 
ORDER BY 1,2; 
--启停作业
expdp sys/firsoul ATTACH=SYS_EXPORT_SCHEMA_01
stop_job/start_job
-- 查找数据泵的master表,停止后无法删除
set lines 150
col OWNER.OBJECT for a40
SELECT o.status, o.object_id, o.object_type, 
       o.owner||'.'||object_name "OWNER.OBJECT" 
  FROM dba_objects o, dba_datapump_jobs j 
WHERE o.owner=j.owner_name AND o.object_name=j.job_name 
   AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2; 
```
**windows定时备份参考**
```
@echo off
set ORACLE_SID=metro
set ORACLE_HOME=D:\app\Administrator\product\11.2.0\dbhome_1
set backupfile=expdp_backup_%date:~0,4%年%date:~5,2%月%date:~8,2%日%time:~0,2%时%time:~3,2%分
set logfile=expdp_backup_%date:~0,4%年%date:~5,2%月%date:~8,2%日%time:~0,2%时%time:~3,2%分
expdp '/ as sysdba' directory=exp_dmp  dumpfile=%backupfile%.dmp logfile=%logfile%.log parfile=d:\expdp_fulldb\expdp_par.par
rem 删除7天前备份
echo start del before 7day dmp >>d:\expdp_fulldb\del_7daydmp.log
forfiles /p "d:\expdp_fulldb" /s /m *.dmp /d -7 /c "cmd /c del @file" >>d:\expdp_fulldb\del_7daydmp.log
echo end_%date:~0,4%年%date:~5,2%月%date:~8,2%日%time:~0,2%时%time:~3,2%分 >>d:\expdp_fulldb\del_7daydmp.log
#expdp_par.par
FULL=Y
EXCLUDE=SCHEMA:"in (select username from dba_users where account_status<>'OPEN' or username in('SYS','SYSTEM','MGMT_VIEW'))"
```
## 6.数据迁移/灾备
数据库迁移分为多种方式，需要根据客户业务系统等综合考虑，一般迁移方法如下：
- 数据泵方式（停机时间较长，尤其Oracle11g使用普通lob字段时）
- rman方式（跨平台、版本限制多）
- Dataguard方式（跨平台、版本等限制）
- 跨平台传输表空间（XTTS方式支持增量，相对灵活，需要一定的停机时间）
- HVR、OGG等方式（支持跨平台、跨版本，对业务量较大数据库最后验证数据有一定风险），切换时间短
- 冷备方式（很少使用，停机时间长，限制多）
> 不管什么方式，如果是逻辑迁移，尤其涉及到跨平台、跨版本，务必做迁移测试并强调需要业务做好充分测试，方可正式迁移。
**本节主要介绍数据泵、rman、xtts、Dataguard**
### 6.1 数据泵
数据泵迁移，建议根据用户进行，需要注意相关表空间、关联用户权限、统计信息等。
#### 命令参考
``` sql
--生成语句(用户创建语句、权限等可自行导出)，手动查询备用
select 'create user '||d.username||' identified by values '''||u.password||''' default tablespace '||d.default_tablespace||' temporary tablespace '||d.TEMPORARY_TABLESPACE||';' from dba_users d,user$ u where d.username=u.name and account_status='OPEN' and username not in('SYS','SYSTEM','DBSNMP','SYSMAN','MGMT_VIEW');
--权限（导出时含有权限，可不统计）
select 'grant '||granted_role||' to '||grantee||';' from dba_role_privs where grantee in (select username from dba_users where account_status='OPEN' and username not in('SYS','SYSTEM')); 
select 'grant '||PRIVILEGE||' to '||grantee||';' from DBA_SYS_PRIVS where grantee in (select username from dba_users where account_status='OPEN' and username not in('SYS','SYSTEM')); 
--表空间  注意是否自动扩展,注意系统表空间大小
--全部表空间
select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;
--某一个表空间
select dbms_metadata.get_ddl('TABLESPACE','tablespace_name') from dual;
--收集统计信息
begin
dbms_stats.gather_schema_stats('DBO_ZS',
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'for all columns size auto',cascade=>TRUE,degree=>4);
end;
/
```
#### 部分参数说明
本节主要涉及迁移，以下参数跟迁移相关，导出相关命令可参考expdp -help
参数 | 说明
---|---
REMAP_SCHEMA | 转换用户：REMAP_SCHEMA=source_schema:target_schema
REMAP_TABLESPACE | 转换表空间
REUSE_DATAFILES | 该选项指定建立表空间时是否覆盖已存在的数据文件.默认为N
SKIP_UNUSABLE_INDEXES | 指定导入是是否跳过不可使用的索引,默认为N
SQLFILE | 指定将导入要指定的索引DDL操作写入到SQL脚本中
TABLE_EXISTS_ACTION | 指定当表已经存在时导入作业要执行的操作,默认为SKIP,或者APPEND/TRUNCATE/REPLACE
TRANSFORM | 指定是否修改建立对象的DDL语句，源端表空间太多，目标端指定一个时。语法：TRANSFORM = transform_name:value[:object_type]，如：TRANSFORM=SEGMENT_ATTRIBUTES:N:table
**参考官网文档**
- https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL837
### 6.2 RMAN
使用rman迁移可分为两种情况，一次性停机迁移，另外就是初始迁移、增量恢复，初始迁移是将备份restore，增量恢复是recover。
#### 一次性迁移
一次性迁移也可以分为落地和不落地，不落地方式主要业务完全停用，数据库唯一名需要更换。
``` sql
--不落地迁移命令参考，更适合创建测试库
create user rmanbak identified by oracle;
grant create session,alter system,sysdba to rmanbak;
--tnsnames
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.173)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl) (UR=A)
    )
  )
ORCLTEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.133)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)  (UR=A)
    )
  )
--listener
--listener
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = xckydg)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_home)
     (SID_NAME = xckydg)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.133)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
--创建pfile，注意 db_name 也需要修改,cp pfile  orapw  
rman target rmanbak/oracle@orcl auxiliary rmanbak/oracle@orcltest <<EOF
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby1 type disk;
allocate auxiliary channel stby2 type disk;
allocate auxiliary channel stby3 type disk;
allocate auxiliary channel stby4 type disk;  
duplicate target database to sypmtest from active database;
}
EOF
```
#### 增量方式(减少停机时间)
增量方式可以通过备份归档日志或者使用增量备份两种，建议使用增量备份，如果使用归档，注意新增数据文件。
``` sql
--初始化数据，也就是将初始的0级备份恢复，参考命令
restore database --可多通道提高速度，更改数据文件路径等
--待可停机时，增量备份或拷贝归档日志，检查是否有新增数据文件
--检查是否有新增数据文件
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
col file_name format a65
col tablespace_name for a25
col fid for 9999
select d.file_id fid,d.tablespace_name,d.file_name,d.bytes/1024/1024/1024 as "bytes(Gb)",d.status,d.autoextensible,f.creation_time 
from dba_data_files d,v$datafile f where d.file_id=f.file# and f.creation_time>=to_date('[0级备份时间]','yyyy/mm/dd')  order by f.creation_time;
--注册备份片，开始恢复
recover database --可多通道提高速度,指定恢复时间或序列号
```
### 6.3 Dataguard
Dataguard是迁移、灾备的利器，也是读写分离不错的一个解决方案，使用DG迁移，主要涉及切换及后续收尾工作，如停止dg（并不建议停止）、更改数据库唯一名、IP等。切换及相关命令参考公司support。 注意事项如下：
- 切换前做好充分检查，尤其数据文件位置、同步情况、强制日志附件日志是否开启等
- 切换后如客户不想变更业务配置，需要修改主备数据库唯一名及IP地址
- 切换后如客户不想保留dg，注意将相关参数备份并置空，避免造成归档无法删除等问题
- 切换后做好新库的充分检查、跟踪。
**部分参考命令**
``` sql
--取消相关参数
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='' SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DATA' SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' SID='*';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT =AUTO SID='*';
ALTER SYSTEM SET FAL_SERVER='' SID='*';
--ALTER SYSTEM SET STANDBY_ARCHIVE_DEST='/fra';  11g作废  
--ALTER SYSTEM SET FAL_CLIENT='MYTESTDB' SID='*';    11g作废
alter system set DB_FILE_NAME_CONVERT='' scope=spfile sid='*';
alter system set LOG_FILE_NAME_CONVERT=''  scope=spfile sid='*'; 
--检查/备份主备控制文件中数据文件位置
select f.file#,f.name from v$datafile f  order by f.file#;
select f.file#,f.name from v$tempfile f  order by f.file#;
--修改唯一名时，注意tnsname及数据库相关参数配置
```
### 6.4 XTTS
xtts(Cross Platform Transportable Tablespaces)通过增量的方式来减少停机时间，支持跨平台不同字节序。
**XTTS典型应用场景步骤如下：**
1. 使源数据库中的表空间为只读
2. 将数据文件传输到目标系统
3. 将数据文件转换为目标系统endian格式
4. 使用数据泵从源数据库导出表空间中对象的元数据
5. 使目标数据库中的表空间读写
**除了可传输表空间的限制和注意事项外，还必须满足以下条件**
- 当前版本不支持Windows
- 源数据库必须运行10.2.0.3或更高版本
- 只支持企业版
- 源数据库的兼容参数必须设置为10.2.0或更高
- 源数据库的兼容参数不能大于目标数据库的兼容参数
- 源数据库必须处于归档模式
- 目标数据库必须运行11.2.0.4或更高版本
- 源数据库版本必须低于或等于目标版本，也可作为一个升级方法
- rman的默认设备类型应配置为磁盘
- 源系统的rman不能将设备类型磁盘配置为压缩，跨平台备份压缩不同
- 要移动的表空间集必须全部联机，并且不包含脱机文件。表空间必须读写，只读可以通过普通的XTTS方法移动。

#### 1.准备
``` sql
--检查字符集
SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME;
col VALUE$ for a20
select name,value$ from PROPS$ where name like '%CHARACTERSET';
--检查表空间
select distinct tablespace_name from dba_segments where owner in ('','');
select ts#,file#,name,status from v$datafile where status='OFLINE';
--检查表空间自包含
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TBS3', TRUE);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;
```
#### 2.初始化
1.检查表空间数据文件所在位置
``` sql
select file_name from dba_data_files where tablespace_name in ('MYWORK','MYTEST');
```
2.源端创建相关目录
``` sql
--源端
create directory sourcedir as '+DATA/mydb/datafile';
export TMPDIR=/home/oracle/xtt
mkdir -p /home/oracle/stage_source
--开启块跟踪
alter database enable block change tracking using file '+data';
```
3.目标端创建相关目录
``` sql
sqlplus sys/oracle@mypdb as sysdba
create directory destdir as '+DATA/TESTDB/9B872C593CBF32F8E053D808A8C0FF07/DATAFILE';
mkdir -p /home/oracle/stage_dest
--创建dblink，需要配置tnsname
create public database link ttslink connect to system identified by oracle using 'mydb';
select * from dual@ttslink;
```
3.解压xtt文件，并调整
``` shell
unzip rman_xttconvert_v3.zip -d /home/oracle/xtt
cat xtt.properties #dbms_file_transfer 方式
#tablespaces/srcdir/dstdir 值需要大写
tablespaces=MYWORK,MYTEST
platformid=13
backupformat=/home/oracle/stage_source
backupondest=/home/oracle/stage_dest
getfileparallel=2
srcdir=SOURCEDIR
dstdir=DESTDIR
stageondest=/home/oracle/stage_dest
srclink=TTSLINK
#拷贝到目标端
scp -r /home/oracle/xtt 192.168.80.210:/home/oracle/

#参考
tablespaces=          ----需要迁移的表空间
platformid=           ----源 OS 平台 ID
dfcopydir=            ----源数据库备份文件存放目录
backupformat=         ---源数据库增备文件存放目录
stageondest=          ----目标据库备份文件存放目录
storageondest=        ----目标据库正式文件存放目录
backupondest=         ----目标据库增备文件存放目录
parallel=             ----备份，转化的并行度
rollparallel=         ----增备的并行度
getfileparallel=      ---- 使用 dbms_file_transfer 方式的并行度
```
#### 4.dbms_file_transfer方式初始化
1 生成相关文件
``` shell
export TMPDIR=/home/oracle/xtt
$ORACLE_HOME/perl/bin/perl xttdriver.pl -S
--参考
oracle@myasm xtt]$ cat getfile.sql 
0,SOURCEDIR,mytest.270.1025380271,DESTDIR,mytest_270_1025380271
0,SOURCEDIR,mytest.342.1033661031,DESTDIR,mytest_342_1033661031
1,SOURCEDIR,mywork.406.1052385811,DESTDIR,mywork_406_1052385811
[oracle@myasm xtt]$ cat xttnewdatafiles.txt      
::MYWORK
8,DESTDIR:/mywork.406.1052385811
::MYTEST
9,DESTDIR:/mytest.270.1025380271
10,DESTDIR:/mytest.342.1033661031
[oracle@myasm xtt]$ cat xttplan.txt 
MYWORK::::26814044
8
MYTEST::::26814044
9
10
```
2.拷贝
```
scp xttnewdatafiles.txt 192.168.80.210:/home/oracle/xtt/
scp getfile.sql 192.168.80.210:/home/oracle/xtt/
```
3.目标端执行
```
export TMPDIR=/home/oracle/xtt
$ORACLE_HOME/perl/bin/perl xttdriver.pl -G
```
#### 5.增量备份
> 增量备份会使数据库重启，如果是pdb，建议新创建一个实例，nomount状态即可，执行恢复，可参考现有实例 创建pfile
1.源端执行
```
$ORACLE_HOME/perl/bin/perl xttdriver.pl -i
--拷贝文件
scp `cat incrbackups.txt` 192.168.80.210:/home/oracle/stage_dest
scp xttplan.txt 192.168.80.210:/home/oracle/xtt
scp tsbkupmap.txt 192.168.80.210:/home/oracle/xtt
--验证/生成新的scan
$ORACLE_HOME/perl/bin/perl xttdriver.pl -s
```
2.目标端执行
```
--目标端转换，数据库实例会重启
$ORACLE_HOME/perl/bin/perl xttdriver.pl -r
```
3.新添加表空间，或数据文件
``` 
#按照以下部署执行
13 1 new datafiles added
=============================================================
============================================================
Running prepare cmd for new files my2021.337.106129557.(.*)
=============================================================
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Error:
------
The incremental backup was not taken as a datafile has been added to the tablespace:
 Please Do the following:
--------------------------
 1. Copy fixnewdf.txt from source to destination temp dir
 scp fixnewdf.txt 192.168.80.210:/home/oracle/xtt/
 2. On Destination, run $ORACLE_HOME/perl/bin/perl xttdriver.pl --fixnewdf
 3. Re-execute the incremental backup in source:
    $ORACLE_HOME/perl/bin/perl xttdriver.pl -i
 
    NOTE:  Before running incremental backup, delete FAILED in source temp dir or 
           run xttdriver.pl with -L option
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
```
#### 5.收尾
1.最后增量
``` sql
#源端
alter tablespace mywork read only;
alter tablespace mytest read only;
alter tablespace my2021 read only;
alter tablespace t2021 read only;
$ORACLE_HOME/perl/bin/perl xttdriver.pl -i
```
2.最后恢复
``` sql
#目标端
$ORACLE_HOME/perl/bin/perl xttdriver.pl -r
```
3.导入元数据
``` sql
#生成impdp
$ORACLE_HOME/perl/bin/perl xttdriver.pl -e
#创建相关目录
create directory TTS_DMP as '/home/oracle';
Grant read,write on directory TTS_DMP to system;
#创建用户，注意密码即将过期用户无法查到（open）
select 'create user '||name||' identified by values '''||password||''';' from user$ where name in ('MYWORK','MYTEST','MY2021','T2021');
#导入
impdp  system/oracle@mypdb directory=TTS_DMP logfile=tts2021_imp.log \
network_link=ttslink transport_full_check=no \
transport_tablespaces=MYWORK,MYTEST,MY2021,T2021 \
transport_datafiles='+DATA/TESTDB/9B872C593CBF32F8E053D808A8C0FF07/DATAFILE/mywork_406_1052385811','+DATA/TESTDB/9B872C593CBF32F8E053D808A8C0FF07/DATAFILE/mywork_339_1061286829','+DATA/TESTDB/9B872C593CBF32F8E053D808A8C0FF07/DATAFILE/mytest_270_1025380271','+DATA/TESTDB/9B872C593CBF32F8E053D808A8C0FF07/DATAFILE/mytest_342_1033661031','+DATA/TESTDB/9B872C593CBF32F8E053D808A8C0FF07/DATAFILE/my2021_338_1061286849','+DATA/TESTDB/9B872C593CBF32F8E053D808A8C0FF07/DATAFILE/my2021_337_1061295575','+DATA/TESTDB/9B872C593CBF32F8E053D808A8C0FF07/DATAFILE/t2021_336_1061295991'
#导入内容参考
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Fri Jan 8 13:21:02 2021 elapsed 0 00:08:56
--表空间改为读写
alter tablespace mywork read write;
alter tablespace mytest read write;
alter tablespace my2021 read write;
alter tablespace t2021 read write;
--赋权
select 'alter user '||username||' default tablespace '||default_tablespace||' TEMPORARY TABLESPACE '||TEMPORARY_TABLESPACE||';' from dba_users where username in ('MYWORK','MYTEST','MY2021','T2021');
select 'grant '||granted_role||' to '||grantee||';' from dba_role_privs where grantee in (select username from dba_users where  username  in('MYWORK','MYTEST','MY2021','T2021')); 
select 'grant '||PRIVILEGE||' to '||grantee||';' from DBA_SYS_PRIVS where grantee in (select username from dba_users where username  in('MYWORK','MYTEST','MY2021','T2021')); 
```
4.验证
``` sql
--验证
validate tablespace TS1, TS2 check logical;
--删除dblink
drop database link ttslink;
--取消块跟踪，根据rman备份情况
alter database disable block change tracking;
#根据要求收集统计信息，数据验证等相关工作
```

#### 附
1.nfs配置参考
```
/stage_source 192.168.8.0/24(rw,no_root_squash,no_all_squash,sync)
exportfs -r
service rpcbind start
service nfs start
mount -t nfs -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,vers=3,timeo=600 192.168.8.24:/stage_source /stage_dest
```
2.RMAN 初始化方式参考
```
#如果使用rman 备份，需要定义xtt.properties:dfcopydir,stageondest
#初始化
$ORACLE_HOME/perl/bin/perl xttdriver.pl -p
#挂在的nfs，无需拷贝
scp /home/oracle/stage_source/* 192.168.80.210:/home/oracle/stage_dest
scp /home/oracle/xtt/rmanconvert.cmd 192.168.80.210:/home/oracle/xtt
#转换开始，最终存储位置为storageondest，转换完成后，可以移除stageondest
$ORACLE_HOME/perl/bin/perl xttdriver.pl -c
```
**参考**
- 11G - Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 1389592.1)
- V4 Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 2471245.1)
- 12C - Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Note 2005729.1)
- https://www.xbdba.com/2019/04/04/xtts-migrate-11g-to-12c/