Pluggable database save state
Automate Pluggable DB opening during Container instance startup
PDB Saved State is a feature in 12.1.0.2 for saving the OPEN state of all PDBS when CDB is restarted. This is for preserving the state of a Pluggable database proir to CDB restart.
During a container database startup, Pluggable databases will be opened/closed to the same Saved state.
Imagine we have 15 PDB’s and we want certain set of PDB’s to open/close as part of Container DB restart, this is the certain feature we need to look into, this would save DBA’s time during any planned/unplanned outages.
PDB Saved State can be discarded upon our requirement and same explained in this article.
1) Check the Saved State for all PDB’s from DBA view
select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;
|
1 2 |
SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id; no rows selected |
2) Check the Saved State for all PDB’s from CDB view
|
1 2 |
SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states; no rows selected |
3) Change Save State of PDB by usig below statement
alter pluggable database CDB1_PDB1 save state;
4) To discard any saved state of a pluggable database using below statement
alter pluggable database CDB1_PDB1 discard state;
Please find below example which gives an understanding about the save state and discard state.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
SQL> show pdbs CON_ID CON_NAME. OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB MOUNTED SQL> show con_id CON_ID ------------------------------ 1 SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> select NAME,DBID,CON_ID,OPEN_MODE,RESTRICTED,OPEN_TIME,CREATE_SCN,TOTAL_SIZE/1024/1024 AS pdb_size from v$pdbs; NAME DBID CON_ID OPEN_MODE RES OPEN_TIME CREATE_SCN PDB_SIZE -------- ---------- ---------- ---------- --- ----------------------------------- ---------- ---------- PDB$SEED 3825831795 2 READ ONLY NO 07-NOV-22 07.55.14.821 PM -08:00 2016961 736 ORCLPDB 401375578 3 MOUNTED 2164563 756 SQL> select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role||'-'||CDB from v$database, v$instance; NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE||'-'||CDB -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ORCLCDB - orclcdb@19cstand.test.com - orclcdb - 19.0.0.0.0 - READ WRITE - 07-NOV-2022 19:55:02 - PRIMARY-YES SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ----------- ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB MOUNTED SQL> alter pluggable database ORCLPDB open; Pluggable database altered. SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states; no rows selected SQL> alter pluggable database ORCLPDB save state; Pluggable database altered. SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id; NAME STATE --------------- -------------- ORCLPDB OPEN SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2969564864 bytes Fixed Size 9139904 bytes Variable Size 654311424 bytes Database Buffers 2298478592 bytes Redo Buffers 7634944 bytes Database mounted. Database opened. SQL> sho pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ----------- ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB READ WRITE NO SQL> alter pluggable database ORCLPDB discard state; Pluggable database altered. SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id; no rows selected SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started Total System Global Area 2969564864 bytes Fixed Size 9139904 bytes Variable Size 654311424 bytes Database Buffers 2298478592 bytes Redo Buffers 7634944 bytes Database mounted. Database opened. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB MOUNTED SQL> |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTExperts is always active on social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Loading...
