本文档主要内容，依据Oracle11g，介绍Oracle数据库重点功能，包含运维管理、性能分析、备份恢复、RAC、Dataguard、数据迁移等

版本变更

• 2021/02/20 ： v1.0 初始版本 — Firsouler

目录

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

常用sql不再描述

1.运维管理

1.1 表空间相关

--查看某个表空间数据文件信息
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 用户相关

--查询用户名、锁定状态、表空间、配置文件、权限---
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 表相关

--取行迁移超过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 资源限制

--设置空闲超过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资源限制

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 其他

--数据库创建时间，实例启动时间
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 等待相关

--等待事件个数大于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;

长事务

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';

会话相关

--统计活动、非活动会话
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

explain plan for ....;
select * from table(dbms_xplan.display);

autotrace

set autotrace on--跟踪实际执行计划，有物理读逻辑读
--or
alter session set statistics_level=all;
sql...
select * from table(dbms_xplan.display_cursor(null,null,'allstats +alias +outline'));

10046

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 优化器选择

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

--查看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无法登陆

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 空间及磁盘路径

--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相关视图

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属性的映射关系:

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 数据泵

数据泵迁移，建议根据用户进行，需要注意相关表空间、关联用户权限、统计信息等。

命令参考

--生成语句(用户创建语句、权限等可自行导出)，手动查询备用
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

参考官网文档

• https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL837

6.2 RMAN

使用rman迁移可分为两种情况，一次性停机迁移，另外就是初始迁移、增量恢复，初始迁移是将备份restore，增量恢复是recover。

一次性迁移

一次性迁移也可以分为落地和不落地，不落地方式主要业务完全停用，数据库唯一名需要更换。

--不落地迁移命令参考，更适合创建测试库
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 <

--初始化数据，也就是将初始的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 --可多通道提高速度,指定恢复时间或序列号

--取消相关参数
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及数据库相关参数配置

--检查字符集
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;

select file_name from dba_data_files where tablespace_name in ('MYWORK','MYTEST');

--源端
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';

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;

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 方式的并行度

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

scp xttnewdatafiles.txt 192.168.80.210:/home/oracle/xtt/
scp getfile.sql 192.168.80.210:/home/oracle/xtt/

export TMPDIR=/home/oracle/xtt
$ORACLE_HOME/perl/bin/perl xttdriver.pl -G

$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

--目标端转换，数据库实例会重启
$ORACLE_HOME/perl/bin/perl xttdriver.pl -r

#按照以下部署执行
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
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

#源端
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

#目标端
$ORACLE_HOME/perl/bin/perl xttdriver.pl -r

#生成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'));

--验证
validate tablespace TS1, TS2 check logical;
--删除dblink
drop database link ttslink;
--取消块跟踪，根据rman备份情况
alter database disable block change tracking;
#根据要求收集统计信息，数据验证等相关工作

/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

#如果使用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