개요
sysbackup 계정 삭제 가능 여부 및 복구 테스트에 대해 안내합니다.
사이트 운영관리 및 보안 규정에 의해 sysbackup 계정을 삭제 하여야 하는 경우, sysbackup 계정을 삭제하고 기존과 같이 sys로 backup, recovery 사용 가능합니다. (tbrmgr 포함)
단, 동일 권한을 갖는 다른 이름의 계정 생성 시에도 복구는 sys 계정으로만 가능합니다.
방법
SYSBACKUP 계정 소개
•
SYSBACKUP 계정이란 sys 계정이 아니면서 backup/recovery 와 관련된 모든 권한을 갖는 계정입니다.
(SYS 계정이 아니지만 NOMOUNT, MOUNT 모드 로그인이 가능)
•
alter database open으로 DB를 open하는 것은 SYSBACKUP 계정에선 불가능하고, SYS 계정에서만 가능합니다. (시도 시, TBR-7607(cannot open database) 에러 발생)
•
sysbackup 계정은 sys가 가진 권한 중 백업/리커버리에 필요한 권한만을 부여받아 백업/리커버리를 수행하기 위한 계정입니다.
SYSBACKUP에 허용된 권한
CREATE USER SYSBACKUP IDENTIFIED BY TIBERO DEFAULT TABLESPACE SYSSUB;
GRANT CREATE SESSION TO SYSBACKUP;
GRANT ALTER SESSION TO SYSBACKUP;
GRANT ALTER SYSTEM TO SYSBACKUP;
GRANT CREATE TABLESPACE TO SYSBACKUP;
GRANT ALTER TABLESPACE TO SYSBACKUP;
GRANT DROP TABLESPACE TO SYSBACKUP;
GRANT ALTER DATABASE TO SYSBACKUP;
GRANT ALTER RMGR TO SYSBACKUP;
GRANT SELECT ANY DICTIONARY TO SYSBACKUP;
PRIV_TYPE GRANTEE PRIVILEGE OBJECT_NAME OWNER
---------- ----------- ----------------- --------------- -------------
OBJECT_PRIV SYSBACKUP EXECUTE DBMS_BACKUP_RESTORE SYS
SYSTEM_PRIV SYSBACKUP ALTER DATABASE
SYSTEM_PRIV SYSBACKUP ALTER RMGR
SYSTEM_PRIV SYSBACKUP ALTER SESSION
SYSTEM_PRIV SYSBACKUP ALTER SYSTEM
SYSTEM_PRIV SYSBACKUP ALTER TABLESPACE
SYSTEM_PRIV SYSBACKUP CREATE SESSION
SYSTEM_PRIV SYSBACKUP CREATE TABLESPACE
SYSTEM_PRIV SYSBACKUP DROP TABLESPACE
SYSTEM_PRIV SYSBACKUP SELECT ANY DICTIONA
SQL
복사
Backup & Recovery 테스트
추가적인 권한을 부여하여 sysbackup 과 동일 권한을 갖는 다른 user 생성시에도 복구는 불가하며,
sys 계정을 사용 하여 복구 필요가 필요합니다.
1. RMGR 을 사용한 Backup 수행
[tibero7@centos722:tibero7:/home/tibero/Archive]tbrmgr backup --userid sysbackup/tibero -o /home/tibero/backup -w
==============================================================================
= Recovery Manager(RMGR) starts =
= =
= TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. =
==============================================================================
archive log check succeeded
RMGR '-o' option used
: backing up to /home/tibero/backup
==============================================================================
RMGR - Backup (FULL)
==============================================================================
Initializing the backup progress, it may take few minutes...
BACKUP (set_id: 2, ts_id: 0, df_id: 0)
100.0% |===============================>| 65536/65536 blks 1.02s
Synchronizing...
BACKUP (set_id: 2, ts_id: 1, df_id: 1)
100.0% |===============================>| 65536/65536 blks 1.01s
Synchronizing...
BACKUP (set_id: 2, ts_id: 3, df_id: 2)
100.0% |===============================>| 16384/16384 blks 0.00s
Synchronizing...
BACKUP (set_id: 2, ts_id: 4, df_id: 3)
100.0% |===============================>| 70400/70400 blks 1.04s
Synchronizing...
BACKUP (set_id: 2, ts_id: 5, df_id: 4)
100.0% |===============================>| 6400/6400 blks 0.00s
Synchronizing...
Switching an online logfile...
Backing up archive logfiles...
Archivelog log-t0-r0-s11.arc (thread: 0, set_id: 2, low_tsn: 104428, next_tsn: 105923) backed up
Archive logfiles backup succeeded
Backing up the control file...
Control file backup succeeded
Database backup succeeded
RMGR backup ends
[tibero7@centos722:tibero7:/home/tibero/Archive]ll -rlt
합계 111412
-rw-------. 1 tibero7 dba 103349760 7월 7 11:19 log-t0-r0-s5.arc
-rw-------. 1 tibero7 dba 1016832 7월 7 11:43 log-t0-r0-s6.arc
-rw-------. 1 tibero7 dba 512 7월 7 11:43 log-t0-r0-s7.arc
-rw-------. 1 tibero7 dba 512 7월 7 11:43 log-t0-r0-s8.arc
-rw-------. 1 tibero7 dba 512 7월 7 11:43 log-t0-r0-s9.arc
drwx------. 14 tibero7 dba 4096 7월 7 13:29 ..
-rw-------. 1 tibero7 dba 6986240 7월 7 13:32 log-t0-r0-s10.arc
drwxr-x---. 2 tibero7 dba 176 7월 7 14:18 .
-rw-------. 1 tibero7 dba 2710528 7월 7 14:18 log-t0-r0-s11.arc
[tibero7@centos722:tibero7:/home/tibero/Archive]cd ..
[tibero7@centos722:tibero7:/home/tibero]cd backup
[tibero7@centos722:tibero7:/home/tibero/backup]ll
합계 3691368
drwx------. 14 tibero7 dba 4096 7월 7 13:29 ..
-rw-------. 1 tibero7 dba 536870912 7월 7 13:32 bkp_20250707_1_0_0_0
-rw-------. 1 tibero7 dba 536870912 7월 7 13:32 bkp_20250707_1_1_1_0
-rw-------. 1 tibero7 dba 134217728 7월 7 13:32 bkp_20250707_1_3_2_0
-rw-------. 1 tibero7 dba 576716800 7월 7 13:32 bkp_20250707_1_4_3_0
-rw-------. 1 tibero7 dba 77021184 7월 7 13:33 bkp_20250707_1_cf_0
-rw-------. 1 tibero7 dba 536870912 7월 7 14:18 bkp_20250707_2_0_0_0
-rw-------. 1 tibero7 dba 536870912 7월 7 14:18 bkp_20250707_2_1_1_0
-rw-------. 1 tibero7 dba 134217728 7월 7 14:18 bkp_20250707_2_3_2_0
-rw-------. 1 tibero7 dba 576716800 7월 7 14:18 bkp_20250707_2_4_3_0
-rw-------. 1 tibero7 dba 52428800 7월 7 14:18 bkp_20250707_2_5_4_0
-rw-------. 1 tibero7 dba 2710528 7월 7 14:18 bkl_2_t0_r0_s11.arc
drwxr-x---. 2 tibero7 dba 4096 7월 7 14:18 .
-rw-------. 1 tibero7 dba 77021184 7월 7 14:19 bkp_20250707_2_cf_0
SQL
복사
2. 데이터 파일 삭제(장애 유발) 후 기동
[tibero7@centos722:tibero7:/home/tibero/tbdata7]mv ./test01.dtf test01_broken.dtf
[tibero7@centos722:tibero7:/home/tibero/tbdata7]tbboot
Change core dump dir to /home/tibero/tibero7/bin/prof.
Listener port = 8629
Critical Warning : Raise svmode failed. The reason is
TBR-1024 : Database needs media recovery: open failed(/home/tibero/tbdata7/test01.dtf).
Current server mode is MOUNT.
**
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started suspended at MOUNT mode.
SQL
복사
3. sysbackup1 계정 신규 생성 후, sysbackup 계정에게 주던 권한(패치 태스크에 작성된 권한)을 동일하게 부여
CREATE USER SYSBACKUP1 IDENTIFIED BY TIBERO DEFAULT TABLESPACE SYSSUB;
GRANT RESOURCE,CONNECT to SYSBACKUP1;
GRANT CREATE SESSION TO SYSBACKUP1;
GRANT ALTER SESSION TO SYSBACKUP1;
GRANT ALTER SYSTEM TO SYSBACKUP1;
GRANT CREATE TABLESPACE TO SYSBACKUP1;
GRANT ALTER TABLESPACE TO SYSBACKUP1;
GRANT DROP TABLESPACE TO SYSBACKUP1;
GRANT ALTER DATABASE TO SYSBACKUP1;
GRANT ALTER RMGR TO SYSBACKUP1;
GRANT SELECT ANY DICTIONARY TO SYSBACKUP1;
SQL
복사
4. RMGR 을 사용한 Recovery 수행 시도
tbrmgr recover --userid sysbackup1/tibero -o /home/tibero/backup -w
==============================================================================
= Recovery Manager(RMGR) starts =
= =
= TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. =
==============================================================================
RMGR '-o' option used
: restoring from /home/tibero/backup
==============================================================================
RMGR - recovery (WITH ARCHIVELOG)
==============================================================================
Shutting down the instance...
Tibero instance terminated (ABNORMAL mode).
info file is deleted.
unlink failed.: No such file or directory
Control file #0 (/home/tibero/tbdata7/c1.ctl) is accessible
Control file #1 (/home/tibero/tbdata7/c2.ctl) is accessible
All control files are accessible. No need to restore the backup control file.
Booting up the instance...
Change core dump dir to /home/tibero/tibero7/bin/prof.
Listener port = 8629
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (MOUNT mode).
RMGR Error: can't connect to Databases.
RMGR Server Error: -12021
[tibero7@centos722:tibero7:/home/tibero/tbdata7]!tberr 12021
tberr 70003 12021
/
err: -70003
name: ERROR_ISQL_UNKNOWN_COMMAND
* desc: Invalid command. Enter HELP or HELP <command>.
* cause: Invalid command.
* action: Verify the command is correct.;
/
/
* err: -12021
* name: ERROR_CLIMSG_RESTRICTED_USER
desc: Only SYS user can log on in restricted boot mode.
* cause: Only the SYS user can log on in the current boot mode.
* action: Log on as SYS user.;
*/
SQL
복사
참고
SYS 계정을 사용한 복구 방법
tbrmgr recover --userid sys/tibero -o /home/tibero/backup -w
[tibero7@centos722:tibero7:/home/tibero/tbdata7]tbrmgr recover --userid sys/tibero -o /home/tibero/backup -w
==============================================================================
= Recovery Manager(RMGR) starts =
= =
= TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. =
==============================================================================
RMGR '-o' option used
: restoring from /home/tibero/backup
==============================================================================
RMGR - recovery (WITH ARCHIVELOG)
==============================================================================
Shutting down the instance...
Tibero instance terminated (ABNORMAL mode).
info file is deleted.
unlink failed.: No such file or directory
Control file #0 (/home/tibero/tbdata7/c1.ctl) is accessible
Control file #1 (/home/tibero/tbdata7/c2.ctl) is accessible
All control files are accessible. No need to restore the backup control file.
Booting up the instance...
Change core dump dir to /home/tibero/tibero7/bin/prof.
Listener port = 8629
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (MOUNT mode).
Initializing the restore progress, it may take few minutes...
RMGR begins restoring backup files.
Full backup set_id: 2
Last incremental backup set_id: 2
RESTORE (set_id: 2, ts_id: 0, df_id: 0) - FULL BACKUP
100.0% |===============================>| 65536/65536 blks 0.00s
Synchronizing...
RESTORE (set_id: 2, ts_id: 1, df_id: 1) - FULL BACKUP
100.0% |===============================>| 65536/65536 blks 0.00s
Synchronizing...
RESTORE (set_id: 2, ts_id: 3, df_id: 2) - FULL BACKUP
100.0% |===============================>| 16384/16384 blks 0.00s
Synchronizing...
RESTORE (set_id: 2, ts_id: 4, df_id: 3) - FULL BACKUP
100.0% |===============================>| 70400/70400 blks 0.00s
Synchronizing...
RESTORE (set_id: 2, ts_id: 5, df_id: 4) - FULL BACKUP
100.0% |===============================>| 6400/6400 blks 0.00s
Synchronizing...
Settling...
Restoring logfiles...
Archivelog log-t0-r0-s11.arc (thread: 0, set_id: 2, low_tsn: 104428, next_tsn: 105923) restored
Archive logfiles restoration succeeded
Database restoration succeeded
Given recovery point: COMPLETE RECOVERY
Recoverable point using only backup archivelogs: change 105922
recoverSQL: ALTER DATABASE RECOVER AUTOMATIC
Database automatic recovery succeeded
Shutting down the instance...
Tibero instance terminated (NORMAL mode).
Booting up the instance...
Change core dump dir to /home/tibero/tibero7/bin/prof.
Listener port = 8629
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL mode).
RMGR recovery ends
SQL
복사