OS 환경 : Oracle Linux 8.1 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c templates 경로의 seeddata 데이터베이스
지난번 템플릿별 컴포넌트 설치 테스트를 하다가 템플릿 경로에 Seed_Database.ctl와 Seed_Database.dfb라는 파일이 존재한다는걸을 확인함
|
1
2
3
4
5
6
7
8
9
10
11
12
|
$ cd $ORACLE_HOME/assistants/dbca/templates
$ ls -al
total 370972
drwxr-xr-x. 2 oracle oinstall 201 Feb 24 11:40 .
drwxr-xr-x. 5 oracle oinstall 81 Feb 5 21:51 ..
-rw-r-----. 1 oracle oinstall 4888 Apr 17 2019 Data_Warehouse.dbc
-rw-r----- 1 oracle oinstall 4783 Oct 21 09:36 General_Purpose.dbc
-rw-r-----. 1 oracle oinstall 10772 Apr 6 2019 New_Database.dbt
-rw-r-----. 1 oracle oinstall 86548480 Apr 17 2019 pdbseed.dfb
-rw-r-----. 1 oracle oinstall 6611 Apr 17 2019 pdbseed.xml
-rw-r-----. 1 oracle oinstall 18726912 Apr 17 2019 Seed_Database.ctl <<---
-rw-r-----. 1 oracle oinstall 274554880 Apr 17 2019 Seed_Database.dfb <<---
|
지난 테스트 참고 : 오라클 19c dbca silent 시 템플릿별 컴포넌트 비활성화 설정 ( https://positivemh.tistory.com/1203 )
그래서 이 파일들이 뭔지 알아보기 위해 dbca 로그를 찾아봄
*참고로 이렇게 Seed_Database를 사용하는 템플릿은 General_Purpose와 Data_Warehouse, custom은 이렇게 진행되지 않았음
|
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
|
$ vi /app/oracle/cfgtoollogs/dbca/oracle19/trace.log_2025-02-24_06-31-00AM
...
[progressPage.flowWorker] [ 2025-02-24 06:31:23.163 KST ] [CloneRmanRestoreStep.executeImpl:353] Using control file: /app/oracle/product/19c/assistants/dbca/templates/Seed_Database.ctl; sourceDBName: seeddata
..
[progressPage.flowWorker] [ 2025-02-24 06:31:32.961 KST ] [CloneAttributes.getDatafileIdNameMapping:1033] newname for datafile /app/oracle/oradata/ORACLE19/sysaux01.dbf ID:3
[progressPage.flowWorker] [ 2025-02-24 06:31:32.961 KST ] [CloneAttributes.getDatafileIdNameMapping:1033] newname for datafile /app/oracle/oradata/ORACLE19/system01.dbf ID:1
[progressPage.flowWorker] [ 2025-02-24 06:31:32.961 KST ] [CloneAttributes.getDatafileIdNameMapping:1033] newname for datafile /app/oracle/oradata/ORACLE19/undotbs01.dbf ID:4
[progressPage.flowWorker] [ 2025-02-24 06:31:32.961 KST ] [CloneAttributes.getDatafileIdNameMapping:1033] newname for datafile /app/oracle/oradata/ORACLE19/users01.dbf ID:7
..
[progressPage.flowWorker] [ 2025-02-24 06:31:34.953 KST ] [RMANUtil.catalogBackup:739] catalog command CATALOG START WITH '/app/oracle/product/19c/assistants/dbca/templates//Seed_Database.dfb' NOPROMPT
..
[progressPage.flowWorker] [ 2025-02-24 06:31:36.476 KST ] [RMANEngine.executeImpl:1302] Command being written to rman process=RUN {
set newname for datafile 1 to '/app/oracle/oradata/ORACLE19/system01.dbf' ;
set newname for datafile 3 to '/app/oracle/oradata/ORACLE19/sysaux01.dbf' ;
set newname for datafile 4 to '/app/oracle/oradata/ORACLE19/undotbs01.dbf' ;
set newname for datafile 7 to '/app/oracle/oradata/ORACLE19/users01.dbf' ;
restore datafile 1;
restore datafile 3;
restore datafile 4;
restore datafile 7; }
..
[progressPage.flowWorker] [ 2025-02-24 06:33:30.585 KST ] [RMANUtil.restoreDataFilesFromBackup:534] done with set new name
[progressPage.flowWorker] [ 2025-02-24 06:33:30.602 KST ] [CloneAttributes.setDataFileNames:1153] Updated datafile name:/app/oracle/oradata/ORACLE19/sysaux01.dbf
[progressPage.flowWorker] [ 2025-02-24 06:33:30.607 KST ] [CloneAttributes.setDataFileNames:1153] Updated datafile name:/app/oracle/oradata/ORACLE19/system01.dbf
[progressPage.flowWorker] [ 2025-02-24 06:33:30.613 KST ] [CloneAttributes.setDataFileNames:1153] Updated datafile name:/app/oracle/oradata/ORACLE19/undotbs01.dbf
[progressPage.flowWorker] [ 2025-02-24 06:33:30.618 KST ] [CloneAttributes.setDataFileNames:1153] Updated datafile name:/app/oracle/oradata/ORACLE19/users01.dbf
[progressPage.flowWorker] [ 2025-02-24 06:33:30.618 KST ] [RmanRestoreDatafilesStep.executeImpl:189] Restored datafiles
..
[progressPage.flowWorker] [ 2025-02-24 06:33:39.079 KST ] [CloneDBCreationStep.executeImpl:486] Length of OriginalRedoLogsGrNames=3
[progressPage.flowWorker] [ 2025-02-24 06:33:39.080 KST ] [RedoLogGroupSQLGenerator.filespecsClause:73] LOG FILE size in MB = 200
[progressPage.flowWorker] [ 2025-02-24 06:33:39.080 KST ] [RedoLogGroupSQLGenerator.filespecsClause:79] RedoFile space clause ('/app/oracle/oradata/ORACLE19/redo01.log') SIZE 200M
..
[progressPage.flowWorker] [ 2025-02-24 06:33:39.081 KST ] [CloneDBCreationStep.executeImpl:516] createCTLSql=Create controlfile reuse set database "oracle19"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'/app/oracle/oradata/ORACLE19/sysaux01.dbf',
'/app/oracle/oradata/ORACLE19/system01.dbf',
'/app/oracle/oradata/ORACLE19/undotbs01.dbf',
'/app/oracle/oradata/ORACLE19/users01.dbf'
LOGFILE GROUP 1 ('/app/oracle/oradata/ORACLE19/redo01.log') SIZE 200M,
GROUP 2 ('/app/oracle/oradata/ORACLE19/redo02.log') SIZE 200M,
GROUP 3 ('/app/oracle/oradata/ORACLE19/redo03.log') SIZE 200M RESETLOGS;
|
로그를 보면 Seed_Database.ctl를 이용해 컨트롤파일을 restore하고, 이후 Seed_Database.dfb를 이용해 datafile을 set newname 한뒤 restore를 진행함
이때까지의 dbname은 seeddata임
이후 redo로그 경로도 내가 설정한 경로로 변경시키고 내가 설정한 dbname으로(oracle19) 컨트롤파일을 재생성 시켜줌
그리고 패치를 적용시키는 등의 작업을 수행함
본문에서는 이 Seed_Database.ctl와 Seed_Database.dfb 파일들을 이용해 db를 복구해서 open 시켜봄
테스트
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
|
$ cd $ORACLE_HOME/dbs
$ vi initSEEDDATA.ora
*.audit_file_dest='/ORA19/app/oracle/admin/SEEDDATA/adump'
*.audit_trail='none'
*.compatible='19.0.0'
*.control_files='/oradata1/SEEDDATA/control01.ctl','/oradata1/SEEDDATA/control02.ctl'
*.db_block_size=8192
*.db_name='SEEDDATA'
*.diagnostic_dest='/ORA19/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oracle19XDB)'
*.local_listener='LISTENER_ORATEST'
*.log_archive_dest_1='location=/oradata1/arch/SEEDDATA'
*.log_archive_format='SEEDDATA_l%t_%s_%r.arc'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=794m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1300m
*.shared_pool_size=200m
*.undo_retention=900
*.undo_tablespace='UNDOTBS1'
*.enable_pluggable_database=true
|
경로는 알맞게 설정 필요
enable_pluggable_database 파라미터를 빼거나 false로 설정하면 db mount 단계에서 에러가 나서 진행이 안됨
필요 폴더 생성
|
1
2
3
|
$ mkdir -p /ORA19/app/oracle/admin/SEEDDATA/adump
$ mkdir -p /oradata1/SEEDDATA/
$ mkdir -p /oradata1/arch/SEEDDATA
|
db nomount로 기동
|
1
2
3
4
5
6
7
8
9
|
$ export ORACLE_SID=SEEDDATA
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1375728232 bytes
Fixed Size 9134696 bytes
Variable Size 251658240 bytes
Database Buffers 1107296256 bytes
Redo Buffers 7639040 bytes
|
기동됨
rman에서 컨트롤파일 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 25 21:39:32 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: SEEDDATA (not mounted)
RMAN> restore controlfile from '/ORA19/app/oracle/product/19.0.0/db_1/assistants/dbca/templates/Seed_Database.ctl';
Starting restore at 2025-02-25 21:39:37
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=379 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=/oradata1/SEEDDATA/control01.ctl
output file name=/oradata1/SEEDDATA/control02.ctl
Finished restore at 2025-02-25 21:39:38
|
마운트로 변경
|
1
2
3
4
|
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
|
rman 카탈로그에 Seed_Database.dfb 백업피스 등록
|
1
2
3
4
|
RMAN> catalog backuppiece '/ORA19/app/oracle/product/19.0.0/db_1/assistants/dbca/templates/Seed_Database.dfb';
channel default: cataloged backup piece
backup piece handle=/ORA19/app/oracle/product/19.0.0/db_1/assistants/dbca/templates/Seed_Database.dfb RECID=3 STAMP=1193917369
|
백업본 확인
|
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
|
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1 Incr 0 82.53M DISK 00:00:11 2019-04-17 02:08:01
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TOCLONE
Piece Name: /ade/b/1818271618/oracle/oradata/pdbseed.dfb
List of Datafiles in backup set 1
Container ID: 4099, PDB Name: UNKNOWN
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
2 0 Incr 1918377 2019-04-17 02:07:46 NO
4 0 Incr 1918377 2019-04-17 02:07:46 NO
9 0 Incr 1918377 2019-04-17 02:07:46 NO
BS Key Type LV Size
------- ---- -- ----------
2 Full 261.83M
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 1920976 2019-04-17 02:12:25 NO /ade/b/1818271618/oracle/oradata/SEEDDATA/system01.dbf
3 Full 1920976 2019-04-17 02:12:25 NO /ade/b/1818271618/oracle/oradata/SEEDDATA/sysaux01.dbf
4 Full 1920976 2019-04-17 02:12:25 NO /ade/b/1818271618/oracle/oradata/SEEDDATA/undotbs01.dbf
7 Full 1920976 2019-04-17 02:12:25 NO /ade/b/1818271618/oracle/oradata/SEEDDATA/users01.dbf
Backup Set Copy #1 of backup set 2
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ ------------------- ---------- ---
DISK 00:00:31 2019-04-17 02:13:30 YES TAG20190417T021258
List of Backup Pieces for backup set 2 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
2 1 AVAILABLE /ade/b/1818271618/oracle/oradata/Seed_Database.dfb
Backup Set Copy #2 of backup set 2
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ ------------------- ---------- ---
DISK 00:00:31 2019-04-17 02:13:29 YES TAG20190417T021258
List of Backup Pieces for backup set 2 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
3 1 AVAILABLE /ORA19/app/oracle/product/19.0.0/db_1/assistants/dbca/templates/Seed_Database.dfb
|
데이터파일명을 set newname으로 변경해주고 restore 진행
|
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
|
RMAN> run
{
allocate channel ch1 device type disk;
set newname for datafile 1 to '/oradata1/SEEDDATA/system01.dbf';
set newname for datafile 3 to '/oradata1/SEEDDATA/sysaux01.dbf';
set newname for datafile 4 to '/oradata1/SEEDDATA/undotbs01.dbf';
set newname for datafile 7 to '/oradata1/SEEDDATA/users01.dbf';
restore database;
switch datafile all;
}
allocated channel: ch1
channel ch1: SID=2 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2025-02-25 21:42:33
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to /oradata1/SEEDDATA/system01.dbf
channel ch1: restoring datafile 00003 to /oradata1/SEEDDATA/sysaux01.dbf
channel ch1: restoring datafile 00004 to /oradata1/SEEDDATA/undotbs01.dbf
channel ch1: restoring datafile 00007 to /oradata1/SEEDDATA/users01.dbf
channel ch1: reading from backup piece /ade/b/1818271618/oracle/oradata/Seed_Database.dfb
channel ch1: errors found reading piece handle=/ade/b/1818271618/oracle/oradata/Seed_Database.dfb
channel ch1: failover to piece handle=/ORA19/app/oracle/product/19.0.0/db_1/assistants/dbca/templates/Seed_Database.dfb tag=TAG20190417T021258
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:35
Finished restore at 2025-02-25 21:43:08
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1194039788 file name=/oradata1/SEEDDATA/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1194039788 file name=/oradata1/SEEDDATA/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=1194039788 file name=/oradata1/SEEDDATA/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=1194039788 file name=/oradata1/SEEDDATA/users01.dbf
released channel: ch1
|
정상적으로 수행됨
명령어 설명 :
allocate channel : rman 채널 할당
set newname for : 데이터파일명 변경
restore database : 데이터파일 restore
switch datafile all : 새 데이터파일 이름으로 컨트롤 파일 업데이트
recover until cancel 진행(sqlplus)
|
1
2
3
4
5
6
7
8
9
|
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1920976 generated at 04/17/2019 02:12:25 needed for thread 1
ORA-00289: suggestion : /oradata1/arch/SEEDDATA/SEEDDATA_1_27_1005785759.arc
ORA-00280: change 1920976 for thread 1 is in sequence #27
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel <<-- cancel 입력
Media recovery cancelled.
|
리두로그 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col member for a60
select thread#, l.group#, member, archived, l.status, (bytes/1024/1024) MB
from v$log l, v$logfile f
where f.group# = l.group#
order by 1, 2;
THREAD# GROUP# MEMBER ARC STATUS MB
---------- ---------- ------------------------------------------------------------ --- ---------------- ----------
1 1 /ade/b/1818271618/oracle/oradata/SEEDDATA/redo01.log NO INACTIVE 200
1 2 /ade/b/1818271618/oracle/oradata/SEEDDATA/redo02.log NO INACTIVE 200
1 3 /ade/b/1818271618/oracle/oradata/SEEDDATA/redo03.log NO CLEARING_CURRENT 200
|
현재 /ade/~ 경로로 되어 있음
1~3 redo 모두 신규 경로로 재생성
|
1
2
3
4
5
6
|
SQL> alter database drop logfile group 1;
SQL> !rm /ade/b/1818271618/oracle/oradata/SEEDDATA/redo01.log
SQL> alter database add logfile group 1 '/oradata1/SEEDDATA/redo01.log' size 200m;
SQL> alter database drop logfile group 2;
SQL> !rm /ade/b/1818271618/oracle/oradata/SEEDDATA/redo02.log
SQL> alter database add logfile group 2 '/oradata1/SEEDDATA/redo02.log' size 200m;
|
1번, 2번 redo는 정상적으로 재생성하였음
하지만 3번은 CLEARING_CURRENT 상태이기 때문에 drop 명령 실행시 에러가 발생함
|
1
2
3
4
5
|
SQL> alter database drop logfile group 3;
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance SEEDDATA (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/ade/b/1818271618/oracle/oradata/SEEDDATA/redo03.log'
|
mount 상태이기 때문에 log switch가 불가능함
|
1
2
3
4
5
|
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open
|
clear 명령 수행
|
1
2
3
4
5
6
7
8
|
SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '/ade/b/1818271618/oracle/oradata/SEEDDATA/redo03.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9
|
리두를 생성할 경로가 없어서 안됨
해당 경로 생성 및 권한 부여
|
1
2
|
# mkdir -p /ade/b/1818271618/oracle/oradata/SEEDDATA/
# chown -R oracle:dba /ade/b/1818271618/oracle/oradata/SEEDDATA/
|
clear 명령 재시도
|
1
2
3
|
SQL> alter database clear logfile group 3;
Database altered.
|
정상적으로 clear 됨
리두로그 상태 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col member for a60
select thread#, l.group#, member, archived, l.status, (bytes/1024/1024) MB
from v$log l, v$logfile f
where f.group# = l.group#
order by 1, 2;
THREAD# GROUP# MEMBER ARC STATUS MB
---------- ---------- ------------------------------------------------------------ --- ---------------- ----------
1 1 /oradata1/SEEDDATA/redo01.log YES UNUSED 200
1 2 /oradata1/SEEDDATA/redo02.log YES UNUSED 200
1 3 /ade/b/1818271618/oracle/oradata/SEEDDATA/redo03.log NO CURRENT 200
|
3번이 current 상태로 됨
실제 파일도 생성됨
|
1
2
|
SQL> !ls -al /ade/b/1818271618/oracle/oradata/SEEDDATA/redo03.log
-rw-r----- 1 oracle oinstall 209715712 Feb 24 16:02 /ade/b/1818271618/oracle/oradata/SEEDDATA/redo03.log
|
하지만 db가 mount 상태이고 redo가 current 상태이기 때문에 log switch나 drop이 불가함
일단 resetlogs로 기동
|
1
2
3
|
SQL> alter database open resetlogs;
Database altered.
|
정상적으로 기동됨
리두로그 상태 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col member for a60
select thread#, l.group#, member, archived, l.status, (bytes/1024/1024) MB
from v$log l, v$logfile f
where f.group# = l.group#
order by 1, 2;
THREAD# GROUP# MEMBER ARC STATUS MB
---------- ---------- ------------------------------------------------------------ --- ---------------- ----------
1 1 /oradata1/SEEDDATA/redo01.log NO CURRENT 200
1 2 /oradata1/SEEDDATA/redo02.log YES UNUSED 200
1 3 /ade/b/1818271618/oracle/oradata/SEEDDATA/redo03.log YES UNUSED 200
|
3번이 unused로 변경됨
3번 redo 재생성
|
1
2
3
|
SQL> alter database drop logfile group 3;
SQL> !rm /ade/b/1818271618/oracle/oradata/SEEDDATA/redo03.log
SQL> alter database add logfile group 3 '/oradata1/SEEDDATA/redo03.log' size 200m;
|
리두로그 상태 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col member for a60
select thread#, l.group#, member, archived, l.status, (bytes/1024/1024) MB
from v$log l, v$logfile f
where f.group# = l.group#
order by 1, 2;
THREAD# GROUP# MEMBER ARC STATUS MB
---------- ---------- ------------------------------------------------------------ --- ---------------- ----------
1 1 /oradata1/SEEDDATA/redo01.log NO CURRENT 200
1 2 /oradata1/SEEDDATA/redo02.log YES UNUSED 200
1 3 /oradata1/SEEDDATA/redo03.log YES UNUSED 200
|
redo가 모두 정상화됨
db 상태 확인
|
1
2
3
4
5
|
SQL> select instance_name, version, status from v$instance;
INSTANCE_NAME VERSION STATUS
---------------- ----------------- ------------
SEEDDATA 19.0.0.0.0 OPEN
|
open 상태임
cdb인지 확인
|
1
2
3
4
5
|
SQL> select name, cdb from v$database
NAME CDB
-------------------- ---
SEEDDATA YES
|
cdb 환경임
pdb 확인
|
1
2
3
4
5
6
7
|
SQL>
set lines 200 pages 1000
col name for a20
select con_id, name, open_mode, to_char(open_time, 'yyyy/mm/dd hh24:mi:ss') open_time, total_size/1024/1024/1024 gb
from v$pdbs;
no rows selected
|
pdb는 존재하지 않음
컴포넌트 확인
|
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
|
SQL>
set lines 200 pages 1000
col comp_id for a15
col comp_name for a50
col version for a10
col status for a10
select comp_id, comp_name, version, status
from dba_registry
order by comp_id;
COMP_ID COMP_NAME VERSION STATUS
--------------- -------------------------------------------------- ---------- ----------
APS OLAP Analytic Workspace 19.0.0.0.0 VALID
CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and Types 19.0.0.0.0 VALID
CONTEXT Oracle Text 19.0.0.0.0 VALID
DV Oracle Database Vault 19.0.0.0.0 VALID
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID
OLS Oracle Label Security 19.0.0.0.0 VALID
ORDIM Oracle Multimedia 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 19.0.0.0.0 VALID
RAC Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
SDO Spatial 19.0.0.0.0 VALID
XDB Oracle XML Database 19.0.0.0.0 VALID
XML Oracle XDK 19.0.0.0.0 VALID
XOQ Oracle OLAP API 19.0.0.0.0 VALID
15 rows selected.
|
모두 valid 상태임
datafile 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col file_name for a70
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible, status, online_status from dba_data_files
union all
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible, status, null from dba_temp_files order by 2;
TABLESPACE_NAME FILE_ID FILE_NAME GB MAX_GB AUT STATUS ONLINE_
-------------------- ---------- ---------------------------------------------------------------------- ---------- ---------- --- --------- -------
SYSTEM 1 /oradata1/SEEDDATA/system01.dbf .86 32 YES AVAILABLE SYSTEM
TEMP 1 /ade/b/1818271618/oracle/oradata/SEEDDATA/temp01.dbf .02 32 YES ONLINE
SYSAUX 3 /oradata1/SEEDDATA/sysaux01.dbf .41 32 YES AVAILABLE ONLINE
UNDOTBS1 4 /oradata1/SEEDDATA/undotbs01.dbf .02 32 YES AVAILABLE ONLINE
USERS 7 /oradata1/SEEDDATA/users01.dbf 0 32 YES AVAILABLE ONLINE
|
temp 경로가 seeddata 구 경로라서 변경이 필요할듯함
그 외에는 모두 정상임
temp 재생성 및 기본 temp 테이블스페이스를 새로만든 temp2로 지정
|
1
2
3
|
SQL> create temporary tablespace temp2 tempfile '/oradata1/SEEDDATA/temp02.dbf' size 500m;
SQL> alter database default temporary tablespace temp2;
SQL> drop tablespace temp including contents and datafiles;
|
datafile 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col file_name for a70
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible, status, online_status from dba_data_files
union all
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible, status, null from dba_temp_files order by 2;
TABLESPACE_NAME FILE_ID FILE_NAME GB MAX_GB AUT STATUS ONLINE_
-------------------- ---------- ---------------------------------------------------------------------- ---------- ---------- --- ---------- -------
SYSTEM 1 /oradata1/SEEDDATA/system01.dbf .86 32 YES AVAILABLE SYSTEM
SYSAUX 3 /oradata1/SEEDDATA/sysaux01.dbf .39 32 YES AVAILABLE ONLINE
TEMP2 3 /oradata1/SEEDDATA/temp02.dbf .49 0 NO ONLINE
UNDOTBS1 4 /oradata1/SEEDDATA/undotbs01.dbf .02 32 YES AVAILABLE ONLINE
USERS 7 /oradata1/SEEDDATA/users01.dbf 0 32 YES AVAILABLE ONLINE
|
모두 정상임
오브젝트 갯수 확인
|
1
2
3
4
5
|
SQL> select count(*) from dba_objects;
COUNT(*)
----------
72369
|
내가 기존에 non-cdb 19c 설치했을때와 비교해봤을때는 오브젝트 갯수가 조금 많은것 같긴함
하지만 cdb이기 때문에 많은것일수도 있음
손상된 블록이나 복구할 내용 있는지 확인
|
1
2
3
4
5
6
7
|
SQL> select * from v$database_block_corruption;
no rows selected
SQL> select * from v$recover_file;
no rows selected
|
없음
alert log에도 특이사항 없음
===참고용===
enable_pluggable_database 파라미터를를 false로 하거나 빼고 mount 명령을 수행하면 에러가 나지만 실제론 마운트는됨
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
RMAN> alter database mount;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 02/25/2025 22:04:06
ORA-65093: multitenant container database not set up properly
SQL> select status from v$instance;
STATUS
------------
MOUNTED
|
하지만 이후 run ~ restore 단계에서 ORA-00600이 발생함
|
1
2
3
4
5
6
|
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/25/2025 22:05:52
ORA-19870: error while restoring backup piece /ORA19/app/oracle/product/19.0.0/db_1/assistants/dbca/templates/Seed_Database.dfb
ORA-00600: internal error code, arguments: [kpdbIdToName], [1], [], [], [], [], [], [], [], [], [], []
|
백업본은 cdb 베이스인데 non-cdb pfile로 기동해서 그런듯함
===참고용===
결론 :
$ORACLE_HOME/assistants/dbca/templates 경로에 존재하는 Seed_Database.ctl와 Seed_Database.dfb 파일로 db를 생성할수도 있음
이때 db를 복구 및 open 시켜보면 dbname이 SEEDDATA라는 cdb 형태로 생성됨
복구 단계 중 enable_pluggable_database 파라미터를 빼거나 false로 설정하면 db mount 단계에서 에러가 나서 진행이 안됨
이 seed 파일들은 General_Purpose나 Data_Warehouse 템플릿을 사용해서 dbca를 할때 사용하는 파일임
정확하진 않지만 아마 이 두개의 템플릿으로 db를 구성할때 조금더 빠르게 생성하기 위해 사용하는게 아닐까 싶음
참조 :
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c 데이터 1천만건 delete 시간 측정 (0) | 2025.06.01 |
|---|---|
| 오라클 19c temp 사용시 tempfile 내용 확인 (0) | 2025.05.25 |
| 오라클 23ai 신기능 Select AI 사용 테스트(non-adb) (0) | 2025.04.17 |
| 오라클 19c dbca silent 시 템플릿별 컴포넌트 비활성화 설정 (0) | 2025.04.14 |
| 오라클 23ai 신기능 dbms_cloud 설치(non-adb) (0) | 2025.04.13 |