Oracle®10g Data Guard Concepts and Administration

1. Creating Physical Standby Database

• Preparing the Primary Database for Standby Database Creation
• Step-by-Step Instructions for Creating a Physical Standby Database
• Post-Creation Steps

Preparing the Primary Database for Standby Database Creation

Before  creation of  a standby database we  must first ensure the primary database is properly configured.

Enable Forced Logging

Create Password File

Configure a Standby Redo Log

Set Primary Data Base initialization parameters

Enable Archiving

Enable Forced Login

Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:

SQL> ALTER DATABASE FORCE LOGGING;

This statement can take a considerable amount of time to complete, because it waits for all unlogged direct write I/O to finish.

Create Password File

Create a password file if one does not already exist. Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed

Configure a Standby Redo Log

A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone.

You should plan the standby redo log configuration and create all required log groups and group members when you create the standby database. For increased availability, consider multiplexing the standby redo log files, similar to the way that online redo log files are multiplexed.

Perform the following steps to configure the standby redo log.

Step 1: Ensure log file sizes are identical on the primary and standby databases.

The size of the current standby redo log files must exactly match the size of the current primary database online redo log files

Step 2: Determine the appropriate number of standby redo log file groups.

Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database. However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database. Use the following equation to determine an appropriate number of standby redo log file groups:

(maximum number of logfiles for each thread + 1) * maximum number of threads

Step 3: Verify related database parameters and settings

Verify the values used for the MAXLOGFILES and MAXLOGMEMBERS clauses on the SQL CREATE DATABASE statement will not limit the number of standby redo log file groups and members that you can add. The only way to override the limits specified by the MAXLOGFILES and MAXLOGMEMBERS clauses is to re-create the primary database or control file

Step 4: Create standby redo log file groups

To create new standby redo log file groups and members, you must have the ALTER DATABASE system privilege. The standby database begins using the newly created standby redo data the next time there is a log switch on the primary database.

How to add a  Standby Redo Log File Group to a Specific Thread

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 5

2> (‘/oracle/dbs/log1c.rdo’,'/oracle/dbs/log2c.rdo’) SIZE 500M;

How to add  a Standby Redo Log File Group to a Specific Group Number

You can also specify a number that identifies the group using the GROUP clause:

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
  2> ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;

Step 5: Verify the standby redo log file groups were created

To verify the standby redo log file groups are created and running correctly, invoke a log switch on the primary database, and then query either the V$STANDBY_LOG view or the V$LOGFILE view on the standby database once it has been created.

SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Set Primary Database Initialization Parameters

On the primary database, you define initialization parameters that control redo transport services while the database is in the primary role. There are additional parameters you need to add that control the receipt of the redo data and log apply services when the primary database is transitioned to the standby role.

DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/chicago/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
 'SERVICE=boston LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

Primary Database: Standby Role Initialization Parameters

FAL_SERVER=boston
FAL_CLIENT=chicago
DB_FILE_NAME_CONVERT='boston','chicago'
LOG_FILE_NAME_CONVERT=  '/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'
STANDBY_FILE_MANAGEMENT=AUTO

Enable Archiving

If archiving is not enabled, issue the following statements to put the primary database in ARCHIVELOG mode and enable automatic archiving:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Step-by-Step Instructions for Creating a Physical Standby Database

1. Create a Backup Copy of the Primary Database Datafile

You can use any backup copy of the primary database to create the physical standby database, as long as you have the necessary archived redo log files to completely recover the database. Oracle recommends that you use the Recovery Manager utility (RMAN).

2. Create a Control File for the Standby Database

If the backup procedure required you to shut down the primary database, issue the following SQL*Plus statement to start the primary database:

SQL> STARTUP MOUNT;

Then, create the control file for the standby database, and open the primary database to user access, as shown in the following example:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';
SQL> ALTER DATABASE OPEN;

3. Prepare an Initialization Parameter File for the Standby Database

Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE) used by the primary database; a text initialization parameter file can be copied to the standby location and modified. For example:

SQL> CREATE PFILE='/tmp/initboston.ora' FROM SPFILE;

Modifying Initialization Parameters for a Physical Standby Database
.
DB_NAME=chicago 
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/boston/control1.ctl', '/arch2/boston/control2.ctl' 
DB_FILE_NAME_CONVERT='chicago','boston'
LOG_FILE_NAME_CONVERT= '/arch1/chicago/','/arch1/boston/','/arch2/chicago/','/arch2/boston/'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=  'LOCATION=/arch1/boston/ 
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  
DB_UNIQUE_NAME=boston' 
LOG_ARCHIVE_DEST_2=  'SERVICE=chicago LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO 
FAL_SERVER=chicago 
FAL_CLIENT=boston

4. Copy Files from the Primary System to the Standby System

Use an operating system copy utility to copy the following binary files from the primary system to the standby system:

Backup DataFile
Standby Control File
Initialization Parameter

5. Set Up the Environment to Support the Standby Database
   1. Create Window Based Service
   2. WINNT> oradim -NEW -SID boston -INTPWD password -STARTMODE manual
   3. Create a password file.
   4. On both the primary and standby sites, use Oracle Net Manager to configure a listener for the respective databases.
   5. % lsnrctl stop
   6. % lsnrctl start
   7. Create Oracle Net service names.
   8. Create a server parameter file for the standby database.

   9. CREATE SPFILE FROM PFILE='initboston.ora';

   • Perform the following steps to create a Windows-based service, create a password file, set up the Oracle Net environment, and create a SPFILE.
6. Start the Physical Standby Database

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

The statement includes the DISCONNECT FROM SESSION option so that Redo Apply runs in a background session

Test archival operations to the physical standby database.

SQL> ALTER SYSTEM SWITCH LOGFILE;

Verify the Physical Standby Database Is Performing Properly

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME   2  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Force a log switch to archive the current online redo log file.
SQL> ALTER SYSTEM SWITCH LOGFILE;

Verify the new redo data was archived on the standby database.
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
  2>  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Verify new archived redo log files were applied.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG   2  ORDER BY SEQUENCE#;

Post-Creation Steps

At this point, the physical standby database is running and can provide the maximum performance level of data protection. The following list describes additional preparations you can take on the physical standby database:

• Upgrade the data protection modeThe Data Guard configuration is initially set up in the maximum performance mode (the default).
• Enable Flashback DatabaseFlashback Database removes the need to re-create the primary database after a failover. Flashback Database enables you to return a database to its state at a time in the recent past much faster than traditional point-in-time recovery, because it does not require restoring datafiles from backup nor the extensive application of redo data. You can enable Flashback Database on the primary database, the standby database, or both.

Data Guard for Oracle Database11g

If you have Oracle Enterprise Database11g installed on your operating system then you can be benefited with the best disaster recovery solution, data protection and comprehensive data availability in the industry.

Data Guard helps to safeguard your precious database from any kind of corruptions, disasters, errors and failures. It not only manages but also provides an automation software infrastructure to create, monitor and maintain enterprise standby databases as synchronized copies from the production database that may be located at a remote location. Data guard would help to prevent any kind of data loss if your production database is corrupted or destroyed due to any unforeseen reason.

Data Guard is a better alternative than older Oracle Real Application Clusters or Oracle RAC. Unlike Oracle RAC, Data Guard is capable to provide you with Disaster Recovery and High Availability requirements. Moreover, Data Guard is designed with such features that it would not let the corruptions to propagate to the stored standby database in Data Guard storage from your primary database. You can use both logical and physical standby databases for productive purposes but would keep them in standby role.

With Data Guard, you can assure of reliable and optimum data availability and protection. You will not have to incur hefty amounts to maintain this level of data security. Moreover, using Data Guard is not complex as its features are very much integrated with Oracle Enterprise Database11g. What is more? You can also use your standby databases for production purposes. It can also be used with various other Oracle Solutions such as Oracle Flashback, Oracle RAC, RMAN etc. to add up to its capabilities. It is also compatible with other Oracle Database11g options such as Oracle Advanced Compression and Oracle Active Data Guard.

Various advantages of Data Guard include its

• Capability to deal with quick switching between primary databases and standby databases. This helps to minimize the downtime you would have wasted when primary database fails to perform.
• Resolve problems like user errors or logical corruptions in primary database. The redo data is always validated before it is applied to the standby database to ensure more security.
• The workload of primary database can also be reduced as all the functions that require read only feature can utilize standby database.

Many people are confused what would happen if the network connection disconnects for sometime between the primary and standby database. Now you need not to worry as all the redo data that was not sent during the disconnection phase is detected and updated by the Data Guard automatically. No manual intervention or support is required in this case. With its simple and centralized management framework or design you can easily monitor and manage various tasks of multiple databases once, you configure your Data Guard for the same.

A Data Guard can support up to nine standby databases and one primary database. It also utilizes feature that compare incoming redo stream and standby database to detect any type of firmware or hardware storage fault. Data Guard also supports for Oracle Active Data Guard and helps to create Snapshot Standby database.

Thanks To anne

Recreate Control Files; How? and When?

Recreate Control Files; How? and When?

When?

The control files of a database store the status of the physical structure of the database. The control file is absolutely crucial to database operation.
Control File contains…

> Database information (RESETLOGS SCN and their time stamp)
> Archive log history
> Tablespace and datafile records
(filenames, datafile checkpoints, read/write status, offline or not)
> Redo Logs (current online redo log)
> Database’s creation date
> database name
> current archive log mode
> Log records (sequence numbers, SCN range in each log)
> RMAN catalog
> Database block corruption information
> Database ID, which is unique to each DB

If the controlfile is lost, it is somewhat difficult to do a recovery because the database cannot be mounted for a recovery. The controlfile must be recreated
When you want to rename your database at that time also u need to recreate control files

How?
You need a create controlfile script for recreating control files.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 – Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.0.1.1.1- Production

#this is for creating Control File Creation script
SQL> alter database backup controlfile to trace;
Database altered.
SQL>

This will create a trace file in the udump directory.
Im my case it was

C:\oracle\test\udump\ORA03620.TRC
Edit the file to point the path of the datafiles and redologfiles.

CREATE CONTROLFILE REUSE DATABASE “TEST” RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1696
LOGFILE
GROUP 1 ‘C:\ORACLE\TEST\REDO\LOG1.LOG’ SIZE 100M,
GROUP 2 ‘C:\ORACLE\TEST\REDO\LOG2.LOG’ SIZE 100M
DATAFILE
‘C:\ORACLE\TEST\DATA\DATA.DBF’,
‘C:\ORACLE\TEST\TS_TEST1.DBF’,
‘C:\ORACLE\TEST\UNDO.DBF’,
‘C:\ORACLE\TEST\DATA\USER_1.DBF’,
‘C:\ORACLE\TEST\USER_2.DBF’,
‘C:\ORACLE\TEST\USER_UNDO.DBF’
‘C:\ORACLE\TEST\USER_TEMP.TMP’
CHARACTER SET WE8MSWIN1252
;

# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP_TEST ADD TEMPFILE ‘C:\ORACLE\TEST\TMP_TEST.DBF’ REUSE;
# End of tempfile additions.
#

ALTER DATABASE OPEN RESETLOGS;

and rename it to control_trace.sql
Then startup the database in nomount mode and run the control_trace.sql file as sys as sysdba user
SQL*Plus: Release 9.0.1.1.1 – Production on Tue Dec 30 10:45:47 2008

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 80812648 bytes
Fixed Size 453224 bytes
Variable Size 54525952 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> @ C:\oracle\test\udump\ control_trace.sql

Control file created.

Database altered.

Check the status from v$instance
Check the name from v$controlfile

Create Oracle 9i database in windows manually

Create Oracle 9i database in windows manually

“dbtest” is the database that i am going to create.
create one folder called dbtest (Example: c:\oracle\dbtest)

1. Open the console(Run –> cmd)
2. Set oracle SID as ’set oracle_sid=dbtest’
3. If dbtest windows service is not created then execute this
c:\>oradim -new -sid dbtest -intpwd dbpassword
4. Create a directory called dbtest. In this case I created it in c:\oracle\dbtest
( you can change the location according to yours )
5. Create your initdbtest.ora that I saved it in c:\oracle\Ora91\database.
This is the default location that oracle will check for pfile. Rather you can save it any where but u should mention the file

during startup; startup pfile=path

Following is the static parameter file that I have used in my database creation
Note: All paths that i had given depends on my configuration. Make sure paths which you had given is correct
(This is the simple way to create a parameter File. You can add whichever parameters you want)

##########################################
db_name=dbtest
instance_name=dbtest
control_files=c:\oracle\dbtest\control\c.ctl
background_dump_dest=c:\oracle\dbtest\bdump
core_dump_dest=c:\oracle\dbtest\cdump
user_dump_dest=c:\oracle\dbtest\udump
db_block_size=4096
db_cache_size=33554432
compatible=9.0.1.1.1
undo_management=AUTO
undo_retention=1
undo_tablespace=UNDOTBS
pga_aggregate_target=25165824
sort_area_size=524288
############################################

6. Open Another command prompt
7. Execute this command set oracle_sid=dbtest
8. Execute this command “sqlplus/nolog” to get the sql prompt
9. Conn sys as sysdba ;
SQL> conn sys as sysdba
10. Now Start the instance in nomount mode
SQL> startup nomount
Why are you starting in nomount mode?
Bcoz still we are not created control files. An instance should be started in NOMOUNT mode only
during the database creation and recreation of control files
11. Create a sql script file to create a database.
(Here i m giving you an example )
######################################
create database dbtest
LOGFILE GROUP 1(‘c:/oracle/dbtest/redo/log1.log’) SIZE 100M,
GROUP 2(‘c:/oracle/dbtest/redo/log2.log’) SIZE 100M
DATAFILE ‘c:/oracle/dbtest/data/data.dbf’ SIZE 325M
MAXLOGFILES 3
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
######################################

12. Run the script in sql prompt
SQL>@ “path of the sql file”
once completed this script you can see the control files, redo log file, Alert log file and .dbf files
files are created in c:\oracle\dbtest\…….

13. Now you can shutdown the database
SQL>SHUT
and restart it
c:\>set oracle_sid=dbtest
If dbtest windows service is not yet started start it manually or execute this
c:\>oradim -startup -sid dbtest -intpwd dbpassword
(Note: Here we don’t need to start the database in nomount mode because we have already created control files)
SQL>conn sys as sysdba
password: sys(sys password)
( or you can simply change the mount state rather restarting the database
SQL>alter database open)
14. Execute catalog.sql
SQL>@c:\ora91\rdbms\admin\catalog.sql
15. Execute catproc.sql
SQL>@d:\ora91\rdbms\admin\catproc.sql

Note: if the password file is corrupted you can recreate
the password file, make sure to delete the existing password file.
c:\>orapwd file=d:\ora91\database\PWDdbtest.ora password=dbpassw

Introduction to Data Blocks

Introduction to Data Blocks, Extends, Segments. Oracle allocates logical database space for all data in a database. The units of database space allocation are data blocks, extents, and segments

At the finest level of granularity, Oracle stores data in data blocks (also called logical blocks, Oracle blocks, or pages). One data block corresponds to a specific number of bytes of physical database space on disk.

The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks allocated for storing a specific type of information.

The level of logical database storage above an extent is called a segment. A segment is a set of extents, each of which has been allocated for a specific data structure and all of which are stored in the same tablespace. For example, each table’s data is stored in its own data segment, while each index’s data is stored in its own index segment. If the table or index is partitioned, each partition is stored in its own segment.

Oracle allocates space for segments in units of one extent. When the existing extents of a segment are full, Oracle allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk.

A segment and all its extents are stored in one tablespace. Within a tablespace, a segment can include extents from more than one file; that is, the segment can span datafiles. However, each extent can contain data from only one datafile.

Although you can allocate additional extents, the blocks themselves are allocated separately. If you allocate an extent to a specific instance, the blocks are immediately allocated to the free list. However, if the extent is not allocated to a specific instance, then the blocks themselves are allocated only when the high water mark moves. The high water mark is the boundary between used and unused space in a segment.

Oracle Corporation recommends that you manage free space automatically

Data Blocks Overview
Oracle manages the storage space in the datafiles of a database in units called data blocks. A data block is the smallest unit of data used by a database. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks.

The standard block size is specified by the initialization parameter DB_BLOCK_SIZE. In addition, you can specify of up to five nonstandard block sizes. The data block sizes should be a multiple of the operating system’s block size within the maximum limit to avoid unnecessary I/O. Oracle data blocks are the smallest units of storage that Oracle can use or allocate.

Header (Common and Variable)
The header contains general block information, such as the block address and the type of segment (for example, data or index).

Table Directory
This portion of the data block contains information about the table having rows in this block.

Row Directory
This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area).

After the space has been allocated in the row directory of a data block’s overhead, this space is not reclaimed when the row is deleted. Therefore, a block that is currently empty but had up to 50 rows at one time continues to’ have 100 bytes allocated in the header for the row directory. Oracle reuses this space only when new rows are inserted in the block.

Overhead
The data block header, table directory, and row directory are referred to collectively as overhead. Some block overhead is fixed in size; the total block overhead size is variable. On average, the fixed and variable portions of data block overhead total 84 to 107 bytes.

Row Data
This portion of the data block contains table or index data. Rows can span blocks.

Free Space
Free space is allocated for insertion of new rows and for updates to rows that require additional space (for example, when a trailing null is updated to a nonnull value). Whether issued insertions actually occur in a given data block is a function of current free space in that data block and the value of the space management parameter PCTFREE.
In data blocks allocated for the data segment of a table or cluster, or for the index segment of an index, free space can also hold transaction entries. A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT…FOR UPDATE statement accessing one or more rows in the block. The space required for transaction entries is operating system dependent

Free Space Management
Free space can be managed automatically or manually.
Free space can be managed automatically inside database segments. The in-segment free/used space is tracked using bitmaps, as opposed to free lists. Automatic segment-space management offers the following benefits:
· Ease of use
· Better space utilization, especially for the objects with highly varying size rows
· Better run-time adjustment to variations in concurrent access
· Better multi-instance behavior in terms of performance/space utilization
You specify automatic segment-space management when you create a locally managed tablespace. The specification then applies to all segments subsequently created in this tablespace.

Availability and Compression of Free Space in a Data Block
Two types of statements can increase the free space of one or more data blocks: DELETE statements, and UPDATE statements that update existing values to smaller values. The released space from these types of statements is available for subsequent INSERT statements under the following conditions:
· If the INSERT statement is in the same transaction and subsequent to the statement that frees space, then the INSERT statement can use the space made available.
· If the INSERT statement is in a separate transaction from the statement that frees space (perhaps being run by another user), then the INSERT statement can use the space made available only after the other transaction commits and only if the space is needed.
Released space may or may not be contiguous with the main area of free space in a data block. Oracle coalesces the free space of a data block only when (1) an INSERT or UPDATE statement attempts to use a block that contains enough free space to contain a new row piece, and (2) the free space is fragmented so the row piece cannot be inserted in a contiguous section of the block. Oracle does this compression only in such situations, because otherwise the performance of a database system decreases due to the continuous compression of the free space in data blocks.

Row Chaining and Migrating
In two cases, the data for a row in a table may be too large to fit into a single data block.

In the first case, the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks, it may be one or more, reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of data type LONG or LONG RAW.  In this case its unavoidable

However, in the second case, a row that originally fit into one data block is updated so that the overall row length increases, and the block’s free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.

When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row.

PCTFREE, PCTUSED, and Row Chaining
For manually managed tablespaces, two space management parameters, PCTFREE and PCTUSED, enable you to control the use of free space for inserts and updates to the rows in all the data blocks of a particular segment. Specify these parameters when you create or alter a table or cluster.