Managing UNDO Data
Local Undo Mode Versus Shared Undo Mode
"LOCAL_UNDO_ENABLED= true" - This mean each pluggable can have its own undo
There is only one shared UNDO tablespace(in CDB root).
There can be a local UNDO tablespacein each PDB
You can set a CDB in local UNDO mode either at CDB creation or by altering the CDB property.
table :DATABASE_PROPERTIESproperty LOCAL_UNDO_ENABLED= true note: it is true by default in release 18c
[oracle@test ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 18 10:57:58 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show user
USER is "SYS"
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
# 각 PDB는 전용 UNDO 데이틀 스페이스를 가진다
SQL> select property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';
PROPERTY_VALUE
----------------------
TRUE
# shutdown immediate;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
# startup upgrade;
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 1526723608 bytes
Fixed Size 9135128 bytes
Variable Size 1023410176 bytes
Database Buffers 486539264 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MIGRATE YES
3 ORCLPDB MOUNTED
6 PROD1 MOUNTED
7 PROD2 MOUNTED
8 PDB10 MOUNTED
# alter database local undo off; UNDO 테이블 스페이스를 공용으로 전환
SQL> alter database local undo off;
Database altered.
# shutdown immediate;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
# startup;
SQL> startup;
ORACLE instance started.
Total System Global Area 1526723608 bytes
Fixed Size 9135128 bytes
Variable Size 1023410176 bytes
Database Buffers 486539264 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
6 PROD1 MOUNTED
7 PROD2 MOUNTED
8 PDB10 READ WRITE NO
# database_properties, UNDO 테이블 스페이스 설정이 공용으로 바뀐것을 확인.
SQL> select property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';
PROPERTY_VALUE
---------------------
FALSE
# show pdbs and choose any one
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
6 PROD1 MOUNTED
7 PROD2 MOUNTED
8 PDB10 READ WRITE NO
# alter session set container=orclpdb;
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
# create table then do uncommited trans
SQL> create table test ( n number );
Table created.
SQL> insert into test values (1);
1 row created.
insert 된 데이터를 commit 하지 않은 상태로 새로운 터미널에서 oracle 을 새로 접속한다.
그래서 PDB 복제를 할때 오류가 발생한다.
이유는 UNDO 테이블 스페이스를 공용 모드로 사용할때는(모든 PDB에서 하나의 UNDO 를 공유), commit 완료되지 않은 transaction 이 있으면 복제할 수 없다.
# open another terminal
1- create directory in the path /u01/app/oracle/oradata/ORCL, name it pdb11 for example
2- open sqlplus / as sysdba and be sure u r in the root
3- try to do this
[oracle@test pdb11]$ pwd
/u01/app/oracle/oradata/ORCL/pdb11
[oracle@test pdb11]$
SQL> create pluggable database pdb11 from orclpdb create_file_dest = '/u01/app/oracle/oradata/ORCL/pdb11';
create pluggable database pdb11 from orclpdb create_file_dest = '/u01/app/oracle/oradata/ORCL/pdb11'
*
ERROR at line 1:
ORA-65035: unable to create pluggable database from ORCLPDB
SQL> 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.
//
# back again to LOCAL_UNDO_ENABLED= true
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 1526723608 bytes
Fixed Size 9135128 bytes
Variable Size 1023410176 bytes
Database Buffers 486539264 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MIGRATE YES
3 ORCLPDB MOUNTED
6 PROD1 MOUNTED
7 PROD2 MOUNTED
8 PDB10 MOUNTED
SQL> alter database local undo on;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1526723608 bytes
Fixed Size 9135128 bytes
Variable Size 1023410176 bytes
Database Buffers 486539264 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> select property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';
PROPERTY_VALUE
---------------------
TRUE
SQL> create pluggable database pdb11 from orclpdb create_file_dest = '/u01/app/oracle/oradata/ORCL/pdb11';
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 PDB11 MOUNTED <<<<<<<<<<<<<<
6 PROD1 MOUNTED
7 PROD2 MOUNTED
8 PDB10 READ WRITE NO
Clone PDB
# session 1 에서 처리할 명령어.
SQL> show user
USER is "SYS"
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
SQL> select * from test;
no rows selected
SQL> insert into test values (1);
1 row created.
SQL> select * from test;
N
----------
1
# commit 을 하지 않은 상태를 유지.
------------------------------------------------------------------------
# session 2 에서 처리할 명령어.
[oracle@test ORCL]$ sqlplus / as sysdba
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> create pluggable database pdb11 from orclpdb create_file_dest = '/u01/app/oracle/oradata/ORCL/pdb11';
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
5 PDB11 MOUNTED
6 PROD1 MOUNTED
7 PROD2 MOUNTED
8 PDB10 READ WRITE NO
SQL> alter pluggable database pdb11 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
5 PDB11 READ WRITE NO
6 PROD1 MOUNTED
7 PROD2 MOUNTED
8 PDB10 READ WRITE NO
SQL> alter session set container=pdb11;
Session altered.
SQL> show user
USER is "SYS"
SQL> show con_name
CON_NAME
------------------------------
PDB11
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
5 PDB11 READ WRITE NO
SQL> select * from test;
no rows selected
# session1 에서는 삽입된 데이터가 commit 을 하지 않았기 때문에
# 복제된 PDB11 에서는 조회가 되지 않는다.
'Database > Oracle' 카테고리의 다른 글
| 103 - [Oracle 19C] About the Undo Retention Period (1) | 2023.12.19 |
|---|---|
| 102 - [Oracle 19C] Overview of Automatic Undo Management (0) | 2023.12.19 |
| 100 - [Oracle 19C] Comparing Undo Data and Redo Data (0) | 2023.12.18 |
| 99 - [Oracle 19C] Managing UNDO Data (1) | 2023.12.18 |
| 98 - [Oracle 19C] Managing Resumable Space Allocation (0) | 2023.12.16 |
