EasyReliableDBA

Software Training and dumps for Exam are available on www.easyreliable.com To download dumps. Please visit Links and Just pay $5/Rs300 http://easyreliable.com/easydumps/ This blog is Oracle DBA blog which cover Oracle RAC(10g,11g ,12c and 19c), Dataguard, RMAN,Backup&Recovery,Troubleshooting and Performance Tuning and other topics like Oracle Cloud,MongoDB,Golden Gate and Exadata.Please subscribe below to get update on my blog.We also provide Online SoftwareTraining and DBA Consulting

Friday, 19 August 2022

Converting a physical standby database into snapshot standby database and vis versa and switch over and switchback

Converting a physical standby database into snapshot standby database

1) To check gap

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#

select name,open_mode,database_role,controlfile_type from v$database;

select flashback_on from v$database;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

alter database flashback on;

select flashback_on from v$database;

show parameter recovery

select name from v$recovery_file_dest;

show parameter db_recovery_file_dest_size

check recovery desk and recovery desk size parameter

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

select name,open_mode,database_role,controlfile_type from v$database;

select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

shutdown immediate ---

startup ------

select name,open_mode,database_role,controlfile_type from v$database;

Converting a snapshot standby database into physical standby

Revert back

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

select name,open_mode,database_role,controlfile_type from v$database;

shutdown immediate -

startup mount -

select name,open_mode,database_role,controlfile_type from v$database;

alter database convert to physical standby;

SQL> alter database convert to physical standby;

Database altered.

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

select name,open_mode,database_role,controlfile_type from v$database

ERROR at line 1:

ORA-01507: database not mounted

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

select name,open_mode,database_role,controlfile_type from v$database;

shutdown immediate;

startup mount

select name,open_mode,database_role,controlfile_type from v$database;

select flashback_on from v$database;

alter database flashback off --

select flashback_on from v$database;

ALTER DATABASE RECOVER managed standby database disconnect from session;

select name,open_mode,database_role,controlfile_type from v$database;

==============Standby Database Switchover and Switchback=============

Overview

Check in db "dba_jobs" - stop those jobs in DC servers

Verify that each database is properly configured for the role

Verify that there are no redo transport errors or redo gaps at the standby database

Verify target of your switchover is synchronized or not

Check at D.R site Redo is received or not and applied

Ensure temporary files exist on the standby database that match the temporary files on the primary database

Ensure Flashback is on both D.C and D.R

Initiate the switchover on the primary database

Shut down and then mount the former primary database.

Verify that the switchover target is ready to be switched to the primary role.

Switch the target physical standby database role to the primary role.

Open the new primary database.

Start Redo Apply on the new physical standby database.

==============

Overview steps:

Step:1 Check the db name, open mode, database role of the primary and standby databases.

Step:2 Select switchover status on primary & standby db.

Step:3 Check that there is no active users connected to the databases.

Step:4 Switch the current online redo log file on primary database and verify that it has been applied in the standby database.

Step:5 Connect with primary database and initiate the switchover.

Step:6 Bounce the primary db and check the switchover status.

Step:7 Then convert the physical standby into primary db.(stop the MRP process)

Step:8 Open the new standby db in read only mode.

Step:9 Apply the redo log files in newly created standby.(start the MRP process).Check whether the logs are applying in the new standby db.

Step:1 Check the db name, open mode, database role of the primary and standby databases.

Precheck

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

SELECT PROTECTION_MODE,CONTROLFILE_TYPE,REMOTE_ARCHIVE,PROTECTION_LEVEL,DATABASE_ROLE ROLE, SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS FROM V$DATABASE;

set lines 2000

set pages 2000

set owner for a13

set username for a12

col host for a50

col username for a15

col DB_LINK for a30

col CREATED for a30

select * from dba_db_links ;

select owner,DB_LINK from dba_db_links

set lines 300 pages 1000

col OWNER for a30

col JOB_NAME for a30

SELECT OWNER, JOB_NAME, START_DATE,NEXT_RUN_DATE,STATE FROM DBA_SCHEDULER_JOBS WHERE ENABLED='TRUE' AND OWNER <> 'SYS';

col JOB_ACTION for a20

col START_DATE for a20

col LAST_START_DATE for a20

col LAST_RUN_DURATION for a20

col NEXT_RUN_DATE for a30

select JOB_NAME,JOB_ACTION,REPEAT_INTERVAL,LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE from dba_scheduler_jobs where owner='SYS' and JOB_NAME like '%STAT%';

SELECT db_unique_name, open_mode, database_role FROM v$database;

SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;

column destination format a35 wrap

column process format a7

column archiver format a8

column ID format 99

column mid format 99

select dest_id "ID",destination,status,target, schedule,process,mountid mid from v$archive_dest order by dest_id;

SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

select * from V$DATAGUARD_CONFIG

SELECT FS_FAILOVER_STATUS "FSFO STATUS",FS_FAILOVER_CURRENT_TARGET TARGET,FS_FAILOVER_THRESHOLD THRESHOLD,FS_FAILOVER_OBSERVER_PRESENT "OBSERVER PRESENT" FROM V$DATABASE;

SELECT PROTECTION_MODE,CONTROLFILE_TYPE,REMOTE_ARCHIVE,PROTECTION_LEVEL,DATABASE_ROLE ROLE, SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS FROM V$DATABASE;

SELECT MESSAGE FROM V$DATAGUARD_STATUS;

SELECT fs_failover_observer_present, fs_failover_observer_host FROM v$database;

set lines 100 pages 999

col member format a70

select st.group#,st.sequence#,ceil(st.bytes / 1048576) mb,lf.member

from v$standby_log st,v$logfile lf

where st.group# = lf.group#

ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;

SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

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;

3) show parameter log_archive_config

show parameter dg

SELECT MESSAGE FROM V$DATAGUARD_STATUS;

4) select name,flashback_on from v$database;

5) select name from v$tempfile;

6) select value from v$diag_info where name ='Diag Trace';

7) show parameter recovery;

show parameter job

show parameter aq

8) check crontab and scheduler if any state and rman backup will scheduled during activity if yes disable it

Implementation

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

run command on primary database(production) fist we need to run primary database

compatible = "11.2.0"

log_archive_dest_1 = "LOCATION=/db1/orafra/svfe VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=svfe"

log_archive_dest_2 = "SERVICE=svfedr LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=svfedr"

log_archive_dest_state_1 = "ENABLE"

alter system switch logfile

switchover primary to standby

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

select SWITCHOVER_STATUS from V$DATABASE;

select name,database_role,switchover_status from v$database;

!ps -ef|grep rman

SELECT * From dba_jobs_running;

show parameter job

show parameter aq

SQL>

SQL> alter system set job_queue_processes=0 ;

System altered.

SQL> alter system set aq_tm_processes=0 ;

System altered.

show parameter job

show parameter aq

select name,database_role,switchover_status from v$database;

alter database commit to switchover to physical standby with session shutdown

shut abort

startup mount;

SHUTDOWN IMMEDIATE;

STARTUP NOMOUNT;

ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED Oracle instance DISCONNECT FROM SESSION;

Archived Log entry 2278737 added for T-1.S-1136245 ID 0x109a137e LAD:1

Completed: ALTER DATABASE MOUNT

2022-05-07T02:09:27.821104+04:00

ARC7 started with pid=42, OS id=941

Starting background process ARC8

2022-05-07T02:09:27.834239+04:00

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY *

Completed Successfully

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY *

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

2022-05-07T02:09:27.861561+04:00

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

select name, open_mode, db_unique_name, database_role from v$database;

==================================

Convert standby to primary: Our primary is already converted to standby. Now it’s time to convert original standby into primary

After completing activity on primary database (production)

convert standby database to primary database

1) SELECT PROTECTION_MODE,CONTROLFILE_TYPE,REMOTE_ARCHIVE,PROTECTION_LEVEL,DATABASE_ROLE ROLE, SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS FROM V$DATABASE;

2) select name,flashback_on from v$database;

3) select name from v$tempfile;

4) select value from v$diag_info where name ='Diag Trace';

5) show parameter recovery;

6) check crontab and scheduler if any state and rman backup will scheduled during activity if yes disable it

1)select SWITCHOVER_STATUS from V$DATABASE;

if require older version

Cancel the MRP process

SQL> alter database recover managed standby database cancel;

Terminate the current switchover to Standby that never completed fully.

select max(sequence#) from v$archived_log where applied='YES';

2) ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN

3) shut immediate

4) startup

alter database open

select database_role from v$database;

select name, open_mode, database_role from v$database;

===============================================startup script===========

$ cat startup.sh

export ORACLE_SID=testdbs3

sqlplus / as sysdba <<EOF

startup mount;

alter database recover managed standby database disconnect from session ;

EOF

export ORACLE_SID=testdbs2

sqlplus / as sysdba <<EOF

startup mount;

alter database recover managed standby database disconnect from session ;

EOF

export ORACLE_SID=testdbs1

sqlplus / as sysdba <<EOF

startup mount;

alter database recover managed standby database disconnect from session ;

EOF

lsnrctl start LISTENER_TESTDB_STB

ps -ef|grep pmon

ps -ef|grep tns

Useful Patching Commands For RAC environment

Oracle 19c Grid/clusterware patching commands

To Relocate Database from One RAC to Another RAC Node

srvctl relocate database -d esr1pcdb -node Easyserver2 -verbose

srvctl relocate database -d esr1pcdb -node Easyserver1 -verbose

srvctl relocate database -d esr2pcdb -node Easyserver2 -verbose

/oracle/esrgrid/19.0.0/crs/install/rootcrs.sh -prepatch

To Rollback patch in RAC Environment Manually

--cd /oracle/software/jdk/

FROM GRID HOME

opatch rollback -id 33197296

FROM RDBMS HOME

opatch rollback -id 33197296 -- rdbms

opatch rollback -id 33197296 -- rdbms (2nd home)

/oracle/esrgrid/19.0.0/rdbms/install/rootadd_rdbms.sh

/oracle/esrgrid/19.0.0/crs/install/rootcrs.sh -postpatch

To Apply patch in RAC Environment using opatchauto

/oracle/esrgrid/19.0.0/OPatch/opatchauto apply /oracle/software/JUL_PSU_GRID/32895426 -analyze

nohup /oracle/esrgrid/19.0.0/OPatch/opatchauto apply /oracle/software/JUL_PSU_GRID/32895426 &

/oracle/esrgrid/19.0.0/OPatch/opatchauto apply /oracle/software/JUL_PSU_GRID/32895426 -analyze

/oracle/esr1pcdb/19.0.0/OPatch/opatchauto apply /oracle/software/JUL_PSU_GRID/32895426 -oh /oracle/esr1pcdb/19.0.0 -analyze

/oracle/esr2pcdb/19.0.0/OPatch/opatchauto apply /oracle/software/JUL_PSU_GRID/32895426 -oh /oracle/esr2pcdb/19.0.0 -analyze

nohup /oracle/esrgrid/19.0.0/OPatch/opatchauto apply /oracle/software/JUL_PSU_GRID/32895426 &

nohup /oracle/esr1pcdb/19.0.0/OPatch/opatchauto apply /oracle/software/JUL_PSU_GRID/32895426 -oh /oracle/esr1pcdb/19.0.0 &

nohup /oracle/esr2pcdb/19.0.0/OPatch/opatchauto apply /oracle/software/JUL_PSU_GRID/32895426 -oh /oracle/esr2pcdb/19.0.0 &

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

To Apply patch in RAC Environment Manually

/oracle/esrgrid/19.0.0/crs/install/rootcrs.sh -prepatch

FROM GRID

cd /oracle/software/OCT_JDK/33197296

opatch apply

FROM RDBMS HOME

cd /oracle/software/OCT_JDK/33197296

opatch apply

opatch apply (2nd home)

From Root

/oracle/esrgrid/19.0.0/crs/rdbms/install/rootadd_rdbms.sh

/oracle/esrgrid/19.0.0/crs/install/rootcrs.sh -postpatch

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

==================Details commands========================

---user equivalance-------------

1) https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=463749886522740&parent=DOCUMENT&sourceId=2464376.1&id=300548.1&_afrWindowMode=0&_adf.ctrl-state=kihwsaqpi_4

2) https://docs.oracle.com/en/database/oracle/oracle-database/18/cwaix/add-all-keys-to-common-authorized-keys-file.html#GUID-6187332B-691C-4221-8B50-E8B46364AD2F

3) https://www.oracle.com/webfolder/community/oracle_database/4318763.html

==Solaris oratab entry==================

cat /var/opt/oracle/oratab

======================================

cd .ssh

ls -ltr

[sppa005:root:/home/root:] ps -ef|grep pmon

oragrid 15204854 1 0 Oct 20 - 4:18 asm_pmon_+ASM1

oradbs 16580982 1 0 Nov 30 - 8:14 ora_pmon_sblprep1

[sppa005:root:/home/root:] ssh

[sppa005:root:/home/root:] su - oragrid

$ cd

$ cd .ssh

$ ls -ltr

total 32

-rw------- 1 oragrid oinstall 397 Aug 05 2019 id_rsa.pub

-rw------- 1 oragrid oinstall 1679 Aug 05 2019 id_rsa

-rw------- 1 oragrid oinstall 795 Aug 08 2019 authorized_keys

-rw-r--r-- 1 oragrid oinstall 1070 Dec 09 10:34 known_hosts

$ cat id_rsa.pub

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQCxPgMZfAsieBn5VVD6Jd6NDTdrV9CWGsldBm6LOyng6So4gVlcaQyw8dyf7o5OUcl+Tk/IyoVjMRafyTzKQZNMoa4jMEAdfhOG9ZMDRkhHGwpHTC69f1OMVg+GHVn7uzMkoA4a62puf53hLSph2HP8gn45Xs8ZdXEsAUlWdnJfDyjrzefZqL6qxFQ2qmyYpnklYa2IL2kaA62tORn3Jm/ljTilQs+0N0xrhFcM++CBJea5dy60dFqMFy0lnRuFTmtBc0jZAHjdZm15G87egByBE3K0TuaaVMLtmZ/f9l5f//ZxCkSBZtj0XBX4Q7RZ+nINlUgOc/guHO3ZP8ONILKx oragrid@sppa005

$ exit

[sppa005:root:/home/root:] su - oradbs

[YOU HAVE NEW MAIL]

$ cd ..

$ cd

$ cd .ssh

$ exit

[sppa005:root:/home/root:] su - oradbs

[YOU HAVE NEW MAIL]

$ cd

$ cd .ssh

$ ls -ltr

total 32

-rw------- 1 oradbs oinstall 396 Aug 05 2019 id_rsa.pub

-rw------- 1 oradbs oinstall 1679 Aug 05 2019 id_rsa

-rw------- 1 oradbs oinstall 1600 Sep 04 2020 authorized_keys

-rw-r--r-- 1 oradbs oinstall 1255 Jan 18 13:35 known_hosts

$ cat id_rsa.pub

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDYLu1ECJJ0TfdYQDamY9wC6rELBSUM7E7tWxTJYuINoFe2BPr6pOIuk4eZs/WKRYIFWc3TDIE0k+xVwffpBXNQJRax/G7MqRXfYB756HfaG+Oh50m1M4R3Cojp/n5smXoheYmGqqkoLk5qBsCFlLYgMkmllpo00oRJ7OSN2gglldof5/ivB/KR5salHOeyeSCDbTN09T/3YIp7+gAhgq8MQR9azKr3RlaTcYTj5WQwvOqmp1c3RzNOb3PfZLS7gAs42wPMEgyMO3CV9PCDrh4TMPpZxiHF4boD/AvvkWhK2Aa2Rpcnn/OCOumydGbqy1Xu1sHBJSIv2PVWc1tFmVxp oradbs@sppa005

If multiple database running one one server . We need to set profile for particular standby Database as below

. ./.profile_esr2pcdb

sqlplus / as sysdba

sql> startup

alter database recover managed standby database disconnect using current logfile;

To Verify port

telnet <server name> 22

To clean patch related log files

opatch util cleanup

To check free space on server

du -sh*

if we have bug on database ,we can apply patch as below

You need to apply the patch as in below sequence.

a) Apply July PSU

b) Apply bug fix patch

The bug fix patch link is available in SR à https://updates.oracle.com/Orion/Services/download/p29930457_12102210720_AIX64-5L.zip?aru=24516785&patch_file=p29930457_12102210720_AIX64-5L.zip

==================

To search profile on server

ls -la .prof*

./.profile_arsdcdb

. ./.profile_esr1pcdb_1

Environment setting

export ORACLE_BASE=/oracle/cmrprod

export ORACLE_HOME=/oracle/cmrprod/12.1.0.2

export ORACLE_SID=cmrprod1

export OPATCH=/oracle/cmrprod/12.1.0.2/OPatch

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:/usr/ccs/bin:/usr/bin:/usr/local/bin:/usr/local/sbin:/usr

export ORACLE_BASE=/oracle/oragrid

export ORACLE_HOME=/oracle/dbugrid/12.1.0.2

export ORACLE_SID=+ASM1

export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch

export ORACLE_BASE=/oracle/dbutest

export ORACLE_HOME=/oracle/dbutest/12.1.0.2

export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch

To Transfer files from one server to another server , We need to use scp os command

scp p6880880_122010_AIX64-5L.zip cpyuser@stsa003:<location>

scp p6880880_122010_AIX64-5L.zip cpyuser@stsa004:<location>

scp /oracle/oracle-patches/19.12.0.0.210720/p32895426_190000_SOLARIS64.zip cpyuser@spps019:/oracle/softwares/

To Take Backup for inventory

cd /oracle/oraInventory

tar -cvf /oracle/softwares/grid_home_inventory.tar .

du -hP /oracle/softwares/grid_home_inventory.tar

To stop/start instance on one Node

srvctl stop instance -d sblprep -i sblprep1 -o immediate

srvctl start instance -d dbutest -i dbutest1

To stop/start instance on other Node

srvctl stop instance -d dbutest -i dbutest2 -o immediate

srvctl start instance -d dbutest -i dbutest2

srvctl stop instance -d dspscdb -i dspscdb2 -o immediate

srvctl start instance -d dspscdb -i dspscdb2

To Check Database status in RAC Environment

$ srvctl status database -d dbutest

Instance dbutest1 is running on node stsa003

Instance dbutest2 is running on node stsa004

Apply patch in RAC Environment

Pre-requisite

To validate inventory and update new opatch version

oragrid@spps018:/oracle/dspsgrid/19.0.0/OPatch$ opatch lsinventory

Oracle Interim Patch Installer version 12.2.0.1.25

Oracle Home : /oracle/dspsgrid/19.0.0

Central Inventory : /oracle/oraInventory

from : /oracle/dspsgrid/19.0.0/oraInst.loc

cd /oracle/sblppgrid/12.1.0.2/

mv OPatch OPatch_old_jan

cp /oracle/software/octru/p6880880_122010_AIX64-5L.zip /oracle/sblppgrid/12.1.0.2/

cp /oracle/software/octru/p6880880_122010_AIX64-5L.zip /oracle/sblprep/12.1.0.2/

unzip 6880880_122010_AIX64-5L.zip

rm 6880880_122010_AIX64-5L.zip

chown -R oragrid:oinstall OPatch

chown -R oradbs:oinstall OPatch

chmod -R 775 OPatch

chown -R oragrid:oinstall softwares

chown oragrid:oinstall software

chown -R oradbs:oinstall OPatch

chmod 775 OPatch

chmod -R 775 32895426

chmod -R 775 32917447

chmod -R 775 32928749

chmod -R 775 octru

chown -R oragrid:oinstall octru

chmod -R 775 octru

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

from root user

ps -ef|grep pmon

cd /oracle/dspsgrid/19.0.0/bin

./crsctl stop crs

./crsctl stop has

cd /oracle/oraInventory

tar -cvf /oracle/software/grid_home_inventory.tar .

du -hP /oracle/softwares/grid_home_inventory.tar

./crsctl start crs

./crsctl start has

export PATH=$PATH:/oracle/dspsgrid/19.0.0/OPatch

cd /oracle/dspsgrid/19.0.0/OPatch

df -hk /oracle/dspsgrid/19.0.0/OPatch

df -hk /tmp

cd /oracle/softwares/32895426

./opatch version

ps -ef|grep pmon

/oracle/dspsgrid/19.0.0/OPatch/opatchauto apply /oracle/softwares/32895426 -analyze -oh /oracle/dspsgrid/19.0.0

implementation

nohup /oracle/dspsgrid/19.0.0/OPatch/opatchauto apply /oracle/softwares/32895426 -oh /oracle/dspsgrid/19.0.0 &

=====Database patch=============================

export PATH=$PATH:/oracle/dspscdb/12.2.0.1/OPatch

cd /oracle/dspscdb/12.2.0.1/OPatch

df -hP /oracle/dspscdb/12.2.0.1/OPatch

df -hP /tmp

cd /oracle/softwares/32928749

./opatch version

ps -ef|grep pmon

/oracle/dspscdb/12.2.0.1/OPatch/opatchauto apply /oracle/softwares/32928749 -analyze -oh /oracle/dspscdb/12.2.0.1

-------implementation---------

nohup /oracle/dspscdb/12.2.0.1/OPatch/opatchauto apply /oracle/softwares/32928749 -oh /oracle/dspscdb/12.2.0.1 &

-------post patch---

./opatch lsinv|grep applied

Patch 33112931 : applied on Wed Jan 12 07:33:21 GMT+03:00 2022

Patch 32768233 : applied on Wed Jan 12 07:32:14 GMT+03:00 2022

Patch 32758932 : applied on Wed Jan 12 07:30:39 GMT+03:00 2022

Patch 26983807 : applied on Fri Apr 02 20:26:22 GMT+03:00 2021

$ ./opatch lspatches

33112931;ACFS PATCH SET UPDATE 12.1.0.2.210720 (33112931)

32768233;Database Patch Set Update : 12.1.0.2.210720 (32768233)

32758932;OCW PATCH SET UPDATE 12.1.0.2.210720 (32758932)

26983807;WLM Patch Set Update: 12.1.0.2.180116 (26983807)

oragrid@spps019:~$

oragrid@spps019:~$ $ORACLE_HOME/jdk/jre/bin/java -version

java version "1.8.0_281"

Java(TM) SE Runtime Environment (build 1.8.0_281-b09)

Java HotSpot(TM) 64-Bit Server VM (build 25.281-b09, mixed mode)

===========JDK=====================

p33197296_190000_Linux-x86-64.zip --19c JDK patch -oct 2021

p33197448_122010_SOLARIS64.zip ---12.2 JDK Patch - oct 2021

Patch 33497160: JDK BUNDLE PATCH 19.0.0.0.220118 -- jan2022

p33497187_122010_SOLARIS64.zip -- jan2022

Patch 33497195: JDK BUNDLE PATCH 12.1.0.2.220118 -- Jan2022

To find file uisng OS command

find . -name 33497195.* 2>/dev/null

/oracle/JDK_Patches/JDK_Patches_Jan2022

[oracle@mprl500 JDK_Patches_Jan2022]$ ls -lrt

total 555584

-rwxrwxr-x 1 oracle dba 127073150 Jan 18 12:53 p33497160_190000_Linux-x86-64.zip

-rwxrwxr-x 1 oracle dba 79653082 Jan 21 16:27 p33497160_190000_SOLARIS64.zip

-rwxrwxr-x 1 oracle dba 79620448 Jan 21 19:45 p33497187_122010_SOLARIS64.zip

-rwxrwxr-x 1 oracle dba 97008076 Jan 21 21:32 p33497195_121020_SOLARIS64.zip

-rwxrwxr-x 1 oracle dba 58513219 Jan 21 21:54 p33497195_121020_Linux-x86-64.zip

-rwxrwxr-x 1 oracle dba 127038899 Jan 22 15:46 p33497187_122010_Linux-x86-64.zip

[oracle@mprl500 JDK_Patches_Jan2022]$

-----------grid----------------

oragrid@spps019:~$

oragrid@spps019:~$ $ORACLE_HOME/jdk/jre/bin/java -version

java version "1.8.0_281"

Java(TM) SE Runtime Environment (build 1.8.0_281-b09)

Java HotSpot(TM) 64-Bit Server VM (build 25.281-b09, mixed mode)

oragrid@spps019:~$

-----------oracle home--------

oradbs@spps019:~$

oradbs@spps019:~$ $ORACLE_HOME/jdk/jre/bin/java -version

java version "1.8.0_291"

Java(TM) SE Runtime Environment (build 1.8.0_291-b09)

Java HotSpot(TM) 64-Bit Server VM (build 25.291-b09, mixed mode)

oradbs@spps019:~$

$ORACLE_HOME/jdk/jre/bin/java -version

cd /oracle/software/oct_jdk/33197296

opatch apply

$ORACLE_HOME/jdk/jre/bin/java -version

Apply JDK patch Manually on Grid and Oracle RAC Home

jdk patch-- p33197296_190000_SOLARIS64.zip -- 19c

jdk patch -- p33197448_122010_SOLARIS64.zip -- 12.d

stop database instance

/oracle/esrgrid/19.0.0/crs/install/rootcrs.sh -prepatch

From grid

Cd /oracle/softwares/33197296

Opatch apply

From rdbms home

Cd /oracle/softwares/33197448

Opatch apply

Opatch apply (2nd home)

From root

/oracle/esrgrid/19.0.0/rdbms/install/rootadd_rdbms.sh

/oracle/esrgrid/19.0.0/rdbms/install/rootcrs.sh -postpatch

==========clusterware details============

export ORACLE_BASE=/oracle/oragrid

export ORACLE_HOME=/oracle/frdgrid/19.0.0

export ORACLE_SID=+ASM

export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:/usr/ccs/bin

root@spps019:/oracle# export PATH=$PATH:/oracle/dspsgrid/19.0.0/OPatch

root@spps019:/oracle# cd /oracle/dspsgrid/19.0.0/OPatch

root@spps019:/oracle/dspsgrid/19.0.0/OPatch# df -hk /oracle/dspsgrid/19.0.0/OPatch

Filesystem Size Used Available Capacity Mounted on

orabinpool/orabin 147G 117G 29.5G 80% /oracle

root@spps019:/oracle/dspsgrid/19.0.0/OPatch# df -hk /tmp

Filesystem Size Used Available Capacity Mounted on

swap 95.7G 39.9M 95.7G 1% /tmp

root@spps019:/oracle/dspsgrid/19.0.0/OPatch# cd /oracle/softwares/32895426

root@spps019:/oracle/softwares/32895426# ./opatch version

-bash: ./opatch: No such file or directory

root@spps019:/oracle/softwares/32895426# opatch version

OPatch Version: 12.2.0.1.27

OPatch succeeded.

root@spps019:/oracle/softwares/32895426# id

uid=0(root) gid=0(root)

root@spps019:/oracle/softwares/32895426# /oracle/dspsgrid/19.0.0/OPatch/opatchauto apply /oracle/softwares/32895426 -analyze -oh /oracle/dspsgrid/19.0.0

OPatchauto session is initiated at Wed Jan 12 12:24:09 2022

System initialization log file is /oracle/dspsgrid/19.0.0/cfgtoollogs/opatchautodb/systemconfig2022-01-12_12-24-16PM.log.

Session log file is /oracle/dspsgrid/19.0.0/cfgtoollogs/opatchauto/opatchauto2022-01-12_12-24-35PM.log

The id for this session is IJIS

Executing OPatch prereq operations to verify patch applicability on home /oracle/dspsgrid/19.0.0

Patch applicability verified successfully on home /oracle/dspsgrid/19.0.0

Executing patch validation checks on home /oracle/dspsgrid/19.0.0

Patch validation checks successfully completed on home /oracle/dspsgrid/19.0.0

OPatchAuto successful.

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

Analysis for applying patches has completed successfully:

Host:spps019

CRS Home:/oracle/dspsgrid/19.0.0

Version:19.0.0.0.0

==Following patches were SKIPPED:

Patch: /oracle/softwares/32895426/32585572

Log: /oracle/dspsgrid/19.0.0/cfgtoollogs/opatchauto/core/opatch/opatch2022-01-12_12-25-08PM_1.log

Reason: /oracle/softwares/32895426/32585572 is not required to be applied to oracle home /oracle/dspsgrid/19.0.0

==Following patches were SUCCESSFULLY analyzed to be applied:

Patch: /oracle/softwares/32895426/32916816

Log: /oracle/dspsgrid/19.0.0/cfgtoollogs/opatchauto/core/opatch/opatch2022-01-12_12-25-08PM_1.log

Patch: /oracle/softwares/32895426/32915586

Log: /oracle/dspsgrid/19.0.0/cfgtoollogs/opatchauto/core/opatch/opatch2022-01-12_12-25-08PM_1.log

Patch: /oracle/softwares/32895426/32918050

Log: /oracle/dspsgrid/19.0.0/cfgtoollogs/opatchauto/core/opatch/opatch2022-01-12_12-25-08PM_1.log

Patch: /oracle/softwares/32895426/32904851

Log: /oracle/dspsgrid/19.0.0/cfgtoollogs/opatchauto/core/opatch/opatch2022-01-12_12-25-08PM_1.log

OPatchauto session completed at Wed Jan 12 12:27:53 2022

Time taken to complete the session 3 minutes, 45 seco

================Database =========

root@spps019:/oracle/softwares/32928749# ps -ef|grep pmon

oragrid 5260 1 0 Dec 02 ? 2:52 asm_pmon_+ASM2

oragrid 7491 1 0 Dec 02 ? 2:54 mdb_pmon_-MGMTDB

oradbs 8720 1 0 Dec 02 ? 6:32 ora_pmon_dspscdb2

root 16203 9049 0 12:37:38 pts/1 0:00 grep pmon

root@spps019:/oracle/softwares/32928749# /oracle/dspscdb/12.2.0.1/OPatch/opatchauto apply /oracle/softwares/32928749 -analyze -oh /oracle/dspscdb/12.2.0.1

OPatchauto session is initiated at Wed Jan 12 12:38:05 2022

System initialization log file is /oracle/dspscdb/12.2.0.1/cfgtoollogs/opatchautodb/systemconfig2022-01-12_12-38-39PM.log.

Session log file is /oracle/dspscdb/12.2.0.1/cfgtoollogs/opatchauto/opatchauto2022-01-12_12-39-32PM.log

The id for this session is 855M

Executing OPatch prereq operations to verify patch applicability on home /oracle/dspscdb/12.2.0.1

Patch applicability verified successfully on home /oracle/dspscdb/12.2.0.1

Executing patch validation checks on home /oracle/dspscdb/12.2.0.1

Patch validation checks successfully completed on home /oracle/dspscdb/12.2.0.1

Verifying SQL patch applicability on home /oracle/dspscdb/12.2.0.1

SQL patch applicability verified successfully on home /oracle/dspscdb/12.2.0.1

OPatchAuto successful.

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

Analysis for applying patches has completed successfully:

Host:spps019

RAC Home:/oracle/dspscdb/12.2.0.1

Version:12.2.0.1.0

==Following patches were SKIPPED:

Patch: /oracle/softwares/32928749/33116894

Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /oracle/softwares/32928749/26839277

Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /oracle/softwares/32928749/32918082

Reason: This patch is not applicable to this specified target type - "rac_database"

==Following patches were SUCCESSFULLY analyzed to be applied:

Patch: /oracle/softwares/32928749/31802727

Log: /oracle/dspscdb/12.2.0.1/cfgtoollogs/opatchauto/core/opatch/opatch2022-01-12_12-39-57PM_1.log

Patch: /oracle/softwares/32928749/32916808

Log: /oracle/dspscdb/12.2.0.1/cfgtoollogs/opatchauto/core/opatch/opatch2022-01-12_12-39-57PM_1.log

OPatchauto session completed at Wed Jan 12 12:43:31 2022

Time taken to complete the session 5 minutes, 28 seconds

root@spps019:/oracle/softwares/32928749#

============post implementation========

col action_time format a30;

col version format a13;

col description format a60;

set lin 200 head on feed on pagesize 100;

select patch_id, action_time, status, description from dba_registry_sqlpatch;

SQL> SQL> SQL> SQL>

PATCH_ID ACTION_TIME STATUS DESCRIPTION

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

30138470 13-JAN-20 04.02.13.478092 PM SUCCESS DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015

30886680 30-AUG-20 09.08.50.762806 PM SUCCESS DATABASE APR 2020 RELEASE UPDATE 12.2.0.1.200414

31741641 05-FEB-21 01.42.24.604744 AM SUCCESS DATABASE OCT 2020 RELEASE UPDATE 12.2.0.1.201020

32228578 03-MAY-21 06.39.13.225367 PM SUCCESS DATABASE JAN 2021 RELEASE UPDATE 12.2.0.1.210119

32507738 23-JUN-21 12.23.32.923495 AM SUCCESS DATABASE APR 2021 RELEASE UPDATE 12.2.0.1.210420

5 rows selected.

==================jkp patch log==============

Verifying environment and performing prerequisite checks...

OPatch continues with these patches: 33197448Do you want to proceed? [y|n]

User Responded with: Y

All checks passed.

Backing up files...

Applying interim patch '33197448' to OH '/oracle/dspscdb/12.2.0.1'Patching component oracle.jdk, 1.8.0.91.0...

Patch 33197448 successfully applied.

Log file location: /oracle/dspscdb/12.2.0.1/cfgtoollogs/opatch/opatch2022-01-12_19-42-05PM_1.logOPatch succeeded.

Verifying environment and performing prerequisite checks...

OPatch continues with these patches: 33197296Do you want to proceed? [y|n]

User Responded with: Y

All checks passed.

Backing up files...

Applying interim patch '33197296' to OH '/oracle/dspsgrid/19.0.0'

ApplySession: Optional component(s) [ oracle.jdk, 1.8.0.191.0 ] not present in the Oracle Home or a higher version is found.Patching component oracle.jdk, 1.8.0.201.0...

Patch 33197296 successfully applied.

Log file location: /oracle/dspsgrid/19.0.0/cfgtoollogs/opatch/opatch2022-01-12_19-35-38PM_1.logOPatch succeeded.

==================================

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Log file location: /oracle/sgwtest/19.0.0/cfgtoollogs/opatch/opatch2022-01-22_12-23-53PM_1.log

OPatch failed with error code 73

oradbs@sdes028:…SU_JULY_2021/32895426/32904851$ fuser /oracle/sgwtest/19.0.0/lib/libclntsh.so.19.1

/oracle/sgwtest/19.0.0/lib/libclntsh.so.19.1: 8011m

oradbs@sdes028:…SU_JULY_2021/32895426/32904851$ fuserr

oradbs@sdes028:…SU_JULY_2021/32895426/32904851$ fuser /oracle/sgwtest/19.0.0/bin/rman

/oracle/sgwtest/19.0.0/bin/rman: 8011tm

oradbs@sdes028:…SU_JULY_2021/32895426/32904851$ ps -ef|grep -i 8011tm

oradbs 8822 6692 0 12:28:45 pts/6 0:00 grep -i 8011tm

oradbs@sdes028:…SU_JULY_2021/32895426/32904851$ ps -ef|grep -i 8011

To Rollback JDK patch in single instance

1) shutdown database

2)/oracle/software/PSU_JULY_2021/32895426/32904851

3) opatch rollback -id 32904851

4) start database and run

cd $ORACLE_HOME/OPatch

./datapatch -verbose

3)/oracle/software/PSU_JULY_2021/32895426/32916816

opatch rollback -id 32916816

start database

==================================

This is single node instance running on RAC, CRS is process is not running ,Only HA process is running

We need to apply RAC patch on Grid home and database . During GRID Home patch ,ASM should be up and running

And during database patch , database need to shutdown during auto patch apply and start database and apply sqlpatch manually

export ORACLE_SID=sgwtest

ORACLE_BASE=/oracle/sgwtest

ORACLE_HOME=/oracle/sgwtest/19.0.0

export ORACLE_BASE

export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH

export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib

export LD_LIBRARY_PATH

OPATCH=/oracle/sgwtest/19.0.0/OPatch

PATH=$PATH:/oracle/sgwtest/19.0.0/OPatch

/oracle/sgwtest/19.0.0/OPatch/opatchauto apply /oracle/software/PSU_JULY_2021/32895426 -analyze -oh /oracle/sgwtest/19.0.0

nohup /oracle/sgwtest/19.0.0/OPatch/opatchauto apply /oracle/software/PSU_JULY_2021/32895426 -oh /oracle/sgwtest/19.0.0 &

=======January 2022 JDK patch=============

Patch 33518559

Simple Search Advanced Search Quick Links Saved Searches

Description Oracle SERVER JRE 8 Update 321

Product Java SE

Release

Oracle JDK 1.8.0_321

Platform or Language Click for more information about this option

Oracle Solaris on SPARC (64-bit)

Last Updated 18-JAN-2022

Size 64M (67582396 bytes)

Entitlement Class Click for more information about this option Software

Classification Click for more information about this option General

========================service stop============

srvctl stop instance -d cmrprod -i cmrprod1 -o immediate

$ ora_pmon_sblprep1

$ srvctl stop instance -d sblprep -i sblprep1 -o immediate

PRCD-1315 : failed to stop instances for database sblprep

PRCR-1014 : Failed to stop resource ora.sblprep.db

PRCR-1065 : Failed to stop resource ora.sblprep.db

CRS-2529: Unable to act on 'ora.sblprep.db' because that would require stopping or relocating 'ora.sblprep.sblsrvc_bms.svc', but the force option was not specified

$ crsctl

$ srvctl status database -d sblprep

Instance sblprep1 is running on node sppa005

Instance sblprep2 is running on node sppa006

$ srvctl status service -d sblprep

Service sblsrvc_bms is running on instance(s) sblprep1,sblprep2

Service sblsrvc_sbl is running on instance(s) sblprep1,sblprep2

Service sblugd is running on instance(s) sblprep1,sblprep2

srvctl stop service -db sblprep -service sblsrvc_bms -node sppa006

srvctl stop instance -d sblprep -i sblprep1

srvctl start instance -d sblprep -i sblprep2

srvctl stop instance -d sblprep -i sblprep2 -force

To list crontab job

$ crontab -l

=================relocate service==========

oragrid@mprs099:~$ srvctl status service -d rfmprod

Service rocfm_serv is running on instance(s) rfm1

Service rocfm_serv1 is running on instance(s) rfm1

oragrid@mprs099:~$

Now relocate service from orcl1 to orcl2

[oracle@rac1 ~]$ srvctl relocate service -d orcl -s myservice -i orcl1 -t orcl2

-i represents an old instance

-t represents a new instance

Service rocfm_serv is running on instance(s) rfm2

Service rocfm_serv1 is running on instance(s) rfm2

oradbs@mprs099:~$ srvctl status service -d rfmprod

Service rocfm_serv is running on instance(s) rfm1

Service rocfm_serv1 is running on instance(s) rfm2

srvctl relocate service -d rfmprod -s rocfm_serv1 -i rfmprod1 -t rfmprod2 -f

srvctl relocate service -d rfmprod -s rocfm_serv -i rfmprod1 -t rfmprod2 -f

srvctl relocate service -d rfmprod -s rocfm_serv1 -i rfmprod2 -t rfmprod1 -f

srvctl relocate service -d rfmprod -s rocfm_serv -i rfmprod2 -t rfmprod1 -f

oragrid@mprs099:~$ ps -ef|grep pmon

oradbs 7482 1 0 Feb 08 ? 27:54 ora_pmon_rfm1

oragrid 1642 1 0 Feb 08 ? 1:40 asm_pmon_+ASM1

oragrid 22678 22049 0 04:52:26 pts/3 0:00 grep pmon

oragrid@mprs099:~$ srvctl status service -d rfmprod

Service rocfm_serv is running on instance(s) rfm1

Service rocfm_serv1 is running on instance(s) rfm1

oragrid@mprs099:~$ srvctl relocate service -d rfmprod -s rocfm_serv1 -i rfmprod1 -t rfmprod2 -f

PRKO-2107 : Failed to find an instance rfmprod1 for database rfmprod

oragrid@mprs099:~$ srvctl relocate service -d rfmprod -s rocfm_serv1 -i rfm1 -t rfm2 -f

oragrid@mprs099:~$ srvctl status service -d rfmprod

Service rocfm_serv is running on instance(s) rfm1

Service rocfm_serv1 is running on instance(s) rfm2

oragrid@mprs099:~$

================12.1 manual DB patching==============

/oracle/software/JUL2021/32917447/32758932/custom/scripts/prepatch.sh -dbhome /oracle/sblprod/12.1.0.2

nohup /oracle/sblprod/12.1.0.2/OPatch/opatch apply -oh /oracle/sblprod/12.1.0.2 -local /oracle/software/JUL2021/32917447/32758932 -silent &

nohup /oracle/sblprod/12.1.0.2/OPatch/opatch apply -oh /oracle/sblprod/12.1.0.2 -local /oracle/software/JUL2021/32917447/32768233 -silent &

/oracle/software/JUL2021/32917447/32758932/custom/scripts/postpatch.sh -dbhome /oracle/sblprod/12.1.0.2

========================

afddriverstate version

afddriverstate supported

afdroot install

afdload start

asmcmd afd_lsdsk

afddriverstate version

afddriverstate supported

/oracle/ctlgrid/19.0.0/bin/afdroot install

/oracle/ctlgrid/19.0.0/bin/afdload start

asmcmd afd_lsdsk

======================

oradbs@mprs104:~$ cd .

oradbs@mprs104:~$ cd

oradbs@mprs104:~$ ls -ltr .prof*

-rw-r--r-- 1 oradbs oinstall 568 Apr 23 2020 .profile

-rw-r--r-- 1 oradbs oinstall 841 Oct 21 2020 .profile_lpprod_1

-rw-r--r-- 1 oradbs oinstall 847 Oct 30 2020 .profile_lpprod_1_11

-rw-r--r-- 1 oradbs oinstall 841 Oct 30 2020 .profile_lpprod_2

-rw-r--r-- 1 oradbs oinstall 846 Nov 10 2020 .profile_repprod_1_19

-rw-r--r-- 1 oradbs oinstall 846 Nov 28 2020 .profile_repprod_1

-rw-r--r-- 1 oradbs oinstall 846 Nov 28 2020 .profile_repprod_2

-rw-r--r-- 1 oradbs oinstall 841 Dec 12 2020 .profile_lpprod

-rw-r--r-- 1 oradbs oinstall 846 Jan 9 2021 .profile_repprod

-rw-r--r-- 1 oradbs oinstall 657 Dec 14 13:04 .profile_Agent13

oradbs@mprs104:~$ ./.profile_repprod_2^C

oradbs@mprs104:~$

oradbs@mprs104:~$ ./. .profile_repprod_2

-bash: ./.: Is a directory

oradbs@mprs104:~$ ./^C

oradbs@mprs104:~$

oradbs@mprs104:~$ . ./.profile_repprod_2

oradbs@mprs104:~$

oradbs@mprs104:~$ srvctl relocate database -d repprodn -node mprs105 -verbose

Configuration updated to two instances

srvctl relocate database -d repprodn -node mprs104 -verbose

oradbs@mprs104:~$ . ./.profile_lpprod_1

oradbs@mprs104:~$

oradbs@mprs104:~$ srvctl relocate database -d lpprodn -node mprs105 -verbose

Configuration updated to two instances

Instance lpprod_2 started

Services relocated

Waiting for up to 30 minutes for instance lpprod_1 to stop ...

Instance lpprod_1 stopped

Configuration updated to one instance

You have new mail in /var/mail/oradbs

srvctl relocate database -d lpprodn -node mprs104 -verbose

oradbs@mprs104:~$ ./. .profile_repprod_2

-bash: ./.: Is a directory

oradbs@mprs104:~$

oradbs@mprs104:~$ . ./.profile_repprod_2

oradbs@mprs104:~$ srvctl relocate database -d repprodn -node mprs104 -verbose

Configuration updated to two instances

Instance repprod_2 started

Services relocated

Waiting for up to 30 minutes for instance repprod_1 to stop ...

Instance repprod_1 stopped

Configuration updated to one instance

oradbs@mprs104:~$

oradbs@mprs104:~$ . ./.profile_lpprod_1

oradbs@mprs104:~$

oradbs@mprs104:~$ srvctl relocate database -d lpprodn -node mprs104 -verbose

Configuration updated to two instances

Instance lpprod_1 started

Services relocated

Waiting for up to 30 minutes for instance lpprod_2 to stop ...

Instance lpprod_2 stopped

Configuration updated to one instance

oradbs@mprs104:~$

===============

==Following patches were SUCCESSFULLY applied:

Patch: /tmp/octru/32895426/32904851

Log: /oracle/lpprod/19.0.0/cfgtoollogs/opatchauto/core/opatch/opatch2022-02-13_06-21-17AM_1.log

Patch: /tmp/octru/32895426/32916816

Log: /oracle/lpprod/19.0.0/cfgtoollogs/opatchauto/core/opatch/opatch2022-02-13_06-21-17AM_1.log

Patching session reported following warning(s):

_________________________________________________

[WARNING] This is a RACOne Database. After completing patching on all the nodes, apply the datapatch step manually on the node where database instance is running. Please refer to the log file for more details.

To find out the node where database instance is running, execute the following command,

$ORACLE_HOME/bin/srvctl status database -d LPPRODN

OPatchauto session completed at Sun Feb 13 06:31:59 2022

Time taken to complete the session 12 minutes, 41 seconds

=====################=============

Jan JDK in single instance

[oracle@eecsaruh2hor002:/tmp/jdkjan] cd

[oracle@eecsaruh2hor002:/tmp/jdkjan] ls -ltr

total 243484

drwxrwxr-x 4 oracle oinstall 67 Dec 22 21:34 33497160

-rw-rw-r-- 1 oracle oinstall 2475 Jan 18 12:52 PatchSearch.xml

-rwxr-x--- 1 oracle oinstall 127073150 Feb 15 17:39 p33497160_190000_Linux-x86-64.zip

-rwxr-x--- 1 oracle oinstall 122247289 Feb 15 17:40 p6880880_122010_Linux-x86-64.zip

[oracle@eecsaruh2hor002:/tmp/jdkjan] cd 33497160

[oracle@eecsaruh2hor002:/tmp/jdkjan/33497160] /dbfiles/app/oracle/product/19.0.0/OPatch/opatch version

OPatch Version: 12.2.0.1.28

OPatch succeeded.

[oracle@eecsaruh2hor002:/tmp/jdkjan/33497160] /dbfiles/app/oracle/product/19.0.0/OPatch/opatch apply

Oracle Interim Patch Installer version 12.2.0.1.28

Oracle Home : /dbfiles/app/oracle/product/19.0.0

Central Inventory : /dbfiles/app/product/oraInventory

from : /dbfiles/app/oracle/product/19.0.0/oraInst.loc

OPatch version : 12.2.0.1.28

OUI version : 12.2.0.7.0

Log file location : /dbfiles/app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2022-02-15_19-40-02PM_1.log

Verifying environment and performing prerequisite checks...

=========JDK patch apply==single instance========

33497195

./opatch lsinv|grep applied|grep -i 33497195

1) check opatch version

/product/oracle/19.0.0/OPatch/opatch version

$ORACLE_HOME/OPatch/opatch version

2) conflict patch

oradbs@mprs106:…e/software/JULYPSU/db/32904851$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.28

3) apply patch

/product/oracle/19.0.0/OPatch/opatch apply

4) jdk version

oragrid@spps019:~$

[oracle@eecsaruh2hor022:/var/tmp/janjdk/33497160] $ORACLE_HOME/jdk/jre/bin/java -version

java version "1.8.0_321"

Java(TM) SE Runtime Environment (build 1.8.0_321-b07)

Java HotSpot(TM) 64-Bit Server VM (build 25.321-b07, mixed mode)

[oracle@eecsaruh2hor022:/var/tmp/janjdk/33497160]

To relocate Database in Oracle Restart Enviroment

oradbs@sprs029:~$

oradbs@sprs029:~$ echo $ORACLE_HOME

oradbs@sprs029:~$ ps -ef|grep pmon

oragrid 16227 1 0 Feb 12 ? 0:50 asm_pmon_+ASM1

oradbs 12325 1 0 Feb 12 ? 2:08 ora_pmon_inprod_2

oragrid 18848 1 0 Feb 12 ? 0:49 mdb_pmon_-MGMTDB

oradbs 25123 23854 0 05:49:30 pts/5 0:00 grep pmon

oradbs@sprs029:~$ source .profile_

-ksh: .: .profile_: cannot open [No such file or directory]

oradbs@sprs029:~$ ls -ltr .pr

^C.pr: No such file or directory

oradbs@sprs029:~$ bash

oradbs@sprs029:~$ source .profile_

.profile_Agent13c .profile_inprod_1 .profile_inprod_2 .profile_ocsprod_1_11 .profile_ocsprod_11204 .profile_ocsprod_2_11

.profile_inprod .profile_inprod_1_19 .profile_ocsprod_1 .profile_ocsprod_11203 .profile_ocsprod_2

oradbs@sprs029:~$ source .profile_inprod_1

oradbs@sprs029:~$ echo $ORACLE_HOME

/oracle/inprod/19.0.0

oradbs@sprs029:~$ srvctl status database -d inprod

PRCD-1120 : The resource for database inprod could not be found.

PRCR-1001 : Resource ora.inprod.db does not exist

oradbs@sprs029:~$ srvctl status database -d inprodn

Instance inprod_2 is running on node sprs029

Online relocation: INACTIVE

oradbs@sprs029:~$ srvctl relocate database -d inprodn -v -n sprs030

Configuration updated to two instances

Instance inprod_1 started

Services relocated

Waiting for up to 30 minutes for instance inprod_2 to stop ...

Instance inprod_2 stopped

Configuration updated to one instance

You have new mail in /var/mail/oradbs

oradbs@sprs029:~$

=====================jdk for agent==AHF##############################

Once you apply the VA fix patch (ex: OCT 33197296) the JDK will be updated with latest one

[oradbs@mprl219 bin]$ /oracle/eoc1pcdb/19.0.0/jdk/jre/bin/java -version

java version "1.8.0_311"

Java(TM) SE Runtime Environment (build 1.8.0_311-b11)

Java HotSpot(TM) 64-Bit Server VM (build 25.311-b11, mixed mode)

After apply Java Patch now fix the Java version for Agent13c and AHF

1. for Agent13c

Replace below directory with DB ORACLE_HOME jdk directory

cd /oracle/Agent13c/agent_13.4.0.0.0/oracle_common

rm -rf jdk

cp -r /oracle/eoc1pcdb/19.0.0/jdk .

cp -r /oracle1/mwfparch/19.0.0/jdk .

cp -r /oracle1/mwfpgis/19.0.0/jdk .

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

2. For AHF

cd /opt/oracle.ahf

rm -rf jre

cp -r /oracle/eoc1pcdb/19.0.0/jdk .

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

You can follow the same for OPatch if the version is not up to date.

/opt/oracle.ahf/jre/bin/java -version

cp /tmp/AHF-SOLARIS.SPARC64_v21.4.1.zip /oracle/software/ahf/

unzip AHF-SOLARIS.SPARC64_v21.4.1.zip

chmod -R 775 ahf

cd ahf

./ahf_setup -ahf_loc /opt/oracle.ahf

RAC database upgrade from 11.2.0.3 to 11.2.0.4

RAC DATABASE UPGRADE

Check Oracle home mount point free space (approx 30 GB) and database size and backup configuration

select sum(bytes/1024/1024/1024) from dba_segments;

select sum(bytes/1024/1024/1024) from dba_data_files;

1). Download the Patchset 13390677 from My Oracle support

https://updates.oracle.com/download/13390677.html

There are 7 .zip files, to install Oracle db server download 1of7 and 2of7 -->>>>>>> are for database

2). Transfer both files in both nodes and Unzip the files in the same directory (do not create separate directories for each zip file)

[Node1:fusppt:install] ls -ltr

drwxr-xr-x 8 fusppt dba 4096 Oct 9 2013 database

-rwxrwxrwx 1 fusppt dba 1170882875 Jan 5 12:35 p13390677_112040_AIX64-5L_2of7.zip

-rwxrwxrwx 1 fusppt dba 1801653734 Jan 5 12:51 p13390677_112040_AIX64-5L_1of7.zip

unzip above patches using instance owner ( fusppt user)

unzip p13390677_112040_AIX64-5L_2of7.zip

unzip p13390677_112040_AIX64-5L_1of7.zip

4). Go through the note 169706.1 and install necessary OS patches/packages.

5). Make new folder in both node with same permission and take tar backup of oracle home from both node and inventory

--inventory backup---------

cd /u02/oracle/oraInventory

tar -cvf /staging/11.2.0.4_software/install_log/oracle_inventory_Node1.tar .

Oracle home backup from instance owner user (TESTDB)

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

cd /u01/oracle/TESTDB/product/11.2.0.4

tar -cvf /staging/11.2.0.4_software/install_log/TESTDB11204eepdec2015.tar .

[Node1:fusppt:install]

mkdir -p /u05/oracle/TESTDB/product/11.2.0.4

chmod -R 750 /u05/oracle/TESTDB/product/11.2.0.4

[Node1:fusppt:product] ls -ltr

total 16

drwxr-x--- 80 fusppt dba 4096 Feb 8 16:31 11.2.0

drwxr-xr-x 74 fusppt dba 4096 Feb 8 16:52 11.2.0.4 ----new folder

[Node1:fusppt:product] cd 11.2.0.4

[Node1:fusppt:11.2.0.4] pwd

/u05/oracle/TESTDB/product/11.2.0.4

[Node1:fusppt:11.2.0.4]

6. Install 11.2.0.4 in a separate Oracle home ( /u05/oracle/TESTDB/product/11.2.0.4 )

Starting with the first patch set for Oracle Database 11g Release 2 (11.2.0.2), Oracle Database patch sets are full installations of the Oracle Database software. You can refer to Note 1189783.1 for more information.

Installation documentation - http://docs.oracle.com/cd/E11882_01/install.112/e24346/toc.htm

a) check whether GUI is configured or not ,

ps -ef|grep vnc

check port no

if vnc already running but GUI is not working ,then kill vnc as below

To stop the VNC server simpy run the following command:

vncserver -kill :2

vncserver -kill :20

run the VNC again

$ vncserver

password= oracle@1234

and check GUI connectivity by vncviewer

b) unset all oracle related path on both node

export IGNORE_PREADDNODE_CHECKS=Y(optinal)

$ unset ORACLE_BASE

$ unset ORACLE_HOME

$ unset ORACLE_HOSTNAME

$ unset ORACLE_SID

$ unset ORACLE_UNQNAM

c) run ./runInstaller

[Node1:fusppt:database] ls -ltr

total 144

drwxr-xr-x 2 fusppt dba 256 Oct 9 2013 sshsetup

-rwxr-xr-x 1 fusppt dba 10305 Oct 9 2013 runInstaller

drwxr-xr-x 2 fusppt dba 256 Oct 9 2013 rpm

-rwxr-xr-x 1 fusppt dba 14047 Oct 9 2013 rootpre.sh

drwxr-xr-x 3 fusppt dba 256 Oct 9 2013 rootpre

drwxr-xr-x 2 fusppt dba 256 Oct 9 2013 response

drwxr-xr-x 4 fusppt dba 4096 Oct 9 2013 install

drwxr-xr-x 14 fusppt dba 4096 Oct 9 2013 stage

-rw-r--r-- 1 fusppt dba 500 Oct 9 2013 welcome.html

-rw-r--r-- 1 fusppt dba 30016 Oct 9 2013 readme.html

[Node1:fusppt:database] pwd

/oswatcher/install/database

[Node1:fusppt:database] run ./rootpre.sh from root on both node

Carefully give the path of oracle base and oracle home

New home= /u05/oracle/TESTDB/product/11.2.0.4

Oracle base =/u05/oracle/TESTDB

6). Verify the installation by going through installAction<time stamp>.log, check there are no errors

Once software has been installed, Start upgradation of database as below

Pre Implementation

[Node1:fusppt:database_upgrad] ps -ef|grep pmon

orappt 15990972 1 0 Feb 04 - 2:05 ora_pmon_PPT1

fusppt 30081072 1 0 Jan 06 - 12:41 ora_pmon_TESTDB1

orauat 38863102 1 0 Jan 06 - 11:20 ora_pmon_UAT1

oracrs 60227780 1 0 Jan 06 - 5:55 asm_pmon_+ASM1

fusppt 65814 48103614 0 17:34:04 pts/3 0:00 grep pmon

[Node1:fusppt:database_upgrad]

[Node1:fusppt:database_upgrad] srvctl config database -d TESTDB -a

Database unique name: TESTDB

Database name: TESTDB

Oracle home: /u05/oracle/TESTDB/product/11.2.0

Oracle user: fusppt

Spfile: +DATA2/TESTDB/spfileTESTDB.ora

Domain: retail2u.trcg.co.uk

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: TESTDB

Database instances: TESTDB1,TESTDB2

Disk Groups:

Services:

Database is enabled

Database is administrator managed

[Node1:fusppt:database_upgrad]

2) Execute pre-upgrade scripts

a. utlu112i.sql (copy this file from 11.2.0.4 $ORACLE_HOME/rdbms) or you can download latest script from "How to Download and Run Oracle's Database Pre-Upgrade Utility ( Doc ID 884522.1 )".

b. dbupgdiag.sql (to download the script refer to Note 556610.1 )

3). Follow the suggestions given by utu112i.sql script and verify that there are no INVALID components and INVALID objects owned by SYS/SYSTEM schema, if there are any, then do not proceed with upgrade, until all components, objects are VALID

4). collect statistics prior to performing the actual database upgrade

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

sql> purge DBA_RECYCLEBIN;

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql ( to check invalid objects)

5). If the auditing is enabled and SYS.AUD$ table is large, it is suggested to take backup of the table, truncate the table and proceed the with upgrade. Refer to Note 979942.1 , Note 73408.1

sql> truncate table SYS.AUD$;

column object_name format a30

select object_name, object_type from dba_objects where OBJECT_NAME not in ('AQ$_SCHEDULES_PRIMARY', 'AQ$_SCHEDULES','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH PACKAGE')

and object_name||object_type in (select object_name||object_type

from dba_objects where owner = 'SYS') and owner = 'SYSTEM';

SQL>@check_stale_s2.sql (To check stale schema mos note id-560336.1 to check the schema stale statistics )

IMPLEMENTATION

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

1. Shutdown source DB

srvctl stop database -d TESTDB

2. set appropriate ENV varliables like ORACLE_HOME, PATH, ORACLE_SID

3. cd $ORACLE_HOME (new oracle home)

export ORACLE_SID=TESTDB 1

export ORACLE_HOME=/u05/oracle/TESTDB/product/11.2.0.4/

4. copy init.ora parameter file to 11.2.0.4 ORACLE_HOME

create pfile='/u05/oracle/TESTDB/product/11.2.0.4/dbs/init_TESTDB.ora' from spfile

create pfile='/u01/oracle/fsprod/db/tech_st/11.2.0.4/dbs/init_FSPROD.ora' from spfile

copy pfile from old to new home on both node

louth[/u01/oracle/fsprod/db/tech_st/11.2.0]$ cd dbs

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/dbs]$ ls -ltr

total 45640

-rw-r--r-- 1 fusprod dba 2851 May 15 2009 init.ora

-rw-r----- 1 fusprod dba 40 Jul 13 2011 initFSPROD1.ora

drwxr-x--- 2 fusprod asmadmin 256 Nov 19 2011 core_64618668

-rw-r----- 1 fusprod dba 1536 Jun 08 2015 orapwFSPROD1

-rw-r--r-- 1 fusprod dba 960 Oct 18 2015 tdpoerror.log

-rw-r----- 1 fusprod asmadmin 23347200 Jul 23 20:10 snapcf_FSPROD1.f

-rw-rw---- 1 fusprod asmadmin 1544 Jul 23 22:53 hc_FSPROD1.dat

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/dbs]$ cat initFSPROD1.ora

SPFILE='+DATA1/FSPROD/spfileFSPROD.ora'

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/dbs]$

louth[/u01/oracle/fsprod/db/tech_st/11.2.0]$ cd dbs

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/dbs]$ ls -ltr

total 45640

-rw-r--r-- 1 fusprod dba 2851 May 15 2009 init.ora

-rw-r----- 1 fusprod dba 40 Jul 13 2011 initFSPROD1.ora

drwxr-x--- 2 fusprod asmadmin 256 Nov 19 2011 core_64618668

-rw-r----- 1 fusprod dba 1536 Jun 08 2015 orapwFSPROD1

-rw-r--r-- 1 fusprod dba 960 Oct 18 2015 tdpoerror.log

-rw-r----- 1 fusprod asmadmin 23347200 Jul 23 20:10 snapcf_FSPROD1.f

-rw-rw---- 1 fusprod asmadmin 1544 Jul 23 22:53 hc_FSPROD1.dat

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/dbs]$ cat initFSPROD1.ora

SPFILE='+DATA1/FSPROD/spfileFSPROD.ora'

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/dbs]$

cd u01/oracle/fsprod/db/tech_st/11.2.0/dbs

cp initFSPROD1.ora orapwFSPROD1 /u01/oracle/fsprod/db/tech_st/11.2.0.4/dbs/

buxton[/u01/oracle/fsprod/db/tech_st/11.2.0]$ cd dbs

buxton[/u01/oracle/fsprod/db/tech_st/11.2.0/dbs]$ ls -ltr

total 45640

-rw-r--r-- 1 fusprod dba 2851 Jul 13 2011 init.ora

-rw-rw---- 1 fusprod dba 1544 Jul 13 2011 hc_DBUA0.dat

-rw-r----- 1 fusprod dba 40 Jul 13 2011 initFSPROD2.ora

-rw-r----- 1 fusprod dba 1536 Jul 03 2013 orapwFSPROD2

-rw-r----- 1 fusprod asmadmin 23347200 Jul 10 08:54 snapcf_FSPROD2.f

-rw-rw---- 1 fusprod asmadmin 1544 Jul 23 23:03 hc_FSPROD2.dat

buxton[/u01/oracle/fsprod/db/tech_st/11.2.0/dbs]$ cat initFSPROD2.ora

SPFILE='+DATA1/FSPROD/spfileFSPROD.ora'

buxton[/u01/oracle/fsprod/db/tech_st/11.2.0/dbs]$

cd u01/oracle/fsprod/db/tech_st/11.2.0/dbs

cp initFSPROD1.ora orapwFSPROD1 /u01/oracle/fsprod/db/tech_st/11.2.0.4/dbs/

5. make changes if any in init.ora file like removing hidden parameters, removing parameters related event tracking

if the db is in RAC setup, then CLUSTER_DATABASE should be false

CLUSTER_DATABASE=false

6. Start the database in upgrade mode from new ORACLE_HOME using newly parameter file (double check CLUSTER_DATABASE=false in new pfile)

7. execute "catupgrd.sql"

export ORACLE_SID=TESTDB 1

export ORACLE_HOME=/u05/oracle/TESTDB/product/11.2.0.4/

cd $ORACLE_HOME/rdbms/admin

$sqlplus "/as sysdba"

startup UPGRADE pfile=/u05/oracle/TESTDB/product/11.2.0.4/dbs/init_TESTDB.ora

spool cat.log

@$ORACLE_HOME/rdbms/admin/catupgrd.sql

spool off

startup upgrade

exit

echo $ORACLE_SID

echo $ORACLE_HOME

nohup sqlplus -S "/ as sysdba" @$ORACLE_HOME/rdbms/admin/catupgrd.sql &

Commit complete.

Database closed.

Database dismounted.

ORACLE instance shut down.

[1] + Done nohup sqlplus -S "/ as sysdba" @$ORACLE_HOME/rdbms/admin/catupgrd.sql &

it will take approx 1 hours

louth[/u01/oracle/fsprod/db/tech_st/11.2.0.4/dbs]$ mv init_FSPROD.ora init_FSPROD.ora.bk

louth[/u01/oracle/fsprod/db/tech_st/11.2.0.4/dbs]$ cp initFSPROD1.ora orapwFSPROD1 /u01/oracle/fsprod/db/tech_st/11.2.0.4/dbs/

louth[/u01/oracle/fsprod/db/tech_st/11.2.0.4/dbs]$ cd ..

louth[/u01/oracle/fsprod/db/tech_st/11.2.0.4]$ cd ..

louth[/u01/oracle/fsprod/db/tech_st]$ ls -ltr

total 14707944

-rw-r--r-- 1 fusprod dba 7510302720 Mar 07 2013 fsprdbkp_07032013.tar

-rw-r--r-- 1 fusprod dba 20152320 Mar 07 2013 Inventorybkp_07032013.tar

drwxr-xr-x 4 fusprod dba 256 Sep 14 2013 patches

drwxr-xr-x 81 fusprod dba 4096 Jul 23 21:12 11.2.0

drwxr-xr-x 75 fusprod dba 4096 Jul 23 22:42 11.2.0.4

louth[/u01/oracle/fsprod/db/tech_st]$ cd 11.2.0

louth[/u01/oracle/fsprod/db/tech_st/11.2.0]$ cd dbs

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/dbs]$ ls -ltr

total 45640

-rw-r--r-- 1 fusprod dba 2851 May 15 2009 init.ora

-rw-r----- 1 fusprod dba 40 Jul 13 2011 initFSPROD1.ora

drwxr-x--- 2 fusprod asmadmin 256 Nov 19 2011 core_64618668

-rw-r----- 1 fusprod dba 1536 Jun 08 2015 orapwFSPROD1

-rw-r--r-- 1 fusprod dba 960 Oct 18 2015 tdpoerror.log

-rw-r----- 1 fusprod asmadmin 23347200 Jul 23 23:05 snapcf_FSPROD1.f

-rw-rw---- 1 fusprod asmadmin 1544 Jul 23 23:32 hc_FSPROD1.dat

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/dbs]$ cp initFSPROD1.ora orapwFSPROD1 /u01/oracle/fsprod/db/tech_st/11.2.0.4/dbs/

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/dbs]$

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/dbs]$ cd ../..

louth[/u01/oracle/fsprod/db/tech_st]$ cd 11.2.0

louth[/u01/oracle/fsprod/db/tech_st/11.2.0]$ cd network/admin

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/network/admin]$ ls -ltr

total 80

-rw-r--r-- 1 fusprod dba 187 May 07 2007 shrept.lst

drwxr-xr-x 2 fusprod dba 256 Jul 13 2011 samples

-rw-r--r-- 1 fusprod dba 983 Feb 15 2013 tnsnames.ora_15022013

-rw-r--r-- 1 fusprod dba 982 Feb 15 2013 tnsnames.ora

-rw-r--r-- 1 fusprod dba 27269 Jul 23 18:57 pre_recycle.log

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/network/admin]$ cp tnsnames.ora /u01/oracle/fsprod/db/tech_st/11.2.0.4/network/admin/

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/network/admin]$

Step 27-Restart the database in normal mode

srvctl upgrade database -d db-unique-name -o oraclehome

srvctl upgrade database -d FSPROD -o /u01/oracle/fsprod/db/tech_st/11.2.0.4

srvctl start database -d FSPROD

IMP : Need to take database to ARCHIVE LOG MODE

Run the postupgrade script utlu112s.sql to display the results of the upgrade and run catuppst.sql to finish the upgrade.

@$ORACLE_HOME/rdbms/admin/utlu112s.sql

@$ORACLE_HOME/rdbms/admin/catuppst.sql

Finally run utlrp.sql to recompile any remaining stored PL/SQL and Java code and verify that all expected packages and classes are valid:

@?/rdbms/admin/utlrp.sql

POST IMPLEMETATION

Pre-upgrade checks should be run during your upgrade planning phase, the tool prompts you which version you are planning to upgrade to:

$ ./orachk –u –o pre

$ ./exachk –u –o pre

After performing your upgrade you should then run the post upgrade checks:

$ ./orachk –u –o post

$ ./exachk –u –o post

For more information about running in on demand mode, see On Demand Mode Operation.

1. Check upgrade logs and make sure catupgrd.sql run without any error.

2. execute dbupgdiag.sql in 11.2.0.4, verify that there are no INVALID components and INVALID objects owned by SYS/SYSTEM schema

SQL> set echo on

-- query registry

SQL> set lines 80 pages 100

SQL> select substr(comp_id,1,15) comp_id,substr(comp_name,1,30)

comp_name,substr(version,1,10) version,status

from dba_registry order by modified;

3. Complete the post ugrade steps (refer Note 837570.1 )

4. Verify/test your application's functionality after db upgrade. Take a full backup, then you can decide to change COMPATIBLE parameter.

3) check the oracle registry and run post up gradation tool( utlu112s.sql)

4) run full database backup and statistics.

5) check the remotely connectivity and to which user database has been started and send mail to customer with screenshot.

6)Confirm Application/Ops team to start their application services

8) In-case of any issues again Application team has to stop their services and then Database team can stop and rollback the change and confirm back application team.

9) Make oracle home change in ORATAB

to copy enviroment variable

ls -ltr /u01/oracle/fsprod/db/tech_st/11.2.0/FSPROD_buxton.retail2u.trcg.co.uk.env

cp /u01/oracle/fsprod/db/tech_st/11.2.0/FSPROD_buxton.retail2u.trcg.co.uk.env

/u01/oracle/fsprod/db/tech_st/11.2.0.4/

-rw-r--r-- 1 fusprod dba 27 Aug 01 14:06 FSPROD2_buxton.env.bk

-rw-r--r-- 1 fusprod dba 178 Aug 01 14:11 FSPROD_buxton.retail2u.trcg.co.uk.env

buxton[/u01/oracle/fsprod/db/tech_st/11.2.0.4]$ cat FSPROD_buxton.retail2u.trcg.co.uk.env

export ORACLE_SID=FSPROD2

export ORACLE_BASE=/u01/oracle/fsprod

export ORACLE_HOME=/u01/oracle/fsprod/db/tech_st/11.2.0.4

export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch

buxton[/u01/oracle/fsprod/db/tech_st/11.2.0.4]$

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/network/admin]$ connect catalog rmc_fsprod/fsprod_rmc@rmcprd;

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/network/admin]$ rman

create soft link in new home

-rwxr-xr-x 1 fusprod dba 69456 Jul 13 2011 libnmcfhc.so

-rw-r--r-- 1 fusprod dba 1114 Jul 13 2011 nnfgt.o

-rw-r--r-- 1 fusprod dba 120 Jul 13 2011 ldflags

lrwxrwxrwx 1 fusprod dba 19 Sep 09 2011 libobk.a -> /usr/lib/libobk64.a

-rw-r--r-- 1 fusprod dba 9874049 Mar 07 2013 libpls11_pic.a

-rw-r--r-- 1 fusprod dba 10107895 Mar 07 2013 libpls11.a

-rw-r--r-- 1 fusprod dba 25582460 Mar 07 2013 libgeneric11.a

-rw-r--r-- 1 fusprod dba 4638861 Mar 07 2013 libcommon11.a

-rw-r--r-- 1 fusprod dba 897977 Mar 07 2013 libasmclnt11.a

-rw-r--r-- 1 fusprod dba 17864404 Mar 07 2013 liboraolap11.a

-rw-r--r-- 1 fusprod dba 941801 Mar 07 2013 libnro11.a

-rw-r--r-- 1 fusprod dba 4973846 Mar 07 2013 libordsdo11.a

-rw-r--r-- 1 fusprod dba 7650899 Mar 07 2013 libxml11.a

-rw-r--r-- 1 fusprod dba 2698059 Mar 07 2013 libztkg11.a

-rw-r--r-- 1 fusprod dba 73592 Mar 07 2013 libzt11.a

-rw-r--r-- 1 fusprod dba 12696784 Mar 07 2013 libnnz11.a

-rw-r--r-- 1 fusprod dba 37075 Mar 07 2013 clntsh.exp

-rwxr-xr-x 1 fusprod dba 48492701 Mar 07 2013 libclntsh.so

-rw-r--r-- 1 fusprod dba 270923 Mar 07 2013 clntsh.map

-rw-r--r-- 1 fusprod dba 49601511 Mar 07 2013 libclntsh.a

-rwxr-xr-x 1 fusprod dba 65985812 Mar 07 2013 libttsh11.so

lrwxrwxrwx 1 fusprod dba 53 Mar 07 2013 libclntsh.so.10.1 -> /u01/oracle/fsprod/db/tech_st/11.2.0/lib/libclntsh.so

-rwxr-xr-x 1 testdba dba 4943481 Mar 07 2013 libocci.so

-rw-r--r-- 1 testdba dba 6362835 Mar 07 2013 libocci.a

-rw-r--r-- 1 testdba dba 2246 Mar 07 2013 agtsh.exp

-rw-r--r-- 1 testdba dba 178761060 Mar 07 2013 libserver11.a

-rw-r--r-- 1 testdba dba 43485308 Mar 07 2013 libagtsh.a

Node1[/u01/oracle/testdb/db/tech_st/11.2.0/lib]$

--on both node

ln -s /usr/lib/libobk64.a libobk.a

you can copy this file as well from old now on both node

cp testdb1_Node1.env /u01/oracle/testdb/db/tech_st/11.2.0.4/

Node1[/u01/oracle/fsprod/db/tech_st/11.2.0.4]$ cat testdb1_Node1.env

export ORACLE_SID=testdb1

export ORACLE_BASE=/u01/oracle/testdb

export ORACLE_HOME=/u01/oracle/testdb/db/tech_st/11.2.0.4

export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch

Node1[/u01/oracle/testdb/db/tech_st/11.2.0.4]$

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jul 24 01:08:50 2016

RMAN> connect catalog rmc_testdb/testdb_rmc@catalogprd;

connected to recovery catalog database

PL/SQL package RMC_testdb.DBMS_RCVCAT version 11.02.00.01 in RCVCAT database is not current

PL/SQL package RMC_testdb.DBMS_RCVMAN version 11.02.00.01 in RCVCAT database is not current

RMAN> connect target;

connected to target database: testdb (DBID=2453172318)

RMAN>

RMAN> upgrade catalog;

recovery catalog owner is RMC_testdb

enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 11.02.00.04

DBMS_RCVMAN package upgraded to version 11.02.00.04

DBMS_RCVCAT package upgraded to version 11.02.00.04

RMAN>

Node1[/u01/oracle/fsprod/db/tech_st/11.2.0/network/admin]$

Node2[/u01/oracle/fsprod/db/tech_st/11.2.0/network/admin]$ srvctl config database -d testdb -a

Database unique name: testdba

Database name: testdba

Oracle home: /u01/oracle/fsprod/db/tech_st/11.2.0.4

Oracle user: testdba

Spfile: +DATA1/FSPROD/spfiletestdba.ora

Domain: retail.trcg.co.uk

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: testdb

Database instances: testdb2,testdb1

Disk Groups: DATA1,testdb_FRA1

Mount point paths:

Services:

Type: RAC

Database is enabled

Database is administrator managed

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/network/admin]$

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/network/admin]$ tnsping fusprod-scan:1522/FSPROD.retail2u.trcg.co.uk

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 24-JUL-2016 02:07:26

Used parameter files:

Used HOSTNAME adapter to resolve the alias

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=FSPROD.retail2u.trcg.co.uk))(ADDRESS=(PROTOCOL=TCP)(HOST=10.160.32.40)(PORT=1522))(ADDRESS=(PROTOCOL=TCP)(HOST=10.160.32.41)(PORT=1522))(ADDRESS=(PROTOCOL=TCP)(HOST=10.160.32.42)(PORT=1522)))

OK (0 msec)

louth[/u01/oracle/fsprod/db/tech_st/11.2.0/network/admin]$

louth[/u01/oracle/fsprod/db/tech_st/11.2.0.4]$ sqlplus system/fsprod123@fusprod-scan:1522/FSPROD.retail2u.trcg.co.uk

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 24 02:48:59 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>

==========================================

If you are upgrading a cluster database from releases 10.2, 11.1, or 11.2, then upgrade the database configuration in Oracle Clusterware using the following command:

$ srvctl upgrade database -d db-unique-name -o oraclehome

Run catuppst.sql to perform upgrade actions that do not require the database to be in UPGRADE mode:

SQL> @catuppst.sql

Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql

· If you are upgrading a cluster database from releases 10.2, 11.1, or 11.2, then upgrade the database configuration in Oracle Clusterware using the following command:

$ srvctl upgrade database -d db-unique-name -o oraclehome

Upgrade/Downgrade 10gR2 <=> 11gR2

After I tested upgrade Oracle Database 10gR2 to 11gR2 by command-line. I just curious to downgrade Oracle Database 11gR2 to 10gR2. One thing, If we need to downgrade Database, don't change "compatible" parameter.

10G ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
11G ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

Step: Upgrade Oracle Database 10.2.0.5 to 11.2.0.2

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl

- Check "compatible" parameter

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.5.0

- Check Oracle Version

- Check by pre-upgrade script

SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 12-29-2010 17:29:14
Script Version: 11.2.0.2.0 Build: 001.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL
--> version: 10.2.0.5.0
--> compatible: 10.2.0.5.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V4.
.
.
.
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
.
.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

EXECUTE dbms_stats.gather_dictionary_stats;

**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'

Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.
**********************************************************************

- Gather stats and truncate AUD$ table

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

SQL> truncate table SYS.AUD$ drop storage;

- Start to upgrade by "catupgrd.sql" script

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

$ cp /u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
$ cp /u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
$ /u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /nolog

SQL> connect / as sysdba
Connected to an idle instance.
SQL>> startup upgrade;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 730714112 bytes
Fixed Size 2230080 bytes
Variable Size 205523136 bytes
Database Buffers 515899392 bytes
Redo Buffers 7061504 bytes
Database mounted.
Database opened.

SQL> spool upgrade.log
SQL> set echo on
SQL> set termout on
SQL> @?/rdbms/admin/catupgrd.sql
.
.
.
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> Rem *********************************************************************
SQL> Rem END catupgrd.sql
SQL> Rem *********************************************************************

After run "catupgrd.sql", Instance should shutdown
- Step to run "catuppst.sql" script(This is post upgrade script: only necessary when upgrading from ≥ 10.1)

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 730714112 bytes
Fixed Size 2230080 bytes
Variable Size 360712384 bytes
Database Buffers 360710144 bytes
Redo Buffers 7061504 bytes
Database mounted.
Database opened.

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.5.0

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> @?/rdbms/admin/catuppst.sql

- Generate fixed object stats

SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

- Recompile

SQL> @?/rdbms/admin/utlrp.sql

- Run post-Upgrade script

SQL> @?/rdbms/admin/utlu112s.sql

- Compare invalid objects scripts

SQL> @?/rdbms/admin/utluiobj.sql

- Adjust time zone data

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 939495424 bytes
Fixed Size 2232088 bytes
Variable Size 251658472 bytes
Database Buffers 679477248 bytes
Redo Buffers 6127616 bytes
Database mounted.
Database opened.

SQL> exec dbms_dst.begin_upgrade(new_version => 11);

PL/SQL procedure successfully completed.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 939495424 bytes
Fixed Size 2232088 bytes
Variable Size 251658472 bytes
Database Buffers 679477248 bytes
Redo Buffers 6127616 bytes
Database mounted.
Database opened.

SQL> declare num_of_failures number;
begin
dbms_dst.upgrade_database(num_of_failures);
dbms_output.put_line(num_of_failures);
dbms_dst.end_upgrade(num_of_failures);
dbms_output.put_line(num_of_failures);
end;
/

- Check Oracle database after Upgrade

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.5.0

Need to Downgrade !!! Don't change "compatible" parameter

Step: Downgrade Oracle Database 11.2.0.2 to 10.2.0.5

- Check Oracle version and "compatible" parameter

-- Make sure "compatible" parameter is not changed.
- Start Oracle database for downgrade (11gR2 Home)

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP DOWNGRADE
ORACLE instance started.

Total System Global Area 939495424 bytes
Fixed Size 2232088 bytes
Variable Size 251658472 bytes
Database Buffers 679477248 bytes
Redo Buffers 6127616 bytes
Database mounted.
Database opened.

- If use EM, then drop EM user

SQL> DROP USER sysman CASCADE;

User dropped.

- Run "catdwgrd.sql" script to downgrade

SQL> SPOOL downgrade.log
SQL> @?/rdbms/admin/catdwgrd.sql
.
.
.
SQL> Rem ***********************************************************************
SQL> Rem END catdwgrd.sql
SQL> Rem ***********************************************************************

- Shutdown database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

- Start Oracle database from 10gR2 Home and use "sqlplus" from 10gR2

$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
$ /u01/app/oracle/product/10.2.0/db_1/bin/sqlplus /nolog

SQL> connect / as sysdba
Connected to an idle instance.

SQL> STARTUP UPGRADE
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 2099136 bytes
Variable Size 192940096 bytes
Database Buffers 532676608 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.

- Run "catrelod.sql" script reloads the appropriate version of all of the database components in the downgraded database

SQL> SPOOL reload.log

SQL> @?/rdbms/admin/catrelod.sql
.
.
.
COMP_NAME STATUS VERSION
----------------------------------- ----------- ----------
Oracle Database Packages and Types VALID 10.2.0.5.0
Oracle Database Catalog Views VALID 10.2.0.5.0
JServer JAVA Virtual Machine VALID 10.2.0.5.0
Oracle XDK VALID 10.2.0.5.0
Oracle Database Java Packages VALID 10.2.0.5.0
Oracle Text VALID 10.2.0.5.0
Oracle XML Database VALID 10.2.0.5.0
Oracle Workspace Manager VALID 10.2.0.5.0
Oracle Data Mining VALID 10.2.0.5.0
OLAP Analytic Workspace VALID 10.2.0.5.0
OLAP Catalog VALID 10.2.0.5.0
Oracle OLAP API VALID 10.2.0.5.0
Oracle interMedia VALID 10.2.0.5.0
.
.
.

SQL> SPOOL OFF

- Shutdown & Startup database and then recompile

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 2099136 bytes
Variable Size 247466048 bytes
Database Buffers 478150656 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.

SQL> @?/rdbms/admin/utlrp.sql

- Check Oracle version

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.5.0

You could copy it from the tnsnames file in the old Oracle Home

/u05/oracle/TESTDB/product/11.2.0/network/admin

[Node1:testdba:admin] cd /u05/oracle/TESTDB/product/11.2.0/network/admin

[Node1:testdba:admin] ls -ltr

total 40

-rw-r--r-- 1 testdba dba 187 May 7 2007 shrept.lst

drwxr-xr-x 2 testdba dba 256 Jul 6 2011 samples

-rw-r--r-- 1 testdba dba 89 Jul 20 2011 afiedt.buf

-rw-r--r-- 1 testdba dba 187 Oct 23 2011 TESTDB1_Node1_ifile.ora

-rw-r--r-- 1 testdba dba 517 Feb 8 2013 tnsnames.ora_08022013

-rw-r--r-- 1 testdba dba 717 Feb 8 2013 tnsnames.ora

[Node1:fusppt:admin]

cd /var/opt/oracle/DBA/logs

cd /u05/oracle/TESTDB/product/11.2.0/network/admin

ls -ltr

pwd

cp -p tnsnames.ora tnsnames.ora_08022013 TESTDB1_Node1_ifile.ora /u05/oracle/TESTDB/product/11.2.0.4/network/admin/

ls -ltr /u05/oracle/TESTDB/product/11.2.0.4/network/admin/

crontab -l

RMAN> upgrade catalog;

rman target /

connect to the catalog user

then run upgrade catalog;

ps -ef|grep -i TESTDB|wc -l

-- backup rman check--

You could copy it from the tnsnames file in the old Oracle Home

/u05/oracle/TESTDB/product/11.2.0/network/admin

[Node1:testdb:admin] cd /u05/oracle/TESTDB/product/11.2.0/network/admin

[Node1:testdb:admin] ls -ltr

total 40

-rw-r--r-- 1 testdba dba 187 May 7 2007 shrept.lst

drwxr-xr-x 2 testdba dba 256 Jul 6 2011 samples

-rw-r--r-- 1 testdba dba 89 Jul 20 2011 afiedt.buf

-rw-r--r-- 1 testdba dba 187 Oct 23 2011 TESTDB1_Node1_ifile.ora

-rw-r--r-- 1 testdba dba 517 Feb 8 2013 tnsnames.ora_08022013

-rw-r--r-- 1 testdba dba 717 Feb 8 2013 tnsnames.ora

[Node1:testdb:admin]

cd /var/opt/oracle/DBA/logs

cd /u05/oracle/TESTDB/product/11.2.0/network/admin

ls -ltr

pwd

cp -p tnsnames.ora tnsnames.ora_08022013 TESTDB1_Node1_ifile.ora /u05/oracle/TESTDB/product/11.2.0.4/network/admin/

ls -ltr /u05/oracle/TESTDB/product/11.2.0.4/network/admin/

crontab -l

RMAN> upgrade catalog;

rman target /

connect to the catalog user

then run upgrade catalog;

ps -ef|grep -i TESTDB|wc -l

and create link in new home

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

pwd

/u05/oracle/TESTDB/product/11.2.0/lib

-rwxr-xr-x 1 testdba dba 727656 Jul 8 2011 libasmclnt11.so

lrwxrwxrwx 1 testdba dba 19 Oct 26 2011 libobk.a -> /usr/lib/libobk64.a

-rw-r--r-- 1 testdba dba 9985523 Nov 2 2012 libpls11.a

9:24:09 PM: D: from Old HOME

9:24:38 PM: D: Go to new home

9:24:39 PM: DP: /u05/oracle/TESTDB/product/11.2.0.4/lib

ln -s /usr/lib/libobk64.a libobk

=======================================================

12c Grid Infrastructure Upgrade on Node1/Node2 from 11g

Preparation

Created 12102_Grid directory under /staging and copied the following files there:

aix.ppc64_12102_grid_1of2.zip

aix.ppc64_12102_grid_2of2.zip

p22191349_121020_AIX64-5L.zip (Jan 2016 Grid PSU)

Unzip each of the files.

cd /staging/12102_Grid/grid

Run CVU to validate readiness for Clusterware upgrade:

Created /staging12102_Grid/tmp because /tmp is too small on preston to hold the output.

export CV_HOME=/staging/12102_Grid/tmp

./runcluvfy.sh stage -pre crsinst -upgrade -rolling -src_crshome /u02/oracle/11.2.0.3/grid -dest_crshome /u02/oracle/12.1.0.2/grid -dest_version 12.1.0.2.0 -fixup -verbose

Check output for failures.

Also downloaded ORAchk (MOS Note:1268927.1) to /staging. Unzip the file and run (instructions say as RDBMS owner):

orachk -u -o pre

Check the output for failures.

Running the Upgrade

From an X Windows session logged on to preston as oracrs. Set DISPLAY

e.g. export DISPLAY=10.125.34.42:0.0

unset ORACLE_BASE

unset ORACLE_HOME

unset ORACLE_SID

./runInstaller

Prompted to run rootpre.sh on both nodes (copied the installation zip files over to upton and unzipped it to run rootpre.sh).

Click Next.

Click Next which gave the following warnings:

Click Yes.

Edit both locations as on the screenshot below.

This gave a warning that it couldn’t create /u02/oracle/12.1.0/grid due to permissions. Open a new session and sudo to root and create this directory manually on both nodes and change permissions on 12.1.0 and grid directories to 755 and the ownership on both to oracrs:dba.

Click Yes.

Click Next.

Tick Ignore All and click Next.

Click Yes.

Click Install.

Click Yes.

Check logs under /u02/oracle/oraInventory/logs:

INFO: Exception occured during spawning :Cannot run program "/u02/oracle/12.1.0/grid/perl/bin/perl": error=13, Permission denied

This directory didn’t have execute permissions.

A separate .err file indicates we ran out of space in /tmp prior to the above error which could be the root cause.

Aborted the install.

Increased /tmp to 6.75GB on preston and 5.5GB on upton and then reran.

[preston:root:/u02/oracle/12.1.0/grid:] ./rootupgrade.sh

Performing root user operation.

The following environment variables are set as:

ORACLE_OWNER= oracrs

ORACLE_HOME= /u02/oracle/12.1.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The contents of "dbhome" have not changed. No need to overwrite.

The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:

The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Performing root user operation.

The following environment variables are set as:

ORACLE_OWNER= oracrs

ORACLE_HOME= /u02/oracle/12.1.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The contents of "dbhome" have not changed. No need to overwrite.

The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:

The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Using configuration parameter file: /u02/oracle/12.1.0/grid/crs/install/crsconfig_params

2016/05/05 14:48:44 CLSRSC-4015: Performing install or upgrade action for Oracle Trace File Analyzer (TFA) Collector.

2016/05/05 14:49:37 CLSRSC-4003: Successfully patched Oracle Trace File Analyzer (TFA) Collector.

2016/05/05 14:49:50 CLSRSC-464: Starting retrieval of the cluster configuration data

2016/05/05 14:50:22 CLSRSC-465: Retrieval of the cluster configuration data has successfully completed.

2016/05/05 14:50:22 CLSRSC-363: User ignored prerequisites during installation

2016/05/05 14:50:49 CLSRSC-515: Starting OCR manual backup.

2016/05/05 14:50:56 CLSRSC-516: OCR manual backup successful.

2016/05/05 14:51:14 CLSRSC-468: Setting Oracle Clusterware and ASM to rolling migration mode

2016/05/05 14:51:14 CLSRSC-482: Running command: '/u02/oracle/12.1.0/grid/bin/asmca -silent -upgradeNodeASM -nonRolling false -oldCRSHome /u02/oracle/11.2.0.3/grid -oldCRSVersion 11.2.0.3.0 -nodeNumber 1 -firstNode true -startRolling true'

ASM configuration upgraded in local node successfully.

2016/05/05 14:51:35 CLSRSC-469: Successfully set Oracle Clusterware and ASM to rolling migration mode

2016/05/05 14:51:35 CLSRSC-466: Starting shutdown of the current Oracle Grid Infrastructure stack

2016/05/05 14:53:47 CLSRSC-467: Shutdown of the current Oracle Grid Infrastructure stack has successfully completed.

User oracrs has the required capabilities to run CSSD in realtime mode

OLR initialization - successful

2016/05/05 15:13:33 CLSRSC-329: Replacing Clusterware entries in file '/etc/inittab'

CRS-4133: Oracle High Availability Services has been stopped.

CRS-4123: Oracle High Availability Services has been started.

2016/05/05 15:28:01 CLSRSC-472: Attempting to export the OCR

2016/05/05 15:28:01 CLSRSC-482: Running command: 'ocrconfig -upgrade oracrs dba'

2016/05/05 15:28:16 CLSRSC-473: Successfully exported the OCR

2016/05/05 15:28:39 CLSRSC-486:

At this stage of upgrade, the OCR has changed.

Any attempt to downgrade the cluster after this point will require a complete cluster outage to restore the OCR.

2016/05/05 15:28:39 CLSRSC-541:

To downgrade the cluster:

1. All nodes that have been upgraded must be downgraded.

2016/05/05 15:28:40 CLSRSC-542:

2. Before downgrading the last node, the Grid Infrastructure stack on all other cluster nodes must be down.

2016/05/05 15:28:40 CLSRSC-543:

3. The downgrade command must be run on the node preston with the '-lastnode' option to restore global configuration data.

2016/05/05 15:29:03 CLSRSC-343: Successfully started Oracle Clusterware stack

clscfg: EXISTING configuration version 5 detected.

clscfg: version 5 is 11g Release 2.

Successfully taken the backup of node specific configuration in OCR.

Successfully accumulated necessary OCR keys.

Creating OCR keys for user 'root', privgrp 'system'..

Operation successful.

2016/05/05 15:29:52 CLSRSC-474: Initiating upgrade of resource types

2016/05/05 15:30:56 CLSRSC-482: Running command: 'upgrade model -s 11.2.0.3.0 -d 12.1.0.2.0 -p first'

2016/05/05 15:30:57 CLSRSC-475: Upgrade of resource types successfully initiated.

2016/05/05 15:31:15 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

[upton:root:/u02/oracle/12.1.0/grid:] ./rootupgrade.sh

Performing root user operation.

The following environment variables are set as:

ORACLE_OWNER= oracrs

ORACLE_HOME= /u02/oracle/12.1.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The contents of "dbhome" have not changed. No need to overwrite.

The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:

The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Performing root user operation.

The following environment variables are set as:

ORACLE_OWNER= oracrs

ORACLE_HOME= /u02/oracle/12.1.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The contents of "dbhome" have not changed. No need to overwrite.

The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:

The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Using configuration parameter file: /u02/oracle/12.1.0/grid/crs/install/crsconfig_params

2016/05/05 15:33:37 CLSRSC-4015: Performing install or upgrade action for Oracle Trace File Analyzer (TFA) Collector.

2016/05/05 15:34:31 CLSRSC-4003: Successfully patched Oracle Trace File Analyzer (TFA) Collector.

2016/05/05 15:34:37 CLSRSC-464: Starting retrieval of the cluster configuration data

2016/05/05 15:34:56 CLSRSC-465: Retrieval of the cluster configuration data has successfully completed.

2016/05/05 15:34:56 CLSRSC-363: User ignored prerequisites during installation

ASM configuration upgraded in local node successfully.

2016/05/05 15:35:30 CLSRSC-466: Starting shutdown of the current Oracle Grid Infrastructure stack

2016/05/05 15:37:36 CLSRSC-467: Shutdown of the current Oracle Grid Infrastructure stack has successfully completed.

User oracrs has the required capabilities to run CSSD in realtime mode

OLR initialization - successful

2016/05/05 15:38:26 CLSRSC-329: Replacing Clusterware entries in file '/etc/inittab'

CRS-4133: Oracle High Availability Services has been stopped.

CRS-4123: Oracle High Availability Services has been started.

2016/05/05 15:43:08 CLSRSC-343: Successfully started Oracle Clusterware stack

clscfg: EXISTING configuration version 5 detected.

clscfg: version 5 is 12c Release 1.

Successfully taken the backup of node specific configuration in OCR.

Successfully accumulated necessary OCR keys.

Creating OCR keys for user 'root', privgrp 'system'..

Operation successful.

Start upgrade invoked..

2016/05/05 15:44:52 CLSRSC-478: Setting Oracle Clusterware active version on the last node to be upgraded

2016/05/05 15:44:53 CLSRSC-482: Running command: '/u02/oracle/12.1.0/grid/bin/crsctl set crs activeversion'

Started to upgrade the Oracle Clusterware. This operation may take a few minutes.

Started to upgrade the OCR.

Started to upgrade the CSS.

The CSS was successfully upgraded.

Started to upgrade Oracle ASM.

Started to upgrade the CRS.

The CRS was successfully upgraded.

Successfully upgraded the Oracle Clusterware.

Oracle Clusterware operating version was successfully set to 12.1.0.2.0

2016/05/05 15:47:48 CLSRSC-479: Successfully set Oracle Clusterware active version

2016/05/05 15:48:01 CLSRSC-476: Finishing upgrade of resource types

2016/05/05 15:48:18 CLSRSC-482: Running command: 'upgrade model -s 11.2.0.3.0 -d 12.1.0.2.0 -p last'

2016/05/05 15:48:19 CLSRSC-477: Successfully completed upgrade of resource types

2016/05/05 15:49:42 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

Back in the X Windows session Click OK.

Click Close.

Took about 2 hours from restarting after the failure.