Monday, August 28, 2023

Unable to shutdown database due to Active Call

 Shutdown was hung with these messages in the alert log:


SHUTDOWN: waiting for active calls to complete.

*** 2015-12-22 10:59:09.298

SHUTDOWN: waiting for active calls to complete.


Why its happned?

The message "SHUTDOWN: waiting for active calls to complete" typically appears in the database alert file. This message indicates that the Oracle Database is in the process of shutting down, but it is waiting for existing connections or active sessions to complete their ongoing tasks before it can completely shut down.


When you initiate a shutdown of the database, the DBMS tries to ensure that all ongoing transactions are completed or rolled back properly to maintain data integrity. If there are active connections or sessions that are still performing operations, the shutdown process will wait for those operations to finish before it can finalize the shutdown.


Here are a few points to consider:


1. **Patience:** Depending on the nature of the active operations and their progress, the shutdown process might take some time to complete. Be patient and allow the system to finish its activities.


2. **Identifying Active Sessions:** It's a good idea to monitor the active sessions on the database using management tools like Oracle Enterprise Manager or SQL queries. This will help you understand what tasks are causing the shutdown delay.


3. **Emergency Shutdown:** If you need to shut down the database immediately and cannot wait for active sessions to complete, you might need to perform a forced or emergency shutdown. However, this should be a last resort, as it can potentially lead to data corruption or loss.


4. **Investigating Long-Running Transactions:** If you frequently encounter delays during shutdown due to long-running transactions, you should investigate the cause. This might involve optimizing queries, tuning the database, or redesigning certain processes to prevent such delays.


5. **Graceful Shutdown:** Whenever possible, try to perform a graceful and planned shutdown of the database during maintenance windows to allow ongoing tasks to complete normally.

Wednesday, May 17, 2023

How To Determine Which File System is the Run or Patch File System Using a SQL Query?

 To determine the RUN file system:


SELECT EXTRACTVALUE(XMLType(text),'//oa_context_file_loc')
 appl_top, status
  FROM fnd_oam_context_files
 WHERE name NOT IN ('TEMPLATE','METADATA','config.txt')
  AND CTX_TYPE='A'
 AND (status IS NULL OR UPPER(status) IN ('S','F'))
  AND EXTRACTVALUE(XMLType(text),'//file_edition_type') = 'run';


To determine the PATCH files system:


SELECT EXTRACTVALUE(XMLType(text),'//oa_context_file_loc')
 appl_top, status
  FROM fnd_oam_context_files
 WHERE name NOT IN ('TEMPLATE','METADATA','config.txt')
  AND CTX_TYPE='A'
 AND (status IS NULL OR UPPER(status) IN ('S','F'))
  AND EXTRACTVALUE(XMLType(text),'//file_edition_type') = 'patch';

Friday, May 5, 2023

Patching in 19C RAC Environment



Patching in 19C RAC Environment

Download: Patch 34762026: GI RELEASE UPDATE 19.18.0.0.0 (REL-JAN230131)


 

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

1.  Perform pre-checks 

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



ps -ef | grep pmon

ps -ef | grep tns

ps -ef | grep d.bin

ps -ef | grep mrp


/oracle/grid/19c/bin/kfod op=patches #Check applied patches

/oracle/grid/19c/bin/kfod op=patchlvl  #Check Current Patch Level

/oracle/grid/19c/bin/crsctl query crs activeversion -f #Check active version

/oracle/grid/19c/bin/crsctl query crs softwarepatch

/oracle/grid/19c/bin/crsctl stat res -t


From Grid Home


export ORACLE_HOME=/oracle/grid/19c

export PATH=$ORACLE_HOME/bin:$PATH

export PATH=$ORACLE_HOME/OPatch:$PATH


opatch lsinv

opatch lspatches  

opatch lsinv | grep -i applied

opatch lspatches | sort -nr


From DB Home


srvctl status database -d PROD


select name,open_mode,database_role from v$database;


export ORACLE_HOME=/oracle/db/19c

export PATH=/oracle/db/19c/bin:$PATH

export PATH=/oracle/db/19c/OPatch:$PATH


opatch lsinv

opatch lspatches  

opatch lsinv | grep -i applied


On ORACLE_HOME validate the database component 


select name,open_mode,database_role from V$database;


show parameter cluster


set lines 200 pages 500

col owner for a15

col OBJECT_NAME for a35

select owner,object_name,object_type,status from dba_objects where status='INVALID';


col COMP_NAME for a40

select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;


col ACTION_TIME for a30

col action for a30

col version for a10

col BUNDLE_SERIES for a30

col COMMENTS for a47

select ACTION_TIME,ACTION,VERSION,BUNDLE_SERIES,COMMENTS from dba_registry_history;


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

2. Perform Conflict Checks on each nodes before patching

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


GRID Home


export ORACLE_HOME=/oracle/grid/19c

export PATH=$ORACLE_HOME/bin:$PATH

export PATH=$ORACLE_HOME/OPatch:$PATH


$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/oracle/32900083/32895426/32904851


$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/oracle/32900083/32895426/32916816


$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/oracle/32900083/32895426/32915586


$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/oracle/32900083/32895426/32918050


$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/oracle/32900083/32895426/32585572


DB Home


export ORACLE_HOME=/oracle/db/19c

export PATH=$ORACLE_HOME/bin:$PATH

export PATH=$ORACLE_HOME/OPatch:$PATH



$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/oracle/32900083/32895426/32904851


$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/oracle/32900083/32895426/32916816


$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/oracle//32900083/32876380


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

3. Run OPatch SystemSpace Check

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


GRID HOME

Check if enough free space is available on the ORACLE_HOME filesystem for the patches to be applied as given below:


For Grid Infrastructure Home, as home user:


Create file /tmp/patch_list_gihome.txt with the following content:


% cat /tmp/patch_list_gihome.txt


 /home/oracle/32900083/32895426/32904851

 /home/oracle/32900083/32895426/32916816

 /home/oracle/32900083/32895426/32915586

 /home/oracle/32900083/32895426/32918050

 /home/oracle/32900083/32895426/32585572


Run the opatch command to check if enough free space is available in the Grid Infrastructure Home:


$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_gihome.txt


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

DB HOME

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

Create file /tmp/patch_list_dbhome.txt with the following content:


cat /tmp/patch_list_dbhome.txt


 /home/oracle/32900083/32895426/32904851

 /home/oracle/32900083/32895426/32916816

 /home/oracle/32900083/32876380


Run opatch command to check if enough free space is available in the Database Home:


$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_dbhome.txt



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

5. Stop Oracle HOME in rolling manner 

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


/oracle/db/19c/bin/srvctl stop home -o /oracle/db/19c -s RACNODE1_JUL21_PSU_08Sept.txt -n racnode1


If there are any application specific listeners please stop it.


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

6. Unlock GI Stack (AS root user)

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


/oracle/grid//19c/crs/install/rootcrs.sh -prepatch


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

7.  Apply Patches on GI Home in Rolling manner from grid user

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


export ORACLE_HOME=/oracle/grid/19c

export PATH=$ORACLE_HOME/bin:$PATH

export PATH=$ORACLE_HOME/OPatch:$PATH



/oracle/grid/19c/OPatch/opatch apply -oh /oracle/grid/19c -local -silent /home/oracle/34762026/34863894


/oracle/grid/19c/OPatch/opatch apply -oh /oracle/grid/19c -local -silent /home/oracle/34762026/34768569


/oracle/grid/19c/OPatch/opatch apply -oh /oracle/grid/19c -local -silent /home/oracle/34762026/34768559


/oracle/grid/19c/OPatch/opatch apply -oh /oracle/grid/19c -local -silent /home/oracle/34762026/34765931


/oracle/grid/19c/OPatch/opatch apply -oh /oracle/grid/19c -local -silent /home/oracle/34762026/33575402



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

8. Verification

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


$ORACLE_HOME/OPatch/opatch lsinv | grep -i applied

$ORACLE_HOME/OPatch/opatch lspatches | sort -nr


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

9. Run Pre-script for DB Component from oracle user

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


/home/oracle/34762026/34765931/custom/scripts/prepatch.sh -dbhome /oracle/db/19c

/home/oracle/34762026/34768559/custom/scripts/prepatch.sh -dbhome /oracle/db/19c


--- Due to patch conflict, If require need to perform rollback of patch ####


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

10. Apply Patches on RDBMS Home from oracle user

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


/oracle/db/19c/OPatch/opatch apply -oh /oracle/db/19c -local -silent /home/oracle/34762026/34863894


/oracle/db/19c/OPatch/opatch apply -oh /oracle/db/19c -local -silent /home/oracle/34762026/34768569


/oracle/db/19c/OPatch/opatch apply -oh /oracle/db/19c -local -silent /home/oracle/34762026/34768559


/oracle/db/19c/OPatch/opatch apply -oh /oracle/db/19c -local -silent /home/oracle/34762026/34765931


/oracle/db/19c/OPatch/opatch apply -oh /oracle/db/19c -local -silent /home/oracle/34762026/33575402


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

11. Verification

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


$ORACLE_HOME/OPatch/opatch lsinv | grep -i applied

$ORACLE_HOME/OPatch/opatch lspatches | sort -nr


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

12. Run Post-script for DB Component from Oracle USER

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


/home/oracle/34762026/34768559/custom/scripts/postpatch.sh -dbhome /oracle/db/19c


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

13. Post patch rootadd_rdbms script execution from root user:

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


/oracle/grid/19c/rdbms/install/rootadd_rdbms.sh

/oracle/grid/19c/crs/install/rootcrs.sh -postpatch


Issue:

CRS-1705: Found 0 configured voting files but 1 voting files are required, terminating to ensure data integrity; details at (:CSSNM00065:) in /home/oracle/app/oracle/diag/crs/racnode1/crs/trace/ocssd.trc

CRS-2883: Resource 'ora.cssd' failed during Clusterware stack start.

CRS-4406: Oracle High Availability Services synchronous start failed.

CRS-41053: checking Oracle Grid Infrastructure for file permission issues

CRS-4000: Command Start failed, or completed with errors.

2023/05/05 11:06:05 CLSRSC-117: Failed to start Oracle Clusterware stack from the Grid Infrastructure home /oracle/grid/19c

Died at /oracle/grid/19c/crs/install/crspatch.pm line 1899.

The command '/oracle/grid/19c/perl/bin/perl -I/oracle/grid/19c/perl/lib -I/oracle/grid/19c/crs/install -I/oracle/grid/19c/xag /oracle/grid/19c/crs/install/rootcrs.pl -postpatch' execution failed


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

14. Start DB Homes from oracle user

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


/oracle/db/19c/bin/srvctl start home -o /oracle/db/19c -s RACNODE1_JUL21_PSU_08Sept.txt  -n racnode1 


If there are any application specific listeners please start it.


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

15. Perform on NODE2

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

Preform above Steps from 5 to 14 on second node.

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

16. Verify below for Cluster status post patching all nodes 

######


ps -ef | grep pmon

ps -ef | grep  tns

ps -ef | grep d.bin

ps -ef | grep mrp


/oracle/grid/19c/bin/kfod op=patches

/oracle/grid/19c/bin/kfod op=patchlvl

/oracle/grid/19c/bin/crsctl query crs activeversion -f

/oracle/grid/19c/bin/crsctl query crs softwarepatch

/oracle/grid/19c/bin/crsctl stat res -t



export ORACLE_HOME=/oracle/grid/19c

export PATH=/oracle/grid/19c/bin:$PATH

export PATH=/oracle/grid/19c/OPatch:$PATH


opatch lsinv


opatch lspatches  

opatch lsinv | grep -i applied


########

17. Perform Post Patch Task 

###########


Post patching task only on prod after completion of patching on all nodes:


cd /oracle/db/19c/OPatch


./datapatch -verbose


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

18. Validate the result

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

select name,open_mode,database_role from V$database;


show parameter cluster


set lines 200 pages 500

col owner for a15

col OBJECT_NAME for a35

select owner,object_name,object_type,status from dba_objects where status='INVALID';


col COMP_NAME for a40

select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;


col ACTION_TIME for a30

col action for a30

col version for a10

col BUNDLE_SERIES for a30

col COMMENTS for a47

select ACTION_TIME,ACTION,VERSION,BUNDLE_SERIES,COMMENTS from dba_registry_history;

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


Thursday, April 20, 2023

What to do with a query, if a query is generating more redo/archive log?

If a query is generating excessive redo/archive logs in Oracle, it is important to identify the root cause and address it.

Here are some steps you can take:

  1. Review the query execution plan to identify any full table scans or other expensive operations that may be causing excessive log generation.

  2. Check if the query is updating or inserting a large number of rows, as this can result in significant log generation.

  3. Review the database configuration settings, such as the log buffer size and log file size, and adjust them if necessary.

  4. Consider partitioning tables or indexes if the query is accessing large tables or indexes.

  5. Tune the database parameters to optimize performance and reduce log generation. This may include adjusting the buffer cache size, increasing the number of redo log groups, or adjusting the log writer parameters.

  6. Consider using the NOLOGGING option for the query if data loss is acceptable and log generation needs to be minimized.

  7. Finally, if none of the above steps resolve the issue, consider contacting Oracle support for further assistance.

Wednesday, March 15, 2023

ORA-15137 After Rolling Back Failed Patch at GI_HOME



 Case 1. Update the patch level on all nodes, As root user

$ clscfg -patch

Example:

# <GRID_HOME>/bin/clscfg -patch
clscfg: -patch mode specified
clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 12c Release 1.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.

And validate as root user

$ crsctl query crs softwarepatch

Example:

# <GRID_HOME>/bin/crsctl query crs softwarepatch
Oracle Clusterware patch level on node host01 is [1964077727].

# <GRID_HOME>/bin/crsctl query crs softwarepatch
Oracle Clusterware patch level on node host02 is [1964077727].

If both were at same level,stop rolling patch mode from one of the node as root user

$ crsctl stop rollingpatch

Then startup the cluster and create asm diskgroup.

Example:

# <GRID_HOME>/bin/crsctl stop rollingpatch
CRS-1161: The cluster was successfully patched to patch level [1964077727].

Friday, February 24, 2023

Enable Archive Log Mode In Oracle RAC

 1. stop the database service.

    srvctl stop database -d PROD

2. start the database in mount state.

srvctl start database -d PROD -o mount

3. enable archive log mode.

alter database archivelog;

4. Restart the database service (using srvctl)

srvctl stop database -d PROD

srvctl start database -d PROD

5. set the archive destination to a ASM DISK

SQL> alter system set log_archive_dest_1='LOCATION=+ARCH/' scope=both sid='*';

System altered.

SQL> archive log list

SQL> alter system switch logfile;

System altered.

How to setup Domain Name System (DNS) on Solaris 10



We will use Perl script called “h2n” that creates the appropriate files for a DNS server. 


Download h2n Scripts---Click Here


Step 1:- Edit /etc/hosts file


  • The new domain server will be on system 192.168.137.25 which is called SCAN.

  • The following /etc/host file has been edit to contain all the DNS entries that are required:

192.168.137.25  scan.indiandba.com  scan

  • Assume domain name is to be indiandba.com

  • Assume network is 192.168.137

  • Create a domain run directory to contain the Named services files called /var/named.


Step 2:- Create named directory and copy h2n script

$ mkdir -p /var/named
$ cp <from the ./h2n-2.56 directory>/h2n /var/named


Step 3:- Run the h2n script file as follows: 

$cd /var/named
$./h2n -d indiandba.com -n 192.168.137 -u oracle@indinadba.com


Step 4:- Download “named.root” file, rename the file to db.cache and copy to the /var/named directory.


Step 5:- Copy named.conf file to /etc directory

$ cp /var/named/named.conf /etc


Step 6:- Edit the file /etc/nsswitch.conf and add the entry dns to the host entry as follows: 

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

#

# /etc/nsswitch.files:
#
# An example file that could be copied over to /etc/nsswitch.conf; it
# does not use any naming service.
#
# "hosts:" and "services:" in this file are used only if the
# /etc/netconfig file has a "-" for nametoaddr_libs of "inet" transports.
passwd:     files
group:      files
hosts:      files dns
networks:   files
protocols:  files
:          :          :          :          :          :
:          :          :          :          :          :

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


Step 7:- Create or edit a file called /etc/defaultdomain and add the single entry: 


$vi /etc/defaultdomain

indiandba.com


Step 8:- Execute the domainname command to set the domain as follows:


$domainname ‘cat /etc/defaultdomain’


Step 9:- Edit /etc/resolv.conf file


$vi /etc/resolv.conf
domain indiandba.com
nameserver      192.168.137.25


Step 10:- Start Named Services

$/usr/sbin/in.named &


Step 11:- To enabled the respository use


$svcs -a | grep dns
disabled         10:15:21 svc:/network/dns/server:default
disabled         10:15:22 svc:/network/dns/client:default

$svcadm enable /network/dns/server

$svcs -a | grep dns
online           10:15:21 svc:/network/dns/server:default
online           10:15:22 svc:/network/dns/client:default



Note:---- /network/dns/server must be online for DNS to run properly and initiate if the DNS server is rebooted.


Step 12:- Configurating Clients to use DNS To use DNS, clients need to modify the /etc/resolv.conf, and /etc/nsswitch.conf as above. The /etc/defaultdomain file must also be created and establsihed as above.




Reference: -

http://www.logiqwest.com/dataCenter/Demos/RunBooks/DNS/DNSsetup.html