We have seen an ERROR in the opening database. let’s recover it.
Step 1: Connect to the database and start the database using STARTUP command
[oracle@dbatesting orclpdb]$ echo $ORACLE_SID orcl [oracle@dbatesting orclpdb]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 26 17:40:07 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2097152000 bytes Fixed Size 8794696 bytes Variable Size 620760504 bytes Database Buffers 1459617792 bytes Redo Buffers 7979008 bytes Database mounted. ORA-01157: cannot identify/lock data file 13 - see DBWR trace file ORA-01110: data file 13: '/home/appndb/oracle/oradata/orcl/orclpdb/test01.dbf' SQL>
We can see in the above output database is unable to open . And it threw an error that it cannot identify/lock data file 13.
Step 2: Let’s make it offline
SQL> alter database datafile '/home/appndb/oracle/oradata/orcl/orclpdb/test01.dbf' offline drop; alter database datafile '/home/appndb/oracle/oradata/orcl/orclpdb/test01.dbf' offline drop * ERROR at line 1: ORA-01516: nonexistent log file, data file, or temporary file "/home/appndb/oracle/oradata/orcl/orclpdb/test01.dbf" in the current container SQL>
oops..!! It gave me an error as this file does not belong to the CDB database. It belongs to the pdb database. Let’s change session to the pdb database and make it offline.
Step 3: connect to pdb and try to drop it.
SQL> alter session set container=orclpdb; Session altered. SQL> show con_name CON_NAME ------------------------------ ORCLPDB SQL> alter database datafile '/home/appndb/oracle/oradata/orcl/orclpdb/test01.dbf' offline drop; Database altered. SQL>
Step 4: Again change session to CDB$ROOT and try the opening database
SQL> alter session set container=CDB$ROOT; Session altered. SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> alter database open; Database altered.
Step 5: Try opening the pdb database
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB MOUNTED
SQL> alter pluggable database orclpdb open;
Pluggable database altered.
SQL>
Step 6: Change the current session to the pdb database
SQL> alter session set container=orclpdb; Session altered.
Step 7: Drop test tablespace
SQL> drop tablespace test including contents and datafiles; Tablespace dropped. SQL>
This is how we can recover the database if there will the loss of any data file and your database is in NOARCHIVELOG mode
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
