Patch for Bug# 21255373 for platform 12.1.0.2.0 grid

Scenario Preview:

Patch for Bug# 21255373 for platform 12.1.0.2.0 grid

While applying patch we do pre-check analyze.   We found getting error as stdrac database as instance do not exists , There was service stdrac was offline and it was do not exists on clusterware services, As we remove stdrac database service error got removed.



[root@srv1 grid]# . oraenv
ORACLE_SID = [root] ? +ASM1
The Oracle base has been set to /u01/app/grid
[root@srv1 grid]# crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.DATA.dg
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.FRA.dg
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.asm
               ONLINE  ONLINE       srv1                     Started,STABLE
               ONLINE  ONLINE       srv2                     Started,STABLE
ora.net1.network
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.ons
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       srv1                     STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       srv2                     STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       srv2                     STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       srv2                     169.254.106.5 192.16
                                                             8.10.2,STABLE
ora.cvu
      1        ONLINE  ONLINE       srv2                     STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       srv2                     Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       srv1                     STABLE
ora.rac.acsrac.svc
      1        ONLINE  ONLINE       srv1                     STABLE
ora.rac.db
      1        ONLINE  ONLINE       srv1                     Open,STABLE
      2        ONLINE  ONLINE       srv2                     Open,STABLE
ora.rac.pretaf.svc
      1        ONLINE  ONLINE       srv1                     STABLE
ora.rac.pretaf_preconnect.svc
      1        ONLINE  ONLINE       srv2                     STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       srv1                     STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       srv2                     STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       srv2                     STABLE
ora.srv1.vip
      1        ONLINE  ONLINE       srv1                     STABLE
ora.srv2.vip
      1        ONLINE  ONLINE       srv2                     STABLE
ora.stdrac.db
      1        OFFLINE OFFLINE                               STABLE
      2        OFFLINE OFFLINE                               STABLE
--------------------------------------------------------------------------------

[root@srv1 OPatch]# ./opatchauto apply /u01/21255373/21255373 -analyze

opatchauto must run from one of the homes specified
opatchauto returns with error code = 2
[root@srv1 OPatch]# . oraenv
ORACLE_SID = [rac1] ? +ASM1
The Oracle base has been changed from /u01/app/oracle to /u01/app/grid
[root@srv1 OPatch]# ./opatchauto apply /u01/21255373/21255373 -analyze

OPatchauto session is initiated at Wed Dec 18 14:13:36 2019

System initialization log file is /u01/app/12.1.0/grid/cfgtoollogs/opatchautodb/systemconfig2019-12-18_02-13-41PM.log.

Following home(s) will not be included as part of current opatchauto session as it's software only RAC home:
Home path:/u01/app/12.1.0/grid, Host:srv2


Session log file is /u01/app/12.1.0/grid/cfgtoollogs/opatchauto/opatchauto2019-12-18_02-14-32PM.log
The id for this session is TQQV
The following RAC Databases have no instances associated with it.
        stdrac


The following RAC Databases have no instances associated with it.
        stdrac


The following RAC Databases have no instances associated with it.
        stdrac


OPATCHAUTO-72017: No RAC instance.
OPATCHAUTO-72017: RAC Databases have no instances associated with it.
OPATCHAUTO-72017: Check system configuration.
OPatchAuto failed.

OPatchauto session completed at Wed Dec 18 14:14:42 2019
Time taken to complete the session 1 minute, 6 seconds

 opatchauto failed with error code 42
[root@srv1 OPatch]#
#################################################################################
[root@srv1 grid]# srvctl remove database -d stdrac
Remove the database stdrac? (y/[n]) y
[root@srv1 grid]# crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.DATA.dg
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.FRA.dg
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.asm
               ONLINE  ONLINE       srv1                     Started,STABLE
               ONLINE  ONLINE       srv2                     Started,STABLE
ora.net1.network
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.ons
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       srv1                     STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       srv2                     STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       srv2                     STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       srv2                     169.254.106.5 192.16
                                                             8.10.2,STABLE
ora.cvu
      1        ONLINE  ONLINE       srv2                     STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       srv2                     Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       srv1                     STABLE
ora.rac.acsrac.svc
      1        ONLINE  ONLINE       srv1                     STABLE
ora.rac.db
      1        ONLINE  ONLINE       srv1                     Open,STABLE
      2        ONLINE  ONLINE       srv2                     Open,STABLE
ora.rac.pretaf.svc
      1        ONLINE  ONLINE       srv1                     STABLE
ora.rac.pretaf_preconnect.svc
      1        ONLINE  ONLINE       srv2                     STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       srv1                     STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       srv2                     STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       srv2                     STABLE
ora.srv1.vip
      1        ONLINE  ONLINE       srv1                     STABLE
ora.srv2.vip
      1        ONLINE  ONLINE       srv2                     STABLE
--------------------------------------------------------------------------------
[root@srv1 grid]#

[root@srv1 OPatch]# ./opatchauto apply /u01/21255373/21255373 -analyze

OPatchauto session is initiated at Wed Dec 18 15:26:32 2019

System initialization log file is /u01/app/12.1.0/grid/cfgtoollogs/opatchautodb/systemconfig2019-12-18_03-26-37PM.log.

Session log file is /u01/app/12.1.0/grid/cfgtoollogs/opatchauto/opatchauto2019-12-18_03-27-25PM.log
The id for this session is RJVJ

Executing OPatch prereq operations to verify patch applicability on home /u01/app/12.1.0/grid

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/12.1.0/dbhome_2
Patch applicability verified successfully on home /u01/app/12.1.0/grid

Patch applicability verified successfully on home /u01/app/oracle/product/12.1.0/dbhome_2


Verifying SQL patch applicability on home /u01/app/oracle/product/12.1.0/dbhome_2
No step execution required.........

OPatchAuto successful.

--------------------------------Summary--------------------------------

Analysis for applying patches has failed:

Host:srv1
CRS Home:/u01/app/12.1.0/grid
Version:12.1.0.2.0


==Following patches were SKIPPED:

Patch: /u01/21255373/21255373
Log: /u01/app/12.1.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-12-18_15-27-57PM_1.log
Reason: /u01/21255373/21255373 is not required to be applied to oracle home /u01/app/12.1.0/grid


Host:srv1
RAC Home:/u01/app/oracle/product/12.1.0/dbhome_2
Version:12.1.0.2.0
Analysis for patches has failed.


==Following patches FAILED in analysis for apply:

Patch: /u01/21255373/21255373
Log: /u01/app/oracle/product/12.1.0/dbhome_2/cfgtoollogs/opatchauto/core/opatch/opatch2019-12-18_15-27-58PM_1.log
Reason: Failed during Analysis: CheckConflictAgainstOracleHome Failed, [ Prerequisite Status: FAILED, Prerequisite output:
Summary of Conflict Analysis:

There are no patches that can be applied now.

Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
21255373, 29494060

Whole composite patch Conflicts/Supersets are:

Patch : 21255373

        Conflict with Composite Patch 29494060

Detail Conflicts/Supersets for each patch are:

Patch : 21255373

        Confl ...


OPATCHAUTO-72053: Analysis for the patches failed.
OPATCHAUTO-72053: Command execution failed.
OPATCHAUTO-72053: Please check the summary for more details.

OPatchauto session completed at Wed Dec 18 15:28:12 2019
Time taken to complete the session 1 minute, 41 seconds
[root@srv1 OPatch]#

NOTE: Already applied patch 29494060 so it is conflicting the same with patch number 21255373.

we can rollback 29494060 patch and apply further 21255373 patch.

Creation of LOGICAL STANDBY on standalone database 12c

Creation of  LOGICAL STANDBY on standalone database 12c

Step-1 > On Standby First we have to cancel the recovery process

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Step-2 > Add the log_archive_dest_3 at primary database for future , when you make primary database to  logical standby SQL>ALTER SYSTEM SET log_archive_dest_3='LOCATION=/u01/app/oracle/oradata/archivelog/ VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=orcl'; Step-3 > Build a Dictionary in the Redo Data SQL> EXECUTE DBMS_LOGSTDBY.BUILD; The DBMS_LOGSTDBY.BUILD procedure uses Flashback Query to obtain a consistent snapshot  of the data dictionary that is then logged in the redo stream. Oracle recommends setting the UNDO_RETENTION  initialization parameter to 3600 on both the primary and logical standby databases. Step-4 > To continue applying redo data to the physical standby database until it is ready to convert to a  logical standby database , issue the following SQL statement: SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY orcldr; Step-5> Create a new password File  Step-6> On the logical standby database, shutdown the instance and issue the STARTUP MOUNT statement to start and mount the database. Do not open the database; it should remain closed to user access   until later in the creation process In pfile Change the ------------(db_name=orcldr) SQL> Startup mount pfile=’?/dbs/pfile.ora’ SQL> create spfile from pfile='?/dbs/pfile.ora'; SQL> shut immediate SQL> startup mount Step-7 > Add the log_archive_dest_3 at Logical standby database   SQL> ALTER SYSTEM SET log_archive_dest_3 ='LOCATION=/u01/app/oracle/oradata/archivelog/   VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=orcldr'; Step-8 > Open the Logical Database  SQL> ALTER DATABASE OPEN RESETLOGS; Step-9 > Issue the following statement to begin applying redo data to the logical standby database SQL>  ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Dataguard : Switchover from Pluggable primary to standby databse 12c

• Points to be noted during switchover

1. Verify the primary database instance is open and the standby database instance is mounted

2. Verify there are no active users connected to the databases  SELECT username, status, logon_time   FROM v$session  WHERE status = 'ACTIVE'

3.Make sure the last redo data transmitted from the Primary database was applied on the standby database

Issue the following commands on Primary database and Standby database to find out redo's applied or not. SQL>select sequence#, applied from v$archvied_log; SQL>select sequence#, applied from v$archvied_log; (check on physical Standby) In order to apply redo data to the standby database as soon as it is received, use Real-time apply Step -1 > Command to execute on primary for Switchover to Standby  SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; Step-2> After step 1 finishes, Switch the original physical standby db orcldb to primary role; SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; Step-3> shut down and restart the former primary instance orcl SQL>SHUTDOWN IMMEDIATE; SQL>STARTUP MOUNT; Step-4 > After step 3 completes Note- If you are using oracle 10 g release 1 than you need to shutdown and restart the new primary  database orcldr SQL>SHUTDOWN IMMEDIATE; SQL>STARTUP; OR----- If you are using oracle 10 g release 2 than you need to open only the new primary  database orcldr SQL>ALTER DATABASE OPEN;  Step-4 > To Start the Recovery of new Standby ORCL SQL>alter database recover managed standby database disconnect from session; Step-5 > On the new primary database orcldr, perform a SWITCH LOGFILE to start sending redo  data to the standby database ORCL SQL>ALTER SYSTEM SWITCH LOGFILE; ------------------------------------------------------------------------------------------------------------- Switchover from Pluggable primary to standby database  example  Primary Instance  : rac1,rac2 Physical standby Instance : racdr In this example we have 2 node RAC with non rac DR physical standby database Check archive log sync between primary and standby database script used standby.sql and d.sql during switchover    select sequence#,applied from v$archived_log order by sequence# asc; SQL> !cat standby.sql select name,open_mode,database_role from v$database; select process,status,sequence# from v$managed_standby; SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD#; SQL> !cat d.sql set lines 200 select name, instance_name,db_unique_name, open_mode, database_role, flashback_on  current_scn,to_char(standby_became_primary_scn) failover_scn from v$database,v$instance; SQL> @d NAME   INSTANCE_NAME    DB_UNIQUE_NAME   OPEN_MODE        DATABASE_ROLE CURRENT_SCN    FAILOVER_SCN --------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ---------------------------------------- RAC   rac1    rac   READ WRITE        PRIMARY NO    0 SQL> @standby                                                                       NAME   OPEN_MODE        DATABASE_ROLE --------- -------------------- ---------------- RAC   READ WRITE        PRIMARY PROCESS   STATUS SEQUENCE# --------- ------------ ---------- ARCH   CLOSING        57 ARCH   CLOSING        58 ARCH   CLOSING        56 ARCH   OPENING        47 DGRD   ALLOCATED 0 DGRD   ALLOCATED 0 LNS   CONNECTED 0 DGRD   ALLOCATED 0 LNS   WRITING        59 DGRD   ALLOCATED 0 10 rows selected.     Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ----------  1        58      58   0  1        58      58   0  2        52      52   0  2        52      52   0 Switchover command for converting primary database to standby database  SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; Database altered. Shutdown database and start old primary in mount stage ( In case of RAC we can start srvctl start database -d rac -o mount ) , Here when type above command both instance 1 and 2 were shutdown.)  SQL> SHUTDOWN IMMEDIATE; ORA-01012: not logged on SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@srv1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 16 15:38:28 2021 Copyright (c) 1982, 2016, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 2432696320 bytes Fixed Size     8795664 bytes Variable Size   805308912 bytes Database Buffers  1610612736 bytes Redo Buffers     7979008 bytes Database mounted. SQL> @d NAME   INSTANCE_NAME    DB_UNIQUE_NAME   OPEN_MODE        DATABASE_ROLE CURRENT_SCN    FAILOVER_SCN --------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ---------------------------------------- RAC   rac1    rac   MOUNTED        PHYSICAL STANDBY NO    0 Now apply switchover command for converting  standby database to primary database SQL> @standby NAME   OPEN_MODE        DATABASE_ROLE --------- -------------------- ---------------- RAC   MOUNTED        PHYSICAL STANDBY PROCESS   STATUS SEQUENCE# --------- ------------ ---------- ARCH   CLOSING        47 DGRD   ALLOCATED 0 DGRD   ALLOCATED 0 ARCH   CLOSING        58 ARCH   CLOSING        52 ARCH   CLOSING        57 RFS   IDLE 0 RFS   IDLE        59 RFS   IDLE 0 RFS   IDLE 0 RFS   IDLE        53 PROCESS   STATUS SEQUENCE# --------- ------------ ---------- RFS   IDLE 0 RFS   IDLE 0 RFS   IDLE 0 MRP0   APPLYING_LOG        53 15 rows selected.     Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1        58      58 0 2        52      52 0 SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; Database altered. SQL> @d NAME   INSTANCE_NAME    DB_UNIQUE_NAME   OPEN_MODE        DATABASE_ROLE CURRENT_SCN    FAILOVER_SCN --------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ---------------------------------------- RAC   racdr    racdr   MOUNTED        PRIMARY YES    3788307 Shutdown new primary database and start database in normal state SQL> SHUTDOWN IMMEDIATE; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2432696320 bytes Fixed Size     8795664 bytes Variable Size   738200048 bytes Database Buffers 1677721600 bytes Redo Buffers     7979008 bytes Database mounted. Database opened. SQL> @d NAME   INSTANCE_NAME    DB_UNIQUE_NAME   OPEN_MODE        DATABASE_ROLE CURRENT_SCN    FAILOVER_SCN --------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ---------------------------------------- RAC   racdr    racdr   READ WRITE        PRIMARY YES    3788307 Forcefully switching  log file for testing purpose so we can confirm MRP0 process running fine on new DR  SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> @standby NAME   OPEN_MODE        DATABASE_ROLE --------- -------------------- ---------------- RAC   READ WRITE        PRIMARY PROCESS   STATUS SEQUENCE# --------- ------------ ---------- DGRD   ALLOCATED 0 ARCH   CLOSING        64 DGRD   ALLOCATED 0 ARCH   CLOSING        61 ARCH   CLOSING        62 ARCH   CLOSING        63 LNS   WRITING        65 DGRD   ALLOCATED 0 DGRD   ALLOCATED 0 9 rows selected.     Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1        64      64 0 1        64      64 0 2        54      54 0 2        54      54 0 Check new standby database and appy mediaa recovery  SQL> @standby NAME   OPEN_MODE        DATABASE_ROLE --------- -------------------- ---------------- RAC   MOUNTED        PHYSICAL STANDBY PROCESS   STATUS SEQUENCE# --------- ------------ ---------- ARCH   CONNECTED 0 DGRD   ALLOCATED 0 DGRD   ALLOCATED 0 ARCH   CLOSING        61 ARCH   CONNECTED 0 ARCH   CONNECTED 0 RFS   IDLE        62 RFS   IDLE 0 RFS   IDLE 0 RFS   IDLE 0 10 rows selected.     Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ----------  1        61      58   3  2        54      52   2 Apply Media recovery process on new standby database. SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> @standby NAME   OPEN_MODE        DATABASE_ROLE --------- -------------------- ---------------- RAC   MOUNTED        PHYSICAL STANDBY PROCESS   STATUS SEQUENCE# --------- ------------ ---------- ARCH   CONNECTED 0 DGRD   ALLOCATED 0 DGRD   ALLOCATED 0 ARCH   CLOSING        64 ARCH   CLOSING        62 ARCH   CLOSING        63 RFS   IDLE        65 RFS   IDLE 0 RFS   IDLE 0 RFS   IDLE 0 MRP0   APPLYING_LOG        65 11 rows selected.     Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ----------  1        64      64   0  2        54      54   0 New Primary PDB database SQL> sho pdbs     CON_ID CON_NAME   OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED   READ ONLY  NO 3 RACPDB   MOUNTED SQL> alter pluggable database all open; Pluggable database altered. SQL> sho pdbs     CON_ID CON_NAME   OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED   READ ONLY  NO 3 RACPDB   READ WRITE NO SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE; Pluggable database altered. SQL> !hostname srv4.example.com SQL>  New Physical Standby database  SQL> sho pdbs     CON_ID CON_NAME   OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED   MOUNTED 3 RACPDB   MOUNTED SQL> !hostname srv1.example.com SQL>