Performing Database Backups

rman backup  =>  backup as compressed backupset database

userManaged backup=> 파일 copy를 이용.

하드웨어를 바꿀때 의미.

미러 DB.

 

userManaged backup 절차

[root@orcl oracle]# mkdir /backup

[root@orcl oracle]# chown oracle:oinstall /backup

S SYS> spool /backup/fileList

S SYS> select name from v$datafile;

NAME

--------------------------------------------------------------------------------            

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_50sxqqv0_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_52d4tltd_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_50sxqqvn_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_50sxqr22_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_50sxt0j6_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519t7p0h_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519tbfc4_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_5316d4z3_.dbf

/home/oracle/disk2/ORCL/datafile/myts_reorg0.dbf

9 rows selected.

S SYS> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------  

/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_50sxs92d_.ctl

/u01/app/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_50sxs97m_.ctl

S SYS> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------      

/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_50sxscov_.log

/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_50sxsdc7_.log

/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_50sxsbt9_.log

/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_50sxscdm_.log

/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_50sxs9rf_.log

/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_1_50sxsbgh_.log

6 rows selected.

S SYS> spool off

[oracle@orcl ~]$ cd /backup

[oracle@orcl backup]$ ls

fileList.lst

[oracle@orcl backup]$ cp fileList.lst fileCopy.sh

[oracle@orcl backup]$ chmod 770 fileCopy.sh

[oracle@orcl backup]$ vi fileCopy.sh

띄워쓰기 제거 후.

cp ~~~~~~ /back 를 붙여준다.   ex) :%s/\n/ \/back\r cp /g

sh  파일 실행후

S SYS> create pfile='/backup/initorcl.ora' from spfile;

 

 

 

--- 1. 고장내기

 

S SYS> select ts# from v$tablespace where name='TS1';

       TS#

----------

         7

S SYS> select name from v$datafile where ts#=7;

NAME

-----------------------------------------------------------------------------------------------

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519t7p0h_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519tbfc4_.dbf

S SYS> !rm /u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519t7p0h_.dbf

S SYS> !rm /u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519tbfc4_.dbf

S SYS> shutdown abort

ORACLE instance shut down.

S SYS> startup

ORACLE instance started.

Total System Global Area  268435456 bytes

Fixed Size                  1218920 bytes

Variable Size             201328280 bytes

Database Buffers           62914560 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519t7p0h_.dbf'

-- 2. restore

 

[oracle@orcl ~]$ cd /backup

 

[oracle@orcl backup]$ cat fileCopy.sh | grep o1_mf_ts1_519t7p0h_.dbf

 cp /u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519t7p0h_.dbf /backup

 

[oracle@orcl backup]$ cp /backup/o1_mf_ts1_519t7p0h_.dbf /u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519t7p0h_.dbf

---3. recover

 

Total System Global Area  268435456 bytes

Fixed Size                  1218920 bytes

Variable Size             201328280 bytes

Database Buffers           62914560 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519t7p0h_.dbf'

S SYS> recover datafile 6

Media recovery complete.

 

S SYS> alter database open;

alter database open

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519tbfc4_.dbf'

 

 

[oracle@orcl backup]$ cat fileCopy.sh  | grep o1_mf_ts1_519tbfc4_.dbf

 cp /u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519tbfc4_.dbf /disk2/backup

[oracle@orcl backup]$ cp /disk2/backup/o1_mf_ts1_519tbfc4_.dbf /u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519tbfc4_.dbf

 

 

S SYS> recover datafile 7

Media recovery complete.

 

S SYS> alter database open;

Database altered.

 

 

rman

[oracle@orcl ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jun 15 14:01:22 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1214711225)

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_orcl.f'; # default

RMAN> list backup of database;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

1       Full    262.77M    DISK        00:01:55     12-JUN-09

        BP Key: 1   Status: AVAILABLE  Compressed: YES  Tag: TAG20090612T161751

        Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_06_12/o1_mf_nnndf_TAG200906161751_534090wh_.bkp

  List of Datafiles in backup set 1

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  1       Full 11223829   12-JUN-09 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_50sxqqv0_.dbf

  2       Full 11223829   12-JUN-09 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_52d4tltd_.dbf

  3       Full 11223829   12-JUN-09 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_50sxqqvn_.dbf

  4       Full 11223829   12-JUN-09 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_50sxqr22_.dbf

  5       Full 11223829   12-JUN-09 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_50sxt0j6_.dbf

  6       Full 11223829   12-JUN-09 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519t7p0h_.dbf

  7       Full 11223829   12-JUN-09 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519tbfc4_.dbf

  9       Full 11223829   12-JUN-09 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_5316d4z3_.dbf

  13      Full 11223829   12-JUN-09 /home/oracle/disk2/ORCL/datafile/myts_reorg0.dbf

 

EM 으로 스케줄러 백업 시 마지막부분에 나오는 rman 스크립트

backup device type disk tag '%TAG' database include current controlfile;

backup device type disk tag '%TAG' archivelog all not backed up;

 

 

control file 고장시 복구 방법

--1. 다중화된 파일을 고장난 파일로 복사.

--2. 백업된 control file 로 복구.

--3. control file 을 강제로 생성함. nomount 모드에서.
               create controlfile 문이 너무 복잡. 그러나 EM에 Control files 부분 들어가서 backup To Tracce 누르면 자동 생성.

 

 

Flash Recovery Area

S SYS> desc V_$FLASH_RECOVERY_AREA_USAGE

 Name                                                        Null?    Type

 ----------------------------------------------------------- -------- ----------------------------------------

 FILE_TYPE                                                            VARCHAR2(12)

 PERCENT_SPACE_USED                                                   NUMBER

 PERCENT_SPACE_RECLAIMABLE                                            NUMBER

 NUMBER_OF_FILES                                                      NUMBER

S SYS> select FILE_TYPE,PERCENT_SPACE_USED,NUMBER_OF_FILES from V_$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE    PERCENT_SPACE_USED NUMBER_OF_FILES

------------ ------------------ ---------------

CONTROLFILE                 .22               1

ONLINELOG                  4.88               3

ARCHIVELOG                 1.19               5

BACKUPPIECE                9.78               3

IMAGECOPY                     0               0

FLASHBACKLOG                  0               0

SYS > ed fra

select name, value

    from V_$PARAMETER

    where name like lower('db_recovery_file%')

/

select FILE_TYPE,PERCENT_SPACE_USED,NUMBER_OF_FILES

    from V_$FLASH_RECOVERY_AREA_USAGE

union all

select 'Total==>',sum(PERCENT_SPACE_USED),null

    from V_$FLASH_RECOVERY_AREA_USAGE

/

SYS > ed fra2

var s number;

begin

select value into :s

    from V_$PARAMETER

    where name like lower('db_recovery_file_dest_size');

end;

/

select FILE_TYPE,PERCENT_SPACE_USED/100*:s/1024/1024 "MB",NUMBER_OF_FILES

    from V_$FLASH_RECOVERY_AREA_USAGE

union all

select 'Total==>',sum(PERCENT_SPACE_USED/100*:s/1024/1024),null

    from V_$FLASH_RECOVERY_AREA_USAGE

/

FILE_TYPE            MB NUMBER_OF_FILES

------------ ---------- ---------------

CONTROLFILE      6.7584               1

ONLINELOG      149.9136               3

ARCHIVELOG      36.5568               5

BACKUPPIECE    300.4416               3

IMAGECOPY             0               0

FLASHBACKLOG          0               0

Total==>       493.6704

rman을 통한 복구 방법

 

고장내기. example t/s의 데이터 파일 지우기

 

[oracle@orcl ~]$ rm /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_50sxt0j6_.dbf

 

 

S SYS> startup

ORACLE instance started.

Total System Global Area  268435456 bytes

Fixed Size                  1218920 bytes

Variable Size             201328280 bytes

Database Buffers           62914560 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_50sxt0j6_.dbf'

 

 

[oracle@orcl ~]$ rman target /

RMAN> restore datafile 5;

Starting restore at 15-JUN-09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00005 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_50sxt0j6_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_06_12/o1_mf_nnndf_TAG20090612T161751_534090wh_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_06_12/o1_mf_nnndf_TAG20090612T161751_534090wh_.bkp tag=TAG20090612T161751

channel ORA_DISK_1: restore complete, elapsed time: 00:00:17

Finished restore at 15-JUN-09

RMAN> recover datafile 5;

 

Starting recover at 15-JUN-09

using channel ORA_DISK_1

 

starting media recovery

media recovery complete, elapsed time: 00:00:05

 

Finished recover at 15-JUN-09

 

RMAN>