Search This Blog

Total Pageviews

Saturday, 19 December 2015

Oracle Standby out of Sync ..


Oracle Standby out of Sync ...

. Standby roll forward using RMAN incremental backup

How to Resolve Primary/Standby Log GAP In Case of Deleting Archivelogs From Primary?


On standby 

Identify the lowest  SCN on the Standby.. 

select MIN(scn) Min_Scn from (SELECT to_char(CURRENT_SCN) scn FROM V$DATABASE
                              union 
                              select to_char(min(fhscn)) from x$kcvfh
                              union 
                              select to_char(min(f.fhscn)) from x$kcvfh f, v$datafile d  
                              where f.hxfil =d.file#
                              and d.enabled != 'READ ONLY'
                              );


MIN_SCN
----------------------------------------
12066937215

Use this scn no to take backup on primary .

RMAN> BACKUP INCREMENTAL FROM SCN 12066937215 DATABASE FORMAT '/u01/rman_backup/stdby20140828/ForStandby_%U' tag 'FORSTANDBY';

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/u01/rman_backup/stdby20140828/ForStandbyCTRL.bck';


cd /u01/rman_backup/

create scp script for copy file 
ls -1 *For* |awk '{print " scp -q "  $1 " " "akl0std:/u02/DISK_BACKUP/ &" }'

after copy the file 

On Standby ...

cancel the recovery ..


alter database recover managed standby database disconnectfrom session;
Database altered.

check for MRP0 process .. 
IMP  This process should not be running .

break on INST_ID
set pages 1000 lines 150
COL GROUP#    for a15
col status    for a20
select INST_ID,PROCESS,STATUS,SEQUENCE#,THREAD#,GROUP#,PID from gV$MANAGED_STANDBY 
order by INST_ID,STATUS;

on OS 
ps -ef|grep -i mrp 



RMAN> SHUTDOWN IMMEDIATE ;

RMAN> STARTUP NOMOUNT; 

RMAN> RESTORE STANDBY CONTROLFILE FROM '/u02/DISK_BACKUP/ForStandbyCTRL.bck'; 


Starting restore at 25-NOV-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=345 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/prod/control01.ctl
output file name=+DATA/prod/control02.ctl
Finished restore at 25-NOV-15

RMAN> shutdown;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

RMAN> CATALOG START WITH '/u02/DISK_BACKUP'; 

RMAN> RECOVER DATABASE NOREDO;

If there are datafiles created on the primary database after the last SCN on standby

SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 12066937215;

Perform the switch so that the controlfile points to the datafiles under the standby directory.

RMAN> SWITCH DATABASE TO COPY;


To start recovery On standby !!!!

sqlplus 

alter database recover managed standby database using current logfile disconnect;


On Standby:
MRP identifier

break on INST_ID
set pages 1000 lines 150
COL GROUP#    for a15
col status    for a20
select INST_ID,PROCESS,STATUS,SEQUENCE#,THREAD#,GROUP#,PID from GV$MANAGED_STANDBY 
order by INST_ID,STATUS;



Metalink Note ..

standby database using RMAN Incremental Backup. (Doc ID 836986.1)
standby database using RMAN incremental backup when datafile is added to primary (Doc ID 1531031.1)  <<<<<<<

 Imp part 

8) Rename the datafiles in new standby controlfile  !!!! if location different on Standby 

Since the controlfile is restored from PRIMARY the datafile locations in STANDBY controlfile will be same as PRIMARY database, so catalog datafiles in STANDBY will do the necessary rename operations.
Perform the below step  in STANDBY for each diskgroup where the datafile directory structure between primary and standby are different.

on standby 
RMAN> CATALOG START WITH '+DATA/<db_unqiue_name>/datafile/';

RMAN> SWITCH DATABASE TO COPY;
RMAN> RECOVER DATABASE NOREDO;



=======





Copy database file directly across network using RMAN in 11g including Primary and Standby (Doc ID 1909235.1)

rman target sys/<password>@<prmy_tns> auxiliary sys/<password>@<stby_tns>



run {
 allocate channel d1 type disk ;
 allocate channel d2 type disk ;
 backup as copy reuse
  datafile 4 auxiliary format '/u01/app/oracle/oradata/stby/users01.dbf'
   datafile 5 auxiliary format '/u01/app/oracle/oradata/stby/example01.dbf'
  ;
}
Posted by at 8 comments:

Thursday, 17 December 2015

Oracle Database Info ... from v$database

Oracle Database Info ... from v$database 


  PROMPT ---- Oracle 9i
  PROMPT *********************************************
  PROMPT *  D A T A B A S E    I N F O R M A T I O N
  PROMPT *********************************************
  PROMPT 
  PROMPT 
  COLUMN column1  FORMAT a70
  COLUMN column2  FORMAT a60
  set pages 0 linesize 200
  SELECT /* First Column */
        '[DB Info]' || chr(10)
      || TRIM(SUBSTR('DB ID                  : ' || d.dbid                                           ,1,50)) || chr(10)
      || TRIM(SUBSTR('DB Name                : ' || d.name                                           ,1,50)) || chr(10)
      || TRIM(SUBSTR('DB Created             : ' || TO_CHAR(d.created,'DD-MON-YYYY HH24:MI:SS')       ,1,50)) || chr(10)
      || TRIM(SUBSTR('Open Mode              : ' || d.open_mode                                       ,1,50)) || chr(10)
      || TRIM(SUBSTR('Open Resetlogs         : ' || d.open_resetlogs                                 ,1,50)) || chr(10)
      || TRIM(SUBSTR('ArchiveLog Mode        : ' || d.log_mode                                       ,1,50)) || chr(10)
      || TRIM(SUBSTR('Force Logging          : ' || d.force_logging                                 ,1,50)) || chr(10)
      || TRIM(SUBSTR('Remote Archive         : ' || d.remote_archive                                 ,1,50)) || chr(10)
      || chr(10)   
      || '[Timestamps]' || chr(10)
      || TRIM(SUBSTR('DB Created             : ' || TO_CHAR(d.created,'DD-MON-YYYY HH24:MI:SS')       ,1,50)) || chr(10)
      || TRIM(SUBSTR('Controlfile Created    : ' || TO_CHAR(d.controlfile_created,'DD-MON-YYYY HH24:MI:SS')     ,1,50)) || chr(10)
      || TRIM(SUBSTR('Controlfile Time       : ' || TO_CHAR(d.controlfile_time,'DD-MON-YYYY HH24:MI:SS')       ,1,50)) || chr(10)
      || TRIM(SUBSTR('Version Time           : ' || TO_CHAR(d.version_time,'DD-MON-YYYY HH24:MI:SS')           ,1,50)) || chr(10)
      || TRIM(SUBSTR('Resetlogs Time         : ' || TO_CHAR(d.resetlogs_time,'DD-MON-YYYY HH24:MI:SS')         ,1,50)) || chr(10)
      || TRIM(SUBSTR('Prior Resetlogs Time   : ' || TO_CHAR(d.prior_resetlogs_time,'DD-MON-YYYY HH24:MI:SS')   ,1,50)) || chr(10)
      || chr(10)   
      || '[System Change Number]' || chr(10)
      || TRIM(SUBSTR('Resetlogs              SCN : ' || d.resetlogs_change#           ,1,50)) || chr(10)
      || TRIM(SUBSTR('Prior Resetlogs        SCN : ' || d.prior_resetlogs_change#     ,1,50)) || chr(10)
      || TRIM(SUBSTR('Checkpoint             SCN : ' || d.checkpoint_change#           ,1,50)) || chr(10)
      || TRIM(SUBSTR('Controlfile            SCN : ' || d.controlfile_change#         ,1,50)) || chr(10)
      || TRIM(SUBSTR('Archivelog Highest NextSCN : ' || d.archivelog_change#           ,1,50)) || chr(10)
      || TRIM(SUBSTR('Force Archivelog       SCN : ' || d.archive_change#             ,1,50)) || chr(10)
      || TRIM(SUBSTR('Archivelog             SCN : ' || d.archive_change#             ,1,50)) || chr(10)
      || chr(10)   
      || '[Controlfile Info]' || chr(10)
      || TRIM(SUBSTR('Controlfile Type       : ' || d.controlfile_type           ,1,50)) || chr(10)
      || TRIM(SUBSTR('Controlfile Created    : ' || TO_CHAR(d.controlfile_created,'DD-MON-YYYY HH24:MI:SS')     ,1,50)) || chr(10)
      || TRIM(SUBSTR('Controlfile SCN        : ' || d.controlfile_change#         ,1,50)) || chr(10)
      || TRIM(SUBSTR('Controlfile Sequence#  : ' || d.controlfile_sequence#       ,1,50)) || chr(10)
      || TRIM(SUBSTR('Controlfile Time       : ' || TO_CHAR(d.controlfile_time,'DD-MON-YYYY HH24:MI:SS')       ,1,50)) || chr(10)
         column1
    FROM v$database d   ;
PROMPT ---- Oracle 10g
PROMPT *********************************************
PROMPT *  D A T A B A S E    I N F O R M A T I O N
PROMPT *********************************************
PROMPT 
PROMPT 
COLUMN column1  FORMAT a70
COLUMN column2  FORMAT a60
set pages 0 linesize 200
SELECT /* First Column */
      '[DB Info]' || chr(10)
    || TRIM(SUBSTR('DB ID                   : ' || d.dbid                                           ,1,50)) || chr(10)
    || TRIM(SUBSTR('DB Name                 : ' || d.name                                           ,1,50)) || chr(10)
    || TRIM(SUBSTR('DB Unique Name         : ' || d.db_unique_name                                 ,1,50)) || chr(10)
    || TRIM(SUBSTR('Platform               : ' || d.platform_name                                   ,1,50)) || chr(10)
    || TRIM(SUBSTR('DB Created             : ' || TO_CHAR(d.created,'DD-MON-YYYY HH24:MI:SS')       ,1,50)) || chr(10)
    || TRIM(SUBSTR('Open Mode               : ' || d.open_mode                                       ,1,50)) || chr(10)
    || TRIM(SUBSTR('Open Resetlogs         : ' || d.open_resetlogs                                 ,1,50)) || chr(10)
    || TRIM(SUBSTR('Flashback ON           : ' || d.flashback_on                                   ,1,50)) || chr(10)
    || TRIM(SUBSTR('ArchiveLog Mode         : ' || d.log_mode                                       ,1,50)) || chr(10)
    || TRIM(SUBSTR('ArchiveLog Compression : ' || d.archivelog_compression                         ,1,50)) || chr(10)
    || TRIM(SUBSTR('Force Logging           : ' || d.force_logging                                   ,1,50)) || chr(10)
    || TRIM(SUBSTR('Remote Archive         : ' || d.remote_archive                                 ,1,50)) || chr(10)
    || TRIM(SUBSTR('Last Open Incarnation# : ' || d.last_open_incarnation#                         ,1,50)) || chr(10)
    || TRIM(SUBSTR('Recovery Target Inc#   : ' || d.recovery_target_incarnation#                   ,1,50)) || chr(10)
    || chr(10)   
    || '[Timestamps]' || chr(10)
    || TRIM(SUBSTR('DB Created             : ' || TO_CHAR(d.created,'DD-MON-YYYY HH24:MI:SS')       ,1,50)) || chr(10)
    || TRIM(SUBSTR('Controlfile Created     : ' || TO_CHAR(d.controlfile_created,'DD-MON-YYYY HH24:MI:SS')  ,1,50)) || chr(10)
    || TRIM(SUBSTR('Controlfile Time       : ' || TO_CHAR(d.controlfile_time,'DD-MON-YYYY HH24:MI:SS')     ,1,50)) || chr(10)
    || TRIM(SUBSTR('Version Time           : ' || TO_CHAR(d.version_time,'DD-MON-YYYY HH24:MI:SS')         ,1,50)) || chr(10)
    || TRIM(SUBSTR('Resetlogs Time         : ' || TO_CHAR(d.resetlogs_time,'DD-MON-YYYY HH24:MI:SS')       ,1,50)) || chr(10)
    || TRIM(SUBSTR('Prior Resetlogs Time   : ' || TO_CHAR(d.prior_resetlogs_time,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)
    || chr(10)   
    || '[System Change Number]' || chr(10)
    || TRIM(SUBSTR('Current                SCN : ' || d.current_scn                 ,1,50)) || chr(10)
    || TRIM(SUBSTR('Resetlogs              SCN : ' || d.resetlogs_change#           ,1,50)) || chr(10)
    || TRIM(SUBSTR('Prior Resetlogs        SCN : ' || d.prior_resetlogs_change#     ,1,50)) || chr(10)
    || TRIM(SUBSTR('Checkpoint             SCN : ' || d.checkpoint_change#           ,1,50)) || chr(10)
    || TRIM(SUBSTR('Controlfile            SCN : ' || d.controlfile_change#         ,1,50)) || chr(10)
    || TRIM(SUBSTR('Archivelog Highest NextSCN : ' || d.archivelog_change#           ,1,50)) || chr(10)
    || TRIM(SUBSTR('Force Archivelog       SCN : ' || d.archive_change#             ,1,50)) || chr(10)
    || TRIM(SUBSTR('Archivelog             SCN : ' || d.archive_change#             ,1,50)) || chr(10)
    || TRIM(SUBSTR('Standby Became Primary SCN : ' || d.standby_became_primary_scn   ,1,50)) || chr(10)
    || chr(10)   
    || '[Controlfile Info]' || chr(10)
    || TRIM(SUBSTR('Controlfile Type       : ' || d.controlfile_type           ,1,50)) || chr(10)
    || TRIM(SUBSTR('Controlfile Created     : ' || TO_CHAR(d.controlfile_created,'DD-MON-YYYY HH24:MI:SS')  ,1,50)) || chr(10)
-- || TRIM(SUBSTR('Controlfile Converted   : ' || d.controlfile_converted       ,1,50)) || chr(10)
    || TRIM(SUBSTR('Controlfile SCN         : ' || d.controlfile_change#         ,1,50)) || chr(10)
    || TRIM(SUBSTR('Controlfile Sequence#   : ' || d.controlfile_sequence#       ,1,50)) || chr(10)
    || TRIM(SUBSTR('Controlfile Time       : ' || TO_CHAR(d.controlfile_time,'DD-MON-YYYY HH24:MI:SS')     ,1,50)) || chr(10)
       column1
  ,    
      '[DataGuard Information]' || chr(10)
  --|| TRIM(SUBSTR('Primary DB Unique Name      : ' || d.primary_db_unique_name     ,1,50)) || chr(10)
    || TRIM(SUBSTR('DataGuard Role              : ' || d.database_role               ,1,50)) || chr(10)
    || TRIM(SUBSTR('Protection Mode             : ' || d.protection_mode             ,1,50)) || chr(10)
    || TRIM(SUBSTR('Protection Level            : ' || d.protection_level           ,1,50)) || chr(10)
-- || TRIM(SUBSTR('DataGuard Broker             : ' || d.dataguard_broker           ,1,50)) || chr(10)
    || TRIM(SUBSTR('DataGuard Status            : ' || d.guard_status               ,1,50)) || chr(10)
    || TRIM(SUBSTR('SwitchOver Status           : ' || d.switchover_status           ,1,50)) || chr(10)
    || TRIM(SUBSTR('Activation SCN              : ' || d.activation#                 ,1,50)) || chr(10)
    || TRIM(SUBSTR('SwitchOver SCN              : ' || d.switchover#                 ,1,50)) || chr(10)
    || TRIM(SUBSTR('Standby Became Primary SCN  : ' || d.standby_became_primary_scn ,1,50)) || chr(10)
    || TRIM(SUBSTR('Supplemental Log Data MIN   : ' || d.supplemental_log_data_min   ,1,50)) || chr(10)
    || TRIM(SUBSTR('Supplemental Log Data PK    : ' || d.supplemental_log_data_pk   ,1,50)) || chr(10)
    || TRIM(SUBSTR('Supplemental Log Data UI    : ' || d.supplemental_log_data_ui   ,1,50)) || chr(10)
-- || TRIM(SUBSTR('Supplemental Log Data PL     : ' || d.supplemental_log_data_pl   ,1,50)) || chr(10)
    || chr(10)   
    || '[Fast Start Failover Info]' || chr(10)
    || TRIM(SUBSTR('FS Failover Status          : ' || d.fs_failover_status           ,1,50)) || chr(10)
    || TRIM(SUBSTR('FS Failover Current Target  : ' || d.fs_failover_current_target   ,1,50)) || chr(10)
    || TRIM(SUBSTR('FS Failover Threshold       : ' || d.fs_failover_threshold         ,1,50)) || chr(10)
    || TRIM(SUBSTR('FS Failover Observer Present: ' || d.fs_failover_observer_present ,1,50)) || chr(10)
  --|| TRIM(SUBSTR('FS Failover Observer Host   : ' || d.fs_failover_observer_host     ,1,50)) || chr(10)
       column2
FROM v$database d ;

PROMPT ----oracle 11gr2 and Oracle 12c<<<<<<<<<<<<<<<
PROMPT *********************************************
PROMPT *  D A T A B A S E    I N F O R M A T I O N
PROMPT *********************************************
PROMPT 
PROMPT 
COLUMN column1  FORMAT a70
COLUMN column2  FORMAT a60
set pages 0 linesize 200
SELECT /* First Column */
      '[DB Info]' || chr(10)
    || TRIM(SUBSTR('DB ID                   : ' || d.dbid                                           ,1,50)) || chr(10)
    || TRIM(SUBSTR('DB Name                 : ' || d.name                                           ,1,50)) || chr(10)
    || TRIM(SUBSTR('DB Unique Name         : ' || d.db_unique_name                                 ,1,50)) || chr(10)
    || TRIM(SUBSTR('Platform               : ' || d.platform_name                                 ,1,50)) || chr(10)
    || TRIM(SUBSTR('DB Created             : ' || TO_CHAR(d.created,'DD-MON-YYYY HH24:MI:SS')     ,1,50)) || chr(10)
    || TRIM(SUBSTR('Open Mode               : ' || d.open_mode                                     ,1,50)) || chr(10)
    || TRIM(SUBSTR('Open Resetlogs         : ' || d.open_resetlogs                                 ,1,50)) || chr(10)
    || TRIM(SUBSTR('Flashback ON           : ' || d.flashback_on                                   ,1,50)) || chr(10)
    || TRIM(SUBSTR('ArchiveLog Mode         : ' || d.log_mode                                       ,1,50)) || chr(10)
    || TRIM(SUBSTR('ArchiveLog Compression : ' || d.archivelog_compression                         ,1,50)) || chr(10)
    || TRIM(SUBSTR('Force Logging           : ' || d.force_logging                                 ,1,50)) || chr(10)
    || TRIM(SUBSTR('Remote Archive           : ' || d.remote_archive                                 ,1,50)) || chr(10)
    || TRIM(SUBSTR('Last Open Incarnation#   : ' || d.last_open_incarnation#                         ,1,50)) || chr(10)
    || TRIM(SUBSTR('Recovery Target Inc#       : ' || d.recovery_target_incarnation#                   ,1,50)) || chr(10)
    || chr(10)   
    || '[Timestamps]' || chr(10)
    || TRIM(SUBSTR('DB Created               : ' || TO_CHAR(d.created,'DD-MON-YYYY HH24:MI:SS')     ,1,50)) || chr(10)
    || TRIM(SUBSTR('Controlfile Created       : ' || TO_CHAR(d.controlfile_created,'DD-MON-YYYY HH24:MI:SS')     ,1,50)) || chr(10)
    || TRIM(SUBSTR('Controlfile Time           : ' || TO_CHAR(d.controlfile_time,'DD-MON-YYYY HH24:MI:SS')       ,1,50)) || chr(10)
    || TRIM(SUBSTR('Version Time               : ' || TO_CHAR(d.version_time,'DD-MON-YYYY HH24:MI:SS')           ,1,50)) || chr(10)
    || TRIM(SUBSTR('Resetlogs Time             : ' || TO_CHAR(d.resetlogs_time,'DD-MON-YYYY HH24:MI:SS')         ,1,50)) || chr(10)
    || TRIM(SUBSTR('Prior Resetlogs Time       : ' || TO_CHAR(d.prior_resetlogs_time,'DD-MON-YYYY HH24:MI:SS')   ,1,50)) || chr(10)
    || chr(10)   
    || '[System Change Number]'|| chr(10)
    || TRIM(SUBSTR('Current                SCN : ' || d.current_scn                 ,1,50)) || chr(10)
    || TRIM(SUBSTR('Resetlogs              SCN : ' || d.resetlogs_change#           ,1,50)) || chr(10)
    || TRIM(SUBSTR('Prior Resetlogs        SCN : ' || d.prior_resetlogs_change#     ,1,50)) || chr(10)
    || TRIM(SUBSTR('Checkpoint             SCN : ' || d.checkpoint_change#           ,1,50)) || chr(10)
    || TRIM(SUBSTR('Controlfile            SCN : ' || d.controlfile_change#         ,1,50)) || chr(10)
    || TRIM(SUBSTR('Archivelog Highest NextSCN : ' || d.archivelog_change#           ,1,50)) || chr(10)
    || TRIM(SUBSTR('Force Archivelog       SCN : ' || d.archive_change#             ,1,50)) || chr(10)
    || TRIM(SUBSTR('Archivelog             SCN : ' || d.archive_change#             ,1,50)) || chr(10)
    || TRIM(SUBSTR('Standby Became Primary SCN : ' || d.standby_became_primary_scn   ,1,50)) || chr(10)
    || chr(10)   
    || '[Controlfile Info]' || chr(10)
    || TRIM(SUBSTR('Controlfile Type       : ' || d.controlfile_type           ,1,50)) || chr(10)
    || TRIM(SUBSTR('Controlfile Created     : ' || TO_CHAR(d.controlfile_created,'DD-MON-YYYY HH24:MI:SS')   ,1,50)) || chr(10)
    || TRIM(SUBSTR('Controlfile Converted   : ' || d.controlfile_converted       ,1,50)) || chr(10)
    || TRIM(SUBSTR('Controlfile SCN         : ' || d.controlfile_change#         ,1,50)) || chr(10)
    || TRIM(SUBSTR('Controlfile Sequence#   : ' || d.controlfile_sequence#       ,1,50)) || chr(10)
    || TRIM(SUBSTR('Controlfile Time       : ' || TO_CHAR(d.controlfile_time,'DD-MON-YYYY HH24:MI:SS')     ,1,50)) || chr(10)
       column1
  ,    
                          '[DataGuard Information]' || chr(10)
    || TRIM(SUBSTR('Primary DB Unique Name      : ' || d.primary_db_unique_name     ,1,50)) || chr(10)
    || TRIM(SUBSTR('DataGuard Role              : ' || d.database_role               ,1,50)) || chr(10)
    || TRIM(SUBSTR('Protection Mode             : ' || d.protection_mode             ,1,50)) || chr(10)
    || TRIM(SUBSTR('Protection Level            : ' || d.protection_level           ,1,50)) || chr(10)
    || TRIM(SUBSTR('DataGuard Broker            : ' || d.dataguard_broker           ,1,50)) || chr(10)
    || TRIM(SUBSTR('DataGuard Status            : ' || d.guard_status               ,1,50)) || chr(10)
    || TRIM(SUBSTR('SwitchOver Status           : ' || d.switchover_status           ,1,50)) || chr(10)
    || TRIM(SUBSTR('Activation SCN              : ' || d.activation#                 ,1,50)) || chr(10)
    || TRIM(SUBSTR('SwitchOver SCN              : ' || d.switchover#                 ,1,50)) || chr(10)
    || TRIM(SUBSTR('Standby Became Primary SCN  : ' || d.standby_became_primary_scn ,1,50)) || chr(10)
    || TRIM(SUBSTR('Supplemental Log Data MIN   : ' || d.supplemental_log_data_min   ,1,50)) || chr(10)
    || TRIM(SUBSTR('Supplemental Log Data PK    : ' || d.supplemental_log_data_pk   ,1,50)) || chr(10)
    || TRIM(SUBSTR('Supplemental Log Data UI    : ' || d.supplemental_log_data_ui   ,1,50)) || chr(10)
    || TRIM(SUBSTR('Supplemental Log Data PL    : ' || d.supplemental_log_data_pl   ,1,50)) || chr(10)
    || chr(10)   
    || '[Fast Start Failover Info]' || chr(10)
    || TRIM(SUBSTR('FS Failover Status          : ' || d.fs_failover_status       ,1,50)) || chr(10)
    || TRIM(SUBSTR('FS Failover Current Target  : ' || d.fs_failover_current_target ,1,50)) || chr(10)
    || TRIM(SUBSTR('FS Failover Threshold       : ' || d.fs_failover_threshold     ,1,50)) || chr(10)
    || TRIM(SUBSTR('FS Failover Observer Present: ' || d.fs_failover_observer_present ,1,50)) || chr(10)
    || TRIM(SUBSTR('FS Failover Observer Host   : ' || d.fs_failover_observer_host   ,1,50)) || chr(10)
       column2
FROM v$database d ;


====

for [DataGuard Information] Info !!!

set pagesize 200

SELECT /* First Column */
                          '[DataGuard Information]' || chr(10)
    || TRIM(SUBSTR('Primary DB Unique Name      : ' || d.primary_db_unique_name             ,1,50)) || chr(10)
    || TRIM(SUBSTR('DataGuard Role              : ' || d.database_role                      ,1,50)) || chr(10)
    || TRIM(SUBSTR('Protection Mode             : ' || d.protection_mode                    ,1,50)) || chr(10)
    || TRIM(SUBSTR('Protection Level            : ' || d.protection_level                   ,1,50)) || chr(10)
    || TRIM(SUBSTR('DataGuard Broker            : ' || d.dataguard_broker                   ,1,50)) || chr(10)
    || TRIM(SUBSTR('DataGuard Status            : ' || d.guard_status                       ,1,50)) || chr(10)
    || TRIM(SUBSTR('SwitchOver Status           : ' || d.switchover_status                  ,1,50)) || chr(10)
    || TRIM(SUBSTR('Activation SCN              : ' || d.activation#                        ,1,50)) || chr(10)
    || TRIM(SUBSTR('SwitchOver SCN              : ' || d.switchover#                        ,1,50)) || chr(10)
    || TRIM(SUBSTR('Standby Became Primary SCN  : ' || d.standby_became_primary_scn         ,1,50)) || chr(10)
    || TRIM(SUBSTR('Supplemental Log Data MIN   : ' || d.supplemental_log_data_min          ,1,50)) || chr(10)
    || TRIM(SUBSTR('Supplemental Log Data PK    : ' || d.supplemental_log_data_pk           ,1,50)) || chr(10)
    || TRIM(SUBSTR('Supplemental Log Data UI    : ' || d.supplemental_log_data_ui           ,1,50)) || chr(10)
    || TRIM(SUBSTR('Supplemental Log Data PL    : ' || d.supplemental_log_data_pl           ,1,50)) || chr(10)
    || chr(10)   
    || '[Fast Start Failover Info]' || chr(10)
    || TRIM(SUBSTR('FS Failover Status          : ' || d.fs_failover_status               ,1,50)) || chr(10)
    || TRIM(SUBSTR('FS Failover Current Target  : ' || d.fs_failover_current_target         ,1,50)) || chr(10)
    || TRIM(SUBSTR('FS Failover Threshold       : ' || d.fs_failover_threshold              ,1,50)) || chr(10)
    || TRIM(SUBSTR('FS Failover Observer Present: ' || d.fs_failover_observer_present        ,1,50)) || chr(10)
    || TRIM(SUBSTR('FS Failover Observer Host   : ' || d.fs_failover_observer_host           ,1,50)) || chr(10)
       column2
FROM v$database d ;



Posted by at No comments:

Tuesday, 15 December 2015

Oracle service name to hostname


Oracle service name to hostname  ... 





undefine service_name
set linesize 200
col name         for a14
col GOAL         for a10
col NETWORK_NAME for a14
col HOST_NAME     for a40
col INSTANCE_NAME for a12
SELECT a.inst_id,INSTANCE_NAME,service_id,HOST_NAME,name,network_name,creation_date,creation_date_hash,goal,dtp,aq_ha_notification,clb_goal 
FROM GV$SERVICES a,GV$INSTANCE b
WHERE  a.inst_id=b.inst_id
and name in  ( upper ('&&service_name') ,lower('&&service_name') ) ;
undefine service_name


 
for Scan ip 
from grid home ...
/u01/grid/11.2.0.3/grid/bin/srvctl config scan


/u01/app/grid/12.1.0/grid/bin/olsnodes -i -n -t -s -a

Posted by at 1 comment:

Saturday, 12 December 2015

Oracle Instance info ...

Instance info ... 

Oracle Instance info ... 



Database size !!!!
http://anuj-singh.blogspot.com/2023/09/size-of-oracle-database.html?zx=72890a8f565d482d





set head off verify off echo off pages 1500 linesize 120 feedback off
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select
 'INSTANCE_NUMBER.............................................: '||   INSTANCE_NUMBER   ,
 'INSTANCE_NAME...............................................: '||     INSTANCE_NAME   ,
 'HOST_NAME...................................................: '||         HOST_NAME   ,
 'VERSION.....................................................: '||           VERSION   ,
 'VERSION_LEGACY..............................................: '||     VERSION_LEGACY   ,
 'VERSION_FULL................................................: '||     VERSION_FULL   ,
 'STARTUP_TIME................................................: '||      STARTUP_TIME   ,
 'STATUS......................................................: '||            STATUS   ,
 'PARALLEL....................................................: '||          PARALLEL   ,
 'THREAD#.....................................................: '||           THREAD#   ,
 'ARCHIVER....................................................: '||          ARCHIVER   ,
 'LOG_SWITCH_WAIT.............................................: '||   LOG_SWITCH_WAIT   ,
 'LOGINS......................................................: '||            LOGINS   ,
 'SHUTDOWN_PENDING............................................: '||  SHUTDOWN_PENDING   ,
 'DATABASE_STATUS.............................................: '||   DATABASE_STATUS   ,
 'INSTANCE_ROLE...............................................: '||     INSTANCE_ROLE   ,
 'ACTIVE_STATE................................................: '||      ACTIVE_STATE   ,
 'BLOCKED.....................................................: '||           BLOCKED 
from  Gv$instance;
set head on verify on echo on feedback on  


set head off verify off echo off pages 1500 linesize 120 feedback off
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select
 'INSTANCE_NUMBER.............................................: '||   INSTANCE_NUMBER   ,
 'INSTANCE_NAME...............................................: '||     INSTANCE_NAME   ,
 'HOST_NAME...................................................: '||         HOST_NAME   ,
 'VERSION.....................................................: '||           VERSION   ,
 'STARTUP_TIME................................................: '||      STARTUP_TIME   ,
 'STATUS......................................................: '||            STATUS   ,
 'PARALLEL....................................................: '||          PARALLEL   ,
 'THREAD#.....................................................: '||           THREAD#   ,
 'ARCHIVER....................................................: '||          ARCHIVER   ,
 'LOG_SWITCH_WAIT.............................................: '||   LOG_SWITCH_WAIT   ,
 'LOGINS......................................................: '||            LOGINS   ,
 'SHUTDOWN_PENDING............................................: '||  SHUTDOWN_PENDING   ,
 'DATABASE_STATUS.............................................: '||   DATABASE_STATUS   ,
 'INSTANCE_ROLE...............................................: '||     INSTANCE_ROLE   ,
 'ACTIVE_STATE................................................: '||      ACTIVE_STATE   ,
 'BLOCKED.....................................................: '||           BLOCKED
from  Gv$instance;
set head on verify on echo on feedback on



set head off verify off echo off pages 0 linesize 120 feedback off alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS'; select 'Sysdate.....................................................: '|| sysdate , 'INSTANCE_NUMBER.............................................: '|| INSTANCE_NUMBER , 'INSTANCE_NAME...............................................: '|| INSTANCE_NAME , 'HOST_NAME...................................................: '|| HOST_NAME , 'VERSION.....................................................: '|| VERSION , 'STARTUP_TIME................................................: '|| STARTUP_TIME , 'STATUS......................................................: '|| STATUS , 'PARALLEL....................................................: '|| PARALLEL , 'THREAD#.....................................................: '|| THREAD# , 'ARCHIVER....................................................: '|| ARCHIVER , 'LOG_SWITCH_WAIT.............................................: '|| LOG_SWITCH_WAIT , 'LOGINS......................................................: '|| LOGINS , 'SHUTDOWN_PENDING............................................: '|| SHUTDOWN_PENDING , 'DATABASE_STATUS.............................................: '|| DATABASE_STATUS , 'INSTANCE_ROLE...............................................: '|| INSTANCE_ROLE , 'ACTIVE_STATE................................................: '|| ACTIVE_STATE , 'BLOCKED.....................................................: '|| BLOCKED from gv$instance; set head on verify on echo on feedback on



set linesize 500
col PDB_NAME for a15
select name CDB_NAME,(select name from v$pdbs where rownum <2) PDB_NAME,open_mode,database_role,
(SELECT to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') FROM dual) "Current_time_db" ,
(select INSTANCE_NAME from v$instance) INSTANCE_NAME,
(select HOST_NAME from v$instance ) HOST_NAME 
from v$database;

Oracle Instance info ... With database Name .
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    ,
'LOG_MODE...................................................: '|| LOG_MODE         ,
'OPEN MODE..................................................: '|| OPEN_MODE        ,
'INSTANCE_NAME..............................................: '|| INSTANCE_NAME    ,
'HOSTNAME...................................................: '|| HOST_NAME        ,
'STATUS.....................................................: '|| STATUS           ,
'LOGINS.....................................................: '|| LOGINS           ,
'STARTUP-TIME...............................................: '|| STARTUP_TIME     ,
'DATAGUARD BROKER...........................................: '|| DATAGUARD_BROKER ,
'GUARD STATUS...............................................: '|| GUARD_STATUS     ,
'FLASHBACK ON...............................................: '|| FLASHBACK_ON     ,
'PROTECTION MODE............................................: '|| PROTECTION_MODE  ,
'CONTROLFILE TYPE...........................................: '|| CONTROLFILE_TYPE
from gv$instance i, v$database d
-- where 1=1  
-- and i.inst_id=d.inst_id
order by 2;
set head on verify on echo on feedback on


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    ,
'LOG_MODE...................................................: '|| LOG_MODE         ,
'OPEN MODE..................................................: '|| OPEN_MODE        ,
'INSTANCE_NAME..............................................: '|| INSTANCE_NAME    ,
'HOSTNAME...................................................: '|| HOST_NAME        ,
'STATUS.....................................................: '|| STATUS           ,
'LOGINS.....................................................: '|| LOGINS           ,
'Uptime.....................................................: '|| floor(sysdate - startup_time) || ' days(s) ' || trunc( 24*((sysdate-startup_time) - trunc(sysdate-startup_time))) || ' hour(s) ' ||  mod(trunc(1440*((sysdate - startup_time) -  trunc(sysdate-startup_time))), 60) ||' minute(s) ' || mod(trunc(86400*((sysdate-startup_time) - trunc(sysdate-startup_time))), 60) ||' seconds'    ,
'STARTUP-TIME...............................................: '|| STARTUP_TIME     ,
'DATAGUARD BROKER...........................................: '|| DATAGUARD_BROKER ,
'GUARD STATUS...............................................: '|| GUARD_STATUS     ,
'FLASHBACK ON...............................................: '|| FLASHBACK_ON     ,
'PROTECTION MODE............................................: '|| PROTECTION_MODE  ,
'CONTROLFILE TYPE...........................................: '|| CONTROLFILE_TYPE
from gv$instance i, v$database d
-- where 1=1  
-- and i.inst_id=d.inst_id
order by 2;
set head on verify on echo on feedback on
Instance uptime ...

 set linesize 200 pagesize 200
column  hostname format a30
column "Instance Name" format a16
column "Started At" format a26
column "Database_Uptime" format a50
SELECT
instance_number ,instance_name as "Instance Name",host_name as Hostname,to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') as "Started_At",floor(sysdate - startup_time) || ' days(s) ' ||trunc( 24*((sysdate - startup_time) - trunc(sysdate - startup_time))) || ' hour(s) ' ||
mod(trunc(1440*((sysdate - startup_time) - trunc(sysdate - startup_time))), 60) ||' minute(s) ' ||mod(trunc(86400*((sysdate - startup_time) - trunc(sysdate - startup_time))), 60) ||' seconds' as "Database_Uptime",STATUS
FROM gv$instance;






set pages 0 lines 300 trims on head off feed off ver off
select '# ---------------------------'||chr(10)||
       '# --  Instance Information --'||chr(10)||
       '# ---------------------------'||chr(10)
from dual;
select 'Host Name : '||host_name||chr(10)||
       'Instance ID : '||inst_id||chr(10)||
       'Instance Name : '||instance_name||chr(10)||
       'Version : '||version||chr(10)||
       'Startup Time : '||to_char(startup_time, 'DD-MON-RR HH24:MI:SS')||chr(10)||
       'Instance Role : '||instance_role||chr(10)||
       'Blocked :' ||blocked
from gv$instance order by inst_id asc
/


======

alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 300
col HOST_NAME for a45
select 
  sysdate
, i.inst_id
, case when i.inst_id=sys_context('userenv', 'instance') then '*' end connected
, i.instance_name
, i.instance_role
, i.host_name
, i.startup_time
, i.status
, i.version
, (select count(*) from gv$session s where s.type='USER' and s.status='ACTIVE' and s.inst_id=i.inst_id) user_sessions
--, round((select m.value from v$sysmetric m where m.metric_id=2147 and m.group_id=2), 2) aas
--, 0 tps
, i.inst_id id_
from   gv$instance i
order by   i.inst_id





set linesize 300 
col BANNER_FULL for a50 wrap
col BANNER_LEGACY for a50 wrap
select * from gv$version;




  
alter session set nls_date_format='dd-mm-yyyy hh24:mi'; 
set line 300 pagesize 100
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
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 * from gv$encryption_wallet order by inst_id;

SET LINESIZE 100
SELECT con_id, key_id FROM v$encryption_keys;


SELECT KEY_ID 
FROM V$ENCRYPTION_KEYS 
WHERE ACTIVATION_TIME = (SELECT MAX(ACTIVATION_TIME) 
                         FROM V$ENCRYPTION_KEYS
                         WHERE ACTIVATING_DBID = (SELECT DBID FROM V$DATABASE));


set linesize 300
col VALUE for a60
col NAME for a25
select name , value from v$parameter where name in ('encrypt_new_tablespaces','tde_configuration','wallet_root') ;





set linesize 300 
col fs_failover_observer_host for a40
select fs_failover_status,fs_failover_observer_present,fs_failover_observer_host     from v$database; 




set linesize 300 
col fs_failover_observer_host for a40
col host format a20
col role for a15
col host for a15 
col role for a20
col  db_name for a15
col host for a20
select sys_context('userenv' ,'server_host') host,sys_context('userenv', 'db_name') db_name,sys_context('userenv' ,'database_role') role,fs_failover_status,fs_failover_observer_present,fs_failover_observer_host
 from v$database; 



set linesize 300 pagesize 300
select ts#, encryptionalg, encryptedts, key_version, status from v$encrypted_tablespaces;

col wrl_parameter for a70
select * from v$encryption_wallet;

select * from v$encryption_keys;

select wrl_parameter,status,wallet_type from v$encryption_wallet;
select key_id,keystore_type from v$encryption_keys;

select key_id from v$encryption_keys;

select keystore_type from v$encryption_keys;

select wrl_parameter from v$encryption_wallet;

select status from v$encryption_wallet;

select * from v$encrypted_tablespaces;

select tablespace_name, encrypted from dba_tablespaces;

select * from dba_encrypted_columns;

database info !!!
  http://anuj-singh.blogspot.com/2017/11/oracle-database-info-from-vdatabase.html


set pages 50  linesize 300
col open_mode for a23
col maxseq for 9999999
Col role for a20
col database_role for a10
col instance_name for a10
col user for a10
col host_name for a20
col HOST for a20
col version for a10
col started for a15

alter session set NLS_DATE_FORMAT ='MM/DD/YYYY HH24:MI:SS';

select tab1.instance,substr(tab1.host_name,1,15) HOST,tab1.version,
to_char(tab1.startup_time,'Mon-dd-yy:hh24:mm') STARTED,tab1.open_mode,tab1.database_role ROLE,tab1.dbid,tab2.seqn MAXSEQ from
(select a.inst_id,a.host_name, b.open_mode,b.database_role,a.version,a.startup_time,b.dbid,
a.thread# thrd,a.instance_name instance from gv$instance a,gv$database b
where a.inst_id=b.inst_id)
tab1,
(select thread#,max(sequence#) seqn from gv$log_history a
where a.resetlogs_time >= (select max(b.RESETLOGS_TIME) from gv$log_history b) group by thread#)
tab2
where tab1.thrd=tab2.thread#
order by inst_id
/



ps -ef|egrep -i 'smon|mrp0|lsp|FSFP|NSV|NSS|NSA|CTWR'|egrep -v egrep

NSV Performs broker network communications between databases in a Data Guard environment
NSS SYNC transport is configured for a remote standby
NSA standby destinations configured for ASYNC transport
CTWR tracks changed blocks as redo is generated at a primary








set linesize 500

COL startup_time FOR A26;
COL short_host_name FOR A30;
COL platform_name FOR A40;

PRO Database/Instance
PRO ~~~~~~~~~~~~~~~~~
SELECT st.dbid,				
       st.instance_number,	
       st.startup_time,		
       i.version,			
       d.name db_name,
       i.instance_name,
       TRANSLATE(LOWER(SUBSTR(SUBSTR(host_name, 1, decode(INSTR(i.host_name, '.'),0,30,INSTR(i.host_name, '.')) - 1), 1, 30)),
        'abcdefghijklmnopqrstuvwxyz0123456789-_ ''`~!@#$%&*()=+[]{}\|;:",.<>/?'||CHR(0)||CHR(9)||CHR(10)||CHR(13)||CHR(38),
        'abcdefghijklmnopqrstuvwxyz0123456789-_')
        short_host_name,			
        pl.platform_name	
  FROM (SELECT DISTINCT dbid, instance_number, startup_time
        FROM dba_hist_database_instance) st,
       gv$instance i,
       gv$database d,
       (select os||' '||bit platform_name
          from (
            SELECT 1 id, substr(banner,9,instr(banner,':')-9)  os FROM v$version  where banner like 'TNS for%') a Left outer join
            (select 1 id, substr(banner,instr(banner,'bit')-2,5) bit   FROM v$version  where banner like '%bit Pro%') b 
            on a.id=b.id
            ) pl
  WHERE st.instance_number=i.inst_id
  AND   i.inst_id=d.inst_id
  ORDER BY
       dbid,				
       instance_number,	
       startup_time
/




===

fro Web 

https://github.com/fatdba/Oracle-Database-Scripts/blob/main/Admin_General/dbserverdetails.sql

set linesize 300 pagesize 300
 col SYSTEM_ITEM for a30 
 col SYSTEM_VALUE for a60
 
 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
;






===================

set linesize 300
col Uptime for a60
col hostname for a40
select  SYS_CONTEXT('USERENV','HOST') hostname , inst_id, instance_name, status, startup_time || ' - ' ||
trunc(SYSDATE-(STARTUP_TIME) ) || ' day(s), ' || trunc(24*((SYSDATE-STARTUP_TIME) -
trunc(SYSDATE-STARTUP_TIME)))||' hour(s), ' || mod(trunc(1440*((SYSDATE-STARTUP_TIME) - trunc(SYSDATE-STARTUP_TIME))), 60) ||' minute(s), ' || mod(trunc(86400*((SYSDATE-STARTUP_TIME) - trunc(SYSDATE-STARTUP_TIME))), 60) ||' seconds' "Uptime"
from     gv$instance
order by inst_id
/

=====

-- from history 






set linesize 300
col instance_name for a15
col startup_time for a27
select * from ( select instance_name,startup_time from dba_hist_database_instance where 1=1 and DBID =(select DBID from v$database)  order by startup_time desc) where rownum < 20;




alter session set nls_date_format='dd-mm-yyyy hh24:mi'; 
set line 300 pagesize 100
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
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 * from gv$encryption_wallet order by inst_id;



 



 
 set linesize 300 pagesize 300
 
col  PLATFORM_NAME for a20 
col HOST_NAME for a20
col CPU_COUNT for a20
 select d.dbid
,d.name dbname
,d.db_unique_name
,d.platform_name
,i.version
,i.inst_id
,i.instance_number
,i.instance_name
,lower(substr(i.host_name||'.', 1, instr(i.host_name||'.', '.') - 1)) host_name
,lpad(ora_hash(lower(substr(i.host_name||'.', 1, instr(i.host_name||'.', '.') - 1)),999999),6,'6') host_hv
,p.value cpu_count
from v$database d
,gv$instance i
,gv$system_parameter2 p
where p.inst_id = i.inst_id
and p.name = 'cpu_count';



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)
,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 '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 'AMD FX(tm)-6300 Six-Core Processor' like '%5675%' then 'X2-2 ' end||
    case when 'AMD FX(tm)-6300 Six-Core Processor' like '%2690%' then 'X3-2 ' end||
    case when 'AMD FX(tm)-6300 Six-Core Processor' like '%2697%' then 'X4-2 ' end||
    case when 'AMD FX(tm)-6300 Six-Core Processor' like '%2699%' then 'X5-2 ' end||
    case when 'AMD FX(tm)-6300 Six-Core Processor' like '%8870%' then 'X3-8 ' end||
    case when 'AMD FX(tm)-6300 Six-Core Processor' like '%8895%' then 'X4-8 or X5-8 ' end||
    'with '||cell.cnt||' storage servers'
    else 'Unknown' end from cell
union all
select 'Processor:', 'AMD FX(tm)-6300 Six-Core Processor'
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;







alter session set nls_date_format='dd-mm-yyyy hh24:mi';




prompt ####################### Execution Time ################################
set linesize 140
set pagesize 9999
select sysdate as current_date from dual;

prompt ####################### Database Version ################################
select * from v$version;




prompt ####################### Instance Information ################################

set linesize 300
col instance_number for 99999999
col INSTANCE_NAME for a14
col host_name for a12
col status for a8
col STARTUP_TIME for a16
col THREAD# for 999999999
col paraller for a12
select INSTANCE_NUMBER,INSTANCE_NAME,host_name,STATUS,STARTUP_TIME,THREAD#,parallel from Gv$instance;





prompt ####################### Database Information ################################
set linesize 300

col "Database Name" 		for a8
col open_mode 			for a12
col created 			for a18
col log_mode 			for a12
col controlfile_type 		for a12
col controlfile_created 	for a18
col controlfile_time 		for a18
col resetlogs_change# 		for 999999999999999
col checkpoint_change# 		for 999999999999999
col dbid 			for 999999999999999
col controlfile_change# 	for 999999999999999
col resetlogs_time 		for a18


select dbid, name "Database Name",open_mode,flashback_on,open_mode, log_mode from v$database;

select created ,checkpoint_change# ,controlfile_type,controlfile_created,controlfile_change#,controlfile_time,resetlogs_change#,resetlogs_time from v$database;





prompt ####################### SCN Information ################################




col time for a20
col scn for 99999999999999999999999
col Headroom for 999999999999999
SELECT to_char(tim,'yyyy-mm-dd hh24:mi:ss') time,scn,round((chk16kscn-scn)/24/3600/16/1024,1) Headroom
FROM  
(
select tim, scn,
((
((to_number(to_char(tim,'YYYY'))-1988)*12*31*24*60*60) +
((to_number(to_char(tim,'MM'))-1)*31*24*60*60) +
(((to_number(to_char(tim,'DD'))-1))*24*60*60) +
(to_number(to_char(tim,'HH24'))*60*60) +
(to_number(to_char(tim,'MI'))*60) +
(to_number(to_char(tim,'SS')))
) * (16*1024)) chk16kscn
from
(select sysdate tim,checkpoint_change# scn from v$database))
ORDER BY tim;







alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set linesize 300
col HOST_ADDRESS for a15
col "DB RAC?" for a25
col HOST_NAME for a26
select name INSTANCE,HOST_NAME, UTL_INADDR.GET_HOST_ADDRESS "HOST_ADDRESS",
LOGINS, archiver,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB_UP_TIME", RESETLOGS_TIME "RESET_TIME", 
FLOOR(sysdate-startup_time) days,
(select DECODE(vp1.value,'TRUE','Yes ('|| decode(vp1.value,'TRUE',' instances: '||vp2.value)||')','No')
from v$instance,(select value from v$parameter where name like 'cluster_database') vp1,
(select value from v$parameter where name like 'cluster_database_instances') vp2) "DB RAC?" from v$database,gv$instance;




=============






set termout off
column db_inst_num  heading "Inst Num"  new_value db_inst_num  format 99999 noprint;
column db_inst_name heading "Instance"  new_value db_inst_name format a12 noprint;
column db_db_name   heading "DB Name"   new_value db_db_name   format a12 noprint;
column db_dbid      heading "DB Id"     new_value db_dbid noprint;

select d.dbid            db_dbid
     , d.name            db_db_name
     , i.instance_number db_inst_num
     , i.instance_name   db_inst_name
  from v$database d,
       v$instance i;








set termout on

-- list databases and instances
set underline on;
column DBID        heading "DB Id"              format a12;
column a_db_name   heading "DB Name"            format a12;
column a_cont_name heading "AWR Data Source"    format a24;
column type        heading "Type"               format a24;

prompt
prompt
prompt Available Databases and Instances.
prompt The database with * is current database
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

select distinct
      (case
          when a.a_dbid = sys_context('userenv','con_dbid') THEN '* '
          else '  '
       end) || a.a_dbid DBID,
       a.a_db_name, a.a_cont_name,
      (case    data_source_type
        when 'ROOT' THEN
          case rac
            when 'YES' THEN
              case cdb
                when 'YES' THEN 'CDB, RAC'
                else 'NON_CDB, RAC'
              end
            else
              case cdb
                when 'YES' THEN 'CDB'
                else 'NON_CDB'
              end
          end
        when 'PDB' THEN
          case rac
            when 'YES' THEN 'PDB, RAC'
            else 'PDB'
          end
        when 'IMPORTED' THEN
          case rac
            when 'YES' THEN
              case cdb
                when 'YES' THEN
                  case
                    when (sys.dbms_sqltune_util2.is_imported_pdb(a.a_dbid) =  'yes')
                      then 'IMPORTED, RAC, PDB'
                    else
                      'IMPORTED, RAC, CDB'
                  end
                else 'IMPORTED, RAC'
              end
            when 'NO' THEN
              case cdb
                when 'YES' THEN
                  case
                    when (sys.dbms_sqltune_util2.is_imported_pdb(a.a_dbid) =  'yes')
                      then 'IMPORTED, PDB'
                    else 'IMPORTED, CDB'
                  end
                else 'IMPORTED'
              end
            else 'IMPORTED'
          end
      end ) type
  from
(
  select wr.dbid a_dbid,
         wr.db_name a_db_name,
         (case wr.cdb
            when 'YES' THEN 'CDB$ROOT'
            else wr.db_name -- in non_cdb, display db_name
          end) a_cont_name,
         sys.dbms_sqltune_util2.resolve_database_type(wr.dbid) data_source_type,
         wr.parallel rac, wr.cdb cdb, wr.con_id con_id
  from awr_root_database_instance wr
  UNION ALL
  select wr.dbid a_dbid,
           wr.db_name a_db_name,
           nvl(pi.pdb_name, wr.db_name) data_source,
           sys.dbms_sqltune_util2.resolve_database_type(wr.dbid) data_source_type,
           wr.parallel rac, wr.cdb cdb, wr.con_id con_id
  from awr_pdb_database_instance wr,
       awr_pdb_pdb_instance pi
  where wr.dbid  = pi.con_dbid (+)
        and sys_context('userenv', 'con_id') > 2
) a ;




prompt
prompt DATABASE Size
prompt
prompt


  

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,DATABASE_ROLE
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,DATABASE_ROLE from v$database)
group by free.SS,LOG_MODE,DATABASE_ROLE
/



set linesize 300
col "Database Size"  			for a20
col "Free space"       			for a20
col "Used space"      			for a20
col hostname   				for a35
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 sysdate Tdate,SYS_CONTEXT('USERENV','HOST') hostname,sys_context('USERENV', 'INSTANCE_NAME') Instance ,SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') DB_unique_name,
dbms_xplan.FORMAT_SIZE(sum(used.bytes)) 			"Database Size" , 
dbms_xplan.FORMAT_SIZE(sum(used.bytes) - free.SS ) 		"Used space"
, dbms_xplan.FORMAT_SIZE(free.SS )  				"Free space"
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
group by free.SS
/


====
Database size !!!!
http://anuj-singh.blogspot.com/2023/09/size-of-oracle-database.html?zx=72890a8f565d482d


Posted by at 5 comments:
Subscribe to: Posts (Atom)

Oracle DBA

My photo
Essex, United Kingdom

anuj blog Archive