SQL> SELECT name, open_mode FROM v$pdbs ORDER BY name;
NAME OPEN_MODE
-------------------- ----------
PDB$SEED READ ONLY
PDB1 READ WRITE
SQL>
SQL>
SQL> show con_id
CON_ID
------------------------------
1
SQL>
SQL> select name from v$datafile where con_id=1;
NAME
-----------------------------------
/data2/CDBPROD/system01.dbf
/data2/CDBPROD/sysaux01.dbf
/data2/CDBPROD/undotbs01.dbf
/data2/CDBPROD/users01.dbf
SQL> select name from v$datafile where con_id=2;
NAME
-----------------------------------
/data2/CDBPROD/pdbseed/system01.dbf
/data2/CDBPROD/pdbseed/sysaux01.dbf
SQL> select name from v$datafile where con_id=3; - No UNDO
NAME
-----------------------------------
/data2/CDBPROD/pdb1/system01.dbf
/data2/CDBPROD/pdb1/sysaux01.dbf
/data2/CDBPROD/pdb1/users01.dbf
SQL> alter session set container=PDB$SEED;
Session altered.
SQL> show con_id
CON_ID
------------------------------
2
SQL> show con_name
CON_NAME
------------------------------
PDB$SEED
SQL>
SQL> select name from v$datafile;
NAME
-----------------------------------
/data2/CDBPROD/undotbs01.dbf
/data2/CDBPROD/pdbseed/system01.dbf
/data2/CDBPROD/pdbseed/sysaux01.dbf
SQL> select FILE_NAME, TABLESPACE_NAME from dba_temp_files;
FILE_NAME TABLESPACE_NAME
----------------------------------------------------- -------------------------
/data2/CDBPROD/pdbseed/temp012024-12-23_15-22-16-492-PM.dbf TEMP
SQL> SELECT tablespace_name, file_name
FROM dba_data_files
WHERE tablespace_name IN (SELECT value FROM v$parameter WHERE name = 'undo_tablespace');
no rows selected
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
----------------------------------- ----------
PDB$SEED READ ONLY
SQL> select * from database_properties where property_name like '%UNDO%';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
---------------------- ---------------------- ----------------------
LOCAL_UNDO_ENABLED FALSE true if local undo is enabled
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
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 property_value
FROM database_properties
WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE'; SQL> 2 3
PROPERTY_VALUE
--------------------
SYSTEM
SQL> column property_name format a30
column property_value format a30
select property_name, property_value
from database_properties
where property_name = 'LOCAL_UNDO_ENABLED';
SQL> SQL> SQL> 2 3
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED FALSE
SQL> select con_id, tablespace_name
from cdb_tablespaces
where contents = 'UNDO'
order by con_id; 2 3 4
no rows selected
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL>
SQL>
SQL>
SQL> show con_id
CON_ID
------------------------------
1
SQL>
SQL>
SQL> select con_id, tablespace_name
from cdb_tablespaces
where contents = 'UNDO'
order by con_id; 2 3 4
CON_ID TABLESPACE_NAME
---------- ------------------------------
1 UNDOTBS1
SQL> column property_name format a30
column property_value format a30
select property_name, property_value
from database_properties
where property_name = 'LOCAL_UNDO_ENABLED';
SQL> SQL> SQL> 2 3
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED FALSE
SQL>
SQL>
SQL> select con_id, tablespace_name
from cdb_tablespaces
where contents = 'UNDO'
order by con_id; 2 3 4
CON_ID TABLESPACE_NAME
---------- ------------------------------
1 UNDOTBS1
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL>
SQL> alter session set container = pdb1;
Session altered.
SQL>
SQL> select file_name
from dba_data_files
where tablespace_name = 'UNDOTBS1'; 2 3
no rows selected
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------
/data2/CDBPROD/pdb1/system01.dbf
/data2/CDBPROD/pdb1/sysaux01.dbf
/data2/CDBPROD/pdb1/users01.dbf
SQL> select con_id, tablespace_name
from cdb_tablespaces
where contents = 'UNDO'
order by con_id; 2 3 4
no rows selected
SQL> alter session set container = cdb$root;
Session altered.
SQL>
SQL> select con_id, tablespace_name
from cdb_tablespaces
where contents = 'UNDO'
order by con_id; 2 3 4
CON_ID TABLESPACE_NAME
---------- ------------------------------
1 UNDOTBS1
SQL>
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 2432695808 bytes
Fixed Size 9166336 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7610368 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL>
SQL> alter database local undo on;
Database altered.
SQL>
SQL> column property_name format a30
column property_value format a30
select property_name, property_value
from database_properties
where property_name = 'LOCAL_UNDO_ENABLED';SQL> SQL> SQL> 2 3
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED TRUE
SQL>
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup;
ORACLE instance started.
Total System Global Area 2432695808 bytes
Fixed Size 9166336 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7610368 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> column property_name format a30
column property_value format a30
select property_name, property_value
from database_properties
where property_name = 'LOCAL_UNDO_ENABLED';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED TRUE
SQL>
SQL>
SQL> select con_id, tablespace_name from cdb_tablespaces
where tablespace_name LIKE 'UNDO%' order by con_id;
CON_ID TABLESPACE_NAME
---------- ------------------------------
1 UNDOTBS1
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL>
SQL> alter session set container=PDB1;
Session altered.
SQL> alter database open;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL>
SQL> alter session set container=cdb$root;
Session altered.
SQL>
SQL> select con_id, tablespace_name
from cdb_tablespaces
where contents = 'UNDO'
order by con_id;
2 3 4
CON_ID TABLESPACE_NAME
---------- ------------------------------
1 UNDOTBS1
3 UNDO_1
SQL> alter session set container = pdb1;
Session altered.
SQL> select file_name
from dba_data_files
where tablespace_name = 'UNDOTBS1'; 2 3
no rows selected
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------
/data2/CDBPROD/pdb1/system01.dbf
/data2/CDBPROD/pdb1/sysaux01.dbf
/data2/CDBPROD/pdb1/users01.dbf
/data2/CDBPROD/pdb1/system01_i1_undo.dbf
No comments:
Post a Comment