Search This Blog

Total Pageviews

Monday 17 November 2014

ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation

ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation 

error
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation
FROM  ."GGS_SETUP" ERROR at line 2: ORA-00942: table or view does not exist
FROM "GGATE" ."GGS_SETUP" ERROR at line 2:ORA-00942: table or view does not exist



error in GoldenGate while setting up ....

[oracle@db ~]$ cd /u01/app/oracle/product/gg/
[oracle@db gg]$ sqlplus / as sysdba

SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @ddl_enable.sql


ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation
FROM  ."GGS_SETUP" ERROR at line 2: ORA-00942: table or view does not exist
FROM "GGATE" ."GGS_SETUP" ERROR at line 2:ORA-00942: table or view does not exist




DDL TRIGGER RUNNING STATUS
-----------------------------------
ENABLED
FROM "GGATE" ."GGS_SETUP"
              *
ERROR at line 2:
ORA-00942: table or view does not exist


FROM "GGATE" ."GGS_SETUP"
              *
ERROR at line 2:
ORA-00942: table or view does not exist


FROM "GGATE" ."GGS_SETUP"
              *
ERROR at line 2:
ORA-00942: table or view does not exist



==============================
solution 

drop this trigger 
as sys

SQL> drop TRIGGER SYS.GGS_DDL_TRIGGER_BEFORE ;

Trigger dropped.


grant following 
SQL> grant create any table to ggate;
grant create any view to ggate;
grant create any procedure to ggate;
grant create any sequence to ggate;
grant create any index to ggate;
grant create any trigger to ggate;
grant create any view to ggate;



Check this shold be true
SQL> show parameter ENABLE_GOLDENGATE_REPLICATION

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication        boolean     TRUE




[oracle@db ~]$ cd /u01/app/oracle/product/gg/
[oracle@db gg]$ sqlplus / as sysdba

SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @ddl_enable.sql


our user ggate ...



SQL>  @ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggate

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.







Using GGATE as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE

CLEAR_TRACE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

CREATE_TRACE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

TRACE_PUT_LINE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

INITIAL_SETUP STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDL IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX  PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
-----------------------------------
ENABLED

STAYMETADATA IN TRIGGER
-----------------------------------
OFF

DDL TRIGGER SQL TRACING
-----------------------------------
0

DDL TRIGGER TRACE LEVEL
-----------------------------------
0

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/vihaan/vihaan/trace/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.
SQL> @ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggate

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.


Using GGATE as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE

CLEAR_TRACE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

CREATE_TRACE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

TRACE_PUT_LINE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

INITIAL_SETUP STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDL IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX  PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
-----------------------------------
ENABLED

STAYMETADATA IN TRIGGER
-----------------------------------
OFF

DDL TRIGGER SQL TRACING
-----------------------------------
0

DDL TRIGGER TRACE LEVEL
-----------------------------------
0

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/vihaan/vihaan/trace/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.
SQL> @role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ggate
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.


Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where <loggedUser> is the user assigned to the GoldenGate processes.
SQL> grant GGS_GGSUSER_ROLE to ggate;

Grant succeeded.

SQL> @ddl_enable.sql

Trigger altered.

Saturday 15 November 2014

Search string in database


Oracle find string or number in the database

search string
find text in database



set serveroutput on size 1000000
accept string char prompt "Enter Char String to search for:"
accept owner_name prompt "Enter Owner Name:"

set term off
set verify off
set lines 80
set feedback off

spool find_string.tmp

prompt spool find_string.out
prompt set feedback off
prompt set pages 1000
prompt set lines 132
prompt set wrap on
prompt break on found_in skip 1
prompt col found_in format a55 heading "Found In .."
prompt col string format a70 heading "String Found"
-- prompt col row_id format 99999999999999999999
prompt set term on
prompt 

declare

cursor varchar_tables_cur IS
select owner
, table_name
, column_name
from dba_tab_columns
where owner != 'SYS'
and owner != 'SYSTEM'
and owner = upper('&OWNER_NAME')
and data_type in ( 'VARCHAR2','VARCHAR','CHAR');

begin
for v_t_rec in varchar_tables_cur loop
dbms_output.put_line('--');
-- dbms_output.put_line('prompt '||v_t_rec.owner||'.'||v_t_rec.table_name||'.'||v_t_rec.column_name||'');
dbms_output.put('select '''||v_t_rec.owner||'.'||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');
dbms_output.new_line;
dbms_output.put(''||v_t_rec.column_name||' String ');
dbms_output.new_line;
-- dbms_output.put_line('rowid Row_id ');
dbms_output.put_line('from '||v_t_rec.owner||'.'||v_t_rec.table_name||'');
dbms_output.put_line('where '||v_t_rec.column_name||' like '||chr(39)||'&string'||chr(39)||'');
dbms_output.put_line('/'); 
end loop;
end;
/

prompt spool off
prompt prompt ====> created file find_string.out for your review

spool off

@find_string.tmp


===


-- set term off
set verify off
set lines 80
set feedback off
set serveroutput on


spool find_string.tmp


prompt spool find_string.out
prompt set feedback off
prompt set pages 1000
prompt set lines 132
prompt set wrap on
prompt break on found_in skip 1
prompt col found_in format a55 heading "Found In .."
prompt col string format a70 heading "String Found"
-- prompt col row_id format 99999999999999999999
prompt set term on
prompt

declare
cursor varchar_tables_cur IS
select table_name,
column_name
from user_tab_columns a, user_objects b
where data_type in ( 'VARCHAR2','VARCHAR','CHAR')
and object_name=table_name
and object_type='TABLE' ;
-- and a.OWNER not in ('SYS','SYSTEM') ;
begin
for v_t_rec in varchar_tables_cur loop
dbms_output.put_line('--');
-- dbms_output.put_line('prompt '||v_t_rec.table_name||'.'||v_t_rec.column_name||'');
dbms_output.put('select distinct'''||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');
dbms_output.new_line;
dbms_output.put(''||v_t_rec.column_name||' String ');
dbms_output.new_line;
-- dbms_output.put_line('rowid Row_id ');
dbms_output.put_line('from '||v_t_rec.table_name||'');
dbms_output.put_line('where '||v_t_rec.column_name||' like '||chr(39)||'&string'||chr(39)||'');
dbms_output.put_line('/');
end loop;
end;
/

prompt spool off
prompt prompt ====> created file find_string.out for your review

spool off

@find_string.tmp
===
Oracle search string from a database 

-- set term off
set verify off
set lines 80
set feedback off
set serveroutput on


spool find_string.tmp


prompt spool find_string.out
prompt set feedback off
prompt set pages 1000
prompt set lines 132
prompt set wrap on
prompt break on found_in skip 1
prompt col found_in format a55 heading "Found In .."
prompt col string format a70 heading "String Found"
-- prompt col row_id format 99999999999999999999
prompt set term on
prompt

declare
cursor varchar_tables_cur IS
select table_name,a.owner owner, column_name
-- from user_tab_columns a, user_objects b
from dba_tab_columns a, dba_objects b
where data_type in ( 'VARCHAR2','VARCHAR','CHAR')
and object_name=table_name
and object_type='TABLE'
and a.OWNER not in ('SYS','SYSTEM') ;
begin
for v_t_rec in varchar_tables_cur loop
dbms_output.put_line('--');
-- dbms_output.put_line('prompt '||v_t_rec.table_name||'.'||v_t_rec.column_name||'');
dbms_output.put('select distinct'''||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');
dbms_output.new_line;
dbms_output.put(''||v_t_rec.column_name||' String ');
dbms_output.new_line;
-- dbms_output.put_line('rowid Row_id ');
dbms_output.put_line('from '||v_t_rec.owner||'.'||v_t_rec.table_name||'');
dbms_output.put_line('where '||v_t_rec.column_name||' like '||chr(39)||'&string'||chr(39)||'');
dbms_output.put_line('/');
end loop;
end;
/

prompt spool off
prompt prompt ====> created file find_string.out for your review

spool off

@find_string.tmp

====
set serveroutput on size 1000000
accept string char prompt "Enter Char String to search for:"
accept owner_name prompt "Enter Owner Name:"

set term off
set verify off
set lines 80
set feedback off

spool find_string.tmp

prompt spool c:\temp\find_string.out
prompt set feedback off
prompt set pages 1000
prompt set lines 132
prompt set wrap on
prompt break on found_in skip 1
prompt col found_in format a55 heading "Found In
.."
p rompt col string format a70 heading "String Found"
-- prompt col row_id format 99999999999999999999
prompt set term on
prompt

declare

cursor varchar_tables_cur IS
select owner
, table_name
, column_name
from dba_tab_columns
where owner != 'SYS'
and owner != 'SYSTEM'
and owner = '&OWNER_NAME'
and data_type in ( 'VARCHAR2','VARCHAR','CHAR');

begin
for v_t_rec in varchar_tables_cur loop
dbms_output.put_line('--');
-- dbms_output.put_line('prompt '||v_t_rec.owner||'.'||v_t_rec.table_name||'.'||v_t_rec.column_n ame||'');
dbms_output.put('select '''||v_t_rec.owner||'.'||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');
dbms_output.new_line;
dbms_output.put(''||v_t_rec.column_name||' String ');
dbms_output.new_line;
-- dbms_output.put_line('rowid Row_id ');
dbms_output.put_line('from '||v_t_rec.owner||'.'||v_t_rec.table_name||'');
dbms_output.put_line('where '||v_t_rec.column_name||' like '||chr(39)||'%&string%'||chr(39)||'');
dbms_output.put_line('/');
end loop;
end;
/

prompt spool off
prompt prompt ====> created file find_string.out for your review

spool off

@find_string.tmp

sqlplus set vi editor



vi editor in sqlplus   ..



vi editor in sqlplus 

EDITOR=vi; export EDITOR    in .profile file 

Saturday 8 November 2014

Oracle Parameter info

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.

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




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 9G999G999D99
column 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
)
/


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;



validate archivelog sequence xxxx;

Oracle DBA

anuj blog Archive