How to create a database mail account on MS SQL SERVER - Step 1 for email notification

I am currently working on SQL Server Always On Availability Group. I wanted to notified when my SQL Servers are failing over.

You can follow this link 

https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/create-a-database-mail-account?view=sql-server-2017

Create a Database Mail Account

• 03/14/2017
• 2 minutes to read
• Contributors
  • 
   
  • 

APPLIES TO: SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse

Use either the Database Mail Configuration Wizard or Transact-SQL to create a Database Mail account.

• Before you begin: Prerequisites
• To Create a Database Mail Account, using: Database Mail Configuration Wizard, Transact-SQL
• Follow Up: Next Steps to Configure the Database Mail

Before You Begin

Prerequisites

• Determine the server name and port number for the Simple Mail Transfer Protocol (SMTP) server you use to send e-mail.If the SMTP server requires authentication, determine the user name and password for the SMTP server.
• Optionally, you may also specify the type of the server and the port number for the server. The server type is always 'SMTP' for outgoing mail. Most SMTP servers use port 25, the default.

Using Database Mail Configuration Wizard

To create a Database Mail account using a Wizard

• In Object Explorer, connect to the SQL Server instance you want to configure Database Mail on, and expand the server tree.
• Expand the Management node
• Double Click Database Mail to open the Database Mail Configuration Wizard.
• On the Select Configuration Task page, select Manage Database Mail accounts and profiles, and click Next.
• On the Manage Profiles and Accounts page, select Create a new account and click Next.
• On the New Account page, specify the account name, description, mail server information, and authentication type. Click Next
• On the Complete the Wizard page, review the actions to be performed and click Finishto complete creating the new account.

Using Transact-SQL

To Create a Database Mail account using Transact-SQL

Execute the stored procedure msdb.dbo.sysmail_add_account_sp to create the account and specify the following information:

• The name of the account to create.
• An optional description of the account.
• The e-mail address to show on outgoing e-mail messages.
• The display name to show on outgoing e-mail messages.
• The server name of the SMTP server.
• The user name to use to log on to the SMTP server, if the SMTP server requires authentication.
• The password to use to log on to the SMTP server, if the SMTP server requires authentication.
  The following example creates a new Database Mail account.

EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'AdventureWorks Administrator',  
    @description = 'Mail account for administrative e-mail.',  
    @email_address = 'dba@Adventure-Works.com',  
    @display_name = 'AdventureWorks Automated Mailer',  
    @mailserver_name = 'smtp.Adventure-Works.com' ;  

mlog$

--perceived size
select table_name,round((blocks*8),2)||'kb' "size"  from all_tables where table_name = 'MLOG$_MTL_ITEM_CATEGORIES1';

--actual

select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size" from all_tables where table_name ='MLOG$_MTL_ITEM_CATEGORIES1';


truncate table inv.MLOG$_MTL_ITEM_CATEGORIES1;

exec dbms_stats.gather_table_stats('INV','MLOG$_MTL_ITEM_CATEGORIES1');

How to maintain disk space on EBS

Purge Concurrent Request and/or Manager Data 

One of the important area of Concurrent Manager tuning is monitoring the space usage for the subsets within each concurrent manager. When the space in FND_CONCURRENT_PROCESSES and FND_CONCURRENT_REQUESTS exceed 50K, you can start to experience serious performance problems within your Oracle Applications. When you experience these space problems, a specific request called “Purge Concurrent Requests And/Or Manager Data” should be scheduled to run on a regular basis. This request to purge can be configured  the request data from the FND tables as well as the log files and output files on accumulate on disk.

When the tables FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROCESSES reaches above 3000-4000 rows, the performance diminishes.You have to run Purge Concurrent Request and/or Manager Data program on a regular basis depending on the amount of request being run.

Find Size in KB for FND_CONCURRENT_REQUESTS

sql> SELECT (BYTES/1024)/1024 “Size in KB” from dba_segments where SEGMENT_NAME=’FND_CONCURRENT_REQUESTS’;

O/P   Size in KB
          ———-
          3.65

SQL> SELECT (BYTES/1024)/1024 “Size in KB” from dba_segments WHERE SEGMENT_NAME=’FND_CONCURRENT_PROCESSES’;

O/P :      Size in KB
              ———-
              10.75

The Purge Concurrent Request and/or Manager Data program job can be used to purge Requests,Mgr Logs

and all requests depending on what is choosen.

Use the following options: Enter=ALL, Mode =Age, Mode Value=15 

How to Purge Concurrent Request and/or Manager Data

Loigin to frontend with sysadmin responsibility:

Navigate :

System Administrator — Concurrent – Requests

Click on Submit a New Request — Select Single Request click ok

Enter Name as Purge Concurrent Request and/or Manager Data

Entity – ALL / Request / Manager

ALL – Purges records from database tables that record history information for concurrent requests,concurrent managers, and purges request log files, manager log files, and report output files from the operating system.

Request – Purges records from database tables that record history information for concurrent requests, and purges request log files and report output files from the operating system.

Manager – Purges records from database tables that record history information for concurrent managers, and purges manager log files from the operating system. 

Mode – Age / Count

Age – Enter the number of days for which you want to save concurrent request history, log files, and report output files. The purge program deletes all records older (in days) than the number you enter. For example, if you enter “5”, then all concurrent request history, log files, and report output files older than five days is purged.

Count – Enter the number of (most recent) records for which you want to save concurrent request history, log file, and report output files. The purge program starts from the most recent records, retains the number you enter, and purges all remaining records. For example, if you enter “5”, then the five most recent concurrent request history records, request log files, manager log files, report output files are saved, and all remaining records are purged.

Mode Value – Value

Mode Value – Enter a value to define the number of days for Mode=Age or the number of records for Mode=Count. The valid values are 1 – 9999999

Then Submit.

The database tables that are affected by running the Purge Concurrent Request and/or Manager Data program are:

FND_CONCURRENT_REQUESTSThis table contains a complete history of all concurrent requests.

FND_RUN_REQUESTS
When a user submits a report set, this table stores information about the reports in the report set and the parameter values for each report.

FND_CONC_REQUEST_ARGUMENTSThis table records arguments passed by the concurrent manager to each program it starts running.

FND_DUAL
This table records when requests do not update database tables.

FND_CONCURRENT_PROCESSESThis table records information about Oracle Applications and operating system processes.

FND_CONC_STAT_LIST
This table collects runtime performance statistics for concurrent requests.

FND_CONC_STAT_SUMMARYThis table contains the concurrent program performance statistics generated by the Purge Concurrent Request and/or Manager Data program. The Purge Concurrent Request and/or Manager Data program uses the data in FND_CONC_STAT_LIST to compute these statistics.

Error: Agent status is currently UP. Shut down the Management Agent and wait for the agent to be marked UNREACHABLE before deleting

How to remove a target manually

Target to be removed: tangle.ecp.priv
OMS Repository:bugsy

-bash-4.1$ cd ./oracle_base/middleware/oms

Login successful
-bash-4.1$ ./emcli sync
Synchronized successfully
-bash-4.1$ ./emcli delete_target -name="tangle.ecp.priv:3872" -type="oracle_emd" -delete_monitored_targets
Error: Agent status is currently UP. Shut down the Management Agent and wait for the agent to be marked UNREACHABLE before deleting.

 ./emcli get_targets | grep tangle

Deinstalling in Silent Mode Using AgentDeinstall.pl Script

To deinstall a Management Agent in silent mode using the AgentDeinstall.pl script, follow these steps:

1. Invoke the AgentDeinstall.pl script:
   $<AGENT_HOME>/perl/bin/perl <AGENT_HOME>/sysman/install/AgentDeinstall.pl -agentHome <AGENT_HOME>
   For example,
   $/u01/app/Oracle/core/12.1.0.1.0/perl/bin/perl /u01/app/Oracle/core/12.1.0.1.0/sysman/install/AgentDeinstall.pl -agentHome /u01/app/Oracle/core/12.1.0.1.0/
2. Manually remove the targets, which were being monitored by the Management Agent you deinstalled, from the Enterprise Manager Cloud Control console.

Everything about EBS Versions

Delta

select
  abbreviation
,codelevel
from
  ad_trackable_entities
where
  abbreviation in( 'ad','txk','fnd','fwk','atg_pf','icx' )
order by
  abbreviation;

Opatch

/ora/10.1.2/forms How to open core.*

strings core.30729 | head -10

Troubleshooting Middleware

adop -status detail
grep oa_service_status $CONTEXT_FILE
grep ports $CONTEXT_FILE
grep oa_service_name $CONTEXT_FILE