1. HOME
  2. [종료]구루비 DB 스터디
  3. 2015년 하반기 - 오라클 데이터베이스 스터디
  4. Chap.03 Oracle Backup

Chap.03 Oracle Backup (by openlsw) [2015.10.23]

Backup

장애를 고치기 위해 필요한 파일을 미리 복사 해두는 것
장애는 언제든 발생할 수 있으므로 관리자는 필요한 파일을 백업 받아야 함

3.1 백업대상

  • 필수 : Data File, Redo Log File, Control File
  • 선택 : Parameter File, Password File, sqlnet.ora, listener.ora, tnsnames.ora 등

1) Data File


-- 사용 중인 Data File 은 자주 백업을 받아야 함
SQL> select name, status from v$datafile;

NAME                                               STATUS
-------------------------------------------------- --------------
/oradata2/system/system01.dbf                      SYSTEM
/oradata2/system/sysaux01.dbf                      ONLINE
/oradata2/system/undotbs01.dbf                     ONLINE
/oradata2/system/users01.dbf                       ONLINE

2) Control File


-- 현재 Control File 을 백업 받아야 함
SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/oradata2/system/control01.ctl
/oradata2/system/control02.ctl

3) Redo Log File


SQL> select a.group#, a.member, b.bytes/1024/1024 mb, b.sequence# "seq#", b.status, b.archived arc
from v$logfile a, v$log b
where a.group# = b.group#
order by 1, 2
/

GROUP# MEMBER                                          MB   seq# STATUS   ARC
------ --------------------------------------------- ---- ------ -------- -----
     1 /oradata2/system/redo01.log                     50   2362 INACTIVE YES
     2 /oradata2/system/redo02.log                     50   2363 INACTIVE YES
     3 /oradata2/system/redo03.log                     50   2364 CURRENT  NO

4) Parameter File/Password File

  • Parameter File
    • Oracle 서버의 각종 설정 정보 저장
    • 기동에 반드시 필요, 최적값 백업 필요
    • $ORACLE_HOME/dbs
  • Password File
    • sysdba 권한 암호 저장
    • DB종료 상태에서 딕셔너리 없이 로그온 하기 위해 존재
    • orapwd 로 재생성 가능

5) sqlnet.ora & listener.ora & tnsnames.ora

  • 외부 에서 DB서버로 접속을 받아주는 리스너의 설정 파일
  • $ORACLE_HOME/network/admin

3.2 백업의 종류

1) 닫힌 백업 (Cold backup/Closed backup)

  • 데이터베이스 종료 후 전체 파일 백업
    • 모든 파일의 Checkpoint SCN 은 같아야 함
      • 일반적으로 각각 다른 시점에 받은 백업 파일은 복구에 같이 사용할 수 없음
    • 정상 Shutdown 상태에서 백업 가능
      • shutdown abort, instance crash 등 상태는 일관성이 흐트러진 상태로 복구에 사용될 수 없음
      • OFFLINE 되어 있는 Data File 도 복구에 사용 불가 - Checkpoint scn 정보가 다름
    • Archive Log Mode 여부 무관
    • Data File, Online Redo Log File, Control File 백업 가능
  • (실습) Cold Backup 수행하기

SQL> select name, status from v$datafile;

NAME                                               STATUS
-------------------------------------------------- --------
/oradata2/system/system01.dbf                      SYSTEM
/oradata2/system/sysaux01.dbf                      ONLINE
/oradata2/system/undotbs01.dbf                     ONLINE
/oradata2/system/users01.dbf                       ONLINE

SQL> @log

GROUP# MEMBER                                          MB   seq# STATUS   ARC
------ --------------------------------------------- ---- ------ -------- -----
     1 /oradata2/system/redo01.log                     50   2362 INACTIVE YES
     2 /oradata2/system/redo02.log                     50   2363 INACTIVE YES
     3 /oradata2/system/redo03.log                     50   2364 CURRENT  NO

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/oradata2/system/control01.ctl
/oradata2/system/control02.ctl

SQL> !ls $ORACLE_HOME/dbs/*UTF8*
/sw/oracle/app/oracle/product/11.2.0/dbs/hc_UTF8.dat
/sw/oracle/app/oracle/product/11.2.0/dbs/lkUTF8
/sw/oracle/app/oracle/product/11.2.0/dbs/orapwUTF8
/sw/oracle/app/oracle/product/11.2.0/dbs/spfileUTF8.ora

SQL> !ls $ORACLE_HOME/network/admin
listener.ora  samples       shrept.lst    sqlnet.ora    tnsnames.ora

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
$
$ ls -al /oradata2/system/*.dbf
-rw-r-----    1 oracle   dba       104865792 Mar 17 2015  /oradata2/system/hotsource.dbf
-rw-r-----    1 oracle   dba      1073750016 Oct 23 14:15 /oradata2/system/sysaux01.dbf
-rw-r-----    1 oracle   dba      1073750016 Oct 23 14:15 /oradata2/system/system01.dbf
-rw-r-----    1 oracle   dba      2147491840 Oct 23 01:26 /oradata2/system/temp01.dbf
-rw-r-----    1 oracle   dba      2147491840 Oct 23 14:15 /oradata2/system/undotbs01.dbf
-rw-r-----    1 oracle   dba        52436992 Oct 23 14:15 /oradata2/system/users01.dbf
$ cp /oradata2/system/*.dbf /home/oracle/backup/close
$ ls -al /oradata2/system/*.log
-rw-r-----    1 oracle   dba        52429312 Oct 23 11:01 /oradata2/system/redo01.log
-rw-r-----    1 oracle   dba        52429312 Oct 23 11:01 /oradata2/system/redo02.log
-rw-r-----    1 oracle   dba        52429312 Oct 23 14:15 /oradata2/system/redo03.log
$ cp /oradata2/system/*.log /home/oracle/backup/close
$ ls -al /oradata2/system/*.ctl
-rw-r-----    1 oracle   dba         7716864 Oct 23 14:15 /oradata2/system/control01.ctl
-rw-r-----    1 oracle   dba         7716864 Oct 23 14:15 /oradata2/system/control02.ctl
$ cp /oradata2/system/*.ctl /home/oracle/backup/close
$ ls -al $ORACLE_HOME/dbs/*UTF8*
-rw-rw----    1 oracle   dba            1544 Oct 23 14:15 /sw/oracle/app/oracle/product/11.2.0/dbs/hc_UTF8.dat
-rw-r-----    1 oracle   dba              24 Nov 17 2011  /sw/oracle/app/oracle/product/11.2.0/dbs/lkUTF8
-rw-r-----    1 oracle   dba            1536 Mar 31 2015  /sw/oracle/app/oracle/product/11.2.0/dbs/orapwUTF8
-rw-r-----    1 oracle   dba            2560 Oct 23 09:27 /sw/oracle/app/oracle/product/11.2.0/dbs/spfileUTF8.ora
$ cp $ORACLE_HOME/dbs/*UTF8* /home/oracle/backup/close
$ ls -al $ORACLE_HOME/network/admin/*.ora
-rw-r--r--    1 oracle   dba            1569 Aug 24 2011  /sw/oracle/app/oracle/product/11.2.0/network/admin/listener.ora
-rw-r--r--    1 oracle   dba             286 Oct 27 2014  /sw/oracle/app/oracle/product/11.2.0/network/admin/sqlnet.ora
-rw-r--r--    1 oracle   dba            4460 Apr 22 2015  /sw/oracle/app/oracle/product/11.2.0/network/admin/tnsnames.ora
$ cp $ORACLE_HOME/network/admin/*.ora /home/oracle/backup/close
$ ls -al /home/oracle/backup/close
total 13227648
drwxr-xr-x    2 oracle   dba            4096 Oct 23 14:30 .
drwxr-xr-x    5 oracle   dba             256 Oct 23 13:42 ..
-rw-r-----    1 oracle   dba         7716864 Oct 23 14:29 control01.ctl
-rw-r-----    1 oracle   dba         7716864 Oct 23 14:29 control02.ctl
-rw-r-----    1 oracle   dba            1544 Oct 23 14:30 hc_UTF8.dat
-rw-r-----    1 oracle   dba       104865792 Oct 23 14:26 hotsource.dbf
-rw-r--r--    1 oracle   dba            1569 Oct 23 14:30 listener.ora
-rw-r-----    1 oracle   dba              24 Oct 23 14:30 lkUTF8
-rw-r-----    1 oracle   dba            1536 Oct 23 14:30 orapwUTF8
-rw-r-----    1 oracle   dba        52429312 Oct 23 14:28 redo01.log
-rw-r-----    1 oracle   dba        52429312 Oct 23 14:28 redo02.log
-rw-r-----    1 oracle   dba        52429312 Oct 23 14:28 redo03.log
-rw-r-----    1 oracle   dba            2560 Oct 23 14:30 spfileUTF8.ora
-rw-r--r--    1 oracle   dba             286 Oct 23 14:30 sqlnet.ora
-rw-r-----    1 oracle   dba      1073750016 Oct 23 14:26 sysaux01.dbf
-rw-r-----    1 oracle   dba      1073750016 Oct 23 14:26 system01.dbf
-rw-r-----    1 oracle   dba      2147491840 Oct 23 14:27 temp01.dbf
-rw-r--r--    1 oracle   dba            4460 Oct 23 14:30 tnsnames.ora
-rw-r-----    1 oracle   dba      2147491840 Oct 23 14:27 undotbs01.dbf
-rw-r-----    1 oracle   dba        52436992 Oct 23 14:27 users01.dbf

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 23 14:31:47 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2221336 bytes
Variable Size             339741416 bytes
Database Buffers          184549376 bytes
Redo Buffers                7950336 bytes
Database mounted.
Database opened.
SQL>

2) 열린 백업 (Hot backup/Open backup/Begin backup/Online backup)

  • 데이터베이스 가동 중 전체 파일 백업
    • Tablespace 단위로 백업 수행 (11g 부터 Database 단위도 가능, Redo 발생량 문제로 비권고)
      • 백업 전 대상 Tablespace 를 백업 모드로 적용하고, 백업 후 백업 모드를 해제
    • Archive Log Mode 필수
  • 예제

SQL> alter tablespace users begin backup;		<<< 백업 모드 적용 (Checkpoint 발생)

Tablespace altered.

SQL> !cp /oradata2/system/users01.dbf /home/oracle/backup/open

SQL> alter tablespace users end backup;		<<< 백업 모드 해제 (Checkpoint 발생)

Tablespace altered.

SQL>

-- Alert Log
Fri Oct 23 14:43:25 2015
alter tablespace users begin backup
Completed: alter tablespace users begin backup
Fri Oct 23 14:43:46 2015
alter tablespace users end backup
Completed: alter tablespace users end backup
  • 열린 백업 과 Checkpoint SCN

-- 평소모드
DATA FILE #4:
  name #8: /oradata2/system/users01.dbf
creation size=6400 block size=8192 status=0xe head=8 tail=8 dup=1
 tablespace 4, index=5 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:2404 scn: 0x0000.02a45591 10/23/2015 14:43:25
 Stop scn: 0xffff.ffffffff 10/23/2015 14:15:42
 Creation Checkpointed at scn:  0x0000.00004539 11/17/2011 10:57:33
 thread:1 rba:(0x1.c55b.10)

SQL> ALTER SYSTEM CHECKPOINT;		<<< CHECKPOINT

DATA FILE #4:
  name #8: /oradata2/system/users01.dbf
creation size=6400 block size=8192 status=0xe head=8 tail=8 dup=1
 tablespace 4, index=5 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:2405 scn: 0x0000.02a4574b 10/23/2015 14:54:33		<<< Chckpoint cnt & Checkpoint scn 증가
 Stop scn: 0xffff.ffffffff 10/23/2015 14:15:42
 Creation Checkpointed at scn:  0x0000.00004539 11/17/2011 10:57:33
 thread:1 rba:(0x1.c55b.10)

SQL> ALTER TABLESPACE USERS BEGIN BACKUP;		<<< BEGIN BACKUP (CHECKPOINT 발생 됨) / Hot-backup-in-progress 플래그 설정 됨 / 이후 변경 데이터의 블록이 모두 Redo Log 에 기록 됨

DATA FILE #4:
  name #8: /oradata2/system/users01.dbf
creation size=6400 block size=8192 status=0xe head=8 tail=8 dup=1
 tablespace 4, index=5 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:2406 scn: 0x0000.02a45764 10/23/2015 14:55:44		<<< Chckpoint cnt & Checkpoint scn 증가
 Stop scn: 0xffff.ffffffff 10/23/2015 14:15:42
 Creation Checkpointed at scn:  0x0000.00004539 11/17/2011 10:57:33
 thread:1 rba:(0x1.c55b.10)

SQL> ALTER SYSTEM CHECKPOINT;		<<< CHECKPOINT

DATA FILE #4:
  name #8: /oradata2/system/users01.dbf
creation size=6400 block size=8192 status=0xe head=8 tail=8 dup=1
 tablespace 4, index=5 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:2407 scn: 0x0000.02a45764 10/23/2015 14:55:44		<<< Chckpoint cnt 증가 & Checkpoint scn 고정
 Stop scn: 0xffff.ffffffff 10/23/2015 14:15:42
 Creation Checkpointed at scn:  0x0000.00004539 11/17/2011 10:57:33
 thread:1 rba:(0x1.c55b.10)

SQL> ALTER SYSTEM CHECKPOINT;		<<< CHECKPOINT

DATA FILE #4:
  name #8: /oradata2/system/users01.dbf
creation size=6400 block size=8192 status=0xe head=8 tail=8 dup=1
 tablespace 4, index=5 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:2408 scn: 0x0000.02a45764 10/23/2015 14:55:44		<<< Chckpoint cnt 증가 & Checkpoint scn 고정
 Stop scn: 0xffff.ffffffff 10/23/2015 14:15:42
 Creation Checkpointed at scn:  0x0000.00004539 11/17/2011 10:57:33
 thread:1 rba:(0x1.c55b.10)

SQL> ALTER SYSTEM CHECKPOINT;		<<< CHECKPOINT
SQL> ALTER SYSTEM CHECKPOINT;		<<< CHECKPOINT
SQL> ALTER SYSTEM CHECKPOINT;		<<< CHECKPOINT
SQL> ALTER SYSTEM CHECKPOINT;		<<< CHECKPOINT

DATA FILE #4:
  name #8: /oradata2/system/users01.dbf
creation size=6400 block size=8192 status=0xe head=8 tail=8 dup=1
 tablespace 4, index=5 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:2412 scn: 0x0000.02a45764 10/23/2015 14:55:44		<<< Chckpoint cnt 증가 & Checkpoint scn 고정
 Stop scn: 0xffff.ffffffff 10/23/2015 14:15:42
 Creation Checkpointed at scn:  0x0000.00004539 11/17/2011 10:57:33
 thread:1 rba:(0x1.c55b.10)
  • 열린 백업 과 Tablespace 사용

SQL> create tablespace test datafile '/oradata2/system/test01.dbf' size 10m;

Tablespace created.

SQL> @dd

TABLESPACE         MB FILE_NAME
---------- ---------- ---------------------------------------------
SYSTEM           1024 /oradata2/system/system01.dbf
SYSAUX           1024 /oradata2/system/sysaux01.dbf
UNDOTBS1         2048 /oradata2/system/undotbs01.dbf
USERS              50 /oradata2/system/users01.dbf
TEST               10 /oradata2/system/test01.dbf

SQL> alter database datafile '/oradata2/system/test01.dbf' autoextend on;

Database altered.

SQL> create table system.gurubee1 (no number, name varchar2(20)) tablespace test;

Table created.

SQL> @ddd

FILE# TS_NAME            MB TOTAL_BLOCKS USED_BLOCKS PCT_USED
----- ---------- ---------- ------------ ----------- ----------
    1 SYSTEM           1024       131072       67720  51.67%
    2 SYSAUX           1024       131072       60088  45.84%
    3 UNDOTBS1         2048       262144      169248  64.56%
    4 USERS              50         6400              00.00%
    5 TEST               10         1280           8  00.63%

SQL> alter tablespace test begin backup;

Tablespace altered.

SQL> begin
  2    for i in 1..100000 loop
  3      insert into system.gurubee1 values (i, dbms_random.string('A', 19));
  4    end loop;
  5    commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> @ddd

FILE# TS_NAME            MB TOTAL_BLOCKS USED_BLOCKS PCT_USED
----- ---------- ---------- ------------ ----------- ----------
    1 SYSTEM           1024       131072       67720  51.67%
    2 SYSAUX           1024       131072       60088  45.84%
    3 UNDOTBS1         2048       262144      170144  64.90%
    4 USERS              50         6400              00.00%
    5 TEST               10         1280         512  40.00%

SQL> alter table system.gurubee1 modify name varchar2(40);

Table altered.

SQL> update system.gurubee1 set name = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';

100000 rows updated.

SQL> @ddd

FILE# TS_NAME            MB TOTAL_BLOCKS USED_BLOCKS PCT_USED
----- ---------- ---------- ------------ ----------- ----------
    1 SYSTEM           1024       131072       67720  51.67%
    2 SYSAUX           1024       131072       60088  45.84%
    3 UNDOTBS1         2048       262144      170144  64.90%
    4 USERS              50         6400              00.00%
    5 TEST               10         1280        1024  80.00%

-- 결론 : 열린 백업 중에도 Data File 변경 내용은 저장 된다.

SQL> @log

GROUP# MEMBER                                          MB   seq# STATUS   ARC
------ --------------------------------------------- ---- ------ -------- -----
     1 /oradata2/system/redo01.log                     50   2365 ACTIVE   YES
     2 /oradata2/system/redo02.log                     50   2366 CURRENT  NO
     3 /oradata2/system/redo03.log                     50   2364 ACTIVE   YES

-- BEGIN BACKUP 설정 시 최종 SCN 이후 변경 사항은 ROW 단위가 아닌 BLOCK 단위로 Redo Log 에 저장 됨
-- 원인 : Split Block 현상

SQL> alter tablespace test end backup;

Tablespace altered.

-- END BACKUP 은 Data File 의 마지막 SCN (BEGIN BACKUP 시점 SCN) 을 시작으로 하는 Redo Record 를 찾아서 Checkpoint 수행 함
-- 열린 백업 상태에서 Checkpoint 수행 중 END BACKUP 을 만나면 Redo Log File 에 블럭 이미지 기록이 중단 되고, Data File 에 Checkpoint 가 우선 수행 됨
  • Split Block
    • Checkpoint 가 발생 하면 버퍼 캐시의 데이터는 블럭 단위로 Data File 에 저장 된다. (보통 상태)
    • 오라클 블럭 1개는 OS 블럭 여러 개를 합친 것 - 블럭단위 (오라클:8K, 윈도우:512B, UNIX:2KB)
    • 열린 백업에 의해 Data File 이 OS 명령어로 OS 블럭 단위로 복사 됨과 동시에 오라클이 Checkpoint 등으로 블럭을 Data File 에 내려 쓸때, 특정 블럭의 백업본 일부는 블럭의 변경전 버전, 일부는 변경후 버전을 가지게 됨
  • 열린 백업 유의 사항
    • 백업 중 shutdown immediate/normal 안됨
    • 해당 테이블스페이스 OFFLINE 안됨
    • END BACKUP 없이 강제 종료 될 경우 재시작 시 복구 필요 상태가 됨

SQL> alter tablespace test begin backup;

Tablespace altered.

SQL> select a.file#, a.name, b.status, to_char(b.time, 'YYYY-MM-DD HH24:MI:SS') as time
  2  from v$datafile a, v$backup b
  3  where a.file# = b.file#;

FILE# NAME                                               STATUS          TIME
----- -------------------------------------------------- --------------- --------------------------------------
    1 /oradata2/system/system01.dbf                      NOT ACTIVE
    2 /oradata2/system/sysaux01.dbf                      NOT ACTIVE
    3 /oradata2/system/undotbs01.dbf                     NOT ACTIVE
    4 /oradata2/system/users01.dbf                       NOT ACTIVE      2015-10-23 14:55:44
    5 /oradata2/system/test01.dbf                        ACTIVE          2015-10-23 19:45:12

SQL> save bg.sql
Created file bg.sql
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2221336 bytes
Variable Size             339741416 bytes
Database Buffers          184549376 bytes
Redo Buffers                7950336 bytes
Database mounted.
ORA-10873: file 5 needs to be either taken out of backup mode or media recovered
ORA-01110: data file 5: '/oradata2/system/test01.dbf'


SQL> @bg

FILE# NAME                                               STATUS          TIME
----- -------------------------------------------------- --------------- --------------------------------------
    1 /oradata2/system/system01.dbf                      NOT ACTIVE
    2 /oradata2/system/sysaux01.dbf                      NOT ACTIVE
    3 /oradata2/system/undotbs01.dbf                     NOT ACTIVE
    4 /oradata2/system/users01.dbf                       NOT ACTIVE      2015-10-23 14:55:44
    5 /oradata2/system/test01.dbf                        ACTIVE          2015-10-23 19:45:12

SQL> alter tablespace test end backup;

Tablespace altered.

SQL> alter database open;

Database altered.

SQL> @bg

FILE# NAME                                               STATUS          TIME
----- -------------------------------------------------- --------------- --------------------------------------
    1 /oradata2/system/system01.dbf                      NOT ACTIVE
    2 /oradata2/system/sysaux01.dbf                      NOT ACTIVE
    3 /oradata2/system/undotbs01.dbf                     NOT ACTIVE
    4 /oradata2/system/users01.dbf                       NOT ACTIVE      2015-10-23 14:55:44
    5 /oradata2/system/test01.dbf                        NOT ACTIVE      2015-10-23 19:49:47
  • (실습) Hot Backup 수행하기

SQL> !cat open_backup.sql
conn / as sysdba;

alter tablespace users begin backkup;
!cp /oradata2/system/users01.dbf /home/oracle/backup/open
alter tablespace users end backup;

alter tablespace sysaux begin backkup;
!cp /oradata2/system/sysaux01.dbf /home/oracle/backup/open
alter tablespace sysaux end backup;

alter tablespace undotbs1 begin backkup;
!cp /oradata2/system/undotbs01.dbf /home/oracle/backup/open
alter tablespace undotbs1 end backup;

alter tablespace system begin backkup;
!cp /oradata2/system/system01.dbf /home/oracle/backup/open
alter tablespace system end backup;

alter tablespace test begin backkup;
!cp /oradata2/system/test01.dbf /home/oracle/backup/open
alter tablespace test end backup;

alter database backup controlfile to '/home/oracle/backup/open/control01.ctl';

SQL> @open_backup.sql
Connected.

Tablespace altered.



Tablespace altered.


Tablespace altered.



Tablespace altered.


Tablespace altered.



Tablespace altered.


Tablespace altered.



Tablespace altered.


Tablespace altered.



Tablespace altered.


Database altered.

SQL> alter system checkpoint;

System altered.

SQL>

-- Alert Log
Fri Oct 23 20:03:43 2015
alter tablespace users begin backup
Completed: alter tablespace users begin backup
alter tablespace users end backup
Completed: alter tablespace users end backup
alter tablespace sysaux begin backup
Completed: alter tablespace sysaux begin backup
alter tablespace sysaux end backup
Completed: alter tablespace sysaux end backup
alter tablespace undotbs1 begin backup
Completed: alter tablespace undotbs1 begin backup
Fri Oct 23 20:04:02 2015
alter tablespace undotbs1 end backup
Completed: alter tablespace undotbs1 end backup
alter tablespace system begin backup
Completed: alter tablespace system begin backup
alter tablespace system end backup
Completed: alter tablespace system end backup
alter tablespace test begin backup
Completed: alter tablespace test begin backup
alter tablespace test end backup
Completed: alter tablespace test end backup
alter database backup controlfile to '/home/oracle/backup/open/control01.ctl'
Completed: alter database backup controlfile to '/home/oracle/backup/open/control01.ctl'
  • (실습) Password File 관리하기

-- Password File 초기화
$ rm $ORACLE_HOME/dbs/orapwUTF8
$ orapwd file=$ORACLE_HOME/dbs/orapwUTF8

Enter password for SYS:
$ ls -al $ORACLE_HOME/dbs/orapwUTF8
-rw-r-----    1 oracle   dba            1536 Oct 23 20:13 /sw/oracle/app/oracle/product/11.2.0/dbs/orapwUTF8

-- 로컬 sysdba 로긴 시 패스워드 안물어봄
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 23 20:08:47 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option

-- 로컬 sysdba 로긴 시 패스워드 물어보게
$ cat >> sqlnet.ora
sqlnet.authentication_services=(none)
$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /sw/oracle/app/oracle/product/11.2.0/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES)

ADR_BASE = /sw/oracle/app/oracle

sqlnet.authentication_services=(none)		<<< 설정
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 23 20:09:54 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name:

$ sqlplus sys/welcome1 as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 23 20:16:11 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option

SQL>
  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2015년 하반기 - 오라클 데이터베이스 스터디
  4. Chap.03 Oracle Backup