logminer & redo scripts

p.148

supplemental 확인===========================================

select supplemental_log_data_min from v$database;

supplemental 활성화=========================================

alter database add supplemental log data;

select supplemental_log_data_min from v$database;

supplemental 비활성화=======================================

alter dataabse drop supplemental log data;

select supplemental_log_data_min from v$database;

supplemental log 특정 테이블 적용===========================

alter table scott.emp add supplemental log data (all) columns;

p. 151

vi log.sql

set line 200

col group# for 999

col mb for 999

col member for a 45

col seq# for 999

col status for a8

col arc for a5

select a.group#, a.member, b.sequence#"SEQ#", b.status, b.archived"ARC"

from v$logfile a, v$log b

where a.group#=b.group#

order by 1,2

/

select supplemental_log_data_min from v$database;

create table scott.test1 (no number);

insert into scott.test1 values(1);

commit;

drop table scott.test1 purge;

p. 152

@log

Insert into emp (name,salary)values('John Doe',50000);

p.154

01) DB 전체를 종료한 후 Parameter file 에 딕셔너리 파일의 위치를 아래와 같이 지정 합니다.

        

        utl_file_dir=/app/oracle/logminer

                       디렉토리는 미리생성 해야합니다.

                       

    02) DB를 시작합니다.

    

    03) 아래와 같이 딕셔너리를 생성합니다.

    

         SYS> exec dbms_logmnr_d.build(dictionary_filename=>d'dict.dat' - dictionary_location => '/app/oracle/logminer');

         

    04) Log Miner에 분석할 로그를 추가합니다.

         SYS> exec dbms_logmnr.add_logfile('app/oracle/oradata/testdb/redo01.log'1);

         

         위 명령의 마지막에 있는 숫자의 의미는 

             1- 신규 등록 , 2- 파일 삭제, 3- 추가 등록

             

         SYS> exec dbms_logmnr.add_logfile('app/oracle/oradata/testdb/redo02.log'3);

         

         SYS> exec dbms_logmnr.add_logfile('app/oracle/oradata/testdb/redo03.log'3);

         

p. 155

set line 200

col db_name for a15

col filename for a50

select db_name, filename from v$logmnr_logs;

vi ar.sh

for i in $(ls /data/arc2);

  do echo"exec dbms_logmnr.add_logfile("/data/arc2/$i'3);" >> /home/oracle/arch.sql;

done;

:wq!

sh ar.sh

vi /home/oracle/arch.sql

exec dbms_logmnr.add_logfile('data/arc2/('위에서 수행한 아카이브 파일 번호',3);

exec dbms_logmnr.add_logfile('data/arc2/('위에서 수행한 아카이브 파일 번호',3);

exec dbms_logmnr.add_logfile('data/arc2/('위에서 수행한 아카이브 파일 번호',3);

exec dbms_logmnr.add_logfile('data/arc2/('위에서 수행한 아카이브 파일 번호',3);

exec dbms_logmnr.add_logfile('data/arc2/('위에서 수행한 아카이브 파일 번호',3);

exec dbms_logmnr.add_logfile('data/arc2/('위에서 수행한 아카이브 파일 번호',3);

p.156

@/home/oracle/arch.sql

exec dbms_logmnr.start_logmnr(dictfilename => '/app/oracle/logminer/dict.dat', - options => dbms_logmnr.ddl_dict_tracking + dbms_logmnr.committer_data_only);

col username for a10

col operation for a10

col sql_redo for a50

alter session set nls_date-format='YYYY-MM-DD:HH24:MI:SS';

select timestamp,usemade,operation,sql_redo from v$logmnr_contents where seg_name='TEST1';

p.157

@log

select supplemental_log_data_min from v$database;

create table scott.test2 (no number, name varchar2(10);

insert into scott.test2 values (1,'AAA');

insert into scott.test2 values (2,'BBB');

p. 158

insert into scott.test2 values (3,'CCC');

commit;

select * from scott.test2;

update scott.test2 set nmae='DDD';

select* from scott.test2;

commit;

p. 159

show parameter utl

exec dbms_logmnr_d.build(dicitionary_filename => 'dict2.dat',-dictionary_location => '/app/oracle/logmnr');

@log

set line 200

col filename for a50

col db_name for a10

select db_name, filename from v$log mnr_logs;

exec dbms_logmnr.start_logmnr(dictfilename => '/app/oracle/logminer/dict2.dat')

col username for a10

col operation for a10

col sql_redo for a70

set pagesize 50

alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS';

select timestamp,username,operation,sql_redo

   from v$logmnr_contents

    where seg_name='TEST2'

    

P.163

Alter database add supplemental log data;

p.167

vi log.sql

set line 200

col group# for 999

col mb for 999

col member for a 45

col seq# for 999

col status for a8

col arc for a5

select a.group#, a.member, b.sequence#"SEQ#", b.status, b.archived"ARC"

from v$logfile a, v$log b

where a.group#=b.group#

order by 1,2

/

:wq!

@log

case 1 -1 개의 member 가 삭제되는 장애가 발생하는 경우

!rm -f /app/oracle/oradata/testdb/redo03_a.log

!ls /app/oracle/oradata/testdb/redo03_a.log

alter system switch log file;

/

/

/

@log

p. 169

!

vi /app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log

p. 170

@log

alter database drop logfile member '/app/oracle/oradata/testdb/redo03_a.log';

@log

alter database add logfile member '/app/oracle/oradata/testdb/redo03_a.log'to group 3;

@log

p.172

@log

!rm /app/oracle/oradata/testdb/redo02*

!ls /app/oracle/oradata/testdb/redo02*

shutdown immediate;

startup

p.173

vi /app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log

p.174

exit

sqlplus sys/[패스워드 입력] as syssdba

startup mount;

@log

p. 175

alter database drop logfile group2;

@log

alter database open;

p. 176

alter database add log file group 2 (

 '/app/oracle/oradata/testdb/redo02_a.log',

 '/app/oracle/oradata/testdb/redo02_b.log') size 50M;

 

 

 @log

 

!rm -f /app/oracle/oradata/testdb/redo03*

p.177

!ls /app/oracle/oradata/testdb/redo03*

alter system switch logfile;

/

/

/

/

@log

p.178

vi /app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log

p.179

alter database clear unarchived logfile group 3;

p. 180

alter system switch logfile;

/

/

/

tail -f /app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log

p. 181

alter database clear unarchived logfile group 3;

alter database drop logfile group 3;

!

rm -f /app/oracle/oradata/testdb/redo03*

ls /app/oracle/oradata/testdb/redo03*

exit

@log

alter database add logfile group 3

 ('/app/oracle/oradata/testdb/redo03_a.log'.'/app/oracle/oradata/testdb/redo03_b.log') size 50M;

 

 

@log

 

 

p. 182

@log

p.183

!rm -f /app/oracle/oradata/testdb/redo02*

!ls /app/oracle/oradata/testdb/redo02*

alter system switch logfile;

/

/

/

p.184

sqlplus scott/tiger

sys/[패스워드] /as sysdba

shutdown abort;

startup

p.185

vi /app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log

exit

sqlplus sys/[패스워드] as sysdba

p.186

startup mount;

alter database drop logfile group 2;

alter database cler unarchived logfile group 2;

alter database open;

!ls /app/oracle/oradata/testdb/redo02*

/app/oracle/oradata/testdb/redo02_a.log /app/oracle/oradata/testdb/redo02_b.log 

@log

p. 187

@log

p. 188

create table scott.tt200 (no number) tablespace users;

insert into scott.tt200 values (1);

commit;

@log

p.189

!rm -f /app/oracle/oradata/testdb/redo02*

!ls /app/oracle/oradata/testdb/redo02_b.log

!ls /app/oracle/oradata/testdb/redo02_a.log

shutdown immediate;

startup

vi /app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log

p.191

recover database until cancel;

alter database open restlogs;

select * from scott.tt200;

p.192

@log

p.193

create table scott.tt600 (no number);

insert into scott.tt600 values (1);

commit;

alter system switch logfile;

@log

p.194

insert into scott.tt600 values (2);

commit;

alter system switch logfile;

@log

insert into scott.tt600 values (3);

commit;

select * from scott.tt600;

p.195

@log

!rm -f /app/oracle/oradata/testdb/redo03*

!ls /app/oracle/oradata/testdb/redo03*

shutdown abort;

p. 196

!cp /data/backup/close/*.dbf /app/oracle/oradata/testdb/

p. 197

startup mount;

recover databse;

recover databse until cancel;

p.198

alter database open restlogs;

select * from scott.tt600;

p.199

shutdown immediate;

startup mount;

archive log list;

p. 200

alter databse noarchivelog;

alter database open;

@log

create table scott.tt650 (no number);

insert into scott.tt650 values (1);

p.201

commit;

alter system switch logfile;

@log

insert into scott.tt650 values (2);

commit;

alter system switch logfile;

@log

p. 202

insert into scott.tt650 values (3);

commit;

select * from scott.tt650;

alter system switch log file;

/

p. 203

/

@log

!rm -f /app/oracle/oradata/testdb/redo03*

!ls /app/oracle/oradata/testdb/redo03*

shutdown abort;

! $ORACLE_HOME/dbs/inittestdb.ora

_allow_resetlogs_corruption=true

:wq!

p. 204

startup

@log

p. 205

recover database until cancel;

alter database open resetlogs;

select * from scott.tt650 ;

p. 206

@log

create table scott.tt650 (no number);

insert into scott.tt650 values (1);

commit;

alter system switch logfile;

insert into scott.tt660 values (2);

commit;

p. 207

@log

alter system switch logfile;

insert into scott.tt660 values (3);

commit;

@log

!rm /app/oracle/oradata/testdb/redo03*

p. 208

!ls /app/oracle/oradata/testdb/redo03*

shutdown abort;

!vi $ORACLE_HOME/dbs/inittestdb.ora

_allow_resetlogs_corruption=true

:wq!

startup mount

@log

p. 209

recover database until cancel;

p. 210

alter database open resetlogs;

select * from scott.tt660;

p.213

@log

select status from v$instance;

!rm /app/oracle/oradta/testdb/*.log

alter system switch logfile;

/

p.214

/

@log

alter databse clear unarchived logfile group 3;

alter system switch logfile;

/

p. 215

@log

alter database clear unarchived logfile group 1;

alter system switch logfile;

/

p. 216

@log

alter database clear unarchived logfile group 2;

alter system switch logfile;

/

/