Years_Months_Days_Hours_Days

SELECT ID,REQUEST_REGISTERED_ON,RECTIFIED_ON,
TRUNC(MONTHS_BETWEEN(RECTIFIED_ON,REQUEST_REGISTERED_ON)/12) AS YEARS,
MOD(TRUNC(MONTHS_BETWEEN(RECTIFIED_ON,REQUEST_REGISTERED_ON)),12) AS MONTHS,
TRUNC(RECTIFIED_ON-ADD_MONTHS(REQUEST_REGISTERED_ON,TRUNC(MONTHS_BETWEEN(RECTIFIED_ON,REQUEST_REGISTERED_ON)))) AS DAYS,
trunc(RECTIFIED_ON - REQUEST_REGISTERED_ON)  as TOTAL_days,
trunc(((RECTIFIED_ON - REQUEST_REGISTERED_ON)-trunc(RECTIFIED_ON - REQUEST_REGISTERED_ON))*24)     AS "DIFF_IN_HOURS",
TRUNC(((((RECTIFIED_ON - REQUEST_REGISTERED_ON)-trunc(RECTIFIED_ON - REQUEST_REGISTERED_ON))*24)-
(trunc(((RECTIFIED_ON - REQUEST_REGISTERED_ON)-trunc(RECTIFIED_ON - REQUEST_REGISTERED_ON))*24)))*60)
 as DIFF_IN_MNTS
FROM NSC_USER.REQUEST_T  WHERE ID = ID AND RECORD_STATUS=1 ;

ORA-01591: lock held by in-doubt distributed transaction

SQL> select local_tran_id from dba_2pc_pending;

LOCAL_TRAN_ID

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

7.55.64129

SQL> rollback force '7.55.64129';

Rollback complete.

SQL> exec dbms_transaction.purge_lost_db_entry('7.55.64129');

PL/SQL procedure successfully completed.

SQL> select local_tran_id from dba_2pc_pending;

no rows selected

RAC Commands

RAC Specific  init.ora parameters

The following are important initialization parameters that must have the same values
across all the instances of an RAC database:

• CLUSTER_DATABASE
• CONTROL_FILES
• DB_BLOCK_SIZE
• COMPATIBLE
• DB_NAME
• ACTIVE_INSTANCE_COUNT
• UNDO_MANAGEMENT
• RESULT_CACHE_MAX_SIZE
• DB_UNIQUE_NAME
• CLUSTER_DATABASE_INSTANCES
• INSTANCE_TYPE
• REMOTE_LOGIN_PASSWORDFILE
• DB_RECOVERY_FILE_DEST
• DB_RECOVERY_FILE_DEST_SIZE

The following are important initialization parameters that must have a unique value
across all instances of an RAC database:

• INSTANCE_NAME
• INSTANCE_NUMBER
• UNDO_TABLESPACE
• THREAD

Srvctl commands

-- Add

 srvctl add database -d MIS -o /app/oracle/product/10.2.0/RACDB

 srvctl add instance -d MIS -i MIS1 -n rac1

 Config

 srvctl config
 srvclt config -v
 srvctl config database -d MIS
 srvctl config database -d MIS -a
 srvctl config nodeapps -a -g -s -i -n rac1
 srvctl config listener -n rac1
 srvctl config service -d MIS
 srvctl config service MIS -s MIS_RAC -a

--Start/Stop

srvctl stop database -d MIS -o immediate
srvctl stop instance -i MIS1 -d MIS -o immediate
srvctl stop asm +ASM1 -n oracls1
srvctl stop asm +ASM2 -n oracls2
srvctl stop listener
srvctl stop nodeapps -n oracls1
srvctl stop nodeapps -n oracls2
./crsctl stop crs

--Status

srvctl status database -d MIS
srvctl status instance -i MIS1 -d MIS
srvctl statsu nodeapps -n oracls1

CRS Tracing

export ORA_CRSDEBUG_ALL=1 to 5

see metalink note 357808 for details

Srvctl Tracing

export SRVM_TRACE=true

 Backup Voting Disks

  dd if=/dev/raw/raw11 of=/home/oracle/votingdsk.bkp

 OCR Commands

ocrconfig -export /tmp/dba/exp_ocr.dmp -s online
ocrconfig  -showbackup
ocrconfig -backup loc dirname
ocrconfig -restore
ocrcheck
 
Determing ocr location

cat /etc/oracle/ocr.loc

 oifcfg Commands

oifcfg iflist
oifcfg iflist -p -n
oifcfg getif -type public
oifcfg getif
oifcfg getif -n rac2
oifcfg getif -if eth2

--Crs Statistics

crs_stat -t
crs_stat -t -v

--Olsnodes

olsnode -n

 cluvfy

cluvfy stage -pre crsinst -n rac1,rac2
cluvfy comp nodereach -n rac1,rac2 -verbose
cluvfy stage -post crsinst -n rac1,rac2 -verbose
cluvfy comp clu -n rac1,rac2 -verbose
cluvfy comp clumgr -n rac1,rac2 -verbose
cluvfy comp nodeapp -n rac1,rac2 -verbose
cluvfy comp crs -n rac1,rac2 -verbose
cluvfy comp ocr -n rac1,rac2 -verbose
cluvfy stage -pre dbcfg -n rac1,rac2 -d -verbose
cluvfy stage -pre dbinst -n rac1,rac2 -verbose
cluvfy comp ofcr -n all

 Check CRS

crsctl check crsd
crsctl check cssd
crsctl check evmd
 
Query voting disks

crsctl query css votedisk

crsctl query crs softwareversion
crsctl query activeversion

List Modules

crsctl lsmodules crs
crsctl lsmodules evm

Archivelog Mode On RAC

This article highlights the differences between resetting the archive log mode on a single node instance and a Real Application Clusters (RAC).

In Stand Alone Database

    ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/ORCL/archive/' SCOPE=spfile;
    ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile;

    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    ALTER DATABASE ARCHIVELOG;
    ALTER DATABASE OPEN;

The ALTER DATABASE ARCHIVELOG command can only be performed if the database in mounted in exclusive mode. This means the whole
clustered database must be stopped before the operation can be performed. First we set the relevant archive parameters.


    ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/ORCL/archive/' SCOPE=spfile;
    ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile;

Since we need to mount the database in exclusive mode we must also alter the following parameter.

    ALTER SYSTEM SET cluster_database=FALSE SCOPE=spfile;

From the command line we can stop the entire cluster using the following.

    $ srvctl stop database -d ORCL

With the cluster down we can connect to a single node and issue the following commands.

    STARTUP MOUNT;
    ARCHIVE LOG START;
    ALTER DATABASE ARCHIVELOG;
    ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile;
    SHUTDOWN IMMEDIATE;

Notice that the CLUSTER_DATABASE parameter has been reset to it's original value. Since the datafiles and spfile are
shared between all instances this operation only has to be done from a single node.

From the command line we can now start the cluster again using.

    $ srvctl start database -d ORCL

The current settings place all archive logs in the same directory. This is acceptible since the thread (%t) is part
of the archive format preventing any name conflicts between instances. If node-specific locations are required the
LOG_ARCHIVE_DEST_1 parameter can be repeated for each instance with the relevant SID prefix.