Home » Posts tagged 'datafile'
Tag Archives: datafile
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.
Example:
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;
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
Re-create my Oracle control file
From my previous posting I had mention that I need to recreate my Oracle database control file since I need to increase my MAXDATAFILES parameter.
Yesterday (sunday) I had a chance to manage my control file since I have time to shutdown my server. I log on my AIX server and logon to Oracle SQL Plus.
ora[sid]>sqlplus /nolog
Connect to my database.
SQL>conn / as sysdba;
Then dump my current control file to a text file.
SQL>alter database backup controlfile to trace as ‘/oracle/[SID]/controlfile.txt’;
Then I copied that file (controlfile.txt) to my local notebook and edit it using my favourite text editor.
Here are list of my old control file :
Check your MAXDATAFILES parameter on Oracle
A few weeks ago, I need to manage my number of datafiles on Oracle server parameter file (spfile). On Oracle spfile, by using BRTOOLS or BRSPACE, you can check your DB_FILES parameter.
ora[sid]>brspace -f dbparam
On my system, currently DBFILES = 768. I need to increase my DB_FILES so it can manage larger number of datafiles but unfortunately I can’t do it right now since my MAXDATAFILES was set to 784. So I can only increase my number of datafiles from 768 to 748.
You can check MAXDATAFILES number on your Oracle control file. Follow this instruction to get your MAXDATAFILES parameter info :
- Login to your DB server using root
- Change to ora[sid] user. root#su – ora[sid] [ENTER]
- Go to sqlplus. ora[sid]>sqlplus /nolog [ENTER]
- Connect to your database instance. SQL>conn / as sysdba [ENTER]
- Run command to extract control file to trace file. SQL>alter database backup controlfile to trace [ENTER]
- Get information about where your trace file is stored. SQL>show parameter user_dump_dest
- Get your trace file (usually it has .trc extension) and open it using your favourite text editor.
- Find MAXDATAFILES info inside it.
Here is my trace file sample :
—-more lines—–STARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE “[SID]” NORESETLOGS FORCE LOGGING ARCHIVELOG— SET STANDBY TO MAXIMIZE PERFORMANCEMAXLOGFILES 255MAXLOGMEMBERS 3MAXDATAFILES 784 ——-> this is the information I mentioned.MAXINSTANCES 50MAXLOGHISTORY 22462LOGFILEGROUP 1 (‘/oracle/[SID]/origlogA/log_g11m1.dbf’,‘/oracle/[SID]/mirrlogA/log_g11m2.dbf’) SIZE 50M,—-more lines—–
If I want to increase MAXDATAFILES, then I need to re-create my control file. I’ll update more about re-creating control file on my next posting.
SAP Basis Cafe 