이상한금요일의 IT세상
블로그 이미지

Home

Write

Setting

Velog
Dark
📁 Database/Oracle

[Oracle] 19c RMAN 복구 : RAC ASM to Single Filesystem

이상한금요일
|2024. 8. 21. 18:16
728x90

1. 운영 DB(RAC, Source DB)

1) Source DB 상태 확인

인스턴스 확인

SQL> 
select instance_name , version, status from gv$instance;

INSTANCE_NAME    VERSION           STATUS
---------------- ----------------- ------------
ORCL1            19.0.0.0.0        OPEN
ORCL2            19.0.0.0.0        OPEN


아카이브 모드 확인

SQL> 
archive log list

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +RECO
Oldest online log sequence     18
Next log sequence to archive   19
Current log sequence           19


데이터 파일 확인

SQL> 
col file_name for a70

select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ----------------------------------------------------------------------
SYSTEM                         +DATA/ORCL/DATAFILE/system.257.1177172145
SYSAUX                         +DATA/ORCL/DATAFILE/sysaux.258.1177172149
UNDOTBS1                       +DATA/ORCL/DATAFILE/undotbs1.259.1177172151
UNDOTBS2                       +DATA/ORCL/DATAFILE/undotbs2.261.1177172159
USERS                          +DATA/ORCL/DATAFILE/users.262.1177514657

 

temp 파일 확인

SQL> 
col file_name for a70

select tablespace_name, file_name from dba_temp_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ----------------------------------------------------------------------
TEMP                           +DATA/ORCL/TEMPFILE/temp.260.1177172151

 

리두 로그 파일 확인

SQL> 
col member for a55

select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
from v$logfile a, v$log b
where a.group#=b.group#
order by 1, 2;

    GROUP# MEMBER                                                          MB ARC STATUS
---------- ------------------------------------------------------- ---------- --- ----------------
         1 +RECO/ORCL/ONLINELOG/group_1.257.1177172141                    200 NO  CURRENT
         2 +RECO/ORCL/ONLINELOG/group_2.258.1177172143                    200 YES INACTIVE
         3 +RECO/ORCL/ONLINELOG/group_3.259.1177174155                    200 YES INACTIVE
         4 +RECO/ORCL/ONLINELOG/group_4.260.1177174155                    200 NO  CURRENT

2) 데이터 생성

테이블 스페이스 생성

SQL>
create tablespace test2 datafile size 5G;


테이블 스페이스 확인 

SQL>
select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ----------------------------------------------------------------------
SYSTEM                         +DATA/ORCL/DATAFILE/system.257.1177172145
SYSAUX                         +DATA/ORCL/DATAFILE/sysaux.258.1177172149
UNDOTBS1                       +DATA/ORCL/DATAFILE/undotbs1.259.1177172151
UNDOTBS2                       +DATA/ORCL/DATAFILE/undotbs2.261.1177172159
USERS                          +DATA/ORCL/DATAFILE/users.262.1177514657
TEST2                          +DATA/ORCL/DATAFILE/test2.264.1177593813


유저 생성 및 권한부여 

SQL> 
create user test2 identified by test2
default tablespace test2 quota unlimited on test2;

SQL> 
grant connect, resource to test2;


데이터 생성 

SQL>
conn test2/test2

SQL>
create table tb_test2(c1 number, c2 number);

SQL>
begin
  for i in 1 .. 10000
  loop
    insert into tb_test2 values(i, i+1);
  end loop;
end;
/

SQL>
commit;

 

데이터 건수 확인 

SQL>
select count(*) from tb_test2;

  COUNT(*)
----------
     10000


현재 시간 확인 

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

DT
-------------------
2024-08-21 13:24:58

3) RMAN 사전 설정

백업 경로 지정

$ mkdir -p /home/oracle/rman
$ rman target /

RMAN> configure channel device type disk format '/home/oracle/rman/db_%U_%T';

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/home/oracle/rman/db_%U_%T';
new RMAN configuration parameters are successfully stored


컨트롤파일 자동 백업 옵션 및 경로 지정 

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/rman/db_control_%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/rman/db_control_%F';
new RMAN configuration parameters are successfully stored


rman 설정 확인 

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/rman/db_control_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/home/oracle/rman/db_%U_%T';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/19.3/db/dbs/snapcf_ORCL1.f'; # default


백업 실행 

RMAN> backup database;

Starting backup at 21-AUG-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1664 instance=ORCL1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/ORCL/DATAFILE/test2.264.1177593813
input datafile file number=00002 name=+DATA/ORCL/DATAFILE/sysaux.258.1177172149
input datafile file number=00001 name=+DATA/ORCL/DATAFILE/system.257.1177172145
input datafile file number=00003 name=+DATA/ORCL/DATAFILE/undotbs1.259.1177172151
input datafile file number=00004 name=+DATA/ORCL/DATAFILE/undotbs2.261.1177172159
input datafile file number=00005 name=+DATA/ORCL/DATAFILE/users.262.1177514657
channel ORA_DISK_1: starting piece 1 at 21-AUG-24
channel ORA_DISK_1: finished piece 1 at 21-AUG-24
piece handle=/home/oracle/rman/db_0i331a33_18_1_1_20240821 tag=TAG20240821T132555 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 21-AUG-24

Starting Control File and SPFILE Autobackup at 21-AUG-24
piece handle=/home/oracle/rman/db_control_c-1704952876-20240821-02 comment=NONE
Finished Control File and SPFILE Autobackup at 21-AUG-24


백업 본 확인 

$ ls -al /home/oracle/rman
total 1696740
drwxr-xr-x  2 oracle dba         84 Aug 21 13:26 .
drwx------. 9 oracle dba       4096 Aug 21 13:25 ..
-rw-r-----  1 oracle dba 1717633024 Aug 21 13:26 db_0i331a33_18_1_1_20240821
-rw-r-----  1 oracle dba   19824640 Aug 21 13:26 db_control_c-1704952876-20240821-02

 

 

일부 데이터 삭제(이 시점으로 복구 예정) 

SQL>
conn test2/test2

SQL>
delete tb_test2 where rownum <= 5000;

SQL>
commit;


데이터 건수 확인(이 시점으로 복구 예정) 

SQL>
select count(*) from tb_test2;

  COUNT(*)
----------
      5000


로그 스위치

SQL>
alter system switch logfile;
/
/
/


현재 시간 확인(이 시점으로 복구 예정)

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

DT
-------------------
2024-08-21 13:29:10

4) 장애 발생

몇 분 기다렸다가 

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

DT
-------------------
2024-08-21 13:55:24


데이터 추가 삽입 

SQL>
conn test2/test2

SQL>
begin
  for i in 1 .. 2500
  loop
    insert into tb_test2 values(i, i+1);
  end loop;
end;
/

SQL>
commit;


데이터 건수 확인 

SQL>
select count(*) from tb_test2;

  COUNT(*)
----------
      7500


로그 스위치 

SQL>
alter system switch logfile;
/
/
/


현재 시간 확인 

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

DT
-------------------
2024-08-21 14:01:36


pfile 생성 

SQL>
create pfile from spfile;

2. 개발 DB(Single, Target DB)

  • 복구 시점 : 2024-08-21 13:29:10
  • 데이터 건수 : 5000건

1) Source DB에서 Target DB로 필요 파일 전송

pfile 복제(운영 -> 개발)


pfile 수정(RAC -> Single)

(변경 전)

$ cat initORCL1.ora -> initCLONE.ora로 수정

ORCL1.__data_transfer_cache_size=0
ORCL2.__data_transfer_cache_size=0
ORCL1.__db_cache_size=1879048192
ORCL2.__db_cache_size=2013265920
ORCL1.__inmemory_ext_roarea=0
ORCL2.__inmemory_ext_roarea=0
ORCL1.__inmemory_ext_rwarea=0
ORCL2.__inmemory_ext_rwarea=0
ORCL1.__java_pool_size=0
ORCL2.__java_pool_size=0
ORCL1.__large_pool_size=16777216
ORCL2.__large_pool_size=16777216
ORCL1.__oracle_base='/oracle'#ORACLE_BASE set from environment
ORCL2.__oracle_base='/oracle'#ORACLE_BASE set from environment
ORCL1.__pga_aggregate_target=1056964608
ORCL2.__pga_aggregate_target=1056964608
ORCL1.__sga_target=3154116608
ORCL2.__sga_target=3154116608
ORCL1.__shared_io_pool_size=134217728
ORCL2.__shared_io_pool_size=134217728
ORCL1.__shared_pool_size=1107296256
ORCL2.__shared_pool_size=973078528
ORCL1.__streams_pool_size=0
ORCL2.__streams_pool_size=0
ORCL1.__unified_pga_pool_size=0
ORCL2.__unified_pga_pool_size=0
*.audit_file_dest='/oracle/admin/ORCL/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+RECO/ORCL/CONTROLFILE/current.256.1177172141'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+RECO'
*.db_name='ORCL'
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
family:dw_helper.instance_mode='read-only'
ORCL2.instance_number=2
ORCL1.instance_number=1
*.local_listener='-oraagent-dummy-'
*.log_archive_dest_1='LOCATION=+RECO'
*.log_archive_format='%t_%s_%r.arc'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1000m
*.processes=2000
*.remote_login_passwordfile='exclusive'
*.sga_target=3000m
ORCL2.thread=2
ORCL1.thread=1
*.undo_tablespace='UNDOTBS1'
ORCL1.undo_tablespace='UNDOTBS1'
ORCL2.undo_tablespace='UNDOTBS2'

 

(변경 후)

$ cat initCLONE.ora

*.audit_file_dest='/oracle/admin/CLONE/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/orawork/backtest/control01.ctl'
*.db_block_size=8192
*.db_name='ORCL'
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLONEXDB)'
*.local_listener='LISTENER_CLONE'
*.log_archive_dest_1='LOCATION=/oraarch/CLONE'
*.log_archive_format='%t_%s_%r.arc'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1000m
*.processes=2000
*.remote_login_passwordfile='exclusive'
*.sga_target=3000m
*.undo_tablespace='UNDOTBS1'

 

필요한 디렉토리 생성

rman 디렉토리는 운영 DB에 백업 받은 경로랑 동일해야 함

$ 
mkdir -p /oracle/admin/CLONE/adump
mkdir -p /oraarch/CLONE
mkdir -p /oradata/CLONE
mkdir -p /home/oracle/rman


Target DB 서버로 rman 백업 파일 전송 

$ ls -al /orawork/backtest
total 1696736
drwxr-xr-x. 2 oracle dba         84 Aug 21 14:26 .
drwxr-xr-x. 3 oracle dba         22 Aug 21 12:56 ..
-rw-r--r--. 1 oracle dba 1717633024 Aug 21 14:26 db_0i331a33_18_1_1_20240821
-rw-r--r--. 1 oracle dba   19824640 Aug 21 14:26 db_control_c-1704952876-20240821-02


Source DB에서 아카이브 로그 확인 

ASMCMD> cd +RECO
ASMCMD> ls -l
Type  Redund  Striped  Time  Sys  Name
                             Y    ORCL/
ASMCMD> cd ORCL
ASMCMD> ls -l
Type  Redund  Striped  Time  Sys  Name
                             Y    ARCHIVELOG/
                             Y    CONTROLFILE/
                             Y    ONLINELOG/
ASMCMD> cd ARCHIVELOG
ASMCMD> ls -l
Type  Redund  Striped  Time  Sys  Name
                             Y    2024_08_21/
ASMCMD> cd 2024_08_21/
ASMCMD> ls -l
Type        Redund  Striped  Time             Sys  Name
ARCHIVELOG  UNPROT  COARSE   AUG 21 13:00:00  Y    thread_1_seq_18.271.1177592407
ARCHIVELOG  UNPROT  COARSE   AUG 21 13:00:00  Y    thread_1_seq_19.269.1177594113
ARCHIVELOG  UNPROT  COARSE   AUG 21 13:00:00  Y    thread_1_seq_20.267.1177594115
ARCHIVELOG  UNPROT  COARSE   AUG 21 13:00:00  Y    thread_1_seq_21.261.1177594117
ARCHIVELOG  UNPROT  COARSE   AUG 21 13:00:00  Y    thread_1_seq_22.270.1177594121
ARCHIVELOG  UNPROT  COARSE   AUG 21 13:00:00  Y    thread_1_seq_23.268.1177594133
ARCHIVELOG  UNPROT  COARSE   AUG 21 13:00:00  Y    thread_1_seq_24.265.1177594137
ARCHIVELOG  UNPROT  COARSE   AUG 21 14:00:00  Y    thread_1_seq_25.263.1177596077
ARCHIVELOG  UNPROT  COARSE   AUG 21 14:00:00  Y    thread_1_seq_26.262.1177596081
ARCHIVELOG  UNPROT  COARSE   AUG 21 14:00:00  Y    thread_1_seq_27.273.1177596085
ARCHIVELOG  UNPROT  COARSE   AUG 21 14:00:00  Y    thread_1_seq_28.274.1177596091
ARCHIVELOG  UNPROT  COARSE   AUG 21 14:00:00  Y    thread_1_seq_29.275.1177596091
ARCHIVELOG  UNPROT  COARSE   AUG 21 14:00:00  Y    thread_1_seq_30.276.1177596093
ARCHIVELOG  UNPROT  COARSE   AUG 21 13:00:00  Y    thread_2_seq_6.264.1177594115
ARCHIVELOG  UNPROT  COARSE   AUG 21 13:00:00  Y    thread_2_seq_7.266.1177594135
ARCHIVELOG  UNPROT  COARSE   AUG 21 14:00:00  Y    thread_2_seq_8.272.1177596083
ARCHIVELOG  UNPROT  COARSE   AUG 21 14:00:00  Y    thread_2_seq_9.277.1177596095


Source DB 내에서 ASM에서 FileSystem 영역으로 복사

ASMCMD> cp thread_1_* /home/grid/
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_1_seq_18.271.1177592407 -> /home/grid//thread_1_seq_18.271.1177592407
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_1_seq_19.269.1177594113 -> /home/grid//thread_1_seq_19.269.1177594113
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_1_seq_20.267.1177594115 -> /home/grid//thread_1_seq_20.267.1177594115
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_1_seq_21.261.1177594117 -> /home/grid//thread_1_seq_21.261.1177594117
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_1_seq_22.270.1177594121 -> /home/grid//thread_1_seq_22.270.1177594121
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_1_seq_23.268.1177594133 -> /home/grid//thread_1_seq_23.268.1177594133
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_1_seq_24.265.1177594137 -> /home/grid//thread_1_seq_24.265.1177594137
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_1_seq_25.263.1177596077 -> /home/grid//thread_1_seq_25.263.1177596077
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_1_seq_26.262.1177596081 -> /home/grid//thread_1_seq_26.262.1177596081
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_1_seq_27.273.1177596085 -> /home/grid//thread_1_seq_27.273.1177596085
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_1_seq_28.274.1177596091 -> /home/grid//thread_1_seq_28.274.1177596091
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_1_seq_29.275.1177596091 -> /home/grid//thread_1_seq_29.275.1177596091
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_1_seq_30.276.1177596093 -> /home/grid//thread_1_seq_30.276.1177596093
ASMCMD> cp thread_2_* /home/grid/
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_2_seq_6.264.1177594115 -> /home/grid//thread_2_seq_6.264.1177594115
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_2_seq_7.266.1177594135 -> /home/grid//thread_2_seq_7.266.1177594135
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_2_seq_8.272.1177596083 -> /home/grid//thread_2_seq_8.272.1177596083
copying +RECO/ORCL/ARCHIVELOG/2024_08_21/thread_2_seq_9.277.1177596095 -> /home/grid//thread_2_seq_9.277.1177596095


Target DB 서버로 아카이브 로그 전송

본 테스트에서는 모든 아카이브 로그를 전송했지만 복구에 필요한 시점까지의 아카이브 로그만 필요

# ls -al /oraarch/CLONE
total 345928
drwxr-xr-x. 2 oracle dba      4096 Aug 21 14:40 .
drwxr-xr-x. 4 oracle dba        31 Aug 21 14:21 ..
-rw-r--r--. 1 oracle dba 186444288 Aug 21 14:40 thread_1_seq_18.271.1177592407
-rw-r--r--. 1 oracle dba   4644864 Aug 21 14:40 thread_1_seq_19.269.1177594113
-rw-r--r--. 1 oracle dba      6144 Aug 21 14:40 thread_1_seq_20.267.1177594115
-rw-r--r--. 1 oracle dba      3072 Aug 21 14:40 thread_1_seq_21.261.1177594117
-rw-r--r--. 1 oracle dba      3072 Aug 21 14:40 thread_1_seq_22.270.1177594121
-rw-r--r--. 1 oracle dba      3584 Aug 21 14:40 thread_1_seq_23.268.1177594133
-rw-r--r--. 1 oracle dba      2560 Aug 21 14:40 thread_1_seq_24.265.1177594137
-rw-r--r--. 1 oracle dba   5950464 Aug 21 14:40 thread_1_seq_25.263.1177596077
-rw-r--r--. 1 oracle dba     34816 Aug 21 14:40 thread_1_seq_26.262.1177596081
-rw-r--r--. 1 oracle dba      6144 Aug 21 14:40 thread_1_seq_27.273.1177596085
-rw-r--r--. 1 oracle dba      2560 Aug 21 14:40 thread_1_seq_28.274.1177596091
-rw-r--r--. 1 oracle dba      3584 Aug 21 14:40 thread_1_seq_29.275.1177596091
-rw-r--r--. 1 oracle dba      3072 Aug 21 14:40 thread_1_seq_30.276.1177596093
-rw-r--r--. 1 oracle dba 151692800 Aug 21 14:40 thread_2_seq_6.264.1177594115
-rw-r--r--. 1 oracle dba      1536 Aug 21 14:40 thread_2_seq_7.266.1177594135
-rw-r--r--. 1 oracle dba   5235200 Aug 21 14:40 thread_2_seq_8.272.1177596083
-rw-r--r--. 1 oracle dba    160256 Aug 21 14:40 thread_2_seq_9.277.1177596095


Source DB 파일 권한 변경(oracle:dba)

백업셋, 컨트롤 파일, 아카이브 로그 파일 

# 
chown -R oracle:dba /oraarch
chown -R oracle:dba /orawork


Source DB  nomount 기동 

$ export ORACLE_SID=CLONE
$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 21 14:41:57 2024
Version 19.24.0.0.0

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3154115504 bytes
Fixed Size                  8944560 bytes
Variable Size             637534208 bytes
Database Buffers         2499805184 bytes
Redo Buffers                7831552 bytes


컨트롤 파일 restore(복원)

$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Aug 21 14:45:13 2024
Version 19.24.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (not mounted)

RMAN> restore controlfile from '/orawork/backtest/db_control_c-1704952876-20240821-02';

Starting restore at 21-AUG-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1597 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/orawork/backtest/control01.ctl
Finished restore at 21-AUG-24


db mount(db name이 운영과 동일하므로 mount까지 올라감) 

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1


RMAN 스크립트 작성을 위해 datafile 번호 확인 

RMAN> report schema;

RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     +DATA/ORCL/DATAFILE/system.257.1177172145
2    0        SYSAUX               ***     +DATA/ORCL/DATAFILE/sysaux.258.1177172149
3    0        UNDOTBS1             ***     +DATA/ORCL/DATAFILE/undotbs1.259.1177172151
4    0        UNDOTBS2             ***     +DATA/ORCL/DATAFILE/undotbs2.261.1177172159
5    0        USERS                ***     +DATA/ORCL/DATAFILE/users.262.1177514657
6    0        TEST2                ***     +DATA/ORCL/DATAFILE/test2.264.1177593813

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/ORCL/TEMPFILE/temp.260.1177172151


데이터 파일 경로 변경

백업셋이 rman 백업 경로에 위치해 있어야 함

users 테이블스페이스를 제외한 나머지 복원

RMAN> run {
set newname for tempfile 1 to '/oradata/CLONE/temp01.dbf';
set newname for datafile 1 to '/oradata/CLONE/system01.dbf';
set newname for datafile 2 to '/oradata/CLONE/sysaux01.dbf';
set newname for datafile 3 to '/oradata/CLONE/undotbs01.dbf';
set newname for datafile 4 to '/oradata/CLONE/undotbs02.dbf';
set newname for datafile 6 to '/oradata/CLONE/test201.dbf';

restore datafile 1,2,3,4,6;

switch datafile all;
switch tempfile all;
}

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 21-AUG-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1604 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oradata/CLONE/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oradata/CLONE/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata/CLONE/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata/CLONE/undotbs02.dbf
channel ORA_DISK_1: restoring datafile 00006 to /oradata/CLONE/test201.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/db_0i331a33_18_1_1_20240821
channel ORA_DISK_1: piece handle=/home/oracle/rman/db_0i331a33_18_1_1_20240821 tag=TAG20240821T132555
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 21-AUG-24

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=1177599834 file name=/oradata/CLONE/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=1177599834 file name=/oradata/CLONE/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=1177599834 file name=/oradata/CLONE/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=1177599834 file name=/oradata/CLONE/undotbs02.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=11 STAMP=1177599834 file name=/oradata/CLONE/test201.dbf

renamed tempfile 1 to /oradata/CLONE/temp01.dbf in control file


컨트롤파일 재생성

SQL> 
alter database backup controlfile to trace as '/home/oracle/recon.sql';

 

DB 종료 

SQL> 
shutdown immediate


pfile 수정(db_name을 CLONE로 변경) 

$ vi $ORACLE_HOME/dbs/initCLONE.ora
*.db_name='CLONE'


컨트롤파일 내용 수정

REUSE -> SET로 변경

"CLONE" RESETLOGS 로 변경

리두 로그 파일 및 데이터 파일 경로 수정

$ cat /home/oracle/recon.sql
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oradata/CLONE/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/oradata/CLONE/redo02.log'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oradata/CLONE/system01.dbf',
  '/oradata/CLONE/sysaux01.dbf',
  '/oradata/CLONE/undotbs01.dbf',
  '/oradata/CLONE/undotbs02.dbf',
  '/oradata/CLONE/test201.dbf'
CHARACTER SET AL32UTF8
;

VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/home/oracle/rman/db_%U_%T''');
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/home/oracle/rman/db_control_%F''');


기존 컨트롤파일 제거 

$ rm /orawork/backtest/control01.ctl


recon.sql 실행 

SQL> 
@/home/oracle/recon.sql

ORACLE instance started.

Total System Global Area 3154115504 bytes
Fixed Size                  8944560 bytes
Variable Size             637534208 bytes
Database Buffers         2499805184 bytes
Redo Buffers                7831552 bytes

Control file created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


인스턴스 이름, 상태, db name 확인

변경된 db name 및 mount 상태 확인

SQL> 
select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
CLONE            MOUNTED

SQL> 
select name from v$database;

NAME
---------
CLONE


복구 진행(시점 복구)

SQL> 
recover database until time '2024-08-21 13:29:10' using backup controlfile;
ORA-00279: change 1397162 generated at 08/21/2024 13:28:33 needed for thread 1
ORA-00289: suggestion : /oraarch/CLONE/1_20_1177172140.arc
ORA-00280: change 1397162 for thread 1 is in sequence #20


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oraarch/CLONE/thread_1_seq_20.267.1177594115
ORA-00279: change 1397162 generated at 08/20/2024 15:27:04 needed for thread 2
ORA-00289: suggestion : /oraarch/CLONE/2_6_1177172140.arc
ORA-00280: change 1397162 for thread 2 is in sequence #6


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oraarch/CLONE/thread_2_seq_6.264.1177594115
ORA-00279: change 1397171 generated at 08/21/2024 13:28:34 needed for thread 1
ORA-00289: suggestion : /oraarch/CLONE/1_21_1177172140.arc
ORA-00280: change 1397171 for thread 1 is in sequence #21
ORA-00278: log file '/oraarch/CLONE/thread_1_seq_20.267.1177594115' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oraarch/CLONE/thread_1_seq_21.261.1177594117
ORA-00279: change 1397178 generated at 08/21/2024 13:28:34 needed for thread 2
ORA-00289: suggestion : /oraarch/CLONE/2_7_1177172140.arc
ORA-00280: change 1397178 for thread 2 is in sequence #7
ORA-00278: log file '/oraarch/CLONE/thread_2_seq_6.264.1177594115' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oraarch/CLONE/thread_2_seq_7.266.1177594135
ORA-00279: change 1397181 generated at 08/21/2024 13:28:37 needed for thread 1
ORA-00289: suggestion : /oraarch/CLONE/1_22_1177172140.arc
ORA-00280: change 1397181 for thread 1 is in sequence #22
ORA-00278: log file '/oraarch/CLONE/thread_1_seq_21.261.1177594117' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oraarch/CLONE/thread_1_seq_22.270.1177594121
ORA-00279: change 1397187 generated at 08/21/2024 13:28:40 needed for thread 1
ORA-00289: suggestion : /oraarch/CLONE/1_23_1177172140.arc
ORA-00280: change 1397187 for thread 1 is in sequence #23
ORA-00278: log file '/oraarch/CLONE/thread_1_seq_22.270.1177594121' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oraarch/CLONE/thread_1_seq_23.268.1177594133
ORA-00279: change 1397215 generated at 08/21/2024 13:28:52 needed for thread 1
ORA-00289: suggestion : /oraarch/CLONE/1_24_1177172140.arc
ORA-00280: change 1397215 for thread 1 is in sequence #24
ORA-00278: log file '/oraarch/CLONE/thread_1_seq_23.268.1177594133' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oraarch/CLONE/thread_1_seq_24.265.1177594137
ORA-00279: change 1397223 generated at 08/21/2024 13:28:55 needed for thread 2
ORA-00289: suggestion : /oraarch/CLONE/2_8_1177172140.arc
ORA-00280: change 1397223 for thread 2 is in sequence #8
ORA-00278: log file '/oraarch/CLONE/thread_2_seq_7.266.1177594135' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

 

DB open(버그로 인한 에러 발생) 

SQL> 
alter database open resetlogs;

alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled


버그 해결을 위한 히든파라미터 적용 

SQL>
alter system set "_no_recovery_through_resetlogs"=true;


DB open 재시도 

SQL> 
alter database open resetlogs;


데이터 5000건 확인

SQL> 

conn test2/test2

SQL>

select count(*) from tb_test2;

  COUNT(*)
----------
      5000

 

temp tablespace 추가

SQL> 
alter tablespace temp add tempfile '/oradata/CLONE/temp01.dbf' size 100m;

 

3) users 테이블스페이스 재생성

users 테이블스페이스가 현재 복구 대상에서 제외했기 때문에 missing 상태로 남아있음.


해당 테이블스페이스가 default 테이블스페이스이므로 바로 삭제되지 않아서
임시 테이블스페이스(users2)를 만든 뒤 임시 테이블스페이스(users2)를 default 테이블스페이스로 지정하고
기존 users 테이블스페이스를 지운 뒤 새로 users 테이블스페이스를 만들고 다시 default 테이블스페이스로 지정하고
임시 테이블스페이스를 지우는 절차를 진행함.

 

테이블스페이스 상태 확인

SQL> 
set lines 300 pages 200
col file_name for a55

select tablespace_name, file_name, online_status from dba_data_files;

TABLESPACE_NAME                FILE_NAME                                               ONLINE_
------------------------------ ------------------------------------------------------- -------
SYSTEM                         /oradata/CLONE/system01.dbf                             SYSTEM
SYSAUX                         /oradata/CLONE/sysaux01.dbf                             ONLINE
UNDOTBS1                       /oradata/CLONE/undotbs01.dbf                            ONLINE
UNDOTBS2                       /oradata/CLONE/undotbs02.dbf                            ONLINE
USERS                          /oracle/product/19.3/db/dbs/MISSING00005                RECOVER
TEST2                          /oradata/CLONE/test201.dbf                              ONLINE

 

USERS 테이블스페이스 제거시도 - 실패함

SQL> 
drop tablespace users including contents and datafiles;

drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

 

임시 테이블스페이스(users2) 생성 

SQL> 
create tablespace users2 datafile '/oradata/CLONE/users02.dbf' size 10m;

     

Default 테이블스페이스를 임시 테이블스페이스(users2)로 지정

SQL> 
alter database default tablespace users2;

 

기존 users 테이블스페이스 제거 

SQL> 
drop tablespace users including contents and datafiles;

 

users 테이블스페이스 재생성 

SQL> 
create tablespace users datafile '/oradata/CLONE/users01.dbf' size 10m autoextend on;

 

Default 테이블스페이스를 users 테이블스페이스로 지정

SQL> 
alter database default tablespace users;

 

임시 테이블스페이스(users2) 제거 

SQL> 
drop tablespace users2 including contents and datafiles;


테이블스페이스 상태 확인 

SQL> 
select tablespace_name, file_name, online_status from dba_data_files;

TABLESPACE_NAME                FILE_NAME                                               ONLINE_
------------------------------ ------------------------------------------------------- -------
SYSTEM                         /oradata/CLONE/system01.dbf                             SYSTEM
SYSAUX                         /oradata/CLONE/sysaux01.dbf                             ONLINE
UNDOTBS1                       /oradata/CLONE/undotbs01.dbf                            ONLINE
UNDOTBS2                       /oradata/CLONE/undotbs02.dbf                            ONLINE
USERS                          /oradata/CLONE/users01.dbf                              ONLINE
TEST2                          /oradata/CLONE/test201.dbf                              ONLINE

 

참고) 컨트롤 파일 재생성 없이 복구

쓰지 않는 데이터 파일 offline drop

SQL> 
alter database datafile '+DATA/ORCL/DATAFILE/users.262.1177514657' offline drop;

 

리두 로그 파일 rename

Source DB에 총 로그 그룹 4개가 있었으며

1, 2 그룹은 1번 노드

3, 4 그룹은 2번 노드

SQL> 
alter database rename file '+RECO/ORCL/ONLINELOG/group_1.257.1177172141' to '/orawork/backtest/redo01.log';
alter database rename file '+RECO/ORCL/ONLINELOG/group_2.258.1177172143' to '/orawork/backtest/redo02.log';

 

사용하지 않는 리두 로그 그룹 및 멤버 삭제

SQL> 
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile member '+RECO/ORCL/ONLINELOG/group_3.259.1177174155';
alter database drop logfile member '+RECO/ORCL/ONLINELOG/group_4.260.1177174155';

 

복구 진행(시점 복구)

SQL> 
recover database until time '2024-08-21 13:29:10' using backup controlfile;

 

DB 종료 및 open

SQL> 
shutdown immediate;

SQL> 
startup mount;

SQL> 
alter database open resetlogs;

 

참조

 

Oracle 19c rac to single clone db 생성 가이드

OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.6.0.0 RAC, 19.10.0.0 SINGLE 방법 : 오라클 19c rac to single clone db 생성 가이드 RAC DB(asm)에서 특정시점의 데이터를 SINGLE DB(filesystem)로 복구하는 내용 특정

positivemh.tistory.com

 

728x90

'📁 Database > Oracle' 카테고리의 다른 글

[Oracle] RMAN DataFile 완전 복구 : Tablespace Offline(ASM 사용 환경)  (0) 2024.09.02
[Oracle] Exadata 핵심 기술요소  (0) 2024.08.27
[Oracle] RMAN DataFile 완전 복구 : Tablespace Offline 불가능 시  (0) 2024.08.20
[Oracle] 19c AutoTask 조회 쿼리  (0) 2024.08.20
[Oracle] 히든 파라미터 조회 쿼리  (0) 2024.08.20

티스토리툴바