Forum Stats

  • 3,855,546 Users
  • 2,264,521 Discussions
  • 7,906,059 Comments

Discussions

Using PDBs in Refresh Manner

MS Sauter
MS Sauter Member Posts: 119 Blue Ribbon
edited May 9, 2018 6:52PM in Multitenant

Hello.  Not new to Oracle or OTN, but fairly new to multi tenant architecture.  I have done alot of reading of Oracle docs, MOS, blogs, etc., but have a few general questions OPERATIONALLY.  Here's what I know and have done:

1.) We are using Oracle 12.1..0.2 (not 12.2) on a RAC configuration as well as ASM on all our HW.

2,) Put a non-CDB DB (named golddb) in READ only mode on Server 1 and created it's XML manifest.

3.) With the DB down, sftp-copied all of the data and temp files for glddb along with the manifest from Server 1 to Server 2.  I will not need Server 1 any more at this point.  All work will be Server 2-centric.

4.) Installed oracle 12.1.2. on Server 2 and with it came all of the prouct options (as I could not select or deselect any)

5.) Patched Server 2 with the same patches I had on Server 1.

6.) Created a CDB named cdb1 on Server 2 via dbca but created it w/o any PDBs.

7.) Created PDB named pdb1 inside cdb1 using the manifest from Server 1's non-PDB glddb (NOTE: not ALL products were installed in golddb like they were in cdb1)

Excluding PDB$SEED, we will only ever have two PDBs in cdb1.  pdb1's contents will NEVER be touched/modified.  My hope is that we can have a TEST environment on Server 2 where testers can change a second PDB named pdb2 (not yet created but 'copied' from pdb1 and also residing in cdb1) after which it (i. e., pdb2) can be unplugged and the datafiles removed.  Then we could turn around and plug in pdb2 again using the XML manifest and the still-unchanged files in ASM from pdb1.

Here are my questions:

1.) MOST IMPORTANTLY, does this make approach sense and will it work operationally?**

2.) What would the HIGH LEVEL COMMAND(s) look like for:

     a.) Copying pdb1 over to pdb2

     b.) Unplugging pdb2 after TEST has modified it and then re-plugging it back in as pdb2 Again but with COPIES of pdb1's UNCHANGED data/temp files?

Thank you.


Matt

** I know there are many options with the CREATE PLUGGABLE DATABASE and ALTER PLUGGABLE DATABASE commands (e. g., SOURCE_FILE_NAME_CONVERT), just looking for agreement and some general syntax of a few commands if this scenario is feasible.

MS Sauter

Best Answer

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    edited May 8, 2018 10:05PM Answer ✓

    This :  " then re-plugging it back in as pdb2 "  is where you are confused.

    You would

    1.  Set PDB1 to READ ONLY

    2.  CREATE PDB2 FROM PDB1

    3.  Run all your tests in PDB2

    4.  DROP PDB2

    5. CREATE PDB2 FROM PDB1  (i.e. go back to Step 2)

    cycle through the steps as many times as you would like.

    Hemant K Chitale

Answers

  • Unknown
    edited May 8, 2018 4:46PM
    Here are my questions:1.) MOST IMPORTANTLY, does this make approach sense and will it work operationally?**2.) What would the HIGH LEVEL COMMAND(s) look like for: a.) Copying pdb1 over to pdb2 b.) Unplugging pdb2 after TEST has modified it and then re-plugging it back in as pdb2 Again but with COPIES of pdb1's UNCHANGED data/temp files?

    No - it does NOT makes sense - yes, operationally it will work (after all that is what you did to create the PDB in the first place)

    It seldom makes sense to try to implement your own solution when Oracle already provides no fewer than THREE methods to do what you are talking about:

    1. - drop the pdb and clone it from your own pdb1 'seed'

    2. - flashback database

    3. - backup/restore

    Excluding PDB$SEED

    That word 'seed' should give you the clue you need to guess the BEST option.

    Use your own PDB1 as a seed when you want to create a new pdb BASELINE.

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/pdb/pdb_clone/pdb_clone.html

    IMHO there is no justification/reason at all to do what you suggest when existing functionality exists that you can use with absolutely NO DEVELOPMENT resources at all. You would never get approval for that approach in any org I have worked with since it can't be justified.

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    edited May 8, 2018 10:05PM Answer ✓

    This :  " then re-plugging it back in as pdb2 "  is where you are confused.

    You would

    1.  Set PDB1 to READ ONLY

    2.  CREATE PDB2 FROM PDB1

    3.  Run all your tests in PDB2

    4.  DROP PDB2

    5. CREATE PDB2 FROM PDB1  (i.e. go back to Step 2)

    cycle through the steps as many times as you would like.

    Hemant K Chitale

  • MS Sauter
    MS Sauter Member Posts: 119 Blue Ribbon
    edited May 9, 2018 2:33PM

    Hermant K Chitale -thank you for your brief, concise direction.  I appreciate it and will file it in my notes.  This is my first shot at doing CDBs/PDBs using a NCDB from another server.

    ============================================

    rp0428 - thank you for your time as well as answer.  Unfortunately, you did not read my initial post close enough to realize that, other than PDB$SEED created by dbca, I have no nor did I create a PDB1 to clone from.  I have been requested to create the initial PDB1 from a NCDB over on another server.  I need those datafiles which cloning from PDB$SEED would not be able to use.  I will not be repeating this process once I get that NCDB over to my new server as then I can go about doing the process that Hermant describes.

    You stated and I repeat below:

    "It seldom makes sense to try to implement your own solution when Oracle already provides no fewer than THREE methods to do what you are talking about:

    1. - drop the pdb and clone it from your own pdb1 'seed'

    2. - flashback database

    3. - backup/restore"

    As for 3 options, I will say:

    1 - I have NO PDB1 in existence to drop.  You stated, "That word 'seed' should give you the clue you need to guess the BEST option." I cannot use the seed as I need the data and associated files from a NCDB located on another server and once I get it moved over, it will be our GOLD PDB and I will NEVER repeat this initial process again.  After that, I can clone PDB2 from PDB1 over and over.

    2 - I have already set up and demonstrated flashback database but this is a study to show which method (CDB/PDB vs flashback) will be the best for our factory environments)

    3 - backup/restore - can do this as well, but we are looking at the faster options of flashback or using CDB/PDBs

    So I have reviewed and am trying to use Oracle-provided solutions - NOT create my own.  Imperfect, yes, but I am trying to utilize the best approach.

    Lastly, you mentioned, "You would never get approval for that approach in any org I have worked with since it can't be justified."  Well, I have and likely will never work in any org you have and vice-versa, so cannot buy off on your words in that sentence.  And while my approach appears flawed (I am learning), there IS justification at the end of the day for the end-product so I do what I can.

    I am grateful that you have made me think through this more and maybe I use a clone approach in conjunction with a DB link back to the NCDB on the other server.  Not sure but will look at it all further.

    As a frequent OTN user who very much appreciates the wealth of knowledge that many members provide, I have seen many of your past responses.  You would seem to be very Oracle savvy in many areas and desire to help others.  That is great.  It would be so much MORE profitable, if you could remove the condescension in your replies when various submitters file queries.  Everyone comes the the table with their experiences/past and not an expert in every facet of Oracle.  You only distract from your good technical feedback when you look down on others in written form.

    Thanks again.

    Matt

  • Unknown
    edited May 9, 2018 5:01PM
    Unfortunately, you did not read my initial post close enough to realize that, other than PDB$SEED created by dbca, I have no nor did I create a PDB1 to clone from.

    Sorry - but you are WRONG on both counts.

    1. I DID read it carefully

    MS Sauter wrote:Hermant K Chitale -thank you for your brief, concise direction. I appreciate it and will file it in my notes. This is my first shot at doing CDBs/PDBs using a NCDB from another server.============================================rp0428 - thank you for your time as well as answer. Unfortunately, you did not read my initial post close enough to realize that, other than PDB$SEED created by dbca, I have no nor did I create a PDB1 to clone from. I have been requested to create the initial PDB1 from a NCDB over on another server. I need those datafiles which cloning from PDB$SEED would not be able to use. I will not be repeating this process once I get that NCDB over to my new server as then I can go about doing the process that Hermant describes.You stated and I repeat below:"It seldom makes sense to try to implement your own solution when Oracle already provides no fewer than THREE methods to do what you are talking about:1. - drop the pdb and clone it from your own pdb1 'seed'2. - flashback database3. - backup/restore"As for 3 options, I will say:1 - I have NO PDB1 in existence to drop. You stated, "That word 'seed' should give you the clue you need to guess the BEST option." I cannot use the seed as I need the data and associated files from a NCDB located on another server and once I get it moved over, it will be our GOLD PDB and I will NEVER repeat this initial process again. After that, I can clone PDB2 from PDB1 over and over.2 - I have already set up and demonstrated flashback database but this is a study to show which method (CDB/PDB vs flashback) will be the best for our factory environments)3 - backup/restore - can do this as well, but we are looking at the faster options of flashback or using CDB/PDBsSo I have reviewed and am trying to use Oracle-provided solutions - NOT create my own. Imperfect, yes, but I am trying to utilize the best approach.Lastly, you mentioned, "You would never get approval for that approach in any org I have worked with since it can't be justified." Well, I have and likely will never work in any org you have and vice-versa, so cannot buy off on your words in that sentence. And while my approach appears flawed (I am learning), there IS justification at the end of the day for the end-product so I do what I can. I am grateful that you have made me think through this more and maybe I use a clone approach in conjunction with a DB link back to the NCDB on the other server. Not sure but will look at it all further.As a frequent OTN user who very much appreciates the wealth of knowledge that many members provide, I have seen many of your past responses. You would seem to be very Oracle savvy in many areas and desire to help others. That is great. It would be so much MORE profitable, if you could remove the condescension in your replies when various submitters file queries. Everyone comes the the table with their experiences/past and not an expert in every facet of Oracle. You only distract from your good technical feedback when you look down on others in written form.Thanks again.Matt

    2. You DID create a PDB1 that you could clone from

    This is what you posted in the original thread.

    7.) Created PDB named pdb1 inside cdb1 using the manifest from Server 1's non-PDB glddb (NOTE: not ALL products were installed in golddb like they were in cdb1)

    That is EXACTLY 'a PDB1 to clone' from and is EXACTLY what both I and Solomon are suggesting you use as your 'seed' to clone from when you need to refresh your working PDB.

    as then I can go about doing the process that Hermant describes.

    LOL! That 'process that Hemant describes' is EXACTLY what I first said.

    1 - I have NO PDB1 in existence to drop. You stated, "That word 'seed' should give you the clue you need to guess the BEST option." I cannot use the seed as I need the data and associated files from a NCDB located  on another server and once I get it moved over, it will be our GOLD PDB and I will NEVER repeat this initial process again. After that, I can clone PDB2 from PDB1 over and over.

    You still don't seem to understand that what I first said and what Hemant said are EXACTLY THE SAME THING.

    You don't drop PDB1 - you use it as your 'SEED' database to do just what you said at the end of the above 'clone PDB2 from PDB1 over and over' - that is EXACTLY what I suggested.

    It is PDB2 that you drop when you are done with it. Then you 'clone PDB2 from PDB1 over and over'.

    When I said the word 'seed' should give you the clue you need I did NOT mean you should clone from PDB$SEED. I meant you should create your own seed (PDB1) and then use it to 'clone PDB2 over and over'.

    You are now saying EXACTLY the same thing I first said - but you don't seem to realize that yet.

    2 - I have already set up and demonstrated flashback database but this is a study to show which method (CDB/PDB vs flashback) will be the best for our factory environments)

    The 'best' solution IMHO for your use case is the 'clone PDB2 from PDB1 over and over' that I first said. That assumes you have the storage available to maintain the storage that the PDB1 'seed' needs to hold all of the data you have created for your baseline.

    A flashback solution also needs that storage. The only solution that doesn't is to use your original process that has the storage on the other server.

    3 - backup/restore - can do this as well, but we are looking at the faster options of flashback or using CDB/PDBs

    Backup/restore for your use case should be fast - any solution involves copying all of the data files from 'somewhere'. The main difference is the location of that 'somewhere'.

    Backups are usually stored on a different server so a backup/restore solution doesn't require space on your new main server for the backup/seed data.

    It is COMMON PRACTICE now for orgs to create their own SEED databases for the same purpose as your use case. Besides a DEV version there is usually a UNIT_TEST version that already has a good-sized set of test data.

    The UNIT_TEST PDB/seed is then used to restore a PDB when you do regression testing so that all tests will have the same data and the same amount of data when they are executed.

    Again - whether you yet realize it or not Hemant said the same thing I did - he just said it differently.

    MS SauterMS Sauter
  • MS Sauter
    MS Sauter Member Posts: 119 Blue Ribbon
    edited May 9, 2018 6:52PM

    rp0428,

    Thank you again for the 2nd reply.  I appreciate it.  I actually am catching myself here, but you were correct in that I said,

    "7.) Created PDB named pdb1 inside cdb1 using the manifest from Server 1's non-PDB glddb (NOTE: not ALL products were installed in golddb like they were in cdb1)".

    I am going to contradict myself here: even though I did created a maniifest as mentioned in my original Step #6 from the NCDB on remote Server #1, I did NOT then actually create the pdb1 itself inside the cdb1.  I said I did in the initial post, but I did not.  I cannot explain that sentence on my side.

    My ULTIMATE GOAL is, as Hemant and you pointed out, is to have a seed pdb1 which I clone over into pdb2.  Once test personnel finish changes on pdb2, I drop pdb2 and recreate it again from pdb1, etc, etc.  All true.  My struggle has been, and I have not yet succeeded, in creating the INITIAL pdb1 from the NCDB from which I created the XML manifest file and from which I copied over OMF-managed datafiles using ASMCMD from the remote Server #1.

    Hence, my last comment that maybe I can create this INITIAL pdb seed (i. e., pdb1) using  a clone approach in conjunction with a DB link back to the NCDB on the other server.  Since I am using ASM and my create_file_dest=+DATA on both the remote/source DB (Server #1) and target/destination (Server #2), I believe I will not need any file/source_file convert statements either.

    Electronic media is great for communicating information, but, in this one case, I straight-up miscommunicated.  And then, sometimes, semantics bite like when you said 'seed" and I interpreted that as PDB$SEED, when you meant pdb1 (in my case).  Thank you for sticking with me and swimming a little deeper.

    Regards,

    Matt

This discussion has been closed.