CATALOG 명령

http://radiocom.kunsan.ac.kr/lecture/oracle/backup_restore/catalog.html

CATALOG 명령

---

rman 툴에 의해서 백업을 유지관리(Backups & Repository Maintence)하는 명령어는 다음과 같다.

• CATALOG 문

• CHANGE 문

• CROSSCHECK 문

• DELETE 문

---

rman 툴은 rman 툴 밖의 운영체제상의 user-managed image copy로 백업해둔 image copy 백업 데이터파일을 사용하여 rman 툴에서 사용할 수 있는데,
그러기 위해서는 먼저 CATALOG 명령을 거쳐야 되는데, 이는 RMAN-generated image copy로 생성된 데이터 파일에는 존재하는 metadata가 User-managed image copy로 저장된 데이터 파일에 없는 metadata를 검사해서 추가하는 과정이 바로 CATALOG이다.
CATALOG 명령을 거치면, datafile copies, backup pieces, archived logs와 같은 파일을 recovery catalog에 카탈로그화 하게 되는 것이다.

백업 데이터 파일은 rman> LIST backup;이나 v$backup_files 뷰에서 확인할 수 있다.

【예제】☜
SQL> ALTER TABLESPACE example BEGIN BACKUP;

Tablespace altered.

SQL> host cp /export/home/oracle/oradata/orcl/example01.dbf /tmp/example01.dbf

SQL> ALTER TABLESPACE example END BACKUP;

Tablespace altered.

SQL> exit

$ rman target /

RMAN> CATALOG DATAFILECOPY '/tmp/example01.dbf';

using target database control file instead of recovery catalog
cataloged datafile copy
datafile copy file name=/tmp/example01.dbf RECID=12 STAMP=711897851

RMAN> exit

$ ls -l /tmp/example01.dbf
-rw-r-----   1 oracle   oinstall 104865792  2월 25일  13:23 /tmp/example01.dbf
$

【예제】☜ 

SQL> select fname from v$backup_files;

FNAME
--------------------------------------------------------------------------------
/export/home/oracle/oradata/orcl/users01.dbf
/export/home/oracle/oradata/orcl/system01.dbf
/export/home/oracle/oradata/orcl/example01.dbf
/export/home/oracle/oradata/orcl/undotbs01.dbf
/export/home/oracle/oradata/orcl/sysaux01.dbf
/export/home/oracle/flash_recovery_area/ORCL/backupset/2010_02_23/o1_mf_nnndf_TAG20100223T153548_5r6xt4m8_.bkp

/export/home/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_5r9kdmo5_.dbf

/export/home/oracle/flash_recovery_area/ORCL/backupset/2010_02_23/o1_mf_ncsnf_TAG20100223T153548_5r6xx4vt_.bkp

/export/home/oracle/flash_recovery_area/ORCL/datafile/o1_mf_system_5r9lv5dz_.dbf
/export/home/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_TAG20100224T154701_5r9lww69_.ctl

/export/home/oracle/flash_recovery_area/ORCL/datafile/o1_mf_system_5r9mdh7l_.dbf


/export/home/oracle/flash_recovery_area/ORCL/backupset/2010_02_24/o1_mf_nnsnf_TAG20100224T154701_5r9lwxb6_.bkp

/export/home/oracle/flash_recovery_area/ORCL/datafile/o1_mf_sysaux_5r9mg6f8_.dbf
/export/home/oracle/flash_recovery_area/ORCL/datafile/o1_mf_undotbs1_5r9mhmc9_.dbf

/export/home/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_5r9mhtg2_.dbf

/export/home/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_TAG20100224T155615_5r9mj9m2_.ctl

/export/home/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_5r9mjbo9_.dbf

/export/home/oracle/flash_recovery_area/ORCL/backupset/2010_02_24/o1_mf_nnsnf_TAG20100224T155615_5r9mjcry_.bkp

/export/home/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_5rco2gvv_.dbf


23 개의 행이 선택되었습니다.

SQL> select file_name from DBA_DATA_FILES;

FILE_NAME
--------------------------------------------------
/export/home/oracle/oradata/orcl/users01.dbf
/export/home/oracle/oradata/orcl/undotbs01.dbf
/export/home/oracle/oradata/orcl/sysaux01.dbf
/export/home/oracle/oradata/orcl/system01.dbf
/export/home/oracle/oradata/orcl/example01.dbf

SQL>
RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    1.05G      DISK        00:01:28     23-FEB-10      
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20100223T153548
        Piece Name: /export/home/oracle/flash_recovery_area/ORCL/backupset/2010_02_23/o1_mf_nnndf_TAG20100223T153548_5r6xt4m8_.bkp
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1177601    23-FEB-10 /export/home/oracle/oradata/orcl/system01.dbf
  2       Full 1177601    23-FEB-10 /export/home/oracle/oradata/orcl/sysaux01.dbf
  3       Full 1177601    23-FEB-10 /export/home/oracle/oradata/orcl/undotbs01.dbf
  4       Full 1177601    23-FEB-10 /export/home/oracle/oradata/orcl/users01.dbf
  5       Full 1177601    23-FEB-10 /export/home/oracle/oradata/orcl/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    9.36M      DISK        00:00:02     23-FEB-10      
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20100223T153548
        Piece Name: /export/home/oracle/flash_recovery_area/ORCL/backupset/2010_02_23/o1_mf_ncsnf_TAG20100223T153548_5r6xx4vt_.bkp
  SPFILE Included: Modification time: 23-FEB-10
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 1177640      Ckp time: 23-FEB-10

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    80.00K     DISK        00:00:00     24-FEB-10      
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20100224T154701
        Piece Name: /export/home/oracle/flash_recovery_area/ORCL/backupset/2010_02_24/o1_mf_nnsnf_TAG20100224T154701_5r9lwxb6_.bkp
  SPFILE Included: Modification time: 24-FEB-10
  SPFILE db_unique_name: ORCL

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Full    80.00K     DISK        00:00:00     24-FEB-10      
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20100224T155615
        Piece Name: /export/home/oracle/flash_recovery_area/ORCL/backupset/2010_02_24/o1_mf_nnsnf_TAG20100224T155615_5r9mjcry_.bkp
  SPFILE Included: Modification time: 24-FEB-10
  SPFILE db_unique_name: ORCL

RMAN>

디스크의 모든 백업파일 catalog하기

$ su - oracle
$ sqlplus sys as sysdba

SQL> ALTER DATABASE BEGIN BACKUP;

Database altered.

SQL> host ls /export/home/oracle/oradata/orcl
control01.ctl  redo01.log     redo03.log     system01.dbf   undotbs01.dbf
example01.dbf  redo02.log     sysaux01.dbf   temp01.dbf     users01.dbf

SQL> host mkdir backup

SQL> host cp /export/home/oracle/oradata/orcl/* backup/

SQL> ALTER DATABASE END BACKUP;

Database altered.

SQL> host ls backup
control01.ctl  redo01.log     redo03.log     system01.dbf   undotbs01.dbf
example01.dbf  redo02.log     sysaux01.dbf   temp01.dbf     users01.dbf

SQL> exit
$ rman target /

RMAN> CATALOG START WITH '+disk';

using target database control file instead of recovery catalog
searching for all files that match the pattern +disk
no files found to be unknown to the database

RMAN> CATALOG START WITH '/export/home/oracle/app/oracle/product/11.2/backup/';

searching for all files that match the pattern /export/home/oracle/app/oracle/product/11.2/backup/

List of Files Unknown to the Database
=====================================
File Name: /export/home/oracle/app/oracle/product/11.2/backup/control01.ctl
File Name: /export/home/oracle/app/oracle/product/11.2/backup/example01.dbf
File Name: /export/home/oracle/app/oracle/product/11.2/backup/redo01.log
File Name: /export/home/oracle/app/oracle/product/11.2/backup/redo02.log
File Name: /export/home/oracle/app/oracle/product/11.2/backup/redo03.log
File Name: /export/home/oracle/app/oracle/product/11.2/backup/sysaux01.dbf
File Name: /export/home/oracle/app/oracle/product/11.2/backup/system01.dbf
File Name: /export/home/oracle/app/oracle/product/11.2/backup/temp01.dbf
File Name: /export/home/oracle/app/oracle/product/11.2/backup/undotbs01.dbf
File Name: /export/home/oracle/app/oracle/product/11.2/backup/users01.dbf

Do you really want to catalog the above files (enter YES or NO)? y
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /export/home/oracle/app/oracle/product/11.2/backup/example01.dbf
File Name: /export/home/oracle/app/oracle/product/11.2/backup/sysaux01.dbf
File Name: /export/home/oracle/app/oracle/product/11.2/backup/system01.dbf
File Name: /export/home/oracle/app/oracle/product/11.2/backup/temp01.dbf
File Name: /export/home/oracle/app/oracle/product/11.2/backup/undotbs01.dbf
File Name: /export/home/oracle/app/oracle/product/11.2/backup/users01.dbf

List of Files Which Where Not Cataloged
=======================================
File Name: /export/home/oracle/app/oracle/product/11.2/backup/control01.ctl
  RMAN-07519: Reason: Error while cataloging. See alert.log.
File Name: /export/home/oracle/app/oracle/product/11.2/backup/redo01.log
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /export/home/oracle/app/oracle/product/11.2/backup/redo02.log
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /export/home/oracle/app/oracle/product/11.2/backup/redo03.log
  RMAN-07529: Reason: catalog is not supported for this file type

RMAN> 
RMAN> catalog recovery area;

searching for all files in the recovery area
no files found to be unknown to the database

RMAN> LIST BACKUP OF DATABASE BY BACKUP;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    1.05G      DISK        00:01:28     23-FEB-10      
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20100223T153548
        Piece Name: /export/home/oracle/flash_recovery_area/ORCL/backupset/2010_02_23/o1_mf_nnndf_TAG20100223T153548_5r6xt4m8_.bkp
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1177601    23-FEB-10 /export/home/oracle/oradata/orcl/system01.dbf
  2       Full 1177601    23-FEB-10 /export/home/oracle/oradata/orcl/sysaux01.dbf
  3       Full 1177601    23-FEB-10 /export/home/oracle/oradata/orcl/undotbs01.dbf
  4       Full 1177601    23-FEB-10 /export/home/oracle/oradata/orcl/users01.dbf
  5       Full 1177601    23-FEB-10 /export/home/oracle/oradata/orcl/example01.dbf

RMAN>

CALTALOG 한 파일에서 UNCATALOG로 metadata를 환원시키기

CATALOG로 의해서 metadata를 넣어 세탁한 것을 다시 환원시키는 과정은 CHANGE ... UNCATALOG 문을 사용하여 CATALOG에 의해 들어갔던 metadata를 삭제하여 환원시키는 것이다.

RMAN> CHANGE DATAFILECOPY '/export/home/oracle/app/oracle/product/11.2/backup/example01.dbf' UNCATALOG;

uncataloged datafile copy
datafile copy file name=/export/home/oracle/app/oracle/product/11.2/backup/example01.dbf RECID=13 STAMP=711902927
Uncataloged 1 objects


RMAN> CHANGE CONTROLFILECOPY '/tmp/control01.ctl' UNCATALOG; ☜ control 파일인 경우

recovery catalog에 쿼리하기

recovery catalog 뷰로부터 어떤 정보를 확인하려면 recovery catalog를 의미하는 RC_prefix로 시작하는 뷰로부터 질의하면 된다.
rc_로 시작되는 뷰는 v$로 시작되는 뷰와 거의 같으나 rc_로 시작되는 뷰에는 metadata에 대한 정보를 담고 있지만, v$로 시작되는 뷰는 metadata에 대한 정보가 없는것이 다르다.

즉,

RC_BACKUP_PIECE와 V$BACKUP_PIECE는 서로 대응되고,

RC_ARCHIVED_LOG와 V$ARCHIVED_LOG도 서로 대응된다.

그리고, RC_BACKUP_FILES를 실행하려면, 먼저 DBMS_RCVMAN.SETDATABASE를 실행시켜야 한다.

【예제】☜
$ sqlplus sys as sysdba

세션이 변경되었습니다.

SQL> select dbid from v$database;

      DBID
----------
1239068612

SQL> call dbms_rcvman.setdatabase(null,null,null,1239068612);

호출이 완료되었습니다.

SQL> execute dbms_rcvman.setdatabase(null,null,null,1239068612);

PL/SQL procedure successfully completed.

SQL>

recovery catalog database를 연결하고,

SQL> select db_key from rc_database
  2  where dbid=dbid_of_target;

SQL> SELECT BS_KEY, BACKUP_TYPE, COMPLETION_TIME
  2   FROM RC_DATABASE_INCARNATION i, RC_BACKUP_SET b
  3   WHERE i.DB_KEY = 1
  4   AND i.DB_KEY = b.DB_KEY
  5   AND i.CURRENT_INCARTION = 'YES';

SQL> SELECT * FROM RC_BACKUP_FILES;