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.

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 

Oracle software download

Oracle 10g download

http://uni-smr.ac.ru/archive/db/Oracle/10g/

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  

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 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

)

/

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;