OraclE

RMAN backup set 에서 archivelog 추출하여 logminor 수행

타마마임팩트_쫀 2018. 2. 22. 14:31


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;