Home » Posts tagged 'Oracle' (Page 2)
Tag Archives: Oracle
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'; SQL> ALTER DATABASE DATAFILE '/new/location' ONLINE;
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'; SQL> ALTER DATABASE OPEN;
Physical Database Limits
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
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;
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…)
Just for refreshing my memory and yours
he differences in the 3 startup stages for oracle db are as below.
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.
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.
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.