Database Administration Exercises - Week 3

I N D I V I D U A L     E X E R C I S E S

HANDS-ON #1: Dictionary Views to help you work

Using your text and any other sources you wish, make a list of the data dictionary views you feel would be the most helpful to a programmer who is writing applications that perform DML actions on rows from tables in the database. Give a brief explanation for your choice of each view.
Place and save your answers in a Word document named week3_exercise.doc.

HANDS-ON #2: Where are my Log Files and Groups?

You have inherited an existing database from a previous Administrator. There is little to no documentation provided for this database and, as you review the database setup, you find you need answers to the following questions:

• What are the redo log group numbers, and how many members does each group have?
• What directory or directories holds the redo log group members?

Write SQL commands or queries to discover all these answers. Place and save your answers in the Word document named week3_exercise.doc that you have started for this week's exercises.

HANDS-ON #3: Working with initialization parameters

Use your text and any online documentation you need, determine which of the initialization parameters (many are discussed in chapter 3) are static and which are dynamic. Also determine which can be changed using ALTER SESSION, and which can be changed using ALTER SYSTEM. Make a spread sheet type table with four columns for your lists (one for each category). Some parameters may appear on more than one list and that is acceptable. Use the parameters listed in the book to begin with, and then add some. You should try to have a listing of at least 20 different parameters.
Place and save your answers in the Word document named week3_exercise.doc that you have started for this week's exercises.

Related Documents

Basic Oracle Sql Exercise

Practice Exercise for Oracle PL/SQL

Oracle Procedure and Function Practice

Exercise Oracle package and package body

Database design practice for a Movie Company

Oracle Enterprise Manager Excercises

Oracle Database Installation Exercises

Oracle Database Creation Exercises

Database Administration Exercises - Week 3

Temporary Tables and Data Types Exercises

Exercises with Oracle Create Table Add Columns

Practice with User Profiles and User Roles in Oracle

Practice Oracle Joins Examples

Oracle Object type Exercises - VARRAY

Audit and Profile Management Exercises in Oracle

What will happen if oracle unable to write Alertlog, Core Dump Or Tracefiles

In this I will show what can happen if Oracle is unable to write to the trace/alert.log to bdump or cdump & udump directories. It can happen if you loss your background_dump_dest or user_dump_dest or core_dump_dest ormay the the partition containing bdump or udump folder is full.

The answer is if oracle is unable to write to the trace/alert.log to bdump & udump directories then the oracle behavior depends on which process is attempting to write to alert.log/trace/coredump file. Based on the process the instance may or may not crash immediately.

If a foreground process corresponding to user process wants to write, but is unable to do so, the process may hang/terminate but there will not be any impact on database.

But if the background process wants to write it may hang and eventually crash the instance if that background process terminates.

In the following section I demonstrate this behavior on 10.2g

1.Start database with spfile Create one bdump directory
SQL> !mkdir /oradata2/bdump

2.Set the background_dump_dest to this location.
SQL> alter system set background_dump_dest='/oradata2/bdump';
System altered.

SQL> show parameter background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oradata2/bdump

3.Switch logfile. It will generate one trace file in the background_dump_dest.
SQL> alter system switch logfile;
System altered.

SQL> !ls /oradata2/bdump
arjudba_arc0_10688.trc

4.Move the bdump location to a new location. Here bdump_bak.
SQL> !mv /oradata2/bdump /oradata2/bdump_bak

SQL> !ls /oradata2/bdump
/oradata2/bdump: No such file or directory

5.Now you may face one of two scenario a) or b).

a)If database is open then Instance will crash
SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

b)If database is shutdown startup will fail with ORA-00444 and ORA-07446.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORA-00444: background process "MMAN" failed while starting
ORA-07446: sdnfy: bad value '' for parameter .

So if you try to write alert log file while instance is up and running it may fail and eventually crush the instance.

However, for a foreground process the database will not crash, if it is unable to write to alert.log.

How to resolve ORA-4030 errors on UNIX

Reasons of ORA-04030
When a client program connects to oracle database an oracle process does work on behalf of the client. The Oracle process allocated memory from PGA(Program Global Area). For dedicated server process PGA contains stack, user session data, cursor information and the sort area. For shared server configuration UGA(user session data, cursor information and the sort area) is allocated in the SGA (System Global Area) and will not be responsible for ORA-4030 errors.

The ORA-4030 can come based on various reasons which is noted below.
1)The Oracle process need more memory in order to request client program and it requests additional memory from Operating System but the Operating System can't serve the request. This is likely to happen if OS does not have enough memory or swap space is not available.

2)There is a memory limit restriction from OS for oracle process to use. Oracle process already use the limit and now it requested additional memory and hence error appears.

3)Might be an oracle bug.

Solution of The Problem
1)Reduce the PGA(Program Global Area): If you don't have enough memory on your system then reducing the PGA can help you to avoid the error. In that case oracle process will not request more memory from OS and if needed more space it will use temporary tablespace segment. But lower the PGA can impact on database performance. If you don't have PGA_AGGREGATE_TARGET set then in order to set lower PGA set SORT_AREA_SIZE parameter to a lower value. You can set it dynamically by,
ALTER SYSTEM SET SORT_AREA_SIZE=10M;

If you have set PGA_AGGREGATE_TARGET then to reduce PGA you have to set PGA_AGGREGATE_TARGET toa lower value.
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100M;

2)If you did not enabled automatic PGA management then enable the automatic SQL execution memory management feature by setting the parameter WORKAREA_SIZE_POLICY to AUTO and by specifying a size of PGA_AGGREGATE_TARGET then it will lessen to happen ORA-4030 error. As by setting automatic SQL execution memory management feature SORT_AREA_SIZE, HASH_AREA_SIZE are handled by oracle automatically and allocate/deallocate by demand.

ALTER SYSTEM SET WORKAREA_SIZE_POLICY=AUTO;

3)If you impose limit to oracle to use memory then increase the amount of memory a UNIX process can request and use from the operating system. The system administrator can see this soft limit
by issuing,
$limit
In order to see hard limit issue,
$limit -h

If you want to increase a specific resource soft limit by specifying it's name and the new value then on
C shell
use,
csh> limit -h datasize 524289
csh> limit -h datasize
To make all resource unlimited issue,
csh>unlimit
If you use
Bash and Korne Shell
then use,
$ulimit -a
In order to set all resource to unlimited.

4)Increase the amount of swap space available on the system. You should have 2-3 times the amount of physical memory available as swap space.

5)Increase the physical RAM on your system if you have lower RAM. If you don't have the scope to increase your RAM then lower the SGA settings. Lowering SGA size will allow to use more PGA.

6)If some PL/SQL procedures need much memory then you might think to rewrite it so that it needs less memory.

7)Install the latest patch set.

Possible Limitations When Adding Datafiles to a Tablespace

Before discussing limitations of adding datafiles to a tablespace let's think about DB_FILES parameter. DB_FILES is an initialization parameter in oracle. When an oracle instance starts, this parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance. This parameter exists throughout the life cycle of the instance. So, changes to DB_FILES affect only after restarting the instance.

As this parameter indicates the amount of SGA space to reserve for datafile information the bigger value settings indicates memory is unnecessarily consumed.

And smaller value setting of this parameter impose limit to create another datafile.

So setting of DB_FILES parameter value is important.

Now let's look about possible limitations when adding datafiles to a tablespace.

Possible Limitations When Adding Datafiles to a Tablespace

•OS imposes a limit of the number of files a process can be opened at a time.

•OS imposes a limit of number and size of datafiles.

•The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.

•Number of datafiles can't exceed DB_FILES initialization parameter.

•It is good to know that after oracle 8i the MAXDATAFILES parameter (of CREATE CONTROLFILE or CREATE DATABASE command) is not responsible of the limitations of adding datafiles.

•If you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.

Related Documents
MAXDATAFILES , DB_FILES parameters and ORA-00059

How to Resize a Datafile

ORA-01667: cannot add any more tablespaces: limit of exceeded

New Feature of 10.2g: Eliminate Control File Re-Creation

When and How to Recreate the Controlfile

MAXDATAFILES , DB_FILES parameters and ORA-00059

Problem Description
Whenever I try to create tablespace it fails with error message, ORA-00059.

SQL> create tablespace test_tbs;
create tablespace test_tbs
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

Cause of The Problem
You have reached the limit of DB_FILES parameter. Before entering into solution part let's have an idea about DB_FILES and MAXDATAFILES parameter.

The DB_FILES parameter limits the maximum number of datafile can exist in oracle database. We can't change this parameter dynamically. We have to change it spfile by using ALTER SYSTEM .... SCOPE=SPFILE or in the pfile.

And the MAXDATAFILES parameter you can find with the CREATE DATABASE command or in CREATE CONTROLFILE command. It is also a limitation of maximum number of datafiles can be in the datafile. But starting from oracle 8 this hard limit parameter can be easily expanded up to DB_FILES parameter. So, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.

So, if you are after oracle 8i then we should just forget about MAXDATAFILES parameter. We should rather think about DB_FILES parameter.

Solution of The Problem
If you use pfile to startup the database then edit pfile and add/modify the DB_FILES parameter to a greater value so that it can accommodate higher number of datafiles. Then start the database using that pfile.

If you use spfile to startup the database then issue,

SQL> alter system set db_files=300 scope=spfile;
System altered.

Then start your database and either create tablespace or add datafile to an existing tablespace. Hopefully it will work.

SQL> conn / as sysdba
SQL> startup force
SQL> CREATE TABLESPACE or ALTER TABLESPACE command to add datafile.
Related Documents
http://arjudba.blogspot.com/2008/07/ora-01667-cannot-add-any-more.html
http://arjudba.blogspot.com/2008/09/list-of-parameters-that-must-have.html
http://arjudba.blogspot.com/2008/08/possible-limitations-when-adding.html

How to monitor alert log file in Oracle

We know in a UNIX system with tail -f we can monitor alert log file to see how alert log file is populated. For example the latest happenings in alert log file can be seen by,
$tail -f background_dump_dest location/alert_$ORACLE_SID.ora

But in windows there is not tail command. We can monitor the alert log within oracle itself regardless of platform. This can be archived by following steps.

1)Create an External Table to read the alert log.

column a_log new_value ALOG noprint
column value new_value bkgd_dmp noprint

select 'alert_'||instance_name||'.log' a_log
from v$instance;

select value
from v$parameter
where name = 'background_dump_dest';

create or replace directory data_dir as '&&bkgd_dmp';

CREATE TABLE alert_t
(
text_line varchar2(255)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(
records delimited by newline
fields
REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION
(
'&&ALOG'
)
)
REJECT LIMIT unlimited;

2)Query from the External Table.

For example, you can then query the last 21 lines of the alert log in this manner:

select text_line
from (
select rownum rn, text_line
from alert_t
)
where rn between (select max(rn) - 20 from (select rownum rn from
alert_t))
and (select max(rn) from (select rownum rn from alert_t));

Related Documents
What is Alert Log?
What is Server-Generated Alerts?

How to know database initialization parameter in oracle

1)SHOW PARAMETER option: If you use SQL*Plus then you can use SHOW PARAMETER parameter_name to display the values of parameters currently in use. Note that with toad or tora you can't use it as it is SQL*plus option.

SQL>SHOW PARAMETERS sga_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 160M

2)Query from V$PARAMETER: You can query value column of this view to display the values of parameters currently in effect.

SQL> SELECT VALUE FROM v$PARAMETER WHERE name='sga_target';
VALUE
--------------------------------------------------------------------------------
167772160

3)Query from V$PARAMETER2: You can query value column of this view to display the values of parameters currently in effect. It is easier to distinguish list parameter values in this view because each list parameter value appears as a row.

4)Query from V$SPPARAMETER: You can query value column of this view to display the current contents of the server parameter file. The view returns FALSE values in the ISSPECIFIED column if a server parameter file is not being used by the instance.

5)Query from V$SYSTEM_PARAMETER: In this view it displays what will be the value if a new session inherits parameter values from the instance-wide values.

6)Query from V$SYSTEM_PARAMETER2: It displays the information about initialization parameters that are currently in effect for the instance, with each list parameter value appearing as a row in the view. A new session inherits parameter values from the instance-wide values.

Related Documents
Difference between V$parameter and v$spparameter

ORA-00019: maximum number of session licenses exceeded

Problem Description
Normal user can't connect to oracle database. Whenever they try to connect to database it fails with ORA-00019.

SQL> conn india/a
ERROR:
ORA-00019: maximum number of session licenses exceeded

Solution of The Problem
1)Log on to database as a DBA user.
SQL> conn arju/a
Connected.

2)Check the license_max_sessions parameter.
SQL> show parameter license_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
license_max_sessions integer 1

3)Increase the value of license_max_sessions parameter.
SQL> alter system set license_max_sessions=100;
System altered.

4)Test database to connect with the normal user.
SQL> conn india/a
Connected.

ORA-00018, ORA-00020 maximum number of sessions exceeded

Error Description
Any operation or a new session creation is failed. Like,
ORA-00018: maximum number of sessions exceeded

ORA-00020: maximum number of processes (string) exceeded

In order to solve the problem you have to increase the value of the PROCESSES/ SESSIONS initialization parameter.

You can see the current settings of the SESSIONS and PROCESSES parameter value by querying from v$spparameter or by simply show parameter parameter_name.
SQL> col name format a30
SQL> col value format a10
SQL> select name, value from v$spparameter where name in ('processes','sessions');

NAME VALUE
------------------------------ ----------
processes 150
sessions

The explicitly value of the sessions parameter is not set and so it is left to blank. It's default value is derived from the processes parameter and value=1.1*process_parameter_value+5
You can calculate the default value of sessions parameter by,

SQL> select 1.1*value+5 "sessions par default value" from v$spparameter where name='processes';

sessions par default value
--------------------------
170

In order to change the value of the sessions you have to change it in spfile or pfile. Dynamically it can't be changed.

How to Solve the Problem

If your database start with spfile then,
Alter system set sessions=200 scope=spfile;
shutdown immediate;
startup;
If your database start with pfile then,
Open the pfile with an editor and edit the sessions parameter value and restart your database.

Similarly you can set the processes parameter.

Related Documents
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/07/process-and-runtime-limits.html
http://arjudba.blogspot.com/2008/04/user-resource-limits-in-oracle.html

http://arjudba.blogspot.com/2009/11/ora-01012-not-logged-on.html

Process and Runtime Limits

1)Instances per database
Maximum number of cluster database instances per database: Operating system-dependent

2)Locks
Row-level: Unlimited
Distributed Lock Manager: Operating system dependent

3)SGA size
Maximum value: Operating system-dependent;
For 32 bit OS, 2GB - 4GB.
For 64 bit OS, >4GB.

4)Advanced Queuing Processes
Maximum per instance: 10

5)Job Queue Processes
Maximum per instance: 1000

6)I/O Slave Processes
Maximum per background process (DBWR, LGWR, ....): 15
Maximum per Backup session: 15

7)Sessions
Maximum per instance: 32 KB; Restricted by the PROCESSES and SESSIONS initialization parameters.

8)Global Cache Service Processes
Maximum per instance: 10

9)Shared Servers
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.

10)Dispatchers
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.

11)Parallel Execution Slaves
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.

12)Backup Sessions
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.

Related Document
Physical Database Limits in oracle
Datatype Limits in Oracle
Logical Database Limits in Oracle

Archiving not possible: No primary destinations

Error Description:
Today I got a very interesting problem. The developer told me that they can't connect to database. It says ORA-00257: archiver error. Then I log on to the database and it was ok. I asked developer it is fine. After some time developers again asked me they are having the same problem. I then look for alert log and saw the following stack of message.

ARC0: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 1192 (4)

Cause of The Problem:
Unknown yet. Possibly hit oracle bug.

Solution of The Problem:
At first seems I guessed there is space issue and look for following thing as in described,
ORA-00257: archiver error. Connect internal only, until freed.

Step1: Look for archival destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1203
Next log sequence to archive 1205
Current log sequence 1205
SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata1
db_recovery_file_dest_size big integer 50G

I also checked for settings whether LOG_ARCHIVE_DEST_10 is implicitly set or not. Yet it was set.
SQL> select DEST_NAME,DESTINATION from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_10';

DEST_NAME
--------------------------------------------------------------------------------
DESTINATION
--------------------------------------------------------------------------------
LOG_ARCHIVE_DEST_10
USE_DB_RECOVERY_FILE_DEST

Step2: Let's check space in flash recovery area.
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/oradata1
5.3687E+10 32381952 0 2

So it was fine. I suddenly look for alert log and it was changed to

ORA-16038: log 2 sequence# 1160 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 2 thread 1: '/oradata1/arju/ARJU/redo02.log'

I immediately try to clear the logfile as it is described in ORA-16038,ORA-00354,ORA-00312 corrupt redo log block header
SQL> alter database clear unarchived logfile group 2;
Database altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
^C alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

But no hope. It stopped and in alert log again it shows error message Archiving not possible: No primary destinations.

Step3: I set LOG_ARCHIVE_DEST_9 explicitly to DB_RECOVERY_FILE_DEST

With the following statement whenever I explicitly set LOG_ARCHIVE_DEST_9 to use DB_RECOVERY_FILE_DEST then the error gone.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_9='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.

In the alert log I put tail -f and wanted to see that status. Immediately status becomes as follows,

Cleared LOG_ARCHIVE_DEST_10 parameter default value
Mon Jul 21 05:52:04 2008
ALTER SYSTEM SET log_archive_dest_9='LOCATION=USE_DB_RECOVERY_FILE_DEST' SCOPE=BOTH;
Mon Jul 21 05:52:04 2008
Archiver process freed from errors. No longer stopped

And error gone. I tested with alter system switch logfile and it went normal.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

I don't know what happened. Possibly this is oracle bug. Later I explicitly set log_archive_dest_9 to use LOCATION USE_DB_RECOVERY_FILE_DEST and unset log_archive_dest_9 and it went normal.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_9='';
System altered.

Related Documents

ORA-16038,ORA-00354,ORA-00312 corrupt redo log block header
ORA-00257: archiver error. Connect internal only, until freed.

Database Startup fails with error ORA-16038,ORA-19809, ORA-00312

Error Description:
Whenever you try to startup the database it fails with error ORA-16038,ORA-19809, ORA-00312.
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 117440552 bytes
Database Buffers 41943040 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-16038: log 3 sequence# 572 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/oradata2/data1/dbase/redo03.log'

Or in mount stage whenever you try to open the database it fails with error ORA-16014, ORA-00312.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16014: log 3 sequence# 572 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/oradata2/data1/dbase/redo03.log'

Cause of The Problem:
----------------------------------------
There was an attempt to archived the online log 3 but it could not archive the online log in the available archived log destination. The most common of happening the error is the archive log destination if full. You have flash recovery area configured and rman retention policy is failed to delete any archived or incremental backups and so can't archived new online log.

Solution of The Problem:
-------------------------------------------
Solution A:(Enough space on the drive)
1)One more check the alert log. (Not needed though)
An extra check you can do in alert log which is in background_dump_dest/alert_$ORACLE_SID.log
SQL> show parameter background_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oracle/app/oracle/product/10.
2.0/db_1/admin/dbase/bdump
$less /oracle/app/oracle/product/10.2.0/db_1/admin/dbase/bdump/alert_dbase.log
You may see the same entry is in the alert log.

2)Check the archive destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 572
Next log sequence to archive 572
Current log sequence 580

So archived log destination is DB_RECOVERY_FILE_DEST. You can see the exact destination in OS by,
SQL> show parameter db_recover

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata2/flash_recovery_area
db_recovery_file_dest_size big integer 10G

3)Increase the value of db_recovery_file_dest_size
As archive destination is full so increase the size.
SQL> alter system set db_recovery_file_dest_size=20G;
System altered.

4)Open the database now.
SQL> alter database open;
Database altered.

Solution B: Have not enough space on the drive
If you have not enough space in your disk and you have recent backup of your database and archive log is not needed then you can issue
$rman target /
RMAN>DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-2';

Solution C: Have not any backup
If you have not any recent backup then backup database to another location and delete archivelogs.

To do this,
$rman target /
RMAN>backup format '/oradata2/%U' archivelog all delete input database;

Solution D: Have recent backup and only need archivelog

In this case backup the archive log to another location and delete archive log from flash recovery area.
You can do this by,

$rman target /
RMAN> backup format '/oradata2/%U' archivelog all delete input;

ORA-1000 Maximum Number of Cursors Exceeded

Error Description:
----------------------------------
Whenever you run any procedures or functions or any other application or transaction either database level or application level you get the following error,

ORA-01000: "maximum open cursors exceeded"

Cause of The Problem:
--------------------------------
The initialization parameter OPEN_CURSORS specifies the maximum number of open cursors a session can have at once. To run a application the program needs to open more cursor in a session than the OPEN_CURSORS and error arises.

To process a SQL statement, Oracle opens a work area called a private SQL area. This private SQL area stores information needed to execute a SQL statement. Cursors are stored in this area to keep track of information.

There are two types of cursor named implicit cursor and explicit cursor.

An IMPLICIT cursor is declared for all data definition and data manipulation statements. They are internal to Oracle and is handled by oracle. They are harder to tune because they are internal to Oracle.

But in order to handle the queries that return more than one row, you must declare an EXPLICIT cursor to retrieve all the information. They can be tuned by determining when you will open or close them.

Solution of The Problem:
--------------------------------------
You can solve the ORA-01000 error by tuning cursor usage at the database level and at the application level.

1. Tuning at the DATABASE LEVEL
-------------------------------------------------------
The initialization parameter OPEN_CURSORS specifies the maximum number of open cursors a session can have at once. The default value of it is 50 and the value ranges from 0 to 65535. By setting the highest value of it likely solve the ORA-01000 error. Like you can issue,
SQL> ALTER SYSTEM SET open_cursors=5000;
System altered.

SQL> show parameter open_curs
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 5000


2. Tuning at the APPLICATION LEVEL
----------------------------------------------------------------------
The three parameters that affect handling cursors at the application level are RELEASE_CURSOR, HOLD_CURSOR, MAXOPENCURSORS.

The default value of HOLD_CURSOR is NO which means that after Oracle executes a SQL statement the links to the cursor cache, memory, and parse locks are released and marked for reuse.

The default value of RELEASE_CURSOR is NO which means that after Oracle executes a SQL statement, the links to the cursor cache is maintained and not released.

To resolve the ORA-01000 error, you should set HOLD_CURSOR=NO and RELEASE_CURSOR=YES.

DB_BLOCK_SIZE, DB_nK_CACHE_SIZE and BLOCKSIZE

First I want to say about BLOCKSIZE cluase in oracle. The BLOCKSIZE cluase is used to specify the block size for the tablespace. If you don't specify this clause while tablespace creation then standard that is default blocksize is used which is specified by parameter DB_BLOCK_SIZE. For example, You have DB_BLOCK_SIZE set to 8K=8192 and you specified CREATE TABLESPACE command without any BLOCKSIZE clause then database use 8k blocksize for the specified tablespace.

The parameter DB_BLOCK_SIZE specifies the size of Oracle database blocks in bytes. The default value of this parameter is 8192 and value ranges between 2048 to 32768. But it must be multiple of physical block size at device level.

Now lets have an attention of the DB_nK_CACHE_SIZE parameters. Here n is variable and can be 2, 4, 8, 16, 32. That is DB_2K_CACHE_SIZE, DB_4K_CACHE_SIZE, DB_16K_CACHE_SIZE etc are available. Now question may come why we will set DB_nK_CACHE_SIZE? What advantage we will get. Before look for advantage (which will be discussed in another topic) let me why we need to set this parameter? This parameter will need to be set whenever I wish to make or want to make a tablespace with non-standard data block size.

Suppose your standard block size that block size set by DB_BLOCK_SIZE is 8K and you want to make a tablespace with block size 16K then at first you need to set DB_16K_CACHE_SIZE and then you need to create tablespace with BLOCKSIZE clause specifying 16K.

Here is an example which shows database standard block size is 8k and you have made an tablespace with 16k blocksize.

Example:
------------------------------
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

ALTER SYSTEM SET DB_16K_CACHE_SIZE=200M SCOPE=BOTH;
ALTER SYSTEM SET DB_CREATE_FILE_DEST='/oradata2';
CREATE TABLESPACE TEST BLOCKSIZE 16K;

Remember You can set this parameter only when DB_BLOCK_SIZE has a value other than nK. For example, if DB_BLOCK_SIZE=8192, then it is illegal to specify the parameter DB_8K_CACHE_SIZE Because the size for the 8 KB block cache is already specified by DB_CACHE_SIZE.
Related Documents

Choose an Optimal Block Size in Oracle

Parameters that control the behavior of Query Optimizer

1)CURSOR_SHARING: •The optimizer generates the execution plan based on the presence of the bind variables but not the actual literal values.

•Based on the settings of this parameter -CURSOR_SHARING it converts literal values in SQL statements to bind variables and affect the execution plan of SQL statements.

•This parameter determines what kind of SQL statements can share the same cursors and can have any of either three values EXACT or SIMILAR or FORCE.

•If it is set to EXACT then it only allows statements with identical text to share the same cursor.

•SIMILAR settings causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized. SIMILAR is default.

•Forces specified that statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

2)DB_FILE_MULTIBLOCK_READ_COUNT:•This parameter specifies the number of blocks that are read in a single I/O during a full table scan or index fast full scan.

•The optimizer uses the value of DB_FILE_MULTIBLOCK_READ_COUNT to cost full table scans and index fast full scans.

•Larger values result in a cheaper cost for full table scans and can result in the optimizer choosing a full table scan over an index scan.

•If this parameter is not set explicitly (or is set is 0), the optimizer will use a default value of 8 when costing full table scans and index fast full scans.

3)OPTIMIZER_INDEX_CACHING:•This parameter controls the costing of an index probe in conjunction with a nested loop.

•The range of values 0 to 100 for OPTIMIZER_INDEX_CACHING indicates percentage of index blocks in the buffer cache, which modifies the optimizer's assumptions about index caching for nested loops and IN-list iterators.

•A value of 100 infers that 100% of the index blocks are likely to be found in the buffer cache and the optimizer adjusts the cost of an index probe or nested loop accordingly.

•Use caution when using this parameter because execution plans can change in favor of index caching.

4)OPTIMIZER_INDEX_COST_ADJ:•OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

•The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost.

•Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.

5)OPTIMIZER_MODE:This parameter is discussed on About Parameter OPTIMIZER_MODE
6)PGA_AGGREGATE_TARGET: This parameter automatically controls the amount of memory allocated for sorts and hash joins. Larger amounts of memory allocated for sorts or hash joins reduce the optimizer cost of these operations. This parameter is discussed on About PGA_AGGREGATE_TARGET parameter
7)STAR_TRANSFORMATION_ENABLED: •STAR_TRANSFORMATION_ENABLED determines whether a cost-based query transformation will be applied to star queries.

•If it is set to TRUE the optimizer will consider performing a cost-based query transformation on the star query.

•If it is set to FALSE the transformation will not be applied.

•If it is set to TEMP_DISABLE the optimizer will consider performing a cost-based query transformation on the star query but will not use temporary tables in the star transformation.

Parameters that enable and control Query Optimizer Features

You know that Query optimizer is responsible to determine the best execution/explain plan. In many cases you may be astonished that the data is same in both database and you have gather statistics but both database give different execution plan. The possible reason for it it the variation of initialization parameter between two database. Now in the later section in this post we will have a look at the initialization parameter that affect the optimizer to determine execution plan.

A)Enable Query Optimizer Feature
--------------------------------------------
OPTIMIZER_FEATURES_ENABLE Parameter
------------------------------------------------
•It is a string type parameter and takes oracle version number as argument.
•Based on this parameter settings it is determined how oracle optimizer behaves.
•Every new release of oracle version comes with new feature for optimizer. Thus new version of optimizer can collect extra features of a query based on which execution plan can changes. If you upgrade your oracle to newer version and your don't want to change your execution plan according to new one (keep like older) then you can set this parameter to older one.
•The valid values of this parameter can be,
8.0.0 | 8.0.3 | 8.0.4 | 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 | 8.1.5 | 8.1.6 | 8.1.7 | 9.0.0 | 9.0.1 | 9.2.0 | 10.0.0 | 10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.2.0.1|10.2.0.2|10.2.0.3| etc.


B)Control the Behavior of the Query Optimizer
---------------------------------------------------------------------------
Here is the list of initialization parameters that can be used to control the behavior of the query optimizer.

1)CURSOR_SHARING
2)DB_FILE_MULTIBLOCK_READ_COUNT
3)OPTIMIZER_INDEX_CACHING
4)OPTIMIZER_INDEX_COST_ADJ
5)OPTIMIZER_MODE
6)PGA_AGGREGATE_TARGET
7)STAR_TRANSFORMATION_ENABLED

All of these parameters are discussed in topic Parameters that control the behavior of Query Optimizer.

Use of Dynamic Sampling while estimating Statistics

The purpose of dynamic sampling is to improve query performance by determining more accurate estimates for predicate selectivity and statistics for tables and indexes. These more accurate estimates allow the optimizer to produce better performing plans.

You can use dynamic sampling to:

•Estimate single-table predicate selectivities when collected statistics cannot be used or are likely to lead to significant errors in estimation.

•Estimate statistics for tables and relevant indexes without statistics.

•Estimate statistics for tables and relevant indexes whose statistics are too out of date to trust.

The dynamic sampling feature is controlled by the OPTIMIZER_DYNAMIC_SAMPLING parameter.

The default value of OPTIMIZER_DYNAMIC_SAMPLING is depend on settings of OPTIMIZER_FEATURES_ENABLE.

•If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2

•If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1

•If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0 that means dynamic sampling feature is disabled.

How dynamic Sampling Works
--------------------------------
The primary performance attribute is compile time. Oracle determines at compile time whether a query would benefit from dynamic sampling. If so, a recursive SQL statement is issued to scan a small random sample of the table's blocks, and to apply the relevant single table predicates to estimate predicate selectivities. The sample cardinality can also be used, in some cases, to estimate table cardinality. Any relevant column and index statistics are also collected.

Depending on the value of the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter, a certain number of blocks are read by the dynamic sampling query.

When to Use Dynamic Sampling
------------------------------------------------
For a query that normally completes quickly (in less than a few seconds), you will not want to incur the cost of dynamic sampling. However, dynamic sampling can be beneficial under any of the following conditions:

•A better plan can be found using dynamic sampling.
•The sampling time is a small fraction of total execution time for the query.
•The query will be executed many times.

Dynamic sampling can be applied to a subset of a single table's predicates and combined with standard selectivity estimates of predicates for which dynamic sampling is not done.

About STATISTICS_LEVEL parameter in Oracle

•The parameter STATISTICS_LEVEL is a string type parameter and it can take any of three values- TYPICAL, ALL or BASIC.

•It specifies the level of collection for database and operating system statistics.

•The default is TYPICAL which ensures collection of all major statistics required for database self-management functionality and provides best overall performance. So if it is set to TYPICAL then we don't need to bother about collection statistics by ANALYZE or by DBMS_STATS package.

•When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.

•If the STATISTICS_LEVEL parameter is set to BASIC it disables the collection of many of the important statistics required by Oracle Database features and functionality.

•STATISTICS_LEVEL parameter can be modified by both session level(using ALTER SESSION) or system level (using ALTER SYSTEM).

•To know the current settings of the parameter issue,
SQL> select value from v$parameter where name='statistics_level';
VALUE
--------------------------------------------------------------------------------
TYPICAL

Or,

SQL> show parameter statistics_level
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

•To know the status of the statistics or advisories controlled by the STATISTICS_LEVEL parameter issue the following query,

SQL> COL STATISTICS_NAME FORMAT A50
SQL>  SELECT STATISTICS_NAME,SESSION_STATUS,SYSTEM_STATUS,ACTIVATION_LEVEL,
SESSION_SETTABLE FROM V$STATISTICS_LEVEL;

STATISTICS_NAME                                    SESSION_ SYSTEM_S ACTIVAT SES
-------------------------------------------------- -------- -------- ------- ---
Buffer Cache Advice                                ENABLED  ENABLED  TYPICAL NO
MTTR Advice                                        ENABLED  ENABLED  TYPICAL NO
Timed Statistics                                   ENABLED  ENABLED  TYPICAL YES
Timed OS Statistics                                DISABLED DISABLED ALL     YES
Segment Level Statistics                           ENABLED  ENABLED  TYPICAL NO
PGA Advice                                         ENABLED  ENABLED  TYPICAL NO
Plan Execution Statistics                          DISABLED DISABLED ALL     YES
Shared Pool Advice                                 ENABLED  ENABLED  TYPICAL NO
Modification Monitoring                            ENABLED  ENABLED  TYPICAL NO
Longops Statistics                                 ENABLED  ENABLED  TYPICAL NO
Bind Data Capture                                  ENABLED  ENABLED  TYPICAL NO

STATISTICS_NAME                                    SESSION_ SYSTEM_S ACTIVAT SES
-------------------------------------------------- -------- -------- ------- ---
Ultrafast Latch Statistics                         ENABLED  ENABLED  TYPICAL NO
Threshold-based Alerts                             ENABLED  ENABLED  TYPICAL NO
Global Cache Statistics                            ENABLED  ENABLED  TYPICAL NO
Active Session History                             ENABLED  ENABLED  TYPICAL NO
Undo Advisor, Alerts and Fast Ramp up              ENABLED  ENABLED  TYPICAL NO

16 rows selected.

As we see as the current settings is TYPICAL so 'Timed OS Statistics' and 'Plan Execution Statistics' are disabled because they enable when STATISTICS_LEVEL is set to ALL. In the query we can also see by SESSION_SETTABLE parameter indicating whether the statistic/advisory can be set at the session level (YES) or not (NO).

Hidden Parameters in Oracle. How to Change Hidden Parameter

The hidden parameters start with an "_".They can not be viewed from the output of show parameter
or querying v$parameter unless and untill they are set explicitly in init.ora.
However if you want to view all the hidden parameters and their default values the following query
could be of help,


SELECT
a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE
a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '/_%' escape '/'
/


In order to see the listing of all hidden parameters query,

select *
from SYS.X$KSPPI
where substr(KSPPINM,1,1) = '_';

Change Hidden Parameters in Oracle
---------------------------------------
It is never recommended to modify these hidden parameters without the assistance of Oracle Support.Changing these parameters may lead to high performance degradation and other problems in the database.
In order to change hidden parameter,
1)If you use pfile then in your initSID.ora you can entry of the hidden parameter and start the database.

2)If you want to use for the current session you can use ALTER SESSION SET ....

3)To set it permanently if you use spfile then use, ALTER SYSTEM SET ...... SCOPE=SPFILE. Since hidden parameter starts with underscore(_) to access it you have to specify within double quotes. If you use SCOPE=SPFILE then in order to take effect you need to restart database. You can use SCOPE=BOTH if parameter can be set in the session also.

Global_names and global_name in Oracle.

There may be at first confusing to you between the two terms global_names and GLOBAL_NAME. I will try to make you understand the difference between these two in my topic. However the detail relation between these two inshallah will be discussed in future topic. In my ORA-2085 a related problem is discussed though.

Before starting you have to be clear that global_names is a PARAMETER and GLOBAL_NAME is a VIEW or SYNONYM.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE 'GLOBAL_NAME%';
OBJECT_NAME OBJECT_TYPE
-------------------- -------------------
GLOBAL_NAME VIEW
GLOBAL_NAME SYNONYM

SQL> DESC GLOBAL_NAME;
Name Null? Type
----------------------------------------- -------- ----------------------------
GLOBAL_NAME VARCHAR2(4000)


SQL> SELECT NAME FROM V$PARAMETER WHERE NAME LIKE 'global_name%';
NAME
--------------------------------------------------------------------------------
global_names

In order to see your database global_names parameter value just issue,
SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE

Or,
SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME='global_names';
VALUE
--------------------------------------------------------------------------------
FALSE

GLOBAL_NAME value:
------------------------------------------------------
The default value of GLOBAL_NAME=DB_NAME+DB_DOMAIN
You can change the default value if you wish.
To know your database global_name issue,
SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
DBASE.REGRESS.RDBMS.DEV.US.ORACLE.COM

In order to rename your global_name issue,

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO DBASE.BANGLADESH.INDIA;
Database altered.

SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
DBASE.BANGLADESH.INDIA

global_names Parameter:
-------------------------------------------------------

•global_names is a boolean type parameter. It can have value either true or false. The default value is false.

In order to set global_names parameter to true for the current session issue,

SQL> ALTER SESSION SET global_names=TRUE;
Session altered.

To set global_names parameter to TRUE permanently use,

SQL> ALTER SYSTEM SET global_names=TRUE;(If your database start with spfile)
Session altered.


•Setting GLOBAL_NAMES to TRUE specifies database link is required to have the same name as the database to which it connects.

•If the value of GLOBAL_NAMES is false, then no check is performed.