One run of following procedure incerased log fie to 8GB and datafile to 6GB

use subscriber_A
go

if exists (select * from sysobjects where name = 'Load_Data')
drop table Load_Data
go
create table Load_Data (
x int not null,
y char(896) not null default (''),
z char(120) not null default('')
)
go
insert Load_Data (x)
select r
from
(
select row_number() over (order by (select 1)) r
from master..spt_values a, master..spt_values b
) p
where r <= 4000000
go
create clustered index ix_x on Load_Data (x, y)
with fillfactor=51
go

[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

Checking status of OPMN managed Oracle HTTP Server (OHS) instance ...

Processes in Instance: VIS_ebs.ebs.oracledbasupport.co.uk
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
OC4J | oafm | 31759 | Alive
OC4J | forms | 31689 | Alive
OC4J | oacore | 31619 | Alive
HTTP_Server | HTTP_Server | N/A | Down

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 start

You are running adapcctl.sh version 120.6.12000000.2

Starting 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

Checking status of OPMN managed Oracle HTTP Server (OHS) instance ...

Processes in Instance: VIS_ebs.ebs.oracledbasupport.co.uk
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
OC4J | oafm | 31759 | Alive
OC4J | forms | 31689 | Alive
OC4J | oacore | 31619 | Alive
HTTP_Server | HTTP_Server | 3505 | Alive

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 logs]$ cd $LOG_HOME
[applmgr@ebs opmn]$ pwd
/oracle/u01/VIS/inst/apps/VIS_ebs/logs/ora/10.1.3/opmn

[applmgr@ebs opmn]$ ls -l
total 48
drwxrwxr-x 2 applmgr applmgr 4096 Jun 13 11:23 forms_default_group_1
-rw-r--r-- 1 applmgr applmgr 1021 Jun 27 15:23 HTTP_Server~1
drwxrwxr-x 2 applmgr applmgr 4096 Jun 13 11:22 oacore_default_group_1

If any errors please look at file /oracle/u01/VIS/inst/apps/VIS_ebs/logs/ora/10.1.3/opmn/HTTP_Server~1

To look at Status and Error of FORMS

[applmgr@ebs opmn]$ cd forms_default_group_1

[applmgr@ebs forms_default_group_1]$ pwd
/oracle/u01/VIS/inst/apps/VIS_ebs/logs/ora/10.1.3/opmn/forms_default_group_1

[applmgr@ebs forms_default_group_1]$ ls -l
total 8
-rw-rw-r-- 1 applmgr applmgr 645 Jun 27 10:17 formsstd.err
-rw-rw-r-- 1 applmgr applmgr 1002 Jun 27 11:08 formsstd.out

Concurrent Manager log files
[applmgr@ebs log]$ pwd
/oracle/u01/VIS/inst/apps/VIS_ebs/logs/appl/conc/log
XDP - Workflow Notification mailer

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...

Mirroring need 2 separate instances so please arrange 2 SQL server databases physically located at different servers.

If you try and locate both databases at same instance/machine you will see error "The principal and mirror server instances cannot be the same instance of SQL Server. Select another instance as the mirror server instance."

Let's connect to the Primary database and start the process

Let's carry a transaction log backup as well as we will need it for Point in Time recovery

Please make sure you select the option 2 at "Recovery State"

Once a restore is successful , you will see a database "AdventureWorksDWStandby" in "Restoring" State.

if you click on AdventureWorksDWStandby , you will see error like one below.

before we go any further we need to configure the Witness Server so click on the "Configure Security"

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"

Reason : THE AGENT_HOME was not set and EMCTL was running from ORACLE_DATABASE_HOME than the AGENT_HOME

(oracle) db10gr2 OEMAGENT:~> emctl upload agent
TZ set to GB-Eire
Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

I used my usual method to clerstate agent and uplaod it again which always workded , but not Today

(oracle) db10gr2 OEMAGENT:~> emctl stop agent
TZ set to GB-Eire
Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Stopping agent ...e stopped.

Delete all files in $AGENT_HOME/sysman/emd/upload and $AGENT_HOME/sysman/emd/state

(oracle) db10gr2 OEMAGENT:~> emctl clearstate agent
TZ set to GB-Eire
Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
EMD clearstate completed successfully
(oracle) db10gr2 OEMAGENT:~> emctl start agent
TZ set to GB-Eire
Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting agent .... started.
(oracle) db10gr2 OEMAGENT:~> emctl upload agent
TZ set to GB-Eire
Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

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 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 (retention

Objectives

The objectives of this document are to:

Record the setup and configuration of the 9i Oracle Standalone environment.

Read more...

Table of Contents

1.1 Objectives

1.2 Scope

2 System Configuration

2.1 Architecture

3 System Configuration

3.1 Machine Configuration

3.2 External/Shared Storage

4 Oracle Pre-Installation tasks

4.1 Redhat Pre-Requisite

4.2 Copy Oracle 10.2.0.1 software onto server

4.3 Unpack Files

4.4 Download Patches

4.5 Check kernel and update rpm files

4.6 Creating Required Operating System Groups and Users

4.7 Oracle required directory creation

4.8 Set Kernel Parameters

4.9 Create Oracle Profile

5 Oracle Software Configuration

5.1 Directory Structure

5.2 Download ASM packages

5.3 ASM package install

5.4 Configuring and Loading ASM

5.5 Creating ASM Disks

5.6 Installation

5.6.1 Database Only Installation

5.6.2 Installing the Listener

5.6.3 DBCA : Creating an ASM /Database Instance

Read more...

While using multimaster replication often you would be bombarded with errors like “No data found”, “Unique Key Violated” etc .

These are purely data mismatch errors and a user intervention needed to resolve them.

I often come across these errors when we run applications at multiple sites which are part of same replication group/tables when they are designed to run at one replication site at any given time.

You can get list of these errors at DEFERROER view with transaction ids but there is no easy way to locate the parameters,columns and tables on which these errors occurred.

Thankfully there is a solution if you have EM installed.

Read more...

Operating Systems Requirements

Kernel Requirements enter: $ uname –r

Required Packages

Read more...