Unable to open Log File-EXPDP & IMPDP error

ORA-39002
ORA-39070
ORA-29283
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283

Check out expdp with default directory.If this ok.Then check out drive security.There is no SYSTEM user in SECURITY Tab.Add system user to SECURITY.Then try.

Cheers!!!

ORA-01000 maximum open cursors exceeded

Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.

Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.

ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH;

Try to find out application to see why cursors are staying open.

LIBRARY CACHE HIT RATIO AND EXECUTION MISSES

The following SQL statement help you to calculate the library cache hit ratio:

SELECT
SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM
V$LIBRARYCACHE;

If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses by increasing the shared pool size.

SHARED POOL TUNING-CACHE HIT RATIO.

We can simply find out cache hit ratio.Put this below query in to your sql window.

SELECT 'Buffer Cache' NAME,
ROUND ( (congets.VALUE + dbgets.VALUE - physreads.VALUE)
* 100
/ (congets.VALUE + dbgets.VALUE),
2
) VALUE
FROM v$sysstat congets, v$sysstat dbgets, v$sysstat physreads
WHERE congets.NAME = 'consistent gets'
AND dbgets.NAME = 'db block gets'
AND physreads.NAME = 'physical reads'
UNION ALL
SELECT 'Execute/NoParse',
DECODE (SIGN (ROUND ( (ec.VALUE - pc.VALUE)
* 100
/ DECODE (ec.VALUE, 0, 1, ec.VALUE),
2
)
),
-1, 0,
ROUND ( (ec.VALUE - pc.VALUE)
* 100
/ DECODE (ec.VALUE, 0, 1, ec.VALUE),
2
)
)
FROM v$sysstat ec, v$sysstat pc
WHERE ec.NAME = 'execute count'
AND pc.NAME IN ('parse count', 'parse count (total)')
UNION ALL
SELECT 'Memory Sort',
ROUND ( ms.VALUE
/ DECODE ((ds.VALUE + ms.VALUE), 0, 1, (ds.VALUE + ms.VALUE))
* 100,
2
)
FROM v$sysstat ds, v$sysstat ms
WHERE ms.NAME = 'sorts (memory)' AND ds.NAME = 'sorts (disk)'
UNION ALL
SELECT 'SQL Area get hitrate', ROUND (gethitratio * 100, 2)
FROM v$librarycache
WHERE namespace = 'SQL AREA'
UNION ALL
SELECT 'Avg Latch Hit (No Miss)',
ROUND ((SUM (gets) - SUM (misses)) * 100 / SUM (gets), 2)
FROM v$latch
UNION ALL
SELECT 'Avg Latch Hit (No Sleep)',
ROUND ((SUM (gets) - SUM (sleeps)) * 100 / SUM (gets), 2)
FROM v$latch;

ORA-00020: maximum number of processes (150) exceeded.

Maximum number of default process is 150.when that abouve error is happened..we can increase our max number of process by

alter system set processes=160 scope=spfile;

Flashback Table in 10g

Reinstating an accidentally dropped table is effortless using the Flashback Table feature in Oracle Database 10g

Here's a scenario that happens more often than it should: a user drops a very important table--accidentally, of course--and it needs to be revived as soon as possible. (In some cases, this unfortunate user may even have been you, the DBA!)

Oracle9i Database introduced the concept of a Flashback Query option to retrieve data from a point in time in the past, but it can't flash back DDL operations such as dropping a table. The only recourse is to use tablespace point-in-time recovery in a different database and then recreate the table in the current database using export/import or some other method. This procedure demands significant DBA effort as well as precious time, not to mention the use of a different database for cloning.

Enter the Flashback Table feature in Oracle Database 10g, which makes the revival of a dropped table as easy as the execution of a few statements. Let's see how this feature works.

See this,
SQL> drop table test;
We can see that particular table in recyclebin

SQL> show recyclebin;
We can recover that table simply by Flashback command:
SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;
After that check the Table by
SQL>select * from tab;
We can see that table came back

Cheers!

Automatic Storage Management (ASM) in Oracle Database 10g

Automatic Storage Management (ASM) is a new feature that has be introduced in Oracle 10g to simplify the storage of Oracle datafiles, controlfiles and logfiles


Overview of Automatic Storage Management (ASM)Automatic Storage Management (ASM)

simplifies administration of Oracle related files by allowing the administrator to reference disk groups rather than individual disks and files, which are managed by ASM. The ASM functionality is an extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems, along with OMF and manually managed files.
The ASM functionality is controlled by an ASM instance. This is not a full database instance, just the memory structures and as such is very small and lightweight.
The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are know as ASM files. The locations and names for the files are controlled by ASM, but user-friendly aliases and directory structures can be defined for ease of reference.
The level of redundancy and the granularity of the striping can be controlled using templates. Default templates are provided for each file type stored by ASM, but additional templates can be defined as needed.
Failure groups are defined within a disk group to support the required level of redundancy. For two-way mirroring you would expect a disk group to contain two failure groups so individual files are written to two locations.

Manages groups of disks, called disk groups. Manages disk redundancy within a disk group. Provides near-optimal I/O balancing without any manual tuning. Enables management of database objects without specifying mount points and filenames. Supports large files.

Initialization Parameters and ASM Instance CreationThe initialization parameters that are of specific interest for an ASM instance are:
INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS. DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.

ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.
ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.