[ORA11g] Online Hot Backup - ver.dBRang

/******************************************************************************************************
-- Title : [ORA11g] Online Hot Backup - ver.dBRang
-- Reference : study group 김0오
-- Key word : oracle 오라클 온라인 핫 백업 온라인 백업 핫백업 온라인백업
******************************************************************************************************/

/********************************************

-- Preparing Backup

********************************************/

-- 1. 로그시위치 및 begin backup

alter system switch logfile;

alter database begin backup;

       

-- 2. Checking Parameter & Control Files

show parameter pfile;                                               /*pfile by show parameter*/

select name, value from v$parameter where name = 'spfile';          /*spfile by v$parameter*/

select name, value from v$spparameter where name = 'control_files'; /*control file by v$spparameter*/

select name from v$controlfile;                                     /*control file by v$controlfile*/

-- 3. ▶ Extraction Parameter backup script

select name, 'cp -rfv ' || value || ' /BACKUP/hot_backup/' ||  file_nm || '_' 

       || to_char(sysdate,'yyyymmdd')  "parameter_file script"

from 

(

    select name, value, SUBSTR(REVERSE(SUBSTR(REVERSE(value),1,instr(REVERSE(value),'/',1))),2) as file_nm

    from v$parameter where name = 'spfile'

) a;

-- 4. 테이블스페이스 확인

select tablespace_name

     , replace(file_name, file_nm, '') "path_nm"

     , file_nm

     , bytes, online_status

from 

(

    select a.tablespace_name, b.file_name, b.bytes, b.online_status

         , SUBSTR(REVERSE(SUBSTR(REVERSE(file_name),1,instr(REVERSE(file_name),'/',1))),2) as file_nm

    from dba_tablespaces a

    inner join dba_data_files b

    on a.tablespace_name = b.tablespace_name

) aa

order by case when tablespace_name in ('SYSTEM','SYSAUX','USERS')

                   or tablespace_name like '%UNDOTBS%'

              then 1

              else 999

         end

       , tablespace_name;

-- 5. ▶ 백업 스크립트 추출

select tablespace_name

     , 'cd ' || replace(file_name, file_nm, '') || ' && ' ||

       'tar zcvf /BACKUP/hot_backup/' || tablespace_name || '_backup_'|| to_char(sysdate,'yyyymmdd') || '.tar.gz ./' || file_nm

       as "tar.gz_script"

     , file_nm

     , bytes

     , TO_CHAR(BYTES/1024/1000, '999,990.00') AS FILE_SIZE_MB

     , online_status

from 

(

    select a.tablespace_name, b.file_name, b.bytes, b.online_status

         , SUBSTR(REVERSE(SUBSTR(REVERSE(file_name),1,instr(REVERSE(file_name),'/',1))),2) as file_nm

    from dba_tablespaces a

    inner join dba_data_files b

    on a.tablespace_name = b.tablespace_name

) aa

order by case when tablespace_name in ('SYSTEM','SYSAUX','USERS')

                   or tablespace_name like '%UNDOTBS%'

              then 1

              else 999

         end

       , tablespace_name;

/********************************************

-- Execution Backup

********************************************/

-- log switching and begin backup

alter system switch logfile;

alter database begin backup;

-- check begin backup status
select a.tablespace_name,a.file_name,a.file_id,b.status /* Active: begin backup, No Active: Nothing */
from sys.dba_data_files a ,v$backup b where b.file# = a.file_id; 

    -- backup parameter and control files(using 3.)

    $ cp -rfv /home/oracle/oracle/11g/dbs/spfileINFRAORA.ora /BACKUP/hot_backup/spfileINFRAORA.ora_20140721

    -- control file/trace backup

    alter database backup controlfile to '/BACKUP/hot_backup/control01.ctl_20140721';

    alter database backup controlfile to trace as '/BACKUP/hot_backup/control01.sql_20140721';

    

    -- backup tablespaces(using 5.)

    cd /home/oracle/oracle/oradata/INFRAORA/ && tar zcvf /BACKUP/hot_backup/SYSAUX_backup_20140721.tar.gz ./sysaux01.dbf

    cd /home/oracle/oracle/oradata/INFRAORA/ && tar zcvf /BACKUP/hot_backup/SYSTEM_backup_20140721.tar.gz ./system01.dbf

    cd /home/oracle/oracle/oradata/INFRAORA/ && tar zcvf /BACKUP/hot_backup/UNDOTBS1_backup_20140721.tar.gz ./undotbs01.dbf

    cd /home/oracle/oracle/oradata/INFRAORA/ && tar zcvf /BACKUP/hot_backup/USERS_backup_20140721.tar.gz ./users01.dbf

    cd /home/oracle/oracle/oradata/INFRAORA/ && tar zcvf /BACKUP/hot_backup/EXAMPLE_backup_20140721.tar.gz ./example01.dbf

    cd /home/oracle/oracle/oradata/OIDB/ && tar zcvf /BACKUP/hot_backup/TBS_TTT_backup_20140721.tar.gz ./tbs_ttt.dbf

    

-- eng backup

alter database end backup;

/********************************************

-- Archive files Backup

********************************************/

-- find archive file greated in 3-day before.

$ find /ARCHIVE/arc*.arc -ctime -3 > /tmp/archive.list

$ tar zcvf /BACKUP/hot_backup/archivefiles.backup_20140721.tar.gz --files-from /tmp/archive.list