Common Oracle Clusterware crsctl commands

Oracle Clusterware crsctl commands:

# Check cluster status

crsctl check crs

crsctl check cluster

# Cluster resources

crsctl status resource -t

crsctl status resource

crsctl stat res -t

# Cluster nodes

crsctl query css votedisk

crsctl query css misscount

olsnodes -n

olsnodes -s

# Start/Stop Clusterware on local node

crsctl start crs

crsctl stop crs

# Start/Stop Clusterware on a remote node

crsctl start cluster -n racnode1

crsctl stop cluster -n racnode1

# Start/Stop Clusterware on all nodes

crsctl start cluster -all

crsctl stop cluster -all

# Check CRS stack

crsctl check cssd

crsctl check evmd

crsctl check crsd

# ASM and listeners (resource status)

crsctl status resource ora.asm -t

crsctl status resource ora.LISTENER.lsnr

# View Clusterware version

crsctl query crs activeversion

crsctl query crs softwareversion

# Enable/Disable CRS autostart

crsctl enable crs

crsctl disable crs

----------RAC node failover

# Gracefully stop Clusterware on node1

crsctl stop cluster -n racnode1

# Verify node status

crsctl check cluster -all

# Start node1 Clusterware

crsctl start cluster -n racnode1

# Verify all resources

crsctl stat res -t

Common Oracle RAC srvctl commands

Common Oracle RAC srvctl commands:

# Database

srvctl status database -d RACDB

srvctl start database -d RACDB

srvctl stop database -d RACDB -o immediate

srvctl config database -d RACDB

# Instance

srvctl status instance -d RACDB -i RACDB1

srvctl start instance -d RACDB -i RACDB1

srvctl stop instance -d RACDB -i RACDB1 -o immediate

# Services

srvctl status service -d RACDB

srvctl start service -d RACDB -s APP_SVC

srvctl stop service -d RACDB -s APP_SVC

srvctl relocate service -d RACDB -s APP_SVC -oldinst RACDB1 -newinst RACDB2

# Listener

srvctl status listener

srvctl start listener

srvctl stop listener

srvctl config listener

# SCAN

srvctl status scan

srvctl status scan_listener

srvctl config scan

srvctl start scan_listener

srvctl stop scan_listener

# ASM

srvctl status asm

srvctl start asm

srvctl stop asm

# Node applications

srvctl status nodeapps

srvctl start nodeapps

srvctl stop nodeapps

------------For your RAC node maintenance

# Stop only node1 database instance

srvctl stop instance -d RACDB -i RACDB1 -o immediate

# Start it again

srvctl start instance -d RACDB -i RACDB1

# Verify status

srvctl status database -d RACDB

View running queries in postgresql

View running queries in postgreSQL：

SELECT * FROM pg_stat_activity;

Golden gate commands

Golden gate commands for ggsci:

$ ./ggsci

GGSCI>

info all - Displays status of all Extract, Replicat, and Manager processes.

Start / Stop Processes

• start extract <extract_name> --- Start Extract process

• stop extract <extract_name> --- Stop Extract process

• start replicat <replicat_name> --- Start Replicat process

• stop replicat <replicat_name> --- Stop Replicat process

check info about individual.

• info extract <extract_name>, detail -- Detailed info about Extract

• info replicat <replicat_name>, detail -- Detailed info about Replicat

• info mgr - Displays Manager process status

Check Lag

• lag extract <extract_name> -- Shows Extract lag

• lag replicat <replicat_name> -- Shows Replicat lag

View Statistics

• stats extract <extract_name> -- Displays Extract statistics

• stats replicat <replicat_name> -- Displays Replicat statistics\

Troubleshooting

• view report <extract_name> --- View Extract report

• view report <replicat_name> --- View Replicat report

• send extract <extract_name>, status --- Real-time Extract status

• send replicat <replicat_name>, status --- Real-time Replicat status

• kill extract <extract_name> --- Forcefully terminate Extract (use with caution)

• kill replicat <replicat_name> --- Forcefully terminate Replicat (use with caution)

How to check database and database owner information on psql

How to check database and database owner information on psql:

You can check with \list command

example:

testsub=> \list

List of databases

----------------+-----------+----------+-----------------+-----------------+--------------------------------

How to check connected user on psql

How to check connected user on psql :

\conninfo

example:

testsub=> \conninfo

You are connected to database "testsub" as user "sub" on host "localhost" (address "127.0.0.1") at port "5432".

Extracting DDL of Objects in PostgreSQL(get table ddl in PostgreSQL)

===================================================

2 Ways to get DDL of objects in PostgreSQL

1. The utility pg_dump is used to dump the DDL.

2. Can get the DDL just by running \d.

1. The utility pg_dump is used to dump the DDL.

Table ddl for testtable from TESTDB

$ pg_dump -d TESTDB -s -t testtable

Table ddl for testtable from TESTDB written in to file ddl.sql

$ pg_dump -d TESTDB -s -T testtable -f ddl.sql

Table ddls for all ables from from TESTDB

$ pg_dump -d TESTDB -s

2. Can get the DDL just by running \d.

TESTDB=# \d+ testtable

Oracle how to get an object Id from the rowid

=================================

select dbms_rowid.rowid_object('<RowID>') from dual;

select data_object_id, object_name, object_type from dba_objects where data_object_id in ('<OBJECT_ID>');

Oracle how to save output of the script in xls or html.

Oracle how to save output of the script in xls or html:

=========================================

SET MARKUP HTML ON

SPOOL <file_name>.xls

SET MARKUP HTML ON

SPOOL <file_name>.html

How to check compression enabled on partition table

=======================================

select

a.partition_name, a.tablespace_name, a.compression, a.compress_for , round(sum(a.num_rows / a.blocks),0) rows_per_block,

sum(a.num_rows) num_rows, sum(a.blocks) blocks , sum(b.bytes) /(1024*1024) mb

from dba_tab_partitions a, dba_segments b

where

a.table_name = 'TESTCOMP' and

a.partition_name = b.partition_name and

a.table_name = b.segment_name

group by a.partition_name, a.tablespace_name, a.compression, a.compress_for

order by 1;

Disable Constraints & Enable Constraints

Disable Constraints & Enable Constraints :

===============================

DISABLE:

select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';' from user_constraints;

SQL> alter table TABLE_NAME disable constraint CONSTRAINT_NAME;

SQL> alter table TABLE_NAME disable constraint CONSTRAINT_NAME cascade;

ENABLE:

select 'alter table '||owner||'.'||table_name||' enable constraint '||constraint_name||';' from user_constraints;

SQL> alter table TABLE_NAME enable constraint CONSTRAINT_NAME;

How to enable compression on table.

Enable compression on table:

===================

Basic compression :

ALTER TABLE table_name move COMPRESS ;

Advanced compression :

alter table table_name move ROW STORE COMPRESS ADVANCED;

Please check other posts for compression on partition table.

Script to check long running query

Long running query from database:

set pages 500
col sid for 9999
set lines 200
col MESSAGE for a85
col username format a15
select sid,serial#,inst_id,username,time_remaining,opname,ELAPSED_SECONDS/60 "Minutes elapsed",time_remaining/60 "Minutes remaining" from gv$session_longops where time_remaining>0;

Script to check archive generation per month

Archive generation per month:

===========================

select to_char(FIRST_TIME,'YYYY') YEAR,to_char(FIRST_TIME,'MM') MONTH,to_char(FIRST_TIME,'DD') DAY,SUM(BLOCKS*BLOCK_SIZE)/1024/1024/1024,COUNT(1) from v$archived_log
where THREAD# = 1
and dest_id = 1
group by to_char(FIRST_TIME,'YYYY') ,to_char(FIRST_TIME,'MM') ,to_char(FIRST_TIME,'DD')
order by DAY
/

Sql to check transction details

Sql to check transction details:

=============================

select s.sid

,s.serial#

,s.username

,s.machine

,s.status

,s.lockwait

,t.used_ublk

,t.used_urec

,t.start_time

from v$transaction t

inner join v$session s on t.addr = s.taddr;

supplemental logging

Supplemental logging :

===============

====Sql's for verify and set:

SELECT supplemental_log_data_min FROM v$database;

==========================

SELECT supplemental_log_data_min MIN,

supplemental_log_data_pk PK,

supplemental_log_data_ui UI,

supplemental_log_data_fk FK,

supplemental_log_data_all "ALL"

FROM v$database;

Set minimal supplemental logging for database.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

For RDS:

exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');

============================SET PK ======================================

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

For RDS:

exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','PRIMARY KEY');

======================HOW UI/ALL=========================================

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

=============================================================================

verify:

SELECT supplemental_log_data_min FROM v$database;

SELECT

SUPPLEMENTAL_LOG_DATA_MIN,

SUPPLEMENTAL_LOG_DATA_PK,

SUPPLEMENTAL_LOG_DATA_UI

FROM V$DATABASE;

select * from v$version;

====================================================================

select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from gv$database;

=========

SELECT supplemental_log_data_min MIN,

supplemental_log_data_pk PK,

supplemental_log_data_ui UI,

supplemental_log_data_fk FK,

supplemental_log_data_all "ALL"

FROM v$database;

Table level logging create group for column and set

ALTER TABLE owner.table_name ADD SUPPLEMENTAL LOG GROUP GROUP_NAME (col1, col2..) ALWAYS;

Table level logging PK column set

ALTER TABLE SCHEMA.TABLE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

Table level logging UI column set

ALTER TABLE SCHEMA.TABLE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

Table level logging ALL column set

ALTER TABLE SCHEMA.TABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER TABLE SCHEMA.TABLE DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Get object id from rowid

Get object id from rowid:

===========================

Some time we have rowid casuing issue, but we dont know for which object you can find object id from the below query.

Select DBMS_ROWID.rowid_object('give your row id here') from dual;

Get init trans value of Object

Get init trans value of Object:

=========================

select table_name, INI_TRANS from dba_tables where table_name = 'TABLE_NAME';

SELECT table_name, index_name, status, ini_trans FROM DBA_INDEXES

INITTRANS value set double for index than object, like below ex:

ALTER TABLE TABLE_NAME MOVE INITRANS 10;

ALTER INDEX TABLE_NAME_PK REBUILD INITRANS 20;

ALTER INDEX TABLE_NAME_I1 REBUILD INITRANS 20;

Get DDL from database

Get DDL from database:

====================

SQL>set lines 120

SQL>set pages 99999

SQL>set long 1000000

SQL> SELECT DBMS_METADATA.GET_DDL('<object_type','<object_name>','<object owner>') FROM DUAL;

select DBMS_METADATA.GET_DDL(object_type, object_name) from user_objects where object_type = 'TABLE' and object_name = 'MY_TABLE'

Blocking session detail

Try below 2 querys for blocking session detail:

For which SQL is currently waiting on:

==================================

select

sid,

sql_text

from

v$session s,

v$sql q

where

sid in

(select

sid

from

v$session

where

state in ('WAITING')

and

wait_class != 'Idle'

and

event='enq: TX - row lock contention'

and

(q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));

The blocking session is:

================================

select

blocking_session,

sid, serial#,

wait_class,

seconds_in_wait

from

v$session

where

blocking_session is not NULL

order by

blocking_session;