Oracle---备份（backup）

##################################################################################
冷备份(offline backup) : shutdown 一停二拷三启动
mkdir -p /home/oracle/coldbk/

select 'cp -v '||name||' /home/oracle/coldbk/' from
(select value name from v$parameter where name='spfile'
union all
select name from v$controlfile
union all
select name from v$datafile
union all
select member from v$logfile);

vi /home/oracle/coldbk/bk.sh
----------------------------------------------------
export ORACLE_SID=orcl
sqlplus /nolog @/home/oracle/coldbk/shut.txt                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
cp -v /u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora /home/oracle/coldbk/
cp -v /u01/app/oracle/oradata/orcl/control01.ctl /home/oracle/coldbk/
cp -v /u01/app/oracle/fast_recovery_area/orcl/control02.ctl /home/oracle/coldbk/
cp -v /u01/app/oracle/oradata/orcl/system01.dbf /home/oracle/coldbk/
cp -v /u01/app/oracle/oradata/orcl/sysaux01.dbf /home/oracle/coldbk/
cp -v /u01/app/oracle/oradata/orcl/undotbs01.dbf /home/oracle/coldbk/
cp -v /u01/app/oracle/oradata/orcl/users01.dbf /home/oracle/coldbk/
cp -v /u01/app/oracle/oradata/orcl/example01.dbf /home/oracle/coldbk/
cp -v /u01/app/oracle/oradata/orcl/redo03.log /home/oracle/coldbk/
cp -v /u01/app/oracle/oradata/orcl/redo02.log /home/oracle/coldbk/
cp -v /u01/app/oracle/oradata/orcl/redo01.log /home/oracle/coldbk/
sqlplus /nolog @/home/oracle/coldbk/start.txt
----------------------------------------------------
chmod +x /home/oracle/coldbk/bk.sh

vi /home/oracle/coldbk/shut.txt
-------------------------------
conn / as sysdba
shutdown immediate
exit
-------------------------------

vi /home/oracle/coldbk/start.txt
-------------------------------
conn / as sysdba
startup
exit
-------------------------------

测试备份脚本可行性
/home/oracle/coldbk/bk.sh

crontab -e
30 23 * * * /home/oracle/coldbk/bk.sh
##################################################################################
冷备份的异地还原：
scp /home/oracle/coldbk/* oracle@oracle2:/home/oracle/db01/
ssh oracle@oracle2
cd /home/oracle/db01/
cp orapwdb01 spfiledb01.ora $ORACLE_HOME/dbs
strings spfiledb01.ora
mkdir -p /u01/app/oracle/admin/orcl/adump
mkdir -p /u01/app/oracle/fast_recovery_area
export ORACLE_SID=db01
sqlplus / as sysdba
startup nomount
alter system set control_files=
'/home/oracle/db01/control01.ctl',
'/home/oracle/db01/control02.ctl'
scope=spfile;
startup force mount
修改数据文件、临时文件、联机日志文件的指针
select * from
(select name from v$datafile
union all
select name from v$tempfile
union all
select member from v$logfile);

set lines 300
set pages 300
select 'alter database rename file '||chr(39)||name||chr(39)||' to '||chr(39)||'/home/oracle/db01/'||substr(name,instr(name,'/',-1)+1)||chr(39)||';'
from
(select name from v$datafile
union all
select name from v$tempfile
union all
select member from v$logfile);

alter database open;
##################################################################################
vi /home/oracle/coldbk/bk.sh
-----------------------------------------------------
export ORACLE_SID=db01
sqlplus /nolog @/home/oracle/coldbk/closed_backup.sql
-----------------------------------------------------
chmod +x /home/oracle/coldbk/bk.sh

vi /home/oracle/coldbk/closed_backup.sql
---------------------------------------------------------------------
conn / as sysdba
set feedback off heading off verify off trimspool on timing off
set pagesize 0 linesize 300

define dir = '/home/oracle/coldbk/'
define bks = '/home/oracle/coldbk/offline_backup.sql'
define cpy = 'cp -v'

prompt *** Spooling script &bks ***

spool &bks replace
select '!&cpy '||value ||' &dir' from v$parameter where name='spfile';
select '!&cpy '||name  ||' &dir' from v$controlfile;
select '!&cpy '||name  ||' &dir' from v$datafile;
select '!&cpy '||member||' &dir' from v$logfile;
spool off

shutdown immediate
@&bks
startup
exit
---------------------------------------------------------------------
##################################################################################
热备份(online backup):必须运行在归档模式下
mkdir -p /home/oracle/hotbk

archive log list
shut immediate
startup mount
alter database archivelog;
alter database open;

热备份用户表空间：users
select owner,table_name from dba_tables where tablespace_name='USERS';

select count(*) from scott.ob1;

  COUNT(*)
----------
    173970

select file_id,file_name from dba_data_files where tablespace_name='USERS';

   FILE_ID
----------
FILE_NAME
----------------------------------------
     4
/u01/app/oracle/oradata/orcl/users01.dbf

进入备份状态：
alter tablespace users begin backup;

SYS> select * from v$backup;

     FILE# STATUS                   CHANGE# TIME
---------- ------------------------------------ ---------- ------------
     1 NOT ACTIVE                     0
     2 NOT ACTIVE                     0
     3 NOT ACTIVE                     0
     4 ACTIVE                   1511288 30-MAR-17
     5 NOT ACTIVE                     0

cp -v /u01/app/oracle/oradata/orcl/users01.dbf /home/oracle/hotbk/

结束备份状态：
alter tablespace users end backup;

insert into ob1 select * from ob1 where rownum<20001;
commit;

SCOTT> select count(*) from ob1;

  COUNT(*)
----------
    220000

模拟users01.dbf文件丢失:
rm -f /u01/app/oracle/oradata/orcl/users01.dbf

alter tablespace users offline;
还原数据文件：将备份的容器拷贝回控制文件指针指向的位置
cp -v /home/oracle/hotbk/users01.dbf /u01/app/oracle/oradata/orcl/users01.dbf
恢复数据文件：使用日志对备份的文件进行交易的重现
recover datafile 4;
-->auto

alter tablespace users online;

SCOTT> select count(*) from ob1;

  COUNT(*)
----------
    210000
##################################################################################
无备份的还原和恢复：
create tablespace data01 datafile '/home/oracle/data01.dbf' size 10m;
alter system checkpoint;
alter system switch logfile;
create table scott.t08 tablespace data01 as select * from scott.emp;
insert into scott.t08 select * from scott.t08;
commit;
alter system checkpoint;
alter system switch logfile;
模拟数据文件丢失
!rm -f /home/oracle/data01.dbf

SYS> select count(*) from scott.t08;

  COUNT(*)
----------
    115276

startup force
alter database datafile 6 offline;
alter database open;
从控制文件获得6号数据文件头的信息，格式化崭新的6号文件
alter database create datafile 6 as '/u01/app/oracle/oradata/orcl/data01.dbf';
恢复数据文件
recover datafile 6;
alter database datafile 6 online;
校验数据：
select count(*) from scott.t08;

校验无备份的还原和恢复手段是否可以使用：
1.数据文件的创建时间
SYS> select file#,to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATION_TIME from v$datafile order by 2;

     FILE# CREATION_TIME
---------- --------------------------------------
     1 2013-08-24 11:37:33
     2 2013-08-24 11:37:37
     4 2013-08-24 11:37:49
     3 2013-08-24 12:07:19
     5 2015-08-24 05:33:59
     6 2017-03-31 10:06:14

6 rows selected.

2.归档保留的历史
select sequence#,name,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') from v$archived_log;
##################################################################################
热备份所有数据文件的脚本：
vi /home/oracle/hotbk/open_backup.sql
----------------------------------------------------------------
conn / as sysdba
set feedback off heading off verify off timing off trimspool on
set pagesize 0 linesize 300

define dir = '/home/oracle/hotbk/'
define bks = '/home/oracle/hotbk/hot_backup.sql'
define cpy = '!cp -v'

prompt *** Spooling script &bks ***

spool &bks replace
select '&cpy '||value ||' &dir' from v$parameter where name='spfile';

select 'alter database backup controlfile to '||chr(39)||'&dir'||'control01.ctl'||chr(39)||';' from dual;

select 'alter tablespace '||tablespace_name||' begin backup;'||chr(10)||
       '!cp -v '||file_name||' /home/oracle/hotbk/'||chr(10)||
       'alter tablespace '||tablespace_name||' end backup;'
from dba_data_files;
spool off
@&bks
exit
----------------------------------------------------------------

vi /home/oracle/hotbk/hotbk.sh
-----------------------------------------------------
export ORACLE_SID=orcl
sqlplus /nolog @/home/oracle/hotbk/open_backup.sql
-----------------------------------------------------
chmod +x /home/oracle/hotbk/hotbk.sh
##################################################################################
不完全恢复：将数据库恢复到历史的一点！
alter system checkpoint;
alter system switch logfile;

1.要有所有数据文件的备份

2.确定灾难产生时间（log miner）：在生产库做
select sequence#,name from v$archived_log;

exec dbms_logmnr.add_logfile('/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_06_27/o1_mf_1_35_do3hw53f_.arc',dbms_logmnr.new);
exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
select scn,sql_redo from v$logmnr_contents
where lower(sql_redo) like 'drop%'
and seg_name='E01'
and seg_owner='SCOTT';

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
create or replace procedure logminer
(p_sequence# number,
p_obname varchar2,
p_owner varchar2)
is
  v_logname varchar2(520);
  v_scn number;
begin
  select name into v_logname from v$archived_log where sequence#=p_sequence#;
  dbms_logmnr.add_logfile(v_logname,dbms_logmnr.new);
  dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
  select scn into v_scn from v$logmnr_contents
  where lower(sql_redo) like 'create%'
  and seg_name=p_obname
  and seg_owner=p_owner;
  dbms_output.put_line('DROP SCN: '||v_scn);
exception
  when no_data_found then
    dbms_output.put_line('drop operation not found!');
end logminer;
/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
scn：1590033 drop table scott.ob1 purge;

SYS> select scn_to_timestamp(1590033) from dual;

SCN_TO_TIMESTAMP(1590033)
-------------------------------
31-MAR-17 11.35.56.000000000 AM

3.在备用节点还原所有的数据文件、参数文件
在备用节点创建目录：
mkdir -p /u01/app/oracle/oradata/orcl/
将备份的数据文件还原到备用节点
scp *.dbf oracle@install0:/u01/app/oracle/oradata/orcl/

scp /u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora oracle@install0:/u01/app/oracle/product/12.0.1/db_1/dbs

准备相关目录
strings spfileorcl.ora

mkdir -p /u01/app/oracle/admin/orcl/adump
mkdir -p /u01/app/oracle/oradata/orcl
mkdir -p /u01/app/oracle/fast_recovery_area/orcl/
mkdir -p /u01/app/oracle/fast_recovery_area

启动实例到nomount
export ORACLE_SID=orcl
sqlplus / as sysdba
startup nomount

4.创建控制文件
create controlfile reuse database 'orcl' archivelog resetlogs
datafile
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf',
'/u01/app/oracle/oradata/orcl/data01.dbf'
logfile
group 1 '/u01/app/oracle/oradata/orcl/redo01.log' size 50m,
group 2 '/u01/app/oracle/oradata/orcl/redo02.log' size 50m;

5.恢复数据库到灾难产生的时间
recover database using backup controlfile until change 1590033;

6.拷贝所需要的归档日志

7.完成恢复，使用resetlogs方式打开数据库
alter database open resetlogs;

8.exp/imp

recover--> 1073986865

select file#,checkpoint_change#,last_change# from v$datafile;
1073986865
select file#,checkpoint_change#,fuzzy from v$datafile_header;

select * from v$recover_file;
##############################################################################
闪回数据库:
SYS> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

alter database flashback on;
查看闪回支持的时间长度
show parameter db_flashback_retention_target=1440
查看可以闪回的最小scn
SELECT OLDEST_FLASHBACK_SCN FROM V$FLASHBACK_DATABASE_LOG;

模拟误操作
确定灾难产生时间
闪回数据库
shut immediate
startup mount
flashback database to scn 2260602;
使用制度模式打开数据库，找回误操作
alter database open read only;
重新启动数据库到mount
shut immediate
startup mount
进行恢复
recover database;
打开数据库
alter database open;
##################################################################################
热备份控制文件：
alter database backup controlfile to '/home/oracle/hotbk/control01.ctl';

alter database backup controlfile to trace as '/home/oracle/crectl.sql';

alter system checkpoint;
alter system switch logfile;

还原controlfile
cp -v /home/oracle/hotbk/control01.ctl --> control_files
恢复controlfile
recover database using backup controlfile;
alter database open resetlogs;

##################################################################################
第一个尝试：使用隐含参数，在数据库损坏的情况下强制resetlogs
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
shut immediate
startup mount
select open_resetlogs from v$database;
--recover database using backup controlfile until cancel;
alter database open resetlogs;

如果出现ORA-00600 [2662]:需要增进SCN
在oracle10g之后增进SCN需要打开调试错误的参数
alter system set "_allow_error_simulation"=true scope=spfile;
重新启动数据库，使参数生效
shut immediate
startup mount
使用oracle的工具oradebug增进SCN
oradebug setmypid
oradebug dumpvar sga kcsgscn_
oradebug poke 0x06001AE70 4 0x40000000
select open_resetlogs from v$database;
alter database open [resetlogs];

将有问题的undo关掉：
alter system set fast_start_parallel_rollback=FALSE scope=spfile;
alter system set undo_management=manual scope=spfile;
删除有问题的undo
drop tablespace undotbs1 including contents and datafiles;
ORA-01548: active rollback segment '_SYSSMU8_517538920$' found, terminate dropping tablespace
查找隐含参数
select ksppinm from x$ksppi where ksppinm like '%rollback%';
利用隐含参数将有问题的rollback segment离线
alter system set "_corrupted_rollback_segments"=true scope=spfile;
alter system set "_offline_rollback_segments"='_SYSSMU8_517538920$' scope=spfile;
drop tablespace undotbs1 including contents and datafiles;
create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' size 50m;
alter system set undo_management=auto scope=spfile;
##################################################################################
create table scott.t02 tablespace data01 as select * from scott.emp;
alter system checkpoint;
alter system switch logfile;

create table tom.testtab tablespace data01 as select * from all_objects;
alter system checkpoint;
alter system switch logfile;

insert into scott.t02 select * from scott.t02;
/
commit;
alter system checkpoint;
alter system switch logfile;

select count(*) from scott.t02;
select count(*) from tom.testtab;

drop tablespace data01 including contents and datafiles;
alter system checkpoint;
alter system switch logfile;

确定灾难产生时间：
exec dbms_logmnr.add_logfile('/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_04_01/o1_mf_1_6_dfygd6n4_.arc',dbms_logmnr.new);
exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
select scn,sql_redo from v$logmnr_contents
where lower(sql_redo) like 'drop%'
and seg_owner='SCOTT';
select scn,sql_redo from v$logmnr_contents
where lower(sql_redo) like 'drop%'
and seg_owner='TOM';

min scn: 1685494

在备用节点还原全库:
recover database using backup controlfile until change 1685494;
##################################################################################
恢复管理器（recover manager）: rman
1.登录rman
rman
RMAN> connect target /

rman target /

2.查看数据库的物理信息
RMAN> report schema;

3.使用rman对控制文件做镜像备份
RMAN> copy current controlfile to '/home/oracle/rmanbk/control01.ctl';

4.使用rman查看控制文件的镜像备份
RMAN> list copy of controlfile;

alter system checkpoint;
alter system switch logfile;
模拟控制文件丢失
rm -f

5.使用rman还原控制文件
RMAN> restore controlfile from '/home/oracle/rmanbk/control01.ctl';

6.使用rman装载数据库
RMAN> alter database mount;

7.使用rman恢复数据库
RMAN> recover database;

8.使用resetlogs方式打开数据库
RMAN> alter database open <resetlogs>;

9.将备份信息重新注册到控制文件
RMAN> catalog start with '/home/oracle/rmanbk/control01.ctl';
RMAN> list copy of controlfile;

10.使用rman镜像备份数据文件
RMAN> report schema;
RMAN> copy datafile 4 to '/home/oracle/rmanbk/users01.dbf';

11.查看镜像备份的数据文件
RMAN> list copy of datafile 4;

alter system checkpoint;
alter system switch logfile;
模拟4号文件丢失
rm -f /u01/app/oracle/oradata/orcl/users01.dbf

select * from v$recover_file;
select * from v$recovery_log;

12.还原4号文件
RMAN> restore datafile 4;

13.恢复4号文件
RMAN> recover datafile 4;

使用rman自动处理失效：11g新特性
RMAN> list failure;
RMAN> advise failure;
RMAN> repair failure;

14.在rman中使用交叉检测命令，确定备份文件的有效性
RMAN> list copy;
RMAN> crosscheck copy;
RMAN> list copy;

15.删除过期拷贝
RMAN> delete noprompt expired copy;

*rman的特征备份格式：备份集 --> 备份片

16.使用备份集备份spfile：
RMAN> backup spfile format '/home/oracle/rmanbk/spfile.bkp';

17.查看包含spfile的备份集
RMAN> list backup of spfile;

模拟spfile丢失：
show parameter spfile
rm -f

18.使用rman还原参数文件
RMAN> startup nomount
RMAN> restore spfile from '/home/oracle/rmanbk/spfile.bkp';
startup force

19.使用rman备份集备份数据文件
RMAN> report schema;
RMAN> backup datafile 5;
RMAN> list backup of datafile 5;
RMAN> backup as compressed backupset datafile 5;
RMAN> list backup of datafile 5;
将压缩风格写入参数
RMAN> configure device type disk backup type to compressed backupset;

alter system checkpoint;
alter system switch logfile;
模拟文件丢失
rm -f /u01/app/oracle/oradata/orcl/example01.dbf

20.使用rman语句块还原和恢复数据文件
run{               
restore datafile 5;
recover datafile 5;
sql 'alter database datafile 5 online';}

21.校验数据文件中的坏块：
RMAN> backup validate datafile 5;
select FILE#,BLOCK# from v$database_block_corruption;

修复单独的怀块
RMAN> blockrecover datafile 5 block 451 from tag = TAG20170407T134841;
修复v$database_block_corruption枚举的所有怀块
RMAN> blockrecover corruption list;
查找一个8k属于哪一个段
select owner,segment_name,segment_type from dba_extents where 451 between block_id and block_id+blocks-1 and file_id=5;

22.修改备份片保存的位置
configure channel device type disk format '/home/oracle/rmanbk/%d_%I_%s_%p_%T_%t.bkp';

23.使用rman备份集备份表空间
RMAN> backup tablespace users;
RMAN> list backup of tablespace users;
还原和恢复表空间
run{               
restore tablespace users;
recover tablespace users;
}

向新的位置恢复数据文件
run{
sql 'alter tablespace users offline';
set newname for datafile 4 to '/home/oracle/users01.dbf';           
restore tablespace users;
switch datafile 4;
recover tablespace users;
sql 'alter tablespace users online';
}

23.使用rman备份全库
RMAN> backup as compressed backupset database plus archivelog;

先使用dummy伪实例还原参数文件，再启动真实例
RMAN> restore spfile from '/home/oracle/test/ORCL_1416338340_10_1_20170410_940932731.bkp';

RMAN> restore spfile to '/home/oracle/test/spfileorcl2.ora' from '/home/oracle/test/ORCL_1416338340_10_1_20170410_940932731.bkp';

使用真实例还原控制文件：还原之前是否需要修改control_files?
RMAN> restore controlfile from '/home/oracle/test/ORCL_1416338340_10_1_20170410_940932731.bkp';

装载数据库：
RMAN> alter database mount;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 04/10/2017 10:39:44
ORA-01102: cannot mount database in EXCLUSIVE mode

SQL> alter system set db_unique_name=orcl2 scope=spfile;
SQL> startup force mount

还原数据库：
RMAN> report schema;
RMAN> list backup;
RMAN> crosscheck backup;
RMAN> delete noprompt expired backupset;
将目录下所有备份片的信息重新注册到控制文件
RMAN> catalog start with '/home/oracle/test/';
RMAN> list backup;

向新的位置还原数据库：
run{
set newname for datafile 1 to '/u01/app/oracle/oradata/orcl2/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/orcl2/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/orcl2/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/orcl2/users01.dbf';
set newname for datafile 5 to '/u01/app/oracle/oradata/orcl2/example01.dbf';
set newname for datafile 6 to '/u01/app/oracle/oradata/orcl2/teacher01.dbf';
set newname for tempfile 1 to '/u01/app/oracle/oradata/orcl2/temp01.dbf';
restore database;
switch datafile all;
switch tempfile all;
recover database;
}

回sqlplus修改联机日志文件的路径
sqlplus / as sysdba
SQL> alter database rename file '/home/oracle/coldbk/redo01.log' to '/u01/app/oracle/oradata/orcl2/redo01.log';
SQL> alter database rename file '/home/oracle/coldbk/redo02.log' to '/u01/app/oracle/oradata/orcl2/redo02.log';
SQL> alter database rename file '/home/oracle/coldbk/redo03.log' to '/u01/app/oracle/oradata/orcl2/redo03.log';
SQL> alter database open resetlogs;

RMAN> list archivelog all;
RMAN> list copy;
SYS> archive log list
SYS> show parameter DB_RECOVERY_FILE_DEST

解决845错误：
SQL> startup nomount
ORA-00845: MEMORY_TARGET not supported on this system

su - root
vi /etc/fstab
--------------------------------------------------------------------------
tmpfs                   /dev/shm                tmpfs   size=2g        0 0
--------------------------------------------------------------------------
mount -o remount /dev/shm
==============================================================================
cd $ORACLE_HOME/assistants/dbca/templates/
Seed_Database.ctl --> 控制文件
Seed_Database.dfb --> 全库的压缩备份片

用备份还原一个叫 crm 的数据库！

修改数据库名：
shut immediate
startup mount
nid target=sys/oracle dbname=crm
startup nomount
alter system set db_name=crm scope=spfile;
startup force mount
alter database open resetlogs;

修改字符集：
shutdown immediate
startup mount
alter system enable restricted session;
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
alter database open;

alter database character set internal_convert zhs16gbk;
alter database national character set internal_convert al16utf16;
==============================================================================
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

declare
  devtype varchar2(256);
  done boolean;
begin
  devtype :=dbms_backup_restore.deviceallocate(type=>'',ident=>'c1');
  dbms_backup_restore.restoresetdatafile;
  dbms_backup_restore.restoredatafileto(dfnumber=>1,toname=>'/u01/app/oracle/oradata/orcl2/system01.dbf');
  dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/home/oracle/rmanbk/ORCL_1416338340_8_1_20170410_940934644.bkp');
  dbms_backup_restore.devicedeallocate;
end;
/
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
rman参数调整：
控制文件自动备份
select ksppinm,indx from x$ksppi where ksppinm like '%controlfile%';
_controlfile_autobackup_delay 1323

select KSPPSTDVL from x$ksppcv where indx=1323;
_controlfile_autobackup_delay --> 300妙

alter system set "_controlfile_autobackup_delay"=0 SCOPE=SPFILE;

CONFIGURE CONTROLFILE AUTOBACKUP ON;
每当使用rman做过备份或者数据库物理结构有变化，控制文件都回自动备份一份！

控制备份片大小:
run{
allocate channel c1 type disk maxpiecesize=10m;
backup tablespace users format '/home/oracle/rmanbk/%d_%I_%s_%p_%T.bkp';
}

控制备份集中数据文件的数量:
run{
allocate channel c1 type disk maxpiecesize=100m;
backup as compressed backupset database format '/home/oracle/rmanbk/%d_%I_%s_%p_%T_%t.bkp' filesperset=3;
backup format '/home/oracle/rmanbk/%d_%I_%e_%s_%p_%T_%t.bkp' archivelog all delete input;
release channel c1;
}

run{
allocate channel c1 type disk maxpiecesize=100m;
backup as compressed backupset database not backed up format '/home/oracle/rmanbk/%d_%I_%s_%p_%T_%t.bkp' filesperset=3;
backup format '/home/oracle/rmanbk/%d_%I_%e_%s_%p_%T_%t.bkp' archivelog all delete input;
release channel c1;
}

增量备份:(0 ~ 4)
backup incremental level 0 tablespace users;
backup incremental level 2 tablespace users;
backup incremental level 2 cumulative tablespace users;
backup incremental level 1 tablespace users;

alter system checkpoint;
alter system switch logfile;

run{
sql 'alter tablespace users offline';
restore tablespace users;
recover tablespace users;
sql 'alter tablespace users online';
}

查询是否启用块跟踪
col FILENAME for a40
select filename,status,bytes from v$block_change_tracking;
启用块跟踪
alter database enable block change tracking using file '/home/oracle/block_track/orcl_block_track.log';
禁用块跟踪
alter database disable block change tracking;
修改块修改跟踪文件路径:
mount
目标文件要存在
alter database rename file '///oldfile' to '///newfile';

并行备份
run{
allocate channel c1 type disk format '/home/oracle/rmanbk/%d_%I_%s_%p_%T.bkp';
allocate channel c2 type disk format '/home/oracle/rmanbk/%d_%I_%s_%p_%T.bkp';
backup
(datafile 4 channel c1)
(datafile 5 channel c2);
release channel c1;
release channel c2;
}

只能拥有一个数据文件，文件可以管理4G个8K：
create bigfile tablespace tbs5 datafile '/home/oracle/tbs05.dbf' size 10m;

11g并行备份单个文件：
run{
allocate channel c1 type disk format '/home/oracle/rmanbk/%d_%I_%s_%p_%T.bkp';
allocate channel c2 type disk format '/home/oracle/rmanbk/%d_%I_%s_%p_%T.bkp';
backup section size 100m datafile 1;
release channel c1;
release channel c2;
}
-------------------------------------------------------------------------------
清理镜像备份:
delete controlfilecopy 6;
delete datafilecopy 7;
delete archivelog sequence 48;
delete copy;
delete expired copy;
delete [noprompt] copy;
清理备份集:
delete backupset;
delete expired backupset;
delete backupset ##;
delete [noprompt] backupset ##;
查看陈旧备份:
按冗余度制定保留策略
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
report obsolete;
report obsolete redundancy=3;
delete obsolete;
delete obsolete redundancy=3;
delete [noprompt] obsolete;
按时间制定保留策略
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
DELETE OBSOLETE RECOVERY WINDOW OF 30 DAYS;
还原保留策略：
CONFIGURE RETENTION POLICY CLEAR;

在一个备份集中生成两个相同的备份片
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
目录是一个要使用c%
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/home/oracle/rmanbk1/%d_%I_%s_%p_%c_%T.bkp';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/rmanbk1/%d_%I_%s_%p_%T.bkp','/home/oracle/rmanbk2/%d_%I_%s_%p_%T.bkp';

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK CLEAR;

限制备份集所备份内容的大小：原始文件超700m将无法备份
CONFIGURE MAXSETSIZE TO 700m;
CONFIGURE MAXSETSIZE CLEAR;

备份集加密：
CONFIGURE ENCRYPTION FOR DATABASE ON;
RMAN> set encryption identified by 'uplooking' only;
RMAN> backup tablespace tbs1;
RMAN> set decryption identified by 'uplooking';
RMAN> restore tablespace tbs1;
RMAN> recover tablespace tbs1;

归档删除策略：
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DEVICE TYPE DISK;

查看数据库有哪些文件失效
LIST FAILURE;
对修复数据库给出建议，
ADVISE FAILURE;
查看修复脚本
vi /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_818686817.hm
执行修复脚本：
REPAIR FAILURE;

块级别修复损坏：
vi -b /u01/app/oracle/oradata/orcl/tbs01.dbf

恢复目录数据库(catalog database):是保存备份信息的一个独立的数据库！
sqlplus sys/oracle@stu4 as sysdba
create tablespace tbsrman datafile '/u01/app/oracle/oradata/orcl/tbsrman01.dbf' size 200m autoextend on;

grant recovery_catalog_owner,resource to test identified by test;

rman catalog test/test@stu4
RMAN> create catalog;
RMAN> exit
rman target / catalog test/test@stu4
RMAN> register database;
RMAN> unregister database;

创建备份脚本
create script b0 {
backup incremental level 0 tablespace users;
}

create script b1 {
backup incremental level 1 tablespace users;
}

显示所有备份脚本名字
RMAN> list script names;
打印指定的脚本内容
RMAN> print script b0;
替换脚本
replace script b0 {
backup incremental level 0 tablespace users;
}
运行脚本
run{execute script b0;}
删除脚本
RMAN> delete script b0;

run{
set until scn 2259297;
restore database;
recover database;
alter database open resetlogs;
}
###################################################################################
--/home/oracle/1.ctl--
LOAD DATA
INFILE '/home/oracle/e.txt'
truncate INTO TABLE e02
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(empno,ename,job,mgr,hiredate date 'yyyy-mm-dd hh24:mi:ss',sal,comm,deptno)
--------------------------
常规装载：
sqlldr scott/tiger control=/home/oracle/1.ctl bad=/home/oracle/bad.txt log=/home/oracle/sqlldr.log

show parameter utl_file_dir='/home/oracle'

declare
  v_filehandle UTL_FILE.FILE_TYPE;
begin
  v_filehandle:=utl_file.fopen('/home/oracle','1.txt','w');
    UTL_FILE.PUTF (v_filehandle,'This emp detail information，export time : %s\n', current_timestamp);
    UTL_FILE.NEW_LINE (v_filehandle);
  for i in (select * from scott.e01)
  loop
    UTL_FILE.PUTF (v_filehandle, '%s,%s,%s,%s,%s,',i.EMPNO,i.ENAME,i.JOB,i.MGR,to_char(i.HIREDATE,'yyyy-mm-dd hh24:mi:ss'));
    UTL_FILE.PUTF (v_filehandle, '%s,%s,%s\n',i.SAL,i.COMM,i.DEPTNO);
  end loop;
    UTL_FILE.FCLOSE (v_filehandle);
end;
/

直接路径装载（insert append）
sqlldr scott/tiger control=/home/oracle/1.ctl direct=true bad=/home/oracle/bad.txt log=/home/oracle/sqlldr.log