If concurrent ma nagers are running then see the process using ps- ef | grep FND : Normally can take upto 5 minutes to kill all processes so be patient.

To abort all procsses = kill -9 `ps -ef | grep FND | awk `{print $2}’`

Status of each concurrent requests are hosted at FND_Concurrent_requests & Fnd_concurrent_queues. Running is R and complegted is set as C in this tables. Read more…

All checks are done using aoljtest.jsp. The app front end connects to database suing $INST_TOP/fnd/secure/DBC- Database connector file

Hierarchy : One off Patch -> Mini pack -> Family pack & Finally Maintenance Pack

Maintenance pack : Upgrade from one version to another . 12.0.2 -> 12.0.4

Family pack – Fixing the functional modules.FIN.B – Latest family pack for release 12 . FIN is financial B where B denotes the version -. For 11i HRPF.K – K is level of Module.

One off – One off issue

Mini Pack : Small functionality

Enhance functionality like VAT change. – One off else MINI pack. Apps patch is done by adpatch utility. Also look into readme file. “adpatch” is like opatch so can be copied from CD.

Read more…

[applmgr@ebs scripts]$ pwd
/oracle/u01/VIS/inst/apps/VIS_ebs/admin/scripts

[applmgr@ebs scripts]$ adapcctl.sh stop

You are running adapcctl.sh version 120.6.12000000.2

Stopping OPMN managed Oracle HTTP Server (OHS) instance …

adapcctl.sh: exiting with status 0

adapcctl.sh: check the logfile /oracle/u01/VIS/inst/apps/VIS_ebs/logs/appl/admin/log/adapcctl.txt for more information …

[applmgr@ebs scripts]$ adapcctl.sh status

You are running adapcctl.sh version 120.6.12000000.2 Read more…

Backup Types

There are 5 backup types. All these backups can be carried out online. databases. Offline backups require SQLSERVER be stopped. Offline backups are useful for the master, msdb and model databases

1. Full : This takes a complete copy of the database and logs.

2. Differential : This takes a copy of all changed pages since the last full backup (for database and logs.)

3. Transaction Log : This takes a copy of the transactions logs and clears its contents on completion.

4. File or File group : This copies only the specified file or group.

5. File differential : This takes a copy of all changed pages in file since the last file backup. In situations when the backup is too big for a backup window. Read more…

Maintenance Plans are only displayed to those users who are connected to the SQL Server using Windows Authentication Mode.

I will use AdventureWorksDW database which uses Simple Recovery Model and hence transactional log backups is not possible.

We first need to change the Recovery Model of AdventureWorks database to Full using GUI/TSQL.

Use master
GO

ALTER DATABASE AdventureWorks
SET RECOVERY FULL
GO

1. Connect to SQL Server 2008 Instance using SQL Server Management Studio.

2. In the Object Explorer, Click on Management and right click Maintenance Plans to open up the popup windows. In the popup windows you need to select Maintenance Plan Wizard.

In the Select Maintenance Tasks screen you need to choose Back up Databases (Full), Backup Databases (Differential) and Back up Database (Transactional Log) as shown in the below snippet and then click Next to continue with the wizard.

This will pop up a screen to Select a database you want to backup.

Then you need to select the option Create a backup file for every database, next select the checkbox, Create a sub-directory for each database checkbox and then provide the folder location where you want the databases backups to be stored along with the file extension as “diff”. If you are interested in verifying backup integrity then you can select Verify backup integrity option.

Click on “Change” to schedulethe the job & timing

Define where you want to store log files on the server.

In Complete the Wizard screen you could see the summary of all the options which you have selected so far in the maintenance plan wizard,

In the Maintenance Plan Wizard Progress screen you need to make sure that all the tasks have completed successfully and then click Close to complete the wizard.

If you want to run the backups manually just navigate to SQL Serevr Agent -> Job Activity Monitor . Right click on the job and select “Start Job”

I can now see a successful backup fie at “D:\SQL_TRAN_LOG\backups\AdventureWorksDW”

EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

Maks sure AGENT_HOME is set and EMCTL is not running from ORACLE_DATABASE_HOME than AGENT_HOME

[oracle@NODE1 bin]$ ./emctl upload agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
—————————————————————
EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet. If this issue persists check trace files for ping to OMS related errors.

[oracle@NODE1 bin]$ ./emctl stop agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Stopping agent … stopped.

[oracle@NODE1 bin]$ ./emctl unsecure agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Checking Agent for HTTP…   Done.
Agent is already stopped…   Done.
Unsecuring agent…   Started.
Agent is now unsecured…   Done.
Unsecuring agent…   Ended.

[oracle@NODE1 bin]$ ./emctl secure agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Agent is already stopped…   Done.
Securing agent…   Started.
Enter Agent Registration Password :
Securing agent…   Successful.

[oracle@NODE1 bin]$ ./emctl clearstate agent

[oracle@NODE1 bin]$ ./emctl start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting agent …….. started.

[oracle@NODE1 bin]$ ./emctl upload agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
—————————————————————
EMD upload completed successfully

Prerequisite :

• 2 separate SQL database instances at 2 different physical boxes – T-B0399254ED744 & TVLX4686.
• A share accessible to Primary and Secondary Server for log shipping

Here I am going to log replicate AdventureWorks database from T-B0399254ED744 to TVLX4686

Read more…

This morning I installed a new instance of SQL server and while connecting remotely it sent me an error message.

Error Has Occurred While Establishing A Connection To SQL Server 2005 Which Does Not Allow Local and Remote Connections

ITLE: Connect to Server—————————– Cannot connect to .——————————
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 233)

Solution is here : http://support.microsoft.com/kb/914277

• Please check firewall and make sure it is disabled else create an exception
• Enable the SQL Server Browser Service
• Enable remote connections for each instance of SQL Server 2005 that you want to connect to from a remote computer.

To do this, follow these steps:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.

2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.

3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.

Note Click OK when you receive the following message:
Changes to Connection Settings will not take effect until you restart the Database Engine service.

4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

• Failover Clustering
• Log Shipping
• Database Mirroring

Failover Clustering : Used for entire SQL server Instance , Hardware Solution based on MSCS

Shared disks configured between MSCS cluster and automatic failover Will be achieved if one of the node goes down

Database Mirroring :: High availability for Database, Software solution based on HOT standby (New 2005)

1. Principle database is applied at Mirror database almost real time.
2. There is one t one relationship and means there would be only one HOT standby for any database.
3. The mirrored database is in recovery mode and never available until failed over
4. A witness box could be used to carry automatic failover when needed
5. Will only work with FULL recovery Model
6. Could be configured into 3 different modes- High Availability (Sync operation with Witness server ), High Protection (Sync Operation with NO Witness server ) & High Performance (ASync operation with NO Witness server )

Log Shipping : High availability for Database, Software solution based on WARM standby (Old 2000)

1. Secondary database is not sync with primary . The primary database doesn’t send the transaction logs directly to the Secondary. The moving of logs is done by the SQL agent using a network share. Primary server has a backup Job which backs up the transaction logs to a network share while Secondary server have Job to apply them at destination.
2. The warm standby can have one to one else one to many relationship with Primary database.
3. The Warm standby is always available in read only mode
4. A monitor server could be used to setup all jobs needed between Primary and Secondary server. We Can omit the Monitor server and push jobs into Primary & secondary if needed.
5. Supports Manuel Failover only
6. Can work with FULL else BULK recovery Model

Please navigate to Management ->Activity Monitor -> Double Click

The architecture of the AWR is quite simple. The MMON background process polls the x$ fixed tables from the SGA region and stores them in the AWR tables. From there, the performance data is instantly available for analysis. The Enterprise Manager can be used for graphical data display. Alternatively, the Automatic Database Diagnostic Monitor (ADDM) can be used for automated tuning analysis, or SQL*Plus can be used if customized Oracle tuning size reports are desired.

AWR interval could be changed using API :
execute dbms_workload_repository.modify_snapshot_settings (
interval => 60,
retention => 43200);
Interval : How often snaps are taken
Retention: Duration in Minutes

Values updated at “dba_hist_wr_control”

AWR Reports

AWR reports are very similar to STATSPACK reports. They typically show:
SQL> @?/rdbms/admin/awrrpt.sql

A nice feature is that an HTML version of the report can be generated. Just follow the prompts. SELECT_ANY_DICTIONARY privilege is required to run a report.

Managing Snapshots
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
retention_period => 0
interval => 0

AWR Views

DBA_HIST_SNAPSHOT # show all snapshots
DBA_HIST_WR_CONTROL # show AWR settings