DBA Blogs

appendChildXML with parent-namespace in the child

Tom Kyte - Tue, 2016-10-11 12:26
Hi Chris & Connor I want to append child nodes to an XML but have problems because of the (parent)namespace in the children: The child node in appendchildxml() is not a valid XML because of the namespace that comes from the parent. The XML shou...
Categories: DBA Blogs

PDF pages in Oracle Apex 5

Tom Kyte - Tue, 2016-10-11 12:26
Dear Tom how to open a specific page or range of pages of a PDF file in oracle APEX 5. Let us say that I want to display only pages 15-20 of a PDF file stored in a database table. I am able to open the PDF file in Apex 5, but I need to open a spec...
Categories: DBA Blogs

file not importing in apex 4.2

Tom Kyte - Tue, 2016-10-11 12:26
Guys, I am getting erro, that " File is not valid Application Express application export file " When importing exported SQL application file, I am selecting Unicode UTF - 8 when importing, I am using apex 4.2 version. My possibilitie...
Categories: DBA Blogs

Log file sync

Tom Kyte - Tue, 2016-10-11 12:26
Hi There, I found slow performing pl/sql functionality indicated to be slow only when a backup of rachivelog is in progress. Checking the events using AWR and ASH reports there is clear indication for "Log file sync" waiting event for a delete...
Categories: DBA Blogs

OTN Appreciation Day : Undo and Redo

Hemant K Chitale - Mon, 2016-10-10 20:06
On OTN Appreciation Day, let me say I like the Undo and Redo features of Oracle.  I name them together as they work together.

Undo also supports MultiVersionReadConsistency -- a great advantage of Oracle.

Redo, with Archive Logging, also supports Online Backups -- an absolute necessity.

These features have been around for almost 30 years now.

Here are some Quick and Rough Notes on Undo and Redo.
Categories: DBA Blogs

Undo and Redo

Hemant K Chitale - Mon, 2016-10-10 20:04
Quick and Rough Notes :

Undo and Redo

Undo is where Oracle logs how to reverse a transaction (one or more DMLs in a transaction)

Redo is where Oracle logs how to replay a transaction

Undo and Redo are written to as the transaction proceeds, not merely at the end of the transaction
(imagine a transaction that consists of 1million single-row inserts, each distinct insert is written to undo and redo)
Undo segments
Oracle dynamically creates and drops Undo segments depending on transaction volume
An undo segment consists of multiple extents. As a transaction grows beyond the current extent, a new extent may be allocated
One undo segment can support multiple transactions but a transaction cannot span multiple undo segments
After COMMIT the undo information is retained for undo_retention or autotuned_undo_retention.
At the end of the retention period, the undo is discarded, the extent is expired

Undo retention
Oracle may autotune the undo retention
If the datafile(s) for the active undo tablespace are set to autoextend OFF, Oracle automatically uses the datafile to the fullest and ignores undo_retention
If the datafile(s) are set to autoextend ON, Oracle autotunes undo_retention to match query lengths
Check V$undostat for this information

Undo and Read Consistency
Oracle's implementation of MultiVersionReadConsistency relies on a user session being able to read the undo generated by another session
A session may need to read the prior image of data because the data has been modified (and may even have been commited) by another session
It clones the current version of the block it is reading and applies the undo for that block to create its read consistent version
Flashback Query is supported by reading from Undo
Read Consistency with READ COMMITTED is at *statement* level by default
A session running multiple queries may each read a different version by default because Read Committed is enforced for each statement
(This also means that if you have a PLSQL block running the same SQL multiple times, each execution can see a different version of the data-- if the data is modified by another session between executions of the SQL !)
A session can choose to set it's ISOLATION LEVEL to SERIALIZABLE which means that every query sees the same version of data
This works only for short running queries and with few changes to the data or read only data.
SERIALIZABLE can update data provided that the same data hasn't been updated and committed by another session after the start (else you get ORA-08177)
READ ONLY does not allow the session to make changes

When a transaction is in progress, it is identified by the Transaction Address, Undo segment, slot and sequence
The ITL slot in the block header contains the reference (address) to the Undo
The SCN is assigned at commit time (therefore a transaction doesn't begin with an SCN)

Temp Undo
12c also allows temporary undo
Normally, changes to GTT generate undo which needs to be written to undo segments
With 12c temp undo, those undo entries are also, like the actual changes, temporary and can be discarded when the commit is issued
Thus, the undo doesn't need to be written to disk (remember data in a GTT is not visible to another session, so there is no need to persist the undo)
Redo also captures Undo One transaction (or multiple concurrent transactions) may have updated multiple database blocks So, DBWR may have written down some of the modified buffers to disk, even before the transaction COMMIT has been issued This means that some of the blocks on disk may have uncomitted changes What happens if the instance were to fail (e.g. a bug takes down a background process or the server crashes due to an OS bug or a CPU failure ?) On instance recovery, Oracle must identify the uncommited transactions and roll them back But if the undo for that was only in memory and was lost on instance/server failure, how can Oracle rollback the uncomitted transaction ? Oracle knows that it must "undo" modified blocks This is done by protecting the undo through the redo as well Before a modified buffer is written to disk by DBWR, LGWR writes the redo for it That redo also captures the undo This ensures that, on the need to do Instance Recovery or Media Recovery, the undo is also available The Rollforward process writes the undo to the undo segments This allows Oracle to rollback the uncommitted transaction because the undo is now on disk (and not lost from memory) Redo Strands Redo consists of multiple strands Since 10g, Oracle has introduced private strands for single-instance databases This allows a process to manage it's private strand of redo until it decides to commit At commit time, the private strand is written into the public redo area and this allows LGWR to flush the redo to disk IMU Similarly, Oracle also manages undo "in memory" (using IMU pools). This means that, for a short period or small transactions, Undo is managed in memory rather than through undo segments Therefore, Oracle doesn't have to track undo segment changes in the redo This also allows bundling the undo for multiple changes into a single redo record, instead of separate redo records RAC In RAC, every instance has (a) a seperate Redo Thread (b) a separate Undo Tablespace However, the redo thread must be readable by every other instance -- as instance recovery by another (surviving) instance needs to read the redo Similarly, the undo tablespace is read by any other instance because queries in instance 2 may need to read undo of instance 1 for read-consistency
Categories: DBA Blogs

Input parameters tramsission into logging procedure

Tom Kyte - Mon, 2016-10-10 18:06
Hello, Tom! I'm sure, my problem is not nontrivial one, but I've certainly stuck on it and need your help. This is it. I need to write a logging procedure that will catch exceptions from other pl-sql blocks and assembly error messages with the str...
Categories: DBA Blogs

Inconsistent results from queries involving date predicates

Tom Kyte - Mon, 2016-10-10 18:06
Hey there, I am confused to see inconsistent query results from (seemingly) same queries: <code> SELECT NVL(ROUND(SUM(TRD_BRKRG_VL)/10000000,2),0) FROM TRD_TRD_DTLS WHERE TRUNC(TRD_TRD_DT) BETWEEN '01-Apr-2014' AND '31-Mar-2015' </code>...
Categories: DBA Blogs

identify increasing amount.

Tom Kyte - Mon, 2016-10-10 18:06
Hi Tom, I have just came across a requirement, where I need to identify the increasing amount. say I have a table for quarterly expenses and I need to identify the dept which has increasing expense by 105 or more from previous quarter, and if any dep...
Categories: DBA Blogs

Passing array size for a structure dynamically in PRO*C using a select query in a function.

Tom Kyte - Mon, 2016-10-10 18:06
Hi, I have declared a C variable in declare section and trying to get its value using a select query from a function as follwing, Note: I have mentioned only relevant codelines. ---------------------------------------------------------------- #...
Categories: DBA Blogs

How to Update millions or records in a table

Tom Kyte - Mon, 2016-10-10 18:06
Good Morning Tom. I need your expertise in this regard. I got a table which contains millions or records. I want to update and commit every time for so many records ( say 10,000 records). I dont want to do in one stroke as I may end up in Roll...
Categories: DBA Blogs

ODBC changes national characters to latin equivelant

Tom Kyte - Mon, 2016-10-10 18:06
Hi all, I ran into a bit of a problem using Oracle database through ODBC from C++. The situation is as follows: - There is an Oracle database installed on a Linux OS - There is a C++ component which is communicating with that database throug...
Categories: DBA Blogs

ORA-27465 when setting File Watcher's attribute steady_state_duration a value less that 10 second

Tom Kyte - Sun, 2016-10-09 23:46
Hi, I am trying to set an interval of 5 seconds to steady_state_duration while creating a file watcher or change the interval to an existing file watcher. BEGIN DBMS_SCHEDULER.CREATE_FILE_WATCHER ( file_watcher_name => 'TEST_FILEW...
Categories: DBA Blogs

oracle real time apply on standby

Learn DB Concepts with me... - Sun, 2016-10-09 11:47
                   Oracle Real Time Apply on Standby

By default, log apply services wait for the full archived redo log file to arrive on the standby database before applying it to the standby database. If the real-time apply feature is enabled, log apply services can apply redo data as it is received from the Primary DB, without waiting for the current standby redo log file to be archived. We can use the ALTER DATABASE statement to enable the real-time apply feature, as below:

  • For physical standby databases, issue the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE statement.

  • For logical standby databases, issue the ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE statement.

NOTE : Standby redo log files are required to use real-time apply.

Lets Test it:

oracle@ORCLSTDBY:[~] $ sqlplus /"as sysdba"

SQL*Plus: Release Production on Tue Oct 4 10:57:52 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

-------------------- ---------- ---------- --- ------------------------------

-- Dest_id can be different in your database. but mostly it will be set to local.
-- Lets start applying logs and start the recovery mode Default (apply on log fill up)

SQL> recover managed standby database disconnect from session;
Media recovery complete.

-- Query the Recovery Mode now:

SQL> col DEST_NAME format A20
col status format A10
col type format A10
col recovery_mode format A30

select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

-------------------- ---------- ---------- --- ------------------------------

-- See that Recovery Mode will be just Managed. 

-- Lets stop log Apply and change it the recovery mode to Real-Time Apply

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME                       STATUS      TYPE       SRL        RECOVERY_MODE
--------------------                   ----------       ----------     ---          ------------------------------

-- We can also check this in alertlog_File.log

Completed: ALTER DATABASE RECOVER  managed standby database cancel
Tue Oct 04 11:00:47 2016
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (ORCLSTDBY)
Tue Oct 04 11:00:47 2016
MRP0 started with pid=58, OS id=40557
MRP0: Background Managed Standby Recovery process started (ORCLSTDBY)
 started logmerger process
Tue Oct 04 11:00:52 2016
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 64 slaves
Waiting for all non-current ORLs to be archived...

Reference Oracle Docs:


 Similar Posts :

Categories: DBA Blogs

sql query related question

Tom Kyte - Sun, 2016-10-09 05:26
i want to compare this month data(ex:sep-2016) with previous year month data(ex:sep-2015) in same page of SQL PLUS by using date column(i.e.DAL_ARR_DATE_TIME) of my table,so please give me a solution for the same.thanks in advance. my table structur...
Categories: DBA Blogs

Implicit rollback on error in NDS and dbms_sql

Tom Kyte - Sun, 2016-10-09 05:26
Good day. Could you please explain to me why the following script returns the next output? <code> in foo_proc Static call - 1 in foo_proc NDS - 0 in foo_proc dbms_sql - 0 </code> Why the inserted row is implicitly rollbacked before th...
Categories: DBA Blogs

Physical Standby database - user sync

Tom Kyte - Sun, 2016-10-09 05:26
Will new users created on the primary database be synced to the standby database automatically?
Categories: DBA Blogs

non-cdb architecture on 12c

Tom Kyte - Sun, 2016-10-09 05:26
Tom. If we upgrade our 11g database to 12c, can we still use the non-cdb model? With 11g going into extended support very soon we would like to upgrade to 12c however, we are not familiar with PDBs yet so we would prefer to mo...
Categories: DBA Blogs

ORA-01156 When Adding Standby Redo Log in Dataguard Configuration

Pakistan's First Oracle Blog - Sat, 2016-10-08 20:20
Database version =

If you get following error while adding a Standby Redo Log (SRL) to a standby database in dataguard configuration:

SQL> alter database add standby logfile group 3 '/logs/test/sbyredo3a.log' size 500M;
alter database add standby logfile group 3 '/logs/test/sbyredo3a.log' size 500M
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

Then it means that first you have to stop the redo apply, add the SRL and then start the redo apply. Best way to do is from dgmgrl like this:

DGMGRL> connect /

DGMGRL> edit database 'test' set state='APPLY-OFF';

SQL> alter database add standby logfile group 3 '/logs/test/sbyredo3a.log' size 500M;

Database altered.

DGMGRL> edit database 'CONPRO_UK' set state='APPLY-ON';


Categories: DBA Blogs

How to speed up an Insert with SELECT

Tom Kyte - Sat, 2016-10-08 11:06
Hi Tom, good morning Sir. I am trying to speed up an Insert statement where the SELECT is selecting all the source table according to this example: var_select_a_insert := 'insert into lwm_usrappods.ITEM_LOC_SOH select to_number...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs