RMAN backup set 에서 archivelog 추출하여 logminor 수행
RMAN backup set 에서 archivelog 추출하여 logminor 수행
work.sql다음과 같이 진행하였습니다.
1. VTL에서 해당 아카이브 백업 셋 restore
2. rman catalog에 백업 셋 파일 등록
3. rman 백업 셋에서 특정 시간의 archive log 추출
4. ASM에서 file system으로 파일 복사
5. 테스트 DB 에 DB_NAME과 DB_ID변경
6. 테스트 DB 에 archive file 등록
7. logminor 수행
8. 대상 쿼리 확인
-------------------------------------------------------------------------------
-- 백업 파일 카탈로그 등록
-------------------------------------------------------------------------------
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
530866 B A A DISK 2018-02-21 01:24:05 1 1 YES TAG20180221T012146
530867 B A A DISK 2018-02-21 01:24:41 1 1 YES TAG20180221T012146
530868 B A A DISK 2018-02-21 01:24:59 1 1 YES TAG20180221T012146
530869 B A A DISK 2018-02-21 01:25:01 1 1 YES TAG20180221T012146
530910 B F A DISK 2018-02-21 02:20:31 6 1 YES TAG20180221T012512
530911 B F A DISK 2018-02-21 02:20:40 1 1 YES TAG20180221T012512
530912 B F A DISK 2018-02-21 02:20:41 1 1 YES TAG20180221T012512
530913 B F A DISK 2018-02-21 02:23:02 7 1 YES TAG20180221T012512
530914 B F A DISK 2018-02-21 02:33:36 7 1 YES TAG20180221T012512
530915 B F A DISK 2018-02-21 02:35:38 9 1 YES TAG20180221T012512
530916 B F A DISK 2018-02-21 02:36:53 9 1 YES TAG20180221T012512
530917 B F A DISK 2018-02-21 02:39:48 7 1 YES TAG20180221T012512
530918 B F A DISK 2018-02-21 02:40:03 9 1 YES TAG20180221T012512
530919 B F A DISK 2018-02-21 02:41:44 9 1 YES TAG20180221T012512
RMAN> catalog backuppiece '/BACKUP01/test/20180219_ARC_6vsrj454_1_1';
cataloged backup piece
backup piece handle=/BACKUP01/test/20180219_ARC_6vsrj454_1_1 RECID=58648 STAMP=968671863
RMAN> catalog backuppiece '/BACKUP01/test/20180219_ARC_70srj454_1_1';
cataloged backup piece
backup piece handle=/BACKUP01/test/20180219_ARC_70srj454_1_1 RECID=58649 STAMP=968671870
RMAN> catalog backuppiece '/BACKUP01/test/20180219_ARC_71srj454_1_1';
cataloged backup piece
backup piece handle=/BACKUP01/test/20180219_ARC_71srj454_1_1 RECID=58650 STAMP=968671877
RMAN> catalog backuppiece '/BACKUP01/test/20180219_ARC_72srj454_1_1';
cataloged backup piece
backup piece handle=/BACKUP01/test/20180219_ARC_72srj454_1_1 RECID=58651 STAMP=968671885
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
530866 B A A DISK 2018-02-21 01:24:05 1 1 YES TAG20180221T012146
530867 B A A DISK 2018-02-21 01:24:41 1 1 YES TAG20180221T012146
530868 B A A DISK 2018-02-21 01:24:59 1 1 YES TAG20180221T012146
530869 B A A DISK 2018-02-21 01:25:01 1 1 YES TAG20180221T012146
530910 B F A DISK 2018-02-21 02:20:31 6 1 YES TAG20180221T012512
530911 B F A DISK 2018-02-21 02:20:40 1 1 YES TAG20180221T012512
530912 B F A DISK 2018-02-21 02:20:41 1 1 YES TAG20180221T012512
530913 B F A DISK 2018-02-21 02:23:02 7 1 YES TAG20180221T012512
530914 B F A DISK 2018-02-21 02:33:36 7 1 YES TAG20180221T012512
530915 B F A DISK 2018-02-21 02:35:38 9 1 YES TAG20180221T012512
530916 B F A DISK 2018-02-21 02:36:53 9 1 YES TAG20180221T012512
530917 B F A DISK 2018-02-21 02:39:48 7 1 YES TAG20180221T012512
530918 B F A DISK 2018-02-21 02:40:03 9 1 YES TAG20180221T012512
530919 B F A DISK 2018-02-21 02:41:44 9 1 YES TAG20180221T012512
531610 B A A DISK 2018-02-19 01:21:40 1 1 YES TAG20180219T012138
531633 B A A DISK 2018-02-19 01:21:40 1 1 YES TAG20180219T012138
531665 B A A DISK 2018-02-19 01:21:40 1 1 YES TAG20180219T012138
531699 B A A DISK 2018-02-19 01:21:40 1 1 YES TAG20180219T012138
-------------------------------------------------------------------------------
-- 백업 파일 카탈로그 삭제 (참고)
-------------------------------------------------------------------------------
RMAN> change backuppiece '/BACKUP01/test/20180219_ARC_6vsrj454_1_1' uncatalog;
uncataloged backup piece
backup piece handle=/BACKUP01/test/20180219_ARC_6vsrj454_1_1 RECID=58644 STAMP=968670404
Uncataloged 1 objects
RMAN> change backuppiece '/BACKUP01/test/20180219_ARC_70srj454_1_1' uncatalog;
uncataloged backup piece
backup piece handle=/BACKUP01/test/20180219_ARC_70srj454_1_1 RECID=58645 STAMP=968670447
Uncataloged 1 objects
RMAN> change backuppiece '/BACKUP01/test/20180219_ARC_71srj454_1_1' uncatalog;
uncataloged backup piece
backup piece handle=/BACKUP01/test/20180219_ARC_71srj454_1_1 RECID=58646 STAMP=968670466
Uncataloged 1 objects
RMAN> change backuppiece '/BACKUP01/test/20180219_ARC_72srj454_1_1' uncatalog;
uncataloged backup piece
backup piece handle=/BACKUP01/test/20180219_ARC_72srj454_1_1 RECID=58647 STAMP=968670480
Uncataloged 1 objects
-------------------------------------------------------------------------------
-- 아카이브 파일 복구
-------------------------------------------------------------------------------
ASMCMD> ls -al
WARNING:option 'a' is deprecated for 'ls'
please use 'absolutepath'
Type Redund Striped Time Sys Name
ARCHIVELOG HIGH COARSE FEB 21 03:00:00 Y none => thread_1_seq_16247.875.968641325
ARCHIVELOG HIGH COARSE FEB 21 05:00:00 Y none => thread_1_seq_16248.880.968649943
ARCHIVELOG HIGH COARSE FEB 21 07:00:00 Y none => thread_1_seq_16249.877.968658249
ARCHIVELOG HIGH COARSE FEB 21 09:00:00 Y none => thread_1_seq_16250.812.968662923
ARCHIVELOG HIGH COARSE FEB 21 10:00:00 Y none => thread_1_seq_16251.876.968667701
ARCHIVELOG HIGH COARSE FEB 21 03:00:00 Y none => thread_2_seq_17067.821.968642667
ARCHIVELOG HIGH COARSE FEB 21 05:00:00 Y none => thread_2_seq_17068.851.968650831
ARCHIVELOG HIGH COARSE FEB 21 07:00:00 Y none => thread_2_seq_17069.874.968658879
ARCHIVELOG HIGH COARSE FEB 21 10:00:00 Y none => thread_2_seq_17070.834.968666563
RUN
{
RESTORE ARCHIVELOG FROM TIME = "to_date('2018-02-18 17:50:00','YYYY-MM-DD HH24:MI:SS')"
UNTIL TIME = "to_date('2018-02-18 18:10:00','YYYY-MM-DD HH24:MI:SS')";
}
ASMCMD> ls -al
WARNING:option 'a' is deprecated for 'ls'
please use 'absolutepath'
Type Redund Striped Time Sys Name
ARCHIVELOG HIGH COARSE FEB 21 11:00:00 Y none => thread_1_seq_16205.855.968672069
ARCHIVELOG HIGH COARSE FEB 21 11:00:00 Y none => thread_1_seq_16206.845.968672069
ARCHIVELOG HIGH COARSE FEB 21 03:00:00 Y none => thread_1_seq_16247.875.968641325
ARCHIVELOG HIGH COARSE FEB 21 05:00:00 Y none => thread_1_seq_16248.880.968649943
ARCHIVELOG HIGH COARSE FEB 21 07:00:00 Y none => thread_1_seq_16249.877.968658249
ARCHIVELOG HIGH COARSE FEB 21 09:00:00 Y none => thread_1_seq_16250.812.968662923
ARCHIVELOG HIGH COARSE FEB 21 10:00:00 Y none => thread_1_seq_16251.876.968667701
ARCHIVELOG HIGH COARSE FEB 21 11:00:00 Y none => thread_2_seq_17037.807.968672069
ARCHIVELOG HIGH COARSE FEB 21 03:00:00 Y none => thread_2_seq_17067.821.968642667
ARCHIVELOG HIGH COARSE FEB 21 05:00:00 Y none => thread_2_seq_17068.851.968650831
ARCHIVELOG HIGH COARSE FEB 21 07:00:00 Y none => thread_2_seq_17069.874.968658879
ARCHIVELOG HIGH COARSE FEB 21 10:00:00 Y none => thread_2_seq_17070.834.968666563
-------------------------------------------------------------------------------
-- 아카이브 파일 복사
-------------------------------------------------------------------------------
ASMCMD> cp thread_1_seq_16205.855.968672069 /acfs_data/thread_1_seq_16205.855.968672069.arc
copying +RECOC1/ORAMP/ARCHIVELOG/2018_02_21/thread_1_seq_16205.855.968672069 -> /acfs_data/test/thread_1_seq_16205.855.968672069.arc
ASMCMD> cp thread_1_seq_16206.845.968672069 /acfs_data/thread_1_seq_16206.845.968672069.arc
copying +RECOC1/ORAMP/ARCHIVELOG/2018_02_21/thread_1_seq_16206.845.968672069 -> /acfs_data/test/thread_1_seq_16206.845.968672069.arc
ASMCMD> cp thread_2_seq_17037.807.968672069 /acfs_data/thread_2_seq_17037.807.968672069.arc
copying +RECOC1/ORAMP/ARCHIVELOG/2018_02_21/thread_2_seq_17037.807.968672069 -> /acfs_data/test/thread_2_seq_17037.807.968672069.arc
-------------------------------------------------------------------------------
-- 준비된 테스트 DB NAME 및 DBID 변경 작업
-------------------------------------------------------------------------------
SQL> shutdown immediate;
SQL> startup open read only;
SQL> select name,dbid from v$database;
-- 운영에서 사용 하고 있는 디비 명 및 디비 ID로 변경
SQL> @chg_dbname_dbid.sql (스크립트 내용 : TAB 확인)
Enter the new Database Name: ORAMP --> 변경 할 db name
Enter the new Database ID: 4224012920 --> 변경 할 db id
-- ##################### chg_dbname_dbid.sql ########################
--The script order came from The Internet
--The script can be modified as well as dbname, dbid, or both at The same time
--The test script OK under 10g, 11g needs to be tested
var old_name varchar2(20)
var old_dbid number
var new_name varchar2(20)
var new_dbid number
exec select name, dbid -
into :old_name,:old_dbid -
from v$database
print old_name
accept new_name prompt "Enter the new Database Name:"
accept new_dbid prompt "Enter the new Database ID:"
exec :new_name:='&&new_name'
exec :new_dbid:=&&new_dbid
set serveroutput on
exec dbms_output.put_line('Convert '||:old_name|| -
'('||to_char(:old_dbid)||') to '||:new_name|| -
'('||to_char(:new_dbid)||')')
declare
v_chgdbid binary_integer;
v_chgdbname binary_integer;
v_skipped binary_integer;
begin
dbms_backup_restore.nidbegin(:new_name,
:old_name,:new_dbid,:old_dbid,0,0,10);
dbms_backup_restore.nidprocesscf(
v_chgdbid,v_chgdbname);
dbms_output.put_line('ControlFile: ');
dbms_output.put_line(' => Change Name:'
||to_char(v_chgdbname));
dbms_output.put_line(' => Change DBID:'
||to_char(v_chgdbid));
for i in (select file#,name from v$datafile)
loop
dbms_backup_restore.nidprocessdf(i.file#,0,
v_skipped,v_chgdbid,v_chgdbname);
dbms_output.put_line('DataFile: '||i.name);
dbms_output.put_line(' => Skipped:'
||to_char(v_skipped));
dbms_output.put_line(' => Change Name:'
||to_char(v_chgdbname));
dbms_output.put_line(' => Change DBID:'
||to_char(v_chgdbid));
end loop;
for i in (select file#,name from v$tempfile)
loop
dbms_backup_restore.nidprocessdf(i.file#,1,
v_skipped,v_chgdbid,v_chgdbname);
dbms_output.put_line('DataFile: '||i.name);
dbms_output.put_line(' => Skipped:'
||to_char(v_skipped));
dbms_output.put_line(' => Change Name:'
||to_char(v_chgdbname));
dbms_output.put_line(' => Change DBID:'
||to_char(v_chgdbid));
end loop;
dbms_backup_restore.nidend;
end;
/
-- ##################################################################
SQL> create pfile from spfile;
SQL> shutdown immediate;
$ export ORACLE_SID=ORAMP
-- initORAMP.ora 의 db_name=ORAMP 로 변경
SQL> startup pfile=/oracle/112/db/ORABK/dbs/initORAMP.ora mount;
SQL> alter database open resetlogs;
SQL> startup force;
SQL> select name,dbid from v$database;
-------------------------------------------------------------------------------
-- LOG MINER 작업
-------------------------------------------------------------------------------
SQL> show parameter utl_file_dir
SQL> alter system set utl_file_dir='/acfs_data/test/' scope=spfile;
SQL> exec dbms_logmnr_d.build('logminrfile.ora','/acfs_data/test/');
-- 최초 등록
SQL> exec dbms_logmnr.add_logfile ('/acfs_data/test/thread_1_seq_16205.855.968672069.arc',dbms_logmnr.new);
-- 추가 등록
SQL> exec dbms_logmnr.add_logfile ('/acfs_data/test/thread_1_seq_16206.845.968672069.arc',dbms_logmnr.addfile);
SQL> exec dbms_logmnr.add_logfile ('/acfs_data/test/thread_2_seq_17037.807.968672069.arc',dbms_logmnr.addfile);
SQL> exec dbms_logmnr.start_logmnr(dictFileName => '/acfs_data/test/logminrfile.ora');
-- 조회 (적절히 변경 할 것)
SQL> select to_char(timestamp,'YYYY-MM-DD HH24:MI:SS'), username, session_info, sql_redo
from v$logmnr_contents
where OPERATION_CODE = 3
and seg_name in ('OBJ# 24005','OBJ# 24133')
and timestamp between to_date('2018-02-18 17:50:00','YYYY-MM-DD HH24:MI:SS')
and to_date('2018-02-18 18:20:00','YYYY-MM-DD HH24:MI:SS')
order by timestamp desc;
select timestamp, seg_name, operation, operation_code, row_id, sql_redo
from sys.logmnr_test
where timestamp between to_date('2018-02-18 17:50:00','YYYY-MM-DD HH24:MI:SS') and to_date('2018-02-18 18:30:00','YYYY-MM-DD HH24:MI:SS')
and seg_name in ('OBJ# 24005','OBJ# 24133')
and operation in ('INSERT','UPDATE','DALETE')
and row_id in ('AAAF5FAEXAADPJtAAC','AAAF3FAEXAAEZjnAAE','AAAF3FAEXAAEZjnAAF')
order by timestamp;