내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
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)로 복구하는 내용
특정 테이블스페이스(users tablespace)는 복구대상에서 제외함
dbname을 변경, RAC DB를 SINGLE DB로 변경, asm을 filesystem으로 변경
source db : RAC(asm)
target db(clone) : SINGLE(filesystem)
시나리오
source db rac 확인
|
1
2
3
4
5
6
|
SQL> select instance_name , version, status from gv$instance;
INSTANCE_NAME VERSION STATUS
---------------- ----------------- ------------
ORADB1 19.0.0.0.0 OPEN
ORADB2 19.0.0.0.0 OPEN
|
아카이브 모드 확인
|
1
2
3
4
5
6
7
|
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ora_arch
Oldest online log sequence 9
Next log sequence to archive 10
Current log sequence 10
|
기존 테이블 스페이스 위치 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
col file_name for a70
select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------------------------------------------------------
SYSTEM +DATA/ORADB/DATAFILE/system.257.1034734733
SYSAUX +DATA/ORADB/DATAFILE/sysaux.258.1034734797
UNDOTBS2 +DATA/ORADB/DATAFILE/undotbs2.265.1034736607
USERS +DATA/ORADB/DATAFILE/users.260.1034734835
UNDOTBS1 +DATA/ORADB/DATAFILE/undotbs1.259.1034734833
|
ASM 사용중
테이블 스페이스 생성 및 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> create tablespace bokgu datafile size 1g;
Tablespace created.
SQL> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------------------------------------------------------
SYSTEM +DATA/ORADB/DATAFILE/system.257.1034734733
SYSAUX +DATA/ORADB/DATAFILE/sysaux.258.1034734797
UNDOTBS2 +DATA/ORADB/DATAFILE/undotbs2.265.1034736607
USERS +DATA/ORADB/DATAFILE/users.260.1034734835
BOKGU +DATA/ORADB/DATAFILE/bokgu.269.1096729085
UNDOTBS1 +DATA/ORADB/DATAFILE/undotbs1.259.1034734833
6 rows selected.
|
유저 생성 및 권한부여
|
1
2
3
4
5
6
7
|
SQL> create user bokgu_user identified by bokgu_user account unlock default tablespace bokgu quota unlimited on bokgu;
User created.
SQL> grant resource, connect to bokgu_user;
User created.
|
데이터 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
conn bokgu_user/bokgu_user;
create table bokgu_table (c1 number, c2 number);
begin
for i in 1..10000
loop
insert into bokgu_table values
(i, i+i);
end loop;
end;
/
commit;
|
데이터 건수 확인
|
1
2
3
4
5
|
SQL> select count(*) from bokgu_table;
COUNT(*)
----------
10000
|
데이터가 해당 tablespace 에 잘 들어갔는지 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
conn / as sysdba
col owner for a20
col segment_name for a20
col tablespace_name for a10
select owner, tablespace_name, segment_name, segment_type
from dba_segments
where owner ='BOKGU_USER';
OWNER TABLESPACE SEGMENT_NAME SEGMENT_TYPE
-------------------- ---------- -------------------- ------------------
BOKGU_USER BOKGU BOKGU_TABLE TABLE
|
현재 시간 확인
|
1
2
3
4
5
|
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') dt from dual;
DT
-------------------
2022-02-15 14:59:59
|
rman 사전설정
백업 경로 지정
|
1
2
3
4
5
6
|
$ mkdir -p /home/oracle/rman/
$ rman target /
RMAN> configure channel device type disk format '/home/oracle/rman/db_%U_%T';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/rman/db_%U_%T';
new RMAN configuration parameters are successfully stored
|
컨트롤파일 자동 백업 옵션 및 경로 지정
|
1
2
3
4
5
6
7
8
9
|
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 설정 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORADB 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 '/ORA19/app/oracle/product/19.3.0/db_1/dbs/snapcf_ORADB1.f'; # default
|
백업 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
RMAN> backup database;
Starting backup at 15-FEB-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=145 instance=ORADB1 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=00002 name=+DATA/ORADB/DATAFILE/bokgu.269.1096729085
input datafile file number=00001 name=+DATA/ORADB/DATAFILE/system.257.1034734733
input datafile file number=00004 name=+DATA/ORADB/DATAFILE/undotbs1.259.1034734833
input datafile file number=00003 name=+DATA/ORADB/DATAFILE/sysaux.258.1034734797
input datafile file number=00005 name=+DATA/ORADB/DATAFILE/undotbs2.265.1034736607
input datafile file number=00007 name=+DATA/ORADB/DATAFILE/users.260.1034734835
channel ORA_DISK_1: starting piece 1 at 15-FEB-22
channel ORA_DISK_1: finished piece 1 at 15-FEB-22
piece handle=/home/oracle/rman/db_030ltgl5_1_1_20220215 tag=TAG20220215T150053 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 15-FEB-22
Starting Control File and SPFILE Autobackup at 15-FEB-22
piece handle=/home/oracle/rman/db_control_c-2787999664-20220215-01 comment=NONE
Finished Control File and SPFILE Autobackup at 15-FEB-22
|
백업본 확인
|
1
2
3
4
5
6
|
$ ls -al /home/oracle/rman/
total 1564820
drwxr-xr-x 2 oracle dba 81 Feb 15 15:01 .
drwx------. 19 oracle oinstall 4096 Feb 15 14:55 ..
-rw-r----- 1 oracle dba 1582546944 Feb 15 15:01 db_030ltgl5_1_1_20220215
-rw-r----- 1 oracle dba 19824640 Feb 15 15:01 db_control_c-2787999664-20220215-01
|
일부 데이터 삭제
|
1
2
3
4
5
6
7
8
|
SQL> conn bokgu_user/bokgu_user;
SQL> delete bokgu_table where rownum <= 5000;
5000 rows deleted.
SQL> commit;
Commit complete.
|
데이터 건수 확인
|
1
2
3
4
5
|
SQL> select count(*) from bokgu_table;
COUNT(*)
----------
5000
|
로그 스위치
|
1
2
3
4
5
6
7
|
SQL>
conn / as sysdba
alter system switch logfile;
/
/
/
/
|
현재 시간 확인
|
1
2
3
4
5
|
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') dt from dual;
DT
-------------------
2022-02-15 15:04:06
|
3분 기다림
|
1
2
3
4
5
|
SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') dt from dual;
DT
-------------------
2022-02-15:15:07:07
|
데이터 추가 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> conn bokgu_user/bokgu_user;
SQL>
begin
for i in 1..2500
loop
insert into bokgu_table values
(i, i+i);
end loop;
end;
/
commit;
|
데이터 건수 확인
|
1
2
3
4
5
|
SQL> select count(*) from bokgu_table;
COUNT(*)
----------
7500
|
로그 스위치
|
1
2
3
4
5
6
7
|
SQL>
conn / as sysdba
alter system switch logfile;
/
/
/
/
|
현재 시간 확인
|
1
2
3
4
5
|
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') dt from dual;
DT
-------------------
2022-02-15 15:10:09
|
복구하려는 데이터 시점과 데이터 건수
시간 : 2022-02-15 15:04:06 데이터 : 5000건
pfile 백업
|
1
2
3
|
SQL> create pfile='$ORACLE_HOME/dbs/initORADB1.ora' from spfile;
File created.
|
pfile을 target db 서버로 전송
|
1
2
3
4
5
6
7
8
|
$ scp $ORACLE_HOME/dbs/initORADB1.ora oracle@192.168.137.50:/app/oracle/product/19c/dbs/
The authenticity of host '192.168.137.50 (192.168.137.50)' can't be established.
ECDSA key fingerprint is SHA256:a4fWwiF7kDjd35fvVbrH/GxJv5BHqgPeBx2AUBB8KHU.
ECDSA key fingerprint is MD5:db:a5:90:6c:8b:3d:30:7a:f7:11:1a:d7:19:02:4e:da.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.137.50' (ECDSA) to the list of known hosts.
oracle@192.168.137.50's password:
ORADB1.ora 100% 1755 2.3MB/s 00:00
|
single target db 서버
백업된 pfile 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
$ cat initORADB1.ora
ORADB1.__data_transfer_cache_size=0
ORADB2.__data_transfer_cache_size=0
ORADB1.__db_cache_size=234881024
ORADB2.__db_cache_size=301989888
ORADB1.__inmemory_ext_roarea=0
ORADB2.__inmemory_ext_roarea=0
ORADB1.__inmemory_ext_rwarea=0
ORADB2.__inmemory_ext_rwarea=0
ORADB1.__java_pool_size=16777216
ORADB2.__java_pool_size=16777216
ORADB1.__large_pool_size=16777216
ORADB2.__large_pool_size=16777216
ORADB1.__oracle_base='/ORA19/app/oracle'#ORACLE_BASE set from environment
ORADB2.__oracle_base='/ORA19/app/oracle'#ORACLE_BASE set from environment
ORADB1.__pga_aggregate_target=822083584
ORADB2.__pga_aggregate_target=771751936
ORADB1.__sga_target=721420288
ORADB2.__sga_target=771751936
ORADB1.__shared_io_pool_size=33554432
ORADB2.__shared_io_pool_size=33554432
ORADB1.__shared_pool_size=402653184
ORADB2.__shared_pool_size=385875968
ORADB1.__streams_pool_size=0
ORADB2.__streams_pool_size=0
ORADB1.__unified_pga_pool_size=0
ORADB2.__unified_pga_pool_size=0
*.audit_file_dest='/ORA19/app/oracle/admin/ORADB/adump'
*.audit_trail='none'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+DATA/ORADB/CONTROLFILE/current.261.1034734897'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='ORADB'
*.diagnostic_dest='/ORA19/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORADBXDB)'
family:dw_helper.instance_mode='read-only'
ORADB2.instance_number=2
ORADB1.instance_number=1
*.local_listener='-oraagent-dummy-'
*.log_archive_dest_1='location=/ora_arch'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=1470m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='exclusive'
ORADB2.thread=2
ORADB1.thread=1
ORADB2.undo_tablespace='UNDOTBS2'
ORADB1.undo_tablespace='UNDOTBS1'
|
init 파일 명 initCLONE.ora 로 변경
dbname은 기존과 동일하게 다른 디렉토리 명은 새 서버 디렉토리에 맞게 CLONE로 변경함
ORADB1,2.__ 부분 모두 삭제
audit_file_dest, dispatchers 변경
local_listener 서버에 맞게 변경
파라미터 주석 : cluster_database,db_create_file_dest, family:dw_helper.instance_mode='read-only', racdb2.instance_number=2, racdb1.instance_number=1, racdb2.thread=1, racdb2.thread=2
컨트롤 파일 위치 변경
memory_target 변경(선택)
undo_tablespace 변경
수정된 pfile
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
$ cat $ORACLE_HOME/dbs/initCLONE.ora
*.audit_file_dest='/app/oracle/admin/CLONE/adump'
*.audit_trail='none'
#*.cluster_database=true
*.compatible='19.0.0'
*.control_files='/app/oracle/oradata/CLONE/control01.ctl'
*.db_block_size=8192
#*.db_create_file_dest='+DATA'
*.db_name='ORADB'
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLONEXDB)'
#family:dw_helper.instance_mode='read-only'
#ORADB2.instance_number=2
#ORADB1.instance_number=1
*.local_listener='LISTENER_CLONE'
*.log_archive_dest_1='location=/app/oracle/arch'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=1470m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='exclusive'
#ORADB2.thread=2
#ORADB1.thread=1
#ORADB2.undo_tablespace='UNDOTBS2'
*.undo_tablespace='UNDOTBS1'
|
db 관련 폴더 생성
|
1
2
3
4
|
$ mkdir -p /app/oracle/admin/CLONE/adump
$ mkdir -p /app/oracle/oradata/CLONE/
$ mkdir -p /home/oracle/rman
$ mkdir -p /app/oracle/arch
|
local_listener 파라미터 관련 내용 수정
|
1
2
3
4
5
6
|
$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /app/oracle/product/19c/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_CLONE =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521))
|
source db서버에서 target db 서버로 rman 백업 파일 전송
|
1
2
3
4
|
$ scp /home/oracle/rman/* oracle@192.168.137.50:/home/oracle/rman
oracle@192.168.137.50's password:
db_030ltgl5_1_1_20220215 100% 1509MB 125.7MB/s 00:12
db_control_c-2787999664-20220215-01 100% 19MB 96.8MB/s 00:00
|
source db서버에서 target db 서버로 전송할 아카이브 로그 대상확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
1번 노드
$ ls -al /ora_arch/
total 21312
drwxr-xr-x. 2 oracle dba 4096 Feb 15 15:09 .
dr-xr-xr-x. 19 root root 4096 Mar 10 2020 ..
-rw-r----- 1 oracle dba 14419456 Feb 15 15:03 1_10_1034734900.arc
-rw-r----- 1 oracle dba 3584 Feb 15 15:03 1_11_1034734900.arc
-rw-r----- 1 oracle dba 1024 Feb 15 15:03 1_12_1034734900.arc
-rw-r----- 1 oracle dba 1024 Feb 15 15:03 1_13_1034734900.arc
-rw-r----- 1 oracle dba 1024 Feb 15 15:03 1_14_1034734900.arc
-rw-r----- 1 oracle dba 7325696 Feb 15 15:09 1_15_1034734900.arc
-rw-r----- 1 oracle dba 33280 Feb 15 15:09 1_16_1034734900.arc
-rw-r----- 1 oracle dba 1024 Feb 15 15:09 1_17_1034734900.arc
-rw-r----- 1 oracle dba 1024 Feb 15 15:09 1_18_1034734900.arc
-rw-r----- 1 oracle dba 1024 Feb 15 15:09 1_19_1034734900.arc
2번 노드
$ ls -al /ora_arch/
total 14892
drwxr-xr-x. 2 oracle dba 58 Feb 15 15:09 .
dr-xr-xr-x. 19 root root 4096 Mar 6 2020 ..
-rw-r----- 1 oracle dba 8208896 Feb 15 15:03 2_2_1034734900.arc
-rw-r----- 1 oracle dba 7032320 Feb 15 15:09 2_3_1034734900.arc
|
Feb 15 15:03 파일까지만 전송(1번노드 1_14_1034734900.arc 까지, 2번노드 2_2_1034734900.arc 까지)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
1번 노드
$ cd /ora_arch/
$ scp 1_10_1034734900.arc 1_11_1034734900.arc 1_12_1034734900.arc 1_13_1034734900.arc 1_14_1034734900.arc oracle@192.168.137.50:/app/oracle/arch/
oracle@192.168.137.50''s password:
1_10_1034734900.arc 100% 14MB 113.8MB/s 00:00
1_11_1034734900.arc 100% 3584 3.9MB/s 00:00
1_12_1034734900.arc 100% 1024 1.3MB/s 00:00
1_13_1034734900.arc 100% 1024 1.4MB/s 00:00
1_14_1034734900.arc 100% 1024 1.4MB/s 00:00
2번 노드
$ cd /ora_arch/
$ scp 2_2_1034734900.arc oracle@192.168.137.50:/app/oracle/arch/
oracle@192.168.137.50''s password:
2_2_1034734900.arc 100% 8017KB 95.9MB/s 00:00
|
target db 서버에서 파일 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
$ cd /app/oracle/arch/
$ ls -al
total 22128
drwxr-xr-x. 2 oracle dba 4096 Feb 15 16:03 .
drwxrwxr-x. 11 oracle dba 4096 Feb 15 15:50 ..
-rw-r-----. 1 oracle dba 14419456 Feb 15 16:13 1_10_1034734900.arc
-rw-r-----. 1 oracle dba 3584 Feb 15 16:13 1_11_1034734900.arc
-rw-r-----. 1 oracle dba 1024 Feb 15 16:13 1_12_1034734900.arc
-rw-r-----. 1 oracle dba 1024 Feb 15 16:13 1_13_1034734900.arc
-rw-r-----. 1 oracle dba 1024 Feb 15 16:13 1_14_1034734900.arc
-rw-r-----. 1 oracle dba 8208896 Feb 15 16:13 2_2_1034734900.arc
$ cd /home/oracle/rman/
$ ls -al
total 1564820
drwxr-xr-x. 2 oracle dba 81 Feb 15 16:12 .
drwx------. 12 oracle dba 4096 Feb 15 15:50 ..
-rw-r-----. 1 oracle dba 1582546944 Feb 15 16:12 db_030ltgl5_1_1_20220215
-rw-r-----. 1 oracle dba 19824640 Feb 15 16:12 db_control_c-2787999664-20220215-01
|
target db nomount로 기동
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
$ export ORACLE_SID=CLONE
$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 15 16:14:57 2022
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1543500136 bytes
Fixed Size 8896872 bytes
Variable Size 889192448 bytes
Database Buffers 637534208 bytes
Redo Buffers 7876608 bytes
|
프로세스 확인
|
1
2
3
|
$ ps -ef | grep pmon
oracle 22359 1 0 16:15 ? 00:00:00 ora_pmon_CLONE
oracle 22583 9621 0 16:15 pts/0 00:00:00 grep --color=auto pmon
|
컨트롤 파일 restore
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Feb 15 16:16:01 2022
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB (not mounted)
RMAN> restore controlfile from '/home/oracle/rman/db_control_c-2787999664-20220215-01';
Starting restore at 15-FEB-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=246 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/app/oracle/oradata/CLONE/control01.ctl
Finished restore at 15-FEB-22
|
db mount 상태로 변경
|
1
2
3
4
|
RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
|
파라미터 파일 명이 initCLONE.ora이지만 (SID=CLONE)
dbname은 기존과 동일하게 racdb이므로 mount 상태로 올라감
rman 설정 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORADB 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 '/app/oracle/product/19c/dbs/snapcf_CLONE.f'; # default
|
db 상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
CLONE MOUNTED
SQL> select name from v$database;
NAME
---------
ORADB
|
RMAN용 스크립트 작성을 위해 datafile 번호 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
$ rman target /
RMAN> report schema;
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORADB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** +DATA/ORADB/DATAFILE/system.257.1034734733
2 0 BOKGU *** +DATA/ORADB/DATAFILE/bokgu.269.1096729085
3 0 SYSAUX *** +DATA/ORADB/DATAFILE/sysaux.258.1034734797
4 0 UNDOTBS1 *** +DATA/ORADB/DATAFILE/undotbs1.259.1034734833
5 0 UNDOTBS2 *** +DATA/ORADB/DATAFILE/undotbs2.265.1034736607
7 0 USERS *** +DATA/ORADB/DATAFILE/users.260.1034734835
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATA/ORADB/TEMPFILE/temp.264.1034734917
|
RMAN용 스크립트 작성 및 실행(datafile restore)
*users 테이블스페이스의 datafile 7번은 set newname 하지않음
한번에 붙여넣으면 에러 발생함 run { 구문까지 입력 후 엔터 후 나머지 입력하면 잘 실행됨
(그래도 안된다면 한줄씩 붙여넣기)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
RMAN> run {
allocate channel ch1 device type disk ;
allocate channel ch2 device type disk ;
set newname for tempfile 1 to '/app/oracle/oradata/CLONE/temp01.dbf';
set newname for datafile 1 to '/app/oracle/oradata/CLONE/system01.dbf' ;
set newname for datafile 2 to '/app/oracle/oradata/CLONE/bokgu01.dbf' ;
set newname for datafile 3 to '/app/oracle/oradata/CLONE/sysaux01.dbf' ;
set newname for datafile 4 to '/app/oracle/oradata/CLONE/undotbs01.dbf' ;
set newname for datafile 5 to '/app/oracle/oradata/CLONE/undotbs02.dbf' ;
#restore database skip tablespace 'users';
restore datafile 1,2,3,4,5;
switch datafile all;
switch tempfile all;
} <- 이까지 입력 후 엔터
allocated channel: ch1
channel ch1: SID=128 device type=DISK
allocated channel: ch2
channel ch2: SID=10 device type=DISK
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 15-FEB-22
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to /app/oracle/oradata/CLONE/system01.dbf
channel ch1: restoring datafile 00002 to /app/oracle/oradata/CLONE/bokgu01.dbf
channel ch1: restoring datafile 00003 to /app/oracle/oradata/CLONE/sysaux01.dbf
channel ch1: restoring datafile 00004 to /app/oracle/oradata/CLONE/undotbs01.dbf
channel ch1: restoring datafile 00005 to /app/oracle/oradata/CLONE/undotbs02.dbf
channel ch1: reading from backup piece /home/oracle/rman/db_030ltgl5_1_1_20220215
channel ch1: piece handle=/home/oracle/rman/db_030ltgl5_1_1_20220215 tag=TAG20220215T150053
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
Finished restore at 15-FEB-22
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1096734968 file name=/app/oracle/oradata/CLONE/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=1096734968 file name=/app/oracle/oradata/CLONE/bokgu01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=1096734968 file name=/app/oracle/oradata/CLONE/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=1096734968 file name=/app/oracle/oradata/CLONE/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=1096734968 file name=/app/oracle/oradata/CLONE/undotbs02.dbf
renamed tempfile 1 to /app/oracle/oradata/CLONE/temp01.dbf in control file
released channel: ch1
released channel: ch2
|
컨트롤파일 백업
|
1
2
3
|
SQL> alter database backup controlfile to trace as '/home/oracle/recon.sql';
Database altered.
|
db 종료
|
1
2
3
4
5
6
|
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
|
pfile 수정(db_name을 ORADB에서 CLONE 로 변경)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
$ cd $ORACLE_HOME/dbs
$ vi initCLONE.ora
*.audit_file_dest='/app/oracle/admin/CLONE/adump'
*.audit_trail='none'
#*.cluster_database=true
*.compatible='19.0.0'
*.control_files='/app/oracle/oradata/CLONE/control01.ctl'
*.db_block_size=8192
#*.db_create_file_dest='+DATA'
*.db_name='CLONE'
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLONEXDB)'
#family:dw_helper.instance_mode='read-only'
#ORADB2.instance_number=2
#ORADB1.instance_number=1
*.local_listener='LISTENER_CLONE'
*.log_archive_dest_1='location=/app/oracle/arch'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=1470m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='exclusive'
#ORADB2.thread=2
#ORADB1.thread=1
#ORADB2.undo_tablespace='UNDOTBS2'
*.undo_tablespace='UNDOTBS1'
|
컨트롤파일을 재생성해서 dbname을 CLONE로 변경시켜줌
컨트롤파일 생성파일 확인
REUSE RESETLOGS가 적힌 부분만 남기고 REUSE를 SET 으로 변경
다른 REUSE NORESETLOGS 부분은 삭제한 상태
users 테이블스페이스의 datafile 은 recon에 입력하지 않음
(데이터 파일을 restore 했기 때문에 controlfile 재생성이 가능함)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
$ 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 '/app/oracle/oradata/CLONE/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/app/oracle/oradata/CLONE/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/app/oracle/oradata/CLONE/redo03.log' SIZE 200M BLOCKSIZE 512
DATAFILE
'/app/oracle/oradata/CLONE/system01.dbf',
'/app/oracle/oradata/CLONE/bokgu01.dbf',
'/app/oracle/oradata/CLONE/sysaux01.dbf',
'/app/oracle/oradata/CLONE/undotbs01.dbf',
'/app/oracle/oradata/CLONE/undotbs02.dbf'
CHARACTER SET KO16MSWIN949
;
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
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 FORMAT FOR DEVICE TYPE','DISK TO ''/home/oracle/rman/db_control_%F''');
--ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/CLONE/temp01.dbf' REUSE;
|
기존 컨트롤파일 제거
|
1
2
|
$ cd /app/oracle/oradata/CLONE/
$ rm control01.ctl
|
recon.sql 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> @/home/oracle/recon.sql
ORACLE instance started.
Total System Global Area 1543500136 bytes
Fixed Size 8896872 bytes
Variable Size 889192448 bytes
Database Buffers 637534208 bytes
Redo Buffers 7876608 bytes
Control file created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
|
인스턴스 이름, 상태 및 dbname 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
CLONE MOUNTED
SQL> select name from v$database;
NAME
---------
CLONE
|
복구 작업 진행
auto 입력시 아래와 같이 자동으로 파일을 인식함
recover 명령 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> recover database until time '2022-02-15 15:04:00' using backup controlfile;
ORA-00279: change 2349929 generated at 02/15/2022 15:00:53 needed for thread 1
ORA-00289: suggestion : /app/oracle/arch/1_10_1034734900.arc
ORA-00280: change 2349929 for thread 1 is in sequence #10
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2349929 generated at needed for thread 2
ORA-00266: name of archived log file needed
|
auto 입력시 ORA-00266 에러가 발생함
https://positivemh.tistory.com/509 참조
해결방법
원본db(source rac db)에서 v$archived_log 뷰 조회
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
col name for a50
select thread#, FIRST_CHANGE#, NEXT_CHANGE#, name
from v$archived_log
where 2349929 between FIRST_CHANGE# and NEXT_CHANGE#
/
THREAD# FIRST_CHANGE# NEXT_CHANGE# NAME
---------- ------------- ------------ --------------------------------------------------
1 2234639 2350099 /ora_arch/1_10_1034734900.arc
2 2334676 2350115 /ora_arch/2_2_1034734900.arc
|
THREAD 1번 2번이 나오는데
나의 경우는 ORA-00279: change 2349929 generated at needed for thread 2 로 표시되기 때문에
2번 아카이브파일 경로(/app/oracle/arch/2_2_1034734900.arc)을 입력해주면됨
THREAD 1번 파일을 입력하면 THREAD 2번 파일이 필요하다고 나옴
이때 THREAD 2번 파일을 입력하면 no longer needed for this recovery(이 복구에 더 이상 필요하지 않음)이라고 표시됨
이후 auto 를 입력하면 자동으로 아카이브를 적용시킴
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
SQL> recover database until time '2022-02-15 15:04:00' using backup controlfile;
ORA-00279: change 2349929 generated at 02/15/2022 15:00:53 needed for thread 1
ORA-00289: suggestion : /app/oracle/arch/1_10_1034734900.arc
ORA-00280: change 2349929 for thread 1 is in sequence #10
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/app/oracle/arch/1_10_1034734900.arc <-- 1번 아카이브 입력
ORA-00279: change 2349929 generated at needed for thread 2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/app/oracle/arch/2_2_1034734900.arc <-- 2번 아카이브 입력
ORA-00279: change 2350099 generated at 02/15/2022 15:03:41 needed for thread 1
ORA-00289: suggestion : /app/oracle/arch/1_11_1034734900.arc
ORA-00280: change 2350099 for thread 1 is in sequence #11
ORA-00278: log file '/app/oracle/arch/1_10_1034734900.arc' no longer needed for
this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto <-- auto 입력
ORA-00279: change 2350102 generated at 02/15/2022 15:03:43 needed for thread 1
ORA-00289: suggestion : /app/oracle/arch/1_12_1034734900.arc
ORA-00280: change 2350102 for thread 1 is in sequence #12
ORA-00278: log file '/app/oracle/arch/1_11_1034734900.arc' no longer needed for
this recovery
ORA-00279: change 2350105 generated at 02/15/2022 15:03:43 needed for thread 1
ORA-00289: suggestion : /app/oracle/arch/1_13_1034734900.arc
ORA-00280: change 2350105 for thread 1 is in sequence #13
ORA-00278: log file '/app/oracle/arch/1_12_1034734900.arc' no longer needed for
this recovery
ORA-00279: change 2350108 generated at 02/15/2022 15:03:43 needed for thread 1
ORA-00289: suggestion : /app/oracle/arch/1_14_1034734900.arc
ORA-00280: change 2350108 for thread 1 is in sequence #14
ORA-00278: log file '/app/oracle/arch/1_13_1034734900.arc' no longer needed for
this recovery
ORA-00279: change 2350111 generated at 02/15/2022 15:03:43 needed for thread 1
ORA-00289: suggestion : /app/oracle/arch/1_15_1034734900.arc
ORA-00280: change 2350111 for thread 1 is in sequence #15
ORA-00278: log file '/app/oracle/arch/1_14_1034734900.arc' no longer needed for
this recovery
ORA-00308: cannot open archived log '/app/oracle/arch/1_15_1034734900.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
|
1_14_1034734900.arc 까지 복구가 완료됨
1_15_1034734900.arc 는 2022-02-15 15:04:00 이후의 로그이기 때문에 target db 서버에는 존재하지 않아서 에러가 발생함
db open
|
1
2
3
4
5
|
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
|
에러가 발생하지만 버그로 발생한 문제임
https://positivemh.tistory.com/507 참조
히든파라미터 적용
|
1
2
3
|
SQL> alter system set "_no_recovery_through_resetlogs"=true;
System altered.
|
다시 open 시도
|
1
2
3
|
SQL> alter database open resetlogs;
Database altered.
|
정상적으로 open됨
데이터 확인
|
1
2
3
4
5
6
7
|
SQL> conn bokgu_user/bokgu_user
Connected.
SQL> select count(*) from bokgu_table;
COUNT(*)
----------
5000
|
정상적으로 원하는 시점까지 복구됨
temp tablespace 추가
|
1
2
3
|
SQL> alter tablespace temp add tempfile '/app/oracle/oradata/CLONE/temp01.dbf' size 100m;
Tablespace altered.
|
19.6 버전에서 19.10 버전으로 clone을 했기 때문에 db에도 패치 적용을 시켜줘야함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
$ cd $ORACLE_HOME/OPatch
$ ./datapatch -verbose
SQL Patching tool version 19.10.0.0.0 Production on Tue Feb 15 18:48:31 2022
Copyright (c) 2012, 2020, Oracle. All rights reserved.
Log file for this invocation: /app/oracle/cfgtoollogs/sqlpatch/sqlpatch_19498_2022_02_15_18_48_31/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
Interim patch 30484981 (OJVM RELEASE UPDATE: 19.6.0.0.200114 (30484981)):
Binary registry: Not installed
SQL registry: Applied successfully on 11-MAR-20 02.48.38.728094 AM
Interim patch 32067171 (OJVM RELEASE UPDATE: 19.10.0.0.210119 (32067171)):
Binary registry: Installed
SQL registry: Not installed
Current state of release update SQL patches:
Binary registry:
19.10.0.0.0 Release_Update 210108185017: Installed
SQL registry:
Applied 19.6.0.0.0 Release_Update 191217155004 successfully on 11-MAR-20 02.48.38.711721 AM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
The following interim patches will be rolled back:
30484981 (OJVM RELEASE UPDATE: 19.6.0.0.200114 (30484981))
Patch 32218454 (Database Release Update : 19.10.0.0.210119 (32218454)):
Apply from 19.6.0.0.0 Release_Update 191217155004 to 19.10.0.0.0 Release_Update 210108185017
The following interim patches will be applied:
32067171 (OJVM RELEASE UPDATE: 19.10.0.0.210119 (32067171))
Installing patches...
Patch installation complete. Total patches installed: 3
Validating logfiles...done
Patch 30484981 rollback: SUCCESS
logfile: /app/oracle/cfgtoollogs/sqlpatch/30484981/23248235/30484981_rollback_CLONE_2022Feb15_18_48_42.log (no errors)
Patch 32218454 apply: SUCCESS
logfile: /app/oracle/cfgtoollogs/sqlpatch/32218454/24018797/32218454_apply_CLONE_2022Feb15_18_49_07.log (no errors)
Patch 32067171 apply: SUCCESS
logfile: /app/oracle/cfgtoollogs/sqlpatch/32067171/23947975/32067171_apply_CLONE_2022Feb15_18_49_07.log (no errors)
SQL Patching tool complete on Tue Feb 15 18:51:57 2022
|
db 패치 내역 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SQL>
SET LINESIZE 400
COLUMN ACTION_TIME FORMAT A20
COLUMN ACTION FORMAT A10
COLUMN STATUS FORMAT A10
COLUMN DESCRIPTION FORMAT A60
COLUMN VERSION FORMAT A10
SELECT TO_CHAR(ACTION_TIME, 'YYYYMMDD HH24:MI:SS') AS ACTION_TIME
,ACTION
,STATUS
,DESCRIPTION
,SOURCE_VERSION
,TARGET_VERSION
,PATCH_ID
FROM DBA_REGISTRY_SQLPATCH
ORDER BY ACTION_TIME
/
ACTION_TIME ACTION STATUS DESCRIPTION SOURCE_VERSION TARGET_VERSION PATCH_ID
-------------------- ---------- ---------- ------------------------------------------------------------ --------------- --------------- ----------
20200311 02:48:38 APPLY SUCCESS OJVM RELEASE UPDATE: 19.6.0.0.200114 (30484981) 19.1.0.0.0 19.1.0.0.0 30484981
20200311 02:48:38 APPLY SUCCESS Database Release Update : 19.6.0.0.200114 (30557433) 19.1.0.0.0 19.6.0.0.0 30557433
20220215 18:51:53 ROLLBACK SUCCESS OJVM RELEASE UPDATE: 19.6.0.0.200114 (30484981) 19.10.0.0.0 19.10.0.0.0 30484981
20220215 18:51:57 APPLY SUCCESS OJVM RELEASE UPDATE: 19.10.0.0.210119 (32067171) 19.6.0.0.0 19.6.0.0.0 32067171
20220215 18:51:57 APPLY SUCCESS Database Release Update : 19.10.0.0.210119 (32218454) 19.6.0.0.0 19.10.0.0.0 32218454
|
정상적으로 19.10 패치 적용됨
정상적으로 RAC DB가 SINGLE DB로 복구, 복제(CLONE)됨
추가 자료
users 테이블스페이스가 현재 복구 대상에서 제외했기 때문에 missing 상태로 남아있음
해당 테이블스페이스가 default tablespace 이기 때문에 바로 삭제되지 않아서
임시 테이블스페이스(users2)를 만든 뒤 임시 테이블스페이스(users2)를 default tablespace로 지정하고
기존 users 테이블스페이스를 지운 뒤 새로 users 테이블스페이스를 만들고 다시 default tablespace로 지정하고
임시 테이블스페이스를 지우는 절차를 진행함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
테이블스페이스 상태확인
SQL> select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_
------------------------------ ------------------------------------------------------------ -------
UNDOTBS2 /app/oracle/oradata/CLONE/undotbs02.dbf ONLINE
UNDOTBS1 /app/oracle/oradata/CLONE/undotbs01.dbf ONLINE
SYSAUX /app/oracle/oradata/CLONE/sysaux01.dbf ONLINE
BOKGU /app/oracle/oradata/CLONE/bokgu01.dbf ONLINE
SYSTEM /app/oracle/oradata/CLONE/system01.dbf SYSTEM
USERS /app/oracle/product/19c/dbs/MISSING00007 RECOVER
6 rows selected.
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
임시 테이블스페이스 생성
SQL> create tablespace users2 datafile '/app/oracle/oradata/CLONE/users02.dbf' size 10m;
Tablespace created.
DAFAULT TABLESPACE를 임시 테이블스페이스(USERS2)로 지정
SQL> alter database default tablespace users2;
Database altered.
기존 USERS 테이블스페이스 제거
SQL> drop tablespace users including contents and datafiles;
Tablespace dropped.
USERS 테이블스페이스 재생성
SQL> create tablespace users datafile '/app/oracle/oradata/CLONE/users01.dbf' size 10m autoextend on;
Tablespace created.
DAFAULT TABLESPACE를 USERS 테이블스페이스로 지정
SQL> alter database default tablespace users;
Database altered.
임시 테이블스페이스(USERS2) 제거
SQL> drop tablespace users2 including contents and datafiles;
Tablespace dropped.
테이블스페이스 상태확인
SQL> select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_
------------------------------ ------------------------------------------------------------ -------
UNDOTBS2 /app/oracle/oradata/CLONE/undotbs02.dbf ONLINE
UNDOTBS1 /app/oracle/oradata/CLONE/undotbs01.dbf ONLINE
SYSAUX /app/oracle/oradata/CLONE/sysaux01.dbf ONLINE
BOKGU /app/oracle/oradata/CLONE/bokgu01.dbf ONLINE
SYSTEM /app/oracle/oradata/CLONE/system01.dbf SYSTEM
USERS /app/oracle/oradata/CLONE/users01.dbf ONLINE
6 rows selected.
|
정상화됨
참조 :
https://positivemh.tistory.com/518
rac db(asm)에서 특정시점의 데이터를 single db(filesystem) 로 dbname 변경 후 복구 테스트2
OS환경 : Oracle Linux 6.8 (64bit) DB 환경 : Oracle Database 11.2.0.4 ASM RAC to FS SINGLE 방법 : rac db(asm)에서 특정시점의 데이터를 single db(filesystem) 로 dbname 변경 후 복구 테스트2 개요 rac db(..
positivemh.tistory.com
DOC 342784.1
http://www.br8dba.com/rman-database-restore-from-asm-to-file-system/
https://gyh214.tistory.com/132
https://dinggur.tistory.com/151
https://docs.oracle.com/cd/E18283_01/backup.112/e10642/rcmdupad.htm
https://dinggur.tistory.com/186
https://community.oracle.com/thread/3875366
https://aboutdb.tistory.com/?page=83
https://positivemh.tistory.com/507
https://positivemh.tistory.com/508
https://positivemh.tistory.com/509
https://gyh214.tistory.com/162
https://m.blog.naver.com/hanccii/220985607102
https://dinggur.tistory.com/182
http://www.dba-oracle.com/t_rman_60_set_newname.htm
https://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=46880&DestinationA=RSS
https://gyh214.tistory.com/130
https://positivemh.tistory.com/268
https://positivemh.tistory.com/515
'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 19c rman backup set 방식과 image copy 방식 백업 크기 비교 (0) | 2023.12.21 |
|---|---|
| 오라클 19c rman 접속하는 몇가지 방법 (0) | 2023.01.25 |
| 오라클 11g R2 rman output 이전 기록 확인 (0) | 2021.12.28 |
| 오라클 11g R2 RAC PSU 패치전 백업 가이드 (0) | 2021.10.20 |
| 오라클 11g R2 GRID OLR 백업 복구 (0) | 2021.10.20 |