The SAVE STATE clause serves to maintain the state of the PDB when the CDB is restarted. For example, if when the CDB was restarted the OPEN_MODE of the PDB was in READ ONLY, then when the CDB will be available the PDB will initialize in READ ONLY state automatically.
- Saving the state of the PDBS PDBOL and PDBOL_2 through the command “ALTER PLUGGABLE DATABASE <PDB NAME> SAVE STATE;”:
SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDBOL READ WRITE PDBOL_2 READ WRITE SQL> ALTER PLUGGABLE DATABASE PDBOL SAVE STATE; Pluggable database altered. SQL> ALTER PLUGGABLE DATABASE PDBOL_2 CLOSE; Pluggable database altered. SQL> ALTER PLUGGABLE DATABASE PDBOL_2 OPEN READ ONLY; Pluggable database altered. SQL> ALTER PLUGGABLE DATABASE PDBOL_2 SAVE STATE; Pluggable database altered. SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDBOL_2 READ ONLY PDBOL READ WRITE
- Simulating the unavailability of the CBD:
[oracle@dbdg admin]$ ps -ef|grep smon oracle 5601 1 0 08:29 ? 00:00:00 ora_smon_dbc2 oracle 10617 2734 0 15:27 pts/0 00:00:00 grep smon [oracle@dbdg admin]$ kill -9 5601 [oracle@dbdg admin]$ ps -ef|grep smon oracle 10619 2734 0 15:28 pts/0 00:00:00 grep smon
- Rebooting the CDB:
[oracle@dbdg admin]$ sqlplus "/as sysdba" SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 28 15:28:20 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 599785472 bytes Fixed Size 2927192 bytes Variable Size 276825512 bytes Database Buffers 314572800 bytes Redo Buffers 5459968 bytes Database mounted. Database opened.
- See that the PDB states were maintained:
SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDBOL READ WRITE PDBOL_2 READ ONLY
- The saved states of the PDBS can be viewed through the DBA_PDB_SAVED_STATES view:
SQL> SET PAGES 2000
SQL> SET LINES 2000
SQL> SELECT * FROM DBA_PDB_SAVED_STATES;
CON_ID CON_NAME INSTANCE_NAME CON_UID GUID STATE RES
---------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------- -------------------------------- -------------- ---
3 PDBOL dbc2 3670152564 661F0F868DF57CE1E0534738A8C09CC0 OPEN NO
4 PDBOL_2 dbc2 4141312548 661F5DBD01CB09B7E0534738A8C08B5F OPEN READ ONLY NO
- Deleting the saved states of all PDBS with the command <ALTER PLUGGABLE DATABASE ALL DISCARD STATE;>:
SQL> ALTER PLUGGABLE DATABASE ALL DISCARD STATE; Pluggable database altered. SQL> SET PAGES 2000 SQL> SET LINES 2000 SQL> SELECT * FROM DBA_PDB_SAVED_STATES; no rows selected
A cláusula de SAVE STATE serve para manter o estado do PDB quando o CDB for reinicializado. Por exemplo, se quando o CDB for reinicializado o OPEN_MODE do PDB for definido no momento que o PDB estava em READ ONLY, assim que o CDB estiver ativo novamente, o PDB inicializá-ra com o estado READ ONLY automaticamente.
- Salvando o estado dos PDBS PDBOL e PDBOL_2 através do comando “ALTER PLUGGABLE DATABASE <NOME DO PDB> SAVE STATE;”:
SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDBOL READ WRITE PDBOL_2 READ WRITE SQL> ALTER PLUGGABLE DATABASE PDBOL SAVE STATE; Pluggable database altered. SQL> ALTER PLUGGABLE DATABASE PDBOL_2 CLOSE; Pluggable database altered. SQL> ALTER PLUGGABLE DATABASE PDBOL_2 OPEN READ ONLY; Pluggable database altered. SQL> ALTER PLUGGABLE DATABASE PDBOL_2 SAVE STATE; Pluggable database altered. SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDBOL_2 READ ONLY PDBOL READ WRITE
- Simulando a indisponibilidade do CDB:
[oracle@dbdg admin]$ ps -ef|grep smon oracle 5601 1 0 08:29 ? 00:00:00 ora_smon_dbc2 oracle 10617 2734 0 15:27 pts/0 00:00:00 grep smon [oracle@dbdg admin]$ kill -9 5601 [oracle@dbdg admin]$ ps -ef|grep smon oracle 10619 2734 0 15:28 pts/0 00:00:00 grep smon
- Reinicializando o CDB:
[oracle@dbdg admin]$ sqlplus "/as sysdba" SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 28 15:28:20 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 599785472 bytes Fixed Size 2927192 bytes Variable Size 276825512 bytes Database Buffers 314572800 bytes Redo Buffers 5459968 bytes Database mounted. Database opened.
- Note que os estados dos PDB foram mantidos:
SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDBOL READ WRITE PDBOL_2 READ ONLY
- Os estados salvos dos PDBS podem ser visualizados através da view DBA_PDB_SAVED_STATES:
SQL> SET PAGES 2000
SQL> SET LINES 2000
SQL> SELECT * FROM DBA_PDB_SAVED_STATES;
CON_ID CON_NAME INSTANCE_NAME CON_UID GUID STATE RES
---------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------- -------------------------------- -------------- ---
3 PDBOL dbc2 3670152564 661F0F868DF57CE1E0534738A8C09CC0 OPEN NO
4 PDBOL_2 dbc2 4141312548 661F5DBD01CB09B7E0534738A8C08B5F OPEN READ ONLY NO
- Descartando os estados salvos de todos os PDBS através do comando <ALTER PLUGGABLE DATABASE ALL DISCARD STATE;>:
SQL> ALTER PLUGGABLE DATABASE ALL DISCARD STATE; Pluggable database altered. SQL> SET PAGES 2000 SQL> SET LINES 2000 SQL> SELECT * FROM DBA_PDB_SAVED_STATES; no rows selected
Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)