Note_Tech

All technological notes.

Project maintained by simonangel-fong Hosted on GitHub Pages — Theme by mattgraham

DBA - Undo

Back

Undo

example_undo

Transactions and Undo Data

Categories of Undo

Lab: Undo Data

Commit Transaction

show user;
-- USER is "PDBTS_ADMIN"
show con_name;
--CON_NAME
----------------------------
--PDBTS

-- create tb
create table emp
(
    n number,
    sal number
);


-- insert data
insert into emp values (1,500);
insert into emp values (2,400 );

-- query transaction within current session
select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where  a.saddr = b.ses_addr
order by b.used_ublk desc;
-- 43	19457	PDBTS_ADMIN	2	1
--USED_UBLK = Number of undo blocks used
--USED_UREC = Number of undo records used
  • the number of undo records is the number of transaction made.
insert into emp values (3,700);

select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where  a.saddr = b.ses_addr
order by b.used_ublk desc;
--43	19457	PDBTS_ADMIN	3	1
-- one more undo record

commit; --this mean end of transaction

select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where  a.saddr = b.ses_addr
order by b.used_ublk desc;
-- return no row
-- undo data will be removed when transaction ends.

Rollback Transaction

insert into emp values (1,500);
insert into emp values (2,400 );

select s.sid, s.serial#, s.username, t.used_urec, t.used_ublk
from   v$session s,
       v$transaction t
where  s.saddr = t.ses_addr
order by t.used_ublk desc;
-- 43	19457	PDBTS_ADMIN	2	1

rollback;

select s.sid, s.serial#, s.username, t.used_urec, t.used_ublk
from   v$session s,
       v$transaction t
where  s.saddr = t.ses_addr
order by t.used_ublk desc;
-- return no row

Implicitly Commit by DDL

-- DDL

SELECT count(*)
FROM emp;
-- 3

insert into emp values (1,500);
insert into emp values (2,400);

select s.sid, s.serial#, s.username, t.used_urec, t.used_ublk
from   v$session s,
       v$transaction t
where  s.saddr = t.ses_addr
order by t.used_ublk desc;
-- 43	19457	PDBTS_ADMIN	2	1

-- DDL
ALTER TABLE emp
RENAME COLUMN sal TO sale;

select s.sid, s.serial#, s.username, t.used_urec, t.used_ublk
from   v$session s,
       v$transaction t
where  s.saddr = t.ses_addr
order by t.used_ublk desc;
-- return no row

SELECT count(*)
FROM emp;
-- 5

Undo Data VS Redo Data

diagram_undo_redo

  • When update and commit, the data will first go to the redo log files.
  • The data will move from redo log file to the database file.
  Undo Redo
Used for Rollback, Read Consistency, Flashback Rolling Forward
Stored in Undo segments Redo Log Files

diagram_undo_redo

  • backup + archive redo log => Restore and Recovery

UNDO TABLESPACE

Lab: Undo TBSP

dbca

sqlplus /  as sysdba
show user
show con_name

-- move session to the pdbundo
alter session set container=pdbundo;
show con_name

-- grant pdb admin privileges
grant create session, dba to pdbundo_admin;

-- verify
connect pdbundo_admin@pdbundo as sysdba
show user
show con_name

Manually Create undo tbsp

show CON_NAME
--CON_NAME
--------------------------------
--PDBUNDO
show user
--USER is "PDBUNDO_ADMIN"

-- query tbsp in current pdb
SELECT TABLESPACE_NAME, BLOCK_SIZE, STATUS, CONTENTS, LOGGING, SEGMENT_SPACE_MANAGEMENT, COMPRESS_FOR
FROM dba_tablespaces;
--SYSTEM	8192	ONLINE	PERMANENT	LOGGING	    MANUAL
--SYSAUX	8192	ONLINE	PERMANENT	LOGGING	    AUTO
--UNDOTBS1	8192	ONLINE	UNDO	    LOGGING	    MANUAL
--TEMP	    8192	ONLINE	TEMPORARY	NOLOGGING	MANUAL
--USERS	    8192	ONLINE	PERMANENT	LOGGING	    AUTO
-- by default, oracle creates undo tbsp for a pdb.

-- ddl of default undo tbsp
-- note the autoextend
SELECT dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1') FROM dual;
--"CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
--  SIZE 104857600
--  AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
--  BLOCKSIZE 8192
--  EXTENT MANAGEMENT LOCAL AUTOALLOCATE"

-- the database know which undo to be used from the parameter UNDO_TABLESPACE
SHOW PARAMETER undo_tablespace
--NAME            TYPE   VALUE
----------------- ------ --------
--undo_tablespace string UNDOTBS1
-- the current db uses UNDOTBS1

-- create undo tbsp
CREATE UNDO TABLESPACE undotbs_k
DATAFILE '/u01/app/oracle/oradata/ORCL/pdbundo/undotbs_k01.dbf'
SIZE 10M;

-- query ddl
SELECT dbms_metadata.get_ddl('TABLESPACE','UNDOTBS_K') FROM dual;
--"CREATE UNDO TABLESPACE "UNDOTBS_K" DATAFILE
--  '/u01/app/oracle/oradata/ORCL/pdbundo/undotbs_k01.dbf' SIZE 10485760
--  BLOCKSIZE 8192
--  EXTENT MANAGEMENT LOCAL AUTOALLOCATE"
--
  • note: undo tbsp is not autoextend

Error due to limited space

-- switch undo tbsp
ALTER SYSTEM SET undo_tablespace=undotbs_k SCOPE=BOTH;

-- show current undo tbsp
show parameter undo_tablespace
--NAME            TYPE   VALUE
----------------- ------ ---------
--undo_tablespace string UNDOTBS_K

-- create tb
CREATE TABLE test100
(
    emp_id number,
    name varchar2(100)
);

-- query ddl
SELECT dbms_metadata.get_ddl('TABLE','TEST100') FROM dual;
--"
--  CREATE TABLE "PDBUNDO_ADMIN"."TEST100"
--   (	"EMP_ID" NUMBER,
--	"NAME" VARCHAR2(100)
--   ) SEGMENT CREATION DEFERRED
--  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
-- NOCOMPRESS LOGGING
--  TABLESPACE "USERS" "

INSERT INTO test100
VALUES(1,400);

-- query undo
select s.sid, s.serial#, s.username, t.used_urec, t.used_ublk
from   v$session s,
       v$transaction t
where  s.saddr = t.ses_addr
order by t.used_ublk desc;
-- 42	46524	PDBUNDO_ADMIN	1	1
-- have one record

-- insert 400000 data
BEGIN
    FOR i IN 1..400000
    LOOP
        INSERT INTO test100 VALUES (i, 'any dummy name');
    END LOOP;
    -- note: commit after insert all data, leading to insufficient size of undo
    COMMIT;
END;
--Error report -
--ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS_K'
--ORA-06512: at line 4
--30036. 00000 -  "unable to extend segment by %s in undo tablespace '%s'"
--*Cause:    the specified undo tablespace has no more space available.
--*Action:   Add more space to the undo tablespace before retrying
--           the operation. An alternative is to wait until active
--           transactions to commit.
/

Local Undo Mode Versus Shared Undo Mode

diagram_undo_mode

STARTUP UPGRADE
ALTER DATABASE LOCAL UNDO ON;

diagram_undo_mode

  • Uncommitted data will not be cloned since only the clean data and committed transactions are copied.

diagram_undo_mode

  • fails because in shared mode, database has only one shared undo tbsp. The clone of one pdb will affect other pdb.

Lab: Shared Undo Mode and clone pdb

sqlplus / as sysdba

-- confirm in the root
show user
show con_name

-- query undo mode
select property_value
from database_properties
where property_name='LOCAL_UNDO_ENABLED';
-- PROPERTY_VALUE
-- ------------------------------------------------------------------------------
-- TRUE

-- set local undo mode to be false
shutdown immediate
startup upgrade;
alter database local undo off;
shutdown immediate;
startup

-- verify
select property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';
-- PROPERTY_VALUE
-- ------------------------------------------------------------------------------
-- FALSE

-- move to a pdb
show pdbs
alter session set container=orclpdb;
show con_name
-- CON_NAME
-- ----------------------------
-- ORCLPDB
alter pluggable database open;

-- create table then do uncommited trans
create table test (n number);
insert into test values (1);

-- create path for dest pdb
mkdir /u01/app/oracle/oradata/ORCL/pdbkh1

sqlplus / as sysdba
show user
show con_name -- in root

-- clone pdb
create pluggable database pdbkh1 from orclpdb
create_file_dest = '/u01/app/oracle/oradata/ORCL/pdbkh1';
-- ERROR at line 1:
-- ORA-65035: unable to create pluggable database from ORCLPDB

-- get info of the error
host oerr ora 65035
-- 65035, 00000, "unable to create pluggable database from %s"
-- // *Cause:  An attempt was made to clone a pluggable database that did not have
-- //          local undo enabled.
-- // *Action: Enable local undo for the PDB and and retry the operation.

shutdown immediate
startup upgrade;
alter database local undo on;
shutdown immediate;
startup

-- verify
select property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';
-- TRUE

-- Now it can clone pdb.
create pluggable database pdbkh1 from orclpdb
create_file_dest = '/u01/app/oracle/oradata/ORCL/pdbkh1';  2
-- Pluggable database created.

-- clean up
DROP PLUGGABLE DATABASE pdbkh1 INCLUDING DATAFILES;

Automatic Undo Management

Flashback

Lab: Flashback Table

select * from employees;

-- create a tb copying from employees
create table emp_copy3
as
select * from employees;

select * from emp_copy3;

-- drop tb
--  DDL statement automatically committed
drop table emp_copy3;

select * from emp_copy3;
-- ORA-00942: table or view does not exist
-- 00942. 00000 -  "table or view does not exist"

-- query recyclebin
select * from recyclebin;
-- BIN$GouL2faYaDngYyUAqMBjcw==$0	EMP_COPY3	DROP	TABLE	USERS	2024-06-10:11:07:23	2024-06-10:11:10:14	11200544		YES	YES	73404	73404	73404	8
  • Note the DROPSCN: 11200544
flashback  table emp_copy3 to before drop;

-- verify
select * from emp_copy3;

-- query recyclebin
select * from recyclebin;
-- return no row

-- Command to empty the recycle bin
purge recyclebin

Lab: Flashback Version Query

-- query before new row version
select salary from
employees
where employee_id=107;
-- 4200

-- update tb to create a new row version
update
employees
set salary=salary+100
where employee_id=107;
commit; -- thi will make new scn

-- query after new row version
select salary from
employees
where employee_id=107;
-- 4300

-- query version
select
    versions_starttime
    , versions_endtime
    , salary
from employees
versions between scn minvalue and maxvalue  -- specify time interval
where employee_id=107;
--10-JUN-24 11.21.01.000000000 AM   (null)                          4300
--(null)                            10-JUN-24 11.21.01.000000000 AM	4200

-- create a new row version
update
employees
set salary=salary+100
where employee_id=107;
commit;

-- query version
select
    versions_starttime
    , versions_endtime
    , salary
from employees
versions between scn minvalue and maxvalue  -- specify time interval
where employee_id=107;
--10-JUN-24 11.27.51.000000000 AM	  (null)	                            4400
--10-JUN-24 11.21.01.000000000 AM   10-JUN-24 11.27.51.000000000 AM     4300
--(null)                            10-JUN-24 11.21.01.000000000 AM	    4200

-- query after 15min
select
    versions_starttime
    , versions_endtime
    , salary
from employees
versions between scn minvalue and maxvalue
where employee_id=107;
-- (null)   (null)  4400

Undo Retention Period

example_undo_retention

Automatic Tuning of Undo Retention

Lab: Undo Retention

show user;
-- USER is "PDBTS_ADMIN"
show con_name;
--CON_NAME
--------------------------------
--PDBTS

-- query parameter
show parameter undo
--NAME              TYPE    VALUE
------------------- ------- --------
--temp_undo_enabled boolean FALSE
--undo_management   string  AUTO
--undo_retention    integer 900
--undo_tablespace   string  UNDOTBS1

-- query tbsp
-- note the retention: NOGUARANTEE
-- NOGUARANTEE:
select *
from DBA_TABLESPACES;
-- UNDOTBS1	8192	65536		1	2147483645	2147483645		65536	ONLINE	UNDO	LOGGING	NO	LOCAL	SYSTEM	NO	MANUAL	DISABLED	NOGUARANTEE	NO	HOST	NO		DISABLED					SHARED	DISABLED					OFF	N

-- query ddl
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','UNDOTBS1') FROM dual;
--  CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
--  SIZE 120586240
--  AUTOEXTEND ON NEXT 120586240 MAXSIZE 32767M
--  BLOCKSIZE 8192
--  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
-- note: enable AUTOEXTEND
  • RETENTION: NOGUARANTEE
    • Oracle will ignore the retention period. The old data can be overwritten more or less than 900, according to the size of undo tbsp.
  • UNDO Tablespace: enable AUTOEXTEND
    • Oracle will adjust the undo_retention automaticlly (for active query)
    • but flashback operations ( maybe or maybe not work )
-- change the retention
alter system set undo_retention=1;

-- create tb
create table xyz
(id number primary key, val number );

-- insert data
begin
for i in 1..500000
loop
insert into xyz values (i,500);
end loop;
commit;
end;
/

-- verify data
select * from xyz
order by 1;

--now do this without commit

-- update data
update xyz
set val=0
where id =500000;
-- data is in the undo tbsp
  • Change the parameter undo_retention to 1s.
  • The updated data is in the undo and only can be seen in the current session.
-- In session B

-- create tb
create table xyz_copy
(id number primary key, val number );

--this wil insert the data from xyz to xyz_copy
begin
for i in (select * from xyz)
loop
insert into xyz_copy values (i.id, i.val );
end loop;
commit;
end;
/
  • note: the value of id 500000 still 500 because the user did not commit
-- session A
commit

-- verify the data
select * from xyz_copy
where id=500000;
-- 500000	500
  • Oracle adjust the undo retention automatically when the undo tbsp is autoextend, and it ignore the paramter.
    • In this example, undo tablespace is autoextend and undo_retention is set to be 1s.
    • When the session A committed the data, the committed data became unexpired undo, which is used by active query or flashback.
      • unexpired undo is supposed to be kept for 1s.
      • Actually, Oracle automatically adjust the undo retention.
  • The Oracle ignores the undo_retention parameter.
  • The value in session B is the old version, 500, due to read consistency.
    • The commit in session A is after the plsql in session B. To keep the read consistency in session B, the old version in undo is used.
    • This also proves that the actual retention is automatically managed, ignoring the value in the paramter.
--flashback maybe or maybe not work

select versions_starttime,versions_endtime, val from
 xyz
versions between scn minvalue and maxvalue
where id=500000;
--10-JUN-24 12.46.22.000000000 PM		0
--	10-JUN-24 12.46.22.000000000 PM	500
  • Even flashback works for this case, it cannot work. It depends on the size of undo tbsp.

Retention Guarantee

TOP