Generally used unix/linux commands in RAC Cluster

Generally used unix/linux commands in RAC Cluster

SRVCTL or Server Control Utility is used to start and stop the database and instances, manage configuration information, and to add, move or remove instances and services.
To Start a RAC database
srvctl start nodeapps -n <nodename>
srvctl start asm -n <nodename>
srvctl start database -d <dbname>
options: srvctl start database -d <dbname> -o open | -o mount | -o nomount
Stop a RAC database
srvctl stop database -d <dbname> -o immediate
options: srvctl stop database -d <dbname> -o normal | -o transactional | -o immediate | -o abort
srvctl stop asm -n <nodename>
options: srvctl stop asm -n <nodename> -o immediate
srvctl stop nodeapps -n <nodename>
To check status and configurations
Nodeapps:
srvctl status nodeapps -n <nodename>
srvctl config nodeapps -n <nodename>
ASM:
srvctl status asm -n <nodename>
srvctl config asm -n <nodename>
Database:
srvctl status database -d <dbname>
srvctl config database -d <dbname> (shows instances name, node and oracle home)
Instance:
srvctl status instance -d <dbname> -i instancename
Services:
srvctl status service -d <dbname>


To start and stop instances
srvctl start instance -d <dbname> -i instancename
srvctl stop instance -d <dbname> -i instancename
To start, stop and manage services
srvctl status service -d <dbname>
srvctl config service -d <dbname>
srvctl start service -d <dbname> -s servicename
srvctl stop service -d <dbname> -s servicename

#Disclaimer: - These commands are general purpose commands. Please contact me over mail or phone for implementing in any production System.

Author – Akash Pramanik.

Database Administrator – IBM

Mail – akash007.pramanik@gmail.com

Phone – +91 9804944189

Creating A DB Link :-
-----------------------------------------------
create database link <DB Link Name> connect to <Username> identified by <Password> using 'Database Identifier';




Dropping a DB link :-
-----------------------------------------------
drop database link <DB Link Name>;

Simple query to Change the site name from back-end :-
-------------------------------------------------------------------------------
UPDATE FND_PROFILE_OPTION_VALUES set profile_option_value='<New Site Name>' where profile_option_id=125 and level_id=10001
/
commit
/
exit









#Note - The values may slightly change depending on environment.

Query to find all the concurrent programs running in any instance, execute this query in toad or sql/pl-sql developer to get the report in XL.

    SELECT fcr.request_id,
    fr.responsibility_name,
           DECODE(fcpt.user_concurrent_program_name,
                  'Report Set',
                  'Report Set:' || fcr.description,
                  fcpt.user_concurrent_program_name) CONC_PROG_NAME,
           argument_text PARAMETERS,
           NVL2(fcr.resubmit_interval,
                'PERIODICALLY',
                NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
           DECODE(NVL2(fcr.resubmit_interval,
                       'PERIODICALLY',
                       NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
                  'PERIODICALLY',
                  'EVERY ' || fcr.resubmit_interval || ' ' ||
                  fcr.resubmit_interval_unit_code || ' FROM ' ||
                  fcr.resubmit_interval_type_code || ' OF PREV RUN',
                  'ONCE',
                  'AT :' ||
                  TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
                  'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
           fu.user_name USER_NAME,
           requested_start_date START_DATE
      FROM apps.fnd_concurrent_programs_tl fcpt,
           apps.fnd_concurrent_requests    fcr,
           apps.fnd_user                   fu,
           fnd_responsibility_tl              fr,
           apps.fnd_conc_release_classes   fcrc
     WHERE fcpt.application_id = fcr.program_application_id
       AND fcpt.concurrent_program_id = fcr.concurrent_program_id
       AND fcr.requested_by = fu.user_id
       and fcr.responsibility_id=fr.responsibility_id
       AND fcr.phase_code = 'P'
       AND fcr.requested_start_date > SYSDATE
       AND fcpt.LANGUAGE = 'US'
       AND fcrc.release_class_id(+) = fcr.release_class_id
       AND fcrc.application_id(+) = fcr.release_class_app_id;


Mail me at - akash007.pramanik@gmail.com if you like this. Also feel free to comment for any issues.

Simple query to check Database locks:-
-----------------------------------------------------------
break on sid_serial;

SELECT l.session_id||','||v.serial# sid_serial,
       l.ORACLE_USERNAME ora_user,
       o.object_name,
       o.object_type,
       DECODE(l.locked_mode,
          0, 'None',
          1, 'Null',
          2, 'Row-S (SS)',
          3, 'Row-X (SX)',
          4, 'Share',
          5, 'S/Row-X (SSX)',
          6, 'Exclusive',
          TO_CHAR(l.locked_mode)
       ) lock_mode,
       o.status,
       to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
FROM dba_objects o, gv$locked_object l, v$session v
WHERE o.object_id = l.object_id
      and l.SESSION_ID=v.sid
order by 2,3;

Simple query to check Database growth in GB with respect to time---
-------------------------------------------------------------------------------------
select to_char(Trunc(creation_time,'MM'), 'MON RRRR') "Month",
sum(bytes)/1024/1024/1024 "Growth in GB"
from sys.v_$datafile
where creation_time > SYSDATE-9999
group by Trunc(creation_time,'MM')
order by Trunc(creation_time,'MM');

Simplest query to check the existing application and Database nodes in your database:
-----------------------------------------------------------------------------------------------------------
set lines 150
set pages 200
col NODE_NAME for a10
col NODE_NAME for a20
col SERVER_ADDRESS for a18
col HOST for a15
col DOMAIN for a18
col SUPPORT_CP for a3
col SUPPORT_FORMS for a3
col SUPPORT_WEB for a3
col SUPPORT_ADMIN for a3
col STATUS for a10
select NODE_NAME, SERVER_ADDRESS, HOST, DOMAIN,SUPPORT_DB D, SUPPORT_CP C, SUPPORT_FORMS F,SUPPORT_WEB w, SUPPORT_ADMIN A, STATUS from apps.fnd_nodes;