Transcript Database Files

Oracle Database Administration
Database files
Database files
• Oracle Database uses the following types of
files:
– Data files
– Control files
– Redo log file (or just: Log files)
– Archive log files
– Parameter files
Database startup process
• Database startup involves 3 steps:
–
Instance startup
•
•
–
Mount database
•
•
–
PARAMETER FILE is read
Parameter file contains location of control
file(s)
CONTROL FILE is opened
Control File contains location of data files and
log files
Open database
•
Data files and log files are opened
Data files
• Data files:
– all database data is stored there
– data files can automatically extend if required (the
AUTOEXTEND clause)
– one or more datafile form a logical unit of database
storage called a tablespace
– Oracle does not write modified data immediately,
if the database is not closed properly, the datafiles
can contain incorrect data
Data files
• Data files:
– all database data is stored there
– data files can automatically extend if required (the
AUTOEXTEND clause)
– one or more datafile form a logical unit of database
storage called a tablespace
– Oracle does not write modified data immediately,
if the database is not closed properly, the datafiles
can contain incorrect data
Control files
• Control file contains:
– database name, timestamp of database creation
– location of database datafiles and redo log files
– system change number (SCN), every transaction
increases SCN
• Control file:
– is modified every time there is structural change to
the database (e.g. new datafile, redo log file)
– is required for the database to operate
– can be multiplexed
– is used during database recovery
Redo log files
• Redo log files:
– must be at least two, usually three or more
– are written sequentially
– record every change to the database
– can be multiplexed
– redo log files are used by recovery operations:
• automatic recovery after instance failure (for example
after SHUTDOWN ABORT), loss of a redo log file can
prevent from opening the database
• manual recovery from backup, loss of a redo log file can
make it impossible to perform complete recovery (and
some transactions will be lost)
Archive log files
• Redo log files can be archived before they are
overwritten, then they become archive log files
• Archive log files:
– are used by manual recovery only
– can be multiplexed
Parameter files
• Parameter file contains instance and database
parameters, for example:
– instance name
– location of control files
– database block size
– amount of memory used by Oracle
• Parameters can be stored in:
– parameter file (regular text file)
– server parameter file (binary file)
Example parameter file
imm.__db_cache_size=138412032
imm.__java_pool_size=4194304
imm.__large_pool_size=4194304
imm.__shared_pool_size=58720256
imm.__streams_pool_size=0
*.audit_file_dest='C:\oracle\admin\imm\adump'
*.background_dump_dest='C:\oracle\admin\imm\bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\oradata\imm\control01.ctl','C:\oracle\oradata\im
m\control02.ctl','C:\oracle\oradata\imm\control03.ctl'
*.core_dump_dest='C:\oracle\admin\imm\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='imm'
*.db_recovery_file_dest='C:\oracle\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.job_queue_processes=10
*.nls_language='AMERICAN'
*.nls_territory='POLAND'
*.open_cursors=300
*.pga_aggregate_target=69206016
*.processes=150
Parameter files
• Oracle can use PFILE or SPFILE
• SPFILE can be modified by Oracle commands,
PFILE must be modified manually
• When using SPFILE:
– some parameters can be changed while the
database is running
– Oracle can adjust some parameters automatically
at runtime (e.g. memory distribution)
Using parameter files
• Normally Oracle uses PFILE or SPFILE from
default locations
• It is possible to override default locations:
– STARTUP PFILE=‘path_to_pfile.ora’
– STARTUP SPFILE=‘path_to_spfile.ora’
• It is possible to create PFILE from SPFILE
and SPFILE from PFILE:
– CREATE SPFILE FROM PFILE
– CREATE PFILE FROM SPFILE
– CREATE PFILE=‘path_to_pfile.ora’ FROM
SPFILE
Adjusting database parameters
• When using PFILE – modify the text file and
restart the database
• When using SPFILE:
– alter system set db_cache_size = 30000000
scope=memory;
– alter system set db_cache_size = 30000000
scope=spfile
• Scope parameter can be:
– memory – change the parameter immediately, do
not write to spfile
– spfile – write to spfile, do not change until restart
– both – change immediately and write to spfile