103 lines
8.9 KiB
SQL
103 lines
8.9 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display DB Information
|
|
* Versions : 9.2 and above
|
|
* Parameters : None
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 05-Apr-13 Vishal Gupta Created
|
|
*/
|
|
|
|
|
|
PROMPT *********************************************
|
|
PROMPT * D A T A B A S E I N F O R M A T I O N
|
|
PROMPT *********************************************
|
|
PROMPT
|
|
PROMPT
|
|
|
|
COLUMN column1 FORMAT a50
|
|
COLUMN column2 FORMAT a50
|
|
|
|
set pages 0
|
|
SELECT /* First Column */
|
|
'[DB Info]' || chr(10)
|
|
|| TRIM(SUBSTR('DB ID : ' || d.dbid ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('DB Name : ' || d.name ,1,50)) || chr(10)
|
|
&&_IF_ORA_10gR1_OR_HIGHER || TRIM(SUBSTR('DB Unique Name : ' || d.db_unique_name ,1,50)) || chr(10)
|
|
&&_IF_ORA_10gR1_OR_HIGHER || TRIM(SUBSTR('Platform : ' || d.platform_name ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('DB Created : ' || TO_CHAR(d.created,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Open Mode : ' || d.open_mode ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Open Resetlogs : ' || d.open_resetlogs ,1,50)) || chr(10)
|
|
&&_IF_ORA_10gR1_OR_HIGHER || TRIM(SUBSTR('Flashback ON : ' || d.flashback_on ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('ArchiveLog Mode : ' || d.log_mode ,1,50)) || chr(10)
|
|
&&_IF_ORA_10gR1_OR_HIGHER || TRIM(SUBSTR('ArchiveLog Compression : ' || d.archivelog_compression ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Force Logging : ' || d.force_logging ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Remote Archive : ' || d.remote_archive ,1,50)) || chr(10)
|
|
&&_IF_ORA_10gR1_OR_HIGHER || TRIM(SUBSTR('Last Open Incarnation# : ' || d.last_open_incarnation# ,1,50)) || chr(10)
|
|
&&_IF_ORA_10gR1_OR_HIGHER || TRIM(SUBSTR('Recovery Target Inc# : ' || d.recovery_target_incarnation# ,1,50)) || chr(10)
|
|
|| chr(10)
|
|
|| '[Timestamps]' || chr(10)
|
|
|| TRIM(SUBSTR('DB Created : ' || TO_CHAR(d.created,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Controlfile Created : ' || TO_CHAR(d.controlfile_created,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Controlfile Time : ' || TO_CHAR(d.controlfile_time,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Version Time : ' || TO_CHAR(d.version_time,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Resetlogs Time : ' || TO_CHAR(d.resetlogs_time,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Prior Resetlogs Time : ' || TO_CHAR(d.prior_resetlogs_time,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)
|
|
|| chr(10)
|
|
|| '[System Change Number]' || chr(10)
|
|
&&_IF_ORA_10gR1_OR_HIGHER || TRIM(SUBSTR('Current SCN : ' || d.current_scn ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Resetlogs SCN : ' || d.resetlogs_change# ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Prior Resetlogs SCN : ' || d.prior_resetlogs_change# ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Checkpoint SCN : ' || d.checkpoint_change# ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Controlfile SCN : ' || d.controlfile_change# ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Archivelog Highest NextSCN : ' || d.archivelog_change# ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Force Archivelog SCN : ' || d.archive_change# ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Archivelog SCN : ' || d.archive_change# ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Standby Became Primary SCN : ' || d.standby_became_primary_scn ,1,50)) || chr(10)
|
|
|| chr(10)
|
|
|| '[Controlfile Info]' || chr(10)
|
|
|| TRIM(SUBSTR('Controlfile Type : ' || d.controlfile_type ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Controlfile Created : ' || TO_CHAR(d.controlfile_created,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)
|
|
&&_IF_ORA_11gR1_OR_HIGHER || TRIM(SUBSTR('Controlfile Converted : ' || d.controlfile_converted ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Controlfile SCN : ' || d.controlfile_change# ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Controlfile Sequence# : ' || d.controlfile_sequence# ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Controlfile Time : ' || TO_CHAR(d.controlfile_time,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)
|
|
column1
|
|
, /* Second Column */
|
|
'[DataGuard Information]' || chr(10)
|
|
&&_IF_ORA_11gR1_OR_HIGHER || TRIM(SUBSTR('Primary DB Unique Name : ' || d.primary_db_unique_name ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Standby DB Unique Name : ' || CASE WHEN d.database_role LIKE '%STANDBY%' THEN d.db_unique_name ELSE '' END ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Database Role : ' || d.database_role ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Protection Mode : ' || d.protection_mode ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Protection Level : ' || d.protection_level ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('DataGuard Broker : ' || d.dataguard_broker ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('DataGuard Status : ' || d.guard_status ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('SwitchOver Status : ' || d.switchover_status ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Activation SCN : ' || d.activation# ,1,50)) || chr(10)
|
|
&&_IF_ORA_10gR1_OR_HIGHER || TRIM(SUBSTR('SwitchOver SCN : ' || d.switchover# ,1,50)) || chr(10)
|
|
&&_IF_ORA_10gR1_OR_HIGHER || TRIM(SUBSTR('Standby Became Primary SCN : ' || d.standby_became_primary_scn ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Supplemental Log Data MIN : ' || d.supplemental_log_data_min ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Supplemental Log Data PK : ' || d.supplemental_log_data_pk ,1,50)) || chr(10)
|
|
|| TRIM(SUBSTR('Supplemental Log Data UI : ' || d.supplemental_log_data_ui ,1,50)) || chr(10)
|
|
&&_IF_ORA_10gR1_OR_HIGHER || TRIM(SUBSTR('Supplemental Log Data FK : ' || d.supplemental_log_data_fk ,1,50)) || chr(10)
|
|
&&_IF_ORA_11gR1_OR_HIGHER || TRIM(SUBSTR('Supplemental Log Data PL : ' || d.supplemental_log_data_pl ,1,50)) || chr(10)
|
|
&&_IF_ORA_10gR1_OR_HIGHER || TRIM(SUBSTR('Supplemental Log Data ALL : ' || d.supplemental_log_data_all ,1,50)) || chr(10)
|
|
|| chr(10)
|
|
&&_IF_ORA_10gR2_OR_HIGHER || '[Fast Start Failover Info]' || chr(10)
|
|
&&_IF_ORA_10gR2_OR_HIGHER || TRIM(SUBSTR('FS Failover Status : ' || d.fs_failover_status ,1,50)) || chr(10)
|
|
&&_IF_ORA_10gR2_OR_HIGHER || TRIM(SUBSTR('FS Failover Current Target : ' || d.fs_failover_current_target ,1,50)) || chr(10)
|
|
&&_IF_ORA_10gR2_OR_HIGHER || TRIM(SUBSTR('FS Failover Threshold : ' || d.fs_failover_threshold ,1,50)) || chr(10)
|
|
&&_IF_ORA_10gR2_OR_HIGHER || TRIM(SUBSTR('FS Failover Observer Present: ' || d.fs_failover_observer_present ,1,50)) || chr(10)
|
|
&&_IF_ORA_10gR2_OR_HIGHER || TRIM(SUBSTR('FS Failover Observer Host : ' || d.fs_failover_observer_host ,1,50)) || chr(10)
|
|
column2
|
|
FROM v$database d
|
|
;
|
|
|
|
|
|
@@footer |