script de levantamiento Oracle

1.- Uno es levantamiento de Sistema Operativo (Linux)

crear shell ejecutable

#vi lev_os.sh

cd /home/oracle

echo "********************************************************** " >> lev_os.txt
echo "hostname " >> lev_os.txt

hostname >> lev_os.txt

echo "********************************************************** " >> lev_os.txt

echo "ifconfig " >> lev_os.txt
ifconfig >> lev_os.txt
echo "********************************************************** " >> lev_os.txt

echo "uname -a " >> lev_os.txt
uname -a >> lev_os.txt
echo "********************************************************** " >> lev_os.txt

echo "cat /proc/meminfo " >> lev_os.txt
cat /proc/meminfo >> lev_os.txt
echo "********************************************************** " >> lev_os.txt

echo "Parametros del Kernel " >> lev_os.txt
echo "cat /etc/sysctl.conf " >> lev_os.txt

cat /etc/sysctl.conf >> lev_os.txt
echo "********************************************************** " >> lev_os.txt


echo "crontab -l " >> lev_os.txt
crontab -l >> lev_os.txt
echo "********************************************************** " >> lev_os.txt

echo "Espacio en disco " >> lev_os.txt
echo "df -m " >> lev_os.txt
df -m >> lev_os.txt
echo "********************************************************** " >> lev_os.txt


echo "Levantamiento Oracle " >> lev_os.txt

su - oracle
sqlplus /nolog @lev_oracle.sql

:wq


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

2.- Levantamiento Oracle.

crear

vi lev_oracle.sql

rem
rem  Script de levantamiento
rem  Valido para bases de datos 10g y superior, en 9i algunas consultas fallaran
rem
rem
rem  Ejecutar en usuario oracle desde el directorio /home/oracle o equivalente
rem  El script genera un archivo llamado lev_oracle.txt, el cual debe ser
rem


connect / as sysdba
spool lev_oracle.txt

prompt **********************************************************
prompt Version de Oracle
prompt **********************************************************

select * from v$version;

prompt **********************************************************
prompt Parametros no default
prompt **********************************************************

column name format a60
column value format a60
set linesize 200
set pagesize 100

select name, value from v$parameter where isdefault='FALSE';

prompt **********************************************************
prompt Archivos de control
prompt **********************************************************

select name from v$controlfile;

prompt **********************************************************
prompt Grupos de redolog
prompt **********************************************************

select * from v$log;

prompt **********************************************************
prompt Grupos de redolog
prompt **********************************************************

column member format a60
select * from v$logfile;

prompt **********************************************************
prompt Directorios de configuracion relevantes
prompt **********************************************************
column name format a60
column value format a60
select name, value
from v$parameter where value like '%/%';


prompt **********************************************************
prompt Directorios Oracle, para datapump y otras aplicaciones
prompt **********************************************************

column directory_path format a90
select directory_path from dba_directories;


prompt **********************************************************
prompt Procesos relacionados a paralelismo
prompt **********************************************************

select slave_name, status from v$PQ_SLAVE;

prompt **********************************************************
prompt Estadistica PGA
prompt **********************************************************

column value format 999,999,999,999
select * from V$PGASTAT;


prompt **********************************************************
prompt SGA
prompt **********************************************************

select *
from
   v$sga;


prompt **********************************************************
prompt Advisor SGA
prompt **********************************************************

select
   sga_size,
   sga_size_factor,
   estd_db_time_factor
from
   v$sga_target_advice
order by
   sga_size asc;



prompt **********************************************************
prompt Archive log list
prompt **********************************************************
archive log list

prompt **********************************************************
prompt Archived log disponibles
prompt **********************************************************

select  SEQUENCE#,  name, deleted from v$archived_log;

prompt **********************************************************
prompt Flash (Fast) Recovery Area
prompt **********************************************************

select name from v$recovery_file_dest;


prompt **********************************************************
prompt Tablespaces
prompt **********************************************************

select TABLESPACE_NAME, STATUS, CONTENTS, EXTENT_MANAGEMENT,
LOGGING, ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT from  dba_tablespaces;


prompt **********************************************************
prompt Tamaño Tablespaces
prompt **********************************************************


select tablespace_name, sum(bytes)/1024/1024 MB from dba_data_files
group by tablespace_name
order by 1;

prompt **********************************************************
prompt Espacio libre en Tablespaces
prompt **********************************************************


select tablespace_name, sum(bytes)/1024/1024 MB from dba_free_space
group by tablespace_name
order by 1 ;


prompt **********************************************************
prompt Datafiles v$datafile
prompt **********************************************************

select substr(t.name,1,20) tbsname , substr(d.name,1,60) filename, d.bytes/1024/1024 MB from v$datafile d, v$tablespace t
where t.ts#=d.ts#
order by 1,2;

prompt **********************************************************
prompt Datafiles dba_data_files
prompt **********************************************************

column tablespace_name format A20
column file_name       format A60
select tablespace_name, file_name, bytes/1024/1024 MB, autoextensible from dba_data_files
order by 1,2;


prompt **********************************************************
prompt RMAN backup Sets
prompt **********************************************************

select RECID, BACKUP_TYPE, decode(BACKUP_TYPE, 'D', 'FULL DATABASE', 'L', 'INCLUYE ARCHIVELOGS', 'I', 'INCREMENTAL', 'OTRO') TIPO_BACKUP, CONTROLFILE_INCLUDED,  PIECES,  KEEP_UNTIL, START_TIME, elapsed_seconds, elapsed_seconds/60 elapsed_minutes
from  v$backup_set;

prompt **********************************************************
prompt RMAN backup pieces
prompt **********************************************************

select  RECID, PIECE#, DEVICE_TYPE, TAG, HANDLE from v$backup_piece;


prompt **********************************************************
prompt RMAN CONFIGURATION
prompt **********************************************************
column name format A50
column value format A60
SELECT * FROM V$RMAN_CONFIGURATION;

prompt **********************************************************
prompt PARAMETROS DE AUDITORIA
prompt **********************************************************
column name format a30
column value format a40
select name, value
from v$parameter where name like '%audit%';


prompt **********************************************************
prompt REGISTROS EN AUD$
prompt **********************************************************
SELECT COUNT(*) FROM aud$;


prompt **********************************************************
prompt Tablespace de tabla de auditoria (si hay registros, deberia
promot tener un tablespace propio
prompt **********************************************************
select tablespace_name, table_name from dba_tables where table_name='AUD$';

spool off
exit