Category: 11g

Query to check blocking sessions in Oracle database

 Query to check blocking sessions in Oracle database from v$lock:

SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘) || sid sess,
  id1,
  id2,
  lmode,
  request,
  type
FROM v$lock
WHERE (id1, id2, type) IN
  (SELECT id1, id2, type FROM v$lock WHERE request > 0
  )
ORDER BY id1,
  request;

Useful srvctl commands

Useful srvctl commands for ORACLE RAC MAINTENANCE

srvctl -help or srvctl -v


srvctl commands to startup database:

srvctl start database -d db_name [-o start_options] [-c connect_str|-q]
srvctl start database -d db_name [-o open]
srvctl start database -d db_name -o nomount
srvctl start database -d db_name -o mount

srvctl commands to shutdown database:

srvctl stop database -d db_name [-o stop_options] [-c connect_str|-q]
srvctl stop database -d db_name [-o normal]
srvctl stop database -d db_name -o transactional
srvctl stop database -d db_name -o immediate
srvctl stop database -d db_name -o abort


srvctl config database
srvctl config database -d db_name [-a] [-t]


srvctl start listener -n node_name [-l listener_names]
srvctl stop listener -n node_name [-l listener_names]


srvctl status listener [-n node_name] [-l listener_names] 
srvctl config listener -n node_name

What is Split Brain Syndrome in Oracle RAC?

Split brain syndrome occurs when the Oracle RAC nodes are unable to communicate with each other via private interconnect, but the communication between client and RAC node is maintained. This can cause data Integrity issues  when the same block is read or updated by two nodes and changes done from one node are overwritten by the other node because the block being changed is not locked.

When a node fails, the failed node is prevented from accessing all the shared disk devices and groups. This methodology is called I/O Fencing, Disk Fencing or Failure Fencing.

The node which first detects that one of the node is not accessible will evict that node from the RAC cluster group.This problem is solved by configuring the heartbeat connections through the same communication channels that are used to access the clients.

What is Flashback feature in Oracle Database?

Oracle Flashback Database and restore points are related data protection features that enable you to rewind data back in time to correct any problems caused by logical data corruption or user errors within a designated time window.

Points to Remember

– Flashback Database command can be run either from SQLPLUS or using RMAN Utility.

– FLASHBACK DATABASE command can be used to rewind the database to a target time, SCN or a log sequence number.

– Flashback command works by undoing the changes made to the data files that exist when you run the command.

– Flashback can fix only logical failures, not physical failures.

– If the database control file is restored from backup or re-created, then all existing flashback log information is discarded.

– Avoid using FLASHBACK DATABASE with a target time or SCN that coincides with a NOLOGGING operation, it can cause block corruption.




How to enable Flashback Database feature in Oracle?

 Prerequisites for Flashback Database and Guaranteed Restore Points

Flashback Database

Configure the following database settings before enabling Flashback Database:

-Your database must be running in ARCHIVELOG mode.

-You must have a fast recovery area enabled.

-For Oracle Real Application Clusters (Oracle RAC) databases, the fast recovery area must be in a clustered file system or in ASM.

Guaranteed Restore Points

To use guaranteed restore points,the COMPATIBLE initialization parameter must be set to 10.2.0 or greater.


Steps to enable Flashback Database:

1. Connect to sqlplus as sysdba and set  the desired value for Flashback retention target using below command.


SQL>ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;

Here, Flashback retention target is set to window of 3 days(4320 Minutes), default value is 1 day (1440 Minutes).


2. Enable the Flashback Database feature for the whole database using the following command:

SQL>ALTER DATABASE FLASHBACK ON;


3.Use the following command to check if Flashback Database is enabled for your target database:

SQL>SELECT FLASHBACK_ON FROM V$DATABASE;



Steps to create Database link from Oracle Database to Microsoft SQLServer Database:

Steps to create Database link from Oracle Database to Microsoft SQLServer Database:


Pre-requisites


1. Check Network communication is Opened from Oracle Database Server to MSSQL Database Server using telnet
2. Download the Oracle Gateway Server Software same as your Oracle Database version.


Installation of Oracle Gateway Software
=========


Hope you have downloaded the Dg4msql software for sql server 


1> Create a directory as gateway under $ORACLE_BASE directory. 


2) Invoke the GUI , Select Oracle Gateway for MYSQL and enter the sql server details at the time of installation 


3) Before completing the installation, OUI invokes the NETCA and create a new listener for gateway with new port (ex:1526) 


Note; In case if you have defined any bash profile for TNS_ADMIN, then please comment it 


4. Follow the below note for pre-req for gateway listener 


How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, AIX,HP-UX) post install ( Doc ID 562509.1 ) 


edit your gateway listener as recommended in the above note. 


5. create a tnsentry in $ORACLE_HOME/network/admin 


6. export TNS_ADMIN=$gateway home/network/admin 


lsnrctl stop <gateway listener> 


lsnrctl start <gateway listener> 


tnsping <gateway alias> 


conn / as sysdba 


create public database link <test> connect to “<sql server user>” identified by “<password>” using ‘<tnsnames.ora>’ 


select * from dual@test; 

Script to check free space and used space in Oracle Database tablespaces

select a.tablespace_name,
       a.bytes_alloc/(1024*1024) “TOTAL ALLOC (MB)”,
 a.physical_bytes/(1024*1024) “TOTAL PHYS ALLOC (MB)”,
       nvl(b.tot_used,0)/(1024*1024) “USED (MB)”,
       (nvl(b.tot_used,0)/a.bytes_alloc)*100 “% USED”
from ( select tablespace_name,
       sum(bytes) physical_bytes,
sum(decode(autoextensible,’NO’,bytes,’YES’,maxbytes)) bytes_alloc
       from dba_data_files
       group by tablespace_name ) a,
     ( select tablespace_name, sum(bytes) tot_used
       from dba_segments
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
–and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
and   a.tablespace_name not like ‘UNDO%’
order by 1
 –order by 5
/ 

RMAN-03009: failure of allocate command on ORA_AUX_SBT_TAPE_1 channel



This is an error during Database Clone using RMAN. I was trying to restore database using RMAN Backup 


RMAN-00571: ===========================================================


RMAN-00569: ===============
ERROR MESSAGE STACK FOLLOWS ===============


RMAN-00571: ===========================================================


RMAN-03002: failure of Duplicate Db command
RMAN-05501: aborting duplication of target database


RMAN-03009: failure of allocate command on ORA_AUX_SBT_TAPE_1 channel 
ORA-19554: error allocating device, device type: SBT_TAPE, device name:


ORA-27211: Failed to load Media Management Library


Additional information: 


Cause: Backup Configuration was modified.


Solution:
Use the below command to Resolve the error and continue with Database Restore


rman auxiliary / 
run
{
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
duplicate database to ‘DEV’ backup location ‘/Daily_Backup/Backup_16042018’;
}
exit;
EOF