Month: January 2018

How to Remove PDB From Restricted Mode

Run “datapatch” might remove PDB from restricted mode

1) Check PDB  status.

SQL> select INST_ID,NAME,OPEN_MODE,RESTRICTED 
     from gv$pdbs order by 1,2;

INST_ID   NAME        OPEN_MODE  RES
-------- ------------ ---------- ---
 1       RACTESTPDB   READ WRITE YES
 1       PDB$SEED     READ ONLY  NO
 2       RACTESTPDB   READ WRITE YES
 2       PDB$SEED     READ ONLY  NO

2) Check PDB_PLUG_IN_VIOLATIONS.

SQL> select status, message, action 
     from   pdb_plug_in_violations 
     where  status !='RESOLVED';

STATUS      MESSAGE       ACTION
---------   ------------ ------------
PNDING     PSU bundle    Call datapatch.
           patch 180116 
           (DATABASE PATCH
           SET UPDATE
           12.1.0.2.180116):
           Installed in the 
           CDB but not in 
           the PDB.
PENDING     ...
            ..
            .

3) Rerun “datapatch” as per “run datapatch”.

4)Bounce all instances and pdbs.

$srvctl stop database -d RACTEST;

$srvctl start database -d RACTEST;

SQL> alter pluggable database RACTESTPDB open instances=all;

Pluggable database altered.

SQL> select INST_ID,NAME,OPEN_MODE,RESTRICTED 
     from gv$pdbs order by 1,2;

INST_ID  NAME         OPEN_MODE  RES
-------- ------------ ---------- ---
 1       RACTESTPDB   READ WRITE NO
 1       PDB$SEED     READ ONLY  NO
 2       RACTESTPDB   READ WRITE NO
 2       PDB$SEED     READ ONLY  NO

SQL> alter pluggable database RACTESTPDB save state instances=all;

Pluggable database altered.

SQL>  select CON_ID,CON_NAME,INSTANCE_NAME,state, restricted from  DBA_PDB_SAVED_STATES;

-- make sure PDB' restricted=NO

The pluggable databases that need to be patched must be in upgrade

Normally “datapatch” for OJVM requires database in UPGRADE mode

After applied PSU and OJVM binary patches, and  run “datapatch”,  then get error “The pluggable databases that need to be patched must be in upgrade mode”.

$ ./datapatch -verbose
...
..
.
 The following patches will be rolled back:
 22139226 (Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016))
 The following patches will be applied:
 27001733 (Database PSU 12.1.0.2.180116, Oracle JavaVM Component (JAN2018))
 26925311 (DATABASE PATCH SET UPDATE 12.1.0.2.180116)

Error: prereq checks failed!
 patch 22139226: The pluggable databases that need to be patched must be in upgrade mode
Prereq check failed, exiting without installing any patches.

...
..
.

The workaround is to add “skip_upgrade_check” option for “datapatch” command:

$ ./datapatch -verbose -skip_upgrade_check
SQL Patching tool version 12.1.0.2.0 Production on Fri Jan 19 17:33:18 2018
Copyright (c) 2012, 2016, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_74462_2018_01_19_17_33_18/sqlpatch_invocation.log

Connecting to database...OK
Note: Datapatch will only apply or rollback SQL fixes for PDBs
 that are in an open state, no patches will be applied to closed PDBs.
 Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
 (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Patch 22139226 (Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016)):
 Installed in RACTESTPDB only
Patch 27001733 (Database PSU 12.1.0.2.180116, Oracle JavaVM Component (JAN2018)):
 Installed in the binary registry and CDB$ROOT PDB$SEED
Bundle series PSU:
 ID 180116 in the binary registry and ID 180116 in PDB CDB$ROOT, ID 180116 in PDB PDB$SEED, ID 160419 in PDB RACTESTPDB

Adding patches to installation queue and performing prereq checks...
Installation queue:
 For the following PDBs: CDB$ROOT PDB$SEED
 Nothing to roll back
 Nothing to apply
 For the following PDBs: RACTESTPDB
 The following patches will be rolled back:
 22139226 (Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016))
 The following patches will be applied:
 27001733 (Database PSU 12.1.0.2.180116, Oracle JavaVM Component (JAN2018))
 26925311 (DATABASE PATCH SET UPDATE 12.1.0.2.180116)

Installing patches...
Patch installation complete. Total patches installed: 3

Validating logfiles...
Patch 22139226 rollback (pdb RACTESTPDB): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22139226/19729684/22139226_rollback
Patch 27001733 apply (pdb RACTESTPDB): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/27001733/21728084/27001733_apply_LG
Patch 26925311 apply (pdb RACTESTPDB): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26925311/21850549/26925311_apply_LG
SQL Patching tool complete on Fri Jan 19 17:37:33 2018


SQL> select PATCH_ID,VERSION,ACTION,STATUS,DESCRIPTION 
     from dba_registry_sqlpatch order by ACTION_TIME;
PATCH_ID  VERSION ACTION     STATUS  DESCRIPTION
---------- --------------    ------- --------------- ----------------------------------------------------------------------------------------------------
 22139226 12.1.0.2 APPLY     SUCCESS Database PSU 12.1.0.2.160119, 
                                     Oracle JavaVM Component (Jan2016)
 22291127 12.1.0.2 APPLY     SUCCESS Database Patch Set Update : 
                                     12.1.0.2.160419 (22291127)
 22139226 12.1.0.2 ROLLBACK  SUCCESS Database PSU 12.1.0.2.160119, 
                                     Oracle JavaVM Component (Jan2016)
 27001733 12.1.0.2 APPLY     SUCCESS Database PSU 12.1.0.2.180116, 
                                     Oracle JavaVM Component (JAN2018)
 26925311 12.1.0.2 APPLY     SUCCESS DATABASE PATCH SET UPDATE 
                                     12.1.0.2.180116

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).

OR

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