Initialization parameters:

There are no initialization parameters associated with this feature.

Purpose of this feature:

If there is a long running query or transaction that gets effected or fails because of the space constraints this new feature is helpful to identify those problems and fix them without redoing the entire query or transaction. The space failures include the following:

Error Conditions That Are Correctable:

ORA – 01650 (unable to extend RBS)
ORA – 01653 ( unable to extend table)
ORA – 01654 (unable to extend index)

ORA-01628 ( Max extents reached for the RBS)
ORA-01631 ( max extents reached for the table)
ORA-01654(max extents reached for the index)

ORA – 01536 (space quota exceeded for the tablespace)

are the general messages that can be faced while a transaction or a query is being executed against some objects of the database to process the data or to insert the data. This new feature enables to suspend that transaction and resume the same after the table or index is taken care of by providing additional space foe the index/table/tablespace. The activity of the session can be suspended and resumed.

Operations That Are Resumable:

01. select statements that require temporary segments for sorting purposes.
02. DML statements like insert, update and delete that generate undo.
03. DDL statements like the following:
a. create table as select
b. create index
c. alter index .. rebuild
d. alter (table or index) .. (move partition or rebuild partition )
e. create materialized view or materialized view log
04. Import and Export – for these utilities there are separate parameters associated with these tools.

Parameters Associated with Export Utility

RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE

Parameters Associated with Import Utility

RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE

05. SQL Loader – for this there are separate ‘resumable’ parameters:
resumable — enable or disable resumable for current session (Default FALSE)
resumable_name — text string to help identify resumable statement
resumable_timeout — wait time (in seconds) for RESUMABLE (Default 7200)

Limitations:

Basically the limitations are concentrated around the system managed or dictionary managed tablespace/s and objects in that/those tablespace/s.

01. DDL like create table or create index with explicit ‘MAXEXTENTS’ clause which leads to space constraints and the action gets aborted, the resumable space management can not be done by Oracle as that is an activity prior to creation of an object. The resumable Space Management activity is for those DML and DDL activities that are used after the object comes into existence.

Save: This limitation can be over come if ‘maxextents’ is set to ‘unlimited’ or by using Locally Managed Tablespaces.

02. If the Rollback Segments are located in the ‘dictionary managed’ Rollback Segments Tablespace then space allocation for those RBS is not ‘resumable’. However, space allocation for the user objects like tables, indexes and like objects, is ‘resumable.

Save: This limitation can be overcome by using Automated Undo Management (AUM) or by using Locally Managed Tablespaces.

Impact of Enabling ‘Resumable Space Management’ feature

It holds up the system resources and hence the users are to be granted that privilege.

Alter user <username> grant resumable to <username>;

This privilege is granted by default to DBA role.

Enabling and disabling Resumable Space Allocation

Resumable Space Management is controlled on a session by session level and is disabled by default..

At Session Level ‘Resumable Space Allocation:

At the session level unless ‘Resumable Space Allocation’ is enabled, it cannot be used.

To enable the feature the following statement is to be issued:
alter session enable resumable;

to disable:
alter session disable resumable;


The suspended session is enabled after a set time out period.
At the session level ‘timeout interval’ can also be set by issuing the following statement:

alter session enable resumable timeout 3600;

At the session level naming the resumable sessions are enabled using the following statement:

Alter session enable resumable timeout 3600 name ‘insert into table’;

At the database level DBA can set the ‘resumable space allocation’ for each session when ever a user is logged into by creating a database level after suspend trigger.

create or replace trigger resumable_default_timeout
after suspend
on database
begin
dbms_resumable.set_timeout(3600);
— 3600 is the number of seconds, which means 1 hour.
end;
/

create or replace trigger resumable_session_start
after logon on database
begin
execute immediate ‘alter session enable resumable’;
end;
/

A suggestion to mange the Errors generated because of the constraints of space

Let a table be created in the schema of SYS to deposit all the space allocation errors raised for various objects in designated table. The sample script is provided by Oracle:

The DBMS_RESUMABLE package

This package has five procedures that can be used in concert with resumable space management. They are

1. abort

This is to cancel all the suspended statements for a given session.
dbms_resumable.abort(session_id in number);

2. get_session_time_out

This is a function and returns the current value of a given session’s resumable space management time out setting in seconds in the form of a number.
SQL> select dbms_resumable.get_session_timeout(8) from dual;

DBMS_RESUMABLE.GET_SESSION_TIMEOUT(8)
————————————-
7200

3.set_session_timeout

This procedure sets the time out period for the session before the suspended statement is restarted.

SQL> exec dbms_resumable.set_session_timeout(8,14400);

PL/SQL procedure successfully completed.

This is to verify the effect of the statement given above.
SQL> select dbms_resumable.get_session_timeout(8) from dual;

DBMS_RESUMABLE.GET_SESSION_TIMEOUT(8)
————————————-
14400

4. get_timeout

this function returns a number value for timeout set for a given or current session.

SQL> select dbms_resumable.get_timeout from dual;

GET_TIMEOUT
———–
14400

5. set_timeout

this procedure is to set a value for the time out of the session current or given.

SQL> exec dbms_resumable.set_timeout(28800);

PL/SQL procedure successfully completed.

6. space_error_info is a function to return space related errors.

dbms_resumable.space_error_info
(ERROR_TYPE,
OBJECT_TYPE,
OBJECT_OWNER,
TABLE_SPACE_NAME,
OBJECT_NAME)
return Boolean;

Oracle has introduced ‘Server Parameter File’ (spfile) feature.
Oracle has introduced restricted dynamism in modifying some initialization parameters at session and system level. But these changes are not persistent over the startup-shutdown cycles of the database at the database level unless the initialization parameter file is modified.

Defining spfile before creating database in the initialization parameter file

This parameter set in the initialization parameter file shall create spfile at the given destination with the given name.
spfile=d:ora901databasespfiletest.ora

Creation of SPFile

The following command is to be issued if the spfile is not created at the time of creating the database.
create spfile=’M:oracleora91databaseSPFILEKNOW9I.ORA’
from pfile=’H:Oracleadminknow9ipfileinit.ora’;

Operation 187 succeeded.

This output indicates that

create spfile=’M:oracleora91databaseSPFILEKNOW9I.ORA’
from pfile=’h:oracleadminknow9ipfileinit.ora’;

File created.

This output indicates that

After creating the spfile shutdown the database and restart the instance using the spfile.

shutdown immediate
startup pfile= M:oracleora91databaseSPFILEKNOW9I.ORA

Default Location of SPFile

The server parameter file is created by default under the Oracle_Homedatabase in case of Windows NT/2000/XP and Oracle_Home/dbs in case of UNIX operating system

Setting up of Persistent Parameters

A new clause to the ‘alter system’ called ‘scope’ has been introduced to enable the DBA to indicate how Oracle is to interpret the desired persistence of the change. The scope has been defined at three levels. Basically there are only two levels and the third level is a put together of both the 2 levels.

First Level spfile – This indicates that the spfile will also be modified. But changes will be
effective from the next startup or instance is recycled using the spfile.
This is the only way to modify STATIC parameters of the database.

Second Level memory – This indicates that the spfile will not be updated for the database and the change is for the instance for the current instance. This can not be used for the STATIC parameters. And the modification is not persistent.

Third Level both – This indicates that the change to the parameter is not only for the current instance but also for the spfile of the database and thus shall persist through the future cycles of the database.

Another new clause to the ‘alter system’ called ‘comment’ is also introduced to enable to record the
comments made at the time of modifying the parameter can be recorded in the spfile.

When the spfile is created after the database is created only those comments that are in the same line of the parameter survive and all others do not appear. Stand alone and header comments do not survive.

A sub-clause for the ‘memory’ is introduced to ‘defer’ the implementation of modifications/changes
for future sessions that connect to the database during the life of that instance.

ex : changing the parameters for the future sessions of the current instance

alter system set query_rewrite_enabled=true comment=’change on 07/21/2002′ scope=memory
deferred;

ex: changing parameters for both

alter system set query_rewrite_enabled=true comment=’change on 07/21/2002′ scope=both;

ex: changing more than one parameters in a single statement

alter system set shared_pool_size=100m, comment=’change on 07/21/2002′ scope=both,
aq_tm_processes=5, comment=’change on 07/21/2002′ scope=both;

Note:

Problems on NT and Windows 2000

On NT / W2Kyou have for every database a dedicated service. This services is created
with oradim.exe and this program doesn’t support spfile with Version 9.0.1

There are Workarounds for this problem.
1. You can remove the pfile entry ORA_<SID>_PFILE from the registry.
2. In your init.ora you can add an entry spfile=<spfile_name>.

The Oracle Managed File (OMF) parameters:

(1) To define the destinations of System, Temp, Rollback and Data files

db_create__file_dest

(2) To define the destination of redo log files

db_create_online_log_dest_n

The maximum number of groups allowed to be created by Oracle 9i are 5 using OMF

‘n’ is the integer for individual groups of the redo log files.

(3) To define control file destination there is an exiting parameter – this is not NEW.

Control_files

If the destination for the control files is not defined the control files are created in the directories which are defined as the OMF destinations. If only ‘db_create_file_dest’ is defined in the init.ora file only one control file is created and if, also, ‘db_create_online_log_dest_n’ is defined second control file created at that destination.

Additional Information

Oracle has its naming convention for the OMF.

Up to 8 characters for the tablespace name is used. The second part of the name, the unique character string, is important, as 2 tablespaces might have unique names, but the 8 character strings may be the same.

If the data file destinations are to be different that can be done using ‘alter system’ and ‘alter session’ commands. But, if the naming convention is not followed that file will no longer be an OMF.

If any existing non-OMF is renamed with the OMF naming convention and this causes error as the prefix ‘ORA_’ is reserved for OMF.

SGA

Oracle 9i Memory Parameters

The new parameter sga_max_size is available and is NOT changeable dynamically. The initial max size of the SGA is the memory configured at the start up of the instance. If the init.ora file has this parameter in it at the startup itself the memory is acquired and this can cause swapping or paging. Hence care should be taken. Do not set the max size in the init.ora file. If the value is set smaller than the total allocated memory, then default is the initially allocated memory.

But when required the default can be modified by issuing the statement:

Alter system set shared_pool_size=40000000;
Alter system set db_cache_size=5000000;
Alter system set db_8k_cache_size=5000000;

The db_block_buffers, buffer_pool_keep, buffer_pool_recycle parameters are deprecated in Oracle 9i. Oracle 9i allows multiple data block sizes while creating tablespaces in multiples of 2K up to 32K.

The existing parameter db_block_size is used for the following tablespaces only:

(1) System tablespace
(2) Rollback / Undo tablespace
(3) Temporary tablespace.

While other tablespaces can be created with the same data block size, option is given to have tablespace with larger data block sizes. To configure a tablespace with larger data block sizes the following parameter is to be included in the initialization parameter file.

db_block_size — to define the data block for creating System, RBS and Temp
db_cache_size — the general parameter with no multiple data block sizes
db_2k_cache_size — for bigger data block tablespace
db_4k_cache_size — for bigger data block tablespace
db_8k_cache_size — for bigger data block tablespace
db_16k_cache_size — for bigger data block tablespace
db_32k_cache_size — for bigger data block tablespace


the values for these parameters are given bytes of kilo bytes as under

db_2k_cache_size=8364032 or
db_2k_cache_size=8000K or
db_2k_cache_size=8M

if the data block size is universally set and you do not want any tablespace with bigger data block size the following parameters are required

db_block_size=4096 –( in bytes)
db_cache_size=33554432 –( in bytes)

While the old parameters still are usable they have no dynamic manageability. The new parameters are dynamically manageable.


PGA

There are Tunable and Un-tunable parameters in PGA (Private Global Area).

sort_area_size
hash_area_size,
bitmap_merge_area_size,
create_bitmap_area_size

These parameters of 8i are tunable and are dynamically changeable.

In Oracle 9i gives an automated option to manage PGA.

pga_aggregate_target:
This parameter allows to set an aggregate amount
of memory that becomes the target amount of PGA
memory available to be allocated in bytes,
kilobytes (K), megabytes (M) and gigabytes (G).
This parameter can be set at system level, but is
dynamic in its nature. If this parameter is set the
workarea_size_policy is by default set to ‘auto’.

Ex:

alter system set pga_aggregate_target = 10m;
select name,value,isdefault from v$parameter where name = ‘workarea_size_policy’;

workarea_size_policy
AUTO
TRUE

After the system has passed over a shutdown the values are as under:

select name,value,isdefault from v$parameter where name = ‘workarea_size_policy’;

workarea_size_policy
MANUAL
TRUE

workarea_size_policy
This parameter can be set at system level or
session level. This parameter establishes whether
a given session’s PGA size should sized
automatically or via the database using init.ora
parameters. If this parameter is set to ‘auto’, the
database will size the tunable PGA memory while
aggregate allocated amount of PGA will be
bounded by pga_aggregate_target size.

Dynamically Changeable Shared Memory:

Redo log buffers, large pool, java pool cannot be dynamically changed.

shared_pool, db_cache_size parameters can be dynamically changed
select name,value
from v$parameter
where name in (‘shared_pool_size’,
‘db_cache_size’,
‘db_16k_cache_size’
);


shared_pool_size 46137344
db_16k_cache_size 0
db_cache_size 33554432

alter system set shared_pool_size=45000000;
alter system set db_cache_size=33000000;


shared_pool_size 46137344
db_16k_cache_size 0
db_cache_size 33554432

the default settings have been seen here as the alter system command has tried to reduce the sizes.

If the memory is not sufficient for the increasing the parameter values dynamically

SQL> alter system set shared_pool_size=50000000;
alter system set shared_pool_size=50000000
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

These error messages are prompted.

Undo Management Parameters

The Rollback Segments’ Management prior to Oracle 9i is the management of ora-1555 which means that the read-consistent image of the data that a given sessions needs no longer exists in the RBS of the database. The New Undo Management introduced by Oracle 9i avoids that as the management is taken over by Oracle itself than being in the hands of the users/administrators. A new parameter defining the retention period for the Undo Image in the RBS is provided the retain the image for a default period of 900 seconds even after the transaction is committed. This new parameter has a significant effect on another feature called ‘flashback query’. Undo Parameter is dynamic and the database is allowed more than one RBS tablespace, which include Undo tablespace. But the limitation is that ‘ no database can have more than one UNDO tablespace in use at a given time’.
There are some limitations or rather notable aspects for these new parameters. The SQL statements like ‘set transaction use rollback segment’ can not be issued against the database to direct a particular query to a RBS, and in case it is attempted ORA-30019 error pops up. This is due to non-compatibility of those queries with UNDO management by default.

The following parameters are to be set to enable the Oracle Automated Undo Management.

Data Dictionary:

V$UNDOSTAT

This view provides system-generated stats collected every 10 minutes for a period of 24 hours.
The UNDOBLKS column is of importance to DBAs. That determines whether the UNDO tablespace is created with sufficient space for the database or not is determined by going through the stats available.

Select sum(undoblks) undofrom v$undostat;
Select tablespace_name,file_name,blocks from dba_data_files;

If the value is greater than the total blocks for the Undo Tablespace then increase the size of the Undo Tablespace.

alter database datafile ‘H:ORACLEORADATAKNOW9IUNDOTBS01.DBF’ resize 220M;

Also watch

UNXPSTEALCNT : This is the count of expired undo blocks which are not to expire per undo_retention prescription, but because of the non-availability of space in the tablespace they are expired. This column if shows values the tablespace has no sufficient space to hold the transaction of undo + the requirement of retention of undo. So increase the size of the data file or set the auto-extension on. If this has a non-zero value ‘snapshot too old’ error messages shall popup.

SSOLDERRCNT
NOSPACEERRCNT
: These two columns keep the track of the Oracle Error messages generated during
the snapshot. If these columns have non-zero values consider to increase the size of the data files associated with the UNDO tablespace, thus increase the size of the tablespace.

dba_undo_extents

This view provides info on each extent in the UNDO tablespace, including the commit time for each transaction. This view enables to determine which tablespace is defined as UNDO tablesapce.

Flashback Query

Oracle has provided a great utility to recover the lost data using a utility to recover the data from the undo segments which are under the auto management introduced by oracle 9i, under Auto Undo Management. The particular parameter undo_retention is the sole source and life line for the recovery management using the undo image of the data which is retained in the undo segments of the database.

Once the flashback query is enabled the objects that have been created/modified before the system change number at which the flashback is enabled cannot be dropped or modified. The database throws up:

SQL> drop table test_flashback_query;
drop table test_flashback_query
*
ERROR at line 1:
ORA-08182: operation not supported while in Flashback mode

Here is an example of such recovery:

SQL> rem=============================
SQL>
SQL> create table emp_recover as select * from scott.emp;

Table created.

SQL> VARIABLE SCN_SAVE NUMBER;
SQL> EXECUTE :SCN_SAVE := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;

PL/SQL procedure successfully completed.

SQL> PRINT SCN_SAVE

SCN_SAVE
———-
25964681

SQL> delete from emp_recover;

14 rows deleted.

SQL> DECLARE
2 CURSOR FLASH_RECOVER IS
3 select * from emp_recover;
4 emp_recover_rec emp_recover%ROWTYPE;
5 begin
6 DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:SCN_SAVE);
7 open FLASH_RECOVER;
8 DBMS_FLASHBACK.DISABLE;
9 loop
10 FETCH FLASH_RECOVER INTO emp_recover_rec;
11 EXIT WHEN FLASH_RECOVER%NOTFOUND;
12 insert into emp_recover
13 values
14 (emp_recover_rec.empno,
15 emp_recover_rec.ename,
16 emp_recover_rec.job,
17 emp_recover_rec.mgr,
18 emp_recover_rec.hiredate,
19 emp_recover_rec.sal,
20 emp_recover_rec.comm,
21 emp_recover_rec.deptno);
22 end loop;
23 CLOSE FLASH_RECOVER;
24 commit;
25 end;
26 /
DECLARE
*
ERROR at line 1:
ORA-08183: Flashback cannot be enabled in the middle of a transaction
ORA-06512: at “SYS.DBMS_FLASHBACK”, line 0
ORA-06512: at line 6


SQL> commit;

Commit complete.

SQL> DECLARE
2 CURSOR FLASH_RECOVER IS
3 select * from emp_recover;
4 emp_recover_rec emp_recover%ROWTYPE;
5 begin
6 DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:SCN_SAVE);
7 open FLASH_RECOVER;
8 DBMS_FLASHBACK.DISABLE;
9 loop
10 FETCH FLASH_RECOVER INTO emp_recover_rec;
11 EXIT WHEN FLASH_RECOVER%NOTFOUND;
12 insert into emp_recover
13 values
14 (emp_recover_rec.empno,
15 emp_recover_rec.ename,
16 emp_recover_rec.job,
17 emp_recover_rec.mgr,
18 emp_recover_rec.hiredate,
19 emp_recover_rec.sal,
20 emp_recover_rec.comm,
21 emp_recover_rec.deptno);
22 end loop;
23 CLOSE FLASH_RECOVER;
24 commit;
25 end;
26 /

PL/SQL procedure successfully completed.

SQL> select count(*) from emp_recover;

COUNT(*)
———-
14

SQL> spool off

Important points to remember:

If a commit is not issued after the DML is issued against the transaction can be rolled back easily by issuing a rollback statement at the SQL prompt.

If it is attempted to enable dbms_flashback at that point of time (before the commit is issued against the database) Oracle throws up error. The undo_retention by default is set to 900 seconds. This can be rest for the session or for the system between 0 and the integer value of 32bit. (i.e., 4294967295).

Using Flashback in export
M:oracleora91BIN>
M:oracleora91BIN>exp scott/tiger@know9i full=y file=exp_07192002.dmp flashback_time=’07/19/2002′

Export: Release 9.0.1.1.1 – Production on Sat Jul 20 20:22:47 2002

(c) Copyright 2001 Oracle Corporation. All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 – Production
With the Partitioning option
JServer Release 9.0.1.1.1 – Production
EXP-00008: ORACLE error 1843 encountered
ORA-01843: not a valid month
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully

The parameter or option FLASHBACK_TIME in the export command
requires the time to be in the following fixed format

YYYY-MM-DD HH24:MI:SS (military time)

M:oracleora91BIN>exp system/manager@know9i full=y file=c:exp_20020720180000.dmp flashback_time=’2002-07-20 07:00:00P’

Export: Release 9.0.1.1.1 – Production on Sat Jul 20 20:40:54 2002

(c) Copyright 2001 Oracle Corporation. All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 – Production
With the Partitioning option
JServer Release 9.0.1.1.1 – Production
EXP-00008: ORACLE error 1830 encountered
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully

M:oracleora91BIN>exp system/manager@know9i full=y file=c:exp_20020720180000.dmp flashback_time=’2002-07-20 18:00:00′

Export: Release 9.0.1.1.1 – Production on Sat Jul 20 20:41:23 2002

(c) Copyright 2001 Oracle Corporation. All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 – Production
With the Partitioning option
JServer Release 9.0.1.1.1 – Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export the entire database …

If the snapshot is not available export throws up error message saying that the snap shot is not available. To have yesterday’s snapshot available better to set the undo retention time to 86400 seconds which is equal to one day. But in this context the space should be available on the database always to hold a days undo data. This is a great over head which should be thought of before deciding. The overhead is proportionately related to the transactions and the space requirement.


Archive Log Destinations and Destination States
(log_archive_dest_n and log_archive_destination_state_n)

These parameters are valid only with Enterprise version

Circuits

CIRCUITS specifies the total number of virtual circuits that are available for inbound and outbound network sessions. It is one of several parameters that contribute to the total SGA requirements of an instance.

Drs_start

DRS_START enables Oracle to determine whether or not the DRMON (Disaster Recovery Monitor) process should be started. DRMON is a non-fatal Oracle background process and exists as long as the instance exists.

If the site is never going to be configured in a disaster recovery configuration, you can leave the parameter unspecified and accept the default value of false. If the site is part of a disaster recovery configuration, automatic startup of the DRMON process is simplified by specifying DRS_START = true in the initialization parameter file.

SPFILE


Providing complete path and name of the file for generating the server parameter file sets this parameter. This is the persistent parameter file in the database.

The creation of the spfile:

While finalizing the initialization parameters if this is included in the init<sid>.ora file along with the database spfile is created.

If the spfile is not created at time creating the database, it can be created by issing create spfile command.

Oracle enables to modify or change the parameters that effect the database online and those changes cannot recycled over the startup and shutdown of the instance unless the initialization parameter file is suitably modified.

But, now, with the introduction of the SPFILE parameter an ability to create persistent changes for the online modifications to the parameters over the instance startup and shutdowns. A new clause SCOPE is added to alter system set …command.
If the changes are to be mode for the life of the instance or to be made persistent or to be effected in both ways there are sub clauses.

scope=spfile

This setting indicates that the change is made in the server parameter file. When changing the value of a static parameter, you must use this setting. The change takes effect when the database instance is restarted.The initialization parameters that are found only in Oracle 9i database

scope=memory

This setting indicates that the change is made in memory and takes effect immediately. The change persists until the database is shut down. If the database instance was started using a PFILE (that is, the init<sid>.ora client-side file), then this is the only option available with the scope. If a PFILE was used to start the database, this is the default setting.

scope= both

This setting indicates that the change is made in memory as well as in the server parameter file. The change takes effect immediately and persists after the database is restarted. If an SPFILE was used to start the database, this is the default setting.

Fast Start Mean Time To Recover parameters to recover from instance failures:

fast_start_mttr_target —-integer——- 300

This parameter was introduced in Oracle9i.
It replaces FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL in Oracle8i,
although the old parameters can still be set if required in Oracle9i.

· FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform crash recovery of a single instance. When specified, FAST_START_MTTR_TARGET
o Is overridden by FAST_START_IO_TARGET
o Is overridden by LOG_CHECKPOINT_INTERVAL


fast_start_parallel_rollback—– string——– LOW

Oracle says:

· FAST_START_PARALLEL_ROLLBACK determines the maximum number of processes that can exist for performing parallel rollback. This parameter is useful on systems in which some or all of the transactions are long running.


· Values:
o FALSE indicates that parallel rollback is disabled
o LOW limits the number of rollback processes to 2 * CPU_COUNT
o HIGH limits the number of rollback processes to 4 * CPU_COUNT


log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE

SGA

(1) db_cache_size

this is the default buffers for the database block
(2) db_nk_cache_size

if different size data blocks are used like 2k,4k, 8k,16k, 32k

(3) db_cache_advice

Oracle9i New features for Dynamic SGA introduces multiple buffer pools in the
memory. Hence the DBA may want to analyze the performance of each of these pools
and set the appropriate sizes.

This feature can be enabled/disabled by using the following initialization parameter:

DB_CACHE_ADVICE = OFF / ON / READY

(4) db_keep_cache_size this is the buffer pool size in the earlier versions

(5) db_recycle_cache_size this is the buffer_pool_recycle in the earlier versions.

(5) sga_max_size set the maximum size the sga can grow.

PGA and UGA

Before we go ahead to discuss the new parameters that effect the PGA and UGA, let us reacquint ourselves with those terms

PGA Process Global Area is private to a single process or thread, and is not possible to access from other processes/threads.

UGA User Global area is associated with the sessions a user opens. It is found either in SGA or PGA depending upon whether you are running in MTS Multi Threaded Server mode or Dedicated Server Mode. In the case of MTS it is in SGA and in case of Dedicated Server it is in PGA.

(1) workarea_size_policy

This parameter can be set at system level or session level. This parameter establishes whether given session’s PGA size should sized automatically or via the database using init.ora parameters. If this parameter is set to ‘auto’, the database will size the tunable PGA memory while aggregate allocated amount of PGA will be bounded by pga_aggregate_target size.

(2) pga_aggregate_target


OMF Oracle Managed File System

(1) db_create_file_dest

for the database data files to be created

(2) db_create_online_redo_log_dest_n

for the database online redo log file destinations. Multiple destinations up to 5 are allowed


Undo Management

(1) undo_management auto/manual

(2) undo_tablespace name of the undo tablespace

(3) undo_retention this helps retain undo data. but costs space, helps flash query feature

(4) undo_suppress_errors this feature suppresses the errors generated.
Suppress RBU errors in SMU mode

Archive Log Destinations (useful while creating Multiple Stand By databases)

(1) log_archive_dest_n Earlier this parameter allowed 5 destinations and now allows 10.
(2) log_archive_dest_state_n Earlier this parameter allowed 5 destinations and now allows 10

Standby Database

(1) standby_file_management

(2) standby_preserves_names

Recovery Management and Disaster Recovery Management

(1) drs_start values are set between 0 to 3600 seconds
enables Oracle to determine whether or not the DRMON (Disaster Recovery Monitor) process should be started. DRMON is a non-fatal Oracle background process and exists as long as the instance exists.
Multiple instances can have different values, and you can change the values at runtime. This is in ORAC environment.
(2) fast_start_mttr_target

It replaces FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL in Oracle8i,although the old parameters can still be set if required in Oracle9i.

PL/SQL (which are of more interest to developers)

(1) plsql_compiler_flags
(2) plsql_native_c_compiler
(3) plsql_native_library_subdir_count
(4) plsql_native_library_dir
(5) plsql_native_linker
(6) plsql_native_make_file_name
(7) plsql_native_make_utility

These parameters are introduced in Oracle 9i to speed up the execution of the PL/SQL code by compiling them into native code residing in shared libraries. If these parameters are set the settings are stored in the dba_stored_settings , all_stored_settings and user_stored_settings.

NLS parameters

(1) nls_length_semantics
(2) nls_nchar_conv_excp
(3) nls_timestamp_format
(4) nls_timestamp_tz_format


Log Miner

(1) logmnr_max_persistent_sessions

Server Parameter File

spfile

This parameter can be set in the init.ora file by specifying the name of the file with path

else spfile can be created by using the following command

create spfile from pfile;
or
create spfile=’file name and path ‘ from pfile=’file name with path’;

Oracle has enabled the users to dynamically modify many parameters at the system level and session level and still they can be made persistent by including those modifications to the server parameter file. To enable the user to make the modifications to the database parameters a new cause is introduced to ‘alter system’ command.

scope this clause enables the user to define the impact and persistence of the modification/s made to the database parameters either confine to the memory ( means confining the impact of the modification to that particular cycle of start and shutdown only by not making them persistent) or make it for the database through spfile (for the future cycles of startup and shutdown and not for the present cycle of startup and shutdown) or make it for both ( means for this cycle and future cycles of the database startup and shutdown).

alter system set db_cache_size=1024M scope=[both][spfile][memory];

Standby Database:

There are lots of enhancements introduced in the Standby Database Management in Oracle 9i. These changes and /or enhancements are introduced through Oracle Data Guard.

The following initialization parameters are introduced in the process of enhancement of Oracle Data Guard, which now takes acre of Standby Database Management.

Log Sequence Gap Detection and Recovery is managed by the following two parameters to be introduced in the initialization parameter file.

(1) fal_server fal – stands for – Fetch Archive Log Server
(2) fal_client
This parameter configures Fetch Archived Log Server Process on Standby Database site and contains the Net Service Name for the Server and like wise Net Service Name of the Standby Database for the Client. These parameters are to be included in the parameter file of the Standby Database only.

log_archive_dest_n parameter enables to define multiple archive log destinations.
archive_lag_target parameter is set to greater frequency of transfer of archive log files to the
standby database. The value is set in seconds. Maximum number of seconds of redo the standby could lose is set by this parameter.


There are many other new parameters that are introduced for the Oracle Parallel Server (OPS) which is called Oracle Real Application Clusters (ORAC).