Distinction between SGA and PGA.
– SGA (System Global Area) is a storage place assigned during the set up of an instance.
– SGA is assigned as 40% of RAM dimension by standard.
– SGA dimension is managed by DB_CACHE_SIZE parameter described in initialization parameter file (init.ora details file or SPFILE).
– PGA (Program or Procedure Global Area) is a storage place that stores a user period particular details.
– PGA is assigned as 10% of RAM dimension by standard.
What are the hard drive elements in Oracle?
These are the actual elements which gets saved in the hard drive.
– Data files
– Upgrade Log files
– Control files
– Security password files
– Parameter files
What is System Change Number (SCN)?
– SCN is a exclusive ID that Oracle produces for every dedicated deal.
– It is documented for every alternation in the redo access.
– SCN is also produced for every checkpoint (CKPT) happened.
– It is an ever improving number which is customized for every 3 seconds
– You can get the SCN number by querying choose SCN from v$database from SQLPLUS.
What is Database Writer (DBWR) and when does DBWR create to the data file?
– DBWR is experience procedure that makes details prevents details from Databases shield storage cache to details.
There are 4 essential circumstances when DBWR makes to details file
– Every 3 seconds
– Whenever checkpoint occurs
– When server process needs 100 % free area in database shield storage cache to read new blocks.
– Whenever variety of changed blocks gets to a highest possible value.
What is Log Writer and when does LGWR writes to log file?
– LGWR makes redo or customized details from redo log shield storage cache to redo log details files in database.
– It is accountable for moving redo buffer details to online redo log details files, when you are making and a log switch also happens.
– LGWR makes to redo details files when the redo log shield is 1/3 rd complete.
– It also makes for every 3 seconds.
– Before DBWR writes customized blocks to the datafiles, LGWR makes to the log file
Which Table spaces are designed instantly when you build a database?
– SYSTEM tablespace is designed instantly during database development.
– It will be always on the internet when the database is start.
Other Tablespaces include
– SYSAUX tablespace
– UNDO tablespace
– TEMP tablespace
– UNDO & TEMP tablespace are optionally available when you build a database.
Which data file is utilized first when Oracle database is began and What is the main distinction between SPFILE and PFILE?
– Init<SID>.ora parameter details file or SPFILE is utilized first .( SID is example name)
– Configurations needed for beginning a database are saved as factors in this details file.
– SPFILE is by standard designed during database development whereas PFILE should be designed from SPFILE.
– PFILE is customer part written text details file whereas SPFILE is server part binary details file.
– SPFILE is a binary details file (it can’t be opened) whereas PFILE is a written text details file we can modify it and set parameter principles.
– Changes produced in SPFILE are dynamically impacted with operating database whereas PFILE changes are impacted after jumping the database.
– We can back-up SPFILE using RMAN.
What are benefits of using SPFILE over PFILE?
– SPFILE is available from Oracle 9i and above.
– Parameters in SPFILE are customized dynamically.
– You can’t create any changes to PFILE when the database is up.
– RMAN cant back-up PFILE, It can back-up SPFILE.
– SPFILE factors changes are examined before they are approved as it is managed by Oracle server thereby lowering a persons misprint mistakes.
How can you discover out if the database is using PFILE or SPFILE?
– You can question Powerful efficiency perspective (v$parameter) to know your database is using PFILE or SPFILE.
– SQL> choose value from V$parameter where name= ‘SPFILE’;
– A non-null value indicates the database is using SPFILE.
– Zero value indicates database is using PFILE.
– You can power a database to use a PFILE by providing a start-up control as
– SQL> start-up PFILE = ‘full direction of Pfile location’;
Where are parameter files stored and how can you commence a database using a particular parameter file?
– In UNIX they are saved in the place $ORACLE_HOME/dbs and ORACLE_HOME/database for Ms windows listing.
– Oracle by standard begins with SPFILE situated in $ORACLE_HOME/dbs.
– If you want to begin with the database with particular details file we can add it at the start-up control as
SQL > start-up PFILE = ‘full direction of parameter details file ‘;
– You could create PFILE from SPFILE as create PFILE from SPFILE;
– All the parameter principles are now customized with SPFILE.
– In the same way, create SPFILE from PFILE; control makes SPFILE from PFILE.
Thus you can join the dba training institute to know more about this topic.