09 December 2016

Shell script to send multiple SQL queries output in HTML format

Shell script to send multiple SQL queries output in HTML format

shell script content
***********************
#!/usr/bin/ksh
export ORACLE_BASE=/optware/oracle
ORACLE_HOME=/optware/oracle/11.2.0.4/db_1
export ORACLE_HOME
ORACLE_SID=PROD3
export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin
export PATH
ORAENV_ASK=NO

sqlplus /nolog <connect sys/password as sysdba

set markup HTML ON HEAD " -
DB Sync Status Report" -
TABLE "border='1' width='90%' align='center'" -
ENTMAP ON SPOOL ON

set pages 10000
spool /home/oracle/scripts/sync_status_detailed.html
@/home/oracle/scripts/dg_check.sql
spool off
exit
EOF

echo "Please find the attached detailed report, showing data about the DB Sync Status." | mutt -a "/home/oracle/scripts/sync_status_detailed.html" -s "DB Sync Status" abc@xyz.com
rm /home/oracle/scripts/sync_status_detailed.html

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

dg_check.sql contents

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

connect system/password@PROD3_P

col DATABASE_ROLE for a20

col host_name for a25

set pages 1000

SET NEWPAGE NONE

set lines 180

prompt

prompt ++++++++++++++++++++++++++++++++++++

prompt + Primary Database details

prompt ++++++++++++++++++++++++++++++++++++

select name "DB Name",host_name "Host Name",database_role "Database Role" from v$database,v$instance;

prompt

prompt ****************Last Sequence generated in Primary****************

SELECT THREAD# "Thread",max(SEQUENCE#)as "Last Sequence Generated"

FROM GV$ARCHIVED_LOG group by THREAD# ORDER BY 1

conn / as sysdba

prompt

set lines 180

set pages 130

SET NEWPAGE NONE

col DATABASE_ROLE for a20

col host_name for a35

col status for a15

prompt ++++++++++++++++++++++++++++++++++++

prompt + Standby Database details

prompt ++++++++++++++++++++++++++++++++++++

select name "DB Name",host_name "Host Name",database_role "Database Role" from v$database,v$instance;

prompt

Prompt *************Last Sequence received/applied/difference in Standby**********

SELECT distinct ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,max(SEQUENCE#) as SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#) group by THREAD#) ARCH,

(SELECT THREAD# ,max(SEQUENCE#) as SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#) group by THREAD#) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1

Prompt *************Last Sequence received/applied time in Standby**********

set pages 0

SET NEWPAGE NONE

select 'Last Sequence applied Time: ' Logs, to_char(next_time,'DD-MON-YY HH:MI:SS AM') Time from v$archived_log

where sequence# = (select max(sequence#) from v$archived_log where applied='YES')

union

select 'Last Sequence received Time: ' Logs, to_char(next_time,'DD-MON-YY HH:MI:SS AM') Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log) order by 1 desc;

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

08 December 2016

Shell script to send SQL query output in HTML format

#!/usr/bin/ksh
export ORACLE_BASE=/optware/oracle
ORACLE_HOME=/optware/oracle/11.2.0.4/db_1
export ORACLE_HOME
ORACLE_SID=PROD3
export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin
export PATH
ORAENV_ASK=NO

sqlplus /nolog < connect sys/xxxxxxx as sysdba
SET MARKUP HTML ON PREFORMAT OFF ENTMAP ON -
HEAD "DB Sync Status Report -
-

-->" -
BODY "TEXT='#FF00Ff'" -
TABLE "WIDTH='90%' BORDER='5'"
@/home/oracle/scripts/sync_status.sql
SPOOL /home/oracle/sync_status.html
/
SPOOL OFF
EXIT;
EOF

echo "Please find the attached report, showing the details on DB Sync Status." | mutt -a "/home/oracle/sync_status.html" -s "DB Sync Status" abc@xyz.com
rm /home/oracle/sync_status.html

25 August 2016

Identifying Your Oracle Database Software Release

Because Oracle Database continues to evolve and can require maintenance, Oracle periodically produces new releases. Not all customers initially subscribe to a new release or require specific maintenance for their existing release. As a result, multiple releases of the product exist simultaneously.

As many as five numbers may be required to fully identify a release. The significance of these numbers is discussed in the sections that follow.

Release Number Format

To understand the release nomenclature used by Oracle, examine the following example of an Oracle Database server labeled "Release 10.1.0.1.0".

Figure 1-1 Example of an Oracle Database Release Number

Description of "Figure 1-1 Example of an Oracle Database Release Number"

Note:

Starting with release 9.2, maintenance releases of Oracle Database are denoted by a change to the second digit of a release number. In previous releases, the third digit indicated a particular maintenance release.

Major Database Release Number

The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.

Database Maintenance Release Number

The second digit represents a maintenance release level. Some new features may also be included.

Application Server Release Number

The third digit reflects the release level of the Oracle Application Server (OracleAS).

Component-Specific Release Number

The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.

Platform-Specific Release Number

The fifth digit identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the affected platforms.

Checking Your Current Release Number

To identify the release of Oracle Database that is currently installed and to see the release levels of other database components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION. A sample query follows. (You can also query the V$VERSION view to see component-level information.) Other product release levels may increment independent of the database server.

COL PRODUCT FORMAT A35
COL VERSION FORMAT A15
COL STATUS FORMAT A15 
SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                  VERSION     STATUS
---------------------------------------- ----------- -----------
NLSRTL                                   10.2.0.1.0  Production
Oracle Database 10g Enterprise Edition   10.2.0.1.0  Prod
PL/SQL                                   10.2.0.1.0  Production
...

It is important to convey to Oracle the results of this query when you report problems with the software.

Reference: https://docs.oracle.com/cd/B28359_01/server.111/b28310/dba004.htm

28 April 2016

Check, how many times a database has been restarted

Can you find out how many times a database has been restarted since its creation?
Well, the answer is YES!

Oracle has this view dba_hist_database_instance for getting these details:

DBA_HIST_DATABASE_INSTANCE

DBA_HIST_DATABASE_INSTANCE displays the databases and instances in the Workload Repository.

Column	Datatype	NULL	Description
`DBID`	`NUMBER`	`NOT NULL`	Database ID
`INSTANCE_NUMBER`	`NUMBER`	`NOT NULL`	Instance number
`STARTUP_TIME`	`TIMESTAMP(3)`	`NOT NULL`	Startup time of the instance
`PARALLEL`	`VARCHAR2(3)`	`NOT NULL`	Indicates whether the instance is running in an Oracle Real Application Clusters (Oracle RAC) environment (`YES`) or not (`NO`)
`VERSION`	`VARCHAR2(17)`	`NOT NULL`	Database version
`DB_NAME`	`VARCHAR2(9)`		Name of the database
`INSTANCE_NAME`	`VARCHAR2(16)`		Name of the instance
`HOST_NAME`	`VARCHAR2(64)`		Name of the host
`LAST_ASH_SAMPLE_ID`	`NUMBER`	`NOT NULL`	Last sample ID for the active session history

SELECT STARTUP_TIME FROM dba_hist_database_instance ORDER BY startup_time DESC;

Hope this was helpful, have an Oracle day!

23 February 2016

V$ARCHIVED_LOG

copied as it is from:

https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1016.htm

This was for my personal reference, where I noticed few "NO" entries in the APPLIED column, now I know, why! I observed this, after switchover of the databases, the "NO" sequence is generated at the local destination, so it remains "NO".

V$ARCHIVED_LOG displays archived log information from the control file, including archive log names. An archive log record is inserted after the online redo log is successfully archived or cleared (name column is NULL if the log was cleared). If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE#, and FIRST_CHANGE#, but with a different name. An archive log record is also inserted when an archive log is restored from a backup set or a copy and whenever a copy of a log is made with the RMAN COPY command.

Column	Datatype	Description
`RECID`	`NUMBER`	Archived log record ID
`STAMP`	`NUMBER`	Archived log record stamp
`NAME`	`VARCHAR2(513)`	Archived log file name. If set to `NULL`, either the log file was cleared before it was archived or an RMAN backup command with the "delete input" option was executed to back up archivelog all (`RMAN> backup archivelog all delete input;`).
`DEST_ID`	`NUMBER`	Original destination from which the archive log was generated. The value is `0` if the destination identifier is not available.
`THREAD#`	`NUMBER`	Redo thread number
`SEQUENCE#`	`NUMBER`	Redo log sequence number
`RESETLOGS_CHANGE#`	`NUMBER`	Resetlogs change number of the database when the log was written
`RESETLOGS_TIME`	`DATE`	Resetlogs time of the database when the log was written
`RESETLOGS_ID`	`NUMBER`	Resetlogs identifier associated with the archived redo log
`FIRST_CHANGE#`	`NUMBER`	First change number in the archived log
`FIRST_TIME`	`DATE`	Timestamp of the first change
`NEXT_CHANGE#`	`NUMBER`	First change in the next log
`NEXT_TIME`	`DATE`	Timestamp of the next change
`BLOCKS`	`NUMBER`	Size of the archived log (in blocks)
`BLOCK_SIZE`	`NUMBER`	Redo log block size. This is the logical block size of the archived log, which is the same as the logical block size of the online log from which the archived log was copied. The online log logical block size is a platform-specific value that is not adjustable by the user.
`CREATOR`	`VARCHAR2(7)`	Creator of the archivelog: `ARCH` - Archiver process `FGRD` - Foreground process `RMAN` - Recovery Manager `SRMN` - RMAN at standby `LGWR` - Logwriter process
`REGISTRAR`	`VARCHAR2(7)`	Registrar of the entry: `RFS` - Remote File Server process `ARCH` - Archiver process `FGRD` - Foreground process `RMAN` - Recovery manager `SRMN` - RMAN at standby `LGWR` - Logwriter process
`STANDBY_DEST`	`VARCHAR2(3)`	Indicates whether the entry is an archivelog destination (`YES`) or not (`NO`)
`ARCHIVED`	`VARCHAR2(3)`	Indicates whether the online redo log was archived (`YES`) or whether RMAN only inspected the log and created a record for future application of redo logs during recovery (`NO`). See Also: Oracle Database Backup and Recovery Basics.
`APPLIED`	`VARCHAR2(3)`	Indicates whether the archivelog has been applied to its corresponding standby database (`YES`) or not (`NO`). The value is always `NO` for local destinations. This column is meaningful at the physical standby site for the `ARCHIVED_LOG` entries with`REGISTRAR`='`RFS`' (which means this log is shipped from the primary to the standby database). If `REGISTRAR`='`RFS`' and `APPLIED` is`NO`, then the log has arrived at the standby but has not yet been applied. If `REGISTRAR`='`RFS`' and `APPLIED` is `YES`, the log has arrived and been applied at the standby database. You can use this field to identify archivelogs that can be backed up and removed from disk.
`DELETED`	`VARCHAR2(3)`	Indicates whether an RMAN `DELETE` command has physically deleted the archived log file from disk, as well as logically removing it from the control file of the target database and from the recovery catalog (`YES`) or not (`NO`)
`STATUS`	`VARCHAR2(1)`	Status of the archived log: `A` - Available `D` - Deleted `U` - Unavailable `X` - Expired
`COMPLETION_TIME`	`DATE`	Time when the archiving completed
`DICTIONARY_BEGIN`	`VARCHAR2(3)`	Indicates whether the log contains the start of a LogMiner dictionary (`YES`) or not (`NO`)
`DICTIONARY_END`	`VARCHAR2(3)`	Indicates whether the log contains the end of a LogMiner dictionary (`YES`) or not (`NO`)
`END_OF_REDO`	`VARCHAR2(3)`	Indicates whether the archived redo log contains the end of all redo information from the primary database (`YES`) or not (`NO`)
`BACKUP_COUNT`	`NUMBER`	Indicates the number of times this file has been backed up. Values range from 0-15. If the file has been backed up more than 15 times, the value remains 15.
`ARCHIVAL_THREAD#`	`NUMBER`	Redo thread number of the instance that performed the archival operation. This column differs from the `THREAD#` column only when a closed thread is archived by another instance.
`ACTIVATION#`	`NUMBER`	Number assigned to the database instantiation
`IS_RECOVERY_DEST_FILE`	`VARCHAR2(3)`	Indicates whether the file was created in the flash recovery area (`YES`) or not (`NO`)
`COMPRESSED`	`VARCHAR2(3)`	This column is reserved for internal use only
`FAL`	`VARCHAR2(3)`	Indicates whether the archive log was generated as the result of a FAL request (`YES`) or not (`NO`)
`END_OF_REDO_TYPE`	`VARCHAR2(10)`	Possible values are as follows: `SWITCHOVER` - Shows archived redo log files that are produced at the end of a switchover `TERMINAL` - Shows archived redo log files produced after a failover `RESETLOGS` - Shows online redo log files archived on the primary database after an `ALTER DATABASE OPEN RESETLOGS` statement is issued `ACTIVATION` - Shows any log files archived on a physical standby database after an `ALTER DATABASE ACTIVATE STANDBY` `DATABASE`statement is issued "empty string" - Any empty string implies that that the log is just a normal archival and was not archived due to any of the other events