Oracle DBA Tips and Techniques

By Arjun Raja

RESTORE AND RECOVER NON SYSTEM TABLESPACE IN PLUGGABLE DATABASE

Posted by Arjun Raja on June 11, 2014

RESTORE AND RECOVER NON SYSTEM TABLESPACE IN PLUGGABLE DATABASE.

cdbd1:/u02/oradata/cdbd1/pdb1d1> l
total 4116376
-rw-r—– 1 oracle dba 20979712 May 11 06:02 temp01.dbf
-rw-r—– 1 oracle dba 1073750016 May 12 09:29 test12c01.dbf
-rw-r—– 1 oracle dba 283123712 May 12 10:15 system01.dbf
-rw-r—– 1 oracle dba 749740032 May 12 10:15 sysaux01.dbf

cdbd1:/u02/oradata/cdbd1/pdb1d1> rm test12c01.dbf

cdbd1:/u02/oradata/cdbd1/pdb1d1> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon May 12 10:23:49 2014

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select count (*) from test;
select count (*) from test
*
ERROR at line 1:
ORA-01116: error in opening database file 14
ORA-01110: data file 14: ‘/u02/oradata/cdbd1/pdb1d1/test12c01.dbf’
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Additional information: 4
Additional information: 4194304

SQL>

Now restore and recover tablespace TEST12C in PDB pdb1d1

First take tablespace offline –

sqlplus sys/xxxxx@pdb1d1 as sysdba

SQL> alter tablespace test12c offline immediate;

Tablespace altered.

SQL>

From CDB root.

RMAN> RESTORE TABLESPACE PDB1D1:TEST12C;

Starting restore at 12-05-2014 10:26:51
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=140 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=14 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00014 to /u02/oradata/cdbd1/pdb1d1/test12c01.dbf
channel ORA_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_12/o1_mf_nnndf_TAG20140512T085552_9q070rny_.bkp
channel ORA_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_12/o1_mf_nnndf_TAG20140512T085552_9q070rny_.bkp tag=TAG20140512T085552
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 12-05-2014 10:27:01

RMAN>

RMAN> RECOVER TABLESPACE PDB1D1:TEST12C;

Starting recover at 12-05-2014 10:27:39
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 12-05-2014 10:27:40
starting full resync of recovery catalog
full resync complete

RMAN>

cdbd1:/u02/oradata/cdbd1/pdb1d1> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon May 12 10:28:05 2014

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter tablespace test12c online;

Tablespace altered.

SQL>

All good.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: