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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l