Few basic concept of Control and Parameter file concept of Oracle Database.
Control file:
A control file, which is a small binary file that records the physical structure of the database.
The control file includes:
The database name
Names and locations of associated datafiles and redo log files
The timestamp of the database creation
The current log sequence number
Checkpoint information
The control file must be available for writing by the Oracle Database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.
For creating oracle control File follow the procedure as below on Source System:
A-
1) Create control.sql by executing the command at the productive system, below;
alter database backup controlfile to trace;
As a result of this command, a trace file (e.g. xxx.trc ) will be created under /oracle/<SSID>/saptace/usertrace directory
2) Goto “/oracle/<SID>/saptrace/usertrace” and open newly created trace file,
Remove all lines before “STA RTUP MOUNT” line and delete all commented “#” lines. Also remove all lines after
“CHARACTER SET WE8DEC;” line. Change all Source SID’s to Target SID via following commands.
UNIX (VI Editor): :%s/<SSID>/<TSID>/g
Parameter file:
Parameter file is a text or binary to store the database initialization parameters. The oracle instance reads the parameter file during startup which are then used to control the behavior of database instance and many other aspects as well. Such as : memory allocation (SGA and PGA), startup of optional background processes, Setting of NLS parameters etc.
There are 2 types of parameter files, namely :
1. pfile (parameter file) – older way [ not recommended by oracle ]
2. spfile (server parameter file) – newer way [ recommended by oracle ]
spfile was introduced starting from oracle 9i, untill that time text based pfile was used to store database initialization parameters.
pfile
— Text file
— Parameters in pfile can be edited using any text editor
— Default location of pfile – $ORACLE_HOME/dbs/init[SID].ora where [SID] – is the name of the instance.
— The RMAN utility can not take backup of a pfile
spfile
— Binary file
— spfile can not be edited using a text editor. Instead it can only be altered using the “”ALTER SYSTEM”” command
— Default location of spfile – $ORACLE_HOME/dbs/spfile[SID].ora where [SID] – is the name of the instance.
— The RMAN utility can take backup of a spfile.
If we want to check if SPFILE or PFILE is used in oracle instance then can be done by following way:
If the query returns no rows – pfile is used. If the query returns any value with a filename(with its path) then SPFILE is used by current running instance.For example:
SQL> show parameter spfile
NAME TYPE VALUE
——————————-
spfile string ?/dbs/spfile@.ora
The above example shows that spfile used by the running instance.
Basic Memory Structures:
Oracle Database includes several memory areas, each of which contains multiple subcomponents.
System global area (SGA):
The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. All server and background processes share the SGA. Examples of data stored in the SGA include cached data blocks and shared SQL areas.
Each database instance has its own SGA. Oracle Database automatically allocates memory for an SGA at instance startup and reclaims the memory at instance shutdown.
Program global area (PGA):
A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. Oracle Database creates the PGA when an Oracle process starts.
One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.
User global area (UGA):
The UGA is memory associated with a user session.
Software code areas:
Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from user programs—a more exclusive or protected location.