Oracle 12c Multi-tenant PDB recovery (single PDB data file, non-system PDB tablespace, entire PDB database)

Source: Internet
Author: User
Tags log log sqlplus

Environment: DB version Oracle database 12c Enterprise Edition release 12.2.0.1.0-64bit Production

Experiment Preparation:
1.--Database Archiving mode
Sql> select Name,log_mode from V$database;
NAME Log_mode
--------- ------------
ANDYCDB ARCHIVELOG > Database archiving mode

2.--Log In the PDB creates the experimental table space with the user
[Email protected] ~]$ sqlplus sys/[email protected]:1521/pdb01 as Sysdba
Sql>
Create tablespace bbb logging datafile '/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb.dbf '
Size 1m autoextend on next 1m maxsize 2m extent management local;
Tablespace created.
Sql>alter tablespace bbb Add datafile '/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb02.dbf ' size 1M
Autoextend on next 1m maxsize 2m;
Tablespace altered.

Create user Andy identified by Andy Default tablespace users;
Create user BBB identified by BBB default tablespace BBB;
Grant DBA to Andy;
Grant DBA to BBB;

3.--back up the entire CDB and all the PDB below it
[Email protected] ~]$ mkdir/home/oracle/rmanback/
[[email protected] ~]$ Rman target/

run{
Alter system archive log current;
Allocate channel CH1 type disk;
Allocate channel CH2 type disk;
Backup incremental Level 0 database plus Archivelog Delete input
Format '/home/oracle/rmanback/db_%d_%u '
Tag=db_inc_0;
Release channel CH1;
Release channel CH2;
}
Finished backup at 12-MAY-17

I. Recovering PDB data files

When the database is open, all data files for the current data are checked.
For System,sysaux and undo table space data files, if there is a problem, the database cannot open. If a common data file in the PDB is lost, we can skip the offline and then hit the database and restore the data file later:
[Email protected] ~]$ sqlplus sys/[email protected]:1521/pdb01 as Sysdba
Sql> Show Con_name
Con_name
------------------------------
PDB01
Sql> select name from V$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/andycdb/pdb01/system01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/sysaux01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/undotbs01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/users01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb02.dbf
6 rows selected.

Touch PDB data File user Delete
[Email protected] ~]$ cd/home/oracle/app/oracle/oradata/andycdb/pdb01/
[Email protected] ~]$ MV USERS01.DBF Users01.dbf.bak
--Analog business
C:\users\andy>sqlplus Andy/[email PROTECTED]:1521/PDB01
Sql> CREATE TABLE Andy as select * from All_objects;
CREATE TABLE Andy as SELECT * FROM All_objects *
ERROR at line 1:
Ora-01116:error in opening database file 12
Ora-01110:data file 12:
'/HOME/ORACLE/APP/ORACLE/ORADATA/ANDYCDB/PDB01/USERS01.DBF '
Ora-27041:unable to open File
linux-x86_64 error:2: No such file or directory
Additional Information:3
Sql> alter system checkpoint;
System altered.
--Alert_andycdb.log log to see the error:
Ora-01110:data file: '/home/oracle/app/oracle/oradata/andycdb/pdb01/users01.dbf '
Ora-01565:error in identifying file '/home/oracle/app/oracle/oradata/andycdb/pdb01/users01.dbf '
--Enter PDB to view data file status
Sql>
Col name for A60;
Select File#,status,name from V$datafile;
file# STATUS NAME
---------- ------- ------------------------------------------------------------
9 system/home/oracle/app/oracle/oradata/andycdb/pdb01/system01.dbf
Ten ONLINE/HOME/ORACLE/APP/ORACLE/ORADATA/ANDYCDB/PDB01/SYSAUX01.DBF
Online/home/oracle/app/oracle/oradata/andycdb/pdb01/undotbs01.dbf
Online/home/oracle/app/oracle/oradata/andycdb/pdb01/users01.dbf
Online/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb.dbf
Online/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb02.dbf
6 rows selected.

Description: 12c R1 version, if the PDB data file is corrupted, will cause CDB to go down together. 12c R2 version PDB data file is corrupt, both CDB and PDB have no downtime.
--Using Rman recovery
[Email protected] pdb01]$ Rman target Sys/[email protected]:1521/pdb01
rman> recover datafile 12;
Rman-06094:datafile must be restored
rman> ALTER DATABASE datafile offline;
rman> ALTER DATABASE datafile offline;
rman> restore DataFile 12;
rman> recover datafile 12;
Finished recover at 12-MAY-17
Then online processing of the data files
rman> ALTER DATABASE datafile online;
Statement processed
--Simulation Business success!
Sql> CREATE TABLE Andy as select * from All_objects;
Table created.

Second, restore the PDB non-system table space

Experiment: Simulate PDB non-system tablespace damage.
[Email protected] ~]$ sqlplus sys/[email protected]:1521/pdb01 as Sysdba
--Query table space situation
Sql> select Tablespace_name,status from Dba_tablespaces;
Tablespace_name STATUS
------------------------------ ---------
SYSTEM ONLINE
Sysaux ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
BBB ONLINE
Sql>
Col Tablespace_name for A15
Col file_name for A55
SELECT Tablespace_name, file_id, file_name, round (Bytes/(1024x768), 0) Total_space
From Dba_data_files where tablespace_name= ' BBB ' ORDER by Tablespace_name;
Tablespace_name file_id file_name Total_space
--------------- ---------- ------------------------------------------------------- -----------
BBB 13/HOME/ORACLE/APP/ORACLE/ORADATA/ANDYCDB/PDB01/BBB.DBF 1
BBB 14/HOME/ORACLE/APP/ORACLE/ORADATA/ANDYCDB/PDB01/BBB02.DBF 1
--Damage to the simulation table space
[Email protected] pdb01]$ pwd
/home/oracle/app/oracle/oradata/andycdb/pdb01
[email protected] pdb01]$ ll bbb*
-rw-r-----. 1 Oracle Oinstall 1056768 may 06:42 bbb02.dbf
-rw-r-----. 1 Oracle Oinstall 1056768 may 06:42 bbb.dbf
[Email protected] pdb01]$ RM-RF bbb*
--Analog business
C:\users\andy>sqlplus Bbb/[email PROTECTED]:1521/PDB01
Sql> CREATE TABLE Andy as select * from All_objects;
ERROR at line 1:
Ora-01116:error in opening database file 13
Ora-01110:data File 13:
'/HOME/ORACLE/APP/ORACLE/ORADATA/ANDYCDB/PDB01/BBB.DBF '
Ora-27041:unable to open File
linux-x86_64 error:2: No such file or directory
Additional Information:3
--Alert_andycdb.log log to see the error:
Ora-01110:data file: '/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb.dbf '
Ora-01565:error in identifying file '/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb.dbf '
Ora-27037:unable to obtain file status
Ora-01110:data file: '/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb02.dbf '
Sql> alter system checkpoint;
System altered.
Sql> select Tablespace_name,status from Dba_tablespaces;
Tablespace_name STATUS
------------------------------ ---------
SYSTEM ONLINE
Sysaux ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
BBB ONLINE
--Using Rman recovery
[Email protected] pdb01]$ Rman target Sys/[email protected]:1521/pdb01
Rman> alter tablespace BBB offline;
Ora-27041:unable to open File
Rman> alter tablespace BBB offline immediate;
Statement processed
rman> recover tablespace BBB;
Rman-06094:datafile must be restored
rman> restore tablespace BBB;
Finished restore at 12-MAY-17
rman> recover tablespace BBB;
Finished recover at 12-MAY-17
Rman> alter tablespace BBB online;
Statement processed
--Simulation Business success!
Sql> CREATE TABLE Andy as select * from All_objects where rownum>2;
Table created.

Third, single PDB database recovery
The prerequisite for recovering a single PDB is that the CDB has been able to start normally, with the restore pluggable database PDB name specified in Rman for recovery in the case of the CDB startup, as follows
Sql> Show Con_name
Con_name
------------------------------
PDB01
Sql> select name from V$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/andycdb/4ecf8621e3da38eee0531019640aa598/datafil
E/o1_mf_system_dk5wmv0s_.dbf
/home/oracle/app/oracle/oradata/andycdb/4ecf8621e3da38eee0531019640aa598/datafil
E/o1_mf_sysaux_dk5wmv11_.dbf
/home/oracle/app/oracle/oradata/andycdb/4ecf8621e3da38eee0531019640aa598/datafil
E/o1_mf_users_dk6wky42_.dbf
/home/oracle/app/oracle/oradata/andycdb/4ecf8621e3da38eee0531019640aa598/datafil
E/o1_mf_undo_1_dk5wmv13_.dbf
--simulates the entire PDB corruption
[Email protected] datafile]$ cd/home/oracle/app/oracle/oradata/andycdb/4ecf8621e3da38eee0531019640aa598
[Email protected] 4ecf8621e3da38eee0531019640aa598]$ RM-RF datafile/
Sql> alter system checkpoint;
System altered.
Sql> Show PDBs
con_id con_name OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 Pdb$seed READ only NO
3 PDB01 Mounted
Rman>restore pluggable Database pdb01;
Finished restore at 11-MAY-17
Rman>recover pluggable Database pdb01;
Finished recover at 11-MAY-17
[Email protected] ~]$ Sqlplus/as SYSDBA
sql> Alter pluggable database PDB01 open;
Pluggable database altered.

Oracle 12c Multi-tenant PDB recovery (single PDB data file, non-system PDB tablespace, entire PDB database)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.