Hi Arun, question regarding row chaining and row migration - 1) how we will be knowing that we have issue with row chaining/row migration what exact thing will give us clear confirmation that issue occurred because of block level either row chaining/row migration 2) how we will solve this problem row chaining and migration 3) if we gather the stats of table and rebuild the index so index will be updated with new block information or still it will search the old block?
In case of external redundancy the diskgroup size would be 3tb, similarly in high redundancy diskgroup size would be 1tb , 3 ways of mirroring data will be stored upto 1tb and accept 2 fault tolerance.
Hello Arun, Thanks! one doubt at 3.30 on "Row migration". I believe each block has the same size. if the new row can't fit into existing block of 4 kb how can it fit into another single block, will it again go for "row chaining" instead of row migration
Was expecting this question !! I should have given below example: You have one 8KB block and there are two records stored inside the block. Rec 1 = 5KB Rec 2 = 2KB Space Remaining = 1KB User Updates Rec 2 and now the new size is 4KB. It will eventually not fit in the existing data block. Hence, Oracle will migrate it to a new block! Hope this example helps ;)
Hi sir, Regarding GoldenGate configuration for source PITR - will it work if we unload schema dump or tablespace dump (expdp) with the current scn -1 via flashback scn parameter... post source DB PITR.... and then load it to GG replication DB.. and then start the replication process with - START replication_process afterCSN scn_number... like how we resolve the gap... will it work here too.. please assist.
Hi sir highly appreciated what you are doing for the dba community.... Just a small request it will great if you explain the wait events in oracle and the recomended approach to solve them. Also what is the best approach to read the awr reports and find the bottlenecks in the oracle performance... Thanks
@@dbagenesis The size of the diskgroup will be same 3TB, But the amount of data can be stored in the disk group will be 1TB for normal & 3TB for external??
Hi Arun, 1. what is a quorum disk and its concept. 2. if i have a normal redundancy in a diskgroup and if my 1 disk gets corrupt. what will happen in this scenario. i will have a copy of data on one disk but when new data will be written to a diskgroup it will not have 2nd disk for redundancy what will happen in this case. ?
Nope! coz the parameter you mentioned has no impact if your database is running in FORCE LOGGIN MODE. And as we all know, primary must run in FORCE LOGGIN MODE in order to setup standby database.
Oracle auto deletes AWR snapshots older than SNAP_INTERVAL parameter inside your database. You can check it via below query: select snap_interval, retention from dba_hist_wr_control; You can modify the retention period using below command: execute dbms_workload_repository.modify_snapshot_settings(interval => 30,retention => 43200);
Whenever you bounce the DB, for that exact particular snap interval, snapshots will not be available. But as you mention that all previous snaps are lost, then look at below what Oracle has to say about it: started with 11g ,if the system is so overloaded that it takes over 15 minutes to gather statistics or other MMON tasks ,this error is expected. It is functionality enhancement in 11g, as it prevents MMON from locking resources other process might be waiting for In 10g MMON process are allowed to run indefinitely This is a new code that will kill the MMON slave if it takes too long to complete so that normal sessions do not have to wait for these resources. There are only two things i can think of to suggest : 1) Add more memory to the machine - the customer is nearing the capacity of their environment 2) set the hidden param_disable_12751 para,eter to TRUE to disable the policy timeout error(ORA-12751). Hardware is not adequate for taking the present load. Schedule hourly snapshot collection thru CRON, but they may alos fail when load increases. Enjoy!
Hi sir, Diskgroup contains 3 disks and each disk having 1Tb size a) If redundancy is external (No Mirroring ) so the size of diskgroup is 3TB b) If redundancy is High(It will maintain 3 copies) so the size of diskgroup is 9TB. If my answer is wrong pls explain and tell the answer so that i can learn.
H sir, The way of explaining and clarifying doubts with example is excellent sir .
Welcome!
Thanks a lot for sharing knowledge :)
My pleasure
Hi Arun, question regarding row chaining and row migration - 1) how we will be knowing that we have issue with row chaining/row migration what exact thing will give us clear confirmation that issue occurred because of block level either row chaining/row migration 2) how we will solve this problem row chaining and migration 3) if we gather the stats of table and rebuild the index so index will be updated with new block information or still it will search the old block?
Picked up your question for upcoming episodes!
After installed 11GR2 for RAC Configuration, why we have showing compatible.asm is 11.2.0.0.0 & compatible.rdbms is 10.1.0.0.0?
Please explain.
In case of external redundancy the diskgroup size would be 3tb, similarly in high redundancy diskgroup size would be 1tb , 3 ways of mirroring data will be stored upto 1tb and accept 2 fault tolerance.
Right answer
Hello Arun,
Thanks! one doubt at 3.30 on "Row migration".
I believe each block has the same size. if the new row can't fit into existing block of 4 kb how can it fit into another single block, will it again go for "row chaining" instead of row migration
Was expecting this question !! I should have given below example:
You have one 8KB block and there are two records stored inside the block.
Rec 1 = 5KB
Rec 2 = 2KB
Space Remaining = 1KB
User Updates Rec 2 and now the new size is 4KB. It will eventually not fit in the existing data block.
Hence, Oracle will migrate it to a new block! Hope this example helps ;)
Hi sir,
Regarding GoldenGate configuration for source PITR - will it work if we unload schema dump or tablespace dump (expdp) with the current scn -1 via flashback scn parameter... post source DB PITR.... and then load it to GG replication DB.. and then start the replication process with - START replication_process afterCSN scn_number...
like how we resolve the gap... will it work here too.. please assist.
Kindly post it on www.forum.dbagenesis.com/
Hi sir highly appreciated what you are doing for the dba community....
Just a small request it will great if you explain the wait events in oracle and the recomended approach to solve them.
Also what is the best approach to read the awr reports and find the bottlenecks in the oracle performance...
Thanks
Sure, will speak about wait events in upcoming episodes. Meanwhile do checkout my previous video on AWR report posted on RUclips.
@@dbagenesis sure sir thanks for the prompt reply
Can please explain briefly about read consistency in oracle M alwys litle bit confusing on that! pls help😊
Thank you for explaining row chaining and migration. Could you also explain about high water mark please?
Maybe in my upcoming episodes !!
@@dbagenesis
The size of the diskgroup will be same 3TB, But the amount of data can be stored in the disk group will be 1TB for normal & 3TB for external??
Hi bro,
How to avoid wait events
Like row lock contention.
Hi Arun,
1. what is a quorum disk and its concept.
2. if i have a normal redundancy in a diskgroup and if my 1 disk gets corrupt. what will happen in this scenario. i will have a copy of data on one disk but when new data will be written to a diskgroup it will not have 2nd disk for redundancy what will happen in this case. ?
Instead of restoring whole tablespace .I think we can restore only table using rman from 12c. What you say?
Depends on the scenario
for external its 3TB
for high its 1TB
Great explanation. Njoying ur videos
Awesome, thank you!
On my primary db while importing table with impdp and new parameter transform=disable_archive_logging:y will it impact my standby database
Nope! coz the parameter you mentioned has no impact if your database is running in FORCE LOGGIN MODE.
And as we all know, primary must run in FORCE LOGGIN MODE in order to setup standby database.
Hi Arun,
How does a CBO generate an optimal execution plan for the SQL statement?
Yes, why you have any doubts on CBO? or do you plan to use RBO?
Thank you so much arun! you alwys hit in cmnts box🤗
I jst want to knw how CBO generate the best plan how it pick up is it required stats gather regular manner for best pick.?
If redundancy external then diskgroup size would be equal or less than 3TB.
If redundancy high then diskgroup size would be equal or less than 1TB.
Can we use different sizes in 3 disks? or same size we use?
You tell me which one will keep your life (or storage admin) life simple? Same size disks or variable size ??
Is there any article or video about row migigration in our channel?
None as of now!
How many types of privileges are available in oracle (both system and object level)? What are the important privileges that DBA should know?
check from dba_sys_privs or dba_object_privs
Q1 - 3 TB, Q2 - 1TB
Thats correct
Question: Why I'm missing AWR snapshot? Once I restarted the DB its started to generate the AWR snapshot?
Oracle auto deletes AWR snapshots older than SNAP_INTERVAL parameter inside your database. You can check it via below query:
select snap_interval, retention from dba_hist_wr_control;
You can modify the retention period using below command:
execute dbms_workload_repository.modify_snapshot_settings(interval => 30,retention => 43200);
Hi Arun
The retention period was set to 7days but it’s still missing all snapshots before the restart of the db
Whenever you bounce the DB, for that exact particular snap interval, snapshots will not be available. But as you mention that all previous snaps are lost, then look at below what Oracle has to say about it:
started with 11g ,if the system is so overloaded that it takes over 15 minutes to gather statistics or other MMON tasks ,this error is expected.
It is functionality enhancement in 11g, as it prevents MMON from locking resources other process might be waiting for
In 10g MMON process are allowed to run indefinitely
This is a new code that will kill the MMON slave if it takes too long to complete so that normal sessions do not have to wait for these resources.
There are only two things i can think of to suggest :
1) Add more memory to the machine - the customer is nearing the capacity of their environment
2) set the hidden param_disable_12751 para,eter to TRUE to disable the policy timeout error(ORA-12751).
Hardware is not adequate for taking the present load.
Schedule hourly snapshot collection thru CRON, but they may alos fail when load increases.
Enjoy!
Nice very useful..
external redundancy will have 3Tb size and High redundancy will have 1TB
Network(tnsnames.ora) could be one of the reasons on the stanby site to connect old primary when switcover failed.
Yup, it can be!
Good sir...
External -3TB,Red - 1TB
Good one!
Difference between cluster and grid
Both are same!
High 1tb ,external 3tb
Correct !
Hi sir, Diskgroup contains 3 disks and each disk having 1Tb size a) If redundancy is external (No Mirroring ) so the size of diskgroup is 3TB b) If redundancy is High(It will maintain 3 copies) so the size of diskgroup is 9TB. If my answer is wrong pls explain and tell the answer so that i can learn.
Wrong Answer!
External Means the size of the disk group is 1 TB & High Means the size of the Disk group is 3 TB.
1.3TB diskgroup size in external redundancy.
2.1TB is the diskgroup size in high redundancy.
wrong answer.
High 1tb, external 3tb
Perfect!
@@dbagenesis Hello, please also help to understand CBC blocks contention.Thanks