Search This Blog

Total Pageviews

Wednesday, 29 November 2017

Oracle Database Info ... from v$database

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 ;


oracle 26ai 


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 ;


********************



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)||
       'Current SCN                     : '||CURRENT_SCN||CHR(10)||
       'Flashback On                    : '||FLASHBACK_ON||CHR(10)||
       'DB Unique Name                  : '||DB_UNIQUE_NAME||CHR(10)||
       'Archivelog Change#              : '||ARCHIVELOG_CHANGE#||CHR(10)||
       'Switchover Status               : '||SWITCHOVER_STATUS||CHR(10)||
       'Remote Archive                  : '||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




SET HEADING OFF
SET FEEDBACK OFF
SET LINESIZE 120

SELECT 
    '# --------------------------------------------------' || CHR(10) ||
    '# --  Database Information -------------------------' || CHR(10) ||
    '# --------------------------------------------------' || CHR(10) ||
    'Hostname                        : ' || SYS_CONTEXT('USERENV', 'SERVER_HOST') || CHR(10) ||
    'DB Name                         : ' || NAME || CHR(10) ||
    'DB Unique Name                  : ' || DB_UNIQUE_NAME || CHR(10) ||
    'Database Role                   : ' || DATABASE_ROLE || CHR(10) ||
    'Open Mode                       : ' || OPEN_MODE || CHR(10) ||
    'Log Mode                        : ' || LOG_MODE || CHR(10) ||
    'Protection Mode                 : ' || PROTECTION_MODE || CHR(10) ||
    'Protection Level                : ' || PROTECTION_LEVEL || CHR(10) ||
    'Switchover Status               : ' || SWITCHOVER_STATUS || CHR(10) ||
    'Data Guard Broker               : ' || DATAGUARD_BROKER || CHR(10) ||
    'Flashback On                    : ' || FLASHBACK_ON || CHR(10) ||
    'Force Logging                   : ' || FORCE_LOGGING || CHR(10) ||
    'Created                         : ' || TO_CHAR(CREATED, 'DD-MON-YYYY HH24:MI:SS') || CHR(10) ||
    'Current SCN                     : ' || CURRENT_SCN || CHR(10) ||
    'Standby Became Primary SCN      : ' || STANDBY_BECAME_PRIMARY_SCN || CHR(10) ||
    'Archivelog Change#              : ' || ARCHIVELOG_CHANGE# || CHR(10) ||
    'Supplemental Log PK             : ' || SUPPLEMENTAL_LOG_DATA_PK || CHR(10) ||
    'Supplemental Log UI             : ' || SUPPLEMENTAL_LOG_DATA_UI
FROM V$DATABASE;

SET HEADING ON
SET FEEDBACK ON



===



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
/


Posted by at 1 comment:

Tuesday, 28 November 2017

Rman active duplicate on same host 


Rman  active duplicate on same host ...





Rman  active duplicate on same host from vihcdb1 to vihcdb2 database 


vihcdb1:/u01/app/oracle/product/12.1.0/db_2:N ------>>>> from 
vihcdb2:/u01/app/oracle/product/12.1.0/db_2:N To <<<<<----- 


Primary database   ( vihcdb1 )
Duplicate database ( vihcdb2 )


Must have password file 

http://anuj-singh.blogspot.co.uk/2012/04/how-to-create-oracle-password-file.html


[oracle@cloud-ora dbs]$ pwd
/u01/app/oracle/product/12.1.0/db_2/dbs

[oracle@cloud-ora dbs]$ ls -ltr ora*
-rw-r----- 1 oracle oinstall 6144 Nov 22 06:17 orapwvihcdb2
-rw-r----- 1 oracle oinstall 6144 Nov 23 06:41 orapwvihcdb1


Imp !!
Primary database ( vihcdb1 ) must be ruuning from spfile !!!!!!

SQL> def
DEFINE _DATE           = "23-NOV-17" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "vihcdb1" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE      = "1202000100" (CHAR)


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.1.0
                                                 /db_2/dbs/spfilevihcdb1.ora




For DB_FILE_NAME_CONVERT parameter run following sql and set value accordingly for duplicate database

select  distinct substr(name, 1, instr(name, '/',-1)) PATH 
                       from  (
                       select name  from v$datafile
                       union all
                       select NAME  from v$controlfile
                       union all
                       select MEMBER  name from v$logfile
                       union all
                       select name from v$tempfile
) order by 1;                   

PATH
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/vihcdb1/ <<<<<<<<<---- 
/u01/app/oracle/oradata/vihcdb1/ANUJ/
/u01/app/oracle/oradata/vihcdb1/pdbseed/



Parameter file for primary database .                                                                                         For Duplicate database 
                                                                                                                              Create all the dir !!!!

cat initvihcdb1.ora                                                                                                          cat initvihcdb2.ora
vihcdb1.__data_transfer_cache_size=0                                                                                         vihcdb2.__data_transfer_cache_size=0
vihcdb1.__db_cache_size=289406976                                                                                            vihcdb2.__db_cache_size=289406976
vihcdb1.__inmemory_ext_roarea=0                                                                                              vihcdb2.__inmemory_ext_roarea=0
vihcdb1.__inmemory_ext_rwarea=0                                                                                              vihcdb2.__inmemory_ext_rwarea=0
vihcdb1.__java_pool_size=4194304                                                                                             vihcdb2.__java_pool_size=4194304
vihcdb1.__large_pool_size=8388608                                                                                            vihcdb2.__large_pool_size=8388608
vihcdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment                                                     vihcdb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
vihcdb1.__pga_aggregate_target=109051904                                                                                     vihcdb2.__pga_aggregate_target=109051904
vihcdb1.__sga_target=536870912                                                                                               vihcdb2.__sga_target=536870912
vihcdb1.__shared_io_pool_size=20971520                                                                                       vihcdb2.__shared_io_pool_size=20971520
vihcdb1.__shared_pool_size=197132288                                                                                         vihcdb2.__shared_pool_size=197132288
vihcdb1.__streams_pool_size=0                                                                                                vihcdb2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/vihcdb1/adump'                                                                      *.audit_file_dest='/u01/app/oracle/admin/vihcdb2/adump'
*.audit_trail='DB'                                                                                                           *.audit_trail='DB'
*.compatible='12.0.0'                                                                                                        *.compatible='12.0.0'
*.control_files='/u01/app/oracle/oradata/vihcdb1/ora_control1.ctl','/u01/app/oracle/oradata/vihcdb1/ora_control2.ctl'        *.control_files='/u01/app/oracle/oradata/vihcdb2/ora_control1.ctl','/u01/app/oracle/oradata/vihcdb2/ora_control2.ctl'
*.db_block_size=8192                                                                                                         *.db_block_size=8192
*.db_domain=''                                                                                                               *.db_domain=''
*.db_name='vihcdb1'                                                                                                          *.db_recovery_file_dest_size=4294967296
*.db_recovery_file_dest_size=4294967296                                                                                      *.db_recovery_file_dest='/u01/app/oracle/vihcdb2'
*.db_recovery_file_dest='/u01/app/oracle/vihcdb1'                                                                            *.db_securefile='PREFERRED'
*.db_securefile='PREFERRED'                                                                                                  *.db_name='vihcdb2'
*.db_unique_name='vihcdb1'                                                                                                   *.db_unique_name='vihcdb2'
*.diagnostic_dest='/u01/app/oracle'                                                                                          *.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=VIHCDBXDB)'                                                                           *.dispatchers='(PROTOCOL=TCP) (SERVICE=VIHCDBXDB)'
*.enable_pluggable_database=TRUE                                                                                             *.enable_pluggable_database=TRUE
vihcdb1.local_listener='(address=(protocol=tcp)(host=001.18.114.253)(port=1521))'                                            vihcdb2.local_listener='(address=(protocol=tcp)(host=001.18.114.253)(port=1521))'
*.log_archive_dest_1='location=/u01/app/Archive_Vihcdb1'                                                                     *.log_archive_dest_1='location=/u01/app/Archive_Vihcdb2'
*.open_cursors=1000                                                                                                          *.open_cursors=1000
*.processes=300                                                                                                              *.processes=300
*.remote_login_passwordfile='EXCLUSIVE'                                                                                      *.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912                                                                                                       *.sga_target=536870912
*.undo_tablespace='UNDOTBS1'                                                                                                 *.undo_tablespace='UNDOTBS1'


Create  script for rman


  
DUPLICATE TARGET DATABASE TO VIHCDB2 FROM ACTIVE DATABASE 
SPFILE PARAMETER_VALUE_CONVERT '/u01/app/oracle/oradata/vihcdb1', '/u01/app/oracle/oradata/vihcdb2' 
SET DB_FILE_NAME_CONVERT '/u01/app/oracle/oradata/vihcdb1', '/u01/app/oracle/oradata/vihcdb2' 
SET LOG_FILE_NAME_CONVERT '/u01/app/oracle/oradata/vihcdb1', '/u01/app/oracle/oradata/vihcdb2' 
SET DB_CREATE_FILE_DEST '/u01/app/oracle/oradata/vihcdb2' 
set db_create_online_log_dest_1 '/u01/app/oracle/oradata/vihcdb2' 
NOFILENAMECHECK; 



vihcdb1:/u01/app/oracle/product/12.1.0/db_2:N
vihcdb2:/u01/app/oracle/product/12.1.0/db_2:N
[oracle@cloud-ora dbs]$ . oraenv
ORACLE_SID = [vihcdb1ora] ? vihcdb2

Imp !!
This database must not running from spfile (Duplicate One ) 

/u01/app/oracle/product/12.1.0/db_2/dbs

[oracle@cloud-ora dbs]$ ls -ltr init*
-rw-r----- 1 oracle oinstall 1256 Nov 22 01:44 initvihcdb2.ora


[oracle@cloud-ora dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 23 06:30:41 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount ;
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  8794848 bytes
Variable Size             306187552 bytes
Database Buffers          213909504 bytes
Redo Buffers                7979008 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64


rman target sys/vihaan*23@001.18.114.253:1521/vihcdb1 auxiliary sys/vihaan*23 |tee  /tmp/rman_clone_vihcdb1.log


[oracle@cloud-ora dbs]$  rman target sys/vihaan*23@001.18.114.253:1521/vihcdb1 auxiliary sys/vihaan*23 |tee  /tmp/rman_clone_vihcdb1.log

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Nov 23 06:32:24 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: VIHCDB1 (DBID=2963737589)
connected to auxiliary database: VIHCDB2 (not mounted)

RMAN> DUPLICATE TARGET DATABASE TO VIHCDB2 FROM ACTIVE DATABASE
2> SPFILE PARAMETER_VALUE_CONVERT '/u01/app/oracle/oradata/vihcdb1', '/u01/app/oracle/oradata/vihcdb2'
3> SET DB_FILE_NAME_CONVERT '/u01/app/oracle/oradata/vihcdb1', '/u01/app/oracle/oradata/vihcdb2'
4> SET LOG_FILE_NAME_CONVERT '/u01/app/oracle/oradata/vihcdb1', '/u01/app/oracle/oradata/vihcdb2'
5> SET DB_CREATE_FILE_DEST '/u01/app/oracle/oradata/vihcdb2'
6> set db_create_online_log_dest_1 '/u01/app/oracle/oradata/vihcdb2'
7> NOFILENAMECHECK;

Starting Duplicate Db at 23-NOV-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=254 device type=DISK
current log archived

contents of Memory Script:
{
   restore clone from service  '001.18.114.253:1521/vihcdb1' spfile to
 '/u01/app/oracle/product/12.1.0/db_2/dbs/spfilevihcdb2.ora';
   sql clone "alter system set spfile= ''/u01/app/oracle/product/12.1.0/db_2/dbs/spfilevihcdb2.ora''";
}
executing Memory Script

Starting restore at 23-NOV-17
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: restoring SPFILE
output file name=/u01/app/oracle/product/12.1.0/db_2/dbs/spfilevihcdb2.ora
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 23-NOV-17

sql statement: alter system set spfile= ''/u01/app/oracle/product/12.1.0/db_2/dbs/spfilevihcdb2.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''VIHCDB2'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''VIHCDB2'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  control_files =
 ''/u01/app/oracle/oradata/vihcdb2/ora_control1.ctl'', ''/u01/app/oracle/oradata/vihcdb2/ora_control2.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''/u01/app/oracle/oradata/vihcdb1'', ''/u01/app/oracle/oradata/vihcdb2'' comment=
 '''' scope=spfile";
   sql clone "alter system set  LOG_FILE_NAME_CONVERT =
 ''/u01/app/oracle/oradata/vihcdb1'', ''/u01/app/oracle/oradata/vihcdb2'' comment=
 '''' scope=spfile";
   sql clone "alter system set  DB_CREATE_FILE_DEST =
 ''/u01/app/oracle/oradata/vihcdb2'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_create_online_log_dest_1 =
 ''/u01/app/oracle/oradata/vihcdb2'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''VIHCDB2'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''VIHCDB2'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  control_files =  ''/u01/app/oracle/oradata/vihcdb2/ora_control1.ctl'', ''/u01/app/oracle/oradata/vihcdb2/ora_control2.ctl'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/u01/app/oracle/oradata/vihcdb1'', ''/u01/app/oracle/oradata/vihcdb2'' comment= '''' scope=spfile

sql statement: alter system set  LOG_FILE_NAME_CONVERT =  ''/u01/app/oracle/oradata/vihcdb1'', ''/u01/app/oracle/oradata/vihcdb2'' comment= '''' scope=spfile

sql statement: alter system set  DB_CREATE_FILE_DEST =  ''/u01/app/oracle/oradata/vihcdb2'' comment= '''' scope=spfile

sql statement: alter system set  db_create_online_log_dest_1 =  ''/u01/app/oracle/oradata/vihcdb2'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     536870912 bytes

Fixed Size                     8794848 bytes
Variable Size                306187552 bytes
Database Buffers             213909504 bytes
Redo Buffers                   7979008 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''VIHCDB1'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''VIHCDB2'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone from service  '001.18.114.253:1521/vihcdb1' primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''VIHCDB1'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''VIHCDB2'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     536870912 bytes

Fixed Size                     8794848 bytes
Variable Size                306187552 bytes
Database Buffers             213909504 bytes
Redo Buffers                   7979008 bytes

Starting restore at 23-NOV-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=254 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/vihcdb2/ora_control1.ctl
output file name=/u01/app/oracle/oradata/vihcdb2/ora_control2.ctl
Finished restore at 23-NOV-17

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/vihcdb2/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/system01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/vihcdb2/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/sysaux01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/vihcdb2/undotbs01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/vihcdb2/users01.dbf";
   set newname for datafile  7 to
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/users01.dbf";
   set newname for datafile  8 to
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/usertbs01.dbf";
   set newname for datafile  9 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/system01.dbf";
   set newname for datafile  10 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/sysaux01.dbf";
   set newname for datafile  11 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/users01.dbf";
   set newname for datafile  12 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/usertbs01.dbf";
   set newname for datafile  14 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/scott01.dbf";
   set newname for datafile  15 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/master01.dbf";
   restore
   from service  '001.18.114.253:1521/vihcdb1'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 23-NOV-17
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/vihcdb2/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/vihcdb2/pdbseed/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/vihcdb2/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/vihcdb2/pdbseed/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/vihcdb2/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:10
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/vihcdb2/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/vihcdb2/pdbseed/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/vihcdb2/pdbseed/usertbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/vihcdb2/ANUJ/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/vihcdb2/ANUJ/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/vihcdb2/ANUJ/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/vihcdb2/ANUJ/usertbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/vihcdb2/ANUJ/scott01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/vihcdb2/ANUJ/master01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:19
Finished restore at 23-NOV-17

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  '001.18.114.253:1521/vihcdb1'
           archivelog from scn  3240340;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 23-NOV-17
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=220
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=221
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 23-NOV-17

datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=16 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=18 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/sysaux01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=19 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=20 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/users01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=21 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=22 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/usertbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=23 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=24 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=25 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/users01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=26 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/usertbs01.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=27 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/scott01.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=28 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/master01.dbf

contents of Memory Script:
{
   set until scn  3240505;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 23-NOV-17
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 220 is already on disk as file /u01/app/Archive_Vihcdb1/1_220_955461173.dbf
archived log for thread 1 with sequence 221 is already on disk as file /u01/app/Archive_Vihcdb1/1_221_955461173.dbf
archived log file name=/u01/app/Archive_Vihcdb1/1_220_955461173.dbf thread=1 sequence=220
archived log file name=/u01/app/Archive_Vihcdb1/1_221_955461173.dbf thread=1 sequence=221
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-NOV-17
Oracle instance started

Total System Global Area     536870912 bytes

Fixed Size                     8794848 bytes
Variable Size                306187552 bytes
Database Buffers             213909504 bytes
Redo Buffers                   7979008 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''VIHCDB2'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
}
executing Memory Script

sql statement: alter system set  db_name =  ''VIHCDB2'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance started

Total System Global Area     536870912 bytes

Fixed Size                     8794848 bytes
Variable Size                306187552 bytes
Database Buffers             213909504 bytes
Redo Buffers                   7979008 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "VIHCDB2" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES     1
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/u01/app/oracle/oradata/vihcdb2/redo01a.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '/u01/app/oracle/oradata/vihcdb2/redo02a.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '/u01/app/oracle/oradata/vihcdb2/redo03a.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/vihcdb2/system01.dbf',
  '/u01/app/oracle/oradata/vihcdb2/pdbseed/system01.dbf',
  '/u01/app/oracle/oradata/vihcdb2/ANUJ/system01.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/vihcdb2/temp01.dbf";
   set newname for tempfile  2 to
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/temp01.dbf";
   set newname for tempfile  3 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/vihcdb2/sysaux01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/sysaux01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/undotbs01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/users01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/users01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/usertbs01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/sysaux01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/users01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/usertbs01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/scott01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/master01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/vihcdb2/temp01.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/vihcdb2/pdbseed/temp01.dbf in control file
renamed tempfile 3 to /u01/app/oracle/oradata/vihcdb2/ANUJ/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/sysaux01.dbf RECID=1 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/sysaux01.dbf RECID=2 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/undotbs01.dbf RECID=3 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/users01.dbf RECID=4 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/users01.dbf RECID=5 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/usertbs01.dbf RECID=6 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/sysaux01.dbf RECID=7 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/users01.dbf RECID=8 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/usertbs01.dbf RECID=9 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/scott01.dbf RECID=10 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/master01.dbf RECID=11 STAMP=960791822

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/sysaux01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/users01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=5 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=6 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/usertbs01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=7 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=8 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/users01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=9 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/usertbs01.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=10 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/scott01.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=11 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/master01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened

contents of Memory Script:
{
   sql clone "alter pluggable database all open";
}
executing Memory Script

sql statement: alter pluggable database all open
Finished Duplicate Db at 23-NOV-17

RMAN>


***************************************************************************************************************

Check for temp file 

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 23 07:15:03 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> def
DEFINE _DATE           = "23-NOV-17" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "vihcdb2" (CHAR)  <<<<<<<<<----- 
DEFINE _USER           = "SYS" (CHAR)


Now database is running spfile . 

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.1.0
                                                 /db_2/dbs/spfilevihcdb2.ora


             
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/Archive_Vihcdb1  <<<<<<If destination is old data then change it
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1



SQL> alter system set log_archive_dest_1='location=/u01/app/Archive_Vihcdb2' scope=both;

System altered.


SQL>  archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/Archive_Vihcdb2
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1



Create temp file if required 

SQL> 
set numf 999.99 linesize 200 pagesize 200
col FILE_NAME format a50
SELECT tablespace_name, file_name, bytes/1024/1024/1024 Gb FROM dba_temp_files WHERE tablespace_name like '%TEMP%' ;

                                                                *
ERROR at line 1:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 201: '/u01/app/oracle/oradata/vihcdb2/temp01.dbf'

alter database tempfile '/u01/app/oracle/oradata/vihcdb2/temp01.dbf' drop;

SQL> alter database tempfile '/u01/app/oracle/oradata/vihcdb2/temp01.dbf' drop;

Database altered.

SQL>

 select TABLESPACE_NAME from dba_tablespaces where CONTENTS='TEMPORARY' ;

TABLESPACE_NAME
------------------------------
TEMPTS


set long 5000 linesize 300 pagesize 200
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb  where CONTENTS='TEMPORARY' ;

DBMS_METADATA.GET_DDL('TABLESPACE',TB.TABLESPACE_NAME)
--------------------------------------------------------------------------------

  CREATE TEMPORARY TABLESPACE "TEMPTS" TEMPFILE
  '/u01/app/oracle/oradata/vihcdb2/temp01.dbf' SIZE 1073741824
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576



ALTER TABLESPACE TEMPTS add tempfile '/u01/app/oracle/oradata/vihcdb2/temp01.dbf' size 1g reuse ;

SQL> ALTER TABLESPACE TEMPTS add tempfile '/u01/app/oracle/oradata/vihcdb2/temp01.dbf' size 1g reuse ;

Tablespace altered.



SQL> DROP TABLESPACE TEMPTS INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TEMPTS INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

         


set linesize 300 pagesize 500  verify off
col tablespace      for a29             heading 'tablespace name / file class'
col filename        for a64             heading 'filename'
col filesize        for 99,999,999,999  heading 'file size'
col autoextensible  for a4              heading 'auto'
col increment_by    for 99,999,999,999  heading 'next'
col maxbytes        for 99,999,999,999  heading 'max'
break on report
compute sum of filesize on report
SELECT /*+ ordered */
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM sys.dba_data_files d , v$datafile v , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e
WHERE (d.file_name = v.name)
UNION
SELECT
d.tablespace_name tablespace 
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM sys.dba_temp_files d , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e
UNION
SELECT '[ ONLINE REDO LOG ]' , a.member , b.bytes , null , TO_NUMBER(null), TO_NUMBER(null) FROM v$logfile a , v$log b
WHERE a.group# = b.group#
UNION
SELECT '[ CONTROL FILE ]' , a.name , TO_NUMBER(null) , null , TO_NUMBER(null), TO_NUMBER(null)
FROM v$controlfile a ORDER BY 1,2
/



Tablespace Name / File Class  Filename                                                               File Size Auto            Next             Max
----------------------------- ---------------------------------------------------------------- --------------- ---- --------------- ---------------
SYSAUX                        /u01/app/oracle/oradata/vihcdb2/sysaux01.dbf                         608,174,080 YES       10,485,760  34,359,721,984
SYSTEM                        /u01/app/oracle/oradata/vihcdb2/system01.dbf                         734,003,200 YES       10,485,760  34,359,721,984
TEMPTS                        /u01/app/oracle/oradata/vihcdb2/temp01.dbf                         1,073,741,824 NO                 0               0
UNDOTBS1                      /u01/app/oracle/oradata/vihcdb2/undotbs01.dbf                        466,616,320 YES        5,242,880  34,359,721,984
USERS                         /u01/app/oracle/oradata/vihcdb2/users01.dbf                          524,288,000 YES            8,192  34,359,721,984
[ CONTROL FILE ]              /u01/app/oracle/oradata/vihcdb2/ora_control1.ctl
[ CONTROL FILE ]              /u01/app/oracle/oradata/vihcdb2/ora_control2.ctl
[ ONLINE REDO LOG ]           /u01/app/oracle/oradata/vihcdb2/redo01a.log                           52,428,800
[ ONLINE REDO LOG ]           /u01/app/oracle/oradata/vihcdb2/redo02a.log                           52,428,800
[ ONLINE REDO LOG ]           /u01/app/oracle/oradata/vihcdb2/redo03a.log                           52,428,800
                                                                                               ---------------
sum                                                                                              3,564,109,824

10 rows selected.
Posted by at 1 comment:

sqlplus command prompt (sqlprompt)

sqlplus command prompt (sqlprompt) ... 


Useful for Standby 

SET TERMOUT OFF 
DEFINE sqlprompt=none
COLUMN sqlprompt NEW_VALUE sqlprompt
SELECT '[-'||sys_context('USERENV', 'DATABASE_ROLE')||'-]'||LOWER(SYS_CONTEXT('USERENV','CURRENT_USER')) || '@' || SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') as sqlprompt FROM DUAL;
SET SQLPROMPT '&sqlprompt> '
UNDEFINE sqlprompt
SET TERMOUT ON

output .. 
[-PRIMARY-]sys@vihcdb1>


 set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
===
Sqlprompt with colour !!!!!!!!!
with colour

SET TERMOUT OFF 
DEFINE sqlprompt=none
COLUMN sqlprompt NEW_VALUE sqlprompt
--select   chr(27)||'[32m'||chr(27)||'[1'||to_char(0)||'m'|| --GREEN  COLOR
select   chr(27)||'[33m'||chr(27)||'[1'||to_char(0)||'m'|| --YELLOW COLOR
--SELECT  chr(27)||'[35m'||chr(27)||'[1'||to_char(0)||'m'|| --PINK   COLOR
 '[-'||sys_context('USERENV', 'DATABASE_ROLE')||'-]'||LOWER(SYS_CONTEXT('USERENV','CURRENT_USER')) || '@' || SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') as sqlprompt FROM DUAL;
SET SQLPROMPT '&sqlprompt> '
UNDEFINE sqlprompt
SET TERMOUT ON


=====



for putty title 

 PROMPT_COMMAND='printf "\033]0;%s@%s - %s\007" "`whoami`" "`hostname`" "`uname -s -r`"'

PROMPT_COMMAND='printf "\033]0;%s@%s - %s\007" "`whoami`" "`hostname`" "`uptime`"'



-- bold yellow color prompt 
export PS1="\e[0;33;1m[\u@\h \W]\$ \e[m"

export PS1="\e[0;33;1m[\u\e[m@\e[0;31;1m\h\e[m \W]\$"


export PS1="\e[0;33;97m[\u\e[m@\e[0;31;1m\h\e[m \W]\$"


                     Red                 Green
$export PS1="\e[0;31;31m[\u\e[m@\e[0;31;92m\h\e[m \W]\$"
  
  Red  @ Green 
[oracle@irac01 ~]$




30 Black | 31 Red | 32 Green | 33 Yellow | 34 Blue| 35 Magenta| 36 Cyan| 37 Light gray |90 Dark gray |91 Light red |92 Light green |93 Light yellow |94 Light blue |95 Light magenta |96 Light cyan |97 White


Changing Color of Linux Terminal Directory and Dir
PS1="\\[\\e]0;\\u@\\h:\\w\\a\\]\\[\\e[1;32m\\]\\u@\\h\\[\\e[1;34m\\] [\\w] \\\$\\[\\e[0m\\] "


export PS1="\[\e[32m\][\[\e[m\]\[\e[31m\]\u\[\e[m\]\[\e[33m\]@\[\e[m\]\[\e[32m\]\h\[\e[m\]:\[\e[36m\]\w\[\e[m\]\[\e[32m\]]\[\e[m\]\[\e[32;47m\]\\$\[\e[m\] "














Posted by



at





1 comment:
  

































          

        

          

        

Sunday, 12 November 2017


          

        









ASM disk operations














ASM disk operations   ..








+ASM disk operations










set linesize 200
select * from v$asm_operation;




select operation, state, power, est_rate, est_minutes, round((sofar/est_work)*100,2) pct_comp from v$asm_operation;


col oper     for a5

col name     for a20
col power    for 9999
col actual   for 9999
col est_min  for 9999
col power    for 9999
col est_rate for 99999


col sofar    for 9999999


select dg.name ,do.operation oper ,do.state ,do.power,do.actual ,do.sofar ,do.est_rate ,do.est_minutes est_min from v$asm_operation do , v$asm_diskgroup dg 
where 1=1 
and dg.group_number = do.group_number
order by 2,1 ;


select group_number, name, total_mb, free_mb from v$asm_disk_stat;



ps -efa | grep asm_arb












Posted by



at





1 comment:
  

































        

      









Subscribe to:
Posts (Atom)







Oracle DBA



My photo








Essex, United Kingdom











anuj blog Archive