How to recovery PDB when PDB database is dropped in Oracle

 

How to recovery PDB when PDB database is dropped :)

[oracle@rac01 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Jan 11 12:20:24 2025
Version 21.13.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL>
SQL>
SQL> alter pluggable database PDB1 close;

Pluggable database altered.

SQL>
SQL>
SQL> drop pluggable database PDB1 including datafiles;

Pluggable database dropped.




SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDB2                           READ WRITE NO

============Alert log file contents======

2025-01-11T12:20:48.610884+01:00
alter pluggable database PDB1 close
2025-01-11T12:20:48.621225+01:00
PDB1(3):Pluggable database PDB1 closing
PDB1(3):JIT: pid 92739 requesting stop
PDB1(3):Closing sequence subsystem (204729984896).
PDB1(3):Buffer Cache flush started: 3
PDB1(3):Buffer Cache flush finished: 3
Pluggable database PDB1 closed
Completed: alter pluggable database PDB1 close
2025-01-11T12:21:10.490974+01:00
drop pluggable database PDB1 including datafiles
2025-01-11T12:21:10.596417+01:00
Deleted file +DATA/CDBAM/PDB1/undo_seed01.dbf
Deleted file +DATA/CDBAM/PDB1/users01.dbf
Deleted file +DATA/CDBAM/PDB1/temp01.dbf
Deleted file +DATA/CDBAM/PDB1/sysaux01.dbf
Deleted file +DATA/CDBAM/PDB1/system01.dbf
2025-01-11T12:21:10.640983+01:00
Stopped service pdb1
Completed: drop pluggable database PDB1 including datafiles

======Execute the below script to recover PDB1 ======

Note: Never use RESTORE command as per Oracle support suggestion

run
{
set newname for datafile 20 to '+DATA/CDBAM/PDB1/system01.dbf';
set newname for datafile 21 to '+DATA/CDBAM/PDB1/sysaux01.dbf';
set newname for datafile 22 to '+DATA/CDBAM/PDB1/users01.dbf';
set newname for datafile 23 to '+DATA/CDBAM/PDB1/undo_seed01.dbf';
recover pluggable database pdb1 until time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')" 
auxiliary destination '/applications/oracle/d19.3.0/CDBAM_BKP/AUX/';
}

log for recovery :-

[oracle@rac01 AUX]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jan 11 14:07:44 2025
Version 19.23.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDBAM (DBID=1604254345)

RMAN>

RMAN> run
{
set newname for datafile 20 to '+DATA/CDBAM/PDB1/system01.dbf';
set newname for datafile 21 to '+DATA/CDBAM/PDB1/sysaux01.dbf';
set newname for datafile 22 to '+DATA/CDBAM/PDB1/users01.dbf';
set newname for datafile 23 to '+DATA/CDBAM/PDB1/undo_seed01.dbf';
2> recover pluggable database pdb1 until time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '/applications/oracle/d19.3.0/CDBAM_BKP/AUX/';
}3> 4> 5> 6> 7> 8>

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting recover at 11-JAN-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='aiAi'

initialization parameters used for automatic instance:
db_name=CDBAM
db_unique_name=aiAi_pitr_pdb1_CDBAM
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/applications/oracle/d19.3.0/oracle_base
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
sga_target=6144M
processes=200
db_create_file_dest=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/
log_archive_dest_1='location=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used


starting up automatic instance CDBAM

Oracle instance started

Total System Global Area    6442447480 bytes

Fixed Size                     9192056 bytes
Variable Size               1124073472 bytes
Database Buffers            5301600256 bytes
Redo Buffers                   7581696 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';
}
executing Memory Script

executing command: SET until clause

Starting restore at 11-JAN-25
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=211 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /applications/oracle/d19.3.0/CDBAM_BKP/c-1604254345-20250111-01
channel ORA_AUX_DISK_1: piece handle=/applications/oracle/d19.3.0/CDBAM_BKP/c-1604254345-20250111-01 tag=TAG20250111T121656
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/controlfile/o1_mf_mr4v9ltv_.ctl
Finished restore at 11-JAN-25

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  5 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  6 to new;
set newname for datafile  20 to new;
set newname for datafile  21 to new;
set newname for datafile  22 to new;
set newname for datafile  23 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 5, 3, 6, 20, 21, 22, 23;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 11-JAN-25
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /applications/oracle/d19.3.0/CDBAM_BKP/0i3evgde_18_1_1
channel ORA_AUX_DISK_1: piece handle=/applications/oracle/d19.3.0/CDBAM_BKP/0i3evgde_18_1_1 tag=TAG20250111T121646
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00020 to +DATA/CDBAM/PDB1/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00021 to +DATA/CDBAM/PDB1/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00022 to +DATA/CDBAM/PDB1/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00023 to +DATA/CDBAM/PDB1/undo_seed01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /applications/oracle/d19.3.0/CDBAM_BKP/0k3evgdm_20_1_1
channel ORA_AUX_DISK_1: piece handle=/applications/oracle/d19.3.0/CDBAM_BKP/0k3evgdm_20_1_1 tag=TAG20250111T121646
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 11-JAN-25

datafile 1 switched to datafile copy
input datafile copy RECID=29 STAMP=1190124510 file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_system_mr4v9vv6_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=30 STAMP=1190124510 file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_undotbs1_mr4v9vvc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=31 STAMP=1190124510 file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_sysaux_mr4v9vv9_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=32 STAMP=1190124510 file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_users_mr4v9vvg_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  5 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  6 online";
sql clone 'PDB1' "alter database datafile
 20 online";
sql clone 'PDB1' "alter database datafile
 21 online";
sql clone 'PDB1' "alter database datafile
 22 online";
sql clone 'PDB1' "alter database datafile
 23 online";
#recover pdb
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS" pluggable database
 'PDB1'  delete archivelog;
#open in read write mode
sql clone 'alter database open resetlogs';
#unplug dropped pdb into temp file
sql clone "alter pluggable database PDB1 unplug into ''
/applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml''";
#create pdb using temp file of recovered pdb
sql "create pluggable database PDB1 using ''
/applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml'' nocopy tempfile reuse";
alter pluggable database PDB1 open;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  5 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  6 online

sql statement: alter database datafile  20 online

sql statement: alter database datafile  21 online

sql statement: alter database datafile  22 online

sql statement: alter database datafile  23 online

Starting recover at 11-JAN-25
using channel ORA_AUX_DISK_1

Executing: alter database datafile 2, 4, 7, 15 offline
Executing: alter database datafile 16, 17, 18, 19 offline
starting media recovery

archived log for thread 1 with sequence 269 is already on disk as file +ARCH/cdbam/arch/1_269_1189847369.dbf
archived log for thread 1 with sequence 270 is already on disk as file +ARCH/cdbam/arch/1_270_1189847369.dbf
archived log file name=+ARCH/cdbam/arch/1_269_1189847369.dbf thread=1 sequence=269
archived log file name=+ARCH/cdbam/arch/1_270_1189847369.dbf thread=1 sequence=270
media recovery complete, elapsed time: 00:00:01
Finished recover at 11-JAN-25

sql statement: alter database open resetlogs

sql statement: alter pluggable database PDB1 unplug into ''/applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml''

sql statement: create pluggable database PDB1 using ''/applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml'' nocopy tempfile reuse

Statement processed

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml deleted
auxiliary instance file /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_sysaux_mr4v9vv9_.dbf deleted
auxiliary instance file /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/controlfile/o1_mf_mr4v9ltv_.ctl deleted
Finished recover at 11-JAN-25

RMAN>

PDB1 recovered successfully.

[oracle@rac01 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Jan 11 13:00:24 2025
Version 21.13.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0


SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
	 3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO

ORA-11183: ASMLIB initialization error [driver/agent not installed] in oracle database in Linux server

 

ORA-11183: ASMLIB initialization error [driver/agent not installed]

After reinstalling grid infrastructure and oracle software  or applying patch (PSU) in linux server. 

You may face below error (and in alert log ORA-11183 error) when you will try to startup the oracle database.

Only three command give you the solution.  

Solution is below.

SQL> host rman target /

Recovery Manager: Release 19.23.0.0.0 - Production on Wed Jan 11 11:05:21 2025

Copyright (c) 1982, 2025, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup     

Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 01/11/2025 11:05:41
ORA-03113: end-of-file on communication channel
Process ID: 16205
Session ID: 199 Serial number: 44057

RMAN> shutdown immediate

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of shutdown command at 01/16/2025 01:23:44
RMAN-12001: could not open channel default
RMAN-01008: could not create channel context
RMAN-01005: error opening cursor
RMAN-01002: ORACLE error: ORA-03114: not connected to ORACLE

RMAN> exit

Alert log content:

**************************

Errors in file /applications/oracle/d19.3.0/oracle_base/diag/rdbms/pocdb/POCDB02/trace/POCDB02_rbal_602138.trc:
ORA-15183: ASMLIB initialization error [driver/agent not installed]
2025-01-11T09:03:10.764438+01:00
WARNING: FAILED to load library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so
2025-01-11T09:03:10.978131+01:00
NOTE: ASMB (index:0) (602142) connected to ASM instance +ASM2, osid: 602152 (Flex mode; client id 0xea3b5ea7aa6dc66d)
NOTE: initiating MARK startup
Starting background process MARK

NOTE: ASMB registering with ASM instance as Standard client 0xffffffffffffffff (reg:4060941312) (new connection)
Wed Jan 11 11:05:37 2025
Errors in file /userdata/app/oracle/diag/rdbms/icmsdb1/ICMSDB/trace/ICMSDB_rbal_16178.trc:
ORA-11183: ASMLIB initialization error [driver/agent not installed]
Wed Jan 11 11:05:37 2025
WARNING: FAILED to load library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so
Wed Jan 11 11:05:37 2025
starting up 1 shared server(s) ...
Wed Jan 11 11:05:37 2025
NOTE: ASMB connected to ASM instance +ASM osid: 16186 (Flex mode; client id 0xffffffffffffffff)
NOTE: initiating MARK startup
Starting background process MARK
Wed Jan 11 11:05:37 2025
MARK started with pid=27, OS id=16192
Wed Jan 11 11:05:37 2025
NOTE: MARK has subscribed
ORACLE_BASE from environment = /userdata/app/oracle
Wed Jan 11 11:05:39 2025
alter database mount
Wed Jan 11 11:05:40 2025
NOTE: ASMB mounting group 1 (DATA)
Wed Jan 11 11:05:40 2025
WARNING: cellinit.ora is missing. RBAL is terminating the instance.
RBAL (ospid: 16178): terminating the instance due to error 27625
Wed Jan 11 11:05:41 2025
System state dump requested by (instance=1, osid=16178 (RBAL)), summary=[abnormal instance termination].
System State dumped to trace file /userdata/app/oracle/diag/rdbms/icmsdb1/ICMSDB/trace/ICMSDB_diag_16112_20250111101541.trc
Wed Jan 11 11:05:41 2025
Dumping diagnostic data in directory=[cdmp_20250111101541], requested by (instance=1, osid=16178 (RBAL)), summary=[abnormal instance termination].
Wed Jan 11 11:05:41 2025
Instance terminated by RBAL, pid = 16178

Cause:

This is an Ownership issue.

After reinstalling grid and oracle software, or applying Patch, it changed the group (ASMADMIN) to (OINSTALL) for ORACLE user and   

$ORACLE_HOME/bin folder. 

So now you have to change the group back again to ASMADMIN and give appropriate permission also.

Before :- 
With root user :-

[oracle@rac01]$ su - root
Password:
[root@rac01 ~]#
[root@rac01 ~]#
[root@rac01 ~]# cd $ORACLE_HOME/bin
[root@rac01 bin]#  ls -lrt oracle
-rwsr-s--x 1 oracle oinstall 462314040 Jan 10 10:45 oracle

After :- Change the ownership and permission 

[root@rac01 bin]# chgrp asmadmin oracle
[root@rac01 bin]#
[root@rac01 bin]# chmod 6751 oracle
[root@rac01 bin]#
[root@rac01 bin]#

[root@rac01 bin]# ls -lrt oracle
-rwxr-x--x 1 oracle asmadmin 462314040 Jan 10 10:45 oracle

After that Database will be startup ..

SQL>
SQL>
SQL>
SQL> host rman target /

Recovery Manager: Release 19.23.0.0.0 - Production on Thu Jan 16 01:24:01 2025

Copyright (c) 1982, 2025, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount ;

Oracle instance started
database mounted

Total System Global Area    4101417920 bytes

Fixed Size                     2932096 bytes
Variable Size               2197811936 bytes
Database Buffers            1895825408 bytes
Redo Buffers                  13844480 bytes

RMAN>

Local Vs Global Index in Oracle

 

The advantage of the indexes is the Oracle query engine will scan only 

the required index partition to serve the query, thus speeding up the query significantly.

While creating the index on the Oracle table partitions we can use these keyword ‘LOCAL’. 

Local indexes are partitioned indexes which are easy to manage and each index partition is associated with its respective table partitions.

The major advantage of local indexes is there will not be any index invalidation when you perform alter table exchange, drop, add partitions.

SQL> CREATE TABLE emp (
    emp_id       NUMBER PRIMARY KEY,
    emp_name     VARCHAR2(100),
    hire_date    DATE,
    salary       NUMBER
)
PARTITION BY RANGE (hire_date)
(
    PARTITION p_2010s VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),  -- Partition for employees hired before 2020
    PARTITION p_2020s VALUES LESS THAN (TO_DATE('01-JAN-2030', 'DD-MON-YYYY')),  -- Partition for employees hired before 2030
    PARTITION p_2030s VALUES LESS THAN (TO_DATE('01-JAN-2040', 'DD-MON-YYYY')),  -- Partition for employees hired before 2040
    PARTITION p_2040s VALUES LESS THAN (TO_DATE('01-JAN-2050', 'DD-MON-YYYY')),  -- Partition for employees hired before 2050
    PARTITION p_2050s VALUES LESS THAN (TO_DATE('01-JAN-2060', 'DD-MON-YYYY'))   -- Partition for employees hired before 2060
); 

SQL> select * from emp;


    EMP_ID EMP_NAME                       HIRE_DATE     SALARY
---------- ---------------------------------------- ----------
         1 John Doe                       15-MAY-15      50000
         2 Jane Smith                     25-NOV-22      60000
         3 David Brown                    05-FEB-31      65000
         4 Sara Wilson                    10-AUG-43      70000
         5 Michael Green                  20-JAN-55      75000

Check the Index:-

SQL> select index_name from dba_indexes where owner='SAM';

INDEX_NAME
--------------------------
SYS_C007884

Let us create index:- (LOCAL index with out Index partitioning) 

SQL> create index EMP_NAME_IDX on emp(EMP_NAME);-------------------No Key word like "LOCAL" used ..

Index created.

SQL> select index_name from dba_indexes where owner='SAM';

INDEX_NAME
---------------------------
SYS_C007884
EMP_NAME_IDX


SQL> select INDEX_NAME,INDEX_type ,PARTITIONED,GLOBAL_STATS from user_indexes where INDEX_NAME='EMP_NAME_IDX';
INDEX_NAME           INDEX_TYPE                  PAR GLO
-------------------- --------------------------- --- ---
EMP_NAME_IDX         NORMAL                      NO  YES




Note:- EMP_NAME_IDX is not the part of Local index and it is Normal Index  we create it.

I just crosschecked the user part indexes and "no rows selected"

SQL> select locality from user_part_indexes where index_name='EMP_NAME_IDX'; -- this is normal Index ...

no rows selected

Let's create index with LOCAL - key word on SALARY columns

EMP_ID EMP_NAME                       HIRE_DATE     SALARY
---------- ---------------------------------------- ----------
         1 John Doe                       15-MAY-15      50000
         2 Jane Smith                     25-NOV-22      60000
         3 David Brown                    05-FEB-31      65000
         4 Sara Wilson                    10-AUG-43      70000
         5 Michael Green                  20-JAN-55      75000

Part 1 :- LOCAL index without Partition 

SQL> create index salary_idx on emp(salary) LOCAL;--------


SQL> select index_name from dba_indexes where owner='SAM';

INDEX_NAME
--------------------
SYS_C007884
EMP_NAME_IDX
SALARY_IDX


col LOCALITY for a20
col TABLE_NAME for a10
SQL> select INDEX_NAME,TABLE_NAME, LOCALITY from user_part_indexes;

INDEX_NAME           TABLE_NAME LOCALITY
-------------------- ---------- --------------------
SALARY_IDX           EMP        LOCAL

SQL> select INDEX_NAME,INDEX_type ,PARTITIONED,GLOBAL_STATS from user_indexes where INDEX_NAME='SALARY_IDX';

INDEX_NAME           INDEX_TYPE                  PAR GLO
-------------------- --------------------------- --- ---
SALARY_IDX           NORMAL                      YES NO

SQL> select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='SALARY_IDX';

INDEX_NAME           PARTITION_NAME  STATUS
-------------------- --------------- --------
SALARY_IDX           P_2010S         USABLE
SALARY_IDX           P_2020S         USABLE
SALARY_IDX           P_2030S         USABLE
SALARY_IDX           P_2040S         USABLE
SALARY_IDX           P_2050S         USABLE

Note :- 

1) When you create a index on a partitioned table using "LOCAL" clause, it automatically creates index as many as in the table partitions. 

2) Similarly when we add a new partition to the table, Oracle automatically adds one index partition to the index as well.

Note:- dropping partition from the table and check the status of Partition index

SQL> ALTER TABLE EMP drop partition P_2050S;

Table altered.

SQL> select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='SALARY_IDX';

INDEX_NAME           PARTITION_NAME  STATUS
-------------------- --------------- --------
SALARY_IDX           P_2010S         USABLE
SALARY_IDX           P_2020S         USABLE
SALARY_IDX           P_2030S         USABLE
SALARY_IDX           P_2040S         USABLE

------------This is pure Index without (Index-Partation) 

SQL> alter index SALARY_IDX drop partition P_2040S;
alter index SALARY_IDX drop partition P_2040S
            *
ERROR at line 1:
ORA-14076: submitted alter index partition/subpartition operation is not valid for local partitioned index


SQL> alter index SALARY_IDX add partition P_2050S;

SQL> alter index SALARY_IDX add partition P_2050S;
alter index SALARY_IDX add partition P_2050S
            *
ERROR at line 1:
ORA-14076: submitted alter index partition/subpartition operation is not valid for local partitioned index


SQL>
SQL> drop index SALARY_IDX;

Index dropped.

SQL> select INDEX_NAME,INDEX_type ,PARTITIONED,GLOBAL_STATS from user_indexes where INDEX_NAME='SALARY_IDX';

no rows selected

SQL>

SQL> select INDEX_NAME,INDEX_type ,PARTITIONED,GLOBAL_STATS from user_indexes
  2  /

INDEX_NAME           INDEX_TYPE                  PAR GLO
-------------------- --------------------------- --- ---
SYS_C007884          NORMAL                      NO  NO
EMP_NAME_IDX         NORMAL                      NO  YES
IDX_EMP_SALARY       NORMAL                      YES NO

Part -2 --Local Index with partition

*****************************************

SQL>
SQL> CREATE INDEX idx_emp_salary
ON emp (salary)
LOCAL
(PARTITION p_2010s TABLESPACE users,
 PARTITION p_2020s TABLESPACE users,
 PARTITION p_2030s TABLESPACE users,
 PARTITION p_2040s TABLESPACE users,
 PARTITION p_2050s TABLESPACE users);  2    3    4    5    6    7    8
ON emp (salary)
   *
ERROR at line 2:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table

Create Index with it's own partitions but on Local non-prefixed indexes 

SQL> select TABLE_OWNER,TABLE_NAME,PARTITION_NAME from dba_tab_partitions where TABLE_OWNER='SAM' and TABLE_NAME='EMP';
TABLE_OWNE TABLE_NAME PARTITION_NAME
---------- ---------- ---------------
SAM        EMP        P_2010S
SAM        EMP        P_2020S
SAM        EMP        P_2030S
SAM        EMP        P_2040S

Note:- A local index is one on one mapping between an index partation and table partation.
You can create index for particular partition only not on whole table.


SQL> CREATE INDEX idx_emp_salary
ON emp (salary)
LOCAL
(
    PARTITION p_2010s TABLESPACE users,
    PARTITION p_2020s TABLESPACE users,
    PARTITION p_2030s TABLESPACE users,
    PARTITION p_2040s TABLESPACE users
);  2    3    4    5    6    7    8    9

Index created.


SQL> select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='IDX_EMP_SALARY';

INDEX_NAME           PARTITION_NAME  STATUS
-------------------- --------------- --------
IDX_EMP_SALARY       P_2010S         USABLE
IDX_EMP_SALARY       P_2020S         USABLE
IDX_EMP_SALARY       P_2030S         USABLE
IDX_EMP_SALARY       P_2040S         USABLE

SQL> select INDEX_NAME,TABLE_NAME, LOCALITY from user_part_indexes;

INDEX_NAME           TABLE_NAME LOCALITY
-------------------- ---------- --------------------
IDX_EMP_SALARY       EMP        LOCAL

Let's try to drop partation from the IDX_EMP_SALARY index:-
---------------------------------------------------------------
alter index IDX_EMP_SALARY drop partition P_2040S;


You can't drop partation from the Index:-
--------------------------------------------
SQL> alter index IDX_EMP_SALARY drop partition P_2040S;
alter index IDX_EMP_SALARY drop partition P_2040S
            *
ERROR at line 1:
ORA-14076: submitted alter index partition/subpartition operation is not valid for local partitioned index

Now let's drop a PARTITION from the Table and check the impact of Index partition

SQL> alter table emp drop PARTITION P_2040S;

Table altered.

SQL> select TABLE_OWNER,TABLE_NAME,PARTITION_NAME from dba_tab_partitions where TABLE_OWNER='SAM' and TABLE_NAME='EMP';

TABLE_OWNE TABLE_NAME PARTITION_NAME
---------- ---------- ---------------
SAM        EMP        P_2010S
SAM        EMP        P_2020S
SAM        EMP        P_2030S

SQL>
SQL> select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='IDX_EMP_SALARY';

INDEX_NAME           PARTITION_NAME  STATUS
-------------------- --------------- --------
IDX_EMP_SALARY       P_2010S         USABLE
IDX_EMP_SALARY       P_2020S         USABLE
IDX_EMP_SALARY       P_2030S         USABLE

Note: When you dropped partition from the table, it's index partition also dropped automatically.

Prefixed vs. Non-Prefixed:

A local prefixed index means the indexed column(s) include the partitioning column (hire_date in this case) as part of the indexed columns.

A local non-prefixed index means the indexed column(s) do not include the partitioning column.

Local Prefixed Index:-

CREATE INDEX idx_emp_prefixed ON emp (hire_date, salary) LOCAL;

Local Non-Prefixed Index:-

CREATE INDEX idx_emp_nonprefixed ON emp (salary) LOCAL;

Global Index:-

Global indexes can be range partitioned using a different partition scheme from the underlying table. 

i.e. on a table (containing two columns id and start_date) that is hash partitioned by id, the global index on start_date can be range partitioned.

There are two types of global indexes.They are,

1. Global non-paritioned indexes (similar to the normal non-partitioned indexes)

2. Global partitioned indexes.

Now employees table creation and let's create Index 

department_id column is partitioned

SQL> CREATE TABLE employees
(employee_id NUMBER(4) NOT NULL,
last_name VARCHAR2(10),
  department_id NUMBER(2))
      PARTITION BY RANGE (department_id)
      (PARTITION employees_part1 VALUES LESS THAN (10) TABLESPACE USERS,
      PARTITION employees_part2 VALUES LESS THAN (20) TABLESPACE USERS,
      PARTITION employees_part3 VALUES LESS THAN (30) TABLESPACE USERS);  

Table created.

SQL>
SQL>
SQL> select partition_name, num_rows from user_tab_partitions  where table_name = 'EMPLOYEES' order by partition_position;

PARTITION_NAME    NUM_ROWS
--------------- ----------
EMPLOYEES_PART1
EMPLOYEES_PART2
EMPLOYEES_PART3

SQL>
SQL> declare
 v_no number :=1;
 begin
 delete employees;
 for i in 1..10 loop
 insert into employees values(v_no,'name...',v_no);
 v_no := v_no+1;
 end loop;
end;
/    

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats('','EMPLOYEES',cascade=>true);

PL/SQL procedure successfully completed.

SQL>
SQL> select partition_name, num_rows from user_tab_partitions  where table_name = 'EMPLOYEES' order by partition_position;

PARTITION_NAME    NUM_ROWS
--------------- ----------
EMPLOYEES_PART1          9
EMPLOYEES_PART2          1
EMPLOYEES_PART3          0

Let's create Index on employee_id column since column is not partition.

It means that for employees table and it's column employee_id , 

CREATE INDEX employees_global_idx ON employees(employee_id) GLOBAL;



col owner for a10
col TABLE_NAME for a15
col PARTITIONED for a10
SQL> select OWNER, INDEX_NAME, TABLE_NAME,STATUS, PARTITIONED from dba_indexes where owner='SAM' and INDEX_NAME='EMPLOYEES_GLOBAL_IDX';


OWNER      INDEX_NAME           TABLE_NAME      STATUS   PARTITIONE
---------- -------------------- --------------- -------- ----------
SAM        EMPLOYEES_GLOBAL_IDX EMPLOYEES       VALID    NO


Let's check Global index with Index partation

select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_IDX';

SQL> select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_IDX';

no rows selected

No rows selected means,its a Global Non-partitioned index.

SQL> select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='EMPLOYEES_GLOBAL_IDX';

INDEX_NAME           INDEX_TYPE                  GLO
-------------------- --------------------------- ---
EMPLOYEES_GLOBAL_IDX NORMAL                      YES

Note:- Now dropping the partation and will check the impact on (EMPLOYEES_GLOBAL_IDX) index

-----

SQL> alter table EMPLOYEES drop PARTITION EMPLOYEES_PART3;

Table altered.

SQL> select partition_name, num_rows from user_tab_partitions  where table_name = 'EMPLOYEES' order by partition_position;

PARTITION_NAME    NUM_ROWS
--------------- ----------
EMPLOYEES_PART1          9
EMPLOYEES_PART2          1


SQL> select OWNER, INDEX_NAME, TABLE_NAME,STATUS, PARTITIONED from dba_indexes where owner='SAM' and INDEX_NAME='EMPLOYEES_GLOBAL_IDX';

OWNER      INDEX_NAME           TABLE_NAME      STATUS   PARTITIONE
---------- -------------------- --------------- -------- ----------
SAM        EMPLOYEES_GLOBAL_IDX EMPLOYEES       VALID    NO


SQL> select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='EMPLOYEES_GLOBAL_IDX';

INDEX_NAME           INDEX_TYPE                  GLO
-------------------- --------------------------- ---
EMPLOYEES_GLOBAL_IDX NORMAL                      YES

Note:- No impact on the Global Index

Now:-

Lets drop this index and create a partioned global index

SQL> drop index EMPLOYEES_GLOBAL_IDX;

Index dropped.

Here is an example for a range-based globally partitioned index,

SQL> CREATE INDEX employees_global_part_idx ON employees(employee_id)
 GLOBAL PARTITION BY RANGE(employee_id)
(PARTITION p1 VALUES LESS THAN(3),
PARTITION p2 VALUES LESS THAN(6),
PARTITION p3 VALUES LESS THAN(11),
PARTITION p4 VALUES LESS THAN(20),
PARTITION p5 VALUES LESS THAN(MAXVALUE));


SQL> CREATE INDEX employees_global_part_idx ON employees(employee_id)
 GLOBAL PARTITION BY RANGE(employee_id)
  2    3  (PARTITION p1 VALUES LESS THAN(3),
  4  PARTITION p2 VALUES LESS THAN(6),
PARTITION p3 VALUES LESS THAN(11),
PARTITION p4 VALUES LESS THAN(20),
PARTITION p5 VALUES LESS THAN(MAXVALUE));
  5    6    7
Index created.

SQL> select partition_name, num_rows from user_tab_partitions  where table_name = 'EMPLOYEES' order by partition_position;

PARTITION_NAME    NUM_ROWS
--------------- ----------
EMPLOYEES_PART1          9
EMPLOYEES_PART2          1
EMPLOYEES_PART3
EMPLOYEES_PART4


SQL>
SQL> select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                INDEX_TYPE                  GLO
------------------------- --------------------------- ---
EMPLOYEES_GLOBAL_PART_IDX NORMAL                      NO

SQL>
SQL> select locality from dba_part_indexes where index_name='EMPLOYEES_GLOBAL_PART_IDX';

LOCALITY
--------------------
GLOBAL

SQL>
SQL> select INDEX_NAME,PARTITION_NAME from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                PARTITION_NAME
------------------------- ---------------
EMPLOYEES_GLOBAL_PART_IDX P1
EMPLOYEES_GLOBAL_PART_IDX P2
EMPLOYEES_GLOBAL_PART_IDX P3
EMPLOYEES_GLOBAL_PART_IDX P4
EMPLOYEES_GLOBAL_PART_IDX P5

SQL> exec dbms_stats.gather_table_stats('','EMPLOYEES',cascade=>true);

PL/SQL procedure successfully completed.

SQL>
SQL>  select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                INDEX_TYPE                  GLO
------------------------- --------------------------- ---
EMPLOYEES_GLOBAL_PART_IDX NORMAL                      YES

Lets remove a empty partition :- 

SQL> select count(*) from employees where employee_id between 12 and 20;

  COUNT(*)
----------
         0

Note:---Removing Partation from Global indexes i.e. employees_global_part_idx

SQL> ALTER index employees_global_part_idx drop partition p4;

Index altered.


Before dropping :-
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                    PARTITION_NAME  STATUS
-------------------------     --------------- --------
EMPLOYEES_GLOBAL_PART_IDX     P1              USABLE
EMPLOYEES_GLOBAL_PART_IDX     P2              USABLE
EMPLOYEES_GLOBAL_PART_IDX     P3              USABLE
EMPLOYEES_GLOBAL_PART_IDX     P4              USABLE -------------dropped 
EMPLOYEES_GLOBAL_PART_IDX     P5              USABLE


After dropping:-

SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                PARTITION_NAME  STATUS
------------------------- --------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1              USABLE
EMPLOYEES_GLOBAL_PART_IDX P2              USABLE
EMPLOYEES_GLOBAL_PART_IDX P3              USABLE
EMPLOYEES_GLOBAL_PART_IDX P5              USABLE

Now we will drop the partition P3 and see status. 

When we drop this partition, it should invalidate the next highest partition. --VIMP( Keep in Mind) 

Here, P5 is next highest partition.

SQL> alter index employees_global_part_idx drop partition p3;

Index altered.

SQL>
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                PARTITION_NAME  STATUS
------------------------- --------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1              USABLE
EMPLOYEES_GLOBAL_PART_IDX P2              USABLE
EMPLOYEES_GLOBAL_PART_IDX P5              UNUSABLE ---------



Let rebuild - index paritioned - p5
-------------------------------------

SQL> alter index employees_global_part_idx rebuild partition p5;

Index altered.


SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                PARTITION_NAME  STATUS
------------------------- --------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1              USABLE
EMPLOYEES_GLOBAL_PART_IDX P2              USABLE
EMPLOYEES_GLOBAL_PART_IDX P5              USABLE


VIMP (Keep this in mind) :- If you drop a partition from the base table all index partition become invalidated

Below are the my Base table's partations..

SQL> select partition_name, num_rows from user_tab_partitions  where table_name = 'EMPLOYEES' order by partition_position;

PARTITION_NAME    NUM_ROWS
--------------- ----------
EMPLOYEES_PART1          9 ----------let's dropp this ..
EMPLOYEES_PART2          1
EMPLOYEES_PART3          1
EMPLOYEES_PART4          1

SQL> alter table EMPLOYEES drop partition EMPLOYEES_PART1;-------

SQL> select partition_name, num_rows from user_tab_partitions  where table_name = 'EMPLOYEES' order by partition_position;

PARTITION_NAME    NUM_ROWS
--------------- ----------
EMPLOYEES_PART2          1
EMPLOYEES_PART3          1
EMPLOYEES_PART4          1

SQL>
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                PARTITION_NAME  STATUS
------------------------- --------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1              UNUSABLE
EMPLOYEES_GLOBAL_PART_IDX P2              UNUSABLE
EMPLOYEES_GLOBAL_PART_IDX P5              UNUSABLE

Let's rebuild the partition

=================================

SQL> alter index employees_global_part_idx rebuild partition p1;

Index altered.

SQL> alter index employees_global_part_idx rebuild partition p2;

Index altered.

SQL> alter index employees_global_part_idx rebuild partition p5;

Index altered.


SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                PARTITION_NAME  STATUS
------------------------- --------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1              USABLE
EMPLOYEES_GLOBAL_PART_IDX P2              USABLE
EMPLOYEES_GLOBAL_PART_IDX P5              USABLE

In case you're using a global partitioned index, 

you can specify the update global indexes clause to prevent the global index from being marked as unusable. 

The two big advantages in specifying the update global indexes clause is that the index remains online and 

available during the maintenance operation and you don't have to rebuild it after the maintenance operation.

SQL> select partition_name, num_rows from user_tab_partitions  where table_name = 'EMPLOYEES' order by partition_position;

PARTITION_NAME    NUM_ROWS
--------------- ----------
EMPLOYEES_PART2          1
EMPLOYEES_PART3          1
EMPLOYEES_PART4          1

SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                PARTITION_NAME  STATUS
------------------------- --------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1              USABLE
EMPLOYEES_GLOBAL_PART_IDX P2              USABLE
EMPLOYEES_GLOBAL_PART_IDX P5              USABLE


Let drop EMPLOYEES_PART2 with clause -- update global indexes and check the status of indexes USABLE or UNUSABLE

SQL>  alter table EMPLOYEES drop partition EMPLOYEES_PART2 update global indexes;

Table altered.

partation is dropped :-

SQL> select partition_name, num_rows from user_tab_partitions  where table_name = 'EMPLOYEES' order by partition_position;

PARTITION_NAME    NUM_ROWS
--------------- ----------
EMPLOYEES_PART3          1
EMPLOYEES_PART4          1


Becasue of the Clause -- status is USABLE

SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                PARTITION_NAME  STATUS
------------------------- --------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1              USABLE
EMPLOYEES_GLOBAL_PART_IDX P2              USABLE
EMPLOYEES_GLOBAL_PART_IDX P5              USABLE

Global Index maintenance is now asynchronous

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

Maintaining global indexes were always a expensive task in Oracle, particularly in the context of partitioned tables, 

where dropping or truncating a table partition could cause a global index to become UNUSABLE/INVALID 

unless specified to update the indexes during the drop/truncate operation. 

However, updating the index entries during partition drop/truncate can eventually slow down the actual drop/truncate operation.

With Oracle 12c, a drop or truncate table partition (with update indexes clause) is optimized by deferring the maintenance of associated global indexes, while still leaving the indexes in VALID state. 

Prior to 12c, a drop/truncate table partition (with update indexes clause) would cause a synchronous maintenance for the associated global indexes and thereby would delay the actual drop/truncate operation. However, with 12c this global index maintenance operation is performed asynchronously which optimizes the drop or truncate partition operation.

SQL>  select INDEX_NAME,PARTITION_NAME,num_rows,orphaned_entries from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                        PARTITION_NAME                      NUM_ROWS ORP
--------------------------------- --------------------------------- ---------- ---
EMPLOYEES_GLOBAL_PART_IDX         P5                                         1 YES
EMPLOYEES_GLOBAL_PART_IDX         P2                                         0 YES
EMPLOYEES_GLOBAL_PART_IDX         P1                                         0 YES

As we can observe, the global indexes are still in VALID/USABLE state.

Undo - PDB Database

 

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