Oracle Database
[ORACLE] Oracle Cloud Rman Recovery Table ( 테이블 복구 )
- -
반응형
RMAN 백업에서 테이블 및 테이블 파티션 복구 목적
- RMAN을 사용하면 나머지 데이터베이스 개체에 영향을 주지 않고 하나 이상의 테이블 또는 테이블 파티션을 지정된 시점으로 복구할 수 있습니다. 이전에 생성한 RMAN 백업을 사용하여 테이블과 테이블 파티션을 지정된 시점으로 복구할 수 있습니다
테이블 및 테이블 파티션을 복구하는 데 필요한 백업
- 테이블 또는 테이블 파티션을 복구하려면 UNDO, SYSTEM, SYSAUX, 및 테이블 또는 테이블 파티션이 포함된 테이블스페이스 의 전체 백업이 있어야 합니다.
RMAN 백업에서 테이블 및 테이블 파티션 복구의 제한 사항
1. SYS스키마에 속한 테이블 및 테이블 파티션 은 복구할 수 없습니다.
2. 테이블과에서 테이블 파티션 SYSTEM과 SYSAUX테이블 스페이스를 복구 할 수 없습니다.
3. 대기 데이터베이스의 테이블 및 테이블 파티션은 복구할 수 없습니다.
4. 명명된 NOT NULL제약 조건이 있는 테이블 은 REMAP옵션 으로 복구할 수 없습니다 .
| 1.테스트 개요 |
- 고객사에서 개발사의 실수로 한 결제 Table을 Truncate하는 상황이 발생하였다.
하지만 논리적인 백업을 수행하고 있지 않았고 Oracle Cloud의 Rman Autobackup ( 일반 Rman Backup과 동일 ) 만을 실행하고있었다.
- 이런 경우 기존 10g, 11g는 Manual하게 Clone DB를 생성하여 Export / Import 작업을 수행하였다.
12C에서는 Rman Recovering Table 기능을 사용하여 보다 쉽게 복구를 할수 있게 되었다.
| 2.테스트 시나리오 |
1) 테이블 생성
2) OCI RMAN BACKUP
3) Truncate Table
4) Rman Recovery Table 복구
5) 테이블 확인
| 3. 실습 |
- Oracle 19C Enterprise Edition Release 19.11.0.0.0 ( Single ) multi tenant
* OCI는 12C 이상부터는 multi tenant환경으로 만들어지기 때문에 PDB에서 해당 실습을 진행하도록 하겠습니다.
#TEST DATA 생성
** RAMN TABLE RECOVERY는 ARCHIVELOG 모드여야한다. **
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
** PDB로 접속 **
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TEST_PDB1 READ WRITE NO
SQL> alter session set container=test_pdb1;
Session altered.
** TEST DATA 준비 **
SQL> create tablespace lee datafile size 32765M;
SQL> create user lee identified by lee default tablespace lee;
SQL> select username,default_tablespace from dba_users where username='LEE';
USERNAME DEFAULT_TABLESPACE
---------------------------------------- ------------------------------
LEE LEE
SQL> grant dba to lee;
SQL> create table lee.test as select * from dba_objects;
SQL> create index lee.test_index on lee.test(owner);
SQL> select object_name,object_type from dba_objects where owner='LEE';
OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
TEST_INDEX INDEX
TEST TABLE
#BACKUP DATABASE
- RMAN BACKUP 명령어를 사용하여도 되지만 OCI의 Console을 이용하여 Backup 해보았습니다.
** Backup File 확인 **
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1 Full 17.95M DISK 00:00:00 2021/08/25 12:05:24
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20210825T120524
Piece Name: +RECO/TEST_TEST/AUTOBACKUP/2021_08_25/s_1081512324.262.1081512325
SPFILE Included: Modification time: 2021/08/25 11:55:42
SPFILE db_unique_name: TEST_TEST
Control File Included: Ckp SCN: 2594870 Ckp time: 2021/08/25 12:05:24
(생략)
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
5 Full 2.25M SBT_TAPE 00:00:02 2021/08/25 14:04:43
BP Key: 5 Status: AVAILABLE Compressed: YES Tag: DBTLONGTERM1629866535460DIC
Handle: DBTLongterm1629866535460diC__TEST_2379657339_0507dbbp_5_1_1_20210825_1081519481_set5 Media: swiftobjectstorage.a..ud.com/v1/dbbackupicn/bv9xPr3rSswWk8A4eGQw
Keep: BACKUP_LOGS Until: 2101/01/18 14:04:40
List of Datafiles in backup set 5
Container ID: 3, PDB Name: TEST_PDB1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
14 Full 2605046 2021/08/25 14:04:41 NO +DATA/TEST_TEST/CA5A52EDFC9A3C13E0531603000A15EA/DATAFILE/lee.273.1081517077
(생략)
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
1 B F A DISK 2021/08/25 12:05:24 1 1 NO TAG20210825T120524
2 B F A DISK 2021/08/25 13:35:27 1 1 NO TAG20210825T133527
3 B F A SBT_TAPE 2021/08/25 13:56:05 1 1 YES AUTO
4 B F A SBT_TAPE 2021/08/25 13:56:06 1 1 YES TAG20210825T135606
5 B F A SBT_TAPE 2021/08/25 14:04:43 1 1 YES DBTLONGTERM1629866535460DIC
6 B F A SBT_TAPE 2021/08/25 14:04:58 1 1 YES DBTLONGTERM1629866535460DIC
7 B F A SBT_TAPE 2021/08/25 14:04:58 1 1 YES DBTLONGTERM1629866535460DIC
8 B F A SBT_TAPE 2021/08/25 14:05:00 1 1 YES DBTLONGTERM1629866535460DIC
9 B F A SBT_TAPE 2021/08/25 14:05:03 1 1 YES DBTLONGTERM1629866535460DIC
10 B F A SBT_TAPE 2021/08/25 14:05:11 1 1 YES DBTLONGTERM1629866535460DIC
11 B F A SBT_TAPE 2021/08/25 14:05:12 1 1 YES DBTLONGTERM1629866535460DIC
12 B F A SBT_TAPE 2021/08/25 14:05:17 1 1 YES DBTLONGTERM1629866535460DIC
13 B F A SBT_TAPE 2021/08/25 14:05:16 1 1 YES DBTLONGTERM1629866535460DIC
14 B F A SBT_TAPE 2021/08/25 14:05:17 1 1 YES DBTLONGTERM1629866535460DIC
15 B A A SBT_TAPE 2021/08/25 14:05:22 1 1 YES DBTLONGTERM1629866535460DIC
16 B F A SBT_TAPE 2021/08/25 14:05:24 1 1 YES DBTLONGTERM1629866535460DIC
17 B F A SBT_TAPE 2021/08/25 14:05:27 1 1 YES DBTLONGTERM1629866535460DIC
18 B F A SBT_TAPE 2021/08/25 14:05:30 1 1 YES DBTLONGTERM1629866535460DIC
19 B F A SBT_TAPE 2021/08/25 14:05:32 1 1 YES TAG20210825T140531
#TEST TABLE TRUNCATE
** TRUNCATE 발생 **
SQL> truncate table lee.test;
Table truncated.
#RECOVER DATABASE
- RMAN RECOVER TABLE을 이용하여 CLONE DB를 만든후 자동으로 Export / Import 하며 Import 이후에는 Dumpfile / Clone DB가 삭제되도록 명령어를 실행하였다.
RMAN> run {
RECOVER TABLE LEE.'TEST' OF PLUGGABLE DATABASE TEST_PDB1
UNTIL TIME "to_date('2021/08/25 14:40:00','yyyy/mm/dd hh24:mi:ss')"
AUXILIARY DESTINATION '/u01/app/oracle/dump'
REMAP TABLE 'LEE'.'TEST':'TEST_BAK';
}
Starting recover at 2021/08/25 15:09:23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=931 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=1087 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Database Backup Service Library VER=21.0.0.1
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=170 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Database Backup Service Library VER=21.0.0.1
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: SID=321 device type=SBT_TAPE
channel ORA_SBT_TAPE_3: Oracle Database Backup Service Library VER=21.0.0.1
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: SID=476 device type=SBT_TAPE
channel ORA_SBT_TAPE_4: Oracle Database Backup Service Library VER=21.0.0.1
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 TEST_PDB1:SYSTEM
Tablespace UNDOTBS1
Tablespace TEST_PDB1:UNDOTBS1
Creating automatic instance, with SID='omkF'
initialization parameters used for automatic instance:
db_name=TEST
db_unique_name=omkF_pitr_TEST_PDB1_TEST
compatible=19.0.0.0
db_block_size=8192
db_files=1024
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
db_domain=subnet06081035.goodusdatavcn.oraclevcn.com
sga_target=29184M
processes=200
db_create_file_dest=/u01/app/oracle/dump
log_archive_dest_1='location=/u01/app/oracle/dump'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance TEST* 여기서 문제가 발생했다... Recover table를 할때 parameter 파일을 지정하지않아 기존 parameter 파일을 읽게되면서 SGA SIZE가 과도하게 사용되었다.
OS Memory가 60G인데 반해 운영 DB가 현재 29814M를 사용하고있느데 Clone DB까지 29814M를 사용하게되면 안되기 떄문에
이번에는 PARAMETER 파일을 새로 생성하여 RECOVER TABLE을 다시 하였다.!!
#PARAMETER 생성 & RECOVER TABLE
- 최소한의 Parameter를 설정한 이후 Recover table을 실행하였다.
기존 RMAN 명령어에 "SET AUXILIARY INSTANCE PARAMETER FILE TO '/u01/app/oracle/dump/pfile.ora'; " 명령어를 추가하여 Parameter File을 읽도록 하였다.
#PFILE.ORA
| [oracle@lee dump]$ cat pfile.ora db_name=TEST db_unique_name=tttt_test compatible=19.0.0.0 db_block_size=8192 db_files=1024 diagnostic_dest=/u01/app/oracle _system_trig_enabled=FALSE db_domain=subnet06081035.goodusdatavcn.oraclevcn.com sga_target=2048M SGA_MAX_SIZE=2048M processes=200 db_create_file_dest=/u01/app/oracle/dump log_archive_dest_1='location=/u01/app/oracle/dump' enable_pluggable_database=true _clone_one_pdb_recovery=true *빨간부분만 주의해서 바꿔 주면된다. /u01/app/oracle/dump 경로에 CLONE DB의 DATAFILE과 이후 복구된 Data의 PUMP파일이 생성된다. |
** Pfile 생성 & 수정 **
SQL> create pfile='/u01/app/oracle/dump/pfile.ora' from spfile;
File created.
[oracle@lee dump]$ cat pfile.ora
db_name=TEST
db_unique_name=tttt_test
compatible=19.0.0.0
db_block_size=8192
db_files=1024
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
db_domain=subnet06081035.goodusdatavcn.oraclevcn.com
sga_target=2048M
SGA_MAX_SIZE=2048M
processes=200
db_create_file_dest=/u01/app/oracle/dump
log_archive_dest_1='location=/u01/app/oracle/dump'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
** RMAN RECOVER **
[oracle@lee dump]$ rman target /
RMAN> run {
SET AUXILIARY INSTANCE PARAMETER FILE TO '/u01/app/oracle/dump/pfile.ora';
RECOVER TABLE LEE.'TEST' OF PLUGGABLE DATABASE TEST_PDB1
UNTIL TIME "to_date('2021/08/25 14:40:00','yyyy/mm/dd hh24:mi:ss')"
AUXILIARY DESTINATION '/u01/app/oracle/dump'
REMAP TABLE 'LEE'.'TEST':'TEST_BAK';
}
executing command: SET auxiliary parameter file
using target database control file instead of recovery catalog
Starting recover at 25-AUG-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=930 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=1086 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Database Backup Service Library VER=21.0.0.1
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=168 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Database Backup Service Library VER=21.0.0.1
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: SID=322 device type=SBT_TAPE
channel ORA_SBT_TAPE_3: Oracle Database Backup Service Library VER=21.0.0.1
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: SID=477 device type=SBT_TAPE
channel ORA_SBT_TAPE_4: Oracle Database Backup Service Library VER=21.0.0.1
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 TEST_PDB1:SYSTEM
Tablespace UNDOTBS1
Tablespace TEST_PDB1:UNDOTBS1
Creating automatic instance, with SID='yxfg'
using contents of file /u01/app/oracle/dump/pfile.ora
initialization parameters used for automatic instance:
db_name=TEST
db_unique_name=yxfg_pitr_TEST_PDB1_TEST
compatible=19.0.0.0
db_block_size=8192
db_files=1024
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
db_domain=subnet06081035.goodusdatavcn.oraclevcn.com
sga_target=29184M
processes=200
db_create_file_dest=/u01/app/oracle/dump
log_archive_dest_1='location=/u01/app/oracle/dump'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
ifile=/u01/app/oracle/dump/pfile.ora
starting up automatic instance TEST
Oracle instance started
Total System Global Area 2147482136 bytes
Fixed Size 9136664 bytes
Variable Size 520093696 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7639040 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2021/08/25 14:40:00','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 25-AUG-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=211 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=251 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Oracle Database Backup Service Library VER=21.0.0.1
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: SID=288 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: Oracle Database Backup Service Library VER=21.0.0.1
allocated channel: ORA_AUX_SBT_TAPE_3
channel ORA_AUX_SBT_TAPE_3: SID=7 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_3: Oracle Database Backup Service Library VER=21.0.0.1
allocated channel: ORA_AUX_SBT_TAPE_4
channel ORA_AUX_SBT_TAPE_4: SID=46 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_4: Oracle Database Backup Service Library VER=21.0.0.1
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: restoring control file
channel ORA_AUX_SBT_TAPE_1: reading from backup piece c-2379657339-20210825-03
channel ORA_AUX_SBT_TAPE_1: piece handle=c-2379657339-20210825-03 tag=TAG20210825T140531
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/dump/TEST_TEST/controlfile/o1_mf_jlcrkz7d_.ctl
Finished restore at 25-AUG-21
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('2021/08/25 14:40:00','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 8 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 10 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 9 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 4 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 8, 4, 10, 3, 9;
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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/dump/TEST_TEST/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 4 to /u01/app/oracle/dump/TEST_TEST/CA5A52EDFC9A3C13E0531603000A15EA/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 25-AUG-21
using channel ORA_AUX_DISK_1
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
using channel ORA_AUX_SBT_TAPE_3
using channel ORA_AUX_SBT_TAPE_4
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00003 to /u01/app/oracle/dump/TEST_TEST/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece DBTLongterm1629866535460diC__TEST_2379657339_0707dbbp_7_1_1_20210825_1081519481_set7
channel ORA_AUX_SBT_TAPE_2: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_2: restoring datafile 00009 to /u01/app/oracle/dump/TEST_TEST/CA5A52EDFC9A3C13E0531603000A15EA/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_SBT_TAPE_2: reading from backup piece DBTLongterm1629866535460diC__TEST_2379657339_0807dbbp_8_1_1_20210825_1081519481_set8
channel ORA_AUX_SBT_TAPE_3: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_3: restoring datafile 00001 to /u01/app/oracle/dump/TEST_TEST/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_SBT_TAPE_3: reading from backup piece DBTLongterm1629866535460diC__TEST_2379657339_0607dbbp_6_1_1_20210825_1081519481_set6
channel ORA_AUX_SBT_TAPE_4: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_4: restoring datafile 00004 to /u01/app/oracle/dump/TEST_TEST/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_SBT_TAPE_4: reading from backup piece DBTLongterm1629866535460diC__TEST_2379657339_0c07dbcm_12_1_1_20210825_1081519510_set12
channel ORA_AUX_SBT_TAPE_1: piece handle=DBTLongterm1629866535460diC__TEST_2379657339_0707dbbp_7_1_1_20210825_1081519481_set7 tag=DBTLONGTERM1629866535460DIC
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00008 to /u01/app/oracle/dump/TEST_TEST/CA5A52EDFC9A3C13E0531603000A15EA/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00010 to /u01/app/oracle/dump/TEST_TEST/CA5A52EDFC9A3C13E0531603000A15EA/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece DBTLongterm1629866535460diC__TEST_2379657339_0a07dbcl_10_1_1_20210825_1081519509_set10
channel ORA_AUX_SBT_TAPE_2: piece handle=DBTLongterm1629866535460diC__TEST_2379657339_0807dbbp_8_1_1_20210825_1081519481_set8 tag=DBTLONGTERM1629866535460DIC
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:26
channel ORA_AUX_SBT_TAPE_3: piece handle=DBTLongterm1629866535460diC__TEST_2379657339_0607dbbp_6_1_1_20210825_1081519481_set6 tag=DBTLONGTERM1629866535460DIC
channel ORA_AUX_SBT_TAPE_3: restored backup piece 1
channel ORA_AUX_SBT_TAPE_3: restore complete, elapsed time: 00:00:26
channel ORA_AUX_SBT_TAPE_4: piece handle=DBTLongterm1629866535460diC__TEST_2379657339_0c07dbcm_12_1_1_20210825_1081519510_set12 tag=DBTLONGTERM1629866535460DIC
channel ORA_AUX_SBT_TAPE_4: restored backup piece 1
channel ORA_AUX_SBT_TAPE_4: restore complete, elapsed time: 00:00:26
channel ORA_AUX_SBT_TAPE_1: piece handle=DBTLongterm1629866535460diC__TEST_2379657339_0a07dbcl_10_1_1_20210825_1081519509_set10 tag=DBTLONGTERM1629866535460DIC
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:07
Finished restore at 25-AUG-21
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=1081524344 file name=/u01/app/oracle/dump/TEST_TEST/datafile/o1_mf_system_jlcrl7p4_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=11 STAMP=1081524344 file name=/u01/app/oracle/dump/TEST_TEST/CA5A52EDFC9A3C13E0531603000A15EA/datafile/o1_mf_system_jlcrm218_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=1081524345 file name=/u01/app/oracle/dump/TEST_TEST/datafile/o1_mf_undotbs1_jlcrl7pn_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=13 STAMP=1081524345 file name=/u01/app/oracle/dump/TEST_TEST/CA5A52EDFC9A3C13E0531603000A15EA/datafile/o1_mf_undotbs1_jlcrm22b_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=1081524345 file name=/u01/app/oracle/dump/TEST_TEST/datafile/o1_mf_sysaux_jlcrl7p9_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=15 STAMP=1081524345 file name=/u01/app/oracle/dump/TEST_TEST/CA5A52EDFC9A3C13E0531603000A15EA/datafile/o1_mf_sysaux_jlcrl809_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2021/08/25 14:40:00','yyyy/mm/dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone 'TEST_PDB1' "alter database datafile
8 online";
sql clone "alter database datafile 4 online";
sql clone 'TEST_PDB1' "alter database datafile
10 online";
sql clone "alter database datafile 3 online";
sql clone 'TEST_PDB1' "alter database datafile
9 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "TEST_PDB1":"SYSTEM", "UNDOTBS1", "TEST_PDB1":"UNDOTBS1", "SYSAUX", "TEST_PDB1":"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 8 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 10 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 9 online
Starting recover at 25-AUG-21
using channel ORA_AUX_DISK_1
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
using channel ORA_AUX_SBT_TAPE_3
using channel ORA_AUX_SBT_TAPE_4
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file +RECO/TEST_TEST/ARCHIVELOG/2021_08_25/thread_1_seq_2.264.1081519521
archived log for thread 1 with sequence 3 is already on disk as file +RECO/TEST_TEST/ARCHIVELOG/2021_08_25/thread_1_seq_3.265.1081522589
archived log file name=+RECO/TEST_TEST/ARCHIVELOG/2021_08_25/thread_1_seq_2.264.1081519521 thread=1 sequence=2
archived log file name=+RECO/TEST_TEST/ARCHIVELOG/2021_08_25/thread_1_seq_3.265.1081522589 thread=1 sequence=3
media recovery complete, elapsed time: 00:00:01
Finished recover at 25-AUG-21
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database TEST_PDB1 open read only';
}
executing Memory Script
sql statement: alter pluggable database TEST_PDB1 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 =
''/u01/app/oracle/dump/TEST_TEST/controlfile/o1_mf_jlcrkz7d_.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 2147482136 bytes
Fixed Size 9136664 bytes
Variable Size 520093696 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7639040 bytes
sql statement: alter system set control_files = ''/u01/app/oracle/dump/TEST_TEST/controlfile/o1_mf_jlcrkz7d_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2147482136 bytes
Fixed Size 9136664 bytes
Variable Size 520093696 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7639040 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2021/08/25 14:40:00','yyyy/mm/dd hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 14 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 14;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 25-AUG-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=130 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=171 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Oracle Database Backup Service Library VER=21.0.0.1
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: SID=212 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: Oracle Database Backup Service Library VER=21.0.0.1
allocated channel: ORA_AUX_SBT_TAPE_3
channel ORA_AUX_SBT_TAPE_3: SID=252 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_3: Oracle Database Backup Service Library VER=21.0.0.1
allocated channel: ORA_AUX_SBT_TAPE_4
channel ORA_AUX_SBT_TAPE_4: SID=293 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_4: Oracle Database Backup Service Library VER=21.0.0.1
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00014 to /u01/app/oracle/dump/TTTT_TEST/CA5A52EDFC9A3C13E0531603000A15EA/datafile/o1_mf_lee_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece DBTLongterm1629866535460diC__TEST_2379657339_0507dbbp_5_1_1_20210825_1081519481_set5
channel ORA_AUX_SBT_TAPE_1: piece handle=DBTLongterm1629866535460diC__TEST_2379657339_0507dbbp_5_1_1_20210825_1081519481_set5 tag=DBTLONGTERM1629866535460DIC
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:05:57
Finished restore at 25-AUG-21
datafile 14 switched to datafile copy
input datafile copy RECID=17 STAMP=1081524796 file name=/u01/app/oracle/dump/TTTT_TEST/CA5A52EDFC9A3C13E0531603000A15EA/datafile/o1_mf_lee_jlcrp761_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2021/08/25 14:40:00','yyyy/mm/dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone 'TEST_PDB1' "alter database datafile
14 online";
# recover and open resetlogs
recover clone database tablespace "TEST_PDB1":"LEE", "SYSTEM", "TEST_PDB1":"SYSTEM", "UNDOTBS1", "TEST_PDB1":"UNDOTBS1", "SYSAUX", "TEST_PDB1":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 14 online
Starting recover at 25-AUG-21
using channel ORA_AUX_DISK_1
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
using channel ORA_AUX_SBT_TAPE_3
using channel ORA_AUX_SBT_TAPE_4
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file +RECO/TEST_TEST/ARCHIVELOG/2021_08_25/thread_1_seq_2.264.1081519521
archived log for thread 1 with sequence 3 is already on disk as file +RECO/TEST_TEST/ARCHIVELOG/2021_08_25/thread_1_seq_3.265.1081522589
archived log file name=+RECO/TEST_TEST/ARCHIVELOG/2021_08_25/thread_1_seq_2.264.1081519521 thread=1 sequence=2
archived log file name=+RECO/TEST_TEST/ARCHIVELOG/2021_08_25/thread_1_seq_3.265.1081522589 thread=1 sequence=3
media recovery complete, elapsed time: 00:00:01
Finished recover at 25-AUG-21
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database TEST_PDB1 open';
}
executing Memory Script
sql statement: alter pluggable database TEST_PDB1 open
contents of Memory Script:
{
# create directory for datapump import
sql 'TEST_PDB1' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/dump''";
# create directory for datapump export
sql clone 'TEST_PDB1' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/dump''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/dump''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/dump''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_yxfg_vrjm":
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
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> . . exported "LEE"."TEST" 9.661 MB 73163 rows
EXPDP> ORA-39173: Encrypted data has been stored unencrypted in dump file set.
EXPDP> Master table "SYS"."TSPITR_EXP_yxfg_vrjm" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_yxfg_vrjm is:
EXPDP> /u01/app/oracle/dump/tspitr_yxfg_31801.dmp
EXPDP> Job "SYS"."TSPITR_EXP_yxfg_vrjm" successfully completed at Wed Aug 25 15:34:35 2021 elapsed 0 00:00:28
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_yxfg_wuEw" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_yxfg_wuEw":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "LEE"."TEST_BAK" 9.661 MB 73163 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_yxfg_wuEw" successfully completed at Wed Aug 25 15:35:07 2021 elapsed 0 00:00:24
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/dump/TEST_TEST/CA5A52EDFC9A3C13E0531603000A15EA/datafile/o1_mf_temp_jlcrms8k_.tmp deleted
auxiliary instance file /u01/app/oracle/dump/TEST_TEST/datafile/o1_mf_temp_jlcrmpco_.tmp deleted
auxiliary instance file /u01/app/oracle/dump/TTTT_TEST/onlinelog/o1_mf_3_jlcs1joj_.log deleted
auxiliary instance file /u01/app/oracle/dump/TTTT_TEST/onlinelog/o1_mf_2_jlcs1jmo_.log deleted
auxiliary instance file /u01/app/oracle/dump/TTTT_TEST/onlinelog/o1_mf_1_jlcs1jl6_.log deleted
auxiliary instance file /u01/app/oracle/dump/TTTT_TEST/CA5A52EDFC9A3C13E0531603000A15EA/datafile/o1_mf_lee_jlcrp761_.dbf deleted
auxiliary instance file /u01/app/oracle/dump/TEST_TEST/CA5A52EDFC9A3C13E0531603000A15EA/datafile/o1_mf_sysaux_jlcrl809_.dbf deleted
auxiliary instance file /u01/app/oracle/dump/TEST_TEST/datafile/o1_mf_sysaux_jlcrl7p9_.dbf deleted
auxiliary instance file /u01/app/oracle/dump/TEST_TEST/CA5A52EDFC9A3C13E0531603000A15EA/datafile/o1_mf_undotbs1_jlcrm22b_.dbf deleted
auxiliary instance file /u01/app/oracle/dump/TEST_TEST/datafile/o1_mf_undotbs1_jlcrl7pn_.dbf deleted
auxiliary instance file /u01/app/oracle/dump/TEST_TEST/CA5A52EDFC9A3C13E0531603000A15EA/datafile/o1_mf_system_jlcrm218_.dbf deleted
auxiliary instance file /u01/app/oracle/dump/TEST_TEST/datafile/o1_mf_system_jlcrl7p4_.dbf deleted
auxiliary instance file /u01/app/oracle/dump/TEST_TEST/controlfile/o1_mf_jlcrkz7d_.ctl deleted
auxiliary instance file tspitr_yxfg_31801.dmp deleted
Finished recover at 25-AUG-21
#TABLE 확인
- TEST_BAK에 기존 DATA가 적재 되었는지 확인하였다.. 기존 Data가 적재되었다!!
SQL> alter session set container=test_pdb1;
Session altered.
SQL> select count(*) from lee.test_bak;
COUNT(*)
----------
73163
| 참조 |
https://blog.goodusdata.com/14
[DB기술노트79회] Recovering Tables (with Rman)
1. 기술노트 개요 00 사이트에서 개발자의 실수로 인한 데이터가 조건절을 부여 실수로 잘못 업데이트가 되는 상황이 발생했습니다. 하지만 하루가 지나서야 데이터가 잘못 변경된 사실을 알았
blog.goodusdata.com
Backup and Recovery User's Guide
docs.oracle.com
반응형
'Oracle Database' 카테고리의 다른 글
| [ Oracle ] 필수 Background Process (1) | 2021.09.11 |
|---|---|
| [ORACLE] Full Table Scan 이해하기 (0) | 2021.09.10 |
| [ Oracle ] Select절 Function 튜닝 (0) | 2021.09.09 |
| [ORACLE] KILL SESSION (0) | 2021.08.27 |
| [Oracle] Oracle Cloud ATP 생성 & 접속 방법 (0) | 2021.08.24 |
Contents
소중한 공감 감사합니다