Flashaback Database 10g

Flashback Oracle10gR1 10.1.0.2.0:

In Oracle 10g, the flashback functionality has been greatly extended.

Flashback Database:

Flashback Database is faster than traditional point-in-time recovery. Traditional recovery uses redo log files and backups. Flashback Database is implemented using a new type of log file called Flashback Database logs. The Oracle database server periodically logs before images of data blocks in the Flashback Database logs. The data block images are used to quickly back out changes to the database during Flashback Database.

Flashback Database reduces the time required to recover the database to a previous point. The time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database.

RVWR Background Process:

When Flashback Database is enabled, a new RVWR background process is started. This process is similar to the LGWR (log writer) process. The new process writes Flashback Database data to the Flashback Database logs.



Figure 12.1 RVWR Background process and Flashback Database Logs Figure 12.1 RVWR Background process and Flashback.


Using Flashback Drop and Managing the Recycle Bin:

A user can view his objects in the recycle bin using the following statement:

SELECT * FROM RECYCLEBIN;

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.

When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names.
The renaming convention is as follows:BIN$unique_id$version

where:unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databasesversion is a version number assigned by the database
You can query objects that are in the recycle bin, just as you can query other objects. However, you must specify the name of the object as it is identified in the recycle bin. For example:

Oracle Flashback Table: Returning Individual Tables to Past States:

SQL> create table hr ( name varchar2(20));
Table created.
SQL> drop table hr;
Table dropped.
SQL> select * from recyclebin;
OBJECT_NAME ORIGINAL_NAME
OPERATION
------------------------------ --------------------------------
---------
TYPE TS_NAME CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME DROPSCN PARTITION_NAME CAN CAN
------------------- ---------- -------------------------------- --- ---
RELATED BASE_OBJECT PURGE_OBJECT SPACE
---------- ----------- ------------ ----------
BIN$74ANPU11QhWw15ah+JM2gQ==$0 HR
DROPTABLE USERS 2006-11-16:17:56:252006-11-16:17:56:34
445402 YES YES
50424 50424 50424 8

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE
DROP TIME
---------------- ------------------------------ ------------ -------------------
HR BIN$74ANPU11QhWw15ah+JM2gQ==$0 TABLE
2006-11-16:17:56:34

SQL> select * from "BIN$74ANPU11QhWw15ah+JM2gQ==$0";
no rows selected

Restoring Tables from the Recycle Bin:

SQL> flashback table hr to before drop rename to scott;
Flashback complete.


You can directly drop table using "purge" cluase.
if use "purge" clause then object not goes in recyclebin. it will delete parmanently.

SQL> drop table scott purge;
Table dropped.

You can also "purge" recyclebin
SQL> show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME---------------- ------------------------------ ------------ -------------------
SCOTT BIN$QFb9kDMkRq2RpWJjjW9+ug==$0 TABLE 2006-11-16:18:14:26
SQL> purge recyclebin;
Recyclebin purged.
SQL> show recyclebin;

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

Flashback Query using Row Movement:

Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement.
You can enable row movement with the following SQL statement

SQL> conn hr/hrConnected.
SQL> create table big_table as select * from all_objects;
Table created.
SQL> alter table big_table enable row movement;
Table altered.
SQL> alter session set nls_date_format = 'hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------
18:43:49
SQL> select count(*) from big_table;
COUNT(*)
----------
46335
SQL> delete from big_table where rownum <= 445; 445 rows deleted.

SQL> commit;
Commit complete.

SQL> flashback table big_table to TIMESTAMP 2 to_timestamp ( '2006-11-16 18:44:00','YYYY-MM-DD HH24:MI:SS');
Flashback complete.
SQL> select count(*) from big_table;
COUNT(*)
----------
46335


Flashback Query:

you must choose an undo retention interval that is long enough to enable users to construct a snapshot of the database for the oldest version of the database that they are interested in.


SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter system set undo_retention = 3600;
System altered.
SQL> conn hr/hr
Connected.
SQL> alter session set nls_date_format = 'hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------
19:04:49
SQL> create table hrtest ( no number, name varchar2(20));
Table created.
SQL> begin
2 insert into hrtest values ( 1, 'aaa');
3 insert into hrtest values ( 2, 'bbb');
4 insert into hrtest values ( 3, 'ccc');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
--------
19:06:23
SQL> delete from hrtest where name = 'ccc';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from hrtest;
NO NAME
---------- --------------------
1 aaa
2 bbb

SQL> edWrote file afiedt.buf
1 select * from hrtest AS OF TIMESTAMP 2 to_timestamp ('2006-11-16 19:06:23','YYYY-MM-DD HH24:MI:SS')
3* where name = 'ccc'
4 /
NO NAME
---------- --------------------
3 ccc
SQL> insert into hrtest 2 ( select * from hrtest AS OF TIMESTAMP 3 to_timestamp ('2006-11-16 19:06:23','YYYY-MM-DD HH24:MI:SS')
4 where name = 'ccc');
1 row created.
SQL> select * from hrtest;
NO NAME
---------- --------------------
1 aaa
2 bbb
3 ccc

x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x
SQL> create table SCN_TEST (no number, name varchar2(20));

Table created.

SQL> insert into scn_test values (1,'a');

1 row created.

SQL> insert into scn_test values (2,'b');

1 row created.

SQL> insert into scn_test values (3,'c');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scn_test;

NO NAME
---------- --------------------
1 a
2 b
3 c

SQL> show parameter undo_retention

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 22719
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
15893109

SQL> delete from scn_test where no = 2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from scn_test AS OF SCN 15893109 where no = 2;

NO NAME
---------- --------------------
2 b

SQL> insert into scn_test ( select * from scn_test AS OF SCN 15893109
2 where no = 2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scn_test;

NO NAME
---------- --------------------
1 a
3 c
2 b


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

Using Flashback Version Query:

SQL> select * from hrtest;
NO NAME
---------- --------------------
1 aaa
2 bbb
3 ccc
SQL> begin
2 insert into hrtest values ( 4, 'ddd');
3 insert into hrtest values ( 5, 'eee');
4 commit;
5 end;
6 /
PL/SQL procedure successfully completed.

SQL> select sysdate from dual;
SYSDATE
--------
19:35:26
SQL> select sysdate from dual;
SYSDATE
--------
19:38:57
SQL> delete from hrtest where rownum <= 3;
3 rows deleted.
SQL> commit;
Commit complete.
SQL> begin
2 update hrtest set no = 0;
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select sysdate from dual;
SYSDATE
--------
19:48:42
SQL> select * from hrtest;
NO NAME
---------- --------------------
0 ddd
0 eee
SQL> ed
Wrote file afiedt.buf
1 SELECT versions_startscn, versions_starttime,
2 versions_endscn, versions_endtime,
3 versions_xid, versions_operation,
4 hrtest.*
5 FROM hrtest
6 VERSIONS BETWEEN TIMESTAMP
7 TO_TIMESTAMP('2006-11-16 19:35:00', 'YYYY-MM-DD HH24:MI:SS')
8 AND TO_TIMESTAMP('2006-11-16 19:48:00', 'YYYY-MM-DD HH24:MI:SS')
9* WHERE name in ('aaa','bbb','ccc');

SQL> column versions_starttime format a25
SQL> column versions_endtime format a25
SQL> /
VERSIONS_STARTSCN VERSIONS_STARTTIME
VERSIONS_ENDSCN
----------------- -------------------------
---------------
VERSIONS_ENDTIME VERSIONS_XID V NO NAME
------------------------- ---------------- - ---------- -------------------- 449718 16-NOV-06 07.39.10 PM
04000E0028000000 D 3 ccc
449718 16-NOV-06 07.39.10 PM
04000E0028000000 D 2 bbb
449718 16-NOV-06 07.39.10 PM
04000E0028000000 D 1 aaa
44971816-NOV-06 07.39.10 PM
1 aaa

44971816-NOV-06 07.39.10 PM
2 bbb

44971816-NOV-06 07.39.10 PM
3 ccc
6 rows selected.


The available pseudocolumn meanings are:

* VERSIONS_STARTSCN or VERSIONS_STARTTIME - Starting SCN and TIMESTAMP when row took on this value. The value of NULL is returned if the row was created before the lower bound SCN ot TIMESTAMP.
* VERSIONS_ENDSCN or VERSIONS_ENDTIME - Ending SCN and TIMESTAMP when row last contained this value. The value of NULL is returned if the value of the row is still current at the upper bound SCN ot TIMESTAMP.
* VERSIONS_XID - ID of the transaction that created the row in it's current state.
* VERSIONS_OPERATION - Operation performed by the transaction ((I)nsert, (U)pdate or (D)elete)

SELECT commit_timestamp , logon_user FROM FLASHBACK_TRANSACTION_QUERY
WHERE xid IN
(SELECT versions_xid FROM employee VERSIONS BETWEEN
TIMESTAMP TO_TIMESTAMP('2003-04-04 14:00:00', 'YYYY-MM-DD HH:MI:SS')
and TO_TIMESTAMP('2003-04-04 17:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN');


Flashback Transaction Query:

Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries. The VERSIONS_XID column values from a flashback version query can be used to query the FLASHBACK_TRANSACTION_QUERY view like:

SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('0600030021000000');


XID OPERATION START_SCN COMMIT_SCN
---------------- -------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------
0600030021000000 UPDATE 725208 725209
SCOTT
update "SCOTT"."FLASHBACK_VERSION_QUERY_TEST" set "DESCRIPTION" = 'ONE' where ROWID = 'AAAMP9AAEAAAA
AYAAA';

0600030021000000 BEGIN 725208 725209
SCOTT

XID OPERATION START_SCN COMMIT_SCN
---------------- -------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------



2 rows selected.



Flashback Database:

Flashback Database is introduce with 10gr1, It is alternative for PITR (Point In Time Recovery).

It is superb feature, we can also called "rewind button" for database. becuase through this option we can rewind database to any point of time.

1. Database must run in archivelog mode

2. connect with SYS user and shutdown database with normal

SQL> conn / as sysdba

Connected.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

3. startup database with MOUNT mode

SQL> startup mount

4. Enable FLASHBACK DATABASE

SQL> alter database flashback on;
Database altered.

4. Open the database with normal operation

SQL> alter database open;
Database altered.

5. Check flashback database option enable for the database

SQL> select flashback_on from v$database;
FLA

---

YES

NOTE: FLASHBACK LOGS created in Flash recovery area

Up to this stage we only ENABLE the flashback database, now we need to configure one more parameter for How far can we bring the database back in time?

for that we need to configure below parameter

db_flashback_retention_target

NOTE: Parameter is dynamic, and default value is 1440 minutes means 24 hrs, parameter value is set in MINUTES

We can set FLASHBACK database option for particular tablespace also.

SQL> alter tablespace TEST1 flashback on;

SQL> alter tablespace TEST1 flashback off;

Estimating flashback database storage reqirement

We can get this information through below query after enable flashback database option.

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

How Flashback Database is important for DBA? lets check with below example

Nive (DBA) working in ABC bank as an DBA, suddenly one application user called and told he was by mistake DROP wrong production table with PURGE option.

Database version is 10gr1, so we can easily recovered dropped table through FLASHBACK TABLE but the problem is here user dropped table with PURGE option so we can't recovered dropped table without perform Point In Time Recover option.

Nive is happy becuase FLASHBACK DATABASE option is ENABLE.

So Miss Nive done the following steps

1. shutdown the database

2. flashback database before table was dropped

3. open database with read only

4. export dropped table

5. shutdown and startup database with mount mode

6. perform recover database

7. open database with normal operation without resetlogs

SQL> select current_scn from v$database;
CURRENT_SCN

-----------

542151

SQL> drop table SCOTT.EMP purge;
Table dropped.

SQL> select current_scn from v$database;
CURRENT_SCN

-----------

542182

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 144964076 bytes

Database Buffers 25165824 bytes

Redo Buffers 1048576 bytes

Database mounted.

SQL> flashback database to scn 542151;
Flashback complete.

SQL> alter database open read only;
Database altered.

SQL> desc scott.emp

Name Null? Type

----------------------------------------- -------- ---------------------------
[output cut]

SQL> host exp system/oracle file=c:\emp.dmp tables=SCOTT.EMP
Export: Release 10.1.0.2.0 - Production on

Sun Jul 20 17:42:03 2008
Copyright (c) 1982, 2004, Oracle.

All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prodution

With the Partitioning, OLAP and Data Mining options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...

Current user changed to SCOTT. .

exporting table EMP 14 rows exported

Export terminated successfully without warnings.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 144964076 bytes

Database Buffers 25165824 bytes

Redo Buffers 1048576 bytes

Database mounted.

SQL> recover database

Media recovery complete.

SQL> alter database open;
Database altered.

SQL> desc scott.emp

ERROR:ORA-04043: object scott.emp does not exist

SQL> host imp system/oracle file=c:\emp.dmp fromuser=scott touser=scott
Import: Release 10.1.0.2.0 - Production on

Sun Jul 20 17:43:10 2008
Copyright (c) 1982, 2004, Oracle.

All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prodution

With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional pathimport done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set.

importing SCOTT's objects into SCOTT. .

importing table "EMP" 14 rows imported

About to enable constraints...

Import terminated successfully without warnings.

SQL> desc scott.emp

Name Null? Type ----------------------------------------- -------- ---------------------------
[output cut]

Finally Nive able to recover dropped table :)

We can flashback database through SCN or TIMESTAMP

How to get back dropped Tablespace using Flashback Database:


It needs to remember that if you drop your tablespace with including datafiles option then it is not possible to get your tablespace back with flashback database. While dropping if you use DROP TABLESPACE INCLUDING CONTENTS option then it is possible to get back tablespace.

Procedures with and Example:
---------------------------------
1)Create a Tablespace and Table inside it.

SQL> create tablespace test_restore datafile '/oradata2/test_restore01.dbf' size 5m;
Tablespace created.

SQL> create table test tablespace test_restore as select level a1 from dual connect by level <99;
Table created.

2)Note the SCN and Drop the Tablespace with including contents option.
---------------------------------------------------------------------------
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
938686

SQL> drop tablespace test_restore including contents;
Tablespace dropped.

3)Mount the database.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 104857640 bytes
Database Buffers 54525952 bytes
Redo Buffers 6369280 bytes
Database mounted.

4)Perform FlashBack.

SQL> flashback database to scn 938686;
flashback database to scn 938686
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5:
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005'

5)The datafile Become Unnamed. So rename it with original data file location.

SQL> alter database rename file '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005' to
2 '/oradata2/test_restore01.dbf';
Database altered.

6)Now perforem Flashback and Open the database with read only mode.

SQL> flashback database to scn 938686;
Flashback complete.

SQL> alter database open read only;
Database altered.

SQL> select count(*) from test;
COUNT(*)
----------
98

sql> alter database open resetlogs;


FLASHBACK USING RMAN:


Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Robert>cd\

C:\>net start oracleservicedba
The OracleServiceDBA service is starting............
The OracleServiceDBA service was started successfully.


C:\>set oracle_sid=dba

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 1 22:46:03 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> conn robo/robo
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE

SQL> select * from test;

NUM
----------
1
2
3

SQL> set time on;
22:46:58 SQL> select * from test;

NUM
----------
1
2
3

22:46:59 SQL> drop table test purge;

Table dropped.

22:47:10 SQL> ho rman target sys/sys

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Aug 1 22:48:50 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: DBA (DBID=1152934289)

RMAN> shutdown immediate;

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 289406976 bytes

Fixed Size 1248576 bytes
Variable Size 75498176 bytes
Database Buffers 205520896 bytes
Redo Buffers 7139328 bytes

RMAN> flashback database to time "to_timestamp('01-AUG-2009 22:46:58','DD-MON-YYYY HH24:MI:SS')";

Starting flashback at 01-AUG-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:07

Finished flashback at 01-AUG-09

RMAN> open resetlogs database;


-------->

RMAN> FLASHBACK DATABASE TO SCN 46963;

RMAN> FLASHBACK DATABASE TO SEQUENCE 5304;

RMAN> FLASHBACK DATABASE TO TIME (SYSDATE-1/24); ----> 1 Hour before.

RMAN> FLASHBACK DATABASE TO TIME (SYSDATE-10/(24*60)); ----> 10 minutes minus from sysdate and if it is +10 then it add 10 minutes.

If you want 10 seconds ago, use SYSDATE - (10 * (1 / 24 / 60 / 60)), because (1 / 24 / 60 / 60) is the number of days in a second.

RMAN> FLASHBACK DATABASE TO TIME timestamp('2002-11-05 14:00:00');

DMT and LMT

Difference between Dictionary managed tablespace (DMT) and Locally managed tablespace (LMT)

Introduction:
The CREATE TABLESPACE command has a new clause introduced in Oracle8i, the "extent management clause", that specifies how the extents of the tablespace are managed. This clause uses one of the following parameters:

- DICTIONARY:
Specifies that the tablespace is managed using dictionary tables. This is the default in Oracle8i.

- LOCAL:
Specifies that tablespace is locally managed. This is the default in Oracle9i. Exception for the SYSTEM tablespace

Locally Managed Tablespaces:
A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables (like sys.uet$, sys.fet$) in the data dictionary (except for special cases such as tablespace quota information).

When you create a locally managed tablespace, header bitmaps are created for each datafile. If more datafiles are added, new header bitmaps are created for each added file.

Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents. The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.

Dictionary Managed Tablespaces:
In DMT, to keep track of the free or used status of blocks, oracle uses data dictionry tables. When an extent is allocated or freed for reuse, free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes agianst these tables. As only one process can acquire the ST enque at a given time, this often lead to contention. These changes generate rollback information because they update tables (like sys.uet$, sys.fet$) in the data dictionary.

Advantages of Locally Managed Tablespaces(LMT) over Dictionary Managed Tablespaces(DMT):

1. Reduced recursive space management
2. Reduced contention on data dictionary tables
3. No rollback generated
4. No coalescing required

Converting DMT to LMT:
SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');
PL/SQL procedure successfully completed.

Converting LMT to DMT:
SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2');
PL/SQL procedure successfully completed.

Important Points:
1. LMTs can be created as
a) AUTOALLOCATE: specifies that the tablespace is system managed. Users cannot specify an extent size.
b) UNIFORM: specifies that the tablespace is managed with uniform extents of SIZE bytes. The default SIZE is 1 megabyte.
2. One cannot create a locally managed SYSTEM tablespace in 8i.
3. This is possible with in 9.2.0.X, where SYSTEM tablespace is created by DBCA as locally managed by default. With a locally managed SYSTEM tablespace, the rest of the tablespaces in such database have to be locally managed as well.
4. Locally managed temporary tablespaces can not be of type "permanent"

RAC FAQ

Oracle Real Application Clusters (RAC) FAQ - previously called Oracle Parallel Server (OPS):

What is RAC and how is it different from non RAC databases?

RAC stands for Real Application Clusters. It allows multiple nodes in a clustered system to mount and open a single database that resides on shared disk storage. Should a single system fail (node), the database service will still be available on the remaining nodes.

A non-RAC database is only available on a single system. If that system fails, the database service will be down (single point of failure).

Creating Real Application Clusters Data Dictionary Views with CATCLUST.SQL

Because you manually created your Real Application Clusters database, you must run the CATCLUST.SQL script to create Real Application Clusters-related views and tables. You must have SYSDBA privileges to run this script.

Can any application be deployed on RAC?

Most applications can be deployed on RAC without any modifications and still scale linearly (well, almost).

However, applications with 'hot' rows (the same row being accessed by processes on different nodes) will not work well. This is because data blocks will constantly be moved from one Oracle Instance to another. In such cases the application needs to be partitioned based on function or data to eliminate contention.

Do you need special hardware to run RAC?

RAC requires the following hardware components:

• A dedicated network interconnect - might be as simple as a fast network connection between nodes; and
• A shared disk subsystem.

Example systems that can be used with RAC:

• Windows Clusters
• Linux Clusters
• Unix Clusters like SUN PDB (Parallel DB).
• IBM z/OS in SYSPLEX
• HP Serviceguard extension for RAC

How many OCR and voting disks should one have?

For redundancy, one should have at lease two OCR disks and three voting disks (raw disk partitions). These disk partitions should be spread across different physical disks.

How does one convert a single instance database to RAC?

Oracle 10gR2 introduces a utility called rconfig (located in $ORACLE_HOME/bin) that will convert a single instance database to a RAC database.

$ cp $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC.xml racconv.xml

$ vi racconv.xml

$ rconfig racconv.xml

One can also use dbca and enterprise manager to convert the database to RAC mode.

For prior releases, follow these steps:

• Shut Down your Database:

SQL> CONNECT SYS AS SYSDBA

SQL> SHUTDOWN NORMAL

• Enable RAC - On Unix this is done by relinking the Oracle software.

• Make the software available on all computer systems that will run RAC. This can be done by copying the software to all systems or to a shared clustered file system.

• Each instance requires its own set of Redo Log Files (called a thread). Create additional log files:

SQL> CONNECT SYS AS SYSBDA

SQL> STARTUP EXCLUSIVE

SQL> ALTER DATABASE ADD LOGFILE THREAD 2

SQL>     GROUP G4 ('RAW_FILE1') SIZE 500k,

SQL>     GROUP G5 ('RAW_FILE2') SIZE 500k,

SQL>     GROUP G6 ('RAW_FILE3') SIZE 500k;

SQL> ALTER DATABASE ENABLE PUBLIC THREAD 2;

• Each instance requires its own set of Undo segments (rollback segments). To add undo segments for New Nodes:

UNDO_MANAGEMENT = auto

UNDO_TABLESPACE = undots2

• Edit the SPFILE/INIT.ORA files and number the instances 1, 2,...:

CLUSTER_DATABASE = TRUE           (PARALLEL_SERVER = TRUE prior to Oracle9i).  

INSTANCE_NUMBER = 1

THREAD = 1

UNDO_TABLESPACE = undots1          (or ROLLBACK_SEGMENTS if you use UNDO_MANAGEMENT=manual)

# Include %T for the thread in the LOG_ARCHIVE_FORMAT string.

# Set LM_PROCS to the number of nodes * PROCESSES

# etc....

• Create the dictionary views needed for RAC by running catclust.sql (previously called catparr.sql):

SQL> START ?/rdbms/admin/catclust.sql

• On all the computer systems, startup the instances:

SQL> CONNECT / as SYSDBA

SQL> STARTUP;

How does one stop and start RAC instances?

There are no difference between the way you start a normal database and RAC database, except that a RAC database needs to be started from multiple nodes. The CLUSTER_DATABASE=TRUE (PARALLEL_SERVER=TRUE) parameter needs to be set before a database can be started in cluster mode.

In Oracle 10g one can use the srvctl utility to start instances and listener across the cluster from a single node. Here are some examples:

$ srvctl status database -d RACDB

$ srvctl start database -d RACDB

$ srvctl start instance -d RACDB -i RACDB1

$ srvctl start instance -d RACDB -i RACDB2

$ srvctl stop database -d RACDB

$ srvctl start asm -n node2

Before Oracle 8.0, use the following command sequence from each node (using the old server manager):

SVRMGR> connect INTERNAL

SVRMGR> set retries 5

SVRMGR> startup parallel retry      .. or SVRMGR> startup shared

You can also use the SET INSTANCE instanceN command to switch between instances (if defined in TNSNAMES.ORA).

Can I test if a database is running in RAC mode?

Use the DBMS_UTILITY package to determine if a database is running in RAC mode or not. Example:

BEGIN

  IF dbms_utility.is_cluster_database THEN

      dbms_output.put_line('Running in SHARED/RAC mode.');

  ELSE

      dbms_output.put_line('Running in EXCLUSIVE mode.');

  END IF;

END;

/

For Oracle 8i and prior releases:

BEGIN

  IF dbms_utility.is_parallel_server THEN

      dbms_output.put_line('Running in SHARED/PARALLEL mode.');

  ELSE

      dbms_output.put_line('Running in EXCLUSIVE mode.');

  END IF;

END;

/

Another method is to look at the database parameters. For example, from SQL*Plus:

SQL> show parameter CLUSTER_DATABASE

If the value of CLUSTER_DATABASE is FALSE then database is not running in RAC Mode.

How can I keep track of active instances?

You can keep track of active RAC instances by executing one of the following queries:

SELECT * FROM SYS.V_$ACTIVE_INSTANCES;

SELECT * FROM SYS.V_$THREAD;

To list the active instances from PL/SQL, use DBMS_UTILITY.ACTIVE_INSTANCES().

How does one setup SQL*Net for RAC?

See Transparent Application Failover.

Can one see how connections are distributed across the nodes?

Select from gv$session. Some examples:

SELECT inst_id, count(*) "DB Sessions" FROM gv$session

 WHERE type = 'USER' GROUP BY inst_id;

With login time (hour):

SELECT inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24') "Hour when connected", count(*) "DB Sessions"

  FROM gv$session

 WHERE type = 'USER'

 GROUP BY inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24')

 ORDER BY inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24');

[edit] What is pinging and why is it so bad?

Starting with Oracle 9i, RAC can transfer blocks from one instance to another across the interconnect (cache fusion). This method is much faster than the old "pinging" method, where one instance had to write the block to disk before another instance could read it.

Oracle 8i and below:

Pinging is the process whereby one instance requests another to write a set of blocks from its SGA to disk so it can obtain it in exclusive mode. This method of moving data blocks from one instance's SGA to another is extremely slow. The challenge of tuning RAC/OPS is to minimize pinging activity.

Real Application Clusters

From Oracle FAQ

Jump to: navigation, search

Oracle Real Application Clusters (RAC) is a database clustering solution that allows more than one instance to mount and open an Oracle database. RAC can only be used on special clustered systems with shared disk storage and a private network interconnect.

A normal Oracle installation consists of a single Oracle instance that accesses a database on the same computer system. In contrast, RAC allows multiple instances on different computer systems (nodes in a cluster) to access the same database files simultaneously. Communication between instances is managed by the Distributed Lock Manager (DLM). To address the possibility of two or more instances attempting to modify the same information simultaneously, Oracle uses up to ten additional background processes, named LCK0 through LCK9, to lock the resources in use by these instances.

RAC is available with Oracle Enterprise Edition, and under certain conditions, with Standard Edition as well. These restrictions (for Standard Edition) include:

• Must use Oracle Clusterware (no third-party clusterware allowed);
• Must use ASM to store database files; and
• Can only use a max of 4 CPU sockets in the cluster (either 2 nodes with 2 CPUs each, or 4 nodes with 1 CPU each).

Contents [hide] 1 History 2 Benefits 3 RAC Storage Options 4 Oracle Clusterware 4.1 Virtual IP 4.2 Voting Disk 4.3 Cluster Registry 4.4 Clusterware processes 4.5 Stop and start 4.6 Starting and stopping resources 5 RAC parameters 6 GV$ views 7 Configure archiving 8 Configure Services 9 Also see 10 External links

[edit] History

RAC was previously called Oracle Parallel Server (OPS). Oracle re-branded OPS as RAC when they released Oracle 9i.

[edit] Benefits

Running Oracle in RAC mode can benefit you in the following ways:

• High availability - If some nodes fail, the remainder of the nodes will still be available for processing requests. Failover support is available from Oracle 8 with Oracle's Transparent Application Failover (TAF) technology and from Oracle 10g, server side load balancing.

• Speedup (increased transaction response time) - RAC normally adds some overhead. However, for some DSS applications one can achieve increased performance by running operations in parallel (mainly for DSS applications).

• Scale-up (increased transaction volume) - RAC can be used to provide increased application scalability (mainly used for OLTP applications).

[edit] RAC Storage Options

The database's data and control files are shared between the instances. However, each instance must have its own UNDO and REDO:

• UNDO: Each instance must have its own UNDO_TABLESPACE
• REDO: Each instance must have its own REDO LOG files (called a thread)

Shared files can be stored on:

• RAW volumes
• ASM
• Clustered file systems (like OCFS)

[edit] Oracle Clusterware

Oracle Clusterware (previously called Cluster Ready Services or CRS) provides Cluster Management Services and High Availability Services to Oracle and 3rd party software that wants to hook into it. For example, if you kill your LGWR process, CRS will detect the failure and automatically restart the database.

Oracle clusterware eliminates the need for 3rd party clusterware software like SUN Clusters, IBM HACMP and HP Omniguard. Oracle clusterware is provided at no additional cost with the 10g database.

Oracle clusterware needs to be installed on all nodes of the cluster before installing the database software. During installation, you will be prompted to configure a virtual IP, voting disk and cluster registry.

[edit] Virtual IP

IP address that will fail-over to another node in the cluster when a failure is detected. This will allow connected session to (almost) immediately fail-over to another node when a problem is experienced.

RAC requires one or more private interconnects, and two or more public network interfaces.

[edit] Voting Disk

A Voting Disk is a shared disk device or file used to determine node availability (establishes quorum) and resolve split-brain scenarios. All instances write to the voting disk (check in) to indicate that they are still active. This is required as instances may not always be able to communicate across the network with each other.

The voting disk must be stored in a raw partition or regular file in a clustered filesystem (like OCFS, but not on ASM!).

The voting disk, like the OCR, should be multiplexed and backed-up to protect them against media failures.

[edit] Cluster Registry

The Cluster Registry (OCR) is used to store cluster wide settings and status information. For example: node names, IP and VIP addresses, voting disk locations, node applications, database names, instance names, listener names, etc.

The Oracle Cluster Registry (OCR) is a binary file that is maintained by the CRS daemon. The OCR must be stored in a raw partition or regular file in a clustered filesystem (not on ASM!).

To see what is in the OCR, use the "ocrdump" command to dump it contents to a flat file.

[edit] Clusterware processes

When clusterware is started, the following process will be running:

crsd – Cluster Resource Services Daemon cssd – Cluster Synchronization Services Daemon evmd – Event Manager Daemon

Corresponded executables are located in $ORA_CRS_HOME/bin/.

[edit] Stop and start

Oracle Clusterware is started at boot with the /etc/rc.d/init.d/init.crs script.

Commands to manually start or stop:

/etc/init.d/init.crs start 

/etc/init.d/init.crs stop 

Commands to manually disable or enable start at boot time:

/etc/init.d/init.crs disable 

/etc/init.d/init.crs enable 

Check status of components registered in the OCR:

crs_stat

Status in tabular format:

crs_stat -t

[edit] Starting and stopping resources

Stops all RAC instances:

$ srvctl stop database –d myracdb 

Stops Listener, VIP, GSD, ONS:

$ srvctl stop nodeapps –n racnode1 

Starts ASM on racnode1 and all required dependencies:

$ srvctl start asm –n racnode1

Starts one instance and all required dependencies:

$ srvctl start instance –d myracdb –i mydb1

For more info, read article srvctl.

RAC parameters

Some of the INIT.ORA/SPFILE parameters required for RAC:

• CLUSTER DATABASE=TRUE -- start the database in RAC mode
• INSTANCE_NUMBER=n -- a unique instance number
• THREAD=n -- each instance must have its own thread of redo logs
• UNDO_TABLESPACE=... -- each instance must have its own undo tablespace
• Etc.

[edit] GV$ views

The V$ views only shows details from the currently connected instance. Use the GV$ parameters to query all values across all instances (note the INST_ID column).

For example: instead of using V$THREAD, query GV$THREAD to see all threads across all nodes.

[edit] Configure archiving

Since each instance has its own thread of redo logs, all instances needs to archive its own redo logs. For convenience, it is recommended that each instance archives to a shared cluster file system.

[edit] Configure Services

One can use DBCA or srvctl to configure a service with "primary" instances and "available" instances. Client connections will be routed to the "primary" instances. When one of the "primary" instances becomes unavailable, one of the "available" instance will be promoted to "primary".

NOTE: while fail-over to a "available" instance happens automatically, you need to manually switch them back to "primary" when they become available again.

RAC for Beginners

Real Application Clusters (RAC):


Oracle RAC, introduced with Oracle9i, is the successor to Oracle Parallel Server (OPS). Oracle RAC allows multiple
instances to access the same database (storage) simultaneously. RAC provides fault tolerance, load balancing,
and performance benefits by allowing the system to scale out, and at the same time since all nodes access the
same database, the failure of one instance will not cause the loss of access to the database.

Oracle RAC 10g is a shared disk subsystem. All nodes in the cluster must be able to access all of the data,
redo log files, control files and parameter files for all nodes in the cluster. The data disks must be globally
available in order to allow all nodes to access the database. Each node has its own redo log file(s) and UNDO tablespace,
but the other nodes must be able to access them (and the shared control file) in order to recover that node
in the event of a system failure.

The difference between Oracle RAC and OPS is the addition of Cache Fusion. With OPS a request for data from
one node to another required the data to be written to disk first, then the requesting node can read that data.
With cache fusion, data is passed along a high-speed interconnect using a sophisticated locking algorithm.

With Oracle RAC 10g, the data files, redo log files, control files, and archived log files reside on shared
storage on raw-disk devices, a NAS, ASM, or on a clustered file system

Oracle RAC is composed of two or more database instances.
They are composed of Memory structures and background processes same as the single instance database.

Oracle RAC instances use two processes

==> GES(Global Enqueue Service)
==> GCS(Global Cache Service) this enable cache fusion.

Oracle RAC instances are composed of following background processes:

ACMS—Atomic Controlfile to Memory Service (ACMS)
GTX0-j—Global Transaction Process
LMON—Global Enqueue Service Monitor
LMD—Global Enqueue Service Daemon
LMS—Global Cache Service Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Management Processes (RMSn)
RSMN—Remote Slave Monitor


LMON

The background Global Enqueue Service Monitor (LMON) monitors the entire cluster to manage global resources.
LMON manages instance and process failures and the associated recovery for the Global Cache Service (GCS) and
Global Enqueue Service (GES). In particular, LMON handles the part of recovery associated with global resources.
LMON-provided services are also known as cluster group services (CGS)

This process monitors global enques and resources across the cluster and performs global enqueue recovery operations.
This is called as Global Enqueue Service Monitor.


LCKx

The LCK process manages instance global enqueue requests and cross-instance call operations. Workload is automatically
shared and balanced when there are multiple Global Cache Service Processes (LMSx).

This process is called as Instance enqueue process.This process manages non-cache fusion resource requests such as
libry and row cache requests.

LMSx

The Global Cache Service Processes (LMSx) are the processes that handle remote Global Cache Service (GCS) messages.
Current Real Application Clusters software provides for up to 10 Global Cache Service Processes. The number of LMSx
varies depending on the amount of messaging traffic among nodes in the cluster. The LMSx handles the acquisition
interrupt and blocking interrupt requests from the remote instances for Global Cache Service resources.
For cross-instance consistent read requests, the LMSx will create a consistent read version of the block and send
it to the requesting instance. The LMSx also controls the flow of messages to remote instances.

This process is called as Global Cache service process.This process maintains statuses of datafiles and each cahed
block by recording information in a Global Resource Dectory(GRD).This process also controls the flow of messages to
remote instances and manages global data block access and transmits block images between the buffer caches of different
instances.This processing is a part of cache fusion feature.


LMDx

The Global Enqueue Service Daemon (LMD) is the resource agent process that manages Global Enqueue Service (GES)
resource requests. The LMD process also handles deadlock detection Global Enqueue Service (GES) requests.
Remote resource requests are requests originating from another instance.

This process is called as global enqueue service daemon. This process manages incoming remote resource requests
within each instance.

DIAG

The diagnose daemon is a Real Application Clusters background process that captures diagnostic data on instance
process failures. No user control is required for this demo.

ACMS

ACMS stands for Atomic Controlfile Memory Service.In an Oracle RAC environment ACMS is an agent that ensures a
distributed SGA memory update(ie)SGA updates are globally committed on success or globally aborted in event of a failure.

GTX0-j

The process provides transparent support for XA global transactions in a RAC environment.The database autotunes
the number of these processes based on the workload of XA global transactions.

RMSn

This process is called as Oracle RAC management process.These pocesses perform managability tasks for Oracle RAC.
Tasks include creation of resources related Oracle RAC when new instances are added to the cluster.

RSMN

This process is called as Remote Slave Monitor.This process manages background slave process creation andd
communication on remote instances. This is a background slave process.This process performs tasks on behalf
of a co-ordinating process running in another instance.

CRS

CRS (Cluster Ready Services) is a new feature for 10g Real Application Clusters that provides a standard cluster
interface on all platforms and performs new high availability operations not available in previous versions.
CRS manages cluster database functions including node membership, group services, global resource management,
and high availability. CRS serves as the clusterware software for all platforms. It can be the only clusterware or
run on top of vendor clusterware such as Sun Cluster, HP Serviceguard, etc.


CRS automatically starts the following resources:
· Nodeapps
o Virtual Internet Protocol(VIP) address for each node
o Global Services Daemon
o Oracle Net Listeners
o Oracle Network Services (ONS)
· Database Instance
· Services


Oracle Clusterware (Cluster Ready Services in 10g/ Cluster Manager in 9i) -
provides infrastructure that binds multiple nodes that then operate as single server. Clusterware monitors all
components like instances and listeners. There are two important components in Oracle clusterware, Voting Disk and
OCR (Oracle Cluster Registry)

OCR & Voting Disk

Oracle, 10g RAC, provided its own cluster-ware stack called CRS. The main file components of CRS are the
Oracle Cluster Repository (OCR) and the Voting Disk.
The OCR contains cluster and database configuration information for RAC and Cluster Ready Services (CRS).
Some of this information includes the cluster node list, cluster database instance-to-node mapping information,
and the CRS application resource profiles. The OCR contains configuration details for the cluster database and for
high availability resources such as services, Virtual Inerconnect Protocoal (VIP) addresses.

The Voting Disk is used by the Oracle cluster manager in various layers. The Node Monitor (NM) uses the Voting Disk
for the Disk Hearbeat, which is essential in the detection and resolution of cluster "split brain".


Cache Fusion:-

Oracle RAC is composed of two or more instances. When a block of data is read from datafile by an instance
within the cluster and another instance is in need of the same block,it is easy to get the block image from
the insatnce which has the block in its SGA rather than reading from the disk. To enable inter instance
communication Oracle RAC makes use of interconnects. The Global Enqueue Service(GES) monitors and Instance
enqueue process manages the cahce fusion

Cache Fusion and Global Cache Service (GCS)
Memory-to-memory copies between buffer caches over high-speed interconnects

· fast remote access times
· memory transfers for write or read access
· transfers for all types (e.g data, index, undo, headers )
· Cache coherency across the cluster
· globally managed access permissions to cached data
· GCS always knows whether and where a data block is cached
· a local cache miss may result in remote cache hit or disk read

Unix Commands for DBA

Monitoring database is one of the role Oracle database administrator has to fulfil. I have listed some of the basic and useful commands which are generally used in database administration. If you feel the list is incomplete, please suggest more Unix commands used by database adminstrators in day to day monitoring and administration.

1. To create a file.
cat > [filname]
2. To view contents of a file.
cat [filname] OR more [filname] OR less [filname]
3. To append a file.
cat >> [filname]
4. Creating an empty file.
touch [filname1][filname2][filname3]…. so on. OR touch filname{1,2,3….}
5. Removing a file.
rm [filname] OR rm -i [filname] OR rm -f [filname]
6. To check file type.
file [filname]
7. To create directory.
mkdir [directoryname]
8. To see present working directory.
pwd
9. To list file and directory in current directory.
ls OR ls -l (for long list)
OR ls -p (puts slash to directories)
OR ls -a (shows all directories including hidden files,hidden files or directories starts with dot.)
10. To create nested directories.
mkdir -p dir/dir1/dir2/dir3
11. To see directories in mapped way.
ls -R [directoryname]
12. To enter in another directory
cd [directoryname]
13. To go to home directory.
cd
14. To back to last directory
cd -
15. To enter in to parent directory
cd..
16. Remove directories
rmdir [directoryname] OR rm -r [directoryname](asks permission before deletion)
17. Removing nested directories
rm -r dir1/dir2/dir3 (dir3 is deleted)

18. Commands to compress and uncompress.
A) compress [filname] (compressed file is renamed with .z extension)
To read this file command is zcat [filname].
To uncompress this filecommand is uncompress [filname].
B) gzip[filname] (compressed file is renamed with .gz extension)
To read this file command is gzcat [filname].
To uncompress this filecommand is gunzip [filname]
C) bzip2[filname] (compressed file is renamed with .bz2 extension)
To read this file command is bzcat [filname].
To uncompress this filecommand is bunzip2 [filname]

19. Disk based utilities.
A) du command(disk usage command)
du [filname](displays file size in blocks)
OR du -k [filname](displays file size in KB)
OR du -h [filname](displays file size in human readable form)
OR du -sh[filename]

B) df command(disk free command)
df (displays diskfilesystems in blocks)
OR df -k (displays filesystem in KB)
OR df -h (displays file system in human readable form)
These commands lists only mounted file systems.

20. Help commands
A) man [command] (displays manual page for specified command)
OR man -a [command] (displays all manual pages for specified command)
B) info[command]
C) [command] –help
21. cp COMMAND
This comman is used to copy files and directories from one location to other location
cp
OR cp -r
22. mv COMMAND
This command is used to move directory from one location to other location as
well as to rename them.
mv (moving)
mv sundir/suntest suntest/moontest (renaming suntest to moontest)
23. Change group of the file
chgrp[options] group file
24. Changing file or directory access permissions
chmod [options] file

25 Reporting current date and time
date [options]
26. Comparing the two files and display the differences(text files only)
diff[options] file1 file2
27. Remotely copy files from this machine to another machine
rcp[options] hostname
28. Displaying the last few lines (or parts)of a file
tail [options] file
tail -100 file(displays last 100 lines of file)
29. Removing repeated lines in file
uniq [options] file.
30. Communicating with another host using telnet protocol
telnet[host[port]]
31. Reporting who is logged in and what process are running
who OR w.

32. Killing the process
kill
33. Set or change your password
passwd[options]

34. Showing status of active process
ps [options]

35. Shows the currently running process and some important information about there CPU Usage and Memory Space Usuage.

TOP

Rebuild Index

Fragmentation or Rebuilding of Index:

Scenario:

Let us consider one Table Emp, which is having 1000 rows and created index on the columns. The User was deleted 500 rows from the Emp Table.

But the deleted rows will be still maintained due to High Water Mark. This may occur in issue of Database performance.

While Re-Building the index, the index Re-creates the index only for existing rows in the table. This Re-Building the index is called the Index Fragmenation.

Steps to Fragmention:

Step 1: Let us consider, TEST is the index name for the Emp table.

Analyze Index TEST validate structure;
--------> Index is Analyzed.

Step 2: Select * from Index_status;

Step 3: Select Del_lf_rows, lf_rows from index_stats;

Del_lf_rows--> No.of deleted rows
lf_rows--> Total no.of rows

Step 4: Select (del_lf_rows/lf_rows*100)from index_stats where name='TEST';

Step 5: The result is 30%, so we must rebuild the index is called fragmentation.

Note: The result is >30% means, we must rebuild/fragment the particular index
The result is <30% means, we must coalesce/defragment the particular index

Step 6: How to Fragment/Rebuild the index

Alter Index TEST Rebuild;

Step 7: How to Coalesce/De fragment the index

Alter Index TEST Coalesce;

Step 8: After the rebuilding/or Coalescing the index we must check out the status of the index;

Analyze Index TEST validate structure;
--------> Index is Analyzed.

Step 9: Select (del_lf_rows/lf_rows*100) from index_stats where name='IBM';

Step 10: The above calculation result should be zero(0).

Block Recover using RMAN

BLOCK CORRUPTION ERROR:

Cause: When the datafile is corrupted, some of the blocks are corrupted in the particular datafile it shows the error message like block corruption.

The error code is ORA-01578.

By default, RMAN will check for physical corruption. We can include CHECK LOGICAL in RMAN
to find the corrupted blocks.

How to check the particular block is corrupted:

we can query,

v$database_block_corruption;

How to recover the corrupted blocks:

RMAN> BLOCKRECOVER DATAFILE 12 BLOCK 5; [recover the particular block number which is corrupted.]

Here Datafile 12 is the particular datafile number, and block 5 is the corrupted block number.

or

RMAN> blockrecover corruption list; (all blocks from v$database_block_corruption it will recover)

Row Chaining & Row Migration

IDENTIFY AND SOLVE THE CHAINED ROWS:

ROW CHAINING:

It occurs due to huge inserts where a particular row is too large to fit in the empty block. In this case, the oracle stores the data of the row in a chain
of two or more data blocks. So more I/O is needed to fetch a particular block.

It usually occurs with large rows that contain a large object[LOB] datatypes. LOB supports upto 2GB data.

ROW MIGRATION:

It occurs due to updations. An update increases the amount of data in a row so that the row no longer fits in its datablock, then
the oracle tries to find another block with enough free space to hold the entire row.

Solutions:

1. Move the table from one tablespace to another tablespace which is bigger blocksize.

Alter table test move tablespace users;

2. Take the export of the table and drop the existing table and import the table.

Steps:

SQL> conn sys/sys@info as sysdba

SQL> alter system set db_4k_cache_size=1m scope=both;

System altered.

SQL> create tablespace info datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\INFO\INFO01.DBF'
2 size 20m blocksize 4k;

Tablespace created.

SQL> create user robo identified by robo
2 default tablespace info
3 quota unlimited on info;

User created.

SQL> grant connect,resource to robo;

Grant succeeded.

SQL> conn robo/robo
Connected.

SQL> create table test as select * from all_objects;

Table created.

SQL> select count(*) from test;

COUNT(*)
----------
40686

SQL> select avg_row_len, chain_cnt, table_name from user_tables
2 where table_name='TEST';

AVG_ROW_LEN CHAIN_CNT TABLE_NAME
----------- ---------- ------------------------------
TEST

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select avg_row_len, chain_cnt, table_name from user_tables
2 where table_name='TEST';

AVG_ROW_LEN CHAIN_CNT TABLE_NAME
----------- ---------- ------------------------------
98 0 TEST

SQL> alter table test add address varchar2(1000);

Table altered.

SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
ADDRESS VARCHAR2(1000)

SQL> update test set address='asasasaasasasasasasasasasasasaasasasasasasas';

40686 rows updated.

SQL> commit;

Commit complete.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select avg_row_len, chain_cnt, table_name from user_tables
2 where table_name='TEST';

AVG_ROW_LEN CHAIN_CNT TABLE_NAME
----------- ---------- ------------------------------
145 10310 TEST

SQL> ho
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>exp 'sys/sys as sysdba' file=abc.dmp tables=robo.test direct=y

Export: Release 10.2.0.1.0 - Production on Mon Aug 3 06:56:51 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Direct Path ...
Current user changed to ROBO
. . exporting table TEST 40686 rows exported
Export terminated successfully without warnings.

C:\>exit

SQL> show user
USER is "ROBO"
SQL> drop table test purge;

Table dropped.

SQL> ho
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>imp 'sys/sys as sysdba' file=abc.dmp tables=test fromuser=robo touser=robo

Import: Release 10.2.0.1.0 - Production on Mon Aug 3 06:59:19 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via direct path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing ROBO's objects into ROBO
. . importing table "TEST" 40686 rows imported
Import terminated successfully without warnings.

C:\>exit

SQL> select count(*) from test;

COUNT(*)
----------
40686

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select avg_row_len, chain_cnt, table_name from user_tables
2 where table_name='TEST';

AVG_ROW_LEN CHAIN_CNT TABLE_NAME
----------- ---------- ------------------------------
143 0 TEST

Note:

The above output displays the Chained Rows is 0...Now it reduces the I/O while fetching the rows....

Oracle 10g Background Process

10G BACKGROUND PROCESS:

MMAN

Memory Manager (MMAN) coordinates the sizing of different memory components within SGA. MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage. This process is used only when you enable Automatic Shared Memory Management.

RVWR

Process responsible for writing flashback logs which stores pre-image of data-blocks.

These logs can be used to flash back database to past point in time. It proves very handy specially in situation of logical data corruptions like an accidental drop/truncate of a table or commit of an unwanted transaction.

CTWR

Change Tracking Writer (CTWR) Process introduced in 10g to track changes to block to assist fast RMAN incremental backups.

MMNL

The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed. It works with Automatic Workload Repository (AWR)

MMON

The memory monitor (MMON) process was introduced in 10g is responsible for gathering statistics for Automatic Workload Repository. This helps Oracle doing automatic problem detection and self tuning.

M000

MMON background slave (m000) processes.


ASM BACKGROUND PROCESS:

RBAL

RBAL (Re-balancer) is the ASM related process that performs rebalancing of disk resources controlled by ASM.

ARBx

These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.

ASMB

The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.

ASM instance uses at least two additional background processes to manage data storage operations. The Rebalancer process, RBAL, coordinates the rebalance activity for ASM disk groups, and the Actual ReBalance processes, ARBn, handle the actual rebalance of data extent movements.

RAC BACKGROUND PROCESS:

The additional RAC centric processes are DIAG, LCK, LMON, LMDn, and LMSn processes. We will give a brief description of each and discuss how they interact in a RAC environment next.

DIAG: This is a diagnostic daemon. It constantly monitors the health of the instances across the RAC and possible failures on the RAC. There is one per instance.

LCK: This lock process manages requests that are not cache-fusion requests. Requests like row cache requests and library cache requests. Only a single LCK process is allowed for each instance.

LMD: The Lock Manager Daemon. This is also sometimes referred to as the GES (Global Enqueue Service) daemon since its job is to manage the global enqueue and global resource access. It also detects deadlocks and monitors lock conversion timeouts.

LMON: The Lock Monitor Process. It is the GES monitor. It reconfigures the lock resources adding or removing nodes. LMON will generate a trace file every time a node reconfiguration takes place. It also monitors the RAC cluster wide and detects a node’s demise and trigger a quick reconfiguration.

LMS: This is the Lock Manager Server Process or the LMS process, sometimes also called the GCS (Global Cache Services) process. Its primary job is to transport blocks across the nodes for cache-fusion requests. If there is a consistent-read request, the LMS process rolls back the block, makes a Consistent-Read image of the block and then ship this block across the HSI (High Speed Interconnect) to the process requesting from a remote node. LMS must also check constantly with the LMD background process (or our GES process) to get the lock requests placed by the LMD process. Up to 10 such processes can be generated dynamically.