Corrupting a PDB datafile – strange world

Corruption is bad, noone would like to restore blocks especially if you don’t check your backups correctly (backup check logical database). What would happen if you do not have valid backups and the blocks cannot be recovered. I would be sad and skipping corrupt blocks which have data is not a real solution, I think.

SQL> startup
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size		    2926320 bytes
Variable Size		  381683984 bytes
Database Buffers	  134217728 bytes
Redo Buffers		    5459968 bytes
show pDatabase mounted.
dbs
Database opened.
SQL> 
    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 RCAT 			  MOUNTED
SQL> alter pluggable database all open ;


Pluggable database altered.

SQL> alter session set container = rcat ;

Session altered.

SQL> create tablespace corruption datafile size 10m ;

Tablespace created.

SQL> create user jano identified by jano quota unlimited on corruption default tablespace corruption ;

User created.

SQL> grant connect, resource to jano ;

Grant succeeded.

[oracle@hackbook admin]$ sqlplus jano/jano@"(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=rcat))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.2.15)(PORT=1521)))"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 4 14:05:04 2016

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


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

SQL> create table test as select level id from dual connect by level <=500 ;

Table created.

SQL> create index testidx on test(id) ;

Index created.

[oracle@hackbook admin]$ rman target=/

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jul 4 14:06:56 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CCLOUD1 (DBID=1191441626)

RMAN> CONFIGURE BACKUP OPTIMIZATION ON ;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN> backup as compressed backupset database ;  
...
piece handle=/u01/app/oradata/fra/CLOUDDB1/3693C164563139E1E0530F02000A55ED/backupset/2016_07_04/o1_mf_nn
ndf_TAG20160704T140801_cqnnjjxq_.bkp tag=TAG20160704T140801 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46
Finished backup at 04-JUL-16

Starting Control File and SPFILE Autobackup at 04-JUL-16
piece handle=/u01/app/oradata/fra/CLOUDDB1/autobackup/2016_07_04/o1_mf_s_916323039_cqnnl05p_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 04-JUL-16

RMAN> 

[oracle@hackbook ~]$ sqlplus / as sysdba @corrupt.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 4 14:31:36 2016

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


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


dd of=/u01/app/oradata/CLOUDDB1/36994AC799462E62E0530F02000AFF86/datafile/o1_mf_corrupti_cqnms1yb_.dbf bs=8192 conv=notrunc seek=131 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt  EOF

[oracle@hackbook ~]$ dd of=/u01/app/oradata/CLOUDDB1/36994AC799462E62E0530F02000AFF86/datafile/o1_mf_corrupti_cqnms1yb_.dbf bs=8192 conv=notrunc seek=131 << EOF
> CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt  EOF
> EOF
0+1 records in
0+1 records out
117 bytes (117 B) copied, 0.0115306 s, 10.1 kB/s
[oracle@hackbook ~]$ 
[oracle@hackbook ~]$ 
[oracle@hackbook ~]$ 
[oracle@hackbook ~]$ sqlplus jano/jano@"(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=rcat))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.2.15)(PORT=1521)))"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 4 14:32:04 2016

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

Last Successful login time: Mon Jul 04 2016 14:22:35 +02:00

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

SQL> select * from test ;
select * from test
       *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 10, block # 131)
ORA-01110: data file 10:
'/u01/app/oradata/CLOUDDB1/36994AC799462E62E0530F02000AFF86/datafile/o1_mf_corru
pti_cqnms1yb_.dbf'

[oracle@hackbook ~]$ rman target=/

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jul 4 14:32:26 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CCLOUD1 (DBID=1191441626)

RMAN> validate database ;
....
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
10   FAILED 0              55           1281            2009247   
  File Name: /u01/app/oradata/CLOUDDB1/36994AC799462E62E0530F02000AFF86/datafile/o1_mf_corrupti_cqnms1yb_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              3               
  Other      1              1222            

[oracle@hackbook ~]$ sqlplus / as sysdba @corrupt.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 4 14:34:26 2016

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


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


dd of=/u01/app/oradata/CLOUDDB1/36994AC799462E62E0530F02000AFF86/datafile/o1_mf_corrupti_cqnms1yb_.dbf bs=8192 conv=notrunc seek=131 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt  EOF


SQL> select * from v$database_block_corruption ;

	10	  131	       1		  0 CORRUPT	       0

SQL> alter database datafile 10 offline ;
alter database datafile 10 offline
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "10"

What is this??????????????????????

SQL> alter session set container=rcat ;

Session altered.

SQL> alter database datafile 10 offline ;

Database altered.

[oracle@hackbook ~]$ rman target=/

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jul 4 14:35:45 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CCLOUD1 (DBID=1191441626)

RMAN> restore datafile 10 ;

Starting restore at 04-JUL-16
using channel ORA_DISK_1

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 00010 to /u01/app/oradata/CLOUDDB1/36994AC799462E62E0530F02000AFF86/datafile/o1_mf_corrupti_cqnms1yb_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oradata/fra/CLOUDDB1/36994AC799462E62E0530F02000AFF86/backupset/2016_07_04/o1_mf_nnndf_TAG20160704T140801_cqnnh3n8_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oradata/fra/CLOUDDB1/36994AC799462E62E0530F02000AFF86/backupset/2016_07_04/o1_mf_nnndf_TAG20160704T140801_cqnnh3n8_.bkp tag=TAG20160704T140801
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 04-JUL-16

RMAN> recover datafile 10 ;

Starting recover at 04-JUL-16
using channel ORA_DISK_1

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

Finished recover at 04-JUL-16

Here I already know that I can offline the datafile only from the PDB

[oracle@hackbook ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 4 14:36:41 2016

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


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


SQL> alter session set container=rcat ;

Session altered.

SQL> alter database datafile 10 online ;

Database altered.

[oracle@hackbook ~]$ sqlplus jano/jano@"(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=rcat))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.2.15)(PORT=1521)))"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 4 14:37:24 2016

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

Last Successful login time: Mon Jul 04 2016 14:32:04 +02:00

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

SQL> select * from test ;

	ID
----------
	 1
	 2
	 3
	 4
	 5
	 6


So what have we learned? we have to switch to the PDB when taking a file offline…

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