Helpful daily use commands Part 1.

1. Killing Oracle database user sessions manually.

    Login to the database.

     select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username =    '<your_schema>'

     Run the generated SQL.

     In multi instance RAC - execute it on all instances or use gv$sessions.

2. SQL To find if your database is using pfile for spfile.

    select decode(count(*), 1, 'spfile', 'pfile' ) from v$spparameter where rownum=1 and              isspecified='TRUE' ;

3. SQL to find size of a schema.

    select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments where owner like 'schema_name' group by owner order by owner;

4. SQL to check if supplemental logging is enabled and enable if not.

    SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK,    SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUUPLEME SUP SUP
---------------- ----- ------
NO NO NO
SUUPLEME must be YES

To Update:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUUPLEME SUP SUP
---------------- ----- ------
YES NO NO

5. SQL to get the SCN number in database.

  SQL > set num 24
  SQL > select dbms_flashback.get_system_change_number from dual;

    GET_SYSTEM_CHANGE_NUMBER
    ------------------------
             17987351734

6. Sequence details

   select SEQUENCE_OWNER,SEQUENCE_NAME,CACHE_SIZE,LAST_NUMBER from    dba_sequences where SEQUENCE_OWNER in ('Schema_name');

7.To generate the DDL

set pagesize 0
set long 90000
set feedback off
set echo off
spool ddl_object.sql
SELECT DBMS_METADATA.GET_DDL('SEQUENCE',a.SEQ_TP_REGION_ID) from user_objects a where a.OBJECT_TYPE ='SEQUENCE' order by a.OBJECT_NAME;
spool off;

exit

8. Find the long running operations (like backup or export).

  select opname, sofar,totalwork from v$session_longops where  sofar/totalwork <> 1 and totalwork<>0;

9. Active database sessions/connections connections in RAC database.

  select inst_id, username, status, service_name, count(*) from gv$session
group by inst_id, username, status, service_name order by inst_id, service_name, status;

select username, status, machine, service_name, count(*)
      from v$session
group by username, status, machine, service_name
order by  service_name, status;

10.  Query for Encryption Wallet open and close.

    select * from gv$encryption_wallet;

    go to specifis instance and then execute
   ALTER SYSTEM SET ENCRYPTION WALLET CLOSE ;
   ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password";

Some useful RAC 11G commands are documented below.

 STOP SEQUENCE

COMMAND                                   DESCRIPTION
------------------------------------------------------------------------------------------
srvctl stop database -d ORCL              STOP DATABASE 
srvctl stop instance -d ORCL -i ORCL1     STOP first INSTANCE (skip it if running 'stop database' as that will stop both instances)
srvctl stop instance -d ORCL -i ORCL2     STOP second INSTANCE (skip it if running 'stop database' as that will stop both instances)
srvctl stop asm -n NODE1                  STOP ASM INSTANCES on NODE 1 (In 11G , we have OCR on ASM so we cannot stop ASM, but if you have OCR in NON-ASM you should stop it)
srvctl stop asm -n NODE2                  STOP ASM INSTANCES on NODE 2 (In 11G , we have OCR on ASM so we cannot stop ASM, but if you have OCR in NON-ASM you should stop it)
srvctl stop nodeapps -n NODE1             STOP NODEAPPS  on NODE 1
srvctl stop nodeapps -n NODE2             STOP NODEAPPS  on NODE 2
/etc/init.d/init.crs stop                 STOP CRS PROCESSES (AS ROOT USER)

START SEQUENCE

COMMAND                                      DESCRIPTION
-------------------------------------------------------------------------------------------
/etc/init.d/init.crs start                   START CRS PROCESS (AS ROOT USER)
srvctl start asm -n NODE1                    START ASM INSTANCE on node 1
srvctl start asm -n NODE2                    START ASM INSTANCE on node 2
srvctl start database -d ORCL                START DATABASE
srvctl start instance -d ORCL -i ORCL1       START first INSTANCE (skip it if running 'start database' as that will start both instances)
srvctl start instance -d ORCL -i ORCL2       START second INSTANCE (skip it if running 'start database', as taht will start both instances)
srvctl start nodeapps -n NODE1               START NODEAPPS on NODE1
srvctl start nodeapps -n NODE2               START NODEAPPS ON NODE2

OTHER USEFUL COMMANDS

COMMAND                                      DESCRIPTION
---------------------------------------------------------------------
crsctl status resource -t                    Clusterware Resource Status Check
srvctl status database -d ORCL               STATUS OF DATABASE
srvctl stop listener -l LISTENER_NAME        STOP A LISTENER
srvctl start listener -l LISTENER_NAME       START A LISTENER
crsctl stop has                              stop all the clusterware services/ resources on specific node (including DB and listener) (run as root)
crsctl start has                             start all the clusterware services/ resources on specific node (including DB and listener) (run as root)
crsctl stop cluster -all                     to stop csr services on all nodes of clusterware (run as root)
crsctl start cluster -all                    to start crs services on all nodes of clusterware (run as root)
crsctl check has                             to check if ohasd is running/ stopped (run as root)
crsctl enable has                            enable Oracle High Availability Services autostart (run as root)
crsctl disable has                           disable Oracle High Availability Services autostart (run as root)
crsctl config has                            check if Oracle High Availability Services autostart is enabled/ disabled (run as root)
srvctl status nodeapps                       to check the status of services on all nodes
crsctl stop crs                              stop all the clusterware services/ resources ON THAT NODE! (run as root)
crsctl start crs                             start all the clusterware services/ resources ON THAT NODE! (run as root)
cluvfy comp scan -verbose                    Verifying scan status scan_listener                         
srvctl config scan_listener                  Verifying scan port
srvctl relocate scan -i 1 -n NODE1           Relocate scan listener 1 to the mentioned node

Above are some of the commands which we have used regulalry in our RAC system. There are many more RAC commands for which below mealink note id can be referred which contains details of all the commands.

11gR2 Clusterware and Grid Home – What you need to know [ID 1053147.1] [ POST VIEWS : 9871 ]