No question at this time
DBA Top 10
1 B. Vroman 15700
2 M. Cadot 10200
3 T. Boles 8350
4 J. Schnackenberg 8300
5 A. Kavsek 7800
6 M. Hidayathullah ... 2500
7 P. Wisse 1200
8 G. Lambregts 1100
9 T. P 1000
10 B. Derous 500
10 R. Pattyn 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48277
Total active users1612
Act. users last 24h4
Act. users last hour0
Registered user hits last week185
Registered user hits last month836
Go up

How much time Oracle needs to change checkpoint_change# value ?
Next thread: recovery scenarios
Prev thread: consistent-read of a block

Message Score Author Date
Dear Experts, I am running following SQL on my ...... Jill Salalila Sep 19, 2018, 19:03
Hi Jill, https://docs.oracle.com/database/121/R...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Philip Wisse Sep 20, 2018, 11:01
Hi Philip Thanks i got it .. 22:31:54 SYS>sh...... Jill Salalila Sep 20, 2018, 15:13
The Oracle doc on MTTR: https://docs.oracle.com/e...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Philip Wisse Sep 20, 2018, 15:39
Hi Philip, Still i have some confusions. Pleas...... Jill Salalila Sep 21, 2018, 08:26
Hi, Recovery after restoring a backup: If you ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Philip Wisse Sep 21, 2018, 13:45
Hi Philip, Thanks for all replies again ! Qu...... Jill Salalila Sep 21, 2018, 14:47

Follow up by mail Click here


Subject: How much time Oracle needs to change checkpoint_change# value ?
Author: Jill Salalila, Philippines
Date: Sep 19, 2018, 19:03, 32 days ago
Os info: Oracle Linux 6.7
Oracle info: 12.1.0.2
Message: Dear Experts,

I am running following SQL on my Personal Machine ..

My Question is


How much time Oracle takes to trigger Checkpoint event ?

>>

02:46:31 SQL> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
5309963


02:51:48 SQL> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
5309963


03:02:55 SQL> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
5309963


Almost 15 mins crossed ..
Please see Machine time before SQL Prompt
Checkpoint_CHANGE# is not Changed

What could be the reason ?
How much time Oracle needs to trigger a Checkpoint Event ?

Thanks
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How much time Oracle needs to change checkpoint_change# value ?
Author: Philip Wisse, Netherlands
Date: Sep 20, 2018, 11:01, 31 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Jill,

https://docs.oracle.com/database/121/REFRN/GUID-C62A7B96-2DD4-4E70-A0D9-26EE4BFBE256.htm#REFRN30047
You're looking at the SCN of the last checkpoint.

So the answer is in you checkpoint parameters:
show parameter checkpoint

HTH, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How much time Oracle needs to change checkpoint_change# value ?
Author: Jill Salalila, Philippines
Date: Sep 20, 2018, 15:13, 31 days ago
Message: Hi Philip

Thanks i got it ..

22:31:54 SYS>show parameter checkpoint;

NAME TYPE VALUE
------------------------------------ ----------- ----------
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean TRUE


>> Snippet from Alert log

Thu Sep 20 22:22:25 2018
Incremental checkpoint up to RBA [0x51.121.0], current log tail at RBA [0x51.108db.0]

Thu Sep 20 22:52:35 2018
Incremental checkpoint up to RBA [0x51.10963.0], current log tail at RBA [0x51.113a7.0]

some Questions

Could you please elaborate little more about FAST_START_MTTR_TARGET Parameter.

1) How it does crash recovery exactly ?
2) If instance is up and running, what is crash recovery here ?

Thanks





Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How much time Oracle needs to change checkpoint_change# value ?
Author: Philip Wisse, Netherlands
Date: Sep 20, 2018, 15:39, 31 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: The Oracle doc on MTTR:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/FAST_START_MTTR_TARGET.html#GUID-D2452F33-0FD8-4297-85EC-FF63A0F03499

Also read the doc on REDO log files:
https://docs.oracle.com/database/121/TGDBA/pfgrf_build_db.htm#TGDBA94147

How does it work?
Oracle writes changes to the redologs with priority.
Writing to the datafiles can be delayed.
The delay is beneficial if a block gets changed multiple times then it needs to be written only once.
You can specify how lazy you want Oracle to be!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How much time Oracle needs to change checkpoint_change# value ?
Author: Jill Salalila, Philippines
Date: Sep 21, 2018, 08:26, 30 days ago
Message: Hi Philip,

Still i have some confusions.
Please clarify.

Oracle doc says definition for FAST_START_MTTR_TARGET parameterr.

FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform crash recovery of a single instance

If so, how oracle can use FAST_START_MTTR_TARGET parameter for Incremental backup ?

I am bit confused.
Please clarify

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How much time Oracle needs to change checkpoint_change# value ?
Author: Philip Wisse, Netherlands
Date: Sep 21, 2018, 13:45, 30 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi,

Recovery after restoring a backup:
If you restore a cold backup there is no need for recovery: it is consistent
If you restore a warm backup you will need recovery.
Recovery will prompt for archivelogs (archived redologs) and possibly many of them.

Crash recovery:
If you bring up your database after a clean shutdown: no need for recovery it is consistent
If you bring up your database after a 'shutdown abort' or power failure: you will need recovery
Recovery in this case only needs the current online redo log file since each log switch comes with a checkpoint.

Typically sequence in time:
1. rollforward of committed transactions (to make the database consistent)
2. open the database (to end the downtime)
3. rollback of uncommitted transactions (which can always be done later on)

So there is no relation between FAST_START_MTTR_TARGET and (incremental) backups.
Of course crash recovery will take longer is FAST_START_MTTR_TARGET is large.
Perhaps you want to refrase your question?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How much time Oracle needs to change checkpoint_change# value ?
Author: Jill Salalila, Philippines
Date: Sep 21, 2018, 14:47, 30 days ago
Message: Hi Philip,

Thanks for all replies again !

Question was 'Incremental backup' but It was typo error !

>> Actually i though to ask following Question ..

How oracle can use FAST_START_MTTR_TARGET parameter for Incremental Checkpoint ?


REF_LINK
---------
https://avdeo.com/2015/09/07/how-many-checkpoints-in-oracle-database/

I checked many databases.
Nowhere enabled this parameter.

We leave at 0 for FAST_START_MTTR_TARGET parameter.

Thanks again







Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here