목차

Oracle DataBase 11g Admin II 강의

02

Oracle Database Recovery
28 Monitoring Fast Recovery Area Space Usage
SELECT * FROM V$RECOVERY_FILE_DEST;
SELECT * FROM V$RECOVERY_AREA_USAGE;

03

RMAN
7 Recovery Catalog 데이타베이스 구성
create database rcat;
create tablespace rcat_ts datafile '/u01/app/oracle/oradata/rcat/rcat_ts01.dbf' size 15M autoextend on next 10m maxsize 1000M;
8 Recovery Catalog 소유자 생성
create user rcowner identified by oracle temporary tablespace temp default tablespace rcat_ts quota unlimited on rcat_ts;
grant recovery_catalog_owner to rcowner;
9 Recovery Catalog 생성
RMAN> connect catalog …
RMAN> create catalog;
20 RMAN 내장 스크립트

04

4 sqlplus system/oracle@orcl
select * from V$RMAN_CONFIGURATION;
16 HWM (High Water Mark)

05

3 Backup and Recovery Reference > Summary of RMAN Subclauses > formatSpec
RMAN> backup as backupset format '/install/rman/df_%d_%s_%p.bus' tablespace users;
4 RMAN> backup as copy datafile '+DATA/orcl/datafile/example.265.933811767';
15 RMAN> backup device type disk as backupset database plus archivelog;

06

06. tempfile recovery
07. redolog
SQL> SELECT GROUP#, STATUS, ARCHIVED FROM V$LOG;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
09. *
11. index re-create
create table tb_all_columns as select owner,table_name,column_name from all_tab_columns;

CREATE INDEX SCOTT.EMP_IDX1 ON SCOTT.EMP (ENAME) TABLESPACE USERS PARALLEL 2 ;
14. orapwd file=$ORACLE_HOME/dbs/orapworcl password=ora entries=5

07

03. RMAN> sql 'alter tablespace example offline immediate';
RMAN> restore tablespace example;
RMAN> recover tablespace example;
RMAN> sql 'alter tablespace example online';
05. EM에서 Complete Recovery 수행
14. PIT Recovery
17. 서버 파라미터 파일 손실에 따른 Recovery
19. 자동 백업으로부터 콘트롤 파일 복원

08

07. orcl 에서 모니터링
10. RMAN DEBUG 옵션
14. *

09

04. Data Recovery Advisor
08. Data Recovery Advisor RMAN command interface
30. http://oracleusefultips.blogspot.kr/2010/05/11g-health-monitor-dbmshm.html
31. show hm_run
show homes
set home diag/rdbms/orcl/orcl
create report hm_run <HM_RUN_ID>
show report hm_run <HM_RUN_ID>

10

11

Oracle Total Recall - FDA를 이용하여 undo의 제한사항을 해결
flashback - recyclebin

12

flashback database

13

14

10 SQL> exec dbms_stats.gather_schema_stats('HR');
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname⇒'HR',tabname⇒'EMP');
SQL> exec dbms_stats.gather_index_stats(ownname⇒'HR',indname⇒'EMP_NAME_IX');
23 SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
SQL> @?/rdbms/admin/awrrpt.sql

15

SQL Tuning Advisors 통계,인덱스,프로파일 등을 이용한 SQL 전체적인 advisor
SQL Access Advisors 인덱스,파티셔닝,M-View 등이 필요한지에 대한 advisor
SQL Performance Advisors 변경되는 구조에 대한 사전,사후 비교 예측 advisor

16

17

18

12 세그먼트 없이 테이블 생성
28 세그먼트 축소 (shrink)

19

14 플랫폼의 엔디언 형식 확인

etc