5. a. Views , b. redolog files, c. control files[ Control file multiplexing ], - sayanbhattacharya1234/Oracle_DBA GitHub Wiki
A. Views - there are lot of views in oracle has given to monitor the database.
Views are 2 type--
Static views = This view gets records or datas from datafile.
this view cannot execute even the database is in mount state, it can execute only the database is in open state.
Any view start with dba_ is Static View
Dynamic views = This view gets records or datas from control file.
thats why this view can execute even the database is in mount state
Any view start with v$ is dynamic view.
to view parameters
Here 'issys_modifiable' means = to which parameter we changed if effect immediately / i need to restart the database / i need to restart the session
Those parameter shows 'IMMEDIATE' = that parameters effect immediately after changes value.
those parameter showa 'FALSE' = we need to restart the database after change any value. ( we need downtime)without any verified approval we dont do anything as a dba.
the parameter shows 'deferred' = we have to restart the session after change any value.
want to see how many views.
dynamic view starts with 'v$'
static view starts with 'dba_'
to see the datafiles
dynamic view only in mount state[ Dynamic views = This view gets records or datas from control file ,thats why this view can execute even my database is in mount state ]
i want to see the redolog file
i want to see the control file
instance name to know
if my database start with spfile that time we change parameter online
if my database start with pfile that time we cannot change the parameter directly, we need to shurt down the database, we have change manually into pfile and then restart the database.
want to change the parameter
if the value issys modifiable = false that means the process need to restart the database through which whatever the values are change that we will see.
if the value issys modifiable = Immediate that means no need to restart the database immediately we can see the changed value.
Deferred means it will not require a database re-start just the session restart (log out and log in) to get in effect.
Alter system set processes=400 scope=spfile; This change will only get affected after database restart.
B. Redolog files
why redolog files are importnent? what is roll forwardind?
my database is running . and sudden power failure / hardware issue and database is off. some of users do commited , some of buffer cache running , some of not commited . all are messed up because of database off . when we again restart the database then SMON proccess that comes into picture and that is responsible for INSTANCE RECOVERY . If read all redolog file and check those values in redolog file, are availble in this datafile or not.
some of commited value in redolog file that not effect datafile
SMON perform those activities means value are updated . this is called ROLL FORWARDING.
The non-completed statements are roll backed by SMON.
View for current SCN
This members are separate files which are connected logically to form this groups. These groups, they form the redolog file. Log Writter when start writing (writes every 3 seconds) it will start writing in group 1. The thing is that it will write the same content in both member 1 and member 2.
When the size of all the members are full in group 1, the log writter will switch to group 2. This is called Log switch. Now if group 2 become full another log switch will occur and the log writter will swithc to group 3.
After group 3 will become full the log writter will switch to group 1 again and over write the previous logs. Now whats the point of this if the previous logs gets deleted. So to make this work it happens like this:
Manually Log switches
command is ' alter system switch logfile; '
RedovLogfile have 1 or 2 members. There are many logfile can be created , by default 3 log files are there.every 3 sec in redo entry LGWR start write in groups [1,2,3,4,...n] have members [1,2,..]. Same content will written into all members .when group 1 is full then a log switch , LGWR write into group 2 when group 2 is full then log switch into group 3 and so on.
This is the view of logfile sequence numbers and status modes. There are 3 modes.
1.Active= when a log switch occars the next group is current, previous group is active . In this status all the contents of the members will be archived that is stored in another place.
2.Current = when the group is currently written by LGWR
3. inactive. = when nothing is happen.
Manual log switch view
When we logswitch manually by this command ' alter system switch logfile; '
Member create for a exist file
i want create a new member for group 2 so we need to create a path where the new member located ' /u01/redolog/' and member redolog02a for group 2
i want to create new group and and a member of that group
Control file
the view to see the number of database are currently in the system
contents of oratab file under the etc dic.
command is cat /etc/oratab
Control file is the heart of the database. It holds the metadata of all the objects and data files . by default there are 2 control file exist . but we can copy it and keep another place. if the server or hard disk crashes then by that we can get back the control file and with control file and spfile we can create the database again.
location of control file : show parameter control_files
there are 2 control files are there by default. control01.ctl , control02.ctl
now Let's create another control file.
first we have to see by which parameter file database has started .spfile or pfile
as they have different process.