enable local undo 以及设置每个pdb 的undo 的步骤

jnrjian

已于 2024-07-09 12:14:02 修改

阅读量461

点赞数 21

文章标签： oracle

于 2024-07-08 18:57:57 首次发布

本文链接：https://blog.csdn.net/jnrjian/article/details/140276309

版权

How to enable Local Undo and convert cdb from shared to local undo mode on RAC (Doc ID 3030347.1)

Oracle Database - Enterprise Edition - Version 19.22.0.0.0 and later
Information in this document applies to any platform.

GOAL

How to enable Local Undo and convert cdb from shared to local undo mode on RAC.

SOLUTION

The following steps need to be performed for enabling Local Undo and convert cdb from shared to local undo mode on RAC.

1) Check LOCAL_UNDO_ENABLED Is set to true. In this case, LOCAL_UNDO_ENABLED is not set.

SQL> SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED';

no rows selected

2) Connect to instance and set the cluster_database parameter to false.

SQL> alter system set cluster_database=false sid='*' scope=spfile;

System altered.

3) Stop database.

srvctl stop database -d <dbname>

4)Start the database in upgrade mode from one node manually.

sqlplus / as sysdba

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 1.0308E+11 bytes
Fixed Size 23396680 bytes
Variable Size 4.7245E+10 bytes
Database Buffers 5.5566E+10 bytes
Redo Buffers 245035008 bytes
Database mounted.
Database opened.

5)Issue an ALTER DATABASE LOCAL UNDO ON statement for enabling local undo.

SQL> alter database local undo on;

Database altered.

6)Restart the instance.

SQL> startup force;

ORACLE instance started.

Total System Global Area 734003200 bytes

Fixed Size 8261248 bytes

Variable Size 562037120 bytes

Database Buffers 50331648 bytes

Redo Buffers 8515584 bytes

In-Memory Area 104857600 bytes

Database mounted.

Database opened.

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE
-------------------- -------------------------
LOCAL_UNDO_ENABLED TRUE

7) Connect to PDB and check undo status.

Note: New undo tablespace(undo_*) will be created automatically by oracle at PDB level once LOCAL_UNDO_ENABLED parameter is set to TRUE in CDB.

SQL>alter session set container=PDB1;

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO

SQL> SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE

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

LOCAL_UNDO_ENABLED TRUE

SQL> select file_name from dba_data_files where tablespace_name like 'UNDO%';

FILE_NAME
--------------------------------------------------------------------------------
+DATA_01/ORCL/705A88E03CD01D91E0531A4DCA0A0DCD/DATAFILE/undo_2.532.1172182099

SQL> sho parameter undo

NAME TYPE VALUE
------------------------------------ ----------- -----------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 1800
undo_tablespace string UNDO_2

8) Set the cluster_database parameter to TRUE

SQL> alter system set cluster_database=true sid='*' scope=spfile;
System altered.

9) Stop database instance

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

10)Start the database by using srvctl

srvctl start database -d <dbname>

11)verify undo tablespace and parameter on node2 as well

SQL> alter session set container=PDB1;

SQL> select file_name from dba_data_files where tablespace_name like 'UNDO%';

FILE_NAME
--------------------------------------------------------------------------------
+DATA_01/ORCL/705A88E03CD01D91E0531A4DCA0A0DCD/DATAFILE/undo_1.532.1172182099
+DATA_01/ORCL/705A88E03CD01D91E0531A4DCA0A0DCD/DATAFILE/undo_2.532.1172182099

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

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> sho parameter undo

System altered.

SQL> alter pluggable database pdb1 close abort
2 ;

Pluggable database altered.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> sho parameter undo

------NODE2

SQL> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDO_2' is currently in use

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
6 PDB1 MOUNTED
SQL> alter system set undo_tablespace=UNDOTBS1 sid='cdb1';
alter system set undo_tablespace=UNDOTBS1 sid='cdb1'
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only

----需要node 1 alter system reset undo_tablespace;
SQL> alter pluggable database pdb1 open;

---正确的做法

SQL> alter system set undo_tablespace=UNDO_2 sid='cdb2';

System altered.

SQL> sho spparameter undo

SYMPTOMS

PDB open fails Intermittently with ORA-30013 error when Local Undo Is enabled in RAC environment

Start the database

srvctl start database -d DATABASE-1

When you check the status, one of the PDB is in mount state.

INST_ID NAME OPEN_MODE

-------------------------------------------------------------------
1               PDB$SEED               READ ONLY
1               PDB12                       READ WRITE
2               PDB$SEED               READ ONLY
2               PDB12                       MOUNTED <<<<<<<<<<<<<<<

When tried to manually open PDB on node 2, below error is reported

SQL> alter pluggable database PDB12 open;
alter pluggable database PDB12 open
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

Basically either one of the PDB instance can't be opened in read/write mode without manually issuing - alter system set undo_tablespace=UNDOTBS2 container=current scope=spfile;

-----

SQL> sho parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO_2
SQL>
SQL>
SQL> alter system set undo_tablespace=UNDOTBS2 container=current ;
alter system set undo_tablespace=UNDOTBS2 container=current
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-30012: undo tablespace 'UNDOTBS2' does not exist or of wrong type 要存在才行

SQL> alter system set undo_tablespace=UNDO_2 container=current ;

System altered.

SQL> sho parameter undo

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* temp_undo_enabled boolean
* undo_management string
* undo_retention integer
cdb2 undo_tablespace string UNDO_2
* undo_tablespace string UNDO_2--错了
SQL> alter system reset undo_tablespace container=current ;

ORA-00933: SQL command not properly ended

SQL> alter system reset undo_tablespace;

System altered.

SQL> sho spparameter undo

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

CHANGES

Local Undo Enabled.

CAUSE

PDB on each node try to allocate same Undo tablespace while opening and one of the PDB fails with ORA-30013.

SQL> alter pluggable database test open instances=all;

alter pluggable database test open instances=all
*
ERROR at line 1:
ORA-65107: Error encountered when processing the current task on instance:1
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

SOLUTION

As this database is enabled with LOCAL UNDO, assign specific Undo tablespace to each PDB on node-1 & node-2 by executing below commands.

On node-1:

alter system set undo_tablespace=UNDOTBS1 container=current sid='DBCDB1' scope=spfile; >>>> sid=First Instance

On Node-2

alter system set undo_tablespace=UNDOTBS2 container=current sid='DBCDB2' scope=spfile; >>>> sid=Second Instance

SCOPE

This document explains the various modes of Undo available in the Multitenant database from 12.2 onwards.

DETAILS

UNDO MODES IN MULTITENANT

Till 12cR1 the Undo tablespace used to be shared or global across all the PDBs in a container. From 12cR2 onwards, the local Undo is introduced. When local undo is enabled, each container has its own undo tablespace for every instance in which it is open. When local undo is disabled, there is one undo tablespace for the entire CDB.

The Undo mode , whether it’s local or shared, is the property of the entire CDB. Either all the PDBs are local undo mode or there’s shared undo for the entire CDB.

There are capabilities to switch between local and shared undo and back again. And the transition is seamless.

Local Undo Mode

Local undo mode means that each container has its own undo tablespace for every instance in which it is open. In this mode, Oracle Database automatically creates an undo tablespace for every container in the CDB (The initial size of undo datafile may vary). For an Oracle RAC CDB, there is one active undo tablespace for each instance for each PDB in local undo mode.

Local undo enables many of the major new capabilities of Multitenant in 12.2, including:

Hot Clone
Refresh PDB
PDB Relocate
Flashback PDB

Oracle recommends local mode.

Shared Undo Mode

Shared undo mode means that there is one active undo tablespace for a single-instance CDB. For an Oracle RAC CDB, there is one active undo tablespace for each instance.

We continue to support shared undo in 12.2, but that is for upgrade transitional purposes only.

How to check the current Undo mode:

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPE DESCRIPTION

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

LOCAL_UNDO_ENABLED TRUE true if local undo is enabled

HOW TO CONVERT CDB FROM LOCAL TO SHARED UNDO MODE

When a CDB is in local undo mode, you can change it to use shared undo mode by issuing an ALTER DATABASE LOCAL UNDO OFF statement and restarting the database.

When it is in shared undo mode, the CDB ignores any local undo tablespaces that were created when it was in local undo mode. Oracle recommends that you delete these local undo tablespaces.

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 734003200 bytes

Fixed Size 8261248 bytes

Variable Size 562037120 bytes

Database Buffers 50331648 bytes

Redo Buffers 8515584 bytes

In-Memory Area 104857600 bytes

Database mounted.

Database opened.

SQL> sho con_name

CON_NAME

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

CDB$ROOT

SQL> ALTER DATABASE LOCAL UNDO off;

Database altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

[oracle@xxx ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.0.3 Production on Fri Aug 5 14:09:04 2016

stConnected to an idle instance.

SQL

SQL> startup

ORACLE instance started.

Total System Global Area 734003200 bytes

Fixed Size 8261248 bytes

Variable Size 562037120 bytes

Database Buffers 50331648 bytes

Redo Buffers 8515584 bytes

In-Memory Area 104857600 bytes

Database mounted.

Database opened.

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME

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

PROPERTY_VALUE

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

DESCRIPTION

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

LOCAL_UNDO_ENABLED

FALSE

true if local undo is enabled

SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO';

CON_ID TABLESPACE_NAME

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

FILE_NAME

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

1 UNDOTBS1

/u01/app/oracle/oradata/orcl/undotbs01.dbf

<< Drop the Undo tablespace from the PDBs>>

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE;

Pluggable database altered.

SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO';

CON_ID TABLESPACE_NAME

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

FILE_NAME

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

3 UNDOTBS1

/u01/app/oracle/oradata/orcl/pdb1/undotbs01.dbf

1 UNDOTBS1

/u01/app/oracle/oradata/orcl/undotbs01.dbf

SQL> ALTER SESSION SET CONTAINER=PDB1;

Session altered.

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

HOW TO CONVERT CDB FROM SHARED TO LOCAL UNDO MODE

When a CDB is in shared undo mode, you can change it to use local undo mode by issuing an ALTER DATABASE LOCAL UNDO ON statement and restarting the database.

When a CDB is changed from shared undo mode to local undo mode, Oracle Database creates the required undo tablespaces automatically.

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 734003200 bytes

Fixed Size 8261248 bytes

Variable Size 562037120 bytes

Database Buffers 50331648 bytes

Redo Buffers 8515584 bytes

In-Memory Area 104857600 bytes

Database mounted.

Database opened.

SQL> alter database local undo on;

Database altered.

SQL> startup force;

ORACLE instance started.

Total System Global Area 734003200 bytes

Fixed Size 8261248 bytes

Variable Size 562037120 bytes

Database Buffers 50331648 bytes

Redo Buffers 8515584 bytes

In-Memory Area 104857600 bytes

Database mounted.

Database opened.

SQL> show con_name

CON_NAME

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

CDB$ROOT

<< Create Undo tablespace for PDB$SEED. This is an optional step and can be used to customize the Undo tablespace, created for all the PDBs.>>

SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE FORCE;

Pluggable database altered.

SQL> ALTER SESSION SET CONTAINER=PDB$SEED;

Session altered.

SQL> CREATE UNDO TABLESPACE test_undo datafile '/u01/app/oracle/oradata/orcl/pdb1/testundo.dbf' SIZE 10M AUTOEXTEND ON ;

Tablespace created.

SQL> alter system set UNDO_TABLESPACE='TEST_UNDO' scope=both;

System altered.

SQL> show parameter undo

NAME TYPE VALUE

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

temp_undo_enabled boolean FALSE

undo_management string AUTO

undo_retention integer 900

undo_tablespace string TEST_UNDO

SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

Session altered.

SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY FORCE;

Pluggable database altered.

<<Open the PDB is read write mode>>

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE;

Pluggable database altered.

SQL> show parameter undo

NAME TYPE VALUE

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

temp_undo_enabled boolean FALSE

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO';

CON_ID TABLESPACE_NAME

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

FILE_NAME

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

3 UNDO_1

/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf << Undo tablespace is created automatically.

1 UNDOTBS1

/u01/app/oracle/oradata/orcl/undotbs01.dbf

<< From the PDB>>

SQL> ALTER SESSION SET CONTAINER=PDB1;

Session altered.

SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO';

CON_ID TABLESPACE_NAME

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

FILE_NAME

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

3 UNDO_1 << Undo tablespace is created automatically.

/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf

Alert Log File entries (for PDB1)

2016-08-05T14:44:52.406849+05:30

ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE

2016-08-05T14:44:54.137062+05:30

PDB1(3):Endian type of dictionary set to little

PDB1(3):Autotune of undo retention is turned on.

PDB1(3):Undo initialization finished serial:0 start:2394185293 end:2394185366 diff:73 ms (0.1 seconds)

PDB1(3):Database Characterset for PDB1 is AL32UTF8

PDB1(3):Opatch validation is skipped for PDB PDB1 (con_id=0)

2016-08-05T14:44:55.457499+05:30

PDB1(3):Opening pdb with no Resource Manager plan active

PDB1(3):CREATE SMALLFILE UNDO TABLESPACE undo_1 DATAFILE '/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf' SIZE 10485760 AUTOEXTEND ON NEXT 8192 MAXSIZE 34359721984 ONLINE

2016-08-05T14:44:58.688075+05:30

PDB1(3):[18452] Successfully onlined Undo Tablespace 5.

PDB1(3):Completed: CREATE SMALLFILE UNDO TABLESPACE undo_1 DATAFILE '/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf' SIZE 10485760 AUTOEXTEND ON NEXT 8192 MAXSIZE 34359721984 ONLINE

Pluggable database PDB1 opened read write

Completed: ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE

jnrjian

关注

21
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
enable local undo 以及设置每个pdb 的undo 的步骤

undo tablespace 'UNDOTBS2' does not exist or of wrong type 要存在才行。* undo_tablespace string UNDO_2--错了。----需要node 1 alter system reset undo_tablespace;-----还是用反了 2用的UNDO_1。
复制链接

扫一扫