50. Revision and some new things (high water mark, unused block skipping(image copy, backup set), restore validate, structure of an extent) - Agnivo102/Database_Architect GitHub Wiki
If the data block required for the user's query is already available in the database buffer cache then it is called cache hit.
When the user first writes a query the server process takes it to library cache, it got divided into hash codes, then the syntax and sementic checks are done. Then the query optimizer figures out the execution plan. This going all the steps from the start to finish is hard parsing.
If the user again writes a query which has its hashcode already present in the sql area, the all the earlier steps are avoided and server process takes the previous execution plan and executes it. This is called soft parsing.
When the user runs a DML, DDL query , first that query is written into Redo log buffer cache. Then the change is done in the data blocks in the database buffer cache. Then on these 4 scenarios,
every 3 seconds, 1/3rd of the redolog buffer cache is full, commit, 1 MB is full in database buffer cache
the queries are then written in the redo log file from the redo log buffer cache.
Oracle home is the location where the oracle application is installed. We can have multiple oracle home in one server. Technically it is an linux environment variable containing the location of the oracle application.
The value of oracle home is: /u01/app/oracle/product/19.0.0/dbhome_1/
Oracle base is the location where the binary files of database is located like data files, control files, alert logs etc.
The value of oracle home is: /u01/app/oracle/
They both are located in $ORACLE_HOME/dbs/
If both the parameter files are deleted by mistake when the database is running then the work of DBA in that scenario:
If pfile and spfile is both deleted when the database is running. Then we can recover the parameter files from the memory. The memory of the running database has the parameter file inside. The command create spfile from memory: recovers the physical parameter file.
Only sysdba role users password are stored in password file. Because only the sysdba role user can login if the database is in shutdown. Other users data is stored in data dictionary.
Restore validate is fininng out whether the data files are in a position to restore like if they are obsolete are not according to retention policy.
Imagine this as a internal boundary marker which separates used data blocks and unused data blocks.
This is maintained by database engine for every database segement.
This is its structure:
Unused block Unused block Unused block ................ High Water mark ................. Used block Used block Used block
This mark is the separation between the used blocks and Unused blocks. This is actually the logical structure of an extent (extent it logical, a collection of data blocks) in Metadata form.
As new data is inserted the high water mark is move upwards automatically. But if data are deleted this doesn't move downwards normally.
When data is deleted from table the physical data blocks and space allocated to that table do not shrink or dissappear automatically.
This is done to further speed up future inserts as charging high water mark requires changing Metadata.
DBA and the owner, and the user with the object privilege alter or drop can lower the high water mark but it doesn't happen normally.
But this has consequences. If a full table scan is done on that table the scan will happen from beginning up to high water mark. So if the data is present on only for 6 blocks but below high water mark there are 106 data blocks the oracle engine will do a full scan on those free blocks so it slows down the process.
Imagine you have a database of 100 GB but the data it contains is only of 30 GB. The remaining 70 GB is consists of empty unallocated data blocks or spaces, or the spaces freed by dropped objects. RMAN for backup set only reeds those data blocks that has data in the backup set.
RMAN interprets the data files block by block, it can consult the segment headers and logically managed extents Metadata to identify which blocks are currently up of high water mark or unallocated. It simply bypasses writing those empty blocks in the backup pieces.
Image copy is exactly a bit for but copy of the original source file, same as the copy we will get if we copy the file through OS itself.
.................. Extent 2 .................. Unused block 6 Unused block 5 Unused block 4 ................ High Water mark ................. Used block 3 Used block 2 Used block 1 ................... Extent 1 ................... Used block 6 Used block 5 Used block 4 Used block 3 Used block 2 Used block 1 ...................
When a table is new oracle allocates it first extent to it. It start filling from data block 1 to the end (here block 6) of the extent as the high water mark moves up.
When extent 1 fills up oracle assigns another extent to the table for new data as the new blocks starting filled up and the high water mark moves up.
If we have 100 people and we have to allocate houses to them one by one in a locality this is how we do to that work. We create a house list and allocate them house one by one and keep a mark on the house list so that we can separate used houses and unsed houses. This is similar to that.
Alter table schema.table enable row movement;
Alter table schema.table shrink space cascade;
backup database format '/u05/rman/%d_%T.bkp';
delete obsolete;
