코봉이와 오라클: DB 정리

▣ BACKUPSET 정리

SQL> @fra2

PL/SQL procedure successfully completed.

FILE_TYPE PERCENT_SPACE_USED MB NUMBER_OF_FILES

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

CONTROLFILE .33 6.7584 1

ONLINELOG 7.32 149.9136 3

ARCHIVELOG 10.9 223.232 8

BACKUPPIECE 36.17 740.7616 12 => 필요없는 부분 수동으로 삭제 -> crosscheck -> delete expired backupset

IMAGECOPY 0 0 0

FLASHBACKLOG 4 81.92 8

Total ==> 58.72 1202.5856

7 rows selected.

SQL> r

1 select RECID,DELETED from V$BACKUP_PIECE_DETAILS

2* order by RECID

RECID DEL

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

7 NO

8 NO

9 NO

10 NO

11 NO

12 NO

13 NO

14 NO

15 NO

16 NO

17 NO

RECID DEL

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

18 NO

19 NO

20 NO

▶ 19, 20 : 현재 사용중인 backupset

14 rows selected.

[oracle@edrsr4p1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 27 10:48:51 2009

connected to target database: ORCL (DBID=1173970302)

RMAN> backup as compressed backupset format '/home/oracle/orcl%d_%s.bus' database;

Starting backup at 27-AUG-09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=152 devtype=DISK

channel ORA_DISK_1: starting compressed full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_3trzfl2y_.dbf

input datafile fno=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_3trzfl4m_.dbf

input datafile fno=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_3trzflmk_.dbf

input datafile fno=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_3trzjjxo_.dbf

input datafile fno=00002 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_3trzflk7_.dbf

channel ORA_DISK_1: starting piece 1 at 27-AUG-09

channel ORA_DISK_1: finished piece 1 at 27-AUG-09

piece handle=/home/oracle/orclORCL_7.bus tag=TAG20090827T104916 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35

channel ORA_DISK_1: starting compressed full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 27-AUG-09

channel ORA_DISK_1: finished piece 1 at 27-AUG-09

piece handle=/home/oracle/orclORCL_8.bus tag=TAG20090827T104916 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 27-AUG-09

RMAN> delete obsolete;

using target database control file instead of recovery catalog

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=143 devtype=DISK

Deleting the following obsolete backups and copies:

Type Key Completion Time Filename/Handle

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

Backup Set 7 27-AUG-09

Backup Piece 7 27-AUG-09 /home/oracle/orclORCL_7.bus

Backup Set 8 27-AUG-09

Backup Piece 8 27-AUG-09 /home/oracle/orclORCL_8.bus

Backup Set 9 27-AUG-09

Backup Piece 9 27-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_nnndf_TAG20090827T105654_59csz6vl_.bkp

Backup Set 10 27-AUG-09

Backup Piece 10 27-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_ncsnf_TAG20090827T105654_59ct1y3w_.bkp

Backup Set 11 27-AUG-09

Backup Piece 11 27-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_nnndf_TAG20090827T105845_59ct2osq_.bkp

Backup Set 12 27-AUG-09

Backup Piece 12 27-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_ncsnf_TAG20090827T105845_59ct5qxt_.bkp

Backup Set 13 27-AUG-09

Backup Piece 13 27-AUG-09 /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_nnndf_TAG20090827T110333_59ctcpc9_.bkp

Do you really want to delete the above objects (enter YES or NO)? yes

deleted backup piece

backup piece handle=/home/oracle/orclORCL_7.bus recid=7 stamp=695990956

deleted backup piece

backup piece handle=/home/oracle/orclORCL_8.bus recid=8 stamp=695991054

deleted backup piece

backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_nnndf_TAG20090827T105654_59csz6vl_.bkp recid=9 stamp=695991414

deleted backup piece

backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_ncsnf_TAG20090827T105654_59ct1y3w_.bkp recid=10 stamp=695991502

deleted backup piece

backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_nnndf_TAG20090827T105845_59ct2osq_.bkp recid=11 stamp=695991525

deleted backup piece

backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_ncsnf_TAG20090827T105845_59ct5qxt_.bkp recid=12 stamp=695991623

deleted backup piece

backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_08_27/o1_mf_nnndf_TAG20090827T110333_59ctcpc9_.bkp recid=13 stamp=695991814

deleted backup piece

SQL> @fra2

PL/SQL procedure successfully completed.

FILE_TYPE PERCENT_SPACE_USED MB NUMBER_OF_FILES

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

CONTROLFILE .33 6.7584 1

ONLINELOG 7.32 149.9136 3

ARCHIVELOG 1.78 36.4544 2

BACKUPPIECE 6.03 123.4944 2

IMAGECOPY 0 0 0

FLASHBACKLOG 4 81.92 8

Total ==> 19.46 398.5408

7 rows selected.

▣ 아카이브 정리

SQL> @fra2

PL/SQL procedure successfully completed.

FILE_TYPE PERCENT_SPACE_USED MB NUMBER_OF_FILES

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

CONTROLFILE .33 6.7584 1

ONLINELOG 7.32 149.9136 3

ARCHIVELOG 10.9 223.232 8

BACKUPPIECE 36.17 740.7616 12

IMAGECOPY 0 0 0

FLASHBACKLOG 4 81.92 8

Total ==> 58.72 1202.5856

7 rows selected.

SQL> alter system switch logfile;

System altered.

[oracle@edrsr4p1 archivelog]$ pwd

/u01/app/oracle/flash_recovery_area/ORCL/archivelog

[oracle@edrsr4p1 archivelog]$ ll

total 16

drwxr-x--- 2 oracle oinstall 4096 Aug 26 22:46 2009_08_26

drwxr-x--- 2 oracle oinstall 4096 Aug 27 11:20 2009_08_27

[oracle@edrsr4p1 archivelog]$ mv 2009_08_27 xx

[oracle@edrsr4p1 archivelog]$ rm -rf 2009_08_26 => 오늘 아카이브만 남기고 모두 삭제

[oracle@edrsr4p1 archivelog]$ mv xx 2009_08_27

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=140 devtype=DISK