You conned using SQL Plus to the root container of a multitenant container database (CDB) with
SYSDBA privilege.
The CDB has several pluggable databases (PDBs) open in the read/write mode.
There are ongoing transactions in both the CDB and PDBs.
What happens alter issuing the SHUTDOWN TRANSACTIONAL statement?
A.
The shutdown proceeds immediately.
The shutdown proceeds as soon as all transactions in the PDBs are either committed or rolled
hack.
B.
The shutdown proceeds as soon as all transactions in the CDB are either committed or rolled
back.
C.
The shutdown proceeds as soon as all transactions in both the CDB and PDBs are either
committed or rolled back.
D.
The statement results in an error because there are open PDBs.
Explanation:
* SHUTDOWN [ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL [LOCAL]]
Shuts down a currently running Oracle Database instance, optionally closing and dismounting a
database. If the current database is a pluggable database, only the pluggable database is closed.The consolidated instance continues to run.
Shutdown commands that wait for current calls to complete or users to disconnect such as
SHUTDOWN NORMAL and SHUTDOWN TRANSACTIONAL have a time limit that the
SHUTDOWN command will wait. If all events blocking the shutdown have not occurred within the
time limit, the shutdown command cancels with the following message:
ORA-01013: user requested cancel of current operation
* If logged into a CDB, shutdown closes the CDB instance.
To shutdown a CDB or non CDB, you must be connected to the CDB or non CDB instance that
you want to close, and then enter
SHUTDOWN
Database closed.
Database dismounted.
Oracle instance shut down.
To shutdown a PDB, you must log into the PDB to issue the SHUTDOWN command.
SHUTDOWN
Pluggable Database closed.
Note:
* Prerequisites for PDB Shutdown
When the current container is a pluggable database (PDB), the SHUTDOWN command can only
be used if:
The current user has SYSDBA, SYSOPER, SYSBACKUP, or SYSDG system privilege.
The privilege is either commonly granted or locally granted in the PDB.
The current user exercises the privilege using AS SYSDBA, AS SYSOPER, AS SYSBACKUP, or
AS SYSDG at connect time.
To close a PDB, the PDB must be open.
While shutdown transactional we will bring down the CDB and the associated PDBs under it. Just test it in shutdown normal! So C
C is not correct. Process of shutting down CDB doesn’t wait for PDB transactions to be commited or rolledback.
B
I Think that “C” is the best choice.
“A CDB instance is shut down in the same fashion that a non-CDB instance is.”
Morris, Matthew (2014-03-20). Study Guide for 1Z0-060: Upgrade to Oracle Database 12c: Oracle Certification Prep (p. 46). ODB Press. Kindle Edition.
“TRANSACTIONAL [LOCAL]
Performs a planned shutdown of an instance while allowing active transactions to complete first. It prevents clients from losing work without requiring all users to log off.
No client can start a new transaction on this instance. Attempting to start a new transaction results in disconnection. After completion of all transactions, any client still connected to the instance is disconnected. Now the instance shuts down just as it would if a SHUTDOWN IMMEDIATE statement was submitted. The next startup of the database will not require any instance recovery procedures.
The LOCAL mode specifies a transactional shutdown on the local instance only, so that it only waits on local transactions to complete, not all transactions. This is useful, for example, for scheduled outage maintenance.”
http://docs.oracle.com/database/121/SQPUG/ch_twelve042.htm
Correct answer B. Test Done:
On CDB xterm:
SQL> insert into cdb_t values(3);
1 row created.
On PDB xterm:
SQL> insert into pdb_t values(3);
1 row created.
Connect on CDB on another xterm:
SQL> shutdown transactional;
….
Nothing happens.
Go to first the CDB xterm
SQL> commit;
Commit complete.
SQL>
The shtdown is gong on:
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SQL> SQL>
On the PDB xterm:
SQL> commit;
commit
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 1811
Session ID: 373 Serial number: 19634
The PDB lost the contact and the transaction was rollback.
Thanks nax ..Nice Testing..
B