RE: PITR with BIGFILE tablespace

Is the logical corruption within the time frame where an "as of" query is possible to get out the data from the objects involved in the logical corruption?

Presumably you have or can get the script? That should document the tables involved.

Is there a lagged standby recovery database? (Last month or last quarter) if an "as of" query is not possible?

If neither of those options is available then it is time to find out if anyone has a "read the datafile into loadable data" tools that work on at least all the column types (and know how to skip the others) that are in the tables you need "back."

Good luck, and thanks for pointing this out.

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Friday, March 05, 2021 10:35 PM
To: ORACLE-L
Subject: PITR with BIGFILE tablespace

I was lazy and I created a BIGFILE tablespace to alleviate the pain with constant space monitoring and adding 32767M files to the tablespace. However, there was a logical corruption caused by running a wrong script on the schema, so I was told to restore the pluggable database to the point in time before the script was run. And that is where the trouble started:

915389 alter database recover datafile list clear 915390 Completed: alter database recover datafile list clear 915391 2021-03-03T22:13:09.935629-05:00
915392 RMAN In-place recover pluggable database id 3 to scn 0 915393 Pluggable Database PSH_8141 (3) incomplete recovery start set at change 45008648710, at 03/03/2021 10:30:55 915394 Pluggable Database Media Recovery Start 915395 2021-03-03T22:13:09.939521-05:00
915396 Serial Media Recovery started
915397 Pluggable Database Media Recovery Log /data/archive/1_65868_1031066218.dbf 915398 2021-03-03T22:13:12.996469-05:00
915399 Incomplete Recovery applied until change 44962012457 time 03/03/2021 09:00:15
915400 Errors in file
/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_86417_86514.trc (incident=13548103) (PDBNAME=CDB$ROOT):
915401 ORA-00600: internal error code, arguments: [kcpSetPDBIncompleteRecoverySCN-1], [44962012457], [45008648710], [], [], [], [], [], [], [], [], [] 915402 Incident details in:
/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_13548103/orcl_ora_86417_86514_i13548103.trc 915403 2021-03-03T22:13:14.351287-05:00
915404 Some DDE async actions failed or were cancelled 915405 2021-03-03T22:13:14.407330-05:00
915406 Use ADRCI or Support Workbench to package the incident.

Long story short, the tenant was lost. It was impossible to do PITR on the tablespace with 8.7 TB file. I even tried recovering the whole database. I contacted the Oracle support but they weren't able to help. The Oracle version is 12.2.0.1, the platform is Linux x86_64. Be aware that bigfile tablespaces can result in the inability to do point in time recovery. I haven't tried the complete recovery, because that would also bring back the results of the "masking" script. Apparently, Oracle isn't testing backup and recovery with the bigfile tablespaces, so surprises are possible. I thought that the feature will be stable by 12.2, but alas, no luck.

--

Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Sat Mar 06 2021 - 10:03:08 CET