본문 바로가기
DBMS/ORACLE

[oracle] COLD BACKUP 이후 발생한 아카이브 로그 적용 방법

by 드바 2024. 5. 19.

 


DBMS: oracle 19.3

 

- cold backup에서 recover 적용 시 using backup controlfile 옵션 없으면 아카이브 적용 안됨
- 백업 이후 발생한 아카이브 적용하려면 using backup controlfile 옵션 필수

 

[목차여기]
 

테스트 준비

COLD BACKUP 수행

SQL> select count(*) from sk.tab1 ;

  COUNT(*)
----------
     72373

SQL> shut immediate 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[ora19ent@svr ~]$ cp /ora19ent/oradata/ENT19/* /home/ora19ent/back/

 

아카이브 로그 발생

insert 후 log switch 수행하여 아카이브 로그 발생시킴

SQL> select count(*) from sk.tab1 ;

  COUNT(*)
----------
     72373

SQL> insert into sk.tab1 select * from dba_objects ;

72374 rows created.

SQL> select count(*) from sk.tab1 ;

  COUNT(*)
----------
    144747

SQL> SELECT * FROM v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC STATUS	       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME	  CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
	 1	    1	      10  209715200	   512		1 NO  CURRENT		     2455893 16-SEP-22	 1.8447E+19		       0
	 2	    1	       8  209715200	   512		1 YES INACTIVE		     2349961 16-SEP-22	    2450848 16-SEP-22	       0
	 3	    1	       9  209715200	   512		1 YES INACTIVE		     2450848 16-SEP-22	    2455893 16-SEP-22	       0

SQL> commit ;

Commit complete.

-- 아카이브 로그 발생
SQL> 
alter system switch logfile;
SQL> 
System altered.

SQL> SELECT * FROM v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC STATUS	       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME	  CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
	 1	    1	      10  209715200	   512		1 YES ACTIVE		     2455893 16-SEP-22	    2459385 16-SEP-22	       0
	 2	    1	      11  209715200	   512		1 NO  CURRENT		     2459385 16-SEP-22	 1.8447E+19		       0
	 3	    1	       9  209715200	   512		1 YES INACTIVE		     2450848 16-SEP-22	    2455893 16-SEP-22	       0

SQL> insert into sk.tab1 select * from dba_objects ;

72374 rows created.

SQL> select count(*) from sk.tab1 ;

  COUNT(*)
----------
    217121

SQL> commit ;

Commit complete.

-- 아카이브 로그 발생
SQL> alter system switch logfile;

System altered.

SQL> select * from v$log ;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC STATUS	       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME	  CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
	 1	    1	      10  209715200	   512		1 YES ACTIVE		     2455893 16-SEP-22	    2459385 16-SEP-22	       0
	 2	    1	      11  209715200	   512		1 YES ACTIVE		     2459385 16-SEP-22	    2459490 16-SEP-22	       0
	 3	    1	      12  209715200	   512		1 NO  CURRENT		     2459490 16-SEP-22	 1.8447E+19		       0

SQL> insert into sk.tab1 select * from dba_objects ;

72374 rows created.

SQL> select count(*) from sk.tab1 ;

  COUNT(*)
----------
    289495

SQL> commit ;

Commit complete.

-- 아카이브 로그 발생
SQL> alter system switch logfile;

System altered.

SQL> select * from v$log ;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC STATUS	       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME	  CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
	 1	    1	      13  209715200	   512		1 NO  CURRENT		     2459588 16-SEP-22	 1.8447E+19		       0
	 2	    1	      11  209715200	   512		1 YES ACTIVE		     2459385 16-SEP-22	    2459490 16-SEP-22	       0
	 3	    1	      12  209715200	   512		1 YES ACTIVE		     2459490 16-SEP-22	    2459588 16-SEP-22	       0

SQL> 
SQL> 
SQL> shut immediate

 

반응형

복구 테스트

using backup controlfile 옵션 미사용

- 복구시점을 현재시간으로 지정 후 recover 수행
- 백업시점 이후 발생한 아카이브 로그 반영 안 함

[ora19ent@svr ~]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 16 20:38:46 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1174404432 bytes
Fixed Size		    8895824 bytes
Variable Size		  318767104 bytes
Database Buffers	  838860800 bytes
Redo Buffers		    7880704 bytes
Database mounted.
SQL> !date
Fri Sep 16 20:40:32 KST 2022

SQL> recover database until time '2022.09.16 20:40:00';
Media recovery complete.
SQL> 
SQL> alter database open resetlogs ;

Database altered.

SQL> select count(*) from sk.tab1 ;

  COUNT(*)
----------
     72373

SQL>

 
 

using backup controlfile 옵션 사용

- 복구시점을 현재시간으로 지정하여 복구 수행
- cold backup 시점 이후 발생한 아카이브 로그 적용하여 복구됨

-- 테스트를 위한 기존 파일 삭제
[ora19ent@svr ~]$ rm -fr /ora19ent/oradata/ENT19/*
[ora19ent@svr ~]$ 
[ora19ent@svr ~]$ 
[ora19ent@svr ~]$ 
[ora19ent@svr ~]$ ls -al /ora19ent/oradata/ENT19/
total 0
drwxr-x---. 2 ora19ent ora19ent  6 Sep 16 20:45 .
drwxr-x---. 3 ora19ent ora19ent 19 Sep 16 14:40 ..
[ora19ent@svr ~]$ ls -al back
total 2525868
drwxrwxr-x.  2 ora19ent ora19ent      4096 Sep 16 20:28 .
drwx------. 11 ora19ent ora19ent      4096 Sep 16 20:43 ..
-rw-rw----.  1 ora19ent ora19ent  28007424 Sep 16 20:28 1_10_1115563313.arc
-rw-rw----.  1 ora19ent ora19ent  11889152 Sep 16 20:28 1_11_1115563313.arc
-rw-rw----.  1 ora19ent ora19ent  11922944 Sep 16 20:28 1_12_1115563313.arc
-rw-r-----.  1 ora19ent ora19ent  10600448 Sep 16 20:22 control01.ctl
-rw-r-----.  1 ora19ent ora19ent  10600448 Sep 16 20:22 control02.ctl
-rw-r-----.  1 ora19ent ora19ent 209715712 Sep 16 20:22 redo01.log
-rw-r-----.  1 ora19ent ora19ent 209715712 Sep 16 20:22 redo02.log
-rw-r-----.  1 ora19ent ora19ent 209715712 Sep 16 20:22 redo03.log
-rw-r-----.  1 ora19ent ora19ent 566239232 Sep 16 20:22 sysaux01.dbf
-rw-r-----.  1 ora19ent ora19ent 943726592 Sep 16 20:22 system01.dbf
-rw-r-----.  1 ora19ent ora19ent  33562624 Sep 16 20:22 temp01.dbf
-rw-r-----.  1 ora19ent ora19ent 356524032 Sep 16 20:22 undotbs01.dbf
-rw-r-----.  1 ora19ent ora19ent  15736832 Sep 16 20:22 users01.dbf

-- COLD BACKUP 복구
[ora19ent@svr ~]$ cp back/*.dbf /ora19ent/oradata/ENT19/
[ora19ent@svr ~]$ cp back/*.ctl /ora19ent/oradata/ENT19/
[ora19ent@svr ~]$ cp back/*.log /ora19ent/oradata/ENT19/
[ora19ent@svr ~]$ ls -al arch
total 50612
drwxrwxr-x.  2 ora19ent ora19ent       87 Sep 16 20:26 .
drwx------. 11 ora19ent ora19ent     4096 Sep 16 20:43 ..
-rw-rw----.  1 ora19ent ora19ent 28007424 Sep 16 20:24 1_10_1115563313.arc
-rw-rw----.  1 ora19ent ora19ent 11889152 Sep 16 20:26 1_11_1115563313.arc
-rw-rw----.  1 ora19ent ora19ent 11922944 Sep 16 20:26 1_12_1115563313.arc
[ora19ent@svr ~]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 16 20:46:44 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> exit
Disconnected
[ora19ent@svr ~]$ ls -al /ora19ent/oradata/ENT19/
total 2475256
drwxr-x---. 2 ora19ent ora19ent      4096 Sep 16 20:46 .
drwxr-x---. 3 ora19ent ora19ent        19 Sep 16 14:40 ..
-rw-r-----. 1 ora19ent ora19ent  10600448 Sep 16 20:46 control01.ctl
-rw-r-----. 1 ora19ent ora19ent  10600448 Sep 16 20:46 control02.ctl
-rw-r-----. 1 ora19ent ora19ent 209715712 Sep 16 20:46 redo01.log
-rw-r-----. 1 ora19ent ora19ent 209715712 Sep 16 20:46 redo02.log
-rw-r-----. 1 ora19ent ora19ent 209715712 Sep 16 20:46 redo03.log
-rw-r-----. 1 ora19ent ora19ent 566239232 Sep 16 20:46 sysaux01.dbf
-rw-r-----. 1 ora19ent ora19ent 943726592 Sep 16 20:46 system01.dbf
-rw-r-----. 1 ora19ent ora19ent  33562624 Sep 16 20:46 temp01.dbf
-rw-r-----. 1 ora19ent ora19ent 356524032 Sep 16 20:46 undotbs01.dbf
-rw-r-----. 1 ora19ent ora19ent  15736832 Sep 16 20:46 users01.dbf
[ora19ent@svr ~]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 16 20:46:50 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1174404432 bytes
Fixed Size		    8895824 bytes
Variable Size		  318767104 bytes
Database Buffers	  838860800 bytes
Redo Buffers		    7880704 bytes
Database mounted.

-- using backup controlfile 옵션 사용하여 recover
SQL> recover database until time '2022.09.16 20:40:00' using backup controlfile ;
ORA-00279: change 2458496 generated at 09/16/2022 20:16:14 needed for thread 1
ORA-00289: suggestion : /home/ora19ent/arch/1_10_1115563313.arc
ORA-00280: change 2458496 for thread 1 is in sequence #10


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2459385 generated at 09/16/2022 20:24:53 needed for thread 1
ORA-00289: suggestion : /home/ora19ent/arch/1_11_1115563313.arc
ORA-00280: change 2459385 for thread 1 is in sequence #11
ORA-00278: log file '/home/ora19ent/arch/1_10_1115563313.arc' no longer needed
for this recovery


ORA-00279: change 2459490 generated at 09/16/2022 20:26:07 needed for thread 1
ORA-00289: suggestion : /home/ora19ent/arch/1_12_1115563313.arc
ORA-00280: change 2459490 for thread 1 is in sequence #12
ORA-00278: log file '/home/ora19ent/arch/1_11_1115563313.arc' no longer needed
for this recovery


ORA-00279: change 2459588 generated at 09/16/2022 20:26:55 needed for thread 1
ORA-00289: suggestion : /home/ora19ent/arch/1_13_1115563313.arc
ORA-00280: change 2459588 for thread 1 is in sequence #13
ORA-00278: log file '/home/ora19ent/arch/1_12_1115563313.arc' no longer needed
for this recovery


ORA-00308: cannot open archived log '/home/ora19ent/arch/1_13_1115563313.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7


SQL> alter database open resetlogs ;

Database altered.

-- 아카이브 로그 반영됨
SQL> select count(*) from sk.tab1 ;

  COUNT(*)
----------
    289495

SQL> exit

'DBMS > ORACLE' 카테고리의 다른 글

[oracle] deadlock 테스트 ORA-00060  (1) 2024.06.13
[oracle] TRANSACTION 모드, ISOLATION LEVEL 테스트  (0) 2024.05.29
[oracle] windows 오라클 설치 시 setup.exe 무반응 해결방법  (0) 2024.05.07
[oracle]alter user ... replace 구문 (ORA-28221)  (0) 2024.04.30
[oracle] data pump 작업 시 trigger 주의  (0) 2024.04.02

관련글

댓글

티스토리툴바