Why and How to manage Sun Cluster resources running for Oracle Database

  Why ?

To do activity for Oracle database like patching , upgrade or switchover/failover ..etc and this sever is running on Sun cluster HA services , you have to do disable/enable and monitor/unmonitored for certain services configured in your cluster resources and group.

How ?

Below are most important commands you may need to use in Sun Cluster management  :

1- Checking Cluster group status:

clrg status

=== Cluster Resource Groups ===

Group Name      Node Name        Suspended      Status

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

Group_res       Server1         No             Online

                Server2         No             Offline

 

2- Check Cluster resources details of the group :

clrs status

=== Cluster Resources ===

Resource Name      Node Name        State       Status Message

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

DB-srv            Server1           Online      Online

                   Server2           Offline     Offline

 

DB-lsnr            Server1           Online      Online

                   Server2           Offline     Offline

 

Ip_HA             server1            Online      Online - LogicalHostname online.

                  server2            Offline     Offline

 

DB_zfs-rs        server1            Online      Online

3- Stop/shutdown certain recourse

clrs disable DB-srv

clrs enable DB-srv

4- Disable the cluster management for certain resource

clrs unmonitor DB-srv

clrs monitor DB-srv

5- Show configured things in resource :

clrs show -v DB-srv

6- Switchover to server2

clrg switch -n server2 Group_res

A good reference you may refer to is :

https://docs.oracle.com/cd/E19787-01/820-7359/x-4805j/index.html

Why and How to create a shell loop script to run same file in multiple directories

  Why ?

To run on command or execute the same file in different directory you will need to do this whether using a find command or with shell script to be in more flexible .

How ?

This is a shell script file  :

1- create a shell file(example):

vim check_file.sh

2- sample of for loop to execute "file.sh" and echo command in multiple directories (dir1, dir2, dir3 , dir4) :

#!/bin/sh

for dir in /path/dir*; do

        echo $dir

        $dir/file.sh

done;

Why and How to stop/start Oracle standby database server under data guard broker ( DGMGRL) for maintenance

 

 Why ?

For maintenance purpose in the standby server or primary server you will need to stop Data Guard things prior reboot or do the activity in the server OS things for example , thus; to avoid  any issue in the data sync between primary and stand by you should stop these shipping data and it will be re-shipped once everything revert back to normal.

How ?

I am assuming you already configure a Data guard broker and everything running normally :

1- check current data guard status:

dgmgrl / as sysdba

DGMGRL> show configuration

Configuration - orcl_dg_config

  Protection Mode: MaxPerformance

  Members:

  Orcl_prim   - Primary database

    Orcl_stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 25 seconds ago)

2- Stop redo transport (stop sending data from primary to standby) :

DGMGRL> EDIT DATABASE '<primary_db>' SET STATE='TRANSPORT-OFF';

3- Stop redo apply (stop applying the received data in standby DB )

DGMGRL> EDIT DATABASE '<standby_db>' SET STATE='APPLY-OFF';

4- Disable broker ( to avoid any failover/switchover during the activity).

 DGMGRL> disable configuration

5- Shutdown database:

sqlplus / as sysdba

shutdown immediate;

quit

lsnrctl stop

6-Perform maintenance activities

7- Start database in the respective open_mode (read write primary, or mounted standby) , In below case you will open standby DB in a read only; if you have an active data guard license .

sqlplus / as sysdba

startup open ;

quit

lsnrctl start

or first mount it and then open in read only mode :

SQL> startup nomount;

SQL> alter database mount;

SQL>  ALTER DATABASE OPEN READ ONLY;

8- Enable broker

DGMGRL> enable configuration

 9. Start redo apply

DGMGRL> EDIT DATABASE '<standby_db>' SET STATE='APPLY-ON';

10. Start redo transport

 

DGMGRL> EDIT DATABASE '<primary_db>' SET STATE='TRANSPORT-ON';

10. Check dataguard status

 

After a while and things got recovered, you can check the status :

 

dgmgrl / as sysdba

DGMGRL> show configuration

Configuration - orcl_dg_config

  Protection Mode: MaxPerformance

  Members:

  Orcl_prim   - Primary database

    Orcl_stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 25 seconds ago)

Why and How to check TDE details in Oracle

 Why ?

TDE is encryption in tablespace level , this will protect the tablespace from works unless the wallet used while created this tablespace is exist and open in the database, you will need to check the status details in DB specially if you are new to manage this DB system or you may need to show auditor / security team the configured things in your DB.

How ?

 

To check TDE in DB use the below :

1- Is tablespace encrypted ?:

SELECT tablespace_name, encrypted, status FROM dba_tablespaces ;

2- Information about the keystore is displayed using the V$ENCRYPTION_WALLET view:

SET LINESIZE 200

COLUMN wrl_parameter FORMAT A50

SELECT * FROM v$encryption_wallet;

3- Details of TDE:

SELECT * FROM V$ENCRYPTION_WALLET;

SELECT * FROM V$ENCRYPTION_KEYS;

SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES;

SELECT * FROM V$ENCRYPTED_TABLESPACES;

4- sqlnet.ora will contain the path of created file , you will do this while creating TDE .

 ENCRYPTION_WALLET_LOCATION=

  (SOURCE=(METHOD=FILE)(METHOD_DATA=

    (DIRECTORY=/u01/app/oracle/product/19.0.0/dbhome_1/admin/ORCL/encryption_keystore/)))

Why and how to check/kill connected sessions in your Oracle DB

  Why ?

All users/Application are connected to the DB server if it is dedicated session configured therefore you will find theses connected as a session in session views in your DB and as PID (process) in the OS of server, any query run to do DDL or DML in the DB can be stopped by killing it is connected session, Also, prior any shutdown with option immediate or abort you may check these affected session or you may shutdown with transactional option and kill these connected session manually .

How ?

 To check connected session in RAC because it is more than one node always use gv$ opation instead of v$ in all views , here I am using gv$ option for both RAC and none RAC  :

1- check connected session :

set linesize 300

col username format A10

col program format A50

col osuser format A10

select b.INST_ID,

       substr(a.spid,1,9) pid,

       substr(b.sid,1,5) sid,

       substr(b.serial#,1,5) ser#,

       substr(b.username,1,10) username,

--       b.server,

       substr(b.osuser,1,8) os_user,

       substr(b.program,1,30) program

from gv$session b, gv$process a

where

b.paddr = a.addr

and type='USER'

order by spid;

2- To kill connected session use  :

ALTER SYSTEM KILL SESSION 'sid,serial#';

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;