Tibero Backup & Recovery

<신예환님 질문답변>

1. 특정테이블스페이스 A를 대상으로 Begin backup 하고, V$BACKUP 조회시, 데이터파일 X 가 ACTIVE 일때 이것은 테이블스페이스 A와 연결된 데이터파일이 한가지(파일 x ) 이기때문인가? ==> 그렇다. 만약에 파일 x,y,z 가 연결된 경우는 3개의 데이터파일이 ACTIVE 로 조회된다.

2. tbdown abort 실행하여 종료되고, 다시 tbboot 할때 인스턴스 복구(장애 이전의 버퍼캐시의 데이터블록을 재현하여 만들어 주는 작업을 하게됨) 를 한다.

그런데, 장애이전의 블록을 생각해보면,

블록1(데이터가 변경되고, 아직 커밋을 안한상태)
블록2(데이터가 변경되고, 커밋을 한 상태)
블록3(데이터가 변경않되고, 조회만 한 상태)

복구는 블록1,2 등 변경된 블록들 대상으로 진행됨.

A. 복구의 1단계(롤 포워드)
1. 데이터파일에서 데이터블록을 버퍼캐시에 올림.
2. 리두로그 파일에서 리두를 읽어서 버퍼캐시에 적용하여 더티블록을 만듬.
3. 위의 블록1,2 들이 재현됨.

B. 복구의 2단계(롤 백)
1. 위의 블록1 들을 대상으로 언두데이터를 적용하여 원래대로 데이터를 되돌리게됨.

<오프라인 백업 복구하기>

[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ mkdir /tibero/s/off
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL> SELECT NAME FROM V$CONTROLFILE;

NAME
--------------------------------------------------------------------------------
/tibero/tbdata/tibero/c1.ctl
/tibero/tbdata/tibero/c2.ctl

2 rows selected.

SQL> SELECT NAME FROM V$DATAFILE;

NAME
--------------------------------------------------------------------------------
/tibero/tbdata/tibero/system001.dtf
/tibero/tbdata/tibero/undo001.dtf
/tibero/tbdata/tibero/usr001.dtf
/tibero/tbdata/tibero/syssub001.dtf
/tibero/tbdata/my_file001.dtf

5 rows selected.

SQL> SELECT GROUP#, MEMBER FROM V$LOGFILE;

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
         0
/tibero/tbdata/tibero/log01.log

         1
/tibero/tbdata/tibero/log11.log

         2
/tibero/tbdata/tibero/log21.log

         3
/tibero/tbdata/tibero/log31.log

         3
/tibero/tbdata/tibero/log32.log


5 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> q
Disconnected.
[tibero@T1:/tibero]$ tbdown immediate


Tibero instance terminated (IMMEDIATE mode).

[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ ps -ef | grep tbsvr
tibero   10527 18506  0 16:29 pts/5    00:00:00 grep --color=auto tbsvr
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ cp /tibero/tbdata/tibero/*.dtf  /tibero/s/off
[tibero@T1:/tibero]$ cp /tibero/tbdata/tibero/*.ctl  /tibero/s/off
[tibero@T1:/tibero]$ cp /tibero/tbdata/tibero/*.log  /tibero/s/off
[tibero@T1:/tibero]$ cp /tibero/tbdata/my_file001.dtf /tibero/s/off
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ tbboot
Change core dump dir to /tibero/tibero7/bin/prof.
Listener port = 8629

Tibero 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL mode).
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ ls -l /tibero/tbdata/tibero/usr001.dtf
-rw------- 1 tibero dba 52428800 Apr 13 16:32 /tibero/tbdata/tibero/usr001.dtf
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ rm /tibero/tbdata/tibero/usr001.dtf
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ ls -l /tibero/tbdata/tibero/usr001.dtf
ls: cannot access /tibero/tbdata/tibero/usr001.dtf: No such file or directory
[tibero@T1:/tibero]$ tbdown abnormal
Tibero instance terminated (ABNORMAL mode).

[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ tbboot
Change core dump dir to /tibero/tibero7/bin/prof.
Listener port = 8629

********************************************************
* Critical Warning : Raise svmode failed. The reason is
*   TBR-1024 :  Database needs media recovery: open failed(/tibero/tbdata/tibero/usr001.dtf).
*   Current server mode is MOUNT.
********************************************************


Tibero 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started suspended at MOUNT mode.
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL> SELECT * FROM V$RECOVER_FILE;

     FILE# ONLINE
---------- -------
ERROR
--------------------------------------------------------------------------------
   CHANGE#
----------
TIME
--------------------------------------------------------------------------------
         2 ONLINE
open failed
         0



1 row selected.

SQL> SELECT * FROM V$DATAFILE WHERE FILE#=2;

     FILE# CREATE_TSN
---------- ----------
CREATE_DATE
--------------------------------------------------------------------------------
       TS#     RFILE# BIGFILE STATUS           ENABLED      CKPT_TSN
---------- ---------- ------- ---------------- ---------- ----------
CKPT_DATE
--------------------------------------------------------------------------------
CREATE_BYTES
------------
NAME
--------------------------------------------------------------------------------
         2          8
2023/04/06
         3          2 NO      ONLINE           READ WRITE     190993
2023/04/13
    52428800
/tibero/tbdata/tibero/usr001.dtf


1 row selected.

SQL> Q
Disconnected.
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ tbdown immediate

Tibero instance terminated (IMMEDIATE mode).

[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ ls -l /tibero/tbdata/tibero/*.dtf
-rw------- 1 tibero dba  52428800 Apr 13 16:33 /tibero/tbdata/tibero/syssub001.dtf
-rw------- 1 tibero dba 171966464 Apr 13 16:33 /tibero/tbdata/tibero/system001.dtf
-rw------- 1 tibero dba 104857600 Apr 13 00:01 /tibero/tbdata/tibero/temp001.dtf
-rw------- 1 tibero dba 209715200 Apr 13 16:33 /tibero/tbdata/tibero/undo001.dtf
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ rm /tibero/tbdata/tibero/*.dtf
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ ls -l /tibero/tbdata/tibero/*.log
-rw------- 1 tibero dba 52428800 Apr 13 16:32 /tibero/tbdata/tibero/log01.log
-rw------- 1 tibero dba 52428800 Apr 13 16:32 /tibero/tbdata/tibero/log11.log
-rw------- 1 tibero dba 52428800 Apr 13 16:33 /tibero/tbdata/tibero/log21.log
-rw------- 1 tibero dba 52428800 Apr 13 16:32 /tibero/tbdata/tibero/log31.log
-rw------- 1 tibero dba 52428800 Apr 13 16:32 /tibero/tbdata/tibero/log32.log
[tibero@T1:/tibero]$ rm /tibero/tbdata/tibero/*.log
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ ls -l /tibero/tbdata/tibero/*.ctl
-rw------- 1 tibero dba 74465280 Apr 13 16:34 /tibero/tbdata/tibero/c1.ctl
-rw------- 1 tibero dba 74465280 Apr 13 16:34 /tibero/tbdata/tibero/c2.ctl
[tibero@T1:/tibero]$ rm /tibero/tbdata/tibero/*.ctl
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ ls -l /tibero/tbdata/*.dat
ls: cannot access /tibero/tbdata/*.dat: No such file or directory
[tibero@T1:/tibero]$ ls -l /tibero/tbdata/*.dtf
-rw------- 1 tibero dba 104857600 Apr 13 16:32 /tibero/tbdata/my_file001.dtf
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ rm /tibero/tbdata/*.dtf
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ ls -l /tibero/s/off
total 1081376
-rwxrwx--- 1 root vboxsf  74465280 Apr 13 16:31 c1.ctl
-rwxrwx--- 1 root vboxsf  74465280 Apr 13 16:31 c2.ctl
-rwxrwx--- 1 root vboxsf  52428800 Apr 13 16:31 log01.log
-rwxrwx--- 1 root vboxsf  52428800 Apr 13 16:31 log11.log
-rwxrwx--- 1 root vboxsf  52428800 Apr 13 16:31 log21.log
-rwxrwx--- 1 root vboxsf  52428800 Apr 13 16:31 log31.log
-rwxrwx--- 1 root vboxsf  52428800 Apr 13 16:31 log32.log
-rwxrwx--- 1 root vboxsf 104857600 Apr 13 16:32 my_file001.dtf
-rwxrwx--- 1 root vboxsf  52428800 Apr 13 16:31 syssub001.dtf
-rwxrwx--- 1 root vboxsf 171966464 Apr 13 16:31 system001.dtf
-rwxrwx--- 1 root vboxsf 104857600 Apr 13 16:31 temp001.dtf
-rwxrwx--- 1 root vboxsf 209715200 Apr 13 16:31 undo001.dtf
-rwxrwx--- 1 root vboxsf  52428800 Apr 13 16:31 usr001.dtf
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ cp /tibero/s/off/*.dtf /tibero/tbdata/tibero
[tibero@T1:/tibero]$ cp /tibero/s/off/*.log /tibero/tbdata/tibero
[tibero@T1:/tibero]$ cp /tibero/s/off/*.ctl /tibero/tbdata/tibero
[tibero@T1:/tibero]$ cp /tibero/s/off/my_file001.dtf /tibero/tbdata
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ rm /tibero/tbdata/tibero/my_file001.dtf
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ tbboot
Change core dump dir to /tibero/tibero7/bin/prof.
Listener port = 8629

Tibero 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL mode).
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$