Oracle Database Info ... from v$database
PROMPT ---- Oracle 9i
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 a70
COLUMN column2 FORMAT a60
set pages 0 linesize 200
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)
|| 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)
|| TRIM(SUBSTR('ArchiveLog Mode : ' || d.log_mode ,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)
|| 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)
|| 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)
|| 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)
|| 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
FROM v$database d ;
PROMPT ---- Oracle 10g
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 a70
COLUMN column2 FORMAT a60
set pages 0 linesize 200
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)
|| TRIM(SUBSTR('DB Unique Name : ' || d.db_unique_name ,1,50)) || chr(10)
|| 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)
|| TRIM(SUBSTR('Flashback ON : ' || d.flashback_on ,1,50)) || chr(10)
|| TRIM(SUBSTR('ArchiveLog Mode : ' || d.log_mode ,1,50)) || chr(10)
|| 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)
|| TRIM(SUBSTR('Last Open Incarnation# : ' || d.last_open_incarnation# ,1,50)) || chr(10)
|| 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)
|| 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)
-- || 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
,
'[DataGuard Information]' || chr(10)
--|| TRIM(SUBSTR('Primary DB Unique Name : ' || d.primary_db_unique_name ,1,50)) || chr(10)
|| TRIM(SUBSTR('DataGuard 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)
|| TRIM(SUBSTR('SwitchOver SCN : ' || d.switchover# ,1,50)) || chr(10)
|| 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)
-- || TRIM(SUBSTR('Supplemental Log Data PL : ' || d.supplemental_log_data_pl ,1,50)) || chr(10)
|| chr(10)
|| '[Fast Start Failover Info]' || chr(10)
|| TRIM(SUBSTR('FS Failover Status : ' || d.fs_failover_status ,1,50)) || chr(10)
|| TRIM(SUBSTR('FS Failover Current Target : ' || d.fs_failover_current_target ,1,50)) || chr(10)
|| TRIM(SUBSTR('FS Failover Threshold : ' || d.fs_failover_threshold ,1,50)) || chr(10)
|| TRIM(SUBSTR('FS Failover Observer Present: ' || d.fs_failover_observer_present ,1,50)) || chr(10)
--|| TRIM(SUBSTR('FS Failover Observer Host : ' || d.fs_failover_observer_host ,1,50)) || chr(10)
column2
FROM v$database d ;
PROMPT ----oracle 11gr2 and Oracle 12c<<<<<<<<<<<<<<<
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 a70
COLUMN column2 FORMAT a60
set pages 0 linesize 200
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)
|| TRIM(SUBSTR('DB Unique Name : ' || d.db_unique_name ,1,50)) || chr(10)
|| 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)
|| TRIM(SUBSTR('Flashback ON : ' || d.flashback_on ,1,50)) || chr(10)
|| TRIM(SUBSTR('ArchiveLog Mode : ' || d.log_mode ,1,50)) || chr(10)
|| 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)
|| TRIM(SUBSTR('Last Open Incarnation# : ' || d.last_open_incarnation# ,1,50)) || chr(10)
|| 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)
|| 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)
|| 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
,
'[DataGuard Information]' || chr(10)
|| TRIM(SUBSTR('Primary DB Unique Name : ' || d.primary_db_unique_name ,1,50)) || chr(10)
|| TRIM(SUBSTR('DataGuard 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)
|| TRIM(SUBSTR('SwitchOver SCN : ' || d.switchover# ,1,50)) || chr(10)
|| 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)
|| TRIM(SUBSTR('Supplemental Log Data PL : ' || d.supplemental_log_data_pl ,1,50)) || chr(10)
|| chr(10)
|| '[Fast Start Failover Info]' || chr(10)
|| TRIM(SUBSTR('FS Failover Status : ' || d.fs_failover_status ,1,50)) || chr(10)
|| TRIM(SUBSTR('FS Failover Current Target : ' || d.fs_failover_current_target ,1,50)) || chr(10)
|| TRIM(SUBSTR('FS Failover Threshold : ' || d.fs_failover_threshold ,1,50)) || chr(10)
|| TRIM(SUBSTR('FS Failover Observer Present: ' || d.fs_failover_observer_present ,1,50)) || chr(10)
|| TRIM(SUBSTR('FS Failover Observer Host : ' || d.fs_failover_observer_host ,1,50)) || chr(10)
column2
FROM v$database d ;
====
for [DataGuard Information] Info !!!
set pagesize 200
SELECT /* First Column */
'[DataGuard Information]' || chr(10)
|| TRIM(SUBSTR('Primary DB Unique Name : ' || d.primary_db_unique_name ,1,50)) || chr(10)
|| TRIM(SUBSTR('DataGuard 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)
|| TRIM(SUBSTR('SwitchOver SCN : ' || d.switchover# ,1,50)) || chr(10)
|| 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)
|| TRIM(SUBSTR('Supplemental Log Data PL : ' || d.supplemental_log_data_pl ,1,50)) || chr(10)
|| chr(10)
|| '[Fast Start Failover Info]' || chr(10)
|| TRIM(SUBSTR('FS Failover Status : ' || d.fs_failover_status ,1,50)) || chr(10)
|| TRIM(SUBSTR('FS Failover Current Target : ' || d.fs_failover_current_target ,1,50)) || chr(10)
|| TRIM(SUBSTR('FS Failover Threshold : ' || d.fs_failover_threshold ,1,50)) || chr(10)
|| TRIM(SUBSTR('FS Failover Observer Present: ' || d.fs_failover_observer_present ,1,50)) || chr(10)
|| TRIM(SUBSTR('FS Failover Observer Host : ' || d.fs_failover_observer_host ,1,50)) || chr(10)
column2
FROM v$database d ;
