Shulou(Shulou.com)06/01 Report--

This paper mainly talks about:

1. Views on undo tablespaces share and local

two。 The method of changing undo to local undo

3. The method of modifying PDB$SEED property

4. About the conversion from local undo to share undo

I. Overview

Starting with version 12.2, the database implements isolation of undo tablespaces at the cdb and pdb levels; and local undo is also the basis for some new features, such as pdb refresh and pdb flashbacks. Version 12.1 cannot be implemented yet. However, this isolation of resources is an inevitable direction of development. After undo achieves isolation, the next step of redo isolation is also possible.

When cdb is the local undo schema, both cdb and pdb of open have their own undo tablespaces. When you plug a pdb from a share undo cdb into a local undo schema cdb, an undo tablespace is automatically created for the pdb at that pdb open.

II. The method of changing undo to local undo

Database version

SYS@cdbtest1 (CDB$ROOT) > select banner from v$version where rownum=1

BANNER

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production

Query current database undo information

SYS@cdbtest1 (CDB$ROOT) > col PROPERTY_NAME for A25

SYS@cdbtest1 (CDB$ROOT) > col PROPERTY_VALUE for A30

SYS@cdbtest1 (CDB$ROOT) > select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name='LOCAL_UNDO_ENABLED'

PROPERTY_NAME PROPERTY_VALUE

LOCAL_UNDO_ENABLED FALSE

SYS@cdbtest1 (CDB$ROOT) > set line 200

SYS@cdbtest1 (CDB$ROOT) > col FILE_NAME for A50

SYS@cdbtest1 (CDB$ROOT) > select a.conformidrect a.tablespaceplayname from cdb_tablespaces b.filewriting name dwelling files b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS recording records UNDO'

CON_ID TABLESPACE_NAME FILE_NAME

-

1 UNDOTBS1 / oradata/cdb/CDBTEST1/datafile/o1_mf_undotbs1_f7nz

8sl3_.dbf

You can see that the current undo is still shared, and there is only one undo tablespace in cdb.

Rough steps

1. Close the cdb instance

two。 Turn on pdb to upgrade mode, startup upgrade

3. Execute in CDB$ROOT: ALTER DATABASE LOCAL UNDO ON

4. Shut down the cdb instance and restart it

The following is the procedure:

SYS@cdbtest1 (CDB$ROOT) > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@cdbtest1 (CDB$ROOT) > startup upgrade

ORACLE instance started.

Total System Global Area 2214592512 bytes

Fixed Size 8795040 bytes

Variable Size 1845496928 bytes

Database Buffers 352321536 bytes

Redo Buffers 7979008 bytes

Database mounted.

Database opened.

SYS@cdbtest1 (CDB$ROOT) > sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED MIGRATE YES

3 CLONEMING MOUNTED

4 MING MOUNTED

SYS@cdbtest1 (CDB$ROOT) > alter database local undo on

Database altered.

SYS@cdbtest1 (CDB$ROOT) > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@cdbtest1 (CDB$ROOT) > startup

ORACLE instance started.

Total System Global Area 2214592512 bytes

Fixed Size 8795040 bytes

Variable Size 1845496928 bytes

Database Buffers 352321536 bytes

Redo Buffers 7979008 bytes

Database mounted.

Database opened.

SYS@cdbtest1 (CDB$ROOT) > col PROPERTY_NAME for A25

SYS@cdbtest1 (CDB$ROOT) > col PROPERTY_VALUE for A30

SYS@cdbtest1 (CDB$ROOT) > select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name='LOCAL_UNDO_ENABLED'

PROPERTY_NAME PROPERTY_VALUE

LOCAL_UNDO_ENABLED TRUE

SYS@cdbtest1 (CDB$ROOT) > select a.conformidrect a.tablespacecharnamerect b.fileroomnamememe b.bytesqq1024company1024M from cdb_tablespaces a cores where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTScriptFILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS encoding UNDO'

CON_ID TABLESPACE_NAME FILE_NAME M

1 UNDOTBS1 / oradata/cdb/CDBTEST1/datafile/o1_mf_undotbs1_f7nz 835

8sl3_.dbf

3 UNDO_1 / oradata/cdb/CDBTEST1/64B070B959B22CC9E053023DA8C0 300

935F/datafile/o1_mf_undo_1_fxv9cmnl_.dbf

4 UNDO_1 / oradata/cdb/CDBTEST1/64AE1A20BB9C1B5FE053023DA8C0 300

6963/datafile/o1_mf_undo_1_fxv9cvrd_.dbf

3. The method of modifying PDB$SEED attribute

As mentioned at the beginning, when you insert a pdb from a share undo cdb into a local undo schema cdb, an undo tablespace is automatically created for that pdb during that pdb open. There are two pdb that are automatically turned on, so the undo tablespace is automatically created for these two pdb. The size of 300m follows the size of the pdb in PDB$SEED. If you want to control the configuration of the undo table space created, such as the size, you can modify the undo table space in pdb$seed.

The methods are as follows:

1. Execute in cdb$root:

Alter session set "_ oracle_script" = true;-- modify the implicit parameters to avoid ORA-65017: seed pluggable database may not be dropped or altered

ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE FORCE

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED READ WRITE NO

two。 Switch to seed pdb

ALTER SESSION SET CONTAINER=PDB$SEED

3. Modify undo tablespace

Alter database datafile'/ oradata/cdb/CDBTEST1/6498843CCFAD2D1FE053023DA8C0AC32/datafile/o1_mf_undotbs1_f7nz8x0z_.dbf' resize 300m

It used to be 210m, which was modified to 300m.

4. Then change the PDB$SEED back to read-only mode

SYS@cdbtest1 (CDB$ROOT) > alter session set "_ oracle_script" = true

Session altered.

SYS@cdbtest1 (CDB$ROOT) > alter pluggable database "pdb$seed" close immediate

Pluggable database altered.

SYS@cdbtest1 (CDB$ROOT) > sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED MOUNTED

3 CLONEMING READ WRITE NO

4 MING READ WRITE NO

SYS@cdbtest1 (CDB$ROOT) > alter pluggable database "pdb$seed" open read only

Pluggable database altered.

SYS@cdbtest1 (CDB$ROOT) > sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED READ ONLY NO

3 CLONEMING READ WRITE NO

4 MING READ WRITE NO

IV. On the conversion from local undo to share undo

This is how oracle turns a shared undo into a local undo. The conversion of local undo to shared undo is not in line with the trend of development, the method is more or less the same, but the previous command is changed to alter database local undo off, the detailed process will not be introduced. Note, however, that when oracle becomes a shared undo schema, it ignores the local udno tablespaces created in the previous local schema, and oracle recommends that you delete these local undo tablespace directly.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.