## 전체복구
※ 파라미터 파일, 컨트롤 파일, 데이터 파일 등이 손실된 상황
### 복구 과정 요약
1. 풀백업
RMAN> backup database plus archivelog; -- 풀백업
RMAN> list backup of controlfile;
RMAN> list backup of spfile; -- 백업이 제대로 됐는지 확인
2. spfile, control file 등 삭제
3. 손실된 파일들 복원 및 복구 과정
→ spfile과 controlfile을 nomount 상태에서 복구한다.
→ 가장 최근의 로그 번호를 확인하고 그 시점으로 복구한다.
RMAN> statup nomount;
RMAN> restore spfile;
RMAN> statup force nomount; -- nomount 상태로 재시작
RMAN> restore controlfile;
RMAN> alter database mount;
RMAN> select * from v$log; -- current sequence 확인
RMAN> run{
set until sequence 5 thread 1;
restore database;
recover database;
}
4. resetlogs로 open
→ 특정 시점 복구이기 때문에 redo log를 초기화하며 오픈한다.
RMAN> alter database open resetlogs;
실행 과정 첨부
[/home/oracle]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 19 18:22:55 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19C (DBID=1186485125)
RMAN> delete backup;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
36 36 1 1 AVAILABLE DISK /app/oracle/rman/ORA19C_1f1q0occ _1_1_20230419.bk
37 37 1 1 AVAILABLE DISK /app/oracle/rman/ORA19C_1g1q0ocd _1_1_20230419.bk
38 38 1 1 AVAILABLE DISK /app/oracle/rman/ORA19C_1h1q0oce _1_1_20230419.bk
39 39 1 1 AVAILABLE DISK /app/oracle/rman/ORA19C_1i1q0ocf _1_1_20230419.bk
40 40 1 1 AVAILABLE DISK /app/oracle/rman/ORA19C_1j1q0oci _1_1_20230419.bk
41 41 1 1 AVAILABLE DISK /app/oracle/rman/db_ctl_c-118648 5125-20230419-05
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/app/oracle/rman/ORA19C_1f1q0occ_1_1_20230419.bk RECID=36 ST AMP=1134584204
deleted backup piece
backup piece handle=/app/oracle/rman/ORA19C_1g1q0ocd_1_1_20230419.bk RECID=37 ST AMP=1134584205
deleted backup piece
backup piece handle=/app/oracle/rman/ORA19C_1h1q0oce_1_1_20230419.bk RECID=38 ST AMP=1134584206
deleted backup piece
backup piece handle=/app/oracle/rman/ORA19C_1i1q0ocf_1_1_20230419.bk RECID=39 ST AMP=1134584207
deleted backup piece
backup piece handle=/app/oracle/rman/ORA19C_1j1q0oci_1_1_20230419.bk RECID=40 ST AMP=1134584210
deleted backup piece
backup piece handle=/app/oracle/rman/db_ctl_c-1186485125-20230419-05 RECID=41 ST AMP=1134584211
Deleted 6 objects
RMAN> backup database plus archivelog;
Starting backup at 19-APR-23
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=1 STAMP=1134474445
input archived log thread=1 sequence=10 RECID=2 STAMP=1134474462
input archived log thread=1 sequence=12 RECID=3 STAMP=1134481365
input archived log thread=1 sequence=13 RECID=4 STAMP=1134481556
input archived log thread=1 sequence=14 RECID=5 STAMP=1134481576
input archived log thread=1 sequence=15 RECID=6 STAMP=1134481586
input archived log thread=1 sequence=16 RECID=7 STAMP=1134576909
input archived log thread=1 sequence=17 RECID=8 STAMP=1134578921
input archived log thread=1 sequence=18 RECID=9 STAMP=1134578929
channel ORA_DISK_1: starting piece 1 at 19-APR-23
channel ORA_DISK_1: finished piece 1 at 19-APR-23
piece handle=/app/oracle/rman/ORA19C_1l1q0op7_1_1_20230419.bk tag=TAG20230419T18 2335 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=18 STAMP=1134582938
input archived log thread=1 sequence=2 RECID=19 STAMP=1134582938
input archived log thread=1 sequence=3 RECID=20 STAMP=1134582938
channel ORA_DISK_1: starting piece 1 at 19-APR-23
channel ORA_DISK_1: finished piece 1 at 19-APR-23
piece handle=/app/oracle/rman/ORA19C_1m1q0op9_1_1_20230419.bk tag=TAG20230419T18 2335 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=21 STAMP=1134584204
input archived log thread=1 sequence=2 RECID=22 STAMP=1134584210
input archived log thread=1 sequence=3 RECID=23 STAMP=1134584615
channel ORA_DISK_1: starting piece 1 at 19-APR-23
channel ORA_DISK_1: finished piece 1 at 19-APR-23
piece handle=/app/oracle/rman/ORA19C_1n1q0opa_1_1_20230419.bk tag=TAG20230419T18 2335 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-APR-23
Starting backup at 19-APR-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/app/oracle/oradata/ORA19C/user_ts/DAMIN_I DX_TS.dbf
input datafile file number=00005 name=/app/oracle/oradata/ORA19C/user_ts/DAMIN_D ATA_TS.dbf
input datafile file number=00001 name=/app/oracle/oradata/ORA19C/system01.dbf
input datafile file number=00003 name=/app/oracle/oradata/ORA19C/sysaux01.dbf
input datafile file number=00004 name=/app/oracle/oradata/ORA19C/undotbs01.dbf
input datafile file number=00007 name=/app/oracle/oradata/ORA19C/users01.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-23
channel ORA_DISK_1: finished piece 1 at 19-APR-23
piece handle=/app/oracle/rman/ORA19C_1o1q0opb_1_1_20230419.bk tag=TAG20230419T18 2339 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 19-APR-23
Starting backup at 19-APR-23
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=24 STAMP=1134584627
channel ORA_DISK_1: starting piece 1 at 19-APR-23
channel ORA_DISK_1: finished piece 1 at 19-APR-23
piece handle=/app/oracle/rman/ORA19C_1p1q0opj_1_1_20230419.bk tag=TAG20230419T18 2347 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-APR-23
Starting Control File and SPFILE Autobackup at 19-APR-23
piece handle=/app/oracle/rman/db_ctl_c-1186485125-20230419-06 comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-23
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
47 Full 10.52M DISK 00:00:00 19-APR-23
BP Key: 47 Status: AVAILABLE Compressed: NO Tag: TAG20230419T182348
Piece Name: /app/oracle/rman/db_ctl_c-1186485125-20230419-06
Control File Included: Ckp SCN: 2799992 Ckp time: 19-APR-23
RMAN> list backup of spfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
47 Full 10.52M DISK 00:00:00 19-APR-23
BP Key: 47 Status: AVAILABLE Compressed: NO Tag: TAG20230419T182348
Piece Name: /app/oracle/rman/db_ctl_c-1186485125-20230419-06
SPFILE Included: Modification time: 19-APR-23
SPFILE db_unique_name: ORA19C
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> exit;
Recovery Manager complete.
[/home/oracle]$ cd $ORALCE_HOME/dbs
-bash: cd: /dbs: No such file or directory
[/home/oracle]$ ls
backup cr_controlfile_no_resetlog.sql cr_controlfile.sql logmnr temp
[/home/oracle]$ cd $ORACLE_HOME/dbs
[/app/oracle/product/19.3/db_1/dbs]$ ls
hc_ORA19C.dat initORA19C.ora orabackup snapcf_ORA19C.f
init.ora lkORA19C orapwORA19C spfileORA19C.ora
[/app/oracle/product/19.3/db_1/dbs]$ rm spfileORA*
[/app/oracle/product/19.3/db_1/dbs]$ ls
hc_ORA19C.dat initORA19C.ora orabackup snapcf_ORA19C.f
init.ora lkORA19C orapwORA19C
[/app/oracle/product/19.3/db_1/dbs]$ cd /app/oracle/oradata/
[/app/oracle/oradata]$ ls
ORA19C
[/app/oracle/oradata]$ cd ORA19C
[/app/oracle/oradata/ORA19C]$ ls
control01.ctl r1a.log redo03.log temp01.dbf user_ts
control02.ctl redo01.log sysaux01.dbf undotbs01.dbf
control03.ctl redo02.log system01.dbf users01.dbf
[/app/oracle/oradata/ORA19C]$ rm control*
[/app/oracle/oradata/ORA19C]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 19 18:25:51 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: Oracle error from target database:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/app/oracle/oradata/ORA19C/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
[/app/oracle/oradata/ORA19C]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 19 18:25:59 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Pr oduction
Version 19.3.0.0.0
[/app/oracle/oradata/ORA19C]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 19 18:26:15 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> exit
Disconnected
[/app/oracle/oradata/ORA19C]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 19 18:26:20 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> ^C
user interrupt received
RMAN> startup nomount;
Oracle instance started
Total System Global Area 1610609200 bytes
Fixed Size 8897072 bytes
Variable Size 385875968 bytes
Database Buffers 1207959552 bytes
Redo Buffers 7876608 bytes
RMAN> restore spfile from '/app/oracle/rman/db_ctl_c-1186485125-20230419-06';
Starting restore at 19-APR-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=261 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /app/oracle/rman/db_ctl_c-1 186485125-20230419-06
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 19-APR-23
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 1610609200 bytes
Fixed Size 8897072 bytes
Variable Size 385875968 bytes
Database Buffers 1207959552 bytes
Redo Buffers 7876608 bytes
RMAN> restore controlfile from '/app/oracle/rman/db_ctl_c-1186485125-20230419-06 ';
Starting restore at 19-APR-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=261 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/app/oracle/oradata/ORA19C/control01.ctl
output file name=/app/oracle/oradata/ORA19C/control02.ctl
output file name=/app/oracle/oradata/ORA19C/control03.ctl
Finished restore at 19-APR-23
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------------- ------------- --------- ------------ --------- ----------
1 1 4 209715200 512 1 YES
INACTIVE 2799938 19-APR-23 2799977 19-APR-23 0
3 1 3 209715200 512 1 YES
INACTIVE 2799036 19-APR-23 2799938 19-APR-23 0
2 1 5 209715200 512 1 NO
CURRENT 2799977 19-APR-23 184467440737 0
RMAN> run {
2> set until sequence 5 thread 1;
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 19-APR-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /app/oracle/oradata/ORA19C/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /app/oracle/oradata/ORA19C/user_ts/DAMIN_IDX_TS.dbf
channel ORA_DISK_1: restoring datafile 00003 to /app/oracle/oradata/ORA19C/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /app/oracle/oradata/ORA19C/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /app/oracle/oradata/ORA19C/user_ts/DAMIN_DATA_TS.dbf
channel ORA_DISK_1: restoring datafile 00007 to /app/oracle/oradata/ORA19C/users01.dbf
channel ORA_DISK_1: reading from backup piece /app/oracle/rman/ORA19C_1o1q0opb_1_1_20230419.bk
channel ORA_DISK_1: piece handle=/app/oracle/rman/ORA19C_1o1q0opb_1_1_20230419.bk tag=TAG20230419T182339
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 19-APR-23
Starting recover at 19-APR-23
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /app/oracle/oradata/ORA19C/redo01.log
archived log file name=/app/oracle/oradata/ORA19C/redo01.log thread=1 sequence=4
media recovery complete, elapsed time: 00:00:00
Finished recover at 19-APR-23
RMAN> alter database open resetlogs;
Statement processed
RMAN> exit
Recovery Manager complete.
[/app/oracle/oradata/ORA19C]$ cd $ORACLE_HOME
[/app/oracle/product/19.3/db_1]$ cd dbs
[/app/oracle/product/19.3/db_1/dbs]$ ls
hc_ORA19C.dat init.ora initORA19C.ora lkORA19C orabackup orapwORA19C snapcf_ORA19C.f spfileORA19C.ora
[/app/oracle/product/19.3/db_1/dbs]$ cd /app/oracle/oradata/ORA19C
[/app/oracle/oradata/ORA19C]$ ls
control01.ctl control02.ctl control03.ctl r1a.log redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf user_ts
[/app/oracle/oradata/ORA19C]$
## System tablespace 단위 복구
※ 시스템 테이블스페이스가 손실된 상황
### 복구 과정 요약
1. tablespace 백업
→ full backup도 사용 가능
RMAN> backup tablespace system;
RMAN> list backup of tablespace system; -- 백업이 제대로 되었는지 확인
2. tablespace 삭제
→ system tablespace 제거 (여기서는 system01.dbf 제거)
3. 손실된 파일들 복원 및 복구
→ 데이터베이스를 startup하려고 하면 datafile 1(ORA-01110) 이 없다는 에러메세지를 확인할 수 있다.
RMAN> restore datafile 1;
RMAN> recover datafile 1;
→ 'select file_id, tablespace_name from dba_data_files;' 이런식으로 sqlplus에서도 확인가능하다.
4. database open
RMAN> alter database open;
실행 과정 첨부
[/home/oracle]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 19 19:35:03 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19C (DBID=1186485125)
RMAN> backup tablespace system;
Starting backup at 19-APR-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/app/oracle/oradata/ORA19C/system01.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-23
channel ORA_DISK_1: finished piece 1 at 19-APR-23
piece handle=/app/oracle/rman/ORA19C_221q0svv_1_1_20230419.bk tag=TAG20230419T19 3527 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 19-APR-23
Starting Control File and SPFILE Autobackup at 19-APR-23
piece handle=/app/oracle/rman/db_ctl_c-1186485125-20230419-09 comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-23
RMAN> list backup of tablespace system;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51 Full 1.24G DISK 00:00:04 19-APR-23
BP Key: 51 Status: AVAILABLE Compressed: NO Tag: TAG20230419T191257
Piece Name: /app/oracle/rman/ORA19C_1u1q0rlq_1_1_20230419.bk
List of Datafiles in backup set 51
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2914852 19-APR-23 NO /app/oracle/oradata/ORA19 C/system01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
55 Full 790.86M DISK 00:00:02 19-APR-23
BP Key: 55 Status: AVAILABLE Compressed: NO Tag: TAG20230419T193527
Piece Name: /app/oracle/rman/ORA19C_221q0svv_1_1_20230419.bk
List of Datafiles in backup set 55
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2917503 19-APR-23 NO /app/oracle/oradata/ORA19 C/system01.dbf
RMAN> exit
Recovery Manager complete.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[/home/oracle]$ cd /app/oracle
[/app/oracle]$ ls
admin audit cfgtoollogs checkpoints diag oradata product rman
[/app/oracle]$ cd oradata
[/app/oracle/oradata]$ ls
ORA19C
[/app/oracle/oradata]$ cd ORA19C
[/app/oracle/oradata/ORA19C]$ ls
control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf user_ts
control02.ctl r1a.log redo02.log sysaux01.dbf temp01.dbf users01.dbf
[/app/oracle/oradata/ORA19C]$ rm system*
[/app/oracle/oradata/ORA19C]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 19 19:50:13 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> shutdown immediate;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of shutdown command at 04/19/2023 19:50:17
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4376
Additional information: -739635665
RMAN> startup
Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 04/19/2023 19:50:40
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/app/oracle/oradata/ORA19C/system01.dbf'
RMAN> restore datafile 1;
Starting restore at 19-APR-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=139 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /app/oracle/oradata/ORA19C/system01.dbf
channel ORA_DISK_1: reading from backup piece /app/oracle/rman/ORA19C_221q0svv_1_1_20230419.bk
channel ORA_DISK_1: piece handle=/app/oracle/rman/ORA19C_221q0svv_1_1_20230419.bk tag=TAG20230419T193527
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 19-APR-23
RMAN> recover datafile 1;
Starting recover at 19-APR-23
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-APR-23
RMAN> alter database open;
Statement processed
RMAN> exit;
Recovery Manager complete.
[/app/oracle/oradata/ORA19C]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 19 19:51:52 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select file#, status, name from v$datafile;
FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
1 SYSTEM
/app/oracle/oradata/ORA19C/system01.dbf
2 ONLINE
/app/oracle/oradata/ORA19C/user_ts/DAMIN_IDX_TS.dbf
3 ONLINE
/app/oracle/oradata/ORA19C/sysaux01.dbf
FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
4 ONLINE
/app/oracle/oradata/ORA19C/undotbs01.dbf
5 ONLINE
/app/oracle/oradata/ORA19C/user_ts/DAMIN_DATA_TS.dbf
7 ONLINE
/app/oracle/oradata/ORA19C/users01.dbf
6 rows selected.
SQL>
## 사용자 tablespace 단위 복구
※ 사용자 테이블스페이스가 손실된 상황
### 복구 과정 요약
1. 풀백업
RMAN> backup database plus archivelog;
RMAN> list backup of controlfile; -- piece name 확인
2. 사용자 tablespace 삭제
3. 손상된 파일 복원 및 복구
restore controlfile from 'controlfile의 piece name';
RMAN> alter database mount;
RMAN> list backup of tablespace DAMIN DATA_TS; -- SCN확인
RMAN> run {
2> SET UNTIL SCN (위에서 확인 SCN번호); -- 여기서 SCN으로 해도되지만 시간으로도 가능
3> RESTORE DATABASE;
4> }
RMAN> run {
2> SET UNTIL SCN (위에서 확인 SCN번호);
3> RECOVER DATABASE;
4> }
4. DB 오픈
alter database open resetlogs;
실행 과정 첨부
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 19 17:50:02 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19C (DBID=1186485125)
RMAN> delete backup;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
27 27 1 1 AVAILABLE DISK /app/oracle/rman/ORA19C_111q0j79_1_1_20230419.bk
28 28 1 1 AVAILABLE DISK /app/oracle/rman/ORA19C_121q0j7a_1_1_20230419.bk
29 29 1 1 AVAILABLE DISK /app/oracle/rman/ORA19C_131q0j7i_1_1_20230419.bk
30 30 1 1 AVAILABLE DISK /app/oracle/rman/db_ctl_c-1186485125-20230419-02
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/app/oracle/rman/ORA19C_111q0j79_1_1_20230419.bk RECID=27 STAMP=1134578921
deleted backup piece
backup piece handle=/app/oracle/rman/ORA19C_121q0j7a_1_1_20230419.bk RECID=28 STAMP=1134578922
deleted backup piece
backup piece handle=/app/oracle/rman/ORA19C_131q0j7i_1_1_20230419.bk RECID=29 STAMP=1134578930
deleted backup piece
backup piece handle=/app/oracle/rman/db_ctl_c-1186485125-20230419-02 RECID=30 STAMP=1134580650
Deleted 4 objects
RMAN> backup database plus archivelog;
Starting backup at 19-APR-23
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=1 STAMP=1134474445
input archived log thread=1 sequence=10 RECID=2 STAMP=1134474462
input archived log thread=1 sequence=12 RECID=3 STAMP=1134481365
input archived log thread=1 sequence=13 RECID=4 STAMP=1134481556
input archived log thread=1 sequence=14 RECID=5 STAMP=1134481576
input archived log thread=1 sequence=15 RECID=6 STAMP=1134481586
input archived log thread=1 sequence=16 RECID=7 STAMP=1134576909
input archived log thread=1 sequence=17 RECID=8 STAMP=1134578921
input archived log thread=1 sequence=18 RECID=9 STAMP=1134578929
channel ORA_DISK_1: starting piece 1 at 19-APR-23
channel ORA_DISK_1: finished piece 1 at 19-APR-23
piece handle=/app/oracle/rman/ORA19C_181q0mqt_1_1_20230419.bk tag=TAG20230419T175021 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=13 STAMP=1134582620
channel ORA_DISK_1: starting piece 1 at 19-APR-23
channel ORA_DISK_1: finished piece 1 at 19-APR-23
piece handle=/app/oracle/rman/ORA19C_191q0mqu_1_1_20230419.bk tag=TAG20230419T175021 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-APR-23
Starting backup at 19-APR-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/app/oracle/oradata/ORA19C/user_ts/DAMIN_IDX_TS.dbf
input datafile file number=00005 name=/app/oracle/oradata/ORA19C/user_ts/DAMIN_DATA_TS.dbf
input datafile file number=00001 name=/app/oracle/oradata/ORA19C/system01.dbf
input datafile file number=00003 name=/app/oracle/oradata/ORA19C/sysaux01.dbf
input datafile file number=00004 name=/app/oracle/oradata/ORA19C/undotbs01.dbf
input datafile file number=00007 name=/app/oracle/oradata/ORA19C/users01.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-23
channel ORA_DISK_1: finished piece 1 at 19-APR-23
piece handle=/app/oracle/rman/ORA19C_1a1q0mqv_1_1_20230419.bk tag=TAG20230419T175023 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 19-APR-23
Starting backup at 19-APR-23
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=14 STAMP=1134582626
channel ORA_DISK_1: starting piece 1 at 19-APR-23
channel ORA_DISK_1: finished piece 1 at 19-APR-23
piece handle=/app/oracle/rman/ORA19C_1b1q0mr2_1_1_20230419.bk tag=TAG20230419T175026 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-APR-23
Starting Control File and SPFILE Autobackup at 19-APR-23
piece handle=/app/oracle/rman/db_ctl_c-1186485125-20230419-03 comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-23
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
35 Full 10.52M DISK 00:00:00 19-APR-23
BP Key: 35 Status: AVAILABLE Compressed: NO Tag: TAG20230419T175027
Piece Name: /app/oracle/rman/db_ctl_c-1186485125-20230419-03
Control File Included: Ckp SCN: 2794401 Ckp time: 19-APR-23
RMAN> ^C
user interrupt received
RMAN> list backup of tablespace DAMIN_DATA_TS;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
33 Full 1.23G DISK 00:00:00 19-APR-23
BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20230419T175023
Piece Name: /app/oracle/rman/ORA19C_1a1q0mqv_1_1_20230419.bk
List of Datafiles in backup set 33
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 2794375 19-APR-23 NO /app/oracle/oradata/ORA19C/user_ts/DAMIN_DATA_TS.dbf
RMAN> exit
Recovery Manager complete.
[/home/oracle]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 19 17:51:58 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> drop tablespace DAMIN_DATA_TS including contents and datafiles;
Tablespace dropped.
SQL> select * from damin.department;
select * from damin.department
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[/home/oracle]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 19 17:52:51 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19C (DBID=1186485125)
RMAN> shutdown immediate;
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 1610609200 bytes
Fixed Size 8897072 bytes
Variable Size 385875968 bytes
Database Buffers 1207959552 bytes
Redo Buffers 7876608 bytes
RMAN> restore controlfile from '/app/oracle/rman/db_ctl_c-1186485125-20230419-03';
Starting restore at 19-APR-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=261 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/app/oracle/oradata/ORA19C/control01.ctl
output file name=/app/oracle/oradata/ORA19C/control02.ctl
output file name=/app/oracle/oradata/ORA19C/control03.ctl
Finished restore at 19-APR-23
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN> list backup of tablespace DAMIN_DATA_TS;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
33 Full 1.23G DISK 00:00:00 19-APR-23
BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20230419T175023
Piece Name: /app/oracle/rman/ORA19C_1a1q0mqv_1_1_20230419.bk
List of Datafiles in backup set 33
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 2794375 19-APR-23 NO /app/oracle/oradata/ORA19C/user_ts/DAMIN_DATA_TS.dbf
RMAN> run {
2> set until scn 2794401;
3> restore database;
4> }
executing command: SET until clause
Starting restore at 19-APR-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /app/oracle/oradata/ORA19C/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /app/oracle/oradata/ORA19C/user_ts/DAMIN_IDX_TS.dbf
channel ORA_DISK_1: restoring datafile 00003 to /app/oracle/oradata/ORA19C/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /app/oracle/oradata/ORA19C/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /app/oracle/oradata/ORA19C/user_ts/DAMIN_DATA_TS.dbf
channel ORA_DISK_1: restoring datafile 00007 to /app/oracle/oradata/ORA19C/users01.dbf
channel ORA_DISK_1: reading from backup piece /app/oracle/rman/ORA19C_1a1q0mqv_1_1_20230419.bk
channel ORA_DISK_1: piece handle=/app/oracle/rman/ORA19C_1a1q0mqv_1_1_20230419.bk tag=TAG20230419T175023
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 19-APR-23
RMAN> run {
2> set until scn 2794401;
3> recover database;
4> }
executing command: SET until clause
Starting recover at 19-APR-23
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /app/oracle/oradata/ORA19C/redo02.log
archived log for thread 1 with sequence 3 is already on disk as file /app/oracle/oradata/ORA19C/redo03.log
archived log file name=/app/oracle/oradata/ORA19C/redo02.log thread=1 sequence=2
archived log file name=/app/oracle/oradata/ORA19C/redo03.log thread=1 sequence=3
media recovery complete, elapsed time: 00:00:00
Finished recover at 19-APR-23
RMAN> alter database open resetlogs;
Statement processed
RMAN> exit
Recovery Manager complete.
[/home/oracle]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 19 17:56:06 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select segment_name from dba_segments where tablespace_name='DAMIN_DATA_TS';
SEGMENT_NAME
--------------------------------------------------------------------------------
PROFESSOR
DEPARTMENT
SQL> select * from damin.department;
DEPTNO DNAME PART BUILD
---------- ------------------------- ---------- --------------------
101 컴퓨터공학과 100 정보관
102 멀티미디어공학과 100 멀티미디어관
103 소프트웨어공학과 100 소프트웨어관
201 전자공학과 200 전자제어관
202 기계공학과 200 기계실험관
203 화학공학과 200 화학실습관
301 문헌정보학과 300 인문관
100 컴퓨터정보학부 10
200 메카트로닉스학부 10
300 인문사회학부 20
10 공과대학
DEPTNO DNAME PART BUILD
---------- ------------------------- ---------- --------------------
20 인문대학
12 rows selected.
## Table 단위 복구 (RMAN table recovery)
※ 테이블이 손실된 상황
복구 과정 요약
TABLE 복구는 자동으로 복구까지 완료해주기 때문에 쉽게 진행 가능하다.
1. 풀백업
backup database plus archivelog;
2. sysdate 확인 후 table 삭제
SQL> alter session set nls_date_format = 'YYYY-MM-DD:HH24:MI:SS';
SQL> select sysdate from dual;
SQL> drop table department purge;
3. 테이블 복원 및 복구 (자동)
→ 임시 저장 경로만 설정해주면 자동으로 복구까지 완료
→ recover table을 진행하면 clone db 생성하여 테이블을 dump해주고 원래 테이블에서 받아줌으로써 복구를 완료
→ 시점복구는 시간으로 복구해줘도 되고 SCN을 확인하여 복구해줘도 된다.
RMAN> recover table damin.department until time "to_date('2023/04/19 18:32:19',
'yyyy-mm-dd:hh24:mi:ss')" auxiliary destination '/home/oracle/temp';
-- to_date대신 SCN을 사용해줘도 가능
실행 과정 첨부
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 19 18:26:13 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1610609200 bytes
Fixed Size 8897072 bytes
Variable Size 385875968 bytes
Database Buffers 1207959552 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/backup/ARCH/
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL> exit;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Pr oduction
Version 19.3.0.0.0
[/home/oracle]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 19 18:28:15 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19C (DBID=1186485125)
RMAN> delete backup;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
36 36 1 1 AVAILABLE DISK /app/oracle/rman/ORA19C_1f1q0occ _1_1_20230419.bk
37 37 1 1 AVAILABLE DISK /app/oracle/rman/ORA19C_1g1q0ocd _1_1_20230419.bk
38 38 1 1 AVAILABLE DISK /app/oracle/rman/ORA19C_1h1q0oce _1_1_20230419.bk
39 39 1 1 AVAILABLE DISK /app/oracle/rman/ORA19C_1i1q0ocf _1_1_20230419.bk
40 40 1 1 AVAILABLE DISK /app/oracle/rman/ORA19C_1j1q0oci _1_1_20230419.bk
41 41 1 1 AVAILABLE DISK /app/oracle/rman/db_ctl_c-118648 5125-20230419-05
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/app/oracle/rman/ORA19C_1f1q0occ_1_1_20230419.bk RECID=36 ST AMP=1134584204
deleted backup piece
backup piece handle=/app/oracle/rman/ORA19C_1g1q0ocd_1_1_20230419.bk RECID=37 ST AMP=1134584205
deleted backup piece
backup piece handle=/app/oracle/rman/ORA19C_1h1q0oce_1_1_20230419.bk RECID=38 ST AMP=1134584206
deleted backup piece
backup piece handle=/app/oracle/rman/ORA19C_1i1q0ocf_1_1_20230419.bk RECID=39 ST AMP=1134584207
deleted backup piece
backup piece handle=/app/oracle/rman/ORA19C_1j1q0oci_1_1_20230419.bk RECID=40 ST AMP=1134584210
deleted backup piece
backup piece handle=/app/oracle/rman/db_ctl_c-1186485125-20230419-05 RECID=41 ST AMP=1134584211
Deleted 6 objects
RMAN> backup database plus archivelog;
Starting backup at 19-APR-23
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=1 STAMP=1134474445
input archived log thread=1 sequence=10 RECID=2 STAMP=1134474462
input archived log thread=1 sequence=12 RECID=3 STAMP=1134481365
input archived log thread=1 sequence=13 RECID=4 STAMP=1134481556
input archived log thread=1 sequence=14 RECID=5 STAMP=1134481576
input archived log thread=1 sequence=15 RECID=6 STAMP=1134481586
input archived log thread=1 sequence=16 RECID=7 STAMP=1134576909
input archived log thread=1 sequence=17 RECID=8 STAMP=1134578921
input archived log thread=1 sequence=18 RECID=9 STAMP=1134578929
channel ORA_DISK_1: starting piece 1 at 19-APR-23
channel ORA_DISK_1: finished piece 1 at 19-APR-23
piece handle=/app/oracle/rman/ORA19C_1l1q0p2j_1_1_20230419.bk tag=TAG20230419T18 2835 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=18 STAMP=1134582938
input archived log thread=1 sequence=2 RECID=19 STAMP=1134582938
input archived log thread=1 sequence=3 RECID=20 STAMP=1134582938
channel ORA_DISK_1: starting piece 1 at 19-APR-23
channel ORA_DISK_1: finished piece 1 at 19-APR-23
piece handle=/app/oracle/rman/ORA19C_1m1q0p2k_1_1_20230419.bk tag=TAG20230419T18 2835 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=21 STAMP=1134584204
input archived log thread=1 sequence=2 RECID=22 STAMP=1134584210
input archived log thread=1 sequence=3 RECID=23 STAMP=1134584787
input archived log thread=1 sequence=4 RECID=24 STAMP=1134584914
channel ORA_DISK_1: starting piece 1 at 19-APR-23
channel ORA_DISK_1: finished piece 1 at 19-APR-23
piece handle=/app/oracle/rman/ORA19C_1n1q0p2l_1_1_20230419.bk tag=TAG20230419T18 2835 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-APR-23
Starting backup at 19-APR-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/app/oracle/oradata/ORA19C/user_ts/DAMIN_I DX_TS.dbf
input datafile file number=00005 name=/app/oracle/oradata/ORA19C/user_ts/DAMIN_D ATA_TS.dbf
input datafile file number=00001 name=/app/oracle/oradata/ORA19C/system01.dbf
input datafile file number=00003 name=/app/oracle/oradata/ORA19C/sysaux01.dbf
input datafile file number=00004 name=/app/oracle/oradata/ORA19C/undotbs01.dbf
input datafile file number=00007 name=/app/oracle/oradata/ORA19C/users01.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-23
channel ORA_DISK_1: finished piece 1 at 19-APR-23
piece handle=/app/oracle/rman/ORA19C_1o1q0p2n_1_1_20230419.bk tag=TAG20230419T18 2838 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 19-APR-23
Starting backup at 19-APR-23
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=25 STAMP=1134584934
channel ORA_DISK_1: starting piece 1 at 19-APR-23
channel ORA_DISK_1: finished piece 1 at 19-APR-23
piece handle=/app/oracle/rman/ORA19C_1p1q0p36_1_1_20230419.bk tag=TAG20230419T18 2854 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-APR-23
Starting Control File and SPFILE Autobackup at 19-APR-23
piece handle=/app/oracle/rman/db_ctl_c-1186485125-20230419-06 comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-23
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
47 Full 10.52M DISK 00:00:00 19-APR-23
BP Key: 47 Status: AVAILABLE Compressed: NO Tag: TAG20230419T182855
Piece Name: /app/oracle/rman/db_ctl_c-1186485125-20230419-06
Control File Included: Ckp SCN: 2901173 Ckp time: 19-APR-23
[/home/oracle]$ sqlplus damin/damin
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 19 18:31:09 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Wed Apr 19 2023 17:34:36 +09:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select * from department;
DEPTNO DNAME PART BUILD
---------- ------------------------- ---------- --------------------
101 컴퓨터공학과 100 정보관
102 멀티미디어공학과 100 멀티미디어관
103 소프트웨어공학과 100 소프트웨어관
201 전자공학과 200 전자제어관
202 기계공학과 200 기계실험관
203 화학공학과 200 화학실습관
301 문헌정보학과 300 인문관
100 컴퓨터정보학부 10
200 메카트로닉스학부 10
300 인문사회학부 20
10 공과대학
DEPTNO DNAME PART BUILD
---------- ------------------------- ---------- --------------------
20 인문대학
12 rows selected.
SQL> alter session set nls_date_format = 'YYYY-MM-DD:HH24:MI:SS';
Session altered.
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
-------------------
2023-04-19:18:32:19
SQL> drop table department purge;
Table dropped.
SQL> exit;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Pr oduction
Version 19.3.0.0.0
[/home/oracle]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 19 18:51:25 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19C (DBID=1186485125)
RMAN> recover table damin.department until time "to_date('2023/04/19 18:32:19',
'yyyy-mm-dd:hh24:mi:ss')" auxiliary destination '/home/oracle/temp';
Starting recover at 19-APR-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=147 device type=DISK
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='rwnr'
initialization parameters used for automatic instance:
db_name=ORA19C
db_unique_name=rwnr_pitr_ORA19C
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/app/oracle
_system_trig_enabled=FALSE
sga_target=1536M
processes=200
db_create_file_dest=/home/oracle/temp
log_archive_dest_1='location=/home/oracle/temp'
#No auxiliary parameter file used
starting up automatic instance ORA19C
Oracle instance started
Total System Global Area 1610609200 bytes
Fixed Size 8897072 bytes
Variable Size 385875968 bytes
Database Buffers 1207959552 bytes
Redo Buffers 7876608 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2023/04/19 18:32:19', 'yyyy-mm-dd:hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET until clause
Starting restore at 19-APR-23
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=244 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /app/oracle/rman/db_ctl_c-1186 485125-20230419-06
channel ORA_AUX_DISK_1: piece handle=/app/oracle/rman/db_ctl_c-1186485125-202304 19-06 tag=TAG20230419T182855
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/temp/ORA19C/controlfile/o1_mf_l3zgldn2_.ctl
Finished restore at 19-APR-23
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2023/04/19 18:32:19', 'yyyy-mm-dd:hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 3 to new;
set newname for clone tempfile 1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/temp/ORA19C/datafile/o1_mf_temp_%u_.tmp in co ntrol file
Starting restore at 19-APR-23
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /home/oracle/temp/ORA19C/dat afile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /home/oracle/temp/ORA19C/dat afile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/temp/ORA19C/dat afile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /app/oracle/rman/ORA19C_1o1q0p 2n_1_1_20230419.bk
channel ORA_AUX_DISK_1: piece handle=/app/oracle/rman/ORA19C_1o1q0p2n_1_1_202304 19.bk tag=TAG20230419T182838
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 19-APR-23
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1134586473 file name=/home/oracle/temp/ORA19C/ datafile/o1_mf_system_l3zgllmn_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=1134586473 file name=/home/oracle/temp/ORA19C/ datafile/o1_mf_undotbs1_l3zglln3_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1134586473 file name=/home/oracle/temp/ORA19C/ datafile/o1_mf_sysaux_l3zglln0_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2023/04/19 18:32:19', 'yyyy-mm-dd:hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 4 online";
sql clone "alter database datafile 3 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 3 online
Starting recover at 19-APR-23
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 5 is already on disk as file /home/oracl e/backup/ARCH/1_5_1134582938.dbf
archived log for thread 1 with sequence 6 is already on disk as file /home/oracl e/backup/ARCH/1_6_1134582938.dbf
archived log file name=/home/oracle/backup/ARCH/1_5_1134582938.dbf thread=1 sequ ence=5
archived log file name=/home/oracle/backup/ARCH/1_6_1134582938.dbf thread=1 sequ ence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 19-APR-23
sql statement: alter database open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/home/oracle/temp/ORA19C/controlfile/o1_mf_l3zgldn2_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1610609200 bytes
Fixed Size 8897072 bytes
Variable Size 385875968 bytes
Database Buffers 1207959552 bytes
Redo Buffers 7876608 bytes
sql statement: alter system set control_files = ''/home/oracle/temp/ORA19C/co ntrolfile/o1_mf_l3zgldn2_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1610609200 bytes
Fixed Size 8897072 bytes
Variable Size 385875968 bytes
Database Buffers 1207959552 bytes
Redo Buffers 7876608 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2023/04/19 18:32:19', 'yyyy-mm-dd:hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 5 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 5;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 19-APR-23
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=91 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /home/oracle/temp/RWNR_PITR_ ORA19C/datafile/o1_mf_damin_da_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /app/oracle/rman/ORA19C_1o1q0p 2n_1_1_20230419.bk
channel ORA_AUX_DISK_1: piece handle=/app/oracle/rman/ORA19C_1o1q0p2n_1_1_202304 19.bk tag=TAG20230419T182838
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 19-APR-23
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=1134586553 file name=/home/oracle/temp/RWNR_PI TR_ORA19C/datafile/o1_mf_damin_da_l3zgo276_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2023/04/19 18:32:19', 'yyyy-mm-dd:hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 5 online";
# recover and open resetlogs
recover clone database tablespace "DAMIN_DATA_TS", "SYSTEM", "UNDOTBS1", "SYSAU X" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 5 online
Starting recover at 19-APR-23
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 5 is already on disk as file /home/oracl e/backup/ARCH/1_5_1134582938.dbf
archived log for thread 1 with sequence 6 is already on disk as file /home/oracl e/backup/ARCH/1_6_1134582938.dbf
archived log file name=/home/oracle/backup/ARCH/1_5_1134582938.dbf thread=1 sequ ence=5
archived log file name=/home/oracle/backup/ARCH/1_6_1134582938.dbf thread=1 sequ ence=6
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-APR-23
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle/temp''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle/temp''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle /temp''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle /temp''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_rwnr_Embe":
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATI STICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
EXPDP> . . exported "DAMIN"."DEPARTMENT" 6.851 KB 12 rows
EXPDP> Master table "SYS"."TSPITR_EXP_rwnr_Embe" successfully loaded/unloaded
EXPDP> ********************************************************************** ********
EXPDP> Dump file set for SYS.TSPITR_EXP_rwnr_Embe is:
EXPDP> /home/oracle/temp/tspitr_rwnr_97387.dmp
EXPDP> Job "SYS"."TSPITR_EXP_rwnr_Embe" successfully completed at Wed Apr 19 18:56:27 2023 elapsed 0 00:00:21
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_rwnr_bxtc" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_rwnr_bxtc":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "DAMIN"."DEPARTMENT" 6.851 KB 12 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATI STICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_rwnr_bxtc" successfully completed at Wed Apr 19 18:56:51 2023 elapsed 0 00:00:21
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /home/oracle/temp/ORA19C/datafile/o1_mf_temp_l3zglw1y_.t mp deleted
auxiliary instance file /home/oracle/temp/RWNR_PITR_ORA19C/onlinelog/o1_mf_3_l3z goccg_.log deleted
auxiliary instance file /home/oracle/temp/RWNR_PITR_ORA19C/onlinelog/o1_mf_2_l3z gocbx_.log deleted
auxiliary instance file /home/oracle/temp/RWNR_PITR_ORA19C/onlinelog/o1_mf_1_l3z goc9j_.log deleted
auxiliary instance file /home/oracle/temp/RWNR_PITR_ORA19C/datafile/o1_mf_damin_ da_l3zgo276_.dbf deleted
auxiliary instance file /home/oracle/temp/ORA19C/datafile/o1_mf_sysaux_l3zglln0_ .dbf deleted
auxiliary instance file /home/oracle/temp/ORA19C/datafile/o1_mf_undotbs1_l3zglln 3_.dbf deleted
auxiliary instance file /home/oracle/temp/ORA19C/datafile/o1_mf_system_l3zgllmn_ .dbf deleted
auxiliary instance file /home/oracle/temp/ORA19C/controlfile/o1_mf_l3zgldn2_.ctl deleted
auxiliary instance file tspitr_rwnr_97387.dmp deleted
Finished recover at 19-APR-23
RMAN> exit;
Recovery Manager complete.
[/home/oracle]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 19 18:57:02 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select * from damin.department;
DEPTNO DNAME PART BUILD
---------- ------------------------- ---------- --------------------
101 컴퓨터공학과 100 정보관
102 멀티미디어공학과 100 멀티미디어관
103 소프트웨어공학과 100 소프트웨어관
201 전자공학과 200 전자제어관
202 기계공학과 200 기계실험관
203 화학공학과 200 화학실습관
301 문헌정보학과 300 인문관
100 컴퓨터정보학부 10
200 메카트로닉스학부 10
300 인문사회학부 20
10 공과대학
DEPTNO DNAME PART BUILD
---------- ------------------------- ---------- --------------------
20 인문대학
12 rows selected.
'ORACLE > ORACLE_Backup&Recovery' 카테고리의 다른 글
| RMAN을 이용한 CloneDB 생성 (0) | 2023.04.20 |
|---|---|
| Hotbackup CloneDB 생성 (0) | 2023.04.20 |
| RMAN 복구 (완전/불완전) (0) | 2023.04.19 |
| RMAN (Recovery Manager) 백업 (일반/증분) (0) | 2023.04.18 |
| ORACLE LogMiner (0) | 2023.04.17 |
