Rename or Move Data file

Yesterday one of the DBA did a mistake and now we want to move the data file to other location.

We have There is two option for rename datafiles.
Option:1

1. shutdown the database

2. Rename or move datafiles at file system

3. startup mount the database

4. rename datafiles in the database
SQL> alter database rename file '/db/u01/oradata/imerge1/dbuser_01.dbf' to '/db/u03/oradata/imerge1/dbuser_01.dbf';

5. Open the database

Option: 2

1. Make the tablespace offline to rename or move datafiles
SQL> alter tablespace dbuser offline;

2. Rename or move datafiles at file system

3. Now we need to rename or move the datafiles in the database
SQL> alter tablespace TBS_NAME rename datafile
'/db/u01/oradata/imerge1/dbuser_01.dbf' to '/db/u03/oradata/imerge1/dbuser_01.dbf';

4. Online the tablespace which was made offline.
SQL> alter tablespace dbuser online;

I think that this will help all of us in the feature....

Reducing database fragmentation

Extremely fragmented tables or indexes can harmfully affect performance. By using the following SQL statement you can identify those database objects that have over 10 extents allocated:

select * from dba_segments where extents > 10;

And if a table or index has more than 10 extents then rebuild it to fit into one extent. A table can only be rebuilt by import/export utility. But to do it we need a downtime from application team because database will be unavailable. We can proceed with the below mentioned steps to complete the activity successfully.

1. Export the table with argument COMPRESS=Y
2. Drop the table
3. Import the table.

The entire index can be rebuilt without preventing others from still using it.

Change the storage parameters to make the ‘next’ storage parameter larger (possibly double it). The initial storage value cannot be changed and later on rebuild the index

Please correct me if I am wrong, Friends.........

Some Process & Parameter Details

1. RFS (Remote File Server): Receives archived and/or standby redo logs from the primary database.

2. MRP (Managed Recovery Process): Applies archive redo log information to the standby database.

3. FAL_CLIENT : Specifies the name of the Fetch Archive log client used by FAL Services (e.g. Production)

4. FAL_SERVER : Specifies the name of the Fetch Archive log server for the standby database and must point to the FAL Services (e.g. Standby)

5. STANDBY_FILE_MANAGEMENT : (MANUAL/AUTO) Specifies Whether automatic standby file management is enabled.

How to Tune Log Buffer

We need to determine the sapce request ratio to tune the value of LOG_BUFFER, this is the ratio of redo log space requests to redo log requests:

Select Round(e.values/s.values,5) "Redo Log Ratio"
From v$sysstat s, v$sysstat e Where s.name = 'redo log space request' and e.name
= 'redo entries';

If (redo log space requests / redo entries) is greater than 1:5000 (i.e. 0.0002 in the above), then we need to increase the size of the redo log buffer until the space request ratio stops falling.

Alternately, if memory is not a restriction then try to decrease the number of period that a process had to wait for the log cache to be flushed:

Select name, value from
v$sysstat Where name = 'redo log space requests';

The number of waits should always be zero. If not, increase the size of LOG_BUFFER, until the number returns to zero. Normally there is no benefit in setting this beyond 1M.

Execute the below mentioned query to find out how long processes had to wait as well as the number of periods:

Select name, value from v$sysstatWhere name in ('redo
log space requests', 'redo log space wait time');

This shows the time in units of 10 milliseconds. Watch out that because of the time granularity, 'redo log space requests' may be greater than zero at the same time 'redo log space wait time' is zero. This happens if none of the waits were for 10ms or longer. Until you have a fully loaded server having 'redo log space wait time' of (or near) zero may show an acceptable level of tuning.

Library Cache Hit Ratio

You can calculate Library Cache hit ratio by executing the following query:

Select sum(pinhits) / sum(pins) "Hit
Ratio", sum(reloads) / sum(pins) "Reload percent" From
v$librarycacheWhere namespace in('SQL AREA', 'TABLE/PROCEDURE', 'BODY',
'TRIGGER');

Cache hit ratio should be at least 85% (i.e. 0.85) and reload percent should be very low, 2% (i.e. 0.02) or less. If this output is not the case, increase the initialisation parameter SHARED_POOL_SIZE. Although less likely, the init.ora parameter OPEN_CURSORS may also need to increased.

OraNA.info Registration News

Hi Friends,

This is to inform you that recently we had registered our blog at OraNA.info website which is known as Oralce News Aggregator.

Thanks,

IngressIT

Compressing Archive Redo File in Oracle 11g

Hi Friends,
Yesterday working with Oracle 11g, I found something intresting which is really helpful to DBA when we are going to run out of space at archive file mount point.

Now Archived Redo file can be compressed , while transfering to standby database

alter system set log_archive_dest_1='SERVICE=standby1
compression=ENABLE';

To check wheather archive file compression is enabled or not

select dest_name , compression from v$archive_dest;

compression column will show enable if it is enable