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 Oracle 19c<<<<<<<<<<<<<<<
PROMPT *********************************************
PROMPT * D A T A B A S E I N F O R M A T I O N
PROMPT *********************************************
PROMPT
PROMPT
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)
|| TRIM(SUBSTR('supplemental log data_sr : ' || d.SUPPLEMENTAL_LOG_DATA_SR ,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 23ai <<<<<<<<<<<<<<<
PROMPT *********************************************
PROMPT * D A T A B A S E I N F O R M A T I O N
PROMPT *********************************************
PROMPT
PROMPT
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)
|| TRIM(SUBSTR('supplemental log data_sr : ' || d.SUPPLEMENTAL_LOG_DATA_SR ,1,50)) || chr(10)
|| TRIM(SUBSTR('GOLDENGATE BLOCKING MODE : ' || d.GOLDENGATE_BLOCKING_MODE ,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)||
Hostname :'|| sys_context('USERENV', 'SERVER_HOST')||chr(10) ||
'DB Name : '||name ||chr(10) ||
'STANDBY BECAME PRIMARY_SCN : '||STANDBY_BECAME_PRIMARY_SCN||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) ||
'STANDBY BECAME PRIMARY_SCN : '||STANDBY_BECAME_PRIMARY_SCN||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#
;
===========
database size !!!
set linesize 300
col "Database Size" for a20
col "Free space" for a20
col "Used space" for a20
col hostname for a37
col Instance for a15
col DB_unique_name for a15
col Tdate heading 'Date'
alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
select /*+ parallel(8) */ sysdate Tdate,SYS_CONTEXT('USERENV','HOST') hostname,sys_context('USERENV', 'INSTANCE_NAME') Instance ,SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') DB_unique_name,
round(sum(used.bytes)/1024/1024/1024 ) || ' GB' "Database Size"
,round(sum(used.bytes)/1024/1024/1024 ) - round(free.SS/1024/1024/1024) || ' GB' "Used space"
,round(free.SS /1024/1024/1024) || ' GB' "Free space",LOG_MODE
from (select bytes from v$datafile
union all
select bytes from v$tempfile
union all
select bytes from v$log) used , (select sum(bytes) SS from dba_free_space) free,(select LOG_MODE from v$database)
group by free.SS,LOG_MODE
/
1 comment:
http://anuj-singh.blogspot.com/2015/12/oracle-instance-info.html
Post a Comment