[제목]
[2025-11-14] 12cR1의 신기능인 MAX_STRING_SIZE를 extended로 설정 후 4000 bytes 초과 칼럼을 생성한 다음에 table/index nologging 설정 시 redo log 최소화가 되는지 테스트
[테스트
개요]
MAX_STRING_SIZE = EXTENDED 로 변경 후 VARCHAR2 컬럼이 4000 bytes 초과(예: 10000
bytes)일 때 내부 저장 구조가 LOB 세그먼트로 생성되는지 확인하였다.
TABLE/INDEX 를 NOLOGGING + APPEND 로
설정해도 4000 bytes 초과 VARCHAR2 컬럼은 REDO 최소화가 전혀 되지 않음을 실제 REDO SIZE 측정으로
검증하였다.
AskTOM 공식 답변과 동일하게, 확장
VARCHAR2(>4000)는 내부 LOB 이지만 사용자가 NOLOGGING 속성을 변경할 수 없으므로 REDO 최소화는 불가능하다는
결론을 확인하였다.
[테스트
환경]
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 파라미터 상태 확인
2. DB 풀 백업
3. MAX_STRING_SIZE 파라미터를 extended로 변경
4. varcahr2 4000 바이트 칼럼을 가지는 테이블 생성 및 table nologging 시 redo log 최소화 테스트
5. varcahr2 4000 바이트 초과 칼럼을 가지는 테이블 생성 및 table nologging 시 redo log 최소화 테스트
6. DB 백업본으로 복구 (optional)
[내용]
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
--> 현재는 varchar2 칼럼이 4000 바이트를 초과한 칼럼을 생성할수
없음
2. DB 풀
백업
--max_string_size 파라미터를
standard 에서 extended로 설정 시 다시 되돌아갈 수 없으므로
--테스트 전 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"'
[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
--파라미터 파일 pfile로
백업
[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/s
pfile.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
------- ---- -- ---------- ----------- ------------
-------------------
104 Full
6.23G
DISK 00:04:50
2025-11-13 22:03:53
BP Key:
104 Status: AVAILABLE Compressed: YES Tag:
TAG20251113T215903
Piece Name:
/home/oracle/backup/ORA12RF_3g48n7h7_1_1.bkp
List of Datafiles in
backup set 104
File LV Type Ckp SCN Ckp
Time Abs Fuz
SCN Sparse Name
---- -- ---- ---------- -------------------
----------- ------ ----
1 Full
7667852 2025-11-13
21:59:04
NO +DATA1/ORA12RF/DATAFILE/system.257.1213483305
2 Full 7667852 2025-11-13
21:59:04
NO
+DATA1/ORA12RF/DATAFILE/tuner_data1.272.1216160599
3 Full 7667852 2025-11-13
21:59:04
NO +DATA1/ORA12RF/DATAFILE/sysaux.258.1213483329
4 Full 7667852 2025-11-13
21:59:04
NO +DATA1/ORA12RF/DATAFILE/undotbs1.259.1213483345
5 Full 7667852 2025-11-13
21:59:04
NO +DATA1/ORA12RF/DATAFILE/undotbs2.266.1213483407
7 Full 7667852 2025-11-13
21:59:04
NO +DATA1/ORA12RF/DATAFILE/users.260.1213483345
8 Full 7667852 2025-11-13
21:59:04
NO
+DATA1/ORA12RF/DATAFILE/tuner_idx1.273.1216160601
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
105 Full
1.09M
DISK 00:00:01
2025-11-13 22:04:00
BP Key:
105 Status: AVAILABLE Compressed: YES Tag:
TAG20251113T215903
Piece Name:
/home/oracle/backup/ORA12RF_3h48n7qf_1_1.bkp
SPFILE Included:
Modification time: 2025-11-13 21:53:03
SPFILE db_unique_name:
ORA12RF
Control File Included: Ckp SCN:
7671036 Ckp time: 2025-11-13
22:03:59
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
106 Full
19.13M DISK
00:00:01 2025-11-13
22:04:02
BP Key: 106
Status: AVAILABLE Compressed: NO Tag:
TAG20251113T220401
Piece Name:
/home/oracle/backup/ORA12RF_CTL_3i48n7qh_1_1_20251113
Control File
Included: Ckp SCN: 7671052 Ckp time: 2025-11-13
22:04:01
BS
Key Size Device Type Elapsed Time
Completion Time
------- ---------- ----------- ------------
-------------------
107
5.79M
DISK 00:00:00
2025-11-13 22:04:05
BP Key:
107 Status: AVAILABLE Compressed: NO Tag:
TAG20251113T220405
Piece Name:
/home/oracle/backup/ORA12RF_ARCHIVE_20251113_3j48n7ql_s115_p1
List of Archived Logs in backup set
107
Thrd Seq Low SCN Low
Time Next
SCN Next Time
---- ------- ---------- -------------------
---------- ---------
1
5 7667743 2025-11-13
21:59:00 7671129 2025-11-13 22:04:05
2 8
7667752 2025-11-13 21:59:02 7671124
2025-11-13 22:04:05
--> scn 기준
7671124-1=7671123 까지 복구가 가능한 상황임
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
108 Full
19.22M DISK
00:00:00 2025-11-13
22:04:06
BP Key: 108
Status: AVAILABLE Compressed: NO Tag:
TAG20251113T220406
Piece Name:
/home/oracle/backup/c-4288610047-20251113-04
SPFILE Included:
Modification time: 2025-11-13 21:53:03
SPFILE db_unique_name:
ORA12RF
Control File Included: Ckp SCN:
7671158 Ckp time: 2025-11-13
22:04:06
-->
/home/oracle/backup/c-4288610047-20251113-04 컨트롤 파일로 복구 시
--> 백업 받은
/home/oracle/backup/ORA12RF_ARCHIVE_20251113_3j48n7ql_s115_p1 아카이브를 인식할 수
있음
3. 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
--> compatible 파라미터가 12.1 이상이어야함
(MAX_STRING_SIZE 파라미터를 extended로 변경하기 위해서 필수 조건임)
[ol7ora12rf1]<SYS@ORA12RF1>$ show parameter cluster_database
NAME
TYPE
VALUE
------------------------------------ ---------------------------------
------------------------------
cluster_database
boolean
TRUE
cluster_database_instances
integer
2
--rac 환경인 경우 cluster_database
파라미터 false 로 설정한 후 한 노드로만 기동한 상태에서 설정해야함
[ol7ora12rf1]<SYS@ORA12RF1>$ alter system set
cluster_database=FALSE scope=spfile sid='*';
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>$
[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
--스크립트 실행 extended로
가자
[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
--invalid한 객체가 있는지
확인하자.
[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));
--> extended 설정이 제대로
된 것을 확인함
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
4. varcahr2 4000 바이트 칼럼을 가지는 테이블 생성 및 table nologging 시
redo log 최소화 테스트
[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 Fri Nov 14 10:33:54 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Fri Nov 14 2025 10:33:37 +09:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
[ol7ora12rf1]<TUNER@ORA12RF1>$ host vi st_redo.sql
[ol7ora12rf1]<TUNER@ORA12RF1>$ host cat st_redo.sql
SET LINESIZE
150
SET PAGESIZE 50
SET VERIFY
OFF
SET FEEDBACK ON
SET NUMWIDTH
12
COLUMN inst_id HEADING
'INST' FORMAT 999
COLUMN name HEADING
'STAT_NAME' FORMAT A25
COLUMN
s.name HEADING 'INSTANCE' FORMAT A15
COLUMN redo_mb HEADING 'REDO SIZE(MB)' FORMAT
999,999,990.00
SELECT
s.inst_id
, n.name
, s.name
, round(s.value/1024/1024,
2) as redo_mb
FROM gv$sysstat s
JOIN v$statname n
ON n.statistic# = s.statistic#
WHERE n.name = 'redo size'
ORDER BY s.inst_id;
DROP TABLE
TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_4000_TEST PURGE;
CREATE TABLE
TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_4000_TEST
(
TEST_NO NUMBER(15)
, DATA_GB VARCHAR2(9)
, CONTS
VARCHAR2(4000)
, CONSTRAINT
PK_TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_4000_TEST PRIMARY KEY (TEST_NO)
) LOGGING
;
[ol7ora12rf1]<TUNER@ORA12RF1>$ alter table TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_4000_TEST logging;
Table altered.
Elapsed: 00:00:00.06
[ol7ora12rf1]<TUNER@ORA12RF1>$ alter index tuner.PK_TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_4000_TEST logging;
Index altered.
Elapsed: 00:00:00.02
[ol7ora12rf1]<TUNER@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- -------------------------
------------------------- ---------------
1
redo
size
redo
size
840.12
2 redo
size
redo
size
249.06
2 rows selected.
Elapsed: 00:00:00.18
--당연히 redo log 가 정상적으로
남아야하는 상황임 (append 힌트 써봐야 소용없음, redo log는 다 남음, table/index logging 이기
때문에)
INSERT /*+ APPEND
*/ INTO TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_4000_TEST
WITH TMP_1 AS
(
SELECT /*+ MATERIALIZE */
NVL(MAX(TEST_NO), 0)+1
AS TEST_NO FROM TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_4000_TEST
)
SELECT (SELECT L.TEST_NO FROM
TMP_1 L)+ROWNUM AS TEST_NO
,
'LOGGING' AS DATA_GB
,
DBMS_RANDOM.STRING('U', 4000) AS CONTS
FROM DUAL
CONNECT BY LEVEL <= 10000
;
*************************[Explain Plan Time: 2025/11/14
10:42:46]*************************
Execution Plan
-----------------------------------------------------------
0 INSERT
STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1)
1 0 TEMP TABLE
TRANSFORMATION
2
1 LOAD AS SELECT OF 'SYS_TEMP_0FD9D6664_751C09'
3
2 SORT (AGGREGATE) (Card=1 Bytes=13)
4
3 INDEX (FULL SCAN (MIN/MAX)) OF
'PK_TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_4000_TEST' (INDEX (UNIQUE)) (Cost=2
Card=1 Bytes=13)
5
1 LOAD AS SELECT OF
'TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_4000_TEST'
6
5 VIEW (Cost=2 Card=1 Bytes=13)
7
6 TABLE ACCESS (FULL) OF
'SYS.SYS_TEMP_0FD9D6664_751C09' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=13)
8
5 COUNT
9 8 CONNECT BY
(WITHOUT FILTERING)
10
9 FAST DUAL (Cost=2
Card=1)
-----------------------------------------------------------
Predicate
information (identified by operation id):
-----------------------------------------------------------
9 - filter(LEVEL<=10000)
-----------------------------------------------------------
COMMIT;
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- -------------------------
------------------------- ---------------
1
redo
size
redo
size
922.90
2 redo
size
redo
size
252.56
2 rows selected.
Elapsed: 00:00:00.01
--> redo log가 정상적으로 생성된 것을 확인 (append 힌트를
기재했지만 소용없음)
2 rows selected.
Elapsed: 00:00:00.02
--이번에는 table nologging 상태에서 테스트
[ol7ora12rf1]<TUNER@ORA12RF1>$ alter table TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_4000_TEST nologging;
Table altered.
Elapsed: 00:00:00.06
[ol7ora12rf1]<TUNER@ORA12RF1>$ alter index tuner.PK_TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_4000_TEST nologging;
Index altered.
Elapsed: 00:00:00.02
[ol7ora12rf1]<TUNER@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- -------------------------
------------------------- ---------------
1
redo
size
redo
size
923.44
2 redo
size
redo
size
252.58
2 rows selected.
Elapsed: 00:00:00.01
--table/index nologging +
append 조합이니 redo log 최소화됨
INSERT /*+ APPEND */ INTO
TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_4000_TEST
WITH TMP_1 AS
(
SELECT /*+ MATERIALIZE */
NVL(MAX(TEST_NO), 0)+1
AS TEST_NO FROM TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_4000_TEST
)
SELECT (SELECT L.TEST_NO FROM
TMP_1 L)+ROWNUM AS TEST_NO
,
'NOLOGGING' AS DATA_GB
,
DBMS_RANDOM.STRING('U', 4000) AS CONTS
FROM DUAL
CONNECT BY LEVEL <= 10000
;
*************************[Explain Plan Time: 2025/11/14
10:45:20]*************************
Execution Plan
-----------------------------------------------------------
0 INSERT
STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1)
1 0 TEMP TABLE
TRANSFORMATION
2
1 LOAD AS SELECT OF 'SYS_TEMP_0FD9D666A_751C09'
3
2 SORT (AGGREGATE) (Card=1 Bytes=13)
4
3 INDEX (FULL SCAN (MIN/MAX)) OF
'PK_TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_4000_TEST' (INDEX (UNIQUE)) (Cost=2
Card=1 Bytes=13)
5
1 LOAD AS SELECT OF
'TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_4000_TEST'
6
5 VIEW (Cost=2 Card=1 Bytes=13)
7
6 TABLE ACCESS (FULL) OF
'SYS.SYS_TEMP_0FD9D666A_751C09' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=13)
8
5 COUNT
9 8 CONNECT BY
(WITHOUT FILTERING)
10
9 FAST DUAL (Cost=2
Card=1)
-----------------------------------------------------------
Predicate
information (identified by operation id):
-----------------------------------------------------------
9 - filter(LEVEL<=10000)
-----------------------------------------------------------
COMMIT;
[ol7ora12rf1]<TUNER@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- -------------------------
------------------------- ---------------
1
redo
size
redo
size
924.12
2 redo
size
redo
size
252.64
--> redo log 생성이 최소화 된 것을
확인함
--> 이것이 바로 정상적인 상황임
--> table/index nologging+append 조합이면 redo log 최소화가 잘
먹어야 세상이 아름다움
2 rows selected.
Elapsed: 00:00:00.01
5. varcahr2 4000 바이트 초과 칼럼을 가지는 테이블 생성 및 table nologging 시
redo log 최소화 테스트
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle/oracle_scripts]$
alias st
alias st='rlwrap sqlplus
tuner/oracle'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle/oracle_scripts]$
st
SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 13 22:51:24 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Thu Nov 13 2025 22:46:28 +09:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
[ol7ora12rf1]<TUNER@ORA12RF1>$
DROP TABLE TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST PURGE;
CREATE TABLE
TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST
(
TEST_NO NUMBER(15)
, DATA_GB VARCHAR2(9)
, CONTS
VARCHAR2(10000)
, CONSTRAINT
PK_TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST PRIMARY KEY (TEST_NO)
) LOGGING
;
[ol7ora12rf1]<TUNER@ORA12RF1>$ alter table TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST logging;
Table altered.
Elapsed: 00:00:00.06
[ol7ora12rf1]<TUNER@ORA12RF1>$ alter index tuner.PK_TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST logging;
Index altered.
Elapsed: 00:00:00.02
[ol7ora12rf1]<TUNER@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- -------------------------
------------------------- ---------------
1
redo
size
redo
size
925.48
2 redo
size
redo
size
253.62
--당연히 redo log가 정상적으로 남는
상태임
INSERT /*+ APPEND
*/ INTO TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST
WITH TMP_1 AS
(
SELECT /*+ MATERIALIZE */
NVL(MAX(TEST_NO), 0)+1
AS TEST_NO FROM TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST
)
SELECT (SELECT L.TEST_NO FROM
TMP_1 L) + ROWNUM AS TEST_NO
,
'LOGGING' AS DATA_GB
,
dbms_random.string('u', 4000)||dbms_random.string('u',
4000)||dbms_random.string('u', 2000) AS CONTS
FROM DUAL CONNECT BY LEVEL <= 10000
;
COMMIT;
select distinct
length(conts) from
TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST;
LENGTH(CONTS)
-------------
10000
1 rows
selected.
;
[ol7ora12rf1]<TUNER@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- -------------------------
------------------------- ---------------
1
redo
size
redo
size
1,049.22
2 redo
size
redo
size
255.57
--> redo log가 정상적으로
생성됨
--> table/index logging 면
append 힌트써도 당연히 redo log 잘 남음
2 rows selected.
Elapsed: 00:00:00.01
--이제 table 및 index
nologging 설정 한 후 테스트
[ol7ora12rf1]<TUNER@ORA12RF1>$ alter table
TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST
nologging;
Table altered.
Elapsed: 00:00:00.06
[ol7ora12rf1]<TUNER@ORA12RF1>$ alter index tuner.PK_TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST nologging;
Index altered.
Elapsed: 00:00:00.02
[ol7ora12rf1]<TUNER@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- -------------------------
------------------------- ---------------
1
redo
size
redo
size
1,049.30
2 redo
size
redo
size
255.64
2 rows selected.
Elapsed: 00:00:00.01
INSERT /*+ APPEND */ INTO
TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST
WITH TMP_1 AS
(
SELECT /*+ MATERIALIZE */
NVL(MAX(TEST_NO), 0)+1
AS TEST_NO FROM TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST
)
SELECT (SELECT L.TEST_NO FROM
TMP_1 L) + ROWNUM AS TEST_NO
,
'NOLOGGING' AS DATA_GB
,
dbms_random.string('u', 4000)||dbms_random.string('u',
4000)||dbms_random.string('u', 2000) AS CONTS
FROM DUAL CONNECT BY LEVEL <= 10000
;
COMMIT;
select distinct
length(conts) from TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST;
LENGTH(CONTS)
-------------
10000
1 row selected.
Elapsed: 00:00:00.60
[ol7ora12rf1]<TUNER@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- -------------------------
------------------------- ---------------
1
redo
size
redo
size
1,167.38
2 redo
size
redo
size
256.07
--> redo log가 최소화 되지 않음
--> nologging 설정해도 redo log 최소화 안됨 (varchar2 칼럼에 4000
바이트 초과 시 redo log 생성 최소화 불가)
--> 흑흑~ table/index
nologging+append 조합인데도!!!! redo log 최소화가 안됨!!!!!!!! (varchar2 칼럼 4000 바이트 초과
시)
--> 결국 varchar2 칼럼 4000 바이트 초과 한 칼럼에 대량의 데이터를
insert 시 아카이브 로그 공간이 full 찰수도 있는 것임 정말 주의해야함
2 rows selected.
Elapsed: 00:00:00.01
set lines 250
pages 200 trimspool on
column
gubun heading
'gubun' format
a40
column owner heading
'OWNER' format
a20
column level1 heading
'LEVEL1' format
a50
column level2 heading
'LEVEL2' format
a50
column sum_gb heading
'SIZE_GB' format
999,999,999,990.0000
SELECT *
FROM (SELECT GUBUN
, OWNER
, LEVEL1
, LEVEL2
, ROUND(SUM(BYTES)/1024/1024/1024, 4) AS SUM_GB
FROM
(SELECT 'TABLE(SEGMENT_NAME and PARTITION_NAME)' AS GUBUN
, A.OWNER AS OWNER
, A.SEGMENT_NAME AS LEVEL1
, A.PARTITION_NAME AS LEVEL2
, A.BYTES AS BYTES
FROM DBA_SEGMENTS A
WHERE A.SEGMENT_TYPE IN ('TABLE'
, 'TABLE PARTITION'
, 'TABLE SUBPARTITION')
UNION ALL
SELECT 'LOB(TABLE_NAME and INDEX_NAME)' AS GUBUN
, L.OWNER AS OWNER
, L.TABLE_NAME AS LEVEL1
, L.COLUMN_NAME AS LEVEL2
, S.BYTES AS BYTES
FROM DBA_LOBS L
, DBA_SEGMENTS S
WHERE S.SEGMENT_NAME = L.SEGMENT_NAME
AND S.OWNER = L.OWNER
AND S.SEGMENT_TYPE IN ('LOBSEGMENT'
, 'LOB PARTITION') ) A
GROUP BY
GUBUN, OWNER, LEVEL1, LEVEL2
ORDER BY 5
DESC )
WHERE LEVEL1 =
'TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST'
;
gubun
OWNER
LEVEL1
LEVEL2 SIZE_GB
---------------------------------------- -------
-------------------------------------------------- ------------------------
LOB(TABLE_NAME and
INDEX_NAME)
TUNER
TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST
CONTS
0.3439
TABLE(SEGMENT_NAME and
PARTITION_NAME) TUNER
TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST
(NULL)
0.0020
2 rows selected.
Elapsed: 00:00:00.02
SET LINESIZE 220
SET PAGESIZE
100
SET NUMWIDTH 15
COLUMN
TABLE_NAME FORMAT
A50 HEADING 'TABLE_NAME'
COLUMN COLUMN_NAME FORMAT
A20 HEADING 'COLUMN_NAME'
COLUMN LOB_SEGMENT FORMAT
A30 HEADING 'LOB_SEGMENT'
COLUMN LOB_INDEX FORMAT
A30 HEADING 'LOB_INDEX'
COLUMN
LOGGING FORMAT
A9 HEADING 'LOGGING'
COLUMN
IN_ROW FORMAT
A7 HEADING 'IN_ROW'
COLUMN TABLESPACE_NAME FORMAT
A20 HEADING 'TABLESPACE'
COLUMN SEGMENT_TYPE FORMAT
A15 HEADING 'SEGMENT_TYPE'
COLUMN
BLOCKS FORMAT 999,999,999
HEADING 'BLOCKS(8K)'
COLUMN
MB
FORMAT 999,999.99 HEADING 'SIZE(MB)'
SELECT
A.TABLE_NAME
, A.COLUMN_NAME
, A.SEGMENT_NAME AS LOB_SEGMENT
, A.INDEX_NAME AS
LOB_INDEX
, A.LOGGING
, A.IN_ROW
, B.TABLESPACE_NAME
, B.SEGMENT_TYPE
,
B.BYTES/8192 AS BLOCKS
, B.BYTES/1024/1024 AS MB
FROM DBA_LOBS A
, DBA_SEGMENTS B
WHERE A.OWNER = 'TUNER'
AND A.TABLE_NAME =
'TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST'
AND B.SEGMENT_NAME = A.SEGMENT_NAME
AND B.OWNER = A.OWNER
ORDER BY A.COLUMN_NAME;
TABLE_NAME
COLUMN_NAME
LOB_SEGMENT
LOB_INDEX
LOGGING IN_ROW
TABLESPACE
SEGMENT_TYPE BLOCKS(8K)
SIZE(MB)
--------------------------------------------------
-------------------- ------------------------------
------------------------------ --------- ------- --------------------
--------------- ------------ -----------
TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST
CONTS
SYS_LOB0000078340C00003$$
SYS_IL0000078340C00003$$
YES YES
TUNER_DATA1
LOBSEGMENT
45,072 352.13
--> conts 칼럼의 데이터 타입은 varchar2 이지만 실제로 lob
segment로 저장됨
1 row selected.
Elapsed: 00:00:00.12
[ol7ora12rf1]<TUNER@ORA12RF1>$ ALTER TABLE
TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST MODIFY LOB (CONTS)
(NOCACHE NOLOGGING);
ALTER TABLE
TUNER.TB_MAX_STRING_SIZE_EXTENDED_VARCHAR2_10000_TEST MODIFY LOB (CONTS)
(NOCACHE NOLOGGING)
*
ERROR at line 1:
ORA-00904:
"CONTS": invalid identifier
--> 해당 설정 불가능 , 저장은 lob 세그먼트로 저장되어 있는데, 칼럼은 varchar2라서
안먹네.
Elapsed: 00:00:00.00
-- 아래 url
참고해보니
https://asktom.oracle.com/ords/asktom.search?tag=modify-nologging-property-of-lob-index-and-segment-for-a-extended-data-type-varchar2-column
--> max_string_size 파라미터가 extended 인 경우 varchar2 4000
바이트 초과 칼럼 생성 시
--> redo log를 최소화할 수 있는 방법은 없다고 함 (lob
칼럼이었다면 modify lob으로 가능했을텐데 이 상황은 안됨)
6. DB 백업본으로 복구
(optional)
--> max_string_size를
standard -> extended로 변경은 가능하나, 다시 되돌아갈수는 없으니
-->
백업본으로 복구해야 다시 되돌릴 수 있음
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ srvctl stop
database -db ora12rf
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle/backup]$ ls -l
/home/oracle/backup
--백업 받았던 pfile 로 nomount 모드로
기동
[ol7ora12rf1]<SYS@ORA12RF1>$ startup nomount
pfile='/home/oracle/backup/initORA12RF1.ora'
--컨트롤 파일 복구
RMAN> restore
controlfile from '/home/oracle/backup/컨트롤파일명';
--> 가급적 아카이브 로그 백업 완료 후 생성된 autobackup된 컨트롤 파일로 복구해야
--> catalog start with 로 아카이브 백업본 인식시켜주지 않아도
됨
--mount 시켜줌
RMAN> alter
database mount;
--INCARNATION
확인
RMAN> LIST INCARNATION;
--restore
작업
RMAN> restore
database;
RMAN> list backup;
RMAN> catalog start with '아카이브로그백업본'; --필요 시
RMAN> LIST
INCARNATION;
RMAN> reset database to incarnation 2; -- 필요
시
--돌리기전 실제 아카이브 로그가 저장되는 asm디스크
경로나, 파일 시스테 경로가서
--현재 존재하는 아카이브 mv 혹은 cp후 rm 시킨 후 돌릴
것
run {
set until scn 목표scn;
recover
database;
}
RMAN> alter database open resetlogs;
[ol7ora12rf1]<SYS@ORA12RF1>$ create spfile='+DATA1' from pfile='/home/oracle/backup/initORA12RF1.ora';
[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