Tag Archives: How to apply patch on standby

Apply DB PSU on Standby

How To Apply Database PSU Patch where Standby in Place

++ First need to be apply patch on standby and then on primary. – To make sure before apply a patch verify it’s certified in the MOS Patch note as “Standby-First”.
++ Make sure you have latest OPatch version before apply patch
++ Environment: Linux 6 , Version 11.2.0.4
++ Backup Oracle Home / Inventory on both primary and standby(click here for example)

Contents
___________________________________________________________________________________________________________

1. Download the PSU from https://support.oracle.com
2. Copy the PSU patch on both primary and standby servers

On Primary :

3. Check database_role
4. Check Archive Log Gap
5. Disable archive shipping

On Standby :

6. Cancel MRP
7. Shutdown Listener
8. Shutdown Database
9. Unzip the patch on empty directory
10. Check patch conflict against ORACLE_HOME
11. Apply Patch
12. Start the Listener
13. Mount the database

On Primary :

14. Shutdown Listener
15. Shutdown Database
16. Unzip the patch on empty directory
17. Check patch conflict against ORACLE_HOME
18. Apply Patch
19. Start the Listener
20. Start the database
21. Enable archive shipping

On Standby :

22. Start MRP

On Primary:

23. Run the catbundle.sql
24. Run utlrp.sql
25. Verify DBA_REGISTRY_HISTORY
26. Verify the Gap
___________________________________________________________________________________________________________


1. Download the PSU from https://support.oracle.com


2. Copy the PSU patch on both primary and standby servers

On Primary :


3. Check database_role

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      READ WRITE           PRIMARY

SQL>


4. Check Archive Log Gap

SQL> 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# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     13                    13          0 <---

SQL>


5. Disable archive shipping (log_archive_dest_state_2)

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE

SQL> alter system set log_archive_dest_state_2=defer scope=both;

System altered.

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      DEFER <----

On Standby :


6. Cancel MRP

SQL> select PROCESS,STATUS from v$managed_standby where PROCESS like 'MRP%';

PROCESS   STATUS
--------- ------------
MRP0      WAIT_FOR_LOG

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL>

SQL> select PROCESS,STATUS from v$managed_standby where PROCESS like 'MRP%';

no rows selected

SQL>


7. Shutdown Listener

[oracle@rac2 PSU]$ ps -ef | grep tns
root        15     2  0 17:28 ?        00:00:00 [netns]
oracle    3236     1  0 17:31 ?        00:00:00 /u01/app/oracle/product/11.2.0.4/bin/tnslsnr LISTENER_11DG -inherit
oracle   11945  3100  0 19:37 pts/0    00:00:00 grep tns
[oracle@rac2 PSU]$
[oracle@rac2 PSU]$ lsnrctl stop LISTENER_11DG

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-NOV-2018 19:38:07

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.rajasekhar.com)(PORT=1621)))
The command completed successfully
[oracle@rac2 PSU]$
[oracle@rac2 PSU]$ ps -ef | grep tns
root        15     2  0 17:28 ?        00:00:00 [netns]
oracle   11953  3100  0 19:38 pts/0    00:00:00 grep tns
[oracle@rac2 PSU]$


8. Shutdown Database

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      MOUNTED              PHYSICAL STANDBY

SQL>
SQL> shut immediate; <---
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>


9. Unzip the patch on empty directory

[oracle@rac2 ~]$ cd /u02/oracle/PSU
[oracle@rac2 PSU]$ ls -ltr
total 171772
-rw-r--r--. 1 oracle dba 175891102 Nov  7 19:21 p28204707_112040_Linux-x86-64.zip
[oracle@rac2 PSU]$
[oracle@rac2 PSU]$ unzip p28204707_112040_Linux-x86-64.zip
[oracle@rac2 PSU]$ ls -ltr
total 171876
drwxr-xr-x. 22 oracle dba      4096 Sep 19 12:24 28204707  <---
-rw-rw-r--.  1 oracle dba    102291 Oct 23 11:34 PatchSearch.xml
-rw-r--r--.  1 oracle dba 175891102 Nov  7 19:21 p28204707_112040_Linux-x86-64.zip
[oracle@rac2 PSU]$


10. Check patch conflict against ORACLE_HOME

[oracle@rac2 PSU]$ cd 28204707
[oracle@rac2 28204707]$ pwd
/u02/oracle/PSU/28204707
[oracle@rac2 28204707]$ /u01/app/oracle/product/11.2.0.4/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph /u02/oracle/PSU/28204707
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2018, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/11.2.0.4
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2018-11-07_19-44-08PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
[oracle@rac2 28204707]$


11. Apply Patch

[oracle@rac2 28204707]$ /u01/app/oracle/product/11.2.0.4/OPatch/opatch apply -oh /u01/app/oracle/product/11.2.0.4
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0.4
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch20

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   24732075  25869727  26609445  26392168  26925576  27338049  27734982  28204707

Do you want to proceed? [y|n]
y <-----
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y



Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0.4')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '24732075' to OH '/u01/app/oracle/product/11.2.0.4'

Patching component oracle.precomp.common, 11.2.0.4.0...

..
..
..
..

Patching component oracle.ctx, 11.2.0.4.0...
Composite patch 28204707 successfully applied.
Log file location: /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2018-11-07_19-45-22PM_1.log

OPatch succeeded.
[oracle@rac2 28204707]$

[oracle@rac2 PSU]$ /u01/app/oracle/product/11.2.0.4/OPatch/opatch lsinv | grep -i "11.2.0.4.181016"
Patch description:  "Database Patch Set Update : 11.2.0.4.181016 (28204707)"
[oracle@rac2 PSU]$


12. Start the Listener

[oracle@rac2 28204707]$ lsnrctl start LISTENER_11DG

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-NOV-2018 19:49:18

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.4/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/rac2/listener_11dg/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1621)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.rajasekhar.com)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_11DG
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                07-NOV-2018 19:49:18
Uptime                    0 days 0 hr. 0 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener_11dg/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1621)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "TEST_DG" has 1 instance(s).
  Instance "TEST_DG", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 28204707]$


13. Mount the database

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [TEST_DG] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 7 19:50:24 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2252784 bytes
Variable Size             788529168 bytes
Database Buffers          436207616 bytes
Redo Buffers                8970240 bytes
SQL>
SQL> alter database mount standby database;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      MOUNTED              PHYSICAL STANDBY

SQL>

On Primary :


14. Shutdown Listener

[oracle@rac1 PSU]$ ps -ef | grep tns
root        22     2  0 11:27 ?        00:00:00 [netns]
oracle    5008     1  0 11:45 ?        00:00:02 /u01/app/oracle/product/11.2.0.4/bin/tnslsnr LISTENER_11G -inherit
oracle   26656 11130  0 19:52 pts/0    00:00:00 grep tns
[oracle@rac1 PSU]$
[oracle@rac1 PSU]$ lsnrctl stop LISTENER_11G

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-NOV-2018 19:52:22

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1621)))
The command completed successfully
[oracle@rac1 PSU]$
[oracle@rac1 PSU]$ ps -ef | grep tns
root        22     2  0 11:27 ?        00:00:00 [netns]
oracle   26666 11130  0 19:52 pts/0    00:00:00 grep tns
[oracle@rac1 PSU]$


15. Shutdown Database

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      READ WRITE           PRIMARY

SQL>

SQL> shut immediate; <----
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@rac1 PSU]$ ps -ef | grep pmon
oracle    3079     1  0 11:28 ?        00:00:05 ora_pmon_UPGR
oracle   26679 11130  0 19:53 pts/0    00:00:00 grep pmon
[oracle@rac1 PSU]$


16. Unzip the patch on empty directory

[oracle@rac1 PSU]$ ls -ltr
-rw-r--r--.  1 oracle dba 175891102 Nov  3 17:23 p28204707_112040_Linux-x86-64.zip
[oracle@rac1 PSU]$ 
[oracle@rac1 PSU]$ unzip p28204707_112040_Linux-x86-64.zip
[oracle@rac1 PSU]$
[oracle@rac1 PSU]$ ls -ltr
total 171876
drwxr-xr-x. 22 oracle dba      4096 Sep 19 12:24 28204707
-rw-rw-r--.  1 oracle dba    102291 Oct 23 11:34 PatchSearch.xml
-rw-r--r--.  1 oracle dba 175891102 Nov  3 17:23 p28204707_112040_Linux-x86-64.zip
[oracle@rac1 PSU]$


17. Check patch conflict against ORACLE_HOME

[oracle@rac1 28204707]$ /u01/app/oracle/product/11.2.0.4/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph /u02/oracle/PSU/28204707
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2018, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/11.2.0.4
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2018-11-07_20-12-26PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed. <----

OPatch succeeded.
[oracle@rac1 28204707]$


18. Apply Patch

[oracle@rac1 28204707]$ /u01/app/oracle/product/11.2.0.4/OPatch/opatch apply -oh /u01/app/oracle/product/11.2.0.4
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0.4
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2018-11-07_20-13-06PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   17478514  18031668  18522509  19121551  19769489  20299013  20760982  21352635  21948347  22502456  23054359  24006111  24732075  25869727  26609445  26392168  26925576  27338049  27734982  28204707

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y



Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0.4')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '17478514' to OH '/u01/app/oracle/product/11.2.0.4'

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.sdo, 11.2.0.4.0...

Patching component oracle.sysman.agent, 10.2.0.4.5...

Patching component oracle.xdk, 11.2.0.4.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

Patching component oracle.sdo.locator, 11.2.0.4.0...

Patching component oracle.nlsrtl.rsf, 11.2.0.4.0...

Patching component oracle.xdk.rsf, 11.2.0.4.0...

..
..
...
..
Patching component oracle.ctx, 11.2.0.4.0...
Composite patch 28204707 successfully applied.
Log file location: /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2018-11-07_20-13-06PM_1.log

OPatch succeeded.
[oracle@rac1 28204707]$

[oracle@rac1 admin]$ /u01/app/oracle/product/11.2.0.4/OPatch/opatch lsinv | grep -i "11.2.0.4.181016"
Patch description:  "Database Patch Set Update : 11.2.0.4.181016 (28204707)"
[oracle@rac1 admin]$


19. Start the Listener

[oracle@rac1 28204707]$ lsnrctl start LISTENER_11G

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-NOV-2018 20:17:23

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.4/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/rac1/listener_11g/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1621)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_11G
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                07-NOV-2018 20:17:23
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener_11g/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1621)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "TEST" has 1 instance(s).
  Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 28204707]$


20. Start the database

[oracle@rac1 28204707]$ . oraenv
ORACLE_SID = [TEST] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 28204707]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 7 20:18:05 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2252784 bytes
Variable Size             788529168 bytes
Database Buffers          436207616 bytes
Redo Buffers                8970240 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      READ WRITE           PRIMARY <-------

SQL>


21. Enable archive shipping (log_archive_dest_state_2)

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      DEFER <---

SQL> alter system set log_archive_dest_state_2=ENABLE scope=both;

System altered.

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE <---

SQL>

On Standby :


22. Start MRP

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      MOUNTED              PHYSICAL STANDBY <-----

SQL> select PROCESS,STATUS from v$managed_standby where PROCESS like 'MRP%';

no rows selected

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select PROCESS,STATUS from v$managed_standby where PROCESS like 'MRP%';

PROCESS   STATUS
--------- ------------
MRP0      WAIT_FOR_LOG

SQL>

On Primary:


23. Run the catbundle.sql

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      READ WRITE           PRIMARY

1 row selected.

SQL>@/u01/app/oracle/product/11.2.0.4/rdbms/admin/catbundle.sql psu apply

SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
  2    (action_time, action,
  3     namespace, version, id,
  4     bundle_series, comments)
  5  VALUES
  6    (SYSTIMESTAMP, 'APPLY',
  7     SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
  8     '11.2.0.4',
  9     181016,
 10     'PSU',
 11     'PSU 11.2.0.4.181016');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_TEST_APPLY_2018Nov07_20_24_25.log
SQL>


24. Run utlrp.sql

SQL> @/u01/app/oracle/product/11.2.0.4/rdbms/admin/utlrp.sql
..
..
..
PL/SQL procedure successfully completed.

SQL>


25. Verify dba_registry_history

SQL> col ACTION for a10
SQL> col version for a10
SQL> col comments for a30
SQL> select action,VERSION,COMMENTS from dba_registry_history where id like '%181016%';

ACTION     VERSION    COMMENTS
---------- ---------- ------------------------------
APPLY      11.2.0.4   PSU 11.2.0.4.181016

SQL>


26. Verify the Gap

SQL> 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# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     15                    15          0 <---

SQL>

SQL> set lines 180
SQL> col DEST_NAME for a30
SQL> col error for a10
SQL> select dest_id,DEST_NAME,status,error from v$archive_dest where dest_id < 3; 
DEST_ID DEST_NAME STATUS ERROR 
---------- ------------------------------ --------- ----------
1 LOG_ARCHIVE_DEST_1 VALID 
2 LOG_ARCHIVE_DEST_2 VALID 
SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com