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 asSHUTDOWN 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
no its wrong.. Answer b is correct only..
try this scenario in your test database.
one session just do a simple insert in cdb
dont commit or rollback
another session try to do shutdown transactional
it will keep hang until the existing transactions getting commited or rollback and the existing connections getting closed.
Answer B is correct only.. Tested
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.
When I tried an insert in CDB and tried to shutdown transactional in another session it didnt do the shutdown. but if the transactions are pending in pdbs its not waiting
The important thing here is what happens to PDB
so the uncommited transaction in PDB are discarded.
so B is correct.
tamil, it is true but really really strange.
why transactional is not the same way like normal?
when I was in PDB and shu normal in CDB its really waiting exit from PDB…
B. The shutdown proceeds as soon as all transactions in the CDB are either committed or rolled back.
B is correct answer, tested.
B
B
The correct answer is C
Transactions needs commit or rollback on CDB
and
Transactions needs commit or rollback on PDB
http://docs.oracle.com/database/121/ADMIN/cdb_pdb_admin.htm
There is no SHUTDOWN command for a PDB that is equivalent to SHUTDOWN TRANSACTIONAL or SHUTDOWN ABORT for a non-CDB.
So, likely B
C
The answer is B:
OCP: ORACLE DATABASE 12C ADMINISTRATOR CERTIFIED PROFESSIONAL STUDY GUIDE: EXAM 1Z0-063
If there are pending transactions in the CDB$ROOT container, the SHUTDOWN TRANSACTIONAL command will wait until the transactions are committed or rolled back, and then the shutdown will continue Issuing the SHUTDOWN TRANSACTIONAL command from the CDB$ROOT will roll back any pending transactions in PDBs and discard any changes that are not committed.
Looks like B is the right answer.
Tested
Inserting in a PDB table and doing shutdown transactional from the root does not wait for the commit
SQL> create table test1 (id number(1));
Table created.
SQL> insert into test1 values(1);
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 7151
Session ID: 42 Serial number: 7017
If you do the shutdown transactional from the PDB, then it wait for the commit
SQL> insert into test1 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> exit
alter session set container=PDBTEST1;
Session altered.
SQL> shutdown transactional; only close after the commit in the other session is complete
Pluggable Database closed.
Peter IS RIGHT!
SQL> alter session set container=testpdb1;
Session altered.
SQL> select * from test3;
select * from test3
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only
SQL> alter pluggable database testpdb1 open;
Pluggable database altered.
SQL> select * from test3;
no rows selected
SQL> insert into test3
2 values
3 (1,’liushao’);
1 row created.
SQL> shutdown transactional
ORA-01097: cannot shutdown while in a transaction – commit or rollback first
B is the right answer.
Testing:
1. Open terminal1 and connect to CDB root as dbadmin user start transaction (without committing first)
SQL> show con_name
CON_NAME
——————————
CDB$ROOT
SQL>
SQL> create table cdb_test1 (name varchar (10), id number(1));
Table created.
SQL> insert into cdb_test1 values(‘Adam’,1);
1 row created.
SQL> select * from cdb_test1;
NAME ID
———- ———-
Adam 1
SQL>
2. open terminal2, connect as hr user and start trsnsaction (do not commit yet)
SQL> show con_name
CON_NAME
——————————
PDBEXPL
SQL>
SQL> conn hr/hr@PDBEXPL
Connected.
SQL>
SQL> show con_name
CON_NAME
——————————
PDBEXPL
SQL>
SQL> create table pdb_test1 (name varchar (10), id number(1));
Table created.
SQL> insert into pdb_test1 values(‘Adam’,1);
1 row created.
SQL>
3. open terminal3 , connect to cdbroot as sysdba. Issue the SHUTDOWN TRANSACTIONAL command
SQL> show con_name
CON_NAME
——————————
CDB$ROOT
SQL>
SQL> shutdown transactional –> shutdown hangs
…..
4. Commit transaction terminal 2 –> SHUTDOWN TRANSACTIONAL still hangs
5. commit transaction on terminal1 (CDB) –> SHUTDOWN TRANSACTIONAL continues
SQL> commit;
Commit complete.
SQL>
@Terminal3
SQL> show con_name
CON_NAME
——————————
CDB$ROOT
SQL>
SQL> shutdown transactional
……
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> commit;
Commit complete.
SQL>
6. After restarting the CDB and opening the PDB, select* from pdb_test1 => no rows (Transaction is rollbacked)
Result: as soon as you commit Transaction on CDB (Terminal 2), the SHUTDOWN TRANSACTIONAL proceed. The transaction in PDB is rollbacked/discarded.
Thus: correct answer is B
Excellent Explanation
B is correct
B is correct