Oracle Database Commands and Practical Examples

Thursday 16 July 2020

Patching - Apply PSU Patch 21255373 on 12.1.0.2.0 grid before upgrade.

Oracle Grid Infrastructure upgrade required patch number 21255373. So we are applying PSU patch 21255373 before upgrade 12.1.0.2.0 to 12.2.0.1.0

Details:

Node 1 : srv1

Node2 : srv2

OS : OEL Linux 6.7 X 64

Database Grid version : 12.1.0.2.0

Download patch number 21255373

OPatch Utility Information

You must use the OPatch utility version 12.1.0.1.2 or later to apply this patch. Oracle recommends that you use the latest released OPatch for 12.1 releases, which is available for download from My Oracle Support patch 6880880. It is recommended that you download the Opatch utility and the patch in a shared location to be able to access them from any node in the cluster for the patch application on each node.
Take tar level backup of grid |oraInventory| database

Check the status of clusterware (Not required)

[root@srv1 u01]# . oraenv

ORACLE_SID = [root] ? +ASM1

The Oracle base has been set to /u01/app/grid

[root@srv1 u01]# crsctl status resource -t

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

Name Target State Server State details

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

Local Resources

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

ora.CRS.dg

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

ora.DATA.dg

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

ora.FRA.dg

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

ora.asm

ONLINE ONLINE srv1 Started,STABLE

ONLINE ONLINE srv2 Started,STABLE

ora.net1.network

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

ora.ons

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

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

Cluster Resources

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

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE srv2 STABLE

ora.LISTENER_SCAN2.lsnr

1 ONLINE ONLINE srv1 STABLE

ora.LISTENER_SCAN3.lsnr

1 ONLINE ONLINE srv1 STABLE

ora.MGMTLSNR

1 ONLINE ONLINE srv1 169.254.41.197 192.1

68.10.1,STABLE

ora.cvu

1 ONLINE ONLINE srv1 STABLE

ora.mgmtdb

1 ONLINE ONLINE srv1 Open,STABLE

ora.oc4j

1 ONLINE ONLINE srv1 STABLE

ora.rac.db

1 ONLINE ONLINE srv1 Open,STABLE

2 ONLINE ONLINE srv2 Open,STABLE

ora.scan1.vip

1 ONLINE ONLINE srv2 STABLE

ora.scan2.vip

1 ONLINE ONLINE srv1 STABLE

ora.scan3.vip

1 ONLINE ONLINE srv1 STABLE

ora.srv1.vip

1 ONLINE ONLINE srv1 STABLE

ora.srv2.vip

1 ONLINE ONLINE srv2 STABLE

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

Check Optach utility information status

[grid@srv1 OPatch]$ ./opatch lsinventory -detail | ./opatch lsinventory

Oracle Interim Patch Installer version 12.2.0.1.21

Oracle Home : /u01/app/12.1.0/grid

Central Inventory : /u01/app/oraInventory

from : /u01/app/12.1.0/grid/oraInst.loc

OPatch version : 12.2.0.1.21

OUI version : 12.1.0.2.0

Log file location : /u01/app/12.1.0/grid/cfgtoollogs/opatch/opatch2020-06-14_17-48-33PM_1.log

Lsinventory Output file location : /u01/app/12.1.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2020-06-14_17-48-33PM.txt

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

Local Machine Information::

Hostname: srv1.example.com

ARU platform id: 226

ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Grid Infrastructure 12c 12.1.0.2.0

There are 1 products installed in this Oracle Home.

Installed Products (101):

Assistant Common Files 12.1.0.2.0

Automatic Storage Management Assistant 12.1.0.2.0

Buildtools Common Files 12.1.0.2.0

Cluster Ready Services Files 12.1.0.2.0

Cluster Verification Utility Common Files 12.1.0.2.0

Cluster Verification Utility Files 12.1.0.2.0

Database Configuration and Upgrade Assistants 12.1.0.2.0

Database Migration Assistant for Unicode 12.1.0.2.0

Database SQL Scripts 12.1.0.2.0

DB TOOLS Listener 12.1.0.2.0

Deinstallation Tool 12.1.0.2.0

Expat libraries 2.0.1.0.2

Hadoopcore Component 12.1.0.2.0

HAS Common Files 12.1.0.2.0

HAS Files for DB 12.1.0.2.0

Installation Common Files 12.1.0.2.0

Installation Plugin Files 12.1.0.2.0

Installer SDK Component 12.1.0.2.0

Java Development Kit 1.6.0.75.0

LDAP Required Support Files 12.1.0.2.0

OLAP SQL Scripts 12.1.0.2.0

Oracle Advanced Security 12.1.0.2.0

Oracle Bali Share 11.1.1.6.0

Oracle Clusterware RDBMS Files 12.1.0.2.0

Oracle Configuration Manager Deconfiguration 10.3.1.0.0

Oracle Containers for Java 12.1.0.2.0

Oracle Core Required Support Files 12.1.0.2.0

Oracle Core Required Support Files for Core DB 12.1.0.2.0

Oracle Database 12c 12.1.0.2.0

Oracle Database 12c Multimedia Files 12.1.0.2.0

Oracle Database Deconfiguration 12.1.0.2.0

Oracle Database User Interface 11.0.0.0.0

Oracle Database Utilities 12.1.0.2.0

Oracle DBCA Deconfiguration 12.1.0.2.0

Oracle Extended Windowing Toolkit 11.1.1.6.0

Oracle Globalization Support 12.1.0.2.0

Oracle Globalization Support For Core 12.1.0.2.0

Oracle Grid Infrastructure 12c 12.1.0.2.0

Oracle Grid Infrastructure Bundled Agents 12.1.0.2.0

Oracle Grid Management Database 12.1.0.2.0

Oracle Help for Java 11.1.1.7.0

Oracle Help Share Library 11.1.1.7.0

Oracle Ice Browser 11.1.1.7.0

Oracle Internet Directory Client 12.1.0.2.0

Oracle Java Client 12.1.0.2.0

Oracle Java Layout Engine 11.0.0.0.0

Oracle JDBC/OCI Instant Client 12.1.0.2.0

Oracle JDBC/THIN Interfaces 12.1.0.2.0

Oracle JFC Extended Windowing Toolkit 11.1.1.6.0

Oracle JVM 12.1.0.2.0

Oracle JVM For Core 12.1.0.2.0

Oracle LDAP administration 12.1.0.2.0

Oracle Locale Builder 12.1.0.2.0

Oracle Multimedia 12.1.0.2.0

Oracle Multimedia Client Option 12.1.0.2.0

Oracle Multimedia Java Advanced Imaging 12.1.0.2.0

Oracle Multimedia Locator 12.1.0.2.0

Oracle Multimedia Locator Java Required Support Files 12.1.0.2.0

Oracle Multimedia Locator RDBMS Files 12.1.0.2.0

Oracle Net 12.1.0.2.0

Oracle Net Java Required Support Files 12.1.0.2.0

Oracle Net Listener 12.1.0.2.0

Oracle Net Required Support Files 12.1.0.2.0

Oracle Netca Client 12.1.0.2.0

Oracle Notification Service 12.1.0.2.0

Oracle Notification Service (eONS) 12.1.0.2.0

Oracle Notification Service for Instant Client 12.1.0.2.0

Oracle One-Off Patch Installer 12.1.0.1.2

Oracle Quality of Service Management (Client) 12.1.0.2.0

Oracle Quality of Service Management (Server) 12.1.0.2.0

Oracle RAC Deconfiguration 12.1.0.2.0

Oracle RAC Required Support Files-HAS 12.1.0.2.0

Oracle Recovery Manager 12.1.0.2.0

Oracle Security Developer Tools 12.1.0.2.0

Oracle Text Required Support Files 12.1.0.2.0

Oracle Universal Connection Pool 12.1.0.2.0

Oracle Universal Installer 12.1.0.2.0

Oracle USM Deconfiguration 12.1.0.2.0

Oracle Wallet Manager 12.1.0.2.0

oracle.swd.oui.core.min 12.1.0.2.0

Parser Generator Required Support Files 12.1.0.2.0

Perl Interpreter 5.14.1.0.0

Perl Modules 5.14.1.0.0

PL/SQL 12.1.0.2.0

PL/SQL Embedded Gateway 12.1.0.2.0

Platform Required Support Files 12.1.0.2.0

Precompiler Required Support Files 12.1.0.2.0

RDBMS Required Support Files 12.1.0.2.0

RDBMS Required Support Files for Instant Client 12.1.0.2.0

RDBMS Required Support Files Runtime 12.1.0.2.0

Required Support Files 12.1.0.2.0

Secure Socket Layer 12.1.0.2.0

SQL*Plus 12.1.0.2.0

SQL*Plus Files for Instant Client 12.1.0.2.0

SQL*Plus Required Support Files 12.1.0.2.0

SSL Required Support Files for InstantClient 12.1.0.2.0

Tracle File Analyzer 12.1.0.2.0

Universal Storage Manager Files 12.1.0.2.0

XDK Required Support Files 12.1.0.2.0

XML Parser for Java 12.1.0.2.0

There are 101 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

Set path andApply Patch on grid using root user as we are using opatchauto utility.

[root@srv1 21255373]# export ORACLE_HOME=/u01/app/12.1.0/grid/

[root@srv1 21255373]# export PATH=$ORACLE_HOME/OPatch:$PATH:$ORACLE_HOME/bin

[root@srv1 21255373]# export PATH=$ORACLE_HOME/perl/bin:$PATH

[root@srv1 21255373]# $ORACLE_HOME/OPatch/opatchauto apply /u01/21255373 -log /u01/21255373_response.txt

OPatchauto session is initiated at Sun Jun 14 17:53:00 2020

System initialization log file is /u01/app/12.1.0/grid/cfgtoollogs/opatchautodb/systemconfig2020-06-14_05-53-13PM.log.

Session log file is /u01/21255373_response.txt

The id for this session is MRLJ

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

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/12.1.0/dbhome_1

Patch applicability verified successfully on home /u01/app/12.1.0/grid

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

Verifying SQL patch applicability on home /u01/app/oracle/product/12.1.0/dbhome_1

No step execution required.........

Preparing to bring down database service on home /u01/app/oracle/product/12.1.0/dbhome_1

Successfully prepared home /u01/app/oracle/product/12.1.0/dbhome_1 to bring down database service

Bringing down CRS service on home /u01/app/12.1.0/grid

Prepatch operation log file location: /u01/app/12.1.0/grid/cfgtoollogs/crsconfig/crspatch_srv1_2020-06-14_05-56-30PM.log

CRS service brought down successfully on home /u01/app/12.1.0/grid

Performing prepatch operation on home /u01/app/oracle/product/12.1.0/dbhome_1

Perpatch operation completed successfully on home /u01/app/oracle/product/12.1.0/dbhome_1

Start applying binary patch on home /u01/app/oracle/product/12.1.0/dbhome_1

Binary patch applied successfully on home /u01/app/oracle/product/12.1.0/dbhome_1

Performing postpatch operation on home /u01/app/oracle/product/12.1.0/dbhome_1

Postpatch operation completed successfully on home /u01/app/oracle/product/12.1.0/dbhome_1

Start applying binary patch on home /u01/app/12.1.0/grid

Binary patch applied successfully on home /u01/app/12.1.0/grid

Starting CRS service on home /u01/app/12.1.0/grid

Postpatch operation log file location: /u01/app/12.1.0/grid/cfgtoollogs/crsconfig/crspatch_srv1_2020-06-14_06-06-05PM.log

CRS service started successfully on home /u01/app/12.1.0/grid

Preparing home /u01/app/oracle/product/12.1.0/dbhome_1 after database service restarted

No step execution required.........

Trying to apply SQL patch on home /u01/app/oracle/product/12.1.0/dbhome_1

No step execution required.........

OPatchAuto successful.

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

Patching is completed successfully. Please find the summary as follows:

Host:srv1

RAC Home:/u01/app/oracle/product/12.1.0/dbhome_1

Version:12.1.0.2.0

Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /u01/21255373/21255373

Log: /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2020-06-14_18-00-41PM_1.log

Host:srv1

CRS Home:/u01/app/12.1.0/grid

Version:12.1.0.2.0

Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /u01/21255373/21255373

Log: /u01/app/12.1.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-06-14_18-01-45PM_1.log

OPatchauto session completed at Sun Jun 14 18:21:31 2020

Time taken to complete the session 28 minutes, 32 seconds

NOTE: As we Apply patch one node at a time so application connection not hammpered. apply same setup on 2nd node

Check Result command :

[grid@srv1 OPatch]$ ./opatch lsinventory

[grid@srv1 OPatch]$ ./opatch lsinventory | grep 21255373

Patch 21255373 : applied on Sun Jun 14 18:05:57 IST 2020

Patch description: "OCW Patch Set Update : 12.1.0.2.0 (21255373)"

21394146, 19916737, 19049721, 19368917, 21255373, 19635215, 20408163

[grid@srv1 OPatch]$

[grid@srv2 OPatch]$ ./opatch lsinventory | grep 21255373
Patch 21255373 : applied on Sun Jun 14 18:46:17 IST 2020
Patch description: "OCW Patch Set Update : 12.1.0.2.0 (21255373)"
21394146, 19916737, 19049721, 19368917, 21255373, 19635215, 20408163

SQL> select * from registry$history order by id;

Monday 13 July 2020

RAC - Change OCR Vote disk and ASM Spfile location to new disk group

Scenario:

Hi all , As per current scenario we have to upgrade 12.1 grid to 12.2 grid on linux suse 12.5, We have CRS disk group having size 10 GB with normal redundancy. CRS diskgoup having OCR VOTING DISK and ASM spfile location.

As per documentation 33GB CRS disk size required for up-gradation 12.2grid. So we add new diskgroup CRSNEW and remove old CRS and move all files before drop.

STEPS:

Create New disk group CRSNEW with normal redundancy.

Mount CRSNEW diskgroup on all cluster nodes , By default it is dismounted state on remote cluster node.

Move OCR and VOTING DISK from CRS to NEWCRS diskgroup.

Create pfile of asm and change the asm location to the NEW CRS diskgroup by creating asm spfile from asm pfile.

Verify diskgroup on all clusterware

Stop and start CRS individually one by one on all clusterware node

Check clusterware status

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

1. Create disk crs1_1, crs2_2 crs2_2 using oracleasm command below.

oracleasm createdisk crs1_1 /dev/sdh1

oracleasm createdisk crs2_2 /dev/sdk1

oracleasm createdisk crs3_3 /dev/sdj1

srv1:~ # ps -ef |grep pmon

grid 2680 1 0 Jul10 ? 00:00:05 mdb_pmon_-MGMTDB

oracle 6945 1 0 Jul10 ? 00:00:08 ora_pmon_smrplm1

root 15886 7522 0 18:11 pts/0 00:00:00 grep --color=auto pmon

grid 19349 1 0 Jul10 ? 00:00:07 asm_pmon_+ASM1

2. Check OracleAsm disk status ( do oracleasm scandisk on another nodes)

srv1:~ # oracleasm listdisks

CRS1

CRS1_1

CRS2

CRS2_2

CRS3

CRS3_3

DATA

FRA

srv1:~ #

3. Check status of disk group.

SQL> set lines 200

SQL> /

DISK_GROUP_NAME DISK_FILE_PATH DISK_FILE_NAME

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

CRS ORCL:CRS1 CRS1

CRS ORCL:CRS3 CRS3

CRS ORCL:CRS2 CRS2

DATA ORCL:DATA DATA

FRA ORCL:FRA FRA

[CANDIDATE] ORCL:CRS2_2

[CANDIDATE] ORCL:CRS1_1

[CANDIDATE] ORCL:CRS3_3

8 rows selected.

4. Create new disk group CRSNEW with normal redundancy.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

grid@srv1:~> sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 10 16:41:12 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

SQL>

SQL> create diskgroup CRSNEW normal redundancy disk 'ORCL:CRS1_1' ,'ORCL:CRS2_2' ,'ORCL:CRS3_3' attribute 'compatible.rdbms'='12.1.0.0', 'compatible.asm'='12.1.0.0';

Diskgroup created.

5. Mount disk group on all nodes and check status

SQL> alter diskgroup CRSNEW mount;

SQL> /

DISK_GROUP_NAME DISK_FILE_PATH DISK_FILE_NAME

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

CRS ORCL:CRS3 CRS3

CRS ORCL:CRS1 CRS1

CRS ORCL:CRS2 CRS2

CRSNEW ORCL:CRS1_1 CRS1_1

CRSNEW ORCL:CRS2_2 CRS2_2

CRSNEW ORCL:CRS3_3 CRS3_3

DATA ORCL:DATA DATA

FRA ORCL:FRA FRA

8 rows selected.

SQL>

SQL> select NAME,STATE,TYPE,TOTAL_MB from v$asm_diskgroup;

NAME STATE TYPE TOTAL_MB

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

FRA MOUNTED EXTERN 12287

DATA MOUNTED EXTERN 12287

CRSNEW MOUNTED NORMAL 122877

CRS MOUNTED NORMAL 30717

SQL>

6. Mount disk group on node srv2 hostname

ORACLE_SID = [grid] ? +ASM2

The Oracle base has been set to /u01/app/grid

grid@srv2:~> sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 10 17:01:56 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

SQL> select NAME,STATE,TYPE,TOTAL_MB from v$asm_diskgroup;

NAME STATE TYPE TOTAL_MB

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

FRA MOUNTED EXTERN 12287

CRSNEW DISMOUNTED 0

DATA MOUNTED EXTERN 12287

CRS MOUNTED NORMAL 30717

SQL> alter diskgroup CRSNEW mount;

Diskgroup altered.

7. go to /u01/app/12.1.0/grid/bin directory with root user register add new diskgroup using ocrconfig command

SQL>

grid@srv1:~> cd /u01/app/12.

12.1.0/ 12.2.0/

grid@srv1:~> cd /u01/app/12.1.0/grid/bin/

grid@srv1:/u01/app/12.1.0/grid/bin> ls -ltr ocrconfig

-rwxr-xr-x 1 root oinstall 9422 Jul 3 18:10 ocrconfig

grid@srv1:/u01/app/12.1.0/grid/bin> su root

Password:

su: Authentication failure

grid@srv1:/u01/app/12.1.0/grid/bin> su root

Password:

srv1:/u01/app/12.1.0/grid/bin # . oraenv

ORACLE_SID = [+ASM1] ?

srv1:/u01/app/12.1.0/grid/bin # ocrconfig -add +CRSNEW

PROT-30: The Oracle Cluster Registry location to be added is not usable

PROC-50: The Oracle Cluster Registry location to be added is inaccessible on nodes srv2.

8. Delete old CRS diskgroup using ocrconfig

srv1:/u01/app/12.1.0/grid/bin # ocrconfig -delete +CRS

srv1:/u01/app/12.1.0/grid/bin # crsctl replace votedisk +CRSNEW

Successful addition of voting disk 5920eeae52e04f40bf92cee3be7ca3e3.

Successful addition of voting disk 6e4c2f38cbe84f76bf941e58abc48035.

Successful addition of voting disk 7af1964f8e3a4ff7bf1a366cffd81af0.

Successful deletion of voting disk f968ab1dfafa4fa1bfc69cec3995c776.

Successful deletion of voting disk e8a2780969fd4f90bfa04544d728c489.

Successful deletion of voting disk a15c1f7048904f4dbfe02381989863ef.

Successfully replaced voting disk group with +CRSNEW.

CRS-4266: Voting file(s) successfully replaced

9. Check the status of votedisk

srv1:/u01/app/12.1.0/grid/bin # crsctl query css votedisk

## STATE File Universal Id File Name Disk group

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

1. ONLINE 5920eeae52e04f40bf92cee3be7ca3e3 (ORCL:CRS1_1) [CRSNEW]

2. ONLINE 6e4c2f38cbe84f76bf941e58abc48035 (ORCL:CRS2_2) [CRSNEW]

3. ONLINE 7af1964f8e3a4ff7bf1a366cffd81af0 (ORCL:CRS3_3) [CRSNEW]

Located 3 voting disk(s).

srv1:/u01/app/12.1.0/grid/bin # ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 4

Total space (kbytes) : 409568

Used space (kbytes) : 1752

Available space (kbytes) : 407816

ID : 486030082

Device/File Name : +CRSNEW

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

srv1:/u01/app/12.1.0/grid/bin #

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

9. Change spfile location by creating first asm pfile then using pfile create new ASMPFILE with NEWCRS diskgroup location.

create pfile='/u01/app/grid' from spfile;

/u01/app/grid

grid@srv1:/u01/app> sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 10 17:41:56 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

SQL> create pfile='/u01/app/grid/asmfile.ora' from spfile;

File created.

SQL>

create spfile='+CRSNEW' from pfile='/u01/app/grid/asmfile.ora';

SQL> create pfile='/u01/app/grid/asmfile.ora' from spfile;

File created.

SQL> create spfile='+CRSNEW' from pfile='/u01/app/grid/asmfile.ora';

File created.

SQL>

10. Using gpnptool get command check the location of new asmspfile.

$ORACLE_HOME/bin/gpnptool get

------gpnptool information

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

grid@srv1:/u01/app> cd 12.1.0/grid/bin/

grid@srv1:/u01/app/12.1.0/grid/bin> pwd

/u01/app/12.1.0/grid/bin

grid@srv1:/u01/app/12.1.0/grid/bin> ls -ltr gpnptool

-rwxr-xr-x 1 grid oinstall 9422 Jul 3 18:10 gpnptool

grid@srv1:/u01/app/12.1.0/grid/bin> ./gpnptool get

Warning: some command line parameters were defaulted. Resulting command line:

./gpnptool.bin get -o-

<?xml version="1.0" encoding="UTF-8"?><gpnp:GPnP-Profile Version="1.0" xmlns="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:gpnp="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:orcl="http://www.oracle.com/gpnp/2005/11/gpnp-profile" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.grid-pnp.org/2005/11/gpnp-profile gpnp-profile.xsd" ProfileSequence="12" ClusterUId="90521a2aafd3ef6bbfdec9507ff8dc0d" ClusterName="smrplm" PALocation=""><gpnp:Network-Profile><gpnp:HostNetwork id="gen" HostName="*"><gpnp:Network id="net1" IP="192.168.56.0" Adapter="eth0" Use="public"/><gpnp:Network id="net2" IP="192.168.10.0" Adapter="eth1" Use="cluster_interconnect"/></gpnp:HostNetwork></gpnp:Network-Profile><orcl:CSS-Profile id="css" DiscoveryString="+asm" LeaseDuration="400"/><orcl:ASM-Profile id="asm" DiscoveryString="" SPFile="+CRSNEW/smrplm/ASMPARAMETERFILE/registry.253.1045417681" Mode="legacy"/><ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#"><ds:SignedInfo><ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/><ds:SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"/><ds:Reference URI=""><ds:Transforms><ds:Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"/><ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"> <InclusiveNamespaces xmlns="http://www.w3.org/2001/10/xml-exc-c14n#" PrefixList="gpnp orcl xsi"/></ds:Transform></ds:Transforms><ds:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"/><ds:DigestValue>bqJG+H/ZAK6chk6GRRDzintmnUY=</ds:DigestValue></ds:Reference></ds:SignedInfo><ds:SignatureValue>B5Dv3kVGKEyY467rOxrr0Ss0sQ6w91M+XfHvDuLGPOW+AExaEWZBpzZ/e8srJCW9yOezxsSMp8YToPbnE9VadmChikYTjJrTBUFjv/wBHO4js7bMCtUB3d3rAR1uIw7xKvqH3bwQWaQLODqI/EDAuvWsOzf9eOnBULhpHJGfrds=</ds:SignatureValue></ds:Signature></gpnp:GPnP-Profile>

Success.

grid@srv1:/u01/app/12.1.0/grid/bin>

11. Stop and start Cluster node on local node then remote node

srv1:/u01/app/12.1.0/grid/bin # crsctl stop crs

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'srv1'

CRS-2673: Attempting to stop 'ora.crsd' on 'srv1'

CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'srv1'

CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'srv1'

CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'srv1'

CRS-2673: Attempting to stop 'ora.mgmtdb' on 'srv1'

CRS-2673: Attempting to stop 'ora.smrplm.pretaf_preconnect.svc' on 'srv2'

CRS-2677: Stop of 'ora.smrplm.pretaf_preconnect.svc' on 'srv2' succeeded

CRS-2673: Attempting to stop 'ora.smrplm.pretaf.svc' on 'srv1'

CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'srv1' succeeded

CRS-2673: Attempting to stop 'ora.scan1.vip' on 'srv1'

CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'srv1' succeeded

CRS-2677: Stop of 'ora.smrplm.pretaf.svc' on 'srv1' succeeded

CRS-2673: Attempting to stop 'ora.smrplm.db' on 'srv1'

CRS-2672: Attempting to start 'ora.smrplm.pretaf.svc' on 'srv2'

CRS-2677: Stop of 'ora.scan1.vip' on 'srv1' succeeded

CRS-2672: Attempting to start 'ora.scan1.vip' on 'srv2'

CRS-2677: Stop of 'ora.smrplm.db' on 'srv1' succeeded

CRS-2677: Stop of 'ora.mgmtdb' on 'srv1' succeeded

CRS-2673: Attempting to stop 'ora.MGMTLSNR' on 'srv1'

CRS-2676: Start of 'ora.scan1.vip' on 'srv2' succeeded

CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'srv2'

CRS-2677: Stop of 'ora.MGMTLSNR' on 'srv1' succeeded

CRS-2672: Attempting to start 'ora.MGMTLSNR' on 'srv2'

CRS-2676: Start of 'ora.smrplm.pretaf.svc' on 'srv2' succeeded

CRS-2672: Attempting to start 'ora.smrplm.pretaf_preconnect.svc' on 'srv2'

CRS-2674: Start of 'ora.smrplm.pretaf_preconnect.svc' on 'srv2' failed

CRS-2673: Attempting to stop 'ora.DATA.dg' on 'srv1'

CRS-2677: Stop of 'ora.DATA.dg' on 'srv1' succeeded

CRS-2673: Attempting to stop 'ora.FRA.dg' on 'srv1'

CRS-2677: Stop of 'ora.FRA.dg' on 'srv1' succeeded

CRS-2676: Start of 'ora.MGMTLSNR' on 'srv2' succeeded

CRS-2679: Attempting to clean 'ora.mgmtdb' on 'srv2'

CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'srv2' succeeded

CRS-2681: Clean of 'ora.mgmtdb' on 'srv2' succeeded

CRS-2672: Attempting to start 'ora.mgmtdb' on 'srv2'

CRS-2676: Start of 'ora.mgmtdb' on 'srv2' succeeded

CRS-2673: Attempting to stop 'ora.CRS.dg' on 'srv1'

CRS-2673: Attempting to stop 'ora.CRSNEW.dg' on 'srv1'

CRS-2673: Attempting to stop 'ora.srv1.vip' on 'srv1'

CRS-2677: Stop of 'ora.CRSNEW.dg' on 'srv1' succeeded

CRS-2677: Stop of 'ora.CRS.dg' on 'srv1' succeeded

CRS-2673: Attempting to stop 'ora.asm' on 'srv1'

CRS-2677: Stop of 'ora.asm' on 'srv1' succeeded

CRS-2677: Stop of 'ora.srv1.vip' on 'srv1' succeeded

CRS-2672: Attempting to start 'ora.srv1.vip' on 'srv2'

CRS-2676: Start of 'ora.srv1.vip' on 'srv2' succeeded

CRS-2673: Attempting to stop 'ora.ons' on 'srv1'

CRS-2677: Stop of 'ora.ons' on 'srv1' succeeded

CRS-2673: Attempting to stop 'ora.net1.network' on 'srv1'

CRS-2677: Stop of 'ora.net1.network' on 'srv1' succeeded

CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'srv1' has completed

CRS-2677: Stop of 'ora.crsd' on 'srv1' succeeded

CRS-2673: Attempting to stop 'ora.crf' on 'srv1'

CRS-2673: Attempting to stop 'ora.storage' on 'srv1'

CRS-2673: Attempting to stop 'ora.mdnsd' on 'srv1'

CRS-2673: Attempting to stop 'ora.ctssd' on 'srv1'

CRS-2673: Attempting to stop 'ora.gpnpd' on 'srv1'

CRS-2677: Stop of 'ora.storage' on 'srv1' succeeded

CRS-2677: Stop of 'ora.crf' on 'srv1' succeeded

CRS-2677: Stop of 'ora.mdnsd' on 'srv1' succeeded

CRS-2677: Stop of 'ora.ctssd' on 'srv1' succeeded

CRS-2673: Attempting to stop 'ora.evmd' on 'srv1'

CRS-2673: Attempting to stop 'ora.asm' on 'srv1'

CRS-2677: Stop of 'ora.gpnpd' on 'srv1' succeeded

CRS-2677: Stop of 'ora.evmd' on 'srv1' succeeded

CRS-2677: Stop of 'ora.asm' on 'srv1' succeeded

CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'srv1'

CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'srv1' succeeded

CRS-2673: Attempting to stop 'ora.cssd' on 'srv1'

CRS-2677: Stop of 'ora.cssd' on 'srv1' succeeded

CRS-2673: Attempting to stop 'ora.gipcd' on 'srv1'

CRS-2677: Stop of 'ora.gipcd' on 'srv1' succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'srv1' has completed

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

srv1:/u01/app/12.1.0/grid/bin #

srv1:/u01/app/12.1.0/grid/bin # pwd

/u01/app/12.1.0/grid/bin

srv1:/u01/app/12.1.0/grid/bin #

srv1:/u01/app/12.1.0/grid/bin # crsctl start crs

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

srv1:/u01/app/12.1.0/grid/bin # ps -ef |grep pmon

root 2180 4539 0 18:46 pts/0 00:00:00 grep --color=auto pmon

oracle 6945 1 0 18:44 ? 00:00:00 ora_pmon_smrplm1

grid 19349 1 0 18:44 ? 00:00:00 asm_pmon_+ASM1

srv1:/u01/app/12.1.0/grid/bin # crsctl status resource -t

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

Name Target State Server State details

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

Local Resources

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

ora.CRS.dg

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

ora.CRSNEW.dg

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

ora.DATA.dg

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

ora.FRA.dg

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

ora.asm

ONLINE ONLINE srv1 Started,STABLE

ONLINE ONLINE srv2 Started,STABLE

ora.net1.network

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

ora.ons

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

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

Cluster Resources

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

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE srv1 STABLE

ora.LISTENER_SCAN2.lsnr

1 ONLINE ONLINE srv2 STABLE

ora.LISTENER_SCAN3.lsnr

1 ONLINE ONLINE srv2 STABLE

ora.MGMTLSNR

1 ONLINE ONLINE srv2 169.254.70.63 192.16

8.10.2,STABLE

ora.cvu

1 ONLINE ONLINE srv2 STABLE

ora.mgmtdb

1 ONLINE ONLINE srv2 Open,STABLE

ora.oc4j

1 ONLINE ONLINE srv2 STABLE

ora.scan1.vip

1 ONLINE ONLINE srv1 STABLE

ora.scan2.vip

1 ONLINE ONLINE srv2 STABLE

ora.scan3.vip

1 ONLINE ONLINE srv2 STABLE

ora.smrplm.db

1 ONLINE ONLINE srv1 Open,STABLE

2 ONLINE ONLINE srv2 Open,STABLE

ora.smrplm.pretaf.svc

1 ONLINE ONLINE srv2 STABLE

ora.smrplm.pretaf_preconnect.svc

1 ONLINE ONLINE srv1 STABLE

ora.srv1.vip

1 ONLINE ONLINE srv1 STABLE

ora.srv2.vip

1 ONLINE ONLINE srv2 STABLE

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

srv1:/u01/app/12.1.0/grid/bin #

12. Check phyiscial location of new file

grid@srv2:~> asmcmd

ASMCMD> lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED NORMAL N 512 4096 1048576 30717 21401 10239 5581 0 Y CRS/

MOUNTED NORMAL N 512 4096 1048576 122877 122250 40959 40645 0 N CRSNEW/

MOUNTED EXTERN N 512 4096 1048576 12287 3289 0 3289 0 N DATA/

MOUNTED EXTERN N 512 4096 1048576 12287 9154 0 9154 0 N FRA/

ASMCMD> cd CRSNEW/

ASMCMD> ls

smrplm/

ASMCMD> cd smrplm

ASMCMD> ls

OCRFILE/

ASMCMD> cd OCRFILE/

ASMCMD> ls

REGISTRY.255.1045415429

ASMCMD> exit

13. Check the disk group and instance status

SQL> select name,state,usable_file_mb,total_mb,free_mb,required_mirror_free_mb from v$asm_diskgroup;

NAME STATE USABLE_FILE_MB TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB

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

FRA MOUNTED 9134 12287 9134 0

DATA MOUNTED 3289 12287 3289 0

CRSNEW MOUNTED 40596 122877 122152 40959

CRS DISMOUNTED 0 0 0 0

SQL>

srv1:/u01/app/12.1.0/grid/bin # su oracle

oracle@srv1:/u01/app/12.1.0/grid/bin> . oraenv

ORACLE_SID = [+ASM1] ? smrplm1

The Oracle base has been changed from /u01/app/grid to /u01/app/oracle

oracle@srv1:/u01/app/12.1.0/grid/bin> sqlplus sys/123@pretaf as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 10 18:57:52 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

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

Advanced Analytics and Real Application Testing options

SQL> select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME from gv$instance;

INSTANCE_NUMBER INSTANCE_NAME HOST_NAME

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

1 smrplm1 srv1

2 smrplm2 srv2

srv1:~ # crsctl check cluster -all

**************************************************************

srv1:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

srv2:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

Wednesday 3 June 2020

RAC - Convert one Node to Standard 2 node RAC database

Scenario Preview:

We have One node RAC database setup 12c with Active - Passive database server. Now we have to convert it into 2 node active -active database .

Status of one node rac database

oracle@srv2:~> srvctl config database -d RAC

Database unique name: RAC

Database name: RAC

Oracle home: /u01/app/oracle/product/12.1.0/db_1

Oracle user: oracle

Spfile: +DATA/RAC/PARAMETERFILE/spfile.274.1038413121

Password file: +DATA/RAC/PASSWORD/pwdrac.262.1038412435

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: FRA,DATA

Mount point paths:

Services: staf

Type: RACOneNode

Online relocation timeout: 5

Instance name prefix: RAC2

Candidate servers: srv1,srv2

OSDBA group: dba

OSOPER group: oper

Database instances:

Database is administrator managed

Active node instance status database

oracle@srv2:~> srvctl status database -d rac

Instance RAC2_1 is running on node srv1

Online relocation: INACTIVE

oracle@srv2:~>

Convert node one database to 2 node RAC Database

oracle@srv1:~> srvctl convert database -d RAC -c RAC

Check the status of 2 node RAC database

oracle@srv1:~> srvctl config database -d rac

Database unique name: RAC

Database name: RAC

Oracle home: /u01/app/oracle/product/12.1.0/db_1

Oracle user: oracle

Spfile: +DATA/RAC/PARAMETERFILE/spfile.274.1038413121

Password file: +DATA/RAC/PASSWORD/pwdrac.262.1038412435

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: FRA,DATA

Mount point paths:

Services: staf

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: oper

Database instances: RAC2_1

Configured nodes: srv1

Database is administrator managed

oracle@srv1:~>

Add instance to rac database and mention server name (here srv2), Rember all commands are operated on active on node database

oracle@srv1:~> srvctl add instance -d RAC -i rac_2 -n srv2

Start instance of node2 instance

oracle@srv1:~> srvctl start instance -d rac -i rac_2

Check the status of database

oracle@srv1:~> srvctl status database -db rac

Instance RAC2_1 is running on node srv1

Instance rac_2 is running on node srv2

Check the configuration status

oracle@srv1:~> srvctl config database -db oradb

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

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

oracle@srv1:~> srvctl config database -db rac

Database unique name: RAC

Database name: RAC

Oracle home: /u01/app/oracle/product/12.1.0/db_1

Oracle user: oracle

Spfile: +DATA/RAC/PARAMETERFILE/spfile.274.1038413121

Password file: +DATA/RAC/PASSWORD/pwdrac.262.1038412435

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: FRA,DATA

Mount point paths:

Services: staf

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: oper

Database instances: RAC2_1,rac_2

Configured nodes: srv1,srv2

Database is administrator managed

Check clusterware status

oracle@srv2:~> crsctl status resource -t

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

Name Target State Server State details

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

Local Resources

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

ora.CRS.dg

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

ora.DATA.dg

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

ora.FRA.dg

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

ora.asm

ONLINE ONLINE srv1 Started,STABLE

ONLINE ONLINE srv2 Started,STABLE

ora.net1.network

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

ora.ons

ONLINE ONLINE srv1 STABLE

ONLINE ONLINE srv2 STABLE

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

Cluster Resources

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

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE srv1 STABLE

ora.LISTENER_SCAN2.lsnr

1 ONLINE ONLINE srv2 STABLE

ora.LISTENER_SCAN3.lsnr

1 ONLINE ONLINE srv2 STABLE

ora.MGMTLSNR

1 ONLINE ONLINE srv1 169.254.52.81 192.16

8.10.1,STABLE

ora.cvu

1 ONLINE ONLINE srv2 STABLE

ora.mgmtdb

1 ONLINE ONLINE srv1 Open,STABLE

ora.oc4j

1 ONLINE ONLINE srv2 STABLE

ora.rac.db

1 ONLINE ONLINE srv1 Open,STABLE

2 ONLINE ONLINE srv2 Open,STABLE

ora.rac.staf.svc

1 ONLINE ONLINE srv1 STABLE

ora.scan1.vip

1 ONLINE ONLINE srv1 STABLE

ora.scan2.vip

1 ONLINE ONLINE srv2 STABLE

ora.scan3.vip

1 ONLINE ONLINE srv2 STABLE

ora.srv1.vip

1 ONLINE ONLINE srv1 STABLE

ora.srv2.vip

1 ONLINE ONLINE srv2 STABLE

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

oracle@srv2:~>

HOME

Thursday 16 July 2020

Patching - Apply PSU Patch 21255373 on 12.1.0.2.0 grid before upgrade.

OPatch Utility Information

Monday 13 July 2020

RAC - Change OCR Vote disk and ASM Spfile location to new disk group

Wednesday 3 June 2020

RAC - Convert one Node to Standard 2 node RAC database