[제목]
[2025-11-13] 12cR1의 신기능인 MAX_STRING_SIZE를 extended로 설정 후 Flashback database로 다시 Standard로 돌아올수 있는지 테스트
[테스트 개요]
12cR1 RAC Non-CDB 환경에서
MAX_STRING_SIZE=STANDARD 상태를 RMAN 풀 백업 및 Flashback Database로 보호하는 구성을
준비했다.
FLASHBACK DATABASE ON 및 GUARANTEED RESTORE POINT 생성 후, STARTUP UPGRADE
+ utl32k.sql로 MAX_STRING_SIZE를 EXTENDED로 변경하고 10,000바이트 VARCHAR2 컬럼 생성/데이터 적재까지
수행했다.
이후 해당 Restore Point로 Flashback Database(RESETLOGS +
MAX_STRING_SIZE=STANDARD 재설정)를 수행하여 다시 4,000바이트 제한 상태로 정상 복귀 가능함을 검증하고,
Flashback 실패 시 RMAN 백업으로도 동일 시점 복구 가능함을 확인했다.
[테스트 환경]
OS : Oracle Linux Server 7.9 (grep
^PRETTY_NAME= /etc/os-release | cut -d= -f2- | tr -d '"')
OS Kernal :
5.4.17-2102.201.3.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-CDB
RAC Node
1
Hostname : ol7ora12rf1
(hostname)
Public IP : 192.168.240.11 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA12R11 (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
Public IP : 192.168.240.12 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA12R12 (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)
33678030;OCW JAN 2022 RELEASE UPDATE 12.2.0.1.220118
(33678030)
33587128;Database Jan 2022 Release Update :
12.2.0.1.220118 (33587128)
[내용]
1. 현재 max_string_size 파라미터 상태 확인
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
st
alias st='rlwrap sqlplus tuner/oracle'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ st
SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 12 14:55:30 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Wed Nov 12 2025 00:26:42 +09:00
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12rf1]<TUNER@ORA12RF1>$ show parameter max_string_size
NAME
TYPE
VALUE
------------------------------------ ---------------------------------
------------------------------
max_string_size
string
STANDARD
[ol7ora12rf1]<TUNER@ORA12RF1>$ show
user;
USER is "TUNER"
[ol7ora12rf1]<TUNER@ORA12RF1>$ create table tb_test_varchar2_10000
(test_col varchar2(10000));
create table tb_test_varchar2_10000
(test_col
varchar2(10000))
*
ERROR at line 1:
ORA-00910: specified length too long for its
datatype
Elapsed:
00:00:00.00
[ol7ora12rf1]<TUNER@ORA12RF1>$
--> 현재는 varchar2 칼럼이 4000 바이트를 초과한 칼럼을 생성할수
없음
2. DB 풀 백업
-- 추후 원상 복구를 위한 디비 풀 백업
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ mkdir -pv
/home/oracle/backup
mkdir: created directory
‘/home/oracle/backup’
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'< /STRONG>
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 12 15:08:58 2025
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>$ create pfile='/home/oracle/backup/initORA12RF1.ora' from spfile;
File created.
Elapsed: 00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ show parameter
spfile
NAME
TYPE
VALUE
------------------------------------ ---------------------------------
--------------------------------------------------
spfile
string
+DATA1/ORA12RF/PARAMETERFILE/spfile.276.1216917171
[ol7ora12rf1]<SYS@ORA12RF1>$ quit
Disconnected from Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ echo
$ORACLE_UNQNAME
ORA12RF
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ echo
$ORACLE_SID
ORA12RF1
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ echo
$ORACLE_DBNAME
ORA12RF
[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 Wed Nov 12 15:10:09 2025
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/%F';
CONFIGURE
SNAPSHOT CONTROLFILE NAME TO '/home/oracle/backup/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 {
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
sql
'alter system archive log current';
sql 'alter system
checkpoint';
crosscheck backupset;
crosscheck backup;
crosscheck
copy;
crosscheck archivelog all;
BACKUP AS COMPRESSED BACKUPSET database
FORMAT '/home/oracle/backup/ORA12RF_%U.bkp';
backup current controlfile
format '/home/oracle/backup/ORA12RF_CTL_%U_%T';
delete noprompt
obsolete;
delete noprompt expired backup;
CONFIGURE CONTROLFILE AUTOBACKUP
ON;
}
run {
crosscheck archivelog all;
backup archivelog
all format '/home/oracle/backup/%d_ARCHIVE_%T_%u_s%s_p%p' delete input;
}
RMAN> list
backup;
--풀 백업 현황을
확인함
List of Backup
Sets
===================
BS Key Type LV
Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
89 Full
6.21G
DISK 00:05:06
2025-11-12 15:24:33
BP Key:
89 Status: AVAILABLE Compressed: YES Tag:
TAG20251112T151927
Piece Name:
/home/oracle/backup/ORA12RF_3048jrnv_1_1.bkp
List of Datafiles in
backup set 89
File LV Type Ckp SCN Ckp
Time Abs Fuz
SCN Sparse Name
---- -- ---- ---------- -------------------
----------- ------ ----
1 Full
7122352 2025-11-12
15:19:27
NO +DATA1/ORA12RF/DATAFILE/system.257.1213483305
2 Full 7122352 2025-11-12
15:19:27
NO
+DATA1/ORA12RF/DATAFILE/tuner_data1.272.1216160599
3 Full 7122352 2025-11-12
15:19:27
NO +DATA1/ORA12RF/DATAFILE/sysaux.258.1213483329
4 Full 7122352 2025-11-12
15:19:27
NO +DATA1/ORA12RF/DATAFILE/undotbs1.259.1213483345
5 Full 7122352 2025-11-12
15:19:27
NO +DATA1/ORA12RF/DATAFILE/undotbs2.266.1213483407
7 Full 7122352 2025-11-12
15:19:27
NO +DATA1/ORA12RF/DATAFILE/users.260.1213483345
8 Full
7122352 2025-11-12
15:19:27
NO
+DATA1/ORA12RF/DATAFILE/tuner_idx1.273.1216160601
BS Key Type LV
Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
90 Full
1.09M
DISK 00:00:01
2025-11-12 15:24:44
BP Key:
90 Status: AVAILABLE Compressed: YES Tag:
TAG20251112T151927
Piece Name:
/home/oracle/backup/ORA12RF_3148js1r_1_1.bkp
SPFILE Included:
Modification time: 2025-11-12 01:10:27
SPFILE db_unique_name:
ORA12RF
Control File Included: Ckp SCN:
7124798 Ckp time: 2025-11-12
15:24:43
BS Key Type LV
Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
91 Full
19.13M DISK
00:00:01 2025-11-12
15:24:46
BP Key: 91
Status: AVAILABLE Compressed: NO Tag:
TAG20251112T152445
Piece Name:
/home/oracle/backup/ORA12RF_CTL_3248js1t_1_1_20251112
Control File Included: Ckp SCN:
7125228 Ckp time: 2025-11-12
15:24:45
BS Key Size
Device Type Elapsed Time Completion Time
------- ---------- -----------
------------ -------------------
92
43.34M DISK
00:00:00 2025-11-12
16:13:59
BP Key: 92
Status: AVAILABLE Compressed: NO Tag:
TAG20251112T161359
Piece Name:
/home/oracle/backup/ORA12RF_ARCHIVE_20251112_3348juu7_s99_p1
List of Archived Logs in backup set 92
Thrd Seq Low SCN Low
Time Next
SCN Next Time
---- ------- ---------- -------------------
---------- ---------
1
6 7122249 2025-11-12
15:19:23 7140417 2025-11-12 16:13:58
2 5
7122260 2025-11-12 15:19:25 7140420
2025-11-12 16:13:59
--> scn 기준 7140417-1=7140416
까지 복구가 가능한 상황임< /STRONG>
BS Key Type LV
Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
93 Full
19.16M DISK
00:00:00 2025-11-12
16:14:00
BP Key: 93
Status: AVAILABLE Compressed: NO Tag:
TAG20251112T161400
Piece Name:
/home/oracle/backup/c-4288610047-20251112-01
SPFILE Included:
Modification time: 2025-11-12 16:00:57
SPFILE db_unique_name:
ORA12RF
Control File Included: Ckp SCN:
7140445 Ckp time: 2025-11-12 16:14:00
-->
/home/oracle/backup/c-4288610047-20251112-01 컨트롤 파일로 복구 시
--> 백업 받은
/home/oracle/backup/ORA12RF_ARCHIVE_20251112_3348juu7_s99_p1 아카이브를 인식할 수
있음
3. FLASHBACK DATABASE 기능 ON
--grid os
user
[+ASM1:grid@ol7ora12rf1][/home/grid]$
alias asmcmd
alias asmcmd='rlwrap asmcmd
-p'< /STRONG>
[+ASM1:grid@ol7ora12rf1][/home/grid]$
asmcmd ls -sl
State Type Rebal Sector
Logical_Sector Block AU
Total_MB Free_MB Req_mir_free_MB Usable_file_MB
Offline_disks Voting_files Name
MOUNTED NORMAL
N
512
512 4096 4194304
6144
5276
2048
1614
0
Y CRS/
MOUNTED EXTERN
N
512
512 4096 4194304
65536
28208
0
28208
0
N DATA1/
MOUNTED EXTERN
N
512
512 4096 4194304
65536
64132
0
64132
0
N FRA1/
MOUNTED EXTERN
N
512
512 4096 4194304
40960
6812
0
6812
0
N MGMT/
--oracle os 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 Nov 13 09:38:55 2025
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>$ select flashback_on from v$database;
FLASHBACK_ON
------------------------------------------------------
NO
1 row selected.
Elapsed: 00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ show parameter
db_recovery_file_dest
NAME
TYPE
VALUE
------------------------------------ ---------------------------------
------------------------------
db_recovery_file_dest
string
(NULL)
db_recovery_file_dest_size
big
integer
0
[ol7ora12rf1]<SYS@ORA12RF1>$ show parameter
db_flashback_retention_target
NAME
TYPE
VALUE
------------------------------------ ---------------------------------
------------------------------
db_flashback_retention_target
integer
1440
--flashback database 활성화를 위한 파라미터 설정
[ol7ora12rf1]<SYS@ORA12RF1>$ alter system set db_recovery_file_dest= '+FRA1'
scope=spfile sid= '*';
System altered.
Elapsed: 00:00:00.06
[ol7ora12rf1]<SYS@ORA12RF1>$ alter system set db_recovery_file_dest_size= 32767M
scope=spfile sid= '*';< /FONT>
< /STRONG>
System altered.< /FONT> < /STRONG>
Elapsed: 00:00:00.01
[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 config database -db
ora12rf
Database unique name: ORA12RF
Database name:
ORA12RF
Oracle home: /u01/app/oracle/product/12c/db_1
Oracle user:
oracle
Spfile: +DATA1/ORA12RF/PARAMETERFILE/spfile.276.1216917171
Password
file: +DATA1/ORA12RF/PASSWORD/pwdora12rf.256.1213483291
Domain:
Start
options: open
Stop options: immediate
Database role: PRIMARY
Management
policy: AUTOMATIC
Server pools:
Disk Groups: FRA1,DATA1
Mount point
paths:
Services:
Type: RAC
Start concurrency:
Stop
concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances:
ORA12RF1,ORA12RF2
Configured nodes: ol7ora12rf1,ol7ora12rf2
CSS critical:
no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network
number for database services:
Database is administrator
managed
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
csrt
alias csrt= 'crsctl stat res -t'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
csrt
--------------------------------------------------------------------------------
Name
Target State
Server
State
details
--------------------------------------------------------------------------------
Local
Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.CRS.dg
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.DATA1.dg
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.FRA1.dg
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.LISTENER.lsnr
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.MGMT.dg
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.chad
ONLINE OFFLINE
ol7ora12rf1
STABLE
ONLINE OFFLINE
ol7ora12rf2
STABLE
ora.net1.network
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.ons
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
--------------------------------------------------------------------------------
Cluster
Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 OFFLINE
OFFLINE
STABLE
ora.LISTENER_SCAN2.lsnr
1 OFFLINE
OFFLINE
STABLE
ora.LISTENER_SCAN3.lsnr
1 OFFLINE
OFFLINE
STABLE
ora.MGMTLSNR
1 OFFLINE
OFFLINE
STABLE
ora.asm
1 ONLINE
ONLINE
ol7ora12rf1
Started,STABLE
2 ONLINE
ONLINE
ol7ora12rf2
Started,STABLE
ora.cvu
1 OFFLINE
OFFLINE
STABLE
ora.mgmtdb
1 ONLINE
OFFLINE
STABLE
ora.ol7ora12rf1.vip
1 ONLINE
ONLINE
ol7ora12rf1
STABLE
ora.ol7ora12rf2.vip
1 ONLINE
ONLINE
ol7ora12rf2
STABLE
ora.ora12rf.db
1 ONLINE
ONLINE
ol7ora12rf1
Open,HOME=/u01/app/o
racle/product/12c/db
_1,STABLE
2 ONLINE
ONLINE
ol7ora12rf2
Open,HOME=/u01/app/o
racle/product/12c/db
_1,STABLE
ora.qosmserver
1 OFFLINE
OFFLINE
STABLE
ora.scan1.vip
1 OFFLINE
OFFLINE
STABLE
ora.scan2.vip
1 OFFLINE
OFFLINE
STABLE
ora.scan3.vip
1 OFFLINE
OFFLINE
STABLE
--------------------------------------------------------------------------------
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
srvctl stop database -db ora12rf
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
csrt
--------------------------------------------------------------------------------
Name
Target State
Server
State
details
--------------------------------------------------------------------------------
Local
Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.CRS.dg
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.DATA1.dg
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.FRA1.dg
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.LISTENER.lsnr
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.MGMT.dg
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.chad
ONLINE OFFLINE
ol7ora12rf1
STABLE
ONLINE OFFLINE
ol7ora12rf2
STABLE
ora.net1.network
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.ons
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
--------------------------------------------------------------------------------
Cluster
Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 OFFLINE
OFFLINE
STABLE
ora.LISTENER_SCAN2.lsnr
1 OFFLINE
OFFLINE
STABLE
ora.LISTENER_SCAN3.lsnr
1 OFFLINE
OFFLINE
STABLE
ora.MGMTLSNR
1 OFFLINE
OFFLINE
STABLE
ora.asm
1 ONLINE
ONLINE
ol7ora12rf1
Started,STABLE
2 ONLINE
ONLINE
ol7ora12rf2
Started,STABLE
ora.cvu
1 OFFLINE
OFFLINE
STABLE
ora.mgmtdb
1 ONLINE
OFFLINE
STABLE
ora.ol7ora12rf1.vip
1 ONLINE
ONLINE
ol7ora12rf1
STABLE
ora.ol7ora12rf2.vip
1 ONLINE
ONLINE
ol7ora12rf2
STABLE
ora.ora12rf.db
1 OFFLINE
OFFLINE
Instance
Shutdown,ST
ABLE
2 OFFLINE
OFFLINE
Instance
Shutdown,ST
ABLE
ora.qosmserver
1 OFFLINE
OFFLINE
STABLE
ora.scan1.vip
1 OFFLINE
OFFLINE
STABLE
ora.scan2.vip
1 OFFLINE
OFFLINE
STABLE
ora.scan3.vip
1 OFFLINE
OFFLINE
STABLE
--------------------------------------------------------------------------------
[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 Nov 13 09:50:23 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
--flashback database 설정은 mount상태에서 해야함
[ol7ora12rf1]<SYS@ORA12RF1>$ startup mount
ORACLE instance started.
Total System Global Area 5033164800 bytes
Fixed
Size
8631048 bytes
Variable
Size
1476398328 bytes
Database
Buffers 3539992576 bytes
Redo
Buffers
8142848 bytes
Database mounted.
[ol7ora12rf1]<SYS@ORA12RF1>$ show parameter
db_recovery_file_dest
NAME
TYPE
VALUE
------------------------------------ ---------------------------------
------------------------------
db_recovery_file_dest
string
+FRA1
db_recovery_file_dest_size
big
integer
32767M
[ol7ora12rf1]<SYS@ORA12RF1>$ show parameter
db_flashback_retention_target
NAME
TYPE
VALUE
------------------------------------ ---------------------------------
------------------------------
db_flashback_retention_target
integer
2880
--flashback database 활성화
[ol7ora12rf1]<SYS@ORA12RF1>$ alter database flashback
on;
Database altered.
Elapsed: 00:00:06.09
[ol7ora12rf1]<SYS@ORA12RF1>$ alter database
open;
Database altered.
Elapsed: 00:00:00.81
[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 start instance -database ora12rf -instance
ORA12RF2
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
csrt
alias csrt='crsctl stat res
-t'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ csrt
--------------------------------------------------------------------------------
Name
Target State
Server
State
details
--------------------------------------------------------------------------------
Local
Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.CRS.dg
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.DATA1.dg
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.FRA1.dg
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.LISTENER.lsnr
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.MGMT.dg
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.chad
ONLINE OFFLINE
ol7ora12rf1
STABLE
ONLINE OFFLINE
ol7ora12rf2
STABLE
ora.net1.network
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
ora.ons
ONLINE ONLINE
ol7ora12rf1
STABLE
ONLINE ONLINE
ol7ora12rf2
STABLE
--------------------------------------------------------------------------------
Cluster
Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 OFFLINE
OFFLINE
STABLE
ora.LISTENER_SCAN2.lsnr
1 OFFLINE
OFFLINE
STABLE
ora.LISTENER_SCAN3.lsnr
1 OFFLINE
OFFLINE
STABLE
ora.MGMTLSNR
1 OFFLINE
OFFLINE
STABLE
ora.asm
1 ONLINE
ONLINE
ol7ora12rf1
Started,STABLE
2 ONLINE
ONLINE
ol7ora12rf2
Started,STABLE
ora.cvu
1 OFFLINE
OFFLINE
STABLE
ora.mgmtdb
1 ONLINE
OFFLINE
STABLE
ora.ol7ora12rf1.vip
1 ONLINE
ONLINE
ol7ora12rf1
STABLE
ora.ol7ora12rf2.vip
1 ONLINE
ONLINE
ol7ora12rf2
STABLE
ora.ora12rf.db
1 ONLINE
ONLINE
ol7ora12rf1
Open,HOME=/u01/app/o
racle/product/12c/db
_1,STABLE
2 ONLINE
ONLINE
ol7ora12rf2
Open,HOME=/u01/app/o
racle/product/12c/db
_1,STABLE
ora.qosmserver
1 OFFLINE
OFFLINE
STABLE
ora.scan1.vip
1 OFFLINE
OFFLINE
STABLE
ora.scan2.vip
1 OFFLINE
OFFLINE
STABLE
ora.scan3.vip
1 OFFLINE
OFFLINE
STABLE
--------------------------------------------------------------------------------
[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 Nov 13 09:55:19 2025
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>$ select flashback_on from v$database;
FLASHBACK_ON
------------------------------------------------------
YES
1 row selected.
Elapsed: 00:00:00.01
--grid os
user
[+ASM1:grid@ol7ora12rf1][/home/grid]$
alias asmcmd
alias asmcmd='rlwrap asmcmd
-p'
[+ASM1:grid@ol7ora12rf1][/home/grid]$ asmcmd
ls -sl +FRA1/ORA12RF/FLASHBACK
Type Redund
Striped
Time
Sys Block_Size Blocks
Bytes Space Name
FLASHBACK
UNPROT COARSE NOV 13 09:00:00
Y 8192
25601 209723392 213909504
log_1.308.1217065975
FLASHBACK UNPROT COARSE NOV 13
09:00:00 Y
8192 25601 209723392 213909504
log_2.303.1217065977
FLASHBACK UNPROT COARSE NOV 13
09:00:00 Y
8192 25601 209723392 213909504
log_3.310.1217066089
FLASHBACK UNPROT
COARSE NOV 13 09:00:00
Y 8192
25601 209723392 213909504
log_4.327.1217066091
4. 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 Nov 13 10:03:36 2025
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>$ select current_scn from v$database;
CURRENT_SCN
-----------
7429969
1 row selected.
Elapsed: 00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ create restore point
before_max_string_size_extended guarantee flashback
database;
Restore point created.
Elapsed: 00:00:00.02
[ol7ora12rf1]<SYS@ORA12RF1>$
col name for
a50
select scn, NAME from
v$restore_point;
SCN
NAME
----------
--------------------------------------------------
7429991
BEFORE_MAX_STRING_SIZE_EXTENDED
1 row selected.
Elapsed: 00:00:00.01
5. MAX_STRING_SIZE 를 extended로
변경
[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 Nov 13 10:06:47 2025
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>$ show parameter compatible
NAME
TYPE
VALUE
------------------------------------ ---------------------------------
------------------------------
compatible
string
12.2.0
noncdb_compatible
boolean
FALSE
[ol7ora12rf1]<SYS@ORA12RF1>$ show parameter
cluster_database
NAME
TYPE
VALUE
------------------------------------ ---------------------------------
------------------------------
cluster_database
boolean
TRUE
cluster_database_instances
integer
2
[ol7ora12rf1]<SYS@ORA12RF1>$ alter system set cluster_database= FALSE scope= spfile sid= '*';</FONT> < /STRONG>
System altered.
Elapsed: 00:00:00.04
[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]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 10 23:14:27 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
[ol7ora12rf1]<SYS@ORA12RF1>$ STARTUP UPGRADE;
ORACLE instance started.
Total System Global Area 5033164800 bytes
Fixed
Size
8631048 bytes
Variable
Size
1409289464 bytes
Database
Buffers 3607101440 bytes
Redo
Buffers
8142848 bytes
Database mounted.
Database opened.
[ol7ora12rf1]<SYS@ORA12RF1>$ ALTER SYSTEM SET max_string_size= extended scope= BOTH SID= '*';
System altered.
Elapsed: 00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ PURGE
DBA_RECYCLEBIN;
DBA Recyclebin purged.
Elapsed: 00:00:00.10
[ol7ora12rf1]<SYS@ORA12RF1>$ quit
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ cat $ORACLE_HOME/rdbms/admin/utl32k.sql
aRem
Rem $Header: rdbms/admin/utl32k.sql
/st_rdbms_12.2.0.1.0/1 2016/08/06 02:27:50 geadon Exp $
Rem
Rem
utl32k.sql
Rem
Rem Copyright (c) 2011, 2016, Oracle and/or its
affiliates.
Rem All rights reserved.
Rem
Rem
NAME
Rem utl32k.sql - <one-line expansion of
the name>
Rem
Rem
DESCRIPTION
Rem <short description of
component this file declares/defines>
Rem
Rem
NOTES
Rem <other useful comments,
qualifications, etc.>
Rem
Rem MODIFIED
(MM/DD/YY)
Rem geadon
07/21/16 - XbranchMerge geadon_bug-22650793 from main
Rem
geadon 03/28/16 - bug 22375145: add ORDER BY for
column migration
Rem sjanardh 11/25/15 -
Replace dbms_aqadm_syscalls APIs w/ dbms_aqadm_sys APIs
Rem
acolunga 11/05/15 - bug 21996904: do not update virtual
columns
in
Rem
xml index path tables.
Rem
geadon 09/28/15 - bug 21907429: enable AQ
DDL
Rem rafsanto 09/10/15 - Bug 21793725:
Let DDL through for csx tokensets
Rem
sanagara 08/12/15 - 21615157: use
DBMS_SQL.DESCRIBE_COLUMNS3
Rem rafsanto
06/03/15 - Bug 20911289 - Let DML go through for
csx
Rem
tokenset objects
Rem pjayapal 10/28/14 -
bug 19063812: procedure to parse mv defn query
Rem
geadon 11/20/13 - bug 16894689: run utlrp.sql in
normal mode
Rem geadon
02/26/13 - bug 16237862: long col names for
describe_columns
Rem geadon
09/24/12 - bug 13824121: fix PROPS$ property name
Rem
geadon 03/14/12 - bug 13332548: enforce utl32k
upgrade
for
Rem
max_sql_string_size
Rem
geadon 12/28/11 - script to set
MAX_SQL_STRING_SIZE=EXTENDED
Rem
geadon 12/28/11 - Created
Rem
Rem Exit immediately if Any failure in this
script
WHENEVER SQLERROR EXIT;
alter session set current_schema = SYS;
DOC
#######################################################################
#######################################################################
The following statement will cause an "ORA-01722: invalid
number"
error if the
database has not been opened for UPGRADE.
Perform a "SHUTDOWN ABORT"
and
restart using
UPGRADE.
#######################################################################
#######################################################################
#
SELECT
TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance
WHERE
status != 'OPEN MIGRATE';
DOC
#######################################################################
#######################################################################
The following statement will cause an "ORA-01722: invalid
number"
error if the
database does not have compatible >= 12.0.0
Set compatible >= 12.0.0 and
retry.
#######################################################################
#######################################################################
#
DECLARE
dummy number;
BEGIN
SELECT CASE WHEN TO_NUMBER(SUBSTR(value,1,2)) <
12
THEN
TO_NUMBER('COMPATIBLE_12.1.0_REQUIRED')
ELSE
NULL
END INTO dummy
FROM v$parameter WHERE name =
'compatible';
END;
/
alter session set "_oracle_script" = TRUE;
---------------------------------------------------------------------------
--
invalidate views that have VARCHAR or NVARCHAR columns >= 4000 bytes or
--
RAW columns >= 2000 bytes because these columns could change when
-- 32k VARCHAR2,
NVARCHAR2, and RAW are enabled.
update obj$ o
set status = 6
where
type# IN (4)
and status not in (5,
6)
and bitand(flags, 196608) =
0
/* not a common object */
and exists (select * from col$
c
where c.obj# =
o.obj#
and ( (c.type# = 1 and c.length >= 4000)
or
(c.type# = 23 and c.length >= 2000) ));
commit;
alter system flush shared_pool;
---------------------------------------------------------------------------
--
invalidate views and synonyms that depend on views that have been
-- invalided
begin
loop
update obj$ o_outer set status =
6
where type#
in (4, 5)
and status
not in (5, 6)
and
bitand(flags, 196608) =
0
/* not a common object
*/
and linkname is
null
and ((subname is
null) or (subname <>
'DBMS_DBUPGRADE_BABY'))
and exists (select o.obj# from obj$ o, dependency$
d
where d.d_obj# =
o_outer.obj#
and d.p_obj# =
o.obj#
and (bitand(d.property, 1) =
1)
and o.status > 1);
exit when sql%notfound;
end loop;
end;
/
commit;
alter system flush shared_pool;
---------------------------------------------------------------------------
--
Now, update virtual columns if their length has changed as a result
-- of enabling 32k
types.
-- Bug 21996904: during selection of candidate columns,
we should leave out
-- columns from path tables. Path tables are internal
unstructured XML Index
-- (UXI) tables and they should not be changed
since:
-- (1) UXI code is not going to be changed in any near future, if
required
-- the XMLIndex layer will take care of
future changes in utl32k regarding
-- this
matter.
-- (2) The column left out from the path table is a virtual column
with
-- SYS_ORDERKEY_PARENT("ORDER_KEY") as
expression. Such operator does not
-- return a 32 kB
type and it is safe to skip during migration.
-- This will be checked by
asserting that the candidate columns do not belong
--
to tables specified in path_table_names of dba_xml_indexes
alter session set "_modify_column_index_unusable" =
TRUE;
--Turn on csx dml internal event to let internal columns be
reloaded
alter session set events '19056 trace name context forever,
level 0x10';
create table utl32k_errors(uname
varchar2(128),
tname
varchar2(128),
cname
varchar2(128),
obj#
int,
intcol#
int,
data_type
varchar2(9),
data_len
int,
char_used
varchar2(4),
virtual
varchar2(1),
mv
varchar2(1),
error
varchar2(4000),
sqlstr
clob,
when timestamp);
create table
utl32k_warnings(uname
varchar2(128),
tname
varchar2(128),
warning
varchar2(4000),
when timestamp);
-- find all indexes that initially have
KQLDINF_TOBE_DROPPED flag set
create table utl32k_ini_tobe_dropped(obj#
int);
truncate table utl32k_ini_tobe_dropped;
insert into
utl32k_ini_tobe_dropped
select obj# from sys.ind$
where bitand(flags, 536870912) != 0;
variable starttime varchar2(128)
exec :starttime :=
to_char(systimestamp, 'MM/DD/YYYY HH24:MI:SS.FF');
print starttime
DECLARE
cursor candidate_columns
is
select u.name
UNAME,
o.name
TNAME,
o.obj#
OBJ#,
DECODE(c.type#,
1, DECODE(c.charsetform, 2, 'NVARCHAR2',
'VARCHAR2'),
23, 'RAW')
DATA_TYPE,
c.spare3
DATA_LEN,
decode(bitand(c.property, 8388608), 0, 'BYTE', 'CHAR')
CHAR_USED,
c.name CNAME,
c.intcol#
INTCOL#,
decode(c.segcol#, 0, 'Y', 'N')
VIRTUAL,
decode(bitand(t.flags, 262144), 0, 'N', 'Y') MV
from
sys.tab$ t, sys.obj$ o, sys.user$ u, sys.col$ c
where t.obj# = o.obj# and o.owner# = u.user# and c.obj# =
t.obj#
and bitand(o.flags, 196608) =
0
-- not a common object
and (c.segcol# =
0
or
-- virtual
column
bitand(c.property, 8388608) != 0 or -- CHAR length
semantics
bitand(t.flags, 262144) != 0) --
materialized view tbl
and ( (c.type# =
1 and c.length >= 4000)
or
(c.type# = 23 and c.length >= 2000) )
and (u.name, o.name) not
in
(select
index_owner, path_table_name from dba_xml_indexes)
order by UNAME, TNAME,
VIRTUAL; -- bug 22375145: physical before virtual
schema_table
varchar2(512); /*
"SCHEMA"."TABLE" for SQL generation */
data_type
varchar2(128);
cur integer;
col_cnt integer;
len integer;
off integer;
discard integer;
str
varchar2(32767);
strlen
integer;
col_desc
DBMS_SQL.DESC_TAB2;
col_expr clob;
sqlstr clob;
mv_str clob;
long_chunk_sz
constant int := 256;
parse_proc varchar2(32767);
parse_exec varchar2(128);
parse_dele
varchar2(128);
tokenset_dml varchar2(30);
mv_objn int;
mv_status
int;
mv_owner varchar2(128);
mv_name varchar2(128);
mv_schema_table
varchar2(512); /* "SCHEMA"."TABLE" for SQL
generation */
BEGIN
-- Bug 21907429: enable DDL on AQ
tables
sys.dbms_aqadm_sys.Mark_Internal_Tables(dbms_aqadm_sys.ENABLE_AQ_DDL);
cur := DBMS_SQL.OPEN_CURSOR;
for target
in candidate_columns loop
BEGIN
schema_table := DBMS_ASSERT.ENQUOTE_NAME(target.UNAME, FALSE) || '.'
|| DBMS_ASSERT.ENQUOTE_NAME(target.TNAME, FALSE);
IF (target.virtual = 'Y') THEN
DBMS_SQL.PARSE(cur,
'select
deflength, default$ from col$ where obj# = :1 and name = :2',
DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cur, ':1', target.obj#);
DBMS_SQL.BIND_VARIABLE(cur,
':2', target.cname);
DBMS_SQL.DEFINE_COLUMN(cur, 1, len);
DBMS_SQL.DEFINE_COLUMN_LONG(cur, 2);
discard :=
DBMS_SQL.EXECUTE(cur);
discard := DBMS_SQL.FETCH_ROWS(cur);
DBMS_SQL.COLUMN_VALUE(cur,
1, len);
col_expr :=
'';
off := 0;
WHILE len > 0 LOOP
DBMS_SQL.COLUMN_VALUE_LONG(cur, 2, long_chunk_sz, off, str, strlen);
col_expr :=
col_expr || str;
off := off +
strlen;
len
:= len - strlen;
END
LOOP;
/* Bug 20911289: let dml to
go through for csx tokenset objects
* This is required for
the virtual column in QN index to be aliased*/
tokenset_dml := '/*+
XMLTSET_DML_ENABLE*/ ';
/* Bug 16237862: use an
alias for the column to avoid column
* name overflow errors
in dbms_sql.describe_columns.
*/
sqlstr := 'SELECT '
|| tokenset_dml || col_expr || ' expr FROM ' || schema_table;
DBMS_SQL.PARSE(cur, sqlstr,
DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS2(cur, col_cnt, col_desc);
data_type := target.DATA_TYPE || '(' || col_desc(1).col_max_len || ')';
ELSIF (target.mv = 'Y') THEN
DBMS_SQL.PARSE(cur,
'select
sumtextlen, sumtext, obj# from sys.sum$ ' ||
'where
containerobj# = :1',
DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cur, ':1', target.obj#);
DBMS_SQL.DEFINE_COLUMN(cur,
1, len);
DBMS_SQL.DEFINE_COLUMN_LONG(cur, 2);
DBMS_SQL.DEFINE_COLUMN(cur,
3, mv_objn);
discard :=
DBMS_SQL.EXECUTE(cur);
discard := DBMS_SQL.FETCH_ROWS(cur);
DBMS_SQL.COLUMN_VALUE(cur,
1, len);
mv_str := '';
off := 0;
WHILE len > 0 LOOP
DBMS_SQL.COLUMN_VALUE_LONG(cur, 2, long_chunk_sz, off, str, strlen);
mv_str := mv_str
|| str;
off
:= off + strlen;
len := len -
strlen;
END LOOP;
DBMS_SQL.COLUMN_VALUE(cur,
3, mv_objn);
-- check status of the MV
EXECUTE IMMEDIATE
'select u.name,
o.name, o.status from sys.obj$ o, sys.user$ u ' ||
'where o.obj# =
:1 and o.owner# = u.user#'
into mv_owner,
mv_name, mv_status
using mv_objn;
mv_schema_table :=
DBMS_ASSERT.ENQUOTE_NAME(mv_owner, FALSE) || '.' ||
DBMS_ASSERT.ENQUOTE_NAME(mv_name, FALSE);
IF mv_status != 1 THEN
sqlstr := 'alter
materialized view ' || mv_schema_table ||
' compile';
EXECUTE IMMEDIATE sqlstr;
EXECUTE
IMMEDIATE
'select u.name, o.name, o.status from sys.obj$ o, sys.user$ u ' ||
'where o.obj# = :1 and o.owner# = u.user#'
into
mv_owner, mv_name, mv_status
using
mv_objn;
END
IF;
IF mv_status != 1 THEN
insert into
utl32k_warnings values(target.UNAME, target.TNAME,
'Materialized view container table ' || schema_table
||
'was not migrated because materialized view '
||
mv_schema_table
||
'has compilation errors. When compilation errors have been fixed, '
||
'column length in the container table can be updated manually '
||
'via ALTER MATERIALIZED VIEW <mview_name> '
||
'MODIFY(<col> <data type>).',
SYSTIMESTAMP);
ELSE
execute
immediate
'alter session set current_schema = '
||
DBMS_ASSERT.ENQUOTE_NAME(target.UNAME, FALSE);
sqlstr := mv_str;
-- Bug 19063812
: Incase of mview definition query,create a procedure
-- as part of
the mview owner schema to do the parsing. This is to
-- avoid ORA
2019 when parsing db links which maybe present in the
--
query.
parse_proc :=
'create or replace procedure utl32k_parsequery
(
sqlstr IN clob,
col_cnt OUT integer,
col_desc OUT DBMS_SQL.DESC_TAB2
)as
cur integer;
begin
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, sqlstr, DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS2(cur, col_cnt, col_desc);
DBMS_SQL.CLOSE_CURSOR(cur);
EXCEPTION WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cur);
RAISE;
end;';
execute immediate parse_proc;
parse_exec :=
'BEGIN utl32k_parsequery(:a, :b, :c); END;';
execute
immediate parse_exec
USING IN
sqlstr,OUT col_cnt,OUT col_desc;
data_type :=
target.DATA_TYPE
|| '(' || col_desc(target.intcol#).col_max_len || ')';
parse_dele :=
'drop procedure utl32k_parsequery';
execute
immediate parse_dele;
execute
immediate
'alter
session set current_schema = SYS';
END IF;
ELSE
IF (target.DATA_TYPE
= 'NVARCHAR2') THEN
data_type :=
target.DATA_TYPE
|| '(' || target.DATA_LEN || ')';
ELSE
data_type :=
target.DATA_TYPE
|| '(' || target.DATA_LEN || ' ' || target.CHAR_USED || ')';
END IF;
END IF;
sqlstr := 'ALTER TABLE ' || schema_table
|| ' MODIFY (' || DBMS_ASSERT.ENQUOTE_NAME(target.CNAME, FALSE)
|| ' ' || data_type || ')';
dbms_output.put_line(sqlstr);
EXECUTE
IMMEDIATE sqlstr;
EXCEPTION WHEN OTHERS THEN
execute immediate
'insert
into utl32k_errors(uname, tname, cname, obj#,
intcol#, data_type, data_len, char_used,
virtual, mv, error, sqlstr,
when)
values
(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)'
using target.uname, target.tname,
target.cname, target.obj#,
target.intcol#, target.data_type, target.data_len, target.char_used,
target.virtual, target.mv, sqlerrm, sqlstr,
systimestamp;
END;
end loop;
DBMS_SQL.CLOSE_CURSOR(cur);
END;
/
show errors;
-- Bug 21907429:
disable DDL on AQ tables since we've finished processing them
BEGIN
sys.dbms_aqadm_sys.Mark_Internal_Tables(dbms_aqadm_sys.DISABLE_AQ_DDL);
END;
/
--Turn off csx dml
internal event after finished processing columns
alter
session set events '19056 trace name context off';
alter
session set "_modify_column_index_unusable" = FALSE;
insert into
utl32k_warnings
select tu.name, tobj.name,
CASE WHEN
i.type# != 9 THEN
'Index "' || iu.name || '"."' || iobj.name
|| '" must be dropped because it now exceeds maximum key length.'
ELSE
'Domain index "' || iu.name || '"."' || iobj.name
|| '" is now unusable because length of the indexed column '
|| 'has changed. This index must be dropped and recreated.'
END,
systimestamp
from sys.ind$ i, obj$
tobj, obj$ iobj, user$ tu, user$ iu
where bitand(i.flags, 536870912) != 0
and i.bo# = tobj.obj# and
i.obj# = iobj.obj#
and
tobj.owner# = tu.user# and iobj.owner# = iu.user#
and i.obj# not in (select
obj# from utl32k_ini_tobe_dropped);
select uname, tname, warning from utl32k_warnings
where when > TO_TIMESTAMP(:starttime, 'MM/DD/YYYY
HH24:MI:SS.FF')
order by when;
select uname,
tname, cname, error from utl32k_errors
where when
> TO_TIMESTAMP(:starttime, 'MM/DD/YYYY HH24:MI:SS.FF')
order by when;
DOC
#######################################################################
#######################################################################
The following statement will cause an
"ORA-01722: invalid number"
error if we
encountered an error while modifying a column to
account for data type length change as a
result of enabling or
disabling 32k
types.
Contact Oracle support for assistance.
#######################################################################
#######################################################################
#
DECLARE
dummy number;
BEGIN
SELECT CASE WHEN COUNT(*) > 0 THEN
TO_NUMBER('ERROR IN UTL32K MIGRATION')
ELSE NULL
END INTO
dummy
FROM utl32k_errors
where when > TO_TIMESTAMP(:starttime,
'MM/DD/YYYY HH24:MI:SS.FF');
END;
/
-- update PROPS$
to indicate that we've completed migration in this container
DECLARE
mss_typ binary_integer; -- type of
max_string_size parameter
mss_len binary_integer; -- length of
max_string_size parameter
mss_prm varchar2(80); --
max_string_size parameter
BEGIN
-- get the MAX_STRING_SIZE parameter
mss_typ :=
SYS.DBMS_UTILITY.GET_PARAMETER_VALUE('MAX_STRING_SIZE',
mss_len, mss_prm);
update
props$ set value$ = mss_prm where name = 'MAX_STRING_SIZE';
END;
/
commit;
Rem Continue even
if there are SQL errors
WHENEVER SQLERROR
CONTINUE;
---------------------------------------------------------------------------
-- bug 16894689: utlrp.sql should be run in normal mode to
recompile all
-- invalid objects
alter package
SYS.DBMS_INTERNAL_LOGSTDBY compile;
alter package
SYS.PRVT_ADVISOR compile;
[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 Nov 13 10:15:50 2025
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>$ @?/rdbms/admin/utl32k.sql
SP2-0042: unknown
command "aRem" - rest of line ignored.
Session altered.
Elapsed:
00:00:00.00
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an
"ORA-01722: invalid number"
DOC> error if
the database has not been opened for UPGRADE.
DOC>
DOC> Perform a "SHUTDOWN ABORT" and
DOC> restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
Elapsed:
00:00:00.00
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an
"ORA-01722: invalid number"
DOC> error if
the database does not have compatible >= 12.0.0
DOC>
DOC> Set
compatible >= 12.0.0 and retry.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
Session altered.
Elapsed: 00:00:00.00
0 rows updated.
Elapsed: 00:00:00.18
Commit complete.
Elapsed: 00:00:00.00
System altered.
Elapsed: 00:00:00.04
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
Commit complete.
Elapsed: 00:00:00.00
System altered.
Elapsed: 00:00:00.02
Session altered.
Elapsed: 00:00:00.00
Session altered.
Elapsed: 00:00:00.00
Table created.
Elapsed: 00:00:00.02
Table created.
Elapsed: 00:00:00.00
Table created.
Elapsed: 00:00:00.01
Table truncated.
Elapsed: 00:00:00.00
0 rows created.
Elapsed: 00:00:00.03
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
STARTTIME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
11/13/2025 10:15:59.772438000
PL/SQL procedure successfully
completed.
Elapsed:
00:00:00.13
No errors.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
Session altered.
Elapsed: 00:00:00.00
Session altered.
Elapsed: 00:00:00.00
0 rows created.
Elapsed: 00:00:00.01
no rows selected
Elapsed: 00:00:00.00
no rows selected
Elapsed:
00:00:00.00
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an
"ORA-01722: invalid number"
DOC> error if
we encountered an error while modifying a column to
DOC> account for data type length change as
a result of enabling or
DOC> disabling
32k types.
DOC>
DOC> Contact Oracle support for
assistance.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
Commit complete.
Elapsed: 00:00:00.00
Package altered.
Elapsed: 00:00:00.46
Package altered.
Elapsed:
00:00:00.35
[ol7ora12rf1]<SYS@ORA12RF1>$ alter system set cluster_database=TRUE scope=spfile
sid='*';
System altered.
Elapsed: 00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ SHUTDOWN IMMEDIATE;
Database closed.
Database
dismounted.
ORACLE instance shut down.
[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 start
database -db 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 Mon Nov 10 23:16:57 2025
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>$ @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP
UTLRP_BGN
2025-11-13 10:19:20
1 row selected.
Elapsed:
00:00:00.06
DOC> The following PL/SQL
block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation
time is proportional to the
DOC> number
of invalid objects in the database, so this command may take
DOC> a long time to execute on a database
with a large number of invalid
DOC>
objects.
DOC>
DOC> Use the following queries to track
recompilation progress:
DOC>
DOC> 1. Query returning the number of
invalid objects remaining. This
DOC> number should
decrease with time.
DOC>
SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2.
Query returning the number of objects compiled so far. This number
DOC> should increase with
time.
DOC>
SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses
serial or parallel recompilation
DOC>
based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU
(parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all
RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to
create jobs for parallel
DOC>
recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the
following queries to verify
DOC> whether
UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1.
Query showing jobs created by UTL_RECOMP
DOC>
SELECT job_name FROM dba_scheduler_jobs
DOC>
WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that
are running
DOC>
SELECT job_name FROM dba_scheduler_running_jobs
DOC>
WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.86
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP
UTLRP_END
2025-11-13 10:19:22
1 row selected.
Elapsed:
00:00:00.01
DOC> The following query reports the
number of invalid objects.
DOC>
DOC> If the number is higher than expected, please
examine the error
DOC> messages reported with each
object (using SHOW ERRORS) to see if they
DOC> point
to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these
objects.
DOC>#
OBJECTS WITH
ERRORS
-------------------
0
1 row selected.
Elapsed:
00:00:00.00
DOC> The following query reports the
number of exceptions caught during
DOC>
recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if
any of these errors
DOC> are due to misconfiguration
or resource constraints that must be
DOC> fixed
before objects can compile successfully.
DOC> Note:
Typical compilation errors (due to coding errors) are not
DOC> logged into
this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING
RECOMPILATION
---------------------------
0
1 row selected.
Elapsed: 00:00:00.00
Function created.
Elapsed: 00:00:00.05
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
Function dropped.
Elapsed: 00:00:00.04
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.90
[ol7ora12rf1]<SYS@ORA12RF1>$
SELECT *
FROM
(
SELECT
OBJECT_NAME
, STATUS
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || '"'|| OBJECT_NAME ||
'"' || ' COMPILE;' AS DDL
FROM
DBA_OBJECTS
WHERE 1=1
AND OBJECT_TYPE IN ('PACKAGE')
AND STATUS
= 'INVALID'
)
UNION
ALL
SELECT *
FROM
(
SELECT
OBJECT_NAME
, STATUS
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER PACKAGE'|| ' ' || OWNER || '.' || '"'|| OBJECT_NAME || '"' || '
COMPILE BODY;' AS DDL
FROM
DBA_OBJECTS
WHERE 1=1
AND OBJECT_TYPE IN ('PACKAGE BODY')
AND STATUS
= 'INVALID'
)
UNION ALL
SELECT *
FROM
(
SELECT
OBJECT_NAME
, STATUS
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER PUBLIC ' || OBJECT_TYPE || ' ' || '"' || OBJECT_NAME || '"'
|| ' COMPILE;' AS DDL
FROM
DBA_OBJECTS
WHERE 1=1
AND OBJECT_TYPE IN ('SYNONYM') AND OWNER = 'PUBLIC'
AND STATUS = 'INVALID'
)
UNION ALL
SELECT *
FROM
(
SELECT
OBJECT_NAME
, STATUS
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || '"'|| OBJECT_NAME ||
'"' || ' COMPILE;' AS DDL
FROM
DBA_OBJECTS
WHERE 1=1
AND OBJECT_TYPE IN ('TYPE')
AND STATUS
= 'INVALID'
)
UNION
ALL
SELECT *
FROM
(
SELECT
OBJECT_NAME
, STATUS
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER TYPE' || ' ' || OWNER || '.' || '"'|| OBJECT_NAME || '"' ||
' COMPILE BODY;' AS DDL
FROM
DBA_OBJECTS
WHERE 1=1
AND OBJECT_TYPE IN ('TYPE BODY')
AND STATUS
= 'INVALID'
)
UNION
ALL
SELECT *
FROM
(
SELECT
OBJECT_NAME
, STATUS
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || '"'|| OBJECT_NAME ||
'"' || ' COMPILE;' AS DDL
FROM
DBA_OBJECTS
WHERE 1=1
AND OBJECT_TYPE IN ('VIEW')
AND STATUS
= 'INVALID'
)
UNION
ALL
SELECT *
FROM
(
SELECT
OBJECT_NAME
, STATUS
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || '"'|| OBJECT_NAME ||
'"' || ' COMPILE;' AS DDL
FROM
DBA_OBJECTS
WHERE 1=1
AND OBJECT_TYPE IN ('PROCEDURE')
AND STATUS
= 'INVALID'
)
UNION
ALL
SELECT *
FROM
(
SELECT
OBJECT_NAME
, STATUS
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || '"'|| OBJECT_NAME ||
'"' || ' COMPILE;' AS DDL
FROM
DBA_OBJECTS
WHERE 1=1
AND OBJECT_TYPE IN ('TRIGGER')
AND STATUS
= 'INVALID'
)
ORDER
BY 3
;
no rows selected
Elapsed: 00:00:00.09
[ol7ora12rf1]<SYS@ORA12RF1>$ show parameter max_string_size
NAME
TYPE
VALUE
------------------------------------
--------------------------------- ------------------------------
max_string_size
string
EXTENDED
[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
st
alias st='rlwrap
sqlplus tuner/oracle'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
st
SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 13 10:20:52 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Wed Nov 12 2025 14:55:30 +09:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
[ol7ora12rf1]<TUNER@ORA12RF1>$ create table tb_test_varchar2_10000 (data_col varchar2(10000));
Table created.
Elapsed: 00:00:00.09
[ol7ora12rf1]<TUNER@ORA12RF1>$
insert into
TUNER.TB_TEST_VARCHAR2_10000
select
dbms_random.string('u', 4000)||dbms_random.string('u',
4000)||dbms_random.string('u', 2000) as string_10000 from dual
;
commit;
[ol7ora12rf1]<TUNER@ORA12RF1>$
select length(DATA_COL) from
TUNER.TB_TEST_VARCHAR2_10000;
LENGTH(DATA_COL)
----------------
10000
1 row selected.
Elapsed: 00:00:00.00
[ol7ora12rf1]<SYS@ORA12RF1>$ drop table TB_TEST_VARCHAR2_10000 purge
Table dropped.
Elapsed: 00:00:00.22
6. MAX_STRING_SIZE의 파라미터값이 STANDARD인 시점으로 Flashback
Database 시도
[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 Mon Nov 10 23:21:46 2025
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 name for
a50
select scn, NAME from
v$restore_point;
SCN NAME
----------
--------------------------------------------------
7429991
BEFORE_MAX_STRING_SIZE_EXTENDED
1 row selected.
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]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 10 23:24:25 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
[ol7ora12rf1]<SYS@ORA12RF1>$ startup mount
ORACLE instance
started.
Total System
Global Area 5033164800 bytes
Fixed
Size
8631048 bytes
Variable
Size
1476398328 bytes
Database
Buffers 3539992576 bytes
Redo
Buffers
8142848 bytes
Database mounted.
[ol7ora12rf1]<SYS@ORA12RF1>$ flashback database to restore point BEFORE_MAX_STRING_SIZE_EXTENDED;
Flashback complete.
Elapsed: 00:00:00.98
[ol7ora12rf1]<SYS@ORA12RF1>$ alter database open;
alter database
open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for
database open
Elapsed: 00:00:00.03
[ol7ora12rf1]<SYS@ORA12RF1>$ alter database open resetlogs;
alter database
open resetlogs
*
ERROR at line
1:
ORA-00603: ORACLE server session terminated by fatal
error
ORA-01092: ORACLE instance terminated.
Disconnection forced
ORA-14694: database must in UPGRADE
mode to begin MAX_STRING_SIZE migration
Process ID:
4885
Session ID: 1640 Serial number:
44098
Elapsed: 00:00:05.01
--> max_string_size 파라미터는 현재 extended라서 에러가 발생함
[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
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 13 14:51:39 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
[ol7ora12rf1]<SYS@ORA12RF1>$ startup mount
ORACLE instance
started.
Total System Global Area 5033164800 bytes
Fixed
Size
8631048 bytes
Variable
Size
1476398328 bytes
Database
Buffers 3539992576 bytes
Redo
Buffers
8142848 bytes
Database mounted.
[ol7ora12rf1]<SYS@ORA12RF1>$ alter system set max_string_size=STANDARD scope=spfile
sid='*';
System altered.
Elapsed: 00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ shutdown immediate;
ORA-01109:
database not open
Database dismounted.
ORACLE
instance shut down.
[ol7ora12rf1]<SYS@ORA12RF1>$ startup mount
ORACLE instance
started.
[ol7ora12rf1]<SYS@ORA12RF1>$ flashback database to restore point BEFORE_MAX_STRING_SIZE_EXTENDED;
Flashback complete.
Elapsed: 00:00:01.46
Total System Global Area
5033164800 bytes
Fixed
Size
8631048 bytes
Variable
Size
1476398328 bytes
Database
Buffers 3539992576 bytes
Redo
Buffers
8142848 bytes
Database mounted.
[ol7ora12rf1]<SYS@ORA12RF1>$ alter database open resetlogs;
Database altered.
Elapsed: 00:00:05.02
[ol7ora12rf1]<SYS@ORA12RF1>$ select current_scn from v$database;
CURRENT_SCN
-----------
7431326
1 row selected.
Elapsed: 00:00:00.00
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
st
alias st='rlwrap sqlplus tuner/oracle'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
st
SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 13 14:56:50 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Wed Nov 12 2025 14:55:30 +09:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
[ol7ora12rf1]<TUNER@ORA12RF1>$
[ol7ora12rf1]<TUNER@ORA12RF1>$ create table tb_test_varchar2_10000 (data_col
varchar2(10000));
create table tb_test_varchar2_10000 (data_col
varchar2(10000))
*
ERROR at line 1:
ORA-00910:
specified length too long for its datatype
Elapsed: 00:00:00.00
[ol7ora12rf1]<TUNER@ORA12RF1>$ show parameter max_string_size
NAME
TYPE
VALUE
------------------------------------
--------------------------------- ------------------------------
max_string_size
string
STANDARD
--> flashback database 로 복구
완료함
-->결론 : flashback database
로 복구 가능한것을 알수 있음
[ol7ora12rf1]<TUNER@ORA12RF1>$ quit
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ srvctl start
instance -database ora12rf -instance ORA12RF2
--플래시백 데이터베이스 설정
해제
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ srvctl stop
database -db 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 Nov 13 16:13:24 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
[ol7ora12rf1]<SYS@ORA12RF1>$ startup mount
ORACLE instance
started.
Total System
Global Area 5033164800 bytes
Fixed
Size
8631048 bytes
Variable
Size
1476398328 bytes
Database
Buffers 3539992576 bytes
Redo
Buffers
8142848 bytes
Database mounted.
[ol7ora12rf1]<SYS@ORA12RF1>$ select flashback_on from v$database;
FLASHBACK_ON
------------------------------------------------------
YES
1 row selected.
Elapsed: 00:00:00.01
col name for
a10
col GUARANTEE for a50
SELECT
name
,
guarantee_flashback_database
FROM
v$restore_point
;
NAME GUARANTEE
------- ----------------------------------
YES
BEFORE_MAX_STRING_SIZE_EXTENDED
1 row selected.
Elapsed: 00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ drop restore point BEFORE_MAX_STRING_SIZE_EXTENDED;
Restore point dropped.
[ol7ora12rf1]<SYS@ORA12RF1>$ ALTER DATABASE FLASHBACK OFF;
Database
altered.
Elapsed: 00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ ALTER SYSTEM RESET db_recovery_file_dest SCOPE=SPFILE
SID='*';
System
altered.
Elapsed: 00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ ALTER SYSTEM RESET db_recovery_file_dest_size SCOPE=SPFILE
SID='*';
System
altered.
Elapsed: 00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ alter system set db_flashback_retention_target=1440
scope=spfile sid='*';
System
altered.
Elapsed: 00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ shutdown immediate;
ORA-01109:
database not open
Database dismounted.
ORACLE instance shut down.
[ol7ora12rf1]<SYS@ORA12RF1>$ startup
ORACLE instance started.
Total System Global Area 5033164800 bytes
Fixed
Size
8631048 bytes
Variable
Size
1476398328 bytes
Database
Buffers 3539992576 bytes
Redo
Buffers
8142848 bytes
Database mounted.
Database opened.
[ol7ora12rf1]<SYS@ORA12RF1>$ SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------------------------------------------
NO
1 row
selected.
Elapsed: 00:00:00.00
[ol7ora12rf1]<SYS@ORA12RF1>$ show parameter db_recovery_file_dest
NAME
TYPE
VALUE
------------------------------------
--------------------------------- ----------------------------- -
db_recovery_file_dest
string
(NULL)
db_recovery_file_dest_size
big
integer
0
[ol7ora12rf1]<SYS@ORA12RF1>$ show parameter db_flashback_retention_target
NAME
TYPE
VALUE
------------------------------------
--------------------------------- ----------------------------- -
db_flashback_retention_target
integer
1440
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ srvctl start instance -database ora12rf -instance ORA12RF2
7. DB 백업본으로 복구
--> 만약 어떠한 이유로 flashback
database가 실패하면 백업본으로 복구해야함
--> 해당
상황을 대비하여 DB 백업본으로 복구를 해봄
--> 아래의 작업과정은 6번에서 7번으로
이어지는게 아님 (과거 연습했던것 정리한 것임)
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ srvctl stop database -db ora12rf
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle/backup]$ ls -l
/home/oracle/backup
total 6556420
-rw-r-----. 1
oracle asmadmin 20103168 Nov 10 20:53 c-4288610047-20251110-01
-rw-r--r--. 1 oracle
asmadmin 2347 Nov 10 20:46
initORA12RF1.ora
-rw-r-----. 1 oracle asmadmin
6644408320 Nov 10 20:53 ORA12RF_2h48f68n_1_1.bkp
-rw-r-----. 1 oracle asmadmin 1146880 Nov
10 20:53 ORA12RF_2i48f6hu_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 8032256 Nov 10 20:53
ORA12RF_ARCHIVE_20251110_2k48f6iv_s84_p1
-rw-r-----. 1
oracle asmadmin 20070400 Nov 10 20:53
ORA12RF_CTL_2j48f6i0_1_1_20251110
-rw-r-----. 1 oracle
asmadmin 20004864 Nov 11 15:24 snapcf_CA.f
--백업 받았던 pfile 로 nomount 모드로
기동
[ol7ora12rf1]<SYS@ORA12RF1>$ startup nomount
pfile='/home/oracle/backup/initORA12RF1.ora'
--컨트롤 파일 복구
RMAN> restore
controlfile from
'/home/oracle/backup/c-4288610047-20251110-01';
Starting restore
at 2025-11-11 19:15:56
using channel
ORA_DISK_1
channel
ORA_DISK_1: restoring control file
channel ORA_DISK_1:
restore complete, elapsed time: 00:00:01
output file
name=+DATA1/ORA12RF/CONTROLFILE/current.261.1213483391
output file
name=+FRA1/ORA12RF/CONTROLFILE/current.256.1213483391
Finished restore at 2025-11-11 19:15:57
--mount 시켜줌
RMAN> alter
database mount;
Statement
processed
released channel:
ORA_DISK_1
--INCARNATION
확인
RMAN> LIST
INCARNATION;
List of Database Incarnations
DB
Key Inc Key DB Name DB
ID
STATUS Reset SCN Reset Time
------- -------
-------- ---------------- --- ---------- ----------
1
1 ORA12RF
4288610047 PARENT
1 2017-01-26 13:52:29
2
2 ORA12RF
4288610047 CURRENT 1408558
2025-10-02 22:43:14
--restore 작업
RMAN> restore
database;
Starting restore
at 2025-11-11 19:17:59
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1641 instance=ORA12RF1 device
type=DISK
channel
ORA_DISK_1: starting datafile backup set restore
channel
ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to
+DATA1/ORA12RF/DATAFILE/system.257.1213483305
channel
ORA_DISK_1: restoring datafile 00002 to
+DATA1/ORA12RF/DATAFILE/tuner_data1.272.1216160599
channel ORA_DISK_1: restoring datafile 00003 to
+DATA1/ORA12RF/DATAFILE/sysaux.258.1213483329
channel
ORA_DISK_1: restoring datafile 00004 to
+DATA1/ORA12RF/DATAFILE/undotbs1.259.1213483345
channel
ORA_DISK_1: restoring datafile 00005 to
+DATA1/ORA12RF/DATAFILE/undotbs2.266.1213483407
channel
ORA_DISK_1: restoring datafile 00007 to
+DATA1/ORA12RF/DATAFILE/users.260.1213483345
channel
ORA_DISK_1: restoring datafile 00008 to
+DATA1/ORA12RF/DATAFILE/tuner_idx1.273.1216160601
channel ORA_DISK_1: reading from backup piece
/home/oracle/backup/ORA12RF_2h48f68n_1_1.bkp
channel
ORA_DISK_1: piece handle=/home/oracle/backup/ORA12RF_2h48f68n_1_1.bkp
tag=TAG20251110T204823
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:08:15
Finished restore at 2025-11-11
19:26:14
RMAN> LIST INCARNATION;
List of Database Incarnations
DB
Key Inc Key DB Name DB
ID
STATUS Reset SCN Reset Time
------- -------
-------- ---------------- --- ---------- ----------
1
1 ORA12RF
4288610047 PARENT
1 2017-01-26 13:52:29
2
2 ORA12RF
4288610047 CURRENT 1408558
2025-10-02 22:43:14
[+ASM1:grid@ol7ora12rf1][/home/grid]$ asmcmd ls -sl
+DATA1/ORA12RF/DATAFILE
Type Redund
Striped
Time
Sys Block_Size
Blocks
Bytes Space Name
DATAFILE UNPROT COARSE NOV 11
19:00:00 Y
8192 345345 2829066240 2835349504
SYSAUX.258.1213483329
DATAFILE UNPROT
COARSE NOV 11 19:00:00
Y 8192
106585 873144320 880803840
SYSTEM.257.1213483305
DATAFILE UNPROT
COARSE NOV 11 19:00:00
Y 8192 2146817
17586724864 17595105280 TUNER_DATA1.272.1216160599
DATAFILE UNPROT COARSE NOV 11
19:00:00 Y
8192 1378433 11292123136 11299454976
TUNER_IDX1.273.1216160601
DATAFILE UNPROT
COARSE NOV 11 19:00:00
Y 8192
318721 2610962432 2617245696
UNDOTBS1.259.1213483345
DATAFILE UNPROT
COARSE NOV 11 19:00:00
Y 8192
12801 104865792 109051904
UNDOTBS2.266.1213483407
DATAFILE UNPROT
COARSE NOV 11 19:00:00
Y
8192 641
5251072 8388608
USERS.260.1213483345
RMAN> list backup;
List of Backup
Sets
===================
BS Key Type LV
Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
76
Full 6.19G
DISK 00:04:48
2025-11-10 20:53:11
BP Key:
76 Status: AVAILABLE Compressed: YES Tag:
TAG20251110T204823
Piece Name:
/home/oracle/backup/ORA12RF_2h48f68n_1_1.bkp
List
of Datafiles in backup set 76
File LV Type Ckp
SCN Ckp
Time Abs Fuz
SCN Sparse Name
---- -- ---- ----------
------------------- ----------- ------ ----
1 Full 6829219 2025-11-10
20:48:23
NO +DATA1/ORA12RF/DATAFILE/system.257.1213483305
2 Full
6829219 2025-11-10
20:48:23
NO +DATA1/ORA12RF/DATAFILE/tuner_data1.272.1216160599
3 Full
6829219 2025-11-10
20:48:23
NO +DATA1/ORA12RF/DATAFILE/sysaux.258.1213483329
4 Full
6829219 2025-11-10
20:48:23
NO +DATA1/ORA12RF/DATAFILE/undotbs1.259.1213483345
5 Full
6829219 2025-11-10
20:48:23
NO +DATA1/ORA12RF/DATAFILE/undotbs2.266.1213483407
7 Full
6829219 2025-11-10
20:48:23
NO +DATA1/ORA12RF/DATAFILE/users.260.1213483345
8 Full
6829219 2025-11-10
20:48:23
NO
+DATA1/ORA12RF/DATAFILE/tuner_idx1.273.1216160601
BS Key Type
LV Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
77
Full 1.08M
DISK 00:00:01
2025-11-10 20:53:19
BP Key:
77 Status: AVAILABLE Compressed: YES Tag:
TAG20251110T204823
Piece Name:
/home/oracle/backup/ORA12RF_2i48f6hu_1_1.bkp
SPFILE Included: Modification time: 2025-11-10 20:42:38
SPFILE db_unique_name: ORA12RF
Control File Included: Ckp SCN:
6831421 Ckp time: 2025-11-10
20:53:18
BS Key Type
LV Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
78
Full 19.13M
DISK 00:00:01
2025-11-10 20:53:21
BP Key:
78 Status: AVAILABLE Compressed: NO Tag:
TAG20251110T205320
Piece Name:
/home/oracle/backup/ORA12RF_CTL_2j48f6i0_1_1_20251110
Control File Included: Ckp SCN:
6831437 Ckp time: 2025-11-10
20:53:20
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
-------------------
79
7.66M
DISK 00:00:00
2025-11-10 20:53:51
BP Key:
79 Status: AVAILABLE Compressed: NO Tag:
TAG20251110T205351
Piece Name:
/home/oracle/backup/ORA12RF_ARCHIVE_20251110_2k48f6iv_s84_p1
List of Archived
Logs in backup set 79
Thrd
Seq Low SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
1 177 6829036
2025-11-10 20:48:16 6831579 2025-11-10 20:53:49
2 89
6829045 2025-11-10 20:48:18 6831590
2025-11-10 20:53:51
--> 현재 복구한 컨트롤 파일은
위의 아카이브 로그가 백업 된 이후에 자동으로 생성되었던 컨트롤 파일임
--> 그러므로 해당
아카이브 백업본을 인식할 수 있는 것임 (catalog start with 명령으로 등록해줄 필요 없는 상황)
--> 해당 아카이브 기준 가장 최신까지 복구할수
있는 scn을 판단하는 방법
--> thread 1과 thread
2 기준 next scn이 더 작은 것은 thread 1 임 scn 6831579 인 상황
--> 6831579-1=6831578 scn까지 복구를 할 수 있는 상황임
--> 목표 scn은 6831578 인 상황임
--필수는 아님 어차피 최신의 INCARNATION
번호인 2번이 복구 대상인 상황
RMAN> reset database to incarnation
2;
--이상황에서 grid os user로 접속해서 asm
disk 혹은 file system의 아카이브 로그가 저장된 경로에 가서
--현재 존재하는 아카이브를 모두 mv 혹은 cp 시킨 후 rm
시켜야함
--그 이유는 아카이브 로그 백업본을 restore
(아카이브 로그 파일) 시킨 후 그 아카이브 로그 파일로 recover 시켜야 하는데
--그 파일이
이미 아카이브 저장 경로에 있으면 백업본이 아닌 엉뚱한 파일로 복구할 수 있음
--백업 받은 후
flashback 설정하고 resetlos open 한 상황이기 때문에 잘못된 아카이브를 rman이 참조할 수 있음
--그러므로 아래와 같이 cp를 하고 난 후 삭제시킨 상태에서 rman recover를 수행해야함
cp
thread_1_seq_1.334.1216855589 /tmp
cp thread_1_seq_178.302.1216849209 /tmp
cp thread_1_seq_179.307.1216850321 /tmp
cp thread_1_seq_180.311.1216852125 /tmp
cp thread_1_seq_180.319.1216855517 /tmp
cp thread_1_seq_181.315.1216854809 /tmp
cp thread_1_seq_181.317.1216855517 /tmp
cp thread_1_seq_182.316.1216855445 /tmp
cp thread_1_seq_182.318.1216855517 /tmp
cp thread_1_seq_2.335.1216855993
/tmp
cp
thread_1_seq_3.333.1216856169 /tmp
cp thread_1_seq_4.332.1216856365
/tmp
cp
thread_2_seq_1.330.1216856365 /tmp
cp thread_2_seq_90.304.1216850319 /tmp
cp thread_2_seq_91.305.1216850319 /tmp
cp thread_2_seq_92.306.1216850319 /tmp
cp thread_2_seq_93.312.1216852169 /tmp
cp thread_2_seq_94.313.1216852169 /tmp
cp thread_2_seq_94.320.1216855517 /tmp
cp thread_2_seq_95.314.1216854809 /tmp
cp thread_2_seq_95.321.1216855517 /tmp
cp thread_2_seq_96.336.1216855519 /tmp
cp thread_1_seq_177.324.1216916849 /tmp
cp thread_1_seq_5.337.1216912589
/tmp
cp
thread_1_seq_5.341.1216913061 /tmp
cp thread_1_seq_6.338.1216912589
/tmp
cp
thread_1_seq_6.342.1216913061 /tmp
cp thread_1_seq_7.339.1216912885
/tmp
cp
thread_1_seq_7.340.1216913061 /tmp
cp thread_2_seq_1.323.1216913065
/tmp
cp
thread_2_seq_1.326.1216916863 /tmp
cp thread_2_seq_1.328.1216928487
/tmp
cp
thread_2_seq_1.343.1216913061 /tmp
cp thread_2_seq_2.327.1216926851
/tmp
cp
thread_2_seq_2.329.1216913061 /tmp
cp thread_2_seq_2.331.1216912589
/tmp
cp
thread_2_seq_3.322.1216913061 /tmp
cp thread_2_seq_89.325.1216916849 /tmp
--해당 아카이브 로그를 삭제함
run {
set until scn 6831578;
recover
database;
}
RMAN> alter database open resetlogs;
[ol7ora12rf1]<SYS@ORA12RF1>$ show parameter db_recovery_file;
NAME
TYPE
VALUE
------------------------------------
--------------------------------- ------------------------------
db_recovery_file_dest
string
(NULL)
db_recovery_file_dest_size
big
integer
0
[ol7ora12rf1]<SYS@ORA12RF1>$ show parameter flashback;
NAME
TYPE
VALUE
------------------------------------
--------------------------------- ------------------------------
db_flashback_retention_target
integer
1440
[ol7ora12rf1]<SYS@ORA12RF1>$ create spfile='+DATA1' from pfile='/home/oracle/backup/initORA12RF1.ora';
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle/backup]$ srvctl
config database -db ora12rf
Database unique name: ORA12RF
Database name: ORA12RF
Oracle home:
/u01/app/oracle/product/12c/db_1
Oracle user: oracle
Spfile:
+DATA1/ORA12RF/PARAMETERFILE/spfile.276.1216917171
Password file:
+DATA1/ORA12RF/PASSWORD/pwdora12rf.256.1213483291
Domain:
Start options: open
Stop options: immediate
Database
role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
FRA1,DATA1
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop
concurrency:
OSDBA group: dba
OSOPER group: oper
Database
instances: ORA12RF1,ORA12RF2
Configured nodes:
ol7ora12rf1,ol7ora12rf2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network
number for database services:
Database is administrator
managed
[ol7ora12rf1]<SYS@ORA12RF1>$ shutdown immediate;
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle/backup]$ srvctl start database -db ora12rf
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle/backup]$
csrt
ora.ora12rf.db
1 ONLINE
ONLINE
ol7ora12rf1
Open,HOME=/u01/app/o
racle/product/12c/db
_1,STABLE
2 ONLINE
ONLINE
ol7ora12rf2
Open,HOME=/u01/app/o
racle/product/12c/db
_1,STABLE
[ol7ora12rf1]<SYS@ORA12RF1>$ show parameter max_string_size
NAME
TYPE
VALUE
------------------------------------
--------------------------------- ------------------------------
max_string_size
string
STANDARD