BLOG DBA TIPS AND TRICKS

This is how you can manage to fix issue ORA-01113 when opening a pdb.
Suppose you have a cdb within some pdbs in ‘MOUNTED’ state. When trying to open a pluggable database, you face issue like below :

SQL> show pdbs
 CON_ID;   CON_NAME            OPEN MODE  RESTRICTED
———- —————————— ———- ———-
                2 PDB$SEED   n                   READ ONLY  NO
                …
                9 GIS                                                    MOUNTED
SQL>

SQL> alter pluggable database GIS open;
alter pluggable database GIS open
*
ERROR at line 1:
ORA-01113: file 168 needs media recovery
ORA-01110: data file 168:
‘+DATA1/DBCONSO/75C3093A66DF2601E0536D21120A29CC/DATAFILE/data_1.dbf’

Solution
1. Check pdb datafiles :
SQL> select file#, name from v$datafile;
FILE# NAME
———- ——————————————————————–
75 +DATA1/dbconso/GIS/system01.dbf
76 +DATA1/dbconso/GIS/sysaux01.dbf
77 +DATA1/dbconso/GIS/undotbs01.dbf
78 +DATA1/dbconso/GIS/users01.dbf
79 +DATA1/dbconso/GIS/system01_i2_undo.dbf
83 +DATA1/DBCONSO/75C3093A66DF2601E0536D21120A29CC/DATAFILE/data.483.987698573
84 +DATA1/DBCONSO/75C3093A66DF2601E0536D21120A29CC/DATAFILE
…
167 +DATA1/DBCONSO/75C3093A66DF2601E0536D21120A29CC/DATAFILE/data_2.dbf
168 +DATA1/DBCONSO/75C3093A66DF2601E0536D21120A29CC/DATAFILE/data_1.dbf

25 rows selected.

2. Check pdf datafiles required for recovery using v$recovery_file :
SQL> select r.file#,d.name from v$recover_file r, v$datafile d where r.file#=d.file#;

FILE# NAME
———- ——————————————————————-
75 +DATA1/dbconso/GIS/system01.dbf
76 +DATA1/dbconso/GIS/sysaux01.dbf
77 +DATA1/dbconso/GIS/undotbs01.dbf
78 +DATA1/dbconso/GIS/users01.dbf
79 +DATA1/dbconso/GIS/system01_i2_undo.dbf
…
/data_2.dbf
168 +DATA1/DBCONSO/75C3093A66DF2601E0536D21120A29CC/DATAFILE/data_1.dbf

25 rows selected.
SQL>

NB: Here, all 25 datafiles need recovery

3. Running restore command
Configure rman to point to backup location. In case archive logs are not found in archive location on disk, rman will search for relevant archive files in backup location.
Here, backup are located in Netbackup storage. We configure rman to point to Netbackup catalog:

$ rman target /
Recovery Manager: Release 12.2.0.1.0 – Production on Mon Dec 17 13:45:59 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBCONSO (DBID=1592572161)

RMAN> run {ALLOCATE CHANNEL ch00 TYPE ‘SBT_TAPE’;
2> ALLOCATE CHANNEL ch01 TYPE ‘SBT_TAPE’;
3> ALLOCATE CHANNEL ch02 TYPE ‘SBT_TAPE’;
4> ALLOCATE CHANNEL ch03 TYPE ‘SBT_TAPE’;
5> ALLOCATE CHANNEL ch04 TYPE ‘SBT_TAPE’;
6> ALLOCATE CHANNEL ch05 TYPE ‘SBT_TAPE’;
7> ALLOCATE CHANNEL ch06 TYPE ‘SBT_TAPE’;
8> ALLOCATE CHANNEL ch07 TYPE ‘SBT_TAPE’;
9> ALLOCATE CHANNEL ch08 TYPE ‘SBT_TAPE’;
10> SEND ‘NB_ORA_CLIENT=bak-svr-dbcora-01,NB_ORA_POLICY= Oracle_DB_Backup_4,NB_ORA_SERV=svr-bakmaster-01,NB_ORA_SCHED=Default_Oracle_4_Full’;
11> recover datafile 75,76,77,78,79,83,84,98,99,151,152,154,155,156,157,158,159,160,161,162,163,164,166,167,168; }
12>
using target database control file instead of recovery catalog
allocated channel: ch00
channel ch00: SID=4723 instance=dbconso1 device type=SBT_TAPE
channel ch00: Veritas NetBackup for Oracle – Release 8.0 (2016110921)

allocated channel: ch01
channel ch01: SID=4842 instance=dbconso1 device type=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle – Release 8.0 (2016110921)

allocated channel: ch02
channel ch02: SID=5087 instance=dbconso1 device type=SBT_TAPE
channel ch02: Veritas NetBackup for Oracle – Release 8.0 (2016110921)

allocated channel: ch03
channel ch03: SID=2 instance=dbconso1 device type=SBT_TAPE
channel ch03: Veritas NetBackup for Oracle – Release 8.0 (2016110921)

Recovery Manager complete.
$
4. Once recovery successfully completed, we can now open the pdb:
SQL> alter pluggable database GIS open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
…
9 GIS READ WRITE NO