100 - [Oracle 19C] Comparing Undo Data and Redo Data

 

• Managing UNDO Data

Comparing  Undo Data and Redo Data

 

Redo log files
stores changes to the database as they occur and are used for data recovery.

 

 

	UNDO	Redo
Used for	Rollback, read consistency, flashback	Rolling forward of database changes
Stored in	Undo segments	Redo Log Files

 

 

 

 

Example

 

show user;
show CON_NAME;
/*
USER is "PDB10_ADMIN"
CON_NAME 
------------------------------
PDB10
*/

 

 

 

select TABLESPACE_NAME,BLOCK_SIZE,STATUS,CONTENTS,LOGGING,SEGMENT_SPACE_MANAGEMENT,COMPRESS_FOR
from dba_tablespaces;
/*
TABLESPACE_NAME BLOCK_SIZE STATUS    CONTENTS   LOGGING   SEGMEN COMPRESS_FOR
--------------- ---------- --------- ---------- --------- ------ ------------
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               
TS1                   8192 ONLINE    PERMANENT  LOGGING   AUTO               
TBSALERT              8192 ONLINE    PERMANENT  LOGGING   AUTO               

7 rows selected. 
*/


SELECT DBMS_METADATA.GET_DDL('TABLESPACE','UNDOTBS1') FROM dual;
/*
DBMS_METADATA.GET_DDL('TABLESPACE','UNDOTBS1')                                  
--------------------------------------------------------------------------------
  CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE 
  '/u01/app/oracle/oradata/ORCL/PDB10/undotbs01.dbf' SIZE 104857600
  AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
   ALTER DATABASE DATAFILE 
  '/u01/app/oracle/oradata/ORCL/PDB10/undotbs01.dbf' RESIZE 183500800
*/

/*
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'/u01/app/oracle/oradata/ORCL/PDB10/undotbs01.dbf' SIZE 10457600
AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
BLOCKSIZE 8192
EXTENT MANAGER LOCAL AUTOALLOCATE
*/


# the database know which undo to be used for the parameter UNDO_TABLESPACE

show parameter UNDO_TABLESPACE
/*
NAME            TYPE   VALUE    
--------------- ------ -------- 
undo_tablespace string UNDOTBS1 
*/

 

 

 

# let us create new UNDO TABLESPACE

CREATE UNDO TABLESPACE UNDOTBS_k DATAFILE
'/u01/app/oracle/oradata/ORCL/PDB10/undotbs_k01.dbf' SIZE 10M;
/*
TABLESPACE UNDOTBS_K created.
*/


SELECT DBMS_METADATA.GET_DDL('TABLESPACE','UNDOTBS_K') FROM dual;
/*
DBMS_METADATA.GET_DDL('TABLESPACE','UNDOTBS_K')                                 
----------------------------------------------------------------------
  CREATE UNDO TABLESPACE "UNDOTBS_K" DATAFILE 
  '/u01/app/oracle/oradata/ORCL/PDB10/undotbs_k01.dbf' SIZE 10485760
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
*/

 

 

 

create table test100 (emp_id number, name varchar2(100) )
/*
Table TEST100 created.
*/


SELECT DBMS_METADATA.GET_DDL('TABLE','TEST100') FROM dual;
/*
DBMS_METADATA.GET_DDL('TABLE','TEST100')                                        
--------------------------------------------------------------------------------

  CREATE TABLE "PDB10_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);
/*
1 row inserted.
*/


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;
/*
       SID    SERIAL# USERNAME         USED_UREC  USED_UBLK
---------- ---------- --------------- ---------- ----------
       410      15469 PDB10_ADMIN              1          1
*/

# 1개의 인서트 데이터가 1개의 블록에 할당되어 있다.

 

 

 

begin
  for i in 1..400000
  loop
    insert into test100 values (i,'any dummy name' );
  end loop;
  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.
*/

# UNDO 테이블 스페이스의 용량이 작아서 오류가 발생한다.
# 아래의 쿼리로 데이터가 하나도 입력되지 않은 것을 확인한다.

select * from test100;
/*
    EMP_ID NAME      
---------- ----------
         1 400  
*/

 

 

 

# 테이블 스페이스가 추가된 것 확인.

select TABLESPACE_NAME,BLOCK_SIZE,STATUS,CONTENTS,LOGGING,SEGMENT_SPACE_MANAGEMENT,COMPRESS_FOR
from dba_tablespaces;
/*
TABLESPACE_NAME BLOCK_SIZE STATUS    CONTENTS   LOGGING   SEGMEN COMPRESS_FOR
--------------- ---------- --------- ---------- --------- ------ ------------
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               
TS1                   8192 ONLINE    PERMANENT  LOGGING   AUTO               
TBSALERT              8192 ONLINE    PERMANENT  LOGGING   AUTO               
UNDOTBS_K             8192 ONLINE    UNDO       LOGGING   MANUAL             

8 rows selected. 
*/


# UNDO 테이블 스페이스를 원래대로 변경.
ALTER SYSTEM SET undo_tablespace=UNDOTBS1 SCOPE=BOTH;
/*
System SET altered.
*/


show parameter UNDO_TABLESPACE
/*
NAME            TYPE   VALUE    
--------------- ------ -------- 
undo_tablespace string UNDOTBS1 
*/


# 데이터 입력이 문제없이 잘되는것을 확인.
begin
  for i in 1..400000
  loop
    insert into test100 values (i,'any dummy name' );
  end loop;
  commit;
end;
/*
PL/SQL procedure successfully completed.
*/


# 데이터가 입력된 것을 확인.
select * from test100 where emp_id > 399990 order by emp_id desc;
/*
    EMP_ID NAME                
---------- --------------------
    400000 any dummy name      
    399999 any dummy name      
    399998 any dummy name      
    399997 any dummy name      
    399996 any dummy name      
    399995 any dummy name      
    399994 any dummy name      
    399993 any dummy name      
    399992 any dummy name      
    399991 any dummy name      

10 rows selected. 
*/