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(*)
-----