Solution for failing to open the entire database due to accidental deletion of pdb data files in oracle 12c, 12 cpdb

Solution for failing to open the entire database due to accidental deletion of pdb data files in oracle 12c, 12 cpdb

Preface

Recently, a colleague accidentally deleted the data files of a plug-in Database (PDB), and the results showed that the entire database, including the container database (CDB), and other plug-in databases were useless. Helpless... After trying a variety of methods to solve the problem, I felt it was necessary to share the solution process. For more information about the methods, see the following. Let's take a look at the detailed introduction.

The method is as follows:

1. Use the sys account to enter the instance. It can be seen that the container database is in the mounted state. All PDBs inserted in the instance are also mounted.

sqlplus sys/pwd@orcl12c as sysdba;

2. Try to open CDB and prompt that the database file cannot be loaded.

SQL> alter database open; alter database open * 1st Line Error: ORA-01157: unable to identify/lock data file 101-see DBWR trace file ORA-01110: Data File 101: 'd: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL12C \ PDBDTJK \ NMEMC. dbf'

This is the case when it is disabled, started, and rolled back and forth:

SQL> shutdown immediate ORA-01109: the database is not open and has been detached. The ORACLE routine has been disabled. The SQL> startupORACLE routine has been started. Total System Global Area 2147483648 bytesFixed Size 3834152 bytesVariable Size 671092440 bytesDatabase Buffers 1459617792 bytesRedo Buffers 12939264 bytes database loaded. ORA-01157: unable to identify/lock data file 101-see DBWR trace file ORA-01110: Data File 101: 'd: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL12C \ PDBDTJK \ NMEMC. dbf'

3. Check online materials to mark the lost file offline and delete it. But the system prompts that the file cannot be found!

SQL> alter database datafile 101 offline; alter database datafile 101 offline * 1st Line Error: ORA-01516: non-existent log file, data file or temporary file "101"

This is strange. You prompted the file, but now you do not know the file.

4. It is estimated that the current database is CDB, and the file belongs to PDB. Therefore, switch to the related PDB and take the file offline.

SQL> alter session set container = PDBDTJK; the session has been changed. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- 13 PDBDTJK MOUNTED

5. offline (offline, drop). Success!

SQL> alter database datafile 'd: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL12C \ PDBDTJK \ NMEMC. DBF 'offline; alter database datafile 'd: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL12C \ PDBDTJK \ NMEMC. DBF 'offline * row 1st error: ORA-01145: do not allow immediate offline SQL> alter database datafile 'd unless media restore is Enabled: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL12C \ PDBDTJK \ NMEMC. dbf'offline drop; the database has been changed.

6. switch back to CDB. Open. Successful!

SQL> alter session set container = CDB $ ROOT; the session has been changed. SQL> alter database open; the database has been changed.

Oh, my God! Hao ~~~

7. Summary

1) if a PDB is damaged, the entire CDB and other PDB cannot be used, and thus cannot be recovered? No, this is oracle. It cannot be so mentally retarded.

Really. I heard that the version of oracle 12c r1 is that PDB is down, and CDB will be down together. It is better to go to R2. It only goes down to PDB.

2) switching from CDB to PDB is critical for this restoration. The answers provided on the Internet are 11 GB or earlier. An Instance corresponds to a database, so there is no concept of database switching. The file says that the database is offline.

3) as long as the database instance can be started and whether or not the CDB is enabled, sys can go in:

sqlplus sys/pwd@servername as sysdba;

But PDB does not. You cannot use the conn user/pwd @ servername method without enabling it. However, you can also switch the session:

SQL> alter session set container = PDBDTJK; the session has been changed.

This is Sun Wukong's life-saving.

4) The solution to the above problems seems to be step-by-step and clear, but how much suffering and suffering has been done in the middle. The problem that occurred yesterday evening has not been solved yet. Go home and check the information. When I woke up at, I checked the session switch. In the morning, people are the most awake.

I don't know how this file was deleted. When you continue to use the file system, the system will prompt that the file is locked. It is estimated that the database will be stopped and deleted again. Isn't that an issue. I can't imagine how hard it would be if the database didn't get up today. Although it is a development database, many projects are in use and each has its own PDB.

Well, the above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.