반응형
제목
(2026-01-22) Oracle 12cR2 non-cdb to 19c PDB Migration 및 Upgrade Using Refeshable PDB (Auto Upgrade 사용)
테스트 환경
소스
-
OS : Oracle Linux Server 7.9 (
grep ^PRETTY_NAME= /etc/os-release | cut -d= -f2- | tr -d '"') -
Kernel : 4.14.35-1902.303.4.el7uek.x86_64 (
uname -r) -
Oracle Version : Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
(echo -e "set pages 0 lines 200 feedback off heading off\nselect banner from v\$version where banner like 'Oracle Database%';" | sqlplus -s / as sysdba) -
Oracle Configuration
DB명 : ORA12RF (echo -e 'set pages 0 feedback off heading off verify off\nselect '\''DB명 : '\''||name from v$database;' | sqlplus -s / as sysdba)
PDB 정보
Non-CDBRAC Node 1
Hostname : ol7ora12rf1 (hostname)
Public IP : 192.168.240.11 (getent ahostsv4hostname| awk '{print $1; exit}')
Instance Name : ORA12RF1 (echo -e 'set pages 0 feedback off heading off verify off\nselect '\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as sysdba)
RAC Node 2
Hostname : ol7ora12rf2 (hostname)
Public IP : 192.168.240.12 (getent ahostsv4hostname| awk '{print $1; exit}')
Instance Name : ORA12RF2 (echo -e 'set pages 0 feedback off heading off verify off\nselect '\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as sysdba) -
Patch Info
Grid (opatch lspatches) (grid os user)
33610989;TOMCAT RELEASE UPDATE 12.2.0.1.0(ID:RELEASE) (33610989)
26839277;DBWLM RELEASE UPDATE 12.2.0.1.0(ID:170913) (26839277)
33116894;ACFS JUL 2021 RELEASE UPDATE 12.2.0.1.210720 (33116894)
33678030;OCW JAN 2022 RELEASE UPDATE 12.2.0.1.220118 (33678030)
33587128;Database Jan 2022 Release Update : 12.2.0.1.220118 (33587128)Oracle (
opatch lspatches) (oracle os user)
33587128;Database Jan 2022 Release Update : 12.2.0.1.220118 (33587128)
33678030;OCW JAN 2022 RELEASE UPDATE 12.2.0.1.220118 (33678030)
타켓
- OS : Oracle Linux Server 8.10 (
grep ^PRETTY_NAME= /etc/os-release | cut -d= -f2- | tr -d '"') - Kernel : 5.15.0-206.153.7.1.el8uek.x86_64 (
uname -r) - Oracle Version : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
(echo -e "set pages 0 lines 200 feedback off heading off\nselect banner from v\$version where banner like 'Oracle Database%';" | sqlplus -s / as sysdba) - Oracle Configuration
- DB명 : ORA19RS (
echo -e 'set pages 0 feedback off heading off verify off\nselect '\''DB명 : '\''||name from v$database;' | sqlplus -s / as sysdba) - PDB 정보
ORA12RF - RAC Node 1
Hostname : ol8ora19rs1 (hostname)
Public IP : 192.168.240.41 (getent ahostsv4hostname| awk '{print $1; exit}')
Instance Name : ORA19RS1 (echo -e 'set pages 0 feedback off heading off verify off\nselect '\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as sysdba) - RAC Node 2
Hostname : ol8ora19rs2 (hostname)
Public IP : 192.168.240.42 (getent ahostsv4hostname| awk '{print $1; exit}')
Instance Name : ORA19RS2 (echo -e 'set pages 0 feedback off heading off verify off\nselect '\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as sysdba)
- DB명 : ORA19RS (
- Patch Info
-
Grid (
opatch lspatches) (grid os user)
38124772;TOMCAT RELEASE UPDATE 19.0.0.0.0 (38124772)
37962946;OCW RELEASE UPDATE 19.28.0.0.0 (37962946)
37962938;ACFS RELEASE UPDATE 19.28.0.0.0 (37962938)
37960098;Database Release Update : 19.28.0.0.250715 (37960098)
36758186;DBWLM RELEASE UPDATE 19.0.0.0.0 (36758186) -
Oracle (
opatch lspatches) (oracle os user)
37962946;OCW RELEASE UPDATE 19.28.0.0.0 (37962946)
37960098;Database Release Update : 19.28.0.0.250715 (37960098)
-
테스트 개요
테스트 개요
본 문서는 Oracle 12.2.0.1 non-CDB 환경의 데이터베이스를 Oracle 19c CDB 환경의 PDB로 마이그레이션 및 업그레이드하는 절차를 기술한다.
다운타임을 최소화하기 위해 AutoUpgrade 툴의 deploy 모드와 Refreshable PDB 기능을 활용하여, 운영 중 데이터를 사전에 동기화하고 Cut-over 시점에 최종 전환하는 방식을 검증한다.
주요 수행 단계 요약
사전 준비 (Source DB - 12c)
- 안전 조치: * RMAN Full Backup 수행 (
/home/oracle/BACKUP_BEFORE_UPGRADE)- Guaranteed Restore Point 생성 (
BEFORE_UPGRADE) - Flashback Database 활성화
- Guaranteed Restore Point 생성 (
- 설정 변경: Block Change Tracking (BCT) 비활성화
- 연결 구성: Target DB에서 접속할 수 있도록 DB Link용 사용자 (
dblinkuser) 및 권한 생성
환경 구성 (Target DB - 19c)
- AutoUpgrade 툴 배치: 최신 버전(v25.6)의
autoupgrade.jar를 Source/Target 양측에 배치 - 네트워크 설정:
tnsnames.ora에 Source DB 접속 정보(ORA12RF) 추가 - DB Link 생성: Source DB를 바라보는 DB Link (
clonepdb) 생성 및 검증
업그레이드 분석 및 수정 (AutoUpgrade)
- Config 파일 작성 (
ora12rf.cfg):target_pdb_copy_option등을 통해 파일 복사 방식 정의source_dblink설정으로 Remote Clone 준비
- Analyze 모드: 업그레이드 사전 검증 (
-mode analyze) 수행 - Fixups 모드: 발견된 이슈에 대한 자동 수정 (
-mode fixups) 수행
데이터 동기화 (Refreshable PDB Deploy)
- Deploy 모드 실행: AutoUpgrade를 통해 PDB 생성 및 데이터 복제 시작 (
-mode deploy) - Refresh 구성: Target PDB가
MOUNTED상태에서 Source의 변경분을 주기적으로 반영하도록 설정- 설정 확인:
REFRESH_MODE,LAST_REFRESH_SCN확인
- 설정 확인:
- 동기화 검증: * Source에 테스트 테이블(
tuner.tb_before_cut_over) 생성- Target PDB 수동 Refresh 후 SCN 변경 및 데이터 반영 확인
전환 (Cut-over) 및 업그레이드 완료
- 서비스 중단: Source DB(
ORA12RF) 종료 (Shutdown) - 최종 전환: AutoUpgrade 콘솔에서
proceed명령 수행- PDB가 Read-Write 모드로 오픈되며 19c 업그레이드 스크립트 자동 수행
- 완료 확인: Job status 완료 및 Summary 리포트 확인
후속 조치 (Post-Upgrade)
- 접속 환경 구성: PDB 접속용 TNS Alias(
ORA19RS1_ORA12RF) 추가 및glogin.sql원복 - 정합성 검증:
- DB 버전 확인 (19.0.0.0.0)
DBA_REGISTRY컴포넌트 상태 확인 (VALID)- Invalid Object 재컴파일 및
0건 확인
- 파라미터 최적화:
- 12c와 19c 간의 파라미터 차이 분석
- 필수 히든 파라미터(
_optimizer_gather_stats_on_load=FALSE)를 PDB 레벨(PDB_SPFILE$)에 적용하여 성능 통계 수집 동작 제어
3. 결론
- AutoUpgrade와 Refreshable PDB 기능을 결합하여, 대용량 데이터베이스의 마이그레이션 시간을 획기적으로 단축할 수 있음을 확인하였다.
- 특히
deploy단계에서 지속적인 아카이브 로그 적용을 통해 Cut-over 시점의 데이터 갭을 최소화하였으며, 업그레이드 후 파라미터 보정을 통해 12c 환경과의 운영 일관성을 확보하였다.
목차
- 제목
- 테스트 환경
- 테스트 개요
- 목차
- 내용
내용
1. 업그레이드 전 소스 DB 작업
1-1. DB 풀 백업
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ mkdir -pv /home/oracle/BACKUP_BEFORE_UPGRADE/autobackup
mkdir: created directory ‘/home/oracle/BACKUP_BEFORE_UPGRADE’
mkdir: created directory ‘/home/oracle/BACKUP_BEFORE_UPGRADE/autobackup’
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 22 22:37:20 2026
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12rf1]<SYS@ORA12RF1>$ alter database backup controlfile to trace as '/home/oracle/BACKUP_BEFORE_UPGRADE/controlfile_script_bak.sql';
Database altered.
Elapsed: 00:00:00.03
[ol7ora12rf1]<SYS@ORA12RF1>$ create pfile='/home/oracle/BACKUP_BEFORE_UPGRADE/pfile_before_upgrade.ora' from spfile;
File created.
Elapsed: 00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias rt
alias rt='rman target /'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ rt
Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jan 22 22:38:20 2026
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA12RF (DBID=4288610047)
RMAN>
run {
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/BACKUP_BEFORE_UPGRADE/autobackup/%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/BACKUP_BEFORE_UPGRADE/snapcf_CA.f';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
#CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; #For ADG
}
run {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
ALLOCATE CHANNEL c3 DEVICE TYPE DISK;
ALLOCATE CHANNEL c4 DEVICE TYPE DISK;
sql 'alter system checkpoint';
sql 'alter system archive log current';
crosscheck backupset;
crosscheck backup;
crosscheck copy;
crosscheck archivelog all;
BACKUP INCREMENTAL LEVEL 0 AS COMPRESSED BACKUPSET FORMAT '/home/oracle/BACKUP_BEFORE_UPGRADE/%d_L0_%T_%U.bkp' DATABASE;
delete noprompt obsolete;
delete noprompt expired backup;
sql 'alter system archive log current';
BACKUP AS COMPRESSED BACKUPSET FORMAT '/home/oracle/BACKUP_BEFORE_UPGRADE/arch_%d_%T_%U.bkp' ARCHIVELOG ALL delete input;
delete backup of archivelog all completed before 'SYSDATE-14';
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}
RMAN> quit
Recovery Manager complete.
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ ls -lR /home/oracle/BACKUP_BEFORE_UPGRADE
/home/oracle/BACKUP_BEFORE_UPGRADE:
total 1379800
-rw-r-----. 1 oracle asmadmin 52224 Jan 22 22:54 arch_ORA12RF_20260122_bu4eihct_1_1.bkp
-rw-r-----. 1 oracle asmadmin 50176 Jan 22 22:54 arch_ORA12RF_20260122_bv4eihct_1_1.bkp
-rw-r-----. 1 oracle asmadmin 7680 Jan 22 22:54 arch_ORA12RF_20260122_c04eihct_1_1.bkp
-rw-r-----. 1 oracle asmadmin 5120 Jan 22 22:54 arch_ORA12RF_20260122_c14eihct_1_1.bkp
drwxr-xr-x. 2 oracle oinstall 70 Jan 22 22:54 autobackup
-rw-r--r--. 1 oracle asmadmin 14253 Jan 22 22:52 controlfile_script_bak.sql
-rw-r-----. 1 oracle asmadmin 2023424 Jan 22 22:53 ORA12RF_L0_20260122_bp4eiham_1_1.bkp
-rw-r-----. 1 oracle asmadmin 1101373440 Jan 22 22:54 ORA12RF_L0_20260122_bq4eiham_1_1.bkp
-rw-r-----. 1 oracle asmadmin 244137984 Jan 22 22:53 ORA12RF_L0_20260122_br4eiham_1_1.bkp
-rw-r-----. 1 oracle asmadmin 44793856 Jan 22 22:53 ORA12RF_L0_20260122_bs4eiham_1_1.bkp
-rw-r--r--. 1 oracle asmadmin 2863 Jan 22 22:52 pfile_before_upgrade.ora
-rw-r-----. 1 oracle asmadmin 20430848 Jan 22 22:54 snapcf_CA.f
/home/oracle/BACKUP_BEFORE_UPGRADE/autobackup:
total 40104
-rw-r-----. 1 oracle asmadmin 20529152 Jan 22 22:54 c-4288610047-20260122-06
-rw-r-----. 1 oracle asmadmin 20529152 Jan 22 22:54 c-4288610047-20260122-07
1-2. Flashback Database 설정 및 Restore Point 생성
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 22 23:00:59 2026
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12rf1]<SYS@ORA12RF1>$ archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA1
Oldest online log sequence 22
Next log sequence to archive 24
Current log sequence 24
[ol7ora12rf1]<SYS@ORA12RF1>$
col dbid for 99999999999
col name for a20
col FLASHBACK_ON for a30
select dbid, NAME, FLASHBACK_ON from v$database;
DBID NAME FLASHBACK_ON
------------ -------------------- ------------------------------
4288610047 ORA12RF NO
1 row selected.
Elapsed: 00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ show parameter recovery
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string (NULL)
db_recovery_file_dest_size big integer 0
recovery_parallelism integer 0
remote_recovery_file_dest string (NULL)
[ol7ora12rf1]<SYS@ORA12RF1>$ alter system set db_recovery_file_dest='+FRA1' scope=spfile sid='*';
System altered.
Elapsed: 00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ alter system set db_recovery_file_dest_size=32G scope=both sid='*';
System altered.
Elapsed: 00:00:00.02
[ol7ora12rf1]<SYS@ORA12RF1>$ show parameter flashback
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_flashback_retention_target integer 1440
[ol7ora12rf1]<SYS@ORA12RF1>$ alter system set db_flashback_retention_target=2880 scope=both sid='*';
System altered.
Elapsed: 00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ srvctl stop database -db ora12rf
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ srvctl start database -db ora12rf -startoption mount
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 22 23:03:27 2026
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12rf1]<SYS@ORA12RF1>$ alter database flashback on;
Database altered.
Elapsed: 00:00:06.30
[ol7ora12rf1]<SYS@ORA12RF1>$ quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ srvctl stop database -d ora12rf -o immediate
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ srvctl start database -d ora12rf
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 22 23:04:44 2026
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12rf1]<SYS@ORA12RF1>$
col dbid for 99999999999
col name for a20
col FLASHBACK_ON for a30
select dbid, NAME, FLASHBACK_ON from v$database;
DBID NAME FLASHBACK_ON
------------ -------------------- ------------------------------
4288610047 ORA12RF YES
1 row selected.
Elapsed: 00:00:00.00
[ol7ora12rf1]<SYS@ORA12RF1>$ CREATE RESTORE POINT BEFORE_UPGRADE GUARANTEE FLASHBACK DATABASE;
Restore point created.
Elapsed: 00:00:00.02
[ol7ora12rf1]<SYS@ORA12RF1>$
SET LINESIZE 200
COL NAME FORMAT A20
COL TIME FORMAT A35
COL GUARANTEE_FLASHBACK_DATABASE FORMAT A3
COL SCN FORMAT 999999999999999
SELECT NAME
, TIME
, GUARANTEE_FLASHBACK_DATABASE AS GFD
, SCN
FROM V$RESTORE_POINT;
NAME TIME GFD SCN
-------------------- ----------------------------------- --------- ----------------
BEFORE_UPGRADE 26/01/22 23:05:16.000000000 YES 25729451
1 row selected.
Elapsed: 00:00:00.01
1-3. BCT (Block Change Tracking) 비활성화
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 22 23:06:01 2026
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12rf1]<SYS@ORA12RF1>$
col status for a10
col filename for a50
select status, filename from v$block_change_tracking;
만약 활성화 되어 있다면 아래의 명령으로 비활성화 시킴
alter database disable block change tracking;
2. Auto Upgrade Tool 최신 버전 설치
AutoUpgrade Tool (Doc ID 2485457.1) 에서 autoupgrade.jar 파일 다운로드
2-1. 소스 서버
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ mkdir -pv /home/oracle/autoupgrade_jar
mkdir: created directory ‘/home/oracle/autoupgrade_jar’
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ cd /home/oracle/autoupgrade_jar
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle/autoupgrade_jar]$ ls -l /home/oracle/autoupgrade_jar
total 6572
-rw-r--r--. 1 oracle oinstall 6729677 Jan 11 21:55 autoupgrade.jar
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ $ORACLE_HOME/jdk/bin/java -version
java version "1.8.0_311"
Java(TM) SE Runtime Environment (build 1.8.0_311-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.311-b11, mixed mode)
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle/autoupgrade_jar]$ $ORACLE_HOME/jdk/bin/java -jar /home/oracle/autoupgrade_jar/autoupgrade.jar -version
build.version 25.6.251016
build.date 2025/10/16 00:04:36 +0000
build.hash 54a1e2442
build.hash_date 2025/10/15 21:11:40 +0000
build.supported_target_versions 12.2,18,19,21,23
build.type production
build.label (HEAD, tag: v25.6, origin/stable_devel, stable_devel)
build.MOS_NOTE 2485457.1
build.MOS_LINK https://support.oracle.com/epmos/faces/DocumentDisplay?id=2485457.1
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle/autoupgrade_jar]$ cp /home/oracle/autoupgrade_jar/autoupgrade.jar \
> $ORACLE_HOME/rdbms/admin/
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle/autoupgrade_jar]$ ls -l $ORACLE_HOME/rdbms/admin/autoupgrade.jar
-rw-r--r--. 1 oracle oinstall 6729677 Jan 22 19:09 /u01/app/oracle/product/12c/db_1/rdbms/admin/autoupgrade.jar
2-2. 타켓 서버
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ mkdir -pv /home/oracle/autoupgrade_jar
mkdir: created directory '/home/oracle/autoupgrade_jar'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ cd /home/oracle/autoupgrade_jar
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle/autoupgrade_jar]$ ls -l /home/oracle/autoupgrade_jar
total 6572
-rw-r--r--. 1 oracle oinstall 6729677 Jan 11 21:55 autoupgrade.jar
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ $ORACLE_HOME/jdk/bin/java -version
java version "1.8.0_451"
Java(TM) SE Runtime Environment (build 1.8.0_451-b10)
Java HotSpot(TM) 64-Bit Server VM (build 25.451-b10, mixed mode)
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle/autoupgrade_jar]$ $ORACLE_HOME/jdk/bin/java -jar /home/oracle/autoupgrade_jar/autoupgrade.jar -version
build.version 25.6.251016
build.date 2025/10/16 00:04:36 +0000
build.hash 54a1e2442
build.hash_date 2025/10/15 21:11:40 +0000
build.supported_target_versions 12.2,18,19,21,23
build.type production
build.label (HEAD, tag: v25.6, origin/stable_devel, stable_devel)
build.MOS_NOTE 2485457.1
build.MOS_LINK https://support.oracle.com/epmos/faces/DocumentDisplay?id=2485457.1
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle/autoupgrade_jar]$ cp $ORACLE_HOME/rdbms/admin/autoupgrade.jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar.bak
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle/autoupgrade_jar]$ cp /home/oracle/autoupgrade_jar/autoupgrade.jar $ORACLE_HOME/rdbms/admin/
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle/autoupgrade_jar]$ ls -l $ORACLE_HOME/rdbms/admin/ | grep -i autoupgrade.jar
-rw-r--r--. 1 oracle oinstall 6729677 Jan 22 19:11 autoupgrade.jar
-rw-r--r--. 1 oracle oinstall 6530318 Jan 22 19:11 autoupgrade.jar.bak
3. 소스 서버와 타켓 서버 간 DB Link 연결
3-1. 소스 서버에서 DB Link용 User 생성
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 22 19:14:02 2026
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Elapsed: 00:00:00.00
[ol7ora12rf1]<SYS@ORA12RF1>$ create user dblinkuser identified by "1234";
User created.
Elapsed: 00:00:00.09
[ol7ora12rf1]<SYS@ORA12RF1>$ grant create session to dblinkuser;
Grant succeeded.
Elapsed: 00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ GRANT CREATE PLUGGABLE DATABASE TO dblinkuser;
Grant succeeded.
Elapsed: 00:00:00.03
[ol7ora12rf1]<SYS@ORA12RF1>$ grant select_catalog_role to dblinkuser;
Grant succeeded.
Elapsed: 00:00:00.02
[ol7ora12rf1]<SYS@ORA12RF1>$ grant read on sys.enc$ to dblinkuser;
Grant succeeded.
Elapsed: 00:00:00.03
[ol7ora12rf1]<SYS@ORA12RF1>$
3-2. 타켓 서버에서 tnsnames.ora 설정
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ cat >> $ORACLE_HOME/network/admin/tnsnames.ora << 'EOF'
ORA12RF =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.240.14)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.240.15)(PORT = 1521))
(LOAD_BALANCE = OFF)
(FAILOVER = ON)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA12RF)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC))
)
)
EOF
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORA19RS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol8ora19rs-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19RS)
)
)
ORA19RS1P1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.240.41)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = ora19rsp1)
)
)
ORA12RF =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.240.14)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.240.15)(PORT = 1521))
(LOAD_BALANCE = OFF)
(FAILOVER = ON)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA12RF)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC))
)
)
3-3. 타켓 서버에 db link 생성
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 22 19:29:54 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1>$ show con_name
CON_NAME
------------------------------
CDB$ROOT
[ol8ora19rs1]<SYS@ORA19RS1>$ show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORA19RSP1 MOUNTED (NULL)
[ol8ora19rs1]<SYS@ORA19RS1>$ create database link clonepdb connect to dblinkuser identified by "1234" using 'ORA12RF';
Database link created.
Elapsed: 00:00:00.03
--db link 정상 동작 확인
[ol8ora19rs1]<SYS@ORA19RS1>$
SET LINESIZE 180
SET PAGESIZE 100
SET FEEDBACK ON
SET TIMING ON
COL INSTANCE_NUMBER FORMAT 9999 HEADING 'INST_ID'
COL INSTANCE_NAME FORMAT A16 HEADING 'INST_NAME'
COL HOST_NAME FORMAT A40 HEADING 'HOST_NAME'
COL VERSION FORMAT A15 HEADING 'VERSION'
COL STATUS FORMAT A12 HEADING 'STATUS'
SELECT INSTANCE_NUMBER
, INSTANCE_NAME
, HOST_NAME
, VERSION
, STATUS
FROM V$INSTANCE@clonepdb;
INST_ID INST_NAME HOST_NAME VERSION STATUS
------- ---------------- ---------------------------------------- --------------- ------------
1 ORA12RF1 ol7ora12rf1 12.2.0.1.0 OPEN
1 row selected.
Elapsed: 00:00:00.05
4. 소스 서버에서 업그레이드 준비 작업
4-1. 소스 서버에 Auto Upgrade Config 파일 생성
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ mkdir -p /home/oracle/autoupgrade_work
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ cat > /home/oracle/autoupgrade_work/ora12rf.cfg << 'EOF'
global.autoupg_log_dir=/home/oracle/autoupgrade_work/logs
upg1.source_home=/u01/app/oracle/product/12c/db_1
upg1.target_home=/u01/app/oracle/product/19c/db_1
upg1.target_version=19
upg1.sid=ORA12RF1
upg1.target_cdb=ORA19RS1
upg1.source_dblink.ORA12RF1=CLONEPDB 600
upg1.target_pdb_name.ORA12RF1=ORA12RF
upg1.start_time=31/12/2099 00:00:00
upg1.target_pdb_copy_option.ORA12RF1=file_name_convert=NONE
upg1.target_is_remote=yes
EOF
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ cat /home/oracle/autoupgrade_work/ora12rf.cfg
global.autoupg_log_dir=/home/oracle/autoupgrade_work/logs
upg1.source_home=/u01/app/oracle/product/12c/db_1
upg1.target_home=/u01/app/oracle/product/19c/db_1
upg1.target_version=19
upg1.sid=ORA12RF1
upg1.target_cdb=ORA19RS1
upg1.source_dblink.ORA12RF1=CLONEPDB 600
upg1.target_pdb_name.ORA12RF1=ORA12RF
upg1.start_time=31/12/2099 00:00:00
upg1.target_pdb_copy_option.ORA12RF1=file_name_convert=NONE
upg1.target_is_remote=yes
4-2. Auto Upgrade Analyze 작업
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ cd /home/oracle/autoupgrade_work
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle/autoupgrade_work]$ $ORACLE_HOME/jdk/bin/java -jar /home/oracle/autoupgrade_jar/autoupgrade.jar \
-mode analyze \
-config /home/oracle/autoupgrade_work/ora12rf.cfg
AutoUpgrade 25.6.251016 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 Non-CDB(s) will be analyzed
Type 'help' to list console commands
upg> lsj
+----+--------+---------+---------+-------+----------+-------+----------------+
|Job#| DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+----+--------+---------+---------+-------+----------+-------+----------------+
| 100|ORA12RF1|PRECHECKS|EXECUTING|RUNNING| 00:00:00| 0s ago|Executing Checks|
+----+--------+---------+---------+-------+----------+-------+----------------+
Total jobs 1
upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Please check the summary report at:
/home/oracle/autoupgrade_work/logs/cfgtoollogs/upgrade/auto/status/status.html
/home/oracle/autoupgrade_work/logs/cfgtoollogs/upgrade/auto/status/status.log
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle/autoupgrade_work]$ ls -l /home/oracle/autoupgrade_work
total 4
-rw-r--r--. 1 oracle oinstall 0 Jan 22 20:16 -config
drwxr-xr-x. 4 oracle oinstall 58 Jan 22 20:17 logs --> 해당 위치에 로그가 남아 있음
-rw-r--r--. 1 oracle oinstall 0 Jan 22 20:16 -mode
-rw-r--r--. 1 oracle oinstall 399 Jan 22 20:16 ora12rf.cfg
4-3. Auto Upgrade FixUps 작업
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ cd /home/oracle/autoupgrade_work
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle/autoupgrade_work]$ $ORACLE_HOME/jdk/bin/java -jar /home/oracle/autoupgrade_jar/autoupgrade.jar \
-mode fixups \
-config /home/oracle/autoupgrade_work/ora12rf.cfg
AutoUpgrade 25.6.251016 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 Non-CDB(s) will be processed
Type 'help' to list console commands
upg> WARNING: Target home entry is not available. This could be because is not specified in the configuration file or the specified path does not exist, this may lead AutoUpgrade to not be able to run the fixups for certain checks which need the target Oracle home presence.
upg> lsj
+----+--------+---------+---------+-------+----------+---------+----------------+
|Job#| DB_NAME| STAGE|OPERATION| STATUS|START_TIME| UPDATED| MESSAGE|
+----+--------+---------+---------+-------+----------+---------+----------------+
| 101|ORA12RF1|PREFIXUPS|EXECUTING|RUNNING| 00:00:00|!145s ago|Executing fixups|
+----+--------+---------+---------+-------+----------+---------+----------------+
Total jobs 1
upg> status -jobs 101
Unrecognized cmd: status -jobs 101
upg> status -job 101
Details
Job No 101
Oracle SID ORA12RF1
Start Time 99/12/31 00:00:00
Elapsed (min): -38,887,408
End time: N/A
Logfiles
Logs Base: /home/oracle/autoupgrade_work/logs/ORA12RF1
Job logs: /home/oracle/autoupgrade_work/logs/ORA12RF1/101
Stage logs: /home/oracle/autoupgrade_work/logs/ORA12RF1/101/prefixups
TimeZone: /home/oracle/autoupgrade_work/logs/ORA12RF1/temp
Remote Dirs:
Stages
SETUP <1 min
DISPATCH <1 min
PRECHECKS <1 min
PREFIXUPS ~3 min (RUNNING)
Stage-Progress Per Container
+--------+---------+
|Database|PREFIXUPS|
+--------+---------+
|ORA12RF1| 14 % |
+--------+---------+
upg> Job 101 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Please check the summary report at:
/home/oracle/autoupgrade_work/logs/cfgtoollogs/upgrade/auto/status/status.html
/home/oracle/autoupgrade_work/logs/cfgtoollogs/upgrade/auto/status/status.log
5. 타켓 서버에서 업그레이드 작업
5-1. 타켓 서버에 Auto Upgrade Config 파일 생성
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ mkdir -p /home/oracle/autoupgrade_work
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ cat > /home/oracle/autoupgrade_work/ora12rf.cfg << 'EOF'
global.autoupg_log_dir=/home/oracle/autoupgrade_work/logs
upg1.source_home=/u01/app/oracle/product/12c/db_1
upg1.target_home=/u01/app/oracle/product/19c/db_1
upg1.target_version=19
upg1.sid=ORA12RF1
upg1.target_cdb=ORA19RS1
upg1.source_dblink.ORA12RF1=CLONEPDB 600
upg1.target_pdb_name.ORA12RF1=ORA12RF
upg1.start_time=31/12/2099 00:00:00
upg1.target_pdb_copy_option.ORA12RF1=file_name_convert=NONE
EOF
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ cat /home/oracle/autoupgrade_work/ora12rf.cfg
global.autoupg_log_dir=/home/oracle/autoupgrade_work/logs
upg1.source_home=/u01/app/oracle/product/12c/db_1
upg1.target_home=/u01/app/oracle/product/19c/db_1
upg1.target_version=19
upg1.sid=ORA12RF1
upg1.target_cdb=ORA19RS1
upg1.source_dblink.ORA12RF1=CLONEPDB 600
upg1.target_pdb_name.ORA12RF1=ORA12RF
upg1.start_time=31/12/2099 00:00:00
upg1.target_pdb_copy_option.ORA12RF1=file_name_convert=NONE
5-2. glogin.sql 비활성화
목차로 돌아가기
소스 서버의 1번 노드
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ mv $ORACLE_HOME/sqlplus/admin/glogin.sql $ORACLE_HOME/sqlplus/admin/glogin.sql.bak
타켓 서버의 1번 노드
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ mv $ORACLE_HOME/sqlplus/admin/glogin.sql $ORACLE_HOME/sqlplus/admin/glogin.sql.bak
5-3 Auto Upgrade Deploy 작업
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ cd /home/oracle/autoupgrade_work
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle/autoupgrade_work]$
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar \
-mode deploy \
-config /home/oracle/autoupgrade_work/ora12rf.cfg
Previous execution found loading latest data
Total jobs recovered: 1
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
Type 'help' to list console commands
upg> status
Config
User configuration file [/home/oracle/autoupgrade_work/ora12rf.cfg]
General logs location [/home/oracle/autoupgrade_work/logs/cfgtoollogs/upgrade/auto]
Mode [DEPLOY]
Jobs Summary
Total databases in configuration file [1]
Total Non-CDB being processed [0]
Total Containers being processed [1]
Jobs finished successfully [0]
Jobs finished/stopped [0]
Jobs in progress [1]
Progress
+---+---------------------------------------------------------+
|Job| Progress|
+---+---------------------------------------------------------+
|100|[| ] 0 %|
+---+---------------------------------------------------------+
upg> lsj
+----+--------+----------+---------+-------+----------+-------+----------------------------+
|Job#| DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+----+--------+----------+---------+-------+----------+-------+----------------------------+
| 100|ORA12RF1|REFRESHPDB|EXECUTING|RUNNING| 00:00:00|16s ago|Starts in 38,886,327 minutes|
+----+--------+----------+---------+-------+----------+-------+----------------------------+
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 23 16:53:43 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
-- SQL*Plus 포맷 설정
SET LINESIZE 200
SET PAGESIZE 100
SET COLSEP ' | '
SET VERIFY OFF
COLUMN PDB_NAME FORMAT A15 HEADING 'PDB Name'
COLUMN STATUS FORMAT A10 HEADING 'Status'
COLUMN CREATION_SCN FORMAT 9999999999999 HEADING 'Creation SCN'
COLUMN CON_ID FORMAT 999 HEADING 'Con ID'
COLUMN GUID FORMAT A32 HEADING 'GUID'
COLUMN REFRESH_MODE FORMAT A12 HEADING 'Refresh Mode'
COLUMN LAST_REFRESH_SCN FORMAT 9999999999999 HEADING 'Last Refresh SCN'
-- 쿼리 실행
SELECT PDB_NAME
, STATUS
, CREATION_SCN
, CON_ID
, GUID
, REFRESH_MODE
, LAST_REFRESH_SCN
FROM cdb_pdbs
WHERE 1=1
AND pdb_name = 'ORA12RF';
5-4. Refresh PDB에 의한 데이터 동기화 여부 검증
목차로 돌아가기
타켓 pdb에서 강제 refresh를 해줌
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 23 16:08:14 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORA19RSP1 MOUNTED
4 ORA12RF MOUNTED
SQL>
SET LINESIZE 200
COL NAME FORMAT A15
COL OPEN_MODE FORMAT A15
COL CURRENT_SCN FORMAT 999999999999999
SELECT p.NAME
, p.OPEN_MODE
, MAX(d.CHECKPOINT_CHANGE#) as CURRENT_SCN
FROM V$PDBS p
JOIN V$DATAFILE d ON p.CON_ID = d.CON_ID
WHERE p.NAME = 'ORA12RF'
GROUP BY p.NAME, p.OPEN_MODE;
NAME OPEN_MODE CURRENT_SCN
--------------- --------------- ----------------
ORA12RF MOUNTED 25998717
SQL>
SQL> ALTER PLUGGABLE DATABASE ORA12RF REFRESH;
Pluggable database altered.
SET LINESIZE 200
COL NAME FORMAT A15
COL OPEN_MODE FORMAT A15
COL CURRENT_SCN FORMAT 999999999999999
SELECT p.NAME
, p.OPEN_MODE
, MAX(d.CHECKPOINT_CHANGE#) as CURRENT_SCN
FROM V$PDBS p
JOIN V$DATAFILE d ON p.CON_ID = d.CON_ID
WHERE p.NAME = 'ORA12RF'
GROUP BY p.NAME, p.OPEN_MODE;
NAME OPEN_MODE CURRENT_SCN
--------------- --------------- ----------------
ORA12RF MOUNTED 26132198
SQL> ALTER PLUGGABLE DATABASE ORA12RF REFRESH MODE EVERY 10 MINUTES;
-- SQL*Plus 포맷 설정
SET LINESIZE 200
SET PAGESIZE 100
SET COLSEP ' | '
SET VERIFY OFF
COLUMN PDB_NAME FORMAT A15 HEADING 'PDB Name'
COLUMN STATUS FORMAT A10 HEADING 'Status'
COLUMN CREATION_SCN FORMAT 9999999999999 HEADING 'Creation SCN'
COLUMN CON_ID FORMAT 999 HEADING 'Con ID'
COLUMN GUID FORMAT A32 HEADING 'GUID'
COLUMN REFRESH_MODE FORMAT A12 HEADING 'Refresh Mode'
COLUMN LAST_REFRESH_SCN FORMAT 9999999999999 HEADING 'Last Refresh SCN'
-- 쿼리 실행
SELECT PDB_NAME
, STATUS
, CREATION_SCN
, CON_ID
, GUID
, REFRESH_MODE
, LAST_REFRESH_SCN
FROM cdb_pdbs
WHERE 1=1
AND pdb_name = 'ORA12RF';
소소 DB에서 타켓 pdb의 scn 추적
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 23 16:21:21 2026
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
SET LINESIZE 250
SET PAGESIZE 100
SET COLSEP ' | '
COLUMN thread# HEADING 'Thread' FORMAT 9999
COLUMN sequence# HEADING 'Seq#' FORMAT 999999
COLUMN status HEADING 'Status' FORMAT a10
COLUMN first_time HEADING 'Start Time'
COLUMN next_time HEADING 'End Time'
COLUMN first_change# HEADING 'Min SCN' FORMAT 999999999999999
COLUMN next_change# HEADING 'Max SCN' FORMAT 999999999999999999999999
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
SELECT thread#
, sequence#
, status
, first_time
, first_change#
, next_time
, next_change#
FROM v$log a
WHERE 26132198 BETWEEN first_change# AND next_change#;
Thread | Seq# | Status | Start Time | Min SCN | End Time | Max SCN
------ | ------- | ---------- | ------------------- | ---------------- | ------------------- | -------------------------
1 | 28 | CURRENT | 2026-01-23 14:36:00 | 26099524 | 2026-01-23 14:36:00 | 18446744073709551615
2 | 26 | CURRENT | 2026-01-23 15:30:05 | 26122035 | | 18446744073709551615
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL>
SET LINESIZE 200
SET PAGESIZE 100
SET COLSEP '|'
COL THREAD# FORMAT 9999 HEADING 'Thread'
COL SEQUENCE# FORMAT 99999 HEADING 'Seq'
COL STATUS FORMAT A10 HEADING 'Status'
COL FIRST_TIME FORMAT A20 HEADING 'First Time'
COL NEXT_TIME FORMAT A20 HEADING 'Next Time'
COL FIRST_CHANGE# FORMAT 999999999999999 HEADING 'First SCN'
COL NEXT_CHANGE# FORMAT 999999999999999 HEADING 'Next SCN'
SELECT THREAD#
, SEQUENCE#
, STATUS
, TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24:MI:SS') AS FIRST_TIME
, FIRST_CHANGE#
, TO_CHAR(NEXT_TIME, 'YYYY-MM-DD HH24:MI:SS') AS NEXT_TIME
, NEXT_CHANGE#
FROM V$ARCHIVED_LOG
WHERE 1=1
AND 26132198 BETWEEN FIRST_CHANGE# AND NEXT_CHANGE#
AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE)
ORDER BY THREAD#, SEQUENCE#;
Thread| Seq|Status |First Time | First SCN|Next Time | Next SCN
------|------|----------|--------------------|----------------|--------------------|----------------
1| 28|A |2026-01-23 14:36:00 | 26099524|2026-01-23 16:24:42 | 26136788
2| 26|A |2026-01-23 15:30:05 | 26122035|2026-01-23 16:24:42 | 26136785
5-5. cut over 전 소스 db에 테이블 생성 및 데이터 입력
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 23 16:31:56 2026
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
create table tuner.tb_before_cut_over as
select * from dba_objects;
select count(*) from tuner.tb_before_cut_over;
SQL> select count(*) from tuner.tb_before_cut_over;
COUNT(*)
----------
74725
SQL>
5-6. cut over 시키고 pdb 업그레이드
소스 DB를 내림
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ srvctl stop database -db ora12rf
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar \
-mode deploy \
-config /home/oracle/autoupgrade_work/ora12rf.cfg
Previous execution found loading latest data
Total jobs recovered: 1
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
Type 'help' to list console commands
upg>
upg> status
Config
User configuration file [/home/oracle/autoupgrade_work/ora12rf.cfg]
General logs location [/home/oracle/autoupgrade_work/logs/cfgtoollogs/upgrade/auto]
Mode [DEPLOY]
Jobs Summary
Total databases in configuration file [1]
Total Non-CDB being processed [0]
Total Containers being processed [1]
Jobs finished successfully [0]
Jobs finished/stopped [0]
Jobs in progress [1]
Progress
+---+---------------------------------------------------------+
|Job| Progress|
+---+---------------------------------------------------------+
|100|[| ] 0 %|
+---+---------------------------------------------------------+
upg>
upg> lsj
+----+--------+----------+---------+-------+----------+------------+----------------------------+
|Job#| DB_NAME| STAGE|OPERATION| STATUS|START_TIME| UPDATED| MESSAGE|
+----+--------+----------+---------+-------+----------+------------+----------------------------+
| 100|ORA12RF1|REFRESHPDB|EXECUTING|RUNNING| 00:00:00|!!!1172s ago|Starts in 38,886,327 minutes|
+----+--------+----------+---------+-------+----------+------------+----------------------------+
Total jobs 1
upg> proceed -job 100
New start time for job 100 is scheduled 0 minute(s) from now, at 23/01/2026 16:56:21
upg> status
Config
User configuration file [/home/oracle/autoupgrade_work/ora12rf.cfg]
General logs location [/home/oracle/autoupgrade_work/logs/cfgtoollogs/upgrade/auto]
Mode [DEPLOY]
Jobs Summary
Total databases in configuration file [1]
Total Non-CDB being processed [0]
Total Containers being processed [1]
Jobs finished successfully [0]
Jobs finished/stopped [0]
Jobs in progress [1]
Progress
+---+---------------------------------------------------------+
|Job| Progress|
+---+---------------------------------------------------------+
|100|[| ] 0 %|
+---+---------------------------------------------------------+
upg> lsj
+----+--------+----------+---------+-------+----------+------------+-------------------+
|Job#| DB_NAME| STAGE|OPERATION| STATUS|START_TIME| UPDATED| MESSAGE|
+----+--------+----------+---------+-------+----------+------------+-------------------+
| 100|ORA12RF1|REFRESHPDB|EXECUTING|RUNNING| 16:56:21|!!!1207s ago|Starts in 0 minutes|
+----+--------+----------+---------+-------+----------+------------+-------------------+
Total jobs 1
upg>
Job 100 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Jobs restored [0]
Jobs pending [0]
Please check the summary report at:
/home/oracle/autoupgrade_work/logs/cfgtoollogs/upgrade/auto/status/status.html
/home/oracle/autoupgrade_work/logs/cfgtoollogs/upgrade/auto/status/status.log
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 23 17:18:38 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORA19RSP1 MOUNTED
4 ORA12RF READ WRITE NO
SQL> alter session set container=ORA12RF;
Session altered.
SQL> select count(*) from tuner.tb_before_cut_over;
COUNT(*)
----------
74725
6. 업그레이드 후 후속작업
6-1. glogin.sql 활성화
목차로 돌아가기
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ mv $ORACLE_HOME/sqlplus/admin/glogin.sql.bak $ORACLE_HOME/sqlplus/admin/glogin.sql
6-2. 버전 확인
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 23 22:42:04 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1>$ show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORA19RSP1 MOUNTED (NULL)
4 ORA12RF READ WRITE NO
[ol8ora19rs1]<SYS@ORA19RS1>$ ALTER SESSION SET CONTAINER = ORA12RF;
Session altered.
Elapsed: 00:00:00.00
[ol8ora19rs1]<SYS@ORA19RS1>$ show con_name
CON_NAME
------------------------------
ORA12RF
[ol8ora19rs1]<SYS@ORA19RS1>$ col banner for a80
[ol8ora19rs1]<SYS@ORA19RS1>$ SELECT BANNER FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
1 row selected.
Elapsed: 00:00:00.00
6-3. PDB로 바로 붙을 수 있게 설정
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ cat >> ~oracle/.bash_profile << 'EOF'
alias ssp2='rlwrap sqlplus sys/oracle@ORA19RS1_ORA12RF as sysdba'
EOF
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ source ~oracle/.bash_profile
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORA19RS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol8ora19rs-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19RS)
)
)
ORA19RS1P1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.240.41)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = ora19rsp1)
)
)
ORA12RF =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.240.14)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.240.15)(PORT = 1521))
(LOAD_BALANCE = OFF)
(FAILOVER = ON)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA12RF)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC))
)
)
ORA19RS1_ORA12RF =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.240.41)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = ORA12RF)
)
)
--> ORA19RS1_ORA12RF 추가함
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias ssp2
alias ssp2='rlwrap sqlplus sys/oracle@ORA19RS1_ORA12RF as sysdba'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ssp2
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 23 22:55:26 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1_ORA12RF>$ show con_name
CON_NAME
------------------------------
ORA12RF
[ol8ora19rs1]<SYS@ORA19RS1_ORA12RF>$ show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 ORA12RF READ WRITE NO
6-4. 컴포넌트 확인
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias ssp2
alias ssp2='rlwrap sqlplus sys/oracle@ORA19RS1_ORA12RF as sysdba'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ssp2
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 23 22:56:12 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1_ORA12RF>$
SET LINESIZE 200
COL COMP_ID FORMAT A10
COL COMP_NAME FORMAT A40
COL VERSION FORMAT A15
COL STATUS FORMAT A15
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 VALID
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.
Elapsed: 00:00:00.00
6-5. 타임존 확인
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias ssp2
alias ssp2='rlwrap sqlplus sys/oracle@ORA19RS1_ORA12RF as sysdba'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ssp2
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 23 22:58:34 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1_ORA12RF>$
SET LINESIZE 200
COL PROPERTY_NAME FORMAT A40
COL PROPERTY_VALUE FORMAT A40
SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%';
PROPERTY_NAME PROPERTY_VALUE
---------------------------------------- ----------------------------------------
DST_SECONDARY_TT_VERSION 0
DST_PRIMARY_TT_VERSION 44
DST_UPGRADE_STATE NONE
3 rows selected.
Elapsed: 00:00:00.00
6-6. invalid 오브젝트 확인
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias ssp2
alias ssp2='rlwrap sqlplus sys/oracle@ORA19RS1_ORA12RF as sysdba'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ssp2
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 23 23:11:31 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1_ORA12RF>$
--아래의 sql문을 실행 시켜 invalid object 컴파일
SET LINESIZE 200
SET PAGESIZE 1000
SET COLSEP ' | '
SET FEEDBACK OFF
SET VERIFY OFF
COL OWNER FORMAT A10 HEADING "Owner"
COL OBJECT_NAME FORMAT A25 HEADING "Object Name"
COL OBJECT_TYPE FORMAT A15 HEADING "Type"
COL STATUS FORMAT A10 HEADING "Status"
COL CON_ID FORMAT 999 HEADING "ConID"
COL DDL_COMMAND FORMAT A60 HEADING "Compilation Command"
-- Main Query
SELECT OWNER
, OBJECT_NAME
, OBJECT_TYPE
, STATUS
, CON_ID
, DDL_COMMAND
FROM (
-- 1. Standard Objects (PROCEDURE, FUNCTION, VIEW, TRIGGER, PACKAGE, TYPE, MATERIALIZED VIEW)
SELECT OWNER
, OBJECT_NAME
, STATUS
, OBJECT_TYPE
, CON_ID
, 'ALTER ' || OBJECT_TYPE || ' "' || OWNER || '"."' || OBJECT_NAME || '" COMPILE;' AS DDL_COMMAND
, 1 AS SORT_ORDER
FROM CDB_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'VIEW', 'TRIGGER', 'PACKAGE', 'TYPE', 'MATERIALIZED VIEW', 'LIBRARY', 'INDEXTYPE')
UNION ALL
-- 2. Body Objects (PACKAGE BODY, TYPE BODY)
SELECT OWNER
, OBJECT_NAME
, STATUS
, OBJECT_TYPE
, CON_ID
, 'ALTER ' || REPLACE(OBJECT_TYPE, ' BODY', '') || ' "' || OWNER || '"."' || OBJECT_NAME || '" COMPILE BODY;' AS DDL_COMMAND
, 2 AS SORT_ORDER
FROM CDB_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE IN ('PACKAGE BODY', 'TYPE BODY')
UNION ALL
-- 3. Public Synonyms (Requires Special Syntax)
SELECT OWNER
, OBJECT_NAME
, STATUS
, OBJECT_TYPE
, CON_ID
, 'ALTER PUBLIC SYNONYM "' || OBJECT_NAME || '" COMPILE;' AS DDL_COMMAND
, 3 AS SORT_ORDER
FROM CDB_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE = 'SYNONYM'
AND OWNER = 'PUBLIC'
)
ORDER BY CON_ID
, SORT_ORDER
, OWNER
, OBJECT_NAME;
Owner | Object Name | Type | Status | ConID | Compilation Command
---------- | ------------------------- | --------------- | ---------- | ----- | ------------------------------------------------------------
HR | ADD_JOB_HISTORY | PROCEDURE | INVALID | 4 | ALTER PROCEDURE "HR"."ADD_JOB_HISTORY" COMPILE;
HR | SECURE_DML | PROCEDURE | INVALID | 4 | ALTER PROCEDURE "HR"."SECURE_DML" COMPILE;
HR | SECURE_EMPLOYEES | TRIGGER | INVALID | 4 | ALTER TRIGGER "HR"."SECURE_EMPLOYEES" COMPILE;
HR | UPDATE_JOB_HISTORY | TRIGGER | INVALID | 4 | ALTER TRIGGER "HR"."UPDATE_JOB_HISTORY" COMPILE;
Elapsed: 00:00:00.02
[ol8ora19rs1]<SYS@ORA19RS1_ORA12RF>$
ALTER PROCEDURE "HR"."ADD_JOB_HISTORY" COMPILE;
ALTER PROCEDURE "HR"."SECURE_DML" COMPILE;
ALTER TRIGGER "HR"."SECURE_EMPLOYEES" COMPILE;
ALTER TRIGGER "HR"."UPDATE_JOB_HISTORY" COMPILE;
[ol8ora19rs1]<SYS@ORA19RS1_ORA12RF>$ select count(*) from dba_objects where status <> 'VALID';
COUNT(*)
----------
0
Elapsed: 00:00:00.01
6-7. 파라미터 설정
소스 서버에서 백업 받았던 소스 DB의 pfile 조회
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ cat /home/oracle/BACKUP_BEFORE_UPGRADE/pfile_before_upgrade.ora
ORA12RF1.__data_transfer_cache_size=0
ORA12RF2.__data_transfer_cache_size=0
ORA12RF1.__db_cache_size=3825205248
ORA12RF2.__db_cache_size=3825205248
ORA12RF1.__inmemory_ext_roarea=0
ORA12RF2.__inmemory_ext_roarea=0
ORA12RF1.__inmemory_ext_rwarea=0
ORA12RF2.__inmemory_ext_rwarea=0
ORA12RF1.__java_pool_size=16777216
ORA12RF2.__java_pool_size=16777216
ORA12RF1.__large_pool_size=33554432
ORA12RF2.__large_pool_size=33554432
ORA12RF1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORA12RF2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORA12RF1.__pga_aggregate_target=1694498816
ORA12RF2.__pga_aggregate_target=1694498816
ORA12RF1.__sga_target=5033164800
ORA12RF2.__sga_target=5033164800
ORA12RF1.__shared_io_pool_size=268435456
ORA12RF2.__shared_io_pool_size=268435456
ORA12RF1.__shared_pool_size=872415232
ORA12RF2.__shared_pool_size=872415232
ORA12RF1.__streams_pool_size=0
ORA12RF2.__streams_pool_size=0
*._optimizer_gather_stats_on_load=FALSE
*.archive_lag_target=0
*.audit_file_dest='/u01/app/oracle/admin/ORA12RF/adump'
*.audit_sys_operations=TRUE
*.audit_trail='OS'
*.cluster_database=TRUE
*.compatible='12.2.0'
*.control_files='+DATA1/ORA12RF/CONTROLFILE/current.261.1213483391','+FRA1/ORA12RF/CONTROLFILE/current.256.1213483391'
*.data_guard_sync_latency=0
*.db_block_size=8192
*.db_create_file_dest='+DATA1'
*.db_create_online_log_dest_1='+DATA1'
*.db_create_online_log_dest_2='+FRA1'
*.db_name='ORA12RF'
*.dg_broker_start=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.disk_asynch_io=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA12RFXDB)'
*.fal_server=''
*.filesystemio_options='SETALL'
*.heat_map='OFF'
family:dw_helper.instance_mode='read-only'
ORA12RF1.instance_number=1
ORA12RF2.instance_number=2
ORA12RF1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.240.14)(PORT=1521))'
ORA12RF2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.240.15)(PORT=1521))'
*.log_archive_dest_1='LOCATION=+FRA1'
*.log_archive_dest_2=''
*.log_archive_format='%t_%s_%r.ARC'
ORA12RF2.log_archive_format='%t_%s_%r.ARC'
ORA12RF1.log_archive_format='%t_%s_%r.ARC'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
ORA12RF2.log_archive_trace=0
ORA12RF1.log_archive_trace=0
*.max_string_size='EXTENDED'
*.nls_language='AMERICAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.optimizer_adaptive_plans=FALSE
*.optimizer_adaptive_reporting_only=TRUE
*.optimizer_dynamic_sampling=0
*.parallel_force_local=TRUE
*.parallel_min_servers=0
*.pga_aggregate_limit=0m
*.pga_aggregate_target=1601m
*.processes=1432
*.remote_login_passwordfile='exclusive'
*.resource_manager_plan='FORCE:'
*.sga_target=4800m
*.standby_file_management='MANUAL'
ORA12RF2.thread=2
ORA12RF1.thread=1
*.undo_retention=3600
ORA12RF1.undo_tablespace='UNDOTBS1'
ORA12RF2.undo_tablespace='UNDOTBS2'
*.uniform_log_timestamp_format=FALSE
*.use_large_pages='ONLY'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
--19c 타켓 DB에서 확인 및 적용해야할 파라미터
*._optimizer_gather_stats_on_load=FALSE
*.audit_sys_operations=TRUE
*.audit_trail='OS'
*.disk_asynch_io=TRUE
*.heat_map='OFF'
*.optimizer_adaptive_plans=FALSE
*.optimizer_adaptive_reporting_only=TRUE
*.optimizer_dynamic_sampling=0
*.parallel_force_local=TRUE
*.parallel_min_servers=0
*.remote_login_passwordfile='exclusive'
*.resource_manager_plan='FORCE:'
*.sga_target=4800m
*.pga_aggregate_limit=0m
*.pga_aggregate_target=1601m
*.uniform_log_timestamp_format=FALSE
일반 파라미터 확인
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 24 00:43:37 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1>$ show con_name
CON_NAME
------------------------------
CDB$ROOT
[ol8ora19rs1]<SYS@ORA19RS1>$
SET LINESIZE 300
SET PAGESIZE 100
SET COLSEP ' | '
SET VERIFY OFF
SET FEEDBACK ON
SET TIMING OFF
COLUMN PARAM_NAME FORMAT A35 HEADING 'Parameter Name'
COLUMN DESIRED_VAL FORMAT A15 HEADING 'Target(Want)'
COLUMN CDB_VAL FORMAT A20 HEADING 'CDB Value(Root)'
COLUMN PDB_VAL FORMAT A20 HEADING 'PDB Value'
COLUMN IS_MOD FORMAT A20 HEADING 'PDB_Modifiable'
COLUMN STATUS FORMAT A15 HEADING 'Result'
WITH DESIRED_LIST (PARAMETER_NAME, DESIRED_VAL) AS (
SELECT 'AUDIT_SYS_OPERATIONS', 'TRUE' FROM DUAL UNION ALL
SELECT 'AUDIT_TRAIL', 'OS' FROM DUAL UNION ALL
SELECT 'DISK_ASYNCH_IO', 'TRUE' FROM DUAL UNION ALL
SELECT 'HEAT_MAP', 'OFF' FROM DUAL UNION ALL
SELECT 'OPTIMIZER_ADAPTIVE_PLANS', 'FALSE' FROM DUAL UNION ALL
SELECT 'OPTIMIZER_DYNAMIC_SAMPLING', '0' FROM DUAL UNION ALL
SELECT 'PARALLEL_FORCE_LOCAL', 'TRUE' FROM DUAL UNION ALL
SELECT 'PARALLEL_MIN_SERVERS', '0' FROM DUAL UNION ALL
SELECT 'REMOTE_LOGIN_PASSWORDFILE', 'EXCLUSIVE' FROM DUAL UNION ALL
SELECT 'RESOURCE_MANAGER_PLAN', 'FORCE:' FROM DUAL UNION ALL
SELECT 'SGA_TARGET', '4800M' FROM DUAL UNION ALL
SELECT 'PGA_AGGREGATE_LIMIT', '0' FROM DUAL UNION ALL
SELECT 'PGA_AGGREGATE_TARGET', '1601M' FROM DUAL UNION ALL
SELECT 'UNIFORM_LOG_TIMESTAMP_FORMAT', 'FALSE' FROM DUAL UNION ALL
SELECT '_OPTIMIZER_GATHER_STATS_ON_LOAD', 'FALSE' FROM DUAL
),
PARAM_STATUS AS (
SELECT UPPER(TRIM(A.PARAMETER_NAME)) AS PARAMETER_NAME
, UPPER(TRIM(A.DESIRED_VAL)) AS DESIRED_VAL
, UPPER(MAX(DECODE(B.CON_ID, 0, B.DISPLAY_VALUE))) AS CDB_VAL_RAW
, UPPER(MAX(DECODE(B.CON_ID, 4, B.DISPLAY_VALUE))) AS PDB_VAL_RAW
, NVL(MAX(B.ISPDB_MODIFIABLE), 'FALSE') AS IS_MOD_CLEAN
FROM DESIRED_LIST A
LEFT JOIN V$SYSTEM_PARAMETER B
ON UPPER(TRIM(A.PARAMETER_NAME)) = UPPER(B.NAME)
AND B.CON_ID IN (0, 4) -- 0: Default/Root, 4: 대상 PDB
GROUP BY A.PARAMETER_NAME, A.DESIRED_VAL
)
SELECT C.PARAMETER_NAME AS PARAM_NAME
, C.DESIRED_VAL AS DESIRED_VAL
, NVL(C.CDB_VAL_RAW, '(NULL)') AS CDB_VAL
, NVL(C.PDB_VAL_RAW, '(NULL)') AS PDB_VAL
, C.IS_MOD_CLEAN AS IS_MOD
, CASE
WHEN C.CDB_VAL_RAW IS NULL AND C.PDB_VAL_RAW IS NULL THEN 'OBSOLETE'
WHEN C.PDB_VAL_RAW IS NOT NULL AND C.PDB_VAL_RAW = C.DESIRED_VAL THEN 'MATCH'
WHEN C.IS_MOD_CLEAN = 'FALSE' AND C.CDB_VAL_RAW = C.DESIRED_VAL THEN 'MATCH (INH)'
ELSE 'DIFF'
END AS STATUS
FROM PARAM_STATUS C
WHERE C.PARAMETER_NAME NOT LIKE '\_%' ESCAPE '\'
ORDER BY 1;
Parameter Name | Target(Want) | CDB Value(Root) | PDB Value | PDB_Modifiable | Result
----------------------------------- | --------------- | -------------------- | -------------------- | -------------------- | ---------------
AUDIT_SYS_OPERATIONS | TRUE | TRUE | (NULL) | FALSE | MATCH (INH)
AUDIT_TRAIL | OS | OS | (NULL) | FALSE | MATCH (INH)
DISK_ASYNCH_IO | TRUE | TRUE | (NULL) | FALSE | MATCH (INH)
HEAT_MAP | OFF | OFF | OFF | TRUE | MATCH
OPTIMIZER_ADAPTIVE_PLANS | FALSE | FALSE | FALSE | TRUE | MATCH
OPTIMIZER_DYNAMIC_SAMPLING | 0 | 0 | 0 | TRUE | MATCH
PARALLEL_FORCE_LOCAL | TRUE | TRUE | TRUE | TRUE | MATCH
PARALLEL_MIN_SERVERS | 0 | 0 | (NULL) | FALSE | MATCH (INH)
PGA_AGGREGATE_LIMIT | 0 | 0 | 0 | TRUE | MATCH
PGA_AGGREGATE_TARGET | 1601M | 1599M | 1599M | TRUE | DIFF
REMOTE_LOGIN_PASSWORDFILE | EXCLUSIVE | EXCLUSIVE | (NULL) | FALSE | MATCH (INH)
RESOURCE_MANAGER_PLAN | FORCE: | FORCE: | FORCE: | TRUE | MATCH
SGA_TARGET | 4800M | 4800M | 4800M | TRUE | MATCH
UNIFORM_LOG_TIMESTAMP_FORMAT | FALSE | FALSE | (NULL) | FALSE | MATCH (INH)
14 rows selected.
--> PGA_AGGREGATE_TARGET 이 파라미터만 다른 상태, 12cr2에서는 1601M 이었으나 19c에서는 1599M 인 상황
--> 별로 차이가 나지 않으므로 바꾸지 않음
히든 파라미터 확인
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ssp2
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 24 00:57:34 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1_ORA12RF>$
SET LINESIZE 300
SET PAGESIZE 100
SET COLSEP ' | '
SET VERIFY OFF
SET FEEDBACK ON
SET TIMING OFF
COLUMN PARAM_NAME FORMAT A40 HEADING 'Parameter Name'
COLUMN DESIRED_VAL FORMAT A15 HEADING 'Target(Want)'
COLUMN CDB_VAL FORMAT A15 HEADING 'CDB Value(Root)'
COLUMN PDB_VAL FORMAT A20 HEADING 'PDB Value'
COLUMN IS_PDB_MODIFIABLE FORMAT A15 HEADING 'PDB_Modifiable'
COLUMN STATUS FORMAT A15 HEADING 'Result'
WITH DESIRED_LIST (PARAMETER_NAME, DESIRED_VAL) AS (
SELECT UPPER(TRIM('AUDIT_SYS_OPERATIONS ')), UPPER(TRIM('TRUE ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('AUDIT_TRAIL ')), UPPER(TRIM('OS ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('DISK_ASYNCH_IO ')), UPPER(TRIM('TRUE ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('HEAT_MAP ')), UPPER(TRIM('OFF ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('OPTIMIZER_ADAPTIVE_PLANS ')), UPPER(TRIM('FALSE ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('OPTIMIZER_DYNAMIC_SAMPLING ')), UPPER(TRIM('0 ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('PARALLEL_FORCE_LOCAL ')), UPPER(TRIM('TRUE ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('PARALLEL_MIN_SERVERS ')), UPPER(TRIM('0 ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('REMOTE_LOGIN_PASSWORDFILE ')), UPPER(TRIM('EXCLUSIVE ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('RESOURCE_MANAGER_PLAN ')), UPPER(TRIM('FORCE: ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('SGA_TARGET ')), UPPER(TRIM('4800M ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('PGA_AGGREGATE_LIMIT ')), UPPER(TRIM('0 ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('PGA_AGGREGATE_TARGET ')), UPPER(TRIM('1601M ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('UNIFORM_LOG_TIMESTAMP_FORMAT ')), UPPER(TRIM('FALSE ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('_OPTIMIZER_GATHER_STATS_ON_LOAD ')), UPPER(TRIM('FALSE ')) FROM DUAL
),
HIDDEN_PARAM_STATUS AS (
SELECT UPPER(TRIM(A.PARAMETER_NAME)) AS PARAMETER_NAME
, UPPER(TRIM(A.DESIRED_VAL)) AS DESIRED_VAL
, UPPER(KCV.KSPPSTVL) AS CURRENT_VAL
, KCV.KSPPSTDF AS IS_DEFAULT
, DECODE(BITAND(KI.KSPPIFLG / 524288, 1), 1, 'TRUE', 'FALSE') AS IS_PDB_MODIFIABLE
FROM DESIRED_LIST A
LEFT JOIN SYS.X$KSPPI KI
ON UPPER(TRIM(A.PARAMETER_NAME)) = UPPER(KI.KSPPINM)
LEFT JOIN SYS.X$KSPPCV KCV
ON KI.INDX = KCV.INDX
WHERE A.PARAMETER_NAME LIKE '\_%' ESCAPE '\'
)
SELECT H.PARAMETER_NAME AS PARAM_NAME
, H.DESIRED_VAL AS DESIRED_VAL
, '(Hidden)' AS CDB_VAL -- 히든 파라미터는 PDB에서 Root 값 직접 조회 불가
, NVL(H.CURRENT_VAL, '(NOT FOUND)') AS PDB_VAL
, H.IS_PDB_MODIFIABLE AS IS_PDB_MODIFIABLE
, CASE
WHEN H.CURRENT_VAL IS NULL THEN 'MISSING'
WHEN H.CURRENT_VAL = H.DESIRED_VAL THEN 'MATCH'
ELSE 'DIFF'
END AS STATUS
FROM HIDDEN_PARAM_STATUS H
ORDER BY 1;
Parameter Name | Target(Want) | CDB Value(Root) | PDB Value | PDB_Modifiable | Result
---------------------------------------- | --------------- | --------------- | -------------------- | --------------- | ---------------
_OPTIMIZER_GATHER_STATS_ON_LOAD | FALSE | (Hidden) | TRUE | TRUE | DIFF
1 row selected.
--> _OPTIMIZER_GATHER_STATS_ON_LOAD 이 파라미터의 값이 12cR2에서는 false였으나 19c에서는 해당 PDB가 TRUE인 상황
[ol8ora19rs1]<SYS@ORA19RS1_ORA12RF>$ alter system set "_OPTIMIZER_GATHER_STATS_ON_LOAD"=false scope=both sid='*';
--> 파라미터 변경해줌
System altered.
[ol8ora19rs1]<SYS@ORA19RS1_ORA12RF>$
SET LINESIZE 300
SET PAGESIZE 100
SET COLSEP ' | '
SET VERIFY OFF
SET FEEDBACK ON
SET TIMING OFF
COLUMN PARAM_NAME FORMAT A40 HEADING 'Parameter Name'
COLUMN DESIRED_VAL FORMAT A15 HEADING 'Target(Want)'
COLUMN CDB_VAL FORMAT A15 HEADING 'CDB Value(Root)'
COLUMN PDB_VAL FORMAT A20 HEADING 'PDB Value'
COLUMN IS_PDB_MODIFIABLE FORMAT A15 HEADING 'PDB_Modifiable'
COLUMN STATUS FORMAT A15 HEADING 'Result'
WITH DESIRED_LIST (PARAMETER_NAME, DESIRED_VAL) AS (
SELECT UPPER(TRIM('AUDIT_SYS_OPERATIONS ')), UPPER(TRIM('TRUE ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('AUDIT_TRAIL ')), UPPER(TRIM('OS ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('DISK_ASYNCH_IO ')), UPPER(TRIM('TRUE ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('HEAT_MAP ')), UPPER(TRIM('OFF ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('OPTIMIZER_ADAPTIVE_PLANS ')), UPPER(TRIM('FALSE ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('OPTIMIZER_DYNAMIC_SAMPLING ')), UPPER(TRIM('0 ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('PARALLEL_FORCE_LOCAL ')), UPPER(TRIM('TRUE ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('PARALLEL_MIN_SERVERS ')), UPPER(TRIM('0 ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('REMOTE_LOGIN_PASSWORDFILE ')), UPPER(TRIM('EXCLUSIVE ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('RESOURCE_MANAGER_PLAN ')), UPPER(TRIM('FORCE: ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('SGA_TARGET ')), UPPER(TRIM('4800M ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('PGA_AGGREGATE_LIMIT ')), UPPER(TRIM('0 ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('PGA_AGGREGATE_TARGET ')), UPPER(TRIM('1601M ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('UNIFORM_LOG_TIMESTAMP_FORMAT ')), UPPER(TRIM('FALSE ')) FROM DUAL UNION ALL
SELECT UPPER(TRIM('_OPTIMIZER_GATHER_STATS_ON_LOAD ')), UPPER(TRIM('FALSE ')) FROM DUAL
),
HIDDEN_PARAM_STATUS AS (
SELECT UPPER(TRIM(A.PARAMETER_NAME)) AS PARAMETER_NAME
, UPPER(TRIM(A.DESIRED_VAL)) AS DESIRED_VAL
, UPPER(KCV.KSPPSTVL) AS CURRENT_VAL
, KCV.KSPPSTDF AS IS_DEFAULT
, DECODE(BITAND(KI.KSPPIFLG / 524288, 1), 1, 'TRUE', 'FALSE') AS IS_PDB_MODIFIABLE
FROM DESIRED_LIST A
LEFT JOIN SYS.X$KSPPI KI
ON UPPER(TRIM(A.PARAMETER_NAME)) = UPPER(KI.KSPPINM)
LEFT JOIN SYS.X$KSPPCV KCV
ON KI.INDX = KCV.INDX
WHERE A.PARAMETER_NAME LIKE '\_%' ESCAPE '\'
)
SELECT H.PARAMETER_NAME AS PARAM_NAME
, H.DESIRED_VAL AS DESIRED_VAL
, '(Hidden)' AS CDB_VAL -- 히든 파라미터는 PDB에서 Root 값 직접 조회 불가
, NVL(H.CURRENT_VAL, '(NOT FOUND)') AS PDB_VAL
, H.IS_PDB_MODIFIABLE AS IS_PDB_MODIFIABLE
, CASE
WHEN H.CURRENT_VAL IS NULL THEN 'MISSING'
WHEN H.CURRENT_VAL = H.DESIRED_VAL THEN 'MATCH'
ELSE 'DIFF'
END AS STATUS
FROM HIDDEN_PARAM_STATUS H
ORDER BY 1;
Parameter Name | Target(Want) | CDB Value(Root) | PDB Value | PDB_Modifiable | Result
---------------------------------------- | --------------- | --------------- | -------------------- | --------------- | ---------------
_OPTIMIZER_GATHER_STATS_ON_LOAD | FALSE | (Hidden) | FALSE | TRUE | MATCH
1 row selected.
--> 매칭된 것을 확인함
CDB$ROOT에 접속해서 ORA12RF PDB의 히든 파라미터가 설정된 것을 확인
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 24 01:06:27 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rs1]<SYS@ORA19RS1>$ show con_name
CON_NAME
------------------------------
CDB$ROOT
[ol8ora19rs1]<SYS@ORA19RS1>$
-- SQLPlus Formatting
SET LINESIZE 200
SET PAGESIZE 100
SET COLSEP ' | '
SET VERIFY OFF
COL PDB_NAME FORMAT A15 HEADING 'PDB Name'
COL PARAM_NAME FORMAT A45 HEADING 'Hidden Parameter Name'
COL VALUE FORMAT A30 HEADING 'SPFILE Value'
COL SID FORMAT A10 HEADING 'SID'
COL PDB_UID FORMAT 9999999999 HEADING 'PDB UID'
-- Query
SELECT P.NAME AS PDB_NAME
, S.NAME AS PARAM_NAME
, S.VALUE$ AS VALUE
, S.SID AS SID
, S.PDB_UID
FROM SYS.PDB_SPFILE$ S
JOIN V$PDBS P
ON S.PDB_UID = P.CON_UID
WHERE S.NAME LIKE '\_%' ESCAPE '\' -- 히든 파라미터만 조회
AND P.NAME = 'ORA12RF' -- 대상 PDB 지정
ORDER BY 2;
PDB Name | Hidden Parameter Name | SPFILE Value | SID | PDB UID
--------------- | --------------------------------------------- | ------------------------------ | ---------- | -----------
ORA12RF | _optimizer_gather_stats_on_load | FALSE | * | 30846782
1 row selected.
Elapsed: 00:00:00.01
반응형