08 - [Oracle 19C] Oracle shutdown

• To shut down a database and instance, you must first connect as SYSOPER or SYSDBA

• We need to shutdown sometimes for specific reasons:

 change a static parameter

 Patch the database server

 perform maintenance or other administrative tasks

 

 

				Abort	Immediate		Transactional		Normal
Allows new connections				NO	NO		NO		NO
Waits until current sessions end				NO	NO		NO		Yes
Wait until current transactions end				NO	NO		Yes		Yes
Forces a checkpoint and closes files				NO	Yes		Yes		Yes

 

 

Abort - It will not force a checkpoint and it closes files so we could lose data because of this option.

Immediate - (Best option) This is called safe shutdown and lead to consistent database.

 

 

* Not need instance recovery - safe shutdown

 

 

 

 

* Need instance recovery

 

 

 

When the DWBn write to data files?

1- Dirty buffers threshold

2- when we need free buffers for some operations

3- shutdown ( normal, transactional, immediate )

4- changing the status for tablespace ( for example read only )

5- during a checkpoint

 

 

 

 

 

아래와 같이 몇가지 실습을 해봅시다.

 

[oracle@test ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 14 11:55:34 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>
SQL> startup;
ORACLE instance started.

Total System Global Area 1526723608 bytes
Fixed Size		    9135128 bytes
Variable Size		  889192448 bytes
Database Buffers	  620756992 bytes
Redo Buffers		    7639040 bytes
Database mounted.
Database opened.
SQL>

 

 

 

• shutdown normal

먼저 shutdown normal 에 대한 테스트 입니다. 세션을 2개 연결해서 1번 세션과 2번 세션으로 테스트를 진행합니다.

2번째 세션에서 commit 를 하지 않아서/세션이 연결되어 있어서,    1번 세션에서 shutdown normal 을 했지만 대기중입니다.

 

 

 

 

2번 세션에서 commit 및 세션 종료를 진행후, 1번 세션에서 디비가 종료된것을 볼 수 있습니다

 

 

 

 

• shutdown transactional

In case of "shutdown transactional" - 세션2에서 commit 이후 세션1에서 instance 가 종료된 것을 볼 수 있다.

 

 

 

 

No more connection and session because the DB shutdown

 

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> insert into test values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 4195
Session ID: 266 Serial number: 20530


SQL>

 

 

 

• shutdown immediate

In case of "shutdown immediate" - 세션2에서 commit 를 하지 않았어도 instance 가 바로 종료된다.

 

 

 

 

3번째 insert 문은 rollback 된 것을 볼 수 있다.

 

SQL> 
SQL> startup;
ORACLE instance started.

Total System Global Area 1526723608 bytes
Fixed Size		    9135128 bytes
Variable Size		  889192448 bytes
Database Buffers	  620756992 bytes
Redo Buffers		    7639040 bytes
Database mounted.
Database opened.
SQL> 
SQL> 
SQL> select * from test;

	 N
----------
	 1
	 2

SQL>