Home » Posts tagged 'Oracle' (Page 2)

Tag Archives: Oracle

Oracle : Move datafile to different location

From time to time a DBA might need move an Oracle database datafile from one location to another. Reasons for this might include: I/O balancing for performance reasons, or to rectify incorrect naming standards.

Choose one of the following procedures based on the log mode of your database (select log_mode from sys.v_$database):

Database is in ARCHIVELOG mode

  • Take the datafile offline with the “ALTER DATABASE DATAFILE ‘/old/location’ OFFLINE;” command.
  • Copy or move the datafile to its new location. On Unix this can be done with the “dd” command.


dd if=/old/location of=/new/location bs=4096

SQL> ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
SQL> RECOVER DATAFILE '/new/location';

Database is in NOARCHIVELOG mode

  • Shutdown the database
  • Copy or move the datafile to its new location. On Unix this can be done with the “dd” command. Example:
dd if=/old/location of=/new/location bs=4096
  • Start SQL*Plus, do a “STARTUP MOUNT” and rename the file:
SQL> ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';

Oracle 9i : Some of limitation on Oracle 9i

Physical Database Limits

Item Type of Limit Limit Value
Database Block Size Minimum 2048 bytes; must be a multiple of operating system physical block size
Maximum Operating system dependent; never more than 32 KB
Database Blocks Minimum in initial extent of a segment. 2 blocks
Maximum per datafile Platform dependent; typically 222-1 blocks
Controlfiles Number of control files 1 minimum; 2 or more (on separate devices) strongly recommended
Size of a control file Dependent on operating system and database creation options; maximum of 20,000 x (database block size)
Database files Maximum per tablespace Operating system dependent; usually 1022
Maximum per database 65533

May be less on some operating systems

Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance

Database extents Maximum 4 GB, regardless of the maximum file size allowed by the operating system
Database file size Maximum Operating system dependent. Limited by maximum operating system file size; typically 222 or 4M blocks
MAXEXTENTS Default value Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
Maximum Unlimited
Redo Log Files Maximum number of logfiles Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement

Control file can be resized to allow more entries; ultimately an operating system limit

Maximum number of logfiles per group Unlimited
Redo Log File Size Minimum size 50 KB
Maximum size Operating system limit; typically 2 GB
Tablespaces Maximum number per database 64 K

Number of tablespaces cannot exceed the number of database files, as each tablespace must include at least one file


source (http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch43.htm#287916)

Oracle 9i : Max size of datafiles

Data files are not exactly unlimited in size, so the term “Unlimited” refers to the ceiling your datafile is able to reach, and it depends on the Oracle Block Size. To find the absolute maximum file size multiply block size by 4194303. This is the actual maximum size. You may want to read the Metalink Note:112011.1.

A datafile cannot be oversized, otherwise it could get corrupted. Let’s say if your database is 8k blocks that means that one file can not exceed approximately 34GB (34,359,730,176 bytes) without having database corruption.

Sizing datafiles is a matter of manageability, it depends on your storage, the amount of space allocated in a single managed storage unit.

128G is the maximum datafile size in 10g, but considering the maximum number of datafiles a Database can have, it can make a database to potentially size 8E (exabytes = 8,388,608 T).

The maximum data file size is calculated by:
Maximum datafile size = db_block_size * maximum number of blocks

The maximum amount of data in an Oracle database is calculated by:
Maximum database size = maximum datafile size * maximum number of datafile

The maximum number of datafiles in Oracle9i and Oracle 10g Database is 65,536. However, the maximum number of blocks in a data file increase from 4,194,304 (4 million) blocks to 4,294,967,296 (4 billion) blocks.

The maximum amount of data for a 32K block size database is eight petabytes (8,192 Terabytes) in Oracle9i.

Maximum database size is 8Pb in Oracle9i & 10g (Small file Tablespaces).

Block Sz   Max Datafile Sz (Gb)   Max DB Sz (Tb)

2,048 8 512 4,096 16 1,024 8,192 32 2,048 16,384 64 4,096 32,768 128 8,192

The maximum database size is 8Eb in Oracle 10g (Big file tablespaces).

Block Sz   Max Datafile Sz (Gb)   Max DB Sz (Tb)

2,048 8,192 524,264 4,096 16,384 1,048,528 8,192 32,768 2,097,056 16,384 65,536 4,194,112 32,768 131,072 8,388,224


Resize redo log groups in Oracle

Here is the procedure to resize the redo log group in SAP/Oracle
To check all Redolog files Run command:
Select * from v$log;

Now add a group, To add a log group
Alter database add logfile group 5 (‘/oracle/SID/origlogA/log_g15m1.dbf’,’/oracle/SID/mirrlogA/log_g15m2.dbf’) size 200M;

alter database add logfile group 6 (‘/oracle/SID/origlogB/log_g16m1.dbf’,’/oracle/SID/mirrlogB/log_g16m2.dbf’) size 200M;

alter database add logfile group 7 (‘/oracle/SID/origlogA/log_g17m1.dbf’,’/oracle/SID/mirrlogA/log_g17m2.dbf’) size 200M;

alter database add logfile group 8 (‘/oracle/SID/origlogB/log_g18m1.dbf’,’/oracle/SID/mirrlogB/log_g18m2.dbf’) size 200M;

After these commands, Force System for log switch
Alter system switch logfile;
Drop Existing Log groups files (Make sure no group has status active or current)
Alter database drop logfile group 1;
Alter database drop logfile group 2;
Alter database drop logfile group 3;
Alter database drop logfile group 4;

Remove these files from OS level
Now again add same Group

alter database add logfile group 1 (‘/oracle/SID/origlogA/log_g11m1.dbf’,’/oracle/SID/mirrlogA/log_g11m2.dbf’) size 200M;

alter database add logfile group 2 (‘/oracle/SID/origlogB/log_g12m1.dbf’,’/oracle/SID/mirrlogB/log_g12m2.dbf’) size 200M;

alter database add logfile group 3 (‘/oracle/SID/origlogA/log_g13m1.dbf’,’/oracle/SID/mirrlogA/log_g13m2.dbf’) size 200M;

alter database add logfile group 4 (‘/oracle/SID/origlogB/log_g14m1.dbf’,’/oracle/SID/mirrlogB/log_g14m2.dbf’) size 200M;

We can drop Log group 5,6,7,8
In case any of these groups are Active or current stage use “Alter system switch logfile”
Alter database drop logfile group 5;
Alter database drop logfile group 6;
Alter database drop logfile group 7;
Alter database drop logfile group 8;

How to estimate the PGA, SGA size, configuration, database server memory

ORACLE to the proposal: OLTP systems PGA = (Total Memory) * 80% * 20%. DSS systems PGA = (Total Memory) * 80% * 50%.

ORACLE recommends a database server, sub-80% of the memory to the database, 20% of the memory to the operating system, how it to a database server with memory?

SQL> select * from v $ pgastat;

————————————————– ————– ———- ————
aggregate PGA target parameter 104857600 bytes

—– This value is equal to the value of parameter PGA_AGGREGATE_TARGET, if this value is 0, disable the automatic PGA management.
aggregate PGA auto target 75220992 bytes

—– Said how much memory to the PGA also provides automatic operation mode, this value is usually close to pga_aggregate_target-total pga inuse.
global memory bound 20971520 bytes

—– Workspace implementation of the maximum, if the value is less than 1M, immediately increase the size of PGA
total PGA inuse 30167040 bytes

—– Total size of the current allocation of PGA, this value may be greater than the PGA, PGA settings if too small. This value is close to the select sum (pga_used_mem) from v $ process.
total PGA allocated 52124672 bytes

—– Take the total size of work area
maximum PGA allocated 67066880 bytes
total freeable PGA memory 0 bytes —- no idle PGA
process count 23 —- 23 present a process
max processes count 25
PGA memory freed back to OS 0 bytes
total PGA used for auto workareas 8891392 bytes
maximum PGA used for auto workareas 22263808 bytes
total PGA used for manual workareas 0 bytes — 0 automated management
maximum PGA used for manual workareas 0 bytes — 0 automated management
over allocation count 0

Oracle memory structure – SGA (Just for memory refresh)

First, an overview of the “Oracle memory structure of-PGA Chapter” mentioned in the article, PGA is a dedicated server process private memory area, while the SGA is the shared memory area.

SGA by a number of parts:
1, the fixed SGA (Fixed SGA)
2, block buffer (Db cache)
3, redo log buffer (Redo log buffer)
4, Java pool (Java pool)
5, large pool (Large pool)
6, shared pool (Shared pool)
7, stream pool (Stream pool)

The following parameters to control the size of shared pool of related components:
1, JAVA_POOL_SIZE: Control Java pool size.
2, SHARED_POOL_SIZE: 9i shared pool to control the largest part of the occupier, 10g shared pool size over the control.
3, LARGE_POOL_SIZE: control of large pool size.
4, DB_ * K_CACHE_SIZE: control the size of the different pieces of the buffer size.
5, LOG_BUFFER: redo log buffer size control.
6, SGA_TARGET: 10g or more automatic SGA memory management control of the total memory size.
7, SGA_MAX_SIZE: can control the maximum SGA size, need to restart the database to change.

The following will describe in more detail the role of the various parts and recommended settings. (more…)

Difference Between Oracle Mount, No Mount and Open Stage

Just for refreshing my memory and yours

he differences in the 3 startup stages for oracle db are as below.

NoMount Stage:
When DB is in the nomount stage, oracle first opens and reads the initialization parameter file “init.ora” for the DB configuration which includes various parameters required for initialization. Based on the parameters, the memory areas in database instance are allocated and db background processes are started. The instance then starts.

Mount Stage:
When DB is in this stage, it opens and reads the control file which contains the physical structure of db  like the database datafiles etc.. It determines the location of the datafiles and the database is ready to be opened.

Open Stage
When DB is in this stage, it opens the database, it tries to access all of the datafiles associated with the database. Once it has accessed the database datafiles, it makes sure that all of the database datafiles are consistent.

After that DB is into normal operations state.