Forum Stats

  • 3,872,942 Users
  • 2,266,490 Discussions
  • 7,911,391 Comments

Discussions

Shutting down the CDB without individually closing each PDBs

KirkPatrick
KirkPatrick Member Posts: 122 Blue Ribbon
edited Oct 5, 2017 12:12PM in Multitenant

DB version: 12.2

OS : Oracle Linux 7.4

I have 5 production PDBs in the below mentioned CDB (its named CDB4 ) . Due to a hardware problem, I wanted to quickly 'close' all the PDBs. Instead, I just shutdown the CBD as shown below.

Is it recommended to shutdown the CDB without individually closing each PDBs ?

[[email protected] ~]$ . oraenv

ORACLE_SID = [oracle] ? CDB4

The Oracle base has been set to /u01/app/oracle

[[email protected] ~]$

[[email protected] ~]$

[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 3 17:00:14 2017

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

FRivasF-OraclevanpupiKirkPatrickPeter_L_

Answers

  • Unknown
    edited Oct 3, 2017 5:16PM
    Is it recommended to shutdown the CDB without individually closing each PDBs ?

    I am not aware of any 'official' recommendation but there should NOT be any issues with shutdown of the CDB since that will close any open PDBs.

    Only your org knows what each PDB does and how it is managed.

    Shutdown of a database can, at times, take time. Depending on the type of shutdown and the activity going on at the time Oracle may wait for that activity to cease.

    The root/CDB should generally be used as a MANAGEMENT tool. It can be valuable to have that tool fully operational in case you need to MANAGE one or more PDBs that may be having trouble with a shutdown.

    I recommend treating the PDBs as individual databases (or, in 12.2 as members of an 'application') and manage the PDBs that way.

    KirkPatrick
  • FRivasF-Oracle
    FRivasF-Oracle Member Posts: 11 Employee
    edited Oct 4, 2017 3:09AM

    Hello @KirkPatrick ,

    It is a supported procedure to shutdown CDB without manually closing all related PDBs beforehand. Your shutdown immediate command at CDB level will ensure transaction consistency on each PDB, whether using local or shared undo. Take a look to the alert log meanwhile.

    Regards,

    KirkPatrick
  • vanpupi
    vanpupi Member Posts: 48
    edited Oct 4, 2017 4:12AM

    Hello,

    As far as I know it is fully supported to do it this way.

    The way I usually use is (when running or creating a new pdb) "alter pluggable database all save state". This way you make sure when you start the db again, the pdbs open in the same state as you closed the CDB.

    But to shutdown, it's normal to stop the cdb.

    Best regards

    Pieter

    KirkPatrick
  • FRivasF-Oracle
    FRivasF-Oracle Member Posts: 11 Employee
    edited Oct 4, 2017 4:26AM

    Hello,

    Also, regarding the PDBs startup, remember that in a Grid Infrastructure (standalone/restart or RAC) you will be able to create Services associated to a specific PDB. So if this new service is created to automatically startup, then the PDB will be automatically opened - if not opened already -. So even when a PDB is not a clusterware resource per se, it will actually be pulled-up by any starting service associated to it. This is also compatible with saved states technique which Pieter already pointed.

    Regards,

    KirkPatrick
  • Unknown
    edited Oct 4, 2017 1:26PM
    The way I usually use is (when running or creating a new pdb) "alter pluggable database all save state". This way you make sure when you start the db again, the pdbs open in the same state as you closed the CDB.

    Everyone, of course, is free to do it however they want. It isn't an issue when everything goes ok.

    But there can be some pretty nasty 'operational problems' when things go wrong.

    And it only takes a problem in ONE pdb for things to go wrong and cause problems.

    For instance that 'save state' you mention. What if the 'state' for a PDB says the PDB should be opened but can't be opened? Guess what happens to the ENTIRE root/CDB/PDB startup process.

    I NEVER recommend that databases be configured to start automatically when the OS reboots. And I NEVER recommend that PDBs use 'save state' to try to automatically open.

    The reason is that I want to maintain as much control over the operations as possible and be able to intercede as quickly as possible when there is a problem of any sort.

    It is really TRIVIAL to have a startup process that brings the DB up and opens the desired PDBs in an orderly manner. By 'orderly' I mean checking each step of the process to make sure it completed successfully.

    At a minimum that means the root/CDB is NOT configured to start automatically on a server reboot. And it means that NO pdbs are configured to open automatically.

    First the root/CDB is started and confirmed to be running properly, with no issues needing to be addressed. Then, and ONLY then, are the desired PDBs opened. Depending on the configuration that could include opening multiple PDBs before confirming that others opened properly.

    But when using the application containers available with 12.2 the application PDBs are treated just like a 'mini-container' database. That is, the PDB application root is opened first and confirmed to be operating properly. Then the remaining application PDBs are opened.

    In 12.2 an 'application' can consist of multiple PDBs with one PDB being considered the application root and containing objects that other subordinate PDBs share. So it is both operationally, and logically, appropriate to make sure the 'parent' container is working properly before trying to open the 'children'.

  • vanpupi
    vanpupi Member Posts: 48
    edited Oct 5, 2017 7:39AM
    rp0428 wrote:For instance that 'save state' you mention. What if the 'state' for a PDB says the PDB should be opened but can't be opened? Guess what happens to the ENTIRE root/CDB/PDB startup process.I NEVER recommend that databases be configured to start automatically when the OS reboots. And I NEVER recommend that PDBs use 'save state' to try to automatically open.The reason is that I want to maintain as much control over the operations as possible and be able to intercede as quickly as possible when there is a problem of any sort.It is really TRIVIAL to have a startup process that brings the DB up and opens the desired PDBs in an orderly manner. By 'orderly' I mean checking each step of the process to make sure it completed successfully.At a minimum that means the root/CDB is NOT configured to start automatically on a server reboot. And it means that NO pdbs are configured to open automatically.

    I partially agree, but this is a opinion complete separate from cdb/pdb. Imho you have 2 "groups". People going for automatic startup and people who don't. Each group of people has it's own reasons to do so. Is the one better than the other, imho not. It's is a vision and sometimes strategy one is better or sometimes strategy 2 is better.
    We have customers which I help the way you say, because I know automatic startup WILL cause errors, other customers are perfectly fine with an automatic startup.

    So let me put a nuance in the previous post. "save state" -> It depends :-)

    FRivasF-OracleKirkPatrickKirkPatrick
  • FRivasF-Oracle
    FRivasF-Oracle Member Posts: 11 Employee
    edited Oct 5, 2017 8:07AM

    Hello @rp0428 ,

    Regarding this (forgive me for the quoting) :  "And it only takes a problem in ONE pdb for things to go wrong and cause problems. For instance that 'save state' you mention. What if the 'state' for a PDB says the PDB should be opened but can't be opened? Guess what happens to the ENTIRE root/CDB/PDB startup process."

    Well, in 12.2 this is not the case. I spent some minutes to show you what happens below, but I can already clarify that nothing will happen to CDB$ROOT nor to any different PDBs in the same container when one PDB is in trouble. They will keep working as usual. Same thing for the saved states case. If one PDB is unable to start, then obviously the CDB$ROOT will not be able to honor the saved state, but it will honor the rest of saved states for any other PDB regardless of its creation order.

    I do recall problems in 12.1.0.1 version. But this has been already solved. Nevertheless don't hesitate sending me the related SR or testcase If you find a different scenario and we will help you.

    Regards,

    ----

    SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 5 13:02:21 2017

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

    Connected to an idle instance.

    SQL> startup

    ORACLE instance started.

    Total System Global Area 1258291200 bytes

    Fixed Size            8792296 bytes

    Variable Size          452986648 bytes

    Database Buffers      788529152 bytes

    Redo Buffers            7983104 bytes

    Database mounted.

    Database opened.

    SQL> show pdbs

        CON_ID CON_NAME              OPEN MODE  RESTRICTED

    ---------- ------------------------------ ---------- ----------

         2 PDB$SEED              READ ONLY  NO

         4 PDB0CDB3              MOUNTED

         5 PDB1CDB3              MOUNTED

    SQL> alter pluggable database all OPEN;

    Pluggable database altered.

    SQL> alter pluggable database all save state;

    Pluggable database altered.

    SQL> show pdbs

        CON_ID CON_NAME              OPEN MODE  RESTRICTED

    ---------- ------------------------------ ---------- ----------

         2 PDB$SEED              READ ONLY  NO

         4 PDB0CDB3              READ WRITE NO ------------> current saved state

         5 PDB1CDB3              READ WRITE NO ------------> current saved state

    SQL> select file_name from cdb_data_files where con_id=5;

    FILE_NAME

    -------------------------------------------------------------------------

    +DATA/CDB3/506BCF2D68B8749AE055000000000001/DATAFILE/system.313.944969357

    +DATA/CDB3/506BCF2D68B8749AE055000000000001/DATAFILE/sysaux.329.944969357

    +DATA/CDB3/506BCF2D68B8749AE055000000000001/DATAFILE/undotbs1.315.944969357

    SQL> alter pluggable database PDB1CDB3 close;

    SQL> alter session set container=PDB1CDB3;

    SQL> alter database datafile 17 offline; -----> this is the PDB system tbs datafile

    SQL> alter session set container=CDB$ROOT;

    Session altered.

    SQL> alter pluggable database PDB0CDB3 close; -------> for the sake of the test, we also close the first PDB

    SQL> show pdbs

        CON_ID CON_NAME              OPEN MODE  RESTRICTED

    ---------- ------------------------------ ---------- ----------

         2 PDB$SEED              READ ONLY  NO

         4 PDB0CDB3              MOUNTED       --------------> DB is mounted and ready to be opened

         5 PDB1CDB3              MOUNTED       --------------> DB is mounted and will not be able to open (no SYSTEM)

    SQL> shutdown immediate;

    Database closed.

    Database dismounted.

    ORACLE instance shut down.

    SQL> startup

    ORACLE instance started.

    Total System Global Area 1258291200 bytes

    Fixed Size            8792296 bytes

    Variable Size          452986648 bytes

    Database Buffers      788529152 bytes

    Redo Buffers            7983104 bytes

    Database mounted.

    Database opened.

    SQL> show pdbs

        CON_ID CON_NAME              OPEN MODE  RESTRICTED

    ---------- ------------------------------ ---------- ----------

         2 PDB$SEED              READ ONLY  NO

         4 PDB0CDB3              READ WRITE NO --------------> DB saved state succeeded

         5 PDB1CDB3              MOUNTED       --------------> DB saved state not honored because of missing system

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

    Now, testing with a new PDB, on a different startup order :

       

        

    SQL> create pluggable database PDB9CDB3 from PDB0CDB3;

    Pluggable database created.

    SQL> show pdbs

        CON_ID CON_NAME              OPEN MODE  RESTRICTED

    ---------- ------------------------------ ---------- ----------

         2 PDB$SEED              READ ONLY  NO

         4 PDB0CDB3              READ WRITE NO

         5 PDB1CDB3              MOUNTED

         6 PDB9CDB3              MOUNTED -------> this is the new one

    SQL> alter pluggable database PDB9CDB3 open;

    Pluggable database altered.

    SQL> alter pluggable database PDB9CDB3 save state;

    Pluggable database altered.

    SQL> show pdbs

        CON_ID CON_NAME              OPEN MODE  RESTRICTED

    ---------- ------------------------------ ---------- ----------

         2 PDB$SEED              READ ONLY  NO

         4 PDB0CDB3              READ WRITE NO

         5 PDB1CDB3              MOUNTED

         6 PDB9CDB3              READ WRITE NO

        

    SQL> alter pluggable database PDB0CDB3 close;

    Pluggable database altered.

    SQL>  alter session set container=PDB0CDB3;

    Session altered.

    SQL> alter database datafile 13 offline;

    Database altered.

    SQL> alter session set container=CDB$ROOT;

    Session altered.

    SQL> show pdbs

        CON_ID CON_NAME              OPEN MODE  RESTRICTED

    ---------- ------------------------------ ---------- ----------

         2 PDB$SEED              READ ONLY  NO

         4 PDB0CDB3              MOUNTED

         5 PDB1CDB3              MOUNTED

         6 PDB9CDB3              READ WRITE NO

    SQL> shutdown immediate;

    Database closed.

    Database dismounted.

    ORACLE instance shut down.

    SQL> startup

    ORACLE instance started.

    Total System Global Area 1258291200 bytes

    Fixed Size            8792296 bytes

    Variable Size          452986648 bytes

    Database Buffers      788529152 bytes

    Redo Buffers            7983104 bytes

    Database mounted.

    Database opened.

    SQL> show pdbs

        CON_ID CON_NAME              OPEN MODE  RESTRICTED

    ---------- ------------------------------ ---------- ----------

         2 PDB$SEED              READ ONLY  NO

         4 PDB0CDB3              MOUNTED

         5 PDB1CDB3              MOUNTED

         6 PDB9CDB3              READ WRITE NO -----------> New PDB is opened due it's saved state, even when the two first are unable to honor it

    vanpupiKirkPatrickKirkPatrickPeter_L_
  • Unknown
    edited Oct 5, 2017 12:12PM
    " . . . Guess what happens to the ENTIRE root/CDB/PDB startup process."I do recall problems in 12.1.0.1 version. But this has been already solved.

    I'm not sure what 'problems' you are referring to specifically when you say 'has been already solved'. In 12.2 Oracle has certainly addressed some of the PHYSICAL issues that existed but, as I tried to emphasize in my reply, there are operational issues involved that are every bit as important as the pure physical process of starting up a container-oriented DB.

    Consider three statements I made that are the core of what I said:

    1. It isn't an issue when everything goes ok.

    I'd be surprised if anyone disagreed with this. If there aren't any problems during the process then it really doesn't matter much how complex the startup operations are: providing they are done in the correct order.

    However, Oracle only has control over the PHYSICAL operations. It doesn't have control, or even knowledge of, the LOGICAL operations that may be important to an installation or application. Those logical DB operations can involved both physical and logical non-db issues.

    Example of the physical include verifying that logging and archive logging are operating properly (remote archive locations could be down - Oracle won't know that as part of its startup). Example of the logical are application-specific issues where the order of services needs to be done properly and in the correct order. That is even more important with the use of the new 12.2 application containers.

    2. But there can be some pretty nasty 'operational problems' when things go wrong.

    I'd also be surprised if there is much, if any, disagreement about that statement.

    When things go wrong and the production DB can't be brought up there is a mad scramble to: 1) find the cause of the problem, 2) determine an effective fix 3) apply that fix 4) retry the startup and validate that the fix really worked.

    Consider the 'common logging' that existed in 12.1 - ONE SET of REDO logs. If even one PDB is up and running it needs those logs to be available. That has, at times, made it difficult to address problems with the startup of the root/CDB or one of the other PDBs that would use those same logs. At times it was necessary to actually shut down the PDBs that had started successfully in order to be able to address/fix the problems with the other components.

    Through RAC and/or DataGuard functionality/architecture into the mix and that 'start everything at once' philosophy is just not practical given all of the things that can go wrong.

    Now you have multiple instances or container databases that have to be synchronized.

    3. The reason is that I want to maintain as much control over the operations as possible and be able to intercede as quickly as possible when there is a problem of any sort.

    My philosophy is to do things, including startup/shutdown, in an orderly controlled fashion. To me that means working with the 'containers' first and making sure that management structure is working properly. For RAC or DataGuard that means the containers/instances for ALL nodes.

    Once the superstructure is known to be working properly then the startup of the PDBs or application containers can be done. For an application once the application container is known to be working properly the member PDBs for that application can be dealt with.

    4. It is really TRIVIAL to have a startup process that brings the DB up and opens the desired PDBs in an orderly manner. By 'orderly' I mean checking each step of the process to make sure it completed successfully.

    There is NO PENALTY for doing things in an orderly fashion and exerting manual control over the startup of the various architectural components.

    Also I see NO ADVANTAGE to trying to do everything in an 'all or nothing' approach given the considerable (IMHO) number of things that can go wrong.

    I'm not advocating that someone manually enter OPEN statements for 10s or 100s of PDBs in a large environment. You can have a simple script that has 10 or 100 OPEN statements in it if you want to do that. But those PDBs should be opened ONLY AFTER it has been confirmed that the root/CDB or application container is already online and functioning properly.

This discussion has been closed.