Oracle Parameter info
set termout off store set sqlplus_settings replace set serveroutput on size 1000000 linesize 132 pagesize 120 termout off verify off head off ttitle off btitle off clear columns clear breaks column param_name format a40 heading "Parameter Name" column param_value format a38 heading "Parameter Value" word_wrap column isdefault format a7 heading "Default|Value" column ismod heading "Is|Modified" column isses_modifiable format a10 heading "Session|Modifiable" column issys_modifiable format a10 heading "System|Modifiable" -- Get database name and store in variable column name new_value s_dbname SELECT rtrim(name) name from v$database; -- Get today's date column today new_value s_curDate SELECT to_char(sysdate, 'Month DD, YYYY') today from dual; -- Get host name and store in variable column host_name new_value s_machine SELECT host_name from v$instance; set termout feedback off head on spool databaseParametersInfo_&s_dbname._&s_machine ttitle left &s_curDate - center "List of Database Parameters in Instance " &s_dbname " on " &s_machine skip 2 select name param_name, value param_value, isdefault, ismodified ismod, isses_modifiable, issys_modifiable from v$parameter where value is not null order by param_name; spool off ttitle off btitle off clear columns clear breaks set feedback on termout on verify on List of Database Parameters in Instance VIHAAN on ora-gold1 Default Is Session System Parameter Name Parameter Value Value Modified Modifiable Modifiable ---------------------------------------- -------------------------------------- ------- ---------- ---------- ---------- DBFIPS_140 FALSE TRUE FALSE FALSE FALSE O7_DICTIONARY_ACCESSIBILITY FALSE TRUE FALSE FALSE FALSE aq_tm_processes 1 TRUE FALSE FALSE IMMEDIATE archive_lag_target 0 FALSE FALSE FALSE IMMEDIATE asm_power_limit 1 TRUE FALSE TRUE IMMEDIATE audit_file_dest /u01/app/oracle/admin/vihaan/adump FALSE FALSE FALSE DEFERRED audit_sys_operations TRUE TRUE FALSE FALSE FALSE audit_trail DB FALSE FALSE FALSE FALSE awr_snapshot_time_offset 0 TRUE FALSE FALSE IMMEDIATE background_core_dump partial TRUE FALSE FALSE FALSE background_dump_dest /u01/app/oracle/product/12.1.0/db_1/rd TRUE FALSE FALSE IMMEDIATE bms/log backup_tape_io_slaves FALSE TRUE FALSE FALSE DEFERRED bitmap_merge_area_size 1048576 TRUE FALSE FALSE FALSE blank_trimming FALSE TRUE FALSE FALSE FALSE cell_offload_compaction ADAPTIVE TRUE FALSE TRUE IMMEDIATE cell_offload_decryption TRUE TRUE FALSE FALSE IMMEDIATE cell_offload_plan_display AUTO TRUE FALSE TRUE IMMEDIATE cell_offload_processing TRUE TRUE FALSE TRUE IMMEDIATE client_result_cache_lag 3000 TRUE FALSE FALSE FALSE client_result_cache_size 0 TRUE FALSE FALSE FALSE clonedb FALSE TRUE FALSE FALSE FALSE cluster_database FALSE TRUE FALSE FALSE FALSE cluster_database_instances 1 TRUE FALSE FALSE FALSE commit_point_strength 1 TRUE FALSE FALSE FALSE common_user_prefix C## TRUE FALSE FALSE FALSE compatible 12.1.0.2.0 FALSE FALSE FALSE FALSE connection_brokers ((TYPE=DEDICATED)(BROKERS=1)), TRUE FALSE FALSE IMMEDIATE ((TYPE=EMON)(BROKERS=1)) control_file_record_keep_time 7 TRUE FALSE FALSE IMMEDIATE control_files /u01/app/oracle/oradata/VIHAAN/control FALSE FALSE FALSE FALSE file/o1_mf_b282tp9s_.ctl, /u01/app/oracle/fast_recovery_area/VIH AAN/controlfile/o1_mf_b282tpl6_.ctl control_management_pack_access DIAGNOSTIC+TUNING TRUE FALSE FALSE IMMEDIATE core_dump_dest /u01/app/oracle/diag/rdbms/vihaan_prim TRUE FALSE FALSE IMMEDIATE /vihaan/cdump cpu_count 1 TRUE FALSE FALSE IMMEDIATE create_bitmap_area_size 8388608 TRUE FALSE FALSE FALSE cursor_bind_capture_destination memory+disk TRUE FALSE TRUE IMMEDIATE cursor_sharing EXACT TRUE FALSE TRUE IMMEDIATE cursor_space_for_time FALSE TRUE FALSE FALSE FALSE db_16k_cache_size 0 TRUE FALSE FALSE IMMEDIATE db_2k_cache_size 0 TRUE FALSE FALSE IMMEDIATE db_32k_cache_size 0 TRUE FALSE FALSE IMMEDIATE db_4k_cache_size 0 TRUE FALSE FALSE IMMEDIATE db_8k_cache_size 0 TRUE FALSE FALSE IMMEDIATE db_big_table_cache_percent_target 0 TRUE FALSE FALSE IMMEDIATE db_block_buffers 0 TRUE FALSE FALSE FALSE db_block_checking FALSE TRUE FALSE FALSE IMMEDIATE db_block_checksum TYPICAL TRUE FALSE FALSE IMMEDIATE db_block_size 8192 FALSE FALSE FALSE FALSE db_cache_advice ON TRUE FALSE FALSE IMMEDIATE db_cache_size 0 TRUE FALSE FALSE IMMEDIATE db_create_file_dest /u01/app/oracle/oradata FALSE FALSE TRUE IMMEDIATE db_file_multiblock_read_count 53 TRUE FALSE TRUE IMMEDIATE db_files 200 TRUE FALSE FALSE FALSE db_flash_cache_size 0 TRUE FALSE FALSE IMMEDIATE db_flashback_retention_target 1440 TRUE FALSE FALSE IMMEDIATE db_index_compression_inheritance NONE TRUE FALSE TRUE IMMEDIATE db_keep_cache_size 0 TRUE FALSE FALSE IMMEDIATE db_lost_write_protect NONE TRUE FALSE FALSE IMMEDIATE db_name vihaan FALSE FALSE FALSE FALSE db_recovery_file_dest /u01/app/oracle/fast_recovery_area FALSE FALSE FALSE IMMEDIATE db_recovery_file_dest_size 4781506560 FALSE FALSE FALSE IMMEDIATE db_recycle_cache_size 0 TRUE FALSE FALSE IMMEDIATE db_securefile PREFERRED TRUE FALSE TRUE IMMEDIATE db_ultra_safe OFF TRUE FALSE FALSE FALSE db_unique_name vihaan_prim FALSE FALSE FALSE FALSE db_unrecoverable_scn_tracking TRUE TRUE FALSE TRUE IMMEDIATE db_writer_processes 1 TRUE FALSE FALSE FALSE dbwr_io_slaves 0 TRUE FALSE FALSE FALSE ddl_lock_timeout 0 TRUE FALSE TRUE IMMEDIATE deferred_segment_creation TRUE TRUE FALSE TRUE IMMEDIATE dg_broker_config_file1 /u01/app/oracle/product/12.1.0/db_1/db TRUE FALSE FALSE IMMEDIATE s/dr1vihaan_prim.dat dg_broker_config_file2 /u01/app/oracle/product/12.1.0/db_1/db TRUE FALSE FALSE IMMEDIATE s/dr2vihaan_prim.dat dg_broker_start TRUE FALSE FALSE FALSE IMMEDIATE diagnostic_dest /u01/app/oracle FALSE FALSE FALSE IMMEDIATE disk_asynch_io TRUE TRUE FALSE FALSE FALSE dispatchers (PROTOCOL=TCP) (SERVICE=vihaanXDB) FALSE FALSE FALSE IMMEDIATE distributed_lock_timeout 60 TRUE FALSE FALSE FALSE dml_locks 2076 TRUE FALSE FALSE FALSE dnfs_batch_size 4096 TRUE FALSE FALSE FALSE dst_upgrade_insert_conv TRUE TRUE FALSE TRUE IMMEDIATE enable_ddl_logging FALSE TRUE FALSE TRUE IMMEDIATE enable_goldengate_replication FALSE TRUE FALSE FALSE IMMEDIATE enable_pluggable_database FALSE TRUE FALSE FALSE FALSE exclude_seed_cdb_view TRUE TRUE FALSE TRUE IMMEDIATE fal_client (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(P FALSE FALSE FALSE IMMEDIATE ROTOCOL=TCP)(HOST=192.168.0.27)(PORT=1 521)))(CONNECT_DATA=(SERVICE_NAME=viha an_prim)(INSTANCE_NAME=vihaan)(SERVER= dedicated))) fal_server NULL FALSE FALSE FALSE IMMEDIATE fast_start_io_target 0 TRUE FALSE FALSE IMMEDIATE fast_start_mttr_target 0 TRUE FALSE FALSE IMMEDIATE fast_start_parallel_rollback LOW TRUE FALSE FALSE IMMEDIATE file_mapping FALSE TRUE FALSE FALSE IMMEDIATE filesystemio_options none TRUE FALSE FALSE FALSE gcs_server_processes 0 TRUE FALSE FALSE FALSE global_names FALSE TRUE FALSE TRUE IMMEDIATE global_txn_processes 1 TRUE FALSE FALSE IMMEDIATE hash_area_size 131072 TRUE FALSE TRUE FALSE heat_map OFF TRUE FALSE TRUE IMMEDIATE hi_shared_memory_address 0 TRUE FALSE FALSE FALSE hs_autoregister TRUE TRUE FALSE FALSE IMMEDIATE inmemory_force DEFAULT TRUE FALSE FALSE IMMEDIATE inmemory_max_populate_servers 0 TRUE FALSE FALSE IMMEDIATE inmemory_query DISABLE FALSE FALSE TRUE IMMEDIATE inmemory_size 0 TRUE FALSE FALSE IMMEDIATE inmemory_trickle_repopulate_servers_perc 1 TRUE FALSE FALSE IMMEDIATE ent instance_name vihaan TRUE FALSE FALSE FALSE instance_number 0 TRUE FALSE FALSE FALSE instance_type RDBMS TRUE FALSE FALSE FALSE instant_restore FALSE TRUE FALSE FALSE FALSE java_jit_enabled TRUE TRUE FALSE TRUE IMMEDIATE java_max_sessionspace_size 0 TRUE FALSE FALSE FALSE java_pool_size 0 TRUE FALSE FALSE IMMEDIATE java_restrict none TRUE FALSE FALSE FALSE java_soft_sessionspace_limit 0 TRUE FALSE FALSE FALSE job_queue_processes 1000 TRUE FALSE FALSE IMMEDIATE large_pool_size 0 TRUE FALSE FALSE IMMEDIATE ldap_directory_access NONE TRUE FALSE FALSE IMMEDIATE ldap_directory_sysauth no TRUE FALSE FALSE FALSE license_max_sessions 0 TRUE FALSE FALSE IMMEDIATE license_max_users 0 TRUE FALSE FALSE IMMEDIATE license_sessions_warning 0 TRUE FALSE FALSE IMMEDIATE local_listener (ADDRESS_LIST = (ADDRESS = (PROTOCOL = FALSE FALSE FALSE IMMEDIATE TCP)(HOST = 192.168.0.27)(PORT = 1521))) lock_sga FALSE TRUE FALSE FALSE FALSE log_archive_config DG_CONFIG=(vihaan_prim, vihaan_stdy) FALSE FALSE FALSE IMMEDIATE log_archive_dest_1 location=/u01/app/oracle/ArchiveLog FALSE FALSE TRUE IMMEDIATE log_archive_dest_2 service="stdy1", ASYNC NOAFFIRM FALSE FALSE TRUE IMMEDIATE delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="vihaan_stdy" net_timeout=30, valid_for=(online_logfile,all_roles) log_archive_dest_state_1 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_10 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_11 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_12 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_13 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_14 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_15 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_16 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_17 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_18 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_19 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_2 RESET FALSE FALSE TRUE IMMEDIATE log_archive_dest_state_20 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_21 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_22 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_23 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_24 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_25 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_26 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_27 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_28 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_29 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_3 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_30 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_31 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_4 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_5 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_6 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_7 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_8 enable TRUE FALSE TRUE IMMEDIATE log_archive_dest_state_9 enable TRUE FALSE TRUE IMMEDIATE log_archive_format Log%s_%t_%r.Arc FALSE FALSE FALSE FALSE log_archive_max_processes 4 FALSE FALSE FALSE IMMEDIATE log_archive_min_succeed_dest 1 FALSE FALSE TRUE IMMEDIATE log_archive_start FALSE TRUE FALSE FALSE FALSE log_archive_trace 0 FALSE SYSTEM_MOD FALSE IMMEDIATE log_buffer 5193728 TRUE FALSE FALSE FALSE log_checkpoint_interval 0 TRUE FALSE FALSE IMMEDIATE log_checkpoint_timeout 1800 TRUE FALSE FALSE IMMEDIATE log_checkpoints_to_alert FALSE TRUE FALSE FALSE IMMEDIATE max_dump_file_size unlimited TRUE FALSE TRUE IMMEDIATE max_enabled_roles 150 TRUE FALSE FALSE FALSE max_string_size STANDARD TRUE FALSE FALSE IMMEDIATE memory_max_target 0 TRUE FALSE FALSE FALSE memory_target 0 TRUE FALSE FALSE IMMEDIATE nls_comp BINARY TRUE FALSE TRUE FALSE nls_date_format DD-MON HH24:MI TRUE MODIFIED TRUE FALSE nls_language AMERICAN TRUE FALSE TRUE FALSE nls_length_semantics BYTE TRUE FALSE TRUE IMMEDIATE nls_nchar_conv_excp FALSE TRUE FALSE TRUE IMMEDIATE nls_territory AMERICA TRUE FALSE TRUE FALSE noncdb_compatible FALSE TRUE FALSE FALSE FALSE object_cache_max_size_percent 10 TRUE FALSE TRUE DEFERRED object_cache_optimal_size 102400 TRUE FALSE TRUE DEFERRED olap_page_pool_size 0 TRUE FALSE TRUE DEFERRED open_cursors 300 FALSE FALSE FALSE IMMEDIATE open_links 4 TRUE FALSE FALSE FALSE open_links_per_instance 4 TRUE FALSE FALSE FALSE optimizer_adaptive_features TRUE TRUE FALSE TRUE IMMEDIATE optimizer_adaptive_reporting_only FALSE TRUE FALSE TRUE IMMEDIATE optimizer_capture_sql_plan_baselines FALSE TRUE FALSE TRUE IMMEDIATE optimizer_dynamic_sampling 2 TRUE FALSE TRUE IMMEDIATE optimizer_features_enable 12.1.0.2 TRUE FALSE TRUE IMMEDIATE optimizer_index_caching 0 TRUE FALSE TRUE IMMEDIATE optimizer_index_cost_adj 100 TRUE FALSE TRUE IMMEDIATE optimizer_inmemory_aware TRUE TRUE FALSE TRUE IMMEDIATE optimizer_mode ALL_ROWS TRUE FALSE TRUE IMMEDIATE optimizer_secure_view_merging TRUE TRUE FALSE FALSE IMMEDIATE optimizer_use_invisible_indexes FALSE TRUE FALSE TRUE IMMEDIATE optimizer_use_pending_statistics FALSE TRUE FALSE TRUE IMMEDIATE optimizer_use_sql_plan_baselines TRUE TRUE FALSE TRUE IMMEDIATE os_authent_prefix ops$ TRUE FALSE FALSE FALSE os_roles FALSE TRUE FALSE FALSE FALSE parallel_adaptive_multi_user TRUE TRUE FALSE FALSE IMMEDIATE parallel_automatic_tuning FALSE TRUE FALSE FALSE FALSE parallel_degree_level 100 TRUE FALSE TRUE IMMEDIATE parallel_degree_limit CPU TRUE FALSE TRUE IMMEDIATE parallel_degree_policy MANUAL TRUE FALSE TRUE IMMEDIATE parallel_execution_message_size 16384 TRUE FALSE FALSE FALSE parallel_force_local FALSE TRUE FALSE TRUE IMMEDIATE parallel_io_cap_enabled FALSE TRUE FALSE TRUE IMMEDIATE parallel_max_servers 40 TRUE FALSE FALSE IMMEDIATE parallel_min_percent 0 TRUE FALSE TRUE FALSE parallel_min_servers 4 TRUE FALSE FALSE IMMEDIATE parallel_min_time_threshold AUTO TRUE FALSE TRUE IMMEDIATE parallel_server FALSE TRUE FALSE FALSE FALSE parallel_server_instances 1 TRUE FALSE FALSE FALSE parallel_servers_target 16 TRUE FALSE FALSE IMMEDIATE parallel_threads_per_cpu 2 TRUE FALSE FALSE IMMEDIATE permit_92_wrap_format TRUE TRUE FALSE FALSE FALSE pga_aggregate_limit 2147483648 TRUE FALSE FALSE IMMEDIATE pga_aggregate_target 152043520 FALSE FALSE FALSE IMMEDIATE plscope_settings IDENTIFIERS:NONE TRUE FALSE TRUE IMMEDIATE plsql_code_type INTERPRETED TRUE FALSE TRUE IMMEDIATE plsql_debug FALSE TRUE FALSE TRUE IMMEDIATE plsql_optimize_level 2 TRUE FALSE TRUE IMMEDIATE plsql_v2_compatibility FALSE TRUE FALSE TRUE IMMEDIATE plsql_warnings DISABLE:ALL TRUE FALSE TRUE IMMEDIATE pre_page_sga TRUE TRUE FALSE FALSE FALSE processes 300 FALSE FALSE FALSE FALSE query_rewrite_enabled TRUE TRUE FALSE TRUE IMMEDIATE query_rewrite_integrity enforced TRUE FALSE TRUE IMMEDIATE read_only_open_delayed FALSE TRUE FALSE FALSE FALSE recovery_parallelism 0 TRUE FALSE FALSE FALSE recyclebin OFF FALSE FALSE TRUE DEFERRED remote_dependencies_mode TIMESTAMP TRUE FALSE TRUE IMMEDIATE remote_login_passwordfile EXCLUSIVE FALSE FALSE FALSE FALSE remote_os_authent FALSE TRUE FALSE FALSE FALSE remote_os_roles FALSE TRUE FALSE FALSE FALSE replication_dependency_tracking TRUE TRUE FALSE FALSE FALSE resource_limit TRUE TRUE FALSE FALSE IMMEDIATE resource_manager_cpu_allocation 1 TRUE FALSE FALSE IMMEDIATE resource_manager_plan SCHEDULER[0x4448]:DEFAULT_MAINTENANCE_ TRUE FALSE FALSE IMMEDIATE PLAN result_cache_max_result 5 TRUE FALSE FALSE IMMEDIATE result_cache_max_size 2293760 TRUE FALSE FALSE IMMEDIATE result_cache_mode MANUAL TRUE FALSE TRUE IMMEDIATE result_cache_remote_expiration 0 TRUE FALSE TRUE IMMEDIATE resumable_timeout 0 TRUE FALSE TRUE IMMEDIATE sec_case_sensitive_logon TRUE TRUE FALSE FALSE IMMEDIATE sec_max_failed_login_attempts 3 TRUE FALSE FALSE FALSE sec_protocol_error_further_action (DROP,3) TRUE FALSE FALSE IMMEDIATE sec_protocol_error_trace_action TRACE TRUE FALSE FALSE IMMEDIATE sec_return_server_release_banner FALSE TRUE FALSE FALSE FALSE serial_reuse disable TRUE FALSE FALSE FALSE service_names VIHAAN1, VIHAAN2 FALSE FALSE FALSE IMMEDIATE session_cached_cursors 50 TRUE FALSE TRUE FALSE session_max_open_files 10 TRUE FALSE FALSE FALSE sessions 472 TRUE FALSE FALSE IMMEDIATE sga_max_size 457179136 TRUE FALSE FALSE FALSE sga_target 457179136 FALSE FALSE FALSE IMMEDIATE shadow_core_dump partial TRUE FALSE FALSE FALSE shared_memory_address 0 TRUE FALSE FALSE FALSE shared_pool_reserved_size 5033164 TRUE FALSE FALSE FALSE shared_pool_size 0 TRUE FALSE FALSE IMMEDIATE shared_servers 1 TRUE FALSE FALSE IMMEDIATE skip_unusable_indexes TRUE TRUE FALSE TRUE IMMEDIATE sort_area_retained_size 0 TRUE FALSE TRUE DEFERRED sort_area_size 65536 TRUE FALSE TRUE DEFERRED spatial_vector_acceleration FALSE TRUE FALSE TRUE IMMEDIATE spfile /u01/app/oracle/product/12.1.0/db_1/db TRUE FALSE FALSE IMMEDIATE s/spfilevihaan.ora sql92_security FALSE TRUE FALSE FALSE FALSE sql_trace FALSE TRUE FALSE TRUE IMMEDIATE sqltune_category DEFAULT TRUE FALSE TRUE IMMEDIATE standby_archive_dest ?/dbs/arch TRUE FALSE FALSE IMMEDIATE standby_file_management MANUAL FALSE FALSE FALSE IMMEDIATE star_transformation_enabled FALSE TRUE FALSE TRUE IMMEDIATE statistics_level TYPICAL TRUE FALSE TRUE IMMEDIATE streams_pool_size 0 TRUE FALSE FALSE IMMEDIATE tape_asynch_io TRUE TRUE FALSE FALSE FALSE temp_undo_enabled FALSE TRUE FALSE TRUE IMMEDIATE thread 0 TRUE FALSE FALSE IMMEDIATE threaded_execution FALSE TRUE FALSE FALSE FALSE timed_os_statistics 0 TRUE FALSE TRUE IMMEDIATE timed_statistics TRUE TRUE FALSE TRUE IMMEDIATE trace_enabled TRUE TRUE FALSE FALSE IMMEDIATE transactions 519 TRUE FALSE FALSE FALSE transactions_per_rollback_segment 5 TRUE FALSE FALSE FALSE undo_management AUTO TRUE FALSE FALSE FALSE undo_retention 900 TRUE FALSE FALSE IMMEDIATE undo_tablespace UNDOTBS1 FALSE FALSE FALSE IMMEDIATE unified_audit_sga_queue_size 1048576 TRUE FALSE FALSE FALSE use_dedicated_broker FALSE TRUE FALSE FALSE IMMEDIATE use_indirect_data_buffers FALSE TRUE FALSE FALSE FALSE use_large_pages TRUE TRUE FALSE FALSE FALSE user_dump_dest /u01/app/oracle/product/12.1.0/db_1/rd TRUE FALSE FALSE IMMEDIATE bms/log workarea_size_policy AUTO TRUE FALSE TRUE IMMEDIATE xml_db_events enable TRUE FALSE TRUE IMMEDIATE 289 rows selected.
Saturday, 8 November 2014
Oracle Parameter info
Oracle Archive generation report
Oracle Archive generation report
set term off term on
DEFINE size_label=Gb
DEFINE size_divider="1024/1024/1024"
DEFINE round_precision=2
SET head on FEED off ECHO OFF LINES 1000 TRIMSPOOL ON TRIM on PAGES 1000
COLUMN separator HEADING "!|!|!" FORMAT A1
COLUMN "Date" HEADING "Date" FORMAT A9
COLUMN "Total" HEADING "Day|Total|(&size_label)" FORMAT 9999
COLUMN "Day" HEADING "Day" FORMAT A3
COLUMN h0 HEADING "h0|(&size_label)" FORMAT 999
COLUMN h1 HEADING "h1|(&size_label)" FORMAT 999
COLUMN h2 HEADING "h2|(&size_label)" FORMAT 999
COLUMN h3 HEADING "h3|(&size_label)" FORMAT 999
COLUMN h4 HEADING "h4|(&size_label)" FORMAT 999
COLUMN h5 HEADING "h5|(&size_label)" FORMAT 999
COLUMN h6 HEADING "h6|(&size_label)" FORMAT 999
COLUMN h7 HEADING "h7|(&size_label)" FORMAT 999
COLUMN h8 HEADING "h8|(&size_label)" FORMAT 999
COLUMN h9 HEADING "h9|(&size_label)" FORMAT 999
COLUMN h10 HEADING "h10|(&size_label)" FORMAT 999
COLUMN h11 HEADING "h11|(&size_label)" FORMAT 999
COLUMN h12 HEADING "h12|(&size_label)" FORMAT 999
COLUMN h13 HEADING "h13|(&size_label)" FORMAT 999
COLUMN h14 HEADING "h14|(&size_label)" FORMAT 999
COLUMN h15 HEADING "h15|(&size_label)" FORMAT 999
COLUMN h16 HEADING "h16|(&size_label)" FORMAT 999
COLUMN h17 HEADING "h17|(&size_label)" FORMAT 999
COLUMN h18 HEADING "h18|(&size_label)" FORMAT 999
COLUMN h19 HEADING "h19|(&size_label)" FORMAT 999
COLUMN h20 HEADING "h20|(&size_label)" FORMAT 999
COLUMN h21 HEADING "h21|(&size_label)" FORMAT 999
COLUMN h22 HEADING "h22|(&size_label)" FORMAT 999
COLUMN h23 HEADING "h23|(&size_label)" FORMAT 999
PROMPT
PROMPT *******************************************************************************************************************************************
PROMPT * A R C H I V E L O G S W I T C H S U M M A R Y (By Size)
PROMPT * (Hourly and Daily figures in &&size_label)
PROMPT *******************************************************************************************************************************************
PROMPT
SELECT to_char(trunc(COMPLETION_TIME),'DD-Mon-YY') "Date",
to_char(COMPLETION_TIME, 'Dy') "Day",
'|' separator,
ROUND(SUM(((BLOCKS * BLOCK_SIZE)/&size_divider))) "Total",
'|' separator,
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'00',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h0",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'01',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h1",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'02',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h2",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'03',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h3",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'04',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h4",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'05',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h5",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'06',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h6",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'07',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h7",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'08',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h8",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'09',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h9",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'10',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h10",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'11',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h11",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'12',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h12",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'13',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h13",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'14',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h14",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'15',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h15",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'16',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h16",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'17',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h17",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'18',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h18",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'19',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h19",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'20',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h20",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'21',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h21",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'22',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h22",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'23',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h23"
from v$archived_log
where 1=1
and standby_dest = 'NO'
-- and CREATOR IN ('ARCH' , 'FGRD','LGWR','RFS')
group by trunc(COMPLETION_TIME), to_char(COMPLETION_TIME, 'Dy')
order by trunc(COMPLETION_TIME)
/
! Day !
! Total ! h0 h1 h2 h3 h4 h5 h6 h7 h8 h9 h10 h11 h12 h13 h14 h15 h16 h17 h18 h19 h20 h21 h22 h23
Date Day ! (GB) ! (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB)
--------- --- - ----- - ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
17-Oct-14 Fri | 0 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
18-Oct-14 Sat | 0 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
19-Oct-14 Sun | 0 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
25-Oct-14 Sat | 0 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
02-Nov-14 Sun | 0 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
08-Nov-14 Sat | 0 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
select * from v$logfile order by group#; select * from v$log order by SEQUENCE#; select max( sequence#) last_sequence, max(completion_time) completion_time, max(block_size) block_size from v$archived_log ;
set linesize 140
set feedback off
set timing off
set pagesize 1000
col ARCHIVED format a8
col ins format 99 heading "DB"
col member format a80
col status format a12
col archive_date format a20
col member format a60
col type format a10
col group# format 99999999
col min_archive_interval format a20
col max_archive_interval format a20
col h00 heading "H00" format a3
col h01 heading "H01" format a3
col h02 heading "H02" format a3
col h03 heading "H03" format a3
col h04 heading "H04" format a3
col h05 heading "H05" format a3
col h06 heading "H06" format a3
col h07 heading "H07" format a3
col h08 heading "H08" format a3
col h09 heading "H09" format a3
col h10 heading "H10" format a3
col h11 heading "H11" format a3
col h12 heading "H12" format a3
col h13 heading "H13" format a3
col h14 heading "H14" format a3
col h15 heading "H15" format a3
col h16 heading "H16" format a3
col h17 heading "H17" format a3
col h18 heading "H18" format a3
col h19 heading "H19" format a3
col h20 heading "H20" format a3
col h21 heading "H21" format a3
col h22 heading "H22" format a3
col h23 heading "H23" format a3
col total format a6
col date format a10
SELECT instance ins,
log_date "DATE" ,
lpad(to_char(NVL( COUNT( * ) , 0 )),6,' ') Total,
lpad(to_char(NVL( SUM( decode( log_hour , '00' , 1 ) ) , 0 )),3,' ') h00 ,
lpad(to_char(NVL( SUM( decode( log_hour , '01' , 1 ) ) , 0 )),3,' ') h01 ,
lpad(to_char(NVL( SUM( decode( log_hour , '02' , 1 ) ) , 0 )),3,' ') h02 ,
lpad(to_char(NVL( SUM( decode( log_hour , '03' , 1 ) ) , 0 )),3,' ') h03 ,
lpad(to_char(NVL( SUM( decode( log_hour , '04' , 1 ) ) , 0 )),3,' ') h04 ,
lpad(to_char(NVL( SUM( decode( log_hour , '05' , 1 ) ) , 0 )),3,' ') h05 ,
lpad(to_char(NVL( SUM( decode( log_hour , '06' , 1 ) ) , 0 )),3,' ') h06 ,
lpad(to_char(NVL( SUM( decode( log_hour , '07' , 1 ) ) , 0 )),3,' ') h07 ,
lpad(to_char(NVL( SUM( decode( log_hour , '08' , 1 ) ) , 0 )),3,' ') h08 ,
lpad(to_char(NVL( SUM( decode( log_hour , '09' , 1 ) ) , 0 )),3,' ') h09 ,
lpad(to_char(NVL( SUM( decode( log_hour , '10' , 1 ) ) , 0 )),3,' ') h10 ,
lpad(to_char(NVL( SUM( decode( log_hour , '11' , 1 ) ) , 0 )),3,' ') h11 ,
lpad(to_char(NVL( SUM( decode( log_hour , '12' , 1 ) ) , 0 )),3,' ') h12 ,
lpad(to_char(NVL( SUM( decode( log_hour , '13' , 1 ) ) , 0 )),3,' ') h13 ,
lpad(to_char(NVL( SUM( decode( log_hour , '14' , 1 ) ) , 0 )),3,' ') h14 ,
lpad(to_char(NVL( SUM( decode( log_hour , '15' , 1 ) ) , 0 )),3,' ') h15 ,
lpad(to_char(NVL( SUM( decode( log_hour , '16' , 1 ) ) , 0 )),3,' ') h16 ,
lpad(to_char(NVL( SUM( decode( log_hour , '17' , 1 ) ) , 0 )),3,' ') h17 ,
lpad(to_char(NVL( SUM( decode( log_hour , '18' , 1 ) ) , 0 )),3,' ') h18 ,
lpad(to_char(NVL( SUM( decode( log_hour , '19' , 1 ) ) , 0 )),3,' ') h19 ,
lpad(to_char(NVL( SUM( decode( log_hour , '20' , 1 ) ) , 0 )),3,' ') h20 ,
lpad(to_char(NVL( SUM( decode( log_hour , '21' , 1 ) ) , 0 )),3,' ') h21 ,
lpad(to_char(NVL( SUM( decode( log_hour , '22' , 1 ) ) , 0 )),3,' ') h22 ,
lpad(to_char(NVL( SUM( decode( log_hour , '23' , 1 ) ) , 0 )),3,' ') h23
FROM (
SELECT thread# INSTANCE ,
TO_CHAR( first_time , 'YYYY-MM-DD' ) log_date ,
TO_CHAR( first_time , 'hh24' ) log_hour
FROM v$log_history
)
GROUP BY
instance,log_date
ORDER BY
log_date ;
select trunc(min(completion_time - first_time))||' Day '||
to_char(trunc(sysdate,'dd') + min(completion_time - first_time),'hh24:mm:ss')||chr(10) min_archive_interval,
trunc(max(completion_time - first_time))||' Day '||
to_char(trunc(sysdate,'dd') + max(completion_time - first_time),'hh24:mm:ss')||chr(10) max_archive_interval
from gv$archived_log
where sequence# <> ( select max(sequence#) from gv$archived_log ) ;set term off term on
SET head on FEED off ECHO OFF LINES 1000 TRIMSPOOL ON TRIM on PAGES 1000
COLUMN separator HEADING "!|!|!" FORMAT A1
COLUMN "Date" HEADING "Date" FORMAT A9
COLUMN "Total" HEADING "Total|(#)" FORMAT 9999
COLUMN "Day" HEADING "Day" FORMAT A3
COLUMN h0 HEADING "h0|(#)" FORMAT 999
COLUMN h1 HEADING "h1|(#)" FORMAT 999
COLUMN h2 HEADING "h2|(#)" FORMAT 999
COLUMN h3 HEADING "h3|(#)" FORMAT 999
COLUMN h4 HEADING "h4|(#)" FORMAT 999
COLUMN h5 HEADING "h5|(#)" FORMAT 999
COLUMN h6 HEADING "h6|(#)" FORMAT 999
COLUMN h7 HEADING "h7|(#)" FORMAT 999
COLUMN h8 HEADING "h8|(#)" FORMAT 999
COLUMN h9 HEADING "h9|(#)" FORMAT 999
COLUMN h10 HEADING "h10|(#)" FORMAT 999
COLUMN h11 HEADING "h11|(#)" FORMAT 999
COLUMN h12 HEADING "h12|(#)" FORMAT 999
COLUMN h13 HEADING "h13|(#)" FORMAT 999
COLUMN h14 HEADING "h14|(#)" FORMAT 999
COLUMN h15 HEADING "h15|(#)" FORMAT 999
COLUMN h16 HEADING "h16|(#)" FORMAT 999
COLUMN h17 HEADING "h17|(#)" FORMAT 999
COLUMN h18 HEADING "h18|(#)" FORMAT 999
COLUMN h19 HEADING "h19|(#)" FORMAT 999
COLUMN h20 HEADING "h20|(#)" FORMAT 999
COLUMN h21 HEADING "h21|(#)" FORMAT 999
COLUMN h22 HEADING "h22|(#)" FORMAT 999
COLUMN h23 HEADING "h23|(#)" FORMAT 999
PROMPT
PROMPT *******************************************************************************************************************************************
PROMPT * A R C H I V E L O G S W I T C H S U M M A R Y (By Frequency)
PROMPT * (Hourly and Daily figures in number of archivelogs)
PROMPT *******************************************************************************************************************************************
PROMPT
PROMPT - <-------------------------------------------------------- Hourly Total ----------------------------------------------->
SELECT to_char(trunc(first_time),'DD-Mon-YY') "Date",
to_char(first_time, 'Dy') "Day",
'|' separator,
count(1) Total,
'|' separator,
SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",
SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",
SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",
SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",
SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",
SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",
SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",
SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",
SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",
SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",
SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",
SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",
SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",
SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",
SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",
SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",
SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",
SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",
SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",
SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",
SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23"
from v$log_history
where 1=1
-- and standby_dest = 'NO'
group by trunc(first_time), to_char(first_time, 'Dy')
order by trunc(first_time)
/
! !
! Total ! h0 h1 h2 h3 h4 h5 h6 h7 h8 h9 h10 h11 h12 h13 h14 h15 h16 h17 h18 h19 h20 h21 h22 h23
Date Day ! (#) ! (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#)
--------- --- - ----- - ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
25-Sep-14 Thu | 4 | 0 0 0 0 0 0 0 0 0 0 0 0 0 4 0 0 0 0 0 0 0 0 0 0
26-Sep-14 Fri | 6 | 0 0 0 0 0 0 0 0 2 1 0 2 1 0 0 0 0 0 0 0 0 0 0 0
27-Sep-14 Sat | 4 | 0 0 0 0 0 0 0 0 0 3 0 1 0 0 0 0 0 0 0 0 0 0 0 0
05-Oct-14 Sun | 2 | 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 1 0 0 0 0 0 0 0
17-Oct-14 Fri | 1 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0
18-Oct-14 Sat | 10 | 0 0 0 0 0 0 0 0 0 0 0 1 5 4 0 0 0 0 0 0 0 0 0 0
19-Oct-14 Sun | 8 | 0 0 0 0 0 0 0 0 2 3 2 1 0 0 0 0 0 0 0 0 0 0 0 0
25-Oct-14 Sat | 1 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0
02-Nov-14 Sun | 4 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 3 0 0 0 0 0====
set linesize 200 pagesize 1000
column day format a3
column total format 9999
column h00 format 9999
column h01 format 9999
column h02 format 9999
column h03 format 9999
column h04 format 9999
column h04 format 9999
column h05 format 9999
column h06 format 9999
column h07 format 9999
column h08 format 9999
column h09 format 9999
column h10 format 9999
column h11 format 9999
column h12 format 9999
column h13 format 9999
column h14 format 9999
column h15 format 9999
column h16 format 9999
column h17 format 9999
column h18 format 9999
column h19 format 9999
column h20 format 9999
column h21 format 9999
column h22 format 9999
column h23 format 9999
column h24 format 9999
break on report
compute max of "total" on report
compute max of "h00" on report
compute max of "h01" on report
compute max of "h02" on report
compute max of "h03" on report
compute max of "h04" on report
compute max of "h05" on report
compute max of "h06" on report
compute max of "h07" on report
compute max of "h08" on report
compute max of "h09" on report
compute max of "h10" on report
compute max of "h11" on report
compute max of "h12" on report
compute max of "h13" on report
compute max of "h14" on report
compute max of "h15" on report
compute max of "h16" on report
compute max of "h17" on report
compute max of "h18" on report
compute max of "h19" on report
compute max of "h20" on report
compute max of "h21" on report
compute max of "h22" on report
compute max of "h23" on report
compute sum of NUM on report
compute sum of GB on report
compute sum of MB on report
compute sum of KB on report
REM Script to Report the Redo Log Switch History
alter session set nls_date_format='DD MON YYYY';
select thread#, trunc(completion_time) as "date", to_char(completion_time,'Dy') as "Day", count(1) as "total",
sum(decode(to_char(completion_time,'HH24'),'00',1,0)) as "h00",
sum(decode(to_char(completion_time,'HH24'),'01',1,0)) as "h01",
sum(decode(to_char(completion_time,'HH24'),'02',1,0)) as "h02",
sum(decode(to_char(completion_time,'HH24'),'03',1,0)) as "h03",
sum(decode(to_char(completion_time,'HH24'),'04',1,0)) as "h04",
sum(decode(to_char(completion_time,'HH24'),'05',1,0)) as "h05",
sum(decode(to_char(completion_time,'HH24'),'06',1,0)) as "h06",
sum(decode(to_char(completion_time,'HH24'),'07',1,0)) as "h07",
sum(decode(to_char(completion_time,'HH24'),'08',1,0)) as "h08",
sum(decode(to_char(completion_time,'HH24'),'09',1,0)) as "h09",
sum(decode(to_char(completion_time,'HH24'),'10',1,0)) as "h10",
sum(decode(to_char(completion_time,'HH24'),'11',1,0)) as "h11",
sum(decode(to_char(completion_time,'HH24'),'12',1,0)) as "h12",
sum(decode(to_char(completion_time,'HH24'),'13',1,0)) as "h13",
sum(decode(to_char(completion_time,'HH24'),'14',1,0)) as "h14",
sum(decode(to_char(completion_time,'HH24'),'15',1,0)) as "h15",
sum(decode(to_char(completion_time,'HH24'),'16',1,0)) as "h16",
sum(decode(to_char(completion_time,'HH24'),'17',1,0)) as "h17",
sum(decode(to_char(completion_time,'HH24'),'18',1,0)) as "h18",
sum(decode(to_char(completion_time,'HH24'),'19',1,0)) as "h19",
sum(decode(to_char(completion_time,'HH24'),'20',1,0)) as "h20",
sum(decode(to_char(completion_time,'HH24'),'21',1,0)) as "h21",
sum(decode(to_char(completion_time,'HH24'),'22',1,0)) as "h22",
sum(decode(to_char(completion_time,'HH24'),'23',1,0)) as "h23"
from
v$archived_log
where first_time > trunc(sysdate-30)
and dest_id = (select dest_id from V$ARCHIVE_DEST_STATUS where status='VALID' and type='LOCAL')
group by thread#, trunc(completion_time), to_char(completion_time, 'Dy') order by 2,1;
THREAD# date Day total h00 h01 h02 h03 h04 h05 h06 h07 h08 h09 h10 h11 h12 h13 h14 h15 h16 h17 h18 h19 h20 h21 h22 h23
---------- ----------- --- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
1 17 FEB 2024 Sat 857 672 7 9 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 9 7 9 8 8 8
2 17 FEB 2024 Sat 356 170 8 8 8 8 8 8 9 8 7 9 8 7 8 8 9 8 8 9 8 8 7 9 8
1 18 FEB 2024 Sun 194 9 7 9 8 8 8 8 8 8 8 8 10 7 8 9 7 8 8 8 8 8 8 8 8
2 18 FEB 2024 Sun 195 10 7 9 8 8 8 8 8 8 8 8 10 7 9 8 7 8 8 8 8 8 8 8 8
1 19 FEB 2024 Mon 193 8 8 9 7 8 8 8 8 8 8 9 8 7 9 7 9 9 7 8 8 8 8 8 8
2 19 FEB 2024 Mon 192 8 8 8 8 8 8 8 8 8 8 9 8 7 9 7 8 9 8 7 8 8 8 8 8
1 20 FEB 2024 Tue 2145 8 8 8 8 8 9 8 463 207 134 137 155 8 495 36 49 7 8 349 8 8 8 8 8
2 20 FEB 2024 Tue 804 8 8 8 8 8 9 8 133 69 47 44 49 8 160 54 22 8 7 105 8 8 8 9 8
1 21 FEB 2024 Wed 2803 8 8 8 8 8 9 8 673 479 161 76 8 8 8 8 8 76 30 31 8 10 544 611 7
2 21 FEB 2024 Wed 918 8 8 8 8 8 9 8 171 121 49 25 8 8 8 8 8 28 12 35 8 8 172 185 7
select THREAD#, trunc(completion_time) as "DATE"
, count(1) num
, trunc(sum(blocks*block_size)/1024/1024/1024) as GB
, trunc(sum(blocks*block_size)/1024/1024) as MB
, sum(blocks*block_size)/1024 as KB
from v$archived_log
where first_time > trunc(sysdate-10)
and dest_id = (select dest_id from V$ARCHIVE_DEST_STATUS where status='VALID' and type='LOCAL')
group by thread#, trunc(completion_time)
order by 2,1
;
THREAD# DATE NUM GB MB KB ---------- ----------- ---------- ---------- ---------- ---------- 1 08 MAR 2024 606 20 20782 21281002.5 2 08 MAR 2024 346 3 3396 3478454 1 09 MAR 2024 791 29 30327 31054968 2 09 MAR 2024 337 0 167 171143.5 1 10 MAR 2024 195 0 160 164815.5
==========
set pagesize 9999 linesize 120
column mb_arch format 9G999G999D99
column real_mb_in_period format 9G999G999D99column max_mb_in_period format 9G999G999D99
column min_mb_in_period format 9G999G999D99
column counted format 99G999D99
column counted_in_period format 99G999D99
define days_on_disk=15 -----<<<
select dag, mb_arch,
sum(mb_arch) over
( order by dag
range &days_on_disk preceding
) as real_mb_in_period,
counted,
sum(counted) over
( order by dag
range &days_on_disk preceding
) counted_in_period,
max(mb_arch) over
( order by dag
range &days_on_disk preceding
) * &days_on_disk as max_mb_in_period,
min(mb_arch) over
( order by dag
range &days_on_disk preceding
) * &days_on_disk as min_mb_in_period
from ( select trunc(completion_time) dag, sum(blocks * block_size)/1024/1024 mb_arch, count(*) counted from v$archived_log
where months_between(trunc(sysdate), trunc(completion_time)) <= 1
and completion_time < trunc(sysdate)
group by trunc(completion_time)
);===
set linesize 300 pagesize 300 with log_history as ( select to_char(trunc(first_time, 'HH24'),'YYYY-MM-DD HH24:MI') "Date" , count(1) "Total LFS per hour" FROM v$log_history where trunc(first_time) > = trunc(sysdate -14) GROUP by to_char(first_time, 'Dy'), to_char(trunc(first_time,'HH24'),'YYYY-MM-DD HH24:MI') order by to_date(to_char(trunc(first_time, 'HH24'),'YYYY-MM-DD HH24:MI'), 'YYYY-MM-DD HH24:MI') desc ), log_history_normalized as ( select snap_id, 'v$log_history' metric_source, 'Total LFS per hour' metric_name, "Total LFS per hour" delta_value from log_history lh, dba_hist_snapshot snap where lh."Date" = to_char(trunc(snap.begin_interval_time,'HH24'),'YYYY-MM-DD HH24:MI') ) select snap_id, metric_source, metric_name, delta_value from log_history_normalized where 1=1 order by snap_id ; SNAP_ID METRIC_SOURCE METRIC_NAME DELTA_VALUE ---------- ------------- ------------------ ----------- 41039 v$log_history Total LFS per hour 1 41039 v$log_history Total LFS per hour 1 41041 v$log_history Total LFS per hour 1 41041 v$log_history Total LFS per hour 1
=========set linesize 300 pagesize 300 col name for a90 col first_time for a16 select thread#,sequence#, name ,creator -- , to_char(first_time,'DD-MON HH24:MI') first_time , to_char(completion_time,'DD-MON HH24:MI') arc_completion,decode (STATUS,'A','Available','D', 'Deleted','U','Unavailable','X' , 'Expired' ) arc_status from v$archived_log where 1=1 and first_time > sysdate -4 --and first_time > sysdate-1 -- and CREATOR!='LGWR' and SEQUENCE#>11468 -- and THREAD# =2 -- and name like '%+RECO01%' order by 5
====
from web
define 1=1
col num_days new_value num_days noprint
set feed on term on echo off tab off
prompt
prompt report how much space N days of archive logs consume
prompt for each day and the preceding (N-1) days
prompt
prompt the first (N-1) days of the report are inaccurate
prompt
prompt Calculate archive log sums for how many days? :
set term off feed off
select '&1' num_days from dual;
set term on feed on
col log_date format a20 head 'LOG DATE'
col bytes format 99,999,999,999,999
col bytes_today format 99,999,999,999,999
col bytes_Nday format 99,999,999,999,999
set pagesize 100 linesize 200 trimspool on
-- for repeated use a temp table of archive logs
-- is *much* faster
--define src_table='archlogs'
define src_table='v$archived_log'
with rawlogs as (
select distinct a.first_time, a.sequence#, a.thread#, a.block_size, a.blocks
from &src_table a
order by a.first_time, a.sequence#,a.thread#
),
logdaysums as (
select
trunc(r.first_time) log_date
, sum(block_size * blocks) bytes
from rawlogs r
group by trunc(r.first_time)
),
ndaysums as (
select
l.log_date
, &num_days days
, l.bytes bytes_today
, sum(l.bytes)
over (order by l.log_date rows (&num_days - 1) preceding) bytes_Nday
from logdaysums l
group by log_date, &num_days, bytes
order by l.log_date
)
select to_char(n.log_date,'yyyy-mm-dd') log_date, n.days, n.bytes_today, n.bytes_Nday
from ndaysums n
union all
select '== AVG N DAYS =====', null, null,null from dual
union all
select 'AVERAGES' log_date, n.days, avg(n.bytes_today) bytes_today, avg(n.bytes_Nday) bytes_Nday
from ndaysums n
group by 'AVERAGES', n.days
union all
select '== MEDIAN N DAYS ===', null, null,null from dual
union all
select 'MEDIAN' log_date, n.days, median(n.bytes_today) bytes_today, median(n.bytes_Nday) bytes_Nday
from ndaysums n
group by 'MEDIAN', n.days
union all
select '== MIN N DAYS =====', null, null,null from dual
union all
select to_char(n2.log_date,'yyyy-mm-dd') log_date, n2.days, null, n2.bytes_Nday
from ndaysums n2
where (n2.bytes_Nday) in (
select min(n3.bytes_Nday) bytes_Nday
from ndaysums n3
)
union all
select '== MAX N DAYS =====', null, null,null from dual
union all
select to_char(n2.log_date,'yyyy-mm-dd') log_date, n2.days, null, n2.bytes_Nday
from ndaysums n2
where (n2.bytes_Nday) in (
select max(n3.bytes_Nday) bytes_Nday
from ndaysums n3
)
/
COL lgs_dest_id FOR 9999 HEAD "ID" COL lgs_dest_name FOR a20 HEAD "Name" COL lgs_target FOR a10 HEAD "Target" COL lgs_status FOR a10 HEAD "Status" COL lgs_destination FOR a30 HEAD "Destination" SELECT a.dest_id lgs_dest_id, a.dest_name lgs_dest_name, a.target lgs_target, a.status lgs_status, a.destination lgs_destination FROM v$archive_dest a, v$archived_log b WHERE a.dest_id=b.dest_id AND a.status='VALID' GROUP by a.dest_id,a.dest_name,a.status,a.destination,a.target; set feedback on set feedback off COL lgs_date FOR a10 HEAD "Day" COL lgs_blocks FOR 99999999 HEAD "GB/day" COL lgs_logs FOR 99999 HEAD "Log/day" COL lgs_switch FOR 99999 HEAD "Switch/day" COL lgs_00 FOR a4 HEAD "00" JUSTIFY RIGHT COL lgs_01 FOR a4 HEAD "01" JUSTIFY RIGHT COL lgs_02 FOR a4 HEAD "02" JUSTIFY RIGHT COL lgs_03 FOR a4 HEAD "03" JUSTIFY RIGHT COL lgs_04 FOR a4 HEAD "04" JUSTIFY RIGHT COL lgs_05 FOR a4 HEAD "05" JUSTIFY RIGHT COL lgs_06 FOR a4 HEAD "06" JUSTIFY RIGHT COL lgs_07 FOR a4 HEAD "07" JUSTIFY RIGHT COL lgs_08 FOR a4 HEAD "08" JUSTIFY RIGHT COL lgs_09 FOR a4 HEAD "09" JUSTIFY RIGHT COL lgs_10 FOR a4 HEAD "10" JUSTIFY RIGHT COL lgs_11 FOR a4 HEAD "11" JUSTIFY RIGHT COL lgs_12 FOR a4 HEAD "12" JUSTIFY RIGHT COL lgs_13 FOR a4 HEAD "13" JUSTIFY RIGHT COL lgs_14 FOR a4 HEAD "14" JUSTIFY RIGHT COL lgs_15 FOR a4 HEAD "15" JUSTIFY RIGHT COL lgs_16 FOR a4 HEAD "16" JUSTIFY RIGHT COL lgs_17 FOR a4 HEAD "17" JUSTIFY RIGHT COL lgs_18 FOR a4 HEAD "18" JUSTIFY RIGHT COL lgs_19 FOR a4 HEAD "19" JUSTIFY RIGHT COL lgs_20 FOR a4 HEAD "20" JUSTIFY RIGHT COL lgs_21 FOR a4 HEAD "21" JUSTIFY RIGHT COL lgs_22 FOR a4 HEAD "22" JUSTIFY RIGHT COL lgs_23 FOR a4 HEAD "23" JUSTIFY RIGHT prompt prompt ===================================================================================================================================================== prompt Log switch's history map prompt ===================================================================================================================================================== WITH archived_logs AS ( SELECT min(dest_id) DEST_ID,al.sequence#,al.BLOCKS,al.BLOCK_SIZE FROM v$archived_log al GROUP BY al.sequence#,al.blocks,al.block_size) SELECT SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),1,10) lgs_date, round(sum(al.BLOCKS*al.BLOCK_SIZE)/1024/1024/1024,0) lgs_blocks, COUNT(lh.first_time) lgs_switch, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'00',1,0)),'9999'))),3) lgs_00, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'01',1,0)),'9999'))),3) lgs_01, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'02',1,0)),'9999'))),3) lgs_02, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'03',1,0)),'9999'))),3) lgs_03, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'04',1,0)),'9999'))),3) lgs_04, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'05',1,0)),'9999'))),3) lgs_05, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'06',1,0)),'9999'))),3) lgs_06, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'07',1,0)),'9999'))),3) lgs_07, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'08',1,0)),'9999'))),3) lgs_08, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'09',1,0)),'9999'))),3) lgs_09, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'10',1,0)),'9999'))),3) lgs_10, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'11',1,0)),'9999'))),3) lgs_11, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'12',1,0)),'9999'))),3) lgs_12, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'13',1,0)),'9999'))),3) lgs_13, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'14',1,0)),'9999'))),3) lgs_14, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'15',1,0)),'9999'))),3) lgs_15, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'16',1,0)),'9999'))),3) lgs_16, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'17',1,0)),'9999'))),3) lgs_17, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'18',1,0)),'9999'))),3) lgs_18, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'19',1,0)),'9999'))),3) lgs_19, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'20',1,0)),'9999'))),3) lgs_20, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'21',1,0)),'9999'))),3) lgs_21, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'22',1,0)),'9999'))),3) lgs_22, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'23',1,0)),'9999'))),3) lgs_23 FROM V$log_history lh, archived_logs al WHERE lh.first_time > sysdate - 30 AND lh.sequence#=al.sequence# GROUP BY SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),1,10) ORDER BY to_date(lgs_date, 'dd.mm.yyyy hh24mi'); Day GB/day Switch/day 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 ---------- --------- ---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- 05.08.2024 350 50 0 0 0 0 0 0 0 0 0 0 0 1 4 4 4 4 4 4 5 4 4 4 4 4 06.08.2024 1087 108 9 4 7 6 4 4 4 4 4 4 4 4 4 4 4 4 4 4 6 4 4 4 4 4 07.08.2024 898 104 8 7 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 5 4 4 4 4 4 08.08.2024 921 105 8 5 6 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 6 4 4 4 4 4 09.08.2024 976 104 8 4 4 6 4 4 4 4 4 4 4 4 4 4 4 4 4 4 6 4 4 4 4 4 10.08.2024 493 97 4 4 4 4 4 4 4 4 4 4 5 4 4 4 4 4 4 4 4 4 4 4 4 4 11.08.2024 491 97 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 5 4 4
set linesize 300 pagesize 300 col HOUR for a20 select to_char(first_time,'DD-MM-YYYY HH24:MI') as hour,count(*) as num_switches, round((sum(blocks*block_size)/1024/1024/1024)) "REDO PER DAY (GB)" from gv$archived_log where 1=1 and first_time >sysdate -7 --and first_time between to_date('05/07/2023 00:00:00','DD/MM/YYYY HH24:MI:SS') and to_date('05/07/2023 02:00:00','DD/MM/YYYY HH24:MI:SS') group by to_char(first_time,'DD-MM-YYYY HH24:MI') order by 1;******************************* -- redo per hour set pagesize 100 col stat_name format a30 col MB format 999,999,999.99 select btime, stat_name, round((end_value-beg_value)/(1024*1024),2) MB from ( select e.stat_name, to_char(s.BEGIN_INTERVAL_TIME,'DD-MON-YY HH24:MI') btime, e.value end_value, Lag (e.value) OVER( PARTITION BY e.stat_name ORDER BY s.snap_id) beg_value from DBA_HIST_SYSSTAT e, DBA_HIST_SNAPSHOT s where s.snap_id=e.snap_id and s.begin_interval_time > sysdate -2 and e.stat_name = 'redo size' order by e.stat_name, begin_interval_time ) where end_value-beg_value > 0 order by btime;
--query shows time between log switches select b.recid, to_char(b.first_time,'dd-mon-yy hh24:mi:ss') start_time, a.recid, to_char(a.first_time,'dd-mon-yy hh24:mi:ss') end_time, round(((a.first_time-b.first_time)*25)*60,2) minutes from v$log_history a, v$log_history b where a.recid = b.recid+1 --and a.first_time between to_date('2024-05-29:08:00:00','yyyy-mm-dd:hh24:mi:ss') and to_date('2024-05-29:17:00:00','yyyy-mm-dd:hh24:mi:ss') and a.first_time > sysdate -1 order by a.first_time asc
validate archivelog sequence xxxx;
Subscribe to:
Posts (Atom)
