Version 변경일자 변경자(작성자) 주요내용 1
Author 김도현 Creation Date
Last Updated Version 1.0
Copyright(C) 2004 Goodus Inc.
All Rights Reserved
RMAN
12c New Feature
- 2 – Contents
1. SYSBACKUP Privilege ... 3
1.1. SYSBACKUP TEST ... 3
2. SQL Interface Improvements ... 4
3. Multisection Backup Improvements... 5
3.1. Multisection 증분 백업 ... 5
3.2. Multisection image copy ... 7
4. Table-Level Recovery From Backups ... 8
4.1. Table recvoery 전제조건 & 제약조건 ... 8
4.2. 테이블 & 파티션 복원 옵션 ... 8
4.3. Table Recovery ... 8
5. Cross-Platform Backup and Restore ... 29
5.1. Cross-Platform 확인사항 ... 29
5.2. Cross-platform same endian ... 29
6. Active Database Duplication ... 35
7. NOOPEN option ... 35
8. 참 고 문 헌 ... 35
- 3 –
1. SYSBACKUP Privilege
- Backup and Recovery administration 을 위한 권한으로 SYSBACKUP - 특정한 management task 를 도입하여 데이터베이스 관리 업무를 분리
- Table Data 에 엑세스를(AS SYSDBA) 하지 않고, backup and recovery 수행 가능
- Backup and recovery 에 대한 권한을 포함하며, select any table 과 같은 데이터 접근 권한 미포함 - 데이터베이스 설치 단계에서 자동으로 생성되며, 사용 전에 반드시 unlock & grant 수행필요 - OS authorization 을 이용중이면, orapwd 파일 생성 시에 SYSBACKUP=Y 를 반드시 설정 필요 - Connect string 에 SYSDBA 나 SYSBACKUP 이 기술되어 있지 않으면 SYSDBA 로 접속
1.1. SYSBACKUP TEST
- SYSBACKUP 권한
SQL> select privilege from dba_sys_privs where grantee='SYSBACKUP';
PRIVILEGE
--- SELECT ANY TRANSACTION UNLIMITED TABLESPACE RESUMABLE
CREATE ANY CLUSTER DROP TABLESPACE ALTER SESSION AUDIT ANY CREATE ANY TABLE CREATE ANY DIRECTORY ALTER SYSTEM
ALTER DATABASE SELECT ANY DICTIONARY ALTER TABLESPACE
- SYSBACKUP DB 재기동
[oracle12@dotang trace]$ sqlplus / as sysbackup;
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 4 18:32:25 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> show user
- 4 – USER is "SYSBACKUP"
SQL> shutdown immediate Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
SQL> show user USER is "SYSBACKUP"
SQL> shutdown abort ORACLE instance shut down.
SQL> startup
ORACLE instance started.
2. SQL Interface Improvements
- RMAN 에서 SQL 명령에 prefix 없이 SQL 과 PL/SQL precedure 수행가능 - select , desc 명령어 포함
- 특정 schema 의 table 접근하기 위해서 해당 오브젝트에 대한 select privilege 을 별도로 제공.
- sysbackup 권한은 user table 또는 view 들에 grant 되지 않으며, manual 하게 권한 필수.
[oracle12@dotang ~]$ rman target sysbackup/oracle
Recovery Manager: Release 12.2.0.1.0 - Production on Sun Jul 30 18:46:57 2017 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: DOTANG12 (DBID=3252395462) RMAN> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
using target database control file instead of recovery catalog Statement processed
RMAN> select sysdate from dual;
SYSDATE --- 30-jul-2017 18:47:29
- 5 – RMAN> desc v$logfile
Name Null? Type
--- --- ---
GROUP# NUMBER STATUS VARCHAR2(7) TYPE VARCHAR2(7) MEMBER VARCHAR2(513) IS_RECOVERY_DEST_FILE VARCHAR2(3) CON_ID NUMBER
3. Multisection Backup Improvements
- RMAN backup 에서의 최소 단위는 datafile
- Datafile size 점점 증가함에 따라 multisection backup 기능이 11g 소개
- Large 파일을 쪼개서 백업/복구를 지원하고, 각 channel 은 backupset 내에 분리된 파일 생성 - 11g 까지는 backupset 에서만 가능했지만, 12c Multisection backup 은 모든 백업 형태에서 가능
- TTS 의 일부 과정으로 파일 복사 또는 Active database duplication 을 이용하여 clone db 생성과 같은 경우 작 업 시간을 단축
- 11g RMAN 에서의 multisction backup 은 전체 백업(Level 0)을 지원했지만, 12c 부터는 RMAN 증분(Level 1) 백 업을 수행할 때도 사용 가능
- Multisection backup 을 할 때 RMAN 이 가능한 경우 BCT(Block Change Tracking) 을 사용하거나 사용되지 않은 블록의 압축을 시도
- Level 0 인 Multisection 증분백업의 경우 compatible parameter 는 11.0 이거나 그 이상으로 설정하면 되지만, Level 1 multisection 증분백업의 경우 Compatible parameter 가 12.0 이거나 그 이상으로 설정 (오직 datafile) - Section size 가 datafile 크기보다 크면, 해당파일은 multisection backup 을 수행하지 않음
- Section size 가 256 개 초과하도록 size 가 주어지면 RMAN 은 256 개 생성이 되도록 section size 증가
3.1. Multisection 증분 백업
[oracle12@dotang trace]$ rman target '"/ as sysbackup"'
Recovery Manager: Release 12.2.0.1.0 - Production on Tue Jul 4 19:36:51 2017 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: DOTANG12 (DBID=3252395462)
- 6 – RMAN> backup incremental level 1 section size 300m database;
Starting backup at 04-JUL-17
using target database control file instead of recovery catalog allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK no parent backup or copy of datafile 4 found no parent backup or copy of datafile 7 found no parent backup or copy of datafile 1 found no parent backup or copy of datafile 3 found
channel ORA_DISK_1: starting incremental level 1 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle12/oradata/DOTANG12/system01.dbf backing up blocks 1 through 38400
channel ORA_DISK_1: starting piece 1 at 04-JUL-17 channel ORA_DISK_1: finished piece 1 at 04-JUL-17
piece handle=/oracle12/product/12.2/dbs/03s8hde4_1_1 tag=TAG20170704T193844 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental level 1 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle12/oradata/DOTANG12/system01.dbf backing up blocks 38401 through 76800
channel ORA_DISK_1: starting piece 2 at 04-JUL-17 channel ORA_DISK_1: finished piece 2 at 04-JUL-17
piece handle=/oracle12/product/12.2/dbs/03s8hde4_2_1 tag=TAG20170704T193844 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental level 1 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle12/oradata/DOTANG12/system01.dbf backing up blocks 76801 through 104960
channel ORA_DISK_1: starting piece 3 at 04-JUL-17 channel ORA_DISK_1: finished piece 3 at 04-JUL-17 중 간 생 략
input datafile file number=00004 name=/oracle12/oradata/DOTANG12/undotbs01.dbf input datafile file number=00007 name=/oracle12/oradata/DOTANG12/users01.dbf channel ORA_DISK_1: starting piece 1 at 04-JUL-17
channel ORA_DISK_1: finished piece 1 at 04-JUL-17
- 7 –
piece handle=/oracle12/product/12.2/dbs/08s8hdf8_1_1 tag=TAG20170704T193844 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-JUL-17
Starting Control File and SPFILE Autobackup at 04-JUL-17
piece handle=/oracle12/product/12.2/dbs/c-3252395462-20170704-00 comment=NONE Finished Control File and SPFILE Autobackup at 04-JUL-17
3.2. Multisection image copy
RMAN> backup as copy section size 300m database;
Starting backup at 04-JUL-17 using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oracle12/oradata/DOTANG12/system01.dbf backing up blocks 1 through 38400
output file name=/oracle12/product/12.2/dbs/data_D-DOTANG12_I-3252395462_TS-SYSTEM_FNO-1_0fs8hetg tag=TAG20170704T200359
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oracle12/oradata/DOTANG12/system01.dbf backing up blocks 38401 through 76800
output file name=/oracle12/product/12.2/dbs/data_D-DOTANG12_I-3252395462_TS-SYSTEM_FNO-1_0fs8hetg tag=TAG20170704T200359
중 간 생 략
input datafile file number=00007 name=/oracle12/oradata/DOTANG12/users01.dbf
output file name=/oracle12/product/12.2/dbs/data_D-DOTANG12_I-3252395462_TS-USERS_FNO-7_0ls8heu0 tag=TAG20170704T200359 RECID=8 STAMP=948485056
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 04-JUL-17
Starting Control File and SPFILE Autobackup at 04-JUL-17
piece handle=/oracle12/product/12.2/dbs/c-3252395462-20170704-02 comment=NONE
Finished Control File and SPFILE Autobackup at 04-JUL-17
- 8 –
4. Table-Level Recovery From Backups
- 기존 11g 까지는 Tablespace Point-in-Time Recovery(PSPITR) 작업을 사용자가 직접 수행하여 특정시점에 대해 서 복구를 수행했으나, 12c 에서는 RMAN 이 모든 작업 과정을 자동으로 진행할 수 있으며, Table Level 까지 복 구가 가능하며, RMAN 을 통하여 사용중인 데이터베이스에 영향을 주지 않고 지정한 시간이나 시점으로 Table 혹은 Table partition 복구가 가능
4.1. Table recovery 전제조건 & 제약조건
l Target 데이터베이스의 전제조건
- Read/Write 모드여야 함 - Archivelog 모드여야 함
- Compatible 파라미터가 11.1.0 이상으로 설정되어 있어야함.
- 최소 한번의 Full Backup 과 Backup 후 모든 아카이브 로그가 필요.
l 복구 대상 Table 이나 Table Partition 의 제약조건
- SYS 스키마 소유가 아니어야 함.
- SYSTEM 이나 SYSAUX Tablespace 에 저장되어 있지 않아야 함 - Standby Database 가 아니여야함.
4.2. 테이블 & 파티션 복원 옵션
Parameter 명 내 용
auxiliary destination recover 명령으로 auxiliary instance 관련 생성에 사용되는 위치설정 지정하지 않으면 $ORACLE_HOME/dbs 에 생성
dump file dump 파일명
datapump destination datapump dmp 파일이 생성되는 위치
notableimport dmp 파일은 생성이 되지만 해당 데이터는 import 되지 않음.
remap table Target 쪽에 Table 명 변경
remap tablespace Target 쪽에 Tablespace 명 변경
4.3. Table Recovery
- SCN 으로 Table 복구
SQL> alter session set nls_date_format='mm/dd/yyyy hh24:mi:ss';
Session altered.
SQL> select sysdate, current_scn from v$database;
SYSDATE CURRENT_SCN --- ---
07/13/2017 20:28:13 2021140
- 9 – SQL> show user
USER is "GOODUS"
SQL> create table test (name varchar2(10));
Table created.
SQL> insert into test values ('kim');
1 row created.
SQL> insert into test values ('do');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
NAME --- kim do
SQL> select sysdate, current_scn from v$database;
SYSDATE CURRENT_SCN --- ---
07/13/2017 20:29:54 2021211
SQL> insert into test values ('hyun');
1 row created.
SQL> insert into test values ('goodus');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
NAME --- kim
- 10 – do
hyun goodus
SQL> drop table test purge;
Table dropped.
[oracle12@dotang ~]$ rman target sysbackup/oracle
Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jul 13 20:34:41 2017 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: DOTANG12 (DBID=3252395462)
RMAN> recover table goodus."TEST" until scn 2021211 auxiliary destination '/tmp/rman/aux';
Starting recover at 13-JUL-17 current log archived
using channel ORA_DISK_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 UNDOTBS1
Creating automatic instance, with SID='gepr'
initialization parameters used for automatic instance:
db_name=DOTANG12
db_unique_name=gepr_pitr_DOTANG12 compatible=12.2.0
db_block_size=8192 db_files=200
diagnostic_dest=/oracle12 _system_trig_enabled=FALSE sga_target=2048M
processes=200
db_create_file_dest=/tmp/rman/aux
log_archive_dest_1='location=/tmp/rman/aux'
#No auxiliary parameter file used
- 11 – starting up automatic instance DOTANG12
Oracle instance started
Total System Global Area 2147483648 bytes
Fixed Size 8622776 bytes Variable Size 503319880 bytes Database Buffers 1627389952 bytes Redo Buffers 8151040 bytes Automatic instance created
contents of Memory Script:
{
# set requested point in time set until scn 2021211;
# 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 13-JUL-17 allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=34 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 /oracle12/product/12.2/dbs/c-3252395462-20170713-05 channel ORA_AUX_DISK_1: piece handle=/oracle12/product/12.2/dbs/c-3252395462-20170713-05
tag=TAG20170713T190015
channel ORA_AUX_DISK_1: restored backup piece 1
- 12 –
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/tmp/rman/aux/DOTANG12/controlfile/o1_mf_dpgpj25s_.ctl Finished restore at 13-JUL-17
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 scn 2021211;
# 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 /tmp/rman/aux/DOTANG12/datafile/o1_mf_temp_%u_.tmp in control file Starting restore at 13-JUL-17
- 13 – 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 /tmp/rman/aux/DOTANG12/datafile/o1_mf_system_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/rman/aux/DOTANG12/datafile/o1_mf_undotbs1_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/rman/aux/DOTANG12/datafile/o1_mf_sysaux_%u_.dbf channel ORA_AUX_DISK_1: reading from backup piece /oracle12/product/12.2/dbs/15s992hg_1_1
channel ORA_AUX_DISK_1: piece handle=/oracle12/product/12.2/dbs/15s992hg_1_1 tag=TAG20170713T190000 channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15 Finished restore at 13-JUL-17
datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=949264295 file
name=/tmp/rman/aux/DOTANG12/datafile/o1_mf_system_dpgpj84g_.dbf datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=949264295 file
name=/tmp/rman/aux/DOTANG12/datafile/o1_mf_undotbs1_dpgpj84r_.dbf datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=949264295 file
name=/tmp/rman/aux/DOTANG12/datafile/o1_mf_sysaux_dpgpj84m_.dbf
contents of Memory Script:
{
# set requested point in time set until scn 2021211;
# 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
- 14 – sql statement: alter database datafile 1 online
sql statement: alter database datafile 4 online sql statement: alter database datafile 3 online
Starting recover at 13-JUL-17 using channel ORA_AUX_DISK_1 starting media recovery
archived log for thread 1 with sequence 43 is already on disk as file /oracle12/arch/1_43_945614022.dbf archived log for thread 1 with sequence 44 is already on disk as file /oracle12/arch/1_44_945614022.dbf archived log for thread 1 with sequence 45 is already on disk as file /oracle12/arch/1_45_945614022.dbf archived log for thread 1 with sequence 46 is already on disk as file /oracle12/arch/1_46_945614022.dbf archived log for thread 1 with sequence 47 is already on disk as file /oracle12/arch/1_47_945614022.dbf archived log for thread 1 with sequence 48 is already on disk as file
/oracle12/product/12.2/dbs/arch1_48_945614022.dbf
archived log for thread 1 with sequence 49 is already on disk as file /oracle12/product/12.2/dbs/arch1_49_945614022.dbf
archived log for thread 1 with sequence 50 is already on disk as file /oracle12/product/12.2/dbs/arch1_50_945614022.dbf
archived log file name=/oracle12/arch/1_43_945614022.dbf thread=1 sequence=43 archived log file name=/oracle12/arch/1_44_945614022.dbf thread=1 sequence=44 archived log file name=/oracle12/arch/1_45_945614022.dbf thread=1 sequence=45 archived log file name=/oracle12/arch/1_46_945614022.dbf thread=1 sequence=46 archived log file name=/oracle12/arch/1_47_945614022.dbf thread=1 sequence=47
archived log file name=/oracle12/product/12.2/dbs/arch1_48_945614022.dbf thread=1 sequence=48 archived log file name=/oracle12/product/12.2/dbs/arch1_49_945614022.dbf thread=1 sequence=49 archived log file name=/oracle12/product/12.2/dbs/arch1_50_945614022.dbf thread=1 sequence=50 media recovery complete, elapsed time: 00:00:03
Finished recover at 13-JUL-17
sql statement: alter database open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
- 15 – startup clone nomount;
sql clone "alter system set control_files =
''/tmp/rman/aux/DOTANG12/controlfile/o1_mf_dpgpj25s_.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 2147483648 bytes
Fixed Size 8622776 bytes Variable Size 503319880 bytes Database Buffers 1627389952 bytes Redo Buffers 8151040 bytes
sql statement: alter system set control_files = ''/tmp/rman/aux/DOTANG12/controlfile/o1_mf_dpgpj25s_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started) Oracle instance started
Total System Global Area 2147483648 bytes Fixed Size 8622776 bytes Variable Size 503319880 bytes
- 16 – Database Buffers 1627389952 bytes
Redo Buffers 8151040 bytes sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time set until scn 2021211;
# set destinations for recovery set and auxiliary set datafiles set newname for datafile 7 to new;
# restore the tablespaces in the recovery set and the auxiliary set restore clone datafile 7;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause executing command: SET NEWNAME
Starting restore at 13-JUL-17 allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=34 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 00007 to
/tmp/rman/aux/GEPR_PITR_DOTANG12/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oracle12/product/12.2/dbs/15s992hg_1_1
channel ORA_AUX_DISK_1: piece handle=/oracle12/product/12.2/dbs/15s992hg_1_1 tag=TAG20170713T190000 channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 13-JUL-17
datafile 7 switched to datafile copy
input datafile copy RECID=16 STAMP=949264355 file
name=/tmp/rman/aux/GEPR_PITR_DOTANG12/datafile/o1_mf_users_dpgpllkz_.dbf
- 17 – contents of Memory Script:
{
# set requested point in time set until scn 2021211;
# online the datafiles restored or switched sql clone "alter database datafile 7 online";
# recover and open resetlogs
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 7 online
Starting recover at 13-JUL-17 using channel ORA_AUX_DISK_1 starting media recovery
archived log for thread 1 with sequence 43 is already on disk as file /oracle12/arch/1_43_945614022.dbf archived log for thread 1 with sequence 44 is already on disk as file /oracle12/arch/1_44_945614022.dbf archived log for thread 1 with sequence 45 is already on disk as file /oracle12/arch/1_45_945614022.dbf archived log for thread 1 with sequence 46 is already on disk as file /oracle12/arch/1_46_945614022.dbf archived log for thread 1 with sequence 47 is already on disk as file /oracle12/arch/1_47_945614022.dbf archived log for thread 1 with sequence 48 is already on disk as file
/oracle12/product/12.2/dbs/arch1_48_945614022.dbf
archived log for thread 1 with sequence 49 is already on disk as file /oracle12/product/12.2/dbs/arch1_49_945614022.dbf
archived log for thread 1 with sequence 50 is already on disk as file /oracle12/product/12.2/dbs/arch1_50_945614022.dbf
archived log file name=/oracle12/arch/1_43_945614022.dbf thread=1 sequence=43 archived log file name=/oracle12/arch/1_44_945614022.dbf thread=1 sequence=44 archived log file name=/oracle12/arch/1_45_945614022.dbf thread=1 sequence=45 archived log file name=/oracle12/arch/1_46_945614022.dbf thread=1 sequence=46 archived log file name=/oracle12/arch/1_47_945614022.dbf thread=1 sequence=47
archived log file name=/oracle12/product/12.2/dbs/arch1_48_945614022.dbf thread=1 sequence=48
- 18 –
archived log file name=/oracle12/product/12.2/dbs/arch1_49_945614022.dbf thread=1 sequence=49 archived log file name=/oracle12/product/12.2/dbs/arch1_50_945614022.dbf thread=1 sequence=50 media recovery complete, elapsed time: 00:00:00
Finished recover at 13-JUL-17 database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as '' /tmp/rman/aux''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as '' /tmp/rman/aux''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/rman/aux'' sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/rman/aux''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_gepr_wtig":
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/TABLE
EXPDP> . . exported "GOODUS"."TEST" 5.070 KB 2 rows EXPDP> Master table "SYS"."TSPITR_EXP_gepr_wtig" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_gepr_wtig is:
EXPDP> /tmp/rman/aux/tspitr_gepr_36143.dmp
EXPDP> Job "SYS"."TSPITR_EXP_gepr_wtig" successfully completed at Thu Jul 13 20:33:55 2017 elapsed 0 00:00:56 Export completed
contents of Memory Script:
{
# shutdown clone before import shutdown clone abort
- 19 – }
executing Memory Script Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_gepr_eodd" successfully loaded/unloaded IMPDP> Starting "SYS"."TSPITR_IMP_gepr_eodd":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "GOODUS"."TEST" 5.070 KB 2 rows IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Job "SYS"."TSPITR_IMP_gepr_eodd" successfully completed at Thu Jul 13 20:34:06 2017 elapsed 0 00:00:05
Import completed
Removing automatic instance Automatic instance removed
auxiliary instance file /tmp/rman/aux/DOTANG12/datafile/o1_mf_temp_dpgpjwy7_.tmp deleted
auxiliary instance file /tmp/rman/aux/GEPR_PITR_DOTANG12/onlinelog/o1_mf_3_dpgpls95_.log deleted auxiliary instance file /tmp/rman/aux/GEPR_PITR_DOTANG12/onlinelog/o1_mf_2_dpgplpy9_.log deleted auxiliary instance file /tmp/rman/aux/GEPR_PITR_DOTANG12/onlinelog/o1_mf_1_dpgplpsy_.log deleted auxiliary instance file /tmp/rman/aux/GEPR_PITR_DOTANG12/datafile/o1_mf_users_dpgpllkz_.dbf deleted auxiliary instance file /tmp/rman/aux/DOTANG12/datafile/o1_mf_sysaux_dpgpj84m_.dbf deleted
auxiliary instance file /tmp/rman/aux/DOTANG12/datafile/o1_mf_undotbs1_dpgpj84r_.dbf deleted auxiliary instance file /tmp/rman/aux/DOTANG12/datafile/o1_mf_system_dpgpj84g_.dbf deleted auxiliary instance file /tmp/rman/aux/DOTANG12/controlfile/o1_mf_dpgpj25s_.ctl deleted auxiliary instance file tspitr_gepr_36143.dmp deleted
Finished recover at 13-JUL-17
SQL> select * from test;
-> scn 2021211 이전 Data 만 복구
NAME --- kim do
- Import 하지 않고 dmp 만 생성
- 20 –
RMAN> recover table goodus."TEST" until time "to_date('07/13/2017 20:29:54','mm/dd/yyyy hh24:mi:ss')" auxiliary destination '/tmp/rman/aux' dump file test.dmp datapump destination '/oracle12/' notableimport;
Starting recover at 13-JUL-17 using channel ORA_DISK_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 UNDOTBS1
Creating automatic instance, with SID='ntac'
initialization parameters used for automatic instance:
db_name=DOTANG12
db_unique_name=ntac_pitr_DOTANG12 compatible=12.2.0
db_block_size=8192 db_files=200
diagnostic_dest=/oracle12 _system_trig_enabled=FALSE sga_target=2048M
processes=200
db_create_file_dest=/tmp/rman/aux
log_archive_dest_1='location=/tmp/rman/aux'
#No auxiliary parameter file used
starting up automatic instance DOTANG12 Oracle instance started
Total System Global Area 2147483648 bytes
Fixed Size 8622776 bytes Variable Size 503319880 bytes Database Buffers 1627389952 bytes Redo Buffers 8151040 bytes Automatic instance created
- 21 – contents of Memory Script:
{
# set requested point in time
set until time "to_date('07/13/2017 20:29:54','mm/dd/yyyy 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 13-JUL-17 allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=36 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 /oracle12/product/12.2/dbs/c-3252395462-20170713-05 channel ORA_AUX_DISK_1: piece handle=/oracle12/product/12.2/dbs/c-3252395462-20170713-05
tag=TAG20170713T190015
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/tmp/rman/aux/DOTANG12/controlfile/o1_mf_dpgwc4w7_.ctl Finished restore at 13-JUL-17
sql statement: alter database mount clone database sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
- 22 –
set until time "to_date('07/13/2017 20:29:54','mm/dd/yyyy 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 /tmp/rman/aux/DOTANG12/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 13-JUL-17 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 /tmp/rman/aux/DOTANG12/datafile/o1_mf_system_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/rman/aux/DOTANG12/datafile/o1_mf_undotbs1_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/rman/aux/DOTANG12/datafile/o1_mf_sysaux_%u_.dbf channel ORA_AUX_DISK_1: reading from backup piece /oracle12/product/12.2/dbs/15s992hg_1_1
channel ORA_AUX_DISK_1: piece handle=/oracle12/product/12.2/dbs/15s992hg_1_1 tag=TAG20170713T190000 channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
- 23 – Finished restore at 13-JUL-17
datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=949270283 file
name=/tmp/rman/aux/DOTANG12/datafile/o1_mf_system_dpgwcd6t_.dbf datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=949270283 file
name=/tmp/rman/aux/DOTANG12/datafile/o1_mf_undotbs1_dpgwcd7x_.dbf datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=949270283 file
name=/tmp/rman/aux/DOTANG12/datafile/o1_mf_sysaux_dpgwcd7c_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('07/13/2017 20:29:54','mm/dd/yyyy 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 13-JUL-17 using channel ORA_AUX_DISK_1 starting media recovery
- 24 –
archived log for thread 1 with sequence 43 is already on disk as file /oracle12/arch/1_43_945614022.dbf archived log for thread 1 with sequence 44 is already on disk as file /oracle12/arch/1_44_945614022.dbf archived log for thread 1 with sequence 45 is already on disk as file /oracle12/arch/1_45_945614022.dbf archived log for thread 1 with sequence 46 is already on disk as file /oracle12/arch/1_46_945614022.dbf archived log for thread 1 with sequence 47 is already on disk as file /oracle12/arch/1_47_945614022.dbf archived log for thread 1 with sequence 48 is already on disk as file
/oracle12/product/12.2/dbs/arch1_48_945614022.dbf
archived log for thread 1 with sequence 49 is already on disk as file /oracle12/product/12.2/dbs/arch1_49_945614022.dbf
archived log for thread 1 with sequence 50 is already on disk as file /oracle12/product/12.2/dbs/arch1_50_945614022.dbf
archived log file name=/oracle12/arch/1_43_945614022.dbf thread=1 sequence=43 archived log file name=/oracle12/arch/1_44_945614022.dbf thread=1 sequence=44 archived log file name=/oracle12/arch/1_45_945614022.dbf thread=1 sequence=45 archived log file name=/oracle12/arch/1_46_945614022.dbf thread=1 sequence=46 archived log file name=/oracle12/arch/1_47_945614022.dbf thread=1 sequence=47
archived log file name=/oracle12/product/12.2/dbs/arch1_48_945614022.dbf thread=1 sequence=48 archived log file name=/oracle12/product/12.2/dbs/arch1_49_945614022.dbf thread=1 sequence=49 archived log file name=/oracle12/product/12.2/dbs/arch1_50_945614022.dbf thread=1 sequence=50 media recovery complete, elapsed time: 00:00:02
Finished recover at 13-JUL-17
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 =
''/tmp/rman/aux/DOTANG12/controlfile/o1_mf_dpgwc4w7_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
- 25 – 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 2147483648 bytes
Fixed Size 8622776 bytes Variable Size 503319880 bytes Database Buffers 1627389952 bytes Redo Buffers 8151040 bytes
sql statement: alter system set control_files = ''/tmp/rman/aux/DOTANG12/controlfile/o1_mf_dpgwc4w7_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started) Oracle instance started
Total System Global Area 2147483648 bytes
Fixed Size 8622776 bytes Variable Size 503319880 bytes Database Buffers 1627389952 bytes Redo Buffers 8151040 bytes sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('07/13/2017 20:29:54','mm/dd/yyyy hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles set newname for datafile 7 to new;
- 26 –
# restore the tablespaces in the recovery set and the auxiliary set restore clone datafile 7;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause executing command: SET NEWNAME
Starting restore at 13-JUL-17 allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=34 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 00007 to
/tmp/rman/aux/NTAC_PITR_DOTANG12/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oracle12/product/12.2/dbs/15s992hg_1_1
channel ORA_AUX_DISK_1: piece handle=/oracle12/product/12.2/dbs/15s992hg_1_1 tag=TAG20170713T190000 channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 13-JUL-17
datafile 7 switched to datafile copy
input datafile copy RECID=16 STAMP=949270342 file
name=/tmp/rman/aux/NTAC_PITR_DOTANG12/datafile/o1_mf_users_dpgwfos6_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('07/13/2017 20:29:54','mm/dd/yyyy hh24:mi:ss')";
# online the datafiles restored or switched sql clone "alter database datafile 7 online";
# recover and open resetlogs
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
- 27 – executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 7 online
Starting recover at 13-JUL-17 using channel ORA_AUX_DISK_1 starting media recovery
archived log for thread 1 with sequence 43 is already on disk as file /oracle12/arch/1_43_945614022.dbf archived log for thread 1 with sequence 44 is already on disk as file /oracle12/arch/1_44_945614022.dbf archived log for thread 1 with sequence 45 is already on disk as file /oracle12/arch/1_45_945614022.dbf archived log for thread 1 with sequence 46 is already on disk as file /oracle12/arch/1_46_945614022.dbf archived log for thread 1 with sequence 47 is already on disk as file /oracle12/arch/1_47_945614022.dbf archived log for thread 1 with sequence 48 is already on disk as file
/oracle12/product/12.2/dbs/arch1_48_945614022.dbf
archived log for thread 1 with sequence 49 is already on disk as file /oracle12/product/12.2/dbs/arch1_49_945614022.dbf
archived log for thread 1 with sequence 50 is already on disk as file /oracle12/product/12.2/dbs/arch1_50_945614022.dbf
archived log file name=/oracle12/arch/1_43_945614022.dbf thread=1 sequence=43 archived log file name=/oracle12/arch/1_44_945614022.dbf thread=1 sequence=44 archived log file name=/oracle12/arch/1_45_945614022.dbf thread=1 sequence=45 archived log file name=/oracle12/arch/1_46_945614022.dbf thread=1 sequence=46 archived log file name=/oracle12/arch/1_47_945614022.dbf thread=1 sequence=47
archived log file name=/oracle12/product/12.2/dbs/arch1_48_945614022.dbf thread=1 sequence=48 archived log file name=/oracle12/product/12.2/dbs/arch1_49_945614022.dbf thread=1 sequence=49 archived log file name=/oracle12/product/12.2/dbs/arch1_50_945614022.dbf thread=1 sequence=50 media recovery complete, elapsed time: 00:00:00
Finished recover at 13-JUL-17 database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
- 28 – /oracle12/''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as '' /oracle12/''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/oracle12/'' sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/oracle12/''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_ntac_Dkeq":
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/TABLE
EXPDP> . . exported "GOODUS"."TEST" 5.070 KB 2 rows EXPDP> Master table "SYS"."TSPITR_EXP_ntac_Dkeq" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_ntac_Dkeq is:
EXPDP> /oracle12/test.dmp
EXPDP> Job "SYS"."TSPITR_EXP_ntac_Dkeq" successfully completed at Thu Jul 13 22:13:58 2017 elapsed 0 00:01:15
Export completed
Not performing table import after point-in-time recovery
Removing automatic instance shutting down automatic instance Oracle instance shut down
Automatic instance removed
auxiliary instance file /tmp/rman/aux/DOTANG12/datafile/o1_mf_temp_dpgwd1vh_.tmp deleted
auxiliary instance file /tmp/rman/aux/NTAC_PITR_DOTANG12/onlinelog/o1_mf_3_dpgwfvbo_.log deleted auxiliary instance file /tmp/rman/aux/NTAC_PITR_DOTANG12/onlinelog/o1_mf_2_dpgwfs0b_.log deleted auxiliary instance file /tmp/rman/aux/NTAC_PITR_DOTANG12/onlinelog/o1_mf_1_dpgwfrtm_.log deleted auxiliary instance file /tmp/rman/aux/NTAC_PITR_DOTANG12/datafile/o1_mf_users_dpgwfos6_.dbf deleted auxiliary instance file /tmp/rman/aux/DOTANG12/datafile/o1_mf_sysaux_dpgwcd7c_.dbf deleted
auxiliary instance file /tmp/rman/aux/DOTANG12/datafile/o1_mf_undotbs1_dpgwcd7x_.dbf deleted auxiliary instance file /tmp/rman/aux/DOTANG12/datafile/o1_mf_system_dpgwcd6t_.dbf deleted
- 29 –
auxiliary instance file /tmp/rman/aux/DOTANG12/controlfile/o1_mf_dpgwc4w7_.ctl deleted Finished recover at 13-JUL-17
SQL> select * from test;
select * from test * ERROR at line 1:
ORA-00942: table or view does not exist
[oracle12@dotang oracle12]$ ls -al test.dmp
-rw-r---. 1 oracle12 dba 167936 Jul 13 22:13 test.dmp
5. Cross-Platform Backup and Restore
- 각 platform 들간 tablespace, datafile, database 의 전송 가능 - 다른 endian format 을 갖는 platform 간의 tablespace transport 포함
- 과거에는 모든 cross-platform data transport 는 image copy 만 가능하였으나 12c 부터는 backupset 이용가능 - backupset 을 이용하게 되서 compression 과 multisection 기능을 사용 할 수 있게 되서, 시간 단축
5.1. Cross-Platform 확인사항
- source 와 target 데이터베이스의 Compatible 파라미터 값은 반드시 12.0. 같거나 커야 함.
- source 데이터베이는 read-only 모드로 오픈되어야 함.
- dbms_tdb.check_db 결과가 source 데이터베이스가 target 데이터베이스 서버로 이동이 가능하다는 결과값이 나와 야 함,
- TO PLATFORM : source database conversion 을 수행
- FOR TRANSPORT : source database 로부터 오는 데이터를 target database 에 conversion 수행 - datapump : export dump file 을 백업을 수행하는 동안 만들 수 있음. (backupset 파일 별도)
5.2. Cross-platform same endian
SQL> select * from v$transportable_platform
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT CON_ID --- --- --- ---
1 Solaris[tm] OE (32-bit) Big 0
2 Solaris[tm] OE (64-bit) Big 0
7 Microsoft Windows IA (32-bit) Little 0
10 Linux IA (32-bit) Little 0
6 AIX-Based Systems (64-bit) Big 0
- 30 –
3 HP-UX (64-bit) Big 0
5 HP Tru64 UNIX Little 0
4 HP-UX IA (64-bit) Big 0
11 Linux IA (64-bit) Little 0
15 HP Open VMS Little 0
8 Microsoft Windows IA (64-bit) Little 0
9 IBM zSeries Based Linux Big 0
13 Linux x86 64-bit Little 0
16 Apple Mac OS Big 0
12 Microsoft Windows x86 64-bit Little 0
17 Solaris Operating System (x86) Little 0
18 IBM Power Based Linux Big 0
19 HP IA Open VMS Little 0
20 Solaris Operating System (x86-64) Little 0
21 Apple Mac OS (x86-64) Little 0
20 rows selected.
[oracle12@dotang ~]$ rman target sysbackup/oracle
Recovery Manager: Release 12.2.0.1.0 - Production on Sun Jul 30 14:51:45 2017 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: DOTANG12 (DBID=3252395462) RMAN> shutdown immediate
using target database control file instead of recovery catalog database closed
database dismounted Oracle instance shut down RMAN> startup mount;
connected to target database (not started) Oracle instance started
database mounted
Total System Global Area 2147483648 bytes
- 31 – Fixed Size 8622776 bytes
Variable Size 838864200 bytes Database Buffers 1174405120 bytes Redo Buffers 8151040 bytes In-Memory Area 117440512 bytes RMAN> alter database open read only;
Statement processed
RMAN> configure device type disk parallelism 5;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 5 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
-> for transport & to platform 서로 배타적인 옵션이며, to platform 옵션을 사용한다면, conversion 작업이 source database 에서 일어나기 때문에 백업 시간이 더 걸리고 CPU 나 I/O 리소스를 더 소비 시킴.
RMAN> backup as compressed backupset for transport 2> format '/oracle12/backup/source_%U' database;
Starting backup at 30-JUL-17
using target database control file instead of recovery catalog allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=49 device type=DISK allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=50 device type=DISK allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=51 device type=DISK allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=52 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle12/oradata/DOTANG12/system01.dbf channel ORA_DISK_1: starting piece 1 at 30-JUL-17
channel ORA_DISK_2: starting compressed full datafile backup set
- 32 – channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/oracle12/oradata/DOTANG12/sysaux01.dbf channel ORA_DISK_2: starting piece 1 at 30-JUL-17
channel ORA_DISK_3: starting compressed full datafile backup set channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00004 name=/oracle12/oradata/DOTANG12/undotbs01.dbf channel ORA_DISK_3: starting piece 1 at 30-JUL-17
channel ORA_DISK_4: starting compressed full datafile backup set channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00007 name=/oracle12/oradata/DOTANG12/users01.dbf channel ORA_DISK_4: starting piece 1 at 30-JUL-17
channel ORA_DISK_4: finished piece 1 at 30-JUL-17
piece handle=/oracle12/backup/source_1psallqj_1_1 tag=TAG20170730T165858 comment=NONE channel ORA_DISK_4: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_3: finished piece 1 at 30-JUL-17
piece handle=/oracle12/backup/source_1osallqj_1_1 tag=TAG20170730T165858 comment=NONE channel ORA_DISK_3: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_2: finished piece 1 at 30-JUL-17
piece handle=/oracle12/backup/source_1nsallqj_1_1 tag=TAG20170730T165858 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:01:06
channel ORA_DISK_1: finished piece 1 at 30-JUL-17
piece handle=/oracle12/backup/source_1msallqj_1_1 tag=TAG20170730T165858 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
Finished backup at 30-JUL-17
RMAN> backup to platform='Linux x86 64-bit'
2> format '/oracle12/backup/linux_source_%U' database;
Starting backup at 30-JUL-17 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 using channel ORA_DISK_5
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=/oracle12/oradata/DOTANG12/system01.dbf channel ORA_DISK_1: starting piece 1 at 30-JUL-17
channel ORA_DISK_2: starting full datafile backup set
- 33 – channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/oracle12/oradata/DOTANG12/sysaux01.dbf channel ORA_DISK_2: starting piece 1 at 30-JUL-17
channel ORA_DISK_3: starting full datafile backup set channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00004 name=/oracle12/oradata/DOTANG12/undotbs01.dbf channel ORA_DISK_3: starting piece 1 at 30-JUL-17
channel ORA_DISK_4: starting full datafile backup set channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00007 name=/oracle12/oradata/DOTANG12/users01.dbf channel ORA_DISK_4: starting piece 1 at 30-JUL-17
channel ORA_DISK_3: finished piece 1 at 30-JUL-17
piece handle=/oracle12/backup/linux_source_1ssalm13_1_1 tag=TAG20170730T170226 comment=NONE channel ORA_DISK_3: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_4: finished piece 1 at 30-JUL-17
piece handle=/oracle12/backup/linux_source_1tsalm13_1_1 tag=TAG20170730T170226 comment=NONE channel ORA_DISK_4: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: finished piece 1 at 30-JUL-17
piece handle=/oracle12/backup/linux_source_1qsalm13_1_1 tag=TAG20170730T170226 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_2: finished piece 1 at 30-JUL-17
piece handle=/oracle12/backup/linux_source_1rsalm13_1_1 tag=TAG20170730T170226 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:15
Finished backup at 30-JUL-17
-> source 에서 conversion 을 했다면 all foreign datafiles 옵션을, 반대에 경우라면 foreign database to new 옵션을 사용
RMAN> restore from platform 'Linux x86 64-bit' 2> foreign database to new
3> from backupset '/oracle12/backup/source_1msallqj_1_1' 4> backupset '/oracle12/backup/source_1nsallqj_1_1' 5> backupset '/oracle12/backup/source_1osallqj_1_1' 6> backupset '/oracle12/backup/source_1psallqj_1_1';
Starting restore at 30-JUL-17 allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
- 34 –
channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring all foreign files in backup piece
channel ORA_DISK_1: reading from backup piece /oracle12/backup/source_1msallqj_1_1 channel ORA_DISK_1: restoring foreign file 1 to
/oracle12/oradata/TEST/DOTANG12/datafile/o1_mf_system_dqv5schr_.dbf
channel ORA_DISK_1: foreign piece handle=/oracle12/backup/source_1msallqj_1_1 channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06 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 all foreign files in backup piece
channel ORA_DISK_1: reading from backup piece /oracle12/backup/source_1nsallqj_1_1 channel ORA_DISK_1: restoring foreign file 3 to
/oracle12/oradata/TEST/DOTANG12/datafile/o1_mf_sysaux_dqv5vfnc_.dbf
channel ORA_DISK_1: foreign piece handle=/oracle12/backup/source_1nsallqj_1_1 channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46 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 all foreign files in backup piece
channel ORA_DISK_1: reading from backup piece /oracle12/backup/source_1osallqj_1_1 channel ORA_DISK_1: restoring foreign file 4 to
/oracle12/oradata/TEST/DOTANG12/datafile/o1_mf_undotbs1_dqv5wvsd_.dbf channel ORA_DISK_1: foreign piece handle=/oracle12/backup/source_1osallqj_1_1 channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 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 all foreign files in backup piece
channel ORA_DISK_1: reading from backup piece /oracle12/backup/source_1psallqj_1_1 channel ORA_DISK_1: restoring foreign file 7 to
/oracle12/oradata/TEST/DOTANG12/datafile/o1_mf_users_dqv5wxvj_.dbf
channel ORA_DISK_1: foreign piece handle=/oracle12/backup/source_1psallqj_1_1 channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 Finished restore at 30-JUL-17
-> controlfile trace 를 생성 후 수정하여, 새로운 create controlfile 을 수행 후 database 을 open
- 35 –
6. Active Database Duplication
- Active duplicate 은 RMAN 11g 에서 처음으로 소개 되었고, Network 을 통해 source 로부터 auxiliary 로 full image copy 를 push process 를 이용하여 전송 (pushing 은 source channel 이 수행)
- 12 부터는 active duplication 을 위해 기본적으로 pull (또는 restore) process 와 backupset 을 이용
- Active database duplication 을 위해서 수행된 백업을 사용하지 않고 새로운 backupset 생성 후 이 백업을 auxiliary 로 바로 전송
- Backupset 을 이용하기 때문에 compression, section size, encryption 의 기능 사용
- Active duplicate 수행을 병렬처리하기 위해 multiple auxiliary channel 할당 필요 (11g 에서는 source channel 이 할 당)
- source database 에서 auxiliary server 로 data transfer 를 off-loading 하여 active duplication 이 처리되는 동안 source database 에서의 영향도 감소
- compression 과 multisection backup 을 이용해서 restore workload 를 분산하여 large 파일들을 갖는 데이터베이 스의 duplication 시간 감축
7. NOOPEN option
- RMAN duplicate 가 완료 된 후 auxiliary database 를 오픈 하지 않고 mount 상태로 유지 가능 - 12.1 전 버전은 duplication 의 마지막 단계에서 자동적으로 RMAN 에 의해서 open 되었음.
- 그 외 다른 용도로 옵션이 사용되는 경우
Fast incremental backup 구성 및 flashback database 설정 BCT file 의 이름과 경로 변경
Upgrade 모드에서 데이터베이스 open 하여 데이터베이스를 업그레이드 이후 업그레이드 스크립트 수행 가능.
8. 참 고 문 헌
- Master Note For Oracle Recovery Manager (RMAN) (문서 ID 1116484.1) - RMAN Enhancements in Oracle 12c (Doc ID 1534487.1)
- RMAN RECOVER TABLE Feature New to Oracle Database 12c (문서 ID 1521524.1) - RMAN: Tablespace Point In Time Recovery (TSPITR) Procedure. (문서 ID 109979.1) - Steps To Recover A Dropped Tablespace Using TSPITR (문서 ID 1277795.1)
- 12c How Perform Cross-Platform Database Transport to different Endian Platform with RMAN Backup Sets (문 서 ID 2013271.1)
- Cross-Platform Database Migration (across same endian) using RMAN backupset in 12c (문서 ID 2013540.1) - How to restore a pre-12c backup to a cross-platform, cross-endian 12c database (문서 ID 1644693.1)
