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, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)