Re: 12c pluggable database shared SGA question
Date: Thu, 11 Sep 2014 09:24:21 +0200
Message-ID: <CALSQGr+VvoaO96gQS0SzaUwv6_aW3s=PgpKrgdG7O11ZuOmZ9Q_at_mail.gmail.com>
Hi all, I'm enjoying this thread so I would like to add my 2 cents...
I haven't explored the scenario yet, but, with the ease of cloning it might
> be feasible, if only one PDB is to be recovered in a 24 PDB environment, to
> perform the recovery on another CDB and clone back to the original CDB
> host. Yes, it could mean chewing through a lot of useless redo, but ...
>
>
The PDB PITR requires anyway an auxiliary instance, so recovering on
another CDB is the only way to do it...
> One might also start to look at alternatives for some of the scenarios,
> such as Flashback DB, PDB cloning, Standby, etc. "Just because we've
> always done it 'that way' doesn't mean it needs to stay 'that way' in the
> future."
>
Flashback sadly affect the whole CDB. One possible way to workaround this (need to try it) is to detach all the other PDBs, flashback the whole CDB for the required PDB and attach back all the other PDBs, but it doesn't seem a reasonable solution. Moreover, flashback is possible only to a point in time later than the last PDB PITR. this almost prevent you from any flashback operation in a CDB...
To add fuel to the fire, I would like to point out that not only the redo logs concur, but there's the need to restore the shared UNDOTBS. in a multi-terabyte database, the UNDOTBS can take several hundreds of GB... and if the PDB that we want to restore is small, there's a big disproportion between the data to be recovered and the data actually restored.
Possible alternatives? failback to expdp for small PDBs? using multiple
writable snapshost clones?
Or, I would recommend, plan carefully the consolidation considering all the
possible scenarios, depending on the real customer requirements. This is
how why should act everyday, isn't it?
-- LudoReceived on Thu Sep 11 2014 - 09:24:21 CEST
>
>
> On 10/09/2014 8:52 PM, Chitale, Hemant K wrote:
>
> Imagine only 2 PDBs in 12c.
>
> PDB “A” generates 1GB of redo per day.
>
> PDB “B” generates 100GB of redo per day.
>
> All is fine when running normally.
>
>
>
> Now imagine having to do a PITR recovery of PDB “A” as of 1.5 days ago.
> Unless you have frequent Incremental Backups of PDB “A” you’d have to have
> Oracle go through a lot of archived redo that is “thrown away” to be able
> to do the PITR for PDB “A”.
>
>
>
> Extend this to PDB “A” being one of 24 PDBs in the same MultiTenant
> environment.
>
>
>
> Hemant K Chitale
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *On
> Behalf Of *Kevin Closson
> *Sent:* Thursday, September 11, 2014 2:54 AM
> *To:* Glenn.Travis_at_sas.com; Oracle-L_at_freelists.org
> *Subject:* Re: 12c pluggable database shared SGA question
>
>
>
> Shared REDO *should* be the end of the conversation, really.
>
>
>
>
>
>
> ------------------------------
>
> *From:* Glenn Travis <Glenn.Travis_at_sas.com>
> *To:* "Oracle-L_at_freelists.org" <Oracle-L_at_freelists.org>
> *Sent:* Wednesday, September 10, 2014 9:11 AM
> *Subject:* 12c pluggable database shared SGA question
>
>
> 12c shared resources:
> "PDBs share UNDO, REDO and control files."
> "PDBs share common SGA and background processes."
>
> The selling point is that only small increments of memory are added as
> additional PDBs are added. BUT
>
> A problem I have with the 12c 'pluggable' database, is that is shares an
> SGA - that is library cache, and buffer cache. The diagrams used in all
> the promo (and training) materials show an ERP, CRM and DW type databases
> sharing the same memory. This seems counter intuitive to everything we as
> DBAs have been taught for many years. Those databases have completely
> different users and usage patterns/requirements. I realize the PDBs are
> not sharing the buffers and statements between them, but they ARE sharing
> the memory footprint, and there is only so much memory.
>
> See slides here :
> [11g] http://goo.gl/wQ612C vs. [12c] http://goo.gl/eshQTA
>
> Obviously an ERP is queried (and tuned) differently (single transactions,
> high volume, key values, shared sql) than a DW (multi, complex
> transactions, low number of users, long running statements, full
> table/index scans, low key value usage, and non-sharable sql).
> Co-existence of these types of environments will not only be impossible to
> tune in one SGA, but the cache will be useless. The DW will flush out all
> the 'good' buffers/pages used by the ERP/OLTP application users, and the
> non-sharable sql will flush out and fragment the library cache. So the ERP
> will have nothing left in memory and constantly re-parse the 'sharable' sql
> and go to disk for all the data. This just doesn't seem logical.
>
> What am I missing here? How can you possibly have this kind of shared
> environment? I agree that 'same' type environments may work as pluggable
> databases in a shared SGA, provided you have enough memory, but not
> disparate databases like those in the examples.
>
> I have asked several people, including Oracle instructors the following
> question, but have not yet received a definitive, convincing answer.
> Comments?
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> This email and any attachments are confidential and may also be
> privileged. If you are not the intended recipient, please delete all copies
> and notify the sender immediately. You may wish to refer to the
> incorporation details of Standard Chartered PLC, Standard Chartered Bank
> and their subsidiaries at https://www.sc.com/en/incorporation-details.html
> .
>
>
>
-- http://www.freelists.org/webpage/oracle-l