Manually corrupting the data block in Linux and recovering it using BLOCKRECOVER command of RMAN

Manually corrupting the data block in Linux and recovering it using BLOCKRECOVER command of RMAN
Sometimes, in order to test the RMAN’s  BLOCKRECOVER command, we need to corrupt the specific data block and recover it for testing purpose.
To do it in Linux, use dd command. In the following example, let’s create a table and corrupt it manually
 (Don’t try it on the production database  or you’ll be fired work from the job )

SQL> create table manu (id number ,name char(15));
Table created.
SQL> insert into manu values(1,'tanu');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from manu;
        ID   NAME
———-------
         1   tanu

SQL> select header_blockfrom dba_segments where segment_name=’MANU’;
HEADER_BLOCK
————
          61201
[oracle@localhost ~]$ dd of=/u01/app/oradata/orcl/system01.dbf bs=8192 conv=notrunc seek=61202 << EOF
> testing corruption
> EOF
0+1 records in
0+1 records out

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.

SQL> select * from manu;
select * from manu
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 61202)
ORA-01110: data file 1: ‘/u01/app/oradata/orcl/system01.dbf’
SQL>

Now connect to the RMAN and recover the data block (not the whole datafile or database) using BLOCKRECOVER command as follows:
SQL> ! rman target /
RMAN>
RMAN>blockrecover datafile 1 block 61202;
Starting blockrecover at 15-OCT-11
<… underprocess … >
<… underprocess … >
Finished blockrecover at 15-OCT-11
RMAN> exit

Connect to SQL*Plus and query the table:SQL> select * from manu;
        ID NAME
———--------
         1     tanu



ALL THE BEST TO ALL...... :)

ORA-00600: internal error code, arguments: [kcratr1_lastbwr]

Some time we encounter this error while starting the database.
Below is the solution for recovering through this error.

Error:

SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size  83888372 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr1_lastbwr], [], [], [], [],[], [], []

Cause:

Oracle is unable to perform instance recover but it works when is invoked manually.

Solution:

SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 83888372 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> recover database

Media recovery complete.
SQL> alter database open;
Database altered.

All The Best ............

Database Startup fails with ORA-00444, ORA-07446

Problem Description
When I start my database my instance fails with ORA-00444 and ORA-07446 as follows.


SQL> startup
ORA-00444: background process "MMAN" failed while starting
ORA-07446: sdnfy: bad value '' for parameter.


Cause of The Problem
Database startup event is logged into alert log file. And alert log file is defined by the background_dump_dest parameter inside the initialization parameter. While starting up the instance if instance fail to write into the alert log file then instance crushes and fail to startup.

Solution of The Problem
There may be different scenario whenever oracle fails to write alert log contents. Suppose the partition reside alert log file is full. In that case delete/move unnecessary file from the alert log partition directory.

There may be the case the the directory defined by the background_dump_dest does not exist in the OS.

In both case you may also like to change the background_dump_dest location inside the pfile.

If you have spfile then,
1.create pfile='/u01/abc.ora' from spfile;
2.edit the pfile /u01/abc.ora
3.Modify the background_dump_dest to a valid location and in a patition where there is sufficient space available.
4.start the database isntance.
startup pfile=' /u01/abc.ora';
5.create spfile again.
create spfile from pfile= '/u01/abc.ora';

DIM-00014: Cannot open the Windows NT Service Control Manager. O/S-Error: (OS 5) Access is denied.

This error is common in Windows Vista and windows 7 .
Its means that the user from which you are using oradim has not sufficient privileges .But most of the users complaint that they are performing this task from Administrator user or they have only one account in windows and its administrator ,but still they are facing this problem .
So the solution is very simple ,
just go to start wirte cmd ,cmd icon appears ,right click on it ....
Then click on "Run as administrator" and then create your required service .


oradim -new -sid myservice ;

Instance Memory Structures

Instance Memory Structures:
The System Global Area is a shared memory region that conatains data and control information for
one oracle instance. Oracle allocates the SGA when instance is starts and deallocates it when
instance is shutdown. Each instance has its own  SGA. SGA is divided into serveral type of memory
structure:
1.  Database buffer : store the most recently used blocks of data. The buffer cache contains
modified as well as unmodified blocks.becouse the most recently used data is kept in memory ,less
disk I/O is necessary and performance is improved.
The buffers in the cache are organized in two lists: the write list and the least recently used (LRU)
list. The write list holds dirty buffers, which contain data that has been modified but has not yet
been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have
not yet been moved to the write list. Free buffers do not contain any useful data and are available
for use. Pinned buffers are currently being accessed.
2. The Redo log buffer: stores redo entries  a lot of changes made to the database . redo log buffer is
used if database recovery is necessary . the size of the redo log is static.
3. Shared pool : contains shared memory constructs ,suchas shared SQL areas which is required to
process every unique SQL statement submitted to a database and contains information  such as the
parse tree and execution plan for the corresponding statement and used by multiple applications that
issue the same statement leaving more shared memory for other uses.
The library cache: includes the shared SQL areas, private SQL areas (in the case of a shared server
configuration), PL/SQL procedures and packages, and control structures such as locks and library
cache handles
The data dictionary :is a collection of database tables and views containing reference information
about the database, its structures, and its users. Oracle accesses the data dictionary frequently
during SQL statement parsing. This access is essential to the continuing operation of Oracle.
4. Large Pool: The database administrator can configure an optional memory area called the large pool
to provide large memory allocations for:
Session memory for the shared server and the Oracle XA interface (used where transactions
interact with more than one database)
I/O server processes
Oracle backup and restore operations
By allocating session memory from the large pool for shared server, Oracle XA, or parallel query
buffers, Oracle can use the shared pool primarily for caching shared SQL and avoid the performance
overhead caused by shrinking the shared SQL cache.
In addition, the memory for Oracle backup and restore operations, for I/O server processes, and for
parallel buffers is allocated in buffers of a few hundred kilobytes. The large pool is better able to
satisfy such large memory requests than the shared pool.
5. Java Pool memory : is used in server memory for all session-specific Java code and data within the
JVM. Java pool memory is used in different ways, depending on what mode the Oracle server is
running in.
6. Streams:Oracle Streams enables information sharing. Using Oracle Streams, you can share
data and events in a stream. The stream can propagate information within a
database or from one database to another. The stream routes specified information
to specified destinations. The result is a feature that provides greater functionality
and flexibility than traditional solutions for capturing and managing events, and
sharing the events with other databases and applications. Streams provides the
capabilities needed to build and operate distributed enterprises and applications,
data warehouses, and high availability solutions. You can use all of the capabilities
of Oracle Streams at the same time. If your needs change, then you can implement a
new capability of Streams without sacrificing existing capabilities.

ORACLE DATABASE ARCHITECTURE

ORACLE DATABASE ARCHITECTURE:
An oracle database which is a collection of data treated as a unit is to store and retrieve related information and is solving the problem of information management.
A Server: reliably manages a large amount of data in a multiuser environment so that many users can
concurrently access the same data and database server prevents unauthorized access and provides
efficient solution for failure recovery.
oracle database is the first database designed for enterprise grid computing the most flexible and
cost effective way to manage information and application.
The database has logical and physical structures because the physical and logical structures are
separate , the physical storage of data can be managed without affecting the access to logical
storage structures.
                             
Overview of oracle Grid architecture :
The oracle grid architecture pools large numbers of servers, storage and networks into a flexible,on
demand computing resource for enterprise computing needs.The grid computing infrastructure
continually analyzes demand for resources and adjusts supply accordingly.
Grid computing uses sophisticated(intricate) workload management that makes it possible for
applications to share resources across many servers.data processing capacity can be added or
removed on demand,resources within a location can be dynamically provisioned. Web services can
quicakly integrate application to create new buiness processes.

grid computing offers high performance and scalability ,because all computing resources can be
flexibly allocated applications as needed.
Oracle Database enables enterprise grid computing in the following ways:
Performance and scalability with low cost hardware clusters, like Itanium and Linux.
Reliability: Continuous availability of data and applications
Security and privacy: security features that lets you share enterprise grid resources with confidence
that privacy is maintained
Self-management: Oracle infrastructure automates many functions so that a single administrator can
manage hundreds of servers.
Distributed computing: Oracle has advanced integration features that allow applications and data to
run anywhere in the network
Difference between a cluster and a grid: Clustering is one technology used to create a grid
infrastructure. Simple clusters have static resources for specific applications by specific owners.
Grids, which can consist of multiple clusters, are dynamic resource pools shareable among many
different applications and users. A grid does not assume that all servers in the grid are running the
same set of applications. Applications can be scheduled and migrated across servers in the grid. Grids
share resources from and among independent system owners.

SQL Server Reporting Services

SQL Server Reporting Services :-

 is a comprehensive, server-based solution that enables the creation, management, and delivery of both traditional, paper-oriented reports and interactive, Web-based reports. An integrated part of the Microsoft Business Intelligence framework, Reporting Services combines the data management capabilities of SQL Server and Microsoft Windows Server with familiar and powerful Microsoft Office System applications to deliver real-time information to support daily operations and drive decisions.

Microsoft SQL Server 2005 Integration Services (SSIS)

Microsoft SQL Server 2005 Integration Services (SSIS) :-
                                                                                                                                                                 is a platform for building high performance data integration solutions, including the extraction, transformation, and loading (ETL) of packages for data warehousing. SSIS is the new name assigned to the component formerly branded as Data Transformation Services (DTS).

Data Transformation Services (DTS)

SQL Server 2000 Data Transformation Services (DTS) provides a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations.

ETL process and concepts :-
ETL stands for Extraction, Transformation and Loading. ETL is a process that involves the following tasks:

• Extracting :- data from source operational or archive systems which are the primary source of data for the data warehouse
• Transforming :- the data - which may involve cleaning, filtering, validating and applying business rules
• Loading :- the data into a data warehouse or any other database or application that houses data

Data Integration Requirements:-

In view of this diversity of data, business needs, and user requirements, the Information Technology department has specified the following set of data integration requirements:

• They must provide reliable and consistent historical and current data integrated from a variety of internal and external sources.
• To reduce lags in data acquisition, data from providers and vendors must be available via Web services or some other direct mechanism such as FTP.
• They need to cleanse and remove duplicate data and otherwise enforce data quality.
• Increasing global regulatory demands require that the company maintain clear audit trails. It is not enough to maintain reliable data; the data needs to be tracked and certified.

Index fragmentation in SQL Server 2005

When data is inserted, updated, or deleted in a database table, if indexes exist that operate on the table, they must be maintained to reflect the table data changes. The maintenance of these indexes will eventually cause the indexes to become less efficient. This inefficiency is typically due to the index becoming fragmented.

There are two types of fragmentation:
 external and internal. External fragmentation occurs when the logical ordering of the index does not match the physical ordering of the index. This causes SQL Server 2005 to perform extra work to return ordered results. For the most part, external fragmentation isn’t too big of a deal for specific searches that return very few records or queries that return result sets that do not need to be ordered.


Internal fragmentation occurs when there is too much free space in the index pages. Typically, some free space is desirable, especially when the index is created or rebuilt. You can specify the Fill Factor setting when the index is created or rebuilt to indicate a percentage of how full the index pages are when created. If the index pages are too fragmented, it will cause queries to take longer (because of the extra reads required to find data) and cause your indexes to grow larger than necessary. If no space is available in the index data pages, data changes (primarily inserts) will cause page splits, which also require additional system resources to perform.

Difference between a cluster and a grid....

Clustering is one technology used to create a grid infrastructure. Simple clusters have static resources for specific applications by specific owners. Grids, which can consist of multiple clusters, are dynamic resource pools shareable among many different applications and users. A grid does not assume that all servers in the grid are running the same set of applications. Applications can be scheduled and migrated across servers in the grid. Grids share resources from and among independent system owners.

This article walks you through how to delete multiple duplicate rows..

CREATE DATAVASE MANOJ

--------

USE MANOJ

------

CREATE TABLE AKKI

(ID INT,

FNAME VARCHAR(50),

LNAME VARCHAR(50)

)

------

insert into AKKI select 1,'Jen','Ambelang'

insert into AKKI select 11,'Jiong','Hong'

insert into AKKI select 25,'Sandra','Mator'

insert into AKKI select 35,'Chun','Chang'

insert into AKKI select 21,'Yuki','Fukushima'

insert into AKKI select 1,'Jen','Ambelang'

insert into AKKI select 1,'Jen','Ambelang'

insert into AKKI select 25,'Sandra','Mator'

insert into AKKI select 25,'Sandra','Mator'

--------

SELECT * FROM AKKI ORDER BY ID

---------

The first step in deleting duplicate rows is to generate a unique row id for the entire table. This can be done using the Row_Number() function.

 

SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,

ID,LNAME,FNAME FROM AKKI

Step 2

The second step in deleting duplicate rows is to generate unique row ids for every group.

Now, by using a co-related sub-query we can produce unique row ids for each group.

SELECT ROW,GROUPROW= CASE WHEN ID=ID

THEN

(

SELECT COUNT(*) FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY ID

) AS ROW,ID,LNAME,FNAME FROM AKKI

) AS A

WHERE A.ID=B.ID AND A.ROW<B.ROW)+1

END ,ID,FNAME,LNAME FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,

ID,LNAME,FNAME FROM AKKI

)AS B

---------
--------By using a co-related sub-query we genrate unique row id for each row--- row grouprow id fname lname
1 1 1 Jen Ambelang
2 2 1 Jen Ambelang
3 3 1 Jen Ambelang
4 1 11 Jiong Hong
5 1 21 Yuki Fukushima
6 1 25 Sandra Mator
7 2 25 Sandra Mator
8 3 25 Sandra Mator
9 1 35 Chun Chang

 

Step 3

The last step in deleting duplicate rows is to use the common table expression, as shown below

WITH DUPLICATE(ROW,GROUPROW,ID,FNAME,LNAME)

AS

(

SELECT ROW,GROUPROW= CASE WHEN ID=ID

THEN

(SELECT COUNT(*) FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,

ID,LNAME,FNAME FROM AKKI

) AS A WHERE A.ID=B.ID AND

A.ROW<B.ROW)+1 END,ID,FNAME,LNAME FROM (SELECT ROW_NUMBER() OVER (ORDER

BY ID) AS ROW,

ID,LNAME,FNAME FROM AKKI

)AS B

)

DELETE FROM DUPLICATE WHERE GROUPROW<>1
---(DELETE FROM DUPLICATE WHERE GROUPROW<>1--(to remove the duplicate records))

--------

VERIFY THE OUTPUT

--------

SELECT * FROM AKKI
----------
select * from AKKI-----GROUPROW IS ALIAS WHICH USE IN THE SELECT QUERY
ID FNAME LNAME
1 Jen Ambelang
11 Jiong Hong
25 Sandra Mator
35 Chun Chang
21 Yuki Fukushima

Step 1

what is the oracle instance?

Instance is a combination of memory structure and process structure. Memory structure is SGA and Process structure is background processes.

SGA - System or Shared Global Area.

Components of SGA:-

DBBC - Database Buffer Cache

SP - Shared Pool. It is futher divided into Library Cache(LC) and Data Dictionary Cache(DDC) or Row Cache.

RLB - Redolog Buffer

Background Process:-

1. Mandatory Processes-

a. SMON - System Monitor

b. PMON - Process Monitor

c. DBWR - Database writer

d. LGWR - Log Writer

e. CKPT - Check point

f. RECO - Recoverer

2. Optional Process:-

a. ARCN

b. RBAC

c. MMAN

d. MMON

e. MMNL

sharepoint - service not available

resolved the issue by restarting the IIS service under the
administrator account default for the service. followed these methods
for answer:

CAUSE
This issue may occur if the application pool for the virtual server is
configured incorrectly in Microsoft Internet Information Services
(IIS) 6.0. This issue may occur if one or more of the following
conditions are true: * The application pool is not running.
* The application pool account uses an incorrect password.
* The application pool account is not a member of both the IIS_WPG
group and the STS_WPG group on the server.

RESOLUTION
To resolve this problem, follow these steps: 1. Verify that the
application pool is configured for the virtual server. The default
application pool is MSSharePointPortalAppPool.

Follow these steps to determine the application pool that the virtual
server is using: a. Click Start, point to Administrative Tools, and
then click Internet Information Services (IIS) Manager.
b. Expand ServerName, expand Web Sites, right-click the virtual
server, and then click Properties.
c. Click the Home Directory tab.

The application pool that is configured for the virtual server is
listed in the Application pool box.
d. Click OK.

2. Verify that the password for the application pool account is
correct. IIS does not automatically poll password changes in Active
Directory directory service. If the application pool account is a
domain account, and the password expires, you may receive the error
message that is described in the "Symptoms" section of this article
after a new password is specified for the account.

Follow these steps to verify that the password for the application
pool account is correct: a. In Internet Information Services (IIS)
Manager, expand Application Pools.
b. Right-click the application pool that is configured for the
virtual server (for example, right-click MSSharePointPortalAppPool),
and then click Properties.
c. Click the Identity tab.
d. In the Password box, type the password of the application pool
account that is listed in the User name box, and then click OK.
e. In the Confirm Password dialog box, type the password again, and
then click OK.

3. Verify that the application pool account is a member of both the
IIS_WPG group and the STS_WPG group on the server:

Use one of the following methods as appropriate to your situation:a.
If Windows SharePoint Services 2.0 is installed on a member server: 1.
Click Start, point to Administrative Tools, and then click Computer
Management.
2. Expand Local Users and Groups, and then expand Users.
3. Right-click the account that is used by the application pool for
the virtual server, and then click Properties.
4. Click the Member of tab.

Verify that both IIS_WPG and STS_WPG appear in the Member of list. If
one or both groups are not listed, add the IIS_WPG group, the STS_WPG
group, or both groups (as appropriate) to the list.

b. If Windows SharePoint Services 2.0 is installed on a domain
controller: 1. Start Active Directory Users and Computers.
2. Expand Users.
3. Right-click the account that is used by the application pool for
the virtual server, and then click Properties.
4. Click the Member of tab.

Verify that both IIS_WPG and STS_WPG appear in the Member of list. If
one or both groups are not listed, add the IIS_WPG group, the STS_WPG
group, or both groups (as appropriate) to the list.


4. Restart IIS to recycle the application pools: a. In Internet
Information Services (IIS) Manager, right-click ServerName, point to
All Tasks, and then click Restart IIS.
b. Click Restart Internet Information Services on ServerName, and
then click OK

.