About DATABASE

주로 사용자의 실수로 datafile 삭제

-- 전제 조건 아카이브 모드 상태.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /arch
Oldest online log sequence     83
Next log sequence to archive   85
Current log sequence           85
SQL> 

-- 상태 확인 
SQL> select file#,name,status,CREATION_TIME from v$datafile;

     FILE# NAME                                               STATUS  CREATION_TIM
---------- -------------------------------------------------- ------- ------------
         1 /oradata/ORA19C/system01.dbf                       SYSTEM  17-APR-19
         3 /oradata/ORA19C/sysaux01.dbf                       ONLINE  17-APR-19
         4 /oradata/ORA19C/undotbs01.dbf                      ONLINE  17-APR-19
         5 /oradata/ORA19C/users02.dbf                        ONLINE  14-FEB-24
         7 /oradata/ORA19C/users01.dbf                        ONLINE  17-APR-19
         8 /oradata/ORA19C/soe01.dbf                          ONLINE  22-JAN-24

-- 장애 상황 발생 
[oracle@ora19c ORA19C]$ rm users02.dbf 
[oracle@ora19c ORA19C]$ exit

-- 로그 스위칭 도중 db down 발생
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5028
Session ID: 154 Serial number: 38319

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
-- DB 마운트 모드 기동
[ora19c:/home/oracle]> ss

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 14 14:52:56 2024
Version 19.21.0.0.0

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3137336112 bytes
Fixed Size                  8930096 bytes
Variable Size             671088640 bytes
Database Buffers         2449473536 bytes
Redo Buffers                7843840 bytes
Database mounted.
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME             CON_ID
---------- ------- ------- -------------------- ---------- ------------ ----------
         5 ONLINE  ONLINE  FILE NOT FOUND                0                       0
 
SQL>  select file#,name,status,CREATION_TIME from v$datafile;
     FILE# NAME                                          STATUS  CREATION_TIM
---------- --------------------------------------------- ------- ------------
         1 /oradata/ORA19C/system01.dbf                  SYSTEM  17-APR-19
         3 /oradata/ORA19C/sysaux01.dbf                  ONLINE  17-APR-19
         4 /oradata/ORA19C/undotbs01.dbf                 ONLINE  17-APR-19
         5 /oradata/ORA19C/users02.dbf                   ONLINE  14-FEB-24
         7 /oradata/ORA19C/users01.dbf                   ONLINE  17-APR-19
         8 /oradata/ORA19C/soe01.dbf                     ONLINE  22-JAN-24

6 rows selected.
-- 삭제 파일  껍데기 만들어 줌
SQL> alter database create datafile '/oradata/ORA19C/users02.dbf';

Database altered.
-- 복구 
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;

Database altered.
-- db open 완료
SQL> alter database open;

Database altered. 

-- 복구 완료 확인

SQL> select * from v$recover_file;

no rows selected

SQL> col file_name for a45
SQL> set lines 200
SQL>  select tablespace_name,file_name,bytes/1024/1024 from dba_data_files;

TABLESPACE_NAME                FILE_NAME                                     BYTES/1024/1024
------------------------------ --------------------------------------------- ---------------
USERS                          /oradata/ORA19C/users01.dbf                             93.75
UNDOTBS1                       /oradata/ORA19C/undotbs01.dbf                             605
SYSTEM                         /oradata/ORA19C/system01.dbf                             1150
SYSAUX                         /oradata/ORA19C/sysaux01.dbf                              910
USERS                          /oradata/ORA19C/users02.dbf                           36.5625
SOE                            /oradata/ORA19C/soe01.dbf                                2419

6 rows selected.

SQL>  select file#,name,status,CREATION_TIME from v$datafile;

     FILE# NAME                                          STATUS  CREATION_TIM
---------- --------------------------------------------- ------- ------------
         1 /oradata/ORA19C/system01.dbf                  SYSTEM  17-APR-19
         3 /oradata/ORA19C/sysaux01.dbf                  ONLINE  17-APR-19
         4 /oradata/ORA19C/undotbs01.dbf                 ONLINE  17-APR-19
         5 /oradata/ORA19C/users02.dbf                   ONLINE  14-FEB-24
         7 /oradata/ORA19C/users01.dbf                   ONLINE  17-APR-19
         8 /oradata/ORA19C/soe01.dbf                     ONLINE  22-JAN-24

6 rows selected.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ora19c           OPEN

※ DB가  다운 되지 않았다면 삭제한 datafile offline 후 alter database create datafile  생성 부터 

진행 하면 된다.

출처 :https://hoing.io/archives/268

1. TEST Information

2. Scenario

1) 현재 testdb1 , testdb2 의 SID로 RAC(10.2.0.5) 에서 HOT Backup을 이용하여 clonedb를 생성

2) 복제 되는 instance의 SID는 copydb 로 할 것이며, single 로 복구를 시도 할 것이다.

3) RAC는 /oradata/testdb ,  single 은 /oradata3/copydb 로 복구 할 것이다.

3. HOT BACKUP

■ 테스트를 간편하게 하기 위해 alter database begin backup; 으로 백업 진행한다.

SQL> alter database begin backup;

■ Redo와 Temp Tablespace 파일을 제외한 모든 datafile 을 복사 한다.

$ cp system01.dbf /oradata3/copydb/

$ cp sysaux01.dbf /oradata3/copydb/

$ cp undotbs01.dbf /oradata3/copydb/

$ cp undotbs02.dbf /oradata3/copydb/

$ cp users01.dbf /oradata3/copydb/

■ 복사 후 end backup  을 실행한다.

SQL> alter database end backup;

END Backup 을 실행한 시간을 확인한다.

SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') "Time" from dual;

Time

-------------------

2011-12-04:12:10:09

4. TEST Table Creation

■ 복사 후 복구 테스트를 위해서 테이블과 데이터를 입력한다.

■ 테이블 생성

SQL> create table test01 (no number) tablespace users;

■ 데이터 입력

BEGIN

for i in 1..1000 loop

insert into test01 values(i);

end loop;

commit;

END;

/

■ log switch  및 checkpoint 발생

SQL> alter system switch logfile;   -- 수회 실행

■ test table삭제

SQL> drop table test01 purge;

■ 삭제 시간 확인

SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') "Time" from dual;

Time

-------------------

2011-12-04:12:26:00

5. Ready to clonedb

■ spfile 로 pfile을 생성

SQL> create pfile='$ORACLE_HOME/dbs/initCOPYDB.ora' from spfile;

■ controlfile 재생성을 위해 trace 파일 생성

SQL> alter database backup controlfile to trace as '/oradata3/copydb/recon.sql';

■ pfile 수정

아래 파라미터를 clonedb 환경에 맞게 수정 한다, 물론 디렉토리도 생성을 해야 한다.

*.audit_file_dest=

*.background_dump_dest=

*.control_files='

*.user_dump_dest='

*.db_name='testdb'

RAC 파라미터 변경, 아래와 같이 변경

*.instance_number=1

*.cluster_database=false

*.thread=1

*.undo_tablespace='UNDOTBS1'

■ pfile 수정

create controlfile 절을 수정한다.

CREATE CONTROLFILE SET DATABASE "COPYDB" RESETLOGS  NOARCHIVELOG

   REUSE => SET

  NORESETLOGS => RESETLOGS

그 외 경로를 clonedb에 맞게 수정한다.

복구 하고 open 후 temp tablespace를 생성 함으로 temp tablespace 생성 절을 별도로 백업 해둔다

ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata3/copydb/temp01.dbf'

SIZE 524288000  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

아래와 같이 CHARACTER SET KO16MSWIN949;   까지 유지해서 파일을 작성한다.

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "COPYDB" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 192

    MAXLOGMEMBERS 3

    MAXDATAFILES 1024

    MAXINSTANCES 32

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 (

    '/oradata3/copydb/redo01a.log',

    '/oradata3/copydb/redo01b.log'

  ) SIZE 300M,

  GROUP 2 (

    '/oradata3/copydb/redo02a.log',

    '/oradata3/copydb/redo02b.log'

  ) SIZE 300M,

  GROUP 3 (

    '/oradata3/copydb/redo03a.log',

    '/oradata3/copydb/redo03b.log'

  ) SIZE 300M,

  GROUP 4 (

    '/oradata3/copydb/redo04a.log',

    '/oradata3/copydb/redo04b.log'

  ) SIZE 300M

-- STANDBY LOGFILE

DATAFILE

  '/oradata3/copydb/system01.dbf',

  '/oradata3/copydb/undotbs01.dbf',

  '/oradata3/copydb/sysaux01.dbf',

  '/oradata3/copydb/undotbs02.dbf',

  '/oradata3/copydb/users01.dbf'

CHARACTER SET KO16MSWIN949;

6. Creation clonedb

■ sid를 변경하고 컨트롤 파일을 재생성 한다.

$ export ORACLE_SID=COPYDB

SQL*Plus: Release 10.2.0.5.0 - Production on Sun Dec 4 12:48:19 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance

SQL> @recon.sql

■ 시간 기반으로 복구 한다.

복구를 진행할 세션에서 복구하기 편한 방식으로 시간 설정을 한다.

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

삭제한 시간이 2011-12-04:12:26:00 이기 때문에 24 분으로 복구 하겠다.

SQL> recover database until time '2011-12-04:12:24:00' using backup controlfile;

복구를 실시하면 아래와 같이 아카이브 파일을 필요로 한다.

ORA-00279: change 565796 generated at 12/04/2011 12:01:54 needed for thread 2

ORA-00289: suggestion : /oracle/product/102/db/dbs/arch2_14_768267462.dbf

ORA-00280: change 565796 for thread 2 is in sequence #14

위에서 알 수 있는 것은 thread 2(RAC에서 2번째 노드) 의 시퀀스 14번 을 가진 아카이브를 원하는

것이다 .

테스트 환경에서는 파일명이 arc_2_14_768267462.arc  이며, 경로 및 파일을 입력한다.

파일명 입력

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/oradata/arch_testdb/arc_2_14_768267462.arc

이번에는 thread 1의 change 565796 을 포함 한 아카이브 파일을 입력 해야 한다.

ORA-00279: change 565796 generated at  needed for thread 1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

RAC 로 접속해서 쿼리를 수행하여 THREAD1의 565796 변경본이 포함된 아카이브 파일을 찾는다.

SQL> set lines 500

SQL> col name for a50

SQL> SELECT THREAD# ,SEQUENCE# , FIRST_CHANGE#, NEXT_CHANGE#, NAME,

TO_CHAR(FIRST_TIME,'YYYY-MM-DD:HH24:MI:SS') FIRST_TIME  FROM V$ARCHIVED_LOG;

THREAD#  SEQUENCE  # FIRST_CHANGE   # NEXT_CHANGE#      NAME                                   FIRST_TIME

----------  ---------- ------------- ------------ -------------------------------------------------------------------------------

  2          14       553454              567190      /oradata/arch_testdb/arc_2_14_768267462.arc    2011-12-04:05:52:04

  2          15       567190              567247      /oradata/arch_testdb/arc_2_15_768267462.arc    2011-12-04:12:20:05

  2          16        567247             567249      /oradata/arch_testdb/arc_2_16_768267462.arc    2011-12-04:12:20:09

  1          11       553456              567252    /oradata/arch_testdb/arc_1_11_768267462.arc   2011-12-04:05:52:05

  2          17       567249              567254      /oradata/arch_testdb/arc_2_17_768267462.arc    2011-12-04:12:20:12

  2          18       567254              567274      /oradata/arch_testdb/arc_2_18_768267462.arc    2011-12-04:12:20:15

  2          19       567274              567279      /oradata/arch_testdb/arc_2_19_768267462.arc    2011-12-04:12:21:12

  1          12       567252              567293      /oradata/arch_testdb/arc_1_12_768267462.arc    2011-12-04:12:20:14

확인 해 보면 arc_1_11_768267462.arc 파일인 것을 알 수 있다. 아래와 같이 경로와 파일명을 입력한다.

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/oradata/arch_testdb/arc_1_11_768267462.arc

ORA-00279: change 567190 generated at 12/04/2011 12:20:05 needed for thread 2

ORA-00289: suggestion : /oracle/product/102/db/dbs/arch2_15_768267462.dbf

ORA-00280: change 567190 for thread 2 is in sequence #15

ORA-00278: log file '/oradata/arch_testdb/arc_2_14_768267462.arc' no longer

needed for this recovery

change 567190 for thread 2 is in sequence #15 메세지를 보면 THREAD2의 15 시퀀스 아카이브 파일을

필요로 한다는 것을 알 수 있다. 경로 및 파일명을 입력한다.

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/oradata/arch_testdb/arc_2_15_768267462.arc

위와 같은 패턴으로 요청하는 아카이브 파일을 계속 적용해준다.

운영중인 RAC에 생성 된 아카이브 보다 더 높은 시퀀스를 요구 한다면 로그 스위치를 발생하여

생성한 아카이브를 적용 시켜준다.

계속 적용 시키면 아래와 같이 recovery가 되었다는 메세지를 볼 수 있다.

Log applied.

Media recovery complete.

resetlogs로 instance를 open한다

SQL> alter database open resetlogs;

Temp Tablespace 를 추가한다.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata3/copydb/temp01.dbf'

SIZE 524288000  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

■ 예외 처리

아래와 같이 ORA-38856 가 나오게 되면 Unpulbished Bug-4355382 로써

오라클 문서ID: 334899.1 를 참조한다.

ERROR at line 1:

ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

pfile에 _no_recovery_through_resetlogs=TRUE  를 추가한 후 다시 resetlogs로 open한다.

open 후 test01 테이블이 복구 되었는지 조회해 본다.

select count(*) from test01;

  COUNT(*)

----------

      1000