Re: 오늘의 마지막 문제1_이수자 평가 4번 문제

[22기_정민수]

## 오늘의 마지막 문제1 (이수자 평가 4)

# 대상: testdb
# 1. RMAN 전체 백업

sh>

cd 
. oraenv
# testdb
rman target /

RMAN> 

backup database include current controlfile;
exit;

# 2. orcl 의 리두 로그 그룹과 멤버처럼 그룹 6개, 멤버 각각 2개씩 orcltest 에도 구현하시오

SQL>
@log
@logfile
    GROUP# MEMBER
---------- ---------------------------------------------
         1 /u01/app/oracle/oradata/TESTDB/onlinelog/o1_mf_1_nsqyp819_.log
         1 /u01/app/oracle/fast_recovery_area/TESTDB/onlinelog/o1_mf_1_nsqypdmv_.log
         2 /u01/app/oracle/oradata/TESTDB/onlinelog/o1_mf_2_nsqyp81m_.log
         2 /u01/app/oracle/fast_recovery_area/TESTDB/onlinelog/o1_mf_2_nsqypg1j_.log
         3 /u01/app/oracle/oradata/TESTDB/onlinelog/o1_mf_3_nsqyp820_.log
         3 /u01/app/oracle/fast_recovery_area/TESTDB/onlinelog/o1_mf_3_nsqypckc_.log
6 행이 선택되었습니다.

SQL> @log

    GROUP# STATUS            SEQUENCE# ARC    MEMBERS
---------- ---------------- ---------- --- ----------
         1 INACTIVE                  4 YES          2
         2 INACTIVE                  5 YES          2
         3 CURRENT                   6 NO           2

ALTER DATABASE ADD LOGFILE GROUP 4 '/u01/app/oracle/oradata/TESTDB/onlinelog/redo04a.log' SIZE 100m;
ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/TESTDB/onlinelog/redo04b.log' TO GROUP 4;

ALTER DATABASE ADD LOGFILE GROUP 5 '/u01/app/oracle/oradata/TESTDB/onlinelog/redo05a.log' SIZE 100m;
ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/TESTDB/onlinelog/redo05b.log' TO GROUP 5;

ALTER DATABASE ADD LOGFILE GROUP 6 '/u01/app/oracle/oradata/TESTDB/onlinelog/redo06a.log' SIZE 100m;
ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/TESTDB/onlinelog/redo06b.log' TO GROUP 6;


SQL>

 @log
    GROUP# STATUS            SEQUENCE# ARC    MEMBERS
---------- ---------------- ---------- --- ----------
         1 INACTIVE                  4 YES          2
         2 INACTIVE                  5 YES          2
         3 CURRENT                   6 NO           2
         4 UNUSED                    0 YES          2
         5 UNUSED                    0 YES          2
         6 UNUSED                    0 YES          2

6 행이 선택되었습니다.

SQL> @logfile
    GROUP# MEMBER
---------- ---------------------------------------------
         1 /u01/app/oracle/fast_recovery_area/TESTDB/onlinelog/o1_mf_1_nsqypdmv_.log
         1 /u01/app/oracle/oradata/TESTDB/onlinelog/o1_mf_1_nsqyp819_.log
         2 /u01/app/oracle/oradata/TESTDB/onlinelog/o1_mf_2_nsqyp81m_.log
         2 /u01/app/oracle/fast_recovery_area/TESTDB/onlinelog/o1_mf_2_nsqypg1j_.log
         3 /u01/app/oracle/fast_recovery_area/TESTDB/onlinelog/o1_mf_3_nsqypckc_.log
         3 /u01/app/oracle/oradata/TESTDB/onlinelog/o1_mf_3_nsqyp820_.log
         4 /u01/app/oracle/oradata/TESTDB/onlinelog/redo04a.log
         4 /u01/app/oracle/oradata/TESTDB/onlinelog/redo04b.log
         5 /u01/app/oracle/oradata/TESTDB/onlinelog/redo05a.log
         5 /u01/app/oracle/oradata/TESTDB/onlinelog/redo05b.log
         6 /u01/app/oracle/oradata/TESTDB/onlinelog/redo06a.log
         6 /u01/app/oracle/oradata/TESTDB/onlinelog/redo06b.log

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

ALTER DATABASE ADD LOGFILE GROUP 1 '/u01/app/oracle/oradata/TESTDB/onlinelog/redo01a.log' SIZE 100m;
ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/TESTDB/onlinelog/redo01b.log' TO GROUP 1;

ALTER DATABASE ADD LOGFILE GROUP 2 '/u01/app/oracle/oradata/TESTDB/onlinelog/redo02a.log' SIZE 100m;
ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/TESTDB/onlinelog/redo02b.log' TO GROUP 2;

ALTER DATABASE ADD LOGFILE GROUP 3 '/u01/app/oracle/oradata/TESTDB/onlinelog/redo03a.log' SIZE 100m;
ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/TESTDB/onlinelog/redo03b.log' TO GROUP 3;

# 3. orcltest 에 ts07 테이블 스페이스를 만들고 ts07 테이블 스페이스로 emp 와 dept 를 이행하시오
# 경로 체크

SQL> 
@ts

CREATE TABLESPACE ts07 DATAFILE '/u01/app/oracle/oradata/TESTDB/datafile/ts07.dbf' size 50m;

CONNECT scott_test/tiger@testdb

SELECT table_name, tablespace_name FROM user_tables;

ALTER TABLE dept MOVE TABLESPACE ts07;
ALTER TABLE emp MOVE TABLESPACE ts07;

SELECT table_name, tablespace_name FROM user_tables;