[oracle@host01]$ $ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Sun Dec 22 14:32:47 2019
Copyright (c) 2012, 2019, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18481316_2019_12_22_14_32_47/sqlpatch_invocation.log
Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of SQL patches:
Bundle series DBRU:
ID 191015 in the binary registry and ID 190416 in the SQL registry
Adding patches to installation queue and performing prereq checks...
Installation queue:
Nothing to roll back
The following patches will be applied:
30138470 (DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015)
Installing patches...
Patch installation complete. Total patches installed: 3
Validating logfiles...
Patch 30138470 apply: SUCCESS
logfile: (no errors)
SQL Patching tool complete on Sun Dec 22 14:33:32 2019
SQL Patching tool version 12.2.0.1.0 Production on Sun Dec 22 14:32:47 2019
Copyright (c) 2012, 2019, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18481316_2019_12_22_14_32_47/sqlpatch_invocation.log
Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of SQL patches:
Bundle series DBRU:
ID 191015 in the binary registry and ID 190416 in the SQL registry
Adding patches to installation queue and performing prereq checks...
Installation queue:
Nothing to roll back
The following patches will be applied:
30138470 (DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015)
Installing patches...
Patch installation complete. Total patches installed: 3
Validating logfiles...
Patch 30138470 apply: SUCCESS
logfile: (no errors)
SQL Patching tool complete on Sun Dec 22 14:33:32 2019
It shows 'Patch 30138470 apply: SUCCESS', but the log file name is missing and patch status shows END in view DBA_REGISTRY_SQLPATCH,
SQL> select patch_id,version,action,status,description
2 from dba_registry_sqlpatch
3 order by action_time;
PATCH_ID VERSION ACTION STATUS DESCRIPTION
---------- ----------- -------- ---------- -------------------------------------------------
29314339 12.2.0.1 APPLY SUCCESS DATABASE APR 2019 RELEASE UPDATE 12.2.0.1.190416
30138470 12.2.0.1 APPLY END
SQL>
2 from dba_registry_sqlpatch
3 order by action_time;
PATCH_ID VERSION ACTION STATUS DESCRIPTION
---------- ----------- -------- ---------- -------------------------------------------------
29314339 12.2.0.1 APPLY SUCCESS DATABASE APR 2019 RELEASE UPDATE 12.2.0.1.190416
30138470 12.2.0.1 APPLY END
SQL>
Check the sql execution log,
[oracle@host01]$ cd /u01/app/oracle/cfgtoollogs/sqlpatch/
[oracle@host01]$ ls -l
total 104
drwxr-xr-x 3 oracle oracle 256 Jul 21 16:28 29314339
drwxr-xr-x 3 oracle oracle 256 Dec 22 14:33 30138470
drwxr-xr-x 2 oracle oracle 4096 Jul 21 16:24 sqlpatch_10879194_2019_07_21_16_24_15
drwxr-xr-x 2 oracle oracle 4096 Dec 22 14:33 sqlpatch_18481316_2019_12_22_14_32_47
-rw-r--r-- 1 oracle oracle 1373 Dec 22 14:31 sqlpatch_history.txt
[oracle@host01]$ cd 30138470
[oracle@host01]$ ls -l
total 0
drwxr-xr-x 2 oracle oracle 256 Dec 22 15:10 23136421
[oracle@host01]$ cd 23136421
[oracle@host01]$ ls -l
total 440
-rw-r--r-- 1 oracle oracle 184086 Dec 22 14:33 30138470_apply_DB01_2019Dec22_14_33_31.log
[oracle@host01]$ pwd
/u01/app/oracle/cfgtoollogs/sqlpatch/30138470/23136421
[oracle@host01]$
[oracle@host01]$ grep -n 'ORA-' 30138470_apply_DB01_2019Dec22_14_33_31.log
32:ORA-01693: max # extents (505) reached in lob segment
34:ORA-06512: at "SYS.DBMS_SQLPATCH", line 639
35:ORA-06512: at "SYS.DBMS_SQLPATCH", line 605
36:ORA-06512: at line 2
65:ORA-20004: Uninitialized state in install_file
66:ORA-06512: at "SYS.DBMS_SQLPATCH", line 738
159:ORA-20004: Uninitialized state in install_file
...
7305:SQL> PROMPT IGNORABLE ERRORS: ORA-04043
7306:IGNORABLE ERRORS: ORA-04043
7313:ORA-20004: Uninitialized state in install_file
7314:ORA-06512: at "SYS.DBMS_SQLPATCH", line 738
7407:ORA-20004: Uninitialized state in install_file
7408:ORA-06512: at "SYS.DBMS_SQLPATCH", line 738
[oracle@host01]$ ls -l
total 104
drwxr-xr-x 3 oracle oracle 256 Jul 21 16:28 29314339
drwxr-xr-x 3 oracle oracle 256 Dec 22 14:33 30138470
drwxr-xr-x 2 oracle oracle 4096 Jul 21 16:24 sqlpatch_10879194_2019_07_21_16_24_15
drwxr-xr-x 2 oracle oracle 4096 Dec 22 14:33 sqlpatch_18481316_2019_12_22_14_32_47
-rw-r--r-- 1 oracle oracle 1373 Dec 22 14:31 sqlpatch_history.txt
[oracle@host01]$ cd 30138470
[oracle@host01]$ ls -l
total 0
drwxr-xr-x 2 oracle oracle 256 Dec 22 15:10 23136421
[oracle@host01]$ cd 23136421
[oracle@host01]$ ls -l
total 440
-rw-r--r-- 1 oracle oracle 184086 Dec 22 14:33 30138470_apply_DB01_2019Dec22_14_33_31.log
[oracle@host01]$ pwd
/u01/app/oracle/cfgtoollogs/sqlpatch/30138470/23136421
[oracle@host01]$
[oracle@host01]$ grep -n 'ORA-' 30138470_apply_DB01_2019Dec22_14_33_31.log
32:ORA-01693: max # extents (505) reached in lob segment
34:ORA-06512: at "SYS.DBMS_SQLPATCH", line 639
35:ORA-06512: at "SYS.DBMS_SQLPATCH", line 605
36:ORA-06512: at line 2
65:ORA-20004: Uninitialized state in install_file
66:ORA-06512: at "SYS.DBMS_SQLPATCH", line 738
159:ORA-20004: Uninitialized state in install_file
...
7305:SQL> PROMPT IGNORABLE ERRORS: ORA-04043
7306:IGNORABLE ERRORS: ORA-04043
7313:ORA-20004: Uninitialized state in install_file
7314:ORA-06512: at "SYS.DBMS_SQLPATCH", line 738
7407:ORA-20004: Uninitialized state in install_file
7408:ORA-06512: at "SYS.DBMS_SQLPATCH", line 738
ORA-01693 is found in line 32 of the log file. List the details of the errors,
[oracle@host01]$ head -37 30138470_apply _DB01_2019Dec22_14_33_31.log
SQL>
SQL> SET PAGESIZE 0
SQL> SELECT 'Starting apply for patch 30138470/23136421 on ' ||
2 SYSTIMESTAMP FROM dual;
Starting apply for patch 30138470/23136421 on 22-DEC-19 02.33.31.103477 PM -05:0
0
SQL> SET PAGESIZE 10
SQL>
SQL> BEGIN
2 dbms_sqlpatch.patch_initialize(p_patch_id => 30138470,
3 p_patch_uid => 23136421,
4 p_flags => '&flags',
5 p_description => '&description',
6 p_action => 'APPLY',
7 p_logfile => '&full_logfile',
8 p_bundle_series => '&bundle_series');
9 END;
10 /
old 4: p_flags => '&flags',
new 4: p_flags => 'NB',
old 5: p_description => '&description',
new 5: p_description => 'DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015',
old 7: p_logfile => '&full_logfile',
new 7: p_logfile => '/u01/app/oracle/cfgtoollogs/sqlpatch/30138470/23136421/30138470_apply_DB01_2019Dec22_14_33_31.log',
old 8: p_bundle_series => '&bundle_series');
new 8: p_bundle_series => 'DBRU');
BEGIN
*
ERROR at line 1:
ORA-01693: max # extents (505) reached in lob segment
SYS.SYS_LOB0000190607C00013$$
ORA-06512: at "SYS.DBMS_SQLPATCH", line 639
ORA-06512: at "SYS.DBMS_SQLPATCH", line 605
ORA-06512: at line 2
[oracle@host01]$
SQL>
SQL> SET PAGESIZE 0
SQL> SELECT 'Starting apply for patch 30138470/23136421 on ' ||
2 SYSTIMESTAMP FROM dual;
Starting apply for patch 30138470/23136421 on 22-DEC-19 02.33.31.103477 PM -05:0
0
SQL> SET PAGESIZE 10
SQL>
SQL> BEGIN
2 dbms_sqlpatch.patch_initialize(p_patch_id => 30138470,
3 p_patch_uid => 23136421,
4 p_flags => '&flags',
5 p_description => '&description',
6 p_action => 'APPLY',
7 p_logfile => '&full_logfile',
8 p_bundle_series => '&bundle_series');
9 END;
10 /
old 4: p_flags => '&flags',
new 4: p_flags => 'NB',
old 5: p_description => '&description',
new 5: p_description => 'DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015',
old 7: p_logfile => '&full_logfile',
new 7: p_logfile => '/u01/app/oracle/cfgtoollogs/sqlpatch/30138470/23136421/30138470_apply_DB01_2019Dec22_14_33_31.log',
old 8: p_bundle_series => '&bundle_series');
new 8: p_bundle_series => 'DBRU');
BEGIN
*
ERROR at line 1:
ORA-01693: max # extents (505) reached in lob segment
SYS.SYS_LOB0000190607C00013$$
ORA-06512: at "SYS.DBMS_SQLPATCH", line 639
ORA-06512: at "SYS.DBMS_SQLPATCH", line 605
ORA-06512: at line 2
[oracle@host01]$
The segment 'SYS.SYS_LOB0000190607C00013$$' reaches the extent maximum limitation (505), and it caused the SQL failure, find out what the segment is
SQL> select owner,object_name,object_type
2 from dba_objects
3 where object_name='SYS_LOB0000190607C00013$$';
OWNER OBJECT_NAME OBJECT_TYPE
-------------------- ------------------------------ -----------------------
SYS SYS_LOB0000190607C00013$$ LOB
SQL> select owner,table_name,column_name,segment_name
2 from dba_lobs
3 where segment_name='SYS_LOB0000190607C00013$$';
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME
---------- ------------------------------ -------------------- -------------------------
SYS REGISTRY$SQLPATCH PATCH_DIRECTORY SYS_LOB0000190607C00013$$
SQL>
SQL> desc sys.registry$sqlpatch
Name Null? Type
----------------------------- -------- -------------------------
PATCH_ID NOT NULL NUMBER
ACTION NOT NULL VARCHAR2(15)
ACTION_TIME NOT NULL TIMESTAMP(6)
DESCRIPTION VARCHAR2(100)
LOGFILE VARCHAR2(500)
STATUS VARCHAR2(25)
VERSION NOT NULL VARCHAR2(20)
PATCH_UID NOT NULL NUMBER
FLAGS VARCHAR2(10)
BUNDLE_SERIES VARCHAR2(30)
BUNDLE_ID NUMBER
INSTALL_ID NUMBER
PATCH_DIRECTORY BLOB
BUNDLE_DATA XMLTYPE
PATCH_DESCRIPTOR XMLTYPE
POST_LOGFILE VARCHAR2(500)
SQL>
SQL> select owner,segment_name,max_extents
2 from dba_segments
3 where segment_name in ('REGISTRY$SQLPATCH','SYS_LOB0000190607C00013$$');
OWNER SEGMENT_NAME MAX_EXTENTS
---------- ------------------------------ -----------
SYS REGISTRY$SQLPATCH 505
SYS SYS_LOB0000190607C00013$$ 505
2 from dba_objects
3 where object_name='SYS_LOB0000190607C00013$$';
OWNER OBJECT_NAME OBJECT_TYPE
-------------------- ------------------------------ -----------------------
SYS SYS_LOB0000190607C00013$$ LOB
SQL> select owner,table_name,column_name,segment_name
2 from dba_lobs
3 where segment_name='SYS_LOB0000190607C00013$$';
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME
---------- ------------------------------ -------------------- -------------------------
SYS REGISTRY$SQLPATCH PATCH_DIRECTORY SYS_LOB0000190607C00013$$
SQL>
SQL> desc sys.registry$sqlpatch
Name Null? Type
----------------------------- -------- -------------------------
PATCH_ID NOT NULL NUMBER
ACTION NOT NULL VARCHAR2(15)
ACTION_TIME NOT NULL TIMESTAMP(6)
DESCRIPTION VARCHAR2(100)
LOGFILE VARCHAR2(500)
STATUS VARCHAR2(25)
VERSION NOT NULL VARCHAR2(20)
PATCH_UID NOT NULL NUMBER
FLAGS VARCHAR2(10)
BUNDLE_SERIES VARCHAR2(30)
BUNDLE_ID NUMBER
INSTALL_ID NUMBER
PATCH_DIRECTORY BLOB
BUNDLE_DATA XMLTYPE
PATCH_DESCRIPTOR XMLTYPE
POST_LOGFILE VARCHAR2(500)
SQL>
SQL> select owner,segment_name,max_extents
2 from dba_segments
3 where segment_name in ('REGISTRY$SQLPATCH','SYS_LOB0000190607C00013$$');
OWNER SEGMENT_NAME MAX_EXTENTS
---------- ------------------------------ -----------
SYS REGISTRY$SQLPATCH 505
SYS SYS_LOB0000190607C00013$$ 505
The segment is BLOB column PATCH_DIRECTORY of table SYS.REGISTRY$SQLPATCH, both the BLOB segment and its table have extent maximum limitation 505. To remove the limitation,
SQL> alter table sys.registry$sqlpatch storage (maxextents unlimited);
Table altered.
SQL> alter table sys.registry$sqlpatch
2 modify lob (patch_directory) (storage (maxextents unlimited));
Table altered.
SQL> select owner,segment_name,max_extents
2 from dba_segments
3 where segment_name in ('REGISTRY$SQLPATCH','SYS_LOB0000190607C00013$$');
OWNER SEGMENT_NAME MAX_EXTENTS
---------- ------------------------------ -----------
SYS REGISTRY$SQLPATCH 2147483645
SYS SYS_LOB0000190607C00013$$ 2147483645
Table altered.
SQL> alter table sys.registry$sqlpatch
2 modify lob (patch_directory) (storage (maxextents unlimited));
Table altered.
SQL> select owner,segment_name,max_extents
2 from dba_segments
3 where segment_name in ('REGISTRY$SQLPATCH','SYS_LOB0000190607C00013$$');
OWNER SEGMENT_NAME MAX_EXTENTS
---------- ------------------------------ -----------
SYS REGISTRY$SQLPATCH 2147483645
SYS SYS_LOB0000190607C00013$$ 2147483645
Then re-run the datapatch utility,
[oracle@host01]$ $ORACLE _HOME/OPatch/datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Sun Dec 22 16:43:43 2019
Copyright (c) 2012, 2019, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_35782878_2019_12_22_16_43_43/sqlpatch_invocation.log
Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of SQL patches:
Bundle series DBRU:
ID 191015 in the binary registry and ID 190416 in the SQL registry
Adding patches to installation queue and performing prereq checks...
Installation queue:
Nothing to roll back
The following patches will be applied:
30138470 (DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015)
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...
Patch 30138470 apply: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30138470/23136421/30138470_apply_DB01_2019Dec22_16_44_33.log (no errors)
SQL Patching tool complete on Sun Dec 22 16:45:22 2019
SQL Patching tool version 12.2.0.1.0 Production on Sun Dec 22 16:43:43 2019
Copyright (c) 2012, 2019, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_35782878_2019_12_22_16_43_43/sqlpatch_invocation.log
Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of SQL patches:
Bundle series DBRU:
ID 191015 in the binary registry and ID 190416 in the SQL registry
Adding patches to installation queue and performing prereq checks...
Installation queue:
Nothing to roll back
The following patches will be applied:
30138470 (DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015)
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...
Patch 30138470 apply: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30138470/23136421/30138470_apply_DB01_2019Dec22_16_44_33.log (no errors)
SQL Patching tool complete on Sun Dec 22 16:45:22 2019
Now, it gives the log file name. Validate the status,
SQL> select patch_id,version,action,status,description
2 from dba_registry_sqlpatch order by action_time;
PATCH_ID VERSION ACTION STATUS DESCRIPTION
---------- ----------- -------- ---------- -------------------------------------------------
29314339 12.2.0.1 APPLY SUCCESS DATABASE APR 2019 RELEASE UPDATE 12.2.0.1.190416
30138470 12.2.0.1 APPLY END
30138470 12.2.0.1 APPLY SUCCESS DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015
2 from dba_registry_sqlpatch order by action_time;
PATCH_ID VERSION ACTION STATUS DESCRIPTION
---------- ----------- -------- ---------- -------------------------------------------------
29314339 12.2.0.1 APPLY SUCCESS DATABASE APR 2019 RELEASE UPDATE 12.2.0.1.190416
30138470 12.2.0.1 APPLY END
30138470 12.2.0.1 APPLY SUCCESS DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015
The patch is installed successfully.
No comments:
Post a Comment