Oracle Database Info ... from v$database
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 ;
PROMPT 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('CDB : ' || d.CDB ,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)
|| '[Pluggable Database Info]' || chr(10)
|| TRIM(SUBSTR('CDB : ' || d.CDB ,1,50)) || chr(10)
|| TRIM(SUBSTR('CON_ID : ' || d.CON_ID ,1,50)) || chr(10)
|| TRIM(SUBSTR('PENDING_ROLE CHANGE TASKS : ' || d.PENDING_ROLE_CHANGE_TASKS ,1,50)) || chr(10)
|| TRIM(SUBSTR('CON DBID : ' || d.CON_DBID ,1,50)) || chr(10)
|| TRIM(SUBSTR('FORCE FULL DB CACHING : ' || d.FORCE_FULL_DB_CACHING ,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)
|| '[Controlfile Info]' || chr(10)
|| TRIM(SUBSTR('Controlfile Type : ' || d.controlfile_type ,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 Standby info
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('CDB : ' || d.CDB ,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)
|| '[Controlfile Info]' || chr(10)
|| TRIM(SUBSTR('Controlfile Type : ' || d.controlfile_type ,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 ;
-- DataGuard Information
set pagesize 300
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 ;
********************
set pagesize 200 linesize 200
select 'DB_NAME' name , name value from v$database
union all
select 'DB_UNIQUE_NAME' name , db_unique_name value from v$database
union all
select 'OPEN_MODE' name , open_mode value from v$database
union all
select 'INSTANCE NAME' name , instance_name from v$instance
union all
select 'INSTANCE STATUS' name , status from v$instance
union all
select 'LOG_MODE' name , log_mode value from v$database
union all
select 'DATABASE_ROLE' name , database_role from v$database
union all
select 'PROTECTION_MODE' name , protection_mode from v$database
union all
select 'PROTECTION_LEVEl' name , protection_level from v$database
union all
select 'SWITCHOVER_STATUS' name , switchover_status from v$database
union all
select 'GUARD STATUS' name , guard_status from v$database
union all
select 'FORCE LOGGING**' name , force_logging from v$database
union
select 'CONTROLFILE TYPE' , controlfile_type from v$database
union all
select 'MRP Process..sequence', process||' '||status||' '||to_char(sequence#) from gv$managed_standby where PROCESS='MRP0'
;
===
set head off verify off echo off pages 150 linesize 120 feedback off
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select distinct
'DATABASE_NAME..............................................: '|| name,
'INSTANCE NAME..............................................: '|| instance_name,
'INST_ID....................................................: '|| inst_id,
'DB UNIQUE NAME.............................................: '|| db_unique_name ,
'dbid.......................................................: '|| dbid ,
'LOG_MODE...................................................: '|| log_mode,
'OPEN MODE..................................................: '|| open_mode,
'DATABASE_STATUS............................................: '|| database_status,
'SHUTDOWN_PENDING ..........................................: '|| shutdown_pending,
'VERSION ...................................................: '|| version,
'INSTANCE ROLE .............................................: '|| instance_role,
'HOSTNAME...................................................: '|| host_name,
'STATUS.....................................................: '|| i.status,
'LOGINS.....................................................: '|| logins,
'STARTUP-TIME...............................................: '|| startup_time,
'ARCHIVER...................................................: '|| archiver,
'DATAGUARD BROKER...........................................: '|| dataguard_broker,
'GUARD STATUS...............................................: '|| guard_status,
'FLASHBACK ON...............................................: '|| flashback_on,
'PROTECTION MODE............................................: '|| protection_mode,
'CONTROLFILE TYPE...........................................: '|| controlfile_type,
'FORCE LOGGING..............................................: '|| force_logging,
'SWITCHOVER_STATUS..........................................: '|| switchover_status,
'DATABASE_ROLE*****.........................................: '|| database_role,
'PARALLEL...................................................: '|| parallel,
'WRL_PARAMETER..............................................: '|| wrl_parameter,
'WALLET STATUS..............................................: '|| w.status,
-- 'WALLET_TYPE................................................: '|| wallet_type,
'FS_FAILOVER_STATUS.........................................: '|| fs_failover_status,
'FS_FAILOVER_CURRENT_TARGET.................................: '|| fs_failover_current_target,
'FS_FAILOVER_THRESHOLD......................................: '|| fs_failover_threshold,
'FS_FAILOVER_OBSERVER_PRESENT...............................: '|| fs_failover_observer_present,
'FS_FAILOVER_OBSERVER_HOST..................................: '|| fs_failover_observer_host,
'SYSDATE....................................................: '|| sysdate
from gv$instance i, v$database d, v$encryption_wallet w
-- where 1=1
-- and i.inst_id=d.inst_id
order by 2;
set head on verify on echo on feedback on
==============================================
alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss' ;
set pagesize 0
select '# --------------------------------------------------'||chr(10)||
'# -- Database Information -------------------------'||chr(10)||
'# --------------------------------------------------'||chr(10)||
'Name : '||name ||chr(10) ||
'Database Role : '||database_role ||chr(10) ||
'Created : '||created ||chr(10) ||
'Log Mode : '||log_mode ||chr(10) ||
'Open Mode : '||open_mode ||chr(10) ||
'Protection Mode : '||protection_mode ||chr(10) ||
'Protection Level : '||protection_level ||chr(10) ||
'Current SCN : '||current_scn ||chr(10) ||
'Flashback on : '||flashback_on||chr(10) ||
'Open Mode : '||open_mode ||chr(10) ||
-- 'Primary DB Unique Name : '||primary_db_unique_name ||chr(10)||
'DB Unique Name : '||db_unique_name ||chr(10)||
'Archivelog Change# : '||archivelog_change# ||chr(10)||
-- 'Archivelog Compression : '||archivelog_compression ||chr(10)||
'Switchover Status : '||switchover_status ||chr(10)||
'Remote Arachive : '||remote_archive ||chr(10)||
'Supplemental Log PK : '||supplemental_log_data_pk ||' - '||
'Supplemental Log UI : '||supplemental_log_data_ui ||chr(10)||
'Data Guard Broker :' ||dataguard_broker||chr(10) ||
'Force Logging : '||force_logging
from v$database
/
set pagesize 80
===
col SYSTEM_ITEM for a30
col SYSTEM_VALUE for a50
WITH
rac AS (SELECT COUNT(*) instances, CASE COUNT(*) WHEN 1 THEN 'Single-instance' ELSE COUNT(*)||'-node RAC cluster' END db_type FROM gv$instance),
mem AS (SELECT SUM(value) target FROM gv$system_parameter2 WHERE name = 'memory_target'),
sga AS (SELECT SUM(value) target FROM gv$system_parameter2 WHERE name = 'sga_target'),
pga AS (SELECT SUM(value) target FROM gv$system_parameter2 WHERE name = 'pga_aggregate_target'),
db_block AS (SELECT value bytes FROM v$system_parameter2 WHERE name = 'db_block_size'),
db AS (SELECT name, platform_name FROM v$database),
pdbs AS (SELECT * FROM v$pdbs),
inst AS (SELECT host_name, version db_version FROM v$instance),
data AS (SELECT SUM(bytes) bytes, COUNT(*) files, COUNT(DISTINCT ts#) tablespaces FROM v$datafile),
temp AS (SELECT SUM(bytes) bytes FROM v$tempfile),
log AS (SELECT SUM(bytes) * MAX(members) bytes FROM v$log),
control AS (SELECT SUM(block_size * file_size_blks) bytes FROM v$controlfile),
cell AS (SELECT COUNT(DISTINCT cell_name) cnt FROM v$cell_state),
core AS (SELECT SUM(value) cnt FROM gv$osstat WHERE stat_name = 'NUM_CPU_CORES'),
cpu AS (SELECT SUM(value) cnt FROM gv$osstat WHERE stat_name = 'NUM_CPUS'),
pmem AS (SELECT SUM(value) bytes FROM gv$osstat WHERE stat_name = 'PHYSICAL_MEMORY_BYTES')
SELECT
'Database name:' system_item, db.name system_value FROM db
UNION ALL
SELECT ' pdb:'||name, 'Open Mode:'||open_mode FROM pdbs -- need 12c flag
UNION ALL
SELECT 'Oracle Database version:', inst.db_version FROM inst
UNION ALL
SELECT 'Database block size:', TRIM(TO_CHAR(db_block.bytes / POWER(2,10), '90'))||' KB' FROM db_block
UNION ALL
SELECT 'Database size:', TRIM(TO_CHAR(ROUND((data.bytes + temp.bytes + log.bytes + control.bytes) / POWER(10,12), 3), '999,999,990.000'))||' TB'
FROM db, data, temp, log, control
UNION ALL
SELECT 'Datafiles:', data.files||' (on '||data.tablespaces||' tablespaces)' FROM data
UNION ALL
SELECT 'Database configuration:', rac.db_type FROM rac
UNION ALL
SELECT 'Database memory:',
CASE WHEN mem.target > 0 THEN 'MEMORY '||TRIM(TO_CHAR(ROUND(mem.target / POWER(2,30), 1), '999,990.0'))||' GB, ' END||
CASE WHEN sga.target > 0 THEN 'SGA ' ||TRIM(TO_CHAR(ROUND(sga.target / POWER(2,30), 1), '999,990.0'))||' GB, ' END||
CASE WHEN pga.target > 0 THEN 'PGA ' ||TRIM(TO_CHAR(ROUND(pga.target / POWER(2,30), 1), '999,990.0'))||' GB, ' END||
CASE WHEN mem.target > 0 THEN 'AMM' ELSE CASE WHEN sga.target > 0 THEN 'ASMM' ELSE 'MANUAL' END END
FROM mem, sga, pga
UNION ALL
SELECT 'Hardware:', CASE WHEN cell.cnt > 0 THEN 'Engineered System '||
CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%5675%' THEN 'X2-2 ' END||
CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%2690%' THEN 'X3-2 ' END||
CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%2697%' THEN 'X4-2 ' END||
CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%2699%' THEN 'X5-2 or X-6 ' END||
CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%8160%' THEN 'X7-2 ' END||
CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%8870%' THEN 'X3-8 ' END||
CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%8895%' THEN 'X4-8 or X5-8 ' END||
'with '||cell.cnt||' storage servers'
ELSE 'Unknown' END FROM cell
UNION ALL
SELECT 'Processor:', 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' FROM DUAL
UNION ALL
SELECT 'Physical CPUs:', core.cnt||' cores'||CASE WHEN rac.instances > 0 THEN ', on '||rac.db_type END FROM rac, core
UNION ALL
SELECT 'Oracle CPUs:', cpu.cnt||' CPUs (threads)'||CASE WHEN rac.instances > 0 THEN ', on '||rac.db_type END FROM rac, cpu
UNION ALL
SELECT 'Physical RAM:', TRIM(TO_CHAR(ROUND(pmem.bytes / POWER(2,30), 1), '999,990.0'))||' GB'||CASE WHEN rac.instances > 0 THEN ', on '||rac.db_type END FROM rac, pmem
UNION ALL
SELECT 'Operating system:', db.platform_name FROM db;
SYSTEM_ITEM SYSTEM_VALUE
------------------------------ --------------------------------------------------
Database name: SSSSJJJ
pdb:XX Open Mode:READ ONLY
pdb:XX Open Mode:READ WRITE
Oracle Database version: 12.2.0.1.0
Database block size: 8 KB
Database size: 48.576 TB
Datafiles: 1538 (on 46 tablespaces)
Database configuration: Single-instance
Database memory: SGA 180.0 GB, PGA 20.0 GB, ASMM
Hardware: Unknown
Processor: Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz
Physical CPUs: 112 cores, on Single-instance
Oracle CPUs: 224 CPUs (threads), on Single-instance
Physical RAM: 754.1 GB, on Single-instance
Operating system: Linux x86 64-bit
=====
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set numw 8 lines 300 timing off echo off pages 10000
col name for a40
col dest_name for a20
col value for a18
col status for a12
col stby for a4
col error for a5
col type for a10
col destination for a25
col database_mode for a15
col db_unique_name for a14
col primary_db_unique_name for a22
col dg_broker for a9
col gap_status for a15
col error for a50
col RMAN1 for a60
col RMAN2 for a60
col inst for 9999
col instname for a12
col host_name for a25
col version for a10
col status for a15
col dbstatus for a10
col db_unique_name for a12
col flashback_on for a20
col instance_role for a18
col wrl_type for a15
col wrl_parameter for a70
col DBID for 999999999999999
col FILENAME for a60
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
prompt *** v$database ***
select inst_id, instance_name instname,startup_time, host_name, version, status, parallel, thread#, archiver, logins, shutdown_pending, database_status dbstatus, instance_role from gv$instance order by inst_id;
select inst_id, dbid, name, db_unique_name, open_mode, log_mode, flashback_on, switchover_status, database_role,CONTROLFILE_TYPE from gv$database order by inst_id;
select DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,REMOTE_ARCHIVE,SWITCHOVER_STATUS,DATAGUARD_BROKER DG_BROKER,PRIMARY_DB_UNIQUE_NAME
from gv$database;
select * from gv$encryption_wallet order by inst_id;
select filename, status, bytes from v$block_change_tracking
prompt *** gv$archive_dest ***
SELECT thread#, dest_id, destination, gvad.status, target, schedule, process, mountid mid FROM gv$archive_dest gvad, gv$instance gvi
WHERE gvad.inst_id = gvi.inst_id
AND schedule='ACTIVE'
AND destination is NOT NULL
ORDER BY thread#, dest_id
;
prompt *** gv$archive_dest_status ***
select
s.DEST_ID,s.STATUS,s.DATABASE_MODE,s.RECOVERY_MODE,s.GAP_STATUS, NVL(s.ERROR,'NONE') error from gv$archive_dest_status s, gv$archive_dest d
where s.dest_id=d.dest_id
and d.schedule<>'INACTIVE'
and s.database_mode<>'UNKNOWN'
;
prompt *** v$thread ***
select thread#,sequence# "CURRENT LOG SEQUENCE",status from v$thread
;
prompt *** gv$archived_log ***
select
dest_id, thread#, applied, max_seq, max_time,max_seq-lead (max_seq) over (partition by thread# order by thread#) delta_seq,
(max_time-lead (max_time) over (partition by thread# order by thread#))*24*60 deta_min
from
(
select dest_id, thread#, applied, max(sequence#) max_seq, max(next_time) max_time from gv$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
and (inst_id,dest_id) in (select inst_id,dest_id from gv$archive_dest where schedule='ACTIVE'
and target=(select decode(database_role,'PRIMARY','STANDBY','LOCAL') from v$database))
group by dest_id, thread#, applied
)
;
prompt *** v$archive_gap ***
select * from v$archive_gap ;
prompt *** GAP can also be verified using RMAN from STANDBY ***
select 'list archivelog from sequence '||sequence#||' thread '||thread#||';' RMAN1 from v$log_history where (thread#,first_time)
in (select thread#,max(first_time) from v$log_history group by thread#) ;
col name for a25
prompt *** v$dataguard_stats ***
select name,value,unit from v$dataguard_stats where name like '%lag%'
;
prompt *** gv$managed_standby ***
select PID,inst_id,thread#,process,client_process,status,sequence#,block#,DELAY_MINS from gv$managed_standby
where BLOCK#>1
and status not in ('CLOSING','IDLE')
order by thread#, sequence#
;