Table of Contents

ORACLE Index

Backup
Archive Mode
Startup scripts
Install

List Tablespaces

select tablespace_name, file_name, bytes, autoextensible from dba_data_files;

Show ASM

SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup;

Add More Space

alter database datafile '/full/path/to/system01.dbf' autoextend on next 128m maxsize 8192m;
alter tablespace SYSTEM add datafile '/full/path/to/system02.dbf' size 512m;

Check status

select status from v$instance;
select status from v$archive_dest;
list backup;
validate database;

RAC

Resources 

crsctl status resource -t

Node Status 

crsctl check crs

Cluster Status 

crsctl check cluster -all

ASMCMD

$ asmcmd
ASMCMD> help {command}
ASMCMD> ls
ASMCMD> lsdsk
ASMCMD> lsdg
ASMCMD> lsdsk -k
ASMCMD> iostat -t 10

Rename service

alter system set service_names = 'mydb' scope = both;
alter database rename global_name to mydb;
alter system register;

show parameter name;
alter system set db_domain='' scope=spfile;

Enable disable DB auditing

ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=SPFILE;

Set log archive

alter system set log_archive_dest_1='LOCATION=/ora/arch' scope=SPFILE;

Delete all archive logs

DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-10';
DELETE NOPROMPT ARCHIVELOG ALL;

Set Recovery size

alter system set db_recovery_file_dest_size=XG;

Check Licence Status

SELECT * FROM V$LICENSE;
SELECT * FROM PRODUCT_COMPONENT_VERSION;

ORACLE Edition

SELECT BANNER FROM V$VERSION WHERE BANNER LIKE '%Edition%';

Partitioning Status

SELECT decode(count(*), 0, 'No', 'Yes') Partitioning FROM (SELECT 1 FROM dba_part_tables WHERE owner NOT IN ('SYSMAN', 'SH', 'SYS', 'SYSTEM', 'AUDSYS') AND rownum = 1 );

ORACLE Spatial Status

SELECT decode(count(*), 0, 'No', 'Yes') Spatial FROM (SELECT 1 FROM all_sdo_geom_metadata WHERE rownum = 1);

RAC Status

SELECT decode(count(*), 0, 'No', 'Yes') RAC FROM (SELECT 1 FROM v$active_instances WHERE rownum = 1);

Features used

SELECT DECODE(detected_usages,0,2,1) nop,
       name, version, detected_usages, currently_used,
       to_char(first_usage_date,'DD/MM/YYYY') first_usage_date, 
       to_char(last_usage_date,'DD/MM/YYYY') last_usage_date
FROM dba_feature_usage_statistics ORDER BY nop, 1, 2

Waste

/opt/oracle/admin/ORAPL/adump/*
/opt/oracle/product/12.1.0/grid/rdbms/audit/*
/opt/oracle/diag/rdbms/orapl/ORAPL/alert/*
/opt/oracle/diag/tnslsnr/oracle/listener/*

crsctl modify resource ora.asm -attr "CHECK_INTERVAL=60"
crsctl stat res ora.asm -p |grep ^CHECK_INTERVAL
showasm.sh
#!/bin/ksh
for i in `/etc/init.d/oracleasm listdisks`
do
v_asmdisk=`/etc/init.d/oracleasm querydisk $i | awk  '{print $2}'`
v_minor=`/etc/init.d/oracleasm querydisk $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $1}'`
v_major=`/etc/init.d/oracleasm querydisk $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $2}'`
v_device=`ls -la /dev | grep $v_minor | grep $v_major | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/$v_device  [$v_minor $v_major]"
done

ORACLE Spatial

https://www.ngdc.noaa.gov/wiki/index.php/Sample_Oracle_Spatial_Queries