Steps to apply Database patches 4Q-2020 (Oct 2020 Release Update 12.2.0.1.201020)

Please find the instructions below to apply db patches to Grid_Home and Db_Home for Q4-2020.

Steps also include a bug fix patch which has been rolled-back and re-applied.

##################################################

## Instructions to apply Database patches 4Q-2020
##################################################
-- ------------------------------------------
-- Summary of patches to be installed
-- ------------------------------------------

Patch No --- Patch Description ---------------------------------------------------------------------------- Which Home to Patch -----------------------
31741641 Database Oct 2020 Release Update 12.2.0.1.201020       Only DB home for Oracle Non-RAC setup.
31802727 OCW Oct 2020 Release Update 12.2.0.1.201020            Both DB homes and Grid home
31749575 ACFS Oct 2020 Release Update 12.2.0.1.201020Footnote 2 Only Grid home
26839277 DBWLM Release Update 12.2.0.1.170913Footnote 2         Only Grid home
31805785 Tomcat Release Update 12.2.0.1.0Footnote 2             Only Grid home

-- ------------------------------------------
-- Check for patch s/w location
-- ------------------------------------------
-bash-4.1$ pwd
/tmp2/Patch_4Q-2020
-bash-4.1$ ls -ltr
drwxr-x--- 8 oracle dba       4096 Oct 10 14:48 31750094
drwxrwxr-x 4 oracle dba       4096 Oct 11 18:43 30910264
-rw-r----- 1 oracle dba     691159 Oct 30 03:33 p30910264_12201201020DBOCT2020RU_Linux-x86-64.zip
-rw-r----- 1 oracle dba 2220434531 Oct 30 03:34 p31750094_122010_Linux-x86-64.zip
-rw-r----- 1 oracle dba  118408624 Oct 30 03:34 p6880880_200000_Linux-x86-64.zip

-- ------------------------------------------
-- check for Grid_Home and Oracle_Home
-- ------------------------------------------
$ cat /etc/oratab | grep -v '^#\|^\s*$'

-- ------------------------------------------
-- Comment cron jobs
-- ------------------------------------------
:g/^/s/^/##db_patching##--/

-- ------------------------------------------
-- shut down db s/w
-- ------------------------------------------
. oraenv
+ASM
crsctl stop has

-- -------------------------------------------------------------------------------------
-- backup Grid_Home and Oracle_Home in compressed format
-- -------------------------------------------------------------------------------------
# tar -czvf grid_home_29102020.tar.gz /u02/app/oracle/product/12.2.0/grid --exclude='/u02/app/oracle/product/12.2.0/grid/rdbms/audit'
# tar -czvf db_home_29102020.tar.gz /u01/app/oracle/product/12.2.0/dbhome_1 --exclude='/u01/app/oracle/admin'
# tar -czvf inventory_29102020.tar.gz /u02/app/oraInventory

-- --------------------------------------------------------------------------------------------
-- check for
-- Opatch version: to check if its latest. if its not, please copy the latest opatch in Oracle Home
-- Oracle Home: to make sure you are patching correct Oracle Home
-- Central Inventory: to check the inventory location
-- --------------------------------------------------------------------------------------------
$ORACLE_HOME/OPatch/opatch lsinventory |grep 'OPatch version'|head -1
$ORACLE_HOME/OPatch/opatch lsinventory |grep 'Oracle Home'
$ORACLE_HOME/OPatch/opatch lsinventory |grep 'Central Inventory'

-- ------------------------------------------------------
-- unlock Grid Home with root user
-- ------------------------------------------------------
# /u02/app/oracle/product/12.2.0/grid/crs/install/roothas.pl -unlock

-- ------------------------------------------------------
-- Apply Grid patches
-- ------------------------------------------------------
[oracle@mydb OPatch]$
$ORACLE_HOME/OPatch/opatch apply -oh /u02/app/oracle/product/12.2.0/grid -local /tmp2/Patch_4Q-2020/31750094/31802727
$ORACLE_HOME/OPatch/opatch apply -oh /u02/app/oracle/product/12.2.0/grid -local /tmp2/Patch_4Q-2020/31750094/31749575
$ORACLE_HOME/OPatch/opatch apply -oh /u02/app/oracle/product/12.2.0/grid -local /tmp2/Patch_4Q-2020/31750094/26839277
$ORACLE_HOME/OPatch/opatch apply -oh /u02/app/oracle/product/12.2.0/grid -local /tmp2/Patch_4Q-2020/31750094/31805785
$ORACLE_HOME/OPatch/opatch lsinventory|grep "applied"

-- ------------------------------------------------------
-- lock Grid Home with root user
-- make sure all db s/w get started.
-- ------------------------------------------------------
# /u02/app/oracle/product/12.2.0/grid/crs/install/roothas.pl -postpatch

-- --------------------------------------------------------------
-- login to database and check for invalid SYS and SYSTEM objects
-- ---------------------------------------------------------------
set pages 9999 lines 160
col owner for a12
col object_name for a30
col object_type for a20
col comp_name for a40
col COMP_ID for a12
col VERSION for a16
select comp_name,comp_id,version,status from dba_registry;
select owner,object_name,object_type,status from  dba_objects where owner in ('SYS','SYSTEM') and status = 'INVALID' order by owner,object_type, object_name;

-- ------------------------------------------------------
-- for all db, set job_queue_process to 0
-- ------------------------------------------------------
set lines 200
select host_name,instance_name from v$instance;
show parameter job_queue
ALTER SYSTEM SET job_queue_processes=0;
show parameter job_queue

-- ------------------------------------------------------
-- shut down again db s/w
-- ------------------------------------------------------
. oraenv
+ASM
crsctl stop has

-- ------------------------------------------------------
-- Check conflict and apply sub-patch 31802727
-- ------------------------------------------------------
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp2/Patch_4Q-2020/31750094/31802727
$ORACLE_HOME/OPatch/opatch apply -oh /u01/app/oracle/product/12.2.0/dbhome_1 -local /tmp2/Patch_4Q-2020/31750094/31802727

-- ------------------------------------------------------
-- rollback bug fix patch 30910264
-- ------------------------------------------------------
$ORACLE_HOME/OPatch/opatch lsinventory|grep "30910264"
$ORACLE_HOME/OPatch/opatch rollback -id 30910264 -local
$ORACLE_HOME/OPatch/opatch lsinventory|grep "30910264"

-- ------------------------------------------------------
-- Check conflict and apply sub-patch 31741641
-- ------------------------------------------------------
$ORACLE_HOME/OPatch/opatch prereq  CheckConflictAgainstOHWithDetail  -phBaseDir /tmp2/Patch_4Q-2020/31750094/31741641
$ORACLE_HOME/OPatch/opatch apply -oh /u01/app/oracle/product/12.2.0/dbhome_1 -local /tmp2/Patch_4Q-2020/31750094/31741641

-- ------------------------------------------------------
-- apply bug fix patch 30910264
-- ------------------------------------------------------
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp2/Patch_4Q-2020/30910264
$ORACLE_HOME/OPatch/opatch apply -oh /u01/app/oracle/product/12.2.0/dbhome_1 -local /tmp2/Patch_4Q-2020/30910264

-- ------------------------------------------------------
-- check and make sure all patches get applied
-- ------------------------------------------------------
$ORACLE_HOME/OPatch/opatch lsinventory|grep "applied"

-- ------------------------------------------------------
-- postpatch to start all resources
-- make sure all db s/w get started.
-- ------------------------------------------------------
# /u02/app/oracle/product/12.2.0/grid/crs/install/roothas.pl -postpatch

crsctl stat res -t

-- ------------------------------------------------------
-- for all db, set job_queue_process back to original value
-- ------------------------------------------------------
set lines 200
select host_name,instance_name from v$instance;
show parameter job_queue
ALTER SYSTEM SET job_queue_processes=500;
show parameter job_queue

-- ------------------------------------------------------
-- run datapatch for each database
-- ------------------------------------------------------
cd $ORACLE_HOME/OPatch
./datapatch -verbose

-- ---------------------------------------------------------------------------------
-- check registry for each database to make sure new patches are visible in registry
-- ---------------------------------------------------------------------------------
set lines 500 pages 1000
set SERVEROUT ON
set long 2000000
col action_time for a12
col action for a10
col bundle_series for a4
col comments for a30
col description for a58
col namespace for a20
col status for a10
col version for a10
select TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time, action, status, description, version, patch_id, bundle_series
 FROM   sys.dba_registry_sqlpatch ORDER by action_time;

-- ------------------------------------------------------
-- run utlrp for each db
-- ------------------------------------------------------
@?/rdbms/admin/utlrp.sql

-- --------------------------------------------------------------------------------------------
-- login to database and check for invalid SYS and SYSTEM objects
-- ---------------------------------------------------------------------------------------------
set pages 9999 lines 160
col owner for a12
col object_name for a30
col object_type for a20
col comp_name for a40
col COMP_ID for a12
col VERSION for a16
select comp_name,comp_id,version,status from dba_registry;
select owner,object_name,object_type,status from  dba_objects where owner in ('SYS','SYSTEM') and status = 'INVALID' order by owner,object_type, object_name;

-- ------------------------------------------
-- Un-comment cron jobs
-- ------------------------------------------

:%s/##db_patching##--//g

RAC - Convert an Admin Managed RAC Database to Policy Managed for 11gR2 database

Convert an Admin Managed RAC Database to Policy Managed for 11gR2 database

Check the current pool status

[oracle@o1 dbs]$ srvctl config database -d RAC

Database unique name: RAC

Database name: RAC

Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/RAC/spfileRAC.ora

Domain: localdomain

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: RAC

Database instances: RAC1,RAC2

Disk Groups: DATA

Mount point paths:

Services:

Type: RAC

Database is administrator managed

Check the status of server pool.

[oracle@o1 dbs]$ srvctl status srvpool -a

Server pool name: Free

Active servers count: 0

Active server names:

Server pool name: Generic

Active servers count: 2

Active server names: o1,o2

NAME=o1 STATE=ONLINE

NAME=o2 STATE=ONLINE

Stop database -

[oracle@o1 dbs]$ srvctl stop database -d RAC

Create a server pool for the policy managed database.

[oracle@o1 dbs]$ srvctl add srvpool -g SrvPool1 -l 1 -u 2

-l refers to Minimum size of the server pool

-u refers to Maximum suze if the server pool

Modify the admin database to the new server pool

[oracle@o1 dbs]$ srvctl modify database -d RAC -g SrvPool1

Verify the status of the server pool

[oracle@o1 dbs]$ srvctl status srvpool -a

Server pool name: Free

Active servers count: 0

Active server names:

Server pool name: Generic

Active servers count: 0

Active server names:

Server pool name: SrvPool1

Active servers count: 2

Active server names: o1,o2

NAME=o1 STATE=ONLINE

NAME=o2 STATE=ONLINE

Verify the status of the database to confirm the conversion to a policy managed database

[oracle@o1 dbs]$ srvctl config database -d RAC

Database unique name: RAC

Database name: RAC

Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/RAC/spfileRAC.ora

Domain: localdomain

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: SrvPool1

Database instances:

Disk Groups: DATA

Mount point paths:

Services:

Type: RAC

Database is policy managed

ASM - sh: /u01/app/oracle/product/11.2.0/dbhome_1/bin/clsecho: No such file or directory

sh: /u01/app/oracle/product/11.2.0/dbhome_1/bin/clsecho: No such file or directory

Once you login to asmcmd, you get above error.

[oracle@o1 dbs]$ asmcmd

Connected to an idle instance.

sh: /u01/app/oracle/product/11.2.0/dbhome_1/bin/clsecho: No such file or directory

ASMCMD> ls

Can't exec "/u01/app/oracle/product/11.2.0/dbhome_1/bin/clsecho": No such file or directory at /u01/app/oracle/product/11.2.0/dbhome_1/lib/asmcmdshare.pm line 494, <STDIN> line 1.

Use of uninitialized value $buf in string ne at /u01/app/oracle/product/11.2.0/dbhome_1/lib/asmcmdshare.pm line 498, <STDIN> line 1.

ASMCMD> exit

This error occurs due to improper setting of ASM environment.

Once you set ASM env properly, error will be gone.

[oracle@o1 dbs]$ . oraenv

ORACLE_SID = [RAC1] ? +ASM1

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@o1 dbs]$ asmcmd

ASMCMD> ls

DATA/

ASMCMD>

RAC - Create spfile from pfile for RAC instances

Create spfile from pfile for RAC instances

The pfile is present at /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initRAC.ora.

We will first start RAC with this pfile and create new spfile in ASM and restart RAC with new spfile.

Steps performed as under:

Stop the cluster.

[oracle@o1 dbs]$ srvctl stop database -d RAC

[oracle@o1 dbs]$ crsctl stat res -t

--------------------------------------------------------------------------------

NAME           TARGET  STATE        SERVER                   STATE_DETAILS       

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.DATA.dg

               ONLINE  ONLINE       o1                                          

               ONLINE  ONLINE       o2                                          

ora.LISTENER.lsnr

               ONLINE  ONLINE       o1                                           

               ONLINE  ONLINE       o2                                          

ora.asm

               ONLINE  ONLINE       o1                       Started            

               ONLINE  ONLINE       o2                       Started             

ora.gsd

               OFFLINE OFFLINE      o1                                          

               OFFLINE OFFLINE      o2                                          

ora.net1.network

               ONLINE  ONLINE       o1                                           

               ONLINE  ONLINE       o2                                          

ora.ons

               ONLINE  ONLINE       o1                                          

               ONLINE  ONLINE       o2                                           

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.LISTENER_SCAN1.lsnr

      1        ONLINE  ONLINE       o1                                          

ora.LISTENER_SCAN2.lsnr

      1        ONLINE  ONLINE       o1                                          

ora.LISTENER_SCAN3.lsnr

      1        ONLINE  ONLINE       o1                                          

ora.cvu

      1        ONLINE  ONLINE       o1                                          

ora.o1.vip

      1        ONLINE  ONLINE       o1                                           

ora.o2.vip

      1        ONLINE  ONLINE       o2                                          

ora.oc4j

      1        ONLINE  ONLINE       o1                                          

ora.rac.db

      1        OFFLINE OFFLINE                               Instance Shutdown  

      2        OFFLINE OFFLINE                               Instance Shutdown  

ora.scan1.vip

      1        ONLINE  ONLINE       o1                                          

ora.scan2.vip

      1        ONLINE  ONLINE       o1                                          

ora.scan3.vip

      1        ONLINE  ONLINE       o1  

Mount one db instance.

[oracle@o1 dbs]$ . oraenv

ORACLE_SID = [+ASM1] ? RAC1

SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initRAC.ora';

SQL> select INSTANCE_NAME,HOST_NAME,STATUS from v$instance;

INSTANCE_NAME   HOST_NAME       STATUS     

--------------  -------------------- ------------

RAC1            o1.localdomain       MOUNTED

Create spfile from pfile.

SQL> create spfile='+DATA/RAC/spfileRAC.ora' from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initRAC.ora';

SQL> exit

Check in ASM if new spfile has been created.

[oracle@o1 dbs]$ . oraenv

ORACLE_SID = [RAC1] ? +ASM1

[oracle@o1 dbs]$ asmcmd

ASMCMD> cd DATA

ASMCMD> cd RAC

ASMCMD> ls -l

Type  Redund  Striped  Time Sys  Name

                            Y    CONTROLFILE/

                            Y    DATAFILE/

                            Y    ONLINELOG/

                            Y    PARAMETERFILE/

                            Y    TEMPFILE/

                            N    spfileRAC.ora => +DATA/RAC/PARAMETERFILE/spfile.268.961984543

ASMCMD> exit

Restart the cluster.

[oracle@o1 dbs]$ srvctl stop database -d RAC

[oracle@o1 dbs]$ srvctl start database -d RAC

Check for new parameter file.

    

[oracle@o1 dbs]$ . oraenv

ORACLE_SID = [+ASM1] ? RAC1

SQL> show parameter spfile

NAME         TYPE VALUE

------------ ----------- ------------------------------

spfile            string  +DATA/rac/spfilerac.ora

Oracle Backup and Restore Case Scenarios - Case Study 8 (Recover Noarchivelog database)

Oracle Backup and Restore Case Scenarios - Case Study 8

Case 8: Recover a Noarchivelog database

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

NAME          OPEN_MODE                     LOG_MODE

--------- -------------------- ------------

ORADB1      READ WRITE                     NOARCHIVELOG

Connect to target and catalog database

rman target / catalog rman/oracle@catdb

Take a full backup of database. You can't take rman backup of noarchivelog database when database is open.

RMAN> backup database;

...

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 09/27/2017 12:31:17

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

Put the database in mount mode.

RMAN> shutdown immediate;

RMAN> startup mount;

Now you can backup your database.

RMAN> backup database;

RMAN> startup

database is already started

database opened

Create One more table to take incremental backup.

sqlplus aj/aj@oradb1

SQL> create table testaj tablespace tbs_test as select * from dba_objects;

Table created.

Take the ccount of table to verify later.

SQL> select count(*) from testaj;

  COUNT(*)

----------

     86978

SQL> shut immediate;

Take an incremental level 1 backup of database

SQL> shutdown immediate

SQL> startup mount

rman target / catalog rman/oracle@catdb

RMAN> backup incremental level 1 database;

RMAN> shutdown immediate;

Simulate failure by renamign all datafiles, controlfiles, spfile and init file for ORADB1

Now run the restore script in RUN prompt.

RMAN> RUN

{

STARTUP NOMOUNT;

RESTORE SPFILE;

SHUTDOWN IMMEDIATE;

STARTUP NOMOUNT;

RESTORE CONTROLFILE;

ALTER DATABASE MOUNT;

RESTORE DATABASE;

ALTER DATABASE OPEN RESETLOGS;

}

connected to target database (not started)

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initORADB1.ora'

...

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

...

channel ORA_DISK_1: restoring SPFILE

output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/spfileORADB1.ora

...

channel ORA_DISK_1: restoring control file

output file name=/u01/app/oracle/oradata/ORADB1/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/ORADB1/control02.ctl

...

database mounted

...

channel ORA_DISK_1: starting datafile backup set restore

...

database opened

Verfy the data.

sqlplus aj/aj@oradb1

SQL> select count(*) from testaj;

  COUNT(*)

----------

     86978