728x90
REPORT SCHEMA 명령어를 이용한 데이터베이스 구조 확인
1> RMAN-툴을 이용하여 sys 계정으로 prod 데이터베이스에 로컬로그인 합니다.
| [oracle@vm161 ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Thu Apr 28 19:13:46 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1430301032) RMAN> |
2> [REPORT SCHEMA ;] 명령어는 컨트롤파일의 내용을 기반해서 데이터베이스를 구성하는 데이터파일과 임시파일의 정보를 표시합니다.
| RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name ORCL List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 750 SYSTEM *** /u01/app/oracle/oradata/orcl/system01.dbf 2 540 SYSAUX *** /u01/app/oracle/oradata/orcl/sysaux01.dbf 3 100 UNDOTBS1 *** /u01/app/oracle/oradata/orcl/undotbs01.dbf 4 5 USERS *** /u01/app/oracle/oradata/orcl/users01.dbf 5 313 EXAMPLE *** /u01/app/oracle/oradata/orcl/example01.dbf 6 3 QUERY *** /u01/app/oracle/oradata/orcl/query01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 29 TEMP 32767 /u01/app/oracle/oradata/orcl/temp01.dbf 2 3 TEMP2 3 /u01/app/oracle/oradata/orcl/temp02.dbf |
■ RMAN BACKUP 명령어
1. 전체 데이터베이스를 구성하는 모든 데이터파일 백업
RMAN으로 백업받을 수 있는 파일은 다음과 같습니다.
- 데이터파일(DATAFILE) : 데이터파일 파일 손상을 해결하기 위하여 백업.
- 컨트롤파일(CONTROLFILE) : 컨트롤파일 파일 손상을 해결하기 위하여 백업.
- 바이너리 형식의 초기화파라미터 파일(SPFILE) : SPFILE-파일 손상을 해결하기 위하여 백업.
- 아카이브 리두 로그파일(ARCHIVED REDO-LOGFILE) : 아카이브 로그파일 생성 경로 상의 공간 확보를 위하여
다른 경로로 이동시키는 것을 RMAN-백업방법을 이용하여 수행.
- 이전에 생성한 RMAN-툴의 백업파일들 : 다른 백업 저장장치로 백업파일을 이동 시킬 때 백업 방법을 이용하여 수행.
# RMAN BACKUP 명령어 옵션 설명
- AS BACKUPSET 옵션: 백업셑 형식으로 백업파일을 생성
- AS COPY 옵션: 각 데이터파일을 IMAGECOPY 방식으로 백업
- FORMAT '값' 옵션 : 백업파일이 생성되는 경로명 및 파일이름을 명시
- DATABASE 옵션 : 데이터베이스를 구성하는 모든 데이터파일을 의미
- INCREMENTAL LEVEL 0 옵션 : 백업을 수행 시에 변경된 블록만 백업하는 증분백업의 기준레벨(레벨 0) 백업을 수행.
#접속 실습
1> 운영체제의 환경변수를 설정하여 RMAN-툴 내에서 날짜-시간 표시형식을 지정합니다.
| [oracle@vm161 ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 [oracle@vm161 ~]$ export NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS' |
2> RMAN-툴을 이용하여 prod 데이터베이스에 로컬로그인 합니다.
| [oracle@vm161 ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Thu Apr 28 19:13:46 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1430301032) RMAN> |
# 백업 실습
1> BACKUPSET 형식의 백업 수행-1 실습: 전체 데이터파일을 모두 백업합니다.
1-1> FILESPERSET 옵션 없이 백업: 데이터베이스를 구성하는 모든 데이터파일을 한 SET 로 묶어서 하나의 백업파일(PIECE)이 생성됩니다.
| RMAN> backup as backupset 2> format '/backup/rman_%s%p' 3> database; Starting backup at 28-APR-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=133 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/query01.dbf channel ORA_DISK_1: starting piece 1 at 28-APR-16 channel ORA_DISK_1: finished piece 1 at 28-APR-16 piece handle=/backup/rman_11 tag=TAG20160428T191843 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:02:22 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 28-APR-16 channel ORA_DISK_1: finished piece 1 at 28-APR-16 piece handle=/backup/rman_21 tag=TAG20160428T191843 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 28-APR-16 |
1-2> 생성된 백업을 운영체제 명령어로 확인
- 생성된 파일정보를 운영체제 명령어(ls -lhtr)로 확인
| RMAN> host 'ls -al /backup/rman*'; -rw-r-----. 1 oracle oinstall 1174257664 Apr 28 19:20 /backup/rman_11 -rw-r-----. 1 oracle oinstall 9797632 Apr 28 19:21 /backup/rman_21 host command complete |
2> BACKUPSET 형식의 백업 수행-2 실습: 전체 데이터파일을 모두 백업합니다.
1-1> FILESPERSET 옵션을 명시하여 백업: [FILESPERSET 2] 옵션 때문에 최대 2개를 넘지 않도록
하나의 BACKUPSET을 구성하여 여러 BACKUPSET로 백업을 수행합니다.
| RMAN> backup as backupset 2> incremental level 0 3> format '/backup/ramn_%U' 4> filesperset 2 5> database 6> tag "ORCL_FULL_01"; Starting backup at 28-APR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental level 0 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/query01.dbf channel ORA_DISK_1: starting piece 1 at 28-APR-16 channel ORA_DISK_1: finished piece 1 at 28-APR-16 piece handle=/backup/ramn_03r46k1h_1_1 tag=ORCL_FULL_01 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45 channel ORA_DISK_1: starting incremental level 0 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_1: starting piece 1 at 28-APR-16 channel ORA_DISK_1: finished piece 1 at 28-APR-16 piece handle=/backup/ramn_04r46k2u_1_1 tag=ORCL_FULL_01 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting incremental level 0 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 28-APR-16 channel ORA_DISK_1: finished piece 1 at 28-APR-16 piece handle=/backup/ramn_05r46k3n_1_1 tag=ORCL_FULL_01 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting incremental level 0 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 28-APR-16 channel ORA_DISK_1: finished piece 1 at 28-APR-16 piece handle=/backup/ramn_06r46k47_1_1 tag=ORCL_FULL_01 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 28-APR-16 |
1-2> 생성된 파일정보를 운영체제 명령어(ls -lhtr)로 확인
| [oracle@vm161 backup]$ ll total 1156764 -rw-r-----. 1 oracle oinstall 673824768 Apr 28 19:39 ramn_03r46k1h_1_1 -rw-r-----. 1 oracle oinstall 417562624 Apr 28 19:39 ramn_04r46k2u_1_1 -rw-r-----. 1 oracle oinstall 83329024 Apr 28 19:39 ramn_05r46k3n_1_1 -rw-r-----. 1 oracle oinstall 9797632 Apr 28 19:39 ramn_06r46k47_1_1 |
3> IMAGECOPY 형식의 백업 수행-1 실습: 전체 데이터파일을 모두 백업합니다.
3-1> IMAGECOPY 방식으로 백업을 수행합니다.
| RMAN> backup as copy 2> incremental level 0 3> format '/backup/rman_%U' 4> database 5> tag "ORCL_FULL_01"; Starting backup at 28-APR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf output file name=/backup/rman_data_D-ORCL_I-1430301032_TS-SYSTEM_FNO-1_07r46kfg tag=ORCL_FULL_01 RECID=2 STAMP=910381587 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf output file name=/backup/rman_data_D-ORCL_I-1430301032_TS-SYSAUX_FNO-2_08r46kgt tag=ORCL_FULL_01 RECID=3 STAMP=910381623 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf output file name=/backup/rman_data_D-ORCL_I-1430301032_TS-EXAMPLE_FNO-5_09r46ki0 tag=ORCL_FULL_01 RECID=4 STAMP=910381646 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf output file name=/backup/rman_data_D-ORCL_I-1430301032_TS-UNDOTBS1_FNO-3_0ar46kif tag=ORCL_FULL_01 RECID=5 STAMP=910381651 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy copying current control file output file name=/backup/rman_cf_D-ORCL_id-1430301032_0br46kim tag=ORCL_FULL_01 RECID=6 STAMP=910381655 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf output file name=/backup/rman_data_D-ORCL_I-1430301032_TS-USERS_FNO-4_0cr46kin tag=ORCL_FULL_01 RECID=7 STAMP=910381656 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/query01.dbf output file name=/backup/rman_data_D-ORCL_I-1430301032_TS-QUERY_FNO-6_0dr46kip tag=ORCL_FULL_01 RECID=8 STAMP=910381657 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 28-APR-16 |
3-2> 생성된 파일정보를 운영체제 명령어(ls -lhtr)로 확인
| RMAN> host 'ls -lhtr /backup'; total 1.7G drwxr-xr-x. 2 oracle dba 4.0K Apr 28 19:36 org -rw-r-----. 1 oracle dba 751M Apr 28 21:19 rman_data_D-ORCL_I-1430301032_TS-SYSTEM_FNO-1_0kr46pqs -rw-r-----. 1 oracle dba 541M Apr 28 21:19 rman_data_D-ORCL_I-1430301032_TS-SYSAUX_FNO-2_0lr46puh -rw-r-----. 1 oracle dba 314M Apr 28 21:20 rman_data_D-ORCL_I-1430301032_TS-EXAMPLE_FNO-5_0mr46pvu -rw-r-----. 1 oracle dba 101M Apr 28 21:21 rman_data_D-ORCL_I-1430301032_TS-UNDOTBS1_FNO-3_0nr46q1b -rw-r-----. 1 oracle dba 9.3M Apr 28 21:21 rman_cf_D-ORCL_id-1430301032_0or46q2f -rw-r-----. 1 oracle dba 5.1M Apr 28 21:21 rman_data_D-ORCL_I-1430301032_TS-USERS_FNO-4_0pr46q2i -rw-r-----. 1 oracle dba 3.1M Apr 28 21:21 rman_data_D-ORCL_I-1430301032_TS-QUERY_FNO-6_0qr46q2l host command complete |
3> BACKUPSET 형식의 백업 수행-3 실습: 전체 데이터파일을 모두 백업합니다.
3-1> 압축된 BACKUPSET 방식 백업을 수행
| RMAN> backup as compressed backupset 2> format '/backup/%U' 3> incremental level 0 4> database 5> tag "ORCL_FULL_02"; Starting backup at 28-APR-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=132 device type=DISK channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/query01.dbf channel ORA_DISK_1: starting piece 1 at 28-APR-16 channel ORA_DISK_1: finished piece 1 at 28-APR-16 piece handle=/backup/0er46l4l_1_1 tag=ORCL_FULL_02 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26 channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 28-APR-16 channel ORA_DISK_1: finished piece 1 at 28-APR-16 piece handle=/backup/0fr46l7b_1_1 tag=ORCL_FULL_02 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 28-APR-16 |
3-2> 생성된 파일정보를 운영체제 명령어(ls -lhtr)로 확인
| RMAN> host 'ls -lhtr /backup'; total 295M -rw-r-----. 1 oracle oinstall 294M Apr 28 19:58 0er46l4l_1_1 -rw-r-----. 1 oracle oinstall 1.1M Apr 28 19:58 0fr46l7b_1_1 host command complete |
3-3> 압축율을 LOW로 설정하고 백업셑 방식으로 백업 수행
| RMAN> set compression algorithm 'LOW'; executing command: SET compression RMAN> backup as compressed backupset 2> format '/backup/%U' 3> database 4> tag "ORCL_FULL_03"; Starting backup at 28-APR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/query01.dbf channel ORA_DISK_1: starting piece 1 at 28-APR-16 channel ORA_DISK_1: finished piece 1 at 28-APR-16 piece handle=/backup/0gr46lbd_1_1 tag=ORCL_FULL_03 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 28-APR-16 channel ORA_DISK_1: finished piece 1 at 28-APR-16 piece handle=/backup/0hr46ldo_1_1 tag=ORCL_FULL_03 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 28-APR-16 |
3-4> 압축율을 HIGH로 설정하고 백업셑 방식으로 백업 수행
| RMAN> set compression algorithm 'HIGH'; executing command: SET compression RMAN> backup as compressed backupset 2> format '/backup/%U' 3> database 4> tag "ORCL_FULL_04"; Starting backup at 28-APR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/query01.dbf channel ORA_DISK_1: starting piece 1 at 28-APR-16 channel ORA_DISK_1: finished piece 1 at 28-APR-16 piece handle=/backup/0ir46lfu_1_1 tag=ORCL_FULL_04 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:02:56 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 28-APR-16 channel ORA_DISK_1: finished piece 1 at 28-APR-16 piece handle=/backup/0jr46lle_1_1 tag=ORCL_FULL_04 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 28-APR-16 |
3-5> 생성된 파일정보를 운영체제 명령어(ls -lhtr)로 확인: 크기 비교
| RMAN> host 'ls -altr /backup'; total 926104 dr-xr-xr-x. 28 root root 4096 Apr 28 19:09 .. drwxr-xr-x. 3 oracle dba 4096 Apr 28 20:06 . drwxr-xr-x. 2 oracle oinstall 4096 Apr 28 19:36 org -rw-r-----. 1 oracle oinstall 307445760 Apr 28 19:58 0er46l4l_1_1 -rw-r-----. 1 oracle oinstall 1097728 Apr 28 19:58 0fr46l7b_1_1 -rw-r-----. 1 oracle oinstall 388407296 Apr 28 20:01 0gr46lbd_1_1 -rw-r-----. 1 oracle oinstall 1130496 Apr 28 20:02 0hr46ldo_1_1 -rw-r-----. 1 oracle oinstall 249126912 Apr 28 20:05 0ir46lfu_1_1 -rw-r-----. 1 oracle oinstall 1097728 Apr 28 20:06 0jr46lle_1_1 host command complete |
이 후 실습을 위하여 기존 RMAN 접속 해제
| RMAN> exit Recovery Manager complete. |
728x90
반응형
'Oracle > Admin' 카테고리의 다른 글
| [Oracle] Index coalesce vs Index rebuild (0) | 2024.11.20 |
|---|---|
| [Oracle] ORA-01804 : this index object is being online .. (2) | 2024.11.20 |
| [Oracle] Index Rebuild (0) | 2024.11.13 |
| [Oracle] ASH, AWR (0) | 2024.11.04 |
| [Oracle] Wait Event 목록 (0) | 2024.10.31 |
