본문 바로가기
Oracle/RMAN

RMAN FULL BACKUP을 활용한 임시DB 완전 복구

by 취미툰 2024. 5. 10.
반응형

 

먼저 rman full backup을 수행합니다. 이미 backup본이 있다면 생략해도 됩니다.

1)

백업 스크립트

백업 스크립트
run
{
configure compression algorithm 'HIGH';
configure channel device type disk format '/ora_work/rman/ysbae/%d_%U_FULL_COMPRESS_%T.bk';
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/ora_work/rman/ysbae/rman_ctl_%F';
backup database;
}

 

2)

복구스크립트

※ dulplicate 명령어를 사용하여 IMSIDB에 복구 예정 FULL RESTORE + archive log를 먹이는 방식 사용예정.
※복구 디렉토리는 /ora_work 아래로 가정함.

 

1.디렉토리 생성

mkdir -p /ora_work/IMSIDB/controlfile
mkdir -p /ora_work/IMSIDB/datafile
mkdir -p /ora_work/IMSIDB/arch
mkdir -p /ora_work/IMSIDB/log

 

2.init파일 수정

2. init파일 생성
cd /oracle/db/product/12.2.0/dbs/initIMSIDB.ora
vi initIMSIDB.ora
*.audit_file_dest='/ora_work/IMSIDB/log'
*.audit_sys_operations=FALSE
*.audit_trail='db'
*.cluster_database=false
*.compatible='12.2.0'
*.control_files='/ora_work/IMSIDB/controlfile/control1.ctl'#Restore Controlfile
*.core_dump_dest='/ora_work/IMSIDB/log/DBCORP/cdump'
*.db_block_size=8192
*.db_cache_advice='ON'
*.db_create_file_dest='/ora_work/IMSIDB/datafile'
*.db_files=5000
*.db_file_multiblock_read_count=128
*.db_flashback_retention_target=10080
*.db_name='IMSIDB'
*.db_unique_name=IMSIDB
*.deferred_segment_creation=FALSE
#*.diagnostic_dest='/logs/oracle'
*.diagnostic_dest='/ora_work/IMSIDB/log'
#*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBIMSIXDB)'
*.enable_ddl_logging=FALSE
*.log_archive_dest_1='LOCATION=/ora_work/IMSIDB/arch'
#*.log_archive_format='archDBCORD_%t_%s_%r.arc'
*.log_archive_format='archDBCORP_%t_%s_%r.arc'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.optimizer_adaptive_plans=FALSE
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.parallel_force_local=TRUE
#*.pga_aggregate_target=512M
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.remote_os_roles=FALSE
*.sec_max_failed_login_attempts=4
*.sec_protocol_error_trace_action='LOG'
*.sec_return_server_release_banner=FALSE
*.service_names='DBCORP'
*.sga_target=3G
*.skip_unusable_indexes=TRUE
*.timed_statistics=TRUE
*.undo_retention=10800
*.workarea_size_policy='AUTO'
*.undo_tablespace='UNDOTBS1'
*.shared_pool_size=1G
*.job_queue_processes=0 #disable dba_jobs

 

 

3.imsidb nomount

3. IMSIDB nomount 
export ORACLE_SID=IMSIDB
sqlplus / as sysdba

startup nomount pfile='/oracle/db/product/12.2.0/dbs/initIMSIDB.ora';
exit

 

4.rman 복구

4.rman 접속 후 duplicate 명령어 수행

rman target sys/원래db비밀번호@M_DBARAC  auxiliary sys/imsidb비밀번호

RMAN> run {
    set newname for database to '/ora_work/IMSIDB/datafile/%b';
    duplicate target database to IMSIDB
    until time "to_date('2024-05-09:00:00:00','yyyy-mm-dd:hh24:mi:ss')";
    };

 

로그 확인

executing command: SET NEWNAME

Starting Duplicate Db at 09-MAY-24
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    3221225472 bytes

Fixed Size                     8719856 bytes
Variable Size               1174405648 bytes
Database Buffers            2030043136 bytes
Redo Buffers                   8056832 bytes

contents of Memory Script:
{
   set until scn  9386699175734;
   sql clone "alter system set  db_name =
 ''DBARAC'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''IMSIDB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

executing command: SET until clause

sql statement: alter system set  db_name =  ''DBARAC'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''IMSIDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    3221225472 bytes

Fixed Size                     8719856 bytes
Variable Size               1174405648 bytes
Database Buffers            2030043136 bytes
Redo Buffers                   8056832 bytes

Starting restore at 09-MAY-24
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=423 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=453 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=3 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=33 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 /ora_work/rman/ysbae/rman_ctl_c-1627335910-20240508-01
channel ORA_AUX_DISK_1: piece handle=/ora_work/rman/ysbae/rman_ctl_c-1627335910-20240508-01 tag=TAG20240508T113955
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/ora_work/IMSIDB/controlfile/control1.ctl
Finished restore at 09-MAY-24

database mounted

contents of Memory Script:
{
   set until scn  9386699175734;
   set newname for datafile  1 to
 "/ora_work/IMSIDB/datafile/system.257.1079561135";
   set newname for datafile  3 to
 "/ora_work/IMSIDB/datafile/sysaux.258.1079561177";
   set newname for datafile  4 to
 "/ora_work/IMSIDB/datafile/undotbs1.259.1079561201";
   set newname for datafile  5 to
 "/ora_work/IMSIDB/datafile/undotbs2.265.1079561303";
   set newname for datafile  7 to
 "/ora_work/IMSIDB/datafile/users.260.1079561203";
   set newname for datafile  8 to
 "/ora_work/IMSIDB/datafile/ts_dbmon.295.1110403131";
   set newname for datafile  9 to
 "/ora_work/IMSIDB/datafile/ts_data01.287.1091539667";
   set newname for datafile  10 to
 "/ora_work/IMSIDB/datafile/ts_data01.288.1092476729";
   set newname for datafile  16 to
 "/ora_work/IMSIDB/datafile/ts_dbmgnt01.292.1130443047";
   restore
   clone database
   ;
}
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

executing command: SET NEWNAME

Starting restore at 09-MAY-24
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

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 /ora_work/IMSIDB/datafile/system.257.1079561135
channel ORA_AUX_DISK_1: restoring datafile 00005 to /ora_work/IMSIDB/datafile/undotbs2.265.1079561303
channel ORA_AUX_DISK_1: restoring datafile 00007 to /ora_work/IMSIDB/datafile/users.260.1079561203
channel ORA_AUX_DISK_1: restoring datafile 00016 to /ora_work/IMSIDB/datafile/ts_dbmgnt01.292.1130443047
channel ORA_AUX_DISK_1: reading from backup piece /ora_work/rman/ysbae/DBARAC_8d2q9jt5_1_1_FULL_COMPRESS_20240508.bk
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00003 to /ora_work/IMSIDB/datafile/sysaux.258.1079561177
channel ORA_AUX_DISK_2: restoring datafile 00004 to /ora_work/IMSIDB/datafile/undotbs1.259.1079561201
channel ORA_AUX_DISK_2: restoring datafile 00008 to /ora_work/IMSIDB/datafile/ts_dbmon.295.1110403131
channel ORA_AUX_DISK_2: restoring datafile 00009 to /ora_work/IMSIDB/datafile/ts_data01.287.1091539667
channel ORA_AUX_DISK_2: restoring datafile 00010 to /ora_work/IMSIDB/datafile/ts_data01.288.1092476729
channel ORA_AUX_DISK_2: reading from backup piece /ora_work/rman/ysbae/DBARAC_8c2q9jt5_1_1_FULL_COMPRESS_20240508.bk

channel ORA_AUX_DISK_1: piece handle=/ora_work/rman/ysbae/DBARAC_8d2q9jt5_1_1_FULL_COMPRESS_20240508.bk tag=TAG20240508T113604
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:08:45

channel ORA_AUX_DISK_2: piece handle=/ora_work/rman/ysbae/DBARAC_8c2q9jt5_1_1_FULL_COMPRESS_20240508.bk tag=TAG20240508T113604
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:19:15
Finished restore at 09-MAY-24

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=25 STAMP=1168511856 file name=/ora_work/IMSIDB/datafile/system.257.1079561135
datafile 3 switched to datafile copy
input datafile copy RECID=26 STAMP=1168511856 file name=/ora_work/IMSIDB/datafile/sysaux.258.1079561177
datafile 4 switched to datafile copy
input datafile copy RECID=27 STAMP=1168511856 file name=/ora_work/IMSIDB/datafile/undotbs1.259.1079561201
datafile 5 switched to datafile copy
input datafile copy RECID=28 STAMP=1168511856 file name=/ora_work/IMSIDB/datafile/undotbs2.265.1079561303
datafile 7 switched to datafile copy
input datafile copy RECID=29 STAMP=1168511856 file name=/ora_work/IMSIDB/datafile/users.260.1079561203
datafile 8 switched to datafile copy
input datafile copy RECID=30 STAMP=1168511856 file name=/ora_work/IMSIDB/datafile/ts_dbmon.295.1110403131
datafile 9 switched to datafile copy
input datafile copy RECID=31 STAMP=1168511856 file name=/ora_work/IMSIDB/datafile/ts_data01.287.1091539667
datafile 10 switched to datafile copy
input datafile copy RECID=32 STAMP=1168511856 file name=/ora_work/IMSIDB/datafile/ts_data01.288.1092476729
datafile 16 switched to datafile copy
input datafile copy RECID=33 STAMP=1168511856 file name=/ora_work/IMSIDB/datafile/ts_dbmgnt01.292.1130443047

contents of Memory Script:
{
   set until time  "to_date('MAY 09 2024 00:00:00', 'MON DD YYYY HH24:MI:SS')";
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 09-MAY-24
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

starting media recovery

archived log for thread 1 with sequence 10522 is already on disk as file +DG_RECO01/DBARAC/ARCHIVELOG/2024_05_08/thread_1_seq_10522.402.1168435645
archived log for thread 1 with sequence 10523 is already on disk as file +DG_RECO01/DBARAC/ARCHIVELOG/2024_05_08/thread_1_seq_10523.657.1168437459
archived log for thread 1 with sequence 10524 is already on disk as file +DG_RECO01/DBARAC/ARCHIVELOG/2024_05_08/thread_1_seq_10524.369.1168450207
archived log for thread 1 with sequence 10525 is already on disk as file +DG_RECO01/DBARAC/ARCHIVELOG/2024_05_08/thread_1_seq_10525.282.1168465211
archived log for thread 1 with sequence 10526 is already on disk as file +DG_RECO01/DBARAC/ARCHIVELOG/2024_05_08/thread_1_seq_10526.356.1168467591
archived log for thread 1 with sequence 10527 is already on disk as file +DG_RECO01/DBARAC/ARCHIVELOG/2024_05_08/thread_1_seq_10527.936.1168467627
archived log for thread 1 with sequence 10528 is already on disk as file +DG_RECO01/DBARAC/ARCHIVELOG/2024_05_08/thread_1_seq_10528.949.1168467663
archived log for thread 1 with sequence 10529 is already on disk as file +DG_RECO01/DBARAC/ARCHIVELOG/2024_05_09/thread_1_seq_10529.671.1168480819
archived log file name=+DG_RECO01/DBARAC/ARCHIVELOG/2024_05_08/thread_1_seq_10522.402.1168435645 thread=1 sequence=10522
archived log file name=+DG_RECO01/DBARAC/ARCHIVELOG/2024_05_08/thread_1_seq_10523.657.1168437459 thread=1 sequence=10523
archived log file name=+DG_RECO01/DBARAC/ARCHIVELOG/2024_05_08/thread_1_seq_10524.369.1168450207 thread=1 sequence=10524
archived log file name=+DG_RECO01/DBARAC/ARCHIVELOG/2024_05_08/thread_1_seq_10525.282.1168465211 thread=1 sequence=10525
archived log file name=+DG_RECO01/DBARAC/ARCHIVELOG/2024_05_08/thread_1_seq_10526.356.1168467591 thread=1 sequence=10526
archived log file name=+DG_RECO01/DBARAC/ARCHIVELOG/2024_05_08/thread_1_seq_10527.936.1168467627 thread=1 sequence=10527
archived log file name=+DG_RECO01/DBARAC/ARCHIVELOG/2024_05_08/thread_1_seq_10528.949.1168467663 thread=1 sequence=10528
archived log file name=+DG_RECO01/DBARAC/ARCHIVELOG/2024_05_09/thread_1_seq_10529.671.1168480819 thread=1 sequence=10529
media recovery complete, elapsed time: 00:01:26
Finished recover at 09-MAY-24
Oracle instance started

Total System Global Area    3221225472 bytes

Fixed Size                     8719856 bytes
Variable Size               1174405648 bytes
Database Buffers            2030043136 bytes
Redo Buffers                   8056832 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''IMSIDB'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
}
executing Memory Script

sql statement: alter system set  db_name =  ''IMSIDB'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance started

Total System Global Area    3221225472 bytes

Fixed Size                     8719856 bytes
Variable Size               1174405648 bytes
Database Buffers            2030043136 bytes
Redo Buffers                   8056832 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "IMSIDB" RESETLOGS ARCHIVELOG
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY     2336
 LOGFILE
  GROUP   5  SIZE 200 M ,
  GROUP  11  SIZE 200 M ,
  GROUP  12  SIZE 200 M ,
  GROUP  13  SIZE 200 M ,
  GROUP  14  SIZE 200 M ,
  GROUP  15  SIZE 200 M
 DATAFILE
  '/ora_work/IMSIDB/datafile/system.257.1079561135'
 CHARACTER SET AL32UTF8

sql statement: ALTER DATABASE ADD LOGFILE
  INSTANCE 'i2'
  GROUP  21  SIZE 200 M ,
  GROUP  22  SIZE 200 M ,
  GROUP  23  SIZE 200 M ,
  GROUP  24  SIZE 200 M ,
  GROUP  25  SIZE 200 M

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/ora_work/IMSIDB/datafile/temp.264.1079561263";
   set newname for tempfile  4 to
 "/ora_work/IMSIDB/datafile/temp_shyeon.302.1139748343";
   set newname for tempfile  5 to
 "/ora_work/IMSIDB/datafile/temp_shyeon.303.1139748343";
   set newname for tempfile  6 to
 "/ora_work/IMSIDB/datafile/temp_shyeon.304.1139748343";
   set newname for tempfile  7 to
 "/ora_work/IMSIDB/datafile/temp_shyeon.305.1139748343";
   switch clone tempfile all;
   catalog clone datafilecopy  "/ora_work/IMSIDB/datafile/sysaux.258.1079561177",
 "/ora_work/IMSIDB/datafile/undotbs1.259.1079561201",
 "/ora_work/IMSIDB/datafile/undotbs2.265.1079561303",
 "/ora_work/IMSIDB/datafile/users.260.1079561203",
 "/ora_work/IMSIDB/datafile/ts_dbmon.295.1110403131",
 "/ora_work/IMSIDB/datafile/ts_data01.287.1091539667",
 "/ora_work/IMSIDB/datafile/ts_data01.288.1092476729",
 "/ora_work/IMSIDB/datafile/ts_dbmgnt01.292.1130443047";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /ora_work/IMSIDB/datafile/temp.264.1079561263 in control file
renamed tempfile 4 to /ora_work/IMSIDB/datafile/temp_shyeon.302.1139748343 in control file
renamed tempfile 5 to /ora_work/IMSIDB/datafile/temp_shyeon.303.1139748343 in control file
renamed tempfile 6 to /ora_work/IMSIDB/datafile/temp_shyeon.304.1139748343 in control file
renamed tempfile 7 to /ora_work/IMSIDB/datafile/temp_shyeon.305.1139748343 in control file

cataloged datafile copy
datafile copy file name=/ora_work/IMSIDB/datafile/sysaux.258.1079561177 RECID=1 STAMP=1168511993
cataloged datafile copy
datafile copy file name=/ora_work/IMSIDB/datafile/undotbs1.259.1079561201 RECID=2 STAMP=1168511993
cataloged datafile copy
datafile copy file name=/ora_work/IMSIDB/datafile/undotbs2.265.1079561303 RECID=3 STAMP=1168511993
cataloged datafile copy
datafile copy file name=/ora_work/IMSIDB/datafile/users.260.1079561203 RECID=4 STAMP=1168511993
cataloged datafile copy
datafile copy file name=/ora_work/IMSIDB/datafile/ts_dbmon.295.1110403131 RECID=5 STAMP=1168511993
cataloged datafile copy
datafile copy file name=/ora_work/IMSIDB/datafile/ts_data01.287.1091539667 RECID=6 STAMP=1168511993
cataloged datafile copy
datafile copy file name=/ora_work/IMSIDB/datafile/ts_data01.288.1092476729 RECID=7 STAMP=1168511993
cataloged datafile copy
datafile copy file name=/ora_work/IMSIDB/datafile/ts_dbmgnt01.292.1130443047 RECID=8 STAMP=1168511993

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=1168511993 file name=/ora_work/IMSIDB/datafile/sysaux.258.1079561177
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=1168511993 file name=/ora_work/IMSIDB/datafile/undotbs1.259.1079561201
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=1168511993 file name=/ora_work/IMSIDB/datafile/undotbs2.265.1079561303
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1168511993 file name=/ora_work/IMSIDB/datafile/users.260.1079561203
datafile 8 switched to datafile copy
input datafile copy RECID=5 STAMP=1168511993 file name=/ora_work/IMSIDB/datafile/ts_dbmon.295.1110403131
datafile 9 switched to datafile copy
input datafile copy RECID=6 STAMP=1168511993 file name=/ora_work/IMSIDB/datafile/ts_data01.287.1091539667
datafile 10 switched to datafile copy
input datafile copy RECID=7 STAMP=1168511993 file name=/ora_work/IMSIDB/datafile/ts_data01.288.1092476729
datafile 16 switched to datafile copy
input datafile copy RECID=8 STAMP=1168511993 file name=/ora_work/IMSIDB/datafile/ts_dbmgnt01.292.1130443047
Reenabling controlfile options for auxiliary database
Executing: alter database add supplemental log data
Executing: alter database enable block change tracking using file '/ora_work/IMSIDB/datafile/ctf.306.1155991709'

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Cannot remove created server parameter file
Finished Duplicate Db at 09-MAY-24

 

완료.

 

 

 

 

반응형
저작자표시

'Oracle > RMAN' 카테고리의 다른 글

configure archivelog deletion policy to backed up 2 times to disk;  (0) 2024.08.07
[Recover Table] 여러 테이블 recover 및 remap 옵션  (0) 2023.06.14
[RMAN] recover table 테스트 시나리오  (0) 2022.07.01
RMAN을 사용하여 Clone DB 생성  (0) 2019.12.23
[RMAN]Recover Table  (2) 2019.12.22

관련글

댓글

티스토리툴바