[Oracle DBA]Backup & Recovery

DATABASE(oracleDB 11g)/DBA

[Oracle DBA]Backup & Recovery

SEUNGSAMI 2019. 4. 10. 16:40

Backup & Recovery

Backup & Recovery 실습을 위한 준비 작업

dbca를 이용해서 기존의 DB(나는 orcl2)를 삭제

삭제를 위해 데이터파일에 접근해야하므로 인스턴스가 실행되어야 한다.

dbca를 이용해 orcl2 DB 생성

global db name : orcl2.example.com

db_name = orcl2
db_domain = example.com
service_names = orcl2.example.com

sid : orcl2
storage : file system

data : /u01/app/oracle/oradata/orcl2 (default)
FRA : /u01/app/oracle/flash_recovery_area/orcl2 (recovery destination)

data characterset : AL16UTF8
password management : unlock, password

sh/sh
hr/hr
scott/tiger

백업

SYS@orcl2> startup force
[orcl2:~]$ dbca

DB삭제

delete 체크 후 Next

orcl2 체크 후 Finish 하고 진행중 뜨는 알림은 전부 yes

-- 정상으로 삭제되었는지 확인

[orcl2:~]$ ls /home/oracle/*.dbf
ls: *.dbf: No such file or directory

[orcl2:~]$ dbca

DB 설치

Create a Database 체크 후Next

General Purpose or Transaction Procession 체크 후 Next

Glocal Database Name에 orcl2.example.com을 적으면 SID는 자동으로 작성된다. 그후 Next

공통 패스워드 선택 후, oracle_4U로 설정 후 Next

Use Common Location for All Database Files 체크 후 경로 확인 후 Next

Sample Schemas 체크 후 Next

메모리 사이즈 적절하게 지정 (550M)

Character Sets 에서 Use Unicode (AL32UTF8) 체크 후 Next

Finish

이러한 창이 뜨면 Password Management... 을 눌러서

SCOTT/tiger

HR/HR

SH/SH

계정을 unlock 하고 패스워드를 설정하고 OK

그러면 패스워드가 짧다고 경고가 나오는데 무시하면 끝.

백업(초기상태) : Closed DB Backup (Offline, Cold)

가장 중요한 것은 DB 가 SHUTDOWN된 상태에서 진행할 것.

[orcl2:~]$ mkdir /home/oracle/BACKUP_ORCL2
-- 백업을 위해 백업 디렉토리 생성


======백업 해야 할 목록======
SYS@orcl2>select name from v$datafile
          union all
          select member from v$logfile
          union all
          select name from v$controlfile;

파라미터 파일, 패스워드 파일
네트워크 구성파일 : listener.ora, tnsnames.ora, sqlnet.ora
============================

SYS@orcl2> !cat db_list.sql
set pages 0
set head off
set feed off
set echo off
spool orcl2_backup.sh
select 'cp ' || name || ' /home/oracle/BACKUP_ORCL2'
from (select name from v$datafile
      union all
      select member from v$logfile
      union all
      select name from v$controlfile)
/
spool off
set pages 120
set head on
set feed on
set echo on


SYS@orcl2> @db_list
cp /u01/app/oracle/orcl2/system01.dbf /home/oracle/BACKUP_ORCL2
cp /u01/app/oracle/orcl2/sysaux01.dbf /home/oracle/BACKUP_ORCL2
cp /u01/app/oracle/orcl2/undotbs01.dbf /home/oracle/BACKUP_ORCL2
cp /u01/app/oracle/orcl2/users01.dbf /home/oracle/BACKUP_ORCL2
cp /u01/app/oracle/orcl2/example01.dbf /home/oracle/BACKUP_ORCL2
cp /u01/app/oracle/orcl2/redo03.log /home/oracle/BACKUP_ORCL2
cp /u01/app/oracle/orcl2/redo02.log /home/oracle/BACKUP_ORCL2
cp /u01/app/oracle/orcl2/redo01.log /home/oracle/BACKUP_ORCL2
cp /u01/app/oracle/orcl2/control01.ctl /home/oracle/BACKUP_ORCL2
cp /u01/app/oracle/flash_recovery_area/orcl2/control02.ctl /home/oracle/BACKUP_ORCL2


SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


[orcl2:~]$ ls -al orcl2_backup.sh
-rwxr-xr-x 1 oracle oinstall 2020 Apr 10 16:46 orcl2_backup.sh
-- 권한 확인

[orcl2:~]$ . orcl2_backup.sh

[orcl2:BACKUP_ORCL2]$ ll
total 1597668
-rw-r----- 1 oracle oinstall   9748480 Apr 10 16:51 control01.ctl
-rw-r----- 1 oracle oinstall   9748480 Apr 10 16:51 control02.ctl
-rw-r----- 1 oracle oinstall 104865792 Apr 10 16:51 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 Apr 10 16:51 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Apr 10 16:51 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Apr 10 16:51 redo03.log
-rw-r----- 1 oracle oinstall 524296192 Apr 10 16:51 sysaux01.dbf
-rw-r----- 1 oracle oinstall 713039872 Apr 10 16:51 system01.dbf
-rw-r----- 1 oracle oinstall 110108672 Apr 10 16:51 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Apr 10 16:51 users01.dbf


[orcl2:BACKUP_ORCL2]$ mkdir OTHERS

[orcl2:OTHERS]$ cp $ORACLE_HOME/dbs/spfileorcl2.ora .
[orcl2:OTHERS]$ cp $ORACLE_HOME/dbs/orapworcl2 .     
[orcl2:OTHERS]$ cp $ORACLE_HOME/network/admin/tnsnames.ora .
[orcl2:OTHERS]$ cp $ORACLE_HOME/network/admin/sqlnet.ora .
만약에 sqlnet.ora가 없을경우=============
vi sqlnet.ora
#SQLNET.AUTHENTICATION_SERVICES=(NONE)
직접 만들어준다
================================
[orcl2:OTHERS]$ cp /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora .

[orcl2:OTHERS]$ ll

-rw-r--r-- 1 oracle oinstall  908 Apr 10 16:55 listener.ora
-rw-r----- 1 oracle oinstall 1536 Apr 10 16:53 orapworcl2
-rw-r----- 1 oracle oinstall 2560 Apr 10 16:53 spfileorcl2.ora
-rw-r----- 1 oracle oinstall 1610 Apr 10 16:58 tnsnames.ora

백업 완료

BACKUP & RECOVERY

Redo log

DB의 모든 변경사항을 기록
recovery의 용도
log buffer
LGWR
Redo log file

(online) redo log file

마지막 체크포인트 이후의 리두가 instance recovery시에 roll forward

(offline) archive redo log file

media recovery
마지막 백업 이후의 모든 archive redo + online redo

Recovery 가능성 구성

정기적 백업
Multiplexing

컨트롤 파일의 다중화

spfile 수정
db 종료
os 상에서 컨트롤 파일을 카피
startup

SYS@orcl2>
alter system set control_files = '/u01/app/oracle/orcl2/control01.ctl' ,  
                        '/u01/app/oracle/flash_recovery_area/orcl2/control02.ctl',
                        '/u01/app/oracle/orcl2/control03.ctl'
                        scope=spfile;
System altered.

SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.



[orcl2:~]$ cp -av /u01/app/oracle/orcl2/control01.ctl  /u01/app/oracle/orcl2/control03.ctl

SYS@orcl2> startup
ORACLE instance started.
Total System Global Area  431038464 bytes
Fixed Size                  1337016 bytes
Variable Size             146803016 bytes
Database Buffers          276824064 bytes
Redo Buffers                6074368 bytes
Database mounted.
Database opened.

리두 로그 파일(그룹)의 다중화

리두로그 멤버 추가
리두로그 그룹 추가

SYS@orcl2> select group#, sequence#, members, status, archived, bytes
           from v$log;

    GROUP#  SEQUENCE#    MEMBERS STATUS           ARC      BYTES
---------- ---------- ---------- ---------------- --- ----------
         1          4          1 INACTIVE         NO    52428800
         2          5          1 INACTIVE         NO    52428800
         3          6          1 CURRENT          NO    52428800
SYS@orcl2> save log
Created file log.sql



SYS@orcl2> select group#, member, status from v$logfile;

    GROUP# MEMBER                                             STATUS
---------- -------------------------------------------------- -------
         3 /u01/app/oracle/orcl2/redo03.log
         2 /u01/app/oracle/orcl2/redo02.log
         1 /u01/app/oracle/orcl2/redo01.log

SYS@orcl2> save logfile
Created file logfile.sql

-- 리두로그 멤버 추가
SYS@orcl2>
alter database add logfile member
'/u01/app/oracle/flash_recovery_area/orcl2/redo01b.log' to group 1,
'/u01/app/oracle/flash_recovery_area/orcl2/redo02b.log' to group 2,
'/u01/app/oracle/flash_recovery_area/orcl2/redo03b.log' to group 3;

SYS@orcl2> @log
    GROUP#  SEQUENCE#    MEMBERS STATUS           ARC      BYTES
---------- ---------- ---------- ---------------- --- ----------
         1          4          2 INACTIVE         NO    52428800
         2          5          2 INACTIVE         NO    52428800
         3          6          2 CURRENT          NO    52428800


SYS@orcl2> @logfile
    GROUP# MEMBER                                                       STATUS
---------- ------------------------------------------------------------ -------
         3 /u01/app/oracle/orcl2/redo03.log
         2 /u01/app/oracle/orcl2/redo02.log
         1 /u01/app/oracle/orcl2/redo01.log
         1 /u01/app/oracle/flash_recovery_area/orcl2/redo01b.log        INVALID
         2 /u01/app/oracle/flash_recovery_area/orcl2/redo02b.log        INVALID
         3 /u01/app/oracle/flash_recovery_area/orcl2/redo03b.log        INVALID


6 rows selected.
==================================================================================================
(cf, ASM system 인 경우 )
SYS@orcl2>  ALTER DATABASE ADD LOGFILE MEMBER '+FRA' TO GROUP 2;
==================================================================================================

-- 리두로그 그룹 추가

SYS@orcl2> alter database add logfile group 4
          '/u01/app/oracle/orcl2/redo04.log' size 5m ;

Database altered.

SYS@orcl2> @log
    GROUP#  SEQUENCE#    MEMBERS STATUS           ARC      BYTES
---------- ---------- ---------- ---------------- --- ----------
         1          4          2 INACTIVE         NO    52428800
         2          5          2 INACTIVE         NO    52428800
         3          6          2 CURRENT          NO    52428800
         4          0          1 UNUSED           YES    5242880

SYS@orcl2> alter system switch logfile;
System altered.
-- status가 변경된다.

SYS@orcl2> @logfile
    GROUP# MEMBER                                                       STATUS
---------- ------------------------------------------------------------ -------
         3 /u01/app/oracle/orcl2/redo03.log
         2 /u01/app/oracle/orcl2/redo02.log
         1 /u01/app/oracle/orcl2/redo01.log
         1 /u01/app/oracle/flash_recovery_area/orcl2/redo01b.log        
         2 /u01/app/oracle/flash_recovery_area/orcl2/redo02b.log       
         3 /u01/app/oracle/flash_recovery_area/orcl2/redo03b.log       
         4 /u01/app/oracle/orcl2/redo04.log


7 rows selected.


SYS@orcl2> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Current log sequence           7
-- 다음에 쓸 로그파일을 볼 수 있다.
==================================================================================================
*** ASM test : ASM이 되어있을 경우에 진행

SYS@orcl2> alter database add logfile group 4
           '+DATA' size 5m ;


SYS@orcl2> alter  database add logfile member
          '/u01/app/oracle/flash_recovery_area/orcl2/redo04b.log' to group 4;


SYS@orcl2> ALTER DATABASE ADD LOGFILE MEMBER '+FRA' TO GROUP 4;
==================================================================================================


-- 리두로그 그룹 삭제
-- 그룹삭제를 위해서는 일단 삭제하려는 logfile의 상태가 current가 아니여야한다.
SYS@orcl2> alter system switch logfile;
or
SYS@orcl2> alter system checkpoint;

SYS@orcl2> @log
    GROUP#  SEQUENCE#    MEMBERS STATUS           ARC      BYTES
---------- ---------- ---------- ---------------- --- ----------
         1         12          2 INACTIVE         NO    52428800
         2         13          2 ACTIVE           NO    52428800
         3         14          2 CURRENT          NO    52428800
         4         11          1 INACTIVE         NO     5242880


SYS@orcl2> alter database drop logfile group 4;


SYS@orcl2> @log

    GROUP#  SEQUENCE#    MEMBERS STATUS           ARC      BYTES
---------- ---------- ---------- ---------------- --- ----------
         1         12          2 INACTIVE         NO    52428800
         2         13          2 INACTIVE         NO    52428800
         3         14          2 CURRENT          NO    52428800


SYS@orcl2> @logfile

    GROUP# MEMBER                                                       STATUS
---------- ------------------------------------------------------------ -------
         3 /u01/app/oracle/orcl2/redo03.log
         2 /u01/app/oracle/orcl2/redo02.log
         1 /u01/app/oracle/orcl2/redo01.log
         1 /u01/app/oracle/flash_recovery_area/orcl2/redo01b.log
         2 /u01/app/oracle/flash_recovery_area/orcl2/redo02b.log
         3 /u01/app/oracle/flash_recovery_area/orcl2/redo03b.log

6 rows selected.

리두 로그의 아카이브된 복사본을 보유

DB의 log mode의 변경 : noarchivelog ==> archivelog

DB 정상 종료 : shutdown immediate
DB mount : startup mount

archive log list
select log_mode from v$database
@log : archivced 컬럼값
위 세개 확인

DB 로그 모드의 변경 : alter database archivelog;

archive log list
select log_mode from v$database
@log : archivced 컬럼값
위 세개 확인

DB 실행 : alter database open;

현제 DB 로그모드 보는법

archive log list
v$database의 log_mode

SYS@orcl2> show parameter recovery


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area

SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@orcl2> startup mount
ORACLE instance started.


Total System Global Area  431038464 bytes
Fixed Size                  1337016 bytes
Variable Size             146803016 bytes
Database Buffers          276824064 bytes
Redo Buffers                6074368 bytes
Database mounted.


SYS@orcl2> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     15
Current log sequence           17


SYS@orcl2> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG


SYS@orcl2> @log

    GROUP#  SEQUENCE#    MEMBERS STATUS           ARC      BYTES
---------- ---------- ---------- ---------------- --- ----------
         1         15          2 INACTIVE         NO    52428800
         2         16          2 INACTIVE         NO    52428800
         3         17          2 CURRENT          NO    52428800


SYS@orcl2> alter database archivelog;
Database altered.


SYS@orcl2> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     15
Next log sequence to archive   17
Current log sequence           17


SYS@orcl2> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG


SYS@orcl2> @log

    GROUP#  SEQUENCE#    MEMBERS STATUS           ARC      BYTES
---------- ---------- ---------- ---------------- --- ----------
         1         15          2 INACTIVE         YES   52428800
         3         17          2 CURRENT          NO    52428800
         2         16          2 INACTIVE         YES   52428800
-- ARC가 YES로 변경되었다.
-- 실제로 Archive가 존재하진 않는다.


[orcl2:~]$ cd /u01/app/oracle/flash_recovery_area/orcl2


SYS@orcl2> alter system switch logfile;
System altered.


[orcl2:~]$ cd /u01/app/oracle/flash_recovery_area/ORCL2/archivelog/2019_04_11/                         


[orcl2:2019_04_11]$ ll
total 260
-rw-r----- 1 oracle dba 261632 Apr 11 14:27 o1_mf_1_17_gbxn1vof_.arc


SYS@orcl2> alter system switch logfile;
System altered.

[orcl2:2019_04_11]$ ll -al
total 396
drwxr-x--- 2 oracle dba   4096 Apr 11 14:30 .
drwxr-x--- 3 oracle dba   4096 Apr 11 14:27 ..
-rw-r----- 1 oracle dba 261632 Apr 11 14:27 o1_mf_1_17_gbxn1vof_.arc
-rw-r----- 1 oracle dba 124416 Apr 11 14:30 o1_mf_1_18_gbxn8k9s_.arc



-- 다시 noarchive log로 바꾸기
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


SYS@orcl2> startup mount
ORACLE instance started.
Total System Global Area  431038464 bytes
Fixed Size                  1337016 bytes
Variable Size             146803016 bytes
Database Buffers          276824064 bytes
Redo Buffers                6074368 bytes
Database mounted.


SYS@orcl2> alter database noarchivelog;
Database altered.


SYS@orcl2> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     17
Current log sequence           19


SYS@orcl2> @log
    GROUP#  SEQUENCE#    MEMBERS STATUS           ARC      BYTES
---------- ---------- ---------- ---------------- --- ----------
         1         18          2 INACTIVE         YES   52428800
         3         17          2 INACTIVE         YES   52428800
         2         19          2 CURRENT          NO    52428800

ARCHIVE LOG MODE ON

BACKUP

NOARCHIVE

Closed DB Backup = Cold, Offline

일관된 DB가 필요

Whole DB Backup

모든 데이터파일을 Backup
전체 데이터베이스

Full DB Backup

100MB DB이면 100MB짜리 Backup
파일의 전체

-- No archived recovery resotre

HR@orcl2> create table test(id number) tablespace example;
Table created.


HR@orcl2> insert into test(id) values(1);
1 row created.


HR@orcl2> commit;
Commit complete.


HR@orcl2> select * from test;

        ID
----------
         1


SYS@orcl2> alter system switch logfile;
System altered.


SYS@orcl2> /
System altered.


SYS@orcl2> /
System altered.


SYS@orcl2> select group#, sequence#, members, status, archived, bytes
           from v$log; 

    GROUP#  SEQUENCE#    MEMBERS STATUS           ARC      BYTES
---------- ---------- ---------- ---------------- --- ----------
         1         21          2 INACTIVE         NO    52428800
         2         22          2 CURRENT          NO    52428800
         3         20          2 INACTIVE         NO    52428800


SYS@orcl2> SELECT f.file_name
           FROM dba_extents e, dba_data_files f
           WHERE e.file_id = f.file_id

           AND e.segment_name = 'TEST'

           AND e.owner = 'HR';

FILE_NAME
----------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/example01.dbf


SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

 -- 장애유발
[orcl2:noarch]$ rm -f /u01/app/oracle/orcl2/example01.dbf


SYS@orcl2> startup
ORACLE instance started.

Total System Global Area  431038464 bytes
Fixed Size                  1337016 bytes
Variable Size             146803016 bytes
Database Buffers          276824064 bytes
Redo Buffers                6074368 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/orcl2/example01.dbf'


-- restore 
SYS@orcl2> ! cp -av /home/oracle/backup/noarch/example01.dbf /u01/app/oracle/orcl2/example01.dbf
`/home/oracle/backup/noarch/example01.dbf' -> `/u01/app/oracle/orcl2/example01.dbf'

SYS@orcl2> recover database;
ORA-00279: change 849574 generated at 04/11/2019 10:03:27 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL2/archivelog/2019_04_11/o1_mf_1_6_%u_.arc
ORA-00280: change 849574 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/ORCL2/archivelog/2019_04_11/o1_mf_1_6_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/ORCL2/archivelog/2019_04_11/o1_mf_1_6_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
-- 안되는 것을 확인

SYS@orcl2> select status from v$instance;

STATUS
------------
MOUNTED


-- Offline + Whole Backup을 Restore

SYS@orcl2> ! cp -av /home/oracle/backup/noarch/*.* /u01/app/oracle/orcl2/
`/home/oracle/backup/noarch/afiedt.buf' -> `/u01/app/oracle/orcl2/afiedt.buf'
`/home/oracle/backup/noarch/control01.ctl' -> `/u01/app/oracle/orcl2/control01.ctl'
`/home/oracle/backup/noarch/control02.ctl' -> `/u01/app/oracle/orcl2/control02.ctl'
`/home/oracle/backup/noarch/db_list2.sql' -> `/u01/app/oracle/orcl2/db_list2.sql'
`/home/oracle/backup/noarch/db_noarch_list.sql' -> `/u01/app/oracle/orcl2/db_noarch_list.sql'
`/home/oracle/backup/noarch/example01.dbf' -> `/u01/app/oracle/orcl2/example01.dbf'
`/home/oracle/backup/noarch/redo01.log' -> `/u01/app/oracle/orcl2/redo01.log'
`/home/oracle/backup/noarch/redo02.log' -> `/u01/app/oracle/orcl2/redo02.log'
`/home/oracle/backup/noarch/redo03.log' -> `/u01/app/oracle/orcl2/redo03.log'
`/home/oracle/backup/noarch/sysaux01.dbf' -> `/u01/app/oracle/orcl2/sysaux01.dbf'
`/home/oracle/backup/noarch/system01.dbf' -> `/u01/app/oracle/orcl2/system01.dbf'
`/home/oracle/backup/noarch/undotbs01.dbf' -> `/u01/app/oracle/orcl2/undotbs01.dbf'
`/home/oracle/backup/noarch/users01.dbf' -> `/u01/app/oracle/orcl2/users01.dbf'



SYS@orcl2> startup force
ORACLE instance started.

Total System Global Area  431038464 bytes
Fixed Size                  1337016 bytes
Variable Size             146803016 bytes
Database Buffers          276824064 bytes
Redo Buffers                6074368 bytes
ORA-00214: control file '/u01/app/oracle/flash_recovery_area/orcl2/control02.ctl' version 907
inconsistent with file '/u01/app/oracle/orcl2/control01.ctl' version 725

SYS@orcl2> !cp -av /home/oracle/backup/noarch/control02.ctl /u01/app/oracle/flash_recovery_area/orcl2/

`/home/oracle/backup/noarch/control02.ctl' -> `/u01/app/oracle/flash_recovery_area/orcl2/control02.ctl'
-- 백업해 놓은 noarch의 control02는 원래 flash_recovery_area에 있었으므로 옮겨 준다.



SYS@orcl2> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SYS@orcl2> startup force
ORACLE instance started.

Total System Global Area  431038464 bytes
Fixed Size                  1337016 bytes
Variable Size             146803016 bytes
Database Buffers          276824064 bytes
Redo Buffers                6074368 bytes
ORA-00214: control file '/u01/app/oracle/orcl2/control03.ctl' version 907 inconsistent with file
'/u01/app/oracle/orcl2/control01.ctl' version 725

SYS@orcl2> !cp -av /home/oracle/backup/noarch/control01.ctl /home/oracle/backup/noarch/control03.ctl
`/home/oracle/backup/noarch/control01.ctl' -> `/home/oracle/backup/noarch/control03.ctl'
-- 다중화 전의 백업파일들이므로, control03은 새로 만들어주어야 한다.


SYS@orcl2> startup force
ORACLE instance started.

Total System Global Area  431038464 bytes
Fixed Size                  1337016 bytes
Variable Size             146803016 bytes
Database Buffers          276824064 bytes
Redo Buffers                6074368 bytes
Database mounted.
Database opened.


HR@orcl2> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
COUNTRIES                      TABLE
DEPARTMENTS                    TABLE
EMPLOYEES                      TABLE
EMP_DETAILS_VIEW               VIEW
JOBS                           TABLE
JOB_HISTORY                    TABLE
LOCATIONS                      TABLE
REGIONS                        TABLE

8 rows selected.
-- test 테이블은 없어진 상태이다.

/* Noarchivelog Mode에서 Backup이 없을 경우 Drop으로 포기 */

SYS@orcl2> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------
USERS                          /u01/app/oracle/orcl2/users01.dbf
UNDOTBS1                       /u01/app/oracle/orcl2/undotbs01.dbf
SYSAUX                         /u01/app/oracle/orcl2/sysaux01.dbf
SYSTEM                         /u01/app/oracle/orcl2/system01.dbf
EXAMPLE                        /u01/app/oracle/orcl2/example01.dbf


SYS@orcl2> create tablespace insa_tbs datafile '/u01/app/oracle/orcl2/insa_tbs01.dbf' size 10m;

Tablespace created.


SYS@orcl2> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------
USERS                          /u01/app/oracle/orcl2/users01.dbf
UNDOTBS1                       /u01/app/oracle/orcl2/undotbs01.dbf
SYSAUX                         /u01/app/oracle/orcl2/sysaux01.dbf
SYSTEM                         /u01/app/oracle/orcl2/system01.dbf
EXAMPLE                        /u01/app/oracle/orcl2/example01.dbf
INSA_TBS                       /u01/app/oracle/orcl2/insa_tbs01.dbf


SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@orcl2> ! rm -f /u01/app/oracle/orcl2/insa_tbs01.dbf

SYS@orcl2> startup
ORACLE instance started.
Total System Global Area  431038464 bytes
Fixed Size                  1337016 bytes
Variable Size             146803016 bytes
Database Buffers          276824064 bytes
Redo Buffers                6074368 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/orcl2/insa_tbs01.dbf'


SYS@orcl2> alter database datafile '/u01/app/oracle/orcl2/insa_tbs01.dbf' offline drop;
Database altered.


SYS@orcl2> select name, status from v$datafile;

NAME                                               STATUS
-------------------------------------------------- -------
/u01/app/oracle/orcl2/system01.dbf                 SYSTEM
/u01/app/oracle/orcl2/sysaux01.dbf                 ONLINE
/u01/app/oracle/orcl2/undotbs01.dbf                ONLINE
/u01/app/oracle/orcl2/users01.dbf                  ONLINE
/u01/app/oracle/orcl2/example01.dbf                ONLINE
/u01/app/oracle/orcl2/insa_tbs01.dbf               OFFLINE

6 rows selected.


SYS@orcl2> alter database open;
Database altered.


SYS@orcl2> drop tablespace insa_tbs including contents and datafiles;
Tablespace dropped.


SYS@orcl2> select name, status from v$datafile;

NAME                                               STATUS
-------------------------------------------------- -------
/u01/app/oracle/orcl2/system01.dbf                 SYSTEM
/u01/app/oracle/orcl2/sysaux01.dbf                 ONLINE
/u01/app/oracle/orcl2/undotbs01.dbf                ONLINE
/u01/app/oracle/orcl2/users01.dbf                  ONLINE
/u01/app/oracle/orcl2/example01.dbf                ONLINE

-- noarchived data control redo new locate

SYS@orcl2> select name from v$controlfile;

NAME
--------------------------------------------------------
/u01/app/oracle/orcl2/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl2/control02.ctl


SYS@orcl2> alter system set control_files='/u01/app/oracle/orcl2/control01.ctl' scope=spfile;
System altered.

SYS@orcl2> startup force
ORACLE instance started.
Total System Global Area  431038464 bytes
Fixed Size                  1337016 bytes
Variable Size             146803016 bytes
Database Buffers          276824064 bytes
Redo Buffers                6074368 bytes
Database mounted.
Database opened.


SYS@orcl2> select name from v$controlfile;

NAME
--------------------------------------------------
/u01/app/oracle/orcl2/control01.ctl


SYS@orcl2> create pfile='/home/oracle/backup/noarch/initorcl2_0411.ora' from spfile;
File created.

SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@orcl2> @db_list2
cp -av /home/oracle/backup/noarch/system01.dbf /u01/app/oracle/orcl2/system01.dbf
cp -av /home/oracle/backup/noarch/sysaux01.dbf /u01/app/oracle/orcl2/sysaux01.dbf
cp -av /home/oracle/backup/noarch/undotbs01.dbf /u01/app/oracle/orcl2/undotbs01.dbf
cp -av /home/oracle/backup/noarch/users01.dbf /u01/app/oracle/orcl2/users01.dbf
cp -av /home/oracle/backup/noarch/example01.dbf /u01/app/oracle/orcl2/example01.dbf
cp -av /home/oracle/backup/noarch/redo03.log /u01/app/oracle/orcl2/redo03.log
cp -av /home/oracle/backup/noarch/redo02.log /u01/app/oracle/orcl2/redo02.log
cp -av /home/oracle/backup/noarch/redo01.log /u01/app/oracle/orcl2/redo01.log
cp -av /home/oracle/backup/noarch/control01.ctl /u01/app/oracle/orcl2/control01.ctl


[orcl2:~]$ . orcl2_noarch_backup.sh
`/u01/app/oracle/orcl2/system01.dbf' -> `/home/oracle/backup/noarch/system01.dbf'
`/u01/app/oracle/orcl2/sysaux01.dbf' -> `/home/oracle/backup/noarch/sysaux01.dbf'
`/u01/app/oracle/orcl2/undotbs01.dbf' -> `/home/oracle/backup/noarch/undotbs01.dbf'
`/u01/app/oracle/orcl2/users01.dbf' -> `/home/oracle/backup/noarch/users01.dbf'
`/u01/app/oracle/orcl2/example01.dbf' -> `/home/oracle/backup/noarch/example01.dbf'
`/u01/app/oracle/orcl2/redo03.log' -> `/home/oracle/backup/noarch/redo03.log'
`/u01/app/oracle/orcl2/redo02.log' -> `/home/oracle/backup/noarch/redo02.log'
`/u01/app/oracle/orcl2/redo01.log' -> `/home/oracle/backup/noarch/redo01.log'
`/u01/app/oracle/orcl2/control01.ctl' -> `/home/oracle/backup/noarch/control01.ctl'
`/u01/app/oracle/flash_recovery_area/orcl2/control02.ctl' -> `/home/oracle/backup/noarch/control02.ctl'


[orcl2:~]$ cd /u01/app/oracle/orcl2/
[orcl2:orcl2]$ rm *

SYS@orcl2> startup
ORACLE instance started.

Total System Global Area  431038464 bytes
Fixed Size                  1337016 bytes
Variable Size             146803016 bytes
Database Buffers          276824064 bytes
Redo Buffers                6074368 bytes
ORA-00205: error in identifying control file, check alert log for more info
-- 파라미터파일만 읽기 때문에 instance만 실행된다.

-- 새로운 디바이스로 복구작업
[orcl2:~]$ mkdir orcl_data
[orcl2:~]$ cd orcl_data
[orcl2:orcl_data]$ cp -av /home/oracle/backup/noarch/* .
`/home/oracle/backup/noarch/afiedt.buf' -> `./afiedt.buf'
`/home/oracle/backup/noarch/control01.ctl' -> `./control01.ctl'
`/home/oracle/backup/noarch/control02.ctl' -> `./control02.ctl'
`/home/oracle/backup/noarch/db_list2.sql' -> `./db_list2.sql'
`/home/oracle/backup/noarch/db_noarch_list.sql' -> `./db_noarch_list.sql'
`/home/oracle/backup/noarch/example01.dbf' -> `./example01.dbf'
`/home/oracle/backup/noarch/initorcl2_0411.ora' -> `./initorcl2_0411.ora'
`/home/oracle/backup/noarch/redo01.log' -> `./redo01.log'
`/home/oracle/backup/noarch/redo02.log' -> `./redo02.log'
`/home/oracle/backup/noarch/redo03.log' -> `./redo03.log'
`/home/oracle/backup/noarch/sysaux01.dbf' -> `./sysaux01.dbf'
`/home/oracle/backup/noarch/system01.dbf' -> `./system01.dbf'
`/home/oracle/backup/noarch/undotbs01.dbf' -> `./undotbs01.dbf'
`/home/oracle/backup/noarch/users01.dbf' -> `./users01.dbf'

[orcl2:noarch]$ ls *.ora
initorcl2_0411.ora

[orcl2:noarch]$ vi initorcl2_0411.ora

        ...............................
*.control_files='/u01/oracle/orcl_data/control01.ctl'

        ...............................
:wq


SYS@orcl2> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.


SYS@orcl2> startup pfile=/home/oracle/backup/noarch/initorcl2_0411.ora mount
ORACLE instance started.

Total System Global Area  431038464 bytes
Fixed Size                  1337016 bytes
Variable Size             146803016 bytes
Database Buffers          276824064 bytes
Redo Buffers                6074368 bytes
Database mounted.


SYS@orcl2> select file#, name, status from v$datafile;

     FILE# NAME                                               STATUS
---------- -------------------------------------------------- -------
         1 /u01/app/oracle/orcl2/system01.dbf                 SYSTEM
         2 /u01/app/oracle/orcl2/sysaux01.dbf                 ONLINE
         3 /u01/app/oracle/orcl2/undotbs01.dbf                ONLINE
         4 /u01/app/oracle/orcl2/users01.dbf                  ONLINE
         5 /u01/app/oracle/orcl2/example01.dbf                ONLINE

SYS@orcl2> select member from v$logfile;

MEMBER

----------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/redo03.log
/u01/app/oracle/orcl2/redo02.log
/u01/app/oracle/orcl2/redo01.log

SYS@orcl2> alter database rename file '/u01/app/oracle/orcl2/system01.dbf' 

                                        to  '/home/oracle/orcl_data/system01.dbf' ;
Database altered.


SYS@orcl2> alter database rename file '/u01/app/oracle/orcl2/sysaux01.dbf' 

                                        to  '/home/oracle/orcl_data/sysaux01.dbf' ;
Database altered.


SYS@orcl2> alter database rename file '/u01/app/oracle/orcl2/undotbs01.dbf' 

                                        to  '/home/oracle/orcl_data/undotbs01.dbf' ;
Database altered.


SYS@orcl2> alter database rename file '/u01/app/oracle/orcl2/users01.dbf' 

                                        to  '/home/oracle/orcl_data/users01.dbf' ;
Database altered.


SYS@orcl2> alter database rename file '/u01/app/oracle/orcl2/example01.dbf' 

                                        to  '/home/oracle/orcl_data/example01.dbf' ;
Database altered.


SYS@orcl2> alter database rename file '/u01/app/oracle/orcl2/redo01.log'
                                        to  '/home/oracle/orcl_data/redo01.log' ;
Database altered.


SYS@orcl2> alter database rename file '/u01/app/oracle/orcl2/redo02.log'
                                        to  '/home/oracle/orcl_data/redo02.log' ;
Database altered.


SYS@orcl2> alter database rename file '/u01/app/oracle/orcl2/redo03.log'
                                        to  '/home/oracle/orcl_data/redo03.log' ;
Database altered.


SYS@orcl2> select file#, name, status from v$datafile;

     FILE# NAME                                               STATUS
---------- -------------------------------------------------- -------
         1 /home/oracle/orcl_data/system01.dbf                SYSTEM
         2 /home/oracle/orcl_data/sysaux01.dbf                ONLINE
         3 /home/oracle/orcl_data/undotbs01.dbf               ONLINE
         4 /home/oracle/orcl_data/users01.dbf                 ONLINE
         5 /home/oracle/orcl_data/example01.dbf               ONLINE


SYS@orcl2> select member from v$logfile;


MEMBER
-------------------------------------------------------
/home/oracle/orcl_data/redo03.log
/home/oracle/orcl_data/redo02.log
/home/oracle/orcl_data/redo01.log


SYS@orcl2> alter database open;

Database altered.

ARCHIVE

Closed DB

일관된 DB가 필요

Open DB = Hot, Online
Whole DB Backup

모든 데이터파일을 Backup
전체 데이터베이스

Partial DB Backup

데이터베이스 일부분

Full

100MB DB이면 100MB짜리 Backup

Incremental

변경된 부분만 Backup
Backup을 빨리 할 수 있다.

RECOVERY

NOARCHIVE

resotre
과거로 돌아가는 것

ARCHIVE

<<<<복습>>>>

1.BACKUP_ORCL2를 삭제하고

/home/oracle/backup/noarch 디렉토리에 Offline Whole 백업을 받으세요

[orcl2:~]$ mkdir -p /home/oracle/backup/noarch

백업받을 리스트를 select 문으로 찾아서 그 결과를 이용하여 백업하세요.

cp -av 명령어를 이용하세요

======백업 해야 할 목록======
SYS@orcl2>select name from v$datafile
          union all
          select member from v$logfile
          union all
          select name from v$controlfile;


파라미터 파일, 패스워드 파일
네트워크 구성파일 : listener.ora, tnsnames.ora, sqlnet.ora
============================

NAME
----------------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/system01.dbf
/u01/app/oracle/orcl2/sysaux01.dbf
/u01/app/oracle/orcl2/undotbs01.dbf
/u01/app/oracle/orcl2/users01.dbf
/u01/app/oracle/orcl2/example01.dbf
/u01/app/oracle/orcl2/redo03.log
/u01/app/oracle/orcl2/redo02.log
/u01/app/oracle/orcl2/redo01.log
/u01/app/oracle/orcl2/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl2/control02.ctl

10 rows selected.



[orcl2:noarch]$ vi db_noarch_list.sql
set pages 0
set head off
set feed off
set echo off
spool orcl2_noarch_backup.sh
select 'cp -av ' || name || ' /home/oracle/backup/noarch'
from (select name from v$datafile
      union all
      select member from v$logfile
      union all
      select name from v$controlfile)
/
spool off
set pages 120
set head on
set feed on
set echo on



SYS@orcl2> @db_noarch_list
cp -av /u01/app/oracle/orcl2/system01.dbf /home/oracle/backup/noarch
cp -av /u01/app/oracle/orcl2/sysaux01.dbf /home/oracle/backup/noarch
cp -av /u01/app/oracle/orcl2/undotbs01.dbf /home/oracle/backup/noarch
cp -av /u01/app/oracle/orcl2/users01.dbf /home/oracle/backup/noarch
cp -av /u01/app/oracle/orcl2/example01.dbf /home/oracle/backup/noarch
cp -av /u01/app/oracle/orcl2/redo03.log /home/oracle/backup/noarch
cp -av /u01/app/oracle/orcl2/redo02.log /home/oracle/backup/noarch
cp -av /u01/app/oracle/orcl2/redo01.log /home/oracle/backup/noarch
cp -av /u01/app/oracle/orcl2/control01.ctl /home/oracle/backup/noarch
cp -av /u01/app/oracle/flash_recovery_area/orcl2/control02.ctl /home/oracle/backup/noarch



SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.



[orcl2:~]$ . orcl2_noarch_backup.sh
`/u01/app/oracle/orcl2/system01.dbf' -> `/home/oracle/backup/noarch/system01.dbf'
`/u01/app/oracle/orcl2/sysaux01.dbf' -> `/home/oracle/backup/noarch/sysaux01.dbf'
`/u01/app/oracle/orcl2/undotbs01.dbf' -> `/home/oracle/backup/noarch/undotbs01.dbf'
`/u01/app/oracle/orcl2/users01.dbf' -> `/home/oracle/backup/noarch/users01.dbf'
`/u01/app/oracle/orcl2/example01.dbf' -> `/home/oracle/backup/noarch/example01.dbf'
`/u01/app/oracle/orcl2/redo03.log' -> `/home/oracle/backup/noarch/redo03.log'
`/u01/app/oracle/orcl2/redo02.log' -> `/home/oracle/backup/noarch/redo02.log'
`/u01/app/oracle/orcl2/redo01.log' -> `/home/oracle/backup/noarch/redo01.log'
`/u01/app/oracle/orcl2/control01.ctl' -> `/home/oracle/backup/noarch/control01.ctl'
`/u01/app/oracle/flash_recovery_area/orcl2/control02.ctl' -> `/home/oracle/backup/noarch/control02.ctl'


[orcl2:noarch]$ ls -al
total 1597676
drwxr-xr-x 2 oracle oinstall      4096 Apr 11 10:05 .
drwxr-xr-x 3 oracle oinstall      4096 Apr 11 09:49 ..
-rw-r----- 1 oracle dba        9748480 Apr 11 10:03 control01.ctl
-rw-r----- 1 oracle dba        9748480 Apr 11 10:03 control02.ctl
-rw-r----- 1 oracle dba      104865792 Apr 11 10:03 example01.dbf
-rw-r----- 1 oracle dba       52429312 Apr 11 10:01 redo01.log
-rw-r----- 1 oracle dba       52429312 Apr 11 10:01 redo02.log
-rw-r----- 1 oracle dba       52429312 Apr 11 10:03 redo03.log
-rw-r----- 1 oracle dba      524296192 Apr 11 10:03 sysaux01.dbf
-rw-r----- 1 oracle dba      713039872 Apr 11 10:03 system01.dbf
-rw-r----- 1 oracle dba      110108672 Apr 11 10:03 undotbs01.dbf
-rw-r----- 1 oracle dba        5251072 Apr 11 10:03 users01.dbf

2.1번의 백업을 restore할 수 있는 스크립트를 생성하세요

힌트: select 'cp -av ' || source ||' ' ||target

from

source 파일명을 작설할 때 디렉토리명을 잘라내기 위해 instr, substr함수를 이용하세요.

SYS@orcl2> select name, instr(name, '/', -1) position from v$datafile;

NAME                                       POSITION
---------------------------------------- ----------
/u01/app/oracle/orcl2/system01.dbf               22
/u01/app/oracle/orcl2/sysaux01.dbf               22
/u01/app/oracle/orcl2/undotbs01.dbf              22
/u01/app/oracle/orcl2/users01.dbf                22
/u01/app/oracle/orcl2/example01.dbf              22


SYS@orcl2> select name, substr(name, instr(name, '/', -1)+1) filename from v$datafile;

NAME                                     FILENAME
---------------------------------------- ----------------------------------------
/u01/app/oracle/orcl2/system01.dbf       system01.dbf
/u01/app/oracle/orcl2/sysaux01.dbf       sysaux01.dbf
/u01/app/oracle/orcl2/undotbs01.dbf      undotbs01.dbf
/u01/app/oracle/orcl2/users01.dbf        users01.dbf
/u01/app/oracle/orcl2/example01.dbf      example01.dbf


[orcl2:noarch]$ vi db_list2.sql
set echo off
set pages 0
set head off
set feed off
spool orcl2_restore.sh

select 'cp -av /home/oracle/backup/noarch/'||substr(name, instr(name, '/', -1)+1)||' '||name
from (select name from v$datafile
      union all
      select member namem from v$logfile
      union all
      select name from v$controlfile)

/
spool off
set pages 120
set head on
set feed on
set echo on



SYS@orcl2> @db_list2
SYS@orcl2> set echo off
cp -av /home/oracle/backup/noarch/system01.dbf /u01/app/oracle/orcl2/system01.dbf
cp -av /home/oracle/backup/noarch/sysaux01.dbf /u01/app/oracle/orcl2/sysaux01.dbf
cp -av /home/oracle/backup/noarch/undotbs01.dbf /u01/app/oracle/orcl2/undotbs01.dbf
cp -av /home/oracle/backup/noarch/users01.dbf /u01/app/oracle/orcl2/users01.dbf
cp -av /home/oracle/backup/noarch/example01.dbf /u01/app/oracle/orcl2/example01.dbf
cp -av /home/oracle/backup/noarch/redo03.log /u01/app/oracle/orcl2/redo03.log
cp -av /home/oracle/backup/noarch/redo02.log /u01/app/oracle/orcl2/redo02.log
cp -av /home/oracle/backup/noarch/redo01.log /u01/app/oracle/orcl2/redo01.log
cp -av /home/oracle/backup/noarch/control01.ctl /u01/app/oracle/orcl2/control01.ctl
cp -av /home/oracle/backup/noarch/control02.ctl /u01/app/oracle/flash_recovery_area/orcl2/control02.ctl

'DATABASE(oracleDB 11g) > DBA' 카테고리의 다른 글

[Oracle DBA]ArchiveMode 에서의 Backup & Recovery-archive_log (0)	2019.04.22
[Oracle DBA]ArchiveMode 에서의 Backup & Recovery-기초 (0)	2019.04.22
[Oracle DBA]성능 관리와 데이터 이동 (0)	2019.04.10
[Oracle DBA]데이터베이스 유지 관리 (0)	2019.04.08
복습문제 (0)	2019.04.05

현재글[Oracle DBA]Backup & Recovery

잡다한 모든 것들이 있는 작업공간

sql, DBA, Raid, WS, Oracle, admin, linux, db, database, 리눅스, instance, pl/sql,

Today :
Yesterday :

승삼이의 작업공간