Configuration
Archived
archive log list
To check if it is a RAC
select parallel from v$instance;
To check if it is a Data Guard
select protection_level from v$database;
To check if it is a Golden Gate
select * from dba_streams_administrator;
To check database files
vi checking_database_files_location.sql
spool checking_database_files_location.log
--to check v$asm_diskgroup
col name format a25
col COMPATIBILITY format a15
col DATABASE_COMPATIBILITY format a15
set line 400
select NAME,BLOCK_SIZE,STATE,TYPE,TOTAL_MB,FREE_MB,USABLE_FILE_MB,REQUIRED_MIRROR_FREE_MB,OFFLINE_DISKS,COMPATIBILITY, DATABASE_COMPATIBILITY
from v$asm_diskgroup where state='CONNECTED';
--to check tempfile
select distinct substr(file_name, 0,60) as TEMPFILES from dba_temp_files;
--to check logfile
select distinct substr(member, 0,60) AS REDOLOGS from v$logfile;
--to check controlfile
select distinct substr(name, 0,60) AS CONTROLFILES from v$controlfile;
--to check datafiles
select distinct substr(file_name, 0,60) AS DATAFILES from dba_data_files;
--to check parameters
select inst_id, NAME, substr(value, 0,20) from gv$parameter where value like '%DATA%';--to check CRS or Oracle Restart
!srvctl config database -d agap
spool off
Oracle Management File (OMF)
show parameter db_create_file_dest
show parameter db_create_online_log_dest_
To check the autotask are enable
select client_name,status from dba_autotask_client;
To check database Express
select dbms_xdb.gethttpport from dual;
Memory
AMM -link
show parameter memory_target
show parameter memory_max_target
MEMORY_TARGET = SGA_TARGET + GREATEST(PGA_AGGREGATE_TARGET, "maximum PGA allocated")
V$MEMORY_CURRENT_RESIZE_OPS
V$MEMORY_DYNAMIC_COMPONENTS
V$MEMORY_RESIZE_OPS
V$MEMORY_TARGET_ADVICE
SELECT name, value
FROM v$parameter
WHERE name IN ('pga_aggregate_target', 'sga_target')
UNION
SELECT 'maximum PGA allocated' AS name, TO_CHAR(value) AS value
FROM v$pgastat
WHERE name = 'maximum PGA allocated';
ASMM
show parameter sga_target
show parameter sga_max_size
PGA
show parameter workarea_size_policy
show parameter pga_aggregate_limit
show parameter pga_aggregate_target
SHARED POOL
show parameter shared_pool_size
show parameter db_cache_size
v$pgastat
TUNING
To check stored outline
show parameter create_stored_outlines
To check auto capture sql plan baselines
show parameter optimizer_capture_sql_plan_baselines
col parameter_name format a30
select parameter_name, parameter_value from dba_sql_management_config;
