Oracle DBA Online Test(MCQ) Intermediate Level

 

Oracle Network Configuration Files

Oracle Network Configuration Files 

Introduction:

To communicate with other systems (server machines to client machines or client machines  to server machines)  we need three network configuration files in order to communicate with the systems  in oracle  three files need to configure that is listener.ora,tnsnames.ora,sqlnet.ora,oracle net enables a network connection between a client and database server oracle net is a software component that resides on both the client  and the database server.oracle net is layered on top of a network protocol -rules that determine how applications access the network and how data is subdivided into packets for transmission across the network . 

The example files below are relevant for an Oracle installation and instance with the following values.

• HOST : chaitanyaoracledba.blogspot.com

• ORACLE_HOME : /u01/app/oracle/product/12.1.0.2/db

• ORACLE_SID : orcl

• Service : orcl

• DOMAIN : blogspot.com

listener.ora

The "listerner.ora" file contains server side network configuration parameters. It can be found in the "$ORACLE_HOME/network/admin" directory on the server. Here is an example of a basic "listener.ora" file from Linux. We can see the listener has the default name of "LISTENER" and is listening for TCP connections on port 1521. Notice the reference to the hostname "chaitanyaoracledba.blogspot.com". If this is incorrect, the listener will not function correctly.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =      

      (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanyaoracledba.blogspot.com)(PORT = 1521))

    )

  )

After the "listener.ora" file is amended the listener should be restarted or reloaded to allow the new configuration to take effect.

$ # Restart

$ lsnrctl stop

$ lsnrctl start

$ # Or Reload.

$ lsnrctl reload

The listener defined above doesn't have any services defined. These are created when database instances auto-register with it. In some cases you may want to manually configure services, so they are still visible even when the database instance is down. If this is the case, you may use a "listener.ora" file like the following.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanyaoracledba.blogspot.com)(PORT = 1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcl.blogspot.com)

      (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db)

      (SID_NAME = orcl)

    )

  )

If there are multiple database instances on the server, you can added multiple SID_DESC entries inside the SID_LIST section.

tnsnames.ora

The "tnsnames.ora" file contains client side network configuration parameters. It can be found in the "$ORACLE_HOME/network/admin" directory on the client. This file will also be present on the server if client style connections are used on the server itself. Here is an example of a "tnsnames.ora" file.

LISTENER = (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanyaoracledba.blogspot.com)(PORT = 1521))

orcl.blogspot.com =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanyaoracledba.blogspot.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

  )

The alias used at the start of the entry can be whatever you want. It doesn't have to match the name of the instance or service. Notice the PROTOCOL, HOST and PORT match that of the listener. The SERVICE_NAME can be any valid service presented by the listener. You can check the available services by issuing the lsnrctl status or lsnrctl service commands on the database server. Typically there is at least one service matching the ORACLE_SID of the instance, but you can create more.

sqlnet.ora

The "sqlnet.ora" file contains client side network configuration parameters. It can be found in the "$ORACLE_HOME/network/admin" directory on the client. This file will also be present on the server if client style connections are used on the server itself, or if some additional server connection configuration is required. Here is an example of an "sqlnet.ora" file.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

NAMES.DEFAULT_DOMAIN = blogspot.com

# The following entry is necessary on Windows if OS authentication is required.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

There are lots of parameters that can be added to control tracing, encryption, wallet locations etc. These are out of the scope of this article.

Testing

Once the files are present in the correct location and amended as necessary the configuration can be tested using SQL*Plus by attempting to connect to the database using the appropriate username (SCOTT), password (TIGER) and service (orcl.blogspot.com).

$ sqlplus scott/tiger@orcl.blogspot.com

Common Problems

The OS hostname command must return the correct hostname of your database server. If not, fix it so it does.

$ hostname

chaitanyaoracledba.blogspot.com

$

The server must have a correct entry in the "/etc/hosts" file matching the hostname and IP address of the server, as well as the loopback entry for localhost. For example in this case the values are as follows.

127.0.0.1      localhost localhost.localdomain localhost4 localhost4.localdomain4

192.168.0.100  chaitanyaoracledba.blogspot.com  chaitanyaoracledba

 If these are provided by DNS, that is fine also.

If you are using the ORACLE_HOSTNAME environment variable, possibly set in your "/home/oracle/.bash_profile", it must be set to the correct value.

export ORACLE_HOSTNAME=chaitanyaoracledba.blogspot.com

The HOST entry in the "listener.ora" file must point to an active network adapter, either real or loopback. If not, the listener will fail to start.

If the HOST entry in the "listener.ora" file is set to "localhost", the listener will start and accept connections from the local server, but not from other clients. You would typically expect this to be set to the hostname of the database server, although some people use the IP address instead.

For the client to make a connection via the listener, there must be a clear route through the network between the two machines. If you are struggling to connect, check network firewalls and the local firewall (iptables, firewalld, Windows Firewall) on the database server.

Tools for Network configuration

Oracle enables you to manage your network configuration with the following tools

Oracle Net Configuration Assistant

Enterprise manager

Oracle Net Manager

Oracle Net Configuration Assistant:  The oracle universal installer launches Oracle Net Configuration Assistant after the database is installed use oracle net configuration assistant to configure the listening protocol address and service information for an oracle database,during a typical database install,oracle net configuration assistant automatically configures a listener called LISTENER that has TCP/IP listening protocol address for the database.Oracle Net Configuration assistant prompts you to configure a listener name and protocol address of your choice.

use Oracle Network Configuration Assistant for initial network configuration after database installation there after you can use the Oracle Enterprise Manager and Oracle Net Manager to configure and administer your network

Oracle Net Manager:

You can access Oracle Net Manager from the command line or for windows platforms through the start menu

 command line (Unix,Linux or windows )run netmgr

on windows click the start button and select programs,oracle-oracle_home,configuration and migration tools, and then Net Manager  

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

RAC Startup Sequences

 RAC Start up Sequence

1.Brief startup sequence

2.Number of Levels while RAC startup

3.In which Level ,The clusterware services get started

4.In which Level database resources are get started in RAC

Oracle  Restart component of a clusterware monitor and manage clusterware resources in a cluster services/daemon involved to run RAC 

Two stack

Lower stack ------->Oracle high availability---->level 0

Upperstack --------->Clusterware processes

Finally we need to get started the resources are network,Scan,vip,listener,DB services

Level 0----->Init---->Ohasd

Level1------>Agents--->orarootagent and oraagent

Level2------->Clusterware services---->crsd,cssd,evmd,procd

Level3-------->Agents

Level4--------->Resources---->DB,Listener,ASM,Instances,VIP,SCAN

Cluster Ready Services(CRSD): Primary program for managing high availability operations in a cluster,operations included start,stop,monitor and failover operations

Cluster Synchronization Services(OCSSD): Manages node memebership information when node joins or leaves the cluster

Event Management (EVMD): A background process that publishes events that oracle clusterware creates

Oracle Notification Services(ONS): A publish and subscribe for communicating fast application notification(FAN) events

Oracle ProcessMonitor DAEMON(OPROCD): The OPROCD periodically wakes up and checks that the interval since it last awake is with in the expected time.If not then

OPROCD resets the processor and restarts the node,an OPROCD failure results in oracle clusterware restarting the node

# ./crsctl check crs

# ./crsctl check cssd

RAC -TOOLS

crsctl--->root user-->cluster ready service control utility

srvctl--->oracle user-->service control utility

To find the resources status

# ./crs-stat-t

or

# ./crsctl stat res -t

Note: On server boot the clusterware services and resources are started on it own

Still to start manually

First start OHASD--on all nodes manually on rac1,rac2

 ./crsctl start crs

./ crsctl check cluster -all

./crsctl check crs

on any node --can start services of every node on rac1

./crsctl start cluster -all

./crsctl check crs

stopping services on rac1

./crsctl stop cluster -all

./crsctl check cluster -all

on every node rac1,rac2

./crsctl stop crs

./crsctl check crs

How to find the health of the cluster

./crsctl check -all

How to start/stop the clusterware services 

verify the instances up and running  on rac nodes 

$ srvctl status -d database prod

/etc/inittab

In 11g r2 - only one entry will see.

/etc/init.d/init.ohasd run > /dev/null 2>&1 </dev/null

INIT process first spawns init.ohasd process which in turn startsup the Oracle High Availability services Daemon(OHASD).

In turn, OHASD spawns additional clusterware processes at each startup level.

Level 1 - OHASD spawns

cssd agent - cssd

root agent - all root owned ohasd resources.

oraroot agent - oracle owned ohasd resources.

cssdmonitor - Monitors CSSD and node health

Level 2—OHASD rootagent spawns:

        Cluster Ready Services Daemon (CRSD)—

primary daemon responsible for managing cluster resources

        Cluster Time Synchronization Services Daemon (CTSSD)

        Diskmon—provides disk monitoring services

        ASM Cluster File System (ACFS) Drivers

During the second level of startup for Clusterware, the oraagent spawns the following Clusterware processes for 11g R2:

    MDNSD: Used for DNS lookup

    GIPCD: Used for inter-process and inter-node communication

    GPNPD: Grid Plug and Play Profile Daemon

    EVMD: Event Monitor Daemon

    ASM: Resource for monitoring ASM instances

Level 3—CRSD spawns:

    orarootagent: for managing all root-owned CRSD resources

    oraagent:   for managing all Oracle-owned CRSD resources

    Level 4—CRSD rootagent spawns:

        Network resource: To monitor the public network

        SCAN VIP(s): Single Client Access Name Virtual IPs

        Node VIPs: One per node

        ACFS Registery: For mounting ASM Cluster File system

        GNS VIP (optional): VIP for GNS

During this phase for Clusterware startup with 11g R2, the oraagent spawns the following processes:

    ASM Resouce: ASM Instance(s) resource

    Diskgroup: Used for managing/monitoring ASM diskgroups

    DB Resource: Used for monitoring and managing the DB and instances

    SCAN Listener: Listener for single client access name, listening on SCAN VIP

    Listener: Node listener listening on the Node VIP

    Services: Used for monitoring and managing services

    ONS: Oracle Notification Service

    eONS: Enhanced Oracle Notification Service

    GSD: For 9i backward compatibility

    GNS (optional): It is a grid naming service that performs name resolution

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Oracle SQL Tuning

 

SQL Tuning

SQL is running slow

Database is running slow.

Tuning - to improve performance of the database to run faster.

Goal to get tune?

Good Throughput - CPU

Best response time/elapsed time/execution time

CPU Elapsed Time

100% 15min   Worst

1% 1s Best Performance

Using Cost based Optimization , the cost is defined on

CPU and Response time.

Now we to identify the cost the sql.

What happens when SQL Statement been executed?

Three stages

Parser - does check syntax and semantic analysis

Fetch -

Execute - Will execute the plan and send result to user

Fetch : OPtimizer will choose best shortest execution plan using

cost based optimization with dictionary stats.

The plan received by Row Source generator.

then fetch the records

note: Dictionary stats

Number of rows,columns,indexes,row length.

There are many factors dependent on slowness of performance.

Backup jobs - RMAN/DP

DML Bulk Jobs -Developers - last night

Memory - AMM -

Network

Poor Coding(procedures/Packages)

Indexes - fragmentation -

Stats

storage

multiplexing - same disk

Dictionary stats

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

Dictionary stats are

number of rows,row length,columns,indexes

If there are DML changes in the last night with 1 million

records are deleted. Will optimizer knows the stats currently

got deleted ? No

These stats to be collected manually every day night. So with

the updated stats, the optimizer will generate best execution

plan

sql>EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'SCOTT',CASCADE=>TRUE);

How we identify the stats gathered?

    from dba_tables (last_analyzed)

sql>select table_name,last_analyzed from dba_tables where owner like

'SCOTT';

If someone complained that query running slow,no changes

did on the query. Verify gather stats.

Purpose of Tuning?

What happens while sql query executed?

What are goals to tune?

What is optimizer?

How to collect/gather stats? and purpose?

How to check stats gathered?

Optimizer - can control using parameter

---------

Will choose the shortest exection plan.

using parameter optimizer_mode

can set the value to all_rows/first_rows_n

n is multiples of 10

sql>alter system set optimizer_mode='first_rows_10' scope=both;

default all_rows

sql>show parameter optimizer_mode

Manually check the execution plan using cost based optimization.

with tools. To identify the cost of plan for a query

explain plan

or

auto trace

1. Auto trace ( introduced from 10g)

sql>set autotrace traceonly

sql>select * from scott.emp;

sql>set autotrace off

2. using explain plan tool ( Being still used)

will store execution plan in plan_table

 sql>explain plan set statement_id='j1' for select * from scott.emp;

sql>@?/rdbms/admin/utlxpls.sql

How to identify the load on the server ?

What is causing slow on the server?

Using unix top command

will identify the load average and top pid consuming more CPU and mem.

$top

Load average  < 10 (best performance)

Idle - CPU idle - No activity - > 70%

Find PID based on CPU/Mem of oracle commands.

using pid from v$session,v$process can find the sid,serial#,sqlid,username

machine,terminal,program,module

Top

pid

sid,serial from v$process,v$session

using sid

find sql_text from v$sql_area

then use explain plan tool

to find the cost and factor causing slow.

If found rman / dp job... impacting db..100% kill it.

sql>alter system kill session '&sid,&serial#'immediate;

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME

Oracle DBA Online Test(MCQ) For Beginner Questions

 

Redo Log Files in Oracle Database

 Introduction

Redo Log Files(.log): Oracle maintains logs of all the transaction against the database,These transactions are recoreded in files called online redo log files(Redo logs) The main purpose of the redo log files is to hold information as recovery in the event of system failure,redo log stores a log of all changes made to the database the redolog files must perform well and be protected against hardware failures (through software or hardware fault tolerance).if redolog information is lost,one cannot recover the system when a transcation occurs in the database,it is entered in the redo log buffers,while the data blocks affected by the transactions are not immediately written to disk,in an oracle database there are at atleast three or more redolog files,oracle  writes to redolog files in a cyclical order i.e after the first log file is filled ,it writes to the second log file,untill that one is filled .when all the redo log files have been filled,it returns to the first log file and begin overwrite its content with new transaction data.note if the database is running in ARCHIVELOG mode,the database will make a copy of the online redolog files before overwriting them

How to increase the size of the redo log files?

Steps :

`       Add new groups with larger size

        drop existing inactive members.

sql>select member from v$logfile;

  eg: expected : 100m Current : 50M  (recommended 1G-2G in live for faster performance)

Adding New Groups:

sql>ALTER DATABASE

ADD LOGFILE GROUP 4 '/oradata/prod/redo4.log' size 200m;

ALTER DATABASE

ADD LOGFILE GROUP 5 '/oradata/prod/redo5.log' size 200m;

ALTER DATABASE

ADD LOGFILE GROUP 6 '/oradata/prod/redo6.log' size 200m;

sql>select group#,members,bytes/1024/1024,status from v$log;

Use manual log switch to make use of new redo groups.

sql>alter system switch logfile;

Now drop inactive groups of different in size.

sql>ALTER DATABASE DROP LOGFILE GROUP 1;

sql>ALTER DATABASE DROP LOGFILE GROUP 2;

sql>ALTER DATABASE DROP LOGFILE GROUP 3;

IF found active - use log switch to make it inactive

sql>alter system switch logfile;

sql>select group#,members,bytes/1024/1024,status from v$log;

Managing Archive log files

logbuffer(1/3rdor3 secs)-lgwr-logfiles->archiver->arc log files

The process of turning online redo logfiles into offline

redolog files is known has archiving.

The offline redo logfiles are called Archivelog files.

with .arc ext

Its mandatory in production to enable archiving and optional in Development.

The content of archives can be used for recovery of datafiles.

Enabling Archive log mode:

Physical Dest - /archives/prod - .arc

mkdir -p /archives/prod

sql>archive log list

sql>show parameter log_archive_dest_1

format

sql>show parameter log_archive_format

sql>alter system set log_archive_dest_1='LOCATION=/archives/prod' scope=both;

sql>alter system set log_archive_format='%t_%r_%s.arc' scope=spfile;

%t - thread number associated with instance number1

%r - redo sequence

%s - log sequence number

sql>shutdown immediate

sql>startup mount

sql>alter database archivelog;

sql>alter database open;

sql>archive log list

sql>alter system switch logfile;

sql>exit

cd /archives/prod

redologfiles->arch(bg process) -> archivelogfile(.arc)

Here archiver is the background process writes from online

redo log files to off archivelog files when log switch occurs.

sql>show parameter log_archive_max_processes

We can have up to 30 max processes of archiver

$ps -ef | grep arc

default - 4

Note: Info on redolog it may be differ from your environment production,testing,dev naming conventions,and directory structure etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Oracle RAC Patching

Oracle RAC Patching

There are total three methods by which we can apply patch to RAC Cluster Environment on Unix System:

Patching RAC as a single instance (All-Node Patch)

Patching RAC using a minimum down-time strategy (Min. Downtime Patch)

Patching RAC using a rolling strategy - No down time (Rolling Patch)

Patching RAC as a single instance (All-Node Patch)

In this mode, all instances must be down during the whole patching process. OPatch applies the patch to the local node first, then propagates the patch to all the other nodes, and finally updates the inventory.

*    Shutdown all Oracle instances on all nodes

*    Apply the patch to the RAC home on all nodes

*    Bring all instances up

Patching RAC using a minimum down-time strategy (Min. Downtime Patch)

In this mode, OPatch patches the local node, asks users for a sub-set of nodes, which will be the first subset of nodes to be patched. After the initial subset of nodes are patched, Opatch propagates the patch to the other nodes and finally updates the inventory. The downtime would happen between the shutdown of the second subset of nodes and the startup of the initial subset of nodes patched.

*    Shutdown all the Oracle instances on node 1

*    Apply the patch to the RAC home on node 1

*    Shutdown all the Oracle instances on node 2

*    Apply the patch to the RAC home on node 2

*    Shutdown all the Oracle instances on node 3

*    At this point, instances on nodes 1 and 2 can be brought up

*    Apply the patch to the RAC home on node 3

*    Startup all the Oracle instances on node 3

Patching RAC using a rolling strategy - No down time (Rolling Patch)

With this method, there is no downtime. Each node would be patched and brought up while all the other nodes are up and running, resulting in no disruption of the system.

Rolling patching strategy incur no downtime, however, some rolling patches may incur downtime due to post-installation steps, i.e. running sql scripts to patch the actual database. Please refer to patch readme to find out whether post-installation steps require downtime or not.

*    Shutdown all the Oracle instances on node 1

*    Apply the patch to the RAC home on node 1

*    Start all the Oracle instances on node 1

*    Shutdown all the Oracle instances on node 2

*    Apply the patch to the RAC home on node 2

*    Start all the Oracle instances on node 2

*    Shutdown all the Oracle instances on node 3

*    Apply the patch to the RAC home on node 3

*    Start all the Oracle instances on node 3

The algorithm used to decide which method is going to be used is the following:

       If (users specify minimize_downtime)

              patching mechanism = Min. Downtime

       else if (patch is a rolling patch)

              patching mechanism = Rolling

            else

                  patching mechanism = All-Node 

When patches are released, they have a tag as "rolling" or "not rolling" patch. While most patches can be applied in a rolling fashion, some patches cannot be applied in this fashion.

Patches that could potentially be installed on rolling fashion include:

  

*    Patches that do not affect the contents of the database.

*    Patches that are not related to the RAC internode communication infrastructure.

*    Patches that change procedural logic and do not modify common header definitions of kernel modules.

*    This includes client side patches that only affect utilities like export, import, sql*plus, sql*loader, etc.

Note: If you plan to apply a patch that is marked as "not rolling" and want to check if is possible to take advantage of the rolling patch strategy, please contact Oracle Support.

How to determine if a patch is a "rolling patch" or not?

As database user execute the following:

    - 9i or 10gR1: opatch query -is_rolling

    - 10gR2: opatch query -all [unzipped patch location] | grep rolling

    - 10gR2 on Windows: opatch query -all [unzipped patch location] | findstr rolling

    - Later 10gR2 or 11g: opatch query -is_rolling_patch [unzipped patch location]

Also, the type of OPatch can be determined by checking the <Patch Number>/etc/config/inventory file. If the variable online_rac_installable is set to true then the patch is a rolling patch.

Here we are using 3rd method of applying patch i.e. “Rolling Patch”.

Patch 25476126 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.170418

Platform:  Linux 64 Bit

Released: Apr 18, 2017

Some Important Notes:

Patch can be applied to both Oracle Database Home refers to Enterprise Edition or Standard Edition Database software.

The GI PSU patch includes updates for both the Clusterware home and Database home that can be applied in a rolling fashion.

This patch is Database Vault installable.

This patch is Data Guard Standby First Installable.

Data Guard Standby-First Patch Apply for details on how to remove risk and reduce downtime when applying this patch.

GI PSUs are cumulative and include the Database PSU and associated CPU program security content.

Check whether below components are configured in your environment.

Q: Is Audit Vault enabled in your database?

Ans: NO

Q: Is Dataguard configured in your environment?

Ans: NO

Note: If Dataguard is configured in your environment then apply the patch DR first.

Then you can start to apply patch to production DC.

Get the confirmation from client for Patching Activity at least a week prior activity.

Advantages:

·       To get total number of servers to be patched.

·       To download a patch.

·       To upload the patch to target servers.

·       To perform prerequisites.

·       To confirm client if you find any conflict in prerequisites.

·       To inform client that we are ready for patching if no conflict found.

Prerequisites Check:

1.     Apply patch in UAT and Development:

First apply patch in your UAT and development servers and then Production servers.

2.     Minimum Opatch Version:

You must use the OPatch utility version 11.2.0.3.6 or later to apply this patch.

Oracle recommends that you use the latest released OPatch version for 11.2 releases, which is available for download from My Oracle Support patch 6880880 by selecting ARU link for the 11.2.0.0.0 release. Replace the same with existing opatch version by taking backup of the current one.

$opatch version => It should be 11.2.0.3.6 or later.

              

3.     Download Patch:

Download latest PSU patch for the existing Operating System and correct Oracle Version.

For Example:

We have Oracle Linux 6.3 64-bit with Oracle 11.2.0.4 Enterprise Edition

Create new directory on target server and give read permission to the ORA_INST group and this directory must be empty because the PSU patch may include multiple patches and you may notice that there are multiple directories created with distinct bug numbers.

unzip p25476126_112040_<platform>.zip

4.     Perform prerequisites:

You will get any conflict if any after executing below commands. If you will not get any conflict then you are OK to go ahead.

As a grid user:

$opatch lsinventory –detail –oh $GRID_HOME

$opatch prereq CheckConflictAgainstOHWithDetail –ph ./

As an oracle user:

$opatchlsinventory –detail –oh $ORACLE_HOME

$opatch prereq CheckConflictgainstOhWithDetail –ph ./

Also execute below commands and get the inventory details.

As a grid user:

$opatch lsinventory –detail –oh $GRID_HOME

As an oracle user:

$opatch lsinventory –detail –oh $ORACLE_HOME

5.     Client Confirmation:

Initiate mail to client at least a week prior the Patching Activity with below detail stating that “We have completed patching prerequisites and we did not find any conflict”.

Sr. No.

Server

IP Address

Database

Instance

Grid Home

Oracle Home

Operating System

Bit Mode

Current Opatch Version

Required Opatch Version

Patch Number

Conflict

  

Start Here (Plan of Action)

We are applying latest PSU patch using Rolling Patch Method.

Note: We are following 3rd method of applying patching i.e. Rolling Patch and hence do not touch 2nd node while applying patch to 1st node. Once done with 1st node, then you can proceed with 2nd node.Also, this document is for Non-Shared Cluster Environment. For Cluster shared environment, there is a different method to apply patch.

Note: Initiate mail to client prior the activity that “We are starting patching activity in next 10-15 minutes”.

Step 1: Backup

Take tar backup of Both Grid and Oracle Home

By grid User:

$tar -cvzf /backup_location/TAR_BKP_Oracle_Binaries.tar.gz $ORACLE_HOME

By Oracle User:

$tar -cvzf /backup_location/TAR_BKP_Oracle_Binaries.tar.gz $GRID_HOME

Step 2: Data Capture

Capture below details prior the patching activity.

By Grid User:

$opatch lsinventory –detail –oh $GRID_HOME

$crsctl stat res –t

$crsctl check crs

$id

By Oracle user:

$opatch lsinventory –detail –oh $ORACLE_HOME

SQL>set lines 300 pages 3000

SQL>col action_time for a34

SQL>col version for a14

SQL>col comments for a28

SQL>col bundle_series for a28

SQL> col action_name for a23

SQL> col action for a10

SQL> col namespace for a9

SQL> select action,bundle_series,comments,action_time,version from registry$history;

$srvctl status database –d DC -v -f

$srvctl config database –d DC -v -f

$ps –ef | grep pmon

$ps –ef | grep tns

$id

Step 3: Stop Agent Process

You must stop the EM agent processes running from the database home, prior to patching the Oracle RAC database or GI Home

$ps –ef | grep agent

$cd $AGENT_HOME/bin

$./emctl status agent

$./emctl stop agent

 

Step 4: Stop Oracle services  

The below command will stop all Oracle services corresponds to mentioned Oracle Home. Please do not delete status.log file which will be required later to start the service.

 

By oracle user:

 

$srvctl stop home -o $ORACLE_HOME -s status.log -n rac1

$ps –ef | grep pmon

$ps –ef | grep tns

$srvctl status database –d DC

$ps –ef | grep ORA_

Step 5: Unmount ACFS if available

Unmount the Oracle ACFS file systems. See My Oracle Support Document 1494652.1 for unmounting ACFS file systems.

In our environment, we are not using ACFS (ASM Cluster File System).

Step 6: Run pre-root script

Go to the GI Home location and execute below command as the root user:

 

#/u01/app/11.2.0/grid/crs/install/rootcrs.pl -unlock

The above script will perform the following actions before patching:

Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params

 

·       Shutdown of Oracle High Availability Services-managed resources on 'rac1'

·       To stop 'ora.crsd' on 'rac1'

·       Shutdown of Cluster Ready Services-managed resources on 'rac1'

·       To stop 'ora.LISTENER_SCAN3.lsnr' on 'rac1'

·       To stop 'ora.LISTENER.lsnr' on 'rac1'

·       To stop 'ora.registry.acfs' on 'rac1'

·       To stop 'ora.DATA.dg' on 'rac1'

·       To stop 'ora.oc4j' on 'rac1'

·       To stop 'ora.LISTENER_SCAN2.lsnr' on 'rac1'

·       To stop 'ora.cvu' on 'rac1'

·       Stop of 'ora.cvu' on 'rac1' succeeded

·       To start 'ora.cvu' on 'rac2'

·       Stop of 'ora.LISTENER_SCAN3.lsnr' on 'rac1' succeeded

·       To stop 'ora.scan3.vip' on 'rac1'

·       Stop of 'ora.LISTENER.lsnr' on 'rac1' succeeded

·       To stop 'ora.rac1.vip' on 'rac1'

·       Stop of 'ora.LISTENER_SCAN2.lsnr' on 'rac1' succeeded

·       To stop 'ora.scan2.vip' on 'rac1'

·       Start of 'ora.cvu' on 'rac2' succeeded

·       Stop of 'ora.scan3.vip' on 'rac1' succeeded

·       To start 'ora.scan3.vip' on 'rac2'

·       Stop of 'ora.registry.acfs' on 'rac1' succeeded

·       Stop of 'ora.rac1.vip' on 'rac1' succeeded

·       Start 'ora.rac1.vip' on 'rac2'

·       Stop of 'ora.scan2.vip' on 'rac1' succeeded

·       To start 'ora.scan2.vip' on 'rac2'

·       Start of 'ora.scan3.vip' on 'rac2' succeeded

·       To start 'ora.LISTENER_SCAN3.lsnr' on 'rac2'

·       Start of 'ora.rac1.vip' on 'rac2' succeeded

·       Start of 'ora.scan2.vip' on 'rac2' succeeded

·       To start 'ora.LISTENER_SCAN2.lsnr' on 'rac2'

·       Stop of 'ora.oc4j' on 'rac1' succeeded

·       To start 'ora.oc4j' on 'rac2'

·       Start of 'ora.LISTENER_SCAN3.lsnr' on 'rac2' succeeded

·       Stop of 'ora.DATA.dg' on 'rac1' succeeded

·       To stop 'ora.asm' on 'rac1'

·       Stop of 'ora.asm' on 'rac1' succeeded

·       Start of 'ora.oc4j' on 'rac2' succeeded

·       Start of 'ora.LISTENER_SCAN2.lsnr' on 'rac2' succeeded

·       To stop 'ora.ons' on 'rac1'

·       Stop of 'ora.ons' on 'rac1' succeeded

·       To stop 'ora.net1.network' on 'rac1'

·       Stop of 'ora.net1.network' on 'rac1' succeeded

·       Shutdown of Cluster Ready Services-managed resources on 'rac1' has completed

·       Stop of 'ora.crsd' on 'rac1' succeeded

·       To stop 'ora.drivers.acfs' on 'rac1'

·       To stop 'ora.crf' on 'rac1'

·       To stop 'ora.ctssd' on 'rac1'

·       To stop 'ora.evmd' on 'rac1'

·       To stop 'ora.asm' on 'rac1'

·       To stop 'ora.mdnsd' on 'rac1'

·       Stop of 'ora.crf' on 'rac1' succeeded

·       Stop of 'ora.evmd' on 'rac1' succeeded

·       Stop of 'ora.mdnsd' on 'rac1' succeeded

·       Stop of 'ora.ctssd' on 'rac1' succeeded

·       Stop of 'ora.asm' on 'rac1' succeeded

·       To stop 'ora.cluster_interconnect.haip' on 'rac1'

·       Stop of 'ora.cluster_interconnect.haip' on 'rac1' succeeded

·       To stop 'ora.cssd' on 'rac1'

·       Stop of 'ora.cssd' on 'rac1' succeeded

·       To stop 'ora.gipcd' on 'rac1'

·       Stop of 'ora.drivers.acfs' on 'rac1' succeeded

·       Stop of 'ora.gipcd' on 'rac1' succeeded

·       To stop 'ora.gpnpd' on 'rac1'

·       Stop of 'ora.gpnpd' on 'rac1' succeeded

·       Shutdown of Oracle High Availability Services-managed resources on 'rac1' has completed

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

·       Successfully unlock /u01/app/11.2.0/grid

Step 7: Apply Grid Patch

Execute below command as a grid user:

$/u01/app/11.2.0/grid/OPatch/opatch napply -oh /u01/app/11.2.0/grid -local /home/oracle/25476126

Step 8: Run pre-script for Database Home

Run the below command by Oracle User:

 

$/home/oracle/25476126/23054319/custom/server/23054319/custom/scripts/prepatch.sh -dbhome /u01/app/oracle/product/11.2.0/dbhome_1

 

/home/oracle/25476126/23054319/custom/server/23054319/custom/scripts/prepatch.sh completed successfully.

Step 9: Apply DB Patch

Execute below commands by oracle user.

$/u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch napply -oh /u01/app/oracle/product/11.2.0/dbhome_1 –local /home/oracle/25476126/23054319/custom/server/23054319

 

$/u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch apply -oh /u01/app/oracle/product/11.2.0/dbhome_1 -local /home/oracle/25476126/24732075

Step 10: Run Post Script for Database Home

Run below command by oracle user.

 

$/home/oracle/25476126/23054319/custom/server/23054319/custom/scripts/postpatch.sh -dbhome /u01/app/oracle/product/11.2.0/dbhome_1

Note: The above command will read the file permissions of below utilities from params.ora file, parse it, verify it and then finally reapply it.

Reading /u01/app/oracle/product/11.2.0/dbhome_1/install/params.ora..

Reading /u01/app/oracle/product/11.2.0/dbhome_1/install/params.ora..

Parsing file /u01/app/oracle/product/11.2.0/dbhome_1/bin/racgwrap

Parsing file /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl

Parsing file /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvconfig

Parsing file /u01/app/oracle/product/11.2.0/dbhome_1/bin/cluvfy

Verifying file /u01/app/oracle/product/11.2.0/dbhome_1/bin/racgwrap

Verifying file /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl

Verifying file /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvconfig

Verifying file /u01/app/oracle/product/11.2.0/dbhome_1/bin/cluvfy

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/racgwrap

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvconfig

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/cluvfy

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/diskmon.bin

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/lsnodes

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/osdbagrp

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/rawutl

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/ractrans

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/getcrshome

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/gnsd

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/crsdiag.pl

Postpatch completed successfully

Step 11: Post Script for Grid

By Root User:

 

#/u01/app/11.2.0/grid/rdbms/install/rootadd_rdbms.sh

 

 

Step 12: Post Root Script

 

#/u01/app/11.2.0/grid/crs/install/rootcrs.pl -patch

 

Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params

Installing Trace File Analyzer

 

 

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

Note: If you are using ACFS and the message “A system reboot is recommended before using ACFS” is shown then a reboot must be issued before continuing.

Failure to do so will result in running with an unpatched ACFS\ADVM\OKS driver.

Step 13: Start Oracle services  

The below command will start all Oracle services using status.log file corresponds to mentioned Oracle Home.

By oracle user:

 

$srvctl start home -o $ORACLE_HOME -s status.log -n rac1

$ps –ef | grep pmon

$ps –ef | grep tns

$srvctl status database –d DC

$ps –ef | grep ORA_

Step 14: Post Patch Verification

Check everything is running fine on 1st node before applying patch to 2nd node.

By Grid User:

$crscrt check crs

$crsctl stat res –t

$opatch lsinventory –detail –oh $GRID_HOME

By Oracle User:

$srvctl status database -d DC -v -f

$opatch lsinventory –detail –oh $ORACLE_HOME

Check latest patch has been applied successfully to both GRID and Oracle Homes.

Apply patch to 2nd Node

Perform all steps from Step 1 to Step 15.

Step 1: Backup

Step 2: Data Capture

Step 3: Stop Agent Process

Step 4: Stop Oracle services  

$srvctl stop home -o $ORACLE_HOME -s status.log -n rac2

Step 5: Unmount ACFS if available.

Step 6: Run pre-root script

Step 7: Apply Grid Patch

Step 8: Run pre-script for Database Home

Step 9: Apply DB Patch

Step 10: Run Post Script for Database Home

Step 11: Post Script for Grid

Step 12: Post Root Script

Step 13: Start Oracle services  

$srvctl start home -o $ORACLE_HOME -s status.log -n rac2

Step 14: Post Patch Verification

Step 15: Registry Update

The following steps load modified SQL files into the database. For an Oracle RAC environment, perform these steps on only one node.

The catbundle.sql execution is reflected in the dba_registry_history view by a row associated with bundle series PSU.

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP

SQL> @catbundle.sql psu apply

SQL> QUIT

 

Verify below logfiles for any errors.

catbundle_PSU_<database SID>_APPLY_<TIMESTAMP>.log

catbundle_PSU_<database SID>_GENERATE_<TIMESTAMP>.log

 

Step 16: Upgrade Catalog

If you are using the Oracle Recovery Manager, the catalog needs to be upgraded.

$ rman catalog username/password@alias

RMAN> UPGRADE CATALOG;

RMAN> UPGRADE CATALOG;

 

 

You have successfully applied latest PSU patch to both Grid and Oracle Homes.

 

Rollback Plan

 

 

Execute the following manual steps on each node of the cluster in non-shared CRS and DB home environment to roll back the patches.

Step 1: Stop Oracle services  

The below command will stop all Oracle services corresponds to mentioned Oracle Home. Please do not delete status.log file which will be required later to start the service.

 

By oracle user:

 

$srvctl stop home -o $ORACLE_HOME -s status.log -n rac1

$ps –ef | grep pmon

$ps –ef | grep tns

$srvctl status database –d DC

$ps –ef | grep ORA_

Step 2: Unmount ACFS if available

Unmount the Oracle ACFS file systems. See My Oracle Support Document 1494652.1 for unmounting ACFS file systems.

Step 3: Run pre-root Script

Go to the GI Home location and execute below command as the root user:

 

#/u01/app/11.2.0/grid/crs/install/rootcrs.pl -unlock

Step 4: Rollback Grid Infrastructure Patch

By Grid User:

$ /u01/app/11.2.0/grid/OPatch/opatch rollback -local -id 23054319 -oh /u01/app/11.2.0/grid

 

$ /u01/app/11.2.0/grid OPatch/opatch rollback -local -id 24732075 -oh /u01/app/11.2.0/grid

Step 5: Pre-script for Database Home

By Oracle User:

$/home/grid/25476126/23054319/custom/server/23054319/custom/scripts/prepatch.sh -dbhome /u01/app/oracle/product/11.2.0/dbhome_1

Step 6: Rollback Oracle Database Patch

By Oracle User:

$opatch rollback -local -id 23054319 -oh /u01/app/oracle/product/11.2.0/dbhome_1

 

$opatch rollback -local -id 24732075 -oh /u01/app/oracle/product/11.2.0/dbhome_1

Step 7: Post Script for Database Component

By Oracle User:

 

$/home/grid/25476126/23054319/custom/server/23054319/custom/scripts/postpatch.sh -dbhome /u01/app/oracle/product/11.2.0/dbhome_1

Step 8: Run post root script

#/u01/app/11.2.0/grid/rdbms/install/rootadd_rdbms.sh

 

#/u01/app/11.2.0/grid/crs/install/rootcrs.pl -patch

 

Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params

Installing Trace File Analyzer

 

 

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

Note: If you are using ACFS and the message “A system reboot is recommended before using ACFS” is shown then a reboot must be issued before continuing.

Failure to do so will result in running with an unpatched ACFS\ADVM\OKS driver.

Step 9: Start Oracle services  

The below command will start all Oracle services using status.log file corresponds to mentioned Oracle Home.

By oracle user:

 

$srvctl start home -o $ORACLE_HOME -s status.log -n rac1

$ps –ef | grep pmon

$ps –ef | grep tns

$srvctl status database –d DC

$ps –ef | grep ORA_

Note: Verify everything is running fine on Node1 before rolling back patch on 2nd node.

Follow the steps of “Rollback Patch” from Step1 to Step9 on Node 2.

Step 1: Stop Oracle services  

$srvctl stop home -o $ORACLE_HOME -s status.log -n rac1

Step 2: Unmount ACFS if available

Step 3: Run pre-root Script

Step 4: Rollback Grid Infrastructure Patch

Step 5: Pre-script for Database Home

Step 6: Rollback Oracle Database Patch

Step 7: Post Script for Database Component

Step 8: Run post root script

Step 9: Start Oracle services  

Step 10: Start all database instances running from the Oracle home.

Step 11: Run catbundle Script

For each database instance running out of the ORACLE_HOME, connect to the database using SQL*Plus as SYSDBA and run the rollback script as follows:

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP

SQL> @catbundle_PSU_<database SID PREFIX>_ROLLBACK.sql

SQL> QUIT

In an Oracle RAC environment, the name of the rollback script will have the format catbundle_PSU_<database SID PREFIX>_ROLLBACK.sql.

$ opatch lsinventory

All other instances can be started and accessed as usual while you are executing the deinstallation steps.

You have successfully rolled back the Patch.

Note: Info On RAc Patching it may be differ from your environment production,testing,development directory structure,naming conventions etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME