Make DBA Life Easy

OPATCHAUTO-68021: The following argument(s) are required: [-wallet]

Always download and apply the latest version of Opatch utility

When applying “Patch 27100009 – Grid Infrastructure Jan 2018 Release Update (GI RU) 12.2.0.1.180116”, get errors from auto opatch:

# /u01/app/12.2.0.1/grid/OPatch/opatchauto apply  /PATCHES/12.2.0.1/27100009
System initialization log file is /u01/app/12.2.0.1/grid/cfgtoollogs/opatchautodb/systemconfig2018-01-18_10-59-37AM.log.
Session log file is /u01/app/12.2.0.1/grid/cfgtoollogs/opatchauto/opatchauto2018-01-18_11-02-03AM.log

OPATCHAUTO-68021: Missing required argument(s).
OPATCHAUTO-68021: The following argument(s) are required: [-wallet]
OPATCHAUTO-68021: Provide the required argument(s).
Oracle OPatchAuto Version 13.9.0.2.0
Copyright (c) 2016, Oracle Corporation.  All rights reserved.

DESCRIPTION
    This operation applies patch.
...
..
.

The workaround is to use latest version of Opatch (12.2.0.1.9 or higher).

Manually create a wallet in 12.2 as per “Creation of opatchauto wallet in 12.2 in 12.2.0.1.8 (Doc ID 2270185.1)”.

OPatchauto Failed due to exit status 2

ALWAYS get latest OPATCH utility on all RAC nodes first before applying patches.

When applying 12.2.0.1 GI RU( 12.2.0.1.180116 ), there are a couple of issues resolved by :

Detach ORACLE_HOME which have no instances running against.
Remove databases which are non required , but still in OCR.
Use latest opatch for “wallet” issue.

Finally get another issue when running opatchauto:

# /u01/app/12.2.0.1/grid/OPatch/opatchauto apply 
  /PATCHES/12.2.0.1/27100009 -oh /u01/app/12.2.0.1/grid

OPatchauto session is initiated at Thu Jan 18 11:56:18 2018
System initialization log file is /u01/app/12.2.0.1/grid/cfgtoollogs/
opatchautodb/systemconfig2018-01-18_11-56-29AM.log.
Session log file is /u01/app/12.2.0.1/grid/cfgtoollogs/opatchauto/
opatchauto2018-01-18_11-57-03AM.log
The id for this session is AJ8A

oracle.dbsysmodel.driver.sdk.productdriver.ProductDriverException: 
Execution failed for host rachost2 due to : com.oracle.cie.remote.RemoteConnectionException: 
Command execution of [/u01/app/12.2.0.1/grid//perl/bin/perl 
/u01/app/12.2.0.1/grid/OPatch/auto/database/bin/RemoteHostExecutor.pl  
-GRID_HOME=/u01/app/12.2.0.1/grid -OBJECTLOC=/u01/app/12.2.0.1/grid/
/cfgtoollogs/opatchautodb/hostdata.obj -CRS_ACTION=check 
-CLUSTERNODES=rachost1,rachost2 -JVM_HANDLER=oracle/dbsysmodel/driver
/sdk/productdriver/remote/RemoteDataCollector ] failed due to exit status 2.

OPatchAuto failed.

OPatchauto session completed at Thu Jan 18 11:57:11 2018

Time taken to complete the session 0 minute, 54 seconds

opatchauto failed with error code 42

The below command caused errors :

 /u01/app/12.2.0.1/grid//perl/bin/perl 
/u01/app/12.2.0.1/grid/OPatch/auto/database/bin/RemoteHostExecutor.pl  
-GRID_HOME=/u01/app/12.2.0.1/grid -OBJECTLOC=/u01/app/12.2.0.1/grid/
/cfgtoollogs/opatchautodb/hostdata.obj -CRS_ACTION=check 
-CLUSTERNODES=rachost1,rachost2 -JVM_HANDLER=oracle/dbsysmodel/driver
/sdk/productdriver/remote/RemoteDataCollector

Found “/u01/app/12.2.0.1/grid/OPatch/auto/database/bin/RemoteHostExecutor.pl” file does not exist on racnode2, because the OPatch on this node is still old version 12.2.0.1.6. But on racnode1 OPatch version is latest 12.2.0.1.11.

Copy the latest OPatch from node1 onto node2, then run the opatchauto successfully.

ALWAYS get latest OPATCH on all RAC nodes first before applying patches.

Warning: standby redo logs not configured for thread 0 on DATABASE

When run “Validate Database” DGML command, get “Warning: standby redo logs not configured for thread 0 ” and “Insufficient SRLs” warnings on 12.1.0.2 database with “Database Patch Set Update : 12.1.0.2.170814 (26609783)”.

DGMGRL> connect sys
Password:
Connected as SYSDG.
DGMGRL> validate database "STBPROD";

Database Role: Physical standby database
 Primary Database: PROD

Ready for Switchover: Yes
 Ready for Failover: Yes (Primary Running)

Temporary Tablespace File Information:
 PROD TEMP Files: 3
 STBPROD TEMP Files: 3

Flashback Database Status:
 PROD: Off
 STBPROD: Off

Current Log File Groups Configuration:
 Thread # Online Redo Log Groups Standby Redo Log Groups Status
 (PROD) (STBPROD)
 0      232               0              Insufficient SRLs
 Warning: standby redo logs not configured for thread 0 on STBPROD

Future Log File Groups Configuration:
 Thread # Online Redo Log Groups Standby Redo Log Groups Status
 (STBPROD) (PROD)
 0      232               0                   Insufficient SRLs
 Warning: standby redo logs not configured for thread 0 on PROD

According to “Bug 20582405 – dgmgrl “validate database” shows warning “standby redo logs not configured for thread 0″ (Doc ID 20582405.8)”, it is fixed:

Fixed:

The fix for 20582405 is first included in

12.2.0.1 (Base Release)

12.1.0.2.170831 (Aug 2017) Bundle Patch for Windows Platforms

Temporary Tablespace Group

Temporary Tablespace and Temporary Tablespace Group

Create a temporary tablespace, and implicitly add it to a temporary tablespace group.

SQL>CREATE BIGFILE TEMPORARY TABLESPACE TEMP01 TEMPFILE '+DATA1' 
    SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 32G EXTENT MANAGEMENT 
    LOCAL UNIFORM SIZE 1M TABLESPACE GROUP TEMP_GRP;

Add a temporary tablespace to the temporary tablespace group.

SQL>ALTER TABLESPACE temp02 TABLESPACE GROUP TEMP_GRP;

Remove a temporary tablespace from a temporary tablespace group.

SQL>ALTER TABLESPACE temp03 TABLESPACE GROUP '';

Assign a user to a default temporary tablespace group.

SQL>ALTER USER TESTUSER TEMPORARY TABLESPACE TEMP_GRP;

Assign database a default temporary tablespace group.

SQL>ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GRP;

Pluggable database altered.

Check temporary tablespace and its files.

SQL> select tablespace_name,FILE_ID,file_name,AUTOEXTENSIBLE,
     MAXBYTES/1024/1024/1024, INCREMENT_BY,USER_BYTES/1024/1024/1024 
     from dba_temp_files;

Check temporary tablespace and group.

SQL>select * from DBA_TABLESPACE_GROUPS;

Check who is using temporary tablespace.

SQL> select INST_ID,USERNAME,TABLESPACE,CONTENTS,SQL_ID,BLOCKS 
     from gv$SORT_USAGE;

Drop temporary tablespace.

SQL> drop tablespace temp02 including contents and datafiles;
drop tablespace temp02 including contents and datafiles
*
ERROR at line 1:
ORA-10921: Cannot drop tablespace belonging to default temporary 
           tablespace group

Drop temporary tablespace group.

After remove all the temporary tablespace from temporary tablespace group, 
the temporary tablespace group will be dropped from database automatically.

Drop Temporary Tablespace Hangs With “enq: TS – contention”

Background process SMON is using the temporary tablespace to be dropped with “enq: TS – contention” wait event.

After created a new temporary tablespace TEMPNEW as database default temporary tablespace, then try to drop the old temporary tablespace “TEMP”, it hangs up forever.

SQL> drop tablespace temp including contents and datafiles;

Check session wait event “enq: TS – contention” against TEMP tablespace.

SQL> select SID,EVENT,P2TEXT,P2,SECONDS_IN_WAIT,WAIT_CLASS 
       from v$session_wait 
      where sid=3413;

SID   EVENT                P2TEXT        P2  SECONDS_IN_WAIT WAIT_CLASS
----- -------------------- ------------- --- -------------- ----------
 3413 enq: TS - contention tablespace ID  2  973             Other

SQL> select TS#,name from v$tablespace where ts#=2;

TS#         NAME
---------- -----------
 2         TEMP

Check who is still using temporary tablespace TEMP.

SQL> select INST_ID,USERNAME,TABLESPACE,CONTENTS,SQL_ID,BLOCKS 
       from gv$SORT_USAGE;

INST_ID     USERNAME   TABLESPACE  CONTENTS  SQL_ID       BLOCKS
---------- ---------   ----------- --------- ------------ ----------
 1         USER1       TEMP        TEMPORARY 4xhb02sm7sgqk 128
 1         USER2       TEMP        TEMPORARY 8r74hjykx88y7 128
 1         USER1       TEMP        TEMPORARY 4xhb02sm7sgqk 128
 1         USER1       TEMP        TEMPORARY 8r74hjykx88y7 128
 1         USER2       TEMP        TEMPORARY cjjt4dw97qc1n 128
 1         USER2       TEMP        TEMPORARY 8r74hjykx88y7 128

6 rows selected.

Those user process are waiting for TS enqueue which is held by SMON.

SQL> SELECT SID,ID1,ID2,LMODE,REQUEST FROM V$LOCK WHERE TYPE='TS';

SID         ID1       ID2        LMODE       REQUEST
---------- ---------- ---------- ---------- ----------
 3413      2          1          0           6
 3413      2          0          6           0
 4261      7          2          3           0

SQL> SELECT PROGRAM FROM V$SESSION WHERE SID in (3413,4261);

PROGRAM
-----------------------------
sqlplus@ractest1 (TNS V1-V3)
oracle@ractest1 (SMON)

SOLUTION

Bounce the database.
then drop the temporary tablespace successfully.